VIEW: APPS.OKC_LAUNCH_COMMGRID_V

Source

View Text - Preformatted

SELECT INT.INTERACTION_ID INTERACTION_ID, ACT.DOC_ID CONTRACT_ID, CUSTTYP.name ROLE, hz.party_name PARTY_NAME, INT.PARTY_ID PARTY_ID, RES.RESOURCE_NAME AGENT_NAME, INT.RESOURCE_ID AGENT_ID, INT.TOUCHPOINT1_TYPE, INT.TOUCHPOINT2_TYPE, MEDIA.MEDIA_ITEM_TYPE MEDIA_TYPE, MEDTYP.MEANING MEDIA_MEANING, ACT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, ACT.START_DATE_TIME ACT_START_DATE, ACT.END_DATE_TIME ACT_END_DATE, INT.START_DATE_TIME INT_START_DATE, INT.END_DATE_TIME INT_END_DATE, ACT.DESCRIPTION DESCRIPTION, INT.CREATED_BY CREATED_BY, INT.CREATION_DATE CREATION_DATE, INT.LAST_UPDATE_DATE LAST_UPDATE_DATE, INT.LAST_UPDATED_BY LAST_UPDATED_BY, AI.ACTION_ITEM_ID ACTION_ITEM_ID, AI.SHORT_DESCRIPTION ACTION_ITEM_MEANING, OTC.OUTCOME_ID OUTCOME_ID, OTC.SHORT_DESCRIPTION OUTCOME_MEANING , NOTE.NOTES NOTES_HEADER, NOTE.NOTES_DETAIL NOTES_DETAIL FROM JTF_IH_ACTIVITIES ACT, JTF_IH_INTERACTIONS INT, FND_LOOKUPS MEDTYP, JTF_RS_RESOURCE_EXTNS_VL RES, JTF_OBJECTS_VL CUSTTYP, HZ_PARTIES HZ, JTF_IH_MEDIA_ITEMS MEDIA, JTF_NOTES_VL NOTE, JTF_IH_ACTION_ITEMS_VL AI, JTF_IH_OUTCOMES_VL OTC WHERE ACT.INTERACTION_ID = INT.INTERACTION_ID AND ACT.MEDIA_ID = MEDIA.MEDIA_ID AND MEDTYP.LOOKUP_CODE = MEDIA.MEDIA_ITEM_TYPE AND MEDTYP.LOOKUP_TYPE = 'JTF_MEDIA_TYPE' AND ACT.DOC_REF = 'OKC_K_HEADERS_V' AND HZ.PARTY_ID = INT.PARTY_ID AND INT.TOUCHPOINT1_TYPE = 'PARTY' AND NOTE.SOURCE_OBJECT_ID = ACT.ACTIVITY_ID AND NOTE.SOURCE_OBJECT_CODE = 'JTF_ACTIVITY' AND AI.ACTION_ITEM_ID = ACT.ACTION_ITEM_ID AND OTC.OUTCOME_ID = ACT.OUTCOME_ID AND CUSTTYP.OBJECT_CODE = INT.TOUCHPOINT1_TYPE AND RES.RESOURCE_ID = INT.RESOURCE_ID UNION ALL SELECT INT.INTERACTION_ID INTERACTION_ID, ACT.DOC_ID CONTRACT_ID, CUSTTYP.NAME ROLE, ROL.SOURCE_NAME PARTY_NAME, INT.PARTY_ID PARTY_ID, RES.RESOURCE_NAME AGENT_NAME, INT.RESOURCE_ID AGENT_ID, INT.TOUCHPOINT1_TYPE, INT.TOUCHPOINT2_TYPE, MEDIA.MEDIA_ITEM_TYPE MEDIA_TYPE, MEDTYP.MEANING MEDIA_MEANING, ACT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, ACT.START_DATE_TIME ACT_START_DATE, ACT.END_DATE_TIME ACT_END_DATE, INT.START_DATE_TIME INT_START_DATE, INT.END_DATE_TIME INT_END_DATE, ACT.DESCRIPTION DESCRIPTION, INT.CREATED_BY CREATED_BY, INT.CREATION_DATE CREATION_DATE, INT.LAST_UPDATE_DATE LAST_UPDATE_DATE, INT.LAST_UPDATED_BY LAST_UPDATED_BY, AI.ACTION_ITEM_ID ACTION_ITEM_ID, AI.SHORT_DESCRIPTION ACTION_ITEM_MEANING, OTC.OUTCOME_ID OUTCOME_ID, OTC.SHORT_DESCRIPTION OUTCOME_MEANING , NOTE.NOTES NOTES_HEADER, NOTE.NOTES_DETAIL NOTES_DETAIL FROM JTF_IH_ACTIVITIES ACT, JTF_IH_INTERACTIONS INT, JTF_OBJECTS_VL CUSTTYP, FND_LOOKUPS MEDTYP, JTF_RS_RESOURCE_EXTNS_VL ROL, JTF_RS_RESOURCE_EXTNS_VL RES, JTF_IH_MEDIA_ITEMS MEDIA , JTF_NOTES_VL NOTE, JTF_IH_ACTION_ITEMS_VL AI, JTF_IH_OUTCOMES_VL OTC WHERE ROL.RESOURCE_ID = INT.PARTY_ID AND ROL.CATEGORY = INT.TOUCHPOINT1_TYPE AND ROL.CATEGORY <> 'PARTY' AND CUSTTYP.OBJECT_CODE = ROL.CATEGORY AND ACT.INTERACTION_ID = INT.INTERACTION_ID AND ACT.MEDIA_ID = MEDIA.MEDIA_ID AND MEDTYP.LOOKUP_CODE = MEDIA.MEDIA_ITEM_TYPE AND MEDTYP.LOOKUP_TYPE = 'JTF_MEDIA_TYPE' AND ACT.DOC_REF = 'OKC_K_HEADERS_V' AND NOTE.SOURCE_OBJECT_ID = ACT.ACTIVITY_ID AND NOTE.SOURCE_OBJECT_CODE = 'JTF_ACTIVITY' AND AI.ACTION_ITEM_ID = ACT.ACTION_ITEM_ID AND OTC.OUTCOME_ID = ACT.OUTCOME_ID AND RES.RESOURCE_ID = INT.RESOURCE_ID

