[Home] [Help]
PACKAGE BODY: APPS.OKL_TAL_PVT
Source
1 PACKAGE BODY OKL_TAL_PVT AS
2 /* $Header: OKLSTALB.pls 120.8 2008/05/23 19:30:58 cklee noship $ */
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
8 G_TAL_LOOKUP_TYPE CONSTANT VARCHAR2(200) := 'OKL_TRANS_LINE_TYPE';
9 G_ID2 CONSTANT VARCHAR2(200) := '#';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
11 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
12 G_RANGE_CHECK CONSTANT VARCHAR2(200) := 'OKL_GREATER_THAN';
13 G_SALVAGE_RANGE CONSTANT VARCHAR2(200) := 'OKL_LLA_SALVAGE_VALUE';
14 G_COL_NAME_TOKEN1 CONSTANT VARCHAR2(200) := 'COL_NAME1';
15 G_COL_NAME_TOKEN2 CONSTANT VARCHAR2(200) := 'COL_NAME2';
16 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
17 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
18 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
21 -- List validation procedures for quick reference
22 --1. validate_tas_id -- Attribute Validation
23 --2. validate_ilo_id -- Attribute Validation
24 --3. validate_ilo_id_old -- Attribute Validation
25 --4. validate_iay_id -- Attribute Validation
26 --5. validate_iay_id_new -- Attribute Validation
27 --6. validate_kle_id -- Attribute Validation
28 --7. validate_tal_type -- Attribute Validation
29 --8. validate_org_id -- Attribute Validation
30 --9. validate_asset_number -- Attribute Validation
31 --10. validate_current_units -- Attribute Validation
32 --11. validate_used_asset_yn -- Attribute Validation
33 --12. validate_life_in_months -- Attribute Validation
34 --13. validate_deprn_id -- Attribute Validation
35 --14. validate_fa_location_id -- Attribute Validation
36 --15. validate_dnz_khr_id -- Attribute Validation
37 --16. validate_shipping_id1 -- Attribute Validation
38 --17. validate_shipping_id2 -- Attribute Validation
39 --18. validate_shipping_code -- Attribute Validation
40 --19. validate_corp_book -- Attribute Validation
41 --20 validate_deprn_method -- Attribute Validation
42 --21. validate_pds_date -- Tuple Record Validation
43 --22 .validate_salv_oec -- Tuple Record Validation
44 --23. validate_inv_due_date -- Tuple Record Validation
45 ------------------------------1----------------------------------------------
46 -- Start of Commnets
47 -- Badrinath Kuchibholta
48 -- Procedure Name : Validate_tas_id
49 -- Description : FK validation with OKL_TRX_ASSETS_V
50 -- Business Rules :
51 -- Parameters : OUT Return Status, IN Rec Info
52 -- Version : 1.0
53 -- End of Commnets
54
55 PROCEDURE validate_tas_id(x_return_status OUT NOCOPY VARCHAR2,
56 p_talv_rec IN talv_rec_type) IS
57
58 ln_dummy number := 0;
59 CURSOR c_tas_id_validate(p_id number) is
60 SELECT 1
61 FROM DUAL
62 WHERE EXISTS (SELECT id
63 FROM OKL_TRX_ASSETS
64 WHERE id = p_id);
65
66 BEGIN
67 -- initialize return status
68 x_return_status := OKC_API.G_RET_STS_SUCCESS;
69 -- data is required
70 IF (p_talv_rec.tas_id = OKC_API.G_MISS_NUM) OR
71 (p_talv_rec.tas_id IS NULL) THEN
72 -- halt validation as it is a required field
73 RAISE G_EXCEPTION_STOP_VALIDATION;
74 END IF;
75 -- Enforce Foreign Key
76 OPEN c_tas_id_validate(p_talv_rec.tas_id);
77 IF c_tas_id_validate%NOTFOUND THEN
78 -- halt validation as it has no parent record
79 RAISE G_EXCEPTION_HALT_VALIDATION;
80 END IF;
81 FETCH c_tas_id_validate into ln_dummy;
82 CLOSE c_tas_id_validate;
83 IF (ln_dummy = 0) then
84 -- halt validation as it has no parent record
85 RAISE G_EXCEPTION_HALT_VALIDATION;
86 END IF;
87 EXCEPTION
88 WHEN G_EXCEPTION_STOP_VALIDATION then
89 -- We are here since the field is required
90 -- store SQL error message on message stack
91 OKC_API.set_message(p_app_name => G_APP_NAME,
92 p_msg_name => G_REQUIRED_VALUE,
93 p_token1 => G_COL_NAME_TOKEN,
94 p_token1_value => 'tas_id');
95 -- Notify Error
96 x_return_status := OKC_API.G_RET_STS_ERROR;
97 WHEN G_EXCEPTION_HALT_VALIDATION then
98 -- We are here b'cause we have no parent record
99 -- store SQL error message on message stack
100 OKC_API.set_message(p_app_name => G_APP_NAME,
101 p_msg_name => G_NO_MATCHING_RECORD,
102 p_token1 => G_COL_NAME_TOKEN,
103 p_token1_value => 'tas_id');
104 IF c_tas_id_validate%ISOPEN THEN
105 CLOSE c_tas_id_validate;
106 END IF;
107 -- notify caller of an error
108 x_return_status := OKC_API.G_RET_STS_ERROR;
109 WHEN OTHERS THEN
110 -- store SQL error message on message stack
111 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
112 p_msg_name => G_UNEXPECTED_ERROR,
113 p_token1 => G_SQLCODE_TOKEN,
114 p_token1_value => SQLCODE,
115 p_token2 => G_SQLERRM_TOKEN,
116 p_token2_value => SQLERRM);
117 -- If the cursor is open then it has to be closed
118 IF c_tas_id_validate%ISOPEN THEN
119 CLOSE c_tas_id_validate;
120 END IF;
121 -- notify caller of an error as UNEXPETED error
122 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
123 END validate_tas_id;
124 -------------------------------2--------------------------------------------------
125 -- Start of Commnets
126 -- Badrinath Kuchibholta
127 -- Procedure Name : Validate_ilo_id
128 -- Description : FK validation with OKX_AST_LOCS_V
129 -- Business Rules :
130 -- Parameters : OUT Return Status, IN Rec Info
131 -- Version : 1.0
132 -- End of Commnets
133
134 PROCEDURE validate_ilo_id(x_return_status OUT NOCOPY VARCHAR2,
135 p_talv_rec IN talv_rec_type) IS
136
137 ln_dummy number := 0;
138 CURSOR c_ilo_id_validate(p_id number) is
139 SELECT 1
140 FROM DUAL
141 WHERE EXISTS (SELECT id1
142 FROM OKX_AST_LOCS_V
143 WHERE id1 = p_id
144 AND id2 = G_ID2);
145
146 BEGIN
147 -- initialize return status
148 x_return_status := OKC_API.G_RET_STS_SUCCESS;
149 -- data is required
150 IF (p_talv_rec.ilo_id = OKC_API.G_MISS_NUM) OR
151 (p_talv_rec.ilo_id IS NULL) THEN
152 -- halt validation as it is a optional field
153 RAISE G_EXCEPTION_STOP_VALIDATION;
154 END IF;
155 -- Enforce Foreign Key
156 OPEN c_ilo_id_validate(p_talv_rec.ilo_id);
157 IF c_ilo_id_validate%NOTFOUND THEN
158 -- halt validation as it has no parent record
159 RAISE G_EXCEPTION_HALT_VALIDATION;
160 END IF;
161 FETCH c_ilo_id_validate into ln_dummy;
162 CLOSE c_ilo_id_validate;
163 IF (ln_dummy = 0) then
164 -- halt validation as it has no parent record
165 RAISE G_EXCEPTION_HALT_VALIDATION;
166 END IF;
167 EXCEPTION
168 WHEN G_EXCEPTION_STOP_VALIDATION then
169 -- We are here since the field is optional
170 null;
171 WHEN G_EXCEPTION_HALT_VALIDATION then
172 -- We are here b'cause we have no parent record
173 -- store SQL error message on message stack
174 OKC_API.set_message(p_app_name => G_APP_NAME,
175 p_msg_name => G_NO_MATCHING_RECORD,
176 p_token1 => G_COL_NAME_TOKEN,
177 p_token1_value => 'ilo_id');
178 -- notify caller of an error
179 IF c_ilo_id_validate%ISOPEN THEN
180 CLOSE c_ilo_id_validate;
181 END IF;
182 x_return_status := OKC_API.G_RET_STS_ERROR;
183 WHEN OTHERS THEN
184 -- store SQL error message on message stack
185 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
186 p_msg_name => G_UNEXPECTED_ERROR,
187 p_token1 => G_SQLCODE_TOKEN,
188 p_token1_value => SQLCODE,
189 p_token2 => G_SQLERRM_TOKEN,
190 p_token2_value => SQLERRM);
191 -- If the cursor is open then it has to be closed
192 IF c_ilo_id_validate%ISOPEN THEN
193 CLOSE c_ilo_id_validate;
194 END IF;
195 -- notify caller of an error as UNEXPETED error
196 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
197 END validate_ilo_id;
198 --------------------------------3-------------------------------------------------
199 -- Start of Commnets
200 -- Badrinath Kuchibholta
201 -- Procedure Name : Validate_ilo_id_old
202 -- Description : FK validation with OKX_AST_LOCS_V
203 -- Business Rules :
204 -- Parameters : OUT Return Status, IN Rec Info
205 -- Version : 1.0
206 -- End of Commnets
207
208 PROCEDURE validate_ilo_id_old(x_return_status OUT NOCOPY VARCHAR2,
209 p_talv_rec IN talv_rec_type) IS
210
211 ln_dummy number := 0;
212 CURSOR c_ilo_id_old_validate(p_id number) is
213 SELECT 1
214 FROM DUAL
215 WHERE EXISTS (SELECT id1
216 FROM OKX_AST_LOCS_V
217 WHERE id1 = p_id
218 AND id2 = G_ID2);
219
220 BEGIN
221 -- initialize return status
222 x_return_status := OKC_API.G_RET_STS_SUCCESS;
223 -- data is required
224 IF (p_talv_rec.ilo_id_old = OKC_API.G_MISS_NUM) OR
225 (p_talv_rec.ilo_id_old IS NULL) THEN
226 -- halt validation as it is a optional field
227 RAISE G_EXCEPTION_STOP_VALIDATION;
228 END IF;
229 -- Enforce Foreign Key
230 OPEN c_ilo_id_old_validate(p_talv_rec.ilo_id_old);
231 IF c_ilo_id_old_validate%NOTFOUND THEN
232 -- halt validation as it has no parent record
233 RAISE G_EXCEPTION_HALT_VALIDATION;
234 END IF;
235 FETCH c_ilo_id_old_validate into ln_dummy;
236 CLOSE c_ilo_id_old_validate;
237 IF (ln_dummy = 0) then
238 -- halt validation as it has no parent record
239 RAISE G_EXCEPTION_HALT_VALIDATION;
240 END IF;
241 EXCEPTION
242 WHEN G_EXCEPTION_STOP_VALIDATION then
243 -- We are here since the field is optional
244 null;
245 WHEN G_EXCEPTION_HALT_VALIDATION then
246 -- We are here b'cause we have no parent record
247 -- store SQL error message on message stack
248 OKC_API.set_message(p_app_name => G_APP_NAME,
249 p_msg_name => G_NO_MATCHING_RECORD,
250 p_token1 => G_COL_NAME_TOKEN,
251 p_token1_value => 'ilo_id_old');
252 IF c_ilo_id_old_validate%ISOPEN THEN
253 CLOSE c_ilo_id_old_validate;
254 END IF;
255 -- notify caller of an error
256 x_return_status := OKC_API.G_RET_STS_ERROR;
257 WHEN OTHERS THEN
258 -- store SQL error message on message stack
259 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
260 p_msg_name => G_UNEXPECTED_ERROR,
261 p_token1 => G_SQLCODE_TOKEN,
262 p_token1_value => SQLCODE,
263 p_token2 => G_SQLERRM_TOKEN,
264 p_token2_value => SQLERRM);
265 -- If the cursor is open then it has to be closed
266 IF c_ilo_id_old_validate%ISOPEN THEN
267 CLOSE c_ilo_id_old_validate;
268 END IF;
269 -- notify caller of an error as UNEXPETED error
270 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
271 END validate_ilo_id_old;
272 ---------------------------------4-----------------------------------------------
273 -- Start of Commnets
274 -- Badrinath Kuchibholta
275 -- Procedure Name : Validate_iay_id
276 -- Description : FK validation with OKX_ASST_CATGRS_V
277 -- Business Rules :
278 -- Parameters : OUT Return Status, IN Rec Info
279 -- Version : 1.0
280 -- End of Commnets
281
282 PROCEDURE validate_iay_id(x_return_status OUT NOCOPY VARCHAR2,
283 p_talv_rec IN talv_rec_type) IS
284
285 ln_dummy number := 0;
286 CURSOR c_iay_id_validate(p_id number) is
287 SELECT 1
288 FROM DUAL
289 WHERE EXISTS (SELECT id1
290 FROM OKX_ASST_CATGRS_V
291 WHERE id1 = p_id
292 AND id2 = G_ID2);
293 BEGIN
294 -- initialize return status
295 x_return_status := OKC_API.G_RET_STS_SUCCESS;
296 -- data is required
297 IF (p_talv_rec.iay_id = OKC_API.G_MISS_NUM) OR
298 (p_talv_rec.iay_id IS NULL) THEN
299 -- halt validation as it is a optional field
300 RAISE G_EXCEPTION_STOP_VALIDATION;
301 END IF;
302 -- Enforce Foreign Key
303 OPEN c_iay_id_validate(p_talv_rec.iay_id);
304 IF c_iay_id_validate%NOTFOUND THEN
305 -- halt validation as it has no parent record
306 RAISE G_EXCEPTION_HALT_VALIDATION;
307 END IF;
308 FETCH c_iay_id_validate into ln_dummy;
309 CLOSE c_iay_id_validate;
310 IF (ln_dummy = 0) then
311 -- halt validation as it has no parent record
312 RAISE G_EXCEPTION_HALT_VALIDATION;
313 END IF;
314 EXCEPTION
315 WHEN G_EXCEPTION_STOP_VALIDATION then
316 -- We are here since the field is optional
317 null;
318 WHEN G_EXCEPTION_HALT_VALIDATION then
319 -- We are here b'cause we have no parent record
320 -- store SQL error message on message stack
321 OKC_API.set_message(p_app_name => G_APP_NAME,
322 p_msg_name => G_NO_MATCHING_RECORD,
323 p_token1 => G_COL_NAME_TOKEN,
324 p_token1_value => 'iay_id');
325 -- If the cursor is open then it has to be closed
326 IF c_iay_id_validate%ISOPEN THEN
327 CLOSE c_iay_id_validate;
328 END IF;
329 -- notify caller of an error
330 x_return_status := OKC_API.G_RET_STS_ERROR;
331 WHEN OTHERS THEN
332 -- store SQL error message on message stack
333 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
334 p_msg_name => G_UNEXPECTED_ERROR,
335 p_token1 => G_SQLCODE_TOKEN,
336 p_token1_value => SQLCODE,
337 p_token2 => G_SQLERRM_TOKEN,
338 p_token2_value => SQLERRM);
339 -- If the cursor is open then it has to be closed
340 IF c_iay_id_validate%ISOPEN THEN
341 CLOSE c_iay_id_validate;
342 END IF;
343 -- notify caller of an error as UNEXPETED error
344 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
345 END validate_iay_id;
346 ----------------------------------5----------------------------------------------
347 -- Start of Commnets
348 -- Badrinath Kuchibholta
349 -- Procedure Name : Validate_iay_id_new
350 -- Description : FK validation with OKX_ASST_CATGRS_V
351 -- Business Rules :
352 -- Parameters : OUT Return Status, IN Rec Info
353 -- Version : 1.0
354 -- End of Commnets
355
356 PROCEDURE validate_iay_id_new(x_return_status OUT NOCOPY VARCHAR2,
357 p_talv_rec IN talv_rec_type) IS
358
359 ln_dummy number := 0;
360 CURSOR c_iay_id_new_validate(p_id number) is
361 SELECT 1
362 FROM DUAL
363 WHERE EXISTS (SELECT id1
364 FROM OKX_ASST_CATGRS_V
365 WHERE id1 = p_id
366 AND id2 = G_ID2);
367 BEGIN
368 -- initialize return status
369 x_return_status := OKC_API.G_RET_STS_SUCCESS;
370 -- data is required
371 IF (p_talv_rec.iay_id_new = OKC_API.G_MISS_NUM) OR
372 (p_talv_rec.iay_id_new IS NULL) THEN
373 -- halt validation as it is a optional field
374 RAISE G_EXCEPTION_STOP_VALIDATION;
375 END IF;
376 -- Enforce Foreign Key
377 OPEN c_iay_id_new_validate(p_talv_rec.iay_id_new);
378 -- If the cursor is open then it has to be closed
379 IF c_iay_id_new_validate%NOTFOUND THEN
380 -- halt validation as it has no parent record
381 RAISE G_EXCEPTION_HALT_VALIDATION;
382 END IF;
383 FETCH c_iay_id_new_validate into ln_dummy;
384 CLOSE c_iay_id_new_validate;
385 IF (ln_dummy = 0) then
386 -- halt validation as it has no parent record
387 RAISE G_EXCEPTION_HALT_VALIDATION;
388 END IF;
389 EXCEPTION
390 WHEN G_EXCEPTION_STOP_VALIDATION then
391 -- We are here since the field is optional
392 null;
393 WHEN G_EXCEPTION_HALT_VALIDATION then
394 -- We are here b'cause we have no parent record
395 -- store SQL error message on message stack
396 OKC_API.set_message(p_app_name => G_APP_NAME,
397 p_msg_name => G_NO_MATCHING_RECORD,
398 p_token1 => G_COL_NAME_TOKEN,
399 p_token1_value => 'iay_id_new');
400 -- If the cursor is open then it has to be closed
401 IF c_iay_id_new_validate%ISOPEN THEN
402 CLOSE c_iay_id_new_validate;
403 END IF;
404 -- notify caller of an error
405 x_return_status := OKC_API.G_RET_STS_ERROR;
406 WHEN OTHERS THEN
407 -- store SQL error message on message stack
408 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
409 p_msg_name => G_UNEXPECTED_ERROR,
410 p_token1 => G_SQLCODE_TOKEN,
411 p_token1_value => SQLCODE,
412 p_token2 => G_SQLERRM_TOKEN,
413 p_token2_value => SQLERRM);
414 -- If the cursor is open then it has to be closed
415 IF c_iay_id_new_validate%ISOPEN THEN
416 CLOSE c_iay_id_new_validate;
417 END IF;
418 -- notify caller of an error as UNEXPETED error
419 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
420 END validate_iay_id_new;
421 ----------------------------------6-----------------------------------------------
422 -- Start of Commnets
423 -- Badrinath Kuchibholta
424 -- Procedure Name : Validate_kle_id
425 -- Description : FK validation with OKL_K_LINES_V
426 -- Business Rules :
427 -- Parameters : OUT Return Status, IN Rec Info
428 -- Version : 1.0
429 -- End of Commnets
430
431 PROCEDURE validate_kle_id(x_return_status OUT NOCOPY VARCHAR2,
432 p_talv_rec IN talv_rec_type) IS
433
434 ln_dummy number := 0;
435 CURSOR c_kle_id_validate(p_id number) is
436 SELECT 1
437 FROM DUAL
438 WHERE EXISTS (SELECT id
439 FROM OKL_K_LINES_V
440 WHERE id = p_id);
441 BEGIN
442 -- initialize return status
443 x_return_status := OKC_API.G_RET_STS_SUCCESS;
444 -- data is required
445 IF (p_talv_rec.kle_id = OKC_API.G_MISS_NUM) OR
446 (p_talv_rec.kle_id IS NULL) THEN
447 -- halt validation as it is a required field
448 RAISE G_EXCEPTION_STOP_VALIDATION;
449 END IF;
450 -- Enforce Foreign Key
451 OPEN c_kle_id_validate(p_talv_rec.kle_id);
452 IF c_kle_id_validate%NOTFOUND THEN
453 -- halt validation as it has no parent record
454 RAISE G_EXCEPTION_HALT_VALIDATION;
455 END IF;
456 FETCH c_kle_id_validate into ln_dummy;
457 CLOSE c_kle_id_validate;
458 IF (ln_dummy = 0) then
459 -- halt validation as it has no parent record
460 RAISE G_EXCEPTION_HALT_VALIDATION;
461 END IF;
462 EXCEPTION
463 WHEN G_EXCEPTION_STOP_VALIDATION then
464 -- We are here since the field is required
465 -- store SQL error message on message stack
466 OKC_API.set_message(p_app_name => G_APP_NAME,
467 p_msg_name => G_REQUIRED_VALUE,
468 p_token1 => G_COL_NAME_TOKEN,
469 p_token1_value => 'kle_id');
470 -- Notify Error
471 x_return_status := OKC_API.G_RET_STS_ERROR;
472 WHEN G_EXCEPTION_HALT_VALIDATION then
473 -- We are here b'cause we have no parent record
474 -- store SQL error message on message stack
475 OKC_API.set_message(p_app_name => G_APP_NAME,
476 p_msg_name => G_NO_MATCHING_RECORD,
477 p_token1 => G_COL_NAME_TOKEN,
478 p_token1_value => 'kle_id');
479 -- If the cursor is open then it has to be closed
480 IF c_kle_id_validate%ISOPEN THEN
481 CLOSE c_kle_id_validate;
482 END IF;
483 -- notify caller of an error
484 x_return_status := OKC_API.G_RET_STS_ERROR;
485 WHEN OTHERS THEN
486 -- store SQL error message on message stack
487 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
488 p_msg_name => G_UNEXPECTED_ERROR,
489 p_token1 => G_SQLCODE_TOKEN,
490 p_token1_value => SQLCODE,
491 p_token2 => G_SQLERRM_TOKEN,
492 p_token2_value => SQLERRM);
493 -- If the cursor is open then it has to be closed
494 IF c_kle_id_validate%ISOPEN THEN
495 CLOSE c_kle_id_validate;
496 END IF;
497 -- notify caller of an error as UNEXPETED error
498 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
499 END validate_kle_id;
500 -----------------------------------7-----------------------------------------------
501 -- Start of Commnets
502 -- Badrinath Kuchibholta
503 -- Procedure Name : Validate_TAL_TYPE
504 -- Description : FK validation with FND COMMON LOOKUPS
505 -- Business Rules :
506 -- Parameters : OUT Return Status, IN Rec Info
507 -- Version : 1.0
508 -- End of Commnets
509
510 PROCEDURE validate_tal_type(x_return_status OUT NOCOPY VARCHAR2,
511 p_talv_rec IN talv_rec_type) IS
512 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
513 BEGIN
514 -- initialize return status
515 x_return_status := OKC_API.G_RET_STS_SUCCESS;
516 -- data is required
517 IF (p_talv_rec.tal_type = OKC_API.G_MISS_CHAR) OR
518 (p_talv_rec.tal_type IS NULL) THEN
519 -- halt validation as it is a required field
520 RAISE G_EXCEPTION_STOP_VALIDATION;
521 END IF;
522 -- Enforce Foreign Key
523 l_return_status := OKC_UTIL.check_lookup_code(G_TAL_LOOKUP_TYPE,
524 p_talv_rec.tal_type);
525 IF l_return_status = x_return_status THEN
526 x_return_status := l_return_status;
527 ELSIF l_return_status <> x_return_status THEN
528 -- halt validation as it has no parent record
529 RAISE G_EXCEPTION_HALT_VALIDATION;
530 END IF;
531 EXCEPTION
532 WHEN G_EXCEPTION_STOP_VALIDATION then
533 -- We are here since the field is required
534 -- store SQL error message on message stack
535 OKC_API.set_message(p_app_name => G_APP_NAME,
536 p_msg_name => G_REQUIRED_VALUE,
537 p_token1 => G_COL_NAME_TOKEN,
538 p_token1_value => 'tal_type');
539 -- Notify Error
540 x_return_status := OKC_API.G_RET_STS_ERROR;
541 WHEN G_EXCEPTION_HALT_VALIDATION then
542 -- We are here b'cause we have no parent record
543 -- store SQL error message on message stack
544 OKC_API.set_message(p_app_name => G_APP_NAME,
545 p_msg_name => G_NO_MATCHING_RECORD,
546 p_token1 => G_COL_NAME_TOKEN,
547 p_token1_value => 'tal_type');
548 -- notify caller of an error
549 x_return_status := OKC_API.G_RET_STS_ERROR;
550 WHEN OTHERS THEN
551 -- store SQL error message on message stack
552 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
553 p_msg_name => G_UNEXPECTED_ERROR,
554 p_token1 => G_SQLCODE_TOKEN,
555 p_token1_value => SQLCODE,
556 p_token2 => G_SQLERRM_TOKEN,
557 p_token2_value => SQLERRM);
558 -- notify caller of an error as UNEXPETED error
559 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
560 END validate_tal_type;
561 -----------------------------------8---------------------------------------------
562 -- Start of Commnets
563 -- Badrinath Kuchibholta
564 -- Procedure Name : Validate_org_id
565 -- Description : Validate org_id against OKC_K_HEADERS_V.AUTHORING_ORG_ID
566 -- Business Rules :
567 -- Parameters : OUT Return Status, IN Rec Info
568 -- Version : 1.0
569 -- End of Commnets
570
571 PROCEDURE validate_org_id(x_return_status OUT NOCOPY VARCHAR2,
572 p_talv_rec IN talv_rec_type) IS
573 ln_dummy number := 0;
574 CURSOR c_org_id_validate(p_org_id OKL_TXL_ASSETS_B.ORG_ID%TYPE,
575 p_kle_id OKL_TXL_ASSETS_B.KLE_ID%TYPE) is
576 SELECT 1
577 FROM DUAL
578 WHERE EXISTS (SELECT chrv.authoring_org_id
579 FROM OKC_K_HEADERS_B chrv,
580 OKC_K_LINES_B cle,
581 OKL_K_LINES_V kle
582 WHERE kle.id = p_kle_id
583 AND kle.id = cle.id
584 AND cle.dnz_chr_id = chrv.id
585 AND chrv.authoring_org_id = p_org_id);
586 BEGIN
587 -- initialize return status
588 x_return_status := OKC_API.G_RET_STS_SUCCESS;
589 -- data is required
590 IF (p_talv_rec.org_id = OKC_API.G_MISS_NUM) OR
591 (p_talv_rec.org_id IS NULL) THEN
592 -- halt validation as it is optional field
593 RAISE G_EXCEPTION_STOP_VALIDATION;
594 END IF;
595 -- Enforce Foreign Key
596 OPEN c_org_id_validate(p_talv_rec.org_id,
597 p_talv_rec.kle_id);
598 IF c_org_id_validate%NOTFOUND THEN
599 -- halt validation as it has no parent record
600 RAISE G_EXCEPTION_HALT_VALIDATION;
601 END IF;
602 FETCH c_org_id_validate into ln_dummy;
603 CLOSE c_org_id_validate;
604 IF (ln_dummy = 0) then
605 -- halt validation as it has no parent record
606 RAISE G_EXCEPTION_HALT_VALIDATION;
607 END IF;
608 EXCEPTION
609 WHEN G_EXCEPTION_STOP_VALIDATION then
610 -- We are here since the field is optional
611 null;
612 WHEN G_EXCEPTION_HALT_VALIDATION then
613 -- We are here b'cause we have no parent record
614 -- store SQL error message on message stack
615 OKC_API.set_message(p_app_name => G_APP_NAME,
616 p_msg_name => G_NO_MATCHING_RECORD,
617 p_token1 => G_COL_NAME_TOKEN,
618 p_token1_value => 'org_id');
619 IF c_org_id_validate%ISOPEN THEN
620 CLOSE c_org_id_validate;
621 END IF;
622 -- notify caller of an error
623 x_return_status := OKC_API.G_RET_STS_ERROR;
624 WHEN OTHERS THEN
625 -- store SQL error message on message stack
626 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
627 p_msg_name => G_UNEXPECTED_ERROR,
628 p_token1 => G_SQLCODE_TOKEN,
629 p_token1_value => SQLCODE,
630 p_token2 => G_SQLERRM_TOKEN,
631 p_token2_value => SQLERRM);
632 -- If the cursor is open then it has to be closed
633 IF c_org_id_validate%ISOPEN THEN
634 CLOSE c_org_id_validate;
635 END IF;
636 -- notify caller of an error as UNEXPETED error
637 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
638 END validate_org_id;
639 -----------------------------------10---------------------------------------------
640 -- Start of Commnets
641 -- Badrinath Kuchibholta
642 -- Procedure Name : Validate_current_units
643 -- Description : See that is more than 0
644 -- Business Rules :
645 -- Parameters : OUT Return Status, IN Rec Info
646 -- Version : 1.0
647 -- End of Commnets
648
649 PROCEDURE validate_current_units(x_return_status OUT NOCOPY VARCHAR2,
650 p_talv_rec IN talv_rec_type) IS
651
652 BEGIN
653 -- initialize return status
654 x_return_status := OKC_API.G_RET_STS_SUCCESS;
655 -- data is required
656 IF (p_talv_rec.current_units = OKC_API.G_MISS_NUM) OR
657 (p_talv_rec.current_units IS NULL) THEN
658 -- halt validation as it is a required field
659 RAISE G_EXCEPTION_STOP_VALIDATION;
660 END IF;
661 -- See that is more than 0
662 IF p_talv_rec.current_units <= 0 THEN
663 -- halt validation as it has no parent record
664 RAISE G_EXCEPTION_HALT_VALIDATION;
665 END IF;
666 EXCEPTION
667 WHEN G_EXCEPTION_STOP_VALIDATION then
668 -- We are here since the field is required
669 -- store SQL error message on message stack
670 OKC_API.set_message(p_app_name => G_APP_NAME,
671 p_msg_name => G_REQUIRED_VALUE,
672 p_token1 => G_COL_NAME_TOKEN,
673 p_token1_value => 'current_units');
674 -- Notify Error
675 x_return_status := OKC_API.G_RET_STS_ERROR;
676 WHEN G_EXCEPTION_HALT_VALIDATION then
677 -- We are here b'cause we have no parent record
678 -- store SQL error message on message stack
679 OKC_API.set_message(p_app_name => G_APP_NAME,
680 p_msg_name => G_INVALID_VALUE,
681 p_token1 => G_COL_NAME_TOKEN,
682 p_token1_value => 'current_units');
683 -- notify caller of an error
684 x_return_status := OKC_API.G_RET_STS_ERROR;
685 WHEN OTHERS THEN
686 -- store SQL error message on message stack
687 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
688 p_msg_name => G_UNEXPECTED_ERROR,
689 p_token1 => G_SQLCODE_TOKEN,
690 p_token1_value => SQLCODE,
691 p_token2 => G_SQLERRM_TOKEN,
692 p_token2_value => SQLERRM);
693 -- notify caller of an error as UNEXPETED error
694 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
695 END validate_current_units;
696
697 -------------------------------------11-------------------------------------------
698 -- Start of Commnets
699 -- Badrinath Kuchibholta
700 -- Procedure Name : Validate_used_asset_yn
701 -- Description : Check Constraint for Y,N
702 -- Business Rules :
703 -- Parameters : OUT Return Status, IN Rec Info
704 -- Version : 1.0
705 -- End of Commnets
706
707 PROCEDURE validate_used_asset_yn(x_return_status OUT NOCOPY VARCHAR2,
708 p_talv_rec IN talv_rec_type) IS
709
710 BEGIN
711 -- initialize return status
712 x_return_status := OKC_API.G_RET_STS_SUCCESS;
713 -- data is required
714 IF (p_talv_rec.used_asset_yn = OKC_API.G_MISS_CHAR) OR
715 (p_talv_rec.used_asset_yn IS NULL) THEN
716 -- halt validation as it is a optional field
717 RAISE G_EXCEPTION_STOP_VALIDATION;
718 END IF;
719 -- See that in Y,N
720 IF p_talv_rec.used_asset_yn not in ('Y','N') THEN
721 -- halt validation as it has no parent record
722 RAISE G_EXCEPTION_HALT_VALIDATION;
723 END IF;
724 EXCEPTION
725 WHEN G_EXCEPTION_STOP_VALIDATION then
726 -- We are here since the field is optional
727 null;
728 WHEN G_EXCEPTION_HALT_VALIDATION then
729 -- We are here b'cause we have no parent record
730 -- store SQL error message on message stack
731 OKC_API.set_message(p_app_name => G_APP_NAME,
732 p_msg_name => G_INVALID_VALUE,
733 p_token1 => G_COL_NAME_TOKEN,
734 p_token1_value => 'used_asset_yn');
735 -- notify caller of an error
736 x_return_status := OKC_API.G_RET_STS_ERROR;
737 WHEN OTHERS THEN
738 -- store SQL error message on message stack
739 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
740 p_msg_name => G_UNEXPECTED_ERROR,
741 p_token1 => G_SQLCODE_TOKEN,
742 p_token1_value => SQLCODE,
743 p_token2 => G_SQLERRM_TOKEN,
744 p_token2_value => SQLERRM);
745 -- notify caller of an error as UNEXPETED error
746 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
747 END validate_used_asset_yn;
748 ---------------------------------------12-----------------------------------------
749 -- Start of Commnets
750 -- Badrinath Kuchibholta
751 -- Procedure Name : Validate_life_in_months
752 -- Description : See that is more than 0
753 -- Business Rules :
754 -- Parameters : OUT Return Status, IN Rec Info
755 -- Version : 1.0
756 -- End of Commnets
757
758 PROCEDURE validate_life_in_months(x_return_status OUT NOCOPY VARCHAR2,
759 p_talv_rec IN talv_rec_type) IS
760
761 BEGIN
762 -- initialize return status
763 x_return_status := OKC_API.G_RET_STS_SUCCESS;
764 -- data is required
765 IF (p_talv_rec.life_in_months = OKC_API.G_MISS_NUM) OR
766 (p_talv_rec.life_in_months IS NULL) THEN
767 -- halt validation as it is a optional field
768 RAISE G_EXCEPTION_STOP_VALIDATION;
769 END IF;
770 -- See that is more than 0
771 IF p_talv_rec.life_in_months <= 0 THEN
772 -- halt validation as it has no parent record
773 RAISE G_EXCEPTION_HALT_VALIDATION;
774 END IF;
775 EXCEPTION
776 WHEN G_EXCEPTION_STOP_VALIDATION then
777 -- We are here since the field is optional
778 null;
779 WHEN G_EXCEPTION_HALT_VALIDATION then
780 -- We are here b'cause we have no parent record
781 -- store SQL error message on message stack
782 OKC_API.set_message(p_app_name => G_APP_NAME,
783 p_msg_name => G_INVALID_VALUE,
784 p_token1 => G_COL_NAME_TOKEN,
785 p_token1_value => 'life_in_months');
786 -- notify caller of an error
787 x_return_status := OKC_API.G_RET_STS_ERROR;
788 WHEN OTHERS THEN
789 -- store SQL error message on message stack
790 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
791 p_msg_name => G_UNEXPECTED_ERROR,
792 p_token1 => G_SQLCODE_TOKEN,
793 p_token1_value => SQLCODE,
794 p_token2 => G_SQLERRM_TOKEN,
795 p_token2_value => SQLERRM);
796 -- notify caller of an error as UNEXPETED error
797 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
798 END validate_life_in_months;
799 -----------------------------------------13---------------------------------------
800 -- Start of Commnets
801 -- Badrinath Kuchibholta
802 -- Procedure Name : Validate_deprn_id
803 -- Description : FK validation with OKX_ASST_CATGRS_V
804 -- Business Rules :
805 -- Parameters : OUT Return Status, IN Rec Info
806 -- Version : 1.0
807 -- End of Commnets
808
809 PROCEDURE validate_deprn_id(x_return_status OUT NOCOPY VARCHAR2,
810 p_talv_rec IN talv_rec_type) IS
811
812 ln_dummy number := 0;
813 CURSOR c_deprn_id_validate(p_id1 OKX_ASST_CATGRS_V.ID1%type) is
814 SELECT 1
815 FROM DUAL
816 WHERE EXISTS (SELECT '1'
817 FROM OKX_ASST_CATGRS_V
818 WHERE id1 = p_id1
819 and id2 = G_ID2);
820 BEGIN
821 -- initialize return status
822 x_return_status := OKC_API.G_RET_STS_SUCCESS;
823 -- data is required
824 IF (p_talv_rec.depreciation_id = OKC_API.G_MISS_NUM) OR
825 (p_talv_rec.depreciation_id IS NULL) THEN
826 -- halt validation as it is a required field
827 RAISE G_EXCEPTION_STOP_VALIDATION;
828 END IF;
829 -- Enforce validation
830 OPEN c_deprn_id_validate(p_talv_rec.depreciation_id);
831 IF c_deprn_id_validate%NOTFOUND THEN
832 -- halt validation as it has no parent record
833 RAISE G_EXCEPTION_HALT_VALIDATION;
834 END IF;
835 FETCH c_deprn_id_validate into ln_dummy;
836 CLOSE c_deprn_id_validate;
837 IF (ln_dummy = 0) then
838 -- halt validation as it has no parent record
839 RAISE G_EXCEPTION_HALT_VALIDATION;
840 END IF;
841 EXCEPTION
842 WHEN G_EXCEPTION_STOP_VALIDATION then
843 -- We are here since the field is optional
844 null;
845 WHEN G_EXCEPTION_HALT_VALIDATION then
846 -- We are here b'cause we have no parent record
847 -- store SQL error message on message stack
848 OKC_API.set_message(p_app_name => G_APP_NAME,
849 p_msg_name => G_NO_MATCHING_RECORD,
850 p_token1 => G_COL_NAME_TOKEN,
851 p_token1_value => 'depreciation_id');
852 -- If the cursor is open then it has to be closed
853 IF c_deprn_id_validate%ISOPEN THEN
854 CLOSE c_deprn_id_validate;
855 END IF;
856 -- notify caller of an error
857 x_return_status := OKC_API.G_RET_STS_ERROR;
858 WHEN OTHERS THEN
859 -- store SQL error message on message stack
860 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
861 p_msg_name => G_UNEXPECTED_ERROR,
862 p_token1 => G_SQLCODE_TOKEN,
863 p_token1_value => SQLCODE,
864 p_token2 => G_SQLERRM_TOKEN,
865 p_token2_value => SQLERRM);
866 -- If the cursor is open then it has to be closed
867 IF c_deprn_id_validate%ISOPEN THEN
868 CLOSE c_deprn_id_validate;
869 END IF;
870 -- notify caller of an error as UNEXPETED error
871 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
872 END validate_deprn_id;
873 -------------------------------------------14-------------------------------------
874 -- Start of Commnets
875 -- Badrinath Kuchibholta
876 -- Procedure Name : Validate_fa_location_id
877 -- Description : FK validation with OKX_ASST_LOCS_V
878 -- Business Rules :
879 -- Parameters : OUT Return Status, IN Rec Info
880 -- Version : 1.0
881 -- End of Commnets
882
883 PROCEDURE validate_fa_location_id(x_return_status OUT NOCOPY VARCHAR2,
884 p_talv_rec IN talv_rec_type) IS
885
886 ln_dummy number := 0;
887 CURSOR c_fa_location_id_validate(p_fa_location_id OKX_AST_LOCS_V.LOCATION_ID%TYPE) is
888 SELECT 1
889 FROM DUAL
890 WHERE EXISTS (SELECT '1'
891 FROM OKX_AST_LOCS_V
892 WHERE id1 = p_fa_location_id
893 AND id2 = G_ID2);
894
895 BEGIN
896 -- initialize return status
897 x_return_status := OKC_API.G_RET_STS_SUCCESS;
898 -- data is required
899 IF (p_talv_rec.fa_location_id = OKC_API.G_MISS_NUM) OR
900 (p_talv_rec.fa_location_id IS NULL) THEN
901 -- halt validation as it is a required field
902 RAISE G_EXCEPTION_STOP_VALIDATION;
903 END IF;
904 -- Enforce Validation
905 OPEN c_fa_location_id_validate(p_talv_rec.fa_location_id);
906 -- If the cursor is open then it has to be closed
907 IF c_fa_location_id_validate%NOTFOUND THEN
908 -- halt validation as it has no parent record
909 RAISE G_EXCEPTION_HALT_VALIDATION;
910 END IF;
911 FETCH c_fa_location_id_validate into ln_dummy;
912 CLOSE c_fa_location_id_validate;
913 IF (ln_dummy = 0) then
914 -- halt validation as it has no parent record
915 RAISE G_EXCEPTION_HALT_VALIDATION;
916 END IF;
917 EXCEPTION
918 WHEN G_EXCEPTION_STOP_VALIDATION then
919 -- We are here since the field is optional
920 null;
921 WHEN G_EXCEPTION_HALT_VALIDATION then
922 -- We are here b'cause we have no parent record
923 -- store SQL error message on message stack
924 OKC_API.set_message(p_app_name => G_APP_NAME,
925 p_msg_name => G_NO_MATCHING_RECORD,
926 p_token1 => G_COL_NAME_TOKEN,
927 p_token1_value => 'fa_location_id');
928 -- If the cursor is open then it has to be closed
929 IF c_fa_location_id_validate%ISOPEN THEN
930 CLOSE c_fa_location_id_validate;
931 END IF;
932 -- notify caller of an error
933 x_return_status := OKC_API.G_RET_STS_ERROR;
934 WHEN OTHERS THEN
935 -- store SQL error message on message stack
936 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
937 p_msg_name => G_UNEXPECTED_ERROR,
938 p_token1 => G_SQLCODE_TOKEN,
939 p_token1_value => SQLCODE,
940 p_token2 => G_SQLERRM_TOKEN,
941 p_token2_value => SQLERRM);
942 -- If the cursor is open then it has to be closed
943 IF c_fa_location_id_validate%ISOPEN THEN
944 CLOSE c_fa_location_id_validate;
945 END IF;
946 -- notify caller of an error as UNEXPETED error
947 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
948 END validate_fa_location_id;
949
950 ---------------------------------------------15-----------------------------------
951 -- Start of Commnets
952 -- Badrinath Kuchibholta
953 -- Procedure Name : Validate_dnz_khr_id
954 -- Description : FK validation with OKL_K_HEADERS_V
955 -- Business Rules :
956 -- Parameters : OUT Return Status, IN Rec Info
957 -- Version : 1.0
958 -- End of Commnets
959
960 PROCEDURE validate_dnz_khr_id(x_return_status OUT NOCOPY VARCHAR2,
961 p_talv_rec IN talv_rec_type) IS
962
963 ln_dummy number := 0;
964 CURSOR c_dnz_khr_id_validate(p_dnz_khr_id OKL_TXL_ASSETS_V.DNZ_KHR_ID%TYPE) is
965 SELECT 1
966 FROM DUAL
967 WHERE EXISTS (SELECT '1'
968 FROM OKC_K_HEADERS_B chrv
969 WHERE chrv.id = p_dnz_khr_id);
970 BEGIN
971 -- initialize return status
972 x_return_status := OKC_API.G_RET_STS_SUCCESS;
973 -- data is required
974 IF (p_talv_rec.dnz_khr_id = OKC_API.G_MISS_NUM) OR
975 (p_talv_rec.dnz_khr_id IS NULL) THEN
976 -- halt validation as it is a required field
977 RAISE G_EXCEPTION_STOP_VALIDATION;
978 END IF;
979 -- Enforce Validation
980 OPEN c_dnz_khr_id_validate(p_talv_rec.dnz_khr_id);
981 -- If the cursor is open then it has to be closed
982 IF c_dnz_khr_id_validate%NOTFOUND THEN
983 -- halt validation as it has no parent record
984 RAISE G_EXCEPTION_HALT_VALIDATION;
985 END IF;
986 FETCH c_dnz_khr_id_validate into ln_dummy;
987 CLOSE c_dnz_khr_id_validate;
988 IF (ln_dummy = 0) then
989 -- halt validation as it has no parent record
990 RAISE G_EXCEPTION_HALT_VALIDATION;
991 END IF;
992 EXCEPTION
993 WHEN G_EXCEPTION_STOP_VALIDATION then
994 -- We are here since the field is optional
995 null;
996 WHEN G_EXCEPTION_HALT_VALIDATION then
997 -- We are here b'cause we have no parent record
998 -- store SQL error message on message stack
999 OKC_API.set_message(p_app_name => G_APP_NAME,
1000 p_msg_name => G_NO_MATCHING_RECORD,
1001 p_token1 => G_COL_NAME_TOKEN,
1002 p_token1_value => 'dnz_khr_id');
1003 -- If the cursor is open then it has to be closed
1004 IF c_dnz_khr_id_validate%ISOPEN THEN
1005 CLOSE c_dnz_khr_id_validate;
1006 END IF;
1007 -- notify caller of an error
1008 x_return_status := OKC_API.G_RET_STS_ERROR;
1009 WHEN OTHERS THEN
1010 -- store SQL error message on message stack
1011 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1012 p_msg_name => G_UNEXPECTED_ERROR,
1013 p_token1 => G_SQLCODE_TOKEN,
1014 p_token1_value => SQLCODE,
1015 p_token2 => G_SQLERRM_TOKEN,
1016 p_token2_value => SQLERRM);
1017 -- If the cursor is open then it has to be closed
1018 IF c_dnz_khr_id_validate%ISOPEN THEN
1019 CLOSE c_dnz_khr_id_validate;
1020 END IF;
1021 -- notify caller of an error as UNEXPETED error
1022 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1023 END validate_dnz_khr_id;
1024 ------------------------------19----------------------------------------------
1025 -- Start of Commnets
1026 -- Badrinath Kuchibholta
1027 -- Procedure Name : validate_corp_book
1028 -- Description : FK validation with OKX_ASST_BK_CONTROLS_V
1029 -- Business Rules :
1030 -- Parameters : OUT Return Status, IN Rec Info
1031 -- Version : 1.0
1032 -- End of Commnets
1033
1034 PROCEDURE validate_corp_book(x_return_status OUT NOCOPY VARCHAR2,
1035 p_talv_rec IN talv_rec_type) IS
1036
1037 ln_dummy number := 0;
1038 CURSOR c_corp_book_validate(p_name OKX_ASST_BK_CONTROLS_V.NAME%TYPE) is
1039 SELECT 1
1040 FROM DUAL
1041 WHERE EXISTS (SELECT id1
1042 FROM OKX_ASST_BK_CONTROLS_V
1043 WHERE name = p_name
1044 AND book_class = 'CORPORATE');
1045
1046 BEGIN
1047 -- initialize return status
1048 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1049 -- data is required
1050 IF (p_talv_rec.corporate_book = OKC_API.G_MISS_CHAR) OR
1051 (p_talv_rec.corporate_book IS NULL) THEN
1052 -- halt validation as it is a required field
1053 RAISE G_EXCEPTION_STOP_VALIDATION;
1054 END IF;
1055 -- Enforce Foreign Key
1056 OPEN c_corp_book_validate(p_talv_rec.corporate_book);
1057 IF c_corp_book_validate%NOTFOUND THEN
1058 -- halt validation as it has no parent record
1059 RAISE G_EXCEPTION_HALT_VALIDATION;
1060 END IF;
1061 FETCH c_corp_book_validate into ln_dummy;
1062 CLOSE c_corp_book_validate;
1063 IF (ln_dummy = 0) then
1064 -- halt validation as it has no parent record
1065 RAISE G_EXCEPTION_HALT_VALIDATION;
1066 END IF;
1067 EXCEPTION
1068 WHEN G_EXCEPTION_STOP_VALIDATION then
1069 -- We are here since the field is optional
1070 null;
1071 WHEN G_EXCEPTION_HALT_VALIDATION then
1072 -- We are here b'cause we have no parent record
1073 -- store SQL error message on message stack
1074 OKC_API.set_message(p_app_name => G_APP_NAME,
1075 p_msg_name => G_NO_MATCHING_RECORD,
1076 p_token1 => G_COL_NAME_TOKEN,
1077 p_token1_value => 'corporate_book');
1078 IF c_corp_book_validate%ISOPEN THEN
1079 CLOSE c_corp_book_validate;
1080 END IF;
1081 -- notify caller of an error
1082 x_return_status := OKC_API.G_RET_STS_ERROR;
1083 WHEN OTHERS THEN
1084 -- store SQL error message on message stack
1085 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1086 p_msg_name => G_UNEXPECTED_ERROR,
1087 p_token1 => G_SQLCODE_TOKEN,
1088 p_token1_value => SQLCODE,
1089 p_token2 => G_SQLERRM_TOKEN,
1090 p_token2_value => SQLERRM);
1091 -- If the cursor is open then it has to be closed
1092 IF c_corp_book_validate%ISOPEN THEN
1093 CLOSE c_corp_book_validate;
1094 END IF;
1095 -- notify caller of an error as UNEXPETED error
1096 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1097 END validate_corp_book;
1098 --------------------------------20-------------------------------------------------
1099 -- Start of Commnets
1100 -- Badrinath Kuchibholta
1101 -- Procedure Name : validate_deprn_method
1102 -- Description : FK validation with OKX_ASST_DEP_METHODS_V
1103 -- Business Rules :
1104 -- Parameters : OUT Return Status, IN Rec Info
1105 -- Version : 1.0
1106 -- End of Commnets
1107
1108 PROCEDURE validate_deprn_method(x_return_status OUT NOCOPY VARCHAR2,
1109 p_talv_rec IN talv_rec_type) IS
1110
1111 ln_dummy number := 0;
1112 CURSOR c_deprn_method_validate(p_deprn_method OKX_ASST_DEP_METHODS_V.METHOD_CODE%TYPE) is
1113 SELECT 1
1114 FROM DUAL
1115 WHERE EXISTS (SELECT method_code
1116 FROM OKX_ASST_DEP_METHODS_V
1117 WHERE method_code = p_deprn_method);
1118
1119 BEGIN
1120 -- initialize return status
1121 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1122 -- data is required
1123 IF (p_talv_rec.deprn_method = OKC_API.G_MISS_CHAR) OR
1124 (p_talv_rec.deprn_method IS NULL) THEN
1125 -- halt validation as it is a optional field
1126 RAISE G_EXCEPTION_STOP_VALIDATION;
1127 END IF;
1128 -- Enforce Foreign Key
1129 OPEN c_deprn_method_validate(p_talv_rec.deprn_method);
1130 IF c_deprn_method_validate%NOTFOUND THEN
1131 -- halt validation as it has no parent record
1132 RAISE G_EXCEPTION_HALT_VALIDATION;
1133 END IF;
1134 FETCH c_deprn_method_validate into ln_dummy;
1135 CLOSE c_deprn_method_validate;
1136 IF (ln_dummy = 0) then
1137 -- halt validation as it has no parent record
1138 RAISE G_EXCEPTION_HALT_VALIDATION;
1139 END IF;
1140 EXCEPTION
1141 WHEN G_EXCEPTION_STOP_VALIDATION then
1142 -- We are here since the field is optional
1143 null;
1144 WHEN G_EXCEPTION_HALT_VALIDATION then
1145 -- We are here b'cause we have no parent record
1146 -- store SQL error message on message stack
1147 OKC_API.set_message(p_app_name => G_APP_NAME,
1148 p_msg_name => G_NO_MATCHING_RECORD,
1149 p_token1 => G_COL_NAME_TOKEN,
1150 p_token1_value => 'deprn_method');
1151 IF c_deprn_method_validate%ISOPEN THEN
1152 CLOSE c_deprn_method_validate;
1153 END IF;
1154 -- notify caller of an error
1155 x_return_status := OKC_API.G_RET_STS_ERROR;
1156 WHEN OTHERS THEN
1157 -- store SQL error message on message stack
1158 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1159 p_msg_name => G_UNEXPECTED_ERROR,
1160 p_token1 => G_SQLCODE_TOKEN,
1161 p_token1_value => SQLCODE,
1162 p_token2 => G_SQLERRM_TOKEN,
1163 p_token2_value => SQLERRM);
1164 -- If the cursor is open then it has to be closed
1165 IF c_deprn_method_validate%ISOPEN THEN
1166 CLOSE c_deprn_method_validate;
1167 END IF;
1168 -- notify caller of an error as UNEXPETED error
1169 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1170 END validate_deprn_method;
1171
1172 -----------------------------------------------21---------------------------------
1173 -- Start of Commnets
1174 -- Badrinath Kuchibholta
1175 -- Procedure Name : Validate_pds_date
1176 -- Description : Tuple record validation of date_purchased,date_delivery
1177 -- and in_service_date
1178 -- Business Rules : Delivery date,in_service_date
1179 -- Should be greater than Purchase date
1180 -- Parameters : OUT Return Status, IN Rec Info
1181 -- Version : 1.0
1182 -- End of Commnets
1183
1184 PROCEDURE validate_pds_date(x_return_status OUT NOCOPY VARCHAR2,
1185 p_talv_rec IN talv_rec_type) IS
1186 ln_dummy number := 0;
1187 BEGIN
1188 -- initialize return status
1189 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1190 -- purchase date should be less than delivery date
1191 -- and in service date
1192 -- When all the dates are given
1193 IF (p_talv_rec.date_purchased IS NOT NULL
1194 AND p_talv_rec.date_delivery IS NOT NULL
1195 AND p_talv_rec.in_service_date IS NOT NULL) THEN
1196 IF(p_talv_rec.date_purchased > p_talv_rec.date_delivery) AND
1197 (p_talv_rec.date_purchased > p_talv_rec.in_service_date) THEN
1198 -- halt validation as the above statments are true
1199 RAISE G_EXCEPTION_HALT_VALIDATION;
1200 ELSIF ((p_talv_rec.date_purchased > p_talv_rec.date_delivery) OR
1201 (p_talv_rec.date_purchased > p_talv_rec.in_service_date) OR
1202 (p_talv_rec.date_delivery > p_talv_rec.in_service_date)) THEN
1203 -- Purchase date should be greater than date_delivery and in_service_date
1204 -- and date_delivery should be less than equal to in_service_date
1205 -- halt validation as the above statments are true
1206 RAISE G_EXCEPTION_HALT_VALIDATION;
1207 END IF;
1208 END IF;
1209 EXCEPTION
1210 WHEN G_EXCEPTION_STOP_VALIDATION then
1211 -- We are here since the field is required
1212 -- store SQL error message on message stack
1213 OKC_API.set_message(p_app_name => G_APP_NAME,
1214 p_msg_name => G_REQUIRED_VALUE,
1215 p_token1 => G_COL_NAME_TOKEN,
1216 p_token1_value => 'date_purchased,date_delivery,in_service_date');
1217 -- Notify Error
1218 x_return_status := OKC_API.G_RET_STS_ERROR;
1219 WHEN G_EXCEPTION_HALT_VALIDATION then
1220 -- We are here b'cause validation falied
1221 -- store SQL error message on message stack
1222 OKC_API.set_message(p_app_name => G_APP_NAME,
1223 p_msg_name => G_RANGE_CHECK,
1224 p_token1 => G_COL_NAME_TOKEN1,
1225 p_token1_value => 'date_delivery',
1226 p_token2 => G_COL_NAME_TOKEN2,
1227 p_token2_value => 'in_service_date');
1228 -- notify caller of an error
1229 x_return_status := OKC_API.G_RET_STS_ERROR;
1230 WHEN OTHERS THEN
1231 -- store SQL error message on message stack
1232 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1233 p_msg_name => G_UNEXPECTED_ERROR,
1234 p_token1 => G_SQLCODE_TOKEN,
1235 p_token1_value => SQLCODE,
1236 p_token2 => G_SQLERRM_TOKEN,
1237 p_token2_value => SQLERRM);
1238 -- notify caller of an error as UNEXPETED error
1239 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1240 END validate_pds_date;
1241
1242 -------------------------------------------------22-------------------------------
1243 -- Start of Commnets
1244 -- Badrinath Kuchibholta
1245 -- Procedure Name : Validate_salv_oec
1246 -- Description : Tuple record validation of salvage_value
1247 -- ,original_cost
1248 -- Business Rules : original_cost Should be greater than salvage_value
1249 -- Parameters : OUT Return Status, IN Rec Info
1250 -- Version : 1.0
1251 -- End of Commnets
1252
1253 PROCEDURE validate_salv_oec(x_return_status OUT NOCOPY VARCHAR2,
1254 p_talv_rec IN talv_rec_type) IS
1255 ln_comp_prn_oec NUMBER := 0;
1256 ln_comp_prn_salv NUMBER := 0;
1257 BEGIN
1258 -- initialize return status
1259 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1260 IF (p_talv_rec.original_cost IS NOT NULL AND
1261 p_talv_rec.salvage_value IS NOT NULL OR
1262 p_talv_rec.percent_salvage_value IS NOT NULL) THEN
1263 ln_comp_prn_oec := (p_talv_rec.original_cost/100);
1264 --Bug# 3950089
1265 IF(nvl(p_talv_rec.salvage_value,0) > p_talv_rec.original_cost) THEN
1266 --IF(p_talv_rec.salvage_value > p_talv_rec.original_cost) THEN
1267 -- original cost is greater than salvage value
1268 -- halt validation as the above statments are true
1269 RAISE G_EXCEPTION_HALT_VALIDATION;
1270 --Bug# 3950089
1271 ELSIF (p_talv_rec.percent_salvage_value > 100) THEN
1272 --ELSIF (p_talv_rec.percent_salvage_value > ln_comp_prn_oec) THEN
1273 -- To Check if computed original_cost is greater than percent_salvage_value
1274 -- halt validation as the above statments are true
1275 RAISE G_EXCEPTION_HALT_VALIDATION;
1276 END IF;
1277 ELSIF (p_talv_rec.original_cost IS NOT NULL
1278 AND p_talv_rec.salvage_value IS NOT NULL
1279 AND p_talv_rec.percent_salvage_value IS NOT NULL) THEN
1280 ln_comp_prn_oec := (p_talv_rec.original_cost/100);
1281 ln_comp_prn_salv := (p_talv_rec.salvage_value/100);
1282 IF (p_talv_rec.salvage_value > p_talv_rec.original_cost) AND
1283 (p_talv_rec.percent_salvage_value > ln_comp_prn_oec) THEN
1284 -- To Check if computed original_cost is greater than percent_salvage_value
1285 -- And original cost is greater than salvage value
1286 -- halt validation as the above statments are true
1287 RAISE G_EXCEPTION_HALT_VALIDATION;
1288 ELSIF (p_talv_rec.salvage_value > p_talv_rec.original_cost) OR
1289 (p_talv_rec.percent_salvage_value > ln_comp_prn_oec) OR
1290 (ln_comp_prn_salv <> p_talv_rec.percent_salvage_value) THEN
1291 -- To Check if computed original_cost is greater than percent_salvage_value
1292 -- or original cost is greater than salvage value
1293 -- or the computed salvage value is not equal to percentage salvage value
1294 -- halt validation as the above statments are true
1295 RAISE G_EXCEPTION_HALT_VALIDATION;
1296 END IF;
1297 END IF;
1298 EXCEPTION
1299 WHEN G_EXCEPTION_STOP_VALIDATION then
1300 -- We are here since the field is required
1301 -- store SQL error message on message stack
1302 OKC_API.set_message(p_app_name => G_APP_NAME,
1303 p_msg_name => G_REQUIRED_VALUE,
1304 p_token1 => G_COL_NAME_TOKEN,
1305 p_token1_value => 'Original_cost');
1306 -- Notify Error
1307 x_return_status := OKC_API.G_RET_STS_ERROR;
1308 WHEN G_EXCEPTION_HALT_VALIDATION then
1309 -- We are here b'cause validation falied
1310 -- store SQL error message on message stack
1311 OKC_API.set_message(p_app_name => G_APP_NAME,
1312 p_msg_name => G_SALVAGE_RANGE);
1313 -- notify caller of an error
1314 x_return_status := OKC_API.G_RET_STS_ERROR;
1315 WHEN OTHERS THEN
1316 -- store SQL error message on message stack
1317 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1318 p_msg_name => G_UNEXPECTED_ERROR,
1319 p_token1 => G_SQLCODE_TOKEN,
1320 p_token1_value => SQLCODE,
1321 p_token2 => G_SQLERRM_TOKEN,
1322 p_token2_value => SQLERRM);
1323 -- notify caller of an error as UNEXPETED error
1324 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1325 END validate_salv_oec;
1326 --Bug# 2981308
1327 -------------------------------------------------23-------------------------------
1328 -- Start of Commnets
1329 -- avsingh
1330 -- Procedure Name : Validate_asset_key
1331 -- Description : validate asset key ccid
1332 --
1333 -- Business Rules : Asset_Key_Id should exist as a valid code_combination_id
1334 -- in FA_ASSET_KEYWORDS
1335 -- Parameters : OUT Return Status, IN Rec Info
1336 -- Version : 1.0
1337 -- End of Commnets
1338
1339 PROCEDURE validate_asset_key(x_return_status OUT NOCOPY VARCHAR2,
1340 p_talv_rec IN talv_rec_type) IS
1341
1342 l_exists Varchar2(1) default 'N';
1343
1344 --cursor to check asset key ccid
1345 cursor l_fak_csr(p_asset_key_id in number) is
1346 select 'Y'
1347 from FA_ASSET_KEYWORDS fak
1348 where fak.CODE_COMBINATION_ID = p_asset_key_id
1349 and fak.enabled_flag = 'Y';
1350 --and trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
1351 --and trunc(nvl(end_date_active,sysdate));
1352
1353 Begin
1354 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1355 If p_talv_rec.asset_key_id <> OKL_API.G_MISS_NUM AND
1356 p_talv_rec.asset_key_id is NOT NULL then
1357 l_exists := 'N';
1358 open l_fak_csr(p_asset_key_id => p_talv_rec.asset_key_id);
1359 fetch l_fak_csr into l_exists;
1360 if l_fak_csr%NOTFOUND then
1361 NULL;
1362 end if;
1363 close l_fak_csr;
1364 IF l_exists = 'N' then
1365 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Asset Key');
1366 x_return_status := OKL_API.G_RET_STS_ERROR;
1367 RAISE G_EXCEPTION_HALT_VALIDATION;
1368 END IF;
1369 End If;
1370
1371 EXCEPTION
1372 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1373 null;
1374 WHEN OTHERS THEN
1375 If l_fak_csr%ISOPEN then
1376 close l_fak_csr;
1377 End If;
1378
1379 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1380 ,p_msg_name => G_UNEXPECTED_ERROR
1381 ,p_token1 => G_SQLCODE_TOKEN
1382 ,p_token1_value => SQLCODE
1383 ,p_token2 => G_SQLERRM_TOKEN
1384 ,p_token2_value => SQLERRM);
1385 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1386 END validate_asset_key;
1387
1388
1389 /************************ HAND-CODED *********************************/
1390
1391 -- Fix Bug# 2737014
1392 --
1393 -- Procedure Name : roundoff_line_amount
1394 -- Description : Round off NOT NULL Asset Transaction line amounts
1395 -- Business Rules :
1396 -- Parameters :
1397 -- Version : 1.0
1398 -- End of comments
1399
1400 PROCEDURE roundoff_line_amount(
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_talv_rec IN talv_rec_type,
1405 x_talv_rec OUT NOCOPY talv_rec_type
1406 ) IS
1407
1408 l_conv_amount NUMBER;
1409 l_currency_code OKC_K_LINES_B.CURRENCY_CODE%TYPE;
1410
1411 roundoff_error EXCEPTION;
1412
1413 BEGIN
1414
1415 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1416
1417 -- Take original record values
1418 x_talv_rec := p_talv_rec;
1419
1420 l_currency_code := p_talv_rec.currency_code;
1421
1422 IF (l_currency_code IS NULL) THEN -- Fatal error, Not a valid currency_code
1423 RAISE roundoff_error;
1424 END IF;
1425
1426 --dbms_output.put_line('Round off start '||l_currency_code);
1427 -- Round off all Asset Transaction Line Amounts
1428 IF (p_talv_rec.original_cost IS NOT NULL
1429 AND
1430 p_talv_rec.original_cost <> OKL_API.G_MISS_NUM) THEN
1431
1432 l_conv_amount := NULL;
1433 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1434 p_amount => p_talv_rec.original_cost,
1435 p_currency_code => l_currency_code
1436 );
1437
1438 x_talv_rec.original_cost := l_conv_amount;
1439 END IF;
1440
1441 IF (p_talv_rec.depreciation_cost IS NOT NULL
1442 AND
1443 p_talv_rec.depreciation_cost <> OKL_API.G_MISS_NUM) THEN
1444
1445 l_conv_amount := NULL;
1446 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1447 p_amount => p_talv_rec.depreciation_cost,
1448 p_currency_code => l_currency_code
1449 );
1450
1451 x_talv_rec.depreciation_cost := l_conv_amount;
1452 END IF;
1453
1454 IF (p_talv_rec.salvage_value IS NOT NULL
1455 AND
1456 p_talv_rec.salvage_value <> OKL_API.G_MISS_NUM) THEN
1457
1458 l_conv_amount := NULL;
1459 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1460 p_amount => p_talv_rec.salvage_value,
1461 p_currency_code => l_currency_code
1462 );
1463
1464 x_talv_rec.salvage_value := l_conv_amount;
1465 END IF;
1466
1467 IF (p_talv_rec.old_salvage_value IS NOT NULL
1468 AND
1469 p_talv_rec.old_salvage_value <> OKL_API.G_MISS_NUM) THEN
1470
1471 l_conv_amount := NULL;
1472 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1473 p_amount => p_talv_rec.old_salvage_value,
1474 p_currency_code => l_currency_code
1475 );
1476
1477 x_talv_rec.old_salvage_value := l_conv_amount;
1478 END IF;
1479
1480 IF (p_talv_rec.new_residual_value IS NOT NULL
1481 AND
1482 p_talv_rec.new_residual_value <> OKL_API.G_MISS_NUM) THEN
1483
1484 l_conv_amount := NULL;
1485 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1486 p_amount => p_talv_rec.new_residual_value,
1487 p_currency_code => l_currency_code
1488 );
1489
1490 x_talv_rec.new_residual_value := l_conv_amount;
1491 END IF;
1492
1493 IF (p_talv_rec.old_residual_value IS NOT NULL
1494 AND
1495 p_talv_rec.old_residual_value <> OKL_API.G_MISS_NUM) THEN
1496
1497 l_conv_amount := NULL;
1498 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1499 p_amount => p_talv_rec.old_residual_value,
1500 p_currency_code => l_currency_code
1501 );
1502
1503 x_talv_rec.old_residual_value := l_conv_amount;
1504 END IF;
1505
1506 IF (p_talv_rec.cost_retired IS NOT NULL
1507 AND
1508 p_talv_rec.cost_retired <> OKL_API.G_MISS_NUM) THEN
1509
1510 l_conv_amount := NULL;
1511 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1512 p_amount => p_talv_rec.cost_retired,
1513 p_currency_code => l_currency_code
1514 );
1515
1516 x_talv_rec.cost_retired := l_conv_amount;
1517 END IF;
1518
1519 IF (p_talv_rec.sale_proceeds IS NOT NULL
1520 AND
1521 p_talv_rec.sale_proceeds <> OKL_API.G_MISS_NUM) THEN
1522
1523 l_conv_amount := NULL;
1524 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1525 p_amount => p_talv_rec.sale_proceeds,
1526 p_currency_code => l_currency_code
1527 );
1528
1529 x_talv_rec.sale_proceeds := l_conv_amount;
1530 END IF;
1531
1532 IF (p_talv_rec.match_amount IS NOT NULL
1533 AND
1534 p_talv_rec.match_amount <> OKL_API.G_MISS_NUM) THEN
1535
1536 l_conv_amount := NULL;
1537 l_conv_amount := okl_accounting_util.cross_currency_round_amount(
1538 p_amount => p_talv_rec.match_amount,
1539 p_currency_code => l_currency_code
1540 );
1541
1542 x_talv_rec.match_amount := l_conv_amount;
1543 END IF;
1544 --dbms_output.put_line('Round off complete');
1545
1546 EXCEPTION
1547 WHEN roundoff_error THEN
1548 x_return_status := OKL_API.G_RET_STS_ERROR;
1549 END roundoff_line_amount;
1550
1551
1552 -- Multi-Currency Change
1553 --
1554 -- PROCEDURE validate_currency
1555 -- Decription: This procedure validates currency_code during insert and update operation
1556 -- Logic:
1557 -- 1. If transaction currency is NULL, take functional currency and
1558 -- make rate, date and type as NULL
1559 -- 2. If transaction currency is NOT NULL and
1560 -- transaction currency <> functional currency and
1561 -- type <> 'User' then
1562 -- get conversion rate from GL and change rate column with new rate
1563 -- 3. If transaction currency is NOT NULL and
1564 -- transaction currency <> functional currency and
1565 -- type = 'User' then
1566 -- take all values as it is
1567 -- 4. If transaction currency = functional currency
1568 -- make rate, date and type as NULL
1569 --
1570 PROCEDURE validate_currency(
1571 x_return_status OUT NOCOPY VARCHAR2,
1572 p_talv_rec IN talv_rec_type,
1573 x_talv_rec OUT NOCOPY talv_rec_type
1574 ) IS
1575
1576 l_func_currency GL_CURRENCIES.CURRENCY_CODE%TYPE;
1577 currency_validation_failed EXCEPTION;
1578 l_ok VARCHAR2(1);
1579
1580 CURSOR conv_type_csr (p_conv_type gl_daily_conversion_types.conversion_type%TYPE) IS
1581 SELECT 'Y'
1582 FROM gl_daily_conversion_types
1583 WHERE conversion_type = p_conv_type;
1584
1585 CURSOR curr_csr (p_curr_code gl_currencies.currency_code%TYPE) IS
1586 SELECT 'Y'
1587 FROM gl_currencies
1588 WHERE currency_code = p_curr_code
1589 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
1590 NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
1591
1592 BEGIN
1593
1594 x_talv_rec := p_talv_rec;
1595 l_func_currency := okl_accounting_util.get_func_curr_code();
1596
1597 --dbms_output.put_line('Func Curr: '||l_func_currency);
1598 --dbms_output.put_line('Trans Curr Code: '|| p_talv_rec.currency_code);
1599 --dbms_output.put_line('Trans Curr Rate: '|| p_talv_rec.currency_conversion_rate);
1600 --dbms_output.put_line('Trans Curr Date: '|| p_talv_rec.currency_conversion_date);
1601 --dbms_output.put_line('Trans Curr Type: '|| p_talv_rec.currency_conversion_type);
1602
1603 IF (p_talv_rec.currency_code IS NULL
1604 OR
1605 p_talv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN -- take functional currency
1606 x_talv_rec.currency_code := l_func_currency;
1607 x_talv_rec.currency_conversion_type := NULL;
1608 x_talv_rec.currency_conversion_rate := NULL;
1609 x_talv_rec.currency_conversion_date := NULL;
1610 ELSE
1611
1612 l_ok := '?';
1613 OPEN curr_csr(p_talv_rec.currency_code);
1614 FETCH curr_csr INTO l_ok;
1615 CLOSE curr_csr;
1616
1617 IF (l_ok <> 'Y') THEN
1618 OKC_API.set_message(p_app_name => G_APP_NAME,
1619 p_msg_name => G_NO_MATCHING_RECORD,
1620 p_token1 => G_COL_NAME_TOKEN,
1621 p_token1_value => 'currency_code');
1622 x_return_status := OKC_API.G_RET_STS_ERROR;
1623 RAISE currency_validation_failed;
1624 END IF;
1625
1626 IF (p_talv_rec.currency_code = l_func_currency) THEN -- both are same
1627 x_talv_rec.currency_conversion_type := NULL;
1628 x_talv_rec.currency_conversion_rate := NULL;
1629 x_talv_rec.currency_conversion_date := NULL;
1630 ELSE -- transactional and functional currency are different
1631
1632 -- Conversion type, date and rate mandetory
1633 IF (p_talv_rec.currency_conversion_type IS NULL
1634 OR
1635 p_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
1636 OKC_API.set_message(
1637 p_app_name => G_APP_NAME,
1638 p_msg_name => G_REQUIRED_VALUE,
1639 p_token1 => G_COL_NAME_TOKEN,
1640 p_token1_value => 'currency_conversion_type');
1641 x_return_status := OKC_API.G_RET_STS_ERROR;
1642 RAISE currency_validation_failed;
1643 END IF;
1644
1645 l_ok := '?';
1646 OPEN conv_type_csr (p_talv_rec.currency_conversion_type);
1647 FETCH conv_type_csr INTO l_ok;
1648 CLOSE conv_type_csr;
1649
1650 IF (l_ok <> 'Y') THEN
1651 OKC_API.set_message(p_app_name => G_APP_NAME,
1652 p_msg_name => G_NO_MATCHING_RECORD,
1653 p_token1 => G_COL_NAME_TOKEN,
1654 p_token1_value => 'currency_conversion_type');
1655 x_return_status := OKC_API.G_RET_STS_ERROR;
1656 RAISE currency_validation_failed;
1657 END IF;
1658
1659 IF (p_talv_rec.currency_conversion_date IS NULL
1660 OR
1661 p_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
1662 OKC_API.set_message(
1663 p_app_name => G_APP_NAME,
1664 p_msg_name => G_REQUIRED_VALUE,
1665 p_token1 => G_COL_NAME_TOKEN,
1666 p_token1_value => 'currency_conversion_date');
1667 x_return_status := OKC_API.G_RET_STS_ERROR;
1668 RAISE currency_validation_failed;
1669 END IF;
1670
1671 IF (p_talv_rec.currency_conversion_type = 'User') THEN
1672
1673 IF (p_talv_rec.currency_conversion_rate IS NULL
1674 OR
1675 p_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
1676 OKC_API.set_message(
1677 p_app_name => G_APP_NAME,
1678 p_msg_name => G_REQUIRED_VALUE,
1679 p_token1 => G_COL_NAME_TOKEN,
1680 p_token1_value => 'currency_conversion_rate');
1681 x_return_status := OKC_API.G_RET_STS_ERROR;
1682 RAISE currency_validation_failed;
1683 END IF;
1684
1685 x_talv_rec.currency_conversion_type := p_talv_rec.currency_conversion_type;
1686 x_talv_rec.currency_conversion_rate := p_talv_rec.currency_conversion_rate;
1687 x_talv_rec.currency_conversion_date := p_talv_rec.currency_conversion_date;
1688
1689 ELSE -- conversion_type <> 'User'
1690
1691 x_talv_rec.currency_conversion_rate := okl_accounting_util.get_curr_con_rate(
1692 p_from_curr_code => p_talv_rec.currency_code,
1693 p_to_curr_code => l_func_currency,
1694 p_con_date => p_talv_rec.currency_conversion_date,
1695 p_con_type => p_talv_rec.currency_conversion_type
1696 );
1697
1698 x_talv_rec.currency_conversion_type := p_talv_rec.currency_conversion_type;
1699 x_talv_rec.currency_conversion_date := p_talv_rec.currency_conversion_date;
1700
1701 END IF; -- conversion_type
1702 END IF; -- currency_code check
1703 END IF; -- currency_code NULL
1704
1705 EXCEPTION
1706 WHEN currency_validation_failed THEN
1707 RETURN;
1708 END validate_currency;
1709 -- Multi-Currency Change
1710
1711 ---------------------------------------------------------------------------
1712 -- FUNCTION get_seq_id
1713 ---------------------------------------------------------------------------
1714 FUNCTION get_seq_id RETURN NUMBER IS
1715 BEGIN
1716 RETURN(okc_p_util.raw_to_number(sys_guid()));
1717 END get_seq_id;
1718
1719 ---------------------------------------------------------------------------
1720 -- PROCEDURE qc
1721 ---------------------------------------------------------------------------
1722 PROCEDURE qc IS
1723 BEGIN
1724 null;
1725 END qc;
1726
1727 ---------------------------------------------------------------------------
1728 -- PROCEDURE change_version
1729 ---------------------------------------------------------------------------
1730 PROCEDURE change_version IS
1731 BEGIN
1732 null;
1733 END change_version;
1734
1735 ---------------------------------------------------------------------------
1736 -- PROCEDURE api_copy
1737 ---------------------------------------------------------------------------
1738 PROCEDURE api_copy IS
1739 BEGIN
1740 null;
1741 END api_copy;
1742
1743 ---------------------------------------------------------------------------
1744 -- PROCEDURE add_language
1745 ---------------------------------------------------------------------------
1746 PROCEDURE add_language IS
1747 BEGIN
1748 DELETE FROM OKL_TXL_ASSETS_TL T
1749 WHERE NOT EXISTS (
1750 SELECT NULL
1751 FROM OKL_TXL_ASSETS_B B --fixed bug 3321017 by kmotepal
1752 WHERE B.ID = T.ID
1753 );
1754
1755 UPDATE OKL_TXL_ASSETS_TL T SET (
1756 DESCRIPTION) = (SELECT
1757 B.DESCRIPTION
1758 FROM OKL_TXL_ASSETS_TL B
1759 WHERE B.ID = T.ID
1760 AND B.LANGUAGE = T.SOURCE_LANG)
1761 WHERE (
1762 T.ID,
1763 T.LANGUAGE)
1764 IN (SELECT
1765 SUBT.ID,
1766 SUBT.LANGUAGE
1767 FROM OKL_TXL_ASSETS_TL SUBB, OKL_TXL_ASSETS_TL SUBT
1768 WHERE SUBB.ID = SUBT.ID
1769 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1770 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1771 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
1772 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
1773 ));
1774
1775 INSERT INTO OKL_TXL_ASSETS_TL (
1776 ID,
1777 LANGUAGE,
1778 SOURCE_LANG,
1779 SFWT_FLAG,
1780 DESCRIPTION,
1781 CREATED_BY,
1782 CREATION_DATE,
1783 LAST_UPDATED_BY,
1784 LAST_UPDATE_DATE,
1785 LAST_UPDATE_LOGIN)
1786 SELECT
1787 B.ID,
1788 L.LANGUAGE_CODE,
1789 B.SOURCE_LANG,
1790 B.SFWT_FLAG,
1791 B.DESCRIPTION,
1792 B.CREATED_BY,
1793 B.CREATION_DATE,
1794 B.LAST_UPDATED_BY,
1795 B.LAST_UPDATE_DATE,
1796 B.LAST_UPDATE_LOGIN
1797 FROM OKL_TXL_ASSETS_TL B, FND_LANGUAGES L
1798 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1799 AND B.LANGUAGE = USERENV('LANG')
1800 AND NOT EXISTS(
1801 SELECT NULL
1802 FROM OKL_TXL_ASSETS_TL T
1803 WHERE T.ID = B.ID
1804 AND T.LANGUAGE = L.LANGUAGE_CODE
1805 );
1806
1807 END add_language;
1808
1809 ---------------------------------------------------------------------------
1810 -- FUNCTION get_rec for: OKL_TXL_ASSETS_B
1811 ---------------------------------------------------------------------------
1812 FUNCTION get_rec (
1813 p_tal_rec IN tal_rec_type,
1814 x_no_data_found OUT NOCOPY BOOLEAN
1815 ) RETURN tal_rec_type IS
1816 CURSOR okl_txl_asset_b_pk_csr (p_id IN NUMBER) IS
1817 SELECT ID,
1818 OBJECT_VERSION_NUMBER,
1819 TAS_ID,
1820 ILO_ID,
1821 ILO_ID_OLD,
1822 IAY_ID,
1823 IAY_ID_NEW,
1824 KLE_ID,
1825 DNZ_KHR_ID,
1826 LINE_NUMBER,
1827 ORG_ID,
1828 TAL_TYPE,
1829 ASSET_NUMBER,
1830 FA_LOCATION_ID,
1831 ORIGINAL_COST,
1832 CURRENT_UNITS,
1833 MANUFACTURER_NAME,
1834 YEAR_MANUFACTURED,
1835 SUPPLIER_ID,
1836 USED_ASSET_YN,
1837 TAG_NUMBER,
1838 MODEL_NUMBER,
1839 CORPORATE_BOOK,
1840 DATE_PURCHASED,
1841 DATE_DELIVERY,
1842 IN_SERVICE_DATE,
1843 LIFE_IN_MONTHS,
1844 DEPRECIATION_ID,
1845 DEPRECIATION_COST,
1846 DEPRN_METHOD,
1847 DEPRN_RATE,
1848 SALVAGE_VALUE,
1849 PERCENT_SALVAGE_VALUE,
1850 --Bug# 2981308
1851 ASSET_KEY_ID,
1852 -- Bug# 4028371
1853 FA_TRX_DATE,
1854 --Bug# 4899328
1855 FA_COST,
1856 ATTRIBUTE_CATEGORY,
1857 ATTRIBUTE1,
1858 ATTRIBUTE2,
1859 ATTRIBUTE3,
1860 ATTRIBUTE4,
1861 ATTRIBUTE5,
1862 ATTRIBUTE6,
1863 ATTRIBUTE7,
1864 ATTRIBUTE8,
1865 ATTRIBUTE9,
1866 ATTRIBUTE10,
1867 ATTRIBUTE11,
1868 ATTRIBUTE12,
1869 ATTRIBUTE13,
1870 ATTRIBUTE14,
1871 ATTRIBUTE15,
1872 CREATED_BY,
1873 CREATION_DATE,
1874 LAST_UPDATED_BY,
1875 LAST_UPDATE_DATE,
1876 LAST_UPDATE_LOGIN,
1877 DEPRECIATE_YN,
1878 HOLD_PERIOD_DAYS,
1879 OLD_SALVAGE_VALUE,
1880 NEW_RESIDUAL_VALUE,
1881 OLD_RESIDUAL_VALUE,
1882 UNITS_RETIRED,
1883 COST_RETIRED,
1884 SALE_PROCEEDS,
1885 REMOVAL_COST,
1886 DNZ_ASSET_ID,
1887 DATE_DUE,
1888 REP_ASSET_ID,
1889 LKE_ASSET_ID,
1890 MATCH_AMOUNT,
1891 SPLIT_INTO_SINGLES_FLAG,
1892 SPLIT_INTO_UNITS,
1893 -- Multi-Currency Change
1894 currency_code,
1895 currency_conversion_type,
1896 currency_conversion_rate,
1897 currency_conversion_date,
1898 -- Multi-Currency Change
1899 -- VRS Project - START
1900 RESIDUAL_SHR_PARTY_ID,
1901 RESIDUAL_SHR_AMOUNT,
1902 RETIREMENT_ID
1903 -- VRS Project - END
1904 FROM Okl_Txl_Assets_B
1905 WHERE okl_txl_assets_b.id = p_id;
1906 l_okl_txl_asset_b_pk okl_txl_asset_b_pk_csr%ROWTYPE;
1907 l_tal_rec tal_rec_type;
1908 BEGIN
1909 x_no_data_found := TRUE;
1910 -- Get current database values
1911 OPEN okl_txl_asset_b_pk_csr (p_tal_rec.id);
1912 FETCH okl_txl_asset_b_pk_csr INTO
1913 l_tal_rec.ID,
1914 l_tal_rec.OBJECT_VERSION_NUMBER,
1915 l_tal_rec.TAS_ID,
1916 l_tal_rec.ILO_ID,
1917 l_tal_rec.ILO_ID_OLD,
1918 l_tal_rec.IAY_ID,
1919 l_tal_rec.IAY_ID_NEW,
1920 l_tal_rec.KLE_ID,
1921 l_tal_rec.DNZ_KHR_ID,
1922 l_tal_rec.LINE_NUMBER,
1923 l_tal_rec.ORG_ID,
1924 l_tal_rec.TAL_TYPE,
1925 l_tal_rec.ASSET_NUMBER,
1926 l_tal_rec.FA_LOCATION_ID,
1927 l_tal_rec.ORIGINAL_COST,
1928 l_tal_rec.CURRENT_UNITS,
1929 l_tal_rec.MANUFACTURER_NAME,
1930 l_tal_rec.YEAR_MANUFACTURED,
1931 l_tal_rec.SUPPLIER_ID,
1932 l_tal_rec.USED_ASSET_YN,
1933 l_tal_rec.TAG_NUMBER,
1934 l_tal_rec.MODEL_NUMBER,
1935 l_tal_rec.CORPORATE_BOOK,
1936 l_tal_rec.DATE_PURCHASED,
1937 l_tal_rec.DATE_DELIVERY,
1938 l_tal_rec.IN_SERVICE_DATE,
1939 l_tal_rec.LIFE_IN_MONTHS,
1940 l_tal_rec.DEPRECIATION_ID,
1941 l_tal_rec.DEPRECIATION_COST,
1942 l_tal_rec.DEPRN_METHOD,
1943 l_tal_rec.DEPRN_RATE,
1944 l_tal_rec.SALVAGE_VALUE,
1945 l_tal_rec.PERCENT_SALVAGE_VALUE,
1946 --Bug# 2981308
1947 l_tal_rec.ASSET_KEY_ID,
1948 -- Bug# 4028371
1949 l_tal_rec.FA_TRX_DATE,
1950 --Bug# 4899328
1951 l_tal_rec.FA_COST,
1952 l_tal_rec.ATTRIBUTE_CATEGORY,
1953 l_tal_rec.ATTRIBUTE1,
1954 l_tal_rec.ATTRIBUTE2,
1955 l_tal_rec.ATTRIBUTE3,
1956 l_tal_rec.ATTRIBUTE4,
1957 l_tal_rec.ATTRIBUTE5,
1958 l_tal_rec.ATTRIBUTE6,
1959 l_tal_rec.ATTRIBUTE7,
1960 l_tal_rec.ATTRIBUTE8,
1961 l_tal_rec.ATTRIBUTE9,
1962 l_tal_rec.ATTRIBUTE10,
1963 l_tal_rec.ATTRIBUTE11,
1964 l_tal_rec.ATTRIBUTE12,
1965 l_tal_rec.ATTRIBUTE13,
1966 l_tal_rec.ATTRIBUTE14,
1967 l_tal_rec.ATTRIBUTE15,
1968 l_tal_rec.CREATED_BY,
1969 l_tal_rec.CREATION_DATE,
1970 l_tal_rec.LAST_UPDATED_BY,
1971 l_tal_rec.LAST_UPDATE_DATE,
1972 l_tal_rec.LAST_UPDATE_LOGIN,
1973 l_tal_rec.DEPRECIATE_YN,
1974 l_tal_rec.HOLD_PERIOD_DAYS,
1975 l_tal_rec.OLD_SALVAGE_VALUE,
1976 l_tal_rec.NEW_RESIDUAL_VALUE,
1977 l_tal_rec.OLD_RESIDUAL_VALUE,
1978 l_tal_rec.UNITS_RETIRED,
1979 l_tal_rec.COST_RETIRED,
1980 l_tal_rec.SALE_PROCEEDS,
1981 l_tal_rec.REMOVAL_COST,
1982 l_tal_rec.DNZ_ASSET_ID,
1983 l_tal_rec.DATE_DUE,
1984 l_tal_rec.REP_ASSET_ID,
1985 l_tal_rec.LKE_ASSET_ID,
1986 l_tal_rec.MATCH_AMOUNT,
1987 l_tal_rec.SPLIT_INTO_SINGLES_FLAG,
1988 l_tal_rec.SPLIT_INTO_UNITS,
1989 -- Multi-Currency Change
1990 l_tal_rec.currency_code,
1991 l_tal_rec.currency_conversion_type,
1992 l_tal_rec.currency_conversion_rate,
1993 l_tal_rec.currency_conversion_date,
1994 -- Multi-Currency Change
1995 -- VRS Project - START
1996 l_tal_rec.RESIDUAL_SHR_PARTY_ID,
1997 l_tal_rec.RESIDUAL_SHR_AMOUNT,
1998 l_tal_rec.RETIREMENT_ID;
1999 -- VRS Project - END
2000 x_no_data_found := okl_txl_asset_b_pk_csr%NOTFOUND;
2001 CLOSE okl_txl_asset_b_pk_csr;
2002 RETURN(l_tal_rec);
2003 END get_rec;
2004
2005 FUNCTION get_rec (
2006 p_tal_rec IN tal_rec_type
2007 ) RETURN tal_rec_type IS
2008 l_row_notfound BOOLEAN := TRUE;
2009 BEGIN
2010 RETURN(get_rec(p_tal_rec, l_row_notfound));
2011 END get_rec;
2012 ---------------------------------------------------------------------------
2013 -- FUNCTION get_rec for: OKL_TXL_ASSETS_TL
2014 ---------------------------------------------------------------------------
2015 FUNCTION get_rec (
2016 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
2017 x_no_data_found OUT NOCOPY BOOLEAN
2018 ) RETURN okl_txl_assets_tl_rec_type IS
2019 CURSOR okl_txl_asset_tl_pk_csr (p_id IN NUMBER,
2020 p_language IN VARCHAR2) IS
2021 SELECT
2022 ID,
2023 LANGUAGE,
2024 SOURCE_LANG,
2025 SFWT_FLAG,
2026 DESCRIPTION,
2027 CREATED_BY,
2028 CREATION_DATE,
2029 LAST_UPDATED_BY,
2030 LAST_UPDATE_DATE,
2031 LAST_UPDATE_LOGIN
2032 FROM Okl_Txl_Assets_Tl
2033 WHERE okl_txl_assets_tl.id = p_id
2034 AND okl_txl_assets_tl.language = p_language;
2035 l_okl_txl_asset_tl_pk okl_txl_asset_tl_pk_csr%ROWTYPE;
2036 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
2037 BEGIN
2038 x_no_data_found := TRUE;
2039 -- Get current database values
2040 OPEN okl_txl_asset_tl_pk_csr (p_okl_txl_assets_tl_rec.id,
2041 p_okl_txl_assets_tl_rec.language);
2042 FETCH okl_txl_asset_tl_pk_csr INTO
2043 l_okl_txl_assets_tl_rec.ID,
2044 l_okl_txl_assets_tl_rec.LANGUAGE,
2045 l_okl_txl_assets_tl_rec.SOURCE_LANG,
2046 l_okl_txl_assets_tl_rec.SFWT_FLAG,
2047 l_okl_txl_assets_tl_rec.DESCRIPTION,
2048 l_okl_txl_assets_tl_rec.CREATED_BY,
2049 l_okl_txl_assets_tl_rec.CREATION_DATE,
2050 l_okl_txl_assets_tl_rec.LAST_UPDATED_BY,
2051 l_okl_txl_assets_tl_rec.LAST_UPDATE_DATE,
2052 l_okl_txl_assets_tl_rec.LAST_UPDATE_LOGIN;
2053 x_no_data_found := okl_txl_asset_tl_pk_csr%NOTFOUND;
2054 CLOSE okl_txl_asset_tl_pk_csr;
2055 RETURN(l_okl_txl_assets_tl_rec);
2056 END get_rec;
2057
2058 FUNCTION get_rec (
2059 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type
2060 ) RETURN okl_txl_assets_tl_rec_type IS
2061 l_row_notfound BOOLEAN := TRUE;
2062 BEGIN
2063 RETURN(get_rec(p_okl_txl_assets_tl_rec, l_row_notfound));
2064 END get_rec;
2065 ---------------------------------------------------------------------------
2066 -- FUNCTION get_rec for: OKL_TXL_ASSETS_V
2067 ---------------------------------------------------------------------------
2068 FUNCTION get_rec (
2069 p_talv_rec IN talv_rec_type,
2070 x_no_data_found OUT NOCOPY BOOLEAN
2071 ) RETURN talv_rec_type IS
2072 CURSOR okl_talv_pk_csr (p_id IN NUMBER) IS
2073 SELECT ID,
2074 OBJECT_VERSION_NUMBER,
2075 SFWT_FLAG,
2076 TAS_ID,
2077 ILO_ID,
2078 ILO_ID_OLD,
2079 IAY_ID,
2080 IAY_ID_NEW,
2081 KLE_ID,
2082 DNZ_KHR_ID,
2083 LINE_NUMBER,
2084 ORG_ID,
2085 TAL_TYPE,
2086 ASSET_NUMBER,
2087 DESCRIPTION,
2088 FA_LOCATION_ID,
2089 ORIGINAL_COST,
2090 CURRENT_UNITS,
2091 MANUFACTURER_NAME,
2092 YEAR_MANUFACTURED,
2093 SUPPLIER_ID,
2094 USED_ASSET_YN,
2095 TAG_NUMBER,
2096 MODEL_NUMBER,
2097 CORPORATE_BOOK,
2098 DATE_PURCHASED,
2099 DATE_DELIVERY,
2100 IN_SERVICE_DATE,
2101 LIFE_IN_MONTHS,
2102 DEPRECIATION_ID,
2103 DEPRECIATION_COST,
2104 DEPRN_METHOD,
2105 DEPRN_RATE,
2106 SALVAGE_VALUE,
2107 PERCENT_SALVAGE_VALUE,
2108 --Bug# 2981308
2109 ASSET_KEY_ID,
2110 -- Bug# 4028371
2111 FA_TRX_DATE,
2112 -- Bug# 4899328
2113 FA_COST,
2114 ATTRIBUTE_CATEGORY,
2115 ATTRIBUTE1,
2116 ATTRIBUTE2,
2117 ATTRIBUTE3,
2118 ATTRIBUTE4,
2119 ATTRIBUTE5,
2120 ATTRIBUTE6,
2121 ATTRIBUTE7,
2122 ATTRIBUTE8,
2123 ATTRIBUTE9,
2124 ATTRIBUTE10,
2125 ATTRIBUTE11,
2126 ATTRIBUTE12,
2127 ATTRIBUTE13,
2128 ATTRIBUTE14,
2129 ATTRIBUTE15,
2130 CREATED_BY,
2131 CREATION_DATE,
2132 LAST_UPDATED_BY,
2133 LAST_UPDATE_DATE,
2134 LAST_UPDATE_LOGIN,
2135 DEPRECIATE_YN,
2136 HOLD_PERIOD_DAYS,
2137 OLD_SALVAGE_VALUE,
2138 NEW_RESIDUAL_VALUE,
2139 OLD_RESIDUAL_VALUE,
2140 UNITS_RETIRED,
2141 COST_RETIRED,
2142 SALE_PROCEEDS,
2143 REMOVAL_COST,
2144 DNZ_ASSET_ID,
2145 DATE_DUE,
2146 REP_ASSET_ID,
2147 LKE_ASSET_ID,
2148 MATCH_AMOUNT,
2149 SPLIT_INTO_SINGLES_FLAG,
2150 SPLIT_INTO_UNITS,
2151 -- Multi-Currency Change
2152 currency_code,
2153 currency_conversion_type,
2154 currency_conversion_rate,
2155 currency_conversion_date,
2156 -- Multi-Currency Change
2157 -- VRS Project - START
2158 RESIDUAL_SHR_PARTY_ID,
2159 RESIDUAL_SHR_AMOUNT,
2160 RETIREMENT_ID
2161 -- VRS Project - END
2162 FROM Okl_Txl_Assets_V
2163 WHERE okl_txl_assets_v.id = p_id;
2164 l_okl_talv_pk okl_talv_pk_csr%ROWTYPE;
2165 l_talv_rec talv_rec_type;
2166 BEGIN
2167 x_no_data_found := TRUE;
2168 -- Get current database values
2169 OPEN okl_talv_pk_csr (p_talv_rec.id);
2170 FETCH okl_talv_pk_csr INTO
2171 l_talv_rec.ID,
2172 l_talv_rec.OBJECT_VERSION_NUMBER,
2173 l_talv_rec.SFWT_FLAG,
2174 l_talv_rec.TAS_ID,
2175 l_talv_rec.ILO_ID,
2176 l_talv_rec.ILO_ID_OLD,
2177 l_talv_rec.IAY_ID,
2178 l_talv_rec.IAY_ID_NEW,
2179 l_talv_rec.KLE_ID,
2180 l_talv_rec.DNZ_KHR_ID,
2181 l_talv_rec.LINE_NUMBER,
2182 l_talv_rec.ORG_ID,
2183 l_talv_rec.TAL_TYPE,
2184 l_talv_rec.ASSET_NUMBER,
2185 l_talv_rec.DESCRIPTION,
2186 l_talv_rec.FA_LOCATION_ID,
2187 l_talv_rec.ORIGINAL_COST,
2188 l_talv_rec.CURRENT_UNITS,
2189 l_talv_rec.MANUFACTURER_NAME,
2190 l_talv_rec.YEAR_MANUFACTURED,
2191 l_talv_rec.SUPPLIER_ID,
2192 l_talv_rec.USED_ASSET_YN,
2193 l_talv_rec.TAG_NUMBER,
2194 l_talv_rec.MODEL_NUMBER,
2195 l_talv_rec.CORPORATE_BOOK,
2196 l_talv_rec.DATE_PURCHASED,
2197 l_talv_rec.DATE_DELIVERY,
2198 l_talv_rec.IN_SERVICE_DATE,
2199 l_talv_rec.LIFE_IN_MONTHS,
2200 l_talv_rec.DEPRECIATION_ID,
2201 l_talv_rec.DEPRECIATION_COST,
2202 l_talv_rec.DEPRN_METHOD,
2203 l_talv_rec.DEPRN_RATE,
2204 l_talv_rec.SALVAGE_VALUE,
2205 l_talv_rec.PERCENT_SALVAGE_VALUE,
2206 --Bug# 2981308
2207 l_talv_rec.ASSET_KEY_ID,
2208 -- Bug# 4028371
2209 l_talv_rec.FA_TRX_DATE,
2210 -- Bug# 4899328
2211 l_talv_rec.FA_COST,
2212 l_talv_rec.ATTRIBUTE_CATEGORY,
2213 l_talv_rec.ATTRIBUTE1,
2214 l_talv_rec.ATTRIBUTE2,
2215 l_talv_rec.ATTRIBUTE3,
2216 l_talv_rec.ATTRIBUTE4,
2217 l_talv_rec.ATTRIBUTE5,
2218 l_talv_rec.ATTRIBUTE6,
2219 l_talv_rec.ATTRIBUTE7,
2220 l_talv_rec.ATTRIBUTE8,
2221 l_talv_rec.ATTRIBUTE9,
2222 l_talv_rec.ATTRIBUTE10,
2223 l_talv_rec.ATTRIBUTE11,
2224 l_talv_rec.ATTRIBUTE12,
2225 l_talv_rec.ATTRIBUTE13,
2226 l_talv_rec.ATTRIBUTE14,
2227 l_talv_rec.ATTRIBUTE15,
2228 l_talv_rec.CREATED_BY,
2229 l_talv_rec.CREATION_DATE,
2230 l_talv_rec.LAST_UPDATED_BY,
2231 l_talv_rec.LAST_UPDATE_DATE,
2232 l_talv_rec.LAST_UPDATE_LOGIN,
2233 l_talv_rec.DEPRECIATE_YN,
2234 l_talv_rec.HOLD_PERIOD_DAYS,
2235 l_talv_rec.OLD_SALVAGE_VALUE,
2236 l_talv_rec.NEW_RESIDUAL_VALUE,
2237 l_talv_rec.OLD_RESIDUAL_VALUE,
2238 l_talv_rec.UNITS_RETIRED,
2239 l_talv_rec.COST_RETIRED,
2240 l_talv_rec.SALE_PROCEEDS,
2241 l_talv_rec.REMOVAL_COST,
2242 l_talv_rec.DNZ_ASSET_ID,
2243 l_talv_rec.DATE_DUE,
2244 l_talv_rec.REP_ASSET_ID,
2245 l_talv_rec.LKE_ASSET_ID,
2246 l_talv_rec.MATCH_AMOUNT,
2247 l_talv_rec.SPLIT_INTO_SINGLES_FLAG,
2248 l_talv_rec.SPLIT_INTO_UNITS,
2249 -- Multi-Currency Change
2250 l_talv_rec.currency_code,
2251 l_talv_rec.currency_conversion_type,
2252 l_talv_rec.currency_conversion_rate,
2253 l_talv_rec.currency_conversion_date,
2254 -- Multi-Currency Change
2255 -- VRS Project - START
2256 l_talv_rec.RESIDUAL_SHR_PARTY_ID,
2257 l_talv_rec.RESIDUAL_SHR_AMOUNT,
2258 l_talv_rec.RETIREMENT_ID;
2259 -- VRS Project - END
2260
2261 x_no_data_found := okl_talv_pk_csr%NOTFOUND;
2262 CLOSE okl_talv_pk_csr;
2263 RETURN(l_talv_rec);
2264 END get_rec;
2265
2266 FUNCTION get_rec (
2267 p_talv_rec IN talv_rec_type
2268 ) RETURN talv_rec_type IS
2269 l_row_notfound BOOLEAN := TRUE;
2270 BEGIN
2271 RETURN(get_rec(p_talv_rec, l_row_notfound));
2272 END get_rec;
2273
2274 ------------------------------------------------------
2275 -- FUNCTION null_out_defaults for: OKL_TXL_ASSETS_V --
2276 ------------------------------------------------------
2277 FUNCTION null_out_defaults (
2278 p_talv_rec IN talv_rec_type
2279 ) RETURN talv_rec_type IS
2280 l_talv_rec talv_rec_type := p_talv_rec;
2281 BEGIN
2282 IF (l_talv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
2283 l_talv_rec.object_version_number := NULL;
2284 END IF;
2285 IF (l_talv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
2286 l_talv_rec.sfwt_flag := NULL;
2287 END IF;
2288 IF (l_talv_rec.tas_id = OKC_API.G_MISS_NUM) THEN
2289 l_talv_rec.tas_id := NULL;
2290 END IF;
2291 IF (l_talv_rec.ilo_id = OKC_API.G_MISS_NUM) THEN
2292 l_talv_rec.ilo_id := NULL;
2293 END IF;
2294 IF (l_talv_rec.ilo_id_old = OKC_API.G_MISS_NUM) THEN
2295 l_talv_rec.ilo_id_old := NULL;
2296 END IF;
2297 IF (l_talv_rec.iay_id = OKC_API.G_MISS_NUM) THEN
2298 l_talv_rec.iay_id := NULL;
2299 END IF;
2300 IF (l_talv_rec.iay_id_new = OKC_API.G_MISS_NUM) THEN
2301 l_talv_rec.iay_id_new := NULL;
2302 END IF;
2303 IF (l_talv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
2304 l_talv_rec.kle_id := NULL;
2305 END IF;
2306 IF (l_talv_rec.dnz_khr_id = OKC_API.G_MISS_NUM) THEN
2307 l_talv_rec.dnz_khr_id := NULL;
2308 END IF;
2309 IF (l_talv_rec.line_number = OKC_API.G_MISS_NUM) THEN
2310 l_talv_rec.line_number := NULL;
2311 END IF;
2312 IF (l_talv_rec.org_id = OKC_API.G_MISS_NUM) THEN
2313 l_talv_rec.org_id := NULL;
2314 END IF;
2315 IF (l_talv_rec.tal_type = OKC_API.G_MISS_CHAR) THEN
2316 l_talv_rec.tal_type := NULL;
2317 END IF;
2318 IF (l_talv_rec.asset_number = OKC_API.G_MISS_CHAR) THEN
2319 l_talv_rec.asset_number := NULL;
2320 END IF;
2321 IF (l_talv_rec.description = OKC_API.G_MISS_CHAR) THEN
2322 l_talv_rec.description := NULL;
2323 END IF;
2324 IF (l_talv_rec.fa_location_id = OKC_API.G_MISS_NUM) THEN
2325 l_talv_rec.fa_location_id := NULL;
2326 END IF;
2327 IF (l_talv_rec.original_cost = OKC_API.G_MISS_NUM) THEN
2328 l_talv_rec.original_cost := NULL;
2329 END IF;
2330 IF (l_talv_rec.current_units = OKC_API.G_MISS_NUM) THEN
2331 l_talv_rec.current_units := NULL;
2332 END IF;
2333 IF (l_talv_rec.manufacturer_name = OKC_API.G_MISS_CHAR) THEN
2334 l_talv_rec.manufacturer_name := NULL;
2335 END IF;
2336 IF (l_talv_rec.year_manufactured = OKC_API.G_MISS_NUM) THEN
2337 l_talv_rec.year_manufactured := NULL;
2338 END IF;
2339 IF (l_talv_rec.supplier_id = OKC_API.G_MISS_NUM) THEN
2340 l_talv_rec.supplier_id := NULL;
2341 END IF;
2342 IF (l_talv_rec.used_asset_yn = OKC_API.G_MISS_CHAR) THEN
2343 l_talv_rec.used_asset_yn := NULL;
2344 END IF;
2345 IF (l_talv_rec.tag_number = OKC_API.G_MISS_CHAR) THEN
2346 l_talv_rec.tag_number := NULL;
2347 END IF;
2348 IF (l_talv_rec.model_number = OKC_API.G_MISS_CHAR) THEN
2349 l_talv_rec.model_number := NULL;
2350 END IF;
2351 IF (l_talv_rec.corporate_book = OKC_API.G_MISS_CHAR) THEN
2352 l_talv_rec.corporate_book := NULL;
2353 END IF;
2354 IF (l_talv_rec.date_purchased = OKC_API.G_MISS_DATE) THEN
2355 l_talv_rec.date_purchased := NULL;
2356 END IF;
2357 IF (l_talv_rec.date_delivery = OKC_API.G_MISS_DATE) THEN
2358 l_talv_rec.date_delivery := NULL;
2359 END IF;
2360 IF (l_talv_rec.in_service_date = OKC_API.G_MISS_DATE) THEN
2361 l_talv_rec.in_service_date := NULL;
2362 END IF;
2363 IF (l_talv_rec.life_in_months = OKC_API.G_MISS_NUM) THEN
2364 l_talv_rec.life_in_months := NULL;
2365 END IF;
2366 IF (l_talv_rec.depreciation_id = OKC_API.G_MISS_NUM) THEN
2367 l_talv_rec.depreciation_id := NULL;
2368 END IF;
2369 IF (l_talv_rec.depreciation_cost = OKC_API.G_MISS_NUM) THEN
2370 l_talv_rec.depreciation_cost := NULL;
2371 END IF;
2372 IF (l_talv_rec.deprn_method = OKC_API.G_MISS_CHAR) THEN
2373 l_talv_rec.deprn_method := NULL;
2374 END IF;
2375 IF (l_talv_rec.deprn_rate = OKC_API.G_MISS_NUM) THEN
2376 l_talv_rec.deprn_rate := NULL;
2377 END IF;
2378 IF (l_talv_rec.salvage_value = OKC_API.G_MISS_NUM) THEN
2379 l_talv_rec.salvage_value := NULL;
2380 END IF;
2381 IF (l_talv_rec.percent_salvage_value = OKC_API.G_MISS_NUM) THEN
2382 l_talv_rec.percent_salvage_value := NULL;
2383 END IF;
2384 --Bug# 2981308
2385 IF (l_talv_rec.asset_key_id = OKL_API.G_MISS_NUM) THEN
2386 l_talv_rec.asset_key_id := NULL;
2387 END IF;
2388 -- Bug# 4028371
2389 IF (l_talv_rec.fa_trx_date = OKL_API.G_MISS_DATE) THEN
2390 l_talv_rec.fa_trx_date := NULL;
2391 END IF;
2392 --Bug# 4899328
2393 IF (l_talv_rec.fa_cost = OKL_API.G_MISS_NUM) THEN
2394 l_talv_rec.fa_cost := NULL;
2395 END IF;
2396 IF (l_talv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
2397 l_talv_rec.attribute_category := NULL;
2398 END IF;
2399 IF (l_talv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
2400 l_talv_rec.attribute1 := NULL;
2401 END IF;
2402 IF (l_talv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
2403 l_talv_rec.attribute2 := NULL;
2404 END IF;
2405 IF (l_talv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
2406 l_talv_rec.attribute3 := NULL;
2407 END IF;
2408 IF (l_talv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
2409 l_talv_rec.attribute4 := NULL;
2410 END IF;
2411 IF (l_talv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
2412 l_talv_rec.attribute5 := NULL;
2413 END IF;
2414 IF (l_talv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
2415 l_talv_rec.attribute6 := NULL;
2416 END IF;
2417 IF (l_talv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
2418 l_talv_rec.attribute7 := NULL;
2419 END IF;
2420 IF (l_talv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
2421 l_talv_rec.attribute8 := NULL;
2422 END IF;
2423 IF (l_talv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
2424 l_talv_rec.attribute9 := NULL;
2425 END IF;
2426 IF (l_talv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
2427 l_talv_rec.attribute10 := NULL;
2428 END IF;
2429 IF (l_talv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
2430 l_talv_rec.attribute11 := NULL;
2431 END IF;
2432 IF (l_talv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
2433 l_talv_rec.attribute12 := NULL;
2434 END IF;
2435 IF (l_talv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
2436 l_talv_rec.attribute13 := NULL;
2437 END IF;
2438 IF (l_talv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
2439 l_talv_rec.attribute14 := NULL;
2440 END IF;
2441 IF (l_talv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
2442 l_talv_rec.attribute15 := NULL;
2443 END IF;
2444 IF (l_talv_rec.created_by = OKC_API.G_MISS_NUM) THEN
2445 l_talv_rec.created_by := NULL;
2446 END IF;
2447 IF (l_talv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
2448 l_talv_rec.creation_date := NULL;
2449 END IF;
2450 IF (l_talv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
2451 l_talv_rec.last_updated_by := NULL;
2452 END IF;
2453 IF (l_talv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
2454 l_talv_rec.last_update_date := NULL;
2455 END IF;
2456 IF (l_talv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
2457 l_talv_rec.last_update_login := NULL;
2458 END IF;
2459 IF (l_talv_rec.depreciate_yn = OKC_API.G_MISS_CHAR) THEN
2460 l_talv_rec.depreciate_yn := NULL;
2461 END IF;
2462 IF (l_talv_rec.hold_period_days = OKC_API.G_MISS_NUM) THEN
2463 l_talv_rec.hold_period_days := NULL;
2464 END IF;
2465 IF (l_talv_rec.old_salvage_value = OKC_API.G_MISS_NUM) THEN
2466 l_talv_rec.old_salvage_value := NULL;
2467 END IF;
2468 IF (l_talv_rec.new_residual_value = OKC_API.G_MISS_NUM) THEN
2469 l_talv_rec.new_residual_value := NULL;
2470 END IF;
2471 IF (l_talv_rec.old_residual_value = OKC_API.G_MISS_NUM) THEN
2472 l_talv_rec.old_residual_value := NULL;
2473 END IF;
2474 IF (l_talv_rec.units_retired = OKC_API.G_MISS_NUM) THEN
2475 l_talv_rec.units_retired := NULL;
2476 END IF;
2477 IF (l_talv_rec.cost_retired = OKC_API.G_MISS_NUM) THEN
2478 l_talv_rec.cost_retired := NULL;
2479 END IF;
2480 IF (l_talv_rec.sale_proceeds = OKC_API.G_MISS_NUM) THEN
2481 l_talv_rec.sale_proceeds := NULL;
2482 END IF;
2483 IF (l_talv_rec.removal_cost = OKC_API.G_MISS_NUM) THEN
2484 l_talv_rec.removal_cost := NULL;
2485 END IF;
2486 IF (l_talv_rec.dnz_asset_id = OKC_API.G_MISS_NUM) THEN
2487 l_talv_rec.dnz_asset_id := NULL;
2488 END IF;
2489 IF (l_talv_rec.date_due = OKC_API.G_MISS_DATE) THEN
2490 l_talv_rec.date_due := NULL;
2491 END IF;
2492 IF (l_talv_rec.rep_asset_id = OKC_API.G_MISS_NUM) THEN
2493 l_talv_rec.rep_asset_id := NULL;
2494 END IF;
2495 IF (l_talv_rec.lke_asset_id = OKC_API.G_MISS_NUM) THEN
2496 l_talv_rec.lke_asset_id := NULL;
2497 END IF;
2498 IF (l_talv_rec.match_amount = OKC_API.G_MISS_NUM) THEN
2499 l_talv_rec.match_amount := NULL;
2500 END IF;
2501 IF (l_talv_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR) THEN
2502 l_talv_rec.split_into_singles_flag := NULL;
2503 END IF;
2504 IF (l_talv_rec.split_into_units = OKC_API.G_MISS_NUM) THEN
2505 l_talv_rec.split_into_units := NULL;
2506 END IF;
2507
2508 -- Multi-Currency Change
2509 IF (l_talv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN
2510 l_talv_rec.currency_code := NULL;
2511 END IF;
2512 IF (l_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
2513 l_talv_rec.currency_conversion_type := NULL;
2514 END IF;
2515 IF (l_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
2516 l_talv_rec.currency_conversion_rate:= NULL;
2517 END IF;
2518 IF (l_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
2519 l_talv_rec.currency_conversion_date := NULL;
2520 END IF;
2521 -- Multi-Currency Change
2522
2523 -- VRS Project - START
2524
2525 IF (l_talv_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM) THEN
2526 l_talv_rec.RESIDUAL_SHR_PARTY_ID:= NULL;
2527 END IF;
2528 IF (l_talv_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM) THEN
2529 l_talv_rec.RESIDUAL_SHR_AMOUNT:= NULL;
2530 END IF;
2531 IF (l_talv_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM) THEN
2532 l_talv_rec.RETIREMENT_ID := NULL;
2533 END IF;
2534
2535 -- VRS Project - END
2536
2537 RETURN(l_talv_rec);
2538 END null_out_defaults;
2539 ---------------------------------------------------------------------------
2540 -- PROCEDURE Validate_Attributes
2541 ---------------------------------------------------------------------------
2542 ----------------------------------------------
2543 -- Validate_Attributes for:OKL_TXL_ASSETS_V --
2544 ----------------------------------------------
2545 FUNCTION Validate_Attributes (
2546 p_talv_rec IN talv_rec_type
2547 ) RETURN VARCHAR2 IS
2548 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2549 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2550 BEGIN
2551 IF p_talv_rec.id = OKC_API.G_MISS_NUM OR
2552 p_talv_rec.id IS NULL THEN
2553 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
2554 x_return_status := OKC_API.G_RET_STS_ERROR;
2555 ELSIF p_talv_rec.object_version_number = OKC_API.G_MISS_NUM OR
2556 p_talv_rec.object_version_number IS NULL THEN
2557 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
2558 x_return_status := OKC_API.G_RET_STS_ERROR;
2559 ELSIF p_talv_rec.line_number = OKC_API.G_MISS_NUM OR
2560 p_talv_rec.line_number IS NULL THEN
2561 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'line_number');
2562 x_return_status := OKC_API.G_RET_STS_ERROR;
2563 ELSIF p_talv_rec.asset_number = OKC_API.G_MISS_CHAR OR
2564 p_talv_rec.asset_number IS NULL THEN
2565 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Asset_number');
2566 x_return_status := OKC_API.G_RET_STS_ERROR;
2567 ELSIF p_talv_rec.original_cost = OKC_API.G_MISS_NUM OR
2568 p_talv_rec.original_cost IS NULL THEN
2569 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'original_cost');
2570 x_return_status := OKC_API.G_RET_STS_ERROR;
2571 END IF;
2572 /************************ HAND-CODED *********************************/
2573 -- Calling the validation procedures for attributes
2574 validate_tas_id(x_return_status => l_return_status,
2575 p_talv_rec => p_talv_rec);
2576 -- Store the Highest Degree of Error
2577 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2578 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2579 x_return_status := l_return_status;
2580 END IF;
2581 END IF;
2582 l_return_status := x_return_status;
2583 validate_ilo_id(x_return_status => l_return_status,
2584 p_talv_rec => p_talv_rec);
2585 -- Store the Highest Degree of Error
2586 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2587 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2588 x_return_status := l_return_status;
2589 END IF;
2590 END IF;
2591 l_return_status := x_return_status;
2592 validate_ilo_id_old(x_return_status => l_return_status,
2593 p_talv_rec => p_talv_rec);
2594 -- Store the Highest Degree of Error
2595 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2596 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2597 x_return_status := l_return_status;
2598 END IF;
2599 END IF;
2600 l_return_status := x_return_status;
2601 validate_iay_id(x_return_status => l_return_status,
2602 p_talv_rec => p_talv_rec);
2603 -- Store the Highest Degree of Error
2604 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2605 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2606 x_return_status := l_return_status;
2607 END IF;
2608 END IF;
2609 l_return_status := x_return_status;
2610 validate_iay_id_new(x_return_status => l_return_status,
2611 p_talv_rec => p_talv_rec);
2612 -- Store the Highest Degree of Error
2613 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2614 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2615 x_return_status := l_return_status;
2616 END IF;
2617 END IF;
2618 l_return_status := x_return_status;
2619 validate_kle_id(x_return_status => l_return_status,
2620 p_talv_rec => p_talv_rec);
2621 -- Store the Highest Degree of Error
2622 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2623 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2624 x_return_status := l_return_status;
2625 END IF;
2626 END IF;
2627 l_return_status := x_return_status;
2628 validate_tal_type(x_return_status => l_return_status,
2629 p_talv_rec => p_talv_rec);
2630 -- Store the Highest Degree of Error
2631 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2632 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2633 x_return_status := l_return_status;
2634 END IF;
2635 END IF;
2636 l_return_status := x_return_status;
2637 validate_org_id(x_return_status => l_return_status,
2638 p_talv_rec => p_talv_rec);
2639 -- Store the Highest Degree of Error
2640 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2641 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2642 x_return_status := l_return_status;
2643 END IF;
2644 END IF;
2645 l_return_status := x_return_status;
2646 validate_current_units(x_return_status => l_return_status,
2647 p_talv_rec => p_talv_rec);
2648 -- Store the Highest Degree of Error
2649 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2650 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2651 x_return_status := l_return_status;
2652 END IF;
2653 END IF;
2654 l_return_status := x_return_status;
2655 validate_used_asset_yn(x_return_status => l_return_status,
2656 p_talv_rec => p_talv_rec);
2657 -- Store the Highest Degree of Error
2658 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2659 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2660 x_return_status := l_return_status;
2661 END IF;
2662 END IF;
2663 l_return_status := x_return_status;
2664 validate_life_in_months(x_return_status => l_return_status,
2665 p_talv_rec => p_talv_rec);
2666 -- Store the Highest Degree of Error
2667 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2668 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2669 x_return_status := l_return_status;
2670 END IF;
2671 END IF;
2672 l_return_status := x_return_status;
2673 validate_deprn_id(x_return_status => l_return_status,
2674 p_talv_rec => p_talv_rec);
2675 -- Store the Highest Degree of Error
2676 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2677 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2678 x_return_status := l_return_status;
2679 END IF;
2680 END IF;
2681 l_return_status := x_return_status;
2682 validate_fa_location_id(x_return_status => l_return_status,
2683 p_talv_rec => p_talv_rec);
2684 -- Store the Highest Degree of Error
2685 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2686 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2687 x_return_status := l_return_status;
2688 END IF;
2689 END IF;
2690 l_return_status := x_return_status;
2691 validate_dnz_khr_id(x_return_status => l_return_status,
2692 p_talv_rec => p_talv_rec);
2693 -- Store the Highest Degree of Error
2694 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2695 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2696 x_return_status := l_return_status;
2697 END IF;
2698 END IF;
2699 l_return_status := x_return_status;
2700 validate_corp_book(x_return_status => l_return_status,
2701 p_talv_rec => p_talv_rec);
2702 -- Store the Highest Degree of Error
2703 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2704 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2705 x_return_status := l_return_status;
2706 END IF;
2707 END IF;
2708 l_return_status := x_return_status;
2709 validate_deprn_method(x_return_status => l_return_status,
2710 p_talv_rec => p_talv_rec);
2711 --Bug# 2981308
2712 validate_asset_key (x_return_status => l_return_status,
2713 p_talv_rec => p_talv_rec);
2714
2715 -- Store the Highest Degree of Error
2716 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2717 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2718 x_return_status := l_return_status;
2719 END IF;
2720 END IF;
2721 l_return_status := x_return_status;
2722 RETURN(l_return_status);
2723 EXCEPTION
2724 WHEN OTHERS THEN
2725 -- store SQL error message on message stack
2726 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2727 p_msg_name => G_UNEXPECTED_ERROR,
2728 p_token1 => G_SQLCODE_TOKEN,
2729 p_token1_value => SQLCODE,
2730 p_token2 => G_SQLERRM_TOKEN,
2731 p_token2_value => SQLERRM);
2732 -- notify caller of an error as UNEXPETED error
2733 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2734 -- Return status to caller
2735 RETURN(x_return_status);
2736 /************************ HAND-CODED *********************************/
2737 END Validate_Attributes;
2738
2739 ---------------------------------------------------------------------------
2740 -- PROCEDURE Validate_Record
2741 ---------------------------------------------------------------------------
2742 ------------------------------------------
2743 -- Validate_Record for:OKL_TXL_ASSETS_V --
2744 ------------------------------------------
2745 FUNCTION Validate_Record (
2746 p_talv_rec IN talv_rec_type
2747 ) RETURN VARCHAR2 IS
2748 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2749 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2750 BEGIN
2751 /************************ HAND-CODED *********************************/
2752 validate_pds_date(x_return_status => l_return_status,
2753 p_talv_rec => p_talv_rec);
2754 -- Store the Highest Degree of Error
2755 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2756 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2757 x_return_status := l_return_status;
2758 END IF;
2759 END IF;
2760 l_return_status := x_return_status;
2761 --start:| 23-May-2008 cklee fixed bug: 6781324 |
2762 -- move this check to QA checker
2763 /*
2764 validate_salv_oec(x_return_status => l_return_status,
2765 p_talv_rec => p_talv_rec);
2766 -- Store the Highest Degree of Error
2767 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2768 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2769 x_return_status := l_return_status;
2770 END IF;
2771 END IF;
2772 */
2773 --end:| 23-May-2008 cklee fixed bug: 6781324 |
2774 l_return_status := x_return_status;
2775 RETURN (l_return_status);
2776 EXCEPTION
2777 WHEN OTHERS THEN
2778 -- store SQL error message on message stack
2779 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2780 p_msg_name => G_UNEXPECTED_ERROR,
2781 p_token1 => G_SQLCODE_TOKEN,
2782 p_token1_value => SQLCODE,
2783 p_token2 => G_SQLERRM_TOKEN,
2784 p_token2_value => SQLERRM);
2785 -- notify caller of an error as UNEXPETED error
2786 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2787 -- Return status to caller
2788 RETURN(x_return_status);
2789 /************************ HAND-CODED *********************************/
2790 END Validate_Record;
2791
2792 ---------------------------------------------------------------------------
2793 -- PROCEDURE Migrate
2794 ---------------------------------------------------------------------------
2795 PROCEDURE migrate (
2796 p_from IN talv_rec_type,
2797 p_to IN OUT NOCOPY tal_rec_type
2798 ) IS
2799 BEGIN
2800 p_to.id := p_from.id;
2801 p_to.object_version_number := p_from.object_version_number;
2802 p_to.tas_id := p_from.tas_id;
2803 p_to.ilo_id := p_from.ilo_id;
2804 p_to.ilo_id_old := p_from.ilo_id_old;
2805 p_to.iay_id := p_from.iay_id;
2806 p_to.iay_id_new := p_from.iay_id_new;
2807 p_to.kle_id := p_from.kle_id;
2808 p_to.dnz_khr_id := p_from.dnz_khr_id;
2809 p_to.line_number := p_from.line_number;
2810 p_to.org_id := p_from.org_id;
2811 p_to.tal_type := p_from.tal_type;
2812 p_to.asset_number := p_from.asset_number;
2813 p_to.fa_location_id := p_from.fa_location_id;
2814 p_to.original_cost := p_from.original_cost;
2815 p_to.current_units := p_from.current_units;
2816 p_to.manufacturer_name := p_from.manufacturer_name;
2817 p_to.year_manufactured := p_from.year_manufactured;
2818 p_to.supplier_id := p_from.supplier_id;
2819 p_to.used_asset_yn := p_from.used_asset_yn;
2820 p_to.tag_number := p_from.tag_number;
2821 p_to.model_number := p_from.model_number;
2822 p_to.corporate_book := p_from.corporate_book;
2823 p_to.date_purchased := p_from.date_purchased;
2824 p_to.date_delivery := p_from.date_delivery;
2825 p_to.in_service_date := p_from.in_service_date;
2826 p_to.life_in_months := p_from.life_in_months;
2827 p_to.depreciation_id := p_from.depreciation_id;
2828 p_to.depreciation_cost := p_from.depreciation_cost;
2829 p_to.deprn_method := p_from.deprn_method;
2830 p_to.deprn_rate := p_from.deprn_rate;
2831 p_to.salvage_value := p_from.salvage_value;
2832 p_to.percent_salvage_value := p_from.percent_salvage_value;
2833 --Bug# 2981308
2834 p_to.asset_key_id := p_from.asset_key_id;
2835 -- Bug# 4028371
2836 p_to.fa_trx_date := p_from.fa_trx_date;
2837 --Bug# 4899328
2838 p_to.fa_cost := p_from.fa_cost;
2839 p_to.attribute_category := p_from.attribute_category;
2840 p_to.attribute1 := p_from.attribute1;
2841 p_to.attribute2 := p_from.attribute2;
2842 p_to.attribute3 := p_from.attribute3;
2843 p_to.attribute4 := p_from.attribute4;
2844 p_to.attribute5 := p_from.attribute5;
2845 p_to.attribute6 := p_from.attribute6;
2846 p_to.attribute7 := p_from.attribute7;
2847 p_to.attribute8 := p_from.attribute8;
2848 p_to.attribute9 := p_from.attribute9;
2849 p_to.attribute10 := p_from.attribute10;
2850 p_to.attribute11 := p_from.attribute11;
2851 p_to.attribute12 := p_from.attribute12;
2852 p_to.attribute13 := p_from.attribute13;
2853 p_to.attribute14 := p_from.attribute14;
2854 p_to.attribute15 := p_from.attribute15;
2855 p_to.created_by := p_from.created_by;
2856 p_to.creation_date := p_from.creation_date;
2857 p_to.last_updated_by := p_from.last_updated_by;
2858 p_to.last_update_date := p_from.last_update_date;
2859 p_to.last_update_login := p_from.last_update_login;
2860 p_to.depreciate_yn := p_from.depreciate_yn;
2861 p_to.hold_period_days := p_from.hold_period_days;
2862 p_to.old_salvage_value := p_from.old_salvage_value;
2863 p_to.new_residual_value := p_from.new_residual_value;
2864 p_to.old_residual_value := p_from.old_residual_value;
2865 p_to.units_retired := p_from.units_retired;
2866 p_to.cost_retired := p_from.cost_retired;
2867 p_to.sale_proceeds := p_from.sale_proceeds;
2868 p_to.removal_cost := p_from.removal_cost;
2869 p_to.dnz_asset_id := p_from.dnz_asset_id;
2870 p_to.date_due := p_from.date_due;
2871 p_to.rep_asset_id := p_from.rep_asset_id;
2872 p_to.lke_asset_id := p_from.lke_asset_id;
2873 p_to.match_amount := p_from.match_amount;
2874 p_to.split_into_singles_flag := p_from.split_into_singles_flag;
2875 p_to.split_into_units := p_from.split_into_units;
2876 -- Multi-Currency Change
2877 p_to.currency_code := p_from.currency_code;
2878 p_to.currency_conversion_type := p_from.currency_conversion_type;
2879 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2880 p_to.currency_conversion_date := p_from.currency_conversion_date;
2881 -- Multi-Currency Change
2882 -- VRS Project - START
2883 p_to.RESIDUAL_SHR_PARTY_ID := p_from.RESIDUAL_SHR_PARTY_ID;
2884 p_to.RESIDUAL_SHR_AMOUNT := p_from.RESIDUAL_SHR_AMOUNT;
2885 p_to.RETIREMENT_ID := p_from.RETIREMENT_ID;
2886 -- VRS Project - END
2887
2888 END migrate;
2889 PROCEDURE migrate (
2890 p_from IN tal_rec_type,
2891 p_to IN OUT NOCOPY talv_rec_type
2892 ) IS
2893 BEGIN
2894 p_to.id := p_from.id;
2895 p_to.object_version_number := p_from.object_version_number;
2896 p_to.tas_id := p_from.tas_id;
2897 p_to.ilo_id := p_from.ilo_id;
2898 p_to.ilo_id_old := p_from.ilo_id_old;
2899 p_to.iay_id := p_from.iay_id;
2900 p_to.iay_id_new := p_from.iay_id_new;
2901 p_to.kle_id := p_from.kle_id;
2902 p_to.dnz_khr_id := p_from.dnz_khr_id;
2903 p_to.line_number := p_from.line_number;
2904 p_to.org_id := p_from.org_id;
2905 p_to.tal_type := p_from.tal_type;
2906 p_to.asset_number := p_from.asset_number;
2907 p_to.fa_location_id := p_from.fa_location_id;
2908 p_to.original_cost := p_from.original_cost;
2909 p_to.current_units := p_from.current_units;
2910 p_to.manufacturer_name := p_from.manufacturer_name;
2911 p_to.year_manufactured := p_from.year_manufactured;
2912 p_to.supplier_id := p_from.supplier_id;
2913 p_to.used_asset_yn := p_from.used_asset_yn;
2914 p_to.tag_number := p_from.tag_number;
2915 p_to.model_number := p_from.model_number;
2916 p_to.corporate_book := p_from.corporate_book;
2917 p_to.date_purchased := p_from.date_purchased;
2918 p_to.date_delivery := p_from.date_delivery;
2919 p_to.in_service_date := p_from.in_service_date;
2920 p_to.life_in_months := p_from.life_in_months;
2921 p_to.depreciation_id := p_from.depreciation_id;
2922 p_to.depreciation_cost := p_from.depreciation_cost;
2923 p_to.deprn_method := p_from.deprn_method;
2924 p_to.deprn_rate := p_from.deprn_rate;
2925 p_to.salvage_value := p_from.salvage_value;
2926 p_to.percent_salvage_value := p_from.percent_salvage_value;
2927 --Bug# 2981308 :
2928 p_to.asset_key_id := p_from.asset_key_id;
2929 -- Bug# 4028371
2930 p_to.fa_trx_date := p_from.fa_trx_date;
2931 --bug# 4899328
2932 p_to.fa_cost := p_from.fa_cost;
2933 p_to.attribute_category := p_from.attribute_category;
2934 p_to.attribute1 := p_from.attribute1;
2935 p_to.attribute2 := p_from.attribute2;
2936 p_to.attribute3 := p_from.attribute3;
2937 p_to.attribute4 := p_from.attribute4;
2938 p_to.attribute5 := p_from.attribute5;
2939 p_to.attribute6 := p_from.attribute6;
2940 p_to.attribute7 := p_from.attribute7;
2941 p_to.attribute8 := p_from.attribute8;
2942 p_to.attribute9 := p_from.attribute9;
2943 p_to.attribute10 := p_from.attribute10;
2944 p_to.attribute11 := p_from.attribute11;
2945 p_to.attribute12 := p_from.attribute12;
2946 p_to.attribute13 := p_from.attribute13;
2947 p_to.attribute14 := p_from.attribute14;
2948 p_to.attribute15 := p_from.attribute15;
2949 p_to.created_by := p_from.created_by;
2950 p_to.creation_date := p_from.creation_date;
2951 p_to.last_updated_by := p_from.last_updated_by;
2952 p_to.last_update_date := p_from.last_update_date;
2953 p_to.last_update_login := p_from.last_update_login;
2954 p_to.depreciate_yn := p_from.depreciate_yn;
2955 p_to.hold_period_days := p_from.hold_period_days;
2956 p_to.old_salvage_value := p_from.old_salvage_value;
2957 p_to.new_residual_value := p_from.new_residual_value;
2958 p_to.old_residual_value := p_from.old_residual_value;
2959 p_to.units_retired := p_from.units_retired;
2960 p_to.cost_retired := p_from.cost_retired;
2961 p_to.sale_proceeds := p_from.sale_proceeds;
2962 p_to.removal_cost := p_from.removal_cost;
2963 p_to.dnz_asset_id := p_from.dnz_asset_id;
2964 p_to.date_due := p_from.date_due;
2965 p_to.rep_asset_id := p_from.rep_asset_id;
2966 p_to.lke_asset_id := p_from.lke_asset_id;
2967 p_to.match_amount := p_from.match_amount;
2968 p_to.split_into_singles_flag := p_from.split_into_singles_flag;
2969 p_to.split_into_units := p_from.split_into_units;
2970 -- Multi-Currency Change
2971 p_to.currency_code := p_from.currency_code;
2972 p_to.currency_conversion_type := p_from.currency_conversion_type;
2973 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2974 p_to.currency_conversion_date := p_from.currency_conversion_date;
2975 -- Multi-Currency Change
2976 -- VRS Project - START
2977 p_to.RESIDUAL_SHR_PARTY_ID := p_from.RESIDUAL_SHR_PARTY_ID;
2978 p_to.RESIDUAL_SHR_AMOUNT := p_from.RESIDUAL_SHR_AMOUNT;
2979 p_to.RETIREMENT_ID := p_from.RETIREMENT_ID;
2980 -- VRS Project - END
2981
2982 END migrate;
2983 PROCEDURE migrate (
2984 p_from IN talv_rec_type,
2985 p_to IN OUT NOCOPY okl_txl_assets_tl_rec_type
2986 ) IS
2987 BEGIN
2988 p_to.id := p_from.id;
2989 p_to.sfwt_flag := p_from.sfwt_flag;
2990 p_to.description := p_from.description;
2991 p_to.created_by := p_from.created_by;
2992 p_to.creation_date := p_from.creation_date;
2993 p_to.last_updated_by := p_from.last_updated_by;
2994 p_to.last_update_date := p_from.last_update_date;
2995 p_to.last_update_login := p_from.last_update_login;
2996 END migrate;
2997 PROCEDURE migrate (
2998 p_from IN okl_txl_assets_tl_rec_type,
2999 p_to IN OUT NOCOPY talv_rec_type
3000 ) IS
3001 BEGIN
3002 p_to.id := p_from.id;
3003 p_to.sfwt_flag := p_from.sfwt_flag;
3004 p_to.description := p_from.description;
3005 p_to.created_by := p_from.created_by;
3006 p_to.creation_date := p_from.creation_date;
3007 p_to.last_updated_by := p_from.last_updated_by;
3008 p_to.last_update_date := p_from.last_update_date;
3009 p_to.last_update_login := p_from.last_update_login;
3010 END migrate;
3011
3012 ---------------------------------------------------------------------------
3013 -- PROCEDURE validate_row
3014 ---------------------------------------------------------------------------
3015 ---------------------------------------
3016 -- validate_row for:OKL_TXL_ASSETS_V --
3017 ---------------------------------------
3018 PROCEDURE validate_row(
3019 p_api_version IN NUMBER,
3020 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3021 x_return_status OUT NOCOPY VARCHAR2,
3022 x_msg_count OUT NOCOPY NUMBER,
3023 x_msg_data OUT NOCOPY VARCHAR2,
3024 p_talv_rec IN talv_rec_type) IS
3025
3026 l_api_version CONSTANT NUMBER := 1;
3027 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
3028 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3029 l_talv_rec talv_rec_type := p_talv_rec;
3030 l_tal_rec tal_rec_type;
3031 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3032 BEGIN
3033 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3034 G_PKG_NAME,
3035 p_init_msg_list,
3036 l_api_version,
3037 p_api_version,
3038 '_PVT',
3039 x_return_status);
3040 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3041 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3042 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3043 RAISE OKC_API.G_EXCEPTION_ERROR;
3044 END IF;
3045 --- Validate all non-missing attributes (Item Level Validation)
3046 l_return_status := Validate_Attributes(l_talv_rec);
3047 --- If any errors happen abort API
3048 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3049 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3050 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3051 RAISE OKC_API.G_EXCEPTION_ERROR;
3052 END IF;
3053 l_return_status := Validate_Record(l_talv_rec);
3054 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3056 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3057 RAISE OKC_API.G_EXCEPTION_ERROR;
3058 END IF;
3059 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3060 EXCEPTION
3061 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3062 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3063 (
3064 l_api_name,
3065 G_PKG_NAME,
3066 'OKC_API.G_RET_STS_ERROR',
3067 x_msg_count,
3068 x_msg_data,
3069 '_PVT'
3070 );
3071 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3072 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3073 (
3074 l_api_name,
3075 G_PKG_NAME,
3076 'OKC_API.G_RET_STS_UNEXP_ERROR',
3077 x_msg_count,
3078 x_msg_data,
3079 '_PVT'
3080 );
3081 WHEN OTHERS THEN
3082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3083 (
3084 l_api_name,
3085 G_PKG_NAME,
3086 'OTHERS',
3087 x_msg_count,
3088 x_msg_data,
3089 '_PVT'
3090 );
3091 END validate_row;
3092 ------------------------------------------
3093 -- PL/SQL TBL validate_row for:TALV_TBL --
3094 ------------------------------------------
3095 PROCEDURE validate_row(
3096 p_api_version IN NUMBER,
3097 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3098 x_return_status OUT NOCOPY VARCHAR2,
3099 x_msg_count OUT NOCOPY NUMBER,
3100 x_msg_data OUT NOCOPY VARCHAR2,
3101 p_talv_tbl IN talv_tbl_type) IS
3102
3103 l_api_version CONSTANT NUMBER := 1;
3104 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
3105 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3106 i NUMBER := 0;
3107 BEGIN
3108 OKC_API.init_msg_list(p_init_msg_list);
3109 -- Make sure PL/SQL table has records in it before passing
3110 IF (p_talv_tbl.COUNT > 0) THEN
3111 i := p_talv_tbl.FIRST;
3112 LOOP
3113 validate_row (
3114 p_api_version => p_api_version,
3115 p_init_msg_list => OKC_API.G_FALSE,
3116 x_return_status => x_return_status,
3117 x_msg_count => x_msg_count,
3118 x_msg_data => x_msg_data,
3119 p_talv_rec => p_talv_tbl(i));
3120 EXIT WHEN (i = p_talv_tbl.LAST);
3121 i := p_talv_tbl.NEXT(i);
3122 END LOOP;
3123 END IF;
3124 EXCEPTION
3125 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3126 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3127 (
3128 l_api_name,
3129 G_PKG_NAME,
3130 'OKC_API.G_RET_STS_ERROR',
3131 x_msg_count,
3132 x_msg_data,
3133 '_PVT'
3134 );
3135 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3136 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3137 (
3138 l_api_name,
3139 G_PKG_NAME,
3140 'OKC_API.G_RET_STS_UNEXP_ERROR',
3141 x_msg_count,
3142 x_msg_data,
3143 '_PVT'
3144 );
3145 WHEN OTHERS THEN
3146 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3147 (
3148 l_api_name,
3149 G_PKG_NAME,
3150 'OTHERS',
3151 x_msg_count,
3152 x_msg_data,
3153 '_PVT'
3154 );
3155 END validate_row;
3156
3157 ---------------------------------------------------------------------------
3158 -- PROCEDURE insert_row
3159 ---------------------------------------------------------------------------
3160 -------------------------------------
3161 -- insert_row for:OKL_TXL_ASSETS_B --
3162 -------------------------------------
3163 PROCEDURE insert_row(
3164 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3165 x_return_status OUT NOCOPY VARCHAR2,
3166 x_msg_count OUT NOCOPY NUMBER,
3167 x_msg_data OUT NOCOPY VARCHAR2,
3168 p_tal_rec IN tal_rec_type,
3169 x_tal_rec OUT NOCOPY tal_rec_type) IS
3170
3171 l_api_version CONSTANT NUMBER := 1;
3172 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
3173 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3174 l_tal_rec tal_rec_type := p_tal_rec;
3175 l_def_tal_rec tal_rec_type;
3176 -----------------------------------------
3177 -- Set_Attributes for:OKL_TXL_ASSETS_B --
3178 -----------------------------------------
3179 FUNCTION Set_Attributes (
3180 p_tal_rec IN tal_rec_type,
3181 x_tal_rec OUT NOCOPY tal_rec_type
3182 ) RETURN VARCHAR2 IS
3183 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3184 BEGIN
3185 x_tal_rec := p_tal_rec;
3186 RETURN(l_return_status);
3187 END Set_Attributes;
3188 BEGIN
3189 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3190 p_init_msg_list,
3191 '_PVT',
3192 x_return_status);
3193 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3194 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3195 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3196 RAISE OKC_API.G_EXCEPTION_ERROR;
3197 END IF;
3198 --- Setting item attributes
3199 l_return_status := Set_Attributes(
3200 p_tal_rec, -- IN
3201 l_tal_rec); -- OUT
3202 --- If any errors happen abort API
3203 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3204 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3205 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3206 RAISE OKC_API.G_EXCEPTION_ERROR;
3207 END IF;
3208 INSERT INTO OKL_TXL_ASSETS_B(
3209 id,
3210 object_version_number,
3211 tas_id,
3212 ilo_id,
3213 ilo_id_old,
3214 iay_id,
3215 iay_id_new,
3216 kle_id,
3217 dnz_khr_id,
3218 line_number,
3219 org_id,
3220 tal_type,
3221 asset_number,
3222 fa_location_Id,
3223 original_cost,
3224 current_units,
3225 manufacturer_name,
3226 year_manufactured,
3227 supplier_id,
3228 used_asset_yn,
3229 tag_number,
3230 model_number,
3231 corporate_book,
3232 date_purchased,
3233 date_delivery,
3234 in_service_date,
3235 life_in_months,
3236 depreciation_id,
3237 depreciation_cost,
3238 deprn_method,
3239 deprn_rate,
3240 salvage_value,
3241 percent_salvage_value,
3242 --Bug# 2981308
3243 asset_key_id,
3244 -- Bug# 4028371
3245 fa_trx_date,
3246 --Bug# 4899328
3247 fa_cost,
3248 attribute_category,
3249 attribute1,
3250 attribute2,
3251 attribute3,
3252 attribute4,
3253 attribute5,
3254 attribute6,
3255 attribute7,
3256 attribute8,
3257 attribute9,
3258 attribute10,
3259 attribute11,
3260 attribute12,
3261 attribute13,
3262 attribute14,
3263 attribute15,
3264 created_by,
3265 creation_date,
3266 last_updated_by,
3267 last_update_date,
3268 last_update_login,
3269 depreciate_yn,
3270 hold_period_days,
3271 old_salvage_value,
3272 new_residual_value,
3273 old_residual_value,
3274 units_retired,
3275 cost_retired,
3276 sale_proceeds,
3277 removal_cost,
3278 dnz_asset_id,
3279 date_due,
3280 rep_asset_id,
3281 lke_asset_id,
3282 match_amount,
3283 split_into_singles_flag,
3284 split_into_units,
3285 -- Multi-Currency Change
3286 currency_code,
3287 currency_conversion_type,
3288 currency_conversion_rate,
3289 currency_conversion_date,
3290 -- Multi-Currency Change
3291 -- VRS Project - END
3292 RESIDUAL_SHR_PARTY_ID,
3293 RESIDUAL_SHR_AMOUNT,
3294 RETIREMENT_ID
3295 -- VRS Project - END
3296 )
3297 VALUES (
3298 l_tal_rec.id,
3299 l_tal_rec.object_version_number,
3300 l_tal_rec.tas_id,
3301 l_tal_rec.ilo_id,
3302 l_tal_rec.ilo_id_old,
3303 l_tal_rec.iay_id,
3304 l_tal_rec.iay_id_new,
3305 l_tal_rec.kle_id,
3306 l_tal_rec.dnz_khr_id,
3307 l_tal_rec.line_number,
3308 l_tal_rec.org_id,
3309 l_tal_rec.tal_type,
3310 l_tal_rec.asset_number,
3311 l_tal_rec.fa_location_id,
3312 l_tal_rec.original_cost,
3313 l_tal_rec.current_units,
3314 l_tal_rec.manufacturer_name,
3315 l_tal_rec.year_manufactured,
3316 l_tal_rec.supplier_id,
3317 l_tal_rec.used_asset_yn,
3318 l_tal_rec.tag_number,
3319 l_tal_rec.model_number,
3320 l_tal_rec.corporate_book,
3321 l_tal_rec.date_purchased,
3322 l_tal_rec.date_delivery,
3323 l_tal_rec.in_service_date,
3324 l_tal_rec.life_in_months,
3325 l_tal_rec.depreciation_id,
3326 l_tal_rec.depreciation_cost,
3327 l_tal_rec.deprn_method,
3328 l_tal_rec.deprn_rate,
3329 l_tal_rec.salvage_value,
3330 l_tal_rec.percent_salvage_value,
3331 --Bug# 2981308
3332 l_tal_rec.asset_key_id,
3333 -- Bug# 4028371
3334 l_tal_rec.fa_trx_date,
3335 --bug# 4899328
3336 l_tal_rec.fa_cost,
3337 l_tal_rec.attribute_category,
3338 l_tal_rec.attribute1,
3339 l_tal_rec.attribute2,
3340 l_tal_rec.attribute3,
3341 l_tal_rec.attribute4,
3342 l_tal_rec.attribute5,
3343 l_tal_rec.attribute6,
3344 l_tal_rec.attribute7,
3345 l_tal_rec.attribute8,
3346 l_tal_rec.attribute9,
3347 l_tal_rec.attribute10,
3348 l_tal_rec.attribute11,
3349 l_tal_rec.attribute12,
3350 l_tal_rec.attribute13,
3351 l_tal_rec.attribute14,
3352 l_tal_rec.attribute15,
3353 l_tal_rec.created_by,
3354 l_tal_rec.creation_date,
3355 l_tal_rec.last_updated_by,
3356 l_tal_rec.last_update_date,
3357 l_tal_rec.last_update_login,
3358 l_tal_rec.depreciate_yn,
3359 l_tal_rec.hold_period_days,
3360 l_tal_rec.old_salvage_value,
3361 l_tal_rec.new_residual_value,
3362 l_tal_rec.old_residual_value,
3363 l_tal_rec.units_retired,
3364 l_tal_rec.cost_retired,
3365 l_tal_rec.sale_proceeds,
3366 l_tal_rec.removal_cost,
3367 l_tal_rec.dnz_asset_id,
3368 l_tal_rec.date_due,
3369 l_tal_rec.rep_asset_id,
3370 l_tal_rec.lke_asset_id,
3371 l_tal_rec.match_amount,
3372 l_tal_rec.split_into_singles_flag,
3373 l_tal_rec.split_into_units,
3374 -- Multi-Currency Change
3375 l_tal_rec.currency_code,
3376 l_tal_rec.currency_conversion_type,
3377 l_tal_rec.currency_conversion_rate,
3378 l_tal_rec.currency_conversion_date,
3379 -- Multi-Currency Change
3380 -- VRS Project - END
3381 l_tal_rec.RESIDUAL_SHR_PARTY_ID,
3382 l_tal_rec.RESIDUAL_SHR_AMOUNT,
3383 l_tal_rec.RETIREMENT_ID
3384 -- VRS Project - END
3385 );
3386
3387 -- Set OUT values
3388 x_tal_rec := l_tal_rec;
3389 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3390 EXCEPTION
3391 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3392 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3393 (
3394 l_api_name,
3395 G_PKG_NAME,
3396 'OKC_API.G_RET_STS_ERROR',
3397 x_msg_count,
3398 x_msg_data,
3399 '_PVT'
3400 );
3401 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3402 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3403 (
3404 l_api_name,
3405 G_PKG_NAME,
3406 'OKC_API.G_RET_STS_UNEXP_ERROR',
3407 x_msg_count,
3408 x_msg_data,
3409 '_PVT'
3410 );
3411 WHEN OTHERS THEN
3412 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3413 (
3414 l_api_name,
3415 G_PKG_NAME,
3416 'OTHERS',
3417 x_msg_count,
3418 x_msg_data,
3419 '_PVT'
3420 );
3421 END insert_row;
3422 --------------------------------------
3423 -- insert_row for:OKL_TXL_ASSETS_TL --
3424 --------------------------------------
3425 PROCEDURE insert_row(
3426 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3427 x_return_status OUT NOCOPY VARCHAR2,
3428 x_msg_count OUT NOCOPY NUMBER,
3429 x_msg_data OUT NOCOPY VARCHAR2,
3430 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
3431 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type) IS
3432
3433 l_api_version CONSTANT NUMBER := 1;
3434 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3435 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3436 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type := p_okl_txl_assets_tl_rec;
3437 l_def_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3438 CURSOR get_languages IS
3439 SELECT *
3440 FROM FND_LANGUAGES
3441 WHERE INSTALLED_FLAG IN ('I', 'B');
3442 ------------------------------------------
3443 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
3444 ------------------------------------------
3445 FUNCTION Set_Attributes (
3446 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
3447 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
3448 ) RETURN VARCHAR2 IS
3449 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3450 BEGIN
3451 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
3452 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
3453 x_okl_txl_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
3454 RETURN(l_return_status);
3455 END Set_Attributes;
3456 BEGIN
3457 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3458 p_init_msg_list,
3459 '_PVT',
3460 x_return_status);
3461 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3462 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3463 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3464 RAISE OKC_API.G_EXCEPTION_ERROR;
3465 END IF;
3466 --- Setting item attributes
3467 l_return_status := Set_Attributes(
3468 p_okl_txl_assets_tl_rec, -- IN
3469 l_okl_txl_assets_tl_rec); -- OUT
3470 --- If any errors happen abort API
3471 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3473 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3474 RAISE OKC_API.G_EXCEPTION_ERROR;
3475 END IF;
3476 FOR l_lang_rec IN get_languages LOOP
3477 l_okl_txl_assets_tl_rec.language := l_lang_rec.language_code;
3478 INSERT INTO OKL_TXL_ASSETS_TL(
3479 id,
3480 language,
3481 source_lang,
3482 sfwt_flag,
3483 description,
3484 created_by,
3485 creation_date,
3486 last_updated_by,
3487 last_update_date,
3488 last_update_login)
3489 VALUES (
3490 l_okl_txl_assets_tl_rec.id,
3491 l_okl_txl_assets_tl_rec.language,
3492 l_okl_txl_assets_tl_rec.source_lang,
3493 l_okl_txl_assets_tl_rec.sfwt_flag,
3494 l_okl_txl_assets_tl_rec.description,
3495 l_okl_txl_assets_tl_rec.created_by,
3496 l_okl_txl_assets_tl_rec.creation_date,
3497 l_okl_txl_assets_tl_rec.last_updated_by,
3498 l_okl_txl_assets_tl_rec.last_update_date,
3499 l_okl_txl_assets_tl_rec.last_update_login);
3500 END LOOP;
3501 -- Set OUT values
3502 x_okl_txl_assets_tl_rec := l_okl_txl_assets_tl_rec;
3503 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3504 EXCEPTION
3505 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3506 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3507 (
3508 l_api_name,
3509 G_PKG_NAME,
3510 'OKC_API.G_RET_STS_ERROR',
3511 x_msg_count,
3512 x_msg_data,
3513 '_PVT'
3514 );
3515 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3516 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3517 (
3518 l_api_name,
3519 G_PKG_NAME,
3520 'OKC_API.G_RET_STS_UNEXP_ERROR',
3521 x_msg_count,
3522 x_msg_data,
3523 '_PVT'
3524 );
3525 WHEN OTHERS THEN
3526 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3527 (
3528 l_api_name,
3529 G_PKG_NAME,
3530 'OTHERS',
3531 x_msg_count,
3532 x_msg_data,
3533 '_PVT'
3534 );
3535 END insert_row;
3536 -------------------------------------
3537 -- insert_row for:OKL_TXL_ASSETS_V --
3538 -------------------------------------
3539 PROCEDURE insert_row(
3540 p_api_version IN NUMBER,
3541 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3542 x_return_status OUT NOCOPY VARCHAR2,
3543 x_msg_count OUT NOCOPY NUMBER,
3544 x_msg_data OUT NOCOPY VARCHAR2,
3545 p_talv_rec IN talv_rec_type,
3546 x_talv_rec OUT NOCOPY talv_rec_type) IS
3547
3548 l_api_version CONSTANT NUMBER := 1;
3549 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3550 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3551 l_talv_rec talv_rec_type;
3552 l_def_talv_rec talv_rec_type;
3553 l_tal_rec tal_rec_type;
3554 lx_tal_rec tal_rec_type;
3555 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3556 lx_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3557 lx_temp_talv_rec talv_rec_type;
3558 -------------------------------
3559 -- FUNCTION fill_who_columns --
3560 -------------------------------
3561 FUNCTION fill_who_columns (
3562 p_talv_rec IN talv_rec_type
3563 ) RETURN talv_rec_type IS
3564 l_talv_rec talv_rec_type := p_talv_rec;
3565 BEGIN
3566 l_talv_rec.CREATION_DATE := SYSDATE;
3567 l_talv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3568 l_talv_rec.LAST_UPDATE_DATE := SYSDATE;
3569 l_talv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3570 l_talv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3571 RETURN(l_talv_rec);
3572 END fill_who_columns;
3573 -----------------------------------------
3574 -- Set_Attributes for:OKL_TXL_ASSETS_V --
3575 -----------------------------------------
3576 FUNCTION Set_Attributes (
3577 p_talv_rec IN talv_rec_type,
3578 x_talv_rec OUT NOCOPY talv_rec_type
3579 ) RETURN VARCHAR2 IS
3580 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3581 BEGIN
3582 x_talv_rec := p_talv_rec;
3583 x_talv_rec.OBJECT_VERSION_NUMBER := 1;
3584 x_talv_rec.SFWT_FLAG := 'N';
3585 RETURN(l_return_status);
3586 END Set_Attributes;
3587 BEGIN
3588 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3589 G_PKG_NAME,
3590 p_init_msg_list,
3591 l_api_version,
3592 p_api_version,
3593 '_PVT',
3594 x_return_status);
3595 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3596 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3597 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3598 RAISE OKC_API.G_EXCEPTION_ERROR;
3599 END IF;
3600 l_talv_rec := null_out_defaults(p_talv_rec);
3601 -- Set primary key value
3602 l_talv_rec.ID := get_seq_id;
3603 --- Setting item attributes
3604 l_return_status := Set_Attributes(
3605 l_talv_rec, -- IN
3606 l_def_talv_rec); -- OUT
3607 --- If any errors happen abort API
3608 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3609 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3610 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3611 RAISE OKC_API.G_EXCEPTION_ERROR;
3612 END IF;
3613 l_def_talv_rec := fill_who_columns(l_def_talv_rec);
3614 --- Validate all non-missing attributes (Item Level Validation)
3615 l_return_status := Validate_Attributes(l_def_talv_rec);
3616 --- If any errors happen abort API
3617
3618 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3619 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3620 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3621 RAISE OKC_API.G_EXCEPTION_ERROR;
3622 END IF;
3623 l_return_status := Validate_Record(l_def_talv_rec);
3624 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3625 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3626 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3627 RAISE OKC_API.G_EXCEPTION_ERROR;
3628 END IF;
3629
3630 --
3631 -- Multi-Currency Change, dedey, 12/04/2002
3632 --
3633 validate_currency(
3634 x_return_status => l_return_status,
3635 p_talv_rec => l_def_talv_rec,
3636 x_talv_rec => lx_temp_talv_rec
3637 );
3638
3639 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3640 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3641 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3642 RAISE OKC_API.G_EXCEPTION_ERROR;
3643 END IF;
3644 l_def_talv_rec := lx_temp_talv_rec;
3645
3646 --dbms_output.put_line('After Change: '||lx_temp_talv_rec.currency_code);
3647 --dbms_output.put_line('After Change: '||l_def_talv_rec.currency_code);
3648 --
3649 -- Multi-Currency Change
3650 --
3651
3652 -- Fix Bug# 2737014
3653 --
3654 -- Round off amounts
3655 --
3656 roundoff_line_amount(
3657 x_return_status => l_return_status,
3658 x_msg_count => x_msg_count,
3659 x_msg_data => x_msg_data,
3660 p_talv_rec => l_def_talv_rec,
3661 x_talv_rec => lx_temp_talv_rec
3662 );
3663
3664 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3665 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3666 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3667 RAISE OKC_API.G_EXCEPTION_ERROR;
3668 END IF;
3669 l_def_talv_rec := lx_temp_talv_rec;
3670
3671 --dbms_output.put_line('After Change Orig cost: '||lx_temp_talv_rec.original_cost);
3672 --dbms_output.put_line('After Change Orig cost: '||l_def_talv_rec.original_cost);
3673
3674
3675 --------------------------------------
3676 -- Move VIEW record to "Child" records
3677 --------------------------------------
3678 migrate(l_def_talv_rec, l_tal_rec);
3679 migrate(l_def_talv_rec, l_okl_txl_assets_tl_rec);
3680
3681 --dbms_output.put_line('After migrate: '||l_tal_rec.currency_code);
3682 --------------------------------------------
3683 -- Call the INSERT_ROW for each child record
3684 --------------------------------------------
3685 insert_row(
3686 p_init_msg_list,
3687 x_return_status,
3688 x_msg_count,
3689 x_msg_data,
3690 l_tal_rec,
3691 lx_tal_rec
3692 );
3693 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3694 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3695 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3696 RAISE OKC_API.G_EXCEPTION_ERROR;
3697 END IF;
3698 migrate(lx_tal_rec, l_def_talv_rec);
3699 insert_row(
3700 p_init_msg_list,
3701 x_return_status,
3702 x_msg_count,
3703 x_msg_data,
3704 l_okl_txl_assets_tl_rec,
3705 lx_okl_txl_assets_tl_rec
3706 );
3707 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3708 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3709 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3710 RAISE OKC_API.G_EXCEPTION_ERROR;
3711 END IF;
3712 migrate(lx_okl_txl_assets_tl_rec, l_def_talv_rec);
3713 -- Set OUT values
3714 x_talv_rec := l_def_talv_rec;
3715 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3716 EXCEPTION
3717 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3718 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3719 (
3720 l_api_name,
3721 G_PKG_NAME,
3722 'OKC_API.G_RET_STS_ERROR',
3723 x_msg_count,
3724 x_msg_data,
3725 '_PVT'
3726 );
3727 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3728 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3729 (
3730 l_api_name,
3731 G_PKG_NAME,
3732 'OKC_API.G_RET_STS_UNEXP_ERROR',
3733 x_msg_count,
3734 x_msg_data,
3735 '_PVT'
3736 );
3737 WHEN OTHERS THEN
3738 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3739 (
3740 l_api_name,
3741 G_PKG_NAME,
3742 'OTHERS',
3743 x_msg_count,
3744 x_msg_data,
3745 '_PVT'
3746 );
3747 END insert_row;
3748 ----------------------------------------
3749 -- PL/SQL TBL insert_row for:TALV_TBL --
3750 ----------------------------------------
3751 PROCEDURE insert_row(
3752 p_api_version IN NUMBER,
3753 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3754 x_return_status OUT NOCOPY VARCHAR2,
3755 x_msg_count OUT NOCOPY NUMBER,
3756 x_msg_data OUT NOCOPY VARCHAR2,
3757 p_talv_tbl IN talv_tbl_type,
3758 x_talv_tbl OUT NOCOPY talv_tbl_type) IS
3759
3760 l_api_version CONSTANT NUMBER := 1;
3761 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3762 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3763 i NUMBER := 0;
3764 BEGIN
3765 OKC_API.init_msg_list(p_init_msg_list);
3766 -- Make sure PL/SQL table has records in it before passing
3767 IF (p_talv_tbl.COUNT > 0) THEN
3768 i := p_talv_tbl.FIRST;
3769 LOOP
3770 insert_row (
3771 p_api_version => p_api_version,
3772 p_init_msg_list => OKC_API.G_FALSE,
3773 x_return_status => x_return_status,
3774 x_msg_count => x_msg_count,
3775 x_msg_data => x_msg_data,
3776 p_talv_rec => p_talv_tbl(i),
3777 x_talv_rec => x_talv_tbl(i));
3778 EXIT WHEN (i = p_talv_tbl.LAST);
3779 i := p_talv_tbl.NEXT(i);
3780 END LOOP;
3781 END IF;
3782 EXCEPTION
3783 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3784 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3785 (
3786 l_api_name,
3787 G_PKG_NAME,
3788 'OKC_API.G_RET_STS_ERROR',
3789 x_msg_count,
3790 x_msg_data,
3791 '_PVT'
3792 );
3793 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3794 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3795 (
3796 l_api_name,
3797 G_PKG_NAME,
3798 'OKC_API.G_RET_STS_UNEXP_ERROR',
3799 x_msg_count,
3800 x_msg_data,
3801 '_PVT'
3802 );
3803 WHEN OTHERS THEN
3804 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3805 (
3806 l_api_name,
3807 G_PKG_NAME,
3808 'OTHERS',
3809 x_msg_count,
3810 x_msg_data,
3811 '_PVT'
3812 );
3813 END insert_row;
3814
3815 ---------------------------------------------------------------------------
3816 -- PROCEDURE lock_row
3817 ---------------------------------------------------------------------------
3818 -----------------------------------
3819 -- lock_row for:OKL_TXL_ASSETS_B --
3820 -----------------------------------
3821 PROCEDURE lock_row(
3822 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3823 x_return_status OUT NOCOPY VARCHAR2,
3824 x_msg_count OUT NOCOPY NUMBER,
3825 x_msg_data OUT NOCOPY VARCHAR2,
3826 p_tal_rec IN tal_rec_type) IS
3827
3828 E_Resource_Busy EXCEPTION;
3829 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3830 CURSOR lock_csr (p_tal_rec IN tal_rec_type) IS
3831 SELECT OBJECT_VERSION_NUMBER
3832 FROM OKL_TXL_ASSETS_B
3833 WHERE ID = p_tal_rec.id
3834 AND OBJECT_VERSION_NUMBER = p_tal_rec.object_version_number
3835 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3836
3837 CURSOR lchk_csr (p_tal_rec IN tal_rec_type) IS
3838 SELECT OBJECT_VERSION_NUMBER
3839 FROM OKL_TXL_ASSETS_B
3840 WHERE ID = p_tal_rec.id;
3841 l_api_version CONSTANT NUMBER := 1;
3842 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3843 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3844 l_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
3845 lc_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
3846 l_row_notfound BOOLEAN := FALSE;
3847 lc_row_notfound BOOLEAN := FALSE;
3848 BEGIN
3849 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3850 p_init_msg_list,
3851 '_PVT',
3852 x_return_status);
3853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3856 RAISE OKC_API.G_EXCEPTION_ERROR;
3857 END IF;
3858 BEGIN
3859 OPEN lock_csr(p_tal_rec);
3860 FETCH lock_csr INTO l_object_version_number;
3861 l_row_notfound := lock_csr%NOTFOUND;
3862 CLOSE lock_csr;
3863 EXCEPTION
3864 WHEN E_Resource_Busy THEN
3865 IF (lock_csr%ISOPEN) THEN
3866 CLOSE lock_csr;
3867 END IF;
3868 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3869 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3870 END;
3871
3872 IF ( l_row_notfound ) THEN
3873 OPEN lchk_csr(p_tal_rec);
3874 FETCH lchk_csr INTO lc_object_version_number;
3875 lc_row_notfound := lchk_csr%NOTFOUND;
3876 CLOSE lchk_csr;
3877 END IF;
3878 IF (lc_row_notfound) THEN
3879 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3880 RAISE OKC_API.G_EXCEPTION_ERROR;
3881 ELSIF lc_object_version_number > p_tal_rec.object_version_number THEN
3882 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3883 RAISE OKC_API.G_EXCEPTION_ERROR;
3884 ELSIF lc_object_version_number <> p_tal_rec.object_version_number THEN
3885 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3886 RAISE OKC_API.G_EXCEPTION_ERROR;
3887 ELSIF lc_object_version_number = -1 THEN
3888 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3889 RAISE OKC_API.G_EXCEPTION_ERROR;
3890 END IF;
3891 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3892 EXCEPTION
3893 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3894 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3895 (
3896 l_api_name,
3897 G_PKG_NAME,
3898 'OKC_API.G_RET_STS_ERROR',
3899 x_msg_count,
3900 x_msg_data,
3901 '_PVT'
3902 );
3903 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3904 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3905 (
3906 l_api_name,
3907 G_PKG_NAME,
3908 'OKC_API.G_RET_STS_UNEXP_ERROR',
3909 x_msg_count,
3910 x_msg_data,
3911 '_PVT'
3912 );
3913 WHEN OTHERS THEN
3914 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3915 (
3916 l_api_name,
3917 G_PKG_NAME,
3918 'OTHERS',
3919 x_msg_count,
3920 x_msg_data,
3921 '_PVT'
3922 );
3923 END lock_row;
3924 ------------------------------------
3925 -- lock_row for:OKL_TXL_ASSETS_TL --
3926 ------------------------------------
3927 PROCEDURE lock_row(
3928 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3929 x_return_status OUT NOCOPY VARCHAR2,
3930 x_msg_count OUT NOCOPY NUMBER,
3931 x_msg_data OUT NOCOPY VARCHAR2,
3932 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
3933
3934 E_Resource_Busy EXCEPTION;
3935 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3936 CURSOR lock_csr (p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
3937 SELECT *
3938 FROM OKL_TXL_ASSETS_TL
3939 WHERE ID = p_okl_txl_assets_tl_rec.id
3940 FOR UPDATE NOWAIT;
3941
3942 l_api_version CONSTANT NUMBER := 1;
3943 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3944 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3945 l_lock_var lock_csr%ROWTYPE;
3946 l_row_notfound BOOLEAN := FALSE;
3947 lc_row_notfound BOOLEAN := FALSE;
3948 BEGIN
3949 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3950 p_init_msg_list,
3951 '_PVT',
3952 x_return_status);
3953 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3954 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3955 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3956 RAISE OKC_API.G_EXCEPTION_ERROR;
3957 END IF;
3958 BEGIN
3959 OPEN lock_csr(p_okl_txl_assets_tl_rec);
3960 FETCH lock_csr INTO l_lock_var;
3961 l_row_notfound := lock_csr%NOTFOUND;
3962 CLOSE lock_csr;
3963 EXCEPTION
3964 WHEN E_Resource_Busy THEN
3965 IF (lock_csr%ISOPEN) THEN
3966 CLOSE lock_csr;
3967 END IF;
3968 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3969 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3970 END;
3971
3972 IF ( l_row_notfound ) THEN
3973 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3974 RAISE OKC_API.G_EXCEPTION_ERROR;
3975 END IF;
3976 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3977 EXCEPTION
3978 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3979 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3980 (
3981 l_api_name,
3982 G_PKG_NAME,
3983 'OKC_API.G_RET_STS_ERROR',
3984 x_msg_count,
3985 x_msg_data,
3986 '_PVT'
3987 );
3988 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3989 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3990 (
3991 l_api_name,
3992 G_PKG_NAME,
3993 'OKC_API.G_RET_STS_UNEXP_ERROR',
3994 x_msg_count,
3995 x_msg_data,
3996 '_PVT'
3997 );
3998 WHEN OTHERS THEN
3999 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4000 (
4001 l_api_name,
4002 G_PKG_NAME,
4003 'OTHERS',
4004 x_msg_count,
4005 x_msg_data,
4006 '_PVT'
4007 );
4008 END lock_row;
4009 -----------------------------------
4010 -- lock_row for:OKL_TXL_ASSETS_V --
4011 -----------------------------------
4012 PROCEDURE lock_row(
4013 p_api_version IN NUMBER,
4014 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4015 x_return_status OUT NOCOPY VARCHAR2,
4016 x_msg_count OUT NOCOPY NUMBER,
4017 x_msg_data OUT NOCOPY VARCHAR2,
4018 p_talv_rec IN talv_rec_type) IS
4019
4020 l_api_version CONSTANT NUMBER := 1;
4021 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
4022 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4023 l_tal_rec tal_rec_type;
4024 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4025 BEGIN
4026 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4027 G_PKG_NAME,
4028 p_init_msg_list,
4029 l_api_version,
4030 p_api_version,
4031 '_PVT',
4032 x_return_status);
4033 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4034 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4035 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4036 RAISE OKC_API.G_EXCEPTION_ERROR;
4037 END IF;
4038 --------------------------------------
4039 -- Move VIEW record to "Child" records
4040 --------------------------------------
4041 migrate(p_talv_rec, l_tal_rec);
4042 migrate(p_talv_rec, l_okl_txl_assets_tl_rec);
4043 --------------------------------------------
4044 -- Call the LOCK_ROW for each child record
4045 --------------------------------------------
4046 lock_row(
4047 p_init_msg_list,
4048 x_return_status,
4049 x_msg_count,
4050 x_msg_data,
4051 l_tal_rec
4052 );
4053 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4055 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4056 RAISE OKC_API.G_EXCEPTION_ERROR;
4057 END IF;
4058 lock_row(
4059 p_init_msg_list,
4060 x_return_status,
4061 x_msg_count,
4062 x_msg_data,
4063 l_okl_txl_assets_tl_rec
4064 );
4065 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4066 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4067 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4068 RAISE OKC_API.G_EXCEPTION_ERROR;
4069 END IF;
4070 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4071 EXCEPTION
4072 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4073 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4074 (
4075 l_api_name,
4076 G_PKG_NAME,
4077 'OKC_API.G_RET_STS_ERROR',
4078 x_msg_count,
4079 x_msg_data,
4080 '_PVT'
4081 );
4082 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4083 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4084 (
4085 l_api_name,
4086 G_PKG_NAME,
4087 'OKC_API.G_RET_STS_UNEXP_ERROR',
4088 x_msg_count,
4089 x_msg_data,
4090 '_PVT'
4091 );
4092 WHEN OTHERS THEN
4093 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4094 (
4095 l_api_name,
4096 G_PKG_NAME,
4097 'OTHERS',
4098 x_msg_count,
4099 x_msg_data,
4100 '_PVT'
4101 );
4102 END lock_row;
4103 --------------------------------------
4104 -- PL/SQL TBL lock_row for:TALV_TBL --
4105 --------------------------------------
4106 PROCEDURE lock_row(
4107 p_api_version IN NUMBER,
4108 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4109 x_return_status OUT NOCOPY VARCHAR2,
4110 x_msg_count OUT NOCOPY NUMBER,
4111 x_msg_data OUT NOCOPY VARCHAR2,
4112 p_talv_tbl IN talv_tbl_type) IS
4113
4114 l_api_version CONSTANT NUMBER := 1;
4115 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
4116 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4117 i NUMBER := 0;
4118 BEGIN
4119 OKC_API.init_msg_list(p_init_msg_list);
4120 -- Make sure PL/SQL table has records in it before passing
4121 IF (p_talv_tbl.COUNT > 0) THEN
4122 i := p_talv_tbl.FIRST;
4123 LOOP
4124 lock_row (
4125 p_api_version => p_api_version,
4126 p_init_msg_list => OKC_API.G_FALSE,
4127 x_return_status => x_return_status,
4128 x_msg_count => x_msg_count,
4129 x_msg_data => x_msg_data,
4130 p_talv_rec => p_talv_tbl(i));
4131 EXIT WHEN (i = p_talv_tbl.LAST);
4132 i := p_talv_tbl.NEXT(i);
4133 END LOOP;
4134 END IF;
4135 EXCEPTION
4136 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4137 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4138 (
4139 l_api_name,
4140 G_PKG_NAME,
4141 'OKC_API.G_RET_STS_ERROR',
4142 x_msg_count,
4143 x_msg_data,
4144 '_PVT'
4145 );
4146 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4147 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4148 (
4149 l_api_name,
4150 G_PKG_NAME,
4151 'OKC_API.G_RET_STS_UNEXP_ERROR',
4152 x_msg_count,
4153 x_msg_data,
4154 '_PVT'
4155 );
4156 WHEN OTHERS THEN
4157 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4158 (
4159 l_api_name,
4160 G_PKG_NAME,
4161 'OTHERS',
4162 x_msg_count,
4163 x_msg_data,
4164 '_PVT'
4165 );
4166 END lock_row;
4167
4168 ---------------------------------------------------------------------------
4169 -- PROCEDURE update_row
4170 ---------------------------------------------------------------------------
4171 -------------------------------------
4172 -- update_row for:OKL_TXL_ASSETS_B --
4173 -------------------------------------
4174 PROCEDURE update_row(
4175 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4176 x_return_status OUT NOCOPY VARCHAR2,
4177 x_msg_count OUT NOCOPY NUMBER,
4178 x_msg_data OUT NOCOPY VARCHAR2,
4179 p_tal_rec IN tal_rec_type,
4180 x_tal_rec OUT NOCOPY tal_rec_type) IS
4181
4182 l_api_version CONSTANT NUMBER := 1;
4183 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
4184 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4185 l_tal_rec tal_rec_type := p_tal_rec;
4186 l_def_tal_rec tal_rec_type;
4187 l_row_notfound BOOLEAN := TRUE;
4188 ----------------------------------
4189 -- FUNCTION populate_new_record --
4190 ----------------------------------
4191 FUNCTION populate_new_record (
4192 p_tal_rec IN tal_rec_type,
4193 x_tal_rec OUT NOCOPY tal_rec_type
4194 ) RETURN VARCHAR2 IS
4195 l_tal_rec tal_rec_type;
4196 l_row_notfound BOOLEAN := TRUE;
4197 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4198 BEGIN
4199 x_tal_rec := p_tal_rec;
4200 -- Get current database values
4201 l_tal_rec := get_rec(p_tal_rec, l_row_notfound);
4202 IF (l_row_notfound) THEN
4203 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4204 END IF;
4205 IF (x_tal_rec.id = OKC_API.G_MISS_NUM)
4206 THEN
4207 x_tal_rec.id := l_tal_rec.id;
4208 END IF;
4209 IF (x_tal_rec.object_version_number = OKC_API.G_MISS_NUM)
4210 THEN
4211 x_tal_rec.object_version_number := l_tal_rec.object_version_number;
4212 END IF;
4213 IF (x_tal_rec.tas_id = OKC_API.G_MISS_NUM)
4214 THEN
4215 x_tal_rec.tas_id := l_tal_rec.tas_id;
4216 END IF;
4217 IF (x_tal_rec.ilo_id = OKC_API.G_MISS_NUM)
4218 THEN
4219 x_tal_rec.ilo_id := l_tal_rec.ilo_id;
4220 END IF;
4221 IF (x_tal_rec.ilo_id_old = OKC_API.G_MISS_NUM)
4222 THEN
4223 x_tal_rec.ilo_id_old := l_tal_rec.ilo_id_old;
4224 END IF;
4225 IF (x_tal_rec.iay_id = OKC_API.G_MISS_NUM)
4226 THEN
4227 x_tal_rec.iay_id := l_tal_rec.iay_id;
4228 END IF;
4229 IF (x_tal_rec.iay_id_new = OKC_API.G_MISS_NUM)
4230 THEN
4231 x_tal_rec.iay_id_new := l_tal_rec.iay_id_new;
4232 END IF;
4233 IF (x_tal_rec.kle_id = OKC_API.G_MISS_NUM)
4234 THEN
4235 x_tal_rec.kle_id := l_tal_rec.kle_id;
4236 END IF;
4237 IF (x_tal_rec.dnz_khr_id = OKC_API.G_MISS_NUM)
4238 THEN
4239 x_tal_rec.dnz_khr_id := l_tal_rec.dnz_khr_id;
4240 END IF;
4241 IF (x_tal_rec.line_number = OKC_API.G_MISS_NUM)
4242 THEN
4243 x_tal_rec.line_number := l_tal_rec.line_number;
4244 END IF;
4245 IF (x_tal_rec.org_id = OKC_API.G_MISS_NUM)
4246 THEN
4247 x_tal_rec.org_id := l_tal_rec.org_id;
4248 END IF;
4249 IF (x_tal_rec.tal_type = OKC_API.G_MISS_CHAR)
4250 THEN
4251 x_tal_rec.tal_type := l_tal_rec.tal_type;
4252 END IF;
4253 IF (x_tal_rec.asset_number = OKC_API.G_MISS_CHAR)
4254 THEN
4255 x_tal_rec.asset_number := l_tal_rec.asset_number;
4256 END IF;
4257 IF (x_tal_rec.original_cost = OKC_API.G_MISS_NUM)
4258 THEN
4259 x_tal_rec.original_cost := l_tal_rec.original_cost;
4260 END IF;
4261 IF (x_tal_rec.current_units = OKC_API.G_MISS_NUM)
4262 THEN
4263 x_tal_rec.current_units := l_tal_rec.current_units;
4264 END IF;
4265 IF (x_tal_rec.manufacturer_name = OKC_API.G_MISS_CHAR)
4266 THEN
4267 x_tal_rec.manufacturer_name := l_tal_rec.manufacturer_name;
4268 END IF;
4269 IF (x_tal_rec.year_manufactured = OKC_API.G_MISS_NUM)
4270 THEN
4271 x_tal_rec.year_manufactured := l_tal_rec.year_manufactured;
4272 END IF;
4273 IF (x_tal_rec.supplier_id = OKC_API.G_MISS_NUM)
4274 THEN
4275 x_tal_rec.supplier_id := l_tal_rec.supplier_id;
4276 END IF;
4277 IF (x_tal_rec.used_asset_yn = OKC_API.G_MISS_CHAR)
4278 THEN
4279 x_tal_rec.used_asset_yn := l_tal_rec.used_asset_yn;
4280 END IF;
4281 IF (x_tal_rec.tag_number = OKC_API.G_MISS_CHAR)
4282 THEN
4283 x_tal_rec.tag_number := l_tal_rec.tag_number;
4284 END IF;
4285 IF (x_tal_rec.model_number = OKC_API.G_MISS_CHAR)
4286 THEN
4287 x_tal_rec.model_number := l_tal_rec.model_number;
4288 END IF;
4289 IF (x_tal_rec.corporate_book = OKC_API.G_MISS_CHAR)
4290 THEN
4291 x_tal_rec.corporate_book := l_tal_rec.corporate_book;
4292 END IF;
4293 IF (x_tal_rec.date_purchased = OKC_API.G_MISS_DATE)
4294 THEN
4295 x_tal_rec.date_purchased := l_tal_rec.date_purchased;
4296 END IF;
4297 IF (x_tal_rec.date_purchased = OKC_API.G_MISS_DATE)
4298 THEN
4299 x_tal_rec.date_delivery := l_tal_rec.date_delivery;
4300 END IF;
4301 IF (x_tal_rec.in_service_date = OKC_API.G_MISS_DATE)
4302 THEN
4303 x_tal_rec.in_service_date := l_tal_rec.in_service_date;
4304 END IF;
4305 IF (x_tal_rec.life_in_months = OKC_API.G_MISS_NUM)
4306 THEN
4307 x_tal_rec.life_in_months := l_tal_rec.life_in_months;
4308 END IF;
4309 IF (x_tal_rec.depreciation_id = OKC_API.G_MISS_NUM)
4310 THEN
4311 x_tal_rec.depreciation_id := l_tal_rec.depreciation_id;
4312 END IF;
4313 IF (x_tal_rec.depreciation_cost = OKC_API.G_MISS_NUM)
4314 THEN
4315 x_tal_rec.depreciation_cost := l_tal_rec.depreciation_cost;
4316 END IF;
4317 IF (x_tal_rec.depreciation_id = OKC_API.G_MISS_NUM)
4318 THEN
4319 x_tal_rec.deprn_method := l_tal_rec.deprn_method;
4320 END IF;
4321 IF (x_tal_rec.deprn_rate = OKC_API.G_MISS_NUM)
4322 THEN
4323 x_tal_rec.deprn_rate := l_tal_rec.deprn_rate;
4324 END IF;
4325 IF (x_tal_rec.salvage_value = OKC_API.G_MISS_NUM)
4326 THEN
4327 x_tal_rec.salvage_value := l_tal_rec.salvage_value;
4328 END IF;
4329 IF (x_tal_rec.percent_salvage_value = OKC_API.G_MISS_NUM)
4330 THEN
4331 x_tal_rec.percent_salvage_value := l_tal_rec.percent_salvage_value;
4332 END IF;
4333 --Bug# 2981308
4334 IF (x_tal_rec.asset_key_id = OKL_API.G_MISS_NUM)
4335 THEN
4336 x_tal_rec.asset_key_id := l_tal_rec.asset_key_id;
4337 END IF;
4338 -- Bug# 4028371
4339 IF (x_tal_rec.fa_trx_date = OKC_API.G_MISS_DATE)
4340 THEN
4341 x_tal_rec.fa_trx_date := l_tal_rec.fa_trx_date;
4342 END IF;
4343 --Bug# 4899328
4344 IF (x_tal_rec.fa_cost = OKL_API.G_MISS_NUM)
4345 THEN
4346 x_tal_rec.fa_cost := l_tal_rec.fa_cost;
4347 END IF;
4348 IF (x_tal_rec.attribute_category = OKC_API.G_MISS_CHAR)
4349 THEN
4350 x_tal_rec.attribute_category := l_tal_rec.attribute_category;
4351 END IF;
4352 IF (x_tal_rec.attribute1 = OKC_API.G_MISS_CHAR)
4353 THEN
4354 x_tal_rec.attribute1 := l_tal_rec.attribute1;
4355 END IF;
4356 IF (x_tal_rec.attribute2 = OKC_API.G_MISS_CHAR)
4357 THEN
4358 x_tal_rec.attribute2 := l_tal_rec.attribute2;
4359 END IF;
4360 IF (x_tal_rec.attribute3 = OKC_API.G_MISS_CHAR)
4361 THEN
4362 x_tal_rec.attribute3 := l_tal_rec.attribute3;
4363 END IF;
4364 IF (x_tal_rec.attribute4 = OKC_API.G_MISS_CHAR)
4365 THEN
4366 x_tal_rec.attribute4 := l_tal_rec.attribute4;
4367 END IF;
4368 IF (x_tal_rec.attribute5 = OKC_API.G_MISS_CHAR)
4369 THEN
4370 x_tal_rec.attribute5 := l_tal_rec.attribute5;
4371 END IF;
4372 IF (x_tal_rec.attribute6 = OKC_API.G_MISS_CHAR)
4373 THEN
4374 x_tal_rec.attribute6 := l_tal_rec.attribute6;
4375 END IF;
4376 IF (x_tal_rec.attribute7 = OKC_API.G_MISS_CHAR)
4377 THEN
4378 x_tal_rec.attribute7 := l_tal_rec.attribute7;
4379 END IF;
4380 IF (x_tal_rec.attribute8 = OKC_API.G_MISS_CHAR)
4381 THEN
4382 x_tal_rec.attribute8 := l_tal_rec.attribute8;
4383 END IF;
4384 IF (x_tal_rec.attribute9 = OKC_API.G_MISS_CHAR)
4385 THEN
4386 x_tal_rec.attribute9 := l_tal_rec.attribute9;
4387 END IF;
4388 IF (x_tal_rec.attribute10 = OKC_API.G_MISS_CHAR)
4389 THEN
4390 x_tal_rec.attribute10 := l_tal_rec.attribute10;
4391 END IF;
4392 IF (x_tal_rec.attribute11 = OKC_API.G_MISS_CHAR)
4393 THEN
4394 x_tal_rec.attribute11 := l_tal_rec.attribute11;
4395 END IF;
4396 IF (x_tal_rec.attribute12 = OKC_API.G_MISS_CHAR)
4397 THEN
4398 x_tal_rec.attribute12 := l_tal_rec.attribute12;
4399 END IF;
4400 IF (x_tal_rec.attribute13 = OKC_API.G_MISS_CHAR)
4401 THEN
4402 x_tal_rec.attribute13 := l_tal_rec.attribute13;
4403 END IF;
4404 IF (x_tal_rec.attribute14 = OKC_API.G_MISS_CHAR)
4405 THEN
4406 x_tal_rec.attribute14 := l_tal_rec.attribute14;
4407 END IF;
4408 IF (x_tal_rec.attribute15 = OKC_API.G_MISS_CHAR)
4409 THEN
4410 x_tal_rec.attribute15 := l_tal_rec.attribute15;
4411 END IF;
4412 IF (x_tal_rec.created_by = OKC_API.G_MISS_NUM)
4413 THEN
4414 x_tal_rec.created_by := l_tal_rec.created_by;
4415 END IF;
4416 IF (x_tal_rec.creation_date = OKC_API.G_MISS_DATE)
4417 THEN
4418 x_tal_rec.creation_date := l_tal_rec.creation_date;
4419 END IF;
4420 IF (x_tal_rec.last_updated_by = OKC_API.G_MISS_NUM)
4421 THEN
4422 x_tal_rec.last_updated_by := l_tal_rec.last_updated_by;
4423 END IF;
4424 IF (x_tal_rec.last_update_date = OKC_API.G_MISS_DATE)
4425 THEN
4426 x_tal_rec.last_update_date := l_tal_rec.last_update_date;
4427 END IF;
4428 IF (x_tal_rec.last_update_login = OKC_API.G_MISS_NUM)
4429 THEN
4430 x_tal_rec.last_update_login := l_tal_rec.last_update_login;
4431 END IF;
4432 IF (x_tal_rec.depreciate_yn = OKC_API.G_MISS_CHAR)
4433 THEN
4434 x_tal_rec.depreciate_yn := l_tal_rec.depreciate_yn;
4435 END IF;
4436 IF (x_tal_rec.hold_period_days = OKC_API.G_MISS_NUM)
4437 THEN
4438 x_tal_rec.hold_period_days := l_tal_rec.hold_period_days;
4439 END IF;
4440 IF (x_tal_rec.old_salvage_value = OKC_API.G_MISS_NUM)
4441 THEN
4442 x_tal_rec.old_salvage_value := l_tal_rec.old_salvage_value;
4443 END IF;
4444 IF (x_tal_rec.new_residual_value = OKC_API.G_MISS_NUM)
4445 THEN
4446 x_tal_rec.new_residual_value := l_tal_rec.new_residual_value;
4447 END IF;
4448 IF (x_tal_rec.old_residual_value = OKC_API.G_MISS_NUM)
4449 THEN
4450 x_tal_rec.old_residual_value := l_tal_rec.old_residual_value;
4451 END IF;
4452 IF (x_tal_rec.units_retired = OKC_API.G_MISS_NUM)
4453 THEN
4454 x_tal_rec.units_retired := l_tal_rec.units_retired;
4455 END IF;
4456 IF (x_tal_rec.cost_retired = OKC_API.G_MISS_NUM)
4457 THEN
4458 x_tal_rec.cost_retired := l_tal_rec.cost_retired;
4459 END IF;
4460 IF (x_tal_rec.sale_proceeds = OKC_API.G_MISS_NUM)
4461 THEN
4462 x_tal_rec.sale_proceeds := l_tal_rec.sale_proceeds;
4463 END IF;
4464 IF (x_tal_rec.removal_cost = OKC_API.G_MISS_NUM)
4465 THEN
4466 x_tal_rec.removal_cost := l_tal_rec.removal_cost;
4467 END IF;
4468 IF (x_tal_rec.dnz_asset_id = OKC_API.G_MISS_NUM)
4469 THEN
4470 x_tal_rec.dnz_asset_id := l_tal_rec.dnz_asset_id;
4471 END IF;
4472 IF (x_tal_rec.date_due = OKC_API.G_MISS_DATE)
4473 THEN
4474 x_tal_rec.date_due := l_tal_rec.date_due;
4475 END IF;
4476 IF (x_tal_rec.rep_asset_id = OKC_API.G_MISS_NUM)
4477 THEN
4478 x_tal_rec.rep_asset_id := l_tal_rec.rep_asset_id;
4479 END IF;
4480 IF (x_tal_rec.lke_asset_id = OKC_API.G_MISS_NUM)
4481 THEN
4482 x_tal_rec.lke_asset_id := l_tal_rec.lke_asset_id;
4483 END IF;
4484 IF (x_tal_rec.match_amount = OKC_API.G_MISS_NUM)
4485 THEN
4486 x_tal_rec.match_amount := l_tal_rec.match_amount;
4487 END IF;
4488 IF (x_tal_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR)
4489 THEN
4490 x_tal_rec.split_into_singles_flag := l_tal_rec.split_into_singles_flag;
4491 END IF;
4492 IF (x_tal_rec.split_into_units = OKC_API.G_MISS_NUM)
4493 THEN
4494 x_tal_rec.split_into_units := l_tal_rec.split_into_units;
4495 END IF;
4496 -- Multi Currency Change
4497 IF (x_tal_rec.currency_code = OKC_API.G_MISS_CHAR)
4498 THEN
4499 x_tal_rec.currency_code := l_tal_rec.currency_code;
4500 END IF;
4501 IF (x_tal_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
4502 THEN
4503 x_tal_rec.currency_conversion_type := l_tal_rec.currency_conversion_type;
4504 END IF;
4505 IF (x_tal_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
4506 THEN
4507 x_tal_rec.currency_conversion_rate := l_tal_rec.currency_conversion_rate;
4508 END IF;
4509 IF (x_tal_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
4510 THEN
4511 x_tal_rec.currency_conversion_date := l_tal_rec.currency_conversion_date;
4512 END IF;
4513 -- Multi Currency Change
4514
4515 -- VRS Project - START
4516
4517 IF (x_tal_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM) THEN
4518 x_tal_rec.RESIDUAL_SHR_PARTY_ID:= l_tal_rec.RESIDUAL_SHR_PARTY_ID;
4519 END IF;
4520
4521 IF (x_tal_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM) THEN
4522 x_tal_rec.RESIDUAL_SHR_AMOUNT := l_tal_rec.RESIDUAL_SHR_AMOUNT;
4523 END IF;
4524 IF (x_tal_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM) THEN
4525 x_tal_rec.RETIREMENT_ID := l_tal_rec.RETIREMENT_ID;
4526 END IF;
4527
4528 -- VRS Project - END
4529
4530
4531 RETURN(l_return_status);
4532 END populate_new_record;
4533 -----------------------------------------
4534 -- Set_Attributes for:OKL_TXL_ASSETS_B --
4535 -----------------------------------------
4536 FUNCTION Set_Attributes (
4537 p_tal_rec IN tal_rec_type,
4538 x_tal_rec OUT NOCOPY tal_rec_type
4539 ) RETURN VARCHAR2 IS
4540 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4541 BEGIN
4542 x_tal_rec := p_tal_rec;
4543 RETURN(l_return_status);
4544 END Set_Attributes;
4545 BEGIN
4546 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4547 p_init_msg_list,
4548 '_PVT',
4549 x_return_status);
4550 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4551 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4552 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4553 RAISE OKC_API.G_EXCEPTION_ERROR;
4554 END IF;
4555 --- Setting item attributes
4556 l_return_status := Set_Attributes(
4557 p_tal_rec, -- IN
4558 l_tal_rec); -- OUT
4559 --- If any errors happen abort API
4560 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4561 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4562 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4563 RAISE OKC_API.G_EXCEPTION_ERROR;
4564 END IF;
4565 l_return_status := populate_new_record(l_tal_rec, l_def_tal_rec);
4566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4567 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4568 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4569 RAISE OKC_API.G_EXCEPTION_ERROR;
4570 END IF;
4571 UPDATE OKL_TXL_ASSETS_B
4572 SET TAS_ID = l_def_tal_rec.tas_id,
4573 OBJECT_VERSION_NUMBER = l_def_tal_rec.object_version_number,
4574 ILO_ID = l_def_tal_rec.ilo_id,
4575 ILO_ID_OLD = l_def_tal_rec.ilo_id_old,
4576 IAY_ID = l_def_tal_rec.iay_id,
4577 IAY_ID_NEW = l_def_tal_rec.iay_id_new,
4578 KLE_ID = l_def_tal_rec.kle_id,
4579 DNZ_KHR_ID = l_def_tal_rec.dnz_khr_id,
4580 LINE_NUMBER = l_def_tal_rec.line_number,
4581 ORG_ID = l_def_tal_rec.org_id,
4582 TAL_TYPE = l_def_tal_rec.tal_type,
4583 ASSET_NUMBER = l_def_tal_rec.asset_number,
4584 FA_LOCATION_ID = l_def_tal_rec.fa_location_id,
4585 ORIGINAL_COST = l_def_tal_rec.original_cost,
4586 CURRENT_UNITS = l_def_tal_rec.current_units,
4587 MANUFACTURER_NAME = l_def_tal_rec.manufacturer_name,
4588 YEAR_MANUFACTURED = l_def_tal_rec.year_manufactured,
4589 SUPPLIER_ID = l_def_tal_rec.supplier_id,
4590 USED_ASSET_YN = l_def_tal_rec.used_asset_yn,
4591 TAG_NUMBER = l_def_tal_rec.tag_number,
4592 MODEL_NUMBER = l_def_tal_rec.model_number,
4593 CORPORATE_BOOK = l_def_tal_rec.corporate_book,
4594 DATE_PURCHASED = l_def_tal_rec.date_purchased,
4595 DATE_DELIVERY = l_def_tal_rec.date_delivery,
4596 IN_SERVICE_DATE = l_def_tal_rec.in_service_date,
4597 LIFE_IN_MONTHS = l_def_tal_rec.life_in_months,
4598 DEPRECIATION_ID = l_def_tal_rec.depreciation_id,
4599 DEPRECIATION_COST = l_def_tal_rec.depreciation_cost,
4600 DEPRN_METHOD = l_def_tal_rec.deprn_method,
4601 DEPRN_RATE = l_def_tal_rec.deprn_rate,
4602 SALVAGE_VALUE = l_def_tal_rec.salvage_value,
4603 PERCENT_SALVAGE_VALUE = l_def_tal_rec.percent_salvage_value,
4604 --Bug# 2981308
4605 ASSET_KEY_ID = l_def_tal_rec.asset_key_id,
4606 -- Bug# 4028371
4607 FA_TRX_DATE = l_def_tal_rec.fa_trx_date,
4608 --Bug# 4899328
4609 FA_COST = l_def_tal_rec.fa_cost,
4610 ATTRIBUTE_CATEGORY = l_def_tal_rec.attribute_category,
4611 ATTRIBUTE1 = l_def_tal_rec.attribute1,
4612 ATTRIBUTE2 = l_def_tal_rec.attribute2,
4613 ATTRIBUTE3 = l_def_tal_rec.attribute3,
4614 ATTRIBUTE4 = l_def_tal_rec.attribute4,
4615 ATTRIBUTE5 = l_def_tal_rec.attribute5,
4616 ATTRIBUTE6 = l_def_tal_rec.attribute6,
4617 ATTRIBUTE7 = l_def_tal_rec.attribute7,
4618 ATTRIBUTE8 = l_def_tal_rec.attribute8,
4619 ATTRIBUTE9 = l_def_tal_rec.attribute9,
4620 ATTRIBUTE10 = l_def_tal_rec.attribute10,
4621 ATTRIBUTE11 = l_def_tal_rec.attribute11,
4622 ATTRIBUTE12 = l_def_tal_rec.attribute12,
4623 ATTRIBUTE13 = l_def_tal_rec.attribute13,
4624 ATTRIBUTE14 = l_def_tal_rec.attribute14,
4625 ATTRIBUTE15 = l_def_tal_rec.attribute15,
4626 CREATED_BY = l_def_tal_rec.created_by,
4627 CREATION_DATE = l_def_tal_rec.creation_date,
4628 LAST_UPDATED_BY = l_def_tal_rec.last_updated_by,
4629 LAST_UPDATE_DATE = l_def_tal_rec.last_update_date,
4630 LAST_UPDATE_LOGIN = l_def_tal_rec.last_update_login,
4631 DEPRECIATE_YN = l_def_tal_rec.depreciate_yn,
4632 HOLD_PERIOD_DAYS = l_def_tal_rec.hold_period_days,
4633 OLD_SALVAGE_VALUE = l_def_tal_rec.old_salvage_value,
4634 NEW_RESIDUAL_VALUE = l_def_tal_rec.new_residual_value,
4635 OLD_RESIDUAL_VALUE = l_def_tal_rec.old_residual_value,
4636 UNITS_RETIRED = l_def_tal_rec.units_retired,
4637 COST_RETIRED = l_def_tal_rec.cost_retired,
4638 SALE_PROCEEDS = l_def_tal_rec.sale_proceeds,
4639 REMOVAL_COST = l_def_tal_rec.removal_cost,
4640 DNZ_ASSET_ID = l_def_tal_rec.dnz_asset_id,
4641 DATE_DUE = l_def_tal_rec.date_due,
4642 REP_ASSET_ID = l_def_tal_rec.rep_asset_id,
4643 LKE_ASSET_ID = l_def_tal_rec.lke_asset_id,
4644 MATCH_AMOUNT = l_def_tal_rec.match_amount,
4645 SPLIT_INTO_SINGLES_FLAG = l_def_tal_rec.split_into_singles_flag,
4646 SPLIT_INTO_UNITS = l_def_tal_rec.split_into_units,
4647 CURRENCY_CODE = l_def_tal_rec.currency_code,
4648 CURRENCY_CONVERSION_TYPE = l_def_tal_rec.currency_conversion_type,
4649 CURRENCY_CONVERSION_RATE = l_def_tal_rec.currency_conversion_rate,
4650 CURRENCY_CONVERSION_DATE = l_def_tal_rec.currency_conversion_date,
4651
4652 -- VRS Project - START
4653 RESIDUAL_SHR_PARTY_ID =l_def_tal_rec.RESIDUAL_SHR_PARTY_ID,
4654 RESIDUAL_SHR_AMOUNT =l_def_tal_rec.RESIDUAL_SHR_AMOUNT,
4655 RETIREMENT_ID = l_def_tal_rec.RETIREMENT_ID
4656 -- VRS Project - END
4657
4658 WHERE ID = l_def_tal_rec.id;
4659
4660 x_tal_rec := l_def_tal_rec;
4661 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4662 EXCEPTION
4663 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4664 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4665 (
4666 l_api_name,
4667 G_PKG_NAME,
4668 'OKC_API.G_RET_STS_ERROR',
4669 x_msg_count,
4670 x_msg_data,
4671 '_PVT'
4672 );
4673 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4675 (
4676 l_api_name,
4677 G_PKG_NAME,
4678 'OKC_API.G_RET_STS_UNEXP_ERROR',
4679 x_msg_count,
4680 x_msg_data,
4681 '_PVT'
4682 );
4683 WHEN OTHERS THEN
4684 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4685 (
4686 l_api_name,
4687 G_PKG_NAME,
4688 'OTHERS',
4689 x_msg_count,
4690 x_msg_data,
4691 '_PVT'
4692 );
4693 END update_row;
4694 --------------------------------------
4695 -- update_row for:OKL_TXL_ASSETS_TL --
4696 --------------------------------------
4697 PROCEDURE update_row(
4698 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4699 x_return_status OUT NOCOPY VARCHAR2,
4700 x_msg_count OUT NOCOPY NUMBER,
4701 x_msg_data OUT NOCOPY VARCHAR2,
4702 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4703 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type) IS
4704
4705 l_api_version CONSTANT NUMBER := 1;
4706 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4707 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4708 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type := p_okl_txl_assets_tl_rec;
4709 l_def_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4710 l_row_notfound BOOLEAN := TRUE;
4711 ----------------------------------
4712 -- FUNCTION populate_new_record --
4713 ----------------------------------
4714 FUNCTION populate_new_record (
4715 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4716 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
4717 ) RETURN VARCHAR2 IS
4718 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4719 l_row_notfound BOOLEAN := TRUE;
4720 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4721 BEGIN
4722 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
4723 -- Get current database values
4724 l_okl_txl_assets_tl_rec := get_rec(p_okl_txl_assets_tl_rec, l_row_notfound);
4725 IF (l_row_notfound) THEN
4726 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4727 END IF;
4728 IF (x_okl_txl_assets_tl_rec.id = OKC_API.G_MISS_NUM)
4729 THEN
4730 x_okl_txl_assets_tl_rec.id := l_okl_txl_assets_tl_rec.id;
4731 END IF;
4732 IF (x_okl_txl_assets_tl_rec.language = OKC_API.G_MISS_CHAR)
4733 THEN
4734 x_okl_txl_assets_tl_rec.language := l_okl_txl_assets_tl_rec.language;
4735 END IF;
4736 IF (x_okl_txl_assets_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
4737 THEN
4738 x_okl_txl_assets_tl_rec.source_lang := l_okl_txl_assets_tl_rec.source_lang;
4739 END IF;
4740 IF (x_okl_txl_assets_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4741 THEN
4742 x_okl_txl_assets_tl_rec.sfwt_flag := l_okl_txl_assets_tl_rec.sfwt_flag;
4743 END IF;
4744 IF (x_okl_txl_assets_tl_rec.description = OKC_API.G_MISS_CHAR)
4745 THEN
4746 x_okl_txl_assets_tl_rec.description := l_okl_txl_assets_tl_rec.description;
4747 END IF;
4748 IF (x_okl_txl_assets_tl_rec.created_by = OKC_API.G_MISS_NUM)
4749 THEN
4750 x_okl_txl_assets_tl_rec.created_by := l_okl_txl_assets_tl_rec.created_by;
4751 END IF;
4752 IF (x_okl_txl_assets_tl_rec.creation_date = OKC_API.G_MISS_DATE)
4753 THEN
4754 x_okl_txl_assets_tl_rec.creation_date := l_okl_txl_assets_tl_rec.creation_date;
4755 END IF;
4756 IF (x_okl_txl_assets_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
4757 THEN
4758 x_okl_txl_assets_tl_rec.last_updated_by := l_okl_txl_assets_tl_rec.last_updated_by;
4759 END IF;
4760 IF (x_okl_txl_assets_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
4761 THEN
4762 x_okl_txl_assets_tl_rec.last_update_date := l_okl_txl_assets_tl_rec.last_update_date;
4763 END IF;
4764 IF (x_okl_txl_assets_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
4765 THEN
4766 x_okl_txl_assets_tl_rec.last_update_login := l_okl_txl_assets_tl_rec.last_update_login;
4767 END IF;
4768 RETURN(l_return_status);
4769 END populate_new_record;
4770 ------------------------------------------
4771 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
4772 ------------------------------------------
4773 FUNCTION Set_Attributes (
4774 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4775 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
4776 ) RETURN VARCHAR2 IS
4777 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4778 BEGIN
4779 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
4780 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
4781 x_okl_txl_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
4782 RETURN(l_return_status);
4783 END Set_Attributes;
4784 BEGIN
4785 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4786 p_init_msg_list,
4787 '_PVT',
4788 x_return_status);
4789 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4791 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4792 RAISE OKC_API.G_EXCEPTION_ERROR;
4793 END IF;
4794 --- Setting item attributes
4795 l_return_status := Set_Attributes(
4796 p_okl_txl_assets_tl_rec, -- IN
4797 l_okl_txl_assets_tl_rec); -- OUT
4798 --- If any errors happen abort API
4799 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4800 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4801 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4802 RAISE OKC_API.G_EXCEPTION_ERROR;
4803 END IF;
4804 l_return_status := populate_new_record(l_okl_txl_assets_tl_rec, l_def_okl_txl_assets_tl_rec);
4805 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4806 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4807 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4808 RAISE OKC_API.G_EXCEPTION_ERROR;
4809 END IF;
4810 UPDATE OKL_TXL_ASSETS_TL
4811 SET DESCRIPTION = l_def_okl_txl_assets_tl_rec.description,
4812 --Bug# 3641933 :
4813 SOURCE_LANG = l_def_okl_txl_assets_tl_rec.source_lang,
4814 CREATED_BY = l_def_okl_txl_assets_tl_rec.created_by,
4815 CREATION_DATE = l_def_okl_txl_assets_tl_rec.creation_date,
4816 LAST_UPDATED_BY = l_def_okl_txl_assets_tl_rec.last_updated_by,
4817 LAST_UPDATE_DATE = l_def_okl_txl_assets_tl_rec.last_update_date,
4818 LAST_UPDATE_LOGIN = l_def_okl_txl_assets_tl_rec.last_update_login
4819 WHERE ID = l_def_okl_txl_assets_tl_rec.id
4820 --Bug# 3641933 :
4821 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
4822 --AND SOURCE_LANG = USERENV('LANG');
4823
4824 UPDATE OKL_TXL_ASSETS_TL
4825 SET SFWT_FLAG = 'Y'
4826 WHERE ID = l_def_okl_txl_assets_tl_rec.id
4827 AND SOURCE_LANG <> USERENV('LANG');
4828
4829 x_okl_txl_assets_tl_rec := l_def_okl_txl_assets_tl_rec;
4830 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4831 EXCEPTION
4832 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4833 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4834 (
4835 l_api_name,
4836 G_PKG_NAME,
4837 'OKC_API.G_RET_STS_ERROR',
4838 x_msg_count,
4839 x_msg_data,
4840 '_PVT'
4841 );
4842 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4843 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4844 (
4845 l_api_name,
4846 G_PKG_NAME,
4847 'OKC_API.G_RET_STS_UNEXP_ERROR',
4848 x_msg_count,
4849 x_msg_data,
4850 '_PVT'
4851 );
4852 WHEN OTHERS THEN
4853 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4854 (
4855 l_api_name,
4856 G_PKG_NAME,
4857 'OTHERS',
4858 x_msg_count,
4859 x_msg_data,
4860 '_PVT'
4861 );
4862 END update_row;
4863 -------------------------------------
4864 -- update_row for:OKL_TXL_ASSETS_V --
4865 -------------------------------------
4866 PROCEDURE update_row(
4867 p_api_version IN NUMBER,
4868 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4869 x_return_status OUT NOCOPY VARCHAR2,
4870 x_msg_count OUT NOCOPY NUMBER,
4871 x_msg_data OUT NOCOPY VARCHAR2,
4872 p_talv_rec IN talv_rec_type,
4873 x_talv_rec OUT NOCOPY talv_rec_type) IS
4874
4875 l_api_version CONSTANT NUMBER := 1;
4876 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4877 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4878 l_talv_rec talv_rec_type := p_talv_rec;
4879 l_def_talv_rec talv_rec_type;
4880 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4881 lx_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4882 l_tal_rec tal_rec_type;
4883 lx_tal_rec tal_rec_type;
4884 lx_temp_talv_rec talv_rec_type;
4885 -------------------------------
4886 -- FUNCTION fill_who_columns --
4887 -------------------------------
4888 FUNCTION fill_who_columns (
4889 p_talv_rec IN talv_rec_type
4890 ) RETURN talv_rec_type IS
4891 l_talv_rec talv_rec_type := p_talv_rec;
4892 BEGIN
4893 l_talv_rec.LAST_UPDATE_DATE := SYSDATE;
4894 l_talv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4895 l_talv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4896 RETURN(l_talv_rec);
4897 END fill_who_columns;
4898 ----------------------------------
4899 -- FUNCTION populate_new_record --
4900 ----------------------------------
4901 FUNCTION populate_new_record (
4902 p_talv_rec IN talv_rec_type,
4903 x_talv_rec OUT NOCOPY talv_rec_type
4904 ) RETURN VARCHAR2 IS
4905 l_talv_rec talv_rec_type;
4906 l_row_notfound BOOLEAN := TRUE;
4907 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4908 BEGIN
4909 x_talv_rec := p_talv_rec;
4910 -- Get current database values
4911 l_talv_rec := get_rec(p_talv_rec, l_row_notfound);
4912 IF (l_row_notfound) THEN
4913 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4914 END IF;
4915 IF (x_talv_rec.id = OKC_API.G_MISS_NUM)
4916 THEN
4917 x_talv_rec.id := l_talv_rec.id;
4918 END IF;
4919 IF (x_talv_rec.object_version_number = OKC_API.G_MISS_NUM)
4920 THEN
4921 x_talv_rec.object_version_number := l_talv_rec.object_version_number;
4922 END IF;
4923 IF (x_talv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4924 THEN
4925 x_talv_rec.sfwt_flag := l_talv_rec.sfwt_flag;
4926 END IF;
4927 IF (x_talv_rec.tas_id = OKC_API.G_MISS_NUM)
4928 THEN
4929 x_talv_rec.tas_id := l_talv_rec.tas_id;
4930 END IF;
4931 IF (x_talv_rec.ilo_id = OKC_API.G_MISS_NUM)
4932 THEN
4933 x_talv_rec.ilo_id := l_talv_rec.ilo_id;
4934 END IF;
4935 IF (x_talv_rec.ilo_id_old = OKC_API.G_MISS_NUM)
4936 THEN
4937 x_talv_rec.ilo_id_old := l_talv_rec.ilo_id_old;
4938 END IF;
4939 IF (x_talv_rec.iay_id = OKC_API.G_MISS_NUM)
4940 THEN
4941 x_talv_rec.iay_id := l_talv_rec.iay_id;
4942 END IF;
4943 IF (x_talv_rec.iay_id_new = OKC_API.G_MISS_NUM)
4944 THEN
4945 x_talv_rec.iay_id_new := l_talv_rec.iay_id_new;
4946 END IF;
4947 IF (x_talv_rec.kle_id = OKC_API.G_MISS_NUM)
4948 THEN
4949 x_talv_rec.kle_id := l_talv_rec.kle_id;
4950 END IF;
4951 IF (x_talv_rec.dnz_khr_id = OKC_API.G_MISS_NUM)
4952 THEN
4953 x_talv_rec.dnz_khr_id := l_talv_rec.dnz_khr_id;
4954 END IF;
4955 IF (x_talv_rec.line_number = OKC_API.G_MISS_NUM)
4956 THEN
4957 x_talv_rec.line_number := l_talv_rec.line_number;
4958 END IF;
4959 IF (x_talv_rec.org_id = OKC_API.G_MISS_NUM)
4960 THEN
4961 x_talv_rec.org_id := l_talv_rec.org_id;
4962 END IF;
4963 IF (x_talv_rec.tal_type = OKC_API.G_MISS_CHAR)
4964 THEN
4965 x_talv_rec.tal_type := l_talv_rec.tal_type;
4966 END IF;
4967 IF (x_talv_rec.asset_number = OKC_API.G_MISS_CHAR)
4968 THEN
4969 x_talv_rec.asset_number := l_talv_rec.asset_number;
4970 END IF;
4971 IF (x_talv_rec.description = OKC_API.G_MISS_CHAR)
4972 THEN
4973 x_talv_rec.description := l_talv_rec.description;
4974 END IF;
4975 IF (x_talv_rec.fa_location_id = OKC_API.G_MISS_NUM)
4976 THEN
4977 x_talv_rec.fa_location_id := l_talv_rec.fa_location_id;
4978 END IF;
4979 IF (x_talv_rec.original_cost = OKC_API.G_MISS_NUM)
4980 THEN
4981 x_talv_rec.original_cost := l_talv_rec.original_cost;
4982 END IF;
4983 IF (x_talv_rec.current_units = OKC_API.G_MISS_NUM)
4984 THEN
4985 x_talv_rec.current_units := l_talv_rec.current_units;
4986 END IF;
4987 IF (x_talv_rec.manufacturer_name = OKC_API.G_MISS_CHAR)
4988 THEN
4989 x_talv_rec.manufacturer_name := l_talv_rec.manufacturer_name;
4990 END IF;
4991 IF (x_talv_rec.year_manufactured = OKC_API.G_MISS_NUM)
4992 THEN
4993 x_talv_rec.year_manufactured := l_talv_rec.year_manufactured;
4994 END IF;
4995 IF (x_talv_rec.supplier_id = OKC_API.G_MISS_NUM)
4996 THEN
4997 x_talv_rec.supplier_id := l_talv_rec.supplier_id;
4998 END IF;
4999 IF (x_talv_rec.used_asset_yn = OKC_API.G_MISS_CHAR)
5000 THEN
5001 x_talv_rec.used_asset_yn := l_talv_rec.used_asset_yn;
5002 END IF;
5003 IF (x_talv_rec.tag_number = OKC_API.G_MISS_CHAR)
5004 THEN
5005 x_talv_rec.tag_number := l_talv_rec.tag_number;
5006 END IF;
5007 IF (x_talv_rec.model_number = OKC_API.G_MISS_CHAR)
5008 THEN
5009 x_talv_rec.model_number := l_talv_rec.model_number;
5010 END IF;
5011 IF (x_talv_rec.corporate_book = OKC_API.G_MISS_CHAR)
5012 THEN
5013 x_talv_rec.corporate_book := l_talv_rec.corporate_book;
5014 END IF;
5015 IF (x_talv_rec.date_purchased = OKC_API.G_MISS_DATE)
5016 THEN
5017 x_talv_rec.date_purchased := l_talv_rec.date_purchased;
5018 END IF;
5019 IF (x_talv_rec.date_delivery = OKC_API.G_MISS_DATE)
5020 THEN
5021 x_talv_rec.date_delivery := l_talv_rec.date_delivery;
5022 END IF;
5023 IF (x_talv_rec.in_service_date = OKC_API.G_MISS_DATE)
5024 THEN
5025 x_talv_rec.in_service_date := l_talv_rec.in_service_date;
5026 END IF;
5027 IF (x_talv_rec.life_in_months = OKC_API.G_MISS_NUM)
5028 THEN
5029 x_talv_rec.life_in_months := l_talv_rec.life_in_months;
5030 END IF;
5031 IF (x_talv_rec.depreciation_id = OKC_API.G_MISS_NUM)
5032 THEN
5033 x_talv_rec.depreciation_id := l_talv_rec.depreciation_id;
5034 END IF;
5035 IF (x_talv_rec.depreciation_cost = OKC_API.G_MISS_NUM)
5036 THEN
5037 x_talv_rec.depreciation_cost := l_talv_rec.depreciation_cost;
5038 END IF;
5039 IF (x_talv_rec.deprn_method = OKC_API.G_MISS_CHAR)
5040 THEN
5041 x_talv_rec.deprn_method := l_talv_rec.deprn_method;
5042 END IF;
5043 IF (x_talv_rec.deprn_rate = OKC_API.G_MISS_NUM)
5044 THEN
5045 x_talv_rec.deprn_rate := l_talv_rec.deprn_rate;
5046 END IF;
5047 IF (x_talv_rec.salvage_value = OKC_API.G_MISS_NUM)
5048 THEN
5049 x_talv_rec.salvage_value := l_talv_rec.salvage_value;
5050 END IF;
5051 IF (x_talv_rec.percent_salvage_value = OKC_API.G_MISS_NUM)
5052 THEN
5053 x_talv_rec.percent_salvage_value := l_talv_rec.percent_salvage_value;
5054 END IF;
5055 --Bug# 2981308
5056 IF (x_talv_rec.asset_key_id = OKL_API.G_MISS_NUM)
5057 THEN
5058 x_talv_rec.asset_key_id := l_talv_rec.asset_key_id;
5059 END IF;
5060 -- Bug# 4028371
5061 IF (x_talv_rec.fa_trx_date = OKC_API.G_MISS_DATE)
5062 THEN
5063 x_talv_rec.fa_trx_date := l_talv_rec.fa_trx_date;
5064 END IF;
5065 --Bug# 4899328
5066 IF (x_talv_rec.fa_cost = OKL_API.G_MISS_NUM)
5067 THEN
5068 x_talv_rec.fa_cost := l_talv_rec.fa_cost;
5069 END IF;
5070 IF (x_talv_rec.attribute_category = OKC_API.G_MISS_CHAR)
5071 THEN
5072 x_talv_rec.attribute_category := l_talv_rec.attribute_category;
5073 END IF;
5074 IF (x_talv_rec.attribute1 = OKC_API.G_MISS_CHAR)
5075 THEN
5076 x_talv_rec.attribute1 := l_talv_rec.attribute1;
5077 END IF;
5078 IF (x_talv_rec.attribute2 = OKC_API.G_MISS_CHAR)
5079 THEN
5080 x_talv_rec.attribute2 := l_talv_rec.attribute2;
5081 END IF;
5082 IF (x_talv_rec.attribute3 = OKC_API.G_MISS_CHAR)
5083 THEN
5084 x_talv_rec.attribute3 := l_talv_rec.attribute3;
5085 END IF;
5086 IF (x_talv_rec.attribute4 = OKC_API.G_MISS_CHAR)
5087 THEN
5088 x_talv_rec.attribute4 := l_talv_rec.attribute4;
5089 END IF;
5090 IF (x_talv_rec.attribute5 = OKC_API.G_MISS_CHAR)
5091 THEN
5092 x_talv_rec.attribute5 := l_talv_rec.attribute5;
5093 END IF;
5094 IF (x_talv_rec.attribute6 = OKC_API.G_MISS_CHAR)
5095 THEN
5096 x_talv_rec.attribute6 := l_talv_rec.attribute6;
5097 END IF;
5098 IF (x_talv_rec.attribute7 = OKC_API.G_MISS_CHAR)
5099 THEN
5100 x_talv_rec.attribute7 := l_talv_rec.attribute7;
5101 END IF;
5102 IF (x_talv_rec.attribute8 = OKC_API.G_MISS_CHAR)
5103 THEN
5104 x_talv_rec.attribute8 := l_talv_rec.attribute8;
5105 END IF;
5106 IF (x_talv_rec.attribute9 = OKC_API.G_MISS_CHAR)
5107 THEN
5108 x_talv_rec.attribute9 := l_talv_rec.attribute9;
5109 END IF;
5110 IF (x_talv_rec.attribute10 = OKC_API.G_MISS_CHAR)
5111 THEN
5112 x_talv_rec.attribute10 := l_talv_rec.attribute10;
5113 END IF;
5114 IF (x_talv_rec.attribute11 = OKC_API.G_MISS_CHAR)
5115 THEN
5116 x_talv_rec.attribute11 := l_talv_rec.attribute11;
5117 END IF;
5118 IF (x_talv_rec.attribute12 = OKC_API.G_MISS_CHAR)
5119 THEN
5120 x_talv_rec.attribute12 := l_talv_rec.attribute12;
5121 END IF;
5122 IF (x_talv_rec.attribute13 = OKC_API.G_MISS_CHAR)
5123 THEN
5124 x_talv_rec.attribute13 := l_talv_rec.attribute13;
5125 END IF;
5126 IF (x_talv_rec.attribute14 = OKC_API.G_MISS_CHAR)
5127 THEN
5128 x_talv_rec.attribute14 := l_talv_rec.attribute14;
5129 END IF;
5130 IF (x_talv_rec.attribute15 = OKC_API.G_MISS_CHAR)
5131 THEN
5132 x_talv_rec.attribute15 := l_talv_rec.attribute15;
5133 END IF;
5134 IF (x_talv_rec.created_by = OKC_API.G_MISS_NUM)
5135 THEN
5136 x_talv_rec.created_by := l_talv_rec.created_by;
5137 END IF;
5138 IF (x_talv_rec.creation_date = OKC_API.G_MISS_DATE)
5139 THEN
5140 x_talv_rec.creation_date := l_talv_rec.creation_date;
5141 END IF;
5142 IF (x_talv_rec.last_updated_by = OKC_API.G_MISS_NUM)
5143 THEN
5144 x_talv_rec.last_updated_by := l_talv_rec.last_updated_by;
5145 END IF;
5146 IF (x_talv_rec.last_update_date = OKC_API.G_MISS_DATE)
5147 THEN
5148 x_talv_rec.last_update_date := l_talv_rec.last_update_date;
5149 END IF;
5150 IF (x_talv_rec.last_update_login = OKC_API.G_MISS_NUM)
5151 THEN
5152 x_talv_rec.last_update_login := l_talv_rec.last_update_login;
5153 END IF;
5154 IF (x_talv_rec.depreciate_yn = OKC_API.G_MISS_CHAR)
5155 THEN
5156 x_talv_rec.depreciate_yn := l_talv_rec.depreciate_yn;
5157 END IF;
5158 IF (x_talv_rec.hold_period_days = OKC_API.G_MISS_NUM)
5159 THEN
5160 x_talv_rec.hold_period_days := l_talv_rec.hold_period_days;
5161 END IF;
5162 IF (x_talv_rec.old_salvage_value = OKC_API.G_MISS_NUM)
5163 THEN
5164 x_talv_rec.old_salvage_value := l_talv_rec.old_salvage_value;
5165 END IF;
5166 IF (x_talv_rec.new_residual_value = OKC_API.G_MISS_NUM)
5167 THEN
5168 x_talv_rec.new_residual_value := l_talv_rec.new_residual_value;
5169 END IF;
5170 IF (x_talv_rec.old_residual_value = OKC_API.G_MISS_NUM)
5171 THEN
5172 x_talv_rec.old_residual_value := l_talv_rec.old_residual_value;
5173 END IF;
5174 IF (x_talv_rec.units_retired = OKC_API.G_MISS_NUM)
5175 THEN
5176 x_talv_rec.units_retired := l_talv_rec.units_retired;
5177 END IF;
5178 IF (x_talv_rec.cost_retired = OKC_API.G_MISS_NUM)
5179 THEN
5180 x_talv_rec.cost_retired := l_talv_rec.cost_retired;
5181 END IF;
5182 IF (x_talv_rec.sale_proceeds = OKC_API.G_MISS_NUM)
5183 THEN
5184 x_talv_rec.sale_proceeds := l_talv_rec.sale_proceeds;
5185 END IF;
5186 IF (x_talv_rec.removal_cost = OKC_API.G_MISS_NUM)
5187 THEN
5188 x_talv_rec.removal_cost := l_talv_rec.removal_cost;
5189 END IF;
5190 IF (x_talv_rec.dnz_asset_id = OKC_API.G_MISS_NUM)
5191 THEN
5192 x_talv_rec.dnz_asset_id := l_talv_rec.dnz_asset_id;
5193 END IF;
5194 IF (x_talv_rec.date_due = OKC_API.G_MISS_DATE)
5195 THEN
5196 x_talv_rec.date_due := l_talv_rec.date_due;
5197 END IF;
5198 IF (x_talv_rec.rep_asset_id = OKC_API.G_MISS_NUM)
5199 THEN
5200 x_talv_rec.rep_asset_id := l_talv_rec.rep_asset_id;
5201 END IF;
5202 IF (x_talv_rec.lke_asset_id = OKC_API.G_MISS_NUM)
5203 THEN
5204 x_talv_rec.lke_asset_id := l_talv_rec.lke_asset_id;
5205 END IF;
5206 IF (x_talv_rec.match_amount = OKC_API.G_MISS_NUM)
5207 THEN
5208 x_talv_rec.match_amount := l_talv_rec.match_amount;
5209 END IF;
5210 IF (x_talv_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR)
5211 THEN
5212 x_talv_rec.split_into_singles_flag := l_talv_rec.split_into_singles_flag;
5213 END IF;
5214 IF (x_talv_rec.split_into_units = OKC_API.G_MISS_NUM)
5215 THEN
5216 x_talv_rec.split_into_units := l_talv_rec.split_into_units;
5217 END IF;
5218
5219 -- Multi Currency Change
5220 IF (x_talv_rec.currency_code = OKC_API.G_MISS_CHAR)
5221 THEN
5222 x_talv_rec.currency_code := l_talv_rec.currency_code;
5223 END IF;
5224 IF (x_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
5225 THEN
5226 x_talv_rec.currency_conversion_type := l_talv_rec.currency_conversion_type;
5227 END IF;
5228 IF (x_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
5229 THEN
5230 x_talv_rec.currency_conversion_rate := l_talv_rec.currency_conversion_rate;
5231 END IF;
5232 IF (x_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
5233 THEN
5234 x_talv_rec.currency_conversion_date := l_talv_rec.currency_conversion_date;
5235 END IF;
5236 -- Multi Currency Change
5237
5238 -- VRS Project - START
5239 IF (x_talv_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM)
5240 THEN
5241 x_talv_rec.RESIDUAL_SHR_PARTY_ID := l_talv_rec.RESIDUAL_SHR_PARTY_ID;
5242 END IF;
5243 IF (x_talv_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM)
5244 THEN
5245 x_talv_rec.RESIDUAL_SHR_AMOUNT := l_talv_rec.RESIDUAL_SHR_AMOUNT;
5246 END IF;
5247 IF (x_talv_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM)
5248 THEN
5249 x_talv_rec.RETIREMENT_ID := l_talv_rec.RETIREMENT_ID;
5250 END IF;
5251 -- VRS Project - END
5252
5253 RETURN(l_return_status);
5254 END populate_new_record;
5255 -----------------------------------------
5256 -- Set_Attributes for:OKL_TXL_ASSETS_V --
5257 -----------------------------------------
5258 FUNCTION Set_Attributes (
5259 p_talv_rec IN talv_rec_type,
5260 x_talv_rec OUT NOCOPY talv_rec_type
5261 ) RETURN VARCHAR2 IS
5262 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5263 BEGIN
5264 x_talv_rec := p_talv_rec;
5265 x_talv_rec.OBJECT_VERSION_NUMBER := NVL(x_talv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
5266 RETURN(l_return_status);
5267 END Set_Attributes;
5268 BEGIN
5269 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5270 G_PKG_NAME,
5271 p_init_msg_list,
5272 l_api_version,
5273 p_api_version,
5274 '_PVT',
5275 x_return_status);
5276 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5277 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5278 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5279 RAISE OKC_API.G_EXCEPTION_ERROR;
5280 END IF;
5281 --- Setting item attributes
5282 l_return_status := Set_Attributes(
5283 p_talv_rec, -- IN
5284 l_talv_rec); -- OUT
5285 --- If any errors happen abort API
5286 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5287 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5288 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5289 RAISE OKC_API.G_EXCEPTION_ERROR;
5290 END IF;
5291 l_return_status := populate_new_record(l_talv_rec, l_def_talv_rec);
5292 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5293 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5294 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5295 RAISE OKC_API.G_EXCEPTION_ERROR;
5296 END IF;
5297 l_def_talv_rec := fill_who_columns(l_def_talv_rec);
5298 --- Validate all non-missing attributes (Item Level Validation)
5299 l_return_status := Validate_Attributes(l_def_talv_rec);
5300 --- If any errors happen abort API
5301 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5302 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5303 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5304 RAISE OKC_API.G_EXCEPTION_ERROR;
5305 END IF;
5306 l_return_status := Validate_Record(l_def_talv_rec);
5307 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5308 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5309 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5310 RAISE OKC_API.G_EXCEPTION_ERROR;
5311 END IF;
5312
5313 --
5314 -- Multi-Currency Change, dedey, 12/04/2002
5315 --
5316 validate_currency(
5317 x_return_status => l_return_status,
5318 p_talv_rec => l_def_talv_rec,
5319 x_talv_rec => lx_temp_talv_rec
5320 );
5321
5322 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5323 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5324 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5325 RAISE OKC_API.G_EXCEPTION_ERROR;
5326 END IF;
5327 l_def_talv_rec := lx_temp_talv_rec;
5328
5329 --dbms_output.put_line('After Change: '||lx_temp_talv_rec.currency_code);
5330 --dbms_output.put_line('After Change: '||l_def_talv_rec.currency_code);
5331 --
5332 -- Multi-Currency Change
5333 --
5334
5335 --
5336 -- Fix Bug# 2737014
5337 --
5338 -- Round off amounts
5339 --
5340 roundoff_line_amount(
5341 x_return_status => l_return_status,
5342 x_msg_count => x_msg_count,
5343 x_msg_data => x_msg_data,
5344 p_talv_rec => l_def_talv_rec,
5345 x_talv_rec => lx_temp_talv_rec
5346 );
5347
5348 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5349 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5350 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5351 RAISE OKC_API.G_EXCEPTION_ERROR;
5352 END IF;
5353 l_def_talv_rec := lx_temp_talv_rec;
5354
5355 --dbms_output.put_line('After Change Orig cost: '||lx_temp_talv_rec.original_cost);
5356 --dbms_output.put_line('After Change Orig cost: '||l_def_talv_rec.original_cost);
5357
5358 --------------------------------------
5359 -- Move VIEW record to "Child" records
5360 --------------------------------------
5361 migrate(l_def_talv_rec, l_okl_txl_assets_tl_rec);
5362 migrate(l_def_talv_rec, l_tal_rec);
5363 --------------------------------------------
5364 -- Call the UPDATE_ROW for each child record
5365 --------------------------------------------
5366 update_row(
5367 p_init_msg_list,
5368 x_return_status,
5369 x_msg_count,
5370 x_msg_data,
5371 l_okl_txl_assets_tl_rec,
5372 lx_okl_txl_assets_tl_rec
5373 );
5374 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5375 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5376 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5377 RAISE OKC_API.G_EXCEPTION_ERROR;
5378 END IF;
5379 migrate(lx_okl_txl_assets_tl_rec, l_def_talv_rec);
5380 update_row(
5381 p_init_msg_list,
5382 x_return_status,
5383 x_msg_count,
5384 x_msg_data,
5385 l_tal_rec,
5386 lx_tal_rec
5387 );
5388 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5389 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5390 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5391 RAISE OKC_API.G_EXCEPTION_ERROR;
5392 END IF;
5393 migrate(lx_tal_rec, l_def_talv_rec);
5394 x_talv_rec := l_def_talv_rec;
5395 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5396 EXCEPTION
5397 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5398 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5399 (
5400 l_api_name,
5401 G_PKG_NAME,
5402 'OKC_API.G_RET_STS_ERROR',
5403 x_msg_count,
5404 x_msg_data,
5405 '_PVT'
5406 );
5407 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5408 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5409 (
5410 l_api_name,
5411 G_PKG_NAME,
5412 'OKC_API.G_RET_STS_UNEXP_ERROR',
5413 x_msg_count,
5414 x_msg_data,
5415 '_PVT'
5416 );
5417 WHEN OTHERS THEN
5418 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5419 (
5420 l_api_name,
5421 G_PKG_NAME,
5422 'OTHERS',
5423 x_msg_count,
5424 x_msg_data,
5425 '_PVT'
5426 );
5427 END update_row;
5428 ----------------------------------------
5429 -- PL/SQL TBL update_row for:TALV_TBL --
5430 ----------------------------------------
5431 PROCEDURE update_row(
5432 p_api_version IN NUMBER,
5433 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5434 x_return_status OUT NOCOPY VARCHAR2,
5435 x_msg_count OUT NOCOPY NUMBER,
5436 x_msg_data OUT NOCOPY VARCHAR2,
5437 p_talv_tbl IN talv_tbl_type,
5438 x_talv_tbl OUT NOCOPY talv_tbl_type) IS
5439
5440 l_api_version CONSTANT NUMBER := 1;
5441 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
5442 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5443 i NUMBER := 0;
5444 BEGIN
5445 OKC_API.init_msg_list(p_init_msg_list);
5446 -- Make sure PL/SQL table has records in it before passing
5447 IF (p_talv_tbl.COUNT > 0) THEN
5448 i := p_talv_tbl.FIRST;
5449 LOOP
5450 update_row (
5451 p_api_version => p_api_version,
5452 p_init_msg_list => OKC_API.G_FALSE,
5453 x_return_status => x_return_status,
5454 x_msg_count => x_msg_count,
5455 x_msg_data => x_msg_data,
5456 p_talv_rec => p_talv_tbl(i),
5457 x_talv_rec => x_talv_tbl(i));
5458 EXIT WHEN (i = p_talv_tbl.LAST);
5459 i := p_talv_tbl.NEXT(i);
5460 END LOOP;
5461 END IF;
5462 EXCEPTION
5463 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5464 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5465 (
5466 l_api_name,
5467 G_PKG_NAME,
5468 'OKC_API.G_RET_STS_ERROR',
5469 x_msg_count,
5470 x_msg_data,
5471 '_PVT'
5472 );
5473 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5474 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5475 (
5476 l_api_name,
5477 G_PKG_NAME,
5478 'OKC_API.G_RET_STS_UNEXP_ERROR',
5479 x_msg_count,
5480 x_msg_data,
5481 '_PVT'
5482 );
5483 WHEN OTHERS THEN
5484 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5485 (
5486 l_api_name,
5487 G_PKG_NAME,
5488 'OTHERS',
5489 x_msg_count,
5490 x_msg_data,
5491 '_PVT'
5492 );
5493 END update_row;
5494
5495 ---------------------------------------------------------------------------
5496 -- PROCEDURE delete_row
5497 ---------------------------------------------------------------------------
5498 -------------------------------------
5499 -- delete_row for:OKL_TXL_ASSETS_B --
5500 -------------------------------------
5501 PROCEDURE delete_row(
5502 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5503 x_return_status OUT NOCOPY VARCHAR2,
5504 x_msg_count OUT NOCOPY NUMBER,
5505 x_msg_data OUT NOCOPY VARCHAR2,
5506 p_tal_rec IN tal_rec_type) IS
5507
5508 l_api_version CONSTANT NUMBER := 1;
5509 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5510 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5511 l_tal_rec tal_rec_type:= p_tal_rec;
5512 l_row_notfound BOOLEAN := TRUE;
5513 BEGIN
5514 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5515 p_init_msg_list,
5516 '_PVT',
5517 x_return_status);
5518 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5519 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5520 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5521 RAISE OKC_API.G_EXCEPTION_ERROR;
5522 END IF;
5523 DELETE FROM OKL_TXL_ASSETS_B
5524 WHERE ID = l_tal_rec.id;
5525
5526 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5527 EXCEPTION
5528 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5529 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5530 (
5531 l_api_name,
5532 G_PKG_NAME,
5533 'OKC_API.G_RET_STS_ERROR',
5534 x_msg_count,
5535 x_msg_data,
5536 '_PVT'
5537 );
5538 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5539 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5540 (
5541 l_api_name,
5542 G_PKG_NAME,
5543 'OKC_API.G_RET_STS_UNEXP_ERROR',
5544 x_msg_count,
5545 x_msg_data,
5546 '_PVT'
5547 );
5548 WHEN OTHERS THEN
5549 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5550 (
5551 l_api_name,
5552 G_PKG_NAME,
5553 'OTHERS',
5554 x_msg_count,
5555 x_msg_data,
5556 '_PVT'
5557 );
5558 END delete_row;
5559 --------------------------------------
5560 -- delete_row for:OKL_TXL_ASSETS_TL --
5561 --------------------------------------
5562 PROCEDURE delete_row(
5563 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5564 x_return_status OUT NOCOPY VARCHAR2,
5565 x_msg_count OUT NOCOPY NUMBER,
5566 x_msg_data OUT NOCOPY VARCHAR2,
5567 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
5568
5569 l_api_version CONSTANT NUMBER := 1;
5570 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5571 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5572 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type:= p_okl_txl_assets_tl_rec;
5573 l_row_notfound BOOLEAN := TRUE;
5574 ------------------------------------------
5575 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
5576 ------------------------------------------
5577 FUNCTION Set_Attributes (
5578 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
5579 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
5580 ) RETURN VARCHAR2 IS
5581 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5582 BEGIN
5583 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
5584 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
5585 RETURN(l_return_status);
5586 END Set_Attributes;
5587 BEGIN
5588 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5589 p_init_msg_list,
5590 '_PVT',
5591 x_return_status);
5592 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5593 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5594 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5595 RAISE OKC_API.G_EXCEPTION_ERROR;
5596 END IF;
5597 --- Setting item attributes
5598 l_return_status := Set_Attributes(
5599 p_okl_txl_assets_tl_rec, -- IN
5600 l_okl_txl_assets_tl_rec); -- OUT
5601 --- If any errors happen abort API
5602 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5603 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5604 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5605 RAISE OKC_API.G_EXCEPTION_ERROR;
5606 END IF;
5607 DELETE FROM OKL_TXL_ASSETS_TL
5608 WHERE ID = l_okl_txl_assets_tl_rec.id;
5609
5610 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5611 EXCEPTION
5612 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5613 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5614 (
5615 l_api_name,
5616 G_PKG_NAME,
5617 'OKC_API.G_RET_STS_ERROR',
5618 x_msg_count,
5619 x_msg_data,
5620 '_PVT'
5621 );
5622 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5623 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5624 (
5625 l_api_name,
5626 G_PKG_NAME,
5627 'OKC_API.G_RET_STS_UNEXP_ERROR',
5628 x_msg_count,
5629 x_msg_data,
5630 '_PVT'
5631 );
5632 WHEN OTHERS THEN
5633 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5634 (
5635 l_api_name,
5636 G_PKG_NAME,
5637 'OTHERS',
5638 x_msg_count,
5639 x_msg_data,
5640 '_PVT'
5641 );
5642 END delete_row;
5643 -------------------------------------
5644 -- delete_row for:OKL_TXL_ASSETS_V --
5645 -------------------------------------
5646 PROCEDURE delete_row(
5647 p_api_version IN NUMBER,
5648 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5649 x_return_status OUT NOCOPY VARCHAR2,
5650 x_msg_count OUT NOCOPY NUMBER,
5651 x_msg_data OUT NOCOPY VARCHAR2,
5652 p_talv_rec IN talv_rec_type) IS
5653
5654 l_api_version CONSTANT NUMBER := 1;
5655 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5656 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5657 l_talv_rec talv_rec_type := p_talv_rec;
5658 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
5659 l_tal_rec tal_rec_type;
5660 BEGIN
5661 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5662 G_PKG_NAME,
5663 p_init_msg_list,
5664 l_api_version,
5665 p_api_version,
5666 '_PVT',
5667 x_return_status);
5668 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5669 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5670 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5671 RAISE OKC_API.G_EXCEPTION_ERROR;
5672 END IF;
5673 --------------------------------------
5674 -- Move VIEW record to "Child" records
5675 --------------------------------------
5676 migrate(l_talv_rec, l_okl_txl_assets_tl_rec);
5677 migrate(l_talv_rec, l_tal_rec);
5678 --------------------------------------------
5679 -- Call the DELETE_ROW for each child record
5680 --------------------------------------------
5681 delete_row(
5682 p_init_msg_list,
5683 x_return_status,
5684 x_msg_count,
5685 x_msg_data,
5686 l_okl_txl_assets_tl_rec
5687 );
5688 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5689 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5690 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5691 RAISE OKC_API.G_EXCEPTION_ERROR;
5692 END IF;
5693 delete_row(
5694 p_init_msg_list,
5695 x_return_status,
5696 x_msg_count,
5697 x_msg_data,
5698 l_tal_rec
5699 );
5700 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5701 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5702 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5703 RAISE OKC_API.G_EXCEPTION_ERROR;
5704 END IF;
5705 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5706 EXCEPTION
5707 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5708 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5709 (
5710 l_api_name,
5711 G_PKG_NAME,
5712 'OKC_API.G_RET_STS_ERROR',
5713 x_msg_count,
5714 x_msg_data,
5715 '_PVT'
5716 );
5717 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5718 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5719 (
5720 l_api_name,
5721 G_PKG_NAME,
5722 'OKC_API.G_RET_STS_UNEXP_ERROR',
5723 x_msg_count,
5724 x_msg_data,
5725 '_PVT'
5726 );
5727 WHEN OTHERS THEN
5728 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5729 (
5730 l_api_name,
5731 G_PKG_NAME,
5732 'OTHERS',
5733 x_msg_count,
5734 x_msg_data,
5735 '_PVT'
5736 );
5737 END delete_row;
5738 ----------------------------------------
5739 -- PL/SQL TBL delete_row for:TALV_TBL --
5740 ----------------------------------------
5741 PROCEDURE delete_row(
5742 p_api_version IN NUMBER,
5743 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5744 x_return_status OUT NOCOPY VARCHAR2,
5745 x_msg_count OUT NOCOPY NUMBER,
5746 x_msg_data OUT NOCOPY VARCHAR2,
5747 p_talv_tbl IN talv_tbl_type) IS
5748
5749 l_api_version CONSTANT NUMBER := 1;
5750 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5751 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5752 i NUMBER := 0;
5753 BEGIN
5754 OKC_API.init_msg_list(p_init_msg_list);
5755 -- Make sure PL/SQL table has records in it before passing
5756 IF (p_talv_tbl.COUNT > 0) THEN
5757 i := p_talv_tbl.FIRST;
5758 LOOP
5759 delete_row (
5760 p_api_version => p_api_version,
5761 p_init_msg_list => OKC_API.G_FALSE,
5762 x_return_status => x_return_status,
5763 x_msg_count => x_msg_count,
5764 x_msg_data => x_msg_data,
5765 p_talv_rec => p_talv_tbl(i));
5766 EXIT WHEN (i = p_talv_tbl.LAST);
5767 i := p_talv_tbl.NEXT(i);
5768 END LOOP;
5769 END IF;
5770 EXCEPTION
5771 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5772 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5773 (
5774 l_api_name,
5775 G_PKG_NAME,
5776 'OKC_API.G_RET_STS_ERROR',
5777 x_msg_count,
5778 x_msg_data,
5779 '_PVT'
5780 );
5781 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5782 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5783 (
5784 l_api_name,
5785 G_PKG_NAME,
5786 'OKC_API.G_RET_STS_UNEXP_ERROR',
5787 x_msg_count,
5788 x_msg_data,
5789 '_PVT'
5790 );
5791 WHEN OTHERS THEN
5792 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5793 (
5794 l_api_name,
5795 G_PKG_NAME,
5796 'OTHERS',
5797 x_msg_count,
5798 x_msg_data,
5799 '_PVT'
5800 );
5801 END delete_row;
5802 END OKL_TAL_PVT;