[Home] [Help]
PACKAGE BODY: APPS.IEX_OPI_PVT
Source
1 PACKAGE BODY IEX_OPI_PVT AS
2 /* $Header: IEXROPIB.pls 120.3 2006/07/13 05:53:12 schekuri noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Procedures and Functions
6 ---------------------------------------------------------------------------
7
8 ---------------------------------------------------------------------------
9 -- PROCEDURE qc
10 ---------------------------------------------------------------------------
11 PROCEDURE qc AS
12 BEGIN
13 NULL;
14 END qc;
15
16 ---------------------------------------------------------------------------
17 -- PROCEDURE change_version
18 ---------------------------------------------------------------------------
19 PROCEDURE change_version AS
20 BEGIN
21 NULL;
22 END change_version;
23
24 ---------------------------------------------------------------------------
25 -- PROCEDURE api_copy
26 ---------------------------------------------------------------------------
27 PROCEDURE api_copy AS
28 BEGIN
29 NULL;
30 END api_copy;
31
32 ---------------------------------------------------------------------------
33 -- PROCEDURE report_all_credit_bureau
34 ---------------------------------------------------------------------------
35 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
36
37 PROCEDURE report_all_credit_bureau(
38 errbuf OUT NOCOPY VARCHAR2,
39 retcode OUT NOCOPY NUMBER) AS
40
41 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
42 lx_msg_count NUMBER ;
43 lx_msg_data VARCHAR2(2000);
44 lx_message VARCHAR2(2000);
45 l_api_version CONSTANT NUMBER := 1;
46 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
47 l_api_name CONSTANT VARCHAR2(30) := 'report_all_credit_bureau';
48
49 l_oinv_rec oinv_rec_type;
50 l_iohv_rec iohv_rec_type;
51 lx_oinv_rec oinv_rec_type;
52 lx_iohv_rec iohv_rec_type;
53
54 l_rows_processed NUMBER := 0;
55 l_rows_failed NUMBER := 0;
56 l_cust_reported NUMBER := 0;
57 l_cust_not_reported NUMBER := 0;
58 l_syndicate_flag VARCHAR2(1) := 'N';
59
60 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
61
62 CURSOR l_report_all_csr(cp_organization_id IN NUMBER) IS
63 SELECT id
64 FROM okc_k_headers_v
65 WHERE authoring_org_id = cp_organization_id
66 AND scs_code = 'LEASE';
67 BEGIN
68 --get organization id
69 --Begin Bug#5373556 schekuri 12-Jul-2006
70 l_organization_id := mo_global.get_current_org_id;
71 --l_organization_id := fnd_profile.value('ORG_ID');
72 --End Bug#5373556 schekuri 12-Jul-2006
73
74
75 -- Get pending records to be processed
76 OPEN l_report_all_csr(l_organization_id);
77 LOOP
78 FETCH l_report_all_csr INTO
79 l_oinv_rec.khr_id;
80 EXIT WHEN l_report_all_csr%NOTFOUND;
81
82 --find out NOCOPY lessee syndicate flag
83 l_return_status := OKL_CONTRACT_INFO.get_syndicate_flag(
84 p_contract_id => l_oinv_rec.khr_id
85 ,x_syndicate_flag => l_syndicate_flag);
86
87 IF NOT ((l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) OR
88 (l_return_status = okl_api.G_RET_STS_ERROR) OR
89 (l_syndicate_flag = 'Y')) THEN
90 l_cust_reported := l_cust_reported + 1;
91 --dbms_output.put_line('report - ' || l_oinv_rec.khr_id);
92 iex_open_interface_pub.insert_pending(
93 p_api_version => l_api_version,
94 p_init_msg_list => l_init_msg_list,
95 p_object1_id1 => l_oinv_rec.khr_id,
96 p_object1_id2 => '#',
97 p_jtot_object1_code => 'OKX_LEASE',
98 p_action => IEX_OPI_PVT.ACTION_REPORT_CB,
99 p_status => IEX_OPI_PVT.STATUS_PENDING_ALL,
100 p_comments => OKC_API.G_MISS_CHAR,
101 p_ext_agncy_id => NULL,
102 p_review_date => NULL,
103 p_recall_date => NULL,
104 p_automatic_recall_flag => NULL,
105 p_review_before_recall_flag => NULL,
106 x_return_status => l_return_status,
107 x_msg_count => lx_msg_count,
108 x_msg_data => lx_msg_data);
109
110 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
111 l_rows_failed := l_rows_failed + 1;
112 ELSE
113 l_rows_processed := l_rows_processed + 1;
114 END IF;
115 ELSE
116 l_cust_not_reported := l_cust_not_reported + 1;
117 --dbms_output.put_line('do not report - ' || l_oinv_rec.id);
118 END IF;
119
120 END LOOP;
121
122 CLOSE l_report_all_csr;
123
124 --dbms_output.PUT_LINE('CUSTOMERS REPORTED = ' || l_cust_reported);
125 --dbms_output.PUT_LINE('CUSTOMERS NOT REPORTED = ' || l_cust_not_reported);
126 --dbms_output.PUT_LINE('CUSTOMERS TO BE REPORTED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
127 --dbms_output.PUT_LINE('CUSTOMERS TO BE REPORTED NOT PROCESSED = ' || l_rows_failed);
128
129 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS REPORTED = ' || l_cust_reported);
130 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS NOT REPORTED = ' || l_cust_not_reported);
131 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE REPORTED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
132 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE REPORTED NOT PROCESSED = ' || l_rows_failed);
133
134 COMMIT;
135 EXCEPTION
136 WHEN OTHERS THEN
137 IF l_report_all_csr%ISOPEN THEN
138 CLOSE l_report_all_csr;
139 END IF;
140 errbuf := substr(SQLERRM, 1, 200);
141 retcode := 1;
142 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
143 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
144 ROLLBACK;
145 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
146 END report_all_credit_bureau;
147
148 ---------------------------------------------------------------------------
149 -- PROCEDURE insert_pending_hst
150 ---------------------------------------------------------------------------
151 PROCEDURE insert_pending_hst(
152 p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 ,
154 p_iohv_rec IN iohv_rec_type,
155 x_iohv_rec OUT NOCOPY iohv_rec_type,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2) AS
159
160 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
161 l_api_version CONSTANT NUMBER := 1;
162 l_api_name CONSTANT VARCHAR2(30) := 'insert_pending_hst';
163
164 l_iohv_rec iohv_rec_type;
165 lx_iohv_rec iohv_rec_type;
166 l_contract_hst_found BOOLEAN := FALSE;
167
168 CURSOR l_ioh_csr(cp_object1_id1 IN VARCHAR2
169 ,cp_object1_id2 IN VARCHAR2
170 ,cp_jtot_object1_code IN VARCHAR2
171 ,cp_action IN VARCHAR2
172 ,cp_status IN VARCHAR2) IS
173 SELECT id
174 FROM iex_open_int_hst
175 WHERE jtot_object1_code = cp_jtot_object1_code
176 AND object1_id1 = cp_object1_id1
177 AND object1_id2 = cp_object1_id2
178 AND action = cp_action
179 AND status = cp_status;
180 BEGIN
181 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
182 G_PKG_NAME,
183 p_init_msg_list,
184 l_api_version,
185 p_api_version,
186 '_PVT',
187 l_return_status);
188
189 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
190 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
191 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
192 RAISE OKC_API.G_EXCEPTION_ERROR;
193 END IF;
194
195 -- Processing starts
196 /*
197 l_iohv_rec.jtot_object1_code := p_iohv_rec.jtot_object1_code;
198 l_iohv_rec.object1_id1 := p_iohv_rec.object1_id1;
199 l_iohv_rec.object1_id2 := p_iohv_rec.object1_id2;
200 l_iohv_rec.action := p_iohv_rec.action;
201 l_iohv_rec.status := p_iohv_rec.status;
202 l_iohv_rec.comments := p_iohv_rec.comments;
203 l_iohv_rec.org_id := p_iohv_rec.org_id;
204 */
205 l_iohv_rec := p_iohv_rec;
206 l_iohv_rec.request_date := sysdate;
207
208 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
209 l_iohv_rec.comments := NULL;
210 END IF;
211
212
213 FOR cur IN l_ioh_csr(l_iohv_rec.object1_id1
214 ,l_iohv_rec.object1_id2
215 ,l_iohv_rec.jtot_object1_code
216 ,l_iohv_rec.action
217 ,l_iohv_rec.status
218 ) LOOP
219 l_iohv_rec.id := cur.id;
220 l_contract_hst_found := TRUE;
221 END LOOP;
222
223 IF l_contract_hst_found THEN
224 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
225 ,p_init_msg_list => p_init_msg_list
226 ,x_return_status => l_return_status
227 ,x_msg_count => x_msg_count
228 ,x_msg_data => x_msg_data
229 ,p_iohv_rec => l_iohv_rec
230 ,x_iohv_rec => lx_iohv_rec);
231 ELSE
232 iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
233 ,p_init_msg_list => p_init_msg_list
234 ,x_return_status => l_return_status
235 ,x_msg_count => x_msg_count
236 ,x_msg_data => x_msg_data
237 ,p_iohv_rec => l_iohv_rec
238 ,x_iohv_rec => lx_iohv_rec);
239 END IF;
240
241 x_iohv_rec := lx_iohv_rec;
242
243 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
244 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
245 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
246 RAISE okl_api.G_EXCEPTION_ERROR;
247 END IF;
248
249 -- Processing ends
250
251 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
252 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
253 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
254 RAISE okl_api.G_EXCEPTION_ERROR;
255 END IF;
256
257 x_return_status := l_return_status;
258 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
259 EXCEPTION
260 WHEN OKC_API.G_EXCEPTION_ERROR THEN
261 x_return_status := OKC_API.HANDLE_EXCEPTIONS
262 (
263 l_api_name,
264 G_PKG_NAME,
265 'OKC_API.G_RET_STS_ERROR',
266 x_msg_count,
267 x_msg_data,
268 '_PVT'
269 );
270 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
271 x_return_status := OKC_API.HANDLE_EXCEPTIONS
272 (
273 l_api_name,
274 G_PKG_NAME,
275 'OKC_API.G_RET_STS_UNEXP_ERROR',
276 x_msg_count,
277 x_msg_data,
278 '_PVT'
279 );
280 WHEN OTHERS THEN
281 x_return_status := OKC_API.HANDLE_EXCEPTIONS
282 (
283 l_api_name,
284 G_PKG_NAME,
285 'OTHERS',
286 x_msg_count,
287 x_msg_data,
288 '_PVT'
289 );
290 END insert_pending_hst;
291
292 ---------------------------------------------------------------------------
293 -- PROCEDURE process_pending_hst
294 ---------------------------------------------------------------------------
295 PROCEDURE process_pending_hst(
296 p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 ,
298 p_oinv_rec IN oinv_rec_type,
299 p_iohv_rec IN iohv_rec_type,
300 x_iohv_rec OUT NOCOPY iohv_rec_type,
301 x_return_status OUT NOCOPY VARCHAR2,
302 x_msg_count OUT NOCOPY NUMBER,
303 x_msg_data OUT NOCOPY VARCHAR2)AS
304
305 lx_msg_count NUMBER ;
306 lx_msg_data VARCHAR2(2000);
307 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
308 l_api_version CONSTANT NUMBER := 1;
309 l_api_name CONSTANT VARCHAR2(30) := 'process_pending_hst';
310
311 l_oinv_rec oinv_rec_type;
312 l_iohv_rec iohv_rec_type;
313 lp_iohv_rec iohv_rec_type;
314 lx_iohv_rec iohv_rec_type;
315 BEGIN
316 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
317 G_PKG_NAME,
318 p_init_msg_list,
319 l_api_version,
320 p_api_version,
321 '_PVT',
322 l_return_status);
323
324 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
325 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
327 RAISE OKC_API.G_EXCEPTION_ERROR;
328 END IF;
329
330 -- Processing starts
331 l_oinv_rec := p_oinv_rec;
332 l_iohv_rec := p_iohv_rec;
333 l_iohv_rec.status := STATUS_PROCESSED;
334 l_iohv_rec.process_date := SYSDATE;
335
336 --Get external agency to pass case
337 IF (l_iohv_rec.action = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY) THEN
338 IF (l_iohv_rec.ext_agncy_id IS NULL) THEN
339 get_external_agency(p_oinv_rec => l_oinv_rec
340 ,p_iohv_rec => l_iohv_rec
341 ,x_ext_agncy_id => l_iohv_rec.ext_agncy_id
342 ,x_return_status => l_return_status);
343
344 IF(l_iohv_rec.ext_agncy_id IS NULL) THEN
345 FND_MESSAGE.SET_NAME('IEX', 'IEX_EXTERNAL_AGENCY_UNASSIGNED');
346 FND_MESSAGE.SET_TOKEN('CASE_ID', TO_CHAR(l_oinv_rec.cas_id));
347 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET);
348 END IF;
349 END IF;
350
351 IF(l_iohv_rec.ext_agncy_id IS NOT NULL) THEN
352 lp_iohv_rec := l_iohv_rec;
353 lp_iohv_rec.id := null;
354 lp_iohv_rec.action := ACTION_NOTIFY_EXT_AGNCY;
355 lp_iohv_rec.status := STATUS_PROCESSED;
356 lp_iohv_rec.request_date := SYSDATE;
357 lp_iohv_rec.process_date := SYSDATE;
358 lp_iohv_rec.review_date := NULL;
359 lp_iohv_rec.recall_date := NULL;
360 lp_iohv_rec.automatic_recall_flag := NULL;
361 lp_iohv_rec.review_before_recall_flag := NULL;
362
363 iex_open_int_hst_pub.insert_open_int_hst(
364 p_api_version => l_api_version,
365 p_init_msg_list => p_init_msg_list,
366 x_return_status => l_return_status,
367 x_msg_count => lx_msg_count,
368 x_msg_data => lx_msg_data,
369 p_iohv_rec => lp_iohv_rec,
370 x_iohv_rec => lx_iohv_rec);
371 END IF;
372
373 /*
374 --Get number of days to transfer case to external agency
375 IF(l_iohv_rec.review_date IS NULL) THEN
376 l_iohv_rec.review_date := l_iohv_rec.request_date + fnd_profile.value('IEX_EA_TRANSFER_DAYS');
377 END IF;
378 */
379 END IF;
380
381 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
382 ,p_init_msg_list => p_init_msg_list
383 ,x_return_status => l_return_status
384 ,x_msg_count => x_msg_count
385 ,x_msg_data => x_msg_data
386 ,p_iohv_rec => l_iohv_rec
387 ,x_iohv_rec => lx_iohv_rec);
388 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
389 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
390 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
391 RAISE okl_api.G_EXCEPTION_ERROR;
392 END IF;
393
394 x_iohv_rec := lx_iohv_rec;
395
396 -- Processing ends
397 x_return_status := l_return_status;
398 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
399 EXCEPTION
400 WHEN OKC_API.G_EXCEPTION_ERROR THEN
401 x_return_status := OKC_API.HANDLE_EXCEPTIONS
402 (
403 l_api_name,
404 G_PKG_NAME,
405 'OKC_API.G_RET_STS_ERROR',
406 x_msg_count,
407 x_msg_data,
408 '_PVT'
409 );
410 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
411 x_return_status := OKC_API.HANDLE_EXCEPTIONS
412 (
413 l_api_name,
414 G_PKG_NAME,
415 'OKC_API.G_RET_STS_UNEXP_ERROR',
416 x_msg_count,
417 x_msg_data,
418 '_PVT'
419 );
420 WHEN OTHERS THEN
421 x_return_status := OKC_API.HANDLE_EXCEPTIONS
422 (
423 l_api_name,
424 G_PKG_NAME,
425 'OTHERS',
426 x_msg_count,
427 x_msg_data,
428 '_PVT'
429 );
430 END process_pending_hst;
431
432 ---------------------------------------------------------------------------
433 -- PROCEDURE process_pending
434 ---------------------------------------------------------------------------
435 PROCEDURE process_pending(
436 errbuf OUT NOCOPY VARCHAR2,
437 retcode OUT NOCOPY NUMBER,
438 p_case_number IN VARCHAR2) AS
439
440 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
441 lx_msg_count NUMBER ;
442 lx_msg_data VARCHAR2(2000);
443 lx_message VARCHAR2(2000);
444 l_api_version CONSTANT NUMBER := 1;
445 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
446 l_api_name CONSTANT VARCHAR2(30) := 'process_pending';
447 l_oinv_rec oinv_rec_type;
448 lx_oinv_rec oinv_rec_type;
449 l_iohv_rec iohv_rec_type;
450 lx_iohv_rec iohv_rec_type;
451
452 l_rows_processed NUMBER := 0;
453 l_rows_failed NUMBER := 0;
454
455 l_case_passed VARCHAR2(1) := Okc_Api.G_TRUE;
456 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
457
458 CURSOR l_oin_pend_csr(cp_case_number IN VARCHAR2
459 ,cp_organization_id IN NUMBER) IS
460 SELECT OIN.ID,
461 OIN.PARTY_ID,
462 OIN.PARTY_NAME,
463 OIN.PARTY_TYPE,
464 OIN.DATE_OF_BIRTH,
465 OIN.PLACE_OF_BIRTH,
466 OIN.PERSON_IDENTIFIER,
467 OIN.PERSON_IDEN_TYPE,
468 OIN.COUNTRY,
469 OIN.ADDRESS1,
470 OIN.ADDRESS2,
471 OIN.ADDRESS3,
472 OIN.ADDRESS4,
473 OIN.CITY,
474 OIN.POSTAL_CODE,
475 OIN.STATE,
476 OIN.PROVINCE,
477 OIN.COUNTY,
478 OIN.PO_BOX_NUMBER,
479 OIN.HOUSE_NUMBER,
480 OIN.STREET_SUFFIX,
481 OIN.APARTMENT_NUMBER,
482 OIN.STREET,
483 OIN.RURAL_ROUTE_NUMBER,
484 OIN.STREET_NUMBER,
485 OIN.BUILDING,
486 OIN.FLOOR,
487 OIN.SUITE,
488 OIN.ROOM,
489 OIN.POSTAL_PLUS4_CODE,
490 OIN.CAS_ID,
491 OIN.CASE_NUMBER,
492 OIN.KHR_ID,
493 OIN.CONTRACT_NUMBER,
494 OIN.CONTRACT_TYPE,
495 OIN.CONTRACT_STATUS,
496 OIN.ORIGINAL_AMOUNT,
497 OIN.START_DATE,
498 OIN.CLOSE_DATE,
499 OIN.TERM_DURATION,
500 OIN.MONTHLY_PAYMENT_AMOUNT,
501 OIN.LAST_PAYMENT_DATE,
502 OIN.DELINQUENCY_OCCURANCE_DATE,
503 OIN.PAST_DUE_AMOUNT,
504 OIN.REMAINING_AMOUNT,
505 OIN.CREDIT_INDICATOR,
506 OIN.NOTIFICATION_DATE,
507 OIN.CREDIT_BUREAU_REPORT_DATE,
508 OIN.CONTACT_ID,
509 OIN.CONTACT_NAME,
510 OIN.CONTACT_PHONE,
511 OIN.CONTACT_EMAIL,
512 OIN.OBJECT_VERSION_NUMBER,
513 OIN.ORG_ID,
514 OIN.REQUEST_ID,
515 OIN.PROGRAM_APPLICATION_ID,
516 OIN.PROGRAM_ID,
517 OIN.PROGRAM_UPDATE_DATE,
518 OIN.ATTRIBUTE_CATEGORY,
519 OIN.ATTRIBUTE1,
520 OIN.ATTRIBUTE2,
521 OIN.ATTRIBUTE3,
522 OIN.ATTRIBUTE4,
523 OIN.ATTRIBUTE5,
524 OIN.ATTRIBUTE6,
525 OIN.ATTRIBUTE7,
526 OIN.ATTRIBUTE8,
527 OIN.ATTRIBUTE9,
528 OIN.ATTRIBUTE10,
529 OIN.ATTRIBUTE11,
530 OIN.ATTRIBUTE12,
531 OIN.ATTRIBUTE13,
532 OIN.ATTRIBUTE14,
533 OIN.ATTRIBUTE15,
534 OIN.CREATED_BY,
535 OIN.CREATION_DATE,
536 OIN.LAST_UPDATED_BY,
537 OIN.LAST_UPDATE_DATE,
538 OIN.LAST_UPDATE_LOGIN,
539 IOH.ID,
540 IOH.OBJECT1_ID1,
541 IOH.OBJECT1_ID2,
542 IOH.JTOT_OBJECT1_CODE,
543 IOH.ACTION,
544 IOH.STATUS,
545 IOH.COMMENTS,
546 IOH.REQUEST_DATE,
547 IOH.PROCESS_DATE,
548 IOH.EXT_AGNCY_ID,
549 IOH.REVIEW_DATE,
550 IOH.RECALL_DATE
551 FROM Okl_Open_Int OIN
552 ,Iex_Open_Int_Hst IOH
553 WHERE OIN.khr_id = TO_NUMBER(IOH.OBJECT1_ID1)
554 AND IOH.JTOT_OBJECT1_CODE = 'OKX_LEASE'
555 AND ((l_case_passed = Okc_Api.G_FALSE) OR
556 (l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
557 AND OIN.org_id = cp_organization_id
558 AND ((IOH.STATUS = STATUS_PENDING_AUTO) OR
559 (IOH.STATUS = STATUS_PENDING_MANUAL) OR
560 (IOH.STATUS = STATUS_PENDING_ALL));
561 BEGIN
562 --check if case number is passed
563 IF (p_case_number = OKC_API.G_MISS_CHAR OR
564 p_case_number IS NULL) THEN
565 l_case_passed := Okc_Api.G_FALSE;
566 --dbms_output.put_line('case is not passed');
567 END IF;
568
569 --get organization id
570 --Begin Bug#5373556 schekuri 12-Jul-2006
571 l_organization_id := mo_global.get_current_org_id;
572 --l_organization_id := fnd_profile.value('ORG_ID');
573 --End Bug#5373556 schekuri 12-Jul-2006
574
575 -- Get pending records to be processed
576 OPEN l_oin_pend_csr(p_case_number
577 ,l_organization_id);
578 LOOP
579 FETCH l_oin_pend_csr INTO
580 l_oinv_rec.id,
581 l_oinv_rec.party_id,
582 l_oinv_rec.party_name,
583 l_oinv_rec.party_type,
584 l_oinv_rec.date_of_birth,
585 l_oinv_rec.place_of_birth,
586 l_oinv_rec.person_identifier,
587 l_oinv_rec.person_iden_type,
588 l_oinv_rec.country,
589 l_oinv_rec.address1,
590 l_oinv_rec.address2,
591 l_oinv_rec.address3,
592 l_oinv_rec.address4,
593 l_oinv_rec.city,
594 l_oinv_rec.postal_code,
595 l_oinv_rec.state,
596 l_oinv_rec.province,
597 l_oinv_rec.county,
598 l_oinv_rec.po_box_number,
599 l_oinv_rec.house_number,
600 l_oinv_rec.street_suffix,
601 l_oinv_rec.apartment_number,
602 l_oinv_rec.street,
603 l_oinv_rec.rural_route_number,
604 l_oinv_rec.street_number,
605 l_oinv_rec.building,
606 l_oinv_rec.floor,
607 l_oinv_rec.suite,
608 l_oinv_rec.room,
609 l_oinv_rec.postal_plus4_code,
610 l_oinv_rec.cas_id,
611 l_oinv_rec.case_number,
612 l_oinv_rec.khr_id,
613 l_oinv_rec.contract_number,
614 l_oinv_rec.contract_type,
615 l_oinv_rec.contract_status,
616 l_oinv_rec.original_amount,
617 l_oinv_rec.start_date,
618 l_oinv_rec.close_date,
619 l_oinv_rec.term_duration,
620 l_oinv_rec.monthly_payment_amount,
621 l_oinv_rec.last_payment_date,
622 l_oinv_rec.delinquency_occurance_date,
623 l_oinv_rec.past_due_amount,
624 l_oinv_rec.remaining_amount,
625 l_oinv_rec.credit_indicator,
626 l_oinv_rec.notification_date,
627 l_oinv_rec.credit_bureau_report_date,
628 l_oinv_rec.contact_id,
629 l_oinv_rec.contact_name,
630 l_oinv_rec.contact_phone,
631 l_oinv_rec.contact_email,
632 l_oinv_rec.object_version_number,
633 l_oinv_rec.org_id,
634 l_oinv_rec.request_id,
635 l_oinv_rec.program_application_id,
636 l_oinv_rec.program_id,
637 l_oinv_rec.program_update_date,
638 l_oinv_rec.attribute_category,
639 l_oinv_rec.attribute1,
640 l_oinv_rec.attribute2,
641 l_oinv_rec.attribute3,
642 l_oinv_rec.attribute4,
643 l_oinv_rec.attribute5,
644 l_oinv_rec.attribute6,
645 l_oinv_rec.attribute7,
646 l_oinv_rec.attribute8,
647 l_oinv_rec.attribute9,
648 l_oinv_rec.attribute10,
649 l_oinv_rec.attribute11,
650 l_oinv_rec.attribute12,
651 l_oinv_rec.attribute13,
652 l_oinv_rec.attribute14,
653 l_oinv_rec.attribute15,
654 l_oinv_rec.created_by,
655 l_oinv_rec.creation_date,
656 l_oinv_rec.last_updated_by,
657 l_oinv_rec.last_update_date,
658 l_oinv_rec.last_update_login,
659 l_iohv_rec.id,
660 l_iohv_rec.object1_id1,
661 l_iohv_rec.object1_id2,
662 l_iohv_rec.jtot_object1_code,
663 l_iohv_rec.action,
664 l_iohv_rec.status,
665 l_iohv_rec.comments,
666 l_iohv_rec.request_date,
667 l_iohv_rec.process_date,
668 l_iohv_rec.ext_agncy_id,
669 l_iohv_rec.review_date,
670 l_iohv_rec.recall_date;
671 EXIT WHEN l_oin_pend_csr%NOTFOUND;
672 --dbms_output.put_line('Processing krd_id : ' || l_oinv_rec.khr_id);
673 okl_open_interface_pub.process_pending_int(p_api_version => l_api_version
674 ,p_init_msg_list => l_init_msg_list
675 ,p_oinv_rec => l_oinv_rec
676 ,p_iohv_rec => l_iohv_rec
677 ,x_oinv_rec => lx_oinv_rec
678 ,x_return_status => l_return_status
679 ,x_msg_count => lx_msg_count
680 ,x_msg_data => lx_msg_data);
681
682 process_pending_hst(p_api_version => l_api_version
683 ,p_init_msg_list => l_init_msg_list
684 ,p_oinv_rec => lx_oinv_rec
685 ,p_iohv_rec => l_iohv_rec
686 ,x_iohv_rec => lx_iohv_rec
687 ,x_return_status => l_return_status
688 ,x_msg_count => lx_msg_count
689 ,x_msg_data => lx_msg_data);
690
691 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
692 l_rows_failed := l_rows_failed + 1;
693 ELSE
694 l_rows_processed := l_rows_processed + 1;
695 END IF;
696 END LOOP;
697 CLOSE l_oin_pend_csr;
698
699 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'ROWS PROCESSED SUCCESSFULLY = ' || l_rows_processed);
700 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'ROWS NOT PROCESSED = ' || l_rows_failed);
701
702 COMMIT;
703 EXCEPTION
704 WHEN OTHERS THEN
705 IF l_oin_pend_csr%ISOPEN THEN
706 CLOSE l_oin_pend_csr;
707 END IF;
708 errbuf := substr(SQLERRM, 1, 200);
709 retcode := 1;
710 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
711 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
712 ROLLBACK;
713 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
714 END process_pending;
715
716 ---------------------------------------------------------------------------
717 -- PROCEDURE complete_report_cb
718 ---------------------------------------------------------------------------
719 PROCEDURE complete_report_cb(
720 p_api_version IN NUMBER,
721 p_init_msg_list IN VARCHAR2 ,
722 p_interface_id IN NUMBER,
723 p_report_date IN DATE,
724 p_comments IN VARCHAR2 ,
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2) AS
728
729 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
730 l_api_version CONSTANT NUMBER := 1;
731 l_api_name CONSTANT VARCHAR2(30) := 'complete_report_cb';
732
733 l_oinv_rec oinv_rec_type;
734 lx_oinv_rec oinv_rec_type;
735
736 l_iohv_rec iohv_rec_type;
737 lx_iohv_rec iohv_rec_type;
738
739 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
740 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
741
742 l_reportCB_process_found BOOLEAN := FALSE;
743
744 CURSOR l_khr_csr(cp_interface_id IN NUMBER) IS
745 SELECT oin.khr_id
746 ,oin.org_id
747 ,ioh.id
748 ,ioh.object1_id1
749 ,ioh.object1_id2
750 ,ioh.jtot_object1_code
751 FROM okl_open_int oin
752 ,iex_open_int_hst ioh
753 WHERE oin.id = cp_interface_id
754 AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
755 AND ioh.jtot_object1_code = 'OKX_LEASE'
756 AND ioh.action = ACTION_REPORT_CB
757 AND ioh.status = STATUS_PROCESSED;
758 BEGIN
759 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
760 G_PKG_NAME,
761 p_init_msg_list,
762 l_api_version,
763 p_api_version,
764 '_PVT',
765 l_return_status);
766
767 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
768 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
769 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
770 RAISE OKC_API.G_EXCEPTION_ERROR;
771 END IF;
772
773 -- Processing starts
774 l_oinv_rec.id := p_interface_id;
775
776 FOR cur IN l_khr_csr(p_interface_id) LOOP
777 l_iohv_rec.object1_id1 := cur.object1_id1;
778 l_iohv_rec.object1_id2 := cur.object1_id2;
779 l_iohv_rec.jtot_object1_code := cur.jtot_object1_code;
780 l_iohv_rec.org_id := cur.org_id;
781 l_reportCB_process_found := TRUE;
782 --dbms_output.put_line('inside loop - ' || l_oihv_rec.khr_id);
783 EXIT;
784 END LOOP;
785
786 IF (l_reportCB_process_found) THEN
787 l_oinv_rec.id := p_interface_id;
788 l_oinv_rec.credit_bureau_report_date := p_report_date;
789
790 l_iohv_rec.request_date := p_report_date;
791 l_iohv_rec.process_date := p_report_date;
792 l_iohv_rec.comments := p_comments;
793
794 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
795 l_iohv_rec.comments := NULL;
796 END IF;
797
798 l_iohv_rec.action := ACTION_REPORT_CB;
799 l_iohv_rec.status := STATUS_COMPLETE;
800
801 okl_open_int_pub.update_open_int(p_api_version => l_api_version
802 ,p_init_msg_list => p_init_msg_list
803 ,x_return_status => l_return_status
804 ,x_msg_count => x_msg_count
805 ,x_msg_data => x_msg_data
806 ,p_oinv_rec => l_oinv_rec
807 ,x_oinv_rec => lx_oinv_rec);
808 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
809 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
810 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
811 RAISE okl_api.G_EXCEPTION_ERROR;
812 END IF;
813
814 iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
815 ,p_init_msg_list => p_init_msg_list
816 ,x_return_status => l_return_status
817 ,x_msg_count => x_msg_count
818 ,x_msg_data => x_msg_data
819 ,p_iohv_rec => l_iohv_rec
820 ,x_iohv_rec => lx_iohv_rec);
821 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
822 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
823 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
824 RAISE okl_api.G_EXCEPTION_ERROR;
825 END IF;
826 ELSE
827 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
828 ,p_msg_name => G_INVALID_ACTION_STATUS);
829 l_return_status := okl_api.G_RET_STS_ERROR;
830 END IF;
831 -- Processing ends
832
833 x_return_status := l_return_status;
834 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
835 EXCEPTION
836 WHEN OKC_API.G_EXCEPTION_ERROR THEN
837 x_return_status := OKC_API.HANDLE_EXCEPTIONS
838 (
839 l_api_name,
840 G_PKG_NAME,
841 'OKC_API.G_RET_STS_ERROR',
842 x_msg_count,
843 x_msg_data,
844 '_PVT'
845 );
846 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
847 x_return_status := OKC_API.HANDLE_EXCEPTIONS
848 (
849 l_api_name,
850 G_PKG_NAME,
851 'OKC_API.G_RET_STS_UNEXP_ERROR',
852 x_msg_count,
853 x_msg_data,
854 '_PVT'
855 );
856 WHEN OTHERS THEN
857 x_return_status := OKC_API.HANDLE_EXCEPTIONS
858 (
859 l_api_name,
860 G_PKG_NAME,
861 'OTHERS',
862 x_msg_count,
863 x_msg_data,
864 '_PVT'
865 );
866 END complete_report_cb;
867
868 ---------------------------------------------------------------------------
869 -- PROCEDURE complete_notify
870 ---------------------------------------------------------------------------
871 PROCEDURE complete_notify(
872 p_api_version IN NUMBER,
873 p_init_msg_list IN VARCHAR2 ,
874 p_interface_id IN NUMBER,
875 p_hst_id IN NUMBER,
876 p_notification_date IN DATE,
877 p_comments IN VARCHAR2 ,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2) AS
881
882 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
883 l_api_version CONSTANT NUMBER := 1;
884 l_api_name CONSTANT VARCHAR2(30) := 'complete_notify';
885
886 l_oinv_rec oinv_rec_type;
887 lx_oinv_rec oinv_rec_type;
888
889 l_iohv_rec iohv_rec_type;
890 lx_iohv_rec iohv_rec_type;
891
892 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
893 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
894 BEGIN
895 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
896 G_PKG_NAME,
897 p_init_msg_list,
898 l_api_version,
899 p_api_version,
900 '_PVT',
901 l_return_status);
902
903 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
904 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
905 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
906 RAISE OKC_API.G_EXCEPTION_ERROR;
907 END IF;
908
909 -- Processing starts
910 get_hst_info(p_hst_id => p_hst_id
911 ,x_action => l_action
912 ,x_status => l_status
913 ,x_return_status => l_return_status);
914
915 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
916 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
917 ,p_msg_name => G_INVALID_ACTION_STATUS);
918 END IF;
919
920 IF ((l_action = ACTION_NOTIFY_CUST) AND
921 (l_status = STATUS_PROCESSED)) THEN
922 l_oinv_rec.id := p_interface_id;
923 l_oinv_rec.notification_date := p_notification_date;
924
925 l_iohv_rec.id := p_hst_id;
926 l_iohv_rec.comments := p_comments;
927
928 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
929 l_iohv_rec.comments := NULL;
930 END IF;
931
932 l_iohv_rec.status := STATUS_COMPLETE;
933 l_iohv_rec.process_date := p_notification_date;
934
935 okl_open_int_pub.update_open_int(p_api_version => l_api_version
936 ,p_init_msg_list => p_init_msg_list
937 ,x_return_status => l_return_status
938 ,x_msg_count => x_msg_count
939 ,x_msg_data => x_msg_data
940 ,p_oinv_rec => l_oinv_rec
941 ,x_oinv_rec => lx_oinv_rec);
942 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
943 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
944 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
945 RAISE okl_api.G_EXCEPTION_ERROR;
946 END IF;
947
948 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
949 ,p_init_msg_list => p_init_msg_list
950 ,x_return_status => l_return_status
951 ,x_msg_count => x_msg_count
952 ,x_msg_data => x_msg_data
953 ,p_iohv_rec => l_iohv_rec
954 ,x_iohv_rec => lx_iohv_rec);
955 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
956 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
957 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
958 RAISE okl_api.G_EXCEPTION_ERROR;
959 END IF;
960 ELSE
961 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
962 ,p_msg_name => G_INVALID_ACTION_STATUS);
963 l_return_status := okl_api.G_RET_STS_ERROR;
964 END IF;
965 -- Processing ends
966
967 x_return_status := l_return_status;
968 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
969 EXCEPTION
970 WHEN OKC_API.G_EXCEPTION_ERROR THEN
971 x_return_status := OKC_API.HANDLE_EXCEPTIONS
972 (
973 l_api_name,
974 G_PKG_NAME,
975 'OKC_API.G_RET_STS_ERROR',
976 x_msg_count,
977 x_msg_data,
978 '_PVT'
979 );
980 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
981 x_return_status := OKC_API.HANDLE_EXCEPTIONS
982 (
983 l_api_name,
984 G_PKG_NAME,
985 'OKC_API.G_RET_STS_UNEXP_ERROR',
986 x_msg_count,
987 x_msg_data,
988 '_PVT'
989 );
990 WHEN OTHERS THEN
991 x_return_status := OKC_API.HANDLE_EXCEPTIONS
992 (
993 l_api_name,
994 G_PKG_NAME,
995 'OTHERS',
996 x_msg_count,
997 x_msg_data,
998 '_PVT'
999 );
1000 END complete_notify;
1001
1002 ---------------------------------------------------------------------------
1003 -- PROCEDURE complete_transfer
1004 ---------------------------------------------------------------------------
1005 PROCEDURE complete_transfer(
1006 p_api_version IN NUMBER,
1007 p_init_msg_list IN VARCHAR2 ,
1008 p_interface_id IN NUMBER,
1009 p_transfer_date IN DATE,
1010 p_comments IN VARCHAR2 ,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2) AS
1014
1015 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1016 l_api_version CONSTANT NUMBER := 1;
1017 l_api_name CONSTANT VARCHAR2(30) := 'complete_transfer';
1018
1019 l_oinv_rec oinv_rec_type;
1020 lx_oinv_rec oinv_rec_type;
1021
1022 l_iohv_rec iohv_rec_type;
1023 lx_iohv_rec iohv_rec_type;
1024
1025 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
1026 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
1027
1028 l_transfer_process_found BOOLEAN := FALSE;
1029
1030 CURSOR l_khr_csr(cp_interface_id IN NUMBER) IS
1031 SELECT oin.khr_id
1032 ,oin.org_id
1033 ,ioh.id
1034 ,ioh.object1_id1
1035 ,ioh.object1_id2
1036 ,ioh.jtot_object1_code
1037 FROM okl_open_int oin
1038 ,iex_open_int_hst ioh
1039 WHERE oin.id = cp_interface_id
1040 AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
1041 AND ioh.jtot_object1_code = 'OKX_LEASE'
1042 AND ioh.action = ACTION_TRANSFER_EXT_AGNCY
1043 AND ioh.status = STATUS_PROCESSED;
1044 BEGIN
1045 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1046 G_PKG_NAME,
1047 p_init_msg_list,
1048 l_api_version,
1049 p_api_version,
1050 '_PVT',
1051 l_return_status);
1052
1053 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1055 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1056 RAISE OKC_API.G_EXCEPTION_ERROR;
1057 END IF;
1058
1059 -- Processing starts
1060 l_oinv_rec.id := p_interface_id;
1061
1062 FOR cur IN l_khr_csr(p_interface_id) LOOP
1063 l_iohv_rec.object1_id1 := cur.object1_id1;
1064 l_iohv_rec.object1_id2 := cur.object1_id2;
1065 l_iohv_rec.jtot_object1_code := cur.jtot_object1_code;
1066 l_iohv_rec.org_id := cur.org_id;
1067 l_transfer_process_found := TRUE;
1068 --dbms_output.put_line('inside loop - ' || l_oihv_rec.khr_id);
1069 EXIT;
1070 END LOOP;
1071
1072 IF (l_transfer_process_found) THEN
1073 l_oinv_rec.id := p_interface_id;
1074 l_oinv_rec.external_agency_transfer_date := p_transfer_date;
1075
1076 l_iohv_rec.request_date := p_transfer_date;
1077 l_iohv_rec.process_date := p_transfer_date;
1078 l_iohv_rec.comments := p_comments;
1079
1080 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
1081 l_iohv_rec.comments := NULL;
1082 END IF;
1083
1084 l_iohv_rec.action := ACTION_TRANSFER_EXT_AGNCY;
1085 l_iohv_rec.status := STATUS_COMPLETE;
1086 --l_iohv_rec.transfer_days := null;
1087 --l_iohv_rec.extend_days := null;
1088
1089
1090 okl_open_int_pub.update_open_int(p_api_version => l_api_version
1091 ,p_init_msg_list => p_init_msg_list
1092 ,x_return_status => l_return_status
1093 ,x_msg_count => x_msg_count
1094 ,x_msg_data => x_msg_data
1095 ,p_oinv_rec => l_oinv_rec
1096 ,x_oinv_rec => lx_oinv_rec);
1097 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1098 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1099 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1100 RAISE okl_api.G_EXCEPTION_ERROR;
1101 END IF;
1102
1103 iex_open_int_hst_pub.insert_open_int_hst(p_api_version => l_api_version
1104 ,p_init_msg_list => p_init_msg_list
1105 ,x_return_status => l_return_status
1106 ,x_msg_count => x_msg_count
1107 ,x_msg_data => x_msg_data
1108 ,p_iohv_rec => l_iohv_rec
1109 ,x_iohv_rec => lx_iohv_rec);
1110 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1111 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1112 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1113 RAISE okl_api.G_EXCEPTION_ERROR;
1114 END IF;
1115 ELSE
1116 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1117 ,p_msg_name => G_INVALID_ACTION_STATUS);
1118 l_return_status := okl_api.G_RET_STS_ERROR;
1119 END IF;
1120
1121 -- Processing ends
1122
1123 x_return_status := l_return_status;
1124 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1125 EXCEPTION
1126 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1127 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1128 (
1129 l_api_name,
1130 G_PKG_NAME,
1131 'OKC_API.G_RET_STS_ERROR',
1132 x_msg_count,
1133 x_msg_data,
1134 '_PVT'
1135 );
1136 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1137 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'OKC_API.G_RET_STS_UNEXP_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_PVT'
1145 );
1146 WHEN OTHERS THEN
1147 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OTHERS',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 END complete_transfer;
1157
1158 ---------------------------------------------------------------------------
1159 -- PROCEDURE complete_notify_ext_agncy
1160 ---------------------------------------------------------------------------
1161 PROCEDURE complete_notify_ext_agncy(
1162 p_api_version IN NUMBER,
1163 p_init_msg_list IN VARCHAR2 ,
1164 p_interface_id IN NUMBER,
1165 p_hst_id IN NUMBER,
1166 p_notification_date IN DATE,
1167 p_comments IN VARCHAR2 ,
1168 x_return_status OUT NOCOPY VARCHAR2,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2) AS
1171
1172 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1173 l_api_version CONSTANT NUMBER := 1;
1174 l_api_name CONSTANT VARCHAR2(30) := 'complete_notify_ext_agncy';
1175
1176 l_oinv_rec oinv_rec_type;
1177 lx_oinv_rec oinv_rec_type;
1178
1179 l_iohv_rec iohv_rec_type;
1180 lx_iohv_rec iohv_rec_type;
1181
1182 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
1183 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
1184 BEGIN
1185 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1186 G_PKG_NAME,
1187 p_init_msg_list,
1188 l_api_version,
1189 p_api_version,
1190 '_PVT',
1191 l_return_status);
1192
1193 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1194 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1195 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1196 RAISE OKC_API.G_EXCEPTION_ERROR;
1197 END IF;
1198
1199 -- Processing starts
1200 get_hst_info(p_hst_id => p_hst_id
1201 ,x_action => l_action
1202 ,x_status => l_status
1203 ,x_return_status => l_return_status);
1204
1205 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1206 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1207 ,p_msg_name => G_INVALID_ACTION_STATUS);
1208 END IF;
1209
1210 IF ((l_action = ACTION_NOTIFY_EXT_AGNCY) AND
1211 (l_status = STATUS_PROCESSED)) THEN
1212 /*
1213 l_oinv_rec.id := p_interface_id;
1214 l_oinv_rec.notification_date := p_notification_date;
1215 */
1216
1217 l_iohv_rec.id := p_hst_id;
1218 l_iohv_rec.comments := p_comments;
1219
1220 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
1221 l_iohv_rec.comments := NULL;
1222 END IF;
1223
1224 l_iohv_rec.status := STATUS_COMPLETE;
1225 l_iohv_rec.process_date := p_notification_date;
1226
1227 /*
1228 okl_open_int_pub.update_open_int(p_api_version => l_api_version
1229 ,p_init_msg_list => p_init_msg_list
1230 ,x_return_status => l_return_status
1231 ,x_msg_count => x_msg_count
1232 ,x_msg_data => x_msg_data
1233 ,p_oinv_rec => l_oinv_rec
1234 ,x_oinv_rec => lx_oinv_rec);
1235 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1236 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1237 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1238 RAISE okl_api.G_EXCEPTION_ERROR;
1239 END IF;
1240 */
1241
1242 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
1243 ,p_init_msg_list => p_init_msg_list
1244 ,x_return_status => l_return_status
1245 ,x_msg_count => x_msg_count
1246 ,x_msg_data => x_msg_data
1247 ,p_iohv_rec => l_iohv_rec
1248 ,x_iohv_rec => lx_iohv_rec);
1249 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1252 RAISE okl_api.G_EXCEPTION_ERROR;
1253 END IF;
1254 ELSE
1255 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1256 ,p_msg_name => G_INVALID_ACTION_STATUS);
1257 l_return_status := okl_api.G_RET_STS_ERROR;
1258 END IF;
1259 -- Processing ends
1260
1261 x_return_status := l_return_status;
1262 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1263 EXCEPTION
1264 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1265 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1266 (
1267 l_api_name,
1268 G_PKG_NAME,
1269 'OKC_API.G_RET_STS_ERROR',
1270 x_msg_count,
1271 x_msg_data,
1272 '_PVT'
1273 );
1274 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1275 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1276 (
1277 l_api_name,
1278 G_PKG_NAME,
1279 'OKC_API.G_RET_STS_UNEXP_ERROR',
1280 x_msg_count,
1281 x_msg_data,
1282 '_PVT'
1283 );
1284 WHEN OTHERS THEN
1285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1286 (
1287 l_api_name,
1288 G_PKG_NAME,
1289 'OTHERS',
1290 x_msg_count,
1291 x_msg_data,
1292 '_PVT'
1293 );
1294 END complete_notify_ext_agncy;
1295
1296 ---------------------------------------------------------------------------
1297 -- PROCEDURE recall_transfer
1298 ---------------------------------------------------------------------------
1299 PROCEDURE recall_transfer(
1300 p_api_version IN NUMBER,
1301 p_init_msg_list IN VARCHAR2 ,
1302 p_interface_id IN NUMBER,
1303 p_recall_date IN DATE,
1304 p_comments IN VARCHAR2 ,
1305 p_ext_agncy_id IN NUMBER ,
1306 x_return_status OUT NOCOPY VARCHAR2,
1307 x_msg_count OUT NOCOPY NUMBER,
1308 x_msg_data OUT NOCOPY VARCHAR2) AS
1309
1310 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
1311 lx_msg_count NUMBER ;
1312 lx_msg_data VARCHAR2(2000);
1313 lx_message VARCHAR2(2000);
1314
1315 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1316 l_api_version CONSTANT NUMBER := 1;
1317 l_api_name CONSTANT VARCHAR2(30) := 'recall_transfer';
1318
1319 l_oinv_rec oinv_rec_type;
1320 lx_oinv_rec oinv_rec_type;
1321
1322 l_iohv_rec iohv_rec_type;
1323 lx_iohv_rec iohv_rec_type;
1324 lp_iohv_rec iohv_rec_type;
1325
1326 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
1327 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
1328 l_ext_agncy_id IEX_OPEN_INT_HST.EXT_AGNCY_ID%TYPE;
1329
1330 CURSOR l_khr_csr(cp_interface_id IN NUMBER) IS
1331 SELECT oin.khr_id
1332 ,oin.org_id
1333 ,oin.referral_number
1334 ,ioh.id
1335 ,ioh.object1_id1
1336 ,ioh.object1_id2
1337 ,ioh.jtot_object1_code
1338 ,ioh.ext_agncy_id
1339 FROM okl_open_int oin
1340 ,iex_open_int_hst ioh
1341 WHERE oin.id = cp_interface_id
1342 AND oin.khr_id = TO_NUMBER(ioh.object1_id1)
1343 AND ioh.jtot_object1_code = 'OKX_LEASE'
1344 AND ioh.action = ACTION_TRANSFER_EXT_AGNCY
1345 AND (ioh.status = STATUS_PROCESSED
1346 OR ioh.status = STATUS_NOTIFIED);
1347 BEGIN
1348 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1349 G_PKG_NAME,
1350 p_init_msg_list,
1351 l_api_version,
1352 p_api_version,
1353 '_PVT',
1354 l_return_status);
1355
1356 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1359 RAISE OKC_API.G_EXCEPTION_ERROR;
1360 END IF;
1361
1362 -- Processing starts
1363 l_oinv_rec.id := p_interface_id;
1364 l_oinv_rec.external_agency_recall_date := p_recall_date;
1365
1366 FOR cur_khr_csr IN l_khr_csr(p_interface_id) LOOP
1367 l_oinv_rec.referral_number := cur_khr_csr.referral_number;
1368 l_iohv_rec.id := cur_khr_csr.id;
1369 l_iohv_rec.object1_id1 := cur_khr_csr.object1_id1;
1370 l_iohv_rec.object1_id2 := cur_khr_csr.object1_id2;
1371 l_iohv_rec.jtot_object1_code := cur_khr_csr.jtot_object1_code;
1372 EXIT;
1373 END LOOP;
1374 l_iohv_rec.comments := p_comments;
1375
1376 IF l_iohv_rec.comments = OKC_API.G_MISS_CHAR THEN
1377 l_iohv_rec.comments := NULL;
1378 END IF;
1379
1380 l_iohv_rec.status := STATUS_RECALLED;
1381 l_iohv_rec.process_date := p_recall_date;
1382
1383 l_oinv_rec.referral_number := nvl(l_oinv_rec.referral_number, 0) + 1;
1384 okl_open_int_pub.update_open_int(p_api_version => l_api_version
1385 ,p_init_msg_list => p_init_msg_list
1386 ,x_return_status => l_return_status
1387 ,x_msg_count => x_msg_count
1388 ,x_msg_data => x_msg_data
1389 ,p_oinv_rec => l_oinv_rec
1390 ,x_oinv_rec => lx_oinv_rec);
1391 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1392 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1393 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1394 RAISE okl_api.G_EXCEPTION_ERROR;
1395 END IF;
1396
1397 IF (p_ext_agncy_id = OKC_API.G_MISS_NUM) THEN
1398 l_ext_agncy_id := NULL;
1399 ELSE
1400 l_ext_agncy_id := p_ext_agncy_id;
1401 END IF;
1402
1403 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
1404 ,p_init_msg_list => p_init_msg_list
1405 ,x_return_status => l_return_status
1406 ,x_msg_count => x_msg_count
1407 ,x_msg_data => x_msg_data
1408 ,p_iohv_rec => l_iohv_rec
1409 ,x_iohv_rec => lx_iohv_rec);
1410 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1411 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1413 RAISE okl_api.G_EXCEPTION_ERROR;
1414 END IF;
1415
1416 l_iohv_rec := lx_iohv_rec;
1417
1418 lp_iohv_rec := l_iohv_rec;
1419 lp_iohv_rec.id := null;
1420 lp_iohv_rec.action := ACTION_NOTIFY_RECALL;
1421 lp_iohv_rec.status := STATUS_PROCESSED;
1422 lp_iohv_rec.request_date := SYSDATE;
1423 lp_iohv_rec.process_date := SYSDATE;
1424 lp_iohv_rec.review_date := NULL;
1425 lp_iohv_rec.recall_date := NULL;
1426 lp_iohv_rec.automatic_recall_flag := NULL;
1427 lp_iohv_rec.review_before_recall_flag := NULL;
1428
1429 iex_open_int_hst_pub.insert_open_int_hst(
1430 p_api_version => l_api_version,
1431 p_init_msg_list => p_init_msg_list,
1432 x_return_status => l_return_status,
1433 x_msg_count => lx_msg_count,
1434 x_msg_data => lx_msg_data,
1435 p_iohv_rec => lp_iohv_rec,
1436 x_iohv_rec => lx_iohv_rec);
1437
1438 /*
1439 iex_open_interface_pub.insert_pending(
1440 p_api_version => l_api_version,
1441 p_init_msg_list => l_init_msg_list,
1442 p_object1_id1 => l_iohv_rec.object1_id1,
1443 p_object1_id2 => l_iohv_rec.object1_id2,
1444 p_jtot_object1_code => l_iohv_rec.jtot_object1_code,
1445 p_action => ACTION_NOTIFY_EXT_AGNCY,
1446 p_status => STATUS_PROCESSED,
1447 p_comments => p_comments,
1448 p_ext_agncy_id => l_iohv_rec.ext_agncy_id,
1449 p_review_date => NULL,
1450 p_recall_date => NULL,
1451 p_automatic_recall_flag => NULL,
1452 p_review_before_recall_flag => NULL,
1453 x_return_status => l_return_status,
1454 x_msg_count => lx_msg_count,
1455 x_msg_data => lx_msg_data);
1456
1457 l_iohv_rec := lx_iohv_rec;
1458 l_iohv_rec.process_date := SYSDATE;
1459
1460 iex_open_int_hst_pub.update_open_int_hst(
1461 p_api_version => l_api_version,
1462 p_init_msg_list => l_init_msg_list,
1463 x_return_status => l_return_status,
1464 x_msg_count => lx_msg_count,
1465 x_msg_data => lx_msg_data,
1466 p_iohv_rec => l_iohv_rec,
1467 x_iohv_rec => lx_iohv_rec);
1468 */
1469
1470 -- Processing ends
1471
1472 x_return_status := l_return_status;
1473 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1474 EXCEPTION
1475 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1476 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1477 (
1478 l_api_name,
1479 G_PKG_NAME,
1480 'OKC_API.G_RET_STS_ERROR',
1481 x_msg_count,
1482 x_msg_data,
1483 '_PVT'
1484 );
1485 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1486 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1487 (
1488 l_api_name,
1489 G_PKG_NAME,
1490 'OKC_API.G_RET_STS_UNEXP_ERROR',
1491 x_msg_count,
1492 x_msg_data,
1493 '_PVT'
1494 );
1495 WHEN OTHERS THEN
1496 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1497 (
1498 l_api_name,
1499 G_PKG_NAME,
1500 'OTHERS',
1501 x_msg_count,
1502 x_msg_data,
1503 '_PVT'
1504 );
1505 END recall_transfer;
1506
1507 ---------------------------------------------------------------------------
1508 -- PROCEDURE review_transfer
1509 ---------------------------------------------------------------------------
1510 PROCEDURE review_transfer(
1511 p_api_version IN NUMBER,
1512 p_init_msg_list IN VARCHAR2 ,
1513 p_oinv_rec IN oinv_rec_type,
1514 p_iohv_rec IN iohv_rec_type,
1515 x_oinv_rec OUT NOCOPY oinv_rec_type,
1516 x_iohv_rec OUT NOCOPY iohv_rec_type,
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_msg_count OUT NOCOPY NUMBER,
1519 x_msg_data OUT NOCOPY VARCHAR2) AS
1520
1521 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
1522 lx_msg_count NUMBER ;
1523 lx_msg_data VARCHAR2(2000);
1524 lx_message VARCHAR2(2000);
1525
1526 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1527 l_api_version CONSTANT NUMBER := 1;
1528 l_api_name CONSTANT VARCHAR2(30) := 'review_transfer';
1529
1530 l_recall VARCHAR2(1) := Okc_Api.G_FALSE;
1531 l_oinv_rec oinv_rec_type;
1532 lx_oinv_rec oinv_rec_type;
1533 l_iohv_rec iohv_rec_type;
1534 lp_iohv_rec iohv_rec_type;
1535 lx_iohv_rec iohv_rec_type;
1536 l_task_name JTF_TASKS_VL.TASK_NAME%TYPE;
1537 l_description JTF_TASKS_VL.DESCRIPTION%TYPE;
1538 BEGIN
1539 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1540 G_PKG_NAME,
1541 p_init_msg_list,
1542 l_api_version,
1543 p_api_version,
1544 '_PVT',
1545 l_return_status);
1546
1547 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1548 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1549 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1550 RAISE OKC_API.G_EXCEPTION_ERROR;
1551 END IF;
1552
1553 -- Processing starts
1554 l_recall := OKC_API.G_FALSE;
1555
1556 l_oinv_rec := p_oinv_rec;
1557 l_iohv_rec := p_iohv_rec;
1558 --check whether contract is to be recalled
1559 get_contract_recall(p_oinv_rec => l_oinv_rec,
1560 p_iohv_rec => l_iohv_rec,
1561 x_recall => l_recall,
1562 x_return_status => l_return_status);
1563
1564 IF (l_recall = OKC_API.G_TRUE) THEN
1565 l_iohv_rec.status := STATUS_NOTIFIED;
1566 l_iohv_rec.process_date := SYSDATE;
1567 --l_iohv_rec.comments := p_comments;
1568
1569 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
1570 ,p_init_msg_list => l_init_msg_list
1571 ,x_return_status => l_return_status
1572 ,x_msg_count => lx_msg_count
1573 ,x_msg_data => lx_msg_data
1574 ,p_iohv_rec => l_iohv_rec
1575 ,x_iohv_rec => lx_iohv_rec);
1576
1577 x_oinv_rec := lx_oinv_rec;
1578 x_iohv_rec := lx_iohv_rec;
1579 l_iohv_rec := lx_iohv_rec;
1580
1581 lp_iohv_rec := l_iohv_rec;
1582 lp_iohv_rec.id := null;
1583 lp_iohv_rec.action := ACTION_RECALL_NOTICE;
1584 lp_iohv_rec.status := STATUS_PROCESSED;
1585 lp_iohv_rec.request_date := SYSDATE;
1586 lp_iohv_rec.process_date := SYSDATE;
1587 lp_iohv_rec.review_date := NULL;
1588 --lp_iohv_rec.recall_date := NULL;
1589 lp_iohv_rec.automatic_recall_flag := NULL;
1590 lp_iohv_rec.review_before_recall_flag := NULL;
1591
1592 iex_open_int_hst_pub.insert_open_int_hst(
1593 p_api_version => l_api_version,
1594 p_init_msg_list => l_init_msg_list,
1595 x_return_status => l_return_status,
1596 x_msg_count => lx_msg_count,
1597 x_msg_data => lx_msg_data,
1598 p_iohv_rec => lp_iohv_rec,
1599 x_iohv_rec => lx_iohv_rec);
1600
1601 l_task_name := 'Oracle Collections Review Transfer to External Agency';
1602 l_description := 'Oracle Collections Review contract before recalling from external agency to which it is transferred.';
1603 create_followup(p_api_version => l_api_version,
1604 p_init_msg_list => l_init_msg_list,
1605 p_oinv_rec => l_oinv_rec,
1606 p_iohv_rec => l_iohv_rec,
1607 p_task_name => l_task_name,
1608 p_description => l_description,
1609 x_return_status => l_return_status,
1610 x_msg_count => lx_msg_count,
1611 x_msg_data => lx_msg_data);
1612 END IF;
1613
1614 -- Processing ends
1615
1616 x_return_status := l_return_status;
1617 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1618 EXCEPTION
1619 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1620 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1621 (
1622 l_api_name,
1623 G_PKG_NAME,
1624 'OKC_API.G_RET_STS_ERROR',
1625 x_msg_count,
1626 x_msg_data,
1627 '_PVT'
1628 );
1629 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1630 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1631 (
1632 l_api_name,
1633 G_PKG_NAME,
1634 'OKC_API.G_RET_STS_UNEXP_ERROR',
1635 x_msg_count,
1636 x_msg_data,
1637 '_PVT'
1638 );
1639 WHEN OTHERS THEN
1640 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OTHERS',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 END review_transfer;
1650
1651 ---------------------------------------------------------------------------
1652 -- PROCEDURE create_followup
1653 ---------------------------------------------------------------------------
1654 PROCEDURE create_followup(
1655 p_api_version IN NUMBER,
1656 p_init_msg_list IN VARCHAR2 ,
1657 p_oinv_rec IN oinv_rec_type,
1658 p_iohv_rec IN iohv_rec_type,
1659 p_task_name IN VARCHAR2,
1660 p_description IN VARCHAR2,
1661 p_start_date IN DATE ,
1662 x_return_status OUT NOCOPY VARCHAR2,
1663 x_msg_count OUT NOCOPY NUMBER,
1664 x_msg_data OUT NOCOPY VARCHAR2) AS
1665
1666 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1667 l_api_version CONSTANT NUMBER := 1;
1668 l_api_name CONSTANT VARCHAR2(30) := 'create_followup';
1669
1670 l_msg_count NUMBER;
1671 l_msg_data VARCHAR2(32767);
1672
1673 l_task_id NUMBER;
1674 l_task_name varchar2(80) ;
1675 l_task_type_id NUMBER ;
1676 l_task_type varchar2(30) ;
1677 l_task_status_id NUMBER ;
1678 l_task_status varchar2(30) ;
1679 l_description varchar2(4000);
1680 l_task_priority_name varchar2(30) ;
1681 l_task_priority_id number;
1682 l_owner_id number;
1683 l_owner varchar2(4000);
1684 l_owner_type_code varchar2(4000);
1685 l_customer_id number;
1686 l_address_id number;
1687 l_start_date date;
1688
1689 l_contract_id OKL_OPEN_INT.KHR_ID%TYPE;
1690 l_contract_number OKL_OPEN_INT.CONTRACT_NUMBER%TYPE;
1691
1692 v_miss_task_assign_tbl Jtf_Tasks_Pub.TASK_ASSIGN_TBL;
1693 v_miss_task_depends_tbl Jtf_Tasks_Pub.TASK_DEPENDS_TBL;
1694 v_miss_task_rsrc_req_tbl Jtf_Tasks_Pub.TASK_RSRC_REQ_TBL;
1695 v_miss_task_refer_tbl Jtf_Tasks_Pub.TASK_REFER_TBL;
1696 v_miss_task_dates_tbl Jtf_Tasks_Pub.TASK_DATES_TBL;
1697 v_miss_task_notes_tbl Jtf_Tasks_Pub.TASK_NOTES_TBL;
1698 v_miss_task_recur_rec Jtf_Tasks_Pub.TASK_RECUR_REC;
1699 v_miss_task_contacts_tbl Jtf_Tasks_Pub.TASK_CONTACTS_TBL;
1700
1701 CURSOR l_oin_csr(cp_contract_id IN NUMBER) IS
1702 SELECT contract_number
1703 ,party_id
1704 FROM OKL_OPEN_INT
1705 WHERE khr_id = cp_contract_id;
1706
1707 CURSOR l_task_type_csr IS
1708 SELECT TASK_TYPE_ID,
1709 NAME
1710 FROM JTF_TASK_TYPES_VL
1711 WHERE NAME = 'Follow up action';
1712
1713 CURSOR l_task_status_csr IS
1714 SELECT TASK.task_status_id,
1715 TASK.name
1716 FROM jtf_task_statuses_vl TASK
1717 WHERE TRUNC(SYSDATE)
1718 BETWEEN TRUNC(NVL(TASK.start_date_active, SYSDATE))
1719 AND TRUNC(NVL(TASK.end_date_active, SYSDATE))
1720 AND TASK.name = 'Open';
1721
1722 CURSOR l_case_owner_csr(cp_case_id IN NUMBER) IS
1723 SELECT owner_resource_id
1724 FROM iex_cases_all_b
1725 WHERE cas_id = cp_case_id;
1726
1727 CURSOR l_task_priority_csr IS
1728 SELECT task_priority_id,
1729 name
1730 FROM jtf_task_priorities_vl
1731 WHERE TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
1732 AND TRUNC(NVL(end_date_active, SYSDATE))
1733 AND name = 'High';
1734
1735 BEGIN
1736 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1737 G_PKG_NAME,
1738 p_init_msg_list,
1739 l_api_version,
1740 p_api_version,
1741 '_PVT',
1742 l_return_status);
1743
1744 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1745 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1746 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1747 RAISE OKC_API.G_EXCEPTION_ERROR;
1748 END IF;
1749
1750 -- Processing starts
1751
1752 --get the contract number, customer_id
1753 l_contract_id := p_oinv_rec.khr_id;
1754 OPEN l_oin_csr(l_contract_id);
1755 FETCH l_oin_csr INTO l_contract_number, l_customer_id;
1756 CLOSE l_oin_csr;
1757
1758 --get the task type name, task type id
1759 OPEN l_task_type_csr;
1760 FETCH l_task_type_csr INTO l_task_type_id, l_task_type;
1761 CLOSE l_task_type_csr;
1762
1763 --get task status id, task status name
1764 OPEN l_task_status_csr;
1765 FETCH l_task_status_csr INTO l_task_status_id, l_task_status;
1766 CLOSE l_task_status_csr;
1767
1768 --get task priority id, task priority name
1769 OPEN l_task_priority_csr;
1770 FETCH l_task_priority_csr INTO l_task_priority_id, l_task_priority_name;
1771 CLOSE l_task_priority_csr;
1772
1773 --l_task_name := 'Oracle Collections Review Transfer to External Agency';
1774 l_task_name := p_task_name;
1775 l_task_status := 'Open';
1776 l_task_type := 'Follow up action';
1777 --l_description := 'Oracle Collections Review contract before recalling from external agency to which it is transferred.';
1778 l_description := p_description;
1779 l_owner_type_code := 'RS_EMPLOYEE';
1780 l_start_date := p_start_date;
1781
1782 --Use the foll query to get an employee resource
1783 --select resource_id, first_name, last_name from jtf_rs_emp_dtls_vl to test
1784 --Right now it is hard coded
1785 --set the value to the case owner resource id
1786 OPEN l_case_owner_csr(p_oinv_rec.cas_id);
1787 FETCH l_case_owner_csr INTO l_owner_id;
1788 CLOSE l_case_owner_csr;
1789
1790 --l_owner_id := 100000803;
1791
1792 Jtf_Tasks_Pub.CREATE_TASK(
1793 P_API_VERSION => p_api_version,
1794 P_INIT_MSG_LIST => Okc_Api.g_true,
1795 P_COMMIT => Okc_Api.g_true,
1796 P_TASK_ID => NULL,
1797 P_TASK_NAME => l_task_name,
1798 P_TASK_TYPE_NAME => l_task_type,
1799 P_TASK_TYPE_ID => l_task_type_id,
1800 P_DESCRIPTION => l_description,
1801 P_TASK_STATUS_NAME => l_task_status,
1802 P_TASK_STATUS_ID => l_task_status_id,
1803 P_TASK_PRIORITY_NAME => l_task_priority_name,
1804 P_TASK_PRIORITY_ID => l_task_priority_id,
1805 p_owner_type_name => Null,
1806 P_OWNER_TYPE_CODE => l_owner_type_code,
1807 P_OWNER_ID => l_owner_id,
1808 P_OWNER_TERRITORY_ID => NULL,
1809 p_assigned_by_name => NULL,
1810 P_ASSIGNED_BY_ID => NULL,
1811 p_customer_number => NULL,
1812 P_CUSTOMER_ID => l_customer_id,
1813 p_cust_account_number => NULL,
1814 P_CUST_ACCOUNT_ID => NULL,
1815 P_ADDRESS_ID => NULL,
1816 p_address_number => NULL,
1817 P_PLANNED_START_DATE => l_start_date,
1818 P_PLANNED_END_DATE => NULL,
1819 P_SCHEDULED_START_DATE => NULL,
1820 P_SCHEDULED_END_DATE => NULL,
1821 P_ACTUAL_START_DATE => NULL,
1822 P_ACTUAL_END_DATE => NULL,
1823 P_TIMEZONE_ID => NULL,
1824 p_timezone_name => NULL,
1825 P_SOURCE_OBJECT_TYPE_CODE => 'IEX_K_HEADER',
1826 P_SOURCE_OBJECT_ID => l_contract_id,
1827 P_SOURCE_OBJECT_NAME => l_contract_number,
1828 P_DURATION => NULL,
1829 P_DURATION_UOM => NULL,
1830 P_PLANNED_EFFORT => NULL,
1831 P_PLANNED_EFFORT_UOM => NULL,
1832 P_ACTUAL_EFFORT => NULL,
1833 P_ACTUAL_EFFORT_UOM => NULL,
1834 P_PERCENTAGE_COMPLETE => NULL,
1835 P_REASON_CODE => NULL,
1836 P_PRIVATE_FLAG => NULL,
1837 P_PUBLISH_FLAG => NULL,
1838 P_RESTRICT_CLOSURE_FLAG => NULL,
1839 P_MULTI_BOOKED_FLAG => NULL,
1840 P_MILESTONE_FLAG => NULL,
1841 P_HOLIDAY_FLAG => NULL,
1842 P_BILLABLE_FLAG => NULL,
1843 P_BOUND_MODE_CODE => NULL,
1844 P_SOFT_BOUND_FLAG => NULL,
1845 P_WORKFLOW_PROCESS_ID => Null,
1846 P_NOTIFICATION_FLAG => NULL,
1847 P_NOTIFICATION_PERIOD => NULL,
1848 P_NOTIFICATION_PERIOD_UOM => NULL,
1849 p_parent_task_number => NULL,
1850 P_PARENT_TASK_ID => NULL,
1851 P_ALARM_START => NULL,
1852 P_ALARM_START_UOM => NULL,
1853 P_ALARM_ON => NULL,
1854 P_ALARM_COUNT => NULL,
1855 P_ALARM_INTERVAL => NULL,
1856 P_ALARM_INTERVAL_UOM => NULL,
1857 P_PALM_FLAG => NULL,
1858 P_WINCE_FLAG => NULL,
1859 P_LAPTOP_FLAG => NULL,
1860 P_DEVICE1_FLAG => NULL,
1861 P_DEVICE2_FLAG => NULL,
1862 P_DEVICE3_FLAG => NULL,
1863 P_COSTS => NULL,
1864 P_CURRENCY_CODE => NULL,
1865 P_ESCALATION_LEVEL => NULL,
1866 p_task_assign_tbl => v_miss_task_assign_tbl,
1867 p_task_depends_tbl => v_miss_task_depends_tbl,
1868 p_task_rsrc_req_tbl => v_miss_task_rsrc_req_tbl,
1869 p_task_refer_tbl => v_miss_task_refer_tbl,
1870 p_task_dates_tbl => v_miss_task_dates_tbl,
1871 p_task_notes_tbl => v_miss_task_notes_tbl,
1872 p_task_recur_rec => v_miss_task_recur_rec,
1873 p_task_contacts_tbl => v_miss_task_contacts_tbl,
1874 x_return_status => x_return_status,
1875 x_msg_count => x_msg_count,
1876 x_msg_data => x_msg_data,
1877 x_task_id => l_task_id,
1878 P_ATTRIBUTE1 => NULL,
1879 P_ATTRIBUTE2 => NULL,
1880 P_ATTRIBUTE3 => NULL,
1881 P_ATTRIBUTE4 => NULL,
1882 P_ATTRIBUTE5 => NULL,
1883 P_ATTRIBUTE6 => NULL,
1884 P_ATTRIBUTE7 => NULL,
1885 P_ATTRIBUTE8 => NULL,
1886 P_ATTRIBUTE9 => NULL,
1887 P_ATTRIBUTE10 => NULL,
1888 P_ATTRIBUTE11 => NULL,
1889 P_ATTRIBUTE12 => NULL,
1890 P_ATTRIBUTE13 => NULL,
1891 P_ATTRIBUTE14 => NULL,
1892 P_ATTRIBUTE15 => NULL,
1893 P_ATTRIBUTE_CATEGORY => NULL );
1894
1895
1896 --Begin bug#5246309 schekuri 29-Jun-2006
1897 --Added log message when failed.
1898 IF x_return_status<>fnd_api.g_ret_sts_success THEN
1899 IF l_owner_id IS NULL THEN
1900 fnd_file.put_line(FND_FILE.LOG, ' Task creation failed due to invalid owner for contract '||l_contract_number);
1901 ELSE
1902 fnd_file.put_line(FND_FILE.LOG, ' Task creation failed for contract '||l_contract_number);
1903 END IF;
1904 END IF;
1905 --End bug#5246309 schekuri 29-Jun-2006
1906
1907 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1908 OKL_API.SET_MESSAGE(G_APP_NAME, G_TASK_CREATION_FAILURE);
1909 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1910 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1911 OKL_API.SET_MESSAGE(G_APP_NAME, G_TASK_CREATION_FAILURE);
1912 RAISE okl_api.G_EXCEPTION_ERROR;
1913 END IF;
1914
1915 -- Processing ends
1916
1917 x_return_status := l_return_status;
1918 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1919 EXCEPTION
1920 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1921 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1922 (
1923 l_api_name,
1924 G_PKG_NAME,
1925 'OKC_API.G_RET_STS_ERROR',
1926 x_msg_count,
1927 x_msg_data,
1928 '_PVT'
1929 );
1930 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1931 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1932 (
1933 l_api_name,
1934 G_PKG_NAME,
1935 'OKC_API.G_RET_STS_UNEXP_ERROR',
1936 x_msg_count,
1937 x_msg_data,
1938 '_PVT'
1939 );
1940 WHEN OTHERS THEN
1941 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1942 (
1943 l_api_name,
1944 G_PKG_NAME,
1945 'OTHERS',
1946 x_msg_count,
1947 x_msg_data,
1948 '_PVT'
1949 );
1950 END create_followup;
1951
1952 ---------------------------------------------------------------------------
1953 -- PROCEDURE notify_customer
1954 ---------------------------------------------------------------------------
1955 PROCEDURE notify_customer(
1956 errbuf OUT NOCOPY VARCHAR2,
1957 retcode OUT NOCOPY NUMBER,
1958 p_case_number IN VARCHAR2,
1959 p_party_id IN NUMBER,
1960 p_agent_id IN NUMBER,
1961 p_content_id IN VARCHAR2,
1962 p_from IN VARCHAR2,
1963 p_subject IN VARCHAR2,
1964 p_email IN VARCHAR2) AS
1965
1966 lx_msg_count NUMBER ;
1967 lx_msg_data VARCHAR2(2000);
1968 lx_message VARCHAR2(2000);
1969 l_api_version CONSTANT NUMBER := 1;
1970 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1971 l_api_name CONSTANT VARCHAR2(30) := 'notify_customer';
1972 l_rows_processed NUMBER := 0;
1973 l_rows_failed NUMBER := 0;
1974 l_cust_notified NUMBER := 0;
1975 l_cust_not_notified NUMBER := 0;
1976
1977 /*
1978 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1979 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1980 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1981 */
1982
1983 l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
1984
1985 l_oinv_rec oinv_rec_type;
1986 l_iohv_rec iohv_rec_type;
1987
1988 l_case_number OKL_OPEN_INT.CASE_NUMBER%TYPE;
1989 l_contract_id OKL_OPEN_INT.KHR_ID%TYPE;
1990 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
1991 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
1992 l_subject VARCHAR2(2000);
1993 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE;
1994 l_from VARCHAR2(2000);
1995 l_agent_id NUMBER;
1996 l_request_id NUMBER;
1997 l_task_name JTF_TASKS_VL.TASK_NAME%TYPE;
1998 l_description JTF_TASKS_VL.DESCRIPTION%TYPE;
1999 l_start_date Date;
2000
2001 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
2002 l_party_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2003 l_case_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2004
2005 CURSOR l_notify_csr(cp_case_number IN VARCHAR2
2006 ,cp_party_id IN NUMBER
2007 ,cp_organization_id IN NUMBER) IS
2008 SELECT OIN.ID,
2009 OIN.KHR_ID,
2010 OIN.CAS_ID,
2011 OIN.PARTY_ID,
2012 OIN.PARTY_NAME,
2013 IOH.ID
2014 FROM Okl_Open_Int OIN
2015 ,Iex_Open_Int_Hst IOH
2016 WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
2017 AND IOH.jtot_object1_code = 'OKX_LEASE'
2018 AND (IOH.ACTION = ACTION_NOTIFY_CUST)
2019 AND (IOH.STATUS = STATUS_PROCESSED)
2020 AND ((l_case_passed = Okc_Api.G_FALSE) OR
2021 (l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
2022 AND ((l_party_passed = Okc_Api.G_FALSE) OR
2023 (l_party_passed = Okc_Api.G_TRUE AND OIN.party_id = cp_party_id))
2024 AND (OIN.org_id = cp_organization_id);
2025 l_contact_destination varchar2(240); -- bug 3955222
2026 l_contact_party_id number; -- bug 3955222
2027 BEGIN
2028 --check to see that contract id is passed when email is passed
2029 IF NOT (p_email = OKC_API.G_MISS_CHAR OR p_email IS NULL) THEN
2030 IF (p_case_number = OKC_API.G_MISS_CHAR OR p_case_number IS NULL) THEN
2031 RAISE G_INVALID_PARAMETERS;
2032 END IF;
2033 END IF;
2034
2035 --get organization id
2036 --Begin Bug#5373556 schekuri 12-Jul-2006
2037 l_organization_id := mo_global.get_current_org_id;
2038 --l_organization_id := fnd_profile.value('ORG_ID');
2039 --End Bug#5373556 schekuri 12-Jul-2006
2040
2041 /*
2042 l_organization_id := okl_context.get_okc_org_id;
2043 --dbms_output.put_line('org is : ' || l_organization_id);
2044
2045 --check if organization_id is null, set it from the profile
2046 IF (l_organization_id IS NULL) THEN
2047 --dbms_output.put_line('org is not set');
2048 okl_context.set_okc_org_context(null,null);
2049 --dbms_output.put_line('org is now set');
2050 l_organization_id := okl_context.get_okc_org_id;
2051 --dbms_output.put_line('org is : ' || l_organization_id);
2052 END IF;
2053 */
2054
2055 --check if case nbr is passed
2056 IF (p_case_number = OKC_API.G_MISS_CHAR OR
2057 p_case_number IS NULL) THEN
2058 l_case_passed := Okc_Api.G_FALSE;
2059 END IF;
2060
2061 --check if party_id is passed
2062 IF (p_party_id = OKC_API.G_MISS_NUM OR
2063 p_party_id IS NULL) THEN
2064 l_party_passed := Okc_Api.G_FALSE;
2065 END IF;
2066
2067 l_subject := p_subject;
2068 l_content_id := p_content_id;
2069 l_agent_id := NVL(p_agent_id, FND_GLOBAL.USER_ID);
2070 l_from := p_from;
2071
2072 --if suject is null get subject
2073 IF (l_subject = OKC_API.G_MISS_CHAR OR
2074 l_subject IS NULL) THEN
2075 l_subject := fnd_profile.value('IEX_CB_NOTIFY_CUST_EMAIL_SUBJECT');
2076 END IF;
2077 --dbms_output.put_line('l_subject : ' || l_subject);
2078
2079 --if content_id is null get content_id
2080 IF (l_content_id = OKC_API.G_MISS_NUM OR
2081 l_content_id IS NULL) THEN
2082 l_content_id := to_number(fnd_profile.value('IEX_CB_NOTIFY_CUST_TEMPLATE'));
2083 END IF;
2084 --dbms_output.put_line('l_content_id : ' || l_content_id);
2085
2086 --if from is null get subject
2087 IF (l_from = OKC_API.G_MISS_CHAR OR
2088 l_from IS NULL) THEN
2089 l_from := fnd_profile.value('IEX_CB_NOTIFY_CUST_EMAIL_FROM');
2090 END IF;
2091 --dbms_output.put_line('l_from : ' || l_from);
2092
2093
2094 OPEN l_notify_csr(p_case_number
2095 ,p_party_id
2096 ,l_organization_id);
2097 LOOP
2098 FETCH l_notify_csr INTO
2099 l_oinv_rec.id,
2100 l_oinv_rec.khr_id,
2101 l_oinv_rec.cas_id,
2102 l_oinv_rec.party_id,
2103 l_oinv_rec.party_name,
2104 l_iohv_rec.id;
2105 EXIT WHEN l_notify_csr%NOTFOUND;
2106
2107 l_party_id := l_oinv_rec.party_id;
2108 l_contract_id := l_oinv_rec.khr_id;
2109 l_email := p_email;
2110
2111 IF ((l_email = OKC_API.G_MISS_CHAR) OR (l_email IS NULL)) THEN
2112 get_party_email(p_party_id => l_oinv_rec.party_id
2113 ,x_email => l_email
2114 ,x_return_status => l_return_status);
2115 END IF;
2116
2117 IF NOT ((l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) OR
2118 (l_return_status = okl_api.G_RET_STS_ERROR) OR
2119 (l_email = OKC_API.G_MISS_CHAR) OR (l_email IS NULL)) THEN
2120 l_cust_notified := l_cust_notified + 1;
2121
2122 /*
2123 l_bind_var(1) := 'p_contract_id';
2124 l_bind_val(1) := l_contract_id;
2125 l_bind_var_type(1) := 'NUMBER';
2126 */
2127 l_bind_tbl(1).KEY_NAME := 'p_contract_id';
2128 l_bind_tbl(1).KEY_TYPE := 'NUMBER';
2129 l_bind_tbl(1).KEY_VALUE := l_contract_id;
2130
2131 --call fulfillment
2132 /*
2133 OKL_FULFILLMENT_PUB.create_fulfillment (
2134 p_api_version => l_api_version,
2135 p_init_msg_list => okl_api.G_TRUE,
2136 p_agent_id => l_agent_id,
2137 p_content_id => l_content_id,
2138 p_from => l_from,
2139 p_subject => l_subject,
2140 p_email => l_email,
2141 p_bind_var => l_bind_var,
2142 p_bind_val => l_bind_val,
2143 p_bind_var_type => l_bind_var_type,
2144 p_commit => okl_api.G_FALSE,
2145 x_request_id => l_request_id,
2146 x_return_status => l_return_status,
2147 x_msg_count => lx_msg_count,
2148 x_msg_data => lx_msg_data);
2149 */
2150
2151 IEX_DUNNING_PVT.Send_Fulfillment(p_api_version => l_api_version,
2152 p_init_msg_list => FND_API.G_TRUE,
2153 p_commit => FND_API.G_TRUE,
2154 p_FULFILLMENT_BIND_TBL => l_bind_tbl,
2155 p_template_id => l_content_id,
2156 p_method => 'EMAIL',
2157 p_party_id => l_party_id,
2158 p_user_id => l_agent_id,
2159 p_email => l_email,
2160 x_return_status => l_return_status,
2161 x_msg_count => lx_msg_count,
2162 x_msg_data => lx_msg_data,
2163 x_REQUEST_ID => l_request_id
2164 , x_contact_destination => l_contact_destination -- bug 3955222
2165 , x_contact_party_id => l_contact_party_id -- bug 3955222
2166 );
2167 /*
2168 --dbms_output.put_line('p_api_version => ' || l_api_version ||
2169 ' p_agent_id => ' || l_agent_id ||
2170 ' p_content_id => ' || l_content_id);
2171 --dbms_output.put_line(' p_subject => ' || l_subject ||
2172 ' p_email => ' || l_email);
2173 --dbms_output.put_line(' p_bind_var => ' || l_bind_var(1) ||
2174 ' p_bind_val => ' || l_bind_val(1) ||
2175 ' p_bind_var_type => ' || l_bind_var_type(1));
2176 --dbms_output.put_line(' party name => ' || l_oinv_rec.party_name ||
2177 ' x_return_status => ' || l_return_status);
2178 */
2179
2180 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2181 complete_notify(
2182 p_api_version => l_api_version,
2183 p_init_msg_list => okl_api.G_TRUE,
2184 p_interface_id => l_oinv_rec.id,
2185 p_hst_id => l_iohv_rec.id,
2186 p_notification_date => SYSDATE,
2187 p_comments => OKC_API.G_MISS_CHAR,
2188 x_return_status => l_return_status,
2189 x_msg_count => lx_msg_count,
2190 x_msg_data => lx_msg_data);
2191 l_rows_processed := l_rows_processed + 1;
2192
2193 l_task_name := 'Oracle Collections Review Contract';
2194 l_description := 'Oracle Collections Review contract for reporting to credit bureau';
2195 l_start_date := sysdate + to_number(fnd_profile.value('IEX_CB_NOTIFY_GRACE_DAYS'));
2196 create_followup(p_api_version => l_api_version,
2197 p_init_msg_list => okl_api.G_TRUE,
2198 p_oinv_rec => l_oinv_rec,
2199 p_iohv_rec => l_iohv_rec,
2200 p_task_name => l_task_name,
2201 p_description => l_description,
2202 p_start_date => l_start_date,
2203 x_return_status => l_return_status,
2204 x_msg_count => lx_msg_count,
2205 x_msg_data => lx_msg_data);
2206 ELSE
2207 FND_MESSAGE.SET_NAME('IEX', 'IEX_INVALID_FULFILLMENT_SETUP');
2208 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET );
2209 l_rows_failed := l_rows_failed + 1;
2210 END IF;
2211 ELSE
2212 l_cust_not_notified := l_cust_not_notified + 1;
2213 --dbms_output.put_line('do not report - ' || l_oinv_rec.id);
2214 END IF;
2215 END LOOP;
2216
2217 --dbms_output.PUT_LINE('CUSTOMERS NOTIFIED = ' || l_cust_notified);
2218 --dbms_output.PUT_LINE('CUSTOMERS NOT NOTIFIED = ' || l_cust_not_notified);
2219 --dbms_output.PUT_LINE('CUSTOMERS TO BE NOTIFIED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
2220 --dbms_output.PUT_LINE('CUSTOMERS TO BE NOTIFIED NOT PROCESSED = ' || l_rows_failed);
2221 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS NOTIFIED = ' || l_cust_notified);
2222 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS NOT NOTIFIED = ' || l_cust_not_notified);
2223 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE NOTIFIED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
2224 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE NOTIFIED NOT PROCESSED = ' || l_rows_failed);
2225
2226 COMMIT;
2227 EXCEPTION
2228 WHEN G_INVALID_PARAMETERS THEN
2229 errbuf := 'G_INVALID_PARAMETERS';
2230 retcode := 1;
2231 FND_MESSAGE.SET_NAME('IEX', 'IEX_MISSING_EMAIL_CASE');
2232 --dbms_output.put_line(FND_MESSAGE.GET);
2233 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET );
2234 ROLLBACK;
2235 l_return_status := OKC_API.G_RET_STS_ERROR;
2236 WHEN OTHERS THEN
2237 IF l_notify_csr%ISOPEN THEN
2238 CLOSE l_notify_csr;
2239 END IF;
2240 errbuf := substr(SQLERRM, 1, 200);
2241 retcode := 1;
2242 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
2243 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
2244 ROLLBACK;
2245 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2246 END notify_customer;
2247
2248 ---------------------------------------------------------------------------
2249 -- PROCEDURE notify_recall_external_agency
2250 ---------------------------------------------------------------------------
2251 PROCEDURE notify_recall_external_agency(
2252 errbuf OUT NOCOPY VARCHAR2,
2253 retcode OUT NOCOPY NUMBER,
2254 p_case_number IN VARCHAR2,
2255 p_ext_agncy_id IN NUMBER,
2256 p_comments IN VARCHAR2) AS
2257
2258 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
2259 lx_msg_count NUMBER ;
2260 lx_msg_data VARCHAR2(2000);
2261 lx_message VARCHAR2(2000);
2262 l_api_version CONSTANT NUMBER := 1;
2263 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2264 l_recall VARCHAR2(1) := Okc_Api.G_FALSE;
2265 l_delinquency_status IEX_DEL_STATUSES.DEL_STATUS%TYPE;
2266
2267 l_api_name CONSTANT VARCHAR2(30) := 'notify_recall_external_agency';
2268 l_ext_agncy_contracts_notified NUMBER := 0;
2269
2270 l_oinv_rec oinv_rec_type;
2271 lx_oinv_rec oinv_rec_type;
2272 l_iohv_rec iohv_rec_type;
2273 lp_iohv_rec iohv_rec_type;
2274 lx_iohv_rec iohv_rec_type;
2275
2276 l_case_number OKL_OPEN_INT.CASE_NUMBER%TYPE;
2277 l_ext_agncy_id IEX_OPEN_INT_HST.EXT_AGNCY_ID%TYPE;
2278 --l_extend_days IEX_OPEN_INT_HST.EXTEND_DAYS%TYPE;
2279 l_task_name JTF_TASKS_VL.TASK_NAME%TYPE;
2280 l_description JTF_TASKS_VL.DESCRIPTION%TYPE;
2281
2282 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
2283 l_case_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2284 l_ext_agncy_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2285
2286 CURSOR l_recall_csr(cp_case_number IN VARCHAR2
2287 ,cp_ext_agncy_id IN NUMBER
2288 ,cp_organization_id IN NUMBER) IS
2289 SELECT OIN.ID,
2290 OIN.KHR_ID,
2291 OIN.CAS_ID,
2292 IOH.ID,
2293 IOH.OBJECT1_ID1,
2294 IOH.OBJECT1_ID2,
2295 IOH.JTOT_OBJECT1_CODE,
2296 IOH.ACTION,
2297 IOH.STATUS,
2298 IOH.REQUEST_DATE,
2299 IOH.PROCESS_DATE,
2300 IOH.EXT_AGNCY_ID,
2301 IOH.REVIEW_BEFORE_RECALL_FLAG
2302 FROM Okl_Open_Int OIN
2303 ,Iex_Open_Int_Hst IOH
2304 WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
2305 AND IOH.jtot_object1_code = 'OKX_LEASE'
2306 AND (IOH.ACTION = ACTION_TRANSFER_EXT_AGNCY)
2307 AND (IOH.STATUS = STATUS_PROCESSED)
2308 AND (TRUNC(IOH.REVIEW_DATE) = TRUNC(SYSDATE))
2309 AND ((l_case_passed = Okc_Api.G_FALSE) OR
2310 (l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
2311 AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
2312 (l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
2313 AND (OIN.org_id = cp_organization_id);
2314 BEGIN
2315 --get organization id
2316 --Begin Bug#5373556 schekuri 12-Jul-2006
2317 l_organization_id := mo_global.get_current_org_id;
2318 --l_organization_id := fnd_profile.value('ORG_ID');
2319 --End Bug#5373556 schekuri 12-Jul-2006
2320
2321 --check if case number is passed
2322 IF (p_case_number = OKC_API.G_MISS_CHAR OR
2323 p_case_number IS NULL) THEN
2324 l_case_passed := Okc_Api.G_FALSE;
2325 --dbms_output.put_line('contract is not passed');
2326 END IF;
2327
2328 --check if ext_agncy_id is passed
2329 IF (p_ext_agncy_id = OKC_API.G_MISS_NUM OR
2330 p_ext_agncy_id IS NULL) THEN
2331 l_ext_agncy_passed := Okc_Api.G_FALSE;
2332 --dbms_output.put_line('ext_agncy is not passed');
2333 END IF;
2334
2335 --l_extend_days := fnd_profile.value('IEX_EA_RECALL_GRACE_DAYS');
2336
2337 OPEN l_recall_csr(p_case_number
2338 ,p_ext_agncy_id
2339 ,l_organization_id);
2340 LOOP
2341 FETCH l_recall_csr INTO
2342 l_oinv_rec.id,
2343 l_oinv_rec.khr_id,
2344 l_oinv_rec.cas_id,
2345 l_iohv_rec.id,
2346 l_iohv_rec.object1_id1,
2347 l_iohv_rec.object1_id2,
2348 l_iohv_rec.jtot_object1_code,
2349 l_iohv_rec.action,
2350 l_iohv_rec.status,
2351 l_iohv_rec.request_date,
2352 l_iohv_rec.process_date,
2353 l_iohv_rec.ext_agncy_id,
2354 l_iohv_rec.review_before_recall_flag;
2355
2356 EXIT WHEN l_recall_csr%NOTFOUND;
2357
2358 l_iohv_rec.comments := p_comments;
2359 review_transfer(
2360 p_api_version => l_api_version,
2361 p_init_msg_list => l_init_msg_list,
2362 p_oinv_rec => l_oinv_rec,
2363 p_iohv_rec => l_iohv_rec,
2364 x_oinv_rec => lx_oinv_rec,
2365 x_iohv_rec => lx_iohv_rec,
2366 x_return_status => l_return_status,
2367 x_msg_count => lx_msg_count,
2368 x_msg_data => lx_msg_data);
2369
2370 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
2371 l_ext_agncy_contracts_notified := l_ext_agncy_contracts_notified + 1;
2372 END IF;
2373
2374 /*
2375 l_recall := OKC_API.G_FALSE;
2376
2377 --check whether contract is to be recalled
2378 get_contract_recall(p_oinv_rec => l_oinv_rec,
2379 p_iohv_rec => l_iohv_rec,
2380 x_recall => l_recall,
2381 x_return_status => l_return_status);
2382
2383 IF (l_recall = OKC_API.G_TRUE) THEN
2384 l_iohv_rec.status := STATUS_NOTIFIED;
2385 l_iohv_rec.process_date := SYSDATE;
2386 l_iohv_rec.comments := p_comments;
2387
2388 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
2389 ,p_init_msg_list => l_init_msg_list
2390 ,x_return_status => l_return_status
2391 ,x_msg_count => lx_msg_count
2392 ,x_msg_data => lx_msg_data
2393 ,p_iohv_rec => l_iohv_rec
2394 ,x_iohv_rec => lx_iohv_rec);
2395
2396 l_iohv_rec := lx_iohv_rec;
2397
2398 lp_iohv_rec := l_iohv_rec;
2399 lp_iohv_rec.id := null;
2400 lp_iohv_rec.action := ACTION_RECALL_NOTICE;
2401 lp_iohv_rec.status := STATUS_PROCESSED;
2402 lp_iohv_rec.request_date := SYSDATE;
2403 lp_iohv_rec.process_date := SYSDATE;
2404 lp_iohv_rec.review_date := NULL;
2405 --lp_iohv_rec.recall_date := NULL;
2406 lp_iohv_rec.automatic_recall_flag := NULL;
2407 lp_iohv_rec.review_before_recall_flag := NULL;
2408
2409 iex_open_int_hst_pub.insert_open_int_hst(
2410 p_api_version => l_api_version,
2411 p_init_msg_list => l_init_msg_list,
2412 x_return_status => l_return_status,
2413 x_msg_count => lx_msg_count,
2414 x_msg_data => lx_msg_data,
2415 p_iohv_rec => lp_iohv_rec,
2416 x_iohv_rec => lx_iohv_rec);
2417
2418 l_task_name := 'Oracle Collections Review Transfer to External Agency';
2419 l_description := 'Oracle Collections Review contract before recalling from external agency to which it is transferred.';
2420 create_followup(p_api_version => l_api_version,
2421 p_init_msg_list => l_init_msg_list,
2422 p_oinv_rec => l_oinv_rec,
2423 p_iohv_rec => l_iohv_rec,
2424 p_task_name => l_task_name,
2425 p_description => l_description,
2426 x_return_status => l_return_status,
2427 x_msg_count => lx_msg_count,
2428 x_msg_data => lx_msg_data);
2429 l_ext_agncy_contracts_notified := l_ext_agncy_contracts_notified + 1;
2430 END IF;
2431 */
2432 /*
2433 IF(NVL(l_iohv_rec.review_before_recall_flag, 'N') = 'Y') THEN
2434 l_task_name := 'Oracle Collections Review Transfer to External Agency';
2435 l_description := 'Oracle Collections Review contract before recalling from external agency to which it is transferred.';
2436 create_followup(p_api_version => l_api_version,
2437 p_init_msg_list => l_init_msg_list,
2438 p_oinv_rec => l_oinv_rec,
2439 p_iohv_rec => l_iohv_rec,
2440 p_task_name => l_task_name,
2441 p_description => l_description,
2442 x_return_status => l_return_status,
2443 x_msg_count => lx_msg_count,
2444 x_msg_data => lx_msg_data);
2445 ELSE
2446 --check whether contract is to be recalled
2447 get_contract_recall(p_oinv_rec => l_oinv_rec,
2448 p_iohv_rec => l_iohv_rec,
2449 x_recall => l_recall,
2450 x_return_status => l_return_status);
2451
2452 IF (l_recall = OKC_API.G_TRUE) THEN
2453 l_iohv_rec.status := STATUS_NOTIFIED;
2454 l_iohv_rec.process_date := SYSDATE;
2455 l_iohv_rec.comments := p_comments;
2456
2457 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
2458 ,p_init_msg_list => l_init_msg_list
2459 ,x_return_status => l_return_status
2460 ,x_msg_count => lx_msg_count
2461 ,x_msg_data => lx_msg_data
2462 ,p_iohv_rec => l_iohv_rec
2463 ,x_iohv_rec => lx_iohv_rec);
2464
2465 --dbms_output.PUT_LINE('update status : ' || l_return_status);
2466
2467 iex_open_interface_pub.insert_pending(
2468 p_api_version => l_api_version,
2469 p_init_msg_list => l_init_msg_list,
2470 p_object1_id1 => l_iohv_rec.object1_id1,
2471 p_object1_id2 => l_iohv_rec.object1_id2,
2472 p_jtot_object1_code => l_iohv_rec.jtot_object1_code,
2473 p_action => IEX_OPI_PVT.ACTION_NOTIFY_EXT_AGNCY,
2474 p_status => IEX_OPI_PVT.STATUS_PROCESSED,
2475 p_comments => p_comments,
2476 p_ext_agncy_id => NULL,
2477 p_review_date => NULL,
2478 p_recall_date => NULL,
2479 p_automatic_recall_flag => NULL,
2480 p_review_before_recall_flag => NULL,
2481 x_return_status => l_return_status,
2482 x_msg_count => lx_msg_count,
2483 x_msg_data => lx_msg_data);
2484 --dbms_output.PUT_LINE('insert status : ' || l_return_status);
2485
2486 lp_iohv_rec := lx_iohv_rec;
2487 lp_iohv_rec.ext_agncy_id := l_iohv_rec.ext_agncy_id;
2488 lp_iohv_rec.process_date := SYSDATE;
2489
2490 iex_open_int_hst_pub.update_open_int_hst(
2491 p_api_version => l_api_version,
2492 p_init_msg_list => l_init_msg_list,
2493 x_return_status => l_return_status,
2494 x_msg_count => lx_msg_count,
2495 x_msg_data => lx_msg_data,
2496 p_iohv_rec => lp_iohv_rec,
2497 x_iohv_rec => lx_iohv_rec);
2498 l_ext_agncy_contracts_notified := l_ext_agncy_contracts_notified + 1;
2499 ELSE
2500 --check delinquency status of the contract
2501 get_contract_delinquency_stat(p_oinv_rec => l_oinv_rec,
2502 p_iohv_rec => l_iohv_rec,
2503 x_delinquency_status => l_delinquency_status,
2504 x_return_status => l_return_status);
2505
2506 IF (l_delinquency_status = CASE_STATUS_CURRENT) THEN
2507 l_iohv_rec.status := STATUS_COLLECTED;
2508 l_iohv_rec.process_date := SYSDATE;
2509
2510 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
2511 ,p_init_msg_list => l_init_msg_list
2512 ,x_return_status => l_return_status
2513 ,x_msg_count => lx_msg_count
2514 ,x_msg_data => lx_msg_data
2515 ,p_iohv_rec => l_iohv_rec
2516 ,x_iohv_rec => lx_iohv_rec);
2517 ELSE
2518 l_iohv_rec.process_date := SYSDATE;
2519
2520 iex_open_int_hst_pub.update_open_int_hst(p_api_version => l_api_version
2521 ,p_init_msg_list => l_init_msg_list
2522 ,x_return_status => l_return_status
2523 ,x_msg_count => lx_msg_count
2524 ,x_msg_data => lx_msg_data
2525 ,p_iohv_rec => l_iohv_rec
2526 ,x_iohv_rec => lx_iohv_rec);
2527
2528 END IF;
2529 END IF;
2530 END IF;
2531 */
2532 END LOOP;
2533
2534 --dbms_output.PUT_LINE('CONTRACTS NOTIFIED ABOUT RECALL = ' || l_ext_agncy_contracts_notified);
2535 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CONTRACTS NOTIFIED ABOUT RECALL = ' || l_ext_agncy_contracts_notified);
2536
2537 COMMIT;
2538 EXCEPTION
2539 WHEN OTHERS THEN
2540 IF l_recall_csr%ISOPEN THEN
2541 CLOSE l_recall_csr;
2542 END IF;
2543 errbuf := substr(SQLERRM, 1, 200);
2544 retcode := 1;
2545 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
2546 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
2547 ROLLBACK;
2548 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2549 END notify_recall_external_agency;
2550
2551 ---------------------------------------------------------------------------
2552 -- PROCEDURE notify_external_agency
2553 ---------------------------------------------------------------------------
2554 PROCEDURE notify_external_agency(
2555 errbuf OUT NOCOPY VARCHAR2,
2556 retcode OUT NOCOPY NUMBER,
2557 p_case_number IN VARCHAR2,
2558 p_ext_agncy_id IN NUMBER,
2559 p_agent_id IN NUMBER,
2560 p_content_id IN VARCHAR2,
2561 p_from IN VARCHAR2,
2562 p_subject IN VARCHAR2,
2563 p_email IN VARCHAR2) AS
2564
2565 lx_msg_count NUMBER ;
2566 lx_msg_data VARCHAR2(2000);
2567 lx_message VARCHAR2(2000);
2568 l_api_version CONSTANT NUMBER := 1;
2569 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2570 l_api_name CONSTANT VARCHAR2(30) := 'notify_external_agency';
2571 l_rows_processed NUMBER := 0;
2572 l_rows_failed NUMBER := 0;
2573 l_ext_agncy_notified NUMBER := 0;
2574 l_ext_agncy_not_notified NUMBER := 0;
2575
2576 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2577 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2578 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
2579
2580 l_oinv_rec oinv_rec_type;
2581 l_iohv_rec iohv_rec_type;
2582
2583 l_case_number OKL_OPEN_INT.CASE_NUMBER%TYPE;
2584 l_contract_id OKL_OPEN_INT.KHR_ID%TYPE;
2585 l_ext_agncy_id IEX_OPEN_INT_HST.EXT_AGNCY_ID%TYPE;
2586 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
2587 l_subject VARCHAR2(2000);
2588 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE;
2589 l_from VARCHAR2(2000);
2590 l_agent_id NUMBER;
2591 l_request_id NUMBER;
2592
2593 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
2594 l_ext_agncy_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2595 l_case_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2596
2597 CURSOR l_notify_csr(cp_case_number IN VARCHAR2
2598 ,cp_ext_agncy_id IN NUMBER
2599 ,cp_organization_id IN NUMBER) IS
2600 SELECT OIN.ID,
2601 OIN.KHR_ID,
2602 IOH.ID,
2603 IOH.EXT_AGNCY_ID
2604 FROM Okl_Open_Int OIN
2605 ,Iex_Open_Int_Hst IOH
2606 WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
2607 AND IOH.jtot_object1_code = 'OKX_LEASE'
2608 AND (IOH.ACTION = ACTION_NOTIFY_EXT_AGNCY)
2609 AND (IOH.STATUS = STATUS_PROCESSED)
2610 AND ((l_case_passed = Okc_Api.G_FALSE) OR
2611 (l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
2612 AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
2613 (l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
2614 AND (OIN.org_id = cp_organization_id);
2615 BEGIN
2616 --check to see that case number is passed when email is passed
2617 IF NOT (p_email = OKC_API.G_MISS_CHAR OR p_email IS NULL) THEN
2618 IF (p_case_number = OKC_API.G_MISS_CHAR OR p_case_number IS NULL) THEN
2619 RAISE G_INVALID_PARAMETERS;
2620 END IF;
2621 END IF;
2622
2623 --get organization id
2624 --Begin Bug#5373556 schekuri 12-Jul-2006
2625 l_organization_id := mo_global.get_current_org_id;
2626 --l_organization_id := fnd_profile.value('ORG_ID');
2627 --End Bug#5373556 schekuri 12-Jul-2006
2628
2629 /*
2630 l_organization_id := okl_context.get_okc_org_id;
2631 --dbms_output.put_line('org is : ' || l_organization_id);
2632
2633 --check if organization_id is null, set it from the profile
2634 IF (l_organization_id IS NULL) THEN
2635 --dbms_output.put_line('org is not set');
2636 okl_context.set_okc_org_context(null,null);
2637 --dbms_output.put_line('org is now set');
2638 l_organization_id := okl_context.get_okc_org_id;
2639 --dbms_output.put_line('org is : ' || l_organization_id);
2640 END IF;
2641 */
2642
2643 --check if contract_id is passed
2644 IF (p_case_number = OKC_API.G_MISS_CHAR OR
2645 p_case_number IS NULL) THEN
2646 l_case_passed := Okc_Api.G_FALSE;
2647 --dbms_output.put_line('contract is not passed');
2648 END IF;
2649
2650 --check if p_ext_agncy_id is passed
2651 IF (p_ext_agncy_id = OKC_API.G_MISS_NUM OR
2652 p_ext_agncy_id IS NULL) THEN
2653 l_ext_agncy_passed := Okc_Api.G_FALSE;
2654 --dbms_output.put_line('ext_agncy is not passed');
2655 END IF;
2656
2657 l_subject := p_subject;
2658 l_content_id := p_content_id;
2659 l_agent_id := NVL(p_agent_id, FND_GLOBAL.USER_ID);
2660 l_from := p_from;
2661
2662
2663 --if suject is null get subject
2664 IF (l_subject = OKC_API.G_MISS_CHAR OR
2665 l_subject IS NULL) THEN
2666 l_subject := fnd_profile.value('IEX_EA_NOTIFY_VENDOR_EMAIL_SUBJECT');
2667 END IF;
2668 --dbms_output.put_line('l_subject : ' || l_subject);
2669
2670 --if content_id is null get content_id
2671 IF (l_content_id = OKC_API.G_MISS_NUM OR
2672 l_content_id IS NULL) THEN
2673 l_content_id := to_number(fnd_profile.value('IEX_EA_NOTIFY_VENDOR_TEMPLATE'));
2674 END IF;
2675 --dbms_output.put_line('l_content_id : ' || l_content_id);
2676
2677 --if from is null get subject
2678 IF (l_from = OKC_API.G_MISS_CHAR OR
2679 l_from IS NULL) THEN
2680 l_from := fnd_profile.value('IEX_EA_NOTIFY_VENDOR_EMAIL_FROM');
2681 END IF;
2682 --dbms_output.put_line('l_from : ' || l_from);
2683
2684
2685 OPEN l_notify_csr(p_case_number
2686 ,p_ext_agncy_id
2687 ,l_organization_id);
2688 LOOP
2689 FETCH l_notify_csr INTO
2690 l_oinv_rec.id,
2691 l_oinv_rec.khr_id,
2692 l_iohv_rec.id,
2693 l_iohv_rec.ext_agncy_id;
2694 EXIT WHEN l_notify_csr%NOTFOUND;
2695
2696 l_ext_agncy_id := l_iohv_rec.ext_agncy_id;
2697 l_contract_id := l_oinv_rec.khr_id;
2698 l_email := p_email;
2699
2700 IF ((l_email = OKC_API.G_MISS_CHAR) OR (l_email IS NULL)) THEN
2701 get_ext_agncy_email(p_ext_agncy_id => l_iohv_rec.ext_agncy_id
2702 ,x_email => l_email
2703 ,x_return_status => l_return_status);
2704 END IF;
2705
2706 IF NOT ((l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) OR
2707 (l_return_status = okl_api.G_RET_STS_ERROR) OR
2708 (l_email = OKC_API.G_MISS_CHAR) OR (l_email IS NULL)) THEN
2709 l_ext_agncy_notified := l_ext_agncy_notified + 1;
2710
2711 /*
2712 l_bind_var(1) := 'p_contract_id';
2713 l_bind_val(1) := l_contract_id;
2714 l_bind_var_type(1) := 'NUMBER';
2715
2716 --call fulfillment
2717 OKL_FULFILLMENT_PUB.create_fulfillment (
2718 p_api_version => l_api_version,
2719 p_init_msg_list => okl_api.G_TRUE,
2720 p_agent_id => l_agent_id,
2721 p_content_id => l_content_id,
2722 p_from => l_from,
2723 p_subject => l_subject,
2724 p_email => l_email,
2725 p_bind_var => l_bind_var,
2726 p_bind_val => l_bind_val,
2727 p_bind_var_type => l_bind_var_type,
2728 p_commit => okl_api.G_FALSE,
2729 x_request_id => l_request_id,
2730 x_return_status => l_return_status,
2731 x_msg_count => lx_msg_count,
2732 x_msg_data => lx_msg_data);
2733 */
2734
2735 /*
2736 --dbms_output.put_line('p_api_version => ' || l_api_version ||
2737 ' p_agent_id => ' || l_agent_id ||
2738 ' p_content_id => ' || l_content_id);
2739 --dbms_output.put_line(' p_subject => ' || l_subject ||
2740 ' p_email => ' || l_email);
2741 --dbms_output.put_line(' p_bind_var => ' || l_bind_var(1) ||
2742 ' p_bind_val => ' || l_bind_val(1) ||
2743 ' p_bind_var_type => ' || l_bind_var_type(1));
2744 --dbms_output.put_line(' party name => ' || l_oinv_rec.party_name ||
2745 ' x_return_status => ' || l_return_status);
2746 */
2747
2748 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2749 complete_notify_ext_agncy(
2750 p_api_version => l_api_version,
2751 p_init_msg_list => okl_api.G_TRUE,
2752 p_interface_id => l_oinv_rec.id,
2753 p_hst_id => l_iohv_rec.id,
2754 p_notification_date => SYSDATE,
2755 p_comments => null,
2756 x_return_status => l_return_status,
2757 x_msg_count => lx_msg_count,
2758 x_msg_data => lx_msg_data);
2759
2760 l_rows_processed := l_rows_processed + 1;
2761 ELSE
2762 FND_MESSAGE.SET_NAME('IEX', 'IEX_INVALID_FULFILLMENT_SETUP');
2763 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET );
2764 l_rows_failed := l_rows_failed + 1;
2765 END IF;
2766 ELSE
2767 l_ext_agncy_not_notified := l_ext_agncy_not_notified + 1;
2768 --dbms_output.put_line('do not report - ' || l_oinv_rec.id);
2769 END IF;
2770 END LOOP;
2771
2772 --dbms_output.PUT_LINE('EXT AGNCYS NOTIFIED = ' || l_ext_agncy_notified);
2773 --dbms_output.PUT_LINE('EXT AGNCYS NOT NOTIFIED = ' || l_ext_agncy_not_notified);
2774 --dbms_output.PUT_LINE('EXT AGNCYS TO BE NOTIFIED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
2775 --dbms_output.PUT_LINE('EXT AGNCYS TO BE NOTIFIED NOT PROCESSED = ' || l_rows_failed);
2776 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'EXT AGNCYS NOTIFIED = ' || l_ext_agncy_notified);
2777 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'EXT AGNCYS NOT NOTIFIED = ' || l_ext_agncy_not_notified);
2778 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'EXT AGNCYS TO BE NOTIFIED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
2779 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'EXT AGNCYS TO BE NOTIFIED NOT PROCESSED = ' || l_rows_failed);
2780
2781 COMMIT;
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 IF l_notify_csr%ISOPEN THEN
2785 CLOSE l_notify_csr;
2786 END IF;
2787 errbuf := substr(SQLERRM, 1, 200);
2788 retcode := 1;
2789 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
2790 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
2791 ROLLBACK;
2792 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2793 END notify_external_agency;
2794
2795 ---------------------------------------------------------------------------
2796 -- PROCEDURE recall_from_external_agency
2797 ---------------------------------------------------------------------------
2798 PROCEDURE recall_from_external_agency(
2799 errbuf OUT NOCOPY VARCHAR2,
2800 retcode OUT NOCOPY NUMBER,
2801 p_case_number IN VARCHAR2,
2802 p_ext_agncy_id IN NUMBER,
2803 p_comments IN VARCHAR2) AS
2804
2805 l_api_name CONSTANT VARCHAR2(30) := 'recall_from_external_agency';
2806 l_api_version CONSTANT NUMBER := 1;
2807 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
2808 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2809 lx_msg_count NUMBER ;
2810 lx_msg_data VARCHAR2(2000);
2811
2812 l_case_number OKL_OPEN_INT.CASE_NUMBER%TYPE;
2813 l_contract_id OKL_OPEN_INT.KHR_ID%TYPE;
2814 l_ext_agncy_id HZ_PARTIES.PARTY_ID%TYPE;
2815
2816 l_contracts_recalled NUMBER := 0;
2817 l_rows_failed NUMBER := 0;
2818
2819 l_oinv_rec oinv_rec_type;
2820 lx_oinv_rec oinv_rec_type;
2821 l_iohv_rec iohv_rec_type;
2822 lp_iohv_rec iohv_rec_type;
2823 lx_iohv_rec iohv_rec_type;
2824
2825 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
2826 l_case_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2827 l_ext_agncy_passed VARCHAR2(1) := Okc_Api.G_TRUE;
2828
2829 CURSOR l_recall_csr(cp_case_number IN VARCHAR2
2830 ,cp_ext_agncy_id IN NUMBER
2831 ,cp_organization_id IN NUMBER) IS
2832 SELECT OIN.ID,
2833 OIN.KHR_ID,
2834 IOH.ID,
2835 IOH.OBJECT1_ID1,
2836 IOH.OBJECT1_ID2,
2837 IOH.JTOT_OBJECT1_CODE,
2838 IOH.EXT_AGNCY_ID
2839 FROM Okl_Open_Int OIN
2840 ,Iex_Open_Int_Hst IOH
2841 WHERE OIN.khr_id = TO_NUMBER(IOH.object1_id1)
2842 AND IOH.jtot_object1_code = 'OKX_LEASE'
2843 AND (IOH.ACTION = ACTION_TRANSFER_EXT_AGNCY)
2844 AND (IOH.STATUS = STATUS_NOTIFIED OR IOH.STATUS = STATUS_PROCESSED)
2845 AND (TRUNC(IOH.RECALL_DATE) = TRUNC(SYSDATE))
2846 AND (NVL(IOH.AUTOMATIC_RECALL_FLAG,'N') = 'Y')
2847 AND ((l_case_passed = Okc_Api.G_FALSE) OR
2848 (l_case_passed = Okc_Api.G_TRUE AND OIN.case_number = cp_case_number))
2849 AND ((l_ext_agncy_passed = Okc_Api.G_FALSE) OR
2850 (l_ext_agncy_passed = Okc_Api.G_TRUE AND IOH.ext_agncy_id = cp_ext_agncy_id))
2851 AND (OIN.org_id = cp_organization_id);
2852 BEGIN
2853 --get organization id
2854 --Begin Bug#5373556 schekuri 12-Jul-2006
2855 l_organization_id := mo_global.get_current_org_id;
2856 --l_organization_id := fnd_profile.value('ORG_ID');
2857 --End Bug#5373556 schekuri 12-Jul-2006
2858
2859 --check if case number is passed
2860 IF (p_case_number = OKC_API.G_MISS_CHAR OR
2861 p_case_number IS NULL) THEN
2862 l_case_passed := Okc_Api.G_FALSE;
2863 --dbms_output.put_line('contract is not passed');
2864 END IF;
2865
2866 --check if party_id is passed
2867 IF (p_ext_agncy_id = OKC_API.G_MISS_NUM OR
2868 p_ext_agncy_id IS NULL) THEN
2869 l_ext_agncy_passed := Okc_Api.G_FALSE;
2870 --dbms_output.put_line('ext_agncy is not passed');
2871 END IF;
2872
2873 OPEN l_recall_csr(p_case_number
2874 ,p_ext_agncy_id
2875 ,l_organization_id);
2876 LOOP
2877 FETCH l_recall_csr INTO
2878 l_oinv_rec.id,
2879 l_oinv_rec.khr_id,
2880 l_iohv_rec.id,
2881 l_iohv_rec.object1_id1,
2882 l_iohv_rec.object1_id2,
2883 l_iohv_rec.jtot_object1_code,
2884 l_iohv_rec.ext_agncy_id;
2885 EXIT WHEN l_recall_csr%NOTFOUND;
2886
2887 recall_transfer(p_api_version => l_api_version
2888 ,p_init_msg_list => l_init_msg_list
2889 ,p_interface_id => l_oinv_rec.id
2890 ,p_recall_date => SYSDATE
2891 ,p_comments => p_comments
2892 ,x_return_status => l_return_status
2893 ,x_msg_count => lx_msg_count
2894 ,x_msg_data => lx_msg_data);
2895
2896 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2897 l_rows_failed := l_rows_failed + 1;
2898 ELSE
2899 l_contracts_recalled := l_contracts_recalled + 1;
2900 END IF;
2901 END LOOP;
2902
2903 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CONTRACTS RECALLED SUCCESSFULLY = ' || l_contracts_recalled);
2904 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CONTRACTS FAILED RECALL = ' || l_rows_failed);
2905 COMMIT;
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 IF l_recall_csr%ISOPEN THEN
2909 CLOSE l_recall_csr;
2910 END IF;
2911 errbuf := substr(SQLERRM, 1, 200);
2912 retcode := 1;
2913 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
2914 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
2915 ROLLBACK;
2916 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2917 END recall_from_external_agency;
2918 ---------------------------------------------------------------------------
2919 -- PROCEDURE get_hst_info
2920 ---------------------------------------------------------------------------
2921 PROCEDURE get_hst_info(
2922 p_hst_id IN NUMBER,
2923 x_action OUT NOCOPY VARCHAR2,
2924 x_status OUT NOCOPY VARCHAR2,
2925 x_return_status OUT NOCOPY VARCHAR2) AS
2926
2927 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2928 l_action IEX_OPEN_INT_HST.ACTION%TYPE;
2929 l_status IEX_OPEN_INT_HST.STATUS%TYPE;
2930
2931 CURSOR l_hst_csr(cp_hst_id IN NUMBER) IS
2932 SELECT action
2933 ,status
2934 FROM IEX_OPEN_INT_HST
2935 WHERE id = cp_hst_id;
2936 BEGIN
2937 OPEN l_hst_csr(p_hst_id);
2938 FETCH l_hst_csr INTO
2939 l_action
2940 ,l_status;
2941 CLOSE l_hst_csr;
2942 x_action := l_action;
2943 x_status := l_status;
2944 x_return_status := l_return_status;
2945 EXCEPTION
2946 WHEN OTHERS THEN
2947 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2948 ,p_msg_name => G_UNEXPECTED_ERROR
2949 ,p_token1 => G_SQLCODE_TOKEN
2950 ,p_token1_value => SQLCODE
2951 ,p_token2 => G_SQLERRM_TOKEN
2952 ,p_token2_value => SQLERRM);
2953 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2954 END get_hst_info;
2955
2956 ---------------------------------------------------------------------------
2957 -- PROCEDURE get_party_email
2958 ---------------------------------------------------------------------------
2959 PROCEDURE get_party_email(
2960 p_party_id IN NUMBER,
2961 x_email OUT NOCOPY VARCHAR2,
2962 x_return_status OUT NOCOPY VARCHAR2) AS
2963
2964 CURSOR l_prty_email_csr(cp_party_id IN NUMBER) IS
2965 SELECT email_address
2966 FROM hz_contact_points
2967 WHERE owner_table_name = 'HZ_PARTIES'
2968 AND owner_table_id = cp_party_id
2969 AND contact_point_type = 'EMAIL'
2970 AND primary_flag = 'Y'
2971 AND status = 'A';
2972
2973 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2974 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
2975 BEGIN
2976 OPEN l_prty_email_csr(p_party_id);
2977 FETCH l_prty_email_csr INTO
2978 l_email;
2979 CLOSE l_prty_email_csr;
2980
2981 x_email := l_email;
2982 x_return_status := l_return_status;
2983 EXCEPTION
2984 WHEN OTHERS THEN
2985 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2986 ,p_msg_name => G_UNEXPECTED_ERROR
2987 ,p_token1 => G_SQLCODE_TOKEN
2988 ,p_token1_value => SQLCODE
2989 ,p_token2 => G_SQLERRM_TOKEN
2990 ,p_token2_value => SQLERRM);
2991 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2992 END get_party_email;
2993
2994 ---------------------------------------------------------------------------
2995 -- PROCEDURE get_ext_agncy_email
2996 ---------------------------------------------------------------------------
2997 PROCEDURE get_ext_agncy_email(
2998 p_ext_agncy_id IN NUMBER,
2999 x_email OUT NOCOPY VARCHAR2,
3000 x_return_status OUT NOCOPY VARCHAR2) AS
3001
3002 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3003 l_email PO_VENDOR_SITES_ALL.EMAIL_ADDRESS%TYPE;
3004
3005 CURSOR l_ext_agncy_email_csr(cp_ext_agncy_id IN NUMBER) IS
3006 SELECT pvs.email_address
3007 FROM iex_ext_agncy_b iea
3008 ,po_vendor_sites_all pvs
3009 WHERE iea.external_agency_id = cp_ext_agncy_id
3010 AND iea.vendor_site_id = pvs.vendor_site_id;
3011 BEGIN
3012 OPEN l_ext_agncy_email_csr(p_ext_agncy_id);
3013 FETCH l_ext_agncy_email_csr
3014 INTO l_email;
3015 CLOSE l_ext_agncy_email_csr;
3016
3017 x_email := l_email;
3018 x_return_status := l_return_status;
3019 EXCEPTION
3020 WHEN OTHERS THEN
3021 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3022 ,p_msg_name => G_UNEXPECTED_ERROR
3023 ,p_token1 => G_SQLCODE_TOKEN
3024 ,p_token1_value => SQLCODE
3025 ,p_token2 => G_SQLERRM_TOKEN
3026 ,p_token2_value => SQLERRM);
3027 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3028 END get_ext_agncy_email;
3029
3030 ---------------------------------------------------------------------------
3031 -- PROCEDURE get_external_agency
3032 ---------------------------------------------------------------------------
3033 PROCEDURE get_external_agency(
3034 p_oinv_rec IN oinv_rec_type,
3035 p_iohv_rec IN iohv_rec_type,
3036 x_ext_agncy_id OUT NOCOPY NUMBER,
3037 x_return_status OUT NOCOPY VARCHAR2) AS
3038
3039 l_oinv_rec oinv_rec_type;
3040 l_iohv_rec iohv_rec_type;
3041 l_ext_agncy_id IEX_OPEN_INT_HST.EXT_AGNCY_ID%TYPE;
3042 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3043 l_score_id IEX_SCORES.SCORE_ID%TYPE;
3044 l_score_value IEX_SCORE_HISTORIES.SCORE_VALUE%TYPE;
3045 l_score_found BOOLEAN := FALSE;
3046
3047 TYPE ext_agncy_tbl_type IS TABLE OF IEX_OPEN_INT_HST.EXT_AGNCY_ID%TYPE
3048 INDEX BY BINARY_INTEGER;
3049
3050 l_exclude_ext_agncy_tbl ext_agncy_tbl_type;
3051 l_ext_agncy_tbl ext_agncy_tbl_type;
3052
3053 i NUMBER := 0;
3054 j NUMBER := 0;
3055
3056 CURSOR l_score_csr(cp_case_id IN NUMBER
3057 ,cp_score_id IN NUMBER
3058 ,cp_creation_date IN DATE) IS
3059 SELECT score_value
3060 FROM IEX_SCORE_HISTORIES
3061 WHERE score_object_id = cp_case_id
3062 AND score_object_code = 'IEX_CASES'
3063 AND score_id = cp_score_id
3064 AND TRUNC(creation_date) = TRUNC(cp_creation_date)
3065 ORDER BY creation_date DESC;
3066
3067 CURSOR l_ext_agncy_csr(cp_score_value IN NUMBER) IS
3068 SELECT external_agency_id
3069 FROM iex_ext_agncy_b
3070 WHERE rank >= cp_score_value
3071 AND SYSDATE BETWEEN effective_start_date AND nvl(effective_end_date, SYSDATE)
3072 ORDER BY rank ASC;
3073
3074 CURSOR l_exclude_ext_agncy_csr(cp_object1_id1 IN NUMBER) IS
3075 SELECT ext_agncy_id
3076 ,status
3077 ,process_date
3078 FROM iex_open_int_hst
3079 WHERE object1_id1 = cp_object1_id1
3080 AND object1_id2 = '#'
3081 AND jtot_object1_code = 'OKX_LEASE'
3082 AND action = ACTION_TRANSFER_EXT_AGNCY
3083 AND (status = STATUS_PROCESSED
3084 OR status = STATUS_RECALLED)
3085 ORDER BY process_date DESC;
3086 BEGIN
3087 l_oinv_rec := p_oinv_rec;
3088 l_iohv_rec := p_iohv_rec;
3089
3090 --get score engine to score cases
3091 l_score_id := to_number(fnd_profile.value('IEX_EA_SCORE_ID'));
3092 --DBMS_OUTPUT.PUT_LINE('Score Engine Id : ' || l_score_id);
3093
3094 --get score if a case has been previously scored on the same day
3095 FOR cur_score IN l_score_csr(l_oinv_rec.cas_id
3096 ,l_score_id
3097 ,l_iohv_rec.process_date) LOOP
3098 l_score_value := cur_score.score_value;
3099 l_score_found := TRUE;
3100 EXIT;
3101 END LOOP;
3102
3103 --if not previously scored on the same day, call scoring engine for object type = IEX_CASES
3104 IF NOT(l_score_found) THEN
3105 --score the case by calling IEX_SCORE_NEW_PVT.scoreObject
3106 l_score_value := IEX_SCORE_NEW_PVT.scoreObject(p_commit => FND_API.G_TRUE,
3107 P_OBJECT_ID => l_oinv_rec.cas_id,
3108 P_OBJECT_TYPE => 'IEX_CASES',
3109 P_SCORE_ID => l_score_id);
3110 --if score value is null set a message appropriately
3111 END IF;
3112
3113 --get external agencies to exlude for case assignment
3114 FOR cur_exclude_ext_agncy IN l_exclude_ext_agncy_csr(l_iohv_rec.object1_id1) LOOP
3115 IF (cur_exclude_ext_agncy.status = STATUS_RECALLED) THEN
3116 j := j + 1;
3117 l_exclude_ext_agncy_tbl(j) := cur_exclude_ext_agncy.ext_agncy_id;
3118 ELSE
3119 EXIT;
3120 END IF;
3121 END LOOP;
3122 --DBMS_OUTPUT.PUT_LINE('exclude_ext_agncy_tbl.count: ' || l_exclude_ext_agncy_tbl.count);
3123
3124 --get external agencies eligible for case assignment
3125 FOR cur_ext_agncy IN l_ext_agncy_csr(l_score_value) LOOP
3126 i := i + 1;
3127 l_ext_agncy_tbl(i) := cur_ext_agncy.external_agency_id;
3128 END LOOP;
3129 --DBMS_OUTPUT.PUT_LINE('l_ext_agncy_tbl.count: ' || l_ext_agncy_tbl.count);
3130
3131 --Eliminate agencies(from which the case has been previously recalled)from
3132 --the list of eligible agencies
3133 i := l_ext_agncy_tbl.first;
3134 WHILE (i IS NOT NULL) LOOP
3135 j := l_exclude_ext_agncy_tbl.first;
3136 WHILE (j IS NOT NULL) LOOP
3137 IF (l_ext_agncy_tbl(i) = l_exclude_ext_agncy_tbl(j)) THEN
3138 l_ext_agncy_tbl.delete(i);
3139 EXIT;
3140 END IF;
3141 j := l_exclude_ext_agncy_tbl.next(j);
3142 END LOOP;
3143
3144 i := l_ext_agncy_tbl.next(i);
3145 END LOOP;
3146
3147
3148 --DBMS_OUTPUT.PUT_LINE('after elimination l_ext_agncy_tbl.count: ' || l_ext_agncy_tbl.count);
3149 l_ext_agncy_id := l_ext_agncy_tbl(l_ext_agncy_tbl.first);
3150
3151 x_ext_agncy_id := l_ext_agncy_id;
3152 x_return_status := l_return_status;
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3156 ,p_msg_name => G_UNEXPECTED_ERROR
3157 ,p_token1 => G_SQLCODE_TOKEN
3158 ,p_token1_value => SQLCODE
3159 ,p_token2 => G_SQLERRM_TOKEN
3160 ,p_token2_value => SQLERRM);
3161 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3162 END get_external_agency;
3163
3164 ---------------------------------------------------------------------------
3165 -- PROCEDURE get_contract_recall
3166 ---------------------------------------------------------------------------
3167 PROCEDURE get_contract_recall(
3168 p_oinv_rec IN oinv_rec_type,
3169 p_iohv_rec IN iohv_rec_type,
3170 x_recall OUT NOCOPY VARCHAR2,
3171 x_return_status OUT NOCOPY VARCHAR2) AS
3172
3173 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3174 l_recall VARCHAR2(1) := Okc_Api.G_TRUE;
3175
3176 l_oinv_rec oinv_rec_type;
3177 l_iohv_rec iohv_rec_type;
3178 l_score_id IEX_SCORES.SCORE_ID%TYPE;
3179 l_score_value IEX_SCORE_HISTORIES.SCORE_VALUE%TYPE;
3180 l_score_value_previous IEX_SCORE_HISTORIES.SCORE_VALUE%TYPE;
3181
3182 CURSOR l_score_csr(cp_case_id IN NUMBER
3183 ,cp_score_id IN NUMBER
3184 ,cp_creation_date IN DATE) IS
3185 SELECT score_value
3186 FROM IEX_SCORE_HISTORIES
3187 WHERE score_object_id = cp_case_id
3188 AND score_object_code = 'IEX_CASES'
3189 AND score_id = cp_score_id
3190 AND TRUNC(creation_date) = TRUNC(cp_creation_date)
3191 ORDER BY creation_date DESC;
3192 BEGIN
3193 l_oinv_rec := p_oinv_rec;
3194 l_iohv_rec := p_iohv_rec;
3195
3196 --get score engine to score cases
3197 l_score_id := to_number(fnd_profile.value('IEX_EA_SCORE_ID'));
3198
3199 --get score of the case when assigned to externalagency previously
3200 FOR cur_score IN l_score_csr(l_oinv_rec.cas_id
3201 ,l_score_id
3202 ,l_iohv_rec.process_date) LOOP
3203 l_score_value_previous := cur_score.score_value;
3204 EXIT;
3205 END LOOP;
3206
3207 --score the case by calling IEX_SCORE_NEW_PVT.scoreObject
3208 l_score_value := IEX_SCORE_NEW_PVT.scoreObject(p_commit => FND_API.G_TRUE,
3209 P_OBJECT_ID => l_oinv_rec.cas_id,
3210 P_OBJECT_TYPE => 'IEX_CASES',
3211 P_SCORE_ID => l_score_id);
3212
3213 IF ((nvl(l_score_value,0) - nvl(l_score_value_previous,0)) >= to_number(fnd_profile.value('IEX_EA_SCORE_DIFF_FOR_RECALL'))) THEN
3214 l_recall := Okc_Api.G_FALSE;
3215 END IF;
3216
3217 x_recall := l_recall;
3218 x_return_status := l_return_status;
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3222 ,p_msg_name => G_UNEXPECTED_ERROR
3223 ,p_token1 => G_SQLCODE_TOKEN
3224 ,p_token1_value => SQLCODE
3225 ,p_token2 => G_SQLERRM_TOKEN
3226 ,p_token2_value => SQLERRM);
3227 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3228 END get_contract_recall;
3229
3230 ---------------------------------------------------------------------------
3231 -- PROCEDURE get_contract_delinquency_stat
3232 ---------------------------------------------------------------------------
3233 PROCEDURE get_contract_delinquency_stat(
3234 p_oinv_rec IN oinv_rec_type,
3235 p_iohv_rec IN iohv_rec_type,
3236 x_delinquency_status OUT NOCOPY VARCHAR2,
3237 x_return_status OUT NOCOPY VARCHAR2) AS
3238
3239 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3240 l_delinquency_status IEX_DEL_STATUSES.DEL_STATUS%TYPE;
3241
3242 l_oinv_rec oinv_rec_type;
3243 l_iohv_rec iohv_rec_type;
3244 l_score_id IEX_SCORES.SCORE_ID%TYPE;
3245 l_score_found BOOLEAN := FALSE;
3246 l_score_value IEX_SCORE_HISTORIES.SCORE_VALUE%TYPE;
3247
3248 CURSOR l_score_csr(cp_case_id IN NUMBER
3249 ,cp_score_id IN NUMBER
3250 ,cp_creation_date IN DATE) IS
3251 SELECT score_value
3252 FROM IEX_SCORE_HISTORIES
3253 WHERE score_object_id = cp_case_id
3254 AND score_object_code = 'IEX_CASES'
3255 AND score_id = cp_score_id
3256 AND TRUNC(creation_date) = TRUNC(cp_creation_date)
3257 ORDER BY creation_date DESC;
3258
3259 CURSOR l_del_status_csr(cp_score_id IN NUMBER
3260 ,cp_score_value IN NUMBER) IS
3261 SELECT del_status
3262 FROM IEX_DEL_STATUSES
3263 WHERE score_id = cp_score_id
3264 AND cp_score_value BETWEEN score_value_low AND score_value_high;
3265 BEGIN
3266 l_oinv_rec := p_oinv_rec;
3267 l_iohv_rec := p_iohv_rec;
3268
3269 --get score engine to score cases
3270 l_score_id := to_number(fnd_profile.value('IEX_EA_SCORE_ID'));
3271
3272 --get score of the case when assigned to externalagency previously
3273 FOR cur_score IN l_score_csr(l_oinv_rec.cas_id
3274 ,l_score_id
3275 ,l_iohv_rec.process_date) LOOP
3276 l_score_value := cur_score.score_value;
3277 l_score_found := TRUE;
3278 EXIT;
3279 END LOOP;
3280
3281 --if score is not found, score the case by calling IEX_SCORE_NEW_PVT.scoreObject
3282 IF NOT(l_score_found) THEN
3283 l_score_value := IEX_SCORE_NEW_PVT.scoreObject(p_commit => FND_API.G_TRUE,
3284 P_OBJECT_ID => l_oinv_rec.cas_id,
3285 P_OBJECT_TYPE => 'IEX_CASES',
3286 P_SCORE_ID => l_score_id);
3287 END IF;
3288
3289 --get delinquency status
3290 FOR cur_del_status IN l_del_status_csr(l_score_id
3291 ,l_score_value) LOOP
3292 l_delinquency_status := cur_del_status.del_status;
3293 EXIT;
3294 END LOOP;
3295
3296 x_delinquency_status := l_delinquency_status;
3297 x_return_status := l_return_status;
3298 EXCEPTION
3299 WHEN OTHERS THEN
3300 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
3301 ,p_msg_name => G_UNEXPECTED_ERROR
3302 ,p_token1 => G_SQLCODE_TOKEN
3303 ,p_token1_value => SQLCODE
3304 ,p_token2 => G_SQLERRM_TOKEN
3305 ,p_token2_value => SQLERRM);
3306 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3307 END get_contract_delinquency_stat;
3308 END IEX_OPI_PVT;