SELECT
hou.name, pla.creation_date,
loc.description location_name,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
hr_org.excise_duty_comm,
hr_org.excise_duty_division,
TO_CHAR(pla.tr6_challan_date, 'DD-MON-YYYY') tr6_challan_date ,
hr_org.excise_duty_range,
hr_org.ec_code,
hr_org.excise_duty_circle,
pla.register_id,
pla.slno,
nvl(pla.inventory_item_id,0) inventory_item_id,
pla.vendor_cust_flag vflag,
pla.vendor_id,
pla.vendor_site_id ,
pla.closing_balance,
TRANSACTION_SOURCE_NUM transaction_id,
DECODE( TRANSACTION_SOURCE_NUM, 91, substr(tr6_challan_no,1,10), dr_invoice_no) Doc_No,
DECODE( TRANSACTION_SOURCE_NUM, 91, tr6_challan_date, dr_invoice_date) Doc_Date,
ROUND(NVL(cr_basic_ed,0),2) cr_basic_ed,
ROUND(NVL(dr_basic_ed,0),2) dr_basic_ed,
ROUND(NVL(cr_additional_ed,0),2) cr_additional_ed,
ROUND(NVL(dr_additional_ed,0),2) dr_additional_ed,
ROUND(NVL(cr_other_ed,0),2) cr_other_ed,
ROUND(NVL(dr_other_ed,0),2) dr_other_ed ,
pla.remarks,
to_char(dr_invoice_date , 'DD-MON-YYYY') dr_invoice_date ,
dr_invoice_no ,
tr6_challan_no,
ref_document_id,
rounding_id,
--JA_JAINPLA_XMLP_PKG.cf_folioformula(pla.inventory_item_id, pla.slno) CF_Folio,
JA_JAINPLA_XMLP_PKG.cf_opening_balformula() CF_open_bal,
JA_JAINPLA_XMLP_PKG.cf_cr_open_balformula() CF_cr_open_bal,
JA_JAINPLA_XMLP_PKG.cf_additional_open_balformula() CF_additional_open_bal,
JA_JAINPLA_XMLP_PKG.cf_other_open_balformula() CF_other_open_bal,
JA_JAINPLA_XMLP_PKG.cf_cess_open_balformula() CF_cess_open_bal,
JA_JAINPLA_XMLP_PKG.cf_cvd_cess_open_balformula() CF_Cvd_Cess_Open_bal,
JA_JAINPLA_XMLP_PKG.cf_hdr_ec_codeformula() CF_Hdr_ec_code,
JA_JAINPLA_XMLP_PKG.cf_cr_cessformula(pla.register_id) CF_CR_CESS,
JA_JAINPLA_XMLP_PKG.cf_db_cessformula(pla.register_id) CF_DB_CESS,
JA_JAINPLA_XMLP_PKG.cf_cr_cvd_cessformula(pla.register_id) CF_CR_CVD_CESS,
JA_JAINPLA_XMLP_PKG.cf_db_cvd_cessformula(pla.register_id) CF_DB_CVD_CESS,
JA_JAINPLA_XMLP_PKG.cf_cvd_cess_clos_balformula(pla.register_id) CF_CVD_CESS_CLOS_BAL,
JA_JAINPLA_XMLP_PKG.cf_1formula0031(TRANSACTION_SOURCE_NUM, rounding_id, pla.inventory_item_id, TO_CHAR ( pla.tr6_challan_date , 'DD-MON-YYYY' ), pla.remarks, DECODE ( TRANSACTION_SOURCE_NUM , 91 , tr6_challan_date , dr_invoice_date ), ROUND ( NVL ( cr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( cr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( cr_other_ed , 0 ) , 2 ), ROUND ( NVL ( dr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( dr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( dr_other_ed , 0 ) , 2 )) CF_TR6NO,
JA_JAINPLA_XMLP_PKG.cf_2formula(rounding_id, pla.inventory_item_id, dr_invoice_no, ROUND ( NVL ( cr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( cr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( cr_other_ed , 0 ) , 2 ), tr6_challan_no, ROUND ( NVL ( dr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( dr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( dr_other_ed , 0 ) , 2 )) CF_DOC_NO,
JA_JAINPLA_XMLP_PKG.cf_1formula(rounding_id, pla.inventory_item_id, to_char ( dr_invoice_date , 'DD-MON-YYYY' ), ROUND ( NVL ( cr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( cr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( cr_other_ed , 0 ) , 2 ), TO_CHAR ( pla.tr6_challan_date , 'DD-MON-YYYY' ), ROUND ( NVL ( dr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( dr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( dr_other_ed , 0 ) , 2 )) CF_DOC_DATE,
JA_JAINPLA_XMLP_PKG.cf_cetformula(pla.inventory_item_id) CF_CET,
JA_JAINPLA_XMLP_PKG.cf_1formula0032(pla.vendor_id, pla.vendor_site_id, pla.vendor_cust_flag) CF_ECCODE,
JA_JAINPLA_XMLP_PKG.cf_rounding_amtsformula(TRANSACTION_SOURCE_NUM, rounding_id, dr_invoice_no) CF_ROUNDING_AMTS,
JA_JAINPLA_XMLP_PKG.cf_cess_close_balformula1(pla.register_id) LN_CESS_BAL,
JA_JAINPLA_XMLP_PKG.cf_cess_close_balformula2(pla.register_id) LN_SHE_CESS_BAL,
JA_JAINPLA_XMLP_PKG.cf_dr_basic_edformula(ROUND ( NVL ( dr_basic_ed , 0 ) , 2 )) cf_dr_basic_ed,
JA_JAINPLA_XMLP_PKG.cf_dr_addl_edformula(ROUND ( NVL ( dr_additional_ed , 0 ) , 2 )) cf_dr_addl_ed,
JA_JAINPLA_XMLP_PKG.cf_dr_other_edformula(ROUND ( NVL ( dr_other_ed , 0 ) , 2 )) cf_dr_other_ed,
JA_JAINPLA_XMLP_PKG.cf_bed_close_balformula(ROUND ( NVL ( cr_basic_ed , 0 ) , 2 ), ROUND ( NVL ( dr_basic_ed , 0 ) , 2 )) CF_BED_CLOSE_BAL,
JA_JAINPLA_XMLP_PKG.cf_aed_close_balformula(ROUND ( NVL ( cr_additional_ed , 0 ) , 2 ), ROUND ( NVL ( dr_additional_ed , 0 ) , 2 )) CF_AED_CLOSE_BAL,
JA_JAINPLA_XMLP_PKG.cf_sed_close_balformula(ROUND ( NVL ( cr_other_ed , 0 ) , 2 ), ROUND ( NVL ( dr_other_ed , 0 ) , 2 )) CF_SED_CLOSE_BAL,
JA_JAINPLA_XMLP_PKG.cf_ack_recd_dateformula(TRANSACTION_SOURCE_NUM, pla.register_id, pla.slno, pla.creation_date) CF_ACK_RECD_DATE,
JA_JAINPLA_XMLP_PKG.cp_rnd_basic_ed_p cp_rnd_basic_ed,
JA_JAINPLA_XMLP_PKG.cp_rnd_addl_ed_p cp_rnd_addl_ed,
JA_JAINPLA_XMLP_PKG.cp_rnd_other_ed_p cp_rnd_other_ed,
JA_JAINPLA_XMLP_PKG.cp_rnd_edu_cess_p cp_rnd_edu_cess,
JA_JAINPLA_XMLP_PKG.cp_rnd_cvd_cess_p cp_rnd_cvd_cess,
JA_JAINPLA_XMLP_PKG.CP_RND_SHE_EDU_CESS_p CP_RND_SHE_EDU_CESS,
JA_JAINPLA_XMLP_PKG.CP_RND_SHE_CVD_CESS_p CP_RND_SHE_CVD_CESS,
'X' dummy
FROM
JAI_CMN_RG_PLA_TRXS pla,
hr_all_organization_units hou,
hr_locations loc,
JAI_CMN_INVENTORY_ORGS hr_org
WHERE
pla.organization_id = :p_organization_id
AND pla.location_id = :p_location_id
AND TRUNC(pla.creation_date) >= NVL(TRUNC(:lp_trn_from_date), TRUNC(pla.creation_date))
AND TRUNC(pla.creation_date) <= NVL(TRUNC(:lp_trn_to_date), TRUNC(sysdate))
AND hou.organization_id = pla.organization_id
AND loc.location_id = pla.location_id
AND hr_org.organization_id = pla.organization_id
AND hr_org.location_id = pla.location_id
AND hr_org.location_id = pla.location_id
AND 1 = 1
and nvl(rounding_id, 1) <> -1
ORDER BY 3 ASC,4 ASC,5 ASC,6 ASC,7 ASC,8 ASC,10 ASC,11 ASC,12 ASC,1 ASC , fin_year , slno |
