View: AR_INTERIM_CR_LINES_V

Product:	AR - Receivables
Description:	(Release 115 Only)
Implementation/DBA Data:	APPS.AR_INTERIM_CR_LINES_V

View Text

SELECT ICRL.ROWID ROW_ID
, ICRL.CASH_RECEIPT_ID CASH_RECEIPT_ID
, ICRL.CASH_RECEIPT_LINE_ID CASH_RECEIPT_LINE_ID
, ICRL.PAYMENT_AMOUNT PAYMENT_AMOUNT
, ICRL.DISCOUNT_TAKEN DISCOUNT_TAKEN
, ICRL.DUE_DATE DUE_DATE
, ICRL.BATCH_ID BATCH_ID
, BT.NAME BATCH_NAME
, ICRL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, ICRL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, ICRL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
, ICRL.SOLD_TO_CUSTOMER SOLD_TO_CUSTOMER
, SUBSTRB(PARTY2.PARTY_NAME
, 1
, 50) SOLD_CUSTOMER_NAME
, CUST_ACCT2.ACCOUNT_NUMBER SOLD_CUSTOMER_NUMBER
, ICRL.CREATED_BY CREATED_BY
, ICRL.CREATION_DATE CREATION_DATE
, ICRL.LAST_UPDATED_BY LAST_UPDATED_BY
, ICRL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ICRL.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ICRL.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, ICRL.PROGRAM_ID PROGRAM_ID
, ICRL.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, ICRL.REQUEST_ID REQUEST_ID
, ICR.AMOUNT AMOUNT
, ICR.EXCHANGE_DATE EXCHANGE_DATE
, ICR.EXCHANGE_RATE EXCHANGE_RATE
, ICR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, ICR.GL_DATE GL_DATE
, ICR.GL_POSTED_DATE GL_POSTED_DATE
, ICR.PAY_FROM_CUSTOMER PAY_FROM_CUSTOMER
, ICR.RECEIPT_DATE RECEIPT_DATE
, ICR.RECEIPT_NUMBER RECEIPT_NUMBER
, ICR.CURRENCY_CODE CURRENCY_CODE
, ICR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, ICR.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCT_USE_ID
, ICR.SITE_USE_ID SITE_USE_ID
, ICR.SPECIAL_TYPE SPECIAL_TYPE
, ICRL.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, ICR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
, ICR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID
, ICR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, ICR.STATUS STATUS
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) PAY_CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER PAY_CUSTOMER_NUMBER
, SU.LOCATION BILLING_LOCATION
, PS.TRX_NUMBER TRX_NUMBER /* PICKUP PS TRX NUMBER */
, CI.CONS_BILLING_NUMBER BILLING_NUMBER
, TYPES.NAME TYPE_NAME
, TYPES.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG
, TYPES.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG
, TYPES.CREATION_SIGN CREATION_SIGN
, RM.NAME RECEIPT_METHOD_NAME
, BANKPARTY.PARTY_NAME BANK_NAME
, BRANCHPARTY.PARTY_ID REMITTANCE_BANK_BRANCH_ID
, CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, PS.TERM_ID TERM_ID
, DECODE(SIGN(ICRL.PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS.TERMS_SEQUENCE_NUMBER) TERMS_SEQUENCE_NUMBER
, DECODE(SIGN(ICRL.PAYMENT_SCHEDULE_ID)
, -1
, ICR.CURRENCY_CODE
, PS.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE
, NVL(PS.AMOUNT_DUE_REMAINING
, 0) AMOUNT_DUE_REMAINING
, PS.TRX_DATE TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, NVL(PS.DISCOUNT_TAKEN_EARNED
, 0) DISCOUNT_TAKEN_EARNED
, NVL(PS.DISCOUNT_TAKEN_UNEARNED
, 0) DISCOUNT_TAKEN_UNEARNED
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) AMOUNT_LINE_ITEMS_ORIGINAL
, CP.DISCOUNT_GRACE_DAYS GRACE_DAYS
, RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG
, RA_TERMS.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG
, ICRL.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, ICRL.ATTRIBUTE1 ATTRIBUTE1
, ICRL.ATTRIBUTE2 ATTRIBUTE2
, ICRL.ATTRIBUTE3 ATTRIBUTE3
, ICRL.ATTRIBUTE4 ATTRIBUTE4
, ICRL.ATTRIBUTE5 ATTRIBUTE5
, ICRL.ATTRIBUTE6 ATTRIBUTE6
, ICRL.ATTRIBUTE7 ATTRIBUTE7
, ICRL.ATTRIBUTE8 ATTRIBUTE8
, ICRL.ATTRIBUTE9 ATTRIBUTE9
, ICRL.ATTRIBUTE10 ATTRIBUTE10
, ICRL.ATTRIBUTE11 ATTRIBUTE11
, ICRL.ATTRIBUTE12 ATTRIBUTE12
, ICRL.ATTRIBUTE13 ATTRIBUTE13
, ICRL.ATTRIBUTE14 ATTRIBUTE14
, ICRL.ATTRIBUTE15 ATTRIBUTE15
, ICRL.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, ICRL.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, ICRL.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, ICRL.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, ICRL.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, ICRL.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, ICRL.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, ICRL.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, ICRL.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, ICRL.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, ICRL.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, ICRL.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, ICRL.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, ICRL.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, ICRL.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, ICRL.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, ICRL.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, ICRL.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, ICRL.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, ICRL.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, ICRL.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, NVL(ICRL.AMOUNT_APPLIED_FROM
, ICRL.PAYMENT_AMOUNT) AMOUNT_APPLIED_FROM
, ICRL.TRANS_TO_RECEIPT_RATE TRANS_TO_RECEIPT_RATE
, ICR.APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, ICRL.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE_MEANING
, ICRL.CUSTOMER_REASON
, ICRL.CUSTOMER_REFERENCE
, ICRL.APPLIED_REC_APP_ID
, DECODE(NVL(ICRL.CUSTOMER_TRX_ID
, '-1')
, '-1'
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, ARP_DEDUCTION.OVERAPPLICATION_INDICATOR (PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING
, ICRL.PAYMENT_AMOUNT ) ) ) OVERAPPLICATION_INDICATOR
, DECODE(PS.CLASS
, 'PMT'
, DECODE(ICRL.APPLIED_REC_APP_ID
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, 'UNAPP')
, DECODE(RA.STATUS
, 'OTHER ACC'
, 'CLAIM INVESTIGATION'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, RA.STATUS) ) ) ) APPLICATION_STATUS
, DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)) CLASS
, PS.CLASS CLASS_SHORT_NAME
, PS.CASH_RECEIPT_ID APPLIED_CASH_RECEIPT_ID
FROM AR_INTERIM_CASH_RECEIPT_LINES ICRL
, AR_INTERIM_CASH_RECEIPTS ICR
, AR_RECEIPT_METHODS RM
, AR_PAYMENT_SCHEDULES PS
, RA_TERMS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT2
, HZ_PARTIES PARTY2
, RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES TYPES
, HZ_CUST_SITE_USES SU
, AR_BATCHES BT
, HZ_CUSTOMER_PROFILES CP
, AR_CONS_INV CI
, AR_RECEIVABLE_APPLICATIONS RA
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
WHERE ICR.CASH_RECEIPT_ID(+) = ICRL.CASH_RECEIPT_ID
AND RM.RECEIPT_METHOD_ID(+) = ICR.RECEIPT_METHOD_ID
AND PS.PAYMENT_SCHEDULE_ID(+) = ICRL.PAYMENT_SCHEDULE_ID
AND PS.CLASS NOT IN ('GUAR')
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.RESERVED_TYPE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.RESERVED_VALUE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = ICR.PAY_FROM_CUSTOMER
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND CUST_ACCT2.CUST_ACCOUNT_ID(+) = ICRL.SOLD_TO_CUSTOMER
AND CUST_ACCT2.PARTY_ID = PARTY2.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = ICR.SITE_USE_ID
AND CT.CUSTOMER_TRX_ID(+) = ICRL.CUSTOMER_TRX_ID
AND RA_TERMS.TERM_ID(+) = PS.TERM_ID
AND TYPES.CUST_TRX_TYPE_ID(+) = CT.CUST_TRX_TYPE_ID
AND BT.BATCH_ID(+) = ICRL.BATCH_ID
AND CP.CUST_ACCOUNT_ID(+) = ICRL.SOLD_TO_CUSTOMER
AND CP.SITE_USE_ID IS NULL
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND RA.RECEIVABLE_APPLICATION_ID(+)=ICRL.APPLIED_REC_APP_ID
AND CBA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID(+)
AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID (+)
AND BRREL.SUBJECT_ID (+) = BRANCHPARTY.PARTY_ID
AND BRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND BRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND BA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)
AND BA.BANK_ACCT_USE_ID(+) = ICR.REMIT_BANK_ACCT_USE_ID

Columns