JA India - AR3A Status - draft

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