[Home] [Help]
PACKAGE BODY: APPS.OKL_CS_TRANSFER_ASSUMPTION_PVT
Source
1 PACKAGE BODY OKL_CS_TRANSFER_ASSUMPTION_PVT AS
2 /* $Header: OKLRTRAB.pls 120.13 2011/07/04 06:18:05 rkuttiya ship $ */
3
4
5 l_tcnv_rec tcnv_rec_type;
6 l_tclv_tbl tclv_tbl_type;
7 lx_tcnv_rec tcnv_rec_type;
8 lx_tclv_tbl tclv_tbl_type;
9 l_taav_tbl taav_tbl_type;
10
11
12
13 PROCEDURE Create_Requests(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
16 p_header_tbl IN tcnv_tbl_type,
17 p_add_hdr_tbl IN add_hdr_tbl_type,
18 p_old_line_tbl IN l_before_trf_tbl,
19 p_new_line_tbl IN l_after_trf_tbl,
20 x_header_tbl OUT NOCOPY OKL_TRX_CONTRACTS_PUB.tcnv_tbl_type,
21 x_taav_tbl OUT NOCOPY taav_tbl_type,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2)
25
26 AS
27 l_count NUMBER;
28 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_Request';
30 l_api_version CONSTANT NUMBER := 1;
31 j BINARY_INTEGER;
32
33
34 BEGIN
35
36 x_return_status := OKL_API.G_RET_STS_SUCCESS;
37
38 --Call start_activity to create savepoint, check compatibility and initialize message list
39
40 x_return_status := OKL_API.START_ACTIVITY(
41 l_api_name
42 ,p_init_msg_list
43 ,'_PUB'
44 ,x_return_status);
45
46 --Check if activity started successfully
47
48 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
49 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
50 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
51 RAISE OKL_API.G_EXCEPTION_ERROR;
52 END IF;
53 --populating the header record
54 l_tcnv_rec.khr_id := p_header_tbl(1).khr_id;
55 l_tcnv_rec.khr_id_old := p_header_tbl(1).khr_id_old;
56 l_tcnv_rec.party_rel_id1_old := p_header_tbl(1).party_rel_id1_old;
57 l_tcnv_rec.party_rel_id2_old := p_header_tbl(1).party_rel_id2_old;
58 l_tcnv_rec.party_rel_id1_new := p_header_tbl(1).party_rel_id1_new;
59 l_tcnv_rec.party_rel_id2_new := p_header_tbl(1).party_rel_id2_new;
60 l_tcnv_rec.complete_transfer_yn := p_header_tbl(1).complete_transfer_yn;
61 l_tcnv_rec.date_transaction_occurred := p_header_tbl(1).date_transaction_occurred;
62 l_tcnv_rec.try_id := p_header_tbl(1).try_id;
63 l_tcnv_rec.tsu_code := p_header_tbl(1).tsu_code;
64 l_tcnv_rec.description := p_header_tbl(1).description;
65 l_tcnv_rec.tcn_type := p_header_tbl(1).tcn_type;
66 l_tcnv_rec.created_by := p_header_tbl(1).created_by;
67 l_tcnv_rec.creation_date := p_header_tbl(1).creation_date;
68 l_tcnv_rec.last_updated_by := p_header_tbl(1).last_updated_by;
69 l_tcnv_rec.last_update_date := p_header_tbl(1).last_update_date;
70 l_tcnv_rec.last_update_login := p_header_tbl(1).last_update_login;
71 l_tcnv_rec.legal_entity_id := p_header_tbl(1).legal_entity_id; --dkagrawa added for LE uptake
72
73 -- skgautam for bug...
74 l_tcnv_rec.rjn_code := p_header_tbl(1).rjn_code;
75 l_tcnv_rec.khr_id_new := p_header_tbl(1).khr_id_new;
76 --
77
78
79
80 --Populating the lines
81
82 l_count := p_old_line_tbl.COUNT;
83
84
85
86 IF l_count > 0 THEN
87 FOR i IN 1..l_count LOOP
88 l_tclv_tbl(i).kle_id := p_old_line_tbl(i).id;
89 l_tclv_tbl(i).before_transfer_yn := p_old_line_tbl(i).line_type;
90 l_tclv_tbl(i).tcl_type := 'TAA';
91 l_tclv_tbl(i).line_number := i;
92 l_tclv_tbl(i).amount := 100;
93 l_tclv_tbl(i).khr_id := l_tcnv_rec.khr_id;
94 END LOOP;
95
96 j := l_tclv_tbl.LAST;
97 l_count := p_new_line_tbl.COUNT;
98
99 IF l_count > 0 THEN
100 FOR i IN 1..l_count LOOP
101 l_tclv_tbl(j + i).kle_id := p_new_line_tbl(i).id;
102 l_tclv_tbl(j + i).before_transfer_yn := p_new_line_tbl(i).line_type;
103 l_tclv_tbl(j + i).tcl_type := 'TAA';
104 -- l_tclv_tbl(j + i).sty_id := l_sty_id;
105 l_tclv_tbl(j + i).line_number := j + i;
106 l_tclv_tbl(j + i).amount := 100;
107 l_tclv_tbl(j + i).khr_id := l_tcnv_rec.khr_id;
108 l_tclv_tbl(j + i).source_column_1 := 'INSTALL_SITE_ID';
109 l_tclv_tbl(j + i).source_value_1 := P_new_line_tbl(i).install_loc_id;
110 l_tclv_tbl(j + i).source_column_2 := 'FA_LOC_ID';
111 l_tclv_tbl(j + i).source_value_2 := P_new_line_tbl(i).fa_loc_id;
112 l_tclv_tbl(j + i).source_column_3 := 'BILL_TO_SITE_ID';
113 l_tclv_tbl(j + i).source_value_3 := P_new_line_tbl(i).bill_to_site_id;
114 END LOOP;
115 END IF;
116 END IF;
117
118 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(p_api_version => p_api_version,
119 p_init_msg_list => p_init_msg_list,
120 x_return_status => x_return_status,
121 x_msg_count => x_msg_count,
122 x_msg_data => x_msg_data,
123 p_tcnv_rec => l_tcnv_rec,
124 p_tclv_tbl => l_tclv_tbl,
125 x_tcnv_rec => lx_tcnv_rec,
126 x_tclv_tbl => lx_tclv_tbl);
127
128 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
129 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
130 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
131 RAISE OKC_API.G_EXCEPTION_ERROR;
132 END IF;
133
134
135 --geting the output header record
136 x_header_tbl(1).trx_number := lx_tcnv_rec.trx_number;
137 x_header_tbl(1).id := lx_tcnv_rec.id;
138
139 --populating the additional Lessee information in the t a entity.
140 l_taav_tbl(1).tcn_id := x_header_tbl(1).id;
141 l_taav_tbl(1).new_contract_number := p_add_hdr_tbl(1).new_contract_number;
142 l_taav_tbl(1).bill_to_site_id := p_add_hdr_tbl(1).bill_to_site_id;
143 l_taav_tbl(1).cust_acct_id := p_add_hdr_tbl(1).cust_acct_id;
144 l_taav_tbl(1).bank_acct_id := p_add_hdr_tbl(1).bank_acct_id;
145 l_taav_tbl(1).invoice_format_id := p_add_hdr_tbl(1).invoice_format_id;
146 l_taav_tbl(1).payment_mthd_id := p_add_hdr_tbl(1).payment_mthd_id;
147 l_taav_tbl(1).mla_id := p_add_hdr_tbl(1).mla_id;
148 l_taav_tbl(1).credit_line_id := p_add_hdr_tbl(1).credit_line_id;
149 l_taav_tbl(1).insurance_yn := p_add_hdr_tbl(1).insurance_yn;
150 l_taav_tbl(1).lease_policy_yn := p_add_hdr_tbl(1).lease_policy_yn;
151
152 --inserting the lessee details into t a entity
153
154 OKL_TAA_PVT.insert_row( p_api_version => l_api_version ,
155 p_init_msg_list => 'F',
156 x_return_status => l_return_status,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data,
159 p_taav_tbl => l_taav_tbl,
160 x_taav_tbl => x_taav_tbl);
161
162 OKL_API.END_ACTIVITY (x_msg_count,
163 x_msg_data );
164
165
166 EXCEPTION
167 WHEN OKL_API.G_EXCEPTION_ERROR THEN
168 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
169 l_api_name,
170 G_PKG_NAME,
171 'OKL_API.G_RET_STS_ERROR',
172 x_msg_count,
173 x_msg_data,
174 '_PUB');
175 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
176 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
177 l_api_name,
178 G_PKG_NAME,
179 'OKL_API.G_RET_STS_UNEXP_ERROR',
180 x_msg_count,
181 x_msg_data,
182 '_PUB');
183 WHEN OTHERS THEN
184 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
185 l_api_name,
186 G_PKG_NAME,
187 'OTHERS',
188 x_msg_count,
189 x_msg_data,
190 '_PUB');
191
192 END Create_Requests;
193
194
195
196 PROCEDURE Accept_Requests(p_api_version IN NUMBER,
197 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
198 p_header_tbl IN tcnv_tbl_type,
199 p_upd_hdr_tbl IN upd_hdr_tbl_type,
200 p_old_line_tbl IN l_before_trf_tbl,
201 p_new_line_tbl IN l_after_trf_tbl,
202 x_header_tbl OUT NOCOPY Okl_Trx_Contracts_Pub.tcnv_tbl_type,
203 x_taaV_tbl OUT NOCOPY taav_tbl_type,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2)
207 AS
208
209 l_api_name varchar2(100) := 'Accept_Requests';
210 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
211 l_api_version CONSTANT NUMBER := 1;
212
213
214 l_tcnv_rec tcnv_rec_type;
215 l_tclv_tbl tclv_tbl_type;
216 lx_tcnv_rec tcnv_rec_type;
217 lx_tclv_tbl tclv_tbl_type;
218 l_count NUMBER;
219 j NUMBER;
220
221 BEGIN
222 x_return_status := OKL_API.G_RET_STS_SUCCESS;
223
224 --Call start_activity to create savepoint, check compatibility and initialize message list
225
226 x_return_status := OKL_API.START_ACTIVITY(
227 l_api_name
228 ,p_init_msg_list
229 ,'_PUB'
230 ,x_return_status);
231
232 --Check if activity started successfully
233
234 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
235 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
236 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
237 RAISE OKL_API.G_EXCEPTION_ERROR;
238 END IF;
239
240 --populating the header record
241 l_tcnv_rec.id := p_header_tbl(1).id;
242 l_tcnv_rec.khr_id := p_header_tbl(1).khr_id;
243 l_tcnv_rec.party_rel_id1_old := p_header_tbl(1).party_rel_id1_old;
244 l_tcnv_rec.party_rel_id1_new := p_header_tbl(1).party_rel_id1_new;
245 l_tcnv_rec.party_rel_id2_new := p_header_tbl(1).party_rel_id2_new;
246 l_tcnv_rec.complete_transfer_yn := p_header_tbl(1).complete_transfer_yn;
247 l_tcnv_rec.date_transaction_occurred := p_header_tbl(1).date_transaction_occurred;
248 l_tcnv_rec.tsu_code := p_header_tbl(1).tsu_code;
249 l_tcnv_rec.description := p_header_tbl(1).description;
250
251 l_tcnv_rec.last_updated_by := p_header_tbl(1).last_updated_by;
252 l_tcnv_rec.last_update_date := p_header_tbl(1).last_update_date;
253 l_tcnv_rec.last_update_login := p_header_tbl(1).last_update_login;
254 l_tcnv_rec.legal_entity_id := p_header_tbl(1).legal_entity_id; --dkagrawa added for LE uptake
255
256
257 --Populating the lines
258
259 l_count := p_old_line_tbl.COUNT;
260
261
262 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => p_api_version,
263 p_init_msg_list => fnd_api.g_false,
264 x_return_status => x_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data,
267 p_tcnv_rec => l_tcnv_rec,
268 p_tclv_tbl => l_tclv_tbl,
269 x_tcnv_rec => lx_tcnv_rec,
270 x_tclv_tbl => lx_tclv_tbl);
271
272 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
274 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
275 RAISE OKC_API.G_EXCEPTION_ERROR;
276 END IF;
277
278
279 --geting the output header record
280 x_header_tbl(1).trx_number := lx_tcnv_rec.trx_number;
281 x_header_tbl(1).id := lx_tcnv_rec.id;
282
283 --populating the additional Lessee information in the t a entity.
284 l_taav_tbl(1).id := p_upd_hdr_tbl(1).id;
285 l_taav_tbl(1).tcn_id := x_header_tbl(1).id;
286 l_taav_tbl(1).new_contract_number := p_upd_hdr_tbl(1).new_contract_number;
287 l_taav_tbl(1).bill_to_site_id := p_upd_hdr_tbl(1).bill_to_site_id;
288 l_taav_tbl(1).cust_acct_id := p_upd_hdr_tbl(1).cust_acct_id;
289 l_taav_tbl(1).bank_acct_id := p_upd_hdr_tbl(1).bank_acct_id;
290 l_taav_tbl(1).invoice_format_id := p_upd_hdr_tbl(1).invoice_format_id;
291 l_taav_tbl(1).payment_mthd_id := p_upd_hdr_tbl(1).payment_mthd_id;
292 l_taav_tbl(1).mla_id := p_upd_hdr_tbl(1).mla_id;
293 l_taav_tbl(1).credit_line_id := p_upd_hdr_tbl(1).credit_line_id;
294 l_taav_tbl(1).insurance_yn := p_upd_hdr_tbl(1).insurance_yn;
295 l_taav_tbl(1).lease_policy_yn := p_upd_hdr_tbl(1).lease_policy_yn;
296
297 --updating the lessee details into t a entity
298
299 OKL_TAA_PVT.update_row( p_api_version => l_api_version ,
300 p_init_msg_list => 'F',
301 x_return_status => l_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data,
304 p_taav_tbl => l_taav_tbl,
305 x_taav_tbl => x_taav_tbl);
306
307
308
309 OKL_API.END_ACTIVITY (x_msg_count,
310 x_msg_data );
311
312
313 EXCEPTION
314 WHEN OKL_API.G_EXCEPTION_ERROR THEN
315 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
316 l_api_name,
317 G_PKG_NAME,
318 'OKL_API.G_RET_STS_ERROR',
319 x_msg_count,
320 x_msg_data,
321 '_PUB');
322 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
323 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
324 l_api_name,
325 G_PKG_NAME,
326 'OKL_API.G_RET_STS_UNEXP_ERROR',
327 x_msg_count,
328 x_msg_data,
329 '_PUB');
330 WHEN OTHERS THEN
331 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
332 l_api_name,
333 G_PKG_NAME,
334 'OTHERS',
335 x_msg_count,
336 x_msg_data,
337 '_PUB');
338
339
340 END Accept_Requests;
341
342
343 PROCEDURE Update_Requests(p_api_version IN NUMBER,
344 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
345 p_header_tbl IN tcnv_tbl_type,
346 p_upd_hdr_tbl IN upd_hdr_tbl_type,
347 p_old_line_tbl IN l_before_trf_tbl,
348 p_new_line_tbl IN l_after_trf_tbl,
349 x_header_tbl OUT NOCOPY Okl_Trx_Contracts_Pub.tcnv_tbl_type,
350 x_taaV_tbl OUT NOCOPY taav_tbl_type,
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2)
354 AS
355
356 l_api_name varchar2(100) := 'Update_Requests';
357 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
358 l_api_version CONSTANT NUMBER := 1;
359
360 l_tcnv_rec tcnv_rec_type;
361 l_tclv_tbl tclv_tbl_type;
362 lx_tcnv_rec tcnv_rec_type;
363 lx_tclv_tbl tclv_tbl_type;
364 l_count NUMBER;
365 j NUMBER;
366 i NUMBER;
367
368 --rkuttiya bug 11784949
369 CURSOR c_get_line_id(p_kle_id IN NUMBER,
370 p_tcn_id IN NUMBER) IS
371 SELECT id
372 FROM okl_txl_cntrct_lns
373 WHERE kle_id = p_kle_id
374 AND tcn_id = p_tcn_id
375 AND BEFORE_TRANSFER_YN='N';
376
377 l_line_id NUMBER;
378 --
379
380
381 BEGIN
382 x_return_status := OKL_API.G_RET_STS_SUCCESS;
383
384 --Call start_activity to create savepoint, check compatibility and initialize message list
385
386 x_return_status := OKL_API.START_ACTIVITY(
387 l_api_name
388 ,p_init_msg_list
389 ,'_PUB'
390 ,x_return_status);
391
392 --Check if activity started successfully
393
394 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
395 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
396 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
397 RAISE OKL_API.G_EXCEPTION_ERROR;
398 END IF;
399
400 --populating the header record
401 l_tcnv_rec.id := p_header_tbl(1).id;
402 l_tcnv_rec.party_rel_id1_old := p_header_tbl(1).party_rel_id1_old;
403 l_tcnv_rec.party_rel_id1_new := p_header_tbl(1).party_rel_id1_new;
404 l_tcnv_rec.party_rel_id2_new := p_header_tbl(1).party_rel_id2_new;
405 l_tcnv_rec.date_transaction_occurred := p_header_tbl(1).date_transaction_occurred;
406 l_tcnv_rec.description := p_header_tbl(1).description;
407 l_tcnv_rec.tsu_code := p_header_tbl(1).tsu_code;
408
409 l_tcnv_rec.last_updated_by := p_header_tbl(1).last_updated_by;
410 l_tcnv_rec.last_update_date := p_header_tbl(1).last_update_date;
411 l_tcnv_rec.last_update_login := p_header_tbl(1).last_update_login;
412 l_tcnv_rec.legal_entity_id := p_header_tbl(1).legal_entity_id; --dkagrawa added for LE uptake
413
414
415 --rkuttiya added for bug #11784949
416 l_count := p_new_line_tbl.COUNT;
417
418
419 IF l_count > 0 THEN
420 FOR i IN 1..l_count LOOP
421
422 OPEN c_get_line_id(p_new_line_tbl(i).id,p_header_tbl(1).id);
423 FETCH c_get_line_id INTO l_line_id;
424 CLOSE c_get_line_id;
425 l_tclv_tbl(i).id := l_line_id;
426 l_tclv_tbl(i).kle_id := p_new_line_tbl(i).id;
427 l_tclv_tbl(i).source_column_1 := 'INSTALL_SITE_ID';
428 l_tclv_tbl(i).source_value_1 := p_new_line_tbl(i).install_loc_id;
429
430
431 l_tclv_tbl(i).source_column_2 := 'FA_LOC_ID';
432 l_tclv_tbl(i).source_value_2 := P_new_line_tbl(i).fa_loc_id;
433
434
435
436 l_tclv_tbl(i).source_column_3 := 'BILL_TO_SITE_ID';
437 l_tclv_tbl(i).source_value_3 := p_new_line_tbl(i).bill_to_site_id;
438
439 END LOOP;
440 END IF;
441
442
443
444 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => p_api_version,
445 p_init_msg_list => fnd_api.g_false,
446 x_return_status => x_return_status,
447 x_msg_count => x_msg_count,
448 x_msg_data => x_msg_data,
449 p_tcnv_rec => l_tcnv_rec,
450 p_tclv_tbl => l_tclv_tbl,
451 x_tcnv_rec => lx_tcnv_rec,
452 x_tclv_tbl => lx_tclv_tbl);
453
454
455 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
456 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
457 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
458 RAISE OKC_API.G_EXCEPTION_ERROR;
459 END IF;
460
461
462
463 --geting the output header record
464 x_header_tbl(1).trx_number := lx_tcnv_rec.trx_number;
465 x_header_tbl(1).id := lx_tcnv_rec.id;
466
467 --populating the additional Lessee information in the t a entity.
468 l_taav_tbl(1).id := p_upd_hdr_tbl(1).id;
469 l_taav_tbl(1).tcn_id := x_header_tbl(1).id;
470 l_taav_tbl(1).new_contract_number := p_upd_hdr_tbl(1).new_contract_number;
471 l_taav_tbl(1).bill_to_site_id := p_upd_hdr_tbl(1).bill_to_site_id;
472 l_taav_tbl(1).cust_acct_id := p_upd_hdr_tbl(1).cust_acct_id;
473 l_taav_tbl(1).bank_acct_id := p_upd_hdr_tbl(1).bank_acct_id;
474 l_taav_tbl(1).invoice_format_id := p_upd_hdr_tbl(1).invoice_format_id;
475 l_taav_tbl(1).payment_mthd_id := p_upd_hdr_tbl(1).payment_mthd_id;
476 l_taav_tbl(1).mla_id := p_upd_hdr_tbl(1).mla_id;
477 l_taav_tbl(1).credit_line_id := p_upd_hdr_tbl(1).credit_line_id;
478 l_taav_tbl(1).insurance_yn := p_upd_hdr_tbl(1).insurance_yn;
479 l_taav_tbl(1).lease_policy_yn := p_upd_hdr_tbl(1).lease_policy_yn;
480
481 --updating the lessee details into t a entity
482
483 OKL_TAA_PVT.update_row( p_api_version => l_api_version ,
484 p_init_msg_list => 'F',
485 x_return_status => l_return_status,
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_taav_tbl => l_taav_tbl,
489 x_taav_tbl => x_taav_tbl);
490
491 commit;
492
493 OKL_API.END_ACTIVITY (x_msg_count,
494 x_msg_data );
495
496
497 EXCEPTION
498 WHEN OKL_API.G_EXCEPTION_ERROR THEN
499 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
500 l_api_name,
501 G_PKG_NAME,
502 'OKL_API.G_RET_STS_ERROR',
503 x_msg_count,
504 x_msg_data,
505 '_PUB');
506 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
507 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
508 l_api_name,
509 G_PKG_NAME,
510 'OKL_API.G_RET_STS_UNEXP_ERROR',
511 x_msg_count,
512 x_msg_data,
513 '_PUB');
514 WHEN OTHERS THEN
515 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
516 l_api_name,
517 G_PKG_NAME,
518 'OTHERS',
519 x_msg_count,
520 x_msg_data,
521 '_PUB');
522
523
524 END Update_Requests;
525
526 PROCEDURE Populate_new_Lessee_details( p_api_version IN NUMBER,
527 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
528 p_request_id IN NUMBER,
529 x_new_lessee_tbl OUT NOCOPY new_lessee_tbl_type,
530 x_return_status OUT NOCOPY VARCHAR2,
531 x_msg_count OUT NOCOPY NUMBER,
532 x_msg_data OUT NOCOPY VARCHAR2)
533 IS
534
535 --Obtain the request record details
536 CURSOR c_request_record(p_request_id IN NUMBER) IS
537 SELECT *
538 FROM OKL_TRX_CONTRACTS
539 WHERE id = p_request_id;
540
541 --Obtain the new lessee name
542 CURSOR c_lessee(p_party_id IN NUMBER) IS
543 SELECT party_id,
544 PARTY_NAME
545 FROM HZ_PARTIES PARTY
546 WHERE PARTY_ID = p_party_id;
547
548 CURSOR c_taa_record(p_tcn_id IN NUMBER) IS
549 SELECT *
550 FROM okl_taa_request_details_v
551 WHERE tcn_id = p_tcn_id;
552
553 CURSOR c_bill_to(p_site_id IN NUMBER) IS
554 SELECT id1,
555 description
556 FROM okx_cust_site_uses_v
557 WHERE ID1 = p_site_id;
558
559 CURSOR c_bank_account(p_bank_acct_id IN NUMBER) IS
560 SELECT id1,
561 -- Bug 9502479
562 -- bank_account_num
563 description
564 -- End Bug 9502479
565 FROM OKX_RCPT_METHOD_ACCOUNTS_V
566 WHERE ID1 = p_bank_acct_id;
567
568
569 -- Populating the customer account
570 CURSOR c_cust_account(p_cust_acct_id IN NUMBER) IS
571 SELECT cust_account_id,
572 account_number
573 FROM hz_cust_accounts
574 WHERE cust_account_id = p_cust_acct_id;
575
576 -- populating the invoice format
577 CURSOR c_invoice_format(p_inv_format_id IN NUMBER) IS
578 SELECT id,
579 name
580 FROM OKL_INVOICE_FORMATS_V
581 WHERE ID = p_inv_format_id;
582
583 -- populating the payment method
584 CURSOR c_payment_mthd(p_pay_mthd_id IN NUMBER) IS
585 SELECT id1,
586 name
587 FROM OKX_RECEIPT_METHODS_V
588 WHERE id1 = p_pay_mthd_id;
589
590 -- populating the new contract number
591 CURSOR c_new_ctr_no(p_request_id IN NUMBER) IS
592 SELECT new_contract_number
593 FROM okl_taa_request_details_b
594 WHERE tcn_id = p_request_id;
595
596 -- populating the master lease agreement and credit line no
597 CURSOR c_mla_no(p_contract_id IN NUMBER) IS
598 SELECT id,
599 contract_number
600 FROM OKC_K_HEADERS_B
601 WHERE ID = p_contract_id;
602
603
604
605 CURSOR c_contact(p_cust_account_id IN NUMBER,
606 p_party_id IN NUMBER) IS
607 SELECT hzp.party_id,
608 hzp.party_name contact_name,
609 hzp.email_address email
610 FROM hz_parties hzp,
611 hz_cust_account_roles hzc
612 WHERE hzc.cust_account_id = p_cust_account_id
613 AND hzc.status = 'A'
614 AND hzc.role_type = 'CONTACT'
615 AND hzc.party_id = hzp.party_id
616 and hzp.party_id = p_party_id ;
617
618
619 --rkraya added for bug:2451527
620 CURSOR c_phone(p_party_id IN NUMBER) IS
621 SELECT decode(nvl(phone_country_code,''),(phone_country_code || '-'),'') || phone_area_code || phone_number phone
622 FROM hz_contact_points
623 WHERE owner_table_id = p_party_id
624 AND contact_point_type = 'PHONE';
625
626
627 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
628 l_api_name CONSTANT VARCHAR2(30) := 'Populate';
629 l_api_version CONSTANT NUMBER := 1;
630 l_id1 VARCHAR2(40);
631 l_id2 VARCHAR2(200);
632 l_lessee_name VARCHAR2(360);
633 l_party_id NUMBER;
634 l_contact_email VARCHAR2(2000);
635 l_billto_address VARCHAR2(4000);
636 l_billto_id NUMBER;
637 l_contact_name VARCHAR2(300);
638 l_contact_party_id NUMBER;
639 l_phone VARCHAR2(30);
640 l_request_record OKL_TRX_CONTRACTS%ROWTYPE;
641 l_taa_record OKL_TAA_REQUEST_DETAILS_B%rowtype;
642 l_ctr_no VARCHAR2(120);
643 l_acct_no VARCHAR2(30);
644 l_acct_id NUMBER;
645 l_location VARCHAR2(4000);
646 l_bank VARCHAR2(100);
647 l_bank_acct_id NUMBER;
648 l_pay_mthd VARCHAR2(30);
649 l_pay_mthd_id NUMBER;
650 l_inv_fmt VARCHAR2(450);
651 l_inv_fmt_id NUMBER;
652 l_master_lease VARCHAR2(120);
653 l_mla_id NUMBER;
654 l_credit_line VARCHAR2(120);
655 l_crd_id NUMBER;
656
657
658 BEGIN
659 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
660 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details','Begin(+)');
661 END IF;
662 --Print Input Variables
663 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
665 'p_api_version :'||p_api_version);
666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
667 'p_init_msg_list :'||p_init_msg_list);
668 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
669 'p_request_id :'||p_request_id);
670 END IF;
671
672 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
673 G_PKG_NAME,
674 p_init_msg_list,
675 l_api_version,
676 p_api_version,
677 '_PVT',
678 x_return_status);
679
680
681
682 --Fetch the request details
683 OPEN c_request_record(p_requesT_id);
684 FETCH c_request_record INTO l_request_record;
685 CLOSE c_request_record;
686
687
688
689 --Fetch the new Lessee Details
690 OPEN c_lessee(l_request_record.party_rel_id2_new);
691 FETCH c_lessee INTO l_party_id,l_lessee_name;
692 CLOSE c_lessee;
693
694
695 OPEN c_taa_record(p_request_id);
696 FETCH c_taa_record INTO l_taa_record;
697 CLOSE c_taa_record;
698
699 --populating the bill to address
700 OPEN c_bill_to(l_taa_record.bill_to_site_id);
701 FETCH c_bill_to INTO l_billto_id,l_billto_address;
702 CLOSE c_bill_to;
703
704
705 -- populating the bank account
706 OPEN c_bank_account(l_taa_record.bank_acct_id);
707 FETCH c_bank_account INTO l_bank_acct_id,l_bank;
708 CLOSE c_bank_account;
709
710
711 -- Populating the customer account
712 OPEN c_cust_account(l_taa_record.cust_acct_id);
713 FETCH c_cust_account INTO l_acct_id,l_acct_no;
714 CLOSE c_cust_account;
715
716 -- populating the invoice format
717 OPEN c_invoice_format(l_taa_record.invoice_format_id) ;
718 FETCH c_invoice_format INTO l_inv_fmt_id,l_inv_fmt;
719 CLOSE c_invoice_format;
720
721 -- populating the payment method
722 OPEN c_payment_mthd(l_taa_record.payment_mthd_id);
723 FETCH c_payment_mthd INTO l_pay_mthd_id,l_pay_mthd;
724 CLOSE c_payment_mthd;
725
726 -- populating the new contract number
727 OPEN c_new_ctr_no(p_request_id) ;
728 FETCH c_new_ctr_no INTO l_ctr_no;
729 CLOSE c_new_ctr_no;
730
731 -- populating the master lease agreement
732 OPEN c_mla_no(l_taa_record.mla_id);
733 FETCH c_mla_no INTO l_mla_id,l_master_lease;
734 CLOSE c_mla_no;
735
736 -- populating the credit line number
737 OPEN c_mla_no(l_taa_record.credit_line_id);
738 FETCH c_mla_no INTO l_crd_id,l_credit_line;
739 CLOSE c_mla_no;
740
741 --Fetching the contact details for the new lessee
742
743
744
745 OPEN c_contact(l_taa_record.cust_acct_id,l_request_record.party_rel_id1_new);
746 FETCH c_contact INTO l_contact_party_id,l_contact_name,l_contact_email;
747 CLOSE c_contact;
748
749
750
751 OPEN c_phone(l_contact_party_id);
752 FETCH c_phone INTO l_phone;
753 CLOSE c_phone;
754
755
756 --Populating the output variables
757 x_new_lessee_tbl(1).taa_id := l_taa_record.id;
758 x_new_lessee_tbl(1).new_contract_number := l_taa_record.new_contract_number;
759 x_new_lessee_tbl(1).new_lessee := l_lessee_name;
760 x_new_lessee_tbl(1).new_party_id := l_party_id;
761 x_new_lessee_tbl(1).contact_name := l_contact_name ;
762 x_new_lessee_tbl(1).contact_id := l_contact_party_id;
763 x_new_lessee_tbl(1).contact_email := l_contact_email;
764 x_new_lessee_tbl(1).contact_phone := l_phone;
765 x_new_lessee_tbl(1).bill_to_address := l_billto_address;
766 x_new_lessee_tbl(1).bill_to_id := l_billto_id;
767 x_new_lessee_tbl(1).cust_acct_number := l_acct_no;
768 x_new_lessee_tbl(1).cust_acct_id := l_acct_id;
769 x_new_lessee_tbl(1).bank_account := l_bank;
770 x_new_lessee_tbl(1).bank_acct_id := l_bank_acct_id;
771 x_new_lessee_tbl(1).invoice_format := l_inv_fmt;
772 x_new_lessee_tbl(1).inv_fmt_id := l_inv_fmt_id;
773 x_new_lessee_tbl(1).payment_method := l_pay_mthd;
774 x_new_lessee_tbl(1).pay_mthd_id := l_pay_mthd_id;
775 x_new_lessee_tbl(1).master_lease := l_master_lease;
776 x_new_lessee_tbl(1).mla_id := l_mla_id;
777 x_new_lessee_tbl(1).credit_line_no := l_credit_line;
778 x_new_lessee_tbl(1).credit_line_id := l_crd_id;
779 x_new_lessee_tbl(1).insurance_yn := l_taa_record.insurance_yn;
780 x_new_lessee_tbl(1).lease_policy_yn := l_taa_record.lease_policy_yn;
781
782 x_return_status := l_return_status;
783
784 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
785 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
786 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Create_Tax_Schedule ','End(-)');
787 END IF;
788
789 EXCEPTION
790 WHEN OKL_API.G_EXCEPTION_ERROR THEN
791 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
792 l_api_name,
793 G_PKG_NAME,
794 'OKL_API.G_RET_STS_ERROR',
795 x_msg_count,
796 x_msg_data,
797 '_PUB');
798 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
799 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
800 l_api_name,
801 G_PKG_NAME,
802 'OKL_API.G_RET_STS_UNEXP_ERROR',
803 x_msg_count,
804 x_msg_data,
805 '_PUB');
806 WHEN OTHERS THEN
807 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
808 l_api_name,
809 G_PKG_NAME,
810 'OTHERS',
811 x_msg_count,
812 x_msg_data,
813 '_PUB');
814 END Populate_new_Lessee_details;
815
816 PROCEDURE Populate_ThirdParty_Insurance( p_api_version IN NUMBER,
817 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
818 p_taa_id IN NUMBER,
819 x_insurance_tbl OUT NOCOPY insurance_tbl_type,
820 x_return_status OUT NOCOPY VARCHAR2,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2)
823 IS
824 --Obtain the insuance details of the taa request.
825 -- changed tcn_id to id for bug:4094898
826 CURSOR c_taa_record(p_taa_id IN NUMBER) IS
827 SELECT *
828 FROM okl_taa_request_details_v
829 WHERE id = p_taa_id;
830
831 --Obtain the insurer/insurance_agent name
832 CURSOR c_lessee(p_party_id IN NUMBER) IS
833 SELECT PARTY_NAME
834 FROM HZ_PARTIES PARTY
835 WHERE PARTY_ID = p_party_id;
836
837
838 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
839 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Insurance_Details';
840 l_api_version CONSTANT NUMBER := 1;
841
842 l_insurer VARCHAR2(360);
843 l_insurance_agent VARCHAR2(360);
844 l_taa_record OKL_TAA_REQUEST_DETAILS_B%rowtype;
845
846 BEGIN
847 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details','Begin(+)');
849 END IF;
850 --Print Input Variables
851 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
852 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
853 'p_api_version :'||p_api_version);
854 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
855 'p_init_msg_list :'||p_init_msg_list);
856 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
857 'p_taa_id :'||p_taa_id);
858 END IF;
859
860 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
861 G_PKG_NAME,
862 p_init_msg_list,
863 l_api_version,
864 p_api_version,
865 '_PVT',
866 x_return_status);
867
868 --Fetch the insuance details tied to the ta request
869
870 OPEN c_taa_record(p_taa_id);
871 FETCH c_taa_record INTO l_taa_record;
872 CLOSE c_taa_record;
873
874 --Fetch the Insurer Name
875 OPEN c_lessee(l_taa_record.isu_id);
876 FETCH c_lessee INTO l_insurer;
877 CLOSE c_lessee;
878
879 --Fetch the Insurer Agent name
880 OPEN c_lessee(l_taa_record.int_id);
881 FETCH c_lessee INTO l_insurance_agent;
882 CLOSE c_lessee;
883
884 x_insurance_tbl(1).insurer := l_insurer;
885 x_insurance_tbl(1).insurance_agent := l_insurance_agent;
886 x_insurance_tbl(1).policy_number := l_taa_record.policy_number;
887 x_insurance_tbl(1).covered_amount := l_taa_record.covered_amt;
888 x_insurance_tbl(1).deductible_amount := l_taa_record.deductible_amt;
889 x_insurance_tbl(1).effective_from := l_taa_record.effective_from_date;
890 x_insurance_tbl(1).effective_to := l_taa_record.effective_to_date;
891 x_insurance_tbl(1).proof_provided := l_taa_record.proof_provided_date;
892 x_insurance_tbl(1).proof_required := l_taa_record.proof_required_date;
893 x_insurance_tbl(1).lessor_insured_yn := l_taa_record.lessor_insured_yn;
894 x_insurance_tbl(1).lessor_payee_yn := l_taa_record.lessor_payee_yn;
895
896 x_return_status := l_return_status;
897 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
898 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
899 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.populate_thirdparty_insurance','End(-)');
900 END IF;
901
902 EXCEPTION
903 WHEN OKL_API.G_EXCEPTION_ERROR THEN
904 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
905 l_api_name,
906 G_PKG_NAME,
907 'OKL_API.G_RET_STS_ERROR',
908 x_msg_count,
909 x_msg_data,
910 '_PUB');
911 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
912 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
913 l_api_name,
914 G_PKG_NAME,
915 'OKL_API.G_RET_STS_UNEXP_ERROR',
916 x_msg_count,
917 x_msg_data,
918 '_PUB');
919 WHEN OTHERS THEN
920 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
921 l_api_name,
922 G_PKG_NAME,
923 'OTHERS',
924 x_msg_count,
925 x_msg_data,
926 '_PUB');
927 END Populate_thirdparty_insurance;
928
929
930
931 END OKL_CS_TRANSFER_ASSUMPTION_PVT;
932
933