[Home] [Help]
PACKAGE BODY: APPS.CS_CONT_GET_DETAILS_PVT
Source
1 PACKAGE BODY CS_CONT_GET_DETAILS_PVT AS
2 /* $Header: csvscgdb.pls 120.0.12010000.2 2008/08/26 10:58:05 isugavan ship $ */
3
4
5 /*********************************************************************
6
7 API : Get_contract_lines
8 Version :
9 Type : Private
10 Function : Get the service coverage level lines based on the
11 input values given by the user
12
13 Parameters :
14
15 IN :
16 p_api_version NUMBER Required
17 p_init_msg_list VARCHAR2 Required
18 P_CONTRACT_NUMBER VARCHAR2
19 P_SERVICE_LINE_ID NUMBER
20 P_CUSTOMER_ID NUMBER
21 P_SITE_ID NUMBER
22 P_CUSTOMER_ACCOUNT_ID NUMBER
23 P_SYSTEM_ID NUMBER
24 P_INVENTORY_ITEM_ID NUMBER
25 P_CUSTOMER_PRODUCT_ID NUMBER
26 P_REQUEST_DATE DATE Required
27 P_BUSINESS_PROCESS_ID IN NUMBER DEFAULT NULL,
28 P_SEVERITY_ID IN NUMBER DEFAULT NULL,
29 P_TIME_ZONE_ID IN NUMBER DEFAULT NULL,
30 P_CALC_RESPTIME_FLAG IN VARCHAR2 DEFAULT NULL,
31 P_VALIDATE_FLAG VARCHAR2 Required
32
33 OUT :
34
35 X_ ENT_CONTRACTS OUT TABLE OF RECORDS
36 x_return_status VARCHAR2
37 x_msg_count NUMBER
38 x_msg_data VARCHAR2
39
40 *********************************************************************/
41
42
43 PROCEDURE GET_CONTRACT_LINES( P_API_VERSION IN NUMBER ,
44 P_INIT_MSG_LIST IN VARCHAR2,
45 P_CONTRACT_NUMBER IN OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE,
46 P_SERVICE_LINE_ID IN NUMBER,
47 P_CUSTOMER_ID IN NUMBER,
48 P_SITE_ID IN NUMBER,
49 P_CUSTOMER_ACCOUNT_ID IN NUMBER,
50 P_SYSTEM_ID IN NUMBER,
51 P_INVENTORY_ITEM_ID IN NUMBER,
52 P_CUSTOMER_PRODUCT_ID IN NUMBER,
53 P_REQUEST_DATE IN DATE,
54 P_BUSINESS_PROCESS_ID IN NUMBER DEFAULT NULL,
55 P_SEVERITY_ID IN NUMBER DEFAULT NULL,
56 P_TIME_ZONE_ID IN NUMBER DEFAULT NULL,
57 P_CALC_RESPTIME_FLAG IN VARCHAR2 DEFAULT NULL,
58 P_VALIDATE_FLAG IN VARCHAR2,
59 P_DATES_IN_INPUT_TZ IN VARCHAR2 DEFAULT 'N',
60 P_INCIDENT_DATE IN DATE DEFAULT NULL,
61 X_ENT_CONTRACTS OUT NOCOPY ENT_CONTRACT_TAB,
62 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
63 X_MSG_COUNT OUT NOCOPY NUMBER,
64 X_MSG_DATA OUT NOCOPY VARCHAR2)
65
66
67 IS
68
69 l_return_status varchar2(1);
70 l_api_name varchar2(30) := 'GET_CONTRACT_LINES';
71 --l_inp_rec OKS_ENTITLEMENTS_PUB.inp_cont_rec;
72 l_inp_rec OKS_ENTITLEMENTS_PUB.get_contin_rec;
73 l_org_id number;
74 l_organization_id number := NULL;
75 BEGIN
76
77 SAVEPOINT Get_contract_lines;
78
79 -- Since the Entitlements API expects input parameters as a record
80 -- copy all the input parameter values into a record before passing
81 -- it to the API
82
83 l_inp_rec.contract_number := p_contract_number;
84 --l_inp_rec.coverage_level_line_id := p_coverage_level_line_id;
85 l_inp_rec.service_line_id := p_service_line_id;
86 l_inp_rec.party_id := p_customer_id;
87 l_inp_rec.site_id := p_site_id;
88 l_inp_rec.cust_acct_id := p_customer_account_id;
89 l_inp_rec.system_id := p_system_id;
90 l_inp_rec.item_id := p_inventory_item_id;
91 l_inp_rec.product_id := p_customer_product_id;
92 l_inp_rec.request_date := p_request_date;
93 l_inp_rec.business_process_id := p_business_process_id;
94 l_inp_rec.severity_id := p_severity_id;
95 l_inp_rec.time_zone_id := p_time_zone_id;
96 l_inp_rec.calc_resptime_flag := nvl(p_calc_resptime_flag,'N');
97 l_inp_rec.validate_flag := p_validate_flag;
98 l_inp_rec.dates_in_input_tz := p_dates_in_input_tz;
99 l_inp_rec.incident_date := p_incident_date ;
100 --Bug 7132754
101 /*
102 The value set in this profile option will be passed to the contracts API to
103 return the contract lines sorted based on Reaction Rime(RCN)/ Resolution Time(RSN)/
104 Importance Level(COVTYP_IMP).
105 Default value: Resolution time (to be backward compatible)
106 */
107
108 l_inp_rec.sort_key:= fnd_profile.value('CS_SR_CONTRACT_SORT_ORDER');
109 if l_inp_rec.sort_key is null then
110 l_inp_rec.sort_key:= 'RSN';
111 end if;
112
113 -- Multi-org change. Get Org_id for Customer Product Id passed
114 -- to the API if it is not null. Get Organization Id from
115 -- the profile MO_OPERATING_UNIT
116 /*******************
117 This SQL is not needed as we are not setting Organization Context.The contracts
118 should be retrieved irrespective of the organization.
119
120 if p_customer_product_id is not null then
121 select org_id into l_org_id
122 from cs_customer_products_all
123 where customer_product_id = p_customer_product_id;
124 else
125 l_org_id := NULL;
126 end if;
127
128 **************/
129 -- fnd_profile.get('MO_OPERATING_UNIT',l_organization_id);
130
131 -- Set the Multi-org context before calling Entitlements API
132 -- commenting the org context as per instructions from contracts
133 -- 12 30 2000
134 --okc_context.set_okc_org_context(l_org_id, l_organization_id);
135
136 -- If the validate_flag is 'Y' then only the valid contracts as of
137 -- 'request_date' is returned. If the validate_flag is 'N' then
138 -- all the contract lines - valid and invalid- are returned.
139
140 OKS_ENTITLEMENTS_PUB.GET_CONTRACTS( p_api_version => p_api_version,
141 p_init_msg_list => p_init_msg_list,
142 p_inp_rec => l_inp_rec,
143 x_return_status => l_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data,
146 x_ent_contracts => x_ent_contracts);
147
148 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
149 RAISE FND_API.G_EXC_ERROR ;
150 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
152 END IF ;
153 x_return_status := l_return_status;
154
155 EXCEPTION
156
157 WHEN FND_API.G_EXC_ERROR THEN
158 ROLLBACK TO Get_contract_lines;
159 x_return_status := FND_API.G_RET_STS_ERROR ;
160 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
161 p_data => x_msg_data ,
162 p_encoded => fnd_api.g_false );
163
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 ROLLBACK TO Get_contract_lines;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
167 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
168 p_data => x_msg_data ,
169 p_encoded => fnd_api.g_false );
170
171 WHEN OTHERS THEN
172 ROLLBACK TO Get_contract_lines;
173 x_return_status := FND_API.G_RET_STS_unexp_error ;
174 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
175 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
176 END IF;
177 fnd_msg_pub.count_and_get ( p_count =>x_msg_count ,
178 p_data => x_msg_data ,
179 p_encoded => fnd_api.g_false );
180
181
182 END;
183
184 /*********************************************************************
185
186 API : Get_Reaction_time
187 Version :
188 Type : Private
189 Function : Get the time within which the Customer call should
190 be returned.
191
192 Parameters :
193
194 IN :
195 p_api_version NUMBER Required
196 p_init_msg_list VARCHAR2 Required
197 P_START_TZ_ID NUMBER Required
198 P_SR_SEVERITY NUMBER Required
199 P_BUSINESS_PROCESS_ID NUMBER Required
200 P_REQUEST_DATE DATE Required
201 P_SERVICE_LINE_ID NUMBER Required
202
203 OUT :
204
205 X_ REACT_BY_DATE DATE
206 x_return_status VARCHAR2
207 x_msg_count NUMBER
208 x_msg_data VARCHAR2
209
210 *********************************************************************/
211
212
213 PROCEDURE GET_REACTION_TIME( P_API_VERSION IN NUMBER ,
214 P_INIT_MSG_LIST IN VARCHAR2,
215 P_START_TZ_ID IN NUMBER,
216 P_SR_SEVERITY IN NUMBER,
217 P_BUSINESS_PROCESS_ID IN NUMBER,
218 P_REQUEST_DATE IN DATE,
219 P_DATES_IN_INPUT_TZ IN VARCHAR2 DEFAULT 'N',
220 P_SERVICE_LINE_ID IN NUMBER,
221 X_REACT_BY_DATE OUT NOCOPY DATE,
222 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
223 X_MSG_COUNT OUT NOCOPY NUMBER,
224 X_MSG_DATA OUT NOCOPY VARCHAR2)
225
226 IS
227
228 l_api_version number := 1.0;
229 l_return_status varchar2(1);
230 l_msg_count number;
231 l_msg_data varchar2(100);
232 l_react_within number;
233 l_react_tuom varchar2(64);
234 l_react_by_date date;
235 l_api_name varchar2(30) := 'GET_REACTION_TIME';
236
237
238
239 BEGIN
240
241 SAVEPOINT check_reaction_times;
242
243 -- Reaction time for a coverage line is calculated based on the
244 -- severity of the SR, business process id, sr time zone and the
245 -- date on which the SR is created. l_react_by_date is based on the
246 -- SR timezone.
247
248 OKS_ENTITLEMENTS_PUB.CHECK_REACTION_TIMES(p_api_version,
249 p_init_msg_list,
250 p_business_process_id,
251 p_request_date,
252 p_sr_severity,
253 p_start_tz_id,
254 p_dates_in_input_tz,
255 p_service_line_id,
256 l_return_status,
257 l_msg_count,
258 l_msg_data,
259 l_react_within,
260 l_react_tuom,
261 l_react_by_date);
262
263
264 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
265 RAISE FND_API.G_EXC_ERROR ;
266 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
268 END IF ;
269
270 x_react_by_date := l_react_by_date;
271 x_return_status := l_return_status;
272 x_msg_count := l_msg_count;
273 x_msg_data := l_msg_data;
274
275 EXCEPTION
276
277 WHEN FND_API.G_EXC_ERROR THEN
278 ROLLBACK TO check_reaction_times;
279 x_return_status := FND_API.G_RET_STS_ERROR ;
280 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
281 p_data => x_msg_data ,
282 p_encoded => fnd_api.g_false );
283
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 ROLLBACK TO check_reaction_times;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
287 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
288 p_data => x_msg_data ,
289 p_encoded => fnd_api.g_false );
290
291 WHEN OTHERS THEN
292 ROLLBACK TO check_reaction_times;
293 x_return_status := FND_API.G_RET_STS_unexp_error ;
294 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
295 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
296 END IF;
297 fnd_msg_pub.count_and_get ( p_count =>x_msg_count ,
298 p_data => x_msg_data ,
299 p_encoded => fnd_api.g_false );
300
301
302 END;
303
304
305
306 /*********************************************************************
307
308 API : Validate_contact
309 Version :
310 Type : Private
311 Function : Validate a Contact against the Contact tied to a
312 Coverage level Line
313
314 Parameters :
315
316 IN :
317 p_api_version NUMBER Required
318 p_init_msg_list VARCHAR2 Required
319 P_CONTACT_ID NUMBER Required
320 P_CONTRACT_ID NUMBER Required
321 P_SERVICE_LINE_ID NUMBER Required
322
323 OUT :
324
325 X_VALID_CONTACT VARCHAR2
326 x_return_status VARCHAR2
327 x_msg_count NUMBER
328 x_msg_data VARCHAR2
329
330 *********************************************************************/
331
332 PROCEDURE VALIDATE_CONTACT ( P_API_VERSION IN NUMBER,
333 P_INIT_MSG_LIST IN VARCHAR2,
334 P_CONTACT_ID IN NUMBER,
335 P_CONTRACT_ID IN NUMBER,
336 P_SERVICE_LINE_ID IN NUMBER,
337 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
338 X_MSG_COUNT OUT NOCOPY NUMBER,
339 X_MSG_DATA OUT NOCOPY VARCHAR2,
340 X_VALID_CONTACT OUT NOCOPY VARCHAR2)
341
342 IS
343
344
345 l_api_version number := 1.0;
346 l_return_status varchar2(1);
347 l_msg_count number;
348 l_msg_data varchar2(100);
349 l_valid_contact varchar2(1) :='N';
350 l_rec_count number :=1;
351 l_ent_contacts Ent_contact_tab;
352 l_contact_id number;
353 l_api_name varchar2(30) := 'VALIDATE_CONTACT';
354
355 BEGIN
356
357 l_contact_id := p_contact_id;
358
359 SAVEPOINT validate_contact;
360
361 -- This API returns a list of valid contacts tied to the coverage
362 -- level line id.
363
367 p_contract_line_id =>p_service_line_id,
364 OKS_ENTITLEMENTS_PUB.GET_CONTACTS (p_api_version => p_api_version,
365 p_init_msg_list =>p_init_msg_list,
366 p_contract_id => p_contract_id,
368 x_return_status => l_return_status,
369 x_msg_count => l_msg_count,
370 x_msg_data =>l_msg_data,
371 x_ent_contacts=>l_ent_contacts);
372
373
374 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
375 RAISE FND_API.G_EXC_ERROR ;
376 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
378 END IF ;
379
380 -- If p_contact_id is one of the valid contacts then l_valid_contact
381 -- flag is set to 'Y' else 'N' is returned.
382
383 WHILE l_ent_contacts.exists(l_rec_count)
384 LOOP
385 IF (l_ent_contacts(l_rec_count).contact_id = l_contact_id) THEN
386 l_valid_contact := 'Y';
387 exit;
388 ELSE
389 l_rec_count := l_rec_count + 1;
390 END IF;
391 END LOOP;
392
393 x_valid_contact := l_valid_contact;
394
395 EXCEPTION
396
397 WHEN FND_API.G_EXC_ERROR THEN
398 ROLLBACK TO validate_contact;
399 x_return_status := FND_API.G_RET_STS_ERROR ;
400 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
401 p_data => x_msg_data ,
402 p_encoded => fnd_api.g_false );
403
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 ROLLBACK TO validate_contact;
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
407 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
408 p_data => x_msg_data ,
409 p_encoded => fnd_api.g_false );
410
411 WHEN OTHERS THEN
412 ROLLBACK TO validate_contact;
413 x_return_status := FND_API.G_RET_STS_unexp_error ;
414 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
415 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
416 END IF;
417 fnd_msg_pub.count_and_get ( p_count =>x_msg_count ,
418 p_data => x_msg_data ,
419 p_encoded => fnd_api.g_false );
420
421 END;
422
423 /*********************************************************************
424
425 API : Validate_contact
426 Version :
427 Type : Private
428 Function : Validate Contacts against the Contacts tied to a
429 Coverage level Line
430
431 Parameters :
432
433 IN :
434 p_api_version NUMBER Required
435 p_init_msg_list VARCHAR2 Required
436 P_CONTRACT_ID NUMBER Required
437 P_SERVICE_LINE_ID NUMBER Required
438
439 OUT :
440
441 X_VALID_CONTACT VARCHAR2
442 x_return_status VARCHAR2
443 x_msg_count NUMBER
444 x_msg_data VARCHAR2
445 IN OUT :
446
447 P_CONTACT_TAB ENT_CONTACT_TAB Required
448 *********************************************************************/
449
450 PROCEDURE VALIDATE_CONTACT ( P_API_VERSION IN NUMBER,
451 P_INIT_MSG_LIST IN VARCHAR2,
452 P_CONTACT_TAB IN OUT NOCOPY INC_CONTACT_TAB,
453 P_CONTRACT_ID IN NUMBER,
454 P_SERVICE_LINE_ID IN NUMBER,
455 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
456 X_MSG_COUNT OUT NOCOPY NUMBER,
457 X_MSG_DATA OUT NOCOPY VARCHAR2)
458
459 IS
460
461
462 l_api_version number := 1.0;
463 l_return_status varchar2(1);
464 l_msg_count number;
465 l_msg_data varchar2(100);
466 l_valid_contact varchar2(1) :='N';
467 l_rec_count number;
468 l_cont_count number;
469 l_ent_contacts Ent_contact_tab;
470 l_contact_id number;
471 l_api_name varchar2(30) := 'VALIDATE_CONTACT';
472
473 BEGIN
474
475
476 SAVEPOINT validate_contact;
477
478 -- This API returns a list of valid contacts tied to the coverage
479 -- level line id.
480
481 OKS_ENTITLEMENTS_PUB.GET_CONTACTS (p_api_version => p_api_version,
482 p_init_msg_list =>p_init_msg_list,
483 p_contract_id => p_contract_id,
484 p_contract_line_id =>p_service_line_id,
485 x_return_status => l_return_status,
486 x_msg_count => l_msg_count,
487 x_msg_data =>l_msg_data,
488 x_ent_contacts=>l_ent_contacts);
489
490
491 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
492 RAISE FND_API.G_EXC_ERROR ;
493 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
495 END IF ;
496
497 -- If p_contact_id is one of the valid contacts then l_valid_contact
498 -- flag is set to 'Y' else 'N' is returned.
499 l_cont_count := p_contact_tab.FIRST;
500
501 WHILE l_cont_count is not null
502 LOOP
503 l_contact_id := p_contact_tab(l_cont_count).contact_id;
504 l_rec_count := l_ent_contacts.FIRST;
505 WHILE l_rec_count is not null
506 LOOP
507 IF (l_ent_contacts(l_rec_count).contact_id = l_contact_id) THEN
508 p_contact_tab(l_cont_count).valid_contact := 'Y';
509 exit;
510 ELSE
511 l_rec_count := l_ent_contacts.NEXT(l_rec_count);
512 END IF;
513 END LOOP;
514 IF l_rec_count is null THEN
515 p_contact_tab(l_cont_count).valid_contact := 'N';
516 END IF;
517 l_cont_count := p_contact_tab.NEXT(l_cont_count);
518
519 END LOOP;
520
521
522 EXCEPTION
523
524 WHEN FND_API.G_EXC_ERROR THEN
525 ROLLBACK TO validate_contact;
526 x_return_status := FND_API.G_RET_STS_ERROR ;
527 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
528 p_data => x_msg_data ,
529 p_encoded => fnd_api.g_false );
530
531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
532 ROLLBACK TO validate_contact;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
534 FND_MSG_PUB.COUNT_AND_GET ( p_count =>x_msg_count ,
535 p_data => x_msg_data ,
536 p_encoded => fnd_api.g_false );
537
538 WHEN OTHERS THEN
539 ROLLBACK TO validate_contact;
540 x_return_status := FND_API.G_RET_STS_unexp_error ;
541 IF fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error ) THEN
542 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name ) ;
543 END IF;
544 fnd_msg_pub.count_and_get ( p_count =>x_msg_count ,
545 p_data => x_msg_data ,
546 p_encoded => fnd_api.g_false );
547
548 END;
549
550 END CS_CONT_GET_DETAILS_PVT;