[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.9 2006/11/16 07:08:55 dkagrawa noship $ */
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
367 BEGIN
368 x_return_status := OKL_API.G_RET_STS_SUCCESS;
369
370 --Call start_activity to create savepoint, check compatibility and initialize message list
371
372 x_return_status := OKL_API.START_ACTIVITY(
373 l_api_name
374 ,p_init_msg_list
375 ,'_PUB'
376 ,x_return_status);
377
378 --Check if activity started successfully
379
380 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
382 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
383 RAISE OKL_API.G_EXCEPTION_ERROR;
384 END IF;
385
386 --populating the header record
387 l_tcnv_rec.id := p_header_tbl(1).id;
388 l_tcnv_rec.party_rel_id1_old := p_header_tbl(1).party_rel_id1_old;
389 l_tcnv_rec.party_rel_id1_new := p_header_tbl(1).party_rel_id1_new;
390 l_tcnv_rec.party_rel_id2_new := p_header_tbl(1).party_rel_id2_new;
391 l_tcnv_rec.date_transaction_occurred := p_header_tbl(1).date_transaction_occurred;
392 l_tcnv_rec.description := p_header_tbl(1).description;
393 l_tcnv_rec.tsu_code := p_header_tbl(1).tsu_code;
394
395 l_tcnv_rec.last_updated_by := p_header_tbl(1).last_updated_by;
396 l_tcnv_rec.last_update_date := p_header_tbl(1).last_update_date;
397 l_tcnv_rec.last_update_login := p_header_tbl(1).last_update_login;
398 l_tcnv_rec.legal_entity_id := p_header_tbl(1).legal_entity_id; --dkagrawa added for LE uptake
399
400
401
402 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => p_api_version,
403 p_init_msg_list => fnd_api.g_false,
404 x_return_status => x_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data,
407 p_tcnv_rec => l_tcnv_rec,
408 p_tclv_tbl => l_tclv_tbl,
409 x_tcnv_rec => lx_tcnv_rec,
410 x_tclv_tbl => lx_tclv_tbl);
411
412
413 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
415 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
416 RAISE OKC_API.G_EXCEPTION_ERROR;
417 END IF;
418
419
420 --geting the output header record
421 x_header_tbl(1).trx_number := lx_tcnv_rec.trx_number;
422 x_header_tbl(1).id := lx_tcnv_rec.id;
423
424 --populating the additional Lessee information in the t a entity.
425 l_taav_tbl(1).id := p_upd_hdr_tbl(1).id;
426 l_taav_tbl(1).tcn_id := x_header_tbl(1).id;
427 l_taav_tbl(1).new_contract_number := p_upd_hdr_tbl(1).new_contract_number;
428 l_taav_tbl(1).bill_to_site_id := p_upd_hdr_tbl(1).bill_to_site_id;
429 l_taav_tbl(1).cust_acct_id := p_upd_hdr_tbl(1).cust_acct_id;
430 l_taav_tbl(1).bank_acct_id := p_upd_hdr_tbl(1).bank_acct_id;
431 l_taav_tbl(1).invoice_format_id := p_upd_hdr_tbl(1).invoice_format_id;
432 l_taav_tbl(1).payment_mthd_id := p_upd_hdr_tbl(1).payment_mthd_id;
433 l_taav_tbl(1).mla_id := p_upd_hdr_tbl(1).mla_id;
434 l_taav_tbl(1).credit_line_id := p_upd_hdr_tbl(1).credit_line_id;
435 l_taav_tbl(1).insurance_yn := p_upd_hdr_tbl(1).insurance_yn;
436 l_taav_tbl(1).lease_policy_yn := p_upd_hdr_tbl(1).lease_policy_yn;
437
438 --updating the lessee details into t a entity
439
440 OKL_TAA_PVT.update_row( p_api_version => l_api_version ,
441 p_init_msg_list => 'F',
442 x_return_status => l_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 p_taav_tbl => l_taav_tbl,
446 x_taav_tbl => x_taav_tbl);
447
448
449
450 OKL_API.END_ACTIVITY (x_msg_count,
451 x_msg_data );
452
453
454 EXCEPTION
455 WHEN OKL_API.G_EXCEPTION_ERROR THEN
456 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
457 l_api_name,
458 G_PKG_NAME,
459 'OKL_API.G_RET_STS_ERROR',
460 x_msg_count,
461 x_msg_data,
462 '_PUB');
463 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
464 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
465 l_api_name,
466 G_PKG_NAME,
467 'OKL_API.G_RET_STS_UNEXP_ERROR',
468 x_msg_count,
469 x_msg_data,
470 '_PUB');
471 WHEN OTHERS THEN
472 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
473 l_api_name,
474 G_PKG_NAME,
475 'OTHERS',
476 x_msg_count,
477 x_msg_data,
478 '_PUB');
479
480
481 END Update_Requests;
482
483 PROCEDURE Populate_new_Lessee_details( p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
485 p_request_id IN NUMBER,
486 x_new_lessee_tbl OUT NOCOPY new_lessee_tbl_type,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2)
490 IS
491
492 --Obtain the request record details
493 CURSOR c_request_record(p_request_id IN NUMBER) IS
494 SELECT *
495 FROM OKL_TRX_CONTRACTS
496 WHERE id = p_request_id;
497
498 --Obtain the new lessee name
499 CURSOR c_lessee(p_party_id IN NUMBER) IS
500 SELECT party_id,
501 PARTY_NAME
502 FROM HZ_PARTIES PARTY
503 WHERE PARTY_ID = p_party_id;
504
505 CURSOR c_taa_record(p_tcn_id IN NUMBER) IS
506 SELECT *
507 FROM okl_taa_request_details_v
508 WHERE tcn_id = p_tcn_id;
509
510 CURSOR c_bill_to(p_site_id IN NUMBER) IS
511 SELECT id1,
512 description
513 FROM okx_cust_site_uses_v
514 WHERE ID1 = p_site_id;
515
516 CURSOR c_bank_account(p_bank_acct_id IN NUMBER) IS
517 SELECT id1,
518 bank_account_num
519 FROM OKX_RCPT_METHOD_ACCOUNTS_V
520 WHERE ID1 = p_bank_acct_id;
521
522
523 -- Populating the customer account
524 CURSOR c_cust_account(p_cust_acct_id IN NUMBER) IS
525 SELECT cust_account_id,
526 account_number
527 FROM hz_cust_accounts
528 WHERE cust_account_id = p_cust_acct_id;
529
530 -- populating the invoice format
531 CURSOR c_invoice_format(p_inv_format_id IN NUMBER) IS
532 SELECT id,
533 name
534 FROM OKL_INVOICE_FORMATS_V
535 WHERE ID = p_inv_format_id;
536
537 -- populating the payment method
538 CURSOR c_payment_mthd(p_pay_mthd_id IN NUMBER) IS
539 SELECT id1,
540 name
541 FROM OKX_RECEIPT_METHODS_V
542 WHERE id1 = p_pay_mthd_id;
543
544 -- populating the new contract number
545 CURSOR c_new_ctr_no(p_request_id IN NUMBER) IS
546 SELECT new_contract_number
547 FROM okl_taa_request_details_b
548 WHERE tcn_id = p_request_id;
549
550 -- populating the master lease agreement and credit line no
551 CURSOR c_mla_no(p_contract_id IN NUMBER) IS
552 SELECT id,
553 contract_number
554 FROM OKC_K_HEADERS_B
555 WHERE ID = p_contract_id;
556
557
558
559 CURSOR c_contact(p_cust_account_id IN NUMBER,
560 p_party_id IN NUMBER) IS
561 SELECT hzp.party_id,
562 hzp.party_name contact_name,
563 hzp.email_address email
564 FROM hz_parties hzp,
565 hz_cust_account_roles hzc
566 WHERE hzc.cust_account_id = p_cust_account_id
567 AND hzc.status = 'A'
568 AND hzc.role_type = 'CONTACT'
569 AND hzc.party_id = hzp.party_id
570 and hzp.party_id = p_party_id ;
571
572
573 --rkraya added for bug:2451527
574 CURSOR c_phone(p_party_id IN NUMBER) IS
575 SELECT decode(nvl(phone_country_code,''),(phone_country_code || '-'),'') || phone_area_code || phone_number phone
576 FROM hz_contact_points
577 WHERE owner_table_id = p_party_id
578 AND contact_point_type = 'PHONE';
579
580
581 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
582 l_api_name CONSTANT VARCHAR2(30) := 'Populate';
583 l_api_version CONSTANT NUMBER := 1;
584 l_id1 VARCHAR2(40);
585 l_id2 VARCHAR2(200);
586 l_lessee_name VARCHAR2(360);
587 l_party_id NUMBER;
588 l_contact_email VARCHAR2(2000);
589 l_billto_address VARCHAR2(4000);
590 l_billto_id NUMBER;
591 l_contact_name VARCHAR2(300);
592 l_contact_party_id NUMBER;
593 l_phone VARCHAR2(30);
594 l_request_record OKL_TRX_CONTRACTS%ROWTYPE;
595 l_taa_record OKL_TAA_REQUEST_DETAILS_B%rowtype;
596 l_ctr_no VARCHAR2(120);
597 l_acct_no VARCHAR2(30);
598 l_acct_id NUMBER;
599 l_location VARCHAR2(4000);
600 l_bank VARCHAR2(30);
601 l_bank_acct_id NUMBER;
602 l_pay_mthd VARCHAR2(30);
603 l_pay_mthd_id NUMBER;
604 l_inv_fmt VARCHAR2(450);
605 l_inv_fmt_id NUMBER;
606 l_master_lease VARCHAR2(120);
607 l_mla_id NUMBER;
608 l_credit_line VARCHAR2(120);
609 l_crd_id NUMBER;
610
611
612 BEGIN
613 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details','Begin(+)');
615 END IF;
616 --Print Input Variables
617 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
618 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
619 'p_api_version :'||p_api_version);
620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
621 'p_init_msg_list :'||p_init_msg_list);
622 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details',
623 'p_request_id :'||p_request_id);
624 END IF;
625
626 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
627 G_PKG_NAME,
628 p_init_msg_list,
629 l_api_version,
630 p_api_version,
631 '_PVT',
632 x_return_status);
633
634
635
636 --Fetch the request details
637 OPEN c_request_record(p_requesT_id);
638 FETCH c_request_record INTO l_request_record;
639 CLOSE c_request_record;
640
641
642
643 --Fetch the new Lessee Details
644 OPEN c_lessee(l_request_record.party_rel_id2_new);
645 FETCH c_lessee INTO l_party_id,l_lessee_name;
646 CLOSE c_lessee;
647
648
649 OPEN c_taa_record(p_request_id);
650 FETCH c_taa_record INTO l_taa_record;
651 CLOSE c_taa_record;
652
653 --populating the bill to address
654 OPEN c_bill_to(l_taa_record.bill_to_site_id);
655 FETCH c_bill_to INTO l_billto_id,l_billto_address;
656 CLOSE c_bill_to;
657
658
659 -- populating the bank account
660 OPEN c_bank_account(l_taa_record.bank_acct_id);
661 FETCH c_bank_account INTO l_bank_acct_id,l_bank;
662 CLOSE c_bank_account;
663
664
665 -- Populating the customer account
666 OPEN c_cust_account(l_taa_record.cust_acct_id);
667 FETCH c_cust_account INTO l_acct_id,l_acct_no;
668 CLOSE c_cust_account;
669
670 -- populating the invoice format
671 OPEN c_invoice_format(l_taa_record.invoice_format_id) ;
672 FETCH c_invoice_format INTO l_inv_fmt_id,l_inv_fmt;
673 CLOSE c_invoice_format;
674
675 -- populating the payment method
676 OPEN c_payment_mthd(l_taa_record.payment_mthd_id);
677 FETCH c_payment_mthd INTO l_pay_mthd_id,l_pay_mthd;
678 CLOSE c_payment_mthd;
679
680 -- populating the new contract number
681 OPEN c_new_ctr_no(p_request_id) ;
682 FETCH c_new_ctr_no INTO l_ctr_no;
683 CLOSE c_new_ctr_no;
684
685 -- populating the master lease agreement
686 OPEN c_mla_no(l_taa_record.mla_id);
687 FETCH c_mla_no INTO l_mla_id,l_master_lease;
688 CLOSE c_mla_no;
689
690 -- populating the credit line number
691 OPEN c_mla_no(l_taa_record.credit_line_id);
692 FETCH c_mla_no INTO l_crd_id,l_credit_line;
693 CLOSE c_mla_no;
694
695 --Fetching the contact details for the new lessee
696
697
698
699 OPEN c_contact(l_taa_record.cust_acct_id,l_request_record.party_rel_id1_new);
700 FETCH c_contact INTO l_contact_party_id,l_contact_name,l_contact_email;
701 CLOSE c_contact;
702
703
704
705 OPEN c_phone(l_contact_party_id);
706 FETCH c_phone INTO l_phone;
707 CLOSE c_phone;
708
709
710 --Populating the output variables
711 x_new_lessee_tbl(1).taa_id := l_taa_record.id;
712 x_new_lessee_tbl(1).new_contract_number := l_taa_record.new_contract_number;
713 x_new_lessee_tbl(1).new_lessee := l_lessee_name;
714 x_new_lessee_tbl(1).new_party_id := l_party_id;
715 x_new_lessee_tbl(1).contact_name := l_contact_name ;
716 x_new_lessee_tbl(1).contact_id := l_contact_party_id;
717 x_new_lessee_tbl(1).contact_email := l_contact_email;
718 x_new_lessee_tbl(1).contact_phone := l_phone;
719 x_new_lessee_tbl(1).bill_to_address := l_billto_address;
720 x_new_lessee_tbl(1).bill_to_id := l_billto_id;
721 x_new_lessee_tbl(1).cust_acct_number := l_acct_no;
722 x_new_lessee_tbl(1).cust_acct_id := l_acct_id;
723 x_new_lessee_tbl(1).bank_account := l_bank;
724 x_new_lessee_tbl(1).bank_acct_id := l_bank_acct_id;
725 x_new_lessee_tbl(1).invoice_format := l_inv_fmt;
726 x_new_lessee_tbl(1).inv_fmt_id := l_inv_fmt_id;
727 x_new_lessee_tbl(1).payment_method := l_pay_mthd;
728 x_new_lessee_tbl(1).pay_mthd_id := l_pay_mthd_id;
729 x_new_lessee_tbl(1).master_lease := l_master_lease;
730 x_new_lessee_tbl(1).mla_id := l_mla_id;
731 x_new_lessee_tbl(1).credit_line_no := l_credit_line;
732 x_new_lessee_tbl(1).credit_line_id := l_crd_id;
733 x_new_lessee_tbl(1).insurance_yn := l_taa_record.insurance_yn;
734 x_new_lessee_tbl(1).lease_policy_yn := l_taa_record.lease_policy_yn;
735
736 x_return_status := l_return_status;
737
738 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
739 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
740 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PROCESS_TAX.Create_Tax_Schedule ','End(-)');
741 END IF;
742
743 EXCEPTION
744 WHEN OKL_API.G_EXCEPTION_ERROR THEN
745 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
746 l_api_name,
747 G_PKG_NAME,
748 'OKL_API.G_RET_STS_ERROR',
749 x_msg_count,
750 x_msg_data,
751 '_PUB');
752 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
753 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
754 l_api_name,
755 G_PKG_NAME,
756 'OKL_API.G_RET_STS_UNEXP_ERROR',
757 x_msg_count,
758 x_msg_data,
759 '_PUB');
760 WHEN OTHERS THEN
761 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
762 l_api_name,
763 G_PKG_NAME,
764 'OTHERS',
765 x_msg_count,
766 x_msg_data,
767 '_PUB');
768 END Populate_new_Lessee_details;
769
770 PROCEDURE Populate_ThirdParty_Insurance( p_api_version IN NUMBER,
771 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
772 p_taa_id IN NUMBER,
773 x_insurance_tbl OUT NOCOPY insurance_tbl_type,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2)
777 IS
778 --Obtain the insuance details of the taa request.
779 -- changed tcn_id to id for bug:4094898
780 CURSOR c_taa_record(p_taa_id IN NUMBER) IS
781 SELECT *
782 FROM okl_taa_request_details_v
783 WHERE id = p_taa_id;
784
785 --Obtain the insurer/insurance_agent name
786 CURSOR c_lessee(p_party_id IN NUMBER) IS
787 SELECT PARTY_NAME
788 FROM HZ_PARTIES PARTY
789 WHERE PARTY_ID = p_party_id;
790
791
792 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
793 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Insurance_Details';
794 l_api_version CONSTANT NUMBER := 1;
795
796 l_insurer VARCHAR2(360);
797 l_insurance_agent VARCHAR2(360);
798 l_taa_record OKL_TAA_REQUEST_DETAILS_B%rowtype;
799
800 BEGIN
801 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
802 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_New_Lessee_details','Begin(+)');
803 END IF;
804 --Print Input Variables
805 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
807 'p_api_version :'||p_api_version);
808 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
809 'p_init_msg_list :'||p_init_msg_list);
810 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_Insurance_Details',
811 'p_taa_id :'||p_taa_id);
812 END IF;
813
814 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
815 G_PKG_NAME,
816 p_init_msg_list,
817 l_api_version,
818 p_api_version,
819 '_PVT',
820 x_return_status);
821
822 --Fetch the insuance details tied to the ta request
823
824 OPEN c_taa_record(p_taa_id);
825 FETCH c_taa_record INTO l_taa_record;
826 CLOSE c_taa_record;
827
828 --Fetch the Insurer Name
829 OPEN c_lessee(l_taa_record.isu_id);
830 FETCH c_lessee INTO l_insurer;
831 CLOSE c_lessee;
832
833 --Fetch the Insurer Agent name
834 OPEN c_lessee(l_taa_record.int_id);
835 FETCH c_lessee INTO l_insurance_agent;
836 CLOSE c_lessee;
837
838 x_insurance_tbl(1).insurer := l_insurer;
839 x_insurance_tbl(1).insurance_agent := l_insurance_agent;
840 x_insurance_tbl(1).policy_number := l_taa_record.policy_number;
841 x_insurance_tbl(1).covered_amount := l_taa_record.covered_amt;
842 x_insurance_tbl(1).deductible_amount := l_taa_record.deductible_amt;
843 x_insurance_tbl(1).effective_from := l_taa_record.effective_from_date;
844 x_insurance_tbl(1).effective_to := l_taa_record.effective_to_date;
845 x_insurance_tbl(1).proof_provided := l_taa_record.proof_provided_date;
846 x_insurance_tbl(1).proof_required := l_taa_record.proof_required_date;
847 x_insurance_tbl(1).lessor_insured_yn := l_taa_record.lessor_insured_yn;
848 x_insurance_tbl(1).lessor_payee_yn := l_taa_record.lessor_payee_yn;
849
850 x_return_status := l_return_status;
851 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
852 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
853 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_TRANSFER_ASSUMPTION_PVT.populate_thirdparty_insurance','End(-)');
854 END IF;
855
856 EXCEPTION
857 WHEN OKL_API.G_EXCEPTION_ERROR THEN
858 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
859 l_api_name,
860 G_PKG_NAME,
861 'OKL_API.G_RET_STS_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PUB');
865 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
866 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
867 l_api_name,
868 G_PKG_NAME,
869 'OKL_API.G_RET_STS_UNEXP_ERROR',
870 x_msg_count,
871 x_msg_data,
872 '_PUB');
873 WHEN OTHERS THEN
874 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
875 l_api_name,
876 G_PKG_NAME,
877 'OTHERS',
878 x_msg_count,
879 x_msg_data,
880 '_PUB');
881 END Populate_thirdparty_insurance;
882
883
884
885 END OKL_CS_TRANSFER_ASSUMPTION_PVT;
886
887