create or replace PACKAGE TIDYPACKAGE AS

  procedure processTidyUp8DayForPlant(plantCode in VARCHAR);

  procedure deleteHistoryCmdtyParts (plantCode in VARCHAR);

  procedure deleteHistoryOrderCmdties (plantCode in VARCHAR);

  procedure deleteHistoryOrders (plantCode in VARCHAR);

  procedure copyCurrentOrders (plantCode in VARCHAR);

  procedure copyCurrentOrderCmdties (plantCode in VARCHAR);

  procedure copyCurrentCmdtyParts (plantCode in VARCHAR);

  procedure deleteCurrentCmdtyParts (plantCode in VARCHAR);

  procedure deleteCurrentOrderCmdties (plantCode in VARCHAR);
  
  procedure modifyTableConstraint (tableName in VARCHAR, constraintName in VARCHAR, modifyAction in VARCHAR);

  procedure deleteCurrentOrders (plantCode in VARCHAR);

  procedure updateExistingPartDetails (plantCode in VARCHAR);

  procedure insertNewPartDetails (plantCode in VARCHAR);

  procedure deleteDifferenceParts (plantCode in VARCHAR);

  procedure deleteDifferenceOrders (plantCode in VARCHAR);

  procedure doTruncatePartTablePartition (tableName IN VARCHAR2, partitionName IN VARCHAR2);

END TIDYUP8DAYMESSAGEPACKAGE;

=======================================================================================================================================================================


