exec mo_global.set_policy_context(‘S’,:&p_org_id);
SELECT DISTINCT gjb.NAME journal_batch_name, gjh.NAME,
gjh.default_effective_date gldate, msi.segment1 item,
NULL brand, rct.bill_to_site_use_id bill_to_site_use_id,
hca.account_number customer,
gcc.segment3 “GL ACCT 3”, gcc.segment1 company,
rct.interface_header_attribute2 ordertype,
rctt.NAME ar_transaction_type, rctl.sales_order order_number,
rct.trx_number invoice_number, rct.purchase_order po_number,
NULL invsales, NULL grosssales, NULL units, NULL frtinvsales,
NULL frtgrosssales,
(rctlgd.amount) * (-1) deductions_after_invoice,
NVL (rctl.description, rct.comments) trx_line_description,
rctlgd.cust_trx_line_gl_dist_id primary_key
FROM apps.ra_cust_trx_line_gl_dist_all rctlgd,
apps.ra_customer_trx_all rct,
apps.gl_code_combinations_kfv gcc,
apps.ra_customer_trx_lines_all rctl,
apps.ra_batch_sources_all rbs,
apps.ra_cust_trx_types_all rctt,
apps.hz_cust_accounts hca,
apps.hz_parties hp,
apps.xla_distribution_links xdl,
apps.xla_ae_lines xal,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_je_batches gjb,
apps.gl_import_references gir,
apps.mtl_system_items_b msi,
apps.org_organization_definitions ood
WHERE rctlgd.customer_trx_id = rct.customer_trx_id
AND rctlgd.customer_trx_line_id = rctl.customer_trx_line_id(+)
AND rct.cust_trx_type_id = rctt.cust_trx_type_id(+)
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.batch_source_id = rbs.batch_source_id
AND xdl.application_id = xal.application_id
AND xal.application_id = 222
AND rctlgd.event_id = xdl.event_id
AND xdl.source_distribution_id_num_1 =
rctlgd.cust_trx_line_gl_dist_id
AND xdl.rounding_class_code = ‘RECEIVABLE’
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.code_combination_id = gcc.code_combination_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND gir.gl_sl_link_id = xal.gl_sl_link_id
–AND gjb.NAME = ‘Receivables A 7264158 183940932’
AND gjl.period_name = ‘JUL-24’
AND rctl.warehouse_id = msi.organization_id(+)
AND rctl.inventory_item_id = msi.inventory_item_id(+)
AND rctl.warehouse_id = ood.organization_id(+)
UNION ALL
SELECT gjb.NAME journal_batch_name, gjh.NAME,
gjh.default_effective_date gldate, NULL item, NULL brand,
acra.customer_site_use_id bill_to_site_use_id,
ac.customer_number customer,
glcc.segment3 “GL ACCT 3”, glcc.segment1 company,
NULL ordertype, ‘Receipt Write-off’ ar_transaction_type,
NULL order_number, acra.receipt_number invoice_number,
araa.application_ref_num po_number, NULL invsales, NULL grosssales,
NULL units, NULL frtinvsales, NULL frtgrosssales,
(araa.amount_applied) * -1 deductions_after_invoice,
arta.NAME trx_line_description,
null primary_key
FROM apps.gl_je_batches gjb,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_ledgers gl,
apps.gl_balances gb,
apps.gl_code_combinations_kfv glcc,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_transaction_entities xte,
apps.ar_receivable_applications_all araa,
apps.ar_receivables_trx_all arta,
apps.xla_events eve,
apps.ar_cash_receipts_all acra,
apps.ar_customers ac
WHERE 1 = 1 –and gjb.NAME = (‘Receivables A 7242158 183302273’)
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_source = ‘Receivables’
AND je_category = ‘Receipts’
AND gjl.je_header_id = gjh.je_header_id
AND gjl.period_name = ‘JUL-24’
AND gjl.code_combination_id = gb.code_combination_id
–AND gjl.code_combination_id = 7463
AND gjh.ledger_id = gl.ledger_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gb.code_combination_id = glcc.code_combination_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.je_batch_id = gjh.je_batch_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND xal.code_combination_id = glcc.code_combination_id
–AND NVL (xte.source_id_int_1, -99) =8793182
AND xte.application_id = xal.application_id
AND xte.entity_id = xah.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = glcc.code_combination_id
AND NVL (xte.source_id_int_1, -99) = araa.cash_receipt_id
AND TO_CHAR (araa.apply_date, ‘MON-RR’) = gjl.period_name
AND araa.code_combination_id = glcc.code_combination_id
AND araa.event_id = eve.event_id
AND xte.entity_id = eve.entity_id
AND gir.reference_6 = araa.event_id
AND araa.receivables_trx_id = arta.receivables_trx_id
AND araa.cash_receipt_id = acra.cash_receipt_id
AND acra.pay_from_customer = ac.customer_id
UNION ALL
SELECT DISTINCT gjb.NAME je_batch_name, gjh.NAME, gjh.posted_date gldate,
NULL item, NULL brand,
rcta.bill_to_site_use_id bill_to_site_use_id,
ada.customer_number customer,
glcc.segment3 “GL ACCT 3”, glcc.segment1 company,
rcta.interface_header_attribute2 ordertype,
xal.accounting_class_code ar_transaction_type,
NULL order_number, rcta.trx_number invoice_number,
adjustment_number po_number, NULL invsales, NULL grosssales,
NULL units, NULL frtinvsales, NULL frtgrosssales,
(NVL (xal.accounted_dr, 0) – NVL (xal.accounted_cr, 0)
) deductions_after_invoice,
ada.activity_name trx_line_description,
null primary_key
FROM apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
apps.gl_code_combinations_kfv glcc,
xla.xla_transaction_entities xte,
apps.ar_adjustments_v ada,
apps.ra_customer_trx_all rcta,
apps.gl_ledgers gl,
apps.gl_balances gb,
apps.ar_customers ac,
apps.gl_je_batches gjb,
apps.org_organization_definitions ood
WHERE 1 = 1
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = glcc.code_combination_id
AND xte.entity_id = xah.entity_id
AND xte.entity_code = ‘ADJUSTMENTS’
AND xte.ledger_id = gl.ledger_id
AND xte.application_id = 222
AND NVL (xte.source_id_int_1, -99) = ada.adjustment_id
AND ada.customer_trx_id = rcta.customer_trx_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gb.ledger_id = gl.ledger_id
AND gjh.je_batch_id = gjb.je_batch_id
AND rcta.bill_to_customer_id = ac.customer_id(+)
AND rcta.interface_header_attribute10 = ood.organization_id
AND gjh.je_source = ‘Receivables’
AND gjl.period_name = ‘JUL-24’
UNION ALL
SELECT gjb.NAME journal_batch_name, gjh.NAME,
gjh.default_effective_date gldate, null item,
NULL brand, ai.vendor_site_id bill_to_site_use_id,
asp.segment1 customer,
gcc.segment3 “GL ACCT 3”, gcc.segment1 company,
null ordertype,
null ar_transaction_type, pha.segment1 order_number,
ai.invoice_num invoice_number, rsh.receipt_num po_number,
NULL invsales, NULL grosssales, NULL units, NULL frtinvsales,
NULL frtgrosssales,
aid.amount deductions_after_invoice,
ai.description trx_line_description,
null primary_key
FROM ap_invoices_all ai,
ap_suppliers asp,
ap_supplier_sites_all ass,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
po_headers_all pha,
–po_distributions_all pda,
rcv_shipment_headers rsh,
–rcv_shipment_lines rsl,
–rcv_transactions rtr,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
gl_code_combinations_kfv gcc,
xla.xla_transaction_entities xte,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers gjh,
gl_je_lines gjl,
apps.gl_je_batches gjb,
apps.gl_ledgers gl,
apps.gl_balances gb
WHERE ai.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ai.invoice_id = aid.invoice_id
AND pha.po_header_id(+)=ai.po_header_id
–AND aid.po_distribution_id=pda.po_distribution_id(+)
and rsh.invoice_num(+)=ai.invoice_num
and rsh.invoice_date(+)=ai.invoice_date
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_code = ‘AP_INVOICES’
AND xte.entity_id = xah.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND aid.dist_code_combination_id = gcc.code_combination_id
AND gcc.code_combination_id = xal.code_combination_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND ai.vendor_id(+) = asp.vendor_id
AND asp.vendor_id = ass.vendor_id
AND ass.vendor_site_id = ai.vendor_site_id
AND aid.amount != 0
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gjh.je_header_id
and gjh.je_source=’Payables’
AND gjh.je_batch_id = gjb.je_batch_id
— AND gjh.je_source = ‘Receivables
AND gjl.period_name = ‘JUL-24’
AND gjl.code_combination_id = gb.code_combination_id
AND gjh.ledger_id = gl.ledger_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gb.code_combination_id = gcc.code_combination_id
