[Home] [Help]
PACKAGE BODY: APPS.XTR_AUDIT
Source
1 PACKAGE BODY XTR_AUDIT AS
2 /* $Header: xtraudtb.pls 120.5.12010000.2 2009/12/02 09:52:36 nipant ship $ */
3
4
5
6 PROCEDURE XTR_AUDIT_REPORT(
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY VARCHAR2,
9 p_event_group VARCHAR2,
10 p_audit_from_date VARCHAR2,
11 p_audit_to_date VARCHAR2)
12 IS
13
14 cursor AUDIT_EVENTS is
15 select EVENT
16 from XTR_AUDIT_GROUPS
17 where GROUP_CODE = p_event_group
18 and EVENT <> 'AUDIT_GROUP_CODE_ROW';
19
20 cursor NEW_REQUEST is
21 select XTR_AUDIT_SUMMARY_S.nextval
22 from dual;
23
24
25 audit_requestion_id NUMBER := 999;
26
27 l_from_date DATE := to_date(p_audit_from_date, 'YYYY/MM/DD HH24:MI:SS');
28 l_to_date DATE := to_date(p_audit_to_date, 'YYYY/MM/DD HH24:MI:SS');
29 l_date_from VARCHAR2(25) := to_char(l_from_date, 'DD/MM/YYYY HH24:MI:SS');
30 l_date_to VARCHAR2(25) := to_char(l_to_date, 'DD/MM/YYYY HH24:MI:SS');
31
32 BEGIN
33
34 open NEW_REQUEST;
35 fetch NEW_REQUEST into audit_requestion_id;
36 close NEW_REQUEST;
37
38 FOR event_cur in AUDIT_EVENTS LOOP
39 --dbms_output.put_line('event = '|| event_cur.EVENT);
40 if event_cur.EVENT <> 'TERM DEPOSIT/ADVANCE ADJUSTMENTS' then
41 XTR_AUDIT_RETRIEVE( to_char(fnd_global.user_id),
42 audit_requestion_id,
43 event_cur.EVENT,
44 l_date_from,
45 l_date_to );
46 else
47 XTR_TERM_ACTIONS_RETRIEVE(to_char(audit_requestion_id),
48 audit_requestion_id,
49 event_cur.EVENT,
50 l_date_from,
51 l_date_to );
52 end if;
53 END LOOP;
54
55 SUBMIT_AUDIT_REPORT(to_char(audit_requestion_id),p_event_group, p_audit_from_date, p_audit_to_date );
56
57 END XTR_AUDIT_REPORT;
58
59 PROCEDURE XTR_AUDIT_RETRIEVE(p_audit_requested_by IN VARCHAR2,
60 p_audit_request_id IN NUMBER,
61 p_event_name IN VARCHAR2,
62 p_date_from IN VARCHAR2,
63 p_date_to IN VARCHAR2) is
64
65 native constant integer := 1;
66 V_MAX_COL constant integer := 60;
67 v_counter binary_integer;
68 v_table_column xtr_audit_columns_v.table_column%TYPE;
69 v_select varchar2(4000);
70 v_num_col integer;
71 v_sql varchar2(4000);
72 v_rec_num binary_integer := 1;
73 v_key_the_same varchar2(1);
74 v_reference_code varchar2(50);
75 v_table_name varchar2(50);
76 v_audit_table_name varchar2(50);
77 ex_error exception;
78 v_cursor binary_integer;
79 v_rows_processed binary_integer;
80 v_old_letter varchar2(1);
81 v_old_updated_on date;
82 v_old_updated_by varchar2(30);
83 v_new_letter varchar2(1);
84 v_new_updated_on date;
85 v_new_updated_by varchar2(30);
86 v_new_created_on date;
87 v_new_created_by varchar2(30);
88
89 v_var1 varchar2(255);
90
91 TYPE t_col_title IS TABLE OF VARCHAR2(50)
92 INDEX BY BINARY_INTEGER;
93
94 TYPE t_col_type IS TABLE OF VARCHAR2(15)
95 INDEX BY BINARY_INTEGER;
96
97 TYPE t_col_pkey IS TABLE OF VARCHAR2(1)
98 INDEX BY BINARY_INTEGER;
99
100 TYPE t_old IS TABLE OF VARCHAR2(255)
101 INDEX BY BINARY_INTEGER;
102
103 TYPE t_new IS TABLE OF VARCHAR2(255)
104 INDEX BY BINARY_INTEGER;
105
106 v_col_title t_col_title;
107 v_col_type t_col_type;
108 v_col_pkey t_col_pkey;
109 v_old t_old; -- Holds old fetched columns
110 v_new t_new; -- Holds newly fetched records columns
111 --
112 cursor c_get_table_name (pc_event varchar2) is
113 select table_name,'XTR_A_'||substr(table_name,5)
114 from XTR_SETUP_AUDIT_REQMTS
115 where event = pc_event;
116 --
117 cursor c_get_columns ( pc_event varchar2 )is
118 select table_column,
119 column_title,
120 upper(nvl(p_key_yn,'N')),
121 upper(column_type)
122 --* Bug#3121210, rravunny
123 --*decode(event,'INTERGROUP TRANSFERS',decode(nvl(P_KEY_YN, 'N'),'Y',decode(table_column,'DEAL_NUMBER',1,'TRANSACTION_NUMBER',1,0),0),0)
124 from XTR_AUDIT_COLUMNS
125 where event = pc_event
126 and ( nvl(audit_yn, 'N') = 'Y' or
127 nvl(P_KEY_YN, 'N') = 'Y' )
128 --* Bug#3121210, rravunny
129 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
130 ;
131
132 begin
133 --
134 -- Get the table name for audit
135 --
136 open c_get_table_name(p_event_name);
137 fetch c_get_table_name into v_table_name,v_audit_table_name;
138 IF c_get_table_name%NOTFOUND THEN
139 close c_get_table_name;
140 raise ex_error;
141 ELSE
142 close c_get_table_name;
143 END IF;
144
145 --
146 -- Build select clause
147 --
148 open c_get_columns ( p_event_name );
149 /*
150 code below modified by Ilavenil to support audit feature for both new and existing table sin patchset F
151
152 Existing tables have PRORATE WHO columns like created_by, created_on, updated_by, updated_on.
153 Newly created tables have AOL wHO columns like created_by, creation_date, last_updated_by, last_update_date.
154 Due to this inconsistency, we are to go for the following IF, ELSIF, ELSE condition which handled this difference
155 in WHO column in a different manner.
156
157 IF condition covers all the existing table with PRORATE WHO columns.
158 ELSIF condition covers Xtr_Deals, which is to be handled specially, though it is an existing table.
159 ELSE condition covers all the newly created table.
160 */
161 If p_event_name in ('BANK A/C SETUP', 'BANK BALANCES', 'BOND ISSUES SETUP', 'BUY / SELL CURRENCIES',
162 'COMPANY LIMITS', 'COUNTERPARTY LIMITS', 'CURRENCIES SETUP', 'DEAL ORDERS',
163 'DEALER LIMITS', 'EXPOSURE TRANSACTIONS', 'EXPOSURE TYPES', 'GL REFERENCES',
164 'INTERGROUP TRANSFERS', 'JOURNAL STRUCTURE', 'JOURNALS', 'PARTIES',
165 'PARTY DEFAULTS', 'PORTFOLIOS SETUP', 'PRODUCT TYPES', 'RATE SETS', 'REVALUATION DETAIL',
166 'REVALUATION RATES', 'SETTLEMENTS', 'STANDING INSTRUCTIONS',
167 'TAX/BROKERAGE RATES', 'TAX/BROKERAGE SETUP', 'TERM DEPOSIT/ADVANCE ADJUSTMENTS',
168 'USER CODES SETUP') then
169 v_select := 'nvl(UPDATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY, '||
170 'nvl(CREATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';
171
172 --BUG 9049453 starts
173 Elsif p_event_name = 'SYSTEM PARAMETERS' then
174 v_select := 'nvl(UPDATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY, '||
175 'nvl(to_date(CREATED_ON) ,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';
176 --BUG 9049453 ends
177
178 Elsif p_event_name = 'TRANSACTIONS' then
179 v_select := 'nvl(UPDATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY_USER, '||
180 'nvl(CREATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY_USER';
181 Else
182 v_select := 'nvl(LAST_UPDATE_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),LAST_UPDATED_BY, '||
183 'nvl(CREATION_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';
184 End if;
185
186
187 v_counter := 1;
188 LOOP
189 EXIT WHEN v_counter > V_MAX_COL;
190 fetch c_get_columns into v_table_column,
191 v_col_title( v_counter),
192 v_col_pkey( v_counter),
193 v_col_type( v_counter);
194 EXIT WHEN c_get_columns%NOTFOUND;
195
196 IF substr(v_col_type(v_counter),1,4) = 'DATE' THEN
197 v_select := v_select || ', to_char('||v_table_column||',''DD/MM/YYYY HH24:MI:SS'')';
198 ELSIF substr(v_col_type(v_counter),1,4) in ('CHAR','VARC') THEN
199 v_select := v_select || ',' || v_table_column;
200 ELSE
201 v_select := v_select || ',to_char(' || v_table_column || ')';
202 END IF;
203
204 v_counter := v_counter + 1;
205 END LOOP;
206 close c_get_columns;
207 v_num_col := v_counter -1;
208
209 --
210 -- Put all of SQL statement together (ie select + where clause)
211 --
212 /*
213 code below modified by Ilavenil to support audit feature for both new and existing table sin patchset F
214
215 Existing tables have PRORATE WHO columns like created_by, created_on, updated_by, updated_on.
216 Newly created tables have AOL wHO columns like created_by, creation_date, last_updated_by, last_update_date.
217 Due to this inconsistency, we are to go for the following IF, ELSIF, ELSE condition which handled this difference
218 in WHO column in a different manner.
219
220 IF condition covers all the existing table with PRORATE WHO columns.
221 ELSIF condition covers Xtr_Deals, which is to be handled specially, though it is an existing table.
222 ELSE condition covers all the newly created table.
223 */
224
225 If p_event_name in ('BANK A/C SETUP', 'BANK BALANCES', 'BOND ISSUES SETUP', 'BUY / SELL CURRENCIES',
226 'COMPANY LIMITS', 'COUNTERPARTY LIMITS', 'CURRENCIES SETUP', 'DEAL ORDERS',
227 'DEALER LIMITS', 'EXPOSURE TRANSACTIONS', 'EXPOSURE TYPES', 'GL REFERENCES',
228 'INTERGROUP TRANSFERS', 'JOURNAL STRUCTURE', 'JOURNALS', 'PARTIES',
229 'PARTY DEFAULTS', 'PORTFOLIOS SETUP', 'PRODUCT TYPES', 'RATE SETS', 'REVALUATION DETAIL',
230 'REVALUATION RATES', 'SETTLEMENTS', 'STANDING INSTRUCTIONS', 'SYSTEM PARAMETERS',
231 'TAX/BROKERAGE RATES', 'TAX/BROKERAGE SETUP', 'TERM DEPOSIT/ADVANCE ADJUSTMENTS',
232 'USER CODES SETUP') then
233 v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
234 'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
235 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
236 '(created_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
237 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) UNION ';
238
239 v_sql := v_sql ||
240 'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
241 'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
242 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
243 '(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
244 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
245 Elsif p_event_name = 'TRANSACTIONS' then
246 v_sql := 'select ''A'',' || v_select ||' from '||'XTR_A_ALL_CONTRACTS_V'||' '||
247 'WHERE (updated_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
248 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
249 '(created_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
250 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
251 Else
252 v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
253 'WHERE (LAST_UPDATE_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
254 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
255 '(CREATION_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
256 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) UNION ';
257
258 v_sql := v_sql ||
259 'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
260 'WHERE (LAST_UPDATE_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
261 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
262 '(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
263 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
264 End if;
265
266 -- Add the primary key column/s to the SORT BY clause
267 FOR v_counter IN 1..v_num_col LOOP
268 IF v_col_pkey(v_counter) = 'Y' THEN
269 v_sql := v_sql || to_char(v_counter+5)||',';
270 END IF;
271 END LOOP;
272 v_sql := v_sql||'1,2,3'; -- Add B/A, "updated_on, updated_by" to SORT BY clause
273
274 --
275 -- Now set up dbms_sql cursor
276 --
277 v_cursor := dbms_sql.open_cursor;
278 dbms_sql.parse(v_cursor,v_sql,native);
279 dbms_sql.define_column(v_cursor,1,v_new_letter, 1);
280 dbms_sql.define_column(v_cursor,2,v_new_updated_on);
281 dbms_sql.define_column(v_cursor,3,v_new_updated_by,30);
282 dbms_sql.define_column(v_cursor,4,v_new_created_on);
283 dbms_sql.define_column(v_cursor,5,v_new_created_by,30);
284
285 -- Its weird how come this next bit works !!??
286 FOR v_counter IN 1..v_num_col LOOP
287 dbms_sql.define_column( v_cursor, v_counter+5, v_var1, 100);
288 END LOOP;
289
290 v_rows_processed := dbms_sql.execute( v_cursor );
291
292 --
293 -- Now loop through records in cursor.
294 --
295 v_rec_num := 1;
296 FOR v_counter in 1..v_num_col LOOP
297 v_old(v_counter) := 'XX';
298 END LOOP;
299
300 LOOP
301
302 EXIT WHEN dbms_sql.fetch_rows(v_cursor) < 1;
303 dbms_sql.column_value(v_cursor,1,v_new_letter);
304 dbms_sql.column_value(v_cursor,2,v_new_updated_on);
305 dbms_sql.column_value(v_cursor,3,v_new_updated_by);
306 dbms_sql.column_value(v_cursor,4,v_new_created_on);
307 dbms_sql.column_value(v_cursor,5,v_new_created_by);
308 FOR v_counter IN 1..v_num_col LOOP
309 dbms_sql.column_value(v_cursor,v_counter + 5,v_var1);
310 v_new(v_counter) := v_var1;
311 END LOOP;
312
313 --
314 -- See if primary keys MATCH
315 --
316 v_key_the_same := 'Y';
317 v_reference_code := null;
318 FOR v_counter IN 1..v_num_col LOOP
319 IF v_col_pkey(v_counter) = 'Y' THEN
320 IF v_reference_code is not null and v_new( v_counter ) is not null THEN
321 v_reference_code := v_reference_code||'|';
322 END IF;
323 v_reference_code := v_reference_code||rtrim(v_new( v_counter ));
324 IF nvl(v_old(v_counter),'JJ') <> nvl(v_new(v_counter),'JJ') THEN
325 v_key_the_same := 'N';
326 END IF;
327 END IF;
328 END LOOP;
329
330 --fnd_message.debug('v_reference_code = ' ||v_reference_code || ' v_key_the_same = ' || v_key_the_same);
331
332 IF v_key_the_same = 'Y' then
333
334 --
335 -- Insert any differences between individual columns
336 --
337 FOR v_counter IN 1..v_num_col LOOP
338 IF nvl(v_old(v_counter),'JJ') <> nvl(v_new(v_counter),'JJ') THEN
339 insert into XTR_AUDIT_SUMMARY(
340 AUDIT_REQUESTED_BY,
341 AUDIT_REQUEST_ID,
342 AUDIT_REQUESTED_ON,
343 AUDIT_RECORDS_FROM,
344 AUDIT_RECORDS_TO,
345 NAME_OF_COLUMN_CHANGED,
346 TABLE_NAME,
347 REFERENCE_CODE,
348 ACTION_CODE,
349 UPDATED_ON_DATE,
350 UPDATED_BY_USER,
351 OLD_VALUE,
352 NEW_VALUE,
353 TRANSACTION_REF,
354 NON_TRANSACTION_REF)
355 values
356 (p_audit_requested_by,
357 p_audit_request_id,
358 sysdate,
359 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
360 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
361 rtrim(v_col_title(v_counter)),
362 upper(v_table_name),
363 rtrim(substr(v_reference_code,1,20)),
364 'UPDATE',
365 to_char(v_new_updated_on,'DD/MM/YYYY HH24:MI:SS'),
366 substr(v_new_updated_by,1,10),
367 rtrim(substr(v_old(v_counter),1,255)),
368 rtrim(substr(v_new(v_counter),1,255)),
369 null,
370 v_new_letter);
371 END IF;
372 END LOOP;
373
374 ELSE -- Insert row for auditing new creation
375
376 IF v_new_created_on between
377 to_date(p_date_from, 'DD/MM/YYYY HH24:MI:SS') and
378 to_date(p_date_to , 'DD/MM/YYYY HH24:MI:SS') THEN
379 insert into XTR_AUDIT_SUMMARY(
380 AUDIT_REQUESTED_BY,
381 AUDIT_REQUEST_ID,
382 AUDIT_REQUESTED_ON,
383 AUDIT_RECORDS_FROM,
384 AUDIT_RECORDS_TO,
385 NAME_OF_COLUMN_CHANGED,
386 TABLE_NAME,
387 REFERENCE_CODE,
388 ACTION_CODE,
389 UPDATED_ON_DATE,
390 UPDATED_BY_USER,
391 OLD_VALUE,
392 NEW_VALUE,
393 TRANSACTION_REF,
394 NON_TRANSACTION_REF)
395 values
396 (p_audit_requested_by,
397 p_audit_request_id,
398 sysdate,
399 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
400 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
401 null, --rtrim(v_col_title(v_counter)),
402 upper(v_table_name),
403 rtrim(substr(v_reference_code,1,20)),
404 'INSERT',
405 to_char(v_new_created_on,'DD/MM/YYYY HH24:MI:SS'),
406 substr(v_new_created_by,1,10),
407 null, --rtrim(substr(v_old(v_counter),1,255)),
408 null, --rtrim(substr(v_new(v_counter),1,255)),
409 null,
410 v_new_letter);
411 END IF;
412 END IF;
413
414 --
415 -- Store all "new" column values into "old"
416 --
417 v_old_letter := v_new_letter;
418 v_old_updated_on := v_new_updated_on;
419 v_old_updated_by := v_new_updated_by;
420 --
421 FOR v_counter in 1..v_num_col LOOP
422 v_old(v_counter) := v_new(v_counter);
423 END LOOP;
424 v_rec_num := v_rec_num + 1;
425 END LOOP;
426 dbms_sql.close_cursor(v_cursor);
427 --
428 END XTR_AUDIT_RETRIEVE;
429
430 PROCEDURE XTR_TERM_ACTIONS_RETRIEVE(p_audit_requested_by IN VARCHAR2,
431 p_audit_request_id IN NUMBER,
432 p_event_name IN VARCHAR2,
433 p_date_from IN VARCHAR2,
434 p_date_to IN VARCHAR2)
435 IS
436 BEGIN
437
438 insert into XTR_AUDIT_SUMMARY(
439 AUDIT_REQUESTED_BY,
440 AUDIT_REQUEST_ID,
441 AUDIT_REQUESTED_ON,
442 AUDIT_RECORDS_FROM,
443 AUDIT_RECORDS_TO,
444 NAME_OF_COLUMN_CHANGED,
445 TABLE_NAME,
446 REFERENCE_CODE,
447 ACTION_CODE,
448 UPDATED_ON_DATE,
449 UPDATED_BY_USER,
450 OLD_VALUE,
451 NEW_VALUE,
452 TRANSACTION_REF,
453 NON_TRANSACTION_REF)
454 select p_audit_requested_by,
455 p_audit_request_id,
456 sysdate,
457 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
458 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
459 null,
460 'XTR_TERM_ACTIONS',
461 to_char(DEAL_NO) ||'|'||INCREASE_EFFECTIVE_FROM_DATE,
462 'PRINCIPAL',
463 CREATED_ON,
464 CREATED_BY,
465 null,
466 to_char(PRINCIPAL_ADJUST),
467 null,
468 null
469 from XTR_TERM_ACTIONS
470 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
471 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
472 and INCREASE_EFFECTIVE_FROM_DATE is not null
473 and PRINCIPAL_ADJUST is not null
474 UNION
475 select p_audit_requested_by,
476 p_audit_request_id,
477 sysdate,
478 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
479 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
480 null,
481 'XTR_TERM_ACTIONS',
482 to_char(DEAL_NO) ||'|'||EFFECTIVE_FROM_DATE,
483 'INTEREST',
484 CREATED_ON,
485 CREATED_BY,
486 null,
487 to_char(NEW_INTEREST_RATE),
488 null,
489 null
490 from XTR_TERM_ACTIONS
491 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
492 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
493 and EFFECTIVE_FROM_DATE is not null
494 and NEW_INTEREST_RATE is not null
495 UNION
496 select p_audit_requested_by,
497 p_audit_request_id,
498 sysdate,
499 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
500 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
501 null,
502 'XTR_TERM_ACTIONS',
503 to_char(DEAL_NO) ||'|'||FROM_START_DATE ,
504 'SCHEDULE',
505 CREATED_ON,
506 CREATED_BY,
507 null,
508 PAYMENT_SCHEDULE_CODE ,
509 null,
510 null
511 from XTR_TERM_ACTIONS
512 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
513 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
514 and FROM_START_DATE is not null
515 and PAYMENT_SCHEDULE_CODE is not null;
516
517
518 END XTR_TERM_ACTIONS_RETRIEVE;
519
520
521 PROCEDURE SUBMIT_AUDIT_REPORT( p_audit_request_id NUMBER,
522 p_event_group VARCHAR2,
523 p_from_date VARCHAR2,
524 p_to_date VARCHAR2)
525 IS
526 req_id NUMBER;
527 request_id NUMBER;
528 orig_req_id VARCHAR2(30);
529 number_of_copies number;
530 printer VARCHAR2(30);
531 print_style VARCHAR2(30);
532 save_output_flag VARCHAR2(30);
533 save_output_bool BOOLEAN;
534
535 BEGIN
536
537 /*
538 --
539 -- Get original request id
540 --
541 fnd_profile.get('CONC_REQUEST_ID', orig_req_id);
542 request_id := to_number(orig_req_id);
543 --
544 -- Get print options
545 --
546 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
547 number_of_copies,
548 print_style,
549 printer,
550 save_output_flag))THEN
551 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
552 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'Message: get print options failed');
553 END IF;
554 ELSE
555 IF (save_output_flag = 'Y') THEN
556 save_output_bool := TRUE;
557 ELSE
558 save_output_bool := FALSE;
559 END IF;
560 --
561 -- Set print options
562 --
563 IF (NOT FND_REQUEST.set_print_options( printer,
564 print_style,
565 number_of_copies,
566 save_output_bool)) THEN
567 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
568 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'Set print options failed');
569 END IF;
570 END IF;
571 END IF;
572
573 */
574
575 req_id := FND_REQUEST.SUBMIT_REQUEST('XTR',
576 'XTRAUSRM',
577 NULL,
578 trunc(sysdate),
579 FALSE,
580 p_audit_request_id,
581 null,
582 p_event_group,
583 null,
584 null,
585 null,
586 p_from_date,
587 p_to_date,
588 'N',
589 'N');
590 COMMIT;
591 IF (req_id = 0) THEN
592 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
593 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'ERROR submitting concurrent request');
594 END IF;
595 ELSE
596 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
597 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'EXECUTION REPORT SUBMITTED');
598 END IF;
599 END IF;
600
601 END SUBMIT_AUDIT_REPORT;
602
603 END XTR_AUDIT;