EBS: FORM窗体开发使用VIEW模式开发，plsql DEVELOPER 自动生成PLSQL脚本

FORM窗体开发使用VIEW模式开发，plsql DEVELOPER 自动生成PLSQL脚本,

CREATE OR REPLACE PACKAGE APPS.HAND_PLSQL_AUTOCREATE AUTHID CURRENT_USER AS
/* $Header: HDPLSATC.pls 115.1 2004/09/02 15:33:09 pkm ship   $ */

  PROCEDURE regist_table( p_table_name IN VARCHAR2,
                          p_owner      IN VARCHAR2,
                          p_appl_short_name IN VARCHAR2 DEFAULT NULL);

  PROCEDURE form_view_iud( p_block_name  IN VARCHAR2,
                           p_table_name  IN VARCHAR2,
                           p_owner       IN VARCHAR2,
                           p_primary_key IN VARCHAR2);

  PROCEDURE table_handle_pkg( p_table_name  IN VARCHAR2,
                              p_owner       IN VARCHAR2,
                              p_primary_key IN VARCHAR2);

  PROCEDURE form_table_handle( p_block_name   IN VARCHAR2,
                               p_package_name IN VARCHAR2,
                               p_table_name   IN VARCHAR2,
                               p_primary_key  IN VARCHAR2);

END HAND_PLSQL_AUTOCREATE;
/

