VIEW: APPS.IEX_CUST_INTERACTION_HISTORY_V

Source

View Text - Preformatted

SELECT DISTINCT JIIN.interaction_id interaction_id, JIA.cust_Account_id Cust_Account_id, JIIN.start_date_time start_date_time, JREV.resource_name resource_name, (Select Decode(Nvl(Count(1), 0), 0, 'N', 'Y') from jtf_ih_activities where Interaction_id = JIIN.Interaction_Id AND doc_ref = 'IEX_DISPUTE') Disputed, (Select Decode(Nvl(Count(1), 0), 0, 'N', 'Y') from jtf_ih_activities where Interaction_id = JIIN.Interaction_Id AND doc_ref = 'IEX_PAYMENT') Paid, (Select Decode(Nvl(Count(1), 0), 0, 'N', 'Y') from jtf_ih_activities where Interaction_id = JIIN.Interaction_Id AND doc_ref = 'IEX_DUNNING') Corresponded, (Select Decode(Nvl(Count(1), 0), 0, 'N', 'Y') from jtf_ih_activities where Interaction_id = JIIN.Interaction_Id AND doc_ref = 'IEX_PROMISE') promise_to_pay, (Select Decode(Nvl(Count(1), 0), 0, 'N', 'Y') from jtf_ih_activities where Interaction_id = JIIN.Interaction_Id AND doc_ref = 'IEX_ADJUSTMENT') adjusted, JIIN.PRIMARY_PARTY_ID From JTF_IH_INTERACTIONS JIIN, JTF_RS_RESOURCE_EXTNS_VL JREV, JTF_IH_ACTIVITIES JIA WHERE JIIN.Resource_Id = JREV.resource_id AND JIIN.interaction_id = JIA.interaction_id

View Text - HTML Formatted

SELECT DISTINCT JIIN.INTERACTION_ID INTERACTION_ID
, JIA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, JIIN.START_DATE_TIME START_DATE_TIME
, JREV.RESOURCE_NAME RESOURCE_NAME
, (SELECT DECODE(NVL(COUNT(1)
, 0)
, 0
, 'N'
, 'Y')
FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = JIIN.INTERACTION_ID
AND DOC_REF = 'IEX_DISPUTE') DISPUTED
, (SELECT DECODE(NVL(COUNT(1)
, 0)
, 0
, 'N'
, 'Y')
FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = JIIN.INTERACTION_ID
AND DOC_REF = 'IEX_PAYMENT') PAID
, (SELECT DECODE(NVL(COUNT(1)
, 0)
, 0
, 'N'
, 'Y')
FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = JIIN.INTERACTION_ID
AND DOC_REF = 'IEX_DUNNING') CORRESPONDED
, (SELECT DECODE(NVL(COUNT(1)
, 0)
, 0
, 'N'
, 'Y')
FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = JIIN.INTERACTION_ID
AND DOC_REF = 'IEX_PROMISE') PROMISE_TO_PAY
, (SELECT DECODE(NVL(COUNT(1)
, 0)
, 0
, 'N'
, 'Y')
FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = JIIN.INTERACTION_ID
AND DOC_REF = 'IEX_ADJUSTMENT') ADJUSTED
, JIIN.PRIMARY_PARTY_ID
FROM JTF_IH_INTERACTIONS JIIN
, JTF_RS_RESOURCE_EXTNS_VL JREV
, JTF_IH_ACTIVITIES JIA
WHERE JIIN.RESOURCE_ID = JREV.RESOURCE_ID
AND JIIN.INTERACTION_ID = JIA.INTERACTION_ID