View: CLN_3C3_INVOICE_V

Product:	CLN - Supply Chain Trading Connector for RosettaNet
Description:	View of invoices which is used ot generate 3C3 outbound
Implementation/DBA Data:	APPS.CLN_3C3_INVOICE_V

View Text

SELECT D.DOCUMENT_TRANSFER_ID DOCUMENT_TRANSFER_ID
, H.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, H.TERM_ID TERM_ID
, (SELECT SUM(EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES L
WHERE L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID) AMOUNT
, H.INVOICE_CURRENCY_CODE CURRENCY_CODE
, H.TRX_DATE TRX_DATE
, H.TRX_NUMBER TRX_NUMBER
, NVL(H.PURCHASE_ORDER
, 'N/AA') PO_NUMBER
, T.TYPE DOCUMENT_TYPE
, RM.PAYMENT_TYPE_CODE PAYMENT_METHOD
, ORG.NAME SUPPLIER_NAME
, LOC.ECE_TP_LOCATION_CODE SUPPLIER_CODE
, SR.NAME SALESREP
, SR.EMAIL_ADDRESS SALESREP_EMAIL
, TO_CHAR(D.DOCUMENT_TRANSFER_ID) ||':'|| TO_CHAR(H.CUSTOMER_TRX_ID) REFERENCE_NUMBER
, CAS.ECE_TP_LOCATION_CODE BILL_TO_LOCATION_CODE
, P.PARTY_NAME CUSTOMER_NAME
, H.ORG_ID ORG_ID
FROM AR_DOCUMENT_TRANSFERS D
, RA_CUSTOMER_TRX_ALL H
, RA_CUST_TRX_TYPES_ALL T
, AR_RECEIPT_METHODS RM
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_LOCATIONS_ALL LOC
, RA_SALESREPS_ALL SR
, HZ_CUST_ACCT_SITES_ALL CAS
, HZ_CUST_SITE_USES_ALL CSU
, HZ_PARTIES P
, HZ_CUST_ACCOUNTS_ALL CA
WHERE D.SOURCE_ID = H.CUSTOMER_TRX_ID
AND H.CUST_TRX_TYPE_ID = T.CUST_TRX_TYPE_ID
AND H.ORG_ID = T.ORG_ID
AND H.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID(+)
AND ORG.ORGANIZATION_ID = H.ORG_ID
AND ORG.LOCATION_ID = LOC.LOCATION_ID(+)
AND NVL (LOC.BUSINESS_GROUP_ID
, NVL(ORG.BUSINESS_GROUP_ID
, -99) ) = NVL (ORG.BUSINESS_GROUP_ID
, NVL( LOC.BUSINESS_GROUP_ID
, -99))
AND H.PRIMARY_SALESREP_ID = SR.SALESREP_ID(+)
AND SR.ORG_ID(+) = H.ORG_ID
AND CAS.ORG_ID = H.ORG_ID
AND CSU.ORG_ID =H.ORG_ID
AND CSU.SITE_USE_ID = H.BILL_TO_SITE_USE_ID
AND CAS.CUST_ACCT_SITE_ID = CSU.CUST_ACCT_SITE_ID
AND CSU.SITE_USE_CODE = 'BILL_TO'
AND CAS.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.PARTY_ID = P.PARTY_ID

Columns