APPS.IBE_DSP_HIERARCHY_QUERY_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 52

    SELECT JMB.msite_id
      INTO l_master_msite_id
      FROM ibe_msites_b JMB
      WHERE JMB.master_msite_flag = 'Y' and
	        JMB.site_type = 'I';

Line: 61

      'SELECT JDMSS.parent_section_id ' ||
      'FROM ibe_dsp_msite_sct_sects JDMSS ' ||
      'START WITH JDMSS.child_section_id = :section_id ' ||
      'AND JDMSS.mini_site_id     = :master_mini_site_id1 ' ||
      'CONNECT BY JDMSS.child_section_id = PRIOR JDMSS.parent_section_id ' ||
      'AND JDMSS.mini_site_id     = :master_mini_site_id2 ' ||
      'AND JDMSS.parent_section_id IS NOT NULL'
      USING p_object_id, l_master_msite_id, l_master_msite_id;

Line: 75

      SELECT JDSV.display_name
        INTO l_section_disp_name
        FROM ibe_dsp_sections_vl JDSV
        WHERE JDSV.section_id = l_section_id;

Line: 86

    SELECT JDSV.display_name
      INTO l_section_disp_name
      FROM ibe_dsp_sections_vl JDSV
      WHERE JDSV.section_id = p_object_id;

Line: 133

  OPEN x_section_csr FOR SELECT display_name FROM ibe_dsp_sections_vl
    WHERE section_id = p_section_id;

Line: 145

  OPEN x_sectionitem_item_csr FOR SELECT MTL.inventory_item_id,
    MTL.organization_id, MTL.concatenated_segments, MTL.description,
    MTL.web_status, L.meaning, SI.section_item_id, SI.object_version_number,
    SI.start_date_active, SI.end_date_active, SI.sort_order
    FROM ibe_dsp_section_items SI, mtl_system_items_vl MTL, fnd_lookups L
    WHERE SI.section_id = p_section_id
    AND   SI.inventory_item_id = MTL.inventory_item_id
    AND   SI.organization_id = MTL.organization_id
    AND   L.lookup_type = 'IBE_ITEM_STATUS'
    AND   L.lookup_code = MTL.web_status
    AND   SI.organization_id = l_organization_id /*bug 4864940 */
    order by SI.sort_order;

Line: 227

  OPEN x_sectionitem_section_csr FOR SELECT S.section_id, S.display_name,
    SI.section_item_id, SI.object_version_number, SI.start_date_active,
    SI.end_date_active, SI.sort_order
    FROM ibe_dsp_sections_vl S, ibe_dsp_section_items SI
    WHERE SI.inventory_item_id = p_inventory_item_id
    AND   SI.organization_id = p_organization_id
    AND   SI.section_id = S.section_id;

Line: 313

  OPEN x_section_csr FOR SELECT display_name FROM ibe_dsp_sections_vl
    WHERE section_id = p_section_id;

Line: 318

  OPEN x_section_section_csr FOR SELECT S.section_id, S.access_name,
    S.display_name, S.section_type_code, L1.meaning, S.status_code, L2.meaning,
    MSS.mini_site_section_section_id, MSS.object_version_number,
    MSS.start_date_active, MSS.end_date_active, MSS.sort_order
    FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS, fnd_lookups L1,
    fnd_lookups L2
    WHERE S.section_id IN
    (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
     WHERE parent_section_id = p_section_id
     AND mini_site_id = l_master_mini_site_id)
    AND   MSS.child_section_id = S.section_id
    AND   MSS.mini_site_id = l_master_mini_site_id
    AND   L1.lookup_type = 'IBE_SECTION_TYPE'
    AND   L1.lookup_code = S.section_type_code
    AND   L2.lookup_type = 'IBE_SECTION_STATUS'
    AND   L2.lookup_code = S.status_code
    --  ORDER BY S.display_name;

