eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.XTR_AUDIT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 15


	select EVENT
	from XTR_AUDIT_GROUPS
	where GROUP_CODE = p_event_group
	and EVENT <> 'AUDIT_GROUP_CODE_ROW';

Line: 21


	select XTR_AUDIT_SUMMARY_S.nextval
	from dual;

Line: 69


  v_select               varchar2(4000);

Line: 81


  v_old_updated_on       date;

Line: 82


  v_old_updated_by       varchar2(30);

Line: 84


  v_new_updated_on       date;

Line: 85


  v_new_updated_by       varchar2(30);

Line: 113


    select table_name,'XTR_A_'||substr(table_name,5)
    from XTR_SETUP_AUDIT_REQMTS
    where event = pc_event;

Line: 118


    select table_column,
	   column_title,
	   upper(nvl(p_key_yn,'N')),
	   upper(column_type)
	   --* Bug#3121210, rravunny
	   --*decode(event,'INTERGROUP TRANSFERS',decode(nvl(P_KEY_YN, 'N'),'Y',decode(table_column,'DEAL_NUMBER',1,'TRANSACTION_NUMBER',1,0),0),0)
    from XTR_AUDIT_COLUMNS
    where event = pc_event
    and ( nvl(audit_yn, 'N') = 'Y' or
	  nvl(P_KEY_YN, 'N') = 'Y' )
    --* Bug#3121210, rravunny
    order by decode(event,'INTERGROUP TRANSFERS',decode(nvl(P_KEY_YN, 'N'),'Y',decode(table_column,'DEAL_NUMBER',1,'TRANSACTION_NUMBER',1,0),0),0) desc
    ;

Line: 152


Existing tables have PRORATE WHO columns like created_by, created_on, updated_by, updated_on.
Newly created tables have AOL wHO columns like created_by, creation_date, last_updated_by, last_update_date.
Due to this inconsistency, we are to go for the following IF, ELSIF, ELSE condition which handled this difference
in WHO column in a different manner.

IF condition covers all the existing table with PRORATE WHO columns.
ELSIF condition covers Xtr_Deals, which is to be handled specially, though it is an existing table.
ELSE condition covers all the newly created table.
*/
  If p_event_name in ('BANK A/C SETUP', 'BANK BALANCES', 'BOND ISSUES SETUP', 'BUY / SELL CURRENCIES',
                      'COMPANY LIMITS', 'COUNTERPARTY LIMITS', 'CURRENCIES SETUP', 'DEAL ORDERS',
                      'DEALER LIMITS', 'EXPOSURE TRANSACTIONS', 'EXPOSURE TYPES', 'GL REFERENCES',
                      'INTERGROUP TRANSFERS', 'JOURNAL STRUCTURE', 'JOURNALS', 'PARTIES',
                      'PARTY DEFAULTS', 'PORTFOLIOS SETUP', 'PRODUCT TYPES', 'RATE SETS', 'REVALUATION DETAIL',
                      'REVALUATION RATES', 'SETTLEMENTS', 'STANDING INSTRUCTIONS', 'SYSTEM PARAMETERS',
                      'TAX/BROKERAGE RATES', 'TAX/BROKERAGE SETUP', 'TERM DEPOSIT/ADVANCE ADJUSTMENTS',
                      'USER CODES SETUP') then
     v_select := 'nvl(UPDATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY, '||
	      'nvl(CREATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';

Line: 172


     v_select := 'nvl(UPDATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY_USER, '||
	      'nvl(CREATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY_USER';

Line: 175


     v_select := 'nvl(LAST_UPDATE_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),LAST_UPDATED_BY, '||
	      'nvl(CREATION_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';

Line: 190


      v_select := v_select || ', to_char('||v_table_column||',''DD/MM/YYYY HH24:MI:SS'')';

Line: 192


      v_select := v_select || ',' || v_table_column;

Line: 194


      v_select := v_select || ',to_char(' || v_table_column || ')';

Line: 208


