[Home] [Help]
PACKAGE BODY: APPS.OKL_SPLIT_ASSET_COMP_PVT
Source
1 PACKAGE BODY OKL_SPLIT_ASSET_COMP_PVT AS
2 /* $Header: OKLRSACB.pls 120.6 2006/06/07 00:02:26 fmiao noship $ */
3
4 /*
5 -- mvasudev, 08/23/2004
6 -- Added Constants to enable Business Event
7 */
8 G_WF_EVT_KHR_SPLIT_ASSET_REQ CONSTANT VARCHAR2(69) := 'oracle.apps.okl.la.lease_contract.split_asset_by_components_requested';
9 G_WF_EVT_KHR_SPLIT_ASSET_COMP CONSTANT VARCHAR2(69) := 'oracle.apps.okl.la.lease_contract.split_asset_by_components_completed';
10
11 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(20) := 'CONTRACT_ID';
12 G_WF_ITM_ASSET_ID CONSTANT VARCHAR2(10) := 'ASSET_ID';
13 --Bug 4047504: increased size
14 G_WF_ITM_TRANS_DATE CONSTANT VARCHAR2(20) := 'TRANSACTION_DATE';
15
16 --------------------------------------------------------------------------
17 ----- Calculates Unit and Cost based on current image
18 --------------------------------------------------------------------------
19 PROCEDURE calculate_unit_cost(p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_tal_id IN NUMBER)
25 IS
26
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_api_name CONSTANT VARCHAR2(30) := 'calculate_unit_cost';
29 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
30 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
31 l_id okl_txd_assets_B.ID%TYPE;
32 l_split_percent okl_txd_assets_B.SPLIT_PERCENT%TYPE;
33 l_oec okl_txl_assets_B.ORIGINAL_COST%TYPE;
34 l_units okl_txl_assets_B.CURRENT_UNITS%TYPE;
35 l_asdv_rec Okl_Asd_Pvt.asdv_rec_type;
36 x_asdv_rec Okl_Asd_Pvt.asdv_rec_type;
37
38 --
39 -- need to get unit, cost to calcuate the new unit and cost!!
40 --
41 CURSOR c(p_tal_id NUMBER)
42 IS
43 SELECT a.id,
44 a.SPLIT_PERCENT
45 FROM okl_txd_assets_v a
46 WHERE TAL_ID = p_tal_id
47 ;
48
49 CURSOR c_org(p_tal_id NUMBER)
50 IS
51 SELECT ORIGINAL_COST,
52 CURRENT_UNITS
53 FROM okl_txl_assets_b
54 WHERE id = p_tal_id
55 ;
56
57 BEGIN
58 -- Set API savepoint
59 SAVEPOINT calculate_unit_cost_pvt;
60
61 -- Check for call compatibility
62 IF (NOT FND_API.Compatible_API_Call (l_api_version,
63 p_api_version,
64 l_api_name,
65 G_PKG_NAME ))
66 THEN
67 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize message list if requested
71 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74
75 -- Initialize API status to success
76 x_return_status := OKL_API.G_RET_STS_SUCCESS;
77
78
79 /*** Begin API body ****************************************************/
80
81 --
82 -- get the original units and cost
83 --
84 OPEN c_org (p_tal_id);
85 FETCH c_org INTO l_oec,
86 l_units;
87 CLOSE c_org;
88
89 --
90 -- update all
91 --
92 OPEN c (p_tal_id);
93 LOOP
94
95 FETCH c INTO l_id,
96 l_split_percent;
97
98 EXIT WHEN c%NOTFOUND;
99
100 l_asdv_rec.id := l_id;
101 l_asdv_rec.cost := l_oec * (l_split_percent/100);
102 l_asdv_rec.quantity := l_units;
103
104 Okl_Asd_Pvt.update_row(p_api_version,
105 p_init_msg_list,
106 x_return_status,
107 x_msg_count,
108 x_msg_data,
109 l_asdv_rec,
110 x_asdv_rec);
111
112 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
114 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
115 RAISE OKL_API.G_EXCEPTION_ERROR;
116 END IF;
117
118 END LOOP;
119 CLOSE c;
120
121 /*** End API body ******************************************************/
122
123 -- Get message count and if count is 1, get message info
124 FND_MSG_PUB.Count_And_Get
125 (p_count => x_msg_count,
126 p_data => x_msg_data);
127
128 EXCEPTION
129 WHEN OKL_API.G_EXCEPTION_ERROR THEN
130 ROLLBACK TO calculate_unit_cost_pvt;
131 x_return_status := OKL_API.G_RET_STS_ERROR;
132 FND_MSG_PUB.Count_And_Get
133 (p_count => x_msg_count,
134 p_data => x_msg_data);
135
136 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
137 ROLLBACK TO calculate_unit_cost_pvt;
138 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
139 FND_MSG_PUB.Count_And_Get
140 (p_count => x_msg_count,
141 p_data => x_msg_data);
142
143 WHEN OTHERS THEN
144 ROLLBACK TO calculate_unit_cost_pvt;
145 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
146 OKL_API.Set_Message(p_app_name => G_APP_NAME,
147 p_msg_name => G_UNEXPECTED_ERROR,
148 p_token1 => G_SQLCODE_TOKEN,
149 p_token1_value => SQLCODE,
150 p_token2 => G_SQLERRM_TOKEN,
151 p_token2_value => SQLERRM);
152 FND_MSG_PUB.Count_And_Get
153 (p_count => x_msg_count,
154 p_data => x_msg_data);
155 END calculate_unit_cost;
156
157 --------------------------------------------------------------------------
158 ----- Validate Asset Number
159 --------------------------------------------------------------------------
160 FUNCTION validate_asset_number(
161 p_asdv_rec IN advv_rec_type
162 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
163 ) RETURN VARCHAR2
164 IS
165 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
166 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
167 BEGIN
168
169 -- is required
170 IF (p_asdv_rec.ASSET_NUMBER IS NULL) OR
171 (p_asdv_rec.ASSET_NUMBER = OKL_API.G_MISS_CHAR)
172 THEN
173 OKL_API.Set_Message(p_app_name => G_APP_NAME,
174 p_msg_name => G_REQUIRED_VALUE,
175 p_token1 => G_COL_NAME_TOKEN,
176 p_token1_value => 'Asset Number');
177 RAISE G_EXCEPTION_HALT_VALIDATION;
178 END IF;
179
180 --
181 -- must be unique within ?:
182 -- can be check at process_split_asset_comp()
183 --
184
185 RETURN l_return_status;
186 EXCEPTION
187 WHEN G_EXCEPTION_HALT_VALIDATION THEN
188 l_return_status := OKL_API.G_RET_STS_ERROR;
189 RETURN l_return_status;
190 WHEN OTHERS THEN
191 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
192 OKL_API.Set_Message(p_app_name => G_APP_NAME,
193 p_msg_name => G_UNEXPECTED_ERROR,
194 p_token1 => G_SQLCODE_TOKEN,
195 p_token1_value => SQLCODE,
196 p_token2 => G_SQLERRM_TOKEN,
197 p_token2_value => SQLERRM);
198 RETURN l_return_status;
199 END;
200 --------------------------------------------------------------------------
201 ----- Validate Asset Description
202 --------------------------------------------------------------------------
203 FUNCTION validate_asset_description(
204 p_asdv_rec IN advv_rec_type
205 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
206 ) RETURN VARCHAR2
207 IS
208 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
209 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
210 BEGIN
211
212 -- is required
213 IF (p_asdv_rec.DESCRIPTION IS NULL) OR
214 (p_asdv_rec.DESCRIPTION = OKL_API.G_MISS_CHAR)
215 THEN
216 OKL_API.Set_Message(p_app_name => G_APP_NAME,
217 p_msg_name => G_REQUIRED_VALUE,
218 p_token1 => G_COL_NAME_TOKEN,
219 p_token1_value => 'Asset Description');
220 RAISE G_EXCEPTION_HALT_VALIDATION;
221 END IF;
222
223 RETURN l_return_status;
224 EXCEPTION
225 WHEN G_EXCEPTION_HALT_VALIDATION THEN
226 l_return_status := OKL_API.G_RET_STS_ERROR;
227 RETURN l_return_status;
228 WHEN OTHERS THEN
229 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
230 OKL_API.Set_Message(p_app_name => G_APP_NAME,
231 p_msg_name => G_UNEXPECTED_ERROR,
232 p_token1 => G_SQLCODE_TOKEN,
233 p_token1_value => SQLCODE,
234 p_token2 => G_SQLERRM_TOKEN,
235 p_token2_value => SQLERRM);
236 RETURN l_return_status;
237 END;
238 --------------------------------------------------------------------------
239 ----- Validate Split Percent
240 --------------------------------------------------------------------------
241 FUNCTION validate_split_percent(
242 p_asdv_rec IN advv_rec_type
243 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
244 ) RETURN VARCHAR2
245 IS
246 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
247 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
248 l_percent NUMBER;
249
250 BEGIN
251
252 -- is required
253 IF (p_asdv_rec.SPLIT_PERCENT IS NULL) OR
254 (p_asdv_rec.SPLIT_PERCENT = OKL_API.G_MISS_NUM)
255 THEN
256 OKL_API.Set_Message(p_app_name => G_APP_NAME,
257 p_msg_name => G_REQUIRED_VALUE,
258 p_token1 => G_COL_NAME_TOKEN,
259 p_token1_value => 'Split Percent');
260 RAISE G_EXCEPTION_HALT_VALIDATION;
261 END IF;
262
263 IF (p_asdv_rec.SPLIT_PERCENT < 0) THEN
264 OKL_API.Set_Message(p_app_name => G_APP_NAME,
265 p_msg_name => 'OKL_LLA_POSITIVE_AMOUNT_ONLY',
266 p_token1 => G_COL_NAME_TOKEN,
267 p_token1_value => 'Split Percent');
268 RAISE G_EXCEPTION_HALT_VALIDATION;
269 END IF;
270
271 IF (p_asdv_rec.SPLIT_PERCENT > 100) THEN
272 OKL_API.Set_Message(p_app_name => G_APP_NAME,
273 p_msg_name => 'OKL_LLA_PERCENT');
274 RAISE G_EXCEPTION_HALT_VALIDATION;
275
276 END IF;
277
278 RETURN l_return_status;
279 EXCEPTION
280 WHEN G_EXCEPTION_HALT_VALIDATION THEN
281 l_return_status := OKL_API.G_RET_STS_ERROR;
282 RETURN l_return_status;
283 WHEN OTHERS THEN
284 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
285 OKL_API.Set_Message(p_app_name => G_APP_NAME,
286 p_msg_name => G_UNEXPECTED_ERROR,
287 p_token1 => G_SQLCODE_TOKEN,
288 p_token1_value => SQLCODE,
289 p_token2 => G_SQLERRM_TOKEN,
290 p_token2_value => SQLERRM);
291 RETURN l_return_status;
292 END;
293 --------------------------------------------------------------------------
294 ----- Validate Item
295 --------------------------------------------------------------------------
296 FUNCTION validate_Item(
297 p_asdv_rec IN advv_rec_type
298 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
299 ) RETURN VARCHAR2
300 IS
301 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
302 l_dummy VARCHAR2(1) := '?';
303
304 BEGIN
305
306 -- is required
307 IF (p_asdv_rec.INVENTORY_ITEM_ID IS NULL) OR
308 (p_asdv_rec.INVENTORY_ITEM_ID = OKL_API.G_MISS_NUM)
309 THEN
310 OKL_API.Set_Message(p_app_name => G_APP_NAME,
311 p_msg_name => G_REQUIRED_VALUE,
312 p_token1 => G_COL_NAME_TOKEN,
313 p_token1_value => 'Item');
314 RAISE G_EXCEPTION_HALT_VALIDATION;
315 END IF;
316
317 -- FK check take care by TAPI: OKL_ASD_PVT
318
319 RETURN l_return_status;
320 EXCEPTION
321 WHEN G_EXCEPTION_HALT_VALIDATION THEN
322 l_return_status := OKL_API.G_RET_STS_ERROR;
323 RETURN l_return_status;
324 WHEN OTHERS THEN
325 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
326 OKL_API.Set_Message(p_app_name => G_APP_NAME,
327 p_msg_name => G_UNEXPECTED_ERROR,
328 p_token1 => G_SQLCODE_TOKEN,
329 p_token1_value => SQLCODE,
330 p_token2 => G_SQLERRM_TOKEN,
331 p_token2_value => SQLERRM);
332 RETURN l_return_status;
333 END;
334
335 --------------------------------------------------------------------------
336 FUNCTION validate_attributes(
337 p_asdv_rec IN advv_rec_type
338 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
339 ) RETURN VARCHAR2
340 IS
341 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
342 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
343
344 BEGIN
345
346 -- Do formal attribute validation:
347 l_return_status := validate_asset_number(p_asdv_rec, p_mode);
348 --- Store the highest degree of error
349 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
350 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
351 x_return_status := l_return_status;
352 END IF;
353 RAISE G_EXCEPTION_HALT_VALIDATION;
354 END IF;
355
356 -- Do formal attribute validation:
357 l_return_status := validate_asset_description(p_asdv_rec, p_mode);
358 --- Store the highest degree of error
359 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
360 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
361 x_return_status := l_return_status;
362 END IF;
363 RAISE G_EXCEPTION_HALT_VALIDATION;
364 END IF;
365
366 -- Do formal attribute validation:
367 l_return_status := validate_split_percent(p_asdv_rec, p_mode);
368 --- Store the highest degree of error
369 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
370 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
371 x_return_status := l_return_status;
372 END IF;
373 RAISE G_EXCEPTION_HALT_VALIDATION;
374 END IF;
375
376 -- Do formal attribute validation:
377 l_return_status := validate_item(p_asdv_rec, p_mode);
378 --- Store the highest degree of error
379 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
380 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
381 x_return_status := l_return_status;
382 END IF;
383 RAISE G_EXCEPTION_HALT_VALIDATION;
384 END IF;
385
386 RETURN x_return_status;
387 EXCEPTION
388 WHEN G_EXCEPTION_HALT_VALIDATION THEN
389 RETURN x_return_status;
390 WHEN OTHERS THEN
391 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
392 OKL_API.Set_Message(p_app_name => G_APP_NAME,
393 p_msg_name => G_UNEXPECTED_ERROR,
394 p_token1 => G_SQLCODE_TOKEN,
395 p_token1_value => SQLCODE,
396 p_token2 => G_SQLERRM_TOKEN,
397 p_token2_value => SQLERRM);
398 RETURN l_return_status;
399 END validate_attributes;
400 --------------------------------------------------------------------------
401 --------------------------------------------------------------------------
402 ----- Validate Split Percent
403 --------------------------------------------------------------------------
404 FUNCTION validate_split_percent(
405 p_asdv_tbl IN advv_tbl_type
406 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
407 ) RETURN VARCHAR2
408 IS
409 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
410 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
411 l_percent NUMBER;
412
413 CURSOR c(p_tal_id NUMBER)
414 IS
415 SELECT NVL(SUM(SPLIT_PERCENT),0)
416 FROM okl_txd_assets_v
417 WHERE TAL_ID = p_tal_id
418 ;
419
420 BEGIN
421
422 --
423 -- the total can not exceeds 100
424 --
425 OPEN c (p_asdv_tbl(p_asdv_tbl.FIRST).tal_id);
426 FETCH c INTO l_percent;
427 CLOSE c;
428
429 IF (l_percent > 100) THEN
430 OKL_API.Set_Message(p_app_name => G_APP_NAME,
431 p_msg_name => 'OKL_LLA_PERCENT');
432 RAISE G_EXCEPTION_HALT_VALIDATION;
433
434 END IF;
435
436 RETURN l_return_status;
437 EXCEPTION
438 WHEN G_EXCEPTION_HALT_VALIDATION THEN
439 l_return_status := OKL_API.G_RET_STS_ERROR;
440 RETURN l_return_status;
441 WHEN OTHERS THEN
442 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
443 OKL_API.Set_Message(p_app_name => G_APP_NAME,
444 p_msg_name => G_UNEXPECTED_ERROR,
445 p_token1 => G_SQLCODE_TOKEN,
446 p_token1_value => SQLCODE,
447 p_token2 => G_SQLERRM_TOKEN,
448 p_token2_value => SQLERRM);
449 RETURN l_return_status;
450 END;
451 --------------------------------------------------------------------------
452 ----- Validate Asset Number
453 --------------------------------------------------------------------------
454 FUNCTION validate_asset_number(
455 p_asdv_tbl IN advv_tbl_type
456 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
457 ) RETURN VARCHAR2
458 IS
459 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
460 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
461 l_asset_number okl_txd_assets_B.ASSET_NUMBER%TYPE;
462
463 --cursor to check duplicate within same transaction
464 CURSOR c(p_tal_id NUMBER)
465 IS
466 SELECT asset_number
467 FROM okl_txd_assets_v
468 WHERE TAL_ID = p_tal_id
469 GROUP BY asset_number
470 HAVING COUNT(1) > 1
471 ;
472 --bug #2465479 begin
473 --cursor to fetch asset number
474 CURSOR asset_cur(p_asd_id IN NUMBER) IS
475 SELECT asset_number
476 FROM okl_txd_assets_v
477 WHERE id = p_asd_id;
478
479 --cursor to check duplicate in FA
480 CURSOR asset_chk_curs1 (p_asset_number IN VARCHAR2) IS
481 SELECT 'Y'
482 FROM okx_assets_v okx
483 WHERE okx.asset_number = p_asset_number;
484
485 --chk for asset on asset line
486 CURSOR asset_chk_curs2 (p_asset_number IN VARCHAR2) IS
487 SELECT 'Y'
488 FROM okl_k_lines_full_v kle,
489 okc_line_styles_b lse
490 WHERE kle.name = p_asset_number
491 AND kle.lse_id = lse.id
492 AND lse.lty_code = 'FIXED_ASSET';
493
494
495
496 --check for asset on create asset or rebook transaction
497 CURSOR asset_chk_curs3 (p_asset_number IN VARCHAR2) IS
498 SELECT 'Y'
499 FROM okl_txl_assets_b txl
500 WHERE txl.asset_number = p_asset_number
501 AND txl.tal_type IN ('ALI','CRB'); --only transactions apart from split which create a new line
502
503 l_asset_exists VARCHAR2(1) DEFAULT 'N';
504 i NUMBER;
505 --bug #2465479 end
506
507 BEGIN
508
509 --
510 -- catch the 1st invalid asset_number only
511 --
512 OPEN c (p_asdv_tbl(p_asdv_tbl.FIRST).tal_id);
513 FETCH c INTO l_asset_number;
514 IF c%NOTFOUND THEN
515 NULL;
516 END IF;
517 CLOSE c;
518
519 IF (l_asset_number IS NOT NULL) THEN
520
521 OKL_API.Set_Message(p_app_name => G_APP_NAME,
522 p_msg_name => G_NOT_UNIQUE,
523 p_token1 => G_COL_NAME_TOKEN,
524 p_token1_value => 'Asset Number '|| l_asset_number);
525 RAISE G_EXCEPTION_HALT_VALIDATION;
526 --bug #2465479 begin
527 ELSIF (l_asset_number IS NULL) THEN
528 IF p_asdv_tbl.COUNT > 0 THEN
529 i := p_asdv_tbl.FIRST;
530 LOOP
531
532 l_asset_number := NULL;
533 OPEN asset_cur(p_asd_id => p_asdv_tbl(i).id);
534 FETCH asset_cur INTO l_asset_number;
535 IF asset_cur%NOTFOUND THEN
536 NULL;
537 END IF;
538 CLOSE asset_cur;
539
540 IF l_asset_number IS NOT NULL THEN
541 l_asset_exists := 'N';
542 OPEN asset_chk_curs1(p_asdv_tbl(i).asset_number);
543 FETCH asset_chk_curs1 INTO l_asset_exists;
544 IF asset_chk_curs1%NOTFOUND THEN
545 OPEN asset_chk_curs2(p_asdv_tbl(i).asset_number);
546 FETCH asset_chk_curs2 INTO l_asset_exists;
547 IF asset_chk_curs2%NOTFOUND THEN
548 OPEN asset_chk_curs3(p_asdv_tbl(i).asset_number);
549 FETCH asset_chk_curs3 INTO l_asset_exists;
550 IF asset_chk_curs3%NOTFOUND THEN
551 NULL;
552 END IF;
553 CLOSE asset_chk_curs3;
554 END IF;
555 CLOSE asset_chk_curs2;
556 END IF;
557 CLOSE asset_chk_curs1;
558 END IF;
559 IF l_asset_exists = 'Y' THEN
560 -- store SQL error message on message stack
561 OKL_API.Set_Message(p_app_name => G_APP_NAME,
562 p_msg_name => G_NOT_UNIQUE,
563 p_token1 => G_COL_NAME_TOKEN,
564 p_token1_value => 'Asset Number '|| p_asdv_tbl(i).asset_number);
565
566 -- halt validation as it is a required field
567 RAISE G_EXCEPTION_HALT_VALIDATION;
568 END IF;
569
570 IF i = p_asdv_tbl.LAST THEN
571 EXIT;
572 ELSE
573 i:= i+1;
574 END IF;
575 END LOOP;
576 END IF;
577 END IF;
578 --bug #2465479 end
579 RETURN l_return_status;
580
581 EXCEPTION
582 WHEN G_EXCEPTION_HALT_VALIDATION THEN
583 l_return_status := OKL_API.G_RET_STS_ERROR;
584 RETURN l_return_status;
585 WHEN OTHERS THEN
586
587 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
588
589 --bug #2465479 begin
590 --close the cursor
591 IF asset_chk_curs1%ISOPEN THEN
592 CLOSE asset_chk_curs1;
593 END IF;
594 IF asset_chk_curs2%ISOPEN THEN
595 CLOSE asset_chk_curs2;
596 END IF;
597 IF asset_chk_curs3%ISOPEN THEN
598 CLOSE asset_chk_curs3;
599 END IF;
600 IF asset_cur%ISOPEN THEN
601 CLOSE asset_cur;
602 END IF;
603 IF c%ISOPEN THEN
604 CLOSE asset_cur;
605 END IF;
606 --bug #2465479 end
607
608
609 OKL_API.Set_Message(p_app_name => G_APP_NAME,
610 p_msg_name => G_UNEXPECTED_ERROR,
611 p_token1 => G_SQLCODE_TOKEN,
612 p_token1_value => SQLCODE,
613 p_token2 => G_SQLERRM_TOKEN,
614 p_token2_value => SQLERRM);
615 RETURN l_return_status;
616 END;
617 --------------------------------------------------------------------------
618 ----- Validate Inventory Item id
619 --------------------------------------------------------------------------
620 FUNCTION validate_inventory_item_id(
621 p_asdv_tbl IN advv_tbl_type
622 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
623 ) RETURN VARCHAR2
624 IS
625 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
626 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
627 l_inventory_item_id okl_txd_assets_B.INVENTORY_ITEM_ID%TYPE;
628 l_inventory_item_name okx_system_items_v.NAME%TYPE;
629
630 CURSOR c(p_tal_id NUMBER)
631 IS
632 SELECT inventory_item_id
633 FROM okl_txd_assets_v
634 WHERE TAL_ID = p_tal_id
635 GROUP BY inventory_item_id
636 HAVING COUNT(1) > 1
637 ;
638
639 -- don't care the org_id
640 CURSOR c_name(p_inventory_item_id NUMBER)
641 IS
642 SELECT i.name
643 FROM okx_system_items_v i
644 WHERE i.ID1 = p_inventory_item_id
645 --group by i.name
646 ;
647
648 BEGIN
649
650 --
651 -- catch the 1st invalid asset_number only
652 --
653 OPEN c (p_asdv_tbl(p_asdv_tbl.FIRST).tal_id);
654 FETCH c INTO l_inventory_item_id;
655 CLOSE c;
656
657 IF (l_inventory_item_id IS NOT NULL) THEN
658
659 OPEN c_name (l_inventory_item_id);
660 FETCH c_name INTO l_inventory_item_name;
661 CLOSE c_name;
662
663 OKL_API.Set_Message(p_app_name => G_APP_NAME,
664 p_msg_name => G_NOT_UNIQUE,
665 p_token1 => G_COL_NAME_TOKEN,
666 p_token1_value => 'Inventory Item Name ' || l_inventory_item_name);
667 RAISE G_EXCEPTION_HALT_VALIDATION;
668
669 END IF;
670
671 RETURN l_return_status;
672 EXCEPTION
673 WHEN G_EXCEPTION_HALT_VALIDATION THEN
674 l_return_status := OKL_API.G_RET_STS_ERROR;
675 RETURN l_return_status;
676 WHEN OTHERS THEN
677 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
678 OKL_API.Set_Message(p_app_name => G_APP_NAME,
679 p_msg_name => G_UNEXPECTED_ERROR,
680 p_token1 => G_SQLCODE_TOKEN,
681 p_token1_value => SQLCODE,
682 p_token2 => G_SQLERRM_TOKEN,
683 p_token2_value => SQLERRM);
684 RETURN l_return_status;
685 END;
686
687 ------------------------------------------------------------------------
688 FUNCTION validate_rows(
689 p_asdv_tbl IN advv_tbl_type
690 ,p_mode IN VARCHAR2 -- 'C'reate,'U'pdate,'D'elete
691 ) RETURN VARCHAR2
692 IS
693 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
694 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
695
696 BEGIN
697
698 -- Do formal attribute validation:
699 l_return_status := validate_split_percent(p_asdv_tbl, p_mode);
700 --- Store the highest degree of error
701 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
702 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
703 x_return_status := l_return_status;
704 END IF;
705 RAISE G_EXCEPTION_HALT_VALIDATION;
706 END IF;
707
708 l_return_status := validate_asset_number(p_asdv_tbl, p_mode);
709 --- Store the highest degree of error
710 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
711 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
712 x_return_status := l_return_status;
713 END IF;
714 RAISE G_EXCEPTION_HALT_VALIDATION;
715 END IF;
716
717 -- fmiao - Bug#5230268 - Commented - Start
718 -- Split Asset by Component. This is to handle the following scenario where while booking
719 -- contracts lessor is unaware of the number units of the item and hence creates the
720 -- asset with 1 unit and later needs to split the asset by repeating the item.
721 /*
722
723 l_return_status := validate_inventory_item_id(p_asdv_tbl, p_mode);
724 --- Store the highest degree of error
725 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
726 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
727 x_return_status := l_return_status;
728 END IF;
729 RAISE G_EXCEPTION_HALT_VALIDATION;
730 END IF;
731 */
732 -- fmiao - Bug#5230268 - Commented - End
733
734 RETURN x_return_status;
735 EXCEPTION
736 WHEN G_EXCEPTION_HALT_VALIDATION THEN
737 RETURN x_return_status;
738 WHEN OTHERS THEN
739 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
740 OKL_API.Set_Message(p_app_name => G_APP_NAME,
741 p_msg_name => G_UNEXPECTED_ERROR,
742 p_token1 => G_SQLCODE_TOKEN,
743 p_token1_value => SQLCODE,
744 p_token2 => G_SQLERRM_TOKEN,
745 p_token2_value => SQLERRM);
746 RETURN l_return_status;
747 END validate_rows;
748 -----------------------------------------------------------------------------------
749 PROCEDURE create_split_asset_comp(
750 p_api_version IN NUMBER,
751 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
752 x_return_status OUT NOCOPY VARCHAR2,
753 x_msg_count OUT NOCOPY NUMBER,
754 x_msg_data OUT NOCOPY VARCHAR2,
755 p_asdv_tbl IN advv_tbl_type,
756 x_asdv_tbl OUT NOCOPY advv_tbl_type)
757 IS
758
759 l_api_version CONSTANT NUMBER := 1.0;
760 l_api_name CONSTANT VARCHAR2(30) := 'create_split_asset_comp';
761 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
762 l_mode VARCHAR2(1) := 'C';
763 i NUMBER;
764 --Bug# 4126331
765 l_asdv_rec advv_rec_type;
766
767 /*
768 -- mvasudev, 08/23/2004
769 -- Added PROCEDURE to enable Business Event
770 */
771 PROCEDURE raise_business_event(
772 x_return_status OUT NOCOPY VARCHAR2
773 )
774 IS
775
776 CURSOR l_cle_trx_csr(p_tal_id IN NUMBER)
777 IS
778 SELECT cleb.dnz_chr_id
779 ,cleb.id cle_id
780 ,trxa.date_trans_occurred
781 FROM okl_trx_assets trxa
782 ,okl_txl_assets_b txla
783 ,okc_k_lines_b cleb
784 ,okc_line_styles_b lseb
785 WHERE txla.kle_id = cleb.id
786 AND cleb.lse_id = lseb.id
787 AND lseb.lty_code = 'FIXED_ASSET'
788 AND txla.tal_type = 'ALI'
789 AND txla.tas_id = trxa.id
790 AND trxa.tsu_code = 'ENTERED'
791 AND txla.id = p_tal_id;
792
793 l_parameter_list wf_parameter_list_t;
794 BEGIN
795
796 IF (p_asdv_tbl.COUNT > 0) THEN
797 FOR l_cle_trx_rec IN l_cle_trx_csr(p_asdv_tbl(1).tal_id)
798 LOOP
799
800 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,l_cle_trx_rec.dnz_chr_id,l_parameter_list);
801 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID,l_cle_trx_rec.cle_id,l_parameter_list);
802 wf_event.AddParameterToList(G_WF_ITM_TRANS_DATE,fnd_date.date_to_canonical(l_cle_trx_rec.date_trans_occurred),l_parameter_list);
803
804 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
805 p_init_msg_list => p_init_msg_list,
806 x_return_status => x_return_status,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 p_event_name => G_WF_EVT_KHR_SPLIT_ASSET_REQ,
810 p_parameters => l_parameter_list);
811
812 END LOOP;
813 END IF;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
818 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
819 END raise_business_event;
820
821 /*
822 -- mvasudev, 08/23/2004
823 -- END, PROCEDURE to enable Business Event
824 */
825
826 BEGIN
827 -- Set API savepoint
828 SAVEPOINT create_split_asset_comp_pvt;
829
830 -- Check for call compatibility
831 IF (NOT FND_API.Compatible_API_Call (l_api_version,
832 p_api_version,
833 l_api_name,
834 G_PKG_NAME ))
835 THEN
836 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
837 END IF;
838
839 -- Initialize message list if requested
840 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
841 FND_MSG_PUB.initialize;
842 END IF;
843
844 -- Initialize API status to success
845 x_return_status := OKL_API.G_RET_STS_SUCCESS;
846
847
848 /*** Begin API body ****************************************************/
849
850 IF (p_asdv_tbl.COUNT > 0) THEN
851 i := p_asdv_tbl.FIRST;
852
853 LOOP
854
855 l_return_status := validate_attributes(p_asdv_tbl(i), l_mode);
856 --- Store the highest degree of error
857 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
858 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
859 x_return_status := l_return_status;
860 END IF;
861 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
862 END IF;
863
864 --Bug# 4126331: Only upper case aset numbers are alowed in FA
865 l_asdv_rec := NULL;
866 l_asdv_rec := p_asdv_tbl(i);
867 l_asdv_rec.asset_number := upper(p_asdv_tbl(i).asset_number);
868 Okl_Asd_Pvt.insert_row(p_api_version,
869 p_init_msg_list,
870 x_return_status,
871 x_msg_count,
872 x_msg_data,
873 l_asdv_rec,
874 --p_asdv_tbl(i),
875 x_asdv_tbl(i));
876
877 EXIT WHEN (i = p_asdv_tbl.LAST);
878 i := p_asdv_tbl.NEXT(i);
879 END LOOP;
880
881 -- validate all based on current image
882 l_return_status := validate_rows(p_asdv_tbl, l_mode);
883 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
884 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
885 x_return_status := l_return_status;
886 END IF;
887 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
888 END IF;
889
890 -- update all based on current image
891 calculate_unit_cost(p_api_version => p_api_version,
892 p_init_msg_list => p_init_msg_list,
893 x_return_status => x_return_status,
894 x_msg_count => x_msg_count,
895 x_msg_data => x_msg_data,
896 p_tal_id => p_asdv_tbl(p_asdv_tbl.FIRST).tal_id);
897
898 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
899 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
900 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
901 RAISE OKL_API.G_EXCEPTION_ERROR;
902 END IF;
903
904 /*
905 -- mvasudev, 08/23/2004
906 -- Code change to enable Business Event
907 */
908 raise_business_event(x_return_status => x_return_status);
909 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
910 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
911 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
912 RAISE OKL_API.G_EXCEPTION_ERROR;
913 END IF;
914
915 /*
916 -- mvasudev, 08/23/2004
917 -- END, Code change to enable Business Event
918 */
919
920 END IF;
921
922 /*** End API body ******************************************************/
923
924 -- Get message count and if count is 1, get message info
925 FND_MSG_PUB.Count_And_Get
926 (p_count => x_msg_count,
927 p_data => x_msg_data);
928
929 EXCEPTION
930 WHEN OKL_API.G_EXCEPTION_ERROR THEN
931 ROLLBACK TO create_split_asset_comp_pvt;
932 x_return_status := OKL_API.G_RET_STS_ERROR;
933 FND_MSG_PUB.Count_And_Get
934 (p_count => x_msg_count,
935 p_data => x_msg_data);
936
937 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
938 ROLLBACK TO create_split_asset_comp_pvt;
939 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
940 FND_MSG_PUB.Count_And_Get
941 (p_count => x_msg_count,
942 p_data => x_msg_data);
943
944 WHEN OTHERS THEN
945 ROLLBACK TO create_split_asset_comp_pvt;
946 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
947 OKL_API.Set_Message(p_app_name => G_APP_NAME,
948 p_msg_name => G_UNEXPECTED_ERROR,
949 p_token1 => G_SQLCODE_TOKEN,
950 p_token1_value => SQLCODE,
951 p_token2 => G_SQLERRM_TOKEN,
952 p_token2_value => SQLERRM);
953 FND_MSG_PUB.Count_And_Get
954 (p_count => x_msg_count,
955 p_data => x_msg_data);
956
957 END create_split_asset_comp;
958
959 -----------------------------------------------------------------------------------
960
961 PROCEDURE update_split_asset_comp(
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2,
967 p_asdv_tbl IN advv_tbl_type,
968 x_asdv_tbl OUT NOCOPY advv_tbl_type)
969 IS
970
971 l_api_version CONSTANT NUMBER := 1.0;
972 l_api_name CONSTANT VARCHAR2(30) := 'update_split_asset_comp';
973 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
974 l_mode VARCHAR2(1) := 'U';
975 i NUMBER;
976 --Bug# 4126331
977 l_asdv_rec advv_rec_type;
978
979 BEGIN
980 -- Set API savepoint
981 SAVEPOINT update_split_asset_comp_pvt;
982
983 -- Check for call compatibility
984 IF (NOT FND_API.Compatible_API_Call (l_api_version,
985 p_api_version,
986 l_api_name,
987 G_PKG_NAME ))
988 THEN
989 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 END IF;
991
992 -- Initialize message list if requested
993 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
994 FND_MSG_PUB.initialize;
995 END IF;
996
997 -- Initialize API status to success
998 x_return_status := OKL_API.G_RET_STS_SUCCESS;
999
1000
1001 /*** Begin API body ****************************************************/
1002
1003
1004 IF (p_asdv_tbl.COUNT > 0) THEN
1005 i := p_asdv_tbl.FIRST;
1006 LOOP
1007
1008 l_return_status := validate_attributes(p_asdv_tbl(i), l_mode);
1009 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1010 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1011 x_return_status := l_return_status;
1012 END IF;
1013 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1014 END IF;
1015
1016 --Bug# 4126331: Only upper case aset numbers are alowed in FA
1017 l_asdv_rec := NULL;
1018 l_asdv_rec := p_asdv_tbl(i);
1019 l_asdv_rec.asset_number := upper(p_asdv_tbl(i).asset_number);
1020 Okl_Asd_Pvt.update_row(p_api_version,
1021 p_init_msg_list,
1022 x_return_status,
1023 x_msg_count,
1024 x_msg_data,
1025 l_asdv_rec,
1026 --p_asdv_tbl(i),
1027 x_asdv_tbl(i));
1028
1029 EXIT WHEN (i = p_asdv_tbl.LAST);
1030 i := p_asdv_tbl.NEXT(i);
1031 END LOOP;
1032
1033 -- validate all based on current image
1034 l_return_status := validate_rows(p_asdv_tbl, l_mode);
1035 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1036 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1037 x_return_status := l_return_status;
1038 END IF;
1039 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1040 END IF;
1041
1042 -- update all based on current image
1043 calculate_unit_cost(p_api_version => p_api_version,
1044 p_init_msg_list => p_init_msg_list,
1045 x_return_status => x_return_status,
1046 x_msg_count => x_msg_count,
1047 x_msg_data => x_msg_data,
1048 p_tal_id => p_asdv_tbl(p_asdv_tbl.FIRST).tal_id);
1049
1050 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1051 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1052 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1053 RAISE OKL_API.G_EXCEPTION_ERROR;
1054 END IF;
1055
1056 END IF;
1057
1058 /*** End API body ******************************************************/
1059
1060 -- Get message count and if count is 1, get message info
1061 FND_MSG_PUB.Count_And_Get
1062 (p_count => x_msg_count,
1063 p_data => x_msg_data);
1064
1065 EXCEPTION
1066 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1067 ROLLBACK TO update_split_asset_comp_pvt;
1068 x_return_status := OKL_API.G_RET_STS_ERROR;
1069 FND_MSG_PUB.Count_And_Get
1070 (p_count => x_msg_count,
1071 p_data => x_msg_data);
1072
1073 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1074 ROLLBACK TO update_split_asset_comp_pvt;
1075 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1076 FND_MSG_PUB.Count_And_Get
1077 (p_count => x_msg_count,
1078 p_data => x_msg_data);
1079
1080 WHEN OTHERS THEN
1081 ROLLBACK TO update_split_asset_comp_pvt;
1082 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1083 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1084 p_msg_name => G_UNEXPECTED_ERROR,
1085 p_token1 => G_SQLCODE_TOKEN,
1086 p_token1_value => SQLCODE,
1087 p_token2 => G_SQLERRM_TOKEN,
1088 p_token2_value => SQLERRM);
1089 FND_MSG_PUB.Count_And_Get
1090 (p_count => x_msg_count,
1091 p_data => x_msg_data);
1092
1093 END update_split_asset_comp;
1094
1095 --------------------------------------------------------------------------------
1096
1097 PROCEDURE delete_split_asset_comp(
1098 p_api_version IN NUMBER,
1099 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1100 x_return_status OUT NOCOPY VARCHAR2,
1101 x_msg_count OUT NOCOPY NUMBER,
1102 x_msg_data OUT NOCOPY VARCHAR2,
1103 p_asdv_tbl IN advv_tbl_type)
1104 IS
1105
1106 l_api_version CONSTANT NUMBER := 1.0;
1107 l_api_name CONSTANT VARCHAR2(30) := 'delete_split_asset_comp';
1108 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1109 BEGIN
1110 -- Set API savepoint
1111 SAVEPOINT delete_split_asset_comp_pvt;
1112
1113 -- Check for call compatibility
1114 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1115 p_api_version,
1116 l_api_name,
1117 G_PKG_NAME ))
1118 THEN
1119 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1120 END IF;
1121
1122 -- Initialize message list if requested
1123 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1124 FND_MSG_PUB.initialize;
1125 END IF;
1126
1127 -- Initialize API status to success
1128 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1129
1130
1131 /*** Begin API body ****************************************************/
1132 Okl_Asd_Pvt.delete_row(p_api_version,
1133 p_init_msg_list,
1134 x_return_status,
1135 x_msg_count,
1136 x_msg_data,
1137 p_asdv_tbl);
1138
1139 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1140 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1141 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
1142 RAISE OKL_API.G_EXCEPTION_ERROR;
1143 END IF;
1144
1145 /*** End API body ******************************************************/
1146
1147 -- Get message count and if count is 1, get message info
1148 FND_MSG_PUB.Count_And_Get
1149 (p_count => x_msg_count,
1150 p_data => x_msg_data);
1151
1152 EXCEPTION
1153 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1154 ROLLBACK TO delete_split_asset_comp_pvt;
1155 x_return_status := OKL_API.G_RET_STS_ERROR;
1156 FND_MSG_PUB.Count_And_Get
1157 (p_count => x_msg_count,
1158 p_data => x_msg_data);
1159
1160 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1161 ROLLBACK TO delete_split_asset_comp_pvt;
1162 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1163 FND_MSG_PUB.Count_And_Get
1164 (p_count => x_msg_count,
1165 p_data => x_msg_data);
1166
1167 WHEN OTHERS THEN
1168 ROLLBACK TO delete_split_asset_comp_pvt;
1169 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1170 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1171 p_msg_name => G_UNEXPECTED_ERROR,
1172 p_token1 => G_SQLCODE_TOKEN,
1173 p_token1_value => SQLCODE,
1174 p_token2 => G_SQLERRM_TOKEN,
1175 p_token2_value => SQLERRM);
1176 FND_MSG_PUB.Count_And_Get
1177 (p_count => x_msg_count,
1178 p_data => x_msg_data);
1179
1180 END delete_split_asset_comp;
1181 -----------------------------------------------------------------------------------
1182 --------------------------------------------------------------------------
1183 ----- Validate Split Percent
1184 --------------------------------------------------------------------------
1185 FUNCTION validate_split_percent(
1186 p_tal_id IN NUMBER
1187 ) RETURN VARCHAR2
1188 IS
1189 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1190 l_dummy VARCHAR2(1) := OKL_API.G_TRUE;
1191 l_percent NUMBER;
1192
1193 CURSOR c(p_tal_id NUMBER)
1194 IS
1195 SELECT NVL(SUM(SPLIT_PERCENT),0)
1196 FROM okl_txd_assets_v
1197 WHERE TAL_ID = p_tal_id
1198 ;
1199
1200 BEGIN
1201
1202 --
1203 -- the total can not exceeds 100
1204 --
1205 OPEN c (p_tal_id);
1206 FETCH c INTO l_percent;
1207 CLOSE c;
1208
1209 IF (l_percent <> 100) THEN
1210 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1211 p_msg_name => 'OKL_LLA_TOTAL_PERCENT',
1212 p_token1 => 'TITLE',
1213 p_token1_value => 'Split Percent');
1214 RAISE G_EXCEPTION_HALT_VALIDATION;
1215
1216 END IF;
1217
1218 RETURN l_return_status;
1219 EXCEPTION
1220 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1221 l_return_status := OKL_API.G_RET_STS_ERROR;
1222 RETURN l_return_status;
1223 WHEN OTHERS THEN
1224 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1225 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1226 p_msg_name => G_UNEXPECTED_ERROR,
1227 p_token1 => G_SQLCODE_TOKEN,
1228 p_token1_value => SQLCODE,
1229 p_token2 => G_SQLERRM_TOKEN,
1230 p_token2_value => SQLERRM);
1231 RETURN l_return_status;
1232 END;
1233 -----------------------------------------------------------------------------------
1234
1235 PROCEDURE process_split_asset_comp(
1236 p_api_version IN NUMBER,
1237 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 x_msg_count OUT NOCOPY NUMBER,
1240 x_msg_data OUT NOCOPY VARCHAR2,
1241 p_tal_id IN NUMBER)
1242 IS
1243
1244 l_api_version CONSTANT NUMBER := 1.0;
1245 l_api_name CONSTANT VARCHAR2(30) := 'process_split_asset_comp';
1246 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1247
1248 CURSOR fin_ast_csr (p_tal_id IN NUMBER) IS
1249 SELECT cle.cle_id
1250 FROM okc_k_lines_b cle,
1251 okl_txl_assets_b tal
1252 WHERE cle.id = tal.kle_id
1253 AND tal.id = p_tal_id;
1254
1255 l_cle_id okc_k_lines_b.id%TYPE;
1256 l_cle_tbl okl_split_asset_pub.cle_tbl_type;
1257
1258 /*
1259 -- mvasudev, 08/24/2004
1260 -- Added PROCEDURE to enable Business Event
1261 */
1262
1263 --Bug 4047504:
1264 CURSOR l_cle_trx_csr(p_tal_id IN NUMBER)
1265 IS
1266 SELECT cleb.dnz_chr_id
1267 ,cleb.cle_id cle_id
1268 ,trxa.date_trans_occurred
1269 FROM okl_trx_assets trxa
1270 ,okl_txl_assets_b txla
1271 ,okc_k_lines_b cleb
1272 ,okc_line_styles_b lseb
1273 WHERE txla.kle_id = cleb.id
1274 AND cleb.lse_id = lseb.id
1275 AND lseb.lty_code = 'FIXED_ASSET'
1276 AND txla.tal_type = 'ALI'
1277 AND txla.tas_id = trxa.id
1278 AND trxa.tsu_code = 'ENTERED'
1279 AND txla.id = p_tal_id;
1280
1281 l_dnz_chr_id NUMBER;
1282 l_trx_date DATE;
1283 l_cleb_id NUMBER;
1284
1285 PROCEDURE raise_business_event(
1286 p_dnz_chr_id IN NUMBER,
1287 p_trx_date IN DATE,
1288 p_cle_id IN NUMBER,
1289 x_return_status OUT NOCOPY VARCHAR2
1290 )
1291 IS
1292
1293 l_parameter_list wf_parameter_list_t;
1294 BEGIN
1295
1296 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1297
1298 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID,p_dnz_chr_id,l_parameter_list);
1299 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID,p_cle_id,l_parameter_list);
1300 wf_event.AddParameterToList(G_WF_ITM_TRANS_DATE,fnd_date.date_to_canonical(p_trx_date),l_parameter_list);
1301
1302 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1303 p_init_msg_list => p_init_msg_list,
1304 x_return_status => x_return_status,
1305 x_msg_count => x_msg_count,
1306 x_msg_data => x_msg_data,
1307 p_event_name => G_WF_EVT_KHR_SPLIT_ASSET_COMP,
1308 p_parameters => l_parameter_list);
1309
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1314 END raise_business_event;
1315
1316 /*
1317 -- mvasudev, 08/24/2004
1318 -- END, PROCEDURE to enable Business Event
1319 */
1320
1321
1322 BEGIN
1323 -- Set API savepoint
1324 SAVEPOINT process_split_asset_comp_pvt;
1325
1326 -- Check for call compatibility
1327 IF (NOT FND_API.Compatible_API_Call (l_api_version,
1328 p_api_version,
1329 l_api_name,
1330 G_PKG_NAME ))
1331 THEN
1332 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1333 END IF;
1334
1335 -- Initialize message list if requested
1336 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
1337 FND_MSG_PUB.initialize;
1338 END IF;
1339
1340 -- Initialize API status to success
1341 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1342
1343
1344 /*** Begin API body ****************************************************/
1345 --
1346 -- process split asset comp code here ->
1347 ------------------------------------------------------------------------
1348 -- get the top line id from p_tal_id
1349 OPEN fin_ast_csr(p_tal_id => p_tal_id);
1350 FETCH fin_ast_csr INTO l_cle_id;
1351 IF fin_ast_csr%NOTFOUND THEN
1352 NULL;
1353 --no fin asset line found for tal_id!!!!
1354 ELSE
1355
1356 --Bug 4047504:
1357 /*
1358 -- mvasudev, 10/28/2004
1359 -- Fetch parameters for Business Event enabling
1360 */
1361 FOR l_cle_trx_rec IN l_cle_trx_csr(p_tal_id => p_tal_id)
1362 LOOP
1363 l_dnz_chr_id := l_cle_trx_rec.dnz_chr_id;
1364 l_trx_date := l_cle_trx_rec.date_trans_occurred;
1365 l_cleb_id := l_cle_trx_rec.cle_id;
1366 END LOOP;
1367 /*
1368 -- mvasudev, 10/28/2004
1369 -- END, Fetch parameters for Business Event enabling
1370 */
1371
1372 -- 1) unique asset number within ?
1373 -- 2) total split percent must = 100
1374 -- 3) same item has been apply to differnt asset number?
1375 -- validation:
1376 l_return_status := validate_split_percent(p_tal_id);
1377 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1378 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1379 x_return_status := l_return_status;
1380 END IF;
1381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 END IF;
1383
1384 OKL_SPLIT_ASSET_PUB.Split_Fixed_Asset
1385 (p_api_version => p_api_version,
1386 p_init_msg_list => p_init_msg_list,
1387 x_return_status => x_return_status,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data,
1390 p_cle_id => l_cle_id,
1391 x_cle_tbl => l_cle_tbl);
1392 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1393 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1394 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1395 RAISE OKL_API.G_EXCEPTION_ERROR;
1396 END IF;
1397 END IF;
1398 CLOSE fin_ast_csr;
1399
1400 --Bug 4047504:
1401 /*
1402 -- mvasudev, 08/24/2004
1403 -- Code change to enable Business Event
1404 */
1405 raise_business_event(p_dnz_chr_id => l_dnz_chr_id,
1406 p_trx_date => l_trx_date,
1407 p_cle_id => l_cleb_id,
1408 x_return_status => x_return_status);
1409 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1410 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1411 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1412 RAISE OKL_API.G_EXCEPTION_ERROR;
1413 END IF;
1414
1415 /*
1416 -- mvasudev, 08/24/2004
1417 -- END, Code change to enable Business Event
1418 */
1419
1420 --
1421 -- end of process split asset comp code
1422 --
1423 /*** End API body ******************************************************/
1424
1425 -- Get message count and if count is 1, get message info
1426 FND_MSG_PUB.Count_And_Get
1427 (p_count => x_msg_count,
1428 p_data => x_msg_data);
1429
1430 EXCEPTION
1431 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1432 ROLLBACK TO process_split_asset_comp_pvt;
1433 x_return_status := OKL_API.G_RET_STS_ERROR;
1434 FND_MSG_PUB.Count_And_Get
1435 (p_count => x_msg_count,
1436 p_data => x_msg_data);
1437
1438 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1439 ROLLBACK TO process_split_asset_comp_pvt;
1440 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1441 FND_MSG_PUB.Count_And_Get
1442 (p_count => x_msg_count,
1443 p_data => x_msg_data);
1444
1445 WHEN OTHERS THEN
1446 ROLLBACK TO process_split_asset_comp_pvt;
1447 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
1448 OKL_API.Set_Message(p_app_name => G_APP_NAME,
1449 p_msg_name => G_UNEXPECTED_ERROR,
1450 p_token1 => G_SQLCODE_TOKEN,
1451 p_token1_value => SQLCODE,
1452 p_token2 => G_SQLERRM_TOKEN,
1453 p_token2_value => SQLERRM);
1454 FND_MSG_PUB.Count_And_Get
1455 (p_count => x_msg_count,
1456 p_data => x_msg_data);
1457
1458 END process_split_asset_comp;
1459
1460
1461 END OKL_SPLIT_ASSET_COMP_PVT;