JA India - RG23 Part II Report - draft - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG23 Part II Report (XML) - Not Supported: Reserved For Future Use - Not Supported: Reserved For Future Use
Short Name: JAI23A_2_XML
DB package: JA_JAIN23P2_XMLP_PKG
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
Parameter Name SQL text Validation
Transaction To Date
 
Date
Transaction From Date
 
Date
Register Type
 
LOV Oracle
Location
 
LOV Oracle
Organization
 
LOV Oracle