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