[Home] [Help]
PACKAGE BODY: APPS.OKL_AUTO_INSURANCE_PVT
Source
1 PACKAGE BODY OKL_AUTO_INSURANCE_PVT AS
2 /* $Header: OKLRICXB.pls 120.18 2007/10/10 11:21:06 zrehman noship $ */
3 ---------------------------------------------------------------------------
4
5 FUNCTION get_trx_type
6 (p_name VARCHAR2,
7 p_language VARCHAR2)
8 RETURN NUMBER IS
9
10 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
11 SELECT id
12 FROM okl_trx_types_tl
13 WHERE name = cp_name
14 AND LANGUAGE = cp_language;-- Fix for 3637102
15 -- Source_lang replaced
16 -- with LANGUAGE
17
18 l_trx_type okl_trx_types_v.id%TYPE;
19
20 BEGIN
21
22 l_trx_type := NULL;
23
24 OPEN c_trx_type (p_name, p_language);
25 FETCH c_trx_type INTO l_trx_type;
26 CLOSE c_trx_type;
27
28 RETURN l_trx_type;
29
30 END get_trx_type;
31
32 ------------------------------------------------------------------
33 -- Procedure pay_ins_payments to pay insurance premium
34 ------------------------------------------------------------------
35
36 PROCEDURE PAY_INS_PAYMENTS
37 (
38 errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY NUMBER
40 ,p_from_bill_date IN VARCHAR2
41 ,p_to_bill_date IN VARCHAR2)IS
42
43 ------------------------------------------------------------
44 -- Extract all streams to be paid
45 ------------------------------------------------------------
46
47 -- Org Stripped OKC_K_HEADERS_B JOIN for ORG
48 -- cursor select changed to select streams based on purpose and that come from stream generation template
49 -- of the contract, implemented for insurance user defined streams, Bug 3924300
50
51 CURSOR c_streams_rec_csr(l_from_bill_date DATE,l_to_bill_date DATE ) IS
52 SELECT stm.khr_id khr_id,
53 TRUNC ( ste.stream_element_date) due_date,
54 stm.kle_id kle_id,
55 ste.id stream_ele_id,
56 stm.sty_id sty_id,
57 ste.amount amount,
58 chr.contract_number contract_number
59 FROM okl_strm_elements ste,
60 okl_streams stm,
61 OKC_K_HEADERS_B chr, -- Added for Org,
62 okl_k_headers oklchr,
63 OKL_STRM_TMPT_LINES_UV stl
64
65 WHERE ste.stream_element_date >= NVL (l_from_bill_date, ste.stream_element_date)
66 AND ste.stream_element_date <= NVL (l_to_bill_date, SYSDATE)
67 AND stm.id = ste.stm_id
68 AND ste.date_billed IS NULL
69 AND stm.active_yn = 'Y'
70 AND stm.say_code = 'CURR'
71 AND stl.primary_yn = 'Y'
72 AND stl.pdt_id = oklchr.pdt_id
73 AND (stl.start_date <= chr.start_date)
74 AND (stl.end_date >= chr.start_date OR stl.end_date IS NULL)
75 AND stl.primary_sty_purpose = 'INSURANCE_PAYABLE'
76 AND stl.primary_sty_id = stm.sty_id
77 AND chr.ID = stm.khr_id
78 AND chr.id = oklchr.id;
79
80
81 c_streams_rec c_streams_rec_csr%ROWTYPE;
82 ------------------------------------------------------------
83 -- Initialise constants
84 ------------------------------------------------------------
85
86 l_trx_type_name CONSTANT VARCHAR2(30) := 'Disbursement';
87 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
88 l_date_entered CONSTANT DATE := SYSDATE;
89
90 ------------------------------------------------------------
91 -- Declare local variables used in the program
92 ------------------------------------------------------------
93 l_trx_type_id NUMBER ;
94 l_from_bill_date DATE ;
95 l_to_bill_date DATE;
96 l_contract_number okc_k_headers_b.contract_number%type ;
97 l_token_val VARCHAR2(80);
98
99
100 ------------------------------------------------------------
101 -- Declare variables required by APIs
102 ------------------------------------------------------------
103
104 l_api_version CONSTANT NUMBER := 1;
105 l_api_name CONSTANT VARCHAR2(30) := 'pay_ins_payments';
106 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
107 l_msg_data VARCHAR2(2000);
108 l_msg_count NUMBER := 0 ;
109
110 l_selv_rec Okl_Streams_Pub.selv_rec_type;
111 x_selv_rec Okl_Streams_Pub.selv_rec_type;
112
113 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
114 SELECT id
115 FROM okl_trx_types_tl
116 WHERE name = cp_name
117 AND language = cp_language;
118
119 CURSOR c_trx_lkup IS
120 SELECT Meaning
121 FROM fnd_lookups
122 WHERE lookup_type = 'OKL_TRANSACTION_TYPE_CLASS'
123 and lookup_code ='DISBURSEMENT';
124
125 BEGIN
126
127
128 IF p_from_bill_date IS NOT NULL THEN
129 l_from_bill_date := FND_DATE.CANONICAL_TO_DATE(p_from_bill_date);
130 END IF;
131
132 IF p_to_bill_date IS NOT NULL THEN
133 l_to_bill_date := FND_DATE.CANONICAL_TO_DATE(p_to_bill_date);
134 END IF;
135 ------------------------------------------------------------
136 -- Start processing
137 ------------------------------------------------------------
138
139 ------------------------------------------------------------
140 -- Process every stream to be billed
141 ------------------------------------------------------------
142
143 -- get transaction id
144 OPEN c_trx_type ('Disbursement', 'US');
145 FETCH c_trx_type INTO l_trx_type_ID;
146 IF(c_trx_type%NOTFOUND) THEN
147 -- 3927315 Fix for hard coded tokens
148 OPEN c_trx_lkup;
149 FETCH c_trx_lkup INTO l_token_val;
150 CLOSE c_trx_lkup;
151 -- 3927315 Fix for hard coded tokens
152 Okc_Api.set_message(G_APP_NAME, 'OKL_AM_NO_TRX_TYPE_FOUND','TRY_NAME',l_token_val); -- 3745151 Fix for Invalid error messages.
153 CLOSE c_trx_type ;
154 RAISE OKC_API.G_EXCEPTION_ERROR;
155 END if ;
156 CLOSE c_trx_type ;
157
158 OPEN c_streams_rec_csr(l_from_bill_date,l_to_bill_date ) ;
159 LOOP
160 FETCH c_streams_rec_csr INTO c_streams_rec;
161 EXIT WHEN c_streams_rec_csr%NOTFOUND;
162 savepoint pay_ins_payments ;
163
164 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing: Contract Number=> '||c_streams_rec.contract_number||
165 ' ,for date=> '||c_streams_rec.due_date||' and Amount=> '||c_streams_rec.amount);
166
167 -- call ap_request
168
169 OKL_INSURANCE_POLICIES_PUB.insert_ap_request(
170 p_api_version =>l_api_version,
171 p_init_msg_list => Okc_Api.G_TRUE,
172 x_return_status =>l_return_status,
173 x_msg_count => l_msg_count,
174 x_msg_data =>l_msg_data,
175 p_tap_id => NULL,
176 p_credit_amount =>c_streams_rec.amount,
177 p_credit_sty_id =>c_streams_rec.STY_ID,
178 p_khr_id => c_streams_rec.khr_id,
179 p_kle_id =>c_streams_rec.kle_id,
180 p_invoice_date =>c_streams_rec.due_date,
181 p_trx_id => l_trx_type_id);
182
183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN --<4
184 FOR i IN 1..l_msg_count LOOP
185 JTF_PLSQL_API.get_messages(i,l_msg_data);
186 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
187 END LOOP;
188 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
189 ELSIF(l_return_status = OKC_API.G_RET_STS_ERROR) THEN --<4
190
191 FOR i IN 1..l_msg_count LOOP
192 JTF_PLSQL_API.get_messages(i,l_msg_data);
193 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
194 END LOOP;
195 ROLLBACK TO pay_ins_payments;
196 ELSE
197 l_selv_rec.id := c_streams_rec.stream_ele_id;
198 l_selv_rec.date_billed := sysdate;
199
200 Okl_Streams_Pub.update_stream_elements
201 (l_api_version
202 ,Okc_Api.G_TRUE
203 ,l_return_status
204 ,l_msg_count
205 ,l_msg_data
206 ,l_selv_rec
207 ,x_selv_rec);
208
209 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN --<4
210 FOR i IN 1..l_msg_count LOOP
211 JTF_PLSQL_API.get_messages(i,l_msg_data);
212 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
213 END LOOP;
214 ROLLBACK TO pay_ins_payments;
215 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
216 ELSIF(l_return_status = OKC_API.G_RET_STS_ERROR) THEN --<4
217
218 FOR i IN 1..l_msg_count LOOP
219 JTF_PLSQL_API.get_messages(i,l_msg_data);
220 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
221 END LOOP;
222 ROLLBACK TO pay_ins_payments;
223 END IF;
224 END IF;
225 commit;
226 END LOOP;
227 close c_streams_rec_csr ;
228
229 EXCEPTION
230
231 ------------------------------------------------------------
232 -- Exception handling
233 ------------------------------------------------------------
234
235 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
236 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
237
238 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
239 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
240
241 WHEN OTHERS THEN
242 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
243
244 END pay_ins_payments;
245
246
247 ---------------------------------------------------------------------------
248
249 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
250 -----------------------------------------------------------------------
251 -- Return 'Y' if there are some active subscription for the given event
252 -- Otherwise it returns 'N'
253 -----------------------------------------------------------------------
254 IS
255 CURSOR cu0 IS
256 SELECT 'Y'
257 FROM wf_event_subscriptions a,
258 wf_events b
259 WHERE a.event_filter_guid = b.guid
260 AND a.status = 'ENABLED'
261 AND b.name = p_event_name
262 AND rownum = 1;
263 l_yn VARCHAR2(1);
264 BEGIN
265 OPEN cu0;
266 FETCH cu0 INTO l_yn;
267 IF cu0%NOTFOUND THEN
268 l_yn := 'N';
269 END IF;
270 CLOSE cu0;
271 RETURN l_yn;
272 END;
273
274
275 PROCEDURE create_third_party_task_event
276 ( p_contract_id IN NUMBER,
277 p_org_id IN NUMBER,
278 x_retrun_status OUT NOCOPY VARCHAR2)
279 IS
280 l_parameter_list wf_parameter_list_t;
281 l_key varchar2(240);
282 l_yn varchar2(1);
283 l_event_name varchar2(240) := 'oracle.apps.okl.in.gthirdprtinsurance';
284 l_seq NUMBER ;
285
286 BEGIN
287
288 SAVEPOINT create_third_party_task_event;
289
290 x_retrun_status := OKC_API.G_RET_STS_SUCCESS ;
291 -- Test if there are any active subscritions
292 -- if it is the case then execute the subscriptions
293 l_yn := exist_subscription(l_event_name);
294
295 IF l_yn = 'Y' THEN
296
297 --Get the item key
298 select okl_wf_item_s.nextval INTO l_seq FROM DUAL ;
299 l_key := l_event_name ||l_seq ;
300
301 --Set Parameters
302 wf_event.AddParameterToList('CONTRACT_ID',p_contract_id,l_parameter_list);
303 -- Call it again if you have more than one parameter
304 -- Keep data type (text) only
305 --added by akrangan
306 wf_event.AddParameterToList('ORG_ID',p_org_id ,l_parameter_list);
307 -- Raise Event
308 -- It is overloaded function so use according to requirement
309 wf_event.raise( p_event_name => l_event_name
310 ,p_event_key => l_key
311 ,p_parameters => l_parameter_list);
312 l_parameter_list.DELETE;
313
314 ELSE
315 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
316 FND_MSG_PUB.ADD;
317 x_retrun_status := OKC_API.G_RET_STS_ERROR ;
318 END IF;
319 EXCEPTION
320 WHEN OTHERS THEN
321 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
322 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
323 FND_MSG_PUB.ADD;
324 ROLLBACK TO create_third_party_task_event;
325 x_retrun_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
326
327 END create_third_party_task_event;
328
329
330 -------------------------------------------------------------------------------
331
332 PROCEDURE pol_exp_notification(
333 errbuf OUT NOCOPY VARCHAR2,
334 retcode OUT NOCOPY NUMBER ,
335 p_template_id IN NUMBER )
336 IS
337 l_chr_id number ;
338 lx_email varchar2(240);
339 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
340 l_msg_count NUMBER ;
341 l_msg_data VARCHAR2(2000);
342 l_api_version CONSTANT NUMBER := 1;
343 l_return_status VARCHAR2(1) :=
344 Okc_Api.G_RET_STS_SUCCESS;
345 ls_to_email varchar2(240);
346 ls_contract_number VARCHAR2(80);
347 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
348 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
349 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
350 l_content_id NUMBER;
351 l_agent_id NUMBER;
352 l_subject VARCHAR2(80);
353 l_email VARCHAR2(80);
354 lx_request_id NUMBER ;
355 ls_policy_number VARCHAR2(80);
356 ls_reminder_yn VARCHAR2(3);
357 ls_reminder_days VARCHAR2(80) ;
358 ls_remder_num_days NUMBER ;
359 l_pol_id NUMBER ;
360 l_cust_acct_id NUMBER; --Bug 4177206
361
362 --PAGARG 27-Jan-2005 Bug 4095801 Org Stripping and correction of some joining conditions
363 CURSOR okl_eli_3rdpolicies_csr IS
364 SELECT IPYB.KHR_ID
365 ,OCHR.CONTRACT_NUMBER
366 ,IPYB.ID
367 ,OCHR.CUST_ACCT_ID --Bug 4177206
368 FROM OKL_INS_POLICIES_B IPYB
369 ,OKC_K_HEADERS_B OCHR
370 WHERE (IPYB.IPY_TYPE = 'THIRD_PARTY_POLICY' OR
371 (IPYB.IPY_TYPE = 'LEASE_POLICY' AND
372 IPYB.ISS_CODE = 'ACTIVE')) AND
373 trunc(IPYB.DATE_TO) = trunc(SYSDATE) + ls_remder_num_days
374 AND OCHR.ID = IPYB.KHR_ID;
375
376 -- Added by Skgautam for bug 4177206
377 -- Cursor to get Email Id
378 CURSOR get_email (p_cust_acct_id NUMBER) IS
379 SELECT hcp_email.email_address
380 FROM hz_cust_account_roles hcar ,
381 hz_contact_points hcp_email
382 WHERE hcar.cust_account_id = p_cust_acct_id
383 AND hcar.cust_acct_site_id IS NULL
384 AND hcar.status = 'A'
385 AND hcar.role_type = 'CONTACT'
386 AND hcp_email.contact_point_type = 'EMAIL'
387 AND hcp_email.owner_table_id = hcar.party_id
388 AND hcp_email.owner_table_name = 'HZ_PARTIES'
389 AND hcp_email.primary_flag = 'Y';
390 -- Added by Skgautam for bug 4177206
391 --Cursor to query user profile option name for given profile option name
392 CURSOR l_profile_name_csr(p_profile_code IN VARCHAR2) IS
393 SELECT USER_PROFILE_OPTION_NAME
394 FROM FND_PROFILE_OPTIONS_VL
395 WHERE PROFILE_OPTION_NAME = p_profile_code;
396
397 l_profile_name FND_PROFILE_OPTIONS_TL.USER_PROFILE_OPTION_NAME%TYPE;
398
399 BEGIN
400 fnd_profile.get('OKLINNTCINSEXPREQ',ls_reminder_yn);
401 fnd_profile.get('OKLINNTCINSEXP',ls_reminder_days);
402
403 ls_remder_num_days := TO_NUMBER(ls_reminder_days);
404
405 IF ls_reminder_yn = 'Y' THEN --Bug: 4177206
406 l_content_id := p_template_id ;
407 IF l_content_id = Okc_Api.G_MISS_NUM OR l_content_id IS NULL THEN --Bug: 4177206
408 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'No Document is selected');
409 END IF;
410 -- Added by Skgautam for bug 4177206
411 --Obtain user profile option name for given profile option name
412 OPEN l_profile_name_csr('OKL_EMAIL_IDENTITY');
413 FETCH l_profile_name_csr INTO l_profile_name;
414 CLOSE l_profile_name_csr;
415
416 OPEN okl_eli_3rdpolicies_csr;
417 LOOP
418 FETCH okl_eli_3rdpolicies_csr INTO l_chr_id,
419 ls_contract_number,l_pol_id ,l_cust_acct_id; --Bug 4177206
420 EXIT WHEN okl_eli_3rdpolicies_csr%NOTFOUND;
421
422 IF l_pol_id IS NOT NULL THEN
423
424 l_bind_var(1) := 'p_policy_id';
425 l_bind_val(1) := l_pol_id ;
426 l_bind_var_type(1) := 'NUMBER' ;
427
428 okl_cs_transactions_pub.get_pvt_label_email(
429 p_khr_id => l_chr_id,
430 x_email => lx_email,
431 x_return_status => l_return_status,
432 x_msg_count => l_msg_data,
433 x_msg_data => l_msg_count);
434
435 IF l_return_status = 'S' AND lx_email <> -1 THEN --Bug: 4177206
436 ls_to_email := lx_email;
437 ELSE
438 ls_to_email := fnd_profile.value('OKL_EMAIL_IDENTITY');
439 -- Check for NULL values and return if either of these is null
440 IF ls_to_email = Okc_Api.G_MISS_CHAR OR ls_to_email IS NULL THEN
441 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'System profile value for ' || l_profile_name || ' is not defined');
442 RETURN;
443 END IF;
444 END IF;
445 -- Added by Skgautam to get email id for bug 4177206
446 OPEN get_email(l_cust_acct_id);
447 FETCH get_email INTO l_email;
448 CLOSE get_email;
449
450 l_agent_id := FND_PROFILE.VALUE('USER_ID');
451 l_subject := 'Third party Insurance Reminder for Contract: '||ls_contract_number ;
452
453 OKL_FULFILLMENT_PUB.create_fulfillment (
454 p_api_version => l_api_version,
455 p_init_msg_list => l_init_msg_list,
456
457 p_agent_id => l_agent_id,
458 p_content_id => l_content_id,
459 p_from => ls_to_email,
460 p_subject => l_subject,
461 p_email => l_email,
462 p_bind_var => l_bind_var,
463 p_bind_val => l_bind_val,
464 p_bind_var_type => l_bind_var_type,
465
466 x_request_id => lx_request_id,
467 x_return_status => l_return_status,
468
469 x_msg_count => l_msg_count,
470 x_msg_data => l_msg_data);
471 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
472 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'No Document is selected');
473 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
474 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Unexpected Exception');
475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
476 END IF;
477
478 END IF;
479
480 end loop;
481 CLOSE okl_eli_3rdpolicies_csr ;
482 END IF;
483
484 EXCEPTION
485
486 WHEN OKL_API.G_EXCEPTION_ERROR THEN
487 --ROLLBACK TO create_fulfillment;
488 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
489
490 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
491 --ROLLBACK TO create_fulfillment;
492 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
493
494 WHEN OTHERS THEN
495 --ROLLBACK TO create_fulfillment;
496 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
497
498 end pol_exp_notification ;
499
500 /* $Header: OKLRICXB.pls 120.18 2007/10/10 11:21:06 zrehman noship $ */
501 ---------------------------------------------------------------------------
502 -- Start of comments
503 --
504 -- Function Name : get_contract_status
505 -- Description :It get Contract status based on contract id.
506 -- Business Rules :
507 -- Parameters :
508 -- Version : 1.0
509 -- End of Comments
510 ---------------------------------------------------------------------------
511 /*FUNCTION get_contract_status (
512 p_khr_id IN NUMBER,
513 x_contract_status OUT NOCOPY VARCHAR2
514 ) RETURN VARCHAR2 IS
515 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
516 CURSOR okc_k_status_csr(p_khr_id IN NUMBER) IS
517 SELECT STE_CODE
518 FROM OKC_K_HEADERS_V KHR , OKC_STATUSES_B OST
519 WHERE KHR.ID = p_khr_id
520 AND KHR.STS_CODE = OST.CODE;
521
522 BEGIN
523 OPEN okc_k_status_csr(p_khr_id);
524 FETCH okc_k_status_csr INTO x_contract_status ;
525 IF(okc_k_status_csr%NOTFOUND) THEN
526 -- store SQL error message on message stack for caller
527 OKL_API.set_message(G_APP_NAME,
528 'OKL_INVALID_CONTRACT_STATUS'
529 );
530 CLOSE okc_k_status_csr ;
531 l_return_status := OKC_API.G_RET_STS_ERROR;
532 -- Change it to
533 RETURN(l_return_status);
534 END IF;
535 CLOSE okc_k_status_csr ;
536 RETURN(l_return_status);
537 EXCEPTION
538 WHEN OTHERS THEN
539 -- store SQL error message on message stack for caller
540 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
541 -- notify caller of an UNEXPECTED error
542 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
543 -- verify that cursor was closed
544 IF okc_k_status_csr%ISOPEN THEN
545 CLOSE okc_k_status_csr;
546 END IF;
547 RETURN(l_return_status);
548 END get_contract_status;
549 */
550 ---------------------------------------------------------------------------
551 -- FUNCTION get_rec for: OKL_INS_POLICIES_V
552 ---------------------------------------------------------------------------
553 FUNCTION get_rec (
554 p_ipyv_rec IN ipyv_rec_type,
555 x_no_data_found OUT NOCOPY BOOLEAN
556 ) RETURN ipyv_rec_type IS
557 CURSOR okl_ipyv_pk_csr (p_id IN NUMBER) IS
558 SELECT
559 ID,
560 ADJUSTMENT,
561 CALCULATED_PREMIUM,
562 OBJECT_VERSION_NUMBER,
563 AGENCY_NUMBER,
564 SFWT_FLAG,
565 IPF_CODE,
566 INT_ID,
567 KHR_ID,
568 ISU_ID,
569 IPT_ID,
570 IPY_ID,
571 IPE_CODE,
572 CRX_CODE,
573 AGENCY_SITE_ID,
574 ISS_CODE,
575 KLE_ID,
576 AGENT_SITE_ID,
577 IPY_TYPE,
578 POLICY_NUMBER,
579 QUOTE_YN,
580 ENDORSEMENT,
581 INSURANCE_FACTOR,
582 COVERED_AMOUNT,
583 ADJUSTED_BY_ID,
584 FACTOR_VALUE,
585 DATE_QUOTED,
586 SALES_REP_ID,
587 DATE_PROOF_REQUIRED,
588 DATE_QUOTE_EXPIRY,
589 DEDUCTIBLE,
590 PAYMENT_FREQUENCY,
591 DATE_PROOF_PROVIDED,
592 DATE_FROM,
593 NAME_OF_INSURED,
594 DATE_TO,
595 DESCRIPTION,
596 ON_FILE_YN,
597 PREMIUM,
598 COMMENTS,
599 ACTIVATION_DATE,
600 PRIVATE_LABEL_YN,
601 LESSOR_INSURED_YN,
602 LESSOR_PAYEE_YN,
603 CANCELLATION_DATE,
604 CANCELLATION_COMMENT,
605 AGENT_YN,
606 ATTRIBUTE_CATEGORY,
607 ATTRIBUTE1,
608 ATTRIBUTE2,
609 ATTRIBUTE3,
610 ATTRIBUTE4,
611 ATTRIBUTE5,
612 ATTRIBUTE6,
613 ATTRIBUTE7,
614 ATTRIBUTE8,
615 ATTRIBUTE9,
616 ATTRIBUTE10,
617 ATTRIBUTE11,
618 ATTRIBUTE12,
619 ATTRIBUTE13,
620 ATTRIBUTE14,
621 ATTRIBUTE15,
622 ORG_ID,
623 REQUEST_ID,
624 PROGRAM_APPLICATION_ID,
625 PROGRAM_ID,
626 PROGRAM_UPDATE_DATE,
627 CREATED_BY,
628 CREATION_DATE,
629 LAST_UPDATED_BY,
630 LAST_UPDATE_DATE,
631 LAST_UPDATE_LOGIN
632 FROM Okl_Ins_Policies_V
633 WHERE okl_ins_policies_v.id = p_id;
634 l_okl_ipyv_pk okl_ipyv_pk_csr%ROWTYPE;
635 l_ipyv_rec ipyv_rec_type;
636 BEGIN
637 x_no_data_found := TRUE;
638 -- Get current database values
639 OPEN okl_ipyv_pk_csr (p_ipyv_rec.id);
640 FETCH okl_ipyv_pk_csr INTO
641 l_ipyv_rec.ID,
642 l_ipyv_rec.ADJUSTMENT,
643 l_ipyv_rec.CALCULATED_PREMIUM,
644 l_ipyv_rec.OBJECT_VERSION_NUMBER,
645 l_ipyv_rec.AGENCY_NUMBER,
646 l_ipyv_rec.SFWT_FLAG,
647 l_ipyv_rec.IPF_CODE,
648 l_ipyv_rec.INT_ID,
649 l_ipyv_rec.KHR_ID,
650 l_ipyv_rec.ISU_ID,
651 l_ipyv_rec.IPT_ID,
652 l_ipyv_rec.IPY_ID,
653 l_ipyv_rec.IPE_CODE,
654 l_ipyv_rec.CRX_CODE,
655 l_ipyv_rec.AGENCY_SITE_ID,
656 l_ipyv_rec.ISS_CODE,
657 l_ipyv_rec.KLE_ID,
658 l_ipyv_rec.AGENT_SITE_ID,
659 l_ipyv_rec.IPY_TYPE,
660 l_ipyv_rec.POLICY_NUMBER,
661 l_ipyv_rec.QUOTE_YN,
662 l_ipyv_rec.ENDORSEMENT,
663 l_ipyv_rec.INSURANCE_FACTOR,
664 l_ipyv_rec.COVERED_AMOUNT,
665 l_ipyv_rec.ADJUSTED_BY_ID,
666 l_ipyv_rec.FACTOR_VALUE,
667 l_ipyv_rec.DATE_QUOTED,
668 l_ipyv_rec.SALES_REP_ID,
669 l_ipyv_rec.DATE_PROOF_REQUIRED,
670 l_ipyv_rec.DATE_QUOTE_EXPIRY,
671 l_ipyv_rec.DEDUCTIBLE,
672 l_ipyv_rec.PAYMENT_FREQUENCY,
673 l_ipyv_rec.DATE_PROOF_PROVIDED,
674 l_ipyv_rec.DATE_FROM,
675 l_ipyv_rec.NAME_OF_INSURED,
676 l_ipyv_rec.DATE_TO,
677 l_ipyv_rec.DESCRIPTION,
678 l_ipyv_rec.ON_FILE_YN,
679 l_ipyv_rec.PREMIUM,
680 l_ipyv_rec.COMMENTS,
681 l_ipyv_rec.ACTIVATION_DATE,
682 l_ipyv_rec.PRIVATE_LABEL_YN,
683 l_ipyv_rec.LESSOR_INSURED_YN,
684 l_ipyv_rec.LESSOR_PAYEE_YN,
685 l_ipyv_rec.CANCELLATION_DATE,
686 l_ipyv_rec.CANCELLATION_COMMENT,
687 l_ipyv_rec.AGENT_YN,
688 l_ipyv_rec.ATTRIBUTE_CATEGORY,
689 l_ipyv_rec.ATTRIBUTE1,
690 l_ipyv_rec.ATTRIBUTE2,
691 l_ipyv_rec.ATTRIBUTE3,
692 l_ipyv_rec.ATTRIBUTE4,
693 l_ipyv_rec.ATTRIBUTE5,
694 l_ipyv_rec.ATTRIBUTE6,
695 l_ipyv_rec.ATTRIBUTE7,
696 l_ipyv_rec.ATTRIBUTE8,
697 l_ipyv_rec.ATTRIBUTE9,
698 l_ipyv_rec.ATTRIBUTE10,
699 l_ipyv_rec.ATTRIBUTE11,
700 l_ipyv_rec.ATTRIBUTE12,
701 l_ipyv_rec.ATTRIBUTE13,
702 l_ipyv_rec.ATTRIBUTE14,
703 l_ipyv_rec.ATTRIBUTE15,
704 l_ipyv_rec.ORG_ID,
705 l_ipyv_rec.REQUEST_ID,
706 l_ipyv_rec.PROGRAM_APPLICATION_ID,
707 l_ipyv_rec.PROGRAM_ID,
708 l_ipyv_rec.PROGRAM_UPDATE_DATE,
709 l_ipyv_rec.CREATED_BY,
710 l_ipyv_rec.CREATION_DATE,
711 l_ipyv_rec.LAST_UPDATED_BY,
712 l_ipyv_rec.LAST_UPDATE_DATE,
713 l_ipyv_rec.LAST_UPDATE_LOGIN;
714 x_no_data_found := okl_ipyv_pk_csr%NOTFOUND;
715 CLOSE okl_ipyv_pk_csr;
716 RETURN(l_ipyv_rec);
717 END get_rec;
718 FUNCTION get_rec (
719 p_ipyv_rec IN ipyv_rec_type
720 ) RETURN ipyv_rec_type IS
721 l_row_notfound BOOLEAN := TRUE;
722 BEGIN
723 RETURN(get_rec(p_ipyv_rec, l_row_notfound));
724 END get_rec;
725
726
727 PROCEDURE third_party_ins_followup(
728 errbuf OUT NOCOPY VARCHAR2,
729 retcode OUT NOCOPY NUMBER,
730 p_template_id IN NUMBER
731 )
732 IS
733 l_chr_id number ;
734
735 --PAGARG 27-Jan-2005 Bug 4095801 Org Stripping and correction of some joining conditions
736 CURSOR okl_eli_3rdpolicies_csr(l_no_of_days IN NUMBER ) IS
737 SELECT IPYB.KHR_ID
738 ,OCHR.CONTRACT_NUMBER
739 ,OCHR.CUST_ACCT_ID -- Bug 4177206
740 FROM OKL_INS_POLICIES_B IPYB
741 ,OKC_K_HEADERS_B OCHR
742 WHERE IPYB.IPY_TYPE = 'THIRD_PARTY_POLICY' AND
743 IPYB.DATE_PROOF_REQUIRED < SYSDATE AND
744 IPYB.DATE_PROOF_REQUIRED >= SYSDATE - l_no_of_days AND
745 IPYB.DATE_PROOF_PROVIDED IS NULL
746 AND OCHR.ID = IPYB.KHR_ID;
747
748 -- Added by Skgautam for bug 4177206
749 -- Cursor to get Email Id
750 CURSOR get_email (p_cust_acct_id NUMBER) IS
751 SELECT hcp_email.email_address
752 FROM hz_cust_account_roles hcar ,
753 hz_contact_points hcp_email
754 WHERE hcar.cust_account_id = p_cust_acct_id
755 AND hcar.cust_acct_site_id IS NULL
756 AND hcar.status = 'A'
757 AND hcar.role_type = 'CONTACT'
758 AND hcp_email.contact_point_type = 'EMAIL'
759 AND hcp_email.owner_table_id = hcar.party_id
760 AND hcp_email.owner_table_name = 'HZ_PARTIES'
761 AND hcp_email.primary_flag = 'Y';
762
763 -- Added by Skgautam for bug 4177206
764 --Cursor to query user profile option name for given profile option name
765 CURSOR l_profile_name_csr(p_profile_code IN VARCHAR2) IS
766 SELECT USER_PROFILE_OPTION_NAME
767 FROM FND_PROFILE_OPTIONS_VL
768 WHERE PROFILE_OPTION_NAME = p_profile_code;
769
770 l_profile_name FND_PROFILE_OPTIONS_TL.USER_PROFILE_OPTION_NAME%TYPE;
771
772 lx_email varchar2(240);
773 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
774 l_msg_count NUMBER ;
775 l_msg_data VARCHAR2(2000);
776 l_api_version CONSTANT NUMBER := 1;
777 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
778 ls_to_email varchar2(240);
779 ls_contract_number VARCHAR2(80);
780 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
781 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
782 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ;
783 l_content_id NUMBER;
784 l_agent_id NUMBER;
785 l_subject VARCHAR2(80);
786 l_email VARCHAR2(80);
787 lx_request_id NUMBER ;
788 l_cust_acct_id NUMBER; -- Bug 4177206
789
790 BEGIN
791
792 l_content_id := p_template_id ;
793 IF l_content_id = Okc_Api.G_MISS_NUM OR l_content_id IS NULL THEN --Bug: 4177206
794 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'No Document is Selected');
795 END IF;
796 -- Added by Skgautam for bug 4177206
797 --Obtain user profile option name for given profile option name
798 OPEN l_profile_name_csr('OKL_EMAIL_IDENTITY');
799 FETCH l_profile_name_csr INTO l_profile_name;
800 CLOSE l_profile_name_csr;
801
802 OPEN okl_eli_3rdpolicies_csr (10) ;
803 LOOP
804 FETCH okl_eli_3rdpolicies_csr INTO l_chr_id, ls_contract_number,l_cust_acct_id ; --Bug: 4177206
805 EXIT WHEN okl_eli_3rdpolicies_csr%NOTFOUND;
806
807 l_bind_var(1) := 'p_contract_id';
808 l_bind_val(1) := l_chr_id ;
809 l_bind_var_type(1) := 'NUMBER' ;
810
811 okl_cs_transactions_pub.get_pvt_label_email(p_khr_id => l_chr_id,
812 x_email => lx_email,
813 x_return_status => l_return_status,
814 x_msg_count => l_msg_data,
815 x_msg_data => l_msg_count);
816
817 IF l_return_status = 'S' AND lx_email <> -1 THEN --Bug: 4177206
818 ls_to_email := lx_email;
819 ELSE
820 ls_to_email := fnd_profile.value('OKL_EMAIL_IDENTITY');
821 -- Check for NULL values and return if either of these is null
822 IF ls_to_email = Okc_Api.G_MISS_CHAR OR ls_to_email IS NULL THEN --Bug: 4177206
823 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'System profile value for ' || l_profile_name || ' is not defined');
824 RETURN;
825 END IF;
826 END IF;
827
828 -- Added by Skgautam to get email id for bug 4177206
829 OPEN get_email(l_cust_acct_id);
830 FETCH get_email INTO l_email;
831 CLOSE get_email;
832 l_agent_id := FND_PROFILE.VALUE('USER_ID');
833 l_subject := 'Insurance Proof for Contract: '||ls_contract_number ;
834
835 OKL_FULFILLMENT_PUB.create_fulfillment (
836 p_api_version => l_api_version,
837 p_init_msg_list => l_init_msg_list,
838 p_agent_id => l_agent_id,
839 p_content_id => l_content_id,
840 p_from => ls_to_email,
841 p_subject => l_subject,
842 p_email => l_email,
843 p_bind_var => l_bind_var,
844 p_bind_val => l_bind_val,
845 p_bind_var_type => l_bind_var_type,
846 x_request_id => lx_request_id,
847 x_return_status => l_return_status,
848 x_msg_count => l_msg_count,
849 x_msg_data => l_msg_data);
850 IF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
851 FOR i IN 1..l_msg_count LOOP
852 JTF_PLSQL_API.get_messages(i,l_msg_data);
853 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
854 END LOOP;
855 ELSIF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
856 FOR i IN 1..l_msg_count LOOP
857 JTF_PLSQL_API.get_messages(i,l_msg_data);
858 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
859 END LOOP;
860 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
861 END IF;
862
863
864
865 end loop;
866 CLOSE okl_eli_3rdpolicies_csr ;
867
868 EXCEPTION
869
870 WHEN OKL_API.G_EXCEPTION_ERROR THEN
871 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
872
873 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
874 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
875 WHEN OTHERS THEN
876 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
877
878 end third_party_ins_followup ;
879
880 -- Start of comments
881 -- API name : auto_ins_establishment
882 -- Pre-reqs : None
883 -- Function : Invoked as Part of Automatic Insurance Concurrent Program
884 -- Parameters :
885 --OUT :errbuf OUT VARCHAR2 Required
886 -- :retcode OUT VARCHAR2 Required
887 --SSDESHPA Bug 6318957:DIT:F - AUTOMATIC INSURANCE DOESNOT CREATE INSURANCE
888 -- End of comments
889 PROCEDURE auto_ins_establishment(
890 errbuf OUT NOCOPY VARCHAR2,
891 retcode OUT NOCOPY NUMBER
892 )
893 IS
894 --04-Jan-2004 PAGARG Bug# 3941338
895 --Modified the query to fix the conditions and improve performance
896 --Org stripped the query to pick contracts in current org
897 CURSOR okl_eli_policies_csr(l_no_of_days IN NUMBER ) IS
898 SELECT OKHB.ID
899 ,OKHB.STS_CODE
900 ,OKHB.START_DATE
901 ,OKHB.END_DATE
902 ,OKHB.CONTRACT_NUMBER
903 --Bug # 6318957 SSDESHPA Changes Start
904 ,OKHB.ORG_ID
905 --Bug # 6318957 SSDESHPA Changes End
906 FROM OKC_K_HEADERS_B OKHB
907 ,OKL_K_HEADERS OKLH
908 ,OKC_STATUSES_B OKSB
909 WHERE OKSB.STE_CODE = 'ACTIVE' AND
910 OKHB.STS_CODE = OKSB.CODE AND
911 OKHB.SCS_CODE = 'LEASE' AND
912 OKHB.END_DATE > SYSDATE AND
913 OKLH.ID = OKHB.ID AND
914 (OKHB.START_DATE + l_no_of_days ) <= SYSDATE
915 AND OKHB.ID NOT IN
916 (SELECT IPYB.KHR_ID
917 FROM OKL_INS_POLICIES_B IPYB
918 WHERE IPYB.IPY_TYPE = 'LEASE_POLICY' AND
919 IPYB.QUOTE_YN = 'N' AND
920 IPYB.ISS_CODE = 'ACTIVE' AND
921 (SYSDATE - l_no_of_days ) BETWEEN IPYB.DATE_FROM AND IPYB.DATE_TO AND
922 IPYB.KHR_ID = OKHB.ID
923 UNION
924 SELECT IPYB.KHR_ID
925 FROM OKL_INS_POLICIES_B IPYB
926 WHERE IPYB.IPY_TYPE = 'THIRD_PARTY_POLICY' AND
927 (IPYB.DATE_PROOF_PROVIDED IS NOT NULL OR
928 (IPYB.DATE_PROOF_PROVIDED IS NULL AND
929 IPYB.DATE_PROOF_REQUIRED >= SYSDATE)) AND
930 IPYB.KHR_ID = OKHB.ID
931 )
932 ORDER BY OKHB.CONTRACT_NUMBER;
933 l_okl_eli_policies_csr okl_eli_policies_csr%ROWTYPE;
934
935 CURSOR okl_provider_csr(ls_country_code IN VARCHAR2, l_factor IN NUMBER) IS
936 SELECT OIR.RANKING_SEQ, OIR.ISU_ID
937 FROM OKL_INSURER_RANKINGS OIR,OKL_INS_PRODUCTS_B IPTB
938 WHERE OIR.ISU_ID = IPTB.ISU_ID
939 AND OIR.IC_CODE = ls_country_code
940 AND IPT_TYPE = 'LEASE_PRODUCT'
941 AND l_factor BETWEEN FACTOR_MIN AND FACTOR_MAX
942 AND SYSDATE BETWEEN oir.DATE_FROM AND DECODE(oir.DATE_TO,NULL,SYSDATE,oir.DATE_TO)
943 ORDER BY RANKING_SEQ ;
944
945
946 l_okl_provider_csr okl_provider_csr%ROWTYPE;
947
948 -- Effective From Date
949 CURSOR okl_last_ins_to_csr(l_khr_id IN NUMBER ) IS
950 SELECT IPYB.DATE_TO
951 FROM OKL_INS_POLICIES_B IPYB
952 WHERE IPYB.IPY_TYPE = 'THIRD_PARTY_POLICY'
953 AND IPYB.QUOTE_YN = 'N'
954 AND IPYB.DATE_PROOF_PROVIDED IS NOT NULL
955 AND IPYB.KHR_ID = l_khr_id
956 UNION
957 SELECT IPYB.DATE_TO
958 FROM OKL_INS_POLICIES_B IPYB
959 WHERE IPYB.IPY_TYPE = 'LEASE_POLICY'
960 AND IPYB.QUOTE_YN = 'N'
961 AND IPYB.ISS_CODE IN ('ACTIVE','EXPIRED','CANCELLED', 'PENDING')
962 AND IPYB.KHR_ID = l_khr_id
963 ORDER BY 1 DESC;
964
965 l_okl_last_ins_to_csr okl_last_ins_to_csr%ROWTYPE;
966
967 CURSOR okl_k_capital_amt_csr (p_khr_id NUMBER) IS
968 SELECT SUM(KLE.CAPITAL_AMOUNT) --,SUM(KLE.OEC)
969 FROM OKC_K_LINES_B CLEB,OKL_K_LINES KLE
970 WHERE CLEB.ID = KLE.ID
971 AND CLEB.DNZ_CHR_ID = p_khr_id
972 AND CLEB.CLE_ID IS NULL
973 GROUP BY CLEB.DNZ_CHR_ID ;
974
975 --smoduga added for bug 3551010
976 -- Fix for getting location based on install_base
977 -- install_location_type
978 CURSOR okl_financial_line_csr (p_khr_id NUMBER) IS
979 select cle.id
980 FROM OKC_K_LINES_B CLE ,
981 OKC_LINE_STYLES_B LST,
982 OKL_K_LINES kle
983 where cle.dnz_chr_id = p_khr_id
984 AND lst.lty_code ='FREE_FORM1'
985 AND lst.id = cle.lse_id
986 AND KLE.id = cle.id ;
987
988
989 cursor c_rulecust_value (p_khr_id NUMBER) IS
990 SELECT RUL.RULE_INFORMATION1,RUL.RULE_INFORMATION2
991 FROM OKC_RULES_V RUL, OKC_RULE_GROUPS_V GRUL
992 WHERE GRUL.RGD_CODE = 'INSRUL'
993 AND RUL.RGP_ID = GRUL.ID
994 AND RUL.rule_information_category = 'INCUST'
995 AND GRUL.CHR_ID = p_khr_id ;
996
997 cursor c_rulelessor_value (p_khr_id NUMBER)IS
998 SELECT RUL.RULE_INFORMATION1
999 FROM OKC_RULES_V RUL, OKC_RULE_GROUPS_V GRUL
1000 WHERE GRUL.RGD_CODE = 'INVRUL'
1001 AND RUL.RGP_ID = GRUL.ID
1002 AND RUL.rule_information_category = 'INVNIN'
1003 AND GRUL.CHR_ID = p_khr_id ;
1004
1005 workflow_tbl_type OKL_AUTO_INSURANCE_PVT.policy_tbl_type ;
1006 policy_tbl_type OKL_AUTO_INSURANCE_PVT.policy_tbl_type;
1007 noins_tbl_type OKL_AUTO_INSURANCE_PVT.policy_tbl_type;
1008 error_tbl_type OKL_AUTO_INSURANCE_PVT.policy_tbl_type;
1009 l_okl_provider_id NUMBER;
1010 l_blanket_ins_yn VARCHAR2(3) ;
1011 l_insurable_yn VARCHAR2(3) ;
1012 l_prm_lessor_sell VARCHAR2(3) ;
1013 l_khr_id NUMBER ;
1014 ls_country VARCHAR2(2);
1015 l_start_date DATE ;
1016 l_deal_amoun NUMBER ;
1017 ls_payment_freq VARCHAR2(30);
1018 l_deal_amount NUMBER;
1019 l_end_date DATE;
1020 lb_provider BOOLEAN := FALSE ;
1021 l_afterlease_criteria NUMBER ;
1022 x_message VARCHAR2(100) ;
1023 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
1024 l_msg_count NUMBER ;
1025 l_msg_data VARCHAR2(2000);
1026 l_api_version CONSTANT NUMBER := 1;
1027 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1028 px_ipyv_rec ipyv_rec_type;
1029 l_api_name CONSTANT VARCHAR2(30) := 'AUTO_INS_ESTABLISHMENT';
1030 x_iasset_tbl Okl_Ins_Quote_Pvt.iasset_tbl_type ;
1031 l_policy_id NUMBER ;
1032 l_counter NUMBER := 0;
1033 I NUMBER := 0;
1034 j NUMBER := 0;
1035 k NUMBER := 0;
1036 L NUMBER := 0;
1037 l_khr_number VARCHAR2(120) ;
1038 l_khr_start DATE ;
1039 ls_fin_line NUMBER;
1040 --Bug # 6318957 Changes Start
1041 l_khr_org_id NUMBER;
1042 --Bug # 6318957 Changes End
1043
1044 --04-Jan-2004 PAGARG Bug# 3941338
1045 --Cursor to query user profile option name for given profile option name
1046 CURSOR l_profile_name_csr(p_profile_code IN VARCHAR2) IS
1047 SELECT USER_PROFILE_OPTION_NAME
1048 FROM FND_PROFILE_OPTIONS_VL
1049 WHERE PROFILE_OPTION_NAME = p_profile_code;
1050 l_profile_name FND_PROFILE_OPTIONS_TL.USER_PROFILE_OPTION_NAME%TYPE;
1051
1052 BEGIN
1053
1054 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1055 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'---Automatic Insurance Start---');
1056 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1057 -- Get values from system profile for Number of days
1058 l_afterlease_criteria := fnd_profile.value('OKLINDAYSFORAUTOINS');
1059
1060 --04-Jan-2004 PAGARG Bug# 3941338
1061 --Obtain user profile option name for given profile option name
1062 OPEN l_profile_name_csr('OKLINDAYSFORAUTOINS');
1063 FETCH l_profile_name_csr INTO l_profile_name;
1064 CLOSE l_profile_name_csr;
1065
1066 -- Check for NULL values and return if either of these is null
1067 IF l_afterlease_criteria = Okc_Api.G_MISS_NUM OR l_afterlease_criteria IS NULL THEN
1068 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'System profile value for ' || l_profile_name || ' is not defined');
1069 RETURN;
1070 END IF;
1071
1072 -- Get all mandatory data
1073 ls_payment_freq := fnd_profile.value('OKLINPAYMENTFREQUENCY');
1074
1075 --04-Jan-2004 PAGARG Bug# 3941338
1076 --Obtain user profile option name for given profile option name
1077 OPEN l_profile_name_csr('OKLINPAYMENTFREQUENCY');
1078 FETCH l_profile_name_csr INTO l_profile_name;
1079 CLOSE l_profile_name_csr;
1080
1081 -- Check for NULL values and return if any of these is null
1082 IF ls_payment_freq = Okc_Api.G_MISS_CHAR OR ls_payment_freq IS NULL THEN
1083 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'System profile value for ' || l_profile_name || ' is not defined');
1084 RETURN;
1085 END IF;
1086
1087 BEGIN
1088
1089 OPEN okl_eli_policies_csr (l_afterlease_criteria) ;
1090 -- new_contract;
1091 LOOP
1092 FETCH okl_eli_policies_csr INTO l_okl_eli_policies_csr;
1093 EXIT WHEN okl_eli_policies_csr%NOTFOUND;
1094
1095 l_khr_id := l_okl_eli_policies_csr.ID ;
1096 l_khr_number := l_okl_eli_policies_csr.CONTRACT_NUMBER;
1097 l_khr_start := l_okl_eli_policies_csr.start_date;
1098 --Bug # 6318957 Changes Start
1099 l_khr_org_id := l_okl_eli_policies_csr.org_id;
1100 --Bug # 6318957 Changes End
1101 mo_global.set_policy_context('S', l_khr_org_id); --added by zrehman for Bug#6363652 10-Oct-2007
1102 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1103 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'--For Contract Number--'|| l_khr_number );
1104 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1105
1106
1107 OPEN c_rulecust_value(l_khr_id);
1108 FETCH c_rulecust_value INTO l_blanket_ins_yn, l_insurable_yn ;
1109 IF (c_rulecust_value%NOTFOUND) THEN
1110 l_blanket_ins_yn := 'N';
1111 l_insurable_yn := 'Y' ;
1112 END IF;
1113 close c_rulecust_value ;
1114
1115
1116 OPEN c_rulelessor_value(l_khr_id);
1117 FETCH c_rulelessor_value INTO l_prm_lessor_sell ;
1118 IF (c_rulelessor_value%NOTFOUND) THEN
1119 l_prm_lessor_sell := 'Y';
1120 END IF;
1121 close c_rulelessor_value ;
1122
1123
1124 IF (l_blanket_ins_yn = 'Y' ) THEN -- <1
1125 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Contract Number'|| l_khr_number ||'has blanket insurance ');
1126 I := I + 1;
1127 noins_tbl_type(I).CONTRACT_NUMBER := l_khr_number ;
1128 noins_tbl_type(I).start_date := l_khr_start ;
1129
1130 ELSIF(l_insurable_yn = 'N') THEN -- <1
1131 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Customer not insurable (Master Lease) for Contract Number '||l_khr_number);
1132 -- Initiate Workflow
1133 create_third_party_task_event
1134 ( p_contract_id => l_khr_id,
1135 p_org_id => l_khr_org_id,
1136 x_retrun_status => l_return_status);
1137 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1138 J:= J+ 1 ;
1139 workflow_tbl_type(j).CONTRACT_NUMBER := l_khr_number ;
1140 workflow_tbl_type(j).start_date := l_khr_start ;
1141 ELSE
1142 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Error in raising business event for Contract Number '||l_khr_number );
1143 K := k + 1 ;
1144 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1145 error_tbl_type(k).start_date := l_khr_start ;
1146 -- Need to check
1147 lb_provider := true;
1148 END IF;
1149
1150
1151 ELSIF(l_prm_lessor_sell = 'N' ) THEN -- 1
1152 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Vendor Program does not allow insurance for Contract Number '||l_khr_number );
1153 -- Initiate Workflow
1154 create_third_party_task_event
1155 ( p_contract_id => l_khr_id,
1156 p_org_id => l_khr_org_id,
1157 x_retrun_status => l_return_status);
1158 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1159 J:= J+ 1 ;
1160 workflow_tbl_type(j).CONTRACT_NUMBER := l_khr_number ;
1161 workflow_tbl_type(j).start_date := l_khr_start ;
1162 ELSE
1163 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Error in raising business event for Contract Number '||l_khr_number );
1164 K := k + 1 ;
1165 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1166 error_tbl_type(k).start_date := l_khr_start ;
1167 -- Need to check
1168 lb_provider := true;
1169 END IF;
1170
1171 ELSE --<1
1172 -- SMODUGA
1173 -- Added as part of fix for Canon bug 3551010
1174 --Get Financial Line
1175 OPEN okl_financial_line_csr(l_khr_id);
1176 FETCH okl_financial_line_csr INTO ls_fin_line;
1177 IF(okl_financial_line_csr%NOTFOUND) THEN
1178 CLOSE okl_financial_line_csr;
1179 K := k+1;
1180 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1181 error_tbl_type(k).start_date := l_khr_start ;
1182 lb_provider := true;
1183 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Financial Line not setup for Contract Number '|| l_khr_number);
1184 ELSIF (ls_fin_line = Okc_Api.G_MISS_NUM AND ls_fin_line IS NULL ) THEN -- <2
1185 CLOSE okl_financial_line_csr;
1186 K := k + 1 ;
1187 lb_provider := true;
1188 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1189 error_tbl_type(k).start_date := l_khr_start ;
1190 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Financial Line not setup for Contract Number '|| l_khr_number);
1191 ELSE -- <2
1192 CLOSE okl_financial_line_csr;
1193 END IF;
1194 -- GET COUNTRY
1195 ls_country := OKL_UTIL.get_active_line_inst_country(ls_fin_line);
1196 IF (ls_country = Okc_Api.G_MISS_CHAR AND ls_country IS NULL ) THEN -- <2
1197 K := k + 1 ;
1198 lb_provider := true;
1199 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1200 error_tbl_type(k).start_date := l_khr_start ;
1201 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Location is missing for Contract Number'||l_khr_number );
1202 ELSE
1203 -- Added as part of fix for Canon bug 3551010
1204 --SMODUGA
1205
1206
1207 OPEN okl_k_capital_amt_csr(l_khr_id);
1208 FETCH okl_k_capital_amt_csr INTO l_deal_amount ;
1209 IF( okl_k_capital_amt_csr%NOTFOUND) THEN --< 3
1210 CLOSE okl_k_capital_amt_csr;
1211 K := k + 1 ;
1212 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1213 error_tbl_type(k).start_date := l_khr_start ;
1214 lb_provider := true;
1215 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Capital Amount is missing for Contract Number '||l_khr_number);
1216 ELSE -- < 3
1217 CLOSE okl_k_capital_amt_csr;
1218
1219 OPEN okl_last_ins_to_csr (l_khr_id); -- changed by zrehman as part of Bug#6363652 09-Oct-2007
1220 -- Loop for FROM DATE
1221 FETCH okl_last_ins_to_csr INTO l_okl_last_ins_to_csr;
1222 IF (okl_last_ins_to_csr%NOTFOUND) THEN
1223 l_start_date := l_okl_eli_policies_csr.START_DATE ;
1224 ELSE
1225 l_start_date := l_okl_last_ins_to_csr.DATE_TO;
1226 END IF ;
1227 CLOSE okl_last_ins_to_csr ;
1228 -- Get End Date
1229 l_end_date := l_okl_eli_policies_csr.END_DATE ;
1230 OPEN okl_provider_csr (ls_country, l_deal_amount ) ;
1231 LOOP
1232 IF(lb_provider = true) then
1233 exit;
1234 END IF;
1235 FETCH okl_provider_csr INTO l_okl_provider_csr;
1236 EXIT WHEN okl_provider_csr%NOTFOUND;
1237
1238 px_ipyv_rec.KHR_ID := l_khr_id ;
1239 px_ipyv_rec.IPY_TYPE := 'LEASE_POLICY' ;
1240 px_ipyv_rec.DATE_FROM := l_start_date ;
1241 px_ipyv_rec.DATE_TO := l_end_date ;
1242 px_ipyv_rec.IPF_CODE := ls_payment_freq ;
1243 px_ipyv_rec.ISU_ID := l_okl_provider_csr.isu_id ;
1244 px_ipyv_rec.territory_code := ls_country;
1245 px_ipyv_rec.lessor_insured_yn := 'Y' ;
1246 px_ipyv_rec.lessor_payee_yn := 'Y' ;
1247 px_ipyv_rec.DATE_QUOTED := SYSDATE - 10 ;
1248 px_ipyv_rec.DATE_QUOTE_EXPIRY := SYSDATE + 20 ;
1249 px_ipyv_rec.OBJECT_VERSION_NUMBER := 1;
1250 --Bug # 6318957 Changes Start
1251 px_ipyv_rec.org_id := l_khr_org_id;
1252 --Bug # 6318957 Changes End
1253
1254 Okl_Ins_Quote_Pub.calc_lease_premium(
1255 p_api_version => l_api_version ,
1256 p_init_msg_list => Okc_Api.G_TRUE,
1257 x_return_status => l_return_status,
1258 x_msg_count => l_msg_count,
1259 x_msg_data => l_msg_data,
1260 px_ipyv_rec => px_ipyv_rec,
1261 x_message =>x_message,
1262 x_iasset_tbl => x_iasset_tbl );
1263
1264 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN --<4
1265 FOR i IN 1..l_msg_count LOOP
1266 JTF_PLSQL_API.get_messages(i,l_msg_data);
1267 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1268 END LOOP;
1269 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1270 ELSIF(l_return_status = OKC_API.G_RET_STS_ERROR) THEN --<4
1271 IF(x_message = OKL_INS_QUOTE_PVT.G_NO_INS ) THEN --<5
1272 lb_provider := false;
1273 FOR i IN 1..l_msg_count LOOP
1274 JTF_PLSQL_API.get_messages(i,l_msg_data);
1275 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1276 END LOOP;
1277 EXIT;
1278 ELSIF(x_message = OKL_INS_QUOTE_PVT.G_NOT_ABLE ) THEN--<5
1279 --Get Next Provider
1280
1281 NULL;
1282 ELSE --<5
1283
1284 FOR i IN 1..l_msg_count LOOP
1285 JTF_PLSQL_API.get_messages(i,l_msg_data);
1286 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1287 END LOOP;
1288 K := k + 1 ;
1289 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1290 error_tbl_type(k).start_date := l_khr_start ;
1291
1292 EXIT;
1293 END IF; -->5
1294 ELSE --<4
1295 SAVEPOINT auto_insurance;
1296 lb_provider := TRUE ;
1297 px_ipyv_rec.ADJUSTMENT := 0 ;
1298 Okl_Ins_Quote_Pub.save_accept_quote(
1299 p_api_version => l_api_version ,
1300 p_init_msg_list => Okc_Api.G_TRUE,
1301 x_return_status => l_return_status,
1302 x_msg_count => l_msg_count,
1303 x_msg_data => l_msg_data,
1304 p_ipyv_rec => px_ipyv_rec,
1305 x_message =>x_message
1306 );
1307 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1308 ROLLBACK TO auto_insurance;
1309 FOR i IN 1..l_msg_count LOOP
1310 JTF_PLSQL_API.get_messages(i,l_msg_data);
1311 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1312 END LOOP;
1313 EXIT;
1314 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1315 ROLLBACK TO auto_insurance;
1316 FOR i IN 1..l_msg_count LOOP
1317 JTF_PLSQL_API.get_messages(i,l_msg_data);
1318 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1319 END LOOP;
1320 EXIT;
1321 END IF;
1322 SELECT IPY_ID
1323 INTO l_policy_id
1324 FROM OKL_INS_POLICIES_B
1325 WHERE ID =x_message ;
1326
1327 IF (l_policy_id IS NULL) THEN
1328 lb_provider := FALSE ;
1329 ELSE ---< 5
1330 L := L + 1 ;
1331 policy_tbl_type(l).CONTRACT_NUMBER := l_khr_number ;
1332 policy_tbl_type(l).start_date := l_khr_start ;
1333
1334 COMMIT ;
1335 SAVEPOINT ACTIVATE ;
1336 Okl_Ins_Quote_Pub.activate_ins_policy(
1337 p_api_version => l_api_version ,
1338 p_init_msg_list => Okc_Api.G_TRUE,
1339 x_return_status => l_return_status,
1340 x_msg_count => l_msg_count,
1341 x_msg_data => l_msg_data,
1342 p_ins_policy_id => l_policy_id );
1343
1344 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1345 ROLLBACK TO ACTIVATE ;
1346 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Activate Policy for Contract' ||l_khr_number );
1347 FOR i IN 1..l_msg_count LOOP
1348 JTF_PLSQL_API.get_messages(i,l_msg_data);
1349 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1350 END LOOP;
1351 EXIT;
1352 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1353 ROLLBACK TO ACTIVATE ;
1354 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Activate Policy for Contract' ||l_khr_number );
1355 FOR i IN 1..l_msg_count LOOP
1356 JTF_PLSQL_API.get_messages(i,l_msg_data);
1357 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,l_msg_data );
1358 END LOOP;
1359 EXIT;
1360 END IF;
1361 COMMIT ;
1362 END IF ;--- 5>
1363 END IF ; ---4>
1364 END LOOP;
1365 CLOSE okl_provider_csr;
1366 END IF ; ---3>
1367 END IF ;---2>
1368 END IF ; ---1>
1369 -- Loop for provider
1370 IF(lb_provider <> TRUE ) THEN
1371 -- Initiate Workflow
1372
1373 create_third_party_task_event
1374 ( p_contract_id => l_khr_id,
1375 p_org_id => l_khr_org_id,
1376 x_retrun_status => l_return_status);
1377 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1378 J:= J+ 1 ;
1379 workflow_tbl_type(j).CONTRACT_NUMBER := l_khr_number ;
1380 workflow_tbl_type(j).start_date := l_khr_start ;
1381 ELSE
1382 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Error in raising business event for Contract Number '||l_khr_number );
1383 K := k + 1 ;
1384 error_tbl_type(k).CONTRACT_NUMBER := l_khr_number ;
1385 error_tbl_type(k).start_date := l_khr_start ;
1386 -- Need to check
1387 lb_provider := true;
1388 END IF;
1389 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'No Provider to provide insurance for Contract Number ' ||l_khr_number ); -- Initiate Workflow
1390 END IF ;
1391 lb_provider := FALSE;
1392 END LOOP ;
1393 CLOSE okl_eli_policies_csr ;
1394 mo_global.init('M'); --added by zrehman for Bug#6363652 10-Oct-2007
1395 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'---------------------- Summary -----------------------------');
1396 IF (policy_tbl_type.COUNT > 0) THEN
1397 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Policy Created For Contracts');
1398 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'Contract Number Start Date ' );
1399 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'--------------------------------' );
1400 FOR i IN policy_tbl_type.first..policy_tbl_type.last LOOP
1401 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||policy_tbl_type(i).CONTRACT_NUMBER ||' ' ||TO_CHAR(policy_tbl_type(i).start_date) );
1402 END LOOP;
1403 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Total = ' || policy_tbl_type.COUNT);
1404 END IF;
1405
1406
1407 IF (workflow_tbl_type.COUNT > 0) THEN
1408 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Third Party Workflow Initiated for Contracts');
1409 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'Contract Number Start Date ' );
1410 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'--------------------------------' );
1411 FOR n IN workflow_tbl_type.first..workflow_tbl_type.last LOOP
1412 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||workflow_tbl_type(n).CONTRACT_NUMBER ||' ' ||TO_CHAR(workflow_tbl_type(n).start_date) );
1413 END LOOP;
1414 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Total = ' || workflow_tbl_type.COUNT);
1415 END IF;
1416
1417 IF (noins_tbl_type.COUNT > 0) THEN
1418 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'No Insurance Required for Contracts');
1419 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'Contract Number Start Date ' );
1420 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'--------------------------------' );
1421 FOR n IN noins_tbl_type.first..noins_tbl_type.last LOOP
1422 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||noins_tbl_type(n).CONTRACT_NUMBER ||' ' ||TO_CHAR(noins_tbl_type(n).start_date) );
1423 END LOOP;
1424 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Total = ' || noins_tbl_type.COUNT);
1425 END IF;
1426
1427 IF ( error_tbl_type.COUNT > 0) THEN
1428 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'There are errors in Contracts');
1429 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'Contract Number Start Date ' );
1430 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||'--------------------------------' );
1431 FOR n IN error_tbl_type.first.. error_tbl_type.last LOOP
1432 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,' '||error_tbl_type(n).CONTRACT_NUMBER ||' ' ||TO_CHAR(error_tbl_type(n).start_date) );
1433 END LOOP;
1434 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'Total = ' || error_tbl_type.COUNT);
1435
1436 END IF;
1437 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1438 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'---Automatic Insurance End---');
1439 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'-------------------------------------------------------------');
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 IF l_profile_name_csr%ISOPEN THEN
1444 CLOSE l_profile_name_csr;
1445 END IF;
1446 IF okl_eli_policies_csr%ISOPEN THEN
1447 CLOSE okl_eli_policies_csr;
1448 END IF;
1449 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
1450 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
1451 END;
1452 END auto_ins_establishment ;
1453 END OKL_AUTO_INSURANCE_PVT;