[Home] [Help]
PACKAGE BODY: APPS.OKL_TAL_PVT
Source
1 PACKAGE BODY OKL_TAL_PVT AS
2 /* $Header: OKLSTALB.pls 120.8.12020000.2 2012/12/18 06:49:32 vloomba ship $ */
3 -- Badrinath Kuchibholta
4 /************************ HAND-CODED *********************************/
5 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
7
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 var_curr_code VARCHAR2(15);
1580 var_CONVERSION_TYPE VARCHAR2(30);
1581 var_CONVERSION_RATE NUMBER;
1582 var_CONVERSION_RATE_DATE DATE;
1583
1584 CURSOR conv_type_csr (p_conv_type gl_daily_conversion_types.conversion_type%TYPE) IS
1585 SELECT 'Y'
1586 FROM gl_daily_conversion_types
1587 WHERE conversion_type = p_conv_type;
1588
1589 CURSOR curr_csr (p_curr_code gl_currencies.currency_code%TYPE) IS
1590 SELECT 'Y'
1591 FROM gl_currencies
1592 WHERE currency_code = p_curr_code
1593 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
1594 NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
1595
1596 BEGIN
1597
1598 x_talv_rec := p_talv_rec;
1599 --Added the following for bug15956795
1600 Select CURRENCY_CODE,CONVERSION_TYPE,CONVERSION_RATE,CONVERSION_RATE_DATE
1601 into var_curr_code,var_CONVERSION_TYPE,var_CONVERSION_RATE,var_CONVERSION_RATE_DATE
1602 from OKC_k_headers_all_b
1603 where ID=p_talv_rec.dnz_khr_id ;
1604 x_talv_rec.currency_code := var_curr_code;
1605 x_talv_rec.currency_conversion_type :=var_CONVERSION_TYPE ;
1606 x_talv_rec.currency_conversion_rate :=var_CONVERSION_RATE;
1607 x_talv_rec.currency_conversion_date :=var_CONVERSION_RATE_DATE ;
1608
1609 --commented out the following for bug15956795
1610 /* l_func_currency := okl_accounting_util.get_func_curr_code();
1611
1612 --dbms_output.put_line('Func Curr: '||l_func_currency);
1613 --dbms_output.put_line('Trans Curr Code: '|| p_talv_rec.currency_code);
1614 --dbms_output.put_line('Trans Curr Rate: '|| p_talv_rec.currency_conversion_rate);
1615 --dbms_output.put_line('Trans Curr Date: '|| p_talv_rec.currency_conversion_date);
1616 --dbms_output.put_line('Trans Curr Type: '|| p_talv_rec.currency_conversion_type);
1617
1618 IF (p_talv_rec.currency_code IS NULL
1619 OR
1620 p_talv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN -- take functional currency
1621 x_talv_rec.currency_code := l_func_currency;
1622 x_talv_rec.currency_conversion_type := NULL;
1623 x_talv_rec.currency_conversion_rate := NULL;
1624 x_talv_rec.currency_conversion_date := NULL;
1625 ELSE
1626
1627 l_ok := '?';
1628 OPEN curr_csr(p_talv_rec.currency_code);
1629 FETCH curr_csr INTO l_ok;
1630 CLOSE curr_csr;
1631
1632 IF (l_ok <> 'Y') THEN
1633 OKC_API.set_message(p_app_name => G_APP_NAME,
1634 p_msg_name => G_NO_MATCHING_RECORD,
1635 p_token1 => G_COL_NAME_TOKEN,
1636 p_token1_value => 'currency_code');
1637 x_return_status := OKC_API.G_RET_STS_ERROR;
1638 RAISE currency_validation_failed;
1639 END IF;
1640
1641 IF (p_talv_rec.currency_code = l_func_currency) THEN -- both are same
1642 x_talv_rec.currency_conversion_type := NULL;
1643 x_talv_rec.currency_conversion_rate := NULL;
1644 x_talv_rec.currency_conversion_date := NULL;
1645 ELSE -- transactional and functional currency are different
1646
1647 -- Conversion type, date and rate mandetory
1648 IF (p_talv_rec.currency_conversion_type IS NULL
1649 OR
1650 p_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
1651 OKC_API.set_message(
1652 p_app_name => G_APP_NAME,
1653 p_msg_name => G_REQUIRED_VALUE,
1654 p_token1 => G_COL_NAME_TOKEN,
1655 p_token1_value => 'currency_conversion_type');
1656 x_return_status := OKC_API.G_RET_STS_ERROR;
1657 RAISE currency_validation_failed;
1658 END IF;
1659
1660 l_ok := '?';
1661 OPEN conv_type_csr (p_talv_rec.currency_conversion_type);
1662 FETCH conv_type_csr INTO l_ok;
1663 CLOSE conv_type_csr;
1664
1665 IF (l_ok <> 'Y') THEN
1666 OKC_API.set_message(p_app_name => G_APP_NAME,
1667 p_msg_name => G_NO_MATCHING_RECORD,
1668 p_token1 => G_COL_NAME_TOKEN,
1669 p_token1_value => 'currency_conversion_type');
1670 x_return_status := OKC_API.G_RET_STS_ERROR;
1671 RAISE currency_validation_failed;
1672 END IF;
1673
1674 IF (p_talv_rec.currency_conversion_date IS NULL
1675 OR
1676 p_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
1677 OKC_API.set_message(
1678 p_app_name => G_APP_NAME,
1679 p_msg_name => G_REQUIRED_VALUE,
1680 p_token1 => G_COL_NAME_TOKEN,
1681 p_token1_value => 'currency_conversion_date');
1682 x_return_status := OKC_API.G_RET_STS_ERROR;
1683 RAISE currency_validation_failed;
1684 END IF;
1685
1686 IF (p_talv_rec.currency_conversion_type = 'User') THEN
1687
1688 IF (p_talv_rec.currency_conversion_rate IS NULL
1689 OR
1690 p_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
1691 OKC_API.set_message(
1692 p_app_name => G_APP_NAME,
1693 p_msg_name => G_REQUIRED_VALUE,
1694 p_token1 => G_COL_NAME_TOKEN,
1695 p_token1_value => 'currency_conversion_rate');
1696 x_return_status := OKC_API.G_RET_STS_ERROR;
1697 RAISE currency_validation_failed;
1698 END IF;
1699
1700 x_talv_rec.currency_conversion_type := p_talv_rec.currency_conversion_type;
1701 x_talv_rec.currency_conversion_rate := p_talv_rec.currency_conversion_rate;
1702 x_talv_rec.currency_conversion_date := p_talv_rec.currency_conversion_date;
1703
1704 ELSE -- conversion_type <> 'User'
1705
1706 x_talv_rec.currency_conversion_rate := okl_accounting_util.get_curr_con_rate(
1707 p_from_curr_code => p_talv_rec.currency_code,
1708 p_to_curr_code => l_func_currency,
1709 p_con_date => p_talv_rec.currency_conversion_date,
1710 p_con_type => p_talv_rec.currency_conversion_type
1711 );
1712
1713 x_talv_rec.currency_conversion_type := p_talv_rec.currency_conversion_type;
1714 x_talv_rec.currency_conversion_date := p_talv_rec.currency_conversion_date;
1715
1716 END IF; -- conversion_type
1717 END IF; -- currency_code check
1718 END IF; -- currency_code NULL
1719 */
1720
1721 EXCEPTION
1722 WHEN currency_validation_failed THEN
1723 RETURN;
1724 END validate_currency;
1725 -- Multi-Currency Change
1726
1727 ---------------------------------------------------------------------------
1728 -- FUNCTION get_seq_id
1729 ---------------------------------------------------------------------------
1730 FUNCTION get_seq_id RETURN NUMBER IS
1731 BEGIN
1732 RETURN(okc_p_util.raw_to_number(sys_guid()));
1733 END get_seq_id;
1734
1735 ---------------------------------------------------------------------------
1736 -- PROCEDURE qc
1737 ---------------------------------------------------------------------------
1738 PROCEDURE qc IS
1739 BEGIN
1740 null;
1741 END qc;
1742
1743 ---------------------------------------------------------------------------
1744 -- PROCEDURE change_version
1745 ---------------------------------------------------------------------------
1746 PROCEDURE change_version IS
1747 BEGIN
1748 null;
1749 END change_version;
1750
1751 ---------------------------------------------------------------------------
1752 -- PROCEDURE api_copy
1753 ---------------------------------------------------------------------------
1754 PROCEDURE api_copy IS
1755 BEGIN
1756 null;
1757 END api_copy;
1758
1759 ---------------------------------------------------------------------------
1760 -- PROCEDURE add_language
1761 ---------------------------------------------------------------------------
1762 PROCEDURE add_language IS
1763 BEGIN
1764 DELETE FROM OKL_TXL_ASSETS_TL T
1765 WHERE NOT EXISTS (
1766 SELECT NULL
1767 FROM OKL_TXL_ASSETS_B B --fixed bug 3321017 by kmotepal
1768 WHERE B.ID = T.ID
1769 );
1770
1771 UPDATE OKL_TXL_ASSETS_TL T SET (
1772 DESCRIPTION) = (SELECT
1773 B.DESCRIPTION
1774 FROM OKL_TXL_ASSETS_TL B
1775 WHERE B.ID = T.ID
1776 AND B.LANGUAGE = T.SOURCE_LANG)
1777 WHERE (
1778 T.ID,
1779 T.LANGUAGE)
1780 IN (SELECT
1781 SUBT.ID,
1782 SUBT.LANGUAGE
1783 FROM OKL_TXL_ASSETS_TL SUBB, OKL_TXL_ASSETS_TL SUBT
1784 WHERE SUBB.ID = SUBT.ID
1785 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1786 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1787 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
1788 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
1789 ));
1790
1791 INSERT INTO OKL_TXL_ASSETS_TL (
1792 ID,
1793 LANGUAGE,
1794 SOURCE_LANG,
1795 SFWT_FLAG,
1796 DESCRIPTION,
1797 CREATED_BY,
1798 CREATION_DATE,
1799 LAST_UPDATED_BY,
1800 LAST_UPDATE_DATE,
1801 LAST_UPDATE_LOGIN)
1802 SELECT
1803 B.ID,
1804 L.LANGUAGE_CODE,
1805 B.SOURCE_LANG,
1806 B.SFWT_FLAG,
1807 B.DESCRIPTION,
1808 B.CREATED_BY,
1809 B.CREATION_DATE,
1810 B.LAST_UPDATED_BY,
1811 B.LAST_UPDATE_DATE,
1812 B.LAST_UPDATE_LOGIN
1813 FROM OKL_TXL_ASSETS_TL B, FND_LANGUAGES L
1814 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1815 AND B.LANGUAGE = USERENV('LANG')
1816 AND NOT EXISTS(
1817 SELECT NULL
1818 FROM OKL_TXL_ASSETS_TL T
1819 WHERE T.ID = B.ID
1820 AND T.LANGUAGE = L.LANGUAGE_CODE
1821 );
1822
1823 END add_language;
1824
1825 ---------------------------------------------------------------------------
1826 -- FUNCTION get_rec for: OKL_TXL_ASSETS_B
1827 ---------------------------------------------------------------------------
1828 FUNCTION get_rec (
1829 p_tal_rec IN tal_rec_type,
1830 x_no_data_found OUT NOCOPY BOOLEAN
1831 ) RETURN tal_rec_type IS
1832 CURSOR okl_txl_asset_b_pk_csr (p_id IN NUMBER) IS
1833 SELECT ID,
1834 OBJECT_VERSION_NUMBER,
1835 TAS_ID,
1836 ILO_ID,
1837 ILO_ID_OLD,
1838 IAY_ID,
1839 IAY_ID_NEW,
1840 KLE_ID,
1841 DNZ_KHR_ID,
1842 LINE_NUMBER,
1843 ORG_ID,
1844 TAL_TYPE,
1845 ASSET_NUMBER,
1846 FA_LOCATION_ID,
1847 ORIGINAL_COST,
1848 CURRENT_UNITS,
1849 MANUFACTURER_NAME,
1850 YEAR_MANUFACTURED,
1851 SUPPLIER_ID,
1852 USED_ASSET_YN,
1853 TAG_NUMBER,
1854 MODEL_NUMBER,
1855 CORPORATE_BOOK,
1856 DATE_PURCHASED,
1857 DATE_DELIVERY,
1858 IN_SERVICE_DATE,
1859 LIFE_IN_MONTHS,
1860 DEPRECIATION_ID,
1861 DEPRECIATION_COST,
1862 DEPRN_METHOD,
1863 DEPRN_RATE,
1864 SALVAGE_VALUE,
1865 PERCENT_SALVAGE_VALUE,
1866 --Bug# 2981308
1867 ASSET_KEY_ID,
1868 -- Bug# 4028371
1869 FA_TRX_DATE,
1870 --Bug# 4899328
1871 FA_COST,
1872 ATTRIBUTE_CATEGORY,
1873 ATTRIBUTE1,
1874 ATTRIBUTE2,
1875 ATTRIBUTE3,
1876 ATTRIBUTE4,
1877 ATTRIBUTE5,
1878 ATTRIBUTE6,
1879 ATTRIBUTE7,
1880 ATTRIBUTE8,
1881 ATTRIBUTE9,
1882 ATTRIBUTE10,
1883 ATTRIBUTE11,
1884 ATTRIBUTE12,
1885 ATTRIBUTE13,
1886 ATTRIBUTE14,
1887 ATTRIBUTE15,
1888 CREATED_BY,
1889 CREATION_DATE,
1890 LAST_UPDATED_BY,
1891 LAST_UPDATE_DATE,
1892 LAST_UPDATE_LOGIN,
1893 DEPRECIATE_YN,
1894 HOLD_PERIOD_DAYS,
1895 OLD_SALVAGE_VALUE,
1896 NEW_RESIDUAL_VALUE,
1897 OLD_RESIDUAL_VALUE,
1898 UNITS_RETIRED,
1899 COST_RETIRED,
1900 SALE_PROCEEDS,
1901 REMOVAL_COST,
1902 DNZ_ASSET_ID,
1903 DATE_DUE,
1904 REP_ASSET_ID,
1905 LKE_ASSET_ID,
1906 MATCH_AMOUNT,
1907 SPLIT_INTO_SINGLES_FLAG,
1908 SPLIT_INTO_UNITS,
1909 -- Multi-Currency Change
1910 currency_code,
1911 currency_conversion_type,
1912 currency_conversion_rate,
1913 currency_conversion_date,
1914 -- Multi-Currency Change
1915 -- VRS Project - START
1916 RESIDUAL_SHR_PARTY_ID,
1917 RESIDUAL_SHR_AMOUNT,
1918 RETIREMENT_ID
1919 -- VRS Project - END
1920 FROM Okl_Txl_Assets_B
1921 WHERE okl_txl_assets_b.id = p_id;
1922 l_okl_txl_asset_b_pk okl_txl_asset_b_pk_csr%ROWTYPE;
1923 l_tal_rec tal_rec_type;
1924 BEGIN
1925 x_no_data_found := TRUE;
1926 -- Get current database values
1927 OPEN okl_txl_asset_b_pk_csr (p_tal_rec.id);
1928 FETCH okl_txl_asset_b_pk_csr INTO
1929 l_tal_rec.ID,
1930 l_tal_rec.OBJECT_VERSION_NUMBER,
1931 l_tal_rec.TAS_ID,
1932 l_tal_rec.ILO_ID,
1933 l_tal_rec.ILO_ID_OLD,
1934 l_tal_rec.IAY_ID,
1935 l_tal_rec.IAY_ID_NEW,
1936 l_tal_rec.KLE_ID,
1937 l_tal_rec.DNZ_KHR_ID,
1938 l_tal_rec.LINE_NUMBER,
1939 l_tal_rec.ORG_ID,
1940 l_tal_rec.TAL_TYPE,
1941 l_tal_rec.ASSET_NUMBER,
1942 l_tal_rec.FA_LOCATION_ID,
1943 l_tal_rec.ORIGINAL_COST,
1944 l_tal_rec.CURRENT_UNITS,
1945 l_tal_rec.MANUFACTURER_NAME,
1946 l_tal_rec.YEAR_MANUFACTURED,
1947 l_tal_rec.SUPPLIER_ID,
1948 l_tal_rec.USED_ASSET_YN,
1949 l_tal_rec.TAG_NUMBER,
1950 l_tal_rec.MODEL_NUMBER,
1951 l_tal_rec.CORPORATE_BOOK,
1952 l_tal_rec.DATE_PURCHASED,
1953 l_tal_rec.DATE_DELIVERY,
1954 l_tal_rec.IN_SERVICE_DATE,
1955 l_tal_rec.LIFE_IN_MONTHS,
1956 l_tal_rec.DEPRECIATION_ID,
1957 l_tal_rec.DEPRECIATION_COST,
1958 l_tal_rec.DEPRN_METHOD,
1959 l_tal_rec.DEPRN_RATE,
1960 l_tal_rec.SALVAGE_VALUE,
1961 l_tal_rec.PERCENT_SALVAGE_VALUE,
1962 --Bug# 2981308
1963 l_tal_rec.ASSET_KEY_ID,
1964 -- Bug# 4028371
1965 l_tal_rec.FA_TRX_DATE,
1966 --Bug# 4899328
1967 l_tal_rec.FA_COST,
1968 l_tal_rec.ATTRIBUTE_CATEGORY,
1969 l_tal_rec.ATTRIBUTE1,
1970 l_tal_rec.ATTRIBUTE2,
1971 l_tal_rec.ATTRIBUTE3,
1972 l_tal_rec.ATTRIBUTE4,
1973 l_tal_rec.ATTRIBUTE5,
1974 l_tal_rec.ATTRIBUTE6,
1975 l_tal_rec.ATTRIBUTE7,
1976 l_tal_rec.ATTRIBUTE8,
1977 l_tal_rec.ATTRIBUTE9,
1978 l_tal_rec.ATTRIBUTE10,
1979 l_tal_rec.ATTRIBUTE11,
1980 l_tal_rec.ATTRIBUTE12,
1981 l_tal_rec.ATTRIBUTE13,
1982 l_tal_rec.ATTRIBUTE14,
1983 l_tal_rec.ATTRIBUTE15,
1984 l_tal_rec.CREATED_BY,
1985 l_tal_rec.CREATION_DATE,
1986 l_tal_rec.LAST_UPDATED_BY,
1987 l_tal_rec.LAST_UPDATE_DATE,
1988 l_tal_rec.LAST_UPDATE_LOGIN,
1989 l_tal_rec.DEPRECIATE_YN,
1990 l_tal_rec.HOLD_PERIOD_DAYS,
1991 l_tal_rec.OLD_SALVAGE_VALUE,
1992 l_tal_rec.NEW_RESIDUAL_VALUE,
1993 l_tal_rec.OLD_RESIDUAL_VALUE,
1994 l_tal_rec.UNITS_RETIRED,
1995 l_tal_rec.COST_RETIRED,
1996 l_tal_rec.SALE_PROCEEDS,
1997 l_tal_rec.REMOVAL_COST,
1998 l_tal_rec.DNZ_ASSET_ID,
1999 l_tal_rec.DATE_DUE,
2000 l_tal_rec.REP_ASSET_ID,
2001 l_tal_rec.LKE_ASSET_ID,
2002 l_tal_rec.MATCH_AMOUNT,
2003 l_tal_rec.SPLIT_INTO_SINGLES_FLAG,
2004 l_tal_rec.SPLIT_INTO_UNITS,
2005 -- Multi-Currency Change
2006 l_tal_rec.currency_code,
2007 l_tal_rec.currency_conversion_type,
2008 l_tal_rec.currency_conversion_rate,
2009 l_tal_rec.currency_conversion_date,
2010 -- Multi-Currency Change
2011 -- VRS Project - START
2012 l_tal_rec.RESIDUAL_SHR_PARTY_ID,
2013 l_tal_rec.RESIDUAL_SHR_AMOUNT,
2014 l_tal_rec.RETIREMENT_ID;
2015 -- VRS Project - END
2016 x_no_data_found := okl_txl_asset_b_pk_csr%NOTFOUND;
2017 CLOSE okl_txl_asset_b_pk_csr;
2018 RETURN(l_tal_rec);
2019 END get_rec;
2020
2021 FUNCTION get_rec (
2022 p_tal_rec IN tal_rec_type
2023 ) RETURN tal_rec_type IS
2024 l_row_notfound BOOLEAN := TRUE;
2025 BEGIN
2026 RETURN(get_rec(p_tal_rec, l_row_notfound));
2027 END get_rec;
2028 ---------------------------------------------------------------------------
2029 -- FUNCTION get_rec for: OKL_TXL_ASSETS_TL
2030 ---------------------------------------------------------------------------
2031 FUNCTION get_rec (
2032 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
2033 x_no_data_found OUT NOCOPY BOOLEAN
2034 ) RETURN okl_txl_assets_tl_rec_type IS
2035 CURSOR okl_txl_asset_tl_pk_csr (p_id IN NUMBER,
2036 p_language IN VARCHAR2) IS
2037 SELECT
2038 ID,
2039 LANGUAGE,
2040 SOURCE_LANG,
2041 SFWT_FLAG,
2042 DESCRIPTION,
2043 CREATED_BY,
2044 CREATION_DATE,
2045 LAST_UPDATED_BY,
2046 LAST_UPDATE_DATE,
2047 LAST_UPDATE_LOGIN
2048 FROM Okl_Txl_Assets_Tl
2049 WHERE okl_txl_assets_tl.id = p_id
2050 AND okl_txl_assets_tl.language = p_language;
2051 l_okl_txl_asset_tl_pk okl_txl_asset_tl_pk_csr%ROWTYPE;
2052 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
2053 BEGIN
2054 x_no_data_found := TRUE;
2055 -- Get current database values
2056 OPEN okl_txl_asset_tl_pk_csr (p_okl_txl_assets_tl_rec.id,
2057 p_okl_txl_assets_tl_rec.language);
2058 FETCH okl_txl_asset_tl_pk_csr INTO
2059 l_okl_txl_assets_tl_rec.ID,
2060 l_okl_txl_assets_tl_rec.LANGUAGE,
2061 l_okl_txl_assets_tl_rec.SOURCE_LANG,
2062 l_okl_txl_assets_tl_rec.SFWT_FLAG,
2063 l_okl_txl_assets_tl_rec.DESCRIPTION,
2064 l_okl_txl_assets_tl_rec.CREATED_BY,
2065 l_okl_txl_assets_tl_rec.CREATION_DATE,
2066 l_okl_txl_assets_tl_rec.LAST_UPDATED_BY,
2067 l_okl_txl_assets_tl_rec.LAST_UPDATE_DATE,
2068 l_okl_txl_assets_tl_rec.LAST_UPDATE_LOGIN;
2069 x_no_data_found := okl_txl_asset_tl_pk_csr%NOTFOUND;
2070 CLOSE okl_txl_asset_tl_pk_csr;
2071 RETURN(l_okl_txl_assets_tl_rec);
2072 END get_rec;
2073
2074 FUNCTION get_rec (
2075 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type
2076 ) RETURN okl_txl_assets_tl_rec_type IS
2077 l_row_notfound BOOLEAN := TRUE;
2078 BEGIN
2079 RETURN(get_rec(p_okl_txl_assets_tl_rec, l_row_notfound));
2080 END get_rec;
2081 ---------------------------------------------------------------------------
2082 -- FUNCTION get_rec for: OKL_TXL_ASSETS_V
2083 ---------------------------------------------------------------------------
2084 FUNCTION get_rec (
2085 p_talv_rec IN talv_rec_type,
2086 x_no_data_found OUT NOCOPY BOOLEAN
2087 ) RETURN talv_rec_type IS
2088 CURSOR okl_talv_pk_csr (p_id IN NUMBER) IS
2089 SELECT ID,
2090 OBJECT_VERSION_NUMBER,
2091 SFWT_FLAG,
2092 TAS_ID,
2093 ILO_ID,
2094 ILO_ID_OLD,
2095 IAY_ID,
2096 IAY_ID_NEW,
2097 KLE_ID,
2098 DNZ_KHR_ID,
2099 LINE_NUMBER,
2100 ORG_ID,
2101 TAL_TYPE,
2102 ASSET_NUMBER,
2103 DESCRIPTION,
2104 FA_LOCATION_ID,
2105 ORIGINAL_COST,
2106 CURRENT_UNITS,
2107 MANUFACTURER_NAME,
2108 YEAR_MANUFACTURED,
2109 SUPPLIER_ID,
2110 USED_ASSET_YN,
2111 TAG_NUMBER,
2112 MODEL_NUMBER,
2113 CORPORATE_BOOK,
2114 DATE_PURCHASED,
2115 DATE_DELIVERY,
2116 IN_SERVICE_DATE,
2117 LIFE_IN_MONTHS,
2118 DEPRECIATION_ID,
2119 DEPRECIATION_COST,
2120 DEPRN_METHOD,
2121 DEPRN_RATE,
2122 SALVAGE_VALUE,
2123 PERCENT_SALVAGE_VALUE,
2124 --Bug# 2981308
2125 ASSET_KEY_ID,
2126 -- Bug# 4028371
2127 FA_TRX_DATE,
2128 -- Bug# 4899328
2129 FA_COST,
2130 ATTRIBUTE_CATEGORY,
2131 ATTRIBUTE1,
2132 ATTRIBUTE2,
2133 ATTRIBUTE3,
2134 ATTRIBUTE4,
2135 ATTRIBUTE5,
2136 ATTRIBUTE6,
2137 ATTRIBUTE7,
2138 ATTRIBUTE8,
2139 ATTRIBUTE9,
2140 ATTRIBUTE10,
2141 ATTRIBUTE11,
2142 ATTRIBUTE12,
2143 ATTRIBUTE13,
2144 ATTRIBUTE14,
2145 ATTRIBUTE15,
2146 CREATED_BY,
2147 CREATION_DATE,
2148 LAST_UPDATED_BY,
2149 LAST_UPDATE_DATE,
2150 LAST_UPDATE_LOGIN,
2151 DEPRECIATE_YN,
2152 HOLD_PERIOD_DAYS,
2153 OLD_SALVAGE_VALUE,
2154 NEW_RESIDUAL_VALUE,
2155 OLD_RESIDUAL_VALUE,
2156 UNITS_RETIRED,
2157 COST_RETIRED,
2158 SALE_PROCEEDS,
2159 REMOVAL_COST,
2160 DNZ_ASSET_ID,
2161 DATE_DUE,
2162 REP_ASSET_ID,
2163 LKE_ASSET_ID,
2164 MATCH_AMOUNT,
2165 SPLIT_INTO_SINGLES_FLAG,
2166 SPLIT_INTO_UNITS,
2167 -- Multi-Currency Change
2168 currency_code,
2169 currency_conversion_type,
2170 currency_conversion_rate,
2171 currency_conversion_date,
2172 -- Multi-Currency Change
2173 -- VRS Project - START
2174 RESIDUAL_SHR_PARTY_ID,
2175 RESIDUAL_SHR_AMOUNT,
2176 RETIREMENT_ID
2177 -- VRS Project - END
2178 FROM Okl_Txl_Assets_V
2179 WHERE okl_txl_assets_v.id = p_id;
2180 l_okl_talv_pk okl_talv_pk_csr%ROWTYPE;
2181 l_talv_rec talv_rec_type;
2182 BEGIN
2183 x_no_data_found := TRUE;
2184 -- Get current database values
2185 OPEN okl_talv_pk_csr (p_talv_rec.id);
2186 FETCH okl_talv_pk_csr INTO
2187 l_talv_rec.ID,
2188 l_talv_rec.OBJECT_VERSION_NUMBER,
2189 l_talv_rec.SFWT_FLAG,
2190 l_talv_rec.TAS_ID,
2191 l_talv_rec.ILO_ID,
2192 l_talv_rec.ILO_ID_OLD,
2193 l_talv_rec.IAY_ID,
2194 l_talv_rec.IAY_ID_NEW,
2195 l_talv_rec.KLE_ID,
2196 l_talv_rec.DNZ_KHR_ID,
2197 l_talv_rec.LINE_NUMBER,
2198 l_talv_rec.ORG_ID,
2199 l_talv_rec.TAL_TYPE,
2200 l_talv_rec.ASSET_NUMBER,
2201 l_talv_rec.DESCRIPTION,
2202 l_talv_rec.FA_LOCATION_ID,
2203 l_talv_rec.ORIGINAL_COST,
2204 l_talv_rec.CURRENT_UNITS,
2205 l_talv_rec.MANUFACTURER_NAME,
2206 l_talv_rec.YEAR_MANUFACTURED,
2207 l_talv_rec.SUPPLIER_ID,
2208 l_talv_rec.USED_ASSET_YN,
2209 l_talv_rec.TAG_NUMBER,
2210 l_talv_rec.MODEL_NUMBER,
2211 l_talv_rec.CORPORATE_BOOK,
2212 l_talv_rec.DATE_PURCHASED,
2213 l_talv_rec.DATE_DELIVERY,
2214 l_talv_rec.IN_SERVICE_DATE,
2215 l_talv_rec.LIFE_IN_MONTHS,
2216 l_talv_rec.DEPRECIATION_ID,
2217 l_talv_rec.DEPRECIATION_COST,
2218 l_talv_rec.DEPRN_METHOD,
2219 l_talv_rec.DEPRN_RATE,
2220 l_talv_rec.SALVAGE_VALUE,
2221 l_talv_rec.PERCENT_SALVAGE_VALUE,
2222 --Bug# 2981308
2223 l_talv_rec.ASSET_KEY_ID,
2224 -- Bug# 4028371
2225 l_talv_rec.FA_TRX_DATE,
2226 -- Bug# 4899328
2227 l_talv_rec.FA_COST,
2228 l_talv_rec.ATTRIBUTE_CATEGORY,
2229 l_talv_rec.ATTRIBUTE1,
2230 l_talv_rec.ATTRIBUTE2,
2231 l_talv_rec.ATTRIBUTE3,
2232 l_talv_rec.ATTRIBUTE4,
2233 l_talv_rec.ATTRIBUTE5,
2234 l_talv_rec.ATTRIBUTE6,
2235 l_talv_rec.ATTRIBUTE7,
2236 l_talv_rec.ATTRIBUTE8,
2237 l_talv_rec.ATTRIBUTE9,
2238 l_talv_rec.ATTRIBUTE10,
2239 l_talv_rec.ATTRIBUTE11,
2240 l_talv_rec.ATTRIBUTE12,
2241 l_talv_rec.ATTRIBUTE13,
2242 l_talv_rec.ATTRIBUTE14,
2243 l_talv_rec.ATTRIBUTE15,
2244 l_talv_rec.CREATED_BY,
2245 l_talv_rec.CREATION_DATE,
2246 l_talv_rec.LAST_UPDATED_BY,
2247 l_talv_rec.LAST_UPDATE_DATE,
2248 l_talv_rec.LAST_UPDATE_LOGIN,
2249 l_talv_rec.DEPRECIATE_YN,
2250 l_talv_rec.HOLD_PERIOD_DAYS,
2251 l_talv_rec.OLD_SALVAGE_VALUE,
2252 l_talv_rec.NEW_RESIDUAL_VALUE,
2253 l_talv_rec.OLD_RESIDUAL_VALUE,
2254 l_talv_rec.UNITS_RETIRED,
2255 l_talv_rec.COST_RETIRED,
2256 l_talv_rec.SALE_PROCEEDS,
2257 l_talv_rec.REMOVAL_COST,
2258 l_talv_rec.DNZ_ASSET_ID,
2259 l_talv_rec.DATE_DUE,
2260 l_talv_rec.REP_ASSET_ID,
2261 l_talv_rec.LKE_ASSET_ID,
2262 l_talv_rec.MATCH_AMOUNT,
2263 l_talv_rec.SPLIT_INTO_SINGLES_FLAG,
2264 l_talv_rec.SPLIT_INTO_UNITS,
2265 -- Multi-Currency Change
2266 l_talv_rec.currency_code,
2267 l_talv_rec.currency_conversion_type,
2268 l_talv_rec.currency_conversion_rate,
2269 l_talv_rec.currency_conversion_date,
2270 -- Multi-Currency Change
2271 -- VRS Project - START
2272 l_talv_rec.RESIDUAL_SHR_PARTY_ID,
2273 l_talv_rec.RESIDUAL_SHR_AMOUNT,
2274 l_talv_rec.RETIREMENT_ID;
2275 -- VRS Project - END
2276
2277 x_no_data_found := okl_talv_pk_csr%NOTFOUND;
2278 CLOSE okl_talv_pk_csr;
2279 RETURN(l_talv_rec);
2280 END get_rec;
2281
2282 FUNCTION get_rec (
2283 p_talv_rec IN talv_rec_type
2284 ) RETURN talv_rec_type IS
2285 l_row_notfound BOOLEAN := TRUE;
2286 BEGIN
2287 RETURN(get_rec(p_talv_rec, l_row_notfound));
2288 END get_rec;
2289
2290 ------------------------------------------------------
2291 -- FUNCTION null_out_defaults for: OKL_TXL_ASSETS_V --
2292 ------------------------------------------------------
2293 FUNCTION null_out_defaults (
2294 p_talv_rec IN talv_rec_type
2295 ) RETURN talv_rec_type IS
2296 l_talv_rec talv_rec_type := p_talv_rec;
2297 BEGIN
2298 IF (l_talv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
2299 l_talv_rec.object_version_number := NULL;
2300 END IF;
2301 IF (l_talv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
2302 l_talv_rec.sfwt_flag := NULL;
2303 END IF;
2304 IF (l_talv_rec.tas_id = OKC_API.G_MISS_NUM) THEN
2305 l_talv_rec.tas_id := NULL;
2306 END IF;
2307 IF (l_talv_rec.ilo_id = OKC_API.G_MISS_NUM) THEN
2308 l_talv_rec.ilo_id := NULL;
2309 END IF;
2310 IF (l_talv_rec.ilo_id_old = OKC_API.G_MISS_NUM) THEN
2311 l_talv_rec.ilo_id_old := NULL;
2312 END IF;
2313 IF (l_talv_rec.iay_id = OKC_API.G_MISS_NUM) THEN
2314 l_talv_rec.iay_id := NULL;
2315 END IF;
2316 IF (l_talv_rec.iay_id_new = OKC_API.G_MISS_NUM) THEN
2317 l_talv_rec.iay_id_new := NULL;
2318 END IF;
2319 IF (l_talv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
2320 l_talv_rec.kle_id := NULL;
2321 END IF;
2322 IF (l_talv_rec.dnz_khr_id = OKC_API.G_MISS_NUM) THEN
2323 l_talv_rec.dnz_khr_id := NULL;
2324 END IF;
2325 IF (l_talv_rec.line_number = OKC_API.G_MISS_NUM) THEN
2326 l_talv_rec.line_number := NULL;
2327 END IF;
2328 IF (l_talv_rec.org_id = OKC_API.G_MISS_NUM) THEN
2329 l_talv_rec.org_id := NULL;
2330 END IF;
2331 IF (l_talv_rec.tal_type = OKC_API.G_MISS_CHAR) THEN
2332 l_talv_rec.tal_type := NULL;
2333 END IF;
2334 IF (l_talv_rec.asset_number = OKC_API.G_MISS_CHAR) THEN
2335 l_talv_rec.asset_number := NULL;
2336 END IF;
2337 IF (l_talv_rec.description = OKC_API.G_MISS_CHAR) THEN
2338 l_talv_rec.description := NULL;
2339 END IF;
2340 IF (l_talv_rec.fa_location_id = OKC_API.G_MISS_NUM) THEN
2341 l_talv_rec.fa_location_id := NULL;
2342 END IF;
2343 IF (l_talv_rec.original_cost = OKC_API.G_MISS_NUM) THEN
2344 l_talv_rec.original_cost := NULL;
2345 END IF;
2346 IF (l_talv_rec.current_units = OKC_API.G_MISS_NUM) THEN
2347 l_talv_rec.current_units := NULL;
2348 END IF;
2349 IF (l_talv_rec.manufacturer_name = OKC_API.G_MISS_CHAR) THEN
2350 l_talv_rec.manufacturer_name := NULL;
2351 END IF;
2352 IF (l_talv_rec.year_manufactured = OKC_API.G_MISS_NUM) THEN
2353 l_talv_rec.year_manufactured := NULL;
2354 END IF;
2355 IF (l_talv_rec.supplier_id = OKC_API.G_MISS_NUM) THEN
2356 l_talv_rec.supplier_id := NULL;
2357 END IF;
2358 IF (l_talv_rec.used_asset_yn = OKC_API.G_MISS_CHAR) THEN
2359 l_talv_rec.used_asset_yn := NULL;
2360 END IF;
2361 IF (l_talv_rec.tag_number = OKC_API.G_MISS_CHAR) THEN
2362 l_talv_rec.tag_number := NULL;
2363 END IF;
2364 IF (l_talv_rec.model_number = OKC_API.G_MISS_CHAR) THEN
2365 l_talv_rec.model_number := NULL;
2366 END IF;
2367 IF (l_talv_rec.corporate_book = OKC_API.G_MISS_CHAR) THEN
2368 l_talv_rec.corporate_book := NULL;
2369 END IF;
2370 IF (l_talv_rec.date_purchased = OKC_API.G_MISS_DATE) THEN
2371 l_talv_rec.date_purchased := NULL;
2372 END IF;
2373 IF (l_talv_rec.date_delivery = OKC_API.G_MISS_DATE) THEN
2374 l_talv_rec.date_delivery := NULL;
2375 END IF;
2376 IF (l_talv_rec.in_service_date = OKC_API.G_MISS_DATE) THEN
2377 l_talv_rec.in_service_date := NULL;
2378 END IF;
2379 IF (l_talv_rec.life_in_months = OKC_API.G_MISS_NUM) THEN
2380 l_talv_rec.life_in_months := NULL;
2381 END IF;
2382 IF (l_talv_rec.depreciation_id = OKC_API.G_MISS_NUM) THEN
2383 l_talv_rec.depreciation_id := NULL;
2384 END IF;
2385 IF (l_talv_rec.depreciation_cost = OKC_API.G_MISS_NUM) THEN
2386 l_talv_rec.depreciation_cost := NULL;
2387 END IF;
2388 IF (l_talv_rec.deprn_method = OKC_API.G_MISS_CHAR) THEN
2389 l_talv_rec.deprn_method := NULL;
2390 END IF;
2391 IF (l_talv_rec.deprn_rate = OKC_API.G_MISS_NUM) THEN
2392 l_talv_rec.deprn_rate := NULL;
2393 END IF;
2394 IF (l_talv_rec.salvage_value = OKC_API.G_MISS_NUM) THEN
2395 l_talv_rec.salvage_value := NULL;
2396 END IF;
2397 IF (l_talv_rec.percent_salvage_value = OKC_API.G_MISS_NUM) THEN
2398 l_talv_rec.percent_salvage_value := NULL;
2399 END IF;
2400 --Bug# 2981308
2401 IF (l_talv_rec.asset_key_id = OKL_API.G_MISS_NUM) THEN
2402 l_talv_rec.asset_key_id := NULL;
2403 END IF;
2404 -- Bug# 4028371
2405 IF (l_talv_rec.fa_trx_date = OKL_API.G_MISS_DATE) THEN
2406 l_talv_rec.fa_trx_date := NULL;
2407 END IF;
2408 --Bug# 4899328
2409 IF (l_talv_rec.fa_cost = OKL_API.G_MISS_NUM) THEN
2410 l_talv_rec.fa_cost := NULL;
2411 END IF;
2412 IF (l_talv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
2413 l_talv_rec.attribute_category := NULL;
2414 END IF;
2415 IF (l_talv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
2416 l_talv_rec.attribute1 := NULL;
2417 END IF;
2418 IF (l_talv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
2419 l_talv_rec.attribute2 := NULL;
2420 END IF;
2421 IF (l_talv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
2422 l_talv_rec.attribute3 := NULL;
2423 END IF;
2424 IF (l_talv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
2425 l_talv_rec.attribute4 := NULL;
2426 END IF;
2427 IF (l_talv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
2428 l_talv_rec.attribute5 := NULL;
2429 END IF;
2430 IF (l_talv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
2431 l_talv_rec.attribute6 := NULL;
2432 END IF;
2433 IF (l_talv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
2434 l_talv_rec.attribute7 := NULL;
2435 END IF;
2436 IF (l_talv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
2437 l_talv_rec.attribute8 := NULL;
2438 END IF;
2439 IF (l_talv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
2440 l_talv_rec.attribute9 := NULL;
2441 END IF;
2442 IF (l_talv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
2443 l_talv_rec.attribute10 := NULL;
2444 END IF;
2445 IF (l_talv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
2446 l_talv_rec.attribute11 := NULL;
2447 END IF;
2448 IF (l_talv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
2449 l_talv_rec.attribute12 := NULL;
2450 END IF;
2451 IF (l_talv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
2452 l_talv_rec.attribute13 := NULL;
2453 END IF;
2454 IF (l_talv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
2455 l_talv_rec.attribute14 := NULL;
2456 END IF;
2457 IF (l_talv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
2458 l_talv_rec.attribute15 := NULL;
2459 END IF;
2460 IF (l_talv_rec.created_by = OKC_API.G_MISS_NUM) THEN
2461 l_talv_rec.created_by := NULL;
2462 END IF;
2463 IF (l_talv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
2464 l_talv_rec.creation_date := NULL;
2465 END IF;
2466 IF (l_talv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
2467 l_talv_rec.last_updated_by := NULL;
2468 END IF;
2469 IF (l_talv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
2470 l_talv_rec.last_update_date := NULL;
2471 END IF;
2472 IF (l_talv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
2473 l_talv_rec.last_update_login := NULL;
2474 END IF;
2475 IF (l_talv_rec.depreciate_yn = OKC_API.G_MISS_CHAR) THEN
2476 l_talv_rec.depreciate_yn := NULL;
2477 END IF;
2478 IF (l_talv_rec.hold_period_days = OKC_API.G_MISS_NUM) THEN
2479 l_talv_rec.hold_period_days := NULL;
2480 END IF;
2481 IF (l_talv_rec.old_salvage_value = OKC_API.G_MISS_NUM) THEN
2482 l_talv_rec.old_salvage_value := NULL;
2483 END IF;
2484 IF (l_talv_rec.new_residual_value = OKC_API.G_MISS_NUM) THEN
2485 l_talv_rec.new_residual_value := NULL;
2486 END IF;
2487 IF (l_talv_rec.old_residual_value = OKC_API.G_MISS_NUM) THEN
2488 l_talv_rec.old_residual_value := NULL;
2489 END IF;
2490 IF (l_talv_rec.units_retired = OKC_API.G_MISS_NUM) THEN
2491 l_talv_rec.units_retired := NULL;
2492 END IF;
2493 IF (l_talv_rec.cost_retired = OKC_API.G_MISS_NUM) THEN
2494 l_talv_rec.cost_retired := NULL;
2495 END IF;
2496 IF (l_talv_rec.sale_proceeds = OKC_API.G_MISS_NUM) THEN
2497 l_talv_rec.sale_proceeds := NULL;
2498 END IF;
2499 IF (l_talv_rec.removal_cost = OKC_API.G_MISS_NUM) THEN
2500 l_talv_rec.removal_cost := NULL;
2501 END IF;
2502 IF (l_talv_rec.dnz_asset_id = OKC_API.G_MISS_NUM) THEN
2503 l_talv_rec.dnz_asset_id := NULL;
2504 END IF;
2505 IF (l_talv_rec.date_due = OKC_API.G_MISS_DATE) THEN
2506 l_talv_rec.date_due := NULL;
2507 END IF;
2508 IF (l_talv_rec.rep_asset_id = OKC_API.G_MISS_NUM) THEN
2509 l_talv_rec.rep_asset_id := NULL;
2510 END IF;
2511 IF (l_talv_rec.lke_asset_id = OKC_API.G_MISS_NUM) THEN
2512 l_talv_rec.lke_asset_id := NULL;
2513 END IF;
2514 IF (l_talv_rec.match_amount = OKC_API.G_MISS_NUM) THEN
2515 l_talv_rec.match_amount := NULL;
2516 END IF;
2517 IF (l_talv_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR) THEN
2518 l_talv_rec.split_into_singles_flag := NULL;
2519 END IF;
2520 IF (l_talv_rec.split_into_units = OKC_API.G_MISS_NUM) THEN
2521 l_talv_rec.split_into_units := NULL;
2522 END IF;
2523
2524 -- Multi-Currency Change
2525 IF (l_talv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN
2526 l_talv_rec.currency_code := NULL;
2527 END IF;
2528 IF (l_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
2529 l_talv_rec.currency_conversion_type := NULL;
2530 END IF;
2531 IF (l_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
2532 l_talv_rec.currency_conversion_rate:= NULL;
2533 END IF;
2534 IF (l_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
2535 l_talv_rec.currency_conversion_date := NULL;
2536 END IF;
2537 -- Multi-Currency Change
2538
2539 -- VRS Project - START
2540
2541 IF (l_talv_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM) THEN
2542 l_talv_rec.RESIDUAL_SHR_PARTY_ID:= NULL;
2543 END IF;
2544 IF (l_talv_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM) THEN
2545 l_talv_rec.RESIDUAL_SHR_AMOUNT:= NULL;
2546 END IF;
2547 IF (l_talv_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM) THEN
2548 l_talv_rec.RETIREMENT_ID := NULL;
2549 END IF;
2550
2551 -- VRS Project - END
2552
2553 RETURN(l_talv_rec);
2554 END null_out_defaults;
2555 ---------------------------------------------------------------------------
2556 -- PROCEDURE Validate_Attributes
2557 ---------------------------------------------------------------------------
2558 ----------------------------------------------
2559 -- Validate_Attributes for:OKL_TXL_ASSETS_V --
2560 ----------------------------------------------
2561 FUNCTION Validate_Attributes (
2562 p_talv_rec IN talv_rec_type
2563 ) RETURN VARCHAR2 IS
2564 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2565 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2566 BEGIN
2567 IF p_talv_rec.id = OKC_API.G_MISS_NUM OR
2568 p_talv_rec.id IS NULL THEN
2569 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
2570 x_return_status := OKC_API.G_RET_STS_ERROR;
2571 ELSIF p_talv_rec.object_version_number = OKC_API.G_MISS_NUM OR
2572 p_talv_rec.object_version_number IS NULL THEN
2573 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
2574 x_return_status := OKC_API.G_RET_STS_ERROR;
2575 ELSIF p_talv_rec.line_number = OKC_API.G_MISS_NUM OR
2576 p_talv_rec.line_number IS NULL THEN
2577 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'line_number');
2578 x_return_status := OKC_API.G_RET_STS_ERROR;
2579 ELSIF p_talv_rec.asset_number = OKC_API.G_MISS_CHAR OR
2580 p_talv_rec.asset_number IS NULL THEN
2581 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Asset_number');
2582 x_return_status := OKC_API.G_RET_STS_ERROR;
2583 ELSIF p_talv_rec.original_cost = OKC_API.G_MISS_NUM OR
2584 p_talv_rec.original_cost IS NULL THEN
2585 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'original_cost');
2586 x_return_status := OKC_API.G_RET_STS_ERROR;
2587 END IF;
2588 /************************ HAND-CODED *********************************/
2589 -- Calling the validation procedures for attributes
2590 validate_tas_id(x_return_status => l_return_status,
2591 p_talv_rec => p_talv_rec);
2592 -- Store the Highest Degree of Error
2593 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2594 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2595 x_return_status := l_return_status;
2596 END IF;
2597 END IF;
2598 l_return_status := x_return_status;
2599 validate_ilo_id(x_return_status => l_return_status,
2600 p_talv_rec => p_talv_rec);
2601 -- Store the Highest Degree of Error
2602 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2603 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2604 x_return_status := l_return_status;
2605 END IF;
2606 END IF;
2607 l_return_status := x_return_status;
2608 validate_ilo_id_old(x_return_status => l_return_status,
2609 p_talv_rec => p_talv_rec);
2610 -- Store the Highest Degree of Error
2611 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2612 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2613 x_return_status := l_return_status;
2614 END IF;
2615 END IF;
2616 l_return_status := x_return_status;
2617 validate_iay_id(x_return_status => l_return_status,
2618 p_talv_rec => p_talv_rec);
2619 -- Store the Highest Degree of Error
2620 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2621 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2622 x_return_status := l_return_status;
2623 END IF;
2624 END IF;
2625 l_return_status := x_return_status;
2626 validate_iay_id_new(x_return_status => l_return_status,
2627 p_talv_rec => p_talv_rec);
2628 -- Store the Highest Degree of Error
2629 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2630 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2631 x_return_status := l_return_status;
2632 END IF;
2633 END IF;
2634 l_return_status := x_return_status;
2635 validate_kle_id(x_return_status => l_return_status,
2636 p_talv_rec => p_talv_rec);
2637 -- Store the Highest Degree of Error
2638 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2639 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2640 x_return_status := l_return_status;
2641 END IF;
2642 END IF;
2643 l_return_status := x_return_status;
2644 validate_tal_type(x_return_status => l_return_status,
2645 p_talv_rec => p_talv_rec);
2646 -- Store the Highest Degree of Error
2647 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2648 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2649 x_return_status := l_return_status;
2650 END IF;
2651 END IF;
2652 l_return_status := x_return_status;
2653 validate_org_id(x_return_status => l_return_status,
2654 p_talv_rec => p_talv_rec);
2655 -- Store the Highest Degree of Error
2656 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2657 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2658 x_return_status := l_return_status;
2659 END IF;
2660 END IF;
2661 l_return_status := x_return_status;
2662 validate_current_units(x_return_status => l_return_status,
2663 p_talv_rec => p_talv_rec);
2664 -- Store the Highest Degree of Error
2665 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2666 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2667 x_return_status := l_return_status;
2668 END IF;
2669 END IF;
2670 l_return_status := x_return_status;
2671 validate_used_asset_yn(x_return_status => l_return_status,
2672 p_talv_rec => p_talv_rec);
2673 -- Store the Highest Degree of Error
2674 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2675 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2676 x_return_status := l_return_status;
2677 END IF;
2678 END IF;
2679 l_return_status := x_return_status;
2680 validate_life_in_months(x_return_status => l_return_status,
2681 p_talv_rec => p_talv_rec);
2682 -- Store the Highest Degree of Error
2683 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2684 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2685 x_return_status := l_return_status;
2686 END IF;
2687 END IF;
2688 l_return_status := x_return_status;
2689 validate_deprn_id(x_return_status => l_return_status,
2690 p_talv_rec => p_talv_rec);
2691 -- Store the Highest Degree of Error
2692 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2693 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2694 x_return_status := l_return_status;
2695 END IF;
2696 END IF;
2697 l_return_status := x_return_status;
2698 validate_fa_location_id(x_return_status => l_return_status,
2699 p_talv_rec => p_talv_rec);
2700 -- Store the Highest Degree of Error
2701 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2702 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2703 x_return_status := l_return_status;
2704 END IF;
2705 END IF;
2706 l_return_status := x_return_status;
2707 validate_dnz_khr_id(x_return_status => l_return_status,
2708 p_talv_rec => p_talv_rec);
2709 -- Store the Highest Degree of Error
2710 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2711 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2712 x_return_status := l_return_status;
2713 END IF;
2714 END IF;
2715 l_return_status := x_return_status;
2716 validate_corp_book(x_return_status => l_return_status,
2717 p_talv_rec => p_talv_rec);
2718 -- Store the Highest Degree of Error
2719 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2720 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2721 x_return_status := l_return_status;
2722 END IF;
2723 END IF;
2724 l_return_status := x_return_status;
2725 validate_deprn_method(x_return_status => l_return_status,
2726 p_talv_rec => p_talv_rec);
2727 --Bug# 2981308
2728 validate_asset_key (x_return_status => l_return_status,
2729 p_talv_rec => p_talv_rec);
2730
2731 -- Store the Highest Degree of Error
2732 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2733 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2734 x_return_status := l_return_status;
2735 END IF;
2736 END IF;
2737 l_return_status := x_return_status;
2738 RETURN(l_return_status);
2739 EXCEPTION
2740 WHEN OTHERS THEN
2741 -- store SQL error message on message stack
2742 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2743 p_msg_name => G_UNEXPECTED_ERROR,
2744 p_token1 => G_SQLCODE_TOKEN,
2745 p_token1_value => SQLCODE,
2746 p_token2 => G_SQLERRM_TOKEN,
2747 p_token2_value => SQLERRM);
2748 -- notify caller of an error as UNEXPETED error
2749 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2750 -- Return status to caller
2751 RETURN(x_return_status);
2752 /************************ HAND-CODED *********************************/
2753 END Validate_Attributes;
2754
2755 ---------------------------------------------------------------------------
2756 -- PROCEDURE Validate_Record
2757 ---------------------------------------------------------------------------
2758 ------------------------------------------
2759 -- Validate_Record for:OKL_TXL_ASSETS_V --
2760 ------------------------------------------
2761 FUNCTION Validate_Record (
2762 p_talv_rec IN talv_rec_type
2763 ) RETURN VARCHAR2 IS
2764 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2765 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2766 BEGIN
2767 /************************ HAND-CODED *********************************/
2768 validate_pds_date(x_return_status => l_return_status,
2769 p_talv_rec => p_talv_rec);
2770 -- Store the Highest Degree of Error
2771 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2772 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2773 x_return_status := l_return_status;
2774 END IF;
2775 END IF;
2776 l_return_status := x_return_status;
2777 --start:| 23-May-2008 cklee fixed bug: 6781324 |
2778 -- move this check to QA checker
2779 /*
2780 validate_salv_oec(x_return_status => l_return_status,
2781 p_talv_rec => p_talv_rec);
2782 -- Store the Highest Degree of Error
2783 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2784 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2785 x_return_status := l_return_status;
2786 END IF;
2787 END IF;
2788 */
2789 --end:| 23-May-2008 cklee fixed bug: 6781324 |
2790 l_return_status := x_return_status;
2791 RETURN (l_return_status);
2792 EXCEPTION
2793 WHEN OTHERS THEN
2794 -- store SQL error message on message stack
2795 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2796 p_msg_name => G_UNEXPECTED_ERROR,
2797 p_token1 => G_SQLCODE_TOKEN,
2798 p_token1_value => SQLCODE,
2799 p_token2 => G_SQLERRM_TOKEN,
2800 p_token2_value => SQLERRM);
2801 -- notify caller of an error as UNEXPETED error
2802 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2803 -- Return status to caller
2804 RETURN(x_return_status);
2805 /************************ HAND-CODED *********************************/
2806 END Validate_Record;
2807
2808 ---------------------------------------------------------------------------
2809 -- PROCEDURE Migrate
2810 ---------------------------------------------------------------------------
2811 PROCEDURE migrate (
2812 p_from IN talv_rec_type,
2813 p_to IN OUT NOCOPY tal_rec_type
2814 ) IS
2815 BEGIN
2816 p_to.id := p_from.id;
2817 p_to.object_version_number := p_from.object_version_number;
2818 p_to.tas_id := p_from.tas_id;
2819 p_to.ilo_id := p_from.ilo_id;
2820 p_to.ilo_id_old := p_from.ilo_id_old;
2821 p_to.iay_id := p_from.iay_id;
2822 p_to.iay_id_new := p_from.iay_id_new;
2823 p_to.kle_id := p_from.kle_id;
2824 p_to.dnz_khr_id := p_from.dnz_khr_id;
2825 p_to.line_number := p_from.line_number;
2826 p_to.org_id := p_from.org_id;
2827 p_to.tal_type := p_from.tal_type;
2828 p_to.asset_number := p_from.asset_number;
2829 p_to.fa_location_id := p_from.fa_location_id;
2830 p_to.original_cost := p_from.original_cost;
2831 p_to.current_units := p_from.current_units;
2832 p_to.manufacturer_name := p_from.manufacturer_name;
2833 p_to.year_manufactured := p_from.year_manufactured;
2834 p_to.supplier_id := p_from.supplier_id;
2835 p_to.used_asset_yn := p_from.used_asset_yn;
2836 p_to.tag_number := p_from.tag_number;
2837 p_to.model_number := p_from.model_number;
2838 p_to.corporate_book := p_from.corporate_book;
2839 p_to.date_purchased := p_from.date_purchased;
2840 p_to.date_delivery := p_from.date_delivery;
2841 p_to.in_service_date := p_from.in_service_date;
2842 p_to.life_in_months := p_from.life_in_months;
2843 p_to.depreciation_id := p_from.depreciation_id;
2844 p_to.depreciation_cost := p_from.depreciation_cost;
2845 p_to.deprn_method := p_from.deprn_method;
2846 p_to.deprn_rate := p_from.deprn_rate;
2847 p_to.salvage_value := p_from.salvage_value;
2848 p_to.percent_salvage_value := p_from.percent_salvage_value;
2849 --Bug# 2981308
2850 p_to.asset_key_id := p_from.asset_key_id;
2851 -- Bug# 4028371
2852 p_to.fa_trx_date := p_from.fa_trx_date;
2853 --Bug# 4899328
2854 p_to.fa_cost := p_from.fa_cost;
2855 p_to.attribute_category := p_from.attribute_category;
2856 p_to.attribute1 := p_from.attribute1;
2857 p_to.attribute2 := p_from.attribute2;
2858 p_to.attribute3 := p_from.attribute3;
2859 p_to.attribute4 := p_from.attribute4;
2860 p_to.attribute5 := p_from.attribute5;
2861 p_to.attribute6 := p_from.attribute6;
2862 p_to.attribute7 := p_from.attribute7;
2863 p_to.attribute8 := p_from.attribute8;
2864 p_to.attribute9 := p_from.attribute9;
2865 p_to.attribute10 := p_from.attribute10;
2866 p_to.attribute11 := p_from.attribute11;
2867 p_to.attribute12 := p_from.attribute12;
2868 p_to.attribute13 := p_from.attribute13;
2869 p_to.attribute14 := p_from.attribute14;
2870 p_to.attribute15 := p_from.attribute15;
2871 p_to.created_by := p_from.created_by;
2872 p_to.creation_date := p_from.creation_date;
2873 p_to.last_updated_by := p_from.last_updated_by;
2874 p_to.last_update_date := p_from.last_update_date;
2875 p_to.last_update_login := p_from.last_update_login;
2876 p_to.depreciate_yn := p_from.depreciate_yn;
2877 p_to.hold_period_days := p_from.hold_period_days;
2878 p_to.old_salvage_value := p_from.old_salvage_value;
2879 p_to.new_residual_value := p_from.new_residual_value;
2880 p_to.old_residual_value := p_from.old_residual_value;
2881 p_to.units_retired := p_from.units_retired;
2882 p_to.cost_retired := p_from.cost_retired;
2883 p_to.sale_proceeds := p_from.sale_proceeds;
2884 p_to.removal_cost := p_from.removal_cost;
2885 p_to.dnz_asset_id := p_from.dnz_asset_id;
2886 p_to.date_due := p_from.date_due;
2887 p_to.rep_asset_id := p_from.rep_asset_id;
2888 p_to.lke_asset_id := p_from.lke_asset_id;
2889 p_to.match_amount := p_from.match_amount;
2890 p_to.split_into_singles_flag := p_from.split_into_singles_flag;
2891 p_to.split_into_units := p_from.split_into_units;
2892 -- Multi-Currency Change
2893 p_to.currency_code := p_from.currency_code;
2894 p_to.currency_conversion_type := p_from.currency_conversion_type;
2895 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2896 p_to.currency_conversion_date := p_from.currency_conversion_date;
2897 -- Multi-Currency Change
2898 -- VRS Project - START
2899 p_to.RESIDUAL_SHR_PARTY_ID := p_from.RESIDUAL_SHR_PARTY_ID;
2900 p_to.RESIDUAL_SHR_AMOUNT := p_from.RESIDUAL_SHR_AMOUNT;
2901 p_to.RETIREMENT_ID := p_from.RETIREMENT_ID;
2902 -- VRS Project - END
2903
2904 END migrate;
2905 PROCEDURE migrate (
2906 p_from IN tal_rec_type,
2907 p_to IN OUT NOCOPY talv_rec_type
2908 ) IS
2909 BEGIN
2910 p_to.id := p_from.id;
2911 p_to.object_version_number := p_from.object_version_number;
2912 p_to.tas_id := p_from.tas_id;
2913 p_to.ilo_id := p_from.ilo_id;
2914 p_to.ilo_id_old := p_from.ilo_id_old;
2915 p_to.iay_id := p_from.iay_id;
2916 p_to.iay_id_new := p_from.iay_id_new;
2917 p_to.kle_id := p_from.kle_id;
2918 p_to.dnz_khr_id := p_from.dnz_khr_id;
2919 p_to.line_number := p_from.line_number;
2920 p_to.org_id := p_from.org_id;
2921 p_to.tal_type := p_from.tal_type;
2922 p_to.asset_number := p_from.asset_number;
2923 p_to.fa_location_id := p_from.fa_location_id;
2924 p_to.original_cost := p_from.original_cost;
2925 p_to.current_units := p_from.current_units;
2926 p_to.manufacturer_name := p_from.manufacturer_name;
2927 p_to.year_manufactured := p_from.year_manufactured;
2928 p_to.supplier_id := p_from.supplier_id;
2929 p_to.used_asset_yn := p_from.used_asset_yn;
2930 p_to.tag_number := p_from.tag_number;
2931 p_to.model_number := p_from.model_number;
2932 p_to.corporate_book := p_from.corporate_book;
2933 p_to.date_purchased := p_from.date_purchased;
2934 p_to.date_delivery := p_from.date_delivery;
2935 p_to.in_service_date := p_from.in_service_date;
2936 p_to.life_in_months := p_from.life_in_months;
2937 p_to.depreciation_id := p_from.depreciation_id;
2938 p_to.depreciation_cost := p_from.depreciation_cost;
2939 p_to.deprn_method := p_from.deprn_method;
2940 p_to.deprn_rate := p_from.deprn_rate;
2941 p_to.salvage_value := p_from.salvage_value;
2942 p_to.percent_salvage_value := p_from.percent_salvage_value;
2943 --Bug# 2981308 :
2944 p_to.asset_key_id := p_from.asset_key_id;
2945 -- Bug# 4028371
2946 p_to.fa_trx_date := p_from.fa_trx_date;
2947 --bug# 4899328
2948 p_to.fa_cost := p_from.fa_cost;
2949 p_to.attribute_category := p_from.attribute_category;
2950 p_to.attribute1 := p_from.attribute1;
2951 p_to.attribute2 := p_from.attribute2;
2952 p_to.attribute3 := p_from.attribute3;
2953 p_to.attribute4 := p_from.attribute4;
2954 p_to.attribute5 := p_from.attribute5;
2955 p_to.attribute6 := p_from.attribute6;
2956 p_to.attribute7 := p_from.attribute7;
2957 p_to.attribute8 := p_from.attribute8;
2958 p_to.attribute9 := p_from.attribute9;
2959 p_to.attribute10 := p_from.attribute10;
2960 p_to.attribute11 := p_from.attribute11;
2961 p_to.attribute12 := p_from.attribute12;
2962 p_to.attribute13 := p_from.attribute13;
2963 p_to.attribute14 := p_from.attribute14;
2964 p_to.attribute15 := p_from.attribute15;
2965 p_to.created_by := p_from.created_by;
2966 p_to.creation_date := p_from.creation_date;
2967 p_to.last_updated_by := p_from.last_updated_by;
2968 p_to.last_update_date := p_from.last_update_date;
2969 p_to.last_update_login := p_from.last_update_login;
2970 p_to.depreciate_yn := p_from.depreciate_yn;
2971 p_to.hold_period_days := p_from.hold_period_days;
2972 p_to.old_salvage_value := p_from.old_salvage_value;
2973 p_to.new_residual_value := p_from.new_residual_value;
2974 p_to.old_residual_value := p_from.old_residual_value;
2975 p_to.units_retired := p_from.units_retired;
2976 p_to.cost_retired := p_from.cost_retired;
2977 p_to.sale_proceeds := p_from.sale_proceeds;
2978 p_to.removal_cost := p_from.removal_cost;
2979 p_to.dnz_asset_id := p_from.dnz_asset_id;
2980 p_to.date_due := p_from.date_due;
2981 p_to.rep_asset_id := p_from.rep_asset_id;
2982 p_to.lke_asset_id := p_from.lke_asset_id;
2983 p_to.match_amount := p_from.match_amount;
2984 p_to.split_into_singles_flag := p_from.split_into_singles_flag;
2985 p_to.split_into_units := p_from.split_into_units;
2986 -- Multi-Currency Change
2987 p_to.currency_code := p_from.currency_code;
2988 p_to.currency_conversion_type := p_from.currency_conversion_type;
2989 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2990 p_to.currency_conversion_date := p_from.currency_conversion_date;
2991 -- Multi-Currency Change
2992 -- VRS Project - START
2993 p_to.RESIDUAL_SHR_PARTY_ID := p_from.RESIDUAL_SHR_PARTY_ID;
2994 p_to.RESIDUAL_SHR_AMOUNT := p_from.RESIDUAL_SHR_AMOUNT;
2995 p_to.RETIREMENT_ID := p_from.RETIREMENT_ID;
2996 -- VRS Project - END
2997
2998 END migrate;
2999 PROCEDURE migrate (
3000 p_from IN talv_rec_type,
3001 p_to IN OUT NOCOPY okl_txl_assets_tl_rec_type
3002 ) IS
3003 BEGIN
3004 p_to.id := p_from.id;
3005 p_to.sfwt_flag := p_from.sfwt_flag;
3006 p_to.description := p_from.description;
3007 p_to.created_by := p_from.created_by;
3008 p_to.creation_date := p_from.creation_date;
3009 p_to.last_updated_by := p_from.last_updated_by;
3010 p_to.last_update_date := p_from.last_update_date;
3011 p_to.last_update_login := p_from.last_update_login;
3012 END migrate;
3013 PROCEDURE migrate (
3014 p_from IN okl_txl_assets_tl_rec_type,
3015 p_to IN OUT NOCOPY talv_rec_type
3016 ) IS
3017 BEGIN
3018 p_to.id := p_from.id;
3019 p_to.sfwt_flag := p_from.sfwt_flag;
3020 p_to.description := p_from.description;
3021 p_to.created_by := p_from.created_by;
3022 p_to.creation_date := p_from.creation_date;
3023 p_to.last_updated_by := p_from.last_updated_by;
3024 p_to.last_update_date := p_from.last_update_date;
3025 p_to.last_update_login := p_from.last_update_login;
3026 END migrate;
3027
3028 ---------------------------------------------------------------------------
3029 -- PROCEDURE validate_row
3030 ---------------------------------------------------------------------------
3031 ---------------------------------------
3032 -- validate_row for:OKL_TXL_ASSETS_V --
3033 ---------------------------------------
3034 PROCEDURE validate_row(
3035 p_api_version IN NUMBER,
3036 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3037 x_return_status OUT NOCOPY VARCHAR2,
3038 x_msg_count OUT NOCOPY NUMBER,
3039 x_msg_data OUT NOCOPY VARCHAR2,
3040 p_talv_rec IN talv_rec_type) IS
3041
3042 l_api_version CONSTANT NUMBER := 1;
3043 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
3044 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3045 l_talv_rec talv_rec_type := p_talv_rec;
3046 l_tal_rec tal_rec_type;
3047 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3048 BEGIN
3049 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3050 G_PKG_NAME,
3051 p_init_msg_list,
3052 l_api_version,
3053 p_api_version,
3054 '_PVT',
3055 x_return_status);
3056 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3057 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3058 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3059 RAISE OKC_API.G_EXCEPTION_ERROR;
3060 END IF;
3061 --- Validate all non-missing attributes (Item Level Validation)
3062 l_return_status := Validate_Attributes(l_talv_rec);
3063 --- If any errors happen abort API
3064 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3066 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3067 RAISE OKC_API.G_EXCEPTION_ERROR;
3068 END IF;
3069 l_return_status := Validate_Record(l_talv_rec);
3070 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3071 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3072 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3073 RAISE OKC_API.G_EXCEPTION_ERROR;
3074 END IF;
3075 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3076 EXCEPTION
3077 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3078 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3079 (
3080 l_api_name,
3081 G_PKG_NAME,
3082 'OKC_API.G_RET_STS_ERROR',
3083 x_msg_count,
3084 x_msg_data,
3085 '_PVT'
3086 );
3087 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3088 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3089 (
3090 l_api_name,
3091 G_PKG_NAME,
3092 'OKC_API.G_RET_STS_UNEXP_ERROR',
3093 x_msg_count,
3094 x_msg_data,
3095 '_PVT'
3096 );
3097 WHEN OTHERS THEN
3098 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3099 (
3100 l_api_name,
3101 G_PKG_NAME,
3102 'OTHERS',
3103 x_msg_count,
3104 x_msg_data,
3105 '_PVT'
3106 );
3107 END validate_row;
3108 ------------------------------------------
3109 -- PL/SQL TBL validate_row for:TALV_TBL --
3110 ------------------------------------------
3111 PROCEDURE validate_row(
3112 p_api_version IN NUMBER,
3113 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3114 x_return_status OUT NOCOPY VARCHAR2,
3115 x_msg_count OUT NOCOPY NUMBER,
3116 x_msg_data OUT NOCOPY VARCHAR2,
3117 p_talv_tbl IN talv_tbl_type) IS
3118
3119 l_api_version CONSTANT NUMBER := 1;
3120 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
3121 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3122 i NUMBER := 0;
3123 BEGIN
3124 OKC_API.init_msg_list(p_init_msg_list);
3125 -- Make sure PL/SQL table has records in it before passing
3126 IF (p_talv_tbl.COUNT > 0) THEN
3127 i := p_talv_tbl.FIRST;
3128 LOOP
3129 validate_row (
3130 p_api_version => p_api_version,
3131 p_init_msg_list => OKC_API.G_FALSE,
3132 x_return_status => x_return_status,
3133 x_msg_count => x_msg_count,
3134 x_msg_data => x_msg_data,
3135 p_talv_rec => p_talv_tbl(i));
3136 EXIT WHEN (i = p_talv_tbl.LAST);
3137 i := p_talv_tbl.NEXT(i);
3138 END LOOP;
3139 END IF;
3140 EXCEPTION
3141 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3142 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3143 (
3144 l_api_name,
3145 G_PKG_NAME,
3146 'OKC_API.G_RET_STS_ERROR',
3147 x_msg_count,
3148 x_msg_data,
3149 '_PVT'
3150 );
3151 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3152 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3153 (
3154 l_api_name,
3155 G_PKG_NAME,
3156 'OKC_API.G_RET_STS_UNEXP_ERROR',
3157 x_msg_count,
3158 x_msg_data,
3159 '_PVT'
3160 );
3161 WHEN OTHERS THEN
3162 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3163 (
3164 l_api_name,
3165 G_PKG_NAME,
3166 'OTHERS',
3167 x_msg_count,
3168 x_msg_data,
3169 '_PVT'
3170 );
3171 END validate_row;
3172
3173 ---------------------------------------------------------------------------
3174 -- PROCEDURE insert_row
3175 ---------------------------------------------------------------------------
3176 -------------------------------------
3177 -- insert_row for:OKL_TXL_ASSETS_B --
3178 -------------------------------------
3179 PROCEDURE insert_row(
3180 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3181 x_return_status OUT NOCOPY VARCHAR2,
3182 x_msg_count OUT NOCOPY NUMBER,
3183 x_msg_data OUT NOCOPY VARCHAR2,
3184 p_tal_rec IN tal_rec_type,
3185 x_tal_rec OUT NOCOPY tal_rec_type) IS
3186
3187 l_api_version CONSTANT NUMBER := 1;
3188 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
3189 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3190 l_tal_rec tal_rec_type := p_tal_rec;
3191 l_def_tal_rec tal_rec_type;
3192 -----------------------------------------
3193 -- Set_Attributes for:OKL_TXL_ASSETS_B --
3194 -----------------------------------------
3195 FUNCTION Set_Attributes (
3196 p_tal_rec IN tal_rec_type,
3197 x_tal_rec OUT NOCOPY tal_rec_type
3198 ) RETURN VARCHAR2 IS
3199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3200 BEGIN
3201 x_tal_rec := p_tal_rec;
3202 RETURN(l_return_status);
3203 END Set_Attributes;
3204 BEGIN
3205 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3206 p_init_msg_list,
3207 '_PVT',
3208 x_return_status);
3209 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3210 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3211 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3212 RAISE OKC_API.G_EXCEPTION_ERROR;
3213 END IF;
3214 --- Setting item attributes
3215 l_return_status := Set_Attributes(
3216 p_tal_rec, -- IN
3217 l_tal_rec); -- OUT
3218 --- If any errors happen abort API
3219 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3220 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3221 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3222 RAISE OKC_API.G_EXCEPTION_ERROR;
3223 END IF;
3224 INSERT INTO OKL_TXL_ASSETS_B(
3225 id,
3226 object_version_number,
3227 tas_id,
3228 ilo_id,
3229 ilo_id_old,
3230 iay_id,
3231 iay_id_new,
3232 kle_id,
3233 dnz_khr_id,
3234 line_number,
3235 org_id,
3236 tal_type,
3237 asset_number,
3238 fa_location_Id,
3239 original_cost,
3240 current_units,
3241 manufacturer_name,
3242 year_manufactured,
3243 supplier_id,
3244 used_asset_yn,
3245 tag_number,
3246 model_number,
3247 corporate_book,
3248 date_purchased,
3249 date_delivery,
3250 in_service_date,
3251 life_in_months,
3252 depreciation_id,
3253 depreciation_cost,
3254 deprn_method,
3255 deprn_rate,
3256 salvage_value,
3257 percent_salvage_value,
3258 --Bug# 2981308
3259 asset_key_id,
3260 -- Bug# 4028371
3261 fa_trx_date,
3262 --Bug# 4899328
3263 fa_cost,
3264 attribute_category,
3265 attribute1,
3266 attribute2,
3267 attribute3,
3268 attribute4,
3269 attribute5,
3270 attribute6,
3271 attribute7,
3272 attribute8,
3273 attribute9,
3274 attribute10,
3275 attribute11,
3276 attribute12,
3277 attribute13,
3278 attribute14,
3279 attribute15,
3280 created_by,
3281 creation_date,
3282 last_updated_by,
3283 last_update_date,
3284 last_update_login,
3285 depreciate_yn,
3286 hold_period_days,
3287 old_salvage_value,
3288 new_residual_value,
3289 old_residual_value,
3290 units_retired,
3291 cost_retired,
3292 sale_proceeds,
3293 removal_cost,
3294 dnz_asset_id,
3295 date_due,
3296 rep_asset_id,
3297 lke_asset_id,
3298 match_amount,
3299 split_into_singles_flag,
3300 split_into_units,
3301 -- Multi-Currency Change
3302 currency_code,
3303 currency_conversion_type,
3304 currency_conversion_rate,
3305 currency_conversion_date,
3306 -- Multi-Currency Change
3307 -- VRS Project - END
3308 RESIDUAL_SHR_PARTY_ID,
3309 RESIDUAL_SHR_AMOUNT,
3310 RETIREMENT_ID
3311 -- VRS Project - END
3312 )
3313 VALUES (
3314 l_tal_rec.id,
3315 l_tal_rec.object_version_number,
3316 l_tal_rec.tas_id,
3317 l_tal_rec.ilo_id,
3318 l_tal_rec.ilo_id_old,
3319 l_tal_rec.iay_id,
3320 l_tal_rec.iay_id_new,
3321 l_tal_rec.kle_id,
3322 l_tal_rec.dnz_khr_id,
3323 l_tal_rec.line_number,
3324 l_tal_rec.org_id,
3325 l_tal_rec.tal_type,
3326 l_tal_rec.asset_number,
3327 l_tal_rec.fa_location_id,
3328 l_tal_rec.original_cost,
3329 l_tal_rec.current_units,
3330 l_tal_rec.manufacturer_name,
3331 l_tal_rec.year_manufactured,
3332 l_tal_rec.supplier_id,
3333 l_tal_rec.used_asset_yn,
3334 l_tal_rec.tag_number,
3335 l_tal_rec.model_number,
3336 l_tal_rec.corporate_book,
3337 l_tal_rec.date_purchased,
3338 l_tal_rec.date_delivery,
3339 l_tal_rec.in_service_date,
3340 l_tal_rec.life_in_months,
3341 l_tal_rec.depreciation_id,
3342 l_tal_rec.depreciation_cost,
3343 l_tal_rec.deprn_method,
3344 l_tal_rec.deprn_rate,
3345 l_tal_rec.salvage_value,
3346 l_tal_rec.percent_salvage_value,
3347 --Bug# 2981308
3348 l_tal_rec.asset_key_id,
3349 -- Bug# 4028371
3350 l_tal_rec.fa_trx_date,
3351 --bug# 4899328
3352 l_tal_rec.fa_cost,
3353 l_tal_rec.attribute_category,
3354 l_tal_rec.attribute1,
3355 l_tal_rec.attribute2,
3356 l_tal_rec.attribute3,
3357 l_tal_rec.attribute4,
3358 l_tal_rec.attribute5,
3359 l_tal_rec.attribute6,
3360 l_tal_rec.attribute7,
3361 l_tal_rec.attribute8,
3362 l_tal_rec.attribute9,
3363 l_tal_rec.attribute10,
3364 l_tal_rec.attribute11,
3365 l_tal_rec.attribute12,
3366 l_tal_rec.attribute13,
3367 l_tal_rec.attribute14,
3368 l_tal_rec.attribute15,
3369 l_tal_rec.created_by,
3370 l_tal_rec.creation_date,
3371 l_tal_rec.last_updated_by,
3372 l_tal_rec.last_update_date,
3373 l_tal_rec.last_update_login,
3374 l_tal_rec.depreciate_yn,
3375 l_tal_rec.hold_period_days,
3376 l_tal_rec.old_salvage_value,
3377 l_tal_rec.new_residual_value,
3378 l_tal_rec.old_residual_value,
3379 l_tal_rec.units_retired,
3380 l_tal_rec.cost_retired,
3381 l_tal_rec.sale_proceeds,
3382 l_tal_rec.removal_cost,
3383 l_tal_rec.dnz_asset_id,
3384 l_tal_rec.date_due,
3385 l_tal_rec.rep_asset_id,
3386 l_tal_rec.lke_asset_id,
3387 l_tal_rec.match_amount,
3388 l_tal_rec.split_into_singles_flag,
3389 l_tal_rec.split_into_units,
3390 -- Multi-Currency Change
3391 l_tal_rec.currency_code,
3392 l_tal_rec.currency_conversion_type,
3393 l_tal_rec.currency_conversion_rate,
3394 l_tal_rec.currency_conversion_date,
3395 -- Multi-Currency Change
3396 -- VRS Project - END
3397 l_tal_rec.RESIDUAL_SHR_PARTY_ID,
3398 l_tal_rec.RESIDUAL_SHR_AMOUNT,
3399 l_tal_rec.RETIREMENT_ID
3400 -- VRS Project - END
3401 );
3402
3403 -- Set OUT values
3404 x_tal_rec := l_tal_rec;
3405 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3406 EXCEPTION
3407 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3408 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3409 (
3410 l_api_name,
3411 G_PKG_NAME,
3412 'OKC_API.G_RET_STS_ERROR',
3413 x_msg_count,
3414 x_msg_data,
3415 '_PVT'
3416 );
3417 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3418 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3419 (
3420 l_api_name,
3421 G_PKG_NAME,
3422 'OKC_API.G_RET_STS_UNEXP_ERROR',
3423 x_msg_count,
3424 x_msg_data,
3425 '_PVT'
3426 );
3427 WHEN OTHERS THEN
3428 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3429 (
3430 l_api_name,
3431 G_PKG_NAME,
3432 'OTHERS',
3433 x_msg_count,
3434 x_msg_data,
3435 '_PVT'
3436 );
3437 END insert_row;
3438 --------------------------------------
3439 -- insert_row for:OKL_TXL_ASSETS_TL --
3440 --------------------------------------
3441 PROCEDURE insert_row(
3442 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3443 x_return_status OUT NOCOPY VARCHAR2,
3444 x_msg_count OUT NOCOPY NUMBER,
3445 x_msg_data OUT NOCOPY VARCHAR2,
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) IS
3448
3449 l_api_version CONSTANT NUMBER := 1;
3450 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3451 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3452 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type := p_okl_txl_assets_tl_rec;
3453 l_def_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3454 CURSOR get_languages IS
3455 SELECT *
3456 FROM FND_LANGUAGES
3457 WHERE INSTALLED_FLAG IN ('I', 'B');
3458 ------------------------------------------
3459 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
3460 ------------------------------------------
3461 FUNCTION Set_Attributes (
3462 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
3463 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
3464 ) RETURN VARCHAR2 IS
3465 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3466 BEGIN
3467 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
3468 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
3469 x_okl_txl_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
3470 RETURN(l_return_status);
3471 END Set_Attributes;
3472 BEGIN
3473 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3474 p_init_msg_list,
3475 '_PVT',
3476 x_return_status);
3477 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3478 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3479 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3480 RAISE OKC_API.G_EXCEPTION_ERROR;
3481 END IF;
3482 --- Setting item attributes
3483 l_return_status := Set_Attributes(
3484 p_okl_txl_assets_tl_rec, -- IN
3485 l_okl_txl_assets_tl_rec); -- OUT
3486 --- If any errors happen abort API
3487 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3488 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3489 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3490 RAISE OKC_API.G_EXCEPTION_ERROR;
3491 END IF;
3492 FOR l_lang_rec IN get_languages LOOP
3493 l_okl_txl_assets_tl_rec.language := l_lang_rec.language_code;
3494 INSERT INTO OKL_TXL_ASSETS_TL(
3495 id,
3496 language,
3497 source_lang,
3498 sfwt_flag,
3499 description,
3500 created_by,
3501 creation_date,
3502 last_updated_by,
3503 last_update_date,
3504 last_update_login)
3505 VALUES (
3506 l_okl_txl_assets_tl_rec.id,
3507 l_okl_txl_assets_tl_rec.language,
3508 l_okl_txl_assets_tl_rec.source_lang,
3509 l_okl_txl_assets_tl_rec.sfwt_flag,
3510 l_okl_txl_assets_tl_rec.description,
3511 l_okl_txl_assets_tl_rec.created_by,
3512 l_okl_txl_assets_tl_rec.creation_date,
3513 l_okl_txl_assets_tl_rec.last_updated_by,
3514 l_okl_txl_assets_tl_rec.last_update_date,
3515 l_okl_txl_assets_tl_rec.last_update_login);
3516 END LOOP;
3517 -- Set OUT values
3518 x_okl_txl_assets_tl_rec := l_okl_txl_assets_tl_rec;
3519 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3520 EXCEPTION
3521 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3522 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3523 (
3524 l_api_name,
3525 G_PKG_NAME,
3526 'OKC_API.G_RET_STS_ERROR',
3527 x_msg_count,
3528 x_msg_data,
3529 '_PVT'
3530 );
3531 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3532 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3533 (
3534 l_api_name,
3535 G_PKG_NAME,
3536 'OKC_API.G_RET_STS_UNEXP_ERROR',
3537 x_msg_count,
3538 x_msg_data,
3539 '_PVT'
3540 );
3541 WHEN OTHERS THEN
3542 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3543 (
3544 l_api_name,
3545 G_PKG_NAME,
3546 'OTHERS',
3547 x_msg_count,
3548 x_msg_data,
3549 '_PVT'
3550 );
3551 END insert_row;
3552 -------------------------------------
3553 -- insert_row for:OKL_TXL_ASSETS_V --
3554 -------------------------------------
3555 PROCEDURE insert_row(
3556 p_api_version IN NUMBER,
3557 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3558 x_return_status OUT NOCOPY VARCHAR2,
3559 x_msg_count OUT NOCOPY NUMBER,
3560 x_msg_data OUT NOCOPY VARCHAR2,
3561 p_talv_rec IN talv_rec_type,
3562 x_talv_rec OUT NOCOPY talv_rec_type) IS
3563
3564 l_api_version CONSTANT NUMBER := 1;
3565 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3566 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3567 l_talv_rec talv_rec_type;
3568 l_def_talv_rec talv_rec_type;
3569 l_tal_rec tal_rec_type;
3570 lx_tal_rec tal_rec_type;
3571 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3572 lx_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
3573 lx_temp_talv_rec talv_rec_type;
3574 -------------------------------
3575 -- FUNCTION fill_who_columns --
3576 -------------------------------
3577 FUNCTION fill_who_columns (
3578 p_talv_rec IN talv_rec_type
3579 ) RETURN talv_rec_type IS
3580 l_talv_rec talv_rec_type := p_talv_rec;
3581 BEGIN
3582 l_talv_rec.CREATION_DATE := SYSDATE;
3583 l_talv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3584 l_talv_rec.LAST_UPDATE_DATE := SYSDATE;
3585 l_talv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3586 l_talv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3587 RETURN(l_talv_rec);
3588 END fill_who_columns;
3589 -----------------------------------------
3590 -- Set_Attributes for:OKL_TXL_ASSETS_V --
3591 -----------------------------------------
3592 FUNCTION Set_Attributes (
3593 p_talv_rec IN talv_rec_type,
3594 x_talv_rec OUT NOCOPY talv_rec_type
3595 ) RETURN VARCHAR2 IS
3596 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3597 BEGIN
3598 x_talv_rec := p_talv_rec;
3599 x_talv_rec.OBJECT_VERSION_NUMBER := 1;
3600 x_talv_rec.SFWT_FLAG := 'N';
3601 RETURN(l_return_status);
3602 END Set_Attributes;
3603 BEGIN
3604 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3605 G_PKG_NAME,
3606 p_init_msg_list,
3607 l_api_version,
3608 p_api_version,
3609 '_PVT',
3610 x_return_status);
3611 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3613 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3614 RAISE OKC_API.G_EXCEPTION_ERROR;
3615 END IF;
3616 l_talv_rec := null_out_defaults(p_talv_rec);
3617 -- Set primary key value
3618 l_talv_rec.ID := get_seq_id;
3619 --- Setting item attributes
3620 l_return_status := Set_Attributes(
3621 l_talv_rec, -- IN
3622 l_def_talv_rec); -- OUT
3623 --- If any errors happen abort API
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 l_def_talv_rec := fill_who_columns(l_def_talv_rec);
3630 --- Validate all non-missing attributes (Item Level Validation)
3631 l_return_status := Validate_Attributes(l_def_talv_rec);
3632 --- If any errors happen abort API
3633
3634 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3635 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3636 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3637 RAISE OKC_API.G_EXCEPTION_ERROR;
3638 END IF;
3639 l_return_status := Validate_Record(l_def_talv_rec);
3640 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3641 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3642 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3643 RAISE OKC_API.G_EXCEPTION_ERROR;
3644 END IF;
3645
3646 --
3647 -- Multi-Currency Change, dedey, 12/04/2002
3648 --
3649 validate_currency(
3650 x_return_status => l_return_status,
3651 p_talv_rec => l_def_talv_rec,
3652 x_talv_rec => lx_temp_talv_rec
3653 );
3654
3655 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3656 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3657 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3658 RAISE OKC_API.G_EXCEPTION_ERROR;
3659 END IF;
3660 l_def_talv_rec := lx_temp_talv_rec;
3661
3662 --dbms_output.put_line('After Change: '||lx_temp_talv_rec.currency_code);
3663 --dbms_output.put_line('After Change: '||l_def_talv_rec.currency_code);
3664 --
3665 -- Multi-Currency Change
3666 --
3667
3668 -- Fix Bug# 2737014
3669 --
3670 -- Round off amounts
3671 --
3672 roundoff_line_amount(
3673 x_return_status => l_return_status,
3674 x_msg_count => x_msg_count,
3675 x_msg_data => x_msg_data,
3676 p_talv_rec => l_def_talv_rec,
3677 x_talv_rec => lx_temp_talv_rec
3678 );
3679
3680 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3681 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3682 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3683 RAISE OKC_API.G_EXCEPTION_ERROR;
3684 END IF;
3685 l_def_talv_rec := lx_temp_talv_rec;
3686
3687 --dbms_output.put_line('After Change Orig cost: '||lx_temp_talv_rec.original_cost);
3688 --dbms_output.put_line('After Change Orig cost: '||l_def_talv_rec.original_cost);
3689
3690
3691 --------------------------------------
3692 -- Move VIEW record to "Child" records
3693 --------------------------------------
3694 migrate(l_def_talv_rec, l_tal_rec);
3695 migrate(l_def_talv_rec, l_okl_txl_assets_tl_rec);
3696
3697 --dbms_output.put_line('After migrate: '||l_tal_rec.currency_code);
3698 --------------------------------------------
3699 -- Call the INSERT_ROW for each child record
3700 --------------------------------------------
3701 insert_row(
3702 p_init_msg_list,
3703 x_return_status,
3704 x_msg_count,
3705 x_msg_data,
3706 l_tal_rec,
3707 lx_tal_rec
3708 );
3709 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3710 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3711 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3712 RAISE OKC_API.G_EXCEPTION_ERROR;
3713 END IF;
3714 migrate(lx_tal_rec, l_def_talv_rec);
3715 insert_row(
3716 p_init_msg_list,
3717 x_return_status,
3718 x_msg_count,
3719 x_msg_data,
3720 l_okl_txl_assets_tl_rec,
3721 lx_okl_txl_assets_tl_rec
3722 );
3723 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3724 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3725 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3726 RAISE OKC_API.G_EXCEPTION_ERROR;
3727 END IF;
3728 migrate(lx_okl_txl_assets_tl_rec, l_def_talv_rec);
3729 -- Set OUT values
3730 x_talv_rec := l_def_talv_rec;
3731 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3732 EXCEPTION
3733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3734 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3735 (
3736 l_api_name,
3737 G_PKG_NAME,
3738 'OKC_API.G_RET_STS_ERROR',
3739 x_msg_count,
3740 x_msg_data,
3741 '_PVT'
3742 );
3743 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3744 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3745 (
3746 l_api_name,
3747 G_PKG_NAME,
3748 'OKC_API.G_RET_STS_UNEXP_ERROR',
3749 x_msg_count,
3750 x_msg_data,
3751 '_PVT'
3752 );
3753 WHEN OTHERS THEN
3754 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3755 (
3756 l_api_name,
3757 G_PKG_NAME,
3758 'OTHERS',
3759 x_msg_count,
3760 x_msg_data,
3761 '_PVT'
3762 );
3763 END insert_row;
3764 ----------------------------------------
3765 -- PL/SQL TBL insert_row for:TALV_TBL --
3766 ----------------------------------------
3767 PROCEDURE insert_row(
3768 p_api_version IN NUMBER,
3769 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3770 x_return_status OUT NOCOPY VARCHAR2,
3771 x_msg_count OUT NOCOPY NUMBER,
3772 x_msg_data OUT NOCOPY VARCHAR2,
3773 p_talv_tbl IN talv_tbl_type,
3774 x_talv_tbl OUT NOCOPY talv_tbl_type) IS
3775
3776 l_api_version CONSTANT NUMBER := 1;
3777 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3779 i NUMBER := 0;
3780 BEGIN
3781 OKC_API.init_msg_list(p_init_msg_list);
3782 -- Make sure PL/SQL table has records in it before passing
3783 IF (p_talv_tbl.COUNT > 0) THEN
3784 i := p_talv_tbl.FIRST;
3785 LOOP
3786 insert_row (
3787 p_api_version => p_api_version,
3788 p_init_msg_list => OKC_API.G_FALSE,
3789 x_return_status => x_return_status,
3790 x_msg_count => x_msg_count,
3791 x_msg_data => x_msg_data,
3792 p_talv_rec => p_talv_tbl(i),
3793 x_talv_rec => x_talv_tbl(i));
3794 EXIT WHEN (i = p_talv_tbl.LAST);
3795 i := p_talv_tbl.NEXT(i);
3796 END LOOP;
3797 END IF;
3798 EXCEPTION
3799 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3800 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3801 (
3802 l_api_name,
3803 G_PKG_NAME,
3804 'OKC_API.G_RET_STS_ERROR',
3805 x_msg_count,
3806 x_msg_data,
3807 '_PVT'
3808 );
3809 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3810 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3811 (
3812 l_api_name,
3813 G_PKG_NAME,
3814 'OKC_API.G_RET_STS_UNEXP_ERROR',
3815 x_msg_count,
3816 x_msg_data,
3817 '_PVT'
3818 );
3819 WHEN OTHERS THEN
3820 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3821 (
3822 l_api_name,
3823 G_PKG_NAME,
3824 'OTHERS',
3825 x_msg_count,
3826 x_msg_data,
3827 '_PVT'
3828 );
3829 END insert_row;
3830
3831 ---------------------------------------------------------------------------
3832 -- PROCEDURE lock_row
3833 ---------------------------------------------------------------------------
3834 -----------------------------------
3835 -- lock_row for:OKL_TXL_ASSETS_B --
3836 -----------------------------------
3837 PROCEDURE lock_row(
3838 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3839 x_return_status OUT NOCOPY VARCHAR2,
3840 x_msg_count OUT NOCOPY NUMBER,
3841 x_msg_data OUT NOCOPY VARCHAR2,
3842 p_tal_rec IN tal_rec_type) IS
3843
3844 E_Resource_Busy EXCEPTION;
3845 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3846 CURSOR lock_csr (p_tal_rec IN tal_rec_type) IS
3847 SELECT OBJECT_VERSION_NUMBER
3848 FROM OKL_TXL_ASSETS_B
3849 WHERE ID = p_tal_rec.id
3850 AND OBJECT_VERSION_NUMBER = p_tal_rec.object_version_number
3851 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3852
3853 CURSOR lchk_csr (p_tal_rec IN tal_rec_type) IS
3854 SELECT OBJECT_VERSION_NUMBER
3855 FROM OKL_TXL_ASSETS_B
3856 WHERE ID = p_tal_rec.id;
3857 l_api_version CONSTANT NUMBER := 1;
3858 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3859 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3860 l_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
3861 lc_object_version_number OKL_TXL_ASSETS_B.OBJECT_VERSION_NUMBER%TYPE;
3862 l_row_notfound BOOLEAN := FALSE;
3863 lc_row_notfound BOOLEAN := FALSE;
3864 BEGIN
3865 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3866 p_init_msg_list,
3867 '_PVT',
3868 x_return_status);
3869 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3870 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3871 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3872 RAISE OKC_API.G_EXCEPTION_ERROR;
3873 END IF;
3874 BEGIN
3875 OPEN lock_csr(p_tal_rec);
3876 FETCH lock_csr INTO l_object_version_number;
3877 l_row_notfound := lock_csr%NOTFOUND;
3878 CLOSE lock_csr;
3879 EXCEPTION
3880 WHEN E_Resource_Busy THEN
3881 IF (lock_csr%ISOPEN) THEN
3882 CLOSE lock_csr;
3883 END IF;
3884 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3885 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3886 END;
3887
3888 IF ( l_row_notfound ) THEN
3889 OPEN lchk_csr(p_tal_rec);
3890 FETCH lchk_csr INTO lc_object_version_number;
3891 lc_row_notfound := lchk_csr%NOTFOUND;
3892 CLOSE lchk_csr;
3893 END IF;
3894 IF (lc_row_notfound) THEN
3895 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3896 RAISE OKC_API.G_EXCEPTION_ERROR;
3897 ELSIF lc_object_version_number > p_tal_rec.object_version_number THEN
3898 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3899 RAISE OKC_API.G_EXCEPTION_ERROR;
3900 ELSIF lc_object_version_number <> p_tal_rec.object_version_number THEN
3901 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3902 RAISE OKC_API.G_EXCEPTION_ERROR;
3903 ELSIF lc_object_version_number = -1 THEN
3904 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3905 RAISE OKC_API.G_EXCEPTION_ERROR;
3906 END IF;
3907 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3908 EXCEPTION
3909 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3910 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3911 (
3912 l_api_name,
3913 G_PKG_NAME,
3914 'OKC_API.G_RET_STS_ERROR',
3915 x_msg_count,
3916 x_msg_data,
3917 '_PVT'
3918 );
3919 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3920 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3921 (
3922 l_api_name,
3923 G_PKG_NAME,
3924 'OKC_API.G_RET_STS_UNEXP_ERROR',
3925 x_msg_count,
3926 x_msg_data,
3927 '_PVT'
3928 );
3929 WHEN OTHERS THEN
3930 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3931 (
3932 l_api_name,
3933 G_PKG_NAME,
3934 'OTHERS',
3935 x_msg_count,
3936 x_msg_data,
3937 '_PVT'
3938 );
3939 END lock_row;
3940 ------------------------------------
3941 -- lock_row for:OKL_TXL_ASSETS_TL --
3942 ------------------------------------
3943 PROCEDURE lock_row(
3944 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3945 x_return_status OUT NOCOPY VARCHAR2,
3946 x_msg_count OUT NOCOPY NUMBER,
3947 x_msg_data OUT NOCOPY VARCHAR2,
3948 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
3949
3950 E_Resource_Busy EXCEPTION;
3951 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3952 CURSOR lock_csr (p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
3953 SELECT *
3954 FROM OKL_TXL_ASSETS_TL
3955 WHERE ID = p_okl_txl_assets_tl_rec.id
3956 FOR UPDATE NOWAIT;
3957
3958 l_api_version CONSTANT NUMBER := 1;
3959 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3960 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3961 l_lock_var lock_csr%ROWTYPE;
3962 l_row_notfound BOOLEAN := FALSE;
3963 lc_row_notfound BOOLEAN := FALSE;
3964 BEGIN
3965 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3966 p_init_msg_list,
3967 '_PVT',
3968 x_return_status);
3969 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3970 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3971 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3972 RAISE OKC_API.G_EXCEPTION_ERROR;
3973 END IF;
3974 BEGIN
3975 OPEN lock_csr(p_okl_txl_assets_tl_rec);
3976 FETCH lock_csr INTO l_lock_var;
3977 l_row_notfound := lock_csr%NOTFOUND;
3978 CLOSE lock_csr;
3979 EXCEPTION
3980 WHEN E_Resource_Busy THEN
3981 IF (lock_csr%ISOPEN) THEN
3982 CLOSE lock_csr;
3983 END IF;
3984 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3985 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3986 END;
3987
3988 IF ( l_row_notfound ) THEN
3989 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3990 RAISE OKC_API.G_EXCEPTION_ERROR;
3991 END IF;
3992 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3993 EXCEPTION
3994 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3995 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3996 (
3997 l_api_name,
3998 G_PKG_NAME,
3999 'OKC_API.G_RET_STS_ERROR',
4000 x_msg_count,
4001 x_msg_data,
4002 '_PVT'
4003 );
4004 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4005 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4006 (
4007 l_api_name,
4008 G_PKG_NAME,
4009 'OKC_API.G_RET_STS_UNEXP_ERROR',
4010 x_msg_count,
4011 x_msg_data,
4012 '_PVT'
4013 );
4014 WHEN OTHERS THEN
4015 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4016 (
4017 l_api_name,
4018 G_PKG_NAME,
4019 'OTHERS',
4020 x_msg_count,
4021 x_msg_data,
4022 '_PVT'
4023 );
4024 END lock_row;
4025 -----------------------------------
4026 -- lock_row for:OKL_TXL_ASSETS_V --
4027 -----------------------------------
4028 PROCEDURE lock_row(
4029 p_api_version IN NUMBER,
4030 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4031 x_return_status OUT NOCOPY VARCHAR2,
4032 x_msg_count OUT NOCOPY NUMBER,
4033 x_msg_data OUT NOCOPY VARCHAR2,
4034 p_talv_rec IN talv_rec_type) IS
4035
4036 l_api_version CONSTANT NUMBER := 1;
4037 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
4038 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4039 l_tal_rec tal_rec_type;
4040 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4041 BEGIN
4042 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4043 G_PKG_NAME,
4044 p_init_msg_list,
4045 l_api_version,
4046 p_api_version,
4047 '_PVT',
4048 x_return_status);
4049 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4050 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4051 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4052 RAISE OKC_API.G_EXCEPTION_ERROR;
4053 END IF;
4054 --------------------------------------
4055 -- Move VIEW record to "Child" records
4056 --------------------------------------
4057 migrate(p_talv_rec, l_tal_rec);
4058 migrate(p_talv_rec, l_okl_txl_assets_tl_rec);
4059 --------------------------------------------
4060 -- Call the LOCK_ROW for each child record
4061 --------------------------------------------
4062 lock_row(
4063 p_init_msg_list,
4064 x_return_status,
4065 x_msg_count,
4066 x_msg_data,
4067 l_tal_rec
4068 );
4069 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4070 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4071 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4072 RAISE OKC_API.G_EXCEPTION_ERROR;
4073 END IF;
4074 lock_row(
4075 p_init_msg_list,
4076 x_return_status,
4077 x_msg_count,
4078 x_msg_data,
4079 l_okl_txl_assets_tl_rec
4080 );
4081 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4082 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4083 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4084 RAISE OKC_API.G_EXCEPTION_ERROR;
4085 END IF;
4086 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4087 EXCEPTION
4088 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4089 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4090 (
4091 l_api_name,
4092 G_PKG_NAME,
4093 'OKC_API.G_RET_STS_ERROR',
4094 x_msg_count,
4095 x_msg_data,
4096 '_PVT'
4097 );
4098 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4099 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4100 (
4101 l_api_name,
4102 G_PKG_NAME,
4103 'OKC_API.G_RET_STS_UNEXP_ERROR',
4104 x_msg_count,
4105 x_msg_data,
4106 '_PVT'
4107 );
4108 WHEN OTHERS THEN
4109 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4110 (
4111 l_api_name,
4112 G_PKG_NAME,
4113 'OTHERS',
4114 x_msg_count,
4115 x_msg_data,
4116 '_PVT'
4117 );
4118 END lock_row;
4119 --------------------------------------
4120 -- PL/SQL TBL lock_row for:TALV_TBL --
4121 --------------------------------------
4122 PROCEDURE lock_row(
4123 p_api_version IN NUMBER,
4124 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4125 x_return_status OUT NOCOPY VARCHAR2,
4126 x_msg_count OUT NOCOPY NUMBER,
4127 x_msg_data OUT NOCOPY VARCHAR2,
4128 p_talv_tbl IN talv_tbl_type) IS
4129
4130 l_api_version CONSTANT NUMBER := 1;
4131 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
4132 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4133 i NUMBER := 0;
4134 BEGIN
4135 OKC_API.init_msg_list(p_init_msg_list);
4136 -- Make sure PL/SQL table has records in it before passing
4137 IF (p_talv_tbl.COUNT > 0) THEN
4138 i := p_talv_tbl.FIRST;
4139 LOOP
4140 lock_row (
4141 p_api_version => p_api_version,
4142 p_init_msg_list => OKC_API.G_FALSE,
4143 x_return_status => x_return_status,
4144 x_msg_count => x_msg_count,
4145 x_msg_data => x_msg_data,
4146 p_talv_rec => p_talv_tbl(i));
4147 EXIT WHEN (i = p_talv_tbl.LAST);
4148 i := p_talv_tbl.NEXT(i);
4149 END LOOP;
4150 END IF;
4151 EXCEPTION
4152 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4153 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4154 (
4155 l_api_name,
4156 G_PKG_NAME,
4157 'OKC_API.G_RET_STS_ERROR',
4158 x_msg_count,
4159 x_msg_data,
4160 '_PVT'
4161 );
4162 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4163 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4164 (
4165 l_api_name,
4166 G_PKG_NAME,
4167 'OKC_API.G_RET_STS_UNEXP_ERROR',
4168 x_msg_count,
4169 x_msg_data,
4170 '_PVT'
4171 );
4172 WHEN OTHERS THEN
4173 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4174 (
4175 l_api_name,
4176 G_PKG_NAME,
4177 'OTHERS',
4178 x_msg_count,
4179 x_msg_data,
4180 '_PVT'
4181 );
4182 END lock_row;
4183
4184 ---------------------------------------------------------------------------
4185 -- PROCEDURE update_row
4186 ---------------------------------------------------------------------------
4187 -------------------------------------
4188 -- update_row for:OKL_TXL_ASSETS_B --
4189 -------------------------------------
4190 PROCEDURE update_row(
4191 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4192 x_return_status OUT NOCOPY VARCHAR2,
4193 x_msg_count OUT NOCOPY NUMBER,
4194 x_msg_data OUT NOCOPY VARCHAR2,
4195 p_tal_rec IN tal_rec_type,
4196 x_tal_rec OUT NOCOPY tal_rec_type) IS
4197
4198 l_api_version CONSTANT NUMBER := 1;
4199 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
4200 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4201 l_tal_rec tal_rec_type := p_tal_rec;
4202 l_def_tal_rec tal_rec_type;
4203 l_row_notfound BOOLEAN := TRUE;
4204 ----------------------------------
4205 -- FUNCTION populate_new_record --
4206 ----------------------------------
4207 FUNCTION populate_new_record (
4208 p_tal_rec IN tal_rec_type,
4209 x_tal_rec OUT NOCOPY tal_rec_type
4210 ) RETURN VARCHAR2 IS
4211 l_tal_rec tal_rec_type;
4212 l_row_notfound BOOLEAN := TRUE;
4213 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4214 BEGIN
4215 x_tal_rec := p_tal_rec;
4216 -- Get current database values
4217 l_tal_rec := get_rec(p_tal_rec, l_row_notfound);
4218 IF (l_row_notfound) THEN
4219 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4220 END IF;
4221 IF (x_tal_rec.id = OKC_API.G_MISS_NUM)
4222 THEN
4223 x_tal_rec.id := l_tal_rec.id;
4224 END IF;
4225 IF (x_tal_rec.object_version_number = OKC_API.G_MISS_NUM)
4226 THEN
4227 x_tal_rec.object_version_number := l_tal_rec.object_version_number;
4228 END IF;
4229 IF (x_tal_rec.tas_id = OKC_API.G_MISS_NUM)
4230 THEN
4231 x_tal_rec.tas_id := l_tal_rec.tas_id;
4232 END IF;
4233 IF (x_tal_rec.ilo_id = OKC_API.G_MISS_NUM)
4234 THEN
4235 x_tal_rec.ilo_id := l_tal_rec.ilo_id;
4236 END IF;
4237 IF (x_tal_rec.ilo_id_old = OKC_API.G_MISS_NUM)
4238 THEN
4239 x_tal_rec.ilo_id_old := l_tal_rec.ilo_id_old;
4240 END IF;
4241 IF (x_tal_rec.iay_id = OKC_API.G_MISS_NUM)
4242 THEN
4243 x_tal_rec.iay_id := l_tal_rec.iay_id;
4244 END IF;
4245 IF (x_tal_rec.iay_id_new = OKC_API.G_MISS_NUM)
4246 THEN
4247 x_tal_rec.iay_id_new := l_tal_rec.iay_id_new;
4248 END IF;
4249 IF (x_tal_rec.kle_id = OKC_API.G_MISS_NUM)
4250 THEN
4251 x_tal_rec.kle_id := l_tal_rec.kle_id;
4252 END IF;
4253 IF (x_tal_rec.dnz_khr_id = OKC_API.G_MISS_NUM)
4254 THEN
4255 x_tal_rec.dnz_khr_id := l_tal_rec.dnz_khr_id;
4256 END IF;
4257 IF (x_tal_rec.line_number = OKC_API.G_MISS_NUM)
4258 THEN
4259 x_tal_rec.line_number := l_tal_rec.line_number;
4260 END IF;
4261 IF (x_tal_rec.org_id = OKC_API.G_MISS_NUM)
4262 THEN
4263 x_tal_rec.org_id := l_tal_rec.org_id;
4264 END IF;
4265 IF (x_tal_rec.tal_type = OKC_API.G_MISS_CHAR)
4266 THEN
4267 x_tal_rec.tal_type := l_tal_rec.tal_type;
4268 END IF;
4269 IF (x_tal_rec.asset_number = OKC_API.G_MISS_CHAR)
4270 THEN
4271 x_tal_rec.asset_number := l_tal_rec.asset_number;
4272 END IF;
4273 IF (x_tal_rec.original_cost = OKC_API.G_MISS_NUM)
4274 THEN
4275 x_tal_rec.original_cost := l_tal_rec.original_cost;
4276 END IF;
4277 IF (x_tal_rec.current_units = OKC_API.G_MISS_NUM)
4278 THEN
4279 x_tal_rec.current_units := l_tal_rec.current_units;
4280 END IF;
4281 IF (x_tal_rec.manufacturer_name = OKC_API.G_MISS_CHAR)
4282 THEN
4283 x_tal_rec.manufacturer_name := l_tal_rec.manufacturer_name;
4284 END IF;
4285 IF (x_tal_rec.year_manufactured = OKC_API.G_MISS_NUM)
4286 THEN
4287 x_tal_rec.year_manufactured := l_tal_rec.year_manufactured;
4288 END IF;
4289 IF (x_tal_rec.supplier_id = OKC_API.G_MISS_NUM)
4290 THEN
4291 x_tal_rec.supplier_id := l_tal_rec.supplier_id;
4292 END IF;
4293 IF (x_tal_rec.used_asset_yn = OKC_API.G_MISS_CHAR)
4294 THEN
4295 x_tal_rec.used_asset_yn := l_tal_rec.used_asset_yn;
4296 END IF;
4297 IF (x_tal_rec.tag_number = OKC_API.G_MISS_CHAR)
4298 THEN
4299 x_tal_rec.tag_number := l_tal_rec.tag_number;
4300 END IF;
4301 IF (x_tal_rec.model_number = OKC_API.G_MISS_CHAR)
4302 THEN
4303 x_tal_rec.model_number := l_tal_rec.model_number;
4304 END IF;
4305 IF (x_tal_rec.corporate_book = OKC_API.G_MISS_CHAR)
4306 THEN
4307 x_tal_rec.corporate_book := l_tal_rec.corporate_book;
4308 END IF;
4309 IF (x_tal_rec.date_purchased = OKC_API.G_MISS_DATE)
4310 THEN
4311 x_tal_rec.date_purchased := l_tal_rec.date_purchased;
4312 END IF;
4313 IF (x_tal_rec.date_purchased = OKC_API.G_MISS_DATE)
4314 THEN
4315 x_tal_rec.date_delivery := l_tal_rec.date_delivery;
4316 END IF;
4317 IF (x_tal_rec.in_service_date = OKC_API.G_MISS_DATE)
4318 THEN
4319 x_tal_rec.in_service_date := l_tal_rec.in_service_date;
4320 END IF;
4321 IF (x_tal_rec.life_in_months = OKC_API.G_MISS_NUM)
4322 THEN
4323 x_tal_rec.life_in_months := l_tal_rec.life_in_months;
4324 END IF;
4325 IF (x_tal_rec.depreciation_id = OKC_API.G_MISS_NUM)
4326 THEN
4327 x_tal_rec.depreciation_id := l_tal_rec.depreciation_id;
4328 END IF;
4329 IF (x_tal_rec.depreciation_cost = OKC_API.G_MISS_NUM)
4330 THEN
4331 x_tal_rec.depreciation_cost := l_tal_rec.depreciation_cost;
4332 END IF;
4333 IF (x_tal_rec.depreciation_id = OKC_API.G_MISS_NUM)
4334 THEN
4335 x_tal_rec.deprn_method := l_tal_rec.deprn_method;
4336 END IF;
4337 IF (x_tal_rec.deprn_rate = OKC_API.G_MISS_NUM)
4338 THEN
4339 x_tal_rec.deprn_rate := l_tal_rec.deprn_rate;
4340 END IF;
4341 IF (x_tal_rec.salvage_value = OKC_API.G_MISS_NUM)
4342 THEN
4343 x_tal_rec.salvage_value := l_tal_rec.salvage_value;
4344 END IF;
4345 IF (x_tal_rec.percent_salvage_value = OKC_API.G_MISS_NUM)
4346 THEN
4347 x_tal_rec.percent_salvage_value := l_tal_rec.percent_salvage_value;
4348 END IF;
4349 --Bug# 2981308
4350 IF (x_tal_rec.asset_key_id = OKL_API.G_MISS_NUM)
4351 THEN
4352 x_tal_rec.asset_key_id := l_tal_rec.asset_key_id;
4353 END IF;
4354 -- Bug# 4028371
4355 IF (x_tal_rec.fa_trx_date = OKC_API.G_MISS_DATE)
4356 THEN
4357 x_tal_rec.fa_trx_date := l_tal_rec.fa_trx_date;
4358 END IF;
4359 --Bug# 4899328
4360 IF (x_tal_rec.fa_cost = OKL_API.G_MISS_NUM)
4361 THEN
4362 x_tal_rec.fa_cost := l_tal_rec.fa_cost;
4363 END IF;
4364 IF (x_tal_rec.attribute_category = OKC_API.G_MISS_CHAR)
4365 THEN
4366 x_tal_rec.attribute_category := l_tal_rec.attribute_category;
4367 END IF;
4368 IF (x_tal_rec.attribute1 = OKC_API.G_MISS_CHAR)
4369 THEN
4370 x_tal_rec.attribute1 := l_tal_rec.attribute1;
4371 END IF;
4372 IF (x_tal_rec.attribute2 = OKC_API.G_MISS_CHAR)
4373 THEN
4374 x_tal_rec.attribute2 := l_tal_rec.attribute2;
4375 END IF;
4376 IF (x_tal_rec.attribute3 = OKC_API.G_MISS_CHAR)
4377 THEN
4378 x_tal_rec.attribute3 := l_tal_rec.attribute3;
4379 END IF;
4380 IF (x_tal_rec.attribute4 = OKC_API.G_MISS_CHAR)
4381 THEN
4382 x_tal_rec.attribute4 := l_tal_rec.attribute4;
4383 END IF;
4384 IF (x_tal_rec.attribute5 = OKC_API.G_MISS_CHAR)
4385 THEN
4386 x_tal_rec.attribute5 := l_tal_rec.attribute5;
4387 END IF;
4388 IF (x_tal_rec.attribute6 = OKC_API.G_MISS_CHAR)
4389 THEN
4390 x_tal_rec.attribute6 := l_tal_rec.attribute6;
4391 END IF;
4392 IF (x_tal_rec.attribute7 = OKC_API.G_MISS_CHAR)
4393 THEN
4394 x_tal_rec.attribute7 := l_tal_rec.attribute7;
4395 END IF;
4396 IF (x_tal_rec.attribute8 = OKC_API.G_MISS_CHAR)
4397 THEN
4398 x_tal_rec.attribute8 := l_tal_rec.attribute8;
4399 END IF;
4400 IF (x_tal_rec.attribute9 = OKC_API.G_MISS_CHAR)
4401 THEN
4402 x_tal_rec.attribute9 := l_tal_rec.attribute9;
4403 END IF;
4404 IF (x_tal_rec.attribute10 = OKC_API.G_MISS_CHAR)
4405 THEN
4406 x_tal_rec.attribute10 := l_tal_rec.attribute10;
4407 END IF;
4408 IF (x_tal_rec.attribute11 = OKC_API.G_MISS_CHAR)
4409 THEN
4410 x_tal_rec.attribute11 := l_tal_rec.attribute11;
4411 END IF;
4412 IF (x_tal_rec.attribute12 = OKC_API.G_MISS_CHAR)
4413 THEN
4414 x_tal_rec.attribute12 := l_tal_rec.attribute12;
4415 END IF;
4416 IF (x_tal_rec.attribute13 = OKC_API.G_MISS_CHAR)
4417 THEN
4418 x_tal_rec.attribute13 := l_tal_rec.attribute13;
4419 END IF;
4420 IF (x_tal_rec.attribute14 = OKC_API.G_MISS_CHAR)
4421 THEN
4422 x_tal_rec.attribute14 := l_tal_rec.attribute14;
4423 END IF;
4424 IF (x_tal_rec.attribute15 = OKC_API.G_MISS_CHAR)
4425 THEN
4426 x_tal_rec.attribute15 := l_tal_rec.attribute15;
4427 END IF;
4428 IF (x_tal_rec.created_by = OKC_API.G_MISS_NUM)
4429 THEN
4430 x_tal_rec.created_by := l_tal_rec.created_by;
4431 END IF;
4432 IF (x_tal_rec.creation_date = OKC_API.G_MISS_DATE)
4433 THEN
4434 x_tal_rec.creation_date := l_tal_rec.creation_date;
4435 END IF;
4436 IF (x_tal_rec.last_updated_by = OKC_API.G_MISS_NUM)
4437 THEN
4438 x_tal_rec.last_updated_by := l_tal_rec.last_updated_by;
4439 END IF;
4440 IF (x_tal_rec.last_update_date = OKC_API.G_MISS_DATE)
4441 THEN
4442 x_tal_rec.last_update_date := l_tal_rec.last_update_date;
4443 END IF;
4444 IF (x_tal_rec.last_update_login = OKC_API.G_MISS_NUM)
4445 THEN
4446 x_tal_rec.last_update_login := l_tal_rec.last_update_login;
4447 END IF;
4448 IF (x_tal_rec.depreciate_yn = OKC_API.G_MISS_CHAR)
4449 THEN
4450 x_tal_rec.depreciate_yn := l_tal_rec.depreciate_yn;
4451 END IF;
4452 IF (x_tal_rec.hold_period_days = OKC_API.G_MISS_NUM)
4453 THEN
4454 x_tal_rec.hold_period_days := l_tal_rec.hold_period_days;
4455 END IF;
4456 IF (x_tal_rec.old_salvage_value = OKC_API.G_MISS_NUM)
4457 THEN
4458 x_tal_rec.old_salvage_value := l_tal_rec.old_salvage_value;
4459 END IF;
4460 IF (x_tal_rec.new_residual_value = OKC_API.G_MISS_NUM)
4461 THEN
4462 x_tal_rec.new_residual_value := l_tal_rec.new_residual_value;
4463 END IF;
4464 IF (x_tal_rec.old_residual_value = OKC_API.G_MISS_NUM)
4465 THEN
4466 x_tal_rec.old_residual_value := l_tal_rec.old_residual_value;
4467 END IF;
4468 IF (x_tal_rec.units_retired = OKC_API.G_MISS_NUM)
4469 THEN
4470 x_tal_rec.units_retired := l_tal_rec.units_retired;
4471 END IF;
4472 IF (x_tal_rec.cost_retired = OKC_API.G_MISS_NUM)
4473 THEN
4474 x_tal_rec.cost_retired := l_tal_rec.cost_retired;
4475 END IF;
4476 IF (x_tal_rec.sale_proceeds = OKC_API.G_MISS_NUM)
4477 THEN
4478 x_tal_rec.sale_proceeds := l_tal_rec.sale_proceeds;
4479 END IF;
4480 IF (x_tal_rec.removal_cost = OKC_API.G_MISS_NUM)
4481 THEN
4482 x_tal_rec.removal_cost := l_tal_rec.removal_cost;
4483 END IF;
4484 IF (x_tal_rec.dnz_asset_id = OKC_API.G_MISS_NUM)
4485 THEN
4486 x_tal_rec.dnz_asset_id := l_tal_rec.dnz_asset_id;
4487 END IF;
4488 IF (x_tal_rec.date_due = OKC_API.G_MISS_DATE)
4489 THEN
4490 x_tal_rec.date_due := l_tal_rec.date_due;
4491 END IF;
4492 IF (x_tal_rec.rep_asset_id = OKC_API.G_MISS_NUM)
4493 THEN
4494 x_tal_rec.rep_asset_id := l_tal_rec.rep_asset_id;
4495 END IF;
4496 IF (x_tal_rec.lke_asset_id = OKC_API.G_MISS_NUM)
4497 THEN
4498 x_tal_rec.lke_asset_id := l_tal_rec.lke_asset_id;
4499 END IF;
4500 IF (x_tal_rec.match_amount = OKC_API.G_MISS_NUM)
4501 THEN
4502 x_tal_rec.match_amount := l_tal_rec.match_amount;
4503 END IF;
4504 IF (x_tal_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR)
4505 THEN
4506 x_tal_rec.split_into_singles_flag := l_tal_rec.split_into_singles_flag;
4507 END IF;
4508 IF (x_tal_rec.split_into_units = OKC_API.G_MISS_NUM)
4509 THEN
4510 x_tal_rec.split_into_units := l_tal_rec.split_into_units;
4511 END IF;
4512 -- Multi Currency Change
4513 IF (x_tal_rec.currency_code = OKC_API.G_MISS_CHAR)
4514 THEN
4515 x_tal_rec.currency_code := l_tal_rec.currency_code;
4516 END IF;
4517 IF (x_tal_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
4518 THEN
4519 x_tal_rec.currency_conversion_type := l_tal_rec.currency_conversion_type;
4520 END IF;
4521 IF (x_tal_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
4522 THEN
4523 x_tal_rec.currency_conversion_rate := l_tal_rec.currency_conversion_rate;
4524 END IF;
4525 IF (x_tal_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
4526 THEN
4527 x_tal_rec.currency_conversion_date := l_tal_rec.currency_conversion_date;
4528 END IF;
4529 -- Multi Currency Change
4530
4531 -- VRS Project - START
4532
4533 IF (x_tal_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM) THEN
4534 x_tal_rec.RESIDUAL_SHR_PARTY_ID:= l_tal_rec.RESIDUAL_SHR_PARTY_ID;
4535 END IF;
4536
4537 IF (x_tal_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM) THEN
4538 x_tal_rec.RESIDUAL_SHR_AMOUNT := l_tal_rec.RESIDUAL_SHR_AMOUNT;
4539 END IF;
4540 IF (x_tal_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM) THEN
4541 x_tal_rec.RETIREMENT_ID := l_tal_rec.RETIREMENT_ID;
4542 END IF;
4543
4544 -- VRS Project - END
4545
4546
4547 RETURN(l_return_status);
4548 END populate_new_record;
4549 -----------------------------------------
4550 -- Set_Attributes for:OKL_TXL_ASSETS_B --
4551 -----------------------------------------
4552 FUNCTION Set_Attributes (
4553 p_tal_rec IN tal_rec_type,
4554 x_tal_rec OUT NOCOPY tal_rec_type
4555 ) RETURN VARCHAR2 IS
4556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4557 BEGIN
4558 x_tal_rec := p_tal_rec;
4559 RETURN(l_return_status);
4560 END Set_Attributes;
4561 BEGIN
4562 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4563 p_init_msg_list,
4564 '_PVT',
4565 x_return_status);
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 --- Setting item attributes
4572 l_return_status := Set_Attributes(
4573 p_tal_rec, -- IN
4574 l_tal_rec); -- OUT
4575 --- If any errors happen abort API
4576 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4578 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4579 RAISE OKC_API.G_EXCEPTION_ERROR;
4580 END IF;
4581 l_return_status := populate_new_record(l_tal_rec, l_def_tal_rec);
4582 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4583 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4584 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4585 RAISE OKC_API.G_EXCEPTION_ERROR;
4586 END IF;
4587 UPDATE OKL_TXL_ASSETS_B
4588 SET TAS_ID = l_def_tal_rec.tas_id,
4589 OBJECT_VERSION_NUMBER = l_def_tal_rec.object_version_number,
4590 ILO_ID = l_def_tal_rec.ilo_id,
4591 ILO_ID_OLD = l_def_tal_rec.ilo_id_old,
4592 IAY_ID = l_def_tal_rec.iay_id,
4593 IAY_ID_NEW = l_def_tal_rec.iay_id_new,
4594 KLE_ID = l_def_tal_rec.kle_id,
4595 DNZ_KHR_ID = l_def_tal_rec.dnz_khr_id,
4596 LINE_NUMBER = l_def_tal_rec.line_number,
4597 ORG_ID = l_def_tal_rec.org_id,
4598 TAL_TYPE = l_def_tal_rec.tal_type,
4599 ASSET_NUMBER = l_def_tal_rec.asset_number,
4600 FA_LOCATION_ID = l_def_tal_rec.fa_location_id,
4601 ORIGINAL_COST = l_def_tal_rec.original_cost,
4602 CURRENT_UNITS = l_def_tal_rec.current_units,
4603 MANUFACTURER_NAME = l_def_tal_rec.manufacturer_name,
4604 YEAR_MANUFACTURED = l_def_tal_rec.year_manufactured,
4605 SUPPLIER_ID = l_def_tal_rec.supplier_id,
4606 USED_ASSET_YN = l_def_tal_rec.used_asset_yn,
4607 TAG_NUMBER = l_def_tal_rec.tag_number,
4608 MODEL_NUMBER = l_def_tal_rec.model_number,
4609 CORPORATE_BOOK = l_def_tal_rec.corporate_book,
4610 DATE_PURCHASED = l_def_tal_rec.date_purchased,
4611 DATE_DELIVERY = l_def_tal_rec.date_delivery,
4612 IN_SERVICE_DATE = l_def_tal_rec.in_service_date,
4613 LIFE_IN_MONTHS = l_def_tal_rec.life_in_months,
4614 DEPRECIATION_ID = l_def_tal_rec.depreciation_id,
4615 DEPRECIATION_COST = l_def_tal_rec.depreciation_cost,
4616 DEPRN_METHOD = l_def_tal_rec.deprn_method,
4617 DEPRN_RATE = l_def_tal_rec.deprn_rate,
4618 SALVAGE_VALUE = l_def_tal_rec.salvage_value,
4619 PERCENT_SALVAGE_VALUE = l_def_tal_rec.percent_salvage_value,
4620 --Bug# 2981308
4621 ASSET_KEY_ID = l_def_tal_rec.asset_key_id,
4622 -- Bug# 4028371
4623 FA_TRX_DATE = l_def_tal_rec.fa_trx_date,
4624 --Bug# 4899328
4625 FA_COST = l_def_tal_rec.fa_cost,
4626 ATTRIBUTE_CATEGORY = l_def_tal_rec.attribute_category,
4627 ATTRIBUTE1 = l_def_tal_rec.attribute1,
4628 ATTRIBUTE2 = l_def_tal_rec.attribute2,
4629 ATTRIBUTE3 = l_def_tal_rec.attribute3,
4630 ATTRIBUTE4 = l_def_tal_rec.attribute4,
4631 ATTRIBUTE5 = l_def_tal_rec.attribute5,
4632 ATTRIBUTE6 = l_def_tal_rec.attribute6,
4633 ATTRIBUTE7 = l_def_tal_rec.attribute7,
4634 ATTRIBUTE8 = l_def_tal_rec.attribute8,
4635 ATTRIBUTE9 = l_def_tal_rec.attribute9,
4636 ATTRIBUTE10 = l_def_tal_rec.attribute10,
4637 ATTRIBUTE11 = l_def_tal_rec.attribute11,
4638 ATTRIBUTE12 = l_def_tal_rec.attribute12,
4639 ATTRIBUTE13 = l_def_tal_rec.attribute13,
4640 ATTRIBUTE14 = l_def_tal_rec.attribute14,
4641 ATTRIBUTE15 = l_def_tal_rec.attribute15,
4642 CREATED_BY = l_def_tal_rec.created_by,
4643 CREATION_DATE = l_def_tal_rec.creation_date,
4644 LAST_UPDATED_BY = l_def_tal_rec.last_updated_by,
4645 LAST_UPDATE_DATE = l_def_tal_rec.last_update_date,
4646 LAST_UPDATE_LOGIN = l_def_tal_rec.last_update_login,
4647 DEPRECIATE_YN = l_def_tal_rec.depreciate_yn,
4648 HOLD_PERIOD_DAYS = l_def_tal_rec.hold_period_days,
4649 OLD_SALVAGE_VALUE = l_def_tal_rec.old_salvage_value,
4650 NEW_RESIDUAL_VALUE = l_def_tal_rec.new_residual_value,
4651 OLD_RESIDUAL_VALUE = l_def_tal_rec.old_residual_value,
4652 UNITS_RETIRED = l_def_tal_rec.units_retired,
4653 COST_RETIRED = l_def_tal_rec.cost_retired,
4654 SALE_PROCEEDS = l_def_tal_rec.sale_proceeds,
4655 REMOVAL_COST = l_def_tal_rec.removal_cost,
4656 DNZ_ASSET_ID = l_def_tal_rec.dnz_asset_id,
4657 DATE_DUE = l_def_tal_rec.date_due,
4658 REP_ASSET_ID = l_def_tal_rec.rep_asset_id,
4659 LKE_ASSET_ID = l_def_tal_rec.lke_asset_id,
4660 MATCH_AMOUNT = l_def_tal_rec.match_amount,
4661 SPLIT_INTO_SINGLES_FLAG = l_def_tal_rec.split_into_singles_flag,
4662 SPLIT_INTO_UNITS = l_def_tal_rec.split_into_units,
4663 CURRENCY_CODE = l_def_tal_rec.currency_code,
4664 CURRENCY_CONVERSION_TYPE = l_def_tal_rec.currency_conversion_type,
4665 CURRENCY_CONVERSION_RATE = l_def_tal_rec.currency_conversion_rate,
4666 CURRENCY_CONVERSION_DATE = l_def_tal_rec.currency_conversion_date,
4667
4668 -- VRS Project - START
4669 RESIDUAL_SHR_PARTY_ID =l_def_tal_rec.RESIDUAL_SHR_PARTY_ID,
4670 RESIDUAL_SHR_AMOUNT =l_def_tal_rec.RESIDUAL_SHR_AMOUNT,
4671 RETIREMENT_ID = l_def_tal_rec.RETIREMENT_ID
4672 -- VRS Project - END
4673
4674 WHERE ID = l_def_tal_rec.id;
4675
4676 x_tal_rec := l_def_tal_rec;
4677 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4678 EXCEPTION
4679 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4680 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4681 (
4682 l_api_name,
4683 G_PKG_NAME,
4684 'OKC_API.G_RET_STS_ERROR',
4685 x_msg_count,
4686 x_msg_data,
4687 '_PVT'
4688 );
4689 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4690 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4691 (
4692 l_api_name,
4693 G_PKG_NAME,
4694 'OKC_API.G_RET_STS_UNEXP_ERROR',
4695 x_msg_count,
4696 x_msg_data,
4697 '_PVT'
4698 );
4699 WHEN OTHERS THEN
4700 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4701 (
4702 l_api_name,
4703 G_PKG_NAME,
4704 'OTHERS',
4705 x_msg_count,
4706 x_msg_data,
4707 '_PVT'
4708 );
4709 END update_row;
4710 --------------------------------------
4711 -- update_row for:OKL_TXL_ASSETS_TL --
4712 --------------------------------------
4713 PROCEDURE update_row(
4714 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4715 x_return_status OUT NOCOPY VARCHAR2,
4716 x_msg_count OUT NOCOPY NUMBER,
4717 x_msg_data OUT NOCOPY VARCHAR2,
4718 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4719 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type) IS
4720
4721 l_api_version CONSTANT NUMBER := 1;
4722 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4723 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4724 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type := p_okl_txl_assets_tl_rec;
4725 l_def_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4726 l_row_notfound BOOLEAN := TRUE;
4727 ----------------------------------
4728 -- FUNCTION populate_new_record --
4729 ----------------------------------
4730 FUNCTION populate_new_record (
4731 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4732 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
4733 ) RETURN VARCHAR2 IS
4734 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4735 l_row_notfound BOOLEAN := TRUE;
4736 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4737 BEGIN
4738 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
4739 -- Get current database values
4740 l_okl_txl_assets_tl_rec := get_rec(p_okl_txl_assets_tl_rec, l_row_notfound);
4741 IF (l_row_notfound) THEN
4742 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4743 END IF;
4744 IF (x_okl_txl_assets_tl_rec.id = OKC_API.G_MISS_NUM)
4745 THEN
4746 x_okl_txl_assets_tl_rec.id := l_okl_txl_assets_tl_rec.id;
4747 END IF;
4748 IF (x_okl_txl_assets_tl_rec.language = OKC_API.G_MISS_CHAR)
4749 THEN
4750 x_okl_txl_assets_tl_rec.language := l_okl_txl_assets_tl_rec.language;
4751 END IF;
4752 IF (x_okl_txl_assets_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
4753 THEN
4754 x_okl_txl_assets_tl_rec.source_lang := l_okl_txl_assets_tl_rec.source_lang;
4755 END IF;
4756 IF (x_okl_txl_assets_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4757 THEN
4758 x_okl_txl_assets_tl_rec.sfwt_flag := l_okl_txl_assets_tl_rec.sfwt_flag;
4759 END IF;
4760 IF (x_okl_txl_assets_tl_rec.description = OKC_API.G_MISS_CHAR)
4761 THEN
4762 x_okl_txl_assets_tl_rec.description := l_okl_txl_assets_tl_rec.description;
4763 END IF;
4764 IF (x_okl_txl_assets_tl_rec.created_by = OKC_API.G_MISS_NUM)
4765 THEN
4766 x_okl_txl_assets_tl_rec.created_by := l_okl_txl_assets_tl_rec.created_by;
4767 END IF;
4768 IF (x_okl_txl_assets_tl_rec.creation_date = OKC_API.G_MISS_DATE)
4769 THEN
4770 x_okl_txl_assets_tl_rec.creation_date := l_okl_txl_assets_tl_rec.creation_date;
4771 END IF;
4772 IF (x_okl_txl_assets_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
4773 THEN
4774 x_okl_txl_assets_tl_rec.last_updated_by := l_okl_txl_assets_tl_rec.last_updated_by;
4775 END IF;
4776 IF (x_okl_txl_assets_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
4777 THEN
4778 x_okl_txl_assets_tl_rec.last_update_date := l_okl_txl_assets_tl_rec.last_update_date;
4779 END IF;
4780 IF (x_okl_txl_assets_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
4781 THEN
4782 x_okl_txl_assets_tl_rec.last_update_login := l_okl_txl_assets_tl_rec.last_update_login;
4783 END IF;
4784 RETURN(l_return_status);
4785 END populate_new_record;
4786 ------------------------------------------
4787 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
4788 ------------------------------------------
4789 FUNCTION Set_Attributes (
4790 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
4791 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
4792 ) RETURN VARCHAR2 IS
4793 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4794 BEGIN
4795 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
4796 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
4797 x_okl_txl_assets_tl_rec.SOURCE_LANG := USERENV('LANG');
4798 RETURN(l_return_status);
4799 END Set_Attributes;
4800 BEGIN
4801 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4802 p_init_msg_list,
4803 '_PVT',
4804 x_return_status);
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 --- Setting item attributes
4811 l_return_status := Set_Attributes(
4812 p_okl_txl_assets_tl_rec, -- IN
4813 l_okl_txl_assets_tl_rec); -- OUT
4814 --- If any errors happen abort API
4815 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4816 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4817 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4818 RAISE OKC_API.G_EXCEPTION_ERROR;
4819 END IF;
4820 l_return_status := populate_new_record(l_okl_txl_assets_tl_rec, l_def_okl_txl_assets_tl_rec);
4821 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4822 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4823 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4824 RAISE OKC_API.G_EXCEPTION_ERROR;
4825 END IF;
4826 UPDATE OKL_TXL_ASSETS_TL
4827 SET DESCRIPTION = l_def_okl_txl_assets_tl_rec.description,
4828 --Bug# 3641933 :
4829 SOURCE_LANG = l_def_okl_txl_assets_tl_rec.source_lang,
4830 CREATED_BY = l_def_okl_txl_assets_tl_rec.created_by,
4831 CREATION_DATE = l_def_okl_txl_assets_tl_rec.creation_date,
4832 LAST_UPDATED_BY = l_def_okl_txl_assets_tl_rec.last_updated_by,
4833 LAST_UPDATE_DATE = l_def_okl_txl_assets_tl_rec.last_update_date,
4834 LAST_UPDATE_LOGIN = l_def_okl_txl_assets_tl_rec.last_update_login
4835 WHERE ID = l_def_okl_txl_assets_tl_rec.id
4836 --Bug# 3641933 :
4837 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
4838 --AND SOURCE_LANG = USERENV('LANG');
4839
4840 UPDATE OKL_TXL_ASSETS_TL
4841 SET SFWT_FLAG = 'Y'
4842 WHERE ID = l_def_okl_txl_assets_tl_rec.id
4843 AND SOURCE_LANG <> USERENV('LANG');
4844
4845 x_okl_txl_assets_tl_rec := l_def_okl_txl_assets_tl_rec;
4846 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4847 EXCEPTION
4848 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4849 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4850 (
4851 l_api_name,
4852 G_PKG_NAME,
4853 'OKC_API.G_RET_STS_ERROR',
4854 x_msg_count,
4855 x_msg_data,
4856 '_PVT'
4857 );
4858 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4859 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4860 (
4861 l_api_name,
4862 G_PKG_NAME,
4863 'OKC_API.G_RET_STS_UNEXP_ERROR',
4864 x_msg_count,
4865 x_msg_data,
4866 '_PVT'
4867 );
4868 WHEN OTHERS THEN
4869 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4870 (
4871 l_api_name,
4872 G_PKG_NAME,
4873 'OTHERS',
4874 x_msg_count,
4875 x_msg_data,
4876 '_PVT'
4877 );
4878 END update_row;
4879 -------------------------------------
4880 -- update_row for:OKL_TXL_ASSETS_V --
4881 -------------------------------------
4882 PROCEDURE update_row(
4883 p_api_version IN NUMBER,
4884 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4885 x_return_status OUT NOCOPY VARCHAR2,
4886 x_msg_count OUT NOCOPY NUMBER,
4887 x_msg_data OUT NOCOPY VARCHAR2,
4888 p_talv_rec IN talv_rec_type,
4889 x_talv_rec OUT NOCOPY talv_rec_type) IS
4890
4891 l_api_version CONSTANT NUMBER := 1;
4892 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4893 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4894 l_talv_rec talv_rec_type := p_talv_rec;
4895 l_def_talv_rec talv_rec_type;
4896 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4897 lx_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
4898 l_tal_rec tal_rec_type;
4899 lx_tal_rec tal_rec_type;
4900 lx_temp_talv_rec talv_rec_type;
4901 -------------------------------
4902 -- FUNCTION fill_who_columns --
4903 -------------------------------
4904 FUNCTION fill_who_columns (
4905 p_talv_rec IN talv_rec_type
4906 ) RETURN talv_rec_type IS
4907 l_talv_rec talv_rec_type := p_talv_rec;
4908 BEGIN
4909 l_talv_rec.LAST_UPDATE_DATE := SYSDATE;
4910 l_talv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4911 l_talv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4912 RETURN(l_talv_rec);
4913 END fill_who_columns;
4914 ----------------------------------
4915 -- FUNCTION populate_new_record --
4916 ----------------------------------
4917 FUNCTION populate_new_record (
4918 p_talv_rec IN talv_rec_type,
4919 x_talv_rec OUT NOCOPY talv_rec_type
4920 ) RETURN VARCHAR2 IS
4921 l_talv_rec talv_rec_type;
4922 l_row_notfound BOOLEAN := TRUE;
4923 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4924 BEGIN
4925 x_talv_rec := p_talv_rec;
4926 -- Get current database values
4927 l_talv_rec := get_rec(p_talv_rec, l_row_notfound);
4928 IF (l_row_notfound) THEN
4929 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4930 END IF;
4931 IF (x_talv_rec.id = OKC_API.G_MISS_NUM)
4932 THEN
4933 x_talv_rec.id := l_talv_rec.id;
4934 END IF;
4935 IF (x_talv_rec.object_version_number = OKC_API.G_MISS_NUM)
4936 THEN
4937 x_talv_rec.object_version_number := l_talv_rec.object_version_number;
4938 END IF;
4939 IF (x_talv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4940 THEN
4941 x_talv_rec.sfwt_flag := l_talv_rec.sfwt_flag;
4942 END IF;
4943 IF (x_talv_rec.tas_id = OKC_API.G_MISS_NUM)
4944 THEN
4945 x_talv_rec.tas_id := l_talv_rec.tas_id;
4946 END IF;
4947 IF (x_talv_rec.ilo_id = OKC_API.G_MISS_NUM)
4948 THEN
4949 x_talv_rec.ilo_id := l_talv_rec.ilo_id;
4950 END IF;
4951 IF (x_talv_rec.ilo_id_old = OKC_API.G_MISS_NUM)
4952 THEN
4953 x_talv_rec.ilo_id_old := l_talv_rec.ilo_id_old;
4954 END IF;
4955 IF (x_talv_rec.iay_id = OKC_API.G_MISS_NUM)
4956 THEN
4957 x_talv_rec.iay_id := l_talv_rec.iay_id;
4958 END IF;
4959 IF (x_talv_rec.iay_id_new = OKC_API.G_MISS_NUM)
4960 THEN
4961 x_talv_rec.iay_id_new := l_talv_rec.iay_id_new;
4962 END IF;
4963 IF (x_talv_rec.kle_id = OKC_API.G_MISS_NUM)
4964 THEN
4965 x_talv_rec.kle_id := l_talv_rec.kle_id;
4966 END IF;
4967 IF (x_talv_rec.dnz_khr_id = OKC_API.G_MISS_NUM)
4968 THEN
4969 x_talv_rec.dnz_khr_id := l_talv_rec.dnz_khr_id;
4970 END IF;
4971 IF (x_talv_rec.line_number = OKC_API.G_MISS_NUM)
4972 THEN
4973 x_talv_rec.line_number := l_talv_rec.line_number;
4974 END IF;
4975 IF (x_talv_rec.org_id = OKC_API.G_MISS_NUM)
4976 THEN
4977 x_talv_rec.org_id := l_talv_rec.org_id;
4978 END IF;
4979 IF (x_talv_rec.tal_type = OKC_API.G_MISS_CHAR)
4980 THEN
4981 x_talv_rec.tal_type := l_talv_rec.tal_type;
4982 END IF;
4983 IF (x_talv_rec.asset_number = OKC_API.G_MISS_CHAR)
4984 THEN
4985 x_talv_rec.asset_number := l_talv_rec.asset_number;
4986 END IF;
4987 IF (x_talv_rec.description = OKC_API.G_MISS_CHAR)
4988 THEN
4989 x_talv_rec.description := l_talv_rec.description;
4990 END IF;
4991 IF (x_talv_rec.fa_location_id = OKC_API.G_MISS_NUM)
4992 THEN
4993 x_talv_rec.fa_location_id := l_talv_rec.fa_location_id;
4994 END IF;
4995 IF (x_talv_rec.original_cost = OKC_API.G_MISS_NUM)
4996 THEN
4997 x_talv_rec.original_cost := l_talv_rec.original_cost;
4998 END IF;
4999 IF (x_talv_rec.current_units = OKC_API.G_MISS_NUM)
5000 THEN
5001 x_talv_rec.current_units := l_talv_rec.current_units;
5002 END IF;
5003 IF (x_talv_rec.manufacturer_name = OKC_API.G_MISS_CHAR)
5004 THEN
5005 x_talv_rec.manufacturer_name := l_talv_rec.manufacturer_name;
5006 END IF;
5007 IF (x_talv_rec.year_manufactured = OKC_API.G_MISS_NUM)
5008 THEN
5009 x_talv_rec.year_manufactured := l_talv_rec.year_manufactured;
5010 END IF;
5011 IF (x_talv_rec.supplier_id = OKC_API.G_MISS_NUM)
5012 THEN
5013 x_talv_rec.supplier_id := l_talv_rec.supplier_id;
5014 END IF;
5015 IF (x_talv_rec.used_asset_yn = OKC_API.G_MISS_CHAR)
5016 THEN
5017 x_talv_rec.used_asset_yn := l_talv_rec.used_asset_yn;
5018 END IF;
5019 IF (x_talv_rec.tag_number = OKC_API.G_MISS_CHAR)
5020 THEN
5021 x_talv_rec.tag_number := l_talv_rec.tag_number;
5022 END IF;
5023 IF (x_talv_rec.model_number = OKC_API.G_MISS_CHAR)
5024 THEN
5025 x_talv_rec.model_number := l_talv_rec.model_number;
5026 END IF;
5027 IF (x_talv_rec.corporate_book = OKC_API.G_MISS_CHAR)
5028 THEN
5029 x_talv_rec.corporate_book := l_talv_rec.corporate_book;
5030 END IF;
5031 IF (x_talv_rec.date_purchased = OKC_API.G_MISS_DATE)
5032 THEN
5033 x_talv_rec.date_purchased := l_talv_rec.date_purchased;
5034 END IF;
5035 IF (x_talv_rec.date_delivery = OKC_API.G_MISS_DATE)
5036 THEN
5037 x_talv_rec.date_delivery := l_talv_rec.date_delivery;
5038 END IF;
5039 IF (x_talv_rec.in_service_date = OKC_API.G_MISS_DATE)
5040 THEN
5041 x_talv_rec.in_service_date := l_talv_rec.in_service_date;
5042 END IF;
5043 IF (x_talv_rec.life_in_months = OKC_API.G_MISS_NUM)
5044 THEN
5045 x_talv_rec.life_in_months := l_talv_rec.life_in_months;
5046 END IF;
5047 IF (x_talv_rec.depreciation_id = OKC_API.G_MISS_NUM)
5048 THEN
5049 x_talv_rec.depreciation_id := l_talv_rec.depreciation_id;
5050 END IF;
5051 IF (x_talv_rec.depreciation_cost = OKC_API.G_MISS_NUM)
5052 THEN
5053 x_talv_rec.depreciation_cost := l_talv_rec.depreciation_cost;
5054 END IF;
5055 IF (x_talv_rec.deprn_method = OKC_API.G_MISS_CHAR)
5056 THEN
5057 x_talv_rec.deprn_method := l_talv_rec.deprn_method;
5058 END IF;
5059 IF (x_talv_rec.deprn_rate = OKC_API.G_MISS_NUM)
5060 THEN
5061 x_talv_rec.deprn_rate := l_talv_rec.deprn_rate;
5062 END IF;
5063 IF (x_talv_rec.salvage_value = OKC_API.G_MISS_NUM)
5064 THEN
5065 x_talv_rec.salvage_value := l_talv_rec.salvage_value;
5066 END IF;
5067 IF (x_talv_rec.percent_salvage_value = OKC_API.G_MISS_NUM)
5068 THEN
5069 x_talv_rec.percent_salvage_value := l_talv_rec.percent_salvage_value;
5070 END IF;
5071 --Bug# 2981308
5072 IF (x_talv_rec.asset_key_id = OKL_API.G_MISS_NUM)
5073 THEN
5074 x_talv_rec.asset_key_id := l_talv_rec.asset_key_id;
5075 END IF;
5076 -- Bug# 4028371
5077 IF (x_talv_rec.fa_trx_date = OKC_API.G_MISS_DATE)
5078 THEN
5079 x_talv_rec.fa_trx_date := l_talv_rec.fa_trx_date;
5080 END IF;
5081 --Bug# 4899328
5082 IF (x_talv_rec.fa_cost = OKL_API.G_MISS_NUM)
5083 THEN
5084 x_talv_rec.fa_cost := l_talv_rec.fa_cost;
5085 END IF;
5086 IF (x_talv_rec.attribute_category = OKC_API.G_MISS_CHAR)
5087 THEN
5088 x_talv_rec.attribute_category := l_talv_rec.attribute_category;
5089 END IF;
5090 IF (x_talv_rec.attribute1 = OKC_API.G_MISS_CHAR)
5091 THEN
5092 x_talv_rec.attribute1 := l_talv_rec.attribute1;
5093 END IF;
5094 IF (x_talv_rec.attribute2 = OKC_API.G_MISS_CHAR)
5095 THEN
5096 x_talv_rec.attribute2 := l_talv_rec.attribute2;
5097 END IF;
5098 IF (x_talv_rec.attribute3 = OKC_API.G_MISS_CHAR)
5099 THEN
5100 x_talv_rec.attribute3 := l_talv_rec.attribute3;
5101 END IF;
5102 IF (x_talv_rec.attribute4 = OKC_API.G_MISS_CHAR)
5103 THEN
5104 x_talv_rec.attribute4 := l_talv_rec.attribute4;
5105 END IF;
5106 IF (x_talv_rec.attribute5 = OKC_API.G_MISS_CHAR)
5107 THEN
5108 x_talv_rec.attribute5 := l_talv_rec.attribute5;
5109 END IF;
5110 IF (x_talv_rec.attribute6 = OKC_API.G_MISS_CHAR)
5111 THEN
5112 x_talv_rec.attribute6 := l_talv_rec.attribute6;
5113 END IF;
5114 IF (x_talv_rec.attribute7 = OKC_API.G_MISS_CHAR)
5115 THEN
5116 x_talv_rec.attribute7 := l_talv_rec.attribute7;
5117 END IF;
5118 IF (x_talv_rec.attribute8 = OKC_API.G_MISS_CHAR)
5119 THEN
5120 x_talv_rec.attribute8 := l_talv_rec.attribute8;
5121 END IF;
5122 IF (x_talv_rec.attribute9 = OKC_API.G_MISS_CHAR)
5123 THEN
5124 x_talv_rec.attribute9 := l_talv_rec.attribute9;
5125 END IF;
5126 IF (x_talv_rec.attribute10 = OKC_API.G_MISS_CHAR)
5127 THEN
5128 x_talv_rec.attribute10 := l_talv_rec.attribute10;
5129 END IF;
5130 IF (x_talv_rec.attribute11 = OKC_API.G_MISS_CHAR)
5131 THEN
5132 x_talv_rec.attribute11 := l_talv_rec.attribute11;
5133 END IF;
5134 IF (x_talv_rec.attribute12 = OKC_API.G_MISS_CHAR)
5135 THEN
5136 x_talv_rec.attribute12 := l_talv_rec.attribute12;
5137 END IF;
5138 IF (x_talv_rec.attribute13 = OKC_API.G_MISS_CHAR)
5139 THEN
5140 x_talv_rec.attribute13 := l_talv_rec.attribute13;
5141 END IF;
5142 IF (x_talv_rec.attribute14 = OKC_API.G_MISS_CHAR)
5143 THEN
5144 x_talv_rec.attribute14 := l_talv_rec.attribute14;
5145 END IF;
5146 IF (x_talv_rec.attribute15 = OKC_API.G_MISS_CHAR)
5147 THEN
5148 x_talv_rec.attribute15 := l_talv_rec.attribute15;
5149 END IF;
5150 IF (x_talv_rec.created_by = OKC_API.G_MISS_NUM)
5151 THEN
5152 x_talv_rec.created_by := l_talv_rec.created_by;
5153 END IF;
5154 IF (x_talv_rec.creation_date = OKC_API.G_MISS_DATE)
5155 THEN
5156 x_talv_rec.creation_date := l_talv_rec.creation_date;
5157 END IF;
5158 IF (x_talv_rec.last_updated_by = OKC_API.G_MISS_NUM)
5159 THEN
5160 x_talv_rec.last_updated_by := l_talv_rec.last_updated_by;
5161 END IF;
5162 IF (x_talv_rec.last_update_date = OKC_API.G_MISS_DATE)
5163 THEN
5164 x_talv_rec.last_update_date := l_talv_rec.last_update_date;
5165 END IF;
5166 IF (x_talv_rec.last_update_login = OKC_API.G_MISS_NUM)
5167 THEN
5168 x_talv_rec.last_update_login := l_talv_rec.last_update_login;
5169 END IF;
5170 IF (x_talv_rec.depreciate_yn = OKC_API.G_MISS_CHAR)
5171 THEN
5172 x_talv_rec.depreciate_yn := l_talv_rec.depreciate_yn;
5173 END IF;
5174 IF (x_talv_rec.hold_period_days = OKC_API.G_MISS_NUM)
5175 THEN
5176 x_talv_rec.hold_period_days := l_talv_rec.hold_period_days;
5177 END IF;
5178 IF (x_talv_rec.old_salvage_value = OKC_API.G_MISS_NUM)
5179 THEN
5180 x_talv_rec.old_salvage_value := l_talv_rec.old_salvage_value;
5181 END IF;
5182 IF (x_talv_rec.new_residual_value = OKC_API.G_MISS_NUM)
5183 THEN
5184 x_talv_rec.new_residual_value := l_talv_rec.new_residual_value;
5185 END IF;
5186 IF (x_talv_rec.old_residual_value = OKC_API.G_MISS_NUM)
5187 THEN
5188 x_talv_rec.old_residual_value := l_talv_rec.old_residual_value;
5189 END IF;
5190 IF (x_talv_rec.units_retired = OKC_API.G_MISS_NUM)
5191 THEN
5192 x_talv_rec.units_retired := l_talv_rec.units_retired;
5193 END IF;
5194 IF (x_talv_rec.cost_retired = OKC_API.G_MISS_NUM)
5195 THEN
5196 x_talv_rec.cost_retired := l_talv_rec.cost_retired;
5197 END IF;
5198 IF (x_talv_rec.sale_proceeds = OKC_API.G_MISS_NUM)
5199 THEN
5200 x_talv_rec.sale_proceeds := l_talv_rec.sale_proceeds;
5201 END IF;
5202 IF (x_talv_rec.removal_cost = OKC_API.G_MISS_NUM)
5203 THEN
5204 x_talv_rec.removal_cost := l_talv_rec.removal_cost;
5205 END IF;
5206 IF (x_talv_rec.dnz_asset_id = OKC_API.G_MISS_NUM)
5207 THEN
5208 x_talv_rec.dnz_asset_id := l_talv_rec.dnz_asset_id;
5209 END IF;
5210 IF (x_talv_rec.date_due = OKC_API.G_MISS_DATE)
5211 THEN
5212 x_talv_rec.date_due := l_talv_rec.date_due;
5213 END IF;
5214 IF (x_talv_rec.rep_asset_id = OKC_API.G_MISS_NUM)
5215 THEN
5216 x_talv_rec.rep_asset_id := l_talv_rec.rep_asset_id;
5217 END IF;
5218 IF (x_talv_rec.lke_asset_id = OKC_API.G_MISS_NUM)
5219 THEN
5220 x_talv_rec.lke_asset_id := l_talv_rec.lke_asset_id;
5221 END IF;
5222 IF (x_talv_rec.match_amount = OKC_API.G_MISS_NUM)
5223 THEN
5224 x_talv_rec.match_amount := l_talv_rec.match_amount;
5225 END IF;
5226 IF (x_talv_rec.split_into_singles_flag = OKC_API.G_MISS_CHAR)
5227 THEN
5228 x_talv_rec.split_into_singles_flag := l_talv_rec.split_into_singles_flag;
5229 END IF;
5230 IF (x_talv_rec.split_into_units = OKC_API.G_MISS_NUM)
5231 THEN
5232 x_talv_rec.split_into_units := l_talv_rec.split_into_units;
5233 END IF;
5234
5235 -- Multi Currency Change
5236 IF (x_talv_rec.currency_code = OKC_API.G_MISS_CHAR)
5237 THEN
5238 x_talv_rec.currency_code := l_talv_rec.currency_code;
5239 END IF;
5240 IF (x_talv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
5241 THEN
5242 x_talv_rec.currency_conversion_type := l_talv_rec.currency_conversion_type;
5243 END IF;
5244 IF (x_talv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
5245 THEN
5246 x_talv_rec.currency_conversion_rate := l_talv_rec.currency_conversion_rate;
5247 END IF;
5248 IF (x_talv_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
5249 THEN
5250 x_talv_rec.currency_conversion_date := l_talv_rec.currency_conversion_date;
5251 END IF;
5252 -- Multi Currency Change
5253
5254 -- VRS Project - START
5255 IF (x_talv_rec.RESIDUAL_SHR_PARTY_ID = OKC_API.G_MISS_NUM)
5256 THEN
5257 x_talv_rec.RESIDUAL_SHR_PARTY_ID := l_talv_rec.RESIDUAL_SHR_PARTY_ID;
5258 END IF;
5259 IF (x_talv_rec.RESIDUAL_SHR_AMOUNT = OKC_API.G_MISS_NUM)
5260 THEN
5261 x_talv_rec.RESIDUAL_SHR_AMOUNT := l_talv_rec.RESIDUAL_SHR_AMOUNT;
5262 END IF;
5263 IF (x_talv_rec.RETIREMENT_ID = OKC_API.G_MISS_NUM)
5264 THEN
5265 x_talv_rec.RETIREMENT_ID := l_talv_rec.RETIREMENT_ID;
5266 END IF;
5267 -- VRS Project - END
5268
5269 RETURN(l_return_status);
5270 END populate_new_record;
5271 -----------------------------------------
5272 -- Set_Attributes for:OKL_TXL_ASSETS_V --
5273 -----------------------------------------
5274 FUNCTION Set_Attributes (
5275 p_talv_rec IN talv_rec_type,
5276 x_talv_rec OUT NOCOPY talv_rec_type
5277 ) RETURN VARCHAR2 IS
5278 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5279 BEGIN
5280 x_talv_rec := p_talv_rec;
5281 x_talv_rec.OBJECT_VERSION_NUMBER := NVL(x_talv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
5282 RETURN(l_return_status);
5283 END Set_Attributes;
5284 BEGIN
5285 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5286 G_PKG_NAME,
5287 p_init_msg_list,
5288 l_api_version,
5289 p_api_version,
5290 '_PVT',
5291 x_return_status);
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 --- Setting item attributes
5298 l_return_status := Set_Attributes(
5299 p_talv_rec, -- IN
5300 l_talv_rec); -- OUT
5301 --- If any errors happen abort API
5302 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5303 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5304 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5305 RAISE OKC_API.G_EXCEPTION_ERROR;
5306 END IF;
5307 l_return_status := populate_new_record(l_talv_rec, l_def_talv_rec);
5308 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5309 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5310 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5311 RAISE OKC_API.G_EXCEPTION_ERROR;
5312 END IF;
5313 l_def_talv_rec := fill_who_columns(l_def_talv_rec);
5314 --- Validate all non-missing attributes (Item Level Validation)
5315 l_return_status := Validate_Attributes(l_def_talv_rec);
5316 --- If any errors happen abort API
5317 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5318 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5319 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5320 RAISE OKC_API.G_EXCEPTION_ERROR;
5321 END IF;
5322 l_return_status := Validate_Record(l_def_talv_rec);
5323 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5324 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5325 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5326 RAISE OKC_API.G_EXCEPTION_ERROR;
5327 END IF;
5328
5329 --
5330 -- Multi-Currency Change, dedey, 12/04/2002
5331 --
5332 validate_currency(
5333 x_return_status => l_return_status,
5334 p_talv_rec => l_def_talv_rec,
5335 x_talv_rec => lx_temp_talv_rec
5336 );
5337
5338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5341 RAISE OKC_API.G_EXCEPTION_ERROR;
5342 END IF;
5343 l_def_talv_rec := lx_temp_talv_rec;
5344
5345 --dbms_output.put_line('After Change: '||lx_temp_talv_rec.currency_code);
5346 --dbms_output.put_line('After Change: '||l_def_talv_rec.currency_code);
5347 --
5348 -- Multi-Currency Change
5349 --
5350
5351 --
5352 -- Fix Bug# 2737014
5353 --
5354 -- Round off amounts
5355 --
5356 roundoff_line_amount(
5357 x_return_status => l_return_status,
5358 x_msg_count => x_msg_count,
5359 x_msg_data => x_msg_data,
5360 p_talv_rec => l_def_talv_rec,
5361 x_talv_rec => lx_temp_talv_rec
5362 );
5363
5364 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5366 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5367 RAISE OKC_API.G_EXCEPTION_ERROR;
5368 END IF;
5369 l_def_talv_rec := lx_temp_talv_rec;
5370
5371 --dbms_output.put_line('After Change Orig cost: '||lx_temp_talv_rec.original_cost);
5372 --dbms_output.put_line('After Change Orig cost: '||l_def_talv_rec.original_cost);
5373
5374 --------------------------------------
5375 -- Move VIEW record to "Child" records
5376 --------------------------------------
5377 migrate(l_def_talv_rec, l_okl_txl_assets_tl_rec);
5378 migrate(l_def_talv_rec, l_tal_rec);
5379 --------------------------------------------
5380 -- Call the UPDATE_ROW for each child record
5381 --------------------------------------------
5382 update_row(
5383 p_init_msg_list,
5384 x_return_status,
5385 x_msg_count,
5386 x_msg_data,
5387 l_okl_txl_assets_tl_rec,
5388 lx_okl_txl_assets_tl_rec
5389 );
5390 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5392 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5393 RAISE OKC_API.G_EXCEPTION_ERROR;
5394 END IF;
5395 migrate(lx_okl_txl_assets_tl_rec, l_def_talv_rec);
5396 update_row(
5397 p_init_msg_list,
5398 x_return_status,
5399 x_msg_count,
5400 x_msg_data,
5401 l_tal_rec,
5402 lx_tal_rec
5403 );
5404 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5405 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5406 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5407 RAISE OKC_API.G_EXCEPTION_ERROR;
5408 END IF;
5409 migrate(lx_tal_rec, l_def_talv_rec);
5410 x_talv_rec := l_def_talv_rec;
5411 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5412 EXCEPTION
5413 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5414 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5415 (
5416 l_api_name,
5417 G_PKG_NAME,
5418 'OKC_API.G_RET_STS_ERROR',
5419 x_msg_count,
5420 x_msg_data,
5421 '_PVT'
5422 );
5423 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5424 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5425 (
5426 l_api_name,
5427 G_PKG_NAME,
5428 'OKC_API.G_RET_STS_UNEXP_ERROR',
5429 x_msg_count,
5430 x_msg_data,
5431 '_PVT'
5432 );
5433 WHEN OTHERS THEN
5434 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5435 (
5436 l_api_name,
5437 G_PKG_NAME,
5438 'OTHERS',
5439 x_msg_count,
5440 x_msg_data,
5441 '_PVT'
5442 );
5443 END update_row;
5444 ----------------------------------------
5445 -- PL/SQL TBL update_row for:TALV_TBL --
5446 ----------------------------------------
5447 PROCEDURE update_row(
5448 p_api_version IN NUMBER,
5449 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5450 x_return_status OUT NOCOPY VARCHAR2,
5451 x_msg_count OUT NOCOPY NUMBER,
5452 x_msg_data OUT NOCOPY VARCHAR2,
5453 p_talv_tbl IN talv_tbl_type,
5454 x_talv_tbl OUT NOCOPY talv_tbl_type) IS
5455
5456 l_api_version CONSTANT NUMBER := 1;
5457 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
5458 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5459 i NUMBER := 0;
5460 BEGIN
5461 OKC_API.init_msg_list(p_init_msg_list);
5462 -- Make sure PL/SQL table has records in it before passing
5463 IF (p_talv_tbl.COUNT > 0) THEN
5464 i := p_talv_tbl.FIRST;
5465 LOOP
5466 update_row (
5467 p_api_version => p_api_version,
5468 p_init_msg_list => OKC_API.G_FALSE,
5469 x_return_status => x_return_status,
5470 x_msg_count => x_msg_count,
5471 x_msg_data => x_msg_data,
5472 p_talv_rec => p_talv_tbl(i),
5473 x_talv_rec => x_talv_tbl(i));
5474 EXIT WHEN (i = p_talv_tbl.LAST);
5475 i := p_talv_tbl.NEXT(i);
5476 END LOOP;
5477 END IF;
5478 EXCEPTION
5479 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5480 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5481 (
5482 l_api_name,
5483 G_PKG_NAME,
5484 'OKC_API.G_RET_STS_ERROR',
5485 x_msg_count,
5486 x_msg_data,
5487 '_PVT'
5488 );
5489 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5490 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5491 (
5492 l_api_name,
5493 G_PKG_NAME,
5494 'OKC_API.G_RET_STS_UNEXP_ERROR',
5495 x_msg_count,
5496 x_msg_data,
5497 '_PVT'
5498 );
5499 WHEN OTHERS THEN
5500 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5501 (
5502 l_api_name,
5503 G_PKG_NAME,
5504 'OTHERS',
5505 x_msg_count,
5506 x_msg_data,
5507 '_PVT'
5508 );
5509 END update_row;
5510
5511 ---------------------------------------------------------------------------
5512 -- PROCEDURE delete_row
5513 ---------------------------------------------------------------------------
5514 -------------------------------------
5515 -- delete_row for:OKL_TXL_ASSETS_B --
5516 -------------------------------------
5517 PROCEDURE delete_row(
5518 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5519 x_return_status OUT NOCOPY VARCHAR2,
5520 x_msg_count OUT NOCOPY NUMBER,
5521 x_msg_data OUT NOCOPY VARCHAR2,
5522 p_tal_rec IN tal_rec_type) IS
5523
5524 l_api_version CONSTANT NUMBER := 1;
5525 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5526 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5527 l_tal_rec tal_rec_type:= p_tal_rec;
5528 l_row_notfound BOOLEAN := TRUE;
5529 BEGIN
5530 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5531 p_init_msg_list,
5532 '_PVT',
5533 x_return_status);
5534 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5536 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5537 RAISE OKC_API.G_EXCEPTION_ERROR;
5538 END IF;
5539 DELETE FROM OKL_TXL_ASSETS_B
5540 WHERE ID = l_tal_rec.id;
5541
5542 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5543 EXCEPTION
5544 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5545 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5546 (
5547 l_api_name,
5548 G_PKG_NAME,
5549 'OKC_API.G_RET_STS_ERROR',
5550 x_msg_count,
5551 x_msg_data,
5552 '_PVT'
5553 );
5554 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5555 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5556 (
5557 l_api_name,
5558 G_PKG_NAME,
5559 'OKC_API.G_RET_STS_UNEXP_ERROR',
5560 x_msg_count,
5561 x_msg_data,
5562 '_PVT'
5563 );
5564 WHEN OTHERS THEN
5565 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5566 (
5567 l_api_name,
5568 G_PKG_NAME,
5569 'OTHERS',
5570 x_msg_count,
5571 x_msg_data,
5572 '_PVT'
5573 );
5574 END delete_row;
5575 --------------------------------------
5576 -- delete_row for:OKL_TXL_ASSETS_TL --
5577 --------------------------------------
5578 PROCEDURE delete_row(
5579 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5580 x_return_status OUT NOCOPY VARCHAR2,
5581 x_msg_count OUT NOCOPY NUMBER,
5582 x_msg_data OUT NOCOPY VARCHAR2,
5583 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type) IS
5584
5585 l_api_version CONSTANT NUMBER := 1;
5586 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5587 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5588 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type:= p_okl_txl_assets_tl_rec;
5589 l_row_notfound BOOLEAN := TRUE;
5590 ------------------------------------------
5591 -- Set_Attributes for:OKL_TXL_ASSETS_TL --
5592 ------------------------------------------
5593 FUNCTION Set_Attributes (
5594 p_okl_txl_assets_tl_rec IN okl_txl_assets_tl_rec_type,
5595 x_okl_txl_assets_tl_rec OUT NOCOPY okl_txl_assets_tl_rec_type
5596 ) RETURN VARCHAR2 IS
5597 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5598 BEGIN
5599 x_okl_txl_assets_tl_rec := p_okl_txl_assets_tl_rec;
5600 x_okl_txl_assets_tl_rec.LANGUAGE := USERENV('LANG');
5601 RETURN(l_return_status);
5602 END Set_Attributes;
5603 BEGIN
5604 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5605 p_init_msg_list,
5606 '_PVT',
5607 x_return_status);
5608 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5609 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5610 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5611 RAISE OKC_API.G_EXCEPTION_ERROR;
5612 END IF;
5613 --- Setting item attributes
5614 l_return_status := Set_Attributes(
5615 p_okl_txl_assets_tl_rec, -- IN
5616 l_okl_txl_assets_tl_rec); -- OUT
5617 --- If any errors happen abort API
5618 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5619 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5620 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5621 RAISE OKC_API.G_EXCEPTION_ERROR;
5622 END IF;
5623 DELETE FROM OKL_TXL_ASSETS_TL
5624 WHERE ID = l_okl_txl_assets_tl_rec.id;
5625
5626 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5627 EXCEPTION
5628 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5629 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5630 (
5631 l_api_name,
5632 G_PKG_NAME,
5633 'OKC_API.G_RET_STS_ERROR',
5634 x_msg_count,
5635 x_msg_data,
5636 '_PVT'
5637 );
5638 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5639 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5640 (
5641 l_api_name,
5642 G_PKG_NAME,
5643 'OKC_API.G_RET_STS_UNEXP_ERROR',
5644 x_msg_count,
5645 x_msg_data,
5646 '_PVT'
5647 );
5648 WHEN OTHERS THEN
5649 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5650 (
5651 l_api_name,
5652 G_PKG_NAME,
5653 'OTHERS',
5654 x_msg_count,
5655 x_msg_data,
5656 '_PVT'
5657 );
5658 END delete_row;
5659 -------------------------------------
5660 -- delete_row for:OKL_TXL_ASSETS_V --
5661 -------------------------------------
5662 PROCEDURE delete_row(
5663 p_api_version IN NUMBER,
5664 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5665 x_return_status OUT NOCOPY VARCHAR2,
5666 x_msg_count OUT NOCOPY NUMBER,
5667 x_msg_data OUT NOCOPY VARCHAR2,
5668 p_talv_rec IN talv_rec_type) IS
5669
5670 l_api_version CONSTANT NUMBER := 1;
5671 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5672 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5673 l_talv_rec talv_rec_type := p_talv_rec;
5674 l_okl_txl_assets_tl_rec okl_txl_assets_tl_rec_type;
5675 l_tal_rec tal_rec_type;
5676 BEGIN
5677 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5678 G_PKG_NAME,
5679 p_init_msg_list,
5680 l_api_version,
5681 p_api_version,
5682 '_PVT',
5683 x_return_status);
5684 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5685 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5686 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5687 RAISE OKC_API.G_EXCEPTION_ERROR;
5688 END IF;
5689 --------------------------------------
5690 -- Move VIEW record to "Child" records
5691 --------------------------------------
5692 migrate(l_talv_rec, l_okl_txl_assets_tl_rec);
5693 migrate(l_talv_rec, l_tal_rec);
5694 --------------------------------------------
5695 -- Call the DELETE_ROW for each child record
5696 --------------------------------------------
5697 delete_row(
5698 p_init_msg_list,
5699 x_return_status,
5700 x_msg_count,
5701 x_msg_data,
5702 l_okl_txl_assets_tl_rec
5703 );
5704 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5705 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5706 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5707 RAISE OKC_API.G_EXCEPTION_ERROR;
5708 END IF;
5709 delete_row(
5710 p_init_msg_list,
5711 x_return_status,
5712 x_msg_count,
5713 x_msg_data,
5714 l_tal_rec
5715 );
5716 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5717 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5718 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5719 RAISE OKC_API.G_EXCEPTION_ERROR;
5720 END IF;
5721 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5722 EXCEPTION
5723 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5724 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5725 (
5726 l_api_name,
5727 G_PKG_NAME,
5728 'OKC_API.G_RET_STS_ERROR',
5729 x_msg_count,
5730 x_msg_data,
5731 '_PVT'
5732 );
5733 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5734 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5735 (
5736 l_api_name,
5737 G_PKG_NAME,
5738 'OKC_API.G_RET_STS_UNEXP_ERROR',
5739 x_msg_count,
5740 x_msg_data,
5741 '_PVT'
5742 );
5743 WHEN OTHERS THEN
5744 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5745 (
5746 l_api_name,
5747 G_PKG_NAME,
5748 'OTHERS',
5749 x_msg_count,
5750 x_msg_data,
5751 '_PVT'
5752 );
5753 END delete_row;
5754 ----------------------------------------
5755 -- PL/SQL TBL delete_row for:TALV_TBL --
5756 ----------------------------------------
5757 PROCEDURE delete_row(
5758 p_api_version IN NUMBER,
5759 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5760 x_return_status OUT NOCOPY VARCHAR2,
5761 x_msg_count OUT NOCOPY NUMBER,
5762 x_msg_data OUT NOCOPY VARCHAR2,
5763 p_talv_tbl IN talv_tbl_type) IS
5764
5765 l_api_version CONSTANT NUMBER := 1;
5766 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5767 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5768 i NUMBER := 0;
5769 BEGIN
5770 OKC_API.init_msg_list(p_init_msg_list);
5771 -- Make sure PL/SQL table has records in it before passing
5772 IF (p_talv_tbl.COUNT > 0) THEN
5773 i := p_talv_tbl.FIRST;
5774 LOOP
5775 delete_row (
5776 p_api_version => p_api_version,
5777 p_init_msg_list => OKC_API.G_FALSE,
5778 x_return_status => x_return_status,
5779 x_msg_count => x_msg_count,
5780 x_msg_data => x_msg_data,
5781 p_talv_rec => p_talv_tbl(i));
5782 EXIT WHEN (i = p_talv_tbl.LAST);
5783 i := p_talv_tbl.NEXT(i);
5784 END LOOP;
5785 END IF;
5786 EXCEPTION
5787 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5788 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5789 (
5790 l_api_name,
5791 G_PKG_NAME,
5792 'OKC_API.G_RET_STS_ERROR',
5793 x_msg_count,
5794 x_msg_data,
5795 '_PVT'
5796 );
5797 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5798 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5799 (
5800 l_api_name,
5801 G_PKG_NAME,
5802 'OKC_API.G_RET_STS_UNEXP_ERROR',
5803 x_msg_count,
5804 x_msg_data,
5805 '_PVT'
5806 );
5807 WHEN OTHERS THEN
5808 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5809 (
5810 l_api_name,
5811 G_PKG_NAME,
5812 'OTHERS',
5813 x_msg_count,
5814 x_msg_data,
5815 '_PVT'
5816 );
5817 END delete_row;
5818 END OKL_TAL_PVT;