VIEW: APPS.ARFV_ON_ACCOUNT_CREDIT_MEMOS

Source

View Text - Preformatted

SELECT /* UNIQUE ATTRIBUTES */ CT.CUSTOMER_TRX_ID , /* REGULAR ATTRIBUTES */ CT.TRX_NUMBER , CT.DOC_SEQUENCE_VALUE , CT.TRX_DATE , CT.EXCHANGE_RATE , CT.EXCHANGE_RATE_TYPE , CT.EXCHANGE_DATE , '_LA:CT.COMPLETE_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CT.STATUS_TRX:AR_LOOKUPS:INVOICE_TRX_STATUS:MEANING' , CT.PURCHASE_ORDER , CT.PURCHASE_ORDER_DATE , CT.PURCHASE_ORDER_REVISION , CT.REASON_CODE , CT.CUSTOMER_REFERENCE , '_LA:CT.FINANCE_CHARGES:AR_LOOKUPS:YES/NO:MEANING' , CT.TERM_DUE_DATE , '_LA:CT.DEFAULT_TAX_EXEMPT_FLAG:AR_LOOKUPS:TAX_CONTROL_FLAG:MEANING' , CT.SHIP_VIA , CT.SHIP_DATE_ACTUAL , CT.WAYBILL_NUMBER , '_LA:CT.FOB_POINT:AR_LOOKUPS:FOB:MEANING' , '_LA:CT.EDI_PROCESSED_FLAG:AR_LOOKUPS:YES/NO:MEANING' , CT.PRINTING_ORIGINAL_DATE , CT.PRINTING_LAST_PRINTED , '_LA:CT.PRINTING_PENDING:AR_LOOKUPS:YES/NO:MEANING' , CT.PRINTING_COUNT , CT.ORIG_SYSTEM_BATCH_NAME , /* RESOLVED FK RESOLUTIONS */ BAT.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 , SAL.NAME , BISU.LOCATION , SISU.LOCATION , SOSU.LOCATION , PASU.LOCATION , 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), decode(extn.instrument_type,'BANKACCOUNT',BACC.BANK_ACCOUNT_NUM ,'CREDITCARD',EXTN.CARD_NUMBER,null), RM.NAME , TER.NAME , AOU.NAME , '_DF:AR:RA_CUSTOMER_TRX:CT' , '_DF:AR:RA_INTERFACE_HEADER:CT' , '_DF:JG:JG_RA_CUSTOMER_TRX:CT' , /* IDS */ CT.BATCH_ID , CT.TERM_ID , CT.BILL_TO_CONTACT_ID , CT.SHIP_TO_CONTACT_ID , CT.SOLD_TO_CONTACT_ID , CT.BILL_TO_CUSTOMER_ID , CT.SHIP_TO_CUSTOMER_ID , CT.SOLD_TO_CUSTOMER_ID , CT.PAYING_CUSTOMER_ID , CT.CUST_TRX_TYPE_ID , CT.PRIMARY_SALESREP_ID , CT.BILL_TO_SITE_USE_ID , CT.SHIP_TO_SITE_USE_ID , CT.SOLD_TO_SITE_USE_ID , CT.PAYING_SITE_USE_ID , CT.REMIT_TO_ADDRESS_ID , CT.PAYMENT_TRXN_EXTENSION_ID, CT.RECEIPT_METHOD_ID , CT.ORG_ID , /* WHO COLUMNS */ CT.LAST_UPDATE_DATE , CT.LAST_UPDATED_BY , CT.CREATION_DATE , CT.CREATED_BY FROM RA_CUSTOMER_TRX CT , RA_BATCHES BAT , 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 , IBY_TRXN_EXTENSIONS_V EXTN, IBY_EXT_BANK_ACCOUNTS BACC , AR_RECEIPT_METHODS RM , RA_TERMS TER , HR_ALL_ORGANIZATION_UNITS AOU WHERE CT.BATCH_ID = BAT.BATCH_ID (+) 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 CT.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+) AND CT.TERM_ID = TER.TERM_ID (+) AND CT.ORG_ID = AOU.ORGANIZATION_ID (+) AND CTT.TYPE = 'CM' AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL WITH READ ONLY