create or replace PACKAGE BODY TIDYUP8DAYMESSAGEPACKAGE AS

  procedure processTidyUp8DayForPlant(plantCode in VARCHAR) AS
  BEGIN

    -- remove the existing historical view
    deleteHistoryCmdtyParts (plantCode);
    deleteHistoryOrderCmdties (plantCode);
    deleteHistoryOrders (plantCode);

    -- copy existing current view to historical view
    copyCurrentOrders (plantCode);
    copyCurrentOrderCmdties (plantCode);
    copyCurrentCmdtyParts (plantCode);

    -- delete existing current view
    deleteCurrentCmdtyParts (plantCode);
    deleteCurrentOrderCmdties (plantCode);
    deleteCurrentOrders (plantCode);

    -- update part details
    updateExistingPartDetails (plantCode);
    insertNewPartDetails (plantCode);

    -- delete differences view
    deleteDifferenceParts (plantCode);
    deleteDifferenceOrders (plantCode);


  END processTidyUp8DayForPlant;

  procedure deleteHistoryCmdtyParts (plantCode in VARCHAR) AS

  BEGIN

    case plantCode
        when 'C' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_C');
        when 'G' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_G');
        when 'H' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_H');
        when 'S' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_S');
        when 'K' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_K');
        when 'X' then
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_X');
        else
            doTruncatePartTablePartition(
                'SCS_HISTORY_ORDER_CMDTY_PART',
                'PLANT_CODE_OTHER');

    end case;

  END deleteHistoryCmdtyParts;

  procedure deleteHistoryOrderCmdties (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting  SCS_HISTORY_ORDER_COMMODITY for plant code ' || plantCode);

    delete SCS_HISTORY_ORDER_COMMODITY
    /*+ index( scs_history_order_commodity scs_history_order_commodity_pk)*/
    where SCS_HISTORY_ORDER_COMMODITY.ORDER_NUMBER in (
      select SCS_HISTORY_ORDER.ORDER_NUMBER
      from SCS_HISTORY_ORDER
      inner join MODEL on SCS_HISTORY_ORDER.MODEL_CODE = MODEL.MODEL_CODE
      where MODEL.FK_PLANT_CODE = plantCode);

  END deleteHistoryOrderCmdties;

  procedure deleteHistoryOrders (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting  SCS_HISTORY_ORDER for plant code ' || plantCode);

    delete from SCS_HISTORY_ORDER
    where SCS_HISTORY_ORDER.MODEL_CODE in (
        select MODEL.MODEL_CODE
        from MODEL WHERE MODEL.FK_PLANT_CODE = plantCode);

  END deleteHistoryOrders;

  procedure copyCurrentOrders (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Inserting into  SCS_HISTORY_ORDER for plant code ' || plantCode);

    insert into SCS_HISTORY_ORDER (
      ORDER_NUMBER,
      ORD_OFFLINE_DATE,
      ORD_STATUS_ID,
      ORDER_PROCESSED,
      MODEL_CODE,
      ORD_LAUNCH_SEQ,
      ORD_BLEND_SEQ,
      BLEND_DATE,
      ACTUAL_OFFLINE_DATE,
      K_SPEC_BATCH_NO)
    select OBOM_ORDER.ORDER_NUMBER,
           OBOM_ORDER.ORD_OFFLINE_DATE,
           OBOM_ORDER.ORD_STATUS_ID,
           'N',
           OBOM_ORDER.MODEL_CODE,
           OBOM_ORDER.ORD_LAUNCH_SEQ,
           OBOM_ORDER.ORD_BLEND_SEQ,
           OBOM_ORDER.BLEND_DATE,
           OBOM_ORDER.ACTUAL_OFFLINE_DATE,
           OBOM_ORDER.K_SPEC_BATCH_NO
    from OBOM_ORDER
    inner join MODEL on OBOM_ORDER.MODEL_CODE = MODEL.MODEL_CODE
    where MODEL.FK_PLANT_CODE=plantCode;

  END copyCurrentOrders;

  procedure copyCurrentOrderCmdties (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Inserting into  SCS_HISTORY_ORDER_COMMODITY for plant code ' || plantCode);

    insert into SCS_HISTORY_ORDER_COMMODITY ( ORDER_NUMBER, COMMODITY_ID )
      select OBOM_ORDER_COMMODITY.ORDER_NUMBER, OBOM_ORDER_COMMODITY.COMMODITY_ID
      from OBOM_ORDER_COMMODITY
      inner join OBOM_ORDER
        on OBOM_ORDER_COMMODITY.ORDER_NUMBER = OBOM_ORDER.ORDER_NUMBER
      inner join MODEL
        on OBOM_ORDER.MODEL_CODE = MODEL.MODEL_CODE
      where MODEL.FK_PLANT_CODE=plantCode;

  END copyCurrentOrderCmdties;

  procedure copyCurrentCmdtyParts (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Inserting into  SCS_HISTORY_ORDER_COMDTY_PART for plant code ' || plantCode);

    insert into SCS_HISTORY_ORDER_CMDTY_PART (
        ORDER_NUMBER,
        ORDER_CMDTY_ID,
        PART_NUMBER,
        ORD_PART_QTY,
        PLANT_CODE)
    select DISTINCT OBOM_ORDER_COMMODITY_PART.ORDER_NUMBER,
                    OBOM_ORDER_COMMODITY_PART.ORDER_CMDTY_ID,
                    OBOM_ORDER_COMMODITY_PART.PART_NUMBER,
                    OBOM_ORDER_COMMODITY_PART.ORD_PART_QTY,
                    OBOM_ORDER_COMMODITY_PART.PLANT_CODE
    from OBOM_ORDER_COMMODITY_PART
    inner join OBOM_ORDER on OBOM_ORDER_COMMODITY_PART.ORDER_NUMBER = OBOM_ORDER.ORDER_NUMBER
    inner join MODEL on OBOM_ORDER.MODEL_CODE = MODEL.MODEL_CODE
    where MODEL.FK_PLANT_CODE= plantCode;

  END copyCurrentCmdtyParts;

  procedure deleteCurrentCmdtyParts (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting OBOM_ORDER_COMMODITY_PART for plant code ' || plantCode);

     case plantCode
        when 'C' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_C');
        when 'G' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_G');
        when 'H' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_H');
        when 'S' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_S');
        when 'K' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_K');
        when 'X' then
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_X');
        else
            doTruncatePartTablePartition(
                'OBOM_ORDER_COMMODITY_PART',
                'PLANT_CODE_OTHER');
    end case;

  END deleteCurrentCmdtyParts;

  procedure deleteCurrentOrderCmdties (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting  OBOM_ORDER_COMMODITY for plant code ' || plantCode);

    modifyTableConstraint ('OBOM_ORDER_COMMODITY_PART', 'FK_OO_CMDTY_PART_OO_CMDTY', 'DISABLE');

    delete OBOM_ORDER_COMMODITY
    /*+ index( obom_order_commodity obom_order_commodity_pk)*/
    where OBOM_ORDER_COMMODITY.ORDER_NUMBER in (
        select OBOM_ORDER.ORDER_NUMBER
        from OBOM_ORDER
        inner join MODEL on OBOM_ORDER.MODEL_CODE = MODEL.MODEL_CODE
        where MODEL.FK_PLANT_CODE = plantCode);

    modifyTableConstraint ('OBOM_ORDER_COMMODITY_PART', 'FK_OO_CMDTY_PART_OO_CMDTY', 'ENABLE');

  END deleteCurrentOrderCmdties;

  procedure modifyTableConstraint (tableName in VARCHAR, constraintName in VARCHAR, modifyAction in VARCHAR) AS
  BEGIN

    execute immediate 'ALTER TABLE '||tableName||
                      ' MODIFY CONSTRAINT '||constraintName||' ' || modifyAction;

  END modifyTableConstraint;

  procedure deleteCurrentOrders (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting OBOM_ORDER for plant code ' || plantCode);

    delete from OBOM_ORDER
    where OBOM_ORDER.MODEL_CODE in (
        select MODEL.MODEL_CODE
        from MODEL WHERE MODEL.FK_PLANT_CODE = plantCode);

  END deleteCurrentOrders;

  procedure updateExistingPartDetails (plantCode in VARCHAR) AS
  BEGIN

      --dbms_output.put_line('Updating PART_DETAILS for plant code ' || plantCode);

      update PART_DETAILS
        SET PART_DETAILS.PART_DESC = (
          select PART.PART_DESC
          from ORD_DIFF_PARTS_WITH_DETAILS
            inner join part
              on ORD_DIFF_PARTS_WITH_DETAILS.PART_NUM = PART.PART_NUM
              and ORD_DIFF_PARTS_WITH_DETAILS.PLANT_CODE = PART.PART_PLANT
              and ORD_DIFF_PARTS_WITH_DETAILS.PLANT_CODE = plantCode
              where PART_DETAILS.PART_NUMBER =
                    ORD_DIFF_PARTS_WITH_DETAILS.PART_NUM)
          where exists (
          select PART.PART_DESC
          from ORD_DIFF_PARTS_WITH_DETAILS
            inner join PART
              on ORD_DIFF_PARTS_WITH_DETAILS.PART_NUM = PART.PART_NUM
              and ORD_DIFF_PARTS_WITH_DETAILS.PLANT_CODE = PART.PART_PLANT
              and ORD_DIFF_PARTS_WITH_DETAILS.PLANT_CODE = plantCode
            where PART_DETAILS.PART_NUMBER =
                  ORD_DIFF_PARTS_WITH_DETAILS.PART_NUM);

  END updateExistingPartDetails;

  procedure insertNewPartDetails (plantCode in VARCHAR) AS
  BEGIN

      ----dbms_output.put_line('Inserting PART_DETAILS for plant code ' || plantCode);

      insert into PART_DETAILS (
          select ORD_DIFF_PARTS_WITHOUT_DETAILS.PART_NUM,
                 PART.PART_DESC
          from SCS_OWNER.ORD_DIFF_PARTS_WITHOUT_DETAILS
            inner join OBOMSO.PART
            on ORD_DIFF_PARTS_WITHOUT_DETAILS.PART_NUM = PART.PART_NUM
          where ORD_DIFF_PARTS_WITHOUT_DETAILS.PLANT_CODE = plantCode
          and obomso.part.PART_PLANT = plantCode);
  END insertNewPartDetails;

  procedure deleteDifferenceParts (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting  ORDER_DIFFERENCE_CMDTY_PART for plant code ' || plantCode);

    case plantCode
        when 'C' then
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_C');
        when 'G' then
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_G');
        when 'H' then
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_H');
        when 'S' then
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_S');
        when 'K' then
            doTruncatePartTablePartition (
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_K');
        when 'X' then
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_X');
        else
            doTruncatePartTablePartition(
                'ORDER_DIFFERENCE_CMDTY_PART',
                'PLANT_CODE_OTHER');
    end case;

  END deleteDifferenceParts;

  procedure deleteDifferenceOrders (plantCode in VARCHAR) AS
  BEGIN

    --dbms_output.put_line('Deleting  ORDER_DIFFERENCE_COMMODITY for plant code ' || plantCode);

    delete ORDER_DIFFERENCE_COMMODITY
      where ORDER_DIFFERENCE_COMMODITY.PLANT_CODE = plantCode;

  END deleteDifferenceOrders;

  procedure doTruncatePartTablePartition (tableName IN VARCHAR2, partitionName IN VARCHAR2) AS

      c1 binary_integer;
      sqlString VARCHAR2(250);

  BEGIN
        sqlString := 'alter table ' || tableName ||
                     ' truncate partition ' || partitionName ||
                     ' update global indexes';
          dbms_output.put_line('**** Doin'' ' || sqlString);
          c1 := dbms_sql.open_cursor;
          dbms_sql.parse(c1, sqlString, dbms_sql.native);
          dbms_sql.close_cursor(c1);

  END doTruncatePartTablePartition;

END TIDYUP8DAYMESSAGEPACKAGE;