Order Receivable Invoice Query

SELECT   ooh.order_number,
         --    ool.line_id,
         ool.ordered_quantity,
         ool.shipped_quantity,
         ool.invoiced_quantity,
         wdd.delivery_detail_id,
         wnd.delivery_id,
         TO_CHAR (wdd.CREATION_DATE, 'DD-MON-YY') delivery_cre_date,
         (SELECT   trx_number
            FROM   ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
           WHERE   rctl.interface_line_attribute1 =
                      TO_CHAR (ooh.order_number)
                   AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id)
                   AND rctl.interface_line_attribute3 =
                         TO_CHAR (wnd.delivery_id)
                   AND rctl.customer_trx_id = rct.customer_trx_id
                   AND rct.interface_header_context = 'ORDER ENTRY')
            trx_number
  FROM   oe_order_headers_all ooh,
         oe_order_lines_all ool,
         wsh_delivery_details wdd,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wda
 WHERE       ooh.header_Id = ool.header_id
         AND ooh.org_id = ool.org_id
         AND wdd.source_header_id = ooh.header_id
         AND wdd.SOURCE_LINE_ID = ool.line_id
         AND wdd.delivery_detail_Id = wda.delivery_detail_id
         AND wda.delivery_id = wnd.delivery_id
         -- AND rctl.interface_line_attribute1 = TO_CHAR (ooh.order_number)
         -- AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id)
         -- AND rctl.interface_line_attribute3 = TO_CHAR (wnd.delivery_id)
         -- AND rctl.customer_trx_id = rct.customer_trx_id
         -- AND rct.interface_header_context = 'ORDER ENTRY'
         AND wdd.CREATION_DATE BETWEEN '01-MAY-15' AND '30-MAY-15'
--and wda.delivery_id = 2555078
--  and ooh.order_number = 1531311781
-- AND rct.org_id = 613

AR Joins

All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Customer Transaction Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND ps.cust_trx_type_id = rtt.cust_trx_type_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS NOT IN ('CM', 'PMT') AND site_uses.site_use_code = 'BILL_TO' AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id AND rta.customer_trx_id = ps.customer_trx_id AND rta.customer_trx_id = rgld.customer_trx_id AND rgld.code_combination_id = cc.code_combination_id AND rgld.account_class = 'REV'

-- Query for Credit Memo Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'