JA India - PLA Register - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - PLA Register Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINPLA_XML
DB package: JA_JAINPLA_XMLP_PKG
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
Parameter NameSQL textValidation
Transaction To Date
 
Date
Transaction From Date
 
Date
Location
 
LOV Oracle
Organization
 
LOV Oracle