[Home] [Help]
PACKAGE BODY: APPS.CS_SUPPORT_UTIL
Source
1 package body CS_SUPPORT_UTIL as
2 /* $Header: cssuutib.pls 115.6 2001/05/15 15:53:08 pkm ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables and types
6 -- ---------------------------------------------------------
7 l_default_date_format CONSTANT VARCHAR2(30) := 'MM/DD/YYYY';
8 /*
9
10 procedure Create_Support_Parties_Link (
11 p_support_id IN NUMBER,
12 p_party_id IN NUMBER,
13 X_Return_Status OUT VARCHAR2)
14
15 is
16 l_support_party_link_id NUMBER;
17 l_number_support_party_link NUMBER;
18 l_current_date DATE :=FND_API.G_MISS_DATE;
19 l_created_by NUMBER :=FND_API.G_MISS_NUM;
20 l_login NUMBER :=FND_API.G_MISS_NUM;
21 l_object_version NUMBER :=1.0;
22 l_security_group_id NUMBER :=0;
23
24 Cursor NumberExist (v_support_id Number, v_party_id Number) is
25 select count(*) from ibu_oss_support_id_parties where support_id = v_support_id and party_id = v_party_id;
26 begin
27 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
28
29 -- Start API Body
30 --Validation
31 if (p_support_id <=0 OR p_party_id <=0) then
32 X_Return_Status := FND_API.G_RET_STS_ERROR;
33 raise FND_API.G_EXC_ERROR;
34 end if;
35
36 --Duplicate Validation
37 open NumberExist (p_support_id, p_party_id);
38 fetch NumberExist into l_number_support_party_link;
39 if (l_number_support_party_link >=1 ) then
40 raise FND_API.G_EXC_ERROR;
41 end if;
42
43 l_current_date := sysdate;
44 l_created_by := fnd_global.user_id;
45 l_login := fnd_global.login_id;
46
47 insert into IBU_OSS_SUPPORT_ID_PARTIES (
48 SUPPORT_ID,
49 PARTY_ID,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SECURITY_GROUP_ID
56 ) values (
57 p_support_id,
58 p_party_id,
59 l_created_by,
60 l_current_date,
61 l_created_by,
62 l_current_date,
63 l_login,
64 l_security_group_id);
65
66
67 end Create_Support_Parties_Link;
68
69 procedure Delete_Support_Parties_Link (
70 p_support_id IN NUMBER,
71 p_party_id IN NUMBER,
72 X_Return_Status OUT VARCHAR2
73 )
74
75 is
76
77 begin
78 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
79 delete from IBU_OSS_SUPPORT_ID_PARTIES where
80 SUPPORT_ID = p_support_id AND
81 PARTY_ID = p_party_id;
82
83 if (sql%notfound) then
84 X_Return_Status := FND_API.G_RET_STS_ERROR;
85 raise no_data_found;
86 end if;
87
88 end Delete_Support_Parties_Link;
89 */
90 /*
91 procedure Create_Support_SR_Link (
92 p_support_id IN NUMBER,
93 p_incident_id IN NUMBER,
94 p_product_version IN VARCHAR2,
95 p_platform_version IN VARCHAR2,
96 p_rdbms_version IN VARCHAR2,
97 X_Return_Status OUT VARCHAR2)
98 is
99
100 l_support_sr_link_id NUMBER;
101 l_number_support_sr_link NUMBER;
102 l_current_date DATE :=FND_API.G_MISS_DATE;
103 l_created_by NUMBER :=FND_API.G_MISS_NUM;
104 l_login NUMBER :=FND_API.G_MISS_NUM;
105 l_object_version NUMBER :=1.0;
106 l_security_group_id NUMBER :=0;
107 Cursor NumberExist (v_support_id Number, v_incident_id Number) is
108 select count(*) from cs_support_id_srs where support_id = v_support_id and incident_id = v_incident_id;
109 begin
110 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
111
112 -- Start API Body
113
114 --Validation
115 if (p_support_id <=0 OR p_incident_id <=0) then
116 X_Return_Status := FND_API.G_RET_STS_ERROR;
117 raise FND_API.G_EXC_ERROR;
118 end if;
119
120 --Duplicate Validation
121 open NumberExist (p_support_id, p_incident_id) ;
122 fetch NumberExist into l_number_support_sr_link;
123 if (l_number_support_sr_link >=1 ) then
124 X_Return_Status := FND_API.G_RET_STS_ERROR;
125 raise FND_API.G_EXC_ERROR;
126 end if;
127
128 l_current_date := sysdate;
129 l_created_by := fnd_global.user_id;
130 l_login := fnd_global.login_id;
131
132 insert into CS_SUPPORT_ID_SRS (
133 SUPPORT_ID,
134 INCIDENT_ID,
135 CREATED_BY,
136 CREATION_DATE,
137 LAST_UPDATED_BY,
138 LAST_UPDATE_DATE,
139 LAST_UPDATE_LOGIN,
140 OBJECT_VERSION_NUMBER,
141 SECURITY_GROUP_ID,
142 PRODUCT_VERSION,
143 PLATFORM_VERSION,
144 DBMS_VERSION ) values (
145 p_support_id,
146 p_incident_id,
147 l_created_by,
148 l_current_date,
149 l_created_by,
150 l_current_date,
151 l_login,
152 l_object_version,
153 l_security_group_id,
154 p_product_version,
155 p_platform_version,
156 p_rdbms_version);
157
158
159
160 end Create_Support_SR_Link;
161
162
163 procedure Delete_Support_SR_Link (
164 p_support_id IN NUMBER,
165 p_incident_id IN NUMBER,
166 X_Return_Status OUT VARCHAR2
167 )
168
169 is
170
171 begin
172 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
173 delete from CS_SUPPORT_ID_SRS where
174 SUPPORT_ID = p_support_id AND
175 INCIDENT_ID = p_incident_id;
176
177 if (sql%notfound) then
178 X_Return_Status := FND_API.G_RET_STS_ERROR;
179 raise no_data_found;
180 end if;
181
182
183 end Delete_Support_SR_Link;
184
185 procedure Create_Support_ID_Level_Link (
186 p_support_id IN NUMBER,
187 p_level_id IN NUMBER,
188 p_start_date IN VARCHAR2,
189 p_end_date IN VARCHAR2,
190 X_support_level_link_id OUT NUMBER,
191 X_Return_Status OUT VARCHAR2)
192 is
193 l_support_level_link_id NUMBER;
194 l_current_date DATE :=FND_API.G_MISS_DATE;
195 l_created_by NUMBER :=FND_API.G_MISS_NUM;
196 l_login NUMBER :=FND_API.G_MISS_NUM;
197 l_object_version NUMBER :=1.0;
198 l_security_group_id NUMBER :=0;
199 l_start_date DATE;
200 l_end_date DATE;
201 l_support_id_level_id NUMBER;
202
203
204 begin
205 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
206
207 -- Start API Body
208 --Validation
209 if (p_support_id <=0 OR p_level_id <=0) then
210 X_Return_Status := FND_API.G_RET_STS_ERROR;
211 raise FND_API.G_EXC_ERROR;
212 end if;
213
214 l_start_date := TO_DATE(p_start_date, l_default_date_format);
215 l_end_date := TO_DATE (p_end_date, l_default_date_format);
216
217 l_current_date := sysdate;
218 l_created_by := fnd_global.user_id;
219 l_login := fnd_global.login_id;
220
221 select CS_SUPPORT_ID_LVLS_S.NEXTVAL into l_support_id_level_id from dual;
222
223 insert into CS_SUPPORT_ID_LVLS (
224 SUP_ID_LVL_ID,
225 SUPPORT_ID,
226 SUPPORT_LVL_ID,
227 START_DATE,
228 END_DATE,
229 CREATED_BY,
230 CREATION_DATE,
231 LAST_UPDATED_BY,
232 LAST_UPDATE_DATE,
233 LAST_UPDATE_LOGIN,
234 OBJECT_VERSION_NUMBER,
235 SECURITY_GROUP_ID
236 ) values (
237 l_support_id_level_id,
238 p_support_id,
239 p_level_id,
240 l_start_date,
241 l_end_date,
242 l_created_by,
243 l_current_date,
244 l_created_by,
245 l_current_date,
246 l_login,
247 l_object_version,
248 l_security_group_id);
249
250
251 X_support_level_link_id := l_support_id_level_id;
252 end Create_Support_ID_Level_Link;
253
254 procedure Delete_Support_ID_Level_Link (
255 p_support_level_link_id IN NUMBER,
256 X_Return_Status OUT VARCHAR2)
257
258
259 is
260
261 begin
262 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
263 delete from CS_SUPPORT_ID_LVLS where
264 sup_id_lvl_id = p_support_level_link_id;
265
266 if (sql%notfound) then
267 X_Return_Status := FND_API.G_RET_STS_ERROR;
268 raise no_data_found;
269 end if;
270
271
272 end Delete_Support_ID_Level_Link;
273
274 procedure Create_Support_Level_Item_Link (
275 p_support_level_link_id IN NUMBER,
276 p_item_id IN NUMBER,
277 X_Return_Status OUT VARCHAR2)
278 is
279
280 l_current_date DATE :=FND_API.G_MISS_DATE;
281 l_created_by NUMBER :=FND_API.G_MISS_NUM;
282 l_login NUMBER :=FND_API.G_MISS_NUM;
283 l_object_version NUMBER :=1.0;
284 l_security_group_id NUMBER :=0;
285 l_inv_org_id NUMBER;
286 l_level_item_number NUMBER;
287
288 Cursor Number_Of_Level_Items (v_support_level_link_id NUMBER, v_inv_item_id NUMBER, v_inv_org_id NUMBER) is
289 select count(*) from CS_SUPPORT_ID_LVL_ITEMS where sup_id_lvl_id = v_support_level_link_id and inv_item_id = v_inv_item_id and inv_organization_id = v_inv_org_id;
290
291 begin
292 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
293 l_inv_org_id := cs_std.get_item_valdn_orgzn_id ();
294
295 -- Start API Body
296
297
298 --Validation
299 if (p_support_level_link_id <=0 OR p_item_id <=0) then
300 X_Return_Status := FND_API.G_RET_STS_ERROR;
301 raise FND_API.G_EXC_ERROR;
302 end if;
303 --duplicate validation
304
305 open Number_Of_Level_Items (p_support_level_link_id, p_item_id, l_inv_org_id );
306 fetch Number_Of_Level_Items into l_level_item_number;
307
308 if (l_level_item_number >=1) then
309 X_Return_Status := FND_API.G_RET_STS_ERROR;
310 raise FND_API.G_EXC_ERROR;
311 end if;
312
313
314 l_current_date := sysdate;
315 l_created_by := fnd_global.user_id;
316 l_login := fnd_global.login_id;
317
318
319 insert into CS_SUPPORT_ID_LVL_ITEMS (
320 SUP_ID_LVL_ID,
321 INV_ITEM_ID ,
322 INV_ORGANIZATION_ID,
323 CREATED_BY,
324 CREATION_DATE,
325 LAST_UPDATED_BY,
326 LAST_UPDATE_DATE,
327 LAST_UPDATE_LOGIN,
328 OBJECT_VERSION_NUMBER,
329 SECURITY_GROUP_ID
330 ) values (
331 p_support_level_link_id,
332 p_item_id,
333 l_inv_org_id ,
334 l_created_by,
335 l_current_date,
336 l_created_by,
337 l_current_date,
338 l_login,
339 l_object_version,
340 l_security_group_id);
341
342
343 end Create_Support_Level_Item_Link;
344
345
346
347 procedure Delete_Support_Level_Item_Link (
348 p_support_level_link_id IN NUMBER,
349 p_item_id IN NUMBER,
350 X_Return_Status OUT VARCHAR2)
351 is
352
353
354 begin
355 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
356 delete from CS_SUPPORT_ID_LVL_ITEMS where
357 sup_id_lvl_id = p_support_leveL_link_id and
358 inv_item_id = p_item_id and
359 inv_organization_id = cs_std.get_item_valdn_orgzn_id ();
360
361 if (sql%notfound) then
362 X_Return_Status := FND_API.G_RET_STS_ERROR;
363 raise no_data_found;
364 end if;
365
366
367 end Delete_Support_Level_Item_Link;
368
369 procedure Delete_All_Level_Item_Link (
370 p_support_leveL_link_id IN NUMBER,
371 X_Return_Status OUT VARCHAR2)
372 is
373
374
375 begin
376 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
377 delete from CS_SUPPORT_ID_LVL_ITEMS where
378 sup_id_lvl_id = p_support_leveL_link_id and
382
379 inv_organization_id = cs_std.get_item_valdn_orgzn_id ();
380
381
383 end Delete_All_Level_Item_Link;
384 */
385 /*
386 procedure get_current_support_id (
387 p_api_version_number IN NUMBER,
388 p_init_msg_list IN VARCHAR2,
389 p_user_id IN NUMBER,
390 p_commit IN VARCHAR,
391 x_support_id OUT NUMBER,
392 x_return_status OUT VARCHAR2,
393 x_msg_count OUT NUMBER,
394 x_msg_data OUT VARCHAR2
395 )
396 as
397 l_profile_name VARCHAR2(60);
398 l_perz_data_name VARCHAR2(360);
399 l_perz_data_type VARCHAR2(30);
400 l_application_id NUMBER;
401 l_pd_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
402 l_support_id NUMBER(15);
403 l_party_id NUMBER(15);
404 out_perz_data_id NUMBER(15);
405 out_perz_data_name VARCHAR2(360);
406 out_perz_data_type VARCHAR2(30);
407 out_perz_data_desc VARCHAR2(240);
408 begin
409 l_support_id := -1;
410 l_profile_name := 'IBU_PERZ_' || to_char(p_user_id);
411 l_perz_data_name := 'IBU_PREFERENCES';
412 l_perz_data_type := 'IBU_PREFERENCES';
413 l_application_id := 672;
414
415 JTF_PERZ_DATA_PUB.Get_Perz_Data
416 (
417 p_api_version_number => 1.0,
418 p_init_msg_list => p_init_msg_list,
419 p_application_id => l_application_id,
420 p_profile_id => null,
421 p_profile_name => l_profile_name,
422 p_perz_data_id => null,
423 p_perz_data_name => l_perz_data_name,
424 p_perz_data_type => null,
425 x_perz_data_id => out_perz_data_id,
426 x_perz_data_name => out_perz_data_name,
427 x_perz_data_type => out_perz_data_type,
428 x_perz_data_desc => out_perz_data_desc,
429 x_data_attrib_tbl => l_pd_attrib_tbl,
430 x_return_status => x_return_status,
431 x_msg_count => x_msg_count,
432 x_msg_data => x_msg_data
433 );
434
435 if (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
436 for l_curr_row in 1..l_pd_attrib_tbl.count
437 loop
438 if l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_NAME = 'support_id' then
439 l_support_id := l_pd_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE;
440 end if;
441 end loop;
442 end if;
443
444 x_support_id := l_support_id;
445
446 exception
447 WHEN NO_DATA_FOUND THEN
448 x_support_id := -1;
449 x_return_status := FND_API.G_RET_STS_ERROR;
450
451 WHEN OTHERS THEN
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
453 FND_MSG_PUB.Count_And_Get
454 (p_count => x_msg_count ,
455 p_data => x_msg_data
456 );
457 end get_current_support_id;
458
459 procedure is_csi_enabled(
460 p_api_version_number IN NUMBER,
461 p_init_msg_list IN VARCHAR2,
462 p_commit IN VARCHAR,
463 x_return_status OUT VARCHAR2,
464 x_msg_count OUT NUMBER,
465 x_msg_data OUT VARCHAR2
466 )
467 is
468 l_user_id NUMBER;
469 l_resp_id NUMBER;
470 l_app_id NUMBER;
471 l_enable_flag VARCHAR2(1);
472 begin
473 x_return_status := FND_PROFILE.VALUE ('CS_CSI_ENABLED');
474
475 exception
476 when others then
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 end is_csi_enabled;
479 */
480
481 end CS_SUPPORT_UTIL;