View Text - HTML Formatted

SELECT /* UNIQUE ATTRIBUTES */ CT.CUSTOMER_TRX_ID
, /* REGULAR ATTRIBUTES */ CT.TRX_NUMBER
, CT.DOC_SEQUENCE_VALUE
, CT.TRX_DATE
, CT.EXCHANGE_RATE
, CT.EXCHANGE_RATE_TYPE
, CT.EXCHANGE_DATE
, '_LA:CT.COMPLETE_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CT.STATUS_TRX:AR_LOOKUPS:INVOICE_TRX_STATUS:MEANING'
, CT.PURCHASE_ORDER
, CT.PURCHASE_ORDER_DATE
, CT.PURCHASE_ORDER_REVISION
, CT.REASON_CODE
, CT.CUSTOMER_REFERENCE
, '_LA:CT.FINANCE_CHARGES:AR_LOOKUPS:YES/NO:MEANING'
, CT.TERM_DUE_DATE
, '_LA:CT.DEFAULT_TAX_EXEMPT_FLAG:AR_LOOKUPS:TAX_CONTROL_FLAG:MEANING'
, CT.SHIP_VIA
, CT.SHIP_DATE_ACTUAL
, CT.WAYBILL_NUMBER
, '_LA:CT.FOB_POINT:AR_LOOKUPS:FOB:MEANING'
, '_LA:CT.EDI_PROCESSED_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, CT.PRINTING_ORIGINAL_DATE
, CT.PRINTING_LAST_PRINTED
, '_LA:CT.PRINTING_PENDING:AR_LOOKUPS:YES/NO:MEANING'
, CT.PRINTING_COUNT
, CT.ORIG_SYSTEM_BATCH_NAME
, /* RESOLVED FK RESOLUTIONS */ BAT.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
, SAL.NAME
, BISU.LOCATION
, SISU.LOCATION
, SOSU.LOCATION
, PASU.LOCATION
, 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)
, DECODE(EXTN.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, BACC.BANK_ACCOUNT_NUM
, 'CREDITCARD'
, EXTN.CARD_NUMBER
, NULL)
, RM.NAME
, TER.NAME
, AOU.NAME
, '_DF:AR:RA_CUSTOMER_TRX:CT'
, '_DF:AR:RA_INTERFACE_HEADER:CT'
, '_DF:JG:JG_RA_CUSTOMER_TRX:CT'
, /* IDS */ CT.BATCH_ID
, CT.TERM_ID
, CT.BILL_TO_CONTACT_ID
, CT.SHIP_TO_CONTACT_ID
, CT.SOLD_TO_CONTACT_ID
, CT.BILL_TO_CUSTOMER_ID
, CT.SHIP_TO_CUSTOMER_ID
, CT.SOLD_TO_CUSTOMER_ID
, CT.PAYING_CUSTOMER_ID
, CT.CUST_TRX_TYPE_ID
, CT.PRIMARY_SALESREP_ID
, CT.BILL_TO_SITE_USE_ID
, CT.SHIP_TO_SITE_USE_ID
, CT.SOLD_TO_SITE_USE_ID
, CT.PAYING_SITE_USE_ID
, CT.REMIT_TO_ADDRESS_ID
, CT.PAYMENT_TRXN_EXTENSION_ID
, CT.RECEIPT_METHOD_ID
, CT.ORG_ID
, /* WHO COLUMNS */ CT.LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY
, CT.CREATION_DATE
, CT.CREATED_BY
FROM RA_CUSTOMER_TRX CT
, RA_BATCHES BAT
, 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
, IBY_TRXN_EXTENSIONS_V EXTN
, IBY_EXT_BANK_ACCOUNTS BACC
, AR_RECEIPT_METHODS RM
, RA_TERMS TER
, HR_ALL_ORGANIZATION_UNITS AOU
WHERE CT.BATCH_ID = BAT.BATCH_ID (+)
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 CT.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+)
AND CT.TERM_ID = TER.TERM_ID (+)
AND CT.ORG_ID = AOU.ORGANIZATION_ID (+)
AND CTT.TYPE = 'CM'
AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL WITH READ ONLY