1 PACKAGE BODY AMW_LOAD_SOD_DATA AS
2 /* $Header: amwsodwb.pls 120.2.12000000.3 2007/06/14 06:59:33 ptulasi ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_LOAD_SOD_DATA';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwsodwb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 v_error_msg VARCHAR2(2000);
10 v_err_msg VARCHAR2(2000);
11 v_error_found boolean;
12
13 AMW_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
14 AMW_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
15 AMW_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
16
17
18 /* To insert data */
19 PROCEDURE insert_data(
20 errbuf OUT NOCOPY VARCHAR2
21 ,retcode OUT NOCOPY VARCHAR2
22 ,p_batch_id IN NUMBER
23 )
24 IS
25 CURSOR c_constraint_data IS
26 SELECT cst_interface_id,
27 cst_name,
28 risk_name,
29 cst_start_date,
30 cst_type_code,
31 cst_entries_function_id,
32 cst_entries_resp_id
33 FROM amw_constraint_interface
34 WHERE batch_id = p_batch_id;
35
36
37 -- Invalid Function
38 CURSOR invalid_func IS
39 SELECT cst_interface_id
40 FROM amw_constraint_interface
41 WHERE batch_id = p_batch_id
42 AND cst_type_code in ('ALL','ME','SET')
43 AND cst_violat_obj_type = 'FUNC'
44 AND cst_entries_function_id IS NOT NULL
45 AND NOT EXISTS
46 ( SELECT 'Y'
47 FROM fnd_form_functions
48 WHERE function_id = cst_entries_function_id
49 )
50 UNION
51 SELECT cst_interface_id
52 FROM amw_constraint_interface
53 WHERE batch_id = p_batch_id
54 AND cst_type_code in ('ALL','ME','SET')
55 AND cst_violat_obj_type = 'CP'
56 AND cst_entries_function_id IS NOT NULL
57 AND NOT EXISTS
58 (
59 SELECT 'Y'
60 FROM fnd_request_group_units rgu ,
61 fnd_concurrent_programs cpv
62 WHERE rgu.request_unit_type = 'P'
63 AND rgu.request_unit_id = cpv.concurrent_program_id
64 AND cpv.enabled_flag = 'Y'
65 AND cpv.concurrent_program_id =cst_entries_function_id
66 );
67
68
69 -- Invalid Responsibility
70 CURSOR invalid_resp IS
71 SELECT cst_interface_id
72 FROM amw_constraint_interface
73 WHERE batch_id = p_batch_id
74 AND cst_entries_resp_id IS NOT NULL
75 AND NOT EXISTS
76 ( SELECT 'Y'
77 FROM FND_RESPONSIBILITY
78 WHERE responsibility_id = cst_entries_resp_id
79 AND start_date <= sysdate
80 AND (end_date >= sysdate OR end_date IS NULL)
81 );
82
83 -- Invalid Type Code
84 CURSOR invalid_type_code IS
85 SELECT cst_interface_id
86 FROM amw_constraint_interface
87 WHERE batch_id = p_batch_id
88 AND NOT EXISTS
89 ( SELECT 'Y'
90 FROM amw_lookups
91 WHERE lookup_code = cst_type_code
92 AND lookup_type='AMW_CONSTRAINT_TYPE'
93 AND enabled_flag ='Y'
94 AND (end_date_active > SYSDATE OR end_date_active IS NULL)
95 );
96
97 -- Object type is responsibility. Responsibility should be entered
98 -- and function should not be entered
99 CURSOR invalid_resptype IS
100 SELECT cst_interface_id,
101 cst_entries_resp_id,
102 cst_entries_function_id
103 FROM amw_constraint_interface
104 WHERE batch_id = p_batch_id
105 AND (substr(cst_type_code,1,4) = 'RESP')
106 AND (cst_entries_resp_id IS NULL OR cst_entries_function_id IS NOT NULL);
107
108 -- Object type is function. Function should be entered
109 -- Responsibility should not be entered
110 CURSOR invalid_functype IS
111 SELECT cst_interface_id,
112 cst_entries_resp_id,
113 cst_entries_function_id
114 FROM amw_constraint_interface
115 WHERE batch_id = p_batch_id
116 AND cst_type_code IN ('ALL','ME','SET')
117 AND (cst_entries_function_id IS NULL OR cst_entries_resp_id IS NOT NULL);
118
119 -- CST_VIOLAT_OBJ_TYPE cannot be null for Function Type constraint
120 CURSOR invalid_obj_type IS
121 SELECT cst_interface_id
122 FROM amw_constraint_interface
123 WHERE batch_id = p_batch_id
124 AND cst_type_code in ('ALL','ME','SET')
125 AND cst_entries_function_id IS NOT NULL
126 AND (cst_violat_obj_type IS NULL OR cst_violat_obj_type NOT IN ('FUNC','CP') );
127
128
129 -- CST_ENTRIES_GROUP_CODE cannot be null for Incompatible sets Type constraint
130 CURSOR invalid_group_code IS
131 SELECT cst_interface_id
132 FROM amw_constraint_interface
133 WHERE batch_id = p_batch_id
134 AND cst_type_code in ('SET','RESPSET')
135 AND (cst_entries_group_code IS NULL OR cst_entries_group_code NOT IN ('1','2'));
136
137 v_name_exists number;
138 v_risk_exists number;
139 v_function_exists number;
140 v_resp_exists number;
141 v_type_exists number;
142 v_interface_status amw_constraint_interface.interface_status%TYPE;
143 BEGIN
144 begin
145
146 for cstfunc_rec in invalid_func LOOP
147 v_error_msg := 'Not a valid Incompatible Function';
148 update_interface_with_error(v_error_msg,cstfunc_rec.cst_interface_id);
149 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || cstfunc_rec.cst_interface_id );
150 end loop;
151
152 for cstresp_rec in invalid_resp LOOP
153 v_error_msg := 'Not a valid Incompatible Responsibility';
154 update_interface_with_error(v_error_msg,cstresp_rec.cst_interface_id);
155 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || cstresp_rec.cst_interface_id );
156 end loop;
157
158 for typecode_rec in invalid_type_code LOOP
159 v_error_msg := 'Not a valid Constraint Type Code';
160 update_interface_with_error(v_error_msg,typecode_rec.cst_interface_id);
161 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || typecode_rec.cst_interface_id );
162 end loop;
163
164 for cstresptype_rec in invalid_resptype LOOP
165 if cstresptype_rec.cst_entries_resp_id is null then
166 v_error_msg := 'Object type is for Responsibility. But responsibility is not defined';
167 else if cstresptype_rec.cst_entries_function_id is not null then
168 v_error_msg := 'Object type is for Responsibility. But function is defined';
169 end if;
170 end if;
171 update_interface_with_error(v_error_msg,cstresptype_rec.cst_interface_id);
172 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || cstresptype_rec.cst_interface_id );
173 end loop;
174
175 for cstfunctype_rec in invalid_functype LOOP
176 if cstfunctype_rec.cst_entries_function_id is null then
177 v_error_msg := 'Object type is for Function. But function is not defined';
178 else if cstfunctype_rec.cst_entries_resp_id is not null then
179 v_error_msg := 'Object type is for Function. But responsibility is defined';
180 end if;
181 end if;
182 update_interface_with_error(v_error_msg,cstfunctype_rec.cst_interface_id);
183 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || cstfunctype_rec.cst_interface_id );
184 end loop;
185
186 for objtype_rec in invalid_obj_type LOOP
187 v_error_msg := 'Not a valid Constraint Object Type';
188 update_interface_with_error(v_error_msg,objtype_rec.cst_interface_id);
189 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || objtype_rec.cst_interface_id );
190 end loop;
191
192 for grpcode_rec in invalid_group_code LOOP
193 v_error_msg := 'Not a valid Constraint Function Set';
194 update_interface_with_error(v_error_msg,grpcode_rec.cst_interface_id);
195 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || grpcode_rec.cst_interface_id );
196 end loop;
197
198 IF (v_error_msg is NULL OR v_error_msg = NULL) THEN
199 AMW_LOAD_CONSTRAINT_DATA.create_constraints
200 ( errbuf => v_error_msg,
201 retcode => v_err_msg,
202 p_batch_id => p_batch_id,
203 p_user_id => g_user_id);
204 ELSE
205 errbuf := v_error_msg;
206 retcode := v_err_msg;
207 END IF;
208
209 fnd_file.put_line (fnd_file.LOG, 'After Calling the webadi procedure');
210 EXCEPTION
211 WHEN OTHERS
212 THEN
213 v_err_msg := 'Error during package processing '
214 || SUBSTR (SQLERRM, 1, 100);
215 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
216 END;
217
218 END insert_data;
219 --
220 -- procedure update_interface_with_error
221 --
222 --
223 PROCEDURE update_interface_with_error (
224 p_err_msg IN VARCHAR2
225 ,p_interface_id IN NUMBER
226 )
227 IS
228 l_interface_status amw_constraint_interface.interface_status%TYPE;
229 BEGIN
230 ROLLBACK; -- rollback any inserts done during the current loop process
231 v_error_found := TRUE;
232
233 BEGIN
234 SELECT interface_status INTO l_interface_status FROM amw_constraint_interface
235 WHERE cst_interface_id = p_interface_id;
236 if l_interface_status is not null then
237 l_interface_status := l_interface_status || ' ; ';
238 end if;
239 l_interface_status := l_interface_status || p_err_msg || ' ';
240 UPDATE amw_constraint_interface SET interface_status = l_interface_status
241 ,error_flag = 'Y'
242 WHERE cst_interface_id = p_interface_id;
243 COMMIT;
244 EXCEPTION
245 WHEN OTHERS
246 THEN
247 v_err_msg := 'Error during package processing ' || ' interface_id: = '
248 || p_interface_id || SUBSTR (SQLERRM, 1, 100);
249 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
250 END;
251
252
253 END update_interface_with_error;
254
255 -- ===============================================================
256 -- Procedure name
257 -- create_constraint_waivers
258 -- Purpose
259 -- import constraint waivers
260 -- from interface table to AMW_CONSTRAINT_WAIVERS_B and
261 -- AMW_CONSTRAINT_WAIVERS_TL
262 -- Notes
263 -- this procedure is called in Concurrent Executable
264 -- ===============================================================
265 PROCEDURE create_constraint_waivers (
266 ERRBUF OUT NOCOPY VARCHAR2,
267 RETCODE OUT NOCOPY VARCHAR2,
268 p_batch_id IN NUMBER := NULL,
269 p_del_after_import IN VARCHAR2 := 'Y'
270 )
271 IS
272 L_API_NAME CONSTANT VARCHAR2(30) := 'create_constraint_waivers';
273 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
274
275 TYPE waiverCurTyp IS REF CURSOR;
276 l_waiver_c waiverCurTyp;
277
278 -- Cursor to check if the constraint for which the waiver is specified is
279 -- valid
280 CURSOR c_invld_cst_name_batch IS
281 SELECT Interface_id
282 FROM amw_cst_waiver_interface
283 WHERE batch_id = p_batch_id
284 AND constraint_rev_id IS NULL
285 AND (process_flag IS NULL OR process_flag = 'N');
286
287 CURSOR c_invld_cst_name IS
288 SELECT Interface_id
289 FROM amw_cst_waiver_interface
290 WHERE constraint_rev_id IS NULL
291 AND (process_flag IS NULL OR process_flag = 'N');
292
293 -- Cursor to check if the responsibility constraint has responsibility waivers
294 CURSOR c_invalid_resp_cst_batch IS
295 SELECT Interface_id
296 FROM amw_cst_waiver_interface
297 WHERE type_code in ('RESPALL','RESPME','RESPSET')
298 AND object_type = 'RESP'
299 AND batch_id = p_batch_id
300 AND pk1 IS NOT NULL
301 AND pk2 IS NOT NULL
302 AND (process_flag IS NULL OR process_flag = 'N');
303
304 CURSOR c_invalid_resp_cst IS
305 SELECT Interface_id
306 FROM amw_cst_waiver_interface
307 WHERE type_code in ('RESPALL','RESPME','RESPSET')
308 AND object_type = 'RESP'
309 AND pk1 IS NOT NULL
310 AND pk2 IS NOT NULL
311 AND (process_flag IS NULL OR process_flag = 'N');
312
313 -- Cursor to check if a user waiver is defined without specifing a valid user
314 CURSOR c_invalid_user_waiver_batch IS
315 SELECT Interface_id
316 FROM amw_cst_waiver_interface
317 WHERE object_type = 'USER'
318 AND batch_id = p_batch_id
319 AND pk1 IS NULL
320 AND (process_flag IS NULL OR process_flag = 'N');
321
322 CURSOR c_invalid_user_waiver IS
323 SELECT Interface_id
324 FROM amw_cst_waiver_interface
325 WHERE object_type = 'USER'
326 AND pk1 IS NULL
327 AND (process_flag IS NULL OR process_flag = 'N');
328
329
330 -- Cursor to check if a responsibility waiver is defined without specifing a
331 -- valid responsibility
332 CURSOR c_invalid_resp_waiver_batch IS
333 SELECT Interface_id
334 FROM amw_cst_waiver_interface
335 WHERE object_type = 'RESP'
336 AND batch_id = p_batch_id
337 AND pk1 IS NULL
338 AND pk2 IS NULL
339 AND (process_flag IS NULL OR process_flag = 'N');
340
341 CURSOR c_invalid_resp_waiver IS
342 SELECT Interface_id
343 FROM amw_cst_waiver_interface
344 WHERE object_type = 'RESP'
345 AND pk1 IS NULL
346 AND pk2 IS NULL
347 AND (process_flag IS NULL OR process_flag = 'N');
348
349
350 -- Cursor to check if a start_date is valid. The start date should be greater
351 -- or equal to sysdate
352 CURSOR c_invalid_start_date_batch IS
353 SELECT Interface_id
354 FROM amw_cst_waiver_interface
355 WHERE TRUNC(start_date)<TRUNC(sysdate)
356 AND batch_id = p_batch_id
357 AND (process_flag IS NULL OR process_flag = 'N');
358
359 CURSOR c_invalid_start_date IS
360 SELECT Interface_id
361 FROM amw_cst_waiver_interface
362 WHERE TRUNC(start_date)<TRUNC(sysdate)
363 AND (process_flag IS NULL OR process_flag = 'N');
364
365 -- ptulasi : 06/01/2007 : Bug 6067714:
366 -- Cursor to check if a start_date is valid. The start date should be greater
367 -- or equal to the sysdate. If the waiver start date is past to the sysdate, then
368 -- the waiver start date should be set to constraint start date if the constraint
369 -- start date is in future or else it is set to the sysdate.
370 CURSOR c_invalid_st_date_batch IS
371 SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
372 FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
373 WHERE acwi.constraint_name = acv.constraint_name
374 AND acwi.constraint_name IS NOT NULL
375 AND acv.start_date IS NOT NULL
376 AND TRUNC(acwi.start_date)<TRUNC(acv.start_date)
377 AND batch_id = p_batch_id
378 AND (process_flag IS NULL OR process_flag = 'N');
379
380 CURSOR c_invalid_st_date IS
381 SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
382 FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
383 WHERE acwi.constraint_name = acv.constraint_name
384 AND acwi.constraint_name IS NOT NULL
385 AND acv.start_date IS NOT NULL
386 AND TRUNC(acwi.start_date)<TRUNC(acv.start_date)
387 AND (process_flag IS NULL OR process_flag = 'N');
388
389 -- Cursor to check if a end_date is valid. The end date should be not be less
390 -- than sysdate
391 CURSOR c_invalid_end_date_batch IS
392 SELECT Interface_id
393 FROM amw_cst_waiver_interface
394 WHERE ( TRUNC(end_date)< TRUNC(start_date)
395 OR TRUNC(end_date)<TRUNC(sysdate) )
396 AND batch_id = p_batch_id
397 AND (process_flag IS NULL OR process_flag = 'N');
398
399 CURSOR c_invalid_end_date IS
400 SELECT Interface_id
401 FROM amw_cst_waiver_interface
402 WHERE ( TRUNC(end_date)< TRUNC(start_date)
403 OR TRUNC(end_date)<TRUNC(sysdate) )
404 AND (process_flag IS NULL OR process_flag = 'N');
405
406
407 -- Cursor to check if a duplicate user waiver is specified for a constraint
408 -- This check should consider the user waivers in the interface table as well
409 -- as the user waiver allready existing in the constraint
410 CURSOR c_duplicate_user_waiver_batch IS
411 SELECT acwi.Interface_id
412 FROM amw_cst_waiver_interface acwi,
413 amw_constraint_waivers_b cstw
414 WHERE acwi.object_type = 'USER'
415 AND cstw.object_type = 'USER'
416 AND acwi.batch_id = p_batch_id
417 AND cstw.constraint_rev_id= acwi.constraint_rev_id
418 AND cstw.pk1 = acwi.pk1
419 AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
420 AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date) )
421 AND acwi.constraint_rev_id IS NOT NULL
422 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
423 UNION
424 SELECT acwi.Interface_id
425 FROM amw_cst_waiver_interface acwi
426 WHERE acwi.object_type = 'USER'
427 AND acwi.batch_id = p_batch_id
428 AND acwi.constraint_rev_id IS NOT NULL
429 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
430 AND EXISTS ( SELECT 'Y'
431 FROM amw_cst_waiver_interface acw
432 WHERE acw.batch_id = p_batch_id
433 AND acw.object_type = 'USER'
434 AND acw.pk1 = acwi.pk1
435 AND acw.object_type = acwi.object_type
436 AND acw.Interface_id <> acwi.Interface_id
437 AND acw.constraint_rev_id = acwi.constraint_rev_id
438 AND acw.constraint_rev_id IS NOT NULL
439 AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
440 );
441
442 CURSOR c_duplicate_user_waiver IS
443 SELECT acwi.Interface_id
444 FROM amw_cst_waiver_interface acwi,
445 amw_constraint_waivers_b cstw
446 WHERE acwi.object_type = 'USER'
447 AND cstw.object_type = 'USER'
448 AND cstw.constraint_rev_id= acwi.constraint_rev_id
449 AND cstw.pk1 = acwi.pk1
450 AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
451 AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date) )
452 AND acwi.constraint_rev_id IS NOT NULL
453 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
454 UNION
455 SELECT acwi.Interface_id
456 FROM amw_cst_waiver_interface acwi
457 WHERE acwi.object_type = 'USER'
458 AND acwi.constraint_rev_id IS NOT NULL
459 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
460 AND EXISTS ( SELECT 'Y'
461 FROM amw_cst_waiver_interface acw
462 WHERE acw.object_type = 'USER'
463 AND acw.pk1 = acwi.pk1
464 AND acw.object_type = acwi.object_type
465 AND acw.Interface_id <> acwi.Interface_id
466 AND acw.constraint_rev_id = acwi.constraint_rev_id
467 AND acw.constraint_rev_id IS NOT NULL
468 AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
469 );
470
471 -- Cursor to check if a duplicate responsibility waiver is specified for a constraint
472 -- This check should consider the responsibility waivers in the interface table as well
473 -- as the user waiver allready existing in the constraint
474 CURSOR c_duplicate_resp_waiver_batch IS
475 SELECT acwi.Interface_id
476 FROM amw_cst_waiver_interface acwi,
477 amw_constraint_waivers_b cstw
478 WHERE acwi.object_type = 'RESP'
479 AND cstw.object_type = 'RESP'
480 AND acwi.batch_id = p_batch_id
481 AND cstw.constraint_rev_id= acwi.constraint_rev_id
482 AND cstw.pk1 = acwi.pk1
483 AND cstw.pk2 = acwi.pk2
484 AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
485 AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date))
486 AND acwi.constraint_rev_id IS NOT NULL
487 AND acwi.type_code in ('ALL','ME','SET')
488 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
489 UNION
490 SELECT acwi.Interface_id
491 FROM amw_cst_waiver_interface acwi
492 WHERE acwi.object_type = 'RESP'
493 AND acwi.batch_id = p_batch_id
494 AND acwi.constraint_rev_id IS NOT NULL
495 AND acwi.type_code in ('ALL','ME','SET')
496 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
497 AND EXISTS ( SELECT 'Y'
498 FROM amw_cst_waiver_interface acw
499 WHERE acw.batch_id = p_batch_id
500 AND acw.object_type = 'RESP'
501 AND acw.pk1 = acwi.pk1
502 AND acw.pk2 = acwi.pk2
503 AND acw.object_type = acwi.object_type
504 AND acw.Interface_id <> acwi.Interface_id
505 AND acw.constraint_rev_id = acwi.constraint_rev_id
506 AND acw.constraint_rev_id IS NOT NULL
507 AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
508 );
509
510 CURSOR c_duplicate_resp_waiver IS
511 SELECT acwi.Interface_id
512 FROM amw_cst_waiver_interface acwi,
513 amw_constraint_waivers_b cstw
514 WHERE acwi.object_type = 'RESP'
515 AND cstw.object_type = 'RESP'
516 AND cstw.constraint_rev_id= acwi.constraint_rev_id
517 AND cstw.pk1 = acwi.pk1
518 AND cstw.pk2 = acwi.pk2
519 AND TRUNC(cstw.start_date)=TRUNC(acwi.start_date)
520 AND (cstw.end_date IS NULL OR TRUNC(cstw.end_date)=TRUNC(acwi.end_date))
521 AND acwi.constraint_rev_id IS NOT NULL
522 AND acwi.type_code in ('ALL','ME','SET')
523 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
524 UNION
525 SELECT acwi.Interface_id
526 FROM amw_cst_waiver_interface acwi
527 WHERE acwi.object_type = 'RESP'
528 AND acwi.constraint_rev_id IS NOT NULL
529 AND acwi.type_code in ('ALL','ME','SET')
530 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
531 AND EXISTS ( SELECT 'Y'
532 FROM amw_cst_waiver_interface acw
533 WHERE acw.object_type = 'RESP'
534 AND acw.pk1 = acwi.pk1
535 AND acw.pk2 = acwi.pk2
536 AND acw.object_type = acwi.object_type
537 AND acw.Interface_id <> acwi.Interface_id
538 AND acw.constraint_rev_id = acwi.constraint_rev_id
539 AND acw.constraint_rev_id IS NOT NULL
540 AND (acw.process_flag IS NULL OR acw.process_flag = 'N')
541 );
542
543
544 BEGIN
545 /*
546 Validations To be handled
547 1. Check if the Constraint Name is Valid.
548 If Not,
549 Set the error_flag = 'Y' and Interface_Status = 'Constraint does
550 not exist.Please enter a valid Constraint' for each waiver record
551 of the constraint.
552
553 2. Check if the Responsibility Type Constraint has any responsibility waivers.
554 If yes,
555 Set the error_flag = 'Y' and Interface_Status = 'Responsibility Type
556 Constraint Cannot have Responsibility Waiver' for each responsibility
557 waiver record of the current constraint. Since there is an error in
558 the current constraint, all waivers of this constraint should not be
559 uploaded. We should set a error messages in the valid wiaver records
560 too for the constraint.
561
562 3. When Object_Type= 'USER',
563 The User_Name Should not be null.
564 The Application_Short_Name Should be null
565 The Responsibility_Name Should be null
566 Set the error_flag = 'Y' and Interface_Status = 'The Application_Short_Name
567 and Responsibility_Name Should be null' for waiver record of the
568 constraint.Since there is an error in the current constraint, all
569 waivers of this constraint should not be uploaded. We should set a
570 error messages in the valid wiaver records too for the constraint.
571
572 4. When Object_type= 'RESP'
573 The Application_short_name should not be null;
574 Responsbility_Name should not be null;
575 The User_Name Should be null;
576 Set the error_flag = 'Y' and Interface_Status = 'The User_Name
577 Should be null' for waiver record of the constraint.
578 Since there is an error in the current constraint, all waivers of
579 this constraint should not be uploaded. We should set a error messages
580 in the valid wiaver records too for the constraint.
581
582 4. Check if the User_Name is valid
583 If Not,
584 Set the error_flag = 'Y' and Interface_Status = 'Invalid User Name.
585 Please enter a valid User Name' for waiver record of the constraint.
586 Since there is an error in the current constraint, all waivers of
587 this constraint should not be uploaded. We should set a error messages
588 in the valid wiaver records too for the constraint.
589
590 5. Check if the Application_Short_Name and Responsibility Name is valid
591 If Not,
592 Set the error_flag = 'Y' and Interface_Status = 'Invalid User Name.
593 Please enter a valid User Name' for waiver record of the constraint.
594 Since there is an error in the current constraint, all waivers of
595 this constraint should not be uploaded. We should set a error messages
596 in the valid wiaver records too for the constraint.
597
598
599 6. Check if the Start_Date and End_Date is less than sysdate
600 If yes ,
601 Set the error_flag = 'Y' and Interface_Status = 'Start_date/End_Date
602 Cannot be less than sysdate' for waiver record of the constraint.
603 Since there is an error in the current constraint, all waivers of
604 this constraint should not be uploaded. We should set a error messages
605 in the valid wiaver records too for the constraint.
606
607 7. Check if duplicate User/Responsibility waiver exist.
608 If Yes,
609 Set the error_flag = 'Y' and Interface_Status = 'Duplicate
610 Responsibility/user waiver' for the waiver record of the constraint.
611 We should take into account the exsisting user waivers.
612 Since there is an error in the current constraint, all waivers of
613 this constraint should not be uploaded. We should set a error messages
614 in the valid wiaver records too for the constraint.
615 */
616
617 IF p_batch_id IS NOT NULL THEN
618
619 -- If Last_update_date is null , then set it to system date
620 UPDATE amw_cst_waiver_interface
621 SET last_update_date = SYSDATE
622 WHERE batch_id = p_batch_id
623 AND last_update_date IS NULL
624 AND (process_flag IS NULL OR process_flag = 'N');
625
626 -- If creation_date is null , then set it to system date
627 UPDATE amw_cst_waiver_interface
628 SET creation_date = SYSDATE
629 WHERE batch_id = p_batch_id
630 AND creation_date IS NULL
631 AND (process_flag IS NULL OR process_flag = 'N');
632
633 -- If last_updated_by is null , then set it to logged in user id
634 UPDATE amw_cst_waiver_interface
635 SET last_updated_by = g_user_id
636 WHERE batch_id = p_batch_id
637 AND last_updated_by IS NULL
638 AND (process_flag IS NULL OR process_flag = 'N');
639
640 -- If created_by is null , then set it to logged in user id
641 UPDATE amw_cst_waiver_interface
642 SET created_by = g_user_id
643 WHERE batch_id = p_batch_id
644 AND created_by IS NULL
645 AND (process_flag IS NULL OR process_flag = 'N');
646
647 -- If last_update_login is null , then set it to logged in user id
648 UPDATE amw_cst_waiver_interface
649 SET last_update_login = g_user_id
650 WHERE batch_id = p_batch_id
651 AND last_update_login IS NULL
652 AND (process_flag IS NULL OR process_flag = 'N');
653
654 /*
655 Set the Constraint_Rev_Id from the Constraint Name.
656
657 If the constraint name is not valid, then the Constraint_Rev_Id will
658 be set to NULL. If the Constraint_Rev_id is Null, then it means that the
659 Constraint Name in the interface table is invalid.
660
661 Populating of the Constraint_Rev_Id will avoid the joining of interface table
662 with Amw_Constraint_Vl to get the constraint_rev_id from constraint name
663 */
664 UPDATE amw_cst_waiver_interface acwi
665 SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
666 FROM amw_constraints_vl acv
667 WHERE acwi.constraint_name = acv.constraint_name
668 AND acv.start_date IS NOT NULL
669 AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
670 WHERE acwi.batch_id = p_batch_id
671 AND acwi.constraint_name IS NOT NULL
672 AND acwi.constraint_rev_id IS NULL
673 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
674
675 /*
676 Set the TYPE_CODE from the Constraint Revision Id.
677
678 If the Constraint Revision Id is NULL, then the TYPE_CODE will
679 be set to NULL. If the Constraint_Rev_id is Null, then it means that the
680 Constraint Name in the interface table is invalid.
681
682 Populating of the TYPE_CODE will avoid the joining of interface table
683 with Amw_Constraint_Vl to get the TYPE_CODE from constraint name
684 */
685 UPDATE amw_cst_waiver_interface acwi
686 SET acwi.type_code = ( SELECT acv.type_code
687 FROM amw_constraints_vl acv
688 WHERE acwi.constraint_rev_id = acv.constraint_rev_id
689 AND acv.start_date IS NOT NULL
690 AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
691 WHERE acwi.batch_id = p_batch_id
692 AND acwi.constraint_name IS NOT NULL
693 AND acwi.constraint_rev_id IS NOT NULL
694 AND acwi.type_code IS NULL
695 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
696
697
698 /*
699 Set the Pk1 = User_id for user waiver defined in the interfcae table.
700
701 If the User Name is not valid, then the PK1 will be set to NULL. If the
702 PK1 is Null, then it means that the User_Name in the interface table is invalid.
703
704 Populating of the Pk1 will avoid the joining of interface table
705 with FND_USER to get the user_id from User_Name
706 */
707 UPDATE amw_cst_waiver_interface acwi
708 SET acwi.pk1 = ( SELECT user_id
709 FROM fnd_user usr
710 WHERE usr.user_name = acwi.user_name
711 AND usr.start_date IS NOT NULL
712 AND (usr.end_date IS NULL OR usr.end_date>=sysdate))
713 WHERE acwi.batch_id = p_batch_id
714 AND acwi.object_type = 'USER'
715 AND acwi.user_name IS NOT NULL
716 AND acwi.pk1 IS NULL
717 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
718
719
720 /*
721 Set the Pk2 = application_id for responsibility waivers defined in the
722 interfcae table.
723
724 If the Application_Short_Name is not valid, then the PK2 will be set to NULL.
725 If the PK2 is Null, then it means that the Application_Short_name in the
726 interface table is invalid.
727
728 Populating of the Pk2 will avoid the joining of interface table with
729 FND_APPlication to get the application_id from Application_Short_Name
730 */
731 UPDATE amw_cst_waiver_interface acwi
732 SET acwi.pk2 = ( SELECT application_id
733 FROM fnd_application appl
734 WHERE appl.Application_short_name = acwi.application_short_name)
735 WHERE acwi.batch_id = p_batch_id
736 AND acwi.object_type = 'RESP'
737 AND acwi.application_short_name IS NOT NULL
738 AND acwi.pk2 IS NULL
739 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
740
741
742 /*
743 Set the Pk1 = responsibility_id for responsibility waivers defined in the
744 interfcae table.
745
746 If the Responsibility_Name is not valid, then the PK1 will be set to NULL.
747 If the PK1 is Null, then it means that the responsibility_name in the
748 interface table is invalid.
749
750 Populating of the Pk1 will avoid the joining of interface table with
751 Fnd_Responsibility_Vl to get the responsibility_id from responsibility_name
752 */
753 UPDATE amw_cst_waiver_interface acwi
754 SET acwi.pk1 = ( SELECT responsibility_id
755 FROM fnd_responsibility_vl resp
756 WHERE resp.application_id = acwi.pk2
757 AND resp.responsibility_name = acwi.responsibility_name)
758 WHERE acwi.batch_id = p_batch_id
759 AND acwi.object_type = 'RESP'
760 AND acwi.responsibility_name IS NOT NULL
761 AND acwi.pk2 IS NOT NULL
762 AND acwi.pk1 IS NULL
763 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
764
765 /*
766 If the pk1 is null for responsibility waiver, we are setting the pk2 to null
767
768 This is to indicate that a responsibility waiver is invalid when pk1 and
769 pk2 is null
770 */
771 UPDATE amw_cst_waiver_interface acwi
772 SET acwi.pk2 = NULL
773 WHERE acwi.batch_id = p_batch_id
774 AND acwi.object_type = 'RESP'
775 AND acwi.pk1 IS NULL
776 AND pk2 IS NOT NULL
777 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
778
779
780 /*
781 Identify the invalid constraints
782 */
783 FOR invldcst_rec IN c_invld_cst_name_batch
784 LOOP
785 v_error_msg := 'Invalid Constraint Name ';
786 update_waiver_intf_with_error(v_error_msg,invldcst_rec.interface_id);
787 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldcst_rec.interface_id);
788 END LOOP;
789
790 -- ptulasi : 06/01/2007 : Bug 6067714 :
791 -- Update all the invalid start date in amw_cst_waiver_interface
792 FOR invldstdate_rec IN c_invalid_st_date_batch
793 LOOP
794 UPDATE amw_cst_waiver_interface acwi
795 SET acwi.start_date = invldstdate_rec.start_date
796 WHERE acwi.interface_id=invldstdate_rec.interface_id;
797 END LOOP;
798
799 /*
800 Identify the responsibility constraint having responsibility waivers
801 */
802 FOR invldrespcst_rec IN c_invalid_resp_cst_batch
803 LOOP
804 v_error_msg := 'Responsibility Type Constraint cannot have Responsibility waivers';
805 update_waiver_intf_with_error(v_error_msg,invldrespcst_rec.interface_id);
806 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldrespcst_rec.interface_id);
807 END LOOP;
808
809 /*
810 Identify the invalid user waiver
811 */
812 FOR invlduser_rec IN c_invalid_user_waiver_batch
813 LOOP
814 v_error_msg := 'Invalid User Name ';
815 update_waiver_intf_with_error(v_error_msg,invlduser_rec.interface_id);
816 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invlduser_rec.interface_id);
817 END LOOP;
818
819 /*
820 Identify the invalid responsibility waiver
821 */
822 FOR invldresp_rec IN c_invalid_resp_waiver_batch
823 LOOP
824 v_error_msg := 'Invalid Application Short Name/Responsibility Name ';
825 update_waiver_intf_with_error(v_error_msg,invldresp_rec.interface_id);
826 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldresp_rec.interface_id);
827 END LOOP;
828
829 /* Commenting this check as the customer may populate the constraint waiver
830 interface and the run the concurren ptogram on different days
831 Identify the invalid start_date
832 FOR invldstdate_rec IN c_invalid_start_date_batch
833 LOOP
834 v_error_msg := 'The Start Date should be greater than or equal to System Date';
835 update_waiver_intf_with_error(v_error_msg,invldstdate_rec.interface_id);
836 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldstdate_rec.interface_id);
837 END LOOP;
838 */
839
840 /*
841 Identify the invalid end_date
842 */
843 FOR invldenddate_rec IN c_invalid_end_date_batch
844 LOOP
845 v_error_msg := 'The End Date should not be less than System date/Start Date';
846 update_waiver_intf_with_error(v_error_msg,invldenddate_rec.interface_id);
847 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldenddate_rec.interface_id);
848 END LOOP;
849
850 /*
851 Identify the duplicate user waivers
852 */
853 FOR dupuserwaiv_rec IN c_duplicate_user_waiver
854 LOOP
855 v_error_msg := 'The User Wiaver is either defined more than once '
856 ||' in the interface table for the constraint / It is allready defined '
857 ||' in the constraint';
858 update_waiver_intf_with_error(v_error_msg,dupuserwaiv_rec.interface_id);
859 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || dupuserwaiv_rec.interface_id);
860 END LOOP;
861
862 /*
863 Identify the duplicate responsibility waivers
864 */
865 FOR duprespwaiv_rec IN c_duplicate_resp_waiver_batch
866 LOOP
867 v_error_msg := 'The Responsibility Wiaver is either defined more than once '
868 ||' in the interface table for the constraint / It is allready defined '
869 ||' in the constraint ';
870 update_waiver_intf_with_error(v_error_msg,duprespwaiv_rec.interface_id);
871 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || duprespwaiv_rec.interface_id);
872 END LOOP;
873
874 /*
875 Should not upload the constraint waivers for a constraint, if any waiver
876 is invalid.
877 So set the error flag and the status.
878 */
879 UPDATE amw_cst_waiver_interface
880 SET error_flag = 'Y',
881 interface_status = 'Please correct the invalid waiver defined for this Constraint'
882 WHERE error_flag IS NULL
883 AND batch_id = p_batch_id
884 AND (process_flag IS NULL OR process_flag = 'N')
885 AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
886 FROM amw_cst_waiver_interface
887 WHERE error_flag = 'Y'
888 AND batch_id = p_batch_id
889 AND (process_flag IS NULL OR process_flag = 'N') );
890
891 /*
892 Set the constraint waiver id for the valid constraint waivers.
893
894 We do this to avoid iterating over each waiver record to set the
895 constraint waiver id by executing select sequence.nextval.
896
897 This also helps us to insert all the data in one single query.
898 */
899 UPDATE amw_cst_waiver_interface
900 SET constraint_waiver_id = amw_constraint_waiver_s.nextval
901 WHERE error_flag IS NULL
902 AND (process_flag IS NULL OR process_flag = 'N')
903 AND batch_id = p_batch_id;
904
905
906 /*
907 Insert the valid constraint wavers into the amw_constraint_waivers_b
908 */
909 INSERT INTO amw_constraint_waivers_b(
910 last_update_date,
911 last_updated_by,
912 last_update_login,
913 creation_date,
914 created_by,
915 security_group_id,
916 constraint_rev_id,
917 object_type,
918 pk1,
919 pk2,
920 pk3,
921 pk4,
922 pk5,
923 start_date,
924 end_date,
925 constraint_waiver_id,
926 object_version_number
927 )
928 SELECT acwi.last_update_date,
929 acwi.last_updated_by,
930 acwi.last_update_login,
931 acwi.creation_date,
932 acwi.created_by,
933 NULL,
934 acwi.constraint_rev_id,
935 acwi.object_type,
936 acwi.pk1,
937 acwi.pk2,
938 acwi.pk3,
939 acwi.pk4,
940 acwi.pk5,
941 acwi.start_date,
942 acwi.end_date,
943 acwi.constraint_waiver_id,
944 1
945 FROM amw_cst_waiver_interface acwi
946 WHERE acwi.error_flag IS NULL
947 AND acwi.batch_id = p_batch_id
948 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
949
950
951 /*
952 Insert the valid constraint wavers into the amw_constraint_waivers_tl
953 */
954 INSERT INTO amw_constraint_waivers_tl (
955 constraint_waiver_id,
956 justification,
957 language,
958 source_lang,
959 last_update_date,
960 last_updated_by,
961 creation_date,
962 created_by,
963 last_update_login,
964 security_group_id
965 )
966 SELECT acwi.constraint_waiver_id,
967 trim(acwi.justification),
968 l.language_code,
969 userenv('LANG'),
970 acwi.last_update_date,
971 acwi.last_updated_by,
972 acwi.creation_date,
973 acwi.created_by,
974 acwi.last_update_login,
975 NULL
976 FROM fnd_languages l,
977 amw_cst_waiver_interface acwi
978 WHERE l.installed_flag IN ('I', 'B')
979 AND acwi.error_flag IS NULL
980 AND acwi.batch_id = p_batch_id
981 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
982 AND NOT EXISTS ( SELECT NULL
983 FROM amw_constraint_waivers_tl t
984 WHERE t.constraint_waiver_id = acwi.constraint_waiver_id
985 AND t.language = l.language_code);
986
987 IF p_del_after_import = 'Y' THEN
988 -- Delete the uploaded constraint waiver
989 DELETE FROM amw_cst_waiver_interface
990 WHERE batch_id = p_batch_id
991 AND error_flag IS NULL;
992 ELSE
993 -- Set the process_flag for valid constraint waivers
994 UPDATE amw_cst_waiver_interface
995 SET process_flag = 'Y'
996 WHERE batch_id = p_batch_id
997 AND error_flag IS NULL;
998 END IF;
999 ELSE
1000 -- If Last_update_date is null , then set it to system date
1001 UPDATE amw_cst_waiver_interface
1002 SET last_update_date = SYSDATE
1003 WHERE last_update_date IS NULL
1004 AND (process_flag IS NULL OR process_flag = 'N');
1005
1006 -- If creation_date is null , then set it to system date
1007 UPDATE amw_cst_waiver_interface
1008 SET creation_date = SYSDATE
1009 WHERE creation_date IS NULL
1010 AND (process_flag IS NULL OR process_flag = 'N');
1011
1012 -- If last_updated_by is null , then set it to logged in user id
1013 UPDATE amw_cst_waiver_interface
1014 SET last_updated_by = g_user_id
1015 WHERE last_updated_by IS NULL
1016 AND (process_flag IS NULL OR process_flag = 'N');
1017
1018 -- If created_by is null , then set it to logged in user id
1019 UPDATE amw_cst_waiver_interface
1020 SET created_by = g_user_id
1021 WHERE created_by IS NULL
1022 AND (process_flag IS NULL OR process_flag = 'N');
1023
1024 -- If last_update_login is null , then set it to logged in user id
1025 UPDATE amw_cst_waiver_interface
1026 SET last_update_login = g_user_id
1027 WHERE last_update_login IS NULL
1028 AND (process_flag IS NULL OR process_flag = 'N');
1029
1030 /*
1031 Set the Constraint_Rev_Id from the Constraint Name.
1032
1033 If the constraint name is not valid, then the Constraint_Rev_Id will
1034 be set to NULL. If the Constraint_Rev_id is Null, then it means that the
1035 Constraint Name in the interface table is invalid.
1036
1037 Populating of the Constraint_Rev_Id will avoid the joining of interface table
1038 with Amw_Constraint_Vl to get the constraint_rev_id from constraint name
1039 */
1040 UPDATE amw_cst_waiver_interface acwi
1041 SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
1042 FROM amw_constraints_vl acv
1043 WHERE acwi.constraint_name = acv.constraint_name
1044 AND acv.start_date IS NOT NULL
1045 AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
1046 WHERE acwi.constraint_name IS NOT NULL
1047 AND acwi.constraint_rev_id IS NULL
1048 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1049
1050 /*
1051 Set the TYPE_CODE from the Constraint Revision Id.
1052
1053 If the Constraint Revision Id is NULL, then the TYPE_CODE will
1054 be set to NULL. If the Constraint_Rev_id is Null, then it means that the
1055 Constraint Name in the interface table is invalid.
1056
1057 Populating of the TYPE_CODE will avoid the joining of interface table
1058 with Amw_Constraint_Vl to get the TYPE_CODE from constraint name
1059 */
1060 UPDATE amw_cst_waiver_interface acwi
1061 SET acwi.type_code = ( SELECT acv.type_code
1062 FROM amw_constraints_vl acv
1063 WHERE acwi.constraint_rev_id = acv.constraint_rev_id
1064 AND acv.start_date IS NOT NULL
1065 AND (acv.end_date IS NULL OR acv.end_date>=sysdate))
1066 WHERE acwi.constraint_name IS NOT NULL
1067 AND acwi.constraint_rev_id IS NOT NULL
1068 AND acwi.type_code IS NULL
1069 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1070
1071 /*
1072 Set the Pk1 = User_id for user waiver defined in the interfcae table.
1073
1074 If the User Name is not valid, then the PK1 will be set to NULL. If the
1075 PK1 is Null, then it means that the User_Name in the interface table is invalid.
1076
1077 Populating of the Pk1 will avoid the joining of interface table
1078 with FND_USER to get the user_id from User_Name
1079 */
1080 UPDATE amw_cst_waiver_interface acwi
1081 SET acwi.pk1 = ( SELECT user_id
1082 FROM fnd_user usr
1083 WHERE usr.user_name = acwi.user_name
1084 AND usr.start_date IS NOT NULL
1085 AND (usr.end_date IS NULL OR usr.end_date>=sysdate))
1086 WHERE acwi.object_type = 'USER'
1087 AND acwi.user_name IS NOT NULL
1088 AND acwi.pk1 IS NULL
1089 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1090
1091 /*
1092 Set the Pk2 = application_id for responsibility waivers defined in the
1093 interfcae table.
1094
1095 If the Application_Short_Name is not valid, then the PK2 will be set to NULL.
1096 If the PK2 is Null, then it means that the Application_Short_name in the
1097 interface table is invalid.
1098
1099 Populating of the Pk2 will avoid the joining of interface table with
1100 FND_APPlication to get the application_id from Application_Short_Name
1101 */
1102 UPDATE amw_cst_waiver_interface acwi
1103 SET acwi.pk2 = ( SELECT application_id
1104 FROM fnd_application appl
1105 WHERE appl.Application_short_name = acwi.application_short_name)
1106 WHERE acwi.object_type = 'RESP'
1107 AND acwi.application_short_name IS NOT NULL
1108 AND acwi.pk2 IS NULL
1109 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1110
1111
1112 /*
1113 Set the Pk1 = responsibility_id for responsibility waivers defined in the
1114 interfcae table.
1115
1116 If the Responsibility_Name is not valid, then the PK1 will be set to NULL.
1117 If the PK1 is Null, then it means that the responsibility_name in the
1118 interface table is invalid.
1119
1120 Populating of the Pk1 will avoid the joining of interface table with
1121 Fnd_Responsibility_Vl to get the responsibility_id from responsibility_name
1122 */
1123 UPDATE amw_cst_waiver_interface acwi
1124 SET acwi.pk1 = ( SELECT responsibility_id
1125 FROM fnd_responsibility_vl resp
1126 WHERE resp.application_id = acwi.pk2
1127 AND resp.responsibility_name = acwi.responsibility_name)
1128 WHERE acwi.object_type = 'RESP'
1129 AND acwi.responsibility_name IS NOT NULL
1130 AND acwi.pk2 IS NOT NULL
1131 AND acwi.pk1 IS NULL
1132 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1133
1134 /*
1135 If the pk1 is null for responsibility waiver, we are setting the pk2 to null
1136
1137 This is to indicate that a responsibility waiver is invalid when pk1 and
1138 pk2 is null
1139 */
1140 UPDATE amw_cst_waiver_interface acwi
1141 SET acwi.pk2 = NULL
1142 WHERE acwi.object_type = 'RESP'
1143 AND acwi.pk1 IS NULL
1144 AND pk2 IS NOT NULL
1145 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1146
1147
1148 /*
1149 Identify the invalid constraints
1150 */
1151 FOR invldcst_rec IN c_invld_cst_name
1152 LOOP
1153 v_error_msg := 'Invalid Constraint Name ';
1154 update_waiver_intf_with_error(v_error_msg,invldcst_rec.interface_id);
1155 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldcst_rec.interface_id);
1156 END LOOP;
1157
1158 -- ptulasi : 06/01/2007 : Bug 6067714 :
1159 -- Update all the invalid start date in amw_cst_waiver_interface
1160 FOR invldstdate_rec IN c_invalid_st_date
1161 LOOP
1162 UPDATE amw_cst_waiver_interface acwi
1163 SET acwi.start_date = invldstdate_rec.start_date
1164 WHERE acwi.interface_id=invldstdate_rec.interface_id;
1165 END LOOP;
1166
1167 /*
1168 Identify the responsibility constraint having responsibility waivers
1169 */
1170 FOR invldrespcst_rec IN c_invalid_resp_cst
1171 LOOP
1172 v_error_msg := 'Responsibility Type Constraint cannot have Responsibility waivers';
1173 update_waiver_intf_with_error(v_error_msg,invldrespcst_rec.interface_id);
1174 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldrespcst_rec.interface_id);
1175 END LOOP;
1176
1177 /*
1178 Identify the invalid user waiver
1179 */
1180 FOR invlduser_rec IN c_invalid_user_waiver
1181 LOOP
1182 v_error_msg := 'Invalid User Name ';
1183 update_waiver_intf_with_error(v_error_msg,invlduser_rec.interface_id);
1184 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invlduser_rec.interface_id);
1185 END LOOP;
1186
1187 /*
1188 Identify the invalid responsibility waiver
1189 */
1190 FOR invldresp_rec IN c_invalid_resp_waiver
1191 LOOP
1192 v_error_msg := 'Invalid Application Short Name/Responsibility Name ';
1193 update_waiver_intf_with_error(v_error_msg,invldresp_rec.interface_id);
1194 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldresp_rec.interface_id);
1195 END LOOP;
1196
1197 /*
1198 Commenting this check as the customer may populate the constraint waiver
1199 interface and the run the concurren ptogram on different days
1200 Identify the invalid start_date
1201
1202 FOR invldstdate_rec IN c_invalid_start_date
1203 LOOP
1204 v_error_msg := 'The Start Date should be greater than or equal to System Date';
1205 update_waiver_intf_with_error(v_error_msg,invldstdate_rec.interface_id);
1206 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldstdate_rec.interface_id);
1207 END LOOP; */
1208
1209 /*
1210 Identify the invalid end_date
1211 */
1212 FOR invldenddate_rec IN c_invalid_end_date
1213 LOOP
1214 v_error_msg := 'The End Date should not be less than System date/Start Date';
1215 update_waiver_intf_with_error(v_error_msg,invldenddate_rec.interface_id);
1216 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldenddate_rec.interface_id);
1217 END LOOP;
1218
1219 /*
1220 Identify the duplicate user waivers
1221 */
1222 FOR dupuserwaiv_rec IN c_duplicate_user_waiver
1223 LOOP
1224 v_error_msg := 'The User Wiaver is either defined more than once '
1225 ||' in the interface table for the constraint / It is allready defined '
1226 ||' in the constraint';
1227 update_waiver_intf_with_error(v_error_msg,dupuserwaiv_rec.interface_id);
1228 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || dupuserwaiv_rec.interface_id);
1229 END LOOP;
1230
1231 /*
1232 Identify the duplicate responsibility waivers
1233 */
1234 FOR duprespwaiv_rec IN c_duplicate_resp_waiver
1235 LOOP
1236 v_error_msg := 'The Responsibility Wiaver is either defined more than once '
1237 ||' in the interface table for the constraint / It is allready defined '
1238 ||' in the constraint ';
1239 update_waiver_intf_with_error(v_error_msg,duprespwaiv_rec.interface_id);
1240 fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || duprespwaiv_rec.interface_id);
1241 END LOOP;
1242
1243 /*
1244 Should not upload the constraint waivers for a constraint, if any waiver
1245 is invalid.
1246 So set the error flag and the status.
1247 */
1248 UPDATE amw_cst_waiver_interface
1249 SET error_flag = 'Y',
1250 interface_status = 'Please correct the invalid waiver defined for this Constraint'
1251 WHERE error_flag IS NULL
1252 AND (process_flag IS NULL OR process_flag = 'N')
1253 AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
1254 FROM amw_cst_waiver_interface
1255 WHERE error_flag = 'Y');
1256
1257 /*
1258 Set the constraint waiver id for the valid constraint waivers.
1259
1260 We do this to avoid iterating over each waiver record to set the
1261 constraint waiver id by executing select sequence.nextval.
1262
1263 This also helps us to insert all the data in one single query.
1264 */
1265 UPDATE amw_cst_waiver_interface
1266 SET constraint_waiver_id = amw_constraint_waiver_s.nextval
1267 WHERE error_flag IS NULL
1268 AND (process_flag IS NULL OR process_flag = 'N');
1269
1270 /*
1271 Insert the valid constraint wavers into the amw_constraint_waivers_b
1272 */
1273 INSERT INTO amw_constraint_waivers_b(
1274 last_update_date,
1275 last_updated_by,
1276 last_update_login,
1277 creation_date,
1278 created_by,
1279 security_group_id,
1280 constraint_rev_id,
1281 object_type,
1282 pk1,
1283 pk2,
1284 pk3,
1285 pk4,
1286 pk5,
1287 start_date,
1288 end_date,
1289 constraint_waiver_id,
1290 object_version_number
1291 )
1292 SELECT acwi.last_update_date,
1293 acwi.last_updated_by,
1294 acwi.last_update_login,
1295 acwi.creation_date,
1296 acwi.created_by,
1297 NULL,
1298 acwi.constraint_rev_id,
1299 acwi.object_type,
1300 acwi.pk1,
1301 acwi.pk2,
1302 acwi.pk3,
1303 acwi.pk4,
1304 acwi.pk5,
1305 acwi.start_date,
1306 acwi.end_date,
1307 acwi.constraint_waiver_id,
1308 1
1309 FROM amw_cst_waiver_interface acwi
1310 WHERE acwi.error_flag IS NULL
1311 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1312
1313
1314 /*
1315 Insert the valid constraint wavers into the amw_constraint_waivers_tl
1316 */
1317 INSERT INTO amw_constraint_waivers_tl (
1318 constraint_waiver_id,
1319 justification,
1320 language,
1321 source_lang,
1322 last_update_date,
1323 last_updated_by,
1324 creation_date,
1325 created_by,
1326 last_update_login,
1327 security_group_id
1328 )
1329 SELECT acwi.constraint_waiver_id,
1330 trim(acwi.justification),
1331 l.language_code,
1332 userenv('LANG'),
1333 acwi.last_update_date,
1334 acwi.last_updated_by,
1335 acwi.creation_date,
1336 acwi.created_by,
1337 acwi.last_update_login,
1338 NULL
1339 FROM fnd_languages l,
1340 amw_cst_waiver_interface acwi
1341 WHERE l.installed_flag IN ('I', 'B')
1342 AND acwi.error_flag IS NULL
1343 AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
1344 AND NOT EXISTS ( SELECT NULL
1345 FROM amw_constraint_waivers_tl t
1346 WHERE t.constraint_waiver_id = acwi.constraint_waiver_id
1347 AND t.language = l.language_code);
1348
1349 IF p_del_after_import = 'Y' THEN
1350 -- Delete the uploaded constraint waiver
1351 DELETE FROM amw_cst_waiver_interface
1352 WHERE error_flag IS NULL;
1353 ELSE
1354 -- Set the process_flag for valid constraint waivers
1355 UPDATE amw_cst_waiver_interface
1356 SET process_flag = 'Y'
1357 WHERE error_flag IS NULL;
1358 END IF;
1359 END IF;
1360 -- commmit all the changes
1361 COMMIT;
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 ROLLBACK;
1366 v_err_msg := 'Error during package processing '||SUBSTR (SQLERRM, 1, 100);
1367 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1368 END create_constraint_waivers;
1369
1370 -- ===============================================================
1371 -- Procedure name
1372 -- update_waiver_intf_with_error
1373 -- Purpose
1374 -- Updates error flag and interface status of
1375 -- amw_cst_waiver_interface interface table
1376 -- ===============================================================
1377 PROCEDURE update_waiver_intf_with_error (
1378 p_err_msg IN VARCHAR2,
1379 p_interface_id IN NUMBER
1380 )
1381 IS
1382 l_interface_status amw_cst_waiver_interface.interface_status%TYPE;
1383 BEGIN
1384 SELECT interface_status
1385 INTO l_interface_status
1386 FROM amw_cst_waiver_interface
1387 WHERE interface_id = p_interface_id;
1388
1389 IF l_interface_status IS NOT NULL THEN
1390 l_interface_status := l_interface_status || ' ; ';
1391 END IF;
1392
1393 l_interface_status := l_interface_status || p_err_msg || ' ';
1394
1395 UPDATE amw_cst_waiver_interface
1396 SET interface_status = l_interface_status,
1397 error_flag = 'Y'
1398 WHERE interface_id = p_interface_id;
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 v_err_msg := 'Error during package processing ' || ' interface_id: = '
1403 || p_interface_id || SUBSTR (SQLERRM, 1, 100);
1404 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1405
1406 END update_waiver_intf_with_error;
1407
1408 -- ===============================================================
1409 -- Procedure name
1410 -- cst_table_update_report
1411 -- Purpose
1412 -- Report the issues identified during updating of the following
1413 -- columsn the application_id
1414 -- 1. AMW_VIOLAT_USER_ENTRIES.APPLICATION_ID
1415 -- 2. AMW_CONSTRAINT_ENTRIES.APPLICATION_ID
1416 -- 3. AMW_VIOLAT_RESP_ENTRIES.APPLICATION_ID
1417 -- 4. AMW_VIOLAT_USER_ENTRIES.PROGRAM_APPLICATION_ID
1418 -- 5. AMW_CONSTRAINT_WAIVERS_B.PK2
1419 -- Notes
1420 -- this procedure is called in Concurrent Executable
1421 -- ===============================================================
1422 PROCEDURE cst_table_update_report (
1423 ERRBUF OUT NOCOPY VARCHAR2,
1424 RETCODE OUT NOCOPY VARCHAR2
1425 ) is
1426 TYPE G_NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1427 TYPE G_VARCHAR_TABLE IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1428 TYPE G_VARCHAR2_CODE_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1429
1430 l_cst_rev_id_list G_NUMBER_TABLE;
1431 l_function_id_list G_NUMBER_TABLE;
1432 l_resp_id_list G_NUMBER_TABLE;
1433 l_appl_id_list G_NUMBER_TABLE;
1434 l_prg_appl_id_list G_NUMBER_TABLE;
1435 l_cst_name_list G_VARCHAR_TABLE;
1436 l_resp_name_list G_VARCHAR_TABLE;
1437 l_appl_name_list G_VARCHAR_TABLE;
1438 l_cp_name_list G_VARCHAR_TABLE;
1439 l_object_type_list G_VARCHAR2_CODE_TABLE;
1440
1441 CURSOR c_invalid_resp IS
1442 SELECT distinct acv.CONSTRAINT_REV_ID,
1443 acv.CONSTRAINT_NAME,
1444 avue.RESPONSIBILITY_ID
1445 FROM AMW_CONSTRAINTS_VL acv,
1446 AMW_VIOLATIONS av,
1447 AMW_VIOLATION_USERS avu,
1448 AMW_VIOLAT_USER_ENTRIES avue
1449 WHERE acv.START_DATE<= SYSDATE
1450 AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
1451 AND acv.CONSTRAINT_REV_ID=av.CONSTRAINT_REV_ID
1452 AND av.VIOLATION_ID=avu.VIOLATION_ID
1453 AND av.STATUS_CODE <> 'NA'
1454 AND avu.USER_VIOLATION_ID=avue.USER_VIOLATION_ID
1455 AND avue.RESPONSIBILITY_ID IS NOT NULL
1456 AND 1 < ( SELECT COUNT(1)
1457 FROM FND_RESPONSIBILITY resp
1458 WHERE resp.START_DATE <= SYSDATE
1459 AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
1460 AND resp.RESPONSIBILITY_ID=avue.RESPONSIBILITY_ID);
1461
1462
1463 -- Identifies all the Incompatible Responsibility/Concurrent Program
1464 -- having more than 1 Application Id
1465 CURSOR c_cst_entries IS
1466 SELECT acv.CONSTRAINT_REV_ID,
1467 acv.CONSTRAINT_NAME,
1468 ace.OBJECT_TYPE,
1469 ace.FUNCTION_ID
1470 FROM AMW_CONSTRAINTS_VL acv,
1471 AMW_CONSTRAINT_ENTRIES ace
1472 WHERE acv.START_DATE<= SYSDATE
1473 AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
1474 AND acv.CONSTRAINT_REV_ID=ace.CONSTRAINT_REV_ID
1475 AND ace.OBJECT_TYPE = 'RESP'
1476 AND 1 < ( SELECT COUNT(1)
1477 FROM FND_RESPONSIBILITY resp
1478 WHERE resp.START_DATE <= SYSDATE
1479 AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
1480 AND resp.RESPONSIBILITY_ID=ace.FUNCTION_ID)
1481 UNION ALL
1482 SELECT acv.CONSTRAINT_REV_ID,
1483 acv.CONSTRAINT_NAME,
1484 ace.OBJECT_TYPE,
1485 ace.FUNCTION_ID
1486 FROM AMW_CONSTRAINTS_VL acv,
1487 AMW_CONSTRAINT_ENTRIES ace
1488 WHERE acv.START_DATE<= SYSDATE
1489 AND (acv.END_DATE IS NULL OR acv.END_DATE>=SYSDATE)
1490 AND acv.CONSTRAINT_REV_ID=ace.CONSTRAINT_REV_ID
1491 AND ace.OBJECT_TYPE = 'CP'
1492 AND 1 < ( SELECT COUNT(1)
1493 FROM FND_CONCURRENT_PROGRAMS conc
1494 WHERE conc.CONCURRENT_PROGRAM_ID=ace.FUNCTION_ID
1495 AND ENABLED_FLAG='Y');
1496
1497
1498 -- Identifies the Constraint whose User Violation has Concurrent Program
1499 -- having more than 1 Application Id
1500 CURSOR c_valid_amw_vio_user_entries IS
1501 SELECT DISTINCT acv.CONSTRAINT_REV_ID,
1502 acv.CONSTRAINT_NAME,
1503 avue.FUNCTION_ID
1504 FROM AMW_CONSTRAINTS_VL acv,
1505 AMW_VIOLATIONS av,
1506 AMW_VIOLATION_USERS avu,
1507 AMW_VIOLAT_USER_ENTRIES avue
1508 WHERE acv.CONSTRAINT_REV_ID=av.CONSTRAINT_REV_ID
1509 AND av.VIOLATION_ID=avu.VIOLATION_ID
1510 AND avu.USER_VIOLATION_ID=avue.USER_VIOLATION_ID
1511 AND avue.OBJECT_TYPE='CP'
1512 AND av.STATUS_CODE <> 'NA'
1513 AND 1 < ( SELECT COUNT(1)
1514 FROM FND_CONCURRENT_PROGRAMS conc
1515 WHERE conc.CONCURRENT_PROGRAM_ID=avue.FUNCTION_ID
1516 AND ENABLED_FLAG='Y');
1517
1518 -- Identifies the Constraint whose Responsibility Violation has Concurrent Program
1519 -- having more than 1 Application Id
1520 CURSOR c_amw_violation_resp_entries IS
1521 SELECT DISTINCT acv.CONSTRAINT_REV_ID,
1522 acv.CONSTRAINT_NAME,
1523 avre.FUNCTION_ID
1524 FROM AMW_VIOLATIONS av,
1525 AMW_CONSTRAINTS_VL acv,
1526 AMW_VIOLATION_RESP avr,
1527 AMW_VIOLAT_RESP_ENTRIES avre
1528 WHERE av.CONSTRAINT_REV_ID = acv.CONSTRAINT_REV_ID
1529 AND av.VIOLATION_ID = avr.VIOLATION_ID
1530 AND avr.RESP_VIOLATION_ID = avre.RESP_VIOLATION_ID
1531 AND avre.OBJECT_TYPE='CP'
1532 AND av.STATUS_CODE <> 'NA'
1533 AND 1 < ( SELECT COUNT(1)
1534 FROM FND_CONCURRENT_PROGRAMS conc
1535 WHERE conc.CONCURRENT_PROGRAM_ID = avre.FUNCTION_ID
1536 AND ENABLED_FLAG='Y');
1537
1538 -- Identifies the Constraint Responsibility Wiaver
1539 -- having more than 1 Application Id
1540 CURSOR c_amw_cst_waiver IS
1541 SELECT acwb.CONSTRAINT_REV_ID,
1542 acv.CONSTRAINT_NAME,
1543 acwb.PK1
1544 FROM AMW_CONSTRAINT_WAIVERS_B acwb,
1545 AMW_CONSTRAINTs_VL acv
1546 WHERE acwb.CONSTRAINT_REV_ID =acv.CONSTRAINT_REV_ID
1547 AND acwb.OBJECT_TYPE='RESP'
1548 AND 1 < ( SELECT COUNT(1)
1549 FROM FND_RESPONSIBILITY resp
1550 WHERE resp.START_DATE <= SYSDATE
1551 AND (resp.END_DATE >= SYSDATE or resp.END_DATE IS NULL)
1552 AND resp.RESPONSIBILITY_ID=acwb.PK1);
1553
1554 BEGIN
1555 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1556 fnd_file.put_line(fnd_file.LOG,'| |');
1557 fnd_file.put_line(fnd_file.LOG,'| Start Data Fix for AMW_VIOLAT_USER_ENTRIES.APPLICATION_ID |');
1558 fnd_file.put_line(fnd_file.LOG,'| |');
1559 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1560 fnd_file.put_line(fnd_file.LOG,'Please Re-Run the violation check for Constraints');
1561
1562 l_cst_rev_id_list.delete();
1563 l_cst_name_list.delete();
1564 l_resp_id_list.delete();
1565
1566 OPEN c_invalid_resp;
1567 FETCH c_invalid_resp
1568 BULK COLLECT INTO l_cst_rev_id_list,
1569 l_cst_name_list,
1570 l_resp_id_list;
1571 CLOSE c_invalid_resp;
1572
1573 IF ((l_resp_id_list IS NOT NULL) and (l_resp_id_list.FIRST IS NOT NULL)) THEN
1574 FOR i in l_resp_id_list.FIRST .. l_resp_id_list.LAST
1575 LOOP
1576 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------------------------');
1577 fnd_file.put_line(fnd_file.LOG,'Constraint Name : '||l_cst_name_list(i));
1578 fnd_file.put_line(fnd_file.LOG,'Responsibility Id that mapping to more than 1 Application_Id : '||l_resp_id_list(i));
1579
1580 l_appl_id_list.delete();
1581 l_resp_name_list.delete();
1582 l_appl_name_list.delete();
1583
1584 SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
1585 BULK COLLECT INTO l_appl_id_list,
1586 l_appl_name_list,
1587 l_resp_name_list
1588 FROM FND_RESPONSIBILITY_VL RESP,
1589 FND_APPLICATION_VL APPL
1590 WHERE RESP.RESPONSIBILITY_ID = l_resp_id_list(i)
1591 AND RESP.START_DATE <= SYSDATE
1592 AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
1593 AND RESP.APPLICATION_ID=APPL.APPLICATION_ID;
1594 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1595 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1596 LOOP
1597 fnd_file.put_line(fnd_file.LOG,' Responsibility Name : '||l_resp_name_list(k));
1598 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1599 fnd_file.put_line(fnd_file.LOG,' Application_Id : '||l_appl_id_list(k));
1600 END LOOP;
1601 END IF;
1602 END LOOP;
1603 END IF;
1604 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1605 fnd_file.put_line(fnd_file.LOG,'| |');
1606 fnd_file.put_line(fnd_file.LOG,'| End Data Fix for AMW_VIOLAT_USER_ENTRIES.APPLICATION_ID |');
1607 fnd_file.put_line(fnd_file.LOG,'| |');
1608 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1609
1610 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1611 fnd_file.put_line(fnd_file.LOG,'| |');
1612 fnd_file.put_line(fnd_file.LOG,'| Start Data Fix for AMW_CONSTRAINT_ENTRIES.APPLICATION_ID |');
1613 fnd_file.put_line(fnd_file.LOG,'| |');
1614 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1615
1616 -- Data fix for AMW_CONSTRAINT_ENTRIES
1617 -- Cleaning the lists
1618 l_cst_rev_id_list.delete();
1619 l_cst_name_list.delete();
1620 l_function_id_list.delete();
1621 l_object_type_list.delete();
1622
1623 fnd_file.put_line (fnd_file.LOG,'Please Manaully verify the following Constraints ');
1624
1625 OPEN c_cst_entries;
1626 FETCH c_cst_entries
1627 BULK COLLECT INTO l_cst_rev_id_list,
1628 l_cst_name_list,
1629 l_object_type_list,
1630 l_function_id_list;
1631 CLOSE c_cst_entries;
1632
1633 IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1634 FOR i IN l_function_id_list.FIRST .. l_function_id_list.LAST
1635 LOOP
1636 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------------------------');
1637 fnd_file.put_line(fnd_file.LOG,'Constraint Name : '||l_cst_name_list(i));
1638
1639 IF l_object_type_list(i)='RESP' THEN
1640 fnd_file.put_line(fnd_file.LOG,'Responsibility Id that mapping to more than 1 Application_Id : '||l_function_id_list(i));
1641 fnd_file.put_line(fnd_file.LOG,'Possible Responsibilities to be included in the contraint : ');
1642
1643 -- Clear the List
1644 l_appl_id_list.delete();
1645 l_appl_name_list.delete();
1646 l_resp_name_list.delete();
1647
1648 SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
1649 BULK COLLECT INTO l_appl_id_list,
1650 l_appl_name_list,
1651 l_resp_name_list
1652 FROM FND_RESPONSIBILITY_VL RESP,
1653 FND_APPLICATION_VL APPL
1654 WHERE RESP.RESPONSIBILITY_ID = l_function_id_list(i)
1655 AND RESP.START_DATE <= SYSDATE
1656 AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
1657 AND RESP.APPLICATION_ID=APPL.APPLICATION_ID
1658 ORDER BY APPLICATION_ID;
1659
1660 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1661 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1662 LOOP
1663 fnd_file.put_line(fnd_file.LOG,' Responsibility Name : '||l_resp_name_list(k));
1664 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1665 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(k));
1666 END LOOP; -- FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1667 fnd_file.put_line(fnd_file.LOG,'Responsibility automatically migrated for the Constraint : ');
1668 fnd_file.put_line(fnd_file.LOG,' Responsibility Name : '||l_resp_name_list(1));
1669 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(1));
1670 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(1));
1671 END IF; -- IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1672 ELSE
1673 fnd_file.put_line(fnd_file.LOG,'Conc Program Id that mapping to more than 1 Application_Id : '||l_function_id_list(i));
1674 fnd_file.put_line(fnd_file.LOG,'Possible Conc. Programs to be included in the Contraint : ');
1675
1676 l_appl_id_list.delete();
1677 l_cp_name_list.delete();
1678 l_appl_name_list.delete();
1679
1680 SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
1681 BULK COLLECT INTO l_appl_id_list,
1682 l_appl_name_list,
1683 l_cp_name_list
1684 FROM FND_CONCURRENT_PROGRAMS_VL conc,
1685 FND_APPLICATION_VL appl
1686 WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
1687 AND conc.APPLICATION_ID=appl.APPLICATION_ID
1688 AND conc.ENABLED_FLAG='Y'
1689 ORDER BY APPLICATION_ID;
1690
1691 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1692 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1693 LOOP
1694 fnd_file.put_line(fnd_file.LOG,' Concurrent Program Name : '||l_cp_name_list(k));
1695 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1696 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(k));
1697 END LOOP; -- FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1698
1699 fnd_file.put_line(fnd_file.LOG,'Concurrent Program automatically migrated for the Constraint : ');
1700 fnd_file.put_line(fnd_file.LOG,' Concurrent Program Name : '||l_cp_name_list(1));
1701 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(1));
1702 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(1));
1703 END IF; -- IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1704 END IF; -- IF l_object_type_list(i)='RESP' THEN
1705 END LOOP; -- FOR i IN l_function_id_list.FIRST .. l_function_id_list.LAST
1706 END IF; -- IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1707
1708 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1709 fnd_file.put_line(fnd_file.LOG,'| |');
1710 fnd_file.put_line(fnd_file.LOG,'| End Data Fix for AMW_CONSTRAINT_ENTRIES.APPLICATION_ID |');
1711 fnd_file.put_line(fnd_file.LOG,'| |');
1712 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1713
1714 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1715 fnd_file.put_line(fnd_file.LOG,'| |');
1716 fnd_file.put_line(fnd_file.LOG,'|Starting Data Fix for AMW_VIOLAT_USER_ENTRIES.PROGRAM_APPLICATION_ID|');
1717 fnd_file.put_line(fnd_file.LOG,'| |');
1718 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1719
1720 fnd_file.put_line (fnd_file.LOG,'Please Re-Run the violation check for Constraints');
1721
1722 -- Data fix for AMW_VIOLAT_USER_ENTRIES.PROGRAM_APPLICATION_ID
1723 -- Clear the List
1724 l_cst_rev_id_list.delete();
1725 l_cst_name_list.delete();
1726 l_function_id_list.delete();
1727
1728 -- Get all the valid amw_violate_user_entries
1729 OPEN c_valid_amw_vio_user_entries;
1730 FETCH c_valid_amw_vio_user_entries
1731 BULK COLLECT INTO l_cst_rev_id_list,l_cst_name_list,l_function_id_list;
1732 CLOSE c_valid_amw_vio_user_entries;
1733
1734 IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1735 FOR i in l_function_id_list.FIRST .. l_function_id_list.LAST
1736 LOOP
1737 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------------------------');
1738 fnd_file.put_line(fnd_file.LOG,'Constraint Name : '||l_cst_name_list(i));
1739 fnd_file.put_line(fnd_file.LOG,'Conc Program Id that mapping to more than 1 Application_Id : '||l_function_id_list(i));
1740
1741 l_appl_id_list.delete();
1742 l_cp_name_list.delete();
1743 l_appl_name_list.delete();
1744
1745 SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
1746 BULK COLLECT INTO l_appl_id_list,
1747 l_appl_name_list,
1748 l_cp_name_list
1749 FROM FND_CONCURRENT_PROGRAMS_VL conc,
1750 FND_APPLICATION_VL appl
1751 WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
1752 AND conc.APPLICATION_ID=appl.APPLICATION_ID
1753 AND conc.ENABLED_FLAG='Y';
1754
1755 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1756 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1757 LOOP
1758 fnd_file.put_line(fnd_file.LOG,' Concurrent Program Name : '||l_cp_name_list(k));
1759 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1760 fnd_file.put_line(fnd_file.LOG,' Application_Id : '||l_appl_id_list(k));
1761 END LOOP;
1762 END IF; -- IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1763 END LOOP; -- FOR i in l_function_id_list.FIRST .. l_function_id_list.LAST
1764 END IF; -- IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1765
1766 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1767 fnd_file.put_line(fnd_file.LOG,'| |');
1768 fnd_file.put_line(fnd_file.LOG,'| Ending Data Fix for AMW_VIOLAT_USER_ENTRIES.PROGRAM_APPLICATION_ID |');
1769 fnd_file.put_line(fnd_file.LOG,'| |');
1770 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1771
1772 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1773 fnd_file.put_line(fnd_file.LOG,'| |');
1774 fnd_file.put_line(fnd_file.LOG,'| Start Data Fix for AMW_VIOLAT_RESP_ENTRIES.APPLICATION_ID |');
1775 fnd_file.put_line(fnd_file.LOG,'| |');
1776 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1777
1778 -- Clear the list
1779 l_cst_rev_id_list.delete();
1780 l_cst_name_list.delete();
1781 l_function_id_list.delete();
1782
1783 OPEN c_amw_violation_resp_entries;
1784 FETCH c_amw_violation_resp_entries
1785 BULK COLLECT INTO l_cst_rev_id_list,l_cst_name_list,l_function_id_list;
1786 CLOSE c_amw_violation_resp_entries;
1787
1788 IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1789 FOR i in l_function_id_list.FIRST .. l_function_id_list.LAST
1790 LOOP
1791 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------------------------');
1792 fnd_file.put_line(fnd_file.LOG, 'Constraint Name : '||l_cst_name_list(i));
1793 fnd_file.put_line(fnd_file.LOG, 'Conc Program Id that mapping to more than 1 Application_Id : '||l_function_id_list(i));
1794
1795 l_appl_id_list.delete();
1796 l_cp_name_list.delete();
1797 l_appl_name_list.delete();
1798
1799 SELECT appl.APPLICATION_ID,appl.APPLICATION_NAME,conc.USER_CONCURRENT_PROGRAM_NAME
1800 BULK COLLECT INTO l_appl_id_list,
1801 l_appl_name_list,
1802 l_cp_name_list
1803 FROM FND_CONCURRENT_PROGRAMS_VL conc,
1804 FND_APPLICATION_VL appl
1805 WHERE conc.CONCURRENT_PROGRAM_ID=l_function_id_list(i)
1806 AND conc.APPLICATION_ID=appl.APPLICATION_ID
1807 AND conc.ENABLED_FLAG='Y';
1808
1809 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1810 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1811 LOOP
1812 fnd_file.put_line(fnd_file.LOG,' Concurrent Program Name : '||l_cp_name_list(k));
1813 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1814 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(k));
1815 END LOOP; -- FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1816 END IF; -- IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1817 END LOOP; -- end of FOR j in l_function_id_list.FIRST .. l_function_id_list.LAST
1818 END IF; -- end of IF ((l_function_id_list IS NOT NULL) and (l_function_id_list.FIRST IS NOT NULL)) THEN
1819
1820
1821 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1822 fnd_file.put_line(fnd_file.LOG,'| |');
1823 fnd_file.put_line(fnd_file.LOG,'| End Data Fix for AMW_VIOLAT_RESP_ENTRIES.APPLICATION_ID |');
1824 fnd_file.put_line(fnd_file.LOG,'| |');
1825 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1826
1827 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1828 fnd_file.put_line(fnd_file.LOG,'| |');
1829 fnd_file.put_line(fnd_file.LOG,'| Start Data Fix for AMW_CONSTRAINT_WAIVERS_B.PK2 |');
1830 fnd_file.put_line(fnd_file.LOG,'| |');
1831 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1832
1833 fnd_file.put_line(fnd_file.LOG, 'Please Manaully verify the following Constraints Responsibility Waiver .');
1834
1835 -- clean up the list
1836 l_cst_rev_id_list.delete();
1837 l_resp_id_list.delete();
1838 l_cst_name_list.delete();
1839
1840 OPEN c_amw_cst_waiver;
1841 FETCH c_amw_cst_waiver
1842 BULK COLLECT INTO l_cst_rev_id_list,l_cst_name_list,l_resp_id_list;
1843 CLOSE c_amw_cst_waiver;
1844
1845 IF ((l_resp_id_list IS NOT NULL) and (l_resp_id_list.FIRST IS NOT NULL)) THEN
1846 FOR i in l_resp_id_list.FIRST .. l_resp_id_list.LAST
1847 LOOP
1848 fnd_file.put_line(fnd_file.LOG,'----------------------------------------------------------------------');
1849 fnd_file.put_line(fnd_file.LOG,'Constraint Name : '||l_cst_name_list(i));
1850
1851 -- Clear the List.
1852 l_appl_id_list.delete();
1853 l_appl_name_list.delete();
1854 l_resp_name_list.delete();
1855
1856 -- Get all the application Ids associated with the responsibility
1857 -- into the list l_appl_id_list in assending order so that the
1858 -- minimum application id is first in the list.
1859 SELECT APPL.APPLICATION_ID,APPL.APPLICATION_NAME,RESP.RESPONSIBILITY_NAME
1860 BULK COLLECT INTO l_appl_id_list,
1861 l_appl_name_list,
1862 l_resp_name_list
1863 FROM FND_RESPONSIBILITY_VL RESP,
1864 FND_APPLICATION_VL APPL
1865 WHERE RESP.RESPONSIBILITY_ID = l_resp_id_list(i)
1866 AND RESP.START_DATE <= SYSDATE
1867 AND (RESP.END_DATE >= SYSDATE OR RESP.END_DATE IS NULL)
1868 AND RESP.APPLICATION_ID=APPL.APPLICATION_ID
1869 ORDER BY APPLICATION_ID;
1870
1871 IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1872
1873 fnd_file.put_line(fnd_file.LOG,'Responsibility Id that mapping to more than 1 Application_Id : '||l_resp_id_list(i));
1874 fnd_file.put_line(fnd_file.LOG,'Possible Responsibilities Waivers to be included in the contraint :');
1875 FOR k in l_appl_id_list.FIRST .. l_appl_id_list.LAST
1876 LOOP
1877 fnd_file.put_line(fnd_file.LOG,' Responsibility Name : '||l_resp_name_list(k));
1878 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(k));
1879 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(k));
1880 END LOOP;
1881 fnd_file.put_line(fnd_file.LOG,'Responsibility Waiver automatically migrated for the Constraint:');
1882 fnd_file.put_line(fnd_file.LOG,' Responsibility Name : '||l_resp_name_list(1));
1883 fnd_file.put_line(fnd_file.LOG,' Application Name : '||l_appl_name_list(1));
1884 fnd_file.put_line(fnd_file.LOG,' Application Id : '||l_appl_id_list(1));
1885 END IF;-- End of IF ((l_appl_id_list IS NOT NULL) and (l_appl_id_list.FIRST IS NOT NULL)) THEN
1886 END LOOP; -- FOR i in l_resp_id_list.FIRST .. l_resp_id_list.LAST
1887 END IF; -- end of ((l_resp_id_list IS NOT NULL) and (l_resp_id_list.FIRST IS NOT NULL)) THEN
1888
1889 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1890 fnd_file.put_line(fnd_file.LOG,'| |');
1891 fnd_file.put_line(fnd_file.LOG,'| End Data Fix for AMW_CONSTRAINT_WAIVERS_B.PK2 |');
1892 fnd_file.put_line(fnd_file.LOG,'| |');
1893 fnd_file.put_line(fnd_file.LOG,'+====================================================================+');
1894 EXCEPTION
1895 WHEN OTHERS THEN
1896 fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 200));
1897 END;
1898
1899
1900 END AMW_LOAD_SOD_DATA;
[Home] [Help]