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