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)) |