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

VIEW: APPS.AR_OPEN_ITEMS_V

Source

View Text - Preformatted


SELECT /* PURPOSE: THIS VIEW IS USED MAINLY IN APPLICATIONS LOVS TO SHOW */ /* OPEN TRANSACTIONS */ PS.TRX_NUMBER TRX_NUMBER , PS.CUSTOMER_ID CUSTOMER_ID , CST.ACCOUNT_NUMBER CUSTOMER_NUMBER , SUBSTRB(CST_PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , SU.LOCATION LOCATION , PS.CLASS CLASS , DECODE(PS.CUSTOMER_ID ,-1,NULL,-3,NULL,-4,NULL,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS)) CLASS_MEANING , CTT.NAME TRX_TYPE , PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , SUBSTR(DECODE(SIGN(PS.CUSTOMER_ID) , -1,NULL,TO_CHAR( DECODE(APP.STATUS,NULL,PS.AMOUNT_DUE_REMAINING, SUM(APP.AMOUNT_APPLIED)*-1), FND_CURRENCY.GET_FORMAT_MASK( DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID), -1,'USD' , PS.INVOICE_CURRENCY_CODE) ,30))),1,30) BALANCE_DUE_CURR , DECODE(APP.STATUS,NULL,PS.AMOUNT_DUE_REMAINING,SUM(APP.AMOUNT_APPLIED)*-1) BALANCE_DUE_CURR_UNFORMATTED , DECODE(APP.STATUS,NULL,PS.ACCTD_AMOUNT_DUE_REMAINING, SUM(APP.ACCTD_AMOUNT_APPLIED_FROM)*-1) BALANCE_DUE_FUNCTIONAL , PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL , PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL , PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED , PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED , PS.EXCHANGE_RATE TRX_EXCHANGE_RATE , DECODE(PS.CLASS,'PMT',CR.RECEIPT_DATE,CT.TRX_DATE) TRX_DATE , PS.GL_DATE TRX_GL_DATE , trunc(DECODE(SIGN(PS.CUSTOMER_ID), -1,fnd_date.canonical_to_date(NULL), DECODE(PS.CLASS,'PMT',CR.RECEIPT_DATE,PS.DUE_DATE) )) TRX_DUE_DATE , PS.TERM_ID TERM_ID , T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG , T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.ALLOW_OVERAPPLICATION_FLAG) ALLOW_OVERAPPLICATION_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',CTT.NATURAL_APPLICATION_ONLY_FLAG) NATURAL_APPLICATION_ONLY_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'P',CTT.CREATION_SIGN) CREATION_SIGN , PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , TO_NUMBER(DECODE( SIGN(PS.PAYMENT_SCHEDULE_ID), -1,NULL , PS.TERMS_SEQUENCE_NUMBER)) TERMS_SEQUENCE_NUMBER , CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.STATUS STATUS , CT.PURCHASE_ORDER , 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 , BS.NAME TRX_BATCH_SOURCE_NAME , NVL(PS.AMOUNT_ADJUSTED,0) AMOUNT_ADJUSTED , NVL(PS.AMOUNT_ADJUSTED_PENDING,0) AMOUNT_ADJUSTED_PENDING , NVL(PS.AMOUNT_LINE_ITEMS_REMAINING,0) AMOUNT_LINE_ITEMS_REMAINING , NVL(PS.FREIGHT_ORIGINAL,0) FREIGHT_ORIGINAL , NVL(PS.FREIGHT_REMAINING,0) FREIGHT_REMAINING , NVL(PS.RECEIVABLES_CHARGES_REMAINING,0) RECEIVABLES_CHARGES_REMAINING , NVL(PS.TAX_ORIGINAL,0) TAX_ORIGINAL , NVL(PS.TAX_REMAINING,0) TAX_REMAINING , PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID , CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER , CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID ,BS.DEFAULT_REFERENCE , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,'CLAIM',APP.STATUS) OPEN_RECEIPT_STATUS , DECODE(APP.STATUS ,NULL,NULL, 'UNAPP',ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE','UNAPP'), PS_DUMMY.TRX_NUMBER) OPEN_RECEIPT_STATUS_MEANING , NVL(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.CUSTOMER_REFERENCE,-1,APP.CUSTOMER_REFERENCE,NULL),CT.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE , DECODE(PS.CLASS,'PMT',DECODE( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.APPLICATION_REF_TYPE,NULL),'CLAIM', ARP_DEDUCTION.GET_TM_ORACLE_REASON( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.SECONDARY_APPLICATION_REF_ID,NULL)) ,NULL),'CM', ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON',CT.REASON_CODE) ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON',CT.REASON_CODE)) REASON , TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, NULL,NULL,APP.RECEIVABLE_APPLICATION_ID)) RECEIVABLE_APPLICATION_ID , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, NULL, NULL, APP.APPLICATION_REF_NUM) APPLICATION_REF_NUM , CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION , DECODE(PS.CLASS, 'PMT', DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, -4, APP.CUSTOMER_REASON, NULL), NULL) CUSTOMER_REASON FROM RA_CUSTOMER_TRX CT , RA_CUST_TRX_TYPES CTT , HZ_CUST_SITE_USES SU , RA_BATCH_SOURCES BS /*, AR_LOOKUPS LU Not used -Get lookup meaning used instead. */ , HZ_CUST_ACCOUNTS CST , HZ_PARTIES CST_PARTY , RA_TERMS T , AR_RECEIVABLE_APPLICATIONS APP , AR_CASH_RECEIPTS CR , AR_PAYMENT_SCHEDULES PS , AR_PAYMENT_SCHEDULES PS_DUMMY , AR_CONS_INV CI WHERE PS.CLASS IN ('CB','CM','DEP','DM','INV','BR','PMT') /* 20-APR-2000 J Rautiainen BR Implementation */ AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL AND PS.RESERVED_TYPE IS NULL /* 29-APR-2000 J Rautiainen BR Implementation */ AND PS.RESERVED_VALUE IS NULL /* 29-APR-2000 J Rautiainen BR Implementation */ AND DECODE(PS.PAYMENT_SCHEDULE_ID,-4,ARPT_SQL_FUNC_UTIL.check_iclaim_installed,'T') = 'T' AND PS.PAYMENT_SCHEDULE_ID NOT IN ( -5,-2,-7) /* snambiar receipt chargeback */ AND T.TERM_ID(+) = PS.TERM_ID AND CT.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID AND BS.BATCH_SOURCE_ID (+) = CT.BATCH_SOURCE_ID AND CTT.CUST_TRX_TYPE_ID(+) = PS.CUST_TRX_TYPE_ID AND CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND CST.PARTY_ID = CST_PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID /*AND PS.CLASS = LU.LOOKUP_CODE Not used -Get lookup meaning used instead.*/ AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL /*AND LU.LOOKUP_TYPE = 'INV/CM' Not used -Get lookup meaning used instead.*/ AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+) AND CR.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID(+) AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_DUMMY.PAYMENT_SCHEDULE_ID(+) AND NVL(APP.STATUS,'UNAPP') IN ('UNAPP','ACC','OTHER ACC') AND DECODE(APP.STATUS,'OTHER ACC',APP.APPLIED_PAYMENT_SCHEDULE_ID,-4) = -4 AND DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-1,APP.DISPLAY,-4,APP.DISPLAY,'Y') = 'Y' GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,NULL,NULL, APP.RECEIVABLE_APPLICATION_ID) , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, NULL, NULL, APP.APPLICATION_REF_NUM) , PS_DUMMY.TRX_NUMBER , PS.TRX_NUMBER , PS.CUSTOMER_ID , CST.ACCOUNT_NUMBER , CST_PARTY.PARTY_NAME , SU.LOCATION , PS.CLASS , DECODE(PS.CUSTOMER_ID ,-1,NULL,-3,NULL,-4,NULL,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS)) , CTT.NAME , PS.CUST_TRX_TYPE_ID , PS.INVOICE_CURRENCY_CODE , APP.STATUS , PS.AMOUNT_DUE_REMAINING , PS.INVOICE_CURRENCY_CODE , PS.ACCTD_AMOUNT_DUE_REMAINING , PS.AMOUNT_DUE_ORIGINAL , PS.AMOUNT_LINE_ITEMS_ORIGINAL , PS.DISCOUNT_TAKEN_EARNED , PS.DISCOUNT_TAKEN_UNEARNED , PS.EXCHANGE_RATE , CR.RECEIPT_DATE , CT.TRX_DATE , PS.GL_DATE , PS.DUE_DATE , PS.TERM_ID , T.CALC_DISCOUNT_ON_LINES_FLAG , T.PARTIAL_DISCOUNT_FLAG , CTT.ALLOW_OVERAPPLICATION_FLAG , CTT.NATURAL_APPLICATION_ONLY_FLAG , CTT.CREATION_SIGN , PS.PAYMENT_SCHEDULE_ID , PS.TERMS_SEQUENCE_NUMBER , CT.CUSTOMER_TRX_ID , PS.STATUS , CT.PURCHASE_ORDER , 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 , BS.NAME , PS.AMOUNT_ADJUSTED , PS.AMOUNT_ADJUSTED_PENDING , PS.AMOUNT_LINE_ITEMS_REMAINING , PS.FREIGHT_ORIGINAL , PS.FREIGHT_REMAINING , PS.RECEIVABLES_CHARGES_REMAINING , PS.TAX_ORIGINAL , PS.TAX_REMAINING , PS.SELECTED_FOR_RECEIPT_BATCH_ID , CI.CONS_BILLING_NUMBER , CT.BILL_TO_SITE_USE_ID , BS.DEFAULT_REFERENCE , APP.APPLIED_PAYMENT_SCHEDULE_ID , DECODE(APP.STATUS ,NULL,NULL,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_OPEN_RECEIPT_STATUSES', DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,'CLAIM',APP.STATUS) )) , APP.DISPLAY , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.APPLICATION_REF_TYPE,NULL) , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.SECONDARY_APPLICATION_REF_ID,NULL) , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.CUSTOMER_REFERENCE,-1,APP.CUSTOMER_REFERENCE,NULL) , CT.CUSTOMER_REFERENCE , CT.REASON_CODE , CST.ACCOUNT_NAME , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,-4,APP.CUSTOMER_REASON,NULL)

View Text - HTML Formatted

SELECT /* PURPOSE: THIS VIEW IS USED MAINLY IN APPLICATIONS LOVS TO SHOW */ /* OPEN TRANSACTIONS */ PS.TRX_NUMBER TRX_NUMBER
, PS.CUSTOMER_ID CUSTOMER_ID
, CST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SUBSTRB(CST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION LOCATION
, PS.CLASS CLASS
, DECODE(PS.CUSTOMER_ID
, -1
, NULL
, -3
, NULL
, -4
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)) CLASS_MEANING
, CTT.NAME TRX_TYPE
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, SUBSTR(DECODE(SIGN(PS.CUSTOMER_ID)
, -1
, NULL
, TO_CHAR( DECODE(APP.STATUS
, NULL
, PS.AMOUNT_DUE_REMAINING
, SUM(APP.AMOUNT_APPLIED)*-1)
, FND_CURRENCY.GET_FORMAT_MASK( DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, 'USD'
, PS.INVOICE_CURRENCY_CODE)
, 30)))
, 1
, 30) BALANCE_DUE_CURR
, DECODE(APP.STATUS
, NULL
, PS.AMOUNT_DUE_REMAINING
, SUM(APP.AMOUNT_APPLIED)*-1) BALANCE_DUE_CURR_UNFORMATTED
, DECODE(APP.STATUS
, NULL
, PS.ACCTD_AMOUNT_DUE_REMAINING
, SUM(APP.ACCTD_AMOUNT_APPLIED_FROM)*-1) BALANCE_DUE_FUNCTIONAL
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, PS.EXCHANGE_RATE TRX_EXCHANGE_RATE
, DECODE(PS.CLASS
, 'PMT'
, CR.RECEIPT_DATE
, CT.TRX_DATE) TRX_DATE
, PS.GL_DATE TRX_GL_DATE
, TRUNC(DECODE(SIGN(PS.CUSTOMER_ID)
, -1
, FND_DATE.CANONICAL_TO_DATE(NULL)
, DECODE(PS.CLASS
, 'PMT'
, CR.RECEIPT_DATE
, PS.DUE_DATE) )) TRX_DUE_DATE
, PS.TERM_ID TERM_ID
, T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, CTT.ALLOW_OVERAPPLICATION_FLAG) ALLOW_OVERAPPLICATION_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, CTT.NATURAL_APPLICATION_ONLY_FLAG) NATURAL_APPLICATION_ONLY_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'P'
, CTT.CREATION_SIGN) CREATION_SIGN
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, TO_NUMBER(DECODE( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS.TERMS_SEQUENCE_NUMBER)) TERMS_SEQUENCE_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.STATUS STATUS
, CT.PURCHASE_ORDER
, 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
, BS.NAME TRX_BATCH_SOURCE_NAME
, NVL(PS.AMOUNT_ADJUSTED
, 0) AMOUNT_ADJUSTED
, NVL(PS.AMOUNT_ADJUSTED_PENDING
, 0) AMOUNT_ADJUSTED_PENDING
, NVL(PS.AMOUNT_LINE_ITEMS_REMAINING
, 0) AMOUNT_LINE_ITEMS_REMAINING
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_ORIGINAL
, NVL(PS.FREIGHT_REMAINING
, 0) FREIGHT_REMAINING
, NVL(PS.RECEIVABLES_CHARGES_REMAINING
, 0) RECEIVABLES_CHARGES_REMAINING
, NVL(PS.TAX_ORIGINAL
, 0) TAX_ORIGINAL
, NVL(PS.TAX_REMAINING
, 0) TAX_REMAINING
, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, BS.DEFAULT_REFERENCE
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, 'CLAIM'
, APP.STATUS) OPEN_RECEIPT_STATUS
, DECODE(APP.STATUS
, NULL
, NULL
, 'UNAPP'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, 'UNAPP')
, PS_DUMMY.TRX_NUMBER) OPEN_RECEIPT_STATUS_MEANING
, NVL(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REFERENCE
, -1
, APP.CUSTOMER_REFERENCE
, NULL)
, CT.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, DECODE(PS.CLASS
, 'PMT'
, DECODE( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.APPLICATION_REF_TYPE
, NULL)
, 'CLAIM'
, ARP_DEDUCTION.GET_TM_ORACLE_REASON( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.SECONDARY_APPLICATION_REF_ID
, NULL))
, NULL)
, 'CM'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON'
, CT.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, CT.REASON_CODE)) REASON
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)) RECEIVABLE_APPLICATION_ID
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM) APPLICATION_REF_NUM
, CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REASON
, NULL)
, NULL) CUSTOMER_REASON
FROM RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_SITE_USES SU
, RA_BATCH_SOURCES BS /*
, AR_LOOKUPS LU NOT USED -GET LOOKUP MEANING USED INSTEAD. */
, HZ_CUST_ACCOUNTS CST
, HZ_PARTIES CST_PARTY
, RA_TERMS T
, AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS_DUMMY
, AR_CONS_INV CI
WHERE PS.CLASS IN ('CB'
, 'CM'
, 'DEP'
, 'DM'
, 'INV'
, 'BR'
, 'PMT') /* 20-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.RESERVED_TYPE IS NULL /* 29-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.RESERVED_VALUE IS NULL /* 29-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND DECODE(PS.PAYMENT_SCHEDULE_ID
, -4
, ARPT_SQL_FUNC_UTIL.CHECK_ICLAIM_INSTALLED
, 'T') = 'T'
AND PS.PAYMENT_SCHEDULE_ID NOT IN ( -5
, -2
, -7) /* SNAMBIAR RECEIPT CHARGEBACK */
AND T.TERM_ID(+) = PS.TERM_ID
AND CT.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID
AND BS.BATCH_SOURCE_ID (+) = CT.BATCH_SOURCE_ID
AND CTT.CUST_TRX_TYPE_ID(+) = PS.CUST_TRX_TYPE_ID
AND CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CST.PARTY_ID = CST_PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID /*AND PS.CLASS = LU.LOOKUP_CODE NOT USED -GET LOOKUP MEANING USED INSTEAD.*/
AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL /*AND LU.LOOKUP_TYPE = 'INV/CM' NOT USED -GET LOOKUP MEANING USED INSTEAD.*/
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
AND CR.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID(+)
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_DUMMY.PAYMENT_SCHEDULE_ID(+)
AND NVL(APP.STATUS
, 'UNAPP') IN ('UNAPP'
, 'ACC'
, 'OTHER ACC')
AND DECODE(APP.STATUS
, 'OTHER ACC'
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4) = -4
AND DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, APP.DISPLAY
, -4
, APP.DISPLAY
, 'Y') = 'Y' GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, PS_DUMMY.TRX_NUMBER
, PS.TRX_NUMBER
, PS.CUSTOMER_ID
, CST.ACCOUNT_NUMBER
, CST_PARTY.PARTY_NAME
, SU.LOCATION
, PS.CLASS
, DECODE(PS.CUSTOMER_ID
, -1
, NULL
, -3
, NULL
, -4
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS))
, CTT.NAME
, PS.CUST_TRX_TYPE_ID
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, PS.AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED
, PS.EXCHANGE_RATE
, CR.RECEIPT_DATE
, CT.TRX_DATE
, PS.GL_DATE
, PS.DUE_DATE
, PS.TERM_ID
, T.CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, CT.CUSTOMER_TRX_ID
, PS.STATUS
, CT.PURCHASE_ORDER
, 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
, BS.NAME
, PS.AMOUNT_ADJUSTED
, PS.AMOUNT_ADJUSTED_PENDING
, PS.AMOUNT_LINE_ITEMS_REMAINING
, PS.FREIGHT_ORIGINAL
, PS.FREIGHT_REMAINING
, PS.RECEIVABLES_CHARGES_REMAINING
, PS.TAX_ORIGINAL
, PS.TAX_REMAINING
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, CI.CONS_BILLING_NUMBER
, CT.BILL_TO_SITE_USE_ID
, BS.DEFAULT_REFERENCE
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, DECODE(APP.STATUS
, NULL
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_OPEN_RECEIPT_STATUSES'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, 'CLAIM'
, APP.STATUS) ))
, APP.DISPLAY
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.APPLICATION_REF_TYPE
, NULL)
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.SECONDARY_APPLICATION_REF_ID
, NULL)
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REFERENCE
, -1
, APP.CUSTOMER_REFERENCE
, NULL)
, CT.CUSTOMER_REFERENCE
, CT.REASON_CODE
, CST.ACCOUNT_NAME
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REASON
, NULL)