Introduction:

This Post illustrates the steps required to Fetch the Invoice lines not transferred to GL in Oracle .

Sql Script:

SELECT rct.trx_number invoice_number, rctl.line_number, msib.segment1,
hp.party_name bill_to_customer,
DECODE (rctt.TYPE,
‘CB’, ‘Chargeback’,
‘CM’, ‘Credit Memo’,
‘DM’, ‘Debit Memo’,
‘DEP’, ‘Deposit’,
‘GUAR’, ‘Guarantee’,
‘INV’, ‘Invoice’,
‘PMT’, ‘Receipt’,
‘Invoice’
) invoice_class,
rctl.quantity_invoiced qty, rctl.unit_selling_price unit_price,
SUM (rctd.amount) amount, rct.invoice_currency_code currency,
rct.trx_date inv_date, rctd.gl_date gl_date,
rct.creation_date inv_creation_date, fu.user_name inv_created_by,
(SELECT NAME
FROM apps.ra_terms rat
WHERE rat.term_id = rct.term_id) terms, rctt.NAME order_type
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctd,
hz_parties hp,
hz_cust_accounts_all hca,
ra_cust_trx_types_all rctt,
hr_operating_units hou,
ar_payment_schedules_all arps,
mtl_system_items_b msib,
fnd_user fu
WHERE rct.customer_trx_id = rctd.customer_trx_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND msib.inventory_item_id = rctl.inventory_item_id
AND rctl.warehouse_id = msib.organization_id
AND hp.party_id = hca.party_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = rctt.org_id
AND rct.org_id = hou.organization_id
AND arps.customer_trx_id(+) = rct.customer_trx_id
AND rct.org_id = NVL (:p_org_id, rct.org_id)
AND rct.trx_number = NVL (:trx_number, rct.trx_number)
AND TRUNC (rct.creation_date) BETWEEN NVL (:p_trx_date_from,
rct.trx_date)
AND NVL (:p_trx_date_to, rct.trx_date)
AND rctd.gl_date BETWEEN NVL (:p_gl_date_from, rctd.gl_date)
AND NVL (:p_gl_date_to, rctd.gl_date)
AND hp.party_name = NVL (:p_cust_name, hp.party_name)
AND NVL (rct.ct_reference, ‘XX’) =
NVL (NVL (:p_sales_order_no, rct.ct_reference), ‘XX’)
AND rctd.posting_control_id = -3
AND rctd.account_set_flag = ‘N’
AND fu.user_id = rct.created_by
GROUP BY rct.trx_number,
arps.amount_due_remaining,
hp.party_name,
rctt.TYPE,
rct.invoice_currency_code,
rct.trx_date,
rctd.gl_date,
rct.term_id,
rctt.NAME,
msib.segment1,
rctl.line_number,
rctl.quantity_invoiced,
rctl.unit_selling_price,
rct.creation_date,
fu.user_name
ORDER BY rct.trx_number, rctl.line_number ASC;

Queries

Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search