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.
PACKAGE BODY: APPS.XLE_HISTORY_PUB

Source


1 PACKAGE BODY XLE_History_PUB AS
2 /* $Header: xlehispb.pls 120.8.12020000.2 2012/11/30 05:52:39 srampure ship $ */
3 
4 PROCEDURE get_record_snapshot(
5     p_id NUMBER,
6     p_primary_key_name VARCHAR2,
7     p_table_name VARCHAR2,
8     p_mode VARCHAR2) IS
9 
10     -- statement that retrieves the name and type of all the columns from p_table_name
11     l_select_col_stmt VARCHAR2(4000);
12     -- statement that retrieves a row with PK value p_id from p_table_name
13     l_select_val_stmt VARCHAR2(4000);
14     l_cursor INTEGER;
15     l_column_name ALL_TAB_COLUMNS.column_name%TYPE;
16     l_data_type ALL_TAB_COLUMNS.data_type%TYPE;
17     l_dummy INTEGER;
18     l_index	NUMBER:=1;
19 
20 BEGIN
21     l_cursor := DBMS_SQL.OPEN_CURSOR;
22     l_select_col_stmt := 'select col.column_name, col.data_type from user_synonyms syn, ALL_TAB_COLUMNS col'
23         || ' where syn.synonym_name = :tab_name and col.owner =  syn.table_owner and col.table_name = syn.table_name order by COL.column_id';
24     l_select_val_stmt := 'select';
25 
26     DBMS_SQL.PARSE(l_cursor, l_select_col_stmt, DBMS_SQL.V7);
27     DBMS_SQL.BIND_VARIABLE(l_cursor, ':tab_name', p_table_name);
28     DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_column_name, 30);
29     DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, l_data_type, 106);
30 
31     l_dummy := DBMS_SQL.EXECUTE(l_cursor);
32 
33     l_index := 1;
34 
35     LOOP
36         IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
37             EXIT;
38         END IF;
39 
40         DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_column_name);
41         DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_data_type);
42 
43         -- some standard columns are not tracked
44         IF (l_column_name NOT IN ('CREATED_BY',
45                                   'CREATION_DATE',
46                                   'LAST_UPDATED_BY',
47                                   'LAST_UPDATE_DATE',
48                                   'LAST_UPDATE_LOGIN',
49                                   'OBJECT_VERSION_NUMBER')) THEN
50             IF (p_mode = 'PRE') THEN
51                 G_VALUE_LIST(l_index).column_name := l_column_name;
52                 G_VALUE_LIST(l_index).data_type := l_data_type;
53             ELSE
54                 -- in POST mode, only the snapshot of the SAME record is taken
55                 IF (G_VALUE_LIST(l_index).column_name <> l_column_name)
56                 THEN
57                     RAISE FND_API.G_EXC_ERROR;
58                 END IF;
59             END IF;
60 
61             -- handle special first case
62             IF (length(l_select_val_stmt) = 6) THEN
63                 l_select_val_stmt := l_select_val_stmt || ' ';
64             ELSE
65                 l_select_val_stmt := l_select_val_stmt || ', ';
66             END IF;
67 
68             IF (l_data_type = 'VARCHAR2') THEN
69                 l_select_val_stmt := l_select_val_stmt || l_column_name;
70             ELSIF (l_data_type = 'NUMBER') THEN
71                 l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_name || ')';
72             ELSIF (l_data_Type = 'DATE') THEN
73                 l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_Name || ', ''DD-MON-YYYY HH24:MI:SS'')';
74             ELSE
75                 l_select_val_stmt := l_select_val_stmt || l_column_name;
76             END IF;
77             l_index := l_index + 1;
78         END IF;
79     END LOOP;
80     l_index := G_VALUE_LIST.count;
81 
82     DBMS_SQL.CLOSE_CURSOR(l_cursor);
83 
84     IF (length(l_select_val_stmt) > 6) THEN
85         l_select_val_stmt := l_select_val_stmt || ' from ' || p_table_name || '
86 where ';
87         l_select_val_stmt := l_select_val_stmt || p_primary_key_name || ' = :p_id';
88 
89         l_cursor := DBMS_SQL.OPEN_CURSOR;
90 
91         DBMS_SQL.PARSE(l_cursor, l_select_val_stmt, DBMS_SQL.V7);
92         DBMS_SQL.BIND_VARIABLE(l_cursor, ':p_id', p_id);
93 
94 --log_csc_form_debug_message('Shikha','select'||l_select_val_stmt);
95 --log_csc_form_debug_message('Shikha','cursor'||l_cursor);
96         FOR i IN 1..l_index LOOP
97             IF (p_mode = 'PRE') THEN
98                 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, G_VALUE_LIST(i).old_value, 2000);
99             ELSE
100                 DBMS_SQL.DEFINE_COLUMN(l_cursor, i, G_VALUE_LIST(i).new_value, 2000);
101             END IF;
102         END LOOP;
103 
104         l_dummy := DBMS_SQL.EXECUTE(l_cursor);
105 
106         IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
107 --log_csc_form_debug_message('Shikha','inside fetch'||l_cursor);
108             RAISE NO_DATA_FOUND;
109         END IF;
110 
111         FOR i IN 1..l_index LOOP
112             IF (p_mode = 'PRE') THEN
113                 DBMS_SQL.COLUMN_VALUE(l_cursor, i, G_VALUE_LIST(i).old_value);
114             ELSE
115                 DBMS_SQL.COLUMN_VALUE(l_cursor, i, G_VALUE_LIST(i).new_value);
116             END IF;
117         END LOOP;
118 
119         DBMS_SQL.CLOSE_CURSOR(l_cursor);
120         ELSE
121             RAISE FND_API.G_EXC_ERROR;
122         END IF;
123 EXCEPTION
124 WHEN OTHERS THEN
125     DBMS_SQL.CLOSE_CURSOR(l_cursor);
126     RAISE;
127 END get_record_snapshot;
128 
129 PROCEDURE log_changes(
130     p_effective_from DATE,
131     p_comment VARCHAR2,
132     p_return_status  OUT NOCOPY  VARCHAR2,
133     p_error_type     OUT NOCOPY  VARCHAR2) IS
134 
135     l_hist_id NUMBER := NULL;
136     l_history_id NUMBER := NULL;
137     l_flag Varchar2(5):='N';
138     l_eff_flag Varchar2(5):='N';
139     l_eff varchar2(2000);
140     l_eff_from Date;
141     l_count Number:=0;
142     l_column_name Varchar2(200);
143 
144     v_chk varchar2(1):='0';
145     v_chk2 varchar2(1):='0';
146 
147     l_start_date Date;
148 
149     cursor eff_from is
150     select effective_from
151     from xle_histories
152     where  source_id=G_PRIMARY_KEY_ID
153     and source_table=G_TABLE_NAME
154     and source_column_name=l_column_name
155     and effective_to is null;
156 BEGIN
157 
158     FOR i IN 1..G_VALUE_LIST.count LOOP
159         IF (G_VALUE_LIST(i).old_value <> G_VALUE_LIST(i).new_value
160             OR (G_VALUE_LIST(i).old_value IS NULL AND G_VALUE_LIST(i).new_value
161 IS NOT NULL)
162             OR (G_VALUE_LIST(i).old_value IS NOT NULL AND G_VALUE_LIST(i).new_value IS NULL))
163         THEN
164 
165             l_count:=l_count+1;
166             l_column_name:=G_VALUE_LIST(i).column_name;
167 
168            For eff_from_r in eff_from loop
169                 If p_effective_from<eff_from_r.effective_from then
170                   l_eff_flag:='Y';
171                 End If;
172            End loop;
173 
174            If G_VALUE_LIST(i).column_name='EFFECTIVE_TO' then
175              l_flag:='Y';
176            --l_eff:=FND_DATE.CANONICAL_TO_DATE(G_VALUE_LIST(i).new_value);
177              l_eff:=G_VALUE_LIST(i).new_value;
178            End if;
179 
180             v_chk:='0';
181             v_chk2:='0';
182 
183               begin
184                 select effective_from into l_start_date
185                 from  xle_registrations
186                 where registration_id = G_PRIMARY_KEY_ID
187                 and rownum < 2;
188                exception
189                  when NO_DATA_FOUND then
190                   l_start_date:=SYSDATE;
191                end;
192 
193 
194             begin
195               select '1' into v_chk
196               from xle_histories
197               where source_id =G_PRIMARY_KEY_ID
198               and source_table=G_TABLE_NAME
199               and source_column_name=G_VALUE_LIST(i).column_name
200               and rownum <2;
201             exception
202               when NO_DATA_FOUND THEN
203 
204                            XLE_Histories_PKG.Insert_Row(
205                   x_history_id => l_hist_id,
206                   p_source_table => G_TABLE_NAME,
207                   p_source_id => G_PRIMARY_KEY_ID,
208                   p_source_column_name => G_VALUE_LIST(i).column_name,
209                   p_source_column_value => G_VALUE_LIST(i).old_value,
210                   p_effective_from => l_start_date,
211                   p_effective_to => p_effective_from,
212                   p_comment => 'Creation',
213                   p_object_version_number => 1);
214 
215             end;
216 
217 
218                   delete from xle_histories
219                   where source_id=G_PRIMARY_KEY_ID
220                   and source_table=G_TABLE_NAME
221                   and source_column_name=G_VALUE_LIST(i).column_name
222                   and effective_from > nvl(p_effective_from,sysdate);
223 
224                   if sql%rowcount > 0 then
225                    /*  XLE_Histories_PKG.Insert_Row(
226                          x_history_id => l_history_id,
227                          p_source_table => G_TABLE_NAME,
228                          p_source_id => G_PRIMARY_KEY_ID,
229                          p_source_column_name => G_VALUE_LIST(i).column_name,
230                          p_source_column_value => G_VALUE_LIST(i).new_value,
231                          p_effective_from => l_start_date,
232                          p_comment => 'Creation',
233                          p_object_version_number => 1);*/
234 
235                     v_chk2:='1';
236 
237                   end if;
238 
239             if (v_chk2='0') then
240 
241              update XLE_Histories
242              set effective_to=decode(trunc(effective_from),trunc(nvl(p_effective_from,sysdate)),(nvl2(p_effective_from,p_effective_from-(1/86400),sysdate-(1/86400))),nvl(p_effective_from,sysdate)-1)
243              where source_id=G_PRIMARY_KEY_ID
244              and source_table=G_TABLE_NAME
245              and source_column_name=G_VALUE_LIST(i).column_name
246              and effective_to is null;
247 
248             end if;
249 
250              XLE_Histories_PKG.Insert_Row(
251                  x_history_id => l_history_id,
252                  p_source_table => G_TABLE_NAME,
253                  p_source_id => G_PRIMARY_KEY_ID,
254                  p_source_column_name => G_VALUE_LIST(i).column_name,
255                  p_source_column_value => G_VALUE_LIST(i).new_value,
256                  p_effective_from => p_effective_from,
257                  p_comment => p_comment,
258                  p_object_version_number => 1);
259 
260 
261 
262              -- TODO: update the effective_from of the previous record to p_effective_from - 1
263         END IF;
264     END LOOP;
265 
266         If l_flag='Y' then
267            update  xle_histories
268            SET effective_to =to_date(to_char(to_date(l_eff, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MM-YYYY'), 'DD-MM-YYYY')
269            where source_id=G_PRIMARY_KEY_ID
270            and source_table=G_TABLE_NAME
271            and effective_to is null;
272 
273             IF (sql%notfound) THEN
274                 RAISE no_data_found;
275             END IF;
276         End if;
277 
278          If l_count>1 and l_eff_flag='Y' then
279             p_return_status:='E';
280             p_error_type := 'DataError';
281          ElsIf l_count=1 and l_eff_flag='Y' and v_chk2='0' then
282              Delete from xle_histories
283              where source_id=G_PRIMARY_KEY_ID
284              and source_table=G_TABLE_NAME
285              and source_column_name=l_column_name
286              and effective_from >  p_effective_from ;
287          End If;
288 
289 EXCEPTION
290 WHEN OTHERS THEN
291     RAISE;
292 
293 END log_changes;
294 
295 PROCEDURE log_record_pre(
296     p_id NUMBER,
297     p_primary_key_name VARCHAR2,
298     p_table_name VARCHAR2) IS
299 BEGIN
300     -- reset the table
301     G_VALUE_LIST.delete;
302     G_PRIMARY_KEY_NAME := p_primary_key_name;
303     G_PRIMARY_KEY_ID := p_id;
304     G_TABLE_NAME := p_table_name;
305     get_record_snapshot(p_id, p_primary_key_name, p_table_name, 'PRE');
306 END log_record_pre;
307 
308 procedure log_record_post(
309     p_id NUMBER,
310     p_primary_key_name VARCHAR2,
311     p_table_name VARCHAR2,
312     p_effective_from DATE,
313     p_comment VARCHAR2,
314     p_error_type     OUT NOCOPY  VARCHAR2,
315     p_return_status  OUT NOCOPY  VARCHAR2) IS
316 
317     l_return_status               VARCHAR2(5);
318     l_error_type                  VARCHAR2(50);
319 
320 BEGIN
321      -- no pre update snapshot
322     IF (G_VALUE_LIST.count = 0) THEN
323         RAISE FND_API.G_EXC_ERROR;
324     END IF;
325 
326     -- invalid table name
327     IF (G_TABLE_NAME <> p_table_name) THEN
328         RAISE FND_API.G_EXC_ERROR;
329     END IF;
330 
331     IF (G_PRIMARY_KEY_NAME <> p_primary_key_name OR G_PRIMARY_KEY_ID <> p_id) THEN
332         RAISE FND_API.G_EXC_ERROR;
333     END IF;
334 
335     get_record_snapshot(p_id, p_primary_key_name, p_table_name, 'POST');
336     log_changes(p_effective_from, p_comment,l_return_status,l_error_type);
337 
338     If l_return_status='E' then
339             p_error_type := l_error_type;
340             p_return_status:=l_return_status;
341     End if;
342 
343 EXCEPTION
344 WHEN OTHERS THEN
345     RAISE;
346 END log_record_post;
347 
348 procedure log_record_ins(
349     p_id NUMBER,
350     p_primary_key_name VARCHAR2,
351     p_table_name VARCHAR2,
352     p_effective_from DATE,
353     p_comment VARCHAR2,
354     p_error_type     OUT NOCOPY  VARCHAR2,
355     p_return_status  OUT NOCOPY  VARCHAR2
356 )  IS
357 
358     l_return_status               VARCHAR2(5);
359     l_error_type                  VARCHAR2(50);
360 
361     l_index     NUMBER:=1;
362 
363     cursor history is
364     select source_column_name
365     from xle_history_columns_b;
366 
367     l_source_column_name varchar2(2000);
368     l_id number;
369 
370     l_history_id NUMBER := NULL;
371     l_flag Varchar2(5):='N';
372     l_eff_flag Varchar2(5):='N';
373     l_eff varchar2(2000);
374     l_eff_from Date;
375     l_count Number:=0;
376     l_column_name Varchar2(200);
377 
378 
379 BEGIN
380     G_VALUE_LIST.delete;
381     G_PRIMARY_KEY_NAME := p_primary_key_name;
382     G_PRIMARY_KEY_ID := p_id;
383     G_TABLE_NAME := p_table_name;
384 
385     for history_r in history loop
386 
387                 G_VALUE_LIST(l_index).column_name := history_r.source_column_name;
388                 l_index := l_index + 1;
389     end loop;
390 begin
391 
392                execute immediate
393   'select ' ||
394                                   G_VALUE_LIST(1).column_name ||','
395                                || G_VALUE_LIST(2).column_name ||','
396                                || G_VALUE_LIST(3).column_name ||','
397                                || G_VALUE_LIST(4).column_name ||','
398                                || G_VALUE_LIST(5).column_name ||','
399                                || G_VALUE_LIST(6).column_name ||','
400                                || G_VALUE_LIST(7).column_name ||','
401                                || G_VALUE_LIST(8).column_name ||','
402                                || G_VALUE_LIST(9).column_name ||','
403                                || G_VALUE_LIST(10).column_name ||','
404                                || G_VALUE_LIST(11).column_name ||','
405                                || G_VALUE_LIST(12).column_name ||','
406                                || G_VALUE_LIST(13).column_name ||','
407                                || G_VALUE_LIST(14).column_name ||','
408                                || G_VALUE_LIST(15).column_name ||','
409                                || G_VALUE_LIST(16).column_name ||','
410                                || G_VALUE_LIST(17).column_name ||','
411                                || G_VALUE_LIST(18).column_name ||','
412                                || G_VALUE_LIST(19).column_name ||','
413                                || G_VALUE_LIST(20).column_name ||','
414                                || G_VALUE_LIST(21).column_name ||','
415                                || G_VALUE_LIST(22).column_name ||','
416                                || G_VALUE_LIST(23).column_name ||','
417                                || G_VALUE_LIST(24).column_name ||','
418                                || G_VALUE_LIST(25).column_name ||','
419                                || G_VALUE_LIST(26).column_name ||','
420                                || G_VALUE_LIST(27).column_name ||','
421                                || G_VALUE_LIST(28).column_name ||','
422                                || G_VALUE_LIST(29).column_name ||'
423                      from XLE_REGISTRATIONS where REGISTRATION_ID='||p_id
424 
425                      INTO         G_VALUE_LIST(1).new_value
426                                 , G_VALUE_LIST(2).new_value
427                                 , G_VALUE_LIST(3).new_value
428                                 , G_VALUE_LIST(4).new_value
429                                 , G_VALUE_LIST(5).new_value
430                                 , G_VALUE_LIST(6).new_value
431                                 , G_VALUE_LIST(7).new_value
432                                 , G_VALUE_LIST(8).new_value
433                                 , G_VALUE_LIST(9).new_value
434                                 , G_VALUE_LIST(10).new_value
435                                 , G_VALUE_LIST(11).new_value
436                                 , G_VALUE_LIST(12).new_value
437                                 , G_VALUE_LIST(13).new_value
438                                 , G_VALUE_LIST(14).new_value
439                                 , G_VALUE_LIST(15).new_value
440                                 , G_VALUE_LIST(16).new_value
441                                 , G_VALUE_LIST(17).new_value
442                                 , G_VALUE_LIST(18).new_value
443                                 , G_VALUE_LIST(19).new_value
444                                 , G_VALUE_LIST(20).new_value
445                                 , G_VALUE_LIST(21).new_value
446                                 , G_VALUE_LIST(22).new_value
447                                 , G_VALUE_LIST(23).new_value
448                                 , G_VALUE_LIST(24).new_value
449                                 , G_VALUE_LIST(25).new_value
450                                 , G_VALUE_LIST(26).new_value
451                                 , G_VALUE_LIST(27).new_value
452                                 , G_VALUE_LIST(28).new_value
453                                 , G_VALUE_LIST(29).new_value;
454 
455 Exception
456 
457 WHEN OTHERS THEN
458     RAISE;
459 end;
460 
461     FOR i IN 1..G_VALUE_LIST.count LOOP
462         IF G_VALUE_LIST(i).new_value IS NOT NULL THEN
463             l_count:=l_count+1;
464             l_column_name:=G_VALUE_LIST(i).column_name;
465 
466             XLE_Histories_PKG.Insert_Row(
467                 x_history_id => l_history_id,
468                 p_source_table => G_TABLE_NAME,
469                 p_source_id => G_PRIMARY_KEY_ID,
470                 p_source_column_name => G_VALUE_LIST(i).column_name,
471                 p_source_column_value => G_VALUE_LIST(i).new_value,
472                 p_effective_from => p_effective_from,
473                 p_comment => p_comment,
474                 p_object_version_number => 1);
475         END IF;
476     END LOOP;
477 
478 Exception
479 
480 WHEN OTHERS THEN
481     RAISE;
482 END log_record_ins;
483 
484 END XLE_History_PUB;
485