AR Customer Ledger with Unpaid Invoices

SELECT arps.org_id, hou.NAME company, rctl.customer_trx_id,
TO_CHAR (arps.trx_date, ‘MON-RRRR’) fiscal_year_month,
arps.trx_number invoice_number, arps.trx_date invoice_date,
arps.customer_id,
NVL (arps.amount_due_original, 0) total_invoice_amount,
NVL (arps.tax_original, 0) total_tax_amount,
NVL (quantity_invoiced, 0)
* NVL (unit_selling_price, 0) line_amount,
arps.due_date,
CASE
WHEN arps.CLASS = ‘INV’
THEN ‘Invoice’
WHEN arps.CLASS = ‘CM’
THEN ‘Credit Memo’
WHEN arps.CLASS = ‘DM’
THEN ‘Debit Memo’
ELSE ‘Others’
END inv_type,
arps.gl_date, REPLACE (rt.NAME, ‘,’, ‘ ‘) payment_terms,
arps.term_id, arps.invoice_currency_code,
TO_CHAR (arps.gl_date, ‘RRRR’) fiscal_year,
rctl.inventory_item_id,
TO_CHAR (arps.gl_date, ‘Mon-RRRR’) fiscal_period,
REPLACE (rctl.description, ‘,’, ‘ ‘) item_desc,
msi.segment1 item_code, rctl.quantity_invoiced,
rctl.extended_amount, gll.currency_code ledger_currency,
(SELECT ct_reference
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id =
rctl.customer_trx_id)
invoice_reference,
(SELECT DISTINCT gck.concatenated_segments
FROM ra_cust_trx_line_gl_dist_all rctd,
gl_code_combinations_kfv gck
WHERE 1 = 1
AND rctl.customer_trx_line_id =
rctd.customer_trx_line_id
AND rctd.account_class IN (‘REV’)
–and rctl.line_type=’LINE’
AND rctd.code_combination_id =
gck.code_combination_id)
rev_cc_account,
(SELECT DISTINCT gck.concatenated_segments
FROM ra_cust_trx_line_gl_dist_all rctd,
gl_code_combinations_kfv gck
WHERE 1 = 1
AND rctd.customer_trx_id = arps.customer_trx_id
AND rctd.account_class IN (‘REC’)
–and rctl.line_type=’LINE’
AND rctd.code_combination_id =
gck.code_combination_id)
rec_cc_account,
rctl.unit_selling_price, hca.account_number customer_number,
SUM (xal.entered_dr) entered_dr,
SUM (xal.entered_cr) entered_cr,
SUM (xal.accounted_cr) accounted_cr,
SUM (xal.accounted_dr) accounted_dr, xal.currency_code,
xal.accounting_date
FROM ar_payment_schedules_all arps,
xla.xla_transaction_entities xte,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl_ledgers gll,
hr_operating_units hou,
ra_terms_vl rt,
ra_customer_trx_lines_all rctl,
mtl_system_items_b msi,
hz_cust_accounts hca
WHERE 1 = 1
AND xte.source_id_int_1 = arps.customer_trx_id
AND xte.entity_id = xah.entity_id(+)
AND xte.ledger_id = gll.ledger_id(+)
AND xah.ae_header_id = xal.ae_header_id(+)
AND xte.entity_code = ‘TRANSACTIONS’
–AND arps.trx_number = ‘10021269’
— AND arps.trx_number = ‘10046064’
— AND xah.event_type_code = ‘INV_CREATE’
— AND arps.customer_trx_id IN (985645, 1202200, 985646, 1037036)
AND arps.amount_due_remaining <> 0
AND arps.org_id = p_org_id –204
AND arps.org_id = hou.organization_id
AND arps.customer_trx_id = rctl.customer_trx_id
AND rctl.line_type = ‘LINE’
AND arps.customer_id = hca.cust_account_id
AND rctl.inventory_item_id = msi.inventory_item_id(+)
AND rctl.warehouse_id = msi.organization_id(+)
AND arps.term_id = rt.term_id
GROUP BY xal.currency_code,
xal.accounting_date,
arps.trx_number,
arps.trx_date,
arps.due_date,
arps.amount_due_original,
arps.tax_original,
arps.CLASS,
arps.gl_date,
arps.invoice_currency_code,
rctl.description,
msi.segment1,
rctl.quantity_invoiced,
arps.customer_trx_id,
rctl.unit_selling_price,
hca.account_number,
rt.NAME,
arps.org_id,
hou.NAME,
arps.customer_id,
rctl.customer_trx_id,
arps.term_id,
rctl.extended_amount,
rctl.customer_trx_line_id,
rctl.inventory_item_id,
gll.currency_code
ORDER BY arps.trx_number;ar_customer_ledger