JA India - ARE1 - draft

Description
Categories: BI Publisher
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
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
P_ORG_ID
 
Number
Bond Date
 
Date
Bond/Undertaking No.
 
Excise postal address
 
Superintendant Address
 
Excise Office Address
 
Invoice To
 
LOV Oracle
Invoice From
 
LOV Oracle
Organization
 
LOV Oracle
Ask a question