CREATE OR REPLACE PACKAGE BODY APPS.HAND_PLSQL_AUTOCREATE AS
/* $Header: HDPLSATC.pls 115.1 2004/09/02 15:33:09 pkm ship   $ */
/**-----------------------------------------------------------------------------------------
 **   HISTORY:
 **     Date         Author               Description
 **     -----------  -------------------  --------------------------------------------------
 **     2004-09-02   jim.lin              creation
 **------------------------------------------------------------------------------------------- */

  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),
         DATA_TYPE      VARCHAR2(106) );

  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 regist_table( p_table_name IN VARCHAR2,
                          p_owner      IN VARCHAR2,
                          p_appl_short_name IN VARCHAR2 DEFAULT NULL)
  IS
    CURSOR c_table
    IS SELECT t.owner, nvl(t.next_extent,2) next_extent, t.pct_free, t.pct_used
         FROM all_tables t
        WHERE t.table_name = p_table_name
          AND t.owner      = p_owner;

    CURSOR c_columns
    IS SELECT tc.column_id, tc.column_name, tc.data_type,
              decode(tc.data_type, 'NUMBER', 38, 'DATE', 9, tc.char_length) col_width,
              tc.nullable
         FROM all_tab_columns tc
        WHERE tc.table_name = p_table_name
          AND tc.owner      = p_owner
          AND tc.data_type IN ('VARCHAR2','VARCHAR','NCHAR','NVARCHAR2','NUMBER','DATE')
        ORDER BY tc.column_id;

    l_appl_short_name     VARCHAR2(30);
    l_table_rec           c_table%ROWTYPE;

    l_msg_data            VARCHAR2(2000);
  BEGIN
    IF p_appl_short_name IS NULL THEN
       l_appl_short_name := p_owner;
    ELSE
       l_appl_short_name := p_appl_short_name;
    END IF;

    OPEN c_table;
    FETCH c_table INTO l_table_rec;
    IF c_table%NOTFOUND THEN
       CLOSE c_table;
       l_msg_data := 'Table ' || p_table_name || ' not found';
       RAISE fnd_api.g_exc_error;
    END IF;
    CLOSE c_table;

    -- regist table
    l_msg_data := 'EXECUTE AD_DD.REGISTER_TABLE(''' || l_appl_short_name || ''','''
                  || p_table_name || ''',''T'',' || l_table_rec.next_extent || ','
                  || l_table_rec.pct_free || ',' || l_table_rec.pct_used || ');';
    output_msg(l_msg_data);

    -- regist column
    FOR r IN c_columns LOOP
       l_msg_data := 'EXECUTE AD_DD.REGISTER_COLUMN(''' || l_appl_short_name || ''','''
                     || p_table_name || ''',''' || r.column_name || ''',' || r.column_id || ','''
                     || r.data_type || ''',' || r.col_width || ',''' || r.nullable || ''',''N'');';
       output_msg(l_msg_data);
    END LOOP;

  EXCEPTION
     WHEN fnd_api.g_exc_error THEN
         output_msg(l_msg_data);
     WHEN OTHERS THEN
         output_msg(SQLERRM);
  END regist_table;

  PROCEDURE form_view_iud( 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 = p_table_name
          AND t.owner      = p_owner;

    CURSOR c_columns
    IS SELECT tc.column_name, tc.nullable
         FROM all_tab_columns tc
        WHERE tc.table_name = p_table_name
          AND tc.owner      = p_owner
        ORDER BY tc.column_id;

    l_dummy          VARCHAR2(1);
    l_msg_data       VARCHAR2(2000);
    l_package_name   VARCHAR2(100);
    l_block_name_ext VARCHAR2(50) := ':' || p_block_name || '.';
    l_rec_ext        VARCHAR2(30) := 'rec.';

    l_column_tbl     COLUMN_TBL_TYPE;
    l_column_count   NUMBER := 0;

  BEGIN
    -- check table exists
    OPEN c_table;
    FETCH c_table INTO l_dummy;
    IF c_table%NOTFOUND THEN
       CLOSE c_table;
       l_msg_data := 'Table ' || p_table_name || ' not found';
       RAISE fnd_api.g_exc_error;
    END IF;
    CLOSE c_table;
    -- 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';
       RAISE fnd_api.g_exc_error;
    END IF;

    l_package_name := upper(p_block_name) || '_PRIVATE';
    -- generate package special
    l_msg_data := 'PACKAGE ' || l_package_name || ' IS' || g_newline || g_newline ||
                  '  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;
    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;
    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 ||
                  '** PROCEDURE: 	update_row()' || 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';
    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;

  PROCEDURE table_handle_pkg( 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 = p_table_name
          AND t.owner      = p_owner;

    CURSOR c_columns
    IS SELECT tc.column_name, tc.nullable, tc.data_type
         FROM all_tab_columns tc
        WHERE tc.table_name = p_table_name
          AND tc.owner      = p_owner
        ORDER BY tc.column_id;

    l_dummy          VARCHAR2(1);
    l_msg_data       VARCHAR2(2000);
    l_package_name   VARCHAR2(100);
    l_object_version_column  BOOLEAN := FALSE;
    l_primary_key_exists BOOLEAN := FALSE;
    l_rec_ext            VARCHAR2(30);

    l_column_tbl     COLUMN_TBL_TYPE;
    l_column_count   NUMBER := 0;
  BEGIN
    -- check table exists
    OPEN c_table;
    FETCH c_table INTO l_dummy;
    IF c_table%NOTFOUND THEN
       CLOSE c_table;
       l_msg_data := 'Table ' || p_table_name || ' not found';
       RAISE fnd_api.g_exc_error;
    END IF;
    CLOSE c_table;
    -- 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;
       l_column_tbl(l_column_count).data_type := r.data_type;
       IF r.column_name = 'OBJECT_VERSION_NUMBER' THEN
          l_object_version_column := TRUE;
       END IF;
    END LOOP;

    IF l_column_count < 1 THEN
       l_msg_data := 'Not column in table';
       RAISE fnd_api.g_exc_error;
    END IF;

    l_package_name := upper(p_table_name) || '_PKG';
    -- generate package special
    l_msg_data := 'CREATE OR REPLACE PACKAGE ' || l_package_name || ' AS' || g_newline || g_newline;
    output_msg(l_msg_data);
    -- insert row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	insert_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);
    l_msg_data := '  PROCEDURE insert_row('  || g_newline ||
                  '               x_row_id IN OUT VARCHAR2,';
    output_msg(l_msg_data);
    FOR i IN 1..l_column_tbl.COUNT LOOP
       IF l_column_tbl(i).column_name = p_primary_key THEN
          l_msg_data := '               x_' || lower(l_column_tbl(i).column_name) || ' IN OUT ' || l_column_tbl(i).data_type;
       ELSE
          l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
       END IF;
       IF l_column_tbl(i).nullable = 'Y' THEN
         l_msg_data := l_msg_data || ' DEFAULT NULL';
       END IF;
       IF i = l_column_tbl.COUNT THEN
          l_msg_data := l_msg_data || ');' || g_newline;
       ELSE
          l_msg_data := l_msg_data || ',';
       END IF;
       output_msg(l_msg_data);
    END LOOP;

    -- lock row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	lock_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);
    IF l_object_version_column THEN
       l_msg_data := '  PROCEDURE lock_row(';
       output_msg(l_msg_data);
       l_msg_data := '               p_' || lower(p_primary_key) || ' IN NUMBER,' || g_newline ||
                     '               p_object_version_number IN NUMBER);' || g_newline;
       output_msg(l_msg_data);
    ELSE
       l_msg_data := '  PROCEDURE lock_row(';
       output_msg(l_msg_data);
       FOR i IN 1..l_column_tbl.COUNT LOOP
--          IF l_column_tbl(i).column_name IN ('CREATION_DATE','CREATED_BY','LAST_UPDATE_DATE','LAST_UPDATED_BY','LAST_UPDATE_LOGIN') THEN
--            NULL;
--          ELSE
            l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
            IF i = l_column_tbl.COUNT THEN
               l_msg_data := l_msg_data || ');' || g_newline;
            ELSE
               l_msg_data := l_msg_data || ',';
            END IF;
            output_msg(l_msg_data);
--          END IF;
      END LOOP;
    END IF;

    -- update row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	update_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);
    l_msg_data := '  PROCEDURE update_row(';
    output_msg(l_msg_data);
    FOR i IN 1..l_column_tbl.COUNT LOOP
      IF l_column_tbl(i).column_name IN ('CREATION_DATE','CREATED_BY') THEN
        NULL;
      ELSE
       l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
       IF l_column_tbl(i).nullable = 'Y' THEN
         l_msg_data := l_msg_data || ' DEFAULT NULL';
       END IF;
       IF i = l_column_tbl.COUNT THEN
          l_msg_data := l_msg_data || ');' || g_newline;
       ELSE
          l_msg_data := l_msg_data || ',';
       END IF;
       output_msg(l_msg_data);
      END IF;
    END LOOP;

    -- delete row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	delete_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);
    l_msg_data := '  PROCEDURE delete_row(' || g_newline ||
                  '               p_' || lower(p_primary_key) || ' IN NUMBER);' || g_newline || g_newline ||
                  'END ' || l_package_name || ';' || g_newline ||
                  '/' || g_newline;
    output_msg(l_msg_data);

    -- generate package body
    -- begin
    l_msg_data := 'CREATE OR REPLACE PACKAGE BODY ' || l_package_name || ' AS' || g_newline;
    output_msg(l_msg_data);

    -- insert row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	insert_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);

    l_msg_data := '  PROCEDURE insert_row('  || g_newline ||
                  '               x_row_id IN OUT VARCHAR2,';
    output_msg(l_msg_data);
    FOR i IN 1..l_column_tbl.COUNT LOOP
       IF l_column_tbl(i).column_name = p_primary_key THEN
          l_primary_key_exists := TRUE;
          l_msg_data := '               x_' || lower(l_column_tbl(i).column_name) || ' IN OUT ' || l_column_tbl(i).data_type;
       ELSE
          l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
       END IF;
       IF l_column_tbl(i).nullable = 'Y' THEN
         l_msg_data := l_msg_data || ' DEFAULT NULL';
       END IF;
       IF i = l_column_tbl.COUNT THEN
          l_msg_data := l_msg_data || ')' || g_newline;
       ELSE
          l_msg_data := l_msg_data || ',';
       END IF;
       output_msg(l_msg_data);
    END LOOP;

    IF l_primary_key_exists THEN
      l_msg_data := '  IS'  || g_newline ||
                    '     CURSOR c' || g_newline ||
                    '     IS SELECT rowid' || g_newline ||
                    '          FROM ' || p_table_name || g_newline ||
                    '         WHERE ' || p_primary_key || ' = x_' || lower(p_primary_key) || ';' || g_newline;
    ELSE
      l_msg_data := '  IS'  || g_newline ||
                    '     CURSOR c' || g_newline ||
                    '     IS SELECT rowid' || g_newline ||
                    '          FROM ' || p_table_name || g_newline ||
                    '         WHERE ' || p_primary_key || ' = p_' || lower(p_primary_key) || ';' || g_newline;
    END IF;
    output_msg(l_msg_data);

    l_msg_data := '  BEGIN' || g_newline;
    output_msg(l_msg_data);

    IF l_primary_key_exists THEN
      l_msg_data := '    IF x_' || lower(p_primary_key) || ' IS NULL THEN' || g_newline ||
                    '      SELECT ' || p_table_name || '_S.NEXTVAL' || g_newline ||
                    '        INTO x_' || lower(p_primary_key) || g_newline ||
                    '        FROM DUAL;' || g_newline ||
                    '    END IF;' || g_newline;
      output_msg(l_msg_data);
    END IF;

    l_msg_data := '    INSERT INTO ' || lower(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 := '       ' || lower(l_column_tbl(i).column_name) || ')';
       ELSE
          l_msg_data := '       ' || lower(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
          IF l_column_tbl(i).column_name = p_primary_key THEN
            l_msg_data := '       x_' || lower(l_column_tbl(i).column_name) || ');' || g_newline;
          ELSE
            l_msg_data := '       p_' || lower(l_column_tbl(i).column_name) || ');' || g_newline;
          END IF;
       ELSE
          IF l_column_tbl(i).column_name = p_primary_key THEN
            l_msg_data := '       x_' || lower(l_column_tbl(i).column_name) || ',';
          ELSE
            l_msg_data := '       p_' || lower(l_column_tbl(i).column_name) || ',';
          END IF;
       END IF;
       output_msg(l_msg_data);
    END LOOP;

    l_msg_data := '    OPEN c;' || g_newline ||
                  '    FETCH c INTO x_row_id;' || g_newline ||
                  '    IF (c%NOTFOUND) THEN' || g_newline ||
                  '       CLOSE c;' || g_newline ||
                  '       RAISE NO_DATA_FOUND;' || g_newline ||
                  '    END IF;' || g_newline ||
                  '    CLOSE c;' || 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 ||
                  '  **=====================================*/';
    output_msg(l_msg_data);

    IF l_object_version_column THEN
       l_msg_data := '  PROCEDURE lock_row(';
       output_msg(l_msg_data);
       l_msg_data := '               p_' || lower(p_primary_key) || ' IN NUMBER,' || g_newline ||
                     '               p_object_version_number IN NUMBER)' || g_newline;
       output_msg(l_msg_data);
    ELSE
       l_msg_data := '  PROCEDURE lock_row(';
       output_msg(l_msg_data);
       FOR i IN 1..l_column_tbl.COUNT LOOP
--        IF l_column_tbl(i).column_name IN ('CREATION_DATE','CREATED_BY','LAST_UDPATE_DATE','LAST_UPDATED_BY','LAST_UPDATE_LOGIN') THEN
--          NULL;
--        ELSE
          l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
          IF i = l_column_tbl.COUNT THEN
             l_msg_data := l_msg_data || ')' || g_newline;
          ELSE
             l_msg_data := l_msg_data || ',';
          END IF;
          output_msg(l_msg_data);
--         END IF;
       END LOOP;
    END IF;
    l_msg_data := '  IS' || g_newline ||
                  '    CURSOR c' || g_newline ||
                  '    IS SELECT ';
    output_msg(l_msg_data);

    IF l_object_version_column THEN
       l_msg_data := '              object_version_number';
       output_msg(l_msg_data);
    ELSE
       FOR i IN 1..l_column_tbl.COUNT LOOP
          IF i = l_column_tbl.COUNT THEN
            l_msg_data := '              ' || lower(l_column_tbl(i).column_name);
          ELSE
            l_msg_data := '              ' || lower(l_column_tbl(i).column_name) || ',';
          END IF;
          output_msg(l_msg_data);
       END LOOP;
    END IF;

    l_msg_data := '         FROM ' || lower(p_table_name) || g_newline ||
                   '        WHERE ' || lower(p_primary_key) || ' = p_' || lower(p_primary_key) || g_newline ||
                   '        FOR UPDATE OF ' || lower(p_primary_key) || ' NOWAIT;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '    rec     c%rowtype;' || g_newline ||
                  '  BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '      OPEN c;' || g_newline ||
                  '      FETCH c INTO rec;';
    output_msg(l_msg_data);

    l_msg_data := '      IF (c%NOTFOUND) THEN' || g_newline ||
                  '        CLOSE c;' || g_newline ||
                  '        fnd_message.set_name(''FND'',''FORM_RECORD_DELETED'');' || g_newline ||
                  '        app_exception.raise_exception;';
    output_msg(l_msg_data);

    l_msg_data := '      END IF;' || g_newline ||
                  '      CLOSE c;' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '      IF (';
    output_msg(l_msg_data);
    l_rec_ext := 'rec.';

    IF l_object_version_column THEN
      l_msg_data := '              (rec.object_version_number = p_object_version_number)';
      output_msg(l_msg_data);
    ELSE
      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 || lower(l_column_tbl(i).column_name) ||
                          ' = p_' || lower(l_column_tbl(i).column_name) || ')';
         ELSE
            l_msg_data := l_msg_data || '((' || l_rec_ext || lower(l_column_tbl(i).column_name) ||
                          ' = p_' || lower(l_column_tbl(i).column_name) || ') OR' || g_newline ||
                          '               ((' || l_rec_ext || lower(l_column_tbl(i).column_name) || ' IS NULL)' ||
                          ' AND (p_' || lower(l_column_tbl(i).column_name) || ' IS NULL)))';
         END IF;
         output_msg(l_msg_data);
      END LOOP;
    END IF;
    l_msg_data := '      ) THEN' || g_newline ||
                  '        NULL;' || g_newline ||
                  '      ELSE' || g_newline ||
                  '        fnd_message.set_name(''FND'', ''FORM_RECORD_CHANGED'');' || g_newline ||
                  '        app_exception.raise_exception;' || g_newline ||
                  '      END IF;' || 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 ||
                  '  ** PROCEDURE: 	update_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);

    l_msg_data := '  PROCEDURE update_row(';
    output_msg(l_msg_data);
    FOR i IN 1..l_column_tbl.COUNT LOOP
      IF l_column_tbl(i).column_name IN ('CREATION_DATE','CREATED_BY') THEN
        NULL;
      ELSE
       l_msg_data := '               p_' || lower(l_column_tbl(i).column_name) || ' IN ' || l_column_tbl(i).data_type;
       IF l_column_tbl(i).nullable = 'Y' THEN
         l_msg_data := l_msg_data || ' DEFAULT NULL';
       END IF;
       IF i = l_column_tbl.COUNT THEN
          l_msg_data := l_msg_data || ')' || g_newline;
       ELSE
          l_msg_data := l_msg_data || ',';
       END IF;
       output_msg(l_msg_data);
      END IF;
    END LOOP;

    l_msg_data := '  IS' || g_newline ||
                  '  BEGIN' || 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
      IF l_column_tbl(i).column_name IN ('CREATION_DATE','CREATED_BY') THEN
        NULL;
      ELSE
       l_msg_data := '        ' || rpad(lower(l_column_tbl(i).column_name),30) || ' = p_' ||
                     lower(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 IF;
    END LOOP;
    l_msg_data := '    WHERE ' || lower(p_primary_key) || ' = p_' || lower(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 update_row;' || g_newline;
    output_msg(l_msg_data);

    -- delete row
    l_msg_data := '  /*=====================================' || g_newline ||
                  '  ** PROCEDURE: 	delete_row()' || g_newline ||
                  '  **=====================================*/';
    output_msg(l_msg_data);

    l_msg_data := '  PROCEDURE delete_row(' || g_newline ||
                  '               p_' || lower(p_primary_key) || ' IN NUMBER)' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '  IS' || g_newline ||
                  '  BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '    DELETE FROM ' || lower(p_table_name) || g_newline ||
                  '    WHERE ' || lower(p_primary_key) || ' = p_' ||
                  lower(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 || ';' || g_newline ||
                  '/' || g_newline;
    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 table_handle_pkg;

  PROCEDURE form_table_handle( p_block_name   IN VARCHAR2,
                               p_package_name IN VARCHAR2,
                               p_table_name   IN VARCHAR2,
                               p_primary_key  IN VARCHAR2)
  IS
    CURSOR c_columns(p_procedure IN VARCHAR2)
    IS SELECT A.ARGUMENT                            column_name,
           DECODE(A.TYPE#, 252, 'BOOLEAN',
                           12, 'DATE',
                           2, 'NUMBER',
                           1, 'VARCHAR2',
                              'VARCHAR2')           date_type,
           DECODE(A.IN_OUT,1,'OUT',2,'IN OUT','IN') inout
    FROM   SYS.ARGUMENT$ A,
           USER_OBJECTS B
    WHERE  A.OBJ# = B.OBJECT_ID
    AND    B.OBJECT_NAME = p_package_name
    AND    A.PROCEDURE$ = p_procedure
    AND    A.OVERLOAD# = 0
    ORDER BY a.sequence#;

    CURSOR c_object_version_number(p_procedure IN VARCHAR2)
    IS SELECT 'Y'
        FROM   SYS.ARGUMENT$ A,
               USER_OBJECTS B
        WHERE  A.OBJ# = B.OBJECT_ID
        AND    B.OBJECT_NAME = p_package_name
        AND    A.PROCEDURE$ = p_procedure
        AND    A.OVERLOAD# = 0
        AND    A.ARGUMENT  = 'P_OBJECT_VERSION_NUMBER';

    l_msg_data       VARCHAR2(2000);
    l_package_name   VARCHAR2(100);
    l_block_name_ext VARCHAR2(50) := ':' || lower(p_block_name) || '.';

    i                NUMBER;
  BEGIN

    l_package_name := upper(p_block_name) || '_PRIVATE';
    -- generate package special
    l_msg_data := 'PACKAGE ' || l_package_name || ' IS' || g_newline || g_newline ||
                  '  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;
    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 || lower(p_primary_key) || ' IS NULL THEN' || g_newline ||
                  '      SELECT ' || upper(p_table_name) || '_S.NEXTVAL' || g_newline ||
                  '        INTO ' || l_block_name_ext || lower(p_primary_key) || g_newline ||
                  '        FROM DUAL;' || g_newline ||
                  '    END IF;' || g_newline;
    output_msg(l_msg_data);

    FOR r IN c_object_version_number('INSERT_ROW') LOOP
      l_msg_data := '    ' || l_block_name_ext || 'object_version_number := 1;';
      output_msg(l_msg_data);
      EXIT;
    END LOOP;

    l_msg_data := '    ' || lower(p_package_name) || '.insert_row (';
    output_msg(l_msg_data);

    i := 1;
    FOR r IN c_columns('INSERT_ROW') LOOP
       IF i = 1 THEN
          l_msg_data := '        ' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       ELSE
          l_msg_data := '       ,' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       END IF;
       output_msg(l_msg_data);
       i := i + 1;
    END LOOP;
    l_msg_data := '       );' || g_newline ||
                  '  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;
    output_msg(l_msg_data);

    l_msg_data := '    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;';
    output_msg(l_msg_data);

    l_msg_data := '        ' || lower(p_package_name) || '.lock_row(';
    output_msg(l_msg_data);
    i := 1;
    FOR r IN c_columns('LOCK_ROW') LOOP
       IF i = 1 THEN
          l_msg_data := '        ' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       ELSE
          l_msg_data := '       ,' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       END IF;
       output_msg(l_msg_data);
       i := i + 1;
    END LOOP;
    l_msg_data := '       );';
    output_msg(l_msg_data);

    l_msg_data := '        RETURN;' || g_newline ||
                  '      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 ||
                  '  ** PROCEDURE: 	update_row()' || 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);

    FOR r IN c_object_version_number('UPDATE_ROW') LOOP
      l_msg_data := '    ' || l_block_name_ext || 'object_version_number := '|| l_block_name_ext || 'object_version_number' ||' + 1;';
      output_msg(l_msg_data);
      EXIT;
    END LOOP;

    l_msg_data := '    ' || lower(p_package_name) || '.update_row(';
    output_msg(l_msg_data);
    i := 1;
    FOR r IN c_columns('UPDATE_ROW') LOOP
       IF i = 1 THEN
          l_msg_data := '        ' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       ELSE
          l_msg_data := '       ,' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       END IF;
       output_msg(l_msg_data);
       i := i + 1;
    END LOOP;
    l_msg_data := '       );';
    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';
    output_msg(l_msg_data);

    l_msg_data := '  BEGIN' || g_newline;
    output_msg(l_msg_data);

    l_msg_data := '    ' || lower(p_package_name) || '.delete_row(';
    output_msg(l_msg_data);
    i := 1;
    FOR r IN c_columns('DELETE_ROW') LOOP
       IF i = 1 THEN
          l_msg_data := '       ' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       ELSE
          l_msg_data := '       ' || lower(r.column_name) || ' => ' || l_block_name_ext || substr(lower(r.column_name),3);
       END IF;
       output_msg(l_msg_data);
       i := i + 1;
    END LOOP;
    l_msg_data := '       );';
    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_table_handle;

END HAND_PLSQL_AUTOCREATE;
/

/*
EBS Form ： 利用代码自动生成器，设置Form为基于view

1.打开包Hand_plsql_autocreate
2.text：form_view_iud
3.输入4个参数：分别为：数据块的名称
                       数据库对应数据库视图对应的基表
                       数据库表的所有者
                       数据库表的主键
4.执行，查看运行结果：
  到form中建包：包名：数据块名称+_PRIVATE
5.添加块触发器：
  on-insert : 数据块名称+_PRIVATE.insert_row
  on-update : 数据块名称+_PRIVATE.update_row
  on-lock   : 数据块名称+_PRIVATE.lock_row
  on-delete : 数据块名称+_PRIVATE.delete_row --若数据块不允许删除数据，则可以省略此触发器
来源： https://www.cnblogs.com/CiWEi-/archive/2011/12/25/2300855.html   
*/

　注意必须：

1）创建序号程序对象： 命名格式：  表名+ “_S";

2）表字段必须有主键ID， 

3）建议有WHO（5个字段）。