JA India - ER1 - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - ER1 Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINER1_XML
DB package: JA_JAINER1_XMLP_PKG
Run JA India - ER1 - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT  SUM(DECODE(register_type, 'A', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)) credit_availed_on_inputs,
             ROUND(SUM(DECODE(register_type, 'C', nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0),0)), 0) credit_availed_on_cap_goods, 
             ROUND(SUM(NVL(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)), 0) total_credit_availed, 
             NVL(ROUND(SUM(NVL(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)), 0),0) total_credit_availed1, 
             ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0) credit_utilized, 
             NVL(ROUND(SUM(nvl(dr_basic_ed,0) + nvl(dr_additional_ed,0) + nvl(dr_other_ed,0)), 0),0) credit_utilized1, 
	JA_JAINER1_XMLP_PKG.cf_opening_balanceformula() CF_opening_balance, 
	NVL(JA_JAINER1_XMLP_PKG.cf_opening_balanceformula(),0) CF_opening_balance1,
--	JA_JAINER1_XMLP_PKG.cf_closing_balanceformula(:CF_opening_balance, ROUND ( SUM ( NVL ( cr_basic_ed , 0 ) + nvl ( cr_additional_ed , 0 ) + nvl ( cr_other_ed , 0 ) ) , 0 ), :CF_cr_utilized, :CF_rtv_amount) CF_closing_balance, 
	JA_JAINER1_XMLP_PKG.cf_credit_input_manf() CF_credit_input_manf, 
	JA_JAINER1_XMLP_PKG.cf_credit_input_i_ii_stg() CF_credit_input_I_II_Stg, 
--	JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_stformula(:CF_Srvc_Open_Balnc, :CF_tax_services) CF_Tot_Cr_availed_ST, 
--	JA_JAINER1_XMLP_PKG.cf_closing_blnc_stformula(:CF_Srvc_Open_Balnc, :CF_Tot_Cr_availed_ST, :CF_tax_duty_goods) CF_Closing_blnc_ST, 
	JA_JAINER1_XMLP_PKG.cf_cess_opening_blncformula() CF_Cess_Opening_Blnc, 
    NVL(JA_JAINER1_XMLP_PKG.cf_cess_opening_blncformula(),0) CF_Cess_Opening_Blnc1, 
	JA_JAINER1_XMLP_PKG.cf_srvc_open_balncformula() CF_Srvc_Open_Balnc, 
	NVL(JA_JAINER1_XMLP_PKG.cf_srvc_open_balncformula(),0) CF_Srvc_Open_Balnc1, 
	JA_JAINER1_XMLP_PKG.cf_srvc_cess_opngformula() CF_Srvc_Cess_Opng, 
	NVL(JA_JAINER1_XMLP_PKG.cf_srvc_cess_opngformula(),0) CF_Srvc_Cess_Opng1,
	JA_JAINER1_XMLP_PKG.cf_cess_excise_inputformula() CF_cess_excise_input, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cess_excise_inputformula(),0) CF_cess_excise_input1, 
	JA_JAINER1_XMLP_PKG.cf_cess_excise_input_manf() CF_cess_excise_input_manf, 
	JA_JAINER1_XMLP_PKG.cf_cess_excise_input_stg() CF_cess_excise_input_Stg, 
	JA_JAINER1_XMLP_PKG.cf_cess_excise_capformula() CF_cess_excise_cap, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cess_excise_capformula(),0) CF_cess_excise_cap1, 
	JA_JAINER1_XMLP_PKG.cf_tax_servicesformula() CF_tax_services, 
	NVL(JA_JAINER1_XMLP_PKG.cf_tax_servicesformula(),0) CF_tax_services1, 
--	JA_JAINER1_XMLP_PKG.cf_cenvat_total1(:CF_cenvat_acc_current, :CF_cenvat_credit_utilized) CF_cenvat_total1, 
	JA_JAINER1_XMLP_PKG.cf_tax_duty_goodsformula() CF_tax_duty_goods, 
	NVL(JA_JAINER1_XMLP_PKG.cf_tax_duty_goodsformula(),0) CF_tax_duty_goods1,
	JA_JAINER1_XMLP_PKG.cf_cess_tax_duty_goodsformula() CF_cess_tax_duty_goods, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cess_tax_duty_goodsformula(),0) CF_cess_tax_duty_goods1, 
	JA_JAINER1_XMLP_PKG.cf_crdt_avalied_cap_goodsformu(ROUND ( SUM ( DECODE ( register_type , 'C' , nvl ( cr_basic_ed , 0 ) + nvl ( cr_additional_ed , 0 ) + nvl ( cr_other_ed , 0 ) , 0 ) ) , 0 )) CF_crdt_avalied_cap_goods, 
