[Home] [Help]
PACKAGE BODY: APPS.XTR_AUDIT
Source
1 PACKAGE BODY XTR_AUDIT AS
2 /* $Header: xtraudtb.pls 120.5 2005/06/29 05:51:36 badiredd 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', 'SYSTEM PARAMETERS',
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 Elsif p_event_name = 'TRANSACTIONS' then
172 v_select := 'nvl(UPDATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY_USER, '||
173 'nvl(CREATED_ON_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY_USER';
174 Else
175 v_select := 'nvl(LAST_UPDATE_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),LAST_UPDATED_BY, '||
176 'nvl(CREATION_DATE,to_date(''01/01/1900'',''DD/MM/YYYY'')),CREATED_BY';
177 End if;
178
179
180 v_counter := 1;
181 LOOP
182 EXIT WHEN v_counter > V_MAX_COL;
183 fetch c_get_columns into v_table_column,
184 v_col_title( v_counter),
185 v_col_pkey( v_counter),
186 v_col_type( v_counter);
187 EXIT WHEN c_get_columns%NOTFOUND;
188
189 IF substr(v_col_type(v_counter),1,4) = 'DATE' THEN
190 v_select := v_select || ', to_char('||v_table_column||',''DD/MM/YYYY HH24:MI:SS'')';
191 ELSIF substr(v_col_type(v_counter),1,4) in ('CHAR','VARC') THEN
192 v_select := v_select || ',' || v_table_column;
193 ELSE
194 v_select := v_select || ',to_char(' || v_table_column || ')';
195 END IF;
196
197 v_counter := v_counter + 1;
198 END LOOP;
199 close c_get_columns;
200 v_num_col := v_counter -1;
201
202 --
203 -- Put all of SQL statement together (ie select + where clause)
204 --
205 /*
206 code below modified by Ilavenil to support audit feature for both new and existing table sin patchset F
207
208 Existing tables have PRORATE WHO columns like created_by, created_on, updated_by, updated_on.
209 Newly created tables have AOL wHO columns like created_by, creation_date, last_updated_by, last_update_date.
210 Due to this inconsistency, we are to go for the following IF, ELSIF, ELSE condition which handled this difference
211 in WHO column in a different manner.
212
213 IF condition covers all the existing table with PRORATE WHO columns.
214 ELSIF condition covers Xtr_Deals, which is to be handled specially, though it is an existing table.
215 ELSE condition covers all the newly created table.
216 */
217
218 If p_event_name in ('BANK A/C SETUP', 'BANK BALANCES', 'BOND ISSUES SETUP', 'BUY / SELL CURRENCIES',
219 'COMPANY LIMITS', 'COUNTERPARTY LIMITS', 'CURRENCIES SETUP', 'DEAL ORDERS',
220 'DEALER LIMITS', 'EXPOSURE TRANSACTIONS', 'EXPOSURE TYPES', 'GL REFERENCES',
221 'INTERGROUP TRANSFERS', 'JOURNAL STRUCTURE', 'JOURNALS', 'PARTIES',
222 'PARTY DEFAULTS', 'PORTFOLIOS SETUP', 'PRODUCT TYPES', 'RATE SETS', 'REVALUATION DETAIL',
223 'REVALUATION RATES', 'SETTLEMENTS', 'STANDING INSTRUCTIONS', 'SYSTEM PARAMETERS',
224 'TAX/BROKERAGE RATES', 'TAX/BROKERAGE SETUP', 'TERM DEPOSIT/ADVANCE ADJUSTMENTS',
225 'USER CODES SETUP') then
226 v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
227 'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
228 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
229 '(created_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
230 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) UNION ';
231
232 v_sql := v_sql ||
233 'select ''A'',' || v_select ||' from '||v_audit_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 '(audit_date_stored 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'')) '||'order by ';
238 Elsif p_event_name = 'TRANSACTIONS' then
239 v_sql := 'select ''A'',' || v_select ||' from '||'XTR_A_ALL_CONTRACTS_V'||' '||
240 'WHERE (updated_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
241 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
242 '(created_on_date between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
243 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
244 Else
245 v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
246 'WHERE (LAST_UPDATE_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
247 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
248 '(CREATION_DATE between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
249 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) UNION ';
250
251 v_sql := v_sql ||
252 'select ''A'',' || v_select ||' from '||v_audit_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 '(audit_date_stored 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'')) '||'order by ';
257 End if;
258
259 -- Add the primary key column/s to the SORT BY clause
260 FOR v_counter IN 1..v_num_col LOOP
261 IF v_col_pkey(v_counter) = 'Y' THEN
262 v_sql := v_sql || to_char(v_counter+5)||',';
263 END IF;
264 END LOOP;
265 v_sql := v_sql||'1,2,3'; -- Add B/A, "updated_on, updated_by" to SORT BY clause
266
267 --
268 -- Now set up dbms_sql cursor
269 --
270 v_cursor := dbms_sql.open_cursor;
271 dbms_sql.parse(v_cursor,v_sql,native);
272 dbms_sql.define_column(v_cursor,1,v_new_letter, 1);
273 dbms_sql.define_column(v_cursor,2,v_new_updated_on);
274 dbms_sql.define_column(v_cursor,3,v_new_updated_by,30);
275 dbms_sql.define_column(v_cursor,4,v_new_created_on);
276 dbms_sql.define_column(v_cursor,5,v_new_created_by,30);
277
278 -- Its weird how come this next bit works !!??
279 FOR v_counter IN 1..v_num_col LOOP
280 dbms_sql.define_column( v_cursor, v_counter+5, v_var1, 100);
281 END LOOP;
282
283 v_rows_processed := dbms_sql.execute( v_cursor );
284
285 --
286 -- Now loop through records in cursor.
287 --
288 v_rec_num := 1;
289 FOR v_counter in 1..v_num_col LOOP
290 v_old(v_counter) := 'XX';
291 END LOOP;
292
293 LOOP
294
295 EXIT WHEN dbms_sql.fetch_rows(v_cursor) < 1;
296 dbms_sql.column_value(v_cursor,1,v_new_letter);
297 dbms_sql.column_value(v_cursor,2,v_new_updated_on);
298 dbms_sql.column_value(v_cursor,3,v_new_updated_by);
299 dbms_sql.column_value(v_cursor,4,v_new_created_on);
300 dbms_sql.column_value(v_cursor,5,v_new_created_by);
301 FOR v_counter IN 1..v_num_col LOOP
302 dbms_sql.column_value(v_cursor,v_counter + 5,v_var1);
303 v_new(v_counter) := v_var1;
304 END LOOP;
305
306 --
307 -- See if primary keys MATCH
308 --
309 v_key_the_same := 'Y';
310 v_reference_code := null;
311 FOR v_counter IN 1..v_num_col LOOP
312 IF v_col_pkey(v_counter) = 'Y' THEN
313 IF v_reference_code is not null and v_new( v_counter ) is not null THEN
314 v_reference_code := v_reference_code||'|';
315 END IF;
316 v_reference_code := v_reference_code||rtrim(v_new( v_counter ));
317 IF nvl(v_old(v_counter),'JJ') <> nvl(v_new(v_counter),'JJ') THEN
318 v_key_the_same := 'N';
319 END IF;
320 END IF;
321 END LOOP;
322
323 --fnd_message.debug('v_reference_code = ' ||v_reference_code || ' v_key_the_same = ' || v_key_the_same);
324
325 IF v_key_the_same = 'Y' then
326
327 --
328 -- Insert any differences between individual columns
329 --
330 FOR v_counter IN 1..v_num_col LOOP
331 IF nvl(v_old(v_counter),'JJ') <> nvl(v_new(v_counter),'JJ') THEN
332 insert into XTR_AUDIT_SUMMARY(
333 AUDIT_REQUESTED_BY,
334 AUDIT_REQUEST_ID,
335 AUDIT_REQUESTED_ON,
336 AUDIT_RECORDS_FROM,
337 AUDIT_RECORDS_TO,
338 NAME_OF_COLUMN_CHANGED,
339 TABLE_NAME,
340 REFERENCE_CODE,
341 ACTION_CODE,
342 UPDATED_ON_DATE,
343 UPDATED_BY_USER,
344 OLD_VALUE,
345 NEW_VALUE,
346 TRANSACTION_REF,
347 NON_TRANSACTION_REF)
348 values
349 (p_audit_requested_by,
350 p_audit_request_id,
351 sysdate,
352 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
353 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
354 rtrim(v_col_title(v_counter)),
355 upper(v_table_name),
356 rtrim(substr(v_reference_code,1,20)),
357 'UPDATE',
358 to_char(v_new_updated_on,'DD/MM/YYYY HH24:MI:SS'),
359 substr(v_new_updated_by,1,10),
360 rtrim(substr(v_old(v_counter),1,255)),
361 rtrim(substr(v_new(v_counter),1,255)),
362 null,
363 v_new_letter);
364 END IF;
365 END LOOP;
366
367 ELSE -- Insert row for auditing new creation
368
369 IF v_new_created_on between
370 to_date(p_date_from, 'DD/MM/YYYY HH24:MI:SS') and
371 to_date(p_date_to , 'DD/MM/YYYY HH24:MI:SS') THEN
372 insert into XTR_AUDIT_SUMMARY(
376 AUDIT_RECORDS_FROM,
373 AUDIT_REQUESTED_BY,
374 AUDIT_REQUEST_ID,
375 AUDIT_REQUESTED_ON,
377 AUDIT_RECORDS_TO,
378 NAME_OF_COLUMN_CHANGED,
379 TABLE_NAME,
380 REFERENCE_CODE,
381 ACTION_CODE,
382 UPDATED_ON_DATE,
383 UPDATED_BY_USER,
384 OLD_VALUE,
385 NEW_VALUE,
386 TRANSACTION_REF,
387 NON_TRANSACTION_REF)
388 values
389 (p_audit_requested_by,
390 p_audit_request_id,
391 sysdate,
392 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
393 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
394 null, --rtrim(v_col_title(v_counter)),
395 upper(v_table_name),
396 rtrim(substr(v_reference_code,1,20)),
397 'INSERT',
398 to_char(v_new_created_on,'DD/MM/YYYY HH24:MI:SS'),
399 substr(v_new_created_by,1,10),
400 null, --rtrim(substr(v_old(v_counter),1,255)),
401 null, --rtrim(substr(v_new(v_counter),1,255)),
402 null,
403 v_new_letter);
404 END IF;
405 END IF;
406
407 --
408 -- Store all "new" column values into "old"
409 --
410 v_old_letter := v_new_letter;
411 v_old_updated_on := v_new_updated_on;
412 v_old_updated_by := v_new_updated_by;
413 --
414 FOR v_counter in 1..v_num_col LOOP
415 v_old(v_counter) := v_new(v_counter);
416 END LOOP;
417 v_rec_num := v_rec_num + 1;
418 END LOOP;
419 dbms_sql.close_cursor(v_cursor);
420 --
421 END XTR_AUDIT_RETRIEVE;
422
423 PROCEDURE XTR_TERM_ACTIONS_RETRIEVE(p_audit_requested_by IN VARCHAR2,
424 p_audit_request_id IN NUMBER,
425 p_event_name IN VARCHAR2,
426 p_date_from IN VARCHAR2,
427 p_date_to IN VARCHAR2)
428 IS
429 BEGIN
430
431 insert into XTR_AUDIT_SUMMARY(
432 AUDIT_REQUESTED_BY,
433 AUDIT_REQUEST_ID,
434 AUDIT_REQUESTED_ON,
435 AUDIT_RECORDS_FROM,
436 AUDIT_RECORDS_TO,
437 NAME_OF_COLUMN_CHANGED,
438 TABLE_NAME,
439 REFERENCE_CODE,
440 ACTION_CODE,
441 UPDATED_ON_DATE,
442 UPDATED_BY_USER,
443 OLD_VALUE,
444 NEW_VALUE,
445 TRANSACTION_REF,
446 NON_TRANSACTION_REF)
447 select p_audit_requested_by,
448 p_audit_request_id,
449 sysdate,
450 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
451 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
452 null,
453 'XTR_TERM_ACTIONS',
454 to_char(DEAL_NO) ||'|'||INCREASE_EFFECTIVE_FROM_DATE,
455 'PRINCIPAL',
456 CREATED_ON,
457 CREATED_BY,
458 null,
459 to_char(PRINCIPAL_ADJUST),
460 null,
461 null
462 from XTR_TERM_ACTIONS
463 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
464 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
465 and INCREASE_EFFECTIVE_FROM_DATE is not null
466 and PRINCIPAL_ADJUST is not null
467 UNION
468 select p_audit_requested_by,
469 p_audit_request_id,
470 sysdate,
471 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
472 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
473 null,
474 'XTR_TERM_ACTIONS',
475 to_char(DEAL_NO) ||'|'||EFFECTIVE_FROM_DATE,
476 'INTEREST',
477 CREATED_ON,
478 CREATED_BY,
479 null,
480 to_char(NEW_INTEREST_RATE),
481 null,
482 null
483 from XTR_TERM_ACTIONS
484 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
485 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
486 and EFFECTIVE_FROM_DATE is not null
487 and NEW_INTEREST_RATE is not null
488 UNION
489 select p_audit_requested_by,
490 p_audit_request_id,
491 sysdate,
492 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
493 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
494 null,
495 'XTR_TERM_ACTIONS',
496 to_char(DEAL_NO) ||'|'||FROM_START_DATE ,
497 'SCHEDULE',
498 CREATED_ON,
499 CREATED_BY,
500 null,
501 PAYMENT_SCHEDULE_CODE ,
502 null,
503 null
504 from XTR_TERM_ACTIONS
505 where (CREATED_ON between to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS') and
506 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'))
507 and FROM_START_DATE is not null
508 and PAYMENT_SCHEDULE_CODE is not null;
509
510
511 END XTR_TERM_ACTIONS_RETRIEVE;
512
513
514 PROCEDURE SUBMIT_AUDIT_REPORT( p_audit_request_id NUMBER,
515 p_event_group VARCHAR2,
516 p_from_date VARCHAR2,
517 p_to_date VARCHAR2)
518 IS
519 req_id NUMBER;
520 request_id NUMBER;
521 orig_req_id VARCHAR2(30);
522 number_of_copies number;
523 printer VARCHAR2(30);
524 print_style VARCHAR2(30);
525 save_output_flag VARCHAR2(30);
526 save_output_bool BOOLEAN;
527
528 BEGIN
529
530 /*
531 --
532 -- Get original request id
533 --
534 fnd_profile.get('CONC_REQUEST_ID', orig_req_id);
535 request_id := to_number(orig_req_id);
536 --
537 -- Get print options
538 --
539 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
540 number_of_copies,
541 print_style,
542 printer,
543 save_output_flag))THEN
544 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
545 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'Message: get print options failed');
546 END IF;
547 ELSE
548 IF (save_output_flag = 'Y') THEN
549 save_output_bool := TRUE;
550 ELSE
551 save_output_bool := FALSE;
552 END IF;
553 --
554 -- Set print options
555 --
556 IF (NOT FND_REQUEST.set_print_options( printer,
557 print_style,
558 number_of_copies,
559 save_output_bool)) THEN
560 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
564 END IF;
561 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'Set print options failed');
562 END IF;
563 END IF;
565
566 */
567
568 req_id := FND_REQUEST.SUBMIT_REQUEST('XTR',
569 'XTRAUSRM',
570 NULL,
571 trunc(sysdate),
572 FALSE,
573 p_audit_request_id,
574 null,
575 p_event_group,
576 null,
577 null,
578 null,
579 p_from_date,
580 p_to_date,
581 'N',
582 'N');
583 COMMIT;
584 IF (req_id = 0) THEN
585 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
586 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'ERROR submitting concurrent request');
587 END IF;
588 ELSE
589 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
590 xtr_debug_pkg.debug('SUBMIT_AUDIT_REPORT: ' || 'EXECUTION REPORT SUBMITTED');
591 END IF;
592 END IF;
593
594 END SUBMIT_AUDIT_REPORT;
595
596 END XTR_AUDIT;