[Home] [Help]
PACKAGE BODY: APPS.OKS_CUSTOMER_ACCEPTANCE_PVT
Source
1 PACKAGE BODY oks_customer_acceptance_pvt AS
2 /* $Header: OKSVCUSB.pls 120.24.12000000.4 2007/10/17 12:24:07 vgujarat ship $ */
3 ------------------------------------------------------------------------------
4 -- GLOBAL CONSTANTS
5 ------------------------------------------------------------------------------
6 g_pkg_name CONSTANT VARCHAR2 (200)
7 := 'OKS_CUSTOMER_ACCEPTANCE_PVT';
8 g_app_name CONSTANT VARCHAR2 (3) := 'OKS';
9 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
10 g_module CONSTANT VARCHAR2 (250)
11 := 'oks.plsql.' ||
12 g_pkg_name ||
13 '.';
14 g_application_id CONSTANT NUMBER := 515; -- OKS Application
15 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
16 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
17 g_ret_sts_success CONSTANT VARCHAR2 (1)
18 := fnd_api.g_ret_sts_success;
19 g_ret_sts_error CONSTANT VARCHAR2 (1)
20 := fnd_api.g_ret_sts_error;
21 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
22 := fnd_api.g_ret_sts_unexp_error;
23 g_unexpected_error CONSTANT VARCHAR2 (200)
24 := 'OKS_UNEXPECTED_ERROR';
25 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
26 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
27
28 FUNCTION get_contract_amount (
29 p_chr_id IN NUMBER
30 )
31 RETURN VARCHAR2 AS
32 l_api_name CONSTANT VARCHAR2 (30)
33 := 'get_contract_amount';
34 l_k_amount VARCHAR2 (1000) := '';
35
36 CURSOR csr_k_amt IS
37 SELECT TO_CHAR
38 (oks_extwar_util_pvt.round_currency_amt
39 ((NVL (ch.estimated_amount, 0) +
40 NVL (sh.tax_amount, 0)
41 ),
42 ch.currency_code),
43 fnd_currency.get_format_mask (ch.currency_code, 50)) ||
44 ' ' ||
45 ch.currency_code AS amount
46 FROM okc_k_headers_all_b ch,
47 oks_k_headers_b sh
48 WHERE ch.ID = sh.chr_id
49 AND ch.ID = p_chr_id;
50 BEGIN
51 -- start debug log
52 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
53 fnd_log.STRING (fnd_log.level_procedure,
54 g_module ||
55 l_api_name,
56 '100: Entered ' ||
57 g_pkg_name ||
58 '.' ||
59 l_api_name
60 );
61 END IF;
62
63 OPEN csr_k_amt;
64
65 FETCH csr_k_amt
66 INTO l_k_amount;
67
68 CLOSE csr_k_amt;
69
70 -- end debug log
71 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
72 fnd_log.STRING (fnd_log.level_procedure,
73 g_module ||
74 l_api_name,
75 '1000: Leaving ' ||
76 g_pkg_name ||
77 '.' ||
78 l_api_name
79 );
80 END IF;
81
82 RETURN l_k_amount;
83 EXCEPTION
84 WHEN OTHERS THEN
85 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
86 fnd_log.STRING (fnd_log.level_procedure,
87 g_module ||
88 l_api_name,
89 '4000: Leaving ' ||
90 g_pkg_name ||
91 '.' ||
92 l_api_name
93 );
94 END IF;
95
96 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
97 RETURN l_k_amount;
98 END get_contract_amount;
99
100 FUNCTION get_contract_subtotal (
101 p_chr_id IN NUMBER
102 )
103 RETURN VARCHAR2 AS
104 l_api_name CONSTANT VARCHAR2 (30)
105 := 'get_contract_subtotal';
106 l_k_subtotal VARCHAR2 (1000) := '';
107
108 CURSOR csr_k_subtotal IS
109 SELECT TO_CHAR
110 (oks_extwar_util_pvt.round_currency_amt
111 (NVL (ch.estimated_amount,
112 0),
113 ch.currency_code),
114 fnd_currency.get_format_mask (ch.currency_code, 50)) ||
115 ' ' ||
116 ch.currency_code AS amount
117 FROM okc_k_headers_all_b ch
118 WHERE ch.ID = p_chr_id;
119 BEGIN
120 -- start debug log
121 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
122 fnd_log.STRING (fnd_log.level_procedure,
123 g_module ||
124 l_api_name,
125 '100: Entered ' ||
126 g_pkg_name ||
127 '.' ||
128 l_api_name
129 );
130 END IF;
131
132 OPEN csr_k_subtotal;
133
134 FETCH csr_k_subtotal
135 INTO l_k_subtotal;
136
137 CLOSE csr_k_subtotal;
138
139 -- end debug log
140 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
141 fnd_log.STRING (fnd_log.level_procedure,
142 g_module ||
143 l_api_name,
144 '1000: Leaving ' ||
145 g_pkg_name ||
146 '.' ||
147 l_api_name
148 );
149 END IF;
150
151 RETURN l_k_subtotal;
152 EXCEPTION
153 WHEN OTHERS THEN
154 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
155 fnd_log.STRING (fnd_log.level_procedure,
156 g_module ||
157 l_api_name,
158 '4000: Leaving ' ||
159 g_pkg_name ||
160 '.' ||
161 l_api_name
162 );
163 END IF;
164
165 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
166 RETURN l_k_subtotal;
167 END get_contract_subtotal;
168
169 FUNCTION get_contract_tax (
170 p_chr_id IN NUMBER
171 )
172 RETURN VARCHAR2 AS
173 l_api_name CONSTANT VARCHAR2 (30)
174 := 'get_contract_amount';
175 l_k_tax VARCHAR2 (1000) := '';
176
177 CURSOR csr_k_tax IS
178 SELECT TO_CHAR
179 (oks_extwar_util_pvt.round_currency_amt
180 (NVL (sh.tax_amount, 0), ch.currency_code),
181 fnd_currency.get_format_mask (ch.currency_code, 50)) ||
182 ' ' ||
183 ch.currency_code AS amount
184 FROM okc_k_headers_all_b ch,
185 oks_k_headers_b sh
186 WHERE ch.ID = sh.chr_id
187 AND ch.ID = p_chr_id;
188
189 BEGIN
190 -- start debug log
191 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
192 fnd_log.STRING (fnd_log.level_procedure,
193 g_module ||
194 l_api_name,
195 '100: Entered ' ||
196 g_pkg_name ||
197 '.' ||
198 l_api_name
199 );
200 END IF;
201
202 OPEN csr_k_tax;
203
204 FETCH csr_k_tax
205 INTO l_k_tax;
206
207 CLOSE csr_k_tax;
208
209 -- end debug log
210 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
211 fnd_log.STRING (fnd_log.level_procedure,
212 g_module ||
213 l_api_name,
214 '1000: Leaving ' ||
215 g_pkg_name ||
216 '.' ||
217 l_api_name
218 );
219 END IF;
220
221 RETURN l_k_tax;
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
225 fnd_log.STRING (fnd_log.level_procedure,
226 g_module ||
227 l_api_name,
228 '4000: Leaving ' ||
229 g_pkg_name ||
230 '.' ||
231 l_api_name
232 );
233 END IF;
234
235 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
236 RETURN l_k_tax;
237 END get_contract_tax;
238
239 FUNCTION get_contract_accept_clause (
240 p_chr_id IN NUMBER
241 )
242 RETURN VARCHAR2 AS
243 l_api_name CONSTANT VARCHAR2 (30)
244 := 'get_contract_accept_clause';
245 l_contract_accept_clause VARCHAR2 (4000) := '';
246 l_customer_name VARCHAR2 (1000) := '';
247 l_vendor_name VARCHAR2 (1000) := '';
248 l_customer_token_exists VARCHAR2 (1) := '';
249 l_vendor_token_exists VARCHAR2 (1) := '';
250
251 CURSOR csr_customer_name IS
252 SELECT p.party_name AS customer_name
253 FROM okc_k_party_roles_b r,
254 hz_parties p
255 WHERE p.party_id = r.object1_id1
256 AND r.jtot_object1_code = 'OKX_PARTY'
257 AND r.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
258 -- gets only the CUSTOMER /SUBSCRIBER
259 AND r.cle_id IS NULL
260 AND r.chr_id = p_chr_id;
261
262 CURSOR csr_vendor_name IS
263 SELECT o.NAME AS vendor_name
264 FROM okc_k_party_roles_b r,
265 hr_all_organization_units o
266 WHERE o.organization_id = r.object1_id1
267 AND r.jtot_object1_code = 'OKX_OPERUNIT'
268 AND r.rle_code IN
269 ('VENDOR', 'MERCHANT') -- gets only the VENDOR / MERCHANT
270 AND r.cle_id IS NULL
271 AND r.chr_id = p_chr_id;
272
273 CURSOR csr_customer_token_exists IS
274 SELECT 'Y'
275 FROM fnd_new_messages
276 WHERE message_name = 'OKS_CUST_ACCEPT_CLAUSE'
277 AND language_code = USERENV ('LANG')
278 AND regexp_like (MESSAGE_TEXT,
279 'CUSTOMER_NAME',
280 'c'
281 );
282
283 CURSOR csr_vendor_token_exists IS
284 SELECT 'Y'
285 FROM fnd_new_messages
286 WHERE message_name = 'OKS_CUST_ACCEPT_CLAUSE'
287 AND language_code = USERENV ('LANG')
288 AND regexp_like (MESSAGE_TEXT,
289 'VENDOR_NAME',
290 'c'
291 );
292 BEGIN
293 -- start debug log
294 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
295 fnd_log.STRING (fnd_log.level_procedure,
296 g_module ||
297 l_api_name,
298 '100: Entered ' ||
299 g_pkg_name ||
300 '.' ||
301 l_api_name
302 );
303 END IF;
304
305 -- check if Customer Name token exists
306 OPEN csr_customer_token_exists;
307
308 FETCH csr_customer_token_exists
309 INTO l_customer_token_exists;
310
311 IF csr_customer_token_exists%FOUND THEN
312 -- Customer Name token exists, get the name
313 OPEN csr_customer_name;
314
315 FETCH csr_customer_name
316 INTO l_customer_name;
317
318 CLOSE csr_customer_name;
319 ELSE
320 -- Customer Name token does not exists
321 l_customer_token_exists := 'N';
322 END IF;
323
324 CLOSE csr_customer_token_exists;
325
326 -- check if Vendor Name token exists
327 OPEN csr_vendor_token_exists;
328
329 FETCH csr_vendor_token_exists
330 INTO l_vendor_token_exists;
331
332 IF csr_vendor_token_exists%FOUND THEN
333 -- Vendor Name token exists, get the name
334 OPEN csr_vendor_name;
335
336 FETCH csr_vendor_name
337 INTO l_vendor_name;
338
339 CLOSE csr_vendor_name;
340 ELSE
341 -- Vendor Name token does not exists
342 l_vendor_token_exists := 'N';
343 END IF;
344
345 CLOSE csr_vendor_token_exists;
346
347 -- set the fnd message for acceptance clause
348 fnd_message.set_name ('OKS', 'OKS_CUST_ACCEPT_CLAUSE');
349
350 IF l_customer_token_exists = 'Y' THEN
351 fnd_message.set_token ('CUSTOMER_NAME', l_customer_name);
352 END IF;
353
354 IF l_vendor_token_exists = 'Y' THEN
355 fnd_message.set_token ('VENDOR_NAME', l_vendor_name);
356 END IF;
357
358 l_contract_accept_clause := fnd_message.get;
359
360 -- end debug log
361 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
362 fnd_log.STRING (fnd_log.level_procedure,
363 g_module ||
364 l_api_name,
365 '1000: Leaving ' ||
366 g_pkg_name ||
367 '.' ||
368 l_api_name
369 );
370 END IF;
371
372 RETURN l_contract_accept_clause;
373 EXCEPTION
374 WHEN OTHERS THEN
375 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
376 fnd_log.STRING (fnd_log.level_procedure,
377 g_module ||
378 l_api_name,
379 '4000: Leaving ' ||
380 g_pkg_name ||
381 '.' ||
382 l_api_name
383 );
384 END IF;
385
386 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
387 RETURN l_contract_accept_clause;
388 END get_contract_accept_clause;
389
390 FUNCTION get_contract_decline_clause (
391 p_chr_id IN NUMBER
392 )
393 RETURN VARCHAR2 AS
394 l_api_name CONSTANT VARCHAR2 (30)
395 := 'get_contract_decline_clause';
396 l_contract_decline_clause VARCHAR2 (4000) := '';
397 l_customer_name VARCHAR2 (1000) := '';
398 l_vendor_name VARCHAR2 (1000) := '';
399 l_customer_token_exists VARCHAR2 (1) := '';
400 l_vendor_token_exists VARCHAR2 (1) := '';
401
402 CURSOR csr_customer_name IS
403 SELECT p.party_name AS customer_name
404 FROM okc_k_party_roles_b r,
405 hz_parties p
406 WHERE p.party_id = r.object1_id1
407 AND r.jtot_object1_code = 'OKX_PARTY'
408 AND r.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
409 -- gets only the CUSTOMER /SUBSCRIBER
410 AND r.cle_id IS NULL
411 AND r.chr_id = p_chr_id;
412
413 CURSOR csr_vendor_name IS
414 SELECT o.NAME AS vendor_name
415 FROM okc_k_party_roles_b r,
416 hr_all_organization_units o
417 WHERE o.organization_id = r.object1_id1
418 AND r.jtot_object1_code = 'OKX_OPERUNIT'
419 AND r.rle_code IN
420 ('VENDOR', 'MERCHANT') -- gets only the VENDOR / MERCHANT
421 AND r.cle_id IS NULL
422 AND r.chr_id = p_chr_id;
423
424 CURSOR csr_customer_token_exists IS
425 SELECT 'Y'
426 FROM fnd_new_messages
427 WHERE message_name = 'OKS_CUST_DECLINE_CLAUSE'
428 AND language_code = USERENV ('LANG')
429 AND regexp_like (MESSAGE_TEXT,
430 'CUSTOMER_NAME',
431 'c'
432 );
433
434 CURSOR csr_vendor_token_exists IS
435 SELECT 'Y'
436 FROM fnd_new_messages
437 WHERE message_name = 'OKS_CUST_DECLINE_CLAUSE'
438 AND language_code = USERENV ('LANG')
439 AND regexp_like (MESSAGE_TEXT,
440 'VENDOR_NAME',
441 'c'
442 );
443 BEGIN
444 -- start debug log
445 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
446 fnd_log.STRING (fnd_log.level_procedure,
447 g_module ||
448 l_api_name,
449 '100: Entered ' ||
450 g_pkg_name ||
451 '.' ||
452 l_api_name
453 );
454 END IF;
455
456 -- check if Customer Name token exists
457 OPEN csr_customer_token_exists;
458
459 FETCH csr_customer_token_exists
460 INTO l_customer_token_exists;
461
462 IF csr_customer_token_exists%FOUND THEN
463 -- Customer Name token exists, get the name
464 OPEN csr_customer_name;
465
466 FETCH csr_customer_name
467 INTO l_customer_name;
468
469 CLOSE csr_customer_name;
470 ELSE
471 -- Customer Name token does not exists
472 l_customer_token_exists := 'N';
473 END IF;
474
475 CLOSE csr_customer_token_exists;
476
477 -- check if Vendor Name token exists
478 OPEN csr_vendor_token_exists;
479
480 FETCH csr_vendor_token_exists
481 INTO l_vendor_token_exists;
482
483 IF csr_vendor_token_exists%FOUND THEN
484 -- Vendor Name token exists, get the name
485 OPEN csr_vendor_name;
486
487 FETCH csr_vendor_name
488 INTO l_vendor_name;
489
490 CLOSE csr_vendor_name;
491 ELSE
492 -- Vendor Name token does not exists
493 l_vendor_token_exists := 'N';
494 END IF;
495
496 CLOSE csr_vendor_token_exists;
497
498 -- set the fnd message for decline clause
499 fnd_message.set_name ('OKS', 'OKS_CUST_DECLINE_CLAUSE');
500
501 IF l_customer_token_exists = 'Y' THEN
502 fnd_message.set_token ('CUSTOMER_NAME', l_customer_name);
503 END IF;
504
505 IF l_vendor_token_exists = 'Y' THEN
506 fnd_message.set_token ('VENDOR_NAME', l_vendor_name);
507 END IF;
508
509 l_contract_decline_clause := fnd_message.get;
510
511 -- end debug log
512 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
513 fnd_log.STRING (fnd_log.level_procedure,
514 g_module ||
515 l_api_name,
516 '1000: Leaving ' ||
517 g_pkg_name ||
518 '.' ||
519 l_api_name
520 );
521 END IF;
522
523 RETURN l_contract_decline_clause;
524 EXCEPTION
525 WHEN OTHERS THEN
526 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
527 fnd_log.STRING (fnd_log.level_procedure,
528 g_module ||
529 l_api_name,
530 '4000: Leaving ' ||
531 g_pkg_name ||
532 '.' ||
533 l_api_name
534 );
535 END IF;
536
537 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
538 RETURN l_contract_decline_clause;
539 END get_contract_decline_clause;
540
541 FUNCTION get_contract_vendor (
542 p_chr_id IN NUMBER
543 )
544 RETURN VARCHAR2 AS
545 l_api_name CONSTANT VARCHAR2 (30)
546 := 'get_contract_vendor';
547 l_vendor_name VARCHAR2 (1000) := '';
548
549 CURSOR csr_vendor_name IS
550 SELECT o.NAME AS vendor_name
551 FROM okc_k_party_roles_b r,
552 hr_all_organization_units o
553 WHERE o.organization_id = r.object1_id1
554 AND r.jtot_object1_code = 'OKX_OPERUNIT'
555 AND r.rle_code = 'VENDOR' -- gets only the VENDOR
556 AND r.cle_id IS NULL
557 AND r.chr_id = p_chr_id;
558 BEGIN
559 -- start debug log
560 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
561 fnd_log.STRING (fnd_log.level_procedure,
562 g_module ||
563 l_api_name,
564 '100: Entered ' ||
565 g_pkg_name ||
566 '.' ||
567 l_api_name
568 );
569 END IF;
570
571 OPEN csr_vendor_name;
572
573 FETCH csr_vendor_name
574 INTO l_vendor_name;
575
576 CLOSE csr_vendor_name;
577
578 -- end debug log
579 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
580 fnd_log.STRING (fnd_log.level_procedure,
581 g_module ||
582 l_api_name,
583 '1000: Leaving ' ||
584 g_pkg_name ||
585 '.' ||
586 l_api_name
587 );
588 END IF;
589
590 RETURN l_vendor_name;
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
594 fnd_log.STRING (fnd_log.level_procedure,
595 g_module ||
596 l_api_name,
597 '4000: Leaving ' ||
598 g_pkg_name ||
599 '.' ||
600 l_api_name
601 );
602 END IF;
603
604 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
605 RETURN l_vendor_name;
606 END get_contract_vendor;
607
608 FUNCTION get_contract_customer (
609 p_chr_id IN NUMBER
610 )
611 RETURN VARCHAR2 AS
612 l_api_name CONSTANT VARCHAR2 (30)
613 := 'get_contract_customer';
614 l_customer_name VARCHAR2 (1000) := '';
615
616 CURSOR csr_customer_name IS
617 SELECT p.party_name AS customer_name
618 FROM okc_k_party_roles_b r,
619 hz_parties p
620 WHERE p.party_id = r.object1_id1
621 AND r.jtot_object1_code = 'OKX_PARTY'
622 AND r.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
623 -- gets only the CUSTOMER /SUBSCRIBER
624 AND r.cle_id IS NULL
625 AND r.dnz_chr_id = p_chr_id; /* changed chr_id to dnz_chr_id for
626 bug6439795 */
627 BEGIN
628 -- start debug log
629 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
630 fnd_log.STRING (fnd_log.level_procedure,
631 g_module ||
632 l_api_name,
633 '100: Entered ' ||
634 g_pkg_name ||
635 '.' ||
636 l_api_name
637 );
638 END IF;
639
640 OPEN csr_customer_name;
641
642 FETCH csr_customer_name
643 INTO l_customer_name;
644
645 CLOSE csr_customer_name;
646
647 -- end debug log
648 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
649 fnd_log.STRING (fnd_log.level_procedure,
650 g_module ||
651 l_api_name,
652 '1000: Leaving ' ||
653 g_pkg_name ||
654 '.' ||
655 l_api_name
656 );
657 END IF;
658
659 RETURN l_customer_name;
660 EXCEPTION
661 WHEN OTHERS THEN
662 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
663 fnd_log.STRING (fnd_log.level_procedure,
664 g_module ||
665 l_api_name,
666 '4000: Leaving ' ||
667 g_pkg_name ||
668 '.' ||
669 l_api_name
670 );
671 END IF;
672
673 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
674 RETURN l_customer_name;
675 END get_contract_customer;
676
677 ---------------------------------------------------
678 FUNCTION get_contract_party (
679 p_chr_id IN NUMBER
680 )
681 RETURN NUMBER AS
682 l_api_name CONSTANT VARCHAR2 (30)
683 := 'get_contract_party';
684 l_party_id NUMBER := '';
685
686 CURSOR csr_k_party IS
687 SELECT object1_id1
688 FROM okc_k_party_roles_b
689 WHERE dnz_chr_id = p_chr_id
690 AND cle_id IS NULL
691 AND jtot_object1_code = 'OKX_PARTY'
692 AND rle_code = 'CUSTOMER';
693 BEGIN
694 -- start debug log
695 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
696 fnd_log.STRING (fnd_log.level_procedure,
697 g_module ||
698 l_api_name,
699 '100: Entered ' ||
700 g_pkg_name ||
701 '.' ||
702 l_api_name
703 );
704 END IF;
705
706 OPEN csr_k_party;
707
708 FETCH csr_k_party
709 INTO l_party_id;
710
711 CLOSE csr_k_party;
712
713 -- end debug log
714 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
715 fnd_log.STRING (fnd_log.level_procedure,
716 g_module ||
717 l_api_name,
718 '1000: Leaving ' ||
719 g_pkg_name ||
720 '.' ||
721 l_api_name
722 );
723 END IF;
724
725 RETURN l_party_id;
726 EXCEPTION
727 WHEN OTHERS THEN
728 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
729 fnd_log.STRING (fnd_log.level_procedure,
730 g_module ||
731 l_api_name,
732 '4000: Leaving ' ||
733 g_pkg_name ||
734 '.' ||
735 l_api_name
736 );
737 END IF;
738
739 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
740 RETURN l_party_id;
741 END get_contract_party;
742
743 ---------------------------------------------------
744 FUNCTION get_contract_organization (
745 p_chr_id IN NUMBER
746 )
747 RETURN NUMBER AS
748 l_api_name CONSTANT VARCHAR2 (30)
749 := 'get_contract_organization';
750 l_organization_id NUMBER := '';
751
752 CURSOR csr_k_organization IS
753 SELECT authoring_org_id
754 FROM okc_k_headers_all_b
755 WHERE ID = p_chr_id;
756 BEGIN
757 -- start debug log
758 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
759 fnd_log.STRING (fnd_log.level_procedure,
760 g_module ||
761 l_api_name,
762 '100: Entered ' ||
763 g_pkg_name ||
764 '.' ||
765 l_api_name
766 );
767 END IF;
768
769 OPEN csr_k_organization;
770
771 FETCH csr_k_organization
772 INTO l_organization_id;
773
774 CLOSE csr_k_organization;
775
776 -- end debug log
777 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
778 fnd_log.STRING (fnd_log.level_procedure,
779 g_module ||
780 l_api_name,
781 '1000: Leaving ' ||
782 g_pkg_name ||
783 '.' ||
784 l_api_name
785 );
786 END IF;
787
788 RETURN l_organization_id;
789 EXCEPTION
790 WHEN OTHERS THEN
791 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
792 fnd_log.STRING (fnd_log.level_procedure,
793 g_module ||
794 l_api_name,
795 '4000: Leaving ' ||
796 g_pkg_name ||
797 '.' ||
798 l_api_name
799 );
800 END IF;
801
802 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
803 RETURN l_organization_id;
804 END get_contract_organization;
805
806 ---------------------------------------------------
807 -- bug 4918198
808 -- The below function would return the Name of salesrep
809 -- instead of the salesrep email
810
811 FUNCTION get_contract_salesrep_email (
812 p_chr_id IN NUMBER
813 )
814 RETURN VARCHAR2 AS
815 l_api_name CONSTANT VARCHAR2 (30)
816 := 'get_contract_salesrep_email';
817 l_salesrep_email VARCHAR2 (1000) := '';
818
819 CURSOR csr_k_salesrep IS
820 SELECT res.resource_name AS salesrep_name
821 FROM okc_k_headers_all_b khr,
822 okc_contacts ct,
823 jtf_rs_salesreps srp,
824 jtf_rs_resource_extns_vl res
825 WHERE khr.ID = ct.dnz_chr_id
826 AND ct.object1_id1 = srp.salesrep_id
827 AND srp.resource_id = res.resource_id
828 AND srp.org_id = khr.authoring_org_id
829 AND ct.jtot_object1_code='OKX_SALEPERS' --bug 6243682
830 AND res.CATEGORY IN
831 ('EMPLOYEE', 'OTHER', 'PARTY', 'PARTNER', 'SUPPLIER_CONTACT')
832 -- AND srp.email_address IS NOT NULL -- bug 4918198
833 AND res.user_name IS NOT NULL -- Salesrep MUST be a FND USER
834 AND khr.ID = p_chr_id;
835
836 -- bug 5218842
837 CURSOR csr_party_helpdesk (
838 p_k_party_id IN NUMBER
839 ) IS
840 SELECT per.full_name help_desk_name
841 FROM jtf_rs_resource_extns jtfrse,
842 oks_k_defaults gcd,
843 per_all_people_f per
844 WHERE jtfrse.user_id = gcd.user_id
845 AND per.person_id = jtfrse.source_id
846 AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
847 FROM per_all_people_f a
848 WHERE a.person_id = per.person_id)
849 AND gcd.cdt_type = 'SDT'
850 AND gcd.jtot_object_code = 'OKX_PARTY'
851 AND jtfrse.category = 'EMPLOYEE'
852 AND gcd.segment_id1 = p_k_party_id;
853 /*
854 SELECT hd.help_desk_name
855 FROM oks_k_defaults gcd,
856 oks_help_desk_v hd
857 WHERE gcd.cdt_type = 'SDT'
858 AND gcd.jtot_object_code = 'OKX_PARTY'
859 AND gcd.user_id = hd.user_id
860 -- AND gcd.email_address IS NOT NULL
861 AND gcd.segment_id1 = p_k_party_id;
862 */
863
864 -- bug 5218842
865 CURSOR csr_org_helpdesk (
866 p_k_org_id IN NUMBER
867 ) IS
868 SELECT per.full_name help_desk_name
869 FROM jtf_rs_resource_extns jtfrse,
870 oks_k_defaults gcd,
871 per_all_people_f per
872 WHERE jtfrse.user_id = gcd.user_id
873 AND per.person_id = jtfrse.source_id
874 AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
875 FROM per_all_people_f a
876 WHERE a.person_id = per.person_id)
877 AND gcd.cdt_type = 'SDT'
878 AND gcd.jtot_object_code = 'OKX_OPERUNIT'
879 AND jtfrse.category = 'EMPLOYEE'
880 AND gcd.segment_id1 = p_k_org_id;
881 /*
882 SELECT hd.help_desk_name
883 FROM oks_k_defaults gcd,
884 oks_help_desk_v hd
885 WHERE gcd.cdt_type = 'SDT'
886 AND gcd.jtot_object_code = 'OKX_OPERUNIT'
887 AND gcd.user_id = hd.user_id
888 -- AND gcd.email_address IS NOT NULL
889 AND gcd.segment_id1 = p_k_org_id;
890 */
891
892 -- bug 5218842
893 CURSOR csr_global_helpdesk IS
894 SELECT per.full_name help_desk_name
895 FROM jtf_rs_resource_extns jtfrse,
896 oks_k_defaults gcd,
897 per_all_people_f per
898 WHERE jtfrse.user_id = gcd.user_id
899 AND per.person_id = jtfrse.source_id
900 AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
901 FROM per_all_people_f a
902 WHERE a.person_id = per.person_id)
903 AND gcd.cdt_type = 'MDT'
904 AND jtfrse.category = 'EMPLOYEE';
905 /*
906 SELECT hd.help_desk_name
907 FROM oks_k_defaults gcd,
908 oks_help_desk_v hd
909 WHERE gcd.user_id = hd.user_id
910 AND gcd.cdt_type = 'MDT';
911 */
912 BEGIN
913 -- start debug log
914 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
915 fnd_log.STRING (fnd_log.level_procedure,
916 g_module ||
917 l_api_name,
918 '100: Entered ' ||
919 g_pkg_name ||
920 '.' ||
921 l_api_name
922 );
923 END IF;
924
925 OPEN csr_k_salesrep;
926
927 FETCH csr_k_salesrep
928 INTO l_salesrep_email;
929
930 IF csr_k_salesrep%FOUND THEN
931 -- Salesrep exist on K
932 CLOSE csr_k_salesrep;
933
934 RETURN l_salesrep_email;
935 END IF; -- k_salesrep csr
936
937 CLOSE csr_k_salesrep;
938
939 -- Go to GCD at party level
940 OPEN csr_party_helpdesk (p_k_party_id => get_contract_party
941 (p_chr_id));
942
943 FETCH csr_party_helpdesk
944 INTO l_salesrep_email;
945
946 IF csr_party_helpdesk%FOUND THEN
947 CLOSE csr_party_helpdesk;
948
949 RETURN l_salesrep_email;
950 END IF; -- helpdesk on party level
951
952 CLOSE csr_party_helpdesk;
953
954 -- Go to GCD at organization level
955 OPEN csr_org_helpdesk (p_k_org_id => get_contract_organization
956 (p_chr_id));
957
958 FETCH csr_org_helpdesk
959 INTO l_salesrep_email;
960
961 IF csr_org_helpdesk%FOUND THEN
962 CLOSE csr_org_helpdesk;
963
964 RETURN l_salesrep_email;
965 END IF; -- helpdesk on party level
966
967 CLOSE csr_org_helpdesk;
968
969 -- Go to GCD at global level
970 OPEN csr_global_helpdesk;
971
972 FETCH csr_global_helpdesk
973 INTO l_salesrep_email;
974
975 CLOSE csr_global_helpdesk;
976
977 -- end debug log
978 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
979 fnd_log.STRING (fnd_log.level_procedure,
980 g_module ||
981 l_api_name,
982 '1000: Leaving ' ||
983 g_pkg_name ||
984 '.' ||
985 l_api_name
986 );
987 END IF;
988
989 RETURN l_salesrep_email;
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
993 fnd_log.STRING (fnd_log.level_procedure,
994 g_module ||
995 l_api_name,
996 '4000: Leaving ' ||
997 g_pkg_name ||
998 '.' ||
999 l_api_name
1000 );
1001 END IF;
1002
1003 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1004 RETURN l_salesrep_email;
1005 END get_contract_salesrep_email;
1006
1007 ---------------------------------------------------
1008 FUNCTION get_contract_cust_account_id (
1009 p_chr_id IN NUMBER
1010 )
1011 RETURN NUMBER AS
1012 l_api_name CONSTANT VARCHAR2 (30)
1013 := 'get_contract_cust_account_id';
1014 l_cust_account_id NUMBER := '';
1015
1016 CURSOR csr_cust_account_dtls IS
1017 SELECT p.party_name AS customer_name,
1018 r.object1_id1 AS party_id,
1019 ca.cust_account_id AS customer_account_id
1020 FROM okc_k_party_roles_b r,
1021 hz_parties p,
1022 hz_cust_accounts ca
1023 WHERE p.party_id = r.object1_id1
1024 AND ca.party_id = p.party_id
1025 AND r.jtot_object1_code = 'OKX_PARTY'
1026 AND r.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
1027 -- gets only the CUSTOMER /SUBSCRIBER
1028 AND r.cle_id IS NULL
1029 AND r.chr_id = p_chr_id;
1030
1031 l_csr_cust_account_id_rec csr_cust_account_dtls%ROWTYPE;
1032 BEGIN
1033 -- start debug log
1034 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1035 fnd_log.STRING (fnd_log.level_procedure,
1036 g_module ||
1037 l_api_name,
1038 '100: Entered ' ||
1039 g_pkg_name ||
1040 '.' ||
1041 l_api_name
1042 );
1043 END IF;
1044
1045 -- set context to multi org
1046 -- mo_global.init ('OKC');
1047
1048 OPEN csr_cust_account_dtls;
1049
1050 FETCH csr_cust_account_dtls
1051 INTO l_csr_cust_account_id_rec;
1052
1053 CLOSE csr_cust_account_dtls;
1054
1055 l_cust_account_id :=
1056 l_csr_cust_account_id_rec.customer_account_id;
1057
1058 -- end debug log
1059 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1060 fnd_log.STRING (fnd_log.level_procedure,
1061 g_module ||
1062 l_api_name,
1063 '1000: Leaving ' ||
1064 g_pkg_name ||
1065 '.' ||
1066 l_api_name
1067 );
1068 END IF;
1069
1070 RETURN l_cust_account_id;
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1074 fnd_log.STRING (fnd_log.level_procedure,
1075 g_module ||
1076 l_api_name,
1077 '4000: Leaving ' ||
1078 g_pkg_name ||
1079 '.' ||
1080 l_api_name
1081 );
1082 END IF;
1083
1084 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1085 RETURN l_cust_account_id;
1086 END get_contract_cust_account_id;
1087
1088 ---------------------------------------------------
1089 FUNCTION get_req_ass_email_subject (
1090 p_chr_id IN NUMBER
1091 )
1092 RETURN VARCHAR2 AS
1093 l_api_name CONSTANT VARCHAR2 (30)
1094 := 'get_req_ass_email_subject';
1095 l_email_subject VARCHAR2 (4000) := '';
1096 l_contract_number VARCHAR2 (1000) := '';
1097
1098 CURSOR csr_k_number IS
1099 SELECT kc.contract_number ||
1100 DECODE (kc.contract_number_modifier,
1101 NULL, NULL,
1102 '-'
1103 ) ||
1104 kc.contract_number_modifier AS contract_number
1105 FROM okc_k_headers_all_b kc
1106 WHERE kc.ID = p_chr_id;
1107 BEGIN
1108 -- start debug log
1109 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1110 fnd_log.STRING (fnd_log.level_procedure,
1111 g_module ||
1112 l_api_name,
1113 '100: Entered ' ||
1114 g_pkg_name ||
1115 '.' ||
1116 l_api_name
1117 );
1118 END IF;
1119
1120 OPEN csr_k_number;
1121
1122 FETCH csr_k_number
1123 INTO l_contract_number;
1124
1125 CLOSE csr_k_number;
1126
1127 -- build the email subject message
1128 -- set the fnd message for email subject
1129 fnd_message.set_name ('OKS', 'OKS_CUST_ACCEPT_EMAIL_SUB');
1130 fnd_message.set_token ('CONTRACT_NUM', l_contract_number);
1131 l_email_subject := fnd_message.get;
1132
1133 -- end debug log
1134 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1135 fnd_log.STRING (fnd_log.level_procedure,
1136 g_module ||
1137 l_api_name,
1138 '1000: Leaving ' ||
1139 g_pkg_name ||
1140 '.' ||
1141 l_api_name
1142 );
1143 END IF;
1144
1145 RETURN l_email_subject;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1149 fnd_log.STRING (fnd_log.level_procedure,
1150 g_module ||
1151 l_api_name,
1152 '4000: Leaving ' ||
1153 g_pkg_name ||
1154 '.' ||
1155 l_api_name
1156 );
1157 END IF;
1158
1159 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1160 RETURN l_email_subject;
1161 END get_req_ass_email_subject;
1162
1163 ---------------------------------------------------
1164 FUNCTION duration_unit_and_period (
1165 p_start_date IN DATE,
1166 p_end_date IN DATE
1167 )
1168 RETURN VARCHAR2 IS
1169 l_api_name CONSTANT VARCHAR2 (30)
1170 := 'duration_unit_and_period';
1171 l_duration NUMBER;
1172 l_timeunit VARCHAR2 (100) := '';
1173 l_timeunit_desc VARCHAR2 (1000) := '';
1174 l_duration_period VARCHAR2 (4000) := '';
1175 l_return_status VARCHAR2 (1);
1176
1177 CURSOR csr_timeunit_desc (
1178 p_code IN VARCHAR2
1179 ) IS
1180 SELECT short_description
1181 FROM okc_time_code_units_v
1182 WHERE uom_code = p_code
1183 AND active_flag = 'Y';
1184 BEGIN
1185 -- start debug log
1186 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1187 fnd_log.STRING (fnd_log.level_procedure,
1188 g_module ||
1189 l_api_name,
1190 '100: Entered ' ||
1191 g_pkg_name ||
1192 '.' ||
1193 l_api_name
1194 );
1195 fnd_log.STRING (fnd_log.level_procedure,
1196 g_module ||
1197 l_api_name,
1198 '100: Parameters : p_start_date : ' ||
1199 p_start_date
1200 );
1201 fnd_log.STRING (fnd_log.level_procedure,
1202 g_module ||
1203 l_api_name,
1204 '100: p_end_date : ' ||
1205 p_end_date
1206 );
1207 fnd_log.STRING (fnd_log.level_procedure,
1208 g_module ||
1209 l_api_name,
1210 '100: Calling OKC_TIME_UTIL_PUB.get_duration'
1211 );
1212 END IF;
1213
1214 okc_time_util_pub.get_duration (p_start_date => p_start_date,
1215 p_end_date => p_end_date,
1216 x_duration => l_duration,
1217 x_timeunit => l_timeunit,
1218 x_return_status => l_return_status
1219 );
1220
1221 -- debug log
1222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1223 fnd_log.STRING (fnd_log.level_procedure,
1224 g_module ||
1225 l_api_name,
1226 '150: After Calling OKC_TIME_UTIL_PUB.get_duration'
1227 );
1228 fnd_log.STRING (fnd_log.level_procedure,
1229 g_module ||
1230 l_api_name,
1231 '150: x_return_status : ' ||
1232 l_return_status
1233 );
1234 fnd_log.STRING (fnd_log.level_procedure,
1235 g_module ||
1236 l_api_name,
1237 '150: x_duration : ' ||
1238 l_duration
1239 );
1240 fnd_log.STRING (fnd_log.level_procedure,
1241 g_module ||
1242 l_api_name,
1243 '150: x_timeunit : ' ||
1244 l_timeunit
1245 );
1246 END IF;
1247
1248 OPEN csr_timeunit_desc (p_code => l_timeunit);
1249
1250 FETCH csr_timeunit_desc
1251 INTO l_timeunit_desc;
1252
1253 CLOSE csr_timeunit_desc;
1254
1255 -- debug log
1256 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1257 fnd_log.STRING (fnd_log.level_procedure,
1258 g_module ||
1259 l_api_name,
1260 '200: l_timeunit_desc : ' ||
1261 l_timeunit_desc
1262 );
1263 fnd_log.STRING (fnd_log.level_procedure,
1264 g_module ||
1265 l_api_name,
1266 '1000: Leaving ' ||
1267 g_pkg_name ||
1268 '.' ||
1269 l_api_name
1270 );
1271 END IF;
1272
1273 l_duration_period := l_duration ||
1274 ' ' ||
1275 l_timeunit_desc;
1276 RETURN l_duration_period;
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1280 fnd_log.STRING (fnd_log.level_procedure,
1281 g_module ||
1282 l_api_name,
1283 '4000: Leaving ' ||
1284 g_pkg_name ||
1285 '.' ||
1286 l_api_name
1287 );
1288 END IF;
1289
1290 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1291 RETURN l_duration_period;
1292 END duration_unit_and_period;
1293
1294 ---------------------------------------------------
1295 FUNCTION get_credit_card_dtls (
1296 p_trxn_extension_id IN NUMBER
1297 )
1298 RETURN VARCHAR2 IS
1299 l_api_name CONSTANT VARCHAR2 (30)
1300 := 'get_credit_card_dtls';
1301 l_cc_detail VARCHAR2 (2000) := '';
1302
1303 CURSOR csr_cc_dtls IS
1304 SELECT ibyt.card_number ||
1305 ' , ' ||
1306 ibyt.card_issuer_name ||
1307 ' , ' ||
1308 TO_CHAR (ibyt.card_expirydate, 'MM/YYYY') AS cc_number
1309 FROM iby_trxn_extensions_v ibyt
1310 WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
1311 BEGIN
1312 -- start debug log
1313 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1314 fnd_log.STRING (fnd_log.level_procedure,
1315 g_module ||
1316 l_api_name,
1317 '100: Entered ' ||
1318 g_pkg_name ||
1319 '.' ||
1320 l_api_name
1321 );
1322 fnd_log.STRING (fnd_log.level_procedure,
1323 g_module ||
1324 l_api_name,
1325 '100: Parameters : p_trxn_extension_id : ' ||
1326 p_trxn_extension_id
1327 );
1328 END IF;
1329
1330 OPEN csr_cc_dtls;
1331
1332 FETCH csr_cc_dtls
1333 INTO l_cc_detail;
1334
1335 CLOSE csr_cc_dtls;
1336
1337 -- debug log
1338 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1339 fnd_log.STRING (fnd_log.level_procedure,
1340 g_module ||
1341 l_api_name,
1342 '200: l_cc_detail : ' ||
1343 l_cc_detail
1344 );
1345 fnd_log.STRING (fnd_log.level_procedure,
1346 g_module ||
1347 l_api_name,
1348 '1000: Leaving ' ||
1349 g_pkg_name ||
1350 '.' ||
1351 l_api_name
1352 );
1353 END IF;
1354
1355 RETURN l_cc_detail;
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1359 fnd_log.STRING (fnd_log.level_procedure,
1360 g_module ||
1361 l_api_name,
1362 '4000: Leaving ' ||
1363 g_pkg_name ||
1364 '.' ||
1365 l_api_name
1366 );
1367 END IF;
1368
1369 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1370 RETURN l_cc_detail;
1371 END get_credit_card_dtls;
1372
1373 ---------------------------------------------------
1374 FUNCTION get_credit_card_cvv2 (
1375 p_trxn_extension_id IN NUMBER
1376 )
1377 RETURN VARCHAR2 IS
1378 l_api_name CONSTANT VARCHAR2 (30)
1379 := 'get_credit_card_cvv2';
1380 l_cc_cvv2 VARCHAR2 (2000) := '';
1381
1382 CURSOR csr_cc_dtls IS
1383 SELECT ibyt.instrument_security_code AS cc_cvv2
1384 FROM iby_trxn_extensions_v ibyt
1385 WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
1386 BEGIN
1387 -- start debug log
1388 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1389 fnd_log.STRING (fnd_log.level_procedure,
1390 g_module ||
1391 l_api_name,
1392 '100: Entered ' ||
1393 g_pkg_name ||
1394 '.' ||
1395 l_api_name
1396 );
1397 fnd_log.STRING (fnd_log.level_procedure,
1398 g_module ||
1399 l_api_name,
1400 '100: Parameters : p_trxn_extension_id : ' ||
1401 p_trxn_extension_id
1402 );
1403 END IF;
1404
1405 OPEN csr_cc_dtls;
1406
1407 FETCH csr_cc_dtls
1408 INTO l_cc_cvv2;
1409
1410 CLOSE csr_cc_dtls;
1411
1412 -- debug log
1413 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1414 fnd_log.STRING (fnd_log.level_procedure,
1415 g_module ||
1416 l_api_name,
1417 '200: l_cc_cvv2 : ' ||
1418 l_cc_cvv2
1419 );
1420 fnd_log.STRING (fnd_log.level_procedure,
1421 g_module ||
1422 l_api_name,
1423 '1000: Leaving ' ||
1424 g_pkg_name ||
1425 '.' ||
1426 l_api_name
1427 );
1428 END IF;
1429
1430 RETURN l_cc_cvv2;
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1434 fnd_log.STRING (fnd_log.level_procedure,
1435 g_module ||
1436 l_api_name,
1437 '4000: Leaving ' ||
1438 g_pkg_name ||
1439 '.' ||
1440 l_api_name
1441 );
1442 END IF;
1443
1444 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1445 RETURN l_cc_cvv2;
1446 END get_credit_card_cvv2;
1447
1448 ---------------------------------------------------
1449 FUNCTION get_contract_currency_tip (
1450 p_chr_id IN NUMBER
1451 )
1452 RETURN VARCHAR2 IS
1453 l_api_name CONSTANT VARCHAR2 (30)
1454 := 'get_contract_currency_tip';
1455 l_currency_code_tip VARCHAR2 (2000) := '';
1456
1457 CURSOR csr_currency_tip IS
1458 SELECT k.currency_code ||
1459 ' = ' ||
1460 f.NAME
1461 FROM okc_k_headers_all_b k,
1462 fnd_currencies_tl f
1463 WHERE k.currency_code = f.currency_code
1464 AND f.LANGUAGE = USERENV ('LANG')
1465 AND k.ID = p_chr_id;
1466 BEGIN
1467 -- start debug log
1468 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1469 fnd_log.STRING (fnd_log.level_procedure,
1470 g_module ||
1471 l_api_name,
1472 '100: Entered ' ||
1473 g_pkg_name ||
1474 '.' ||
1475 l_api_name
1476 );
1477 fnd_log.STRING (fnd_log.level_procedure,
1478 g_module ||
1479 l_api_name,
1480 '100: Parameters : p_chr_id : ' ||
1481 p_chr_id
1482 );
1483 END IF;
1484
1485 OPEN csr_currency_tip;
1486
1487 FETCH csr_currency_tip
1488 INTO l_currency_code_tip;
1489
1490 CLOSE csr_currency_tip;
1491
1492 -- debug log
1493 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1494 fnd_log.STRING (fnd_log.level_procedure,
1495 g_module ||
1496 l_api_name,
1497 '200: l_currency_code_tip : ' ||
1498 l_currency_code_tip
1499 );
1500 fnd_log.STRING (fnd_log.level_procedure,
1501 g_module ||
1502 l_api_name,
1503 '1000: Leaving ' ||
1504 g_pkg_name ||
1505 '.' ||
1506 l_api_name
1507 );
1508 END IF;
1509
1510 RETURN l_currency_code_tip;
1511 EXCEPTION
1512 WHEN OTHERS THEN
1513 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1514 fnd_log.STRING (fnd_log.level_procedure,
1515 g_module ||
1516 l_api_name,
1517 '4000: Leaving ' ||
1518 g_pkg_name ||
1519 '.' ||
1520 l_api_name
1521 );
1522 END IF;
1523
1524 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1525 RETURN l_currency_code_tip;
1526 END get_contract_currency_tip;
1527
1528 ---------------------------------------------------
1529 PROCEDURE decline_contract (
1530 p_api_version IN NUMBER,
1531 p_init_msg_list IN VARCHAR2,
1532 p_chr_id IN NUMBER,
1533 p_reason_code IN VARCHAR2,
1534 p_decline_reason IN VARCHAR2,
1535 x_return_status OUT NOCOPY VARCHAR2,
1536 x_msg_data OUT NOCOPY VARCHAR2,
1537 x_msg_count OUT NOCOPY NUMBER
1538 ) AS
1539 l_api_version CONSTANT NUMBER := 1;
1540 l_api_name CONSTANT VARCHAR2 (30)
1541 := 'decline_contract';
1542 BEGIN
1543 -- start debug log
1544 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1545 fnd_log.STRING (fnd_log.level_procedure,
1546 g_module ||
1547 l_api_name,
1548 '100: Entered ' ||
1549 g_pkg_name ||
1550 '.' ||
1551 l_api_name
1552 );
1553 END IF;
1554
1555 -- Standard call to check for call compatibility.
1556 IF NOT fnd_api.compatible_api_call (l_api_version,
1557 p_api_version,
1558 l_api_name,
1559 g_pkg_name
1560 ) THEN
1561 RAISE fnd_api.g_exc_unexpected_error;
1562 END IF;
1563
1564 -- Initialize message list if p_init_msg_list is set to TRUE.
1565 IF fnd_api.to_boolean (p_init_msg_list) THEN
1566 fnd_msg_pub.initialize;
1567 END IF;
1568
1569 -- Initialize API return status to success
1570 x_return_status := fnd_api.g_ret_sts_success;
1571 -- set context to multi org
1572 -- mo_global.init ('OKC');
1573 oks_wf_k_process_pvt.customer_decline_quote
1574 (p_api_version => p_api_version,
1575 p_init_msg_list => p_init_msg_list,
1576 p_commit => g_true,
1577 p_contract_id => p_chr_id,
1578 p_item_key => NULL,
1579 p_reason_code => p_reason_code,
1580 p_comments => p_decline_reason,
1581 x_return_status => x_return_status,
1582 x_msg_data => x_msg_data,
1583 x_msg_count => x_msg_count
1584 );
1585
1586 --- If any errors happen abort API
1587 IF (x_return_status = g_ret_sts_unexp_error) THEN
1588 RAISE fnd_api.g_exc_unexpected_error;
1589 ELSIF (x_return_status = g_ret_sts_error) THEN
1590 RAISE fnd_api.g_exc_error;
1591 END IF;
1592
1593 -- Standard call to get message count and if count is 1, get message info.
1594 fnd_msg_pub.count_and_get (p_encoded => 'F',
1595 p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598
1599 -- end debug log
1600 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1601 fnd_log.STRING (fnd_log.level_procedure,
1602 g_module ||
1603 l_api_name,
1604 '1000: Leaving ' ||
1605 g_pkg_name ||
1606 '.' ||
1607 l_api_name
1608 );
1609 END IF;
1610 EXCEPTION
1611 WHEN fnd_api.g_exc_error THEN
1612 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1613 fnd_log.STRING (fnd_log.level_procedure,
1614 g_module ||
1615 l_api_name,
1616 '2000: Leaving ' ||
1617 g_pkg_name ||
1618 '.' ||
1619 l_api_name
1620 );
1621 END IF;
1622
1623 x_return_status := g_ret_sts_error;
1624 fnd_msg_pub.count_and_get (p_encoded => 'F',
1625 p_count => x_msg_count,
1626 p_data => x_msg_data
1627 );
1628 WHEN fnd_api.g_exc_unexpected_error THEN
1629 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1630 fnd_log.STRING (fnd_log.level_procedure,
1631 g_module ||
1632 l_api_name,
1633 '3000: Leaving ' ||
1634 g_pkg_name ||
1635 '.' ||
1636 l_api_name
1637 );
1638 END IF;
1639
1640 x_return_status := g_ret_sts_unexp_error;
1641 fnd_msg_pub.count_and_get (p_encoded => 'F',
1642 p_count => x_msg_count,
1643 p_data => x_msg_data
1644 );
1645 WHEN OTHERS THEN
1646 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1647 fnd_log.STRING (fnd_log.level_procedure,
1648 g_module ||
1649 l_api_name,
1650 '4000: Leaving ' ||
1651 g_pkg_name ||
1652 '.' ||
1653 l_api_name
1654 );
1655 END IF;
1656
1657 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1658 fnd_msg_pub.count_and_get (p_encoded => 'F',
1659 p_count => x_msg_count,
1660 p_data => x_msg_data
1661 );
1662 END decline_contract;
1663
1664 ---------------------------------------------------
1665 PROCEDURE accept_contract (
1666 p_api_version IN NUMBER,
1667 p_init_msg_list IN VARCHAR2,
1668 p_chr_id IN NUMBER,
1669 x_return_status OUT NOCOPY VARCHAR2,
1670 x_msg_data OUT NOCOPY VARCHAR2,
1671 x_msg_count OUT NOCOPY NUMBER
1672 ) AS
1673 l_api_version CONSTANT NUMBER := 1;
1674 l_api_name CONSTANT VARCHAR2 (30)
1675 := 'accept_contract';
1676 BEGIN
1677 -- start debug log
1678 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1679 fnd_log.STRING (fnd_log.level_procedure,
1680 g_module ||
1681 l_api_name,
1682 '100: Entered ' ||
1683 g_pkg_name ||
1684 '.' ||
1685 l_api_name
1686 );
1687 END IF;
1688
1689 -- Standard call to check for call compatibility.
1690 IF NOT fnd_api.compatible_api_call (l_api_version,
1691 p_api_version,
1692 l_api_name,
1693 g_pkg_name
1694 ) THEN
1695 RAISE fnd_api.g_exc_unexpected_error;
1696 END IF;
1697
1698 -- Initialize message list if p_init_msg_list is set to TRUE.
1699 IF fnd_api.to_boolean (p_init_msg_list) THEN
1700 fnd_msg_pub.initialize;
1701 END IF;
1702
1703 -- Initialize API return status to success
1704 x_return_status := fnd_api.g_ret_sts_success;
1705 -- set context to multi org
1706 -- mo_global.init ('OKC');
1707 oks_wf_k_process_pvt.customer_accept_quote
1708 (p_api_version => p_api_version,
1709 p_init_msg_list => p_init_msg_list,
1710 p_contract_id => p_chr_id,
1711 p_item_key => NULL,
1712 x_return_status => x_return_status,
1713 x_msg_data => x_msg_data,
1714 x_msg_count => x_msg_count
1715 );
1716
1717 --- If any errors happen abort API
1718 IF (x_return_status = g_ret_sts_unexp_error) THEN
1719 RAISE fnd_api.g_exc_unexpected_error;
1720 ELSIF (x_return_status = g_ret_sts_error) THEN
1721 RAISE fnd_api.g_exc_error;
1722 END IF;
1723
1724 -- Standard call to get message count and if count is 1, get message info.
1725 fnd_msg_pub.count_and_get (p_encoded => 'F',
1726 p_count => x_msg_count,
1727 p_data => x_msg_data
1728 );
1729
1730 -- end debug log
1731 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1732 fnd_log.STRING (fnd_log.level_procedure,
1733 g_module ||
1734 l_api_name,
1735 '1000: Leaving ' ||
1736 g_pkg_name ||
1737 '.' ||
1738 l_api_name
1739 );
1740 END IF;
1741 EXCEPTION
1742 WHEN fnd_api.g_exc_error THEN
1743 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1744 fnd_log.STRING (fnd_log.level_procedure,
1745 g_module ||
1746 l_api_name,
1747 '2000: Leaving ' ||
1748 g_pkg_name ||
1749 '.' ||
1750 l_api_name
1751 );
1752 END IF;
1753
1754 x_return_status := g_ret_sts_error;
1755 fnd_msg_pub.count_and_get (p_encoded => 'F',
1756 p_count => x_msg_count,
1757 p_data => x_msg_data
1758 );
1759 WHEN fnd_api.g_exc_unexpected_error THEN
1760 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1761 fnd_log.STRING (fnd_log.level_procedure,
1762 g_module ||
1763 l_api_name,
1764 '3000: Leaving ' ||
1765 g_pkg_name ||
1766 '.' ||
1767 l_api_name
1768 );
1769 END IF;
1770
1771 x_return_status := g_ret_sts_unexp_error;
1772 fnd_msg_pub.count_and_get (p_encoded => 'F',
1773 p_count => x_msg_count,
1774 p_data => x_msg_data
1775 );
1776 WHEN OTHERS THEN
1777 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1778 fnd_log.STRING (fnd_log.level_procedure,
1779 g_module ||
1780 l_api_name,
1781 '4000: Leaving ' ||
1782 g_pkg_name ||
1783 '.' ||
1784 l_api_name
1785 );
1786 END IF;
1787
1788 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1789 fnd_msg_pub.count_and_get (p_encoded => 'F',
1790 p_count => x_msg_count,
1791 p_data => x_msg_data
1792 );
1793 END accept_contract;
1794
1795 ---------------------------------------------------
1796 PROCEDURE update_payment_details (
1797 p_api_version IN NUMBER,
1798 p_init_msg_list IN VARCHAR2,
1799 p_chr_id IN NUMBER,
1800 p_payment_type IN VARCHAR2,
1801 p_payment_details IN VARCHAR2,
1802 p_party_id IN NUMBER,
1803 p_cust_account_id IN NUMBER,
1804 p_card_number IN VARCHAR2 DEFAULT NULL,
1805 p_expiration_month IN VARCHAR2 DEFAULT NULL,
1806 p_expiration_year IN VARCHAR2 DEFAULT NULL,
1807 p_cvv_code IN VARCHAR2 DEFAULT NULL,
1808 p_instr_assignment_id IN NUMBER DEFAULT NULL,
1809 p_old_txn_entension_id IN NUMBER DEFAULT NULL,
1810 x_return_status OUT NOCOPY VARCHAR2,
1811 x_msg_data OUT NOCOPY VARCHAR2,
1812 x_msg_count OUT NOCOPY NUMBER
1813 ) AS
1814 l_api_version CONSTANT NUMBER := 1;
1815 l_api_name CONSTANT VARCHAR2 (30)
1816 := 'update_payment_details';
1817 l_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
1818 x_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
1819
1820 CURSOR csr_billing_address_id IS
1821 SELECT st.party_site_id
1822 FROM okc_k_headers_all_b okc,
1823 hz_cust_site_uses_all su,
1824 hz_cust_acct_sites_all sa,
1825 hz_party_sites st
1826 WHERE okc.bill_to_site_use_id = su.site_use_id
1827 AND su.cust_acct_site_id = sa.cust_acct_site_id
1828 AND sa.party_site_id = st.party_site_id
1829 AND okc.ID = p_chr_id;
1830
1831 CURSOR csr_expiration_date (
1832 p_month IN VARCHAR2,
1833 p_year IN VARCHAR2
1834 ) IS
1835 SELECT LAST_DAY (TO_DATE (p_month ||
1836 '/' ||
1837 p_year, 'MM/YYYY'))
1838 FROM DUAL;
1839
1840 l_trxn_extension_id oks_k_headers_b.trxn_extension_id%TYPE;
1841 l_billing_address_id hz_party_sites.party_site_id%TYPE;
1842 l_expiration_date DATE := '';
1843
1844 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
1845
1846 l_payer l_payer_type;
1847 l_response iby_fndcpt_common_pub.result_rec_type;
1848 BEGIN
1849 -- start debug log
1850 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1851 fnd_log.STRING (fnd_log.level_procedure,
1852 g_module ||
1853 l_api_name,
1854 '100: Entered ' ||
1855 g_pkg_name ||
1856 '.' ||
1857 l_api_name
1858 );
1859 fnd_log.STRING (fnd_log.level_procedure,
1860 g_module ||
1861 l_api_name,
1862 '100: Parameters p_chr_id : ' ||
1863 p_chr_id
1864 );
1865 fnd_log.STRING (fnd_log.level_procedure,
1866 g_module ||
1867 l_api_name,
1868 '100: p_payment_type : ' ||
1869 p_payment_type
1870 );
1871 fnd_log.STRING (fnd_log.level_procedure,
1872 g_module ||
1873 l_api_name,
1874 '100: p_payment_details : ' ||
1875 p_payment_details
1876 );
1877 fnd_log.STRING (fnd_log.level_procedure,
1878 g_module ||
1879 l_api_name,
1880 '100: p_party_id : ' ||
1881 p_party_id
1882 );
1883 fnd_log.STRING (fnd_log.level_procedure,
1884 g_module ||
1885 l_api_name,
1886 '100: p_cust_account_id : ' ||
1887 p_cust_account_id
1888 );
1889 fnd_log.STRING (fnd_log.level_procedure,
1890 g_module ||
1891 l_api_name,
1892 '100: p_expiration_month : ' ||
1893 p_expiration_month
1894 );
1895 fnd_log.STRING (fnd_log.level_procedure,
1896 g_module ||
1897 l_api_name,
1898 '100: p_expiration_year : ' ||
1899 p_expiration_year
1900 );
1901 fnd_log.STRING (fnd_log.level_procedure,
1902 g_module ||
1903 l_api_name,
1904 '100: p_cvv_code : ' ||
1905 p_cvv_code
1906 );
1907 fnd_log.STRING (fnd_log.level_procedure,
1908 g_module ||
1909 l_api_name,
1910 '100: p_instr_assignment_id : ' ||
1911 p_instr_assignment_id
1912 );
1913 fnd_log.STRING (fnd_log.level_procedure,
1914 g_module ||
1915 l_api_name,
1916 '100: p_old_txn_entension_id : ' ||
1917 p_old_txn_entension_id
1918 );
1919 END IF;
1920
1921 -- Standard call to check for call compatibility.
1922 IF NOT fnd_api.compatible_api_call (l_api_version,
1923 p_api_version,
1924 l_api_name,
1925 g_pkg_name
1926 ) THEN
1927 RAISE fnd_api.g_exc_unexpected_error;
1928 END IF;
1929
1930 -- Initialize message list if p_init_msg_list is set to TRUE.
1931 IF fnd_api.to_boolean (p_init_msg_list) THEN
1932 fnd_msg_pub.initialize;
1933 END IF;
1934
1935 -- Initialize API return status to success
1936 x_return_status := fnd_api.g_ret_sts_success;
1937
1938 -- if p_payment_type is USEDCC then don't update credit card information
1939 IF (p_payment_type = 'USEDCC') THEN
1940 RETURN;
1941 END IF;
1942
1943 -- delete any existing old txn extension id record
1944 delete_transaction_extension (p_chr_id => p_chr_id,
1945 p_commit => fnd_api.g_false,
1946 x_return_status => x_return_status,
1947 x_msg_data => x_msg_data,
1948 x_msg_count => x_msg_count
1949 );
1950
1951 --- If any errors happen abort API
1952 IF (x_return_status = g_ret_sts_unexp_error) THEN
1953 RAISE fnd_api.g_exc_unexpected_error;
1954 ELSIF (x_return_status = g_ret_sts_error) THEN
1955 RAISE fnd_api.g_exc_error;
1956 END IF;
1957
1958 -- Get the payment_terms_id1 from GCD
1959 -- debug log
1960 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1961 fnd_log.STRING (fnd_log.level_procedure,
1962 g_module ||
1963 l_api_name,
1964 '210: Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
1965 );
1966 END IF;
1967
1968 -- Call OKS_RENEW_UTIL_PVT.get_renew_rules
1969 oks_renew_util_pvt.get_renew_rules (x_return_status => x_return_status,
1970 p_api_version => 1.0,
1971 p_init_msg_list => g_false,
1972 p_chr_id => p_chr_id,
1973 p_party_id => NULL,
1974 p_org_id => NULL,
1975 p_date => SYSDATE,
1976 p_rnrl_rec => l_rnrl_rec,
1977 x_rnrl_rec => x_rnrl_rec,
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data
1980 );
1981
1982 -- debug log
1983 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1984 fnd_log.STRING (fnd_log.level_procedure,
1985 g_module ||
1986 l_api_name,
1987 '250: After Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
1988 );
1989 fnd_log.STRING (fnd_log.level_procedure,
1990 g_module ||
1991 l_api_name,
1992 '250: x_return_status : ' ||
1993 x_return_status
1994 );
1995 fnd_log.STRING (fnd_log.level_procedure,
1996 g_module ||
1997 l_api_name,
1998 '250:x_rnrl_rec.payment_terms_id1 : ' ||
1999 x_rnrl_rec.payment_terms_id1
2000 );
2001 fnd_log.STRING (fnd_log.level_procedure,
2002 g_module ||
2003 l_api_name,
2004 '250:x_rnrl_rec.payment_terms_id2 : ' ||
2005 x_rnrl_rec.payment_terms_id2
2006 );
2007 END IF;
2008
2009 --- If any errors happen abort API
2010 IF (x_return_status = g_ret_sts_unexp_error) THEN
2011 RAISE fnd_api.g_exc_unexpected_error;
2012 ELSIF (x_return_status = g_ret_sts_error) THEN
2013 RAISE fnd_api.g_exc_error;
2014 END IF;
2015
2016 -- Update contract table attributes
2017 IF p_payment_type = 'CCR' THEN
2018 -- Validate all CCR information is entered from UI
2019 IF (p_instr_assignment_id IS NULL) THEN
2020 -- this is a new credit card, check if all info is entered from UI
2021 IF (p_card_number IS NULL)
2022 OR (p_expiration_month IS NULL)
2023 OR (p_expiration_year IS NULL) THEN
2024 fnd_message.set_name (g_app_name, 'OKS_CC_INVALID_DATA');
2025 fnd_msg_pub.ADD;
2026 RAISE fnd_api.g_exc_unexpected_error;
2027 END IF; -- cc info invalid
2028 END IF; -- p_instr_assignment_id is null
2029
2030 -- get billing_address_id
2031 OPEN csr_billing_address_id;
2032
2033 FETCH csr_billing_address_id
2034 INTO l_billing_address_id;
2035
2036 CLOSE csr_billing_address_id;
2037
2038 -- debug log
2039 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2040 fnd_log.STRING (fnd_log.level_statement,
2041 g_module ||
2042 l_api_name,
2043 '300: l_billing_address_id : ' ||
2044 l_billing_address_id
2045 );
2046 END IF;
2047
2048 -- get the card expiration date
2049 IF (p_expiration_month IS NOT NULL)
2050 AND (p_expiration_year IS NOT NULL) THEN
2051 /*
2052 OPEN csr_expiration_date (p_month => p_expiration_month,
2053 p_year => p_expiration_year);
2054 FETCH csr_expiration_date
2055 INTO l_expiration_date;
2056 CLOSE csr_expiration_date;
2057 */
2058 l_expiration_date := LAST_DAY (TO_DATE (p_expiration_month ||'/' ||p_expiration_year, 'MM/YYYY'));
2059 END IF;
2060
2061 -- debug log
2062 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2063 fnd_log.STRING (fnd_log.level_statement,
2064 g_module ||
2065 l_api_name,
2066 '300: l_expiration_date : ' ||
2067 TO_CHAR (l_expiration_date)
2068 );
2069 END IF;
2070
2071 -- call process_credit_card
2072 -- debug log
2073 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2074 fnd_log.STRING (fnd_log.level_statement,
2075 g_module ||
2076 l_api_name,
2077 '350: Calling process_credit_card '
2078 );
2079 END IF;
2080
2081 process_credit_card (p_api_version => 1.0,
2082 p_init_msg_list => g_false,
2083 p_commit => g_false,
2084 p_order_id => p_chr_id,
2085 p_party_id => p_party_id,
2086 p_cust_account_id => p_cust_account_id,
2087 p_card_number => p_card_number,
2088 p_expiration_date => l_expiration_date,
2089 p_billing_address_id => l_billing_address_id,
2090 p_cvv_code => p_cvv_code,
2091 p_instr_assignment_id => p_instr_assignment_id,
2092 p_old_txn_entension_id => NULL,
2093 -- as we are deleting above
2094 x_new_txn_entension_id => l_trxn_extension_id,
2095 x_return_status => x_return_status,
2096 x_msg_data => x_msg_data,
2097 x_msg_count => x_msg_count
2098 );
2099
2100 -- debug log
2101 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2102 fnd_log.STRING
2103 (fnd_log.level_statement,
2104 g_module ||
2105 l_api_name,
2106 '450: After Calling process_credit_card x_return_status : ' ||
2107 x_return_status
2108 );
2109 fnd_log.STRING (fnd_log.level_statement,
2110 g_module ||
2111 l_api_name,
2112 '450: l_trxn_extension_id : ' ||
2113 l_trxn_extension_id
2114 );
2115 END IF;
2116
2117 IF (x_return_status = g_ret_sts_unexp_error) THEN
2118 RAISE fnd_api.g_exc_unexpected_error;
2119 ELSIF (x_return_status = g_ret_sts_error) THEN
2120 RAISE fnd_api.g_exc_error;
2121 END IF;
2122
2123 -- update OKC and OKS entities
2124 UPDATE oks_k_headers_b
2125 SET payment_type = p_payment_type,
2126 trxn_extension_id = l_trxn_extension_id,
2127 commitment_id = NULL,
2128 object_version_number = object_version_number +
2129 1,
2130 last_update_date = SYSDATE,
2131 last_updated_by = fnd_global.user_id,
2132 last_update_login = fnd_global.login_id
2133 WHERE chr_id = p_chr_id;
2134
2135 UPDATE okc_k_headers_all_b
2136 SET cust_po_number = NULL,
2137 payment_instruction_type = NULL,
2138 cust_po_number_req_yn = 'N',
2139 payment_term_id =
2140 NVL (x_rnrl_rec.payment_terms_id1, payment_term_id),
2141 object_version_number = object_version_number +
2142 1,
2143 last_update_date = SYSDATE,
2144 last_updated_by = fnd_global.user_id,
2145 last_update_login = fnd_global.login_id
2146 WHERE ID = p_chr_id;
2147 ELSIF p_payment_type = 'COM' THEN
2148 UPDATE oks_k_headers_b
2149 SET payment_type = p_payment_type,
2150 commitment_id = p_payment_details,
2151 trxn_extension_id = NULL,
2152 object_version_number = object_version_number +
2153 1,
2154 last_update_date = SYSDATE,
2155 last_updated_by = fnd_global.user_id,
2156 last_update_login = fnd_global.login_id
2157 WHERE chr_id = p_chr_id;
2158
2159 UPDATE okc_k_headers_all_b
2160 SET cust_po_number = NULL,
2161 payment_instruction_type = NULL,
2162 cust_po_number_req_yn = 'N',
2163 object_version_number = object_version_number +
2164 1,
2165 last_update_date = SYSDATE,
2166 last_updated_by = fnd_global.user_id,
2167 last_update_login = fnd_global.login_id
2168 WHERE ID = p_chr_id;
2169 ELSE
2170 UPDATE oks_k_headers_b
2171 SET payment_type = NULL,
2172 trxn_extension_id = NULL,
2173 commitment_id = NULL,
2174 object_version_number = object_version_number +
2175 1,
2176 last_update_date = SYSDATE,
2177 last_updated_by = fnd_global.user_id,
2178 last_update_login = fnd_global.login_id
2179 WHERE chr_id = p_chr_id;
2180
2181 UPDATE okc_k_headers_all_b
2182 SET cust_po_number = p_payment_details,
2183 payment_instruction_type = p_payment_type,
2184 object_version_number = object_version_number +
2185 1,
2186 last_update_date = SYSDATE,
2187 last_updated_by = fnd_global.user_id,
2188 last_update_login = fnd_global.login_id
2189 WHERE ID = p_chr_id;
2190 END IF;
2191
2192 -- bump up the minor version number
2193 UPDATE okc_k_vers_numbers
2194 SET minor_version = minor_version +
2195 1,
2196 object_version_number = object_version_number +
2197 1,
2198 last_update_date = SYSDATE,
2199 last_updated_by = fnd_global.user_id,
2200 last_update_login = fnd_global.login_id
2201 WHERE chr_id = p_chr_id;
2202
2203 -- Standard call to get message count and if count is 1, get message info.
2204 fnd_msg_pub.count_and_get (p_encoded => 'F',
2205 p_count => x_msg_count,
2206 p_data => x_msg_data
2207 );
2208
2209 -- end debug log
2210 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2211 fnd_log.STRING (fnd_log.level_procedure,
2212 g_module ||
2213 l_api_name,
2214 '1000: Leaving ' ||
2215 g_pkg_name ||
2216 '.' ||
2217 l_api_name
2218 );
2219 END IF;
2220 EXCEPTION
2221 WHEN fnd_api.g_exc_error THEN
2222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2223 fnd_log.STRING (fnd_log.level_procedure,
2224 g_module ||
2225 l_api_name,
2226 '2000: Leaving ' ||
2227 g_pkg_name ||
2228 '.' ||
2229 l_api_name
2230 );
2231 END IF;
2232
2233 x_return_status := g_ret_sts_error;
2234 fnd_msg_pub.count_and_get (p_encoded => 'F',
2235 p_count => x_msg_count,
2236 p_data => x_msg_data
2237 );
2238 WHEN fnd_api.g_exc_unexpected_error THEN
2239 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2240 fnd_log.STRING (fnd_log.level_procedure,
2241 g_module ||
2242 l_api_name,
2243 '3000: Leaving ' ||
2244 g_pkg_name ||
2245 '.' ||
2246 l_api_name
2247 );
2248 END IF;
2249
2250 x_return_status := g_ret_sts_unexp_error;
2251 fnd_msg_pub.count_and_get (p_encoded => 'F',
2252 p_count => x_msg_count,
2253 p_data => x_msg_data
2254 );
2255 WHEN OTHERS THEN
2256 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2257 fnd_log.STRING (fnd_log.level_procedure,
2258 g_module ||
2259 l_api_name,
2260 '4000: Leaving ' ||
2261 g_pkg_name ||
2262 '.' ||
2263 l_api_name
2264 );
2265 END IF;
2266
2267 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2268 fnd_msg_pub.count_and_get (p_encoded => 'F',
2269 p_count => x_msg_count,
2270 p_data => x_msg_data
2271 );
2272 END update_payment_details;
2273
2274 ---------------------------------------------------
2275 PROCEDURE send_email (
2276 p_api_version IN NUMBER,
2277 p_init_msg_list IN VARCHAR2,
2278 p_chr_id IN NUMBER,
2279 p_send_to IN VARCHAR2,
2280 p_cc_to IN VARCHAR2,
2281 p_subject IN VARCHAR2,
2282 p_text IN VARCHAR2,
2283 x_return_status OUT NOCOPY VARCHAR2,
2284 x_msg_data OUT NOCOPY VARCHAR2,
2285 x_msg_count OUT NOCOPY NUMBER
2286 ) AS
2287 l_api_version CONSTANT NUMBER := 1;
2288 l_api_name CONSTANT VARCHAR2 (30) := 'send_email';
2289 BEGIN
2290 -- start debug log
2291 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2292 fnd_log.STRING (fnd_log.level_procedure,
2293 g_module ||
2294 l_api_name,
2295 '100: Entered ' ||
2296 g_pkg_name ||
2297 '.' ||
2298 l_api_name
2299 );
2300 fnd_log.STRING (fnd_log.level_procedure,
2301 g_module ||
2302 l_api_name,
2303 '100: Parameters p_chr_id : ' ||
2304 p_chr_id
2305 );
2306 fnd_log.STRING (fnd_log.level_procedure,
2307 g_module ||
2308 l_api_name,
2309 '100: p_send_to : ' ||
2310 p_send_to
2311 );
2312 fnd_log.STRING (fnd_log.level_procedure,
2313 g_module ||
2314 l_api_name,
2315 '100: p_api_version : ' ||
2316 p_api_version
2317 );
2318 END IF;
2319
2320 -- Standard call to check for call compatibility.
2321 IF NOT fnd_api.compatible_api_call (l_api_version,
2322 p_api_version,
2323 l_api_name,
2324 g_pkg_name
2325 ) THEN
2326 RAISE fnd_api.g_exc_unexpected_error;
2327 END IF;
2328
2329 -- debug log
2330 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2331 fnd_log.STRING (fnd_log.level_procedure,
2332 g_module ||
2333 l_api_name,
2334 '110: Calling FND_MSG_PUB.initialize'
2335 );
2336 END IF;
2337
2338 -- Initialize message list if p_init_msg_list is set to TRUE.
2339 IF fnd_api.to_boolean (p_init_msg_list) THEN
2340 fnd_msg_pub.initialize;
2341 END IF;
2342
2343 -- Initialize API return status to success
2344 x_return_status := fnd_api.g_ret_sts_success;
2345 -- set context to multi org
2346 -- mo_global.init ('OKC');
2347
2348 -- debug log
2349 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2350 fnd_log.STRING
2351 (fnd_log.level_procedure,
2352 g_module ||
2353 l_api_name,
2354 '140: Calling OKS_WF_K_PROCESS_PVT.customer_request_assistance '
2355 );
2356 END IF;
2357
2358 oks_wf_k_process_pvt.customer_request_assistance
2359 (p_api_version => p_api_version,
2360 p_init_msg_list => p_init_msg_list,
2361 p_commit => g_true,
2362 p_contract_id => p_chr_id,
2363 p_item_key => NULL,
2364 p_to_email => p_send_to,
2365 p_cc_email => p_cc_to,
2366 p_subject => p_subject,
2367 p_message => p_text,
2368 x_return_status => x_return_status,
2369 x_msg_data => x_msg_data,
2370 x_msg_count => x_msg_count
2371 );
2372
2373 -- debug log
2374 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2375 fnd_log.STRING
2376 (fnd_log.level_procedure,
2377 g_module ||
2378 l_api_name,
2379 '150: After Calling customer_request_assistance x_return_status : ' ||
2380 x_return_status
2381 );
2382 END IF;
2383
2384 --- If any errors happen abort API
2385 IF (x_return_status = g_ret_sts_unexp_error) THEN
2386 RAISE fnd_api.g_exc_unexpected_error;
2387 ELSIF (x_return_status = g_ret_sts_error) THEN
2388 RAISE fnd_api.g_exc_error;
2389 END IF;
2390
2391 -- Standard call to get message count and if count is 1, get message info.
2392 fnd_msg_pub.count_and_get (p_encoded => 'F',
2393 p_count => x_msg_count,
2394 p_data => x_msg_data
2395 );
2396
2397 -- end debug log
2398 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2399 fnd_log.STRING (fnd_log.level_procedure,
2400 g_module ||
2401 l_api_name,
2402 '1000: Leaving ' ||
2403 g_pkg_name ||
2404 '.' ||
2405 l_api_name
2406 );
2407 END IF;
2408 EXCEPTION
2409 WHEN fnd_api.g_exc_error THEN
2410 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2411 fnd_log.STRING (fnd_log.level_procedure,
2412 g_module ||
2413 l_api_name,
2414 '2000: Leaving ' ||
2415 g_pkg_name ||
2416 '.' ||
2417 l_api_name
2418 );
2419 END IF;
2420
2421 x_return_status := g_ret_sts_error;
2422 fnd_msg_pub.count_and_get (p_encoded => 'F',
2423 p_count => x_msg_count,
2424 p_data => x_msg_data
2425 );
2426 WHEN fnd_api.g_exc_unexpected_error THEN
2427 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2428 fnd_log.STRING (fnd_log.level_procedure,
2429 g_module ||
2430 l_api_name,
2431 '3000: Leaving ' ||
2432 g_pkg_name ||
2433 '.' ||
2434 l_api_name
2435 );
2436 END IF;
2437
2438 x_return_status := g_ret_sts_unexp_error;
2439 fnd_msg_pub.count_and_get (p_encoded => 'F',
2440 p_count => x_msg_count,
2441 p_data => x_msg_data
2442 );
2443 WHEN OTHERS THEN
2444 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2445 fnd_log.STRING (fnd_log.level_procedure,
2446 g_module ||
2447 l_api_name,
2448 '4000: Leaving ' ||
2449 g_pkg_name ||
2450 '.' ||
2451 l_api_name
2452 );
2453 END IF;
2454
2455 x_return_status := g_ret_sts_unexp_error;
2456 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2457 fnd_msg_pub.count_and_get (p_encoded => 'F',
2458 p_count => x_msg_count,
2459 p_data => x_msg_data
2460 );
2461 END send_email;
2462
2463 ---------------------------------------------------
2464 PROCEDURE get_valid_payments (
2465 p_api_version IN NUMBER,
2466 p_init_msg_list IN VARCHAR2,
2467 p_chr_id IN NUMBER,
2468 x_valid_payments OUT NOCOPY VARCHAR2,
2469 x_default_payment OUT NOCOPY VARCHAR2,
2470 x_return_status OUT NOCOPY VARCHAR2,
2471 x_msg_data OUT NOCOPY VARCHAR2,
2472 x_msg_count OUT NOCOPY NUMBER
2473 ) AS
2474 l_api_name CONSTANT VARCHAR2 (30)
2475 := 'get_valid_payments';
2476 l_api_version CONSTANT NUMBER := 1;
2477 l_valid_payments VARCHAR2 (2000) := '';
2478 l_default_payment VARCHAR2 (2000) := '';
2479 l_separator VARCHAR2 (1) := '';
2480 l_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
2481 x_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
2482 l_k_amount NUMBER (15, 2);
2483 l_k_curr VARCHAR2 (100);
2484 l_curr_instrument okc_k_headers_all_b.payment_instruction_type%TYPE
2485 := '';
2486 l_curr_payment oks_k_headers_b.payment_type%TYPE
2487 := '';
2488 l_k_current_payments VARCHAR2 (2000) := '';
2489
2490 CURSOR csr_k_amt_curr IS
2491 SELECT (NVL (ch.estimated_amount, 0) + NVL (sh.tax_amount, 0) ) AS amount,
2492 ch.currency_code AS currency_code,
2493 ch.payment_instruction_type AS instrument_type,
2494 sh.payment_type AS payment_type
2495 FROM okc_k_headers_all_b ch,
2496 oks_k_headers_b sh
2497 WHERE ch.ID = sh.chr_id
2498 AND ch.ID = p_chr_id;
2499
2500 CURSOR csr_curr_payment IS
2501 SELECT payment_type
2502 FROM oks_k_headers_b
2503 WHERE chr_id = p_chr_id;
2504 BEGIN
2505 -- start debug log
2506 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2507 fnd_log.STRING (fnd_log.level_procedure,
2508 g_module ||
2509 l_api_name,
2510 '100: Entered ' ||
2511 g_pkg_name ||
2512 '.' ||
2513 l_api_name
2514 );
2515 END IF;
2516
2517 -- Standard call to check for call compatibility.
2518 IF NOT fnd_api.compatible_api_call (l_api_version,
2519 p_api_version,
2520 l_api_name,
2521 g_pkg_name
2522 ) THEN
2523 RAISE fnd_api.g_exc_unexpected_error;
2524 END IF;
2525
2526 -- Initialize message list if p_init_msg_list is set to TRUE.
2527 IF fnd_api.to_boolean (p_init_msg_list) THEN
2528 fnd_msg_pub.initialize;
2529 END IF;
2530
2531 -- Initialize API return status to success
2532 x_return_status := fnd_api.g_ret_sts_success;
2533 -- set context to multi org
2534 -- mo_global.init ('OKC');
2535
2536 -- debug log
2537 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2538 fnd_log.STRING (fnd_log.level_procedure,
2539 g_module ||
2540 l_api_name,
2541 '200: Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
2542 );
2543 END IF;
2544
2545 -- Call OKS_RENEW_UTIL_PVT.get_renew_rules
2546 oks_renew_util_pvt.get_renew_rules (x_return_status => x_return_status,
2547 p_api_version => 1.0,
2548 p_init_msg_list => g_false,
2549 p_chr_id => p_chr_id,
2550 p_party_id => NULL,
2551 p_org_id => NULL,
2552 p_date => SYSDATE,
2553 p_rnrl_rec => l_rnrl_rec,
2554 x_rnrl_rec => x_rnrl_rec,
2555 x_msg_count => x_msg_count,
2556 x_msg_data => x_msg_data
2557 );
2558
2559 -- debug log
2560 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2561 fnd_log.STRING (fnd_log.level_procedure,
2562 g_module ||
2563 l_api_name,
2564 '250: After Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
2565 );
2566 fnd_log.STRING (fnd_log.level_procedure,
2567 g_module ||
2568 l_api_name,
2569 '250: x_return_status : ' ||
2570 x_return_status
2571 );
2572 fnd_log.STRING (fnd_log.level_procedure,
2573 g_module ||
2574 l_api_name,
2575 '250:x_rnrl_rec.credit_card_flag : ' ||
2576 x_rnrl_rec.credit_card_flag
2577 );
2578 fnd_log.STRING (fnd_log.level_procedure,
2579 g_module ||
2580 l_api_name,
2581 '250:x_rnrl_rec.commitment_number_flag : ' ||
2582 x_rnrl_rec.commitment_number_flag
2583 );
2584 fnd_log.STRING (fnd_log.level_procedure,
2585 g_module ||
2586 l_api_name,
2587 '250:x_rnrl_rec.purchase_order_flag : ' ||
2588 x_rnrl_rec.purchase_order_flag
2589 );
2590 fnd_log.STRING (fnd_log.level_procedure,
2591 g_module ||
2592 l_api_name,
2593 '250:x_rnrl_rec.check_flag : ' ||
2594 x_rnrl_rec.check_flag
2595 );
2596 fnd_log.STRING (fnd_log.level_procedure,
2597 g_module ||
2598 l_api_name,
2599 '250:x_rnrl_rec.wire_flag : ' ||
2600 x_rnrl_rec.wire_flag
2601 );
2602 END IF;
2603
2604 --- If any errors happen abort API
2605 IF (x_return_status = g_ret_sts_unexp_error) THEN
2606 RAISE fnd_api.g_exc_unexpected_error;
2607 ELSIF (x_return_status = g_ret_sts_error) THEN
2608 RAISE fnd_api.g_exc_error;
2609 END IF;
2610
2611 -- check the contract amount and payment_threshold_amt
2612 -- if contract amount is less then the payment_threshold_amt then credit card is the only valid option
2613 OPEN csr_k_amt_curr;
2614
2615 FETCH csr_k_amt_curr
2616 INTO l_k_amount,
2617 l_k_curr,
2618 l_curr_instrument,
2619 l_curr_payment;
2620
2621 CLOSE csr_k_amt_curr;
2622
2623 -- debug log
2624 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2625 fnd_log.STRING (fnd_log.level_procedure,
2626 g_module ||
2627 l_api_name,
2628 '300: l_k_amount : ' ||
2629 l_k_amount
2630 );
2631 fnd_log.STRING (fnd_log.level_procedure,
2632 g_module ||
2633 l_api_name,
2634 '300:l_k_curr : ' ||
2635 l_k_curr
2636 );
2637 fnd_log.STRING (fnd_log.level_procedure,
2638 g_module ||
2639 l_api_name,
2640 '300:l_curr_instrument : ' ||
2641 l_curr_instrument
2642 );
2643 fnd_log.STRING (fnd_log.level_procedure,
2644 g_module ||
2645 l_api_name,
2646 '300: x_rnrl_rec.payment_threshold_amt : ' ||
2647 x_rnrl_rec.payment_threshold_amt
2648 );
2649 fnd_log.STRING (fnd_log.level_procedure,
2650 g_module ||
2651 l_api_name,
2652 '300: x_rnrl_rec.base_currency : ' ||
2653 x_rnrl_rec.base_currency
2654 );
2655 END IF;
2656
2657 -- if the contract has some payment type or instrument type entered thru forms
2658 -- then always append to valid payment types
2659 /*
2660 OPEN csr_curr_payment;
2661
2662 FETCH csr_curr_payment
2663 INTO l_curr_payment;
2664
2665 CLOSE csr_curr_payment;
2666 */
2667 -- debug log
2668 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2669 fnd_log.STRING (fnd_log.level_procedure,
2670 g_module ||
2671 l_api_name,
2672 '300: l_curr_payment : ' ||
2673 l_curr_payment
2674 );
2675 END IF;
2676
2677 IF l_curr_payment = 'CCR' THEN
2678 l_k_current_payments :=
2679 '''' ||
2680 'USEDCC' ||
2681 '''' ||
2682 ',' ||
2683 '''' ||
2684 'CCR' ||
2685 '''' ||
2686 ',' ||
2687 '''' ||
2688 'NEWCC' ||
2689 '''';
2690 l_separator := ',';
2691 l_default_payment := 'USEDCC';
2692 ELSIF l_curr_payment = 'COM' THEN
2693 l_k_current_payments := '''' ||
2694 l_curr_payment ||
2695 '''';
2696 l_separator := ',';
2697 l_default_payment := l_curr_payment;
2698 END IF;
2699
2700 IF l_curr_instrument IS NOT NULL THEN
2701 l_k_current_payments :=
2702 l_k_current_payments ||
2703 l_separator ||
2704 '''' ||
2705 l_curr_instrument ||
2706 '''';
2707 l_separator := ',';
2708
2709 IF l_default_payment IS NULL THEN
2710 l_default_payment := l_curr_instrument;
2711 END IF;
2712 END IF; -- curr instrument
2713
2714 IF x_rnrl_rec.base_currency = l_k_curr THEN
2715 IF NVL (l_k_amount, 0) < NVL (x_rnrl_rec.payment_threshold_amt, 0) THEN
2716 -- credit card is the only valid option
2717 x_valid_payments :=
2718 l_k_current_payments ||
2719 l_separator ||
2720 '''' ||
2721 'CCR' ||
2722 '''' ||
2723 ',' ||
2724 '''' ||
2725 'NEWCC' ||
2726 '''';
2727 x_default_payment := 'CCR';
2728 RETURN;
2729 END IF; -- credit card is the only payment option
2730 END IF; -- currency of K and base curr same
2731
2732 -- debug log
2733 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2734 fnd_log.STRING (fnd_log.level_procedure,
2735 g_module ||
2736 l_api_name,
2737 '350: l_k_current_payments : ' ||
2738 l_k_current_payments
2739 );
2740 END IF;
2741
2742 -- if contract has any current payment, prepend the same
2743 IF l_k_current_payments IS NOT NULL THEN
2744 l_valid_payments := l_k_current_payments;
2745 l_separator := ',';
2746 END IF;
2747
2748 -- get the allowed payment methods
2749 IF NVL (x_rnrl_rec.credit_card_flag, 'N') = 'Y' THEN
2750 l_valid_payments :=
2751 l_valid_payments ||
2752 l_separator ||
2753 '''' ||
2754 'CCR' ||
2755 '''' ||
2756 ',' ||
2757 '''' ||
2758 'NEWCC' ||
2759 '''';
2760 l_separator := ',';
2761 l_default_payment := 'CCR';
2762 END IF;
2763
2764 IF NVL (x_rnrl_rec.commitment_number_flag, 'N') = 'Y' THEN
2765 l_valid_payments :=
2766 l_valid_payments ||
2767 l_separator ||
2768 '''' ||
2769 'COM' ||
2770 '''';
2771 l_separator := ',';
2772
2773 IF l_default_payment IS NULL THEN
2774 l_default_payment := 'COM';
2775 END IF;
2776 END IF;
2777
2778 IF NVL (x_rnrl_rec.purchase_order_flag, 'N') = 'Y' THEN
2779 l_valid_payments :=
2780 l_valid_payments ||
2781 l_separator ||
2782 '''' ||
2783 'PON' ||
2784 '''';
2785 l_separator := ',';
2786
2787 IF l_default_payment IS NULL THEN
2788 l_default_payment := 'PON';
2789 END IF;
2790 END IF;
2791
2792 IF NVL (x_rnrl_rec.check_flag, 'N') = 'Y' THEN
2793 l_valid_payments :=
2794 l_valid_payments ||
2795 l_separator ||
2796 '''' ||
2797 'CHK' ||
2798 '''';
2799 l_separator := ',';
2800
2801 IF l_default_payment IS NULL THEN
2802 l_default_payment := 'CHK';
2803 END IF;
2804 END IF;
2805
2806 IF NVL (x_rnrl_rec.wire_flag, 'N') = 'Y' THEN
2807 l_valid_payments :=
2808 l_valid_payments ||
2809 l_separator ||
2810 '''' ||
2811 'WIR' ||
2812 '''';
2813 l_separator := ',';
2814
2815 IF l_default_payment IS NULL THEN
2816 l_default_payment := 'WIR';
2817 END IF;
2818 END IF;
2819
2820 -- debug log
2821 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2822 fnd_log.STRING (fnd_log.level_procedure,
2823 g_module ||
2824 l_api_name,
2825 '500: x_valid_payments : ' ||
2826 l_valid_payments
2827 );
2828 fnd_log.STRING (fnd_log.level_procedure,
2829 g_module ||
2830 l_api_name,
2831 '500: x_default_payment : ' ||
2832 l_default_payment
2833 );
2834 END IF;
2835
2836 x_valid_payments := l_valid_payments;
2837 x_default_payment := l_default_payment;
2838
2839 -- end debug log
2840 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2841 fnd_log.STRING (fnd_log.level_procedure,
2842 g_module ||
2843 l_api_name,
2844 '1000: Leaving ' ||
2845 g_pkg_name ||
2846 '.' ||
2847 l_api_name
2848 );
2849 END IF;
2850 EXCEPTION
2851 WHEN fnd_api.g_exc_error THEN
2852 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2853 fnd_log.STRING (fnd_log.level_procedure,
2854 g_module ||
2855 l_api_name,
2856 '2000: Leaving ' ||
2857 g_pkg_name ||
2858 '.' ||
2859 l_api_name
2860 );
2861 END IF;
2862
2863 x_return_status := g_ret_sts_error;
2864 fnd_msg_pub.count_and_get (p_encoded => 'F',
2865 p_count => x_msg_count,
2866 p_data => x_msg_data
2867 );
2868 WHEN fnd_api.g_exc_unexpected_error THEN
2869 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2870 fnd_log.STRING (fnd_log.level_procedure,
2871 g_module ||
2872 l_api_name,
2873 '3000: Leaving ' ||
2874 g_pkg_name ||
2875 '.' ||
2876 l_api_name
2877 );
2878 END IF;
2879
2880 x_return_status := g_ret_sts_unexp_error;
2881 fnd_msg_pub.count_and_get (p_encoded => 'F',
2882 p_count => x_msg_count,
2883 p_data => x_msg_data
2884 );
2885 WHEN OTHERS THEN
2886 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2887 fnd_log.STRING (fnd_log.level_procedure,
2888 g_module ||
2889 l_api_name,
2890 '4000: Leaving ' ||
2891 g_pkg_name ||
2892 '.' ||
2893 l_api_name
2894 );
2895 END IF;
2896
2897 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2898 fnd_msg_pub.count_and_get (p_encoded => 'F',
2899 p_count => x_msg_count,
2900 p_data => x_msg_data
2901 );
2902 END get_valid_payments;
2903
2904 ---------------------------------------------------
2905 PROCEDURE process_credit_card (
2906 p_api_version IN NUMBER,
2907 p_init_msg_list IN VARCHAR2,
2908 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2909 p_order_id IN NUMBER,
2910 p_party_id IN NUMBER,
2911 p_cust_account_id IN NUMBER,
2912 p_card_number IN VARCHAR2 DEFAULT NULL,
2913 p_expiration_date IN DATE DEFAULT NULL,
2914 p_billing_address_id IN NUMBER DEFAULT NULL,
2915 p_cvv_code IN VARCHAR2 DEFAULT NULL,
2916 p_instr_assignment_id IN NUMBER DEFAULT NULL,
2917 p_old_txn_entension_id IN NUMBER DEFAULT NULL,
2918 x_new_txn_entension_id OUT NOCOPY NUMBER,
2919 x_return_status OUT NOCOPY VARCHAR2,
2920 x_msg_data OUT NOCOPY VARCHAR2,
2921 x_msg_count OUT NOCOPY NUMBER
2922 ) AS
2923 l_api_name CONSTANT VARCHAR2 (30)
2924 := 'process_credit_card';
2925 l_api_version CONSTANT NUMBER := 1;
2926 l_instr_assignment_id NUMBER := '';
2927
2928 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
2929
2930 SUBTYPE l_credit_card_type IS iby_fndcpt_setup_pub.creditcard_rec_type;
2931
2932 SUBTYPE l_pmtinstrassignment_type IS iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
2933
2934 SUBTYPE l_trxnextension_type IS iby_fndcpt_trxn_pub.trxnextension_rec_type;
2935
2936 l_payer l_payer_type;
2937 l_credit_card l_credit_card_type;
2938 l_pmtinstrassignment l_pmtinstrassignment_type;
2939 l_response iby_fndcpt_common_pub.result_rec_type;
2940 l_trxnextension l_trxnextension_type;
2941 BEGIN
2942 -- start debug log
2943 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2944 fnd_log.STRING (fnd_log.level_procedure,
2945 g_module ||
2946 l_api_name,
2947 '100: Entered ' ||
2948 g_pkg_name ||
2949 '.' ||
2950 l_api_name
2951 );
2952 fnd_log.STRING (fnd_log.level_procedure,
2953 g_module ||
2954 l_api_name,
2955 '100: ******* Parameters ********'
2956 );
2957 fnd_log.STRING (fnd_log.level_procedure,
2958 g_module ||
2959 l_api_name,
2960 '100: p_init_msg_list : ' ||
2961 p_init_msg_list
2962 );
2963 fnd_log.STRING (fnd_log.level_procedure,
2964 g_module ||
2965 l_api_name,
2966 '100: p_order_id : ' ||
2967 p_order_id
2968 );
2969 fnd_log.STRING (fnd_log.level_procedure,
2970 g_module ||
2971 l_api_name,
2972 '100: p_party_id : ' ||
2973 p_party_id
2974 );
2975 fnd_log.STRING (fnd_log.level_procedure,
2976 g_module ||
2977 l_api_name,
2978 '100: p_cust_account_id : ' ||
2979 p_cust_account_id
2980 );
2981 fnd_log.STRING (fnd_log.level_procedure,
2982 g_module ||
2983 l_api_name,
2984 '100: p_card_number : ' ||
2985 p_card_number
2986 );
2987 fnd_log.STRING (fnd_log.level_procedure,
2988 g_module ||
2989 l_api_name,
2990 '100: p_expiration_date : ' ||
2991 TO_CHAR (p_expiration_date)
2992 );
2993 fnd_log.STRING (fnd_log.level_procedure,
2994 g_module ||
2995 l_api_name,
2996 '100: p_billing_address_id : ' ||
2997 p_billing_address_id
2998 );
2999 fnd_log.STRING (fnd_log.level_procedure,
3000 g_module ||
3001 l_api_name,
3002 '100: p_cvv_code : ' ||
3003 p_cvv_code
3004 );
3005 fnd_log.STRING (fnd_log.level_procedure,
3006 g_module ||
3007 l_api_name,
3008 '100: p_instr_assignment_id : ' ||
3009 p_instr_assignment_id
3010 );
3011 fnd_log.STRING (fnd_log.level_procedure,
3012 g_module ||
3013 l_api_name,
3014 '100: p_old_txn_entension_id : ' ||
3015 p_old_txn_entension_id
3016 );
3017 END IF;
3018
3019 -- Standard call to check for call compatibility.
3020 IF NOT fnd_api.compatible_api_call (l_api_version,
3021 p_api_version,
3022 l_api_name,
3023 g_pkg_name
3024 ) THEN
3025 RAISE fnd_api.g_exc_unexpected_error;
3026 END IF;
3027
3028 -- Initialize message list if p_init_msg_list is set to TRUE.
3029 IF fnd_api.to_boolean (p_init_msg_list) THEN
3030 fnd_msg_pub.initialize;
3031 END IF;
3032
3033 -- Initialize API return status to success
3034 x_return_status := fnd_api.g_ret_sts_success;
3035 -- populate the payer record
3036 l_payer.payment_function := 'CUSTOMER_PAYMENT';
3037 l_payer.party_id := p_party_id;
3038 l_payer.cust_account_id := p_cust_account_id;
3039
3040 -- Delete any old transaction extension id
3041 IF (p_old_txn_entension_id IS NOT NULL) THEN
3042 -- debug log
3043 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3044 fnd_log.STRING (fnd_log.level_statement,
3045 g_module ||
3046 l_api_name,
3047 '150: Found old txn extension id : ' ||
3048 p_old_txn_entension_id
3049 );
3050 fnd_log.STRING
3051 (fnd_log.level_statement,
3052 g_module ||
3053 l_api_name,
3054 '150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3055 );
3056 fnd_log.STRING (fnd_log.level_statement,
3057 g_module ||
3058 l_api_name,
3059 '150: ***** Parameters *****'
3060 );
3061 fnd_log.STRING (fnd_log.level_statement,
3062 g_module ||
3063 l_api_name,
3064 '150: l_payer.Payment_Function : ' ||
3065 'CUSTOMER_PAYMENT'
3066 );
3067 fnd_log.STRING (fnd_log.level_statement,
3068 g_module ||
3069 l_api_name,
3070 '150: l_payer.Party_Id : ' ||
3071 l_payer.party_id
3072 );
3073 fnd_log.STRING (fnd_log.level_statement,
3074 g_module ||
3075 l_api_name,
3076 '150: l_payer.cust_account_id : ' ||
3077 l_payer.cust_account_id
3078 );
3079 fnd_log.STRING (fnd_log.level_statement,
3080 g_module ||
3081 l_api_name,
3082 '150: p_commit : ' ||
3083 p_commit
3084 );
3085 END IF;
3086
3087 -- dbms_output.put_line('Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3088 iby_fndcpt_trxn_pub.delete_transaction_extension
3089 (p_api_version => 1.0,
3090 p_init_msg_list => fnd_api.g_false,
3091 p_commit => p_commit,
3092 x_return_status => x_return_status,
3093 x_msg_count => x_msg_count,
3094 x_msg_data => x_msg_data,
3095 p_payer => l_payer,
3096 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3097 p_entity_id => p_old_txn_entension_id,
3098 x_response => l_response
3099 );
3100
3101 /*
3102 dbms_output.put_line('After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3103 dbms_output.put_line(' x_return_status : '|| x_return_status);
3104 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3105 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3106 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3107 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3108 */
3109
3110 -- debug log
3111 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3112 fnd_log.STRING
3113 (fnd_log.level_statement,
3114 g_module ||
3115 l_api_name,
3116 '200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3117 );
3118 fnd_log.STRING (fnd_log.level_statement,
3119 g_module ||
3120 l_api_name,
3121 '200: x_return_status : ' ||
3122 x_return_status
3123 );
3124 fnd_log.STRING (fnd_log.level_statement,
3125 g_module ||
3126 l_api_name,
3127 '200: x_msg_count : ' ||
3128 x_msg_count
3129 );
3130 fnd_log.STRING (fnd_log.level_statement,
3131 g_module ||
3132 l_api_name,
3133 '200: x_response.result_code : ' ||
3134 l_response.result_code
3135 );
3136 fnd_log.STRING (fnd_log.level_statement,
3137 g_module ||
3138 l_api_name,
3139 '200: x_response.result_category : ' ||
3140 l_response.result_category
3141 );
3142 fnd_log.STRING (fnd_log.level_statement,
3143 g_module ||
3144 l_api_name,
3145 '200: x_response.result_message : ' ||
3146 l_response.result_message
3147 );
3148 END IF;
3149
3150 IF (x_return_status = g_ret_sts_unexp_error) THEN
3151 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3152 fnd_message.set_token
3153 ('IBY_API_NAME',
3154 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3155 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3156 fnd_msg_pub.ADD;
3157 RAISE fnd_api.g_exc_unexpected_error;
3158 ELSIF (x_return_status = g_ret_sts_error) THEN
3159 /*
3160 iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
3161 In OKS QA check, we get a authorization from iby to validate credit card
3162 If QA check is run on the contract, then delete will fail and iby will return an error
3163 We will ignore Error from iby when delete txn is called.
3164
3165
3166 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3167 fnd_message.set_token
3168 ('IBY_API_NAME',
3169 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3170 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3171 fnd_msg_pub.ADD;
3172 RAISE fnd_api.g_exc_error;
3173 */
3174 null; -- error is ignored
3175 END IF;
3176 END IF; -- p_old_txn_entension_id is not null
3177
3178 -- if p_instr_assignment_id IS NULL then it is new credit card
3179 -- call the process_credit_card_api to get instrument assignment id
3180 IF (p_instr_assignment_id IS NULL) THEN
3181 l_credit_card.owner_id := p_party_id;
3182 l_credit_card.billing_address_id := p_billing_address_id;
3183 l_credit_card.card_number := p_card_number;
3184 l_credit_card.expiration_date := p_expiration_date;
3185
3186 -- debug log
3187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3188 fnd_log.STRING (fnd_log.level_statement,
3189 g_module ||
3190 l_api_name,
3191 '300: p_instr_assignment_id IS NULL'
3192 );
3193 fnd_log.STRING
3194 (fnd_log.level_statement,
3195 g_module ||
3196 l_api_name,
3197 '300: ***** Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card *****'
3198 );
3199 fnd_log.STRING (fnd_log.level_statement,
3200 g_module ||
3201 l_api_name,
3202 '300: ***** Parameters *****'
3203 );
3204 fnd_log.STRING (fnd_log.level_statement,
3205 g_module ||
3206 l_api_name,
3207 '300: l_credit_card.Owner_Id : ' ||
3208 l_credit_card.owner_id
3209 );
3210 fnd_log.STRING (fnd_log.level_statement,
3211 g_module ||
3212 l_api_name,
3213 '300: l_credit_card.Billing_Address_Id : ' ||
3214 l_credit_card.billing_address_id
3215 );
3216 fnd_log.STRING (fnd_log.level_statement,
3217 g_module ||
3218 l_api_name,
3219 '300: l_credit_card.Card_Number : ' ||
3220 l_credit_card.card_number
3221 );
3222 fnd_log.STRING (fnd_log.level_statement,
3223 g_module ||
3224 l_api_name,
3225 '300: l_credit_card.Expiration_Date : ' ||
3226 l_credit_card.expiration_date
3227 );
3228 fnd_log.STRING (fnd_log.level_statement,
3229 g_module ||
3230 l_api_name,
3231 '300: l_payer.Payment_Function : ' ||
3232 'CUSTOMER_PAYMENT'
3233 );
3234 fnd_log.STRING (fnd_log.level_statement,
3235 g_module ||
3236 l_api_name,
3237 '300: l_payer.Party_Id : ' ||
3238 l_payer.party_id
3239 );
3240 fnd_log.STRING (fnd_log.level_statement,
3241 g_module ||
3242 l_api_name,
3243 '300: l_payer.cust_account_id : ' ||
3244 l_payer.cust_account_id
3245 );
3246 fnd_log.STRING (fnd_log.level_statement,
3247 g_module ||
3248 l_api_name,
3249 '300: p_commit : ' ||
3250 p_commit
3251 );
3252 END IF;
3253
3254 -- dbms_output.put_line('Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3255 iby_fndcpt_setup_pub.process_credit_card
3256 (p_api_version => 1.0,
3257 p_init_msg_list => fnd_api.g_false,
3258 p_commit => p_commit,
3259 x_return_status => x_return_status,
3260 x_msg_count => x_msg_count,
3261 x_msg_data => x_msg_data,
3262 p_payer => l_payer,
3263 p_credit_card => l_credit_card,
3264 p_assignment_attribs => l_pmtinstrassignment,
3265 x_assign_id => l_instr_assignment_id,
3266 x_response => l_response
3267 );
3268
3269 /*
3270 dbms_output.put_line('After Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3271 dbms_output.put_line(' x_return_status : '|| x_return_status);
3272 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3273 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3274 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3275 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3276 */
3277
3278 -- debug log
3279 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3280 fnd_log.STRING
3281 (fnd_log.level_statement,
3282 g_module ||
3283 l_api_name,
3284 '350: After Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card'
3285 );
3286 fnd_log.STRING (fnd_log.level_statement,
3287 g_module ||
3288 l_api_name,
3289 '350: x_return_status : ' ||
3290 x_return_status
3291 );
3292 fnd_log.STRING (fnd_log.level_statement,
3293 g_module ||
3294 l_api_name,
3295 '350: l_instr_assignment_id : ' ||
3296 l_instr_assignment_id
3297 );
3298 fnd_log.STRING (fnd_log.level_statement,
3299 g_module ||
3300 l_api_name,
3301 '350: x_msg_count : ' ||
3302 x_msg_count
3303 );
3304 fnd_log.STRING (fnd_log.level_statement,
3305 g_module ||
3306 l_api_name,
3307 '350: x_response.result_code : ' ||
3308 l_response.result_code
3309 );
3310 fnd_log.STRING (fnd_log.level_statement,
3311 g_module ||
3312 l_api_name,
3313 '350: x_response.result_category : ' ||
3314 l_response.result_category
3315 );
3316 fnd_log.STRING (fnd_log.level_statement,
3317 g_module ||
3318 l_api_name,
3319 '350: x_response.result_message : ' ||
3320 l_response.result_message
3321 );
3322 END IF;
3323
3324 IF (x_return_status = g_ret_sts_unexp_error) THEN
3325 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3326 fnd_message.set_token ('IBY_API_NAME',
3327 'IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3328 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3329 fnd_msg_pub.ADD;
3330 RAISE fnd_api.g_exc_unexpected_error;
3331 ELSIF (x_return_status = g_ret_sts_error) THEN
3332 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3333 fnd_message.set_token ('IBY_API_NAME',
3334 'IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3335 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3336 fnd_msg_pub.ADD;
3337 RAISE fnd_api.g_exc_error;
3338 END IF;
3339 ELSE
3340 -- assignment id exists already
3341 l_instr_assignment_id := p_instr_assignment_id;
3342 END IF; -- p_instr_assignment_id IS NULL
3343
3344 -- Create a new transaction extension id with the instrument assignment id
3345 l_trxnextension.originating_application_id := 515; -- OKS
3346 l_trxnextension.order_id := p_order_id;
3347 l_trxnextension.instrument_security_code := p_cvv_code;
3348 l_trxnextension.trxn_ref_number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss');
3349
3350 -- debug log
3351 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3352 fnd_log.STRING
3353 (fnd_log.level_statement,
3354 g_module ||
3355 l_api_name,
3356 '500: ***** Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension *****'
3357 );
3358 fnd_log.STRING (fnd_log.level_statement,
3359 g_module ||
3360 l_api_name,
3361 '500: ***** Parameters *****'
3362 );
3363 fnd_log.STRING (fnd_log.level_statement,
3364 g_module ||
3365 l_api_name,
3366 '500: l_TrxnExtension.Originating_Application_Id : ' ||
3367 l_trxnextension.originating_application_id
3368 );
3369 fnd_log.STRING (fnd_log.level_statement,
3370 g_module ||
3371 l_api_name,
3372 '500: l_TrxnExtension.Order_Id : ' ||
3373 l_trxnextension.order_id
3374 );
3375 fnd_log.STRING (fnd_log.level_statement,
3376 g_module ||
3377 l_api_name,
3378 '500: l_TrxnExtension.instrument_security_code : ' ||
3379 l_trxnextension.instrument_security_code
3380 );
3381 fnd_log.STRING (fnd_log.level_statement,
3382 g_module ||
3383 l_api_name,
3384 '500: l_payer.Payment_Function : ' ||
3385 'CUSTOMER_PAYMENT'
3386 );
3387 fnd_log.STRING (fnd_log.level_statement,
3388 g_module ||
3389 l_api_name,
3390 '500: l_payer.Party_Id : ' ||
3391 l_payer.party_id
3392 );
3393 fnd_log.STRING (fnd_log.level_statement,
3394 g_module ||
3395 l_api_name,
3396 '500: l_payer.cust_account_id : ' ||
3397 l_payer.cust_account_id
3398 );
3399 fnd_log.STRING (fnd_log.level_statement,
3400 g_module ||
3401 l_api_name,
3402 '500: p_commit : ' ||
3403 p_commit
3404 );
3405 fnd_log.STRING (fnd_log.level_statement,
3406 g_module ||
3407 l_api_name,
3408 '500: p_payer_equivalency : ' ||
3409 iby_fndcpt_common_pub.g_payer_equiv_full
3410 );
3411 fnd_log.STRING (fnd_log.level_statement,
3412 g_module ||
3413 l_api_name,
3414 '500: p_pmt_channel : ' ||
3415 iby_fndcpt_setup_pub.g_channel_credit_card
3416 );
3417 fnd_log.STRING (fnd_log.level_statement,
3418 g_module ||
3419 l_api_name,
3420 '500: p_instr_assignment : ' ||
3421 l_instr_assignment_id
3422 );
3423 END IF;
3424
3425 -- dbms_output.put_line('Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3426 iby_fndcpt_trxn_pub.create_transaction_extension
3427 (p_api_version => 1.0,
3428 p_init_msg_list => fnd_api.g_false,
3429 p_commit => p_commit,
3430 x_return_status => x_return_status,
3431 x_msg_count => x_msg_count,
3432 x_msg_data => x_msg_data,
3433 p_payer => l_payer,
3434 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3435 -- FULL
3436 p_pmt_channel => iby_fndcpt_setup_pub.g_channel_credit_card,
3437 -- CREDIT_CARD
3438 p_instr_assignment => l_instr_assignment_id,
3439 p_trxn_attribs => l_trxnextension,
3440 x_entity_id => x_new_txn_entension_id,
3441 x_response => l_response
3442 );
3443
3444 /*
3445 dbms_output.put_line('After Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3446 dbms_output.put_line(' x_return_status : '|| x_return_status);
3447 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3448 dbms_output.put_line(' x_entity_id : '|| x_new_txn_entension_id);
3449 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3450 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3451 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3452 */
3453
3454 -- debug log
3455 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3456 fnd_log.STRING
3457 (fnd_log.level_statement,
3458 g_module ||
3459 l_api_name,
3460 '600: After Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension'
3461 );
3462 fnd_log.STRING (fnd_log.level_statement,
3463 g_module ||
3464 l_api_name,
3465 '600: x_return_status : ' ||
3466 x_return_status
3467 );
3468 fnd_log.STRING (fnd_log.level_statement,
3469 g_module ||
3470 l_api_name,
3471 '600: x_new_txn_entension_id : ' ||
3472 x_new_txn_entension_id
3473 );
3474 fnd_log.STRING (fnd_log.level_statement,
3475 g_module ||
3476 l_api_name,
3477 '600: x_msg_count : ' ||
3478 x_msg_count
3479 );
3480 fnd_log.STRING (fnd_log.level_statement,
3481 g_module ||
3482 l_api_name,
3483 '600: x_response.result_code : ' ||
3484 l_response.result_code
3485 );
3486 fnd_log.STRING (fnd_log.level_statement,
3487 g_module ||
3488 l_api_name,
3489 '600: x_response.result_category : ' ||
3490 l_response.result_category
3491 );
3492 fnd_log.STRING (fnd_log.level_statement,
3493 g_module ||
3494 l_api_name,
3495 '600: x_response.result_message : ' ||
3496 l_response.result_message
3497 );
3498 END IF;
3499
3500 IF (x_return_status = g_ret_sts_unexp_error) THEN
3501 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3502 fnd_message.set_token
3503 ('IBY_API_NAME',
3504 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3505 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3506 fnd_msg_pub.ADD;
3507 RAISE fnd_api.g_exc_unexpected_error;
3508 ELSIF (x_return_status = g_ret_sts_error) THEN
3509 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3510 fnd_message.set_token
3511 ('IBY_API_NAME',
3512 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3513 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3514 fnd_msg_pub.ADD;
3515 RAISE fnd_api.g_exc_error;
3516 END IF;
3517
3518 -- end debug log
3519 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3520 fnd_log.STRING (fnd_log.level_procedure,
3521 g_module ||
3522 l_api_name,
3523 '1000: Leaving ' ||
3524 g_pkg_name ||
3525 '.' ||
3526 l_api_name
3527 );
3528 END IF;
3529 EXCEPTION
3530 WHEN fnd_api.g_exc_error THEN
3531 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3532 fnd_log.STRING (fnd_log.level_procedure,
3533 g_module ||
3534 l_api_name,
3535 '2000: Leaving ' ||
3536 g_pkg_name ||
3537 '.' ||
3538 l_api_name
3539 );
3540 END IF;
3541
3542 x_return_status := g_ret_sts_error;
3543 fnd_msg_pub.count_and_get (p_encoded => 'F',
3544 p_count => x_msg_count,
3545 p_data => x_msg_data
3546 );
3547 WHEN fnd_api.g_exc_unexpected_error THEN
3548 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3549 fnd_log.STRING (fnd_log.level_procedure,
3550 g_module ||
3551 l_api_name,
3552 '3000: Leaving ' ||
3553 g_pkg_name ||
3554 '.' ||
3555 l_api_name
3556 );
3557 END IF;
3558
3559 x_return_status := g_ret_sts_unexp_error;
3560 fnd_msg_pub.count_and_get (p_encoded => 'F',
3561 p_count => x_msg_count,
3562 p_data => x_msg_data
3563 );
3564 WHEN OTHERS THEN
3565 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3566 fnd_log.STRING (fnd_log.level_procedure,
3567 g_module ||
3568 l_api_name,
3569 '4000: Leaving ' ||
3570 g_pkg_name ||
3571 '.' ||
3572 l_api_name
3573 );
3574 END IF;
3575
3576 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3577 fnd_msg_pub.count_and_get (p_encoded => 'F',
3578 p_count => x_msg_count,
3579 p_data => x_msg_data
3580 );
3581 END process_credit_card;
3582
3583 ---------------------------------------------------
3584 PROCEDURE get_contract_salesrep_details (
3585 p_chr_id IN NUMBER,
3586 x_salesrep_email OUT NOCOPY VARCHAR2,
3587 x_salesrep_username OUT NOCOPY VARCHAR2,
3588 x_return_status OUT NOCOPY VARCHAR2,
3589 x_msg_data OUT NOCOPY VARCHAR2,
3590 x_msg_count OUT NOCOPY NUMBER
3591 ) AS
3592 l_api_name CONSTANT VARCHAR2 (30)
3593 := 'get_contract_salesrep_details';
3594
3595 CURSOR csr_k_salesrep IS
3596 SELECT srp.email_address AS email_address,
3597 res.user_name AS username
3598 FROM okc_k_headers_all_b khr,
3599 okc_contacts ct,
3600 jtf_rs_salesreps srp,
3601 jtf_rs_resource_extns res
3602 WHERE khr.ID = ct.dnz_chr_id
3603 AND ct.object1_id1 = srp.salesrep_id
3604 AND srp.resource_id = res.resource_id
3605 AND srp.org_id = khr.authoring_org_id
3606 AND ct.jtot_object1_code='OKX_SALEPERS' --bug 6243682
3607 AND res.CATEGORY IN
3608 ('EMPLOYEE', 'OTHER', 'PARTY', 'PARTNER', 'SUPPLIER_CONTACT')
3609 -- AND srp.email_address IS NOT NULL -- bug 4918198
3610 AND res.user_name IS NOT NULL -- Salesrep MUST BE a FND USER
3611 AND khr.ID = p_chr_id;
3612
3613 CURSOR csr_party_helpdesk (
3614 p_k_party_id IN NUMBER
3615 ) IS
3616 SELECT gcd.email_address,
3617 fnd.user_name
3618 FROM oks_k_defaults gcd,
3619 fnd_user fnd
3620 WHERE gcd.user_id = fnd.user_id
3621 AND gcd.cdt_type = 'SDT'
3622 AND gcd.jtot_object_code = 'OKX_PARTY'
3623 -- AND gcd.email_address IS NOT NULL -- bug 4918198
3624 AND gcd.segment_id1 = p_k_party_id;
3625
3626 CURSOR csr_org_helpdesk (
3627 p_k_org_id IN NUMBER
3628 ) IS
3629 SELECT gcd.email_address,
3630 fnd.user_name
3631 FROM oks_k_defaults gcd,
3632 fnd_user fnd
3633 WHERE gcd.user_id = fnd.user_id
3634 AND gcd.cdt_type = 'SDT'
3635 AND gcd.jtot_object_code = 'OKX_OPERUNIT'
3636 -- AND gcd.email_address IS NOT NULL -- bug 4918198
3637 AND gcd.segment_id1 = p_k_org_id;
3638
3639 CURSOR csr_global_helpdesk IS
3640 SELECT gcd.email_address,
3641 fnd.user_name
3642 FROM oks_k_defaults gcd,
3643 fnd_user fnd
3644 WHERE gcd.user_id = fnd.user_id
3645 AND gcd.cdt_type = 'MDT';
3646 BEGIN
3647 -- start debug log
3648 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3649 fnd_log.STRING (fnd_log.level_procedure,
3650 g_module ||
3651 l_api_name,
3652 '100: Entered ' ||
3653 g_pkg_name ||
3654 '.' ||
3655 l_api_name
3656 );
3657 fnd_log.STRING (fnd_log.level_procedure,
3658 g_module ||
3659 l_api_name,
3660 '100: ******* Parameters ********'
3661 );
3662 fnd_log.STRING (fnd_log.level_procedure,
3663 g_module ||
3664 l_api_name,
3665 '100: p_chr_id : ' ||
3666 p_chr_id
3667 );
3668 END IF;
3669
3670 -- Initialize API return status to success
3671 x_return_status := fnd_api.g_ret_sts_success;
3672
3673 OPEN csr_k_salesrep;
3674
3675 FETCH csr_k_salesrep
3676 INTO x_salesrep_email,
3677 x_salesrep_username;
3678
3679 IF csr_k_salesrep%FOUND THEN
3680 -- Salesrep exist on K
3681 CLOSE csr_k_salesrep;
3682
3683 RETURN;
3684 END IF; -- k_salesrep csr
3685
3686 CLOSE csr_k_salesrep;
3687
3688 -- Go to GCD at party level
3689 OPEN csr_party_helpdesk (p_k_party_id => get_contract_party
3690 (p_chr_id));
3691
3692 FETCH csr_party_helpdesk
3693 INTO x_salesrep_email,
3694 x_salesrep_username;
3695
3696 IF csr_party_helpdesk%FOUND THEN
3697 CLOSE csr_party_helpdesk;
3698
3699 RETURN;
3700 END IF; -- helpdesk on party level
3701
3702 CLOSE csr_party_helpdesk;
3703
3704 -- Go to GCD at organization level
3705 OPEN csr_org_helpdesk (p_k_org_id => get_contract_organization
3706 (p_chr_id));
3707
3708 FETCH csr_org_helpdesk
3709 INTO x_salesrep_email,
3710 x_salesrep_username;
3711
3712 IF csr_org_helpdesk%FOUND THEN
3713 CLOSE csr_org_helpdesk;
3714
3715 RETURN;
3716 END IF; -- helpdesk on party level
3717
3718 CLOSE csr_org_helpdesk;
3719
3720 -- Go to GCD at global level
3721 OPEN csr_global_helpdesk;
3722
3723 FETCH csr_global_helpdesk
3724 INTO x_salesrep_email,
3725 x_salesrep_username;
3726
3727 CLOSE csr_global_helpdesk;
3728
3729 -- end debug log
3730 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3731 fnd_log.STRING (fnd_log.level_procedure,
3732 g_module ||
3733 l_api_name,
3734 '1000: Leaving ' ||
3735 g_pkg_name ||
3736 '.' ||
3737 l_api_name
3738 );
3739 END IF;
3740 EXCEPTION
3741 WHEN fnd_api.g_exc_error THEN
3742 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3743 fnd_log.STRING (fnd_log.level_procedure,
3744 g_module ||
3745 l_api_name,
3746 '2000: Leaving ' ||
3747 g_pkg_name ||
3748 '.' ||
3749 l_api_name
3750 );
3751 END IF;
3752
3753 x_return_status := g_ret_sts_error;
3754 fnd_msg_pub.count_and_get (p_encoded => 'F',
3755 p_count => x_msg_count,
3756 p_data => x_msg_data
3757 );
3758 WHEN fnd_api.g_exc_unexpected_error THEN
3759 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3760 fnd_log.STRING (fnd_log.level_procedure,
3761 g_module ||
3762 l_api_name,
3763 '3000: Leaving ' ||
3764 g_pkg_name ||
3765 '.' ||
3766 l_api_name
3767 );
3768 END IF;
3769
3770 x_return_status := g_ret_sts_unexp_error;
3771 fnd_msg_pub.count_and_get (p_encoded => 'F',
3772 p_count => x_msg_count,
3773 p_data => x_msg_data
3774 );
3775 WHEN OTHERS THEN
3776 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3777 fnd_log.STRING (fnd_log.level_procedure,
3778 g_module ||
3779 l_api_name,
3780 '4000: Leaving ' ||
3781 g_pkg_name ||
3782 '.' ||
3783 l_api_name
3784 );
3785 END IF;
3786
3787 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3788 fnd_msg_pub.count_and_get (p_encoded => 'F',
3789 p_count => x_msg_count,
3790 p_data => x_msg_data
3791 );
3792 END get_contract_salesrep_details;
3793
3794 ---------------------------------------------------
3795 PROCEDURE delete_transaction_extension (
3796 p_chr_id IN NUMBER,
3797 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
3798 x_return_status OUT NOCOPY VARCHAR2,
3799 x_msg_data OUT NOCOPY VARCHAR2,
3800 x_msg_count OUT NOCOPY NUMBER
3801 ) AS
3802 l_api_name CONSTANT VARCHAR2 (30)
3803 := 'delete_transaction_extension';
3804
3805 CURSOR csr_old_txn_id IS
3806 SELECT oks.trxn_extension_id,
3807 ca.cust_account_id,
3808 ca.party_id
3809 FROM okc_k_headers_all_b okc,
3810 oks_k_headers_b oks,
3811 hz_cust_site_uses_all su,
3812 hz_cust_acct_sites_all sa,
3813 hz_cust_accounts_all ca
3814 WHERE oks.chr_id = okc.ID
3815 AND okc.bill_to_site_use_id = su.site_use_id
3816 AND su.cust_acct_site_id = sa.cust_acct_site_id
3817 AND sa.cust_account_id = ca.cust_account_id
3818 AND oks.trxn_extension_id IS NOT NULL
3819 AND okc.ID = p_chr_id;
3820
3821 l_cust_account_id hz_cust_accounts_all.cust_account_id%TYPE;
3822 l_party_id hz_cust_accounts_all.party_id%TYPE;
3823 l_trxn_extension_id oks_k_headers_b.trxn_extension_id%TYPE;
3824
3825 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
3826
3827 l_payer l_payer_type;
3828 l_response iby_fndcpt_common_pub.result_rec_type;
3829 BEGIN
3830 -- start debug log
3831 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3832 fnd_log.STRING (fnd_log.level_procedure,
3833 g_module ||
3834 l_api_name,
3835 '100: Entered ' ||
3836 g_pkg_name ||
3837 '.' ||
3838 l_api_name
3839 );
3840 fnd_log.STRING (fnd_log.level_procedure,
3841 g_module ||
3842 l_api_name,
3843 '100: ******* Parameters ********'
3844 );
3845 fnd_log.STRING (fnd_log.level_procedure,
3846 g_module ||
3847 l_api_name,
3848 '100: p_chr_id : ' ||
3849 p_chr_id
3850 );
3851 END IF;
3852
3853 -- Initialize API return status to success
3854 x_return_status := fnd_api.g_ret_sts_success;
3855
3856 OPEN csr_old_txn_id;
3857
3858 FETCH csr_old_txn_id
3859 INTO l_trxn_extension_id,
3860 l_cust_account_id,
3861 l_party_id;
3862
3863 IF csr_old_txn_id%FOUND THEN
3864 -- old txn extension id exists, call iby delete API
3865
3866 -- populate the payer record
3867 l_payer.payment_function := 'CUSTOMER_PAYMENT';
3868 l_payer.party_id := l_party_id;
3869 l_payer.cust_account_id := l_cust_account_id;
3870
3871 -- debug log
3872 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3873 fnd_log.STRING (fnd_log.level_statement,
3874 g_module ||
3875 l_api_name,
3876 '150: Found old txn extension id : ' ||
3877 l_trxn_extension_id
3878 );
3879 fnd_log.STRING
3880 (fnd_log.level_statement,
3881 g_module ||
3882 l_api_name,
3883 '150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3884 );
3885 fnd_log.STRING (fnd_log.level_statement,
3886 g_module ||
3887 l_api_name,
3888 '150: ***** Parameters *****'
3889 );
3890 fnd_log.STRING (fnd_log.level_statement,
3891 g_module ||
3892 l_api_name,
3893 '150: l_payer.Payment_Function : ' ||
3894 'CUSTOMER_PAYMENT'
3895 );
3896 fnd_log.STRING (fnd_log.level_statement,
3897 g_module ||
3898 l_api_name,
3899 '150: l_payer.Party_Id : ' ||
3900 l_payer.party_id
3901 );
3902 fnd_log.STRING (fnd_log.level_statement,
3903 g_module ||
3904 l_api_name,
3905 '150: l_payer.cust_account_id : ' ||
3906 l_payer.cust_account_id
3907 );
3908 fnd_log.STRING (fnd_log.level_statement,
3909 g_module ||
3910 l_api_name,
3911 '150: l_trxn_extension_id : ' ||
3912 l_trxn_extension_id
3913 );
3914 fnd_log.STRING (fnd_log.level_statement,
3915 g_module ||
3916 l_api_name,
3917 '150: p_commit : ' ||
3918 p_commit
3919 );
3920 END IF;
3921
3922 iby_fndcpt_trxn_pub.delete_transaction_extension
3923 (p_api_version => 1.0,
3924 p_init_msg_list => fnd_api.g_false,
3925 p_commit => p_commit,
3926 x_return_status => x_return_status,
3927 x_msg_count => x_msg_count,
3928 x_msg_data => x_msg_data,
3929 p_payer => l_payer,
3930 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3931 p_entity_id => l_trxn_extension_id,
3932 x_response => l_response
3933 );
3934
3935 -- debug log
3936 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3937 fnd_log.STRING
3938 (fnd_log.level_statement,
3939 g_module ||
3940 l_api_name,
3941 '200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3942 );
3943 fnd_log.STRING (fnd_log.level_statement,
3944 g_module ||
3945 l_api_name,
3946 '200: x_return_status : ' ||
3947 x_return_status
3948 );
3949 fnd_log.STRING (fnd_log.level_statement,
3950 g_module ||
3951 l_api_name,
3952 '200: x_msg_count : ' ||
3953 x_msg_count
3954 );
3955 fnd_log.STRING (fnd_log.level_statement,
3956 g_module ||
3957 l_api_name,
3958 '200: x_response.result_code : ' ||
3959 l_response.result_code
3960 );
3961 fnd_log.STRING (fnd_log.level_statement,
3962 g_module ||
3963 l_api_name,
3964 '200: x_response.result_category : ' ||
3965 l_response.result_category
3966 );
3967 fnd_log.STRING (fnd_log.level_statement,
3968 g_module ||
3969 l_api_name,
3970 '200: x_response.result_message : ' ||
3971 l_response.result_message
3972 );
3973 END IF; -- debug log
3974
3975 IF (x_return_status = g_ret_sts_unexp_error) THEN
3976 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3977 fnd_message.set_token
3978 ('IBY_API_NAME',
3979 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3980 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3981 fnd_msg_pub.ADD;
3982 RAISE fnd_api.g_exc_unexpected_error;
3983 ELSIF (x_return_status = g_ret_sts_error) THEN
3984 /*
3985 bug 5486543
3986 iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
3987 In OKS QA check, we get a authorization from iby to validate credit card
3988 If QA check is run on the contract, then delete will fail and iby will return an error
3989 We will ignore Error from iby when delete txn is called.
3990
3991 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3992 fnd_message.set_token
3993 ('IBY_API_NAME',
3994 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3995 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3996 fnd_msg_pub.ADD;
3997 RAISE fnd_api.g_exc_error;
3998 */
3999 x_return_status := fnd_api.g_ret_sts_success; -- initialize
4000 null; -- error is ignored
4001 END IF;
4002 END IF; -- csr_old_txn_id%FOUND
4003
4004 CLOSE csr_old_txn_id;
4005
4006 -- end debug log
4007 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4008 fnd_log.STRING (fnd_log.level_procedure,
4009 g_module ||
4010 l_api_name,
4011 '1000: Leaving ' ||
4012 g_pkg_name ||
4013 '.' ||
4014 l_api_name
4015 );
4016 END IF;
4017 EXCEPTION
4018 WHEN fnd_api.g_exc_error THEN
4019 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4020 fnd_log.STRING (fnd_log.level_procedure,
4021 g_module ||
4022 l_api_name,
4023 '2000: Leaving ' ||
4024 g_pkg_name ||
4025 '.' ||
4026 l_api_name
4027 );
4028 END IF;
4029
4030 x_return_status := g_ret_sts_error;
4031 fnd_msg_pub.count_and_get (p_encoded => 'F',
4032 p_count => x_msg_count,
4033 p_data => x_msg_data
4034 );
4035 WHEN fnd_api.g_exc_unexpected_error THEN
4036 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4037 fnd_log.STRING (fnd_log.level_procedure,
4038 g_module ||
4039 l_api_name,
4040 '3000: Leaving ' ||
4041 g_pkg_name ||
4042 '.' ||
4043 l_api_name
4044 );
4045 END IF;
4046
4047 x_return_status := g_ret_sts_unexp_error;
4048 fnd_msg_pub.count_and_get (p_encoded => 'F',
4049 p_count => x_msg_count,
4050 p_data => x_msg_data
4051 );
4052 WHEN OTHERS THEN
4053 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4054 fnd_log.STRING (fnd_log.level_procedure,
4055 g_module ||
4056 l_api_name,
4057 '4000: Leaving ' ||
4058 g_pkg_name ||
4059 '.' ||
4060 l_api_name
4061 );
4062 END IF;
4063
4064 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4065 fnd_msg_pub.count_and_get (p_encoded => 'F',
4066 p_count => x_msg_count,
4067 p_data => x_msg_data
4068 );
4069 END delete_transaction_extension;
4070 ---------------------------------------------------
4071 END oks_customer_acceptance_pvt;