[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_INTEGRATION_PUB
Source
1 PACKAGE BODY jtf_rs_integration_pub AS
2 /* $Header: jtfrspxb.pls 120.2 2005/10/18 17:25:14 nsinghai noship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 FUNCTION get_default_sales_group
8 (p_salesrep_id IN NUMBER,
9 p_org_id IN NUMBER,
10 p_date IN DATE)
11 RETURN NUMBER
12 IS
13 l_date DATE;
14 l_group_id NUMBER;
15 BEGIN
16
17 l_date := TRUNC(NVL(p_date, SYSDATE));
18
19 SELECT group_id
20 INTO l_group_id
21 FROM jtf_rs_srp_groups
22 WHERE salesrep_id = p_salesrep_id
23 AND nvl(org_id,-99) = nvl(p_org_id,-99)
24 AND l_date BETWEEN start_date AND end_date;
25
26 RETURN l_group_id;
27
28 -- if too_many_rows or no_data_found then return -1
29 EXCEPTION WHEN OTHERS THEN
30 RETURN -1;
31
32 END get_default_sales_group;
33
34 /* ****************************************************************************
35 Procedure to get resources based on Skill (Platform,Product,Component,ProblemCode,Level combination)
36 Modified By Nishant Singhai 18-Oct-2005 Bug 4674016: Removed literals from
37 dynamic WHERE clause and replaced it
38 with bind variables. No change in
39 logic.
40 ******************************************************************************/
41
42 PROCEDURE get_resources_by_skill
43 (p_category_id IN jtf_rs_resource_skills.category_id%TYPE,
44 p_subcategory IN jtf_rs_resource_skills.subcategory%TYPE,
45 p_product_id IN jtf_rs_resource_skills.product_id%TYPE,
46 p_product_org_id IN jtf_rs_resource_skills.product_org_id%TYPE,
47 p_component_id IN jtf_rs_resource_skills.component_id%TYPE,
48 p_subcomponent_id IN jtf_rs_resource_skills.subcomponent_id%TYPE,
49 p_platform_id IN jtf_rs_resource_skills.platform_id%TYPE,
50 p_platform_org_id IN jtf_rs_resource_skills.platform_org_id%TYPE,
51 p_problem_code IN jtf_rs_resource_skills.problem_code%TYPE,
52 p_skill_level_id IN jtf_rs_resource_skills.skill_level_id%TYPE,
53 x_get_resources_tbl OUT NOCOPY jtf_rs_integration_pub.Resource_table_type,
54 x_return_status OUT NOCOPY VARCHAR2
55 ) IS
56
57 l_get_resources_tbl jtf_rs_integration_pub.Resource_table_type;
58
59 TYPE getResources IS REF CURSOR;
60 get_resources getResources;
61
62 l_resource_id number;
63 l_skill_level_id number;
64 l_query varchar2(1300);
65 l_select varchar2(300);
66 l_where varchar2(1000);
67 i number;
68 l_api_name varchar2(30);
69
70 -- variables for dynamic bind to query
71 TYPE bind_rec_type IS RECORD (bind_value VARCHAR2(2000));
72 TYPE bind_tbl_type IS TABLE OF bind_rec_type INDEX BY binary_integer;
73 bind_table bind_tbl_type;
74 l_bind_counter NUMBER;
75
76 BEGIN
77
78 l_api_name := 'GET_RESOURCES_BY_SKILL';
79 x_return_status := fnd_api.g_ret_sts_success;
80 l_select := 'SELECT distinct resource_id from jtf_rs_resource_skills ';
81 l_where := NULL;
82 i := 1;
83
84 /* BINDVAR_SCAN_IGNORE [57] */
85 if p_category_id is not NULL then
86 if l_where is NULL then
87 --l_where := 'where category_id = '|| p_category_id;
88 l_where := ' where category_id = to_number(:p_category_id) ';
89 else
90 --l_where := l_where || ' and category_id = '|| p_category_id;
91 l_where := l_where || ' and category_id = to_number(:p_category_id) ';
92 end if;
93 bind_table(bind_table.COUNT+1).bind_value := p_category_id;
94 end if;
95
96 if p_subcategory is not NULL then
97 if l_where is NULL then
98 -- l_where := 'where subcategory = '''|| p_subcategory || '''';
99 l_where := ' where subcategory = :p_subcategory ';
100 else
101 -- l_where := l_where || ' and subcategory = '''|| p_subcategory ||'''';
102 l_where := l_where || ' and subcategory = :p_subcategory ';
103 end if;
104 bind_table(bind_table.COUNT+1).bind_value := p_subcategory;
105 end if;
106
107 if p_product_id is not NULL then
108 if l_where is NULL then
109 -- l_where := 'where product_id = '|| p_product_id;
110 l_where := ' where product_id = to_number(:p_product_id) ';
111 -- l_where := l_where || ' and nvl(product_org_id,-99) = '|| nvl(p_product_org_id,-99);
112 l_where := l_where || ' and nvl(product_org_id,-99) = to_number(nvl(:p_product_org_id,-99)) ';
113 else
114 -- l_where := l_where || ' and product_id = '|| p_product_id;
115 l_where := l_where || ' and product_id = to_number(:p_product_id) ';
116 -- l_where := l_where || ' and nvl(product_org_id,-99) = '|| nvl(p_product_org_id,-99);
117 l_where := l_where || ' and nvl(product_org_id,-99) = to_number(nvl(:p_product_org_id,-99)) ';
118 end if;
119 bind_table(bind_table.COUNT+1).bind_value := p_product_id;
120 bind_table(bind_table.COUNT+1).bind_value := p_product_org_id;
121 end if;
122
123 if p_component_id is not NULL then
124 if l_where is NULL then
125 -- l_where := 'where component_id = '|| p_component_id;
126 l_where := ' where component_id = to_number(:p_component_id) ';
127 else
128 -- l_where := l_where || ' and component_id = '|| p_component_id;
129 l_where := l_where || ' and component_id = to_number(:p_component_id) ';
130 end if;
131 bind_table(bind_table.COUNT+1).bind_value := p_component_id;
132 end if;
133
134 if p_subcomponent_id is not NULL then
135 if l_where is NULL then
136 -- l_where := ' where subcomponent_id = '|| p_subcomponent_id;
137 l_where := ' where subcomponent_id = to_number(:p_subcomponent_id) ';
138 else
139 -- l_where := l_where || ' and subcomponent_id = '|| p_subcomponent_id;
140 l_where := l_where || ' and subcomponent_id = to_number(:p_subcomponent_id) ';
141 end if;
142 bind_table(bind_table.COUNT+1).bind_value := p_subcomponent_id;
143 end if;
144
145 if p_platform_id is not NULL then
146 if l_where is NULL then
147 -- l_where := ' where platform_id = '|| p_platform_id;
148 l_where := ' where platform_id = to_number(:p_platform_id) ';
149 -- l_where := l_where || ' and nvl(platform_org_id,-99) = '|| nvl(p_platform_org_id,-99);
150 l_where := l_where || ' and nvl(platform_org_id,-99) = to_number(:p_platform_org_id,-99) ';
151 else
152 -- l_where := l_where || ' and platform_id = '|| p_platform_id;
153 l_where := l_where || ' and platform_id = to_number(:p_platform_id) ';
154 -- l_where := l_where || ' and nvl(platform_org_id,-99) = '|| nvl(p_platform_org_id,-99);
155 l_where := l_where || ' and nvl(platform_org_id,-99) = to_number(nvl(:p_platform_org_id,-99)) ';
156 end if;
157 bind_table(bind_table.COUNT+1).bind_value := p_platform_id;
158 bind_table(bind_table.COUNT+1).bind_value := p_platform_org_id;
159 end if;
160
161 if p_problem_code is not NULL then
162 if l_where is NULL then
163 -- l_where := 'where problem_code = '''|| p_problem_code || '''';
164 l_where := ' where problem_code = :p_problem_code ';
165 else
166 -- l_where := l_where || ' and problem_code = '''|| p_problem_code || '''';
167 l_where := l_where || ' and problem_code = :p_problem_code ';
168 end if;
169 bind_table(bind_table.COUNT+1).bind_value := p_problem_code;
170 end if;
171
172 if p_skill_level_id is not NULL then
173 if l_where is NULL then
174 -- l_where := 'where skill_level_id = '|| p_skill_level_id;
175 l_where := ' where skill_level_id = to_number(:p_skill_level_id) ';
176 else
177 -- l_where := l_where || ' and skill_level_id = '|| p_skill_level_id;
178 l_where := l_where || ' and skill_level_id = to_number(:p_skill_level_id) ';
179 end if;
180 bind_table(bind_table.COUNT+1).bind_value := p_skill_level_id;
181 end if;
182
183 l_query := l_select || l_where;
184
185 -- Commented out (by Nishant on 18-Oct-2005) logic to open cursor
186 -- Replaced below with Bind Variables for all input parameters.
187 /* if (p_subcategory is NOT NULL) and (p_problem_code is NOT NULL) then
188 open get_resources FOR l_query using p_subcategory, p_problem_code;
189 elsif (p_subcategory is NOT NULL) and (p_problem_code is NULL) then
190 open get_resources FOR l_query using p_subcategory;
191 elsif (p_subcategory is NULL) and (p_problem_code is NOT NULL) then
192 open get_resources FOR l_query using p_problem_code;
193 else
194 open get_resources FOR l_query;
195 end if;
196 */
197 l_bind_counter := bind_table.COUNT;
198 IF (l_bind_counter = 0) THEN
199 OPEN get_resources FOR l_query;
200 ELSIF (l_bind_counter = 1) THEN
201 OPEN get_resources FOR l_query using bind_table(1).bind_value;
202 ELSIF (l_bind_counter = 2) THEN
203 OPEN get_resources FOR l_query using bind_table(1).bind_value,
204 bind_table(2).bind_value
205 ;
206 ELSIF (l_bind_counter = 3) THEN
207 OPEN get_resources FOR l_query using bind_table(1).bind_value,
208 bind_table(2).bind_value,
209 bind_table(3).bind_value
210 ;
211 ELSIF (l_bind_counter = 4) THEN
212 OPEN get_resources FOR l_query using bind_table(1).bind_value,
213 bind_table(2).bind_value,
214 bind_table(3).bind_value,
215 bind_table(4).bind_value
216 ;
217 ELSIF (l_bind_counter = 5) THEN
218 OPEN get_resources FOR l_query using bind_table(1).bind_value,
219 bind_table(2).bind_value,
220 bind_table(3).bind_value,
221 bind_table(4).bind_value,
222 bind_table(5).bind_value
223 ;
224 ELSIF (l_bind_counter = 6) THEN
225 OPEN get_resources FOR l_query using bind_table(1).bind_value,
226 bind_table(2).bind_value,
227 bind_table(3).bind_value,
228 bind_table(4).bind_value,
229 bind_table(5).bind_value,
230 bind_table(6).bind_value
231 ;
232 ELSIF (l_bind_counter = 7) THEN
233 OPEN get_resources FOR l_query using bind_table(1).bind_value,
234 bind_table(2).bind_value,
235 bind_table(3).bind_value,
236 bind_table(4).bind_value,
237 bind_table(5).bind_value,
238 bind_table(6).bind_value,
239 bind_table(7).bind_value
240 ;
241 ELSIF (l_bind_counter = 8) THEN
242 OPEN get_resources FOR l_query using bind_table(1).bind_value,
243 bind_table(2).bind_value,
244 bind_table(3).bind_value,
245 bind_table(4).bind_value,
246 bind_table(5).bind_value,
247 bind_table(6).bind_value,
248 bind_table(7).bind_value,
249 bind_table(8).bind_value
250 ;
251 ELSIF (l_bind_counter = 9) THEN
252 OPEN get_resources FOR l_query using bind_table(1).bind_value,
253 bind_table(2).bind_value,
254 bind_table(3).bind_value,
255 bind_table(4).bind_value,
256 bind_table(5).bind_value,
257 bind_table(6).bind_value,
258 bind_table(7).bind_value,
259 bind_table(8).bind_value,
260 bind_table(9).bind_value
261 ;
262 ELSIF (l_bind_counter = 10) THEN
263 OPEN get_resources FOR l_query using bind_table(1).bind_value,
264 bind_table(2).bind_value,
265 bind_table(3).bind_value,
266 bind_table(4).bind_value,
267 bind_table(5).bind_value,
268 bind_table(6).bind_value,
269 bind_table(7).bind_value,
270 bind_table(8).bind_value,
271 bind_table(9).bind_value,
272 bind_table(10).bind_value
273 ;
274
275 END IF;
276 loop
277 fetch get_resources into l_resource_id;
278 exit when get_resources%NOTFOUND;
279 l_get_resources_tbl(i).resource_id := l_resource_id;
280 i := i+1;
281 end loop;
282 close get_resources;
283
284 x_get_resources_tbl := l_get_resources_tbl;
285
286 EXCEPTION
287 WHEN OTHERS
288 THEN
289 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
290 fnd_message.set_token('P_SQLCODE',SQLCODE);
291 fnd_message.set_token('P_SQLERRM',SQLERRM);
292 fnd_message.set_token('P_API_NAME', l_api_name);
293 FND_MSG_PUB.add;
294 x_return_status := fnd_api.g_ret_sts_unexp_error;
295
296 END get_resources_by_skill;
297
298 END jtf_rs_integration_pub;