VIEW: APPS.AR_INTERIM_CR_LINES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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