create or replace package xxx_plsql_generator_pkg is  /*==================================================    Copyright (C) LYR Consulting Co., Ltd     All rights reserved  ===================================================*/  -- Author  : luoyuren  -- Created : 2010-12-07  -- Purpose : Auto Generat Plsql Code  g_exception exception;  /******************************************************************    IN     p_block_name  Form中数据块的名称     p_table_name  需要进行操作的数据表     p_owner       数据表的owner     p_primary_key 数据表的主键    DESCEIPTOIN     此过程主要用来生成基于视图的数据块常用触发器ON-INSERT,     ON-UPDATE,ON-DELETE,ON-LOCK之代码,使用时采用PL/SQL Developer     开发工具的的Test Windows,代码执行完成后会在DBMS Output标签页     中输出相关代码,使用时将代码Copy至Oralce Form Builder的Program     Units中,分为Package Spec和Package Body,并在相应触发器中加入相     应引用代码;    ***************************************************************/   procedure form_view_iud_p(p_block_name  in varchar2                           ,p_table_name  in varchar2                           ,p_owner       in varchar2                           ,p_primary_key in varchar2);end xxx_plsql_generator_pkg;/create or replace package body xxx_plsql_generator_pkg is  /*==================================================    Copyright (C) LYR Consulting Co., Ltd     All rights reserved  ===================================================*/  g_output_first boolean := false;  g_cp_flag      number; -- conc program  g_newline      varchar2(1) := chr(10);  type column_rec_type is record(    column_name varchar2(30),    nullable    varchar2(1));  type column_tbl_type is table of column_rec_type index by binary_integer;  procedure output_msg(p_msg_data in varchar2) is  begin    if g_output_first = false    then      g_cp_flag := fnd_profile.value(‘CONC_REQUEST_ID‘);      if g_cp_flag > 0      then        null;      else        dbms_output.enable(buffer_size => 20000000);      end if;    end if;      if (g_cp_flag > 0)    then      fnd_file.put_line(fnd_file.log                       ,p_msg_data);    else      dbms_output.put_line(p_msg_data);    end if;  exception    when others then      null;  end output_msg;  procedure form_view_iud_p(p_block_name  in varchar2                           ,p_table_name  in varchar2                           ,p_owner       in varchar2                           ,p_primary_key in varchar2) is    cursor c_table is      select ‘Y‘        from all_tables t       where t.table_name = upper(p_table_name)         and t.owner = upper(p_owner);      cursor c_tabcol is      select ‘Y‘        from all_tab_columns tc       where tc.table_name = upper(p_table_name)         and tc.owner = upper(p_owner)         and tc.column_name = upper(p_primary_key);      cursor c_sequence is      select ‘Y‘        from all_objects ao       where ao.owner = p_owner         and ao.object_type = ‘SEQUENCE‘         and ao.object_name = upper(p_table_name) || ‘_S‘;      cursor c_columns is      select tc.column_name            ,tc.nullable        from all_tab_columns tc       where tc.table_name = upper(p_table_name)         and tc.owner = upper(p_owner)       order by tc.column_id;      l_dummy          varchar2(1);    l_msg_data       varchar2(4000);    l_package_name   varchar2(100);    l_block_name_ext varchar2(50) := ‘:‘ || upper(p_block_name) || ‘.‘;    l_rec_ext        varchar2(30) := ‘rec.‘;      l_column_tbl   column_tbl_type;    l_column_count number := 0;    begin      if (p_block_name is null) or       (p_table_name is null) or       (p_owner is null) or       (p_primary_key is null)    then      l_msg_data := ‘Parameter not allow null !‘;      raise fnd_api.g_exc_error;    end if;      -- check table exists    open c_table;    fetch c_table      into l_dummy;    if c_table%notfound    then      close c_table;      l_msg_data := ‘Table ‘ || upper(p_table_name) || ‘ not found !‘;      raise fnd_api.g_exc_error;    end if;    close c_table;      --check primary_key exists    open c_tabcol;    fetch c_tabcol      into l_dummy;    if c_tabcol%notfound    then      close c_tabcol;      l_msg_data := ‘Table ‘ || upper(p_table_name) ||                    ‘ not exists column ‘ || upper(p_primary_key) || ‘ !‘;      raise fnd_api.g_exc_error;    end if;    close c_tabcol;      --check sequence exists    /*Open c_sequence;    Fetch c_sequence      Into l_dummy;    If c_sequence%Notfound Then      Close c_sequence;      l_msg_data := ‘Sequence ‘ || upper(p_block_name) || ‘_S not exists !‘;      Raise fnd_api.g_exc_error;    End If;    Close c_sequence;*/      -- get columns    for r in c_columns    loop      l_column_count := l_column_count + 1;      l_column_tbl(l_column_count).column_name := r.column_name;      l_column_tbl(l_column_count).nullable := r.nullable;    end loop;      if l_column_count < 1    then      l_msg_data := ‘Not column in table ‘ || p_table_name;      raise fnd_api.g_exc_error;    end if;      l_package_name := upper(p_block_name) || ‘_PKG‘;    -- generate package special    l_msg_data := ‘PACKAGE ‘ || l_package_name || ‘ IS‘ || g_newline ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘/*===============================================================‘ ||                  g_newline ||                  ‘*   Copyright (C) LYR Consulting Co., Ltd All rights reserved‘ ||                  g_newline ||                  ‘* ===============================================================‘ ||                  g_newline || ‘*    Program Name:‘ || l_package_name;      output_msg(l_msg_data);      l_msg_data := ‘*    Author      :‘ || g_newline || ‘*    Date        :‘ ||                  g_newline || ‘*    Purpose     :‘ || g_newline ||                  ‘*    Parameters  :‘ || g_newline ||                  ‘*    Update History‘ || g_newline ||                  ‘*    Version    Date         Name            Description‘ ||                  g_newline ||                  ‘*    --------  ----------  ---------------  --------------------‘;      output_msg(l_msg_data);      l_msg_data := ‘*     V1.0                                   Creation    ‘ ||                  g_newline || ‘*‘ || g_newline ||                  ‘  ===============================================================*/‘ ||                  g_newline;      output_msg(l_msg_data);      l_msg_data := ‘  PROCEDURE insert_row;‘ || g_newline ||                  ‘  PROCEDURE lock_row;‘ || g_newline ||                  ‘  PROCEDURE update_row;‘ || g_newline ||                  ‘  PROCEDURE delete_row;‘ || g_newline || g_newline ||                  ‘END ‘ || l_package_name || ‘;‘ || g_newline;      output_msg(l_msg_data);      -- generate package body    -- begin    l_msg_data := ‘PACKAGE BODY ‘ || l_package_name || ‘ IS‘ || g_newline;    output_msg(l_msg_data);      -- insert row      /*    l_msg_data := ‘\*=====================================‘ || g_newline || ‘** PROCEDURE:  insert_row()‘ || g_newline ||                  ‘**=====================================*\‘ || g_newline || ‘PROCEDURE insert_row IS‘ || g_newline;                  */      l_msg_data := ‘/*===============================================================‘ ||                  g_newline || ‘*    Program Name:insert_row()‘ ||                  g_newline || ‘*    Author      :‘ || g_newline ||                  ‘*    Date        :‘ || g_newline || ‘*    Purpose     :‘ ||                  g_newline || ‘*    Parameters  :‘ || g_newline ||                  ‘*             In       X     --X的说明‘;      output_msg(l_msg_data);      l_msg_data := ‘*             Out      Y     --Y的说明‘ || g_newline ||                  ‘*    Update History‘ || g_newline ||                  ‘*    Version    Date         Name            Description‘ ||                  g_newline ||                  ‘*    --------  ----------  ---------------  --------------------‘;      output_msg(l_msg_data);      l_msg_data := ‘*     V1.0                                   Creation    ‘ ||                  g_newline || ‘*‘ || g_newline ||                  ‘  ===============================================================*/‘ ||                  g_newline || ‘PROCEDURE insert_row IS‘;      output_msg(l_msg_data);      l_msg_data := ‘  CURSOR row_id‘ || g_newline || ‘  IS    SELECT ROWID‘ ||                  g_newline || ‘       FROM ‘ || p_table_name || g_newline ||                  ‘      WHERE ‘ || p_primary_key || ‘ = ‘ ||                  l_block_name_ext || p_primary_key || ‘;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘BEGIN‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  fnd_standard.set_who;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  IF ‘ || l_block_name_ext || p_primary_key ||                  ‘ IS NULL THEN‘ || g_newline || ‘    SELECT ‘ ||                  upper(p_table_name) || ‘_S.NEXTVAL‘ || g_newline ||                  ‘      INTO ‘ || l_block_name_ext || p_primary_key ||                  g_newline || ‘      FROM SYS.DUAL;‘ || g_newline ||                  ‘  END IF;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  INSERT INTO ‘ || p_table_name || ‘ (‘;    output_msg(l_msg_data);    for i in 1 .. l_column_count    loop      if i = l_column_count      then        l_msg_data := ‘     ‘ || l_column_tbl(i).column_name || ‘)‘;      else        l_msg_data := ‘     ‘ || l_column_tbl(i).column_name || ‘,‘;      end if;      output_msg(l_msg_data);    end loop;    l_msg_data := ‘  VALUES (‘;    output_msg(l_msg_data);    for i in 1 .. l_column_count    loop      if i = l_column_count      then        l_msg_data := ‘     ‘ || l_block_name_ext || l_column_tbl(i)                     .column_name || ‘);‘ || g_newline;      else        l_msg_data := ‘     ‘ || l_block_name_ext || l_column_tbl(i)                     .column_name || ‘,‘;      end if;      output_msg(l_msg_data);    end loop;      l_msg_data := ‘  OPEN row_id;‘ || g_newline || ‘  FETCH row_id INTO ‘ ||                  l_block_name_ext || ‘ROW_ID;‘ || g_newline ||                  ‘  IF (row_id%NOTFOUND) THEN‘ || g_newline ||                  ‘     CLOSE row_id;‘ || g_newline ||                  ‘     RAISE NO_DATA_FOUND;‘ || g_newline || ‘  END IF;‘ ||                  g_newline || ‘  CLOSE row_id;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘END insert_row;‘ || g_newline;    output_msg(l_msg_data);      -- lock row      --l_msg_data := ‘\*=====================================‘ || g_newline || ‘** PROCEDURE:  lock_row()‘ || g_newline ||    --              ‘**=====================================*\‘ || g_newline || ‘PROCEDURE lock_row IS‘ || g_newline;      l_msg_data := ‘/*===============================================================‘ ||                  g_newline || ‘*    Program Name:lock_row()‘ || g_newline ||                  ‘*    Author      :‘ || g_newline || ‘*    Date        :‘ ||                  g_newline || ‘*    Purpose     :‘ || g_newline ||                  ‘*    Parameters  :‘ || g_newline ||                  ‘*             In       X     --X的说明‘;      output_msg(l_msg_data);      l_msg_data := ‘*             Out      Y     --Y的说明‘ || g_newline ||                  ‘*    Update History‘ || g_newline ||                  ‘*    Version    Date         Name            Description‘ ||                  g_newline ||                  ‘*    --------  ----------  ---------------  --------------------‘;      output_msg(l_msg_data);      l_msg_data := ‘*     V1.0                                   Creation    ‘ ||                  g_newline || ‘*‘ || g_newline ||                  ‘  ===============================================================*/‘ ||                  g_newline || ‘PROCEDURE lock_row IS‘;      output_msg(l_msg_data);      l_msg_data := ‘  CURSOR c_row‘ || g_newline || ‘  IS SELECT *‘ ||                  g_newline || ‘       FROM ‘ || p_table_name || g_newline ||                  ‘      WHERE rowid = ‘ || l_block_name_ext || ‘ROW_ID‘ ||                  g_newline || ‘      FOR UPDATE OF ‘ || p_primary_key ||                  ‘ NOWAIT;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  rec     c_row%rowtype;‘ || g_newline ||                  ‘  i NUMBER := 0;‘ || g_newline || ‘BEGIN‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  LOOP‘ || g_newline || ‘    BEGIN‘;    output_msg(l_msg_data);      l_msg_data := ‘      i := i + 1;‘ || g_newline || ‘      OPEN c_row;‘ ||                  g_newline || ‘      FETCH c_row INTO rec;‘;    output_msg(l_msg_data);      l_msg_data := ‘      IF (c_row%NOTFOUND) THEN‘ || g_newline ||                  ‘        CLOSE c_row;‘ || g_newline ||                  ‘        fnd_message.set_name(‘‘FND‘‘,‘‘FORM_RECORD_DELETED‘‘);‘ ||                  g_newline || ‘        fnd_message.error;‘ || g_newline ||                  ‘        RAISE FORM_TRIGGER_FAILURE;‘;    output_msg(l_msg_data);      l_msg_data := ‘      END IF;‘ || g_newline || ‘      CLOSE c_row;‘ ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘      IF (‘;    output_msg(l_msg_data);    for i in 1 .. l_column_count    loop      if i = 1      then        l_msg_data := ‘              ‘;      else        l_msg_data := ‘          AND ‘;      end if;      if l_column_tbl(i).column_name = p_primary_key      then        l_msg_data := l_msg_data || ‘(‘ || l_rec_ext || l_column_tbl(i)                     .column_name || ‘ = ‘ || l_block_name_ext ||                      l_column_tbl(i).column_name || ‘)‘;      else        l_msg_data := l_msg_data || ‘((‘ || l_rec_ext || l_column_tbl(i)                     .column_name || ‘ = ‘ || l_block_name_ext ||                      l_column_tbl(i)                     .column_name || ‘) OR‘ || g_newline ||                      ‘               ((‘ || l_rec_ext || l_column_tbl(i)                     .column_name || ‘ IS NULL)‘ || ‘ AND (‘ ||                      l_block_name_ext || l_column_tbl(i)                     .column_name || ‘ IS NULL)))‘;      end if;      output_msg(l_msg_data);    end loop;      l_msg_data := ‘      ) THEN‘ || g_newline || ‘        RETURN;‘ ||                  g_newline || ‘      ELSE‘ || g_newline ||                  ‘        fnd_message.set_name(‘‘FND‘‘, ‘‘FORM_RECORD_CHANGED‘‘);‘ ||                  g_newline || ‘        fnd_message.error;‘ || g_newline ||                  ‘        RAISE FORM_TRIGGER_FAILURE;‘ || g_newline ||                  ‘      END IF;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘    EXCEPTION‘ || g_newline ||                  ‘      WHEN app_exception.record_lock_exception THEN‘ ||                  g_newline ||                  ‘        app_exception.record_lock_error(i);‘ ||                  g_newline || ‘    END;‘ || g_newline || ‘  END LOOP;‘ ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘END lock_row;‘ || g_newline;    output_msg(l_msg_data);      -- update row    l_msg_data := ‘/*===============================================================‘ ||                  g_newline || ‘*    Program Name:update_row()‘ ||                  g_newline || ‘*    Author      :‘ || g_newline ||                  ‘*    Date        :‘ || g_newline || ‘*    Purpose     :‘ ||                  g_newline || ‘*    Parameters  :‘ || g_newline ||                  ‘*             In       X     --X的说明‘;      output_msg(l_msg_data);      l_msg_data := ‘*             Out      Y     --Y的说明‘ || g_newline ||                  ‘*    Update History‘ || g_newline ||                  ‘*    Version    Date         Name            Description‘ ||                  g_newline ||                  ‘*    --------  ----------  ---------------  --------------------‘;      output_msg(l_msg_data);      l_msg_data := ‘*     V1.0                                   Creation    ‘ ||                  g_newline || ‘*‘ || g_newline ||                  ‘  ===============================================================*/‘ ||                  g_newline || ‘PROCEDURE update_row IS‘;      output_msg(l_msg_data);      l_msg_data := ‘BEGIN‘ || g_newline;    output_msg(l_msg_data);    l_msg_data := ‘  fnd_standard.set_who;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  UPDATE ‘ || p_table_name || ‘ SET‘;    output_msg(l_msg_data);    for i in 1 .. l_column_count    loop      l_msg_data := ‘      ‘ || rpad(l_column_tbl(i).column_name                                    ,30) || ‘ = ‘ || l_block_name_ext ||                    l_column_tbl(i).column_name;      if i < l_column_count      then        l_msg_data := l_msg_data || ‘,‘;      end if;      output_msg(l_msg_data);    end loop;    l_msg_data := ‘  WHERE ROWID = ‘ || l_block_name_ext || ‘ROW_ID;‘ ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  IF (SQL%NOTFOUND) THEN‘ || g_newline ||                  ‘     RAISE NO_DATA_FOUND;‘ || g_newline || ‘  END IF;‘ ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘END update_row;‘ || g_newline;    output_msg(l_msg_data);      -- delete row      --l_msg_data := ‘\*=====================================‘ || g_newline || ‘** PROCEDURE:  delete_row()‘ || g_newline ||    --                  ‘**=====================================*\‘ || g_newline || ‘PROCEDURE delete_row IS‘;      l_msg_data := ‘/*===============================================================‘ ||                  g_newline || ‘*    Program Name:delete_row()‘ ||                  g_newline || ‘*    Author      :‘ || g_newline ||                  ‘*    Date        :‘ || g_newline || ‘*    Purpose     :‘ ||                  g_newline || ‘*    Parameters  :‘ || g_newline ||                  ‘*             In       X     --X的说明‘;      output_msg(l_msg_data);      l_msg_data := ‘*             Out      Y     --Y的说明‘ || g_newline ||                  ‘*    Update History‘ || g_newline ||                  ‘*    Version    Date         Name            Description‘ ||                  g_newline ||                  ‘*    --------  ----------  ---------------  --------------------‘;      output_msg(l_msg_data);      l_msg_data := ‘*     V1.0                                   Creation    ‘ ||                  g_newline || ‘*‘ || g_newline ||                  ‘  ===============================================================*/‘ ||                  g_newline || ‘PROCEDURE delete_row IS‘;      output_msg(l_msg_data);      l_msg_data := ‘BEGIN‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  DELETE FROM ‘ || p_table_name || g_newline ||                  ‘  WHERE ‘ || p_primary_key || ‘ = ‘ || l_block_name_ext ||                  p_primary_key || ‘;‘ || g_newline;    output_msg(l_msg_data);      l_msg_data := ‘  IF (SQL%NOTFOUND) THEN‘ || g_newline ||                  ‘     RAISE NO_DATA_FOUND;‘ || g_newline || ‘  END IF;‘ ||                  g_newline;    output_msg(l_msg_data);      l_msg_data := ‘END delete_row;‘ || g_newline;    output_msg(l_msg_data);      -- end    l_msg_data := ‘END ‘ || l_package_name || ‘;‘;    output_msg(l_msg_data);    exception    when fnd_api.g_exc_error then      output_msg(l_msg_data);    when others then      output_msg(sqlerrm);  end form_view_iud_p;end xxx_plsql_generator_pkg;/