1 PACKAGE BODY qa_plans_api AS
2 /* $Header: qltplanb.plb 120.1 2006/01/31 04:51:08 saugupta noship $ */
3
4 --
5 -- Type definition. These are tables used to create internal
6 -- cache to improve performance. Any records retrieved will be
7 -- temporarily saved into these tables.
8 --
9
10 TYPE qa_plans_table IS TABLE OF qa_plans%ROWTYPE INDEX BY BINARY_INTEGER;
11
12 x_qa_plans_array qa_plans_table;
13
14 FUNCTION exists_qa_plans(plan_id IN NUMBER)
15 RETURN BOOLEAN IS
16
17 BEGIN
18
19 RETURN x_qa_plans_array.EXISTS(plan_id);
20
21 END exists_qa_plans;
22
23
24 PROCEDURE fetch_qa_plans (plan_id IN NUMBER) IS
25
26 CURSOR C1 (p_id NUMBER) IS
27 SELECT *
28 FROM qa_plans
29 WHERE plan_id = p_id;
30
31 BEGIN
32
33 IF NOT exists_qa_plans(plan_id) THEN
34 OPEN C1(plan_id);
35 FETCH C1 INTO x_qa_plans_array(plan_id);
36 CLOSE C1;
37 END IF;
38
39 END fetch_qa_plans;
40
41
42 FUNCTION org_id(plan_id IN NUMBER)
43 RETURN NUMBER IS
44
45 BEGIN
46
47 fetch_qa_plans(plan_id);
48 IF NOT exists_qa_plans(plan_id) THEN
49 RETURN NULL;
50 END IF;
51 RETURN x_qa_plans_array(plan_id).organization_id;
52
53 END org_id;
54
55
56 FUNCTION plan_name (plan_id IN NUMBER)
57 RETURN VARCHAR2 IS
58
59 BEGIN
60
61 fetch_qa_plans(plan_id);
62 IF NOT exists_qa_plans(plan_id) THEN
63 RETURN NULL;
64 END IF;
65 RETURN x_qa_plans_array(plan_id).name;
66
67 END plan_name;
68
69
70 FUNCTION valid_plan_id (plan_id IN NUMBER)
71 RETURN BOOLEAN IS
72
73 BEGIN
74
75 fetch_qa_plans(plan_id);
76 IF exists_qa_plans(plan_id) THEN
77 RETURN TRUE;
78 ELSE
79 RETURN FALSE;
80 END IF;
81
82 END valid_plan_id;
83
84
85 FUNCTION plan_id (plan_name IN VARCHAR2)
86 RETURN NUMBER IS
87
88 CURSOR c IS
89 SELECT plan_id
90 FROM qa_plans
91 WHERE name = plan_name;
92
93 l_plan_id NUMBER;
94
95 BEGIN
96
97 OPEN c;
98 FETCH c INTO l_plan_id;
99 CLOSE c;
100
101 RETURN l_plan_id;
102
103 END plan_id;
104
105
106 FUNCTION get_org_id (p_org_code IN VARCHAR2)
107 RETURN NUMBER IS
108
109 -- Bug 4958764. SQL Repository Fix SQL ID: 15008188
110 CURSOR c IS
111 SELECT organization_id
112 FROM mtl_parameters
113 WHERE organization_code = upper(p_org_code);
114 /*
115 SELECT organization_id
116 FROM org_organization_definitions
117 WHERE organization_code = p_org_code;
118 */
119 l_org_id NUMBER;
120
121 BEGIN
122
123 OPEN c;
124 FETCH c INTO l_org_id;
125 CLOSE c;
126
127 RETURN l_org_id;
128
129 END get_org_id;
130
131 FUNCTION get_plan_type (p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
132
133
134 -- anagarwa Tue Jun 8 14:57:05 PDT 2004
135 -- bug 3384507
136 -- to show correct plan type in user's language, we should
137 -- restrict search by user's language
138 CURSOR c IS
139 SELECT meaning
140 FROM fnd_lookup_values
141 WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
142 AND lookup_code = p_lookup_code
143 AND LANGUAGE = userenv('LANG');
144
145 ret_val VARCHAR2(80);
146 BEGIN
147
148 OPEN c;
149 FETCH c INTO ret_val;
150 IF c%NOTFOUND THEN
151 ret_val := '';
152 END IF;
153
154 CLOSE c;
155 RETURN ret_val;
156
157 END get_plan_type;
158
159
160 END qa_plans_api;
[Home] [Help]