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
Source: India - PLA Register Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINPLA_XML
DB package: JA_JAINPLA_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 Name | SQL text | Validation | |
---|---|---|---|
Organization |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
Transaction From Date |
|
Date | |
Transaction To Date |
|
Date |