JA India - AR3A Status - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - AR3A Status Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINAR3R_XML
DB package: JA_JAINAR3R_XMLP_PKG
select 
	a.organization_id, 
	a.location_id, 
	e.organization_name, 
	f.description, 
	f.address_line_1, 
	f.address_line_2, 
	f.address_line_3, 
	f.country, 
	a.customer_id, 
	a.customer_site_id, 
	b.party_name customer_name, 
	decode(order_flag,'Y','ORDER','N','INVOICE') trx_type, 
	a.trx_number,
	a.trx_date, 
	a.delivery_id   delivery_detail_id, 
	 a.delivery_date, 
	a.excise_exempt_type, 
	a.excise_exempt_refno,
	a.excise_exempt_date, 
	a.ar3_form_no, 
	a.ar3_form_date, 
	a.received_date, 
	JA_JAINAR3R_XMLP_PKG.cf_1formula0031(a.customer_site_id) location, 
	JA_JAINAR3R_XMLP_PKG.cf_1formula(a.trx_number, decode ( order_flag , 'Y' , 'ORDER' , 'N' , 'INVOICE' )) trx_number1, 
	JA_JAINAR3R_XMLP_PKG.cf_delivery_idformula(a.delivery_id) CF_DELIVERY_ID, 
	JA_JAINAR3R_XMLP_PKG.delivery_nameformula(a.delivery_id) delivery_name
FROM JAI_CMN_OM_AR_AR3_FORMS a,
     hz_parties b, hz_cust_accounts hzca,
     org_organization_definitions e,
     hr_locations f
Where
    a.organization_id = NVL(:p_organization_id,a.organization_id)
AND a.location_id = NVL(:p_location_id,a.location_id)
AND a.customer_id = NVL(:p_customer_id,a.customer_id)
AND NVL(a.customer_site_id,0) = NVL(:p_customer_site_id,NVL(a.customer_site_id,0))
AND a.excise_exempt_type = NVL(:p_excise_exempt_type,a.excise_exempt_type)
AND a.order_flag = decode(:p_transaction_type, 'ORDER','Y', 'INVOICE','N',a.order_flag)
AND a.trx_number = NVL(:p_trx_id,NVL(:P_TRX_ID2,a.trx_number))
AND a.customer_id = hzca.cust_account_id
AND b.party_id    = hzca.cust_account_id
AND e.organization_id = a.organization_id
AND f.location_id = a.location_id
&p_w_clause
--Order By
--a.Organization_id, a.location_id, decode(a.order_flag,'Y',1,'N',2),
--a.customer_id, a.customer_site_id
ORDER BY 3 ASC,4 ASC,5 ASC,6 ASC,7 ASC,8 ASC,1 ASC,2 ASC,9 ASC,11 ASC,10 ASC,12 ASC , a.Organization_id , a.location_id , decode ( a.order_flag , 'Y' , 1 , 'N' , 2 ) , a.customer_id , a.customer_site_id
Parameter Name SQL text Validation
Order Number
 
LOV Oracle
Status
 
LOV Oracle
Excise Exempt Type
 
LOV Oracle
Invoice Number
 
LOV Oracle
Transaction Type
 
LOV Oracle
Customer Site
 
LOV Oracle
Customer Name
 
LOV Oracle
Location
 
LOV Oracle
Organization Name
 
LOV Oracle