CREATE OR REPLACE PACKAGE BODY MEDLKPADM.IE_DATA_MAINT
AS
   /****************************************************************************
      NAME:       IE_DATA_MAINT
      PURPOSE:

      REVISIONS:
      Ver        Date        Author           Short Description
      ---------  ----------  ---------------  ----------------------------------
      01.00      02/02/2022   mrh18496        1. Created this package body.

      Ver        Long Description
      ---------  ---------------------------------------------------------------
      01.00      1. Created this package body.

      NOTES:



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

   --=========================================================================--
   --=                       Private Globals and Types                       =--
   --=========================================================================--

   --Standard Function Return Codes
   pgc_Success   CONSTANT NUMBER := 0;            --Function Success Return Code
   pgc_Failure   CONSTANT NUMBER := 1;              --Function Error Return Code


   --=========================================================================--
   --=                   Private Procedures and Functions                    =--
   --=========================================================================--

   FUNCTION Valid_Score_Name( i_ScoreName IN VARCHAR2 )
      /*************************************************************************
       Name:    Valid_Score_Name

       Purpose: To take a CLOB and shorten it to a VARCHAR2 using parameterized
                starting position and overall size

       Parameters:
         i_ScoreName  in string   Name of the sequence to be reset

       Return:
         Number pgc_Success - Success
                pgc_Failure - Failure
       ************************************************************************/
      RETURN NUMBER
   IS
      o_Return   NUMBER := pgc_Failure;
   BEGIN
-- <Toad_181928047_1> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_1}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_1}[--- 1 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_1}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_1}[1] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_1}[1] o_Return = ' || o_Return);
-- </Toad_181928047_1>
      SELECT NVL( (SELECT pgc_Success
                     FROM SYS.ALL_TAB_COLS ATC
                    WHERE OWNER = 'MEDLKPADM'
                      AND TABLE_NAME = 'IE_UNMET_NEEDS_LKP'
                      AND COLUMN_NAME LIKE 'UNMET%'
                      AND COLUMN_NAME = i_ScoreName)
                , pgc_Failure
                 )
        INTO o_Return
        FROM DUAL;
-- <Toad_181928047_2> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_2}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_2}[--- 2 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_2}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_2}[2] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_2}[2] o_Return = ' || o_Return);
-- </Toad_181928047_2>

      RETURN o_Return;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(
            'ERROR IN FUNCTION IE_DATA_MAINT.Valid_Score_Name'
         );
         DBMS_OUTPUT.put_line( SQLERRM );
         DBMS_OUTPUT.put_line( SQLCODE );
         -- Log the Exception
         UTIL_PKG.LOG_EXCEPTION( 'MEDLKPADM.IE_DATA_MAINT'
                               , 'Valid_Score_Name'
                               , 'Error'
                               ,    SQLCODE
                                 || ': '
                                 || SQLERRM
                               ,    'i_ScoreName =:'
                                 || i_ScoreName
                                 || ':'
                                );
