VIEW: APPS.JTF_IH_ACTIVITIES_VL2

Source

View Text - Preformatted

SELECT act.rowid row_id, act.activity_id, act.interaction_id, int.party_id, hz.party_name, act.cust_account_id, hzca.account_number, int.resource_id, rsc.resource_name, act.ACTION_ITEM_ID, aci.ACTION_ITEM, aci.short_description aci_desc, act.ACTION_ID, actn.ACTION, actn.short_description actn_desc, act.doc_id, act.doc_ref, objtl.name BUSINESS_OBJECT, act.media_id, media.direction, media.media_data, media.media_item_ref, media.media_item_type, media.source_item_id media_source_item_id, fndlkup.description INTERACTION_TYPE, decode(media.direction || '_' || media.media_item_type,'_', 'ACTIVITY', media.direction || '_' || media.media_item_type) INTERACTION_TYPE_CODE, act.active, act.start_date_time, act.end_date_time, act.duration, act.outcome_id, out.outcome_code, out.short_description out_desc, act.result_id, rsl.result_code, rsl.short_description rsl_desc, act.reason_id, rea.reason_code, rea.short_description rea_desc, act.SOURCE_CODE, act.SOURCE_CODE_ID, act.OBJECT_TYPE, act.OBJECT_ID, act.CREATION_DATE, act.CREATED_BY, act.LAST_UPDATE_DATE, act.LAST_UPDATED_BY, act.LAST_UPDATE_LOGIN, act.object_version_number, fndapp.application_name, ACT.BULK_WRITER_CODE, ACT.BULK_BATCH_TYPE, ACT.BULK_BATCH_ID, ACT.BULK_INTERACTION_ID FROM jtf_ih_activities act, jtf_ih_interactions int, hz_parties hz, jtf_ih_media_items media, jtf_ih_outcomes_tl out, jtf_ih_results_tl rsl, jtf_ih_reasons_tl rea, jtf_ih_action_items_tl aci, jtf_ih_actions_tl actn, jtf_rs_resource_extns_tl rsc, hz_cust_accounts hzca, jtf_objects_tl objtl, fnd_lookups fndlkup, fnd_application_tl fndapp WHERE act.interaction_id = int.interaction_id AND int.party_id = hz.party_id AND act.cust_account_id = hzca.cust_account_id(+) AND int.resource_id = rsc.resource_id AND int.handler_id = fndapp.application_id AND fndapp.language(+) = userenv('LANG') AND act.media_id = media.media_id(+) AND act.outcome_id = out.outcome_id(+) AND out.language(+) = userenv('LANG') AND act.result_id = rsl.result_id(+) AND rsl.language(+) = userenv('LANG') AND act.reason_id = rea.reason_id(+) AND rea.language(+) = userenv('LANG') AND act.action_item_id = aci.action_item_id(+) AND aci.language(+) = userenv('LANG') AND act.action_id = actn.action_id(+) AND actn.language(+) = userenv('LANG') AND act.doc_ref = objtl.object_code(+) AND objtl.language(+) = userenv('LANG') AND (fndlkup.lookup_type(+) = 'JTF_IH_OBJECT_TYPE' AND decode(media.direction || '_' || media.media_item_type,'_','ACTIVITY', media.direction || '_' || media.media_item_type) = fndlkup.lookup_code(+))

View Text - HTML Formatted

