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