VIEW: APPS.JAI_AR_TRX_DTLS_V

Source

View Text - Preformatted

SELECT a.trx_date, a.trx_number, hzp.party_name customer_name, a.customer_trx_id, j.organization_id FROM ra_customer_trx_all a, OE_ORDER_headers_all b, JAI_OM_OE_BOND_REG_DTLS c, JAI_OM_OE_BOND_REG_HDRS d, hz_parties hzp, hz_cust_accounts hzca , JAI_AR_TRXS j WHERE a.interface_header_attribute1 is not null and ltrim(rtrim(a.interface_header_attribute1)) = ltrim(rtrim(to_char(b.order_number))) and b.order_type_id = c.order_type_id and c.order_flag = 'Y' and c.register_id = d.register_id AND hzp.party_id = hzca.party_id and hzca.cust_account_id = a.ship_to_customer_id and j.customer_trx_id = a.customer_trx_id and (upper(d.register_code) like '%EXPORT%' or upper(d.register_code) like '%BOND%') UNION ALL SELECT a.trx_date, a.trx_number, hzp.party_name customer_name, a.customer_trx_id, j.organization_id FROM ra_customer_trx_all a, JAI_OM_OE_BOND_REG_DTLS b, JAI_OM_OE_BOND_REG_HDRS c, hz_parties hzp, hz_cust_accounts hzca , JAI_AR_TRXS j WHERE a.interface_header_attribute1 is null and a.batch_source_id = b.order_type_id and b.order_flag = 'N' and c.register_id = b.register_id and (upper(c.register_code) like '%EXPORT%' or upper(c.register_code) like '%BOND%') AND hzp.party_id = hzca.party_id and hzca.cust_account_id= a.ship_to_customer_id and j.customer_trx_id = a.customer_trx_id

View Text - HTML Formatted

SELECT A.TRX_DATE
, A.TRX_NUMBER
, HZP.PARTY_NAME CUSTOMER_NAME
, A.CUSTOMER_TRX_ID
, J.ORGANIZATION_ID
FROM RA_CUSTOMER_TRX_ALL A
, OE_ORDER_HEADERS_ALL B
, JAI_OM_OE_BOND_REG_DTLS C
, JAI_OM_OE_BOND_REG_HDRS D
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, JAI_AR_TRXS J
WHERE A.INTERFACE_HEADER_ATTRIBUTE1 IS NOT NULL
AND LTRIM(RTRIM(A.INTERFACE_HEADER_ATTRIBUTE1)) = LTRIM(RTRIM(TO_CHAR(B.ORDER_NUMBER)))
AND B.ORDER_TYPE_ID = C.ORDER_TYPE_ID
AND C.ORDER_FLAG = 'Y'
AND C.REGISTER_ID = D.REGISTER_ID
AND HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID = A.SHIP_TO_CUSTOMER_ID
AND J.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID
AND (UPPER(D.REGISTER_CODE) LIKE '%EXPORT%' OR UPPER(D.REGISTER_CODE) LIKE '%BOND%') UNION ALL SELECT A.TRX_DATE
, A.TRX_NUMBER
, HZP.PARTY_NAME CUSTOMER_NAME
, A.CUSTOMER_TRX_ID
, J.ORGANIZATION_ID
FROM RA_CUSTOMER_TRX_ALL A
, JAI_OM_OE_BOND_REG_DTLS B
, JAI_OM_OE_BOND_REG_HDRS C
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, JAI_AR_TRXS J
WHERE A.INTERFACE_HEADER_ATTRIBUTE1 IS NULL
AND A.BATCH_SOURCE_ID = B.ORDER_TYPE_ID
AND B.ORDER_FLAG = 'N'
AND C.REGISTER_ID = B.REGISTER_ID
AND (UPPER(C.REGISTER_CODE) LIKE '%EXPORT%' OR UPPER(C.REGISTER_CODE) LIKE '%BOND%')
AND HZP.PARTY_ID = HZCA.PARTY_ID
AND HZCA.CUST_ACCOUNT_ID= A.SHIP_TO_CUSTOMER_ID
AND J.CUSTOMER_TRX_ID = A.CUSTOMER_TRX_ID