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