VIEW: APPS.CLN_3C3_INVOICE_V

Source

View Text - Preformatted

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_all 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/AAA') 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

View Text - HTML Formatted

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_ALL 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/AAA') 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