[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.column_name,
a.column_id,
a.column_type,
a.width
from fnd_columns a,
fnd_audit_columns b,
fnd_tables c
where b.table_id = a.table_id
and b.table_id = c.table_id
and c.table_name = P_TABLE
and b.column_id = a.column_id
and b.table_app_id = a.application_id
and b.schema_id <> -1
and a.column_name <> P_PRIMARY
and a.column_name <> 'EFFECTIVE_START_DATE'
and a.column_name <> 'EFFECTIVE_END_DATE'
order by sequence_id;
select 1
from dual
where exists (
select 1
from all_tables tab
where tab.table_name = p_table_name
and tab.owner = p_oracle_schema
);
select fa.application_short_name
into l_appl_short_name
from fnd_application fa,
fnd_tables ft
where ft.table_name = P_TABLE
and fa.application_id = ft.application_id;
dt_text5:= 'cursor dt_curs is select ' ||
'{DT_TABLE}_TT(audit_session_id,' ||
'audit_commit_id,' ||
'{DT_PRIMARY}),' ||
'audit_timestamp,' ||
'audit_transaction_type,' ||
'audit_user_name,' ||
'audit_session_id,' ||
'audit_commit_id,' ||
'audit_sequence_id,' ||
'audit_true_nulls,' ||
'{DT_PRIMARY},' ||
'effective_start_date,' ||
'effective_end_date ';
'insert into hr_audit_columns' ||
'(audit_id,column_id,column_name,old_value,new_value)' ||
'values(hr_audits_s.currval,' ||
'l_cid,' ||
'l_cn,' ||
'lov,lnv);' ||
'select hr_audits_s.nextval into ' ||
'l_audit from dual;' ||
dt_text10:= 'if l_trans=''FIRST_INSERT'' then ' ||
'do_ins:=true;do_comp:=true;' ||
'elsif l_trans in(''UPDATE'',''UPDATE_CHANGE_INSERT'')then '||
'if l_type=''I'' then ' ||
'ict:=ict+1;' ||
/* Bug 5277170 logic changes for 'Delete Next' : The if condition (dct>1) was changed
to (dct>0) thus eliminating the need for the if condition (dct=1). This change will
affect all the Delete transactions to appear on the Audit Report with appripriate old and
new dates. This logic holds good for 'Future Change' transaction also. */
'elsif l_trans in(''DELETE_NEXT_CHANGE'',''FUTURE_CHANGE'') then ' ||
'if l_type =''I'' then ' ||
'ict:=ict+1;' ||
/* Bug 5277170 logic changes for 'Delete'(End Date) : In 'I' transaction type, t_sd and t_ed
are being initialised to make sure appropriate new start and end dates appear in the
audit report. The n_sd and n_ed need not be intialised in 'I' type as they are being assigned
t_sd and t_ed in 'D' type. Hence two statements have been commented. */
'elsif l_trans=''DELETE'' then ' ||
'if l_type= ''I'' then ' ||
'ict:=ict+1;' ||
/* Bug 5277170 logic changes for 'Update Override' : After 'I' type, 'TABLE_NAME'_VP procedure
is called to update the new values of all the audit columns to which n_sd and n_ed are sent as
parameters. In the two 'I' type transactions for 'Update Override', n_sd and n_ed were null before.
Now, they are being assigned appropriate values so that all the audit columns' values are updated.
Also, do_comp is assigned 'false' in the if (l_sd=t_sd) condition in 'D' type. */
'elsif l_trans=''UPDATE_OVERRIDE'' then ' ||
'if l_type=''I'' then ' ||
'ict:=ict+1;' ||
'insert into hr_audits ' ||
'(audit_id,commit_id,current_session_id,primary_key,' ||
'primary_key_value,sequence_id,session_id,table_name,' ||
'timestamp,transaction,transaction_type,user_name,' ||
'effective_end_date,effective_start_date)' ||
'values ' ||
'(hr_audits_s.nextval,l_comm,''' || to_char(p_session_id) ||
''',''' || p_primary || ''',l_pkval, l_seq, l_sess' ||
',''' || p_table || ''',l_tstamp,l_trans,l_type,l_uname,' ||
'l_ed,l_sd);' ||
ndt_text4 := 'cursor ndt_curs is select ' ||
'audit_timestamp,' ||
'audit_transaction_type,' ||
'audit_user_name,' ||
'audit_true_nulls,' ||
'audit_session_id,' ||
'audit_commit_id,' ||
'audit_sequence_id,' ||
'{NDT_PRIMARY} ';
'insert into hr_audit_columns ' ||
'(audit_id,column_id,column_name,old_value,new_value)' ||
'values(hr_audits_s.currval,' ||
'l_c_id,' ||
'l_c_name,' ||
'o_val,n_val);' ||
ndt_text10:= ' cursor base_curs is select ';
'select hr_audits_s.nextval into ' ||
'l_audit from dual;' ||
'l_trans :=''NORMAL_INSERT'';' ||
'l_trans :=''NORMAL_UPDATE'';' ||
'l_trans :=''NORMAL_DELETE'';' ||
'insert into hr_audits ' ||
'(audit_id,commit_id,current_session_id,primary_key,' ||
'primary_key_value,sequence_id,session_id,table_name,' ||
'timestamp,transaction,transaction_type,user_name,' ||
'effective_end_date,effective_start_date)' ||
'values ' ||
'(hr_audits_s.nextval,l_comm,''' || to_char(p_session_id) ||
''',''' || p_primary || ''',l_pkval, l_seq, l_sess' ||
',''' || p_table || ''',l_tstamp,l_trans,l_type,l_uname,' ||
'null,null);' ||