[Home] [Help]
PACKAGE BODY: APPS.OKL_AGR_PVT
Source
1 PACKAGE BODY OKL_AGR_PVT AS
2 /* $Header: OKLSAGRB.pls 120.3 2006/07/13 12:51:42 adagur noship $ */
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_RULES
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_agr_rec IN agr_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN agr_rec_type IS
42 CURSOR agr_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 AE_LINE_TYPE,
46 ORG_ID,
47 SET_OF_BOOKS_ID,
48 OBJECT_VERSION_NUMBER,
49 CREATED_BY,
50 CREATION_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATE_LOGIN
54 FROM Okl_Acc_Gen_Rules
55 WHERE okl_acc_gen_rules.id = p_id;
56 l_agr_pk agr_pk_csr%ROWTYPE;
57 l_agr_rec agr_rec_type;
58 BEGIN
59 x_no_data_found := TRUE;
60 -- Get current database values
61 OPEN agr_pk_csr (p_agr_rec.id);
62 FETCH agr_pk_csr INTO
63 l_agr_rec.ID,
64 l_agr_rec.AE_LINE_TYPE,
65 l_agr_rec.ORG_ID,
66 l_agr_rec.SET_OF_BOOKS_ID,
67 l_agr_rec.OBJECT_VERSION_NUMBER,
68 l_agr_rec.CREATED_BY,
69 l_agr_rec.CREATION_DATE,
70 l_agr_rec.LAST_UPDATED_BY,
71 l_agr_rec.LAST_UPDATE_DATE,
72 l_agr_rec.LAST_UPDATE_LOGIN;
73 x_no_data_found := agr_pk_csr%NOTFOUND;
74 CLOSE agr_pk_csr;
75 RETURN(l_agr_rec);
76 END get_rec;
77
78 FUNCTION get_rec (
79 p_agr_rec IN agr_rec_type
80 ) RETURN agr_rec_type IS
81 l_row_notfound BOOLEAN := TRUE;
82 BEGIN
83 RETURN(get_rec(p_agr_rec, l_row_notfound));
84 END get_rec;
85 ---------------------------------------------------------------------------
86 -- FUNCTION get_rec for: OKL_ACC_GEN_RULES_V
87 ---------------------------------------------------------------------------
88 FUNCTION get_rec (
89 p_agrv_rec IN agrv_rec_type,
90 x_no_data_found OUT NOCOPY BOOLEAN
91 ) RETURN agrv_rec_type IS
92 CURSOR okl_agrv_pk_csr (p_id IN NUMBER) IS
93 SELECT
94 ID,
95 OBJECT_VERSION_NUMBER,
96 AE_LINE_TYPE,
97 SET_OF_BOOKS_ID,
98 ORG_ID,
99 CREATED_BY,
100 CREATION_DATE,
101 LAST_UPDATED_BY,
102 LAST_UPDATE_DATE,
103 LAST_UPDATE_LOGIN
104 FROM Okl_Acc_Gen_Rules_V
105 WHERE okl_acc_gen_rules_v.id = p_id;
106 l_okl_agrv_pk okl_agrv_pk_csr%ROWTYPE;
107 l_agrv_rec agrv_rec_type;
108 BEGIN
109 x_no_data_found := TRUE;
110 -- Get current database values
111 OPEN okl_agrv_pk_csr (p_agrv_rec.id);
112 FETCH okl_agrv_pk_csr INTO
113 l_agrv_rec.ID,
114 l_agrv_rec.OBJECT_VERSION_NUMBER,
115 l_agrv_rec.AE_LINE_TYPE,
116 l_agrv_rec.SET_OF_BOOKS_ID,
117 l_agrv_rec.ORG_ID,
118 l_agrv_rec.CREATED_BY,
119 l_agrv_rec.CREATION_DATE,
120 l_agrv_rec.LAST_UPDATED_BY,
121 l_agrv_rec.LAST_UPDATE_DATE,
122 l_agrv_rec.LAST_UPDATE_LOGIN;
123 x_no_data_found := okl_agrv_pk_csr%NOTFOUND;
124 CLOSE okl_agrv_pk_csr;
125 RETURN(l_agrv_rec);
126 END get_rec;
127
128 FUNCTION get_rec (
129 p_agrv_rec IN agrv_rec_type
130 ) RETURN agrv_rec_type IS
131 l_row_notfound BOOLEAN := TRUE;
132 BEGIN
133 RETURN(get_rec(p_agrv_rec, l_row_notfound));
134 END get_rec;
135
136 ---------------------------------------------------------
137 -- FUNCTION null_out_defaults for: OKL_ACC_GEN_RULES_V --
138 ---------------------------------------------------------
139 FUNCTION null_out_defaults (
140 p_agrv_rec IN agrv_rec_type
141 ) RETURN agrv_rec_type IS
142 l_agrv_rec agrv_rec_type := p_agrv_rec;
143 BEGIN
144 IF (l_agrv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
145 l_agrv_rec.object_version_number := NULL;
146 END IF;
147 IF (l_agrv_rec.ae_line_type = OKC_API.G_MISS_CHAR) THEN
148 l_agrv_rec.ae_line_type := NULL;
149 END IF;
150 IF (l_agrv_rec.set_of_books_id = OKC_API.G_MISS_NUM) THEN
151 l_agrv_rec.set_of_books_id := NULL;
152 END IF;
153 IF (l_agrv_rec.org_id = OKC_API.G_MISS_NUM) THEN
154 l_agrv_rec.org_id := NULL;
155 END IF;
156 IF (l_agrv_rec.created_by = OKC_API.G_MISS_NUM) THEN
157 l_agrv_rec.created_by := NULL;
158 END IF;
159 IF (l_agrv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
160 l_agrv_rec.creation_date := NULL;
161 END IF;
162 IF (l_agrv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
163 l_agrv_rec.last_updated_by := NULL;
164 END IF;
165 IF (l_agrv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
166 l_agrv_rec.last_update_date := NULL;
167 END IF;
168 IF (l_agrv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
169 l_agrv_rec.last_update_login := NULL;
170 END IF;
171 RETURN(l_agrv_rec);
172 END null_out_defaults;
173
174 /*****************************************************
175 05-10-01 : spalod : start - commented out nocopy tapi code
176 ---------------------------------------------------------------------------
177 -- PROCEDURE Validate_Attributes
178 ---------------------------------------------------------------------------
179 -------------------------------------------------
180 -- Validate_Attributes for:OKL_ACC_GEN_RULES_V --
181 -------------------------------------------------
182 FUNCTION Validate_Attributes (
183 p_agrv_rec IN agrv_rec_type
184 ) RETURN VARCHAR2 IS
185 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
186 BEGIN
187 IF p_agrv_rec.id = OKC_API.G_MISS_NUM OR
188 p_agrv_rec.id IS NULL
189 THEN
190 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
191 l_return_status := OKC_API.G_RET_STS_ERROR;
192 ELSIF p_agrv_rec.object_version_number = OKC_API.G_MISS_NUM OR
193 p_agrv_rec.object_version_number IS NULL
194 THEN
195 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
196 l_return_status := OKC_API.G_RET_STS_ERROR;
197 ELSIF p_agrv_rec.ae_line_type = OKC_API.G_MISS_CHAR OR
198 p_agrv_rec.ae_line_type IS NULL
199 THEN
200 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ae_line_type');
201 l_return_status := OKC_API.G_RET_STS_ERROR;
202 ELSIF p_agrv_rec.set_of_books_id = OKC_API.G_MISS_NUM OR
203 p_agrv_rec.set_of_books_id IS NULL
204 THEN
205 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'set_of_books_id');
206 l_return_status := OKC_API.G_RET_STS_ERROR;
207 ELSIF p_agrv_rec.org_id = OKC_API.G_MISS_NUM OR
208 p_agrv_rec.org_id IS NULL
209 THEN
210 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'org_id');
211 l_return_status := OKC_API.G_RET_STS_ERROR;
212 END IF;
213 RETURN(l_return_status);
214 END Validate_Attributes;
215
216 05-10-01 : spalod : end - commented out nocopy tapi code
217 ****************************************************/
218
219 -- 05-10-01 : spalod : start - procedures for validateing attributes
220
221 ---------------------------------------------------------------------------
222 -- PROCEDURE Validate_Id
223 ---------------------------------------------------------------------------
224 -- Start of comments
225 --
226 -- Procedure Name : Validate_Id
227 -- Description :
228 -- Business Rules :
229 -- Parameters :
230 -- Version : 1.0
231 -- End of comments
232 ---------------------------------------------------------------------------
233 PROCEDURE Validate_Id (x_return_status OUT NOCOPY VARCHAR2
234 ,p_agrv_rec IN agrv_rec_type )
235 IS
236
237 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
238
239 BEGIN
240 -- initialize return status
241 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
242 -- check for data before processing
243 IF (p_agrv_rec.id IS NULL) OR
244 (p_agrv_rec.id = Okc_Api.G_MISS_NUM) THEN
245 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
246 ,p_msg_name => g_required_value
247 ,p_token1 => g_col_name_token
248 ,p_token1_value => 'id');
249 x_return_status := Okc_Api.G_RET_STS_ERROR;
250 RAISE G_EXCEPTION_HALT_VALIDATION;
251 END IF;
252
253 EXCEPTION
254 WHEN G_EXCEPTION_HALT_VALIDATION THEN
255 -- no processing necessary; validation can continue
256 -- with the next column
257 NULL;
258
259 WHEN OTHERS THEN
260 -- store SQL error message on message stack for caller
261 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
262 p_msg_name => g_unexpected_error,
263 p_token1 => g_sqlcode_token,
264 p_token1_value => SQLCODE,
265 p_token2 => g_sqlerrm_token,
266 p_token2_value => SQLERRM);
267
268 -- notify caller of an UNEXPECTED error
269 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
270
271 END Validate_Id;
272
273 ---------------------------------------------------------------------------
274 -- PROCEDURE Validate_Object_Version_Number
275 ---------------------------------------------------------------------------
276 -- Start of comments
277 --
278 -- Procedure Name : Validate_Object_Version_Number
279 -- Description :
280 -- Business Rules :
281 -- Parameters :
282 -- Version : 1.0
283 -- End of comments
284 ---------------------------------------------------------------------------
285 PROCEDURE Validate_Object_Version_Number(x_return_status OUT NOCOPY VARCHAR2
286 ,p_agrv_rec IN agrv_rec_type )
287 IS
288
289 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
290
291 BEGIN
292 -- initialize return status
293 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
294 -- check for data before processing
295 IF (p_agrv_rec.object_version_number IS NULL) OR
296 (p_agrv_rec.object_version_number = Okc_Api.G_MISS_NUM) THEN
297 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
298 ,p_msg_name => g_required_value
299 ,p_token1 => g_col_name_token
300 ,p_token1_value => 'object_version_number');
301 x_return_status := Okc_Api.G_RET_STS_ERROR;
302 RAISE G_EXCEPTION_HALT_VALIDATION;
303 END IF;
304
305 EXCEPTION
306 WHEN G_EXCEPTION_HALT_VALIDATION THEN
307 -- no processing necessary; validation can continue
308 -- with the next column
309 NULL;
310
311 WHEN OTHERS THEN
312 -- store SQL error message on message stack for caller
313 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
314 p_msg_name => g_unexpected_error,
315 p_token1 => g_sqlcode_token,
316 p_token1_value => SQLCODE,
317 p_token2 => g_sqlerrm_token,
318 p_token2_value => SQLERRM);
319
320 -- notify caller of an UNEXPECTED error
321 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
322
323 END Validate_Object_Version_Number;
324
325 ---------------------------------------------------------------------------
326 -- PROCEDURE validate_ae_line_type
327 ---------------------------------------------------------------------------
328 PROCEDURE validate_ae_line_type(
329 x_return_status OUT NOCOPY VARCHAR2,
330 p_agrv_rec IN agrv_rec_type
331 ) IS
332
333 l_dummy VARCHAR2(1) := OKC_API.G_FALSE;
334
335 BEGIN
336 x_return_status := OKC_API.G_RET_STS_SUCCESS;
337 IF (p_agrv_rec.ae_line_type IS NULL) OR (p_agrv_rec.ae_line_type = OKC_API.G_MISS_CHAR) THEN
338 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
339 p_msg_name => g_required_value,
340 p_token1 => g_col_name_token,
341 p_token1_value => 'AE_LINE_TYPE');
342
343 x_return_status := OKC_API.G_RET_STS_ERROR;
344 RAISE G_EXCEPTION_HALT_VALIDATION;
345 END IF;
346
347 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE
348 (p_lookup_type => 'OKL_AE_LINE_TYPE',
349 p_lookup_code => p_agrv_rec.ae_line_type);
350
351 IF (l_dummy = okl_api.g_false) THEN
352 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
353 p_msg_name => g_invalid_value,
354 p_token1 => g_col_name_token,
355 p_token1_value => 'AE_LINE_TYPE');
356 x_return_status := Okc_Api.G_RET_STS_ERROR;
357 RAISE G_EXCEPTION_HALT_VALIDATION;
358 END IF;
359
360 EXCEPTION
361 WHEN G_EXCEPTION_HALT_VALIDATION THEN
362 NULL;
363 WHEN OTHERS THEN
364 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
365 p_msg_name => g_required_value,
366 p_token1 => g_sqlcode_token,
367 p_token1_value => SQLCODE,
368 p_token2 => g_sqlerrm_token,
369 p_token2_value => SQLERRM);
370 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
371 END validate_ae_line_type;
372
373 ---------------------------------------------------------------------------
374 -- PROCEDURE Validate_Attributes
375 ---------------------------------------------------------------------------
376 -------------------------------------------------
377 -- Validate_Attributes for:OKL_ACC_GEN_RULES_V --
378 -------------------------------------------------
379 FUNCTION Validate_Attributes (
380 p_agrv_rec IN agrv_rec_type
381 ) RETURN VARCHAR2 IS
382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
383 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
384 BEGIN
385
386 Validate_Id (x_return_status => x_return_status
387 ,p_agrv_rec => p_agrv_rec);
388
389 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
390 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
391 -- need to leave
392 l_return_status := x_return_status;
393 RAISE G_EXCEPTION_HALT_VALIDATION;
394 ELSE
395 -- record that there was an error
396 l_return_status := x_return_status;
397 END IF;
398
399 END IF;
400
401 Validate_Object_Version_Number(x_return_status => x_return_status
402 ,p_agrv_rec => p_agrv_rec);
403
404 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
405 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
406 -- need to leave
407 l_return_Status := x_return_Status;
408 RAISE G_EXCEPTION_HALT_VALIDATION;
409 ELSE
410 -- record that there was an error
411 l_return_Status := x_return_Status;
412 END IF;
413
414 END IF;
415
416 validate_ae_line_type(x_return_status => x_return_status
417 ,p_agrv_rec => p_agrv_rec);
418
419 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
420 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
421 -- need to leave
422 l_return_Status := x_return_Status;
423 RAISE G_EXCEPTION_HALT_VALIDATION;
424 ELSE
425 -- record that there was an error
426 l_return_Status := x_return_Status;
427 END IF;
428
429 END IF;
430
431 RETURN(l_return_status);
432
433 EXCEPTION
434 WHEN G_EXCEPTION_HALT_VALIDATION THEN
435 NULL;
436 RETURN (l_return_status);
437
438 WHEN OTHERS THEN
439 -- store SQL error message on message stack for caller
440 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
441 p_msg_name => g_unexpected_error,
442 p_token1 => g_sqlcode_token,
443 p_token1_value => SQLCODE,
444 p_token2 => g_sqlerrm_token,
445 p_token2_value => SQLERRM);
446 -- notify caller of an UNEXPECTED error
447 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
448 RETURN(l_return_status);
449
450 END Validate_Attributes;
451
452 -- 05-10-01 : spalod : end - procedures for validateing attributes
453
454
455 ---------------------------------------------------------------------------
456 -- PROCEDURE Validate_Record
457 ---------------------------------------------------------------------------
458 ---------------------------------------------
459 -- Validate_Record for:OKL_ACC_GEN_RULES_V --
460 ---------------------------------------------
461 FUNCTION Validate_Record (
462 p_agrv_rec IN agrv_rec_type
463 ) RETURN VARCHAR2 IS
464 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
465 BEGIN
466 RETURN (l_return_status);
467 END Validate_Record;
468
469 ---------------------------------------------------------------------------
470 -- PROCEDURE Migrate
471 ---------------------------------------------------------------------------
472 PROCEDURE migrate (
473 p_from IN agrv_rec_type,
474 p_to OUT NOCOPY agr_rec_type
475 ) IS
476 BEGIN
477 p_to.id := p_from.id;
478 p_to.ae_line_type := p_from.ae_line_type;
479 p_to.org_id := p_from.org_id;
480 p_to.set_of_books_id := p_from.set_of_books_id;
481 p_to.object_version_number := p_from.object_version_number;
482 p_to.created_by := p_from.created_by;
483 p_to.creation_date := p_from.creation_date;
484 p_to.last_updated_by := p_from.last_updated_by;
485 p_to.last_update_date := p_from.last_update_date;
486 p_to.last_update_login := p_from.last_update_login;
487 END migrate;
488 PROCEDURE migrate (
489 p_from IN agr_rec_type,
490 p_to OUT NOCOPY agrv_rec_type
491 ) IS
492 BEGIN
493 p_to.id := p_from.id;
494 p_to.ae_line_type := p_from.ae_line_type;
495 p_to.org_id := p_from.org_id;
496 p_to.set_of_books_id := p_from.set_of_books_id;
497 p_to.object_version_number := p_from.object_version_number;
498 p_to.created_by := p_from.created_by;
499 p_to.creation_date := p_from.creation_date;
500 p_to.last_updated_by := p_from.last_updated_by;
501 p_to.last_update_date := p_from.last_update_date;
502 p_to.last_update_login := p_from.last_update_login;
503 END migrate;
504
505 ---------------------------------------------------------------------------
506 -- PROCEDURE validate_row
507 ---------------------------------------------------------------------------
508 ------------------------------------------
509 -- validate_row for:OKL_ACC_GEN_RULES_V --
510 ------------------------------------------
511 PROCEDURE validate_row(
512 p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2,
517 p_agrv_rec IN agrv_rec_type) IS
518
519 l_api_version CONSTANT NUMBER := 1;
520 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
521 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
522 l_agrv_rec agrv_rec_type := p_agrv_rec;
523 l_agr_rec agr_rec_type;
524 BEGIN
525 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
526 G_PKG_NAME,
527 p_init_msg_list,
528 l_api_version,
529 p_api_version,
530 '_PVT',
531 x_return_status);
532 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
533 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
534 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
535 RAISE OKC_API.G_EXCEPTION_ERROR;
536 END IF;
537 --- Validate all non-missing attributes (Item Level Validation)
538 l_return_status := Validate_Attributes(l_agrv_rec);
539 --- If any errors happen abort API
540 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
541 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
542 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
543 RAISE OKC_API.G_EXCEPTION_ERROR;
544 END IF;
545 l_return_status := Validate_Record(l_agrv_rec);
546 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
547 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
549 RAISE OKC_API.G_EXCEPTION_ERROR;
550 END IF;
551 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
552 EXCEPTION
553 WHEN OKC_API.G_EXCEPTION_ERROR THEN
554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
555 (
556 l_api_name,
557 G_PKG_NAME,
558 'OKC_API.G_RET_STS_ERROR',
559 x_msg_count,
560 x_msg_data,
561 '_PVT'
562 );
563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
564 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
565 (
566 l_api_name,
567 G_PKG_NAME,
568 'OKC_API.G_RET_STS_UNEXP_ERROR',
569 x_msg_count,
570 x_msg_data,
571 '_PVT'
572 );
573 WHEN OTHERS THEN
574 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
575 (
576 l_api_name,
577 G_PKG_NAME,
578 'OTHERS',
579 x_msg_count,
580 x_msg_data,
581 '_PVT'
582 );
583 END validate_row;
584 ------------------------------------------
585 -- PL/SQL TBL validate_row for:AGRV_TBL --
586 ------------------------------------------
587 PROCEDURE validate_row(
588 p_api_version IN NUMBER,
589 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
590 x_return_status OUT NOCOPY VARCHAR2,
591 x_msg_count OUT NOCOPY NUMBER,
592 x_msg_data OUT NOCOPY VARCHAR2,
593 p_agrv_tbl IN agrv_tbl_type) IS
594
595 l_api_version CONSTANT NUMBER := 1;
596 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
597 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
598 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
599 i NUMBER := 0;
600 BEGIN
601 OKC_API.init_msg_list(p_init_msg_list);
602 -- Make sure PL/SQL table has records in it before passing
603 IF (p_agrv_tbl.COUNT > 0) THEN
604 i := p_agrv_tbl.FIRST;
605 LOOP
606 validate_row (
607 p_api_version => p_api_version,
608 p_init_msg_list => OKC_API.G_FALSE,
609 x_return_status => x_return_status,
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data,
612 p_agrv_rec => p_agrv_tbl(i));
613
614 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
615 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
616 l_overall_status := x_return_status;
617 END IF;
618 END IF;
619
620 EXIT WHEN (i = p_agrv_tbl.LAST);
621 i := p_agrv_tbl.NEXT(i);
622 END LOOP;
623 END IF;
624 x_return_status := l_overall_status;
625 EXCEPTION
626 WHEN OKC_API.G_EXCEPTION_ERROR THEN
627 x_return_status := OKC_API.HANDLE_EXCEPTIONS
628 (
629 l_api_name,
630 G_PKG_NAME,
631 'OKC_API.G_RET_STS_ERROR',
632 x_msg_count,
633 x_msg_data,
634 '_PVT'
635 );
636 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
637 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
638 (
639 l_api_name,
640 G_PKG_NAME,
641 'OKC_API.G_RET_STS_UNEXP_ERROR',
642 x_msg_count,
643 x_msg_data,
644 '_PVT'
645 );
646 WHEN OTHERS THEN
647 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
648 (
649 l_api_name,
650 G_PKG_NAME,
651 'OTHERS',
652 x_msg_count,
653 x_msg_data,
654 '_PVT'
655 );
656 END validate_row;
657
658 ---------------------------------------------------------------------------
659 -- PROCEDURE insert_row
660 ---------------------------------------------------------------------------
661 --------------------------------------
662 -- insert_row for:OKL_ACC_GEN_RULES --
663 --------------------------------------
664 PROCEDURE insert_row(
665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 p_agr_rec IN agr_rec_type,
670 x_agr_rec OUT NOCOPY agr_rec_type) IS
671
672 l_api_version CONSTANT NUMBER := 1;
673 l_api_name CONSTANT VARCHAR2(30) := 'RULES_insert_row';
674 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
675 l_agr_rec agr_rec_type := p_agr_rec;
676 l_def_agr_rec agr_rec_type;
677 ------------------------------------------
678 -- Set_Attributes for:OKL_ACC_GEN_RULES --
679 ------------------------------------------
680 FUNCTION Set_Attributes (
681 p_agr_rec IN agr_rec_type,
682 x_agr_rec OUT NOCOPY agr_rec_type
683 ) RETURN VARCHAR2 IS
684 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
685 BEGIN
686 x_agr_rec := p_agr_rec;
687 RETURN(l_return_status);
688 END Set_Attributes;
689 BEGIN
690 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
691 p_init_msg_list,
692 '_PVT',
693 x_return_status);
694 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
695 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
696 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
697 RAISE OKC_API.G_EXCEPTION_ERROR;
698 END IF;
699 --- Setting item attributes
700 l_return_status := Set_Attributes(
701 p_agr_rec, -- IN
702 l_agr_rec); -- OUT
703 --- If any errors happen abort API
704 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
705 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
706 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
707 RAISE OKC_API.G_EXCEPTION_ERROR;
708 END IF;
709 INSERT INTO OKL_ACC_GEN_RULES(
710 id,
711 ae_line_type,
712 org_id,
713 set_of_books_id,
714 object_version_number,
715 created_by,
716 creation_date,
717 last_updated_by,
718 last_update_date,
719 last_update_login)
720 VALUES (
721 l_agr_rec.id,
722 l_agr_rec.ae_line_type,
723 l_agr_rec.org_id,
724 l_agr_rec.set_of_books_id,
725 l_agr_rec.object_version_number,
726 l_agr_rec.created_by,
727 l_agr_rec.creation_date,
728 l_agr_rec.last_updated_by,
729 l_agr_rec.last_update_date,
730 l_agr_rec.last_update_login);
731 -- Set OUT values
732 x_agr_rec := l_agr_rec;
733 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
734 EXCEPTION
735 WHEN OKC_API.G_EXCEPTION_ERROR THEN
736 x_return_status := OKC_API.HANDLE_EXCEPTIONS
737 (
738 l_api_name,
739 G_PKG_NAME,
740 'OKC_API.G_RET_STS_ERROR',
741 x_msg_count,
742 x_msg_data,
743 '_PVT'
744 );
745 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
746 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
747 (
748 l_api_name,
749 G_PKG_NAME,
750 'OKC_API.G_RET_STS_UNEXP_ERROR',
751 x_msg_count,
752 x_msg_data,
753 '_PVT'
754 );
755 WHEN OTHERS THEN
756 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
757 (
758 l_api_name,
759 G_PKG_NAME,
760 'OTHERS',
761 x_msg_count,
762 x_msg_data,
763 '_PVT'
764 );
765 END insert_row;
766 ----------------------------------------
767 -- insert_row for:OKL_ACC_GEN_RULES_V --
768 ----------------------------------------
769 PROCEDURE insert_row(
770 p_api_version IN NUMBER,
771 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
772 x_return_status OUT NOCOPY VARCHAR2,
773 x_msg_count OUT NOCOPY NUMBER,
774 x_msg_data OUT NOCOPY VARCHAR2,
775 p_agrv_rec IN agrv_rec_type,
776 x_agrv_rec OUT NOCOPY agrv_rec_type) IS
777
778 l_api_version CONSTANT NUMBER := 1;
779 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
780 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
781 l_agrv_rec agrv_rec_type;
782 l_def_agrv_rec agrv_rec_type;
783 l_agr_rec agr_rec_type;
784 lx_agr_rec agr_rec_type;
785 -------------------------------
786 -- FUNCTION fill_who_columns --
787 -------------------------------
788 FUNCTION fill_who_columns (
789 p_agrv_rec IN agrv_rec_type
790 ) RETURN agrv_rec_type IS
791 l_agrv_rec agrv_rec_type := p_agrv_rec;
792 BEGIN
793 l_agrv_rec.CREATION_DATE := SYSDATE;
794 l_agrv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
795 l_agrv_rec.LAST_UPDATE_DATE := SYSDATE;
796 l_agrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
797 l_agrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
798 RETURN(l_agrv_rec);
799 END fill_who_columns;
800 --------------------------------------------
801 -- Set_Attributes for:OKL_ACC_GEN_RULES_V --
802 --------------------------------------------
803 FUNCTION Set_Attributes (
804 p_agrv_rec IN agrv_rec_type,
805 x_agrv_rec OUT NOCOPY agrv_rec_type
806 ) RETURN VARCHAR2 IS
807 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
808 BEGIN
809 x_agrv_rec := p_agrv_rec;
810 x_agrv_rec.OBJECT_VERSION_NUMBER := 1;
811 x_agrv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
812 x_agrv_rec.set_of_books_id := okl_accounting_util.get_set_of_books_id;
813 RETURN(l_return_status);
814 END Set_Attributes;
815 BEGIN
816 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
817 G_PKG_NAME,
818 p_init_msg_list,
819 l_api_version,
820 p_api_version,
821 '_PVT',
822 x_return_status);
823 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
824 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
825 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
826 RAISE OKC_API.G_EXCEPTION_ERROR;
827 END IF;
828 l_agrv_rec := null_out_defaults(p_agrv_rec);
829 -- Set primary key value
830 l_agrv_rec.ID := get_seq_id;
831 --- Setting item attributes
832 l_return_status := Set_Attributes(
833 l_agrv_rec, -- IN
834 l_def_agrv_rec); -- OUT
835 --- If any errors happen abort API
836 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
837 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
838 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
839 RAISE OKC_API.G_EXCEPTION_ERROR;
840 END IF;
841 l_def_agrv_rec := fill_who_columns(l_def_agrv_rec);
842 --- Validate all non-missing attributes (Item Level Validation)
843 l_return_status := Validate_Attributes(l_def_agrv_rec);
844 --- If any errors happen abort API
845 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
846 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
847 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
848 RAISE OKC_API.G_EXCEPTION_ERROR;
849 END IF;
850 -- l_return_status := Validate_Record(l_def_agrv_rec);
851 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
852 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
853 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
854 RAISE OKC_API.G_EXCEPTION_ERROR;
855 END IF;
856 --------------------------------------
857 -- Move VIEW record to "Child" records
858 --------------------------------------
859 migrate(l_def_agrv_rec, l_agr_rec);
860 --------------------------------------------
861 -- Call the INSERT_ROW for each child record
862 --------------------------------------------
863 insert_row(
864 p_init_msg_list,
865 x_return_status,
866 x_msg_count,
867 x_msg_data,
868 l_agr_rec,
869 lx_agr_rec
870 );
871 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
872 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
873 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
874 RAISE OKC_API.G_EXCEPTION_ERROR;
875 END IF;
876 migrate(lx_agr_rec, l_def_agrv_rec);
877 -- Set OUT values
878 x_agrv_rec := l_def_agrv_rec;
879 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
880 EXCEPTION
881 WHEN OKC_API.G_EXCEPTION_ERROR THEN
882 x_return_status := OKC_API.HANDLE_EXCEPTIONS
883 (
884 l_api_name,
885 G_PKG_NAME,
886 'OKC_API.G_RET_STS_ERROR',
887 x_msg_count,
888 x_msg_data,
889 '_PVT'
890 );
891 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
892 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
893 (
894 l_api_name,
895 G_PKG_NAME,
896 'OKC_API.G_RET_STS_UNEXP_ERROR',
897 x_msg_count,
898 x_msg_data,
899 '_PVT'
900 );
901 WHEN OTHERS THEN
902 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
903 (
904 l_api_name,
905 G_PKG_NAME,
906 'OTHERS',
907 x_msg_count,
908 x_msg_data,
909 '_PVT'
910 );
911 END insert_row;
912 ----------------------------------------
913 -- PL/SQL TBL insert_row for:AGRV_TBL --
914 ----------------------------------------
915 PROCEDURE insert_row(
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_msg_count OUT NOCOPY NUMBER,
920 x_msg_data OUT NOCOPY VARCHAR2,
921 p_agrv_tbl IN agrv_tbl_type,
922 x_agrv_tbl OUT NOCOPY agrv_tbl_type) IS
923
924 l_api_version CONSTANT NUMBER := 1;
925 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
926 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
927 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
928 i NUMBER := 0;
929 BEGIN
930 OKC_API.init_msg_list(p_init_msg_list);
931 -- Make sure PL/SQL table has records in it before passing
932 IF (p_agrv_tbl.COUNT > 0) THEN
933 i := p_agrv_tbl.FIRST;
934 LOOP
935 insert_row (
936 p_api_version => p_api_version,
937 p_init_msg_list => OKC_API.G_FALSE,
938 x_return_status => x_return_status,
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data,
941 p_agrv_rec => p_agrv_tbl(i),
942 x_agrv_rec => x_agrv_tbl(i));
943
944 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
945 IF (l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
946 l_overall_status := x_return_status;
947 END IF;
948 END IF;
949
950 EXIT WHEN (i = p_agrv_tbl.LAST);
951 i := p_agrv_tbl.NEXT(i);
952 END LOOP;
953 END IF;
954 x_return_status := l_overall_status;
955
956 EXCEPTION
957 WHEN OKC_API.G_EXCEPTION_ERROR THEN
958 x_return_status := OKC_API.HANDLE_EXCEPTIONS
959 (
960 l_api_name,
961 G_PKG_NAME,
962 'OKC_API.G_RET_STS_ERROR',
963 x_msg_count,
964 x_msg_data,
965 '_PVT'
966 );
967 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
968 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
969 (
970 l_api_name,
971 G_PKG_NAME,
972 'OKC_API.G_RET_STS_UNEXP_ERROR',
973 x_msg_count,
974 x_msg_data,
975 '_PVT'
976 );
977 WHEN OTHERS THEN
978 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
979 (
980 l_api_name,
981 G_PKG_NAME,
982 'OTHERS',
983 x_msg_count,
984 x_msg_data,
985 '_PVT'
986 );
987 END insert_row;
988
989 ---------------------------------------------------------------------------
990 -- PROCEDURE lock_row
991 ---------------------------------------------------------------------------
992 ------------------------------------
993 -- lock_row for:OKL_ACC_GEN_RULES --
994 ------------------------------------
995 PROCEDURE lock_row(
996 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
997 x_return_status OUT NOCOPY VARCHAR2,
998 x_msg_count OUT NOCOPY NUMBER,
999 x_msg_data OUT NOCOPY VARCHAR2,
1000 p_agr_rec IN agr_rec_type) IS
1001
1002 E_Resource_Busy EXCEPTION;
1003 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1004 CURSOR lock_csr (p_agr_rec IN agr_rec_type) IS
1005 SELECT OBJECT_VERSION_NUMBER
1006 FROM OKL_ACC_GEN_RULES
1007 WHERE ID = p_agr_rec.id
1008 AND OBJECT_VERSION_NUMBER = p_agr_rec.object_version_number
1009 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1010
1011 CURSOR lchk_csr (p_agr_rec IN agr_rec_type) IS
1012 SELECT OBJECT_VERSION_NUMBER
1013 FROM OKL_ACC_GEN_RULES
1014 WHERE ID = p_agr_rec.id;
1015 l_api_version CONSTANT NUMBER := 1;
1016 l_api_name CONSTANT VARCHAR2(30) := 'RULES_lock_row';
1017 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1018 l_object_version_number OKL_ACC_GEN_RULES.OBJECT_VERSION_NUMBER%TYPE;
1019 lc_object_version_number OKL_ACC_GEN_RULES.OBJECT_VERSION_NUMBER%TYPE;
1020 l_row_notfound BOOLEAN := FALSE;
1021 lc_row_notfound BOOLEAN := FALSE;
1022 BEGIN
1023 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1024 p_init_msg_list,
1025 '_PVT',
1026 x_return_status);
1027 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1028 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1029 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1030 RAISE OKC_API.G_EXCEPTION_ERROR;
1031 END IF;
1032 BEGIN
1033 OPEN lock_csr(p_agr_rec);
1034 FETCH lock_csr INTO l_object_version_number;
1035 l_row_notfound := lock_csr%NOTFOUND;
1036 CLOSE lock_csr;
1037 EXCEPTION
1038 WHEN E_Resource_Busy THEN
1039 IF (lock_csr%ISOPEN) THEN
1040 CLOSE lock_csr;
1041 END IF;
1042 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1043 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1044 END;
1045
1046 IF ( l_row_notfound ) THEN
1047 OPEN lchk_csr(p_agr_rec);
1048 FETCH lchk_csr INTO lc_object_version_number;
1049 lc_row_notfound := lchk_csr%NOTFOUND;
1050 CLOSE lchk_csr;
1051 END IF;
1052 IF (lc_row_notfound) THEN
1053 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1054 RAISE OKC_API.G_EXCEPTION_ERROR;
1055 ELSIF lc_object_version_number > p_agr_rec.object_version_number THEN
1056 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1057 RAISE OKC_API.G_EXCEPTION_ERROR;
1058 ELSIF lc_object_version_number <> p_agr_rec.object_version_number THEN
1059 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1060 RAISE OKC_API.G_EXCEPTION_ERROR;
1061 ELSIF lc_object_version_number = -1 THEN
1062 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1063 RAISE OKC_API.G_EXCEPTION_ERROR;
1064 END IF;
1065 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1066 EXCEPTION
1067 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1068 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1069 (
1070 l_api_name,
1071 G_PKG_NAME,
1072 'OKC_API.G_RET_STS_ERROR',
1073 x_msg_count,
1074 x_msg_data,
1075 '_PVT'
1076 );
1077 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1078 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1079 (
1080 l_api_name,
1081 G_PKG_NAME,
1082 'OKC_API.G_RET_STS_UNEXP_ERROR',
1083 x_msg_count,
1084 x_msg_data,
1085 '_PVT'
1086 );
1087 WHEN OTHERS THEN
1088 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1089 (
1090 l_api_name,
1091 G_PKG_NAME,
1092 'OTHERS',
1093 x_msg_count,
1094 x_msg_data,
1095 '_PVT'
1096 );
1097 END lock_row;
1098 --------------------------------------
1099 -- lock_row for:OKL_ACC_GEN_RULES_V --
1100 --------------------------------------
1101 PROCEDURE lock_row(
1102 p_api_version IN NUMBER,
1103 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1104 x_return_status OUT NOCOPY VARCHAR2,
1105 x_msg_count OUT NOCOPY NUMBER,
1106 x_msg_data OUT NOCOPY VARCHAR2,
1107 p_agrv_rec IN agrv_rec_type) IS
1108
1109 l_api_version CONSTANT NUMBER := 1;
1110 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1111 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1112 l_agr_rec agr_rec_type;
1113 BEGIN
1114 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1115 G_PKG_NAME,
1116 p_init_msg_list,
1117 l_api_version,
1118 p_api_version,
1119 '_PVT',
1120 x_return_status);
1121 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1122 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1123 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1124 RAISE OKC_API.G_EXCEPTION_ERROR;
1125 END IF;
1126 --------------------------------------
1127 -- Move VIEW record to "Child" records
1128 --------------------------------------
1129 migrate(p_agrv_rec, l_agr_rec);
1130 --------------------------------------------
1131 -- Call the LOCK_ROW for each child record
1132 --------------------------------------------
1133 lock_row(
1134 p_init_msg_list,
1135 x_return_status,
1136 x_msg_count,
1137 x_msg_data,
1138 l_agr_rec
1139 );
1140 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1142 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1143 RAISE OKC_API.G_EXCEPTION_ERROR;
1144 END IF;
1145 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1146 EXCEPTION
1147 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1148 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1149 (
1150 l_api_name,
1151 G_PKG_NAME,
1152 'OKC_API.G_RET_STS_ERROR',
1153 x_msg_count,
1154 x_msg_data,
1155 '_PVT'
1156 );
1157 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1158 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1159 (
1160 l_api_name,
1161 G_PKG_NAME,
1162 'OKC_API.G_RET_STS_UNEXP_ERROR',
1163 x_msg_count,
1164 x_msg_data,
1165 '_PVT'
1166 );
1167 WHEN OTHERS THEN
1168 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1169 (
1170 l_api_name,
1171 G_PKG_NAME,
1172 'OTHERS',
1173 x_msg_count,
1174 x_msg_data,
1175 '_PVT'
1176 );
1177 END lock_row;
1178 --------------------------------------
1179 -- PL/SQL TBL lock_row for:AGRV_TBL --
1180 --------------------------------------
1181 PROCEDURE lock_row(
1182 p_api_version IN NUMBER,
1183 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_count OUT NOCOPY NUMBER,
1186 x_msg_data OUT NOCOPY VARCHAR2,
1187 p_agrv_tbl IN agrv_tbl_type) IS
1188
1189 l_api_version CONSTANT NUMBER := 1;
1190 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1191 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1192 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1193 i NUMBER := 0;
1194 BEGIN
1195 OKC_API.init_msg_list(p_init_msg_list);
1196 -- Make sure PL/SQL table has records in it before passing
1197 IF (p_agrv_tbl.COUNT > 0) THEN
1198 i := p_agrv_tbl.FIRST;
1199 LOOP
1200 lock_row (
1201 p_api_version => p_api_version,
1202 p_init_msg_list => OKC_API.G_FALSE,
1203 x_return_status => x_return_status,
1204 x_msg_count => x_msg_count,
1205 x_msg_data => x_msg_data,
1206 p_agrv_rec => p_agrv_tbl(i));
1207
1208 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1209 IF (l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1210 l_overall_status := x_return_status;
1211 END IF;
1212 END IF;
1213
1214 EXIT WHEN (i = p_agrv_tbl.LAST);
1215 i := p_agrv_tbl.NEXT(i);
1216 END LOOP;
1217 END IF;
1218 x_return_Status := l_overall_Status;
1219
1220 EXCEPTION
1221 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1222 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1223 (
1224 l_api_name,
1225 G_PKG_NAME,
1226 'OKC_API.G_RET_STS_ERROR',
1227 x_msg_count,
1228 x_msg_data,
1229 '_PVT'
1230 );
1231 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1232 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1233 (
1234 l_api_name,
1235 G_PKG_NAME,
1236 'OKC_API.G_RET_STS_UNEXP_ERROR',
1237 x_msg_count,
1238 x_msg_data,
1239 '_PVT'
1240 );
1241 WHEN OTHERS THEN
1242 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1243 (
1244 l_api_name,
1245 G_PKG_NAME,
1246 'OTHERS',
1247 x_msg_count,
1248 x_msg_data,
1249 '_PVT'
1250 );
1251 END lock_row;
1252
1253 ---------------------------------------------------------------------------
1254 -- PROCEDURE update_row
1255 ---------------------------------------------------------------------------
1256 --------------------------------------
1257 -- update_row for:OKL_ACC_GEN_RULES --
1258 --------------------------------------
1259 PROCEDURE update_row(
1260 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2,
1264 p_agr_rec IN agr_rec_type,
1265 x_agr_rec OUT NOCOPY agr_rec_type) IS
1266
1267 l_api_version CONSTANT NUMBER := 1;
1268 l_api_name CONSTANT VARCHAR2(30) := 'RULES_update_row';
1269 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1270 l_agr_rec agr_rec_type := p_agr_rec;
1271 l_def_agr_rec agr_rec_type;
1272 l_row_notfound BOOLEAN := TRUE;
1273 ----------------------------------
1274 -- FUNCTION populate_new_record --
1275 ----------------------------------
1276 FUNCTION populate_new_record (
1277 p_agr_rec IN agr_rec_type,
1278 x_agr_rec OUT NOCOPY agr_rec_type
1279 ) RETURN VARCHAR2 IS
1280 l_agr_rec agr_rec_type;
1281 l_row_notfound BOOLEAN := TRUE;
1282 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1283 BEGIN
1284 x_agr_rec := p_agr_rec;
1285 -- Get current database values
1286 l_agr_rec := get_rec(p_agr_rec, l_row_notfound);
1287 IF (l_row_notfound) THEN
1288 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1289 END IF;
1290 IF (x_agr_rec.id = OKC_API.G_MISS_NUM)
1291 THEN
1292 x_agr_rec.id := l_agr_rec.id;
1293 END IF;
1294 IF (x_agr_rec.ae_line_type = OKC_API.G_MISS_CHAR)
1295 THEN
1296 x_agr_rec.ae_line_type := l_agr_rec.ae_line_type;
1297 END IF;
1298 IF (x_agr_rec.org_id = OKC_API.G_MISS_NUM)
1299 THEN
1300 x_agr_rec.org_id := l_agr_rec.org_id;
1301 END IF;
1302 IF (x_agr_rec.set_of_books_id = OKC_API.G_MISS_NUM)
1303 THEN
1304 x_agr_rec.set_of_books_id := l_agr_rec.set_of_books_id;
1305 END IF;
1306 IF (x_agr_rec.object_version_number = OKC_API.G_MISS_NUM)
1307 THEN
1308 x_agr_rec.object_version_number := l_agr_rec.object_version_number;
1309 END IF;
1310 IF (x_agr_rec.created_by = OKC_API.G_MISS_NUM)
1311 THEN
1312 x_agr_rec.created_by := l_agr_rec.created_by;
1313 END IF;
1314 IF (x_agr_rec.creation_date = OKC_API.G_MISS_DATE)
1315 THEN
1316 x_agr_rec.creation_date := l_agr_rec.creation_date;
1317 END IF;
1318 IF (x_agr_rec.last_updated_by = OKC_API.G_MISS_NUM)
1319 THEN
1320 x_agr_rec.last_updated_by := l_agr_rec.last_updated_by;
1321 END IF;
1322 IF (x_agr_rec.last_update_date = OKC_API.G_MISS_DATE)
1323 THEN
1324 x_agr_rec.last_update_date := l_agr_rec.last_update_date;
1325 END IF;
1326 IF (x_agr_rec.last_update_login = OKC_API.G_MISS_NUM)
1327 THEN
1328 x_agr_rec.last_update_login := l_agr_rec.last_update_login;
1329 END IF;
1330 RETURN(l_return_status);
1331 END populate_new_record;
1332 ------------------------------------------
1333 -- Set_Attributes for:OKL_ACC_GEN_RULES --
1334 ------------------------------------------
1335 FUNCTION Set_Attributes (
1336 p_agr_rec IN agr_rec_type,
1337 x_agr_rec OUT NOCOPY agr_rec_type
1338 ) RETURN VARCHAR2 IS
1339 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1340 BEGIN
1341 x_agr_rec := p_agr_rec;
1342 RETURN(l_return_status);
1343 END Set_Attributes;
1344 BEGIN
1345 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1346 p_init_msg_list,
1347 '_PVT',
1348 x_return_status);
1349 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1350 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1352 RAISE OKC_API.G_EXCEPTION_ERROR;
1353 END IF;
1354 --- Setting item attributes
1355 l_return_status := Set_Attributes(
1356 p_agr_rec, -- IN
1357 l_agr_rec); -- OUT
1358 --- If any errors happen abort API
1359 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1360 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1361 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1362 RAISE OKC_API.G_EXCEPTION_ERROR;
1363 END IF;
1364 l_return_status := populate_new_record(l_agr_rec, l_def_agr_rec);
1365 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1366 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1367 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1368 RAISE OKC_API.G_EXCEPTION_ERROR;
1369 END IF;
1370 UPDATE OKL_ACC_GEN_RULES
1371 SET AE_LINE_TYPE = l_def_agr_rec.ae_line_type,
1372 ORG_ID = l_def_agr_rec.org_id,
1373 SET_OF_BOOKS_ID = l_def_agr_rec.set_of_books_id,
1374 OBJECT_VERSION_NUMBER = l_def_agr_rec.object_version_number,
1375 CREATED_BY = l_def_agr_rec.created_by,
1376 CREATION_DATE = l_def_agr_rec.creation_date,
1377 LAST_UPDATED_BY = l_def_agr_rec.last_updated_by,
1378 LAST_UPDATE_DATE = l_def_agr_rec.last_update_date,
1379 LAST_UPDATE_LOGIN = l_def_agr_rec.last_update_login
1380 WHERE ID = l_def_agr_rec.id;
1381
1382 x_agr_rec := l_def_agr_rec;
1383 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1384 EXCEPTION
1385 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1386 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1387 (
1388 l_api_name,
1389 G_PKG_NAME,
1390 'OKC_API.G_RET_STS_ERROR',
1391 x_msg_count,
1392 x_msg_data,
1393 '_PVT'
1394 );
1395 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1396 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1397 (
1398 l_api_name,
1399 G_PKG_NAME,
1400 'OKC_API.G_RET_STS_UNEXP_ERROR',
1401 x_msg_count,
1402 x_msg_data,
1403 '_PVT'
1404 );
1405 WHEN OTHERS THEN
1406 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1407 (
1408 l_api_name,
1409 G_PKG_NAME,
1410 'OTHERS',
1411 x_msg_count,
1412 x_msg_data,
1413 '_PVT'
1414 );
1415 END update_row;
1416 ----------------------------------------
1417 -- update_row for:OKL_ACC_GEN_RULES_V --
1418 ----------------------------------------
1419 PROCEDURE update_row(
1420 p_api_version IN NUMBER,
1421 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1422 x_return_status OUT NOCOPY VARCHAR2,
1423 x_msg_count OUT NOCOPY NUMBER,
1424 x_msg_data OUT NOCOPY VARCHAR2,
1425 p_agrv_rec IN agrv_rec_type,
1426 x_agrv_rec OUT NOCOPY agrv_rec_type) IS
1427
1428 l_api_version CONSTANT NUMBER := 1;
1429 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1430 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1431 l_agrv_rec agrv_rec_type := p_agrv_rec;
1432 l_def_agrv_rec agrv_rec_type;
1433 l_agr_rec agr_rec_type;
1434 lx_agr_rec agr_rec_type;
1435 -------------------------------
1436 -- FUNCTION fill_who_columns --
1437 -------------------------------
1438 FUNCTION fill_who_columns (
1439 p_agrv_rec IN agrv_rec_type
1440 ) RETURN agrv_rec_type IS
1441 l_agrv_rec agrv_rec_type := p_agrv_rec;
1442 BEGIN
1443 l_agrv_rec.LAST_UPDATE_DATE := SYSDATE;
1444 l_agrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1445 l_agrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1446 RETURN(l_agrv_rec);
1447 END fill_who_columns;
1448 ----------------------------------
1449 -- FUNCTION populate_new_record --
1450 ----------------------------------
1451 FUNCTION populate_new_record (
1452 p_agrv_rec IN agrv_rec_type,
1453 x_agrv_rec OUT NOCOPY agrv_rec_type
1454 ) RETURN VARCHAR2 IS
1455 l_agrv_rec agrv_rec_type;
1456 l_row_notfound BOOLEAN := TRUE;
1457 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1458 BEGIN
1459 x_agrv_rec := p_agrv_rec;
1460 -- Get current database values
1461 l_agrv_rec := get_rec(p_agrv_rec, l_row_notfound);
1462 IF (l_row_notfound) THEN
1463 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1464 END IF;
1465 IF (x_agrv_rec.id = OKC_API.G_MISS_NUM)
1466 THEN
1467 x_agrv_rec.id := l_agrv_rec.id;
1468 END IF;
1469 IF (x_agrv_rec.object_version_number = OKC_API.G_MISS_NUM)
1470 THEN
1471 x_agrv_rec.object_version_number := l_agrv_rec.object_version_number;
1472 END IF;
1473 IF (x_agrv_rec.ae_line_type = OKC_API.G_MISS_CHAR)
1474 THEN
1475 x_agrv_rec.ae_line_type := l_agrv_rec.ae_line_type;
1476 END IF;
1477 IF (x_agrv_rec.set_of_books_id = OKC_API.G_MISS_NUM)
1478 THEN
1479 x_agrv_rec.set_of_books_id := l_agrv_rec.set_of_books_id;
1480 END IF;
1481 IF (x_agrv_rec.org_id = OKC_API.G_MISS_NUM)
1482 THEN
1483 x_agrv_rec.org_id := l_agrv_rec.org_id;
1484 END IF;
1485 IF (x_agrv_rec.created_by = OKC_API.G_MISS_NUM)
1486 THEN
1487 x_agrv_rec.created_by := l_agrv_rec.created_by;
1488 END IF;
1489 IF (x_agrv_rec.creation_date = OKC_API.G_MISS_DATE)
1490 THEN
1491 x_agrv_rec.creation_date := l_agrv_rec.creation_date;
1492 END IF;
1493 IF (x_agrv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1494 THEN
1495 x_agrv_rec.last_updated_by := l_agrv_rec.last_updated_by;
1496 END IF;
1497 IF (x_agrv_rec.last_update_date = OKC_API.G_MISS_DATE)
1498 THEN
1499 x_agrv_rec.last_update_date := l_agrv_rec.last_update_date;
1500 END IF;
1501 IF (x_agrv_rec.last_update_login = OKC_API.G_MISS_NUM)
1502 THEN
1503 x_agrv_rec.last_update_login := l_agrv_rec.last_update_login;
1504 END IF;
1505 RETURN(l_return_status);
1506 END populate_new_record;
1507 --------------------------------------------
1508 -- Set_Attributes for:OKL_ACC_GEN_RULES_V --
1509 --------------------------------------------
1510 FUNCTION Set_Attributes (
1511 p_agrv_rec IN agrv_rec_type,
1512 x_agrv_rec OUT NOCOPY agrv_rec_type
1513 ) RETURN VARCHAR2 IS
1514 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1515 BEGIN
1516 x_agrv_rec := p_agrv_rec;
1517
1518 RETURN(l_return_status);
1519 END Set_Attributes;
1520 BEGIN
1521 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1522 G_PKG_NAME,
1523 p_init_msg_list,
1524 l_api_version,
1525 p_api_version,
1526 '_PVT',
1527 x_return_status);
1528 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1529 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1530 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1531 RAISE OKC_API.G_EXCEPTION_ERROR;
1532 END IF;
1533 --- Setting item attributes
1534 l_return_status := Set_Attributes(
1535 p_agrv_rec, -- IN
1536 l_agrv_rec); -- OUT
1537 --- If any errors happen abort API
1538 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1541 RAISE OKC_API.G_EXCEPTION_ERROR;
1542 END IF;
1543 l_return_status := populate_new_record(l_agrv_rec, l_def_agrv_rec);
1544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1546 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1547 RAISE OKC_API.G_EXCEPTION_ERROR;
1548 END IF;
1549 l_def_agrv_rec := fill_who_columns(l_def_agrv_rec);
1550 --- Validate all non-missing attributes (Item Level Validation)
1551 l_return_status := Validate_Attributes(l_def_agrv_rec);
1552 --- If any errors happen abort API
1553 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1555 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_ERROR;
1557 END IF;
1558 l_return_status := Validate_Record(l_def_agrv_rec);
1559 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1560 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1561 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1562 RAISE OKC_API.G_EXCEPTION_ERROR;
1563 END IF;
1564
1565 --------------------------------------
1566 -- Move VIEW record to "Child" records
1567 --------------------------------------
1568 migrate(l_def_agrv_rec, l_agr_rec);
1569 --------------------------------------------
1570 -- Call the UPDATE_ROW for each child record
1571 --------------------------------------------
1572 update_row(
1573 p_init_msg_list,
1574 x_return_status,
1575 x_msg_count,
1576 x_msg_data,
1577 l_agr_rec,
1578 lx_agr_rec
1579 );
1580 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1581 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1582 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1583 RAISE OKC_API.G_EXCEPTION_ERROR;
1584 END IF;
1585 migrate(lx_agr_rec, l_def_agrv_rec);
1586 x_agrv_rec := l_def_agrv_rec;
1587 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1588 EXCEPTION
1589 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1590 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1591 (
1592 l_api_name,
1593 G_PKG_NAME,
1594 'OKC_API.G_RET_STS_ERROR',
1595 x_msg_count,
1596 x_msg_data,
1597 '_PVT'
1598 );
1599 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1600 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1601 (
1602 l_api_name,
1603 G_PKG_NAME,
1604 'OKC_API.G_RET_STS_UNEXP_ERROR',
1605 x_msg_count,
1606 x_msg_data,
1607 '_PVT'
1608 );
1609 WHEN OTHERS THEN
1610 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1611 (
1612 l_api_name,
1613 G_PKG_NAME,
1614 'OTHERS',
1615 x_msg_count,
1616 x_msg_data,
1617 '_PVT'
1618 );
1619 END update_row;
1620 ----------------------------------------
1621 -- PL/SQL TBL update_row for:AGRV_TBL --
1622 ----------------------------------------
1623 PROCEDURE update_row(
1624 p_api_version IN NUMBER,
1625 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1626 x_return_status OUT NOCOPY VARCHAR2,
1627 x_msg_count OUT NOCOPY NUMBER,
1628 x_msg_data OUT NOCOPY VARCHAR2,
1629 p_agrv_tbl IN agrv_tbl_type,
1630 x_agrv_tbl OUT NOCOPY agrv_tbl_type) IS
1631
1632 l_api_version CONSTANT NUMBER := 1;
1633 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1634 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1635 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1636 i NUMBER := 0;
1637 BEGIN
1638 OKC_API.init_msg_list(p_init_msg_list);
1639 -- Make sure PL/SQL table has records in it before passing
1640 IF (p_agrv_tbl.COUNT > 0) THEN
1641 i := p_agrv_tbl.FIRST;
1642 LOOP
1643 update_row (
1644 p_api_version => p_api_version,
1645 p_init_msg_list => OKC_API.G_FALSE,
1646 x_return_status => x_return_status,
1647 x_msg_count => x_msg_count,
1648 x_msg_data => x_msg_data,
1649 p_agrv_rec => p_agrv_tbl(i),
1650 x_agrv_rec => x_agrv_tbl(i));
1651 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1652 IF (l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1653 l_overall_status := x_return_status;
1654 END IF;
1655 END IF;
1656 EXIT WHEN (i = p_agrv_tbl.LAST);
1657 i := p_agrv_tbl.NEXT(i);
1658 END LOOP;
1659 END IF;
1660 x_return_status := l_overall_Status;
1661
1662 EXCEPTION
1663 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1664 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1665 (
1666 l_api_name,
1667 G_PKG_NAME,
1668 'OKC_API.G_RET_STS_ERROR',
1669 x_msg_count,
1670 x_msg_data,
1671 '_PVT'
1672 );
1673 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1675 (
1676 l_api_name,
1677 G_PKG_NAME,
1678 'OKC_API.G_RET_STS_UNEXP_ERROR',
1679 x_msg_count,
1680 x_msg_data,
1681 '_PVT'
1682 );
1683 WHEN OTHERS THEN
1684 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1685 (
1686 l_api_name,
1687 G_PKG_NAME,
1688 'OTHERS',
1689 x_msg_count,
1690 x_msg_data,
1691 '_PVT'
1692 );
1693 END update_row;
1694
1695 ---------------------------------------------------------------------------
1696 -- PROCEDURE delete_row
1697 ---------------------------------------------------------------------------
1698 --------------------------------------
1699 -- delete_row for:OKL_ACC_GEN_RULES --
1700 --------------------------------------
1701 PROCEDURE delete_row(
1702 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1703 x_return_status OUT NOCOPY VARCHAR2,
1704 x_msg_count OUT NOCOPY NUMBER,
1705 x_msg_data OUT NOCOPY VARCHAR2,
1706 p_agr_rec IN agr_rec_type) IS
1707
1708 l_api_version CONSTANT NUMBER := 1;
1709 l_api_name CONSTANT VARCHAR2(30) := 'RULES_delete_row';
1710 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1711 l_agr_rec agr_rec_type:= p_agr_rec;
1712 l_row_notfound BOOLEAN := TRUE;
1713 BEGIN
1714 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1715 p_init_msg_list,
1716 '_PVT',
1717 x_return_status);
1718 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1719 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1720 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1721 RAISE OKC_API.G_EXCEPTION_ERROR;
1722 END IF;
1723 DELETE FROM OKL_ACC_GEN_RULES
1724 WHERE ID = l_agr_rec.id;
1725
1726 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1727 EXCEPTION
1728 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1729 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1730 (
1731 l_api_name,
1732 G_PKG_NAME,
1733 'OKC_API.G_RET_STS_ERROR',
1734 x_msg_count,
1735 x_msg_data,
1736 '_PVT'
1737 );
1738 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1739 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1740 (
1741 l_api_name,
1742 G_PKG_NAME,
1743 'OKC_API.G_RET_STS_UNEXP_ERROR',
1744 x_msg_count,
1745 x_msg_data,
1746 '_PVT'
1747 );
1748 WHEN OTHERS THEN
1749 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1750 (
1751 l_api_name,
1752 G_PKG_NAME,
1753 'OTHERS',
1754 x_msg_count,
1755 x_msg_data,
1756 '_PVT'
1757 );
1758 END delete_row;
1759 ----------------------------------------
1760 -- delete_row for:OKL_ACC_GEN_RULES_V --
1761 ----------------------------------------
1762 PROCEDURE delete_row(
1763 p_api_version IN NUMBER,
1764 p_init_msg_list IN VARCHAR2 DEFAULT OKC_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_agrv_rec IN agrv_rec_type) IS
1769
1770 l_api_version CONSTANT NUMBER := 1;
1771 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1772 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1773 l_agrv_rec agrv_rec_type := p_agrv_rec;
1774 l_agr_rec agr_rec_type;
1775 BEGIN
1776 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1777 G_PKG_NAME,
1778 p_init_msg_list,
1779 l_api_version,
1780 p_api_version,
1781 '_PVT',
1782 x_return_status);
1783 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1785 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1786 RAISE OKC_API.G_EXCEPTION_ERROR;
1787 END IF;
1788 --------------------------------------
1789 -- Move VIEW record to "Child" records
1790 --------------------------------------
1791 migrate(l_agrv_rec, l_agr_rec);
1792 --------------------------------------------
1793 -- Call the DELETE_ROW for each child record
1794 --------------------------------------------
1795 delete_row(
1796 p_init_msg_list,
1797 x_return_status,
1798 x_msg_count,
1799 x_msg_data,
1800 l_agr_rec
1801 );
1802 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1803 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1804 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1805 RAISE OKC_API.G_EXCEPTION_ERROR;
1806 END IF;
1807 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1808 EXCEPTION
1809 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1810 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1811 (
1812 l_api_name,
1813 G_PKG_NAME,
1814 'OKC_API.G_RET_STS_ERROR',
1815 x_msg_count,
1816 x_msg_data,
1817 '_PVT'
1818 );
1819 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1820 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1821 (
1822 l_api_name,
1823 G_PKG_NAME,
1824 'OKC_API.G_RET_STS_UNEXP_ERROR',
1825 x_msg_count,
1826 x_msg_data,
1827 '_PVT'
1828 );
1829 WHEN OTHERS THEN
1830 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1831 (
1832 l_api_name,
1833 G_PKG_NAME,
1834 'OTHERS',
1835 x_msg_count,
1836 x_msg_data,
1837 '_PVT'
1838 );
1839 END delete_row;
1840 ----------------------------------------
1841 -- PL/SQL TBL delete_row for:AGRV_TBL --
1842 ----------------------------------------
1843 PROCEDURE delete_row(
1844 p_api_version IN NUMBER,
1845 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1846 x_return_status OUT NOCOPY VARCHAR2,
1847 x_msg_count OUT NOCOPY NUMBER,
1848 x_msg_data OUT NOCOPY VARCHAR2,
1849 p_agrv_tbl IN agrv_tbl_type) IS
1850
1851 l_api_version CONSTANT NUMBER := 1;
1852 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1853 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1854 l_overall_Status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1855 i NUMBER := 0;
1856 BEGIN
1857 OKC_API.init_msg_list(p_init_msg_list);
1858 -- Make sure PL/SQL table has records in it before passing
1859 IF (p_agrv_tbl.COUNT > 0) THEN
1860 i := p_agrv_tbl.FIRST;
1861 LOOP
1862 delete_row (
1863 p_api_version => p_api_version,
1864 p_init_msg_list => OKC_API.G_FALSE,
1865 x_return_status => x_return_status,
1866 x_msg_count => x_msg_count,
1867 x_msg_data => x_msg_data,
1868 p_agrv_rec => p_agrv_tbl(i));
1869
1870 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1871 IF (l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1872 l_overall_status := x_return_status;
1873 END IF;
1874 END IF;
1875
1876 EXIT WHEN (i = p_agrv_tbl.LAST);
1877 i := p_agrv_tbl.NEXT(i);
1878 END LOOP;
1879 END IF;
1880 x_return_status := l_overall_Status;
1881
1882 EXCEPTION
1883 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1884 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1885 (
1886 l_api_name,
1887 G_PKG_NAME,
1888 'OKC_API.G_RET_STS_ERROR',
1889 x_msg_count,
1890 x_msg_data,
1891 '_PVT'
1892 );
1893 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1894 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1895 (
1896 l_api_name,
1897 G_PKG_NAME,
1898 'OKC_API.G_RET_STS_UNEXP_ERROR',
1899 x_msg_count,
1900 x_msg_data,
1901 '_PVT'
1902 );
1903 WHEN OTHERS THEN
1904 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1905 (
1906 l_api_name,
1907 G_PKG_NAME,
1908 'OTHERS',
1909 x_msg_count,
1910 x_msg_data,
1911 '_PVT'
1912 );
1913 END delete_row;
1914 END OKL_AGR_PVT;