Purchase order with Budget account query in oracle apps

SELECT   prh.SEGMENT1 "PO_Rrequisition_ #",
         Prl.LINE_NUM "PO_Rrequisition_Line_ #",
         (Prl.UNIT_PRICE * Prl.QUANTITY) "PO_Rrequisition_Amount",
         Prd.GL_ENCUMBERED_DATE "PO_Req_Encumbrance_Date",
         Prd.GL_ENCUMBERED_PERIOD_NAME "PO_Req_Period",
         Prd.ENCUMBERED_FLAG "Req_Encum_Flag",
         Prd.ENCUMBERED_AMOUNT "Req_Encum_Amount",
         glkfv.SEGMENT5 "Account",
         glkfv.CONCATENATED_SEGMENTS "Concat_Seg",
         poh.SEGMENT1 "PO_ #",
         pol.LINE_NUM "PO_Line_ #",
         (pol.UNIT_PRICE * pol.QUANTITY) "PO_Amount",
         Pod.GL_ENCUMBERED_DATE "PO_Encumbrance_Date",
         Pod.GL_ENCUMBERED_PERIOD_NAME "PO_Period",
         pol.CLOSED_DATE,
         pol.CLOSED_FLAG,
         Pod.ENCUMBERED_FLAG "PO_Encum_Flag",
         Pod.ENCUMBERED_AMOUNT "PO_Encum_Amount"
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         PO_REQ_DISTRIBUTIONS_INQ_V PORV,
         po_distributions_all pod,
         po_headers_all poh,
         po_lines_all pol,
         GL_CODE_COMBINATIONS_kfv glkfv
 WHERE       1 = 1
         AND prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND prd.DISTRIBUTION_ID = PORV.DISTRIBUTION_ID
         AND prd.distribution_id = pod.req_distribution_id(+)
         AND pol.po_line_id(+) = pod.po_line_id
         AND poh.po_header_id(+) = pol.po_header_id
        AND glkfv.CODE_COMBINATION_ID = PORV.BUDGET_ACCOUNT_ID
         AND glkfv.SEGMENT5 IN ('00110011')

exec MO_GLOBAL.INIT('PO');

Purchase order to AP Invoice Detail Query

/* Formatted on 10/26/2016 1:41:32 PM (QP5 v5.114.809.3010) */
SELECT   prh.SEGMENT1 "PO_Rrequisition_ #",
         Prl.LINE_NUM "PO_Rrequisition_Line_ #",
         (Prl.UNIT_PRICE * Prl.QUANTITY) "PO_Rrequisition_Amount",
         Prd.GL_ENCUMBERED_DATE "PO_Req_Encumbered_Date",
         Prd.GL_ENCUMBERED_PERIOD_NAME "PO_Req_Period",
         poh.SEGMENT1 "PO_ #",
         pol.LINE_NUM "PO_Line_ #",
         (pol.UNIT_PRICE * pol.QUANTITY) "PO_Amount",
         Pod.GL_ENCUMBERED_DATE "PO_Encumbered_Date",
         Pod.GL_ENCUMBERED_PERIOD_NAME "PO_Period",
         Api.INVOICE_NUM "Invoice #",
         Apl.LINE_NUMBER "Invoice_Line_ #",
         Apl.AMOUNT "Invoice_Amount",
         Apl.PERIOD_NAME "Invoice_Period",
         Api.GL_DATE "INN_Gl_Date"
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         po_distributions_all pod,
         po_headers_all poh,
         po_lines_all pol,
         ap_invoice_distributions_all apd,
         ap_invoice_lines_all apl,
         ap_invoices_all api
 WHERE       1 = 1
         AND prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND prh.SEGMENT1 in('7') -- PO Requisition #
--         And  poh.SEGMENT1 = '1083'  -- Purchase Order #
--         AND api.invoice_num = 'CRINV00930'  -- AP Invoice #
         AND prd.distribution_id = pod.req_distribution_id(+)
         AND pol.po_line_id(+) = pod.po_line_id
         AND poh.po_header_id(+) = pol.po_header_id
         AND pod.po_distribution_id = apd.PO_DISTRIBUTION_ID(+)
         AND api.invoice_id(+) = apd.invoice_id
         AND api.invoice_id = apl.invoice_id(+)

Query to get Set of Books, Operating Unit, Organization data

