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.IEX_AG_TRX_V

Source

View Text - Preformatted


SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , PS.TRX_NUMBER TRX_NUMBER , PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER , CT.PURCHASE_ORDER PURCHASE_ORDER , CT.LAST_UPDATE_DATE LAST_UPDATE_DATE , CT.LAST_UPDATED_BY LAST_UPDATED_BY , CT.CREATION_DATE CREATION_DATE , CT.CREATED_BY CREATED_BY , CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , PS.DUE_DATE DUE_DATE , PS.TRX_DATE TRX_DATE , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING , PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING , PS.STATUS CUST_TRX_STATUS_CODE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INVOICE_TRX_STATUS',ps.status) CUST_TRX_STATUS_NAME , PS.CLASS CUST_TRX_CLASS_CODE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS) CUST_TRX_CLASS_NAME, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, CTT.NAME CUST_TRX_TYPE_NAME , PS.CUSTOMER_ID CUSTOMER_ID , SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID , PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.ORG_ID ORG_ID , substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER , SITE_USES.LOCATION BILL_TO_LOCATION , CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID , CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, DEL.DELINQUENCY_ID DELINQUENCY_ID, DEL.STATUS DELINQUENCY_STATUS, iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS',del.status) DEL_STATUS_DSP, DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - TRUNC(PS.DUE_DATE)) DAYS_PAST_DUE, DECODE(PS.DISPUTE_DATE, null, 'N', 'Y') DISPUTED_FLAG , PS.DISPUTE_DATE DISPUTE_DATE, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE , '' PROMISED_FLAG , '' PAID_FLAG , '' SALES_ORDER , '' TRX_SCORE, '' STRATEGY_NAME , PS.GL_DATE GL_DATE , CT.COMMENTS COMMENTS , CT.TERM_ID TERM_ID , ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS.TERM_ID,'NAME') TERM_NAME , CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID,'NAME',CT.ORG_ID) PRIMARY_SALESREP_NAME, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , CT.ATTRIBUTE1 ATTRIBUTE1 , CT.ATTRIBUTE2 ATTRIBUTE2, CT.ATTRIBUTE3 ATTRIBUTE3 , CT.ATTRIBUTE4 ATTRIBUTE4 , CT.ATTRIBUTE5 ATTRIBUTE5 , CT.ATTRIBUTE6 ATTRIBUTE6 , CT.ATTRIBUTE7 ATTRIBUTE7 , CT.ATTRIBUTE8 ATTRIBUTE8 , CT.ATTRIBUTE9 ATTRIBUTE9 , CT.ATTRIBUTE10 ATTRIBUTE10 , CT.ATTRIBUTE11 ATTRIBUTE11 , CT.ATTRIBUTE12 ATTRIBUTE12 , CT.ATTRIBUTE13 ATTRIBUTE13 , CT.ATTRIBUTE14 ATTRIBUTE14 , CT.ATTRIBUTE15 ATTRIBUTE15 , CT.INTERFACE_HEADER_CONTEXT INTERFACE_HEADER_CONTEXT , CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1 , CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2 , CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3 , CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4 , CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5 , CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6 , CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7 , CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8 , CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9 , CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10 , CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11 , CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12 , CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13 , CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14 , CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15 , CT.BILL_TO_ADDRESS_ID BILL_TO_ADDRESS_ID , CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID , CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID , CT.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY , CT.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1 , CT.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10 , CT.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11 , CT.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12 , CT.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13 , CT.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14 , CT.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15 , CT.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16 , CT.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17 , CT.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18 , CT.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19 , CT.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2 , CT.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20 , CT.GLOBAL_ATTRIBUTE21 GLOBAL_ATTRIBUTE21 , CT.GLOBAL_ATTRIBUTE22 GLOBAL_ATTRIBUTE22 , CT.GLOBAL_ATTRIBUTE23 GLOBAL_ATTRIBUTE23 , CT.GLOBAL_ATTRIBUTE24 GLOBAL_ATTRIBUTE24 , CT.GLOBAL_ATTRIBUTE25 GLOBAL_ATTRIBUTE25 , CT.GLOBAL_ATTRIBUTE26 GLOBAL_ATTRIBUTE26 , CT.GLOBAL_ATTRIBUTE27 GLOBAL_ATTRIBUTE27 , CT.GLOBAL_ATTRIBUTE28 GLOBAL_ATTRIBUTE28 , CT.GLOBAL_ATTRIBUTE29 GLOBAL_ATTRIBUTE29 , CT.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3 , CT.GLOBAL_ATTRIBUTE30 GLOBAL_ATTRIBUTE30 , CT.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4 , CT.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5 , CT.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6 , CT.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7 , CT.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8 , CT.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9, CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , PARTY.PARTY_ID PARTY_ID, SUBSTR(LOC.ADDRESS1,1,25) ||' ' || SUBSTR(LOC.ADDRESS2,1,25) ||' ' || SUBSTR(LOC.ADDRESS3,1,25) || ' ' || SUBSTR(LOC.ADDRESS4,1,25) ||' ' || LOC.CITY|| ',' || ' ' || NVL(LOC.STATE, LOC.PROVINCE) || ' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS , PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, CT.CREATION_DATE TRX_CREATION_DATE , PS.CONS_INV_ID CONS_INV_ID, CT.PRINTING_OPTION DEFAULT_PRINTING_OPTION, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, OP_UNIT.NAME OPERATING_UNIT, iex_utilities.get_lookup_meaning('IEX_UWQ_NODE_STATUS', DEL.UWQ_STATUS) UWQ_STATUS, iex_utilities.get_lookup_meaning('IEX_UNPAID_REASON', DEL.UNPAID_REASON_CODE) UNPAID_REASON_MEANING , CT.COMMENTS RECEIVABLES_COMMENTS , CT.INTERNAL_NOTES RECEIVABLES_INTERNAL_NOTES , CT.SPECIAL_INSTRUCTIONS RECEIVABLES_SPL_INSTRUCTIONS, CT.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, XEP.NAME LEGAL_ENTITY_NAME FROM AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX CT, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, IEX_DELINQUENCIES DEL, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, /*HZ_LOC_ASSIGNMENTS LOC_ASSIGN,*/ HZ_LOCATIONS LOC, FND_TERRITORIES_VL TERR, AR_CONS_INV CONS, HR_OPERATING_UNITS OP_UNIT , XLE_ENTITY_PROFILES XEP WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+) AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND /*PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND ACCT_SITE.ORG_ID = LOC_ASSIGN.ORG_ID AND*/ LOC.COUNTRY = TERR.TERRITORY_CODE AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID AND PS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+) AND CTT.ORG_ID = PS.ORG_ID AND CT.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID(+) UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , PS.TRX_NUMBER TRX_NUMBER , PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER , NULL PURCHASE_ORDER , CR.LAST_UPDATE_DATE LAST_UPDATE_DATE , CR.LAST_UPDATED_BY LAST_UPDATED_BY , CR.CREATION_DATE CREATION_DATE , CR.CREATED_BY CREATED_BY , CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , PS.DUE_DATE DUE_DATE , PS.TRX_DATE TRX_DATE , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.AMOUNT_DUE_ORIGINAL, -1 * PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL , 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_AMOUNT_DUE_REMAINING , DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.STATUS, 'OP') CUST_TRX_STATUS_CODE, 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')) CUST_TRX_STATUS_NAME , PS.CLASS CUST_TRX_CLASS_CODE, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS) CUST_TRX_CLASS_NAME, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, RM.NAME CUST_TRX_TYPE_NAME , PS.CUSTOMER_ID CUSTOMER_ID , SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID , PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.ORG_ID ORG_ID , substrb(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER , SITE_USES.LOCATION BILL_TO_LOCATION , TO_NUMBER('') BILL_TO_CONTACT_ID , CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, DEL.DELINQUENCY_ID DELINQUENCY_ID, NULL DELINQUENCY_STATUS, NULL DEL_STATUS_DSP, DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - TRUNC(PS.DUE_DATE)) DAYS_PAST_DUE, DECODE(PS.DISPUTE_DATE, null, 'N', 'Y') DISPUTED_FLAG , PS.DISPUTE_DATE DISPUTE_DATE, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE , '' PROMISED_FLAG , '' PAID_FLAG , '' SALES_ORDER , '' TRX_SCORE, '' STRATEGY_NAME , PS.GL_DATE GL_DATE , CR.COMMENTS COMMENTS , PS.TERM_ID TERM_ID , NULL TERM_NAME, TO_NUMBER('') PRIMARY_SALESREP_ID, NULL PRIMARY_SALESREP_NAME, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , PS.ATTRIBUTE1 ATTRIBUTE1 , PS.ATTRIBUTE2 ATTRIBUTE2, PS.ATTRIBUTE3 ATTRIBUTE3 , PS.ATTRIBUTE4 ATTRIBUTE4 , PS.ATTRIBUTE5 ATTRIBUTE5 , PS.ATTRIBUTE6 ATTRIBUTE6 , PS.ATTRIBUTE7 ATTRIBUTE7 , PS.ATTRIBUTE8 ATTRIBUTE8 , PS.ATTRIBUTE9 ATTRIBUTE9 , PS.ATTRIBUTE10 ATTRIBUTE10 , PS.ATTRIBUTE11 ATTRIBUTE11 , PS.ATTRIBUTE12 ATTRIBUTE12 , PS.ATTRIBUTE13 ATTRIBUTE13 , PS.ATTRIBUTE14 ATTRIBUTE14 , PS.ATTRIBUTE15 ATTRIBUTE15 , NULL INTERFACE_HEADER_CONTEXT , NULL INTERFACE_HEADER_ATTRIBUTE1 , NULL INTERFACE_HEADER_ATTRIBUTE2 , NULL INTERFACE_HEADER_ATTRIBUTE3 , NULL INTERFACE_HEADER_ATTRIBUTE4 , NULL INTERFACE_HEADER_ATTRIBUTE5 , NULL INTERFACE_HEADER_ATTRIBUTE6 , NULL INTERFACE_HEADER_ATTRIBUTE7 , NULL INTERFACE_HEADER_ATTRIBUTE8 , NULL INTERFACE_HEADER_ATTRIBUTE9 , NULL INTERFACE_HEADER_ATTRIBUTE10, NULL INTERFACE_HEADER_ATTRIBUTE11, NULL INTERFACE_HEADER_ATTRIBUTE12, NULL INTERFACE_HEADER_ATTRIBUTE13, NULL INTERFACE_HEADER_ATTRIBUTE14, NULL INTERFACE_HEADER_ATTRIBUTE15, TO_NUMBER('') BILL_TO_ADDRESS_ID , TO_NUMBER('') BILL_TO_CUSTOMER_ID , TO_NUMBER('') BILL_TO_SITE_USE_ID , CR.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY , CR.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1 , CR.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10 , CR.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11 , CR.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12 , CR.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13 , CR.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14 , CR.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15 , CR.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16 , CR.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17 , CR.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18 , CR.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19 , CR.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2 , CR.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20 , NULL GLOBAL_ATTRIBUTE21 , NULL GLOBAL_ATTRIBUTE22 , NULL GLOBAL_ATTRIBUTE23 , NULL GLOBAL_ATTRIBUTE24 , NULL GLOBAL_ATTRIBUTE25 , NULL GLOBAL_ATTRIBUTE26 , NULL GLOBAL_ATTRIBUTE27 , NULL GLOBAL_ATTRIBUTE28 , NULL GLOBAL_ATTRIBUTE29 , CR.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3 , NULL GLOBAL_ATTRIBUTE30 , CR.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4 , CR.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5 , CR.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6 , CR.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7 , CR.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8 , CR.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , PARTY.PARTY_ID PARTY_ID, SUBSTR(LOC.ADDRESS1,1,25) ||' ' || SUBSTR(LOC.ADDRESS2,1,25) ||' ' || SUBSTR(LOC.ADDRESS3,1,25) || ' ' || SUBSTR(LOC.ADDRESS4,1,25) ||' ' || LOC.CITY|| ',' || ' ' || NVL(LOC.STATE, LOC.PROVINCE) || ' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS , PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, CR.CREATION_DATE TRX_CREATION_DATE , PS.CONS_INV_ID CONS_INV_ID, NULL DEFAULT_PRINTING_OPTION, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, OP_UNIT.NAME OPERATING_UNIT, NULL UWQ_STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_UNPAID_REASON', DEL.UNPAID_REASON_CODE) UNPAID_REASON_MEANING, CR.COMMENTS RECEIVABLES_COMMENTS, NULL RECEIVABLES_INTERNAL_NOTES, NULL RECEIVABLES_SPL_INSTRUCTIONS, CR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID, XEP.NAME LEGAL_ENTITY_NAME FROM AR_PAYMENT_SCHEDULES PS, IEX_DELINQUENCIES DEL, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, /*HZ_LOC_ASSIGNMENTS LOC_ASSIGN,*/ HZ_LOCATIONS LOC, FND_TERRITORIES_VL TERR, AR_CONS_INV CONS, AR_LOOKUPS AL_RISK_RECEIPT, AR_RECEIPT_METHODS RM, AR_CASH_RECEIPT_HISTORY CRH, AR_CASH_RECEIPT_HISTORY CRH_CURRENT, AR_CASH_RECEIPTS CR, AR_CASH_RECEIPT_HISTORY CRH_REMIT, FND_CURRENCIES FC, HR_OPERATING_UNITS OP_UNIT, XLE_ENTITY_PROFILES XEP WHERE PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+) AND PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND SITE_USES.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID AND /*ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID AND*/ ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND /*PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID(+) AND*/ /*ACCT_SITE.ORG_ID = LOC_ASSIGN.ORG_ID AND*/ LOC.COUNTRY = TERR.TERRITORY_CODE(+) AND CONS.CONS_INV_ID(+) = PS.CONS_INV_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 CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID AND CRH_CURRENT.CURRENT_RECORD_FLAG = 'Y' AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+) AND CRH_REMIT.STATUS(+) = 'REMITTED' AND FC.CURRENCY_CODE = CR.CURRENCY_CODE AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y' AND AL_RISK_RECEIPT.LOOKUP_TYPE = 'YES/NO' 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') )))) AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL AND CR.ORG_ID = OP_UNIT.ORGANIZATION_ID(+) AND CR.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID(+) UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, PS.TRX_NUMBER TRX_NUMBER, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, NULL PURCHASE_ORDER, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE, CR.LAST_UPDATED_BY LAST_UPDATED_BY, CR.CREATION_DATE CREATION_DATE, CR.CREATED_BY CREATED_BY, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, PS.DUE_DATE DUE_DATE, PS.TRX_DATE TRX_DATE, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS.AMOUNT_DUE_ORIGINAL,-1*PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL, 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_AMOUNT_DUE_REMAINING, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE,'N',PS. STATUS,'OP') CUST_TRX_STATUS_CODE , 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')) CUST_TRX_STATUS_NAME, PS.CLASS CUST_TRX_CLASS_CODE, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM',PS.CLASS) CUST_TRX_CLASS_NAME, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, RM.NAME CUST_TRX_TYPE_NAME, PS.CUSTOMER_ID CUSTOMER_ID, NULL CUSTOMER_SITE_USE_ID, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.ORG_ID ORG_ID, SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, NULL BILL_TO_LOCATION, TO_NUMBER('') BILL_TO_CONTACT_ID, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, TO_NUMBER('') DELINQUENCY_ID, NULL DELINQUENCY_STATUS, NULL DEL_STATUS_DSP, DECODE(PS.AMOUNT_DUE_REMAINING,0,TO_NUMBER(NULL),TRUNC(SYSDATE)-PS.DUE_DATE) DAYS_PAST_DUE, DECODE(PS.DISPUTE_DATE,NULL, 'N','Y') DISPUTED_FLAG, PS.DISPUTE_DATE DISPUTE_DATE, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE, '' PROMISED_FLAG, '' PAID_FLAG, '' SALES_ORDER, '' TRX_SCORE, '' STRATEGY_NAME, PS.GL_DATE GL_DATE, CR.COMMENTS COMMENTS, PS.TERM_ID TERM_ID, NULL, TO_NUMBER('') PRIMARY_SALESREP_ID, NULL PRIMARY_SALESREP_NAME, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, PS.ATTRIBUTE1 ATTRIBUTE1, PS.ATTRIBUTE2 ATTRIBUTE2 , PS.ATTRIBUTE3 ATTRIBUTE3 , PS.ATTRIBUTE4 ATTRIBUTE4 , PS.ATTRIBUTE5 ATTRIBUTE5 , PS.ATTRIBUTE6 ATTRIBUTE6 , PS.ATTRIBUTE7 ATTRIBUTE7 , PS.ATTRIBUTE8 ATTRIBUTE8 , PS.ATTRIBUTE9 ATTRIBUTE9 , PS.ATTRIBUTE10 ATTRIBUTE10 , PS.ATTRIBUTE11 ATTRIBUTE11 , PS.ATTRIBUTE12 ATTRIBUTE12 , PS.ATTRIBUTE13 ATTRIBUTE13 , PS.ATTRIBUTE14 ATTRIBUTE14 , PS.ATTRIBUTE15 ATTRIBUTE15 , NULL INTERFACE_HEADER_CONTEXT , NULL INTERFACE_HEADER_ATTRIBUTE1 , NULL INTERFACE_HEADER_ATTRIBUTE2 , NULL INTERFACE_HEADER_ATTRIBUTE3 , NULL INTERFACE_HEADER_ATTRIBUTE4 , NULL INTERFACE_HEADER_ATTRIBUTE5 , NULL INTERFACE_HEADER_ATTRIBUTE6 , NULL INTERFACE_HEADER_ATTRIBUTE7 , NULL INTERFACE_HEADER_ATTRIBUTE8 , NULL INTERFACE_HEADER_ATTRIBUTE9 , NULL INTERFACE_HEADER_ATTRIBUTE10 , NULL INTERFACE_HEADER_ATTRIBUTE11 , NULL INTERFACE_HEADER_ATTRIBUTE12 , NULL INTERFACE_HEADER_ATTRIBUTE13 , NULL INTERFACE_HEADER_ATTRIBUTE14 , NULL INTERFACE_HEADER_ATTRIBUTE15 , TO_NUMBER('') BILL_TO_ADDRESS_ID , TO_NUMBER('') BILL_TO_CUSTOMER_ID , TO_NUMBER('') BILL_TO_SITE_USE_ID , CR.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY , CR.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1 , CR.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10 , CR.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11 , CR.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12 , CR.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13 , CR.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14 , CR.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15 , CR.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16 , CR.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17 , CR.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18 , CR.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19 , CR.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2 , CR.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20 , NULL GLOBAL_ATTRIBUTE21 , NULL GLOBAL_ATTRIBUTE22 , NULL GLOBAL_ATTRIBUTE23 , NULL GLOBAL_ATTRIBUTE24 , NULL GLOBAL_ATTRIBUTE25 , NULL GLOBAL_ATTRIBUTE26 , NULL GLOBAL_ATTRIBUTE27 , NULL GLOBAL_ATTRIBUTE28 , NULL GLOBAL_ATTRIBUTE29 , CR.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3 , NULL GLOBAL_ATTRIBUTE30 , CR.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4 , CR.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5 , CR.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6 , CR.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7 , CR.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8 , CR.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9 , CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , PARTY.PARTY_ID PARTY_ID , NULL BILL_TO_ADDRESS , NULL PARTY_SITE_NUMBER , CR.CREATION_DATE TRX_CREATION_DATE , PS.CONS_INV_ID CONS_INV_ID , NULL DEFAULT_PRINTING_OPTION , CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER , OP_UNIT.NAME OPERATING_UNIT , NULL UWQ_STATUS , NULL UNPAID_REASON_MEANING, CR.COMMENTS RECEIVABLES_COMMENTS , NULL RECEIVABLES_INTERNAL_NOTES , NULL RECEIVABLES_SPL_INSTRUCTIONS , NULL LEGAL_ENTITY_ID , NULL LEGAL_ENTITY_NAME FROM AR_PAYMENT_SCHEDULES PS , HZ_CUST_ACCOUNTS CUST_ACCT , HZ_PARTIES PARTY , AR_CONS_INV CONS , AR_LOOKUPS AL_RISK_RECEIPT , AR_RECEIPT_METHODS RM , AR_CASH_RECEIPT_HISTORY CRH , AR_CASH_RECEIPT_HISTORY CRH_CURRENT, AR_CASH_RECEIPTS CR , AR_CASH_RECEIPT_HISTORY CRH_REMIT , FND_CURRENCIES FC , HR_OPERATING_UNITS OP_UNIT WHERE PS.CUSTOMER_ID =CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID =PARTY.PARTY_ID AND PS.CUSTOMER_SITE_USE_ID IS NULL AND CONS.CONS_INV_ID(+) =PS.CONS_INV_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 CR.CASH_RECEIPT_ID =CRH_CURRENT.CASH_RECEIPT_ID AND CRH_CURRENT.CURRENT_RECORD_FLAG ='Y' AND CR.CASH_RECEIPT_ID =CRH_REMIT.CASH_RECEIPT_ID(+) AND CRH_REMIT.STATUS(+) ='REMITTED' AND FC.CURRENCY_CODE =CR.CURRENCY_CODE AND CRH_REMIT.CURRENT_RECORD_FLAG(+) ='Y' AND AL_RISK_RECEIPT.LOOKUP_TYPE ='YES/NO' 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'))))) AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL AND CR.ORG_ID =OP_UNIT.ORGANIZATION_ID(+)

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CT.LAST_UPDATED_BY LAST_UPDATED_BY
, CT.CREATION_DATE CREATION_DATE
, CT.CREATED_BY CREATED_BY
, CT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.DUE_DATE DUE_DATE
, PS.TRX_DATE TRX_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, PS.STATUS CUST_TRX_STATUS_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICE_TRX_STATUS'
, PS.STATUS) CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CTT.NAME CUST_TRX_TYPE_NAME
, PS.CUSTOMER_ID CUSTOMER_ID
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SITE_USES.LOCATION BILL_TO_LOCATION
, CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, DEL.DELINQUENCY_ID DELINQUENCY_ID
, DEL.STATUS DELINQUENCY_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, DEL.STATUS) DEL_STATUS_DSP
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - TRUNC(PS.DUE_DATE)) DAYS_PAST_DUE
, DECODE(PS.DISPUTE_DATE
, NULL
, 'N'
, 'Y') DISPUTED_FLAG
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, '' PROMISED_FLAG
, '' PAID_FLAG
, '' SALES_ORDER
, '' TRX_SCORE
, '' STRATEGY_NAME
, PS.GL_DATE GL_DATE
, CT.COMMENTS COMMENTS
, CT.TERM_ID TERM_ID
, ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS.TERM_ID
, 'NAME') TERM_NAME
, CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
, 'NAME'
, CT.ORG_ID) PRIMARY_SALESREP_NAME
, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1 ATTRIBUTE1
, CT.ATTRIBUTE2 ATTRIBUTE2
, CT.ATTRIBUTE3 ATTRIBUTE3
, CT.ATTRIBUTE4 ATTRIBUTE4
, CT.ATTRIBUTE5 ATTRIBUTE5
, CT.ATTRIBUTE6 ATTRIBUTE6
, CT.ATTRIBUTE7 ATTRIBUTE7
, CT.ATTRIBUTE8 ATTRIBUTE8
, CT.ATTRIBUTE9 ATTRIBUTE9
, CT.ATTRIBUTE10 ATTRIBUTE10
, CT.ATTRIBUTE11 ATTRIBUTE11
, CT.ATTRIBUTE12 ATTRIBUTE12
, CT.ATTRIBUTE13 ATTRIBUTE13
, CT.ATTRIBUTE14 ATTRIBUTE14
, CT.ATTRIBUTE15 ATTRIBUTE15
, CT.INTERFACE_HEADER_CONTEXT INTERFACE_HEADER_CONTEXT
, CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15
, CT.BILL_TO_ADDRESS_ID BILL_TO_ADDRESS_ID
, CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CT.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, CT.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, CT.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, CT.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, CT.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, CT.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, CT.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, CT.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, CT.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, CT.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, CT.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, CT.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, CT.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, CT.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, CT.GLOBAL_ATTRIBUTE21 GLOBAL_ATTRIBUTE21
, CT.GLOBAL_ATTRIBUTE22 GLOBAL_ATTRIBUTE22
, CT.GLOBAL_ATTRIBUTE23 GLOBAL_ATTRIBUTE23
, CT.GLOBAL_ATTRIBUTE24 GLOBAL_ATTRIBUTE24
, CT.GLOBAL_ATTRIBUTE25 GLOBAL_ATTRIBUTE25
, CT.GLOBAL_ATTRIBUTE26 GLOBAL_ATTRIBUTE26
, CT.GLOBAL_ATTRIBUTE27 GLOBAL_ATTRIBUTE27
, CT.GLOBAL_ATTRIBUTE28 GLOBAL_ATTRIBUTE28
, CT.GLOBAL_ATTRIBUTE29 GLOBAL_ATTRIBUTE29
, CT.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, CT.GLOBAL_ATTRIBUTE30 GLOBAL_ATTRIBUTE30
, CT.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, CT.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, CT.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, CT.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, CT.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, CT.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, CT.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PARTY.PARTY_ID PARTY_ID
, SUBSTR(LOC.ADDRESS1
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS2
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS3
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS4
, 1
, 25) ||' ' || LOC.CITY|| '
, ' || ' ' || NVL(LOC.STATE
, LOC.PROVINCE) || ' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, CT.CREATION_DATE TRX_CREATION_DATE
, PS.CONS_INV_ID CONS_INV_ID
, CT.PRINTING_OPTION DEFAULT_PRINTING_OPTION
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, OP_UNIT.NAME OPERATING_UNIT
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_UWQ_NODE_STATUS'
, DEL.UWQ_STATUS) UWQ_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_UNPAID_REASON'
, DEL.UNPAID_REASON_CODE) UNPAID_REASON_MEANING
, CT.COMMENTS RECEIVABLES_COMMENTS
, CT.INTERNAL_NOTES RECEIVABLES_INTERNAL_NOTES
, CT.SPECIAL_INSTRUCTIONS RECEIVABLES_SPL_INSTRUCTIONS
, CT.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, XEP.NAME LEGAL_ENTITY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX CT
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, IEX_DELINQUENCIES DEL
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, /*HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, */ HZ_LOCATIONS LOC
, FND_TERRITORIES_VL TERR
, AR_CONS_INV CONS
, HR_OPERATING_UNITS OP_UNIT
, XLE_ENTITY_PROFILES XEP
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+)
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND /*PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND ACCT_SITE.ORG_ID = LOC_ASSIGN.ORG_ID AND*/ LOC.COUNTRY = TERR.TERRITORY_CODE
AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID
AND PS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND CTT.ORG_ID = PS.ORG_ID
AND CT.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID(+) UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, NULL PURCHASE_ORDER
, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CR.LAST_UPDATED_BY LAST_UPDATED_BY
, CR.CREATION_DATE CREATION_DATE
, CR.CREATED_BY CREATED_BY
, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.DUE_DATE DUE_DATE
, PS.TRX_DATE TRX_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.AMOUNT_DUE_ORIGINAL
, -1 * PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, 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_AMOUNT_DUE_REMAINING
, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.STATUS
, 'OP') CUST_TRX_STATUS_CODE
, 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')) CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, RM.NAME CUST_TRX_TYPE_NAME
, PS.CUSTOMER_ID CUSTOMER_ID
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SITE_USES.LOCATION BILL_TO_LOCATION
, TO_NUMBER('') BILL_TO_CONTACT_ID
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, DEL.DELINQUENCY_ID DELINQUENCY_ID
, NULL DELINQUENCY_STATUS
, NULL DEL_STATUS_DSP
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - TRUNC(PS.DUE_DATE)) DAYS_PAST_DUE
, DECODE(PS.DISPUTE_DATE
, NULL
, 'N'
, 'Y') DISPUTED_FLAG
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, '' PROMISED_FLAG
, '' PAID_FLAG
, '' SALES_ORDER
, '' TRX_SCORE
, '' STRATEGY_NAME
, PS.GL_DATE GL_DATE
, CR.COMMENTS COMMENTS
, PS.TERM_ID TERM_ID
, NULL TERM_NAME
, TO_NUMBER('') PRIMARY_SALESREP_ID
, NULL PRIMARY_SALESREP_NAME
, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1 ATTRIBUTE1
, PS.ATTRIBUTE2 ATTRIBUTE2
, PS.ATTRIBUTE3 ATTRIBUTE3
, PS.ATTRIBUTE4 ATTRIBUTE4
, PS.ATTRIBUTE5 ATTRIBUTE5
, PS.ATTRIBUTE6 ATTRIBUTE6
, PS.ATTRIBUTE7 ATTRIBUTE7
, PS.ATTRIBUTE8 ATTRIBUTE8
, PS.ATTRIBUTE9 ATTRIBUTE9
, PS.ATTRIBUTE10 ATTRIBUTE10
, PS.ATTRIBUTE11 ATTRIBUTE11
, PS.ATTRIBUTE12 ATTRIBUTE12
, PS.ATTRIBUTE13 ATTRIBUTE13
, PS.ATTRIBUTE14 ATTRIBUTE14
, PS.ATTRIBUTE15 ATTRIBUTE15
, NULL INTERFACE_HEADER_CONTEXT
, NULL INTERFACE_HEADER_ATTRIBUTE1
, NULL INTERFACE_HEADER_ATTRIBUTE2
, NULL INTERFACE_HEADER_ATTRIBUTE3
, NULL INTERFACE_HEADER_ATTRIBUTE4
, NULL INTERFACE_HEADER_ATTRIBUTE5
, NULL INTERFACE_HEADER_ATTRIBUTE6
, NULL INTERFACE_HEADER_ATTRIBUTE7
, NULL INTERFACE_HEADER_ATTRIBUTE8
, NULL INTERFACE_HEADER_ATTRIBUTE9
, NULL INTERFACE_HEADER_ATTRIBUTE10
, NULL INTERFACE_HEADER_ATTRIBUTE11
, NULL INTERFACE_HEADER_ATTRIBUTE12
, NULL INTERFACE_HEADER_ATTRIBUTE13
, NULL INTERFACE_HEADER_ATTRIBUTE14
, NULL INTERFACE_HEADER_ATTRIBUTE15
, TO_NUMBER('') BILL_TO_ADDRESS_ID
, TO_NUMBER('') BILL_TO_CUSTOMER_ID
, TO_NUMBER('') BILL_TO_SITE_USE_ID
, CR.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, CR.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, CR.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, CR.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, CR.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, CR.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, CR.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, CR.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, CR.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, CR.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, CR.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, CR.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, CR.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, CR.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, NULL GLOBAL_ATTRIBUTE21
, NULL GLOBAL_ATTRIBUTE22
, NULL GLOBAL_ATTRIBUTE23
, NULL GLOBAL_ATTRIBUTE24
, NULL GLOBAL_ATTRIBUTE25
, NULL GLOBAL_ATTRIBUTE26
, NULL GLOBAL_ATTRIBUTE27
, NULL GLOBAL_ATTRIBUTE28
, NULL GLOBAL_ATTRIBUTE29
, CR.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, NULL GLOBAL_ATTRIBUTE30
, CR.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, CR.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, CR.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, CR.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, CR.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, CR.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PARTY.PARTY_ID PARTY_ID
, SUBSTR(LOC.ADDRESS1
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS2
, 1
, 25) ||' ' || SUBSTR(LOC.ADDRESS3
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS4
, 1
, 25) ||' ' || LOC.CITY|| '
, ' || ' ' || NVL(LOC.STATE
, LOC.PROVINCE) || ' ' || TERR.TERRITORY_SHORT_NAME BILL_TO_ADDRESS
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, CR.CREATION_DATE TRX_CREATION_DATE
, PS.CONS_INV_ID CONS_INV_ID
, NULL DEFAULT_PRINTING_OPTION
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, OP_UNIT.NAME OPERATING_UNIT
, NULL UWQ_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_UNPAID_REASON'
, DEL.UNPAID_REASON_CODE) UNPAID_REASON_MEANING
, CR.COMMENTS RECEIVABLES_COMMENTS
, NULL RECEIVABLES_INTERNAL_NOTES
, NULL RECEIVABLES_SPL_INSTRUCTIONS
, CR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, XEP.NAME LEGAL_ENTITY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, IEX_DELINQUENCIES DEL
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, /*HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, */ HZ_LOCATIONS LOC
, FND_TERRITORIES_VL TERR
, AR_CONS_INV CONS
, AR_LOOKUPS AL_RISK_RECEIPT
, AR_RECEIPT_METHODS RM
, AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPT_HISTORY CRH_CURRENT
, AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH_REMIT
, FND_CURRENCIES FC
, HR_OPERATING_UNITS OP_UNIT
, XLE_ENTITY_PROFILES XEP
WHERE PS.PAYMENT_SCHEDULE_ID = DEL.PAYMENT_SCHEDULE_ID(+)
AND PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND SITE_USES.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USES.CUST_ACCT_SITE_ID
AND /*ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID AND*/ ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND /*PARTY_SITE.LOCATION_ID = LOC_ASSIGN.LOCATION_ID(+) AND*/ /*ACCT_SITE.ORG_ID = LOC_ASSIGN.ORG_ID AND*/ LOC.COUNTRY = TERR.TERRITORY_CODE(+)
AND CONS.CONS_INV_ID(+) = PS.CONS_INV_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 CR.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = 'Y'
AND CR.CASH_RECEIPT_ID = CRH_REMIT.CASH_RECEIPT_ID(+)
AND CRH_REMIT.STATUS(+) = 'REMITTED'
AND FC.CURRENCY_CODE = CR.CURRENCY_CODE
AND CRH_REMIT.CURRENT_RECORD_FLAG(+) = 'Y'
AND AL_RISK_RECEIPT.LOOKUP_TYPE = 'YES/NO'
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') ))))
AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
AND CR.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND CR.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID(+) UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, NULL PURCHASE_ORDER
, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CR.LAST_UPDATED_BY LAST_UPDATED_BY
, CR.CREATION_DATE CREATION_DATE
, CR.CREATED_BY CREATED_BY
, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.DUE_DATE DUE_DATE
, PS.TRX_DATE TRX_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS.AMOUNT_DUE_ORIGINAL
, -1*PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, 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_AMOUNT_DUE_REMAINING
, DECODE(AL_RISK_RECEIPT.LOOKUP_CODE
, 'N'
, PS. STATUS
, 'OP') CUST_TRX_STATUS_CODE
, 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')) CUST_TRX_STATUS_NAME
, PS.CLASS CUST_TRX_CLASS_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, RM.NAME CUST_TRX_TYPE_NAME
, PS.CUSTOMER_ID CUSTOMER_ID
, NULL CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.ORG_ID ORG_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, NULL BILL_TO_LOCATION
, TO_NUMBER('') BILL_TO_CONTACT_ID
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, TO_NUMBER('') DELINQUENCY_ID
, NULL DELINQUENCY_STATUS
, NULL DEL_STATUS_DSP
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE)-PS.DUE_DATE) DAYS_PAST_DUE
, DECODE(PS.DISPUTE_DATE
, NULL
, 'N'
, 'Y') DISPUTED_FLAG
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, '' PROMISED_FLAG
, '' PAID_FLAG
, '' SALES_ORDER
, '' TRX_SCORE
, '' STRATEGY_NAME
, PS.GL_DATE GL_DATE
, CR.COMMENTS COMMENTS
, PS.TERM_ID TERM_ID
, NULL
, TO_NUMBER('') PRIMARY_SALESREP_ID
, NULL PRIMARY_SALESREP_NAME
, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1 ATTRIBUTE1
, PS.ATTRIBUTE2 ATTRIBUTE2
, PS.ATTRIBUTE3 ATTRIBUTE3
, PS.ATTRIBUTE4 ATTRIBUTE4
, PS.ATTRIBUTE5 ATTRIBUTE5
, PS.ATTRIBUTE6 ATTRIBUTE6
, PS.ATTRIBUTE7 ATTRIBUTE7
, PS.ATTRIBUTE8 ATTRIBUTE8
, PS.ATTRIBUTE9 ATTRIBUTE9
, PS.ATTRIBUTE10 ATTRIBUTE10
, PS.ATTRIBUTE11 ATTRIBUTE11
, PS.ATTRIBUTE12 ATTRIBUTE12
, PS.ATTRIBUTE13 ATTRIBUTE13
, PS.ATTRIBUTE14 ATTRIBUTE14
, PS.ATTRIBUTE15 ATTRIBUTE15
, NULL INTERFACE_HEADER_CONTEXT
, NULL INTERFACE_HEADER_ATTRIBUTE1
, NULL INTERFACE_HEADER_ATTRIBUTE2
, NULL INTERFACE_HEADER_ATTRIBUTE3
, NULL INTERFACE_HEADER_ATTRIBUTE4
, NULL INTERFACE_HEADER_ATTRIBUTE5
, NULL INTERFACE_HEADER_ATTRIBUTE6
, NULL INTERFACE_HEADER_ATTRIBUTE7
, NULL INTERFACE_HEADER_ATTRIBUTE8
, NULL INTERFACE_HEADER_ATTRIBUTE9
, NULL INTERFACE_HEADER_ATTRIBUTE10
, NULL INTERFACE_HEADER_ATTRIBUTE11
, NULL INTERFACE_HEADER_ATTRIBUTE12
, NULL INTERFACE_HEADER_ATTRIBUTE13
, NULL INTERFACE_HEADER_ATTRIBUTE14
, NULL INTERFACE_HEADER_ATTRIBUTE15
, TO_NUMBER('') BILL_TO_ADDRESS_ID
, TO_NUMBER('') BILL_TO_CUSTOMER_ID
, TO_NUMBER('') BILL_TO_SITE_USE_ID
, CR.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, CR.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, CR.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, CR.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, CR.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, CR.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, CR.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, CR.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, CR.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, CR.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, CR.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, CR.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, CR.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, CR.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, NULL GLOBAL_ATTRIBUTE21
, NULL GLOBAL_ATTRIBUTE22
, NULL GLOBAL_ATTRIBUTE23
, NULL GLOBAL_ATTRIBUTE24
, NULL GLOBAL_ATTRIBUTE25
, NULL GLOBAL_ATTRIBUTE26
, NULL GLOBAL_ATTRIBUTE27
, NULL GLOBAL_ATTRIBUTE28
, NULL GLOBAL_ATTRIBUTE29
, CR.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, NULL GLOBAL_ATTRIBUTE30
, CR.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, CR.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, CR.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, CR.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, CR.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, CR.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, CR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PARTY.PARTY_ID PARTY_ID
, NULL BILL_TO_ADDRESS
, NULL PARTY_SITE_NUMBER
, CR.CREATION_DATE TRX_CREATION_DATE
, PS.CONS_INV_ID CONS_INV_ID
, NULL DEFAULT_PRINTING_OPTION
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, OP_UNIT.NAME OPERATING_UNIT
, NULL UWQ_STATUS
, NULL UNPAID_REASON_MEANING
, CR.COMMENTS RECEIVABLES_COMMENTS
, NULL RECEIVABLES_INTERNAL_NOTES
, NULL RECEIVABLES_SPL_INSTRUCTIONS
, NULL LEGAL_ENTITY_ID
, NULL LEGAL_ENTITY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_CONS_INV CONS
, AR_LOOKUPS AL_RISK_RECEIPT
, AR_RECEIPT_METHODS RM
, AR_CASH_RECEIPT_HISTORY CRH
, AR_CASH_RECEIPT_HISTORY CRH_CURRENT
, AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH_REMIT
, FND_CURRENCIES FC
, HR_OPERATING_UNITS OP_UNIT
WHERE PS.CUSTOMER_ID =CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID =PARTY.PARTY_ID
AND PS.CUSTOMER_SITE_USE_ID IS NULL
AND CONS.CONS_INV_ID(+) =PS.CONS_INV_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 CR.CASH_RECEIPT_ID =CRH_CURRENT.CASH_RECEIPT_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG ='Y'
AND CR.CASH_RECEIPT_ID =CRH_REMIT.CASH_RECEIPT_ID(+)
AND CRH_REMIT.STATUS(+) ='REMITTED'
AND FC.CURRENCY_CODE =CR.CURRENCY_CODE
AND CRH_REMIT.CURRENT_RECORD_FLAG(+) ='Y'
AND AL_RISK_RECEIPT.LOOKUP_TYPE ='YES/NO'
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')))))
AND ARP_VIEW_CONSTANTS.GET_SALES_ORDER IS NULL
AND CR.ORG_ID =OP_UNIT.ORGANIZATION_ID(+)