JA India - ARE1 Report- Not Supported: Reserved For Future Use

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Report Generation for Bond Register related Invoices
Application: Asia/Pacific Localizations
Source: India - ARE1 Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINARE1_XML
DB package: JA_JAINARE1_XMLP_PKG
Run JA India - ARE1 Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
		jihou.excise_duty_region region,
		jihou.excise_duty_reg_no registration_no,
		jihou.excise_duty_range range,
		jihou.excise_duty_comm commissionerate,
		jihou.excise_duty_division division,
	sum(jispl.quantity) quantity,
	jispl.organization_id organization_id,
	jispl.location_id location_id,
	jispl.delivery_id delivery_id,
	ract.trx_number trx_number,
	nvl(ract.ship_to_site_use_id, BILL_TO_SITE_USE_ID) ship_to_site_use_id,
	deliveries.customer_trx_id customer_trx_id,
	wdd.source_header_id order_header_id,
	wdd.source_line_id order_line_id,
	wdd.requested_quantity_uom requested_quantity_uom,
	sum(wdd.gross_weight) gross_weight,
	sum(wdd.net_weight) net_weight,
	wdd.item_description item_description,
	wdd.inventory_item_id          inventory_item_id,
	reg_h.register_id               register_id, 
	JA_JAINARE1_XMLP_PKG.cf_cityformula() CF_CITY, 
	JA_JAINARE1_XMLP_PKG.cf_legal_entityformula() CF_LEGAL_ENTITY, 
	JA_JAINARE1_XMLP_PKG.cf_destination_countryformula(nvl ( ract.ship_to_site_use_id , BILL_TO_SITE_USE_ID )) CF_DESTINATION_COUNTRY,
	JA_JAINARE1_XMLP_PKG.cf_balancesformula(wdd.source_header_id, reg_h.register_id, wdd.source_line_id, deliveries.customer_trx_id, jispl.delivery_id, ract.trx_number) CF_BALANCES,
	JA_JAINARE1_XMLP_PKG.CP_CLOSING_BAL_p CP_CLOSING_BAL,
	JA_JAINARE1_XMLP_PKG.CP_DEBIT_AMOUNT_p CP_DEBIT_AMOUNT,
	JA_JAINARE1_XMLP_PKG.CP_OPENING_BAL_p CP_OPENING_BAL, 
	JA_JAINARE1_XMLP_PKG.cf_qty_of_goodsformula(sum ( jispl.quantity ), wdd.requested_quantity_uom) CF_QTY_OF_GOODS, 
	JA_JAINARE1_XMLP_PKG.cf_tax_rateformula(jispl.delivery_id, wdd.inventory_item_id) CF_TAX_RATE, 
	JA_JAINARE1_XMLP_PKG.cf_valueformula(jispl.delivery_id, wdd.source_header_id, wdd.source_line_id, sum ( jispl.quantity ), :CF_SET_OF_BOOKS_ID, :CF_FUN_CURR) CF_VALUE, 
	JA_JAINARE1_XMLP_PKG.cf_func_tax_amountformula(jispl.delivery_id, wdd.inventory_item_id) CF_FUNC_TAX_AMOUNT, 
	JA_JAINARE1_XMLP_PKG.CP_NULL_p CP_NULL,
	JA_JAINARE1_XMLP_PKG.CP_EXCISE_CESS_AMOUNT_p CP_EXCISE_CESS_AMOUNT,
	JA_JAINARE1_XMLP_PKG.CP_EXCISE_SH_CESS_AMOUNT_p CP_EXCISE_SH_CESS_AMOUNT
FROM JAI_CMN_INVENTORY_ORGS jihou,
	wsh_delivery_details wdd,
	ra_customer_trx_all ract,
	JAI_AR_TRXS jract,
	JAI_OM_WSH_LINES_ALL jispl,
               wsh_new_deliveries          wnd ,
	(select distinct customer_trx_id, interface_line_attribute3 delivery_name
		from ra_customer_trx_lines_all 
		where customer_trx_id BETWEEN :p_customer_trx_id AND :p_customer_trx_id1
		and interface_line_context = 'ORDER ENTRY'
		and line_type = 'LINE') deliveries,
	JAI_OM_OE_BOND_REG_HDRS reg_h,
	JAI_OM_OE_BOND_TRXS reg_d		
WHERE wdd.delivery_detail_id = jispl.delivery_detail_id
	AND wdd.organization_id = jispl.organization_id
	AND wnd.name                = deliveries.delivery_name 
	AND to_char( jispl.delivery_id ) = wnd.delivery_id
	AND reg_h.register_code = 'BOND_REG' 
	AND reg_h.register_id = reg_d.register_id
	AND reg_d.order_header_id = jispl.order_header_id			
	AND reg_d.order_flag = 'Y'
	AND reg_h.organization_id = jispl.organization_id
	AND reg_h.location_id = jispl.location_id
	AND jract.customer_trx_id = ract.customer_trx_id
	AND jihou.organization_id = jract.organization_id
	AND jihou.location_id = jract.location_id
	AND ract.customer_trx_id = deliveries.customer_trx_id
	AND ract.customer_trx_id  BETWEEN :p_customer_trx_id AND :p_customer_trx_id1
	AND ract.org_id = :p_org_id
	AND ract.interface_header_context = 'ORDER ENTRY'
GROUP BY
  jihou.excise_duty_region         ,
	jihou.excise_duty_reg_no         ,
	jihou.excise_duty_range          ,
	jihou.excise_duty_comm           ,
	jihou.excise_duty_division       ,
	jispl.organization_id            ,
	jispl.location_id                ,
	jispl.delivery_id                ,
	ract.trx_number                            ,
	nvl(ract.ship_to_site_use_id, BILL_TO_SITE_USE_ID) ,
	deliveries.customer_trx_id       ,
	wdd.source_header_id             ,
	wdd.source_line_id               ,
	wdd.requested_quantity_uom       ,
	wdd.item_description             ,
                     wdd.inventory_item_id          ,
	reg_h.register_id
	ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,8 ASC,7 ASC,12 ASC,10 ASC
Parameter Name SQL text Validation
Operating Unit
 
LOV
Organization
 
LOV Oracle
Invoice From
 
LOV Oracle
Invoice To
 
LOV Oracle
Excise Office Address
 
Char
Superintendant Address
 
Char
Excise postal address
 
Char
Bond/Undertaking No.
 
Char
Bond Date
 
Date
Blitz Report™