--	JA_JAINER1_XMLP_PKG.cf_cr_utilizedformula(ROUND ( SUM ( nvl ( dr_basic_ed , 0 ) + nvl ( dr_additional_ed , 0 ) + nvl ( dr_other_ed , 0 ) ) , 0 ), :CF_rtv_amount) CF_cr_utilized, 
	JA_JAINER1_XMLP_PKG.cf_pla_opening_balanceformula() CF_pla_opening_balance, 
	NVL(JA_JAINER1_XMLP_PKG.cf_pla_opening_balanceformula(),0) CF_pla_opening_balance1, 
	JA_JAINER1_XMLP_PKG.cf_pla_tr6_challan_amtformula() CF_pla_TR6_Challan_Amt, 
	NVL(JA_JAINER1_XMLP_PKG.cf_pla_tr6_challan_amtformula(),0) CF_pla_TR6_Challan_Amt1, 
	JA_JAINER1_XMLP_PKG.cf_cenvat_credit_utilizedformu(ROUND ( SUM ( nvl ( dr_basic_ed , 0 ) + nvl ( dr_additional_ed , 0 ) + nvl ( dr_other_ed , 0 ) ) , 0 ), :CF_aed_acc_credit) CF_cenvat_credit_utilized, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cenvat_credit_utilizedformu(ROUND ( SUM ( nvl ( dr_basic_ed , 0 ) + nvl ( dr_additional_ed , 0 ) + nvl ( dr_other_ed , 0 ) ) , 0 ), :CF_aed_acc_credit),0) CF_cenvat_credit_utilized1, 
	JA_JAINER1_XMLP_PKG.cf_pla_closing_balanceformula(:CF_pla_total, :CF_cenvat_acc_current) CF_pla_closing_balance, 
	JA_JAINER1_XMLP_PKG.cf_rtv_amountformula() CF_rtv_amount, 
	JA_JAINER1_XMLP_PKG.cf_rtv_amountformula() CF_rtv_amount1, 
--	JA_JAINER1_XMLP_PKG.cf_pla_totalformula(:CF_pla_opening_balance, :CF_pla_TR6_Challan_Amt) CF_pla_total, 
	JA_JAINER1_XMLP_PKG.cf_cess_tax_duty_servicesformu() CF_cess_tax_duty_services, 
	JA_JAINER1_XMLP_PKG.cf_tax_duty_servicesformula() CF_tax_duty_services, 
--	JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_ecformula(:CF_cess_excise_input, :CF_cess_excise_cap) CF_Tot_CR_availed_EC, 
	JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_scformula(JA_JAINER1_XMLP_PKG.cf_srvc_cess_opngformula()) CF_Tot_CR_availed_SC, 
	NVL(JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_scformula(JA_JAINER1_XMLP_PKG.cf_srvc_cess_opngformula()),0) CF_Tot_CR_availed_SC1, 
--	JA_JAINER1_XMLP_PKG.cf_closing_blnc_ecformula(:CF_Cess_Opening_Blnc, :CF_Tot_CR_availed_EC, :CF_cess_util_pay_goods, :CF_cr_util_inp_rm_cess) CF_Closing_blnc_EC, 
--	JA_JAINER1_XMLP_PKG.cf_closing_blnc_scformula(:CF_Srvc_Cess_Opng, :CF_Tot_CR_availed_SC, :CF_cess_tax_duty_goods) CF_Closing_Blnc_SC, 
	JA_JAINER1_XMLP_PKG.cf_cess_util_pay_goodsformula(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_cessformula()) CF_cess_util_pay_goods, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cess_util_pay_goodsformula(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_cessformula()),0) CF_cess_util_pay_goods1,
	JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_cessformula() CF_cr_util_inp_rm_cess, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_cessformula(),0) CF_cr_util_inp_rm_cess1, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_opening_blncformula() CF_sh_Cess_Opening_Blnc, 
	NVL(JA_JAINER1_XMLP_PKG.cf_sh_cess_opening_blncformula(),0) CF_sh_Cess_Opening_Blnc1,
	JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_input_manffo() CF_sh_cess_excise_input_manf, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_input_stgfor() CF_sh_cess_excise_input_Stg, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_capformula() CF_SH_cess_excise_cap, 
	NVL(JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_capformula(),0) CF_SH_cess_excise_cap1,
