VIEW: APPS.AR_ICR_LINES_TRX_V

Source

View Text - Preformatted

SELECT /*+ ORDERED use_nl(PS, TRX, CI, CUST_ACCT, PARTY, SITE_USES, BS) */ PS.PAYMENT_SCHEDULE_ID , PS.TRX_NUMBER , CI.CONS_BILLING_NUMBER BILLING_NUMBER , PS.TERM_ID , PS.TERMS_SEQUENCE_NUMBER , PS.DUE_DATE , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , DECODE(PS.CLASS,'PMT',(SUM(APP.AMOUNT_APPLIED) * -1),NVL(PS.AMOUNT_DUE_REMAINING,0)) AMOUNT_DUE_REMAINING , NVL(PS.CUSTOMER_TRX_ID , -1) CUSTOMER_TRX_ID , PS.TRX_DATE , PS.AMOUNT_DUE_ORIGINAL , NVL(PS.DISCOUNT_TAKEN_EARNED, 0) DISCOUNT_TAKEN_EARNED , NVL(PS.DISCOUNT_TAKEN_UNEARNED, 0) DISCOUNT_TAKEN_UNEARNED , NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL, 0) AMOUNT_LINE_ITEMS_ORIGINAL , PS.CUSTOMER_ID CUSTOMER_ID , SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER , DECODE(PS.CLASS,'BR',BR_SITE_USES.LOCATION,'PMT',RCT_SITE_USES.LOCATION,SITE_USES.LOCATION) LOCATION , RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG , RA_TERMS.PARTIAL_DISCOUNT_FLAG , CP.DISCOUNT_GRACE_DAYS GRACE_DAYS , arpt_sql_func_util.get_trx_type_details(TRX.CUST_TRX_TYPE_ID, 'NAME') TYPE_NAME ,arpt_sql_func_util.get_trx_type_details(TRX.CUST_TRX_TYPE_ID , 'ALLOW_OVERAPPLICATION_FLAG') ALLOW_OVERAPPLICATION_FLAG ,arpt_sql_func_util.get_trx_type_details(TRX.CUST_TRX_TYPE_ID, 'NATURAL_APPLICATION_ONLY_FLAG') NATURAL_APPLICATION_ONLY_FLAG , arpt_sql_func_util.get_trx_type_details(TRX.CUST_TRX_TYPE_ID, 'CREATION_SIGN') CREATION_SIGN , PS.CLASS , arpt_sql_func_util.get_lookup_meaning('INV/CM',PS.CLASS) CLASS_MEANING , TRX.INTERFACE_HEADER_ATTRIBUTE1 , TRX.INTERFACE_HEADER_ATTRIBUTE2 , TRX.INTERFACE_HEADER_ATTRIBUTE3 , TRX.INTERFACE_HEADER_ATTRIBUTE4 , TRX.INTERFACE_HEADER_ATTRIBUTE5 , TRX.INTERFACE_HEADER_ATTRIBUTE6 , TRX.INTERFACE_HEADER_ATTRIBUTE7 , TRX.INTERFACE_HEADER_ATTRIBUTE8 , TRX.INTERFACE_HEADER_ATTRIBUTE9 , TRX.INTERFACE_HEADER_ATTRIBUTE10 , TRX.INTERFACE_HEADER_ATTRIBUTE11 , TRX.INTERFACE_HEADER_ATTRIBUTE12 , TRX.INTERFACE_HEADER_ATTRIBUTE13 , TRX.INTERFACE_HEADER_ATTRIBUTE14 , TRX.INTERFACE_HEADER_ATTRIBUTE15 , TRX.PURCHASE_ORDER , DECODE(PS.CLASS,'PMT',DECODE(APP.STATUS,'UNAPP',NULL,APP.APPLICATION_REF_NUM),BS.DEFAULT_REFERENCE) DEFAULT_REFERENCE , APP.STATUS APPLICATION_STATUS , DECODE(APP.STATUS ,NULL,NULL, 'UNAPP',ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE','UNAPP'), PS_DUMMY.TRX_NUMBER) APPLICATION_STATUS_MEANING , DECODE(PS.CLASS,'PMT',DECODE(APP.STATUS,'UNAPP',NULL,APP.CUSTOMER_REFERENCE),TRX.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE , DECODE(PS.CLASS,'PMT',DECODE(APP.STATUS,'UNAPP',NULL,APP.APPLICATION_REF_REASON),TRX.REASON_CODE) REASON_CODE , 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',TRX.REASON_CODE) ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON',TRX.REASON_CODE)) REASON_MEANING , DECODE(APP.STATUS,'UNAPP',NULL,APP.RECEIVABLE_APPLICATION_ID) APPLIED_REC_APP_ID , APP.CASH_RECEIPT_ID APPLIED_CASH_RECEIPT_ID , CUST_ACCT.ACCOUNT_NAME ACCOUNT_DESCRIPTION FROM AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, RA_CUSTOMER_TRX TRX, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_SITE_USES BR_SITE_USES, HZ_CUST_SITE_USES RCT_SITE_USES, RA_TERMS, HZ_CUSTOMER_PROFILES CP, AR_RECEIVABLE_APPLICATIONS APP, AR_PAYMENT_SCHEDULES PS_DUMMY, AR_CONS_INV CI , RA_BATCH_SOURCES BS WHERE PS.STATUS <> 'CL' AND PS.CLASS <> 'GUAR' AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL AND PS.RESERVED_TYPE IS NULL /* 08-JUL-2000 J Rautiainen BR Implementation */ AND PS.RESERVED_VALUE IS NULL /* 08-JUL-2000 J Rautiainen BR Implementation */ AND TRX.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID AND CUST_ACCT.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+) AND SITE_USES.SITE_USE_ID(+) = TRX.BILL_TO_SITE_USE_ID AND BR_SITE_USES.SITE_USE_ID(+) = TRX.DRAWEE_SITE_USE_ID /* 01-AUG-2000 J Rautiainen BR Implementation */ AND RCT_SITE_USES.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID AND PS.TERM_ID = RA_TERMS.TERM_ID (+) AND CP.CUST_ACCOUNT_ID(+) = TRX.BILL_TO_CUSTOMER_ID AND CP.SITE_USE_ID IS NULL AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID AND BS.BATCH_SOURCE_ID (+) = TRX.BATCH_SOURCE_ID AND PS.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' AND PS.PAYMENT_SCHEDULE_ID > 0 /* Bug 6215163*/ GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID,NULL,NULL,APP.RECEIVABLE_APPLICATION_ID) , PS.PAYMENT_SCHEDULE_ID , PS.TRX_NUMBER , CI.CONS_BILLING_NUMBER , PS.TERM_ID , PS.TERMS_SEQUENCE_NUMBER , PS.DUE_DATE , PS.INVOICE_CURRENCY_CODE , PS.CUSTOMER_TRX_ID , PS.AMOUNT_DUE_REMAINING , PS.TRX_DATE , PS.AMOUNT_DUE_ORIGINAL , PS.DISCOUNT_TAKEN_EARNED , PS.DISCOUNT_TAKEN_UNEARNED , PS.AMOUNT_LINE_ITEMS_ORIGINAL , PS.CUSTOMER_ID , SUBSTRB(PARTY.PARTY_NAME,1,50) , CUST_ACCT.ACCOUNT_NUMBER , DECODE(PS.CLASS,'BR',BR_SITE_USES.LOCATION,'PMT',RCT_SITE_USES.LOCATION,SITE_USES.LOCATION) , RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG , RA_TERMS.PARTIAL_DISCOUNT_FLAG , CP.DISCOUNT_GRACE_DAYS , TRX.CUST_TRX_TYPE_ID , PS.CLASS , TRX.INTERFACE_HEADER_ATTRIBUTE1 , TRX.INTERFACE_HEADER_ATTRIBUTE2 , TRX.INTERFACE_HEADER_ATTRIBUTE3 , TRX.INTERFACE_HEADER_ATTRIBUTE4 , TRX.INTERFACE_HEADER_ATTRIBUTE5 , TRX.INTERFACE_HEADER_ATTRIBUTE6 , TRX.INTERFACE_HEADER_ATTRIBUTE7 , TRX.INTERFACE_HEADER_ATTRIBUTE8 , TRX.INTERFACE_HEADER_ATTRIBUTE9 , TRX.INTERFACE_HEADER_ATTRIBUTE10 , TRX.INTERFACE_HEADER_ATTRIBUTE11 , TRX.INTERFACE_HEADER_ATTRIBUTE12 , TRX.INTERFACE_HEADER_ATTRIBUTE13 , TRX.INTERFACE_HEADER_ATTRIBUTE14 , TRX.INTERFACE_HEADER_ATTRIBUTE15 , TRX.PURCHASE_ORDER , PS_DUMMY.TRX_NUMBER , APP.STATUS , DECODE(PS.CLASS,'PMT',DECODE(APP.STATUS,'UNAPP',NULL,APP.APPLICATION_REF_NUM),BS.DEFAULT_REFERENCE) , TRX.CUSTOMER_REFERENCE , DECODE(APP.STATUS,'UNAPP',NULL,APP.CUSTOMER_REFERENCE) , DECODE(PS.CLASS,'PMT',DECODE(APP.STATUS,'UNAPP',NULL,APP.APPLICATION_REF_REASON),TRX.REASON_CODE) , DECODE(APP.STATUS,'UNAPP',NULL,APP.APPLICATION_REF_REASON) , TRX.REASON_CODE , APP.CASH_RECEIPT_ID , 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.STATUS,'UNAPP',NULL,APP.RECEIVABLE_APPLICATION_ID) , CUST_ACCT.ACCOUNT_NAME HAVING DECODE(PS.CLASS,'PMT',(SUM(APP.AMOUNT_APPLIED) * -1),PS.AMOUNT_DUE_REMAINING) <> 0 UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID , decode(PS.PAYMENT_SCHEDULE_ID, -1,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','ON_ACC'), -4,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS','CLAIM_INV')) , NULL BILLING_NUMBER , PS.TERM_ID , PS.TERMS_SEQUENCE_NUMBER , PS.DUE_DATE , PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , NVL(PS.AMOUNT_DUE_REMAINING,0) AMOUNT_DUE_REMAINING , NVL(PS.CUSTOMER_TRX_ID , -1) CUSTOMER_TRX_ID , PS.TRX_DATE , PS.AMOUNT_DUE_ORIGINAL , NVL(PS.DISCOUNT_TAKEN_EARNED, 0) DISCOUNT_TAKEN_EARNED , NVL(PS.DISCOUNT_TAKEN_UNEARNED, 0) DISCOUNT_TAKEN_UNEARNED , NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL, 0) AMOUNT_LINE_ITEMS_ORIGINAL , PS.CUSTOMER_ID CUSTOMER_ID , NULL CUSTOMER_NAME , NULL CUSTOMER_NUMBER , NULL LOCATION , NULL , NULL , NULL GRACE_DAYS , arpt_sql_func_util.get_trx_type_details(NULL,'NAME') TYPE_NAME , arpt_sql_func_util.get_trx_type_details(NULL,'ALLOW_OVERAPPLICATION_FLAG') ALLOW_OVERAPPLICATION_FLAG , arpt_sql_func_util.get_trx_type_details(NULL,'NATURAL_APPLICATION_ONLY_FLAG') NATURAL_APPLICATION_ONLY_FLAG , arpt_sql_func_util.get_trx_type_details(NULL,'CREATION_SIGN') CREATION_SIGN , PS.CLASS , arpt_sql_func_util.get_lookup_meaning('INV/CM',PS.CLASS) CLASS_MEANING , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL DEFAULT_REFERENCE , NULL APPLICATION_STATUS , NULL APPLICATION_STATUS_MEANING , NULL CUSTOMER_REFERENCE , NULL REASON_CODE , ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON',NULL) REASON_MEANING , NULL APPLIED_REC_APP_ID , NULL APPLIED_CASH_RECEIPT_ID , NULL ACCOUNT_DESCRIPTION FROM AR_PAYMENT_SCHEDULES PS WHERE PS.PAYMENT_SCHEDULE_ID IN (-1,-4)

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PS
, TRX
, CI
, CUST_ACCT
, PARTY
, SITE_USES
, BS) */ PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, CI.CONS_BILLING_NUMBER BILLING_NUMBER
, PS.TERM_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, DECODE(PS.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, NVL(PS.AMOUNT_DUE_REMAINING
, 0)) AMOUNT_DUE_REMAINING
, NVL(PS.CUSTOMER_TRX_ID
, -1) CUSTOMER_TRX_ID
, PS.TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL
, NVL(PS.DISCOUNT_TAKEN_EARNED
, 0) DISCOUNT_TAKEN_EARNED
, NVL(PS.DISCOUNT_TAKEN_UNEARNED
, 0) DISCOUNT_TAKEN_UNEARNED
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) AMOUNT_LINE_ITEMS_ORIGINAL
, PS.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, DECODE(PS.CLASS
, 'BR'
, BR_SITE_USES.LOCATION
, 'PMT'
, RCT_SITE_USES.LOCATION
, SITE_USES.LOCATION) LOCATION
, RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG
, RA_TERMS.PARTIAL_DISCOUNT_FLAG
, CP.DISCOUNT_GRACE_DAYS GRACE_DAYS
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'NAME') TYPE_NAME
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'ALLOW_OVERAPPLICATION_FLAG') ALLOW_OVERAPPLICATION_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'NATURAL_APPLICATION_ONLY_FLAG') NATURAL_APPLICATION_ONLY_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'CREATION_SIGN') CREATION_SIGN
, PS.CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CLASS_MEANING
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.PURCHASE_ORDER
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_NUM)
, BS.DEFAULT_REFERENCE) DEFAULT_REFERENCE
, APP.STATUS APPLICATION_STATUS
, DECODE(APP.STATUS
, NULL
, NULL
, 'UNAPP'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, 'UNAPP')
, PS_DUMMY.TRX_NUMBER) APPLICATION_STATUS_MEANING
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.CUSTOMER_REFERENCE)
, TRX.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE) REASON_CODE
, 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'
, TRX.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, TRX.REASON_CODE)) REASON_MEANING
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.RECEIVABLE_APPLICATION_ID) APPLIED_REC_APP_ID
, APP.CASH_RECEIPT_ID APPLIED_CASH_RECEIPT_ID
, CUST_ACCT.ACCOUNT_NAME ACCOUNT_DESCRIPTION
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUSTOMER_TRX TRX
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_SITE_USES BR_SITE_USES
, HZ_CUST_SITE_USES RCT_SITE_USES
, RA_TERMS
, HZ_CUSTOMER_PROFILES CP
, AR_RECEIVABLE_APPLICATIONS APP
, AR_PAYMENT_SCHEDULES PS_DUMMY
, AR_CONS_INV CI
, RA_BATCH_SOURCES BS
WHERE PS.STATUS <> 'CL'
AND PS.CLASS <> 'GUAR'
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.RESERVED_TYPE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.RESERVED_VALUE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND TRX.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND SITE_USES.SITE_USE_ID(+) = TRX.BILL_TO_SITE_USE_ID
AND BR_SITE_USES.SITE_USE_ID(+) = TRX.DRAWEE_SITE_USE_ID /* 01-AUG-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND RCT_SITE_USES.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID
AND PS.TERM_ID = RA_TERMS.TERM_ID (+)
AND CP.CUST_ACCOUNT_ID(+) = TRX.BILL_TO_CUSTOMER_ID
AND CP.SITE_USE_ID IS NULL
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND BS.BATCH_SOURCE_ID (+) = TRX.BATCH_SOURCE_ID
AND PS.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'
AND PS.PAYMENT_SCHEDULE_ID > 0 /* BUG 6215163*/ GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, CI.CONS_BILLING_NUMBER
, PS.TERM_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE
, PS.INVOICE_CURRENCY_CODE
, PS.CUSTOMER_TRX_ID
, PS.AMOUNT_DUE_REMAINING
, PS.TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, PS.CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, DECODE(PS.CLASS
, 'BR'
, BR_SITE_USES.LOCATION
, 'PMT'
, RCT_SITE_USES.LOCATION
, SITE_USES.LOCATION)
, RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG
, RA_TERMS.PARTIAL_DISCOUNT_FLAG
, CP.DISCOUNT_GRACE_DAYS
, TRX.CUST_TRX_TYPE_ID
, PS.CLASS
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.PURCHASE_ORDER
, PS_DUMMY.TRX_NUMBER
, APP.STATUS
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_NUM)
, BS.DEFAULT_REFERENCE)
, TRX.CUSTOMER_REFERENCE
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.CUSTOMER_REFERENCE)
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE)
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE
, APP.CASH_RECEIPT_ID
, 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.STATUS
, 'UNAPP'
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, CUST_ACCT.ACCOUNT_NAME HAVING DECODE(PS.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, PS.AMOUNT_DUE_REMAINING) <> 0 UNION ALL SELECT PS.PAYMENT_SCHEDULE_ID
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'ON_ACC')
, -4
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CLAIM_INV'))
, NULL BILLING_NUMBER
, PS.TERM_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, NVL(PS.AMOUNT_DUE_REMAINING
, 0) AMOUNT_DUE_REMAINING
, NVL(PS.CUSTOMER_TRX_ID
, -1) CUSTOMER_TRX_ID
, PS.TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL
, NVL(PS.DISCOUNT_TAKEN_EARNED
, 0) DISCOUNT_TAKEN_EARNED
, NVL(PS.DISCOUNT_TAKEN_UNEARNED
, 0) DISCOUNT_TAKEN_UNEARNED
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) AMOUNT_LINE_ITEMS_ORIGINAL
, PS.CUSTOMER_ID CUSTOMER_ID
, NULL CUSTOMER_NAME
, NULL CUSTOMER_NUMBER
, NULL LOCATION
, NULL
, NULL
, NULL GRACE_DAYS
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(NULL
, 'NAME') TYPE_NAME
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(NULL
, 'ALLOW_OVERAPPLICATION_FLAG') ALLOW_OVERAPPLICATION_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(NULL
, 'NATURAL_APPLICATION_ONLY_FLAG') NATURAL_APPLICATION_ONLY_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(NULL
, 'CREATION_SIGN') CREATION_SIGN
, PS.CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CLASS_MEANING
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL DEFAULT_REFERENCE
, NULL APPLICATION_STATUS
, NULL APPLICATION_STATUS_MEANING
, NULL CUSTOMER_REFERENCE
, NULL REASON_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, NULL) REASON_MEANING
, NULL APPLIED_REC_APP_ID
, NULL APPLIED_CASH_RECEIPT_ID
, NULL ACCOUNT_DESCRIPTION
FROM AR_PAYMENT_SCHEDULES PS
WHERE PS.PAYMENT_SCHEDULE_ID IN (-1
, -4)