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