AR Customer Master Query

SELECT   hca.account_number   --,cust.party_id
       --  (select ORGANIZATION_NAME from org_organization_definitions where OPERATING_UNIT = hcsu.org_id) org
        ,customer_name,
         (   hl.address1
          || '  '
          || hl.address2
          || ' '
          || hl.address3
          || ' '
          || hl.address4)
            ship_to,
         hl.city,
         hl.country,
         (SELECT   hcp.raw_phone_number
            FROM   hz_contact_points hcp, hz_relationships hr, hz_parties hzp
           WHERE       hcp.owner_table_id = hr.party_id
                   AND hr.object_id = NVL (cust.party_id, hr.object_id)
                   AND hr.status = 'A'
                   AND hcp.status = 'A'
                   AND hcp.primary_flag = 'Y'
                   AND hzp.party_id = hr.party_id
                   AND hcp.phone_line_type = 'EMAIL'
                   AND hcp.owner_table_name = 'HZ_PARTIES')
            email,
         (SELECT   description
            FROM   ar_customer_profiles_v a, ra_terms b
           WHERE       customer_id = NVL (f.customer_id, customer_id)
                   AND status = 'A'
                   AND site_use_id IS NULL
                   AND a.standard_terms = b.term_id)
            cust_profile,
        -- hcsu.org_id,
         xyka_custom_yka.get_ar_balance_yka (customer_id) balance,
         xyka_custom_yka.get_ar_overall_cr_limit (customer_id) cr_limit,
         xyka_custom_yka.get_ar_customer_category_name (customer_id)
            customer_category,
         xyka_custom_yka.get_ar_credit_check (customer_id, 126) credit_check, -- org_id
         xyka_custom_yka.get_ar_collector_code (customer_id, 126) collector,
         (SELECT   MAX (name) collector_id
            --into l_collector_code
            FROM   ar_customers a, hz_customer_profiles b, ar_collectors c
           WHERE       a.customer_id = b.cust_account_id
                   AND a.customer_id = b.cust_account_id
                   AND b.site_use_id IS NULL
                   AND b.collector_id = c.collector_id
                   AND a.customer_id = f.customer_id)
            collector_name,
         (SELECT   MAX (credit_checking)
            FROM   hz_customer_profiles hcp
           WHERE   hcp.cust_account_id = f.customer_id)            cr_chk ,
           (select segment5 from gl_code_combinations where code_combination_id = hcsu.GL_ID_REC ) gcc
  FROM   hz_parties cust,
         hz_cust_accounts hca,
         hz_cust_acct_sites_all hcas,
         hz_cust_site_uses_all hcsu,
         hz_party_sites hps,
         hz_locations hl,
         ar_customers f
 WHERE       1 = 1
         AND hca.cust_account_id = hcas.cust_account_id
         AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
         AND hps.location_id(+) = hl.location_id
         AND hps.party_site_id = hcas.party_site_id
         AND cust.party_id = hca.party_id
         AND f.customer_id = hca.cust_account_id
         --and hcsu.primary_flag = 'y'
         AND hcsu.status = 'A'
         AND hcsu.site_use_code = 'BILL_TO'
        -- AND hca.cust_account_id = 7168
         AND hcsu.org_id = NVL (:org_id, hcsu.org_id)
         and hca.account_number like 'S%'
         order by 1

AP Trial Balance Report Redinition

The R12 Accounts Payable Trial Balance has 4 templates available that control the output that is
displayed. The four templates available are:

Accounts Payable Trial Balance - Group by Account, Summary
Accounts Payable Trial Balance - Group by Account, Detail
Accounts Payable Trial Balance - Group by Third Party, Summary
Accounts Payable Trial Balance - Group by Third Party, Detail

Do the following to change the template that is used when the report is submitted:

1. Navigate: Payables Responsibility>Other>Request>Run
2. Select the Accounts Payable Trial Balance
3. Enter all of the parameters for the report. DO NOT SUBMIT THE REPORT.
4. Click on the Options button in the Upon Completion section of the Submit Request screen.
5. Select the desired template name.
6. Submit the Trial balance.

Do the following to change the default template used when the report is submitted:

1. Navigate: System Administration Responsibility>Concurrent>Programs
2. Search for:

Application = Payables
Program = Accounts Payable Trial Balance

Click on Go

3. Click on Update for the Accounts Payable Trial Balance program

4. Select the Onsite Setting tab

5. Select the default template from the Template dropdown in the General section.

6. Click on Apply to save the changes.
--------------------------- IMPORTANT --------------------------------

Navigate to AP->Setup->Accounting Setups->Subledger Accounting Setup->Open Account Balances Listing Definitions

Click on Create and give in the details.

Save and try to run the Report. It should show LOV now.