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'