Existing tables have PRORATE WHO columns like created_by, created_on, updated_by, updated_on.
Newly created tables have AOL wHO columns like created_by, creation_date, last_updated_by, last_update_date.
Due to this inconsistency, we are to go for the following IF, ELSIF, ELSE condition which handled this difference
in WHO column in a different manner.

IF condition covers all the existing table with PRORATE WHO columns.
ELSIF condition covers Xtr_Deals, which is to be handled specially, though it is an existing table.
ELSE condition covers all the newly created table.
*/

 If p_event_name in ('BANK A/C SETUP', 'BANK BALANCES', 'BOND ISSUES SETUP', 'BUY / SELL CURRENCIES',
                      'COMPANY LIMITS', 'COUNTERPARTY LIMITS', 'CURRENCIES SETUP', 'DEAL ORDERS',
                      'DEALER LIMITS', 'EXPOSURE TRANSACTIONS', 'EXPOSURE TYPES', 'GL REFERENCES',
                      'INTERGROUP TRANSFERS', 'JOURNAL STRUCTURE', 'JOURNALS', 'PARTIES',
                      'PARTY DEFAULTS', 'PORTFOLIOS SETUP', 'PRODUCT TYPES', 'RATE SETS', 'REVALUATION DETAIL',
                      'REVALUATION RATES', 'SETTLEMENTS', 'STANDING INSTRUCTIONS', 'SYSTEM PARAMETERS',
                      'TAX/BROKERAGE RATES', 'TAX/BROKERAGE SETUP', 'TERM DEPOSIT/ADVANCE ADJUSTMENTS',
                      'USER CODES SETUP') then
    v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
           'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
           '(created_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS''))  UNION ';

Line: 233


 	   'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
           'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
           '(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';

Line: 239


    v_sql := 'select ''A'',' || v_select ||' from '||'XTR_A_ALL_CONTRACTS_V'||' '||
           'WHERE (updated_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
           '(created_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';

Line: 245


    v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
           'WHERE (LAST_UPDATE_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
           '(CREATION_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS''))  UNION ';

Line: 252


 	   'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
           'WHERE (LAST_UPDATE_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
           '(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
           'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';

Line: 265


  v_sql := v_sql||'1,2,3'; -- Add B/A, "updated_on, updated_by" to SORT BY clause

Line: 273


  dbms_sql.define_column(v_cursor,2,v_new_updated_on);

Line: 274


  dbms_sql.define_column(v_cursor,3,v_new_updated_by,30);

Line: 297


    dbms_sql.column_value(v_cursor,2,v_new_updated_on);

Line: 298


    dbms_sql.column_value(v_cursor,3,v_new_updated_by);

Line: 332


            	insert into XTR_AUDIT_SUMMARY(
			AUDIT_REQUESTED_BY,
			AUDIT_REQUEST_ID,
			AUDIT_REQUESTED_ON,
                 	AUDIT_RECORDS_FROM,
			AUDIT_RECORDS_TO,
                 	NAME_OF_COLUMN_CHANGED,
			TABLE_NAME,
                 	REFERENCE_CODE,
			ACTION_CODE,
			UPDATED_ON_DATE,
                 	UPDATED_BY_USER,
			OLD_VALUE,
			NEW_VALUE,
			TRANSACTION_REF,
                 	NON_TRANSACTION_REF)
            	values
                	(p_audit_requested_by,
			p_audit_request_id,
			sysdate,
                 	to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
                 	to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
                 	rtrim(v_col_title(v_counter)),
			upper(v_table_name),
                 	rtrim(substr(v_reference_code,1,20)),
			'UPDATE',
                 	to_char(v_new_updated_on,'DD/MM/YYYY HH24:MI:SS'),
                 	substr(v_new_updated_by,1,10),
			rtrim(substr(v_old(v_counter),1,255)),
                 	rtrim(substr(v_new(v_counter),1,255)),
			null,
			v_new_letter);

