View: ARFV_CREDIT_MEMO_ARS

Product:	AR - Receivables
Description:	This shows information about documents that partially or fully reduce the original balance due for an invoice.
Implementation/DBA Data:	APPS.ARFV_CREDIT_MEMO_ARS

View Text

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
, CRCT.TRX_NUMBER
, 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.PREVIOUS_CUSTOMER_TRX_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
, RA_CUSTOMER_TRX CRCT
, 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.PREVIOUS_CUSTOMER_TRX_ID = CRCT.CUSTOMER_TRX_ID (+)
AND CT.ORG_ID = AOU.ORGANIZATION_ID (+)
AND CTT.TYPE = 'CM'
AND CT.PREVIOUS_CUSTOMER_TRX_ID > -9999999 WITH READ ONLY

Columns