VIEW: APPS.AR_OPEN_TRX_V

Source

View Text - Preformatted

SELECT /*+ ORDERED use_nl(PS, CT , CI , CST, CST_PARTY, SU, BS) */ /* 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 , 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(TO_CHAR(PS.AMOUNT_DUE_REMAINING,FND_CURRENCY.GET_FORMAT_MASK(PS.INVOICE_CURRENCY_CODE,30)),1,30) BALANCE_DUE_CURR , PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED , PS.ACCTD_AMOUNT_DUE_REMAINING 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 , CT.TRX_DATE TRX_DATE , PS.GL_DATE TRX_GL_DATE , trunc(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 , CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG , CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG , CTT.CREATION_SIGN CREATION_SIGN , PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , TO_NUMBER(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 , NULL , NULL , CT.CUSTOMER_REFERENCE , DECODE(PS.CLASS,'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(NULL) RECEIVABLE_APPLICATION_ID , CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION , 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_PAYMENT_SCHEDULES PS , AR_CONS_INV CI WHERE PS.CLASS IN ('CB','CM','DEP','DM','INV','BR') /* 20-APR-2000 J Rautiainen BR Implementation */ AND DECODE(arp_view_constants.get_ps_selected_in_batch, null, 1,PS.selected_for_receipt_batch_id ) = nvl(PS.selected_for_receipt_batch_id,1) /* Commented below line for Bug 4685431 --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 PS.PAYMENT_SCHEDULE_ID > 0 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 UNION ALL SELECT /* Bug 6215163 */ /* PURPOSE: THIS VIEW IS USED MAINLY IN APPLICATIONS LOVS TO SHOW */ /* OPEN TRANSACTIONS */ decode(PS.PAYMENT_SCHEDULE_ID, -1,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','ON_ACC'), -3,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','RCPT_WRITE_OFF'), -4,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','CLAIM_INV'), -6,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','CC_REFUND'), -8,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','REFUND'), -9,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','CC_CHARGEBACK'), PS.TRX_NUMBER) TRX_NUMBER , PS.CUSTOMER_ID CUSTOMER_ID , NULL CUSTOMER_NUMBER , NULL CUSTOMER_NAME , NULL 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 , NULL TRX_TYPE , PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , NULL BALANCE_DUE_CURR , PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED , PS.ACCTD_AMOUNT_DUE_REMAINING 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 , NULL TRX_DATE , PS.GL_DATE TRX_GL_DATE , trunc(DECODE(SIGN(PS.CUSTOMER_ID), -1,fnd_date.canonical_to_date(NULL), PS.DUE_DATE)) TRX_DUE_DATE , PS.TERM_ID TERM_ID , NULL CALC_DISCOUNT_ON_LINES_FLAG , NULL PARTIAL_DISCOUNT_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',NULL) ALLOW_OVERAPPLICATION_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'N',NULL) NATURAL_APPLICATION_ONLY_FLAG , DECODE(PS.PAYMENT_SCHEDULE_ID, -3,'P',NULL) 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 , NULL CUSTOMER_TRX_ID , PS.STATUS STATUS , NULL , 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 , NULL 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 , NULL TRX_BILLING_NUMBER , NULL BILL_TO_SITE_USE_ID , NULL , NULL , NULL , NULL , NULL REASON , TO_NUMBER(NULL) RECEIVABLE_APPLICATION_ID , NULL ACCOUNT_DESCRIPTION , NULL CUSTOMER_REASON FROM AR_PAYMENT_SCHEDULES PS WHERE 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) AND PS.PAYMENT_SCHEDULE_ID < 0

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PS
, CT
, CI
, CST
, CST_PARTY
, SU
, BS) */ /* 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
, 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(TO_CHAR(PS.AMOUNT_DUE_REMAINING
, FND_CURRENCY.GET_FORMAT_MASK(PS.INVOICE_CURRENCY_CODE
, 30))
, 1
, 30) BALANCE_DUE_CURR
, PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED
, PS.ACCTD_AMOUNT_DUE_REMAINING 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
, CT.TRX_DATE TRX_DATE
, PS.GL_DATE TRX_GL_DATE
, TRUNC(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
, CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN CREATION_SIGN
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, TO_NUMBER(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
, NULL
, NULL
, CT.CUSTOMER_REFERENCE
, DECODE(PS.CLASS
, '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(NULL) RECEIVABLE_APPLICATION_ID
, CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION
, 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_PAYMENT_SCHEDULES PS
, AR_CONS_INV CI
WHERE PS.CLASS IN ('CB'
, 'CM'
, 'DEP'
, 'DM'
, 'INV'
, 'BR') /* 20-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND DECODE(ARP_VIEW_CONSTANTS.GET_PS_SELECTED_IN_BATCH
, NULL
, 1
, PS.SELECTED_FOR_RECEIPT_BATCH_ID ) = NVL(PS.SELECTED_FOR_RECEIPT_BATCH_ID
, 1) /* COMMENTED BELOW LINE FOR BUG 4685431 --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 PS.PAYMENT_SCHEDULE_ID > 0
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 UNION ALL SELECT /* BUG 6215163 */ /* PURPOSE: THIS VIEW IS USED MAINLY IN APPLICATIONS LOVS TO SHOW */ /* OPEN TRANSACTIONS */ DECODE(PS.PAYMENT_SCHEDULE_ID
, -1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'ON_ACC')
, -3
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'RCPT_WRITE_OFF')
, -4
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CLAIM_INV')
, -6
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CC_REFUND')
, -8
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'REFUND')
, -9
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CC_CHARGEBACK')
, PS.TRX_NUMBER) TRX_NUMBER
, PS.CUSTOMER_ID CUSTOMER_ID
, NULL CUSTOMER_NUMBER
, NULL CUSTOMER_NAME
, NULL 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
, NULL TRX_TYPE
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, NULL BALANCE_DUE_CURR
, PS.AMOUNT_DUE_REMAINING BALANCE_DUE_CURR_UNFORMATTED
, PS.ACCTD_AMOUNT_DUE_REMAINING 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
, NULL TRX_DATE
, PS.GL_DATE TRX_GL_DATE
, TRUNC(DECODE(SIGN(PS.CUSTOMER_ID)
, -1
, FND_DATE.CANONICAL_TO_DATE(NULL)
, PS.DUE_DATE)) TRX_DUE_DATE
, PS.TERM_ID TERM_ID
, NULL CALC_DISCOUNT_ON_LINES_FLAG
, NULL PARTIAL_DISCOUNT_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, NULL) ALLOW_OVERAPPLICATION_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, NULL) NATURAL_APPLICATION_ONLY_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'P'
, NULL) 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
, NULL CUSTOMER_TRX_ID
, PS.STATUS STATUS
, NULL
, 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
, NULL 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
, NULL TRX_BILLING_NUMBER
, NULL BILL_TO_SITE_USE_ID
, NULL
, NULL
, NULL
, NULL
, NULL REASON
, TO_NUMBER(NULL) RECEIVABLE_APPLICATION_ID
, NULL ACCOUNT_DESCRIPTION
, NULL CUSTOMER_REASON
FROM AR_PAYMENT_SCHEDULES PS
WHERE 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)
AND PS.PAYMENT_SCHEDULE_ID < 0