[Home] [Help]
PACKAGE BODY: APPS.OKL_DEAL_ASSET_PVT
Source
1 Package body OKL_DEAL_ASSET_PVT as
2 /* $Header: OKLRDASB.pls 120.17.12020000.3 2013/04/02 12:15:41 racheruv ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_INVALID_CRITERIA CONSTANT VARCHAR2(200) := 'OKL_LLA_INVALID_CRITERIA';
7 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
8 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
9 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 -------------------------------------------------------------------------------------------------
14 -- GLOBAL OKL MESSAGES
15 -------------------------------------------------------------------------------------------------
16 G_AMOUNT_FORMAT CONSTANT VARCHAR2(200) := 'OKL_AMOUNT_FORMAT';
17 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
18 G_LLA_AST_SERIAL CONSTANT VARCHAR2(200) := 'OKL_LLA_AST_SERIAL';
19 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
20 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
21 -------------------------------------------------------------------------------------------------
22 -- GLOBAL EXCEPTION
23 -------------------------------------------------------------------------------------------------
24 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
25 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
26 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
27 G_API_VERSION CONSTANT NUMBER := 1.0;
28 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
29 -------------------------------------------------------------------------------------------------
30 -- GLOBAL VARIABLES
31 -------------------------------------------------------------------------------------------------
32 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_DEAL_ASSET_PVT';
33 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
34 -------------------------------------------------------------------------------------------------
35
36 TYPE fin_line_tab_type IS TABLE OF NUMBER;
37
38 TYPE asset_rec_type IS RECORD (fin_asset_id OKC_K_LINES_B.id%TYPE,
39 amount OKL_K_LINES.tradein_amount%TYPE,
40 asset_number OKC_K_LINES_TL.name%TYPE,
41 description OKC_K_LINES_TL.item_description%TYPE,
42 oec OKL_K_LINES.oec%TYPE,
43 capitalize_yn OKL_K_LINES.capitalize_down_payment_yn%TYPE,
44 receiver_code OKL_K_LINES.down_payment_receiver_code%TYPE);
45
46 TYPE asset_tbl_type IS TABLE OF asset_rec_type INDEX BY BINARY_INTEGER;
47
48 PROCEDURE Create_all_line(
49 p_api_version IN NUMBER,
50 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 p_las_rec IN las_rec_type,
55 x_las_rec OUT NOCOPY las_rec_type) IS
56
57 l_clev_fin_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
58 l_klev_fin_rec OKL_KLE_PVT.klev_rec_type;
59 l_cimv_model_rec OKL_OKC_MIGRATION_PVT.cimv_rec_type;
60 l_clev_fa_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
61 l_cimv_fa_rec OKL_OKC_MIGRATION_PVT.cimv_rec_type;
62 l_talv_fa_rec OKL_TAL_PVT.talv_rec_type;
63 l_itiv_ib_tbl OKL_ITI_PVT.itiv_tbl_type;
64 -- gboomina Added for Bug 5876083 - Start
65 l_tal_id OKL_TXL_ASSETS_B.ID%TYPE;
66 l_clev_fa_id OKC_K_LINES_B.ID%TYPE;
67 l_asset_number FA_ADDITIONS_B.ASSET_NUMBER%TYPE;
68 -- gboomina Added for Bug 5876083 - End
69 x_clev_fin_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
70 x_clev_model_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
71 x_clev_fa_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
72 x_clev_ib_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
73
74 l_residual_value NUMBER;
75 l_guranteed_amount NUMBER;
76 l_unit_cost NUMBER;
77 l_press_yn VARCHAR2(1);
78 l_new_yn VARCHAR2(1);
79 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ALL_LINE';
80 l_api_version CONSTANT NUMBER := 1;
81 l_msg_data VARCHAR2(4000);
82 l_msg_index_out number;
83
84 -- gboomina added - Cursor to get the okl_txl_assets_b rec's id
85 -- Record for Corporate book details will be created in okl_txl_assets_b when asset is getting created.
86 -- As per new UI design, We need to get this record and update the user inputs along with asset
87 -- creation.
88 CURSOR get_tal_id_csr(p_clev_fa_id NUMBER, p_asset_number VARCHAR2) IS
89 SELECT ID
90 FROM OKL_TXL_ASSETS_B
91 WHERE KLE_ID = p_clev_fa_id
92 AND ASSET_NUMBER = p_asset_number;
93
94 --Bug #15992711 : Start
95 --Cursor to check if the asset is a released asset
96 -- 'N' to indicate it is not a new asset
97 CURSOR chk_rel_ass_csr (p_asset_number VARCHAR2) IS
98 SELECT 'N' new_asset
99 FROM okl_asset_returns_b KAR
100 WHERE KAR.kle_id IN (SELECT KLE.cle_id
101 FROM okl_txl_assets_b TXL
102 ,okl_txd_assets_b TXD
103 ,okc_k_lines_b KLE
104 WHERE TXD.asset_number = p_asset_number
105 AND KLE.id = TXL.kle_id
106 AND TXL.id = TXD.tal_id )
107 AND KAR.ars_code = 'RE_LEASE';
108 -- Bug #15992711 : End
109
110 BEGIN
111 x_return_status := OKL_API.G_RET_STS_SUCCESS;
112 -- Call start_activity to create savepoint, check compatibility and initialize message list
113 x_return_status := OKL_API.START_ACTIVITY(
114 p_api_name => l_api_name,
115 p_pkg_name => g_pkg_name,
116 p_init_msg_list => p_init_msg_list,
117 l_api_version => l_api_version,
118 p_api_version => p_api_version,
119 p_api_type => g_api_type,
120 x_return_status => x_return_status);
121
122 -- check if activity started successfully
123 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
124 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
125 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
126 RAISE OKL_API.G_EXCEPTION_ERROR;
127 END IF;
128
129 l_cimv_model_rec.object1_id1 := p_las_rec.inventory_item_id;
130 l_clev_fin_rec.exception_yn := 'N';
131 l_clev_fin_rec.dnz_chr_id := p_las_rec.dnz_chr_id;
132 l_clev_fin_rec.item_description := p_las_rec.description;
133 l_cimv_model_rec.object1_id2 := p_las_rec.inventory_org_id;
134 l_klev_fin_rec.residual_code := nvl(p_las_rec.residual_code,'LESSEE');
135
136 IF (p_las_rec.residual_value IS NOT NULL) THEN
137 l_residual_value := NULL;
138 l_residual_value := okl_accounting_util.cross_currency_round_amount(
139 p_amount => p_las_rec.residual_value,
140 p_currency_code => p_las_rec.currency_code
141 );
142 IF (l_residual_value IS NULL) THEN
143 OKL_API.set_message(p_app_name => G_APP_NAME,
144 p_msg_name => G_AMOUNT_FORMAT,
145 p_token1 => G_COL_NAME_TOKEN,
146 p_token1_value => 'OKC_K_LINES.RESIDUAL_VALUE');
147 RAISE OKL_API.G_EXCEPTION_ERROR;
148 ELSE
149 l_klev_fin_rec.residual_value := l_residual_value;
150 END IF;
151 -- gboomina added for Bug 6369401 - Start
152 -- down the line OKL_CREATE_KLE_PVT expects NULL to be passed if not entered.
153 ELSE
154 l_klev_fin_rec.residual_value := NULL;
155 -- gboomina added for Bug 6369401 - End
156 END IF;
157
158 IF (p_las_rec.guranteed_amount IS NOT NULL) THEN
159 l_guranteed_amount := NULL;
160 l_guranteed_amount := okl_accounting_util.cross_currency_round_amount(
161 p_amount => p_las_rec.guranteed_amount,
162 p_currency_code => p_las_rec.currency_code
163 );
164 IF (l_guranteed_amount IS NULL) THEN
165 OKL_API.set_message(p_app_name => G_APP_NAME,
166 p_msg_name => G_AMOUNT_FORMAT,
167 p_token1 => G_COL_NAME_TOKEN,
168 p_token1_value => 'OKC_K_LINES.GURANTEED_AMOUNT');
169 RAISE OKL_API.G_EXCEPTION_ERROR;
170 ELSE
171 l_klev_fin_rec.residual_grnty_amount := l_guranteed_amount;
172 END IF;
173 END IF;
174
175 IF (p_las_rec.rvi_premium IS NOT NULL) THEN
176 l_klev_fin_rec.rvi_premium := p_las_rec.rvi_premium;
177 END IF;
178
179
180 IF ((p_las_rec.prescribed_asset_yn IS NULL) OR (p_las_rec.prescribed_asset_yn ='N' ))THEN
181 l_press_yn := 'N';
182 ELSIF (p_las_rec.prescribed_asset_yn ='Y' ) THEN
183 l_press_yn := 'Y';
184 END IF;
185 l_klev_fin_rec.prescribed_asset_yn := l_press_yn;
186
187 l_cimv_fa_rec.object1_id1 := NULL;
188 l_cimv_fa_rec.object1_id2 := NULL;
189
190 --Bug #15992711: Start
191 /*IF (p_las_rec.release_asset_flag) THEN
192 l_new_yn := 'N';
193 else
194 l_new_yn := 'Y';
195 END IF;
196
197
198 IF ((p_las_rec.release_asset_flag) AND (p_las_rec.asset_id IS NOT NULL)) THEN
199 l_cimv_fa_rec.object1_id1 := p_las_rec.asset_id;
200 l_cimv_fa_rec.object1_id2 := '#';
201 END IF; */
202
203 OPEN chk_rel_ass_csr( p_las_rec.asset_number);
204 FETCH chk_rel_ass_csr INTO l_new_yn;
205 IF chk_rel_ass_csr%NOTFOUND THEN
206 l_new_yn := 'Y';
207 ELSE
208 l_new_yn := 'N';
209 END IF;
210 CLOSE chk_rel_ass_csr;
211
212 IF (p_las_rec.asset_id IS NOT NULL) THEN
213
214 l_cimv_fa_rec.object1_id1 := p_las_rec.asset_id;
215 l_cimv_fa_rec.object1_id2 := '#';
216 END IF;
217
218 -- Bug #15992711 : End
219
220
221 l_talv_fa_rec.dnz_khr_id := p_las_rec.dnz_chr_id;
222 l_talv_fa_rec.asset_number := p_las_rec.asset_number;
223 l_talv_fa_rec.description := p_las_rec.description;
224
225 IF ((p_las_rec.deal_type = 'LEASE') AND (p_las_rec.fa_location_id IS NULL)) THEN
226 OKL_API.set_message(p_app_name => G_APP_NAME,
227 p_msg_name => G_REQUIRED_VALUE,
228 p_token1 => G_COL_NAME_TOKEN,
229 p_token1_value => 'OKL_TXL_ASSETS_B.FA_LOCATION_ID');
230 RAISE OKL_API.G_EXCEPTION_ERROR;
231 ELSE
232 l_talv_fa_rec.fa_location_id := p_las_rec.fa_location_id;
233 END IF;
234
235 l_talv_fa_rec.asset_key_id := p_las_rec.asset_key_id;
236
237 IF (p_las_rec.unit_cost IS NOT NULL) THEN
238 l_unit_cost := NULL;
239 l_unit_cost := okl_accounting_util.cross_currency_round_amount(
240 p_amount => p_las_rec.unit_cost,
241 p_currency_code => p_las_rec.currency_code
242 );
243 IF (l_unit_cost IS NULL) THEN
244 OKL_API.set_message(p_app_name => G_APP_NAME,
245 p_msg_name => G_AMOUNT_FORMAT,
246 p_token1 => G_COL_NAME_TOKEN,
247 p_token1_value => 'OKC_K_LINES_B.PRICE_UNIT');
248 RAISE OKL_API.G_EXCEPTION_ERROR;
249 ELSE
250 l_talv_fa_rec.original_cost := l_unit_cost;
251 END IF;
252 END IF;
253
254 l_talv_fa_rec.current_units:= p_las_rec.units;
255 l_talv_fa_rec.model_number := p_las_rec.model_number;
256 l_talv_fa_rec.year_manufactured := p_las_rec.year_manufactured;
257 l_talv_fa_rec.manufacturer_name := p_las_rec.manufacturer_name;
258
259 IF (p_las_rec.residual_percentage IS NOT NULL) THEN
260 l_klev_fin_rec.residual_percentage := p_las_rec.residual_percentage;
261 -- gboomina added for Bug 6369401 - Start
262 -- down the line OKL_CREATE_KLE_PVT expects NULL to be passed if not entered.
263 ELSE
264 l_klev_fin_rec.residual_percentage := NULL;
265 -- gboomina added for Bug 6369401 - End
266 END IF;
267
268 IF (l_new_yn = 'Y') THEN
269 l_talv_fa_rec.used_asset_yn := null;
270 ELSE
271 l_talv_fa_rec.used_asset_yn := 'Y';
272 -- Bug #15992711 : Start
273 l_klev_fin_rec.re_lease_yn := 'Y';
274 -- Bug #15992711 : End
275 END IF;
276
277 l_klev_fin_rec.date_delivery_expected := p_las_rec.date_delivery_expected;
278 l_klev_fin_rec.date_funding_expected := p_las_rec.date_funding_expected;
279
280 l_klev_fin_rec.validate_dff_yn := 'Y';
281 l_klev_fin_rec.attribute_category := p_las_rec.attribute_category;
282 l_klev_fin_rec.attribute1 := p_las_rec.attribute1;
283 l_klev_fin_rec.attribute2 := p_las_rec.attribute2;
284 l_klev_fin_rec.attribute3 := p_las_rec.attribute3;
285 l_klev_fin_rec.attribute4 := p_las_rec.attribute4;
286 l_klev_fin_rec.attribute5 := p_las_rec.attribute5;
287 l_klev_fin_rec.attribute6 := p_las_rec.attribute6;
288 l_klev_fin_rec.attribute7 := p_las_rec.attribute7;
289 l_klev_fin_rec.attribute8 := p_las_rec.attribute8;
290 l_klev_fin_rec.attribute9 := p_las_rec.attribute9;
291 l_klev_fin_rec.attribute10 := p_las_rec.attribute10;
292 l_klev_fin_rec.attribute11 := p_las_rec.attribute11;
293 l_klev_fin_rec.attribute12 := p_las_rec.attribute12;
294 l_klev_fin_rec.attribute13 := p_las_rec.attribute13;
295 l_klev_fin_rec.attribute14 := p_las_rec.attribute14;
296 l_klev_fin_rec.attribute15 := p_las_rec.attribute15;
297
298
299 IF (p_las_rec.units > 0) THEN
300 FOR i in 1..p_las_rec.units
301 LOOP
302 l_itiv_ib_tbl(i).mfg_serial_number_yn := 'N';
303 l_itiv_ib_tbl(i).object_id1_new := p_las_rec.party_site_use_id;
304 l_itiv_ib_tbl(i).object_id2_new := '#';
305 l_itiv_ib_tbl(i).jtot_object_code_new := 'OKX_PARTYSITE';
306 END LOOP;
307 END IF;
308
309 -- gboomina Added for Bug 5876083 - Start
310 -- setting OKC context which is required by other API's down the line
311 IF p_las_rec.dnz_chr_id IS NOT NULL THEN
312 okl_context.set_okc_org_context(p_chr_id => p_las_rec.dnz_chr_id);
313 END IF;
314 -- gboomina Added for Bug 5876083 - End
315
316 -- Business API call section
317 OKL_CREATE_KLE_PVT.Create_all_line(p_api_version => p_api_version,
318 p_init_msg_list => p_init_msg_list,
319 x_return_status => x_return_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data,
322 P_new_yn => l_new_yn,
323 p_asset_number => p_las_rec.asset_number,
324 p_clev_fin_rec => l_clev_fin_rec,
325 p_klev_fin_rec => l_klev_fin_rec,
326 p_cimv_model_rec => l_cimv_model_rec,
327 p_clev_fa_rec => l_clev_fa_rec,
328 p_cimv_fa_rec => l_cimv_fa_rec,
329 p_talv_fa_rec => l_talv_fa_rec,
330 p_itiv_ib_tbl => l_itiv_ib_tbl,
331 x_clev_fin_rec => x_clev_fin_rec,
332 x_clev_model_rec => x_clev_model_rec,
333 x_clev_fa_rec => x_clev_fa_rec,
334 x_clev_ib_rec => x_clev_ib_rec);
335
336 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
337 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
338 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 ELSIF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
341 x_las_rec := p_las_rec;
342 x_las_rec.clev_fin_id := x_clev_fin_rec.id;
343 x_las_rec.clev_model_id := x_clev_model_rec.id;
344 x_las_rec.clev_fa_id := x_clev_fa_rec.id;
345 x_las_rec.clev_ib_id := x_clev_ib_rec.id;
346
347 -- Bug #15992711: Start
348 --x_las_rec.release_asset_flag := l_new_yn;
349 -- Bug #15992711: End
350
351 -- Get tal_id
352 l_clev_fa_id :=x_las_rec.clev_fa_id ;
353 -- always cast asset number to upper case because asset number is stored
354 -- in upper case in tables
355 l_asset_number :=UPPER(p_las_rec.asset_number);
356 OPEN get_tal_id_csr(l_clev_fa_id, l_asset_number);
357 FETCH get_tal_id_csr INTO l_tal_id;
358 CLOSE get_tal_id_csr;
359 x_las_rec.tal_id := l_tal_id;
360
361 END IF;
362
363
364 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
365
366 EXCEPTION
367 WHEN OKL_API.G_EXCEPTION_ERROR THEN
368 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
369 p_api_name => l_api_name,
370 p_pkg_name => g_pkg_name,
371 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_api_type => g_api_type);
375
376 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
377 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
378 p_api_name => l_api_name,
379 p_pkg_name => g_pkg_name,
380 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_api_type => g_api_type);
384
385 WHEN OTHERS THEN
386 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
387 p_api_name => l_api_name,
388 p_pkg_name => g_pkg_name,
389 p_exc_name => 'OTHERS',
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_api_type => g_api_type);
393
394 END Create_all_line;
395
396
397 PROCEDURE update_all_line(
398 p_api_version IN NUMBER,
399 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
400 x_return_status OUT NOCOPY VARCHAR2,
401 x_msg_count OUT NOCOPY NUMBER,
402 x_msg_data OUT NOCOPY VARCHAR2,
403 p_las_rec IN las_rec_type,
404 x_las_rec OUT NOCOPY las_rec_type) IS
405
406 l_clev_fin_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
407 l_klev_fin_rec OKL_KLE_PVT.klev_rec_type;
408 l_clev_model_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
409 l_cimv_model_rec OKL_OKC_MIGRATION_PVT.cimv_rec_type;
410 l_clev_fa_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
411 l_clev_ib_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
412 l_cimv_fa_rec OKL_OKC_MIGRATION_PVT.cimv_rec_type;
413 l_talv_fa_rec OKL_TAL_PVT.talv_rec_type;
414 l_itiv_rec OKL_TXL_ITM_INSTS_PUB.iipv_rec_type;
415
416 x_clev_fin_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
417 x_clev_model_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
418 x_clev_fa_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
419 x_clev_ib_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
420
421 l_residual_value NUMBER;
422 l_guranteed_amount NUMBER;
423 l_unit_cost NUMBER;
424 l_press_yn VARCHAR2(1);
425 l_new_yn VARCHAR2(1);
426 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ALL_LINE';
427 l_api_version CONSTANT NUMBER := 1;
428 l_msg_data VARCHAR2(4000);
429 l_msg_index_out number;
430
431 ln_dummy number := 0;
432 CURSOR c_serial_num_present(p_dnz_cle_id OKL_TXL_ITM_INSTS.DNZ_CLE_ID%TYPE) is
433 SELECT 1
434 FROM OKL_TXL_ITM_INSTS
435 WHERE dnz_cle_id = p_dnz_cle_id;
436 BEGIN
437 x_return_status := OKL_API.G_RET_STS_SUCCESS;
438 -- Call start_activity to create savepoint, check compatibility and initialize message list
439 x_return_status := OKL_API.START_ACTIVITY(
440 p_api_name => l_api_name,
441 p_pkg_name => g_pkg_name,
442 p_init_msg_list => p_init_msg_list,
443 l_api_version => l_api_version,
444 p_api_version => p_api_version,
445 p_api_type => g_api_type,
446 x_return_status => x_return_status);
447
448 -- check if activity started successfully
449 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
450 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
451 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
452 RAISE OKL_API.G_EXCEPTION_ERROR;
453 END IF;
454
455 l_clev_fin_rec.id := p_las_rec.clev_fin_id;
456 l_clev_model_rec.id := p_las_rec.clev_model_id;
457 l_clev_fa_rec.id := p_las_rec.clev_fa_id;
458 l_clev_ib_rec.id := p_las_rec.clev_ib_id;
459 l_clev_fin_rec.exception_yn := 'N';
460 l_clev_fin_rec.dnz_chr_id := p_las_rec.dnz_chr_id;
461 l_clev_fin_rec.item_description := p_las_rec.description;
462 l_cimv_model_rec.object1_id1 := p_las_rec.inventory_item_id;
463 l_cimv_model_rec.object1_id2 := p_las_rec.inventory_org_id;
464 l_cimv_model_rec.exception_yn := 'N';
465
466 l_klev_fin_rec.residual_code := nvl(p_las_rec.residual_code,'NONE');
467
468 IF (p_las_rec.residual_value IS NOT NULL) THEN
469 l_residual_value := NULL;
470 l_residual_value := okl_accounting_util.cross_currency_round_amount(
471 p_amount => p_las_rec.residual_value,
472 p_currency_code => p_las_rec.currency_code
473 );
474 IF (l_residual_value IS NULL) THEN
475 OKL_API.set_message(p_app_name => G_APP_NAME,
476 p_msg_name => G_AMOUNT_FORMAT,
477 p_token1 => G_COL_NAME_TOKEN,
478 p_token1_value => 'OKC_K_LINES.RESIDUAL_VALUE');
479 RAISE OKL_API.G_EXCEPTION_ERROR;
480 ELSE
481 l_klev_fin_rec.residual_value := l_residual_value;
482 END IF;
483 -- gboomina added for Bug 6369401 - Start
484 -- down the line OKL_CREATE_KLE_PVT expects NULL to be passed if not entered.
485 ELSE
486 l_klev_fin_rec.residual_value := NULL;
487 -- gboomina added for Bug 6369401 - End
488 END IF;
489
490 IF (p_las_rec.guranteed_amount IS NOT NULL) THEN
491 l_guranteed_amount := NULL;
492 l_guranteed_amount := okl_accounting_util.cross_currency_round_amount(
493 p_amount => p_las_rec.guranteed_amount,
494 p_currency_code => p_las_rec.currency_code
495 );
496 IF (l_guranteed_amount IS NULL) THEN
497 OKL_API.set_message(p_app_name => G_APP_NAME,
498 p_msg_name => G_AMOUNT_FORMAT,
499 p_token1 => G_COL_NAME_TOKEN,
500 p_token1_value => 'OKC_K_LINES.GURANTEED_AMOUNT');
501 RAISE OKL_API.G_EXCEPTION_ERROR;
502 ELSE
503 l_klev_fin_rec.residual_grnty_amount := l_guranteed_amount;
504 END IF;
505 END IF;
506
507 IF (p_las_rec.rvi_premium IS NOT NULL) THEN
508 l_klev_fin_rec.rvi_premium := p_las_rec.rvi_premium;
509 END IF;
510
511
512 IF ((p_las_rec.prescribed_asset_yn IS NULL) OR (p_las_rec.prescribed_asset_yn ='N' ))THEN
513 l_press_yn := 'N';
514 ELSIF (p_las_rec.prescribed_asset_yn ='Y' ) THEN
515 l_press_yn := 'Y';
516 END IF;
517 l_klev_fin_rec.prescribed_asset_yn := l_press_yn;
518
519 l_talv_fa_rec.dnz_khr_id := p_las_rec.dnz_chr_id;
520 l_talv_fa_rec.asset_number := upper(p_las_rec.asset_number);
521 l_talv_fa_rec.description := p_las_rec.description;
522
523 IF ((p_las_rec.deal_type = 'LEASE') AND (p_las_rec.fa_location_id IS NULL)) THEN
524 OKL_API.set_message(p_app_name => G_APP_NAME,
525 p_msg_name => G_REQUIRED_VALUE,
526 p_token1 => G_COL_NAME_TOKEN,
527 p_token1_value => 'OKL_TXL_ASSETS_B.FA_LOCATION_ID');
528 RAISE OKL_API.G_EXCEPTION_ERROR;
529 ELSE
530 l_talv_fa_rec.fa_location_id := p_las_rec.fa_location_id;
531 END IF;
532
533 l_talv_fa_rec.asset_key_id := p_las_rec.asset_key_id;
534
535 IF (p_las_rec.unit_cost IS NOT NULL) THEN
536 l_unit_cost := NULL;
537 l_unit_cost := okl_accounting_util.cross_currency_round_amount(
538 p_amount => p_las_rec.unit_cost,
539 p_currency_code => p_las_rec.currency_code
540 );
541 IF (l_unit_cost IS NULL) THEN
542 OKL_API.set_message(p_app_name => G_APP_NAME,
543 p_msg_name => G_AMOUNT_FORMAT,
544 p_token1 => G_COL_NAME_TOKEN,
545 p_token1_value => 'OKC_K_LINES_B.PRICE_UNIT');
546 RAISE OKL_API.G_EXCEPTION_ERROR;
547 ELSE
548 l_talv_fa_rec.original_cost := l_unit_cost;
549 END IF;
550 END IF;
551
552 IF ((p_las_rec.units IS NOT NULL) AND (p_las_rec.old_units IS NOT NULL) AND (p_las_rec.clev_ib_id IS NOT NULL)) THEN
553 IF (p_las_rec.units <> p_las_rec.old_units) THEN
554 OKL_API.set_message(p_app_name => G_APP_NAME,
555 p_msg_name => G_LLA_AST_SERIAL);
556 RAISE OKL_API.G_EXCEPTION_ERROR;
557 END IF;
558 END IF;
559 l_talv_fa_rec.current_units:= p_las_rec.units;
560 l_talv_fa_rec.model_number := p_las_rec.model_number;
561 l_talv_fa_rec.year_manufactured := p_las_rec.year_manufactured;
562 l_talv_fa_rec.manufacturer_name := p_las_rec.manufacturer_name;
563
564 IF (p_las_rec.residual_percentage IS NOT NULL) THEN
565 l_klev_fin_rec.residual_percentage := p_las_rec.residual_percentage;
566 -- gboomina added for Bug 6369401 - Start
567 -- down the line OKL_CREATE_KLE_PVT expects NULL to be passed if not entered.
568 ELSE
569 l_klev_fin_rec.residual_percentage := NULL;
570 -- gboomina added for Bug 6369401 - End
571 END IF;
572
573 IF (p_las_rec.release_asset_flag) THEN
574 l_new_yn := 'N';
575 else
576 l_new_yn := 'Y';
577 END IF;
578
579 IF (l_new_yn = 'Y') THEN
580 l_talv_fa_rec.used_asset_yn := null;
581 ELSE
582 l_talv_fa_rec.used_asset_yn := 'Y';
583 END IF;
584
585 l_cimv_fa_rec.object1_id1 := null;
586 l_cimv_fa_rec.object1_id2 := null;
587
588 l_itiv_rec.mfg_serial_number_yn := l_new_yn;
589 l_itiv_rec.object_id1_new := p_las_rec.party_site_use_id;
590 l_itiv_rec.object_id2_new := '#';
591
592 l_klev_fin_rec.date_delivery_expected := p_las_rec.date_delivery_expected;
593 l_klev_fin_rec.date_funding_expected := p_las_rec.date_funding_expected;
594
595 l_klev_fin_rec.validate_dff_yn := 'Y';
596 l_klev_fin_rec.attribute_category := p_las_rec.attribute_category;
597 l_klev_fin_rec.attribute1 := p_las_rec.attribute1;
598 l_klev_fin_rec.attribute2 := p_las_rec.attribute2;
599 l_klev_fin_rec.attribute3 := p_las_rec.attribute3;
600 l_klev_fin_rec.attribute4 := p_las_rec.attribute4;
601 l_klev_fin_rec.attribute5 := p_las_rec.attribute5;
602 l_klev_fin_rec.attribute6 := p_las_rec.attribute6;
603 l_klev_fin_rec.attribute7 := p_las_rec.attribute7;
604 l_klev_fin_rec.attribute8 := p_las_rec.attribute8;
605 l_klev_fin_rec.attribute9 := p_las_rec.attribute9;
606 l_klev_fin_rec.attribute10 := p_las_rec.attribute10;
607 l_klev_fin_rec.attribute11 := p_las_rec.attribute11;
608 l_klev_fin_rec.attribute12 := p_las_rec.attribute12;
609 l_klev_fin_rec.attribute13 := p_las_rec.attribute13;
610 l_klev_fin_rec.attribute14 := p_las_rec.attribute14;
611 l_klev_fin_rec.attribute15 := p_las_rec.attribute15;
612
613 OPEN c_serial_num_present(l_clev_fin_rec.id);
614 FETCH c_serial_num_present into ln_dummy;
615 CLOSE c_serial_num_present;
616
617 IF (ln_dummy = 1) THEN
618 l_itiv_rec.dnz_cle_id := l_clev_fin_rec.id;
619 END IF;
620
621 -- gboomina Added for Bug 5876083 - Start
622 -- setting OKC context which is required by other API's down the line
623 IF p_las_rec.dnz_chr_id IS NOT NULL THEN
624 okl_context.set_okc_org_context(p_chr_id => p_las_rec.dnz_chr_id);
625 END IF;
626 -- gboomina Added for Bug 5876083 - End
627
628 -- Business API call section
629 OKL_CREATE_KLE_PVT.update_all_line(p_api_version => p_api_version,
630 p_init_msg_list => p_init_msg_list,
631 x_return_status => x_return_status,
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data,
634 P_new_yn => l_new_yn,
635 p_asset_number => p_las_rec.asset_number,
636 p_clev_fin_rec => l_clev_fin_rec,
637 p_klev_fin_rec => l_klev_fin_rec,
638 p_clev_model_rec => l_clev_model_rec,
639 p_cimv_model_rec => l_cimv_model_rec,
640 p_clev_fa_rec => l_clev_fa_rec,
641 p_cimv_fa_rec => l_cimv_fa_rec,
642 p_talv_fa_rec => l_talv_fa_rec,
643 p_clev_ib_rec => l_clev_ib_rec,
644 p_itiv_ib_rec => l_itiv_rec,
645 x_clev_fin_rec => x_clev_fin_rec,
646 x_clev_model_rec => x_clev_model_rec,
647 x_clev_fa_rec => x_clev_fa_rec,
648 x_clev_ib_rec => x_clev_ib_rec);
649
650 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
651 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
652 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
653 RAISE OKL_API.G_EXCEPTION_ERROR;
654 ELSIF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
655 x_las_rec := p_las_rec;
656 x_las_rec.clev_fin_id := x_clev_fin_rec.id;
657 x_las_rec.clev_model_id := x_clev_model_rec.id;
658 x_las_rec.clev_fa_id := x_clev_fa_rec.id;
659 x_las_rec.clev_ib_id := x_clev_ib_rec.id;
660 END IF;
661
662
663 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
664
665 EXCEPTION
666 WHEN OKL_API.G_EXCEPTION_ERROR THEN
667 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
668 p_api_name => l_api_name,
669 p_pkg_name => g_pkg_name,
670 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
671 x_msg_count => x_msg_count,
672 x_msg_data => x_msg_data,
673 p_api_type => g_api_type);
674
675 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
676 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
677 p_api_name => l_api_name,
678 p_pkg_name => g_pkg_name,
679 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
680 x_msg_count => x_msg_count,
681 x_msg_data => x_msg_data,
682 p_api_type => g_api_type);
683
684 WHEN OTHERS THEN
685 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
686 p_api_name => l_api_name,
687 p_pkg_name => g_pkg_name,
688 p_exc_name => 'OTHERS',
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data,
691 p_api_type => g_api_type);
692
693 END update_all_line;
694
695 PROCEDURE load_all_line(
696 p_api_version IN NUMBER,
697 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
698 x_return_status OUT NOCOPY VARCHAR2,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701 p_chr_id IN NUMBER,
702 p_clev_fin_id IN NUMBER,
703 x_las_rec OUT NOCOPY las_rec_type) IS
704 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ALL_LINE';
705 l_api_version CONSTANT NUMBER := 1;
706
707 -- gboomina Modified this to get deal type and sts code also
708 CURSOR c_khr_info(p_khr_id NUMBER) IS
709 SELECT id, deal_type, sts_code
710 FROM okl_k_headers_full_v
711 WHERE id = p_khr_id;
712
713 l_khr_id OKL_K_HEADERS_FULL_V.ID%TYPE;
714 l_deal_type OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE;
715 l_sts_code OKL_K_HEADERS_FULL_V.STS_CODE%TYPE;
716 l_is_contract_active BOOLEAN;
717
718 CURSOR c_item_line(p_dnz_chr_id NUMBER, p_clev_fin_id NUMBER) IS
719 SELECT cleb_mdl.id clev_model_id,
720 cim_mdl.object1_id1 inventory_item_id,
721 cim_mdl.object1_id2 inventory_org_id,
722 cim_mdl.number_of_items,
723 cleb_mdl.price_unit,
724 msit.description inventory_item_name
725 FROM okc_k_lines_b cleb_mdl,
726 okc_line_styles_b lse_mdl,
727 okc_k_items cim_mdl,
728 mtl_system_items_tl msit
729 WHERE cim_mdl.cle_id = cleb_mdl.id
730 AND cim_mdl.dnz_chr_id = cleb_mdl.dnz_chr_id
731 AND cim_mdl.jtot_object1_code = 'OKX_SYSITEM'
732 AND lse_mdl.id = cleb_mdl.lse_id
733 AND lse_mdl.lty_code = 'ITEM'
734 AND msit.inventory_item_id = cim_mdl.object1_id1
735 AND msit.organization_id = cim_mdl.object1_id2
736 AND msit.language = USERENV('LANG')
737 AND cleb_mdl.dnz_chr_id = p_dnz_chr_id
738 AND cleb_mdl.cle_id = p_clev_fin_id;
739
740 c_item_line_rec c_item_line%ROWTYPE;
741
742 CURSOR c_fa_line(p_dnz_chr_id NUMBER, p_clev_fin_id NUMBER) IS
743 SELECT cleb_fa.id clev_fa_id,
744 txl.id txl_id,
745 txl.model_number,
746 kle_fa.year_built,
747 txl.fa_location_id,
748 ast_loc.name fa_location_name,
749 txl.manufacturer_name,
750 txl.asset_key_id,
751 ast_key.concatenated_segments asset_key_name,
752 cim_fa.object1_id1 asset_id
753 FROM okl_txl_assets_b txl,
754 okc_line_styles_b lse_fa,
755 okl_k_lines kle_fa,
756 okc_k_lines_b cleb_fa,
757 okl_asset_key_lov_uv ast_key,
758 okx_ast_locs_v ast_loc,
759 okc_k_items cim_fa
760 WHERE cleb_fa.id = kle_fa.id
761 AND lse_fa.id = cleb_fa.lse_id
762 AND lse_fa.lty_code = 'FIXED_ASSET'
763 AND cleb_fa.id = txl.kle_id
764 AND txl.asset_key_id = ast_key.code_combination_id(+)
765 AND txl.fa_location_id = ast_loc.location_id(+)
766 AND cleb_fa.cle_id = p_clev_fin_id
767 AND cleb_fa.dnz_chr_id = p_dnz_chr_id
768 and cleb_fa.id = cim_fa.cle_id
769 AND cleb_fa.dnz_chr_id = cim_fa.dnz_chr_id;
770
771 c_fa_line_rec c_fa_line%ROWTYPE;
772
773 CURSOR c_ib_line(p_dnz_chr_id NUMBER, p_clev_fin_id NUMBER) IS
774 SELECT cleb_ib.id clev_ib_id,
775 iti.id,
776 iti.object_id1_new party_site_use_id
777 FROM okc_k_lines_b cleb_inst,
778 okc_k_lines_b cleb_ib,
779 okc_line_styles_b lse_inst,
780 okc_line_styles_b lse_ib,
781 okl_txl_itm_insts iti
782 WHERE cleb_inst.cle_id = p_clev_fin_id
783 AND cleb_inst.dnz_chr_id = p_dnz_chr_id
784 AND cleb_inst.lse_id = lse_inst.id
785 AND lse_inst.lty_code = 'FREE_FORM2'
786 AND cleb_ib.cle_id = cleb_inst.id
787 AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
788 AND cleb_ib.lse_id = lse_ib.id
789 AND lse_ib.lty_code = 'INST_ITEM'
790 AND iti.kle_id = cleb_ib.id;
791
792 c_ib_line_rec c_ib_line%ROWTYPE;
793
794 CURSOR c_install_site(p_party_site_use_id NUMBER) IS
795 SELECT SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
796 hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
797 null, null,null,null,null,null,null,'n','n',80,1,1),1,80) party_site_name
798 FROM hz_locations hl,
799 hz_party_sites hps,
800 hz_party_site_uses hpu
801 WHERE hpu.party_site_use_id = p_party_site_use_id
802 AND hps.party_site_id = hpu.party_site_id
803 AND hl.location_id = hps.location_id;
804
805 --Bug# 9721029
806 l_install_site_rec c_install_site%ROWTYPE;
807
808 -- gboomina added - Start
809 -- This cursor is used to get Install Site from CSI_ITEM_INSTANCES for Active Contract
810 -- vsgandhi 17-Aug-2011 Fixed Bug 12783759
811 -- Passed p_clev_fin_id to cursor to get the install site name
812
813 CURSOR c_install_site_active(p_chr_id NUMBER, p_clev_fin_id NUMBER) IS
814 select cle_ib.id clev_ib_id,
815 substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,
816 hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,
817 hl.postal_code,null,hl.country,null, null,null,null,null,null,
818 null,'n','n',80,1,1),1,80) party_site_name
819 from hz_locations hl,
820 hz_party_sites hps,
821 csi_item_instances csi,
822 okc_k_items cim_ib,
823 okc_line_styles_b lse_ib,
824 okc_k_lines_b cle_ib,
825 okc_line_styles_b lse_inst,
826 okc_k_lines_b cle_inst,
827 okc_line_styles_b lse_fin,
828 okc_k_lines_b cle_fin
829 where cle_fin.cle_id is null
830 and cle_fin.chr_id = cle_fin.dnz_chr_id
831 and lse_fin.id = cle_fin.lse_id
832 and lse_fin.lty_code = 'FREE_FORM1'
833 and cle_inst.cle_id = cle_fin.id
834 and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
835 and cle_inst.lse_id = lse_inst.id
836 and lse_inst.lty_code = 'FREE_FORM2'
837 and cle_ib.cle_id = cle_inst.id
838 and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
839 and cle_ib.lse_id = lse_ib.id
840 and lse_ib.lty_code = 'INST_ITEM'
841 and cim_ib.cle_id = cle_ib.id
842 and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
843 and cim_ib.object1_id1 = csi.instance_id
844 and cim_ib.object1_id2 = '#'
845 and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
846 and csi.install_location_id = hps.party_site_id
847 and csi.install_location_type_code = 'HZ_PARTY_SITES'
848 and hps.location_id = hl.location_id
849 and cle_fin.dnz_chr_id = p_chr_id
850 and cle_fin.id = p_clev_fin_id
851 order by cle_fin.id asc;
852 l_install_site_active_rec c_install_site_active%ROWTYPE;
853
854 --Bug# 8652738
855 -- Corrected table name to fa_additions
856 -- This cursor is used to get Asset Key from FA tables for Active Contract
857 CURSOR c_asset_key_active(p_asset_number IN fa_additions_b.asset_number%type)
858 IS
859 select faa.asset_key_ccid ccid,
860 fakw.concatenated_segments segs
861 from fa_additions faa,
862 fa_asset_keywords_kfv fakw
863 where faa.asset_key_ccid = fakw.code_combination_id
864 and faa.asset_number = p_asset_number;
865 l_asset_key_active_rec c_asset_key_active%ROWTYPE;
866
867 -- This cursor is used to get Model Number, Manufacturer and FA Location
868 -- from FA tables for Active Contract
869 CURSOR c_fa_line_active(p_asset_number IN fa_additions_b.asset_number%type)
870 IS
871 select loc.name fa_location_name,
872 loc.id1 fa_location_id,
873 fa.manufacturer_name,
874 fa.model_number
875 from fa_distribution_history fa_hist,
876 okx_ast_locs_v loc,
877 fa_additions_b fa
878 where fa.asset_id = fa_hist.asset_id
879 and fa_hist.location_id = loc.location_id
880 and fa.asset_number = p_asset_number
881 and fa_hist.transaction_header_id_out is null
882 and fa_hist.retirement_id is null;
883 l_fa_line_active_rec c_fa_line_active%ROWTYPE;
884
885 -- This cursor is used to get asset number
886 CURSOR c_asset_info(p_cle_id OKC_K_LINES_B.ID%TYPE)
887 IS
888 select name
889 from okc_k_lines_tl
890 where id = p_cle_id;
891 l_asset_number OKC_K_LINES_TL.NAME%TYPE;
892
893
894 --start NISINHA Bug 6490572
895
896 -- cursor to get Model Number and Manufacturer from OKL_K_LINES for loan contracts
897
898 CURSOR c_fa_line_loan(p_clev_fin_id IN okc_k_lines_b.id%type,
899 p_dnz_chr_id IN okc_k_headers_all_b.id%type) IS
900 SELECT kle_fa.model_number,
901 kle_fa.year_built,
902 kle_fa.manufacturer_name
903 FROM okc_line_styles_b lse_fa,
904 okl_k_lines kle_fa,
905 okc_k_lines_b cleb_fa
906 WHERE cleb_fa.id = kle_fa.id
907 AND lse_fa.id = cleb_fa.lse_id
908 AND lse_fa.lty_code = 'FIXED_ASSET'
909 AND cleb_fa.cle_id = p_clev_fin_id
910 AND cleb_fa.dnz_chr_id = p_dnz_chr_id;
911
912 l_fa_line_loan_rec c_fa_line_loan%ROWTYPE;
913
914 --end NISINHA Bug 6490572
915
916
917 BEGIN
918 x_return_status := OKL_API.G_RET_STS_SUCCESS;
919 -- Call start_activity to create savepoint, check compatibility and initialize message list
920 x_return_status := OKL_API.START_ACTIVITY(
921 p_api_name => l_api_name,
922 p_pkg_name => g_pkg_name,
923 p_init_msg_list => p_init_msg_list,
924 l_api_version => l_api_version,
925 p_api_version => p_api_version,
926 p_api_type => g_api_type,
927 x_return_status => x_return_status);
928
929 -- check if activity started successfully
930 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
931 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
932 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
933 RAISE OKL_API.G_EXCEPTION_ERROR;
934 END IF;
935
936 -- gboomina Modified to get deal_type and sts_code which is used as
937 -- input to okl_la_asset_pvt.isContractActive API
938 OPEN c_khr_info(p_chr_id);
939 FETCH c_khr_info INTO l_khr_id, l_deal_type, l_sts_code;
940 IF c_khr_info%NOTFOUND THEN
941 CLOSE c_khr_info;
942 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
943 p_msg_name => G_MISSING_CONTRACT,
944 p_token1 => G_CONTRACT_ID_TOKEN,
945 p_token1_value => to_char(p_chr_id));
946 RAISE OKL_API.G_EXCEPTION_ERROR;
947 END IF;
948 CLOSE c_khr_info;
949
950 -- gboomina added to check whether contract is active or not
951 l_is_contract_active := okl_la_asset_pvt.isContractActive(l_khr_id,
952 l_deal_type,
953 l_sts_code);
954
955 OPEN c_item_line(p_chr_id, p_clev_fin_id);
956 FETCH c_item_line INTO c_item_line_rec;
957 x_las_rec.clev_model_id := c_item_line_rec.clev_model_id;
958 x_las_rec.inventory_item_id := c_item_line_rec.inventory_item_id;
959 x_las_rec.inventory_org_id := c_item_line_rec.inventory_org_id;
960 x_las_rec.inventory_item_name := c_item_line_rec.inventory_item_name;
961 x_las_rec.units := c_item_line_rec.number_of_items;
962 x_las_rec.unit_cost := c_item_line_rec.price_unit;
963 CLOSE c_item_line;
964
965 -- gboomina Modified - Start
966 -- For Active Contract, get transaction values from FA tables
967 -- instead of transaction tables.
968 OPEN c_fa_line(p_chr_id, p_clev_fin_id);
969 FETCH c_fa_line INTO c_fa_line_rec;
970 CLOSE c_fa_line;
971
972 x_las_rec.clev_fa_id := c_fa_line_rec.clev_fa_id;
973 x_las_rec.year_manufactured := c_fa_line_rec.year_built;
974 x_las_rec.asset_id := c_fa_line_rec.asset_id;
975
976 IF (l_is_contract_active) THEN
977 -- Get asset number
978 OPEN c_asset_info(p_clev_fin_id);
979 FETCH c_asset_info INTO l_asset_number;
980 CLOSE c_asset_info;
981
982 -- Get values from FA table if contract is active
983 OPEN c_fa_line_active(l_asset_number);
984 FETCH c_fa_line_active INTO l_fa_line_active_rec;
985 CLOSE c_fa_line_active;
986 x_las_rec.model_number := l_fa_line_active_rec.model_number;
987 x_las_rec.fa_location_id := l_fa_line_active_rec.fa_location_id;
988 x_las_rec.fa_location_name := l_fa_line_active_rec.fa_location_name;
989 x_las_rec.manufacturer_name := l_fa_line_active_rec.manufacturer_name;
990
991 OPEN c_asset_key_active(l_asset_number);
992 FETCH c_asset_key_active INTO l_asset_key_active_rec;
993 CLOSE c_asset_key_active;
994 --Bug# 8652738
995 x_las_rec.asset_key_id := l_asset_key_active_rec.ccid;
996 x_las_rec.asset_key_name := l_asset_key_active_rec.segs;
997
998 -- Get Install Site Name from CSI_ITEM_INSTANCE table
999 OPEN c_install_site_active(p_chr_id, p_clev_fin_id);
1000 FETCH c_install_site_active INTO l_install_site_active_rec;
1001 CLOSE c_install_site_active;
1002 x_las_rec.clev_ib_id := l_install_site_active_rec.clev_ib_id;
1003 x_las_rec.party_site_name := l_install_site_active_rec.party_site_name;
1004
1005 ELSE
1006 x_las_rec.model_number := c_fa_line_rec.model_number;
1007 x_las_rec.fa_location_id := c_fa_line_rec.fa_location_id;
1008 x_las_rec.fa_location_name := c_fa_line_rec.fa_location_name;
1009 x_las_rec.manufacturer_name := c_fa_line_rec.manufacturer_name;
1010 x_las_rec.asset_key_id := c_fa_line_rec.asset_key_id;
1011 x_las_rec.asset_key_name := c_fa_line_rec.asset_key_name;
1012
1013
1014 OPEN c_ib_line(p_chr_id, p_clev_fin_id);
1015 FETCH c_ib_line INTO c_ib_line_rec;
1016 x_las_rec.clev_ib_id := c_ib_line_rec.clev_ib_id;
1017 x_las_rec.party_site_use_id := c_ib_line_rec.party_site_use_id;
1018
1019 IF (c_ib_line_rec.party_site_use_id IS NOT NULL) THEN
1020 OPEN c_install_site(c_ib_line_rec.party_site_use_id);
1021 --Bug# 9721029
1022 FETCH c_install_site INTO l_install_site_rec;
1023 x_las_rec.party_site_name := l_install_site_rec.party_site_name;
1024 CLOSE c_install_site;
1025 END IF;
1026 CLOSE c_ib_line;
1027
1028 END IF;
1029 -- gboomina - End
1030
1031
1032
1033 -- start NISINHA Bug 6490572
1034 IF ( l_deal_type = 'LOAN') THEN
1035 OPEN c_fa_line_loan(p_clev_fin_id, p_chr_id);
1036 FETCH c_fa_line_loan INTO l_fa_line_loan_rec;
1037 CLOSE c_fa_line_loan;
1038 x_las_rec.model_number := l_fa_line_loan_rec.model_number;
1039 x_las_rec.manufacturer_name := l_fa_line_loan_rec.manufacturer_name;
1040 END IF;
1041 --end NISINHA Bug 6490572
1042
1043 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1044
1045 EXCEPTION
1046 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1047 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1048 p_api_name => l_api_name,
1049 p_pkg_name => g_pkg_name,
1050 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1051 x_msg_count => x_msg_count,
1052 x_msg_data => x_msg_data,
1053 p_api_type => g_api_type);
1054
1055 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1056 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1057 p_api_name => l_api_name,
1058 p_pkg_name => g_pkg_name,
1059 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1060 x_msg_count => x_msg_count,
1061 x_msg_data => x_msg_data,
1062 p_api_type => g_api_type);
1063
1064 WHEN OTHERS THEN
1065 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1066 p_api_name => l_api_name,
1067 p_pkg_name => g_pkg_name,
1068 p_exc_name => 'OTHERS',
1069 x_msg_count => x_msg_count,
1070 x_msg_data => x_msg_data,
1071 p_api_type => g_api_type);
1072 END load_all_line;
1073
1074
1075 FUNCTION addon_ship_to_site_name(
1076 p_site_use_id IN NUMBER
1077 )
1078 RETURN VARCHAR2
1079 IS
1080 CURSOR c_party_site_name(p_site_use_id NUMBER) IS
1081 SELECT SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
1082 hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
1083 null, null,null,null,null,null,null,'n','n',80,1,1),1,80) ship_to_site_name
1084 FROM hz_locations hl,
1085 hz_party_sites hps,
1086 hz_cust_acct_sites_all cas,
1087 hz_cust_site_uses_all csu
1088 WHERE csu.site_use_id = p_site_use_id
1089 AND cas.cust_acct_site_id = csu.cust_acct_site_id
1090 AND hps.party_site_id = cas.party_site_id
1091 AND hl.location_id = hps.location_id;
1092
1093 --Bug# 9721029
1094 l_party_site_rec c_party_site_name%ROWTYPE;
1095
1096 BEGIN
1097 OPEN c_party_site_name(p_site_use_id);
1098 FETCH c_party_site_name INTO l_party_site_rec;
1099 CLOSE c_party_site_name;
1100
1101 return l_party_site_rec.ship_to_site_name;
1102
1103 END addon_ship_to_site_name;
1104
1105 PROCEDURE process_line_billing_setup(
1106 p_api_version IN NUMBER,
1107 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1108 x_return_status OUT NOCOPY VARCHAR2,
1109 x_msg_count OUT NOCOPY NUMBER,
1110 x_msg_data OUT NOCOPY VARCHAR2,
1111 p_rgpv_rec IN OKL_DEAL_TERMS_PVT.billing_setup_rec_type,
1112 x_rgpv_rec OUT NOCOPY OKL_DEAL_TERMS_PVT.billing_setup_rec_type) IS
1113
1114 l_api_name VARCHAR2(30) := 'process_line_billing_setup';
1115 l_api_version CONSTANT NUMBER := 1.0;
1116
1117 lp_labill_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
1118 lx_labill_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
1119
1120 lp_lapmth_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1121 lx_lapmth_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1122
1123 lp_labacc_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1124 lx_labacc_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1125
1126 lp_lainvd_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1127 lx_lainvd_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1128
1129 lp_lainpr_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1130 lx_lainpr_rulv_rec Okl_Rule_Pub.rulv_rec_type;
1131
1132 lp_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
1133 lx_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
1134
1135 lp_klev_rec OKL_KLE_PVT.klev_rec_type;
1136 lx_klev_rec OKL_KLE_PVT.klev_rec_type;
1137
1138 BEGIN
1139 x_return_status := OKL_API.START_ACTIVITY(
1140 p_api_name => l_api_name,
1141 p_pkg_name => g_pkg_name,
1142 p_init_msg_list => p_init_msg_list,
1143 l_api_version => l_api_version,
1144 p_api_version => p_api_version,
1145 p_api_type => g_api_type,
1146 x_return_status => x_return_status);
1147
1148 -- check if activity started successfully
1149 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1151 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1152 RAISE OKL_API.G_EXCEPTION_ERROR;
1153 END IF;
1154
1155 IF (p_rgpv_rec.rgp_id IS NULL) THEN
1156 -- Create LABILL rule group
1157 lp_labill_rgpv_rec.id := NULL;
1158 lp_labill_rgpv_rec.rgd_code := 'LABILL';
1159 lp_labill_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1160 lp_labill_rgpv_rec.cle_id := p_rgpv_rec.cle_id;
1161 lp_labill_rgpv_rec.rgp_type := 'KRG';
1162
1163 OKL_RULE_PUB.create_rule_group(
1164 p_api_version => p_api_version,
1165 p_init_msg_list => p_init_msg_list,
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data,
1169 p_rgpv_rec => lp_labill_rgpv_rec,
1170 x_rgpv_rec => lx_labill_rgpv_rec);
1171
1172 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1173 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1174 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1175 RAISE OKC_API.G_EXCEPTION_ERROR;
1176 END IF;
1177
1178 x_rgpv_rec.rgp_id := lx_labill_rgpv_rec.id;
1179 x_rgpv_rec.rgp_labill_lapmth_id := lx_labill_rgpv_rec.id;
1180 x_rgpv_rec.rgp_labill_labacc_id := lx_labill_rgpv_rec.id;
1181
1182 ELSE
1183 -- Update LABILL rule group
1184 lp_labill_rgpv_rec.id := p_rgpv_rec.rgp_id;
1185 lp_labill_rgpv_rec.rgd_code := 'LABILL';
1186 lp_labill_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1187 lp_labill_rgpv_rec.cle_id := p_rgpv_rec.cle_id;
1188 lp_labill_rgpv_rec.rgp_type := 'KRG';
1189
1190 OKL_RULE_PUB.update_rule_group(
1191 p_api_version => p_api_version,
1192 p_init_msg_list => p_init_msg_list,
1193 x_return_status => x_return_status,
1194 x_msg_count => x_msg_count,
1195 x_msg_data => x_msg_data,
1196 p_rgpv_rec => lp_labill_rgpv_rec,
1197 x_rgpv_rec => lx_labill_rgpv_rec);
1198
1199 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1200 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1201 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1202 RAISE OKC_API.G_EXCEPTION_ERROR;
1203 END IF;
1204 x_rgpv_rec.rgp_id := p_rgpv_rec.rgp_id;
1205 END IF;
1206
1207 -- Update Contract line with Bill-To
1208 lp_clev_rec.id := p_rgpv_rec.cle_id;
1209 lp_clev_rec.chr_id := p_rgpv_rec.chr_id;
1210 lp_clev_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1211 lp_clev_rec.bill_to_site_use_id := p_rgpv_rec.bill_to_site_use_id;
1212 lp_klev_rec.id := p_rgpv_rec.cle_id;
1213 OKL_CONTRACT_PUB.update_contract_line(
1214 p_api_version => p_api_version,
1215 p_init_msg_list => p_init_msg_list,
1216 x_return_status => x_return_status,
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 p_clev_rec => lp_clev_rec,
1220 p_klev_rec => lp_klev_rec,
1221 p_edit_mode => 'N',
1222 x_clev_rec => lx_clev_rec,
1223 x_klev_rec => lx_klev_rec);
1224
1225 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1228 RAISE OKC_API.G_EXCEPTION_ERROR;
1229 END IF;
1230
1231 IF (p_rgpv_rec.rul_lapmth_id IS NULL) THEN
1232 -- Create LAPMTH rule
1233 lp_lapmth_rulv_rec.id := NULL;
1234 lp_lapmth_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
1235 lp_lapmth_rulv_rec.rule_information_category := 'LAPMTH';
1236 lp_lapmth_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1237 lp_lapmth_rulv_rec.WARN_YN := 'N';
1238 lp_lapmth_rulv_rec.STD_TEMPLATE_YN := 'N';
1239 IF ((p_rgpv_rec.rul_lapmth_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lapmth_object1_id2 IS NOT NULL)) THEN
1240 lp_lapmth_rulv_rec.object1_id1 := p_rgpv_rec.rul_lapmth_object1_id1;
1241 lp_lapmth_rulv_rec.object1_id2 := p_rgpv_rec.rul_lapmth_object1_id2;
1242 lp_lapmth_rulv_rec.jtot_object1_code := 'OKX_RCPTMTH';
1243 END IF;
1244
1245 OKL_RULE_PUB.create_rule(
1246 p_api_version => p_api_version,
1247 p_init_msg_list => p_init_msg_list,
1248 x_return_status => x_return_status,
1249 x_msg_count => x_msg_count,
1250 x_msg_data => x_msg_data,
1251 p_rulv_rec => lp_lapmth_rulv_rec,
1252 x_rulv_rec => lx_lapmth_rulv_rec);
1253
1254 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1255 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1256 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1257 RAISE OKC_API.G_EXCEPTION_ERROR;
1258 END IF;
1259
1260 x_rgpv_rec.rul_lapmth_id := lx_lapmth_rulv_rec.id;
1261 ELSE
1262 -- update LAPMTH rule
1263 lp_lapmth_rulv_rec.id := p_rgpv_rec.rul_lapmth_id;
1264 lp_lapmth_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
1265 lp_lapmth_rulv_rec.rule_information_category := 'LAPMTH';
1266 lp_lapmth_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1267 lp_lapmth_rulv_rec.WARN_YN := 'N';
1268 lp_lapmth_rulv_rec.STD_TEMPLATE_YN := 'N';
1269 IF ((p_rgpv_rec.rul_lapmth_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lapmth_object1_id2 IS NOT NULL)) THEN
1270 lp_lapmth_rulv_rec.object1_id1 := p_rgpv_rec.rul_lapmth_object1_id1;
1271 lp_lapmth_rulv_rec.object1_id2 := p_rgpv_rec.rul_lapmth_object1_id2;
1272 lp_lapmth_rulv_rec.jtot_object1_code := 'OKX_RCPTMTH';
1273 ELSE
1274 -- Added for bug 9324646
1275 lp_lapmth_rulv_rec.object1_id1 := NULL;
1276 lp_lapmth_rulv_rec.object1_id2 := NULL;
1277 lp_lapmth_rulv_rec.jtot_object1_code := NULL;
1278 END IF;
1279
1280 OKL_RULE_PUB.update_rule(
1281 p_api_version => p_api_version,
1282 p_init_msg_list => p_init_msg_list,
1283 x_return_status => x_return_status,
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data,
1286 p_rulv_rec => lp_lapmth_rulv_rec,
1287 x_rulv_rec => lx_lapmth_rulv_rec);
1288
1289 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1291 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1292 RAISE OKC_API.G_EXCEPTION_ERROR;
1293 END IF;
1294
1295 END IF;
1296
1297 IF (p_rgpv_rec.rul_labacc_id IS NULL) THEN
1298 -- Create LABACC rule
1299 lp_labacc_rulv_rec.id := NULL;
1300 lp_labacc_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
1301 lp_labacc_rulv_rec.rule_information_category := 'LABACC';
1302 lp_labacc_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1303 lp_labacc_rulv_rec.WARN_YN := 'N';
1304 lp_labacc_rulv_rec.STD_TEMPLATE_YN := 'N';
1305 IF ((p_rgpv_rec.rul_labacc_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_labacc_object1_id2 IS NOT NULL)) THEN
1306 lp_labacc_rulv_rec.object1_id1 := p_rgpv_rec.rul_labacc_object1_id1;
1307 lp_labacc_rulv_rec.object1_id2 := p_rgpv_rec.rul_labacc_object1_id2;
1308 lp_labacc_rulv_rec.jtot_object1_code := 'OKX_CUSTBKAC';
1309 END IF;
1310
1311 OKL_RULE_PUB.create_rule(
1312 p_api_version => p_api_version,
1313 p_init_msg_list => p_init_msg_list,
1314 x_return_status => x_return_status,
1315 x_msg_count => x_msg_count,
1316 x_msg_data => x_msg_data,
1317 p_rulv_rec => lp_labacc_rulv_rec,
1318 x_rulv_rec => lx_labacc_rulv_rec);
1319
1320 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1321 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1322 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1323 RAISE OKC_API.G_EXCEPTION_ERROR;
1324 END IF;
1325
1326 x_rgpv_rec.rul_labacc_id := lx_labacc_rulv_rec.id;
1327
1328 ELSE
1329 -- update LABACC rule
1330 lp_labacc_rulv_rec.id := p_rgpv_rec.rul_labacc_id;
1331 lp_labacc_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
1332 lp_labacc_rulv_rec.rule_information_category := 'LABACC';
1333 lp_labacc_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
1334 lp_labacc_rulv_rec.WARN_YN := 'N';
1335 lp_labacc_rulv_rec.STD_TEMPLATE_YN := 'N';
1336 IF ((p_rgpv_rec.rul_labacc_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_labacc_object1_id2 IS NOT NULL)) THEN
1337 lp_labacc_rulv_rec.object1_id1 := p_rgpv_rec.rul_labacc_object1_id1;
1338 lp_labacc_rulv_rec.object1_id2 := p_rgpv_rec.rul_labacc_object1_id2;
1339 lp_labacc_rulv_rec.jtot_object1_code := 'OKX_CUSTBKAC';
1340 ELSE
1341 -- Added for bug 9324646
1342 lp_labacc_rulv_rec.object1_id1 := NULL;
1343 lp_labacc_rulv_rec.object1_id2 := NULL;
1344 lp_labacc_rulv_rec.jtot_object1_code := NULL;
1345 END IF;
1346
1347 OKL_RULE_PUB.update_rule(
1348 p_api_version => p_api_version,
1349 p_init_msg_list => p_init_msg_list,
1350 x_return_status => x_return_status,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data,
1353 p_rulv_rec => lp_labacc_rulv_rec,
1354 x_rulv_rec => lx_labacc_rulv_rec);
1355
1356 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1359 RAISE OKC_API.G_EXCEPTION_ERROR;
1360 END IF;
1361
1362 END IF;
1363
1364
1365 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1366
1367 EXCEPTION
1368 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1369 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1370 p_api_name => l_api_name,
1371 p_pkg_name => g_pkg_name,
1372 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1373 x_msg_count => x_msg_count,
1374 x_msg_data => x_msg_data,
1375 p_api_type => g_api_type);
1376
1377 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1379 p_api_name => l_api_name,
1380 p_pkg_name => g_pkg_name,
1381 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1382 x_msg_count => x_msg_count,
1383 x_msg_data => x_msg_data,
1384 p_api_type => g_api_type);
1385
1386 WHEN OTHERS THEN
1387 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1388 p_api_name => l_api_name,
1389 p_pkg_name => g_pkg_name,
1390 p_exc_name => 'OTHERS',
1391 x_msg_count => x_msg_count,
1392 x_msg_data => x_msg_data,
1393 p_api_type => g_api_type);
1394 END process_line_billing_setup;
1395
1396 PROCEDURE load_line_billing_setup(
1397 p_api_version IN NUMBER,
1398 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1399 x_return_status OUT NOCOPY VARCHAR2,
1400 x_msg_count OUT NOCOPY NUMBER,
1401 x_msg_data OUT NOCOPY VARCHAR2,
1402 p_dnz_chr_id IN NUMBER,
1403 p_cle_id IN NUMBER,
1404 x_billing_setup_rec OUT NOCOPY OKL_DEAL_TERMS_PVT.billing_setup_rec_type) IS
1405
1406 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
1407 l_api_name CONSTANT VARCHAR2(30) := 'load_line_billing_setup';
1408 l_api_version CONSTANT NUMBER := 1.0;
1409
1410 CURSOR c_bill_to(p_cle_id NUMBER) IS
1411 SELECT clet_fin.name asset_number,
1412 clet_fin.item_description,
1413 cleb_fin.bill_to_site_use_id,
1414 csu.location bill_to_site_name
1415 FROM okc_k_lines_b cleb_fin,
1416 hz_cust_site_uses_all csu ,
1417 okc_k_lines_tl clet_fin
1418 WHERE cleb_fin.id = p_cle_id
1419 AND csu.site_use_id = cleb_fin.bill_to_site_use_id
1420 AND clet_fin.id = cleb_fin.id
1421 AND clet_fin.language = userenv('LANG');
1422
1423 CURSOR c_rule(p_chr_id NUMBER, p_cle_id NUMBER, p_rgd_code VARCHAR2, p_rule_info_cat VARCHAR2) IS
1424 SELECT rul.rgp_id,rgp.rgd_code,rul.ID,rul.object1_id1,rul.object1_id2,rul.rule_information1,rul.rule_information2,
1425 rul.rule_information3, rul.rule_information4
1426 FROM okc_rules_b rul,
1427 okc_rule_groups_b rgp
1428 WHERE rgp.dnz_chr_id = p_chr_id
1429 AND rgp.cle_id = p_cle_id
1430 AND rgp.rgd_code = p_rgd_code
1431 AND rgp.id = rul.rgp_id
1432 AND rgp.dnz_chr_id = rul.dnz_chr_id
1433 AND rul.rule_information_category = p_rule_info_cat;
1434
1435 l_rule c_rule%ROWTYPE;
1436
1437 CURSOR c_payment_method(p_object1_id1 NUMBER) IS
1438 SELECT name
1439 FROM okx_receipt_methods_v
1440 WHERE id1 = p_object1_id1;
1441
1442 CURSOR c_bank_info(p_object1_id1 NUMBER) IS
1443 SELECT description name,bank bank_name
1444 FROM okx_rcpt_method_accounts_v
1445 WHERE id1 = p_object1_id1;
1446 BEGIN
1447 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1448 -- Call start_activity to create savepoint, check compatibility
1449 -- and initialize message list
1450 x_return_status := OKL_API.START_ACTIVITY (
1451 l_api_name
1452 ,p_init_msg_list
1453 ,'_PVT'
1454 ,x_return_status);
1455 -- Check if activity started successfully
1456 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1457 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1458 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1459 RAISE OKL_API.G_EXCEPTION_ERROR;
1460 END IF;
1461
1462 x_billing_setup_rec.chr_id := p_dnz_chr_id;
1463 x_billing_setup_rec.cle_id := p_cle_id;
1464
1465 OPEN c_bill_to(p_cle_id);
1466 FETCH c_bill_to INTO x_billing_setup_rec.asset_number,x_billing_setup_rec.item_description,
1467 x_billing_setup_rec.bill_to_site_use_id, x_billing_setup_rec.bill_to_site_name;
1468 CLOSE c_bill_to;
1469
1470 OPEN c_rule(p_dnz_chr_id, p_cle_id, 'LABILL', 'LAPMTH');
1471 FETCH c_rule INTO l_rule;
1472 x_billing_setup_rec.rgp_id := l_rule.rgp_id;
1473 x_billing_setup_rec.rgp_labill_lapmth_id := l_rule.rgp_id;
1474 x_billing_setup_rec.rgp_labill_labacc_id := l_rule.rgp_id;
1475 x_billing_setup_rec.rul_lapmth_id := l_rule.id;
1476 x_billing_setup_rec.rul_lapmth_object1_id1 := l_rule.object1_id1;
1477 x_billing_setup_rec.rul_lapmth_object1_id2 := l_rule.object1_id2;
1478 CLOSE c_rule;
1479
1480 IF (x_billing_setup_rec.rul_lapmth_object1_id1 IS NOT NULL) THEN
1481 OPEN c_payment_method(x_billing_setup_rec.rul_lapmth_object1_id1);
1482 FETCH c_payment_method INTO x_billing_setup_rec.rul_lapmth_name;
1483 CLOSE c_payment_method;
1484 END IF;
1485
1486 OPEN c_rule(p_dnz_chr_id, p_cle_id, 'LABILL', 'LABACC');
1487 FETCH c_rule INTO l_rule;
1488 x_billing_setup_rec.rul_labacc_id := l_rule.id;
1489 x_billing_setup_rec.rul_labacc_object1_id1 := l_rule.object1_id1;
1490 x_billing_setup_rec.rul_labacc_object1_id2 := l_rule.object1_id2;
1491 CLOSE c_rule;
1492
1493 IF (x_billing_setup_rec.rul_labacc_object1_id1 IS NOT NULL) THEN
1494 OPEN c_bank_info(x_billing_setup_rec.rul_labacc_object1_id1);
1495 FETCH c_bank_info INTO x_billing_setup_rec.rul_labacc_name,x_billing_setup_rec.rul_labacc_bank_name;
1496 CLOSE c_bank_info;
1497 END IF;
1498
1499 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1500
1501 EXCEPTION
1502 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1503 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1504 l_api_name,
1505 G_PKG_NAME,
1506 'OKL_API.G_RET_STS_ERROR',
1507 x_msg_count,
1508 x_msg_data,
1509 '_PVT');
1510 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1511 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1512 l_api_name,
1513 G_PKG_NAME,
1514 'OKL_API.G_RET_STS_UNEXP_ERROR',
1515 x_msg_count,
1516 x_msg_data,
1517 '_PVT');
1518 WHEN OTHERS THEN
1519 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1520 l_api_name,
1521 G_PKG_NAME,
1522 'OTHERS',
1523 x_msg_count,
1524 x_msg_data,
1525 '_PVT');
1526 END load_line_billing_setup;
1527
1528 PROCEDURE create_assetaddon_line(
1529 p_api_version IN NUMBER,
1530 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1531 x_return_status OUT NOCOPY VARCHAR2,
1532 x_msg_count OUT NOCOPY NUMBER,
1533 x_msg_data OUT NOCOPY VARCHAR2,
1534 p_addon_rec IN addon_rec_type,
1535 x_addon_rec OUT NOCOPY addon_rec_type) IS
1536 BEGIN
1537 null;
1538 END create_assetaddon_line;
1539
1540 PROCEDURE create_assetaddon_line(
1541 p_api_version IN NUMBER,
1542 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1543 x_return_status OUT NOCOPY VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 p_addon_tbl IN addon_tbl_type,
1547 x_addon_tbl OUT NOCOPY addon_tbl_type) IS
1548 BEGIN
1549 null;
1550 END create_assetaddon_line;
1551
1552 PROCEDURE update_assetaddon_line(
1553 p_api_version IN NUMBER,
1554 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1555 x_return_status OUT NOCOPY VARCHAR2,
1556 x_msg_count OUT NOCOPY NUMBER,
1557 x_msg_data OUT NOCOPY VARCHAR2,
1558 p_addon_rec IN addon_rec_type,
1559 x_addon_rec OUT NOCOPY addon_rec_type) IS
1560 BEGIN
1561 null;
1562 END update_assetaddon_line;
1563
1564 PROCEDURE update_assetaddon_line(
1565 p_api_version IN NUMBER,
1566 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1567 x_return_status OUT NOCOPY VARCHAR2,
1568 x_msg_count OUT NOCOPY NUMBER,
1569 x_msg_data OUT NOCOPY VARCHAR2,
1570 p_addon_tbl IN addon_tbl_type,
1571 x_addon_tbl OUT NOCOPY addon_tbl_type) IS
1572 BEGIN
1573 null;
1574 END update_assetaddon_line;
1575
1576 PROCEDURE allocate_amount_tradein (
1577 p_api_version IN NUMBER,
1578 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1579 x_return_status OUT NOCOPY VARCHAR2,
1580 x_msg_count OUT NOCOPY NUMBER,
1581 x_msg_data OUT NOCOPY VARCHAR2,
1582 p_chr_id IN NUMBER,
1583 p_tradein_amount IN NUMBER,
1584 p_mode IN VARCHAR2,
1585 x_tradein_tbl OUT NOCOPY tradein_tbl_type) IS
1586
1587
1588 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount_tradein';
1589 l_api_version CONSTANT NUMBER := 1.0;
1590
1591 CURSOR c_assets IS
1592 SELECT cleb_fin.id fin_asset_id,
1593 clet_fin.name asset_number,
1594 clet_fin.item_description description,
1595 NVL(kle_fin.oec,0) oec
1596 FROM okc_k_lines_b cleb_fin,
1597 okc_k_lines_tl clet_fin,
1598 okl_k_lines kle_fin,
1599 okc_line_styles_b lse_fin,
1600 okc_statuses_b sts
1601 WHERE cleb_fin.dnz_chr_id = p_chr_id
1602 AND cleb_fin.chr_id = p_chr_id
1603 AND clet_fin.id = cleb_fin.id
1604 AND clet_fin.language = USERENV('LANG')
1605 AND cleb_fin.id = kle_fin.id
1606 AND lse_fin.id = cleb_fin.lse_id
1607 AND lse_fin.lty_code = 'FREE_FORM1'
1608 AND cleb_fin.sts_code = sts.code
1609 AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
1610 AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
1611
1612 CURSOR c_new_assets IS
1613 SELECT cleb_fin.id fin_asset_id,
1614 clet_fin.name asset_number,
1615 clet_fin.item_description description,
1616 NVL(kle_fin.oec,0) oec
1617 FROM okc_k_lines_b cleb_fin,
1618 okc_k_lines_tl clet_fin,
1619 okl_k_lines kle_fin,
1620 okc_line_styles_b lse_fin,
1621 okc_statuses_b sts
1622 WHERE cleb_fin.dnz_chr_id = p_chr_id
1623 AND cleb_fin.chr_id = p_chr_id
1624 AND clet_fin.id = cleb_fin.id
1625 AND clet_fin.language = USERENV('LANG')
1626 AND cleb_fin.id = kle_fin.id
1627 AND lse_fin.id = cleb_fin.lse_id
1628 AND lse_fin.lty_code = 'FREE_FORM1'
1629 AND cleb_fin.sts_code = sts.code
1630 AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
1631 AND kle_fin.tradein_amount IS NULL
1632 AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
1633
1634 CURSOR c_term_fin_lines(p_chr_id IN NUMBER) is
1635 SELECT SUM(NVL(kle_fin.tradein_amount,0)) amount
1636 FROM okc_k_lines_b cleb_fin,
1637 okl_k_lines kle_fin,
1638 okc_line_styles_b lse_fin
1639 WHERE cleb_fin.dnz_chr_id = p_chr_id
1640 AND cleb_fin.chr_id = p_chr_id
1641 AND cleb_fin.sts_code = 'TERMINATED'
1642 AND kle_fin.id = cleb_fin.id
1643 AND lse_fin.id = cleb_fin.id
1644 AND lse_fin.lty_code = 'FREE_FORM1';
1645
1646 l_term_lines_tradein_amt NUMBER;
1647 i NUMBER := 0;
1648 l_chr_id OKC_K_HEADERS_B.id%TYPE;
1649 l_tradein_amount NUMBER := 0;
1650 l_oec_total NUMBER := 0;
1651 l_assoc_amount NUMBER;
1652 l_assoc_total NUMBER := 0;
1653 l_diff NUMBER;
1654 l_currency_code OKC_K_HEADERS_B.currency_code%TYPE;
1655 l_asset_tbl asset_tbl_type;
1656 l_tradein_tbl tradein_tbl_type;
1657
1658 BEGIN
1659
1660 -- call START_ACTIVITY to create savepoint, check compatibility
1661 -- and initialize message list
1662 x_return_status := OKC_API.START_ACTIVITY(
1663 p_api_name => l_api_name,
1664 p_pkg_name => g_pkg_name,
1665 p_init_msg_list => p_init_msg_list,
1666 l_api_version => l_api_version,
1667 p_api_version => p_api_version,
1668 p_api_type => g_api_type,
1669 x_return_status => x_return_status);
1670
1671 -- check if activity started successfully
1672 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1673 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1675 raise OKC_API.G_EXCEPTION_ERROR;
1676 End If;
1677
1678 l_chr_id := p_chr_id;
1679 If okl_context.get_okc_org_id is null then
1680 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1681 End If;
1682
1683 l_tradein_amount := p_tradein_amount;
1684
1685 i := 0;
1686 IF p_mode = 'CREATE' THEN
1687
1688 FOR l_asset IN c_new_assets LOOP
1689 i := i + 1;
1690
1691 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
1692 l_asset_tbl(i).asset_number := l_asset.asset_number;
1693 l_asset_tbl(i).description := l_asset.description;
1694 l_asset_tbl(i).oec := l_asset.oec;
1695 END LOOP;
1696
1697 ELSIF p_mode = 'UPDATE' THEN
1698
1699 FOR l_asset IN c_assets LOOP
1700 i := i + 1;
1701
1702 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
1703 l_asset_tbl(i).asset_number := l_asset.asset_number;
1704 l_asset_tbl(i).description := l_asset.description;
1705 l_asset_tbl(i).oec := l_asset.oec;
1706 END LOOP;
1707
1708 -- Exclude Terminated line tradein amounts from
1709 -- total amount available for allocation
1710 l_term_lines_tradein_amt := 0;
1711 OPEN c_term_fin_lines(p_chr_id => l_chr_id);
1712 FETCH c_term_fin_lines INTO l_term_lines_tradein_amt;
1713 CLOSE c_term_fin_lines;
1714
1715 l_tradein_amount := l_tradein_amount - NVL(l_term_lines_tradein_amt,0);
1716
1717 IF l_tradein_amount < 0 THEN
1718 OKL_API.SET_MESSAGE(p_app_name => g_app_name
1719 ,p_msg_name => 'OKL_LA_NEGATIVE_TRADEIN_AMT'
1720 ,p_token1 => 'AMOUNT'
1721 ,p_token1_value => TO_CHAR(NVL(l_term_lines_tradein_amt,0)));
1722 RAISE OKL_API.G_EXCEPTION_ERROR;
1723 END IF;
1724
1725 END IF;
1726
1727 IF (l_asset_tbl.COUNT > 0) THEN
1728
1729 ------------------------------------------------------------------
1730 -- 1. Loop through to get OEC total of all assets being associated
1731 ------------------------------------------------------------------
1732 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
1733
1734 IF l_asset_tbl.EXISTS(i) THEN
1735 l_oec_total := l_oec_total + l_asset_tbl(i).oec;
1736 END IF;
1737
1738 END LOOP;
1739
1740 SELECT currency_code
1741 INTO l_currency_code
1742 FROM okc_k_headers_b
1743 WHERE id = l_chr_id;
1744
1745 ----------------------------------------------------------------------------
1746 -- 2. Loop through to determine associated amounts and round off the amounts
1747 ----------------------------------------------------------------------------
1748 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
1749
1750 IF l_asset_tbl.EXISTS(i) THEN
1751
1752 IF l_asset_tbl.COUNT = 1 THEN
1753
1754 l_assoc_amount := l_tradein_amount;
1755
1756 ELSE
1757
1758 l_assoc_amount := l_tradein_amount * l_asset_tbl(i).oec / l_oec_total;
1759
1760 END IF;
1761
1762 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
1763 p_currency_code => l_currency_code);
1764
1765 l_assoc_total := l_assoc_total + l_assoc_amount;
1766
1767 l_tradein_tbl(i).cleb_fin_id := l_asset_tbl(i).fin_asset_id;
1768 l_tradein_tbl(i).dnz_chr_id := l_chr_id;
1769 l_tradein_tbl(i).asset_number := l_asset_tbl(i).asset_number;
1770 l_tradein_tbl(i).asset_cost := l_asset_tbl(i).oec;
1771 l_tradein_tbl(i).description := l_asset_tbl(i).description;
1772 l_tradein_tbl(i).tradein_amount := l_assoc_amount;
1773
1774 END IF;
1775
1776 END LOOP;
1777
1778 ----------------------------------------------------------------------------------------------------
1779 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
1780 ----------------------------------------------------------------------------------------------------
1781 IF l_assoc_total <> l_tradein_amount THEN
1782
1783 l_diff := l_tradein_amount - l_assoc_total;
1784
1785 l_tradein_tbl(l_tradein_tbl.FIRST).tradein_amount := l_tradein_tbl(l_tradein_tbl.FIRST).tradein_amount + l_diff;
1786
1787 END IF;
1788
1789 END IF;
1790
1791 x_tradein_tbl := l_tradein_tbl;
1792
1793 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1794
1795 EXCEPTION
1796
1797 when OKC_API.G_EXCEPTION_ERROR then
1798 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1799 p_api_name => l_api_name,
1800 p_pkg_name => g_pkg_name,
1801 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1802 x_msg_count => x_msg_count,
1803 x_msg_data => x_msg_data,
1804 p_api_type => g_api_type);
1805
1806 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1807 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1808 p_api_name => l_api_name,
1809 p_pkg_name => g_pkg_name,
1810 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1811 x_msg_count => x_msg_count,
1812 x_msg_data => x_msg_data,
1813 p_api_type => g_api_type);
1814
1815 when OTHERS then
1816 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1817 p_api_name => l_api_name,
1818 p_pkg_name => g_pkg_name,
1819 p_exc_name => 'OTHERS',
1820 x_msg_count => x_msg_count,
1821 x_msg_data => x_msg_data,
1822 p_api_type => g_api_type);
1823
1824 END allocate_amount_tradein;
1825
1826 PROCEDURE allocate_amount_down_payment (
1827 p_api_version IN NUMBER,
1828 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_msg_count OUT NOCOPY NUMBER,
1831 x_msg_data OUT NOCOPY VARCHAR2,
1832 p_chr_id IN NUMBER,
1833 p_down_payment IN NUMBER,
1834 p_basis IN VARCHAR2,
1835 p_mode IN VARCHAR2,
1836 x_down_payment_tbl OUT NOCOPY down_payment_tbl_type) IS
1837
1838 l_api_name CONSTANT VARCHAR2(30) := 'allocate_amount_down_pymt';
1839 l_api_version CONSTANT NUMBER := 1.0;
1840
1841 CURSOR c_assets IS
1842 SELECT cleb_fin.id fin_asset_id,
1843 clet_fin.name asset_number,
1844 clet_fin.item_description description,
1845 NVL(kle_fin.oec,0) oec,
1846 NVL(kle_fin.capitalize_down_payment_yn,'Y') capitalize_yn,
1847 NVL(kle_fin.down_payment_receiver_code,'LESSOR') receiver_code
1848 FROM okc_k_lines_b cleb_fin,
1849 okc_k_lines_tl clet_fin,
1850 okl_k_lines kle_fin,
1851 okc_line_styles_b lse_fin,
1852 okc_statuses_b sts
1853 WHERE cleb_fin.dnz_chr_id = p_chr_id
1854 AND cleb_fin.chr_id = p_chr_id
1855 AND clet_fin.id = cleb_fin.id
1856 AND clet_fin.language = USERENV('LANG')
1857 AND cleb_fin.id = kle_fin.id
1858 AND lse_fin.id = cleb_fin.lse_id
1859 AND lse_fin.lty_code = 'FREE_FORM1'
1860 AND cleb_fin.sts_code = sts.code
1861 AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
1862 AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
1863
1864 CURSOR c_new_assets IS
1865 SELECT cleb_fin.id fin_asset_id,
1866 clet_fin.name asset_number,
1867 clet_fin.item_description description,
1868 NVL(kle_fin.oec,0) oec,
1869 NVL(kle_fin.capitalize_down_payment_yn,'Y') capitalize_yn,
1870 NVL(kle_fin.down_payment_receiver_code,'LESSOR') receiver_code
1871 FROM okc_k_lines_b cleb_fin,
1872 okc_k_lines_tl clet_fin,
1873 okl_k_lines kle_fin,
1874 okc_line_styles_b lse_fin,
1875 okc_statuses_b sts
1876 WHERE cleb_fin.dnz_chr_id = p_chr_id
1877 AND cleb_fin.chr_id = p_chr_id
1878 AND clet_fin.id = cleb_fin.id
1879 AND clet_fin.language = USERENV('LANG')
1880 AND cleb_fin.id = kle_fin.id
1881 AND lse_fin.id = cleb_fin.lse_id
1882 AND lse_fin.lty_code = 'FREE_FORM1'
1883 AND cleb_fin.sts_code = sts.code
1884 AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
1885 AND (kle_fin.capital_reduction IS NULL AND
1886 kle_fin.capital_reduction_percent IS NULL)
1887 AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
1888
1889 CURSOR c_term_fin_lines(p_chr_id IN NUMBER) is
1890 SELECT SUM( NVL(kle_fin.capital_reduction,0) +
1891 (NVL(kle_fin.capital_reduction_percent,0)/100 * kle_fin.oec)) amount
1892 FROM okc_k_lines_b cleb_fin,
1893 okl_k_lines kle_fin,
1894 okc_line_styles_b lse_fin
1895 WHERE cleb_fin.dnz_chr_id = p_chr_id
1896 AND cleb_fin.chr_id = p_chr_id
1897 AND cleb_fin.sts_code = 'TERMINATED'
1898 AND kle_fin.id = cleb_fin.id
1899 AND lse_fin.id = cleb_fin.id
1900 AND lse_fin.lty_code = 'FREE_FORM1';
1901
1902 l_term_lines_down_pymt_amt NUMBER;
1903 i NUMBER := 0;
1904 l_chr_id OKC_K_HEADERS_B.id%TYPE;
1905 l_down_payment NUMBER := 0;
1906 l_basis FND_LOOKUPS.lookup_code%TYPE;
1907 l_oec_total NUMBER := 0;
1908 l_assoc_amount NUMBER;
1909 l_assoc_total NUMBER := 0;
1910 l_diff NUMBER;
1911 l_currency_code OKC_K_HEADERS_B.currency_code%TYPE;
1912 l_asset_tbl asset_tbl_type;
1913 l_down_payment_tbl down_payment_tbl_type;
1914
1915 BEGIN
1916
1917 -- call START_ACTIVITY to create savepoint, check compatibility
1918 -- and initialize message list
1919 x_return_status := OKC_API.START_ACTIVITY(
1920 p_api_name => l_api_name,
1921 p_pkg_name => g_pkg_name,
1922 p_init_msg_list => p_init_msg_list,
1923 l_api_version => l_api_version,
1924 p_api_version => p_api_version,
1925 p_api_type => g_api_type,
1926 x_return_status => x_return_status);
1927
1928 -- check if activity started successfully
1929 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1930 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1931 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
1932 raise OKC_API.G_EXCEPTION_ERROR;
1933 End If;
1934
1935 l_chr_id := p_chr_id;
1936 If okl_context.get_okc_org_id is null then
1937 okl_context.set_okc_org_context(p_chr_id => l_chr_id );
1938 End If;
1939
1940 l_down_payment := p_down_payment;
1941 l_basis := p_basis;
1942
1943 i := 0;
1944 IF p_mode = 'CREATE' THEN
1945
1946 FOR l_asset IN c_new_assets LOOP
1947 i := i + 1;
1948
1949 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
1950 l_asset_tbl(i).asset_number := l_asset.asset_number;
1951 l_asset_tbl(i).description := l_asset.description;
1952 l_asset_tbl(i).oec := l_asset.oec;
1953 l_asset_tbl(i).capitalize_yn := l_asset.capitalize_yn;
1954 l_asset_tbl(i).receiver_code := l_asset.receiver_code;
1955 END LOOP;
1956
1957 ELSIF p_mode = 'UPDATE' THEN
1958
1959 FOR l_asset IN c_assets LOOP
1960 i := i + 1;
1961
1962 l_asset_tbl(i).fin_asset_id := l_asset.fin_asset_id;
1963 l_asset_tbl(i).asset_number := l_asset.asset_number;
1964 l_asset_tbl(i).description := l_asset.description;
1965 l_asset_tbl(i).oec := l_asset.oec;
1966 l_asset_tbl(i).capitalize_yn := l_asset.capitalize_yn;
1967 l_asset_tbl(i).receiver_code := l_asset.receiver_code;
1968 END LOOP;
1969
1970 -- Exclude Terminated line downpayment amounts from
1971 -- total amount available for allocation
1972 l_term_lines_down_pymt_amt := 0;
1973 OPEN c_term_fin_lines(p_chr_id => l_chr_id);
1974 FETCH c_term_fin_lines INTO l_term_lines_down_pymt_amt;
1975 CLOSE c_term_fin_lines;
1976
1977 IF l_basis = 'FIXED' THEN
1978 l_down_payment := l_down_payment - NVL(l_term_lines_down_pymt_amt,0);
1979
1980 IF l_down_payment < 0 THEN
1981 OKL_API.SET_MESSAGE(p_app_name => g_app_name
1982 ,p_msg_name => 'OKL_LA_NEGATIVE_DOWNPYMT_AMT'
1983 ,p_token1 => 'AMOUNT'
1984 ,p_token1_value => TO_CHAR(NVL(l_term_lines_down_pymt_amt,0)));
1985 RAISE OKL_API.G_EXCEPTION_ERROR;
1986 END IF;
1987 END IF;
1988
1989 END IF;
1990
1991 IF (l_asset_tbl.COUNT > 0) THEN
1992
1993 ------------------------------------------------------------------
1994 -- 1. Loop through to get OEC total of all assets being associated
1995 ------------------------------------------------------------------
1996 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
1997
1998 IF l_asset_tbl.EXISTS(i) THEN
1999 l_oec_total := l_oec_total + l_asset_tbl(i).oec;
2000 END IF;
2001
2002 END LOOP;
2003
2004 SELECT currency_code
2005 INTO l_currency_code
2006 FROM okc_k_headers_b
2007 WHERE id = l_chr_id;
2008
2009 ----------------------------------------------------------------------------
2010 -- 2. Loop through to determine associated amounts and round off the amounts
2011 ----------------------------------------------------------------------------
2012 FOR i IN l_asset_tbl.FIRST .. l_asset_tbl.LAST LOOP
2013
2014 IF l_asset_tbl.EXISTS(i) THEN
2015
2016 IF l_asset_tbl.COUNT = 1 THEN
2017
2018 l_assoc_amount := l_down_payment;
2019
2020 ELSE
2021
2022 IF l_basis = 'ASSET_COST' THEN
2023 l_assoc_amount := l_down_payment;
2024
2025 ELSIF l_basis = 'FIXED' THEN
2026 l_assoc_amount := l_down_payment * l_asset_tbl(i).oec / l_oec_total;
2027
2028 l_assoc_amount := okl_accounting_util.round_amount(p_amount => l_assoc_amount,
2029 p_currency_code => l_currency_code);
2030 END IF;
2031 END IF;
2032
2033 l_assoc_total := l_assoc_total + l_assoc_amount;
2034
2035 l_down_payment_tbl(i).cleb_fin_id := l_asset_tbl(i).fin_asset_id;
2036 l_down_payment_tbl(i).dnz_chr_id := l_chr_id;
2037 l_down_payment_tbl(i).asset_number := l_asset_tbl(i).asset_number;
2038 l_down_payment_tbl(i).asset_cost := l_asset_tbl(i).oec;
2039 l_down_payment_tbl(i).description := l_asset_tbl(i).description;
2040 l_down_payment_tbl(i).basis := l_basis;
2041 l_down_payment_tbl(i).down_payment := l_assoc_amount;
2042 l_down_payment_tbl(i).down_payment_receiver_code := l_asset_tbl(i).receiver_code;
2043 l_down_payment_tbl(i).capitalize_down_payment_yn := l_asset_tbl(i).capitalize_yn;
2044 END IF;
2045
2046 END LOOP;
2047
2048 ----------------------------------------------------------------------------------------------------
2049 -- 3. Adjust associated amount if associated total does not tally up with line amount after rounding
2050 ----------------------------------------------------------------------------------------------------
2051 IF l_basis = 'FIXED' THEN
2052 IF l_assoc_total <> l_down_payment THEN
2053
2054 l_diff := l_down_payment - l_assoc_total;
2055
2056 l_down_payment_tbl(l_down_payment_tbl.FIRST).down_payment := l_down_payment_tbl(l_down_payment_tbl.FIRST).down_payment + l_diff;
2057
2058 END IF;
2059 END IF;
2060
2061 END IF;
2062
2063 x_down_payment_tbl := l_down_payment_tbl;
2064
2065 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
2066
2067 EXCEPTION
2068
2069 when OKC_API.G_EXCEPTION_ERROR then
2070 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2071 p_api_name => l_api_name,
2072 p_pkg_name => g_pkg_name,
2073 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2074 x_msg_count => x_msg_count,
2075 x_msg_data => x_msg_data,
2076 p_api_type => g_api_type);
2077
2078 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2079 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2080 p_api_name => l_api_name,
2081 p_pkg_name => g_pkg_name,
2082 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2083 x_msg_count => x_msg_count,
2084 x_msg_data => x_msg_data,
2085 p_api_type => g_api_type);
2086
2087 when OTHERS then
2088 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2089 p_api_name => l_api_name,
2090 p_pkg_name => g_pkg_name,
2091 p_exc_name => 'OTHERS',
2092 x_msg_count => x_msg_count,
2093 x_msg_data => x_msg_data,
2094 p_api_type => g_api_type);
2095
2096 END allocate_amount_down_payment;
2097
2098 FUNCTION get_subsidy_amount(
2099 p_khr_id IN NUMBER,
2100 p_subsidy_id IN NUMBER)
2101 RETURN VARCHAR2
2102 IS
2103 CURSOR c_subsidy_amount(p_khr_id NUMBER, p_subsidy_id NUMBER) IS
2104 SELECT OKL_ACCOUNTING_UTIL.format_amount(SUM(NVL(kle_sub.subsidy_override_amount,kle_sub.amount)), cleb_sub.currency_code) subsidy_amount
2105 FROM okl_k_lines kle_sub,
2106 okc_k_lines_b cleb_sub
2107 WHERE kle_sub.subsidy_id = p_subsidy_id
2108 AND cleb_sub.dnz_chr_id = p_khr_id
2109 AND cleb_sub.id = kle_sub.id
2110 AND cleb_sub.sts_code <> 'ABANDONED'
2111 GROUP BY cleb_sub.currency_code;
2112
2113 l_subsidy_fmt_amount VARCHAR2(100);
2114 BEGIN
2115 OPEN c_subsidy_amount(p_khr_id, p_subsidy_id);
2116 FETCH c_subsidy_amount into l_subsidy_fmt_amount;
2117 CLOSE c_subsidy_amount;
2118
2119 return l_subsidy_fmt_amount;
2120
2121 END get_subsidy_amount;
2122
2123 FUNCTION get_down_payment_amount(
2124 p_khr_id IN NUMBER)
2125 RETURN VARCHAR2
2126 IS
2127 CURSOR c_down_payment_amount(p_khr_id NUMBER) IS
2128 SELECT OKL_ACCOUNTING_UTIL.format_amount(SUM( NVL(kle_fin.capital_reduction,0) + (NVL(kle_fin.capital_reduction_percent,0)/100 * kle_fin.oec) ), cleb_fin.currency_code) down_payment_amount
2129 FROM okl_k_lines kle_fin,
2130 okc_k_lines_b cleb_fin
2131 WHERE cleb_fin.chr_id = p_khr_id
2132 AND cleb_fin.dnz_chr_id = p_khr_id
2133 AND kle_fin.id = cleb_fin.id
2134 AND (kle_fin.capital_reduction_percent IS NOT NULL OR
2135 kle_fin.capital_reduction IS NOT NULL)
2136 GROUP BY cleb_fin.currency_code;
2137
2138 l_down_payment_amount VARCHAR2(100);
2139 BEGIN
2140 OPEN c_down_payment_amount(p_khr_id);
2141 FETCH c_down_payment_amount into l_down_payment_amount;
2142 CLOSE c_down_payment_amount;
2143
2144 return l_down_payment_amount;
2145
2146 END get_down_payment_amount;
2147
2148 End OKL_DEAL_ASSET_PVT;