--	JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_sh_ecformula(:CF_SH_cess_excise_input, :CF_SH_cess_excise_cap) CF_Tot_CR_availed_SH_EC, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_inputformula() CF_SH_cess_excise_input, 
	NVL(JA_JAINER1_XMLP_PKG.cf_sh_cess_excise_inputformula(),0) CF_SH_cess_excise_input1, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_util_pay_goodsformu(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_sh_cessformu()) CF_SH_cess_util_pay_goods, 
	NVL(JA_JAINER1_XMLP_PKG.cf_sh_cess_util_pay_goodsformu(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_sh_cessformu()),0) CF_SH_cess_util_pay_goods1,
	JA_JAINER1_XMLP_PKG.cf_srvc_sh_cess_opngformula() CF_Srvc_sh_Cess_Opng, 
	NVL(JA_JAINER1_XMLP_PKG.cf_srvc_sh_cess_opngformula(),0) CF_Srvc_sh_Cess_Opng1, 
	JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_sh_cessformu() CF_cr_util_inp_rm_sh_cess, 
	NVL(JA_JAINER1_XMLP_PKG.cf_cr_util_inp_rm_sh_cessformu(),0) CF_cr_util_inp_rm_sh_cess1, 
--	JA_JAINER1_XMLP_PKG.cf_closing_blnc_sh_ecformula(:CF_sh_Cess_Opening_Blnc, :CF_Tot_CR_availed_SH_EC, :CF_SH_cess_util_pay_goods, :CF_cr_util_inp_rm_sh_cess) CF_Closing_blnc_sh_EC, 
--	JA_JAINER1_XMLP_PKG.cf_closing_blnc_sh_scformula(:CF_Srvc_sh_Cess_Opng, :CF_Tot_CR_availed_sh_SC, :CF_sh_cess_tax_duty_goods) CF_Closing_Blnc_sh_SC, 
	JA_JAINER1_XMLP_PKG.cf_sh_cess_tax_duty_goodsformu() CF_sh_cess_tax_duty_goods, 
	NVL(JA_JAINER1_XMLP_PKG.cf_sh_cess_tax_duty_goodsformu(),0) CF_sh_cess_tax_duty_goods1,
	JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_sh_scformula(JA_JAINER1_XMLP_PKG.cf_srvc_sh_cess_opngformula()) CF_Tot_CR_availed_sh_SC,
	NVL(JA_JAINER1_XMLP_PKG.cf_tot_cr_availed_sh_scformula(JA_JAINER1_XMLP_PKG.cf_srvc_sh_cess_opngformula()),0) CF_Tot_CR_availed_sh_SC1 ,
	JA_JAINER1_XMLP_PKG.cp_crdt_import_cap_goods_p cp_crdt_import_cap_goods,
	JA_JAINER1_XMLP_PKG.cp_crdt_import_input_p cp_crdt_import_input,
	JA_JAINER1_XMLP_PKG.CP_cess_credit_avld_p CP_cess_credit_avld,
	NVL(JA_JAINER1_XMLP_PKG.CP_cess_credit_avld_p,0) CP_cess_credit_avld1,
	JA_JAINER1_XMLP_PKG.CP_sh_cess_credit_avld_p CP_sh_cess_credit_avld,
	NVL(JA_JAINER1_XMLP_PKG.CP_sh_cess_credit_avld_p,0) CP_sh_cess_credit_avld1,
	'x' dummy
FROM      JAI_CMN_RG_23AC_II_TRXS
     WHERE   location_id = :P_Location_id
     AND         organization_id = :p_Organization_id 
     AND         trunc(creation_date) >= :p_start_date
     AND         trunc(creation_date) <= trunc(nvl(:p_end_date,sysdate))
Parameter Name SQL text Validation
Organization
 
LOV Oracle
Location
 
LOV Oracle
Start Date
 
Date
End Date
 
Date
Service Tax Registration Number
 
LOV Oracle
Operating unit
 
LOV Oracle
Name of the Assessee
 
Char
City
 
Char
Group Quantitative Data
 
LOV Oracle
Blitz Report™