VIEW: APPS.AR_PAYMENT_SCHEDULES_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 , PS.STATUS , NULL , NULL , 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 , party.party_name , CUST_ACCT.ACCOUNT_NUMBER , SU.LOCATION , BS.NAME , CTT.NAME ,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS) , CT.DOC_SEQUENCE_VALUE , CT.PURCHASE_ORDER , CT.PREVIOUS_CUSTOMER_TRX_ID , CT.INTERFACE_HEADER_CONTEXT , CT.INTERFACE_HEADER_ATTRIBUTE1 , CT.INTERFACE_HEADER_ATTRIBUTE2 , CT.INTERFACE_HEADER_ATTRIBUTE3 , CT.INTERFACE_HEADER_ATTRIBUTE4 , CT.INTERFACE_HEADER_ATTRIBUTE5 , CT.INTERFACE_HEADER_ATTRIBUTE6 , CT.INTERFACE_HEADER_ATTRIBUTE7 , CT.INTERFACE_HEADER_ATTRIBUTE8 , CT.INTERFACE_HEADER_ATTRIBUTE9 , CT.INTERFACE_HEADER_ATTRIBUTE10 , CT.INTERFACE_HEADER_ATTRIBUTE11 , CT.INTERFACE_HEADER_ATTRIBUTE12 , CT.INTERFACE_HEADER_ATTRIBUTE13 , CT.INTERFACE_HEADER_ATTRIBUTE14 , CT.INTERFACE_HEADER_ATTRIBUTE15 , PS.DUE_DATE - PS.TRX_DATE , PS.DUE_DATE , PS.AMOUNT_DUE_ORIGINAL , NULL , PS.AMOUNT_DUE_REMAINING , PS.ACCTD_AMOUNT_DUE_REMAINING , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INVOICE_TRX_STATUS',PS.STATUS) , 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 */ , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO',DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID, NULL, 'N','Y')) , DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID, NULL, 'N','Y') /*SELECTED_FOR_RECEIPT_CODE*/ , NULL , /* AL_RECEIPT_CONFIRMED.MEANING */ 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) ,SUBSTR(ARPT_SQL_FUNC_UTIL.GET_DUNNING_DATE_LAST(PS.PAYMENT_SCHEDULE_ID),1,11) , /* DUNNING_DATE_LAST MAX(ARC.CORRESPONDENCE_DATE)*/ PS.CALL_DATE_LAST , PS.PROMISE_AMOUNT_LAST , TO_NUMBER(NULL) , /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('FOLLOW_UP',PS.FOLLOW_UP_CODE_LAST) , PS.FOLLOW_UP_DATE_LAST , NULL , /* AL_IN_COLLECTION.MEANING */ ARPT_SQL_FUNC_UTIL.get_term_details(PS.TERM_ID,'NAME') , SUBSTR(ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE (CT.CUSTOMER_TRX_ID, CTT.TYPE),1,30) , /* CHARGEBACK_INVOICE_NUM */ SUBSTR(ARPT_SQL_FUNC_UTIL.GET_BS_NAME_FOR_CB_INVOICE(CTT.TYPE, CT.CUSTOMER_TRX_ID),1,50) , /* CHARGEBACK_BATCH_SOURCE */ CONS.CONS_BILLING_NUMBER , PS.STAGED_DUNNING_LEVEL , PS.DUNNING_LEVEL_OVERRIDE_DATE , NULL /* 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 , CT.COMMENTS , CT.LEGAL_ENTITY_ID , CT.BILLING_DATE , PS.ORG_ID FROM /* SHIV RAGUNAT 10/10/96 ADDED THE FOLLOWING 3 TABLES TO GET */ /* THE FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ ar_cons_inv_all cons, ra_cust_trx_types_all ctt, ra_batch_sources_all bs, ra_customer_trx_all ct, hz_cust_site_uses_all su, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules ps 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.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+) AND PS.ORG_ID = CONS.ORG_ID(+) AND CTT.ORG_ID = PS.ORG_ID AND BS.ORG_ID =PS.ORG_ID AND CT.ORG_ID =PS.ORG_ID AND SU.ORG_ID =PS.ORG_ID AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS) UNION ALL 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, /* */ PARTY.PARTY_NAME, 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')), /* 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, 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 */ ,DECODE(crh_current.status, 'REMITTED', rc.remit_method_code, NULL) /* 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 ,null ,PS.ORG_ID 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, fnd_currencies fc, ar_receipt_classes rc 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.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_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 rc.receipt_class_id = rm.receipt_class_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 */ /* Bug 2111509 Added for handling two REMITTED history statuses*/ /* 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 2111509 Modified the decode statement for CLEARED */ /* Bug 1612243 Account details window showing duplicate receipts in risk.*/ /* 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 CRH.ORG_ID=PS.ORG_ID AND CRH_CURRENT.ORG_ID=PS.ORG_ID AND CR.ORG_ID=PS.ORG_ID AND PS.ORG_ID=SU.ORG_ID(+) /* 6772271 */ 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)) UNION ALL 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, /* */ NULL, NULL, 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')), /* 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, 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 */ ,DECODE(crh_current.status, 'REMITTED', rc.remit_method_code, NULL) /* 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 ,null ,PS.ORG_ID 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, ar_payment_schedules ps, fnd_currencies fc, ar_receipt_classes rc WHERE PS.CUSTOMER_ID IS NULL AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+) AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_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 rc.receipt_class_id = rm.receipt_class_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 */ /* Bug 2111509 Added for handling two REMITTED history statuses*/ /* 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 2111509 Modified the decode statement for CLEARED */ /* Bug 1612243 Account details window showing duplicate receipts in risk.*/ /* 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 CRH.ORG_ID=PS.ORG_ID AND CRH_CURRENT.ORG_ID=PS.ORG_ID AND CR.ORG_ID=PS.ORG_ID AND PS.ORG_ID=SU.ORG_ID(+) /* 6772271 */ 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
, PS.STATUS
, NULL
, NULL
, 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
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, SU.LOCATION
, BS.NAME
, CTT.NAME
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, CT.DOC_SEQUENCE_VALUE
, CT.PURCHASE_ORDER
, CT.PREVIOUS_CUSTOMER_TRX_ID
, CT.INTERFACE_HEADER_CONTEXT
, CT.INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15
, PS.DUE_DATE - PS.TRX_DATE
, PS.DUE_DATE
, PS.AMOUNT_DUE_ORIGINAL
, NULL
, PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICE_TRX_STATUS'
, PS.STATUS)
, 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 */
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID
, NULL
, 'N'
, 'Y'))
, DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID
, NULL
, 'N'
, 'Y') /*SELECTED_FOR_RECEIPT_CODE*/
, NULL
, /* AL_RECEIPT_CONFIRMED.MEANING */ 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)
, SUBSTR(ARPT_SQL_FUNC_UTIL.GET_DUNNING_DATE_LAST(PS.PAYMENT_SCHEDULE_ID)
, 1
, 11)
, /* DUNNING_DATE_LAST MAX(ARC.CORRESPONDENCE_DATE)*/ PS.CALL_DATE_LAST
, PS.PROMISE_AMOUNT_LAST
, TO_NUMBER(NULL)
, /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('FOLLOW_UP'
, PS.FOLLOW_UP_CODE_LAST)
, PS.FOLLOW_UP_DATE_LAST
, NULL
, /* AL_IN_COLLECTION.MEANING */ ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS.TERM_ID
, 'NAME')
, SUBSTR(ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE (CT.CUSTOMER_TRX_ID
, CTT.TYPE)
, 1
, 30)
, /* CHARGEBACK_INVOICE_NUM */ SUBSTR(ARPT_SQL_FUNC_UTIL.GET_BS_NAME_FOR_CB_INVOICE(CTT.TYPE
, CT.CUSTOMER_TRX_ID)
, 1
, 50)
, /* CHARGEBACK_BATCH_SOURCE */ CONS.CONS_BILLING_NUMBER
, PS.STAGED_DUNNING_LEVEL
, PS.DUNNING_LEVEL_OVERRIDE_DATE
, NULL /* 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
, CT.COMMENTS
, CT.LEGAL_ENTITY_ID
, CT.BILLING_DATE
, PS.ORG_ID
FROM /* SHIV RAGUNAT 10/10/96 ADDED THE FOLLOWING 3 TABLES TO GET */ /* THE FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ AR_CONS_INV_ALL CONS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_BATCH_SOURCES_ALL BS
, RA_CUSTOMER_TRX_ALL CT
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
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.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
AND PS.ORG_ID = CONS.ORG_ID(+)
AND CTT.ORG_ID = PS.ORG_ID
AND BS.ORG_ID =PS.ORG_ID
AND CT.ORG_ID =PS.ORG_ID
AND SU.ORG_ID =PS.ORG_ID
AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, PS.STATUS) UNION ALL 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
, /* */ PARTY.PARTY_NAME
, 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'))
, /* 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
, 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 */
, DECODE(CRH_CURRENT.STATUS
, 'REMITTED'
, RC.REMIT_METHOD_CODE
, NULL) /* 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
, NULL
, PS.ORG_ID
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
, FND_CURRENCIES FC
, AR_RECEIPT_CLASSES RC
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.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_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 RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_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 */ /* BUG 2111509 ADDED FOR HANDLING TWO REMITTED HISTORY STATUSES*/ /* 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 2111509 MODIFIED THE DECODE STATEMENT FOR CLEARED */ /* BUG 1612243 ACCOUNT DETAILS WINDOW SHOWING DUPLICATE RECEIPTS IN RISK.*/ /* 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 CRH.ORG_ID=PS.ORG_ID
AND CRH_CURRENT.ORG_ID=PS.ORG_ID
AND CR.ORG_ID=PS.ORG_ID
AND PS.ORG_ID=SU.ORG_ID(+) /* 6772271 */
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)) UNION ALL 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
, /* */ NULL
, NULL
, 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'))
, /* 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
, 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 */
, DECODE(CRH_CURRENT.STATUS
, 'REMITTED'
, RC.REMIT_METHOD_CODE
, NULL) /* 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
, NULL
, PS.ORG_ID
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
, AR_PAYMENT_SCHEDULES PS
, FND_CURRENCIES FC
, AR_RECEIPT_CLASSES RC
WHERE PS.CUSTOMER_ID IS NULL
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_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 RC.RECEIPT_CLASS_ID = RM.RECEIPT_CLASS_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 */ /* BUG 2111509 ADDED FOR HANDLING TWO REMITTED HISTORY STATUSES*/ /* 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 2111509 MODIFIED THE DECODE STATEMENT FOR CLEARED */ /* BUG 1612243 ACCOUNT DETAILS WINDOW SHOWING DUPLICATE RECEIPTS IN RISK.*/ /* 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 CRH.ORG_ID=PS.ORG_ID
AND CRH_CURRENT.ORG_ID=PS.ORG_ID
AND CR.ORG_ID=PS.ORG_ID
AND PS.ORG_ID=SU.ORG_ID(+) /* 6772271 */
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))