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