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,
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'
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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
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,
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
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;
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,
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'
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;
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,
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'
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;
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,
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
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
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,
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
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;
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,
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'
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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' )
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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
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;
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,
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'
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;
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,
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'
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;
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,
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
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;
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,
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'
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;