[Home] [Help]
PACKAGE BODY: APPS.OKL_TAS_PVT
Source
1 PACKAGE BODY OKL_TAS_PVT AS
2 /* $Header: OKLSTASB.pls 120.6 2008/01/17 10:10:26 veramach noship $ */
3 -- Badrinath Kuchibholta
4 /************************ HAND-CODED *********************************/
5 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
7 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
8 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
9 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
10 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
11 G_ID2 CONSTANT VARCHAR2(200) := '#';
12 G_TSU_LOOKUP_TYPE CONSTANT VARCHAR2(200) := 'OKL_TRANSACTION_STATUS';
13 G_TAS_LOOKUP_TYPE CONSTANT VARCHAR2(200) := 'OKL_TRANS_HEADER_TYPE';
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
16 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
17 -------------------------------------------------------------------------------------
18 -- Start of Commnets
19 -- Badrinath Kuchibholta
20 -- Procedure Name : Validate_ica_id
21 -- Description : FK validation with OKX_CUSTOMER_ACCOUNTS_V
22 -- Business Rules :
23 -- Parameters : OUT Return Status, IN Rec Info
24 -- Version : 1.0
25 -- End of Commnets
26
27 PROCEDURE validate_ica_id(x_return_status OUT NOCOPY VARCHAR2,
28 p_tasv_rec IN tasv_rec_type) IS
29 ln_dummy NUMBER := 0;
30 CURSOR c_ica_validate(p_id number)
31 IS
32 SELECT 1
33 FROM DUAL
34 WHERE EXISTS (SELECT ID1
35 FROM OKX_CUSTOMER_ACCOUNTS_V
36 WHERE id1 = p_id
37 AND id2 = G_ID2);
38 BEGIN
39 -- initialize return status
40 x_return_status := OKC_API.G_RET_STS_SUCCESS;
41 -- data is required
42 IF (p_tasv_rec.ica_id = OKC_API.G_MISS_NUM) OR
43 (p_tasv_rec.ica_id IS NULL) THEN
44 -- halt validation as it is a optional field
45 RAISE G_EXCEPTION_STOP_VALIDATION;
46 END IF;
47 -- Enforce Foreign Key
48 OPEN c_ica_validate(p_tasv_rec.ica_id);
49 IF c_ica_validate%NOTFOUND THEN
50 -- halt validation as it has no parent record
51 RAISE G_EXCEPTION_HALT_VALIDATION;
52 END IF;
53 FETCH c_ica_validate into ln_dummy;
54 CLOSE c_ica_validate;
55 -- Checking if we have record or not
56 IF (ln_dummy = 0) then
57 -- halt validation as it has no parent record
58 RAISE G_EXCEPTION_HALT_VALIDATION;
59 END IF;
60 EXCEPTION
61 WHEN G_EXCEPTION_STOP_VALIDATION then
62 -- We are here since the field is optional
63 null;
64 WHEN G_EXCEPTION_HALT_VALIDATION then
65 -- We are here b'cause we have no parent record
66 -- store SQL error message on message stack
67 OKC_API.set_message(p_app_name => G_APP_NAME,
68 p_msg_name => G_NO_MATCHING_RECORD,
69 p_token1 => G_COL_NAME_TOKEN,
70 p_token1_value => 'ica_id');
71 -- notify caller of an error
72 IF c_ica_validate%ISOPEN THEN
73 CLOSE c_ica_validate;
74 END IF;
75 x_return_status := OKC_API.G_RET_STS_ERROR;
76 WHEN OTHERS THEN
77 -- store SQL error message on message stack
78 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
79 p_msg_name => G_UNEXPECTED_ERROR,
80 p_token1 => G_SQLCODE_TOKEN,
81 p_token1_value => SQLCODE,
82 p_token2 => G_SQLERRM_TOKEN,
83 p_token2_value => SQLERRM);
84 -- If the cursor is open then it has to be closed
85 IF c_ica_validate%ISOPEN THEN
86 CLOSE c_ica_validate;
87 END IF;
88 -- notify caller of an error as UNEXPETED error
89 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
90 END validate_ica_id;
91 ----------------------------------------------------------------------------
92 -- Start of Commnets
93 -- Badrinath Kuchibholta
94 -- Procedure Name : Validate_tas_type
95 -- Description : FK validation with FND COMMON LOOKUPS
96 -- Business Rules :
97 -- Parameters : OUT Return Status, IN Rec Info
98 -- Version : 1.0
99 -- End of Commnets
100
101 PROCEDURE validate_tas_type(x_return_status OUT NOCOPY VARCHAR2,
102 p_tasv_rec IN tasv_rec_type) IS
103 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
104 BEGIN
105 -- initialize return status
106 x_return_status := OKC_API.G_RET_STS_SUCCESS;
107 -- data is required
108 IF (p_tasv_rec.tas_type = OKC_API.G_MISS_CHAR) OR
109 (p_tasv_rec.tas_type IS NULL) THEN
110 -- halt validation
111 RAISE G_EXCEPTION_STOP_VALIDATION;
112 END IF;
113 -- Enforce Foreign Key
114 l_return_status := OKC_UTIL.check_lookup_code(G_TAS_LOOKUP_TYPE,
115 p_tasv_rec.tas_type);
116 IF l_return_status = x_return_status THEN
117 x_return_status := l_return_status;
118 ELSIF l_return_status <> x_return_status THEN
119 -- Notify Error
120 RAISE G_EXCEPTION_HALT_VALIDATION;
121 END IF;
122 EXCEPTION
123 WHEN G_EXCEPTION_STOP_VALIDATION then
124 -- We are here since the field is required
125 -- store SQL error message on message stack
126 OKC_API.set_message(p_app_namE => G_APP_NAME,
127 p_msg_name => G_REQUIRED_VALUE,
128 p_token1 => G_COL_NAME_TOKEN,
129 p_token1_value => 'tas_type');
130 -- notify caller of an error
131 x_return_status := OKC_API.G_RET_STS_ERROR;
132 WHEN G_EXCEPTION_HALT_VALIDATION then
133 -- We are here b'cause we have no parent record
134 -- store SQL error message on message stack
135 OKC_API.set_message(p_app_name => G_APP_NAME,
136 p_msg_name => G_NO_MATCHING_RECORD,
137 p_token1 => G_COL_NAME_TOKEN,
138 p_token1_value => 'tas_type');
139 -- notify caller of an error
140 x_return_status := OKC_API.G_RET_STS_ERROR;
141 WHEN OTHERS THEN
142 -- store SQL error message on message stack
143 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
144 p_msg_name => G_UNEXPECTED_ERROR,
145 p_token1 => G_SQLCODE_TOKEN,
146 p_token1_value => SQLCODE,
147 p_token2 => G_SQLERRM_TOKEN,
148 p_token2_value => SQLERRM);
149 -- notify caller of an error as UNEXPETED error
150 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
151 END validate_tas_type;
152 ----------------------------------------------------------------------------
153 -- Start of Commnets
154 -- Badrinath Kuchibholta
155 -- Procedure Name : Validate_tsu_code
156 -- Description : FK validation with FND COMMON LOOKUPS
157 -- Business Rules :
158 -- Parameters : OUT Return Status, IN Rec Info
159 -- Version : 1.0
160 -- End of Commnets
161
162 PROCEDURE validate_tsu_code(x_return_status OUT NOCOPY VARCHAR2,
163 p_tasv_rec IN tasv_rec_type) IS
164 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
165 BEGIN
166 -- initialize return status
167 x_return_status := OKC_API.G_RET_STS_SUCCESS;
168 -- data is required
169 IF (p_tasv_rec.tsu_code = OKC_API.G_MISS_CHAR) OR
170 (p_tasv_rec.tsu_code IS NULL) THEN
171 -- halt validation
172 RAISE G_EXCEPTION_STOP_VALIDATION;
173 END IF;
174 -- Enforce Foreign Key
175 l_return_status := OKC_UTIL.check_lookup_code(G_TSU_LOOKUP_TYPE,
176 p_tasv_rec.tsu_code);
177 IF l_return_status = x_return_status THEN
178 x_return_status := l_return_status;
179 ELSIF l_return_status <> x_return_status THEN
180 -- Notify Error
181 RAISE G_EXCEPTION_HALT_VALIDATION;
182 END IF;
183
184 EXCEPTION
185 WHEN G_EXCEPTION_STOP_VALIDATION then
186 -- We are here since the field is required
187 -- store SQL error message on message stack
188 OKC_API.set_message(p_app_name => G_APP_NAME,
189 p_msg_name => G_REQUIRED_VALUE,
190 p_token1 => G_COL_NAME_TOKEN,
191 p_token1_value => 'tsu_code');
192 -- Notify Error
193 x_return_status := OKC_API.G_RET_STS_ERROR;
194 WHEN G_EXCEPTION_HALT_VALIDATION then
195 -- We are here b'cause we have no parent record
196 -- store SQL error message on message stack
197 OKC_API.set_message(p_app_name => G_APP_NAME,
198 p_msg_name => G_NO_MATCHING_RECORD,
199 p_token1 => G_COL_NAME_TOKEN,
200 p_token1_value => 'tsu_code');
201 -- notify caller of an error
202 x_return_status := OKC_API.G_RET_STS_ERROR;
203 WHEN OTHERS THEN
204 -- store SQL error message on message stack
205 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
206 p_msg_name => G_UNEXPECTED_ERROR,
207 p_token1 => G_SQLCODE_TOKEN,
208 p_token1_value => SQLCODE,
209 p_token2 => G_SQLERRM_TOKEN,
210 p_token2_value => SQLERRM);
211 -- notify caller of an error as UNEXPETED error
212 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
213 END validate_tsu_code;
214 -------------------------------------------------------------------------------
215 -- Start of Commnets
216 -- Badrinath Kuchibholta
217 -- Procedure Name : Validate_try_id
218 -- Description : FK validation with OKL_TRX_TYPES_V
219 -- Business Rules :
220 -- Parameters : OUT Return Status, IN Rec Info
221 -- Version : 1.0
222 -- End of Commnets
223
224 PROCEDURE validate_try_id(x_return_status OUT NOCOPY VARCHAR2,
225 p_tasv_rec IN tasv_rec_type) IS
226
227 CURSOR c_try_id_validate(p_id number)
228 is
229 SELECT 1
230 FROM DUAl
231 WHERE EXISTS (SELECT id
232 FROM OKL_TRX_TYPES_B
233 WHERE id = p_id);
234
235 ln_dummy number := 0;
236 BEGIN
237 -- initialize return status
238 x_return_status := OKC_API.G_RET_STS_SUCCESS;
239 -- data is required
240 IF (p_tasv_rec.try_id = OKC_API.G_MISS_NUM) OR
241 (p_tasv_rec.try_id IS NULL) THEN
242 -- halt validation
243 RAISE G_EXCEPTION_STOP_VALIDATION;
244 END IF;
245 -- Enforce Foreign Key
246 OPEN c_try_id_validate(p_tasv_rec.try_id);
247 -- If the cursor is open then it has to be closed
248 IF c_try_id_validate%NOTFOUND THEN
249 close c_try_id_validate;
250 END IF;
251 FETCH c_try_id_validate into ln_dummy;
252 CLOSE c_try_id_validate;
253 IF (ln_dummy = 0) then
254 -- notify caller of an error
255 RAISE G_EXCEPTION_HALT_VALIDATION;
256 END IF;
257 EXCEPTION
258 WHEN G_EXCEPTION_STOP_VALIDATION then
259 -- We are here since the field is required
260 -- store SQL error message on message stack
261 OKC_API.set_message(p_app_name => G_APP_NAME,
262 p_msg_name => G_REQUIRED_VALUE,
263 p_token1 => G_COL_NAME_TOKEN,
264 p_token1_value => 'try_id');
265 -- notify caller of an error
266 x_return_status := OKC_API.G_RET_STS_ERROR;
267 WHEN G_EXCEPTION_HALT_VALIDATION then
268 -- We are here b'cause we have no parent record
269 -- store SQL error message on message stack
270 OKC_API.set_message(p_app_name => G_APP_NAME,
271 p_msg_name => G_NO_MATCHING_RECORD,
272 p_token1 => G_COL_NAME_TOKEN,
273 p_token1_value => 'try_id');
274 -- If the cursor is open then it has to be closed
275 IF c_try_id_validate%ISOPEN THEN
276 close c_try_id_validate;
277 END IF;
278 -- notify caller of an error
279 x_return_status := OKC_API.G_RET_STS_ERROR;
280 WHEN OTHERS THEN
281 -- store SQL error message on message stack
282 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
283 p_msg_name => G_UNEXPECTED_ERROR,
284 p_token1 => G_SQLCODE_TOKEN,
285 p_token1_value => SQLCODE,
286 p_token2 => G_SQLERRM_TOKEN,
287 p_token2_value => SQLERRM);
288 -- If the cursor is open then it has to be closed
289 IF c_try_id_validate%ISOPEN THEN
290 close c_try_id_validate;
291 END IF;
292 -- notify caller of an error as UNEXPETED error
293 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
294 END validate_try_id;
295 -------------------------------------------------------------------------------------
296 -- Start of Commnets
297 -- Bug # 2697681 - 11.5.9 schema changes avsingh
298 -- Procedure Name : Validate_org_id
299 -- Description : FK validation with HR_OPERATING_UNITS
300 -- Business Rules :
301 -- Parameters : OUT Return Status, IN Rec Info
302 -- Version : 1.0
303 -- End of Commnets
304
305 PROCEDURE validate_org_id(x_return_status OUT NOCOPY VARCHAR2,
306 p_tasv_rec IN tasv_rec_type) IS
307 ln_dummy NUMBER := 0;
308 CURSOR c_org_validate(p_id number)
309 IS
310 SELECT 1
311 FROM DUAL
312 WHERE EXISTS (SELECT Organization_id
313 FROM HR_OPERATING_UNITS
314 WHERE Organization_id = p_id);
315 BEGIN
316 -- initialize return status
317 x_return_status := OKC_API.G_RET_STS_SUCCESS;
318 -- data is required
319 IF (p_tasv_rec.org_id = OKC_API.G_MISS_NUM) OR
320 (p_tasv_rec.org_id IS NULL) THEN
321 -- halt validation as it is a optional field
322 RAISE G_EXCEPTION_STOP_VALIDATION;
323 END IF;
324 -- Enforce Foreign Key
325 OPEN c_org_validate(p_tasv_rec.org_id);
326 IF c_org_validate%NOTFOUND THEN
327 -- halt validation as it has no parent record
328 RAISE G_EXCEPTION_HALT_VALIDATION;
329 END IF;
330 FETCH c_org_validate into ln_dummy;
331 CLOSE c_org_validate;
332 -- Checking if we have record or not
333 IF (ln_dummy = 0) then
334 -- halt validation as it has no parent record
335 RAISE G_EXCEPTION_HALT_VALIDATION;
336 END IF;
337 EXCEPTION
338 WHEN G_EXCEPTION_STOP_VALIDATION then
339 -- We are here since the field is optional
340 null;
341 WHEN G_EXCEPTION_HALT_VALIDATION then
342 -- We are here b'cause we have no parent record
343 -- store SQL error message on message stack
344 OKC_API.set_message(p_app_name => G_APP_NAME,
345 p_msg_name => G_NO_MATCHING_RECORD,
346 p_token1 => G_COL_NAME_TOKEN,
347 p_token1_value => 'org_id');
348 -- notify caller of an error
349 IF c_org_validate%ISOPEN THEN
350 CLOSE c_org_validate;
351 END IF;
352 x_return_status := OKC_API.G_RET_STS_ERROR;
353 WHEN OTHERS THEN
354 -- store SQL error message on message stack
355 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
356 p_msg_name => G_UNEXPECTED_ERROR,
357 p_token1 => G_SQLCODE_TOKEN,
358 p_token1_value => SQLCODE,
359 p_token2 => G_SQLERRM_TOKEN,
360 p_token2_value => SQLERRM);
361 -- If the cursor is open then it has to be closed
362 IF c_org_validate%ISOPEN THEN
363 CLOSE c_org_validate;
364 END IF;
365 -- notify caller of an error as UNEXPETED error
366 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
367 END validate_org_id;
368
369 -- Added by dpsingh
370 ---------------------------------------------------------------------------
371 -- PROCEDURE Validate_LE_Id
372 ---------------------------------------------------------------------------
373 -- Start of comments
374 --
375 -- Procedure Name : Validate_LE_Id
376 -- Description :
377 -- Business Rules :
378 -- Parameters :
379 -- Version : 1.0
380 -- End of comments
381 ---------------------------------------------------------------------------
382 PROCEDURE Validate_LE_Id(p_tasv_rec IN tasv_rec_type
383 ,x_return_status OUT NOCOPY VARCHAR2)
384 IS
385 l_exists NUMBER(1);
386 item_not_found_error EXCEPTION;
387
388 BEGIN
389
390 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
391
392 IF (p_tasv_rec.legal_entity_id IS NOT NULL) AND
393 (p_tasv_rec.legal_entity_id <> Okl_Api.G_MISS_NUM) THEN
394 l_exists := OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_tasv_rec.legal_entity_id) ;
395 IF (l_exists<>1) THEN
396 Okc_Api.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LEGAL_ENTITY_ID');
397 RAISE item_not_found_error;
398 END IF;
399 END IF;
400
401 EXCEPTION
402 WHEN item_not_found_error THEN
403 x_return_status := Okc_Api.G_RET_STS_ERROR;
404
405 WHEN OTHERS THEN
406 -- store SQL error message on message stack for caller
407 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
408 ,p_msg_name => g_unexpected_error
409 ,p_token1 => g_sqlcode_token
410 ,p_token1_value => SQLCODE
411 ,p_token2 => g_sqlerrm_token
412 ,p_token2_value => SQLERRM);
413
414 -- notify caller of an UNEXPECTED error
415 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
416
417 END Validate_LE_Id;
418
419 /************************ HAND-CODED *********************************/
420 ---------------------------------------------------------------------------
421 -- FUNCTION get_seq_id
422 ---------------------------------------------------------------------------
423 FUNCTION get_seq_id RETURN NUMBER IS
424 BEGIN
425 RETURN(okc_p_util.raw_to_number(sys_guid()));
426 END get_seq_id;
427
428 ---------------------------------------------------------------------------
429 -- PROCEDURE qc
430 ---------------------------------------------------------------------------
431 PROCEDURE qc IS
432 BEGIN
433 null;
434 END qc;
435
436 ---------------------------------------------------------------------------
437 -- PROCEDURE change_version
438 ---------------------------------------------------------------------------
439 PROCEDURE change_version IS
440 BEGIN
441 null;
442 END change_version;
443
444 ---------------------------------------------------------------------------
445 -- PROCEDURE api_copy
446 ---------------------------------------------------------------------------
447 PROCEDURE api_copy IS
448 BEGIN
449 null;
450 END api_copy;
451
452 ---------------------------------------------------------------------------
453 -- FUNCTION get_rec for: OKL_TRX_ASSETS
454 ---------------------------------------------------------------------------
455 FUNCTION get_rec (
456 p_tas_rec IN tas_rec_type,
457 x_no_data_found OUT NOCOPY BOOLEAN
458 ) RETURN tas_rec_type IS
459 CURSOR okl_trx_assets_pk_csr (p_id IN NUMBER) IS
460 SELECT
461 ID,
462 ICA_ID,
463 TAS_TYPE,
464 OBJECT_VERSION_NUMBER,
465 ATTRIBUTE_CATEGORY,
466 ATTRIBUTE1,
467 ATTRIBUTE2,
468 ATTRIBUTE3,
469 ATTRIBUTE4,
470 ATTRIBUTE5,
471 ATTRIBUTE6,
472 ATTRIBUTE7,
473 ATTRIBUTE8,
474 ATTRIBUTE9,
475 ATTRIBUTE10,
476 ATTRIBUTE11,
477 ATTRIBUTE12,
478 ATTRIBUTE13,
479 ATTRIBUTE14,
480 ATTRIBUTE15,
481 CREATED_BY,
482 CREATION_DATE,
483 LAST_UPDATED_BY,
484 LAST_UPDATE_DATE,
485 LAST_UPDATE_LOGIN,
486 TSU_CODE,
487 TRY_ID,
488 DATE_TRANS_OCCURRED,
489 TRANS_NUMBER,
490 COMMENTS,
491 REQ_ASSET_ID,
492 TOTAL_MATCH_AMOUNT,
493 --Bug # 2697681 - 11.5.9 schema change
494 ORG_ID,
495 --Added by dpsingh for LE uptake
496 LEGAL_ENTITY_ID
497 ,TRANSACTION_DATE
498 FROM Okl_Trx_Assets
499 WHERE okl_trx_assets.id = p_id;
500 l_okl_trx_assets_pk okl_trx_assets_pk_csr%ROWTYPE;
501 l_tas_rec tas_rec_type;
502 BEGIN
503 x_no_data_found := TRUE;
504 -- Get current database values
505 OPEN okl_trx_assets_pk_csr (p_tas_rec.id);
506 FETCH okl_trx_assets_pk_csr INTO
507 l_tas_rec.ID,
508 l_tas_rec.ICA_ID,
509 l_tas_rec.TAS_TYPE,
510 l_tas_rec.OBJECT_VERSION_NUMBER,
511 l_tas_rec.ATTRIBUTE_CATEGORY,
512 l_tas_rec.ATTRIBUTE1,
513 l_tas_rec.ATTRIBUTE2,
514 l_tas_rec.ATTRIBUTE3,
515 l_tas_rec.ATTRIBUTE4,
516 l_tas_rec.ATTRIBUTE5,
517 l_tas_rec.ATTRIBUTE6,
518 l_tas_rec.ATTRIBUTE7,
519 l_tas_rec.ATTRIBUTE8,
520 l_tas_rec.ATTRIBUTE9,
521 l_tas_rec.ATTRIBUTE10,
522 l_tas_rec.ATTRIBUTE11,
523 l_tas_rec.ATTRIBUTE12,
524 l_tas_rec.ATTRIBUTE13,
525 l_tas_rec.ATTRIBUTE14,
526 l_tas_rec.ATTRIBUTE15,
527 l_tas_rec.CREATED_BY,
528 l_tas_rec.CREATION_DATE,
529 l_tas_rec.LAST_UPDATED_BY,
530 l_tas_rec.LAST_UPDATE_DATE,
531 l_tas_rec.LAST_UPDATE_LOGIN,
532 l_tas_rec.TSU_CODE,
533 l_tas_rec.TRY_ID,
534 l_tas_rec.DATE_TRANS_OCCURRED,
535 l_tas_rec.TRANS_NUMBER,
536 l_tas_rec.COMMENTS,
537 l_tas_rec.REQ_ASSET_ID,
538 l_tas_rec.TOTAL_MATCH_AMOUNT,
539 --Bug # 2697681 - 11.5.9 Schema change
540 l_tas_rec.ORG_ID,
541 --Added by dpsingh for LE uptake
542 l_tas_rec.LEGAL_ENTITY_ID
543 ,l_tas_rec.TRANSACTION_DATE;
544 x_no_data_found := okl_trx_assets_pk_csr%NOTFOUND;
545 CLOSE okl_trx_assets_pk_csr;
546 RETURN(l_tas_rec);
547 END get_rec;
548
549 FUNCTION get_rec (
550 p_tas_rec IN tas_rec_type
551 ) RETURN tas_rec_type IS
552 l_row_notfound BOOLEAN := TRUE;
553 BEGIN
554 RETURN(get_rec(p_tas_rec, l_row_notfound));
555 END get_rec;
556 ---------------------------------------------------------------------------
557 -- FUNCTION get_rec for: OKL_TRX_ASSETS_V
558 ---------------------------------------------------------------------------
559 FUNCTION get_rec (
560 p_tasv_rec IN tasv_rec_type,
561 x_no_data_found OUT NOCOPY BOOLEAN
562 ) RETURN tasv_rec_type IS
563 CURSOR okl_tasv_pk_csr (p_id IN NUMBER) IS
564 SELECT
565 ID,
566 OBJECT_VERSION_NUMBER,
567 ICA_ID,
568 ATTRIBUTE_CATEGORY,
569 ATTRIBUTE1,
570 ATTRIBUTE2,
571 ATTRIBUTE3,
572 ATTRIBUTE4,
573 ATTRIBUTE5,
574 ATTRIBUTE6,
575 ATTRIBUTE7,
576 ATTRIBUTE8,
577 ATTRIBUTE9,
578 ATTRIBUTE10,
579 ATTRIBUTE11,
580 ATTRIBUTE12,
581 ATTRIBUTE13,
582 ATTRIBUTE14,
583 ATTRIBUTE15,
584 TAS_TYPE,
585 CREATED_BY,
586 CREATION_DATE,
587 LAST_UPDATED_BY,
588 LAST_UPDATE_DATE,
589 LAST_UPDATE_LOGIN,
590 TSU_CODE,
591 TRY_ID,
592 DATE_TRANS_OCCURRED,
593 TRANS_NUMBER,
594 COMMENTS,
595 REQ_ASSET_ID,
596 TOTAL_MATCH_AMOUNT,
597 --Bug # 2697681 : 11.5.9 schema change
598 ORG_ID ,
599 --Added by dpsingh for LE uptake
600 LEGAL_ENTITY_ID
601 ,TRANSACTION_DATE
602 FROM OKL_TRX_ASSETS
603 WHERE OKL_TRX_ASSETS.id = p_id;
604 l_okl_tasv_pk okl_tasv_pk_csr%ROWTYPE;
605 l_tasv_rec tasv_rec_type;
606 BEGIN
607 x_no_data_found := TRUE;
608 -- Get current database values
609 OPEN okl_tasv_pk_csr (p_tasv_rec.id);
610 FETCH okl_tasv_pk_csr INTO
611 l_tasv_rec.ID,
612 l_tasv_rec.OBJECT_VERSION_NUMBER,
613 l_tasv_rec.ICA_ID,
614 l_tasv_rec.ATTRIBUTE_CATEGORY,
615 l_tasv_rec.ATTRIBUTE1,
616 l_tasv_rec.ATTRIBUTE2,
617 l_tasv_rec.ATTRIBUTE3,
618 l_tasv_rec.ATTRIBUTE4,
619 l_tasv_rec.ATTRIBUTE5,
620 l_tasv_rec.ATTRIBUTE6,
621 l_tasv_rec.ATTRIBUTE7,
622 l_tasv_rec.ATTRIBUTE8,
623 l_tasv_rec.ATTRIBUTE9,
624 l_tasv_rec.ATTRIBUTE10,
625 l_tasv_rec.ATTRIBUTE11,
626 l_tasv_rec.ATTRIBUTE12,
627 l_tasv_rec.ATTRIBUTE13,
628 l_tasv_rec.ATTRIBUTE14,
629 l_tasv_rec.ATTRIBUTE15,
630 l_tasv_rec.TAS_TYPE,
631 l_tasv_rec.CREATED_BY,
632 l_tasv_rec.CREATION_DATE,
633 l_tasv_rec.LAST_UPDATED_BY,
634 l_tasv_rec.LAST_UPDATE_DATE,
635 l_tasv_rec.LAST_UPDATE_LOGIN,
636 l_tasv_rec.TSU_CODE,
637 l_tasv_rec.TRY_ID,
638 l_tasv_rec.DATE_TRANS_OCCURRED,
639 l_tasv_rec.TRANS_NUMBER,
640 l_tasv_rec.COMMENTS,
641 l_tasv_rec.REQ_ASSET_ID,
642 l_tasv_rec.TOTAL_MATCH_AMOUNT,
643 --Bug# 2697681 : 11.5.9 Schema changes
644 l_tasv_rec.ORG_ID,
645 --Added by dpsingh for LE uptake
646 l_tasv_rec.LEGAL_ENTITY_ID
647 ,l_tasv_rec.TRANSACTION_DATE;
648 x_no_data_found := okl_tasv_pk_csr%NOTFOUND;
649 CLOSE okl_tasv_pk_csr;
650 RETURN(l_tasv_rec);
651 END get_rec;
652
653 FUNCTION get_rec (
654 p_tasv_rec IN tasv_rec_type
655 ) RETURN tasv_rec_type IS
656 l_row_notfound BOOLEAN := TRUE;
657 BEGIN
658 RETURN(get_rec(p_tasv_rec, l_row_notfound));
659 END get_rec;
660
661 ------------------------------------------------------
662 -- FUNCTION null_out_defaults for: OKL_TRX_ASSETS_V --
663 ------------------------------------------------------
664 FUNCTION null_out_defaults (
665 p_tasv_rec IN tasv_rec_type
666 ) RETURN tasv_rec_type IS
667 l_tasv_rec tasv_rec_type := p_tasv_rec;
668 BEGIN
669 IF (l_tasv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
670 l_tasv_rec.object_version_number := NULL;
671 END IF;
672 IF (l_tasv_rec.ica_id = OKC_API.G_MISS_NUM) THEN
673 l_tasv_rec.ica_id := NULL;
674 END IF;
675 IF (l_tasv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
676 l_tasv_rec.attribute_category := NULL;
677 END IF;
678 IF (l_tasv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
679 l_tasv_rec.attribute1 := NULL;
680 END IF;
681 IF (l_tasv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
682 l_tasv_rec.attribute2 := NULL;
683 END IF;
684 IF (l_tasv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
685 l_tasv_rec.attribute3 := NULL;
686 END IF;
687 IF (l_tasv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
688 l_tasv_rec.attribute4 := NULL;
689 END IF;
690 IF (l_tasv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
691 l_tasv_rec.attribute5 := NULL;
692 END IF;
693 IF (l_tasv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
694 l_tasv_rec.attribute6 := NULL;
695 END IF;
696 IF (l_tasv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
697 l_tasv_rec.attribute7 := NULL;
698 END IF;
699 IF (l_tasv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
700 l_tasv_rec.attribute8 := NULL;
701 END IF;
702 IF (l_tasv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
703 l_tasv_rec.attribute9 := NULL;
704 END IF;
705 IF (l_tasv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
706 l_tasv_rec.attribute10 := NULL;
707 END IF;
708 IF (l_tasv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
709 l_tasv_rec.attribute11 := NULL;
710 END IF;
711 IF (l_tasv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
712 l_tasv_rec.attribute12 := NULL;
713 END IF;
714 IF (l_tasv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
715 l_tasv_rec.attribute13 := NULL;
716 END IF;
717 IF (l_tasv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
718 l_tasv_rec.attribute14 := NULL;
719 END IF;
720 IF (l_tasv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
721 l_tasv_rec.attribute15 := NULL;
722 END IF;
723 IF (l_tasv_rec.tas_type = OKC_API.G_MISS_CHAR) THEN
724 l_tasv_rec.tas_type := NULL;
725 END IF;
726 IF (l_tasv_rec.created_by = OKC_API.G_MISS_NUM) THEN
727 l_tasv_rec.created_by := NULL;
728 END IF;
729 IF (l_tasv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
730 l_tasv_rec.creation_date := NULL;
731 END IF;
732 IF (l_tasv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
733 l_tasv_rec.last_updated_by := NULL;
734 END IF;
735 IF (l_tasv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
736 l_tasv_rec.last_update_date := NULL;
737 END IF;
738 IF (l_tasv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
739 l_tasv_rec.last_update_login := NULL;
740 END IF;
741 IF (l_tasv_rec.tsu_code = OKC_API.G_MISS_CHAR) THEN
742 l_tasv_rec.tsu_code := NULL;
743 END IF;
744 IF (l_tasv_rec.try_id = OKC_API.G_MISS_NUM) THEN
745 l_tasv_rec.try_id := NULL;
746 END IF;
747 IF (l_tasv_rec.date_trans_occurred = OKC_API.G_MISS_DATE) THEN
748 l_tasv_rec.date_trans_occurred := NULL;
749 END IF;
750 IF (l_tasv_rec.trans_number = OKC_API.G_MISS_NUM) THEN
751 l_tasv_rec.trans_number := NULL;
752 END IF;
753 IF (l_tasv_rec.comments = OKC_API.G_MISS_CHAR) THEN
754 l_tasv_rec.comments := NULL;
755 END IF;
756 IF (l_tasv_rec.req_asset_id = OKC_API.G_MISS_NUM) THEN
757 l_tasv_rec.req_asset_id := NULL;
758 END IF;
759 IF (l_tasv_rec.total_match_amount = OKC_API.G_MISS_NUM) THEN
760 l_tasv_rec.total_match_amount := NULL;
761 END IF;
762 --Bug# 2697681 : 11.5.9 Schema changes
763 IF (l_tasv_rec.org_id = OKC_API.G_MISS_NUM) THEN
764 l_tasv_rec.org_id := NULL;
765 END IF;
766 --Added by dpsingh for LE uptake
767 IF (l_tasv_rec.LEGAL_ENTITY_ID = OKL_API.G_MISS_NUM) THEN
768 l_tasv_rec.LEGAL_ENTITY_ID := NULL;
769 END IF;
770 IF (l_tasv_rec.TRANSACTION_DATE = OKL_API.G_MISS_DATE) THEN
771 l_tasv_rec.TRANSACTION_DATE := NULL;
772 END IF;
773 RETURN(l_tasv_rec);
774 END null_out_defaults;
775 ---------------------------------------------------------------------------
776 -- PROCEDURE Validate_Attributes
777 ---------------------------------------------------------------------------
778 ----------------------------------------------
779 -- Validate_Attributes for:OKL_TRX_ASSETS_V --
780 ----------------------------------------------
781 FUNCTION Validate_Attributes (
782 p_tasv_rec IN tasv_rec_type
783 ) RETURN VARCHAR2 IS
784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
785 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
786 BEGIN
787 IF p_tasv_rec.id = OKC_API.G_MISS_NUM OR
788 p_tasv_rec.id IS NULL 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_REQUIRED_VALUE,
792 p_token1 => G_COL_NAME_TOKEN,
793 p_token1_value => 'id');
794 -- notify caller of an error
795 x_return_status := OKC_API.G_RET_STS_ERROR;
796 ELSIF p_tasv_rec.object_version_number = OKC_API.G_MISS_NUM OR
797 p_tasv_rec.object_version_number IS NULL THEN
798 -- store SQL error message on message stack
799 OKC_API.set_message(p_app_name => G_APP_NAME,
800 p_msg_name => G_REQUIRED_VALUE,
801 p_token1 => G_COL_NAME_TOKEN,
802 p_token1_value => 'object_version_number');
803 -- notify caller of an error
804 x_return_status := OKC_API.G_RET_STS_ERROR;
805 ELSIF p_tasv_rec.date_trans_occurred = OKC_API.G_MISS_DATE OR
806 p_tasv_rec.date_trans_occurred IS NULL THEN
807 -- store SQL error message on message stack
808 OKC_API.set_message(p_app_name => G_APP_NAME,
809 p_msg_name => G_REQUIRED_VALUE,
810 p_token1 => G_COL_NAME_TOKEN,
811 p_token1_value => 'date_trans_occurred');
812 -- notify caller of an error
813 x_return_status := OKC_API.G_RET_STS_ERROR;
814 END IF;
815 /************************ HAND-CODED *********************************/
816 -- Calling the Validate Procedure to validate Individual Attributes
817 validate_ica_id(x_return_status => l_return_status,
818 p_tasv_rec => p_tasv_rec);
819 -- Store the Highest Degree of Error
820 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
821 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
822 x_return_status := l_return_status;
823 END IF;
824 END IF;
825 l_return_status := x_return_status;
826 validate_tas_type(x_return_status => l_return_status,
827 p_tasv_rec => p_tasv_rec);
828 -- Store the Highest Degree of Error
829 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
830 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
831 x_return_status := l_return_status;
832 END IF;
833 END IF;
834 l_return_status := x_return_status;
835 validate_tsu_code(x_return_status => l_return_status,
836 p_tasv_rec => p_tasv_rec);
837 -- Store the Highest Degree of Error
838 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
839 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
840 x_return_status := l_return_status;
841 END IF;
842 END IF;
843 l_return_status := x_return_status;
844 validate_try_id(x_return_status => l_return_status,
845 p_tasv_rec => p_tasv_rec);
846 -- Store the Highest Degree of Error
847 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
848 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
849 x_return_status := l_return_status;
850 END IF;
851 END IF;
852 l_return_status := x_return_status;
853 --Added by dpsingh for LE uptake
854 Validate_LE_Id(p_tasv_rec => p_tasv_rec,
855 x_return_status => l_return_status);
856 -- Store the Highest Degree of Error
857 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
858 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
859 x_return_status := l_return_status;
860 END IF;
861 END IF;
862 l_return_status := x_return_status;
863 RETURN(l_return_status);
864 EXCEPTION
865 WHEN OTHERS THEN
866 -- store SQL error message on message stack
867 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
868 p_msg_name => G_UNEXPECTED_ERROR,
869 p_token1 => G_SQLCODE_TOKEN,
870 p_token1_value => SQLCODE,
871 p_token2 => G_SQLERRM_TOKEN,
872 p_token2_value => SQLERRM);
873 -- notify caller of an error as UNEXPETED error
874 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
875 -- Return status to caller
876 RETURN(x_return_status);
877 END Validate_Attributes;
878 /************************ HAND-CODED *********************************/
879 ---------------------------------------------------------------------------
880 -- PROCEDURE Validate_Record
881 ---------------------------------------------------------------------------
882 ------------------------------------------
883 -- Validate_Record for:OKL_TRX_ASSETS_V --
884 ------------------------------------------
885 FUNCTION Validate_Record (
886 p_tasv_rec IN tasv_rec_type
887 ) RETURN VARCHAR2 IS
888 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
889 BEGIN
890 RETURN (l_return_status);
891 END Validate_Record;
892
893 ---------------------------------------------------------------------------
894 -- PROCEDURE Migrate
895 ---------------------------------------------------------------------------
896 PROCEDURE migrate (
897 p_from IN tasv_rec_type,
898 p_to OUT NOCOPY tas_rec_type
899 ) IS
900 BEGIN
901 p_to.id := p_from.id;
902 p_to.ica_id := p_from.ica_id;
903 p_to.tas_type := p_from.tas_type;
904 p_to.object_version_number := p_from.object_version_number;
905 p_to.attribute_category := p_from.attribute_category;
906 p_to.attribute1 := p_from.attribute1;
907 p_to.attribute2 := p_from.attribute2;
908 p_to.attribute3 := p_from.attribute3;
909 p_to.attribute4 := p_from.attribute4;
910 p_to.attribute5 := p_from.attribute5;
911 p_to.attribute6 := p_from.attribute6;
912 p_to.attribute7 := p_from.attribute7;
913 p_to.attribute8 := p_from.attribute8;
914 p_to.attribute9 := p_from.attribute9;
915 p_to.attribute10 := p_from.attribute10;
916 p_to.attribute11 := p_from.attribute11;
917 p_to.attribute12 := p_from.attribute12;
918 p_to.attribute13 := p_from.attribute13;
919 p_to.attribute14 := p_from.attribute14;
920 p_to.attribute15 := p_from.attribute15;
921 p_to.created_by := p_from.created_by;
922 p_to.creation_date := p_from.creation_date;
923 p_to.last_updated_by := p_from.last_updated_by;
924 p_to.last_update_date := p_from.last_update_date;
925 p_to.last_update_login := p_from.last_update_login;
926 p_to.tsu_code := p_from.tsu_code;
927 p_to.try_id := p_from.try_id;
928 p_to.date_trans_occurred := p_from.date_trans_occurred;
929 p_to.trans_number := p_from.trans_number;
930 p_to.comments := p_from.comments;
931 p_to.req_asset_id := p_from.req_asset_id;
932 p_to.total_match_amount := p_from.total_match_amount;
933 --Bug# 2697681 : 11.5.9 Schema changes
934 p_to.org_id := p_from.org_id;
935 --Added by dpsingh for LE uptake
936 p_to.LEGAL_ENTITY_ID := p_from.LEGAL_ENTITY_ID;
937 p_to.TRANSACTION_DATE := p_from.TRANSACTION_DATE;
938 END migrate;
939 PROCEDURE migrate (
940 p_from IN tas_rec_type,
941 p_to OUT NOCOPY tasv_rec_type
942 ) IS
943 BEGIN
944 p_to.id := p_from.id;
945 p_to.ica_id := p_from.ica_id;
946 p_to.tas_type := p_from.tas_type;
947 p_to.object_version_number := p_from.object_version_number;
948 p_to.attribute_category := p_from.attribute_category;
949 p_to.attribute1 := p_from.attribute1;
950 p_to.attribute2 := p_from.attribute2;
951 p_to.attribute3 := p_from.attribute3;
952 p_to.attribute4 := p_from.attribute4;
953 p_to.attribute5 := p_from.attribute5;
954 p_to.attribute6 := p_from.attribute6;
955 p_to.attribute7 := p_from.attribute7;
956 p_to.attribute8 := p_from.attribute8;
957 p_to.attribute9 := p_from.attribute9;
958 p_to.attribute10 := p_from.attribute10;
959 p_to.attribute11 := p_from.attribute11;
960 p_to.attribute12 := p_from.attribute12;
961 p_to.attribute13 := p_from.attribute13;
962 p_to.attribute14 := p_from.attribute14;
963 p_to.attribute15 := p_from.attribute15;
964 p_to.created_by := p_from.created_by;
965 p_to.creation_date := p_from.creation_date;
966 p_to.last_updated_by := p_from.last_updated_by;
967 p_to.last_update_date := p_from.last_update_date;
968 p_to.last_update_login := p_from.last_update_login;
969 p_to.tsu_code := p_from.tsu_code;
970 p_to.try_id := p_from.try_id;
971 p_to.date_trans_occurred := p_from.date_trans_occurred;
972 p_to.trans_number := p_from.trans_number;
973 p_to.comments := p_from.comments;
974 p_to.req_asset_id := p_from.req_asset_id;
975 p_to.total_match_amount := p_from.total_match_amount;
976 --Bug# 2697681 : 11.5.9 Schema changes
977 p_to.org_id := p_from.org_id;
978 --Added by dpsingh for LE uptake
979 p_to.LEGAL_ENTITY_ID := p_from.LEGAL_ENTITY_ID;
980 p_to.TRANSACTION_DATE := p_from.TRANSACTION_DATE;
981 END migrate;
982
983 ---------------------------------------------------------------------------
984 -- PROCEDURE validate_row
985 ---------------------------------------------------------------------------
986 ---------------------------------------
987 -- validate_row for:OKL_TRX_ASSETS_V --
988 ---------------------------------------
989 PROCEDURE validate_row(
990 p_api_version IN NUMBER,
991 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
992 x_return_status OUT NOCOPY VARCHAR2,
993 x_msg_count OUT NOCOPY NUMBER,
994 x_msg_data OUT NOCOPY VARCHAR2,
995 p_tasv_rec IN tasv_rec_type) IS
996
997 l_api_version CONSTANT NUMBER := 1;
998 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
999 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1000 l_tasv_rec tasv_rec_type := p_tasv_rec;
1001 l_tas_rec tas_rec_type;
1002 BEGIN
1003 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1004 G_PKG_NAME,
1005 p_init_msg_list,
1006 l_api_version,
1007 p_api_version,
1008 '_PVT',
1009 x_return_status);
1010 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1011 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1012 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1013 RAISE OKC_API.G_EXCEPTION_ERROR;
1014 END IF;
1015 --- Validate all non-missing attributes (Item Level Validation)
1016 l_return_status := Validate_Attributes(l_tasv_rec);
1017 --- If any errors happen abort API
1018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1019 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1021 RAISE OKC_API.G_EXCEPTION_ERROR;
1022 END IF;
1023 l_return_status := Validate_Record(l_tasv_rec);
1024 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1025 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1027 RAISE OKC_API.G_EXCEPTION_ERROR;
1028 END IF;
1029 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1030 EXCEPTION
1031 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1032 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1033 (
1034 l_api_name,
1035 G_PKG_NAME,
1036 'OKC_API.G_RET_STS_ERROR',
1037 x_msg_count,
1038 x_msg_data,
1039 '_PVT'
1040 );
1041 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1042 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1043 (
1044 l_api_name,
1045 G_PKG_NAME,
1046 'OKC_API.G_RET_STS_UNEXP_ERROR',
1047 x_msg_count,
1048 x_msg_data,
1049 '_PVT'
1050 );
1051 WHEN OTHERS THEN
1052 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1053 (
1054 l_api_name,
1055 G_PKG_NAME,
1056 'OTHERS',
1057 x_msg_count,
1058 x_msg_data,
1059 '_PVT'
1060 );
1061 END validate_row;
1062 ------------------------------------------
1063 -- PL/SQL TBL validate_row for:TASV_TBL --
1064 ------------------------------------------
1065 PROCEDURE validate_row(
1066 p_api_version IN NUMBER,
1067 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2,
1071 p_tasv_tbl IN tasv_tbl_type) IS
1072
1073 l_api_version CONSTANT NUMBER := 1;
1074 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1075 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1076 i NUMBER := 0;
1077 BEGIN
1078 OKC_API.init_msg_list(p_init_msg_list);
1079 -- Make sure PL/SQL table has records in it before passing
1080 IF (p_tasv_tbl.COUNT > 0) THEN
1081 i := p_tasv_tbl.FIRST;
1082 LOOP
1083 validate_row (
1084 p_api_version => p_api_version,
1085 p_init_msg_list => OKC_API.G_FALSE,
1086 x_return_status => x_return_status,
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data,
1089 p_tasv_rec => p_tasv_tbl(i));
1090 EXIT WHEN (i = p_tasv_tbl.LAST);
1091 i := p_tasv_tbl.NEXT(i);
1092 END LOOP;
1093 END IF;
1094 EXCEPTION
1095 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1096 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1097 (
1098 l_api_name,
1099 G_PKG_NAME,
1100 'OKC_API.G_RET_STS_ERROR',
1101 x_msg_count,
1102 x_msg_data,
1103 '_PVT'
1104 );
1105 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1106 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1107 (
1108 l_api_name,
1109 G_PKG_NAME,
1110 'OKC_API.G_RET_STS_UNEXP_ERROR',
1111 x_msg_count,
1112 x_msg_data,
1113 '_PVT'
1114 );
1115 WHEN OTHERS THEN
1116 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1117 (
1118 l_api_name,
1119 G_PKG_NAME,
1120 'OTHERS',
1121 x_msg_count,
1122 x_msg_data,
1123 '_PVT'
1124 );
1125 END validate_row;
1126
1127 ---------------------------------------------------------------------------
1128 -- PROCEDURE insert_row
1129 ---------------------------------------------------------------------------
1130 -----------------------------------
1131 -- insert_row for:OKL_TRX_ASSETS --
1132 -----------------------------------
1133 PROCEDURE insert_row(
1134 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_count OUT NOCOPY NUMBER,
1137 x_msg_data OUT NOCOPY VARCHAR2,
1138 p_tas_rec IN tas_rec_type,
1139 x_tas_rec OUT NOCOPY tas_rec_type) IS
1140
1141 l_api_version CONSTANT NUMBER := 1;
1142 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_insert_row';
1143 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1144 l_tas_rec tas_rec_type := p_tas_rec;
1145 l_def_tas_rec tas_rec_type;
1146 ---------------------------------------
1147 -- Set_Attributes for:OKL_TRX_ASSETS --
1148 ---------------------------------------
1149 FUNCTION Set_Attributes (
1150 p_tas_rec IN tas_rec_type,
1151 x_tas_rec OUT NOCOPY tas_rec_type
1152 ) RETURN VARCHAR2 IS
1153 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1154 BEGIN
1155 x_tas_rec := p_tas_rec;
1156 RETURN(l_return_status);
1157 END Set_Attributes;
1158 BEGIN
1159 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1160 p_init_msg_list,
1161 '_PVT',
1162 x_return_status);
1163 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1164 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1165 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1166 RAISE OKC_API.G_EXCEPTION_ERROR;
1167 END IF;
1168 --- Setting item attributes
1169 l_return_status := Set_Attributes(
1170 p_tas_rec, -- IN
1171 l_tas_rec); -- OUT
1172 --- If any errors happen abort API
1173 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1174 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1176 RAISE OKC_API.G_EXCEPTION_ERROR;
1177 END IF;
1178 INSERT INTO OKL_TRX_ASSETS(
1179 id,
1180 ica_id,
1181 tas_type,
1182 object_version_number,
1183 attribute_category,
1184 attribute1,
1185 attribute2,
1186 attribute3,
1187 attribute4,
1188 attribute5,
1189 attribute6,
1190 attribute7,
1191 attribute8,
1192 attribute9,
1193 attribute10,
1194 attribute11,
1195 attribute12,
1196 attribute13,
1197 attribute14,
1198 attribute15,
1199 created_by,
1200 creation_date,
1201 last_updated_by,
1202 last_update_date,
1203 last_update_login,
1204 tsu_code,
1205 try_id,
1206 date_trans_occurred,
1207 trans_number,
1208 comments,
1209 req_asset_id,
1210 total_match_amount,
1211 --Bug# 2697681 : 11.5.9 Schema changes
1212 org_id,
1213 --Added by dpsingh for LE uptake
1214 legal_entity_id
1215 ,transaction_date)
1216 VALUES (
1217 l_tas_rec.id,
1218 l_tas_rec.ica_id,
1219 l_tas_rec.tas_type,
1220 l_tas_rec.object_version_number,
1221 l_tas_rec.attribute_category,
1222 l_tas_rec.attribute1,
1223 l_tas_rec.attribute2,
1224 l_tas_rec.attribute3,
1225 l_tas_rec.attribute4,
1226 l_tas_rec.attribute5,
1227 l_tas_rec.attribute6,
1228 l_tas_rec.attribute7,
1229 l_tas_rec.attribute8,
1230 l_tas_rec.attribute9,
1231 l_tas_rec.attribute10,
1232 l_tas_rec.attribute11,
1233 l_tas_rec.attribute12,
1234 l_tas_rec.attribute13,
1235 l_tas_rec.attribute14,
1236 l_tas_rec.attribute15,
1237 l_tas_rec.created_by,
1238 l_tas_rec.creation_date,
1239 l_tas_rec.last_updated_by,
1240 l_tas_rec.last_update_date,
1241 l_tas_rec.last_update_login,
1242 l_tas_rec.tsu_code,
1243 l_tas_rec.try_id,
1244 l_tas_rec.date_trans_occurred,
1245 l_tas_rec.trans_number,
1246 l_tas_rec.comments,
1247 l_tas_rec.req_asset_id,
1248 l_tas_rec.total_match_amount,
1249 --Bug# 2697681 : 11.5.9 Schema changes
1250 l_tas_rec.org_id,
1251 --Added by dpsingh for LE uptake
1252 l_tas_rec.legal_entity_id
1253 ,NVL(l_tas_rec.transaction_date,SYSDATE));
1254 -- Set OUT values
1255 x_tas_rec := l_tas_rec;
1256 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1257 EXCEPTION
1258 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1259 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1260 (
1261 l_api_name,
1262 G_PKG_NAME,
1263 'OKC_API.G_RET_STS_ERROR',
1264 x_msg_count,
1265 x_msg_data,
1266 '_PVT'
1267 );
1268 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1269 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1270 (
1271 l_api_name,
1272 G_PKG_NAME,
1273 'OKC_API.G_RET_STS_UNEXP_ERROR',
1274 x_msg_count,
1275 x_msg_data,
1276 '_PVT'
1277 );
1278 WHEN OTHERS THEN
1279 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1280 (
1281 l_api_name,
1282 G_PKG_NAME,
1283 'OTHERS',
1284 x_msg_count,
1285 x_msg_data,
1286 '_PVT'
1287 );
1288 END insert_row;
1289 -------------------------------------
1290 -- insert_row for:OKL_TRX_ASSETS_V --
1291 -------------------------------------
1292 PROCEDURE insert_row(
1293 p_api_version IN NUMBER,
1294 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1295 x_return_status OUT NOCOPY VARCHAR2,
1296 x_msg_count OUT NOCOPY NUMBER,
1297 x_msg_data OUT NOCOPY VARCHAR2,
1298 p_tasv_rec IN tasv_rec_type,
1299 x_tasv_rec OUT NOCOPY tasv_rec_type) IS
1300
1301
1302 l_api_version CONSTANT NUMBER := 1;
1303 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1304 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1305 l_tasv_rec tasv_rec_type;
1306 l_def_tasv_rec tasv_rec_type;
1307 l_tas_rec tas_rec_type;
1308 lx_tas_rec tas_rec_type;
1309 -------------------------------
1310 -- FUNCTION fill_who_columns --
1311 -------------------------------
1312 FUNCTION fill_who_columns (
1313 p_tasv_rec IN tasv_rec_type
1314 ) RETURN tasv_rec_type IS
1315 l_tasv_rec tasv_rec_type := p_tasv_rec;
1316 BEGIN
1317 l_tasv_rec.CREATION_DATE := SYSDATE;
1318 l_tasv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1319 l_tasv_rec.LAST_UPDATE_DATE := SYSDATE;
1320 l_tasv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1321 l_tasv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1322 RETURN(l_tasv_rec);
1323 END fill_who_columns;
1324 -----------------------------------------
1325 -- Set_Attributes for:OKL_TRX_ASSETS_V --
1326 -----------------------------------------
1327 FUNCTION Set_Attributes (
1328 p_tasv_rec IN tasv_rec_type,
1329 x_tasv_rec OUT NOCOPY tasv_rec_type
1330 ) RETURN VARCHAR2 IS
1331 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1332 CURSOR c_get_trans_number
1333 IS
1334 SELECT OKL_TRN_SEQ.NEXTVAL
1335 FROM dual;
1336 BEGIN
1337 x_tasv_rec := p_tasv_rec;
1338 x_tasv_rec.OBJECT_VERSION_NUMBER := 1;
1339 -- Bug no 2627151 change start
1340 IF (p_tasv_rec.trans_number IS NULL OR
1341 p_tasv_rec.trans_number = OKL_API.G_MISS_NUM) THEN
1342 OPEN c_get_trans_number;
1343 FETCH c_get_trans_number INTO x_tasv_rec.trans_number;
1344 CLOSE c_get_trans_number;
1345 END IF;
1346 -- Bug no 2627151 change end
1347 RETURN(l_return_status);
1348 END Set_Attributes;
1349 BEGIN
1350 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1351 G_PKG_NAME,
1352 p_init_msg_list,
1353 l_api_version,
1354 p_api_version,
1355 '_PVT',
1356 x_return_status);
1357 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1358 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1360 RAISE OKC_API.G_EXCEPTION_ERROR;
1361 END IF;
1362 l_tasv_rec := null_out_defaults(p_tasv_rec);
1363 -- Set primary key value
1364 l_tasv_rec.ID := get_seq_id;
1365 --- Setting item attributes
1366 l_return_status := Set_Attributes(
1367 l_tasv_rec, -- IN
1368 l_def_tasv_rec); -- OUT
1369 --- If any errors happen abort API
1370 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1371 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1372 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1373 RAISE OKC_API.G_EXCEPTION_ERROR;
1374 END IF;
1375 l_def_tasv_rec := fill_who_columns(l_def_tasv_rec);
1376 --- Validate all non-missing attributes (Item Level Validation)
1377 l_return_status := Validate_Attributes(l_def_tasv_rec);
1378 --- If any errors happen abort API
1379 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1380 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1381 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1382 RAISE OKC_API.G_EXCEPTION_ERROR;
1383 END IF;
1384 l_return_status := Validate_Record(l_def_tasv_rec);
1385 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1386 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1387 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1388 RAISE OKC_API.G_EXCEPTION_ERROR;
1389 END IF;
1390 --------------------------------------
1391 -- Move VIEW record to "Child" records
1392 --------------------------------------
1393 migrate(l_def_tasv_rec, l_tas_rec);
1394 --------------------------------------------
1395 -- Call the INSERT_ROW for each child record
1396 --------------------------------------------
1397 insert_row(
1398 p_init_msg_list,
1399 x_return_status,
1400 x_msg_count,
1401 x_msg_data,
1402 l_tas_rec,
1403 lx_tas_rec
1404 );
1405 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1406 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1407 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1408 RAISE OKC_API.G_EXCEPTION_ERROR;
1409 END IF;
1410 migrate(lx_tas_rec, l_def_tasv_rec);
1411 -- Set OUT values
1412 x_tasv_rec := l_def_tasv_rec;
1413 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1414 EXCEPTION
1415 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1416 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1417 (
1418 l_api_name,
1419 G_PKG_NAME,
1420 'OKC_API.G_RET_STS_ERROR',
1421 x_msg_count,
1422 x_msg_data,
1423 '_PVT'
1424 );
1425 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1426 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1427 (
1428 l_api_name,
1429 G_PKG_NAME,
1430 'OKC_API.G_RET_STS_UNEXP_ERROR',
1431 x_msg_count,
1432 x_msg_data,
1433 '_PVT'
1434 );
1435 WHEN OTHERS THEN
1436 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1437 (
1438 l_api_name,
1439 G_PKG_NAME,
1440 'OTHERS',
1441 x_msg_count,
1442 x_msg_data,
1443 '_PVT'
1444 );
1445 END insert_row;
1446 ----------------------------------------
1447 -- PL/SQL TBL insert_row for:TASV_TBL --
1448 ----------------------------------------
1449 PROCEDURE insert_row(
1450 p_api_version IN NUMBER,
1451 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1452 x_return_status OUT NOCOPY VARCHAR2,
1453 x_msg_count OUT NOCOPY NUMBER,
1454 x_msg_data OUT NOCOPY VARCHAR2,
1455 p_tasv_tbl IN tasv_tbl_type,
1456 x_tasv_tbl OUT NOCOPY tasv_tbl_type) IS
1457
1458 l_api_version CONSTANT NUMBER := 1;
1459 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1460 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1461 i NUMBER := 0;
1462 BEGIN
1463 OKC_API.init_msg_list(p_init_msg_list);
1464 -- Make sure PL/SQL table has records in it before passing
1465 IF (p_tasv_tbl.COUNT > 0) THEN
1466 i := p_tasv_tbl.FIRST;
1467 LOOP
1468 insert_row (
1469 p_api_version => p_api_version,
1470 p_init_msg_list => OKC_API.G_FALSE,
1471 x_return_status => x_return_status,
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data,
1474 p_tasv_rec => p_tasv_tbl(i),
1475 x_tasv_rec => x_tasv_tbl(i));
1476 EXIT WHEN (i = p_tasv_tbl.LAST);
1477 i := p_tasv_tbl.NEXT(i);
1478 END LOOP;
1479 END IF;
1480 EXCEPTION
1481 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1482 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1483 (
1484 l_api_name,
1485 G_PKG_NAME,
1486 'OKC_API.G_RET_STS_ERROR',
1487 x_msg_count,
1488 x_msg_data,
1489 '_PVT'
1490 );
1491 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1492 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1493 (
1494 l_api_name,
1495 G_PKG_NAME,
1496 'OKC_API.G_RET_STS_UNEXP_ERROR',
1497 x_msg_count,
1498 x_msg_data,
1499 '_PVT'
1500 );
1501 WHEN OTHERS THEN
1502 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1503 (
1504 l_api_name,
1505 G_PKG_NAME,
1506 'OTHERS',
1507 x_msg_count,
1508 x_msg_data,
1509 '_PVT'
1510 );
1511 END insert_row;
1512
1513 ---------------------------------------------------------------------------
1514 -- PROCEDURE lock_row
1515 ---------------------------------------------------------------------------
1516 ---------------------------------
1517 -- lock_row for:OKL_TRX_ASSETS --
1518 ---------------------------------
1519 PROCEDURE lock_row(
1520 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1521 x_return_status OUT NOCOPY VARCHAR2,
1522 x_msg_count OUT NOCOPY NUMBER,
1523 x_msg_data OUT NOCOPY VARCHAR2,
1524 p_tas_rec IN tas_rec_type) IS
1525
1526 E_Resource_Busy EXCEPTION;
1527 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1528 CURSOR lock_csr (p_tas_rec IN tas_rec_type) IS
1529 SELECT OBJECT_VERSION_NUMBER
1530 FROM OKL_TRX_ASSETS
1531 WHERE ID = p_tas_rec.id
1532 AND OBJECT_VERSION_NUMBER = p_tas_rec.object_version_number
1533 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1534
1535 CURSOR lchk_csr (p_tas_rec IN tas_rec_type) IS
1536 SELECT OBJECT_VERSION_NUMBER
1537 FROM OKL_TRX_ASSETS
1538 WHERE ID = p_tas_rec.id;
1539 l_api_version CONSTANT NUMBER := 1;
1540 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_lock_row';
1541 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1542 l_object_version_number OKL_TRX_ASSETS.OBJECT_VERSION_NUMBER%TYPE;
1543 lc_object_version_number OKL_TRX_ASSETS.OBJECT_VERSION_NUMBER%TYPE;
1544 l_row_notfound BOOLEAN := FALSE;
1545 lc_row_notfound BOOLEAN := FALSE;
1546 BEGIN
1547 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1548 p_init_msg_list,
1549 '_PVT',
1550 x_return_status);
1551 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1552 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1553 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_ERROR;
1555 END IF;
1556 BEGIN
1557 OPEN lock_csr(p_tas_rec);
1558 FETCH lock_csr INTO l_object_version_number;
1559 l_row_notfound := lock_csr%NOTFOUND;
1560 CLOSE lock_csr;
1561 EXCEPTION
1562 WHEN E_Resource_Busy THEN
1563 IF (lock_csr%ISOPEN) THEN
1564 CLOSE lock_csr;
1565 END IF;
1566 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1567 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1568 END;
1569
1570 IF ( l_row_notfound ) THEN
1571 OPEN lchk_csr(p_tas_rec);
1572 FETCH lchk_csr INTO lc_object_version_number;
1573 lc_row_notfound := lchk_csr%NOTFOUND;
1574 CLOSE lchk_csr;
1575 END IF;
1576 IF (lc_row_notfound) THEN
1577 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1578 RAISE OKC_API.G_EXCEPTION_ERROR;
1579 ELSIF lc_object_version_number > p_tas_rec.object_version_number THEN
1580 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1581 RAISE OKC_API.G_EXCEPTION_ERROR;
1582 ELSIF lc_object_version_number <> p_tas_rec.object_version_number THEN
1583 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1584 RAISE OKC_API.G_EXCEPTION_ERROR;
1585 ELSIF lc_object_version_number = -1 THEN
1586 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1587 RAISE OKC_API.G_EXCEPTION_ERROR;
1588 END IF;
1589 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1590 EXCEPTION
1591 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1592 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1593 (
1594 l_api_name,
1595 G_PKG_NAME,
1596 'OKC_API.G_RET_STS_ERROR',
1597 x_msg_count,
1598 x_msg_data,
1599 '_PVT'
1600 );
1601 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1602 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OKC_API.G_RET_STS_UNEXP_ERROR',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT'
1610 );
1611 WHEN OTHERS THEN
1612 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1613 (
1614 l_api_name,
1615 G_PKG_NAME,
1616 'OTHERS',
1617 x_msg_count,
1618 x_msg_data,
1619 '_PVT'
1620 );
1621 END lock_row;
1622 -----------------------------------
1623 -- lock_row for:OKL_TRX_ASSETS_V --
1624 -----------------------------------
1625 PROCEDURE lock_row(
1626 p_api_version IN NUMBER,
1627 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1628 x_return_status OUT NOCOPY VARCHAR2,
1629 x_msg_count OUT NOCOPY NUMBER,
1630 x_msg_data OUT NOCOPY VARCHAR2,
1631 p_tasv_rec IN tasv_rec_type) IS
1632
1633 l_api_version CONSTANT NUMBER := 1;
1634 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1635 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1636 l_tas_rec tas_rec_type;
1637 BEGIN
1638 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1639 G_PKG_NAME,
1640 p_init_msg_list,
1641 l_api_version,
1642 p_api_version,
1643 '_PVT',
1644 x_return_status);
1645 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1646 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1647 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1648 RAISE OKC_API.G_EXCEPTION_ERROR;
1649 END IF;
1650 --------------------------------------
1651 -- Move VIEW record to "Child" records
1652 --------------------------------------
1653 migrate(p_tasv_rec, l_tas_rec);
1654 --------------------------------------------
1655 -- Call the LOCK_ROW for each child record
1656 --------------------------------------------
1657 lock_row(
1658 p_init_msg_list,
1659 x_return_status,
1660 x_msg_count,
1661 x_msg_data,
1662 l_tas_rec
1663 );
1664 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1665 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1666 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1667 RAISE OKC_API.G_EXCEPTION_ERROR;
1668 END IF;
1669 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1670 EXCEPTION
1671 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1672 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1673 (
1674 l_api_name,
1675 G_PKG_NAME,
1676 'OKC_API.G_RET_STS_ERROR',
1677 x_msg_count,
1678 x_msg_data,
1679 '_PVT'
1680 );
1681 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1682 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1683 (
1684 l_api_name,
1685 G_PKG_NAME,
1686 'OKC_API.G_RET_STS_UNEXP_ERROR',
1687 x_msg_count,
1688 x_msg_data,
1689 '_PVT'
1690 );
1691 WHEN OTHERS THEN
1692 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1693 (
1694 l_api_name,
1695 G_PKG_NAME,
1696 'OTHERS',
1697 x_msg_count,
1698 x_msg_data,
1699 '_PVT'
1700 );
1701 END lock_row;
1702 --------------------------------------
1703 -- PL/SQL TBL lock_row for:TASV_TBL --
1704 --------------------------------------
1705 PROCEDURE lock_row(
1706 p_api_version IN NUMBER,
1707 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1708 x_return_status OUT NOCOPY VARCHAR2,
1709 x_msg_count OUT NOCOPY NUMBER,
1710 x_msg_data OUT NOCOPY VARCHAR2,
1711 p_tasv_tbl IN tasv_tbl_type) IS
1712
1713 l_api_version CONSTANT NUMBER := 1;
1714 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1715 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1716 i NUMBER := 0;
1717 BEGIN
1718 OKC_API.init_msg_list(p_init_msg_list);
1719 -- Make sure PL/SQL table has records in it before passing
1720 IF (p_tasv_tbl.COUNT > 0) THEN
1721 i := p_tasv_tbl.FIRST;
1722 LOOP
1723 lock_row (
1724 p_api_version => p_api_version,
1725 p_init_msg_list => OKC_API.G_FALSE,
1726 x_return_status => x_return_status,
1727 x_msg_count => x_msg_count,
1728 x_msg_data => x_msg_data,
1729 p_tasv_rec => p_tasv_tbl(i));
1730 EXIT WHEN (i = p_tasv_tbl.LAST);
1731 i := p_tasv_tbl.NEXT(i);
1732 END LOOP;
1733 END IF;
1734 EXCEPTION
1735 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1736 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1737 (
1738 l_api_name,
1739 G_PKG_NAME,
1740 'OKC_API.G_RET_STS_ERROR',
1741 x_msg_count,
1742 x_msg_data,
1743 '_PVT'
1744 );
1745 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1746 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1747 (
1748 l_api_name,
1749 G_PKG_NAME,
1750 'OKC_API.G_RET_STS_UNEXP_ERROR',
1751 x_msg_count,
1752 x_msg_data,
1753 '_PVT'
1754 );
1755 WHEN OTHERS THEN
1756 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1757 (
1758 l_api_name,
1759 G_PKG_NAME,
1760 'OTHERS',
1761 x_msg_count,
1762 x_msg_data,
1763 '_PVT'
1764 );
1765 END lock_row;
1766
1767 ---------------------------------------------------------------------------
1768 -- PROCEDURE update_row
1769 ---------------------------------------------------------------------------
1770 -----------------------------------
1771 -- update_row for:OKL_TRX_ASSETS --
1772 -----------------------------------
1773 PROCEDURE update_row(
1774 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1775 x_return_status OUT NOCOPY VARCHAR2,
1776 x_msg_count OUT NOCOPY NUMBER,
1777 x_msg_data OUT NOCOPY VARCHAR2,
1778 p_tas_rec IN tas_rec_type,
1779 x_tas_rec OUT NOCOPY tas_rec_type) IS
1780
1781 l_api_version CONSTANT NUMBER := 1;
1782 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_update_row';
1783 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1784 l_tas_rec tas_rec_type := p_tas_rec;
1785 l_def_tas_rec tas_rec_type;
1786 l_row_notfound BOOLEAN := TRUE;
1787 ----------------------------------
1788 -- FUNCTION populate_new_record --
1789 ----------------------------------
1790 FUNCTION populate_new_record (
1791 p_tas_rec IN tas_rec_type,
1792 x_tas_rec OUT NOCOPY tas_rec_type
1793 ) RETURN VARCHAR2 IS
1794 l_tas_rec tas_rec_type;
1795 l_row_notfound BOOLEAN := TRUE;
1796 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1797 BEGIN
1798 x_tas_rec := p_tas_rec;
1799 -- Get current database values
1800 l_tas_rec := get_rec(p_tas_rec, l_row_notfound);
1801 IF (l_row_notfound) THEN
1802 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1803 END IF;
1804 IF (x_tas_rec.id = OKC_API.G_MISS_NUM)
1805 THEN
1806 x_tas_rec.id := l_tas_rec.id;
1807 END IF;
1808 IF (x_tas_rec.ica_id = OKC_API.G_MISS_NUM)
1809 THEN
1810 x_tas_rec.ica_id := l_tas_rec.ica_id;
1811 END IF;
1812 IF (x_tas_rec.tas_type = OKC_API.G_MISS_CHAR)
1813 THEN
1814 x_tas_rec.tas_type := l_tas_rec.tas_type;
1815 END IF;
1816 IF (x_tas_rec.object_version_number = OKC_API.G_MISS_NUM)
1817 THEN
1818 x_tas_rec.object_version_number := l_tas_rec.object_version_number;
1819 END IF;
1820 IF (x_tas_rec.attribute_category = OKC_API.G_MISS_CHAR)
1821 THEN
1822 x_tas_rec.attribute_category := l_tas_rec.attribute_category;
1823 END IF;
1824 IF (x_tas_rec.attribute1 = OKC_API.G_MISS_CHAR)
1825 THEN
1826 x_tas_rec.attribute1 := l_tas_rec.attribute1;
1827 END IF;
1828 IF (x_tas_rec.attribute2 = OKC_API.G_MISS_CHAR)
1829 THEN
1830 x_tas_rec.attribute2 := l_tas_rec.attribute2;
1831 END IF;
1832 IF (x_tas_rec.attribute3 = OKC_API.G_MISS_CHAR)
1833 THEN
1834 x_tas_rec.attribute3 := l_tas_rec.attribute3;
1835 END IF;
1836 IF (x_tas_rec.attribute4 = OKC_API.G_MISS_CHAR)
1837 THEN
1838 x_tas_rec.attribute4 := l_tas_rec.attribute4;
1839 END IF;
1840 IF (x_tas_rec.attribute5 = OKC_API.G_MISS_CHAR)
1841 THEN
1842 x_tas_rec.attribute5 := l_tas_rec.attribute5;
1843 END IF;
1844 IF (x_tas_rec.attribute6 = OKC_API.G_MISS_CHAR)
1845 THEN
1846 x_tas_rec.attribute6 := l_tas_rec.attribute6;
1847 END IF;
1848 IF (x_tas_rec.attribute7 = OKC_API.G_MISS_CHAR)
1849 THEN
1850 x_tas_rec.attribute7 := l_tas_rec.attribute7;
1851 END IF;
1852 IF (x_tas_rec.attribute8 = OKC_API.G_MISS_CHAR)
1853 THEN
1854 x_tas_rec.attribute8 := l_tas_rec.attribute8;
1855 END IF;
1856 IF (x_tas_rec.attribute9 = OKC_API.G_MISS_CHAR)
1857 THEN
1858 x_tas_rec.attribute9 := l_tas_rec.attribute9;
1859 END IF;
1860 IF (x_tas_rec.attribute10 = OKC_API.G_MISS_CHAR)
1861 THEN
1862 x_tas_rec.attribute10 := l_tas_rec.attribute10;
1863 END IF;
1864 IF (x_tas_rec.attribute11 = OKC_API.G_MISS_CHAR)
1865 THEN
1866 x_tas_rec.attribute11 := l_tas_rec.attribute11;
1867 END IF;
1868 IF (x_tas_rec.attribute12 = OKC_API.G_MISS_CHAR)
1869 THEN
1870 x_tas_rec.attribute12 := l_tas_rec.attribute12;
1871 END IF;
1872 IF (x_tas_rec.attribute13 = OKC_API.G_MISS_CHAR)
1873 THEN
1874 x_tas_rec.attribute13 := l_tas_rec.attribute13;
1875 END IF;
1876 IF (x_tas_rec.attribute14 = OKC_API.G_MISS_CHAR)
1877 THEN
1878 x_tas_rec.attribute14 := l_tas_rec.attribute14;
1879 END IF;
1880 IF (x_tas_rec.attribute15 = OKC_API.G_MISS_CHAR)
1881 THEN
1882 x_tas_rec.attribute15 := l_tas_rec.attribute15;
1883 END IF;
1884 IF (x_tas_rec.created_by = OKC_API.G_MISS_NUM)
1885 THEN
1886 x_tas_rec.created_by := l_tas_rec.created_by;
1887 END IF;
1888 IF (x_tas_rec.creation_date = OKC_API.G_MISS_DATE)
1889 THEN
1890 x_tas_rec.creation_date := l_tas_rec.creation_date;
1891 END IF;
1892 IF (x_tas_rec.last_updated_by = OKC_API.G_MISS_NUM)
1893 THEN
1894 x_tas_rec.last_updated_by := l_tas_rec.last_updated_by;
1895 END IF;
1896 IF (x_tas_rec.last_update_date = OKC_API.G_MISS_DATE)
1897 THEN
1898 x_tas_rec.last_update_date := l_tas_rec.last_update_date;
1899 END IF;
1900 IF (x_tas_rec.last_update_login = OKC_API.G_MISS_NUM)
1901 THEN
1902 x_tas_rec.last_update_login := l_tas_rec.last_update_login;
1903 END IF;
1904 IF (x_tas_rec.tsu_code = OKC_API.G_MISS_CHAR)
1905 THEN
1906 x_tas_rec.tsu_code := l_tas_rec.tsu_code;
1907 END IF;
1908 IF (x_tas_rec.try_id = OKC_API.G_MISS_NUM)
1909 THEN
1910 x_tas_rec.try_id := l_tas_rec.try_id;
1911 END IF;
1912 IF (x_tas_rec.date_trans_occurred = OKC_API.G_MISS_DATE)
1913 THEN
1914 x_tas_rec.date_trans_occurred := l_tas_rec.date_trans_occurred;
1915 END IF;
1916 IF (x_tas_rec.trans_number = OKC_API.G_MISS_NUM)
1917 THEN
1918 x_tas_rec.trans_number := l_tas_rec.trans_number;
1919 END IF;
1920 IF (x_tas_rec.comments = OKC_API.G_MISS_CHAR)
1921 THEN
1922 x_tas_rec.comments := l_tas_rec.comments;
1923 END IF;
1924 IF (x_tas_rec.req_asset_id = OKC_API.G_MISS_NUM)
1925 THEN
1926 x_tas_rec.req_asset_id := l_tas_rec.req_asset_id;
1927 END IF;
1928 IF (x_tas_rec.total_match_amount = OKC_API.G_MISS_NUM)
1929 THEN
1930 x_tas_rec.total_match_amount := l_tas_rec.total_match_amount;
1931 END IF;
1932 --Bug# 2697681 : 11.5.9 Schema changes
1933 IF (x_tas_rec.org_id = OKC_API.G_MISS_NUM)
1934 THEN
1935 x_tas_rec.org_id := l_tas_rec.org_id;
1936 END IF;
1937 -- Added by dpsingh for LE uptake
1938 IF (x_tas_rec.legal_entity_id = OKL_API.G_MISS_NUM)
1939 THEN
1940 x_tas_rec.legal_entity_id := l_tas_rec.legal_entity_id;
1941 END IF;
1942 IF (x_tas_rec.transaction_date = OKL_API.G_MISS_DATE)
1943 THEN
1944 x_tas_rec.transaction_date := SYSDATE;
1945 END IF;
1946 RETURN(l_return_status);
1947 END populate_new_record;
1948 ---------------------------------------
1949 -- Set_Attributes for:OKL_TRX_ASSETS --
1950 ---------------------------------------
1951 FUNCTION Set_Attributes (
1952 p_tas_rec IN tas_rec_type,
1953 x_tas_rec OUT NOCOPY tas_rec_type
1954 ) RETURN VARCHAR2 IS
1955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1956 BEGIN
1957 x_tas_rec := p_tas_rec;
1958 RETURN(l_return_status);
1959 END Set_Attributes;
1960 BEGIN
1961 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1962 p_init_msg_list,
1963 '_PVT',
1964 x_return_status);
1965 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1966 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1967 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1968 RAISE OKC_API.G_EXCEPTION_ERROR;
1969 END IF;
1970 --- Setting item attributes
1971 l_return_status := Set_Attributes(
1972 p_tas_rec, -- IN
1973 l_tas_rec); -- OUT
1974 --- If any errors happen abort API
1975 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1976 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1977 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1978 RAISE OKC_API.G_EXCEPTION_ERROR;
1979 END IF;
1980 l_return_status := populate_new_record(l_tas_rec, l_def_tas_rec);
1981 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1982 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1983 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1984 RAISE OKC_API.G_EXCEPTION_ERROR;
1985 END IF;
1986 UPDATE OKL_TRX_ASSETS
1987 SET ICA_ID = l_def_tas_rec.ica_id,
1988 TAS_TYPE = l_def_tas_rec.tas_type,
1989 OBJECT_VERSION_NUMBER = l_def_tas_rec.object_version_number,
1990 ATTRIBUTE_CATEGORY = l_def_tas_rec.attribute_category,
1991 ATTRIBUTE1 = l_def_tas_rec.attribute1,
1992 ATTRIBUTE2 = l_def_tas_rec.attribute2,
1993 ATTRIBUTE3 = l_def_tas_rec.attribute3,
1994 ATTRIBUTE4 = l_def_tas_rec.attribute4,
1995 ATTRIBUTE5 = l_def_tas_rec.attribute5,
1996 ATTRIBUTE6 = l_def_tas_rec.attribute6,
1997 ATTRIBUTE7 = l_def_tas_rec.attribute7,
1998 ATTRIBUTE8 = l_def_tas_rec.attribute8,
1999 ATTRIBUTE9 = l_def_tas_rec.attribute9,
2000 ATTRIBUTE10 = l_def_tas_rec.attribute10,
2001 ATTRIBUTE11 = l_def_tas_rec.attribute11,
2002 ATTRIBUTE12 = l_def_tas_rec.attribute12,
2003 ATTRIBUTE13 = l_def_tas_rec.attribute13,
2004 ATTRIBUTE14 = l_def_tas_rec.attribute14,
2005 ATTRIBUTE15 = l_def_tas_rec.attribute15,
2006 CREATED_BY = l_def_tas_rec.created_by,
2007 CREATION_DATE = l_def_tas_rec.creation_date,
2008 LAST_UPDATED_BY = l_def_tas_rec.last_updated_by,
2009 LAST_UPDATE_DATE = l_def_tas_rec.last_update_date,
2010 LAST_UPDATE_LOGIN = l_def_tas_rec.last_update_login,
2011 TSU_CODE = l_def_tas_rec.tsu_code,
2012 TRY_ID = l_def_tas_rec.try_id,
2013 DATE_TRANS_OCCURRED = l_def_tas_rec.date_trans_occurred,
2014 TRANS_NUMBER = l_def_tas_rec.trans_number,
2015 COMMENTS = l_def_tas_rec.comments,
2016 REQ_ASSET_ID = l_def_tas_rec.req_asset_id,
2017 TOTAL_MATCH_AMOUNT = l_def_tas_rec.total_match_amount,
2018 --Bug# 2697681 : 11.5.9 Schema changes
2019 ORG_ID = l_def_tas_rec.org_id ,
2020 --Added by dpsingh for LE uptake
2021 LEGAL_ENTITY_ID = l_def_tas_rec.legal_entity_id
2022 ,TRANSACTION_DATE = l_def_tas_rec.transaction_date
2023 WHERE ID = l_def_tas_rec.id;
2024
2025 x_tas_rec := l_def_tas_rec;
2026 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2027 EXCEPTION
2028 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2029 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2030 (
2031 l_api_name,
2032 G_PKG_NAME,
2033 'OKC_API.G_RET_STS_ERROR',
2034 x_msg_count,
2035 x_msg_data,
2036 '_PVT'
2037 );
2038 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2039 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2040 (
2041 l_api_name,
2042 G_PKG_NAME,
2043 'OKC_API.G_RET_STS_UNEXP_ERROR',
2044 x_msg_count,
2045 x_msg_data,
2046 '_PVT'
2047 );
2048 WHEN OTHERS THEN
2049 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2050 (
2051 l_api_name,
2052 G_PKG_NAME,
2053 'OTHERS',
2054 x_msg_count,
2055 x_msg_data,
2056 '_PVT'
2057 );
2058 END update_row;
2059 -------------------------------------
2060 -- update_row for:OKL_TRX_ASSETS_V --
2061 -------------------------------------
2062 PROCEDURE update_row(
2063 p_api_version IN NUMBER,
2064 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2065 x_return_status OUT NOCOPY VARCHAR2,
2066 x_msg_count OUT NOCOPY NUMBER,
2067 x_msg_data OUT NOCOPY VARCHAR2,
2068 p_tasv_rec IN tasv_rec_type,
2069 x_tasv_rec OUT NOCOPY tasv_rec_type) IS
2070
2071 l_api_version CONSTANT NUMBER := 1;
2072 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2073 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2074 l_tasv_rec tasv_rec_type := p_tasv_rec;
2075 l_def_tasv_rec tasv_rec_type;
2076 l_tas_rec tas_rec_type;
2077 lx_tas_rec tas_rec_type;
2078 -------------------------------
2079 -- FUNCTION fill_who_columns --
2080 -------------------------------
2081 FUNCTION fill_who_columns (
2082 p_tasv_rec IN tasv_rec_type
2083 ) RETURN tasv_rec_type IS
2084 l_tasv_rec tasv_rec_type := p_tasv_rec;
2085 BEGIN
2086 l_tasv_rec.LAST_UPDATE_DATE := SYSDATE;
2087 l_tasv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2088 l_tasv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2089 RETURN(l_tasv_rec);
2090 END fill_who_columns;
2091 ----------------------------------
2092 -- FUNCTION populate_new_record --
2093 ----------------------------------
2094 FUNCTION populate_new_record (
2095 p_tasv_rec IN tasv_rec_type,
2096 x_tasv_rec OUT NOCOPY tasv_rec_type
2097 ) RETURN VARCHAR2 IS
2098 l_tasv_rec tasv_rec_type;
2099 l_row_notfound BOOLEAN := TRUE;
2100 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2101 BEGIN
2102 x_tasv_rec := p_tasv_rec;
2103 -- Get current database values
2104 l_tasv_rec := get_rec(p_tasv_rec, l_row_notfound);
2105 IF (l_row_notfound) THEN
2106 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2107 END IF;
2108 IF (x_tasv_rec.id = OKC_API.G_MISS_NUM)
2109 THEN
2110 x_tasv_rec.id := l_tasv_rec.id;
2111 END IF;
2112 IF (x_tasv_rec.object_version_number = OKC_API.G_MISS_NUM)
2113 THEN
2114 x_tasv_rec.object_version_number := l_tasv_rec.object_version_number;
2115 END IF;
2116 IF (x_tasv_rec.ica_id = OKC_API.G_MISS_NUM)
2117 THEN
2118 x_tasv_rec.ica_id := l_tasv_rec.ica_id;
2119 END IF;
2120 IF (x_tasv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2121 THEN
2122 x_tasv_rec.attribute_category := l_tasv_rec.attribute_category;
2123 END IF;
2124 IF (x_tasv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2125 THEN
2126 x_tasv_rec.attribute1 := l_tasv_rec.attribute1;
2127 END IF;
2128 IF (x_tasv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2129 THEN
2130 x_tasv_rec.attribute2 := l_tasv_rec.attribute2;
2131 END IF;
2132 IF (x_tasv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2133 THEN
2134 x_tasv_rec.attribute3 := l_tasv_rec.attribute3;
2135 END IF;
2136 IF (x_tasv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2137 THEN
2138 x_tasv_rec.attribute4 := l_tasv_rec.attribute4;
2139 END IF;
2140 IF (x_tasv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2141 THEN
2142 x_tasv_rec.attribute5 := l_tasv_rec.attribute5;
2143 END IF;
2144 IF (x_tasv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2145 THEN
2146 x_tasv_rec.attribute6 := l_tasv_rec.attribute6;
2147 END IF;
2148 IF (x_tasv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2149 THEN
2150 x_tasv_rec.attribute7 := l_tasv_rec.attribute7;
2151 END IF;
2152 IF (x_tasv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2153 THEN
2154 x_tasv_rec.attribute8 := l_tasv_rec.attribute8;
2155 END IF;
2156 IF (x_tasv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2157 THEN
2158 x_tasv_rec.attribute9 := l_tasv_rec.attribute9;
2159 END IF;
2160 IF (x_tasv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2161 THEN
2162 x_tasv_rec.attribute10 := l_tasv_rec.attribute10;
2163 END IF;
2164 IF (x_tasv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2165 THEN
2166 x_tasv_rec.attribute11 := l_tasv_rec.attribute11;
2167 END IF;
2168 IF (x_tasv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2169 THEN
2170 x_tasv_rec.attribute12 := l_tasv_rec.attribute12;
2171 END IF;
2172 IF (x_tasv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2173 THEN
2174 x_tasv_rec.attribute13 := l_tasv_rec.attribute13;
2175 END IF;
2176 IF (x_tasv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2177 THEN
2178 x_tasv_rec.attribute14 := l_tasv_rec.attribute14;
2179 END IF;
2180 IF (x_tasv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2181 THEN
2182 x_tasv_rec.attribute15 := l_tasv_rec.attribute15;
2183 END IF;
2184 IF (x_tasv_rec.tas_type = OKC_API.G_MISS_CHAR)
2185 THEN
2186 x_tasv_rec.tas_type := l_tasv_rec.tas_type;
2187 END IF;
2188 IF (x_tasv_rec.created_by = OKC_API.G_MISS_NUM)
2189 THEN
2190 x_tasv_rec.created_by := l_tasv_rec.created_by;
2191 END IF;
2192 IF (x_tasv_rec.creation_date = OKC_API.G_MISS_DATE)
2193 THEN
2194 x_tasv_rec.creation_date := l_tasv_rec.creation_date;
2195 END IF;
2196 IF (x_tasv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2197 THEN
2198 x_tasv_rec.last_updated_by := l_tasv_rec.last_updated_by;
2199 END IF;
2200 IF (x_tasv_rec.last_update_date = OKC_API.G_MISS_DATE)
2201 THEN
2202 x_tasv_rec.last_update_date := l_tasv_rec.last_update_date;
2203 END IF;
2204 IF (x_tasv_rec.last_update_login = OKC_API.G_MISS_NUM)
2205 THEN
2206 x_tasv_rec.last_update_login := l_tasv_rec.last_update_login;
2207 END IF;
2208 IF (x_tasv_rec.tsu_code = OKC_API.G_MISS_CHAR)
2209 THEN
2210 x_tasv_rec.tsu_code := l_tasv_rec.tsu_code;
2211 END IF;
2212 IF (x_tasv_rec.try_id = OKC_API.G_MISS_NUM)
2213 THEN
2214 x_tasv_rec.try_id := l_tasv_rec.try_id;
2215 END IF;
2216 IF (x_tasv_rec.date_trans_occurred = OKC_API.G_MISS_DATE)
2217 THEN
2218 x_tasv_rec.date_trans_occurred := l_tasv_rec.date_trans_occurred;
2219 END IF;
2220 IF (x_tasv_rec.trans_number = OKC_API.G_MISS_NUM)
2221 THEN
2222 x_tasv_rec.trans_number := l_tasv_rec.trans_number;
2223 END IF;
2224 IF (x_tasv_rec.comments = OKC_API.G_MISS_CHAR)
2225 THEN
2226 x_tasv_rec.comments := l_tasv_rec.comments;
2227 END IF;
2228 IF (x_tasv_rec.req_asset_id = OKC_API.G_MISS_NUM)
2229 THEN
2230 x_tasv_rec.req_asset_id := l_tasv_rec.req_asset_id;
2231 END IF;
2232 IF (x_tasv_rec.total_match_amount = OKC_API.G_MISS_NUM)
2233 THEN
2234 x_tasv_rec.total_match_amount := l_tasv_rec.total_match_amount;
2235 END IF;
2236 --Bug# 2697681 : 11.5.9 Schema changes
2237 IF (x_tasv_rec.org_id= OKC_API.G_MISS_NUM)
2238 THEN
2239 x_tasv_rec.org_id := l_tasv_rec.org_id;
2240 END IF;
2241 --Added by dpsingh for LE uptake
2242 IF (x_tasv_rec.legal_entity_id = OKL_API.G_MISS_NUM)
2243 THEN
2244 x_tasv_rec.legal_entity_id := l_tasv_rec.legal_entity_id;
2245 END IF;
2246 IF (x_tasv_rec.transaction_date = OKL_API.G_MISS_DATE)
2247 THEN
2248 x_tasv_rec.transaction_date := l_tasv_rec.transaction_date;
2249 END IF;
2250 RETURN(l_return_status);
2251 END populate_new_record;
2252 -----------------------------------------
2253 -- Set_Attributes for:OKL_TRX_ASSETS_V --
2254 -----------------------------------------
2255 FUNCTION Set_Attributes (
2256 p_tasv_rec IN tasv_rec_type,
2257 x_tasv_rec OUT NOCOPY tasv_rec_type
2258 ) RETURN VARCHAR2 IS
2259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2260 BEGIN
2261 x_tasv_rec := p_tasv_rec;
2262 x_tasv_rec.OBJECT_VERSION_NUMBER := NVL(x_tasv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2263 RETURN(l_return_status);
2264 END Set_Attributes;
2265 BEGIN
2266 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2267 G_PKG_NAME,
2268 p_init_msg_list,
2269 l_api_version,
2270 p_api_version,
2271 '_PVT',
2272 x_return_status);
2273 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2274 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2275 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2276 RAISE OKC_API.G_EXCEPTION_ERROR;
2277 END IF;
2278 --- Setting item attributes
2279 l_return_status := Set_Attributes(
2280 p_tasv_rec, -- IN
2281 l_tasv_rec); -- OUT
2282 --- If any errors happen abort API
2283 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2284 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2285 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2286 RAISE OKC_API.G_EXCEPTION_ERROR;
2287 END IF;
2288 l_return_status := populate_new_record(l_tasv_rec, l_def_tasv_rec);
2289 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2292 RAISE OKC_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 l_def_tasv_rec := fill_who_columns(l_def_tasv_rec);
2295 --- Validate all non-missing attributes (Item Level Validation)
2296 l_return_status := Validate_Attributes(l_def_tasv_rec);
2297 --- If any errors happen abort API
2298 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2299 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2300 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2301 RAISE OKC_API.G_EXCEPTION_ERROR;
2302 END IF;
2303 l_return_status := Validate_Record(l_def_tasv_rec);
2304 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2305 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2306 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2307 RAISE OKC_API.G_EXCEPTION_ERROR;
2308 END IF;
2309
2310 --------------------------------------
2311 -- Move VIEW record to "Child" records
2312 --------------------------------------
2313 migrate(l_def_tasv_rec, l_tas_rec);
2314 --------------------------------------------
2315 -- Call the UPDATE_ROW for each child record
2316 --------------------------------------------
2317 update_row(
2318 p_init_msg_list,
2319 x_return_status,
2320 x_msg_count,
2321 x_msg_data,
2322 l_tas_rec,
2323 lx_tas_rec
2324 );
2325 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2327 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2328 RAISE OKC_API.G_EXCEPTION_ERROR;
2329 END IF;
2330 migrate(lx_tas_rec, l_def_tasv_rec);
2331 x_tasv_rec := l_def_tasv_rec;
2332 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2333 EXCEPTION
2334 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2335 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2336 (
2337 l_api_name,
2338 G_PKG_NAME,
2339 'OKC_API.G_RET_STS_ERROR',
2340 x_msg_count,
2341 x_msg_data,
2342 '_PVT'
2343 );
2344 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2345 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2346 (
2347 l_api_name,
2348 G_PKG_NAME,
2349 'OKC_API.G_RET_STS_UNEXP_ERROR',
2350 x_msg_count,
2351 x_msg_data,
2352 '_PVT'
2353 );
2354 WHEN OTHERS THEN
2355 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2356 (
2357 l_api_name,
2358 G_PKG_NAME,
2359 'OTHERS',
2360 x_msg_count,
2361 x_msg_data,
2362 '_PVT'
2363 );
2364 END update_row;
2365 ----------------------------------------
2366 -- PL/SQL TBL update_row for:TASV_TBL --
2367 ----------------------------------------
2368 PROCEDURE update_row(
2369 p_api_version IN NUMBER,
2370 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2371 x_return_status OUT NOCOPY VARCHAR2,
2372 x_msg_count OUT NOCOPY NUMBER,
2373 x_msg_data OUT NOCOPY VARCHAR2,
2374 p_tasv_tbl IN tasv_tbl_type,
2375 x_tasv_tbl OUT NOCOPY tasv_tbl_type) IS
2376
2377 l_api_version CONSTANT NUMBER := 1;
2378 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2379 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2380 i NUMBER := 0;
2381 BEGIN
2382 OKC_API.init_msg_list(p_init_msg_list);
2383 -- Make sure PL/SQL table has records in it before passing
2384 IF (p_tasv_tbl.COUNT > 0) THEN
2385 i := p_tasv_tbl.FIRST;
2386 LOOP
2387 update_row (
2388 p_api_version => p_api_version,
2389 p_init_msg_list => OKC_API.G_FALSE,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_tasv_rec => p_tasv_tbl(i),
2394 x_tasv_rec => x_tasv_tbl(i));
2395 EXIT WHEN (i = p_tasv_tbl.LAST);
2396 i := p_tasv_tbl.NEXT(i);
2397 END LOOP;
2398 END IF;
2399 EXCEPTION
2400 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2401 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2402 (
2403 l_api_name,
2404 G_PKG_NAME,
2405 'OKC_API.G_RET_STS_ERROR',
2406 x_msg_count,
2407 x_msg_data,
2408 '_PVT'
2409 );
2410 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2411 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2412 (
2413 l_api_name,
2414 G_PKG_NAME,
2415 'OKC_API.G_RET_STS_UNEXP_ERROR',
2416 x_msg_count,
2417 x_msg_data,
2418 '_PVT'
2419 );
2420 WHEN OTHERS THEN
2421 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2422 (
2423 l_api_name,
2424 G_PKG_NAME,
2425 'OTHERS',
2426 x_msg_count,
2427 x_msg_data,
2428 '_PVT'
2429 );
2430 END update_row;
2431
2432 ---------------------------------------------------------------------------
2433 -- PROCEDURE delete_row
2434 ---------------------------------------------------------------------------
2435 -----------------------------------
2436 -- delete_row for:OKL_TRX_ASSETS --
2437 -----------------------------------
2438 PROCEDURE delete_row(
2439 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2440 x_return_status OUT NOCOPY VARCHAR2,
2441 x_msg_count OUT NOCOPY NUMBER,
2442 x_msg_data OUT NOCOPY VARCHAR2,
2443 p_tas_rec IN tas_rec_type) IS
2444
2445 l_api_version CONSTANT NUMBER := 1;
2446 l_api_name CONSTANT VARCHAR2(30) := 'ASSETS_delete_row';
2447 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2448 l_tas_rec tas_rec_type:= p_tas_rec;
2449 l_row_notfound BOOLEAN := TRUE;
2450 BEGIN
2451 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2452 p_init_msg_list,
2453 '_PVT',
2454 x_return_status);
2455 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2456 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2457 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2458 RAISE OKC_API.G_EXCEPTION_ERROR;
2459 END IF;
2460 DELETE FROM OKL_TRX_ASSETS
2461 WHERE ID = l_tas_rec.id;
2462
2463 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2464 EXCEPTION
2465 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2466 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2467 (
2468 l_api_name,
2469 G_PKG_NAME,
2470 'OKC_API.G_RET_STS_ERROR',
2471 x_msg_count,
2472 x_msg_data,
2473 '_PVT'
2474 );
2475 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2476 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2477 (
2478 l_api_name,
2479 G_PKG_NAME,
2480 'OKC_API.G_RET_STS_UNEXP_ERROR',
2481 x_msg_count,
2482 x_msg_data,
2483 '_PVT'
2484 );
2485 WHEN OTHERS THEN
2486 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2487 (
2488 l_api_name,
2489 G_PKG_NAME,
2490 'OTHERS',
2491 x_msg_count,
2492 x_msg_data,
2493 '_PVT'
2494 );
2495 END delete_row;
2496 -------------------------------------
2497 -- delete_row for:OKL_TRX_ASSETS_V --
2498 -------------------------------------
2499 PROCEDURE delete_row(
2500 p_api_version IN NUMBER,
2501 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2502 x_return_status OUT NOCOPY VARCHAR2,
2503 x_msg_count OUT NOCOPY NUMBER,
2504 x_msg_data OUT NOCOPY VARCHAR2,
2505 p_tasv_rec IN tasv_rec_type) IS
2506
2507 l_api_version CONSTANT NUMBER := 1;
2508 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2509 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2510 l_tasv_rec tasv_rec_type := p_tasv_rec;
2511 l_tas_rec tas_rec_type;
2512 BEGIN
2513 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2514 G_PKG_NAME,
2515 p_init_msg_list,
2516 l_api_version,
2517 p_api_version,
2518 '_PVT',
2519 x_return_status);
2520 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2521 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2522 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2523 RAISE OKC_API.G_EXCEPTION_ERROR;
2524 END IF;
2525 --------------------------------------
2526 -- Move VIEW record to "Child" records
2527 --------------------------------------
2528 migrate(l_tasv_rec, l_tas_rec);
2529 --------------------------------------------
2530 -- Call the DELETE_ROW for each child record
2531 --------------------------------------------
2532 delete_row(
2533 p_init_msg_list,
2534 x_return_status,
2535 x_msg_count,
2536 x_msg_data,
2537 l_tas_rec
2538 );
2539 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2541 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2542 RAISE OKC_API.G_EXCEPTION_ERROR;
2543 END IF;
2544 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2545 EXCEPTION
2546 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2547 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2548 (
2549 l_api_name,
2550 G_PKG_NAME,
2551 'OKC_API.G_RET_STS_ERROR',
2552 x_msg_count,
2553 x_msg_data,
2554 '_PVT'
2555 );
2556 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2557 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2558 (
2559 l_api_name,
2560 G_PKG_NAME,
2561 'OKC_API.G_RET_STS_UNEXP_ERROR',
2562 x_msg_count,
2563 x_msg_data,
2564 '_PVT'
2565 );
2566 WHEN OTHERS THEN
2567 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2568 (
2569 l_api_name,
2570 G_PKG_NAME,
2571 'OTHERS',
2572 x_msg_count,
2573 x_msg_data,
2574 '_PVT'
2575 );
2576 END delete_row;
2577 ----------------------------------------
2578 -- PL/SQL TBL delete_row for:TASV_TBL --
2579 ----------------------------------------
2580 PROCEDURE delete_row(
2581 p_api_version IN NUMBER,
2582 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2583 x_return_status OUT NOCOPY VARCHAR2,
2584 x_msg_count OUT NOCOPY NUMBER,
2585 x_msg_data OUT NOCOPY VARCHAR2,
2586 p_tasv_tbl IN tasv_tbl_type) IS
2587
2588 l_api_version CONSTANT NUMBER := 1;
2589 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2590 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2591 i NUMBER := 0;
2592 BEGIN
2593 OKC_API.init_msg_list(p_init_msg_list);
2594 -- Make sure PL/SQL table has records in it before passing
2595 IF (p_tasv_tbl.COUNT > 0) THEN
2596 i := p_tasv_tbl.FIRST;
2597 LOOP
2598 delete_row (
2599 p_api_version => p_api_version,
2600 p_init_msg_list => OKC_API.G_FALSE,
2601 x_return_status => x_return_status,
2602 x_msg_count => x_msg_count,
2603 x_msg_data => x_msg_data,
2604 p_tasv_rec => p_tasv_tbl(i));
2605 EXIT WHEN (i = p_tasv_tbl.LAST);
2606 i := p_tasv_tbl.NEXT(i);
2607 END LOOP;
2608 END IF;
2609 EXCEPTION
2610 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2611 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2612 (
2613 l_api_name,
2614 G_PKG_NAME,
2615 'OKC_API.G_RET_STS_ERROR',
2616 x_msg_count,
2617 x_msg_data,
2618 '_PVT'
2619 );
2620 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2621 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2622 (
2623 l_api_name,
2624 G_PKG_NAME,
2625 'OKC_API.G_RET_STS_UNEXP_ERROR',
2626 x_msg_count,
2627 x_msg_data,
2628 '_PVT'
2629 );
2630 WHEN OTHERS THEN
2631 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2632 (
2633 l_api_name,
2634 G_PKG_NAME,
2635 'OTHERS',
2636 x_msg_count,
2637 x_msg_data,
2638 '_PVT'
2639 );
2640 END delete_row;
2641 END OKL_TAS_PVT;