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 |
