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个字段）。
优质生活从拆开始
浙公网安备 33010602011771号