India Localization Queries in oracle apps

/* Formatted on 10/3/2016 8:31:52 PM (QP5 v5.114.809.3010) */
  SELECT   jai.register_id,
           jai.slno,
           jai.register_id_part_i,
           jai.receipt_ref,
           jai.remarks,
           jai.location_id,
           jai.register_type,
           TRUNC (jai.transaction_date),
           org.organization_name,
           org.organization_code,
           (SELECT   segment1 || '-' || segment2
              FROM   mtl_system_items
             WHERE   inventory_item_id = jai.inventory_item_id
                     AND organization_id = jai.organization_id)
              ITEM_CODE,
           (SELECT   DESCRIPTION
              FROM   mtl_system_items
             WHERE   inventory_item_id = jai.inventory_item_id
                     AND organization_id = jai.organization_id)
              ITEM_CDESC,
           (SELECT   DISTINCT jat.attribute_value
              FROM   jai_rgm_itm_regns jar, jai_rgm_itm_tmpl_attrs jat
             WHERE       jar.rgm_item_regns_id = jat.rgm_item_regns_id
                     AND jat.attribute_code = 'ITEM TARIFF'
                     AND jar.inventory_item_id = jai.inventory_item_id
                     AND jar.organization_id = jai.organization_id)
              tarrif_num,
           --  jah.receipt_num,
           jai.fin_year,
           jai.inventory_item_id,
           jai.organization_id,
           TRUNC (jai.receipt_date),
           NVL (jai.excise_invoice_no, REFERENCE_NUM) excise_invoice_no,
           jai.excise_invoice_date,
           CASE
              WHEN jai.vendor_id IS NULL
              THEN
                 DECODE (jai.cr_basic_ed,
                         0,
                         jai.cr_additional_ed,
                         '',
                         jai.cr_additional_ed,
                         jai.cr_basic_ed,
                         jai.cr_basic_ed)
           END
              AS IMP_BE_BED,              -----FOR BILL OF ENTRY TYP EOF ENTRY
           jai.cr_additional_cvd IMP_BE_AED,
           CASE
              WHEN jav.vendor_type = 'Manufacturer'
                   OR jav.vendor_type IS NULL
                     AND jai.vendor_id NOT IN (-262, -1175)
              THEN
                 (SELECT   DISTINCT DECODE (jai1.cr_basic_ed,
                                            '',
                                            jai1.cr_additional_ed,
                                            0,
                                            jai1.cr_additional_ed,
                                            jai1.cr_basic_ed,
                                            jai1.cr_basic_ed)
                    FROM   jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jav1.vendor_id = jai.vendor_id
                           AND jav1.vendor_site_id = jai.vendor_site_id
                           AND jai1.register_type = jai.register_type
                           AND jai.dr_basic_ed IS NULL
                           AND (jav1.vendor_type = 'Manufacturer'
                                OR jav1.vendor_type IS NULL))
              WHEN jai.vendor_id IN (-262, -1175)
              THEN
                 jai.cr_basic_ed -----------------DORF KETAL SPECIALITY CATALYST PVT LTD.
           END
              AS man_bed,
           CASE
              WHEN jav.vendor_type = 'Manufacturer' OR jav.vendor_type IS NULL
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND (jav1.vendor_type = 'Manufacturer'
                                OR jav1.vendor_type IS NULL)
                           AND jac.tax_type = 'EXCISE_EDUCATION_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS man_edu,
           CASE
              WHEN jav.vendor_type = 'Manufacturer'
                   OR jav.vendor_type IS NULL AND jai.vendor_id <> -262
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND (jav1.vendor_type = 'Manufacturer'
                                OR jav1.vendor_type IS NULL)
                           AND jac.tax_type = 'EXCISE_SH_EDU_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS man_sec,
           CASE
              WHEN jav.vendor_type IN
                         ('First Stage Dealer', 'Second Stage Dealer')
              THEN
                 (SELECT   DISTINCT DECODE (jai1.cr_basic_ed,
                                            0,
                                            jai1.cr_additional_ed,
                                            jai1.cr_basic_ed,
                                            jai1.cr_basic_ed)
                    FROM   jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jav1.vendor_id = jai.vendor_id
                           AND jav1.vendor_site_id = jai.vendor_site_id
                           AND jai1.register_type = jai.register_type
                           AND jai.dr_basic_ed IS NULL
                           AND jav1.vendor_type IN
                                    ('First Stage Dealer',
                                     'Second Stage Dealer'))
           END
              AS trader_bed,
           CASE
              WHEN jav.vendor_type IN
                         ('First Stage Dealer', 'Second Stage Dealer')
              THEN
                 (SELECT   DISTINCT jai1.cr_additional_ed
                    FROM   jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jav1.vendor_id = jai.vendor_id
                           AND jav1.vendor_site_id = jai.vendor_site_id
                           AND jai1.register_type = jai.register_type
                           AND jai.dr_basic_ed IS NULL
                           AND jav1.vendor_type IN
                                    ('First Stage Dealer',
                                     'Second Stage Dealer'))
           END
              AS trader_ad_cvd,
           CASE
              WHEN jav.vendor_type IN
                         ('First Stage Dealer', 'Second Stage Dealer')
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND jav.vendor_type IN
                                    ('First Stage Dealer',
                                     'Second Stage Dealer')
                           AND jac.tax_type = 'EXCISE_EDUCATION_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS trader_edu,
           CASE
              WHEN jav.vendor_type IN
                         ('First Stage Dealer', 'Second Stage Dealer')
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND jav.vendor_type IN
                                    ('First Stage Dealer',
                                     'Second Stage Dealer')
                           AND jac.tax_type = 'EXCISE_SH_EDU_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS trader_sec,
           CASE
              WHEN jav.vendor_type = 'Importer'
              THEN
                 (SELECT   DISTINCT DECODE (jai1.cr_basic_ed,
                                            '',
                                            jai1.cr_additional_ed,
                                            0,
                                            jai1.cr_additional_ed,
                                            jai1.cr_basic_ed,
                                            jai1.cr_basic_ed)
                    FROM   jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jav1.vendor_id = jai.vendor_id
                           AND jav1.vendor_site_id = jai.vendor_site_id
                           AND jai1.register_type = jai.register_type
                           AND jai.dr_basic_ed IS NULL
                           AND jav1.vendor_type = 'Importer')
           END
              AS import_bde,
           CASE
              WHEN jav.vendor_type = 'Importer'
              THEN
                 (SELECT   DISTINCT jai1.cr_additional_cvd
                    FROM   jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jav1.vendor_id = jai.vendor_id
                           AND jav1.vendor_site_id = jai.vendor_site_id
                           AND jai1.register_type = jai.register_type
                           AND jai.dr_basic_ed IS NULL
                           AND jav1.vendor_type = 'Importer')
           END
              AS import_ad_cvd,
           CASE
              WHEN jav.vendor_type = 'Importer'
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND jav.vendor_type = 'Importer'
                           AND jac.tax_type = 'EXCISE_EDUCATION_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS import_edu,
           CASE
              WHEN jav.vendor_type = 'Importer'
              THEN
                 (SELECT   credit
                    FROM   jai_cmn_rg_others jac,
                           jai_cmn_rg_23ac_ii_trxs jai1,
                           jai_cmn_vendor_sites jav1
                   WHERE       jac.source_register_id = jai1.register_id
                           AND jai1.vendor_id = jav1.vendor_id(+)
                           AND jai1.vendor_site_id = jav1.vendor_site_id(+)
                           AND jai1.register_id = jai.register_id
                           AND jai1.register_type = jai.register_type
                           AND jav.vendor_type = 'Importer'
                           AND jac.tax_type = 'EXCISE_SH_EDU_CESS'
                           AND source_register = DECODE (jai.register_type,
                                                         'A',
                                                         'RG23A_P2',
                                                         'C',
                                                         'RG23C_P2'))
           END
              AS import_sec,
           NVL (JAI.DR_BASIC_ED, DR_ADDITIONAL_ED) DR_BASIC_ED,
           --   JAI.DR_BASIC_ED,
           (SELECT   DISTINCT JAC.DEBIT
              FROM   jai_cmn_rg_others jac
             WHERE       jac.source_register_id = jai.register_id
                     AND jac.tax_type = 'EXCISE_EDUCATION_CESS'
                     AND source_register = DECODE (jai.register_type,
                                                   'A',
                                                   'RG23A_P2',
                                                   'C',
                                                   'RG23C_P2'))
              DR_EDU,
           (SELECT   DISTINCT JAC.DEBIT
              FROM   jai_cmn_rg_others jac
             WHERE   jac.source_register_id = jai.register_id
                     AND jac.tax_type IN
                              ('EXCISE_SH_EDU_CESS', 'CVD_EDUCATION_CESS')
                     AND source_register = DECODE (jai.register_type,
                                                   'A',
                                                   'RG23A_P2',
                                                   'C',
                                                   'RG23C_P2'))
              DR_SEC_EDU,
           --  jai.cr_additional_ed, jai.cr_other_ed, jai.cr_additional_cvd,
           (SELECT   credit
              FROM   jai_cmn_rg_others
             WHERE       tax_type = 'CVD_EDUCATION_CESS'
                     AND source_register = DECODE (jai.register_type,
                                                   'A',
                                                   'RG23A_P2',
                                                   'C',
                                                   'RG23C_P2')
                     AND source_register_id = jai.register_id)
              cvd_education_cess,
           (SELECT   credit
              FROM   jai_cmn_rg_others
             WHERE       tax_type = 'CVD_SH_EDU_CESS'
                     AND source_register = DECODE (jai.register_type,
                                                   'A',
                                                   'RG23A_P2',
                                                   'C',
                                                   'RG23C_P2')
                     AND source_register_id = jai.register_id)
              cvd_sh_edu_cess,
           jav.vendor_type,
           jai.vendor_id,
           jai.vendor_site_id,
           CASE
              WHEN jai.vendor_id IS NULL
                   AND NVL (jai.excise_invoice_no, REFERENCE_NUM) LIKE 'BE%'
              THEN
                 'Taloja Bonded warehouse'
              WHEN jai.vendor_id IN (-262, -1175)
              THEN
                 'DKSC MUNDRA PROCESS  WAREHOUSE'
              WHEN jai.vendor_id NOT IN (-262, -1175)
              THEN
                 NVL (
                    (SELECT   DISTINCT hz.party_name
                       FROM   hz_parties hz, hz_cust_accounts hca
                      WHERE   hz.party_id = hca.party_id
                              AND hca.cust_account_id = jai.CUSTOMER_ID),
                    (SELECT   DISTINCT vendor_name
                       FROM   po_vendors
                      WHERE   vendor_id = jai.vendor_id)
                 )
           END
              AS vendor_name,
           (SELECT   DISTINCT segment1
              FROM   po_vendors
             WHERE   vendor_id = jai.vendor_id)
              vendor_number
    FROM   jai_cmn_rg_23ac_ii_trxs jai,
           -- jai_rcv_headers jah,
           jai_cmn_vendor_sites jav,
           org_organization_definitions org
   WHERE       jai.vendor_id = jav.vendor_id(+)
           AND jai.vendor_site_id = jav.vendor_site_id(+)
           --  AND jah.excise_invoice_no = jai.excise_invoice_no
           --   and JAI.REGISTER_ID=21282
           --   AND jai.dr_basic_ed IS NULL
           --    AND jai.register_id_part_i=74208
           AND org.organization_id = jai.organization_id
           AND jai.register_type = :p_register_type
           AND jai.organization_id =
                 NVL (:p_organization_id, jai.organization_id)
           AND jai.location_id = NVL (:p_location_id, jai.location_id)
           AND TRUNC (jai.creation_date) BETWEEN NVL (
                                                    TRUNC(TO_DATE (
                                                             :p_from_date,
                                                             'DD-MON-RRRR'
                                                          )),
                                                    TRUNC (jai.creation_date)
                                                 )
                                             AND  NVL (
                                                     TRUNC(TO_DATE (
                                                              :p_to_date,
                                                              'DD-MON-RRRR'
                                                           )),
                                                     TRUNC (jai.creation_date)
                                                  )
ORDER BY   jai.remarks, jai.slno, TRUNC (jai.transaction_date)