[Home] [Help]
PACKAGE BODY: APPS.QA_SS_PARENT_CHILD_PKG
Source
1 PACKAGE BODY qa_ss_parent_child_pkg AS
2 /* $Header: qapcssb.pls 120.4 2010/10/11 10:24:28 pdube noship $ */
3
4 FUNCTION check_for_elements (
5 p_plan_id IN NUMBER,
6 p_search_array IN qa_txn_grp.ElementsArray)
7 RETURN VARCHAR2
8
9 IS
10 x_char_id NUMBER;
11 BEGIN
12
13 x_char_id := p_search_array.FIRST;
14
15 WHILE x_char_id IS NOT NULL LOOP
16 IF (qa_plan_element_api.element_in_plan(p_plan_id, x_char_id))
17 THEN
18 x_char_id := p_search_array.NEXT(x_char_id);
19 ELSE
20 RETURN 'N';
21 END IF;
22 END LOOP;
23
24 --All Collection Elements Present
25 RETURN 'Y';
26 END check_for_elements;
27
28
29 --ilawler - bug #3436428 - Wed Mar 3 14:03:14 2004
30 --Rewrote this function to take into account the datatype when searching
31 --for plans with matching results. As an invariant, numbers, dates and
32 --datetimes are passed in the canonical form/server timezone. This conversion
33 --is handled by the client CO before passing the criteria to the PL/SQL.
34 FUNCTION check_for_results (p_plan_id IN NUMBER,
35 p_search_array IN qa_txn_grp.ElementsArray)
36 RETURN VARCHAR2
37 IS
38 l_select_clause VARCHAR2(10) := 'SELECT 1';
39 l_from_clause VARCHAR2(20) := ' FROM QA_RESULTS_V';
40 -- Bug 9773013 FP to 9492520.Added condition for status.pdube
41 -- l_where_clause VARCHAR2(4000) := ' WHERE plan_id = :1';
42 l_where_clause VARCHAR2(4000) := ' WHERE plan_id = :1 AND (status = 2 or status IS NULL) ';
43 l_query_clause VARCHAR2(4030);
44
45 l_char_id NUMBER;
46 l_res_col_name VARCHAR2(60);
47 l_data_type NUMBER;
48 l_precision NUMBER;
49 c1 NUMBER;
50 i NUMBER;
51 l_ignore NUMBER;
52
53 --array to store bind variable values along with a bind var counter
54 --that starts at 2 because of plan_id
55 TYPE bindVarTab IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
56 l_bind_vars bindVarTab;
57 l_bind_var_count NUMBER := 2;
58 l_bind_var_name VARCHAR2(60);
59
60 BEGIN
61 --add plan_id as a bind var
62 l_bind_vars(1) := p_plan_id;
63
64 --Compose our own where clause here instead of using get_where_clause
65 --because we can do binding here and get_where_clause can't because
66 --it's called by the getters for the VQR VO sql.
67 l_char_id := p_search_array.FIRST;
68 WHILE l_char_id IS NOT NULL LOOP
69 --left side of where condition
70 l_res_col_name := qa_core_pkg.get_result_column_name(l_char_id,
71 p_plan_id);
72 IF (l_char_id = qa_ss_const.item) THEN
73 l_res_col_name := 'qa_flex_util.item(organization_id, item_id)';
74 END IF;
75
76 --store value and init right side of condition to the bind var
77 l_bind_vars(l_bind_var_count) := qa_core_pkg.dequote(p_search_array(l_char_id).value);
78 l_bind_var_name := ':'||l_bind_var_count;
79
80 --depending on the plan char's properties we need to add modifiers to
81 --handle the different datatypes
82 l_data_type := qa_chars_api.datatype(l_char_id);
83 IF l_data_type = 2 THEN
84 --number
85 l_precision := qa_chars_api.decimal_precision(l_char_id);
86 IF l_precision IS NOT NULL THEN
87 l_bind_var_name := 'round(nvl(qltdate.canon_to_number(' || l_bind_var_name || '), 0), '|| l_precision || ')';
88 ELSE
89 l_bind_var_name := 'nvl(qltdate.canon_to_number(' || l_bind_var_name || '), 0)';
90 END IF;
91 END IF;
92 --only do a to_date conversion when comparing against hardcoded fields,
93 --otherwise do straight canonical string comparison with soft coded dates
94 IF l_res_col_name NOT LIKE 'CHARACTER%' THEN
95 IF l_data_type = 3 then
96 --date
97 l_bind_var_name := 'qltdate.any_to_date(' || l_bind_var_name || ')';
98 ELSIF l_data_type = 6 THEN
99 --datetime
100 l_bind_var_name := 'qltdate.any_to_datetime(' || l_bind_var_name || ')';
101 END IF;
102 END IF;
103
104 --finally append this res col/value pair to the where clause
105 l_where_clause := l_where_clause || ' AND ' ||
106 l_res_col_name || ' = ' || l_bind_var_name;
107
108 l_bind_var_count := l_bind_var_count + 1;
109 l_char_id := p_search_array.NEXT(l_char_id);
110 END LOOP;
111
112 --For performance, only retrieve one row
113 l_where_clause := l_where_clause || ' AND ROWNUM = 1';
114 l_query_clause := l_select_clause || l_from_clause || l_where_clause;
115
116 --PREPARE the query for execution
117 c1 := dbms_sql.open_cursor;
118 dbms_sql.parse(c1, l_query_clause, dbms_sql.native);
119
120 --go through the bind var values array and do the bindings
121 i := l_bind_vars.FIRST;
122 WHILE (i IS NOT NULL) LOOP
123 dbms_sql.bind_variable(c1, ':'||to_char(i), l_bind_vars(i));
124 i := l_bind_vars.NEXT(i);
125 END LOOP;
126
127 --dummy return value
128 dbms_sql.define_column(c1, 1, i);
129
130 --EXECUTE the result check query
131 l_ignore := dbms_sql.execute(c1);
132
133 --if it returned a row, return success
134 IF dbms_sql.fetch_rows(c1) > 0 THEN
135 dbms_sql.close_cursor(c1);
136 return 'Y';
137 ELSE
138 dbms_sql.close_cursor(c1);
139 return 'N';
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 --whenever we encounter an error, silence it and just return a failed result check
145 return 'N';
146
147 END check_for_results;
148
149
150 --ilawler - bug #3436428 - Thu Mar 4 11:17:32 2004
151 --Rewrote it, as with check_for_results, to consider data types. The
152 --same invariants apply here: numbers, dates and datetimes are passed
153 --in the canonical form/server timezone.
154 FUNCTION get_where_clause (p_plan_id IN NUMBER,
155 p_search_array IN qa_txn_grp.ElementsArray)
156 RETURN VARCHAR2
157 IS
158 -- Bug 9773013 FP to 9492520.Added condition for status.pdube
159 -- l_where_clause VARCHAR2(4000) := ' WHERE plan_id = '||p_plan_id;
160 l_where_clause VARCHAR2(4000) := ' WHERE plan_id = '||p_plan_id ||' AND (status = 2 or status IS NULL) ';
161
162 l_char_id NUMBER;
163 l_res_col_name VARCHAR2(60);
164 l_res_col_value VARCHAR2(210);
165 l_data_type NUMBER;
166 l_precision NUMBER;
167
168 BEGIN
169 --Compose a where clause by concatenating result column names and
170 --values into one long static string. Bind variables are not an
171 --option since this where clause is passed to VQR VOs
172 l_char_id := p_search_array.FIRST;
173 WHILE l_char_id IS NOT NULL LOOP
174 --left side of where condition
175 l_res_col_name := qa_core_pkg.get_result_column_name(l_char_id,
176 p_plan_id);
177 IF (l_char_id = qa_ss_const.item) THEN
178 l_res_col_name := 'qa_flex_util.item(organization_id, item_id)';
179 END IF;
180
181 --initialize the res_col_value to a dequoted string of our input
182 l_res_col_value := ''''||qa_core_pkg.dequote(p_search_array(l_char_id).value)||'''';
183
184 --depending on the plan char's properties we need to add modifiers to
185 --handle the different datatypes
186 l_data_type := qa_chars_api.datatype(l_char_id);
187 IF l_data_type = 2 THEN
188 --number
189 l_precision := qa_chars_api.decimal_precision(l_char_id);
190 IF l_precision IS NOT NULL THEN
191 l_res_col_value := 'round(nvl(qltdate.canon_to_number(' || l_res_col_value || '), 0), '|| l_precision || ')';
192 ELSE
193 l_res_col_value := 'nvl(qltdate.canon_to_number(' || l_res_col_value || '), 0)';
194 END IF;
195 END IF;
196 --only do a to_date conversion when comparing against hardcoded fields,
197 --otherwise do straight canonical string comparison with soft coded dates
198 IF l_res_col_name NOT LIKE 'CHARACTER%' THEN
199 IF l_data_type = 3 then
200 --date
201 l_res_col_value := 'qltdate.any_to_date(' || l_res_col_value || ')';
202 ELSIF l_data_type = 6 THEN
203 --datetime
204 l_res_col_value := 'qltdate.any_to_datetime(' || l_res_col_value || ')';
205 END IF;
206 END IF;
207
208 --finally append this res col/value pair to the where clause
209 l_where_clause := l_where_clause || ' AND ' ||
210 l_res_col_name || ' = ' || l_res_col_value;
211
212 l_char_id := p_search_array.NEXT(l_char_id);
213 END LOOP;
214
215 RETURN l_where_clause;
216
217 END get_where_clause;
218
219
220 PROCEDURE post_error_messages (p_errors IN ErrorTable)
221 IS
222
223 l_message_name VARCHAR2(1000);
224
225 BEGIN
226
227 fnd_msg_pub.Initialize();
228 fnd_msg_pub.reset();
229
230 FOR i IN p_errors.FIRST .. p_errors.LAST LOOP
231 l_message_name := p_errors(i);
232 fnd_message.set_name('QA', l_message_name);
233 fnd_msg_pub.add();
234 END LOOP;
235
236 END post_error_messages;
237
238
239 /*
240
241 This procedure is called from QaPcPlanRelVORowImpl. It validates the parent
242 and child plans for validity and return 'T' is they are valid, else it
243 return 'F'
244
245 */
246 PROCEDURE insert_plan_rel_chk(
247 p_parent_plan_id IN NUMBER,
248 p_parent_plan_name IN VARCHAR2,
249 p_child_plan_id IN NUMBER,
250 p_child_plan_name IN VARCHAR2,
251 p_data_entry_mode IN NUMBER,
252 p_layout_mode IN NUMBER,
253 p_num_auto_rows IN NUMBER,
254 x_parent_plan_id OUT NOCOPY NUMBER,
255 x_child_plan_id OUT NOCOPY NUMBER,
256 x_status OUT NOCOPY VARCHAR2)
257 IS
258
259 ret_status VARCHAR2(1) := 'T';
260 l_error_array ErrorTable;
261 result_num NUMBER;
262 l_count NUMBER;
263 l_rel_exists NUMBER;
264 -- bug 2390520
265 -- anagarwa Fri May 24 12:36:23 PDT 2002
266 l_parent_plan_id NUMBER;
267 l_child_plan_id NUMBER;
268
269 -- local variables will be used instead of input variables.
270 CURSOR c(c_parent_plan_id NUMBER,
271 c_child_plan_id NUMBER) IS
272 SELECT 1
273 FROM qa_pc_plan_relationship
274 WHERE parent_plan_id = c_parent_plan_id
275 AND child_plan_id = c_child_plan_id;
276
277 BEGIN
278
279 fnd_msg_pub.Initialize();
280 fnd_msg_pub.reset();
281
282 l_error_array.delete;
283 l_parent_plan_id := p_parent_plan_id;
284
285 IF (p_parent_plan_id is NULL OR p_parent_plan_id <0) THEN
286 select qa_plans_api.plan_id(p_parent_plan_name) into result_num from dual;
287 IF (result_num IS NULL OR result_num < 1) THEN
288 ret_status := 'F';
289 l_error_array(1) := 'QA_PC_SS_INVALID_PARENT_PLAN';
290 ELSE
291 l_parent_plan_id := result_num;
292 END IF;
293 END IF;
294
295 l_child_plan_id := p_child_plan_id;
296 IF (p_child_plan_id is NULL OR p_child_plan_id <0) THEN
297 select qa_plans_api.plan_id(p_child_plan_name) into result_num from dual;
298 IF (result_num IS NULL OR result_num < 1) THEN
299 ret_status := 'F';
300 l_count := l_error_array.count +1;
301 l_error_array(l_count) := 'QA_PC_SS_INVALID_CHILD_PLAN';
302 ELSE
303 l_child_plan_id := result_num;
304 END IF;
305 END IF;
306
307 IF l_child_plan_id = l_parent_plan_id THEN
308 ret_status := 'F';
309 l_count := l_error_array.count +1;
310 l_error_array(l_count) := 'QA_PC_SS_SAME_PLAN';
311 END IF;
312
313 OPEN c(l_parent_plan_id, l_child_plan_id) ;
314 FETCH c INTO l_rel_exists;
315 IF l_rel_exists IS NOT NULL AND
316 l_rel_exists = 1 THEN
317 -- bug 2390520
318 -- anagarwa Fri May 24 12:36:23 PDT 2002
319 ret_status := 'F';
320 l_count := l_error_array.count +1;
321 l_error_array(l_count) := 'QA_PC_SS_REL_EXISTS';
322 END IF;
323 CLOSE c;
324
325
326 --if data entry mode=automatic, then ensure that num. of rows is entered.
327 IF p_data_entry_mode = 2 AND
328 (p_num_auto_rows is NULL OR p_num_auto_rows <1) THEN
329 ret_status := 'F';
330 l_count := l_error_array.count +1;
331 l_error_array(l_count) := 'QA_PC_SS_ROW_COUNT_REQD';
332
333 -- set an error message here.
334 ELSIF p_data_entry_mode <> 2 AND p_num_auto_rows >0 THEN
335 ret_status := 'F';
336 l_count := l_error_array.count +1;
337 l_error_array(l_count) := 'QA_PC_SS_ROW_COUNT_NOT_REQD';
338 END IF;
339
340 -- assign out variables to local var.
341 x_status := ret_status;
342 x_child_plan_id := l_child_plan_id;
343 x_parent_plan_id := l_parent_plan_id;
344
345 IF ret_status = 'F' THEN
346 -- call post_error_message
347 post_error_messages(l_error_array);
348
349 END IF;
350
351 END insert_plan_rel_chk;
352
353 PROCEDURE update_plan_rel_chk(
354 p_parent_plan_id IN NUMBER,
355 p_parent_plan_name IN VARCHAR2,
356 p_child_plan_id IN NUMBER,
357 p_child_plan_name IN VARCHAR2,
358 p_data_entry_mode IN NUMBER,
359 p_layout_mode IN NUMBER,
360 p_num_auto_rows IN NUMBER,
361 p_new_plan IN VARCHAR2,
362 x_parent_plan_id OUT NOCOPY NUMBER,
363 x_child_plan_id OUT NOCOPY NUMBER,
364 x_status OUT NOCOPY VARCHAR2)
365 IS
366
367 ret_status VARCHAR2(1) := 'T';
368 l_error_array ErrorTable;
369 result_num NUMBER;
370 l_count NUMBER;
371 l_rel_exists NUMBER;
372
373 -- bug 2390520
374 -- anagarwa Fri May 24 12:36:23 PDT 2002
375 l_parent_plan_id NUMBER;
376 l_child_plan_id NUMBER;
377
378 -- local variables will be used instead of inout variables.
379 CURSOR c(c_parent_plan_id NUMBER,
380 c_child_plan_id NUMBER) IS
381 SELECT 1
382 FROM qa_pc_plan_relationship
383 WHERE parent_plan_id = c_parent_plan_id
384 AND child_plan_id = c_child_plan_id;
385
386 BEGIN
387 fnd_msg_pub.Initialize();
388
389 fnd_msg_pub.reset();
390
391 l_error_array.delete;
392 l_parent_plan_id := p_parent_plan_id;
393
394 IF (p_parent_plan_id is NULL OR p_parent_plan_id <0) THEN
395 select qa_plans_api.plan_id(p_parent_plan_name) into result_num from dual;
396 IF (result_num IS NULL OR result_num < 1) THEN
397 ret_status := 'F';
398 l_error_array(1) := 'QA_PC_SS_INVALID_PARENT_PLAN';
399 ELSE
400 l_parent_plan_id := result_num;
401 END IF;
402 END IF;
403
404 l_child_plan_id := p_child_plan_id;
405 IF (p_child_plan_id is NULL OR p_child_plan_id <0) THEN
406 select qa_plans_api.plan_id(p_child_plan_name) into result_num from dual;
407 IF (result_num IS NULL OR result_num < 1) THEN
408 ret_status := 'F';
409 l_count := l_error_array.count +1;
410 l_error_array(l_count) := 'QA_PC_SS_INVALID_CHILD_PLAN';
411 ELSE
412 l_child_plan_id := result_num;
413 END IF;
414 END IF;
415
416 IF l_child_plan_id = l_parent_plan_id THEN
417 ret_status := 'F';
418 l_count := l_error_array.count +1;
419 l_error_array(l_count) := 'QA_PC_SS_SAME_PLAN';
420 END IF;
421
422 OPEN c(l_parent_plan_id, l_child_plan_id) ;
423 FETCH c INTO l_rel_exists;
424 IF l_rel_exists IS NOT NULL AND
425 l_rel_exists = 1 AND
426 p_new_plan = 'Y' THEN
427 -- bug 2390520
428 -- anagarwa Fri May 24 12:36:23 PDT 2002
429 ret_status := 'F';
430 l_count := l_error_array.count +1;
431 l_error_array(l_count) := 'QA_PC_SS_REL_EXISTS';
432 END IF;
433 CLOSE c;
434
435
436 IF p_data_entry_mode = 2 AND
437 (p_num_auto_rows is NULL OR p_num_auto_rows <1) THEN
438 ret_status := 'F';
439 l_count := l_error_array.count +1;
440 l_error_array(l_count) := 'QA_PC_SS_ROW_COUNT_REQD';
441
442 -- set an error message here.
443 ELSIF p_data_entry_mode <> 2 AND p_num_auto_rows >0 THEN
444 ret_status := 'F';
445 l_count := l_error_array.count +1;
446 l_error_array(l_count) := 'QA_PC_SS_ROW_COUNT_NOT_REQD';
447 END IF;
448
449 x_status := ret_status;
450 x_child_plan_id := l_child_plan_id;
451 x_parent_plan_id := l_parent_plan_id;
452
453 IF ret_status = 'F' THEN
454 -- call post_error_message
455 post_error_messages(l_error_array);
456
457 END IF;
458
459 END update_plan_rel_chk;
460
461
462 PROCEDURE insert_plan_rel(p_parent_plan_id NUMBER,
463 p_child_plan_id NUMBER,
464 p_plan_relationship_type NUMBER,
465 p_data_entry_mode NUMBER,
466 p_layout_mode NUMBER,
467 p_auto_row_count NUMBER,
468 p_default_parent_spec VARCHAR2,
469 p_last_updated_by NUMBER := fnd_global.user_id,
470 p_created_by NUMBER := fnd_global.user_id,
471 p_last_update_login NUMBER := fnd_global.user_id,
472 x_plan_relationship_id IN OUT NOCOPY NUMBER)
473 IS
474
475 l_sysdate DATE;
476 x_row_id VARCHAR2(1000);
477 l_default_parent_spec NUMBER := 2;
478 l_request_id NUMBER;
479 BEGIN
480
481 SELECT sysdate INTO l_sysdate
482 FROM DUAL;
483
484 IF p_default_parent_spec = 'Y' THEN
485 l_default_parent_spec := 1;
486 END IF;
487
488 QA_PC_PLAN_REL_PKG.Insert_Row(
489 X_Rowid => x_row_id,
490 X_Plan_Relationship_Id =>x_plan_relationship_id,
491 X_Parent_Plan_Id => p_parent_plan_id,
492 X_Child_Plan_id => p_child_plan_id,
493 X_Plan_Relationship_Type => p_plan_relationship_type,
494 X_Data_Entry_Mode => p_data_entry_mode,
495 X_Layout_Mode => p_layout_mode,
496 X_Auto_Row_Count => p_auto_row_count,
497 X_Default_Parent_Spec => l_default_parent_spec,
498 X_Last_Update_Date =>l_sysdate,
499 X_Last_Updated_By => p_last_updated_by,
500 X_Creation_Date =>l_sysdate,
501 X_Created_By => p_created_by,
502 X_Last_Update_Login => p_last_update_login);
503
504 -- call mapping API twice to do AK mapping
505 -- AK mapping for VQR results inquiry
506 -- twice: once for parent planid and then child planid
507 l_request_id := fnd_request.submit_request('QA',
508 'QLTSSCPB',
509 null,
510 null,
511 FALSE,
512 'CREATE',
513 'PLAN',
514 to_char(p_parent_plan_id));
515
516 l_request_id := fnd_request.submit_request('QA',
517 'QLTSSCPB',
518 null,
519 null,
520 FALSE,
521 'CREATE',
522 'PLAN',
523 to_char(p_child_plan_id));
524
525 --No more calls to AK mapping. Converted to JRAD
526 --Also, better to go through concurrent request. so change made
527 --qa_ak_mapping_api.map_plan(p_parent_plan_id, 250, 250);
528 --qa_ak_mapping_api.map_plan(p_child_plan_id, 250, 250);
529 END insert_plan_rel;
530
531
532 PROCEDURE insert_element_rel_chk(p_parent_char_id NUMBER,
533 p_child_char_id NUMBER,
534 p_relationship_type NUMBER,
535 x_status OUT NOCOPY VARCHAR2) IS
536
537 l_error_array ErrorTable;
538 l_status VARCHAR2(1) := 'T';
539 l_count NUMBER;
540 l_parent_datatype NUMBER := -1;
541 l_child_datatype NUMBER := -1;
542 c_char_id NUMBER;
543
544 CURSOR c(c_char_id NUMBER) IS
545 SELECT datatype
546 FROM qa_chars
547 WHERE char_id = c_char_id;
548
549 BEGIN
550
551 fnd_msg_pub.Initialize();
552 fnd_msg_pub.reset();
553
554 IF l_error_array.count > 0 THEN
555 l_error_array.delete;
556 END IF;
557
558 IF p_parent_char_id IS NULL OR
559 p_parent_char_id < 0 THEN
560 l_status := 'F';
561 l_error_array(1) := 'QA_PC_SS_INVALID_PARENT_ELMNT';
562 END IF;
563
564 IF p_child_char_id IS NULL OR
565 p_child_char_id < 0 THEN
566 l_status := 'F';
567 l_count := l_error_array.count +1;
568 l_error_array(l_count) := 'QA_PC_SS_INVALID_CHILD_ELEMENT';
569 END IF;
570
571 IF p_relationship_type IS NULL OR
572 p_relationship_type <0 THEN
573 l_status := 'F';
574 l_count := l_error_array.count +1;
575 l_error_array(l_count) := 'QA_PC_SS_INVALID_ELMT_REL';
576 END IF;
577
578 --also check for datatypes and mandatory flags.
579
580 OPEN c(p_parent_char_id);
581 FETCH c INTO l_parent_datatype;
582 CLOSE c;
583
584 OPEN c(p_child_char_id);
585 FETCH c INTO l_child_datatype;
586 CLOSE c;
587
588 -- anagarwa Mon Apr 29 11:29:08 PDT 2002
589 -- Bug 2345082 : If relationship is count then datatypes don't matter.
590
591 IF( l_parent_datatype IS NOT NULL AND
592 l_child_datatype IS NOT NULL AND
593 l_parent_datatype <> l_child_datatype AND
594 p_relationship_type <> 8) THEN
595
596 -- anagarwa Wed Nov 27 10:36:00 PDT 2002
597 -- Bug 2642484. this enhancement is done for Nonconformance Sol.
598 -- Now SEQUENCE element can be copied to CHARACTER element.
599 IF (p_relationship_type = 1 AND
600 l_parent_datatype = 5 AND
601 l_child_datatype = 1) THEN
602 -- do nothing
603 null;
604 ELSE
605 l_status := 'F';
606 l_count := l_error_array.count + 1;
607 l_error_array(l_count) := 'QA_PC_SS_ELEMENT_MISMATCH';
608 END IF;
609 END IF;
610
611 x_status := l_status;
612 IF l_status = 'F' THEN
613 -- call post_error_message
614 post_error_messages(l_error_array);
615 END IF;
616
617 END insert_element_rel_chk;
618
619 PROCEDURE insert_element_rel(
620 p_plan_relationship_id NUMBER,
621 p_parent_char_id NUMBER,
622 p_child_char_id NUMBER,
623 p_element_relationship_type NUMBER,
624 p_link_flag VARCHAR2,
625 p_last_updated_by NUMBER := fnd_global.user_id,
626 p_created_by NUMBER := fnd_global.user_id,
627 p_last_update_login NUMBER := fnd_global.user_id,
628 x_element_relationship_id OUT NOCOPY NUMBER) IS
629
630 l_sysdate DATE;
631 l_row_id VARCHAR2(1000);
632 l_link_flag NUMBER;
633
634 BEGIN
635
636 SELECT sysdate INTO l_sysdate
637 FROM DUAL;
638
639 IF p_link_flag = 'Y' THEN
640 l_link_flag := 1;
641 ELSE
642 l_link_flag := 2;
643 END IF;
644
645 QA_PC_ELEMENT_REL_PKG.Insert_Row(
646 X_Rowid => l_row_id,
647 X_Element_Relationship_Id => x_element_relationship_id,
648 X_Plan_Relationship_Id =>p_plan_relationship_id,
649 X_Parent_Char_id => p_parent_char_id,
650 X_Child_Char_id => p_child_char_id,
651 X_Element_Relationship_Type=>p_element_relationship_type,
652 X_Link_Flag=>l_link_flag,
653 X_Last_Update_Date=>l_sysdate,
654 X_Last_Updated_By => p_last_updated_by,
655 X_Creation_Date =>l_sysdate,
656 X_Created_By => p_created_by,
657 X_Last_Update_Login => p_last_update_login);
658
659 END insert_element_rel;
660
661 PROCEDURE insert_criteria_rel(p_plan_relationship_id NUMBER,
662 p_char_id NUMBER,
663 p_operator NUMBER,
664 p_low_value VARCHAR2,
665 -- p_low_value_id NUMBER,
666 p_high_value VARCHAR2,
667 -- p_high_value_id NUMBER,
668 p_last_updated_by NUMBER := fnd_global.user_id,
669 p_created_by NUMBER := fnd_global.user_id,
670 p_last_update_login NUMBER := fnd_global.user_id,
671 x_criteria_id OUT NOCOPY NUMBER) IS
672
673 l_sysdate DATE;
674 l_row_id VARCHAR2(1000);
675
676 BEGIN
677
678 SELECT sysdate INTO l_sysdate
679 FROM DUAL;
680
681 QA_PC_CRITERIA_PKG.Insert_Row(
682 X_Rowid => l_row_id,
683 X_Criteria_Id => x_criteria_id,
684 X_Plan_Relationship_Id =>p_plan_relationship_id,
685 X_Char_id => p_char_id,
686 X_Operator => p_operator,
687 X_Low_Value => p_low_value,
688 X_Low_Value_Id => null,
689 X_High_Value => p_high_value,
690 X_High_Value_Id => null,
691 X_Last_Update_Date=>l_sysdate,
692 X_Last_Updated_By => p_last_updated_by,
693 X_Creation_Date =>l_sysdate,
694 X_Created_By => p_created_by,
695 X_Last_Update_Login => p_last_update_login);
696
697 END insert_criteria_rel;
698
699
700 PROCEDURE update_plan_rel(
701 -- p_rowid VARCHAR2,
702 p_plan_relationship_id NUMBER,
703 p_parent_plan_id NUMBER,
704 p_child_plan_id NUMBER,
705 p_plan_relationship_type NUMBER,
706 p_data_entry_mode NUMBER,
707 p_layout_mode NUMBER,
708 p_auto_row_count NUMBER,
709 p_default_parent_spec VARCHAR2,
710 p_last_updated_by NUMBER:=fnd_global.user_id,
711 p_created_by NUMBER:=fnd_global.user_id,
712 p_last_update_login NUMBER:=fnd_global.user_id
713 ) IS
714 l_sysdate DATE;
715 l_rowid VARCHAR2(1000);
716 l_default_parent_spec NUMBER := 2;
717
718 CURSOR c IS
719
720 select rowid
721 from qa_pc_plan_relationship
722 where plan_relationship_id = p_plan_relationship_id;
723
724 BEGIN
725
726 SELECT sysdate INTO l_sysdate
727 FROM DUAL;
728
729 OPEN c ;
730 FETCH c INTO l_rowid;
731 CLOSE c;
732
733 IF p_default_parent_spec = 'Y' THEN
734 l_default_parent_spec := 1;
735 END IF;
736
737 QA_PC_PLAN_REL_PKG.Update_Row(
738 X_Rowid => l_rowid,
739 X_Plan_Relationship_Id => p_plan_relationship_id,
740 X_Parent_Plan_Id => p_parent_plan_id,
741 X_Child_Plan_id => p_child_plan_id,
742 X_Plan_Relationship_Type => p_plan_relationship_type,
743 X_Data_Entry_Mode => p_data_entry_mode,
744 X_Layout_Mode => p_layout_mode,
745 X_Auto_Row_Count => p_auto_row_count,
746 X_Default_Parent_Spec => l_default_parent_spec,
747 X_Last_Update_Date => l_sysdate,
748 X_Last_Updated_By => p_last_updated_by,
749 X_Creation_Date => l_sysdate,
750 X_Created_By => p_created_by,
751 X_Last_Update_Login => p_last_update_login );
752
753
754 END update_plan_rel;
755
756 PROCEDURE update_element_rel(
757 p_element_relationship_id NUMBER,
758 p_plan_relationship_id NUMBER,
759 p_parent_char_id NUMBER,
760 p_child_char_id NUMBER,
761 p_element_relationship_type NUMBER,
762 p_link_flag VARCHAR2,
763 p_last_updated_by NUMBER := fnd_global.user_id,
764 p_created_by NUMBER := fnd_global.user_id,
765 p_last_update_login NUMBER := fnd_global.user_id,
766 p_row_id VARCHAR2) IS
767
768 l_sysdate DATE;
769 l_row_id VARCHAR2(1000);
770 l_link_flag NUMBER;
771
772 BEGIN
773
774 SELECT sysdate INTO l_sysdate
775 FROM DUAL;
776
777 IF p_link_flag = 'Y' THEN
778 l_link_flag := 1;
779 ELSE
780 l_link_flag := 2;
781 END IF;
782
783 QA_PC_ELEMENT_REL_PKG.Update_Row(
784 X_Rowid => p_row_id,
785 X_Element_Relationship_Id => p_element_relationship_id,
786 X_Plan_Relationship_Id =>p_plan_relationship_id,
787 X_Parent_Char_id => p_parent_char_id,
788 X_Child_Char_id => p_child_char_id,
789 X_Element_Relationship_Type=>p_element_relationship_type,
790 X_Link_Flag=>l_link_flag,
791 X_Last_Update_Date=>l_sysdate,
792 X_Last_Updated_By => p_last_updated_by,
793 X_Creation_Date =>l_sysdate,
794 X_Created_By => p_created_by,
795 X_Last_Update_Login => p_last_update_login);
796
797 END update_element_rel;
798
799
800 PROCEDURE update_criteria_rel(
801 p_rowid VARCHAR2,
802 p_plan_relationship_id NUMBER,
803 p_char_id NUMBER,
804 p_operator NUMBER,
805 p_low_value VARCHAR2,
806 p_high_value VARCHAR2,
807 p_last_updated_by NUMBER := fnd_global.user_id,
808 p_created_by NUMBER := fnd_global.user_id,
809 p_last_update_login NUMBER := fnd_global.user_id,
810 p_criteria_id NUMBER) IS
811
812 l_sysdate DATE;
813
814 BEGIN
815
816 SELECT sysdate INTO l_sysdate
817 FROM DUAL;
818
819 QA_PC_CRITERIA_PKG.Update_Row(
820 X_Rowid => p_rowid,
821 X_Criteria_Id => p_criteria_id,
822 X_Plan_Relationship_Id =>p_plan_relationship_id,
823 X_Char_id => p_char_id,
824 X_Operator => p_operator,
825 X_Low_Value => p_low_value,
826 X_Low_Value_Id => null,
827 X_High_Value => p_high_value,
828 X_High_Value_Id => null,
829 X_Last_Update_Date=>l_sysdate,
830 X_Last_Updated_By => p_last_updated_by,
831 X_Creation_Date =>l_sysdate,
832 X_Created_By => p_created_by,
833 X_Last_Update_Login => p_last_update_login);
834
835 END update_criteria_rel;
836
837 PROCEDURE delete_element_rel(p_element_relationship_id NUMBER) IS
838
839 BEGIN
840 --QA_PC_CRITERIA_PKG.Delete_Row(X_Rowid => p_rowid);
841 DELETE FROM QA_PC_ELEMENT_RELATIONSHIP
842 WHERE element_relationship_id = p_element_relationship_id;
843
844 END delete_element_rel;
845
846
847 PROCEDURE delete_criteria(p_criteria_id NUMBER) IS
848
849 BEGIN
850 --QA_PC_CRITERIA_PKG.Delete_Row(X_Rowid => p_rowid);
851 DELETE FROM QA_PC_CRITERIA
852 WHERE criteria_id = p_criteria_id;
853
854 END delete_criteria;
855
856
857
858 /*
859 This function takes in plan_id, collection_id and occurrence and returns a 'T'
860 if it finds any child record for this record. Otherwise it returns 'F'.
861 */
862
863 FUNCTION descendant_plans_exist(p_plan_id NUMBER)
864 RETURN VARCHAR2 IS
865
866 CURSOR c(c_child_plan_id NUMBER) IS
867 SELECT 1
868 FROM qa_pc_plan_relationship
869 WHERE parent_plan_id = c_child_plan_id;
870
871 l_exists NUMBER:= -1;
872
873 BEGIN
874
875 OPEN c(p_plan_id);
876 FETCH c INTO l_exists;
877 CLOSE c;
878
879 IF (l_exists <> 1) THEN
880 RETURN 'No';
881 END IF;
882
883 RETURN 'Yes';
884 END descendant_plans_exist;
885
886
887 FUNCTION is_plan_applicable (
888 p_plan_id IN NUMBER,
889 search_array IN qa_txn_grp.ElementsArray)
890 RETURN VARCHAR2
891 IS
892 security_profile NUMBER;
893 allow VARCHAR2(1) := 'T';
894 BEGIN
895
896 /*
897 --add security related logic here
898 --
899 security_profile := FND_PROFILE.VALUE('QA_SECURITY_USED');
900 IF (security_profile = 1) THEN --make use of security
901 allow := fnd_data_security.check_function(
902 p_api_version => 1.0,
903 p_function => 'QA_RESULTS_VIEW',
904 p_object_name => 'QA_PLANS',
905 p_instance_pk1_value => p_plan_id
906 );
907 --user name is default current user
908 --not necessary to pass in
909 if (allow = 'F') then
910 return 'N';--plan does not apply
911 end if; --else continue on below
912 END IF;--end if for security profile
913 --end security related logic
914 */
915
916
917 IF ( check_for_elements(p_plan_id, search_array) = 'N')
918 THEN
919 RETURN 'N'; --plan does not apply
920 END IF;
921
922 IF ( check_for_results(p_plan_id, search_array) = 'N')
923 THEN
924 RETURN 'N';
925 END IF;
926
927 -- if reached here all checks passed fine
928 RETURN 'Y'; --plan applies
929
930 END is_plan_applicable; --end function
931
932 FUNCTION get_plan_vqr_sql (
933 p_plan_id IN NUMBER,
934 p_search_str IN VARCHAR2,
935 p_collection_id IN NUMBER,
936 p_occurrence IN NUMBER,
937 p_search_str2 IN VARCHAR2 default null, --future use
938 p_search_str3 IN VARCHAR2 default null) --future use
939 RETURN VARCHAR2
940 IS
941 search_array qa_txn_grp.ElementsArray;
942 SelectFromClause VARCHAR2(20000);
943 WhereClause VARCHAR2(20000);
944 BEGIN
945 --p_search_str is of form 10=XYZ@19=Ssaf@87=fsfsf
946 --should not have '@' at the beginning or end
947 --
948
949 SelectFromClause :=
950 qa_results_interface_pkg.get_plan_vqr_sql (p_plan_id);
951
952 --below for direct link, first check coll id and occ values
953
954 if (p_collection_id is not null and p_occurrence is not null
955 and p_collection_id <> -1 and p_occurrence <> -1) then
956 -- bug 9773013 FP to 9492520.Added condition for status.pdube
957 WhereClause := ' WHERE plan_id = '||p_plan_id
958 || ' AND (status =2 or status is null) '
959 || ' AND collection_id = '||p_collection_id
960 || ' AND occurrence = ' || p_occurrence;
961 else
962
963 search_array := qa_txn_grp.result_to_array(p_search_str);
964
965 WhereClause := get_where_clause(p_plan_id, search_array);
966 end if; --for direct link embedding in if clause
967
968 RETURN SelectFromClause || WhereClause;
969
970 END get_plan_vqr_sql; --end function
971
972 FUNCTION get_child_vqr_sql (
973 p_child_plan_id IN NUMBER,
974 p_parent_plan_id IN NUMBER,
975 p_parent_collection_id IN NUMBER,
976 p_parent_occurrence IN NUMBER)
977 RETURN VARCHAR2
978 IS
979
980 SelectFromClause VARCHAR2(20000);
981 WhereClause VARCHAR2(20000);
982 BEGIN
983
984 SelectFromClause :=
985 qa_results_interface_pkg.get_plan_vqr_sql (p_child_plan_id);
986
987
988 -- Bug 9773013.Forward porting bug#9207821.Added condition of status = 2 to pick only
989 -- valid child records.pdube
990 WhereClause := ' WHERE PLAN_ID = ' || p_child_plan_id
991 || ' AND (STATUS = 2 or STATUS IS NULL) '
992 || ' AND (COLLECTION_ID, OCCURRENCE) IN (SELECT CHILD_COLLECTION_ID, CHILD_OCCURRENCE FROM QA_PC_RESULTS_RELATIONSHIP WHERE PARENT_OCCURRENCE = ' || p_parent_occurrence || ' AND CHILD_PLAN_ID = ' || p_child_plan_id || ' ) ';
993
994
995 RETURN SelectFromClause || WhereClause;
996
997 END get_child_vqr_sql; --end function
998
999 FUNCTION get_parent_vqr_sql (
1000 p_parent_plan_id IN NUMBER,
1001 p_parent_collection_id IN NUMBER,
1002 p_parent_occurrence IN NUMBER)
1003 RETURN VARCHAR2
1004 IS
1005
1006 SelectFromClause VARCHAR2(20000);
1007 WhereClause VARCHAR2(20000);
1008 BEGIN
1009
1010 SelectFromClause :=
1011 qa_results_interface_pkg.get_plan_vqr_sql (p_parent_plan_id);
1012
1013
1014 -- Bug 9773013 FP to 9492520.Added condition for status.pdube
1015 WhereClause := ' WHERE PLAN_ID = ' || p_parent_plan_id
1016 || ' AND (STATUS = 2 OR STATUS IS NULL ) '
1017 || ' AND COLLECTION_ID = ' || p_parent_collection_id
1018 || ' AND OCCURRENCE = ' || p_parent_occurrence;
1019
1020
1021 RETURN SelectFromClause || WhereClause;
1022
1023 END get_parent_vqr_sql; --end function
1024
1025
1026
1027 PROCEDURE delete_plan_rel(p_plan_relationship_id NUMBER) IS
1028
1029 BEGIN
1030
1031 DELETE FROM QA_PC_PLAN_RELATIONSHIP
1032 WHERE plan_relationship_id = p_plan_relationship_id;
1033
1034 DELETE FROM QA_PC_ELEMENT_RELATIONSHIP
1035 WHERE plan_relationship_id = p_plan_relationship_id;
1036
1037 DELETE FROM QA_PC_CRITERIA
1038 WHERE plan_relationship_id = p_plan_relationship_id;
1039
1040 END delete_plan_rel;
1041
1042 --ilawler - bug #3436428 - Thu Mar 4 11:17:32 2004
1043 --cleaned up the code and changed return semantics
1044 --
1045 --p_search_str is of form: '<char_id1>=<val1>@<char_id2>=<val2>@...'
1046 --if plans found, returns string in the form: '<plan_id1>, <plan_id2>, ...'
1047 --
1048 --Invariants: p_search_str should not have a '@' at the beginning or end
1049 FUNCTION get_plan_ids (p_search_str IN VARCHAR2,
1050 p_org_id IN VARCHAR2 default null,
1051 p_search_str2 IN VARCHAR2 default null, --future use
1052 p_search_str3 IN VARCHAR2 default null)
1053 RETURN VARCHAR2
1054 IS
1055 l_plan_ids VARCHAR2(2000) := '';
1056 l_plan_seen BOOLEAN := false;
1057 l_plan_separator VARCHAR2(1) := ',';
1058 l_applicable VARCHAR2(1);
1059 l_search_array qa_txn_grp.ElementsArray;
1060
1061 cursor l_plans_cursor IS
1062 select distinct qpr.parent_plan_id
1063 from qa_pc_plan_relationship qpr, qa_plans qp
1064 where qpr.parent_plan_id = qp.plan_id
1065 and qp.organization_id = p_org_id;
1066 BEGIN
1067 --sanity check, don't let them blink search using an org
1068 IF (p_search_str IS NULL) THEN
1069 RETURN '';
1070 END IF;
1071
1072 --parse the search criteria into an array
1073 l_search_array := qa_txn_grp.result_to_array(p_search_str);
1074
1075 --loop over possible parent plans, checking each one
1076 FOR l_plan_rec IN l_plans_cursor LOOP
1077 l_applicable := is_plan_applicable(l_plan_rec.parent_plan_id,
1078 l_search_array);
1079 IF (l_applicable = 'Y') THEN
1080 IF l_plan_seen THEN
1081 l_plan_ids := l_plan_ids || l_plan_separator || l_plan_rec.parent_plan_id;
1082 ELSE
1083 l_plan_ids := l_plan_rec.parent_plan_id;
1084 l_plan_seen := true;
1085 END IF;
1086 END IF;
1087
1088 END LOOP;
1089
1090 RETURN l_plan_ids;
1091 END get_plan_ids;
1092
1093 END qa_ss_parent_child_pkg;
1094