SELECT open_trx.org_id
,open_trx.operating_unit
,open_trx.trx_id
,open_trx.trx_number
,to_char(open_trx.trx_date) trx_date
,open_trx.document_type
,open_trx.document_status
,open_trx.posting_status
,open_trx.document_source
,open_trx.party_id
,open_trx.party_number
,open_trx.party_name
,open_trx.party_site_id
,open_trx.party_site_code
,open_trx.party_site_name
,open_trx.entity_code
,open_trx.event_class_code
,open_trx.entity_name
FROM (SELECT to_number(NULL) org_id
,to_char(NULL) operating_unit
,to_number(NULL) trx_id
,to_char(NULL) trx_number
,to_date(NULL) trx_date
,to_char(NULL) document_type
,to_char(NULL) document_status
,to_char(NULL) posting_status
,to_char(NULL) document_source
,to_number(NULL) party_id
,to_char(NULL) party_number
,to_char(NULL) party_name
,to_number(NULL) party_site_id
,to_char(NULL) party_site_code
,to_char(NULL) party_site_name
,to_char(NULL) entity_code
,to_char(NULL) event_class_code
,to_char(NULL) entity_name
FROM dual
WHERE 1 = 0
-- Open Purchase Order
UNION ALL
SELECT poh.org_id org_id
,hou.name operating_unit
,poh.po_header_id trx_id
,poh.segment1 trx_number
,poh.creation_date trx_date
,flv_type.displayed_field document_type
,flv_status.displayed_field document_status
,to_char(NULL) document_status
,to_char(NULL) document_source
,poh.vendor_id party_id
,pv.segment1 party_number
,pv.vendor_name party_name
,poh.vendor_site_id party_site_id
,pvs.vendor_site_code party_site_code
,pvs.vendor_site_code party_site_name
,'PURCHASE_ORDER' entity_code
,'PO_PA' event_class_code
,'Purchase Order' entity_name
FROM po_headers_all poh
,po_lookup_codes flv_type
,po_lookup_codes flv_status
,ap_suppliers pv
,ap_supplier_sites_all pvs
,hr_operating_units hou
WHERE poh.type_lookup_code = flv_type.lookup_code(+)
AND flv_type.lookup_type(+) = 'PO TYPE'
AND poh.authorization_status = flv_status.lookup_code(+)
AND flv_status.lookup_type(+) = 'DOCUMENT STATE'
/*
AND poh.closed_date IS NULL
AND poh.authorization_status IN ('REQUIRES REAPPROVAL'
,'INCOMPLETE'
,'IN PROCESS'
,'PRE-APPROVED')
*/
AND poh.vendor_id = pv.vendor_id
AND poh.vendor_site_id = pvs.vendor_site_id
AND poh.org_id = hou.organization_id
AND NOT EXISTS (SELECT '2-Way Match'
FROM po_distributions_all pod
,ap_invoice_lines_all ail
WHERE pod.po_distribution_id = ail.po_distribution_id
AND pod.po_header_id = poh.po_header_id)
AND EXISTS (SELECT 'Legacy Tax Attached'
FROM jai_po_taxes jlt
WHERE jlt.po_header_id = poh.po_header_id)
AND poh.org_id = &P_ORG_ID
-- Open PO Receipts
UNION ALL
SELECT pll.org_id org_id
,hou.name operating_unit
,rsh.shipment_header_id trx_id
,rsh.receipt_num trx_number
,rt.transaction_date trx_date
,flv_doc.displayed_field document_type
,'' document_status
,to_char(NULL) document_status
,to_char(NULL) document_source
,poh.vendor_id party_id
,pv.segment1 party_number
,pv.vendor_name party_name
,poh.vendor_site_id party_site_id
,pvs.vendor_site_code party_site_code
,pvs.vendor_site_code party_site_name
,'RCV_TRANSACTION' entity_code
,'RECEIVING' event_class_code
,'PO Receipts' entity_name
FROM po_line_locations_all pll
,po_headers_all poh
,rcv_shipment_lines rsl
,rcv_shipment_headers rsh
,rcv_transactions rt
,po_lookup_codes flv_doc
,ap_suppliers pv
,ap_supplier_sites_all pvs
,hr_operating_units hou
WHERE pll.po_header_id = poh.po_header_id
AND pll.line_location_id = rsl.po_line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.source_document_code = flv_doc.lookup_code(+)
AND flv_doc.lookup_type(+) = 'DOCUMENT'
AND rt.transaction_type <> 'UNORDERED'
AND poh.vendor_id = pv.vendor_id
AND poh.vendor_site_id = pvs.vendor_site_id
AND poh.org_id = hou.organization_id
AND pll.quantity_received > 0
AND (pll.quantity_received > pll.quantity_billed OR
(pll.quantity_received = pll.quantity_billed AND NOT EXISTS
(SELECT 'Delivered'
FROM rcv_transactions rtr
WHERE rtr.transaction_type = 'DELIVER'
AND rtr.shipment_header_id = rt.shipment_header_id
AND rtr.shipment_line_id = rt.shipment_line_id) AND NOT EXISTS
(SELECT 'RTR with RTV'
FROM rcv_transactions rtr
,rcv_transactions rtv
WHERE rtr.transaction_type = 'RETURN TO RECEIVING'
AND rtr.shipment_header_id = rtv.shipment_header_id
AND rtr.shipment_line_id = rtv.shipment_line_id
AND rtv.transaction_type = 'RETURN TO VENDOR'
AND rtr.shipment_header_id = rt.shipment_header_id
AND rtr.shipment_line_id = rt.shipment_line_id) AND NOT EXISTS
(SELECT 'RTV with Debit Memo'
FROM rcv_transactions rtr
,ap_invoice_distributions_all aid
,ap_invoices_all aia
WHERE rtr.transaction_type = 'RETURN TO RECEIVING'
AND rtr.po_distribution_id = aid.po_distribution_id
AND aid.invoice_id = aia.invoice_id
AND aia.invoice_type_lookup_code = 'DEBIT'
AND rtr.shipment_header_id = rt.shipment_header_id
AND rtr.shipment_line_id = rt.shipment_line_id)))
AND EXISTS (SELECT 'Legacy Tax Attached'
FROM jai_rcv_line_taxes jlt
WHERE jlt.transaction_id = rt.transaction_id)
AND NOT EXISTS (SELECT 'Tax contains Excise/VAT and ModVAT claimed'
FROM jai_rcv_cenvat_claims jrcc
WHERE jrcc.transaction_id = rt.transaction_id)
AND pll.org_id = &P_ORG_ID
-- Open AP Invoice
UNION ALL
SELECT ai.org_id org_id
,hou.name operating_unit
,ai.invoice_id trx_id
,ai.invoice_num trx_number
,ai.invoice_date trx_date
,flv_type.displayed_field document_type
,flv_appl.displayed_field document_status
,flv_acct.displayed_field posting_status
,ai.source document_source
,ai.vendor_id party_id
,pv.segment1 party_number
,pv.vendor_name party_name
,ai.vendor_site_id party_site_id
,pvs.vendor_site_code party_site_code
,pvs.vendor_site_code party_site_name
,'AP_INVOICES' entity_code
,'STANDARD INVOICES' event_class_code
,'AP Invoice' entity_name
FROM (SELECT aia.invoice_id
,aia.invoice_num
,aia.invoice_date
,aia.org_id
,ap_invoices_pkg.get_approval_status(aia.invoice_id
,aia.invoice_amount
,aia.payment_status_flag
,aia.invoice_type_lookup_code) approval_status
,ap_invoices_pkg.get_posting_status(aia.invoice_id) posting_status
,ap_invoices_utility_pkg.get_prepay_amount_remaining(aia.invoice_id) prepay_remaining
,aia.invoice_type_lookup_code
,aia.source
,aia.vendor_id
,aia.vendor_site_id
FROM ap_invoices_all aia
WHERE org_id = &P_ORG_ID) ai
,ap_lookup_codes flv_type
,ap_lookup_codes flv_appl
,ap_lookup_codes flv_acct
,ap_suppliers pv
,ap_supplier_sites_all pvs
,hr_operating_units hou
WHERE ai.invoice_type_lookup_code = flv_type.lookup_code(+)
AND flv_type.lookup_type(+) = 'INVOICE TYPE'
AND ai.approval_status = flv_appl.lookup_code(+)
AND flv_appl.lookup_type(+) = DECODE(ai.invoice_type_lookup_code
,'PREPAYMENT'
,'PREPAY STATUS'
,'NLS TRANSLATION')
AND ai.posting_status = flv_acct.lookup_code(+)
AND flv_acct.lookup_type(+) = 'POSTING STATUS'
AND ai.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND ai.org_id = hou.organization_id
AND ai.posting_status <> 'Y'
AND EXISTS (SELECT 'Legacy Tax Attached'
FROM jai_ap_taxes_v jlt
WHERE jlt.invoice_id = ai.invoice_id)
-- Open Sales Orders
UNION ALL
SELECT ooh.org_id org_id
,hou.name operating_unit
,ooh.header_id trx_id
,to_char(ooh.order_number) trx_number
,ooh.ordered_date trx_date
,ott.name document_type
,flv.meaning document_status
,NULL posting_status
,oos.name document_source
,ooh.sold_to_org_id party_id
,hp.party_name party_name
,hca.account_number party_number
,ooh.ship_to_org_id party_site_id
,hs.location party_site_code
,hs.party_site_number party_site_name
,'OE_ORDER_HEADERS' entity_code
,'SALES_TRANSACTION_TAX_QUOTE' event_class_code
,'Sales Order' entity_name
FROM oe_order_headers_all ooh
,oe_transaction_types_tl ott
,oe_lookups flv
,oe_order_sources oos
,hz_cust_accounts hca
,hz_parties hp
,hr_operating_units hou
,(SELECT hcsu.site_use_id
,hcsu.location
,hcsu.cust_acct_site_id
,hps.party_site_number
FROM hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcas
,hz_party_sites hps
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id) hs
WHERE ooh.order_type_id = ott.transaction_type_id
AND ott.language = USERENV('LANG')
AND ooh.flow_status_code = flv.lookup_code(+)
AND flv.lookup_type(+) = 'FLOW_STATUS'
AND ooh.order_source_id = oos.order_source_id(+)
AND ooh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ooh.ship_to_org_id = hs.site_use_id(+)
AND ooh.org_id = hou.organization_id
AND (
-- Ship Confirmed but not invoiced
EXISTS (SELECT 'Ship Confirmed'
FROM oe_order_lines_all ool
,oe_order_headers_all oh
,wsh_delivery_details wdd
WHERE ool.header_id = ooh.header_id
AND oh.header_id = ooh.header_id
AND ool.line_id = wdd.source_line_id
AND wdd.source_code = 'OE'
AND wdd.inv_interfaced_flag = 'Y'
AND NOT EXISTS
(SELECT 'AR Invoiced'
FROM ra_customer_trx_lines_all rctl
WHERE ltrim(rtrim(rctl.interface_line_attribute1)) = to_char(oh.order_number)
AND ltrim(rtrim(rctl.interface_line_attribute6)) = to_char(ool.line_id)))
--RMA but not received or invoiced
OR EXISTS (SELECT 'RMA Not Fully Received'
FROM oe_order_lines_all ool
,oe_transaction_types_all ott
WHERE ool.line_type_id = ott.transaction_type_id
AND ott.order_category_code = 'RETURN'
AND ool.header_id = ooh.header_id
AND nvl(ool.shipped_quantity
,0) <> ool.ordered_quantity)
--
)
AND EXISTS (SELECT 'Legacy Tax Attached'
FROM jai_om_oe_so_taxes jlt
WHERE jlt.header_id = ooh.header_id)
AND ooh.org_id = &P_ORG_ID
-- Open AR Transactions
UNION ALL
SELECT rct.org_id org_id
,hou.name operating_unit
,rct.customer_trx_id trx_id
,rct.trx_number trx_number
,rct.trx_date trx_date
,rctt.name document_type
,decode(rct.complete_flag
,'N'
,'Incomplete'
,'Y'
,'Completed'
,rct.complete_flag) document_status
,'N' posting_status
,rbs.name document_source
,rct.bill_to_customer_id party_id
,hp.party_name party_name
,hca.account_number party_number
,hs.cust_acct_site_id party_site_id
,hs.location party_site_code
,hs.party_site_number party_site_number
,'TRANSACTIONS' entity_code
,DECODE(rctt.type
,'INV'
,'INVOICE'
,'CM'
,'CREDIT_MEMO'
,'DM'
,'DEBIT_MEMO'
,rctt.type) event_class_code
,'AR ' || DECODE(rctt.type
,'INV'
,'Invoice'
,'CM'
,'Credit Memo'
,'DM'
,'Debit Memo'
,rctt.type) entity_name
FROM ra_customer_trx_all rct
,ra_cust_trx_types_all rctt
,ra_batch_sources_all rbs
,hz_cust_accounts hca
,hz_parties hp
,(SELECT hcsu.site_use_id
,hcsu.location
,hcsu.cust_acct_site_id
,hps.party_site_number
FROM hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcas
,hz_party_sites hps
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id) hs
,hr_operating_units hou
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = rctt.org_id
AND rct.batch_source_id = rbs.batch_source_id
AND rct.org_id = rbs.org_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.bill_to_site_use_id = hs.site_use_id(+)
AND rct.org_id = hou.organization_id
AND NOT EXISTS (SELECT 1
FROM ra_cust_trx_line_gl_dist_all rcd
,xla_distribution_links xdl
,xla_ae_lines xal
,gl_import_references gir
WHERE rcd.customer_trx_id = rct.customer_trx_id
AND rcd.account_class = 'REV'
AND rcd.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1
AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.gl_sl_link_id = gir.gl_sl_link_id)
AND EXISTS (SELECT 'Legacy Tax Attached'
FROM jai_ar_trx_tax_lines jlt
,ra_customer_trx_lines_all rctl
WHERE jlt.Link_To_Cust_Trx_Line_Id = rctl.customer_trx_line_id
AND rctl.customer_trx_id = rct.customer_trx_id)
AND rct.org_id = &P_ORG_ID) open_trx
WHERE NOT EXISTS (SELECT 1
FROM jai_tax_det_factors jtdf
WHERE jtdf.org_id = open_trx.org_id
AND jtdf.trx_id = open_trx.trx_id
AND jtdf.entity_code = open_trx.entity_code
AND jtdf.event_class_code = open_trx.event_class_code)
ORDER BY open_trx.entity_code
,open_trx.event_class_code
,open_trx.trx_number |
