[Home] [Help]
| Product: | BIC - Customer Intelligence (obsolete) |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CTA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, CTA.BILL_TO_CUSTOMER_ID CUSTOMER_ID
, CTA.TRX_NUMBER INVOICE_NUMBER
, CTA.TRX_DATE INVOICE_DATE
, CTA.TERM_DUE_DATE DUE_DATE
, CTA.PURCHASE_ORDER ORDER_NUMBER
, CTA.COMPLETE_FLAG INVCOM_FLAG
, CTA.STATUS_TRX STATUS
, CTA.COMPLETE_FLAG COMPLETE_FLAG
, CTA.INVOICE_CURRENCY_CODE CURRENCY
, CTA.CUSTOMER_REFERENCE REFERENCE
, CTA.SHIP_DATE_ACTUAL SHIP_DATE
, CTA.SHIP_VIA SHIP_VIA
, SR.NAME SALESPERSON
, WAYBILL_NUMBER SHIPPING_REFERENCE
, CTT.TYPE TYPE
, TRM.NAME TERM
, LOC.ADDRESS REMIT_TO
, CTA.ORG_ID ORG_ID
, PRTY.PARTY_ID PARTY_ID
, CUST.ACCOUNT_NUMBER
FROM RA_CUSTOMER_TRX_ALL CTA
, RA_CUST_TRX_TYPES_ALL CTT
, RA_TERMS TRM
, RA_SALESREPS_ALL SR
, BIC_ADDRESS_V LOC
, HZ_PARTIES PRTY
, HZ_CUST_ACCOUNTS CUST
WHERE CTT.TYPE = 'INV'
AND PRTY.PARTY_ID = CUST.PARTY_ID
AND CUST.CUST_ACCOUNT_ID = CTA.BILL_TO_CUSTOMER_ID
AND CTT.CUST_TRX_TYPE_ID = CTA.CUST_TRX_TYPE_ID
AND (CTT.ORG_ID = CTA.ORG_ID OR (CTT.ORG_ID IS NULL
AND CTA.ORG_ID IS NULL) )
AND SR.SALESREP_ID(+) != -3
AND CTA.PRIMARY_SALESREP_ID = SR.SALESREP_ID (+)
AND CTA.TERM_ID = TRM.TERM_ID (+)
AND CTA.REMIT_TO_ADDRESS_ID = LOC.LOCATION_ID(+)