View: OKC_LAUNCH_COMMGRID_V

Product:	OKC - Contracts Core
Description:
Implementation/DBA Data:	APPS.OKC_LAUNCH_COMMGRID_V

View Text

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

Columns