[Home] [Help]
PACKAGE BODY: APPS.OKS_AUTH_UTIL_PUB
Source
1 PACKAGE BODY OKS_AUTH_UTIL_PUB AS
2 /* $Header: OKSPAUTB.pls 120.6 2006/06/09 05:20:05 gchadha noship $ */
3
4 PROCEDURE GetSelections_prod(p_api_version IN NUMBER
5 , p_init_msg_list IN VARCHAR2
6 , p_clvl_filter_rec IN clvl_filter_rec
7 , x_return_status OUT NOCOPY VARCHAR2
8 , x_msg_count OUT NOCOPY NUMBER
9 , x_msg_data OUT NOCOPY VARCHAR2
10 , x_prod_selections_tbl OUT NOCOPY prod_selections_tbl)
11 IS
12 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
13 l_api_name CONSTANT VARCHAR2(30) := 'GetSelections_prod';
14 BEGIN
15 l_return_status := OKC_API.START_ACTIVITY
16 (l_api_name
17 , p_init_msg_list
18 , '_PUB'
19 , x_return_status);
20 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
21 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
22 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
23 RAISE OKC_API.G_EXCEPTION_ERROR;
24 END IF;
25
26 OKS_AUTH_UTIL_PVT.GetSelections_prod
27 (p_api_version => p_api_version
28 , p_init_msg_list => p_init_msg_list
29 , p_clvl_filter_rec => p_clvl_filter_rec
30 , x_return_status => l_return_status
31 , x_msg_count => x_msg_count
32 , x_msg_data => x_msg_data
33 , x_prod_selections_tbl => x_prod_selections_tbl);
34
35 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
36 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
37 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
38 RAISE OKC_API.G_EXCEPTION_ERROR;
39 END IF;
40
41 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
42
43 x_return_status := l_return_status;
44 EXCEPTION
45 WHEN OKC_API.G_EXCEPTION_ERROR THEN
46 x_return_status := OKC_API.HANDLE_EXCEPTIONS
47 (l_api_name,
48 G_PKG_NAME,
49 'OKC_API.G_RET_STS_ERROR',
50 x_msg_count,
51 x_msg_data,
52 '_PUB');
53 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
54 x_return_status := OKC_API.HANDLE_EXCEPTIONS
55 (l_api_name,
56 G_PKG_NAME,
57 'OKC_API.G_RET_STS_UNEXP_ERROR',
58 x_msg_count,
59 x_msg_data,
60 '_PUB');
61 WHEN OTHERS THEN
62 x_return_status := OKC_API.HANDLE_EXCEPTIONS
63 (l_api_name,
64 G_PKG_NAME,
65 'OTHERS',
66 x_msg_count,
67 x_msg_data,
68 '_PUB');
69 END GetSelections_prod;
70
71 PROCEDURE GetSelections_other(p_api_version IN NUMBER
72 , p_init_msg_list IN VARCHAR2
73 , p_clvl_filter_rec IN clvl_filter_rec
74 , x_return_status OUT NOCOPY VARCHAR2
75 , x_msg_count OUT NOCOPY NUMBER
76 , x_msg_data OUT NOCOPY VARCHAR2
77 , x_clvl_selections_tbl OUT NOCOPY clvl_selections_tbl)
78 IS
79 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
80 l_api_name CONSTANT VARCHAR2(30) := 'GetSelections_prod';
81 BEGIN
82 l_return_status := OKC_API.START_ACTIVITY
83 (l_api_name
84 , p_init_msg_list
85 , '_PUB'
86 , x_return_status);
87 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
88 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
89 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
90 RAISE OKC_API.G_EXCEPTION_ERROR;
91 END IF;
92
93 OKS_AUTH_UTIL_PVT.GetSelections_other
94 (p_api_version => p_api_version
95 , p_init_msg_list => p_init_msg_list
96 , p_clvl_filter_rec => p_clvl_filter_rec
97 , x_return_status => l_return_status
98 , x_msg_count => x_msg_count
99 , x_msg_data => x_msg_data
100 , x_clvl_selections_tbl => x_clvl_selections_tbl);
101
102 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
103 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
104 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
105 RAISE OKC_API.G_EXCEPTION_ERROR;
106 END IF;
107
108 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
109
110 x_return_status := l_return_status;
111 EXCEPTION
112 WHEN OKC_API.G_EXCEPTION_ERROR THEN
113 x_return_status := OKC_API.HANDLE_EXCEPTIONS
114 (l_api_name,
115 G_PKG_NAME,
116 'OKC_API.G_RET_STS_ERROR',
117 x_msg_count,
118 x_msg_data,
119 '_PUB');
120 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
121 x_return_status := OKC_API.HANDLE_EXCEPTIONS
122 (l_api_name,
123 G_PKG_NAME,
124 'OKC_API.G_RET_STS_UNEXP_ERROR',
125 x_msg_count,
126 x_msg_data,
127 '_PUB');
128 WHEN OTHERS THEN
129 x_return_status := OKC_API.HANDLE_EXCEPTIONS
130 (l_api_name,
131 G_PKG_NAME,
132 'OTHERS',
133 x_msg_count,
134 x_msg_data,
135 '_PUB');
136 END GetSelections_other;
137
138 PROCEDURE CopyService(p_api_version IN NUMBER
139 , p_init_msg_list IN VARCHAR2
140 , p_source_rec IN copy_source_rec
141 , p_target_tbl IN copy_target_tbl
142 , x_return_status OUT NOCOPY VARCHAR2
143 , x_msg_count OUT NOCOPY NUMBER
144 , x_msg_data OUT NOCOPY VARCHAR2
145 , p_change_status IN VARCHAR2) -- Added an additional flag parameter, p_change_status,
146 -- to decide whether to allow change of status of sublines
147 -- of the topline during update service
148 IS
149 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
150 l_api_name CONSTANT VARCHAR2(30) := 'CopyService';
151 BEGIN
152 l_return_status := OKC_API.START_ACTIVITY
153 (l_api_name
154 , p_init_msg_list
155 , '_PUB'
156 , x_return_status);
157 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
159 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
160 RAISE OKC_API.G_EXCEPTION_ERROR;
161 END IF;
162
163 OKS_AUTH_UTIL_PVT.CopyService
164 (p_api_version => p_api_version
165 , p_init_msg_list => p_init_msg_list
166 , p_source_rec => p_source_rec
167 , p_target_tbl => p_target_tbl
168 , x_return_status => l_return_status
169 , x_msg_count => x_msg_count
170 , x_msg_data => x_msg_data
171 , p_change_status => p_change_status); -- LLC Added an additional flag parameter, p_change_status,
172 -- to decide whether to allow change of status of sublines
173 -- of the topline during update service
174
175 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
177 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
178 RAISE OKC_API.G_EXCEPTION_ERROR;
179 END IF;
180
181 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
182
183 x_return_status := l_return_status;
184
185 EXCEPTION
186 WHEN OKC_API.G_EXCEPTION_ERROR THEN
187 x_return_status := OKC_API.HANDLE_EXCEPTIONS
188 (l_api_name,
189 G_PKG_NAME,
190 'OKC_API.G_RET_STS_ERROR',
191 x_msg_count,
192 x_msg_data,
193 '_PUB');
194 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
195 x_return_status := OKC_API.HANDLE_EXCEPTIONS
196 (l_api_name,
197 G_PKG_NAME,
198 'OKC_API.G_RET_STS_UNEXP_ERROR',
199 x_msg_count,
200 x_msg_data,
201 '_PUB');
202 WHEN OTHERS THEN
203 x_return_status := OKC_API.HANDLE_EXCEPTIONS
204 (l_api_name,
205 G_PKG_NAME,
206 'OTHERS',
207 x_msg_count,
208 x_msg_data,
209 '_PUB');
210 END CopyService;
211
212 --Added for Bug#2419645, 07/18/2002, Sudam.
213 PROCEDURE COPY_PARAMETER(
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
216 p_in_parameter_record IN in_parameter_record,
217 x_cur_rec OUT NOCOPY cur_rec,
218 x_hdr_cur_rec OUT NOCOPY hdr_cur_rec,
219 x_line_cur_rec OUT NOCOPY line_cur_rec,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2
223 )
224 IS
225 CURSOR hdr_cur(p_chr_id IN NUMBER) IS
226 SELECT short_description,
227 contract_number,
228 contract_number_modifier,
229 start_date,
230 end_date,
231 currency_code
232 FROM OKC_K_HEADERS_V
233 WHERE ID = p_chr_id;
234
235 CURSOR line_cur(p_line_id IN NUMBER) IS
236 SELECT line_number,
237 start_date,
238 end_date,
239 cognomen,
240 lse_id
241 FROM OKC_K_LINES_V
242 WHERE ID = p_line_id;
243
244 CURSOR prod_name_desc_cur(p_line_id IN NUMBER,
245 p_organization_id IN NUMBER,
246 p_inventory_item_id IN NUMBER) IS
247 SELECT description,
248 segment1,
249 concatenated_segments
250
251 FROM mtl_system_items_kfv
252
253 WHERE inventory_item_id = p_inventory_item_id AND
254 organization_id = p_organization_id ;
255
256 l_chr_id NUMBER;
257 p_chr_id NUMBER;
258 l_line_id NUMBER;
259 l_organization_id NUMBER;
260 l_inventory_item_id NUMBER;
261 p_inventory_item_id NUMBER;
262 p_line_id NUMBER;
263 p_organization_id NUMBER;
264 l_name VARCHAR2(40);
265 l_description VARCHAR2(240);
266 l_line_reference VARCHAR2(300);
267 l_short_description VARCHAR2(600);
268 l_cur_rec cur_rec;
269 l_hdr_cur_rec hdr_cur_rec;
270 l_line_cur_rec line_cur_rec;
271 l_api_version CONSTANT NUMBER := 1.0;
272 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
273
274 BEGIN
275
276 p_line_id := p_in_parameter_record.line_id;
277 p_chr_id := p_in_parameter_record.chr_id;
278 p_organization_id := p_in_parameter_record.organization_id;
279 p_inventory_item_id := p_in_parameter_record.inventory_item_id;
280
281 IF p_chr_id IS NOT NULL THEN
282 OPEN hdr_cur(p_chr_id);
283 FETCH hdr_cur INTO l_hdr_cur_rec;
284
285 IF hdr_cur%Notfound THEN
286 CLOSE hdr_cur;
287 x_return_status := 'E';
288 RAISE G_ERROR;
289 END IF;
290
291 CLOSE hdr_cur;
292 END IF;
293
294 IF p_line_id IS NOT NULL THEN
295 OPEN line_cur(p_line_id);
296 FETCH line_cur INTO l_line_cur_rec;
297
298 IF line_cur%Notfound THEN
299 CLOSE line_cur;
300 x_return_status := 'E';
301 RAISE G_ERROR;
302 END IF;
303
304 CLOSE line_cur;
305 END IF;
306
307 IF p_line_id IS NOT NULL AND
308 p_organization_id IS NOT NULL AND
309 p_inventory_item_id IS NOT NULL THEN
310
311 OPEN prod_name_desc_cur(p_line_id, p_organization_id, p_inventory_item_id);
312 FETCH prod_name_desc_cur INTO l_cur_rec;
313
314 IF prod_name_desc_cur%Notfound THEN
315 CLOSE prod_name_desc_cur;
316 x_return_status := 'E';
317 RAISE G_ERROR;
318 END IF;
319
320 CLOSE prod_name_desc_cur;
321 END IF;
322
323 x_cur_rec := l_cur_rec;
324 x_hdr_cur_rec := l_hdr_cur_rec;
325 x_line_cur_rec := l_line_cur_rec;
326 x_return_status := l_return_status;
327
328
329 EXCEPTION
330 WHEN G_ERROR THEN
331 NULL;
332 WHEN OTHERS THEN
333 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
334 p_msg_name => G_UNEXPECTED_ERROR,
335 p_token1 => G_SQLCODE_TOKEN,
336 p_token1_value => SQLCODE,
337 p_token2 => G_SQLERRM_TOKEN,
338 p_token2_value => SQLERRM);
339
340 x_return_status := G_RET_STS_UNEXP_ERROR;
341
342 END COPY_PARAMETER;
343
344 FUNCTION chk_counter (p_object1_id1 NUMBER,
345 p_cle_id NUMBER,
346 p_lse_id NUMBER DEFAULT NULL) RETURN NUMBER IS
347 CURSOR get_ctr_association_csr IS
348 SELECT counter_group_id
349 FROM okx_ctr_associations_v
350 WHERE source_object_id = p_object1_id1;
351
352 get_ctr_association_rec get_ctr_association_csr%ROWTYPE;
353
354 -- Bug 5284349 --
355 -- Called in the case of Service line
356
357 CURSOR get_ctr_grp_csr IS
358 --Bug 5036682 performance issue
359 --SELECT template_flag
360 --FROM okx_counter_groups_v
361 --WHERE source_object_id = p_cle_id
362 --AND source_object_code = 'CONTRACT_LINE';
363 /*
364 SELECT a.template_flag
365 FROM cs_csi_counter_groups a
366 WHERE a.source_object_id = p_cle_id
367 AND a.source_object_code = 'CONTRACT_LINE'
368 AND(
369 (a.template_flag = 'Y') OR
370 (a.template_flag = 'N' AND EXISTS (select 1 from
371 csi_counters_b ccb, csi_counter_associations cca
372 where a.counter_group_id = ccb.group_id AND
373 ccb.counter_id = cca.counter_id))
374 );*/
375
376 SELECT a.template_flag
377 FROM cs_csi_counter_groups a,
378 csi_counters_b ccb, csi_counter_associations cca
379 WHERE a.counter_group_id = ccb.group_id AND
380 ccb.counter_id = cca.counter_id AND
381 cca.source_object_id = p_cle_id AND
382 cca.source_object_code = 'CONTRACT_LINE';
383 -- Bug 5284349 --
384 get_ctr_grp_rec get_ctr_grp_csr%ROWTYPE;
385
386 CURSOR get_ctr_csr IS
387 --SELECT COUNT(counter_id) cnt
388 --FROM okx_counters_v
389 --WHERE counter_id = p_object1_id1;
390 --bug 5036623
391 SELECT COUNT(temp.counter_id) cnt
392 FROM
393 (SELECT counter_id FROM CSI_COUNTER_TEMPLATE_B
394 UNION ALL
395 SELECT counter_id FROM CSI_COUNTERS_B) temp
396 WHERE temp.counter_id = p_object1_id1;
397
398 get_ctr_rec get_ctr_csr%ROWTYPE;
399
400 CURSOR get_ctr_grp_csr1 (p_ctr_grp_id NUMBER) IS
401 SELECT template_flag
402 -- FROM okx_counter_groups_v
403 FROM csi_counter_groups_v -- Bug 5284349
404 WHERE counter_group_id = p_ctr_grp_id;
405
406 get_ctr_grp_rec1 get_ctr_grp_csr1%ROWTYPE;
407 l_count NUMBER := 0;
408
409 /*Value of l_return
410 '0' -> no record
411 '1' -> record exist
412 '-99 -> record exists but template one
413 */
414
415 BEGIN
416 IF NVL(p_lse_id, - 99) = 12
417 THEN
418 OPEN get_ctr_csr;
419 FETCH get_ctr_csr INTO get_ctr_rec;
420 CLOSE get_ctr_csr;
421 RETURN(get_ctr_rec.cnt);
422 ELSE
423
424 l_count := 0;
425
426 IF p_cle_id IS NOT NULL
427 THEN
428 OPEN get_ctr_grp_csr;
429 FETCH get_ctr_grp_csr INTO get_ctr_grp_rec;
430 IF get_ctr_grp_csr%FOUND
431 THEN
432 IF get_ctr_grp_rec.template_flag = 'Y'
433 THEN
434 l_count := - 99;
435 ELSE
436 l_count := 1;
437 END IF;
438 ELSE
439 l_count := 0;
440 END IF; --IF get_ctr_grp_csr%FOUND
441
442 CLOSE get_ctr_grp_csr;
443 RETURN(l_count);
444 END IF; --IF p_cle_id IS NOT NULL
445
446 l_count := 0;
447 -- Code would not be Used
448 IF (p_cle_id IS NULL AND p_object1_id1 IS NOT NULL)
449 THEN
450 OPEN get_ctr_association_csr;
451 FETCH get_ctr_association_csr INTO get_ctr_association_rec;
452 IF get_ctr_association_csr%FOUND
453 THEN
454 OPEN get_ctr_grp_csr1 (get_ctr_association_rec.counter_group_id);
455 FETCH get_ctr_grp_csr1 INTO get_ctr_grp_rec1;
456 IF get_ctr_grp_csr1%FOUND
457 THEN
458 IF get_ctr_grp_rec1.template_flag = 'Y'
459 THEN
460 l_count := - 99;
461 ELSE
462 l_count := 1;
463 END IF;
464 END IF; --IF get_ctr_grp_csr1%FOUND
465 CLOSE get_ctr_grp_csr1;
466 ELSE
467 l_count := 0;
468 END IF; --IF get_ctr_association_csr%FOUND
469 CLOSE get_ctr_association_csr;
470 RETURN(l_count);
471 END IF; --IF (p_cle_id IS NULL AND p_object1_id1 IS NOT NULL)
472 -- Code would not be used
473 END IF; -- IF p_lse_id IS NULL
474 RETURN(l_count);
475
476 END chk_counter;
477
478 FUNCTION chk_event (p_object1_id1 NUMBER DEFAULT NULL,
479 p_cle_id NUMBER DEFAULT NULL,
480 p_lse_id NUMBER DEFAULT NULL,
481 p_counter_group_id NUMBER DEFAULT NULL,
482 p_template_counter_group_id NUMBER DEFAULT NULL) RETURN NUMBER
483 IS
484 CURSOR get_inst_event_csr IS
485 SELECT COUNT(object_id) cnt
486 FROM okc_condition_headers_b
487 WHERE object_id = p_cle_id
488 AND jtot_object_code = 'OKC_K_LINE';
489
490 get_inst_event_rec get_inst_event_csr%ROWTYPE;
491
492 CURSOR get_ctr_association_csr IS
493 SELECT counter_group_id
494 FROM okx_ctr_associations_v
495 WHERE source_object_id = p_object1_id1;
496
497 get_ctr_association_rec get_ctr_association_csr%ROWTYPE;
498
499 CURSOR get_template_event_csr (p_ctr_grp_id NUMBER) IS
500 SELECT COUNT(counter_group_id) cnt
501 FROM okc_condition_headers_b
502 WHERE counter_group_id = p_ctr_grp_id
503 AND template_yn = 'Y';
504
505 get_template_event_rec get_template_event_csr%ROWTYPE;
506 l_return NUMBER := 0;
507
508 /*Value of l_return
509 '0' -> no record
510 '1' -> record exist
511 '-99 -> record exists but template one
512 */
513
514 FUNCTION chk_event_template (p_ctr_grp_id NUMBER) RETURN NUMBER IS
515 CURSOR get_event_csr (p_ctr_grp_id NUMBER) IS
516 SELECT template_yn
517 FROM OKC_CONDITION_HEADERS_B
518 WHERE counter_group_id = p_ctr_grp_id;
519 get_event_rec get_event_csr%ROWTYPE;
520 l_return_no NUMBER := 0;
521 BEGIN
522
523 OPEN get_event_csr(p_ctr_grp_id);
524 FETCH get_event_csr INTO get_event_rec;
525 IF get_event_csr%FOUND
526 THEN
527 IF get_event_rec.template_yn = 'Y'
528 THEN
529 l_return_no := - 99;
530 ELSE
531 l_return_no := 1;
532 END IF;
533 ELSE
534 l_return_no := 0;
535 END IF; --IF get_event_csr%FOUND
536 CLOSE get_event_csr;
537 RETURN (l_return_no);
538
539 END;
540 BEGIN
541 l_return := 0;
542
543 IF NVL(p_lse_id, - 99) = 12
544 THEN
545 IF p_counter_group_id IS NOT NULL
546 THEN
547 l_return := chk_event_template (p_counter_group_id);
548
549 IF (l_return = 0 AND p_template_counter_group_id IS NOT NULL)
550 THEN
551 l_return := chk_event_template (p_template_counter_group_id);
552 END IF;
553 END IF;
554 RETURN (l_return);
555
556 ELSE
557 IF p_cle_id IS NOT NULL
558 THEN
559 OPEN get_inst_event_csr;
560 FETCH get_inst_event_csr INTO get_inst_event_rec;
561 CLOSE get_inst_event_csr;
562 RETURN(get_inst_event_rec.cnt);
563
564 END IF; -- IF p_cle_id IS NOT NULL
565
566 IF p_cle_id IS NULL
567 THEN
568 --get counter_group_id
569 OPEN get_ctr_association_csr;
570 FETCH get_ctr_association_csr INTO get_ctr_association_rec;
571 --get attached event if
572 IF get_ctr_association_csr%FOUND
573 THEN
574 OPEN get_template_event_csr(get_ctr_association_rec.counter_group_id);
575 FETCH get_template_event_csr INTO get_template_event_rec;
576 CLOSE get_template_event_csr;
577 IF get_template_event_rec.cnt > 0
578 THEN
579 l_return := - 99; -- '-99' is for template event not intantiated one
580 ELSE
581 l_return := 0;
582 END IF;
583 END IF; --get_ctr_association_csr%FOUND
584 CLOSE get_ctr_association_csr;
585 RETURN(l_return);
586
587 END IF; -- IF p_cle_id IS NULL
588 END IF; --IF p_lse_id = 12
589 RETURN(l_return);
590 END chk_event;
591
592
593 PROCEDURE Contact_Point
594 (
595 p_api_version IN NUMBER,
596 p_init_msg_list IN VARCHAR2,
597 p_commit IN VARCHAR2,
598 P_contact_point_rec IN contact_point_rec,
599 x_return_status OUT NOCOPY VARCHAR2,
600 x_msg_count OUT NOCOPY NUMBER,
601 x_msg_data OUT NOCOPY VARCHAR2,
602 x_contact_point_id OUT NOCOPY NUMBER
603 )
604 IS
605 G_ERROR EXCEPTION;
606 l_create_update_flag VARCHAR2(10);
607 BEGIN
608
609 IF (P_Contact_point_rec.contact_point_id IS NULL AND
610 p_contact_point_rec.email_address IS NULL) THEN
611 x_return_status := OKC_API.G_RET_STS_SUCCESS;
612 RETURN;
613 ELSIF (P_Contact_point_rec.contact_point_id IS NULL AND
614 p_contact_point_rec.email_address IS NOT NULL) THEN
615 --Create
616 oks_auth_util_pvt.Create_Contact_Points
617 (
618 p_api_version => p_api_version,
619 p_init_msg_list => p_init_msg_list,
620 p_commit => p_commit,
621 P_contact_point_rec => p_contact_point_rec,
622 x_return_status => x_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data,
625 x_contact_point_id => x_contact_point_id
626 );
627 IF x_return_status <> 'S' THEN
628 RAISE G_ERROR;
629 END IF;
630 ELSE
631 --Update
632 oks_auth_util_pvt.Update_Contact_Points
633 (
634 p_api_version => p_api_version,
635 p_init_msg_list => p_init_msg_list,
636 p_commit => p_commit,
637 P_contact_point_rec => p_contact_point_rec,
638 x_return_status => x_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data
641 );
642 IF x_return_status <> 'S' THEN
643 RAISE G_ERROR;
644 END IF;
645 x_contact_point_id := p_contact_point_rec.contact_point_id;
646 END IF;
647 x_return_status := OKC_API.G_RET_STS_SUCCESS;
648 EXCEPTION
649 WHEN G_ERROR THEN
650 NULL;
651 WHEN OTHERS THEN
652 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
653 OKC_API.set_message
654 (OKC_API.G_APP_NAME,
655 G_UNEXPECTED_ERROR,
656 G_SQLCODE_TOKEN,
657 SQLCODE,
658 G_SQLERRM_TOKEN,
659 SQLERRM);
660 END Contact_Point;
661
662
663
664 PROCEDURE CREATE_CII_FOR_SUBSCRIPTION
665 (
666 p_api_version IN NUMBER,
667 p_init_msg_list IN VARCHAR2,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_msg_count OUT NOCOPY NUMBER,
670 x_msg_data OUT NOCOPY VARCHAR2,
671 p_cle_id IN NUMBER,
672 p_quantity IN NUMBER DEFAULT 1,
673 x_instance_id OUT NOCOPY NUMBER
674 )IS
675 BEGIN
676 OKS_AUTH_UTIL_PVT.CREATE_CII_FOR_SUBSCRIPTION
677 (
678 p_api_version,
679 p_init_msg_list,
680 x_return_status,
681 x_msg_count,
682 x_msg_data,
683 p_cle_id,
684 p_quantity,
685 x_instance_id
686 );
687
688
689 END;
690
691 PROCEDURE DELETE_CII_FOR_SUBSCRIPTION
692 (p_api_version IN NUMBER,
693 p_init_msg_list IN VARCHAR2,
694 x_return_status OUT NOCOPY VARCHAR2,
695 x_msg_count OUT NOCOPY NUMBER,
696 x_msg_data OUT NOCOPY VARCHAR2,
697 p_instance_id IN NUMBER
698 )
699 IS
700 BEGIN
701 OKS_AUTH_UTIL_PVT.DELETE_CII_FOR_SUBSCRIPTION
702 (p_api_version,
703 p_init_msg_list,
704 x_return_status,
705 x_msg_count,
706 x_msg_data,
707 p_instance_id
708 ) ;
709
710 END DELETE_CII_FOR_SUBSCRIPTION ;
711
712
713 FUNCTION def_sts_code(p_ste_code VARCHAR2) RETURN VARCHAR2 IS
714 CURSOR get_def_sts_code_csr IS
715 SELECT code
716 FROM okc_statuses_v
717 WHERE ste_code = p_ste_code
718 AND default_yn = 'Y';
719 get_def_sts_code_rec get_def_sts_code_csr%ROWTYPE;
720 BEGIN
721
722 OPEN get_def_sts_code_csr;
723 FETCH get_def_sts_code_csr INTO get_def_sts_code_rec;
724 CLOSE get_def_sts_code_csr;
725 RETURN (get_def_sts_code_rec.code);
726
727 END def_sts_code;
728
729 FUNCTION get_ste_code(p_sts_code VARCHAR2) RETURN VARCHAR2 IS
730 CURSOR get_ste_code_csr IS
731 SELECT ste_code
732 FROM okc_statuses_v
733 WHERE code = p_sts_code;
734 get_ste_code_rec get_ste_code_csr%ROWTYPE;
735 BEGIN
736
737 OPEN get_ste_code_csr;
738 FETCH get_ste_code_csr INTO get_ste_code_rec;
739 CLOSE get_ste_code_csr;
740 RETURN (get_ste_code_rec.ste_code);
741
742 END get_ste_code;
743 --start contact creation OCT 2004
744 -- added new procedure for contact creation project
745 PROCEDURE create_person (
746 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
747 p_person_tbl IN PERSON_TBL_TYPE,
748 x_party_id OUT NOCOPY NUMBER,
749 x_party_number OUT NOCOPY VARCHAR2,
750 x_profile_id OUT NOCOPY NUMBER,
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2
754 ) IS
755 BEGIN
756 OKS_AUTH_UTIL_PVT.create_person
757 (p_init_msg_list,
758 p_person_tbl,
759 x_party_id,
760 x_party_number,
761 x_profile_id,
762 x_return_status,
763 x_msg_count,
764 x_msg_data);
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
769 OKC_API.set_message
770 (OKC_API.G_APP_NAME,
771 G_UNEXPECTED_ERROR,
772 G_SQLCODE_TOKEN,
773 SQLCODE,
774 G_SQLERRM_TOKEN,
775 SQLERRM);
776 END;
777
778 PROCEDURE update_person (
779 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
780 p_person_tbl IN PERSON_TBL_TYPE,
781 p_party_object_version_number IN NUMBER,
782 x_profile_id OUT NOCOPY NUMBER,
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_msg_count OUT NOCOPY NUMBER,
785 x_msg_data OUT NOCOPY VARCHAR2
786 )IS
787 BEGIN
788 OKS_AUTH_UTIL_PVT.update_person
789 (p_init_msg_list,
790 p_person_tbl,
791 p_party_object_version_number,
792 x_profile_id,
793 x_return_status,
794 x_msg_count,
795 x_msg_data);
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
800 OKC_API.set_message
801 (OKC_API.G_APP_NAME,
802 G_UNEXPECTED_ERROR,
803 G_SQLCODE_TOKEN,
804 SQLCODE,
805 G_SQLERRM_TOKEN,
806 SQLERRM);
807 END;
808
809 PROCEDURE create_org_contact (
810 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
811 p_org_contact_tbl IN ORG_CONTACT_TBL_TYPE,
812 p_relationship_tbl_type IN relationship_tbl_type,
813 x_org_contact_id OUT NOCOPY NUMBER,
814 x_party_rel_id OUT NOCOPY NUMBER,
815 x_party_id OUT NOCOPY NUMBER,
816 x_party_number OUT NOCOPY VARCHAR2,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2
820 )IS
821 BEGIN
822 OKS_AUTH_UTIL_PVT.create_org_contact
823 (p_init_msg_list,
824 p_org_contact_tbl,
825 p_relationship_tbl_type,
826 x_org_contact_id,
827 x_party_rel_id,
828 x_party_id,
829 x_party_number,
830 x_return_status,
831 x_msg_count,
832 x_msg_data);
833
834 EXCEPTION
835 WHEN OTHERS THEN
836 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
837 OKC_API.set_message
838 (OKC_API.G_APP_NAME,
839 G_UNEXPECTED_ERROR,
840 G_SQLCODE_TOKEN,
841 SQLCODE,
842 G_SQLERRM_TOKEN,
843 SQLERRM);
844 END;
845
846 PROCEDURE update_org_contact (
847 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
848 p_org_contact_tbl IN ORG_CONTACT_TBL_TYPE,
849 p_relationship_tbl_type IN relationship_tbl_type,
850 p_cont_object_version_number IN OUT NOCOPY NUMBER,
851 p_rel_object_version_number IN OUT NOCOPY NUMBER,
852 p_party_object_version_number IN OUT NOCOPY NUMBER,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2
856 )IS
857 BEGIN
858 OKS_AUTH_UTIL_PVT.update_org_contact
859 (p_init_msg_list,
860 p_org_contact_tbl,
861 p_relationship_tbl_type,
862 p_cont_object_version_number,
863 p_rel_object_version_number,
864 p_party_object_version_number,
865 x_return_status,
866 x_msg_count,
867 x_msg_data);
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
872 OKC_API.set_message
873 (OKC_API.G_APP_NAME,
874 G_UNEXPECTED_ERROR,
875 G_SQLCODE_TOKEN,
876 SQLCODE,
877 G_SQLERRM_TOKEN,
878 SQLERRM);
879 END;
880
881 PROCEDURE create_party_site (
882 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
883 p_party_site_tbl IN PARTY_SITE_TBL_TYPE,
884 x_party_site_id OUT NOCOPY NUMBER,
885 x_party_site_number OUT NOCOPY VARCHAR2,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2
889 )IS
890 BEGIN
891 OKS_AUTH_UTIL_PVT.create_party_site
892 (p_init_msg_list,
893 p_party_site_tbl,
894 x_party_site_id,
895 x_party_site_number,
896 x_return_status,
897 x_msg_count,
898 x_msg_data);
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
903 OKC_API.set_message
904 (OKC_API.G_APP_NAME,
905 G_UNEXPECTED_ERROR,
906 G_SQLCODE_TOKEN,
907 SQLCODE,
908 G_SQLERRM_TOKEN,
909 SQLERRM);
910 END;
911
912 PROCEDURE update_party_site (
913 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
914 p_party_site_tbl IN PARTY_SITE_TBL_TYPE,
915 p_object_version_number IN OUT NOCOPY NUMBER,
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_count OUT NOCOPY NUMBER,
918 x_msg_data OUT NOCOPY VARCHAR2
919 )IS
920 BEGIN
921 OKS_AUTH_UTIL_PVT.update_party_site
922 (p_init_msg_list,
923 p_party_site_tbl,
924 p_object_version_number,
925 x_return_status,
926 x_msg_count,
927 x_msg_data);
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
932 OKC_API.set_message
933 (OKC_API.G_APP_NAME,
934 G_UNEXPECTED_ERROR,
935 G_SQLCODE_TOKEN,
936 SQLCODE,
937 G_SQLERRM_TOKEN,
938 SQLERRM);
939 END;
940
941 PROCEDURE create_cust_account_role (
942 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
943 p_cust_account_role_tbl IN CUST_ACCOUNT_ROLE_tbl_TYPE,
944 x_cust_account_role_id OUT NOCOPY NUMBER,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER,
947 x_msg_data OUT NOCOPY VARCHAR2
948 )IS
949 BEGIN
950 OKS_AUTH_UTIL_PVT.create_cust_account_role
951 (p_init_msg_list,
952 p_cust_account_role_tbl,
953 x_cust_account_role_id,
954 x_return_status,
955 x_msg_count,
956 x_msg_data);
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
961 OKC_API.set_message
962 (OKC_API.G_APP_NAME,
963 G_UNEXPECTED_ERROR,
964 G_SQLCODE_TOKEN,
965 SQLCODE,
966 G_SQLERRM_TOKEN,
967 SQLERRM);
968 END;
969
970 PROCEDURE update_cust_account_role (
971 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
972 p_cust_account_role_tbl IN CUST_ACCOUNT_ROLE_tbl_TYPE,
973 p_object_version_number IN OUT NOCOPY NUMBER,
974 x_return_status OUT NOCOPY VARCHAR2,
975 x_msg_count OUT NOCOPY NUMBER,
976 x_msg_data OUT NOCOPY VARCHAR2
977 ) IS
978 BEGIN
979 OKS_AUTH_UTIL_PVT.update_cust_account_role
980 (p_init_msg_list,
981 p_cust_account_role_tbl,
982 p_object_version_number,
983 x_return_status,
984 x_msg_count,
985 x_msg_data);
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
990 OKC_API.set_message
991 (OKC_API.G_APP_NAME,
992 G_UNEXPECTED_ERROR,
993 G_SQLCODE_TOKEN,
994 SQLCODE,
995 G_SQLERRM_TOKEN,
996 SQLERRM);
997 END;
998
999 PROCEDURE create_cust_acct_site (
1000 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1001 p_cust_acct_site_tbl IN CUST_ACCT_SITE_TBL_TYPE,
1002 x_cust_acct_site_id OUT NOCOPY NUMBER,
1003 x_return_status OUT NOCOPY VARCHAR2,
1004 x_msg_count OUT NOCOPY NUMBER,
1005 x_msg_data OUT NOCOPY VARCHAR2
1006 )IS
1007 BEGIN
1008 OKS_AUTH_UTIL_PVT.create_cust_acct_site
1009 (p_init_msg_list,
1010 p_cust_acct_site_tbl,
1011 x_cust_acct_site_id,
1012 x_return_status,
1013 x_msg_count,
1014 x_msg_data);
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1019 OKC_API.set_message
1020 (OKC_API.G_APP_NAME,
1021 G_UNEXPECTED_ERROR,
1022 G_SQLCODE_TOKEN,
1023 SQLCODE,
1024 G_SQLERRM_TOKEN,
1025 SQLERRM);
1026 END;
1027
1028 PROCEDURE update_cust_acct_site (
1029 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1030 p_cust_acct_site_tbl IN CUST_ACCT_SITE_TBL_TYPE,
1031 p_object_version_number IN OUT NOCOPY NUMBER,
1032 x_return_status OUT NOCOPY VARCHAR2,
1033 x_msg_count OUT NOCOPY NUMBER,
1034 x_msg_data OUT NOCOPY VARCHAR2
1035 )IS
1036
1037 BEGIN
1038 OKS_AUTH_UTIL_PVT.update_cust_acct_site
1039 (p_init_msg_list,
1040 p_cust_acct_site_tbl,
1041 p_object_version_number,
1042 x_return_status,
1043 x_msg_count,
1044 x_msg_data);
1045
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1049 OKC_API.set_message
1050 (OKC_API.G_APP_NAME,
1051 G_UNEXPECTED_ERROR,
1052 G_SQLCODE_TOKEN,
1053 SQLCODE,
1054 G_SQLERRM_TOKEN,
1055 SQLERRM);
1056 END;
1057
1058
1059 -- GCHADHA --
1060 -- MULTI CURRENCY PRICELIST --
1061 -- THIS FUNCTION IS CALLED WHEN REPRICING MULTIPLE LINES --
1062 -- 28-OCT-2004 --
1063 PROCEDURE COMPUTE_PRICE_MULTIPLE_LINE(
1064 p_api_version IN NUMBER,
1065 p_detail_tbl IN MULTI_LINE_TBL,
1066 x_return_status OUT NOCOPY VARCHAR2,
1067 x_status_tbl OUT NOCOPY oks_qp_int_pvt.Pricing_Status_tbl ) IS
1068
1069 l_input_details OKS_QP_PKG.INPUT_DETAILS;
1070 l_output_details OKS_QP_PKG.PRICE_DETAILS;
1071 l_modif_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1072 l_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
1073 l_return_status VARCHAR2(20);
1074 l_msg_count NUMBER;
1075 l_msg_data VARCHAR2(2000);
1076 l_status_tbl oks_qp_int_pvt.Pricing_Status_tbl;
1077 l_final_status_tbl oks_qp_int_pvt.Pricing_Status_tbl;
1078 l_count NUMBER;
1079
1080
1081 BEGIN
1082 IF p_detail_tbl.COUNT > 0 THEN
1083 FOR I IN p_detail_tbl.FIRST..p_detail_tbl.LAST
1084 LOOP
1085 -- Changes made for Partial Period Change Request --
1086 -- IF nvl(p_detail_tbl(I).line_pl_flag,'N') <> 'N' OR nvl(p_detail_tbl(I).line_uom_flag,'N') <> 'N' THEN
1087 IF nvl(p_detail_tbl(I).line_pl_flag, 'N') <> 'N' THEN
1088 -- Changes made for Partial Period Change Request --
1089 l_input_details.line_id := p_detail_tbl(I).id;
1090 IF p_detail_tbl(I).lse_id = '46' THEN
1091 l_input_details.intent := 'SB_P';
1092 ELSE
1093 l_input_details.intent := 'LP';
1094 END IF;
1095
1096
1097 oks_qp_int_pvt.compute_price
1098 (
1099 p_api_version => 1.0,
1100 p_init_msg_list => 'T',
1101 p_detail_rec => l_input_details,
1102 x_price_details => l_output_details,
1103 x_modifier_details => l_modif_details,
1104 x_price_break_details => l_pb_details,
1105 x_return_status => l_return_status,
1106 x_msg_count => l_msg_count,
1107 x_msg_data => l_msg_data
1108 );
1109
1110 IF NVL(l_return_status, '!') <> 'S' THEN
1111 x_return_status := l_return_status;
1112 RAISE G_ERROR;
1113 END IF;
1114 IF l_status_tbl.COUNT = 0 THEN
1115 l_count := 0;
1116 END IF;
1117
1118 l_status_tbl := oks_qp_int_pvt.Get_Pricing_Messages;
1119 -- GCHADHA --
1120 -- BUG 4020869 --
1121 -- 19-NOV-2004 --
1122 IF l_status_tbl.COUNT > 0 THEN
1123 FOR I IN l_status_tbl.FIRST..l_status_tbl.LAST LOOP
1124 l_final_status_tbl(l_count).Service_name := l_status_tbl(I).Service_name;
1125 l_final_status_tbl(l_count).Coverage_level_name := l_status_tbl(I).Coverage_level_name;
1126 l_final_status_tbl(l_count).Status_Code := l_status_tbl(I).Status_Code;
1127 l_final_status_tbl(l_count).Status_text := l_status_tbl(I).Status_text;
1128 l_count := l_count + 1;
1129 END LOOP;
1130 END IF;
1131 -- END GCHADHA --
1132 END IF;
1133 END LOOP;
1134 END IF;
1135
1136 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1137 x_status_tbl := l_final_status_tbl;
1138 EXCEPTION
1139 WHEN G_ERROR THEN
1140 NULL;
1141 END COMPUTE_PRICE_MULTIPLE_LINE;
1142 -- END GCHADHA --
1143
1144 -- GCHADHA --
1145 -- BUG 4053911 --
1146 -- DELETING THE PRICING MODIFIER WHEN THE CURRENCY OF THE
1147 -- CONTRACT IS CHANGED.
1148 PROCEDURE DELETE_PRICE_ADJUST_LINE(
1149 p_api_version IN NUMBER,
1150 p_chr_id IN NUMBER,
1151 p_header_currency IN VARCHAR2) IS
1152
1153 BEGIN
1154 -- HEADERS --
1155 DELETE FROM OKC_PRICE_ADJUSTMENTS
1156 WHERE ID IN
1157 (SELECT A.ID
1158 FROM OKC_PRICE_ADJUSTMENTS A, qp_list_headers_b B
1159 WHERE A.CHR_ID = P_CHR_ID
1160 AND B.LIST_HEADER_ID = A.LIST_HEADER_ID
1161 AND B.CURRENCY_CODE <> P_HEADER_CURRENCY
1162 AND B.CURRENCY_CODE IS NOT NULL ) ;
1163 -- LINES AND SUBLINES --
1164 DELETE FROM OKC_PRICE_ADJUSTMENTS WHERE
1165 ID IN (SELECT A.ID
1166 FROM OKC_PRICE_ADJUSTMENTS A, QP_LIST_HEADERS_B B
1167 WHERE A.CLE_ID IN
1168 (SELECT ID FROM OKC_K_LINES_B WHERE
1169 DNZ_CHR_ID = P_CHR_ID
1170 AND LSE_ID IN (1, 12, 46, 19, 7, 8, 9, 10, 11, 13, 35, 25))
1171 AND B.LIST_HEADER_ID = A.LIST_HEADER_ID
1172 AND B.CURRENCY_CODE <> P_HEADER_CURRENCY
1173 AND B.CURRENCY_CODE IS NOT NULL ) ;
1174 END;
1175 -- END GCHADHA --
1176
1177 -- END GCHADHA --
1178
1179 -- PARTIAL PERIOD COMPUTATION PCC --
1180 FUNCTION is_not_subscrip(p_cle_id NUMBER) RETURN VARCHAR2
1181 IS
1182 CURSOR chk_subscr_item_csr IS
1183 SELECT COUNT(instance_id) cnt
1184 FROM oks_subscr_header_b sub, okc_k_items_v item
1185 WHERE sub.instance_id = item.object1_id1
1186 AND item.cle_id = p_cle_id;
1187 chk_subscr_item_rec chk_subscr_item_csr%ROWTYPE;
1188
1189 l_yes_no VARCHAR2(1);
1190 BEGIN
1191
1192 OPEN chk_subscr_item_csr;
1193 FETCH chk_subscr_item_csr INTO chk_subscr_item_rec;
1194 CLOSE chk_subscr_item_csr;
1195
1196 IF chk_subscr_item_rec.cnt > 0
1197 THEN
1198 l_yes_no := 'Y';
1199 ELSE
1200 l_yes_no := 'N';
1201 END IF;
1202 RETURN(l_yes_no);
1203 END is_not_subscrip;
1204
1205 -- PARTIAL PERIOD COMPUTATION PCC --
1206
1207 END OKS_AUTH_UTIL_PUB;