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 |
