[Home] [Help]
PACKAGE BODY: APPS.OKS_AUTO_REMINDER
Source
1 PACKAGE BODY oks_auto_reminder AS
2 /* $Header: OKSARNWB.pls 120.26.12010000.2 2008/10/22 12:48:37 ssreekum ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (200)
4 := 'OKS_AUTO_REMINDER';
5 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
6 g_module CONSTANT VARCHAR2 (250)
7 := 'oks.plsql.' ||
8 g_pkg_name ||
9 '.';
10 g_application_id CONSTANT NUMBER := 515; -- OKS Application
11 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
12 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
13 g_ret_sts_success CONSTANT VARCHAR2 (1)
14 := fnd_api.g_ret_sts_success;
15 g_ret_sts_error CONSTANT VARCHAR2 (1)
16 := fnd_api.g_ret_sts_error;
17 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
18 := fnd_api.g_ret_sts_unexp_error;
19
20 FUNCTION get_org_context
21 RETURN NUMBER
22 IS
23 BEGIN
24 okc_context.set_okc_org_context;
25 RETURN okc_context.get_okc_org_id;
26 END;
27
28 FUNCTION get_org_id
29 RETURN VARCHAR2
30 IS
31 BEGIN
32 okc_context.set_okc_org_context;
33 RETURN TO_CHAR (okc_context.get_okc_org_id);
34 END;
35
36 FUNCTION get_org_context (
37 p_org_id NUMBER
38 )
39 RETURN NUMBER
40 IS
41 BEGIN
42 okc_context.set_okc_org_context (p_org_id => p_org_id);
43 RETURN okc_context.get_okc_org_id;
44 END;
45
46 PROCEDURE get_qto_email (
47 p_chr_id IN NUMBER,
48 x_qto_email OUT NOCOPY VARCHAR2
49 )
50 IS
51 CURSOR l_qto_details_csr (
52 p_chr_id NUMBER
53 )
54 IS
55 SELECT quote_to_email_id
56 FROM oks_k_headers_b
57 WHERE chr_id = p_chr_id;
58
59 CURSOR l_emailaddress_csr (
60 p_contactpoint_id NUMBER
61 )
62 IS
63 SELECT email_address
64 FROM okx_contact_points_v
65 WHERE contact_point_id = p_contactpoint_id;
66
67 l_email_address VARCHAR2 (2000)
68 := okc_api.g_miss_char;
69 l_contact_id NUMBER;
70 l_api_name CONSTANT VARCHAR2 (30) := 'get_QTO_email';
71 BEGIN
72 OPEN l_qto_details_csr (p_chr_id);
73
74 FETCH l_qto_details_csr
75 INTO l_contact_id;
76
77 IF l_qto_details_csr%FOUND
78 THEN
79 OPEN l_emailaddress_csr (l_contact_id);
80
81 FETCH l_emailaddress_csr
82 INTO l_email_address;
83
84 IF l_emailaddress_csr%NOTFOUND
85 THEN
86 l_email_address := okc_api.g_miss_char;
87 END IF;
88
89 CLOSE l_emailaddress_csr;
90 END IF;
91
92 CLOSE l_qto_details_csr;
93
94 x_qto_email := l_email_address;
95 EXCEPTION
96 WHEN OTHERS
97 THEN
98 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
99 END;
100
101 -- Traverse thru the global defaults and get the template set id
102 -- associated for this contract
103 PROCEDURE get_party_id (
104 p_chr_id IN NUMBER,
105 x_party_id OUT NOCOPY NUMBER
106 )
107 IS
108 CURSOR csr_party_dtls
109 IS
110 SELECT r.object1_id1 AS party_id
111 FROM okc_k_party_roles_b r,
112 hz_parties p
113 WHERE p.party_id = r.object1_id1
114 AND r.jtot_object1_code = 'OKX_PARTY'
115 AND r.rle_code IN
116 ('CUSTOMER', 'SUBSCRIBER')
117 -- gets only the CUSTOMER /SUBSCRIBER
118 AND r.cle_id IS NULL
119 AND r.chr_id = p_chr_id;
120 /*
121 CURSOR l_CustPartyId_csr IS
122 SELECT distinct hp.party_id
123 FROM okc_k_party_roles_b okpr,
124 hz_parties hp,
125 --NPALEPU
126 --08-AUG-2005
127 --TCA Project
128 --Replaced hz_party_relationships table with hz_relationships table
129 -- hz_party_relationships hpr --
130 hz_relationships hpr
131 --END NPALEPU
132 WHERE okpr.rle_code = 'CUSTOMER'
133 AND jtot_object1_code = 'OKX_PARTY'
134 AND okpr.object1_id1 = hp.party_id
135 AND hpr.object_id = hp.party_id
136 AND okpr.dnz_chr_id = p_chr_id;
137 */
138 BEGIN
139 /*
140 OPEN l_CustPartyId_csr;
141 FETCH l_CustPartyId_csr into x_party_id;
142 CLOSE l_CustPartyId_csr;
143 */
144 OPEN csr_party_dtls;
145
146 FETCH csr_party_dtls
147 INTO x_party_id;
148
149 CLOSE csr_party_dtls;
150 END get_party_id;
151
152 PROCEDURE get_qtoparty_id (
153 p_chr_id IN NUMBER,
154 x_party_id OUT NOCOPY NUMBER
155 )
156 IS
157 /*
158 CURSOR l_QtoPartyId_csr IS
159 SELECT hzr.party_id
160 FROM oks_k_headers_b kh,
161 --NPALEPU
162 --08-AUG-2005
163 --TCA Project
164 --Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts with OKS_RA_HCONTACTS_V
165 --Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
166 --ra_hcontacts rah,
167 hz_party_relationships hzr
168 WHERE kh.chr_id = p_chr_id
169 AND kh.quote_to_contact_id = rah.contact_id
170 AND rah.party_relationship_id = hzr.party_relationship_id;
171 OKS_RA_HCONTACTS_V rah,
172 hz_relationships hzr
173 WHERE kh.chr_id = p_chr_id
174 AND kh.quote_to_contact_id = rah.contact_id
175 AND rah.party_relationship_id = hzr.relationship_id
176 AND hzr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
177 AND hzr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
178 AND hzr.DIRECTIONAL_FLAG = 'F';
179 --END NPALEPU
180 */
181
182 -- skekkar
183 l_person_party_id oks_k_headers_b.person_party_id%TYPE;
184 l_quote_to_contact_id oks_k_headers_b.quote_to_contact_id%TYPE;
185
186 CURSOR csr_person_party_id
187 IS
188 SELECT ks.person_party_id,
189 ks.quote_to_contact_id
190 FROM oks_k_headers_b ks
191 WHERE ks.chr_id = p_chr_id;
192
193 CURSOR csr_qtc_person_party_id (
194 p_quote_to_contact_id IN NUMBER
195 )
196 IS
197 SELECT hzp.party_id
198 FROM hz_cust_account_roles car,
199 hz_relationships rln,
200 hz_parties hzp
201 WHERE car.cust_account_role_id = p_quote_to_contact_id
202 AND car.party_id = rln.party_id
203 AND rln.subject_id = hzp.party_id
204 AND car.role_type = 'CONTACT'
205 AND rln.directional_flag = 'F'
206 AND rln.content_source_type = 'USER_ENTERED';
207 BEGIN
208 /*
209 OPEN l_QtoPartyId_csr;
210 FETCH l_QtoPartyId_csr into x_party_id;
211 CLOSE l_QtoPartyId_csr;
212 */
213
214 -- Get the Person Party Id and quote_to_contact_id from oks_k_headers_b
215 OPEN csr_person_party_id;
216
217 FETCH csr_person_party_id
218 INTO l_person_party_id,
219 l_quote_to_contact_id;
220
221 CLOSE csr_person_party_id;
222
223 -- if the person_party_id is NULL then get person_party_id for quote_to_contact_id
224 IF l_person_party_id IS NULL
225 THEN
226 OPEN csr_qtc_person_party_id
227 (p_quote_to_contact_id => l_quote_to_contact_id);
228
229 FETCH csr_qtc_person_party_id
230 INTO l_person_party_id;
231
232 CLOSE csr_qtc_person_party_id;
233 END IF;
234
235 x_party_id := l_person_party_id;
236 END get_qtoparty_id;
237
238 /*
239 PROCEDURE create_user (
240 p_user_name IN VARCHAR2,
241 x_password OUT NOCOPY VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_err_msg OUT NOCOPY VARCHAR2
244 ) IS
245
246 l_counter NUMBER;
247 l_err_msg OKS_AUTO_REMINDER.message_rec_tbl;
248
249 BEGIN
250
251 OKS_AUTO_REMINDER.create_user(
252 p_user_name => p_user_name,
253 x_password => x_password,
254 x_return_status => x_return_status,
255 x_err_msg => l_err_msg
256 );
257
258 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
259
260 l_counter := l_err_msg.FIRST;
261 IF l_counter > 0 THEN
262 LOOP
263 x_err_msg := x_err_msg || ';' || l_err_msg(l_counter).description;
264
265 EXIT WHEN l_counter = l_err_msg.LAST;
266 l_counter := l_err_msg.next(l_counter);
267 END LOOP;
268 END IF;
269 END IF;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
274 OKC_API.set_message ( G_APP_NAME,
275 G_UNEXPECTED_ERROR,
276 G_SQLCODE_TOKEN,
277 SQLCODE,
278 G_SQLERRM_TOKEN,
279 SQLERRM
280 );
281
282 END;
283 */
284
285 /*
286 SSO Changes for 11.5.10 CU3
287
288 Pseudo Logic:
289 -------------
290 Step 1: Get the person_party_id for quote to contact id
291
292 Step 2: Get the user_name and person_party_id in fnd_user where user_name is quote to contact email address
293 Case A: Record found in fnd_user
294 If person_party_id in fnd_user is null then
295 UPDATE fnd_user record with person_party_id from step 1 above
296 If person_party_id in fnd_user is NOT the same as person_party_id from step 1 above
297 RAISE error here
298 If person_party_id in fnd_user = person_party_id from step 1 above
299 we are fine, do nothing
300
301 Case B: Record NOT found in fnd_user
302 Call FND_USER_PKG.TestUserName
303 --@ TestUserName() returns:
304 --@ USER_OK_CREATE constant pls_integer := 0;
305 --@ USER_INVALID_NAME constant pls_integer := 1;
306 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
307 --@ USER_SYNCHED constant pls_integer := 3;
308 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
309 IF l_test_user IN (0,3) THEN
310 CALL FND_USER_PKG.CreateUserIdParty
311 Note : Call FND_CRYPTO for generating the password (track bug 4358822)
312 FND_USER_RESP_GROUPS_API.insert_assignment with responsibility as 'OKS_ERN_WEB'
313 ELSE -- l_test_user <> 0 ,3
314 -- error, raise exception
315 RAISE FND_API.G_EXC_ERROR;
316 END IF;
317
318 */
319 PROCEDURE create_user (
320 p_user_name IN VARCHAR2,
321 p_contract_id IN NUMBER,
322 x_password OUT NOCOPY VARCHAR2,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_err_msg OUT NOCOPY oks_auto_reminder.message_rec_tbl
325 )
326 IS
327 CURSOR l_user_csr (
328 p_user_name IN VARCHAR2
329 )
330 IS
331 SELECT user_id,
332 person_party_id
333 FROM fnd_user
334 WHERE UPPER (user_name) = p_user_name
335 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
336 AND NVL (end_date, SYSDATE);
337
338 CURSOR l_user_resp_csr (
339 p_user_id IN NUMBER,
340 p_responsibility_id IN NUMBER
341 )
342 IS
343 SELECT user_id
344 FROM fnd_user_resp_groups
345 WHERE user_id = p_user_id
346 AND responsibility_id = p_responsibility_id
347 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
348 AND NVL (end_date, SYSDATE);
349
350 CURSOR l_resp_csr (
351 p_resp_key VARCHAR2
352 )
353 IS
354 SELECT responsibility_id
355 FROM fnd_responsibility
356 WHERE responsibility_key = p_resp_key
357 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
358 AND NVL (end_date, SYSDATE);
359
360 CURSOR l_security_grp_csr (
361 p_security_grp_key VARCHAR2
362 )
363 IS
364 SELECT security_group_id
365 FROM fnd_security_groups
366 WHERE security_group_key = p_security_grp_key;
367
368 -- modified cursor to always generate minimum length password
369 -- as defined by the Signon Password Length profile option.
370 -- Bug fix 4228115
371 CURSOR l_password_csr
372 IS
373 SELECT CONCAT
374 (DBMS_RANDOM.STRING
375 ('l',
376 (NVL
377 (fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
378 7) -
379 3
380 )),
381 ROUND (DBMS_RANDOM.VALUE (100, 999)))
382 FROM DUAL;
383
384 l_return_status VARCHAR2 (1)
385 := okc_api.g_ret_sts_success;
386 l_user_name VARCHAR2 (100);
387 l_user_id NUMBER (15);
388 l_temp_user_id NUMBER (15);
389 l_responsibility_id NUMBER (15);
390 l_security_grp_id NUMBER (15);
391 l_return_value BOOLEAN;
392 l_count BINARY_INTEGER := 1;
393
394 -- SSO changes
395 CURSOR csr_qtc_person_party_id
396 IS
397 SELECT hzp.party_id
398 FROM hz_cust_account_roles car,
399 hz_relationships rln,
400 hz_parties hzp,
401 oks_k_headers_b ks
402 WHERE ks.quote_to_contact_id = car.cust_account_role_id
403 AND car.party_id = rln.party_id
404 AND rln.subject_id = hzp.party_id
405 AND car.role_type = 'CONTACT'
406 AND rln.directional_flag = 'F'
407 AND rln.content_source_type = 'USER_ENTERED'
408 AND ks.chr_id = p_contract_id;
409
410 l_api_name CONSTANT VARCHAR2 (30) := 'create_user';
411 l_test_user PLS_INTEGER;
412 l_qtc_person_party_id fnd_user.person_party_id%TYPE;
413 l_fnd_person_party_id fnd_user.person_party_id%TYPE;
414 l_row_notfound BOOLEAN := TRUE;
415 --
416 BEGIN
417 -- start debug log
418 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
419 THEN
420 fnd_log.STRING (fnd_log.level_procedure,
421 g_module ||
422 l_api_name,
423 '100: Entered ' ||
424 g_pkg_name ||
425 '.' ||
426 l_api_name
427 );
428 END IF;
429
430 -- Initialize API return status to success
431 x_return_status := fnd_api.g_ret_sts_success;
432
433 OPEN csr_qtc_person_party_id;
434
435 FETCH csr_qtc_person_party_id
436 INTO l_qtc_person_party_id;
437
438 CLOSE csr_qtc_person_party_id;
439
440 l_user_name := UPPER (TRIM (p_user_name));
441
442 OPEN l_user_csr (l_user_name);
443
444 FETCH l_user_csr
445 INTO l_user_id,
446 l_fnd_person_party_id;
447
448 l_row_notfound := l_user_csr%NOTFOUND;
449
450 CLOSE l_user_csr;
451
452 IF l_row_notfound
453 THEN
454 -- create a NEW FND USER
455 -- Call the testUserName pkg
456 --@ TestUserName() returns:
457 --@ USER_OK_CREATE constant pls_integer := 0;
458 --@ USER_INVALID_NAME constant pls_integer := 1;
459 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
460 --@ USER_SYNCHED constant pls_integer := 3;
461 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
462 l_test_user :=
463 fnd_user_pkg.testusername (x_user_name => l_user_name);
464
465 IF l_test_user IN (0, 3)
466 THEN
467 IF l_test_user = 0
468 THEN
469 -- ok to create a new user
470 x_password :=
471 DBMS_RANDOM.STRING
472 ('l',
473 (NVL (fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
474 7) -
475 3
476 )) ||
477 ROUND (DBMS_RANDOM.VALUE (100, 999));
478 l_user_id :=
479 fnd_user_pkg.createuseridparty
480 (x_user_name => UPPER
481 (TRIM
482 (p_user_name)),
483 x_owner => 'CUST',
484 x_unencrypted_password => x_password,
485 x_description => 'Electronic renewals User',
486 x_email_address => UPPER
487 (TRIM
488 (p_user_name)),
489 x_person_party_id => l_qtc_person_party_id
490 );
491 ELSE -- l_test_user = 3
492 -- USER_SYNCHED constant pls_integer := 3;
493 -- Call the FND_USER_PKG.CreateUserIdParty WITHOUT password as password exists in OID
494 -- in Notification, put the password as *******'
495 l_user_id :=
496 fnd_user_pkg.createuseridparty
497 (x_user_name => UPPER
498 (TRIM
499 (p_user_name)),
500 x_owner => 'SEED',
501 x_description => 'Electronic renewals User',
502 x_email_address => UPPER
503 (TRIM
504 (p_user_name)),
505 x_person_party_id => l_qtc_person_party_id
506 );
507 x_password := '******';
508 END IF; -- l_test_user = 0 or 3
509
510 IF l_user_id IS NOT NULL
511 THEN
512 -- assign responsibility to user created
513 OPEN l_resp_csr (p_resp_key => g_ern_web_responsibility);
514
515 FETCH l_resp_csr
516 INTO l_responsibility_id;
517
518 CLOSE l_resp_csr;
519
520 OPEN l_security_grp_csr ('STANDARD');
521
522 FETCH l_security_grp_csr
523 INTO l_security_grp_id;
524
525 CLOSE l_security_grp_csr;
526
527 fnd_user_resp_groups_api.insert_assignment
528 (user_id => l_user_id,
529 responsibility_id => l_responsibility_id,
530 responsibility_application_id => 515,
531 security_group_id => l_security_grp_id,
532 description => 'Electronic renewals User',
533 start_date => SYSDATE,
534 end_date => NULL
535 );
536 l_return_value :=
537 fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
538 x_value => 'PHP',
539 x_level_name => 'USER',
540 x_level_value => TO_CHAR
541 (l_user_id)
542 );
543
544 IF l_return_value
545 THEN
546 RETURN;
547 ELSE
548 -- error in fnd_profile.save
549 fnd_message.set_name ('OKS', 'OKS_SSO_FND_PROFILE_ERROR');
550 x_err_msg (l_count).description := fnd_message.get;
551 RAISE fnd_api.g_exc_error;
552 END IF;
553 ELSE
554 -- l_user_id is null, raise exception
555 fnd_message.set_name ('OKS', 'OKS_SSO_USER_ID_NULL');
556 x_err_msg (l_count).description := fnd_message.get;
557 RAISE fnd_api.g_exc_error;
558 END IF; -- l_user_id is not null
559 ELSE -- l_test_user <> 0 ,3
560 -- error, raise exception
561 fnd_message.set_name ('OKS', 'OKS_SSO_TEST_USER_ERROR');
562 fnd_message.set_token ('RETURN_VAL', l_test_user);
563 x_err_msg (l_count).description := fnd_message.get;
564 RAISE fnd_api.g_exc_error;
565 END IF; -- l_test_user check
566 ELSE -- l_row_notfound is false i.e record exists in fnd_user
567 x_password := '******';
568 IF l_fnd_person_party_id IS NULL
569 THEN
570 fnd_user_pkg.updateuserparty
571 (x_user_name => UPPER
572 (TRIM
573 (p_user_name)),
574 x_owner => 'CUST',
575 x_person_party_id => l_qtc_person_party_id
576 );
577 ELSIF l_fnd_person_party_id <> l_qtc_person_party_id
578 THEN
579 -- fnd_user.person_party_id does NOT match oks_person_party_id
580 fnd_message.set_name ('OKS', 'OKS_SSO_PERSON_PARTY_ERROR');
581 x_err_msg (l_count).description := fnd_message.get;
582 RAISE fnd_api.g_exc_error;
583 ELSE
584 -- l_fnd_person_party_id = l_qtc_person_party_id
585 RETURN;
586 END IF; -- person_party_id check
587 END IF; -- IF l_row_notfound
588
589 -- end debug log
590 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
591 THEN
592 fnd_log.STRING (fnd_log.level_procedure,
593 g_module ||
594 l_api_name,
595 '1000: Leaving ' ||
596 g_pkg_name ||
597 '.' ||
598 l_api_name
599 );
600 END IF;
601 EXCEPTION
602 WHEN fnd_api.g_exc_error
603 THEN
604 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
605 THEN
606 fnd_log.STRING (fnd_log.level_procedure,
607 g_module ||
608 l_api_name,
609 '2000: Leaving ' ||
610 g_pkg_name ||
611 '.' ||
612 l_api_name
613 );
614 END IF;
615
616 x_return_status := g_ret_sts_error;
617 WHEN fnd_api.g_exc_unexpected_error
618 THEN
619 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
620 THEN
621 fnd_log.STRING (fnd_log.level_procedure,
622 g_module ||
623 l_api_name,
624 '3000: Leaving ' ||
625 g_pkg_name ||
626 '.' ||
627 l_api_name
628 );
629 END IF;
630
631 x_return_status := g_ret_sts_unexp_error;
632 WHEN OTHERS
633 THEN
634 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
635 THEN
636 fnd_log.STRING (fnd_log.level_procedure,
637 g_module ||
638 l_api_name,
639 '4000: Leaving ' ||
640 g_pkg_name ||
641 '.' ||
642 l_api_name
643 );
644 END IF;
645
646 x_return_status := okc_api.g_ret_sts_unexp_error;
647 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
648 END create_user;
649
650 PROCEDURE update_contract_status (
651 p_chr_id IN VARCHAR2,
652 p_status IN VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2
654 )
655 IS
656 l_api_name CONSTANT VARCHAR2 (30)
657 := 'update_contract_status';
658 -- bug 5161013 check STE_CODE for the contract
659 CURSOR csr_k_ste_code IS
660 SELECT s.ste_code
661 FROM okc_k_headers_all_b k,
662 okc_statuses_b s
663 WHERE k.sts_code = s.code
664 AND k.id = p_chr_id;
665
666 l_ste_code okc_statuses_b.ste_code%TYPE;
667
668 BEGIN
669 -- bug 5161013
670 -- check if the STE_CODE of the contract is ENTERED and only then update the status
671 OPEN csr_k_ste_code;
672 FETCH csr_k_ste_code INTO l_ste_code;
673 CLOSE csr_k_ste_code;
674
675 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
676 fnd_log.STRING (fnd_log.level_procedure,
677 g_module ||l_api_name,
678 'l_ste_code : ' ||l_ste_code||
679 ' (p_chr_id=> ' ||p_chr_id ||
680 ' p_status=>' ||p_status ||')'
681 );
682 END IF;
683
684 IF l_ste_code = 'ENTERED' THEN
685 oks_auto_reminder.update_contract_status
686 (p_chr_id => TO_NUMBER
687 (p_chr_id),
688 p_status => p_status,
689 x_return_status => x_return_status
690 );
691 END IF;
692
693 EXCEPTION
694 WHEN OTHERS
695 THEN
696 x_return_status := okc_api.g_ret_sts_unexp_error;
697 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
698 END;
699
700 PROCEDURE update_contract_status (
701 p_chr_id IN NUMBER,
702 p_status IN VARCHAR2,
703 x_return_status OUT NOCOPY VARCHAR2
704 )
705 IS
706 l_api_version CONSTANT NUMBER := 1.0;
707 l_api_name CONSTANT VARCHAR2 (50)
708 := 'update_contract_status';
709 l_chrv_rec okc_contract_pub.chrv_rec_type;
710 x_chrv_rec okc_contract_pub.chrv_rec_type;
711 l_clev_rec okc_contract_pub.clev_rec_type;
712 x_clev_rec okc_contract_pub.clev_rec_type;
713 l_return_status VARCHAR2 (1)
714 := okc_api.g_ret_sts_success;
715 l_msg_data VARCHAR2 (2000);
716 l_msg_count NUMBER;
717 l_status_code VARCHAR2(40);
718
719 CURSOR l_lines_csr
720 IS
721 SELECT ID,
722 sts_code
723 FROM okc_k_lines_b
724 WHERE dnz_chr_id = p_chr_id;
725
726 /*added for bug6651207*/
727 CURSOR l_lines_entered_csr
728 IS SELECT ID,
729 sts_code
730 FROM okc_k_lines_b
731 WHERE dnz_chr_id = p_chr_id
732 AND (DATE_TERMINATED IS NULL AND DATE_CANCELLED IS NULL);
733
734 FUNCTION GET_STATUS(pcode IN VARCHAR2) RETURN VARCHAR2
735 IS
736 lcode VARCHAR2(40);
737 BEGIN
738 SELECT STE_CODE INTO lcode FROM okc_statuses_b WHERE code = pcode;
739 RETURN lcode;
740 EXCEPTION
741 WHEN OTHERS THEN
742 RETURN pcode;
743 END;
744
745 BEGIN
746 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
747 THEN
748 fnd_log.STRING (fnd_log.level_procedure,
749 g_module ||
750 l_api_name,
751 'Entered ' ||
752 g_pkg_name ||
753 '.' ||
754 l_api_name ||
755 '(p_chr_id=>' ||
756 p_chr_id ||
757 'p_status=>' ||
758 p_status ||
759 ')'
760 );
761 END IF;
762
763 -- Initialize API return status to success
764 x_return_status := g_ret_sts_success;
765 mo_global.init (p_appl_short_name => 'OKC');
766 l_chrv_rec.ID := p_chr_id;
767 l_chrv_rec.sts_code := p_status;
768
769 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
770 THEN
771 fnd_log.STRING
772 (fnd_log.level_event,
773 g_module ||
774 l_api_name ||
775 '.external_call.before',
776 'OKC_CONTRACT_PUB.update_contract_header(p_chr_id=' ||
777 p_chr_id ||
778 ')'
779 );
780 END IF;
781
782 okc_contract_pub.update_contract_header
783 (p_api_version => l_api_version,
784 p_init_msg_list => okc_api.g_false,
785 x_return_status => l_return_status,
786 x_msg_count => l_msg_count,
787 x_msg_data => l_msg_data,
788 p_restricted_update => okc_api.g_false,
789 p_chrv_rec => l_chrv_rec,
790 x_chrv_rec => x_chrv_rec
791 );
792
793 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
794 THEN
795 fnd_log.STRING
796 (fnd_log.level_event,
797 g_module ||
798 l_api_name ||
799 '.external_call.after',
800 'OKC_CONTRACT_PUB.update_contract_header(x_return_status= ' ||
801 l_return_status ||
802 ' l_msg_count =' ||
803 l_msg_count ||
804 ')'
805 );
806 END IF;
807
808 IF l_return_status = g_ret_sts_unexp_error
809 THEN
810 RAISE fnd_api.g_exc_unexpected_error;
811 ELSIF l_return_status = g_ret_sts_error
812 THEN
813 RAISE fnd_api.g_exc_error;
814 END IF;
815
816 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
817 THEN
818 fnd_log.STRING (fnd_log.level_event,
819 g_module ||
820 l_api_name ||
821 '.external_call.before',
822 'OKC_CONTRACT_PUB.update_contract_line - Loop begin'
823 );
824 END IF;
825
826 l_clev_rec.sts_code := p_status;
827 /*added for bug6651207*/
828 l_status_code := get_status(p_status);
829
830 /*MODIFIED FOR BUG6651207*/
831 IF Nvl(l_status_code,'X') = 'ENTERED' THEN
832 FOR l_lines_rec IN l_lines_entered_csr
833 LOOP
834 l_clev_rec.ID := l_lines_rec.ID;
835
836 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
837 THEN
838 fnd_log.STRING (fnd_log.level_event,
839 g_module ||
840 l_api_name,
841 'Updating line: ' ||
842 l_lines_rec.ID
843 );
844 END IF;
845
846 okc_contract_pub.update_contract_line
847 (p_api_version => l_api_version,
848 p_init_msg_list => okc_api.g_false,
849 x_return_status => l_return_status,
850 x_msg_count => l_msg_count,
851 x_msg_data => l_msg_data,
852 p_restricted_update => okc_api.g_false,
853 p_clev_rec => l_clev_rec,
854 x_clev_rec => x_clev_rec
855 );
856
857 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
858 THEN
859 fnd_log.STRING (fnd_log.level_event,
860 g_module ||
861 l_api_name,
862 'Result: ' ||
863 l_return_status
864 );
865 END IF;
866
867 IF l_return_status = g_ret_sts_unexp_error
868 THEN
869 RAISE fnd_api.g_exc_unexpected_error;
870 ELSIF l_return_status = g_ret_sts_error
871 THEN
872 RAISE fnd_api.g_exc_error;
873 END IF;
874 END LOOP;
875 ELSE
876 FOR l_lines_rec IN l_lines_csr
877 LOOP
878 l_clev_rec.ID := l_lines_rec.ID;
879
880 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
881 THEN
882 fnd_log.STRING (fnd_log.level_event,
883 g_module ||
884 l_api_name,
885 'Updating line: ' ||
886 l_lines_rec.ID
887 );
888 END IF;
889
890 okc_contract_pub.update_contract_line
891 (p_api_version => l_api_version,
892 p_init_msg_list => okc_api.g_false,
893 x_return_status => l_return_status,
894 x_msg_count => l_msg_count,
895 x_msg_data => l_msg_data,
896 p_restricted_update => okc_api.g_false,
897 p_clev_rec => l_clev_rec,
898 x_clev_rec => x_clev_rec
899 );
900
901 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
902 THEN
903 fnd_log.STRING (fnd_log.level_event,
904 g_module ||
905 l_api_name,
906 'Result: ' ||
907 l_return_status
908 );
909 END IF;
910
911 IF l_return_status = g_ret_sts_unexp_error
912 THEN
913 RAISE fnd_api.g_exc_unexpected_error;
914 ELSIF l_return_status = g_ret_sts_error
915 THEN
916 RAISE fnd_api.g_exc_error;
917 END IF;
918 END LOOP;
919 END IF; /*IF NVL(l_status_code,'X') = 'ENTERED' THEN*/
920
921 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
922 THEN
923 fnd_log.STRING (fnd_log.level_event,
924 g_module ||
925 l_api_name ||
926 '.external_call.after',
927 'OKC_CONTRACT_PUB.update_contract_line - loop end'
928 );
929 END IF;
930 EXCEPTION
931 WHEN fnd_api.g_exc_error
932 THEN
933 fnd_msg_pub.count_and_get (p_encoded => 'F',
934 p_count => l_msg_count,
935 p_data => l_msg_data
936 );
937
938 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
939 THEN
940 fnd_log.STRING (fnd_log.level_procedure,
941 g_module ||
942 l_api_name,
943 'Leaving ' ||
944 g_pkg_name ||
945 '.' ||
946 l_api_name ||
947 ' from G_EXC_ERROR'
948 );
949 fnd_log.STRING (fnd_log.level_procedure,
950 g_module ||
951 l_api_name,
952 l_msg_data
953 );
954 END IF;
955
956 x_return_status := g_ret_sts_error;
957 WHEN fnd_api.g_exc_unexpected_error
958 THEN
959 fnd_msg_pub.count_and_get (p_encoded => 'F',
960 p_count => l_msg_count,
961 p_data => l_msg_data
962 );
963
964 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
965 THEN
966 fnd_log.STRING (fnd_log.level_procedure,
967 g_module ||
968 l_api_name,
969 'Leaving ' ||
970 g_pkg_name ||
971 '.' ||
972 l_api_name ||
973 ' from G_EXC_UNEXPECTED_ERROR'
974 );
975 fnd_log.STRING (fnd_log.level_procedure,
976 g_module ||
977 l_api_name,
978 l_msg_data
979 );
980 END IF;
981
982 x_return_status := g_ret_sts_unexp_error;
983 WHEN OTHERS
984 THEN
985 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
986 THEN
987 fnd_log.STRING (fnd_log.level_procedure,
988 g_module ||
989 l_api_name,
990 'Leaving ' ||
991 g_pkg_name ||
992 '.' ||
993 l_api_name ||
994 ' from OTHERS sqlcode = ' ||
995 SQLCODE ||
996 ', sqlerrm = ' ||
997 SQLERRM
998 );
999 fnd_msg_pub.add_exc_msg (g_pkg_name,
1000 l_api_name,
1001 SUBSTR (SQLERRM,
1002 1,
1003 240
1004 )
1005 );
1006 END IF;
1007
1008 fnd_msg_pub.count_and_get (p_encoded => 'F',
1009 p_count => l_msg_count,
1010 p_data => l_msg_data
1011 );
1012 x_return_status := g_ret_sts_unexp_error;
1013 END update_contract_status;
1014
1015 PROCEDURE get_time_stats (
1016 p_start_date IN DATE,
1017 p_end_date IN DATE,
1018 x_duration OUT NOCOPY NUMBER,
1019 x_period OUT NOCOPY VARCHAR2,
1020 x_return_status OUT NOCOPY VARCHAR2
1021 )
1022 IS
1023 l_api_name CONSTANT VARCHAR2 (30)
1024 := 'get_time_stats';
1025 l_duration NUMBER;
1026 l_period VARCHAR2 (10);
1027 l_return_status VARCHAR2 (1)
1028 := okc_api.g_ret_sts_success;
1029 BEGIN
1030 IF p_start_date > p_end_date
1031 THEN
1032 okc_time_util_pub.get_duration (p_start_date => p_end_date,
1033 p_end_date => p_start_date,
1034 x_duration => l_duration,
1035 x_timeunit => l_period,
1036 x_return_status => l_return_status
1037 );
1038 x_duration := l_duration;
1039 ELSE
1040 okc_time_util_pub.get_duration (p_start_date => p_start_date,
1041 p_end_date => p_end_date,
1042 x_duration => l_duration,
1043 x_timeunit => l_period,
1044 x_return_status => l_return_status
1045 );
1046 x_duration := l_duration *
1047 -1;
1048 END IF;
1049
1050 IF l_return_status <> okc_api.g_ret_sts_success
1051 THEN
1052 x_duration := okc_api.g_miss_num;
1053 x_period := okc_api.g_miss_char;
1054 ELSE
1055 x_period := l_period;
1056 END IF;
1057
1058 x_return_status := l_return_status;
1059 EXCEPTION
1060 WHEN OTHERS
1061 THEN
1062 x_return_status := okc_api.g_ret_sts_error;
1063 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1064 END;
1065
1066 PROCEDURE get_duration (
1067 p_start_date IN DATE,
1068 p_end_date IN DATE,
1069 p_source_uom IN VARCHAR2,
1070 x_duration OUT NOCOPY NUMBER
1071 )
1072 IS
1073 l_api_name CONSTANT VARCHAR2 (30) := 'get_duration';
1074 l_duration NUMBER;
1075 l_period VARCHAR2 (10);
1076 BEGIN
1077 IF p_start_date > p_end_date
1078 THEN
1079 l_duration :=
1080 oks_time_measures_pub.get_quantity (p_start_date => p_end_date,
1081 p_end_date => p_start_date,
1082 p_source_uom => p_source_uom
1083 );
1084 x_duration := l_duration;
1085 ELSE
1086 l_duration :=
1087 oks_time_measures_pub.get_quantity (p_start_date => p_start_date,
1088 p_end_date => p_end_date,
1089 p_source_uom => p_source_uom
1090 );
1091 x_duration := l_duration *
1092 -1;
1093 END IF;
1094 EXCEPTION
1095 WHEN OTHERS
1096 THEN
1097 x_duration := okc_api.g_miss_num;
1098 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1099 END;
1100
1101 PROCEDURE update_renewal_status (
1102 p_chr_id IN NUMBER,
1103 p_renewal_status IN VARCHAR2,
1104 x_return_status OUT NOCOPY VARCHAR2
1105 )
1106 IS
1107 l_api_name CONSTANT VARCHAR2 (30)
1108 := 'update_renewal_status';
1109 l_return_status VARCHAR2 (1) := 'S';
1110 l_api_version NUMBER := 1.0;
1111 l_init_msg_list VARCHAR2 (1) := 'F';
1112 l_msg_count NUMBER;
1113 l_msg_data VARCHAR2 (2000);
1114 l_khdr_rec_in oks_contract_hdr_pub.khrv_rec_type;
1115 l_khdr_rec_out oks_contract_hdr_pub.khrv_rec_type;
1116 l_kdetails_rec oks_qp_int_pvt.k_details_rec;
1117 BEGIN
1118 l_return_status := okc_api.g_ret_sts_success;
1119 oks_qp_int_pvt.get_k_details (p_id => p_chr_id,
1120 p_type => 'KHR',
1121 x_k_det_rec => l_kdetails_rec
1122 );
1123 l_khdr_rec_in.chr_id := p_chr_id;
1124 l_khdr_rec_in.renewal_status := p_renewal_status;
1125 l_khdr_rec_in.ID := l_kdetails_rec.ID;
1126 l_khdr_rec_in.object_version_number :=
1127 l_kdetails_rec.object_version_number;
1128 oks_contract_hdr_pub.update_header (p_api_version => l_api_version,
1129 p_init_msg_list => l_init_msg_list,
1130 x_return_status => l_return_status,
1131 x_msg_count => l_msg_count,
1132 x_msg_data => l_msg_data,
1133 p_khrv_rec => l_khdr_rec_in,
1134 x_khrv_rec => l_khdr_rec_out,
1135 p_validate_yn => 'N'
1136 );
1137
1138 IF (l_return_status <> okc_api.g_ret_sts_success)
1139 THEN
1140 RAISE g_exception_halt_validation;
1141 END IF;
1142
1143 x_return_status := l_return_status;
1144 EXCEPTION
1145 WHEN g_exception_halt_validation
1146 THEN
1147 x_return_status := l_return_status;
1148 WHEN OTHERS
1149 THEN
1150 x_return_status := l_return_status;
1151 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1152 END;
1153
1154 FUNCTION get_product_name (
1155 p_lse_id IN NUMBER,
1156 p_object_id IN NUMBER,
1157 p_inv_org_id IN NUMBER DEFAULT NULL
1158 )
1159 RETURN VARCHAR2
1160 IS
1161 l_name VARCHAR2 (1000);
1162 l_return_status VARCHAR2 (1);
1163
1164 CURSOR c1
1165 IS
1166 SELECT mtl.NAME
1167 FROM okx_system_items_v mtl,
1168 okx_cust_prod_v cp
1169 WHERE mtl.id1 = cp.inventory_item_id
1170 AND mtl.organization_id = p_inv_org_id
1171 AND cp.customer_product_id = p_object_id;
1172
1173 CURSOR c2
1174 IS
1175 SELECT NAME
1176 FROM okx_parties_v
1177 WHERE id1 = p_object_id
1178 AND id2 = '#';
1179
1180 CURSOR c3
1181 IS
1182 SELECT NAME
1183 FROM okx_systems_v
1184 WHERE id1 = p_object_id
1185 AND id2 = '#';
1186 BEGIN
1187 IF p_lse_id = 25
1188 THEN
1189 OPEN c1;
1190
1191 FETCH c1
1192 INTO l_name;
1193
1194 CLOSE c1;
1195 END IF;
1196
1197 IF p_lse_id = 35
1198 THEN
1199 OPEN c2;
1200
1201 FETCH c2
1202 INTO l_name;
1203
1204 CLOSE c2;
1205 END IF;
1206
1207 IF p_lse_id = 11
1208 THEN
1209 OPEN c3;
1210
1211 FETCH c3
1212 INTO l_name;
1213
1214 CLOSE c3;
1215 END IF;
1216
1217 RETURN l_name;
1218 EXCEPTION
1219 WHEN OTHERS
1220 THEN
1221 RETURN NULL;
1222 END;
1223
1224 FUNCTION get_no_of_users (
1225 p_lse_id IN NUMBER,
1226 p_object_id IN NUMBER
1227 )
1228 RETURN VARCHAR2
1229 IS
1230 l_name VARCHAR2 (1000);
1231
1232 CURSOR l_users_csr
1233 IS
1234 SELECT pricing_attribute3
1235 FROM cs_customer_products_all
1236 WHERE customer_product_id = p_object_id;
1237 BEGIN
1238 IF p_lse_id = 25
1239 THEN
1240 OPEN l_users_csr;
1241
1242 FETCH l_users_csr
1243 INTO l_name;
1244
1245 CLOSE l_users_csr;
1246 END IF;
1247
1248 RETURN l_name;
1249 EXCEPTION
1250 WHEN OTHERS
1251 THEN
1252 RETURN NULL;
1253 END;
1254
1255 FUNCTION get_service_name (
1256 p_line_id IN NUMBER,
1257 p_contract_id IN NUMBER
1258 )
1259 RETURN VARCHAR2
1260 IS
1261 CURSOR l_service_csr
1262 IS
1263 SELECT NAME
1264 FROM okx_system_items_v mtl,
1265 okc_k_items itm
1266 WHERE mtl.id1 = itm.object1_id1
1267 AND mtl.id2 = itm.object1_id2
1268 AND itm.cle_id = p_line_id
1269 AND itm.dnz_chr_id = p_contract_id;
1270
1271 l_service_name okx_system_items_v.NAME%TYPE;
1272 BEGIN
1273 OPEN l_service_csr;
1274
1275 FETCH l_service_csr
1276 INTO l_service_name;
1277
1278 CLOSE l_service_csr;
1279
1280 RETURN l_service_name;
1281 EXCEPTION
1282 WHEN OTHERS
1283 THEN
1284 IF l_service_csr%ISOPEN
1285 THEN
1286 CLOSE l_service_csr;
1287 END IF;
1288
1289 RETURN NULL;
1290 END;
1291
1292 FUNCTION get_license_level (
1293 p_lse_id IN NUMBER,
1294 p_object_id IN NUMBER
1295 )
1296 RETURN VARCHAR2
1297 IS
1298 l_name VARCHAR2 (1000);
1299
1300 CURSOR l_license_level_csr
1301 IS
1302 SELECT pricing_attribute5
1303 FROM cs_customer_products_all
1304 WHERE customer_product_id = p_object_id;
1305 BEGIN
1306 IF p_lse_id = 25
1307 THEN
1308 OPEN l_license_level_csr;
1309
1310 FETCH l_license_level_csr
1311 INTO l_name;
1312
1313 CLOSE l_license_level_csr;
1314 END IF;
1315
1316 RETURN l_name;
1317 EXCEPTION
1318 WHEN OTHERS
1319 THEN
1320 RETURN NULL;
1321 END;
1322
1323 FUNCTION get_pricing_type (
1324 p_lse_id IN NUMBER,
1325 p_object_id IN NUMBER
1326 )
1327 RETURN VARCHAR2
1328 IS
1329 l_name VARCHAR2 (1000);
1330
1331 CURSOR l_pricing_type_csr
1332 IS
1333 SELECT pricing_attribute4
1334 FROM cs_customer_products_all
1335 WHERE customer_product_id = p_object_id;
1336 BEGIN
1337 IF p_lse_id = 25
1338 THEN
1339 OPEN l_pricing_type_csr;
1340
1341 FETCH l_pricing_type_csr
1342 INTO l_name;
1343
1344 CLOSE l_pricing_type_csr;
1345 END IF;
1346
1347 RETURN l_name;
1348 EXCEPTION
1349 WHEN OTHERS
1350 THEN
1351 RETURN NULL;
1352 END;
1353
1354 FUNCTION get_billto_contact (
1355 p_org_id IN NUMBER,
1356 p_inv_org_id IN NUMBER,
1357 p_contract_id IN NUMBER
1358 )
1359 RETURN VARCHAR2
1360 IS
1361 CURSOR l_billto_contact_csr
1362 IS
1363 SELECT object1_id1
1364 FROM okc_contacts ct
1365 WHERE ct.cro_code = 'BILLING'
1366 AND ct.dnz_chr_id = p_contract_id;
1367
1368 l_id NUMBER;
1369 l_name VARCHAR2 (240);
1370
1371 CURSOR l_btc_name_csr
1372 IS
1373 SELECT NAME
1374 FROM okx_party_contacts_v
1375 WHERE id1 = l_id;
1376 BEGIN
1377 okc_context.set_okc_org_context (p_org_id, p_inv_org_id);
1378
1379 OPEN l_billto_contact_csr;
1380
1381 FETCH l_billto_contact_csr
1382 INTO l_id;
1383
1384 CLOSE l_billto_contact_csr;
1385
1386 OPEN l_btc_name_csr;
1387
1388 FETCH l_btc_name_csr
1389 INTO l_name;
1390
1391 CLOSE l_btc_name_csr;
1392
1393 RETURN l_name;
1394 EXCEPTION
1395 WHEN OTHERS
1396 THEN
1397 RETURN NULL;
1398 END;
1399
1400 FUNCTION get_billto_phone (
1401 p_contract_id IN NUMBER
1402 )
1403 RETURN VARCHAR2
1404 IS
1405 CURSOR l_phone_csr (
1406 p_id NUMBER
1407 )
1408 IS
1409 SELECT phone_area_code ||
1410 '-' ||
1411 phone_number
1412 FROM okx_contact_points_v
1413 WHERE owner_table_id = p_id
1414 AND owner_table_name = 'HZ_PARTIES'
1415 AND contact_point_type = 'PHONE'
1416 AND phone_number IS NOT NULL
1417 ORDER BY DECODE (phone_line_type,
1418 'GEN', 1,
1419 'OFFICE', 2,
1420 'PHONE', 3,
1421 'Direct Phone', 4,
1422 5
1423 );
1424
1425 CURSOR l_svc_admin_csr
1426 IS
1427 SELECT object1_id1
1428 FROM okc_contacts ct
1429 WHERE ct.cro_code = 'BILLING'
1430 AND ct.dnz_chr_id = p_contract_id;
1431
1432 l_id NUMBER;
1433 l_phone VARCHAR2 (240);
1434 BEGIN
1435 OPEN l_svc_admin_csr;
1436
1437 FETCH l_svc_admin_csr
1438 INTO l_id;
1439
1440 CLOSE l_svc_admin_csr;
1441
1442 OPEN l_phone_csr (l_id);
1443
1444 FETCH l_phone_csr
1445 INTO l_phone;
1446
1447 CLOSE l_phone_csr;
1448
1449 RETURN l_phone;
1450 EXCEPTION
1451 WHEN OTHERS
1452 THEN
1453 RETURN NULL;
1454 END;
1455
1456 FUNCTION get_billto_fax (
1457 p_contract_id IN NUMBER
1458 )
1459 RETURN VARCHAR2
1460 IS
1461 CURSOR l_phone_csr (
1462 p_id NUMBER
1463 )
1464 IS
1465 SELECT phone_area_code ||
1466 '-' ||
1467 phone_number
1468 FROM okx_contact_points_v
1469 WHERE owner_table_id = p_id
1470 AND owner_table_name = 'HZ_PARTIES'
1471 AND contact_point_type IN ('PHONE', 'FAX')
1472 AND phone_line_type = 'FAX';
1473
1474 CURSOR l_svc_admin_csr
1475 IS
1476 SELECT object1_id1
1477 FROM okc_contacts ct
1478 WHERE ct.cro_code = 'BILLING'
1479 AND ct.dnz_chr_id = p_contract_id;
1480
1481 l_id NUMBER;
1482 l_phone VARCHAR2 (240);
1483 BEGIN
1484 OPEN l_svc_admin_csr;
1485
1486 FETCH l_svc_admin_csr
1487 INTO l_id;
1488
1489 CLOSE l_svc_admin_csr;
1490
1491 OPEN l_phone_csr (l_id);
1492
1493 FETCH l_phone_csr
1494 INTO l_phone;
1495
1496 CLOSE l_phone_csr;
1497
1498 RETURN l_phone;
1499 EXCEPTION
1500 WHEN OTHERS
1501 THEN
1502 RETURN NULL;
1503 END;
1504
1505 FUNCTION get_billto_email (
1506 p_contract_id IN NUMBER
1507 )
1508 RETURN VARCHAR2
1509 IS
1510 /*
1511 Forward port for bug 5051455 (bug 5082822)
1512 modified the below cursor to get billto_email addres
1513 */
1514 CURSOR l_billto_email IS
1515 SELECT hcp.email_address
1516 FROM hz_contact_points hcp,
1517 okc_contacts kc
1518 WHERE hcp.owner_table_id = kc.object1_id1
1519 AND kc.cro_code = 'BILLING'
1520 AND hcp.contact_point_type='EMAIL'
1521 AND hcp.owner_table_name = 'HZ_PARTIES'
1522 AND hcp.content_source_type = 'USER_ENTERED'
1523 AND hcp.status = 'A'
1524 AND kc.dnz_chr_id= p_contract_id
1525 ORDER BY hcp.primary_flag desc;
1526
1527 l_email VARCHAR2 (2000);
1528
1529 BEGIN
1530 -- Forward port Bug 5051455
1531 OPEN l_billto_email;
1532 FETCH l_billto_email INTO l_email;
1533 CLOSE l_billto_email;
1534
1535 RETURN l_email;
1536
1537 EXCEPTION
1538 WHEN OTHERS
1539 THEN
1540 RETURN NULL;
1541 END;
1542
1543 PROCEDURE log_interaction (
1544 x_return_status OUT NOCOPY VARCHAR2,
1545 x_msg_count OUT NOCOPY NUMBER,
1546 x_msg_data OUT NOCOPY VARCHAR2,
1547 p_chr_id IN VARCHAR2,
1548 p_subject IN VARCHAR2 DEFAULT NULL,
1549 p_msg_body IN VARCHAR2 DEFAULT NULL,
1550 p_sent2_email IN VARCHAR2 DEFAULT NULL
1551 )
1552 IS
1553 BEGIN
1554 oks_auto_reminder.log_interaction (p_api_version => 1.0,
1555 p_init_msg_list => 'F',
1556 x_return_status => x_return_status,
1557 x_msg_count => x_msg_count,
1558 x_msg_data => x_msg_data,
1559 p_chr_id => TO_NUMBER
1560 (p_chr_id),
1561 p_subject => p_subject,
1562 p_msg_body => p_msg_body,
1563 p_sent2_email => p_sent2_email
1564 );
1565 EXCEPTION
1566 WHEN OTHERS
1567 THEN
1568 x_return_status := okc_api.g_ret_sts_unexp_error;
1569 okc_api.set_message (g_app_name,
1570 g_unexpected_error,
1571 g_sqlcode_token,
1572 SQLCODE,
1573 g_sqlerrm_token,
1574 SQLERRM
1575 );
1576 END;
1577
1578 PROCEDURE log_interaction (
1579 p_api_version IN NUMBER,
1580 p_init_msg_list IN VARCHAR2,
1581 x_return_status OUT NOCOPY VARCHAR2,
1582 x_msg_count OUT NOCOPY NUMBER,
1583 x_msg_data OUT NOCOPY VARCHAR2,
1584 p_chr_id IN NUMBER,
1585 p_subject IN VARCHAR2 DEFAULT NULL,
1586 p_msg_body IN VARCHAR2 DEFAULT NULL,
1587 p_sent2_email IN VARCHAR2 DEFAULT NULL,
1588 p_media_type IN VARCHAR2 DEFAULT 'EMAIL'
1589 )
1590 IS
1591 l_api_version CONSTANT NUMBER := 1.0;
1592 l_api_name CONSTANT VARCHAR2 (50)
1593 := 'log_interaction';
1594
1595 CURSOR l_kdetails_csr (
1596 p_chr_id NUMBER
1597 )
1598 IS
1599 SELECT contract_number,
1600 contract_number_modifier
1601 FROM okc_k_headers_all_b
1602 WHERE ID = p_chr_id;
1603
1604 CURSOR l_userres_csr (
1605 p_user_id NUMBER
1606 )
1607 IS
1608 SELECT rsc.resource_id resource_id
1609 FROM jtf_rs_resource_extns rsc,
1610 fnd_user u
1611 WHERE u.user_id = rsc.user_id
1612 AND u.user_id = p_user_id;
1613
1614 l_rownotfound BOOLEAN := FALSE;
1615 l_salesrep_resource_id NUMBER;
1616 l_salesrep_id NUMBER;
1617 l_salesrep_name VARCHAR2 (100);
1618 l_qto_email VARCHAR2 (2000);
1619 l_party_id NUMBER;
1620 l_subject VARCHAR2 (200);
1621 l_interaction_body VARCHAR2 (2000);
1622 l_interaction_id NUMBER;
1623 l_kdetails_rec l_kdetails_csr%ROWTYPE;
1624 BEGIN
1625 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
1626 THEN
1627 fnd_log.STRING (fnd_log.level_procedure,
1628 g_module ||
1629 l_api_name,
1630 'Entered ' ||
1631 g_pkg_name ||
1632 '.' ||
1633 l_api_name ||
1634 '(p_chr_id=>' ||
1635 p_chr_id ||
1636 'p_subject=>' ||
1637 SUBSTR (p_subject,
1638 1,
1639 240
1640 ) ||
1641 'p_msg_body=>' ||
1642 SUBSTR (p_msg_body,
1643 1,
1644 240
1645 ) ||
1646 'p_sent2_email=>' ||
1647 SUBSTR (p_sent2_email,
1648 1,
1649 240
1650 ) ||
1651 ')'
1652 );
1653 END IF;
1654
1655 -- Initialize message list if p_init_msg_list is set to TRUE.
1656 IF fnd_api.to_boolean (p_init_msg_list)
1657 THEN
1658 fnd_msg_pub.initialize;
1659 END IF;
1660
1661 -- Initialize API return status to success
1662 x_return_status := g_ret_sts_success;
1663 mo_global.init (p_appl_short_name => 'OKC');
1664
1665 OPEN l_kdetails_csr (p_chr_id);
1666
1667 FETCH l_kdetails_csr
1668 INTO l_kdetails_rec;
1669
1670 l_rownotfound := l_kdetails_csr%NOTFOUND;
1671
1672 CLOSE l_kdetails_csr;
1673
1674 IF l_rownotfound
1675 THEN
1676 fnd_message.set_name (g_app_name, 'OKS_INVD_CONTRACT_ID');
1677 fnd_message.set_token ('HDR_ID', p_chr_id);
1678 fnd_msg_pub.ADD;
1679 RAISE fnd_api.g_exc_error;
1680 END IF;
1681
1682 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1683 THEN
1684 fnd_log.STRING (fnd_log.level_event,
1685 g_module ||
1686 l_api_name ||
1687 '.external_call.before',
1688 'OKS_AUTO_REMINDER.get_qtoparty_id(p_chr_id= ' ||
1689 p_chr_id ||
1690 ')'
1691 );
1692 END IF;
1693
1694 -- Get the party ID for logging interaction history
1695 oks_auto_reminder.get_qtoparty_id (p_chr_id => p_chr_id,
1696 x_party_id => l_party_id);
1697
1698 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1699 THEN
1700 fnd_log.STRING (fnd_log.level_event,
1701 g_module ||
1702 l_api_name ||
1703 '.external_call.after',
1704 'OKS_AUTO_REMINDER.get_qtoparty_id(x_party_id= ' ||
1705 l_party_id ||
1706 ')'
1707 );
1708 END IF;
1709
1710 IF l_party_id IS NULL
1711 THEN
1712 fnd_message.set_name (g_app_name, 'OKS_NO_QTO_CONTACT');
1713 fnd_msg_pub.ADD;
1714 RAISE fnd_api.g_exc_error;
1715 END IF;
1716
1717 -- Get Salesrep user name which will be used as performer
1718 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1719 THEN
1720 fnd_log.STRING (fnd_log.level_event,
1721 g_module ||
1722 l_api_name ||
1723 '.external_call.before',
1724 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(p_chr_id=' ||
1725 p_chr_id ||
1726 ')'
1727 );
1728 END IF;
1729
1730 oks_renew_contract_pvt.get_user_name
1731 (p_api_version => l_api_version,
1732 p_init_msg_list => g_false,
1733 x_return_status => x_return_status,
1734 x_msg_count => x_msg_count,
1735 x_msg_data => x_msg_data,
1736 p_chr_id => p_chr_id,
1737 p_hdesk_user_id => NULL,
1738 x_user_id => l_salesrep_id,
1739 x_user_name => l_salesrep_name
1740 );
1741
1742 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1743 THEN
1744 fnd_log.STRING
1745 (fnd_log.level_event,
1746 g_module ||
1747 l_api_name ||
1748 '.external_call.after',
1749 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(x_return_status= ' ||
1750 x_return_status ||
1751 ' x_msg_count =' ||
1752 x_msg_count ||
1753 ')'
1754 );
1755 fnd_log.STRING (fnd_log.level_event,
1756 g_module ||
1757 l_api_name ||
1758 '.external_call.after',
1759 ' x_user_id =' ||
1760 l_salesrep_id ||
1761 ' x_user_name =' ||
1762 l_salesrep_name
1763 );
1764 END IF;
1765
1766 IF x_return_status = g_ret_sts_unexp_error
1767 THEN
1768 RAISE fnd_api.g_exc_unexpected_error;
1769 ELSIF x_return_status = g_ret_sts_error
1770 THEN
1771 RAISE fnd_api.g_exc_error;
1772 END IF;
1773
1774 OPEN l_userres_csr (l_salesrep_id);
1775
1776 FETCH l_userres_csr
1777 INTO l_salesrep_resource_id;
1778
1779 l_rownotfound := l_userres_csr%NOTFOUND;
1780
1781 CLOSE l_userres_csr;
1782
1783 IF l_rownotfound
1784 OR l_salesrep_resource_id IS NULL
1785 THEN
1786 fnd_message.set_name (g_app_name, 'OKS_INTERACTION_FAILED');
1787 fnd_msg_pub.ADD;
1788 RAISE fnd_api.g_exc_error;
1789 END IF;
1790
1791 IF p_subject IS NULL
1792 THEN
1793 fnd_message.set_name ('OKS', 'OKS_INT_HISTORY_SUBJECT');
1794 l_subject := fnd_message.get;
1795 ELSE
1796 l_subject := p_subject;
1797 END IF;
1798
1799 IF p_msg_body IS NULL
1800 THEN
1801 -- assemble interaction history body
1802 fnd_message.set_name ('OKS', 'OKS_INT_HISTORY_MSG_BODY');
1803 fnd_message.set_token ('TOKEN1', l_kdetails_rec.contract_number);
1804 fnd_message.set_token ('TOKEN2',
1805 l_kdetails_rec.contract_number_modifier);
1806
1807 IF p_sent2_email IS NULL
1808 THEN
1809 oks_auto_reminder.get_qto_email (p_chr_id => p_chr_id,
1810 x_qto_email => l_qto_email);
1811
1812 IF l_qto_email = okc_api.g_miss_char
1813 THEN
1814 fnd_message.set_name (g_app_name, 'OKS_NO_QTO_EMAIL');
1815 fnd_msg_pub.ADD;
1816 RAISE fnd_api.g_exc_error;
1817 END IF;
1818
1819 fnd_message.set_token ('TOKEN3', l_qto_email);
1820 ELSE
1821 fnd_message.set_token ('TOKEN3', p_sent2_email);
1822 END IF;
1823
1824 l_interaction_body := fnd_message.get;
1825 ELSE
1826 l_interaction_body := p_msg_body;
1827 END IF;
1828
1829 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1830 THEN
1831 fnd_log.STRING
1832 (fnd_log.level_event,
1833 g_module ||
1834 l_api_name ||
1835 '.external_call.before',
1836 'OKC_INTERACT_HISTORY_PUB.CREATE_INTERACT_HISTORY(p_chr_id=' ||
1837 p_chr_id ||
1838 'p_notes=>' ||
1839 SUBSTR (l_subject,
1840 1,
1841 240
1842 ) ||
1843 'p_notes_detail=>' ||
1844 SUBSTR (l_interaction_body,
1845 1,
1846 240
1847 ) ||
1848 'p_resource1_id=>' ||
1849 l_party_id ||
1850 'p_resource2_id=>' ||
1851 l_salesrep_resource_id ||
1852 ')'
1853 );
1854 END IF;
1855
1856 okc_interact_history_pub.create_interact_history
1857 (x_return_status => x_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data,
1860 x_interaction_id => l_interaction_id,
1861 p_media_type => p_media_type,
1862 p_action_item_id => 45,
1863 -- Email Message
1864 p_outcome_id => 41,
1865 -- Compose
1866 p_touchpoint1_type => 'PARTY',
1867 p_resource1_id => l_party_id,
1868 p_touchpoint2_type => 'EMPLOYEE',
1869 p_resource2_id => l_salesrep_resource_id,
1870 p_contract_id => p_chr_id,
1871 p_int_start_date => SYSDATE,
1872 p_int_end_date => SYSDATE,
1873 p_notes => l_subject,
1874 p_notes_detail => l_interaction_body
1875 );
1876
1877 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1878 THEN
1879 fnd_log.STRING
1880 (fnd_log.level_event,
1881 g_module ||
1882 l_api_name ||
1883 '.external_call.after',
1884 'OKC_INTERACT_HISTORY_PUB.CREATE_INTERACT_HISTORY(x_return_status= ' ||
1885 x_return_status ||
1886 ' x_msg_count =' ||
1887 x_msg_count ||
1888 'x_interaction_id =' ||
1889 l_interaction_id ||
1890 ')'
1891 );
1892 END IF;
1893
1894 IF x_return_status = g_ret_sts_unexp_error
1895 THEN
1896 RAISE fnd_api.g_exc_unexpected_error;
1897 ELSIF x_return_status = g_ret_sts_error
1898 THEN
1899 RAISE fnd_api.g_exc_error;
1900 END IF;
1901 EXCEPTION
1902 WHEN fnd_api.g_exc_error
1903 THEN
1904 fnd_msg_pub.count_and_get (p_encoded => 'F',
1905 p_count => x_msg_count,
1906 p_data => x_msg_data
1907 );
1908
1909 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1910 THEN
1911 fnd_log.STRING (fnd_log.level_procedure,
1912 g_module ||
1913 l_api_name,
1914 'Leaving ' ||
1915 g_pkg_name ||
1916 '.' ||
1917 l_api_name ||
1918 ' from G_EXC_ERROR'
1919 );
1920 fnd_log.STRING (fnd_log.level_procedure,
1921 g_module ||
1922 l_api_name,
1923 x_msg_data
1924 );
1925 END IF;
1926
1927 x_return_status := g_ret_sts_error;
1928 WHEN fnd_api.g_exc_unexpected_error
1929 THEN
1930 fnd_msg_pub.count_and_get (p_encoded => 'F',
1931 p_count => x_msg_count,
1932 p_data => x_msg_data
1933 );
1934
1935 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1936 THEN
1937 fnd_log.STRING (fnd_log.level_procedure,
1938 g_module ||
1939 l_api_name,
1940 'Leaving ' ||
1941 g_pkg_name ||
1942 '.' ||
1943 l_api_name ||
1944 ' from G_EXC_UNEXPECTED_ERROR'
1945 );
1946 fnd_log.STRING (fnd_log.level_procedure,
1947 g_module ||
1948 l_api_name,
1949 x_msg_data
1950 );
1951 END IF;
1952
1953 x_return_status := g_ret_sts_unexp_error;
1954 WHEN OTHERS
1955 THEN
1956 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1957 THEN
1958 fnd_log.STRING (fnd_log.level_procedure,
1959 g_module ||
1960 l_api_name,
1961 'Leaving ' ||
1962 g_pkg_name ||
1963 '.' ||
1964 l_api_name ||
1965 ' from OTHERS sqlcode = ' ||
1966 SQLCODE ||
1967 ', sqlerrm = ' ||
1968 SQLERRM
1969 );
1970 fnd_msg_pub.add_exc_msg (g_pkg_name,
1971 l_api_name,
1972 SUBSTR (SQLERRM,
1973 1,
1974 240
1975 )
1976 );
1977 END IF;
1978
1979 fnd_msg_pub.count_and_get (p_encoded => 'F',
1980 p_count => x_msg_count,
1981 p_data => x_msg_data
1982 );
1983 x_return_status := g_ret_sts_unexp_error;
1984 END log_interaction;
1985
1986 FUNCTION get_fnd_message RETURN VARCHAR2 IS
1987 i NUMBER := 0;
1988 l_return_status VARCHAR2(1);
1989 l_msg_count NUMBER;
1990 l_msg_data VARCHAR2(2000);
1991 l_msg_index_out NUMBER;
1992 l_mesg VARCHAR2(2000) := NULL;
1993 BEGIN
1994 FOR i in 1..fnd_msg_pub.count_msg
1995 LOOP
1996 fnd_msg_pub.get
1997 (
1998 p_msg_index => i,
1999 p_encoded => 'F',
2000 p_data => l_msg_data,
2001 p_msg_index_out => l_msg_index_out
2002 );
2003 IF l_mesg IS NULL THEN
2004 l_mesg := i || ':' || l_msg_data;
2005 ELSE
2006 l_mesg := l_mesg || ':' || i || ':' || l_msg_data;
2007 END IF;
2008 END LOOP;
2009 RETURN l_mesg;
2010 END get_fnd_message;
2011
2012 PROCEDURE validate_autoreminder_k (
2013 p_chr_id IN VARCHAR2,
2014 x_is_eligible OUT NOCOPY VARCHAR2,
2015 x_quote_id OUT NOCOPY VARCHAR2,
2016 x_cover_id OUT NOCOPY VARCHAR2,
2017 x_sender OUT NOCOPY VARCHAR2,
2018 x_qto_email OUT NOCOPY VARCHAR2,
2019 x_subject OUT NOCOPY VARCHAR2,
2020 x_status OUT NOCOPY VARCHAR2,
2021 x_attachment_name OUT NOCOPY VARCHAR2,
2022 x_return_status OUT NOCOPY VARCHAR2,
2023 x_msg_count OUT NOCOPY VARCHAR2,
2024 x_msg_data OUT NOCOPY VARCHAR2
2025 )
2026 IS
2027 l_template_set_id NUMBER;
2028 l_duration NUMBER;
2029 l_period VARCHAR2 (10);
2030 l_report_type VARCHAR2 (90);
2031 l_temp_duration NUMBER;
2032 l_count NUMBER := 0;
2033 l_wf_name VARCHAR2 (150);
2034 l_wf_process_name VARCHAR2 (150);
2035 l_package_name VARCHAR2 (150);
2036 l_procedure_name VARCHAR2 (150);
2037 l_usage VARCHAR2 (150);
2038 l_concat_k_number VARCHAR2 (250);
2039
2040 CURSOR l_k_details_csr (
2041 p_chr_id NUMBER
2042 )
2043 IS
2044 SELECT kh.ID,
2045 kh.contract_number,
2046 kh.contract_number_modifier,
2047 kh.start_date,
2048 kh.end_date,
2049 kh.sts_code
2050 FROM okc_k_headers_b kh
2051 WHERE kh.ID = p_chr_id;
2052
2053 l_k_details_rec l_k_details_csr%ROWTYPE;
2054
2055 CURSOR l_reports_csr (
2056 l_template_set VARCHAR2,
2057 l_period VARCHAR2,
2058 p_process_code VARCHAR2,
2059 p_applies_to VARCHAR2
2060 )
2061 IS
2062 SELECT message_template_id,
2063 report_duration,
2064 report_period,
2065 template_set_type,
2066 report_id,
2067 attachment_name,
2068 sts_code
2069 FROM oks_report_templates
2070 WHERE template_set_id = l_template_set
2071 AND ( DECODE (process_code,
2072 'B', 'B',
2073 'X'
2074 ) = 'B'
2075 OR process_code = p_process_code
2076 )
2077 AND ( DECODE (applies_to,
2078 'B', 'B',
2079 'X'
2080 ) = 'B'
2081 OR applies_to = p_applies_to
2082 )
2083 AND report_period <> l_period
2084 AND report_duration <> 0
2085 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2086 AND NVL (end_date, SYSDATE);
2087
2088 CURSOR l_reportmatch_csr (
2089 p_template_set VARCHAR2,
2090 p_duration NUMBER,
2091 p_period VARCHAR2,
2092 p_process_code VARCHAR2,
2093 p_applies_to VARCHAR2
2094 )
2095 IS
2096 SELECT message_template_id,
2097 template_set_type,
2098 report_id,
2099 attachment_name,
2100 sts_code
2101 FROM oks_report_templates
2102 WHERE template_set_id = p_template_set
2103 AND report_duration = p_duration
2104 AND report_period = p_period
2105 AND ( DECODE (process_code,
2106 'B', 'B',
2107 'X'
2108 ) = 'B'
2109 OR process_code = p_process_code
2110 )
2111 AND ( DECODE (applies_to,
2112 'B', 'B',
2113 'X'
2114 ) = 'B'
2115 OR applies_to = p_applies_to
2116 )
2117 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2118 AND NVL (end_date, SYSDATE);
2119
2120 l_reportmatch_rec l_reportmatch_csr%ROWTYPE;
2121
2122 CURSOR l_duration_csr (
2123 p_template_set VARCHAR2,
2124 p_duration NUMBER,
2125 p_process_code VARCHAR2,
2126 p_applies_to VARCHAR2
2127 )
2128 IS
2129 SELECT message_template_id,
2130 template_set_type,
2131 report_id,
2132 attachment_name,
2133 sts_code
2134 FROM oks_report_templates
2135 WHERE template_set_id = p_template_set
2136 AND report_duration = p_duration
2137 AND ( DECODE (process_code,
2138 'B', 'B',
2139 'X'
2140 ) = 'B'
2141 OR process_code = p_process_code
2142 )
2143 AND ( DECODE (applies_to,
2144 'B', 'B',
2145 'X'
2146 ) = 'B'
2147 OR applies_to = p_applies_to
2148 )
2149 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2150 AND NVL (end_date, SYSDATE);
2151
2152 l_duration_rec l_duration_csr%ROWTYPE;
2153
2154 CURSOR l_user_email_csr (p_user_id NUMBER) IS
2155 SELECT source_email
2156 FROM jtf_rs_resource_extns
2157 WHERE user_id = p_user_id;
2158
2159 l_api_name CONSTANT VARCHAR2 (30) := 'validate_autoreminder_k';
2160 l_item_key wf_items.item_key%TYPE := '';
2161 l_online_yn VARCHAR2 (1) := 'N';
2162 l_process_code oks_report_templates_v.process_code%TYPE;
2163 l_applies_to oks_report_templates_v.applies_to%TYPE;
2164 l_salesrep_id NUMBER;
2165 l_salesrep_name VARCHAR2 (1000);
2166
2167 CURSOR csr_k_hdr_details IS
2168 SELECT DECODE (renewal_type_used,NULL,'N','R'), wf_item_key
2169 FROM oks_k_headers_b
2170 WHERE chr_id = p_chr_id;
2171
2172 CURSOR csr_xdo_template_name(p_attachment_template_id IN NUMBER) IS
2173 SELECT template_name
2174 FROM xdo_templates_vl
2175 WHERE template_id=p_attachment_template_id;
2176
2177 BEGIN
2178
2179 -- Contract is eligibile for a reminder or cancellation notice
2180 x_is_eligible := 'Y';
2181 x_return_status := g_ret_sts_success;
2182
2183 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
2185 'Entered with p_chr_id '||p_chr_id);
2186 END IF;
2187 -- Initialize message stack
2188 FND_MSG_PUB.initialize;
2189
2190 OPEN l_k_details_csr (TO_NUMBER (p_chr_id));
2191 FETCH l_k_details_csr INTO l_k_details_rec;
2192 IF l_k_details_csr%NOTFOUND THEN
2193 CLOSE l_k_details_csr;
2194 x_is_eligible := 'N';
2195 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_INVD_CONTRACT_ID');
2196 FND_MESSAGE.SET_TOKEN('HDR_ID',p_chr_id);
2197 FND_MSG_PUB.add;
2198 ELSE
2199 CLOSE l_k_details_csr;
2200 IF l_k_details_rec.contract_number_modifier IS NULL THEN
2201 l_concat_k_number := l_k_details_rec.contract_number;
2202 ELSE
2203 l_concat_k_number := l_k_details_rec.contract_number || ' - ' ||
2204 l_k_details_rec.contract_number_modifier;
2205 END IF;
2206 END IF;
2207
2208 -- STEP 1: Check for active workflow processes
2209 IF x_is_eligible = 'Y' THEN
2210 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2211 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2212 'okc_contract_pub.get_active_process(p_contract_number= '||
2213 l_k_details_rec.contract_number||
2214 ' p_contract_number_modifier ='||l_k_details_rec.contract_number_modifier||')');
2215 END IF;
2216 okc_contract_pub.get_active_process
2217 (
2218 p_api_version => 1.0,
2219 p_init_msg_list => 'Y',
2220 x_return_status => x_return_status,
2221 x_msg_count => x_msg_count,
2222 x_msg_data => x_msg_data,
2223 p_contract_number => l_k_details_rec.contract_number,
2224 p_contract_number_modifier => l_k_details_rec.contract_number_modifier,
2225 x_wf_name => l_wf_name,
2226 x_wf_process_name => l_wf_process_name,
2227 x_package_name => l_package_name,
2228 x_procedure_name => l_procedure_name,
2229 x_usage => l_usage
2230 );
2231 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2232 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2233 'okc_contract_pub.get_active_process(x_return_status= '||x_return_status||
2234 ' x_msg_count ='||x_msg_count||')');
2235 END IF;
2236
2237 -- If error, skip this contract and proceed with the next
2238 IF x_return_status <> g_ret_sts_success OR l_wf_name IS NOT NULL THEN
2239 x_is_eligible := 'N';
2240 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_K_IN_APPROVAL_PROCESS');
2241 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2242 FND_MSG_PUB.add;
2243 END IF;
2244 END IF;
2245
2246 -- STEP 2: Get template set
2247 IF x_is_eligible = 'Y' THEN
2248 -- get the workflow key
2249 OPEN csr_k_hdr_details;
2250 FETCH csr_k_hdr_details INTO l_applies_to, l_item_key;
2251 CLOSE csr_k_hdr_details;
2252
2253 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2254 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2255 'OKS_WF_K_PROCESS_PVT.is_online_k_yn(p_contract_id= '||p_chr_id||
2256 ' p_item_key ='||l_item_key||')');
2257 END IF;
2258 -- check if the current contract is ONLINE or MANUAL
2259 OKS_WF_K_PROCESS_PVT.is_online_k_yn
2260 (
2261 p_api_version => 1.0,
2262 p_init_msg_list => FND_API.G_FALSE,
2263 p_contract_id => p_chr_id ,
2264 p_item_key => l_item_key ,
2265 x_online_yn => l_online_yn ,
2266 x_return_status => x_return_status,
2267 x_msg_count => x_msg_count,
2268 x_msg_data => x_msg_data
2269 );
2270 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2271 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2272 'OKS_WF_K_PROCESS_PVT.is_online_k_yn(x_return_status= '||x_return_status||
2273 ' x_msg_count ='||x_msg_count||'l_online_yn '||l_online_yn||')');
2274 END IF;
2275 --- If any errors happen treat it as online K
2276 IF (x_return_status <> g_ret_sts_success) THEN
2277 l_online_yn := 'Y';
2278 x_return_status := g_ret_sts_success;
2279 END IF;
2280
2281 IF l_online_yn = 'Y' THEN
2282 l_process_code := 'O';
2283 ELSE
2284 l_process_code := 'M';
2285 END IF;
2286
2287 BEGIN
2288 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2289 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2290 'oks_renew_util_pub.get_template_set_id(p_contract_id= '||
2291 l_k_details_rec.ID||')');
2292 END IF;
2293
2294 l_template_set_id := oks_renew_util_pub.get_template_set_id(l_k_details_rec.ID);
2295
2296 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2297 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2298 'oks_renew_util_pub.get_template_set_id(x_template_set_id= '||
2299 l_template_set_id||')');
2300 END IF;
2301 IF l_template_set_id IS NULL THEN
2302 x_is_eligible := 'N';
2303 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_TEMPLATE_SET');
2304 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2305 FND_MSG_PUB.add;
2306 END IF;
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 x_is_eligible := 'N';
2310 x_return_status := g_ret_sts_unexp_error;
2311 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2312 END;
2313 END IF;
2314
2315 -- STEP 3: Check if the contract qualifies for a reminder / cancelation notice
2316 IF l_template_set_id IS NOT NULL AND x_is_eligible = 'Y' THEN
2317 l_duration := TRUNC (SYSDATE) - TRUNC (l_k_details_rec.start_date);
2318 l_period := oks_time_measures_pub.get_uom_code ('DAY', 1);
2319
2320 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2321 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2322 'l_duration: ' ||l_duration||' l_period: '||l_period );
2323 END IF;
2324
2325 -- If there is an error getting the duration skip this contract
2326 IF l_duration <> 0 THEN
2327 -- Check if we have a report having the same duration, period
2328 -- in the Template set. If foudn pick the report for generating
2329 -- the email message
2330 OPEN l_reportmatch_csr (p_template_set => l_template_set_id,
2331 p_duration => l_duration,
2332 p_period => l_period,
2333 p_process_code => l_process_code,
2334 p_applies_to => l_applies_to
2335 );
2336 FETCH l_reportmatch_csr INTO l_reportmatch_rec;
2337 IF l_reportmatch_csr%FOUND THEN
2338 x_cover_id := l_reportmatch_rec.message_template_id;
2339 x_quote_id := l_reportmatch_rec.report_id;
2340 x_attachment_name := l_reportmatch_rec.attachment_name;
2341 l_report_type := l_reportmatch_rec.template_set_type;
2342 x_status := l_reportmatch_rec.sts_code;
2343
2344 CLOSE l_reportmatch_csr;
2345 ELSE -- Straight match not found
2346 CLOSE l_reportmatch_csr;
2347
2348 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2349 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2350 'Exact match not found; continue search by converting ');
2351 END IF;
2352 -- Now trying to convert and look for match
2353 FOR l_reports_rec IN l_reports_csr (l_template_set_id,
2354 l_period,
2355 l_process_code,
2356 l_applies_to
2357 )
2358 LOOP
2359 IF l_reports_rec.report_duration < 0 THEN
2360 l_temp_duration := oks_time_measures_pub.get_target_qty
2361 (
2362 p_start_date => TRUNC(SYSDATE),
2363 p_source_qty => ABS(l_reports_rec.report_duration),
2364 p_source_uom => l_reports_rec.report_period,
2365 p_target_uom => l_period,
2366 p_round_dec => 18
2367 );
2368 l_temp_duration := (l_temp_duration - 1) * -1;
2369 ELSE
2370 l_temp_duration := oks_time_measures_pub.get_target_qty
2371 (
2372 p_start_date => TRUNC(l_k_details_rec.start_date),
2373 p_source_qty => l_reports_rec.report_duration,
2374 p_source_uom => l_reports_rec.report_period,
2375 p_target_uom => l_period,
2376 p_round_dec => 18
2377 );
2378 l_temp_duration := l_temp_duration - 1;
2379 END IF;
2380
2381 IF (l_duration = l_temp_duration) THEN
2382 x_cover_id := l_reports_rec.message_template_id;
2383 x_quote_id := l_reports_rec.report_id;
2384 x_attachment_name := l_reports_rec.attachment_name;
2385 l_report_type := l_reports_rec.template_set_type;
2386 x_status := l_reports_rec.sts_code;
2387 EXIT;
2388 END IF;
2389 END LOOP; -- Inner loop for iterating thru report templates
2390 END IF; -- Report Match cursor IF
2391 -- Special handler for 0 duration
2392 ELSIF l_duration = 0 THEN
2393 OPEN l_duration_csr (l_template_set_id,
2394 l_duration,
2395 l_process_code,
2396 l_applies_to
2397 );
2398 FETCH l_duration_csr INTO l_duration_rec;
2399 IF l_duration_csr%FOUND THEN
2400 x_cover_id := l_duration_rec.message_template_id;
2401 x_quote_id := l_duration_rec.report_id;
2402 x_attachment_name := l_duration_rec.attachment_name;
2403 l_report_type := l_duration_rec.template_set_type;
2404 x_status := l_duration_rec.sts_code;
2405 END IF;
2406 CLOSE l_duration_csr;
2407 END IF; -- Get Duration Status check
2408 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2409 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2410 'x_cover_id: ' ||x_cover_id);
2411 END IF;
2412
2413 IF NVL(x_quote_id, x_cover_id) IS NULL THEN
2414 x_is_eligible := 'N';
2415 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_QUAL_NOTICE');
2416 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2417 FND_MSG_PUB.add;
2418 END IF;
2419 END IF;
2420
2421 -- STEP 4: Get Quote to contact email address
2422 IF NVL(x_quote_id, x_cover_id) IS NOT NULL AND x_is_eligible = 'Y' THEN
2423 BEGIN
2424 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2425 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2426 'oks_auto_reminder.get_qto_email(p_chr_id= '||
2427 l_k_details_rec.ID||')');
2428 END IF;
2429 oks_auto_reminder.get_qto_email
2430 (
2431 p_chr_id => l_k_details_rec.ID,
2432 x_qto_email => x_qto_email
2433 );
2434 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2435 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2436 'oks_auto_reminder.get_qto_email(x_qto_email= '||
2437 x_qto_email||')');
2438 END IF;
2439 IF x_qto_email IS NULL OR x_qto_email = okc_api.g_miss_char THEN
2440 x_is_eligible := 'N';
2441 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_QTO_EMAIL');
2442 FND_MSG_PUB.add;
2443 END IF;
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 x_is_eligible := 'N';
2447 x_return_status := g_ret_sts_unexp_error;
2448 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2449 END;
2450 END IF;
2451
2452 -- STEP 5: Get Sender email address
2453 IF x_is_eligible = 'Y' THEN
2454 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
2455 fnd_log.STRING(fnd_log.level_event,g_module ||l_api_name ||
2456 '.external_call.before',
2457 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(p_chr_id=' ||
2458 l_k_details_rec.ID ||')');
2459 END IF;
2460 oks_renew_contract_pvt.get_user_name
2461 (
2462 p_api_version => 1.0,
2463 p_init_msg_list => g_false,
2464 x_return_status => x_return_status,
2465 x_msg_count => x_msg_count,
2466 x_msg_data => x_msg_data,
2467 p_chr_id => l_k_details_rec.ID,
2468 p_hdesk_user_id => NULL,
2469 x_user_id => l_salesrep_id,
2470 x_user_name => l_salesrep_name
2471 );
2472 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
2473 fnd_log.STRING(fnd_log.level_event,g_module ||l_api_name ||
2474 '.external_call.after',
2475 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(x_return_status= ' ||
2476 x_return_status ||' x_msg_count ='||x_msg_count||')');
2477 fnd_log.STRING (fnd_log.level_event,g_module ||l_api_name ||
2478 '.external_call.after',' x_user_id =' ||l_salesrep_id ||
2479 ' x_user_name =' ||l_salesrep_name);
2480 END IF;
2481
2482 IF x_return_status = g_ret_sts_success AND
2483 l_salesrep_id IS NOT NULL THEN
2484 OPEN l_user_email_csr (l_salesrep_id);
2485 FETCH l_user_email_csr INTO x_sender;
2486 CLOSE l_user_email_csr;
2487 END IF;
2488 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2489 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2490 'x_sender: ' ||x_sender);
2491 END IF;
2492 IF x_sender IS NULL THEN
2493 x_is_eligible := 'N';
2494 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_EMAIL_NOT_FOUND');
2495 FND_MSG_PUB.add;
2496 END IF;
2497 END IF;
2498
2499 -- STEP 6: Get email subject
2500 IF x_sender IS NOT NULL AND x_is_eligible = 'Y' THEN
2501 IF l_report_type = 'RMN' THEN
2502 fnd_message.set_name ('OKS','OKS_AREM_RNT_SUBJECT');
2503 ELSE
2504 fnd_message.set_name ('OKS','OKS_AREM_CNT_SUBJECT');
2505 END IF;
2506 fnd_message.set_token('TOKEN1',l_concat_k_number);
2507 x_subject := fnd_message.get;
2508 END IF;
2509
2510 -- if attachment name is NULL then get template name from xdo_templates_vl
2511 IF x_is_eligible = 'Y' AND x_quote_id IS NOT NULL AND
2512 x_attachment_name IS NULL THEN
2513 OPEN csr_xdo_template_name(p_attachment_template_id => x_quote_id);
2514 FETCH csr_xdo_template_name INTO x_attachment_name;
2515 CLOSE csr_xdo_template_name;
2516 END IF;
2517 IF x_is_eligible = 'N' THEN
2518 x_msg_data := get_fnd_message;
2519 END IF;
2520
2521 EXCEPTION
2522 WHEN FND_API.G_EXC_ERROR THEN
2523 x_is_eligible := 'N';
2524 x_msg_data := get_fnd_message;
2525 WHEN OTHERS THEN
2526 x_is_eligible := 'N';
2527 x_msg_data := 'Exception: ' || SQLERRM;
2528 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2529 END validate_autoreminder_k;
2530
2531 /*
2532
2533 PROCEDURE create_sso_user
2534 Pseudo Logic:
2535 -------------
2536
2537 Step 1: Get the person_party_id from oks_k_headers_b
2538
2539 IF oks_k_headers_b.person_party_id IS NULL THEN
2540 Fetch the person_party_id for quote to contact id
2541 UPDATE oks_k_headers_b record with the person_party_id for quote to contact id
2542
2543 Step 2: Check if record exists in fnd_user for the above person_party_id (filter expired records here)
2544 -- 1 or more Record Exists
2545 If record found then
2546 take the first hit record and put the user name and password in the email notification
2547 check if this user has the responsibility' Electronic Renewal'
2548 If yes then return
2549 else
2550 add responsibility and return
2551 end if;
2552
2553 Step 3: If no record found in step 2 above then check in fnd_user if record exists with the user name as quote to contact email
2554
2555 CASE A: Record NOT Found in fnd_user
2556
2557 Call FND_USER_PKG.TestUserName
2558 --@ TestUserName() returns:
2559 --@ USER_OK_CREATE constant pls_integer := 0;
2560 --@ USER_INVALID_NAME constant pls_integer := 1;
2561 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
2562 --@ USER_SYNCHED constant pls_integer := 3;
2563 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
2564
2565 IF l_test_user IN (0,3) THEN
2566 CALL FND_USER_PKG.CreateUserIdParty
2567 FND_USER_RESP_GROUPS_API.insert_assignment with responsibility as 'OKS_ERN_WEB'
2568
2569 ELSE -- l_test_user <> 0 ,3
2570 -- error, raise exception
2571 RAISE FND_API.G_EXC_ERROR;
2572 END IF;
2573
2574
2575 CASE B: Record Found in fnd_user
2576
2577 Check if fnd_user is Valid or expired
2578 Case Valid
2579
2580 Check person_party_id of fnd_user
2581 CASE 1: fnd_user person_party_id is NULL
2582 UPDATE fnd_user record with person_party_id from step 1 above
2583 CASE 2: person_party_id of fnd_user DOES NOT MATCH the person_party_id from step 1 above
2584 RAISE error here
2585 CASE 3: person_party_id of fnd_user MATCHES the person_party_id from step 1 above
2586 we are fine, do nothing
2587
2588 Case Expired
2589
2590 fnd_user exists and is expired , raise exception
2591
2592
2593 */
2594 PROCEDURE create_sso_user (
2595 p_user_name IN VARCHAR2,
2596 p_contract_id IN NUMBER,
2597 x_user_name OUT NOCOPY VARCHAR2,
2598 x_password OUT NOCOPY VARCHAR2,
2599 x_return_status OUT NOCOPY VARCHAR2,
2600 x_msg_data OUT NOCOPY VARCHAR2,
2601 x_msg_count OUT NOCOPY NUMBER
2602 )
2603 AS
2604 l_api_name CONSTANT VARCHAR2 (30)
2605 := 'create_sso_user';
2606 l_person_party_id oks_k_headers_b.person_party_id%TYPE;
2607 l_fnd_person_party_id fnd_user.person_party_id%TYPE;
2608 l_start_date fnd_user.start_date%TYPE;
2609 l_end_date fnd_user.end_date%TYPE;
2610 l_test_user PLS_INTEGER;
2611 l_responsibility_id fnd_responsibility_vl.responsibility_id%TYPE;
2612 l_security_grp_id fnd_security_groups.security_group_id%TYPE;
2613 l_return_value BOOLEAN;
2614 l_user_id fnd_user.user_id%TYPE;
2615 l_quote_to_contact_id oks_k_headers_b.quote_to_contact_id%TYPE;
2616
2617 l_suggested_user_name fnd_user.user_name%TYPE;
2618
2619 CURSOR csr_person_party_id
2620 IS
2621 SELECT ks.person_party_id,
2622 ks.quote_to_contact_id
2623 FROM oks_k_headers_b ks
2624 WHERE ks.chr_id = p_contract_id;
2625
2626 CURSOR csr_qtc_person_party_id (
2627 p_quote_to_contact_id IN NUMBER
2628 )
2629 IS
2630 SELECT hzp.party_id
2631 FROM hz_cust_account_roles car,
2632 hz_relationships rln,
2633 hz_parties hzp
2634 WHERE car.cust_account_role_id = p_quote_to_contact_id
2635 AND car.party_id = rln.party_id
2636 AND rln.subject_id = hzp.party_id
2637 AND car.role_type = 'CONTACT'
2638 AND rln.directional_flag = 'F'
2639 AND rln.content_source_type = 'USER_ENTERED';
2640
2641 CURSOR csr_check_fnd_user_exists (
2642 p_person_party_id IN NUMBER
2643 )
2644 IS
2645 SELECT user_id,
2646 user_name,
2647 encrypted_user_password
2648 FROM fnd_user
2649 WHERE SYSDATE BETWEEN start_date AND NVL (end_date, SYSDATE +
2650 1)
2651 AND person_party_id = p_person_party_id;
2652
2653 CURSOR csr_chk_qtc_fnd_user (
2654 p_user_name IN VARCHAR2
2655 )
2656 IS
2657 SELECT f.person_party_id,
2658 f.start_date,
2659 f.end_date,
2660 f.encrypted_user_password
2661 FROM fnd_user f
2662 WHERE f.user_name = p_user_name;
2663
2664 CURSOR l_resp_csr (
2665 p_resp_key VARCHAR2
2666 )
2667 IS
2668 SELECT responsibility_id
2669 FROM fnd_responsibility
2670 WHERE responsibility_key = p_resp_key
2671 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2672 AND NVL (end_date, SYSDATE);
2673
2674 CURSOR l_security_grp_csr (
2675 p_security_grp_key VARCHAR2
2676 )
2677 IS
2678 SELECT security_group_id
2679 FROM fnd_security_groups
2680 WHERE security_group_key = p_security_grp_key;
2681
2682 CURSOR csr_get_per_party_name (p_party_id IN NUMBER) IS
2683 SELECT party_name
2684 FROM hz_parties
2685 WHERE party_id = p_party_id;
2686
2687 l_oks_per_party_name hz_parties.party_name%TYPE;
2688 l_fnd_per_party_name hz_parties.party_name%TYPE;
2689
2690 CURSOR csr_get_party_name (p_party_id IN NUMBER) IS
2691 SELECT p.party_name , p.party_id
2692 FROM hz_relationships r , hz_parties p
2693 WHERE p.party_id = r.object_id
2694 AND r.subject_type='PERSON'
2695 AND r.object_type='ORGANIZATION'
2696 AND r.subject_id = p_party_id;
2697
2698 l_oks_party_name hz_parties.party_name%TYPE;
2699 l_fnd_party_name hz_parties.party_name%TYPE;
2700 l_oks_party_id hz_parties.party_id%TYPE;
2701 l_fnd_party_id hz_parties.party_id%TYPE;
2702
2703 BEGIN
2704 -- start debug log
2705 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2706 THEN
2707 fnd_log.STRING (fnd_log.level_procedure,
2708 g_module ||
2709 l_api_name,
2710 '100: Entered ' ||
2711 g_pkg_name ||
2712 '.' ||
2713 l_api_name
2714 );
2715 fnd_log.STRING (fnd_log.level_procedure,
2716 g_module ||
2717 l_api_name,
2718 '100: Parameter p_user_name : ' ||
2719 p_user_name
2720 );
2721 fnd_log.STRING (fnd_log.level_procedure,
2722 g_module ||
2723 l_api_name,
2724 '100: p_contract_id : ' ||
2725 p_contract_id
2726 );
2727 END IF;
2728
2729 -- log file
2730 fnd_file.put_line (fnd_file.LOG, ' ');
2731 fnd_file.put_line
2732 (fnd_file.LOG,
2733 '---------------------------------------------------------- ');
2734 fnd_file.put_line (fnd_file.LOG, 'Entered create_sso_user');
2735 fnd_file.put_line (fnd_file.LOG, 'Parameters ');
2736 fnd_file.put_line (fnd_file.LOG, 'p_user_name : ' ||
2737 p_user_name);
2738 fnd_file.put_line (fnd_file.LOG, 'p_contract_id : ' ||
2739 p_contract_id);
2740 fnd_file.put_line
2741 (fnd_file.LOG,
2742 '---------------------------------------------------------- ');
2743 fnd_file.put_line (fnd_file.LOG, ' ');
2744 -- Initialize API return status to success
2745 x_return_status := fnd_api.g_ret_sts_success;
2746
2747 -- Get the Person Party Id and quote_to_contact_id from oks_k_headers_b
2748 OPEN csr_person_party_id;
2749
2750 FETCH csr_person_party_id
2751 INTO l_person_party_id,
2752 l_quote_to_contact_id;
2753
2754 CLOSE csr_person_party_id;
2755
2756 -- debug log
2757 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2758 THEN
2759 fnd_log.STRING (fnd_log.level_procedure,
2760 g_module ||
2761 l_api_name,
2762 '110: l_person_party_id : ' ||
2763 l_person_party_id
2764 );
2765 fnd_log.STRING (fnd_log.level_procedure,
2766 g_module ||
2767 l_api_name,
2768 '110: l_quote_to_contact_id : ' ||
2769 l_quote_to_contact_id
2770 );
2771 END IF;
2772
2773 -- log file
2774 fnd_file.put_line (fnd_file.LOG,
2775 'OKS l_person_party_id : ' ||
2776 l_person_party_id);
2777 fnd_file.put_line (fnd_file.LOG,
2778 'OKS l_quote_to_contact_id : ' ||
2779 l_quote_to_contact_id);
2780
2781 -- if the person_party_id is NULL then get person_party_id for quote_to_contact_id
2782 IF l_person_party_id IS NULL
2783 THEN
2784 OPEN csr_qtc_person_party_id
2785 (p_quote_to_contact_id => l_quote_to_contact_id);
2786
2787 FETCH csr_qtc_person_party_id
2788 INTO l_person_party_id;
2789
2790 CLOSE csr_qtc_person_party_id;
2791
2792 -- debug log
2793 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2794 THEN
2795 fnd_log.STRING
2796 (fnd_log.level_procedure,
2797 g_module ||
2798 l_api_name,
2799 '120: l_person_party_id for quote to contact: ' ||
2800 l_person_party_id
2801 );
2802 END IF;
2803
2804 -- log file
2805 fnd_file.put_line (fnd_file.LOG,
2806 'HZ l_person_party_id : ' ||
2807 l_person_party_id);
2808
2809 -- update the oks_k_headers_b with the person_party_id
2810 UPDATE oks_k_headers_b
2811 SET person_party_id = l_person_party_id,
2812 object_version_number = object_version_number +
2813 1,
2814 last_update_date = SYSDATE,
2815 last_updated_by = fnd_global.user_id,
2816 last_update_login = fnd_global.login_id
2817 WHERE chr_id = p_contract_id;
2818
2819 -- bump up the minor version number
2820 UPDATE okc_k_vers_numbers
2821 SET minor_version = minor_version +
2822 1,
2823 object_version_number = object_version_number +
2824 1,
2825 last_update_date = SYSDATE,
2826 last_updated_by = fnd_global.user_id,
2827 last_update_login = fnd_global.login_id
2828 WHERE chr_id = p_contract_id;
2829 END IF; -- l_person_party_id is null in oks_k_headers_b
2830
2831 -- Check if record exists in fnd_user for the above person_party_id
2832 OPEN csr_check_fnd_user_exists (p_person_party_id => l_person_party_id);
2833
2834 FETCH csr_check_fnd_user_exists
2835 INTO l_user_id,
2836 x_user_name,
2837 x_password;
2838
2839 CLOSE csr_check_fnd_user_exists;
2840
2841 -- debug log
2842 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2843 THEN
2844 fnd_log.STRING (fnd_log.level_procedure,
2845 g_module ||
2846 l_api_name,
2847 '130: l_user_id : ' ||
2848 l_user_id
2849 );
2850 fnd_log.STRING (fnd_log.level_procedure,
2851 g_module ||
2852 l_api_name,
2853 '130: x_user_name : ' ||
2854 x_user_name
2855 );
2856 fnd_log.STRING (fnd_log.level_procedure,
2857 g_module ||
2858 l_api_name,
2859 '130: x_password : ' ||
2860 x_password
2861 );
2862 END IF;
2863
2864 -- log file
2865 fnd_file.put_line (fnd_file.LOG,
2866 'Check FND User record for person_party_id : ' ||
2867 l_person_party_id);
2868 fnd_file.put_line (fnd_file.LOG, 'l_user_id : ' ||
2869 l_user_id);
2870 fnd_file.put_line (fnd_file.LOG, 'x_user_name : ' ||
2871 x_user_name);
2872
2873 -- Check if valid fnd_user found
2874 IF x_user_name IS NOT NULL
2875 THEN
2876 x_password := '******';
2877 -- log file
2878 fnd_file.put_line (fnd_file.LOG,
2879 'Found FND User record for person_party_id : ' ||
2880 l_person_party_id);
2881 -- Bug 4650639
2882 -- FND USER found, check if this user has the responsibility G_ERN_WEB_RESPONSIBILITY
2883 fnd_file.put_line (fnd_file.LOG,
2884 'Check if the user has ERN responsibility');
2885
2886 OPEN l_resp_csr (p_resp_key => g_ern_web_responsibility);
2887
2888 FETCH l_resp_csr
2889 INTO l_responsibility_id;
2890
2891 CLOSE l_resp_csr;
2892
2893 OPEN l_security_grp_csr ('STANDARD');
2894
2895 FETCH l_security_grp_csr
2896 INTO l_security_grp_id;
2897
2898 CLOSE l_security_grp_csr;
2899
2900 IF fnd_user_resp_groups_api.assignment_exists (l_user_id,
2901 l_responsibility_id,
2902 515,
2903 l_security_grp_id
2904 )
2905 THEN
2906 -- user has the ERN responsibility
2907 fnd_file.put_line (fnd_file.LOG,
2908 'user : ' ||
2909 x_user_name ||
2910 ' has ERN responsibility');
2911 RETURN;
2912 ELSE
2913 -- add responsibility and return
2914 -- log file
2915 fnd_file.put_line (fnd_file.LOG,
2916 'user : ' ||
2917 x_user_name ||
2918 ' DOES NOT HAVE ERN responsibility');
2919 fnd_file.put_line (fnd_file.LOG,
2920 'assign responsibility to user created');
2921 fnd_file.put_line (fnd_file.LOG,
2922 'l_responsibility_id : ' ||
2923 l_responsibility_id);
2924 fnd_file.put_line (fnd_file.LOG,
2925 'l_security_grp_id : ' ||
2926 l_security_grp_id);
2927 fnd_user_resp_groups_api.insert_assignment
2928 (user_id => l_user_id,
2929 responsibility_id => l_responsibility_id,
2930 responsibility_application_id => 515,
2931 security_group_id => l_security_grp_id,
2932 description => 'Electronic renewals User',
2933 start_date => SYSDATE,
2934 end_date => NULL
2935 );
2936 l_return_value :=
2937 fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
2938 x_value => 'PHP',
2939 x_level_name => 'USER',
2940 x_level_value => TO_CHAR
2941 (l_user_id)
2942 );
2943
2944 IF l_return_value
2945 THEN
2946 RETURN;
2947 ELSE
2948 -- error in fnd_profile.save
2949 -- debug log
2950 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2951 )
2952 THEN
2953 fnd_log.STRING (fnd_log.level_procedure,
2954 g_module ||
2955 l_api_name,
2956 '135: error in fnd_profile.save : '
2957 );
2958 END IF;
2959
2960 -- log file
2961 fnd_file.put_line (fnd_file.LOG, 'error in fnd_profile.save ');
2962 fnd_file.put_line (fnd_file.LOG, SQLERRM);
2963 RAISE fnd_api.g_exc_error;
2964 END IF; -- l_return_value
2965 END IF; -- assignment_exists
2966 END IF; -- x_user_name IS NOT NULL THEN
2967
2968 -- check in fnd_user if record exists with the user name as quote to contact email
2969 OPEN csr_chk_qtc_fnd_user (p_user_name => UPPER
2970 (TRIM
2971 (p_user_name)));
2972
2973 FETCH csr_chk_qtc_fnd_user
2974 INTO l_fnd_person_party_id,
2975 l_start_date,
2976 l_end_date,
2977 x_password;
2978
2979 -- debug log
2980 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2981 THEN
2982 fnd_log.STRING (fnd_log.level_procedure,
2983 g_module ||
2984 l_api_name,
2985 '140: l_fnd_person_party_id : ' ||
2986 l_fnd_person_party_id
2987 );
2988 fnd_log.STRING (fnd_log.level_procedure,
2989 g_module ||
2990 l_api_name,
2991 '140: l_start_date : ' ||
2992 l_start_date
2993 );
2994 fnd_log.STRING (fnd_log.level_procedure,
2995 g_module ||
2996 l_api_name,
2997 '140: l_end_date : ' ||
2998 l_end_date
2999 );
3000 fnd_log.STRING (fnd_log.level_procedure,
3001 g_module ||
3002 l_api_name,
3003 '140: x_password : ' ||
3004 x_password
3005 );
3006 END IF;
3007
3008 -- log file
3009 fnd_file.put_line (fnd_file.LOG,
3010 'Check FND User record for user name : ' ||
3011 p_user_name);
3012 fnd_file.put_line (fnd_file.LOG,
3013 'l_fnd_person_party_id : ' ||
3014 l_fnd_person_party_id);
3015 fnd_file.put_line (fnd_file.LOG, 'l_start_date : ' ||
3016 l_start_date);
3017 fnd_file.put_line (fnd_file.LOG, 'l_end_date : ' ||
3018 l_end_date);
3019
3020 IF csr_chk_qtc_fnd_user%NOTFOUND
3021 THEN
3022 -- create a NEW FND USER
3023 -- Call the testUserName pkg
3024 --@ TestUserName() returns:
3025 --@ USER_OK_CREATE constant pls_integer := 0;
3026 --@ USER_INVALID_NAME constant pls_integer := 1;
3027 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
3028 --@ USER_SYNCHED constant pls_integer := 3;
3029 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
3030
3031 -- log file
3032 fnd_file.put_line
3033 (fnd_file.LOG,
3034 'Getting Suggested username, calling UMX_PUB.get_suggested_username');
3035
3036 -- Call API UMX_PUB.get_suggested_username to get the suggested user name
3037 UMX_PUB.get_suggested_username(p_person_party_id => l_person_party_id,
3038 x_suggested_username => l_suggested_user_name);
3039
3040 -- log file
3041 fnd_file.put_line
3042 (fnd_file.LOG,
3043 'Suggested username : '||l_suggested_user_name);
3044
3045 -- debug log
3046 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3047 THEN
3048 fnd_log.STRING (fnd_log.level_procedure,
3049 g_module ||
3050 l_api_name,
3051 '145: l_suggested_user_name from UMX_PUB.get_suggested_username : ' ||
3052 l_suggested_user_name
3053 );
3054 END IF;
3055
3056 IF l_suggested_user_name IS NULL THEN
3057 -- suggested name same as email address
3058 l_suggested_user_name := UPPER(TRIM(p_user_name));
3059 END IF;
3060
3061 -- debug log
3062 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3063 THEN
3064 fnd_log.STRING (fnd_log.level_procedure,
3065 g_module ||
3066 l_api_name,
3067 '146: l_suggested_user_name : ' ||
3068 l_suggested_user_name
3069 );
3070 END IF;
3071
3072 -- log file
3073 fnd_file.put_line
3074 (fnd_file.LOG,
3075 'Creating New user, calling FND_USER_PKG.TestUserName for username :'||l_suggested_user_name);
3076 l_test_user :=
3077 fnd_user_pkg.testusername
3078 (x_user_name => l_suggested_user_name);
3079
3080 -- debug log
3081 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3082 THEN
3083 fnd_log.STRING (fnd_log.level_procedure,
3084 g_module ||
3085 l_api_name,
3086 '150: l_test_user : ' ||
3087 l_test_user
3088 );
3089 END IF;
3090
3091 -- log file
3092 fnd_file.put_line
3093 (fnd_file.LOG,
3094 'After calling FND_USER_PKG.TestUserName l_test_user : ' ||
3095 l_test_user);
3096
3097 IF l_test_user IN (0, 3)
3098 THEN
3099 IF l_test_user = 0
3100 THEN
3101 -- ok to create a new user
3102 /*
3103 x_password := dbms_random.string('l', (NVL(FND_PROFILE.value('SIGNON_PASSWORD_LENGTH'),7)-3))||
3104 round(dbms_random.value(100,999));
3105 */
3106
3107 -- log file
3108 fnd_file.put_line
3109 (fnd_file.LOG,
3110 'ok to create a new user, calling FND_CRYPTO.randombytes');
3111 x_password :=
3112 fnd_crypto.randomstring
3113 (NVL (fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
3114 4));
3115 l_user_id :=
3116 fnd_user_pkg.createuseridparty
3117 (x_user_name => l_suggested_user_name,
3118 x_owner => 'SEED',
3119 x_unencrypted_password => x_password,
3120 x_description => 'Electronic renewals User',
3121 x_email_address => UPPER
3122 (TRIM
3123 (p_user_name)),
3124 x_person_party_id => l_person_party_id
3125 );
3126 x_user_name := l_suggested_user_name;
3127 -- log file
3128 fnd_file.put_line
3129 (fnd_file.LOG,
3130 'FND_USER_PKG.CreateUserIdParty l_user_id : ' ||
3131 l_user_id);
3132 ELSE -- l_test_user = 3
3133 -- USER_SYNCHED constant pls_integer := 3;
3134 -- Call the FND_USER_PKG.CreateUserIdParty WITHOUT password as password exists in OID
3135 -- in Notification, put the password as '******'
3136
3137 -- log file
3138 fnd_file.put_line
3139 (fnd_file.LOG,
3140 'USER_SYNCHED , calling FND_USER_PKG.CreateUserIdParty');
3141 l_user_id :=
3142 fnd_user_pkg.createuseridparty
3143 (x_user_name => l_suggested_user_name,
3144 x_owner => 'SEED',
3145 x_description => 'Electronic renewals User',
3146 x_email_address => UPPER
3147 (TRIM
3148 (p_user_name)),
3149 x_person_party_id => l_person_party_id
3150 );
3151 x_user_name := l_suggested_user_name;
3152 x_password := '******';
3153 -- log file
3154 fnd_file.put_line
3155 (fnd_file.LOG,
3156 'FND_USER_PKG.CreateUserIdParty l_user_id : ' ||
3157 l_user_id);
3158 END IF; -- l_test_user = 0 or 3
3159
3160 -- debug log
3161 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3162 THEN
3163 fnd_log.STRING (fnd_log.level_procedure,
3164 g_module ||
3165 l_api_name,
3166 '160: l_user_id : ' ||
3167 l_user_id
3168 );
3169 fnd_log.STRING (fnd_log.level_procedure,
3170 g_module ||
3171 l_api_name,
3172 '160: x_user_name : ' ||
3173 x_user_name
3174 );
3175 fnd_log.STRING (fnd_log.level_procedure,
3176 g_module ||
3177 l_api_name,
3178 '160: x_password : ' ||
3179 x_password
3180 );
3181 END IF;
3182
3183 -- log file
3184 fnd_file.put_line (fnd_file.LOG, 'l_user_id : ' ||
3185 l_user_id);
3186 fnd_file.put_line (fnd_file.LOG, 'x_user_name : ' ||
3187 x_user_name);
3188
3189 IF l_user_id IS NOT NULL
3190 THEN
3191 -- assign responsibility to user created
3192 OPEN l_resp_csr (p_resp_key => g_ern_web_responsibility);
3193
3194 FETCH l_resp_csr
3195 INTO l_responsibility_id;
3196
3197 CLOSE l_resp_csr;
3198
3199 OPEN l_security_grp_csr ('STANDARD');
3200
3201 FETCH l_security_grp_csr
3202 INTO l_security_grp_id;
3203
3204 CLOSE l_security_grp_csr;
3205
3206 -- debug log
3207 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3208 )
3209 THEN
3210 fnd_log.STRING (fnd_log.level_procedure,
3211 g_module ||
3212 l_api_name,
3213 '170: l_responsibility_id : ' ||
3214 l_responsibility_id
3215 );
3216 fnd_log.STRING (fnd_log.level_procedure,
3217 g_module ||
3218 l_api_name,
3219 '170: l_security_grp_id : ' ||
3220 l_security_grp_id
3221 );
3222 END IF;
3223
3224 -- log file
3225 fnd_file.put_line (fnd_file.LOG,
3226 'assign responsibility to user created');
3227 fnd_file.put_line (fnd_file.LOG,
3228 'l_responsibility_id : ' ||
3229 l_responsibility_id);
3230 fnd_file.put_line (fnd_file.LOG,
3231 'l_security_grp_id : ' ||
3232 l_security_grp_id);
3233 fnd_user_resp_groups_api.insert_assignment
3234 (user_id => l_user_id,
3235 responsibility_id => l_responsibility_id,
3236 responsibility_application_id => 515,
3237 security_group_id => l_security_grp_id,
3238 description => 'Electronic renewals User',
3239 start_date => SYSDATE,
3240 end_date => NULL
3241 );
3242 l_return_value :=
3243 fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
3244 x_value => 'FWK',
3245 x_level_name => 'USER',
3246 x_level_value => TO_CHAR
3247 (l_user_id)
3248 );
3249
3250 IF l_return_value
3251 THEN
3252 RETURN;
3253 ELSE
3254 -- error in fnd_profile.save
3255 -- debug log
3256 IF (fnd_log.level_procedure >=
3257 fnd_log.g_current_runtime_level
3258 )
3259 THEN
3260 fnd_log.STRING (fnd_log.level_procedure,
3261 g_module ||
3262 l_api_name,
3263 '180: error in fnd_profile.save : '
3264 );
3265 END IF;
3266
3267 -- log file
3268 fnd_file.put_line (fnd_file.LOG,
3269 'error in fnd_profile.save ');
3270 fnd_file.put_line (fnd_file.LOG, SQLERRM);
3271 RAISE fnd_api.g_exc_error;
3272 END IF;
3273 ELSE
3274 -- l_user_id is null, raise exception
3275 -- debug log
3276 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3277 )
3278 THEN
3279 fnd_log.STRING (fnd_log.level_procedure,
3280 g_module ||
3281 l_api_name,
3282 '190: l_user_id is null, raise exception '
3283 );
3284 END IF;
3285
3286 -- log file
3287 fnd_file.put_line (fnd_file.LOG,
3288 'l_user_id is null, raise exception ');
3289 fnd_file.put_line (fnd_file.LOG, SQLERRM);
3290 RAISE fnd_api.g_exc_error;
3291 END IF; -- l_user_id is not null
3292 ELSE -- l_test_user <> 0 ,3
3293 -- error, raise exception
3294 fnd_message.set_name ('OKS', 'OKS_SSO_TEST_USER_ERROR');
3295 fnd_message.set_token ('RETURN_VAL', l_test_user);
3296 fnd_msg_pub.ADD;
3297 -- log file
3298 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_TEST_USER_ERROR');
3299 fnd_file.put_line (fnd_file.LOG, 'l_test_user: ' ||
3300 l_test_user);
3301 RAISE fnd_api.g_exc_error;
3302 END IF; -- l_test_user check
3303 ELSE -- csr_chk_qtc_fnd_user%FOUND
3304 -- check if the above fnd_user is valid
3305 IF SYSDATE BETWEEN l_start_date AND NVL (l_end_date, SYSDATE +
3306 1)
3307 THEN
3308 -- fnd user is valid
3309
3310 -- log file
3311 fnd_file.put_line
3312 (fnd_file.LOG,
3313 'chk_qtc_fnd_user%FOUND, check if the above fnd_user is valid');
3314 fnd_file.put_line (fnd_file.LOG,
3315 'l_fnd_person_party_id : ' ||
3316 l_fnd_person_party_id);
3317 fnd_file.put_line (fnd_file.LOG,
3318 'l_person_party_id : ' ||
3319 l_person_party_id);
3320
3321 -- Check the person_party_id of the fnd_user
3322 IF l_fnd_person_party_id IS NULL
3323 THEN
3324 -- fnd_user.person_party_id IS NULL
3325 UPDATE fnd_user
3326 SET person_party_id = l_person_party_id
3327 WHERE user_name = UPPER (TRIM (p_user_name));
3328 ELSIF l_person_party_id <> l_fnd_person_party_id
3329 THEN
3330 -- fnd_user.person_party_id does NOT match oks_person_party_id
3331 -- get the party names from hz_parties and raise error
3332 -- oks person party name
3333 OPEN csr_get_per_party_name (p_party_id => l_person_party_id);
3334 FETCH csr_get_per_party_name INTO l_oks_per_party_name;
3335 CLOSE csr_get_per_party_name;
3336 -- log file
3337 fnd_file.put_line (fnd_file.LOG,'OKS Person Party Name: '||l_oks_per_party_name);
3338
3339 -- fnd person party name
3340 OPEN csr_get_per_party_name (p_party_id => l_fnd_person_party_id);
3341 FETCH csr_get_per_party_name INTO l_fnd_per_party_name;
3342 CLOSE csr_get_per_party_name;
3343 -- log file
3344 fnd_file.put_line (fnd_file.LOG,'FND Person Party Name: '||l_fnd_per_party_name);
3345
3346 -- get the party name (organization name) for oks person
3347 OPEN csr_get_party_name (p_party_id => l_person_party_id);
3348 FETCH csr_get_party_name INTO l_oks_party_name, l_oks_party_id;
3349 CLOSE csr_get_party_name;
3350 -- log file
3351 fnd_file.put_line (fnd_file.LOG,'OKS Party Name: '||l_oks_party_name);
3352 fnd_file.put_line (fnd_file.LOG,'OKS Party ID: '||l_oks_party_id);
3353
3354 -- get the party name (organization name) for FND person
3355 OPEN csr_get_party_name (p_party_id => l_fnd_person_party_id);
3356 FETCH csr_get_party_name INTO l_fnd_party_name, l_fnd_party_id;
3357 CLOSE csr_get_party_name;
3358 -- log file
3359 fnd_file.put_line (fnd_file.LOG,'FND Party Name: '||l_fnd_party_name);
3360 fnd_file.put_line (fnd_file.LOG,'FND Party ID: '||l_fnd_party_id);
3361
3362 -- set error message
3363 fnd_message.set_name ('OKS', 'OKS_SSO_PERSON_PARTY_ERROR');
3364 fnd_message.set_token ('OKS_USER_NAME', p_user_name);
3365 fnd_message.set_token ('OKS_PARTY_ID', l_oks_party_id);
3366 fnd_message.set_token ('OKS_PARTY_NAME', l_oks_party_name);
3367 fnd_message.set_token ('FND_PARTY_ID', l_fnd_party_id);
3368 fnd_message.set_token ('FND_PARTY_NAME', l_fnd_party_name);
3369 fnd_message.set_token ('OKS_PERSON_PARTY_ID', l_person_party_id);
3370 fnd_message.set_token ('FND_PERSON_PARTY_ID', l_fnd_person_party_id);
3371 fnd_message.set_token ('OKS_PERSON_PARTY_NAME', l_oks_per_party_name); -- bug 6338286
3372 fnd_message.set_token ('FND_PERSON_PARTY_NAME', l_fnd_per_party_name); -- bug 6338286
3373 fnd_msg_pub.ADD;
3374
3375 -- log file
3376 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_PERSON_PARTY_ERROR');
3377 RAISE fnd_api.g_exc_error;
3378 END IF; -- person_party_id check
3379
3380 x_user_name := UPPER (TRIM (p_user_name));
3381 x_password := '******'; -- bug 5357772
3382
3383 CLOSE csr_chk_qtc_fnd_user;
3384
3385 RETURN;
3386 ELSE
3387 -- fnd user has expired RAISE exception;
3388 fnd_message.set_name ('OKS', 'OKS_SSO_USER_EXPIRED');
3389 fnd_message.set_token ('USER_NAME', UPPER (TRIM (p_user_name)));
3390 fnd_msg_pub.ADD;
3391 -- log file
3392 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_USER_EXPIRED ');
3393 RAISE fnd_api.g_exc_error;
3394 END IF;
3395 END IF;
3396
3397 CLOSE csr_chk_qtc_fnd_user;
3398
3399 -- log file
3400 fnd_file.put_line (fnd_file.LOG, ' ');
3401 fnd_file.put_line
3402 (fnd_file.LOG,
3403 '---------------------------------------------------------- ');
3404 fnd_file.put_line (fnd_file.LOG, 'Leaving create_sso_user');
3405 fnd_file.put_line
3406 (fnd_file.LOG,
3407 '---------------------------------------------------------- ');
3408 fnd_file.put_line (fnd_file.LOG, ' ');
3409 -- Standard call to get message count and if count is 1, get message info.
3410 fnd_msg_pub.count_and_get (p_encoded => 'F',
3411 p_count => x_msg_count,
3412 p_data => x_msg_data
3413 );
3414
3415
3416 -- end debug log
3417 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3418 THEN
3419 fnd_log.STRING (fnd_log.level_procedure,
3420 g_module ||
3421 l_api_name,
3422 '1000: Leaving ' ||
3423 g_pkg_name ||
3424 '.' ||
3425 l_api_name
3426 );
3427 END IF;
3428 EXCEPTION
3429 WHEN fnd_api.g_exc_error
3430 THEN
3431 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3432 THEN
3433 fnd_log.STRING (fnd_log.level_procedure,
3434 g_module ||
3435 l_api_name,
3436 '2000: Leaving ' ||
3437 g_pkg_name ||
3438 '.' ||
3439 l_api_name
3440 );
3441 END IF;
3442
3443 fnd_file.put_line (fnd_file.LOG, '2000: Leaving create_sso_user');
3444
3445 x_return_status := g_ret_sts_error;
3446 fnd_msg_pub.count_and_get (p_encoded => 'F',
3447 p_count => x_msg_count,
3448 p_data => x_msg_data
3449 );
3450 WHEN fnd_api.g_exc_unexpected_error
3451 THEN
3452 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3453 THEN
3454 fnd_log.STRING (fnd_log.level_procedure,
3455 g_module ||
3456 l_api_name,
3457 '3000: Leaving ' ||
3458 g_pkg_name ||
3459 '.' ||
3460 l_api_name
3461 );
3462 END IF;
3463
3464 fnd_file.put_line (fnd_file.LOG, '3000: Leaving create_sso_user');
3465
3466 x_return_status := g_ret_sts_unexp_error;
3467 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3468 fnd_msg_pub.count_and_get (p_encoded => 'F',
3469 p_count => x_msg_count,
3470 p_data => x_msg_data
3471 );
3472 WHEN OTHERS
3473 THEN
3474 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3475 THEN
3476 fnd_log.STRING (fnd_log.level_procedure,
3477 g_module ||
3478 l_api_name,
3479 '4000: Leaving ' ||
3480 g_pkg_name ||
3481 '.' ||
3482 l_api_name
3483 );
3484 END IF;
3485
3486 fnd_file.put_line (fnd_file.LOG, '4000: Leaving create_sso_user');
3487
3488 x_return_status := g_ret_sts_error;
3489 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3490 fnd_msg_pub.count_and_get (p_encoded => 'F',
3491 p_count => x_msg_count,
3492 p_data => x_msg_data
3493 );
3494 END create_sso_user;
3495 END oks_auto_reminder;