View: AR_PAYMENT_SCHEDULES_PMT_V

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

View Text

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 ( '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'
, 'Y')
, DECODE(CRH_CURRENT.STATUS
, 'RISK_ELIMINATED'
, 'N'
, 'Y') )))) /* BUG 2264551: CODE FIX ENDS */
AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, PS.STATUS)

Columns