[Home] [Help]
PACKAGE BODY: APPS.AMW_LOAD_CONSTRAINT_DATA
Source
1 PACKAGE BODY AMW_LOAD_CONSTRAINT_DATA as
2 /* $Header: amwcstlb.pls 120.9 2007/10/12 11:09:29 ptulasi ship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_LOAD_CONSTRAINT_DATA
7 -- Purpose
8 --
9 -- History
10 -- 10/01/2004 tsho Creates
11 -- 03/25/2004 tsho add validation between diff rows
12 -- ===============================================================
13
14 -- if error is found during the process, set this global value to TRUE
15 v_error_found BOOLEAN DEFAULT FALSE;
16 v_err_msg VARCHAR2 (2000);
17
18 -- function security for import
19 v_import_func CONSTANT VARCHAR2(30) := 'AMW_SOD_IMPORT';
20
21
22 -- ===============================================================
23 -- Procedure name
24 -- insert_constraint_entries
25 -- Purpose
26 -- insert Incompatible Functions/Responsiblities to AMW_CONSTRAINT_ENTRIES
27 -- History
28 -- 09.13.2005 tsho: consider group_code, object_type of constraint entries
29 -- ===============================================================
30 procedure insert_constraint_entries(
31 p_constraint_rev_id IN NUMBER,
32 p_object_id IN NUMBER,
33 p_app_id IN NUMBER,
34 p_user_id IN NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2,
36 p_group_code IN VARCHAR2 := NULL, -- 09.13.2005 tsho added
37 p_object_type IN VARCHAR2 := NULL -- 09.13.2005 tsho added
38 )
39 IS
40 L_API_NAME CONSTANT VARCHAR2(30) := 'insert_constraint_entries';
41 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
42
43 -- Added by QLIU. Temporary fix for Application_ID
44 l_application_id NUMBER := null;
45
46 CURSOR c_cp_appl_id (c_conc_program_id NUMBER) IS
47 select application_id from fnd_concurrent_programs
48 where concurrent_program_id = c_conc_program_id;
49
50 CURSOR c_resp_appl_id (c_resp_id NUMBER) IS
51 select application_id from fnd_responsibility
52 where responsibility_id = c_resp_id;
53 BEGIN
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 --Added by DLIAO. fix for duplicate function/cp/resp. issue.
57 l_application_id := p_app_id;
58
59 -- Added by QLIU. Temporary fix for Application_ID
60 -- Modified by DLIAO. fix for duplicate function/cp/resp. issue.
61 IF ( p_object_type = 'CP' AND l_application_id IS NULL ) THEN
62 OPEN c_cp_appl_id (p_object_id);
63 FETCH c_cp_appl_id INTO l_application_id;
64 CLOSE c_cp_appl_id;
65 END IF;
66
67 --Modified by DLIAO. fix for duplicate function/cp/resp. issue.
68 IF ( p_object_type = 'RESP' AND l_application_id IS NULL ) THEN
69 OPEN c_resp_appl_id (p_object_id);
70 FETCH c_resp_appl_id INTO l_application_id;
71 CLOSE c_resp_appl_id;
72 END IF;
73
74 insert into AMW_CONSTRAINT_ENTRIES (
75 CONSTRAINT_REV_ID,
76 FUNCTION_ID,
77 LAST_UPDATED_BY,
78 LAST_UPDATE_DATE,
79 CREATED_BY,
80 CREATION_DATE,
81 LAST_UPDATE_LOGIN,
82 SECURITY_GROUP_ID,
83 GROUP_CODE, -- 09.13.2005 tsho added
84 OBJECT_TYPE, -- 09.13.2005 tsho added
85 APPLICATION_ID -- 04.21.2006 qliu added
86 ) values (
87 p_constraint_rev_id,
88 p_object_id,
89 p_user_id,
90 sysdate,
91 p_user_id,
92 sysdate,
93 p_user_id,
94 null,
95 p_group_code, -- 09.13.2005 tsho added
96 p_object_type, -- 09.13.2005 tsho added
97 l_application_id -- 04.21.2006 qliu added
98 );
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
103 v_err_msg :='Error working in procedure: '
104 || L_API_NAME
105 || ' '
106 || SUBSTR (SQLERRM, 1, 100);
107 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
108 END insert_constraint_entries;
109
110
111
112
113 -- ===============================================================
114 -- Procedure name
115 -- delete_constraint_entries
116 -- Purpose
117 -- delete Incompatible Functions/Responsiblities from AMW_CONSTRAINT_ENTRIES
118 -- for specified constraint_rev_id
119 -- ===============================================================
120 procedure delete_constraint_entries(
121 p_constraint_rev_id IN NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2
123 )
124 IS
125 L_API_NAME CONSTANT VARCHAR2(30) := 'delete_constraint_entries';
126 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
127 BEGIN
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 delete from amw_constraint_entries
131 where constraint_rev_id = p_constraint_rev_id;
132
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136
137 EXCEPTION
138 WHEN no_data_found THEN
139 fnd_file.put_line (fnd_file.LOG, 'No data found for AMW_LOAD_CONSTRAINT_DATA.delete_constraint_entries: constraint_rev_id = '||p_constraint_rev_id);
140
141 WHEN OTHERS THEN
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 v_err_msg :='Error working in procedure: '
144 || L_API_NAME
145 || ' '
146 || SUBSTR (SQLERRM, 1, 100);
147 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
148 END delete_constraint_entries;
149
150
151
152 -- ===============================================================
153 -- Procedure name
154 -- delete_constraint_waivers
155 -- Purpose
156 -- delete Waivers from AMW_CONSTRAINT_WAIVERS
157 -- for specified constraint_rev_id
158 -- ===============================================================
159 procedure delete_constraint_waivers(
160 p_constraint_rev_id IN NUMBER,
161 x_return_status OUT NOCOPY VARCHAR2
162 )
163 IS
164 L_API_NAME CONSTANT VARCHAR2(30) := 'delete_constraint_waivers';
165 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
166 BEGIN
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 delete from amw_constraint_waivers
170 where constraint_rev_id = p_constraint_rev_id;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 EXCEPTION
177 WHEN no_data_found THEN
178 fnd_file.put_line (fnd_file.LOG, 'No data found for AMW_LOAD_CONSTRAINT_DATA.delete_constraint_waivers: constraint_rev_id = '||p_constraint_rev_id);
179
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 v_err_msg :='Error working in procedure: '
183 || L_API_NAME
184 || ' '
185 || SUBSTR (SQLERRM, 1, 100);
186 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
187 END delete_constraint_waivers;
188
189
190 -- ===============================================================
191 -- Private Procedure name
192 -- update_violations
193 -- Purpose
194 -- update violations' status in AMW_VIOLATIONS
195 -- for specified constraint_rev_id
196 -- Params
197 -- p_constraint_rev_id := specified constraint_rev_id
198 -- p_violation_status := new violation status
199 -- Notes
200 -- this is to update the status_code in AMW_VIOLATIONS
201 -- if p_violation_status = null, then use default 'NA' (Not Applicable)
202 -- ===============================================================
203 PROCEDURE update_violations (
204 x_return_status OUT NOCOPY VARCHAR2,
205 p_constraint_rev_id IN NUMBER,
206 p_violation_status IN VARCHAR2
207 )
208 IS
209
210 L_API_NAME CONSTANT VARCHAR2(30) := 'update_violations';
211 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
212
213 -- store the violation status (status_code), default is 'NA' (Not Applicable)
214 l_violation_status VARCHAR2(30) := 'NA';
215
216 BEGIN
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 -- decide violation status, if no passed-in p_violation_status, use default
220 IF (p_violation_status is not NULL) THEN
221 l_violation_status := p_violation_status;
222 END IF;
223
224 UPDATE amw_violations
225 SET status_code = l_violation_status
226 WHERE constraint_rev_id = p_constraint_rev_id;
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231
232 EXCEPTION
233 WHEN no_data_found THEN
234 fnd_file.put_line (fnd_file.LOG, 'No data found for AMW_LOAD_CONSTRAINT_DATA.update_violations: constraint_rev_id = '||p_constraint_rev_id);
235
236 WHEN OTHERS THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 v_err_msg :='Error working in procedure: '
239 || L_API_NAME
240 || ' '
241 || SUBSTR (SQLERRM, 1, 100);
242 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
243
244 END update_violations;
245
246
247
248 -- ===============================================================
249 -- Function name
250 -- Has_Import_Privilege
251 -- Purpose
252 -- check the user access privilege see if s/he can import data
253 -- ===============================================================
254 FUNCTION Has_Import_Privilege
255 RETURN Boolean
256 IS
257 L_API_NAME CONSTANT VARCHAR2(30) := 'Has_Import_Privilege';
258 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
259
260 CURSOR c_func_exists IS
261 SELECT 'Y'
262 FROM fnd_responsibility r, fnd_compiled_menu_functions m, fnd_form_functions f
263 WHERE r.responsibility_id = fnd_global.resp_id
264 AND r.application_id=fnd_global.resp_appl_id
265 AND r.menu_id = m.menu_id
266 AND m.function_id = f.function_id
267 AND f.function_name = v_import_func;
268
269 CURSOR c_func_excluded IS
270 SELECT 'Y'
271 FROM fnd_resp_functions rf, fnd_form_functions f
272 WHERE rf.application_id = fnd_global.resp_appl_id
273 AND rf.responsibility_id = fnd_global.resp_appl_id
274 AND rf.rule_type = 'F'
275 AND rf.action_id = f.function_id
276 AND f.function_name = v_import_func;
277
278 l_func_exists VARCHAR2(1);
279 l_func_excluded VARCHAR2(1);
280
281 BEGIN
282 OPEN c_func_exists;
283 FETCH c_func_exists INTO l_func_exists;
284 IF c_func_exists%NOTFOUND THEN
285 CLOSE c_func_exists;
286 return FALSE;
287 END IF;
288 CLOSE c_func_exists;
289
290 OPEN c_func_excluded;
291 FETCH c_func_excluded INTO l_func_excluded;
292 CLOSE c_func_excluded;
293
294 IF l_func_excluded is not null THEN
295 return FALSE;
296 END IF;
297
298 return TRUE;
299 END Has_Import_Privilege;
300
301
302 -- ===============================================================
303 -- Function name
304 -- Get_Party_Id
305 -- Purpose
306 -- get the party_id by specified user_id
307 -- Params
308 -- p_user_id := specified user_id
309 -- ===============================================================
310 Function Get_Party_Id (
311 p_user_id IN NUMBER
312 )
313 Return NUMBER
314 IS
315
316 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Party_Id';
317 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
318
319 l_party_id NUMBER := NULL;
320
321 -- find all employees having corresponding user_id in g_amw_user
322 -- 12.12.2003 tsho: use static sql for AMW for the time being
323 -- 04.30.2004 tsho: enable dynamic sql in AMW.C with 5.10
324 TYPE userCurTyp IS REF CURSOR;
325 c_user_dynamic_sql userCurTyp;
326 l_user_dynamic_sql VARCHAR2(200) :=
327 'SELECT person_party_id '
328 ||' FROM '||G_AMW_USER ||' u '
329 ||' WHERE u.user_id = :1 ';
330
331 BEGIN
332 --FND_FILE.put_line(fnd_file.log,'inside api '||L_API_NAME);
333 -- 12.12.2003 tsho: use static sql for AMW for the time being
334 -- 04.30.2004 tsho: enable dynamic sql in AMW.C with 5.10
335 OPEN c_user_dynamic_sql FOR l_user_dynamic_sql USING
336 p_user_id;
337 FETCH c_user_dynamic_sql INTO l_party_id;
338 CLOSE c_user_dynamic_sql;
339 /*
340 SELECT person_party_id
341 INTO l_party_id
342 FROM FND_USER u
343 WHERE u.user_id = p_user_id;
344 */
345
346 RETURN l_party_id;
347
348 END Get_Party_Id;
349
350
351 -- ===============================================================
352 -- Procedure name
353 -- update_interface_with_error
354 -- Purpose
355 -- update interface table with error mesg
356 -- ===============================================================
357 PROCEDURE update_interface_with_error (
358 p_err_msg IN VARCHAR2,
359 p_table_name IN VARCHAR2,
360 p_interface_id IN NUMBER
361 )
362 IS
363 L_API_NAME CONSTANT VARCHAR2(30) := 'update_interface_with_error';
364 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
365 l_interface_status amw_ap_interface.interface_status%TYPE;
366
367 BEGIN
368 ROLLBACK; -- rollback any inserts done during the current loop process
369
370 -- 08:23:2006 psomanat : Fix for Constraint mass upload issue
371 -- we should allways insert then valid constraint for performance
372 -- So Commenting the v_error_found
373 -- v_error_found := TRUE;
374
375 BEGIN
376 SELECT interface_status
377 INTO l_interface_status
378 FROM amw_constraint_interface
379 WHERE cst_interface_id = p_interface_id;
380 EXCEPTION
381 WHEN OTHERS THEN
382 v_err_msg :=
383 'interface_id: = '
384 || p_interface_id
385 || ' '
386 || SUBSTR (SQLERRM, 1, 100);
387 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
388 END;
389
390 BEGIN
391 UPDATE amw_constraint_interface
392 SET interface_status =
393 l_interface_status
394 || p_err_msg
395 || '**'
396 ,error_flag = 'Y'
397 WHERE cst_interface_id = p_interface_id;
398 fnd_file.put_line (fnd_file.LOG, SUBSTR (l_interface_status, 1, 200));
399 COMMIT;
400 EXCEPTION
401 WHEN OTHERS THEN
402 v_err_msg :=
403 'Error during package processing '
404 || ' interface_id: = '
405 || p_interface_id
406 || SUBSTR (SQLERRM, 1, 100);
407 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
408 END;
409
410 COMMIT;
411 END update_interface_with_error;
412
413
414 -- =============================================================================
415 -- Procedure name
416 -- insert_constraint
417 -- Purpose
418 -- insert new constraint into AMW_CONSTRAINTS_B/_TL
419 -- History
420 -- 09.13.2005 tsho: consider classification, objective of constraint
421 -- 19.04.2006 PSOMANAT: Default Objective_code = 'DT'
425 x_return_status OUT NOCOPY VARCHAR2,
422 -- Default Classification to the seeded Constraint Classification
423 -- =============================================================================
424 PROCEDURE insert_constraint(
426 x_CONSTRAINT_REV_ID OUT NOCOPY NUMBER,
427 p_START_DATE IN DATE,
428 p_END_DATE IN DATE,
429 p_ENTERED_BY_ID IN NUMBER,
430 p_TYPE_CODE IN VARCHAR2,
431 p_RISK_ID IN NUMBER,
432 p_APPROVAL_STATUS IN VARCHAR2,
433 p_CONSTRAINT_NAME IN VARCHAR2,
434 p_CONSTRAINT_DESCRIPTION IN VARCHAR2,
435 p_user_id IN NUMBER,
436 p_classification IN VARCHAR2 := NULL, -- 09.13.2005 tsho added
437 p_objective_code IN VARCHAR2 := 'DT' -- 09.13.2005 tsho added
438 )
439 IS
440 L_API_NAME CONSTANT VARCHAR2(30) := 'insert_constraint';
441 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
442
443 l_row_id AMW_CONSTRAINTS_VL.row_id%type;
444 l_constraint_rev_id NUMBER;
445 l_classification VARCHAR2(30):=p_classification;
446
447 -- get constraint_rev_id from AMW_CONSTRAINT_REV_S
448 CURSOR c_constraint_rev_id IS
449 SELECT AMW_CONSTRAINT_REV_S.NEXTVAL
450 FROM dual;
451
452 BEGIN
453 -- get constraint_rev_id from AMW_CONSTRAINT_REV_S
454 OPEN c_constraint_rev_id;
455 FETCH c_constraint_rev_id INTO l_constraint_rev_id;
456 CLOSE c_constraint_rev_id;
457
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459 x_CONSTRAINT_REV_ID := NULL;
460 IF l_classification IS NULL THEN
461 BEGIN
462 SELECT work_type_id
463 INTO l_classification
464 FROM amw_work_types_b
465 WHERE work_type_code = 'AMW_UNDEF'
466 AND object_id = (SELECT object_id
467 FROM fnd_objects
468 WHERE obj_name = 'AMW_CONSTRAINT');
469 EXCEPTION
470 WHEN no_data_found THEN
471 l_classification := NULL;
472 WHEN too_many_rows THEN
473 l_classification := NULL;
474 END;
475 END IF;
476
477 AMW_CONSTRAINTS_PKG.INSERT_ROW(
478 X_ROWID => l_row_id,
479 X_CONSTRAINT_ID => l_constraint_rev_id,
480 X_CONSTRAINT_REV_ID => l_constraint_rev_id,
481 X_START_DATE => p_start_date,
482 X_END_DATE => p_end_date,
483 X_ENTERED_BY_ID => p_entered_by_id,
484 X_TYPE_CODE => p_type_code,
485 X_RISK_ID => p_risk_id,
486 X_LAST_UPDATED_BY => p_user_id,
487 X_LAST_UPDATE_DATE => sysdate,
488 X_CREATED_BY => p_user_id,
489 X_CREATION_DATE => sysdate,
490 X_LAST_UPDATE_LOGIN => p_user_id,
491 X_SECURITY_GROUP_ID => NULL,
492 X_OBJECT_VERSION_NUMBER => 1,
493 X_ATTRIBUTE_CATEGORY => NULL,
497 X_ATTRIBUTE4 => NULL,
494 X_ATTRIBUTE1 => NULL,
495 X_ATTRIBUTE2 => NULL,
496 X_ATTRIBUTE3 => NULL,
498 X_ATTRIBUTE5 => NULL,
499 X_ATTRIBUTE6 => NULL,
500 X_ATTRIBUTE7 => NULL,
501 X_ATTRIBUTE8 => NULL,
502 X_ATTRIBUTE9 => NULL,
503 X_ATTRIBUTE10 => NULL,
504 X_ATTRIBUTE11 => NULL,
505 X_ATTRIBUTE12 => NULL,
506 X_ATTRIBUTE13 => NULL,
507 X_ATTRIBUTE14 => NULL,
508 X_ATTRIBUTE15 => NULL,
509 X_CONSTRAINT_NAME => p_constraint_name,
510 X_CONSTRAINT_DESCRIPTION => p_constraint_description,
511 X_APPROVAL_STATUS => p_approval_status,
512 X_CLASSIFICATION => l_classification, -- 09.13.2005 tsho added
513 X_OBJECTIVE_CODE => p_objective_code -- 09.13.2005 tsho added
514 );
515
516 x_CONSTRAINT_REV_ID := l_constraint_rev_id;
517 --fnd_file.put_line(fnd_file.LOG,'Done with AMW_CONSTRAINTS_PKG.INSERT_ROW');
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 v_err_msg :='Error working in procedure: '
523 || L_API_NAME
524 || ' '
525 || SUBSTR (SQLERRM, 1, 100);
526 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
527 END insert_constraint;
528
529
530
531 -- ===============================================================
532 -- Procedure name
533 -- update_constraint
534 -- Purpose
535 -- update existing constraint in AMW_CONSTRAINTS_B/_TL
536 -- History
537 -- 09.13.2005 tsho: consider classification, objective of constraint
538 -- ===============================================================
539 PROCEDURE update_constraint(
540 x_return_status OUT NOCOPY VARCHAR2,
541 p_CONSTRAINT_REV_ID IN NUMBER,
542 p_START_DATE IN DATE,
543 p_END_DATE IN DATE,
544 p_ENTERED_BY_ID IN NUMBER,
545 p_TYPE_CODE IN VARCHAR2,
546 p_RISK_ID IN NUMBER,
547 p_APPROVAL_STATUS IN VARCHAR2,
548 p_CONSTRAINT_NAME IN VARCHAR2,
549 p_CONSTRAINT_DESCRIPTION IN VARCHAR2,
550 p_user_id IN NUMBER,
551 p_classification IN VARCHAR2 := NULL, -- 09.13.2005 tsho added
552 p_objective_code IN VARCHAR2 := NULL -- 09.13.2005 tsho added
553 )
554 IS
555 L_API_NAME CONSTANT VARCHAR2(30) := 'update_constraint';
556 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
557
558 l_constraint_rev_id NUMBER;
559 l_classification VARCHAR2(30):=p_classification;
560 l_objective_code VARCHAR2(30):=p_objective_code;
561 -- get constraint_rev_id from AMW_CONSTRAINT_REV_S
562 CURSOR c_constraint_rev_id IS
563 SELECT AMW_CONSTRAINT_REV_S.NEXTVAL
564 FROM dual;
565
566 CURSOR c_classification(c_cst_rev_id number) IS
567 SELECT classification
568 FROM amw_constraints_b
569 WHERE constraint_rev_id=c_cst_rev_id;
570
571 CURSOR c_objective_code(c_cst_rev_id number) IS
572 SELECT objective_code
573 FROM amw_constraints_b
574 WHERE constraint_rev_id=c_cst_rev_id;
575 BEGIN
576 -- get constraint_rev_id from AMW_CONSTRAINT_REV_S
577 OPEN c_constraint_rev_id;
578 FETCH c_constraint_rev_id INTO l_constraint_rev_id;
579 CLOSE c_constraint_rev_id;
580
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582 IF l_classification IS NULL THEN
583 OPEN c_classification(p_CONSTRAINT_REV_ID);
584 FETCH c_classification INTO l_classification;
585 CLOSE c_classification;
586 END IF;
587
588 IF l_objective_code IS NULL THEN
589 OPEN c_objective_code(p_CONSTRAINT_REV_ID);
590 FETCH c_objective_code INTO l_objective_code;
591 CLOSE c_objective_code;
592 END IF;
593
594 AMW_CONSTRAINTS_PKG.UPDATE_ROW(
595 X_CONSTRAINT_ID => p_constraint_rev_id,
596 X_CONSTRAINT_REV_ID => p_constraint_rev_id,
597 X_START_DATE => p_start_date,
598 X_END_DATE => p_end_date,
599 X_ENTERED_BY_ID => p_entered_by_id,
600 X_TYPE_CODE => p_type_code,
601 X_RISK_ID => p_risk_id,
602 X_LAST_UPDATED_BY => p_user_id,
603 X_LAST_UPDATE_DATE => sysdate,
604 X_LAST_UPDATE_LOGIN => p_user_id,
605 X_SECURITY_GROUP_ID => NULL,
606 X_OBJECT_VERSION_NUMBER => 1,
607 X_ATTRIBUTE_CATEGORY => NULL,
608 X_ATTRIBUTE1 => NULL,
609 X_ATTRIBUTE2 => NULL,
610 X_ATTRIBUTE3 => NULL,
611 X_ATTRIBUTE4 => NULL,
612 X_ATTRIBUTE5 => NULL,
613 X_ATTRIBUTE6 => NULL,
614 X_ATTRIBUTE7 => NULL,
615 X_ATTRIBUTE8 => NULL,
616 X_ATTRIBUTE9 => NULL,
617 X_ATTRIBUTE10 => NULL,
621 X_ATTRIBUTE14 => NULL,
618 X_ATTRIBUTE11 => NULL,
619 X_ATTRIBUTE12 => NULL,
620 X_ATTRIBUTE13 => NULL,
622 X_ATTRIBUTE15 => NULL,
623 X_CONSTRAINT_NAME => p_constraint_name,
624 X_CONSTRAINT_DESCRIPTION => p_constraint_description,
625 X_APPROVAL_STATUS => p_approval_status,
626 X_CLASSIFICATION => l_classification, -- 09.13.2005 tsho added
627 X_OBJECTIVE_CODE => l_objective_code -- 09.13.2005 tsho added
628 );
629
630 --fnd_file.put_line(fnd_file.LOG,'Done with AMW_CONSTRAINTS_PKG.UPDATE_ROW');
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 v_err_msg :='Error working in procedure: '
636 || L_API_NAME
637 || ' '
638 || SUBSTR (SQLERRM, 1, 100);
639 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
640 END update_constraint;
641
642
643 -- ===============================================================
644 -- Procedure name
645 -- create_constraints
646 -- Purpose
647 -- import constraints
648 -- from interface table to AMW_CONSTRAINTS_B and AMW_CONSTRAINTS_TL
649 -- Notes
650 -- this procedure is called in Concurrent Executable
651 -- History
652 -- 09.13.2005 tsho: consider group_code, object_type of constraint entries
653 -- ===============================================================
654 PROCEDURE create_constraints (
655 ERRBUF OUT NOCOPY VARCHAR2,
656 RETCODE OUT NOCOPY VARCHAR2,
657 p_batch_id IN NUMBER,
658 p_user_id IN NUMBER
659 )
660 IS
661 L_API_NAME CONSTANT VARCHAR2(30) := 'create_constraints';
662 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
663
664 -- 08:23:2006 psomanat : Fix for Constraint mass upload issue
665 -- Commenting the check on p_user_id as the interface table records may be
666 -- created by different user and the mass upload concurrent program may be
667 -- run by a different user.This should effect the webadi
668 -- get distinct constraints from interface table
669 CURSOR constraints_cur IS
670 SELECT intf.cst_name,
671 intf.cst_description,
672 intf.cst_approval_status_code,
673 intf.cst_start_date,
674 intf.cst_end_date,
675 intf.risk_name,
676 intf.entered_by_id,
677 intf.cst_type_code,
678 intf.cst_interface_id,
679 intf.cst_entries_function_id,
680 intf.cst_entries_resp_id,
681 intf.cst_entries_group_code, -- 09.13.2005 tsho added
682 intf.cst_violat_obj_type -- 09.13.2005 tsho added
683 FROM amw_constraint_interface intf
684 WHERE intf.cst_interface_id = (
685 SELECT min(ci.cst_interface_id)
686 FROM amw_constraint_interface ci
687 WHERE ci.cst_name = intf.cst_name
688 --AND created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
689 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
690 AND process_flag IS NULL
691 AND error_flag IS NULL
692 );
693
694 -- check if the constraint already existed
695 CURSOR c_constraint_exists (l_cst_name IN VARCHAR2) IS
696 SELECT b.constraint_id, b.constraint_rev_id, b.approval_status
697 FROM amw_constraints_b b, amw_constraints_tl tl
698 WHERE tl.name = l_cst_name
699 AND tl.language = USERENV('LANG')
700 AND tl.constraint_id = b.constraint_id;
701
702 -- 08:23:2006 psomanat : Fix for Constraint mass upload issue
703 -- Commenting the check on p_user_id as the interface table records may be
704 -- created by different user and the mass upload concurrent program may be
705 -- run by a different user.This should effect the webadi
706 -- get constraint entries for specific constriant
707 CURSOR c_constraint_entries (l_cst_name IN VARCHAR2) IS
708 SELECT cst_name,
709 cst_interface_id,
710 cst_entries_function_id,
711 cst_entries_resp_id,
712 cst_entries_group_code, -- 09.13.2005 tsho added
713 cst_violat_obj_type, -- 09.13.2005 tsho added
714 cst_entries_appl_id -- 03.23.2007 dliao added
715 FROM amw_constraint_interface
716 WHERE cst_name = l_cst_name
717 --AND created_by = DECODE (p_user_id, NULL, created_by, p_user_id)
718 AND batch_id = DECODE (p_batch_id, NULL, batch_id, p_batch_id)
719 AND process_flag IS NULL
720 AND error_flag IS NULL;
721
722 -- 08:23:2006 psomanat : Fix for Constraint mass upload issue
723 -- Commenting the check on p_user_id as the interface table records may be
724 -- created by different user and the mass upload concurrent program may be
725 -- run by a different user.This should effect the webadi
726 -- 03.25.2005 tsho: bug 4243661, check constraint data between rows
727 CURSOR c_invalid_constraints IS
728 SELECT intf.cst_name,
729 intf.cst_start_date,
730 intf.cst_end_date,
731 intf.cst_type_code,
732 intf.cst_interface_id,
733 intf.cst_entries_function_id,
734 intf.cst_entries_resp_id,
735 intf.cst_entries_group_code, -- 09.13.2005 tsho added
736 intf.cst_violat_obj_type -- 09.13.2005 tsho added
740 intf.batch_id = DECODE (p_batch_id, NULL, intf.batch_id, p_batch_id)
737 FROM amw_constraint_interface intf
738 WHERE
739 --intf.created_by = DECODE (p_user_id, NULL, intf.created_by, p_user_id)
741 AND intf.process_flag IS NULL
742 AND intf.error_flag IS NULL;
743
744 /*02.27.2006 psomanat: added below cursor to raise errors for all invalid rows*/
745
746 -- ptulasi : 10/11/2007
747 -- bug : 6494262 : Modified the below code to upload function constraint of type all
748 -- with out any error.
749 cursor c_check_inv_func is
750 select intf.cst_interface_id, intf.cst_type_code
751 from amw_constraint_interface intf
752 where intf.batch_id=p_batch_id
753 and exists(select ci.cst_name
754 from (select cst_name,count(distinct cst_entries_function_id) as ct_cst_entries_function_id,
755 count(distinct cst_entries_group_code) as ct_cst_entries_group_code
756 from amw_constraint_interface
757 where batch_id=p_batch_id
758 and (cst_type_code='SET' or cst_type_code='ME')
759 group by cst_name) ci
760 where ((cst_type_code='ME' and ci.ct_cst_entries_function_id=1) or
761 (cst_type_code='SET' and (ci.ct_cst_entries_group_code=1 or ci.ct_cst_entries_function_id<2) ))
762 and intf.cst_name=ci.cst_name
763 );
764
765 /*02.27.2006 psomanat: added below cursor to raise errors for all invalid rows*/
766 -- ptulasi : 10/11/2007
767 -- bug : 6494262 : Modified the below code to upload Responsibility constraint of type all
768 -- with out any error.
769 cursor c_check_inv_resp is
770 select intf.cst_interface_id, intf.cst_type_code
771 from amw_constraint_interface intf
772 where intf.batch_id=p_batch_id
773 and exists ( select ci.cst_name
774 from (select cst_name,count(distinct cst_entries_resp_id) as ct_cst_entries_resp_id,
775 count(distinct cst_entries_group_code) as ct_cst_entries_group_code
776 from amw_constraint_interface
777 where batch_id=p_batch_id
778 and (cst_type_code='RESPSET' or cst_type_code='RESPME')
779 group by cst_name) ci
780 where ((cst_type_code='RESPME' and ci.ct_cst_entries_resp_id=1) or
781 (cst_type_code='RESPSET' and (ci.ct_cst_entries_group_code=1 or ci.ct_cst_entries_resp_id<2) ))
782 and intf.cst_name=ci.cst_name
783 );
784
785
786 /*02.27.2006 psomanat: added below cursor to raise errors for duplicate constraint*/
787 cursor c_chk_dup_csts is
788 select intf.cst_interface_id
789 from amw_constraint_interface intf
790 where intf.batch_id=p_batch_id
791 and exists (select ci.cst_name
792 from (select cst_name,count(cst_name) as count_diff_const
793 from (select distinct cst_name,cst_type_code,cst_start_date,cst_end_date
794 from amw_constraint_interface
795 where batch_id=p_batch_id)
796 group by cst_name) ci
797 where ci.count_diff_const>1
798 and intf.cst_name=ci.cst_name);
799
800
801 /*04.21.2006 qliu: added below to raise errors for ambiguous IDs*/
802 cursor c_chk_ambiguous_cp is
803 select intf.cst_interface_id
804 from amw_constraint_interface intf
805 where intf.batch_id=p_batch_id
806 and intf.cst_violat_obj_type='CP'
807 and (select count(1) from fnd_concurrent_programs cp
808 where cp.concurrent_program_id = intf.cst_entries_function_id)>1;
809
810 cursor c_chk_ambiguous_resp is
811 select intf.cst_interface_id
812 from amw_constraint_interface intf
813 where intf.batch_id=p_batch_id
814 and (select count(1) from fnd_responsibility resp
815 where resp.responsibility_id = intf.cst_entries_resp_id
816 and resp.START_DATE <= SYSDATE
817 and (resp.END_DATE >= SYSDATE OR resp.END_DATE IS NULL)
818 )>1;
819 e_invalid_entered_by_id EXCEPTION;
820 e_no_import_access EXCEPTION;
821 E_INCONSIST_CST_UPL EXCEPTION;
822 E_INV_CST_ENTRIES_UPL EXCEPTION;
823 E_AMBIGUOUS_CP_UPL EXCEPTION;
824 E_AMBIGUOUS_RESP_UPL EXCEPTION;
825
826 l_amw_delt_constraint_intf VARCHAR2(2);
827 l_entered_by_id NUMBER;
828 l_constraint_id NUMBER;
829 l_constraint_rev_id NUMBER;
830 l_approval_status VARCHAR2(30);
831 l_interface_id NUMBER;
832 l_process_flag VARCHAR2(1);
833 l_interface_id_count NUMBER;
834
835 x_return_status VARCHAR2(30);
836
837 BEGIN
838 --fnd_file.put_line (fnd_file.LOG, 'resp id: '||fnd_global.RESP_ID);
839 --fnd_file.put_line (fnd_file.LOG, 'resp appl id: '||fnd_global.RESP_APPL_ID);
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841
842 -- use party_id as entered_by_id in AMW_CONSTRAINTS_B
843 l_entered_by_id := Get_Party_Id(p_user_id);
844 IF (l_entered_by_id is NULL) THEN
845 RAISE e_invalid_entered_by_id;
846 END IF;
847
848 -- check access privilege
849 IF not Has_Import_Privilege THEN
850 RAISE e_no_import_access;
851 END IF;
852
856 l_interface_id := NULL;
853 -- get profile info for deleting records from interface table
854 l_amw_delt_constraint_intf := NVL(fnd_profile.VALUE ('AMW_DELT_CST_INTF'), 'N');
855
857 l_interface_id_count := NULL;
858
859 FOR c_inv_func_rec IN c_check_inv_func LOOP
860 begin
861 l_interface_id := c_inv_func_rec.cst_interface_id;
862 IF(l_interface_id IS NOT NULL) THEN
863 RAISE E_INV_CST_ENTRIES_UPL;
864 END IF;
865 exception
866 -- found there is not sufficient constraint entries for the same constraint name
867 WHEN E_INV_CST_ENTRIES_UPL THEN
868 BEGIN
869 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INSUFFICIENT CST ENTRIES UPLOAD FOUND');
870
871 -- ptulasi : 10/11/2007
872 -- bug : 6494262 : Modified the below code to display different messages for set type
873 -- and me type constraint
874 IF c_inv_func_rec.cst_type_code = 'ME' THEN
875 v_err_msg := AMW_UTILITY_PVT.get_message_text('AMW_CONSTRAINT_UPL_NUM_ERROR');
876 ELSE
877 v_err_msg := AMW_UTILITY_PVT.get_message_text('AMW_CST_FUNCSET_NUM_ERROR');
878 END IF;
879 FND_FILE.PUT_LINE(FND_FILE.LOG, 'c_check_inv_func*** '||v_err_msg||' for interface_id: '||L_INTERFACE_ID);
880 update_interface_with_error(
881 p_ERR_MSG => v_err_msg
882 ,p_table_name => 'AMW_CONSTRAINTS_B'
883 ,P_INTERFACE_ID => L_INTERFACE_ID);
884 EXCEPTION
885 WHEN OTHERS THEN
886 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INCONSIST_CST_UPL: '||sqlerrm);
887 END;
888 WHEN NO_DATA_FOUND THEN
889 NULL;
890 end;
891 end loop;
892
893 FOR c_inv_resp_rec IN c_check_inv_resp LOOP
894 begin
895 l_interface_id := c_inv_resp_rec.cst_interface_id;
896 IF(l_interface_id IS NOT NULL) THEN
897 RAISE E_INV_CST_ENTRIES_UPL;
898 END IF;
899 exception
900 -- found there is not sufficient constraint entries for the same constraint name
901 WHEN E_INV_CST_ENTRIES_UPL THEN
902 BEGIN
903 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INSUFFICIENT CST ENTRIES UPLOAD FOUND');
904 -- ptulasi : 10/11/2007
905 -- bug : 6494262 : Modified the below code to display different messages for respset
906 -- type and respme type constraint
907 IF c_inv_resp_rec.cst_type_code = 'RESPME' THEN
908 v_err_msg := AMW_UTILITY_PVT.get_message_text('AMW_CONSTRAINT_UPL_NUM_ERROR');
909 ELSE
910 v_err_msg := AMW_UTILITY_PVT.get_message_text('AMW_CST_RESPSET_NUM_ERROR');
911 END IF;
912 FND_FILE.PUT_LINE(FND_FILE.LOG, 'c_check_inv_resp*** '||v_err_msg||' for interface_id: '||L_INTERFACE_ID);
913 update_interface_with_error(
914 p_ERR_MSG => v_err_msg
915 ,p_table_name => 'AMW_CONSTRAINTS_B'
916 ,P_INTERFACE_ID => L_INTERFACE_ID);
917 EXCEPTION
918 WHEN OTHERS THEN
919 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INCONSIST_CST_UPL: '||sqlerrm);
920 END;
921 WHEN NO_DATA_FOUND THEN
922 NULL;
923 end;
924 end loop;
925
926 -- check if there's any different constraint definition, startDate, endDate between diff rows
927 -- for the same constraint name
928 FOR c_dup_csts_rec IN c_chk_dup_csts LOOP
929 begin
930 l_interface_id := c_dup_csts_rec.cst_interface_id;
931 IF(l_interface_id IS NOT NULL) THEN
932 RAISE E_INCONSIST_CST_UPL;
933 END IF;
934 exception
935 -- found there is not sufficient constraint entries for the same constraint name
936 WHEN E_INCONSIST_CST_UPL THEN
937 BEGIN
938 FND_FILE.PUT_LINE(FND_FILE.LOG, 'INCONSISTENT CST UPLOAD FOUND' );
939 v_err_msg := AMW_UTILITY_PVT.get_message_text('AMW_CONSTRAINT_UPL_INCONSIST');
940 FND_FILE.PUT_LINE(FND_FILE.LOG, 'c_chk_dup_csts*** '||v_err_msg||' for interface_id: '||L_INTERFACE_ID);
941 update_interface_with_error(
942 p_ERR_MSG => v_err_msg
943 ,p_table_name => 'AMW_CONSTRAINTS_B'
944 ,P_INTERFACE_ID => L_INTERFACE_ID);
945 EXCEPTION
946 WHEN OTHERS THEN
947 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INCONSIST_CST_UPL: '||sqlerrm);
948 END;
949 WHEN NO_DATA_FOUND THEN
950 NULL;
951 end;
952 end loop;
953
954 /*04.21.2006 qliu: added below to raise errors for ambiguous CP and Resp IDs*/
955 FOR c_cp_rec IN c_chk_ambiguous_cp LOOP
956 begin
957 l_interface_id := c_cp_rec.cst_interface_id;
958 IF(l_interface_id IS NOT NULL) THEN
959 RAISE E_AMBIGUOUS_CP_UPL;
960 END IF;
961 exception
962 WHEN E_AMBIGUOUS_CP_UPL THEN
963 BEGIN
964 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FOUND NON-UNIQUE CONC. PROGRAM ID' );
965 v_err_msg := 'Concurrent Program ID is not unique. Please create this constraint from Self-Service UI.';
966 FND_FILE.PUT_LINE(FND_FILE.LOG, '*** '||v_err_msg||' for interface_id: '||L_INTERFACE_ID);
967 update_interface_with_error(
971 EXCEPTION
968 p_ERR_MSG => v_err_msg
969 ,p_table_name => 'AMW_CONSTRAINTS_B'
970 ,P_INTERFACE_ID => L_INTERFACE_ID);
972 WHEN OTHERS THEN
973 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INCONSIST_CST_UPL: '||sqlerrm);
974 END;
975 WHEN NO_DATA_FOUND THEN
976 NULL;
977 end;
978 end loop;
979
980 FOR c_resp_rec IN c_chk_ambiguous_resp LOOP
981 begin
982 l_interface_id := c_resp_rec.cst_interface_id;
983 IF(l_interface_id IS NOT NULL) THEN
984 RAISE E_AMBIGUOUS_RESP_UPL;
985 END IF;
986 exception
987 -- found there is not sufficient constraint entries for the same constraint name
988 WHEN E_AMBIGUOUS_RESP_UPL THEN
989 BEGIN
990 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FOUND NON-UNIQUE RESPONSIBILITY ID' );
991 v_err_msg := 'Responsibility ID is not unique. Please create this constraint from Self-Service UI.';
992 FND_FILE.PUT_LINE(FND_FILE.LOG, '*** '||v_err_msg||' for interface_id: '||L_INTERFACE_ID);
993 update_interface_with_error(
994 p_ERR_MSG => v_err_msg
995 ,p_table_name => 'AMW_CONSTRAINTS_B'
996 ,P_INTERFACE_ID => L_INTERFACE_ID);
997 EXCEPTION
998 WHEN OTHERS THEN
999 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling INCONSIST_CST_UPL: '||sqlerrm);
1000 END;
1001 WHEN NO_DATA_FOUND THEN
1002 NULL;
1003 end;
1004 end loop;
1005
1006 /*
1007 Should not upload the constraint, if any constraint entry is invalid.
1008 So set the error flag and the status.
1009 */
1010 UPDATE amw_constraint_interface
1011 SET error_flag = 'Y',
1012 interface_status = 'Please correct all the invalid incompatible'
1013 ||' Functions/Responsibilities defined for this'
1014 ||' Constraint'
1015 WHERE error_flag IS NULL
1016 AND batch_id = p_batch_id
1017 AND (process_flag IS NULL OR process_flag = 'N')
1018 AND CST_NAME IN ( SELECT DISTINCT CST_NAME
1019 FROM amw_constraint_interface
1020 WHERE error_flag = 'Y'
1021 AND batch_id = p_batch_id
1022 AND (process_flag IS NULL OR process_flag = 'N') );
1023
1024 /*03.27.2006 psomanat: added check for error --- process further
1025 only if no error in validation above*/
1026
1027 -- 08:23:2006 psomanat : Fix for Constraint mass upload issue
1028 -- we should allways insert the valid constraint for performance
1029 -- So Commenting the if condition
1030 --IF(NOT v_error_found) THEN
1031
1032 -- loop processing each record
1033 FOR constraint_rec IN constraints_cur
1034 LOOP
1035 l_interface_id := constraint_rec.cst_interface_id;
1036 l_constraint_id := NULL;
1037 l_constraint_rev_id := NULL;
1038
1039 --fnd_file.put_line(fnd_file.LOG,'processing interface_id = '||l_interface_id);
1040
1041 -- check if the constraint already existed
1042 OPEN c_constraint_exists(constraint_rec.cst_name);
1043 FETCH c_constraint_exists INTO l_constraint_id, l_constraint_rev_id, l_approval_status;
1044 CLOSE c_constraint_exists;
1045
1046 IF (l_constraint_id is NULL) THEN
1047 -- create new constraint
1048 --fnd_file.put_line(fnd_file.LOG,'%%%%%%%%%%%% Before AMW_LOAD_CONSTRIANT_DATA.insert_constraint %%%%%%%%%%%%');
1049 --fnd_file.put_line(fnd_file.LOG,'constraint_rec.cst_name: '||constraint_rec.cst_name);
1050 insert_constraint(
1051 x_return_status => x_return_status,
1052 x_CONSTRAINT_REV_ID => l_constraint_rev_id,
1053 p_START_DATE => constraint_rec.cst_start_date,
1054 p_END_DATE => constraint_rec.cst_end_date,
1055 p_ENTERED_BY_ID => l_entered_by_id,
1056 p_TYPE_CODE => constraint_rec.cst_type_code,
1057 p_RISK_ID => constraint_rec.risk_name,
1058 p_APPROVAL_STATUS => constraint_rec.cst_approval_status_code,
1059 p_CONSTRAINT_NAME => constraint_rec.cst_name,
1060 p_CONSTRAINT_DESCRIPTION => constraint_rec.cst_description,
1061 p_user_id => p_user_id
1062 );
1063 --fnd_file.put_line(fnd_file.LOG,'%%%%%%%%%%%% After AMW_LOAD_CONSTRIANT_DATA.insert_constraint %%%%%%%%%%%%');
1064
1065 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1066 v_err_msg :='Error working in procedure: '
1067 || L_API_NAME
1068 || ' '
1069 || SUBSTR (SQLERRM, 1, 100);
1070 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1071 update_interface_with_error (v_err_msg
1072 ,'AMW_CONSTRAINTS'
1073 ,l_interface_id);
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077
1078 -- create new constraint entries for the newly created constraint
1079 -- 09.13.2005 tsho: consider group_code, object_type of constraint entries
1080 IF (substr(constraint_rec.cst_type_code,1,4) = 'RESP') THEN
1081 IF (constraint_rec.cst_type_code = 'RESPSET') THEN
1085 insert_constraint_entries(
1082 -- constraint type is Responsibility Set (RESPSET)
1083 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1084 LOOP
1086 p_constraint_rev_id => l_constraint_rev_id,
1087 p_object_id => constraint_entries_rec.cst_entries_resp_id,
1088 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1089 p_user_id => p_user_id,
1090 x_return_status => x_return_status,
1091 p_group_code => constraint_entries_rec.cst_entries_group_code,
1092 p_object_type => 'RESP' -- 21:04:2006 psomanat : temporary fix for Object_type column = null
1093 ); -- for responsibility constraint created via webadi
1094
1095 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1096 v_err_msg :='Error working in procedure: '
1097 || L_API_NAME
1098 || ' '
1099 || SUBSTR (SQLERRM, 1, 100);
1100 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1101 update_interface_with_error (v_err_msg
1102 ,'AMW_CONSTRAINT_ENTRIES'
1103 ,constraint_entries_rec.cst_interface_id);
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105 END IF;
1106 END LOOP; -- end of for: constraint_entries_rec, RESPSET constraint type
1107 ELSE
1108 -- constraint type is Responsibility (RESPALL, RESPME)
1109 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1110 LOOP
1111 insert_constraint_entries(
1112 p_constraint_rev_id => l_constraint_rev_id,
1113 p_object_id => constraint_entries_rec.cst_entries_resp_id,
1114 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1115 p_user_id => p_user_id,
1116 x_return_status => x_return_status,
1117 p_group_code => '1', -- 21:04:2006 psomanat : group code set to 1 in self service
1118 p_object_type => 'RESP' -- 21:04:2006 psomanat : temporary fix for Object_type column = null
1119 ); -- for responsibility constraint created via webadi
1120
1121 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1122 v_err_msg :='Error working in procedure: '
1123 || L_API_NAME
1124 || ' '
1125 || SUBSTR (SQLERRM, 1, 100);
1126 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1127 update_interface_with_error (v_err_msg
1128 ,'AMW_CONSTRAINT_ENTRIES'
1129 ,constraint_entries_rec.cst_interface_id);
1130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1131 END IF;
1132 END LOOP; -- end of for: constraint_entries_rec, RESPALL, RESPME constraint type
1133 END IF; -- end of if: constraint_rec.cst_type_code = 'RESPSET'
1134
1135 ELSE
1136 IF (constraint_rec.cst_type_code = 'SET') THEN
1137 -- constraint type is Function (SET)
1138 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1139 LOOP
1140 insert_constraint_entries(
1141 p_constraint_rev_id => l_constraint_rev_id,
1142 p_object_id => constraint_entries_rec.cst_entries_function_id,
1143 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1144 p_user_id => p_user_id,
1145 x_return_status => x_return_status,
1146 p_group_code => constraint_entries_rec.cst_entries_group_code,
1147 p_object_type => constraint_entries_rec.cst_violat_obj_type
1148 );
1149 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1150 v_err_msg :='Error working in procedure: '
1151 || L_API_NAME
1152 || ' '
1153 || SUBSTR (SQLERRM, 1, 100);
1154 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1155 update_interface_with_error (v_err_msg
1156 ,'AMW_CONSTRAINT_ENTRIES'
1157 ,constraint_entries_rec.cst_interface_id);
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 END IF;
1160 END LOOP; -- end of for: constraint_entries_rec, Function Constraint (SET) type
1161 ELSE
1162 -- constraint type is Function (ALL, ME)
1163 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1164 LOOP
1165 insert_constraint_entries(
1166 p_constraint_rev_id => l_constraint_rev_id,
1167 p_object_id => constraint_entries_rec.cst_entries_function_id,
1168 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1169 p_user_id => p_user_id,
1170 x_return_status => x_return_status,
1171 p_group_code => '1', -- 21:04:2006 psomanat : group code set to 1 in self service
1172 p_object_type => constraint_entries_rec.cst_violat_obj_type
1173 );
1177 || ' '
1174 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1175 v_err_msg :='Error working in procedure: '
1176 || L_API_NAME
1178 || SUBSTR (SQLERRM, 1, 100);
1179 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1180 update_interface_with_error (v_err_msg
1181 ,'AMW_CONSTRAINT_ENTRIES'
1182 ,constraint_entries_rec.cst_interface_id);
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 END IF;
1185 END LOOP; -- end of for: constraint_entries_rec, Function Constraint (ALL, ME) type
1186
1187 END IF; -- end of if: constraint_rec.cst_type_code = 'SET'
1188
1189 END IF; -- end of if: substr(constraint_rec.cst_type_code,1,4) = 'RESP'
1190
1191 ELSE
1192 -- update existing constraint with specified constraint_rev_id
1193 --fnd_file.put_line(fnd_file.LOG,'%%%%%%%%%%%% Before AMW_LOAD_CONSTRIANT_DATA.update_constraint %%%%%%%%%%%%');
1194 --fnd_file.put_line(fnd_file.LOG,'constraint_rec.cst_name: '||constraint_rec.cst_name);
1195 update_constraint(
1196 x_return_status => x_return_status,
1197 p_CONSTRAINT_REV_ID => l_constraint_rev_id,
1198 p_START_DATE => constraint_rec.cst_start_date,
1199 p_END_DATE => constraint_rec.cst_end_date,
1200 p_ENTERED_BY_ID => l_entered_by_id,
1201 p_TYPE_CODE => constraint_rec.cst_type_code,
1202 p_RISK_ID => constraint_rec.risk_name,
1203 p_APPROVAL_STATUS => constraint_rec.cst_approval_status_code,
1204 p_CONSTRAINT_NAME => constraint_rec.cst_name,
1205 p_CONSTRAINT_DESCRIPTION => constraint_rec.cst_description,
1206 p_user_id => p_user_id
1207 );
1208 --fnd_file.put_line(fnd_file.LOG,'%%%%%%%%%%%% After AMW_LOAD_CONSTRIANT_DATA.update_constraint %%%%%%%%%%%%');
1209
1210 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1211 v_err_msg :='Error working in procedure: '
1212 || L_API_NAME
1213 || ' '
1214 || SUBSTR (SQLERRM, 1, 100);
1215 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1216 update_interface_with_error (v_err_msg
1217 ,'AMW_CONSTRAINTS'
1218 ,l_interface_id);
1219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1220 END IF;
1221
1222 -- update violations's status for specified constraint_rev_d
1223 update_violations (
1224 x_return_status => x_return_status,
1225 p_constraint_rev_id => l_constraint_rev_id,
1226 p_violation_status => 'NA'
1227 );
1228 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1229 v_err_msg :='Error working in procedure: '
1230 || L_API_NAME
1231 || ' '
1232 || SUBSTR (SQLERRM, 1, 100);
1233 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1234 update_interface_with_error (v_err_msg
1235 ,'AMW_VIOLATIONS'
1236 ,l_interface_id);
1237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1238 END IF;
1239
1240
1241 -- delete existing constraint waivers for specified constraint_rev_d
1242 delete_constraint_waivers(
1243 p_constraint_rev_id => l_constraint_rev_id,
1244 x_return_status => x_return_status
1245 );
1246 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1247 v_err_msg :='Error working in procedure: '
1248 || L_API_NAME
1249 || ' '
1250 || SUBSTR (SQLERRM, 1, 100);
1251 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1252 update_interface_with_error (v_err_msg
1253 ,'AMW_CONSTRAINT_WAIVERS'
1254 ,l_interface_id);
1255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1256 END IF;
1257
1258 -- delete existing constraint entries for specified constraint_rev_d
1259 delete_constraint_entries(
1260 p_constraint_rev_id => l_constraint_rev_id,
1261 x_return_status => x_return_status
1262 );
1263 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1264 v_err_msg :='Error working in procedure: '
1265 || L_API_NAME
1266 || ' '
1267 || SUBSTR (SQLERRM, 1, 100);
1268 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1269 update_interface_with_error (v_err_msg
1270 ,'AMW_CONSTRAINT_ENTRIES'
1271 ,l_interface_id);
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274
1275 -- insert constraint entries for the specified constraint_rev_id
1276 -- 09.13.2005 tsho: consider group_code, object_type of constraint entries
1277 IF (substr(constraint_rec.cst_type_code,1,4) = 'RESP') THEN
1278 IF (constraint_rec.cst_type_code = 'RESPSET') THEN
1279 -- constraint type is Responsibility Set (RESPSET)
1280 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1281 LOOP
1282 insert_constraint_entries(
1283 p_constraint_rev_id => l_constraint_rev_id,
1284 p_object_id => constraint_entries_rec.cst_entries_resp_id,
1285 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1286 p_user_id => p_user_id,
1287 x_return_status => x_return_status,
1288 p_group_code => constraint_entries_rec.cst_entries_group_code,
1289 p_object_type => 'RESP' -- 21:04:2006 psomanat : temporary fix for Object_type column = null
1290 ); -- for responsibility constraint created via webadi
1291
1292 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1293 v_err_msg :='Error working in procedure: '
1294 || L_API_NAME
1295 || ' '
1296 || SUBSTR (SQLERRM, 1, 100);
1297 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1298 update_interface_with_error (v_err_msg
1299 ,'AMW_CONSTRAINT_ENTRIES'
1300 ,constraint_entries_rec.cst_interface_id);
1301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1302 END IF;
1303 END LOOP; -- end of for: constraint_entries_rec, RESPSET constraint type
1304 ELSE
1305 -- constraint type is Responsibility (RESPALL, RESPME)
1306 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1307 LOOP
1308 insert_constraint_entries(
1309 p_constraint_rev_id => l_constraint_rev_id,
1310 p_object_id => constraint_entries_rec.cst_entries_resp_id,
1311 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1312 p_user_id => p_user_id,
1313 x_return_status => x_return_status,
1314 p_group_code => '1', -- 21:04:2006 psomanat : group code set to 1 in self service
1315 p_object_type => 'RESP' -- 21:04:2006 psomanat : temporary fix for Object_type column = null
1316 ); -- for responsibility constraint created via webadi
1317
1318 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1319 v_err_msg :='Error working in procedure: '
1320 || L_API_NAME
1321 || ' '
1322 || SUBSTR (SQLERRM, 1, 100);
1323 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1324 update_interface_with_error (v_err_msg
1325 ,'AMW_CONSTRAINT_ENTRIES'
1326 ,constraint_entries_rec.cst_interface_id);
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329 END LOOP; -- end of for: constraint_entries_rec, RESPALL, RESPME constraint type
1330 END IF; -- end of if: constraint_rec.cst_type_code = 'RESPSET'
1331
1332 ELSE
1333 IF (constraint_rec.cst_type_code = 'SET') THEN
1334 -- constraint type is Function (SET)
1335 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1336 LOOP
1337 insert_constraint_entries(
1338 p_constraint_rev_id => l_constraint_rev_id,
1339 p_object_id => constraint_entries_rec.cst_entries_function_id,
1340 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1341 p_user_id => p_user_id,
1342 x_return_status => x_return_status,
1343 p_group_code => constraint_entries_rec.cst_entries_group_code,
1344 p_object_type => constraint_entries_rec.cst_violat_obj_type
1345 );
1346 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1347 v_err_msg :='Error working in procedure: '
1348 || L_API_NAME
1349 || ' '
1350 || SUBSTR (SQLERRM, 1, 100);
1351 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1352 update_interface_with_error (v_err_msg
1353 ,'AMW_CONSTRAINT_ENTRIES'
1354 ,constraint_entries_rec.cst_interface_id);
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 END IF;
1357 END LOOP; -- end of for: constraint_entries_rec, Function Constraint (SET) type
1358 ELSE
1359 -- constraint type is Function (ALL, ME)
1360 FOR constraint_entries_rec IN c_constraint_entries(constraint_rec.cst_name)
1361 LOOP
1362 insert_constraint_entries(
1363 p_constraint_rev_id => l_constraint_rev_id,
1364 p_object_id => constraint_entries_rec.cst_entries_function_id,
1365 p_app_id => constraint_entries_rec.cst_entries_appl_id,
1366 p_user_id => p_user_id,
1367 x_return_status => x_return_status,
1368 p_group_code => '1', -- 21:04:2006 psomanat : group code set to 1 in self service
1369 p_object_type => constraint_entries_rec.cst_violat_obj_type
1370 );
1374 || ' '
1371 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1372 v_err_msg :='Error working in procedure: '
1373 || L_API_NAME
1375 || SUBSTR (SQLERRM, 1, 100);
1376 fnd_file.put_line (fnd_file.LOG, SUBSTR (v_err_msg, 1, 200));
1377 update_interface_with_error (v_err_msg
1378 ,'AMW_CONSTRAINT_ENTRIES'
1379 ,constraint_entries_rec.cst_interface_id);
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381 END IF;
1382 END LOOP; -- end of for: constraint_entries_rec, Function Constraint (ALL, ME) type
1383
1384 END IF; -- end of if: constraint_rec.cst_type_code = 'SET'
1385
1386 END IF; -- end of if: substr(constraint_rec.cst_type_code,1,4) = 'RESP'
1387
1388 END IF;-- end of if: l_constraint_id is NULL
1389
1390 END LOOP; -- end of for: constraints_cur
1391 --END IF; -- 03.27.2006 psomanat: end check for validation error
1392
1393 -- 08.23.2006 psomanat : Fix for constraint mass upload
1394 -- we should allways insert the valid constraint for performance
1395 /*
1396 IF v_error_found THEN
1397 ROLLBACK;
1398 l_process_flag := NULL;
1399 ELSE
1400 l_process_flag := 'Y';
1401 END IF;
1402 */
1403 l_process_flag := 'Y';
1404
1405 -- check option value to delete records from interface table or not
1406 IF UPPER (l_amw_delt_constraint_intf) <> 'Y' THEN
1410 SET process_flag = l_process_flag,
1407 -- don't delete records from interface table
1408 BEGIN
1409 UPDATE amw_constraint_interface
1411 last_update_date = SYSDATE,
1412 last_updated_by = p_user_id
1413 WHERE batch_id = p_batch_id
1414 AND error_flag IS NULL;
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 fnd_file.put_line (fnd_file.LOG,'err in update process flag: '||SUBSTR (SQLERRM, 1, 200));
1418 END;
1419 ELSE
1420
1421 -- delete records from interface table if no error found
1422 IF NOT v_error_found THEN
1423 BEGIN
1424 DELETE FROM amw_constraint_interface
1425 WHERE batch_id = p_batch_id
1426 AND error_flag IS NULL;
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 fnd_file.put_line (fnd_file.LOG,'err in delete interface records: '||SUBSTR (SQLERRM, 1, 200));
1430 END;
1431 END IF;
1432 END IF; -- end of if: l_amw_delt_constraint_intf
1433
1434 EXCEPTION
1435 -- invalid entered_by_id
1436 WHEN e_invalid_entered_by_id THEN
1437 BEGIN
1438 v_err_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_UNKNOWN_EMPLOYEE');
1439 fnd_file.put_line (fnd_file.LOG, 'Invalid entered_by_id.');
1440 UPDATE amw_constraint_interface
1441 SET error_flag = 'Y',
1442 interface_status = v_err_msg
1443 WHERE batch_id = p_batch_id;
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling e_invalid_entered_by_id: '||sqlerrm);
1447 END;
1448
1449 -- no import privilege
1450 WHEN e_no_import_access THEN
1451 BEGIN
1452 v_err_msg := FND_MESSAGE.GET_STRING('AMW', 'AMW_NO_IMPORT_ACCESS');
1453 fnd_file.put_line (fnd_file.LOG, 'no import privilege');
1454 UPDATE amw_ap_interface
1455 SET error_flag = 'Y',
1456 interface_status = v_err_msg
1457 WHERE batch_id = p_batch_id;
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in handling e_no_import_access: '||sqlerrm);
1461 END;
1462
1463 -- other exceptions
1464 WHEN others THEN
1465 rollback;
1466 fnd_file.put_line (fnd_file.LOG, 'unexpected exception in create_constraints: '||sqlerrm);
1467 END create_constraints;
1468 -- ----------------------------------------------------------------------
1469 END AMW_LOAD_CONSTRAINT_DATA;