CREATE OR REPLACE PROCEDURE STG.FP_REMOVE_MST_OLD_DATA (EXITCODE OUT NUMBER)

IS

   /***********************************************************************

          **  存储过程名称:  FP_REMOVE_OLD_DATA

         **  存储过程创建日期:

         **  存储过程创建人:

         **  目的:  删除FP上次在ODS的输出数据

         **  输入参数:

         **  输出参数:EXITCODE 预留接口给外部调用者，让其知道存储过程的执行结果，0.预定逻辑执行完成; 1.遇到异常，预定逻辑未执行完成

         **  返回值:

         **  用法:

         **

         ** 修订版本:

         **  版本号     修订时间    修订人        描述

         **  ---------  ----------  ------------  ------------------------

         **    1.0

         **

    **********************************************************************/

   ----------------------- 以下定义存储过程使用的变量---------------------------------

   V_PKGName          VARCHAR2 (40) := 'PKG_FP';

   V_ProName          VARCHAR2 (40) := 'FP_REMOVE_MST_OLD_DATA';

   V_Action           VARCHAR2 (1);

   V_Step             NUMBER := 0;

   V_MainTable        VARCHAR2 (40);

   E_ErrMessage       VARCHAR2 (3000);

----------------------- 以下是存储过程的主体------------------------------------------

BEGIN

   EXITCODE := 0;

   DELETE FROM STG.DISABLED_R_CONSTRAINT;

   INSERT INTO STG.DISABLED_R_CONSTRAINT SELECT  CONSTRAINT_NAME from all_constraints where constraint_type='R' and status='DISABLED' AND OWNER='ABPPMGR';

   COMMIT;

   V_Step := V_Step + 1;

   V_Action := PKG_CONSTANT.CONS_ACTION_DELETE;

   V_MainTable := 'ALL';

   PKG_UTIL.CREATELOG (

      V_PKGName,                                       --Put package name here

      V_ProName,                                     --Put procedure name here

      V_Step,                                       --Step seqence No. if have

      V_Action,                                       --Action 'I','D','U','T'

      PKG_CONSTANT.CONS_MSG_INFO,     --Message tppe, 'SUCCESS' or 'EXCEPTION'

      'start to delete old data.', --Any generic information here

      V_MainTable);                               --Major table name processed

   FOR c_cons_dis

      IN (SELECT    'alter table '

                 || T2.owner

                 || '.'

                 || T2.table_name

                 || ' disable constraint '

                 || t2.CONSTRAINT_NAME

                    sqltext

           FROM all_constraints T2

           WHERE     t2.owner = 'ABPPMGR'

                 AND T2.CONSTRAINT_TYPE = 'R'

                 AND  T2.table_name IN

              (select phy_mst from abppmgr.SYS_TABLE_MAP))

   LOOP

      EXECUTE IMMEDIATE c_cons_dis.sqltext;

   END LOOP;

   FOR c_trunc

      IN (SELECT 'truncate table ABPPMGR.' || table_name || ' drop storage' sqltext

  FROM all_tables

 WHERE owner = 'ABPPMGR'

       AND table_name IN

              ('MST_BOMCOMPONENTS',

'MST_ROUTINGOPERATION',

'MST_ROUTINGHEADER',

'MST_BOMHEADER',

'MST_BOMCOMPONENTS_P',

'MST_ITEMMASTER',

'MST_ITEMSITEMASTER',

'MST_ITEMBOMROUTING_P',

'MST_ITEMGROUPMASTER',

'MST_OPRESOURCE',

'MST_ITEMSITEIPPARM',

'MST_APPRSUPITEM',

'MST_UOMCONVERSION',

'MST_ITEMGROUPDETAIL',

'MST_APPRSUPITEM_P',

'MST_ITEMBOMROUTING',

'MST_SUPPLIERCALENDAR',

'PURCHORDLINE',

'INVENTORY',

'INVENTORYPROPERTY',

'SALESORDERLINE',

'SALESORDERLINE_P',

'FORECASTDETAIL'))

   LOOP

      EXECUTE IMMEDIATE c_trunc.sqltext;

   END LOOP;

   FOR c_cons_en

      IN (SELECT    'alter table '

                 || T2.owner

                 || '.'

                 || T2.table_name

                 || ' enable novalidate constraint '

                 || t2.CONSTRAINT_NAME

                    sqltext

            FROM all_constraints T2

           WHERE     t2.owner = 'ABPPMGR'

                 AND T2.CONSTRAINT_TYPE = 'R'

                 AND  T2.table_name IN

              (select phy_mst from abppmgr.SYS_TABLE_MAP) AND T2.CONSTRAINT_NAME NOT IN (SELECT CONSTRAINT_NAME FROM STG.DISABLED_R_CONSTRAINT) )

   LOOP

      EXECUTE IMMEDIATE c_cons_en.sqltext;

   END LOOP;

   V_Step := V_Step + 1;

   V_Action := PKG_CONSTANT.CONS_ACTION_DELETE;

   V_MainTable := 'ALL';

   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here

                       V_ProName,                    --Put procedure name here

                       V_Step,                      --Step seqence No. if have

                       V_Action,                      --Action 'I','D','U','T'

                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'

                       'remove data completed.', --Any generic information here

                       V_MainTable);

EXCEPTION

   --总程序异常处理部分

   WHEN OTHERS

   THEN

      BEGIN

         ROLLBACK;

         EXITCODE := 1;

         e_ErrMessage :=

            SUBSTR (SQLERRM, 1, 1000)

            || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000);

         PKG_UTIL.CREATELOG (V_PKGName,

                             V_ProName,

                             V_Step,

                             V_Action,

                             PKG_CONSTANT.CONS_MSG_EXCEPTION,

                             e_ErrMessage,

                             V_MainTable);

      END;

END;

/