oracle-EBS-PL/sql 查看视图View的组成

有时候知道一个view，为了迅速的查询这个view是设么sql组成的，可以用以下sql语句：

select object_name from user_objects where object_type='AR_PAYMETN_SCHEDULES_V'

select dbms_metadata.get_ddl('VIEW','AR_PAYMETN_SCHEDULES_V') from dual; --查看视图的组成,注意VIEW 要大写--常用

执行后，会出现如下sql代码，放入代码编辑器，格式化即可：

"
CREATE OR REPLACE FORCE VIEW "APPS"."AR_PAYMENT_SCHEDULES_V" ("ROW_ID", "PAYMENT_SCHEDULE_ID", "TRX_NUMBER", "TRX_DATE", "TERMS_SEQUENCE_NUMBER", "INVOICE_CURRENCY_CODE", "EXCHANGE_RATE", "GL_DATE", "ATTRIBUTE_CATEGORY", "ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3", "ATTRIBUTE4", "ATTRIBUTE5", "ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE8", "ATTRIBUTE9", "ATTRIBUTE10", "ATTRIBUTE11", "ATTRIBUTE12", "ATTRIBUTE13", "ATTRIBUTE14", "ATTRIBUTE15", "CUSTOMER_ID", "CUSTOMER_SITE_USE_ID", "CUSTOMER_TRX_ID", "CUST_TRX_TYPE_ID", "CASH_RECEIPT_ID", "CLASS", "STATUS", "STATE_DSP", "STATE", "RECEIPT_CONFIRMED_FLAG", "SELECTED_FOR_RECEIPT_BATCH_ID", "COLLECTOR_LAST", "FOLLOW_UP_CODE_LAST", "IN_COLLECTION", "TERM_ID", "RAC_CUSTOMER_NAME", "RAC_CUSTOMER_NUMBER", "SU_LOCATION", "BS_BATCH_SOURCE_NAME", "TYPE_NAME", "AL_CLASS_MEANING", "DOC_SEQUENCE_VALUE", "CT_PURCHASE_ORDER", "CT_PREVIOUS_CUSTOMER_TRX_ID", "INTERFACE_HEADER_CONTEXT", "INTERFACE_HEADER_ATTRIBUTE1", "INTERFACE_HEADER_ATTRIBUTE2", "INTERFACE_HEADER_ATTRIBUTE3", "INTERFACE_HEADER_ATTRIBUTE4", "INTERFACE_HEADER_ATTRIBUTE5", "INTERFACE_HEADER_ATTRIBUTE6", "INTERFACE_HEADER_ATTRIBUTE7", "INTERFACE_HEADER_ATTRIBUTE8", "INTERFACE_HEADER_ATTRIBUTE9", "INTERFACE_HEADER_ATTRIBUTE10", "INTERFACE_HEADER_ATTRIBUTE11", "INTERFACE_HEADER_ATTRIBUTE12", "INTERFACE_HEADER_ATTRIBUTE13", "INTERFACE_HEADER_ATTRIBUTE14", "INTERFACE_HEADER_ATTRIBUTE15", "DUE_DAYS", "DUE_DATE", "AMOUNT_DUE_ORIGINAL", "ACCTD_AMOUNT_DUE_ORIGINAL", "AMOUNT_DUE_REMAINING", "ACCTD_AMOUNT_DUE_REMAINING", "AL_STATUS_MEANING", "ACTUAL_DATE_CLOSED", "GL_DATE_CLOSED", "AMOUNT_LINE_ITEMS_ORIGINAL", "ACCTD_AMOUNT_LINE_ITEMS_ORIG", "AMOUNT_LINE_ITEMS_REMAINING", "ACCTD_AMOUNT_LINE_ITEMS_REMAIN", "TAX_ORIGINAL", "ACCTD_TAX_ORIGINAL", "TAX_REMAINING", "ACCTD_TAX_REMAINING", "FREIGHT_ORIGINAL", "ACCTD_FREIGHT_ORIGINAL", "FREIGHT_REMAINING", "ACCTD_FREIGHT_REMAINING", "AMOUNT_ADJUSTED", "ACCTD_AMOUNT_ADJUSTED", "AMOUNT_ADJUSTED_PENDING", "ACCTD_AMOUNT_ADJUSTED_PENDING", "RECEIVABLES_CHARGES_CHARGED", "ACCTD_REC_CHARGES_CHARGED", "RECEIVABLES_CHARGES_REMAINING", "ACCTD_REC_CHARGES_REMAINING", "AMOUNT_APPLIED", "ACCTD_AMOUNT_APPLIED", "AMOUNT_CREDITED", "ACCTD_AMOUNT_CREDITED", "AMOUNT_IN_DISPUTE", "ACCTD_AMOUNT_IN_DISPUTE", "DISPUTE_DATE", "DISCOUNT_TAKEN_EARNED", "ACCTD_DISCOUNT_TAKEN_EARNED", "DISCOUNT_TAKEN_UNEARNED", "ACCTD_DISCOUNT_TAKEN_UNEARNED", "AL_SELECTED_FOR_RECEIPT_FLAG", "AL_SELECTED_FOR_RECEIPT_CODE", "AL_RECEIPT_CONFIRMED_MEANING", "CR_SELECTED_RECEIPT_NUMBER", "ARB_SELECTED_BATCH_NAME", "ARB_SELECTED_BATCH_DATE", "DAYS_PAST_DUE", "DUNNING_DATE_LAST", "CALL_DATE_LAST", "PROMISE_AMOUNT_LAST", "ACCTD_PROMISE_AMOUNT_LAST", "PROMISE_DATE_LAST", "AL_FOLLOW_UP_LAST_MEANING", "FOLLOW_UP_DATE_LAST", "AL_IN_COLLECTION_MEANING", "RAT_TERM_NAME", "CHARGEBACK_INVOICE_NUM", "CHARGEBACK_BATCH_SOURCE", "BILLING_NUMBER", "STAGED_DUNNING_LEVEL", "DUNNING_LEVEL_OVERRIDE_DATE", "REMITTANCE_METHOD", "GLOBAL_ATTRIBUTE_CATEGORY", "GLOBAL_ATTRIBUTE1", "GLOBAL_ATTRIBUTE2", "GLOBAL_ATTRIBUTE3", "GLOBAL_ATTRIBUTE4", "GLOBAL_ATTRIBUTE5", "GLOBAL_ATTRIBUTE6", "GLOBAL_ATTRIBUTE7", "GLOBAL_ATTRIBUTE8", "GLOBAL_ATTRIBUTE9", "GLOBAL_ATTRIBUTE10", "GLOBAL_ATTRIBUTE11", "GLOBAL_ATTRIBUTE12", "GLOBAL_ATTRIBUTE13", "GLOBAL_ATTRIBUTE14", "GLOBAL_ATTRIBUTE15", "GLOBAL_ATTRIBUTE16", "GLOBAL_ATTRIBUTE17", "GLOBAL_ATTRIBUTE18", "GLOBAL_ATTRIBUTE19", "GLOBAL_ATTRIBUTE20", "CONS_INV_ID", "LAST_UPDATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATE_LOGIN", "CREATED_BY", "CREATION_DATE", "COMMENTS", "LEGAL_ENTITY_ID", "BILLING_DATE", "ORG_ID") AS
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))"