1 PACKAGE BODY CSD_REPAIR_TASK_UTIL as
2 /* $Header: csdvrtub.pls 120.1 2005/08/09 16:27:03 sangigup noship $ csdtactb.pls */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIR_TASK_UTIL';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrtub.pls';
6 l_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
7
8 -- Global variable for storing the debug level
9 G_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10
11
12 procedure string_to_array( p_x_string IN OUT NOCOPY VARCHAR2, x_result_ids OUT NOCOPY NUMBER_ARRAY_TYPE) IS
13
14 pos NUMBER;
15 total_length NUMBER := length(p_x_string);
16 i NUMBER := 1;
17 --p_x_string = '123,456,789'
18
19 BEGIN
20 --i := l_result.FIRST;
21 while length(p_x_string) >0 LOOP
22 --dbms_output.put_line('inside ');
23 pos := INSTR(p_x_string, ',', 1, 1); -- first appearance of , in the string
24 if pos =0 then
25 x_result_ids (i) := p_x_string ;
26 exit;
27 else
28 --dbms_output.put_line('pos:= '|| pos);
29 x_result_ids(i) := to_number(substr(p_x_string, 1, pos-1)); -- will put 123 in the array- extracts pos-1 characters
30 --dbms_output.put_line('i '|| x_plan_ids(i));
31 p_x_string := substr(p_x_string,pos +1, length(p_x_string)-pos);
32 --dbms_output.put_line('p_x_string := '|| p_x_string);
33 i := i + 1;
34 --i := l_result.NEXT(i);
35 end if;
36
37 ENd LOOP;
38
39 END; --procedure string_to_array
40
41 --function to get the plan name from the plan id
42 function get_plan_name (p_plan_id IN NUMBER ) return VARCHAR2 IS
43 l_dummy varchar2(30);
44 cursor c_get_plan_name ( p_plan_id NUMBER) IS
45 select name
46 from qa_plans
47 where plan_id = p_plan_id;
48 BEGIN
49 OPEN c_get_plan_name(p_plan_id);
50 FETCH c_get_plan_name into l_dummy;
51 CLOSE c_get_plan_name;
52
53 return l_dummy;
54
55 END get_plan_name;
56
57 --procedure to return plan ids for the collection ids. This will return all the plan ids
58 -- for which data was collected for a given collection id.
59 PROCEDURE get_planIds_for_CIds(p_local_cids_array IN NUMBER_ARRAY_TYPE,
60 x_local_plan_ids_array out NOCOPY NUMBER_ARRAY_TYPE) IS
61
62 l_cid_list varchar2(10000);
63 i NUMBER;
64
65 CURSOR c_plan_ids (p_local_cids VARCHAR2) IS
66 SELECT distinct plan_id
67 FROM qa_results
68 WHERE collection_id in (p_local_cids);
69
70
71 BEGIN
72
73 -- convert array to comma delimited string
74 for i in 1.. p_local_cids_array.count LOOP
75 l_cid_list := l_cid_list ||',' || p_local_cids_array(i);
76 END LOOP;
77
78 IF l_cid_list IS NOT NULL THEN
79 l_cid_list := substr(l_cid_list, 2);
80 END IF;
81
82 FOR c1 in c_plan_ids (l_cid_list)
83 LOOP
84 i:= i+1;
85 x_local_plan_ids_array(i) := c1.plan_id;
86 END LOOP;
87
88
89
90 END get_planIds_for_CIds;
91
92
93
94 End CSD_REPAIR_TASK_UTIL;
[Home] [Help]