1 PACKAGE BODY QA_PARENT_CHILD_COPY_PKG as
2 /* $Header: qapccpb.pls 120.0.12020000.2 2012/07/03 14:36:21 ntungare ship $ */
3
4 --this procedure copies the newest parent child relationship between source
5 --parent/child to a new relationship between target parent/child. if there
6 --are more than one relationship between the source plans it only takes the one with
7 --the most recent creation_date. it returns the old_plan_relationship_id,
8 --new plan_relationship_id as well as a return status
9 PROCEDURE COPY_PLAN_REL(p_source_parent_plan_id IN NUMBER,
10 p_source_child_plan_id IN NUMBER,
11 p_target_parent_plan_id IN NUMBER,
12 p_target_child_plan_id IN NUMBER,
13 x_old_plan_relationship_id OUT NOCOPY NUMBER,
14 x_new_plan_relationship_id OUT NOCOPY NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2)
16 IS
17 CURSOR C1 (parent_id NUMBER, child_id NUMBER) IS
18 SELECT qpr.plan_relationship_id, qpr.data_entry_mode, qpr.auto_row_count,
19 qpr.plan_relationship_type,
20 qpr.default_parent_spec, qpr.layout_mode
21 FROM qa_pc_plan_relationship qpr
22 WHERE qpr.parent_plan_id = parent_id
23 AND qpr.child_plan_id = child_id
24 ORDER BY qpr.creation_date DESC;
25 c1_rec C1%ROWTYPE;
26 x_row_id VARCHAR2(1000);
27 BEGIN
28 --fetch the most recently created parent child relationship between source parent and source child
29 OPEN C1(p_source_parent_plan_id, p_source_child_plan_id);
30
31 --make sure a relationship was found
32 FETCH C1 INTO c1_rec;
33 IF C1%NOTFOUND THEN
34 CLOSE C1;
35 x_old_plan_relationship_id := -1;
36 x_new_plan_relationship_id := -1;
37 RETURN;
38 ELSE
39 CLOSE C1;
40 END IF;
41
42 --now perform the actual insert, use table handler instead of insert_plan_rel to avoid
43 --calls to qa_ak_mapping_api
44 /*
45 QA_SS_PARENT_CHILD_PKG.insert_plan_rel(
46 p_parent_plan_id => p_target_parent_plan_id,
47 p_child_plan_id => p_target_child_plan_id,
48 p_plan_relationship_type => c1_rec.plan_relationship_type,
49 p_data_entry_mode => c1_rec.data_entry_mode,
50 p_auto_row_count => c1_rec.auto_row_count,
51 p_default_parent_spec => c1_rec.default_parent_spec,
52 x_plan_relationship_id => x_new_plan_relationship_id);
53 */
54
55 QA_PC_PLAN_REL_PKG.Insert_Row(
56 X_Rowid => x_row_id,
57 X_Plan_Relationship_Id => x_new_plan_relationship_id,
58 X_Parent_Plan_Id => p_target_parent_plan_id,
59 X_Child_Plan_id => p_target_child_plan_id,
60 X_Plan_Relationship_Type => c1_rec.plan_relationship_type,
61 X_Data_Entry_Mode => c1_rec.data_entry_mode,
62 X_Layout_mode => c1_rec.layout_mode,
63 X_Auto_Row_Count => c1_rec.auto_row_count,
64 X_Default_Parent_Spec => c1_rec.default_parent_spec,
65 X_Last_Update_Date => SYSDATE,
66 X_Last_Updated_By => fnd_global.user_id,
67 X_Creation_Date => SYSDATE,
68 X_Created_By => fnd_global.user_id,
69 X_Last_Update_Login => fnd_global.user_id);
70
71 --return a value of success
72 x_old_plan_relationship_id := c1_rec.plan_relationship_id;
73 x_return_status := fnd_api.g_true;
74 END;
75
76 --this procedure copies all element relationships pertaining to p_old_relationship_id
77 --to p_new_plan_relationship_id. returns a status value to indicate success/failure.
78 PROCEDURE COPY_ELEMENT_REL(
79 p_old_plan_relationship_id IN NUMBER,
80 p_new_plan_relationship_id IN NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2
82 )
83 IS
84 CURSOR C1 IS
85 SELECT parent_char_id, child_char_id, element_relationship_type,
86 decode(link_flag,1,'Y','N') as vlink_flag
87 FROM qa_pc_element_relationship
88 WHERE plan_relationship_id = p_old_plan_relationship_id;
89
90 l_new_element_relationship_id NUMBER;
91 BEGIN
92 --for each element relationship, insert an identical relationship with the
93 --new_plan_relationship_id also
94 FOR c1_rec IN C1 LOOP
95 QA_SS_PARENT_CHILD_PKG.insert_element_rel(
96 p_plan_relationship_id => p_new_plan_relationship_id,
97 p_parent_char_id => c1_rec.parent_char_id,
98 p_child_char_id => c1_rec.child_char_id,
99 p_element_relationship_type => c1_rec.element_relationship_type,
100 p_link_flag => c1_rec.vlink_flag,
101 x_element_relationship_id => l_new_element_relationship_id);
102 END LOOP;
103
104 --return a value of success if we make it this far
105 x_return_status := fnd_api.g_true;
106 END;
107
108 --this procedure copies all criteria pertaining to p_old_plan_relationship_id to
109 --p_new_plan_relationship_id. returns a return_status value to indicate success/failure
110 PROCEDURE COPY_CRITERIA(
111 p_old_plan_relationship_id IN NUMBER,
112 p_new_plan_relationship_id IN NUMBER,
113 x_return_status OUT NOCOPY VARCHAR2
114 )
115 IS
116 CURSOR C1 IS
117 SELECT char_id, operator, low_value, high_value
118 FROM qa_pc_criteria
119 WHERE plan_relationship_id = p_old_plan_relationship_id;
120
121 l_new_criteria_id NUMBER;
122 BEGIN
123 --for each criteria, insert an identical one with the new_plan_relationship_id
124 FOR c1_rec IN C1 LOOP
125 QA_SS_PARENT_CHILD_PKG.insert_criteria_rel(
126 p_plan_relationship_id => p_new_plan_relationship_id,
127 p_char_id => c1_rec.char_id,
128 p_operator => c1_rec.operator,
129 p_low_value => c1_rec.low_value,
130 p_high_value => c1_rec.high_value,
131 x_criteria_id => l_new_criteria_id);
132 END LOOP;
133
134 --return a value of success if we make it this far
135 x_return_status := fnd_api.g_true;
136
137 END;
138
139 --this is a helper procedure that calls the UI mapping code for a given plan_id, x_return_status is
140 --fnd_api.g_true on success, fnd_api.g_false on failure
141 --this function assumes that fnd_global.apps_initialize has already been called
142
143 -- anagarwa Tue Dec 24 11:44:52 PST 2002
144 -- Bug 2725466
145 -- The parameter plan_id is being replaced by p_plan_id to comply
146 -- with coding standards
147
148 PROCEDURE map_ui_from_plan_id(p_plan_id IN NUMBER,
149 x_return_status OUT NOCOPY VARCHAR2)
150 IS
151 l_request_id NUMBER;
152 BEGIN
153 IF p_plan_id IS NOT NULL THEN
154
155 l_request_id := fnd_request.submit_request(application => 'QA',
156 program => 'QLTSSCPB',
157 argument1 => 'CREATE',
158 argument2 => 'PLAN',
159 argument3 => to_char(p_plan_id));
160 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
161 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
162 'qapccpb.pls', 'copy_all, add ak generation request #'||l_request_id);
163 x_return_status := fnd_api.g_true;
164 end if;
165 ELSE
166 x_return_status := fnd_api.g_false;
167 END IF;
168 END;
169
170 PROCEDURE COPY_ALL(p_source_parent_plan_id IN NUMBER,
171 p_source_child_plan_id IN NUMBER,
172 p_target_parent_plan_id IN NUMBER,
173 p_target_child_plan_id IN NUMBER,
174 p_call_mapping IN VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2)
176 IS
177 l_old_plan_relationship_id NUMBER;
178 l_new_plan_relationship_id NUMBER;
179 l_return_status VARCHAR2(2);
180 BEGIN
181 --create a savepoint before copying any relationship info
182 SAVEPOINT copy_all_pub;
183
184 --attempt to copy plan relationships first
185 copy_plan_rel(p_source_parent_plan_id,
186 p_source_child_plan_id,
187 p_target_parent_plan_id,
188 p_target_child_plan_id,
189 l_old_plan_relationship_id,
190 l_new_plan_relationship_id,
191 l_return_status);
192 IF l_return_status <> fnd_api.g_true OR l_old_plan_relationship_id = -1 THEN
193 x_return_status := fnd_api.g_false;
194 RETURN;
195 END IF;
196
197 --attempt to copy element relationships next
198 copy_element_rel(l_old_plan_relationship_id,
199 l_new_plan_relationship_id,
200 l_return_status);
201 IF l_return_status <> fnd_api.g_true THEN
202 ROLLBACK TO copy_all_pub;
203 x_return_status := fnd_api.g_false;
204 RETURN;
205 END IF;
206
207 --attempt to copy criteria lastly
208 copy_criteria(l_old_plan_relationship_id,
209 l_new_plan_relationship_id,
210 l_return_status);
211 IF l_return_status <> fnd_api.g_true THEN
212 ROLLBACK TO copy_all_pub;
213 x_return_status := fnd_api.g_false;
214 RETURN;
215 END IF;
216
217 --see if we need to call the mapping functions
218 IF p_call_mapping = fnd_api.g_true THEN
219 --initialize the app for creating the UI mappings for these plans
220 fnd_global.apps_initialize(user_id => fnd_global.user_id,
221 resp_id => 20561,
222 resp_appl_id => 250);
223
224
225 map_ui_from_plan_id(p_target_parent_plan_id, x_return_status);
226 IF x_return_status <> fnd_api.g_true THEN
227 RETURN;
228 END IF;
229
230 map_ui_from_plan_id(p_target_child_plan_id, x_return_status);
231 IF x_return_status <> fnd_api.g_true THEN
232 RETURN;
233 END IF;
234 END IF;
235
236 --otherwise everything was successfull so return TRUE
237 x_return_status := fnd_api.g_true;
238 END;
239
240 --this function gets the organization_code from an org id
241 FUNCTION get_org_code_from_orgid(p_orgid IN NUMBER)
242 RETURN VARCHAR2
243 IS
244 x_orgcode mtl_parameters.organization_code%TYPE;
245 CURSOR C IS
246 SELECT organization_code
247 FROM mtl_parameters
248 WHERE organization_id = p_orgid;
249 BEGIN
250 OPEN C;
251 FETCH C INTO x_orgcode;
252 IF C%NOTFOUND THEN
253 CLOSE C;
254 RETURN NULL;
255 ELSE
256 CLOSE C;
257 RETURN x_orgcode;
258 END IF;
259 END;
260
261 --this function is copied from qltsswfb.plb to get the user_name from a user_id
262 FUNCTION get_user_name(u_id IN NUMBER) RETURN VARCHAR2 IS
263 u_name fnd_user.user_name%TYPE;
264 cursor c IS
265 SELECT DISTINCT fu.user_name FROM
266 fnd_user fu
267 WHERE
268 fu.user_id = u_id;
269 BEGIN
270 open c;
271 fetch c INTO u_name;
272 IF c%notfound THEN
273 close c;
274 RETURN NULL;
275 ELSE
276 close c;
277 RETURN u_name;
278 END IF;
279 END get_user_name;
280
281 --get_plan_view and get_import_view are copied from qa_plans_pub to build the view names in the view_name and import_view_name
282 --for a given plan name
283 FUNCTION get_plan_view_name(p_name VARCHAR2) RETURN VARCHAR2 IS
284 BEGIN
285 return 'Q_' || translate(substr(p_name, 1, 26), ' ''', '__') || '_V';
286 END get_plan_view_name;
287
288
289 FUNCTION get_import_view_name(p_name VARCHAR2) RETURN VARCHAR2 IS
290 BEGIN
291 return 'Q_' || translate(substr(p_name, 1, 25), ' ''', '__') || '_IV';
292 END get_import_view_name;
293
294 --this function copied from qltssreb.plb to parse first half of planid, plan_name
295 --token from a flat_string. returns the planid as a number
296 FUNCTION parse_id(x_result IN VARCHAR2, n IN INTEGER,
297 p IN INTEGER, q IN INTEGER)
298 RETURN NUMBER
299 IS
300 BEGIN
301 RETURN to_number(substr(x_result, p, q-p));
302 END parse_id;
303
304
305 --this function copied from qltssreb.plb to parse the plan_name from a plan_id,
306 --src_plan_name,dest_plan_name token in the flat_string. returns the plan_name
307 --with all double-separator occurrences removed
308 FUNCTION parse_value(x_result IN VARCHAR2, n IN INTEGER,
309 p IN OUT NOCOPY INTEGER)
310 RETURN plan_info
311 IS
312 new_info plan_info;
313 value qa_plans.name%TYPE;
314 c VARCHAR2(10);
315 separator CONSTANT VARCHAR2(1) := '@';
316 subseparator CONSTANT VARCHAR2(1) := ',';
317 BEGIN
318 --
319 -- Loop until a single @ is found or x_result is exhausted.
320 --
321 p := p + 1; -- add 1 before substr to skip '='
322 WHILE p <= n LOOP
323 c := substr(x_result, p, 1);
324 p := p + 1;
325 IF (c = subseparator) THEN
326 --we've completed the first name, store it in the new_info record
327 new_info.src_name := value;
328 value := '';
329 ELSIF (c = separator) THEN
330 -- take a peak at the next character, if not another @,
331 -- we have reached the end so finalize the record
332 -- Otherwise, skip this @
333 IF substr(x_result, p, 1) <> separator THEN
334 new_info.dest_name := value;
335 RETURN new_info;
336 ELSE
337 p := p + 1;
338 END IF;
339 value := value || c;
340 ELSE
341 value := value || c;
342 END IF;
343 END LOOP;
344
345 --out of characters, save the dest_name and return the record
346 new_info.dest_name := value;
347 RETURN new_info;
348 END parse_value;
349
350
351 --this function is derived from result_to_array() in qltssreb.plb.
352 --it goes through the input flat_string, p_str, and parses planid=src_plan_name!dest_plan_name tokens
353 --and inserts them into the plans_htable. it returns an integer return code:
354 --0 is success, -1 is failure
355 FUNCTION parse_flat_string(p_str IN VARCHAR2,
356 p_replace IN BOOLEAN,
357 x_htable IN OUT NOCOPY plan_htable)
358 RETURN INTEGER
359 IS
360 n INTEGER := length(p_str);
361 p INTEGER; -- starting string position
362 q INTEGER; -- ending string position
363 x_plan_id NUMBER;
364 new_info plan_info;
365 BEGIN
366 p := 1;
367 WHILE p < n LOOP
368 q := instr(p_str, '=', p);
369 --
370 -- found the first = sign. To the left, must be char_id
371 --
372 x_plan_id := parse_id(p_str, n, p, q);
373 --
374 -- To the right, must be the value
375 --
376 new_info := parse_value(p_str, n, q);
377 IF ((x_htable.exists(x_plan_id) = false) OR
378 (x_htable.exists(x_plan_id) AND p_replace)) THEN
379 x_htable(x_plan_id) := new_info;
380 END IF;
381 p := q;
382 END LOOP;
383
384 RETURN 0;
385 END parse_flat_string;
386
387 --this procedure finds the search data in the repl_htable and returns the full replacement token
388 PROCEDURE execute_NCM_find_repl_text(p_search_data IN VARCHAR2,
389 p_delim IN VARCHAR2,
390 p_suffix_num IN NUMBER,
391 p_repl_htable IN ncm_repl_htable,
392 x_repl_data OUT NOCOPY VARCHAR2)
393 IS
394 repl_info ncm_repl_info;
395 i NUMBER;
396 j NUMBER;
397 BEGIN
398 --try to match the p_search_data to find a repl_str, default repl to p_search_data
399 x_repl_data := p_search_data;
400 i := p_repl_htable.FIRST;
401 WHILE (i IS NOT NULL) LOOP
402 repl_info := p_repl_htable(i);
403
404 --check for match in all search strings
405 IF repl_info.search_str1 = p_search_data OR
406 repl_info.search_str2 = p_search_data OR
407 repl_info.search_str3 = p_search_data THEN
408 --if it's a match then use the p_suffix to figure out which repl string to use
409 IF p_suffix_num = 1 THEN
410 x_repl_data := repl_info.repl_str1;
411 ELSIF p_suffix_num = 2 THEN
412 x_repl_data := repl_info.repl_str2;
413 ELSIF p_suffix_num = 3 THEN
414 x_repl_data := repl_info.repl_str3;
415 END IF;
416
417 --exit the loop looking for a replacement
418 EXIT;
419 END IF;
420
421 i := p_repl_htable.NEXT(i);
422 END LOOP;
423
424 --add delim info to replacement text
425 IF x_repl_data IS NOT NULL THEN
426 IF p_delim = ' ' THEN
427 x_repl_data := p_delim||x_repl_data;
428 ELSE
429 x_repl_data := p_delim||x_repl_data||p_delim;
430 END IF;
431 END IF;
432 END;
433
434 --this procedure looks through the text for any of our special tokens and then uses the data before the token to do
435 --a smart replacement based on the contents of the p_repl_htable
436 PROCEDURE execute_NCM_repl_func(p_text IN OUT NOCOPY VARCHAR2,
437 p_repl_htable IN ncm_repl_htable,
438 p_ncm_suffix_list IN ncm_suffix_list_t)
439 IS
440 --initialize the suffix table
441 suffix VARCHAR2(30);
442 possible_delim VARCHAR2(1);
443 i NUMBER;
444 rev_search_pos NUMBER;
445 token_pos NUMBER;
446 delim_pos NUMBER;
447 match_count NUMBER := 1;
448 text_length NUMBER := length(p_text);
449 match_data_token VARCHAR2(32);
450 match_full_token VARCHAR2(80);
451 repl_data VARCHAR2(32);
452 BEGIN
453 FOR i IN p_ncm_suffix_list.FIRST..p_ncm_suffix_list.LAST LOOP
454 suffix := p_ncm_suffix_list(i);
455 token_pos := instr(p_text, suffix, 1, match_count);
456 WHILE token_pos <> 0 LOOP
457 --dbms_output.put_line('suffix: ('||p_ncm_suffix_list(i)||') found('||token_pos||')');
458 possible_delim := substr(p_text, token_pos-1, 1);
459 rev_search_pos := token_pos - text_length - 3;
460 IF (possible_delim <> SP_NCM_DELIM_CHAR1 AND possible_delim <> SP_NCM_DELIM_CHAR2) THEN
461 possible_delim := ' ';
462 END IF;
463 delim_pos := instr(p_text, possible_delim, rev_search_pos);
464 --dbms_output.put_line('possible delim: ('||possible_delim||') found('||delim_pos||')');
465
466 --make sure we found the delim, otherwise skip this match
467 IF delim_pos <> 0 THEN
468 match_full_token := substr(p_text, delim_pos, token_pos - delim_pos + length(suffix));
469 --figure out the data token
470 IF possible_delim = ' ' THEN
471 match_data_token := substr(p_text, delim_pos+1, token_pos - delim_pos - 1);
472 ELSE
473 match_data_token := substr(p_text, delim_pos+1, token_pos - delim_pos - 2);
474 END IF;
475
476 --dbms_output.put_line('match_data_token: ('||upper(match_data_token)||')');
477 --try to find the replacement text and do the replacement
478 execute_NCM_find_repl_text(upper(match_data_token), possible_delim, i, p_repl_htable, repl_data);
479 IF repl_data IS NOT NULL THEN
480 p_text := replace(p_text, match_full_token, repl_data||suffix);
481 END IF;
482 END IF;
483 match_count := match_count + 1;
484 token_pos := instr(p_text, suffix, 1, match_count);
485 END LOOP;
486 END LOOP;
487 END;
488
489 --this function is one of the helper functions to execute_spec_proc_requests. It updates all
490 --the actions associated with the NCM project. it returns either fnd_api.g_true or fnd_api.g_false
491 --in x_return_status and uses x_msg_data for any return code reason.
492 PROCEDURE execute_NCM_spec_proc_request(p_src_org_id IN VARCHAR2,
493 p_dest_org_code IN VARCHAR2,
494 p_phtable IN plan_htable,
495 x_msg_data OUT NOCOPY VARCHAR2,
496 x_return_status OUT NOCOPY VARCHAR2)
497 IS
498 i NUMBER;
499 suffix VARCHAR2(30);
500 l_src_plan_id NUMBER;
501 l_dest_plan_id NUMBER;
502
503 repl_info ncm_repl_info;
504 repl_htable ncm_repl_htable;
505
506 --this cursor selects all actions for a given plan_id
507 CURSOR C1 (p_plan_id NUMBER) IS
508 SELECT qpca.plan_char_action_id, qpca.alr_action_id, qpca.message
509 FROM qa_plans qp, qa_plan_chars qpc, qa_plan_char_action_triggers qpcat, qa_plan_char_actions qpca
510 WHERE qp.plan_id = qpc.plan_id AND qpc.char_id = qpcat.char_id AND qp.plan_id = qpcat.plan_id
511 AND qpcat.plan_char_action_trigger_id = qpca.plan_char_action_trigger_id AND qp.plan_id=p_plan_id;
512 l_message qa_plan_char_actions.message%TYPE;
513
514 --this cursor get's the body field from alr_actions for a given action_id
515 CURSOR C2 (p_alract_id NUMBER) IS
516 SELECT body
517 FROM alr_actions
518 WHERE action_id = p_alract_id AND application_id = 250;
519 l_body alr_actions.body%TYPE;
520
521 --initialize the list of suffixes
522 ncm_suffix_list ncm_suffix_list_t := ncm_suffix_list_t(SP_NCM_PLAN_NAME_SUFFIX, SP_NCM_VIEW_NAME_SUFFIX, SP_NCM_IMPORT_NAME_SUFFIX);
523 BEGIN
524 --as a preprocessing step, go through each plan and make the search/replacement names and put them in
525 --the repl_htable
526 l_src_plan_id := p_phtable.FIRST;
527 WHILE (l_src_plan_id IS NOT NULL) LOOP
528 repl_info.search_str1 := upper(p_phtable(l_src_plan_id).src_name);
529 repl_info.repl_str1 := upper(p_phtable(l_src_plan_id).dest_name);
530 repl_info.search_str2 := get_plan_view_name(p_phtable(l_src_plan_id).src_name);
531 repl_info.repl_str2 := get_plan_view_name(p_phtable(l_src_plan_id).dest_name);
532 repl_info.search_str3 := get_import_view_name(p_phtable(l_src_plan_id).src_name);
533 repl_info.repl_str3 := get_import_view_name(p_phtable(l_src_plan_id).dest_name);
534
535 repl_htable(l_src_plan_id) := repl_info;
536 l_src_plan_id := p_phtable.NEXT(l_src_plan_id);
537 END LOOP;
538
539 --for each target plan, look up every action associated with it
540 l_src_plan_id := p_phtable.FIRST;
541 WHILE (l_src_plan_id IS NOT NULL) LOOP
542 l_dest_plan_id := p_phtable(l_src_plan_id).dest_id;
543 --dbms_output.put_line('src planid: "'||l_src_plan_id||'", dest planid: "'||l_dest_plan_id||'"');
544
545 --get every plan_char for this target plan
546 FOR c1_rec IN C1(l_dest_plan_id) LOOP
547 l_message := c1_rec.message;
548
549 --check if the action has an alr_action_id, if so we need to get the action from the alr_alerts table
550 IF c1_rec.alr_action_id IS NOT NULL THEN
551 FOR c2_rec IN C2(c1_rec.alr_action_id) LOOP
552 l_body := c2_rec.body;
553
554 --now we do the replacement function on l_body
555 execute_NCM_repl_func(l_body, repl_htable, ncm_suffix_list);
556
557 --and update the ALR_ACTIONS table
558 UPDATE ALR_ACTIONS SET BODY = l_body WHERE APPLICATION_ID=250 AND ACTION_ID = c1_rec.alr_action_id;
559 END LOOP;
560 END IF;
561
562 --dbms_output.put_line('begin actid '||c1_rec.plan_char_action_id);
563 --make sure the message has something in it
564 IF l_message IS NOT NULL THEN
565 --now check the message field
566 execute_NCM_repl_func(l_message, repl_htable, ncm_suffix_list);
567
568 --and update the QA_PLAN_CHAR_ACTIONS table
569 UPDATE QA_PLAN_CHAR_ACTIONS SET MESSAGE = l_message WHERE PLAN_CHAR_ACTION_ID = c1_rec.plan_char_action_id;
570 END IF;
571 --dbms_output.put_line('done actid '||c1_rec.plan_char_action_id);
572 END LOOP;
573 l_src_plan_id := p_phtable.NEXT(l_src_plan_id);
574 END LOOP;
575
576 --return that we succceeded in action processing
577 x_return_status := fnd_api.g_true;
578 EXCEPTION
579 WHEN OTHERS THEN
580 x_msg_data := 'Special Processing Request "'||SP_NCM||'" encountered an error: Code('||SQLCODE||'), Message("'||SQLERRM||'")';
581 x_return_status := fnd_api.g_false;
582 RETURN;
583 END;
584
585 --this function takes the p_src_org_id, p_dest_org_code, and p_phtable representing
586 --the source and duplicate schema information and applies all special processing requests in
587 --the p_special_proc_field string and returns a success/failure indicator in x_return_status and an
588 --optional failure message in x_msg_data
589 --p_special_proc_field is of the form <token1>@<token2>@...
590 --x_return_status = fnd_api.g_true on success, fnd_api.g_false on failure
591 PROCEDURE execute_spec_proc_requests(p_src_org_id IN VARCHAR2,
592 p_dest_org_code IN VARCHAR2,
593 p_phtable IN plan_htable,
594 p_special_proc_field IN VARCHAR2,
595 x_msg_data OUT NOCOPY VARCHAR2,
596 x_return_status OUT NOCOPY VARCHAR2)
597 IS
598 n INTEGER := length(p_special_proc_field);
599 p INTEGER; -- starting string position
600 q INTEGER; -- ending string position
601 separator CONSTANT VARCHAR2(1) := '@';
602 token VARCHAR2(200);
603 BEGIN
604 --to allow for multiple, ordered special processing requests on a schema, parse the
605 --p_special_proc_field for the separator
606 p := 1;
607 WHILE p < n LOOP
608 q := instr(p_special_proc_field, separator, p);
609
610 --if we didn't find the separator, grab the rest of the string
611 IF (q = 0) THEN
612 token := substr(p_special_proc_field, p);
613 p := n;
614 ELSE
615 token := substr(p_special_proc_field, p, (q-p));
616 p := q + 1;
617 END IF;
618
619 IF (token = SP_NCM) THEN
620 execute_NCM_spec_proc_request(p_src_org_id => p_src_org_id,
621 p_dest_org_code =>p_dest_org_code,
622 p_phtable => p_phtable,
623 x_msg_data => x_msg_data,
624 x_return_status => x_return_status);
625 ELSE
626 x_return_status := fnd_api.g_false;
627 x_msg_data := 'Error while executing special processing requests! Special Processing Request Identifier "'||token||'" invalid.';
628 RETURN;
629 END IF;
630
631 --see if the special processing function returned ok, if not return
632 IF x_return_status <> fnd_api.g_true THEN
633 RETURN;
634 END IF;
635
636 END LOOP;
637
638 --x_msg_data := '';
639 x_return_status := fnd_api.g_true;
640 END;
641
642 --this function is used by setup_plans to make sure that there isn't a duplicate plan name.
643 --instead of checking the full name, however, it checks the first 25 characters since the view names
644 --generated from the plan name require the first 25 characters be unique
645 --returns 0 on success, -1 on failure
646 FUNCTION check_plan_name(p_plan_name IN VARCHAR2)
647 RETURN INTEGER
648 IS
649 CURSOR C1 IS
650 -- anagarwa Tue Dec 24 11:44:52 PST 2002
651 -- Bug 2725466
652 -- Changing SYS.DUAL to DUAL as this is the correct coding standard
653 -- The code may fail in databases that do not have SYS schema
654 SELECT 1 FROM DUAL
655 WHERE NOT EXISTS
656 (SELECT 1 FROM qa_plans
657 WHERE translate(substr(upper(name),1,25),' ''','__') =
658 translate(substr(upper(p_plan_name),1,25),' ''','__'));
659
660 C1_rec C1%ROWTYPE;
661 BEGIN
662 --dbms_output.put_line('entered check_plan_name with name: '||p_plan_name||', mauled: '||translate(substr(upper(p_plan_name), 1, 25), ' ''', '__'));
663 OPEN C1;
664 FETCH C1 INTO C1_rec;
665 IF C1%NOTFOUND THEN
666 --dbms_output.put_line('got in the good case');
667 CLOSE C1;
668 RETURN -1;
669 ELSE
670 --dbms_output.put_line('got in the else case');
671 CLOSE C1;
672 RETURN 0;
673 END IF;
674 END;
675
676 --To handle capturing an eSignature for each newly created plan, we have a parameter that
677 --forces all new plans to be created as disabled. This is implemented by moving the
678 --Effective To and Effective From dates to something which makes sense.
679 --optional failure message in x_msg_data
680 --x_return_status = fnd_api.g_true on success, fnd_api.g_false on failure
681 PROCEDURE disable_plans(p_phtable IN plan_htable,
682 x_msg_data OUT NOCOPY VARCHAR2,
683 x_return_status OUT NOCOPY VARCHAR2)
684 IS
685 i INTEGER;
686 j INTEGER;
687 n INTEGER := p_phtable.COUNT;
688 c1 NUMBER;
689 c2 NUMBER;
690 fetch_count NUMBER;
691 ignore NUMBER;
692 indx NUMBER := 1;
693
694 id_table dbms_sql.Number_Table;
695 from_table dbms_sql.Date_Table;
696 to_table dbms_sql.Date_Table;
697 select_string VARCHAR2(4000);
698 update_string VARCHAR2(4000);
699 BEGIN
700 --sanity check
701 IF p_phtable.COUNT < 1 THEN
702 x_msg_data := '';
703 x_return_status := fnd_api.g_true;
704 RETURN;
705 END IF;
706
707 --init the sql string
708 select_string := 'select plan_id, effective_from, effective_to from qa_plans where plan_id in (';
709 FOR i in 1..n LOOP
710 IF i <> n THEN
711 select_string := select_string || ':' || to_char(i) || ', ';
712 ELSE
713 select_string := select_string || ':' || to_char(i) || ')';
714 END IF;
715 END LOOP;
716
717 --use dbms_sql to execute the sql
718 c1 := dbms_sql.open_cursor;
719 dbms_sql.parse(c1, select_string, dbms_sql.native);
720
721 --bind using the dest plan_ids from the phtable
722 i := p_phtable.FIRST;
723 j := 1;
724 WHILE (i IS NOT NULL) LOOP
725 dbms_sql.bind_variable(c1, ':' || to_char(j), p_phtable(i).dest_id);
726 i := p_phtable.NEXT(i);
727 j := j + 1;
728 END LOOP;
729
730 --set up the bulk collect arrays
731 dbms_sql.define_array(c1, 1, id_table, 20, indx);
732 dbms_sql.define_array(c1, 2, from_table, 20, indx);
733 dbms_sql.define_array(c1, 3, to_table, 20, indx);
734
735 --execute the sql and collect the results
736 ignore := dbms_sql.execute(c1);
737 loop
738 fetch_count := dbms_sql.fetch_rows(c1);
739
740 dbms_sql.column_value(c1, 1, id_table);
741 dbms_sql.column_value(c1, 2, from_table);
742 dbms_sql.column_value(c1, 3, to_table);
743
744 exit when fetch_count <> 20;
745 end loop;
746 dbms_sql.close_cursor(c1);
747
748 --make sure we got everything
749 IF id_table.COUNT <> n THEN
750 x_msg_data := 'Invalid fetch count('||id_table.COUNT||'), expecting('||n||').';
751 x_return_status := fnd_api.g_false;
752 RETURN;
753 END IF;
754
755 --now go through each plan_id and modify the dates
756 FOR j in 1..n LOOP
757 IF to_table(j) IS NULL OR to_table(j) >= TRUNC(SYSDATE) THEN
758 to_table(j) := TRUNC(SYSDATE) - 1;
759 --move the from back if it's now later than the to
760 IF from_table(j) > to_table(j) THEN
761 from_table(j) := to_table(j) - 1;
762 END IF;
763 END IF;
764 END LOOP;
765
766 --now perform the bulk DML update
767 update_string := 'UPDATE QA_PLANS SET effective_from = :1, effective_to = :2 WHERE plan_id = :3';
768 c2 := dbms_sql.open_cursor;
769 dbms_sql.parse(c2, update_string, dbms_sql.native);
770 dbms_sql.bind_array(c2, ':1', from_table);
771 dbms_sql.bind_array(c2, ':2', to_table);
772 dbms_sql.bind_array(c2, ':3', id_table);
773 ignore := dbms_sql.execute(c2);
774 dbms_sql.close_cursor(c2);
775
776 --return success
777 x_msg_data := '';
778 x_return_status := fnd_api.g_true;
779
780 exception when others then
781 if dbms_sql.is_open(c1) then
782 dbms_sql.close_cursor(c1);
783 end if;
784 if dbms_sql.is_open(c2) then
785 dbms_sql.close_cursor(c2);
786 end if;
787 x_msg_data := 'unexpected error: Code('||SQLCODE||'), Message("'||SQLERRM||'")';
788 x_return_status := fnd_api.g_false;
789 RETURN;
790 END;
791
792 FUNCTION SETUP_PLANS(p_src_org_id IN VARCHAR2,
793 p_dest_org_code IN VARCHAR2,
794 p_plans_flatstring IN VARCHAR2,
795 p_root_plan_src_id IN VARCHAR2,
796 x_root_plan_dest_id OUT NOCOPY NUMBER,
797 p_special_proc_field IN VARCHAR2,
798 p_disable_plans IN VARCHAR2,
799 x_return_msg OUT NOCOPY VARCHAR2,
800 x_index_drop_list OUT NOCOPY VARCHAR2)
801 RETURN INTEGER
802 IS
803 phtable plan_htable;
804 this_plan plan_info;
805 src_org_code org_organization_definitions.organization_code%TYPE;
806 src_org_id NUMBER;
807 ret_code INTEGER;
808 row_count INTEGER := 0;
809 src_id NUMBER;
810 dest_id NUMBER;
811 child_id NUMBER;
812 l_request_id NUMBER;
813
814 user_name fnd_user.user_name%TYPE := get_user_name(fnd_global.user_id);
815 --user_name fnd_user.user_name%TYPE := 'MFG';
816 CURSOR C1(parent_id NUMBER) IS
817 SELECT child_plan_id
818 FROM qa_pc_plan_relationship
819 WHERE parent_plan_id = parent_id;
820
821 -- Bug 3926150. Performance: searching on softcoded element improved by functional indexes.
822 -- Added the cursor qa_pc, which returns char details of enabled functional indexes.
823 -- Please see bugdb/design document for more details.
824 -- srhariha. Tue Nov 30 11:59:20 PST 2004
825
826 CURSOR qa_pc(p_plan_id NUMBER) IS
827 SELECT qpc.char_id,qpc.result_column_name,qc.name
828 FROM qa_plan_chars qpc,qa_chars qc, qa_char_indexes qci
829 WHERE qpc.plan_id = p_plan_id
830 AND qpc.char_id = qc.char_id
831 AND qpc.char_id = qci.char_id
832 AND qci.enabled_flag = 1;
833 -- AND qa_char_indexes_pkg.index_exists_and_enabled(qc.char_id) = 1;
834 --local variables to facilitate certain function calls
835 l_return_status VARCHAR2(2);
836 l_msg_count NUMBER;
837 l_msg_data VARCHAR2(2000);
838 l_msg VARCHAR2(2000);
839 l_slog boolean := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
840 l_plog boolean := FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
841
842 -- Bug 3926150. Performance: searching on softcoded element improved by functional indexes.
843 -- Added two local variables.
844 -- Please see bugdb/design document for more details.
845 -- srhariha. Tue Nov 30 11:59:20 PST 2004
846
847
848 -- total message size can be 1260.
849 char_id_list VARCHAR2(2000);
850 l_temp NUMBER;
851 BEGIN
852 if FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
853 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'qapccpb.pls', 'entered setup_plans');
854 end if;
855 --fnd_log.string(FND_LOG.LEVEL_UNEXPECTED, 'qapccpb.pls', 'entered setup_plans');
856
857 --reinitialize the message list for this attempt at duplication
858 fnd_msg_pub.initialize;
859
860 --parse the source_plan_id, dest_plan_name tokens from the flat_string into
861 --a name hash table
862 ret_code := parse_flat_string(p_plans_flatstring, false, phtable);
863 IF (ret_code < 0) THEN
864 x_return_msg := 'setup_plans: failed to parse the provided plans_flatstring.';
865 RETURN -1;
866 END IF;
867
868 --lookup the org code for this org id
869 src_org_id := to_number(p_src_org_id);
870 src_org_code := get_org_code_from_orgid(src_org_id);
871
872 --make a savepoint before doing the batch of plan creations
873 SAVEPOINT setup_plans_pub;
874
875 --first, try to duplicate each involved plan and place the destination's plan id in
876 --the htable
877 src_id := phtable.FIRST;
878 WHILE (src_id IS NOT NULL) LOOP
879 --dbms_output.put_line('row'||row_count||': "'||src_id||'", "'||phtable(src_id).src_name||'", "'||phtable(src_id).dest_name||'"');
880
881 --get a reference to this plan's info
882 this_plan := phtable(src_id);
883
884 --even though copy_collection_plan checks the destination name to make sure it's
885 --unique, it doesn't tell us that it didn't copy the plan so we have to explicitly
886 --check here first and make sure that we check only the first 25 chars
887 ret_code := check_plan_name(this_plan.dest_name);
888 IF (ret_code < 0) THEN
889 ROLLBACK TO setup_plans_pub;
890 fnd_message.set_name('QA', 'QA_PC_COPY_DUPLICATE_NAME');
891 fnd_message.set_token('DESTPLANNAME', this_plan.dest_name);
892 fnd_msg_pub.add();
893 x_return_msg := 'dupilicate dest name:'||this_plan.dest_name||', retcode: '||ret_code;
894 RETURN -1;
895 END IF;
896
897 --call the copy_collection_plan procedure
898 qa_plans_pub.copy_collection_plan(
899 p_api_version => 1.0,
900 p_user_name => user_name,
901 p_plan_name => this_plan.src_name,
902 p_organization_code => src_org_code,
903 p_to_plan_name => this_plan.dest_name,
904 p_to_organization_code => p_dest_org_code,
905 x_to_plan_id => phtable(src_id).dest_id,
906 x_msg_count => l_msg_count,
907 x_msg_data => l_msg_data,
908 x_return_status => l_return_status);
909 IF l_return_status <> fnd_api.g_ret_sts_success THEN
910 ROLLBACK TO setup_plans_pub;
911 x_return_msg := 'setup_plans,copy collection_plan: src_plan('||
912 phtable(src_id).src_name||'), dest_plan('||phtable(src_id).dest_name||
913 ') returned with an error.';
914 RETURN -1;
915 END IF;
916
917 src_id := phtable.NEXT(src_id);
918 row_count := row_count + 1;
919 END LOOP;
920
921 --now that all the duplicate plans exist, we go through each plan and check if the child is
922 --created and if so copy the link
923 src_id := phtable.FIRST;
924 row_count := 0;
925 WHILE (src_id IS NOT NULL) LOOP
926 FOR C1_rec IN C1(src_id) LOOP
927 child_id := C1_rec.child_plan_id;
928 --see if we duplicated this child in the last loop
929 IF (phtable.EXISTS(child_id)) THEN
930 --child plan was copied so perform the copy_all between this parent and child with no mapping
931 copy_all(p_source_parent_plan_id => src_id,
932 p_source_child_plan_id => child_id,
933 p_target_parent_plan_id => phtable(src_id).dest_id,
934 p_target_child_plan_id => phtable(child_id).dest_id,
935 p_call_mapping => fnd_api.g_false,
936 x_return_status => l_return_status);
937 IF l_return_status <> fnd_api.g_true THEN
938 x_return_msg := 'setup_plans, copy_all: src_parent_id('||src_id||
939 '),src_child_id('||child_id||'), dest_parent_id('||
940 phtable(src_id).dest_id||'), dest_child_id('||phtable(child_id).dest_id||
941 ') failed.';
942 ROLLBACK TO setup_plans_pub;
943 RETURN -1;
944 END IF;
945 END IF;
946 END LOOP;
947 src_id := phtable.NEXT(src_id);
948 row_count := row_count + 1;
949 END LOOP;
950
951
952 -- Bug 3926150. Performance: searching on softcoded element improved by functional indexes.
953 -- Compiling the list of char elements whose functional index must be dropped.
954 -- Please see bugdb/design document for more details.
955 -- srhariha. Tue Nov 30 11:59:20 PST 2004
956
957
958
959 src_id := phtable.FIRST;
960 row_count := 0;
961 char_id_list := null;
962
963 WHILE (src_id IS NOT NULL) LOOP
964 FOR qa_pc_rec in qa_pc(phtable(src_id).dest_id) LOOP -- fetch all indexed softcoded chars
965
966 if qa_pc_rec.result_column_name =
967 qa_char_indexes_pkg.get_default_result_column(qa_pc_rec.char_id) then
968 null; -- no need to drop because it is already the best fit.
969 else
970 l_temp := qa_char_indexes_pkg.disable_index(qa_pc_rec.char_id); -- disable the index
971
972 -- To restrict message length to 1260. (60 is an offset for message content)
973 if((nvl(length(char_id_list),0) + nvl(length(qa_pc_rec.name),0)) < 1200) then
974 char_id_list := char_id_list || ', ' || qa_pc_rec.name;
975 end if;
976 end if;
977 END LOOP;
978
979 src_id := phtable.NEXT(src_id);
980 row_count := row_count + 1;
981 END LOOP;
982
983
984 --at this point, we've fully duplicated the schema. now check if any special processing needs to be done
985 execute_spec_proc_requests(p_src_org_id => p_src_org_id,
986 p_dest_org_code => p_dest_org_code,
987 p_phtable => phtable,
988 p_special_proc_field => p_special_proc_field,
989 x_msg_data => l_msg_data,
990 x_return_status => l_return_status);
991 IF l_return_status <> fnd_api.g_true THEN
992 ROLLBACK TO setup_plans_pub;
993 x_return_msg := 'setup_plans,execute special processing requests: returned "'||l_msg_data||'"';
994 RETURN -1;
995 END IF;
996
997 --here we put in the call to either create the dynamic views/UI mapping regions or to
998 --change the effective from/to dates if p_disable_plans is enabled
999 IF p_disable_plans IS NOT NULL and p_disable_plans = 'Y' THEN
1000 disable_plans(p_phtable => phtable,
1001 x_msg_data => l_msg_data,
1002 x_return_status => l_return_status);
1003 IF l_return_status <> fnd_api.g_true THEN
1004 ROLLBACK TO setup_plans_pub;
1005 x_return_msg := 'setup_plans,execute disable_plans: returned "'||l_msg_data||'"';
1006 RETURN -1;
1007 END IF;
1008 ELSE
1009 --initialize the app for creating dynamic views
1010 fnd_global.apps_initialize(user_id => fnd_global.user_id,
1011 resp_id => 20561,
1012 resp_appl_id => 250);
1013
1014 src_id := phtable.FIRST;
1015 WHILE (src_id IS NOT NULL) LOOP
1016 --get a reference to this plan's info
1017 this_plan := phtable(src_id);
1018
1019 l_request_id := fnd_request.submit_request(application => 'QA',
1020 program => 'QLTPVWWB',
1021 argument1 => get_plan_view_name(this_plan.dest_name),
1022 argument2 => NULL,
1023 argument3 => to_char(this_plan.dest_id),
1024 argument4 => get_import_view_name(this_plan.dest_name),
1025 argument5 => NULL,
1026 argument6 => NULL);
1027 if FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1028 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'qapccpb.pls', 'add view generation request #'||l_request_id);
1029 end if;
1030
1031 l_request_id := fnd_request.submit_request(application => 'QA',
1032 program => 'QLTSSCPB',
1033 argument1 => 'CREATE',
1034 argument2 => 'PLAN',
1035 argument3 => to_char(this_plan.dest_id));
1036 if FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1037 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'qapccpb.pls', 'add ui generation request #'||l_request_id);
1038 end if;
1039 src_id := phtable.NEXT(src_id);
1040 END LOOP;
1041 END IF;
1042
1043 --make one final call to regen the global view
1044 l_request_id := fnd_request.submit_request(application => 'QA',
1045 program => 'QLTPVWWB',
1046 argument1 => NULL,
1047 argument2 => NULL,
1048 argument3 => NULL,
1049 argument4 => NULL,
1050 argument5 => NULL,
1051 argument6 => 'QA_GLOBAL_RESULTS_V');
1052 if FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1053 fnd_log.string(FND_LOG.LEVEL_STATEMENT, 'qapccpb.pls', 'add global view generation request #'||l_request_id);
1054 end if;
1055
1056 ret_code := 0;
1057 --x_return_msg := '';
1058 x_return_msg := l_msg_data;
1059 IF (p_root_plan_src_id IS NULL OR p_root_plan_src_id = '' OR p_root_plan_src_id = '-1') THEN
1060 x_root_plan_dest_id := 0;
1061 ELSE
1062 x_root_plan_dest_id := phtable(to_number(p_root_plan_src_id)).dest_id;
1063 END IF;
1064
1065 -- Bug 3926150. Performance: searching on softcoded element improved by functional indexes.
1066 -- Put list into OUT parameter x_index_drop_list after stripping initial comma.
1067 -- Please see bugdb/design document for more details.
1068 -- srhariha. Tue Nov 30 11:59:20 PST 2004
1069
1070 if(char_id_list is not null) then
1071 x_index_drop_list := substr(char_id_list,2);
1072 end if;
1073
1074 RETURN ret_code;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 ROLLBACK TO setup_plans_pub;
1079 x_return_msg := 'setup_plans, unhandled exception:: Code('||SQLCODE||'), Message("'||SQLERRM||'")';
1080 --x_return_msg := 'setup_plans, unhandled exception occurred.';
1081 RETURN -1;
1082 END;
1083
1084 END QA_PARENT_CHILD_COPY_PKG;
[Home] [Help]