-- <Toad_181928047_3> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_3}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_3}[--- 3 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_3}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_3}[3] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_3}[3] o_Return = ' || o_Return);
-- </Toad_181928047_3>
   END Valid_Score_Name;

   --=========================================================================--
   --=                    Public Procedures and Functions                    =--
   --=========================================================================--


   PROCEDURE p_MUN_PurgeScores( i_Product     IN VARCHAR2
                              , i_ScoreName   IN VARCHAR2
                               )
   /***************************************************************************
    Name:    p_MUN_PurgeScores
    Created: 2/2/2022 5:13:20 PM by mrh18496
    Purpose: To purge records from IE_UNMET_NEEDS_LKP for a specific Product
             Score combination

    Parameters:
      i_Product   IN VARCHAR2 - Product Name
      i_ScoreName IN VARCHAR2 - MUN Score Column Name

    Error Handling: Exceptions will be logged via local
                    UTIL_PKG.LOG_EXCEPTION

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

   IS
      -- Local Variable Definitions
      v_tmpVar   NUMBER DEFAULT 0;
   BEGIN
-- <Toad_181928047_4> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_4}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_4}[--- 4 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_4}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_4}[4] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_4}[4] o_Return = ' || o_Return);
-- </Toad_181928047_4>
      v_tmpVar := 1;
-- <Toad_181928047_5> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}[--- 5 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}[5] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}[5] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_5}[5] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_5>
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(
            'ERROR IN FUNCTION IE_DATA_MAINT.p_MUN_PurgeScores'
         );
         DBMS_OUTPUT.put_line( SQLERRM );
         DBMS_OUTPUT.put_line( SQLCODE );
         -- Log the Exception
         UTIL_PKG.LOG_EXCEPTION( 'MEDLKPADM.IE_DATA_MAINT'
                               , 'p_MUN_PurgeScores'
                               , 'Error'
                               ,    SQLCODE
                                 || ': '
                                 || SQLERRM
                               ,    'i_Product =:'
                                 || i_Product
                                 || ':,i_ScoreName =:'
                                 || i_ScoreName
                                 || ':'
                                );
-- <Toad_181928047_6> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}[--- 6 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}[6] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}[6] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_6}[6] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_6>
   END p_MUN_PurgeScores;


   PROCEDURE p_MUN_DecrementScores( i_Product     IN VARCHAR2
                                  , i_ScoreName   IN VARCHAR2
                                  , i_Value       IN NUMBER
                                  , i_Frequency   IN NUMBER
                                   )
   /***************************************************************************
    Name:    p_MUN_DecrementScores
    Created: 2/3/2022 2:01:21 PM by mrh18496
    Purpose:

    Parameters:
      i_Product     IN  Product associated with the score to be decremented
      i_ScoreName   IN  Score to be Decremented
      i_Value       IN  Amount to decrement the Score by
      i_Frequency   IN  Number of days before Socer is Decremented

    Error Handling: Exceptions will be logged via local
                    UTIL_PKG.LOG_EXCEPTION

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

   IS
      -- Local Variable Definitions
      v_Query       VARCHAR2( 2000 CHAR );
      v_ScoreName   VARCHAR2( 128 CHAR ) := UPPER( i_ScoreName );
      v_Status      NUMBER := pgc_Success;
   BEGIN
-- <Toad_181928047_7> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}[--- 7 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}[7] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}[7] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_7}[7] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_7>
      v_Status := IE_DATA_MAINT.Valid_Score_Name( v_ScoreName );
-- <Toad_181928047_8> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[--- 8 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] i_Frequency = ' || i_Frequency);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] i_Value = ' || i_Value);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] v_Query = ' || v_Query);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] v_ScoreName = ' || v_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_8}[8] v_Status = ' || v_Status);
-- </Toad_181928047_8>

      IF v_Status = pgc_Success
      THEN
         --Set Query Text
         /*
         v_Query :=
               ' UPDATE MEDLKPADM.IE_UNMET_NEEDS_LKP SET '
            || i_ScoreName
            || ' = '
            || i_ScoreName
            || ' - '
            || i_Value
            || ' WHERE PRODUCT = '''
            || i_Product
            || ''' AND (AUDT_LST_CHG_DT IS NULL AND TRUNC( AUDT_ADD_DT ) < TRUNC( SYSDATE ) - '
            || i_Frequency
            || ') OR (TRUNC( I.AUDT_LST_CHG_DT ) < TRUNC( SYSDATE ) - '
            || i_Frequency
            || ');';
            */
         v_Query :=
               ' UPDATE MEDLKPADM.IE_UNMET_NEEDS_LKP SET '
            || i_ScoreName
            || ' = '
            || i_ScoreName
            || ' - '
            || i_Value
            || ' WHERE PRODUCT = '''
            || i_Product
            || ''' AND '
            || i_ScoreName
            || ' IS NOT NULL AND  '
            || i_ScoreName
            || ' >= '
            || i_Value
            || ' ;';
-- <Toad_181928047_9> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[--- 9 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] i_Frequency = ' || i_Frequency);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] i_Value = ' || i_Value);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] v_Query = ' || v_Query);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] v_ScoreName = ' || v_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_9}[9] v_Status = ' || v_Status);
-- </Toad_181928047_9>
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(
            'ERROR IN FUNCTION IE_DATA_MAINT.p_MUN_DecrementScores'
         );
         DBMS_OUTPUT.put_line( SQLERRM );
         DBMS_OUTPUT.put_line( SQLCODE );
         -- Log the Exception
         UTIL_PKG.LOG_EXCEPTION( '<Schema>.IE_DATA_MAINT'
                               , 'p_MUN_DecrementScores'
                               , 'Error'
                               ,    SQLCODE
                                 || ': '
                                 || SQLERRM
                               , NULL     --List Input Values seperated by Comma
                                -- (I_PRODUCT IN VARCHAR2, I_SCORENAME IN VARCHAR2, I_VALUE IN NUMBER)
                                 );
-- <Toad_181928047_10> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[--- 10 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] i_Frequency = ' || i_Frequency);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] i_Value = ' || i_Value);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] v_Query = ' || v_Query);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] v_ScoreName = ' || v_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_10}[10] v_Status = ' || v_Status);
-- </Toad_181928047_10>
   END p_MUN_DecrementScores;


   PROCEDURE p_MUN_RemoveEmptyRows
   /***************************************************************************
    Name:    p_MUN_RemoveEmptyRows
    Created: 2/3/2022 2:01:21 PM by mrh18496
    Purpose:

    Parameters:


    Error Handling: Exceptions will be logged via local
                    UTIL_PKG.LOG_EXCEPTION

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

   IS
      -- Local Variable Definitions
      v_tmpVar   NUMBER DEFAULT 0;
   BEGIN
-- <Toad_181928047_11> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[--- 11 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] i_Frequency = ' || i_Frequency);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] i_Product = ' || i_Product);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] i_ScoreName = ' || i_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] i_Value = ' || i_Value);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] v_Query = ' || v_Query);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] v_ScoreName = ' || v_ScoreName);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_11}[11] v_Status = ' || v_Status);
-- </Toad_181928047_11>
      v_tmpVar := 1;
-- <Toad_181928047_12> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_12}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_12}[--- 12 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_12}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_12}[12] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_12>
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(
            'ERROR IN FUNCTION IE_DATA_MAINT.p_MUN_RemoveEmptyRows'
         );
         DBMS_OUTPUT.put_line( SQLERRM );
         DBMS_OUTPUT.put_line( SQLCODE );
         -- Log the Exception
         UTIL_PKG.LOG_EXCEPTION( '<Schema>.IE_DATA_MAINT'
                               , 'p_MUN_RemoveEmptyRows'
                               , 'Error'
                               ,    SQLCODE
                                 || ': '
                                 || SQLERRM
                               , NULL     --List Input Values seperated by Comma
                                --
                                 );
-- <Toad_181928047_13> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_13}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_13}[--- 13 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_13}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_13}[13] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_13>
   END p_MUN_RemoveEmptyRows;


   PROCEDURE p_MUN_MergeScore( I_PRODUCT     IN VARCHAR2
                             , I_CID         IN NVARCHAR2
                             , I_SCORENAME   IN VARCHAR2
                             , I_SCORE       IN VARCHAR2
                              )
   /***************************************************************************
    Name:    p_MUN_MergeScore
    Created: 2/3/2022 2:01:21 PM by mrh18496
    Purpose:

    Parameters:
      (I_PRODUCT IN VARCHAR2, I_CID IN NVARCHAR2, I_SCORENAME IN VARCHAR2, I_SCORE IN VARCHAR2)

    Error Handling: Exceptions will be logged via local
                    UTIL_PKG.LOG_EXCEPTION

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

   IS
      -- Local Variable Definitions
      v_tmpVar   NUMBER DEFAULT 0;
   BEGIN
-- <Toad_181928047_14> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_14}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_14}[--- 14 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_14}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_14}[14] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_14>
      v_tmpVar := 1;
-- <Toad_181928047_15> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[--- 15 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[15] I_CID = ' || I_CID);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[15] I_PRODUCT = ' || I_PRODUCT);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[15] I_SCORE = ' || I_SCORE);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[15] I_SCORENAME = ' || I_SCORENAME);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_15}[15] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_15>
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(
            'ERROR IN FUNCTION IE_DATA_MAINT.p_MUN_MergeScore'
         );
         DBMS_OUTPUT.put_line( SQLERRM );
         DBMS_OUTPUT.put_line( SQLCODE );
         -- Log the Exception
         UTIL_PKG.LOG_EXCEPTION( '<Schema>.IE_DATA_MAINT'
                               , 'p_MUN_MergeScore'
                               , 'Error'
                               ,    SQLCODE
                                 || ': '
                                 || SQLERRM
                               , NULL     --List Input Values seperated by Comma
                                -- (I_PRODUCT IN VARCHAR2, I_CID IN NVARCHAR2, I_SCORENAME IN VARCHAR2, I_SCORE IN VARCHAR2)
                                 );
-- <Toad_181928047_16> *** DO NOT REMOVE THE AUTO DEBUGGER START/END TAGS
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[--- 16 ---]');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}');
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[16] I_CID = ' || I_CID);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[16] I_PRODUCT = ' || I_PRODUCT);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[16] I_SCORE = ' || I_SCORE);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[16] I_SCORENAME = ' || I_SCORENAME);
DBMS_OUTPUT.PUT_LINE('{Toad_181928047_16}[16] v_tmpVar = ' || v_tmpVar);
-- </Toad_181928047_16>
   END p_MUN_MergeScore;
END IE_DATA_MAINT;
/