Line: 339

  OPEN x_section_section_csr FOR SELECT S.section_id, S.access_name,
    S.display_name, S.section_type_code, L1.meaning, S.status_code, L2.meaning,
    MSS.mini_site_section_section_id, MSS.object_version_number,
    MSS.start_date_active, MSS.end_date_active, MSS.sort_order
    FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS, fnd_lookups L1,
    fnd_lookups L2
    WHERE MSS.parent_section_id = p_section_id
    AND   MSS.child_section_id = S.section_id
    AND   MSS.mini_site_id = l_master_mini_site_id
    AND   L1.lookup_type = 'IBE_SECTION_TYPE'
    AND   L1.lookup_code = S.section_type_code
    AND   L2.lookup_type = 'IBE_SECTION_STATUS'
    AND   L2.lookup_code = S.status_code
        ---Added by amaheshw on 10.Jan.2008 bug 6712124. check end date also
    AND sysdate BETWEEN S.start_date_active AND NVL(S.end_date_active,sysdate)
   ORDER BY MSS.sort_order;

Line: 427

    SELECT rowid FROM ibe_dsp_msite_sct_sects
      WHERE mini_site_id = l_c_master_mini_site_id
      AND parent_section_id = l_c_section_id;

Line: 586

  OPEN x_section_csr FOR SELECT section_id, object_version_number,
    display_name, access_name, section_type_code, status_code,
    start_date_active, end_date_active, description, long_description,
    keywords, deliverable_id, display_context_id, auto_placement_rule,
    order_by_clause
    FROM ibe_dsp_sections_vl
    WHERE section_id = p_section_id;

Line: 603

  OPEN x_section_type_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
    WHERE lookup_type = 'IBE_SECTION_TYPE';

Line: 607

  OPEN x_section_status_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
    WHERE lookup_type = 'IBE_SECTION_STATUS';

Line: 612

  OPEN x_deliverable_csr FOR SELECT item_id, access_name, item_name
    FROM jtf_amv_items_vl
    WHERE deliverable_type_code = 'TEMPLATE'
    AND applicable_to_code LIKE '%SECTION'
    AND application_id= 671;

Line: 619

  OPEN x_display_context_csr FOR SELECT context_id, access_name, name
    FROM ibe_dsp_context_vl
    WHERE context_type_code = 'TEMPLATE';

Line: 792

  IS SELECT parent_section_id FROM ibe_dsp_msite_sct_sects
    WHERE mini_site_id = l_c_master_mini_site_id
    AND child_section_id = l_c_section_id;

Line: 837

  OPEN x_section_csr FOR SELECT section_id, object_version_number,
    display_name, available_in_all_sites_flag
    FROM ibe_dsp_sections_vl
    WHERE section_id = p_section_id;

Line: 853

  OPEN x_incl_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
    FROM ibe_msites_vl
    WHERE EXISTS
    (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
    WHERE mini_site_id = msite_id
    AND mini_site_id <> l_master_mini_site_id
    AND child_section_id = p_section_id)
    ORDER BY msite_name;

Line: 871

    OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name,
      msite_description
      FROM ibe_msites_vl
      WHERE msite_id <> l_master_mini_site_id AND
      (msite_root_section_id = p_section_id OR
      EXISTS (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
      WHERE mini_site_id = msite_id
      AND mini_site_id <> l_master_mini_site_id
      AND child_section_id = l_parent_section_id))
      ORDER BY msite_name;

Line: 887

    OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name,
      msite_description
      FROM ibe_msites_vl
      WHERE msite_id <> l_master_mini_site_id
	--bug 3410883
      AND (msite_root_section_id = l_master_root_section_id or msite_root_section_id is null)
      AND site_type = 'I'
      ORDER BY msite_name;

Line: 975

  OPEN x_inventory_item_csr FOR SELECT inventory_item_id, organization_id,
    web_status, description, long_description, concatenated_segments,
    creation_date, last_updated_by, last_update_login, last_update_date
    FROM mtl_system_items_vl
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id = p_organization_id;

Line: 983

  OPEN x_web_status_type_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
    WHERE lookup_type = 'IBE_ITEM_STATUS';

Line: 1079

  OPEN x_inventory_item_csr FOR SELECT MTL.inventory_item_id,
    MTL.organization_id, MTL.web_status, L.meaning, MTL.description,
    MTL.concatenated_segments, MTL.creation_date, MTL.last_updated_by,
    MTL.last_update_login, MTL.last_update_date
    FROM mtl_system_items_vl MTL, fnd_lookups L
    WHERE MTL.inventory_item_id = p_inventory_item_id
    AND MTL.organization_id = p_organization_id
    AND L.lookup_type = 'IBE_ITEM_STATUS'
    AND L.lookup_code = MTL.web_status;

