[Home] [Help]
PACKAGE BODY: APPS.OKL_OPI_PVT
Source
1 PACKAGE BODY OKL_OPI_PVT AS
2 /* $Header: OKLROPIB.pls 120.2 2006/09/25 09:25:52 dkagrawa 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 insert_pending_int
34 ---------------------------------------------------------------------------
35 PROCEDURE insert_pending_int(
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
38 p_contract_id IN NUMBER,
39 x_oinv_rec OUT NOCOPY oinv_rec_type,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2) AS
43
44 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
45 l_api_version CONSTANT NUMBER := 1;
46 l_api_name CONSTANT VARCHAR2(30) := 'insert_pending_int';
47
48 l_oinv_rec oinv_rec_type;
49 lx_oinv_rec oinv_rec_type;
50 l_contract_rec contract_rec_type;
51 l_party_rec party_rec_type;
52 l_contract_found BOOLEAN := FALSE;
53
54 CURSOR l_oin_csr(cp_contract_id IN NUMBER) IS
55 SELECT id
56 FROM okl_open_int
57 WHERE khr_id = cp_contract_id;
58 BEGIN
59 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
60 G_PKG_NAME,
61 p_init_msg_list,
62 l_api_version,
63 p_api_version,
64 '_PVT',
65 l_return_status);
66
67 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
68 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
69 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
70 RAISE OKC_API.G_EXCEPTION_ERROR;
71 END IF;
72
73 -- Processing starts
74 l_oinv_rec.khr_id := p_contract_id;
75
76 --Get contract information
77 get_contract(p_contract_id => l_oinv_rec.khr_id
78 ,x_contract_rec => l_contract_rec
79 ,x_return_status => l_return_status);
80
81 --dbms_output.put_line('contract # - ' || l_contract_rec.contract_number);
82
83 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
84 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
85 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
86 RAISE OKC_API.G_EXCEPTION_ERROR;
87 END IF;
88
89 l_oinv_rec.contract_number := l_contract_rec.contract_number;
90 l_oinv_rec.contract_type := l_contract_rec.contract_type;
91 l_oinv_rec.contract_status := l_contract_rec.contract_status;
92 l_oinv_rec.org_id := l_contract_rec.org_id;
93
94
95 --Get party information
96 get_party(p_contract_id => l_oinv_rec.khr_id
97 ,x_party_rec => l_party_rec
98 ,x_return_status => l_return_status);
99
100 --dbms_output.put_line('party name - ' || l_party_rec.party_name);
101
102 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
103 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
104 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
105 RAISE OKC_API.G_EXCEPTION_ERROR;
106 END IF;
107
108 l_oinv_rec.party_id := l_party_rec.party_id;
109 l_oinv_rec.party_name := l_party_rec.party_name;
110 l_oinv_rec.party_type := l_party_rec.party_type;
111
112 --Get case information
113 get_case(p_contract_id => l_oinv_rec.khr_id
114 ,x_cas_id => l_oinv_rec.cas_id
115 ,x_case_number => l_oinv_rec.case_number
116 ,x_return_status => l_return_status);
117
118
119 --dbms_output.put_line('case number - ' || l_oinv_rec.case_number);
120
121 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
122 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
123 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
124 RAISE okl_api.G_EXCEPTION_ERROR;
125 END IF;
126
127 FOR cur IN l_oin_csr(p_contract_id) LOOP
128 l_oinv_rec.id := cur.id;
129 l_contract_found := TRUE;
130 EXIT;
131 END LOOP;
132
133 IF l_contract_found THEN
134 okl_open_int_pub.update_open_int(p_api_version => l_api_version
135 ,p_init_msg_list => p_init_msg_list
136 ,x_return_status => l_return_status
137 ,x_msg_count => x_msg_count
138 ,x_msg_data => x_msg_data
139 ,p_oinv_rec => l_oinv_rec
140 ,x_oinv_rec => lx_oinv_rec);
141 ELSE
142 okl_open_int_pub.insert_open_int(p_api_version => l_api_version
143 ,p_init_msg_list => p_init_msg_list
144 ,x_return_status => l_return_status
145 ,x_msg_count => x_msg_count
146 ,x_msg_data => x_msg_data
147 ,p_oinv_rec => l_oinv_rec
148 ,x_oinv_rec => lx_oinv_rec);
149
150 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
151 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
152 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
153 RAISE OKC_API.G_EXCEPTION_ERROR;
154 END IF;
155 END IF;
156
157 x_oinv_rec := lx_oinv_rec;
158
159 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
160 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
161 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
162 RAISE okl_api.G_EXCEPTION_ERROR;
163 END IF;
164
165 -- Processing ends
166
167 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
168 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
169 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
170 RAISE okl_api.G_EXCEPTION_ERROR;
171 END IF;
172
173 x_return_status := l_return_status;
174 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
175 EXCEPTION
176 WHEN OKC_API.G_EXCEPTION_ERROR THEN
177 x_return_status := OKC_API.HANDLE_EXCEPTIONS
178 (
179 l_api_name,
180 G_PKG_NAME,
181 'OKC_API.G_RET_STS_ERROR',
182 x_msg_count,
183 x_msg_data,
184 '_PVT'
185 );
186 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
187 x_return_status := OKC_API.HANDLE_EXCEPTIONS
188 (
189 l_api_name,
190 G_PKG_NAME,
191 'OKC_API.G_RET_STS_UNEXP_ERROR',
192 x_msg_count,
193 x_msg_data,
194 '_PVT'
195 );
196 WHEN OTHERS THEN
197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
198 (
199 l_api_name,
200 G_PKG_NAME,
201 'OTHERS',
202 x_msg_count,
203 x_msg_data,
204 '_PVT'
205 );
206 END insert_pending_int;
207
208 ---------------------------------------------------------------------------
209 -- PROCEDURE process_pending_int
210 ---------------------------------------------------------------------------
211 PROCEDURE process_pending_int(
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
214 p_oinv_rec IN oinv_rec_type,
215 p_iohv_rec IN iohv_rec_type,
216 x_oinv_rec OUT NOCOPY oinv_rec_type,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2)AS
220
221 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
222 l_api_version CONSTANT NUMBER := 1;
223 l_api_name CONSTANT VARCHAR2(30) := 'process_pending_int';
224
225 l_oinv_rec oinv_rec_type;
226 lx_oinv_rec oinv_rec_type;
227
228 l_oipv_rec oipv_rec_type;
229 lx_oipv_rec oipv_rec_type;
230
231 l_iohv_rec iohv_rec_type;
232
233 l_party_rec party_rec_type;
234 l_contract_rec contract_rec_type;
235 l_out_contract_rec contract_rec_type;
236 l_guarantor_tbl party_tbl_type;
237 i NUMBER := 0;
238 l_guarantor_found BOOLEAN := FALSE;
239
240 CURSOR l_guarantor_csr(cp_khr_id IN NUMBER
241 ,cp_party_id IN NUMBER) IS
242 SELECT id
243 FROM okl_open_int_prty
244 WHERE khr_id = cp_khr_id
245 AND party_id = cp_party_id;
246 BEGIN
247 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
248 G_PKG_NAME,
249 p_init_msg_list,
250 l_api_version,
251 p_api_version,
252 '_PVT',
253 l_return_status);
254
255 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
256 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
257 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
258 RAISE OKC_API.G_EXCEPTION_ERROR;
259 END IF;
260
261 -- Processing starts
262 l_oinv_rec := p_oinv_rec;
263 l_iohv_rec := p_iohv_rec;
264
265 --Calling get_party to fill in party attributes
266 get_party(p_contract_id => l_oinv_rec.khr_id
267 ,x_party_rec => l_party_rec
268 ,x_return_status => l_return_status);
269
270 l_oinv_rec.date_of_birth := l_party_rec.date_of_birth;
271 l_oinv_rec.place_of_birth := l_party_rec.place_of_birth;
272 l_oinv_rec.person_identifier := l_party_rec.person_identifier;
273 l_oinv_rec.person_iden_type := l_party_rec.person_iden_type;
274 l_oinv_rec.address1 := l_party_rec.address1;
275 l_oinv_rec.address2 := l_party_rec.address2;
276 l_oinv_rec.address3 := l_party_rec.address3;
277 l_oinv_rec.address4 := l_party_rec.address4;
278 l_oinv_rec.country := l_party_rec.country;
279 l_oinv_rec.city := l_party_rec.city;
280 l_oinv_rec.postal_code := l_party_rec.postal_code;
281 l_oinv_rec.state := l_party_rec.state;
282 l_oinv_rec.province := l_party_rec.province;
283 l_oinv_rec.county := l_party_rec.county;
284 l_oinv_rec.po_box_number := l_party_rec.po_box_number;
285 l_oinv_rec.house_number := l_party_rec.house_number;
286 l_oinv_rec.street_suffix := l_party_rec.street_suffix;
287 l_oinv_rec.apartment_number := l_party_rec.apartment_number;
288 l_oinv_rec.street := l_party_rec.street;
289 l_oinv_rec.rural_route_number := l_party_rec.rural_route_number;
290 l_oinv_rec.street_number := l_party_rec.street_number;
291 l_oinv_rec.building := l_party_rec.building;
292 l_oinv_rec.floor := l_party_rec.floor;
293 l_oinv_rec.suite := l_party_rec.suite;
294 l_oinv_rec.room := l_party_rec.room;
295 l_oinv_rec.postal_plus4_code := l_party_rec.postal_plus4_code;
296
297 --Calling get_contract to fill in contract attributes
298 get_contract(p_contract_id => l_oinv_rec.khr_id
299 ,x_contract_rec => l_contract_rec
300 ,x_return_status => l_return_status);
301
302
303 l_oinv_rec.start_date := l_contract_rec.start_date;
304 l_oinv_rec.close_date := l_contract_rec.close_date;
305 l_oinv_rec.term_duration := l_contract_rec.term_duration;
306
307
308 --Calling get_contract_payment_info to fill in contract payment attributes
309 get_contract_payment_info(p_contract_rec => l_contract_rec
310 ,x_contract_rec => l_out_contract_rec
311 ,x_return_status => l_return_status);
312
313 l_oinv_rec.original_amount := l_out_contract_rec.original_amount;
314 l_oinv_rec.monthly_payment_amount := l_out_contract_rec.monthly_payment_amount;
315 l_oinv_rec.last_payment_date := l_out_contract_rec.last_payment_date;
316 l_oinv_rec.delinquency_occurance_date := l_out_contract_rec.delinquency_occurance_date;
317 l_oinv_rec.past_due_amount := l_out_contract_rec.past_due_amount;
318 l_oinv_rec.remaining_amount := l_out_contract_rec.remaining_amount;
319 l_oinv_rec.credit_indicator := l_out_contract_rec.credit_indicator;
320
321
322 --Calling get_case_owner to fill in case owner attributes
323 get_case_owner(p_cas_id => l_oinv_rec.cas_id
324 ,x_owner_resource_id => l_oinv_rec.contact_id
325 ,x_resource_name => l_oinv_rec.contact_name
326 ,x_resource_phone => l_oinv_rec.contact_phone
327 ,x_resource_email => l_oinv_rec.contact_email
328 ,x_return_status => l_return_status);
329
330
331 --Update open interface row with new data
332 okl_open_int_pub.update_open_int(p_api_version => l_api_version
333 ,p_init_msg_list => p_init_msg_list
334 ,x_return_status => l_return_status
335 ,x_msg_count => x_msg_count
336 ,x_msg_data => x_msg_data
337 ,p_oinv_rec => l_oinv_rec
338 ,x_oinv_rec => lx_oinv_rec);
339
340 --dbms_output.put_line('khr_id: ' || lx_oinv_rec.khr_id);
341 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
342 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
343 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
344 RAISE OKC_API.G_EXCEPTION_ERROR;
345 END IF;
346
347 x_oinv_rec := lx_oinv_rec;
348
349 --Get guarantors
350 get_guarantor(p_contract_id => lx_oinv_rec.khr_id
351 ,x_party_tbl => l_guarantor_tbl
352 ,x_return_status => l_return_status);
353
354 --insert/update guarantors into okl_open_int_prty_v
355 --DBMS_OUTPUT.PUT_LINE('GUARANTOR count - ' || l_guarantor_tbl.count);
356 FOR i IN 1..l_guarantor_tbl.COUNT LOOP
357 l_oipv_rec.khr_id := lx_oinv_rec.khr_id;
358 l_oipv_rec.party_id := l_guarantor_tbl(i).party_id;
359 l_oipv_rec.party_name := l_guarantor_tbl(i).party_name;
360 l_oipv_rec.org_id := lx_oinv_rec.org_id;
361 l_oipv_rec.address1 := l_guarantor_tbl(i).address1;
362 l_oipv_rec.address2 := l_guarantor_tbl(i).address2;
363 l_oipv_rec.address3 := l_guarantor_tbl(i).address3;
364 l_oipv_rec.address4 := l_guarantor_tbl(i).address4;
365 l_oipv_rec.country := l_guarantor_tbl(i).country;
366 l_oipv_rec.city := l_guarantor_tbl(i).city;
367 l_oipv_rec.postal_code := l_guarantor_tbl(i).postal_code;
368 l_oipv_rec.state := l_guarantor_tbl(i).state;
369 l_oipv_rec.province := l_guarantor_tbl(i).province;
370 l_oipv_rec.county := l_guarantor_tbl(i).county;
371 l_oipv_rec.po_box_number := l_guarantor_tbl(i).po_box_number;
372 l_oipv_rec.house_number := l_guarantor_tbl(i).house_number;
373 l_oipv_rec.street_suffix := l_guarantor_tbl(i).street_suffix;
374 l_oipv_rec.apartment_number := l_guarantor_tbl(i).apartment_number;
375 l_oipv_rec.street := l_guarantor_tbl(i).street;
376 l_oipv_rec.rural_route_number := l_guarantor_tbl(i).rural_route_number;
377 l_oipv_rec.street_number := l_guarantor_tbl(i).street_number;
378 l_oipv_rec.building := l_guarantor_tbl(i).building;
379 l_oipv_rec.floor := l_guarantor_tbl(i).floor;
380 l_oipv_rec.suite := l_guarantor_tbl(i).suite;
381 l_oipv_rec.room := l_guarantor_tbl(i).room;
382 l_oipv_rec.postal_plus4_code := l_guarantor_tbl(i).postal_plus4_code;
383
384 l_oipv_rec.phone_country_code := l_guarantor_tbl(i).phone_country_code;
385 l_oipv_rec.phone_area_code := l_guarantor_tbl(i).phone_area_code;
386 l_oipv_rec.phone_number := l_guarantor_tbl(i).phone_number;
387 l_oipv_rec.phone_extension := l_guarantor_tbl(i).phone_extension;
388
389 --DBMS_OUTPUT.PUT_LINE('GUARANTOR - ' || l_guarantor_tbl(i).party_name);
390 l_guarantor_found := FALSE;
391 FOR cur in l_guarantor_csr(l_oipv_rec.khr_id, l_oipv_rec.party_id ) LOOP
392 l_guarantor_found := TRUE;
393 l_oipv_rec.id := cur.id;
394 EXIT;
395 END LOOP;
396
397 IF (l_guarantor_found) THEN
398 okl_open_int_prty_pub.update_open_int_prty( p_api_version => l_api_version
399 ,p_init_msg_list => p_init_msg_list
400 ,x_return_status => l_return_status
401 ,x_msg_count => x_msg_count
402 ,x_msg_data => x_msg_data
403 ,p_oipv_rec => l_oipv_rec
404 ,x_oipv_rec => lx_oipv_rec);
405 /*
406 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
407 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
408 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
409 RAISE OKC_API.G_EXCEPTION_ERROR;
410 END IF;
411 */
412 ELSE
413 --dbms_output.put_line('khr_id : ' || l_oipv_rec.khr_id);
414 --dbms_output.put_line('party_id : ' || l_oipv_rec.party_id);
415 --dbms_output.put_line('party_name : ' || l_oipv_rec.party_name);
416
417 okl_open_int_prty_pub.insert_open_int_prty( p_api_version => l_api_version
418 ,p_init_msg_list => p_init_msg_list
419 ,x_return_status => l_return_status
420 ,x_msg_count => x_msg_count
421 ,x_msg_data => x_msg_data
422 ,p_oipv_rec => l_oipv_rec
423 ,x_oipv_rec => lx_oipv_rec);
424 /*
425 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
426 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
427 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
428 RAISE OKC_API.G_EXCEPTION_ERROR;
429 END IF;
430 */
431 END IF;
432 END LOOP;
433
434
435 --Get contract asset details
436 IF (p_iohv_rec.action = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY) THEN
437 --dbms_OUTPUT.PUT_LINE('Processing assets...');
438 process_pending_asset(p_api_version => l_api_version
439 ,p_init_msg_list => p_init_msg_list
440 ,p_iohv_rec => l_iohv_rec
441 ,x_return_status => l_return_status
442 ,x_msg_count => x_msg_count
443 ,x_msg_data => x_msg_data);
444
445 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
446 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
447 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
448 RAISE OKC_API.G_EXCEPTION_ERROR;
449 END IF;
450 END IF;
451
452 -- Processing ends
453 x_return_status := l_return_status;
454 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
455 EXCEPTION
456 WHEN OKC_API.G_EXCEPTION_ERROR THEN
457 x_return_status := OKC_API.HANDLE_EXCEPTIONS
458 (
459 l_api_name,
460 G_PKG_NAME,
461 'OKC_API.G_RET_STS_ERROR',
462 x_msg_count,
463 x_msg_data,
464 '_PVT'
465 );
466 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
467 x_return_status := OKC_API.HANDLE_EXCEPTIONS
468 (
469 l_api_name,
470 G_PKG_NAME,
471 'OKC_API.G_RET_STS_UNEXP_ERROR',
472 x_msg_count,
473 x_msg_data,
474 '_PVT'
475 );
476 WHEN OTHERS THEN
477 x_return_status := OKC_API.HANDLE_EXCEPTIONS
478 (
479 l_api_name,
480 G_PKG_NAME,
481 'OTHERS',
482 x_msg_count,
483 x_msg_data,
484 '_PVT'
485 );
486 END process_pending_int;
487
488 ---------------------------------------------------------------------------
489 -- PROCEDURE process_pending_asset
490 ---------------------------------------------------------------------------
491 PROCEDURE process_pending_asset(
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
494 p_iohv_rec IN iohv_rec_type,
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_msg_data OUT NOCOPY VARCHAR2) AS
498
499 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
500 l_api_version CONSTANT NUMBER := 1;
501 l_api_name CONSTANT VARCHAR2(30) := 'process_pending_asset';
502 lx_oiav_tbl oiav_tbl_type;
503 l_oiav_rec oiav_rec_type;
504 lx_oiav_rec oiav_rec_type;
505
506 i NUMBER := 0;
507 l_asset_found BOOLEAN := FALSE;
508
509 CURSOR l_asset_csr(cp_khr_id IN VARCHAR2
510 ,cp_instance_number IN VARCHAR2) IS
511 SELECT id
512 FROM okl_open_int_asst
513 WHERE khr_id = cp_khr_id
514 AND instance_number = cp_instance_number;
515 BEGIN
516 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
517 G_PKG_NAME,
518 p_init_msg_list,
519 l_api_version,
520 p_api_version,
521 '_PVT',
522 l_return_status);
523
524 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
525 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
526 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
527 RAISE OKC_API.G_EXCEPTION_ERROR;
528 END IF;
529
530 -- Processing starts
531
532 --Get contract assets details
533 lx_oiav_tbl.delete;
534 get_assets(p_contract_id => TO_NUMBER(p_iohv_rec.object1_id1),
535 x_oiav_tbl => lx_oiav_tbl,
536 x_return_status => l_return_status);
537
538 --dbms_OUTPUT.PUT_LINE('assets found : ' || lx_oiav_tbl.count);
539 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
541 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
542 RAISE OKC_API.G_EXCEPTION_ERROR;
543 END IF;
544
545 --insert/update assets in open interface assets
546 FOR i IN 1..lx_oiav_tbl.count LOOP
547 l_oiav_rec := lx_oiav_tbl(i);
548
549 l_asset_found := FALSE;
550 --dbms_OUTPUT.PUT_LINE('khr_id : ' || l_oiav_rec.khr_id);
551 --dbms_OUTPUT.PUT_LINE('instance_number : ' || l_oiav_rec.instance_number);
552 --dbms_OUTPUT.PUT_LINE('asset_id : ' || l_oiav_rec.asset_id);
553 FOR cur_asset_csr IN l_asset_csr(l_oiav_rec.khr_id
554 ,l_oiav_rec.instance_number) LOOP
555 l_asset_found := TRUE;
556 l_oiav_rec.id := cur_asset_csr.id;
557 --dbms_OUTPUT.PUT_LINE('found : ' || l_oiav_rec.instance_number);
558 END LOOP;
559
560 IF (l_asset_found) THEN
561 --dbms_OUTPUT.PUT_LINE('updating asset');
562 okl_open_int_asst_pub.update_open_int_asst (
563 p_api_version => p_api_version,
564 p_init_msg_list => OKC_API.G_FALSE,
565 x_return_status => l_return_status,
566 x_msg_count => x_msg_count,
567 x_msg_data => x_msg_data,
568 p_oiav_rec => l_oiav_rec,
569 x_oiav_rec => lx_oiav_rec);
570
571 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
572 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
573 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
574 RAISE OKC_API.G_EXCEPTION_ERROR;
575 END IF;
576 ELSE
577 --dbms_OUTPUT.PUT_LINE('inserting asset');
578 l_oiav_rec.org_id := p_iohv_rec.org_id;
579 okl_open_int_asst_pub.insert_open_int_asst (
580 p_api_version => p_api_version,
581 p_init_msg_list => OKC_API.G_FALSE,
582 x_return_status => l_return_status,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 p_oiav_rec => l_oiav_rec,
586 x_oiav_rec => lx_oiav_rec);
587 /*
588 --dbms_OUTPUT.PUT_LINE('x_return_status : ' || l_return_status);
589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
590 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
592 RAISE OKC_API.G_EXCEPTION_ERROR;
593 END IF;
594 --dbms_OUTPUT.PUT_LINE('inserted asset');
595 */
596 END IF;
597
598 END LOOP;
599
600 -- Processing ends
601 l_return_status := Okc_Api.G_RET_STS_SUCCESS;
602 x_return_status := l_return_status;
603 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
604 EXCEPTION
605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
607 (
608 l_api_name,
609 G_PKG_NAME,
610 'OKC_API.G_RET_STS_ERROR',
611 x_msg_count,
612 x_msg_data,
613 '_PVT'
614 );
615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
616 x_return_status := OKC_API.HANDLE_EXCEPTIONS
617 (
618 l_api_name,
619 G_PKG_NAME,
620 'OKC_API.G_RET_STS_UNEXP_ERROR',
621 x_msg_count,
622 x_msg_data,
623 '_PVT'
624 );
625 WHEN OTHERS THEN
626 x_return_status := OKC_API.HANDLE_EXCEPTIONS
627 (
628 l_api_name,
629 G_PKG_NAME,
630 'OTHERS',
631 x_msg_count,
632 x_msg_data,
633 '_PVT'
634 );
635 END process_pending_asset;
636
637 /*
638 ---------------------------------------------------------------------------
639 -- PROCEDURE report_all_credit_bureau
640 ---------------------------------------------------------------------------
641 PROCEDURE report_all_credit_bureau(
642 errbuf OUT NOCOPY VARCHAR2,
643 retcode OUT NOCOPY NUMBER) AS
644
645 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE ;
646 lx_msg_count NUMBER ;
647 lx_msg_data VARCHAR2(2000);
648 lx_message VARCHAR2(2000);
649 l_api_version CONSTANT NUMBER := 1;
650 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
651 l_api_name CONSTANT VARCHAR2(30) := 'report_all_credit_bureau';
652
653 l_oinv_rec oinv_rec_type;
654 l_iohv_rec iohv_rec_type;
655 lx_oinv_rec oinv_rec_type;
656 lx_iohv_rec iohv_rec_type;
657
658 l_rows_processed NUMBER := 0;
659 l_rows_failed NUMBER := 0;
660 l_cust_reported NUMBER := 0;
661 l_cust_not_reported NUMBER := 0;
662 l_syndicate_flag VARCHAR2(1) := 'N';
663
664 l_organization_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;
665
666 CURSOR l_report_all_csr(cp_organization_id IN NUMBER) IS
667 SELECT id
668 FROM okc_k_headers_v
669 WHERE authoring_org_id = cp_organization_id
670 AND scs_code = 'LEASE';
671 BEGIN
672 --get organization id
673 l_organization_id := okl_context.get_okc_org_id;
674 --dbms_output.put_line('org is : ' || l_organization_id);
675
676 --check if organization_id is null, set it from the profile
677 IF (l_organization_id IS NULL) THEN
678 --dbms_output.put_line('org is not set');
679 okl_context.set_okc_org_context(null,null);
680 --dbms_output.put_line('org is now set');
681 l_organization_id := okl_context.get_okc_org_id;
682 --dbms_output.put_line('org is : ' || l_organization_id);
683 END IF;
684
685 -- Get pending records to be processed
686 OPEN l_report_all_csr(l_organization_id);
687 LOOP
688 FETCH l_report_all_csr INTO
689 l_oinv_rec.khr_id;
690 EXIT WHEN l_report_all_csr%NOTFOUND;
691
692 --find out lessee syndicate flag
693 l_return_status := OKL_CONTRACT_INFO.get_syndicate_flag(
694 p_contract_id => l_oinv_rec.khr_id
695 ,x_syndicate_flag => l_syndicate_flag);
696
697 IF NOT ((l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) OR
698 (l_return_status = okl_api.G_RET_STS_ERROR) OR
699 (l_syndicate_flag = 'Y')) THEN
700 l_cust_reported := l_cust_reported + 1;
701 --dbms_output.put_line('report - ' || l_oinv_rec.khr_id);
702 iex_open_interface_pub.insert_pending(
703 p_api_version => l_api_version,
704 p_init_msg_list => l_init_msg_list,
705 p_object1_id1 => l_oinv_rec.khr_id,
706 p_object1_id2 => '#',
707 p_jtot_object1_code => 'OKX_LEASE',
708 p_action => IEX_OPI_PVT.ACTION_REPORT_CB,
709 p_status => IEX_OPI_PVT.STATUS_PENDING_ALL,
710 p_comments => OKC_API.G_MISS_CHAR,
711 p_ext_agncy_id => NULL,
712 p_transfer_days => NULL,
713 p_extend_days => NULL,
714 x_return_status => l_return_status,
715 x_msg_count => lx_msg_count,
716 x_msg_data => lx_msg_data);
717
718 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
719 l_rows_failed := l_rows_failed + 1;
720 ELSE
721 l_rows_processed := l_rows_processed + 1;
722 END IF;
723 ELSE
724 l_cust_not_reported := l_cust_not_reported + 1;
725 --dbms_output.put_line('do not report - ' || l_oinv_rec.id);
726 END IF;
727
728 END LOOP;
729
730 CLOSE l_report_all_csr;
731
732 --dbms_output.PUT_LINE('CUSTOMERS REPORTED = ' || l_cust_reported);
733 --dbms_output.PUT_LINE('CUSTOMERS NOT REPORTED = ' || l_cust_not_reported);
734 --dbms_output.PUT_LINE('CUSTOMERS TO BE REPORTED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
735 --dbms_output.PUT_LINE('CUSTOMERS TO BE REPORTED NOT PROCESSED = ' || l_rows_failed);
736
737 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS REPORTED = ' || l_cust_reported);
738 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS NOT REPORTED = ' || l_cust_not_reported);
739 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE REPORTED PROCESSED SUCCESSFULLY = ' || l_rows_processed);
740 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'CUSTOMERS TO BE REPORTED NOT PROCESSED = ' || l_rows_failed);
741
742 COMMIT;
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF l_report_all_csr%ISOPEN THEN
746 CLOSE l_report_all_csr;
747 END IF;
748 errbuf := substr(SQLERRM, 1, 200);
749 retcode := 1;
750 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'SQL ERROR : SQLCODE = ' || SQLCODE);
751 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, ' MESSAGE = ' || SQLERRM);
752 ROLLBACK;
753 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
754 END report_all_credit_bureau;
755 */
756
757 ---------------------------------------------------------------------------
758 -- PROCEDURE get_party
759 ---------------------------------------------------------------------------
760 PROCEDURE get_party(
761 p_contract_id IN NUMBER,
762 x_party_rec OUT NOCOPY party_rec_type,
763 x_return_status OUT NOCOPY VARCHAR2) AS
764
765 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
766 l_bill_to_add_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
767 l_party_rec party_rec_type;
768 l_bill_to_address_id HZ_LOCATIONS.LOCATION_ID%TYPE;
769
770 CURSOR l_party_csr(cp_party_id IN NUMBER) IS
771 SELECT hp.party_name
772 ,hp.party_type
773 FROM hz_parties hp
774 WHERE hp.party_id = cp_party_id;
775
776 CURSOR l_pp_csr(cp_party_id IN NUMBER) IS
777 SELECT hpp.date_of_birth
778 ,hpp.place_of_birth
779 ,hpp.person_identifier
780 ,hpp.person_iden_type
781 FROM hz_person_profiles hpp
782 WHERE hpp.party_id = cp_party_id;
783
784 CURSOR l_hzl_csr(cp_bill_to_address_id IN NUMBER) IS
785 SELECT hzl.address1
786 ,hzl.address2
787 ,hzl.address3
788 ,hzl.address4
789 ,hzl.country
790 ,hzl.city
791 ,hzl.postal_code
792 ,hzl.state
793 ,hzl.province
794 ,hzl.county
795 ,hzl.po_box_number
796 ,hzl.house_number
797 ,hzl.street_suffix
798 ,hzl.apartment_number
799 ,hzl.street
800 ,hzl.rural_route_number
801 ,hzl.street_number
802 ,hzl.building
803 ,hzl.floor
804 ,hzl.suite
805 ,hzl.room
806 ,hzl.postal_plus4_code
807 FROM hz_locations hzl
808 WHERE hzl.location_id = cp_bill_to_address_id;
809 BEGIN
810 l_return_status := OKL_CONTRACT_INFO.get_customer(p_contract_id, l_party_rec.party_id);
811 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
812 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
813 ,p_msg_name => G_INVALID_PARTY);
814 END IF;
815
816 OPEN l_party_csr(l_party_rec.party_id);
817 FETCH l_party_csr INTO
818 l_party_rec.party_name
819 ,l_party_rec.party_type;
820 CLOSE l_party_csr;
821
822 OPEN l_pp_csr(l_party_rec.party_id);
823 FETCH l_pp_csr INTO
824 l_party_rec.date_of_birth
825 ,l_party_rec.place_of_birth
826 ,l_party_rec.person_identifier
827 ,l_party_rec.person_iden_type;
828 CLOSE l_pp_csr;
829
830
831 l_bill_to_add_return_status := OKL_CONTRACT_INFO.get_bill_to_address(p_contract_id, l_bill_to_address_id);
832
833 OPEN l_hzl_csr(l_bill_to_address_id);
834 FETCH l_hzl_csr INTO
835 l_party_rec.address1
836 ,l_party_rec.address2
837 ,l_party_rec.address3
838 ,l_party_rec.address4
839 ,l_party_rec.country
840 ,l_party_rec.city
841 ,l_party_rec.postal_code
842 ,l_party_rec.state
843 ,l_party_rec.province
844 ,l_party_rec.county
845 ,l_party_rec.po_box_number
846 ,l_party_rec.house_number
847 ,l_party_rec.street_suffix
848 ,l_party_rec.apartment_number
849 ,l_party_rec.street
850 ,l_party_rec.rural_route_number
851 ,l_party_rec.street_number
852 ,l_party_rec.building
853 ,l_party_rec.floor
854 ,l_party_rec.suite
855 ,l_party_rec.room
856 ,l_party_rec.postal_plus4_code;
857 --dbms_output.put_line('bill to add found');
858 CLOSE l_hzl_csr;
859
860 x_party_rec := l_party_rec;
861 x_return_status := l_return_status;
862 EXCEPTION
863 WHEN OTHERS THEN
864 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
865 ,p_msg_name => G_UNEXPECTED_ERROR
866 ,p_token1 => G_SQLCODE_TOKEN
867 ,p_token1_value => SQLCODE
868 ,p_token2 => G_SQLERRM_TOKEN
869 ,p_token2_value => SQLERRM);
870 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
871 END get_party;
872
873 ---------------------------------------------------------------------------
874 -- PROCEDURE get_guarantor
875 ---------------------------------------------------------------------------
876 PROCEDURE get_guarantor(
877 p_contract_id IN NUMBER,
878 x_party_tbl OUT NOCOPY party_tbl_type,
879 x_return_status OUT NOCOPY VARCHAR2) AS
880
881 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
882 l_guarantor_tbl party_tbl_type;
883 l_guarantor_rec party_rec_type;
884 i NUMBER := 0;
885
886 CURSOR l_guarantor_csr(cp_contract_id IN NUMBER) IS
887 SELECT hp.party_id
888 ,hp.party_name
889 ,hl.address1
890 ,hl.address2
891 ,hl.address3
892 ,hl.address4
893 ,hl.country
894 ,hl.city
895 ,hl.postal_code
896 ,hl.state
897 ,hl.province
898 ,hl.county
899 ,hl.po_box_number
900 ,hl.house_number
901 ,hl.street_suffix
902 ,hl.apartment_number
903 ,hl.street
904 ,hl.rural_route_number
905 ,hl.street_number
906 ,hl.building
907 ,hl.floor
908 ,hl.suite
909 ,hl.room
910 ,hl.postal_plus4_code
911 FROM okc_k_party_roles_b opr
912 ,hz_parties hp
913 ,hz_party_sites hps
914 ,hz_locations hl
915 ,hz_party_site_uses hpsu
916 WHERE opr.dnz_chr_id = cp_contract_id
917 AND opr.rle_code = 'GUARANTOR'
918 AND opr.object1_id1 = hp.party_id
919 AND hp.party_id = hps.party_id
920 AND hps.party_site_id = hpsu.party_site_id
921 AND hpsu.site_use_type = 'BILL_TO'
922 AND hpsu.primary_per_type = 'Y'
923 AND hps.location_id = hl.location_id
924 ORDER BY hp.party_id;
925
926 CURSOR l_phone_csr(cp_party_id IN NUMBER) IS
927 SELECT phone_country_code
928 ,phone_area_code
929 ,phone_number
930 ,phone_extension
931 FROM hz_contact_points
932 WHERE owner_table_name = 'HZ_PARTIES'
933 AND owner_table_id = cp_party_id
934 AND contact_point_type = 'PHONE'
935 AND primary_flag = 'Y'
936 AND status = 'A';
937 BEGIN
938 --dbms_output.put_line('getting guarantors for contract ' || p_contract_id);
939 OPEN l_guarantor_csr(p_contract_id);
940 LOOP
941 FETCH l_guarantor_csr INTO
942 l_guarantor_rec.party_id
943 ,l_guarantor_rec.party_name
944 ,l_guarantor_rec.address1
945 ,l_guarantor_rec.address2
946 ,l_guarantor_rec.address3
947 ,l_guarantor_rec.address4
948 ,l_guarantor_rec.country
949 ,l_guarantor_rec.city
950 ,l_guarantor_rec.postal_code
951 ,l_guarantor_rec.state
952 ,l_guarantor_rec.province
953 ,l_guarantor_rec.county
954 ,l_guarantor_rec.po_box_number
955 ,l_guarantor_rec.house_number
956 ,l_guarantor_rec.street_suffix
957 ,l_guarantor_rec.apartment_number
958 ,l_guarantor_rec.street
959 ,l_guarantor_rec.rural_route_number
960 ,l_guarantor_rec.street_number
961 ,l_guarantor_rec.building
962 ,l_guarantor_rec.floor
963 ,l_guarantor_rec.suite
964 ,l_guarantor_rec.room
965 ,l_guarantor_rec.postal_plus4_code;
966 EXIT WHEN l_guarantor_csr%NOTFOUND;
967
968 FOR cur_phone IN l_phone_csr(l_guarantor_rec.party_id) LOOP
969 l_guarantor_rec.phone_country_code := cur_phone.phone_country_code;
970 l_guarantor_rec.phone_area_code := cur_phone.phone_area_code;
971 l_guarantor_rec.phone_number := cur_phone.phone_number;
972 l_guarantor_rec.phone_extension := cur_phone.phone_extension;
973 EXIT;
974 END LOOP;
975
976 i := i + 1;
977 l_guarantor_tbl(i).party_id := l_guarantor_rec.party_id;
978 l_guarantor_tbl(i).party_name := l_guarantor_rec.party_name;
979 l_guarantor_tbl(i).address1 := l_guarantor_rec.address1;
980 l_guarantor_tbl(i).address2 := l_guarantor_rec.address2;
981 l_guarantor_tbl(i).address3 := l_guarantor_rec.address3;
982 l_guarantor_tbl(i).address4 := l_guarantor_rec.address4;
983 l_guarantor_tbl(i).country := l_guarantor_rec.country;
984 l_guarantor_tbl(i).city := l_guarantor_rec.city;
985 l_guarantor_tbl(i).postal_code := l_guarantor_rec.postal_code;
986 l_guarantor_tbl(i).state := l_guarantor_rec.state;
987 l_guarantor_tbl(i).province := l_guarantor_rec.province;
988 l_guarantor_tbl(i).county := l_guarantor_rec.county;
989 l_guarantor_tbl(i).po_box_number := l_guarantor_rec.po_box_number;
990 l_guarantor_tbl(i).house_number := l_guarantor_rec.house_number;
991 l_guarantor_tbl(i).street_suffix := l_guarantor_rec.street_suffix;
992 l_guarantor_tbl(i).apartment_number := l_guarantor_rec.apartment_number;
993 l_guarantor_tbl(i).street := l_guarantor_rec.street;
994 l_guarantor_tbl(i).rural_route_number := l_guarantor_rec.rural_route_number;
995 l_guarantor_tbl(i).street_number := l_guarantor_rec.street_number;
996 l_guarantor_tbl(i).building := l_guarantor_rec.building;
997 l_guarantor_tbl(i).floor := l_guarantor_rec.floor;
998 l_guarantor_tbl(i).suite := l_guarantor_rec.suite;
999 l_guarantor_tbl(i).room := l_guarantor_rec.room;
1000 l_guarantor_tbl(i).postal_plus4_code := l_guarantor_rec.postal_plus4_code;
1001 l_guarantor_tbl(i).phone_country_code := l_guarantor_rec.phone_country_code;
1002 l_guarantor_tbl(i).phone_area_code := l_guarantor_rec.phone_area_code;
1003 l_guarantor_tbl(i).phone_number := l_guarantor_rec.phone_number;
1004 l_guarantor_tbl(i).phone_extension := l_guarantor_rec.phone_extension;
1005 END LOOP;
1006 CLOSE l_guarantor_csr;
1007
1008 x_party_tbl := l_guarantor_tbl;
1009 --dbms_output.put_line('guarantors count ' || x_party_tbl.count);
1010 x_return_status := l_return_status;
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 IF l_guarantor_csr%ISOPEN THEN
1014 CLOSE l_guarantor_csr;
1015 END IF;
1016 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1017 ,p_msg_name => G_UNEXPECTED_ERROR
1018 ,p_token1 => G_SQLCODE_TOKEN
1019 ,p_token1_value => SQLCODE
1020 ,p_token2 => G_SQLERRM_TOKEN
1021 ,p_token2_value => SQLERRM);
1022 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1023 END get_guarantor;
1024
1025 ---------------------------------------------------------------------------
1026 -- PROCEDURE get_case
1027 ---------------------------------------------------------------------------
1028 PROCEDURE get_case(
1029 p_contract_id IN NUMBER,
1030 x_cas_id OUT NOCOPY NUMBER,
1031 x_case_number OUT NOCOPY VARCHAR2,
1032 x_return_status OUT NOCOPY VARCHAR2) AS
1033
1034 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1035 l_cas_id IEX_CASE_OBJECTS.CAS_ID%TYPE;
1036 l_case_number IEX_CASES_ALL_B.CASE_NUMBER%TYPE;
1037
1038 CURSOR l_case_csr(cp_contract_id IN NUMBER) IS
1039 SELECT ico.cas_id
1040 ,ica.case_number
1041 FROM iex_case_objects ico
1042 ,iex_cases_all_b ica
1043 WHERE ico.object_id = cp_contract_id
1044 AND ico.cas_id = ica.cas_id
1045 AND ica.active_flag = 'Y';
1046 BEGIN
1047 OPEN l_case_csr(p_contract_id);
1048 FETCH l_case_csr INTO
1049 l_cas_id
1050 ,l_case_number;
1051 IF l_case_csr%NOTFOUND THEN
1052 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1053 ,p_msg_name => G_INVALID_CASE);
1054 l_return_status := OKC_API.G_RET_STS_ERROR;
1055 CLOSE l_case_csr;
1056 ELSE
1057 CLOSE l_case_csr;
1058 END IF;
1059 x_cas_id := l_cas_id;
1060 x_case_number := l_case_number;
1061 x_return_status := l_return_status;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1065 ,p_msg_name => G_UNEXPECTED_ERROR
1066 ,p_token1 => G_SQLCODE_TOKEN
1067 ,p_token1_value => SQLCODE
1068 ,p_token2 => G_SQLERRM_TOKEN
1069 ,p_token2_value => SQLERRM);
1070 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1071 END get_case;
1072
1073 ---------------------------------------------------------------------------
1074 -- PROCEDURE get_contract
1075 ---------------------------------------------------------------------------
1076 PROCEDURE get_contract(
1077 p_contract_id IN NUMBER,
1078 x_contract_rec OUT NOCOPY contract_rec_type,
1079 x_return_status OUT NOCOPY VARCHAR2) AS
1080
1081 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1082 l_contract_rec contract_rec_type;
1083
1084 CURSOR l_khr_csr(cp_contract_id IN NUMBER) IS
1085 SELECT okhv.contract_number
1086 ,okhv.scs_code
1087 ,okhv.sts_code
1088 ,okhv.start_date
1089 ,nvl(okhv.date_terminated, okhv.end_date) close_date
1090 ,okh.term_duration
1091 ,okhv.authoring_org_id
1092 FROM okc_k_headers_v okhv
1093 ,okl_k_headers okh
1094 WHERE okhv.id = cp_contract_id
1095 AND okhv.id = okh.id;
1096
1097 BEGIN
1098 l_contract_rec.khr_id := p_contract_id;
1099 OPEN l_khr_csr(l_contract_rec.khr_id);
1100 FETCH l_khr_csr INTO
1101 l_contract_rec.contract_number
1102 ,l_contract_rec.contract_type
1103 ,l_contract_rec.contract_status
1104 ,l_contract_rec.start_date
1105 ,l_contract_rec.close_date
1106 ,l_contract_rec.term_duration
1107 ,l_contract_rec.org_id;
1108 IF l_khr_csr%NOTFOUND THEN
1109 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1110 ,p_msg_name => G_INVALID_CONTRACT);
1111 CLOSE l_khr_csr;
1112 l_return_status := OKC_API.G_RET_STS_ERROR;
1113 ELSE
1114 CLOSE l_khr_csr;
1115 END IF;
1116 x_contract_rec := l_contract_rec;
1117 x_return_status := l_return_status;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1121 ,p_msg_name => G_UNEXPECTED_ERROR
1122 ,p_token1 => G_SQLCODE_TOKEN
1123 ,p_token1_value => SQLCODE
1124 ,p_token2 => G_SQLERRM_TOKEN
1125 ,p_token2_value => SQLERRM);
1126 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1127 END get_contract;
1128
1129 ---------------------------------------------------------------------------
1130 -- PROCEDURE get_contract_payment_info
1131 ---------------------------------------------------------------------------
1132 PROCEDURE get_contract_payment_info(
1133 p_contract_rec IN contract_rec_type,
1134 x_contract_rec OUT NOCOPY contract_rec_type,
1135 x_return_status OUT NOCOPY VARCHAR2) AS
1136
1137 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1138 l_contract_rec contract_rec_type;
1139 l_security_deposit NUMBER;
1140 l_interest_type NUMBER;
1141
1142 CURSOR l_khr_past_due_csr(cp_contract_id IN NUMBER) IS
1143 SELECT sum(nvl(aps.amount_due_remaining, 0)) past_due_amount
1144 FROM okl_cnsld_ar_strms_b ocas
1145 ,ar_payment_schedules_all aps
1146 WHERE ocas.khr_id = cp_contract_id
1147 AND ocas.receivables_invoice_id = aps.customer_trx_id
1148 AND aps.class = 'INV'
1149 AND aps.due_date < sysdate
1150 AND nvl(aps.amount_due_remaining, 0) > 0;
1151
1152 CURSOR l_khr_due_date(cp_contract_id IN NUMBER) IS
1153 SELECT min(aps.due_date) due_date
1154 FROM okl_cnsld_ar_strms_b ocas
1155 ,ar_payment_schedules_all aps
1156 WHERE ocas.khr_id = cp_contract_id
1157 AND ocas.receivables_invoice_id = aps.customer_trx_id
1158 AND aps.class = 'INV'
1159 AND aps.due_date < sysdate
1160 AND nvl(aps.amount_due_remaining, 0) > 0;
1161
1162 CURSOR l_khr_last_pymt_date(cp_contract_id IN NUMBER) IS
1163 SELECT min(ara.apply_date) apply_date
1164 FROM okl_cnsld_ar_strms_b ocas
1165 ,ar_payment_schedules_all aps
1166 ,ar_receivable_applications_all ara
1167 WHERE ocas.khr_id = cp_contract_id
1168 AND ocas.receivables_invoice_id = aps.customer_trx_id
1169 AND aps.class = 'INV'
1170 AND aps.payment_schedule_id = ara.payment_schedule_id
1171 AND ara.status = 'APP';
1172 BEGIN
1173 l_contract_rec := p_contract_rec;
1174
1175 --Get past due amount
1176 OPEN l_khr_past_due_csr(l_contract_rec.khr_id);
1177 FETCH l_khr_past_due_csr INTO
1178 l_contract_rec.past_due_amount;
1179 CLOSE l_khr_past_due_csr;
1180
1181 --Get past due date (date when delinquency occured)
1182 OPEN l_khr_due_date(l_contract_rec.khr_id);
1183 FETCH l_khr_due_date INTO
1184 l_contract_rec.delinquency_occurance_date;
1185 CLOSE l_khr_due_date;
1186
1187 --Get last payment date
1188 OPEN l_khr_last_pymt_date(l_contract_rec.khr_id);
1189 FETCH l_khr_last_pymt_date INTO
1190 l_contract_rec.last_payment_date;
1191 CLOSE l_khr_last_pymt_date;
1192
1193 --code for getting original_amount
1194 /*
1195 l_contract_rec.original_amount := okl_formula_function_pvt.ctrt_capitalamount(
1196 p_chr_id => l_contract_rec.khr_id,
1197 p_line_id => NULL);
1198 */
1199 l_contract_rec.original_amount := NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_oec(p_chr_id => l_contract_rec.khr_id, p_line_id => NULL),0)
1200 - NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_tradein(p_chr_id => l_contract_rec.khr_id, p_line_id => NULL),0)
1201 - NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capital_reduction(p_chr_id => l_contract_rec.khr_id, p_line_id => NULL),0)
1202 + NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_fees_capitalized(p_chr_id => l_contract_rec.khr_id, p_line_id => NULL),0);
1203
1204
1205 --code for getting monthly_payment_amount
1206 l_return_status := OKL_CONTRACT_INFO.get_rent_security_interest(
1207 p_contract_id => l_contract_rec.khr_id,
1208 x_advance_rent => l_contract_rec.monthly_payment_amount,
1209 x_security_deposit => l_security_deposit,
1210 x_interest_type => l_interest_type);
1211
1212 --code for getting remaining_amount
1213 l_return_status := OKL_CONTRACT_INFO.get_outstanding_rcvble(
1214 p_contract_id => l_contract_rec.khr_id,
1215 x_rcvble_amt => l_contract_rec.remaining_amount);
1216
1217 --code for setting credit_indicator
1218 IF (SIGN(l_contract_rec.remaining_amount) = -1) THEN
1219 l_contract_rec.credit_indicator := 'Debit';
1220 ELSE
1221 l_contract_rec.credit_indicator := 'Credit';
1222 END IF;
1223
1224 x_contract_rec := l_contract_rec;
1225 x_return_status := l_return_status;
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1229 ,p_msg_name => G_UNEXPECTED_ERROR
1230 ,p_token1 => G_SQLCODE_TOKEN
1231 ,p_token1_value => SQLCODE
1232 ,p_token2 => G_SQLERRM_TOKEN
1233 ,p_token2_value => SQLERRM);
1234 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1235 END get_contract_payment_info;
1236
1237 ---------------------------------------------------------------------------
1238 -- PROCEDURE get_case_owner
1239 ---------------------------------------------------------------------------
1240 PROCEDURE get_case_owner(
1241 p_cas_id IN NUMBER,
1242 x_owner_resource_id OUT NOCOPY NUMBER,
1243 x_resource_name OUT NOCOPY VARCHAR2,
1244 x_resource_phone OUT NOCOPY VARCHAR2,
1245 x_resource_email OUT NOCOPY VARCHAR2,
1246 x_return_status OUT NOCOPY VARCHAR2) AS
1247
1248 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1249
1250 CURSOR l_resource_csr(cp_cas_id IN NUMBER) IS
1251 SELECT ica.owner_resource_id
1252 ,jre.source_name
1253 ,jre.source_phone
1254 ,jre.source_email
1255 FROM iex_cases_all_b ica
1256 ,jtf_rs_resource_extns jre
1257 WHERE ica.cas_id = cp_cas_id
1258 AND ica.owner_resource_id = jre.resource_id;
1259 BEGIN
1260 FOR cur IN l_resource_csr(p_cas_id) LOOP
1261 x_owner_resource_id := cur.owner_resource_id;
1262 x_resource_name := cur.source_name;
1263 x_resource_phone := cur.source_phone;
1264 x_resource_email := cur.source_email;
1265 EXIT;
1266 END LOOP;
1267 x_return_status := l_return_status;
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1271 ,p_msg_name => G_UNEXPECTED_ERROR
1272 ,p_token1 => G_SQLCODE_TOKEN
1273 ,p_token1_value => SQLCODE
1274 ,p_token2 => G_SQLERRM_TOKEN
1275 ,p_token2_value => SQLERRM);
1276 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1277 END get_case_owner;
1278
1279 ---------------------------------------------------------------------------
1280 -- PROCEDURE get_assets
1281 ---------------------------------------------------------------------------
1282 PROCEDURE get_assets(
1283 p_contract_id IN NUMBER,
1284 x_oiav_tbl OUT NOCOPY oiav_tbl_type,
1285 x_return_status OUT NOCOPY VARCHAR2) AS
1286
1287 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1288 l_oiav_rec oiav_rec_type;
1289 l_oiav_tbl oiav_tbl_type;
1290 i NUMBER := 0;
1291
1292 CURSOR l_top_line_csr(cp_contract_id IN NUMBER) IS
1293 SELECT ols.lty_code
1294 ,okl.id
1295 FROM okc_k_lines_v okl
1296 ,okc_line_styles_b ols
1297 WHERE okl.dnz_chr_id = cp_contract_id
1298 AND okl.lse_id = ols.id
1299 AND ols.lty_code = 'FREE_FORM1';
1300
1301 CURSOR l_model_csr(cp_top_line_id IN NUMBER) IS
1302 SELECT name
1303 ,description
1304 ,asset_id
1305 ,asset_number
1306 ,original_cost
1307 ,tag_number
1308 ,manufacturer_name
1309 ,model_number
1310 ,asset_type
1311 FROM okx_assets_v oka
1312 ,(select oki.object1_id1
1313 ,oki.object1_id2
1314 FROM okc_k_items_v oki
1315 ,(SELECT ols.lty_code, okl.id
1316 FROM okc_k_lines_v okl
1317 ,okc_line_styles_b ols
1318 WHERE okl.cle_id = cp_top_line_id
1319 AND okl.lse_id = ols.id
1320 AND ols.lty_code = 'FIXED_ASSET') fa
1321 WHERE oki.cle_id = fa.id) oi
1322 WHERE oka.id1 = TO_NUMBER(oi.object1_id1)
1323 AND oka.id2 = oi.object1_id2;
1324
1325 CURSOR l_install_base_csr(cp_top_line_id IN NUMBER) IS
1326 SELECT oii.instance_number
1327 ,oii.serial_number
1328 ,oii.quantity
1329 ,oii.install_location_id
1330 FROM okx_install_items_v oii
1331 ,(SELECT oki.object1_id1
1332 ,oki.object1_id2
1333 FROM okc_k_items_v oki
1334 ,(SELECT ols.lty_code
1335 ,okl.id
1336 FROM okc_k_lines_v okl
1337 ,okc_line_styles_b ols
1338 ,(SELECT ols.lty_code, okl.id
1339 FROM okc_k_lines_v okl
1340 ,okc_line_styles_b ols
1341 WHERE okl.cle_id = cp_top_line_id
1342 AND okl.lse_id = ols.id
1343 AND ols.lty_code = 'FREE_FORM2') ff2
1344 WHERE okl.cle_id = ff2.id
1345 AND okl.lse_id = ols.id
1346 AND ols.lty_code = 'INST_ITEM') ii
1347 WHERE oki.cle_id = ii.id) oi
1348 WHERE id1 = TO_NUMBER(oi.object1_id1)
1349 AND id2 = oi.object1_id2;
1350
1351 CURSOR l_location_csr(cp_location_id IN NUMBER) IS
1352 SELECT hl.country
1353 ,hl.address1
1354 ,hl.address2
1355 ,hl.address3
1356 ,hl.address4
1357 ,hl.city
1358 ,hl.postal_code
1359 ,hl.state
1360 ,hl.province
1361 ,hl.county
1362 ,hl.po_box_number
1363 ,hl.house_number
1364 ,hl.street_suffix
1365 ,hl.apartment_number
1366 ,hl.street
1367 ,hl.rural_route_number
1368 ,hl.street_number
1369 ,hl.building
1370 ,hl.floor
1371 ,hl.suite
1372 ,hl.room
1373 ,hl.postal_plus4_code
1374 FROM hz_locations hl
1375 ,(SELECT location_id
1376 FROM hz_party_sites
1377 WHERE party_site_id = cp_location_id) hps
1378 WHERE hl.location_id = hps.location_id;
1379 BEGIN
1380 x_oiav_tbl.delete;
1381 FOR cur_top_line_csr IN l_top_line_csr(p_contract_id) LOOP
1382 l_oiav_rec.khr_id := p_contract_id;
1383 FOR cur_model_csr IN l_model_csr(cur_top_line_csr.id) LOOP
1384 l_oiav_rec.asset_id := cur_model_csr.asset_id;
1385 l_oiav_rec.asset_number := cur_model_csr.asset_number;
1386 l_oiav_rec.description := cur_model_csr.description;
1387 l_oiav_rec.asset_type := cur_model_csr.asset_type;
1388 l_oiav_rec.manufacturer_name := cur_model_csr.manufacturer_name;
1389 l_oiav_rec.model_number := cur_model_csr.model_number;
1390 l_oiav_rec.tag_number := cur_model_csr.tag_number;
1391 l_oiav_rec.original_cost := cur_model_csr.original_cost;
1392 END LOOP;
1393
1394 FOR cur_install_base_csr IN l_install_base_csr(cur_top_line_csr.id) LOOP
1395 l_oiav_rec.instance_number := cur_install_base_csr.instance_number;
1396 l_oiav_rec.serial_number := cur_install_base_csr.serial_number;
1397 l_oiav_rec.quantity := cur_install_base_csr.quantity;
1398 FOR cur_location_csr in l_location_csr(cur_install_base_csr.install_location_id) LOOP
1399 l_oiav_rec.country := cur_location_csr.country;
1400 l_oiav_rec.address1 := cur_location_csr.address1;
1401 l_oiav_rec.address2 := cur_location_csr.address2;
1402 l_oiav_rec.address3 := cur_location_csr.address3;
1403 l_oiav_rec.address4 := cur_location_csr.address4;
1404 l_oiav_rec.city := cur_location_csr.city;
1405 l_oiav_rec.postal_code := cur_location_csr.postal_code;
1406 l_oiav_rec.state := cur_location_csr.state;
1407 l_oiav_rec.province := cur_location_csr.province;
1408 l_oiav_rec.county := cur_location_csr.county;
1409 l_oiav_rec.po_box_number := cur_location_csr.po_box_number;
1410 l_oiav_rec.house_number := cur_location_csr.house_number;
1411 l_oiav_rec.street_suffix := cur_location_csr.street_suffix;
1412 l_oiav_rec.apartment_number := cur_location_csr.apartment_number;
1413 l_oiav_rec.street := cur_location_csr.street;
1414 l_oiav_rec.rural_route_number := cur_location_csr.rural_route_number;
1415 l_oiav_rec.street_number := cur_location_csr.street_number;
1416 l_oiav_rec.building := cur_location_csr.building;
1417 l_oiav_rec.floor := cur_location_csr.floor;
1418 l_oiav_rec.suite := cur_location_csr.suite;
1419 l_oiav_rec.room := cur_location_csr.room;
1420 l_oiav_rec.postal_plus4_code := cur_location_csr.postal_plus4_code;
1421 END LOOP; --location loop
1422 i := i + 1;
1423 x_oiav_tbl(i) := l_oiav_rec;
1424 END LOOP; --install base loop
1425
1426 END LOOP; --top line loop
1427
1428 x_return_status := l_return_status;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1432 ,p_msg_name => G_UNEXPECTED_ERROR
1433 ,p_token1 => G_SQLCODE_TOKEN
1434 ,p_token1_value => SQLCODE
1435 ,p_token2 => G_SQLERRM_TOKEN
1436 ,p_token2_value => SQLERRM);
1437 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1438 END get_assets;
1439
1440
1441
1442
1443 ---------------------------------------------------
1444 ----------- API BODY-----------------------------
1445 ----------------------------------------------------
1446 ---- Party Merge
1447
1448 PROCEDURE OKL_OPEN_INT_PARTY_MERGE (
1449 p_entity_name IN VARCHAR2,
1450 p_from_id IN NUMBER,
1451 x_to_id OUT NOCOPY NUMBER,
1452 p_from_fk_id IN NUMBER,
1453 p_to_fk_id IN NUMBER,
1454 p_parent_entity_name IN VARCHAR2,
1455 p_batch_id IN NUMBER,
1456 p_batch_party_id IN NUMBER,
1457 x_return_status OUT NOCOPY VARCHAR2)
1458 IS
1459 --
1460 l_merge_reason_code VARCHAR2(30);
1461 l_api_name VARCHAR2(30) := 'OKL_OPEN_INT_PARTY_MERGE';
1462 l_count NUMBER(10) := 0;
1463 --
1464 BEGIN
1465 --
1466 fnd_file.put_line(fnd_file.log, 'OKL_OPEN_INT.OKL_OPEN_INT_PARTY_MERGE');
1467 --
1468 arp_message.set_line('OKL_OPEN_INT.OKL_OPEN_INT_PARTY_MERGE()+');
1469
1470 x_return_status := FND_API.G_RET_STS_SUCCESS;
1471
1472
1473 --
1474 select merge_reason_code
1475 into l_merge_reason_code
1476 from hz_merge_batch
1477 where batch_id = p_batch_id;
1478
1479 if l_merge_reason_code = 'DUPLICATE' then
1480 -- if reason code is duplicate then allow the party merge to happen without
1481 -- any validations.
1482 null;
1483 else
1484 -- if there are any validations to be done, include it in this section
1485 null;
1486 end if;
1487
1488 -- If the parent has not changed (ie. Parent getting transferred) then nothing
1489 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1490
1491 if p_from_fk_id = p_to_fk_id then
1492 x_to_id := p_from_id;
1493 return;
1494 end if;
1495
1496 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1497 -- dependent record to the new parent. Before transferring check if a similar
1498 -- dependent record exists on the new parent. If a duplicate exists then do
1499 -- not transfer and return the id of the duplicate record as the Merged To Id
1500
1501 if p_from_fk_id <> p_to_fk_id then
1502 begin
1503 arp_message.set_name('AR','AR_UPDATING_TABLE');
1504 arp_message.set_token('TABLE_NAME','OKL_OPEN_INT',FALSE);
1505 --
1506 --
1507 UPDATE OKL_OPEN_INT opi
1508 SET opi.party_ID = p_to_fk_id
1509 ,opi.object_version_number = opi.object_version_number + 1
1510 ,opi.last_update_date = SYSDATE
1511 ,opi.last_updated_by = arp_standard.profile.user_id
1512 ,opi.last_update_login = arp_standard.profile.last_update_login
1513 WHERE opi.party_ID = p_from_fk_id ;
1514
1515 l_count := sql%rowcount;
1516 arp_message.set_name('AR','AR_ROWS_UPDATED');
1517 arp_message.set_token('NUM_ROWS',to_char(l_count));
1518 --
1519 exception
1520 when others then
1521 arp_message.set_line(G_PKG_NAME || '.' || l_api_name || ': ' || sqlerrm);
1522 --
1523 fnd_file.put_line(fnd_file.log,(G_PKG_NAME || '.' || l_api_name ||
1524 'OKL_OPEN_INT for = '|| p_from_id));
1525 --
1526 fnd_file.put_line(fnd_file.log, G_PKG_NAME||'.'||l_api_name||':'||sqlerrm);
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 end;
1529 end if;
1530 END OKL_OPEN_INT_PARTY_MERGE ;
1531
1532 END OKL_OPI_PVT;