VIEW: APPS.JTF_DSP_FEATURED_MEDIA_V

Source

View Text - Preformatted

SELECT 'P', d.object_id, e.access_name, f.access_name, c.mini_site_id from jtf_dsp_section_items a, jtf_dsp_sections_b b, jtf_dsp_msite_sct_items c, jtf_dsp_obj_lgl_ctnt d, jtf_dsp_context_b e, jtf_amv_items_vl f where d.item_id = f.item_id and d.item_id is NOT NULL and d.object_type = 'I' and e.context_type_code = 'MEDIA' and d.context_id = e.context_id and d.object_id = a.inventory_item_id and a.section_item_id = c.section_item_id and a.section_id = b.section_id and b.section_type_code = 'F' UNION ALL select distinct 'C', d.object_id, e.access_name, h.access_name, c.mini_site_id from jtf_dsp_section_items a, jtf_dsp_sections_b b, jtf_dsp_msite_sct_items c, jtf_dsp_obj_lgl_ctnt d, jtf_dsp_context_b e, mtl_item_categories f, jtf_amv_items_vl h where d.item_id is NOT NULL and d.item_id = h.item_id and d.object_type = 'C' and d.context_id = e.context_id and e.context_type_code = 'MEDIA' and d.object_id = f.category_id and f.category_set_id = fnd_profile.value('IBE_CATEGORY_SET') and f.inventory_item_id = a.inventory_item_id and a.section_item_id = c.section_item_id and a.section_id = b.section_id and b.section_type_code = 'F' UNION ALL select distinct 'S', a.object_id, b.access_name, c.access_name, e.mini_site_id from jtf_dsp_obj_lgl_ctnt a, jtf_dsp_context_b b, jtf_amv_items_vl c, jtf_dsp_section_items d, jtf_dsp_msite_sct_items e where a.item_id = c.item_id and a.context_id = b.context_id and b.context_type_code = 'MEDIA' and a.item_id is NOT NULL and a.object_type = 'S'and a.object_id = d.section_id and e.section_item_id = d.section_item_id

View Text - HTML Formatted

SELECT 'P'
, D.OBJECT_ID
, E.ACCESS_NAME
, F.ACCESS_NAME
, C.MINI_SITE_ID
FROM JTF_DSP_SECTION_ITEMS A
, JTF_DSP_SECTIONS_B B
, JTF_DSP_MSITE_SCT_ITEMS C
, JTF_DSP_OBJ_LGL_CTNT D
, JTF_DSP_CONTEXT_B E
, JTF_AMV_ITEMS_VL F
WHERE D.ITEM_ID = F.ITEM_ID
AND D.ITEM_ID IS NOT NULL
AND D.OBJECT_TYPE = 'I'
AND E.CONTEXT_TYPE_CODE = 'MEDIA'
AND D.CONTEXT_ID = E.CONTEXT_ID
AND D.OBJECT_ID = A.INVENTORY_ITEM_ID
AND A.SECTION_ITEM_ID = C.SECTION_ITEM_ID
AND A.SECTION_ID = B.SECTION_ID
AND B.SECTION_TYPE_CODE = 'F' UNION ALL SELECT DISTINCT 'C'
, D.OBJECT_ID
, E.ACCESS_NAME
, H.ACCESS_NAME
, C.MINI_SITE_ID
FROM JTF_DSP_SECTION_ITEMS A
, JTF_DSP_SECTIONS_B B
, JTF_DSP_MSITE_SCT_ITEMS C
, JTF_DSP_OBJ_LGL_CTNT D
, JTF_DSP_CONTEXT_B E
, MTL_ITEM_CATEGORIES F
, JTF_AMV_ITEMS_VL H
WHERE D.ITEM_ID IS NOT NULL
AND D.ITEM_ID = H.ITEM_ID
AND D.OBJECT_TYPE = 'C'
AND D.CONTEXT_ID = E.CONTEXT_ID
AND E.CONTEXT_TYPE_CODE = 'MEDIA'
AND D.OBJECT_ID = F.CATEGORY_ID
AND F.CATEGORY_SET_ID = FND_PROFILE.VALUE('IBE_CATEGORY_SET')
AND F.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
AND A.SECTION_ITEM_ID = C.SECTION_ITEM_ID
AND A.SECTION_ID = B.SECTION_ID
AND B.SECTION_TYPE_CODE = 'F' UNION ALL SELECT DISTINCT 'S'
, A.OBJECT_ID
, B.ACCESS_NAME
, C.ACCESS_NAME
, E.MINI_SITE_ID
FROM JTF_DSP_OBJ_LGL_CTNT A
, JTF_DSP_CONTEXT_B B
, JTF_AMV_ITEMS_VL C
, JTF_DSP_SECTION_ITEMS D
, JTF_DSP_MSITE_SCT_ITEMS E
WHERE A.ITEM_ID = C.ITEM_ID
AND A.CONTEXT_ID = B.CONTEXT_ID
AND B.CONTEXT_TYPE_CODE = 'MEDIA'
AND A.ITEM_ID IS NOT NULL
AND A.OBJECT_TYPE = 'S'AND A.OBJECT_ID = D.SECTION_ID
AND E.SECTION_ITEM_ID = D.SECTION_ITEM_ID