View: ARFV_AR_TRANSACTIONS

Product:	AR - Receivables
Description:	This shows information about transactions imported from a foreign system into Oracle Receivables.
Implementation/DBA Data:	APPS.ARFV_AR_TRANSACTIONS

View Text

SELECT /* UNIQUE ATTRIBUTES */ CT.CUSTOMER_TRX_ID TRANSACTION_ID
, /* REGULAR ATTRIBUTES */ CT.TRX_NUMBER TRANSACTION_NUMBER
, CT.DOC_SEQUENCE_VALUE DOCUMENT_SEQUENCE_NUMBER
, CT.TRX_DATE TRANSACTION_DATE
, CT.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, CT.EXCHANGE_RATE CURRENCY_EXCHANGE_RATE
, CT.EXCHANGE_RATE_TYPE CURRENCY_EXCHANGE_RATE_TYPE
, CT.EXCHANGE_DATE CURRENCY_EXCHANGE_RATE_DATE
, '_LA:CT.COMPLETE_FLAG:AR_LOOKUPS:YES/NO:MEANING' "_LA:COMPLETE_FLAG"
, '_LA:CT.STATUS_TRX:AR_LOOKUPS:INVOICE_TRX_STATUS:MEANING' "_LA:TRANSACTION_STATUS"
, CT.PURCHASE_ORDER PURCHASE_ORDER_NUMBER
, CT.PURCHASE_ORDER_DATE PURCHASE_ORDER_DATE
, CT.PURCHASE_ORDER_REVISION PURCHASE_ORDER_REVISION
, CT.REASON_CODE REASON_CODE
, CT.CT_REFERENCE REFERENCE_NUMBER
, '_LA:CT.FINANCE_CHARGES:AR_LOOKUPS:YES/NO:MEANING' "_LA:FINANCE_CHARGES_FLAG"
, CT.TERM_DUE_DATE DUE_DATE
, '_LA:CT.DEFAULT_TAX_EXEMPT_FLAG:AR_LOOKUPS:TAX_CONTROL_FLAG:MEANING' "_LA:TAX_EXEMPT_FLAG"
, CT.SHIP_VIA FREIGHT_CARRIER
, CT.SHIP_DATE_ACTUAL SHIP_DATE
, CT.WAYBILL_NUMBER SHIPPING_REFERENCE
, '_LA:CT.FOB_POINT:AR_LOOKUPS:FOB:MEANING' "_LA:FOB_POINT"
, '_LA:CT.EDI_PROCESSED_FLAG:AR_LOOKUPS:YES/NO:MEANING' "_LA:EDI_PROCESSED_FLAG"
, CT.PRINTING_ORIGINAL_DATE FIRST_PRINT_DATE
, CT.PRINTING_LAST_PRINTED LAST_PRINT_DATE
, '_LA:CT.PRINTING_PENDING:AR_LOOKUPS:YES/NO:MEANING' "_LA:PENDING_PRINT_FLAG"
, CT.PRINTING_COUNT PRINTING_COUNT
, CT.ORIG_SYSTEM_BATCH_NAME ORIGINAL_SYSTEM_BATCH_NAME
, BAT.NAME BATCH_NAME
, BATSRC.NAME BATCH_SOURCE_NAME
, DECODE(CT.BILL_TO_CONTACT_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_CONTACT_NAME(CT.BILL_TO_CONTACT_ID)
, 1
, 91)) BILL_TO_CONTACT_NAME
, DECODE(CT.SHIP_TO_CONTACT_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_CONTACT_NAME(CT.SHIP_TO_CONTACT_ID)
, 1
, 91)) SHIP_TO_CONTACT_NAME
, DECODE(CT.SOLD_TO_CONTACT_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_CONTACT_NAME(CT.SOLD_TO_CONTACT_ID)
, 1
, 91)) SOLD_TO_CONTACT_NAME
, SUBSTRB(BIPARTY.PARTY_NAME
, 1
, 50) BILL_TO_CUSTOMER
, DECODE(CT.SHIP_TO_CUSTOMER_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_NAME(CT.SHIP_TO_CUSTOMER_ID)
, 1
, 50)) SHIP_TO_CUSTOMER
, DECODE(CT.SOLD_TO_CUSTOMER_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_NAME(CT.SOLD_TO_CUSTOMER_ID)
, 1
, 50)) SOLD_TO_CUSTOMER
, DECODE(CT.PAYING_CUSTOMER_ID
, NULL
, NULL
, SUBSTR(HZ_COMMON_PUB.GET_CUST_NAME(CT.PAYING_CUSTOMER_ID)
, 1
, 50)) PAYING_CUSTOMER
, CTT.NAME TRANSACTION_TYPE_NAME
, SAL.NAME SALESPERSON_NAME
, BISU.LOCATION BILL_TO_SITE
, SISU.LOCATION SHIP_TO_SITE
, SOSU.LOCATION SOLD_TO_SITE
, PASU.LOCATION PAYING_SITE
, SUBSTR(HZ_COMMON_PUB.GET_CUST_ADDRESS(CT.REMIT_TO_ADDRESS_ID)
, 1
, 240) REMIT_TO_ADDRESS
, DECODE(EXTN.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, BACC.BANK_ACCOUNT_NAME
, NULL) CUSTOMER_BANK_ACCOUNT_NAME
, DECODE(EXTN.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, BACC.BANK_ACCOUNT_NUM
, 'CREDITCARD'
, C.MASKED_CC_NUMBER
, NULL) CUSTOMER_BANK_ACCOUNT_NUMBER
, RM.NAME PAYMENT_METHOD
, AOU.NAME OPERATING_UNIT
, '_DF:AR:RA_CUSTOMER_TRX:CT' /* "_DF:TRANS_INFO" */
, '_DF:AR:RA_INTERFACE_HEADER:CT' /* "_DF:INVOICE_TRANS" */
, '_DF:JG:JG_RA_CUSTOMER_TRX:CT' /* "_DF:GLOBAL_FLEX" */
, /* IDS */ CT.BATCH_ID BATCH_ID
, CT.BATCH_SOURCE_ID BATCH_SOURCE_ID
, CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, CT.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, CT.SOLD_TO_CONTACT_ID SOLD_TO_CONTACT_ID
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CT.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, CT.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID
, CT.PAYING_CUSTOMER_ID PAYING_CUSTOMER_ID
, CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CT.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID
, CT.SOLD_TO_SITE_USE_ID SOLD_TO_SITE_USE_ID
, CT.PAYING_SITE_USE_ID PAYING_SITE_USE_ID
, CT.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID
, CT.PAYMENT_TRXN_EXTENSION_ID CUSTOMER_BANK_ACCOUNT_ID
, CT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, CT.ORG_ID ORG_ID
, /* WHO COLUMNS */ CT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY LAST_UPDATED_BY
, CT.CREATION_DATE CREATION_DATE
, CT.CREATED_BY CREATED_BY
, CT.INTERNAL_NOTES INTERNAL_NOTES
, CT.COMMENTS COMMENTS
, CTT.ACCOUNTING_AFFECT_FLAG
, CT.AGREEMENT_ID
, CT.RECURRED_FROM_TRX_NUMBER
, CT.INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX CT
, RA_BATCHES BAT
, RA_BATCH_SOURCES BATSRC
, HZ_PARTIES BIPARTY
, HZ_CUST_ACCOUNTS BICUST_ACCT
, RA_CUST_TRX_TYPES CTT
, RA_SALESREPS SAL
, HZ_CUST_SITE_USES BISU
, HZ_CUST_SITE_USES SISU
, HZ_CUST_SITE_USES SOSU
, HZ_CUST_SITE_USES PASU
, (SELECT X.TRXN_EXTENSION_ID
, NVL(U.INSTRUMENT_TYPE
, P.INSTRUMENT_TYPE) INSTRUMENT_TYPE
, U.INSTRUMENT_ID
FROM IBY_FNDCPT_PMT_CHNNLS_B P
, IBY_FNDCPT_TX_EXTENSIONS X
, IBY_PMT_INSTR_USES_ALL U
WHERE X.INSTR_ASSIGNMENT_ID = U.INSTRUMENT_PAYMENT_USE_ID(+)
AND X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE ) EXTN
, IBY_EXT_BANK_ACCOUNTS BACC
, IBY_CREDITCARD C
, AR_RECEIPT_METHODS RM
, HR_ALL_ORGANIZATION_UNITS AOU
WHERE CT.BATCH_ID = BAT.BATCH_ID (+)
AND CT.BATCH_SOURCE_ID = BATSRC.BATCH_SOURCE_ID (+)
AND ((CT.BATCH_SOURCE_ID IS NULL) OR (CT.BATCH_SOURCE_ID IS NOT NULL
AND NVL(CT.ORG_ID
, -12345) = NVL(BATSRC.ORG_ID
, -12345)))
AND CT.BILL_TO_CUSTOMER_ID = BICUST_ACCT.CUST_ACCOUNT_ID (+)
AND BICUST_ACCT.PARTY_ID = BIPARTY.PARTY_ID (+)
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND ((CT.CUST_TRX_TYPE_ID IS NULL) OR (CT.CUST_TRX_TYPE_ID IS NOT NULL
AND NVL(CT.ORG_ID
, -12345) = NVL(CTT.ORG_ID
, -12345)))
AND CT.PRIMARY_SALESREP_ID = SAL.SALESREP_ID (+)
AND ((CT.PRIMARY_SALESREP_ID IS NULL) OR (CT.PRIMARY_SALESREP_ID IS NOT NULL
AND NVL(CT.ORG_ID
, -12345) = NVL(SAL.ORG_ID
, -12345)))
AND CT.BILL_TO_SITE_USE_ID = BISU.SITE_USE_ID (+)
AND CT.SHIP_TO_SITE_USE_ID = SISU.SITE_USE_ID (+)
AND CT.SOLD_TO_SITE_USE_ID = SOSU.SITE_USE_ID (+)
AND CT.PAYING_SITE_USE_ID = PASU.SITE_USE_ID (+)
AND EXTN.TRXN_EXTENSION_ID(+) = CT.PAYMENT_TRXN_EXTENSION_ID
AND DECODE(EXTN.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, EXTN.INSTRUMENT_ID)= BACC.EXT_BANK_ACCOUNT_ID(+)
AND DECODE(EXTN.INSTRUMENT_TYPE
, 'CREDITCARD'
, EXTN.INSTRUMENT_ID
, NULL) = C.INSTRID(+)
AND CT.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+)
AND CT.ORG_ID = AOU.ORGANIZATION_ID (+) WITH READ ONLY

Columns