The following lines contain the word 'select', 'insert', 'update' or 'delete':
select from_clause, where_clause
from fnd_audit_disp_cols, fnd_tables
where table_name = p_table_name and
fnd_tables.table_id = fnd_audit_disp_cols.table_id;
audit_sql_stmt := ' select distinct FND_TABLES.TABLE_NAME TABLE_NAME ,
FND_TABLES.USER_TABLE_NAME USER_TABLE_NAME ,
FND_TABLES.TABLE_ID TABLE_ID ,
FND_TABLES.APPLICATION_ID TABLE_APPLICATION_ID
from FND_AUDIT_GROUPS ,
FND_AUDIT_TMPLT_DTL ,
FND_AUDIT_TABLES ,
FND_TABLES
WHERE FND_AUDIT_TMPLT_DTL.AUDIT_GROUP_ID = FND_AUDIT_GROUPS.AUDIT_GROUP_ID
AND FND_AUDIT_TMPLT_DTL.APPLICATION_ID = FND_AUDIT_GROUPS.APPLICATION_ID
AND FND_AUDIT_GROUPS.APPLICATION_ID = FND_AUDIT_TABLES.AUDIT_GROUP_APP_ID
AND FND_AUDIT_GROUPS.AUDIT_GROUP_ID = FND_AUDIT_TABLES.AUDIT_GROUP_ID
AND FND_AUDIT_TABLES.TABLE_ID = FND_TABLES.TABLE_ID
AND FND_AUDIT_TABLES.TABLE_APP_ID = FND_TABLES.APPLICATION_ID
AND FND_AUDIT_GROUPS.STATE in (''E'',''G'',''N'')
AND FND_AUDIT_TABLES.STATE in (''E'',''G'',''N'')
AND '|| AUDIT_WHERE_CLAUSE;
SELECT 1 into shadow_table_exists
FROM tab
where tname like substrb(AUDIT_TABLE_REC.TABLE_NAME,1,24)||'_AC1';
table_sql_stmt := ' SELECT 1 DATA_EXISTS_FLAG '||
' FROM '||substrb(AUDIT_TABLE_REC.TABLE_NAME,1,24)||'_AC1 ' || disp_from_clause ||
' WHERE ' || TABLE_WHERE_CLAUSE || disp_where_clause;
PROCEDURE POPULATE_TAB_REP_DATA(P_SELECT_CLAUSE VARCHAR2,
P_TABLE_NAME VARCHAR2,
P_USER_TABLE_NAME VARCHAR2,
P_WHERE_CLAUSE VARCHAR2,
P_APPLICATION_ID NUMBER,
P_TABLE_ID NUMBER,
P_REP_ID NUMBER) IS
-- Local Variables
TYPE RETRIEVE_DATA IS REF CURSOR;
data_select_stmt VARCHAR2(4000) := null;
v_select_stmt VARCHAR2(4000) := null;
v_select_stmt1 VARCHAR2(4000) := null;
SELECT COL_DISP_IND ,
SELECT_CLAUSE,
FROM_CLAUSE,
WHERE_CLAUSE
FROM FND_AUDIT_DISP_COLS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND TABLE_ID = P_TABLE_ID;
SELECT fnd_cols.column_name column_name
FROM FND_COLUMNS fnd_cols,
FND_PRIMARY_KEYS pks,
FND_PRIMARY_KEY_COLUMNS keycols
WHERE pks.application_id = keycols.application_id
AND pks.table_id = keycols.table_id
AND pks.primary_key_id = keycols.primary_key_id
AND keycols.application_id = fnd_cols.application_id
AND keycols.table_id = fnd_cols.table_id
AND keycols.column_id = fnd_cols.column_id
AND pks.table_id = P_TABLE_ID
AND pks.application_id = P_APPLICATION_ID
AND pks.audit_key_flag = 'Y';
IF missing_base_table_flag = 0 AND User_key_columns_rec.select_clause IS NOT NULL THEN
user_column_list := replace(User_key_columns_rec.select_clause,',','||'||''''||','||''''||'||');
v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
user_column_list || ' disp_val ' ;
v_select_stmt := v_select_stmt || ' FROM ' ||User_key_columns_rec.FROM_CLAUSE;
v_select_stmt := v_select_stmt || ' FROM '|| p_table_name;
v_select_stmt := v_select_stmt ||
' WHERE '||User_key_columns_rec.where_clause;
v_select_stmt := v_select_stmt ||
' WHERE 1=1 ';
v_select_stmt := ' SELECT '||pk_column_list || ' ID, '||
pk_column_list || ' disp_val ' ||
' FROM '|| p_table_name ||
' WHERE 1=1 ';
data_select_stmt := ' SELECT '||l_where_clause || ' AUDIT_KEY,AUDIT_TIMESTAMP,AUDIT_TRANSACTION_TYPE,AUDIT_USER_NAME,'
|| P_SELECT_CLAUSE
|| ' FROM '||substrb(P_TABLE_NAME,1,24)||'_AC1'
|| ' WHERE '|| P_WHERE_CLAUSE
|| ' order by '||l_where_clause;
OPEN AUDIT_DATA FOR data_select_stmt;
v_select_stmt1 := v_select_stmt || ' AND '||pk_column_list ||' = :auditkey';
OPEN DISP_COL_VALUE FOR v_select_stmt1 USING AUDIT_DATA_REC.AUDIT_KEY;
INSERT INTO FND_AUDIT_REP_DTL(REP_ID
,TABLE_NAME
,AUDIT_KEY
,AUDIT_TIMESTAMP
,AUDIT_TRANSACTION_TYPE
,AUDIT_USER_NAME
,COLUMN1_VALUE
,COLUMN2_VALUE
,COLUMN3_VALUE
,COLUMN4_VALUE
,COLUMN5_VALUE
,ROW_DISP_COL)
VALUES
(P_REP_ID
,P_TABLE_NAME
,substrb(AUDIT_DATA_REC.AUDIT_KEY,1,240)
,AUDIT_DATA_REC.AUDIT_TIMESTAMP
,AUDIT_DATA_REC.AUDIT_TRANSACTION_TYPE
,AUDIT_DATA_REC.AUDIT_USER_NAME
,substrb(AUDIT_DATA_REC.COLUMN1_VALUE,1,240)
,substrb(AUDIT_DATA_REC.COLUMN2_VALUE,1,240)
,substrb(AUDIT_DATA_REC.COLUMN3_VALUE,1,240)
,substrb(AUDIT_DATA_REC.COLUMN4_VALUE,1,240)
,substrb(AUDIT_DATA_REC.COLUMN5_VALUE,1,240)
,substrb(DISP_VAL,1,240));