Line: 1092

  OPEN x_incl_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
    FROM ibe_msites_vl
    WHERE EXISTS
    (SELECT mini_site_id FROM ibe_dsp_msite_sct_items
    WHERE mini_site_id = msite_id
    AND section_item_id IN
    (SELECT section_item_id FROM ibe_dsp_section_items
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id = p_organization_id))
    ORDER BY msite_name;

Line: 1106

  OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
    FROM ibe_msites_vl
    WHERE EXISTS
    (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
    WHERE mini_site_id = msite_id
    AND mini_site_id <> l_master_mini_site_id
    AND child_section_id IN
    (SELECT section_id FROM ibe_dsp_section_items
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id = p_organization_id))
    ORDER BY msite_name;

Line: 1203

  select display_name,description
  from ibe_dsp_sections_vl where section_id=p_section_id;

Line: 1230

  SELECT INSTR(x_section_path,'/', -1, 1) into l_position FROM DUAL;

Line: 1233

 SELECT SUBSTR(x_section_path,1,l_position) into x_section_path from dual;

Line: 1288

   /*SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, fl.meaning,s.status_code,  s.start_date_active,
     child_sct.parent_section_id subsections,child_item.section_id products,
     s.section_type_code, fl1.meaning status_name,s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,fnd_lookups fl,
     fnd_lookups fl1, ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
     WHERE S.section_id = MSS.child_section_id  AND fl.lookup_code=s.section_type_code
     and  fl.lookup_type= 'IBE_SECTION_TYPE' AND fl1.lookup_code=s.status_code
     AND  fl1.lookup_type= 'IBE_SECTION_STATUS' AND fl.enabled_flag = fl1.enabled_flag
     AND MSS.mini_site_id = p_msite_id   AND s.section_id = child_sct.parent_section_id (+)
     AND child_sct.mini_site_id (+)  = p_msite_id
     AND s.section_id = child_item.section_id (+)
     AND mss.level_number <= p_level_number
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;

Line: 1303

     SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
     child_sct.parent_section_id subsections,child_item.section_id products,
     s.section_type_code, s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,
     ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
     WHERE S.section_id = MSS.child_section_id
     AND MSS.mini_site_id = p_msite_id  AND s.section_id = child_sct.parent_section_id (+)
     AND child_sct.mini_site_id (+)  = p_msite_id
     AND s.section_id = child_item.section_id (+)
     AND mss.level_number <= p_level_number
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;

Line: 1316

    SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
     s.section_type_code, s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
     WHERE S.section_id = MSS.child_section_id
     AND MSS.mini_site_id = p_msite_id
     AND mss.level_number <= p_level_number
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;

Line: 1328

 /*  SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, fl.meaning,s.status_code,  s.start_date_active,
     child_sct.parent_section_id subsections,child_item.section_id products,
     s.section_type_code, fl1.meaning status_name,s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,fnd_lookups fl,
     fnd_lookups fl1, ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
     WHERE S.section_id = MSS.child_section_id  AND fl.lookup_code=s.section_type_code
     and  fl.lookup_type= 'IBE_SECTION_TYPE' AND fl1.lookup_code=s.status_code
     AND  fl1.lookup_type= 'IBE_SECTION_STATUS' AND fl.enabled_flag = fl1.enabled_flag
     AND MSS.mini_site_id = p_msite_id   AND s.section_id = child_sct.parent_section_id (+)
     AND child_sct.mini_site_id (+)  = p_msite_id
     AND s.section_id = child_item.section_id (+)
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;

Line: 1342

     SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
     child_sct.parent_section_id subsections,child_item.section_id products,
     s.section_type_code, s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,
     ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
     WHERE S.section_id = MSS.child_section_id
     AND MSS.mini_site_id = p_msite_id  AND s.section_id = child_sct.parent_section_id (+)
     AND child_sct.mini_site_id (+)  = p_msite_id
     AND s.section_id = child_item.section_id (+)
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;

Line: 1354

   SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
     s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
     s.section_type_code, s.access_name,MSS.level_number
     FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
     WHERE S.section_id = MSS.child_section_id
     AND MSS.mini_site_id = p_msite_id
     ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;