JA India - Open Transactions Report for Prior-GST (XML Publisher)
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Open Transactions Report for Prior-GST (XML Publisher)
Short Name: JAINPOTR_XML
DB package:
Source: India - Open Transactions Report for Prior-GST (XML Publisher)
Short Name: JAINPOTR_XML
DB package:
Run
JA India - Open Transactions Report for Prior-GST (XML Publisher) and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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 |
| Parameter Name | SQL text | Validation |
|---|---|---|
| Operating Unit |