[Home] [Help]
PACKAGE BODY: APPS.ITA_RECORD_CURR_STATUS_PKG
Source
1 package body ITA_RECORD_CURR_STATUS_PKG as
2 /* $Header: itarcurb.pls 120.22.12000000.3 2007/04/02 19:26:26 sanayak ship $ */
3
4 PROCEDURE enable_tracking(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY VARCHAR2, p_table_name IN VARCHAR2)
6 IS
7 l_ret_code NUMBER;
8 l_ret_val BOOLEAN;
9 l_app_id NUMBER;
10 l_set_warn BOOLEAN;
11 CURSOR c_all_tables IS
12 SELECT table_name, application_id
13 FROM fnd_tables
14 WHERE table_id IN (SELECT table_id FROM ita_setup_groups_b WHERE audit_end_date IS NULL);
15
16 l_table c_all_tables%ROWTYPE;
17
18 BEGIN
19 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Starting concurrent program');
20 IF p_table_name IS NOT NULL
21 THEN
22 l_app_id := -1;
23 SELECT application_id
24 INTO l_app_id
25 FROM fnd_tables
26 WHERE table_name = p_table_name;
27
28 IF l_app_id = -1
29 THEN
30 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Table ' || p_table_name || ' does not exist in fnd_tables.');
31 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Current State not fetched.');
32 RETURN;
33 END IF;
34 l_ret_code := enable_tracking_for_table(l_app_id, p_table_name);
35 IF l_ret_code = 0
36 THEN
37 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Current State not fetched.');
38 ELSE
39 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Current State fetched.');
40 END IF;
41 RETURN;
42 ELSE
43 FOR l_table IN c_all_tables
44 LOOP
45 EXIT WHEN c_all_tables%NOTFOUND;
46 l_ret_code := enable_tracking_for_table(l_table.application_id, l_table.table_name);
47 IF l_ret_code = 0
48 THEN
49 l_set_warn := TRUE;
50 END IF;
51 END LOOP;
52 /*l_ret_code := enable_tracking_for_table(200, 'AP_SYSTEM_PARAMETERS_ALL');
53 IF l_ret_code = 0
54 THEN
55 l_set_warn := TRUE;
56 END IF;
57 l_ret_code := enable_tracking_for_table(200, 'FINANCIALS_SYSTEM_PARAMS_ALL');
58 IF l_ret_code = 0
59 THEN
60 l_set_warn := TRUE;
61 END IF;
62 l_ret_code := enable_tracking_for_table(222, 'AR_SYSTEM_PARAMETERS_ALL');
63 IF l_ret_code = 0
64 THEN
65 l_set_warn := TRUE;
66 END IF;
67 l_ret_code := enable_tracking_for_table(260, 'CE_SYSTEM_PARAMETERS_ALL');
68 IF l_ret_code = 0
69 THEN
70 l_set_warn := TRUE;
71 END IF;
72 l_ret_code := enable_tracking_for_table(101, 'GL_SETS_OF_BOOKS');
73 IF l_ret_code = 0
74 THEN
75 l_set_warn := TRUE;
76 END IF;
77 l_ret_code := enable_tracking_for_table(101, 'GL_TAX_OPTIONS');
78 IF l_ret_code = 0
79 THEN
80 l_set_warn := TRUE;
81 END IF;
82 l_ret_code := enable_tracking_for_table(401, 'MTL_PARAMETERS');
83 IF l_ret_code = 0
84 THEN
85 l_set_warn := TRUE;
86 END IF;
87 l_ret_code := enable_tracking_for_table(201, 'PO_SYSTEM_PARAMETERS_ALL');
88 IF l_ret_code = 0
89 THEN
90 l_set_warn := TRUE;
91 END IF;
92 l_ret_code := enable_tracking_for_table(201, 'RCV_PARAMETERS');
93 IF l_ret_code = 0
94 THEN
95 l_set_warn := TRUE;
96 END IF;
97 l_ret_code := enable_tracking_for_table(665, 'WSH_SHIPPING_PARAMETERS');
98 IF l_ret_code = 0
99 THEN
100 l_set_warn := TRUE;
101 END IF;
102 l_ret_code := enable_tracking_for_table(140, 'FA_SYSTEM_CONTROLS');
103 IF l_ret_code = 0
104 THEN
105 l_set_warn := TRUE;
106 END IF;
107 l_ret_code := enable_tracking_for_table(8901, 'FV_SYSTEM_PARAMETERS');
108 IF l_ret_code = 0
109 THEN
110 l_set_warn := TRUE;
111 END IF;
112 l_ret_code := enable_tracking_for_table(8901, 'FV_FEDERAL_OPTIONS');
113 IF l_ret_code = 0
114 THEN
115 l_set_warn := TRUE;
116 END IF;
117 l_ret_code := enable_tracking_for_table(201, 'AP_SUPPLIERS');
118 IF l_ret_code = 0
119 THEN
120 l_set_warn := TRUE;
121 END IF;
122 l_ret_code := enable_tracking_for_table(201, 'AP_SUPPLIER_SITES_ALL');
123 IF l_ret_code = 0
124 THEN
125 l_set_warn := TRUE;
126 END IF;
127 l_ret_code := enable_tracking_for_table(0, 'FND_PROFILE_OPTION_VALUES');
128 IF l_ret_code = 0
129 THEN
130 l_set_warn := TRUE;
131 END IF;*/
132 END IF;
133 -- Status - NORMAL, WARNING, ERROR
134 IF l_set_warn = TRUE
135 THEN
136 errbuf := 'Current state fetched for some of the tables.';
137 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', errbuf);
138 retcode := FND_API.G_RET_STS_SUCCESS;
139 ELSE
140 errbuf := 'Current state fetched.';
141 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', errbuf);
142 retcode := FND_API.G_RET_STS_SUCCESS;
143 END IF;
144 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Finished fetching current configuration.');
145 EXCEPTION WHEN OTHERS
146 THEN
147 l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Current State not fetched.');
148 errbuf := SQLERRM;
149 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
150 END enable_tracking;
151
152 FUNCTION enable_tracking_for_table
153 (p_application_id IN NUMBER,
154 p_table_name IN VARCHAR2)
155 RETURN NUMBER
156 IS
157 l_ret_code NUMBER;
158 BEGIN
159 l_ret_code := create_shadow_trigger(p_application_id, p_table_name);
160 IF l_ret_code = 0
161 THEN
162 RETURN l_ret_code;
163 END IF;
164 IF p_table_name = 'FND_PROFILE_OPTION_VALUES'
165 THEN
166 l_ret_code := record_profile_current_state(p_application_id, p_table_name);
167 ELSE
168 l_ret_code := record_current_state(p_application_id, p_table_name);
169 END IF;
170 IF l_ret_code = 0
171 THEN
172 RETURN l_ret_code;
173 END IF;
174 -- l_ret_code = 2 means audit_start_date was already set so current state already fetched.
175 IF l_ret_code <> 2
176 THEN
177 l_ret_code := set_audit_start_date(p_application_id, p_table_name);
178 END IF;
179 IF l_ret_code = 0
180 THEN
181 RETURN l_ret_code;
182 END IF;
183 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Done with ' || p_table_name);
184 COMMIT;
185 RETURN 1;
186 END enable_tracking_for_table;
187
188 FUNCTION set_audit_start_date
189 (p_application_id IN NUMBER,
190 p_table_name IN VARCHAR2)
191 RETURN NUMBER
192 IS
193 l_setup_gp_code ITA_SETUP_GROUPS_B.setup_group_code%TYPE;
194 l_ret_val BOOLEAN;
195 BEGIN
196
197 select setup_gp.SETUP_GROUP_CODE
198 INTO l_setup_gp_code
199 FROM ITA_SETUP_GROUPS_B setup_gp, FND_TABLES ft
200 WHERE setup_gp.TABLE_APP_ID = p_application_id and setup_gp.TABLE_ID = ft.table_id and
201 ft.application_id = setup_gp.table_app_id and ft.table_name = UPPER(p_table_name);
202
203 UPDATE ita_setup_groups_b SET audit_start_date = sysdate
204 WHERE setup_group_code = l_setup_gp_code;
205 RETURN 1;
206 EXCEPTION
207 WHEN OTHERS
208 THEN
209 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
210 -- dbms_output.put_line(SUBSTR (SQLERRM, 1, 2000));
211 --l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Audit Start date not set for ' || p_table_name);
212 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Audit Start Date not set for ' || p_table_name);
213 RETURN 0;
214 END set_audit_start_date;
215
216
217 FUNCTION get_shadow_table_prefix
218 (p_table_name IN VARCHAR2)
219 RETURN VARCHAR2
220 IS
221 l_shadow_table_name VARCHAR2(40);
222
223 BEGIN
224 IF LENGTH(p_table_name) IS NULL
225 THEN
226 RETURN NULL;
227 END IF;
228 IF LENGTH(p_table_name) > 24
229 THEN
230 l_shadow_table_name := SUBSTR(p_table_name, 1, 24);
231 ELSE
232 l_shadow_table_name := p_table_name;
233 END IF;
234 l_shadow_table_name := CONCAT(l_shadow_table_name, '_A');
235 RETURN l_shadow_table_name;
236 END get_shadow_table_prefix;
237
238 FUNCTION create_shadow_trigger
239 (p_application_id IN NUMBER,
240 p_table_name IN VARCHAR2)
241 RETURN NUMBER
242 IS
243 --- sanayak start bug#5766565 - skip workflow biz events for audit disabled parameters
244 Cursor curParams
245 is
246 select distinct
247 PARAMETER_CODE,
248 PARAMETER_NAME,
249 SETUP_GROUP_CODE,
250 COLUMN_ID,
251 (
252 select COLUMN_NAME
253 from FND_COLUMNS fc
254 where
255 (APPLICATION_ID, TABLE_ID) = (
256 select TABLE_APP_ID, TABLE_ID
257 from ITA_SETUP_GROUPS_B
258 where SETUP_GROUP_CODE = ispv.SETUP_GROUP_CODE) and
259 COLUMN_ID = ispv.COLUMN_ID
260 ) COLUMN_NAME,
261 AUDIT_ENABLED_FLAG
262 from ITA_SETUP_PARAMETERS_VL ispv
263 where ispv.SETUP_GROUP_CODE in
264 (select distinct
265 setup_gp.SETUP_GROUP_CODE
266 from
267 ITA_SETUP_GROUPS_VL setup_gp,
268 FND_TABLES fnd_table ,
269 FND_APPLICATION_VL FND_APP
270 where
271 fnd_table.APPLICATION_ID (+) = setup_gp.TABLE_APP_ID and
272 fnd_table.TABLE_ID (+) = setup_gp.TABLE_ID and
273 setup_gp.TABLE_APP_ID = FND_APP.application_id
274 and setup_gp.TABLE_APP_ID = setup_gp.TABLE_APP_ID
275 and setup_gp.table_app_id = p_application_id
276 and table_name = p_table_name
277 )
278 and exists --- query to find existance in audit schema tables(R12 only)
279 (
280 select COLUMN_id
281 from FND_AUDIT_COLUMNS
282 where
283 (TABLE_APP_ID, TABLE_ID) = (
284 select TABLE_APP_ID, TABLE_ID
285 from ITA_SETUP_GROUPS_B
286 where SETUP_GROUP_CODE = ispv.SETUP_GROUP_CODE)
287 and ispv.Column_id = column_id
288 )
289 ;
290
291 l_shadow_table_name VARCHAR2(40);
292 l_if_block VARCHAR2(32767) := 'if ((';
293 l_if_notnull VARCHAR2(32767);
294 l_if_null VARCHAR2(32767);
295 l_query_header VARCHAR2(32767);
296 l_query VARCHAR2(32767);
297 l_query_launch VARCHAR2(32767);
298 l_skip_chgevent BOOLEAN := FALSE;
299
300 l_ret_val BOOLEAN;
301 BEGIN
302 /* for all rows in ITA_SETUP_PARAMETERS_VL view
303 if there is alteast one not null column which
304 meets the above mentioned criteria, skip the workflow
305 */
306 for recParams in curParams
307 Loop
308
309 if recParams.AUDIT_ENABLED_FLAG = 'N' then
310 l_if_notnull := l_if_notnull || ':n.' || recParams.COLUMN_NAME || ' is not null or ';
311 l_skip_chgevent := TRUE;
312 else
313 l_if_null := l_if_null || ':n.' || recParams.COLUMN_NAME || ' is null and ';
314
315 end if;
316
317 End loop;
318
319 l_if_block := l_if_block || l_if_notnull || '''Y'' = ''N'') and (' || l_if_null || '''Y'' = ''Y''))'||
320 ' then return; end if;';
321
322 l_shadow_table_name := ITA_RECORD_CURR_STATUS_PKG.get_shadow_table_prefix(p_table_name);
323
324 l_query_header :=
325 'create or replace trigger ' || l_shadow_table_name || '_ITA ' ||
326 'after insert or update ' ||
327 'on ' || l_shadow_table_name || ' ' ||
328 'referencing new as n ' ||
329 'for each row ' ||
330 'declare l_item_key WF_ITEMS.ITEM_KEY%type; ' ||
331 'begin ' ;
332
333 l_query_launch :=
334 'l_item_key := ITA_BIZ_EVENTS_PVT.RAISE_CHANGE_EVENT(''' ||
335 p_application_id || ''', ''' || p_table_name || ''', :n.ROWID); ' || 'end;';
336
337 if (l_skip_chgevent) then --- code to return without launching workflow
338 l_query := l_query_header||l_if_block||l_query_launch;
339 else
340 l_query := l_query_header ||l_query_launch;
341 end if;
342 --- sanayak end bug#5766565
343 EXECUTE IMMEDIATE l_query;
344 RETURN 1;
345 EXCEPTION
346 WHEN OTHERS
347 THEN
348 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
349 --dbms_output.put_line(SUBSTR (SQLERRM, 1, 2000));
350 --l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Shadow trigger not created for ' || p_table_name);
351 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Shadow trigger not created for ' || p_table_name);
352 RETURN 0;
353 END create_shadow_trigger;
354
355 FUNCTION record_current_state
356 (p_application_id IN NUMBER,
357 p_table_name IN VARCHAR2)
358 RETURN NUMBER
359 IS
360
361 l_ret_val BOOLEAN;
362 l_table_id NUMBER;
363 l_setup_gp_code ITA_SETUP_GROUPS_B.SETUP_GROUP_CODE%TYPE;
364 l_audit_start_date DATE;
365 l_context_param_code ITA_SETUP_GROUPS_B.CONTEXT_PARAMETER_CODE%TYPE;
366 l_hier_level_code VARCHAR2(30);
367 l_context_param_code2 ITA_SETUP_GROUPS_B.CONTEXT_PARAMETER_CODE2%TYPE;
368 l_column_id NUMBER;
369 l_column_id1 NUMBER;
370 --l_shadow_table_name VARCHAR2(40);
371 l_del_sql VARCHAR2(32767);
372 l_select_clause ITA_SETUP_PARAMETERS_B.SELECT_CLAUSE%TYPE;
373 l_from_clause ITA_SETUP_PARAMETERS_B.FROM_CLAUSE%TYPE;
374 l_where_clause ITA_SETUP_PARAMETERS_B.WHERE_CLAUSE%TYPE;
375 l_ins_sql VARCHAR2(32767);
376 l_column_name FND_COLUMNS.COLUMN_NAME%TYPE;
377 l_user_id NUMBER;
378 l_login_id NUMBER;
379 l_inst_code ITA_SETUP_CHANGE_HISTORY.INSTANCE_CODE%TYPE;
380 l_rec_val_code_sql VARCHAR2(32767);
381
382 CURSOR c_get_parameters(p_setup_group_code IN VARCHAR2)
383 IS
384 SELECT fnd.column_name, isp.parameter_code
385 from fnd_columns fnd, ita_setup_parameters_b isp
386 WHERE isp.setup_group_code = p_setup_group_code AND
387 isp.column_id = fnd.column_id;
388
389 l_param_rec c_get_parameters%ROWTYPE;
390 l_curr_sql VARCHAR2(32767);
391 l_rec_sql VARCHAR2(32767);
392 l_upd_sql VARCHAR2(32767);
393 l_curr_val_code ita_setup_change_history.pk3_value%TYPE;
394 l_rec_val_code ita_setup_change_history.pk5_value%TYPE;
395
396 BEGIN
397 IF LENGTH(p_table_name) IS NULL
398 THEN
399 RETURN 0;
400 END IF;
401
402 SELECT fnd_table.TABLE_ID
403 INTO l_table_id
404 FROM FND_TABLES fnd_table
405 WHERE fnd_table.APPLICATION_ID = p_application_id AND fnd_table.TABLE_NAME = UPPER(p_table_name);
406
407 select setup_gp.SETUP_GROUP_CODE, setup_gp.AUDIT_START_DATE,
408 setup_gp.CONTEXT_PARAMETER_CODE, setup_gp.HIERARCHY_LEVEL hierarchy_level_code,
409 setup_gp.context_parameter_code2,
410 (SELECT column_id FROM ita_setup_parameters_b WHERE parameter_code = setup_gp.CONTEXT_PARAMETER_CODE) column_id,
411 (SELECT column_id FROM ita_setup_parameters_b WHERE parameter_code = setup_gp.CONTEXT_PARAMETER_CODE2) column_id1
412 INTO l_setup_gp_code, l_audit_start_date, l_context_param_code, l_hier_level_code, l_context_param_code2,
413 l_column_id, l_column_id1
414 FROM ITA_SETUP_GROUPS_B setup_gp
415 WHERE setup_gp.TABLE_APP_ID = p_application_id and
416 setup_gp.TABLE_ID = l_table_id;
417
418 select COLUMN_NAME
419 INTO l_column_name
420 from FND_COLUMNS
421 where (APPLICATION_ID, TABLE_ID) = (select TABLE_APP_ID, TABLE_ID
422 from ITA_SETUP_GROUPS_B
423 where SETUP_GROUP_CODE = l_setup_gp_code) and
424 COLUMN_ID = l_column_id;
425
426 l_user_id := fnd_global.user_id;
427 l_login_id := fnd_global.conc_login_id;
428 IF l_audit_start_date IS NULL
429 THEN
430 l_del_sql := 'delete from ITA_SETUP_CHANGE_HISTORY where INSTANCE_CODE = ''CURRENT'' and SETUP_GROUP_CODE =''' || l_setup_gp_code || '''';
431 EXECUTE IMMEDIATE l_del_sql;
432 SELECT INSTANCE_CODE INTO l_inst_code FROM ITA_SETUP_INSTANCES_B WHERE CURRENT_FLAG='Y';
433 -- l_shadow_table_name := get_shadow_table_prefix(p_table_name);
434 FOR l_param_rec IN c_get_parameters(l_setup_gp_code)
435 LOOP
436 EXIT WHEN c_get_parameters%NOTFOUND;
437
438 SELECT select_clause, from_clause, where_clause
439 INTO l_select_clause, l_from_clause, l_where_clause
440 FROM ita_setup_parameters_b
441 WHERE parameter_code = l_param_rec.parameter_code;
442
443 IF l_select_clause IS NOT NULL
444 THEN
445 l_select_clause := RTRIM(l_select_clause);
446 END IF;
447 IF l_from_clause IS NOT NULL
448 THEN
449 l_from_clause := RTRIM(l_from_clause);
450 END IF;
451 IF l_where_clause IS NOT NULL
452 THEN
453 l_where_clause := RTRIM(l_where_clause);
454 END IF;
455 IF LENGTH(l_select_clause) IS NOT NULL and LENGTH(l_from_clause) IS NOT NULL
456 THEN
457 l_select_clause := CONCAT(l_select_clause, ' ');
458 l_select_clause := CONCAT(l_select_clause, l_from_clause);
459 IF LENGTH(l_where_clause) IS NOT NULL
460 THEN
461 l_select_clause := CONCAT(l_select_clause, ' ');
462 l_select_clause := CONCAT(l_select_clause, l_where_clause);
463 END IF;
464 END IF;
465 IF LENGTH(l_select_clause) IS NOT NULL
466 THEN
467 l_curr_sql := REPLACE(l_select_clause, ''':1''', 'bt.' || l_param_rec.column_name);
468 l_curr_sql := REPLACE(l_curr_sql, ':1', 'bt.' || l_param_rec.column_name);
469 l_curr_sql := REPLACE(l_curr_sql, ':2', '(SELECT ORG_ID FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID = bt.' || l_column_name || ')');
470 --l_rec_sql := REPLACE(l_select_clause, ''':1''', 'pk5_value');
471 --l_rec_sql := REPLACE(l_select_clause, ':1', 'pk5_value');
472 ELSE
473 l_curr_sql := 'bt.' || l_param_rec.column_name;
474 l_rec_sql := NULL;
475 END IF;
476
477 IF l_setup_gp_code = 'FND' || '.FND_PROFILE_OPTION_VALUES'
478 THEN
479 l_ins_sql := '';
480 END IF;
481 -- insert into ita_setup_change_history
482 IF p_table_name = 'AP_SUPPLIERS'
483 THEN
484 /* pk1 - supplier name
485 pk2 - supplier id */
486 l_ins_sql := 'INSERT INTO ITA_SETUP_CHANGE_HISTORY(INSTANCE_CODE, CHANGE_ID, PARAMETER_CODE, ' ||
487 'SETUP_GROUP_CODE, CHANGE_AUTHOR, CHANGE_DATE, PK2_VALUE, PK3_VALUE, PK1_VALUE, ' ||
488 'PK5_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ' ||
489 'LAST_UPDATE_DATE, CURRENT_VALUE, OBJECT_VERSION_NUMBER) ' ||
490 '(SELECT ''' || l_inst_code || ''', ' ||
491 'ITA_SETUP_CHANGE_HISTORY_S1.NEXTVAL, ''' || l_param_rec.parameter_code || ''', ''' || l_setup_gp_code || ''', ' ||
492 '(SELECT USER_NAME FROM FND_USER WHERE USER_ID=bt.LAST_UPDATED_BY), bt.LAST_UPDATE_DATE, ' ||
493 'bt.' || l_column_name || ', bt.' || l_param_rec.column_name || ', ' ||
494 '(SELECT VENDOR_NAME FROM AP_SUPPLIERS WHERE VENDOR_ID = bt.' || l_column_name || '), ' ||
495 'to_char(null), ' || l_user_id || ', sysdate, ' || l_user_id || ', ' || l_login_id || ', sysdate, (' || l_curr_sql || '), 1 obj_ver FROM ' || p_table_name || ' bt WHERE ' ||
496 l_param_rec.column_name || ' IS NOT NULL)';
497 BEGIN
498 EXECUTE IMMEDIATE l_ins_sql;
499 EXCEPTION
500 WHEN OTHERS
501 THEN
502 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
503 fnd_file.put_line(fnd_file.LOG, 'Not fetched for ' || l_param_rec.column_name);
504 END;
505 END IF;
506 IF p_table_name = 'AP_SUPPLIER_SITES_ALL'
507 THEN
508 /* pk1 - org name
509 pk2 - org id
510 pk6 - supplier name
511 pk7 - supplier id
512 pk8 - site name
513 pk9 - site id*/
514 l_ins_sql := 'INSERT INTO ITA_SETUP_CHANGE_HISTORY(INSTANCE_CODE, CHANGE_ID, PARAMETER_CODE, ' ||
515 'SETUP_GROUP_CODE, CHANGE_AUTHOR, CHANGE_DATE, PK9_VALUE, PK3_VALUE, PK8_VALUE, ' ||
516 'PK5_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ' ||
517 'LAST_UPDATE_DATE, PK1_VALUE, PK7_VALUE, PK6_VALUE, PK2_VALUE, CURRENT_VALUE, ' ||
518 'OBJECT_VERSION_NUMBER) ' ||
519 '(SELECT ''' || l_inst_code || ''', ' ||
520 'ITA_SETUP_CHANGE_HISTORY_S1.NEXTVAL, ''' || l_param_rec.parameter_code || ''', ''' || l_setup_gp_code || ''', ' ||
521 '(SELECT USER_NAME FROM FND_USER WHERE USER_ID=bt.LAST_UPDATED_BY), bt.LAST_UPDATE_DATE, ' ||
522 'bt.' || l_column_name || ', bt.' || l_param_rec.column_name || ', ' ||
523 '(SELECT VENDOR_SITE_CODE FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID = bt.' || l_column_name || '), ' ||
524 'to_char(null), ' || l_user_id || ', sysdate, ' || l_user_id || ', ' || l_login_id || ', sysdate, ' ||
525 '(SELECT name FROM ((select distinct org.ORGANIZATION_ID, org.NAME, org_info.ORG_INFORMATION1 type ' ||
526 'from HR_ALL_ORGANIZATION_UNITS org, HR_ORGANIZATION_INFORMATION org_info ' ||
527 'where org_info.ORGANIZATION_ID = org.ORGANIZATION_ID and ' ||
528 'org_info.ORG_INFORMATION_CONTEXT = ''CLASS'') ' ||
529 'union ' ||
530 '(select distinct SET_OF_BOOKS_ID organization_id, NAME, ''SET_BOOKS'' type ' ||
531 'from GL_SETS_OF_BOOKS ' ||
532 ')) WHERE organization_id = (SELECT ORG_ID FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID=bt.' || l_column_name || ') and type = ''' || l_hier_level_code || ''') org_name , ' ||
533 '(SELECT VENDOR_ID FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID = bt.' || l_column_name || ')vendor_id, ' ||
534 '(SELECT VENDOR_NAME FROM AP_SUPPLIERS v, AP_SUPPLIER_SITES_ALL vs WHERE v.VENDOR_ID = vs.VENDOR_ID and VENDOR_SITE_ID = bt.' || l_column_name || ')vendor_name, ' ||
535 '(SELECT ORG_ID FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_SITE_ID = bt.' || l_column_name || ') org_id, ' ||
536 '(' || l_curr_sql || ') , 1 obj_ver FROM ' || p_table_name || ' bt WHERE ' ||
537 l_param_rec.column_name || ' IS NOT NULL)';
538 BEGIN
539 EXECUTE IMMEDIATE l_ins_sql;
540 EXCEPTION
541 WHEN OTHERS
542 THEN
543 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
544 fnd_file.put_line(fnd_file.LOG, 'Not fetched for ' || l_param_rec.column_name);
545 END;
546 END IF;
547 IF p_table_name <> 'FND_PROFILE_OPTION_VALUES' and p_table_name <> 'AP_SUPPLIERS' and
548 p_table_name <> 'AP_SUPPLIER_SITES_ALL'
549 THEN
550 /* pk1 - org name
551 pk2 - org id */
552 l_rec_val_code_sql := '((select recommended_value from ita_setup_rec_values_vl where parameter_code = '''
553 || l_param_rec.parameter_code || ''' and pk1_value = to_char(bt.' || l_column_name ||
554 ')) union (select recommended_value from ita_setup_rec_values_vl where parameter_code = '''
555 || l_param_rec.parameter_code || ''' and default_flag = ''Y'' and not exists (select recommended_value ' ||
556 ' from ita_setup_rec_values_vl where parameter_code = ''' || l_param_rec.parameter_code || ''' and pk1_value = to_char(bt.' ||
557 l_column_name || ')))) ';
558 l_rec_sql := l_rec_val_code_sql || 'recomm_val';
559 l_ins_sql := 'INSERT INTO ITA_SETUP_CHANGE_HISTORY(INSTANCE_CODE, CHANGE_ID, PARAMETER_CODE, ' ||
560 'SETUP_GROUP_CODE, CHANGE_AUTHOR, CHANGE_DATE, PK2_VALUE, PK3_VALUE, PK1_VALUE, ' ||
561 'PK5_VALUE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ' ||
562 'LAST_UPDATE_DATE, CURRENT_VALUE, RECOMMENDED_VALUE, OBJECT_VERSION_NUMBER) ' ||
563 '(SELECT ''' || l_inst_code || ''', ' ||
564 'ITA_SETUP_CHANGE_HISTORY_S1.NEXTVAL, ''' || l_param_rec.parameter_code || ''', ''' || l_setup_gp_code || ''', ' ||
565 '(SELECT USER_NAME FROM FND_USER WHERE USER_ID=bt.LAST_UPDATED_BY), bt.LAST_UPDATE_DATE, ' ||
566 'bt.' || l_column_name || ', bt.' || l_param_rec.column_name || ', ' ||
567 '(SELECT name FROM ((select distinct org.ORGANIZATION_ID, org.NAME, org_info.ORG_INFORMATION1 type ' ||
568 'from HR_ALL_ORGANIZATION_UNITS org, HR_ORGANIZATION_INFORMATION org_info ' ||
569 'where org_info.ORGANIZATION_ID = org.ORGANIZATION_ID and ' ||
570 'org_info.ORG_INFORMATION_CONTEXT = ''CLASS'') ' ||
571 'union ' ||
572 '(select distinct SET_OF_BOOKS_ID organization_id, NAME, ''SET_BOOKS'' type ' ||
573 'from GL_SETS_OF_BOOKS ' ||
574 ')) WHERE organization_id = bt.' || l_column_name || ' and type = ''' ||
575 l_hier_level_code || ''') org_name , ' ||
576 l_rec_val_code_sql || 'recomm_code, ' || l_user_id || ', sysdate, ' || l_user_id || ', ' || l_login_id || ', sysdate, ' ||
577 '(' || l_curr_sql || '), ' || l_rec_sql || ' , 1 obj_ver FROM ' || p_table_name || ' bt WHERE ' ||
578 l_param_rec.column_name || ' IS NOT NULL)';
579 BEGIN
580 EXECUTE IMMEDIATE l_ins_sql;
581 EXCEPTION
582 WHEN OTHERS
583 THEN
584 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
585 fnd_file.put_line(fnd_file.LOG, 'Not fetched for ' || l_param_rec.column_name);
586 END;
587 END IF;
588
589 END LOOP;
590 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current State fetched for table - ' || p_table_name);
591 ELSE
592 -- Log that current state already fetched for l_setup_gp_code
593 --dbms_output.put_line('Current State already fetched for table - ' || p_table_name);
594 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current State already fetched for table - ' || p_table_name);
595 RETURN 2;
596 END IF;
597 RETURN 1;
598 EXCEPTION
599 WHEN OTHERS
600 THEN
601 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
602 --dbms_output.put_line(SUBSTR (SQLERRM, 1, 2000));
603 --l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Current state not fetched for ' || p_table_name);
604 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current state not fetched for ' || p_table_name);
605 RETURN 0;
606 END record_current_state;
607
608 FUNCTION record_profile_current_state
609 (p_application_id IN NUMBER,
610 p_table_name IN VARCHAR2)
611 RETURN NUMBER
612 IS
613
614 l_ret_val BOOLEAN;
615 l_table_id NUMBER;
616 l_setup_gp_code ITA_SETUP_GROUPS_B.SETUP_GROUP_CODE%TYPE;
617 l_audit_start_date DATE;
618 l_context_param_code ITA_SETUP_GROUPS_B.CONTEXT_PARAMETER_CODE%TYPE;
619 l_hier_level_code VARCHAR2(30);
620 l_context_param_code2 ITA_SETUP_GROUPS_B.CONTEXT_PARAMETER_CODE2%TYPE;
621 l_column_id NUMBER;
622 l_column_id1 NUMBER;
623 l_del_sql VARCHAR2(32767);
624 l_select_clause ITA_SETUP_PARAMETERS_B.SELECT_CLAUSE%TYPE;
625 l_ins_sql VARCHAR2(32767);
626 l_ins_param_sql VARCHAR2(32767);
627 l_column_name FND_COLUMNS.COLUMN_NAME%TYPE;
628 l_column_name1 FND_COLUMNS.COLUMN_NAME%TYPE;
629 l_user_id NUMBER;
630 l_login_id NUMBER;
631 l_inst_code ITA_SETUP_CHANGE_HISTORY.INSTANCE_CODE%TYPE;
632 l_rec_val_code_sql VARCHAR2(32767);
633
634 l_curr_sql VARCHAR2(32767);
635 l_rec_sql VARCHAR2(32767);
636 l_upd_sql VARCHAR2(32767);
637 l_curr_val_code ita_setup_change_history.pk3_value%TYPE;
638 l_rec_val_code ita_setup_change_history.pk5_value%TYPE;
639
640 -- cpetriuc start - bug 5163722
641 CURSOR c_get_profiles_with_sql(p_setup_group_code IN VARCHAR2) IS
642 SELECT isch.CHANGE_ID, isp.SELECT_CLAUSE, isch.CURRENT_VALUE
643 FROM ITA_SETUP_CHANGE_HISTORY isch, ITA_SETUP_PARAMETERS_B isp
644 WHERE
645 isch.SETUP_GROUP_CODE = p_setup_group_code and
646 isch.CURRENT_VALUE is not null and
647 isch.PARAMETER_CODE = isp.PARAMETER_CODE and
648 isp.SELECT_CLAUSE is not null and
649 LTRIM(isp.SELECT_CLAUSE) is not null; -- cpetriuc - bug 5638086
650
651 l_change_id NUMBER;
652 l_profile_sql VARCHAR2(3000);
653 l_profile_value_code VARCHAR2(3000);
654 l_profile_value_meaning VARCHAR2(3000);
655 l_index_comma NUMBER;
656 l_index_begin NUMBER;
657 l_index_end NUMBER;
658 l_update_sql VARCHAR2(3000);
659 -- cpetriuc end - bug 5163722
660
661 l_prof_rec c_get_profiles_with_sql%ROWTYPE;
662 n NUMBER;
663 l_pos NUMBER;
664 l_vis_op_val_pos NUMBER;
665
666 BEGIN
667 IF LENGTH(p_table_name) IS NULL
668 THEN
669 RETURN 0;
670 END IF;
671
672 SELECT fnd_table.TABLE_ID
673 INTO l_table_id
674 FROM FND_TABLES fnd_table
675 WHERE fnd_table.APPLICATION_ID = p_application_id AND fnd_table.TABLE_NAME = UPPER(p_table_name);
676
677 select setup_gp.SETUP_GROUP_CODE, setup_gp.AUDIT_START_DATE,
678 setup_gp.CONTEXT_PARAMETER_CODE, setup_gp.HIERARCHY_LEVEL hierarchy_level_code,
679 setup_gp.context_parameter_code2,
680 (SELECT column_id FROM ita_setup_parameters_b WHERE parameter_code = setup_gp.CONTEXT_PARAMETER_CODE) column_id,
681 (SELECT column_id FROM ita_setup_parameters_b WHERE parameter_code = setup_gp.CONTEXT_PARAMETER_CODE2) column_id1
682 INTO l_setup_gp_code, l_audit_start_date, l_context_param_code, l_hier_level_code, l_context_param_code2,
683 l_column_id, l_column_id1
684 FROM ITA_SETUP_GROUPS_B setup_gp
685 WHERE setup_gp.TABLE_APP_ID = p_application_id and
686 setup_gp.TABLE_ID = l_table_id;
687
688 select COLUMN_NAME
689 INTO l_column_name
690 from FND_COLUMNS
691 where (APPLICATION_ID, TABLE_ID) = (select TABLE_APP_ID, TABLE_ID
692 from ITA_SETUP_GROUPS_B
693 where SETUP_GROUP_CODE = l_setup_gp_code)
694 and COLUMN_ID = l_column_id;
695
696 select COLUMN_NAME
697 INTO l_column_name1
698 from FND_COLUMNS
699 where (APPLICATION_ID, TABLE_ID) = (select TABLE_APP_ID, TABLE_ID
700 from ITA_SETUP_GROUPS_B
701 where SETUP_GROUP_CODE = l_setup_gp_code)
702 and COLUMN_ID = l_column_id1;
703
704 l_user_id := fnd_global.user_id;
705 l_login_id := fnd_global.conc_login_id;
706 IF l_audit_start_date IS NULL
707 THEN
708 l_del_sql := 'delete from ITA_SETUP_CHANGE_HISTORY where INSTANCE_CODE = ''CURRENT'' and SETUP_GROUP_CODE =''' || l_setup_gp_code || '''';
709 EXECUTE IMMEDIATE l_del_sql;
710 SELECT INSTANCE_CODE INTO l_inst_code FROM ITA_SETUP_INSTANCES_B WHERE CURRENT_FLAG='Y';
711 /* pk1 - level id
712 pk2 - level value
713 pk6 - level value name
714 pk10 - level value application id */
715 l_ins_sql := 'INSERT INTO ITA_SETUP_CHANGE_HISTORY(INSTANCE_CODE, CHANGE_ID, PARAMETER_CODE,' ||
716 'SETUP_GROUP_CODE, CHANGE_AUTHOR, CHANGE_DATE, PK2_VALUE, PK3_VALUE, PK1_VALUE, ' ||
717 'CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ' ||
718 'LAST_UPDATE_DATE, PK6_VALUE, OBJECT_VERSION_NUMBER, CURRENT_VALUE, RECOMMENDED_VALUE, PK5_VALUE, PK10_VALUE) ' ||
719 '(SELECT /*+ PARALLEL(bt) */''' || l_inst_code || ''', ' || 'ITA_SETUP_CHANGE_HISTORY_S1.NEXTVAL, ''' ||
720 l_setup_gp_code || '.''|| (SELECT profile_option_name FROM fnd_profile_options WHERE application_id=bt.application_id and profile_option_id=bt.profile_option_id), ''' ||
721 l_setup_gp_code || ''', ' ||
722 '(SELECT USER_NAME FROM FND_USER WHERE USER_ID=bt.LAST_UPDATED_BY) change_author, bt.LAST_UPDATE_DATE, ' ||
723 'bt.LEVEL_VALUE, bt.PROFILE_OPTION_VALUE, bt.LEVEL_ID, ' ||
724 l_user_id || ', sysdate, ' || l_user_id || ', ' || l_login_id ||
725 ', sysdate, ' ||
726 '(DECODE(bt.LEVEL_ID, 10002, (SELECT application_name FROM fnd_application_tl WHERE application_id=bt.LEVEL_VALUE AND language = USERENV(''LANG'')),' ||
727 '(DECODE(bt.LEVEL_ID, 10003, (SELECT RESPONSIBILITY_NAME FROM fnd_responsibility_tl WHERE ' ||
728 'RESPONSIBILITY_ID=bt.LEVEL_VALUE and APPLICATION_ID=bt.LEVEL_VALUE_APPLICATION_ID AND language = USERENV(''LANG'')), (DECODE(bt.LEVEL_ID, 10004, (SELECT user_name FROM fnd_user ' ||
729 'WHERE user_id=bt.LEVEL_VALUE), (DECODE(bt.LEVEL_ID, 10005, (SELECT node_name from fnd_nodes where node_id=bt.LEVEL_VALUE), ' ||
730 '(DECODE(bt.LEVEL_ID, 10006, (SELECT name from HR_ALL_ORGANIZATION_UNITS_TL where organization_id=bt.LEVEL_ID AND language = USERENV(''LANG'')), null))) ' ||
731 '))))))) level_value_name, 1 obj_ver, bt.profile_option_value, ' ||
732 '(SELECT recommended_value FROM ita_setup_rec_values_vl WHERE default_flag=''Y'' and ' ||
733 'parameter_code=''' || l_setup_gp_code || '.''|| (SELECT profile_option_name FROM ' ||
734 'fnd_profile_options WHERE application_id=bt.application_id and profile_option_id=bt.profile_option_id)) rec_value, ' ||
735 '(SELECT recommended_value FROM ita_setup_rec_values_vl WHERE default_flag=''Y'' and ' ||
736 'parameter_code=''' || l_setup_gp_code || '.''|| (SELECT profile_option_name FROM ' ||
737 'fnd_profile_options WHERE application_id=bt.application_id and profile_option_id=' ||
738 'bt.profile_option_id)) rec_code, bt.LEVEL_VALUE_APPLICATION_ID FROM ' || p_table_name || ' bt WHERE bt.profile_option_value is NOT NULL)';
739 EXECUTE IMMEDIATE l_ins_sql;
740 -- For all profiles in fnd_profile_options that don't exist in ita_setup_parameters_b and
741 -- exist in fnd_profile_option_values,
742 -- insert into ita_setup_parameters_b, ita_setup_parameters_tl
743 l_ins_param_sql := 'INSERT INTO ita_setup_parameters_b (PARAMETER_CODE, SETUP_GROUP_CODE, COLUMN_ID, AUDIT_ENABLED_FLAG, COLUMN_REFERENCE1, COLUMN_REFERENCE2, CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,' ||
744 'LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, SELECT_CLAUSE)' ||
745 '(SELECT ''' || l_setup_gp_code || '.' || ''' || bt.profile_option_name, ''' || l_setup_gp_code || ''', bt.profile_option_id' || ', ''Y'',' ||
746 'bt.profile_option_id, bt.application_id, ' || l_user_id || ', sysdate, sysdate, ' || l_user_id ||
747 ', ' || l_login_id || ', 1, ' ||
748 -- '(UPPER(REPLACE(substr(sql_validation, instr(upper(sql_validation), ''SELECT ''),
749 -- (instr(upper(sql_validation), ''"'' || chr(10) || ''COLUMN='') - instr(upper(sql_validation),
750 -- ''SELECT ''))), substr(sql_validation, instr(sql_validation, ''\"''), instr(sql_validation,
751 -- ''\"'', 1, 2) - instr(sql_validation, ''\"'') + 2), ''visible_option_value''))) select_cl ' ||
752 -- cpetriuc start - bug 5638086
753 -- Removed the space character after "SELECT".
754 -- Introduced call to LENGTH.
755 -- '(UPPER(REPLACE(substr(sql_validation, instr(upper(sql_validation), ''SELECT ''), (instr(upper(sql_validation), ''"'' || chr(10) || ''COLUMN='') - instr(upper(sql_validation), ''SELECT ''))), ''\"'', ''"''))) select_cl ' ||
756 '(UPPER(REPLACE(substr(sql_validation, instr(upper(sql_validation), ''SELECT''), (1 + length(upper(sql_validation)) - instr(upper(sql_validation), ''SELECT''))), ''\"'', ''"''))) select_cl ' ||
757 -- cpetriuc end - bug 5638086
758 'FROM fnd_profile_options bt WHERE ''' || l_setup_gp_code || '.' || ''' || bt.profile_option_name NOT IN ' ||
759 '(SELECT parameter_code FROM ita_setup_parameters_b) AND (bt.profile_option_id, bt.application_id) ' ||
760 'IN (SELECT profile_option_id, application_id from fnd_profile_option_values))';
761 EXECUTE IMMEDIATE l_ins_param_sql;
762 l_ins_param_sql := 'INSERT INTO ita_setup_parameters_tl (PARAMETER_CODE, CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE,' ||
763 'LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, LANGUAGE, SOURCE_LANG, PARAMETER_NAME)' ||
764 '(SELECT PARAMETER_CODE, isp.CREATED_BY, isp.CREATION_DATE, isp.LAST_UPDATE_DATE, isp.LAST_UPDATED_BY, isp.LAST_UPDATE_LOGIN, 1, prof.language, prof.source_lang, prof.user_profile_option_name ' ||
765 'FROM ita_setup_parameters_b isp, fnd_profile_options_tl prof WHERE ''' || l_setup_gp_code || '.' || ''' || prof.profile_option_name = isp.parameter_code ' ||
766 'AND (isp.parameter_code, prof.language) NOT IN (SELECT parameter_code, language from ita_setup_parameters_tl))';
767 EXECUTE IMMEDIATE l_ins_param_sql;
768 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current State fetched for table - ' || p_table_name);
769 ELSE
770 -- Log that current state already fetched for l_setup_gp_code
771 --dbms_output.put_line('Current State already fetched for table - ' || p_table_name);
772 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current State already fetched for table - ' || p_table_name);
773 RETURN 2;
774 END IF;
775
776 -- cpetriuc start - bug 5163722
777 FOR l_profile_change IN c_get_profiles_with_sql(l_setup_gp_code)
778 LOOP EXIT WHEN c_get_profiles_with_sql%NOTFOUND;
779
780 l_change_id := l_profile_change.CHANGE_ID;
781 l_profile_sql := UPPER(l_profile_change.SELECT_CLAUSE);
782 l_profile_value_code := l_profile_change.CURRENT_VALUE;
783
784 /*
785 -- cpetriuc start - bug 5410296
786 l_index_comma := INSTR(l_profile_sql, ',');
787
788 IF INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE') < INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE')
789 THEN
790 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' code, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
791 --l_profile_sql := REPLACE(l_profile_sql, 'INTO :PROFILE_OPTION_VALUE,', ' meaning ');
792 --l_profile_sql := REPLACE(l_profile_sql, ':VISIBLE_OPTION_VALUE');
793 l_index_begin := INSTR(l_profile_sql, 'INTO');
794 -- During our investigations, the following query returned no rows:
795 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
796 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
797 l_index_end := INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE');
798 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' meaning ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
799 ELSE
800 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' meaning, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
801 --l_profile_sql := REPLACE(l_profile_sql, 'INTO :VISIBLE_OPTION_VALUE,', ' code ');
802 --l_profile_sql := REPLACE(l_profile_sql, ':PROFILE_OPTION_VALUE');
803 l_index_begin := INSTR(l_profile_sql, 'INTO');
804 -- During our investigations, the following query returned no rows:
805 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
806 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
807 l_index_end := INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE');
808 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' code ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
809 END IF;
810
811 -- Remove from the query the text between double quotes.
812 WHILE INSTR(l_profile_sql, '"') <> 0 LOOP
813 l_index_begin := INSTR(l_profile_sql, '"');
814 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_begin + 1, LENGTH(l_profile_sql) - l_index_begin);
815 l_index_end := INSTR(l_profile_sql, '"');
816 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_end + 1, LENGTH(l_profile_sql) - l_index_end);
817 END LOOP;
818
819
820 BEGIN
821
822 l_profile_sql := 'SELECT meaning FROM ( ' || l_profile_sql || ' ) WHERE code = ''' || l_profile_value_code || '''';
823 EXECUTE IMMEDIATE l_profile_sql INTO l_profile_value_meaning;
824 -- cpetriuc end - bug 5410296
825 */
826
827
828 BEGIN
829
830 l_profile_value_meaning := get_profile_value_meaning(l_profile_sql, l_profile_value_code);
831
832 l_update_sql := 'UPDATE ITA_SETUP_CHANGE_HISTORY SET CURRENT_VALUE = ''' || l_profile_value_meaning || ''' WHERE CHANGE_ID = ' || l_change_id;
833 EXECUTE IMMEDIATE l_update_sql;
834
835 EXCEPTION
836 WHEN OTHERS THEN fnd_file.PUT_LINE(fnd_file.LOG, SUBSTR(SQLERRM, 1, 2000));
837
838 END;
839
840
841 END LOOP;
842 -- cpetriuc end - bug 5163722
843
844 RETURN 1;
845 EXCEPTION
846 WHEN OTHERS
847 THEN
848 fnd_file.put_line(fnd_file.LOG, SUBSTR (SQLERRM, 1, 2000));
849 --dbms_output.put_line(SUBSTR (SQLERRM, 1, 2000));
850 --l_ret_val := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Current state not fetched for ' || p_table_name);
851 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current state not fetched for ' || p_table_name);
852 RETURN 0;
853 END record_profile_current_state;
854
855
856
857
858 -- cpetriuc start - bug 5410296
859 FUNCTION get_profile_value_meaning
860 (p_profile_sql IN VARCHAR2,
861 p_profile_value_code IN VARCHAR2)
862 RETURN VARCHAR2
863 IS
864
865 l_profile_sql VARCHAR2(3000);
866 l_profile_value_meaning VARCHAR2(3000);
867 --l_index_comma NUMBER; // commented by cpetriuc - bug 5235411
868 --l_index_begin NUMBER; // commented by cpetriuc - bug 5235411
869 --l_index_end NUMBER; // commented by cpetriuc - bug 5235411
870
871 BEGIN
872
873 /* comment block by cpetriuc - bug 5235411
874 l_profile_sql := UPPER(p_profile_sql);
875 l_index_comma := INSTR(l_profile_sql, ',');
876
877 IF INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE') < INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE')
878 THEN
879 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' code, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
880 l_index_begin := INSTR(l_profile_sql, 'INTO');
881 -- During our investigations, the following query returned no rows:
882 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
883 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
884 l_index_end := INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE');
885 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' meaning ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
886 ELSE
887 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' meaning, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
888 l_index_begin := INSTR(l_profile_sql, 'INTO');
889 -- During our investigations, the following query returned no rows:
890 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
891 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
892 l_index_end := INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE');
893 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' code ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
894 END IF;
895
896 -- Remove from the query the text between double quotes.
897 WHILE INSTR(l_profile_sql, '"') <> 0 LOOP
898 l_index_begin := INSTR(l_profile_sql, '"');
899 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_begin + 1, LENGTH(l_profile_sql) - l_index_begin);
900 l_index_end := INSTR(l_profile_sql, '"');
901 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_end + 1, LENGTH(l_profile_sql) - l_index_end);
902 END LOOP;
903 */
904
905 l_profile_sql := strip_profile_query(p_profile_sql);
906 l_profile_sql := 'SELECT value_meaning FROM ( ' || l_profile_sql || ' ) WHERE value_code = ''' || p_profile_value_code || '''';
907 EXECUTE IMMEDIATE l_profile_sql INTO l_profile_value_meaning;
908
909 RETURN l_profile_value_meaning;
910
911 EXCEPTION
912 WHEN OTHERS
913 THEN
914 fnd_file.put_line(fnd_file.LOG, SUBSTR(SQLERRM, 1, 2000));
915 fnd_file.put_line(fnd_file.LOG, 'GET_PROFILE_VALUE_MEANING: SQL Statement: ' || l_profile_sql); -- cpetriuc - bug 5638086
916 --dbms_output.put_line(SUBSTR(SQLERRM, 1, 2000));
917 RETURN p_profile_value_code;
918
919 END get_profile_value_meaning;
920 -- cpetriuc end - bug 5410296
921
922
923
924
925 -- cpetriuc start - bug 5235411
926 FUNCTION strip_profile_query
927 (p_profile_sql IN VARCHAR2)
928 RETURN VARCHAR2
929 IS
930
931 l_profile_sql VARCHAR2(3000);
932 l_index_comma NUMBER;
933 l_index_begin NUMBER;
934 l_index_end NUMBER;
935
936 BEGIN
937
938 --l_profile_sql := UPPER(p_profile_sql); -- commented by cpetriuc - bug 5638086
939 -- cpetriuc start - bug 5638086
940 l_profile_sql := strip_double_quotes(p_profile_sql);
941 l_profile_sql := strip_aliases(l_profile_sql);
942 -- cpetriuc end - bug 5638086
943
944 l_index_comma := INSTR(l_profile_sql, ',');
945
946 IF INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE') < INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE')
947 THEN
948 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' value_code, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
949 l_index_begin := INSTR(l_profile_sql, 'INTO');
950 -- During our investigations, the following query returned no rows:
951 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
952 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
953 l_index_end := INSTR(l_profile_sql, ':VISIBLE_OPTION_VALUE');
954 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' value_meaning ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
955 ELSE
956 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_comma - 1) || ' value_meaning, ' || SUBSTR(l_profile_sql, l_index_comma + 1, LENGTH(l_profile_sql) - l_index_comma);
957 l_index_begin := INSTR(l_profile_sql, 'INTO');
958 -- During our investigations, the following query returned no rows:
959 -- select SQL_VALIDATION from FND_PROFILE_OPTIONS
960 -- where upper(SQL_VALIDATION) like '%INTO%INTO%'
961 l_index_end := INSTR(l_profile_sql, ':PROFILE_OPTION_VALUE');
962 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || ' value_code ' || SUBSTR(l_profile_sql, l_index_end + 21, LENGTH(l_profile_sql) - l_index_end - 20);
963 END IF;
964
965 /*
966 -- commented by cpetriuc - bug 5638086
967 -- Remove from the query the text between double quotes.
968 WHILE INSTR(l_profile_sql, '"') <> 0 LOOP
969 l_index_begin := INSTR(l_profile_sql, '"');
970 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_begin + 1, LENGTH(l_profile_sql) - l_index_begin);
971 l_index_end := INSTR(l_profile_sql, '"');
972 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_end + 1, LENGTH(l_profile_sql) - l_index_end);
973 END LOOP;
974 */
975
976 -- cpetriuc - bug 5638086
977 -- Reorder the columns before returning, in the order (VALUE_MEANING, VALUE_CODE),
978 -- because of limitations in the OA Framework that were discovered during the
979 -- creation of the dynamic view object definition in method
980 -- createRecommendedValueLOVView of
981 -- java/setup/server/RecommendedValuesBuildAMImpl.java.
982 -- Please see that file for more information.
983 l_profile_sql := 'SELECT value_meaning, value_code FROM ( ' || l_profile_sql || ' )';
984
985 RETURN l_profile_sql;
986
987 EXCEPTION
988 WHEN OTHERS
989 THEN
990 fnd_file.put_line(fnd_file.LOG, SUBSTR(SQLERRM, 1, 2000));
991 fnd_file.put_line(fnd_file.LOG, 'STRIP_PROFILE_QUERY: SQL Statement: ' || l_profile_sql); -- cpetriuc - bug 5638086
992 --dbms_output.put_line(SUBSTR(SQLERRM, 1, 2000));
993 RETURN p_profile_sql;
994
995 END strip_profile_query;
996 -- cpetriuc end - bug 5235411
997
998
999
1000
1001 -- cpetriuc start - bug 5638086
1002 FUNCTION strip_double_quotes
1003 (p_profile_sql IN VARCHAR2)
1004 RETURN VARCHAR2
1005 IS
1006
1007 l_profile_sql VARCHAR2(3000);
1008 l_index_column NUMBER;
1009 l_index_title NUMBER;
1010 l_index_begin NUMBER;
1011 l_index_end NUMBER;
1012
1013 BEGIN
1014
1015 l_profile_sql := UPPER(p_profile_sql);
1016
1017 -- Some SQL validation strings in FND_PROFILE_OPTIONS.SQL_VALIDATION
1018 -- contain the code "TITLE=" before the code "COLUMN=". The logic
1019 -- in RECORD_PROFILE_CURRENT_STATE above used to check for the latter.
1020 -- As of bug 5638086, we are not checking for that anymore in the code
1021 -- above. Instead, we do that processing here.
1022 l_index_column := INSTR(l_profile_sql, '"' || fnd_global.local_chr(10) || 'COLUMN=');
1023 l_index_title := INSTR(l_profile_sql, '"' || fnd_global.local_chr(10) || 'TITLE=');
1024
1025 l_index_end := l_index_column;
1026 IF (l_index_title <> 0) THEN
1027 IF (l_index_end = 0 or l_index_title < l_index_end) THEN
1028 l_index_end := l_index_title;
1029 END IF;
1030 END IF;
1031
1032 IF l_index_end <> 0 THEN
1033 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_end - 1);
1034 END IF;
1035
1036 -- Remove from the query the text between double quotes.
1037 WHILE INSTR(l_profile_sql, '"') <> 0 LOOP
1038 l_index_begin := INSTR(l_profile_sql, '"');
1039 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_begin + 1, LENGTH(l_profile_sql) - l_index_begin);
1040 l_index_end := INSTR(l_profile_sql, '"');
1041 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_begin - 1) || SUBSTR(l_profile_sql, l_index_end + 1, LENGTH(l_profile_sql) - l_index_end);
1042 END LOOP;
1043
1044 RETURN l_profile_sql;
1045
1046 EXCEPTION
1047 WHEN OTHERS
1048 THEN
1049 fnd_file.put_line(fnd_file.LOG, SUBSTR(SQLERRM, 1, 2000));
1050 fnd_file.put_line(fnd_file.LOG, 'STRIP_DOUBLE_QUOTES: SQL Statement: ' || l_profile_sql);
1051 --dbms_output.put_line(SUBSTR(SQLERRM, 1, 2000));
1052 RETURN p_profile_sql;
1053
1054 END strip_double_quotes;
1055 -- cpetriuc end - bug 5638086
1056
1057
1058
1059
1060 -- cpetriuc start - bug 5638086
1061 FUNCTION strip_aliases
1062 (p_profile_sql IN VARCHAR2)
1063 RETURN VARCHAR2
1064 IS
1065
1066 l_profile_sql VARCHAR2(3000);
1067 l_index_select NUMBER;
1068 l_index_comma NUMBER;
1069 l_index_into NUMBER;
1070 l_index_mark NUMBER;
1071 letter VARCHAR2(1);
1072
1073 BEGIN
1074
1075 l_profile_sql := UPPER(p_profile_sql);
1076 l_profile_sql := REPLACE(l_profile_sql, fnd_global.local_chr(9), ' '); -- Replace tabs.
1077 l_profile_sql := REPLACE(l_profile_sql, fnd_global.local_chr(10), ' '); -- Replace new lines.
1078
1079 -- Try to find the beginning of the first column name.
1080 l_index_select := INSTR(l_profile_sql, 'SELECT');
1081 l_index_mark := l_index_select + 6;
1082 LOOP
1083 letter := SUBSTR(l_profile_sql, l_index_mark, 1);
1084 IF letter <> ' ' and letter <> fnd_global.local_chr(9) and letter <> fnd_global.local_chr(10) THEN EXIT; END IF;
1085 l_index_mark := l_index_mark + 1;
1086 END LOOP;
1087
1088 -- Check that this word is not "DISTINCT".
1089 IF SUBSTR(l_profile_sql, l_index_mark, 8) = 'DISTINCT' THEN
1090 l_index_mark := l_index_mark + 8;
1091 -- Find the beginning of the first column name.
1092 LOOP
1093 letter := SUBSTR(l_profile_sql, l_index_mark, 1);
1094 IF letter <> ' ' and letter <> fnd_global.local_chr(9) and letter <> fnd_global.local_chr(10) THEN EXIT; END IF;
1095 l_index_mark := l_index_mark + 1;
1096 END LOOP;
1097 END IF;
1098
1099 -- Delete the first column alias.
1100 l_index_mark := INSTR(l_profile_sql, ' ', l_index_mark, 1);
1101 l_index_comma := INSTR(l_profile_sql, ',');
1102 IF l_index_mark < l_index_comma THEN
1103 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_mark - 1) || SUBSTR(l_profile_sql, l_index_comma, LENGTH(l_profile_sql) - l_index_comma + 1);
1104 END IF;
1105
1106 -- Find the beginning of the second column name.
1107 l_index_comma := INSTR(l_profile_sql, ',');
1108 l_index_mark := l_index_comma + 1;
1109 LOOP
1110 letter := SUBSTR(l_profile_sql, l_index_mark, 1);
1111 IF letter <> ' ' and letter <> fnd_global.local_chr(9) and letter <> fnd_global.local_chr(10) THEN EXIT; END IF;
1112 l_index_mark := l_index_mark + 1;
1113 END LOOP;
1114
1115 -- Delete the second column alias.
1116 l_index_mark := INSTR(l_profile_sql, ' ', l_index_mark, 1);
1117 l_index_into := INSTR(l_profile_sql, 'INTO');
1118 IF l_index_mark < l_index_into THEN
1119 l_profile_sql := SUBSTR(l_profile_sql, 1, l_index_mark) || SUBSTR(l_profile_sql, l_index_into, LENGTH(l_profile_sql) - l_index_into + 1);
1120 END IF;
1121
1122 RETURN l_profile_sql;
1123
1124 EXCEPTION
1125 WHEN OTHERS
1126 THEN
1127 fnd_file.put_line(fnd_file.LOG, SUBSTR(SQLERRM, 1, 2000));
1128 fnd_file.put_line(fnd_file.LOG, 'STRIP_ALIASES: SQL Statement: ' || l_profile_sql);
1129 --dbms_output.put_line(SUBSTR(SQLERRM, 1, 2000));
1130 RETURN p_profile_sql;
1131
1132 END strip_aliases;
1133 -- cpetriuc end - bug 5638086
1134
1135
1136
1137
1138 end ITA_RECORD_CURR_STATUS_PKG;