SQL Query to get Ship_To Site Details for an AR Transaction in Oracle Fusion Account Receivables

SELECT DISTINCT rcta.trx_number,
                hca.account_number,
                hps.party_site_number
FROM   hz_cust_accounts hca
       JOIN hz_cust_acct_sites_all hcsa
         ON hca.cust_account_id = hcsa.cust_account_id
       JOIN hz_cust_site_uses_all hcsu
         ON hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
       JOIN hz_party_sites hps
         ON hcsa.party_site_id = hps.party_site_id
       JOIN ra_customer_trx_all rcta
         ON hca.party_id = rcta.ship_to_party_id
WHERE  hcsu.site_use_code = 'SHIP_TO'
       AND rcta.trx_number = :trx_number 

SQL Code to add SysAdmin responsibility to a specific User

The following query grants a user the System Administrator (SYSADMIN) responsibility. The query must be executed by APPS user.

I used the username "DAVID" in the example below.

-------------------------------------------------------------------------------
-- Query to add SYSADMIN responsibility to a user, using FND_USER_PKG.ADDRESP
-------------------------------------------------------------------------------
BEGIN
    fnd_user_pkg.Addresp(username => 'DAVID', -- User Name -- 
    resp_app => 'SYSADMIN', -- Apps Short Name
    resp_key => 'SYSTEM_ADMINISTRATOR', -- Responsibility Key
    security_group => 'STANDARD', description => NULL, 

    start_date => SYSDATE,
    end_date => NULL);

    COMMIT;

    dbms_output.Put_line('SYSADMIN Responsibility successfully added');
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('SYSADMIN responsibility not added due to '
                           || SQLERRM);

      ROLLBACK;
END; 

SQL Query to get Payment Process Request (PPR) Templates Used to Create a Payment Process Request (PPR) in oracle EBS R12

Query to get all AP Payments related to a PPR:-

SELECT c.*
FROM   ap_checks_all c
WHERE  c.payment_id IN (SELECT ip.payment_id
                        FROM   iby_payments_all ip
                        WHERE  payment_service_request_id IN
                               (SELECT
                               payment_service_request_id
                                                              FROM
                               iby_pay_service_requests
                                                              WHERE
                               calling_app_id = 200
                               AND
                                       call_app_pay_service_req_code =
                                       '&checkrun_name')

                              ); 

SQL Query to get Payment Process Request (PPR) Templates Used to Create a Payment Process Request (PPR) in oracle EBS R12 :-

SELECT AISCA.checkrun_name                        PPR_Name,

       apt.template_id,
       apt.template_name,
       ipmv.payment_method_name                   pmt_method_name,
       cba.bank_account_name                      bank_acct_name,
       ipp.payment_profile_name                   pmt_profile_name,
       cpd.payment_document_name                  pmt_doc_name,
       apt.pay_group_option                       pay_grp_optn,
       (SELECT Rtrim(Xmlagg(XMLELEMENT(e, apg.vendor_pay_group||
                     ',')) .extract('//text()'),
                       ',')
        FROM   ap_pay_group apg
        WHERE  apg.template_id = apt.template_id) enabled_pay_groups,
       apt.ou_group_option                        ou_grp_optn,
       (SELECT Rtrim(Xmlagg(XMLELEMENT(e, hou.short_code||
                     ',')) .extract('//text()'),
               ',')
        FROM   ap_ou_group,
               hr_operating_units hou
        WHERE  org_id = hou.organization_id
               AND template_id = apt.template_id) enabled_ous,
       apt.currency_group_option                  curr_grp_optn,
       (SELECT Rtrim(Xmlagg(XMLELEMENT(e, acg.currency_code||
                     ',')) .extract('//text()'), ',')
        FROM   ap_currency_group acg
        WHERE  acg.template_id = apt.template_id) enabled_currencies,
       apt.description                            ppp_description,
       apt.inactive_date,
       apt.addl_pay_thru_days,
       apt.addl_pay_from_days,
       apt.low_payment_priority,
       apt.hi_payment_priority,
       apt.vendor_id,
       apt.pay_only_when_due_flag,
       apt.vendor_type_lookup_code                vdr_type_lcode,
       apt.bank_account_id,
       apt.payment_profile_id,
       apt.zero_inv_allowed_flag,
       apt.payment_method_code,
       apt.inv_exchange_rate_type,
       apt.payment_date_option,
       apt.addl_payment_days,
       apt.payment_exchange_rate_type,
       apt.zero_amounts_allowed,
       apt.payables_review_settings,
       apt.calc_awt_int_flag,
       apt.payments_review_settings,
       apt.document_rejection_level_code          doc_reject_lvl,
       apt.create_instrs_flag,
       apt.payment_rejection_level_code           pmt_reject_lvl,
       apt.payment_document_id,
       plc.displayed_field                        supplier_type,
       pv.vendor_name                             payee,
       alc1.displayed_field                       template_type_name,
       gdct.user_conversion_type                  user_rate_type,
       fu.user_name
FROM   ap_payment_templates apt,
       po_lookup_codes plc,
       iby_payment_methods_vl ipmv,
       iby_payment_profiles ipp,
       ce_bank_accounts cba,
       ap_lookup_codes alc1,
       gl_daily_conversion_types gdct,
       po_vendors pv,
       fnd_user fu,
       ce_payment_documents cpd,
       ap_inv_selection_criteria_all AISCA
WHERE  1 = 1
       AND fu.user_id = apt.last_updated_by
       AND AISCA.checkrun_name = '&checkrun_name'
       AND AISCA.template_id = apt.template_id
       AND plc.lookup_code(+) = apt.vendor_type_lookup_code
       AND plc.lookup_type(+) = 'VENDOR TYPE'
       AND cba.bank_account_id(+) = apt.bank_account_id
       AND ipmv.payment_method_code(+) = apt.payment_method_code
       AND alc1.lookup_type(+) = 'PAYMENT_TEMPLATE_TYPE'
       AND alc1.lookup_code(+) = apt.template_type
       AND gdct.conversion_type(+) = apt.payment_exchange_rate_type
       AND ipp.payment_profile_id(+) = apt.payment_profile_id
       AND pv.party_id(+) = apt.party_id
       AND apt.payment_document_id = cpd.payment_document_id(+)
ORDER  BY apt.template_name,
          ipmv.payment_method_name;