JA India - Commercial Invoice - draft

SELECT distinct
	a.customer_trx_id,
	C.LINE_NUMBER,
	D.CONCATENATED_SEGMENTS,
	E.ITEM_TARIfF,
	f.organization_id, F.CUSTOMER_ID, F.RATE_PER_UNIT, F.EXCISE_DUTY_rATE, F.DUTY_AMOUNT,
	H.PAN_NO PAN_NO2, H.EXCISE_DUTY_REG_NO, H.EXCISE_DUTY_RANGE ,
	H.EXCISE_DUTY_DIVISION , H.EXCISE_DUTY_COMM,H.ST_REG_NO ST_REG_NO2 ,H.CST_REG_NO CST_REG_NO2,
	r.party_name customer_name,
	SUBSTR(j.ADDRESS1,1,5), SUBSTR(j.ADDRESS2,1,5),
	SUBSTR(j.ADDRESS3,1,5), SUBSTR(j.ADDRESS4,1,5),
	C.QUANTITY_INVOICED, a.trx_number, a.trx_date,
	C.UOM_CODE, C.EXTENDED_AMOUNT,
	C.LINE_TYPE, C.CUSTOMER_TRX_LINE_ID,
	b.delivery_id
FROM RA_CUSTOMER_TRX_ALL A,
	JAI_OM_WSH_LINES_ALL b,
	RA_CUSTOMER_TRX_LINES_ALL C,
	MTL_SYSTEM_ITEMS_KFV D,
	JAI_INV_ITM_SETUPS E,
	JAI_CMN_RG_23D_TRXS F,
	hz_cust_site_uses_all  G,
	JAI_CMN_CUS_ADDRESSES H,
	HZ_LOCATIONS J, HZ_PARTY_SITES HZPS, HZ_CUST_ACCT_SITES_ALL HZCAS,
	hz_parties r, hz_cust_accounts hzca
WHERE  
	a.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID and
	a.created_from = 'RAXTRX'	and	
	b.delivery_id = to_number(a.interface_header_attribute3) and
	b.order_line_id = to_number(c.interface_line_attribute6) AND
	f.reference_LINE_ID = b.delivery_detail_id
	AND E.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND E.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND F.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND F.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND D.INVENTORY_ITEM_ID= B.INVENTORY_ITEM_ID
	AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
	AND A.BILL_TO_CUSTOMER_ID = nvl(:p_customer_id, A.BILL_TO_CUSTOMER_ID)
	AND A.BILL_TO_CUSTOMER_ID = F.CUSTOMER_ID
  AND hzca.cust_account_id = f.customer_id
  AND r.party_id = hzca.party_id
	and b.organization_id = nvl(:p_org_id, b.organization_id)
	and b.location_id = nvl(:p_loc_id, b.location_id)
	and a.customer_trx_id = nvl(:p_invoice_no,a.customer_trx_id)
	AND G.SITE_USE_ID= F.SHIP_TO_SITE_ID
	AND G.cust_acct_site_id = H.ADDRESS_ID
	AND H.ADDRESS_ID= hzcas.cust_acct_site_id
  AND hzcas.party_site_id = hzps.party_site_id
  AND hzps.location_id = j.location_id 
 and f.organization_id=:organization_id1
 ORDER BY 16 ASC,15 ASC,23 ASC,24 ASC,13 ASC,14 ASC,12 ASC,11 ASC,17 ASC,6 ASC,10 ASC,5 ASC,20 ASC,19 ASC,21 ASC,18 ASC,1 ASC