Line: 367


    ELSE -- Insert row for auditing new creation

	IF v_new_created_on between
	    to_date(p_date_from, 'DD/MM/YYYY HH24:MI:SS') and
	    to_date(p_date_to  , 'DD/MM/YYYY HH24:MI:SS')  THEN
            	insert into XTR_AUDIT_SUMMARY(
			AUDIT_REQUESTED_BY,
			AUDIT_REQUEST_ID,
			AUDIT_REQUESTED_ON,
                 	AUDIT_RECORDS_FROM,
			AUDIT_RECORDS_TO,
                 	NAME_OF_COLUMN_CHANGED,
			TABLE_NAME,
                 	REFERENCE_CODE,
			ACTION_CODE,
			UPDATED_ON_DATE,
                 	UPDATED_BY_USER,
			OLD_VALUE,
			NEW_VALUE,
			TRANSACTION_REF,
                 	NON_TRANSACTION_REF)
            	values
                	(p_audit_requested_by,
			p_audit_request_id,
			sysdate,
                 	to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
                 	to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
                 	null,  	--rtrim(v_col_title(v_counter)),
			upper(v_table_name),
                 	rtrim(substr(v_reference_code,1,20)),
			'INSERT',
                 	to_char(v_new_created_on,'DD/MM/YYYY HH24:MI:SS'),
                 	substr(v_new_created_by,1,10),
			null,   	--rtrim(substr(v_old(v_counter),1,255)),
                 	null,		--rtrim(substr(v_new(v_counter),1,255)),
			null,
			v_new_letter);

Line: 411


    v_old_updated_on := v_new_updated_on;

Line: 412


    v_old_updated_by := v_new_updated_by;

Line: 431


       		insert into XTR_AUDIT_SUMMARY(
			AUDIT_REQUESTED_BY,
			AUDIT_REQUEST_ID,
			AUDIT_REQUESTED_ON,
                 	AUDIT_RECORDS_FROM,
			AUDIT_RECORDS_TO,
                 	NAME_OF_COLUMN_CHANGED,
			TABLE_NAME,
                 	REFERENCE_CODE,
			ACTION_CODE,
			UPDATED_ON_DATE,
                 	UPDATED_BY_USER,
			OLD_VALUE,
			NEW_VALUE,
			TRANSACTION_REF,
                 	NON_TRANSACTION_REF)
		select  p_audit_requested_by,
			p_audit_request_id,
			sysdate,
                 	to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
                 	to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
			null,
			'XTR_TERM_ACTIONS',
			to_char(DEAL_NO) ||'|'||INCREASE_EFFECTIVE_FROM_DATE,
			'PRINCIPAL',
			CREATED_ON,
			CREATED_BY,
			null,
			to_char(PRINCIPAL_ADJUST),
			null,
			null
		from 	XTR_TERM_ACTIONS
		where   (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
					to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
		and     INCREASE_EFFECTIVE_FROM_DATE is not null
		and     PRINCIPAL_ADJUST is not null
	UNION
		select 	p_audit_requested_by,
			p_audit_request_id,
			sysdate,
			to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
			to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
			null,
			'XTR_TERM_ACTIONS',
			to_char(DEAL_NO) ||'|'||EFFECTIVE_FROM_DATE,
			'INTEREST',
			CREATED_ON,
			CREATED_BY,
			null,
			to_char(NEW_INTEREST_RATE),
			null,
			null
		from 	XTR_TERM_ACTIONS
		where   (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
					to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
		and     EFFECTIVE_FROM_DATE is not null
		and 	NEW_INTEREST_RATE is not null
	UNION
		select 	p_audit_requested_by,
			p_audit_request_id,
			sysdate,
			to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
			to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
			null,
			'XTR_TERM_ACTIONS',
			to_char(DEAL_NO) ||'|'||FROM_START_DATE ,
			'SCHEDULE',
			CREATED_ON,
			CREATED_BY,
			null,
			PAYMENT_SCHEDULE_CODE ,
			null,
			null
		from 	XTR_TERM_ACTIONS
		where   (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
					to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
		and 	FROM_START_DATE is not null
		and 	PAYMENT_SCHEDULE_CODE is not null;