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_ALL_HISTORY_V

Source

View Text - Preformatted


SELECT ARCR.CASH_RECEIPT_ID ACTIVITY_ID, HCA.CUST_ACCOUNT_ID ACCOUNT_ID, HCA.PARTY_ID PARTY_CUST_ID, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID, TO_NUMBER(NULL) DELINQUENCY_ID, ARCR.STATUS STATUS, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CHECK_STATUS', ARCR.STATUS) STATUS_MEANING, ARCR.RECEIPT_DATE ACTIVITY_DATE, ARCR.RECEIPT_DATE CREATION_DATE, 'Payment' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_HISTORY_TYPE' ,'PAYMENT') ACTIVITY_TYPE_MEANING, ARCR.RECEIPT_NUMBER TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, ARCR.AMOUNT ACTIVITY_AMOUNT, ARCR.CURRENCY_CODE CURRENCY_CODE, ARCR.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(ARCR.ORG_ID) OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, TO_CHAR(NULL) LAST_NOTES FROM HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNTS HCA, AR_CASH_RECEIPTS ARCR WHERE SITE_USES.SITE_USE_ID(+) = ARCR.CUSTOMER_SITE_USE_ID AND HCA.CUST_ACCOUNT_ID = ARCR.PAY_FROM_CUSTOMER UNION ALL SELECT ADJ.ADJUSTMENT_ID ACTIVITY_ID, PS.CUSTOMER_ID ACT_ACCOUNT_ID, HCA.PARTY_ID PARTY_CUST_ID, PS.CUSTOMER_SITE_USE_ID, IDL.DELINQUENCY_ID, ADJ.STATUS STATUS, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE', ADJ.STATUS) STATUS_MEANING, ADJ.APPLY_DATE ACTIVITY_DATE, ADJ.APPLY_DATE CREATION_DATE, 'Adjustment' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_HISTORY_TYPE' ,'ADJUSTMENT') ACTIVITY_TYPE_MEANING, CT.TRX_NUMBER TRANSACTION_NUMBER, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, ADJ.AMOUNT ACTIVITY_AMOUNT, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, ADJ.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(ADJ.ORG_ID) OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL LAST_NOTES FROM AR_ADJUSTMENTS ADJ, RA_CUSTOMER_TRX_ALL CT, AR_PAYMENT_SCHEDULES_ALL PS, IEX_DELINQUENCIES IDL, HZ_CUST_ACCOUNTS HCA WHERE ADJ.RECEIVABLES_TRX_ID <> -15 AND ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID(+) AND PS.PAYMENT_SCHEDULE_ID=IDL.PAYMENT_SCHEDULE_ID(+) AND ADJ.ORG_ID = PS.ORG_ID(+) AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+) AND ADJ.ORG_ID = CT.ORG_ID(+) AND PS.AMOUNT_ADJUSTED >-10000000 AND PS.CUSTOMER_TRX_ID = IDL.TRANSACTION_ID(+) AND PS.CUSTOMER_ID = IDL.CUST_ACCOUNT_ID(+) AND PS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID UNION ALL /*DISPUTE*/ SELECT REQ.REQUEST_ID ACTIVITY_ID, PS.CUSTOMER_ID ACCOUNT_ID, HCA.PARTY_ID PARTY_CUST_ID, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, TO_NUMBER(NULL) DELINQUENCY_ID, REQ.STATUS STATUS, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REQUEST_STATUS', REQ.STATUS) STATUS_MEANING, REQ.CREATION_DATE ACTIVITY_DATE, REQ.CREATION_DATE CREATION_DATE, 'Dispute' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_HISTORY_TYPE' ,'DISPUTE') ACTIVITY_TYPE_MEANING, TRX.TRX_NUMBER TRANSACTION_NUMBER, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, DECODE(REQ.TOTAL_AMOUNT, 0, REQ.LINE_AMOUNT, REQ.TOTAL_AMOUNT) ACTIVITY_AMOUNT, TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE, TRX.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(TRX.ORG_ID) OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_DISPUTES DIS, RA_CUSTOMER_TRX_ALL TRX, RA_CM_REQUESTS REQ, HZ_CUST_ACCOUNTS HCA, AR_PAYMENT_SCHEDULES_ALL PS WHERE REQ.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND PS.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID AND PS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID AND REQ.REQUEST_ID = DIS.CM_REQUEST_ID(+) AND NVL(PS.TERMS_SEQUENCE_NUMBER,1)=1 UNION ALL /*DELINQUENCY*/ SELECT DUN.DUNNING_ID ACTIVITY_ID, DEL.CUST_ACCOUNT_ID ACCOUNT_ID, DEL.PARTY_CUST_ID PARTY_CUST_ID, DEL.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, DUN.DELINQUENCY_ID DELINQUENCY_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) ACTIVITY_DATE, DUN.CREATION_DATE CREATION_DATE, 'Dunning' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_AR_DUNNING' ,'IEX_DUNNING') ACTIVITY_TYPE_MEANING, DECODE(DUN.DUNNING_LEVEL, 'DELINQUENCY', CT.TRX_NUMBER, NULL) TRANSACTION_NUMBER, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT, DUN.CURRENCY_CODE CURRENCY_CODE, DEL.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(DEL.ORG_ID) OPERATING_UNIT, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_DUNNINGS DUN, IEX_DELINQUENCIES DEL, RA_CUSTOMER_TRX CT, IEX_DUNNING_PLANS_TL DPL WHERE DEL.DELINQUENCY_ID = DUN.DELINQUENCY_ID AND DEL.TRANSACTION_ID = CT.CUSTOMER_TRX_ID AND DUN.DUNNING_LEVEL = 'DELINQUENCY' AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+) AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*ACCOUNT*/ SELECT DUN.DUNNING_ID ACTIVITY_ID, DUN.DUNNING_OBJECT_ID ACCOUNT_ID, HA.PARTY_ID PARTY_CUST_ID, NULL CUSTOMER_SITE_USE_ID, DUN.DELINQUENCY_ID DELINQUENCY_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) ACTIVITY_DATE, DUN.CREATION_DATE CREATION_DATE, 'Dunning' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_AR_DUNNING' ,'IEX_DUNNING') ACTIVITY_TYPE_MEANING, NULL TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT, DUN.CURRENCY_CODE CURRENCY_CODE, NULL ORG_ID, NULL OPERATING_UNIT, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_DUNNINGS DUN, HZ_CUST_ACCOUNTS HA, IEX_DUNNING_PLANS_TL DPL WHERE DUN.DUNNING_OBJECT_ID = HA.CUST_ACCOUNT_ID AND DUN.DUNNING_LEVEL = 'ACCOUNT' AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+) AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*CUSTOMER*/ SELECT DUN.DUNNING_ID ACTIVITY_ID, NULL ACCOUNT_ID, DUN.DUNNING_OBJECT_ID PARTY_CUST_ID, NULL CUSTOMER_SITE_USE_ID, DUN.DELINQUENCY_ID DELINQUENCY_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) ACTIVITY_DATE, DUN.CREATION_DATE CREATION_DATE, 'Dunning' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_AR_DUNNING' ,'IEX_DUNNING') ACTIVITY_TYPE_MEANING, NULL TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT, DUN.CURRENCY_CODE CURRENCY_CODE, NULL ORG_ID, NULL OPERATING_UNIT, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_DUNNINGS DUN, HZ_PARTIES HP, IEX_DUNNING_PLANS_TL DPL WHERE HP.PARTY_ID = DUN.DUNNING_OBJECT_ID AND DUN.DUNNING_LEVEL = 'CUSTOMER' AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+) AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*BILL TO*/ SELECT DUN.DUNNING_ID ACTIVITY_ID, HA.CUST_ACCOUNT_ID ACCOUNT_ID, HA.PARTY_ID PARTY_CUST_ID, DUN.DUNNING_OBJECT_ID CUSTOMER_SITE_USE_ID, DUN.DELINQUENCY_ID DELINQUENCY_ID, DUN.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS', DUN.STATUS) STATUS_MEANING, NVL(DUN.CORRESPONDENCE_DATE , DUN.CREATION_DATE) ACTIVITY_DATE, DUN.CREATION_DATE CREATION_DATE, 'Dunning' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_AR_DUNNING' ,'IEX_DUNNING') ACTIVITY_TYPE_MEANING, NULL TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT, DUN.CURRENCY_CODE CURRENCY_CODE, NULL ORG_ID, NULL OPERATING_UNIT, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID, DPL.NAME DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_DUNNINGS DUN, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_CUST_SITE_USES SITE_USES, HZ_CUST_ACCOUNTS HA, IEX_DUNNING_PLANS_TL DPL WHERE DUN.DUNNING_OBJECT_ID = SITE_USES.SITE_USE_ID AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.CUST_ACCOUNT_ID = HA.CUST_ACCOUNT_ID AND DUN.DUNNING_LEVEL = 'BILL_TO' AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+) AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL SELECT NULL ACTIVITY_ID, COR.CUSTOMER_ID ACCOUNT_ID, E.PARTY_ID PARTY_CUST_ID, COR.SITE_USE_ID CUSTOMER_SITE_USE_ID, NULL DELINQUENCY_ID, 'CLOSE' STATUS, 'Close' STATUS_MEANING, COR.CORRESPONDENCE_DATE ACTIVITY_DATE, COR.CORRESPONDENCE_DATE CREATION_DATE, 'AR Dunning' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_AR_DUNNING' ,'AR_DUNNING' ) ACTIVITY_TYPE_MEANING, NULL TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, SUM(CPS.AMOUNT_DUE_REMAINING) ACTIVITY_AMOUNT, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, COR.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(COR.ORG_ID) OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL LAST_NOTES FROM AR_CORRESPONDENCES COR, AR_CORRESPONDENCE_PAY_SCHED CPS, AR_PAYMENT_SCHEDULES PS, HZ_CUST_ACCOUNTS E WHERE CPS.CORRESPONDENCE_ID = COR.CORRESPONDENCE_ID AND PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID AND COR.PRELIMINARY_FLAG = 'N' AND COR.CUSTOMER_ID = E.CUST_ACCOUNT_ID GROUP BY COR.CORRESPONDENCE_DATE, E.PARTY_ID, PS.INVOICE_CURRENCY_CODE, COR.CORRESPONDENCE_ID, COR.CUSTOMER_ID, COR.SITE_USE_ID, COR.ORG_ID UNION ALL /* PROMISE */ SELECT PRD.PROMISE_DETAIL_ID ACTIVITY_ID, HCA.CUST_ACCOUNT_ID ACCOUNT_ID, HP.PARTY_ID PARTY_CUST_ID, PSA.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, PRD.DELINQUENCY_ID DELINQUENCY_ID, PRD.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_PROMISE_STATUSES', PRD.STATUS) STATUS_MEANING, PRD.PROMISE_DATE ACTIVITY_DATE, PRD.CREATION_DATE CREATION_DATE, 'Promise' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_HISTORY_TYPE' ,'PROMISE') ACTIVITY_TYPE_MEANING, PSA.TRX_NUMBER TRANSACTION_NUMBER, PSA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PRD.PROMISE_AMOUNT ACTIVITY_AMOUNT, PRD.CURRENCY_CODE CURRENCY_CODE, PSA.ORG_ID ORG_ID, MO_GLOBAL.GET_OU_NAME(PSA.ORG_ID) OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL LAST_NOTES FROM IEX_PROMISE_DETAILS PRD, IEX_DELINQUENCIES DEL, AR_PAYMENT_SCHEDULES_ALL PSA, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP WHERE PRD.CONTRACT_ID IS NULL AND PRD.CNSLD_INVOICE_ID IS NULL AND PRD.DELINQUENCY_ID IS NOT NULL AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID AND HCA.CUST_ACCOUNT_ID = DEL.CUST_ACCOUNT_ID AND DEL.PARTY_CUST_ID = HP.PARTY_ID AND HCA.PARTY_ID = HP.PARTY_ID UNION ALL SELECT DISTINCT JIIN.INTERACTION_ID ACTIVITY_ID, DECODE(IEX_UTILITIES.GET_VIEW_BY_LEVEL, 'PARTY', TO_NUMBER(NULL), JIA.CUST_ACCOUNT_ID) ACCOUNT_ID, JIIN.PRIMARY_PARTY_ID PARTY_CUST_ID, NULL CUSTOMER_SITE_USE_ID, NULL DELINQUENCY_ID, NULL STATUS, NULL STATUS_MEANING, JIIN.START_DATE_TIME ACTIVITY_DATE, JIIN.START_DATE_TIME CREATION_DATE, 'Interaction' ACTIVITY_TYPE, iex_utilities.get_lookup_meaning ('IEX_HISTORY_TYPE' ,'CALL') ACTIVITY_TYPE_MEANING, NULL TRANSACTION_NUMBER, TO_NUMBER(NULL) CUSTOMER_TRX_ID, NULL ACTIVITY_AMOUNT, NULL CURRENCY_CODE, NULL ORG_ID, NULL OPERATING_UNIT, NULL DUNNING_PLAN_ID, NULL DUNNING_PLAN_NAME, NULL LAST_NOTES FROM JTF_IH_INTERACTIONS JIIN, JTF_IH_ACTIVITIES JIA WHERE JIIN.INTERACTION_ID = JIA.INTERACTION_ID(+)

