CREATE OR REPLACE PACKAGE BODY intf_tbl_update
AS
   PROCEDURE intfc_rec_update
   (
      p_action             IN     VARCHAR2,
      p_table              IN     VARCHAR2,
      p_set_col_name1      IN     VARCHAR2,
      p_set_col_value1     IN     VARCHAR2,
      p_set_col_name2      IN     VARCHAR2,
      p_set_col_value2     IN     VARCHAR2,
      p_set_col_name3      IN     VARCHAR2,
      p_set_col_value3     IN     VARCHAR2,
      p_set_col_name4      IN     VARCHAR2,
      p_set_col_value4     IN     VARCHAR2,
      p_set_col_name5      IN     VARCHAR2,
      p_set_col_value5     IN     VARCHAR2,
      p_set_col_name6      IN     VARCHAR2,
      p_set_col_value6     IN     VARCHAR2,
      p_set_col_name7      IN     VARCHAR2,
      p_set_col_value7     IN     VARCHAR2,
      p_set_col_name8      IN     VARCHAR2,
      p_set_col_value8     IN     VARCHAR2,
      p_set_col_name9      IN     VARCHAR2,
      p_set_col_value9     IN     VARCHAR2,
      p_set_col_name10     IN     VARCHAR2,
      p_set_col_value10    IN     VARCHAR2,
      p_where_col_name1    IN     VARCHAR2,
      p_where_col_value1   IN     VARCHAR2,
      p_where_col_name2    IN     VARCHAR2,
      p_where_col_value2   IN     VARCHAR2,
      p_where_col_name3    IN     VARCHAR2,
      p_where_col_value3   IN     VARCHAR2,
      p_where_col_name4    IN     VARCHAR2,
      p_where_col_value4   IN     VARCHAR2,
      p_where_col_name5    IN     VARCHAR2,
      p_where_col_value5   IN     VARCHAR2,
      p_reterror              OUT VARCHAR2,
      p_retcode               OUT NUMBER
   )
   IS
      l_action             VARCHAR2 (10);
      l_table              VARCHAR2 (50);
      l_set_col_name1      VARCHAR2 (50);
      l_set_col_value1     VARCHAR2 (2000);
      l_set_col_name2      VARCHAR2 (50);
      l_set_col_value2     VARCHAR2 (2000);
      l_set_col_name3      VARCHAR2 (50);
      l_set_col_value3     VARCHAR2 (2000);
      l_set_col_name4      VARCHAR2 (50);
      l_set_col_value4     VARCHAR2 (2000);
      l_set_col_name5      VARCHAR2 (50);
      l_set_col_value5     VARCHAR2 (2000);
      l_set_col_name6      VARCHAR2 (50);
      l_set_col_value6     VARCHAR2 (2000);
      l_set_col_name7      VARCHAR2 (50);
      l_set_col_value7     VARCHAR2 (2000);
      l_set_col_name8      VARCHAR2 (50);
      l_set_col_value8     VARCHAR2 (2000);
      l_set_col_name9      VARCHAR2 (50);
      l_set_col_value9     VARCHAR2 (2000);
      l_set_col_name10     VARCHAR2 (50);
      l_set_col_value10    VARCHAR2 (2000);
      l_where_col_name1    VARCHAR2 (50);
      l_where_col_value1   VARCHAR2 (2000);
      l_where_col_name2    VARCHAR2 (50);
      l_where_col_value2   VARCHAR2 (2000);
      l_where_col_name3    VARCHAR2 (50);
      l_where_col_value3   VARCHAR2 (2000);
      l_where_col_name4    VARCHAR2 (50);
      l_where_col_value4   VARCHAR2 (2000);
      l_where_col_name5    VARCHAR2 (50);
      l_where_col_value5   VARCHAR2 (2000);
      l_quote              VARCHAR2 (1) := CHR (39);
      l_plsql_block        VARCHAR2 (2000) := NULL;
   BEGIN
      l_action             := p_action;
      l_table              := p_table;
      l_set_col_name1      := p_set_col_name1;
      l_set_col_value1     := p_set_col_value1;
      l_set_col_name2      := p_set_col_name2;
      l_set_col_value2     := p_set_col_value2;
      l_set_col_name3      := p_set_col_name3;
      l_set_col_value3     := p_set_col_value3;
      l_set_col_name4      := p_set_col_name4;
      l_set_col_value4     := p_set_col_value4;
      l_set_col_name5      := p_set_col_name5;
      l_set_col_value5     := p_set_col_value5;
      l_set_col_name6      := p_set_col_name6;
      l_set_col_value6     := p_set_col_value6;
      l_set_col_name7      := p_set_col_name7;
      l_set_col_value7     := p_set_col_value7;
      l_set_col_name8      := p_set_col_name8;
      l_set_col_value8     := p_set_col_value8;
      l_set_col_name9      := p_set_col_name9;
      l_set_col_value9     := p_set_col_value9;
      l_set_col_name10     := p_set_col_name10;
      l_set_col_value10    := p_set_col_value10;
      l_where_col_name1    := p_where_col_name1;
      l_where_col_value1   := p_where_col_value1;
      l_where_col_name2    := p_where_col_name2;
      l_where_col_value2   := p_where_col_value2;
      l_where_col_name3    := p_where_col_name3;
      l_where_col_value3   := p_where_col_value3;
      l_where_col_name4    := p_where_col_name4;
      l_where_col_value4   := p_where_col_value4;
      l_where_col_name5    := p_where_col_name5;
      l_where_col_value5   := p_where_col_value5;

      IF (    (l_where_col_name1 IS NULL)
          AND (l_where_col_value1 IS NULL)
          AND (l_where_col_name2 IS NULL)
          AND (l_where_col_value2 IS NULL)
          AND (l_where_col_name3 IS NULL)
          AND (l_where_col_value3 IS NULL)
          AND (l_where_col_name4 IS NULL)
          AND (l_where_col_value4 IS NULL)
          AND (l_where_col_name5 IS NULL)
          AND (l_where_col_value5 IS NULL))
      THEN
         p_retcode   := 1;
         p_reterror      :=
            'Please enter atleast one selection criteria to update the record and resubmit the program...';
      ELSE
         BEGIN
            -- Dynamic PL/SQL block invokes subprogram:
            l_plsql_block      :=
                  'BEGIN '
               || l_action
               || ' '
               || l_table
               || ' TBL SET TBL.'
               || l_set_col_name1
               || ' = '
               || l_quote
               || l_set_col_value1
               || l_quote
               || ' WHERE TBL.'
               || l_where_col_name1
               || ' = '
               || l_quote
               || l_where_col_value1
               || l_quote
               || '; COMMIT; END;';

            EXECUTE IMMEDIATE l_plsql_block
               USING IN OUT l_action,
                     l_table,
                     l_set_col_name1,
                     l_set_col_value1,
                     l_set_col_name2,
                     l_set_col_value2,
                     l_set_col_name3,
                     l_set_col_value3,
                     l_set_col_name4,
                     l_set_col_value4,
                     l_set_col_name5,
                     l_set_col_value5,
                     l_set_col_name6,
                     l_set_col_value6,
                     l_set_col_name7,
                     l_set_col_value7,
                     l_set_col_name8,
                     l_set_col_value8,
                     l_set_col_name9,
                     l_set_col_value9,
                     l_set_col_name10,
                     l_set_col_value10,
                     l_where_col_name1,
                     l_where_col_value1,
                     l_where_col_name2,
                     l_where_col_value2,
                     l_where_col_name3,
                     l_where_col_value3,
                     l_where_col_name4,
                     l_where_col_value4,
                     l_where_col_name5,
                     l_where_col_value5;

            COMMIT;
            p_retcode    := 0;
            p_reterror   := 'Interface Table Record Updated successfully';
         EXCEPTION
            WHEN OTHERS
            THEN
               p_retcode   := 1;
               p_reterror      :=
                     'Interface Table Record Update failed with exception :: *** '
                  || l_plsql_block
                  || ' ***'
                  || SUBSTR (SQLERRM, 1, 1999);
         END;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_retcode    := 1;
         p_reterror   := SUBSTR (SQLERRM, 1, 1999);
   END;
END intf_tbl_update;