eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_PAYMENT_SCHEDULES_PMT_V

Source

View Text - Preformatted


SELECT PS.ROWID, PS.PAYMENT_SCHEDULE_ID, PS.TRX_NUMBER, PS.TRX_DATE, PS.TERMS_SEQUENCE_NUMBER, PS.INVOICE_CURRENCY_CODE, PS.EXCHANGE_RATE, PS.GL_DATE, PS.ATTRIBUTE_CATEGORY, PS.ATTRIBUTE1, PS.ATTRIBUTE2, PS.ATTRIBUTE3, PS.ATTRIBUTE4, PS.ATTRIBUTE5, PS.ATTRIBUTE6, PS.ATTRIBUTE7, PS.ATTRIBUTE8, PS.ATTRIBUTE9, PS.ATTRIBUTE10, PS.ATTRIBUTE11, PS.ATTRIBUTE12, PS.ATTRIBUTE13, PS.ATTRIBUTE14, PS.ATTRIBUTE15, /* */ PS.CUSTOMER_ID, PS.CUSTOMER_SITE_USE_ID, PS.CUSTOMER_TRX_ID, PS.CUST_TRX_TYPE_ID, PS.CASH_RECEIPT_ID, PS.CLASS, /* Bug 2264551: Removed the DECODE for the case when 'LOOKUP_CODE' is 'Y'*/ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.STATUS, 'OP'), /* STATUS */ ARPT_SQL_FUNC_UTIL.get_lookup_meaning('RECEIPT_CREATION_STATUS',CRH_CURRENT.STATUS), CRH_CURRENT.STATUS, PS.RECEIPT_CONFIRMED_FLAG, PS.SELECTED_FOR_RECEIPT_BATCH_ID, PS.COLLECTOR_LAST, PS.FOLLOW_UP_CODE_LAST, NULL, /* PS.IN_COLLECTION */ PS.TERM_ID, /* */ substrb(PARTY.PARTY_NAME,1,50), CUST_ACCT.ACCOUNT_NUMBER, SU.LOCATION, BS.NAME, RM.NAME, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS), CR.DOC_SEQUENCE_VALUE, NULL, TO_NUMBER(NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_NUMBER(NULL), PS.DUE_DATE, /* Bug 2264551: Removed the DECODE for the case when 'LOOKUP_CODE' is 'Y'*/ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.AMOUNT_DUE_ORIGINAL, -1 * ps.amount_due_original), /* AMOUNT_DUE_ORIGINAL */ NULL, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.AMOUNT_DUE_REMAINING, -1 * ps.amount_due_original), /* AMOUNT_DUE_REMAINING */ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.ACCTD_AMOUNT_DUE_REMAINING, -1 * round(ps.amount_due_original * nvl(cr.exchange_rate,1), fc.precision)), /* ACCTD_AMT_DUE_REM */ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N', ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INVOICE_TRX_STATUS',PS.STATUS), ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INVOICE_TRX_STATUS','OP')),/*ajay*/ /* AL_STATUS_MEANING */ /* Bug 2264551: Code fix ends */ DECODE(PS.STATUS, 'OP', to_date(NULL), PS.ACTUAL_DATE_CLOSED), DECODE(PS.STATUS, 'OP', to_date(NULL), PS.GL_DATE_CLOSED), PS.AMOUNT_LINE_ITEMS_ORIGINAL, TO_NUMBER(NULL), /* ACCTD_AMOUNT_LINE_ITEMS_ORIG */ PS.AMOUNT_LINE_ITEMS_REMAINING, TO_NUMBER(NULL), /* ACCTD_AMOUNT_LINE_ITEMS_REMAIN */ PS.TAX_ORIGINAL, TO_NUMBER(NULL), /* ACCTD_TAX_ORIGINAL */ PS.TAX_REMAINING, TO_NUMBER(NULL), /* ACCTD_TAX_REMAINING */ PS.FREIGHT_ORIGINAL, TO_NUMBER(NULL), /* ACCTD_FREIGHT_ORIGINAL */ PS.FREIGHT_REMAINING, TO_NUMBER(NULL), /* ACCTD_FREIGHT_REMAINING */ PS.AMOUNT_ADJUSTED, TO_NUMBER(NULL), /* ACCTD_AMOUNT_ADJUSTED */ PS.AMOUNT_ADJUSTED_PENDING, TO_NUMBER(NULL), /* ACCTD_AMOUNT_ADJUSTED_PENDING */ PS.RECEIVABLES_CHARGES_CHARGED, TO_NUMBER(NULL), /* ACCTD_REC_CHARGES_CHARGED */ PS.RECEIVABLES_CHARGES_REMAINING, TO_NUMBER(NULL), /* ACCTD_REC_CHARGES_REMAINING */ PS.AMOUNT_APPLIED, TO_NUMBER(NULL), /* ACCTD_AMOUNT_APPLIED */ PS.AMOUNT_CREDITED, TO_NUMBER(NULL), /* ACCTD_AMOUNT_CREDITED */ PS.AMOUNT_IN_DISPUTE, TO_NUMBER(NULL), /* ACCTD_AMOUNT_IN_DISPUTE */ PS.DISPUTE_DATE, PS.DISCOUNT_TAKEN_EARNED, TO_NUMBER(NULL), /* ACCTD_DISCOUNT_TAKEN_EARNED */ PS.DISCOUNT_TAKEN_UNEARNED, TO_NUMBER(NULL), /* ACCTD_DISCOUNT_TAKEN_UNEARNED */ NULL, /* AL_SELECTED_FOR_RECEIPT.MEANING,*/ NULL, /* AL_SELECTED_FOR_RECEIPT.LOOKUP_CODE,*/ ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO',PS.RECEIPT_CONFIRMED_FLAG), NULL, /* CR_SELECTED_RECEIPT_NUMBER */ NULL, /* ARB_SELECTED_NAME */ NULL, /* ARB_SELECTED_DATE */ DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE), /* DAYS_PAST_DUE */ NULL, /* AL_IN_COLLECTION.MEANING OF PREVIOUS PART */ PS.CALL_DATE_LAST, NULL, PS.PROMISE_AMOUNT_LAST, TO_NUMBER(NULL), /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST, NULL, PS.FOLLOW_UP_DATE_LAST, NULL, NULL, NULL, NULL, CONS.CONS_BILLING_NUMBER, PS.STAGED_DUNNING_LEVEL, PS.DUNNING_LEVEL_OVERRIDE_DATE /* BOE */ ,ARB_REMIT.REMIT_METHOD_CODE /* REMITTANCE_METHOD */ ,PS.GLOBAL_ATTRIBUTE_CATEGORY ,PS.GLOBAL_ATTRIBUTE1 ,PS.GLOBAL_ATTRIBUTE2 ,PS.GLOBAL_ATTRIBUTE3 ,PS.GLOBAL_ATTRIBUTE4 ,PS.GLOBAL_ATTRIBUTE5 ,PS.GLOBAL_ATTRIBUTE6 ,PS.GLOBAL_ATTRIBUTE7 ,PS.GLOBAL_ATTRIBUTE8 ,PS.GLOBAL_ATTRIBUTE9 ,PS.GLOBAL_ATTRIBUTE10 ,PS.GLOBAL_ATTRIBUTE11 ,PS.GLOBAL_ATTRIBUTE12 ,PS.GLOBAL_ATTRIBUTE13 ,PS.GLOBAL_ATTRIBUTE14 ,PS.GLOBAL_ATTRIBUTE15 ,PS.GLOBAL_ATTRIBUTE16 ,PS.GLOBAL_ATTRIBUTE17 ,PS.GLOBAL_ATTRIBUTE18 ,PS.GLOBAL_ATTRIBUTE19 ,PS.GLOBAL_ATTRIBUTE20 ,PS.CONS_INV_ID ,PS.LAST_UPDATED_BY ,PS.LAST_UPDATE_DATE ,PS.LAST_UPDATE_LOGIN ,PS.CREATED_BY , PS.CREATION_DATE , CR.COMMENTS , CR.LEGAL_ENTITY_ID , PS.ORG_ID , NULL /* Bug 6355096 */ FROM /* SHIV RAGUNAT 10/10/96 ADDED THESE 3 TABLES TO GET THE */ /* FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ ar_lookups al_risk_receipt, ar_cons_inv_all cons, ar_receipt_methods rm, ar_batch_sources_all bs, ar_batches_all arb2, ar_cash_receipt_history_all crh, ar_cash_receipt_history_all crh_current, /* current_record */ ar_cash_receipts_all cr, hz_cust_site_uses_all su, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules ps, ar_cash_receipt_history_all crh_remit, ar_batches_all arb_remit, fnd_currencies fc WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+) AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+) AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+) AND PS.ORG_ID = SU.ORG_ID (+) AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND PS.ORG_ID = CR.ORG_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND CR.ORG_ID = CRH.ORG_ID AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y' AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID AND CRH.BATCH_ID = ARB2.BATCH_ID (+) AND CRH.ORG_ID = ARB2.ORG_ID (+) AND ARB2.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID(+) AND ARB2.ORG_ID = BS.ORG_ID(+) /* 6/25/96 SRAGUNAT , ADDED THIS FOR THE STATE FIELD */ AND CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID AND CRH_CURRENT.CURRENT_RECORD_FLAG = 'Y' AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+) AND PS.ORG_ID = CONS.ORG_ID (+) AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+) AND CR.ORG_ID = CRH_REMIT.ORG_ID(+) AND CRH_REMIT.STATUS(+) = 'REMITTED' AND CRH_REMIT.BATCH_ID = ARB_REMIT.BATCH_ID(+) AND CRH_REMIT.ORG_ID = ARB_REMIT.ORG_ID(+) AND FC.CURRENCY_CODE = CR.CURRENCY_CODE /*Bug 2423740: Added folowing AND clause to avoid duplicacy of same receipt in AccountDetails window having more than one REMITTED status */ AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y' /* Added condition for Bug 2111509 */ /* Bug 2264551: Commented the next 'AND' condition introduced in fix for Bug 2111509 AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y' */ AND AL_RISK_RECEIPT.LOOKUP_TYPE = 'YES/NO' /* bug 1668685 : Added decode for status CLEARED */ /* Modified the Decode for CLEARED for Bug 2111509 */ /* Bug 2264551: Modified the DECODE to populate view correctly */ AND AL_RISK_RECEIPT.LOOKUP_CODE IN ( DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS,'X'), 'OP','X','N'), DECODE( NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK,'N'),'N', 'N', DECODE( CRH_CURRENT.STATUS,'REVERSED', 'N', DECODE(CRH_CURRENT.FACTOR_FLAG,'N', DECODE(CRH_CURRENT.STATUS,'CLEARED','N',DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS,'X'),'CL','X','Y')), DECODE(CRH_CURRENT.STATUS,'RISK_ELIMINATED','N',DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS,'X'),'CL','X','Y')))))) /* Bug 2264551: Code fix ends */ AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL AND PS.STATUS = DECODE( NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK,'N'), 'Y', DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS,'X'),'X',PS.STATUS,'OP', DECODE( CRH_CURRENT.STATUS,'REVERSED', 'OP', DECODE(CRH_CURRENT.FACTOR_FLAG,'N', DECODE(CRH_CURRENT.STATUS,'CLEARED','OP',PS.STATUS), DECODE(CRH_CURRENT.STATUS,'RISK_ELIMINATED','OP',PS.STATUS))), 'CL', NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)), NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS))