/* Formatted on 10/3/2016 8:38:19 PM (QP5 v5.114.809.3010) */
  SELECT   gl.set_of_books_id,
           gl.name,
           gl.short_name,
           hou.name operatin_unit,
           hou.organization_id operating_unit_id,
           org.organization_name warehouse_name,
           org.organization_id warehouse_id
    FROM   org_organization_definitions org,
           hr_operating_units hou,
           gl_sets_of_books gl
   WHERE   org.operating_unit = hou.organization_id
           AND hou.set_of_books_id = gl.set_of_books_id
ORDER BY   1, 3, 6

India Localization Queries in oracle apps

Query to find if ST form has been Issued to Vendor

/* Formatted on 10/3/2016 8:35:48 PM (QP5 v5.114.809.3010) */
SELECT   hdr.org_id,
         dtl.organization_id,
         (SELECT   DISTINCT organization_name
            FROM   org_organization_definitions
           WHERE   organization_id = dtl.organization_id)
            org_name,
         (SELECT   DISTINCT organization_code
            FROM   org_organization_definitions
           WHERE   organization_id = dtl.organization_id)
            org_code,
         (SELECT   DISTINCT fin_year
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id)
            fin_year,
         (SELECT   DISTINCT form_number
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id AND form_id = dtlf.form_id)
            form_number,
         dtlf.matched_amount form_matched_amount,
         ap.invoice_num,
         ap.invoice_currency_code,
         ap.invoice_amount,
         ap.amount_paid,
         ap.invoice_date,
         ap.invoice_received_date,
         ap.SOURCE,
         ap.doc_sequence_value,
         ap.gl_date,
         apl.period_name,
         TRUNC (rcv.transaction_date) grn_date,
         (SELECT   receipt_num
            FROM   rcv_shipment_headers
           WHERE   shipment_header_id = rcv.shipment_header_id)
            receipt_num,
         (SELECT   DISTINCT item_description
            FROM   rcv_shipment_lines
           WHERE   shipment_line_id = rcv.shipment_line_id
                   AND po_line_id = dtl.line_id)
            item_desc,
         (SELECT   DISTINCT concatenated_segments
            FROM   mtl_system_items_kfv
           WHERE   inventory_item_id = apl.inventory_item_id
                   AND organization_id = dtl.organization_id)
            Item_code,
         apl.QUANTITY_INVOICED,
         (SELECT   DISTINCT quantity_received
            FROM   rcv_shipment_lines
           WHERE   shipment_line_id = rcv.shipment_line_id
                   AND po_line_id = dtl.line_id)
            quantity_rec,
         rcv.po_unit_price,
         rcv.uom_code,
         jait.tax_name,
         jait.tax_type,
         jait.tax_id,
         dtlf.form_id,
         hdr.st_hdr_id,
         hdr.party_id,
         aps.vendor_name,
         aps.segment1 supplier_num,
         aps.vendor_type_lookup_code,
         (SELECT   vendor_site_code
            FROM   ap_supplier_sites_all
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            vendor_site_code,
         (SELECT   st_reg_no
            FROM   jai_cmn_vendor_sites
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            lst_number,
         (SELECT   cst_reg_no
            FROM   jai_cmn_vendor_sites
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            cst_number,
         (SELECT   territory_short_name
            FROM   fnd_territories_tl
           WHERE   LANGUAGE = 'US'
                   AND territory_code =
                         (SELECT   DISTINCT country
                            FROM   ap_supplier_sites_all
                           WHERE   vendor_id = hdr.party_id
                                   AND vendor_site_id = hdr.party_site_id))
            country,
         (SELECT   state
            FROM   ap_supplier_sites_all
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            state,
         dtl.po_num,
         hdr.form_type,
         TRUNC (hdr.creation_date),
         TRUNC (dtlf.creation_date) form_line_creation_date,
         dtl.header_id,
         dtl.line_id,
         dtl.location_id,
         dtl.tax_id,
         dtl.invoice_id,
         TO_CHAR ( (SELECT   SUM (tax_amount)
                      FROM   jai_po_taxes
                     WHERE       po_header_id = rcv.po_header_id
                             AND po_line_id = dtl.line_id
                             AND tax_id IN (SELECT   tax_id
                                              FROM   jai_cmn_taxes_all
                                             WHERE   tax_type IN ('CST'))),
                  '9,999,999,999.00')
            cst,
         dtl.tax_target_amount,
         dtl.matched_amount,
         NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0)
            balance_amount,
         CASE
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) >
                    0
            THEN
               'Pending'
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) =
                    0
            THEN
               'Form_Recieved'
         END
            status
  FROM   jai_cmn_stform_hdrs_all hdr,
         jai_cmn_st_form_dtls dtl,
         jai_cmn_st_match_dtls dtlf,
         jai_cmn_taxes_all jait,
         ap_invoices_all ap,
         ap_invoice_lines_all apl,
         rcv_transactions rcv,
         ap_suppliers aps
 WHERE       hdr.st_hdr_id = dtl.st_hdr_id
         AND dtl.st_dtl_id = dtlf.st_dtl_id(+)
         AND jait.tax_id = dtl.tax_id(+)
         AND dtl.invoice_id = ap.invoice_id
         AND dtl.rcv_transaction_id = rcv.transaction_id
         AND apl.invoice_id = ap.invoice_id
         AND apl.rcv_transaction_id = rcv.transaction_id
         AND aps.vendor_id = hdr.party_id
         AND dtl.issue_receipt_flag = 'I'
         AND hdr.party_type_flag = 'V'
         AND apl.discarded_flag = 'N'
         AND ap.cancelled_by IS NULL
         AND ap.cancelled_amount IS NULL
         AND dtl.tax_target_amount IS NOT NULL
         --  AND hdr.st_hdr_id = '10092'
         AND hdr.org_id = :p_org_id
         AND TRUNC (ap.invoice_date) BETWEEN NVL (
                                                TRUNC(TO_DATE (
                                                         :p_from_invoice_date,
                                                         'DD-MON-RRRR'
                                                      )),
                                                TRUNC (ap.invoice_date)
                                             )
                                         AND  NVL (
                                                 TRUNC(TO_DATE (
                                                          :p_to_invoice_date,
                                                          'DD-MON-RRRR'
                                                       )),
                                                 TRUNC (ap.invoice_date)
                                              )
         AND TRUNC (rcv.transaction_date) BETWEEN NVL (
                                                     TRUNC(TO_DATE (
                                                              :p_from_grn_date,
                                                              'DD-MON-RRRR'
                                                           )),
                                                     TRUNC (
                                                        rcv.transaction_date
                                                     )
                                                  )
                                              AND  NVL (
                                                      TRUNC(TO_DATE (
                                                               :p_to_grn_date,
                                                               'DD-MON-RRRR'
                                                            )),
                                                      TRUNC (
                                                         rcv.transaction_date
                                                      )
                                                   )
         AND aps.vendor_name BETWEEN NVL (:p_from_vendor, aps.vendor_name)
                                 AND  NVL (:p_to_vendor, aps.vendor_name)
         AND:p_status =
               CASE
                  WHEN NVL (dtl.tax_target_amount, 0)
                       - NVL (dtl.matched_amount, 0) <> 0
                  THEN
                     'Pending'
                  WHEN NVL (dtl.tax_target_amount, 0)
                       - NVL (dtl.matched_amount, 0) = 0
                  THEN
                     'Form_Recieved'
               END
UNION
SELECT   hdr.org_id,
         dtl.organization_id,
         (SELECT   DISTINCT organization_name
            FROM   org_organization_definitions
           WHERE   organization_id = dtl.organization_id)
            org_name,
         (SELECT   DISTINCT organization_code
            FROM   org_organization_definitions
           WHERE   organization_id = dtl.organization_id)
            org_code,
         (SELECT   DISTINCT fin_year
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id)
            fin_year,
         (SELECT   DISTINCT form_number
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id AND form_id = dtlf.form_id)
            form_number,
         dtlf.matched_amount form_matched_amount,
         ap.invoice_num,
         ap.invoice_currency_code,
         ap.invoice_amount,
         ap.amount_paid,
         ap.invoice_date,
         ap.invoice_received_date,
         ap.SOURCE,
         ap.doc_sequence_value,
         ap.gl_date,
         apl.period_name,
         TRUNC (rcv.transaction_date) grn_date,
         (SELECT   receipt_num
            FROM   rcv_shipment_headers
           WHERE   shipment_header_id = rcv.shipment_header_id)
            receipt_num,
         (SELECT   DISTINCT item_description
            FROM   rcv_shipment_lines
           WHERE   shipment_line_id = rcv.shipment_line_id
                   AND po_line_id = dtl.line_id)
            item_desc,
         (SELECT   DISTINCT concatenated_segments
            FROM   mtl_system_items_kfv
           WHERE   inventory_item_id = apl.inventory_item_id
                   AND organization_id = dtl.organization_id)
            Item_code,
         apl.QUANTITY_INVOICED,
         (SELECT   DISTINCT quantity_received
            FROM   rcv_shipment_lines
           WHERE   shipment_line_id = rcv.shipment_line_id
                   AND po_line_id = dtl.line_id)
            quantity_rec,
         rcv.po_unit_price,
         rcv.uom_code,
         jait.tax_name,
         jait.tax_type,
         jait.tax_id,
         dtlf.form_id,
         hdr.st_hdr_id,
         hdr.party_id,
         aps.vendor_name,
         aps.segment1 supplier_num,
         aps.vendor_type_lookup_code,
         (SELECT   vendor_site_code
            FROM   ap_supplier_sites_all
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            vendor_site_code,
         (SELECT   st_reg_no
            FROM   jai_cmn_vendor_sites
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            lst_number,
         (SELECT   cst_reg_no
            FROM   jai_cmn_vendor_sites
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            cst_number,
         (SELECT   territory_short_name
            FROM   fnd_territories_tl
           WHERE   LANGUAGE = 'US'
                   AND territory_code =
                         (SELECT   DISTINCT country
                            FROM   ap_supplier_sites_all
                           WHERE   vendor_id = hdr.party_id
                                   AND vendor_site_id = hdr.party_site_id))
            country,
         (SELECT   state
            FROM   ap_supplier_sites_all
           WHERE   vendor_id = hdr.party_id
                   AND vendor_site_id = hdr.party_site_id)
            state,
         dtl.po_num,
         hdr.form_type,
         TRUNC (hdr.creation_date),
         TRUNC (dtlf.creation_date) form_line_creation_date,
         dtl.header_id,
         dtl.line_id,
         dtl.location_id,
         dtl.tax_id,
         dtl.invoice_id,
         TO_CHAR ( (SELECT   SUM (tax_amount)
                      FROM   jai_po_taxes
                     WHERE       po_header_id = rcv.po_header_id
                             AND po_line_id = dtl.line_id
                             AND tax_id IN (SELECT   tax_id
                                              FROM   jai_cmn_taxes_all
                                             WHERE   tax_type IN ('CST'))),
                  '9,999,999,999.00')
            cst,
         dtl.tax_target_amount,
         dtl.matched_amount,
         NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0)
            balance_amount,
         CASE
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) >
                    0
            THEN
               'Pending'
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) =
                    0
            THEN
               'Form_Recieved'
         END
            status
  FROM   jai_cmn_stform_hdrs_all hdr,
         jai_cmn_st_form_dtls dtl,
         jai_cmn_st_match_dtls dtlf,
         jai_cmn_taxes_all jait,
         ap_invoices_all ap,
         ap_invoice_lines_all apl,
         rcv_transactions rcv,
         ap_suppliers aps
 WHERE       hdr.st_hdr_id = dtl.st_hdr_id
         AND dtl.st_dtl_id = dtlf.st_dtl_id(+)
         AND jait.tax_id = dtl.tax_id(+)
         AND dtl.invoice_id = ap.invoice_id
         AND dtl.rcv_transaction_id = rcv.transaction_id
         AND apl.invoice_id = ap.invoice_id
         AND apl.rcv_transaction_id = rcv.transaction_id
         AND aps.vendor_id = hdr.party_id
         AND dtl.issue_receipt_flag = 'I'
         AND hdr.party_type_flag = 'V'
         AND apl.discarded_flag = 'N'
         AND ap.cancelled_by IS NULL
         AND ap.cancelled_amount IS NULL
         AND dtl.tax_target_amount IS NOT NULL
         --  AND hdr.st_hdr_id = '10092'
         AND hdr.org_id = :p_org_id
         AND TRUNC (ap.invoice_date) BETWEEN NVL (
                                                TRUNC(TO_DATE (
                                                         :p_from_invoice_date,
                                                         'DD-MON-RRRR'
                                                      )),
                                                TRUNC (ap.invoice_date)
                                             )
                                         AND  NVL (
                                                 TRUNC(TO_DATE (
                                                          :p_to_invoice_date,
                                                          'DD-MON-RRRR'
                                                       )),
                                                 TRUNC (ap.invoice_date)
                                              )
         AND TRUNC (rcv.transaction_date) BETWEEN NVL (
                                                     TRUNC(TO_DATE (
                                                              :p_from_grn_date,
                                                              'DD-MON-RRRR'
                                                           )),
                                                     TRUNC (
                                                        rcv.transaction_date
                                                     )
                                                  )
                                              AND  NVL (
                                                      TRUNC(TO_DATE (
                                                               :p_to_grn_date,
                                                               'DD-MON-RRRR'
                                                            )),
                                                      TRUNC (
                                                         rcv.transaction_date
                                                      )
                                                   )
         AND aps.vendor_name BETWEEN NVL (:p_from_vendor, aps.vendor_name)
                                 AND  NVL (:p_to_vendor, aps.vendor_name)
         AND:p_status = 'ALL'
ORDER BY   12, 18, 32

India Localization Queries in oracle apps

 Query  to find If  ST Form is Received from Customer


/* Formatted on 10/3/2016 8:33:50 PM (QP5 v5.114.809.3010) */
SELECT   hdr.org_id,
         dtl.organization_id,
         (SELECT   DISTINCT Organization_Name
            FROM   Org_Organization_Definitions
           WHERE   Organization_Id = Dtl.Organization_Id)
            Org_name,
         (SELECT   DISTINCT Organization_code
            FROM   Org_Organization_Definitions
           WHERE   Organization_Id = Dtl.Organization_Id)
            Org_code,
         (SELECT   DISTINCT fin_year
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id)
            fin_year,
         (SELECT   DISTINCT form_number
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id AND form_id = dtlf.form_id)
            form_number,
         jait.tax_name,
         jait.tax_type,
         jait.tax_id,
         dtlf.form_id,
         hdr.st_hdr_id,
         hdr.party_id,
         (SELECT   DISTINCT party_name
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            cust_name,
         (SELECT   DISTINCT account_number
            FROM   hz_cust_accounts
           WHERE   cust_account_id = hdr.party_id)
            customer_num,
         (SELECT   DISTINCT hl.address1
            FROM   apps.hz_parties hp,
                   apps.hz_party_sites hps,
                   apps.hz_locations hl,
                   apps.hz_cust_accounts_all hca,
                   apps.hz_cust_acct_sites_all hcsa,
                   apps.hz_cust_site_uses_all hcsu
           WHERE       hp.party_id = hps.party_id
                   AND hps.location_id = hl.location_id
                   AND hp.party_id = hca.party_id
                   AND hcsa.party_site_id = hps.party_site_id
                   AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
                   AND hca.cust_account_id = hcsa.cust_account_id
                   AND hcsu.site_use_id = rac.SHIP_TO_SITE_USE_ID
                   AND hcsa.cust_account_id = hdr.party_id)
            ship_site,
         (SELECT   DISTINCT
                      hl.address2
                   || '-'
                   || hl.address3
                   || '-'
                   || hl.city
                   || ','
                   || hl.state
                   || '-'
                   || hl.country
            FROM   apps.hz_parties hp,
                   apps.hz_party_sites hps,
                   apps.hz_locations hl,
                   apps.hz_cust_accounts_all hca,
                   apps.hz_cust_acct_sites_all hcsa,
                   apps.hz_cust_site_uses_all hcsu
           WHERE       hp.party_id = hps.party_id
                   AND hps.location_id = hl.location_id
                   AND hp.party_id = hca.party_id
                   AND hcsa.party_site_id = hps.party_site_id
                   AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
                   AND hca.cust_account_id = hcsa.cust_account_id
                   AND hcsu.site_use_id = rac.SHIP_TO_SITE_USE_ID
                   AND hcsa.cust_account_id = hdr.party_id)
            ship_to_add,
         (SELECT   DISTINCT LOCATION
            FROM   hz_cust_site_uses_all
           WHERE   cust_acct_site_id IN
                         (SELECT   cust_acct_site_id
                            FROM   hz_cust_acct_sites_all
                           WHERE   cust_account_id = hdr.party_id)
                   AND site_use_id = hdr.party_site_id)
            cust_site,
         (SELECT   territory_short_name
            FROM   fnd_territories_tl
           WHERE   LANGUAGE = 'US'
                   AND territory_code =
                         (SELECT   country
                            FROM   hz_parties
                           WHERE   party_id =
                                      (SELECT   party_id
                                         FROM   hz_cust_accounts
                                        WHERE   cust_account_id =
                                                   hdr.party_id)))
            country,
         (SELECT   state
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            state,
         (SELECT   city
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            city,
         (SELECT   postal_code
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            postal_code,
         (SELECT   DISTINCT cust_po_number
            FROM   oe_order_headers_all
           WHERE   header_id = dtl.header_id)
            cust_po_number,
         (SELECT   DISTINCT attribute9
            FROM   oe_order_headers_all
           WHERE   header_id = dtl.header_id)
            trade,
         hdr.party_site_id,
         hdr.form_type,
         hdr.party_type_flag,
         -------------------C Fro Customer And V For Vendor,
         TRUNC (hdr.creation_date),
         TRUNC (dtlf.creation_date) form_line_creation_date,
         dtl.header_id,
         -----------So Header_Id
         dtl.line_id,                                 --------Invoice Liine Id
         TRUNC (rac.creation_date) invoice_creation_date,
         rac.line_number,
         (SELECT   DISTINCT concatenated_segments
            FROM   mtl_system_items_kfv
           WHERE   inventory_item_id = rac.inventory_item_id
                   AND organization_id = dtl.organization_id)
            Item_code,
         rac.description,
         rac.quantity_ordered,
         rac.quantity_credited,
         rac.quantity_invoiced,
         rac.unit_selling_price,
         rac.sales_order_date,
         rac.attribute13,
         rac.attribute14,
         rac.attribute15,
         rac.uom_code,
         dtl.location_id,
         dtl.tax_id,
         dtl.invoice_id,
         dtl.issue_receipt_flag,
         -----R For Reciept And I For Issue
         dtl.order_flag,
         dtl.order_number,
         dtl.trx_number,
         dtl.tax_target_amount,
         dtl.matched_amount,
         NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0)
            balance_amount,
         CASE
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) >
                    0
            THEN
               'Pending'
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) =
                    0
            THEN
               'Form_Recieved'
         END
            status,
         rac.customer_trx_line_id,
         rac.revenue_amount,
         rac.extended_amount,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN (SELECT   tax_id
                                       FROM   jai_cmn_taxes_all
                                      WHERE   tax_type IN ('CST'))),
            '9,999,999,999.00'
         )
            cst,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN
                               (SELECT   tax_id
                                  FROM   jai_cmn_taxes_all
                                 WHERE   tax_type IN
                                               ('Excise',
                                                'EXCISE_EDUCATION_CESS',
                                                'EXCISE_SH_EDU_CESS'))),
            '9,999,999,999.00'
         )
            excise,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN
                               (SELECT   tax_id
                                  FROM   jai_cmn_taxes_all
                                 WHERE   tax_type IN
                                               ('Service',
                                                'SERVICE_EDUCATION_CESS',
                                                'SERVICE_SH_EDU_CESS'))),
            '9,999,999,999.00'
         )
            service
  FROM   jai_cmn_stform_hdrs_all hdr,
         jai_cmn_st_form_dtls dtl,
         jai_cmn_st_match_dtls dtlf,
         jai_cmn_taxes_all jait,
         ra_customer_trx_lines_all rac
 WHERE       hdr.st_hdr_id = dtl.st_hdr_id
         AND dtl.st_dtl_id = dtlf.st_dtl_id(+)
         AND jait.tax_id = dtl.tax_id(+)
         AND dtl.line_id = rac.customer_trx_line_id
         AND dtl.issue_receipt_flag = 'R'
         AND hdr.party_type_flag = 'C'
         --  AND hdr.st_hdr_id = '10165'
         AND hdr.org_id = :p_org_id
         AND TRUNC (rac.creation_date) BETWEEN NVL (
                                                  TRUNC(TO_DATE (
                                                           :p_from_invoice_date,
                                                           'DD-MON-RRRR'
                                                        )),
                                                  TRUNC (rac.creation_date)
                                               )
                                           AND  NVL (
                                                   TRUNC(TO_DATE (
                                                            :p_to_invoice_date,
                                                            'DD-MON-RRRR'
                                                         )),
                                                   TRUNC (rac.creation_date)
                                                )
         AND (SELECT   DISTINCT account_number
                FROM   hz_cust_accounts
               WHERE   cust_account_id = hdr.party_id) BETWEEN NVL (
                                                                  :p_from_customer,
                                                                  (SELECT   DISTINCT
                                                                            account_number
                                                                     FROM   hz_cust_accounts
                                                                    WHERE   cust_account_id =
                                                                               hdr.party_id)
                                                               )
                                                           AND  NVL (
                                                                   :p_to_customer,
                                                                   (SELECT   DISTINCT
                                                                             account_number
                                                                      FROM   hz_cust_accounts
                                                                     WHERE   cust_account_id =
                                                                                hdr.party_id)
                                                                )
         AND CASE
               WHEN NVL (dtl.tax_target_amount, 0)
                    - NVL (dtl.matched_amount, 0) <> 0
               THEN
                  'Pending'
               WHEN NVL (dtl.tax_target_amount, 0)
                    - NVL (dtl.matched_amount, 0) = 0
               THEN
                  'Form Recieved'
            END = :p_status
