select
max(a.transaction_date) transaction_date ,
min(a.receipt_ref) receipt_id,
min(a.division_no) division_no,
min(a.range_no) range_no,
sum(nvl(a.cr_basic_ed,0)) cr_basic_ed,
sum(nvl(a.cr_additional_ed,0)) cr_additional_ed,
sum(nvl(a.cr_other_ed,0)) cr_other_ed,
sum(nvl(a.cr_additional_ed,0)) + sum(nvl(a.cr_other_ed,0)) cr_addl_n_oth_ed,
a.excise_invoice_no excise_invoice_no,
a.excise_invoice_date excise_invoice_date,
sum(nvl(a.dr_basic_ed,0)) dr_basic_ed,
sum(nvl(a.dr_additional_ed,0)) dr_additional_ed,
sum(nvl(a.dr_other_ed,0)) dr_other_ed,
sum(nvl(a.dr_additional_ed,0)) + sum(nvl(a.dr_other_ed,0)) dr_addl_n_oth_ed,
sum(nvl(a.other_tax_credit,0)) other_tax_credit,
sum(nvl(a.other_tax_debit,0)) other_tax_debit,
SUM(NVL(a.cr_additional_cvd, 0)) cr_additional_cvd,
SUM(NVL(a.dr_additional_cvd, 0)) dr_additional_cvd,
a.vendor_id vendor_id,
a.vendor_site_id vendor_site_id,
a.customer_id customer_id,
a.customer_site_id customer_site_id,
a.transaction_source_num transaction_id,
JA_JAIN23P2_XMLP_PKG.cf_register_idformula(min ( a.receipt_ref )) CF_register_id,
JA_JAIN23P2_XMLP_PKG.cf_division_noformula(a.vendor_id, a.vendor_site_id, min ( a.receipt_ref ), min ( a.division_no )) CF_Division_No,
JA_JAIN23P2_XMLP_PKG.cf_range_noformula(a.vendor_id, a.vendor_site_id, min ( a.receipt_ref ), min ( a.range_no )) CF_Range_No,
JA_JAIN23P2_XMLP_PKG.cf_ec_code_suppformula(a.vendor_id, a.vendor_site_id, sum ( nvl ( a.cr_basic_ed , 0 ) ), sum ( nvl ( a.cr_additional_ed , 0 ) ), SUM ( NVL ( a.cr_additional_cvd , 0 ) ), sum ( nvl ( a.cr_other_ed , 0 ) ), min ( a.receipt_ref )) CF_EC_Code_Supp,
JA_JAIN23P2_XMLP_PKG.cf_ec_code_custformula(a.customer_id, a.customer_site_id, sum ( nvl ( a.dr_basic_ed , 0 ) ), sum ( nvl ( a.dr_additional_ed , 0 ) ), sum ( nvl ( a.dr_other_ed , 0 ) )) CF_EC_Code_Cust,
JA_JAIN23P2_XMLP_PKG.cf_folio_part_iformula(a.excise_invoice_no, a.excise_invoice_date) CF_folio_part_i,
JA_JAIN23P2_XMLP_PKG.CF_SEQUENCE_NOFORMULA CF_Sequence_No,
JA_JAIN23P2_XMLP_PKG.cf_rounding_amountformula(a.transaction_source_num, a.excise_invoice_no, a.excise_invoice_date, min ( a.receipt_ref )) CF_Rounding_Amount,
JA_JAIN23P2_XMLP_PKG.cf_cr_basic_edformula(sum ( nvl ( a.cr_basic_ed , 0 ) ), JA_JAIN23P2_XMLP_PKG.cf_rounding_amountformula(a.transaction_source_num, a.excise_invoice_no, a.excise_invoice_date, min ( a.receipt_ref ))) CF_Cr_Basic_ed,
JA_JAIN23P2_XMLP_PKG.cf_receipt_numformula(a.excise_invoice_no, a.excise_invoice_date, min ( a.receipt_ref ), a.transaction_source_num) CF_Receipt_Num,
JA_JAIN23P2_XMLP_PKG.cf_cr_cvdformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id) CF_CR_CVD,
JA_JAIN23P2_XMLP_PKG.cf_cr_exciseformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id) CF_CR_EXCISE,
JA_JAIN23P2_XMLP_PKG.cf_cr_sh_cvdformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id, a.transaction_source_num) CF_CR_SH_CVD,
JA_JAIN23P2_XMLP_PKG.cf_cr_sh_exciseformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id, a.transaction_source_num) CF_CR_SH_EXCISE,
JA_JAIN23P2_XMLP_PKG.cf_dr_exciseformula(sum ( nvl ( a.other_tax_debit , 0 ) )) CF_DR_EXCISE,
JA_JAIN23P2_XMLP_PKG.cf_dr_cvdformula() CF_DR_CVD,
JA_JAIN23P2_XMLP_PKG.cf_dr_sh_exciseformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id, a.transaction_source_num) CF_DR_SH_EXCISE,
JA_JAIN23P2_XMLP_PKG.cf_dr_sh_cvdformula(a.excise_invoice_no, a.excise_invoice_date, a.vendor_id, a.vendor_site_id, a.customer_id, a.customer_site_id, a.transaction_source_num) CF_DR_SH_CVD,
JA_JAIN23P2_XMLP_PKG.cf_additional_amountformula(sum ( nvl ( a.cr_additional_ed , 0 ) )) CF_additional_amount,
JA_JAIN23P2_XMLP_PKG.cf_additionalcvd_amountformula(SUM ( NVL ( a.cr_additional_cvd , 0 ) )) CF_additionalcvd_amount,
JA_JAIN23P2_XMLP_PKG.cf_other_amountformula(sum ( nvl ( a.cr_other_ed , 0 ) )) CF_Other_Amount,
JA_JAIN23P2_XMLP_PKG.cf_dr_addl_n_oth_edformula(sum ( nvl ( a.dr_additional_ed , 0 ) ) + sum ( nvl ( a.dr_other_ed , 0 ) )) cf_dr_addl_n_oth_ed,
JA_JAIN23P2_XMLP_PKG.cf_dr_addl_cvdformula(SUM ( NVL ( a.dr_additional_cvd , 0 ) )) cf_dr_addl_cvd,
JA_JAIN23P2_XMLP_PKG.cf_dr_basic_edformula(sum ( nvl ( a.dr_basic_ed , 0 ) )) cf_dr_basic_ed,
JA_JAIN23P2_XMLP_PKG.CP_additional_rounding_p CP_additional_rounding,
JA_JAIN23P2_XMLP_PKG.CP_additionalcvd_rounding_p CP_additionalcvd_rounding,
JA_JAIN23P2_XMLP_PKG.CP_other_rounding_p CP_other_rounding,
JA_JAIN23P2_XMLP_PKG.cp_rounded_excise_cess_p cp_rounded_excise_cess,
JA_JAIN23P2_XMLP_PKG.CP_ROUNDED_SH_EXCISE_CESS_p CP_ROUNDED_SH_EXCISE_CESS,
JA_JAIN23P2_XMLP_PKG.cp_rounded_cvd_cess_p cp_rounded_cvd_cess,
JA_JAIN23P2_XMLP_PKG.CP_rounded_sh_cvd_cess_p CP_rounded_sh_cvd_cess,
JA_JAIN23P2_XMLP_PKG.CP_rnd_dr_basic_ed_p CP_rnd_dr_basic_ed,
JA_JAIN23P2_XMLP_PKG.cp_rnd_dr_addl_ed_p cp_rnd_dr_addl_ed,
JA_JAIN23P2_XMLP_PKG.cp_rnd_dr_addl_cvd_p cp_rnd_dr_addl_cvd,
JA_JAIN23P2_XMLP_PKG.cp_rnd_dr_other_ed_p cp_rnd_dr_other_ed,
JA_JAIN23P2_XMLP_PKG.cp_rnd_dr_exc_edu_cess_p cp_rnd_dr_exc_edu_cess,
JA_JAIN23P2_XMLP_PKG.CP_RND_DR_SH_CVD_EDU_CESS_p CP_RND_DR_SH_CVD_EDU_CESS,
JA_JAIN23P2_XMLP_PKG.cp_rnd_dr_cvd_edu_cess_p cp_rnd_dr_cvd_edu_cess,
JA_JAIN23P2_XMLP_PKG.CP_RND_DR_SH_EXC_EDU_CESS_p CP_RND_DR_SH_EXC_EDU_CESS
from
JAI_CMN_RG_23AC_II_TRXS a
where a.organization_id = :p_organization_id
and a.location_id = :p_location_id
and register_type = :p_register_type
and a.Inventory_Item_id <> 0
&P_QUERY_CONCAT
Group By
a.excise_invoice_no ,
a.excise_invoice_date,
a.vendor_id ,
a.vendor_site_id,
a.customer_id,
a.customer_site_id,
a.transaction_source_num
ORDER BY 1 |
