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'
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
No comments:
Post a Comment