[Home] [Help]
PACKAGE BODY: APPS.QA_CHAR_UPDATE_PKG
Source
1 PACKAGE BODY QA_CHAR_UPDATE_PKG AS
2 /* $Header: qacharub.pls 120.0 2005/05/24 19:18:07 appldev noship $ */
3
4 --
5 -- FILE NAME
6 -- qacharub.pls
7 --
8 -- PACKAGE NAME
9 -- QA_CHAR_UPDATE_PKG
10 --
11 -- DESCRIPTION
12 -- This package is used for Updating all instances QA Schema when a value
13 -- stored for a Collection Element has changed externally.
14 --
15 -- This package was primarily created for handling FND User Name Changes
16 -- Which are propagated to impacted products using a Workflow Business
17 -- Event Subscription ( oracle.apps.fnd.wf.ds.user.nameChanged ).
18 --
19 -- TRACKING BUG
20 -- 4305107
21 --
22 -- HISTORY
23 -- 12-APR-2005 Sivakumar Kalyanasunderam Created.
24
25
26 -- Package Name
27 g_pkg_name CONSTANT VARCHAR2(30) := 'QA_CHAR_UPDATE_PKG';
28
29 -- Wrapper API which is invoked by the business event subscription
30 -- when FND User Name Changes
31 FUNCTION Update_User_Name
32 (
33 p_subscription_guid IN RAW,
34 p_event IN OUT NOCOPY WF_EVENT_T
35 ) RETURN VARCHAR2
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'Update_User_Name';
38 l_return_status VARCHAR2(1);
39 l_msg_count NUMBER;
40 l_msg_index_out NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_error_string VARCHAR2(5000);
43
44 l_event_key VARCHAR2(1000);
45 l_old_user_name WF_ROLES.name%TYPE;
46 l_new_user_name WF_ROLES.name%TYPE;
47
48 BEGIN
49
50 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
51 FND_LOG.string
52 (
53 FND_LOG.level_procedure,
54 g_pkg_name || '.' || l_api_name,
55 'ENTERING PROCEDURE'
56 );
57 END IF;
58
59 -- Get the Event Key
60 l_event_key := p_event.GetEventKey();
61
62 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
63 FND_LOG.string
64 (
65 FND_LOG.level_statement,
66 g_pkg_name || '.' || l_api_name,
67 'EVENT NAME: ' || p_event.GetEventName() || ' EVENT KEY: ' || l_event_key || ' USER ID: ' || FND_GLOBAL.user_id || ' LOGIN ID: ' || FND_GLOBAL.login_id
68 );
69 END IF;
70
71 -- Resolve the Event Key into the old and new User Names
72 -- The Event Key is of the form NEWUSERNAME:OLDUSERNAME
73 l_new_user_name := SUBSTR( l_event_key, 1 , (INSTR( l_event_key, ':', 1, 1 ) -1) );
74 l_old_user_name := SUBSTR( l_event_key, (INSTR( l_event_key, ':', 1, 1 ) +1) );
75
76 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
77 FND_LOG.string
78 (
79 FND_LOG.level_statement,
80 g_pkg_name || '.' || l_api_name,
81 'Before Invoking Update_Element_Value with p_old_value => ' || l_old_user_name || ' and p_new_value => ' || l_new_user_name
82 );
83 END IF;
84
85 -- Invoke the Core API to Update the Value of User Name for impacted
86 -- Elements. ( In this case the "Send Notification To" Element )
87 Update_Element_Value
88 (
89 p_api_version => 1.0,
90 p_init_msg_list => FND_API.G_TRUE,
91 p_commit => FND_API.G_TRUE,
92 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
93 p_char_id => QA_SS_CONST.send_notification_to,
94 p_old_value => l_old_user_name,
95 p_new_value => l_new_user_name,
96 x_return_status => l_return_status,
97 x_msg_count => l_msg_count,
98 x_msg_data => l_msg_data
99 );
100
101 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
102 FND_LOG.string
103 (
104 FND_LOG.level_statement,
105 g_pkg_name || '.' || l_api_name,
106 'After Invoking Update_Element_Value. Return Status is: ' || l_return_status
107 );
108 END IF;
109
110 -- Error Handling
111 IF ( l_return_status = FND_API.G_RET_STS_ERROR OR
112 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
113
114 -- Get all Error Messages
115 FOR I IN 1..l_msg_count LOOP
116 FND_MSG_PUB.get
117 (
118 p_msg_index => I,
119 p_encoded => FND_API.G_FALSE,
120 p_data => l_msg_data,
121 p_msg_index_out => l_msg_index_out
122 );
123
124 l_error_string := l_error_string || I || ': ' || l_msg_data;
125 END LOOP;
126
127 IF ( FND_LOG.level_error >= FND_LOG.g_current_runtime_level ) THEN
128 FND_LOG.string
129 (
130 FND_LOG.level_error,
131 g_pkg_name || '.' || l_api_name,
132 'User Name Update Failed with Error: ' || l_error_string
133 );
134 ELSIF ( FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level ) THEN
135 FND_LOG.string
136 (
137 FND_LOG.level_unexpected,
138 g_pkg_name || '.' || l_api_name,
139 'User Name Update Failed with Error: ' || l_error_string
140 );
141 END IF;
142
143 -- Set the Context for the WF Event
144 WF_CORE.Context
145 (
146 g_pkg_name,
147 l_api_name,
148 p_event.getEventName(),
149 p_subscription_guid
150 );
151
152 -- Set the Error Info for the WF Event
153 WF_EVENT.setErrorInfo( p_event, 'ERROR' );
154
155 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
156 FND_LOG.string
157 (
158 FND_LOG.level_procedure,
159 g_pkg_name || '.' || l_api_name,
160 'Exiting Procedure: Error'
161 );
162 END IF;
163
164 -- Return value expected by WF Event in case of error
165 RETURN 'ERROR';
166
167 END IF;
168
169 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
170 FND_LOG.string
171 (
172 FND_LOG.level_procedure,
173 g_pkg_name || '.' || l_api_name,
174 'Exiting Procedure: Success'
175 );
176 END IF;
177
178 -- Return value expected by WF Event in case of success
179 RETURN 'SUCCESS';
180
181 EXCEPTION
182 WHEN OTHERS THEN
183
184 IF ( FND_LOG.level_error >= FND_LOG.g_current_runtime_level ) THEN
185 FND_LOG.string
186 (
187 FND_LOG.level_error,
188 g_pkg_name || '.' || l_api_name,
189 'User Name Update Failed with Error: ' || SQLERRM
190 );
191 ELSIF ( FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level ) THEN
192 FND_LOG.string
193 (
194 FND_LOG.level_unexpected,
195 g_pkg_name || '.' || l_api_name,
196 'User Name Update Failed with Error: ' || SQLERRM
197 );
198 END IF;
199
200 -- Set the Context for the WF Event
201 WF_CORE.Context
202 (
203 g_pkg_name,
204 l_api_name,
205 p_event.getEventName(),
206 p_subscription_guid
207 );
208
209 -- Set the Error Info for the WF Event
210 WF_EVENT.setErrorInfo( p_event, 'ERROR' );
211
212 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
213 FND_LOG.string
214 (
215 FND_LOG.level_procedure,
216 g_pkg_name || '.' || l_api_name,
217 'Exiting Procedure: Error'
218 );
219 END IF;
220
221 -- Return value expected by WF Event in case of error
222 RETURN 'ERROR';
223
224 END Update_User_Name;
225
226 -- Local procedure for Updating QA Results
227 -- This procedure does not update value changes for Hardcoded elements
228 -- since the IDs are stored in QA Results in this case.
229 PROCEDURE update_results
230 (
231 p_char_id IN NUMBER,
232 p_old_value IN VARCHAR2,
233 p_new_value IN VARCHAR2,
234 p_commit IN BOOLEAN
235 )
236 IS
237
238 l_api_name CONSTANT VARCHAR2(30) := 'update_results';
239
240 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
241 TYPE result_column_tab IS TABLE OF qa_plan_chars.result_column_name%TYPE
242 INDEX BY BINARY_INTEGER;
243
244 l_plan_ids number_tab;
245 l_result_columns result_column_tab;
246
247 l_index_predicate VARCHAR2(32767);
248 l_dml_string VARCHAR2(32767);
249
250 BEGIN
251
252 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
253 FND_LOG.string
254 (
255 FND_LOG.level_statement,
256 g_pkg_name || '.' || l_api_name,
257 'Before Getting Result Columns for char_id: '|| p_char_id
258 );
259 END IF;
260
261 -- Get the Result Column Names for Plans containing the given Element
262 -- Filter out history plans
263 SELECT qpc.plan_id,
264 qpc.result_column_name
265 BULK COLLECT INTO
266 l_plan_ids,
267 l_result_columns
268 FROM qa_chars qc,
269 qa_plan_chars qpc,
270 qa_plans qp
271 WHERE qc.hardcoded_column IS NULL
272 AND qc.char_id = qpc.char_id
273 AND qpc.char_id = p_char_id
274 AND qpc.plan_id = qp.plan_id
275 AND qp.organization_id <> 0
276 AND NOT EXISTS
277 (
278 SELECT 1
279 FROM qa_pc_plan_relationship
280 WHERE data_entry_mode = 4
281 AND child_plan_id = qp.plan_id
282 );
283
284 IF ( l_plan_ids.COUNT = 0 ) THEN
285 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
286 FND_LOG.string
287 (
288 FND_LOG.level_statement,
289 g_pkg_name || '.' || l_api_name,
290 'No Plans found for char_id: '|| p_char_id
291 );
292 END IF;
293
294 -- No Plan containing the given Element
295 RETURN;
296 END IF;
297
298 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
299 FND_LOG.string
300 (
301 FND_LOG.level_statement,
302 g_pkg_name || '.' || l_api_name,
303 'Before calling QA_CHAR_INDEXES_PKG.get_predicate for char_id: '|| p_char_id
304 );
305 END IF;
306
307 -- Get the Index Predicate for the given element
308 -- This would improve performance by using the right index
309 QA_CHAR_INDEXES_PKG.get_predicate
310 (
311 p_char_id => p_char_id,
312 p_alias => null,
313 x_predicate => l_index_predicate
314 );
315
316 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
317 FND_LOG.string
318 (
319 FND_LOG.level_statement,
320 g_pkg_name || '.' || l_api_name,
321 'Index Predicate for char_id: '|| p_char_id || ' is ' || NVL( l_index_predicate, 'NONE' )
322 );
323 END IF;
324
325 FOR i IN l_plan_ids.FIRST .. l_plan_ids.LAST LOOP
326
327 -- Form the DML String for Updating QA Results
328 l_dml_string := 'UPDATE qa_results qr SET ' || l_result_columns(i) || ' = :1 WHERE plan_id = :2 AND ' || NVL( l_index_predicate, l_result_columns(i) ) || ' = :3 ';
329
330 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
331 FND_LOG.string
332 (
333 FND_LOG.level_statement,
334 g_pkg_name || '.' || l_api_name,
335 'Before updating QA Results with DML: ' || l_dml_string
336 );
337 END IF;
338
339 -- Perform the Updates
340 -- Ignore if there are no records with elements containing the old value
341 EXECUTE IMMEDIATE
342 l_dml_string
343 USING
344 p_new_value,
345 l_plan_ids(i),
346 p_old_value;
347
348 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
349 FND_LOG.string
350 (
351 FND_LOG.level_statement,
352 g_pkg_name || '.' || l_api_name,
353 'Updated ' || SQL%ROWCOUNT || ' Rows of QA Results with plan_id: ' || l_plan_ids(i) || ' for char_id: ' || p_char_id
354 );
355 END IF;
356
357 -- Since there a possibility of many rows being updated
358 -- we need to commit after processing each plan
359 IF ( p_commit ) THEN
360 COMMIT WORK;
361 SAVEPOINT Update_Element_Value_PKG;
362 END IF;
363
364 END LOOP;
365
366 END update_results;
367
368 -- Local procedure to update Plan Elements
369 PROCEDURE update_plan_chars
370 (
371 p_char_id IN NUMBER,
372 p_old_value IN VARCHAR2,
373 p_new_value IN VARCHAR2
374 )
375 IS
376 l_api_name CONSTANT VARCHAR2(30) := 'update_plan_chars';
377 BEGIN
378 UPDATE QA_PLAN_CHARS
379 SET default_value = p_new_value
380 WHERE char_id = p_char_id
381 AND default_value = p_old_value;
382
383 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
384 FND_LOG.string
385 (
386 FND_LOG.level_statement,
387 g_pkg_name || '.' || l_api_name,
388 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHARS.default_value for char_id: ' || p_char_id
389 );
390 END IF;
391 END update_plan_chars;
392
393 -- Local procedure to update Elements
394 PROCEDURE update_chars
395 (
396 p_char_id IN NUMBER,
397 p_old_value IN VARCHAR2,
398 p_new_value IN VARCHAR2
399 )
400 IS
401 l_api_name CONSTANT VARCHAR2(30) := 'update_chars';
402 BEGIN
403 UPDATE QA_CHARS
404 SET default_value = p_new_value
405 WHERE char_id = p_char_id
406 AND default_value = p_old_value;
407
408 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
409 FND_LOG.string
410 (
411 FND_LOG.level_statement,
412 g_pkg_name || '.' || l_api_name,
413 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHARS.default_value for char_id: ' || p_char_id
414 );
415 END IF;
416 END update_chars;
417
418 -- Local procedure to update Element Action Triggers
419 PROCEDURE update_char_action_trig
420 (
421 p_char_id IN NUMBER,
422 p_old_value IN VARCHAR2,
423 p_new_value IN VARCHAR2
424 )
425 IS
426 l_api_name CONSTANT VARCHAR2(30) := 'update_char_action_trig';
427 BEGIN
428 UPDATE QA_CHAR_ACTION_TRIGGERS
429 SET low_value_other = p_new_value
430 WHERE char_id = p_char_id
431 AND low_value_other = p_old_value;
432
433 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
434 FND_LOG.string
435 (
436 FND_LOG.level_statement,
437 g_pkg_name || '.' || l_api_name,
438 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHAR_ACTION_TRIGGERS.low_value_other for char_id: ' || p_char_id
439 );
440 END IF;
441
442 UPDATE QA_CHAR_ACTION_TRIGGERS
443 SET high_value_other = p_new_value
444 WHERE char_id = p_char_id
445 AND high_value_other = p_old_value;
446
447 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
448 FND_LOG.string
449 (
450 FND_LOG.level_statement,
451 g_pkg_name || '.' || l_api_name,
452 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHAR_ACTION_TRIGGERS.high_value_other for char_id: ' || p_char_id
453 );
454 END IF;
455 END update_char_action_trig;
456
457 -- Local procedure to update Plan Element Action Triggers
458 PROCEDURE update_plan_char_action_trig
459 (
460 p_char_id IN NUMBER,
461 p_old_value IN VARCHAR2,
462 p_new_value IN VARCHAR2
463 )
464 IS
465 l_api_name CONSTANT VARCHAR2(30) := 'update_plan_char_action_trig';
466 BEGIN
467 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
468 SET low_value_other = p_new_value
469 WHERE char_id = p_char_id
470 AND low_value_other = p_old_value
471 AND plan_id IN
472 (
473 SELECT DISTINCT plan_id
474 FROM QA_PLAN_CHARS
475 WHERE char_id = p_char_id
476 );
477
478 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
479 FND_LOG.string
480 (
481 FND_LOG.level_statement,
482 g_pkg_name || '.' || l_api_name,
483 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHAR_ACTION_TRIGGERS.low_value_other for char_id: ' || p_char_id
484 );
485 END IF;
486
487 UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
488 SET high_value_other = p_new_value
489 WHERE char_id = p_char_id
490 AND high_value_other = p_old_value
491 AND plan_id IN
492 (
493 SELECT DISTINCT plan_id
494 FROM QA_PLAN_CHARS
495 WHERE char_id = p_char_id
496 );
497
498 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
499 FND_LOG.string
500 (
501 FND_LOG.level_statement,
502 g_pkg_name || '.' || l_api_name,
503 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHAR_ACTION_TRIGGERS.high_value_other for char_id: ' || p_char_id
504 );
505 END IF;
506 END update_plan_char_action_trig;
507
508 -- Local procedure to update Plan Collection Triggers
509 PROCEDURE update_plan_coll_trig
510 (
511 p_char_id IN NUMBER,
512 p_old_value IN VARCHAR2,
513 p_new_value IN VARCHAR2
514 )
515 IS
516 l_api_name CONSTANT VARCHAR2(30) := 'update_plan_coll_trig';
517 BEGIN
518 UPDATE QA_PLAN_COLLECTION_TRIGGERS
519 SET low_value = p_new_value
520 WHERE collection_trigger_id = p_char_id
521 AND low_value = p_old_value
522 AND plan_transaction_id IN
523 (
524 SELECT qpt.plan_transaction_id
525 FROM QA_PLAN_TRANSACTIONS qpt,
526 QA_PLAN_CHARS qpc
527 WHERE qpt.plan_id = qpc.plan_id
528 AND qpc.char_id = p_char_id
529 );
530
531 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
532 FND_LOG.string
533 (
534 FND_LOG.level_statement,
535 g_pkg_name || '.' || l_api_name,
536 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_COLLECTION_TRIGGERS.low_value for char_id: ' || p_char_id
537 );
538 END IF;
539
540 UPDATE QA_PLAN_COLLECTION_TRIGGERS
541 SET high_value = p_new_value
542 WHERE collection_trigger_id = p_char_id
543 AND high_value = p_old_value
544 AND plan_transaction_id IN
545 (
546 SELECT qpt.plan_transaction_id
547 FROM QA_PLAN_TRANSACTIONS qpt,
548 QA_PLAN_CHARS qpc
549 WHERE qpt.plan_id = qpc.plan_id
550 AND qpc.char_id = p_char_id
551 );
552
553 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
554 FND_LOG.string
555 (
556 FND_LOG.level_statement,
557 g_pkg_name || '.' || l_api_name,
558 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_COLLECTION_TRIGGERS.high_value for char_id: ' || p_char_id
559 );
560 END IF;
561 END update_plan_coll_trig;
562
563 -- Local procedure to update Criteria
564 PROCEDURE update_criteria
565 (
566 p_char_id IN NUMBER,
567 p_old_value IN VARCHAR2,
568 p_new_value IN VARCHAR2
569 )
570 IS
571 l_api_name CONSTANT VARCHAR2(30) := 'update_criteria';
572 BEGIN
573 UPDATE QA_CRITERIA
574 SET low_value = p_new_value
575 WHERE char_id = p_char_id
576 AND low_value = p_old_value
577 AND criteria_id IN
578 (
579 SELECT qch.criteria_id
580 FROM QA_CRITERIA_HEADERS qch,
581 QA_PLANS qp,
582 QA_PLAN_CHARS qpc
583 WHERE qch.organization_id = qp.organization_id
584 AND qp.plan_id = qpc.plan_id
585 AND qpc.char_id = p_char_id
586 );
587
588 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
589 FND_LOG.string
590 (
591 FND_LOG.level_statement,
592 g_pkg_name || '.' || l_api_name,
593 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CRITERIA.low_value for char_id: ' || p_char_id
594 );
595 END IF;
596
597 UPDATE QA_CRITERIA
598 SET high_value = p_new_value
599 WHERE char_id = p_char_id
600 AND high_value = p_old_value
601 AND criteria_id IN
602 (
603 SELECT qch.criteria_id
604 FROM QA_CRITERIA_HEADERS qch,
605 QA_PLANS qp,
606 QA_PLAN_CHARS qpc
607 WHERE qch.organization_id = qp.organization_id
608 AND qp.plan_id = qpc.plan_id
609 AND qpc.char_id = p_char_id
610 );
611
612 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
613 FND_LOG.string
614 (
615 FND_LOG.level_statement,
616 g_pkg_name || '.' || l_api_name,
617 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CRITERIA.high_value for char_id: ' || p_char_id
618 );
619 END IF;
620 END update_criteria;
621
622 -- Local procedure to update Parent Child Criteria
623 PROCEDURE update_pc_criteria
624 (
625 p_char_id IN NUMBER,
626 p_old_value IN VARCHAR2,
627 p_new_value IN VARCHAR2
628 )
629 IS
630 l_api_name CONSTANT VARCHAR2(30) := 'update_pc_criteria';
631 BEGIN
632 UPDATE QA_PC_CRITERIA
633 SET low_value = p_new_value
634 WHERE char_id = p_char_id
635 AND low_value = p_old_value
636 AND plan_relationship_id IN
637 (
638 SELECT qppr.plan_relationship_id
639 FROM QA_PC_PLAN_RELATIONSHIP qppr,
640 QA_PLAN_CHARS qpc
641 WHERE qppr.parent_plan_id = qpc.plan_id
642 AND qpc.char_id = p_char_id
643 );
644
645 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
646 FND_LOG.string
647 (
648 FND_LOG.level_statement,
649 g_pkg_name || '.' || l_api_name,
650 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PC_CRITERIA.low_value for char_id: ' || p_char_id
651 );
652 END IF;
653
654 UPDATE QA_PC_CRITERIA
655 SET high_value = p_new_value
656 WHERE char_id = p_char_id
657 AND high_value = p_old_value
658 AND plan_relationship_id IN
659 (
660 SELECT qppr.plan_relationship_id
661 FROM QA_PC_PLAN_RELATIONSHIP qppr,
662 QA_PLAN_CHARS qpc
663 WHERE qppr.parent_plan_id = qpc.plan_id
664 AND qpc.char_id = p_char_id
665 );
666
667 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
668 FND_LOG.string
669 (
670 FND_LOG.level_statement,
671 g_pkg_name || '.' || l_api_name,
672 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PC_CRITERIA.high_value for char_id: ' || p_char_id
673 );
674 END IF;
675 END update_pc_criteria;
676
677 -- Local procedure to update Specifications
678 PROCEDURE update_specs
679 (
680 p_char_id IN NUMBER,
681 p_old_value IN VARCHAR2,
682 p_new_value IN VARCHAR2
683 )
684 IS
685 l_api_name CONSTANT VARCHAR2(30) := 'update_specs';
686 BEGIN
687 UPDATE QA_SPECS
688 SET spec_element_value = p_new_value
689 WHERE char_id = p_char_id
690 AND spec_element_value = p_old_value;
691
692 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
693 FND_LOG.string
694 (
695 FND_LOG.level_statement,
696 g_pkg_name || '.' || l_api_name,
697 'Updated ' || SQL%ROWCOUNT || ' Rows for QA_SPECS.spec_element_value for char_id: ' || p_char_id
698 );
699 END IF;
700 END update_specs;
701
702 -- Core API which would accept the element, old value and new value
703 -- and update all instances where the old value is stored
704 -- with the new value.
705 PROCEDURE Update_Element_Value
706 (
707 p_api_version IN NUMBER := NULL,
708 p_init_msg_list IN VARCHAR2 := NULL,
709 p_commit IN VARCHAR2 := NULL,
710 p_validation_level IN NUMBER := NULL,
711 p_char_id IN NUMBER,
712 p_old_value IN VARCHAR2,
713 p_new_value IN VARCHAR2,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2
717 )
718 IS
719 l_api_name CONSTANT VARCHAR2(30) := 'Update_Element_Value';
720 l_api_version CONSTANT NUMBER := 1.0;
721
722 CURSOR plan_cur IS
723 SELECT 'Y'
724 FROM QA_PLAN_CHARS qpc,
725 QA_PLANS qp
726 WHERE qpc.char_id = p_char_id
727 AND qpc.plan_id = qp.plan_id
728 AND qp.organization_id <> 0;
729
730 l_plans_exist VARCHAR2(1);
731 l_commit BOOLEAN;
732
733 BEGIN
734 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
735
736 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
737 FND_LOG.string
738 (
739 FND_LOG.level_procedure,
740 g_pkg_name || '.' || l_api_name,
741 'Entering Procedure for element: ' || p_char_id
742 );
743 END IF;
744
745 -- Standard Start of API savepoint
746 SAVEPOINT Update_Element_Value_PKG;
747
748 -- Standard call to check for call compatibility.
749 IF NOT FND_API.Compatible_API_Call
750 (
751 l_api_version,
752 NVL( p_api_version, 1.0 ),
753 l_api_name,
754 g_pkg_name
755 ) THEN
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757 END IF;
758
759 -- Initialize message list if p_init_msg_list is set to TRUE.
760 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763
764 -- Initialize API return status to success
765 x_return_status := FND_API.G_RET_STS_SUCCESS;
766
767 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
768 FND_LOG.string
769 (
770 FND_LOG.level_statement,
771 g_pkg_name || '.' || l_api_name,
772 'Before Calling update_chars'
773 );
774 END IF;
775
776 -- Invoke Local procedure to update Elements
777 update_chars
778 (
779 p_char_id => p_char_id,
780 p_old_value => p_old_value,
781 p_new_value => p_new_value
782 );
783
784 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
785 FND_LOG.string
786 (
787 FND_LOG.level_statement,
788 g_pkg_name || '.' || l_api_name,
789 'Before Calling update_char_action_trig'
790 );
791 END IF;
792
793 -- Invoke Local procedure to update Element Action Triggers
794 update_char_action_trig
795 (
796 p_char_id => p_char_id,
797 p_old_value => p_old_value,
798 p_new_value => p_new_value
799 );
800
801 -- Check if any Collection Plan is using the given element.
802 -- If no Plans found then, further processing is not required
803 OPEN plan_cur;
804 FETCH plan_cur INTO l_plans_exist;
805 IF ( plan_cur%NOTFOUND ) THEN
806
807 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
808 FND_LOG.string
809 (
810 FND_LOG.level_statement,
811 g_pkg_name || '.' || l_api_name,
812 'No Plans exist using the char_id: ' || p_char_id
813 );
814 END IF;
815
816 CLOSE plan_cur;
817
818 -- No further processing is required.
819 RETURN;
820 END IF;
821
822 CLOSE plan_cur;
823
824 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
825 FND_LOG.string
826 (
827 FND_LOG.level_statement,
828 g_pkg_name || '.' || l_api_name,
829 'Before Calling update_plan_chars'
830 );
831 END IF;
832
833 -- Invoke Local procedure to update Plan Elements
834 update_plan_chars
835 (
836 p_char_id => p_char_id,
837 p_old_value => p_old_value,
838 p_new_value => p_new_value
839 );
840
841 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
842 FND_LOG.string
843 (
844 FND_LOG.level_statement,
845 g_pkg_name || '.' || l_api_name,
846 'Before Calling update_plan_char_action_trig'
847 );
848 END IF;
849
850 -- Invoke Local procedure to update Plan Element Action Triggers
851 update_plan_char_action_trig
852 (
853 p_char_id => p_char_id,
854 p_old_value => p_old_value,
855 p_new_value => p_new_value
856 );
857
858 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
859 FND_LOG.string
860 (
861 FND_LOG.level_statement,
862 g_pkg_name || '.' || l_api_name,
863 'Before Calling update_plan_coll_trig'
864 );
865 END IF;
866
867 -- Invoke Local procedure to update Plan Collection Triggers
868 update_plan_coll_trig
869 (
870 p_char_id => p_char_id,
871 p_old_value => p_old_value,
872 p_new_value => p_new_value
873 );
874
875 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
876 FND_LOG.string
877 (
878 FND_LOG.level_statement,
879 g_pkg_name || '.' || l_api_name,
880 'Before Calling update_criteria'
881 );
882 END IF;
883
884 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
885 FND_LOG.string
886 (
887 FND_LOG.level_statement,
888 g_pkg_name || '.' || l_api_name,
889 'Before Calling update_pc_criteria'
890 );
891 END IF;
892
893 -- Invoke Local procedure to update Parent Child Criteria
894 update_pc_criteria
895 (
896 p_char_id => p_char_id,
897 p_old_value => p_old_value,
898 p_new_value => p_new_value
899 );
900
901 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
902 FND_LOG.string
903 (
904 FND_LOG.level_statement,
905 g_pkg_name || '.' || l_api_name,
906 'Before Calling update_specs'
907 );
908 END IF;
909
910 -- Invoke Local procedure to update Specifications
911 update_specs
912 (
913 p_char_id => p_char_id,
914 p_old_value => p_old_value,
915 p_new_value => p_new_value
916 );
917
918 -- Invoke Local procedure to update Criteria
919 update_criteria
920 (
921 p_char_id => p_char_id,
922 p_old_value => p_old_value,
923 p_new_value => p_new_value
924 );
925
926 -- Commit Work at this pont because there will be intermediate
927 -- Commits when QA results are updated.
928 IF ( l_commit ) THEN
929 COMMIT WORK;
930 SAVEPOINT Update_Element_Value_PKG;
931 END IF;
932
933 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
934 FND_LOG.string
935 (
936 FND_LOG.level_statement,
937 g_pkg_name || '.' || l_api_name,
938 'Before Calling update_results'
939 );
940 END IF;
941
942 -- Invoke Local procedure to update QA Results
943 update_results
944 (
945 p_char_id => p_char_id,
946 p_old_value => p_old_value,
947 p_new_value => p_new_value,
948 p_commit => l_commit
949 );
950
951 -- Commit (if requested)
952 IF ( l_commit ) THEN
953 COMMIT WORK;
954 END IF;
955
956 -- Standard call to get message count and if count is 1, get message info.
957 FND_MSG_PUB.Count_And_Get
958 (
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
964 FND_LOG.string
965 (
966 FND_LOG.level_procedure,
967 g_pkg_name || '.' || l_api_name,
968 'Exiting Procedure: Success'
969 );
970 END IF;
971
972 EXCEPTION
973
974 WHEN FND_API.G_EXC_ERROR THEN
975 ROLLBACK TO Update_Element_Value_PKG;
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 FND_MSG_PUB.Count_And_Get
978 (
979 p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982
983 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
984 FND_LOG.string
985 (
986 FND_LOG.level_procedure,
987 g_pkg_name || '.' || l_api_name,
988 'Exiting Procedure: Error'
989 );
990 END IF;
991
992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
993 ROLLBACK TO Update_Element_Value_PKG;
994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995 FND_MSG_PUB.Count_And_Get
996 (
997 p_count => x_msg_count,
998 p_data => x_msg_data
999 );
1000
1001 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1002 FND_LOG.string
1003 (
1004 FND_LOG.level_procedure,
1005 g_pkg_name || '.' || l_api_name,
1006 'Exiting Procedure: Error'
1007 );
1008 END IF;
1009
1010 WHEN OTHERS THEN
1011 ROLLBACK TO Update_Element_Value_PKG;
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1013 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
1014 FND_MSG_PUB.Add_Exc_Msg
1015 (
1016 p_pkg_name => g_pkg_name,
1017 p_procedure_name => l_api_name,
1018 p_error_text => SUBSTR(SQLERRM,1,240)
1019 );
1020 END IF;
1021
1022 FND_MSG_PUB.Count_And_Get
1023 (
1024 p_count => x_msg_count,
1025 p_data => x_msg_data
1026 );
1027
1028 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1029 FND_LOG.string
1030 (
1031 FND_LOG.level_procedure,
1032 g_pkg_name || '.' || l_api_name,
1033 'Exiting Procedure: Error'
1034 );
1035 END IF;
1036
1037 END Update_Element_Value;
1038
1039 END QA_CHAR_UPDATE_PKG;