[Home] [Help]
PACKAGE BODY: APPS.OKS_AUTO_REMINDER
Source
1 PACKAGE BODY oks_auto_reminder AS
2 /* $Header: OKSARNWB.pls 120.34 2010/08/26 12:51:04 vgujarat 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 /*start of bug8345674*/
735 CURSOR c_old_sts is
736 SELECT STS_CODE
737 FROM OKC_K_HEADERS_ALL_B okck,
738 OKC_STATUSES_B sts
739 WHERE id = p_chr_id
740 AND sts.code = okck.sts_code;
741
742 l_old_sts OKC_STATUSES_B.CODE%TYPE;
743 /*end of bug8345674*/
744
745 FUNCTION GET_STATUS(pcode IN VARCHAR2) RETURN VARCHAR2
746 IS
747 lcode VARCHAR2(40);
748 BEGIN
749 SELECT STE_CODE INTO lcode FROM okc_statuses_b WHERE code = pcode;
750 RETURN lcode;
751 EXCEPTION
752 WHEN OTHERS THEN
753 RETURN pcode;
754 END;
755 BEGIN
756
757 /*added for bug6651207*/
758 l_status_code := get_status(p_status);
759
760 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
761 THEN
762 fnd_log.STRING (fnd_log.level_procedure,
763 g_module ||
764 l_api_name,
765 'Entered ' ||
766 g_pkg_name ||
767 '.' ||
768 l_api_name ||
769 '(p_chr_id=>' ||
770 p_chr_id ||
771 'p_status=>' ||
772 p_status ||
773 ')'
774 );
775 END IF;
776
777 -- Initialize API return status to success
778 x_return_status := g_ret_sts_success;
779 mo_global.init (p_appl_short_name => 'OKC');
780 l_chrv_rec.ID := p_chr_id;
781 l_chrv_rec.sts_code := p_status;
782
783
784
785 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
786 THEN
787 fnd_log.STRING
788 (fnd_log.level_event,
789 g_module ||
790 l_api_name ||
791 '.external_call.before',
792 'OKC_CONTRACT_PUB.update_contract_header(p_chr_id=' ||
793 p_chr_id ||
794 ')'
795 );
796 END IF;
797 /*modified for bug7342736*/
798 IF l_status_code = 'CANCELLED' THEN
799 l_chrv_rec.datetime_cancelled := SYSDATE;
800 l_chrv_rec.term_cancel_source := 'CUSTOMER';
801 l_chrv_rec.trn_code := 'NFC';
802 l_chrv_rec.new_ste_code := 'CANCELLED';
803 /*commented and modified for bug8345674
804 okc_contract_pub.update_contract_header
805 (p_api_version => l_api_version,
806 p_init_msg_list => okc_api.g_false,
807 x_return_status => l_return_status,
808 x_msg_count => l_msg_count,
809 x_msg_data => l_msg_data,
810 p_restricted_update => okc_api.g_true,
811 p_chrv_rec => l_chrv_rec,
812 x_chrv_rec => x_chrv_rec
813 );
814 */
815 /*start of bug8345674*/
816 OPEN c_old_sts;
817 FETCH c_old_sts INTO l_old_sts;
818 CLOSE c_old_sts;
819
820 OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS (
821 x_return_status => l_return_status,
822 x_msg_data => l_msg_data,
823 x_msg_count => l_msg_count,
824 p_init_msg_list => G_FALSE,
825 p_id => p_chr_id,
826 p_new_sts_code => p_status,
827 p_old_sts_code => l_old_sts,
828 p_canc_reason_code => 'NFC',
829 p_comments => 'Automatic Cancellation of Contract',
830 p_term_cancel_source => 'CUSTOMER',
831 p_date_cancelled => SYSDATE,
832 p_validate_status => 'N') ;
833
834 UPDATE oks_k_headers_b
835 SET renewal_status = 'QUOTE_CNCLD'
836 WHERE chr_id = p_chr_id;
837 /*end of bug8345674*/
838 ELSE
839 okc_contract_pub.update_contract_header
840 (p_api_version => l_api_version,
841 p_init_msg_list => okc_api.g_false,
842 x_return_status => l_return_status,
843 x_msg_count => l_msg_count,
844 x_msg_data => l_msg_data,
845 p_restricted_update => okc_api.g_false,
846 p_chrv_rec => l_chrv_rec,
847 x_chrv_rec => x_chrv_rec
848 );
849 END IF;
850
851 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
852 THEN
853 fnd_log.STRING
854 (fnd_log.level_event,
855 g_module ||
856 l_api_name ||
857 '.external_call.after',
858 'OKC_CONTRACT_PUB.update_contract_header(x_return_status= ' ||
859 l_return_status ||
860 ' l_msg_count =' ||
861 l_msg_count ||
862 ')'
863 );
864 END IF;
865
866 IF l_return_status = g_ret_sts_unexp_error
867 THEN
868 RAISE fnd_api.g_exc_unexpected_error;
869 ELSIF l_return_status = g_ret_sts_error
870 THEN
871 RAISE fnd_api.g_exc_error;
872 END IF;
873
874 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
875 THEN
876 fnd_log.STRING (fnd_log.level_event,
877 g_module ||
878 l_api_name ||
879 '.external_call.before',
880 'OKC_CONTRACT_PUB.update_contract_line - Loop begin'
881 );
882 END IF;
883
884 l_clev_rec.sts_code := p_status;
885
886 /*MODIFIED FOR BUG6651207*/
887 IF Nvl(l_status_code,'X') = 'ENTERED' THEN
888 FOR l_lines_rec IN l_lines_entered_csr
889 LOOP
890 l_clev_rec.ID := l_lines_rec.ID;
891
892 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
893 THEN
894 fnd_log.STRING (fnd_log.level_event,
895 g_module ||
896 l_api_name,
897 'Updating line: ' ||
898 l_lines_rec.ID
899 );
900 END IF;
901
902 okc_contract_pub.update_contract_line
903 (p_api_version => l_api_version,
904 p_init_msg_list => okc_api.g_false,
905 x_return_status => l_return_status,
906 x_msg_count => l_msg_count,
907 x_msg_data => l_msg_data,
908 p_restricted_update => okc_api.g_false,
909 p_clev_rec => l_clev_rec,
910 x_clev_rec => x_clev_rec
911 );
912
913 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
914 THEN
915 fnd_log.STRING (fnd_log.level_event,
916 g_module ||
917 l_api_name,
918 'Result: ' ||
919 l_return_status
920 );
921 END IF;
922
923 IF l_return_status = g_ret_sts_unexp_error
924 THEN
925 RAISE fnd_api.g_exc_unexpected_error;
926 ELSIF l_return_status = g_ret_sts_error
927 THEN
928 RAISE fnd_api.g_exc_error;
929 END IF;
930 END LOOP;
931 ELSE
932 FOR l_lines_rec IN l_lines_csr
933 LOOP
934 l_clev_rec.ID := l_lines_rec.ID;
935
936
937 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
938 THEN
939 fnd_log.STRING (fnd_log.level_event,
940 g_module ||
941 l_api_name,
942 'Updating line: ' ||
943 l_lines_rec.ID
944 );
945 END IF;
946 /*modified for bug7342736*/
947 IF l_status_code = 'CANCELLED' THEN
948 /*commented code here for bug8345674 as the line and subline level
949 cancellation is taken care at header level by OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS api
950 l_clev_rec.date_cancelled := SYSDATE;
951 l_clev_rec.term_cancel_source := 'CUSTOMER';
952 l_clev_rec.trn_code := 'NFC';
953 l_clev_rec.new_ste_code := 'CANCELLED';
954 okc_contract_pub.update_contract_line
955 (p_api_version => l_api_version,
956 p_init_msg_list => okc_api.g_false,
957 x_return_status => l_return_status,
958 x_msg_count => l_msg_count,
959 x_msg_data => l_msg_data,
960 p_restricted_update => okc_api.g_true,
961 p_clev_rec => l_clev_rec,
962 x_clev_rec => x_clev_rec
963 );
964 */
965 NULL;
966 ELSE
967 okc_contract_pub.update_contract_line
968 (p_api_version => l_api_version,
969 p_init_msg_list => okc_api.g_false,
970 x_return_status => l_return_status,
971 x_msg_count => l_msg_count,
972 x_msg_data => l_msg_data,
973 p_restricted_update => okc_api.g_false,
974 p_clev_rec => l_clev_rec,
975 x_clev_rec => x_clev_rec
976 );
977 END IF;
978 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
979 THEN
980 fnd_log.STRING (fnd_log.level_event,
981 g_module ||
982 l_api_name,
983 'Result: ' ||
984 l_return_status
985 );
986 END IF;
987
988 IF l_return_status = g_ret_sts_unexp_error
989 THEN
990 RAISE fnd_api.g_exc_unexpected_error;
991 ELSIF l_return_status = g_ret_sts_error
992 THEN
993 RAISE fnd_api.g_exc_error;
994 END IF;
995 END LOOP;
996 END IF; /*IF NVL(l_status_code,'X') = 'ENTERED' THEN*/
997
998 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
999 THEN
1000 fnd_log.STRING (fnd_log.level_event,
1001 g_module ||
1002 l_api_name ||
1003 '.external_call.after',
1004 'OKC_CONTRACT_PUB.update_contract_line - loop end'
1005 );
1006 END IF;
1007 EXCEPTION
1008 WHEN fnd_api.g_exc_error
1009 THEN
1010 fnd_msg_pub.count_and_get (p_encoded => 'F',
1011 p_count => l_msg_count,
1012 p_data => l_msg_data
1013 );
1014
1015 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1016 THEN
1017 fnd_log.STRING (fnd_log.level_procedure,
1018 g_module ||
1019 l_api_name,
1020 'Leaving ' ||
1021 g_pkg_name ||
1022 '.' ||
1023 l_api_name ||
1024 ' from G_EXC_ERROR'
1025 );
1026 fnd_log.STRING (fnd_log.level_procedure,
1027 g_module ||
1028 l_api_name,
1029 l_msg_data
1030 );
1031 END IF;
1032
1033 x_return_status := g_ret_sts_error;
1034 WHEN fnd_api.g_exc_unexpected_error
1035 THEN
1036 fnd_msg_pub.count_and_get (p_encoded => 'F',
1037 p_count => l_msg_count,
1038 p_data => l_msg_data
1039 );
1040
1041 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1042 THEN
1043 fnd_log.STRING (fnd_log.level_procedure,
1044 g_module ||
1045 l_api_name,
1046 'Leaving ' ||
1047 g_pkg_name ||
1048 '.' ||
1049 l_api_name ||
1050 ' from G_EXC_UNEXPECTED_ERROR'
1051 );
1052 fnd_log.STRING (fnd_log.level_procedure,
1053 g_module ||
1054 l_api_name,
1055 l_msg_data
1056 );
1057 END IF;
1058
1059 x_return_status := g_ret_sts_unexp_error;
1060 WHEN OTHERS
1061 THEN
1062 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1063 THEN
1064 fnd_log.STRING (fnd_log.level_procedure,
1065 g_module ||
1066 l_api_name,
1067 'Leaving ' ||
1068 g_pkg_name ||
1069 '.' ||
1070 l_api_name ||
1071 ' from OTHERS sqlcode = ' ||
1072 SQLCODE ||
1073 ', sqlerrm = ' ||
1074 SQLERRM
1075 );
1076 fnd_msg_pub.add_exc_msg (g_pkg_name,
1077 l_api_name,
1078 SUBSTR (SQLERRM,
1079 1,
1080 240
1081 )
1082 );
1083 END IF;
1084
1085 fnd_msg_pub.count_and_get (p_encoded => 'F',
1086 p_count => l_msg_count,
1087 p_data => l_msg_data
1088 );
1089 x_return_status := g_ret_sts_unexp_error;
1090 END update_contract_status;
1091
1092 PROCEDURE get_time_stats (
1093 p_start_date IN DATE,
1094 p_end_date IN DATE,
1095 x_duration OUT NOCOPY NUMBER,
1096 x_period OUT NOCOPY VARCHAR2,
1097 x_return_status OUT NOCOPY VARCHAR2
1098 )
1099 IS
1100 l_api_name CONSTANT VARCHAR2 (30)
1101 := 'get_time_stats';
1102 l_duration NUMBER;
1103 l_period VARCHAR2 (10);
1104 l_return_status VARCHAR2 (1)
1105 := okc_api.g_ret_sts_success;
1106 BEGIN
1107 IF p_start_date > p_end_date
1108 THEN
1109 okc_time_util_pub.get_duration (p_start_date => p_end_date,
1110 p_end_date => p_start_date,
1111 x_duration => l_duration,
1112 x_timeunit => l_period,
1113 x_return_status => l_return_status
1114 );
1115 x_duration := l_duration;
1116 ELSE
1117 okc_time_util_pub.get_duration (p_start_date => p_start_date,
1118 p_end_date => p_end_date,
1119 x_duration => l_duration,
1120 x_timeunit => l_period,
1121 x_return_status => l_return_status
1122 );
1123 x_duration := l_duration *
1124 -1;
1125 END IF;
1126
1127 IF l_return_status <> okc_api.g_ret_sts_success
1128 THEN
1129 x_duration := okc_api.g_miss_num;
1130 x_period := okc_api.g_miss_char;
1131 ELSE
1132 x_period := l_period;
1133 END IF;
1134
1135 x_return_status := l_return_status;
1136 EXCEPTION
1137 WHEN OTHERS
1138 THEN
1139 x_return_status := okc_api.g_ret_sts_error;
1140 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1141 END;
1142
1143 PROCEDURE get_duration (
1144 p_start_date IN DATE,
1145 p_end_date IN DATE,
1146 p_source_uom IN VARCHAR2,
1147 x_duration OUT NOCOPY NUMBER
1148 )
1149 IS
1150 l_api_name CONSTANT VARCHAR2 (30) := 'get_duration';
1151 l_duration NUMBER;
1152 l_period VARCHAR2 (10);
1153 BEGIN
1154 IF p_start_date > p_end_date
1155 THEN
1156 l_duration :=
1157 oks_time_measures_pub.get_quantity (p_start_date => p_end_date,
1158 p_end_date => p_start_date,
1159 p_source_uom => p_source_uom
1160 );
1161 x_duration := l_duration;
1162 ELSE
1163 l_duration :=
1164 oks_time_measures_pub.get_quantity (p_start_date => p_start_date,
1165 p_end_date => p_end_date,
1166 p_source_uom => p_source_uom
1167 );
1168 x_duration := l_duration *
1169 -1;
1170 END IF;
1171 EXCEPTION
1172 WHEN OTHERS
1173 THEN
1174 x_duration := okc_api.g_miss_num;
1175 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1176 END;
1177
1178 PROCEDURE update_renewal_status (
1179 p_chr_id IN NUMBER,
1180 p_renewal_status IN VARCHAR2,
1181 x_return_status OUT NOCOPY VARCHAR2
1182 )
1183 IS
1184 l_api_name CONSTANT VARCHAR2 (30)
1185 := 'update_renewal_status';
1186 l_return_status VARCHAR2 (1) := 'S';
1187 l_api_version NUMBER := 1.0;
1188 l_init_msg_list VARCHAR2 (1) := 'F';
1189 l_msg_count NUMBER;
1190 l_msg_data VARCHAR2 (2000);
1191 l_khdr_rec_in oks_contract_hdr_pub.khrv_rec_type;
1192 l_khdr_rec_out oks_contract_hdr_pub.khrv_rec_type;
1193 l_kdetails_rec oks_qp_int_pvt.k_details_rec;
1194 BEGIN
1195 l_return_status := okc_api.g_ret_sts_success;
1196 oks_qp_int_pvt.get_k_details (p_id => p_chr_id,
1197 p_type => 'KHR',
1198 x_k_det_rec => l_kdetails_rec
1199 );
1200 l_khdr_rec_in.chr_id := p_chr_id;
1201 l_khdr_rec_in.renewal_status := p_renewal_status;
1202 l_khdr_rec_in.ID := l_kdetails_rec.ID;
1203 l_khdr_rec_in.object_version_number :=
1204 l_kdetails_rec.object_version_number;
1205 oks_contract_hdr_pub.update_header (p_api_version => l_api_version,
1206 p_init_msg_list => l_init_msg_list,
1207 x_return_status => l_return_status,
1208 x_msg_count => l_msg_count,
1209 x_msg_data => l_msg_data,
1210 p_khrv_rec => l_khdr_rec_in,
1211 x_khrv_rec => l_khdr_rec_out,
1212 p_validate_yn => 'N'
1213 );
1214
1215 IF (l_return_status <> okc_api.g_ret_sts_success)
1216 THEN
1217 RAISE g_exception_halt_validation;
1218 END IF;
1219
1220 x_return_status := l_return_status;
1221 EXCEPTION
1222 WHEN g_exception_halt_validation
1223 THEN
1224 x_return_status := l_return_status;
1225 WHEN OTHERS
1226 THEN
1227 x_return_status := l_return_status;
1228 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1229 END;
1230
1231 FUNCTION get_product_name (
1232 p_lse_id IN NUMBER,
1233 p_object_id IN NUMBER,
1234 p_inv_org_id IN NUMBER DEFAULT NULL
1235 )
1236 RETURN VARCHAR2
1237 IS
1238 l_name VARCHAR2 (1000);
1239 l_return_status VARCHAR2 (1);
1240
1241 CURSOR c1
1242 IS
1243 SELECT mtl.NAME
1244 FROM okx_system_items_v mtl,
1245 okx_cust_prod_v cp
1246 WHERE mtl.id1 = cp.inventory_item_id
1247 AND mtl.organization_id = p_inv_org_id
1248 AND cp.customer_product_id = p_object_id;
1249
1250 CURSOR c2
1251 IS
1252 SELECT NAME
1253 FROM okx_parties_v
1254 WHERE id1 = p_object_id
1255 AND id2 = '#';
1256
1257 CURSOR c3
1258 IS
1259 SELECT NAME
1260 FROM okx_systems_v
1261 WHERE id1 = p_object_id
1262 AND id2 = '#';
1263 BEGIN
1264 IF p_lse_id = 25
1265 THEN
1266 OPEN c1;
1267
1268 FETCH c1
1269 INTO l_name;
1270
1271 CLOSE c1;
1272 END IF;
1273
1274 IF p_lse_id = 35
1275 THEN
1276 OPEN c2;
1277
1278 FETCH c2
1279 INTO l_name;
1280
1281 CLOSE c2;
1282 END IF;
1283
1284 IF p_lse_id = 11
1285 THEN
1286 OPEN c3;
1287
1288 FETCH c3
1289 INTO l_name;
1290
1291 CLOSE c3;
1292 END IF;
1293
1294 RETURN l_name;
1295 EXCEPTION
1296 WHEN OTHERS
1297 THEN
1298 RETURN NULL;
1299 END;
1300
1301 FUNCTION get_no_of_users (
1302 p_lse_id IN NUMBER,
1303 p_object_id IN NUMBER
1304 )
1305 RETURN VARCHAR2
1306 IS
1307 l_name VARCHAR2 (1000);
1308
1309 CURSOR l_users_csr
1310 IS
1311 SELECT pricing_attribute3
1312 FROM cs_customer_products_all
1313 WHERE customer_product_id = p_object_id;
1314 BEGIN
1315 IF p_lse_id = 25
1316 THEN
1317 OPEN l_users_csr;
1318
1319 FETCH l_users_csr
1320 INTO l_name;
1321
1322 CLOSE l_users_csr;
1323 END IF;
1324
1325 RETURN l_name;
1326 EXCEPTION
1327 WHEN OTHERS
1328 THEN
1329 RETURN NULL;
1330 END;
1331
1332 FUNCTION get_service_name (
1333 p_line_id IN NUMBER,
1334 p_contract_id IN NUMBER
1335 )
1336 RETURN VARCHAR2
1337 IS
1338 CURSOR l_service_csr
1339 IS
1340 SELECT NAME
1341 FROM okx_system_items_v mtl,
1342 okc_k_items itm
1343 WHERE mtl.id1 = itm.object1_id1
1344 AND mtl.id2 = itm.object1_id2
1345 AND itm.cle_id = p_line_id
1346 AND itm.dnz_chr_id = p_contract_id;
1347
1348 l_service_name okx_system_items_v.NAME%TYPE;
1349 BEGIN
1350 OPEN l_service_csr;
1351
1352 FETCH l_service_csr
1353 INTO l_service_name;
1354
1355 CLOSE l_service_csr;
1356
1357 RETURN l_service_name;
1358 EXCEPTION
1359 WHEN OTHERS
1360 THEN
1361 IF l_service_csr%ISOPEN
1362 THEN
1363 CLOSE l_service_csr;
1364 END IF;
1365
1366 RETURN NULL;
1367 END;
1368
1369 FUNCTION get_license_level (
1370 p_lse_id IN NUMBER,
1371 p_object_id IN NUMBER
1372 )
1373 RETURN VARCHAR2
1374 IS
1375 l_name VARCHAR2 (1000);
1376
1377 CURSOR l_license_level_csr
1378 IS
1379 SELECT pricing_attribute5
1380 FROM cs_customer_products_all
1381 WHERE customer_product_id = p_object_id;
1382 BEGIN
1383 IF p_lse_id = 25
1384 THEN
1385 OPEN l_license_level_csr;
1386
1387 FETCH l_license_level_csr
1388 INTO l_name;
1389
1390 CLOSE l_license_level_csr;
1391 END IF;
1392
1393 RETURN l_name;
1394 EXCEPTION
1395 WHEN OTHERS
1396 THEN
1397 RETURN NULL;
1398 END;
1399
1400 FUNCTION get_pricing_type (
1401 p_lse_id IN NUMBER,
1402 p_object_id IN NUMBER
1403 )
1404 RETURN VARCHAR2
1405 IS
1406 l_name VARCHAR2 (1000);
1407
1408 CURSOR l_pricing_type_csr
1409 IS
1410 SELECT pricing_attribute4
1411 FROM cs_customer_products_all
1412 WHERE customer_product_id = p_object_id;
1413 BEGIN
1414 IF p_lse_id = 25
1415 THEN
1416 OPEN l_pricing_type_csr;
1417
1418 FETCH l_pricing_type_csr
1419 INTO l_name;
1420
1421 CLOSE l_pricing_type_csr;
1422 END IF;
1423
1424 RETURN l_name;
1425 EXCEPTION
1426 WHEN OTHERS
1427 THEN
1428 RETURN NULL;
1429 END;
1430
1431 FUNCTION get_billto_contact (
1432 p_org_id IN NUMBER,
1433 p_inv_org_id IN NUMBER,
1434 p_contract_id IN NUMBER
1435 )
1436 RETURN VARCHAR2
1437 IS
1438 CURSOR l_billto_contact_csr
1439 IS
1440 SELECT object1_id1
1441 FROM okc_contacts ct
1442 WHERE ct.cro_code = 'BILLING'
1443 AND ct.dnz_chr_id = p_contract_id;
1444
1445 l_id NUMBER;
1446 l_name VARCHAR2 (240);
1447
1448 CURSOR l_btc_name_csr
1449 IS
1450 SELECT NAME
1451 FROM okx_party_contacts_v
1452 WHERE id1 = l_id;
1453 BEGIN
1454 okc_context.set_okc_org_context (p_org_id, p_inv_org_id);
1455
1456 OPEN l_billto_contact_csr;
1457
1458 FETCH l_billto_contact_csr
1459 INTO l_id;
1460
1461 CLOSE l_billto_contact_csr;
1462
1463 OPEN l_btc_name_csr;
1464
1465 FETCH l_btc_name_csr
1466 INTO l_name;
1467
1468 CLOSE l_btc_name_csr;
1469
1470 RETURN l_name;
1471 EXCEPTION
1472 WHEN OTHERS
1473 THEN
1474 RETURN NULL;
1475 END;
1476
1477 FUNCTION get_billto_phone (
1478 p_contract_id IN NUMBER
1479 )
1480 RETURN VARCHAR2
1481 IS
1482 CURSOR l_phone_csr (
1483 p_id NUMBER
1484 )
1485 IS
1486 SELECT phone_area_code ||
1487 '-' ||
1488 phone_number
1489 FROM okx_contact_points_v
1490 WHERE owner_table_id = p_id
1491 AND owner_table_name = 'HZ_PARTIES'
1492 AND contact_point_type = 'PHONE'
1493 AND phone_number IS NOT NULL
1494 ORDER BY DECODE (phone_line_type,
1495 'GEN', 1,
1496 'OFFICE', 2,
1497 'PHONE', 3,
1498 'Direct Phone', 4,
1499 5
1500 );
1501
1502 CURSOR l_svc_admin_csr
1503 IS
1504 SELECT object1_id1
1505 FROM okc_contacts ct
1506 WHERE ct.cro_code = 'BILLING'
1507 AND ct.dnz_chr_id = p_contract_id;
1508
1509 l_id NUMBER;
1510 l_phone VARCHAR2 (240);
1511 BEGIN
1512 OPEN l_svc_admin_csr;
1513
1514 FETCH l_svc_admin_csr
1515 INTO l_id;
1516
1517 CLOSE l_svc_admin_csr;
1518
1519 OPEN l_phone_csr (l_id);
1520
1521 FETCH l_phone_csr
1522 INTO l_phone;
1523
1524 CLOSE l_phone_csr;
1525
1526 RETURN l_phone;
1527 EXCEPTION
1528 WHEN OTHERS
1529 THEN
1530 RETURN NULL;
1531 END;
1532
1533 FUNCTION get_billto_fax (
1534 p_contract_id IN NUMBER
1535 )
1536 RETURN VARCHAR2
1537 IS
1538 CURSOR l_phone_csr (
1539 p_id NUMBER
1540 )
1541 IS
1542 SELECT phone_area_code ||
1543 '-' ||
1544 phone_number
1545 FROM okx_contact_points_v
1546 WHERE owner_table_id = p_id
1547 AND owner_table_name = 'HZ_PARTIES'
1548 AND contact_point_type IN ('PHONE', 'FAX')
1549 AND phone_line_type = 'FAX';
1550
1551 CURSOR l_svc_admin_csr
1552 IS
1553 SELECT object1_id1
1554 FROM okc_contacts ct
1555 WHERE ct.cro_code = 'BILLING'
1556 AND ct.dnz_chr_id = p_contract_id;
1557
1558 l_id NUMBER;
1559 l_phone VARCHAR2 (240);
1560 BEGIN
1561 OPEN l_svc_admin_csr;
1562
1563 FETCH l_svc_admin_csr
1564 INTO l_id;
1565
1566 CLOSE l_svc_admin_csr;
1567
1568 OPEN l_phone_csr (l_id);
1569
1570 FETCH l_phone_csr
1571 INTO l_phone;
1572
1573 CLOSE l_phone_csr;
1574
1575 RETURN l_phone;
1576 EXCEPTION
1577 WHEN OTHERS
1578 THEN
1579 RETURN NULL;
1580 END;
1581
1582 FUNCTION get_billto_email (
1583 p_contract_id IN NUMBER
1584 )
1585 RETURN VARCHAR2
1586 IS
1587 /*
1588 Forward port for bug 5051455 (bug 5082822)
1589 modified the below cursor to get billto_email addres
1590 */
1591 CURSOR l_billto_email IS
1592 SELECT hcp.email_address
1593 FROM hz_contact_points hcp,
1594 okc_contacts kc
1595 WHERE hcp.owner_table_id = kc.object1_id1
1596 AND kc.cro_code = 'BILLING'
1597 AND hcp.contact_point_type='EMAIL'
1598 AND hcp.owner_table_name = 'HZ_PARTIES'
1599 AND hcp.content_source_type = 'USER_ENTERED'
1600 AND hcp.status = 'A'
1601 AND kc.dnz_chr_id= p_contract_id
1602 ORDER BY hcp.primary_flag desc;
1603
1604 l_email VARCHAR2 (2000);
1605
1606 BEGIN
1607 -- Forward port Bug 5051455
1608 OPEN l_billto_email;
1609 FETCH l_billto_email INTO l_email;
1610 CLOSE l_billto_email;
1611
1612 RETURN l_email;
1613
1614 EXCEPTION
1615 WHEN OTHERS
1616 THEN
1617 RETURN NULL;
1618 END;
1619
1620 PROCEDURE log_interaction (
1621 x_return_status OUT NOCOPY VARCHAR2,
1622 x_msg_count OUT NOCOPY NUMBER,
1623 x_msg_data OUT NOCOPY VARCHAR2,
1624 p_chr_id IN VARCHAR2,
1625 p_subject IN VARCHAR2 DEFAULT NULL,
1626 p_msg_body IN VARCHAR2 DEFAULT NULL,
1627 p_sent2_email IN VARCHAR2 DEFAULT NULL
1628 )
1629 IS
1630 BEGIN
1631 oks_auto_reminder.log_interaction (p_api_version => 1.0,
1632 p_init_msg_list => 'F',
1633 x_return_status => x_return_status,
1634 x_msg_count => x_msg_count,
1635 x_msg_data => x_msg_data,
1636 p_chr_id => TO_NUMBER
1637 (p_chr_id),
1638 p_subject => p_subject,
1639 p_msg_body => p_msg_body,
1640 p_sent2_email => p_sent2_email
1641 );
1642 EXCEPTION
1643 WHEN OTHERS
1644 THEN
1645 x_return_status := okc_api.g_ret_sts_unexp_error;
1646 okc_api.set_message (g_app_name,
1647 g_unexpected_error,
1648 g_sqlcode_token,
1649 SQLCODE,
1650 g_sqlerrm_token,
1651 SQLERRM
1652 );
1653 END;
1654
1655 PROCEDURE log_interaction (
1656 p_api_version IN NUMBER,
1657 p_init_msg_list IN VARCHAR2,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2,
1661 p_chr_id IN NUMBER,
1662 p_subject IN VARCHAR2 DEFAULT NULL,
1663 p_msg_body IN VARCHAR2 DEFAULT NULL,
1664 p_sent2_email IN VARCHAR2 DEFAULT NULL,
1665 p_media_type IN VARCHAR2 DEFAULT 'EMAIL'
1666 )
1667 IS
1668 l_api_version CONSTANT NUMBER := 1.0;
1669 l_api_name CONSTANT VARCHAR2 (50)
1670 := 'log_interaction';
1671
1672 CURSOR l_kdetails_csr (
1673 p_chr_id NUMBER
1674 )
1675 IS
1676 SELECT contract_number,
1677 contract_number_modifier
1678 FROM okc_k_headers_all_b
1679 WHERE ID = p_chr_id;
1680
1681 CURSOR l_userres_csr (
1682 p_user_id NUMBER
1683 )
1684 IS
1685 SELECT rsc.resource_id resource_id
1686 FROM jtf_rs_resource_extns rsc,
1687 fnd_user u
1688 WHERE u.user_id = rsc.user_id
1689 AND u.user_id = p_user_id;
1690
1691 l_rownotfound BOOLEAN := FALSE;
1692 l_salesrep_resource_id NUMBER;
1693 l_salesrep_id NUMBER;
1694 l_salesrep_name VARCHAR2 (100);
1695 l_qto_email VARCHAR2 (2000);
1696 l_party_id NUMBER;
1697 l_subject VARCHAR2 (200);
1698 l_interaction_body VARCHAR2 (2000);
1699 l_interaction_id NUMBER;
1700 l_kdetails_rec l_kdetails_csr%ROWTYPE;
1701 BEGIN
1702 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
1703 THEN
1704 fnd_log.STRING (fnd_log.level_procedure,
1705 g_module ||
1706 l_api_name,
1707 'Entered ' ||
1708 g_pkg_name ||
1709 '.' ||
1710 l_api_name ||
1711 '(p_chr_id=>' ||
1712 p_chr_id ||
1713 'p_subject=>' ||
1714 SUBSTR (p_subject,
1715 1,
1716 240
1717 ) ||
1718 'p_msg_body=>' ||
1719 SUBSTR (p_msg_body,
1720 1,
1721 240
1722 ) ||
1723 'p_sent2_email=>' ||
1724 SUBSTR (p_sent2_email,
1725 1,
1726 240
1727 ) ||
1728 ')'
1729 );
1730 END IF;
1731
1732 -- Initialize message list if p_init_msg_list is set to TRUE.
1733 IF fnd_api.to_boolean (p_init_msg_list)
1734 THEN
1735 fnd_msg_pub.initialize;
1736 END IF;
1737
1738 -- Initialize API return status to success
1739 x_return_status := g_ret_sts_success;
1740 mo_global.init (p_appl_short_name => 'OKC');
1741
1742 OPEN l_kdetails_csr (p_chr_id);
1743
1744 FETCH l_kdetails_csr
1745 INTO l_kdetails_rec;
1746
1747 l_rownotfound := l_kdetails_csr%NOTFOUND;
1748
1749 CLOSE l_kdetails_csr;
1750
1751 IF l_rownotfound
1752 THEN
1753 fnd_message.set_name (g_app_name, 'OKS_INVD_CONTRACT_ID');
1754 fnd_message.set_token ('HDR_ID', p_chr_id);
1755 fnd_msg_pub.ADD;
1756 RAISE fnd_api.g_exc_error;
1757 END IF;
1758
1759 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1760 THEN
1761 fnd_log.STRING (fnd_log.level_event,
1762 g_module ||
1763 l_api_name ||
1764 '.external_call.before',
1765 'OKS_AUTO_REMINDER.get_qtoparty_id(p_chr_id= ' ||
1766 p_chr_id ||
1767 ')'
1768 );
1769 END IF;
1770
1771 -- Get the party ID for logging interaction history
1772 oks_auto_reminder.get_qtoparty_id (p_chr_id => p_chr_id,
1773 x_party_id => l_party_id);
1774
1775 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1776 THEN
1777 fnd_log.STRING (fnd_log.level_event,
1778 g_module ||
1779 l_api_name ||
1780 '.external_call.after',
1781 'OKS_AUTO_REMINDER.get_qtoparty_id(x_party_id= ' ||
1782 l_party_id ||
1783 ')'
1784 );
1785 END IF;
1786
1787 IF l_party_id IS NULL
1788 THEN
1789 fnd_message.set_name (g_app_name, 'OKS_NO_QTO_CONTACT');
1790 fnd_msg_pub.ADD;
1791 RAISE fnd_api.g_exc_error;
1792 END IF;
1793
1794 -- Get Salesrep user name which will be used as performer
1795 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1796 THEN
1797 fnd_log.STRING (fnd_log.level_event,
1798 g_module ||
1799 l_api_name ||
1800 '.external_call.before',
1801 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(p_chr_id=' ||
1802 p_chr_id ||
1803 ')'
1804 );
1805 END IF;
1806
1807 oks_renew_contract_pvt.get_user_name
1808 (p_api_version => l_api_version,
1809 p_init_msg_list => g_false,
1810 x_return_status => x_return_status,
1811 x_msg_count => x_msg_count,
1812 x_msg_data => x_msg_data,
1813 p_chr_id => p_chr_id,
1814 p_hdesk_user_id => NULL,
1815 x_user_id => l_salesrep_id,
1816 x_user_name => l_salesrep_name
1817 );
1818
1819 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1820 THEN
1821 fnd_log.STRING
1822 (fnd_log.level_event,
1823 g_module ||
1824 l_api_name ||
1825 '.external_call.after',
1826 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(x_return_status= ' ||
1827 x_return_status ||
1828 ' x_msg_count =' ||
1829 x_msg_count ||
1830 ')'
1831 );
1832 fnd_log.STRING (fnd_log.level_event,
1833 g_module ||
1834 l_api_name ||
1835 '.external_call.after',
1836 ' x_user_id =' ||
1837 l_salesrep_id ||
1838 ' x_user_name =' ||
1839 l_salesrep_name
1840 );
1841 END IF;
1842
1843 IF x_return_status = g_ret_sts_unexp_error
1844 THEN
1845 RAISE fnd_api.g_exc_unexpected_error;
1846 ELSIF x_return_status = g_ret_sts_error
1847 THEN
1848 RAISE fnd_api.g_exc_error;
1849 END IF;
1850
1851 OPEN l_userres_csr (l_salesrep_id);
1852
1853 FETCH l_userres_csr
1854 INTO l_salesrep_resource_id;
1855
1856 l_rownotfound := l_userres_csr%NOTFOUND;
1857
1858 CLOSE l_userres_csr;
1859
1860 IF l_rownotfound
1861 OR l_salesrep_resource_id IS NULL
1862 THEN
1863 fnd_message.set_name (g_app_name, 'OKS_INTERACTION_FAILED');
1864 fnd_msg_pub.ADD;
1865 RAISE fnd_api.g_exc_error;
1866 END IF;
1867
1868 IF p_subject IS NULL
1869 THEN
1870 fnd_message.set_name ('OKS', 'OKS_INT_HISTORY_SUBJECT');
1871 l_subject := fnd_message.get;
1872 ELSE
1873 l_subject := p_subject;
1874 END IF;
1875
1876 IF p_msg_body IS NULL
1877 THEN
1878 -- assemble interaction history body
1879 fnd_message.set_name ('OKS', 'OKS_INT_HISTORY_MSG_BODY');
1880 fnd_message.set_token ('TOKEN1', l_kdetails_rec.contract_number);
1881 fnd_message.set_token ('TOKEN2',
1882 l_kdetails_rec.contract_number_modifier);
1883
1884 IF p_sent2_email IS NULL
1885 THEN
1886 oks_auto_reminder.get_qto_email (p_chr_id => p_chr_id,
1887 x_qto_email => l_qto_email);
1888
1889 IF l_qto_email = okc_api.g_miss_char
1890 THEN
1891 fnd_message.set_name (g_app_name, 'OKS_NO_QTO_EMAIL');
1892 fnd_msg_pub.ADD;
1893 RAISE fnd_api.g_exc_error;
1894 END IF;
1895
1896 fnd_message.set_token ('TOKEN3', l_qto_email);
1897 ELSE
1898 fnd_message.set_token ('TOKEN3', p_sent2_email);
1899 END IF;
1900
1901 l_interaction_body := fnd_message.get;
1902 ELSE
1903 l_interaction_body := p_msg_body;
1904 END IF;
1905
1906 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1907 THEN
1908 fnd_log.STRING
1909 (fnd_log.level_event,
1910 g_module ||
1911 l_api_name ||
1912 '.external_call.before',
1913 'OKC_INTERACT_HISTORY_PUB.CREATE_INTERACT_HISTORY(p_chr_id=' ||
1914 p_chr_id ||
1915 'p_notes=>' ||
1916 SUBSTR (l_subject,
1917 1,
1918 240
1919 ) ||
1920 'p_notes_detail=>' ||
1921 SUBSTR (l_interaction_body,
1922 1,
1923 240
1924 ) ||
1925 'p_resource1_id=>' ||
1926 l_party_id ||
1927 'p_resource2_id=>' ||
1928 l_salesrep_resource_id ||
1929 ')'
1930 );
1931 END IF;
1932
1933 okc_interact_history_pub.create_interact_history
1934 (x_return_status => x_return_status,
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 x_interaction_id => l_interaction_id,
1938 p_media_type => p_media_type,
1939 p_action_item_id => 45,
1940 -- Email Message
1941 p_outcome_id => 41,
1942 -- Compose
1943 p_touchpoint1_type => 'PARTY',
1944 p_resource1_id => l_party_id,
1945 p_touchpoint2_type => 'EMPLOYEE',
1946 p_resource2_id => l_salesrep_resource_id,
1947 p_contract_id => p_chr_id,
1948 p_int_start_date => SYSDATE,
1949 p_int_end_date => SYSDATE,
1950 p_notes => l_subject,
1951 p_notes_detail => l_interaction_body
1952 );
1953
1954 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
1955 THEN
1956 fnd_log.STRING
1957 (fnd_log.level_event,
1958 g_module ||
1959 l_api_name ||
1960 '.external_call.after',
1961 'OKC_INTERACT_HISTORY_PUB.CREATE_INTERACT_HISTORY(x_return_status= ' ||
1962 x_return_status ||
1963 ' x_msg_count =' ||
1964 x_msg_count ||
1965 'x_interaction_id =' ||
1966 l_interaction_id ||
1967 ')'
1968 );
1969 END IF;
1970
1971 IF x_return_status = g_ret_sts_unexp_error
1972 THEN
1973 RAISE fnd_api.g_exc_unexpected_error;
1974 ELSIF x_return_status = g_ret_sts_error
1975 THEN
1976 RAISE fnd_api.g_exc_error;
1977 END IF;
1978 EXCEPTION
1979 WHEN fnd_api.g_exc_error
1980 THEN
1981 fnd_msg_pub.count_and_get (p_encoded => 'F',
1982 p_count => x_msg_count,
1983 p_data => x_msg_data
1984 );
1985
1986 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1987 THEN
1988 fnd_log.STRING (fnd_log.level_procedure,
1989 g_module ||
1990 l_api_name,
1991 'Leaving ' ||
1992 g_pkg_name ||
1993 '.' ||
1994 l_api_name ||
1995 ' from G_EXC_ERROR'
1996 );
1997 fnd_log.STRING (fnd_log.level_procedure,
1998 g_module ||
1999 l_api_name,
2000 x_msg_data
2001 );
2002 END IF;
2003
2004 x_return_status := g_ret_sts_error;
2005 WHEN fnd_api.g_exc_unexpected_error
2006 THEN
2007 fnd_msg_pub.count_and_get (p_encoded => 'F',
2008 p_count => x_msg_count,
2009 p_data => x_msg_data
2010 );
2011
2012 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2013 THEN
2014 fnd_log.STRING (fnd_log.level_procedure,
2015 g_module ||
2016 l_api_name,
2017 'Leaving ' ||
2018 g_pkg_name ||
2019 '.' ||
2020 l_api_name ||
2021 ' from G_EXC_UNEXPECTED_ERROR'
2022 );
2023 fnd_log.STRING (fnd_log.level_procedure,
2024 g_module ||
2025 l_api_name,
2026 x_msg_data
2027 );
2028 END IF;
2029
2030 x_return_status := g_ret_sts_unexp_error;
2031 WHEN OTHERS
2032 THEN
2033 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2034 THEN
2035 fnd_log.STRING (fnd_log.level_procedure,
2036 g_module ||
2037 l_api_name,
2038 'Leaving ' ||
2039 g_pkg_name ||
2040 '.' ||
2041 l_api_name ||
2042 ' from OTHERS sqlcode = ' ||
2043 SQLCODE ||
2044 ', sqlerrm = ' ||
2045 SQLERRM
2046 );
2047 fnd_msg_pub.add_exc_msg (g_pkg_name,
2048 l_api_name,
2049 SUBSTR (SQLERRM,
2050 1,
2051 240
2052 )
2053 );
2054 END IF;
2055
2056 fnd_msg_pub.count_and_get (p_encoded => 'F',
2057 p_count => x_msg_count,
2058 p_data => x_msg_data
2059 );
2060 x_return_status := g_ret_sts_unexp_error;
2061 END log_interaction;
2062
2063 FUNCTION get_fnd_message RETURN VARCHAR2 IS
2064 i NUMBER := 0;
2065 l_return_status VARCHAR2(1);
2066 l_msg_count NUMBER;
2067 l_msg_data VARCHAR2(2000);
2068 l_msg_index_out NUMBER;
2069 l_mesg VARCHAR2(2000) := NULL;
2070 BEGIN
2071 FOR i in 1..fnd_msg_pub.count_msg
2072 LOOP
2073 fnd_msg_pub.get
2074 (
2075 p_msg_index => i,
2076 p_encoded => 'F',
2077 p_data => l_msg_data,
2078 p_msg_index_out => l_msg_index_out
2079 );
2080 IF l_mesg IS NULL THEN
2081 l_mesg := i || ':' || l_msg_data;
2082 ELSE
2083 l_mesg := l_mesg || ':' || i || ':' || l_msg_data;
2084 END IF;
2085 END LOOP;
2086 RETURN l_mesg;
2087 END get_fnd_message;
2088
2089 PROCEDURE validate_autoreminder_k (
2090 p_chr_id IN VARCHAR2,
2091 x_is_eligible OUT NOCOPY VARCHAR2,
2092 x_quote_id OUT NOCOPY VARCHAR2,
2093 x_cover_id OUT NOCOPY VARCHAR2,
2094 x_sender OUT NOCOPY VARCHAR2,
2095 x_qto_email OUT NOCOPY VARCHAR2,
2096 x_subject OUT NOCOPY VARCHAR2,
2097 x_status OUT NOCOPY VARCHAR2,
2098 x_attachment_name OUT NOCOPY VARCHAR2,
2099 x_return_status OUT NOCOPY VARCHAR2,
2100 x_msg_count OUT NOCOPY VARCHAR2,
2101 x_msg_data OUT NOCOPY VARCHAR2
2102 )
2103 IS
2104 l_template_set_id NUMBER;
2105 l_duration NUMBER;
2106 l_period VARCHAR2 (10);
2107 l_report_type VARCHAR2 (90);
2108 l_temp_duration NUMBER;
2109 l_count NUMBER := 0;
2110 l_wf_name VARCHAR2 (150);
2111 l_wf_process_name VARCHAR2 (150);
2112 l_package_name VARCHAR2 (150);
2113 l_procedure_name VARCHAR2 (150);
2114 l_usage VARCHAR2 (150);
2115 l_concat_k_number VARCHAR2 (250);
2116
2117 CURSOR l_k_details_csr (
2118 p_chr_id NUMBER
2119 )
2120 IS
2121 SELECT kh.ID,
2122 kh.contract_number,
2123 kh.contract_number_modifier,
2124 kh.start_date,
2125 kh.end_date,
2126 kh.sts_code
2127 FROM okc_k_headers_b kh
2128 WHERE kh.ID = p_chr_id;
2129
2130 l_k_details_rec l_k_details_csr%ROWTYPE;
2131
2132 CURSOR l_reports_csr (
2133 l_template_set VARCHAR2,
2134 l_period VARCHAR2,
2135 p_process_code VARCHAR2,
2136 p_applies_to VARCHAR2
2137 )
2138 IS
2139 SELECT message_template_id,
2140 report_duration,
2141 report_period,
2142 template_set_type,
2143 report_id,
2144 attachment_name,
2145 sts_code
2146 FROM oks_report_templates
2147 WHERE template_set_id = l_template_set
2148 AND ( DECODE (process_code,
2149 'B', 'B',
2150 'X'
2151 ) = 'B'
2152 OR process_code = p_process_code
2153 )
2154 AND ( DECODE (applies_to,
2155 'B', 'B',
2156 'X'
2157 ) = 'B'
2158 OR applies_to = p_applies_to
2159 )
2160 AND report_period <> l_period
2161 AND report_duration <> 0
2162 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2163 AND NVL (end_date, SYSDATE);
2164
2165 CURSOR l_reportmatch_csr (
2166 p_template_set VARCHAR2,
2167 p_duration NUMBER,
2168 p_period VARCHAR2,
2169 p_process_code VARCHAR2,
2170 p_applies_to VARCHAR2
2171 )
2172 IS
2173 SELECT message_template_id,
2174 template_set_type,
2175 report_id,
2176 attachment_name,
2177 sts_code
2178 FROM oks_report_templates
2179 WHERE template_set_id = p_template_set
2180 AND report_duration = p_duration
2181 AND report_period = p_period
2182 AND ( DECODE (process_code,
2183 'B', 'B',
2184 'X'
2185 ) = 'B'
2186 OR process_code = p_process_code
2187 )
2188 AND ( DECODE (applies_to,
2189 'B', 'B',
2190 'X'
2191 ) = 'B'
2192 OR applies_to = p_applies_to
2193 )
2194 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2195 AND NVL (end_date, SYSDATE);
2196
2197 l_reportmatch_rec l_reportmatch_csr%ROWTYPE;
2198
2199 CURSOR l_duration_csr (
2200 p_template_set VARCHAR2,
2201 p_duration NUMBER,
2202 p_process_code VARCHAR2,
2203 p_applies_to VARCHAR2
2204 )
2205 IS
2206 SELECT message_template_id,
2207 template_set_type,
2208 report_id,
2209 attachment_name,
2210 sts_code
2211 FROM oks_report_templates
2212 WHERE template_set_id = p_template_set
2213 AND report_duration = p_duration
2214 AND ( DECODE (process_code,
2215 'B', 'B',
2216 'X'
2217 ) = 'B'
2218 OR process_code = p_process_code
2219 )
2220 AND ( DECODE (applies_to,
2221 'B', 'B',
2222 'X'
2223 ) = 'B'
2224 OR applies_to = p_applies_to
2225 )
2226 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2227 AND NVL (end_date, SYSDATE);
2228
2229 l_duration_rec l_duration_csr%ROWTYPE;
2230
2231 CURSOR l_user_email_csr (p_user_id NUMBER) IS
2232 SELECT source_email
2233 FROM jtf_rs_resource_extns
2234 WHERE user_id = p_user_id;
2235
2236 l_api_name CONSTANT VARCHAR2 (30) := 'validate_autoreminder_k';
2237 l_item_key wf_items.item_key%TYPE := '';
2238 l_online_yn VARCHAR2 (1) := 'N';
2239 l_process_code oks_report_templates_v.process_code%TYPE;
2240 l_applies_to oks_report_templates_v.applies_to%TYPE;
2241 l_salesrep_id NUMBER;
2242 l_salesrep_name VARCHAR2 (1000);
2243
2244 CURSOR csr_k_hdr_details IS
2245 SELECT DECODE (renewal_type_used,NULL,'N','R'), wf_item_key
2246 FROM oks_k_headers_b
2247 WHERE chr_id = p_chr_id;
2248
2249 CURSOR csr_xdo_template_name(p_attachment_template_id IN NUMBER) IS
2250 SELECT template_name
2251 FROM xdo_templates_vl
2252 WHERE template_id=p_attachment_template_id;
2253
2254 /*added for bug6956935*/
2255 Cursor l_chrv_csr(p_chr_id NUMBER) Is
2256 select 'Y'
2257 from oks_k_headers_b
2258 where chr_id = p_chr_id
2259 and RENEWAL_TYPE_USED = 'ERN'
2260 and date_accepted is not null;
2261
2262 l_cust_accept_flag VARCHAR2(1);
2263
2264 BEGIN
2265
2266 -- Contract is eligibile for a reminder or cancellation notice
2267 x_is_eligible := 'Y';
2268 x_return_status := g_ret_sts_success;
2269
2270 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2271 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
2272 'Entered with p_chr_id '||p_chr_id);
2273 END IF;
2274 -- Initialize message stack
2275 FND_MSG_PUB.initialize;
2276
2277 OPEN l_k_details_csr (TO_NUMBER (p_chr_id));
2278 FETCH l_k_details_csr INTO l_k_details_rec;
2279 IF l_k_details_csr%NOTFOUND THEN
2280 CLOSE l_k_details_csr;
2281 x_is_eligible := 'N';
2282 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_INVD_CONTRACT_ID');
2283 FND_MESSAGE.SET_TOKEN('HDR_ID',p_chr_id);
2284 FND_MSG_PUB.add;
2285 ELSE
2286 CLOSE l_k_details_csr;
2287 IF l_k_details_rec.contract_number_modifier IS NULL THEN
2288 l_concat_k_number := l_k_details_rec.contract_number;
2289 ELSE
2290 l_concat_k_number := l_k_details_rec.contract_number || ' - ' ||
2291 l_k_details_rec.contract_number_modifier;
2292 END IF;
2293 END IF;
2294
2295 -- STEP 1: Check for active workflow processes
2296 IF x_is_eligible = 'Y' THEN
2297 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2298 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2299 'okc_contract_pub.get_active_process(p_contract_number= '||
2300 l_k_details_rec.contract_number||
2301 ' p_contract_number_modifier ='||l_k_details_rec.contract_number_modifier||')');
2302 END IF;
2303 okc_contract_pub.get_active_process
2304 (
2305 p_api_version => 1.0,
2306 p_init_msg_list => 'Y',
2307 x_return_status => x_return_status,
2308 x_msg_count => x_msg_count,
2309 x_msg_data => x_msg_data,
2310 p_contract_number => l_k_details_rec.contract_number,
2311 p_contract_number_modifier => l_k_details_rec.contract_number_modifier,
2312 x_wf_name => l_wf_name,
2313 x_wf_process_name => l_wf_process_name,
2314 x_package_name => l_package_name,
2315 x_procedure_name => l_procedure_name,
2316 x_usage => l_usage
2317 );
2318 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2319 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2320 'okc_contract_pub.get_active_process(x_return_status= '||x_return_status||
2321 ' x_msg_count ='||x_msg_count||')');
2322 END IF;
2323
2324 /*BUG6956935*/
2325 /*changes made in order to check whether customer has accepted the renewal contract, if yes then we should not send auto reminder for that contract*/
2326 OPEN l_chrv_csr(to_number(p_chr_id));
2327 FETCH l_chrv_csr into l_cust_accept_flag;
2328 IF l_chrv_csr%NOTFOUND THEN
2329 l_cust_accept_flag := 'N';
2330 ELSE
2331 l_cust_accept_flag := 'Y';
2332 END IF;
2333 CLOSE l_chrv_csr;
2334
2335 -- If error, skip this contract and proceed with the next
2336 IF x_return_status <> g_ret_sts_success OR l_wf_name IS NOT NULL OR l_cust_accept_flag = 'Y' THEN
2337 x_is_eligible := 'N';
2338 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_K_IN_APPROVAL_PROCESS');
2339 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2340 FND_MSG_PUB.add;
2341 END IF;
2342 END IF;
2343
2344 -- STEP 2: Get template set
2345 IF x_is_eligible = 'Y' THEN
2346 -- get the workflow key
2347 OPEN csr_k_hdr_details;
2348 FETCH csr_k_hdr_details INTO l_applies_to, l_item_key;
2349 CLOSE csr_k_hdr_details;
2350
2351 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2352 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2353 'OKS_WF_K_PROCESS_PVT.is_online_k_yn(p_contract_id= '||p_chr_id||
2354 ' p_item_key ='||l_item_key||')');
2355 END IF;
2356 -- check if the current contract is ONLINE or MANUAL
2357 OKS_WF_K_PROCESS_PVT.is_online_k_yn
2358 (
2359 p_api_version => 1.0,
2360 p_init_msg_list => FND_API.G_FALSE,
2361 p_contract_id => p_chr_id ,
2362 p_item_key => l_item_key ,
2363 x_online_yn => l_online_yn ,
2364 x_return_status => x_return_status,
2365 x_msg_count => x_msg_count,
2366 x_msg_data => x_msg_data
2367 );
2368 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2369 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2370 'OKS_WF_K_PROCESS_PVT.is_online_k_yn(x_return_status= '||x_return_status||
2371 ' x_msg_count ='||x_msg_count||'l_online_yn '||l_online_yn||')');
2372 END IF;
2373 --- If any errors happen treat it as online K
2374 IF (x_return_status <> g_ret_sts_success) THEN
2375 l_online_yn := 'Y';
2376 x_return_status := g_ret_sts_success;
2377 END IF;
2378
2379 IF l_online_yn = 'Y' THEN
2380 l_process_code := 'O';
2381 ELSE
2382 l_process_code := 'M';
2383 END IF;
2384
2385 BEGIN
2386 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2387 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2388 'oks_renew_util_pub.get_template_set_id(p_contract_id= '||
2389 l_k_details_rec.ID||')');
2390 END IF;
2391
2392 l_template_set_id := oks_renew_util_pub.get_template_set_id(l_k_details_rec.ID);
2393
2394 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2395 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2396 'oks_renew_util_pub.get_template_set_id(x_template_set_id= '||
2397 l_template_set_id||')');
2398 END IF;
2399 IF l_template_set_id IS NULL THEN
2400 x_is_eligible := 'N';
2401 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_TEMPLATE_SET');
2402 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2403 FND_MSG_PUB.add;
2404 END IF;
2405 EXCEPTION
2406 WHEN OTHERS THEN
2407 x_is_eligible := 'N';
2408 x_return_status := g_ret_sts_unexp_error;
2409 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2410 END;
2411 END IF;
2412
2413 -- STEP 3: Check if the contract qualifies for a reminder / cancelation notice
2414 IF l_template_set_id IS NOT NULL AND x_is_eligible = 'Y' THEN
2415 l_duration := TRUNC (SYSDATE) - TRUNC (l_k_details_rec.start_date);
2416 l_period := oks_time_measures_pub.get_uom_code ('DAY', 1);
2417
2418 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2419 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2420 'l_duration: ' ||l_duration||' l_period: '||l_period );
2421 END IF;
2422
2423 -- If there is an error getting the duration skip this contract
2424 IF l_duration <> 0 THEN
2425 -- Check if we have a report having the same duration, period
2426 -- in the Template set. If foudn pick the report for generating
2427 -- the email message
2428 OPEN l_reportmatch_csr (p_template_set => l_template_set_id,
2429 p_duration => l_duration,
2430 p_period => l_period,
2431 p_process_code => l_process_code,
2432 p_applies_to => l_applies_to
2433 );
2434 FETCH l_reportmatch_csr INTO l_reportmatch_rec;
2435 IF l_reportmatch_csr%FOUND THEN
2436 x_cover_id := l_reportmatch_rec.message_template_id;
2437 x_quote_id := l_reportmatch_rec.report_id;
2438 x_attachment_name := l_reportmatch_rec.attachment_name;
2439 l_report_type := l_reportmatch_rec.template_set_type;
2440 x_status := l_reportmatch_rec.sts_code;
2441
2442 CLOSE l_reportmatch_csr;
2443 ELSE -- Straight match not found
2444 CLOSE l_reportmatch_csr;
2445
2446 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2447 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2448 'Exact match not found; continue search by converting ');
2449 END IF;
2450 -- Now trying to convert and look for match
2451 FOR l_reports_rec IN l_reports_csr (l_template_set_id,
2452 l_period,
2453 l_process_code,
2454 l_applies_to
2455 )
2456 LOOP
2457 IF l_reports_rec.report_duration < 0 THEN
2458 l_temp_duration := oks_time_measures_pub.get_target_qty
2459 (
2460 p_start_date => TRUNC(SYSDATE),
2461 p_source_qty => ABS(l_reports_rec.report_duration),
2462 p_source_uom => l_reports_rec.report_period,
2463 p_target_uom => l_period,
2464 p_round_dec => 18
2465 );
2466 l_temp_duration := (l_temp_duration - 1) * -1;
2467 ELSE
2468 l_temp_duration := oks_time_measures_pub.get_target_qty
2469 (
2470 p_start_date => TRUNC(l_k_details_rec.start_date),
2471 p_source_qty => l_reports_rec.report_duration,
2472 p_source_uom => l_reports_rec.report_period,
2473 p_target_uom => l_period,
2474 p_round_dec => 18
2475 );
2476 l_temp_duration := l_temp_duration - 1;
2477 END IF;
2478
2479 IF (l_duration = l_temp_duration) THEN
2480 x_cover_id := l_reports_rec.message_template_id;
2481 x_quote_id := l_reports_rec.report_id;
2482 x_attachment_name := l_reports_rec.attachment_name;
2483 l_report_type := l_reports_rec.template_set_type;
2484 x_status := l_reports_rec.sts_code;
2485 EXIT;
2486 END IF;
2487 END LOOP; -- Inner loop for iterating thru report templates
2488 END IF; -- Report Match cursor IF
2489 -- Special handler for 0 duration
2490 ELSIF l_duration = 0 THEN
2491 OPEN l_duration_csr (l_template_set_id,
2492 l_duration,
2493 l_process_code,
2494 l_applies_to
2495 );
2496 FETCH l_duration_csr INTO l_duration_rec;
2497 IF l_duration_csr%FOUND THEN
2498 x_cover_id := l_duration_rec.message_template_id;
2499 x_quote_id := l_duration_rec.report_id;
2500 x_attachment_name := l_duration_rec.attachment_name;
2501 l_report_type := l_duration_rec.template_set_type;
2502 x_status := l_duration_rec.sts_code;
2503 END IF;
2504 CLOSE l_duration_csr;
2505 END IF; -- Get Duration Status check
2506 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2507 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2508 'x_cover_id: ' ||x_cover_id);
2509 END IF;
2510
2511 IF NVL(x_quote_id, x_cover_id) IS NULL THEN
2512 x_is_eligible := 'N';
2513 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_QUAL_NOTICE');
2514 FND_MESSAGE.SET_TOKEN('K_NUMBER',l_concat_k_number);
2515 FND_MSG_PUB.add;
2516 END IF;
2517 END IF;
2518
2519 -- STEP 4: Get Quote to contact email address
2520 IF NVL(x_quote_id, x_cover_id) IS NOT NULL AND x_is_eligible = 'Y' THEN
2521 BEGIN
2522 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2523 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.before',
2524 'oks_auto_reminder.get_qto_email(p_chr_id= '||
2525 l_k_details_rec.ID||')');
2526 END IF;
2527 oks_auto_reminder.get_qto_email
2528 (
2529 p_chr_id => l_k_details_rec.ID,
2530 x_qto_email => x_qto_email
2531 );
2532 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2533 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE||l_api_name||'.external_call.after',
2534 'oks_auto_reminder.get_qto_email(x_qto_email= '||
2535 x_qto_email||')');
2536 END IF;
2537 IF x_qto_email IS NULL OR x_qto_email = okc_api.g_miss_char THEN
2538 x_is_eligible := 'N';
2539 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_NO_QTO_EMAIL');
2540 FND_MSG_PUB.add;
2541 END IF;
2542 EXCEPTION
2543 WHEN OTHERS THEN
2544 x_is_eligible := 'N';
2545 x_return_status := g_ret_sts_unexp_error;
2546 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2547 END;
2548 END IF;
2549
2550 -- STEP 5: Get Sender email address
2551 IF x_is_eligible = 'Y' THEN
2552 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
2553 fnd_log.STRING(fnd_log.level_event,g_module ||l_api_name ||
2554 '.external_call.before',
2555 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(p_chr_id=' ||
2556 l_k_details_rec.ID ||')');
2557 END IF;
2558 oks_renew_contract_pvt.get_user_name
2559 (
2560 p_api_version => 1.0,
2561 p_init_msg_list => g_false,
2562 x_return_status => x_return_status,
2563 x_msg_count => x_msg_count,
2564 x_msg_data => x_msg_data,
2565 p_chr_id => l_k_details_rec.ID,
2566 p_hdesk_user_id => NULL,
2567 x_user_id => l_salesrep_id,
2568 x_user_name => l_salesrep_name
2569 );
2570 IF fnd_log.level_event >= fnd_log.g_current_runtime_level THEN
2571 fnd_log.STRING(fnd_log.level_event,g_module ||l_api_name ||
2572 '.external_call.after',
2573 'OKS_RENEW_CONTRACT_PVT.GET_USER_NAME(x_return_status= ' ||
2574 x_return_status ||' x_msg_count ='||x_msg_count||')');
2575 fnd_log.STRING (fnd_log.level_event,g_module ||l_api_name ||
2576 '.external_call.after',' x_user_id =' ||l_salesrep_id ||
2577 ' x_user_name =' ||l_salesrep_name);
2578 END IF;
2579
2580 IF x_return_status = g_ret_sts_success AND
2581 l_salesrep_id IS NOT NULL THEN
2582 OPEN l_user_email_csr (l_salesrep_id);
2583 FETCH l_user_email_csr INTO x_sender;
2584 CLOSE l_user_email_csr;
2585 END IF;
2586 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2587 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
2588 'x_sender: ' ||x_sender);
2589 END IF;
2590 IF x_sender IS NULL THEN
2591 x_is_eligible := 'N';
2592 FND_MESSAGE.SET_NAME(G_APP_NAME,'OKS_EMAIL_NOT_FOUND');
2593 FND_MSG_PUB.add;
2594 END IF;
2595 END IF;
2596
2597 -- STEP 6: Get email subject
2598 IF x_sender IS NOT NULL AND x_is_eligible = 'Y' THEN
2599 IF l_report_type = 'RMN' THEN
2600 fnd_message.set_name ('OKS','OKS_AREM_RNT_SUBJECT');
2601 ELSE
2602 fnd_message.set_name ('OKS','OKS_AREM_CNT_SUBJECT');
2603 END IF;
2604 fnd_message.set_token('TOKEN1',l_concat_k_number);
2605 x_subject := fnd_message.get;
2606 END IF;
2607
2608 -- if attachment name is NULL then get template name from xdo_templates_vl
2609 IF x_is_eligible = 'Y' AND x_quote_id IS NOT NULL AND
2610 x_attachment_name IS NULL THEN
2611 OPEN csr_xdo_template_name(p_attachment_template_id => x_quote_id);
2612 FETCH csr_xdo_template_name INTO x_attachment_name;
2613 CLOSE csr_xdo_template_name;
2614 END IF;
2615 IF x_is_eligible = 'N' THEN
2616 x_msg_data := get_fnd_message;
2617 END IF;
2618
2619 EXCEPTION
2620 WHEN FND_API.G_EXC_ERROR THEN
2621 x_is_eligible := 'N';
2622 x_msg_data := get_fnd_message;
2623 WHEN OTHERS THEN
2624 x_is_eligible := 'N';
2625 x_msg_data := 'Exception: ' || SQLERRM;
2626 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2627 END validate_autoreminder_k;
2628
2629 /*added for bug10018402*/
2630 /*following function is used to issue explicit commit once we make a call to
2631 fnd_profile.save api, this has been added as per the AOL team suggestion
2632 given in bug10022142*/
2633
2634 FUNCTION set_user_home_page(l_value varchar2, l_level_value number)
2635 RETURN boolean AS
2636 PRAGMA AUTONOMOUS_TRANSACTION;
2637
2638 l_api_name CONSTANT VARCHAR2(30) := 'set_user_home_page';
2639 l_return_value BOOLEAN;
2640 BEGIN
2641
2642 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2643 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2644 'Entered '||G_PKG_NAME ||'.'||l_api_name );
2645 END IF;
2646
2647 l_return_value := fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
2648 x_value => l_value,
2649 x_level_name => 'USER',
2650 x_level_value => to_char(l_level_value)
2651 );
2652
2653 IF l_return_value THEN
2654 COMMIT;
2655 END IF;
2656 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,G_MODULE||l_api_name,
2658 'Leaving '||G_PKG_NAME ||'.'||l_api_name);
2659 END IF;
2660 RETURN l_return_value;
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2664 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
2665 'Entered exception section'||G_PKG_NAME ||'.'||l_api_name );
2666 END IF;
2667 RETURN FALSE;
2668 END;
2669
2670 /*
2671
2672 PROCEDURE create_sso_user
2673 Pseudo Logic:
2674 -------------
2675
2676 Step 1: Get the person_party_id from oks_k_headers_b
2677
2678 IF oks_k_headers_b.person_party_id IS NULL THEN
2679 Fetch the person_party_id for quote to contact id
2680 UPDATE oks_k_headers_b record with the person_party_id for quote to contact id
2681
2682 Step 2: Check if record exists in fnd_user for the above person_party_id (filter expired records here)
2683 -- 1 or more Record Exists
2684 If record found then
2685 take the first hit record and put the user name and password in the email notification
2686 check if this user has the responsibility' Electronic Renewal'
2687 If yes then return
2688 else
2689 add responsibility and return
2690 end if;
2691
2692 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
2693
2694 CASE A: Record NOT Found in fnd_user
2695
2696 Call FND_USER_PKG.TestUserName
2697 --@ TestUserName() returns:
2698 --@ USER_OK_CREATE constant pls_integer := 0;
2699 --@ USER_INVALID_NAME constant pls_integer := 1;
2700 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
2701 --@ USER_SYNCHED constant pls_integer := 3;
2702 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
2703
2704 IF l_test_user IN (0,3) THEN
2705 CALL FND_USER_PKG.CreateUserIdParty
2706 FND_USER_RESP_GROUPS_API.insert_assignment with responsibility as 'OKS_ERN_WEB'
2707
2708 ELSE -- l_test_user <> 0 ,3
2709 -- error, raise exception
2710 RAISE FND_API.G_EXC_ERROR;
2711 END IF;
2712
2713
2714 CASE B: Record Found in fnd_user
2715
2716 Check if fnd_user is Valid or expired
2717 Case Valid
2718
2719 Check person_party_id of fnd_user
2720 CASE 1: fnd_user person_party_id is NULL
2721 UPDATE fnd_user record with person_party_id from step 1 above
2722 CASE 2: person_party_id of fnd_user DOES NOT MATCH the person_party_id from step 1 above
2723 (logic introduced as part of bugfix for 58983305)
2724 CASE i: If the value of the profile option OKS Overide SSO Behaviour is Y then
2725 Update the person_party_id of oks_k_headers_b and continue publishing
2726 CASE ii:If the value of the profile option OKS Overide SSO Behaviour is N
2727 RAISE error here
2728 CASE 3: person_party_id of fnd_user MATCHES the person_party_id from step 1 above
2729 we are fine, do nothing
2730
2731 Case Expired
2732
2733 fnd_user exists and is expired , raise exception
2734
2735
2736 */
2737 PROCEDURE create_sso_user (
2738 p_user_name IN VARCHAR2,
2739 p_contract_id IN NUMBER,
2740 x_user_name OUT NOCOPY VARCHAR2,
2741 x_password OUT NOCOPY VARCHAR2,
2742 x_return_status OUT NOCOPY VARCHAR2,
2743 x_msg_data OUT NOCOPY VARCHAR2,
2744 x_msg_count OUT NOCOPY NUMBER
2745 )
2746 AS
2747 l_api_name CONSTANT VARCHAR2 (30)
2748 := 'create_sso_user';
2749 l_person_party_id oks_k_headers_b.person_party_id%TYPE;
2750 l_fnd_person_party_id fnd_user.person_party_id%TYPE;
2751 l_start_date fnd_user.start_date%TYPE;
2752 l_end_date fnd_user.end_date%TYPE;
2753 l_test_user PLS_INTEGER;
2754 l_responsibility_id fnd_responsibility_vl.responsibility_id%TYPE;
2755 l_security_grp_id fnd_security_groups.security_group_id%TYPE;
2756 l_return_value BOOLEAN;
2757 l_user_id fnd_user.user_id%TYPE;
2758 l_quote_to_contact_id oks_k_headers_b.quote_to_contact_id%TYPE;
2759
2760 l_suggested_user_name fnd_user.user_name%TYPE;
2761
2762 CURSOR csr_person_party_id
2763 IS
2764 SELECT ks.person_party_id,
2765 ks.quote_to_contact_id
2766 FROM oks_k_headers_b ks
2767 WHERE ks.chr_id = p_contract_id;
2768
2769 CURSOR csr_qtc_person_party_id (
2770 p_quote_to_contact_id IN NUMBER
2771 )
2772 IS
2773 SELECT hzp.party_id
2774 FROM hz_cust_account_roles car,
2775 hz_relationships rln,
2776 hz_parties hzp
2777 WHERE car.cust_account_role_id = p_quote_to_contact_id
2778 AND car.party_id = rln.party_id
2779 AND rln.subject_id = hzp.party_id
2780 AND car.role_type = 'CONTACT'
2781 AND rln.directional_flag = 'F'
2782 AND rln.content_source_type = 'USER_ENTERED';
2783
2784 CURSOR csr_check_fnd_user_exists (
2785 p_person_party_id IN NUMBER
2786 )
2787 IS
2788 SELECT user_id,
2789 user_name,
2790 encrypted_user_password
2791 FROM fnd_user
2792 WHERE SYSDATE BETWEEN start_date AND NVL (end_date, SYSDATE +
2793 1)
2794 AND person_party_id = p_person_party_id;
2795
2796 CURSOR csr_chk_qtc_fnd_user (
2797 p_user_name IN VARCHAR2
2798 )
2799 IS
2800 SELECT f.person_party_id,
2801 f.start_date,
2802 f.end_date,
2803 f.encrypted_user_password
2804 FROM fnd_user f
2805 WHERE f.user_name = p_user_name;
2806
2807 CURSOR l_resp_csr (
2808 p_resp_key VARCHAR2
2809 )
2810 IS
2811 SELECT responsibility_id
2812 FROM fnd_responsibility
2813 WHERE responsibility_key = p_resp_key
2814 AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
2815 AND NVL (end_date, SYSDATE);
2816
2817 CURSOR l_security_grp_csr (
2818 p_security_grp_key VARCHAR2
2819 )
2820 IS
2821 SELECT security_group_id
2822 FROM fnd_security_groups
2823 WHERE security_group_key = p_security_grp_key;
2824
2825 CURSOR csr_get_per_party_name (p_party_id IN NUMBER) IS
2826 SELECT party_name
2827 FROM hz_parties
2828 WHERE party_id = p_party_id;
2829
2830 l_oks_per_party_name hz_parties.party_name%TYPE;
2831 l_fnd_per_party_name hz_parties.party_name%TYPE;
2832
2833 CURSOR csr_get_party_name (p_party_id IN NUMBER) IS
2834 SELECT p.party_name , p.party_id
2835 FROM hz_relationships r , hz_parties p
2836 WHERE p.party_id = r.object_id
2837 AND r.subject_type='PERSON'
2838 AND r.object_type='ORGANIZATION'
2839 AND r.subject_id = p_party_id;
2840
2841 l_oks_party_name hz_parties.party_name%TYPE;
2842 l_fnd_party_name hz_parties.party_name%TYPE;
2843 l_oks_party_id hz_parties.party_id%TYPE;
2844 l_fnd_party_id hz_parties.party_id%TYPE;
2845 /*bug7207391*/
2846 l_result VARCHAR2(10);
2847 v_counter NUMBER := 1;
2848 BEGIN
2849 -- start debug log
2850 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2851 THEN
2852 fnd_log.STRING (fnd_log.level_procedure,
2853 g_module ||
2854 l_api_name,
2855 '100: Entered ' ||
2856 g_pkg_name ||
2857 '.' ||
2858 l_api_name
2859 );
2860 fnd_log.STRING (fnd_log.level_procedure,
2861 g_module ||
2862 l_api_name,
2863 '100: Parameter p_user_name : ' ||
2864 p_user_name
2865 );
2866 fnd_log.STRING (fnd_log.level_procedure,
2867 g_module ||
2868 l_api_name,
2869 '100: p_contract_id : ' ||
2870 p_contract_id
2871 );
2872 END IF;
2873
2874 -- log file
2875 fnd_file.put_line (fnd_file.LOG, ' ');
2876 fnd_file.put_line
2877 (fnd_file.LOG,
2878 '---------------------------------------------------------- ');
2879 fnd_file.put_line (fnd_file.LOG, 'Entered create_sso_user');
2880 fnd_file.put_line (fnd_file.LOG, 'Parameters ');
2881 fnd_file.put_line (fnd_file.LOG, 'p_user_name : ' ||
2882 p_user_name);
2883 fnd_file.put_line (fnd_file.LOG, 'p_contract_id : ' ||
2884 p_contract_id);
2885 fnd_file.put_line
2886 (fnd_file.LOG,
2887 '---------------------------------------------------------- ');
2888 fnd_file.put_line (fnd_file.LOG, ' ');
2889 -- Initialize API return status to success
2890 x_return_status := fnd_api.g_ret_sts_success;
2891
2892 -- Get the Person Party Id and quote_to_contact_id from oks_k_headers_b
2893 OPEN csr_person_party_id;
2894
2895 FETCH csr_person_party_id
2896 INTO l_person_party_id,
2897 l_quote_to_contact_id;
2898
2899 CLOSE csr_person_party_id;
2900
2901 -- debug log
2902 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2903 THEN
2904 fnd_log.STRING (fnd_log.level_procedure,
2905 g_module ||
2906 l_api_name,
2907 '110: l_person_party_id : ' ||
2908 l_person_party_id
2909 );
2910 fnd_log.STRING (fnd_log.level_procedure,
2911 g_module ||
2912 l_api_name,
2913 '110: l_quote_to_contact_id : ' ||
2914 l_quote_to_contact_id
2915 );
2916 END IF;
2917
2918 -- log file
2919 fnd_file.put_line (fnd_file.LOG,
2920 'OKS l_person_party_id : ' ||
2921 l_person_party_id);
2922 fnd_file.put_line (fnd_file.LOG,
2923 'OKS l_quote_to_contact_id : ' ||
2924 l_quote_to_contact_id);
2925
2926 -- if the person_party_id is NULL then get person_party_id for quote_to_contact_id
2927 IF l_person_party_id IS NULL
2928 THEN
2929 OPEN csr_qtc_person_party_id
2930 (p_quote_to_contact_id => l_quote_to_contact_id);
2931
2932 FETCH csr_qtc_person_party_id
2933 INTO l_person_party_id;
2934
2935 CLOSE csr_qtc_person_party_id;
2936
2937 -- debug log
2938 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2939 THEN
2940 fnd_log.STRING
2941 (fnd_log.level_procedure,
2942 g_module ||
2943 l_api_name,
2944 '120: l_person_party_id for quote to contact: ' ||
2945 l_person_party_id
2946 );
2947 END IF;
2948
2949 -- log file
2950 fnd_file.put_line (fnd_file.LOG,
2951 'HZ l_person_party_id : ' ||
2952 l_person_party_id);
2953
2954 -- update the oks_k_headers_b with the person_party_id
2955 UPDATE oks_k_headers_b
2956 SET person_party_id = l_person_party_id,
2957 object_version_number = object_version_number +
2958 1,
2959 last_update_date = SYSDATE,
2960 last_updated_by = fnd_global.user_id,
2961 last_update_login = fnd_global.login_id
2962 WHERE chr_id = p_contract_id;
2963
2964 -- bump up the minor version number
2965 UPDATE okc_k_vers_numbers
2966 SET minor_version = minor_version +
2967 1,
2968 object_version_number = object_version_number +
2969 1,
2970 last_update_date = SYSDATE,
2971 last_updated_by = fnd_global.user_id,
2972 last_update_login = fnd_global.login_id
2973 WHERE chr_id = p_contract_id;
2974 END IF; -- l_person_party_id is null in oks_k_headers_b
2975
2976 -- Check if record exists in fnd_user for the above person_party_id
2977 OPEN csr_check_fnd_user_exists (p_person_party_id => l_person_party_id);
2978
2979 FETCH csr_check_fnd_user_exists
2980 INTO l_user_id,
2981 x_user_name,
2982 x_password;
2983
2984 CLOSE csr_check_fnd_user_exists;
2985
2986 -- debug log
2987 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2988 THEN
2989 fnd_log.STRING (fnd_log.level_procedure,
2990 g_module ||
2991 l_api_name,
2992 '130: l_user_id : ' ||
2993 l_user_id
2994 );
2995 fnd_log.STRING (fnd_log.level_procedure,
2996 g_module ||
2997 l_api_name,
2998 '130: x_user_name : ' ||
2999 x_user_name
3000 );
3001 fnd_log.STRING (fnd_log.level_procedure,
3002 g_module ||
3003 l_api_name,
3004 '130: x_password : ' ||
3005 x_password
3006 );
3007 END IF;
3008
3009 -- log file
3010 fnd_file.put_line (fnd_file.LOG,
3011 'Check FND User record for person_party_id : ' ||
3012 l_person_party_id);
3013 fnd_file.put_line (fnd_file.LOG, 'l_user_id : ' ||
3014 l_user_id);
3015 fnd_file.put_line (fnd_file.LOG, 'x_user_name : ' ||
3016 x_user_name);
3017
3018 -- Check if valid fnd_user found
3019 IF x_user_name IS NOT NULL
3020 THEN
3021 x_password := '******';
3022 -- log file
3023 fnd_file.put_line (fnd_file.LOG,
3024 'Found FND User record for person_party_id : ' ||
3025 l_person_party_id);
3026 -- Bug 4650639
3027 -- FND USER found, check if this user has the responsibility G_ERN_WEB_RESPONSIBILITY
3028 fnd_file.put_line (fnd_file.LOG,
3029 'Check if the user has ERN responsibility');
3030
3031 OPEN l_resp_csr (p_resp_key => g_ern_web_responsibility);
3032
3033 FETCH l_resp_csr
3034 INTO l_responsibility_id;
3035
3036 CLOSE l_resp_csr;
3037
3038 OPEN l_security_grp_csr ('STANDARD');
3039
3040 FETCH l_security_grp_csr
3041 INTO l_security_grp_id;
3042
3043 CLOSE l_security_grp_csr;
3044
3045 IF fnd_user_resp_groups_api.assignment_exists (l_user_id,
3046 l_responsibility_id,
3047 515,
3048 l_security_grp_id
3049 )
3050 THEN
3051 -- user has the ERN responsibility
3052 fnd_file.put_line (fnd_file.LOG,
3053 'user : ' ||
3054 x_user_name ||
3055 ' has ERN responsibility');
3056 RETURN;
3057 ELSE
3058 -- add responsibility and return
3059 -- log file
3060 fnd_file.put_line (fnd_file.LOG,
3061 'user : ' ||
3062 x_user_name ||
3063 ' DOES NOT HAVE ERN responsibility');
3064 fnd_file.put_line (fnd_file.LOG,
3065 'assign responsibility to user created');
3066 fnd_file.put_line (fnd_file.LOG,
3067 'l_responsibility_id : ' ||
3068 l_responsibility_id);
3069 fnd_file.put_line (fnd_file.LOG,
3070 'l_security_grp_id : ' ||
3071 l_security_grp_id);
3072 fnd_user_resp_groups_api.insert_assignment
3073 (user_id => l_user_id,
3074 responsibility_id => l_responsibility_id,
3075 responsibility_application_id => 515,
3076 security_group_id => l_security_grp_id,
3077 description => 'Electronic renewals User',
3078 start_date => SYSDATE,
3079 end_date => NULL
3080 );
3081 /*modified for bug10018402*/
3082 l_return_value := set_user_home_page('PHP', l_user_id);
3083
3084 /* fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
3085 x_value => 'PHP',
3086 x_level_name => 'USER',
3087 x_level_value => TO_CHAR
3088 (l_user_id)
3089 );*/
3090
3091 IF l_return_value
3092 THEN
3093 RETURN;
3094 ELSE
3095 -- error in fnd_profile.save
3096 -- debug log
3097 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3098 )
3099 THEN
3100 fnd_log.STRING (fnd_log.level_procedure,
3101 g_module ||
3102 l_api_name,
3103 '135: error in fnd_profile.save : '
3104 );
3105 END IF;
3106
3107 -- log file
3108 fnd_file.put_line (fnd_file.LOG, 'error in fnd_profile.save ');
3109 fnd_file.put_line (fnd_file.LOG, SQLERRM);
3110 RAISE fnd_api.g_exc_error;
3111 END IF; -- l_return_value
3112 END IF; -- assignment_exists
3113 END IF; -- x_user_name IS NOT NULL THEN
3114
3115 -- check in fnd_user if record exists with the user name as quote to contact email
3116 OPEN csr_chk_qtc_fnd_user (p_user_name => UPPER
3117 (TRIM
3118 (p_user_name)));
3119
3120 FETCH csr_chk_qtc_fnd_user
3121 INTO l_fnd_person_party_id,
3122 l_start_date,
3123 l_end_date,
3124 x_password;
3125
3126 -- debug log
3127 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3128 THEN
3129 fnd_log.STRING (fnd_log.level_procedure,
3130 g_module ||
3131 l_api_name,
3132 '140: l_fnd_person_party_id : ' ||
3133 l_fnd_person_party_id
3134 );
3135 fnd_log.STRING (fnd_log.level_procedure,
3136 g_module ||
3137 l_api_name,
3138 '140: l_start_date : ' ||
3139 l_start_date
3140 );
3141 fnd_log.STRING (fnd_log.level_procedure,
3142 g_module ||
3143 l_api_name,
3144 '140: l_end_date : ' ||
3145 l_end_date
3146 );
3147 fnd_log.STRING (fnd_log.level_procedure,
3148 g_module ||
3149 l_api_name,
3150 '140: x_password : ' ||
3151 x_password
3152 );
3153 END IF;
3154
3155 -- log file
3156 fnd_file.put_line (fnd_file.LOG,
3157 'Check FND User record for user name : ' ||
3158 p_user_name);
3159 fnd_file.put_line (fnd_file.LOG,
3160 'l_fnd_person_party_id : ' ||
3161 l_fnd_person_party_id);
3162 fnd_file.put_line (fnd_file.LOG, 'l_start_date : ' ||
3163 l_start_date);
3164 fnd_file.put_line (fnd_file.LOG, 'l_end_date : ' ||
3165 l_end_date);
3166
3167 IF csr_chk_qtc_fnd_user%NOTFOUND
3168 THEN
3169 -- create a NEW FND USER
3170 -- Call the testUserName pkg
3171 --@ TestUserName() returns:
3172 --@ USER_OK_CREATE constant pls_integer := 0;
3173 --@ USER_INVALID_NAME constant pls_integer := 1;
3174 --@ USER_EXISTS_IN_FND constant pls_integer := 2;
3175 --@ USER_SYNCHED constant pls_integer := 3;
3176 --@ USER_EXISTS_NO_LINK_ALLOWED constant pls_integer := 4;
3177
3178 -- log file
3179 fnd_file.put_line
3180 (fnd_file.LOG,
3181 'Getting Suggested username, calling UMX_PUB.get_suggested_username');
3182
3183 -- Call API UMX_PUB.get_suggested_username to get the suggested user name
3184 UMX_PUB.get_suggested_username(p_person_party_id => l_person_party_id,
3185 x_suggested_username => l_suggested_user_name);
3186
3187 -- log file
3188 fnd_file.put_line
3189 (fnd_file.LOG,
3190 'Suggested username : '||l_suggested_user_name);
3191
3192 -- debug log
3193 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3194 THEN
3195 fnd_log.STRING (fnd_log.level_procedure,
3196 g_module ||
3197 l_api_name,
3198 '145: l_suggested_user_name from UMX_PUB.get_suggested_username : ' ||
3199 l_suggested_user_name
3200 );
3201 END IF;
3202
3203 IF l_suggested_user_name IS NULL THEN
3204 -- suggested name same as email address
3205 l_suggested_user_name := UPPER(TRIM(p_user_name));
3206 END IF;
3207
3208 -- debug log
3209 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3210 THEN
3211 fnd_log.STRING (fnd_log.level_procedure,
3212 g_module ||
3213 l_api_name,
3214 '146: l_suggested_user_name : ' ||
3215 l_suggested_user_name
3216 );
3217 END IF;
3218
3219 -- log file
3220 fnd_file.put_line
3221 (fnd_file.LOG,
3222 'Creating New user, calling FND_USER_PKG.TestUserName for username :'||l_suggested_user_name);
3223 l_test_user :=
3224 fnd_user_pkg.testusername
3225 (x_user_name => l_suggested_user_name);
3226
3227 -- debug log
3228 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3229 THEN
3230 fnd_log.STRING (fnd_log.level_procedure,
3231 g_module ||
3232 l_api_name,
3233 '150: l_test_user : ' ||
3234 l_test_user
3235 );
3236 END IF;
3237
3238 -- log file
3239 fnd_file.put_line
3240 (fnd_file.LOG,
3241 'After calling FND_USER_PKG.TestUserName l_test_user : ' ||
3242 l_test_user);
3243
3244 IF l_test_user IN (0, 3)
3245 THEN
3246 IF l_test_user = 0
3247 THEN
3248 -- ok to create a new user
3249 /*
3250 x_password := dbms_random.string('l', (NVL(FND_PROFILE.value('SIGNON_PASSWORD_LENGTH'),7)-3))||
3251 round(dbms_random.value(100,999));
3252 */
3253
3254 -- log file
3255 fnd_file.put_line
3256 (fnd_file.LOG,
3257 'ok to create a new user, calling FND_CRYPTO.randombytes');
3258 x_password :=
3259 fnd_crypto.randomstring
3260 (NVL (fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
3261 4));
3262
3263 /*modified for bug7207391*/
3264 /*IF Nvl(fnd_profile.value('SIGNON_PASSWORD_HARD_TO_GUESS'),'N') = 'Y' THEN*/
3265 -- loop till password clears the validations
3266 l_result := FND_WEB_SEC.validate_password (l_suggested_user_name, x_password);
3267 WHILE ((l_result <> 'Y') AND (v_counter <= 100)) LOOP
3268
3269 -- incrementing the counter
3270 v_counter := v_counter + 1;
3271
3272 -- generate password
3273 x_password :=
3274 fnd_crypto.randomstring
3275 (NVL (fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
3276 4));
3277
3278 fnd_file.put_line
3279 (fnd_file.LOG,
3280 'validating password, calling FND_WEB_SEC.validate_password');
3281
3282 l_result := FND_WEB_SEC.validate_password (l_suggested_user_name, x_password);
3283
3284 IF ( v_counter = 100 ) THEN
3285 IF ( l_result <> 'Y' ) THEN
3286 -- Throw exception as even though generated password 100 times, but
3287 -- cannot pass validation criteria
3288 fnd_file.put_line(fnd_file.LOG,
3289 'password validation failed, raised user defined exception');
3290 raise_application_error (-20000,'Could not generated password automatically which satisfies validation requirements.');
3291 END IF;
3292 END IF;
3293 END LOOP;
3294 /* END IF;*/
3295 /*end of modification for bug7207391*/
3296
3297 l_user_id :=
3298 fnd_user_pkg.createuseridparty
3299 (x_user_name => l_suggested_user_name,
3300 x_owner => 'SEED',
3301 x_unencrypted_password => x_password,
3302 x_description => 'Electronic renewals User',
3303 x_email_address => UPPER
3304 (TRIM
3305 (p_user_name)),
3306 x_person_party_id => l_person_party_id
3307 );
3308 x_user_name := l_suggested_user_name;
3309 -- log file
3310 fnd_file.put_line
3311 (fnd_file.LOG,
3312 'FND_USER_PKG.CreateUserIdParty l_user_id : ' ||
3313 l_user_id);
3314 ELSE -- l_test_user = 3
3315 -- USER_SYNCHED constant pls_integer := 3;
3316 -- Call the FND_USER_PKG.CreateUserIdParty WITHOUT password as password exists in OID
3317 -- in Notification, put the password as '******'
3318
3319 -- log file
3320 fnd_file.put_line
3321 (fnd_file.LOG,
3322 'USER_SYNCHED , calling FND_USER_PKG.CreateUserIdParty');
3323 l_user_id :=
3324 fnd_user_pkg.createuseridparty
3325 (x_user_name => l_suggested_user_name,
3326 x_owner => 'SEED',
3327 x_description => 'Electronic renewals User',
3328 x_email_address => UPPER
3329 (TRIM
3330 (p_user_name)),
3331 x_person_party_id => l_person_party_id
3332 );
3333 x_user_name := l_suggested_user_name;
3334 x_password := '******';
3335 -- log file
3336 fnd_file.put_line
3337 (fnd_file.LOG,
3338 'FND_USER_PKG.CreateUserIdParty l_user_id : ' ||
3339 l_user_id);
3340 END IF; -- l_test_user = 0 or 3
3341
3342 -- debug log
3343 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3344 THEN
3345 fnd_log.STRING (fnd_log.level_procedure,
3346 g_module ||
3347 l_api_name,
3348 '160: l_user_id : ' ||
3349 l_user_id
3350 );
3351 fnd_log.STRING (fnd_log.level_procedure,
3352 g_module ||
3353 l_api_name,
3354 '160: x_user_name : ' ||
3355 x_user_name
3356 );
3357 fnd_log.STRING (fnd_log.level_procedure,
3358 g_module ||
3359 l_api_name,
3360 '160: x_password : ' ||
3361 x_password
3362 );
3363 END IF;
3364
3365 -- log file
3366 fnd_file.put_line (fnd_file.LOG, 'l_user_id : ' ||
3367 l_user_id);
3368 fnd_file.put_line (fnd_file.LOG, 'x_user_name : ' ||
3369 x_user_name);
3370
3371 IF l_user_id IS NOT NULL
3372 THEN
3373 -- assign responsibility to user created
3374 OPEN l_resp_csr (p_resp_key => g_ern_web_responsibility);
3375
3376 FETCH l_resp_csr
3377 INTO l_responsibility_id;
3378
3379 CLOSE l_resp_csr;
3380
3381 OPEN l_security_grp_csr ('STANDARD');
3382
3383 FETCH l_security_grp_csr
3384 INTO l_security_grp_id;
3385
3386 CLOSE l_security_grp_csr;
3387
3388 -- debug log
3389 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3390 )
3391 THEN
3392 fnd_log.STRING (fnd_log.level_procedure,
3393 g_module ||
3394 l_api_name,
3395 '170: l_responsibility_id : ' ||
3396 l_responsibility_id
3397 );
3398 fnd_log.STRING (fnd_log.level_procedure,
3399 g_module ||
3400 l_api_name,
3401 '170: l_security_grp_id : ' ||
3402 l_security_grp_id
3403 );
3404 END IF;
3405
3406 -- log file
3407 fnd_file.put_line (fnd_file.LOG,
3408 'assign responsibility to user created');
3409 fnd_file.put_line (fnd_file.LOG,
3410 'l_responsibility_id : ' ||
3411 l_responsibility_id);
3412 fnd_file.put_line (fnd_file.LOG,
3413 'l_security_grp_id : ' ||
3414 l_security_grp_id);
3415 fnd_user_resp_groups_api.insert_assignment
3416 (user_id => l_user_id,
3417 responsibility_id => l_responsibility_id,
3418 responsibility_application_id => 515,
3419 security_group_id => l_security_grp_id,
3420 description => 'Electronic renewals User',
3421 start_date => SYSDATE,
3422 end_date => NULL
3423 );
3424 /*modified for bug10018402*/
3425 l_return_value := set_user_home_page('FWK', l_user_id);
3426 /* fnd_profile.SAVE (x_name => 'APPLICATIONS_HOME_PAGE',
3427 x_value => 'FWK',
3428 x_level_name => 'USER',
3429 x_level_value => TO_CHAR
3430 (l_user_id)
3431 );*/
3432
3433 IF l_return_value
3434 THEN
3435 RETURN;
3436 ELSE
3437 -- error in fnd_profile.save
3438 -- debug log
3439 IF (fnd_log.level_procedure >=
3440 fnd_log.g_current_runtime_level
3441 )
3442 THEN
3443 fnd_log.STRING (fnd_log.level_procedure,
3444 g_module ||
3445 l_api_name,
3446 '180: error in fnd_profile.save : '
3447 );
3448 END IF;
3449
3450 -- log file
3451 fnd_file.put_line (fnd_file.LOG,
3452 'error in fnd_profile.save ');
3453 fnd_file.put_line (fnd_file.LOG, SQLERRM);
3454 RAISE fnd_api.g_exc_error;
3455 END IF;
3456 ELSE
3457 -- l_user_id is null, raise exception
3458 -- debug log
3459 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3460 )
3461 THEN
3462 fnd_log.STRING (fnd_log.level_procedure,
3463 g_module ||
3464 l_api_name,
3465 '190: l_user_id is null, raise exception '
3466 );
3467 END IF;
3468
3469 -- log file
3470 fnd_file.put_line (fnd_file.LOG,
3471 'l_user_id is null, raise exception ');
3472 fnd_file.put_line (fnd_file.LOG, SQLERRM);
3473 RAISE fnd_api.g_exc_error;
3474 END IF; -- l_user_id is not null
3475 ELSE -- l_test_user <> 0 ,3
3476 -- error, raise exception
3477 fnd_message.set_name ('OKS', 'OKS_SSO_TEST_USER_ERROR');
3478 fnd_message.set_token ('RETURN_VAL', l_test_user);
3479 fnd_msg_pub.ADD;
3480 -- log file
3481 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_TEST_USER_ERROR');
3482 fnd_file.put_line (fnd_file.LOG, 'l_test_user: ' ||
3483 l_test_user);
3484 RAISE fnd_api.g_exc_error;
3485 END IF; -- l_test_user check
3486 ELSE -- csr_chk_qtc_fnd_user%FOUND
3487 -- check if the above fnd_user is valid
3488 IF SYSDATE BETWEEN l_start_date AND NVL (l_end_date, SYSDATE +
3489 1)
3490 THEN
3491 -- fnd user is valid
3492
3493 -- log file
3494 fnd_file.put_line
3495 (fnd_file.LOG,
3496 'chk_qtc_fnd_user%FOUND, check if the above fnd_user is valid');
3497 fnd_file.put_line (fnd_file.LOG,
3498 'l_fnd_person_party_id : ' ||
3499 l_fnd_person_party_id);
3500 fnd_file.put_line (fnd_file.LOG,
3501 'l_person_party_id : ' ||
3502 l_person_party_id);
3503
3504 -- Check the person_party_id of the fnd_user
3505 IF l_fnd_person_party_id IS NULL
3506 THEN
3507 -- fnd_user.person_party_id IS NULL
3508 UPDATE fnd_user
3509 SET person_party_id = l_person_party_id
3510 WHERE user_name = UPPER (TRIM (p_user_name));
3511 ELSIF l_person_party_id <> l_fnd_person_party_id
3512 THEN
3513 --codefix for bug 5893305
3514 --check the value of the profile option oks_override_sso and update
3515 --oks_k_headers_b table accordingly.
3516 IF NVL(FND_PROFILE.VALUE('OKS_OVERRIDE_SSO'),'N') <>'N'
3517 THEN
3518 fnd_file.put_line (fnd_file.LOG,'Overriding SSO behavior');
3519 --update oks_k_headers_b with fnd_profile value.
3520 UPDATE oks_k_headers_b
3521 SET person_party_id =l_fnd_person_party_id
3522 WHERE chr_id=p_contract_id;
3523 fnd_file.put_line (fnd_file.LOG,'OKS Person Party ID updated to:'||l_fnd_person_party_id);
3524 ELSE
3525
3526 fnd_file.put_line (fnd_file.LOG,'Using SSO behavior');
3527 -- fnd_user.person_party_id does NOT match oks_person_party_id
3528 -- get the party names from hz_parties and raise error
3529 -- oks person party name
3530 OPEN csr_get_per_party_name (p_party_id => l_person_party_id);
3531 FETCH csr_get_per_party_name INTO l_oks_per_party_name;
3532 CLOSE csr_get_per_party_name;
3533 -- log file
3534 fnd_file.put_line (fnd_file.LOG,'OKS Person Party Name: '||l_oks_per_party_name);
3535
3536 -- fnd person party name
3537 OPEN csr_get_per_party_name (p_party_id => l_fnd_person_party_id);
3538 FETCH csr_get_per_party_name INTO l_fnd_per_party_name;
3539 CLOSE csr_get_per_party_name;
3540 -- log file
3541 fnd_file.put_line (fnd_file.LOG,'FND Person Party Name: '||l_fnd_per_party_name);
3542
3543 -- get the party name (organization name) for oks person
3544 OPEN csr_get_party_name (p_party_id => l_person_party_id);
3545 FETCH csr_get_party_name INTO l_oks_party_name, l_oks_party_id;
3546 CLOSE csr_get_party_name;
3547 -- log file
3548 fnd_file.put_line (fnd_file.LOG,'OKS Party Name: '||l_oks_party_name);
3549 fnd_file.put_line (fnd_file.LOG,'OKS Party ID: '||l_oks_party_id);
3550
3551 -- get the party name (organization name) for FND person
3552 OPEN csr_get_party_name (p_party_id => l_fnd_person_party_id);
3553 FETCH csr_get_party_name INTO l_fnd_party_name, l_fnd_party_id;
3554 CLOSE csr_get_party_name;
3555 -- log file
3556 fnd_file.put_line (fnd_file.LOG,'FND Party Name: '||l_fnd_party_name);
3557 fnd_file.put_line (fnd_file.LOG,'FND Party ID: '||l_fnd_party_id);
3558
3559 -- set error message
3560 fnd_message.set_name ('OKS', 'OKS_SSO_PERSON_PARTY_ERROR');
3561 fnd_message.set_token ('OKS_USER_NAME', p_user_name);
3562 fnd_message.set_token ('OKS_PARTY_ID', l_oks_party_id);
3563 fnd_message.set_token ('OKS_PARTY_NAME', l_oks_party_name);
3564 fnd_message.set_token ('FND_PARTY_ID', l_fnd_party_id);
3565 fnd_message.set_token ('FND_PARTY_NAME', l_fnd_party_name);
3566 fnd_message.set_token ('OKS_PERSON_PARTY_ID', l_person_party_id);
3567 fnd_message.set_token ('FND_PERSON_PARTY_ID', l_fnd_person_party_id);
3568 fnd_message.set_token ('OKS_PERSON_PARTY_NAME', l_oks_per_party_name); -- bug 6338286
3569 fnd_message.set_token ('FND_PERSON_PARTY_NAME', l_fnd_per_party_name); -- bug 6338286
3570 fnd_msg_pub.ADD;
3571
3572 -- log file
3573 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_PERSON_PARTY_ERROR');
3574 RAISE fnd_api.g_exc_error;
3575 END IF; --profile value check
3576
3577 END IF; -- person_party_id check
3578
3579 x_user_name := UPPER (TRIM (p_user_name));
3580 x_password := '******'; -- bug 5357772
3581
3582 CLOSE csr_chk_qtc_fnd_user;
3583
3584 RETURN;
3585 ELSE
3586 -- fnd user has expired RAISE exception;
3587 fnd_message.set_name ('OKS', 'OKS_SSO_USER_EXPIRED');
3588 fnd_message.set_token ('USER_NAME', UPPER (TRIM (p_user_name)));
3589 fnd_msg_pub.ADD;
3590 -- log file
3591 fnd_file.put_line (fnd_file.LOG, 'OKS_SSO_USER_EXPIRED ');
3592 RAISE fnd_api.g_exc_error;
3593 END IF;
3594 END IF;
3595
3596 CLOSE csr_chk_qtc_fnd_user;
3597
3598 -- log file
3599 fnd_file.put_line (fnd_file.LOG, ' ');
3600 fnd_file.put_line
3601 (fnd_file.LOG,
3602 '---------------------------------------------------------- ');
3603 fnd_file.put_line (fnd_file.LOG, 'Leaving create_sso_user');
3604 fnd_file.put_line
3605 (fnd_file.LOG,
3606 '---------------------------------------------------------- ');
3607 fnd_file.put_line (fnd_file.LOG, ' ');
3608 -- Standard call to get message count and if count is 1, get message info.
3609 fnd_msg_pub.count_and_get (p_encoded => 'F',
3610 p_count => x_msg_count,
3611 p_data => x_msg_data
3612 );
3613
3614
3615 -- end debug log
3616 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3617 THEN
3618 fnd_log.STRING (fnd_log.level_procedure,
3619 g_module ||
3620 l_api_name,
3621 '1000: Leaving ' ||
3622 g_pkg_name ||
3623 '.' ||
3624 l_api_name
3625 );
3626 END IF;
3627 EXCEPTION
3628 WHEN fnd_api.g_exc_error
3629 THEN
3630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3631 THEN
3632 fnd_log.STRING (fnd_log.level_procedure,
3633 g_module ||
3634 l_api_name,
3635 '2000: Leaving ' ||
3636 g_pkg_name ||
3637 '.' ||
3638 l_api_name
3639 );
3640 END IF;
3641
3642 fnd_file.put_line (fnd_file.LOG, '2000: Leaving create_sso_user');
3643
3644 x_return_status := g_ret_sts_error;
3645 fnd_msg_pub.count_and_get (p_encoded => 'F',
3646 p_count => x_msg_count,
3647 p_data => x_msg_data
3648 );
3649 WHEN fnd_api.g_exc_unexpected_error
3650 THEN
3651 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3652 THEN
3653 fnd_log.STRING (fnd_log.level_procedure,
3654 g_module ||
3655 l_api_name,
3656 '3000: Leaving ' ||
3657 g_pkg_name ||
3658 '.' ||
3659 l_api_name
3660 );
3661 END IF;
3662
3663 fnd_file.put_line (fnd_file.LOG, '3000: Leaving create_sso_user');
3664
3665 x_return_status := g_ret_sts_unexp_error;
3666 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3667 fnd_msg_pub.count_and_get (p_encoded => 'F',
3668 p_count => x_msg_count,
3669 p_data => x_msg_data
3670 );
3671 WHEN OTHERS
3672 THEN
3673 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3674 THEN
3675 fnd_log.STRING (fnd_log.level_procedure,
3676 g_module ||
3677 l_api_name,
3678 '4000: Leaving ' ||
3679 g_pkg_name ||
3680 '.' ||
3681 l_api_name
3682 );
3683 END IF;
3684
3685 fnd_file.put_line (fnd_file.LOG, '4000: Leaving create_sso_user');
3686
3687 x_return_status := g_ret_sts_error;
3688 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3689 fnd_msg_pub.count_and_get (p_encoded => 'F',
3690 p_count => x_msg_count,
3691 p_data => x_msg_data
3692 );
3693 END create_sso_user;
3694 END oks_auto_reminder;