View Text - HTML Formatted

SELECT PS.ROWID
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, PS.TRX_DATE
, PS.TERMS_SEQUENCE_NUMBER
, PS.INVOICE_CURRENCY_CODE
, PS.EXCHANGE_RATE
, PS.GL_DATE
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* */ PS.CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID
, PS.CUST_TRX_TYPE_ID
, PS.CASH_RECEIPT_ID
, PS.CLASS
, /* BUG 2264551: REMOVED THE DECODE FOR THE CASE WHEN 'LOOKUP_CODE' IS 'Y'*/ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.STATUS
, 'OP')
, /* STATUS */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RECEIPT_CREATION_STATUS'
, CRH_CURRENT.STATUS)
, CRH_CURRENT.STATUS
, PS.RECEIPT_CONFIRMED_FLAG
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, PS.COLLECTOR_LAST
, PS.FOLLOW_UP_CODE_LAST
, NULL
, /* PS.IN_COLLECTION */ PS.TERM_ID
, /* */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, SU.LOCATION
, BS.NAME
, RM.NAME
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, CR.DOC_SEQUENCE_VALUE
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, PS.DUE_DATE
, /* BUG 2264551: REMOVED THE DECODE FOR THE CASE WHEN 'LOOKUP_CODE' IS 'Y'*/ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.AMOUNT_DUE_ORIGINAL
, -1 * PS.AMOUNT_DUE_ORIGINAL)
, /* AMOUNT_DUE_ORIGINAL */ NULL
, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.AMOUNT_DUE_REMAINING
, -1 * PS.AMOUNT_DUE_ORIGINAL)
, /* AMOUNT_DUE_REMAINING */ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.ACCTD_AMOUNT_DUE_REMAINING
, -1 * ROUND(PS.AMOUNT_DUE_ORIGINAL * NVL(CR.EXCHANGE_RATE
, 1)
, FC.PRECISION))
, /* ACCTD_AMT_DUE_REM */ DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICE_TRX_STATUS'
, PS.STATUS)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICE_TRX_STATUS'
, 'OP'))
, /*AJAY*/ /* AL_STATUS_MEANING */ /* BUG 2264551: CODE FIX ENDS */ DECODE(PS.STATUS
, 'OP'
, TO_DATE(NULL)
, PS.ACTUAL_DATE_CLOSED)
, DECODE(PS.STATUS
, 'OP'
, TO_DATE(NULL)
, PS.GL_DATE_CLOSED)
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_LINE_ITEMS_ORIG */ PS.AMOUNT_LINE_ITEMS_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_LINE_ITEMS_REMAIN */ PS.TAX_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_TAX_ORIGINAL */ PS.TAX_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_TAX_REMAINING */ PS.FREIGHT_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_FREIGHT_ORIGINAL */ PS.FREIGHT_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_FREIGHT_REMAINING */ PS.AMOUNT_ADJUSTED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_ADJUSTED */ PS.AMOUNT_ADJUSTED_PENDING
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_ADJUSTED_PENDING */ PS.RECEIVABLES_CHARGES_CHARGED
, TO_NUMBER(NULL)
, /* ACCTD_REC_CHARGES_CHARGED */ PS.RECEIVABLES_CHARGES_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_REC_CHARGES_REMAINING */ PS.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_APPLIED */ PS.AMOUNT_CREDITED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_CREDITED */ PS.AMOUNT_IN_DISPUTE
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_IN_DISPUTE */ PS.DISPUTE_DATE
, PS.DISCOUNT_TAKEN_EARNED
, TO_NUMBER(NULL)
, /* ACCTD_DISCOUNT_TAKEN_EARNED */ PS.DISCOUNT_TAKEN_UNEARNED
, TO_NUMBER(NULL)
, /* ACCTD_DISCOUNT_TAKEN_UNEARNED */ NULL
, /* AL_SELECTED_FOR_RECEIPT.MEANING
, */ NULL
, /* AL_SELECTED_FOR_RECEIPT.LOOKUP_CODE
, */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, PS.RECEIPT_CONFIRMED_FLAG)
, NULL
, /* CR_SELECTED_RECEIPT_NUMBER */ NULL
, /* ARB_SELECTED_NAME */ NULL
, /* ARB_SELECTED_DATE */ DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE)
, /* DAYS_PAST_DUE */ NULL
, /* AL_IN_COLLECTION.MEANING OF PREVIOUS PART */ PS.CALL_DATE_LAST
, NULL
, PS.PROMISE_AMOUNT_LAST
, TO_NUMBER(NULL)
, /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST
, NULL
, PS.FOLLOW_UP_DATE_LAST
, NULL
, NULL
, NULL
, NULL
, CONS.CONS_BILLING_NUMBER
, PS.STAGED_DUNNING_LEVEL
, PS.DUNNING_LEVEL_OVERRIDE_DATE /* BOE */
, ARB_REMIT.REMIT_METHOD_CODE /* REMITTANCE_METHOD */
, PS.GLOBAL_ATTRIBUTE_CATEGORY
, PS.GLOBAL_ATTRIBUTE1
, PS.GLOBAL_ATTRIBUTE2
, PS.GLOBAL_ATTRIBUTE3
, PS.GLOBAL_ATTRIBUTE4
, PS.GLOBAL_ATTRIBUTE5
, PS.GLOBAL_ATTRIBUTE6
, PS.GLOBAL_ATTRIBUTE7
, PS.GLOBAL_ATTRIBUTE8
, PS.GLOBAL_ATTRIBUTE9
, PS.GLOBAL_ATTRIBUTE10
, PS.GLOBAL_ATTRIBUTE11
, PS.GLOBAL_ATTRIBUTE12
, PS.GLOBAL_ATTRIBUTE13
, PS.GLOBAL_ATTRIBUTE14
, PS.GLOBAL_ATTRIBUTE15
, PS.GLOBAL_ATTRIBUTE16
, PS.GLOBAL_ATTRIBUTE17
, PS.GLOBAL_ATTRIBUTE18
, PS.GLOBAL_ATTRIBUTE19
, PS.GLOBAL_ATTRIBUTE20
, PS.CONS_INV_ID
, PS.LAST_UPDATED_BY
, PS.LAST_UPDATE_DATE
, PS.LAST_UPDATE_LOGIN
, PS.CREATED_BY
, PS.CREATION_DATE
, CR.COMMENTS
, CR.LEGAL_ENTITY_ID
, PS.ORG_ID
, NULL /* BUG 6355096 */
FROM /* SHIV RAGUNAT 10/10/96 ADDED THESE 3 TABLES TO GET THE */ /* FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ AR_LOOKUPS AL_RISK_RECEIPT
, AR_CONS_INV_ALL CONS
, AR_RECEIPT_METHODS RM
, AR_BATCH_SOURCES_ALL BS
, AR_BATCHES_ALL ARB2
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT
, /* CURRENT_RECORD */ AR_CASH_RECEIPTS_ALL CR
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
, AR_CASH_RECEIPT_HISTORY_ALL CRH_REMIT
, AR_BATCHES_ALL ARB_REMIT
, FND_CURRENCIES FC
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID (+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
AND PS.ORG_ID = SU.ORG_ID (+)
AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND PS.ORG_ID = CR.ORG_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.ORG_ID = CRH.ORG_ID
AND CRH.FIRST_POSTED_RECORD_FLAG = 'Y'
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
AND CRH.BATCH_ID = ARB2.BATCH_ID (+)
AND CRH.ORG_ID = ARB2.ORG_ID (+)
AND ARB2.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID(+)
AND ARB2.ORG_ID = BS.ORG_ID(+) /* 6/25/96 SRAGUNAT
, ADDED THIS FOR THE STATE FIELD */
AND CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = 'Y'
AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
AND PS.ORG_ID = CONS.ORG_ID (+)
AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+)
AND CR.ORG_ID = CRH_REMIT.ORG_ID(+)
AND CRH_REMIT.STATUS(+) = 'REMITTED'
AND CRH_REMIT.BATCH_ID = ARB_REMIT.BATCH_ID(+)
AND CRH_REMIT.ORG_ID = ARB_REMIT.ORG_ID(+)
AND FC.CURRENCY_CODE = CR.CURRENCY_CODE /*BUG 2423740: ADDED FOLOWING
AND CLAUSE TO AVOID DUPLICACY OF SAME RECEIPT IN ACCOUNTDETAILS WINDOW HAVING MORE THAN ONE REMITTED STATUS */
AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y' /* ADDED CONDITION FOR BUG 2111509 */ /* BUG 2264551: COMMENTED THE NEXT 'AND' CONDITION INTRODUCED IN FIX FOR BUG 2111509
AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y' */
AND AL_RISK_RECEIPT.LOOKUP_TYPE = 'YES/NO' /* BUG 1668685 : ADDED DECODE FOR STATUS CLEARED */ /* MODIFIED THE DECODE FOR CLEARED FOR BUG 2111509 */ /* BUG 2264551: MODIFIED THE DECODE TO POPULATE VIEW CORRECTLY */
AND AL_RISK_RECEIPT.LOOKUP_CODE IN ( DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, 'X')
, 'OP'
, 'X'
, 'N')
, DECODE( NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK
, 'N')
, 'N'
, 'N'
, DECODE( CRH_CURRENT.STATUS
, 'REVERSED'
, 'N'
, DECODE(CRH_CURRENT.FACTOR_FLAG
, 'N'
, DECODE(CRH_CURRENT.STATUS
, 'CLEARED'
, 'N'
, DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, 'X')
, 'CL'
, 'X'
, 'Y'))
, DECODE(CRH_CURRENT.STATUS
, 'RISK_ELIMINATED'
, 'N'
, DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, 'X')
, 'CL'
, 'X'
, 'Y')))))) /* BUG 2264551: CODE FIX ENDS */
AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
AND PS.STATUS = DECODE( NVL(ARP_VIEW_CONSTANTS.GET_INCL_RECEIPTS_AT_RISK
, 'N')
, 'Y'
, DECODE( NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, 'X')
, 'X'
, PS.STATUS
, 'OP'
, DECODE( CRH_CURRENT.STATUS
, 'REVERSED'
, 'OP'
, DECODE(CRH_CURRENT.FACTOR_FLAG
, 'N'
, DECODE(CRH_CURRENT.STATUS
, 'CLEARED'
, 'OP'
, PS.STATUS)
, DECODE(CRH_CURRENT.STATUS
, 'RISK_ELIMINATED'
, 'OP'
, PS.STATUS)))
, 'CL'
, NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, PS.STATUS))
, NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, PS.STATUS))