[Home] [Help]
PACKAGE BODY: APPS.OKS_CUSTOMER_ACCEPTANCE_PVT
Source
1 PACKAGE BODY oks_customer_acceptance_pvt AS
2 /* $Header: OKSVCUSB.pls 120.27.12010000.4 2009/11/17 16:03:49 cgopinee 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 /*modified by cgopinee for PA-DSS one off strategy*/
1308 ||' , ' ||
1309 decode(encrypted,'A','',TO_CHAR(TO_DATE(ibyt.card_expirydate), 'MM/YYYY')) AS cc_number
1310 FROM iby_trxn_extensions_v ibyt
1311 WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
1312 BEGIN
1313 -- start debug log
1314 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1315 fnd_log.STRING (fnd_log.level_procedure,
1316 g_module ||
1317 l_api_name,
1318 '100: Entered ' ||
1319 g_pkg_name ||
1320 '.' ||
1321 l_api_name
1322 );
1323 fnd_log.STRING (fnd_log.level_procedure,
1324 g_module ||
1325 l_api_name,
1326 '100: Parameters : p_trxn_extension_id : ' ||
1327 p_trxn_extension_id
1328 );
1329 END IF;
1330
1331 OPEN csr_cc_dtls;
1332
1333 FETCH csr_cc_dtls
1334 INTO l_cc_detail;
1335
1336 CLOSE csr_cc_dtls;
1337
1338 -- debug log
1339 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1340 fnd_log.STRING (fnd_log.level_procedure,
1341 g_module ||
1342 l_api_name,
1343 '200: l_cc_detail : ' ||
1344 l_cc_detail
1345 );
1346 fnd_log.STRING (fnd_log.level_procedure,
1347 g_module ||
1348 l_api_name,
1349 '1000: Leaving ' ||
1350 g_pkg_name ||
1351 '.' ||
1352 l_api_name
1353 );
1354 END IF;
1355
1356 RETURN l_cc_detail;
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1360 fnd_log.STRING (fnd_log.level_procedure,
1361 g_module ||
1362 l_api_name,
1363 '4000: Leaving ' ||
1364 g_pkg_name ||
1365 '.' ||
1366 l_api_name
1367 );
1368 END IF;
1369
1370 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1371 RETURN l_cc_detail;
1372 END get_credit_card_dtls;
1373
1374 ---------------------------------------------------
1375 FUNCTION get_credit_card_cvv2 (
1376 p_trxn_extension_id IN NUMBER
1377 )
1378 RETURN VARCHAR2 IS
1379 l_api_name CONSTANT VARCHAR2 (30)
1380 := 'get_credit_card_cvv2';
1381 l_cc_cvv2 VARCHAR2 (2000) := '';
1382
1383 CURSOR csr_cc_dtls IS
1384 SELECT ibyt.instrument_security_code AS cc_cvv2
1385 FROM iby_trxn_extensions_v ibyt
1386 WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
1387 BEGIN
1388 -- start debug log
1389 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1390 fnd_log.STRING (fnd_log.level_procedure,
1391 g_module ||
1392 l_api_name,
1393 '100: Entered ' ||
1394 g_pkg_name ||
1395 '.' ||
1396 l_api_name
1397 );
1398 fnd_log.STRING (fnd_log.level_procedure,
1399 g_module ||
1400 l_api_name,
1401 '100: Parameters : p_trxn_extension_id : ' ||
1402 p_trxn_extension_id
1403 );
1404 END IF;
1405
1406 OPEN csr_cc_dtls;
1407
1408 FETCH csr_cc_dtls
1409 INTO l_cc_cvv2;
1410
1411 CLOSE csr_cc_dtls;
1412
1413 -- debug log
1414 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1415 fnd_log.STRING (fnd_log.level_procedure,
1416 g_module ||
1417 l_api_name,
1418 '200: l_cc_cvv2 : ' ||
1419 l_cc_cvv2
1420 );
1421 fnd_log.STRING (fnd_log.level_procedure,
1422 g_module ||
1423 l_api_name,
1424 '1000: Leaving ' ||
1425 g_pkg_name ||
1426 '.' ||
1427 l_api_name
1428 );
1429 END IF;
1430
1431 RETURN l_cc_cvv2;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1435 fnd_log.STRING (fnd_log.level_procedure,
1436 g_module ||
1437 l_api_name,
1438 '4000: Leaving ' ||
1439 g_pkg_name ||
1440 '.' ||
1441 l_api_name
1442 );
1443 END IF;
1444
1445 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1446 RETURN l_cc_cvv2;
1447 END get_credit_card_cvv2;
1448
1449 ---------------------------------------------------
1450 FUNCTION get_contract_currency_tip (
1451 p_chr_id IN NUMBER
1452 )
1453 RETURN VARCHAR2 IS
1454 l_api_name CONSTANT VARCHAR2 (30)
1455 := 'get_contract_currency_tip';
1456 l_currency_code_tip VARCHAR2 (2000) := '';
1457
1458 CURSOR csr_currency_tip IS
1459 SELECT k.currency_code ||
1460 ' = ' ||
1461 f.NAME
1462 FROM okc_k_headers_all_b k,
1463 fnd_currencies_tl f
1464 WHERE k.currency_code = f.currency_code
1465 AND f.LANGUAGE = USERENV ('LANG')
1466 AND k.ID = p_chr_id;
1467 BEGIN
1468 -- start debug log
1469 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1470 fnd_log.STRING (fnd_log.level_procedure,
1471 g_module ||
1472 l_api_name,
1473 '100: Entered ' ||
1474 g_pkg_name ||
1475 '.' ||
1476 l_api_name
1477 );
1478 fnd_log.STRING (fnd_log.level_procedure,
1479 g_module ||
1480 l_api_name,
1481 '100: Parameters : p_chr_id : ' ||
1482 p_chr_id
1483 );
1484 END IF;
1485
1486 OPEN csr_currency_tip;
1487
1488 FETCH csr_currency_tip
1489 INTO l_currency_code_tip;
1490
1491 CLOSE csr_currency_tip;
1492
1493 -- debug log
1494 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1495 fnd_log.STRING (fnd_log.level_procedure,
1496 g_module ||
1497 l_api_name,
1498 '200: l_currency_code_tip : ' ||
1499 l_currency_code_tip
1500 );
1501 fnd_log.STRING (fnd_log.level_procedure,
1502 g_module ||
1503 l_api_name,
1504 '1000: Leaving ' ||
1505 g_pkg_name ||
1506 '.' ||
1507 l_api_name
1508 );
1509 END IF;
1510
1511 RETURN l_currency_code_tip;
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1515 fnd_log.STRING (fnd_log.level_procedure,
1516 g_module ||
1517 l_api_name,
1518 '4000: Leaving ' ||
1519 g_pkg_name ||
1520 '.' ||
1521 l_api_name
1522 );
1523 END IF;
1524
1525 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1526 RETURN l_currency_code_tip;
1527 END get_contract_currency_tip;
1528
1529 ---------------------------------------------------
1530 PROCEDURE decline_contract (
1531 p_api_version IN NUMBER,
1532 p_init_msg_list IN VARCHAR2,
1533 p_chr_id IN NUMBER,
1534 p_reason_code IN VARCHAR2,
1535 p_decline_reason IN VARCHAR2,
1536 x_return_status OUT NOCOPY VARCHAR2,
1537 x_msg_data OUT NOCOPY VARCHAR2,
1538 x_msg_count OUT NOCOPY NUMBER
1539 ) AS
1540 l_api_version CONSTANT NUMBER := 1;
1541 l_api_name CONSTANT VARCHAR2 (30)
1542 := 'decline_contract';
1543 BEGIN
1544 -- start debug log
1545 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1546 fnd_log.STRING (fnd_log.level_procedure,
1547 g_module ||
1548 l_api_name,
1549 '100: Entered ' ||
1550 g_pkg_name ||
1551 '.' ||
1552 l_api_name
1553 );
1554 END IF;
1555
1556 -- Standard call to check for call compatibility.
1557 IF NOT fnd_api.compatible_api_call (l_api_version,
1558 p_api_version,
1559 l_api_name,
1560 g_pkg_name
1561 ) THEN
1562 RAISE fnd_api.g_exc_unexpected_error;
1563 END IF;
1564
1565 -- Initialize message list if p_init_msg_list is set to TRUE.
1566 IF fnd_api.to_boolean (p_init_msg_list) THEN
1567 fnd_msg_pub.initialize;
1568 END IF;
1569
1570 -- Initialize API return status to success
1571 x_return_status := fnd_api.g_ret_sts_success;
1572 -- set context to multi org
1573 -- mo_global.init ('OKC');
1574 oks_wf_k_process_pvt.customer_decline_quote
1575 (p_api_version => p_api_version,
1576 p_init_msg_list => p_init_msg_list,
1577 p_commit => g_true,
1578 p_contract_id => p_chr_id,
1579 p_item_key => NULL,
1580 p_reason_code => p_reason_code,
1581 p_comments => p_decline_reason,
1582 x_return_status => x_return_status,
1583 x_msg_data => x_msg_data,
1584 x_msg_count => x_msg_count
1585 );
1586
1587 --- If any errors happen abort API
1588 IF (x_return_status = g_ret_sts_unexp_error) THEN
1589 RAISE fnd_api.g_exc_unexpected_error;
1590 ELSIF (x_return_status = g_ret_sts_error) THEN
1591 RAISE fnd_api.g_exc_error;
1592 END IF;
1593
1594 -- Standard call to get message count and if count is 1, get message info.
1595 fnd_msg_pub.count_and_get (p_encoded => 'F',
1596 p_count => x_msg_count,
1597 p_data => x_msg_data
1598 );
1599
1600 -- end debug log
1601 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1602 fnd_log.STRING (fnd_log.level_procedure,
1603 g_module ||
1604 l_api_name,
1605 '1000: Leaving ' ||
1606 g_pkg_name ||
1607 '.' ||
1608 l_api_name
1609 );
1610 END IF;
1611 EXCEPTION
1612 WHEN fnd_api.g_exc_error THEN
1613 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1614 fnd_log.STRING (fnd_log.level_procedure,
1615 g_module ||
1616 l_api_name,
1617 '2000: Leaving ' ||
1618 g_pkg_name ||
1619 '.' ||
1620 l_api_name
1621 );
1622 END IF;
1623
1624 x_return_status := g_ret_sts_error;
1625 fnd_msg_pub.count_and_get (p_encoded => 'F',
1626 p_count => x_msg_count,
1627 p_data => x_msg_data
1628 );
1629 WHEN fnd_api.g_exc_unexpected_error THEN
1630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1631 fnd_log.STRING (fnd_log.level_procedure,
1632 g_module ||
1633 l_api_name,
1634 '3000: Leaving ' ||
1635 g_pkg_name ||
1636 '.' ||
1637 l_api_name
1638 );
1639 END IF;
1640
1641 x_return_status := g_ret_sts_unexp_error;
1642 fnd_msg_pub.count_and_get (p_encoded => 'F',
1643 p_count => x_msg_count,
1644 p_data => x_msg_data
1645 );
1646 WHEN OTHERS THEN
1647 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1648 fnd_log.STRING (fnd_log.level_procedure,
1649 g_module ||
1650 l_api_name,
1651 '4000: Leaving ' ||
1652 g_pkg_name ||
1653 '.' ||
1654 l_api_name
1655 );
1656 END IF;
1657
1658 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1659 fnd_msg_pub.count_and_get (p_encoded => 'F',
1660 p_count => x_msg_count,
1661 p_data => x_msg_data
1662 );
1663 END decline_contract;
1664
1665 ---------------------------------------------------
1666 PROCEDURE accept_contract (
1667 p_api_version IN NUMBER,
1668 p_init_msg_list IN VARCHAR2,
1669 p_chr_id IN NUMBER,
1670 x_return_status OUT NOCOPY VARCHAR2,
1671 x_msg_data OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER
1673 ) AS
1674 l_api_version CONSTANT NUMBER := 1;
1675 l_api_name CONSTANT VARCHAR2 (30)
1676 := 'accept_contract';
1677 BEGIN
1678 -- start debug log
1679 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1680 fnd_log.STRING (fnd_log.level_procedure,
1681 g_module ||
1682 l_api_name,
1683 '100: Entered ' ||
1684 g_pkg_name ||
1685 '.' ||
1686 l_api_name
1687 );
1688 END IF;
1689
1690 -- Standard call to check for call compatibility.
1691 IF NOT fnd_api.compatible_api_call (l_api_version,
1692 p_api_version,
1693 l_api_name,
1694 g_pkg_name
1695 ) THEN
1696 RAISE fnd_api.g_exc_unexpected_error;
1697 END IF;
1698
1699 -- Initialize message list if p_init_msg_list is set to TRUE.
1700 IF fnd_api.to_boolean (p_init_msg_list) THEN
1701 fnd_msg_pub.initialize;
1702 END IF;
1703
1704 -- Initialize API return status to success
1705 x_return_status := fnd_api.g_ret_sts_success;
1706 -- set context to multi org
1707 -- mo_global.init ('OKC');
1708 oks_wf_k_process_pvt.customer_accept_quote
1709 (p_api_version => p_api_version,
1710 p_init_msg_list => p_init_msg_list,
1711 p_contract_id => p_chr_id,
1712 p_item_key => NULL,
1713 x_return_status => x_return_status,
1714 x_msg_data => x_msg_data,
1715 x_msg_count => x_msg_count
1716 );
1717
1718 --- If any errors happen abort API
1719 IF (x_return_status = g_ret_sts_unexp_error) THEN
1720 RAISE fnd_api.g_exc_unexpected_error;
1721 ELSIF (x_return_status = g_ret_sts_error) THEN
1722 RAISE fnd_api.g_exc_error;
1723 END IF;
1724
1725 -- Standard call to get message count and if count is 1, get message info.
1726 fnd_msg_pub.count_and_get (p_encoded => 'F',
1727 p_count => x_msg_count,
1728 p_data => x_msg_data
1729 );
1730
1731 -- end debug log
1732 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1733 fnd_log.STRING (fnd_log.level_procedure,
1734 g_module ||
1735 l_api_name,
1736 '1000: Leaving ' ||
1737 g_pkg_name ||
1738 '.' ||
1739 l_api_name
1740 );
1741 END IF;
1742 EXCEPTION
1743 WHEN fnd_api.g_exc_error THEN
1744 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1745 fnd_log.STRING (fnd_log.level_procedure,
1746 g_module ||
1747 l_api_name,
1748 '2000: Leaving ' ||
1749 g_pkg_name ||
1750 '.' ||
1751 l_api_name
1752 );
1753 END IF;
1754
1755 x_return_status := g_ret_sts_error;
1756 fnd_msg_pub.count_and_get (p_encoded => 'F',
1757 p_count => x_msg_count,
1758 p_data => x_msg_data
1759 );
1760 WHEN fnd_api.g_exc_unexpected_error THEN
1761 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1762 fnd_log.STRING (fnd_log.level_procedure,
1763 g_module ||
1764 l_api_name,
1765 '3000: Leaving ' ||
1766 g_pkg_name ||
1767 '.' ||
1768 l_api_name
1769 );
1770 END IF;
1771
1772 x_return_status := g_ret_sts_unexp_error;
1773 fnd_msg_pub.count_and_get (p_encoded => 'F',
1774 p_count => x_msg_count,
1775 p_data => x_msg_data
1776 );
1777 WHEN OTHERS THEN
1778 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1779 fnd_log.STRING (fnd_log.level_procedure,
1780 g_module ||
1781 l_api_name,
1782 '4000: Leaving ' ||
1783 g_pkg_name ||
1784 '.' ||
1785 l_api_name
1786 );
1787 END IF;
1788
1789 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1790 fnd_msg_pub.count_and_get (p_encoded => 'F',
1791 p_count => x_msg_count,
1792 p_data => x_msg_data
1793 );
1794 END accept_contract;
1795
1796 ---------------------------------------------------
1797 PROCEDURE update_payment_details (
1798 p_api_version IN NUMBER,
1799 p_init_msg_list IN VARCHAR2,
1800 p_chr_id IN NUMBER,
1801 p_payment_type IN VARCHAR2,
1802 p_payment_details IN VARCHAR2,
1803 p_party_id IN NUMBER,
1804 p_cust_account_id IN NUMBER,
1805 p_card_number IN VARCHAR2 DEFAULT NULL,
1806 p_expiration_month IN VARCHAR2 DEFAULT NULL,
1807 p_expiration_year IN VARCHAR2 DEFAULT NULL,
1808 p_cvv_code IN VARCHAR2 DEFAULT NULL,
1809 p_instr_assignment_id IN NUMBER DEFAULT NULL,
1810 p_old_txn_entension_id IN NUMBER DEFAULT NULL,
1811 x_return_status OUT NOCOPY VARCHAR2,
1812 x_msg_data OUT NOCOPY VARCHAR2,
1813 x_msg_count OUT NOCOPY NUMBER
1814 ) AS
1815 l_api_version CONSTANT NUMBER := 1;
1816 l_api_name CONSTANT VARCHAR2 (30)
1817 := 'update_payment_details';
1818 l_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
1819 x_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
1820
1821 CURSOR csr_billing_address_id IS
1822 SELECT st.party_site_id
1823 FROM okc_k_headers_all_b okc,
1824 hz_cust_site_uses_all su,
1825 hz_cust_acct_sites_all sa,
1826 hz_party_sites st
1827 WHERE okc.bill_to_site_use_id = su.site_use_id
1828 AND su.cust_acct_site_id = sa.cust_acct_site_id
1829 AND sa.party_site_id = st.party_site_id
1830 AND okc.ID = p_chr_id;
1831
1832 CURSOR csr_expiration_date (
1833 p_month IN VARCHAR2,
1834 p_year IN VARCHAR2
1835 ) IS
1836 SELECT LAST_DAY (TO_DATE (p_month ||
1837 '/' ||
1838 p_year, 'MM/YYYY'))
1839 FROM DUAL;
1840
1841 l_trxn_extension_id oks_k_headers_b.trxn_extension_id%TYPE;
1842 l_billing_address_id hz_party_sites.party_site_id%TYPE;
1843 l_expiration_date DATE := '';
1844
1845 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
1846
1847 l_payer l_payer_type;
1848 l_response iby_fndcpt_common_pub.result_rec_type;
1849 BEGIN
1850 -- start debug log
1851 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1852 fnd_log.STRING (fnd_log.level_procedure,
1853 g_module ||
1854 l_api_name,
1855 '100: Entered ' ||
1856 g_pkg_name ||
1857 '.' ||
1858 l_api_name
1859 );
1860 fnd_log.STRING (fnd_log.level_procedure,
1861 g_module ||
1862 l_api_name,
1863 '100: Parameters p_chr_id : ' ||
1864 p_chr_id
1865 );
1866 fnd_log.STRING (fnd_log.level_procedure,
1867 g_module ||
1868 l_api_name,
1869 '100: p_payment_type : ' ||
1870 p_payment_type
1871 );
1872 fnd_log.STRING (fnd_log.level_procedure,
1873 g_module ||
1874 l_api_name,
1875 '100: p_payment_details : ' ||
1876 p_payment_details
1877 );
1878 fnd_log.STRING (fnd_log.level_procedure,
1879 g_module ||
1880 l_api_name,
1881 '100: p_party_id : ' ||
1882 p_party_id
1883 );
1884 fnd_log.STRING (fnd_log.level_procedure,
1885 g_module ||
1886 l_api_name,
1887 '100: p_cust_account_id : ' ||
1888 p_cust_account_id
1889 );
1890 fnd_log.STRING (fnd_log.level_procedure,
1891 g_module ||
1892 l_api_name,
1893 '100: p_expiration_month : ' ||
1894 p_expiration_month
1895 );
1896 fnd_log.STRING (fnd_log.level_procedure,
1897 g_module ||
1898 l_api_name,
1899 '100: p_expiration_year : ' ||
1900 p_expiration_year
1901 );
1902 fnd_log.STRING (fnd_log.level_procedure,
1903 g_module ||
1904 l_api_name,
1905 '100: p_cvv_code : ' ||
1906 p_cvv_code
1907 );
1908 fnd_log.STRING (fnd_log.level_procedure,
1909 g_module ||
1910 l_api_name,
1911 '100: p_instr_assignment_id : ' ||
1912 p_instr_assignment_id
1913 );
1914 fnd_log.STRING (fnd_log.level_procedure,
1915 g_module ||
1916 l_api_name,
1917 '100: p_old_txn_entension_id : ' ||
1918 p_old_txn_entension_id
1919 );
1920 END IF;
1921
1922 -- Standard call to check for call compatibility.
1923 IF NOT fnd_api.compatible_api_call (l_api_version,
1924 p_api_version,
1925 l_api_name,
1926 g_pkg_name
1927 ) THEN
1928 RAISE fnd_api.g_exc_unexpected_error;
1929 END IF;
1930
1931 -- Initialize message list if p_init_msg_list is set to TRUE.
1932 IF fnd_api.to_boolean (p_init_msg_list) THEN
1933 fnd_msg_pub.initialize;
1934 END IF;
1935
1936 -- Initialize API return status to success
1937 x_return_status := fnd_api.g_ret_sts_success;
1938
1939 -- if p_payment_type is USEDCC then don't update credit card information
1940 IF (p_payment_type = 'USEDCC') THEN
1941 RETURN;
1942 END IF;
1943
1944 -- delete any existing old txn extension id record
1945 delete_transaction_extension (p_chr_id => p_chr_id,
1946 p_commit => fnd_api.g_false,
1947 x_return_status => x_return_status,
1948 x_msg_data => x_msg_data,
1949 x_msg_count => x_msg_count
1950 );
1951
1952 --- If any errors happen abort API
1953 IF (x_return_status = g_ret_sts_unexp_error) THEN
1954 RAISE fnd_api.g_exc_unexpected_error;
1955 ELSIF (x_return_status = g_ret_sts_error) THEN
1956 RAISE fnd_api.g_exc_error;
1957 END IF;
1958
1959 -- Get the payment_terms_id1 from GCD
1960 -- debug log
1961 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1962 fnd_log.STRING (fnd_log.level_procedure,
1963 g_module ||
1964 l_api_name,
1965 '210: Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
1966 );
1967 END IF;
1968
1969 -- Call OKS_RENEW_UTIL_PVT.get_renew_rules
1970 oks_renew_util_pvt.get_renew_rules (x_return_status => x_return_status,
1971 p_api_version => 1.0,
1972 p_init_msg_list => g_false,
1973 p_chr_id => p_chr_id,
1974 p_party_id => NULL,
1975 p_org_id => NULL,
1976 p_date => SYSDATE,
1977 p_rnrl_rec => l_rnrl_rec,
1978 x_rnrl_rec => x_rnrl_rec,
1979 x_msg_count => x_msg_count,
1980 x_msg_data => x_msg_data
1981 );
1982
1983 -- debug log
1984 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1985 fnd_log.STRING (fnd_log.level_procedure,
1986 g_module ||
1987 l_api_name,
1988 '250: After Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
1989 );
1990 fnd_log.STRING (fnd_log.level_procedure,
1991 g_module ||
1992 l_api_name,
1993 '250: x_return_status : ' ||
1994 x_return_status
1995 );
1996 fnd_log.STRING (fnd_log.level_procedure,
1997 g_module ||
1998 l_api_name,
1999 '250:x_rnrl_rec.payment_terms_id1 : ' ||
2000 x_rnrl_rec.payment_terms_id1
2001 );
2002 fnd_log.STRING (fnd_log.level_procedure,
2003 g_module ||
2004 l_api_name,
2005 '250:x_rnrl_rec.payment_terms_id2 : ' ||
2006 x_rnrl_rec.payment_terms_id2
2007 );
2008 END IF;
2009
2010 --- If any errors happen abort API
2011 IF (x_return_status = g_ret_sts_unexp_error) THEN
2012 RAISE fnd_api.g_exc_unexpected_error;
2013 ELSIF (x_return_status = g_ret_sts_error) THEN
2014 RAISE fnd_api.g_exc_error;
2015 END IF;
2016
2017 -- Update contract table attributes
2018 IF p_payment_type = 'CCR' THEN
2019 -- Validate all CCR information is entered from UI
2020 IF (p_instr_assignment_id IS NULL) THEN
2021 -- this is a new credit card, check if all info is entered from UI
2022 IF (p_card_number IS NULL)
2023 OR (p_expiration_month IS NULL)
2024 OR (p_expiration_year IS NULL) THEN
2025 fnd_message.set_name (g_app_name, 'OKS_CC_INVALID_DATA');
2026 fnd_msg_pub.ADD;
2027 RAISE fnd_api.g_exc_unexpected_error;
2028 END IF; -- cc info invalid
2029 END IF; -- p_instr_assignment_id is null
2030
2031 -- get billing_address_id
2032 OPEN csr_billing_address_id;
2033
2034 FETCH csr_billing_address_id
2035 INTO l_billing_address_id;
2036
2037 CLOSE csr_billing_address_id;
2038
2039 -- debug log
2040 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2041 fnd_log.STRING (fnd_log.level_statement,
2042 g_module ||
2043 l_api_name,
2044 '300: l_billing_address_id : ' ||
2045 l_billing_address_id
2046 );
2047 END IF;
2048
2049 -- get the card expiration date
2050 IF (p_expiration_month IS NOT NULL)
2051 AND (p_expiration_year IS NOT NULL) THEN
2052 /*
2053 OPEN csr_expiration_date (p_month => p_expiration_month,
2054 p_year => p_expiration_year);
2055 FETCH csr_expiration_date
2056 INTO l_expiration_date;
2057 CLOSE csr_expiration_date;
2058 */
2059 l_expiration_date := LAST_DAY (TO_DATE (p_expiration_month ||'/' ||p_expiration_year, 'MM/YYYY'));
2060 END IF;
2061
2062 -- debug log
2063 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2064 fnd_log.STRING (fnd_log.level_statement,
2065 g_module ||
2066 l_api_name,
2067 '300: l_expiration_date : ' ||
2068 TO_CHAR (l_expiration_date)
2069 );
2070 END IF;
2071
2072 -- call process_credit_card
2073 -- debug log
2074 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2075 fnd_log.STRING (fnd_log.level_statement,
2076 g_module ||
2077 l_api_name,
2078 '350: Calling process_credit_card '
2079 );
2080 END IF;
2081
2082 process_credit_card (p_api_version => 1.0,
2083 p_init_msg_list => g_false,
2084 p_commit => g_false,
2085 p_order_id => p_chr_id,
2086 p_party_id => p_party_id,
2087 p_cust_account_id => p_cust_account_id,
2088 p_card_number => p_card_number,
2089 p_expiration_date => l_expiration_date,
2090 p_billing_address_id => l_billing_address_id,
2091 p_cvv_code => p_cvv_code,
2092 p_instr_assignment_id => p_instr_assignment_id,
2093 p_old_txn_entension_id => NULL,
2094 -- as we are deleting above
2095 x_new_txn_entension_id => l_trxn_extension_id,
2096 x_return_status => x_return_status,
2097 x_msg_data => x_msg_data,
2098 x_msg_count => x_msg_count
2099 );
2100
2101 -- debug log
2102 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2103 fnd_log.STRING
2104 (fnd_log.level_statement,
2105 g_module ||
2106 l_api_name,
2107 '450: After Calling process_credit_card x_return_status : ' ||
2108 x_return_status
2109 );
2110 fnd_log.STRING (fnd_log.level_statement,
2111 g_module ||
2112 l_api_name,
2113 '450: l_trxn_extension_id : ' ||
2114 l_trxn_extension_id
2115 );
2116 END IF;
2117
2118 IF (x_return_status = g_ret_sts_unexp_error) THEN
2119 RAISE fnd_api.g_exc_unexpected_error;
2120 ELSIF (x_return_status = g_ret_sts_error) THEN
2121 RAISE fnd_api.g_exc_error;
2122 END IF;
2123
2124 -- update OKC and OKS entities
2125 UPDATE oks_k_headers_b
2126 SET payment_type = p_payment_type,
2127 trxn_extension_id = l_trxn_extension_id,
2128 commitment_id = NULL,
2129 object_version_number = object_version_number +
2130 1,
2131 last_update_date = SYSDATE,
2132 last_updated_by = fnd_global.user_id,
2133 last_update_login = fnd_global.login_id
2134 WHERE chr_id = p_chr_id;
2135
2136 UPDATE okc_k_headers_all_b
2137 SET cust_po_number = NULL,
2138 payment_instruction_type = NULL,
2139 cust_po_number_req_yn = 'N',
2140 payment_term_id =
2141 NVL (x_rnrl_rec.payment_terms_id1, payment_term_id),
2142 object_version_number = object_version_number +
2143 1,
2144 last_update_date = SYSDATE,
2145 last_updated_by = fnd_global.user_id,
2146 last_update_login = fnd_global.login_id
2147 WHERE ID = p_chr_id;
2148 ELSIF p_payment_type = 'COM' THEN
2149 UPDATE oks_k_headers_b
2150 SET payment_type = p_payment_type,
2151 commitment_id = p_payment_details,
2152 trxn_extension_id = NULL,
2153 object_version_number = object_version_number +
2154 1,
2155 last_update_date = SYSDATE,
2156 last_updated_by = fnd_global.user_id,
2157 last_update_login = fnd_global.login_id
2158 WHERE chr_id = p_chr_id;
2159
2160 UPDATE okc_k_headers_all_b
2161 SET cust_po_number = NULL,
2162 payment_instruction_type = NULL,
2163 cust_po_number_req_yn = 'N',
2164 object_version_number = object_version_number +
2165 1,
2166 last_update_date = SYSDATE,
2167 last_updated_by = fnd_global.user_id,
2168 last_update_login = fnd_global.login_id
2169 WHERE ID = p_chr_id;
2170 ELSE
2171 UPDATE oks_k_headers_b
2172 SET payment_type = NULL,
2173 trxn_extension_id = NULL,
2174 commitment_id = NULL,
2175 object_version_number = object_version_number +
2176 1,
2177 last_update_date = SYSDATE,
2178 last_updated_by = fnd_global.user_id,
2179 last_update_login = fnd_global.login_id
2180 WHERE chr_id = p_chr_id;
2181
2182 UPDATE okc_k_headers_all_b
2183 SET cust_po_number = p_payment_details,
2184 payment_instruction_type = p_payment_type,
2185 object_version_number = object_version_number +
2186 1,
2187 last_update_date = SYSDATE,
2188 last_updated_by = fnd_global.user_id,
2189 last_update_login = fnd_global.login_id
2190 WHERE ID = p_chr_id;
2191 END IF;
2192
2193 -- bump up the minor version number
2194 UPDATE okc_k_vers_numbers
2195 SET minor_version = minor_version +
2196 1,
2197 object_version_number = object_version_number +
2198 1,
2199 last_update_date = SYSDATE,
2200 last_updated_by = fnd_global.user_id,
2201 last_update_login = fnd_global.login_id
2202 WHERE chr_id = p_chr_id;
2203
2204 -- Standard call to get message count and if count is 1, get message info.
2205 fnd_msg_pub.count_and_get (p_encoded => 'F',
2206 p_count => x_msg_count,
2207 p_data => x_msg_data
2208 );
2209
2210 -- end debug log
2211 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2212 fnd_log.STRING (fnd_log.level_procedure,
2213 g_module ||
2214 l_api_name,
2215 '1000: Leaving ' ||
2216 g_pkg_name ||
2217 '.' ||
2218 l_api_name
2219 );
2220 END IF;
2221 EXCEPTION
2222 WHEN fnd_api.g_exc_error THEN
2223 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2224 fnd_log.STRING (fnd_log.level_procedure,
2225 g_module ||
2226 l_api_name,
2227 '2000: Leaving ' ||
2228 g_pkg_name ||
2229 '.' ||
2230 l_api_name
2231 );
2232 END IF;
2233
2234 x_return_status := g_ret_sts_error;
2235 fnd_msg_pub.count_and_get (p_encoded => 'F',
2236 p_count => x_msg_count,
2237 p_data => x_msg_data
2238 );
2239 WHEN fnd_api.g_exc_unexpected_error THEN
2240 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2241 fnd_log.STRING (fnd_log.level_procedure,
2242 g_module ||
2243 l_api_name,
2244 '3000: Leaving ' ||
2245 g_pkg_name ||
2246 '.' ||
2247 l_api_name
2248 );
2249 END IF;
2250
2251 x_return_status := g_ret_sts_unexp_error;
2252 fnd_msg_pub.count_and_get (p_encoded => 'F',
2253 p_count => x_msg_count,
2254 p_data => x_msg_data
2255 );
2256 WHEN OTHERS THEN
2257 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2258 fnd_log.STRING (fnd_log.level_procedure,
2259 g_module ||
2260 l_api_name,
2261 '4000: Leaving ' ||
2262 g_pkg_name ||
2263 '.' ||
2264 l_api_name
2265 );
2266 END IF;
2267
2268 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2269 fnd_msg_pub.count_and_get (p_encoded => 'F',
2270 p_count => x_msg_count,
2271 p_data => x_msg_data
2272 );
2273 END update_payment_details;
2274
2275 ---------------------------------------------------
2276 PROCEDURE send_email (
2277 p_api_version IN NUMBER,
2278 p_init_msg_list IN VARCHAR2,
2279 p_chr_id IN NUMBER,
2280 p_send_to IN VARCHAR2,
2281 p_cc_to IN VARCHAR2,
2282 p_subject IN VARCHAR2,
2283 p_text IN VARCHAR2,
2284 x_return_status OUT NOCOPY VARCHAR2,
2285 x_msg_data OUT NOCOPY VARCHAR2,
2286 x_msg_count OUT NOCOPY NUMBER
2287 ) AS
2288 l_api_version CONSTANT NUMBER := 1;
2289 l_api_name CONSTANT VARCHAR2 (30) := 'send_email';
2290 BEGIN
2291 -- start debug log
2292 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2293 fnd_log.STRING (fnd_log.level_procedure,
2294 g_module ||
2295 l_api_name,
2296 '100: Entered ' ||
2297 g_pkg_name ||
2298 '.' ||
2299 l_api_name
2300 );
2301 fnd_log.STRING (fnd_log.level_procedure,
2302 g_module ||
2303 l_api_name,
2304 '100: Parameters p_chr_id : ' ||
2305 p_chr_id
2306 );
2307 fnd_log.STRING (fnd_log.level_procedure,
2308 g_module ||
2309 l_api_name,
2310 '100: p_send_to : ' ||
2311 p_send_to
2312 );
2313 fnd_log.STRING (fnd_log.level_procedure,
2314 g_module ||
2315 l_api_name,
2316 '100: p_api_version : ' ||
2317 p_api_version
2318 );
2319 END IF;
2320
2321 -- Standard call to check for call compatibility.
2322 IF NOT fnd_api.compatible_api_call (l_api_version,
2323 p_api_version,
2324 l_api_name,
2325 g_pkg_name
2326 ) THEN
2327 RAISE fnd_api.g_exc_unexpected_error;
2328 END IF;
2329
2330 -- debug log
2331 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2332 fnd_log.STRING (fnd_log.level_procedure,
2333 g_module ||
2334 l_api_name,
2335 '110: Calling FND_MSG_PUB.initialize'
2336 );
2337 END IF;
2338
2339 -- Initialize message list if p_init_msg_list is set to TRUE.
2340 IF fnd_api.to_boolean (p_init_msg_list) THEN
2341 fnd_msg_pub.initialize;
2342 END IF;
2343
2344 -- Initialize API return status to success
2345 x_return_status := fnd_api.g_ret_sts_success;
2346 -- set context to multi org
2347 -- mo_global.init ('OKC');
2348
2349 -- debug log
2350 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2351 fnd_log.STRING
2352 (fnd_log.level_procedure,
2353 g_module ||
2354 l_api_name,
2355 '140: Calling OKS_WF_K_PROCESS_PVT.customer_request_assistance '
2356 );
2357 END IF;
2358
2359 oks_wf_k_process_pvt.customer_request_assistance
2360 (p_api_version => p_api_version,
2361 p_init_msg_list => p_init_msg_list,
2362 p_commit => g_true,
2363 p_contract_id => p_chr_id,
2364 p_item_key => NULL,
2365 p_to_email => p_send_to,
2366 p_cc_email => p_cc_to,
2367 p_subject => p_subject,
2368 p_message => p_text,
2369 x_return_status => x_return_status,
2370 x_msg_data => x_msg_data,
2371 x_msg_count => x_msg_count
2372 );
2373
2374 -- debug log
2375 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2376 fnd_log.STRING
2377 (fnd_log.level_procedure,
2378 g_module ||
2379 l_api_name,
2380 '150: After Calling customer_request_assistance x_return_status : ' ||
2381 x_return_status
2382 );
2383 END IF;
2384
2385 --- If any errors happen abort API
2386 IF (x_return_status = g_ret_sts_unexp_error) THEN
2387 RAISE fnd_api.g_exc_unexpected_error;
2388 ELSIF (x_return_status = g_ret_sts_error) THEN
2389 RAISE fnd_api.g_exc_error;
2390 END IF;
2391
2392 -- Standard call to get message count and if count is 1, get message info.
2393 fnd_msg_pub.count_and_get (p_encoded => 'F',
2394 p_count => x_msg_count,
2395 p_data => x_msg_data
2396 );
2397
2398 -- end debug log
2399 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2400 fnd_log.STRING (fnd_log.level_procedure,
2401 g_module ||
2402 l_api_name,
2403 '1000: Leaving ' ||
2404 g_pkg_name ||
2405 '.' ||
2406 l_api_name
2407 );
2408 END IF;
2409 EXCEPTION
2410 WHEN fnd_api.g_exc_error THEN
2411 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2412 fnd_log.STRING (fnd_log.level_procedure,
2413 g_module ||
2414 l_api_name,
2415 '2000: Leaving ' ||
2416 g_pkg_name ||
2417 '.' ||
2418 l_api_name
2419 );
2420 END IF;
2421
2422 x_return_status := g_ret_sts_error;
2423 fnd_msg_pub.count_and_get (p_encoded => 'F',
2424 p_count => x_msg_count,
2425 p_data => x_msg_data
2426 );
2427 WHEN fnd_api.g_exc_unexpected_error THEN
2428 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2429 fnd_log.STRING (fnd_log.level_procedure,
2430 g_module ||
2431 l_api_name,
2432 '3000: Leaving ' ||
2433 g_pkg_name ||
2434 '.' ||
2435 l_api_name
2436 );
2437 END IF;
2438
2439 x_return_status := g_ret_sts_unexp_error;
2440 fnd_msg_pub.count_and_get (p_encoded => 'F',
2441 p_count => x_msg_count,
2442 p_data => x_msg_data
2443 );
2444 WHEN OTHERS THEN
2445 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2446 fnd_log.STRING (fnd_log.level_procedure,
2447 g_module ||
2448 l_api_name,
2449 '4000: Leaving ' ||
2450 g_pkg_name ||
2451 '.' ||
2452 l_api_name
2453 );
2454 END IF;
2455
2456 x_return_status := g_ret_sts_unexp_error;
2457 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2458 fnd_msg_pub.count_and_get (p_encoded => 'F',
2459 p_count => x_msg_count,
2460 p_data => x_msg_data
2461 );
2462 END send_email;
2463
2464 ---------------------------------------------------
2465 PROCEDURE get_valid_payments (
2466 p_api_version IN NUMBER,
2467 p_init_msg_list IN VARCHAR2,
2468 p_chr_id IN NUMBER,
2469 x_valid_payments OUT NOCOPY VARCHAR2,
2470 x_default_payment OUT NOCOPY VARCHAR2,
2471 x_return_status OUT NOCOPY VARCHAR2,
2472 x_msg_data OUT NOCOPY VARCHAR2,
2473 x_msg_count OUT NOCOPY NUMBER
2474 ) AS
2475 l_api_name CONSTANT VARCHAR2 (30)
2476 := 'get_valid_payments';
2477 l_api_version CONSTANT NUMBER := 1;
2478 l_valid_payments VARCHAR2 (2000) := '';
2479 l_effective_payments VARCHAR2 (2000) := '';
2480 l_default_payment VARCHAR2 (2000) := '';
2481 l_separator VARCHAR2 (1) := '';
2482 l_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
2483 x_rnrl_rec oks_renew_util_pvt.rnrl_rec_type;
2484 l_k_amount NUMBER (15, 2);
2485 l_k_curr VARCHAR2 (100);
2486 l_curr_instrument okc_k_headers_all_b.payment_instruction_type%TYPE
2487 := '';
2488 l_curr_payment oks_k_headers_b.payment_type%TYPE
2489 := '';
2490 l_k_current_payments VARCHAR2 (2000) := '';
2491
2492 CURSOR csr_k_amt_curr IS
2493 SELECT (NVL (ch.estimated_amount, 0) + NVL (sh.tax_amount, 0) ) AS amount,
2494 ch.currency_code AS currency_code,
2495 ch.payment_instruction_type AS instrument_type,
2496 sh.payment_type AS payment_type
2497 FROM okc_k_headers_all_b ch,
2498 oks_k_headers_b sh
2499 WHERE ch.ID = sh.chr_id
2500 AND ch.ID = p_chr_id;
2501
2502 CURSOR csr_curr_payment IS
2503 SELECT payment_type
2504 FROM oks_k_headers_b
2505 WHERE chr_id = p_chr_id;
2506
2507 --cgopinee bugfix for 7443435
2508 CURSOR csr_chk_effective_payments(l_valid_payments IN VARCHAR2) IS
2509 SELECT LOOKUP_CODE
2510 FROM fnd_lookups
2511 WHERE lookup_type='OKS_OA_PAYMENT_TYPES'
2512 AND INSTR (l_valid_payments,(lookup_code)) <> 0
2513 AND ENABLED_FLAG<>'N'
2514 AND Nvl(END_DATE_ACTIVE,SYSDATE)>= SYSDATE ;
2515
2516
2517 BEGIN
2518 -- start debug log
2519 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2520 fnd_log.STRING (fnd_log.level_procedure,
2521 g_module ||
2522 l_api_name,
2523 '100: Entered ' ||
2524 g_pkg_name ||
2525 '.' ||
2526 l_api_name
2527 );
2528 END IF;
2529
2530 -- Standard call to check for call compatibility.
2531 IF NOT fnd_api.compatible_api_call (l_api_version,
2532 p_api_version,
2533 l_api_name,
2534 g_pkg_name
2535 ) THEN
2536 RAISE fnd_api.g_exc_unexpected_error;
2537 END IF;
2538
2539 -- Initialize message list if p_init_msg_list is set to TRUE.
2540 IF fnd_api.to_boolean (p_init_msg_list) THEN
2541 fnd_msg_pub.initialize;
2542 END IF;
2543
2544 -- Initialize API return status to success
2545 x_return_status := fnd_api.g_ret_sts_success;
2546 -- set context to multi org
2547 -- mo_global.init ('OKC');
2548
2549 -- debug log
2550 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2551 fnd_log.STRING (fnd_log.level_procedure,
2552 g_module ||
2553 l_api_name,
2554 '200: Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
2555 );
2556 END IF;
2557
2558 -- Call OKS_RENEW_UTIL_PVT.get_renew_rules
2559 oks_renew_util_pvt.get_renew_rules (x_return_status => x_return_status,
2560 p_api_version => 1.0,
2561 p_init_msg_list => g_false,
2562 p_chr_id => p_chr_id,
2563 p_party_id => NULL,
2564 p_org_id => NULL,
2565 p_date => SYSDATE,
2566 p_rnrl_rec => l_rnrl_rec,
2567 x_rnrl_rec => x_rnrl_rec,
2568 x_msg_count => x_msg_count,
2569 x_msg_data => x_msg_data
2570 );
2571
2572 -- debug log
2573 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2574 fnd_log.STRING (fnd_log.level_procedure,
2575 g_module ||
2576 l_api_name,
2577 '250: After Calling OKS_RENEW_UTIL_PVT.get_renew_rules'
2578 );
2579 fnd_log.STRING (fnd_log.level_procedure,
2580 g_module ||
2581 l_api_name,
2582 '250: x_return_status : ' ||
2583 x_return_status
2584 );
2585 fnd_log.STRING (fnd_log.level_procedure,
2586 g_module ||
2587 l_api_name,
2588 '250:x_rnrl_rec.credit_card_flag : ' ||
2589 x_rnrl_rec.credit_card_flag
2590 );
2591 fnd_log.STRING (fnd_log.level_procedure,
2592 g_module ||
2593 l_api_name,
2594 '250:x_rnrl_rec.commitment_number_flag : ' ||
2595 x_rnrl_rec.commitment_number_flag
2596 );
2597 fnd_log.STRING (fnd_log.level_procedure,
2598 g_module ||
2599 l_api_name,
2600 '250:x_rnrl_rec.purchase_order_flag : ' ||
2601 x_rnrl_rec.purchase_order_flag
2602 );
2603 fnd_log.STRING (fnd_log.level_procedure,
2604 g_module ||
2605 l_api_name,
2606 '250:x_rnrl_rec.check_flag : ' ||
2607 x_rnrl_rec.check_flag
2608 );
2609 fnd_log.STRING (fnd_log.level_procedure,
2610 g_module ||
2611 l_api_name,
2612 '250:x_rnrl_rec.wire_flag : ' ||
2613 x_rnrl_rec.wire_flag
2614 );
2615 END IF;
2616
2617 --- If any errors happen abort API
2618 IF (x_return_status = g_ret_sts_unexp_error) THEN
2619 RAISE fnd_api.g_exc_unexpected_error;
2620 ELSIF (x_return_status = g_ret_sts_error) THEN
2621 RAISE fnd_api.g_exc_error;
2622 END IF;
2623
2624 -- check the contract amount and payment_threshold_amt
2625 -- if contract amount is less then the payment_threshold_amt then credit card is the only valid option
2626 OPEN csr_k_amt_curr;
2627
2628 FETCH csr_k_amt_curr
2629 INTO l_k_amount,
2630 l_k_curr,
2631 l_curr_instrument,
2632 l_curr_payment;
2633
2634 CLOSE csr_k_amt_curr;
2635
2636 -- debug log
2637 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2638 fnd_log.STRING (fnd_log.level_procedure,
2639 g_module ||
2640 l_api_name,
2641 '300: l_k_amount : ' ||
2642 l_k_amount
2643 );
2644 fnd_log.STRING (fnd_log.level_procedure,
2645 g_module ||
2646 l_api_name,
2647 '300:l_k_curr : ' ||
2648 l_k_curr
2649 );
2650 fnd_log.STRING (fnd_log.level_procedure,
2651 g_module ||
2652 l_api_name,
2653 '300:l_curr_instrument : ' ||
2654 l_curr_instrument
2655 );
2656 fnd_log.STRING (fnd_log.level_procedure,
2657 g_module ||
2658 l_api_name,
2659 '300: x_rnrl_rec.payment_threshold_amt : ' ||
2660 x_rnrl_rec.payment_threshold_amt
2661 );
2662 fnd_log.STRING (fnd_log.level_procedure,
2663 g_module ||
2664 l_api_name,
2665 '300: x_rnrl_rec.base_currency : ' ||
2666 x_rnrl_rec.base_currency
2667 );
2668 END IF;
2669
2670 -- if the contract has some payment type or instrument type entered thru forms
2671 -- then always append to valid payment types
2672 /*
2673 OPEN csr_curr_payment;
2674
2675 FETCH csr_curr_payment
2676 INTO l_curr_payment;
2677
2678 CLOSE csr_curr_payment;
2679 */
2680 -- debug log
2681 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2682 fnd_log.STRING (fnd_log.level_procedure,
2683 g_module ||
2684 l_api_name,
2685 '300: l_curr_payment : ' ||
2686 l_curr_payment
2687 );
2688 END IF;
2689
2690 IF l_curr_payment = 'CCR' THEN
2691 l_k_current_payments :=
2692 '''' ||
2693 'USEDCC' ||
2694 '''' ||
2695 ',' ||
2696 '''' ||
2697 'CCR' ||
2698 '''' ||
2699 ',' ||
2700 '''' ||
2701 'NEWCC' ||
2702 '''';
2703 l_separator := ',';
2704 l_default_payment := 'USEDCC';
2705 ELSIF l_curr_payment = 'COM' THEN
2706 l_k_current_payments := '''' ||
2707 l_curr_payment ||
2708 '''';
2709 l_separator := ',';
2710 l_default_payment := l_curr_payment;
2711 END IF;
2712
2713 IF l_curr_instrument IS NOT NULL THEN
2714 l_k_current_payments :=
2715 l_k_current_payments ||
2716 l_separator ||
2717 '''' ||
2718 l_curr_instrument ||
2719 '''';
2720 l_separator := ',';
2721
2722 IF l_default_payment IS NULL THEN
2723 l_default_payment := l_curr_instrument;
2724 END IF;
2725 END IF; -- curr instrument
2726
2727 IF x_rnrl_rec.base_currency = l_k_curr THEN
2728 IF NVL (l_k_amount, 0) < NVL (x_rnrl_rec.payment_threshold_amt, 0) THEN
2729 -- credit card is the only valid option
2730 x_valid_payments :=
2731 l_k_current_payments ||
2732 l_separator ||
2733 '''' ||
2734 'CCR' ||
2735 '''' ||
2736 ',' ||
2737 '''' ||
2738 'NEWCC' ||
2739 '''';
2740 x_default_payment := 'CCR';
2741 RETURN;
2742 END IF; -- credit card is the only payment option
2743 END IF; -- currency of K and base curr same
2744
2745 -- debug log
2746 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2747 fnd_log.STRING (fnd_log.level_procedure,
2748 g_module ||
2749 l_api_name,
2750 '350: l_k_current_payments : ' ||
2751 l_k_current_payments
2752 );
2753 END IF;
2754
2755 -- if contract has any current payment, prepend the same
2756 IF l_k_current_payments IS NOT NULL THEN
2757 l_valid_payments := l_k_current_payments;
2758 l_separator := ',';
2759 END IF;
2760
2761 -- get the allowed payment methods
2762 IF NVL (x_rnrl_rec.credit_card_flag, 'N') = 'Y' THEN
2763 l_valid_payments :=
2764 l_valid_payments ||
2765 l_separator ||
2766 '''' ||
2767 'CCR' ||
2768 '''' ||
2769 ',' ||
2770 '''' ||
2771 'NEWCC' ||
2772 '''';
2773 l_separator := ',';
2774 l_default_payment := 'CCR';
2775 END IF;
2776
2777 IF NVL (x_rnrl_rec.commitment_number_flag, 'N') = 'Y' THEN
2778 l_valid_payments :=
2779 l_valid_payments ||
2780 l_separator ||
2781 '''' ||
2782 'COM' ||
2783 '''';
2784 l_separator := ',';
2785
2786 IF l_default_payment IS NULL THEN
2787 l_default_payment := 'COM';
2788 END IF;
2789 END IF;
2790
2791 IF NVL (x_rnrl_rec.purchase_order_flag, 'N') = 'Y' THEN
2792 l_valid_payments :=
2793 l_valid_payments ||
2794 l_separator ||
2795 '''' ||
2796 'PON' ||
2797 '''';
2798 l_separator := ',';
2799
2800 IF l_default_payment IS NULL THEN
2801 l_default_payment := 'PON';
2802 END IF;
2803 END IF;
2804
2805 IF NVL (x_rnrl_rec.check_flag, 'N') = 'Y' THEN
2806 l_valid_payments :=
2807 l_valid_payments ||
2808 l_separator ||
2809 '''' ||
2810 'CHK' ||
2811 '''';
2812 l_separator := ',';
2813
2814 IF l_default_payment IS NULL THEN
2815 l_default_payment := 'CHK';
2816 END IF;
2817 END IF;
2818
2819 IF NVL (x_rnrl_rec.wire_flag, 'N') = 'Y' THEN
2820 l_valid_payments :=
2821 l_valid_payments ||
2822 l_separator ||
2823 '''' ||
2824 'WIR' ||
2825 '''';
2826 l_separator := ',';
2827
2828 IF l_default_payment IS NULL THEN
2829 l_default_payment := 'WIR';
2830 END IF;
2831 END IF;
2832
2833
2834
2835 --cgopinee bugfix for 7443435
2836
2837 /* checking for the effective payment types from the list.*/
2838
2839 FOR rec in csr_chk_effective_payments(l_valid_payments)
2840 LOOP
2841 IF l_effective_payments IS NOT NULL THEN
2842 l_effective_payments := l_effective_payments||','||''''||rec.LOOKUP_CODE||'''';
2843 ELSE
2844 l_effective_payments :=''''||rec.LOOKUP_CODE||'''';
2845 END IF;
2846 END LOOP;
2847
2848 /*Check if the default payment type is in the list of valid payment type
2849 else assign the payment type in the same order as it was assigned earlier*/
2850
2851 IF (InStr(l_effective_payments,l_default_payment) <>0 ) THEN
2852 l_default_payment := l_default_payment;
2853 ELSIF (InStr(l_effective_payments,'CCR') <>0 ) THEN
2854 l_default_payment := 'CCR';
2855 ELSIF (InStr(l_effective_payments,'COM') <>0 ) THEN
2856 l_default_payment := 'COM';
2857 ELSIF (InStr(l_effective_payments,'PON') <>0 ) THEN
2858 l_default_payment := 'PON';
2859 ELSIF (InStr(l_effective_payments,'CHK') <>0 ) THEN
2860 l_default_payment := 'CHK';
2861 ELSE
2862 l_default_payment := 'WIR';
2863 END IF;
2864
2865
2866 -- debug log
2867 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2868 fnd_log.STRING (fnd_log.level_procedure,
2869 g_module ||
2870 l_api_name,
2871 '500: x_valid_payments : ' ||
2872 l_valid_payments
2873 );
2874 fnd_log.STRING (fnd_log.level_procedure,
2875 g_module ||
2876 l_api_name,
2877 '500: x_default_payment : ' ||
2878 l_default_payment
2879 );
2880 END IF;
2881
2882
2883
2884 x_valid_payments := l_effective_payments;
2885 x_default_payment := l_default_payment;
2886
2887 -- end debug log
2888 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2889 fnd_log.STRING (fnd_log.level_procedure,
2890 g_module ||
2891 l_api_name,
2892 '1000: Leaving ' ||
2893 g_pkg_name ||
2894 '.' ||
2895 l_api_name
2896 );
2897 END IF;
2898 EXCEPTION
2899 WHEN fnd_api.g_exc_error THEN
2900 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2901 fnd_log.STRING (fnd_log.level_procedure,
2902 g_module ||
2903 l_api_name,
2904 '2000: Leaving ' ||
2905 g_pkg_name ||
2906 '.' ||
2907 l_api_name
2908 );
2909 END IF;
2910
2911 x_return_status := g_ret_sts_error;
2912 fnd_msg_pub.count_and_get (p_encoded => 'F',
2913 p_count => x_msg_count,
2914 p_data => x_msg_data
2915 );
2916 WHEN fnd_api.g_exc_unexpected_error THEN
2917 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2918 fnd_log.STRING (fnd_log.level_procedure,
2919 g_module ||
2920 l_api_name,
2921 '3000: Leaving ' ||
2922 g_pkg_name ||
2923 '.' ||
2924 l_api_name
2925 );
2926 END IF;
2927
2928 x_return_status := g_ret_sts_unexp_error;
2929 fnd_msg_pub.count_and_get (p_encoded => 'F',
2930 p_count => x_msg_count,
2931 p_data => x_msg_data
2932 );
2933 WHEN OTHERS THEN
2934 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2935 fnd_log.STRING (fnd_log.level_procedure,
2936 g_module ||
2937 l_api_name,
2938 '4000: Leaving ' ||
2939 g_pkg_name ||
2940 '.' ||
2941 l_api_name
2942 );
2943 END IF;
2944
2945 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2946 fnd_msg_pub.count_and_get (p_encoded => 'F',
2947 p_count => x_msg_count,
2948 p_data => x_msg_data
2949 );
2950 END get_valid_payments;
2951
2952 ---------------------------------------------------
2953 PROCEDURE process_credit_card (
2954 p_api_version IN NUMBER,
2955 p_init_msg_list IN VARCHAR2,
2956 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2957 p_order_id IN NUMBER,
2958 p_party_id IN NUMBER,
2959 p_cust_account_id IN NUMBER,
2960 p_card_number IN VARCHAR2 DEFAULT NULL,
2961 p_expiration_date IN DATE DEFAULT NULL,
2962 p_billing_address_id IN NUMBER DEFAULT NULL,
2963 p_cvv_code IN VARCHAR2 DEFAULT NULL,
2964 p_instr_assignment_id IN NUMBER DEFAULT NULL,
2965 p_old_txn_entension_id IN NUMBER DEFAULT NULL,
2966 x_new_txn_entension_id OUT NOCOPY NUMBER,
2967 x_return_status OUT NOCOPY VARCHAR2,
2968 x_msg_data OUT NOCOPY VARCHAR2,
2969 x_msg_count OUT NOCOPY NUMBER
2970 ) AS
2971 l_api_name CONSTANT VARCHAR2 (30)
2972 := 'process_credit_card';
2973 l_api_version CONSTANT NUMBER := 1;
2974 l_instr_assignment_id NUMBER := '';
2975
2976 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
2977
2978 SUBTYPE l_credit_card_type IS iby_fndcpt_setup_pub.creditcard_rec_type;
2979
2980 SUBTYPE l_pmtinstrassignment_type IS iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
2981
2982 SUBTYPE l_trxnextension_type IS iby_fndcpt_trxn_pub.trxnextension_rec_type;
2983
2984 l_payer l_payer_type;
2985 l_credit_card l_credit_card_type;
2986 l_pmtinstrassignment l_pmtinstrassignment_type;
2987 l_response iby_fndcpt_common_pub.result_rec_type;
2988 l_trxnextension l_trxnextension_type;
2989 BEGIN
2990 -- start debug log
2991 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2992 fnd_log.STRING (fnd_log.level_procedure,
2993 g_module ||
2994 l_api_name,
2995 '100: Entered ' ||
2996 g_pkg_name ||
2997 '.' ||
2998 l_api_name
2999 );
3000 fnd_log.STRING (fnd_log.level_procedure,
3001 g_module ||
3002 l_api_name,
3003 '100: ******* Parameters ********'
3004 );
3005 fnd_log.STRING (fnd_log.level_procedure,
3006 g_module ||
3007 l_api_name,
3008 '100: p_init_msg_list : ' ||
3009 p_init_msg_list
3010 );
3011 fnd_log.STRING (fnd_log.level_procedure,
3012 g_module ||
3013 l_api_name,
3014 '100: p_order_id : ' ||
3015 p_order_id
3016 );
3017 fnd_log.STRING (fnd_log.level_procedure,
3018 g_module ||
3019 l_api_name,
3020 '100: p_party_id : ' ||
3021 p_party_id
3022 );
3023 fnd_log.STRING (fnd_log.level_procedure,
3024 g_module ||
3025 l_api_name,
3026 '100: p_cust_account_id : ' ||
3027 p_cust_account_id
3028 );
3029 fnd_log.STRING (fnd_log.level_procedure,
3030 g_module ||
3031 l_api_name,
3032 '100: p_card_number : ' ||
3033 p_card_number
3034 );
3035 fnd_log.STRING (fnd_log.level_procedure,
3036 g_module ||
3037 l_api_name,
3038 '100: p_expiration_date : ' ||
3039 TO_CHAR (p_expiration_date)
3040 );
3041 fnd_log.STRING (fnd_log.level_procedure,
3042 g_module ||
3043 l_api_name,
3044 '100: p_billing_address_id : ' ||
3045 p_billing_address_id
3046 );
3047 fnd_log.STRING (fnd_log.level_procedure,
3048 g_module ||
3049 l_api_name,
3050 '100: p_cvv_code : ' ||
3051 p_cvv_code
3052 );
3053 fnd_log.STRING (fnd_log.level_procedure,
3054 g_module ||
3055 l_api_name,
3056 '100: p_instr_assignment_id : ' ||
3057 p_instr_assignment_id
3058 );
3059 fnd_log.STRING (fnd_log.level_procedure,
3060 g_module ||
3061 l_api_name,
3062 '100: p_old_txn_entension_id : ' ||
3063 p_old_txn_entension_id
3064 );
3065 END IF;
3066
3067 -- Standard call to check for call compatibility.
3068 IF NOT fnd_api.compatible_api_call (l_api_version,
3069 p_api_version,
3070 l_api_name,
3071 g_pkg_name
3072 ) THEN
3073 RAISE fnd_api.g_exc_unexpected_error;
3074 END IF;
3075
3076 -- Initialize message list if p_init_msg_list is set to TRUE.
3077 IF fnd_api.to_boolean (p_init_msg_list) THEN
3078 fnd_msg_pub.initialize;
3079 END IF;
3080
3081 -- Initialize API return status to success
3082 x_return_status := fnd_api.g_ret_sts_success;
3083 -- populate the payer record
3084 l_payer.payment_function := 'CUSTOMER_PAYMENT';
3085 l_payer.party_id := p_party_id;
3086 l_payer.cust_account_id := p_cust_account_id;
3087
3088 -- Delete any old transaction extension id
3089 IF (p_old_txn_entension_id IS NOT NULL) THEN
3090 -- debug log
3091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3092 fnd_log.STRING (fnd_log.level_statement,
3093 g_module ||
3094 l_api_name,
3095 '150: Found old txn extension id : ' ||
3096 p_old_txn_entension_id
3097 );
3098 fnd_log.STRING
3099 (fnd_log.level_statement,
3100 g_module ||
3101 l_api_name,
3102 '150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3103 );
3104 fnd_log.STRING (fnd_log.level_statement,
3105 g_module ||
3106 l_api_name,
3107 '150: ***** Parameters *****'
3108 );
3109 fnd_log.STRING (fnd_log.level_statement,
3110 g_module ||
3111 l_api_name,
3112 '150: l_payer.Payment_Function : ' ||
3113 'CUSTOMER_PAYMENT'
3114 );
3115 fnd_log.STRING (fnd_log.level_statement,
3116 g_module ||
3117 l_api_name,
3118 '150: l_payer.Party_Id : ' ||
3119 l_payer.party_id
3120 );
3121 fnd_log.STRING (fnd_log.level_statement,
3122 g_module ||
3123 l_api_name,
3124 '150: l_payer.cust_account_id : ' ||
3125 l_payer.cust_account_id
3126 );
3127 fnd_log.STRING (fnd_log.level_statement,
3128 g_module ||
3129 l_api_name,
3130 '150: p_commit : ' ||
3131 p_commit
3132 );
3133 END IF;
3134
3135 -- dbms_output.put_line('Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3136 iby_fndcpt_trxn_pub.delete_transaction_extension
3137 (p_api_version => 1.0,
3138 p_init_msg_list => fnd_api.g_false,
3139 p_commit => p_commit,
3140 x_return_status => x_return_status,
3141 x_msg_count => x_msg_count,
3142 x_msg_data => x_msg_data,
3143 p_payer => l_payer,
3144 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3145 p_entity_id => p_old_txn_entension_id,
3146 x_response => l_response
3147 );
3148
3149 /*
3150 dbms_output.put_line('After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3151 dbms_output.put_line(' x_return_status : '|| x_return_status);
3152 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3153 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3154 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3155 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3156 */
3157
3158 -- debug log
3159 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3160 fnd_log.STRING
3161 (fnd_log.level_statement,
3162 g_module ||
3163 l_api_name,
3164 '200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3165 );
3166 fnd_log.STRING (fnd_log.level_statement,
3167 g_module ||
3168 l_api_name,
3169 '200: x_return_status : ' ||
3170 x_return_status
3171 );
3172 fnd_log.STRING (fnd_log.level_statement,
3173 g_module ||
3174 l_api_name,
3175 '200: x_msg_count : ' ||
3176 x_msg_count
3177 );
3178 fnd_log.STRING (fnd_log.level_statement,
3179 g_module ||
3180 l_api_name,
3181 '200: x_response.result_code : ' ||
3182 l_response.result_code
3183 );
3184 fnd_log.STRING (fnd_log.level_statement,
3185 g_module ||
3186 l_api_name,
3187 '200: x_response.result_category : ' ||
3188 l_response.result_category
3189 );
3190 fnd_log.STRING (fnd_log.level_statement,
3191 g_module ||
3192 l_api_name,
3193 '200: x_response.result_message : ' ||
3194 l_response.result_message
3195 );
3196 END IF;
3197
3198 IF (x_return_status = g_ret_sts_unexp_error) THEN
3199 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3200 fnd_message.set_token
3201 ('IBY_API_NAME',
3202 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3203 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3204 fnd_msg_pub.ADD;
3205 RAISE fnd_api.g_exc_unexpected_error;
3206 ELSIF (x_return_status = g_ret_sts_error) THEN
3207 /*
3208 iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
3209 In OKS QA check, we get a authorization from iby to validate credit card
3210 If QA check is run on the contract, then delete will fail and iby will return an error
3211 We will ignore Error from iby when delete txn is called.
3212
3213
3214 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3215 fnd_message.set_token
3216 ('IBY_API_NAME',
3217 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
3218 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3219 fnd_msg_pub.ADD;
3220 RAISE fnd_api.g_exc_error;
3221 */
3222 null; -- error is ignored
3223 END IF;
3224 END IF; -- p_old_txn_entension_id is not null
3225
3226 -- if p_instr_assignment_id IS NULL then it is new credit card
3227 -- call the process_credit_card_api to get instrument assignment id
3228 IF (p_instr_assignment_id IS NULL) THEN
3229 l_credit_card.owner_id := p_party_id;
3230 l_credit_card.billing_address_id := p_billing_address_id;
3231 l_credit_card.card_number := p_card_number;
3232 l_credit_card.expiration_date := p_expiration_date;
3233
3234 -- debug log
3235 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3236 fnd_log.STRING (fnd_log.level_statement,
3237 g_module ||
3238 l_api_name,
3239 '300: p_instr_assignment_id IS NULL'
3240 );
3241 fnd_log.STRING
3242 (fnd_log.level_statement,
3243 g_module ||
3244 l_api_name,
3245 '300: ***** Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card *****'
3246 );
3247 fnd_log.STRING (fnd_log.level_statement,
3248 g_module ||
3249 l_api_name,
3250 '300: ***** Parameters *****'
3251 );
3252 fnd_log.STRING (fnd_log.level_statement,
3253 g_module ||
3254 l_api_name,
3255 '300: l_credit_card.Owner_Id : ' ||
3256 l_credit_card.owner_id
3257 );
3258 fnd_log.STRING (fnd_log.level_statement,
3259 g_module ||
3260 l_api_name,
3261 '300: l_credit_card.Billing_Address_Id : ' ||
3262 l_credit_card.billing_address_id
3263 );
3264 fnd_log.STRING (fnd_log.level_statement,
3265 g_module ||
3266 l_api_name,
3267 '300: l_credit_card.Card_Number : ' ||
3268 l_credit_card.card_number
3269 );
3270 fnd_log.STRING (fnd_log.level_statement,
3271 g_module ||
3272 l_api_name,
3273 '300: l_credit_card.Expiration_Date : ' ||
3274 l_credit_card.expiration_date
3275 );
3276 fnd_log.STRING (fnd_log.level_statement,
3277 g_module ||
3278 l_api_name,
3279 '300: l_payer.Payment_Function : ' ||
3280 'CUSTOMER_PAYMENT'
3281 );
3282 fnd_log.STRING (fnd_log.level_statement,
3283 g_module ||
3284 l_api_name,
3285 '300: l_payer.Party_Id : ' ||
3286 l_payer.party_id
3287 );
3288 fnd_log.STRING (fnd_log.level_statement,
3289 g_module ||
3290 l_api_name,
3291 '300: l_payer.cust_account_id : ' ||
3292 l_payer.cust_account_id
3293 );
3294 fnd_log.STRING (fnd_log.level_statement,
3295 g_module ||
3296 l_api_name,
3297 '300: p_commit : ' ||
3298 p_commit
3299 );
3300 END IF;
3301
3302 -- dbms_output.put_line('Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3303 iby_fndcpt_setup_pub.process_credit_card
3304 (p_api_version => 1.0,
3305 p_init_msg_list => fnd_api.g_false,
3306 p_commit => p_commit,
3307 x_return_status => x_return_status,
3308 x_msg_count => x_msg_count,
3309 x_msg_data => x_msg_data,
3310 p_payer => l_payer,
3311 p_credit_card => l_credit_card,
3312 p_assignment_attribs => l_pmtinstrassignment,
3313 x_assign_id => l_instr_assignment_id,
3314 x_response => l_response
3315 );
3316
3317 /*
3318 dbms_output.put_line('After Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3319 dbms_output.put_line(' x_return_status : '|| x_return_status);
3320 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3321 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3322 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3323 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3324 */
3325
3326 -- debug log
3327 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3328 fnd_log.STRING
3329 (fnd_log.level_statement,
3330 g_module ||
3331 l_api_name,
3332 '350: After Calling IBY_FNDCPT_SETUP_PUB.Process_Credit_Card'
3333 );
3334 fnd_log.STRING (fnd_log.level_statement,
3335 g_module ||
3336 l_api_name,
3337 '350: x_return_status : ' ||
3338 x_return_status
3339 );
3340 fnd_log.STRING (fnd_log.level_statement,
3341 g_module ||
3342 l_api_name,
3343 '350: l_instr_assignment_id : ' ||
3344 l_instr_assignment_id
3345 );
3346 fnd_log.STRING (fnd_log.level_statement,
3347 g_module ||
3348 l_api_name,
3349 '350: x_msg_count : ' ||
3350 x_msg_count
3351 );
3352 fnd_log.STRING (fnd_log.level_statement,
3353 g_module ||
3354 l_api_name,
3355 '350: x_response.result_code : ' ||
3356 l_response.result_code
3357 );
3358 fnd_log.STRING (fnd_log.level_statement,
3359 g_module ||
3360 l_api_name,
3361 '350: x_response.result_category : ' ||
3362 l_response.result_category
3363 );
3364 fnd_log.STRING (fnd_log.level_statement,
3365 g_module ||
3366 l_api_name,
3367 '350: x_response.result_message : ' ||
3368 l_response.result_message
3369 );
3370 END IF;
3371
3372 IF (x_return_status = g_ret_sts_unexp_error) THEN
3373 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3374 fnd_message.set_token ('IBY_API_NAME',
3375 'IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3376 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3377 fnd_msg_pub.ADD;
3378 RAISE fnd_api.g_exc_unexpected_error;
3379 ELSIF (x_return_status = g_ret_sts_error) THEN
3380 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3381 fnd_message.set_token ('IBY_API_NAME',
3382 'IBY_FNDCPT_SETUP_PUB.Process_Credit_Card');
3383 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3384 fnd_msg_pub.ADD;
3385 RAISE fnd_api.g_exc_error;
3386 END IF;
3387 ELSE
3388 -- assignment id exists already
3389 l_instr_assignment_id := p_instr_assignment_id;
3390 END IF; -- p_instr_assignment_id IS NULL
3391
3392 -- Create a new transaction extension id with the instrument assignment id
3393 l_trxnextension.originating_application_id := 515; -- OKS
3394 l_trxnextension.order_id := p_order_id;
3395 l_trxnextension.instrument_security_code := p_cvv_code;
3396 l_trxnextension.trxn_ref_number1 := to_char(SYSDATE,'ddmmyyyyhhmmssss');
3397
3398 -- debug log
3399 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3400 fnd_log.STRING
3401 (fnd_log.level_statement,
3402 g_module ||
3403 l_api_name,
3404 '500: ***** Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension *****'
3405 );
3406 fnd_log.STRING (fnd_log.level_statement,
3407 g_module ||
3408 l_api_name,
3409 '500: ***** Parameters *****'
3410 );
3411 fnd_log.STRING (fnd_log.level_statement,
3412 g_module ||
3413 l_api_name,
3414 '500: l_TrxnExtension.Originating_Application_Id : ' ||
3415 l_trxnextension.originating_application_id
3416 );
3417 fnd_log.STRING (fnd_log.level_statement,
3418 g_module ||
3419 l_api_name,
3420 '500: l_TrxnExtension.Order_Id : ' ||
3421 l_trxnextension.order_id
3422 );
3423 fnd_log.STRING (fnd_log.level_statement,
3424 g_module ||
3425 l_api_name,
3426 '500: l_TrxnExtension.instrument_security_code : ' ||
3427 l_trxnextension.instrument_security_code
3428 );
3429 fnd_log.STRING (fnd_log.level_statement,
3430 g_module ||
3431 l_api_name,
3432 '500: l_payer.Payment_Function : ' ||
3433 'CUSTOMER_PAYMENT'
3434 );
3435 fnd_log.STRING (fnd_log.level_statement,
3436 g_module ||
3437 l_api_name,
3438 '500: l_payer.Party_Id : ' ||
3439 l_payer.party_id
3440 );
3441 fnd_log.STRING (fnd_log.level_statement,
3442 g_module ||
3443 l_api_name,
3444 '500: l_payer.cust_account_id : ' ||
3445 l_payer.cust_account_id
3446 );
3447 fnd_log.STRING (fnd_log.level_statement,
3448 g_module ||
3449 l_api_name,
3450 '500: p_commit : ' ||
3451 p_commit
3452 );
3453 fnd_log.STRING (fnd_log.level_statement,
3454 g_module ||
3455 l_api_name,
3456 '500: p_payer_equivalency : ' ||
3457 iby_fndcpt_common_pub.g_payer_equiv_full
3458 );
3459 fnd_log.STRING (fnd_log.level_statement,
3460 g_module ||
3461 l_api_name,
3462 '500: p_pmt_channel : ' ||
3463 iby_fndcpt_setup_pub.g_channel_credit_card
3464 );
3465 fnd_log.STRING (fnd_log.level_statement,
3466 g_module ||
3467 l_api_name,
3468 '500: p_instr_assignment : ' ||
3469 l_instr_assignment_id
3470 );
3471 END IF;
3472
3473 -- dbms_output.put_line('Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3474 iby_fndcpt_trxn_pub.create_transaction_extension
3475 (p_api_version => 1.0,
3476 p_init_msg_list => fnd_api.g_false,
3477 p_commit => p_commit,
3478 x_return_status => x_return_status,
3479 x_msg_count => x_msg_count,
3480 x_msg_data => x_msg_data,
3481 p_payer => l_payer,
3482 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3483 -- FULL
3484 p_pmt_channel => iby_fndcpt_setup_pub.g_channel_credit_card,
3485 -- CREDIT_CARD
3486 p_instr_assignment => l_instr_assignment_id,
3487 p_trxn_attribs => l_trxnextension,
3488 x_entity_id => x_new_txn_entension_id,
3489 x_response => l_response
3490 );
3491
3492 /*
3493 dbms_output.put_line('After Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3494 dbms_output.put_line(' x_return_status : '|| x_return_status);
3495 dbms_output.put_line(' x_msg_count : '|| x_msg_count);
3496 dbms_output.put_line(' x_entity_id : '|| x_new_txn_entension_id);
3497 dbms_output.put_line(' l_response.result_code : '|| l_response.result_code);
3498 dbms_output.put_line(' l_response.result_category : '|| l_response.result_category);
3499 dbms_output.put_line(' l_response.result_message : '|| l_response.result_message);
3500 */
3501
3502 -- debug log
3503 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3504 fnd_log.STRING
3505 (fnd_log.level_statement,
3506 g_module ||
3507 l_api_name,
3508 '600: After Calling IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension'
3509 );
3510 fnd_log.STRING (fnd_log.level_statement,
3511 g_module ||
3512 l_api_name,
3513 '600: x_return_status : ' ||
3514 x_return_status
3515 );
3516 fnd_log.STRING (fnd_log.level_statement,
3517 g_module ||
3518 l_api_name,
3519 '600: x_new_txn_entension_id : ' ||
3520 x_new_txn_entension_id
3521 );
3522 fnd_log.STRING (fnd_log.level_statement,
3523 g_module ||
3524 l_api_name,
3525 '600: x_msg_count : ' ||
3526 x_msg_count
3527 );
3528 fnd_log.STRING (fnd_log.level_statement,
3529 g_module ||
3530 l_api_name,
3531 '600: x_response.result_code : ' ||
3532 l_response.result_code
3533 );
3534 fnd_log.STRING (fnd_log.level_statement,
3535 g_module ||
3536 l_api_name,
3537 '600: x_response.result_category : ' ||
3538 l_response.result_category
3539 );
3540 fnd_log.STRING (fnd_log.level_statement,
3541 g_module ||
3542 l_api_name,
3543 '600: x_response.result_message : ' ||
3544 l_response.result_message
3545 );
3546 END IF;
3547
3548 IF (x_return_status = g_ret_sts_unexp_error) THEN
3549 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3550 fnd_message.set_token
3551 ('IBY_API_NAME',
3552 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3553 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3554 fnd_msg_pub.ADD;
3555 RAISE fnd_api.g_exc_unexpected_error;
3556 ELSIF (x_return_status = g_ret_sts_error) THEN
3557 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
3558 fnd_message.set_token
3559 ('IBY_API_NAME',
3560 'IBY_FNDCPT_TRXN_PUB.Create_Transaction_Extension');
3561 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
3562 fnd_msg_pub.ADD;
3563 RAISE fnd_api.g_exc_error;
3564 END IF;
3565
3566 -- end debug log
3567 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3568 fnd_log.STRING (fnd_log.level_procedure,
3569 g_module ||
3570 l_api_name,
3571 '1000: Leaving ' ||
3572 g_pkg_name ||
3573 '.' ||
3574 l_api_name
3575 );
3576 END IF;
3577 EXCEPTION
3578 WHEN fnd_api.g_exc_error THEN
3579 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3580 fnd_log.STRING (fnd_log.level_procedure,
3581 g_module ||
3582 l_api_name,
3583 '2000: Leaving ' ||
3584 g_pkg_name ||
3585 '.' ||
3586 l_api_name
3587 );
3588 END IF;
3589
3590 x_return_status := g_ret_sts_error;
3591 fnd_msg_pub.count_and_get (p_encoded => 'F',
3592 p_count => x_msg_count,
3593 p_data => x_msg_data
3594 );
3595 WHEN fnd_api.g_exc_unexpected_error THEN
3596 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3597 fnd_log.STRING (fnd_log.level_procedure,
3598 g_module ||
3599 l_api_name,
3600 '3000: Leaving ' ||
3601 g_pkg_name ||
3602 '.' ||
3603 l_api_name
3604 );
3605 END IF;
3606
3607 x_return_status := g_ret_sts_unexp_error;
3608 fnd_msg_pub.count_and_get (p_encoded => 'F',
3609 p_count => x_msg_count,
3610 p_data => x_msg_data
3611 );
3612 WHEN OTHERS THEN
3613 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3614 fnd_log.STRING (fnd_log.level_procedure,
3615 g_module ||
3616 l_api_name,
3617 '4000: Leaving ' ||
3618 g_pkg_name ||
3619 '.' ||
3620 l_api_name
3621 );
3622 END IF;
3623
3624 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3625 fnd_msg_pub.count_and_get (p_encoded => 'F',
3626 p_count => x_msg_count,
3627 p_data => x_msg_data
3628 );
3629 END process_credit_card;
3630
3631 ---------------------------------------------------
3632 PROCEDURE get_contract_salesrep_details (
3633 p_chr_id IN NUMBER,
3634 x_salesrep_email OUT NOCOPY VARCHAR2,
3635 x_salesrep_username OUT NOCOPY VARCHAR2,
3636 x_return_status OUT NOCOPY VARCHAR2,
3637 x_msg_data OUT NOCOPY VARCHAR2,
3638 x_msg_count OUT NOCOPY NUMBER
3639 ) AS
3640 l_api_name CONSTANT VARCHAR2 (30)
3641 := 'get_contract_salesrep_details';
3642
3643 CURSOR csr_k_salesrep IS
3644 SELECT srp.email_address AS email_address,
3645 res.user_name AS username
3646 FROM okc_k_headers_all_b khr,
3647 okc_contacts ct,
3648 jtf_rs_salesreps srp,
3649 jtf_rs_resource_extns res
3650 WHERE khr.ID = ct.dnz_chr_id
3651 AND ct.object1_id1 = srp.salesrep_id
3652 AND srp.resource_id = res.resource_id
3653 AND srp.org_id = khr.authoring_org_id
3654 AND ct.jtot_object1_code='OKX_SALEPERS' --bug 6243682
3655 AND res.CATEGORY IN
3656 ('EMPLOYEE', 'OTHER', 'PARTY', 'PARTNER', 'SUPPLIER_CONTACT')
3657 -- AND srp.email_address IS NOT NULL -- bug 4918198
3658 AND res.user_name IS NOT NULL -- Salesrep MUST BE a FND USER
3659 AND khr.ID = p_chr_id;
3660
3661 CURSOR csr_party_helpdesk (
3662 p_k_party_id IN NUMBER
3663 ) IS
3664 SELECT gcd.email_address,
3665 fnd.user_name
3666 FROM oks_k_defaults gcd,
3667 fnd_user fnd
3668 WHERE gcd.user_id = fnd.user_id
3669 AND gcd.cdt_type = 'SDT'
3670 AND gcd.jtot_object_code = 'OKX_PARTY'
3671 -- AND gcd.email_address IS NOT NULL -- bug 4918198
3672 AND gcd.segment_id1 = p_k_party_id;
3673
3674 CURSOR csr_org_helpdesk (
3675 p_k_org_id IN NUMBER
3676 ) IS
3677 SELECT gcd.email_address,
3678 fnd.user_name
3679 FROM oks_k_defaults gcd,
3680 fnd_user fnd
3681 WHERE gcd.user_id = fnd.user_id
3682 AND gcd.cdt_type = 'SDT'
3683 AND gcd.jtot_object_code = 'OKX_OPERUNIT'
3684 -- AND gcd.email_address IS NOT NULL -- bug 4918198
3685 AND gcd.segment_id1 = p_k_org_id;
3686
3687 CURSOR csr_global_helpdesk IS
3688 SELECT gcd.email_address,
3689 fnd.user_name
3690 FROM oks_k_defaults gcd,
3691 fnd_user fnd
3692 WHERE gcd.user_id = fnd.user_id
3693 AND gcd.cdt_type = 'MDT';
3694 BEGIN
3695 -- start debug log
3696 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3697 fnd_log.STRING (fnd_log.level_procedure,
3698 g_module ||
3699 l_api_name,
3700 '100: Entered ' ||
3701 g_pkg_name ||
3702 '.' ||
3703 l_api_name
3704 );
3705 fnd_log.STRING (fnd_log.level_procedure,
3706 g_module ||
3707 l_api_name,
3708 '100: ******* Parameters ********'
3709 );
3710 fnd_log.STRING (fnd_log.level_procedure,
3711 g_module ||
3712 l_api_name,
3713 '100: p_chr_id : ' ||
3714 p_chr_id
3715 );
3716 END IF;
3717
3718 -- Initialize API return status to success
3719 x_return_status := fnd_api.g_ret_sts_success;
3720
3721 OPEN csr_k_salesrep;
3722
3723 FETCH csr_k_salesrep
3724 INTO x_salesrep_email,
3725 x_salesrep_username;
3726
3727 IF csr_k_salesrep%FOUND THEN
3728 -- Salesrep exist on K
3729 CLOSE csr_k_salesrep;
3730
3731 RETURN;
3732 END IF; -- k_salesrep csr
3733
3734 CLOSE csr_k_salesrep;
3735
3736 -- Go to GCD at party level
3737 OPEN csr_party_helpdesk (p_k_party_id => get_contract_party
3738 (p_chr_id));
3739
3740 FETCH csr_party_helpdesk
3741 INTO x_salesrep_email,
3742 x_salesrep_username;
3743
3744 IF csr_party_helpdesk%FOUND THEN
3745 CLOSE csr_party_helpdesk;
3746
3747 RETURN;
3748 END IF; -- helpdesk on party level
3749
3750 CLOSE csr_party_helpdesk;
3751
3752 -- Go to GCD at organization level
3753 OPEN csr_org_helpdesk (p_k_org_id => get_contract_organization
3754 (p_chr_id));
3755
3756 FETCH csr_org_helpdesk
3757 INTO x_salesrep_email,
3758 x_salesrep_username;
3759
3760 IF csr_org_helpdesk%FOUND THEN
3761 CLOSE csr_org_helpdesk;
3762
3763 RETURN;
3764 END IF; -- helpdesk on party level
3765
3766 CLOSE csr_org_helpdesk;
3767
3768 -- Go to GCD at global level
3769 OPEN csr_global_helpdesk;
3770
3771 FETCH csr_global_helpdesk
3772 INTO x_salesrep_email,
3773 x_salesrep_username;
3774
3775 CLOSE csr_global_helpdesk;
3776
3777 -- end debug log
3778 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3779 fnd_log.STRING (fnd_log.level_procedure,
3780 g_module ||
3781 l_api_name,
3782 '1000: Leaving ' ||
3783 g_pkg_name ||
3784 '.' ||
3785 l_api_name
3786 );
3787 END IF;
3788 EXCEPTION
3789 WHEN fnd_api.g_exc_error THEN
3790 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3791 fnd_log.STRING (fnd_log.level_procedure,
3792 g_module ||
3793 l_api_name,
3794 '2000: Leaving ' ||
3795 g_pkg_name ||
3796 '.' ||
3797 l_api_name
3798 );
3799 END IF;
3800
3801 x_return_status := g_ret_sts_error;
3802 fnd_msg_pub.count_and_get (p_encoded => 'F',
3803 p_count => x_msg_count,
3804 p_data => x_msg_data
3805 );
3806 WHEN fnd_api.g_exc_unexpected_error THEN
3807 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3808 fnd_log.STRING (fnd_log.level_procedure,
3809 g_module ||
3810 l_api_name,
3811 '3000: Leaving ' ||
3812 g_pkg_name ||
3813 '.' ||
3814 l_api_name
3815 );
3816 END IF;
3817
3818 x_return_status := g_ret_sts_unexp_error;
3819 fnd_msg_pub.count_and_get (p_encoded => 'F',
3820 p_count => x_msg_count,
3821 p_data => x_msg_data
3822 );
3823 WHEN OTHERS THEN
3824 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3825 fnd_log.STRING (fnd_log.level_procedure,
3826 g_module ||
3827 l_api_name,
3828 '4000: Leaving ' ||
3829 g_pkg_name ||
3830 '.' ||
3831 l_api_name
3832 );
3833 END IF;
3834
3835 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3836 fnd_msg_pub.count_and_get (p_encoded => 'F',
3837 p_count => x_msg_count,
3838 p_data => x_msg_data
3839 );
3840 END get_contract_salesrep_details;
3841
3842 ---------------------------------------------------
3843 PROCEDURE delete_transaction_extension (
3844 p_chr_id IN NUMBER,
3845 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
3846 x_return_status OUT NOCOPY VARCHAR2,
3847 x_msg_data OUT NOCOPY VARCHAR2,
3848 x_msg_count OUT NOCOPY NUMBER
3849 ) AS
3850 l_api_name CONSTANT VARCHAR2 (30)
3851 := 'delete_transaction_extension';
3852
3853 CURSOR csr_old_txn_id IS
3854 SELECT oks.trxn_extension_id,
3855 ca.cust_account_id,
3856 ca.party_id
3857 FROM okc_k_headers_all_b okc,
3858 oks_k_headers_b oks,
3859 hz_cust_site_uses_all su,
3860 hz_cust_acct_sites_all sa,
3861 hz_cust_accounts_all ca
3862 WHERE oks.chr_id = okc.ID
3863 AND okc.bill_to_site_use_id = su.site_use_id
3864 AND su.cust_acct_site_id = sa.cust_acct_site_id
3865 AND sa.cust_account_id = ca.cust_account_id
3866 AND oks.trxn_extension_id IS NOT NULL
3867 AND okc.ID = p_chr_id;
3868
3869 l_cust_account_id hz_cust_accounts_all.cust_account_id%TYPE;
3870 l_party_id hz_cust_accounts_all.party_id%TYPE;
3871 l_trxn_extension_id oks_k_headers_b.trxn_extension_id%TYPE;
3872
3873 SUBTYPE l_payer_type IS iby_fndcpt_common_pub.payercontext_rec_type;
3874
3875 l_payer l_payer_type;
3876 l_response iby_fndcpt_common_pub.result_rec_type;
3877 BEGIN
3878 -- start debug log
3879 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3880 fnd_log.STRING (fnd_log.level_procedure,
3881 g_module ||
3882 l_api_name,
3883 '100: Entered ' ||
3884 g_pkg_name ||
3885 '.' ||
3886 l_api_name
3887 );
3888 fnd_log.STRING (fnd_log.level_procedure,
3889 g_module ||
3890 l_api_name,
3891 '100: ******* Parameters ********'
3892 );
3893 fnd_log.STRING (fnd_log.level_procedure,
3894 g_module ||
3895 l_api_name,
3896 '100: p_chr_id : ' ||
3897 p_chr_id
3898 );
3899 END IF;
3900
3901 -- Initialize API return status to success
3902 x_return_status := fnd_api.g_ret_sts_success;
3903
3904 OPEN csr_old_txn_id;
3905
3906 FETCH csr_old_txn_id
3907 INTO l_trxn_extension_id,
3908 l_cust_account_id,
3909 l_party_id;
3910
3911 IF csr_old_txn_id%FOUND THEN
3912 -- old txn extension id exists, call iby delete API
3913
3914 -- populate the payer record
3915 l_payer.payment_function := 'CUSTOMER_PAYMENT';
3916 l_payer.party_id := l_party_id;
3917 l_payer.cust_account_id := l_cust_account_id;
3918
3919 -- debug log
3920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3921 fnd_log.STRING (fnd_log.level_statement,
3922 g_module ||
3923 l_api_name,
3924 '150: Found old txn extension id : ' ||
3925 l_trxn_extension_id
3926 );
3927 fnd_log.STRING
3928 (fnd_log.level_statement,
3929 g_module ||
3930 l_api_name,
3931 '150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3932 );
3933 fnd_log.STRING (fnd_log.level_statement,
3934 g_module ||
3935 l_api_name,
3936 '150: ***** Parameters *****'
3937 );
3938 fnd_log.STRING (fnd_log.level_statement,
3939 g_module ||
3940 l_api_name,
3941 '150: l_payer.Payment_Function : ' ||
3942 'CUSTOMER_PAYMENT'
3943 );
3944 fnd_log.STRING (fnd_log.level_statement,
3945 g_module ||
3946 l_api_name,
3947 '150: l_payer.Party_Id : ' ||
3948 l_payer.party_id
3949 );
3950 fnd_log.STRING (fnd_log.level_statement,
3951 g_module ||
3952 l_api_name,
3953 '150: l_payer.cust_account_id : ' ||
3954 l_payer.cust_account_id
3955 );
3956 fnd_log.STRING (fnd_log.level_statement,
3957 g_module ||
3958 l_api_name,
3959 '150: l_trxn_extension_id : ' ||
3960 l_trxn_extension_id
3961 );
3962 fnd_log.STRING (fnd_log.level_statement,
3963 g_module ||
3964 l_api_name,
3965 '150: p_commit : ' ||
3966 p_commit
3967 );
3968 END IF;
3969
3970 iby_fndcpt_trxn_pub.delete_transaction_extension
3971 (p_api_version => 1.0,
3972 p_init_msg_list => fnd_api.g_false,
3973 p_commit => p_commit,
3974 x_return_status => x_return_status,
3975 x_msg_count => x_msg_count,
3976 x_msg_data => x_msg_data,
3977 p_payer => l_payer,
3978 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
3979 p_entity_id => l_trxn_extension_id,
3980 x_response => l_response
3981 );
3982
3983 -- debug log
3984 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3985 fnd_log.STRING
3986 (fnd_log.level_statement,
3987 g_module ||
3988 l_api_name,
3989 '200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
3990 );
3991 fnd_log.STRING (fnd_log.level_statement,
3992 g_module ||
3993 l_api_name,
3994 '200: x_return_status : ' ||
3995 x_return_status
3996 );
3997 fnd_log.STRING (fnd_log.level_statement,
3998 g_module ||
3999 l_api_name,
4000 '200: x_msg_count : ' ||
4001 x_msg_count
4002 );
4003 fnd_log.STRING (fnd_log.level_statement,
4004 g_module ||
4005 l_api_name,
4006 '200: x_response.result_code : ' ||
4007 l_response.result_code
4008 );
4009 fnd_log.STRING (fnd_log.level_statement,
4010 g_module ||
4011 l_api_name,
4012 '200: x_response.result_category : ' ||
4013 l_response.result_category
4014 );
4015 fnd_log.STRING (fnd_log.level_statement,
4016 g_module ||
4017 l_api_name,
4018 '200: x_response.result_message : ' ||
4019 l_response.result_message
4020 );
4021 END IF; -- debug log
4022
4023 IF (x_return_status = g_ret_sts_unexp_error) THEN
4024 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
4025 fnd_message.set_token
4026 ('IBY_API_NAME',
4027 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
4028 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
4029 fnd_msg_pub.ADD;
4030 RAISE fnd_api.g_exc_unexpected_error;
4031 ELSIF (x_return_status = g_ret_sts_error) THEN
4032 /*
4033 bug 5486543
4034 iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
4035 In OKS QA check, we get a authorization from iby to validate credit card
4036 If QA check is run on the contract, then delete will fail and iby will return an error
4037 We will ignore Error from iby when delete txn is called.
4038
4039 fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
4040 fnd_message.set_token
4041 ('IBY_API_NAME',
4042 'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
4043 fnd_message.set_token ('ERROR_DTLS', l_response.result_message);
4044 fnd_msg_pub.ADD;
4045 RAISE fnd_api.g_exc_error;
4046 */
4047 x_return_status := fnd_api.g_ret_sts_success; -- initialize
4048 null; -- error is ignored
4049 END IF;
4050 END IF; -- csr_old_txn_id%FOUND
4051
4052 CLOSE csr_old_txn_id;
4053
4054 -- end debug log
4055 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4056 fnd_log.STRING (fnd_log.level_procedure,
4057 g_module ||
4058 l_api_name,
4059 '1000: Leaving ' ||
4060 g_pkg_name ||
4061 '.' ||
4062 l_api_name
4063 );
4064 END IF;
4065 EXCEPTION
4066 WHEN fnd_api.g_exc_error THEN
4067 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4068 fnd_log.STRING (fnd_log.level_procedure,
4069 g_module ||
4070 l_api_name,
4071 '2000: Leaving ' ||
4072 g_pkg_name ||
4073 '.' ||
4074 l_api_name
4075 );
4076 END IF;
4077
4078 x_return_status := g_ret_sts_error;
4079 fnd_msg_pub.count_and_get (p_encoded => 'F',
4080 p_count => x_msg_count,
4081 p_data => x_msg_data
4082 );
4083 WHEN fnd_api.g_exc_unexpected_error THEN
4084 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4085 fnd_log.STRING (fnd_log.level_procedure,
4086 g_module ||
4087 l_api_name,
4088 '3000: Leaving ' ||
4089 g_pkg_name ||
4090 '.' ||
4091 l_api_name
4092 );
4093 END IF;
4094
4095 x_return_status := g_ret_sts_unexp_error;
4096 fnd_msg_pub.count_and_get (p_encoded => 'F',
4097 p_count => x_msg_count,
4098 p_data => x_msg_data
4099 );
4100 WHEN OTHERS THEN
4101 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4102 fnd_log.STRING (fnd_log.level_procedure,
4103 g_module ||
4104 l_api_name,
4105 '4000: Leaving ' ||
4106 g_pkg_name ||
4107 '.' ||
4108 l_api_name
4109 );
4110 END IF;
4111
4112 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4113 fnd_msg_pub.count_and_get (p_encoded => 'F',
4114 p_count => x_msg_count,
4115 p_data => x_msg_data
4116 );
4117 END delete_transaction_extension;
4118 ---------------------------------------------------
4119 END oks_customer_acceptance_pvt;