, , , , ,

Query for Supplier Bank Details

SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
                pvs.vendor_site_code vendor_site_code,
                aba.bank_account_name bank_account_name,
                aba.bank_account_num bank_account_num,
                aba.currency_code currency_code,
                abau.primary_flag primary_flag, abb.bank_name bank_name,
                abb.bank_number bank_number,
                abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
           FROM ap_bank_account_uses_all abau,
                ap_bank_accounts_all aba,
                ap_bank_branches abb,
                po_vendors pv,
                po_vendor_sites_all pvs
          WHERE abau.external_bank_account_id = aba.bank_account_id
            AND aba.bank_branch_id = abb.bank_branch_id
            AND abau.vendor_id = pv.vendor_id
            AND abau.vendor_id = pvs.vendor_id(+)
            AND abau.vendor_site_id = pvs.vendor_site_id(+)
/by
, , , , ,

Query for Customer Receipt Details

SELECT   acra.cash_receipt_id,
         DECODE (acra.TYPE,
                 ‘cash’, ‘cash receipt receipt’,
                 ‘misc’, ‘miscellaneous’,
                 acra.TYPE
                ) receipt_type,
         acra.currency_code, acra.doc_sequence_value receipt_number,
         acra.receipt_number reference_number,
         TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
         acra.misc_payment_source, hca.account_number customer_no,
         NVL (acra.amount, 0) entered_amount,
         NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
         arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
         abb.bank_name, abb.bank_branch_name, acra.comments description
    FROM ar_cash_receipts_all acra,
         ar_receipt_methods arm,
         ap_bank_accounts_all abaa,
         ap_bank_branches abb,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE acra.pay_from_customer = hca.cust_account_id(+)
     AND acra.org_id = abaa.org_id(+)
     AND hca.party_id = hp.party_id(+)
     AND acra.receipt_method_id = arm.receipt_method_id
     AND acra.remittance_bank_account_id = abaa.bank_account_id
     AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;
/by
, , , ,

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