[Home] [Help]
PACKAGE BODY: APPS.OKL_ASD_PVT
Source
1 PACKAGE BODY OKL_ASD_PVT AS
2 /* $Header: OKLSASDB.pls 120.2.12020000.3 2012/12/18 08:48:34 vloomba ship $ */
3 -- Badrinath Kuchibholta
4 /************************ HAND-CODED *********************************/
5 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
7 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
8 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
9 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
10 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
11 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
12 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
13 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
14 -- List validation procedures for quick reference
15 --1. validate_tal_id -- Attribute Validation
16 --2. validate_asset_number -- Attribute Validation
17 --3. validate_quantity -- Attribute Validation
18 --4. validate_life_in_months_tax -- Attribute Validation
19 --5. validate_target_kle_id -- Attribute Validation
20 --7. Validate_INVENTORY_ITEM_ID -- Attribute Validation
21 --6. validate_qcs -- Tuple Record Validation
22 ----------------------------------7---------------------------------------------
23 -- Start of Commnets
24 -- chenkuang.lee
25 -- Procedure Name : Validate_INVENTORY_ITEM_ID
26 -- Description : FK validation with okx_system_items_v
27 -- Business Rules :
28 -- Parameters : OUT Return Status, IN Rec Info
29 -- Version : 1.0
30 -- End of Commnets
31
32 PROCEDURE Validate_INVENTORY_ITEM_ID(x_return_status OUT NOCOPY VARCHAR2,
33 p_asdv_rec IN asdv_rec_type) IS
34 ln_dummy number := 0;
35 CURSOR c_INVENTORY_ITEM_ID_validate(p_id number) is
36 SELECT 1
37 FROM okx_system_items_v
38 WHERE id1 = p_id;
39 --AND ID2=OKL_CONTEXT.get_okc_organization_id; -- multi-org enable
40
41 BEGIN
42 -- initialize return status
43 x_return_status := OKC_API.G_RET_STS_SUCCESS;
44 -- data is not required
45 IF (p_asdv_rec.INVENTORY_ITEM_ID <> OKC_API.G_MISS_NUM) AND
46 (p_asdv_rec.INVENTORY_ITEM_ID IS NOT NULL) THEN
47
48 -- Enforce Foreign Key
49 OPEN c_INVENTORY_ITEM_ID_validate(p_asdv_rec.INVENTORY_ITEM_ID);
50 FETCH c_INVENTORY_ITEM_ID_validate into ln_dummy;
51 CLOSE c_INVENTORY_ITEM_ID_validate;
52 IF (ln_dummy = 0) then
53 -- halt validation as it has no parent record
54 RAISE G_EXCEPTION_HALT_VALIDATION;
55 END IF;
56
57 END IF;
58
59 EXCEPTION
60 WHEN G_EXCEPTION_STOP_VALIDATION then
61 OKC_API.set_message(p_app_name => G_APP_NAME,
62 p_msg_name => G_REQUIRED_VALUE,
63 p_token1 => G_COL_NAME_TOKEN,
64 p_token1_value => 'INVENTORY_ITEM_ID');
65 -- Notify Error
66 x_return_status := OKC_API.G_RET_STS_ERROR;
67 WHEN G_EXCEPTION_HALT_VALIDATION then
68 -- We are here b'cause we have no parent record
69 -- store SQL error message on message stack
70 OKC_API.set_message(p_app_name => G_APP_NAME,
71 p_msg_name => G_NO_MATCHING_RECORD,
72 p_token1 => G_COL_NAME_TOKEN,
73 p_token1_value => 'INVENTORY_ITEM_ID');
74 -- If the cursor is open then it has to be closed
75 IF c_INVENTORY_ITEM_ID_validate%ISOPEN THEN
76 CLOSE c_INVENTORY_ITEM_ID_validate;
77 END IF;
78 -- notify caller of an error
79 x_return_status := OKC_API.G_RET_STS_ERROR;
80 WHEN OTHERS THEN
81 -- store SQL error message on message stack
82 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
83 p_msg_name => G_UNEXPECTED_ERROR,
84 p_token1 => G_SQLCODE_TOKEN,
85 p_token1_value => SQLCODE,
86 p_token2 => G_SQLERRM_TOKEN,
87 p_token2_value => SQLERRM);
88 -- If the cursor is open then it has to be closed
89 IF c_INVENTORY_ITEM_ID_validate%ISOPEN THEN
90 CLOSE c_INVENTORY_ITEM_ID_validate;
91 END IF;
92 -- notify caller of an error as UNEXPETED error
93 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
94 END Validate_INVENTORY_ITEM_ID;
95
96 --------------------------------------1-----------------------------------------
97 -- Start of Commnets
98 -- Badrinath Kuchibholta
99 -- Procedure Name : Validate_Tal_Id
100 -- Description : FK validation with OKL_TRX_ASSET_LINES_B
101 -- Business Rules :
102 -- Parameters : OUT Return Status, IN Rec Info
103 -- Version : 1.0
104 -- End of Commnets
105
106 PROCEDURE validate_tal_id(x_return_status OUT NOCOPY VARCHAR2,
107 p_asdv_rec IN asdv_rec_type) IS
108 ln_dummy number := 0;
109 CURSOR c_tal_id_validate(p_id number) is
110 SELECT 1
111 FROM DUAL
112 WHERE EXISTS (SELECT '1'
113 FROM OKL_TXL_ASSETS_V
114 WHERE id = p_id);
115
116 BEGIN
117 -- initialize return status
118 x_return_status := OKC_API.G_RET_STS_SUCCESS;
119 -- data is required
120 IF (p_asdv_rec.tal_id = OKC_API.G_MISS_NUM) OR
121 (p_asdv_rec.tal_id IS NULL) THEN
122 -- halt validation as it is a required field
123 RAISE G_EXCEPTION_STOP_VALIDATION;
124 END IF;
125 -- Enforce Foreign Key
126 OPEN c_tal_id_validate(p_asdv_rec.tal_id);
127 IF c_tal_id_validate%NOTFOUND THEN
128 -- halt validation as it has no parent record
129 RAISE G_EXCEPTION_HALT_VALIDATION;
130 END IF;
131 FETCH c_tal_id_validate into ln_dummy;
132 CLOSE c_tal_id_validate;
133 IF (ln_dummy = 0) then
134 -- halt validation as it has no parent record
135 RAISE G_EXCEPTION_HALT_VALIDATION;
136 END IF;
137 EXCEPTION
138 WHEN G_EXCEPTION_STOP_VALIDATION then
139 -- We are here since the field is required
140 -- store SQL error message on message stack
141 OKC_API.set_message(p_app_name => G_APP_NAME,
142 p_msg_name => G_REQUIRED_VALUE,
143 p_token1 => G_COL_NAME_TOKEN,
144 p_token1_value => 'tal_id');
145 -- Notify Error
146 x_return_status := OKC_API.G_RET_STS_ERROR;
147 WHEN G_EXCEPTION_HALT_VALIDATION then
148 -- We are here b'cause we have no parent record
149 -- store SQL error message on message stack
150 OKC_API.set_message(p_app_name => G_APP_NAME,
151 p_msg_name => G_NO_MATCHING_RECORD,
152 p_token1 => G_COL_NAME_TOKEN,
153 p_token1_value => 'tal_id');
154 -- If the cursor is open then it has to be closed
155 IF c_tal_id_validate%ISOPEN THEN
156 CLOSE c_tal_id_validate;
157 END IF;
158 -- notify caller of an error
159 x_return_status := OKC_API.G_RET_STS_ERROR;
160 WHEN OTHERS THEN
161 -- store SQL error message on message stack
162 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
163 p_msg_name => G_UNEXPECTED_ERROR,
164 p_token1 => G_SQLCODE_TOKEN,
165 p_token1_value => SQLCODE,
166 p_token2 => G_SQLERRM_TOKEN,
167 p_token2_value => SQLERRM);
168 -- If the cursor is open then it has to be closed
169 IF c_tal_id_validate%ISOPEN THEN
170 CLOSE c_tal_id_validate;
171 END IF;
172 -- notify caller of an error as UNEXPETED error
173 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
174 END validate_tal_id;
175 ------------------------------------3--------------------------------------------
176 -- Start of Commnets
177 -- Badrinath Kuchibholta
178 -- Procedure Name : Validate_quantity
179 -- Description : See that is more than 0
180 -- Business Rules :
181 -- Parameters : OUT Return Status, IN Rec Info
182 -- Version : 1.0
183 -- End of Commnets
184
185 PROCEDURE validate_quantity(x_return_status OUT NOCOPY VARCHAR2,
186 p_asdv_rec IN asdv_rec_type) IS
187
188 BEGIN
189 -- initialize return status
190 x_return_status := OKC_API.G_RET_STS_SUCCESS;
191 -- data is required
192 IF (p_asdv_rec.quantity = OKC_API.G_MISS_NUM) OR
193 (p_asdv_rec.quantity IS NULL) THEN
194 -- halt validation as it is a required field
195 RAISE G_EXCEPTION_STOP_VALIDATION;
196 END IF;
197 -- See that is more than 0
198 IF p_asdv_rec.quantity <= 0 THEN
199 -- halt validation
200 RAISE G_EXCEPTION_HALT_VALIDATION;
201 END IF;
202 EXCEPTION
203 WHEN G_EXCEPTION_STOP_VALIDATION then
204 -- We are here since the field is optional
205 null;
206 WHEN G_EXCEPTION_HALT_VALIDATION then
207 -- We are here b'cause not greater than zero
208 -- store SQL error message on message stack
209 OKC_API.set_message(p_app_name => G_APP_NAME,
210 p_msg_name => G_INVALID_VALUE,
211 p_token1 => G_COL_NAME_TOKEN,
212 p_token1_value => 'quantity');
213 -- notify caller of an error
214 x_return_status := OKC_API.G_RET_STS_ERROR;
215 WHEN OTHERS THEN
216 -- store SQL error message on message stack
217 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
218 p_msg_name => G_UNEXPECTED_ERROR,
219 p_token1 => G_SQLCODE_TOKEN,
220 p_token1_value => SQLCODE,
221 p_token2 => G_SQLERRM_TOKEN,
222 p_token2_value => SQLERRM);
223 -- notify caller of an error as UNEXPETED error
224 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
225 END validate_quantity;
226
227 -------------------------------------4-------------------------------------------
228 -- Start of Commnets
229 -- Badrinath Kuchibholta
230 -- Procedure Name : Validate_life_in_months_tax
231 -- Description : See that is more than 0
232 -- Business Rules :
233 -- Parameters : OUT Return Status, IN Rec Info
234 -- Version : 1.0
235 -- End of Commnets
236
237 PROCEDURE validate_life_in_months_tax(x_return_status OUT NOCOPY VARCHAR2,
238 p_asdv_rec IN asdv_rec_type) IS
239 BEGIN
240 -- initialize return status
241 x_return_status := OKC_API.G_RET_STS_SUCCESS;
242 -- data is required
243 IF (p_asdv_rec.life_in_months_tax = OKC_API.G_MISS_NUM) OR
244 (p_asdv_rec.life_in_months_tax IS NULL) THEN
245 -- halt validation as it is a required field
246 RAISE G_EXCEPTION_STOP_VALIDATION;
247 END IF;
248 -- See that is more than 0
249 IF p_asdv_rec.life_in_months_tax <= 0 THEN
250 -- halt validation
251 RAISE G_EXCEPTION_HALT_VALIDATION;
252 END IF;
253 EXCEPTION
254 WHEN G_EXCEPTION_STOP_VALIDATION then
255 -- We are here since the field is optional
256 null;
257 WHEN G_EXCEPTION_HALT_VALIDATION then
258 -- We are here b'cause not greater than zero
259 -- store SQL error message on message stack
260 OKC_API.set_message(p_app_name => G_APP_NAME,
261 p_msg_name => G_INVALID_VALUE,
262 p_token1 => G_COL_NAME_TOKEN,
263 p_token1_value => 'life_in_months_tax');
264 -- notify caller of an error
265 x_return_status := OKC_API.G_RET_STS_ERROR;
266 WHEN OTHERS THEN
267 -- store SQL error message on message stack
268 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
269 p_msg_name => G_UNEXPECTED_ERROR,
270 p_token1 => G_SQLCODE_TOKEN,
271 p_token1_value => SQLCODE,
272 p_token2 => G_SQLERRM_TOKEN,
273 p_token2_value => SQLERRM);
274 -- notify caller of an error as UNEXPETED error
275 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
276 END validate_life_in_months_tax;
277 --------------------------------------5-------------------------------------------
278 -- Start of Commnets
279 -- Badrinath Kuchibholta
280 -- Procedure Name : Validate_target_kle_id
281 -- Description : FK validation with OKL_K_LINES_V
282 -- Business Rules :
283 -- Parameters : OUT Return Status, IN Rec Info
284 -- Version : 1.0
285 -- End of Commnets
286
287 PROCEDURE validate_target_kle_id(x_return_status OUT NOCOPY VARCHAR2,
288 p_asdv_rec IN asdv_rec_type) IS
289 ln_dummy number := 0;
290 CURSOR c_target_kle_id_validate(p_id OKL_TXD_ASSETS_V.TARGET_KLE_ID%TYPE) is
291 SELECT 1
292 FROM DUAL
293 WHERE EXISTS (SELECT '1'
294 FROM OKL_K_LINES_V kle
295 WHERE kle.id = p_id);
296
297 BEGIN
298 -- initialize return status
299 x_return_status := OKC_API.G_RET_STS_SUCCESS;
300 -- data is required
301 IF (p_asdv_rec.target_kle_id = OKC_API.G_MISS_NUM) OR
302 (p_asdv_rec.target_kle_id IS NULL) THEN
303 -- halt validation as it is a required field
304 RAISE G_EXCEPTION_STOP_VALIDATION;
305 END IF;
306 -- Enforce Foreign Key
307 OPEN c_target_kle_id_validate(p_asdv_rec.target_kle_id);
308 IF c_target_kle_id_validate%NOTFOUND THEN
309 -- halt validation as it has no parent record
310 RAISE G_EXCEPTION_HALT_VALIDATION;
311 END IF;
312 FETCH c_target_kle_id_validate into ln_dummy;
313 CLOSE c_target_kle_id_validate;
314 IF (ln_dummy = 0) then
315 -- halt validation as it has no parent record
316 RAISE G_EXCEPTION_HALT_VALIDATION;
317 END IF;
318 EXCEPTION
319 WHEN G_EXCEPTION_STOP_VALIDATION then
320 -- We are here since the field is optional
321 null;
322 WHEN G_EXCEPTION_HALT_VALIDATION then
323 -- We are here b'cause we have no parent record
324 -- store SQL error message on message stack
325 OKC_API.set_message(p_app_name => G_APP_NAME,
326 p_msg_name => G_NO_MATCHING_RECORD,
327 p_token1 => G_COL_NAME_TOKEN,
328 p_token1_value => 'target_kle_id');
329 -- If the cursor is open then it has to be closed
330 IF c_target_kle_id_validate%ISOPEN THEN
331 CLOSE c_target_kle_id_validate;
332 END IF;
333 -- notify caller of an error
334 x_return_status := OKC_API.G_RET_STS_ERROR;
335 WHEN OTHERS THEN
336 -- store SQL error message on message stack
337 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
338 p_msg_name => G_UNEXPECTED_ERROR,
339 p_token1 => G_SQLCODE_TOKEN,
340 p_token1_value => SQLCODE,
341 p_token2 => G_SQLERRM_TOKEN,
342 p_token2_value => SQLERRM);
343 -- If the cursor is open then it has to be closed
344 IF c_target_kle_id_validate%ISOPEN THEN
345 CLOSE c_target_kle_id_validate;
346 END IF;
347 -- notify caller of an error as UNEXPETED error
348 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
349 END validate_target_kle_id;
350 ------------------------------19----------------------------------------------
351 -- Start of Commnets
352 -- Badrinath Kuchibholta
353 -- Procedure Name : validate_tax_book
354 -- Description : FK validation with OKX_ASST_BK_CONTROLS_V
355 -- Business Rules :
356 -- Parameters : OUT Return Status, IN Rec Info
357 -- Version : 1.0
358 -- End of Commnets
359
360 PROCEDURE validate_tax_book(x_return_status OUT NOCOPY VARCHAR2,
361 p_asdv_rec IN asdv_rec_type) IS
362
363 ln_dummy number := 0;
364 CURSOR c_tax_book_validate(p_name OKX_ASST_BK_CONTROLS_V.NAME%TYPE) is
365 SELECT 1
366 FROM DUAL
367 WHERE EXISTS (SELECT id1
368 FROM OKX_ASST_BK_CONTROLS_V
369 WHERE name = p_name
370 AND book_class = 'TAX');
371
372 BEGIN
373 -- initialize return status
374 x_return_status := OKC_API.G_RET_STS_SUCCESS;
375 -- data is required
376 IF (p_asdv_rec.tax_book = OKC_API.G_MISS_CHAR) OR
377 (p_asdv_rec.tax_book IS NULL) THEN
378 -- halt validation as it is a required field
379 RAISE G_EXCEPTION_STOP_VALIDATION;
380 END IF;
381 -- Enforce Foreign Key
382 OPEN c_tax_book_validate(p_asdv_rec.tax_book);
383 IF c_tax_book_validate%NOTFOUND THEN
384 -- halt validation as it has no parent record
385 RAISE G_EXCEPTION_HALT_VALIDATION;
386 END IF;
387 FETCH c_tax_book_validate into ln_dummy;
388 CLOSE c_tax_book_validate;
389 IF (ln_dummy = 0) then
390 -- halt validation as it has no parent record
391 RAISE G_EXCEPTION_HALT_VALIDATION;
392 END IF;
393 EXCEPTION
394 WHEN G_EXCEPTION_STOP_VALIDATION then
395 -- We are here since the field is optional
396 null;
397 WHEN G_EXCEPTION_HALT_VALIDATION then
398 -- We are here b'cause we have no parent record
399 -- store SQL error message on message stack
400 OKC_API.set_message(p_app_name => G_APP_NAME,
401 p_msg_name => G_NO_MATCHING_RECORD,
402 p_token1 => G_COL_NAME_TOKEN,
403 p_token1_value => 'tax_book');
404 IF c_tax_book_validate%ISOPEN THEN
405 CLOSE c_tax_book_validate;
406 END IF;
407 -- notify caller of an error
408 x_return_status := OKC_API.G_RET_STS_ERROR;
409 WHEN OTHERS THEN
410 -- store SQL error message on message stack
411 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
412 p_msg_name => G_UNEXPECTED_ERROR,
413 p_token1 => G_SQLCODE_TOKEN,
414 p_token1_value => SQLCODE,
415 p_token2 => G_SQLERRM_TOKEN,
416 p_token2_value => SQLERRM);
417 -- If the cursor is open then it has to be closed
418 IF c_tax_book_validate%ISOPEN THEN
419 CLOSE c_tax_book_validate;
420 END IF;
421 -- notify caller of an error as UNEXPETED error
422 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
423 END validate_tax_book;
424 --------------------------------20-------------------------------------------------
425 -- Start of Commnets
426 -- Badrinath Kuchibholta
427 -- Procedure Name : validate_dprn_mtd_tax
428 -- Description : FK validation with OKX_ASST_DEP_METHODS_V
429 -- Business Rules :
430 -- Parameters : OUT Return Status, IN Rec Info
431 -- Version : 1.0
432 -- End of Commnets
433
434 PROCEDURE validate_dprn_mtd_tax(x_return_status OUT NOCOPY VARCHAR2,
435 p_asdv_rec IN asdv_rec_type) IS
436
437 ln_dummy number := 0;
438 CURSOR c_dprn_mtd_tax_validate(p_deprn_method OKX_ASST_DEP_METHODS_V.METHOD_CODE%TYPE) is
439 SELECT 1
440 FROM DUAL
441 WHERE EXISTS (SELECT method_code
442 FROM OKX_ASST_DEP_METHODS_V
443 WHERE method_code = p_deprn_method);
444
445 BEGIN
446 -- initialize return status
447 x_return_status := OKC_API.G_RET_STS_SUCCESS;
448 -- data is required
449 IF (p_asdv_rec.deprn_method_tax = OKC_API.G_MISS_CHAR) OR
450 (p_asdv_rec.deprn_method_tax IS NULL) THEN
451 -- halt validation as it is a optional field
452 RAISE G_EXCEPTION_STOP_VALIDATION;
453 END IF;
454 -- Enforce Foreign Key
455 OPEN c_dprn_mtd_tax_validate(p_asdv_rec.deprn_method_tax);
456 IF c_dprn_mtd_tax_validate%NOTFOUND THEN
457 -- halt validation as it has no parent record
458 RAISE G_EXCEPTION_HALT_VALIDATION;
459 END IF;
460 FETCH c_dprn_mtd_tax_validate into ln_dummy;
461 CLOSE c_dprn_mtd_tax_validate;
462 IF (ln_dummy = 0) then
463 -- halt validation as it has no parent record
464 RAISE G_EXCEPTION_HALT_VALIDATION;
465 END IF;
466 EXCEPTION
467 WHEN G_EXCEPTION_STOP_VALIDATION then
468 -- We are here since the field is optional
469 null;
470 WHEN G_EXCEPTION_HALT_VALIDATION then
471 -- We are here b'cause we have no parent record
472 -- store SQL error message on message stack
473 OKC_API.set_message(p_app_name => G_APP_NAME,
474 p_msg_name => G_NO_MATCHING_RECORD,
475 p_token1 => G_COL_NAME_TOKEN,
476 p_token1_value => 'deprn_method_tax');
477 IF c_dprn_mtd_tax_validate%ISOPEN THEN
478 CLOSE c_dprn_mtd_tax_validate;
479 END IF;
480 -- notify caller of an error
481 x_return_status := OKC_API.G_RET_STS_ERROR;
482 WHEN OTHERS THEN
483 -- store SQL error message on message stack
484 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
485 p_msg_name => G_UNEXPECTED_ERROR,
486 p_token1 => G_SQLCODE_TOKEN,
487 p_token1_value => SQLCODE,
488 p_token2 => G_SQLERRM_TOKEN,
489 p_token2_value => SQLERRM);
490 -- If the cursor is open then it has to be closed
491 IF c_dprn_mtd_tax_validate%ISOPEN THEN
492 CLOSE c_dprn_mtd_tax_validate;
493 END IF;
494 -- notify caller of an error as UNEXPETED error
495 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
496 END validate_dprn_mtd_tax;
497
498 /************************ HAND-CODED *********************************/
499
500 --
501 -- Fix Bug# 2737014
502 --
503 -- Procedure Name : roundoff_line_amount
504 -- Description : Round off NOT NULL Asset Transaction line amounts
505 -- Business Rules :
506 -- Parameters :
507 -- Version : 1.0
508 -- End of comments
509
510 PROCEDURE roundoff_line_amount(
511 x_return_status OUT NOCOPY VARCHAR2,
512 x_msg_count OUT NOCOPY NUMBER,
513 x_msg_data OUT NOCOPY VARCHAR2,
514 p_asdv_rec IN asdv_rec_type,
515 x_asdv_rec OUT NOCOPY asdv_rec_type
516 ) IS
517
518 l_conv_amount NUMBER;
519 l_currency_code OKC_K_LINES_B.CURRENCY_CODE%TYPE;
520
521 roundoff_error EXCEPTION;
522
523 BEGIN
524
525 x_return_status := OKL_API.G_RET_STS_SUCCESS;
526
527 -- Take original record values
528 x_asdv_rec := p_asdv_rec;
529
530 l_currency_code := p_asdv_rec.currency_code;
531
532 IF (l_currency_code IS NULL) THEN -- Fatal error, Not a valid currency_code
533 RAISE roundoff_error;
534 END IF;
535
536 --dbms_output.put_line('Round off start '||l_currency_code);
537 -- Round off all Asset Transaction Line Amounts
538 IF (p_asdv_rec.cost IS NOT NULL
539 AND
540 p_asdv_rec.cost <> OKL_API.G_MISS_NUM) THEN
541
542 l_conv_amount := NULL;
543 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
544 p_amount => p_asdv_rec.cost,
545 p_currency_code => l_currency_code
546 );
547
548 x_asdv_rec.cost := l_conv_amount;
549 END IF;
550
551 IF (p_asdv_rec.salvage_value IS NOT NULL
552 AND
553 p_asdv_rec.salvage_value <> OKL_API.G_MISS_NUM) THEN
554
555 l_conv_amount := NULL;
556 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
557 p_amount => p_asdv_rec.salvage_value,
558 p_currency_code => l_currency_code
559 );
560
561 x_asdv_rec.salvage_value := l_conv_amount;
562 END IF;
563
564 --dbms_output.put_line('Round off complete');
565
566 EXCEPTION
567 WHEN roundoff_error THEN
568 x_return_status := OKL_API.G_RET_STS_ERROR;
569 END roundoff_line_amount;
570
571 -- Multi-Currency Change
572 --
573 -- PROCEDURE validate_currency
574 -- Decription: This procedure validates currency_code during insert and update operation
575 -- Logic:
576 -- 1. If transaction currency is NULL, take functional currency and
577 -- make rate, date and type as NULL
578 -- 2. If transaction currency is NOT NULL and
579 -- transaction currency <> functional currency and
580 -- type <> 'User' then
581 -- get conversion rate from GL and change rate column with new rate
582 -- 3. If transaction currency is NOT NULL and
583 -- transaction currency <> functional currency and
584 -- type = 'User' then
585 -- take all values as it is
586 -- 4. If transaction currency = functional currency
587 -- make rate, date and type as NULL
588 --
589 PROCEDURE validate_currency(
590 x_return_status OUT NOCOPY VARCHAR2,
591 p_asdv_rec IN asdv_rec_type,
592 x_asdv_rec OUT NOCOPY asdv_rec_type
593 ) IS
594
595 l_func_currency GL_CURRENCIES.CURRENCY_CODE%TYPE;
596 currency_validation_failed EXCEPTION;
597 l_ok VARCHAR2(1);
598 var_curr_code VARCHAR2(15);
599 var_CONVERSION_TYPE VARCHAR2(30);
600 var_CONVERSION_RATE NUMBER;
601 var_CONVERSION_RATE_DATE DATE;
602 CURSOR conv_type_csr (p_conv_type gl_daily_conversion_types.conversion_type%TYPE) IS
603 SELECT 'Y'
604 FROM gl_daily_conversion_types
605 WHERE conversion_type = p_conv_type;
606
607 CURSOR curr_csr (p_curr_code gl_currencies.currency_code%TYPE) IS
608 SELECT 'Y'
609 FROM gl_currencies
610 WHERE currency_code = p_curr_code
611 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
612 NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
613
614 BEGIN
615
616 x_asdv_rec := p_asdv_rec;
617 --Added the following for bug15956795
618 Select CURRENCY_CODE,CONVERSION_TYPE,CONVERSION_RATE,CONVERSION_RATE_DATE
619 into var_curr_code,var_CONVERSION_TYPE,var_CONVERSION_RATE,var_CONVERSION_RATE_DATE
620 from OKC_k_headers_all_b
621 where ID= (select DNZ_KHR_ID
622 from OKL_TXL_ASSETS_B
623 where id = p_asdv_rec.tal_id) ;
624
625 x_asdv_rec.currency_code := var_curr_code;
626 x_asdv_rec.currency_conversion_type :=var_CONVERSION_TYPE ;
627 x_asdv_rec.currency_conversion_rate :=var_CONVERSION_RATE;
628 x_asdv_rec.currency_conversion_date :=var_CONVERSION_RATE_DATE ;
629
630
631 --commented out the following for bug15956795
632 /* l_func_currency := okl_accounting_util.get_func_curr_code();
633
634 --dbms_output.put_line('Func Curr: '||l_func_currency);
635 --dbms_output.put_line('Trans Curr Code: '|| p_asdv_rec.currency_code);
636 --dbms_output.put_line('Trans Curr Rate: '|| p_asdv_rec.currency_conversion_rate);
637 --dbms_output.put_line('Trans Curr Date: '|| p_asdv_rec.currency_conversion_date);
638 --dbms_output.put_line('Trans Curr Type: '|| p_asdv_rec.currency_conversion_type);
639
640 IF (p_asdv_rec.currency_code IS NULL
641 OR
642 p_asdv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN -- take functional currency
643 x_asdv_rec.currency_code := l_func_currency;
644 x_asdv_rec.currency_conversion_type := NULL;
645 x_asdv_rec.currency_conversion_rate := NULL;
646 x_asdv_rec.currency_conversion_date := NULL;
647 ELSE
648
649 l_ok := '?';
650 OPEN curr_csr(p_asdv_rec.currency_code);
651 FETCH curr_csr INTO l_ok;
652 CLOSE curr_csr;
653
654 IF (l_ok <> 'Y') THEN
655 OKC_API.set_message(p_app_name => G_APP_NAME,
656 p_msg_name => G_NO_MATCHING_RECORD,
657 p_token1 => G_COL_NAME_TOKEN,
658 p_token1_value => 'currency_code');
659 x_return_status := OKC_API.G_RET_STS_ERROR;
660 RAISE currency_validation_failed;
661 END IF;
662
663 IF (p_asdv_rec.currency_code = l_func_currency) THEN -- both are same
664 x_asdv_rec.currency_conversion_type := NULL;
665 x_asdv_rec.currency_conversion_rate := NULL;
666 x_asdv_rec.currency_conversion_date := NULL;
667 ELSE -- transactional and functional currency are different
668
669 -- Conversion type, date and rate mandetory
670 IF (p_asdv_rec.currency_conversion_type IS NULL
671 OR
672 p_asdv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
673 OKC_API.set_message(
674 p_app_name => G_APP_NAME,
675 p_msg_name => G_REQUIRED_VALUE,
676 p_token1 => G_COL_NAME_TOKEN,
677 p_token1_value => 'currency_conversion_type');
678 x_return_status := OKC_API.G_RET_STS_ERROR;
679 RAISE currency_validation_failed;
680 END IF;
681
682 l_ok := '?';
683 OPEN conv_type_csr (p_asdv_rec.currency_conversion_type);
684 FETCH conv_type_csr INTO l_ok;
685 CLOSE conv_type_csr;
686
687 IF (l_ok <> 'Y') THEN
688 OKC_API.set_message(p_app_name => G_APP_NAME,
689 p_msg_name => G_NO_MATCHING_RECORD,
690 p_token1 => G_COL_NAME_TOKEN,
691 p_token1_value => 'currency_conversion_type');
692 x_return_status := OKC_API.G_RET_STS_ERROR;
693 RAISE currency_validation_failed;
694 END IF;
695
696 IF (p_asdv_rec.currency_conversion_date IS NULL
697 OR
698 p_asdv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
699 OKC_API.set_message(
700 p_app_name => G_APP_NAME,
701 p_msg_name => G_REQUIRED_VALUE,
702 p_token1 => G_COL_NAME_TOKEN,
703 p_token1_value => 'currency_conversion_date');
704 x_return_status := OKC_API.G_RET_STS_ERROR;
705 RAISE currency_validation_failed;
706 END IF;
707
708 IF (p_asdv_rec.currency_conversion_type = 'User') THEN
709
710 IF (p_asdv_rec.currency_conversion_rate IS NULL
711 OR
712 p_asdv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
713 OKC_API.set_message(
714 p_app_name => G_APP_NAME,
715 p_msg_name => G_REQUIRED_VALUE,
716 p_token1 => G_COL_NAME_TOKEN,
717 p_token1_value => 'currency_conversion_rate');
718 x_return_status := OKC_API.G_RET_STS_ERROR;
719 RAISE currency_validation_failed;
720 END IF;
721
722 x_asdv_rec.currency_conversion_type := p_asdv_rec.currency_conversion_type;
723 x_asdv_rec.currency_conversion_rate := p_asdv_rec.currency_conversion_rate;
724 x_asdv_rec.currency_conversion_date := p_asdv_rec.currency_conversion_date;
725
726 ELSE -- conversion_type <> 'User'
727
728 x_asdv_rec.currency_conversion_rate := okl_accounting_util.get_curr_con_rate(
729 p_from_curr_code => p_asdv_rec.currency_code,
730 p_to_curr_code => l_func_currency,
731 p_con_date => p_asdv_rec.currency_conversion_date,
732 p_con_type => p_asdv_rec.currency_conversion_type
733 );
734
735 x_asdv_rec.currency_conversion_type := p_asdv_rec.currency_conversion_type;
736 x_asdv_rec.currency_conversion_date := p_asdv_rec.currency_conversion_date;
737
738 END IF; -- conversion_type
739 END IF; -- currency_code check
740 END IF; -- currency_code NULL */
741
742 EXCEPTION
743 WHEN currency_validation_failed THEN
744 RETURN;
745 END validate_currency;
746 -- Multi-Currency Change
747 ---------------------------------------------------------------------------
748 -- FUNCTION get_seq_id
749 ---------------------------------------------------------------------------
750 FUNCTION get_seq_id RETURN NUMBER IS
751 BEGIN
752 RETURN(okc_p_util.raw_to_number(sys_guid()));
753 END get_seq_id;
754
755 ---------------------------------------------------------------------------
756 -- PROCEDURE qc
757 ---------------------------------------------------------------------------
758 PROCEDURE qc IS
759 BEGIN
760 null;
761 END qc;
762
763 ---------------------------------------------------------------------------
764 -- PROCEDURE change_version
765 ---------------------------------------------------------------------------
766 PROCEDURE change_version IS
767 BEGIN
768 null;
769 END change_version;
770
771 ---------------------------------------------------------------------------
772 -- PROCEDURE api_copy
773 ---------------------------------------------------------------------------
774 PROCEDURE api_copy IS
775 BEGIN
776 null;
777 END api_copy;
778 ---------------------------------------------------------------------------
779 -- PROCEDURE add_language
780 ---------------------------------------------------------------------------
781 PROCEDURE add_language IS
782 BEGIN
783 DELETE FROM OKL_TXD_ASSETS_TL T
784 WHERE NOT EXISTS (
785 SELECT NULL
786 FROM OKL_TXD_ASSETS_B B --fixed bug 3321017 by kmotepal
787 WHERE B.ID = T.ID
788 );
789
790 UPDATE OKL_TXD_ASSETS_TL T SET (
791 DESCRIPTION) = (SELECT
792 B.DESCRIPTION
793 FROM OKL_TXD_ASSETS_TL B
794 WHERE B.ID = T.ID
795 AND B.LANGUAGE = T.SOURCE_LANG)
796 WHERE (
797 T.ID,
798 T.LANGUAGE)
799 IN (SELECT
800 SUBT.ID,
801 SUBT.LANGUAGE
802 FROM OKL_TXD_ASSETS_TL SUBB, OKL_TXD_ASSETS_TL SUBT
803 WHERE SUBB.ID = SUBT.ID
804 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
805 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
806 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
807 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
808 ));
809
810 INSERT INTO OKL_TXD_ASSETS_TL (
811 ID,
812 LANGUAGE,
813 SOURCE_LANG,
814 SFWT_FLAG,
815 DESCRIPTION,
816 CREATED_BY,
817 CREATION_DATE,
818 LAST_UPDATED_BY,
819 LAST_UPDATE_DATE,
820 LAST_UPDATE_LOGIN)
821 SELECT
822 B.ID,
823 L.LANGUAGE_CODE,
824 B.SOURCE_LANG,
825 B.SFWT_FLAG,
826 B.DESCRIPTION,
827 B.CREATED_BY,
828 B.CREATION_DATE,
829 B.LAST_UPDATED_BY,
830 B.LAST_UPDATE_DATE,
831 B.LAST_UPDATE_LOGIN
832 FROM OKL_TXD_ASSETS_TL B, FND_LANGUAGES L
833 WHERE L.INSTALLED_FLAG IN ('I', 'B')
834 AND B.LANGUAGE = USERENV('LANG')
835 AND NOT EXISTS(
836 SELECT NULL
837 FROM OKL_TXD_ASSETS_TL T
838 WHERE T.ID = B.ID
839 AND T.LANGUAGE = L.LANGUAGE_CODE
840 );
841
842 END add_language;
843
844 ---------------------------------------------------------------------------
845 -- FUNCTION get_rec for: OKL_TXD_ASSETS
846 ---------------------------------------------------------------------------
847 FUNCTION get_rec (
848 p_asd_rec IN asd_rec_type,
849 x_no_data_found OUT NOCOPY BOOLEAN
850 ) RETURN asd_rec_type IS
851 CURSOR asd_pk_csr (p_id IN NUMBER) IS
852 SELECT
853 ID,
854 OBJECT_VERSION_NUMBER,
855 TAL_ID,
856 TARGET_KLE_ID,
857 LINE_DETAIL_NUMBER,
858 ASSET_NUMBER,
859 QUANTITY,
860 COST,
861 TAX_BOOK,
862 LIFE_IN_MONTHS_TAX,
863 DEPRN_METHOD_TAX,
864 DEPRN_RATE_TAX,
865 SALVAGE_VALUE,
866 -- added new columns for split asset component
867 SPLIT_PERCENT,
868 INVENTORY_ITEM_ID,
869 -- end of added new columns for split asset component
870 ATTRIBUTE_CATEGORY,
871 ATTRIBUTE1,
872 ATTRIBUTE2,
873 ATTRIBUTE3,
874 ATTRIBUTE4,
875 ATTRIBUTE5,
876 ATTRIBUTE6,
877 ATTRIBUTE7,
878 ATTRIBUTE8,
879 ATTRIBUTE9,
880 ATTRIBUTE10,
881 ATTRIBUTE11,
882 ATTRIBUTE12,
883 ATTRIBUTE13,
884 ATTRIBUTE14,
885 ATTRIBUTE15,
886 CREATED_BY,
887 CREATION_DATE,
888 LAST_UPDATED_BY,
889 LAST_UPDATE_DATE,
890 LAST_UPDATE_LOGIN,
891 -- Multi-Currency changes
892 CURRENCY_CODE,
893 CURRENCY_CONVERSION_TYPE,
894 CURRENCY_CONVERSION_RATE,
895 CURRENCY_CONVERSION_DATE
896 -- Multi-Currency changes
897 FROM Okl_Txd_Assets_b txd
898 WHERE txd.id = p_id;
899 l_asd_pk asd_pk_csr%ROWTYPE;
900 l_asd_rec asd_rec_type;
901 BEGIN
902 x_no_data_found := TRUE;
903 -- Get current database values
904 OPEN asd_pk_csr (p_asd_rec.id);
905 FETCH asd_pk_csr INTO
906 l_asd_rec.ID,
907 l_asd_rec.OBJECT_VERSION_NUMBER,
908 l_asd_rec.TAL_ID,
909 l_asd_rec.TARGET_KLE_ID,
910 l_asd_rec.LINE_DETAIL_NUMBER,
911 l_asd_rec.ASSET_NUMBER,
912 l_asd_rec.QUANTITY,
913 l_asd_rec.COST,
914 l_asd_rec.TAX_BOOK,
915 l_asd_rec.LIFE_IN_MONTHS_TAX,
916 l_asd_rec.DEPRN_METHOD_TAX,
917 l_asd_rec.DEPRN_RATE_TAX,
918 l_asd_rec.SALVAGE_VALUE,
919 -- added new columns for split asset component
920 l_asd_rec.SPLIT_PERCENT,
921 l_asd_rec.INVENTORY_ITEM_ID,
922 -- end of added new columns for split asset component
923 l_asd_rec.ATTRIBUTE_CATEGORY,
924 l_asd_rec.ATTRIBUTE1,
925 l_asd_rec.ATTRIBUTE2,
926 l_asd_rec.ATTRIBUTE3,
927 l_asd_rec.ATTRIBUTE4,
928 l_asd_rec.ATTRIBUTE5,
929 l_asd_rec.ATTRIBUTE6,
930 l_asd_rec.ATTRIBUTE7,
931 l_asd_rec.ATTRIBUTE8,
932 l_asd_rec.ATTRIBUTE9,
933 l_asd_rec.ATTRIBUTE10,
934 l_asd_rec.ATTRIBUTE11,
935 l_asd_rec.ATTRIBUTE12,
936 l_asd_rec.ATTRIBUTE13,
937 l_asd_rec.ATTRIBUTE14,
938 l_asd_rec.ATTRIBUTE15,
939 l_asd_rec.CREATED_BY,
940 l_asd_rec.CREATION_DATE,
941 l_asd_rec.LAST_UPDATED_BY,
942 l_asd_rec.LAST_UPDATE_DATE,
943 l_asd_rec.LAST_UPDATE_LOGIN,
944 -- Multi-Currency changes
945 l_asd_rec.CURRENCY_CODE,
946 l_asd_rec.CURRENCY_CONVERSION_TYPE,
947 l_asd_rec.CURRENCY_CONVERSION_RATE,
948 l_asd_rec.CURRENCY_CONVERSION_DATE;
949 -- Multi-Currency changes
950 x_no_data_found := asd_pk_csr%NOTFOUND;
951 CLOSE asd_pk_csr;
952 RETURN(l_asd_rec);
953 END get_rec;
954
955 FUNCTION get_rec (
956 p_asd_rec IN asd_rec_type
957 ) RETURN asd_rec_type IS
958 l_row_notfound BOOLEAN := TRUE;
959 BEGIN
960 RETURN(get_rec(p_asd_rec, l_row_notfound));
961 END get_rec;
962 ---------------------------------------------------------------------------
963 -- FUNCTION get_rec for: OKL_TXD_ASSETS_TL
964 ---------------------------------------------------------------------------
965 FUNCTION get_rec (
966 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
967 x_no_data_found OUT NOCOPY BOOLEAN
968 ) RETURN okl_txd_assets_tl_rec_type IS
969 CURSOR okl_txd_asset_tl_pk_csr (p_id IN NUMBER,
970 p_language IN VARCHAR2) IS
971 SELECT
972 ID,
973 LANGUAGE,
974 SOURCE_LANG,
975 SFWT_FLAG,
976 DESCRIPTION,
977 CREATED_BY,
978 CREATION_DATE,
979 LAST_UPDATED_BY,
980 LAST_UPDATE_DATE,
981 LAST_UPDATE_LOGIN
982 FROM okl_txd_assets_tl
983 WHERE okl_txd_assets_tl.id = p_id
984 AND okl_txd_assets_tl.language = p_language;
985 l_okl_txd_asset_tl_pk okl_txd_asset_tl_pk_csr%ROWTYPE;
986 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
987 BEGIN
988 x_no_data_found := TRUE;
989 -- Get current database values
990 OPEN okl_txd_asset_tl_pk_csr (p_okl_txd_assets_tl_rec.id,
991 p_okl_txd_assets_tl_rec.language);
992 FETCH okl_txd_asset_tl_pk_csr INTO
993 l_okl_txd_assets_tl_rec.ID,
994 l_okl_txd_assets_tl_rec.LANGUAGE,
995 l_okl_txd_assets_tl_rec.SOURCE_LANG,
996 l_okl_txd_assets_tl_rec.SFWT_FLAG,
997 l_okl_txd_assets_tl_rec.DESCRIPTION,
998 l_okl_txd_assets_tl_rec.CREATED_BY,
999 l_okl_txd_assets_tl_rec.CREATION_DATE,
1000 l_okl_txd_assets_tl_rec.LAST_UPDATED_BY,
1001 l_okl_txd_assets_tl_rec.LAST_UPDATE_DATE,
1002 l_okl_txd_assets_tl_rec.LAST_UPDATE_LOGIN;
1003 x_no_data_found := okl_txd_asset_tl_pk_csr%NOTFOUND;
1004 CLOSE okl_txd_asset_tl_pk_csr;
1005 RETURN(l_okl_txd_assets_tl_rec);
1006 END get_rec;
1007
1008 FUNCTION get_rec (
1009 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type
1010 ) RETURN okl_txd_assets_tl_rec_type IS
1011 l_row_notfound BOOLEAN := TRUE;
1012 BEGIN
1013 RETURN(get_rec(p_okl_txd_assets_tl_rec, l_row_notfound));
1014 END get_rec;
1015 ---------------------------------------------------------------------------
1016 -- FUNCTION get_rec for: OKL_TXD_ASSETS_V
1017 ---------------------------------------------------------------------------
1018 FUNCTION get_rec (
1019 p_asdv_rec IN asdv_rec_type,
1020 x_no_data_found OUT NOCOPY BOOLEAN
1021 ) RETURN asdv_rec_type IS
1022 CURSOR okl_asdv_pk_csr (p_id IN NUMBER) IS
1023 SELECT id,
1024 object_version_number,
1025 sfwt_flag,
1026 tal_id,
1027 target_kle_id,
1028 line_detail_number,
1029 asset_number,
1030 description,
1031 quantity,
1032 cost,
1033 tax_book,
1034 life_in_months_tax,
1035 deprn_method_tax,
1036 deprn_rate_tax,
1037 salvage_value,
1038 -- added new columns for split asset component
1039 SPLIT_PERCENT,
1040 INVENTORY_ITEM_ID,
1041 -- end of added new columns for split asset component
1042 attribute_category,
1043 attribute1,
1044 attribute2,
1045 attribute3,
1046 attribute4,
1047 attribute5,
1048 attribute6,
1049 attribute7,
1050 attribute8,
1051 attribute9,
1052 attribute10,
1053 attribute11,
1054 attribute12,
1055 attribute13,
1056 attribute14,
1057 attribute15,
1058 created_by,
1059 creation_date,
1060 last_updated_by,
1061 last_update_date,
1062 last_update_login
1063 FROM Okl_Txd_Assets_V txd
1064 WHERE txd.id = p_id;
1065 l_okl_asdv_pk okl_asdv_pk_csr%ROWTYPE;
1066 l_asdv_rec asdv_rec_type;
1067 BEGIN
1068 x_no_data_found := TRUE;
1069 -- Get current database values
1070 OPEN okl_asdv_pk_csr (p_asdv_rec.id);
1071 FETCH okl_asdv_pk_csr INTO
1072 l_asdv_rec.ID,
1073 l_asdv_rec.OBJECT_VERSION_NUMBER,
1074 l_asdv_rec.SFWT_FLAG,
1075 l_asdv_rec.TAL_ID,
1076 l_asdv_rec.TARGET_KLE_ID,
1077 l_asdv_rec.LINE_DETAIL_NUMBER,
1078 l_asdv_rec.ASSET_NUMBER,
1079 l_asdv_rec.DESCRIPTION,
1080 l_asdv_rec.QUANTITY,
1081 l_asdv_rec.COST,
1082 l_asdv_rec.TAX_BOOK,
1083 l_asdv_rec.LIFE_IN_MONTHS_TAX,
1084 l_asdv_rec.DEPRN_METHOD_TAX,
1085 l_asdv_rec.DEPRN_RATE_TAX,
1086 l_asdv_rec.SALVAGE_VALUE,
1087 -- added new columns for split asset component
1088 l_asdv_rec.SPLIT_PERCENT,
1089 l_asdv_rec.INVENTORY_ITEM_ID,
1090 -- end of added new columns for split asset component
1091 l_asdv_rec.ATTRIBUTE_CATEGORY,
1092 l_asdv_rec.ATTRIBUTE1,
1093 l_asdv_rec.ATTRIBUTE2,
1094 l_asdv_rec.ATTRIBUTE3,
1095 l_asdv_rec.ATTRIBUTE4,
1096 l_asdv_rec.ATTRIBUTE5,
1097 l_asdv_rec.ATTRIBUTE6,
1098 l_asdv_rec.ATTRIBUTE7,
1099 l_asdv_rec.ATTRIBUTE8,
1100 l_asdv_rec.ATTRIBUTE9,
1101 l_asdv_rec.ATTRIBUTE10,
1102 l_asdv_rec.ATTRIBUTE11,
1103 l_asdv_rec.ATTRIBUTE12,
1104 l_asdv_rec.ATTRIBUTE13,
1105 l_asdv_rec.ATTRIBUTE14,
1106 l_asdv_rec.ATTRIBUTE15,
1107 l_asdv_rec.CREATED_BY,
1108 l_asdv_rec.CREATION_DATE,
1109 l_asdv_rec.LAST_UPDATED_BY,
1110 l_asdv_rec.LAST_UPDATE_DATE,
1111 l_asdv_rec.LAST_UPDATE_LOGIN;
1112 x_no_data_found := okl_asdv_pk_csr%NOTFOUND;
1113 CLOSE okl_asdv_pk_csr;
1114 RETURN(l_asdv_rec);
1115 END get_rec;
1116
1117 FUNCTION get_rec (
1118 p_asdv_rec IN asdv_rec_type
1119 ) RETURN asdv_rec_type IS
1120 l_row_notfound BOOLEAN := TRUE;
1121 BEGIN
1122 RETURN(get_rec(p_asdv_rec, l_row_notfound));
1123 END get_rec;
1124
1125 ------------------------------------------------------
1126 -- FUNCTION null_out_defaults for: OKL_TXD_ASSETS_V --
1127 ------------------------------------------------------
1128 FUNCTION null_out_defaults (
1129 p_asdv_rec IN asdv_rec_type
1130 ) RETURN asdv_rec_type IS
1131 l_asdv_rec asdv_rec_type := p_asdv_rec;
1132 BEGIN
1133 IF (l_asdv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1134 l_asdv_rec.object_version_number := NULL;
1135 END IF;
1136 IF (l_asdv_rec.tal_id = OKC_API.G_MISS_NUM) THEN
1137 l_asdv_rec.tal_id := NULL;
1138 END IF;
1139 IF (l_asdv_rec.target_kle_id = OKC_API.G_MISS_NUM) THEN
1140 l_asdv_rec.target_kle_id := NULL;
1141 END IF;
1142 IF (l_asdv_rec.line_detail_number = OKC_API.G_MISS_NUM) THEN
1143 l_asdv_rec.line_detail_number := NULL;
1144 END IF;
1145 IF (l_asdv_rec.asset_number = OKC_API.G_MISS_CHAR) THEN
1146 l_asdv_rec.asset_number := NULL;
1147 END IF;
1148 IF (l_asdv_rec.description = OKC_API.G_MISS_CHAR) THEN
1149 l_asdv_rec.description := NULL;
1150 END IF;
1151 IF (l_asdv_rec.quantity = OKC_API.G_MISS_NUM) THEN
1152 l_asdv_rec.quantity := NULL;
1153 END IF;
1154 IF (l_asdv_rec.cost = OKC_API.G_MISS_NUM) THEN
1155 l_asdv_rec.cost := NULL;
1156 END IF;
1157 IF (l_asdv_rec.tax_book = OKC_API.G_MISS_CHAR) THEN
1158 l_asdv_rec.tax_book := NULL;
1159 END IF;
1160 IF (l_asdv_rec.life_in_months_tax = OKC_API.G_MISS_NUM) THEN
1161 l_asdv_rec.life_in_months_tax := NULL;
1162 END IF;
1163 IF (l_asdv_rec.deprn_method_tax = OKC_API.G_MISS_CHAR) THEN
1164 l_asdv_rec.deprn_method_tax := NULL;
1165 END IF;
1166 IF (l_asdv_rec.deprn_rate_tax = OKC_API.G_MISS_NUM) THEN
1167 l_asdv_rec.deprn_rate_tax := NULL;
1168 END IF;
1169 IF (l_asdv_rec.salvage_value = OKC_API.G_MISS_NUM) THEN
1170 l_asdv_rec.salvage_value := NULL;
1171 END IF;
1172
1173 -- added new columns for split asset component
1174 IF (l_asdv_rec.SPLIT_PERCENT = OKC_API.G_MISS_NUM) THEN
1175 l_asdv_rec.SPLIT_PERCENT := NULL;
1176 END IF;
1177 IF (l_asdv_rec.INVENTORY_ITEM_ID = OKC_API.G_MISS_NUM) THEN
1178 l_asdv_rec.INVENTORY_ITEM_ID := NULL;
1179 END IF;
1180 -- end of added new columns for split asset component
1181
1182 IF (l_asdv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
1183 l_asdv_rec.attribute_category := NULL;
1184 END IF;
1185 IF (l_asdv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1186 l_asdv_rec.attribute1 := NULL;
1187 END IF;
1188 IF (l_asdv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1189 l_asdv_rec.attribute2 := NULL;
1190 END IF;
1191 IF (l_asdv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1192 l_asdv_rec.attribute3 := NULL;
1193 END IF;
1194 IF (l_asdv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1195 l_asdv_rec.attribute4 := NULL;
1196 END IF;
1197 IF (l_asdv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1198 l_asdv_rec.attribute5 := NULL;
1199 END IF;
1200 IF (l_asdv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1201 l_asdv_rec.attribute6 := NULL;
1202 END IF;
1203 IF (l_asdv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1204 l_asdv_rec.attribute7 := NULL;
1205 END IF;
1206 IF (l_asdv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1207 l_asdv_rec.attribute8 := NULL;
1208 END IF;
1209 IF (l_asdv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1210 l_asdv_rec.attribute9 := NULL;
1211 END IF;
1212 IF (l_asdv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1213 l_asdv_rec.attribute10 := NULL;
1214 END IF;
1215 IF (l_asdv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1216 l_asdv_rec.attribute11 := NULL;
1217 END IF;
1218 IF (l_asdv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1219 l_asdv_rec.attribute12 := NULL;
1220 END IF;
1221 IF (l_asdv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1222 l_asdv_rec.attribute13 := NULL;
1223 END IF;
1224 IF (l_asdv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1225 l_asdv_rec.attribute14 := NULL;
1226 END IF;
1227 IF (l_asdv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1228 l_asdv_rec.attribute15 := NULL;
1229 END IF;
1230 IF (l_asdv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1231 l_asdv_rec.created_by := NULL;
1232 END IF;
1233 IF (l_asdv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1234 l_asdv_rec.creation_date := NULL;
1235 END IF;
1236 IF (l_asdv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1237 l_asdv_rec.last_updated_by := NULL;
1238 END IF;
1239 IF (l_asdv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1240 l_asdv_rec.last_update_date := NULL;
1241 END IF;
1242 IF (l_asdv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1243 l_asdv_rec.last_update_login := NULL;
1244 END IF;
1245 RETURN(l_asdv_rec);
1246 END null_out_defaults;
1247 ---------------------------------------------------------------------------
1248 -- PROCEDURE Validate_Attributes
1249 ---------------------------------------------------------------------------
1250 ----------------------------------------------
1251 -- Validate_Attributes for:OKL_TXD_ASSETS_V --
1252 ----------------------------------------------
1253 FUNCTION Validate_Attributes (
1254 p_asdv_rec IN asdv_rec_type
1255 ) RETURN VARCHAR2 IS
1256 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1257 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1258 BEGIN
1259 IF p_asdv_rec.id = OKC_API.G_MISS_NUM OR
1260 p_asdv_rec.id IS NULL THEN
1261 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1262 x_return_status := OKC_API.G_RET_STS_ERROR;
1263 ELSIF p_asdv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1264 p_asdv_rec.object_version_number IS NULL THEN
1265 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1266 x_return_status := OKC_API.G_RET_STS_ERROR;
1267 ELSIF p_asdv_rec.asset_number = OKC_API.G_MISS_CHAR OR
1268 p_asdv_rec.asset_number IS NULL THEN
1269 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Asset_number');
1270 x_return_status := OKC_API.G_RET_STS_ERROR;
1271 END IF;
1272 /************************ HAND-CODED *********************************/
1273 -- Calling the Validate Procedure to validate Individual Attributes
1274 validate_tal_id(x_return_status => l_return_status,
1275 p_asdv_rec => p_asdv_rec);
1276 -- Store the Highest Degree of Error
1277 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1278 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1279 x_return_status := l_return_status;
1280 END IF;
1281 END IF;
1282 l_return_status := x_return_status;
1283 validate_quantity(x_return_status => l_return_status,
1284 p_asdv_rec => p_asdv_rec);
1285 -- Store the Highest Degree of Error
1286 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1287 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1288 x_return_status := l_return_status;
1289 END IF;
1290 END IF;
1291 l_return_status := x_return_status;
1292 validate_life_in_months_tax(x_return_status => l_return_status,
1293 p_asdv_rec => p_asdv_rec);
1294 -- Store the Highest Degree of Error
1295 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1296 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1297 x_return_status := l_return_status;
1298 END IF;
1299 END IF;
1300 l_return_status := x_return_status;
1301 validate_target_kle_id(x_return_status => l_return_status,
1302 p_asdv_rec => p_asdv_rec);
1303 -- Store the Highest Degree of Error
1304 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1305 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1306 x_return_status := l_return_status;
1307 END IF;
1308 END IF;
1309 l_return_status := x_return_status;
1310 validate_tax_book(x_return_status => l_return_status,
1311 p_asdv_rec => p_asdv_rec);
1312 -- Store the Highest Degree of Error
1313 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1314 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1315 x_return_status := l_return_status;
1316 END IF;
1317 END IF;
1318 l_return_status := x_return_status;
1319 validate_dprn_mtd_tax(x_return_status => l_return_status,
1320 p_asdv_rec => p_asdv_rec);
1321 -- Store the Highest Degree of Error
1322 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1323 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1324 x_return_status := l_return_status;
1325 END IF;
1326 END IF;
1327 l_return_status := x_return_status;
1328 --
1329 Validate_INVENTORY_ITEM_ID(x_return_status => l_return_status,
1330 p_asdv_rec => p_asdv_rec);
1331 -- Store the Highest Degree of Error
1332 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1333 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1334 x_return_status := l_return_status;
1335 END IF;
1336 END IF;
1337 l_return_status := x_return_status;
1338 --
1339
1340 RETURN(l_return_status);
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 -- store SQL error message on message stack
1344 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1345 p_msg_name => G_UNEXPECTED_ERROR,
1346 p_token1 => G_SQLCODE_TOKEN,
1347 p_token1_value => SQLCODE,
1348 p_token2 => G_SQLERRM_TOKEN,
1349 p_token2_value => SQLERRM);
1350 -- notify caller of an error as UNEXPETED error
1351 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1352 -- Return status to caller
1353 RETURN(x_return_status);
1354 /************************ HAND-CODED *********************************/
1355 END Validate_Attributes;
1356
1357 ---------------------------------------------------------------------------
1358 -- PROCEDURE Validate_Record
1359 ---------------------------------------------------------------------------
1360 ------------------------------------------
1361 -- Validate_Record for:OKL_TXD_ASSETS_V --
1362 ------------------------------------------
1363 FUNCTION Validate_Record (
1364 p_asdv_rec IN asdv_rec_type
1365 ) RETURN VARCHAR2 IS
1366 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1367 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1368 BEGIN
1369 RETURN (l_return_status);
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 -- store SQL error message on message stack
1373 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1374 p_msg_name => G_UNEXPECTED_ERROR,
1375 p_token1 => G_SQLCODE_TOKEN,
1376 p_token1_value => SQLCODE,
1377 p_token2 => G_SQLERRM_TOKEN,
1378 p_token2_value => SQLERRM);
1379 -- notify caller of an error as UNEXPETED error
1380 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1381 -- Return status to caller
1382 RETURN(l_return_status);
1383 END Validate_Record;
1384 ---------------------------------------------------------------------------
1385 -- PROCEDURE Migrate
1386 ---------------------------------------------------------------------------
1387 PROCEDURE migrate (
1388 p_from IN asdv_rec_type,
1389 p_to IN OUT NOCOPY asd_rec_type
1390 ) IS
1391 BEGIN
1392 p_to.id := p_from.id;
1393 p_to.object_version_number := p_from.object_version_number;
1394 p_to.tal_id := p_from.tal_id;
1395 p_to.target_kle_id := p_from.target_kle_id;
1396 p_to.line_detail_number := p_from.line_detail_number;
1397 p_to.asset_number := p_from.asset_number;
1398 p_to.quantity := p_from.quantity;
1399 p_to.cost := p_from.cost;
1400 p_to.tax_book := p_from.tax_book;
1401 p_to.life_in_months_tax := p_from.life_in_months_tax;
1402 p_to.deprn_method_tax := p_from.deprn_method_tax;
1403 p_to.deprn_rate_tax := p_from.deprn_rate_tax;
1404 p_to.salvage_value := p_from.salvage_value;
1405 --
1406 p_to.SPLIT_PERCENT := p_from.SPLIT_PERCENT;
1407 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
1408 --
1409 p_to.attribute_category := p_from.attribute_category;
1410 p_to.attribute1 := p_from.attribute1;
1411 p_to.attribute2 := p_from.attribute2;
1412 p_to.attribute3 := p_from.attribute3;
1413 p_to.attribute4 := p_from.attribute4;
1414 p_to.attribute5 := p_from.attribute5;
1415 p_to.attribute6 := p_from.attribute6;
1416 p_to.attribute7 := p_from.attribute7;
1417 p_to.attribute8 := p_from.attribute8;
1418 p_to.attribute9 := p_from.attribute9;
1419 p_to.attribute10 := p_from.attribute10;
1420 p_to.attribute11 := p_from.attribute11;
1421 p_to.attribute12 := p_from.attribute12;
1422 p_to.attribute13 := p_from.attribute13;
1423 p_to.attribute14 := p_from.attribute14;
1424 p_to.attribute15 := p_from.attribute15;
1425 p_to.created_by := p_from.created_by;
1426 p_to.creation_date := p_from.creation_date;
1427 p_to.last_updated_by := p_from.last_updated_by;
1428 p_to.last_update_date := p_from.last_update_date;
1429 p_to.last_update_login := p_from.last_update_login;
1430 -- Multi-Currency Change
1431 p_to.currency_code := p_from.currency_code;
1432 p_to.currency_conversion_type := p_from.currency_conversion_type;
1433 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
1434 p_to.currency_conversion_date := p_from.currency_conversion_date;
1435 -- Multi-Currency Change
1436 END migrate;
1437 PROCEDURE migrate (
1438 p_from IN asd_rec_type,
1439 p_to IN OUT NOCOPY asdv_rec_type
1440 ) IS
1441 BEGIN
1442 p_to.id := p_from.id;
1443 p_to.object_version_number := p_from.object_version_number;
1444 p_to.tal_id := p_from.tal_id;
1445 p_to.target_kle_id := p_from.target_kle_id;
1446 p_to.line_detail_number := p_from.line_detail_number;
1447 p_to.asset_number := p_from.asset_number;
1448 p_to.quantity := p_from.quantity;
1449 p_to.cost := p_from.cost;
1450 p_to.tax_book := p_from.tax_book;
1451 p_to.life_in_months_tax := p_from.life_in_months_tax;
1452 p_to.deprn_method_tax := p_from.deprn_method_tax;
1453 p_to.deprn_rate_tax := p_from.deprn_rate_tax;
1454 p_to.salvage_value := p_from.salvage_value;
1455 --
1456 p_to.SPLIT_PERCENT := p_from.SPLIT_PERCENT;
1457 p_to.INVENTORY_ITEM_ID := p_from.INVENTORY_ITEM_ID;
1458 --
1459 p_to.attribute_category := p_from.attribute_category;
1460 p_to.attribute1 := p_from.attribute1;
1461 p_to.attribute2 := p_from.attribute2;
1462 p_to.attribute3 := p_from.attribute3;
1463 p_to.attribute4 := p_from.attribute4;
1464 p_to.attribute5 := p_from.attribute5;
1465 p_to.attribute6 := p_from.attribute6;
1466 p_to.attribute7 := p_from.attribute7;
1467 p_to.attribute8 := p_from.attribute8;
1468 p_to.attribute9 := p_from.attribute9;
1469 p_to.attribute10 := p_from.attribute10;
1470 p_to.attribute11 := p_from.attribute11;
1471 p_to.attribute12 := p_from.attribute12;
1472 p_to.attribute13 := p_from.attribute13;
1473 p_to.attribute14 := p_from.attribute14;
1474 p_to.attribute15 := p_from.attribute15;
1475 p_to.created_by := p_from.created_by;
1476 p_to.creation_date := p_from.creation_date;
1477 p_to.last_updated_by := p_from.last_updated_by;
1478 p_to.last_update_date := p_from.last_update_date;
1479 p_to.last_update_login := p_from.last_update_login;
1480 -- Multi-Currency Change
1481 p_to.currency_code := p_from.currency_code;
1482 p_to.currency_conversion_type := p_from.currency_conversion_type;
1483 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
1484 p_to.currency_conversion_date := p_from.currency_conversion_date;
1485 -- Multi-Currency Change
1486 END migrate;
1487 PROCEDURE migrate (
1488 p_from IN asdv_rec_type,
1489 p_to IN OUT NOCOPY okl_txd_assets_tl_rec_type
1490 ) IS
1491 BEGIN
1492 p_to.id := p_from.id;
1493 p_to.sfwt_flag := p_from.sfwt_flag;
1494 p_to.description := p_from.description;
1495 p_to.created_by := p_from.created_by;
1496 p_to.creation_date := p_from.creation_date;
1497 p_to.last_updated_by := p_from.last_updated_by;
1498 p_to.last_update_date := p_from.last_update_date;
1499 p_to.last_update_login := p_from.last_update_login;
1500 END migrate;
1501 PROCEDURE migrate (
1502 p_from IN okl_txd_assets_tl_rec_type,
1503 p_to IN OUT NOCOPY asdv_rec_type
1504 ) IS
1505 BEGIN
1506 p_to.id := p_from.id;
1507 p_to.sfwt_flag := p_from.sfwt_flag;
1508 p_to.description := p_from.description;
1509 p_to.created_by := p_from.created_by;
1510 p_to.creation_date := p_from.creation_date;
1511 p_to.last_updated_by := p_from.last_updated_by;
1512 p_to.last_update_date := p_from.last_update_date;
1513 p_to.last_update_login := p_from.last_update_login;
1514 END migrate;
1515
1516 ---------------------------------------------------------------------------
1517 -- PROCEDURE validate_row
1518 ---------------------------------------------------------------------------
1519 ---------------------------------------
1520 -- validate_row for:OKL_TXD_ASSETS_V --
1521 ---------------------------------------
1522 PROCEDURE validate_row(
1523 p_api_version IN NUMBER,
1524 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_msg_count OUT NOCOPY NUMBER,
1527 x_msg_data OUT NOCOPY VARCHAR2,
1528 p_asdv_rec IN asdv_rec_type) IS
1529
1530 l_api_version CONSTANT NUMBER := 1;
1531 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1532 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1533 l_asdv_rec asdv_rec_type := p_asdv_rec;
1534 l_asd_rec asd_rec_type;
1535 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
1536 BEGIN
1537 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1538 G_PKG_NAME,
1539 p_init_msg_list,
1540 l_api_version,
1541 p_api_version,
1542 '_PVT',
1543 x_return_status);
1544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1546 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1547 RAISE OKC_API.G_EXCEPTION_ERROR;
1548 END IF;
1549
1550 --- Validate all non-missing attributes (Item Level Validation)
1551 l_return_status := Validate_Attributes(l_asdv_rec);
1552 --- If any errors happen abort API
1553 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_ERROR;
1557 END IF;
1558 l_return_status := Validate_Record(l_asdv_rec);
1559 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1560 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1561 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1562 RAISE OKC_API.G_EXCEPTION_ERROR;
1563 END IF;
1564 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1565 EXCEPTION
1566 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1567 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1568 (
1569 l_api_name,
1570 G_PKG_NAME,
1571 'OKC_API.G_RET_STS_ERROR',
1572 x_msg_count,
1573 x_msg_data,
1574 '_PVT'
1575 );
1576 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1577 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1578 (
1579 l_api_name,
1580 G_PKG_NAME,
1581 'OKC_API.G_RET_STS_UNEXP_ERROR',
1582 x_msg_count,
1583 x_msg_data,
1584 '_PVT'
1585 );
1586 WHEN OTHERS THEN
1587 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1588 (
1589 l_api_name,
1590 G_PKG_NAME,
1591 'OTHERS',
1592 x_msg_count,
1593 x_msg_data,
1594 '_PVT'
1595 );
1596 END validate_row;
1597 ------------------------------------------
1598 -- PL/SQL TBL validate_row for:ASDV_TBL --
1599 ------------------------------------------
1600 PROCEDURE validate_row(
1601 p_api_version IN NUMBER,
1602 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2,
1606 p_asdv_tbl IN asdv_tbl_type) IS
1607
1608 l_api_version CONSTANT NUMBER := 1;
1609 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1610 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1611 i NUMBER := 0;
1612 BEGIN
1613 OKC_API.init_msg_list(p_init_msg_list);
1614 -- Make sure PL/SQL table has records in it before passing
1615 IF (p_asdv_tbl.COUNT > 0) THEN
1616 i := p_asdv_tbl.FIRST;
1617 LOOP
1618 validate_row (
1619 p_api_version => p_api_version,
1620 p_init_msg_list => OKC_API.G_FALSE,
1621 x_return_status => x_return_status,
1622 x_msg_count => x_msg_count,
1623 x_msg_data => x_msg_data,
1624 p_asdv_rec => p_asdv_tbl(i));
1625 EXIT WHEN (i = p_asdv_tbl.LAST);
1626 i := p_asdv_tbl.NEXT(i);
1627 END LOOP;
1628 END IF;
1629 EXCEPTION
1630 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1631 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1632 (
1633 l_api_name,
1634 G_PKG_NAME,
1635 'OKC_API.G_RET_STS_ERROR',
1636 x_msg_count,
1637 x_msg_data,
1638 '_PVT'
1639 );
1640 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1641 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1642 (
1643 l_api_name,
1644 G_PKG_NAME,
1645 'OKC_API.G_RET_STS_UNEXP_ERROR',
1646 x_msg_count,
1647 x_msg_data,
1648 '_PVT'
1649 );
1650 WHEN OTHERS THEN
1651 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1652 (
1653 l_api_name,
1654 G_PKG_NAME,
1655 'OTHERS',
1656 x_msg_count,
1657 x_msg_data,
1658 '_PVT'
1659 );
1660 END validate_row;
1661
1662 ---------------------------------------------------------------------------
1663 -- PROCEDURE insert_row
1664 ---------------------------------------------------------------------------
1665 -----------------------------------
1666 -- insert_row for:OKL_TXD_ASSETS_B --
1667 -----------------------------------
1668 PROCEDURE insert_row(
1669 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1670 x_return_status OUT NOCOPY VARCHAR2,
1671 x_msg_count OUT NOCOPY NUMBER,
1672 x_msg_data OUT NOCOPY VARCHAR2,
1673 p_asd_rec IN asd_rec_type,
1674 x_asd_rec OUT NOCOPY asd_rec_type) IS
1675
1676 l_api_version CONSTANT NUMBER := 1;
1677 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_insert_row';
1678 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1679 l_asd_rec asd_rec_type := p_asd_rec;
1680 l_def_asd_rec asd_rec_type;
1681 ---------------------------------------
1682 -- Set_Attributes for:OKL_TXD_ASSETS_B --
1683 ---------------------------------------
1684 FUNCTION Set_Attributes (
1685 p_asd_rec IN asd_rec_type,
1686 x_asd_rec OUT NOCOPY asd_rec_type
1687 ) RETURN VARCHAR2 IS
1688 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1689 BEGIN
1690 x_asd_rec := p_asd_rec;
1691 RETURN(l_return_status);
1692 END Set_Attributes;
1693 BEGIN
1694 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1695 p_init_msg_list,
1696 '_PVT',
1697 x_return_status);
1698 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1699 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1700 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1701 RAISE OKC_API.G_EXCEPTION_ERROR;
1702 END IF;
1703 --- Setting item attributes
1704 l_return_status := Set_Attributes(
1705 p_asd_rec, -- IN
1706 l_asd_rec); -- OUT
1707 --- If any errors happen abort API
1708 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1709 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1710 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1711 RAISE OKC_API.G_EXCEPTION_ERROR;
1712 END IF;
1713 INSERT INTO OKL_TXD_ASSETS_B(
1714 id,
1715 object_version_number,
1716 tal_id,
1717 target_kle_id,
1718 line_detail_number,
1719 asset_number,
1720 quantity,
1721 cost,
1722 tax_book,
1723 life_in_months_tax,
1724 deprn_method_tax,
1725 deprn_rate_tax,
1726 salvage_value,
1727
1728 -- added new columns for split asset component
1729 SPLIT_PERCENT,
1730 INVENTORY_ITEM_ID,
1731 -- end of added new columns for split asset component
1732
1733 attribute_category,
1734 attribute1,
1735 attribute2,
1736 attribute3,
1737 attribute4,
1738 attribute5,
1739 attribute6,
1740 attribute7,
1741 attribute8,
1742 attribute9,
1743 attribute10,
1744 attribute11,
1745 attribute12,
1746 attribute13,
1747 attribute14,
1748 attribute15,
1749 created_by,
1750 creation_date,
1751 last_updated_by,
1752 last_update_date,
1753 last_update_login,
1754 -- Multi-Currency changes
1755 currency_code,
1756 currency_conversion_type,
1757 currency_conversion_rate,
1758 currency_conversion_date)
1759 -- Multi-Currency changes
1760 VALUES (
1761 l_asd_rec.id,
1762 l_asd_rec.object_version_number,
1763 l_asd_rec.tal_id,
1764 l_asd_rec.target_kle_id,
1765 l_asd_rec.line_detail_number,
1766 l_asd_rec.asset_number,
1767 l_asd_rec.quantity,
1768 l_asd_rec.cost,
1769 l_asd_rec.tax_book,
1770 l_asd_rec.life_in_months_tax,
1771 l_asd_rec.deprn_method_tax,
1772 l_asd_rec.deprn_rate_tax,
1773 l_asd_rec.salvage_value,
1774
1775 -- added new columns for split asset component
1776 l_asd_rec.SPLIT_PERCENT,
1777 l_asd_rec.INVENTORY_ITEM_ID,
1778 -- end of added new columns for split asset component
1779
1780 l_asd_rec.attribute_category,
1781 l_asd_rec.attribute1,
1782 l_asd_rec.attribute2,
1783 l_asd_rec.attribute3,
1784 l_asd_rec.attribute4,
1785 l_asd_rec.attribute5,
1786 l_asd_rec.attribute6,
1787 l_asd_rec.attribute7,
1788 l_asd_rec.attribute8,
1789 l_asd_rec.attribute9,
1790 l_asd_rec.attribute10,
1791 l_asd_rec.attribute11,
1792 l_asd_rec.attribute12,
1793 l_asd_rec.attribute13,
1794 l_asd_rec.attribute14,
1795 l_asd_rec.attribute15,
1796 l_asd_rec.created_by,
1797 l_asd_rec.creation_date,
1798 l_asd_rec.last_updated_by,
1799 l_asd_rec.last_update_date,
1800 l_asd_rec.last_update_login,
1801 -- Multi-Currency Change
1802 l_asd_rec.currency_code,
1803 l_asd_rec.currency_conversion_type,
1804 l_asd_rec.currency_conversion_rate,
1805 l_asd_rec.currency_conversion_date);
1806 -- Multi-Currency Change
1807 -- Set OUT values
1808 x_asd_rec := l_asd_rec;
1809 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1810 EXCEPTION
1811 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1812 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1813 (
1814 l_api_name,
1815 G_PKG_NAME,
1816 'OKC_API.G_RET_STS_ERROR',
1817 x_msg_count,
1818 x_msg_data,
1819 '_PVT'
1820 );
1821 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1822 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1823 (
1824 l_api_name,
1825 G_PKG_NAME,
1826 'OKC_API.G_RET_STS_UNEXP_ERROR',
1827 x_msg_count,
1828 x_msg_data,
1829 '_PVT'
1830 );
1831 WHEN OTHERS THEN
1832 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1833 (
1834 l_api_name,
1835 G_PKG_NAME,
1836 'OTHERS',
1837 x_msg_count,
1838 x_msg_data,
1839 '_PVT'
1840 );
1841 END insert_row;
1842 --------------------------------------
1843 -- insert_row for:OKL_TXD_ASSETS_TL --
1844 --------------------------------------
1845 PROCEDURE insert_row(
1846 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1847 x_return_status OUT NOCOPY VARCHAR2,
1848 x_msg_count OUT NOCOPY NUMBER,
1849 x_msg_data OUT NOCOPY VARCHAR2,
1850 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
1851 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type) IS
1852
1853 l_api_version CONSTANT NUMBER := 1;
1854 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1855 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1856 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type := p_okl_txd_assets_tl_rec;
1857 l_def_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
1858 CURSOR get_languages IS
1859 SELECT *
1860 FROM FND_LANGUAGES
1861 WHERE INSTALLED_FLAG IN ('I', 'B');
1862 ------------------------------------------
1863 -- Set_Attributes for:OKL_TXD_ASSETS_TL --
1864 ------------------------------------------
1865 FUNCTION Set_Attributes (
1866 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
1867 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type
1868 ) RETURN VARCHAR2 IS
1869 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1870 BEGIN
1871 x_okl_txd_assets_tl_rec := p_okl_txd_assets_tl_rec;
1872 x_okl_txd_assets_tl_rec.LANGUAGE := USERENV('LANG');
1873 x_okl_txd_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
1874 RETURN(l_return_status);
1875 END Set_Attributes;
1876 BEGIN
1877 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1878 p_init_msg_list,
1879 '_PVT',
1880 x_return_status);
1881 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1882 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1883 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1884 RAISE OKC_API.G_EXCEPTION_ERROR;
1885 END IF;
1886 --- Setting item attributes
1887 l_return_status := Set_Attributes(
1888 p_okl_txd_assets_tl_rec, -- IN
1889 l_okl_txd_assets_tl_rec); -- OUT
1890 --- If any errors happen abort API
1891 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1892 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1893 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1894 RAISE OKC_API.G_EXCEPTION_ERROR;
1895 END IF;
1896 FOR l_lang_rec IN get_languages LOOP
1897 l_okl_txd_assets_tl_rec.language := l_lang_rec.language_code;
1898 INSERT INTO OKL_TXD_ASSETS_TL(
1899 id,
1900 language,
1901 source_lang,
1902 sfwt_flag,
1903 description,
1904 created_by,
1905 creation_date,
1906 last_updated_by,
1907 last_update_date,
1908 last_update_login)
1909 VALUES (
1910 l_okl_txd_assets_tl_rec.id,
1911 l_okl_txd_assets_tl_rec.language,
1912 l_okl_txd_assets_tl_rec.source_lang,
1913 l_okl_txd_assets_tl_rec.sfwt_flag,
1914 l_okl_txd_assets_tl_rec.description,
1915 l_okl_txd_assets_tl_rec.created_by,
1916 l_okl_txd_assets_tl_rec.creation_date,
1917 l_okl_txd_assets_tl_rec.last_updated_by,
1918 l_okl_txd_assets_tl_rec.last_update_date,
1919 l_okl_txd_assets_tl_rec.last_update_login);
1920 END LOOP;
1921 -- Set OUT values
1922 x_okl_txd_assets_tl_rec := l_okl_txd_assets_tl_rec;
1923 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1924 EXCEPTION
1925 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1926 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1927 (
1928 l_api_name,
1929 G_PKG_NAME,
1930 'OKC_API.G_RET_STS_ERROR',
1931 x_msg_count,
1932 x_msg_data,
1933 '_PVT'
1934 );
1935 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1936 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1937 (
1938 l_api_name,
1939 G_PKG_NAME,
1940 'OKC_API.G_RET_STS_UNEXP_ERROR',
1941 x_msg_count,
1942 x_msg_data,
1943 '_PVT'
1944 );
1945 WHEN OTHERS THEN
1946 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1947 (
1948 l_api_name,
1949 G_PKG_NAME,
1950 'OTHERS',
1951 x_msg_count,
1952 x_msg_data,
1953 '_PVT'
1954 );
1955 END insert_row;
1956 -------------------------------------
1957 -- insert_row for:OKL_TXD_ASSETS_V --
1958 -------------------------------------
1959 PROCEDURE insert_row(
1960 p_api_version IN NUMBER,
1961 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1962 x_return_status OUT NOCOPY VARCHAR2,
1963 x_msg_count OUT NOCOPY NUMBER,
1964 x_msg_data OUT NOCOPY VARCHAR2,
1965 p_asdv_rec IN asdv_rec_type,
1966 x_asdv_rec OUT NOCOPY asdv_rec_type) IS
1967
1968 l_api_version CONSTANT NUMBER := 1;
1969 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1970 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1971 l_asdv_rec asdv_rec_type;
1972 l_def_asdv_rec asdv_rec_type;
1973 l_asd_rec asd_rec_type;
1974 lx_asd_rec asd_rec_type;
1975 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
1976 lx_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
1977 lx_temp_asdv_rec asdv_rec_type;
1978 -------------------------------
1979 -- FUNCTION fill_who_columns --
1980 -------------------------------
1981 FUNCTION fill_who_columns (
1982 p_asdv_rec IN asdv_rec_type
1983 ) RETURN asdv_rec_type IS
1984 l_asdv_rec asdv_rec_type := p_asdv_rec;
1985 BEGIN
1986 l_asdv_rec.CREATION_DATE := SYSDATE;
1987 l_asdv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1988 l_asdv_rec.LAST_UPDATE_DATE := SYSDATE;
1989 l_asdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1990 l_asdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1991 RETURN(l_asdv_rec);
1992 END fill_who_columns;
1993 -----------------------------------------
1994 -- Set_Attributes for:OKL_TXD_ASSETS_V --
1995 -----------------------------------------
1996 FUNCTION Set_Attributes (
1997 p_asdv_rec IN asdv_rec_type,
1998 x_asdv_rec OUT NOCOPY asdv_rec_type
1999 ) RETURN VARCHAR2 IS
2000 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2001 BEGIN
2002 x_asdv_rec := p_asdv_rec;
2003 x_asdv_rec.OBJECT_VERSION_NUMBER := 1;
2004 x_asdv_rec.SFWT_FLAG := 'N';
2005 RETURN(l_return_status);
2006 END Set_Attributes;
2007 BEGIN
2008 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2009 G_PKG_NAME,
2010 p_init_msg_list,
2011 l_api_version,
2012 p_api_version,
2013 '_PVT',
2014 x_return_status);
2015 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2017 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2018 RAISE OKC_API.G_EXCEPTION_ERROR;
2019 END IF;
2020 l_asdv_rec := null_out_defaults(p_asdv_rec);
2021 -- Set primary key value
2022 l_asdv_rec.ID := get_seq_id;
2023 --- Setting item attributes
2024 l_return_status := Set_Attributes(
2025 l_asdv_rec, -- IN
2026 l_def_asdv_rec); -- OUT
2027 --- If any errors happen abort API
2028 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2029 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2030 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2031 RAISE OKC_API.G_EXCEPTION_ERROR;
2032 END IF;
2033 l_def_asdv_rec := fill_who_columns(l_def_asdv_rec);
2034 --- Validate all non-missing attributes (Item Level Validation)
2035 l_return_status := Validate_Attributes(l_def_asdv_rec);
2036 --- If any errors happen abort API
2037 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2038 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2039 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2040 RAISE OKC_API.G_EXCEPTION_ERROR;
2041 END IF;
2042 l_return_status := Validate_Record(l_def_asdv_rec);
2043 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2044 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2045 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2046 RAISE OKC_API.G_EXCEPTION_ERROR;
2047 END IF;
2048
2049 --
2050 -- Multi-Currency Change, dedey, 12/04/2002
2051 --
2052 validate_currency(
2053 x_return_status => l_return_status,
2054 p_asdv_rec => l_def_asdv_rec,
2055 x_asdv_rec => lx_temp_asdv_rec
2056 );
2057
2058 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2060 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2061 RAISE OKC_API.G_EXCEPTION_ERROR;
2062 END IF;
2063 l_def_asdv_rec := lx_temp_asdv_rec;
2064
2065 --dbms_output.put_line('After Change: '||lx_temp_asdv_rec.currency_code);
2066 --dbms_output.put_line('After Change: '||l_def_asdv_rec.currency_code);
2067 --
2068 -- Multi-Currency Change
2069 --
2070
2071 -- Fix Bug# 2737014
2072 --
2073 -- Round off amounts
2074 --
2075 roundoff_line_amount(
2076 x_return_status => l_return_status,
2077 x_msg_count => x_msg_count,
2078 x_msg_data => x_msg_data,
2079 p_asdv_rec => l_def_asdv_rec,
2080 x_asdv_rec => lx_temp_asdv_rec
2081 );
2082
2083 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2084 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2085 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2086 RAISE OKC_API.G_EXCEPTION_ERROR;
2087 END IF;
2088 l_def_asdv_rec := lx_temp_asdv_rec;
2089
2090 --------------------------------------
2091 -- Move VIEW record to "Child" records
2092 --------------------------------------
2093 migrate(l_def_asdv_rec, l_asd_rec);
2094 migrate(l_def_asdv_rec, l_okl_txd_assets_tl_rec);
2095 --------------------------------------------
2096 -- Call the INSERT_ROW for each child record
2097 --------------------------------------------
2098 insert_row(
2099 p_init_msg_list,
2100 x_return_status,
2101 x_msg_count,
2102 x_msg_data,
2103 l_asd_rec,
2104 lx_asd_rec
2105 );
2106 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2107 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2108 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2109 RAISE OKC_API.G_EXCEPTION_ERROR;
2110 END IF;
2111 migrate(lx_asd_rec, l_def_asdv_rec);
2112 insert_row(
2113 p_init_msg_list,
2114 x_return_status,
2115 x_msg_count,
2116 x_msg_data,
2117 l_okl_txd_assets_tl_rec,
2118 lx_okl_txd_assets_tl_rec
2119 );
2120 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2121 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2122 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2123 RAISE OKC_API.G_EXCEPTION_ERROR;
2124 END IF;
2125 migrate(lx_okl_txd_assets_tl_rec, l_def_asdv_rec);
2126 -- Set OUT values
2127 x_asdv_rec := l_def_asdv_rec;
2128 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2129 EXCEPTION
2130 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2131 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2132 (
2133 l_api_name,
2134 G_PKG_NAME,
2135 'OKC_API.G_RET_STS_ERROR',
2136 x_msg_count,
2137 x_msg_data,
2138 '_PVT'
2139 );
2140 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2141 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2142 (
2143 l_api_name,
2144 G_PKG_NAME,
2145 'OKC_API.G_RET_STS_UNEXP_ERROR',
2146 x_msg_count,
2147 x_msg_data,
2148 '_PVT'
2149 );
2150 WHEN OTHERS THEN
2151 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2152 (
2153 l_api_name,
2154 G_PKG_NAME,
2155 'OTHERS',
2156 x_msg_count,
2157 x_msg_data,
2158 '_PVT'
2159 );
2160 END insert_row;
2161 ----------------------------------------
2162 -- PL/SQL TBL insert_row for:ASDV_TBL --
2163 ----------------------------------------
2164 PROCEDURE insert_row(
2165 p_api_version IN NUMBER,
2166 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2167 x_return_status OUT NOCOPY VARCHAR2,
2168 x_msg_count OUT NOCOPY NUMBER,
2169 x_msg_data OUT NOCOPY VARCHAR2,
2170 p_asdv_tbl IN asdv_tbl_type,
2171 x_asdv_tbl OUT NOCOPY asdv_tbl_type) IS
2172
2173 l_api_version CONSTANT NUMBER := 1;
2174 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2175 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2176 i NUMBER := 0;
2177 BEGIN
2178 OKC_API.init_msg_list(p_init_msg_list);
2179 -- Make sure PL/SQL table has records in it before passing
2180 IF (p_asdv_tbl.COUNT > 0) THEN
2181 i := p_asdv_tbl.FIRST;
2182 LOOP
2183 insert_row (
2184 p_api_version => p_api_version,
2185 p_init_msg_list => OKC_API.G_FALSE,
2186 x_return_status => x_return_status,
2187 x_msg_count => x_msg_count,
2188 x_msg_data => x_msg_data,
2189 p_asdv_rec => p_asdv_tbl(i),
2190 x_asdv_rec => x_asdv_tbl(i));
2191 EXIT WHEN (i = p_asdv_tbl.LAST);
2192 i := p_asdv_tbl.NEXT(i);
2193 END LOOP;
2194 END IF;
2195 EXCEPTION
2196 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2197 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2198 (
2199 l_api_name,
2200 G_PKG_NAME,
2201 'OKC_API.G_RET_STS_ERROR',
2202 x_msg_count,
2203 x_msg_data,
2204 '_PVT'
2205 );
2206 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2207 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2208 (
2209 l_api_name,
2210 G_PKG_NAME,
2211 'OKC_API.G_RET_STS_UNEXP_ERROR',
2212 x_msg_count,
2213 x_msg_data,
2214 '_PVT'
2215 );
2216 WHEN OTHERS THEN
2217 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2218 (
2219 l_api_name,
2220 G_PKG_NAME,
2221 'OTHERS',
2222 x_msg_count,
2223 x_msg_data,
2224 '_PVT'
2225 );
2226 END insert_row;
2227
2228 ---------------------------------------------------------------------------
2229 -- PROCEDURE lock_row
2230 ---------------------------------------------------------------------------
2231 ---------------------------------
2232 -- lock_row for:OKL_TXD_ASSETS_B --
2233 ---------------------------------
2234 PROCEDURE lock_row(
2235 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2236 x_return_status OUT NOCOPY VARCHAR2,
2237 x_msg_count OUT NOCOPY NUMBER,
2238 x_msg_data OUT NOCOPY VARCHAR2,
2239 p_asd_rec IN asd_rec_type) IS
2240
2241 E_Resource_Busy EXCEPTION;
2242 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2243 CURSOR lock_csr (p_asd_rec IN asd_rec_type) IS
2244 SELECT OBJECT_VERSION_NUMBER
2245 FROM OKL_TXD_ASSETS_B
2246 WHERE ID = p_asd_rec.id
2247 AND OBJECT_VERSION_NUMBER = p_asd_rec.object_version_number
2248 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2249
2250 CURSOR lchk_csr (p_asd_rec IN asd_rec_type) IS
2251 SELECT OBJECT_VERSION_NUMBER
2252 FROM OKL_TXD_ASSETS_B
2253 WHERE ID = p_asd_rec.id;
2254 l_api_version CONSTANT NUMBER := 1;
2255 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_lock_row';
2256 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2257 l_object_version_number OKL_TXD_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
2258 lc_object_version_number OKL_TXD_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
2259 l_row_notfound BOOLEAN := FALSE;
2260 lc_row_notfound BOOLEAN := FALSE;
2261 BEGIN
2262 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2263 p_init_msg_list,
2264 '_PVT',
2265 x_return_status);
2266 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2267 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2268 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2269 RAISE OKC_API.G_EXCEPTION_ERROR;
2270 END IF;
2271 BEGIN
2272 OPEN lock_csr(p_asd_rec);
2273 FETCH lock_csr INTO l_object_version_number;
2274 l_row_notfound := lock_csr%NOTFOUND;
2275 CLOSE lock_csr;
2276 EXCEPTION
2277 WHEN E_Resource_Busy THEN
2278 IF (lock_csr%ISOPEN) THEN
2279 CLOSE lock_csr;
2280 END IF;
2281 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2282 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2283 END;
2284
2285 IF ( l_row_notfound ) THEN
2286 OPEN lchk_csr(p_asd_rec);
2287 FETCH lchk_csr INTO lc_object_version_number;
2288 lc_row_notfound := lchk_csr%NOTFOUND;
2289 CLOSE lchk_csr;
2290 END IF;
2291 IF (lc_row_notfound) THEN
2292 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2293 RAISE OKC_API.G_EXCEPTION_ERROR;
2294 ELSIF lc_object_version_number > p_asd_rec.object_version_number THEN
2295 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2296 RAISE OKC_API.G_EXCEPTION_ERROR;
2297 ELSIF lc_object_version_number <> p_asd_rec.object_version_number THEN
2298 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2299 RAISE OKC_API.G_EXCEPTION_ERROR;
2300 ELSIF lc_object_version_number = -1 THEN
2301 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2302 RAISE OKC_API.G_EXCEPTION_ERROR;
2303 END IF;
2304 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2305 EXCEPTION
2306 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2307 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2308 (
2309 l_api_name,
2310 G_PKG_NAME,
2311 'OKC_API.G_RET_STS_ERROR',
2312 x_msg_count,
2313 x_msg_data,
2314 '_PVT'
2315 );
2316 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2317 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2318 (
2319 l_api_name,
2320 G_PKG_NAME,
2321 'OKC_API.G_RET_STS_UNEXP_ERROR',
2322 x_msg_count,
2323 x_msg_data,
2324 '_PVT'
2325 );
2326 WHEN OTHERS THEN
2327 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2328 (
2329 l_api_name,
2330 G_PKG_NAME,
2331 'OTHERS',
2332 x_msg_count,
2333 x_msg_data,
2334 '_PVT'
2335 );
2336 END lock_row;
2337 ------------------------------------
2338 -- lock_row for:OKL_TXD_ASSETS_TL --
2339 ------------------------------------
2340 PROCEDURE lock_row(
2341 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2342 x_return_status OUT NOCOPY VARCHAR2,
2343 x_msg_count OUT NOCOPY NUMBER,
2344 x_msg_data OUT NOCOPY VARCHAR2,
2345 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type) IS
2346
2347 E_Resource_Busy EXCEPTION;
2348 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2349 CURSOR lock_csr (p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type) IS
2350 SELECT *
2351 FROM OKL_TXD_ASSETS_TL
2352 WHERE ID = p_okl_txd_assets_tl_rec.id
2353 FOR UPDATE NOWAIT;
2354
2355 l_api_version CONSTANT NUMBER := 1;
2356 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2357 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2358 l_lock_var lock_csr%ROWTYPE;
2359 l_row_notfound BOOLEAN := FALSE;
2360 lc_row_notfound BOOLEAN := FALSE;
2361 BEGIN
2362 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2363 p_init_msg_list,
2364 '_PVT',
2365 x_return_status);
2366 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2367 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2368 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2369 RAISE OKC_API.G_EXCEPTION_ERROR;
2370 END IF;
2371 BEGIN
2372 OPEN lock_csr(p_okl_txd_assets_tl_rec);
2373 FETCH lock_csr INTO l_lock_var;
2374 l_row_notfound := lock_csr%NOTFOUND;
2375 CLOSE lock_csr;
2376 EXCEPTION
2377 WHEN E_Resource_Busy THEN
2378 IF (lock_csr%ISOPEN) THEN
2379 CLOSE lock_csr;
2380 END IF;
2381 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2382 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2383 END;
2384
2385 IF ( l_row_notfound ) THEN
2386 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2387 RAISE OKC_API.G_EXCEPTION_ERROR;
2388 END IF;
2389 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2390 EXCEPTION
2391 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2392 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2393 (
2394 l_api_name,
2395 G_PKG_NAME,
2396 'OKC_API.G_RET_STS_ERROR',
2397 x_msg_count,
2398 x_msg_data,
2399 '_PVT'
2400 );
2401 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2402 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2403 (
2404 l_api_name,
2405 G_PKG_NAME,
2406 'OKC_API.G_RET_STS_UNEXP_ERROR',
2407 x_msg_count,
2408 x_msg_data,
2409 '_PVT'
2410 );
2411 WHEN OTHERS THEN
2412 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2413 (
2414 l_api_name,
2415 G_PKG_NAME,
2416 'OTHERS',
2417 x_msg_count,
2418 x_msg_data,
2419 '_PVT'
2420 );
2421 END lock_row;
2422 -----------------------------------
2423 -- lock_row for:OKL_TXD_ASSETS_V --
2424 -----------------------------------
2425 PROCEDURE lock_row(
2426 p_api_version IN NUMBER,
2427 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2428 x_return_status OUT NOCOPY VARCHAR2,
2429 x_msg_count OUT NOCOPY NUMBER,
2430 x_msg_data OUT NOCOPY VARCHAR2,
2431 p_asdv_rec IN asdv_rec_type) IS
2432
2433 l_api_version CONSTANT NUMBER := 1;
2434 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2435 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2436 l_asd_rec asd_rec_type;
2437 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
2438 BEGIN
2439 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2440 G_PKG_NAME,
2441 p_init_msg_list,
2442 l_api_version,
2443 p_api_version,
2444 '_PVT',
2445 x_return_status);
2446 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2447 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2448 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2449 RAISE OKC_API.G_EXCEPTION_ERROR;
2450 END IF;
2451 --------------------------------------
2452 -- Move VIEW record to "Child" records
2453 --------------------------------------
2454 migrate(p_asdv_rec, l_asd_rec);
2455 migrate(p_asdv_rec, l_okl_txd_assets_tl_rec);
2456 --------------------------------------------
2457 -- Call the LOCK_ROW for each child record
2458 --------------------------------------------
2459 lock_row(
2460 p_init_msg_list,
2461 x_return_status,
2462 x_msg_count,
2463 x_msg_data,
2464 l_asd_rec
2465 );
2466 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2468 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2469 RAISE OKC_API.G_EXCEPTION_ERROR;
2470 END IF;
2471 lock_row(
2472 p_init_msg_list,
2473 x_return_status,
2474 x_msg_count,
2475 x_msg_data,
2476 l_okl_txd_assets_tl_rec
2477 );
2478 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2479 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2480 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2481 RAISE OKC_API.G_EXCEPTION_ERROR;
2482 END IF;
2483 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2484 EXCEPTION
2485 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2486 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2487 (
2488 l_api_name,
2489 G_PKG_NAME,
2490 'OKC_API.G_RET_STS_ERROR',
2491 x_msg_count,
2492 x_msg_data,
2493 '_PVT'
2494 );
2495 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2496 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2497 (
2498 l_api_name,
2499 G_PKG_NAME,
2500 'OKC_API.G_RET_STS_UNEXP_ERROR',
2501 x_msg_count,
2502 x_msg_data,
2503 '_PVT'
2504 );
2505 WHEN OTHERS THEN
2506 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2507 (
2508 l_api_name,
2509 G_PKG_NAME,
2510 'OTHERS',
2511 x_msg_count,
2512 x_msg_data,
2513 '_PVT'
2514 );
2515 END lock_row;
2516 --------------------------------------
2517 -- PL/SQL TBL lock_row for:ASDV_TBL --
2518 --------------------------------------
2519 PROCEDURE lock_row(
2520 p_api_version IN NUMBER,
2521 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2522 x_return_status OUT NOCOPY VARCHAR2,
2523 x_msg_count OUT NOCOPY NUMBER,
2524 x_msg_data OUT NOCOPY VARCHAR2,
2525 p_asdv_tbl IN asdv_tbl_type) IS
2526
2527 l_api_version CONSTANT NUMBER := 1;
2528 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2529 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2530 i NUMBER := 0;
2531 BEGIN
2532 OKC_API.init_msg_list(p_init_msg_list);
2533 -- Make sure PL/SQL table has records in it before passing
2534 IF (p_asdv_tbl.COUNT > 0) THEN
2535 i := p_asdv_tbl.FIRST;
2536 LOOP
2537 lock_row (
2538 p_api_version => p_api_version,
2539 p_init_msg_list => OKC_API.G_FALSE,
2540 x_return_status => x_return_status,
2541 x_msg_count => x_msg_count,
2542 x_msg_data => x_msg_data,
2543 p_asdv_rec => p_asdv_tbl(i));
2544 EXIT WHEN (i = p_asdv_tbl.LAST);
2545 i := p_asdv_tbl.NEXT(i);
2546 END LOOP;
2547 END IF;
2548 EXCEPTION
2549 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2550 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2551 (
2552 l_api_name,
2553 G_PKG_NAME,
2554 'OKC_API.G_RET_STS_ERROR',
2555 x_msg_count,
2556 x_msg_data,
2557 '_PVT'
2558 );
2559 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2560 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2561 (
2562 l_api_name,
2563 G_PKG_NAME,
2564 'OKC_API.G_RET_STS_UNEXP_ERROR',
2565 x_msg_count,
2566 x_msg_data,
2567 '_PVT'
2568 );
2569 WHEN OTHERS THEN
2570 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2571 (
2572 l_api_name,
2573 G_PKG_NAME,
2574 'OTHERS',
2575 x_msg_count,
2576 x_msg_data,
2577 '_PVT'
2578 );
2579 END lock_row;
2580
2581 ---------------------------------------------------------------------------
2582 -- PROCEDURE update_row
2583 ---------------------------------------------------------------------------
2584 -----------------------------------
2585 -- update_row for:OKL_TXD_ASSETS_B --
2586 -----------------------------------
2587 PROCEDURE update_row(
2588 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2589 x_return_status OUT NOCOPY VARCHAR2,
2590 x_msg_count OUT NOCOPY NUMBER,
2591 x_msg_data OUT NOCOPY VARCHAR2,
2592 p_asd_rec IN asd_rec_type,
2593 x_asd_rec OUT NOCOPY asd_rec_type) IS
2594
2595 l_api_version CONSTANT NUMBER := 1;
2596 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_update_row';
2597 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2598 l_asd_rec asd_rec_type := p_asd_rec;
2599 l_def_asd_rec asd_rec_type;
2600 l_row_notfound BOOLEAN := TRUE;
2601 ----------------------------------
2602 -- FUNCTION populate_new_record --
2603 ----------------------------------
2604 FUNCTION populate_new_record (
2605 p_asd_rec IN asd_rec_type,
2606 x_asd_rec OUT NOCOPY asd_rec_type
2607 ) RETURN VARCHAR2 IS
2608 l_asd_rec asd_rec_type;
2609 l_row_notfound BOOLEAN := TRUE;
2610 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2611 BEGIN
2612 x_asd_rec := p_asd_rec;
2613 -- Get current database values
2614 l_asd_rec := get_rec(p_asd_rec, l_row_notfound);
2615 IF (l_row_notfound) THEN
2616 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2617 END IF;
2618 IF (x_asd_rec.id = OKC_API.G_MISS_NUM)
2619 THEN
2620 x_asd_rec.id := l_asd_rec.id;
2621 END IF;
2622 IF (x_asd_rec.object_version_number = OKC_API.G_MISS_NUM)
2623 THEN
2624 x_asd_rec.object_version_number := l_asd_rec.object_version_number;
2625 END IF;
2626 IF (x_asd_rec.tal_id = OKC_API.G_MISS_NUM)
2627 THEN
2628 x_asd_rec.tal_id := l_asd_rec.tal_id;
2629 END IF;
2630 IF (x_asd_rec.target_kle_id = OKC_API.G_MISS_NUM)
2631 THEN
2632 x_asd_rec.target_kle_id := l_asd_rec.target_kle_id;
2633 END IF;
2634 IF (x_asd_rec.line_detail_number = OKC_API.G_MISS_NUM)
2635 THEN
2636 x_asd_rec.line_detail_number := l_asd_rec.line_detail_number;
2637 END IF;
2638 IF (x_asd_rec.asset_number = OKC_API.G_MISS_CHAR)
2639 THEN
2640 x_asd_rec.asset_number := l_asd_rec.asset_number;
2641 END IF;
2642 IF (x_asd_rec.quantity = OKC_API.G_MISS_NUM)
2643 THEN
2644 x_asd_rec.quantity := l_asd_rec.quantity;
2645 END IF;
2646 IF (x_asd_rec.cost = OKC_API.G_MISS_NUM)
2647 THEN
2648 x_asd_rec.cost := l_asd_rec.cost;
2649 END IF;
2650 IF (x_asd_rec.tax_book = OKC_API.G_MISS_CHAR)
2651 THEN
2652 x_asd_rec.tax_book := l_asd_rec.tax_book;
2653 END IF;
2654 IF (x_asd_rec.life_in_months_tax = OKC_API.G_MISS_NUM)
2655 THEN
2656 x_asd_rec.life_in_months_tax := l_asd_rec.life_in_months_tax;
2657 END IF;
2658 IF (x_asd_rec.deprn_method_tax = OKC_API.G_MISS_CHAR)
2659 THEN
2660 x_asd_rec.deprn_method_tax := l_asd_rec.deprn_method_tax;
2661 END IF;
2662 IF (x_asd_rec.deprn_rate_tax = OKC_API.G_MISS_NUM)
2663 THEN
2664 x_asd_rec.deprn_rate_tax := l_asd_rec.deprn_rate_tax;
2665 END IF;
2666 IF (x_asd_rec.salvage_value = OKC_API.G_MISS_NUM)
2667 THEN
2668 x_asd_rec.salvage_value := l_asd_rec.salvage_value;
2669 END IF;
2670
2671 -- added new columns for split asset component
2672 IF (x_asd_rec.SPLIT_PERCENT = OKC_API.G_MISS_NUM)
2673 THEN
2674 x_asd_rec.SPLIT_PERCENT := l_asd_rec.SPLIT_PERCENT;
2675 END IF;
2676 IF (x_asd_rec.INVENTORY_ITEM_ID = OKC_API.G_MISS_NUM)
2677 THEN
2678 x_asd_rec.INVENTORY_ITEM_ID := l_asd_rec.INVENTORY_ITEM_ID;
2679 END IF;
2680 -- end of added new columns for split asset component
2681
2682 IF (x_asd_rec.attribute_category = OKC_API.G_MISS_CHAR)
2683 THEN
2684 x_asd_rec.attribute_category := l_asd_rec.attribute_category;
2685 END IF;
2686 IF (x_asd_rec.attribute1 = OKC_API.G_MISS_CHAR)
2687 THEN
2688 x_asd_rec.attribute1 := l_asd_rec.attribute1;
2689 END IF;
2690 IF (x_asd_rec.attribute2 = OKC_API.G_MISS_CHAR)
2691 THEN
2692 x_asd_rec.attribute2 := l_asd_rec.attribute2;
2693 END IF;
2694 IF (x_asd_rec.attribute3 = OKC_API.G_MISS_CHAR)
2695 THEN
2696 x_asd_rec.attribute3 := l_asd_rec.attribute3;
2697 END IF;
2698 IF (x_asd_rec.attribute4 = OKC_API.G_MISS_CHAR)
2699 THEN
2700 x_asd_rec.attribute4 := l_asd_rec.attribute4;
2701 END IF;
2702 IF (x_asd_rec.attribute5 = OKC_API.G_MISS_CHAR)
2703 THEN
2704 x_asd_rec.attribute5 := l_asd_rec.attribute5;
2705 END IF;
2706 IF (x_asd_rec.attribute6 = OKC_API.G_MISS_CHAR)
2707 THEN
2708 x_asd_rec.attribute6 := l_asd_rec.attribute6;
2709 END IF;
2710 IF (x_asd_rec.attribute7 = OKC_API.G_MISS_CHAR)
2711 THEN
2712 x_asd_rec.attribute7 := l_asd_rec.attribute7;
2713 END IF;
2714 IF (x_asd_rec.attribute8 = OKC_API.G_MISS_CHAR)
2715 THEN
2716 x_asd_rec.attribute8 := l_asd_rec.attribute8;
2717 END IF;
2718 IF (x_asd_rec.attribute9 = OKC_API.G_MISS_CHAR)
2719 THEN
2720 x_asd_rec.attribute9 := l_asd_rec.attribute9;
2721 END IF;
2722 IF (x_asd_rec.attribute10 = OKC_API.G_MISS_CHAR)
2723 THEN
2724 x_asd_rec.attribute10 := l_asd_rec.attribute10;
2725 END IF;
2726 IF (x_asd_rec.attribute11 = OKC_API.G_MISS_CHAR)
2727 THEN
2728 x_asd_rec.attribute11 := l_asd_rec.attribute11;
2729 END IF;
2730 IF (x_asd_rec.attribute12 = OKC_API.G_MISS_CHAR)
2731 THEN
2732 x_asd_rec.attribute12 := l_asd_rec.attribute12;
2733 END IF;
2734 IF (x_asd_rec.attribute13 = OKC_API.G_MISS_CHAR)
2735 THEN
2736 x_asd_rec.attribute13 := l_asd_rec.attribute13;
2737 END IF;
2738 IF (x_asd_rec.attribute14 = OKC_API.G_MISS_CHAR)
2739 THEN
2740 x_asd_rec.attribute14 := l_asd_rec.attribute14;
2741 END IF;
2742 IF (x_asd_rec.attribute15 = OKC_API.G_MISS_CHAR)
2743 THEN
2744 x_asd_rec.attribute15 := l_asd_rec.attribute15;
2745 END IF;
2746 IF (x_asd_rec.created_by = OKC_API.G_MISS_NUM)
2747 THEN
2748 x_asd_rec.created_by := l_asd_rec.created_by;
2749 END IF;
2750 IF (x_asd_rec.creation_date = OKC_API.G_MISS_DATE)
2751 THEN
2752 x_asd_rec.creation_date := l_asd_rec.creation_date;
2753 END IF;
2754 IF (x_asd_rec.last_updated_by = OKC_API.G_MISS_NUM)
2755 THEN
2756 x_asd_rec.last_updated_by := l_asd_rec.last_updated_by;
2757 END IF;
2758 IF (x_asd_rec.last_update_date = OKC_API.G_MISS_DATE)
2759 THEN
2760 x_asd_rec.last_update_date := l_asd_rec.last_update_date;
2761 END IF;
2762 IF (x_asd_rec.last_update_login = OKC_API.G_MISS_NUM)
2763 THEN
2764 x_asd_rec.last_update_login := l_asd_rec.last_update_login;
2765 END IF;
2766 -- Multi Currency Change
2767 IF (x_asd_rec.currency_code = OKC_API.G_MISS_CHAR)
2768 THEN
2769 x_asd_rec.currency_code := l_asd_rec.currency_code;
2770 END IF;
2771 IF (x_asd_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
2772 THEN
2773 x_asd_rec.currency_conversion_type := l_asd_rec.currency_conversion_type;
2774 END IF;
2775 IF (x_asd_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
2776 THEN
2777 x_asd_rec.currency_conversion_rate := l_asd_rec.currency_conversion_rate;
2778 END IF;
2779 IF (x_asd_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
2780 THEN
2781 x_asd_rec.currency_conversion_date := l_asd_rec.currency_conversion_date;
2782 END IF;
2783 -- Multi Currency Change
2784 RETURN(l_return_status);
2785 END populate_new_record;
2786 ---------------------------------------
2787 -- Set_Attributes for:OKL_TXD_ASSETS_B --
2788 ---------------------------------------
2789 FUNCTION Set_Attributes (
2790 p_asd_rec IN asd_rec_type,
2791 x_asd_rec OUT NOCOPY asd_rec_type
2792 ) RETURN VARCHAR2 IS
2793 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2794 BEGIN
2795 x_asd_rec := p_asd_rec;
2796 RETURN(l_return_status);
2797 END Set_Attributes;
2798 BEGIN
2799 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2800 p_init_msg_list,
2801 '_PVT',
2802 x_return_status);
2803 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2804 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2805 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2806 RAISE OKC_API.G_EXCEPTION_ERROR;
2807 END IF;
2808 --- Setting item attributes
2809 l_return_status := Set_Attributes(
2810 p_asd_rec, -- IN
2811 l_asd_rec); -- OUT
2812 --- If any errors happen abort API
2813 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2814 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2815 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2816 RAISE OKC_API.G_EXCEPTION_ERROR;
2817 END IF;
2818 l_return_status := populate_new_record(l_asd_rec, l_def_asd_rec);
2819 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2820 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2821 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2822 RAISE OKC_API.G_EXCEPTION_ERROR;
2823 END IF;
2824 UPDATE OKL_TXD_ASSETS_B
2825 SET OBJECT_VERSION_NUMBER = l_def_asd_rec.object_version_number,
2826 TAL_ID = l_def_asd_rec.tal_id,
2827 TARGET_KLE_ID = l_def_asd_rec.target_kle_id,
2828 LINE_DETAIL_NUMBER = l_def_asd_rec.line_detail_number,
2829 ASSET_NUMBER = l_def_asd_rec.asset_number,
2830 QUANTITY = l_def_asd_rec.quantity,
2831 COST = l_def_asd_rec.cost,
2832 TAX_BOOK = l_def_asd_rec.tax_book,
2833 LIFE_IN_MONTHS_TAX = l_def_asd_rec.life_in_months_tax,
2834 DEPRN_METHOD_TAX = l_def_asd_rec.deprn_method_tax,
2835 DEPRN_RATE_TAX = l_def_asd_rec.deprn_rate_tax,
2836 SALVAGE_VALUE = l_def_asd_rec.salvage_value,
2837
2838 -- added new columns for split asset component
2839 SPLIT_PERCENT = l_def_asd_rec.SPLIT_PERCENT,
2840 INVENTORY_ITEM_ID = l_def_asd_rec.INVENTORY_ITEM_ID,
2841 -- end of added new columns for split asset component
2842
2843 ATTRIBUTE_CATEGORY = l_def_asd_rec.attribute_category,
2844 ATTRIBUTE1 = l_def_asd_rec.attribute1,
2845 ATTRIBUTE2 = l_def_asd_rec.attribute2,
2846 ATTRIBUTE3 = l_def_asd_rec.attribute3,
2847 ATTRIBUTE4 = l_def_asd_rec.attribute4,
2848 ATTRIBUTE5 = l_def_asd_rec.attribute5,
2849 ATTRIBUTE6 = l_def_asd_rec.attribute6,
2850 ATTRIBUTE7 = l_def_asd_rec.attribute7,
2851 ATTRIBUTE8 = l_def_asd_rec.attribute8,
2852 ATTRIBUTE9 = l_def_asd_rec.attribute9,
2853 ATTRIBUTE10 = l_def_asd_rec.attribute10,
2854 ATTRIBUTE11 = l_def_asd_rec.attribute11,
2855 ATTRIBUTE12 = l_def_asd_rec.attribute12,
2856 ATTRIBUTE13 = l_def_asd_rec.attribute13,
2857 ATTRIBUTE14 = l_def_asd_rec.attribute14,
2858 ATTRIBUTE15 = l_def_asd_rec.attribute15,
2859 CREATED_BY = l_def_asd_rec.created_by,
2860 CREATION_DATE = l_def_asd_rec.creation_date,
2861 LAST_UPDATED_BY = l_def_asd_rec.last_updated_by,
2862 LAST_UPDATE_DATE = l_def_asd_rec.last_update_date,
2863 LAST_UPDATE_LOGIN = l_def_asd_rec.last_update_login,
2864 CURRENCY_CODE = l_def_asd_rec.currency_code,
2865 CURRENCY_CONVERSION_TYPE = l_def_asd_rec.currency_conversion_type,
2866 CURRENCY_CONVERSION_RATE = l_def_asd_rec.currency_conversion_rate,
2867 CURRENCY_CONVERSION_DATE = l_def_asd_rec.currency_conversion_date
2868 WHERE ID = l_def_asd_rec.id;
2869
2870 x_asd_rec := l_def_asd_rec;
2871 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2872 EXCEPTION
2873 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2874 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2875 (
2876 l_api_name,
2877 G_PKG_NAME,
2878 'OKC_API.G_RET_STS_ERROR',
2879 x_msg_count,
2880 x_msg_data,
2881 '_PVT'
2882 );
2883 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2884 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2885 (
2886 l_api_name,
2887 G_PKG_NAME,
2888 'OKC_API.G_RET_STS_UNEXP_ERROR',
2889 x_msg_count,
2890 x_msg_data,
2891 '_PVT'
2892 );
2893 WHEN OTHERS THEN
2894 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2895 (
2896 l_api_name,
2897 G_PKG_NAME,
2898 'OTHERS',
2899 x_msg_count,
2900 x_msg_data,
2901 '_PVT'
2902 );
2903 END update_row;
2904 --------------------------------------
2905 -- update_row for:OKL_TXD_ASSETS_TL --
2906 --------------------------------------
2907 PROCEDURE update_row(
2908 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2909 x_return_status OUT NOCOPY VARCHAR2,
2910 x_msg_count OUT NOCOPY NUMBER,
2911 x_msg_data OUT NOCOPY VARCHAR2,
2912 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
2913 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type) IS
2914
2915 l_api_version CONSTANT NUMBER := 1;
2916 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2917 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2918 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type := p_okl_txd_assets_tl_rec;
2919 l_def_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
2920 l_row_notfound BOOLEAN := TRUE;
2921 ----------------------------------
2922 -- FUNCTION populate_new_record --
2923 ----------------------------------
2924 FUNCTION populate_new_record (
2925 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
2926 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type
2927 ) RETURN VARCHAR2 IS
2928 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
2929 l_row_notfound BOOLEAN := TRUE;
2930 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2931 BEGIN
2932 x_okl_txd_assets_tl_rec := p_okl_txd_assets_tl_rec;
2933 -- Get current database values
2934 l_okl_txd_assets_tl_rec := get_rec(p_okl_txd_assets_tl_rec, l_row_notfound);
2935 IF (l_row_notfound) THEN
2936 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2937 END IF;
2938 IF (x_okl_txd_assets_tl_rec.id = OKC_API.G_MISS_NUM)
2939 THEN
2940 x_okl_txd_assets_tl_rec.id := l_okl_txd_assets_tl_rec.id;
2941 END IF;
2942 IF (x_okl_txd_assets_tl_rec.language = OKC_API.G_MISS_CHAR)
2943 THEN
2944 x_okl_txd_assets_tl_rec.language := l_okl_txd_assets_tl_rec.language;
2945 END IF;
2946 IF (x_okl_txd_assets_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2947 THEN
2948 x_okl_txd_assets_tl_rec.source_lang := l_okl_txd_assets_tl_rec.source_lang;
2949 END IF;
2950 IF (x_okl_txd_assets_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2951 THEN
2952 x_okl_txd_assets_tl_rec.sfwt_flag := l_okl_txd_assets_tl_rec.sfwt_flag;
2953 END IF;
2954 IF (x_okl_txd_assets_tl_rec.description = OKC_API.G_MISS_CHAR)
2955 THEN
2956 x_okl_txd_assets_tl_rec.description := l_okl_txd_assets_tl_rec.description;
2957 END IF;
2958 IF (x_okl_txd_assets_tl_rec.created_by = OKC_API.G_MISS_NUM)
2959 THEN
2960 x_okl_txd_assets_tl_rec.created_by := l_okl_txd_assets_tl_rec.created_by;
2961 END IF;
2962 IF (x_okl_txd_assets_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2963 THEN
2964 x_okl_txd_assets_tl_rec.creation_date := l_okl_txd_assets_tl_rec.creation_date;
2965 END IF;
2966 IF (x_okl_txd_assets_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2967 THEN
2968 x_okl_txd_assets_tl_rec.last_updated_by := l_okl_txd_assets_tl_rec.last_updated_by;
2969 END IF;
2970 IF (x_okl_txd_assets_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2971 THEN
2972 x_okl_txd_assets_tl_rec.last_update_date := l_okl_txd_assets_tl_rec.last_update_date;
2973 END IF;
2974 IF (x_okl_txd_assets_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2975 THEN
2976 x_okl_txd_assets_tl_rec.last_update_login := l_okl_txd_assets_tl_rec.last_update_login;
2977 END IF;
2978 /*
2979 -- Multi Currency Change
2980 IF (x_okl_txd_assets_tl_rec.currency_code = OKC_API.G_MISS_CHAR)
2981 THEN
2982 x_okl_txd_assets_tl_rec.currency_code := l_okl_txd_assets_tl_rec.currency_code;
2983 END IF;
2984 IF (x_okl_txd_assets_tl_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
2985 THEN
2986 x_okl_txd_assets_tl_rec.currency_conversion_type := l_okl_txd_assets_tl_rec.currency_conversion_type;
2987 END IF;
2988 IF (x_okl_txd_assets_tl_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
2989 THEN
2990 x_okl_txd_assets_tl_rec.currency_conversion_rate := l_okl_txd_assets_tl_rec.currency_conversion_rate;
2991 END IF;
2992 IF (x_okl_txd_assets_tl_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
2993 THEN
2994 x_okl_txd_assets_tl_rec.currency_conversion_date := l_okl_txd_assets_tl_rec.currency_conversion_date;
2995 END IF;
2996 -- Multi Currency Change
2997 */
2998 RETURN(l_return_status);
2999 END populate_new_record;
3000 ------------------------------------------
3001 -- Set_Attributes for:OKL_TXD_ASSETS_TL --
3002 ------------------------------------------
3003 FUNCTION Set_Attributes (
3004 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
3005 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type
3006 ) RETURN VARCHAR2 IS
3007 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3008 BEGIN
3009 x_okl_txd_assets_tl_rec := p_okl_txd_assets_tl_rec;
3010 x_okl_txd_assets_tl_rec.LANGUAGE := USERENV('LANG');
3011 x_okl_txd_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
3012 RETURN(l_return_status);
3013 END Set_Attributes;
3014 BEGIN
3015 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3016 p_init_msg_list,
3017 '_PVT',
3018 x_return_status);
3019 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3020 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3021 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3022 RAISE OKC_API.G_EXCEPTION_ERROR;
3023 END IF;
3024 --- Setting item attributes
3025 l_return_status := Set_Attributes(
3026 p_okl_txd_assets_tl_rec, -- IN
3027 l_okl_txd_assets_tl_rec); -- OUT
3028 --- If any errors happen abort API
3029 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3031 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3032 RAISE OKC_API.G_EXCEPTION_ERROR;
3033 END IF;
3034 l_return_status := populate_new_record(l_okl_txd_assets_tl_rec, l_def_okl_txd_assets_tl_rec);
3035 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3038 RAISE OKC_API.G_EXCEPTION_ERROR;
3039 END IF;
3040 UPDATE OKL_TXD_ASSETS_TL
3041 SET DESCRIPTION = l_def_okl_txd_assets_tl_rec.description,
3042 --Bug# 3641933 :
3043 SOURCE_LANG = l_def_okl_txd_assets_tl_rec.source_lang,
3044 CREATED_BY = l_def_okl_txd_assets_tl_rec.created_by,
3045 CREATION_DATE = l_def_okl_txd_assets_tl_rec.creation_date,
3046 LAST_UPDATED_BY = l_def_okl_txd_assets_tl_rec.last_updated_by,
3047 LAST_UPDATE_DATE = l_def_okl_txd_assets_tl_rec.last_update_date,
3048 LAST_UPDATE_LOGIN = l_def_okl_txd_assets_tl_rec.last_update_login
3049 WHERE ID = l_def_okl_txd_assets_tl_rec.id
3050 --Bug# 3641933 :
3051 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
3052 --AND SOURCE_LANG = USERENV('LANG');
3053
3054 UPDATE OKL_TXD_ASSETS_TL
3055 SET SFWT_FLAG = 'Y'
3056 WHERE ID = l_def_okl_txd_assets_tl_rec.id
3057 AND SOURCE_LANG <> USERENV('LANG');
3058
3059 x_okl_txd_assets_tl_rec := l_def_okl_txd_assets_tl_rec;
3060 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3061 EXCEPTION
3062 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3063 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3064 (
3065 l_api_name,
3066 G_PKG_NAME,
3067 'OKC_API.G_RET_STS_ERROR',
3068 x_msg_count,
3069 x_msg_data,
3070 '_PVT'
3071 );
3072 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3073 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3074 (
3075 l_api_name,
3076 G_PKG_NAME,
3077 'OKC_API.G_RET_STS_UNEXP_ERROR',
3078 x_msg_count,
3079 x_msg_data,
3080 '_PVT'
3081 );
3082 WHEN OTHERS THEN
3083 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3084 (
3085 l_api_name,
3086 G_PKG_NAME,
3087 'OTHERS',
3088 x_msg_count,
3089 x_msg_data,
3090 '_PVT'
3091 );
3092 END update_row;
3093 -------------------------------------
3094 -- update_row for:OKL_TXD_ASSETS_V --
3095 -------------------------------------
3096 PROCEDURE update_row(
3097 p_api_version IN NUMBER,
3098 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3099 x_return_status OUT NOCOPY VARCHAR2,
3100 x_msg_count OUT NOCOPY NUMBER,
3101 x_msg_data OUT NOCOPY VARCHAR2,
3102 p_asdv_rec IN asdv_rec_type,
3103 x_asdv_rec OUT NOCOPY asdv_rec_type) IS
3104
3105 l_api_version CONSTANT NUMBER := 1;
3106 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3107 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3108 l_asdv_rec asdv_rec_type := p_asdv_rec;
3109 l_def_asdv_rec asdv_rec_type;
3110 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
3111 lx_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
3112 l_asd_rec asd_rec_type;
3113 lx_asd_rec asd_rec_type;
3114 lx_temp_asdv_rec asdv_rec_type;
3115 -------------------------------
3116 -- FUNCTION fill_who_columns --
3117 -------------------------------
3118 FUNCTION fill_who_columns (
3119 p_asdv_rec IN asdv_rec_type
3120 ) RETURN asdv_rec_type IS
3121 l_asdv_rec asdv_rec_type := p_asdv_rec;
3122 BEGIN
3123 l_asdv_rec.LAST_UPDATE_DATE := SYSDATE;
3124 l_asdv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3125 l_asdv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3126 RETURN(l_asdv_rec);
3127 END fill_who_columns;
3128 ----------------------------------
3129 -- FUNCTION populate_new_record --
3130 ----------------------------------
3131 FUNCTION populate_new_record (
3132 p_asdv_rec IN asdv_rec_type,
3133 x_asdv_rec OUT NOCOPY asdv_rec_type
3134 ) RETURN VARCHAR2 IS
3135 l_asdv_rec asdv_rec_type;
3136 l_row_notfound BOOLEAN := TRUE;
3137 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3138 BEGIN
3139 x_asdv_rec := p_asdv_rec;
3140 -- Get current database values
3141 l_asdv_rec := get_rec(p_asdv_rec, l_row_notfound);
3142 IF (l_row_notfound) THEN
3143 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3144 END IF;
3145 IF (x_asdv_rec.id = OKC_API.G_MISS_NUM)
3146 THEN
3147 x_asdv_rec.id := l_asdv_rec.id;
3148 END IF;
3149 IF (x_asdv_rec.object_version_number = OKC_API.G_MISS_NUM)
3150 THEN
3151 x_asdv_rec.object_version_number := l_asdv_rec.object_version_number;
3152 END IF;
3153 IF (x_asdv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3154 THEN
3155 x_asdv_rec.sfwt_flag := l_asdv_rec.sfwt_flag;
3156 END IF;
3157 IF (x_asdv_rec.tal_id = OKC_API.G_MISS_NUM)
3158 THEN
3159 x_asdv_rec.tal_id := l_asdv_rec.tal_id;
3160 END IF;
3161 IF (x_asdv_rec.target_kle_id = OKC_API.G_MISS_NUM)
3162 THEN
3163 x_asdv_rec.target_kle_id := l_asdv_rec.target_kle_id;
3164 END IF;
3165 IF (x_asdv_rec.line_detail_number = OKC_API.G_MISS_NUM)
3166 THEN
3167 x_asdv_rec.line_detail_number := l_asdv_rec.line_detail_number;
3168 END IF;
3169 IF (x_asdv_rec.asset_number = OKC_API.G_MISS_CHAR)
3170 THEN
3171 x_asdv_rec.asset_number := l_asdv_rec.asset_number;
3172 END IF;
3173 IF (x_asdv_rec.description = OKC_API.G_MISS_CHAR)
3174 THEN
3175 x_asdv_rec.description := l_asdv_rec.description;
3176 END IF;
3177 IF (x_asdv_rec.quantity = OKC_API.G_MISS_NUM)
3178 THEN
3179 x_asdv_rec.quantity := l_asdv_rec.quantity;
3180 END IF;
3181 IF (x_asdv_rec.cost = OKC_API.G_MISS_NUM)
3182 THEN
3183 x_asdv_rec.cost := l_asdv_rec.cost;
3184 END IF;
3185 IF (x_asdv_rec.tax_book = OKC_API.G_MISS_CHAR)
3186 THEN
3187 x_asdv_rec.tax_book := l_asdv_rec.tax_book;
3188 END IF;
3189 IF (x_asdv_rec.life_in_months_tax = OKC_API.G_MISS_NUM)
3190 THEN
3191 x_asdv_rec.life_in_months_tax := l_asdv_rec.life_in_months_tax;
3192 END IF;
3193 IF (x_asdv_rec.deprn_method_tax = OKC_API.G_MISS_CHAR)
3194 THEN
3195 x_asdv_rec.deprn_method_tax := l_asdv_rec.deprn_method_tax;
3196 END IF;
3197 IF (x_asdv_rec.deprn_rate_tax = OKC_API.G_MISS_NUM)
3198 THEN
3199 x_asdv_rec.deprn_rate_tax := l_asdv_rec.deprn_rate_tax;
3200 END IF;
3201 IF (x_asdv_rec.salvage_value = OKC_API.G_MISS_NUM)
3202 THEN
3203 x_asdv_rec.salvage_value := l_asdv_rec.salvage_value;
3204 END IF;
3205
3206 -- added new columns for split asset component
3207 IF (x_asdv_rec.SPLIT_PERCENT = OKC_API.G_MISS_NUM)
3208 THEN
3209 x_asdv_rec.SPLIT_PERCENT := l_asdv_rec.SPLIT_PERCENT;
3210 END IF;
3211 IF (x_asdv_rec.INVENTORY_ITEM_ID = OKC_API.G_MISS_NUM)
3212 THEN
3213 x_asdv_rec.INVENTORY_ITEM_ID := l_asdv_rec.INVENTORY_ITEM_ID;
3214 END IF;
3215 -- end of added new columns for split asset component
3216
3217 IF (x_asdv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3218 THEN
3219 x_asdv_rec.attribute_category := l_asdv_rec.attribute_category;
3220 END IF;
3221 IF (x_asdv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3222 THEN
3223 x_asdv_rec.attribute1 := l_asdv_rec.attribute1;
3224 END IF;
3225 IF (x_asdv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3226 THEN
3227 x_asdv_rec.attribute2 := l_asdv_rec.attribute2;
3228 END IF;
3229 IF (x_asdv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3230 THEN
3231 x_asdv_rec.attribute3 := l_asdv_rec.attribute3;
3232 END IF;
3233 IF (x_asdv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3234 THEN
3235 x_asdv_rec.attribute4 := l_asdv_rec.attribute4;
3236 END IF;
3237 IF (x_asdv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3238 THEN
3239 x_asdv_rec.attribute5 := l_asdv_rec.attribute5;
3240 END IF;
3241 IF (x_asdv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3242 THEN
3243 x_asdv_rec.attribute6 := l_asdv_rec.attribute6;
3244 END IF;
3245 IF (x_asdv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3246 THEN
3247 x_asdv_rec.attribute7 := l_asdv_rec.attribute7;
3248 END IF;
3249 IF (x_asdv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3250 THEN
3251 x_asdv_rec.attribute8 := l_asdv_rec.attribute8;
3252 END IF;
3253 IF (x_asdv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3254 THEN
3255 x_asdv_rec.attribute9 := l_asdv_rec.attribute9;
3256 END IF;
3257 IF (x_asdv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3258 THEN
3259 x_asdv_rec.attribute10 := l_asdv_rec.attribute10;
3260 END IF;
3261 IF (x_asdv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3262 THEN
3263 x_asdv_rec.attribute11 := l_asdv_rec.attribute11;
3264 END IF;
3265 IF (x_asdv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3266 THEN
3267 x_asdv_rec.attribute12 := l_asdv_rec.attribute12;
3268 END IF;
3269 IF (x_asdv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3270 THEN
3271 x_asdv_rec.attribute13 := l_asdv_rec.attribute13;
3272 END IF;
3273 IF (x_asdv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3274 THEN
3275 x_asdv_rec.attribute14 := l_asdv_rec.attribute14;
3276 END IF;
3277 IF (x_asdv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3278 THEN
3279 x_asdv_rec.attribute15 := l_asdv_rec.attribute15;
3280 END IF;
3281 IF (x_asdv_rec.created_by = OKC_API.G_MISS_NUM)
3282 THEN
3283 x_asdv_rec.created_by := l_asdv_rec.created_by;
3284 END IF;
3285 IF (x_asdv_rec.creation_date = OKC_API.G_MISS_DATE)
3286 THEN
3287 x_asdv_rec.creation_date := l_asdv_rec.creation_date;
3288 END IF;
3289 IF (x_asdv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3290 THEN
3291 x_asdv_rec.last_updated_by := l_asdv_rec.last_updated_by;
3292 END IF;
3293 IF (x_asdv_rec.last_update_date = OKC_API.G_MISS_DATE)
3294 THEN
3295 x_asdv_rec.last_update_date := l_asdv_rec.last_update_date;
3296 END IF;
3297 IF (x_asdv_rec.last_update_login = OKC_API.G_MISS_NUM)
3298 THEN
3299 x_asdv_rec.last_update_login := l_asdv_rec.last_update_login;
3300 END IF;
3301 -- Multi Currency Change
3302 IF (x_asdv_rec.currency_code = OKC_API.G_MISS_CHAR)
3303 THEN
3304 x_asdv_rec.currency_code := l_asdv_rec.currency_code;
3305 END IF;
3306 IF (x_asdv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
3307 THEN
3308 x_asdv_rec.currency_conversion_type := l_asdv_rec.currency_conversion_type;
3309 END IF;
3310 IF (x_asdv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
3311 THEN
3312 x_asdv_rec.currency_conversion_rate := l_asdv_rec.currency_conversion_rate;
3313 END IF;
3314 IF (x_asdv_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
3315 THEN
3316 x_asdv_rec.currency_conversion_date := l_asdv_rec.currency_conversion_date;
3317 END IF;
3318 -- Multi Currency Change
3319 RETURN(l_return_status);
3320 END populate_new_record;
3321 -----------------------------------------
3322 -- Set_Attributes for:OKL_TXD_ASSETS_V --
3323 -----------------------------------------
3324 FUNCTION Set_Attributes (
3325 p_asdv_rec IN asdv_rec_type,
3326 x_asdv_rec OUT NOCOPY asdv_rec_type
3327 ) RETURN VARCHAR2 IS
3328 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3329 BEGIN
3330 x_asdv_rec := p_asdv_rec;
3331 x_asdv_rec.OBJECT_VERSION_NUMBER := NVL(x_asdv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3332 RETURN(l_return_status);
3333 END Set_Attributes;
3334 BEGIN
3335 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3336 G_PKG_NAME,
3337 p_init_msg_list,
3338 l_api_version,
3339 p_api_version,
3340 '_PVT',
3341 x_return_status);
3342 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3343 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3344 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3345 RAISE OKC_API.G_EXCEPTION_ERROR;
3346 END IF;
3347 --- Setting item attributes
3348 l_return_status := Set_Attributes(
3349 p_asdv_rec, -- IN
3350 l_asdv_rec); -- OUT
3351 --- If any errors happen abort API
3352 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3353 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3354 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3355 RAISE OKC_API.G_EXCEPTION_ERROR;
3356 END IF;
3357 l_return_status := populate_new_record(l_asdv_rec, l_def_asdv_rec);
3358 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3359 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3360 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3361 RAISE OKC_API.G_EXCEPTION_ERROR;
3362 END IF;
3363 l_def_asdv_rec := fill_who_columns(l_def_asdv_rec);
3364 --- Validate all non-missing attributes (Item Level Validation)
3365 l_return_status := Validate_Attributes(l_def_asdv_rec);
3366 --- If any errors happen abort API
3367 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3368 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3369 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3370 RAISE OKC_API.G_EXCEPTION_ERROR;
3371 END IF;
3372 l_return_status := Validate_Record(l_def_asdv_rec);
3373 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3374 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3375 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3376 RAISE OKC_API.G_EXCEPTION_ERROR;
3377 END IF;
3378
3379 --
3380 -- Multi-Currency Change, dedey, 12/04/2002
3381 --
3382 validate_currency(
3383 x_return_status => l_return_status,
3384 p_asdv_rec => l_def_asdv_rec,
3385 x_asdv_rec => lx_temp_asdv_rec
3386 );
3387
3388 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3389 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3390 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3391 RAISE OKC_API.G_EXCEPTION_ERROR;
3392 END IF;
3393 l_def_asdv_rec := lx_temp_asdv_rec;
3394
3395 --dbms_output.put_line('After Change: '||lx_temp_asdv_rec.currency_code);
3396 --dbms_output.put_line('After Change: '||l_def_asdv_rec.currency_code);
3397 --
3398 -- Multi-Currency Change
3399 --
3400
3401 -- Fix Bug# 2737014
3402 --
3403 -- Round off amounts
3404 --
3405 roundoff_line_amount(
3406 x_return_status => l_return_status,
3407 x_msg_count => x_msg_count,
3408 x_msg_data => x_msg_data,
3409 p_asdv_rec => l_def_asdv_rec,
3410 x_asdv_rec => lx_temp_asdv_rec
3411 );
3412
3413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3416 RAISE OKC_API.G_EXCEPTION_ERROR;
3417 END IF;
3418 l_def_asdv_rec := lx_temp_asdv_rec;
3419
3420 --------------------------------------
3421 -- Move VIEW record to "Child" records
3422 --------------------------------------
3423 migrate(l_def_asdv_rec, l_okl_txd_assets_tl_rec);
3424 migrate(l_def_asdv_rec, l_asd_rec);
3425 --------------------------------------------
3426 -- Call the UPDATE_ROW for each child record
3427 --------------------------------------------
3428 update_row(
3429 p_init_msg_list,
3430 x_return_status,
3431 x_msg_count,
3432 x_msg_data,
3433 l_okl_txd_assets_tl_rec,
3434 lx_okl_txd_assets_tl_rec
3435 );
3436 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3437 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3438 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3439 RAISE OKC_API.G_EXCEPTION_ERROR;
3440 END IF;
3441 migrate(lx_okl_txd_assets_tl_rec, l_def_asdv_rec);
3442 --------------------------------------------
3443 -- Call the UPDATE_ROW for each child record
3444 --------------------------------------------
3445 update_row(
3446 p_init_msg_list,
3447 x_return_status,
3448 x_msg_count,
3449 x_msg_data,
3450 l_asd_rec,
3451 lx_asd_rec
3452 );
3453 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3454 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3455 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3456 RAISE OKC_API.G_EXCEPTION_ERROR;
3457 END IF;
3458 migrate(lx_asd_rec, l_def_asdv_rec);
3459 x_asdv_rec := l_def_asdv_rec;
3460 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3461 EXCEPTION
3462 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3463 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3464 (
3465 l_api_name,
3466 G_PKG_NAME,
3467 'OKC_API.G_RET_STS_ERROR',
3468 x_msg_count,
3469 x_msg_data,
3470 '_PVT'
3471 );
3472 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3473 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3474 (
3475 l_api_name,
3476 G_PKG_NAME,
3477 'OKC_API.G_RET_STS_UNEXP_ERROR',
3478 x_msg_count,
3479 x_msg_data,
3480 '_PVT'
3481 );
3482 WHEN OTHERS THEN
3483 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3484 (
3485 l_api_name,
3486 G_PKG_NAME,
3487 'OTHERS',
3488 x_msg_count,
3489 x_msg_data,
3490 '_PVT'
3491 );
3492 END update_row;
3493 ----------------------------------------
3494 -- PL/SQL TBL update_row for:ASDV_TBL --
3495 ----------------------------------------
3496 PROCEDURE update_row(
3497 p_api_version IN NUMBER,
3498 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3499 x_return_status OUT NOCOPY VARCHAR2,
3500 x_msg_count OUT NOCOPY NUMBER,
3501 x_msg_data OUT NOCOPY VARCHAR2,
3502 p_asdv_tbl IN asdv_tbl_type,
3503 x_asdv_tbl OUT NOCOPY asdv_tbl_type) IS
3504
3505 l_api_version CONSTANT NUMBER := 1;
3506 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3508 i NUMBER := 0;
3509 BEGIN
3510 OKC_API.init_msg_list(p_init_msg_list);
3511 -- Make sure PL/SQL table has records in it before passing
3512 IF (p_asdv_tbl.COUNT > 0) THEN
3513 i := p_asdv_tbl.FIRST;
3514 LOOP
3515 update_row (
3516 p_api_version => p_api_version,
3517 p_init_msg_list => OKC_API.G_FALSE,
3518 x_return_status => x_return_status,
3519 x_msg_count => x_msg_count,
3520 x_msg_data => x_msg_data,
3521 p_asdv_rec => p_asdv_tbl(i),
3522 x_asdv_rec => x_asdv_tbl(i));
3523 EXIT WHEN (i = p_asdv_tbl.LAST);
3524 i := p_asdv_tbl.NEXT(i);
3525 END LOOP;
3526 END IF;
3527 EXCEPTION
3528 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3529 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3530 (
3531 l_api_name,
3532 G_PKG_NAME,
3533 'OKC_API.G_RET_STS_ERROR',
3534 x_msg_count,
3535 x_msg_data,
3536 '_PVT'
3537 );
3538 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3539 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3540 (
3541 l_api_name,
3542 G_PKG_NAME,
3543 'OKC_API.G_RET_STS_UNEXP_ERROR',
3544 x_msg_count,
3545 x_msg_data,
3546 '_PVT'
3547 );
3548 WHEN OTHERS THEN
3549 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3550 (
3551 l_api_name,
3552 G_PKG_NAME,
3553 'OTHERS',
3554 x_msg_count,
3555 x_msg_data,
3556 '_PVT'
3557 );
3558 END update_row;
3559
3560 ---------------------------------------------------------------------------
3561 -- PROCEDURE delete_row
3562 ---------------------------------------------------------------------------
3563 -----------------------------------
3564 -- delete_row for:OKL_TXD_ASSETS_B --
3565 -----------------------------------
3566 PROCEDURE delete_row(
3567 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3568 x_return_status OUT NOCOPY VARCHAR2,
3569 x_msg_count OUT NOCOPY NUMBER,
3570 x_msg_data OUT NOCOPY VARCHAR2,
3571 p_asd_rec IN asd_rec_type) IS
3572
3573 l_api_version CONSTANT NUMBER := 1;
3574 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_delete_row';
3575 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3576 l_asd_rec asd_rec_type:= p_asd_rec;
3577 l_row_notfound BOOLEAN := TRUE;
3578 BEGIN
3579 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3580 p_init_msg_list,
3581 '_PVT',
3582 x_return_status);
3583 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3584 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3585 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3586 RAISE OKC_API.G_EXCEPTION_ERROR;
3587 END IF;
3588 DELETE FROM OKL_TXD_ASSETS_B
3589 WHERE ID = l_asd_rec.id;
3590
3591 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3592 EXCEPTION
3593 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3594 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3595 (
3596 l_api_name,
3597 G_PKG_NAME,
3598 'OKC_API.G_RET_STS_ERROR',
3599 x_msg_count,
3600 x_msg_data,
3601 '_PVT'
3602 );
3603 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3604 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3605 (
3606 l_api_name,
3607 G_PKG_NAME,
3608 'OKC_API.G_RET_STS_UNEXP_ERROR',
3609 x_msg_count,
3610 x_msg_data,
3611 '_PVT'
3612 );
3613 WHEN OTHERS THEN
3614 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3615 (
3616 l_api_name,
3617 G_PKG_NAME,
3618 'OTHERS',
3619 x_msg_count,
3620 x_msg_data,
3621 '_PVT'
3622 );
3623 END delete_row;
3624 --------------------------------------
3625 -- delete_row for:OKL_TXD_ASSETS_TL --
3626 --------------------------------------
3627 PROCEDURE delete_row(
3628 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3629 x_return_status OUT NOCOPY VARCHAR2,
3630 x_msg_count OUT NOCOPY NUMBER,
3631 x_msg_data OUT NOCOPY VARCHAR2,
3632 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type) IS
3633
3634 l_api_version CONSTANT NUMBER := 1;
3635 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3636 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3637 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type:= p_okl_txd_assets_tl_rec;
3638 l_row_notfound BOOLEAN := TRUE;
3639 ------------------------------------------
3640 -- Set_Attributes for:OKL_TXD_ASSETS_TL --
3641 ------------------------------------------
3642 FUNCTION Set_Attributes (
3643 p_okl_txd_assets_tl_rec IN okl_txd_assets_tl_rec_type,
3644 x_okl_txd_assets_tl_rec OUT NOCOPY okl_txd_assets_tl_rec_type
3645 ) RETURN VARCHAR2 IS
3646 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3647 BEGIN
3648 x_okl_txd_assets_tl_rec := p_okl_txd_assets_tl_rec;
3649 x_okl_txd_assets_tl_rec.LANGUAGE := USERENV('LANG');
3650 RETURN(l_return_status);
3651 END Set_Attributes;
3652 BEGIN
3653 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3654 p_init_msg_list,
3655 '_PVT',
3656 x_return_status);
3657 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3658 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3659 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3660 RAISE OKC_API.G_EXCEPTION_ERROR;
3661 END IF;
3662 --- Setting item attributes
3663 l_return_status := Set_Attributes(
3664 p_okl_txd_assets_tl_rec, -- IN
3665 l_okl_txd_assets_tl_rec); -- OUT
3666 --- If any errors happen abort API
3667 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3668 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3669 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3670 RAISE OKC_API.G_EXCEPTION_ERROR;
3671 END IF;
3672 DELETE FROM OKL_TXD_ASSETS_TL
3673 WHERE ID = l_okl_txd_assets_tl_rec.id;
3674
3675 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3676 EXCEPTION
3677 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3678 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3679 (
3680 l_api_name,
3681 G_PKG_NAME,
3682 'OKC_API.G_RET_STS_ERROR',
3683 x_msg_count,
3684 x_msg_data,
3685 '_PVT'
3686 );
3687 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3688 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3689 (
3690 l_api_name,
3691 G_PKG_NAME,
3692 'OKC_API.G_RET_STS_UNEXP_ERROR',
3693 x_msg_count,
3694 x_msg_data,
3695 '_PVT'
3696 );
3697 WHEN OTHERS THEN
3698 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3699 (
3700 l_api_name,
3701 G_PKG_NAME,
3702 'OTHERS',
3703 x_msg_count,
3704 x_msg_data,
3705 '_PVT'
3706 );
3707 END delete_row;
3708 -------------------------------------
3709 -- delete_row for:OKL_TXD_ASSETS_V --
3710 -------------------------------------
3711 PROCEDURE delete_row(
3712 p_api_version IN NUMBER,
3713 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3714 x_return_status OUT NOCOPY VARCHAR2,
3715 x_msg_count OUT NOCOPY NUMBER,
3716 x_msg_data OUT NOCOPY VARCHAR2,
3717 p_asdv_rec IN asdv_rec_type) IS
3718
3719 l_api_version CONSTANT NUMBER := 1;
3720 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3721 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3722 l_asdv_rec asdv_rec_type := p_asdv_rec;
3723 l_okl_txd_assets_tl_rec okl_txd_assets_tl_rec_type;
3724 l_asd_rec asd_rec_type;
3725 BEGIN
3726 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3727 G_PKG_NAME,
3728 p_init_msg_list,
3729 l_api_version,
3730 p_api_version,
3731 '_PVT',
3732 x_return_status);
3733 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3734 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3735 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3736 RAISE OKC_API.G_EXCEPTION_ERROR;
3737 END IF;
3738 --------------------------------------
3739 -- Move VIEW record to "Child" records
3740 --------------------------------------
3741 migrate(l_asdv_rec, l_okl_txd_assets_tl_rec);
3742 migrate(l_asdv_rec, l_asd_rec);
3743 --------------------------------------------
3744 -- Call the DELETE_ROW for each child record
3745 --------------------------------------------
3746 delete_row(
3747 p_init_msg_list,
3748 x_return_status,
3749 x_msg_count,
3750 x_msg_data,
3751 l_okl_txd_assets_tl_rec
3752 );
3753 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3754 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3755 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3756 RAISE OKC_API.G_EXCEPTION_ERROR;
3757 END IF;
3758 --------------------------------------------
3759 -- Call the DELETE_ROW for each child record
3760 --------------------------------------------
3761 delete_row(
3762 p_init_msg_list,
3763 x_return_status,
3764 x_msg_count,
3765 x_msg_data,
3766 l_asd_rec
3767 );
3768 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3769 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3770 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3771 RAISE OKC_API.G_EXCEPTION_ERROR;
3772 END IF;
3773 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3774 EXCEPTION
3775 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3776 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3777 (
3778 l_api_name,
3779 G_PKG_NAME,
3780 'OKC_API.G_RET_STS_ERROR',
3781 x_msg_count,
3782 x_msg_data,
3783 '_PVT'
3784 );
3785 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3786 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3787 (
3788 l_api_name,
3789 G_PKG_NAME,
3790 'OKC_API.G_RET_STS_UNEXP_ERROR',
3791 x_msg_count,
3792 x_msg_data,
3793 '_PVT'
3794 );
3795 WHEN OTHERS THEN
3796 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3797 (
3798 l_api_name,
3799 G_PKG_NAME,
3800 'OTHERS',
3801 x_msg_count,
3802 x_msg_data,
3803 '_PVT'
3804 );
3805 END delete_row;
3806 ----------------------------------------
3807 -- PL/SQL TBL delete_row for:ASDV_TBL --
3808 ----------------------------------------
3809 PROCEDURE delete_row(
3810 p_api_version IN NUMBER,
3811 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3812 x_return_status OUT NOCOPY VARCHAR2,
3813 x_msg_count OUT NOCOPY NUMBER,
3814 x_msg_data OUT NOCOPY VARCHAR2,
3815 p_asdv_tbl IN asdv_tbl_type) IS
3816
3817 l_api_version CONSTANT NUMBER := 1;
3818 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3819 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3820 i NUMBER := 0;
3821 BEGIN
3822 OKC_API.init_msg_list(p_init_msg_list);
3823 -- Make sure PL/SQL table has records in it before passing
3824 IF (p_asdv_tbl.COUNT > 0) THEN
3825 i := p_asdv_tbl.FIRST;
3826 LOOP
3827 delete_row (
3828 p_api_version => p_api_version,
3829 p_init_msg_list => OKC_API.G_FALSE,
3830 x_return_status => x_return_status,
3831 x_msg_count => x_msg_count,
3832 x_msg_data => x_msg_data,
3833 p_asdv_rec => p_asdv_tbl(i));
3834 EXIT WHEN (i = p_asdv_tbl.LAST);
3835 i := p_asdv_tbl.NEXT(i);
3836 END LOOP;
3837 END IF;
3838 EXCEPTION
3839 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3840 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3841 (
3842 l_api_name,
3843 G_PKG_NAME,
3844 'OKC_API.G_RET_STS_ERROR',
3845 x_msg_count,
3846 x_msg_data,
3847 '_PVT'
3848 );
3849 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3850 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3851 (
3852 l_api_name,
3853 G_PKG_NAME,
3854 'OKC_API.G_RET_STS_UNEXP_ERROR',
3855 x_msg_count,
3856 x_msg_data,
3857 '_PVT'
3858 );
3859 WHEN OTHERS THEN
3860 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3861 (
3862 l_api_name,
3863 G_PKG_NAME,
3864 'OTHERS',
3865 x_msg_count,
3866 x_msg_data,
3867 '_PVT'
3868 );
3869 END delete_row;
3870 END OKL_ASD_PVT;