UNION
SELECT   hdr.org_id,
         dtl.organization_id,
         (SELECT   DISTINCT Organization_Name
            FROM   Org_Organization_Definitions
           WHERE   Organization_Id = Dtl.Organization_Id)
            Org_name,
         (SELECT   DISTINCT Organization_code
            FROM   Org_Organization_Definitions
           WHERE   Organization_Id = Dtl.Organization_Id)
            Org_code,
         (SELECT   DISTINCT fin_year
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id)
            fin_year,
         (SELECT   DISTINCT form_number
            FROM   jai_cmn_st_forms
           WHERE   st_hdr_id = hdr.st_hdr_id AND form_id = dtlf.form_id)
            form_number,
         jait.tax_name,
         jait.tax_type,
         jait.tax_id,
         dtlf.form_id,
         hdr.st_hdr_id,
         hdr.party_id,
         (SELECT   DISTINCT party_name
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            cust_name,
         (SELECT   DISTINCT account_number
            FROM   hz_cust_accounts
           WHERE   cust_account_id = hdr.party_id)
            customer_num,
         (SELECT   DISTINCT hl.address1
            FROM   apps.hz_parties hp,
                   apps.hz_party_sites hps,
                   apps.hz_locations hl,
                   apps.hz_cust_accounts_all hca,
                   apps.hz_cust_acct_sites_all hcsa,
                   apps.hz_cust_site_uses_all hcsu
           WHERE       hp.party_id = hps.party_id
                   AND hps.location_id = hl.location_id
                   AND hp.party_id = hca.party_id
                   AND hcsa.party_site_id = hps.party_site_id
                   AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
                   AND hca.cust_account_id = hcsa.cust_account_id
                   AND hcsu.site_use_id = rac.SHIP_TO_SITE_USE_ID
                   AND hcsa.cust_account_id = hdr.party_id)
            ship_site,
         (SELECT   DISTINCT
                      hl.address2
                   || '-'
                   || hl.address3
                   || '-'
                   || hl.city
                   || ','
                   || hl.state
                   || '-'
                   || hl.country
            FROM   apps.hz_parties hp,
                   apps.hz_party_sites hps,
                   apps.hz_locations hl,
                   apps.hz_cust_accounts_all hca,
                   apps.hz_cust_acct_sites_all hcsa,
                   apps.hz_cust_site_uses_all hcsu
           WHERE       hp.party_id = hps.party_id
                   AND hps.location_id = hl.location_id
                   AND hp.party_id = hca.party_id
                   AND hcsa.party_site_id = hps.party_site_id
                   AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
                   AND hca.cust_account_id = hcsa.cust_account_id
                   AND hcsu.site_use_id = rac.SHIP_TO_SITE_USE_ID
                   AND hcsa.cust_account_id = hdr.party_id)
            ship_to_add,
         (SELECT   DISTINCT LOCATION
            FROM   hz_cust_site_uses_all
           WHERE   cust_acct_site_id IN
                         (SELECT   cust_acct_site_id
                            FROM   hz_cust_acct_sites_all
                           WHERE   cust_account_id = hdr.party_id)
                   AND site_use_id = hdr.party_site_id)
            cust_site,
         (SELECT   territory_short_name
            FROM   fnd_territories_tl
           WHERE   LANGUAGE = 'US'
                   AND territory_code =
                         (SELECT   country
                            FROM   hz_parties
                           WHERE   party_id =
                                      (SELECT   party_id
                                         FROM   hz_cust_accounts
                                        WHERE   cust_account_id =
                                                   hdr.party_id)))
            country,
         (SELECT   state
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            state,
         (SELECT   city
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            city,
         (SELECT   postal_code
            FROM   hz_parties
           WHERE   party_id = (SELECT   party_id
                                 FROM   hz_cust_accounts
                                WHERE   cust_account_id = hdr.party_id))
            postal_code,
         (SELECT   DISTINCT cust_po_number
            FROM   oe_order_headers_all
           WHERE   header_id = dtl.header_id)
            cust_po_number,
         (SELECT   DISTINCT attribute9
            FROM   oe_order_headers_all
           WHERE   header_id = dtl.header_id)
            trade,
         hdr.party_site_id,
         hdr.form_type,
         hdr.party_type_flag,
         -------------------C Fro Customer And V For Vendor,
         TRUNC (hdr.creation_date),
         TRUNC (dtlf.creation_date) form_line_creation_date,
         dtl.header_id,
         -----------So Header_Id
         dtl.line_id,                                 --------Invoice Liine Id
         TRUNC (rac.creation_date) invoice_creation_date,
         rac.line_number,
         (SELECT   DISTINCT concatenated_segments
            FROM   mtl_system_items_kfv
           WHERE   inventory_item_id = rac.inventory_item_id
                   AND organization_id = dtl.organization_id)
            Item_code,
         rac.description,
         rac.quantity_ordered,
         rac.quantity_credited,
         rac.quantity_invoiced,
         rac.unit_selling_price,
         rac.sales_order_date,
         rac.attribute13,
         rac.attribute14,
         rac.attribute15,
         rac.uom_code,
         dtl.location_id,
         dtl.tax_id,
         dtl.invoice_id,
         dtl.issue_receipt_flag,
         -----R For Reciept And I For Issue
         dtl.order_flag,
         dtl.order_number,
         dtl.trx_number,
         dtl.tax_target_amount,
         dtl.matched_amount,
         NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0)
            balance_amount,
         CASE
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) >
                    0
            THEN
               'Pending'
            WHEN NVL (dtl.tax_target_amount, 0) - NVL (dtl.matched_amount, 0) =
                    0
            THEN
               'Form_Recieved'
         END
            status,
         rac.customer_trx_line_id,
         rac.revenue_amount,
         rac.extended_amount,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN (SELECT   tax_id
                                       FROM   jai_cmn_taxes_all
                                      WHERE   tax_type IN ('CST'))),
            '9,999,999,999.00'
         )
            cst,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN
                               (SELECT   tax_id
                                  FROM   jai_cmn_taxes_all
                                 WHERE   tax_type IN
                                               ('Excise',
                                                'EXCISE_EDUCATION_CESS',
                                                'EXCISE_SH_EDU_CESS'))),
            '9,999,999,999.00'
         )
            excise,
         TO_CHAR (
            (SELECT   SUM (tax_amount)
               FROM   jai_ar_trx_tax_lines
              WHERE   link_to_cust_trx_line_id = rac.customer_trx_line_id
                      AND tax_id IN
                               (SELECT   tax_id
                                  FROM   jai_cmn_taxes_all
                                 WHERE   tax_type IN
                                               ('Service',
                                                'SERVICE_EDUCATION_CESS',
                                                'SERVICE_SH_EDU_CESS'))),
            '9,999,999,999.00'
         )
            service
  FROM   jai_cmn_stform_hdrs_all hdr,
         jai_cmn_st_form_dtls dtl,
         jai_cmn_st_match_dtls dtlf,
         jai_cmn_taxes_all jait,
         ra_customer_trx_lines_all rac
 WHERE       hdr.st_hdr_id = dtl.st_hdr_id
         AND dtl.st_dtl_id = dtlf.st_dtl_id(+)
         AND jait.tax_id = dtl.tax_id(+)
         AND dtl.line_id = rac.customer_trx_line_id
         AND dtl.issue_receipt_flag = 'R'
         AND hdr.party_type_flag = 'C'
         --  AND hdr.st_hdr_id = '10165'
         AND hdr.org_id = :p_org_id
         AND TRUNC (rac.creation_date) BETWEEN NVL (
                                                  TRUNC(TO_DATE (
                                                           :p_from_invoice_date,
                                                           'DD-MON-RRRR'
                                                        )),
                                                  TRUNC (rac.creation_date)
                                               )
                                           AND  NVL (
                                                   TRUNC(TO_DATE (
                                                            :p_to_invoice_date,
                                                            'DD-MON-RRRR'
                                                         )),
                                                   TRUNC (rac.creation_date)
                                                )
         AND (SELECT   DISTINCT account_number
                FROM   hz_cust_accounts
               WHERE   cust_account_id = hdr.party_id) BETWEEN NVL (
                                                                  :p_from_customer,
                                                                  (SELECT   DISTINCT
                                                                            account_number
                                                                     FROM   hz_cust_accounts
                                                                    WHERE   cust_account_id =
                                                                               hdr.party_id)
                                                               )
                                                           AND  NVL (
                                                                   :p_to_customer,
                                                                   (SELECT   DISTINCT
                                                                             account_number
                                                                      FROM   hz_cust_accounts
                                                                     WHERE   cust_account_id =
                                                                                hdr.party_id)
                                                                )
         AND:p_status = 'ALL'