Query for PLA Register in India Localization

SELECT
pl.organization_id,
(select distinct organization_name from org_organization_definitions
where organization_id=pl.organization_id) Org_name,
(select distinct organization_code from org_organization_definitions
where organization_id=pl.organization_id) Org_code,
pl.register_id,
pl.fin_year,
pl.slno,
pl.charge_account_id,
pl.ref_document_id,
trunc(pl.ref_document_date),
nvl(pl.dr_invoice_date,pl.tr6_challan_date) Invoice_Date,
nvl(pl.dr_invoice_no,pl.tr6_challan_no) Invoice_num,
pl.location_id,
trunc(pl.entry_date),
pl.inventory_item_id,
pl.vendor_cust_flag,
pl.vendor_id,
decode (pl.vendor_cust_flag,'C',(select party_name from hz_parties where party_id=
                                (select party_id from hz_cust_accounts where cust_account_id =pl.vendor_id))
                           ,'V',(select vendor_name from po_vendors where vendor_id=pl.vendor_id)) Customer_Vendor_name,
decode (pl.vendor_cust_flag,'C',(select address3 || ' - ' || city from hz_locations where location_id =
                                (select location_id from hz_party_sites  where party_site_id=
                                (select party_site_id from hz_cust_acct_sites_all  where cust_acct_site_id =
                                (select cust_acct_site_id from hz_cust_site_uses_all where site_use_id=14474))))
                           ,'V',(select vendor_site_code || ' - ' || state from ap_supplier_sites_all
                                 where vendor_id=pl.vendor_id
                                 and vendor_site_id=pl.vendor_site_id)) Site_Name,
pl.vendor_site_id,
pl.excise_invoice_no,
trunc(pl.transaction_date),
pl.transaction_source_num,
(pl.cr_basic_ed + pl.cr_other_ed) cr_Bed,
pl.cr_additional_ed,
 (SELECT sum(credit)
                       FROM jai_cmn_rg_others jac                                                
                      WHERE jac.source_register_id = pl.register_id                                                                  
                        AND jac.tax_type in( 'EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')
                       ) cr_edu,
(SELECT sum(credit)
              FROM jai_cmn_rg_others jac                                                
             WHERE jac.source_register_id = pl.register_id                                                                  
               AND jac.tax_type in ('EXCISE_SH_EDU_CESS','CVD_SH_EDU_CESS')
                       )   cr_she_cess ,
(pl.dr_basic_ed + dr_other_ed) Dr_Bed,
pl.dr_additional_ed,
 (SELECT sum(debit)
                       FROM jai_cmn_rg_others jac                                                
                      WHERE jac.source_register_id = pl.register_id                                                                  
                        AND jac.tax_type in( 'EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS')
                       ) dr_edu,
 (SELECT sum(debit)
                       FROM jai_cmn_rg_others jac                                                
                      WHERE jac.source_register_id = pl.register_id                                                                  
                        AND jac.tax_type in ('EXCISE_SH_EDU_CESS','CVD_SH_EDU_CESS')
                       )   dr_she_cess ,
pl.other_tax_credit,
pl.other_tax_debit,
wda.delivery_id,
wda.delivery_detail_id,
wdd.item_description,
(select concatenated_segments from mtl_system_items_kfv
where inventory_item_id=wdd.inventory_item_id
and organization_id=pl.organization_id) item_code,
wdd.shipped_quantity,
wdd.unit_price,
wdd.source_header_number,
wdd.requested_quantity_uom,
 (select distinct doc_sequence_value from  GL_JE_HEADERS
          where je_header_id in  (select distinct je_header_id FROM gl_je_lines
                                  where reference_5=to_char(wda.delivery_id)
                                  and CODE_COMBINATION_ID=pl.CHARGE_ACCOUNT_ID)) GL_DOC_SEQ,
         (select distinct default_effective_date from  GL_JE_HEADERS
          where je_header_id in (select distinct je_header_id FROM gl_je_lines
                                  where reference_5=to_char(wda.delivery_id)
                                  and CODE_COMBINATION_ID=pl.CHARGE_ACCOUNT_ID)) GL_Date
from jai_cmn_rg_pla_trxs pl,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
where  pl.ref_document_id=wda.delivery_detail_id(+)
and wdd.delivery_detail_id(+)=wda.delivery_detail_id
and pl.organization_id =:organizaion_id
and pl.location_id=nvl(:location_id,pl.location_id)
and TRUNC (pl.transaction_date) BETWEEN TRUNC (NVL (TO_DATE (:p_from_date),
                                                       pl.transaction_date
                                                      )
                                                 )
                                       AND TRUNC (NVL (TO_DATE (:p_to_date),
                                                       pl.transaction_date
                                                      ))
ORDER BY pl.slno

India Localization Basic Tax Structure

­­­India Localization

Tax: Tax is basically of two types.

1.     Direct  Tax

2.     Indirect Tax

1. Direct Tax – We pay directly to the government. For e.g. Income Tax

2. Indirect Tax - We don’t pay directly to the government but we have to pay indirectly like Excise, VAT, TDS, WCT, and Service Tax, Cess, Shecess.

Indirect Tax is also divided into two parts:

1.     Claimable

2.     Non Claimable

Service Tax:

LST (Local Sale Tax) – Not Claimable

VAT (Value Added Tax) – Claimable

Claim Process – Paid/Receive

Capital Item – Not consume

The two types of Industries are:

1.     Goods Industry

a.     Manufacturing -- Excise

b.     Trading – VAT(Value Added Tax),LST(Local Sales Tax),CST(Central Sales Tax)

2.     Service industry

In oracle we maintain several excise registers:

RG 23 A	Part 1(Quantity)	Raw Material
	Part 2  (Value)
RG 23 C	Part 1(Quantity)	Capital Items
	Part 2  (Value)
RG 1	Quantity	Finish Goods
RG 23 D		Only for Trading
PLA(Personal Ledger Account)		Negative value is fill in PLA

Slab of Excise Duty

1.     Basic Excise   : -  10% (Total Value)

2.     Cess                :-    2% (On Basic Excise)

3.     Edu. Cess       :-    1% (On Basic Excise)

            Total               : - 10.30%

Suppose: Purchase or Sale of Rs. 100

Slab of Excise as follows:

Basic Excise Duty     = 10 (10% of Rs.100)

Cess                              =   2 (2% of Rs. 10)

Edu. Cess                    =   1 (1% of Rs. 10)

Total Tax                     = 10.30 

India Localization Most Usable Queries for Report Purpose

1)-----Query for RG23 A/C Part by Vendor Type as Either Manufacturer /Dealer or Importer  .



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)








2)----------Query  to find If  ST Form is Received from Customer


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'

3)-------Query to find if ST form has been Issued to Vendor

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