[Home] [Help]
PACKAGE BODY: APPS.PY_AUDIT_REP_PKG
Source
1 package body py_audit_rep_pkg as
2 /* $Header: pyadyn.pkb 120.1.12000000.2 2007/02/28 06:13:32 ckesanap noship $ */
3 --
4 -- flemonni bug 630622
5 --
6 -- specific exception required for size of sql parse string
7 --
8 -- logic behind revision: all of the _text? variables expanded
9 -- to accommodate possible large values (origin of bug 630622 was that
10 -- one variable was too small) so that only the overall length raises
11 -- an error if length > 32K.
12 --
13 g_parse_string_too_big EXCEPTION;
14 g_audit_table VARCHAR2 (100);
15 --
16 procedure py_audit_rep_proc
17 (p_table in varchar2,
18 p_primary in varchar2,
19 p_session_id in number,
20 p_start_date in varchar2,
21 p_end_date in varchar2,
22 p_username in varchar2,
23 p_table_type in varchar2)
24 is
25 --
26 error_message varchar2(100);
27 --
28 -- datetrack text varchars for use in building dynamic cursor text
29 --
30 dt_text1 varchar2(32000);
31 dt_text2 varchar2(32000);
32 dt_text3 varchar2(32000);
33 dt_text4 varchar2(32000);
34 dt_text5 varchar2(32000);
35 dt_text6 varchar2(32000);
36 dt_text7 varchar2(32000);
37 dt_text8 varchar2(32000);
38 dt_text9 varchar2(32000);
39 dt_text10 varchar2(32000);
40 dt_text11 varchar2(32000);
41 dt_text12 varchar2(32000);
42 dt_text12a varchar2(32000);
43 dt_text12b varchar2(32000);
44 dt_text13a varchar2(32000);
45 dt_text13 varchar2(32000);
46 dt_text14 varchar2(32000);
47 --
48 -- non-datetrack text varchars for use in building dynamic cursor text
49 --
50 ndt_text1 varchar2(32000);
51 ndt_text2 varchar2(32000);
52 ndt_text3 varchar2(32000);
53 ndt_text4 varchar2(32000);
54 ndt_text5 varchar2(32000);
55 ndt_text6 varchar2(32000);
56 ndt_text7 varchar2(32000);
57 ndt_text8 varchar2(1); -- not used ?
58 ndt_text9 varchar2(1); -- not used ?
59 ndt_text10 varchar2(32000);
60 ndt_text11 varchar2(32000);
61 ndt_text12 varchar2(32000);
62 ndt_text13 varchar2(32000);
63 ndt_text14 varchar2(32000);
64 ndt_text15 varchar2(32000);
65 ndt_text16 varchar2(32000);
66 ndt_text17 varchar2(32000);
67 ndt_text18 varchar2(32000);
68 ndt_text19 varchar2(32000);
69 ndt_text20 varchar2(32000);
70 ndt_text21 varchar2(32000);
71 --
72 -- procedure cursor variables
73 --
74 -- datetrack cursor
75 --
76 dt_proc_cursor integer;
77 dt_proc_cursor_text varchar2(32767);
78 dt_proc_rows integer;
79 --
80 -- non datetrack cursor variables
81 --
82 ndt_proc_cursor integer;
83 ndt_proc_cursor_text varchar2(32767);
84 ndt_proc_rows integer;
85 --
86 -- loop variable
87 --
88 t_loop_count integer;
89 --
90 -- column_details cursor variables
91 --
92 t_column_name varchar2(30);
93 t_column_id number;
94 t_column_type varchar2(1);
95 t_column_width number;
96 --
97 l_result boolean;
98 l_prod_status varchar2(1);
99 l_industry varchar2(1);
100 l_oracle_schema varchar2(30);
101 l_appl_short_name varchar2(50);
102 l_dummy number(1);
103 --
104 -- column_curs cursor to retrieve column related information from column tables
105 --
106 cursor column_curs is
107 select a.column_name,
108 a.column_id,
109 a.column_type,
110 a.width
111 from fnd_columns a,
112 fnd_audit_columns b,
113 fnd_tables c
114 where b.table_id = a.table_id
115 and b.table_id = c.table_id
116 and c.table_name = P_TABLE
117 and b.column_id = a.column_id
118 and b.table_app_id = a.application_id
119 and b.schema_id <> -1
120 and a.column_name <> P_PRIMARY
121 and a.column_name <> 'EFFECTIVE_START_DATE'
122 and a.column_name <> 'EFFECTIVE_END_DATE'
123 order by sequence_id;
124 --
125 -- cursor to check that audit table exists
126 --
127 cursor csr_chk_tabname(p_table_name varchar2, p_oracle_schema varchar2) is
128 select 1
129 from dual
130 where exists (
131 select 1
132 from all_tables tab
133 where tab.table_name = p_table_name
134 and tab.owner = p_oracle_schema
135 );
136 --
137 begin
138 --
139 -- Confirm that Audit objects have been created for the table
140 --
141 select fa.application_short_name
142 into l_appl_short_name
143 from fnd_application fa,
144 fnd_tables ft
145 where ft.table_name = P_TABLE
146 and fa.application_id = ft.application_id;
147
148 l_result := fnd_installation.get_app_info ( l_appl_short_name,
149 l_prod_status,
150 l_industry,
151 l_oracle_schema );
152 --
153 open csr_chk_tabname(substr(P_TABLE,1,24)||'_A',l_oracle_schema);
154 fetch csr_chk_tabname into l_dummy;
155 if csr_chk_tabname%notfound Then
156 close csr_chk_tabname;
157 hr_utility.set_message(800, 'HR_34865_NO_AUDIT_TABLE');
158 hr_utility.set_message_token('AUDIT_TABLE',substr(P_TABLE,1,24)||'_A');
159 hr_utility.set_message_token('BASE_TABLE',P_TABLE);
160 hr_utility.raise_error;
161 end if;
162 close csr_chk_tabname;
163 --
164 -- DATETRACK TABLE OPERATIONS
165 --
166 if p_table_type = 'DT' then
167 --
168 -- assignments of procedure cursor text
169 --
170 -- l_err error handling
171 -- l_trans datetrack_transaction
172 -- l_tstamp audit_timestamp
173 -- l_type audit_transaction_type
174 -- l_uname audit_user_name
175 -- l_sess audit_session_id
176 -- l_comm audit_commit_id
177 -- l_seq audit_sequence_id
178 -- l_nulls audit_true_nulls
179 -- l_pkval primary_key_value
180 -- l_sd effective_start_date
181 -- l_ed effective_end_date
182 -- l_audit audit_id from NEXTVAL
183 -- l_stat procedure ..._VP status return
184 -- l_grkey_cur current group key (primary key||session_id||commit_id)
185 -- l_grkey_prv previous group key
186 --
187 -- DT TEXT 1
188 --
189 dt_text1:= 'declare l_err varchar2(100);' ||
190 'l_trans varchar2(40);' ||
191 'l_tstamp date;' ||
192 'l_type varchar2(1);' ||
193 'l_uname varchar2(100);' ||
194 'l_sess number;' ||
195 'l_comm number;' ||
196 'l_seq number;' ||
197 'l_nulls varchar2(250);' ||
198 'l_pkval number;' ||
199 'l_sd date;' ||
200 'l_ed date;' ||
201 'do_alt boolean;' ||
202 'do_proc boolean;' ||
203 'do_ass boolean;' ||
204 'do_ins boolean;' ||
205 'do_dates boolean;' ||
206 'do_comp boolean;' ||
207 'do_nass boolean;' ||
208 'o_sd date;' ||
209 'o_ed date;' ||
210 'n_sd date;' ||
211 'n_ed date;' ||
212 'n_type varchar2(1);' ||
213 'st_sd varchar2(40);' ||
214 'st_ed varchar2(40);' ||
215 't_sd date;' ||
216 't_ed date;' ||
217 'a_sd date;' ||
218 'a_ed date;' ||
219 'l_stat varchar2(40);' ||
220 'l_audit number;' ||
221 'l_grkey_cur varchar2(200);' ||
222 'l_grkey_prv varchar2(200);' ||
223 'dct number;' ||
224 'ict number;' ||
225 'D1 number;' ||
226 'D2 number;' ||
227 'D3 number;' ||
228 'D4 number;' ||
229 --
230 -- added for multiple changes
231 --
232 'o_dt_start date;' ||
233 'o_dt_end date;' ||
234 'multiple_start_flag boolean;' ||
235 'multiple_end_flag boolean;';
236 --
237 --
238 -- DT TEXT 5
239 --
240 dt_text5:= 'cursor dt_curs is select ' ||
241 '{DT_TABLE}_TT(audit_session_id,' ||
242 'audit_commit_id,' ||
243 '{DT_PRIMARY}),' ||
244 'audit_timestamp,' ||
245 'audit_transaction_type,' ||
246 'audit_user_name,' ||
247 'audit_session_id,' ||
248 'audit_commit_id,' ||
249 'audit_sequence_id,' ||
250 'audit_true_nulls,' ||
251 '{DT_PRIMARY},' ||
252 'effective_start_date,' ||
253 'effective_end_date ';
254 --
255 dt_text5 := replace(dt_text5, '{DT_TABLE}', substr(P_TABLE,1,24));
256 dt_text5 := replace(dt_text5, '{DT_PRIMARY}', P_PRIMARY);
257 --
258 -- DT TEXT 6
259 --
260 dt_text6 := ' from {DT_TABLE}_A ' ||
261 'where audit_timestamp >= ' ||
262 'to_date(''' || P_START_DATE ||
263 ''',''DD-MM-YYYY HH24:MI'') ' ||
264 'and audit_timestamp < ' ||
265 'to_date(''' || P_END_DATE ||
266 ''',''DD-MM-YYYY HH24:MI'') ' ||
267 'and audit_user_name like ''' || P_USERNAME ||
268 ''' order by {DT_PRIMARY}' ||
269 --
270 -- datetrack function for checking varchar2 differences
271 --
272 'function dv(pa in varchar2,' ||
273 'pb in varchar2,' ||
274 'pp in number)' ||
275 'return boolean is result boolean:=false;' ||
276 'begin ' ||
277 'IF pa IS NOT NULL AND pb IS NOT NULL AND pa<>pb ' ||
278 'THEN result:=true;END IF;' ||
279 'IF pa IS NULL AND pb IS NOT NULL AND ' ||
280 '((l_nulls IS NOT NULL AND SUBSTR(l_nulls,pp+3,1)=''Y'') '||
281 'OR (l_nulls IS NULL AND l_type=''I'')) ' ||
282 'THEN result:=true;END IF;' ||
283 'IF pa IS NOT NULL AND pb IS NULL ' ||
284 'THEN result:=true;END IF;' ||
285 'RETURN result;' ||
286 'end dv;' ||
287 --
288 -- datetrack function for checking number differences
289 --
290 'function dn(pa in number,' ||
291 'pb in number,' ||
292 'pp in number)' ||
293 'return boolean is result boolean:=false;' ||
294 'begin ' ||
295 'IF pa IS NOT NULL AND pb IS NOT NULL AND pa<>pb ' ||
296 'THEN result:=true;END IF;' ||
297 'IF pa IS NULL AND pb IS NOT NULL AND ' ||
298 '((l_nulls IS NOT NULL AND SUBSTR(l_nulls,pp+3,1)=''Y'') ' ||
299 'OR (l_nulls IS NULL AND l_type=''I'')) ' ||
300 'THEN result:=true;END IF;' ||
301 'IF pa IS NOT NULL AND pb IS NULL ' ||
302 'THEN result:=true;END IF;' ||
303 'RETURN result;' ||
304 'end dn;' ||
305 --
306 -- datetrack insert procedure for the hr_audits_columns table
307 --
308 -- cid column_id
309 -- cn column_name
310 -- ov old value
311 -- nv new value
312 -- lov local old value
313 -- lnv local new value
314 -- varchar2 format
315 --
316 'procedure ins(cid in number,' ||
317 'cn in varchar2,' ||
318 'ov in varchar2,' ||
319 'nv in varchar2)is ' ||
320 'lov varchar2(240);lnv varchar2(240);' ||
321 'l_cid number;l_cn varchar2(240);' ||
322 'BEGIN ' ||
323 'IF cid IS NULL THEN l_cid:=0;' ||
324 'ELSE l_cid:=cid;END IF;' ||
325 'IF cn IS NULL THEN l_cn:=''***'' || ' ||
326 ' to_char(l_cid);ELSE l_cn:=cn;END IF;' ||
327 'lov:=ov;lnv:=nv;' ||
328 'if ov=''31-12-4712'' then ' ||
329 'lov:=''** END OF TIME **'';end if;' ||
330 'if nv=''31-12-4712'' then ' ||
331 'lnv:=''** END OF TIME **'';end if;' ||
332 'insert into hr_audit_columns' ||
333 '(audit_id,column_id,column_name,old_value,new_value)' ||
334 'values(hr_audits_s.currval,' ||
335 'l_cid,' ||
336 'l_cn,' ||
337 'lov,lnv);' ||
338 ' exception when others then null;raise;' ||
339 'end ins;' ||
340 --
341 -- number format
342 --
343 'procedure ins(cid in number,' ||
344 'cn in varchar2,' ||
345 'ov in number,' ||
346 'nv in number)is ' ||
347 'begin ins(cid,cn,to_char(ov),to_char(nv));'||
348 'end ins;' ||
349 --
350 -- date format
351 --
352 'procedure ins(cid in number,' ||
353 'cn in varchar2,' ||
354 'ov in date,' ||
355 'nv in date)is ' ||
356 'begin ins(cid,cn,to_char(ov,''DD-MM-YYYY''),' ||
357 'to_char(nv,''DD-MM-YYYY''));' ||
358 'end ins;' ||
359 --
360 'begin ' ||
361 'select hr_audits_s.nextval into ' ||
362 'l_audit from dual;' ||
363 'open dt_curs;' ||
364 'loop ' ||
365 'fetch dt_curs into l_trans,' ||
366 'l_tstamp,' ||
367 'l_type,' ||
368 'l_uname,' ||
369 'l_sess,' ||
370 'l_comm,' ||
371 'l_seq,' ||
372 'l_nulls,' ||
373 'l_pkval,' ||
374 'l_sd,' ||
375 'l_ed';
376 --
377 dt_text6 := replace(dt_text6, '{DT_TABLE}', substr(P_TABLE,1,24));
378 --
379 dt_text6 := replace(dt_text6,'{DT_PRIMARY}',
380 ' 9 ASC,5 ASC,6 ASC,3 DESC,10 ASC,11 ASC;');
381 -- 9 responds to primary key
382 -- 5 responds to session id
383 -- 6 responds to commit id
384 -- 3 responds to audit transaction type
385 -- 10 responds to effective start date
386 -- 11 responds to effective end date
387 --
388 -- DT TEXT 7
389 --
390 dt_text7 := 'if dt_curs%notfound then ' ||
391 'close dt_curs;exit;end if;' ||
392 'l_grkey_cur:=to_char(l_pkval)||to_char(l_sess)|| ' ||
393 'to_char(l_comm);' ||
394 'dbms_output.put_line(l_grkey_cur);' ||
395 'if l_grkey_prv IS NULL or l_grkey_prv<>l_grkey_cur ' ||
396 'then l_stat:=''ERROR''; ';
397 --
398 -- DT TEXT 9
399 --
400 -- initialization for each new group
401 dt_text9:= 'a_sd:=null;a_ed:=null;' ||
402 't_sd:=null;t_ed:=null;' ||
403 'st_sd:=null;st_ed:=null;' ||
404 'n_type:=null;' ||
405 'dct:=0;ict:=0;' ||
406 'D1:=1;D2:=2;D3:=3;D4:=4;' ||
407 'o_dt_start:=null; ' ||
408 'o_dt_end:=null;' ||
409 'multiple_start_flag:=false;' ||
410 'multiple_end_flag:=false;' ||
411 'end if;' ||
412 -- end of initialization for each new group
413 -- initialization for each row in a group
414 'do_proc:=false;' ||
415 'do_ass:=false;' ||
416 'do_ins:=false;' ||
417 'do_dates:=false;' ||
418 'do_alt:=false;' ||
419 'do_comp:=false;' ||
420 'do_nass:=false;' ||
421 'o_sd:=null;o_ed:=null;' ||
422 'n_sd:=null;n_ed:=null;' ||
423 'n_type:=null;';
424
425 -- end of initialization for each row
426 --
427 -- DT TEXT 10
428 -- datetrack transaction logic
429 --
430 -- dt first insert logic
431 --
432 dt_text10:= 'if l_trans=''FIRST_INSERT'' then ' ||
433 'do_ins:=true;do_comp:=true;' ||
434 'do_alt:=false;do_dates:=true;' ||
435 'do_ass:=true;do_proc:=true;do_nass:=false;' ||
436 'n_sd:=l_sd;' ||
437 'n_ed:=l_ed;' ||
438 --
439 -- dt correction logic
440 --
441 'elsif l_trans=''CORRECTION'' then ' ||
442 'do_ins:=true;do_comp:=true;' ||
443 'do_alt:=false;do_dates:=true;' ||
444 'do_ass:=true;do_proc:=true;do_nass:=false;' ||
445 'n_sd:=l_sd;' ||
446 'n_ed:=l_ed;' ||
447 --
448 -- dt update/update change insert logic
449 --
450 'elsif l_trans in(''UPDATE'',''UPDATE_CHANGE_INSERT'')then '||
451 'if l_type=''I'' then ' ||
452 'ict:=ict+1;' ||
453 'if t_sd is null then ' ||
454 't_sd:=l_sd;t_ed:=l_ed;' ||
455 'elsif t_sd is not null then ' ||
456 'if l_sd > t_sd then ' ||
457 'n_sd:=l_sd;n_ed:=l_ed;' ||
458 'o_sd:=t_sd;o_ed:=t_ed;' ||
459 'else n_sd:=t_sd;n_ed:=t_ed;' ||
460 'o_sd:=l_sd;o_ed:=l_ed;' ||
461 'end if;' ||
462 'end if;' ||
463 'if ict=1 then ' ||
464 'do_ins:=false;do_comp:=false;' ||
465 'do_alt:=false;do_dates:=false;' ||
466 'do_ass:=false;do_proc:=true;do_nass:=false;'||
467 'end if;' ||
468 'if ict=2 then ' ||
469 'do_ins:=true;do_comp:=false;' ||
470 'do_alt:=false;do_dates:=true;' ||
471 'do_ass:=false;do_proc:=true;do_nass:=false;'||
472 'end if;' ||
473 'elsif l_type=''D'' then ' ||
474 'dct:=dct+1;' ||
475 'a_sd:=l_sd;a_ed:=l_ed;' ||
476 'st_sd:=''Former From Date'';' ||
477 'st_ed:=''Former To Date'';' ||
478 'do_ins:=false;do_comp:=true;' ||
479 'do_alt:=true;do_dates:=false;' ||
480 'do_ass:=true;do_proc:=false;do_nass:=false;'||
481 'end if;' ||
482 --
483 -- dt change hire date logic
484 --
485 'elsif l_trans=''CHANGE_HIRE_DATE'' then ' ||
486 'if l_type =''I'' then ' ||
487 'ict:=ict+1;' ||
488 't_sd:=l_sd;t_ed:=l_ed;' ||
489 'a_sd:=l_sd;a_ed:=l_ed;' ||
490 'do_ins:=false;do_comp:=false;' ||
491 'do_alt:=false;do_dates:=false;' ||
492 'do_ass:=false;do_proc:=false;do_nass:=false;' ||
493 'elsif l_type =''D'' then ' ||
494 'dct:=dct+1;' ||
495 'o_sd:=l_sd;o_ed:=l_ed;' ||
496 'n_sd:=a_sd;n_ed:=a_ed;' ||
497 'if dct=1 then ' ||
498 'do_ins:=true;do_comp:=true;' ||
499 'do_alt:=false;do_dates:=true;' ||
500 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
501 'end if; ' ||
502 'if dct>1 then ' ||
503 'do_ins:=true;do_comp:=true;' ||
504 'do_alt:=false;do_dates:=true;' ||
505 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
506 'end if;' ||
507 'end if;' ||
508 --
509 -- dt reverse termination logic
510 --
511 'elsif l_trans=''REVERSE_TERMINATION'' then ' ||
512 'if l_type =''I'' then ' ||
513 'ict:=ict+1;' ||
514 'do_ins:=false;do_comp:=false;' ||
515 'do_alt:=false;do_dates:=false;' ||
516 'do_ass:=false;do_proc:=false;do_nass:=false;' ||
517 'elsif l_type =''D'' then ' ||
518 'dct:=dct+1;' ||
519 'o_sd:=l_sd;o_ed:=l_ed;' ||
520 'if dct=1 then ' ||
521 'do_ins:=false;do_comp:=false;' ||
522 'do_alt:=false;do_dates:=false;' ||
523 'do_ass:=true;do_proc:=false;do_nass:=true;' ||
524 'end if;' ||
525 'if dct=2 then ' ||
526 'n_sd:=a_sd;n_ed:=a_ed;' ||
527 'do_ins:=false;do_comp:=true;' ||
528 'do_alt:=false;do_dates:=true;' ||
529 'do_ass:=false;do_proc:=false;do_nass:=true;' ||
530 'end if;' ||
531 'elsif l_type =''U'' then ' ||
532 'a_sd:=l_sd;a_ed:=l_ed;' ||
533 'do_ins:=true;do_comp:=false;' ||
534 'do_alt:=false;do_dates:=false;' ||
535 'do_ass:=false;do_proc:=false;do_nass:=false;' ||
536 'end if;' ||
537 --
538 -- dt delete next change/future change logic
539 --
540 -- VT 02/06/96 changed options for Forms 2.3
541 -- cancellation of termination.
542 -- previous options were :
543 -- I do_proc:=false;
544 -- D first do_ins:=true;do_comp:=true;do_dates:=true;
545 -- do_ass:=true;
546
547 /* Bug 5277170 logic changes for 'Delete Next' : The if condition (dct>1) was changed
548 to (dct>0) thus eliminating the need for the if condition (dct=1). This change will
549 affect all the Delete transactions to appear on the Audit Report with appripriate old and
550 new dates. This logic holds good for 'Future Change' transaction also. */
551
552 'elsif l_trans in(''DELETE_NEXT_CHANGE'',''FUTURE_CHANGE'') then ' ||
553 'if l_type =''I'' then ' ||
554 'ict:=ict+1;' ||
555 't_sd:=l_sd;t_ed:=l_ed;' ||
556 'do_ins:=false;do_comp:=false;' ||
557 'do_alt:=false;do_dates:=false;' ||
558 'do_ass:=false;do_proc:=true;do_nass:=false;' ||
559 'elsif l_type =''D'' then ' ||
560 'dct:=dct+1;' ||
561 'o_sd:=l_sd;o_ed:=l_ed;' ||
562 /* 'if dct=1 then ' ||
563 'do_ins:=false;do_comp:=false;' ||
564 'do_alt:=false;do_dates:=false;' ||
565 'do_ass:=false;do_proc:=false;do_nass:=false;' ||
566 'end if;' || */
567 'if dct>0 then ' || -- bug 5277170
568 'do_ins:=true;do_comp:=true;' ||
569 'do_alt:=false;do_dates:=true;' ||
570 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
571 'end if;' ||
572 'if l_sd=t_sd then ' ||
573 'n_sd:=t_sd;n_ed:=t_ed;' ||
574 'do_comp:=false;' ||
575 'else n_sd:=null;n_ed:=null;' ||
576 'end if;end if;' ||
577 --
578 -- dt zap logic
579 --
580 'elsif l_trans=''ZAP'' then ' ||
581 'dct:=dct+1;' ||
582 'o_sd:=l_sd;o_ed:=l_ed;' ||
583 'do_ins:=true;do_comp:=true;' ||
584 'do_alt:=false;do_dates:=true;' ||
585 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
586 --
587 -- dt delete logic
588 --
589 /* Bug 5277170 logic changes for 'Delete'(End Date) : In 'I' transaction type, t_sd and t_ed
590 are being initialised to make sure appropriate new start and end dates appear in the
591 audit report. The n_sd and n_ed need not be intialised in 'I' type as they are being assigned
592 t_sd and t_ed in 'D' type. Hence two statements have been commented. */
593
594 'elsif l_trans=''DELETE'' then ' ||
595 'if l_type= ''I'' then ' ||
596 'ict:=ict+1;' ||
597 -- 'n_sd:=l_sd;n_ed:=l_ed;' || -- bug 5277170
598 't_sd:=l_sd;t_ed:=l_ed;' ||
599 'elsif l_type=''D'' then ' ||
600 'dct:=dct+1;' ||
601 'o_sd:=l_sd;o_ed:=l_ed;' ||
602 'if l_sd<>t_sd then ' ||
603 'n_sd:=null;n_ed:=null;' ||
604 'elsif l_sd=t_sd then ' ||
605 'n_sd:=t_sd;n_ed:=t_ed;end if;' ||
606 'do_ins:=true;do_comp:=true;' ||
607 'do_alt:=false;do_dates:=true;' ||
608 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
609 'end if;' ||
610 --
611 -- dt update override logic
612 --
613 /* Bug 5277170 logic changes for 'Update Override' : After 'I' type, 'TABLE_NAME'_VP procedure
614 is called to update the new values of all the audit columns to which n_sd and n_ed are sent as
615 parameters. In the two 'I' type transactions for 'Update Override', n_sd and n_ed were null before.
616 Now, they are being assigned appropriate values so that all the audit columns' values are updated.
617 Also, do_comp is assigned 'false' in the if (l_sd=t_sd) condition in 'D' type. */
618
619 'elsif l_trans=''UPDATE_OVERRIDE'' then ' ||
620 'if l_type=''I'' then ' ||
621 'ict:=ict+1;' ||
622 'if t_sd is null then ' ||
623 't_sd:=l_sd;t_ed:=l_ed;' ||
624 'do_ins:=false;do_comp:=false;' ||
625 'do_alt:=false;do_dates:=false;' ||
626 'do_ass:=false;do_proc:=true;do_nass:=false;' ||
627 'elsif t_sd is not null and l_sd > t_sd ' ||
628 'then a_sd:=l_sd;a_ed:=l_ed;' ||
629 'n_sd:=l_sd;n_ed:=l_ed;' || -- bug 5277170
630 'do_ins:=false;do_comp:=false;' ||
631 'do_alt:=false;do_dates:=false;' ||
632 'do_ass:=false;do_proc:=true;do_nass:=false;' ||
633 'end if;' ||
634 'elsif l_type=''D'' then ' ||
635 'dct:=dct+1;' ||
636 'if l_sd=t_sd then ' ||
637 'do_ins:=true;do_comp:=false;' || -- bug 5277170
638 'do_alt:=false;do_dates:=true;' ||
639 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
640 'o_sd:=l_sd;o_ed:=l_ed;' ||
641 'n_sd:=t_sd;n_ed:=t_ed;' ||
642 'else ' ||
643 'do_ins:=true;do_comp:=true;' ||
644 'do_alt:=false;do_dates:=true;' ||
645 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
646 'o_sd:=l_sd;o_ed:=l_ed;' ||
647 'n_sd:=a_sd;n_ed:=a_ed;' ||
648 'end if;' ||
649 'end if;' ||
650 --
651 -- Multiple changes logic
652 --
653 'elsif l_trans in(''MULTIPLE_CHANGES'')then '||
654 'if l_type=''I'' then ' ||
655 'if o_dt_start is null then ' ||
656 'o_dt_start := l_sd; ' ||
657 'o_dt_end := l_ed; ' ||
658 'multiple_start_flag := true; ' ||
659 'multiple_end_flag := true; ' ||
660 'elsif l_sd > o_dt_end then ' ||
661 'o_dt_start := l_sd; ' ||
662 'o_dt_end := l_ed; ' ||
663 'multiple_end_flag := true; ' ||
664 'end if; ' ||
665 'if (multiple_start_flag=true and multiple_end_flag=true) then ' ||
666 'multiple_end_flag:=false;' ||
667 'do_ins:=true;do_comp:=true;' ||
668 'do_alt:=false;do_dates:=true;' ||
669 'do_ass:=false;do_proc:=true;do_nass:=false;'||
670 'end if; ' ||
671 'end if; ' ||
672
673 --
674 -- Error returned by ..._TT function
675 --
676 'elsif l_trans=''ERROR - Not a DateTrack Transaction'' then ' ||
677 'l_trans:=SUBSTR(l_trans,1,30);' ||
678 'ict:=ict+1;' ||
679 'if ict=1 then ' ||
680 'do_ins:=true;do_comp:=true;' ||
681 'do_alt:=false;do_dates:=false;' ||
682 'do_ass:=true;do_proc:=false;do_nass:=false;' ||
683 'end if;' ||
684 'end if;if do_ass then ';
685 --
686 --
687 -- DT TEXT 12
688 -- special dt correction logic
689 --
690 dt_text12:= 'end if;if do_proc then ' ||
691 'if l_trans=''CORRECTION'' then ' ||
692 'n_type:=''U'';' ||
693 'else n_type:=''I'';' ||
694 'end if;';
695
696 --
697 -- Call the datetrack specific procedure <TABLE_NAME>_F_VP
698 --
699 -- dt_text12a:='if (l_trans=''MULTIPLE_CHANGES'' and '||
700 -- '(multiple_start_flag=true and multiple_end_flag=true)) then ' ||
701 -- '{DT_TABLE}_VP(l_sess,l_comm,l_pkval,' ||
702 -- 'o_dt_start,o_dt_end,n_type,l_trans,l_tstamp,l_nulls,l_stat ';
703
704 dt_text12a:='if (l_trans=''MULTIPLE_CHANGES'') then '||
705 '{DT_TABLE}_VP(l_sess,l_comm,l_pkval,' ||
706 'o_dt_start,o_dt_end,n_type,l_trans,l_tstamp,l_nulls,l_stat ';
707
708 dt_text12b:='else '||
709 'if l_stat<>''OK'' then ' ||
710 '{DT_TABLE}_VP(l_sess,l_comm,l_pkval,' ||
711 'n_sd,n_ed,n_type,l_trans,l_tstamp,l_nulls,l_stat ';
712
713
714 --
715 dt_text12a := REPLACE(dt_text12a, '{DT_TABLE}', substr(P_TABLE,1,24));
716 dt_text12b := REPLACE(dt_text12b, '{DT_TABLE}', substr(P_TABLE,1,24));
717 --
718 -- DT TEXT 13
719 -- evaluation of operations and inserting into the hr_audits_table
720 --
721 dt_text13a:= 'end if;' ||
722 'if do_nass then ';
723 dt_text13:= 'if do_ins then ' ||
724 --
725 -- insert transaction details into the hr_audits table
726 --
727 'begin ' ||
728 'insert into hr_audits ' ||
729 '(audit_id,commit_id,current_session_id,primary_key,' ||
730 'primary_key_value,sequence_id,session_id,table_name,' ||
731 'timestamp,transaction,transaction_type,user_name,' ||
732 'effective_end_date,effective_start_date)' ||
733 'values ' ||
734 '(hr_audits_s.nextval,l_comm,''' || to_char(p_session_id) ||
735 ''',''' || p_primary || ''',l_pkval, l_seq, l_sess' ||
736 ',''' || p_table || ''',l_tstamp,l_trans,l_type,l_uname,' ||
737 'l_ed,l_sd);' ||
738 ' exception when others then null;raise;' ||
739 'end;' ||
740 'end if;' ||
741 'if do_alt then ' ||
742 'ins(1, st_sd, to_char(a_sd,''DD-MM-YYYY''),null);' ||
743 'ins(2, st_ed, to_char(a_ed,''DD-MM-YYYY''),null);' ||
744 'end if;' ||
745 'if do_dates then ' ||
746 'if (l_trans=''MULTIPLE_CHANGES'') then ' ||
747 'ins(3,''From Date'',null,to_char(o_dt_start,''DD-MM-YYYY''));'||
748 'ins(4,''To Date'',null,to_char(o_dt_end,''DD-MM-YYYY''));'||
749 'else ' ||
750 'ins(3,''From Date'',to_char(o_sd,''DD-MM-YYYY''),' ||
751 'to_char(n_sd,''DD-MM-YYYY''));' ||
752 'ins(4,''To Date'',to_char(o_ed,''DD-MM-YYYY''),' ||
753 'to_char(n_ed,''DD-MM-YYYY''));' ||
754 'end if;' ||
755 'end if;if do_comp then ';
756 --
757 -- DT TEXT 14
758 -- commit operations and exception handling
759 --
760 dt_text14:= 'end if;commit;l_grkey_prv:=l_grkey_cur;end loop;exception ' ||
761 'when others then close dt_curs;' ||
762 'hr_utility.set_location(''ERROR:''||' ||
763 'to_char(dbms_sql.last_sql_function_code),00);' ||
764 'hr_utility.set_location(''ERROR:''||' ||
765 'to_char(dbms_sql.last_error_position),00);' ||
766 'l_err:=hr_utility.get_message;' ||
767 'hr_utility.trace(l_err);' ||
768 'raise;end;';
769 --
770 -- open the column_curs cursor fetching in column information and
771 -- building up the dynamic text for each column
772 --
773 open column_curs;
774 --
775 -- initialise loop counter
776 --
777 t_loop_count := 0;
778 --
779 loop
780 --
781 -- fetch column information
782 --
783 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',10);
784 fetch column_curs into t_column_name,
785 t_column_id,
786 t_column_type,
787 t_column_width;
788 if column_curs%notfound then
789 close column_curs;
790 exit;
791 end if;
792 --
793 -- increment the count
794 --
795 t_loop_count := t_loop_count + 1;
796 --
797 -- build up cursor text using the column details retrieved
798 --
799 dt_text2 := dt_text2 || 'L' || to_char(t_loop_count);
800 dt_text3 := dt_text3 || 'O' || to_char(t_loop_count);
801 dt_text4 := dt_text4 || 'N' || to_char(t_loop_count);
802 dt_text13a := dt_text13a || 'N' || to_char(t_loop_count) || ':=O' ||
803 to_char(t_loop_count) || ';O' || to_char(t_loop_count) ||
804 ':=L' || to_char(t_loop_count) || ';';
805 --
806 -- special logic for varchar2 columns over the length of 240
807 --
808 if t_column_type = 'V' and t_column_width > 240 then
809 dt_text5:= dt_text5 || ',substr(' || t_column_name || ',1,240)';
810 else
811 dt_text5:= dt_text5 || ',' || t_column_name ;
812 end if;
813 --
814 dt_text6 := dt_text6 || ',L' || to_char(t_loop_count);
815 dt_text7 := dt_text7 || 'O' || to_char(t_loop_count) || ':=null;';
816 dt_text8 := dt_text8 || 'N' || to_char(t_loop_count) || ':=null;';
817 dt_text11 :=dt_text11 || 'O' || to_char(t_loop_count) ||
818 ':=L' || to_char(t_loop_count) || ';';
819 dt_text12a :=dt_text12a || ',N' || to_char(t_loop_count);
820 dt_text12b :=dt_text12b || ',N' || to_char(t_loop_count);
821 --
822 -- date column logic
823 --
824 if t_column_type = 'D' then
825 dt_text2 := dt_text2 || ' date;';
826 dt_text3 := dt_text3 || ' date;';
827 dt_text4 := dt_text4 || ' date;';
828 dt_text13 := dt_text13 || 'if dv(to_char(O' ||
829 to_char(t_loop_count) ||
830 ',''DD-MM-YYYY''),to_char(N' ||
831 to_char(t_loop_count) ||
832 ',''DD-MM-YYYY'')' ||
833 ',' || to_char(t_loop_count) || ') ';
834 --
835 -- varchar2 column logic
836 --
837 elsif t_column_type = 'V' then
838 if t_column_width >= 240 then
839 dt_text2 := dt_text2 || ' varchar2(240);';
840 dt_text3 := dt_text3 || ' varchar2(240);';
841 dt_text4 := dt_text4 || ' varchar2(240);';
842 else
843 dt_text2 := dt_text2 || ' varchar2(' ||
844 to_char(t_column_width) || ');';
845 dt_text3 := dt_text3 || ' varchar2(' ||
846 to_char(t_column_width) || ');';
847 dt_text4 := dt_text4 || ' varchar2(' ||
848 to_char(t_column_width) || ');';
849 end if;
850 dt_text13 := dt_text13 || 'if dv(O' || to_char(t_loop_count) ||
851 ',N' || to_char(t_loop_count) ||
852 ',' || to_char(t_loop_count) || ') ';
853 --
854 -- number column logic
855 --
856 elsif t_column_type = 'N' then
857 dt_text2 := dt_text2 || ' number;';
858 dt_text3 := dt_text3 || ' number;';
859 dt_text4 := dt_text4 || ' number;';
860 dt_text13 := dt_text13 || 'if dn(O' || to_char(t_loop_count) ||
861 ',N' || to_char(t_loop_count) ||
862 ',' || to_char(t_loop_count) || ') ';
863 end if;
864 dt_text13 := dt_text13 ||
865 ' then ' ||
866 'ins(' || t_column_id || ',''' || t_column_name || ''',' ||
867 'O' || to_char(t_loop_count) || ',N' ||
868 to_char(t_loop_count) || ');end if;';
869 end loop;
870 --
871 -- assign the ends of the text
872 --
873 dt_text12a := dt_text12a || ');';
874 dt_text12b := dt_text12b || ');end if; end if;';
875 dt_text6 := dt_text6 || ';';
876 --
877 -- if there were column cursor rows fetched, assign the ends of the text
878 --
879 if t_loop_count > 0 then
880 dt_text13a := dt_text13a || 'null; end if;';
881 end if;
882 --
883 -- if there were no column cursor rows fetched, assign the primary key rules
884 if t_loop_count = 0 then
885 dt_text10 := dt_text10 || ' null;';
886 dt_text13 := dt_text13 || ' null; end if;';
887 end if;
888 --
889 -- CONCATENATE DATETRACK DYNAMIC SQL TEXT
890 --
891 --
892 -- flemonni 630622
893 --
894 -- put this in its own block, so that if variable length is exceeded can raise
895 -- a specific error
896 --
897 BEGIN
898 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',20);
899 dt_proc_cursor_text := dt_text1 ||
900 dt_text2 ||
901 dt_text3 ||
902 dt_text4 ||
903 dt_text5 ||
904 dt_text6 ||
905 dt_text7 ||
906 dt_text8 ||
907 dt_text9 ||
908 dt_text10 ||
909 dt_text11 ||
910 dt_text12 ||
911 dt_text12a ||
912 dt_text12b ||
913 dt_text13a ||
914 dt_text13 ||
915 dt_text14;
916 EXCEPTION
917 WHEN OTHERS THEN
918 g_audit_table := p_table;
919 RAISE g_parse_string_too_big;
920 END;
921 --
922 -- open parse and execute the dynamic sql cursor text
923 --
924 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',30);
925 dt_proc_cursor := dbms_sql.open_cursor;
926 --
927 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',40);
928 dbms_sql.parse(dt_proc_cursor, dt_proc_cursor_text, dbms_sql.v7);
929 --
930 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',50);
931 dt_proc_rows := dbms_sql.execute(dt_proc_cursor);
932 --
933 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',60);
934 if dbms_sql.is_open(dt_proc_cursor) then
935 dbms_sql.close_cursor(dt_proc_cursor);
936 end if;
937 --
938 -- commit all transactions
939 --
940 commit;
941 --
942 -- NON-DATETRACK TABLE OPERATIONS
943 --
944 elsif p_table_type = 'NDT' then
945 --
946 -- assignments of procedure cursor text
947 --
948 -- l_trans non-datetrack_transaction
949 -- l_tstamp audit_timestamp
950 -- l_type audit_transaction_type
951 -- l_uname audit_user_name
952 -- l_nulls audit_true_nulls
953 -- l_sess audit_session_id
954 -- l_comm audit_commit_id
955 -- l_seq audit_sequence_id
956 -- l_pkval primary_key_value current
957 -- l_prev_pk primary_key_value previous
958 -- l_hist history_check_field
959 --
960 -- NDT TEXT 1
961 --
962 ndt_text1 := 'declare l_err varchar2(100);' ||
963 'l_trans varchar2(40);' ||
964 'l_tstamp date;' ||
965 'l_type varchar2(1);' ||
966 'l_uname varchar2(100);' ||
967 'l_nulls varchar2(250);' ||
968 'l_sess number;' ||
969 'l_comm number;' ||
970 'l_seq number;' ||
971 'l_hist varchar2(40);' ||
972 'l_prev_pk number;' ||
973 'l_pkval number;' ||
974 'l_audit number;';
975 --
976 -- NDT TEXT 4
977 --
978 ndt_text4 := 'cursor ndt_curs is select ' ||
979 'audit_timestamp,' ||
980 'audit_transaction_type,' ||
981 'audit_user_name,' ||
982 'audit_true_nulls,' ||
983 'audit_session_id,' ||
984 'audit_commit_id,' ||
985 'audit_sequence_id,' ||
986 '{NDT_PRIMARY} ';
987 --
988 ndt_text4 := replace(ndt_text4, '{NDT_PRIMARY}', P_PRIMARY);
989 --
990 -- NDT TEXT 5
991 --
992 ndt_text5 := ' from {NDT_TABLE}_A ' ||
993 'where audit_timestamp >= ' ||
994 'to_date(''' || P_START_DATE ||
995 ''',''DD-MM-YYYY HH24:MI'') ' ||
996 'and audit_timestamp < ' ||
997 'to_date(''' || P_END_DATE ||
998 ''',''DD-MM-YYYY HH24:MI'') ' ||
999 'and audit_user_name like ''' || P_USERNAME ||
1000 ''' order by {NDT_PRIMARY} asc,' ||
1001 'audit_timestamp desc;' ||
1002 --
1003 -- nondatetrack insert procedure for the hr_audits_columns table
1004 --
1005 'procedure ins(c_id in number,' ||
1006 'c_name in varchar2,' ||
1007 'o_val in varchar2,' ||
1008 'n_val in varchar2) is ' ||
1009 'l_c_id number;l_c_name varchar2(30);' ||
1010 'BEGIN ' ||
1011 'IF c_id IS NULL THEN l_c_id:=0; ' ||
1012 'ELSE l_c_id:=c_id;END IF;' ||
1013 'IF c_name IS NULL THEN l_c_name:=''***'' || ' ||
1014 ' to_char(l_c_id);ELSE l_c_name:=c_name;END IF;'||
1015 'insert into hr_audit_columns ' ||
1016 '(audit_id,column_id,column_name,old_value,new_value)' ||
1017 'values(hr_audits_s.currval,' ||
1018 'l_c_id,' ||
1019 'l_c_name,' ||
1020 'o_val,n_val);' ||
1021 'exception when others then null;raise;' ||
1022 'end;';
1023 --
1024 ndt_text5 := replace(ndt_text5, '{NDT_TABLE}', substr(P_TABLE,1,24));
1025 ndt_text5 := replace(ndt_text5, '{NDT_PRIMARY}', P_PRIMARY);
1026 --
1027 -- NDT TEXT 6
1028 -- define procedure for obtaining new values in non datetrack mode
1029 --
1030 ndt_text6 := ' procedure hist_values (P_{NDT_PRIMARY} in out number,' ||
1031 'P_TYPE in varchar2, ' ||
1032 'P_HIST in out varchar2)';
1033 --
1034 ndt_text6 := replace(ndt_text6, '{NDT_PRIMARY}', P_PRIMARY);
1035 --
1036 -- NDT TEXT 7
1037 --
1038 ndt_text7 := ' is status_field varchar2(40);';
1039 --
1040 -- NDT TEXT 8
1041 --
1042 -- NDT TEXT 9
1043 --
1044 -- NDT TEXT 10
1045 -- definition of the base table search cursor
1046 --
1047 ndt_text10:= ' cursor base_curs is select ';
1048 --
1049 -- NDT TEXT 11
1050 --
1051 ndt_text11:= ' from {NDT_TABLE} where {NDT_PRIMARY} = P_{NDT_PRIMARY};' ||
1052 'begin status_field:=''OK'';';
1053 --
1054 ndt_text11:= replace(ndt_text11, '{NDT_TABLE}', P_TABLE);
1055 ndt_text11:= replace(ndt_text11, '{NDT_PRIMARY}', P_PRIMARY);
1056 --
1057 -- NDT TEXT 12
1058 --
1059 ndt_text12:= 'if p_type = ''D'' then ';
1060 --
1061 -- NDT TEXT 13
1062 -- if there is nothing found under the shadow table
1063 --
1064 ndt_text13:= ' elsif p_type = ''U'' or p_type = ''I'' then ' ||
1065 'open base_curs; ' ||
1066 'loop ' ||
1067 'fetch base_curs into ';
1068 --
1069 -- NDT TEXT 14
1070 -- if there is nothing found under the base table
1071 --
1072 ndt_text14:= 'if base_curs%notfound then ' ||
1073 'status_field:=''ERROR - NO VALUES IN BASE '';' ||
1074 'p_hist := status_field;' ||
1075 'end if;exit;end loop;close base_curs;' ||
1076 'end if; ' ||
1077 'end;';
1078 --
1079 -- NDT TEXT 15
1080 --
1081 ndt_text15:= 'begin ' ||
1082 'select hr_audits_s.nextval into ' ||
1083 'l_audit from dual;' ||
1084 'open ndt_curs;' ||
1085 'loop ' ||
1086 'fetch ndt_curs into l_tstamp,' ||
1087 'l_type,' ||
1088 'l_uname,' ||
1089 'l_nulls,' ||
1090 'l_sess,' ||
1091 'l_comm,' ||
1092 'l_seq,' ||
1093 'l_pkval';
1094 --
1095 -- NDT TEXT 16
1096 --
1097 ndt_text16:= 'if ndt_curs%notfound then close ndt_curs;' ||
1098 'exit;end if;';
1099 --
1100 -- NDT TEXT 17
1101 -- evaluation of the non datetrack audit transaction types
1102 --
1103 ndt_text17:= 'if l_prev_pk is null or l_pkval <> l_prev_pk then ' ||
1104 'l_hist:= ''OK'';' ||
1105 'hist_values(l_pkval, l_type, l_hist);end if;' ||
1106 'if substr(l_hist,1,2) = ''OK'' then ' ||
1107 'if l_type=''I'' then ' ||
1108 'l_trans :=''NORMAL_INSERT'';' ||
1109 'elsif l_type=''U'' then ' ||
1110 'l_trans :=''NORMAL_UPDATE'';' ||
1111 'elsif l_type=''D'' then ' ||
1112 'l_trans :=''NORMAL_DELETE'';' ||
1113 'end if;';
1114 --
1115 -- NDT TEXT 18
1116 -- inserting into the hr_audits_table
1117 --
1118 --
1119 -- insert transaction details into the hr_audits table
1120 --
1121 ndt_text18:= 'begin ' ||
1122 'insert into hr_audits ' ||
1123 '(audit_id,commit_id,current_session_id,primary_key,' ||
1124 'primary_key_value,sequence_id,session_id,table_name,' ||
1125 'timestamp,transaction,transaction_type,user_name,' ||
1126 'effective_end_date,effective_start_date)' ||
1127 'values ' ||
1128 '(hr_audits_s.nextval,l_comm,''' || to_char(p_session_id) ||
1129 ''',''' || p_primary || ''',l_pkval, l_seq, l_sess' ||
1130 ',''' || p_table || ''',l_tstamp,l_trans,l_type,l_uname,' ||
1131 'null,null);' ||
1132 'exception when others then null;raise;end;';
1133 --
1134 -- NDT TEXT 19
1135 --
1136 ndt_text19 := 'if l_type = ''D'' then ';
1137 --
1138 -- NDT TEXT 21
1139 -- commit operations and exception handling
1140 --
1141 ndt_text21 := 'elsif l_type = ''I'' then null;end if;' ||
1142 'commit;end if;l_prev_pk:=l_pkval;' ||
1143 'end loop;exception when others then close ndt_curs;' ||
1144 'hr_utility.set_location(''ERROR:''||' ||
1145 'to_char(dbms_sql.last_sql_function_code),00);' ||
1146 'hr_utility.set_location(''ERROR:''||' ||
1147 'to_char(dbms_sql.last_error_position),00);' ||
1148 'l_err:=hr_utility.get_message;' ||
1149 'hr_utility.trace(l_err);' ||
1150 'end;';
1151 --
1152 -- open the column_curs cursor fetching in column information and
1153 -- building up the dynamic text for each column
1154 --
1155 open column_curs;
1156 --
1157 -- initialise loop counter
1158 --
1159 t_loop_count := 0;
1160 --
1161 loop
1162 --
1163 -- fetch column information
1164 --
1165 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',60);
1166 fetch column_curs into t_column_name,
1167 t_column_id,
1168 t_column_type,
1169 t_column_width;
1170 -- if no rows are fetched in use only the primary key to drive off
1171 if column_curs%notfound then
1172 close column_curs;
1173 exit;
1174 end if;
1175 --
1176 -- increment the count
1177 --
1178 t_loop_count := t_loop_count + 1;
1179 --
1180 -- build up cursor text using the column details retrieved
1181 --
1182 ndt_text2 := ndt_text2 || 'O' || to_char(t_loop_count);
1183 ndt_text3 := ndt_text3 || 'N' || to_char(t_loop_count);
1184 --
1185 -- special logic for varchar2 columns over the length of 240
1186 --
1187 if t_column_type = 'V' and t_column_width > 240 then
1188 ndt_text4 := ndt_text4 || ', substr(' || t_column_name || ',1,240)';
1189 else
1190 ndt_text4 := ndt_text4 || ',' || t_column_name ;
1191 end if;
1192 ndt_text15 := ndt_text15 || ',O' || to_char(t_loop_count);
1193 ndt_text18 := ndt_text18 || 'if ' ||
1194 '(O' ||
1195 to_char(t_loop_count) ||
1196 ' is not null and N' || to_char(t_loop_count) ||
1197 ' is not null and O' || to_char(t_loop_count) ||
1198 '<>N' || to_char(t_loop_count) || ')' ||
1199 ' or ' ||
1200 '(O' ||
1201 to_char(t_loop_count) ||
1202 ' is null and N' || to_char(t_loop_count) ||
1203 ' is not null and ((l_nulls is not null and ' ||
1204 'substr(l_nulls,' || to_char(t_loop_count+1) ||
1205 ',1)=''Y'') or (l_nulls is null and l_type=''I'')))' ||
1206 ' or ' ||
1207 '(O' ||
1208 to_char(t_loop_count) ||
1209 ' is not null and N' || to_char(t_loop_count) ||
1210 ' is null)' ||
1211 ' then ins(' ||
1212 t_column_id || ',''' || t_column_name || ''',' ;
1213 ndt_text19 := ndt_text19 || 'N' || to_char(t_loop_count) || ':=O' ||
1214 to_char(t_loop_count) || ';';
1215 ndt_text20 := ndt_text20 || 'if O' || to_char(t_loop_count) ||
1216 ' is not null and O' ||
1217 to_char(t_loop_count) ||
1218 '<> N' ||
1219 to_char(t_loop_count) || ' then N' ||
1220 to_char(t_loop_count) || ':=O' ||
1221 to_char(t_loop_count) ||
1222 ';end if;';
1223 ndt_text20 := ndt_text20 || 'if l_nulls is not null and substr(l_nulls,'||
1224 to_char(t_loop_count+1) || ',1)=''Y'' and O' ||
1225 to_char(t_loop_count) || ' is null then N' ||
1226 to_char(t_loop_count) || ':=null; end if;';
1227 --
1228 -- a condition to cover the first time the loop is entered as
1229 -- the texts require a different concatenation
1230 --
1231 if t_loop_count = 1 then
1232 ndt_text10 := ndt_text10 || t_column_name;
1233 ndt_text12 := ndt_text12 || 'N' || to_char(t_loop_count) || ':=null;';
1234 ndt_text13 := ndt_text13 || 'N' || to_char(t_loop_count);
1235 else
1236 ndt_text10 := ndt_text10 || ',' || t_column_name;
1237 ndt_text12 := ndt_text12 || 'N' || to_char(t_loop_count) || ':=null;';
1238 ndt_text13 := ndt_text13 || ',N' || to_char(t_loop_count);
1239 end if;
1240 --
1241 -- date column logic
1242 --
1243 if t_column_type = 'D' then
1244 ndt_text2 := ndt_text2 || ' date;';
1245 ndt_text3 := ndt_text3 || ' date;';
1246 ndt_text18 := ndt_text18 || ' to_char(O' || to_char(t_loop_count) ||
1247 ',''DD-MM-YYYY'') ' || ', to_char(N' || to_char(t_loop_count) ||
1248 ',''DD-MM-YYYY''));end if;';
1249 --
1250 -- varchar2 column logic
1251 --
1252 elsif t_column_type = 'V' then
1253 if t_column_width >= 240 then
1254 ndt_text2 := ndt_text2 || ' VARCHAR(240);';
1255 ndt_text3 := ndt_text3 || ' VARCHAR(240);';
1256 else
1257 ndt_text2 := ndt_text2 || ' varchar2(' ||
1258 to_char(t_column_width) || ');';
1259 ndt_text3 := ndt_text3 || ' varchar2(' ||
1260 to_char(t_column_width) || ');';
1261 end if;
1262 ndt_text18 := ndt_text18 || ' O' || to_char(t_loop_count) ||
1263 ',N' || to_char(t_loop_count) ||');end if;';
1264 --
1265 -- number column logic
1266 --
1267 elsif t_column_type = 'N' then
1268 ndt_text2 := ndt_text2 || ' number;';
1269 ndt_text3 := ndt_text3 || ' number;';
1270 ndt_text18 := ndt_text18 || ' to_char(O' || to_char(t_loop_count) ||
1271 '),to_char(N' || to_char(t_loop_count) || '));end if;';
1272 end if;
1273 end loop;
1274 --
1275 -- if there were column cursor rows fetched in assign the ends of the text
1276 --
1277 ndt_text15 := ndt_text15 || ';';
1278
1279 if t_loop_count > 0 then
1280 ndt_text13 := ndt_text13 || ';';
1281 -- if there were no column cursor rows fetched, assign the primary key rules
1282 else
1283 ndt_text10 := ndt_text10 || ' {NDT_PRIMARY} ';
1284 ndt_text10 := replace(ndt_text10, '{NDT_PRIMARY}', P_PRIMARY);
1285 ndt_text12 := ndt_text12 || ' null; ';
1286 ndt_text13 := ndt_text13 || ' P_{NDT_PRIMARY};';
1287 ndt_text13 := replace(ndt_text13, '{NDT_PRIMARY}', P_PRIMARY);
1288 ndt_text19 := ndt_text19 || 'null; ';
1289 ndt_text20 := 'null; ';
1290 end if;
1291 --
1292 ndt_text19 := ndt_text19 || 'elsif l_type = ''U'' then ';
1293 --
1294 -- CONCATENATE NON-DATETRACK DYNAMIC SQL TEXT
1295 --
1296 --
1297 -- flemonni 630622
1298 --
1299 -- put this in its own block, so that if variable length is exceeded can raise
1300 -- a specific error
1301 --
1302 BEGIN
1303 ndt_proc_cursor_text := ndt_text1 ||
1304 ndt_text2 ||
1305 ndt_text3 ||
1306 ndt_text4 ||
1307 ndt_text5 ||
1308 ndt_text6 ||
1309 ndt_text7 ||
1310 ndt_text8 ||
1311 ndt_text9 ||
1312 ndt_text10 ||
1313 ndt_text11 ||
1314 ndt_text12 ||
1315 ndt_text13 ||
1316 ndt_text14 ||
1317 ndt_text15 ||
1318 ndt_text16 ||
1319 ndt_text17 ||
1320 ndt_text18 ||
1321 ndt_text19 ||
1322 ndt_text20 ||
1323 ndt_text21;
1324
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 g_audit_table := p_table;
1328 RAISE g_parse_string_too_big;
1329 END;
1330
1331 --
1332 -- open parse and execute the dynamic sql cursor text
1333 --
1334 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',70);
1335 ndt_proc_cursor := dbms_sql.open_CURSOR;
1336 --
1337 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',80);
1338 dbms_sql.parse(ndt_proc_cursor, ndt_proc_cursor_text, dbms_sql.v7);
1339 --
1340 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',90);
1341 ndt_proc_rows := dbms_sql.EXECUTE(ndt_proc_cursor);
1342 --
1343 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',100);
1344 if dbms_sql.is_open(ndt_proc_cursor) then
1345 dbms_sql.close_CURSOR(ndt_proc_cursor);
1346 end if;
1347 --
1348 -- commit all transactions
1349 --
1350 commit;
1351 end if;
1352 --
1353 -- EXCEPTION HANDLING
1354 -- close down cursors when the exception is raised
1355 --
1356 exception
1357 --
1358 -- flemonni 630622
1359 --
1360 --
1361 WHEN g_parse_string_too_big THEN
1362 hr_utility.set_location('py_audit_rep_pkg.py_audit_rep_proc',101);
1363 fnd_message.set_name('PER', 'PER_52348_AUDIT_SQL_TOO_LARGE');
1364 fnd_message.set_token ('AUDITTABLE', g_audit_table);
1365 --
1366 RAISE;
1367 when others then
1368 --
1369 -- if the datetrack cursor is open close it.
1370 --
1371 if dbms_sql.is_open(dt_proc_cursor) then
1372 dbms_sql.close_cursor(dt_proc_cursor);
1373 end if;
1374 --
1375 -- if the non datetrack cursor is open close it
1376 --
1377 if dbms_sql.is_open(ndt_proc_cursor) then
1378 dbms_sql.close_cursor(ndt_proc_cursor);
1379 end if;
1380 --
1381 -- display the error
1382 --
1383 hr_utility.set_location('error is : ' ||
1384 to_char(dbms_sql.last_sql_function_code),00);
1385 hr_utility.set_location('error is : ' ||
1386 to_char(dbms_sql.last_error_position),00);
1387 error_message := hr_utility.get_message;
1388 hr_utility.trace(error_message);
1389 hr_utility.trace('all cursors closed');
1390 raise;
1391 end py_audit_rep_proc;
1392 end py_audit_rep_pkg;