--
-- $Header: whdev/2.0/owb/oracle/wh/repos/pdl/foundation/plsql/OWBDeletion.pkb /main/1 2009/04/27 17:07:34 rvelisar Exp $
--
-- OWBDeletion.pkb
--
-- Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
--
--   NAME
--     OWBDeletion.pkb - <one-line expansion of the name>
--
--   DESCRIPTION
--     <short description of component this file declares/defines>
--
--   RETURNS
--
--   NOTES
--     <other useful comments, qualifications, etc.>
--
--   MODIFIED   (MM/DD/YY)
--   rvelisar    04/07/09 - Creation
--

CREATE OR REPLACE PACKAGE BODY OWBDeletion AS

-----------------------------------------------------------------------------
-- Public constants
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
-- Procedures and functions
-----------------------------------------------------------------------------

-- Procedure 

PROCEDURE DELETE_IDS (list_of_ids IN OWB_NUMBER_TABLE_TYPE)
IS
BEGIN
  delete /*+ INDEX (CMPFCOClasses idx_FCOelementid) */ from CMPFCOClasses 
    where workspaceid = (select sys_context('owb_workspace','workspaceID') from dual)
    and i_1 in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOClasses idx_SCOelementid) */ from CMPSCOClasses
    where workspaceid = (select sys_context('owb_workspace','workspaceID') from dual)
    and i_1 in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOCfgElement_V idx_SCOCfgelementid) */ from CMPSCOCfgElement_V 
    where elementid in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOMapElement_V idx_SCOMapelementid) */ from CMPSCOMapElement_V 
    where elementid in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOPrpElement_V idx_SCOPrpelementid) */ from CMPSCOPrpElement_V 
    where elementid in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSYSElement_V idx_SYSelementid) */ from CMPSYSElement_V 
    where elementid in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPTranslation_V idx_SCOtranslatable) */ from CMPTranslation_V 
    where mlstranslatable in (select * from table(list_of_ids) where rownum > 0);
END DELETE_IDS;


PROCEDURE DELETE_FCOS_SCOS (list_of_ids IN OWB_NUMBER_TABLE_TYPE)
IS
BEGIN
  delete /*+ INDEX_SS (CMPSCOElement_V IDX_SCOFCOClass) */ from CMPSCOElement_V 
    where firstclassobject in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOCfgElement_V IDX_SCOCfgFCOClass) */ from CMPSCOCfgElement_V 
    where firstclassobject in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOMapElement_V IDX_SCOMapFCOClass) */ from CMPSCOMapElement_V 
    where firstclassobject in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPSCOPrpElement_V IDX_SCOPrpFCOClass) */ from CMPSCOPrpElement_V 
    where firstclassobject in (select * from table(list_of_ids) where rownum > 0);
  delete /*+ INDEX (CMPFCOClasses idx_FCOelementid) */ from CMPFCOClasses
    where workspaceid = (select sys_context('owb_workspace','workspaceID') from dual)
    and i_1 in (select * from table(list_of_ids) where rownum > 0);
END DELETE_FCOS_SCOS;


END OWBDeletion;