VIEW: APPS.ARFV_AR_TRANSACTIONS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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