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