SELECT ACT.ROWID ROW_ID
, ACT.ACTIVITY_ID
, ACT.INTERACTION_ID
, INT.PARTY_ID
, HZ.PARTY_NAME
, ACT.CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER
, INT.RESOURCE_ID
, RSC.RESOURCE_NAME
, ACT.ACTION_ITEM_ID
, ACI.ACTION_ITEM
, ACI.SHORT_DESCRIPTION ACI_DESC
, ACT.ACTION_ID
, ACTN.ACTION
, ACTN.SHORT_DESCRIPTION ACTN_DESC
, ACT.DOC_ID
, ACT.DOC_REF
, OBJTL.NAME BUSINESS_OBJECT
, ACT.MEDIA_ID
, MEDIA.DIRECTION
, MEDIA.MEDIA_DATA
, MEDIA.MEDIA_ITEM_REF
, MEDIA.MEDIA_ITEM_TYPE
, MEDIA.SOURCE_ITEM_ID MEDIA_SOURCE_ITEM_ID
, FNDLKUP.DESCRIPTION INTERACTION_TYPE
, DECODE(MEDIA.DIRECTION || '_' || MEDIA.MEDIA_ITEM_TYPE
, '_'
, 'ACTIVITY'
, MEDIA.DIRECTION || '_' || MEDIA.MEDIA_ITEM_TYPE) INTERACTION_TYPE_CODE
, ACT.ACTIVE
, ACT.START_DATE_TIME
, ACT.END_DATE_TIME
, ACT.DURATION
, ACT.OUTCOME_ID
, OUT.OUTCOME_CODE
, OUT.SHORT_DESCRIPTION OUT_DESC
, ACT.RESULT_ID
, RSL.RESULT_CODE
, RSL.SHORT_DESCRIPTION RSL_DESC
, ACT.REASON_ID
, REA.REASON_CODE
, REA.SHORT_DESCRIPTION REA_DESC
, ACT.SOURCE_CODE
, ACT.SOURCE_CODE_ID
, ACT.OBJECT_TYPE
, ACT.OBJECT_ID
, ACT.CREATION_DATE
, ACT.CREATED_BY
, ACT.LAST_UPDATE_DATE
, ACT.LAST_UPDATED_BY
, ACT.LAST_UPDATE_LOGIN
, ACT.OBJECT_VERSION_NUMBER
, FNDAPP.APPLICATION_NAME
, ACT.BULK_WRITER_CODE
, ACT.BULK_BATCH_TYPE
, ACT.BULK_BATCH_ID
, ACT.BULK_INTERACTION_ID
FROM JTF_IH_ACTIVITIES ACT
, JTF_IH_INTERACTIONS INT
, HZ_PARTIES HZ
, JTF_IH_MEDIA_ITEMS MEDIA
, JTF_IH_OUTCOMES_TL OUT
, JTF_IH_RESULTS_TL RSL
, JTF_IH_REASONS_TL REA
, JTF_IH_ACTION_ITEMS_TL ACI
, JTF_IH_ACTIONS_TL ACTN
, JTF_RS_RESOURCE_EXTNS_TL RSC
, HZ_CUST_ACCOUNTS HZCA
, JTF_OBJECTS_TL OBJTL
, FND_LOOKUPS FNDLKUP
, FND_APPLICATION_TL FNDAPP
WHERE ACT.INTERACTION_ID = INT.INTERACTION_ID
AND INT.PARTY_ID = HZ.PARTY_ID
AND ACT.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID(+)
AND INT.RESOURCE_ID = RSC.RESOURCE_ID
AND INT.HANDLER_ID = FNDAPP.APPLICATION_ID
AND FNDAPP.LANGUAGE(+) = USERENV('LANG')
AND ACT.MEDIA_ID = MEDIA.MEDIA_ID(+)
AND ACT.OUTCOME_ID = OUT.OUTCOME_ID(+)
AND OUT.LANGUAGE(+) = USERENV('LANG')
AND ACT.RESULT_ID = RSL.RESULT_ID(+)
AND RSL.LANGUAGE(+) = USERENV('LANG')
AND ACT.REASON_ID = REA.REASON_ID(+)
AND REA.LANGUAGE(+) = USERENV('LANG')
AND ACT.ACTION_ITEM_ID = ACI.ACTION_ITEM_ID(+)
AND ACI.LANGUAGE(+) = USERENV('LANG')
AND ACT.ACTION_ID = ACTN.ACTION_ID(+)
AND ACTN.LANGUAGE(+) = USERENV('LANG')
AND ACT.DOC_REF = OBJTL.OBJECT_CODE(+)
AND OBJTL.LANGUAGE(+) = USERENV('LANG')
AND (FNDLKUP.LOOKUP_TYPE(+) = 'JTF_IH_OBJECT_TYPE'
AND DECODE(MEDIA.DIRECTION || '_' || MEDIA.MEDIA_ITEM_TYPE
, '_'
, 'ACTIVITY'
, MEDIA.DIRECTION || '_' || MEDIA.MEDIA_ITEM_TYPE) = FNDLKUP.LOOKUP_CODE(+))