, , , ,

Concurrent Request Status in R12

SELECT   fcr.phase_code,
         DECODE (fcr.phase_code,’C’, ‘Completed’, ‘P’, ‘Pending’, ‘R’, ‘Running’, ‘I’, ‘Inactive’, fcr.phase_code) phase,
         fcr.status_code,
         DECODE (fcr.status_code,’A’, ‘Waiting’,
                                ‘B’, ‘Resuming’,
                                ‘C’, ‘Normal’,
                                ‘D’, ‘Cancelled’,
                                ‘E’, ‘Error’,
                                ‘F’, ‘Scheduled’,
                                ‘G’, ‘Warning’,
                                ‘H’, ‘On Hold’,
                                ‘I’, ‘Normal’,
                                ‘M’, ‘No Manager’,
                                ‘Q’, ‘Standby’,
                                ‘R’, ‘Normal’,
                                ‘S’, ‘Suspended’,
                                ‘T’, ‘Terminating’,
                                ‘U’, ‘Disabled’,
                                ‘W’, ‘Paused’,
                                ‘X’, ‘Terminated’,
                                ‘Z’, ‘Waiting’,
                                fcr.status_code) status,
         request_date,
         fat.description, frt.responsibility_name, fu.user_name,
         fu.description, fcpt.user_concurrent_program_name, fcpt.description,
         fcr.request_id, fcr.request_date, fcr.priority, fcr.requested_start_date, fcr.hold_flag,
         fcr.number_of_arguments, fcr.number_of_copies, fcr.save_output_flag,
         fcr.printer, fcr.parent_request_id, fcr.description,
         fcr.resubmit_time, fcr.resubmit_end_date, fcr.argument_text,
         fcr.argument1, fcr.argument2, fcr.argument3, fcr.argument4,
         fcr.argument5, fcr.argument6, fcr.argument7, fcr.argument8,
         fcr.argument9 org, fcr.argument10, fcr.argument11, fcr.argument12,
         fcr.argument13, fcr.argument14, fcr.argument15, fcr.argument16,
         fcr.argument17, fcr.argument18, fcr.argument19, fcr.argument20,
         fcr.argument21, fcr.argument22, fcr.argument23, fcr.argument24,
         fcr.argument25, fcr.output_file_type, fcr.cancel_or_hold,
         fcr.completion_code, fcr.ofile_size, fcr.lfile_size,
         fcr.logfile_name, fcr.logfile_node_name, fcr.outfile_name,
         fcr.outfile_node_name
    FROM fnd_concurrent_requests fcr,
         fnd_user fu,
         fnd_responsibility_tl frt,
         fnd_application_tl fat,
         fnd_concurrent_programs_tl fcpt
   WHERE (fu.user_id = fcr.requested_by)
     AND (fat.application_id = fcr.program_application_id)
     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)
     AND (fcr.responsibility_id = frt.responsibility_id)
     AND fat.LANGUAGE = ‘US’
     AND frt.LANGUAGE = ‘US’
     AND fcpt.LANGUAGE = ‘US’
     AND fcr.request_id = NVL (:request_id, fcr.request_id)
ORDER BY fcr.request_date DESC
/by
, , , , ,

Reset Oracle Application Password

This is an anonymous PL/SQL block to reset your application password through back end.

This Code is tested in R12.1.3 Instance.

DECLARE
v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘ORACLEERP’,’learn123#’);
COMMIT;
END;

/by
, , , ,

API to Purge an person from Oracle HRMS

DECLARE
l_person_org_manager_warning VARCHAR2 (200);
BEGIN
hr_person_api.delete_person(p_validate => FALSE,
p_effective_date => SYSDATE,
p_person_id => :person_id,
p_perform_predel_validation => FALSE,
p_person_org_manager_warning => l_person_org_manager_warning
);
COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.

/by