View Text - HTML Formatted

SELECT ARCR.CASH_RECEIPT_ID ACTIVITY_ID
, HCA.CUST_ACCOUNT_ID ACCOUNT_ID
, HCA.PARTY_ID PARTY_CUST_ID
, SITE_USES.SITE_USE_ID CUSTOMER_SITE_USE_ID
, TO_NUMBER(NULL) DELINQUENCY_ID
, ARCR.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CHECK_STATUS'
, ARCR.STATUS) STATUS_MEANING
, ARCR.RECEIPT_DATE ACTIVITY_DATE
, ARCR.RECEIPT_DATE CREATION_DATE
, 'PAYMENT' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_HISTORY_TYPE'
, 'PAYMENT') ACTIVITY_TYPE_MEANING
, ARCR.RECEIPT_NUMBER TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, ARCR.AMOUNT ACTIVITY_AMOUNT
, ARCR.CURRENCY_CODE CURRENCY_CODE
, ARCR.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(ARCR.ORG_ID) OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, TO_CHAR(NULL) LAST_NOTES
FROM HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS HCA
, AR_CASH_RECEIPTS ARCR
WHERE SITE_USES.SITE_USE_ID(+) = ARCR.CUSTOMER_SITE_USE_ID
AND HCA.CUST_ACCOUNT_ID = ARCR.PAY_FROM_CUSTOMER UNION ALL SELECT ADJ.ADJUSTMENT_ID ACTIVITY_ID
, PS.CUSTOMER_ID ACT_ACCOUNT_ID
, HCA.PARTY_ID PARTY_CUST_ID
, PS.CUSTOMER_SITE_USE_ID
, IDL.DELINQUENCY_ID
, ADJ.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE'
, ADJ.STATUS) STATUS_MEANING
, ADJ.APPLY_DATE ACTIVITY_DATE
, ADJ.APPLY_DATE CREATION_DATE
, 'ADJUSTMENT' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_HISTORY_TYPE'
, 'ADJUSTMENT') ACTIVITY_TYPE_MEANING
, CT.TRX_NUMBER TRANSACTION_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, ADJ.AMOUNT ACTIVITY_AMOUNT
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, ADJ.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(ADJ.ORG_ID) OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM AR_ADJUSTMENTS ADJ
, RA_CUSTOMER_TRX_ALL CT
, AR_PAYMENT_SCHEDULES_ALL PS
, IEX_DELINQUENCIES IDL
, HZ_CUST_ACCOUNTS HCA
WHERE ADJ.RECEIVABLES_TRX_ID <> -15
AND ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID(+)
AND PS.PAYMENT_SCHEDULE_ID=IDL.PAYMENT_SCHEDULE_ID(+)
AND ADJ.ORG_ID = PS.ORG_ID(+)
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND ADJ.ORG_ID = CT.ORG_ID(+)
AND PS.AMOUNT_ADJUSTED >-10000000
AND PS.CUSTOMER_TRX_ID = IDL.TRANSACTION_ID(+)
AND PS.CUSTOMER_ID = IDL.CUST_ACCOUNT_ID(+)
AND PS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID UNION ALL /*DISPUTE*/ SELECT REQ.REQUEST_ID ACTIVITY_ID
, PS.CUSTOMER_ID ACCOUNT_ID
, HCA.PARTY_ID PARTY_CUST_ID
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, TO_NUMBER(NULL) DELINQUENCY_ID
, REQ.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REQUEST_STATUS'
, REQ.STATUS) STATUS_MEANING
, REQ.CREATION_DATE ACTIVITY_DATE
, REQ.CREATION_DATE CREATION_DATE
, 'DISPUTE' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_HISTORY_TYPE'
, 'DISPUTE') ACTIVITY_TYPE_MEANING
, TRX.TRX_NUMBER TRANSACTION_NUMBER
, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, DECODE(REQ.TOTAL_AMOUNT
, 0
, REQ.LINE_AMOUNT
, REQ.TOTAL_AMOUNT) ACTIVITY_AMOUNT
, TRX.INVOICE_CURRENCY_CODE CURRENCY_CODE
, TRX.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(TRX.ORG_ID) OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_DISPUTES DIS
, RA_CUSTOMER_TRX_ALL TRX
, RA_CM_REQUESTS REQ
, HZ_CUST_ACCOUNTS HCA
, AR_PAYMENT_SCHEDULES_ALL PS
WHERE REQ.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND PS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND REQ.REQUEST_ID = DIS.CM_REQUEST_ID(+)
AND NVL(PS.TERMS_SEQUENCE_NUMBER
, 1)=1 UNION ALL /*DELINQUENCY*/ SELECT DUN.DUNNING_ID ACTIVITY_ID
, DEL.CUST_ACCOUNT_ID ACCOUNT_ID
, DEL.PARTY_CUST_ID PARTY_CUST_ID
, DEL.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) ACTIVITY_DATE
, DUN.CREATION_DATE CREATION_DATE
, 'DUNNING' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_AR_DUNNING'
, 'IEX_DUNNING') ACTIVITY_TYPE_MEANING
, DECODE(DUN.DUNNING_LEVEL
, 'DELINQUENCY'
, CT.TRX_NUMBER
, NULL) TRANSACTION_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT
, DUN.CURRENCY_CODE CURRENCY_CODE
, DEL.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(DEL.ORG_ID) OPERATING_UNIT
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_DUNNINGS DUN
, IEX_DELINQUENCIES DEL
, RA_CUSTOMER_TRX CT
, IEX_DUNNING_PLANS_TL DPL
WHERE DEL.DELINQUENCY_ID = DUN.DELINQUENCY_ID
AND DEL.TRANSACTION_ID = CT.CUSTOMER_TRX_ID
AND DUN.DUNNING_LEVEL = 'DELINQUENCY'
AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*ACCOUNT*/ SELECT DUN.DUNNING_ID ACTIVITY_ID
, DUN.DUNNING_OBJECT_ID ACCOUNT_ID
, HA.PARTY_ID PARTY_CUST_ID
, NULL CUSTOMER_SITE_USE_ID
, DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) ACTIVITY_DATE
, DUN.CREATION_DATE CREATION_DATE
, 'DUNNING' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_AR_DUNNING'
, 'IEX_DUNNING') ACTIVITY_TYPE_MEANING
, NULL TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT
, DUN.CURRENCY_CODE CURRENCY_CODE
, NULL ORG_ID
, NULL OPERATING_UNIT
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_DUNNINGS DUN
, HZ_CUST_ACCOUNTS HA
, IEX_DUNNING_PLANS_TL DPL
WHERE DUN.DUNNING_OBJECT_ID = HA.CUST_ACCOUNT_ID
AND DUN.DUNNING_LEVEL = 'ACCOUNT'
AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*CUSTOMER*/ SELECT DUN.DUNNING_ID ACTIVITY_ID
, NULL ACCOUNT_ID
, DUN.DUNNING_OBJECT_ID PARTY_CUST_ID
, NULL CUSTOMER_SITE_USE_ID
, DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) ACTIVITY_DATE
, DUN.CREATION_DATE CREATION_DATE
, 'DUNNING' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_AR_DUNNING'
, 'IEX_DUNNING') ACTIVITY_TYPE_MEANING
, NULL TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT
, DUN.CURRENCY_CODE CURRENCY_CODE
, NULL ORG_ID
, NULL OPERATING_UNIT
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_DUNNINGS DUN
, HZ_PARTIES HP
, IEX_DUNNING_PLANS_TL DPL
WHERE HP.PARTY_ID = DUN.DUNNING_OBJECT_ID
AND DUN.DUNNING_LEVEL = 'CUSTOMER'
AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL /*BILL TO*/ SELECT DUN.DUNNING_ID ACTIVITY_ID
, HA.CUST_ACCOUNT_ID ACCOUNT_ID
, HA.PARTY_ID PARTY_CUST_ID
, DUN.DUNNING_OBJECT_ID CUSTOMER_SITE_USE_ID
, DUN.DELINQUENCY_ID DELINQUENCY_ID
, DUN.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DUNNING_STATUS'
, DUN.STATUS) STATUS_MEANING
, NVL(DUN.CORRESPONDENCE_DATE
, DUN.CREATION_DATE) ACTIVITY_DATE
, DUN.CREATION_DATE CREATION_DATE
, 'DUNNING' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_AR_DUNNING'
, 'IEX_DUNNING') ACTIVITY_TYPE_MEANING
, NULL TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, DUN.AMOUNT_DUE_REMAINING ACTIVITY_AMOUNT
, DUN.CURRENCY_CODE CURRENCY_CODE
, NULL ORG_ID
, NULL OPERATING_UNIT
, DUN.DUNNING_PLAN_ID DUNNING_PLAN_ID
, DPL.NAME DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_DUNNINGS DUN
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_ACCOUNTS HA
, IEX_DUNNING_PLANS_TL DPL
WHERE DUN.DUNNING_OBJECT_ID = SITE_USES.SITE_USE_ID
AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HA.CUST_ACCOUNT_ID
AND DUN.DUNNING_LEVEL = 'BILL_TO'
AND DUN.DUNNING_PLAN_ID = DPL.DUNNING_PLAN_ID(+)
AND DPL.LANGUAGE(+) = USERENV('LANG') UNION ALL SELECT NULL ACTIVITY_ID
, COR.CUSTOMER_ID ACCOUNT_ID
, E.PARTY_ID PARTY_CUST_ID
, COR.SITE_USE_ID CUSTOMER_SITE_USE_ID
, NULL DELINQUENCY_ID
, 'CLOSE' STATUS
, 'CLOSE' STATUS_MEANING
, COR.CORRESPONDENCE_DATE ACTIVITY_DATE
, COR.CORRESPONDENCE_DATE CREATION_DATE
, 'AR DUNNING' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_AR_DUNNING'
, 'AR_DUNNING' ) ACTIVITY_TYPE_MEANING
, NULL TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, SUM(CPS.AMOUNT_DUE_REMAINING) ACTIVITY_AMOUNT
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, COR.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(COR.ORG_ID) OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM AR_CORRESPONDENCES COR
, AR_CORRESPONDENCE_PAY_SCHED CPS
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS E
WHERE CPS.CORRESPONDENCE_ID = COR.CORRESPONDENCE_ID
AND PS.PAYMENT_SCHEDULE_ID = CPS.PAYMENT_SCHEDULE_ID
AND COR.PRELIMINARY_FLAG = 'N'
AND COR.CUSTOMER_ID = E.CUST_ACCOUNT_ID GROUP BY COR.CORRESPONDENCE_DATE
, E.PARTY_ID
, PS.INVOICE_CURRENCY_CODE
, COR.CORRESPONDENCE_ID
, COR.CUSTOMER_ID
, COR.SITE_USE_ID
, COR.ORG_ID UNION ALL /* PROMISE */ SELECT PRD.PROMISE_DETAIL_ID ACTIVITY_ID
, HCA.CUST_ACCOUNT_ID ACCOUNT_ID
, HP.PARTY_ID PARTY_CUST_ID
, PSA.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PRD.DELINQUENCY_ID DELINQUENCY_ID
, PRD.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_PROMISE_STATUSES'
, PRD.STATUS) STATUS_MEANING
, PRD.PROMISE_DATE ACTIVITY_DATE
, PRD.CREATION_DATE CREATION_DATE
, 'PROMISE' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_HISTORY_TYPE'
, 'PROMISE') ACTIVITY_TYPE_MEANING
, PSA.TRX_NUMBER TRANSACTION_NUMBER
, PSA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PRD.PROMISE_AMOUNT ACTIVITY_AMOUNT
, PRD.CURRENCY_CODE CURRENCY_CODE
, PSA.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(PSA.ORG_ID) OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM IEX_PROMISE_DETAILS PRD
, IEX_DELINQUENCIES DEL
, AR_PAYMENT_SCHEDULES_ALL PSA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE PRD.CONTRACT_ID IS NULL
AND PRD.CNSLD_INVOICE_ID IS NULL
AND PRD.DELINQUENCY_ID IS NOT NULL
AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID
AND HCA.CUST_ACCOUNT_ID = DEL.CUST_ACCOUNT_ID
AND DEL.PARTY_CUST_ID = HP.PARTY_ID
AND HCA.PARTY_ID = HP.PARTY_ID UNION ALL SELECT DISTINCT JIIN.INTERACTION_ID ACTIVITY_ID
, DECODE(IEX_UTILITIES.GET_VIEW_BY_LEVEL
, 'PARTY'
, TO_NUMBER(NULL)
, JIA.CUST_ACCOUNT_ID) ACCOUNT_ID
, JIIN.PRIMARY_PARTY_ID PARTY_CUST_ID
, NULL CUSTOMER_SITE_USE_ID
, NULL DELINQUENCY_ID
, NULL STATUS
, NULL STATUS_MEANING
, JIIN.START_DATE_TIME ACTIVITY_DATE
, JIIN.START_DATE_TIME CREATION_DATE
, 'INTERACTION' ACTIVITY_TYPE
, IEX_UTILITIES.GET_LOOKUP_MEANING ('IEX_HISTORY_TYPE'
, 'CALL') ACTIVITY_TYPE_MEANING
, NULL TRANSACTION_NUMBER
, TO_NUMBER(NULL) CUSTOMER_TRX_ID
, NULL ACTIVITY_AMOUNT
, NULL CURRENCY_CODE
, NULL ORG_ID
, NULL OPERATING_UNIT
, NULL DUNNING_PLAN_ID
, NULL DUNNING_PLAN_NAME
, NULL LAST_NOTES
FROM JTF_IH_INTERACTIONS JIIN
, JTF_IH_ACTIVITIES JIA
WHERE JIIN.INTERACTION_ID = JIA.INTERACTION_ID(+)