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:
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
 
Number