View Text - HTML Formatted

SELECT INT.INTERACTION_ID INTERACTION_ID
, ACT.DOC_ID CONTRACT_ID
, CUSTTYP.NAME ROLE
, HZ.PARTY_NAME PARTY_NAME
, INT.PARTY_ID PARTY_ID
, RES.RESOURCE_NAME AGENT_NAME
, INT.RESOURCE_ID AGENT_ID
, INT.TOUCHPOINT1_TYPE
, INT.TOUCHPOINT2_TYPE
, MEDIA.MEDIA_ITEM_TYPE MEDIA_TYPE
, MEDTYP.MEANING MEDIA_MEANING
, ACT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACT.START_DATE_TIME ACT_START_DATE
, ACT.END_DATE_TIME ACT_END_DATE
, INT.START_DATE_TIME INT_START_DATE
, INT.END_DATE_TIME INT_END_DATE
, ACT.DESCRIPTION DESCRIPTION
, INT.CREATED_BY CREATED_BY
, INT.CREATION_DATE CREATION_DATE
, INT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, INT.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.ACTION_ITEM_ID ACTION_ITEM_ID
, AI.SHORT_DESCRIPTION ACTION_ITEM_MEANING
, OTC.OUTCOME_ID OUTCOME_ID
, OTC.SHORT_DESCRIPTION OUTCOME_MEANING
, NOTE.NOTES NOTES_HEADER
, NOTE.NOTES_DETAIL NOTES_DETAIL
FROM JTF_IH_ACTIVITIES ACT
, JTF_IH_INTERACTIONS INT
, FND_LOOKUPS MEDTYP
, JTF_RS_RESOURCE_EXTNS_VL RES
, JTF_OBJECTS_VL CUSTTYP
, HZ_PARTIES HZ
, JTF_IH_MEDIA_ITEMS MEDIA
, JTF_NOTES_VL NOTE
, JTF_IH_ACTION_ITEMS_VL AI
, JTF_IH_OUTCOMES_VL OTC
WHERE ACT.INTERACTION_ID = INT.INTERACTION_ID
AND ACT.MEDIA_ID = MEDIA.MEDIA_ID
AND MEDTYP.LOOKUP_CODE = MEDIA.MEDIA_ITEM_TYPE
AND MEDTYP.LOOKUP_TYPE = 'JTF_MEDIA_TYPE'
AND ACT.DOC_REF = 'OKC_K_HEADERS_V'
AND HZ.PARTY_ID = INT.PARTY_ID
AND INT.TOUCHPOINT1_TYPE = 'PARTY'
AND NOTE.SOURCE_OBJECT_ID = ACT.ACTIVITY_ID
AND NOTE.SOURCE_OBJECT_CODE = 'JTF_ACTIVITY'
AND AI.ACTION_ITEM_ID = ACT.ACTION_ITEM_ID
AND OTC.OUTCOME_ID = ACT.OUTCOME_ID
AND CUSTTYP.OBJECT_CODE = INT.TOUCHPOINT1_TYPE
AND RES.RESOURCE_ID = INT.RESOURCE_ID UNION ALL SELECT INT.INTERACTION_ID INTERACTION_ID
, ACT.DOC_ID CONTRACT_ID
, CUSTTYP.NAME ROLE
, ROL.SOURCE_NAME PARTY_NAME
, INT.PARTY_ID PARTY_ID
, RES.RESOURCE_NAME AGENT_NAME
, INT.RESOURCE_ID AGENT_ID
, INT.TOUCHPOINT1_TYPE
, INT.TOUCHPOINT2_TYPE
, MEDIA.MEDIA_ITEM_TYPE MEDIA_TYPE
, MEDTYP.MEANING MEDIA_MEANING
, ACT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, ACT.START_DATE_TIME ACT_START_DATE
, ACT.END_DATE_TIME ACT_END_DATE
, INT.START_DATE_TIME INT_START_DATE
, INT.END_DATE_TIME INT_END_DATE
, ACT.DESCRIPTION DESCRIPTION
, INT.CREATED_BY CREATED_BY
, INT.CREATION_DATE CREATION_DATE
, INT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, INT.LAST_UPDATED_BY LAST_UPDATED_BY
, AI.ACTION_ITEM_ID ACTION_ITEM_ID
, AI.SHORT_DESCRIPTION ACTION_ITEM_MEANING
, OTC.OUTCOME_ID OUTCOME_ID
, OTC.SHORT_DESCRIPTION OUTCOME_MEANING
, NOTE.NOTES NOTES_HEADER
, NOTE.NOTES_DETAIL NOTES_DETAIL
FROM JTF_IH_ACTIVITIES ACT
, JTF_IH_INTERACTIONS INT
, JTF_OBJECTS_VL CUSTTYP
, FND_LOOKUPS MEDTYP
, JTF_RS_RESOURCE_EXTNS_VL ROL
, JTF_RS_RESOURCE_EXTNS_VL RES
, JTF_IH_MEDIA_ITEMS MEDIA
, JTF_NOTES_VL NOTE
, JTF_IH_ACTION_ITEMS_VL AI
, JTF_IH_OUTCOMES_VL OTC
WHERE ROL.RESOURCE_ID = INT.PARTY_ID
AND ROL.CATEGORY = INT.TOUCHPOINT1_TYPE
AND ROL.CATEGORY <> 'PARTY'
AND CUSTTYP.OBJECT_CODE = ROL.CATEGORY
AND ACT.INTERACTION_ID = INT.INTERACTION_ID
AND ACT.MEDIA_ID = MEDIA.MEDIA_ID
AND MEDTYP.LOOKUP_CODE = MEDIA.MEDIA_ITEM_TYPE
AND MEDTYP.LOOKUP_TYPE = 'JTF_MEDIA_TYPE'
AND ACT.DOC_REF = 'OKC_K_HEADERS_V'
AND NOTE.SOURCE_OBJECT_ID = ACT.ACTIVITY_ID
AND NOTE.SOURCE_OBJECT_CODE = 'JTF_ACTIVITY'
AND AI.ACTION_ITEM_ID = ACT.ACTION_ITEM_ID
AND OTC.OUTCOME_ID = ACT.OUTCOME_ID
AND RES.RESOURCE_ID = INT.RESOURCE_ID