JA India - ER1 - draft

Description
Categories: BI Publisher
Columns: Credit Availed On Inputs, Credit Availed On Cap Goods, Total Credit Availed, Total Credit Availed1, Credit Utilized, Credit Utilized1, Cf Opening Balance, Cf Opening Balance1, Cf Credit Input Manf, Cf Credit Input I Ii Stg ...
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
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
 
City
 
Group Quantitative Data
 
LOV Oracle