[Home] [Help]
PACKAGE BODY: APPS.OKC_RLD_PVT
Source
1 PACKAGE BODY OKC_RLD_PVT as
2 /* $Header: OKCSRLDB.pls 120.0 2005/05/27 05:18:21 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
7 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
8 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
9
10 FUNCTION Validate_dff(p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type) RETURN VARCHAR2;
11
12 ---------------------------------------------------------------------------
13 -- PROCEDURE load_error_tbl
14 ---------------------------------------------------------------------------
15 PROCEDURE load_error_tbl (
16 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
17 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
18
19 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
20 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
21 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
22 BEGIN
23 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
24 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
25 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
26 -- automatically increments the index by 1, (making it 2), however, when the GET function
27 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
28 -- message 2. To circumvent this problem, check the amount of messages and compensate.
29 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
30 -- will only update the index variable when 1 and only 1 message is on the stack.
31 IF (last_msg_idx = 1) THEN
32 l_msg_idx := FND_MSG_PUB.G_FIRST;
33 END IF;
34 LOOP
35 fnd_msg_pub.get(
36 p_msg_index => l_msg_idx,
37 p_encoded => fnd_api.g_false,
38 p_data => px_error_rec.msg_data,
39 p_msg_index_out => px_error_rec.msg_count);
40 px_error_tbl(j) := px_error_rec;
41 j := j + 1;
42 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
43 END LOOP;
44 END load_error_tbl;
45 ---------------------------------------------------------------------------
46 -- FUNCTION find_highest_exception
47 ---------------------------------------------------------------------------
48 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
49 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
50 FUNCTION find_highest_exception(
51 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
52 ) RETURN VARCHAR2 IS
53 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
54 i INTEGER := 1;
55 BEGIN
56 IF (p_error_tbl.COUNT > 0) THEN
57 i := p_error_tbl.FIRST;
58 LOOP
59 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
60 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
61 l_return_status := p_error_tbl(i).error_type;
62 END IF;
63 END IF;
64 EXIT WHEN (i = p_error_tbl.LAST);
65 i := p_error_tbl.NEXT(i);
66 END LOOP;
67 END IF;
68 RETURN(l_return_status);
69 END find_highest_exception;
70 ---------------------------------------------------------------------------
71 -- FUNCTION get_seq_id
72 ---------------------------------------------------------------------------
73 FUNCTION get_seq_id RETURN NUMBER IS
74 BEGIN
75 RETURN(okc_p_util.raw_to_number(sys_guid()));
76 END get_seq_id;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE qc
80 ---------------------------------------------------------------------------
81 PROCEDURE qc IS
82 BEGIN
83 null;
84 END qc;
85
86 ---------------------------------------------------------------------------
87 -- PROCEDURE change_version
88 ---------------------------------------------------------------------------
89 PROCEDURE change_version IS
90 BEGIN
91 null;
92 END change_version;
93
94 ---------------------------------------------------------------------------
95 -- PROCEDURE api_copy
96 ---------------------------------------------------------------------------
97 PROCEDURE api_copy IS
98 BEGIN
99 null;
100 END api_copy;
101
102 ---------------------------------------------------------------------------
103 -- PROCEDURE add_language
104 ---------------------------------------------------------------------------
105 PROCEDURE add_language IS
106 BEGIN
107 DELETE FROM OKC_RULE_DEFS_TL T
108 WHERE NOT EXISTS (
109 SELECT NULL
110 FROM OKC_RULE_DEFS_TL B
111 WHERE B.RULE_CODE =T.RULE_CODE
112 );
113
114 UPDATE OKC_RULE_DEFS_TL T SET(
115 MEANING,
116 DESCRIPTION) = (SELECT
117 B.MEANING,
118 B.DESCRIPTION
119 FROM OKC_RULE_DEFS_TL B
120 WHERE B.RULE_CODE = T.RULE_CODE
121 AND B.LANGUAGE = T.SOURCE_LANG)
122 WHERE ( T.RULE_CODE,
123 T.LANGUAGE)
124 IN (SELECT
125 SUBT.RULE_CODE,
126 SUBT.LANGUAGE
127 FROM OKC_RULE_DEFS_TL SUBB, OKC_RULE_DEFS_TL SUBT
128 WHERE SUBB.RULE_CODE = SUBT.RULE_CODE
129 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
130 AND (SUBB.MEANING <> SUBT.MEANING
131 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
132 OR (SUBB.MEANING IS NOT NULL AND SUBT.MEANING IS NULL)
133 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
134 ));
135
136 INSERT INTO OKC_RULE_DEFS_TL (
137 RULE_CODE,
138 MEANING,
139 DESCRIPTION,
140 LANGUAGE,
141 SOURCE_LANG,
142 SFWT_FLAG,
143 CREATED_BY,
144 CREATION_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_DATE,
147 LAST_UPDATE_LOGIN)
148 SELECT
149 B.RULE_CODE,
150 B.MEANING,
151 B.DESCRIPTION,
152 L.LANGUAGE_CODE,
153 B.SOURCE_LANG,
154 B.SFWT_FLAG,
155 B.CREATED_BY,
156 B.CREATION_DATE,
157 B.LAST_UPDATED_BY,
158 B.LAST_UPDATE_DATE,
159 B.LAST_UPDATE_LOGIN
160 FROM OKC_RULE_DEFS_TL B, FND_LANGUAGES L
161 WHERE L.INSTALLED_FLAG IN ('I', 'B')
162 AND B.LANGUAGE = USERENV('LANG')
163 AND NOT EXISTS (
164 SELECT NULL
165 FROM OKC_RULE_DEFS_TL T
166 WHERE T.RULE_CODE = B.RULE_CODE
167 AND T.LANGUAGE = L.LANGUAGE_CODE
168 );
169 END add_language;
170
171 ---------------------------------------------------------------------------
172 -- FUNCTION get_rec for: OKC_RULE_DEFS_V
173 ---------------------------------------------------------------------------
174 FUNCTION get_rec (
175 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
176 x_no_data_found OUT NOCOPY BOOLEAN
177 ) RETURN okc_rule_defs_v_rec_type IS
178 CURSOR okc_rule_defs_pk_csr (p_rule_code IN VARCHAR2) IS
179 SELECT
180 APPLICATION_ID,
181 APPLICATION_NAME,
182 RULE_CODE,
183 DESCRIPTIVE_FLEXFIELD_NAME,
184 MEANING,
185 DESCRIPTION,
186 SFWT_FLAG,
187 OBJECT_VERSION_NUMBER,
188 CREATED_BY,
189 CREATION_DATE,
190 LAST_UPDATED_BY,
191 LAST_UPDATE_DATE,
192 LAST_UPDATE_LOGIN
193 FROM Okc_Rule_Defs_V
194 WHERE okc_rule_defs_v.rule_code = p_rule_code;
195 l_okc_rule_defs_pk okc_rule_defs_pk_csr%ROWTYPE;
196 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type;
197 BEGIN
198 x_no_data_found := TRUE;
199 -- Get current database values
200 OPEN okc_rule_defs_pk_csr (p_okc_rule_defs_v_rec.rule_code);
201 FETCH okc_rule_defs_pk_csr INTO
202 l_okc_rule_defs_v_rec.application_id,
203 l_okc_rule_defs_v_rec.application_name,
204 l_okc_rule_defs_v_rec.rule_code,
205 l_okc_rule_defs_v_rec.descriptive_flexfield_name,
206 l_okc_rule_defs_v_rec.meaning,
207 l_okc_rule_defs_v_rec.description,
208 l_okc_rule_defs_v_rec.sfwt_flag,
209 l_okc_rule_defs_v_rec.object_version_number,
210 l_okc_rule_defs_v_rec.created_by,
211 l_okc_rule_defs_v_rec.creation_date,
212 l_okc_rule_defs_v_rec.last_updated_by,
213 l_okc_rule_defs_v_rec.last_update_date,
214 l_okc_rule_defs_v_rec.last_update_login;
215 x_no_data_found := okc_rule_defs_pk_csr%NOTFOUND;
216 CLOSE okc_rule_defs_pk_csr;
217 RETURN(l_okc_rule_defs_v_rec);
218 END get_rec;
219
220 ------------------------------------------------------------------
221 -- This version of get_rec sets error messages if no data found --
222 ------------------------------------------------------------------
223 FUNCTION get_rec (
224 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
225 x_return_status OUT NOCOPY VARCHAR2
226 ) RETURN okc_rule_defs_v_rec_type IS
227 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type;
228 l_row_notfound BOOLEAN := TRUE;
229 BEGIN
230 x_return_status := OKC_API.G_RET_STS_SUCCESS;
231 l_okc_rule_defs_v_rec := get_rec(p_okc_rule_defs_v_rec, l_row_notfound);
232 IF (l_row_notfound) THEN
233 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'RULE_CODE');
234 x_return_status := OKC_API.G_RET_STS_ERROR;
235 END IF;
236 RETURN(l_okc_rule_defs_v_rec);
237 END get_rec;
238 -----------------------------------------------------------
239 -- So we don't have to pass an "l_row_notfound" variable --
240 -----------------------------------------------------------
241 FUNCTION get_rec (
242 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
243 ) RETURN okc_rule_defs_v_rec_type IS
244 l_row_not_found BOOLEAN := TRUE;
245 BEGIN
246 RETURN(get_rec(p_okc_rule_defs_v_rec, l_row_not_found));
247 END get_rec;
248 ---------------------------------------------------------------------------
249 -- FUNCTION get_rec for: OKC_RULE_DEFS_B
250 ---------------------------------------------------------------------------
251 FUNCTION get_rec (
252 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
253 x_no_data_found OUT NOCOPY BOOLEAN
254 ) RETURN okc_rule_defs_b_rec_type IS
255 CURSOR okc_rule_defs_b_pk_csr (p_rule_code IN VARCHAR2) IS
256 SELECT
257 RULE_CODE,
258 APPLICATION_ID,
259 DESCRIPTIVE_FLEXFIELD_NAME,
260 OBJECT_VERSION_NUMBER,
261 CREATED_BY,
262 CREATION_DATE,
263 LAST_UPDATED_BY,
264 LAST_UPDATE_DATE,
265 LAST_UPDATE_LOGIN
266 FROM Okc_Rule_Defs_B
267 WHERE okc_rule_defs_b.rule_code = p_rule_code;
268 l_okc_rule_defs_b_pk okc_rule_defs_b_pk_csr%ROWTYPE;
269 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
270 BEGIN
271 x_no_data_found := TRUE;
272 -- Get current database values
273 OPEN okc_rule_defs_b_pk_csr (p_okc_rule_defs_b_rec.rule_code);
274 FETCH okc_rule_defs_b_pk_csr INTO
275 l_okc_rule_defs_b_rec.rule_code,
276 l_okc_rule_defs_b_rec.application_id,
277 l_okc_rule_defs_b_rec.descriptive_flexfield_name,
278 l_okc_rule_defs_b_rec.object_version_number,
279 l_okc_rule_defs_b_rec.created_by,
280 l_okc_rule_defs_b_rec.creation_date,
281 l_okc_rule_defs_b_rec.last_updated_by,
282 l_okc_rule_defs_b_rec.last_update_date,
283 l_okc_rule_defs_b_rec.last_update_login;
284 x_no_data_found := okc_rule_defs_b_pk_csr%NOTFOUND;
285 CLOSE okc_rule_defs_b_pk_csr;
286 RETURN(l_okc_rule_defs_b_rec);
287 END get_rec;
288
289 ------------------------------------------------------------------
290 -- This version of get_rec sets error messages if no data found --
291 ------------------------------------------------------------------
292 FUNCTION get_rec (
293 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
294 x_return_status OUT NOCOPY VARCHAR2
295 ) RETURN okc_rule_defs_b_rec_type IS
296 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
297 l_row_notfound BOOLEAN := TRUE;
298 BEGIN
299 x_return_status := OKC_API.G_RET_STS_SUCCESS;
300 l_okc_rule_defs_b_rec := get_rec(p_okc_rule_defs_b_rec, l_row_notfound);
301 IF (l_row_notfound) THEN
302 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'RULE_CODE');
303 x_return_status := OKC_API.G_RET_STS_ERROR;
304 END IF;
305 RETURN(l_okc_rule_defs_b_rec);
306 END get_rec;
307 -----------------------------------------------------------
308 -- So we don't have to pass an "l_row_notfound" variable --
309 -----------------------------------------------------------
310 FUNCTION get_rec (
311 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type
312 ) RETURN okc_rule_defs_b_rec_type IS
313 l_row_not_found BOOLEAN := TRUE;
314 BEGIN
315 RETURN(get_rec(p_okc_rule_defs_b_rec, l_row_not_found));
316 END get_rec;
317 ---------------------------------------------------------------------------
318 -- FUNCTION get_rec for: OKC_RULE_DEFS_TL
319 ---------------------------------------------------------------------------
320 FUNCTION get_rec (
321 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
322 x_no_data_found OUT NOCOPY BOOLEAN
323 ) RETURN okc_rule_defs_tl_rec_type IS
324 CURSOR okc_rule_defs_tl_pk_csr (p_rule_code IN VARCHAR2,
325 p_language IN VARCHAR2) IS
326 SELECT
327 RULE_CODE,
328 MEANING,
329 DESCRIPTION,
330 LANGUAGE,
331 SOURCE_LANG,
332 SFWT_FLAG,
333 CREATED_BY,
334 CREATION_DATE,
335 LAST_UPDATED_BY,
336 LAST_UPDATE_DATE,
337 LAST_UPDATE_LOGIN
338 FROM Okc_Rule_Defs_Tl
339 WHERE okc_rule_defs_tl.rule_code = p_rule_code
340 AND okc_rule_defs_tl.language = p_language;
341 l_okc_rule_defs_tl_pk okc_rule_defs_tl_pk_csr%ROWTYPE;
342 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
343 BEGIN
344 x_no_data_found := TRUE;
345 -- Get current database values
346 OPEN okc_rule_defs_tl_pk_csr (p_okc_rule_defs_tl_rec.rule_code,
347 p_okc_rule_defs_tl_rec.language);
348 FETCH okc_rule_defs_tl_pk_csr INTO
349 l_okc_rule_defs_tl_rec.rule_code,
350 l_okc_rule_defs_tl_rec.meaning,
351 l_okc_rule_defs_tl_rec.description,
352 l_okc_rule_defs_tl_rec.language,
353 l_okc_rule_defs_tl_rec.source_lang,
354 l_okc_rule_defs_tl_rec.sfwt_flag,
355 l_okc_rule_defs_tl_rec.created_by,
356 l_okc_rule_defs_tl_rec.creation_date,
357 l_okc_rule_defs_tl_rec.last_updated_by,
358 l_okc_rule_defs_tl_rec.last_update_date,
362 RETURN(l_okc_rule_defs_tl_rec);
359 l_okc_rule_defs_tl_rec.last_update_login;
360 x_no_data_found := okc_rule_defs_tl_pk_csr%NOTFOUND;
361 CLOSE okc_rule_defs_tl_pk_csr;
363 END get_rec;
364
365 ------------------------------------------------------------------
366 -- This version of get_rec sets error messages if no data found --
367 ------------------------------------------------------------------
368 FUNCTION get_rec (
369 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
370 x_return_status OUT NOCOPY VARCHAR2
371 ) RETURN okc_rule_defs_tl_rec_type IS
372 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
373 l_row_notfound BOOLEAN := TRUE;
374 BEGIN
375 x_return_status := OKC_API.G_RET_STS_SUCCESS;
376 l_okc_rule_defs_tl_rec := get_rec(p_okc_rule_defs_tl_rec, l_row_notfound);
377 IF (l_row_notfound) THEN
378 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'RULE_CODE');
379 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
380 x_return_status := OKC_API.G_RET_STS_ERROR;
381 END IF;
382 RETURN(l_okc_rule_defs_tl_rec);
383 END get_rec;
384 -----------------------------------------------------------
385 -- So we don't have to pass an "l_row_notfound" variable --
386 -----------------------------------------------------------
387 FUNCTION get_rec (
388 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type
389 ) RETURN okc_rule_defs_tl_rec_type IS
390 l_row_not_found BOOLEAN := TRUE;
391 BEGIN
392 RETURN(get_rec(p_okc_rule_defs_tl_rec, l_row_not_found));
393 END get_rec;
394 ---------------------------------------------------------------------------
395 -- FUNCTION null_out_defaults for: OKC_RULE_DEFS_V
396 ---------------------------------------------------------------------------
397 FUNCTION null_out_defaults (
398 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
399 ) RETURN okc_rule_defs_v_rec_type IS
400 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
401 BEGIN
402 IF (l_okc_rule_defs_v_rec.application_id = OKC_API.G_MISS_NUM ) THEN
403 l_okc_rule_defs_v_rec.application_id := NULL;
404 END IF;
405 IF (l_okc_rule_defs_v_rec.application_name = OKC_API.G_MISS_CHAR ) THEN
406 l_okc_rule_defs_v_rec.application_name := NULL;
407 END IF;
408 IF (l_okc_rule_defs_v_rec.rule_code = OKC_API.G_MISS_CHAR ) THEN
409 l_okc_rule_defs_v_rec.rule_code := NULL;
410 END IF;
411 IF (l_okc_rule_defs_v_rec.descriptive_flexfield_name = OKC_API.G_MISS_CHAR ) THEN
412 l_okc_rule_defs_v_rec.descriptive_flexfield_name := NULL;
413 END IF;
414 IF (l_okc_rule_defs_v_rec.meaning = OKC_API.G_MISS_CHAR ) THEN
415 l_okc_rule_defs_v_rec.meaning := NULL;
416 END IF;
417 IF (l_okc_rule_defs_v_rec.description = OKC_API.G_MISS_CHAR ) THEN
418 l_okc_rule_defs_v_rec.description := NULL;
419 END IF;
420 IF (l_okc_rule_defs_v_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
421 l_okc_rule_defs_v_rec.sfwt_flag := NULL;
422 END IF;
423 IF (l_okc_rule_defs_v_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
424 l_okc_rule_defs_v_rec.object_version_number := NULL;
425 END IF;
426 IF (l_okc_rule_defs_v_rec.created_by = OKC_API.G_MISS_NUM ) THEN
427 l_okc_rule_defs_v_rec.created_by := NULL;
428 END IF;
429 IF (l_okc_rule_defs_v_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
430 l_okc_rule_defs_v_rec.creation_date := NULL;
431 END IF;
432 IF (l_okc_rule_defs_v_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
433 l_okc_rule_defs_v_rec.last_updated_by := NULL;
434 END IF;
435 IF (l_okc_rule_defs_v_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
436 l_okc_rule_defs_v_rec.last_update_date := NULL;
437 END IF;
438 IF (l_okc_rule_defs_v_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
439 l_okc_rule_defs_v_rec.last_update_login := NULL;
440 END IF;
441 RETURN(l_okc_rule_defs_v_rec);
442 END null_out_defaults;
443 ---------------------------------------------
444 -- Validate_Attributes for: APPLICATION_ID --
445 ---------------------------------------------
446 PROCEDURE validate_application_id(
447 x_return_status OUT NOCOPY VARCHAR2,
448 p_application_id IN NUMBER) IS
449 l_applid_var VARCHAR2(1) := '?';
450 Cursor l_applid_csr Is
451 select '!'
452 from FND_APPLICATION
453 where application_id = p_application_id;
454 BEGIN
455 x_return_status := OKC_API.G_RET_STS_SUCCESS;
456 Open l_applid_csr;
457 Fetch l_applid_csr Into l_applid_var;
458 Close l_applid_csr;
459 IF (p_application_id = OKC_API.G_MISS_NUM OR
460 p_application_id IS NULL)
461 THEN
462 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'application_id');
463 x_return_status := OKC_API.G_RET_STS_ERROR;
464 RAISE G_EXCEPTION_HALT_VALIDATION;
465 ELSIF (l_applid_var = '?') THEN
466 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'APPLICATION_ID');
467 x_return_status := OKC_API.G_RET_STS_ERROR;
468 RAISE G_EXCEPTION_HALT_VALIDATION;
469 END IF;
470 EXCEPTION
471 WHEN G_EXCEPTION_HALT_VALIDATION THEN
472 null;
476 ,p_token1 => G_SQLCODE_TOKEN
473 WHEN OTHERS THEN
474 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
475 ,p_msg_name => G_UNEXPECTED_ERROR
477 ,p_token1_value => SQLCODE
478 ,p_token2 => G_SQLERRM_TOKEN
479 ,p_token2_value => SQLERRM);
480 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
481 END validate_application_id;
482 -----------------------------------------------
483 -- Validate_Attributes for: APPLICATION_NAME --
484 -----------------------------------------------
485 PROCEDURE validate_application_name(
486 x_return_status OUT NOCOPY VARCHAR2,
487 p_application_name IN VARCHAR2) IS
488 /*
489 l_applnm_var VARCHAR2(1) := '?';
490 Cursor l_applnm_csr Is
491 select '!'
492 from FND_APPLICATION
493 where application_short_name = p_application_name;
494 */
495 BEGIN
496 x_return_status := OKC_API.G_RET_STS_SUCCESS;
497 /*
498 Open l_applnm_csr;
499 Fetch l_applnm_csr Into l_applnm_var;
500 Close l_applnm_csr;
501 */
502 IF (p_application_name = OKC_API.G_MISS_CHAR OR
503 p_application_name IS NULL)
504 THEN
505 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'application_name');
506 x_return_status := OKC_API.G_RET_STS_ERROR;
507 RAISE G_EXCEPTION_HALT_VALIDATION;
508 /*
509 ELSIF (l_applnm_var = '?') THEN
510 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'APPLICATION_NAME');
511 x_return_status := OKC_API.G_RET_STS_ERROR;
512 RAISE G_EXCEPTION_HALT_VALIDATION;
513 */
514 END IF;
515 EXCEPTION
516 WHEN G_EXCEPTION_HALT_VALIDATION THEN
517 null;
518 WHEN OTHERS THEN
519 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
520 ,p_msg_name => G_UNEXPECTED_ERROR
521 ,p_token1 => G_SQLCODE_TOKEN
522 ,p_token1_value => SQLCODE
523 ,p_token2 => G_SQLERRM_TOKEN
524 ,p_token2_value => SQLERRM);
525 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
526 END validate_application_name;
527 ----------------------------------------
528 -- Validate_Attributes for: RULE_CODE --
529 ----------------------------------------
530 PROCEDURE validate_rule_code(
531 x_return_status OUT NOCOPY VARCHAR2,
532 p_rule_code IN VARCHAR2) IS
533 BEGIN
534 x_return_status := OKC_API.G_RET_STS_SUCCESS;
535 IF (p_rule_code = OKC_API.G_MISS_CHAR OR
536 p_rule_code IS NULL)
537 THEN
538 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'rule_code');
539 x_return_status := OKC_API.G_RET_STS_ERROR;
540 RAISE G_EXCEPTION_HALT_VALIDATION;
541 END IF;
542 EXCEPTION
543 WHEN G_EXCEPTION_HALT_VALIDATION THEN
544 null;
545 WHEN OTHERS THEN
546 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
547 ,p_msg_name => G_UNEXPECTED_ERROR
548 ,p_token1 => G_SQLCODE_TOKEN
549 ,p_token1_value => SQLCODE
550 ,p_token2 => G_SQLERRM_TOKEN
551 ,p_token2_value => SQLERRM);
552 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
553 END validate_rule_code;
554 ---------------------------------------------------------
555 -- Validate_Attributes for: DESCRIPTIVE_FLEXFIELD_NAME --
556 ---------------------------------------------------------
557 PROCEDURE validate_dff_name(
558 x_return_status OUT NOCOPY VARCHAR2,
559 p_descriptive_flexfield_name IN VARCHAR2) IS
560 l_dff_var VARCHAR2(1) := '?';
561 Cursor l_dff_csr Is
562 select distinct '!'
563 from FND_DESCR_FLEX_CONTEXTS_VL
564 where descriptive_flexfield_name = p_descriptive_flexfield_name;
565 BEGIN
566 x_return_status := OKC_API.G_RET_STS_SUCCESS;
567 Open l_dff_csr;
568 Fetch l_dff_csr Into l_dff_var;
569 Close l_dff_csr;
570 IF (p_descriptive_flexfield_name = OKC_API.G_MISS_CHAR OR
571 p_descriptive_flexfield_name IS NULL)
572 THEN
573 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'descriptive_flexfield_name');
574 x_return_status := OKC_API.G_RET_STS_ERROR;
575 RAISE G_EXCEPTION_HALT_VALIDATION;
576 ELSIF (l_dff_var = '?') THEN
577 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'DESCRIPTIVE_FLEXFIELD_NAME');
578 x_return_status := OKC_API.G_RET_STS_ERROR;
579 RAISE G_EXCEPTION_HALT_VALIDATION;
580 END IF;
581 EXCEPTION
582 WHEN G_EXCEPTION_HALT_VALIDATION THEN
583 null;
584 WHEN OTHERS THEN
585 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
586 ,p_msg_name => G_UNEXPECTED_ERROR
587 ,p_token1 => G_SQLCODE_TOKEN
588 ,p_token1_value => SQLCODE
589 ,p_token2 => G_SQLERRM_TOKEN
590 ,p_token2_value => SQLERRM);
591 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
592 END validate_dff_name;
596 PROCEDURE validate_meaning(
593 --------------------------------------
594 -- Validate_Attributes for: MEANING --
595 --------------------------------------
597 x_return_status OUT NOCOPY VARCHAR2,
598 p_meaning IN VARCHAR2) IS
599 BEGIN
600 x_return_status := OKC_API.G_RET_STS_SUCCESS;
601 IF (p_meaning = OKC_API.G_MISS_CHAR OR
602 p_meaning IS NULL)
603 THEN
604 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'meaning');
605 x_return_status := OKC_API.G_RET_STS_ERROR;
606 RAISE G_EXCEPTION_HALT_VALIDATION;
607 END IF;
608 EXCEPTION
609 WHEN G_EXCEPTION_HALT_VALIDATION THEN
610 null;
611 WHEN OTHERS THEN
612 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
613 ,p_msg_name => G_UNEXPECTED_ERROR
614 ,p_token1 => G_SQLCODE_TOKEN
615 ,p_token1_value => SQLCODE
616 ,p_token2 => G_SQLERRM_TOKEN
617 ,p_token2_value => SQLERRM);
618 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
619 END validate_meaning;
620 ----------------------------------------
621 -- Validate_Attributes for: SFWT_FLAG --
622 ----------------------------------------
623 PROCEDURE validate_sfwt_flag(
624 x_return_status OUT NOCOPY VARCHAR2,
625 p_sfwt_flag IN VARCHAR2) IS
626 BEGIN
627 x_return_status := OKC_API.G_RET_STS_SUCCESS;
628 IF (p_sfwt_flag = OKC_API.G_MISS_CHAR OR
629 p_sfwt_flag IS NULL)
630 THEN
631 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
632 x_return_status := OKC_API.G_RET_STS_ERROR;
633 RAISE G_EXCEPTION_HALT_VALIDATION;
634 END IF;
635 EXCEPTION
636 WHEN G_EXCEPTION_HALT_VALIDATION THEN
637 null;
638 WHEN OTHERS THEN
639 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
640 ,p_msg_name => G_UNEXPECTED_ERROR
641 ,p_token1 => G_SQLCODE_TOKEN
642 ,p_token1_value => SQLCODE
643 ,p_token2 => G_SQLERRM_TOKEN
644 ,p_token2_value => SQLERRM);
645 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
646 END validate_sfwt_flag;
647 ----------------------------------------------------
648 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
649 ----------------------------------------------------
650 PROCEDURE validate_object_version_number(
651 x_return_status OUT NOCOPY VARCHAR2,
652 p_object_version_number IN NUMBER) IS
653 BEGIN
654 x_return_status := OKC_API.G_RET_STS_SUCCESS;
655 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
656 p_object_version_number IS NULL)
657 THEN
658 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
659 x_return_status := OKC_API.G_RET_STS_ERROR;
660 RAISE G_EXCEPTION_HALT_VALIDATION;
661 END IF;
662 EXCEPTION
663 WHEN G_EXCEPTION_HALT_VALIDATION THEN
664 null;
665 WHEN OTHERS THEN
666 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
667 ,p_msg_name => G_UNEXPECTED_ERROR
668 ,p_token1 => G_SQLCODE_TOKEN
669 ,p_token1_value => SQLCODE
670 ,p_token2 => G_SQLERRM_TOKEN
671 ,p_token2_value => SQLERRM);
672 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
673 END validate_object_version_number;
674 ----------------------------------------------------------------------------------
675 -- Validate_Attributes for: APPLICATION_ID,RULE_CODE,DESCRIPTIVE_FLEXFIELD_NAME --
676 ----------------------------------------------------------------------------------
677 PROCEDURE validate_appl_rule_dff(
678 x_return_status OUT NOCOPY VARCHAR2,
679 p_application_id IN NUMBER,
680 p_rule_code IN VARCHAR2,
681 p_descriptive_flexfield_name IN VARCHAR2) IS
682 l_var VARCHAR2(1) := '?';
683 Cursor l_csr Is
684 select distinct '!'
685 from FND_DESCR_FLEX_CONTEXTS_VL
686 where
687 APPLICATION_ID = p_application_id
688 and
689 DESCRIPTIVE_FLEX_CONTEXT_CODE = p_rule_code
690 and
691 DESCRIPTIVE_FLEXFIELD_NAME = p_descriptive_flexfield_name;
692 BEGIN
693 x_return_status := OKC_API.G_RET_STS_SUCCESS;
694 Open l_csr;
695 Fetch l_csr Into l_var;
696 Close l_csr;
697 IF (l_var = '?') THEN
698 OKC_API.set_message( G_APP_NAME,
699 G_INVALID_VALUE,
700 G_COL_NAME_TOKEN,
701 'APPLICATION_ID,RULE_CODE,DESCRIPTIVE_FLEXFIELD_NAME');
702 x_return_status := OKC_API.G_RET_STS_ERROR;
703 RAISE G_EXCEPTION_HALT_VALIDATION;
704 END IF;
705 EXCEPTION
706 WHEN G_EXCEPTION_HALT_VALIDATION THEN
707 null;
708 WHEN OTHERS THEN
709 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
710 ,p_msg_name => G_UNEXPECTED_ERROR
711 ,p_token1 => G_SQLCODE_TOKEN
712 ,p_token1_value => SQLCODE
716 END validate_appl_rule_dff;
713 ,p_token2 => G_SQLERRM_TOKEN
714 ,p_token2_value => SQLERRM);
715 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
717 ---------------------------------------------------------------------------
718 -- FUNCTION Validate_Attributes
719 ---------------------------------------------------------------------------
720 ---------------------------------------------
721 -- Validate_Attributes for:OKC_RULE_DEFS_V --
722 ---------------------------------------------
723 FUNCTION Validate_Attributes (
724 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
725 ) RETURN VARCHAR2 IS
726 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
727 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
728 BEGIN
729
730 -----------------------------
731 -- Column Level Validation --
732 -----------------------------
733 -- ***
734 -- application_id
735 -- ***
736 validate_application_id(x_return_status, p_okc_rule_defs_v_rec.application_id);
737 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
738 l_return_status := x_return_status;
739 RAISE G_EXCEPTION_HALT_VALIDATION;
740 END IF;
741
742 -- ***
743 -- application_name
744 -- ***
745 validate_application_name(x_return_status, p_okc_rule_defs_v_rec.application_name);
746 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
747 l_return_status := x_return_status;
748 RAISE G_EXCEPTION_HALT_VALIDATION;
749 END IF;
750
751 -- ***
752 -- rule_code
753 -- ***
754 validate_rule_code(x_return_status, p_okc_rule_defs_v_rec.rule_code);
755 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
756 l_return_status := x_return_status;
757 RAISE G_EXCEPTION_HALT_VALIDATION;
758 END IF;
759
760 -- ***
761 -- descriptive_flexfield_name
762 -- ***
763 validate_dff_name(x_return_status, p_okc_rule_defs_v_rec.descriptive_flexfield_name);
764 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
765 l_return_status := x_return_status;
766 RAISE G_EXCEPTION_HALT_VALIDATION;
767 END IF;
768
769 -- ***
770 -- application_id, rule_code, descriptive_flexfield_name
771 -- ***
772 validate_appl_rule_dff(x_return_status,
773 p_okc_rule_defs_v_rec.application_id,
774 p_okc_rule_defs_v_rec.rule_code,
775 p_okc_rule_defs_v_rec.descriptive_flexfield_name);
776 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
777 l_return_status := x_return_status;
778 RAISE G_EXCEPTION_HALT_VALIDATION;
779 END IF;
780
781 -- ***
782 -- meaning
783 -- ***
784 validate_meaning(x_return_status, p_okc_rule_defs_v_rec.meaning);
785 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
786 l_return_status := x_return_status;
787 RAISE G_EXCEPTION_HALT_VALIDATION;
788 END IF;
789
790 -- ***
791 -- sfwt_flag
792 -- ***
793 validate_sfwt_flag(x_return_status, p_okc_rule_defs_v_rec.sfwt_flag);
794 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
795 l_return_status := x_return_status;
796 RAISE G_EXCEPTION_HALT_VALIDATION;
797 END IF;
798
799 -- ***
800 -- object_version_number
801 -- ***
802 validate_object_version_number(x_return_status, p_okc_rule_defs_v_rec.object_version_number);
803 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
804 l_return_status := x_return_status;
805 RAISE G_EXCEPTION_HALT_VALIDATION;
806 END IF;
807
808 RETURN(l_return_status);
809 EXCEPTION
810 WHEN G_EXCEPTION_HALT_VALIDATION THEN
811 RETURN(l_return_status);
812 WHEN OTHERS THEN
813 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
814 ,p_msg_name => G_UNEXPECTED_ERROR
815 ,p_token1 => G_SQLCODE_TOKEN
816 ,p_token1_value => SQLCODE
817 ,p_token2 => G_SQLERRM_TOKEN
818 ,p_token2_value => SQLERRM);
819 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
820 RETURN(l_return_status);
821 END Validate_Attributes;
822 ---------------------------------------------------------------------------
823 -- PROCEDURE Validate_Record
824 ---------------------------------------------------------------------------
825 -----------------------------------------
826 -- Validate Record for:OKC_RULE_DEFS_V --
827 -----------------------------------------
828 FUNCTION Validate_Record (
829 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
830 p_db_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
831 ) RETURN VARCHAR2 IS
832 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
833 BEGIN
834 RETURN (l_return_status);
835 END Validate_Record;
836
837 FUNCTION Validate_Record (
838 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
839 ) RETURN VARCHAR2 IS
840 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
841 l_db_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := get_rec(p_okc_rule_defs_v_rec);
845 RETURN (l_return_status);
842 BEGIN
843 l_return_status := Validate_Record(p_okc_rule_defs_v_rec => p_okc_rule_defs_v_rec,
844 p_db_okc_rule_defs_v_rec => l_db_okc_rule_defs_v_rec);
846 END Validate_Record;
847
848 ---------------------------------------------------------------------------
849 -- PROCEDURE Migrate
850 ---------------------------------------------------------------------------
851 PROCEDURE migrate (
852 p_from IN okc_rule_defs_v_rec_type,
853 p_to IN OUT NOCOPY okc_rule_defs_b_rec_type
854 ) IS
855 BEGIN
856 p_to.rule_code := p_from.rule_code;
857 p_to.application_id := p_from.application_id;
858 p_to.descriptive_flexfield_name := p_from.descriptive_flexfield_name;
859 p_to.object_version_number := p_from.object_version_number;
860 p_to.created_by := p_from.created_by;
861 p_to.creation_date := p_from.creation_date;
862 p_to.last_updated_by := p_from.last_updated_by;
863 p_to.last_update_date := p_from.last_update_date;
864 p_to.last_update_login := p_from.last_update_login;
865 END migrate;
866 PROCEDURE migrate (
867 p_from IN okc_rule_defs_b_rec_type,
868 p_to IN OUT NOCOPY okc_rule_defs_v_rec_type
869 ) IS
870 BEGIN
871 p_to.application_id := p_from.application_id;
872 p_to.rule_code := p_from.rule_code;
873 p_to.descriptive_flexfield_name := p_from.descriptive_flexfield_name;
874 p_to.object_version_number := p_from.object_version_number;
875 p_to.created_by := p_from.created_by;
876 p_to.creation_date := p_from.creation_date;
877 p_to.last_updated_by := p_from.last_updated_by;
878 p_to.last_update_date := p_from.last_update_date;
879 p_to.last_update_login := p_from.last_update_login;
880 END migrate;
881 PROCEDURE migrate (
882 p_from IN okc_rule_defs_v_rec_type,
883 p_to IN OUT NOCOPY okc_rule_defs_tl_rec_type
884 ) IS
885 BEGIN
886 p_to.rule_code := p_from.rule_code;
887 p_to.meaning := p_from.meaning;
888 p_to.description := p_from.description;
889 p_to.sfwt_flag := p_from.sfwt_flag;
890 p_to.created_by := p_from.created_by;
891 p_to.creation_date := p_from.creation_date;
892 p_to.last_updated_by := p_from.last_updated_by;
893 p_to.last_update_date := p_from.last_update_date;
894 p_to.last_update_login := p_from.last_update_login;
895 END migrate;
896 PROCEDURE migrate (
897 p_from IN okc_rule_defs_tl_rec_type,
898 p_to IN OUT NOCOPY okc_rule_defs_v_rec_type
899 ) IS
900 BEGIN
901 p_to.rule_code := p_from.rule_code;
902 p_to.meaning := p_from.meaning;
903 p_to.description := p_from.description;
904 p_to.sfwt_flag := p_from.sfwt_flag;
905 p_to.created_by := p_from.created_by;
906 p_to.creation_date := p_from.creation_date;
907 p_to.last_updated_by := p_from.last_updated_by;
908 p_to.last_update_date := p_from.last_update_date;
909 p_to.last_update_login := p_from.last_update_login;
910 END migrate;
911 ---------------------------------------------------------------------------
912 -- PROCEDURE validate_row
913 ---------------------------------------------------------------------------
914 --------------------------------------
915 -- validate_row for:OKC_RULE_DEFS_V --
916 --------------------------------------
917 PROCEDURE validate_row(
918 p_api_version IN NUMBER,
919 p_init_msg_list IN VARCHAR2,
920 x_return_status OUT NOCOPY VARCHAR2,
921 x_msg_count OUT NOCOPY NUMBER,
922 x_msg_data OUT NOCOPY VARCHAR2,
923 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type) IS
924
925 l_api_version CONSTANT NUMBER := 1;
926 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
927 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
928 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
929 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
930 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
931 BEGIN
932 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
933 G_PKG_NAME,
934 p_init_msg_list,
935 l_api_version,
936 p_api_version,
937 '_PVT',
938 x_return_status);
939 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
940 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
941 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
942 RAISE OKC_API.G_EXCEPTION_ERROR;
943 END IF;
944 --- Validate all non-missing attributes (Item Level Validation)
945 l_return_status := Validate_Attributes(l_okc_rule_defs_v_rec);
946 --- If any errors happen abort API
947 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
948 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
949 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
950 RAISE OKC_API.G_EXCEPTION_ERROR;
951 END IF;
952 l_return_status := Validate_Record(l_okc_rule_defs_v_rec);
953 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
954 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
958 x_return_status := l_return_status;
955 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
956 RAISE OKC_API.G_EXCEPTION_ERROR;
957 END IF;
959 EXCEPTION
960 WHEN OKC_API.G_EXCEPTION_ERROR THEN
961 x_return_status := OKC_API.HANDLE_EXCEPTIONS
962 (
963 l_api_name,
964 G_PKG_NAME,
965 'OKC_API.G_RET_STS_ERROR',
966 x_msg_count,
967 x_msg_data,
968 '_PVT'
969 );
970 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
971 x_return_status := OKC_API.HANDLE_EXCEPTIONS
972 (
973 l_api_name,
974 G_PKG_NAME,
975 'OKC_API.G_RET_STS_UNEXP_ERROR',
976 x_msg_count,
977 x_msg_data,
978 '_PVT'
979 );
980 WHEN OTHERS THEN
981 x_return_status := OKC_API.HANDLE_EXCEPTIONS
982 (
983 l_api_name,
984 G_PKG_NAME,
985 'OTHERS',
986 x_msg_count,
987 x_msg_data,
988 '_PVT'
989 );
990 END validate_row;
991 -------------------------------------------------
992 -- PL/SQL TBL validate_row for:OKC_RULE_DEFS_V --
993 -------------------------------------------------
994 PROCEDURE validate_row(
995 p_api_version IN NUMBER,
996 p_init_msg_list IN VARCHAR2,
997 x_return_status OUT NOCOPY VARCHAR2,
998 x_msg_count OUT NOCOPY NUMBER,
999 x_msg_data OUT NOCOPY VARCHAR2,
1000 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
1001 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1002
1003 l_api_version CONSTANT NUMBER := 1;
1004 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1005 i NUMBER := 0;
1006 BEGIN
1007 OKC_API.init_msg_list(p_init_msg_list);
1008 -- Make sure PL/SQL table has records in it before passing
1009 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
1010 i := p_okc_rule_defs_v_tbl.FIRST;
1011 LOOP
1012 DECLARE
1013 l_error_rec OKC_API.ERROR_REC_TYPE;
1014 BEGIN
1015 l_error_rec.api_name := l_api_name;
1016 l_error_rec.api_package := G_PKG_NAME;
1017 l_error_rec.idx := i;
1018 validate_row (
1019 p_api_version => p_api_version,
1020 p_init_msg_list => OKC_API.G_FALSE,
1021 x_return_status => l_error_rec.error_type,
1022 x_msg_count => l_error_rec.msg_count,
1023 x_msg_data => l_error_rec.msg_data,
1024 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_tbl(i));
1025 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1026 l_error_rec.sqlcode := SQLCODE;
1027 load_error_tbl(l_error_rec, px_error_tbl);
1028 ELSE
1029 x_msg_count := l_error_rec.msg_count;
1030 x_msg_data := l_error_rec.msg_data;
1031 END IF;
1032 EXCEPTION
1033 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1034 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1035 l_error_rec.sqlcode := SQLCODE;
1036 load_error_tbl(l_error_rec, px_error_tbl);
1037 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1038 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1039 l_error_rec.sqlcode := SQLCODE;
1040 load_error_tbl(l_error_rec, px_error_tbl);
1041 WHEN OTHERS THEN
1042 l_error_rec.error_type := 'OTHERS';
1043 l_error_rec.sqlcode := SQLCODE;
1044 load_error_tbl(l_error_rec, px_error_tbl);
1045 END;
1046 EXIT WHEN (i = p_okc_rule_defs_v_tbl.LAST);
1047 i := p_okc_rule_defs_v_tbl.NEXT(i);
1048 END LOOP;
1049 END IF;
1050 -- Loop through the error_tbl to find the error with the highest severity
1051 -- and return it.
1052 x_return_status := find_highest_exception(px_error_tbl);
1053 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1054 EXCEPTION
1055 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1056 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1057 (
1058 l_api_name,
1059 G_PKG_NAME,
1060 'OKC_API.G_RET_STS_ERROR',
1061 x_msg_count,
1062 x_msg_data,
1063 '_PVT'
1064 );
1065 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1066 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1067 (
1068 l_api_name,
1069 G_PKG_NAME,
1070 'OKC_API.G_RET_STS_UNEXP_ERROR',
1071 x_msg_count,
1072 x_msg_data,
1073 '_PVT'
1074 );
1075 WHEN OTHERS THEN
1076 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1077 (
1078 l_api_name,
1079 G_PKG_NAME,
1080 'OTHERS',
1081 x_msg_count,
1082 x_msg_data,
1083 '_PVT'
1084 );
1085 END validate_row;
1086
1087 -------------------------------------------------
1088 -- PL/SQL TBL validate_row for:OKC_RULE_DEFS_V --
1089 -------------------------------------------------
1090 PROCEDURE validate_row(
1091 p_api_version IN NUMBER,
1095 x_msg_data OUT NOCOPY VARCHAR2,
1092 p_init_msg_list IN VARCHAR2,
1093 x_return_status OUT NOCOPY VARCHAR2,
1094 x_msg_count OUT NOCOPY NUMBER,
1096 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type) IS
1097
1098 l_api_version CONSTANT NUMBER := 1;
1099 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1100 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1101 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1102 BEGIN
1103 OKC_API.init_msg_list(p_init_msg_list);
1104 -- Make sure PL/SQL table has records in it before passing
1105 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
1106 validate_row (
1107 p_api_version => p_api_version,
1108 p_init_msg_list => OKC_API.G_FALSE,
1109 x_return_status => x_return_status,
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data,
1112 p_okc_rule_defs_v_tbl => p_okc_rule_defs_v_tbl,
1113 px_error_tbl => l_error_tbl);
1114 END IF;
1115 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1116 EXCEPTION
1117 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1118 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1119 (
1120 l_api_name,
1121 G_PKG_NAME,
1122 'OKC_API.G_RET_STS_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_PVT'
1126 );
1127 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1128 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1129 (
1130 l_api_name,
1131 G_PKG_NAME,
1132 'OKC_API.G_RET_STS_UNEXP_ERROR',
1133 x_msg_count,
1134 x_msg_data,
1135 '_PVT'
1136 );
1137 WHEN OTHERS THEN
1138 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1139 (
1140 l_api_name,
1141 G_PKG_NAME,
1142 'OTHERS',
1143 x_msg_count,
1144 x_msg_data,
1145 '_PVT'
1146 );
1147 END validate_row;
1148
1149 ---------------------------------------------------------------------------
1150 -- PROCEDURE insert_row
1151 ---------------------------------------------------------------------------
1152 ------------------------------------
1153 -- insert_row for:OKC_RULE_DEFS_B --
1154 ------------------------------------
1155 PROCEDURE insert_row(
1156 p_init_msg_list IN VARCHAR2,
1157 x_return_status OUT NOCOPY VARCHAR2,
1158 x_msg_count OUT NOCOPY NUMBER,
1159 x_msg_data OUT NOCOPY VARCHAR2,
1160 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
1161 x_okc_rule_defs_b_rec OUT NOCOPY okc_rule_defs_b_rec_type) IS
1162
1163 l_api_version CONSTANT NUMBER := 1;
1164 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1165 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1166 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type := p_okc_rule_defs_b_rec;
1167 l_def_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
1168 ----------------------------------------
1169 -- Set_Attributes for:OKC_RULE_DEFS_B --
1170 ----------------------------------------
1171 FUNCTION Set_Attributes (
1172 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
1173 x_okc_rule_defs_b_rec OUT NOCOPY okc_rule_defs_b_rec_type
1174 ) RETURN VARCHAR2 IS
1175 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1176 BEGIN
1177 x_okc_rule_defs_b_rec := p_okc_rule_defs_b_rec;
1178 RETURN(l_return_status);
1179 END Set_Attributes;
1180 BEGIN
1181 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1182 p_init_msg_list,
1183 '_PVT',
1184 x_return_status);
1185 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1188 RAISE OKC_API.G_EXCEPTION_ERROR;
1189 END IF;
1190 --- Setting item atributes
1191 l_return_status := Set_Attributes(
1192 p_okc_rule_defs_b_rec, -- IN
1193 l_okc_rule_defs_b_rec); -- OUT
1194 --- If any errors happen abort API
1195 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1196 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1198 RAISE OKC_API.G_EXCEPTION_ERROR;
1199 END IF;
1200 INSERT INTO OKC_RULE_DEFS_B(
1201 rule_code,
1202 application_id,
1203 descriptive_flexfield_name,
1204 object_version_number,
1205 created_by,
1206 creation_date,
1207 last_updated_by,
1208 last_update_date,
1209 last_update_login)
1210 VALUES (
1211 l_okc_rule_defs_b_rec.rule_code,
1212 l_okc_rule_defs_b_rec.application_id,
1213 l_okc_rule_defs_b_rec.descriptive_flexfield_name,
1214 l_okc_rule_defs_b_rec.object_version_number,
1215 l_okc_rule_defs_b_rec.created_by,
1219 l_okc_rule_defs_b_rec.last_update_login);
1216 l_okc_rule_defs_b_rec.creation_date,
1217 l_okc_rule_defs_b_rec.last_updated_by,
1218 l_okc_rule_defs_b_rec.last_update_date,
1220 -- Set OUT values
1221 x_okc_rule_defs_b_rec := l_okc_rule_defs_b_rec;
1222 x_return_status := l_return_status;
1223 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1224 EXCEPTION
1225 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1226 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1227 (
1228 l_api_name,
1229 G_PKG_NAME,
1230 'OKC_API.G_RET_STS_ERROR',
1231 x_msg_count,
1232 x_msg_data,
1233 '_PVT'
1234 );
1235 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1236 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1237 (
1238 l_api_name,
1239 G_PKG_NAME,
1240 'OKC_API.G_RET_STS_UNEXP_ERROR',
1241 x_msg_count,
1242 x_msg_data,
1243 '_PVT'
1244 );
1245 WHEN OTHERS THEN
1246 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1247 (
1248 l_api_name,
1249 G_PKG_NAME,
1250 'OTHERS',
1251 x_msg_count,
1252 x_msg_data,
1253 '_PVT'
1254 );
1255 END insert_row;
1256 -------------------------------------
1257 -- insert_row for:OKC_RULE_DEFS_TL --
1258 -------------------------------------
1259 PROCEDURE insert_row(
1260 p_init_msg_list IN VARCHAR2,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2,
1264 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
1265 x_okc_rule_defs_tl_rec OUT NOCOPY okc_rule_defs_tl_rec_type) IS
1266
1267 l_api_version CONSTANT NUMBER := 1;
1268 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1269 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1270 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type := p_okc_rule_defs_tl_rec;
1271 l_def_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
1272 CURSOR get_languages IS
1273 SELECT *
1274 FROM FND_LANGUAGES
1275 WHERE INSTALLED_FLAG IN ('I', 'B');
1276 -----------------------------------------
1277 -- Set_Attributes for:OKC_RULE_DEFS_TL --
1278 -----------------------------------------
1279 FUNCTION Set_Attributes (
1280 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
1281 x_okc_rule_defs_tl_rec OUT NOCOPY okc_rule_defs_tl_rec_type
1282 ) RETURN VARCHAR2 IS
1283 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1284 BEGIN
1285 x_okc_rule_defs_tl_rec := p_okc_rule_defs_tl_rec;
1286 x_okc_rule_defs_tl_rec.LANGUAGE := USERENV('LANG');
1287 x_okc_rule_defs_tl_rec.SOURCE_LANG := USERENV('LANG');
1288 RETURN(l_return_status);
1289 END Set_Attributes;
1290 BEGIN
1291 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1292 p_init_msg_list,
1293 '_PVT',
1294 x_return_status);
1295 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1296 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1297 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1298 RAISE OKC_API.G_EXCEPTION_ERROR;
1299 END IF;
1300 --- Setting item attributes
1301 l_return_status := Set_Attributes(
1302 p_okc_rule_defs_tl_rec, -- IN
1303 l_okc_rule_defs_tl_rec); -- OUT
1304 --- If any errors happen abort API
1305 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1306 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1307 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1308 RAISE OKC_API.G_EXCEPTION_ERROR;
1309 END IF;
1310 FOR l_lang_rec IN get_languages LOOP
1311 l_okc_rule_defs_tl_rec.language := l_lang_rec.language_code;
1312 INSERT INTO OKC_RULE_DEFS_TL(
1313 rule_code,
1314 meaning,
1315 description,
1316 language,
1317 source_lang,
1318 sfwt_flag,
1319 created_by,
1320 creation_date,
1321 last_updated_by,
1322 last_update_date,
1323 last_update_login)
1324 VALUES (
1325 l_okc_rule_defs_tl_rec.rule_code,
1326 l_okc_rule_defs_tl_rec.meaning,
1327 l_okc_rule_defs_tl_rec.description,
1328 l_okc_rule_defs_tl_rec.language,
1329 l_okc_rule_defs_tl_rec.source_lang,
1330 l_okc_rule_defs_tl_rec.sfwt_flag,
1331 l_okc_rule_defs_tl_rec.created_by,
1332 l_okc_rule_defs_tl_rec.creation_date,
1333 l_okc_rule_defs_tl_rec.last_updated_by,
1334 l_okc_rule_defs_tl_rec.last_update_date,
1335 l_okc_rule_defs_tl_rec.last_update_login);
1336 END LOOP;
1337 -- Set OUT values
1338 x_okc_rule_defs_tl_rec := l_okc_rule_defs_tl_rec;
1339 x_return_status := l_return_status;
1340 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1341 EXCEPTION
1342 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1343 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1344 (
1345 l_api_name,
1346 G_PKG_NAME,
1350 '_PVT'
1347 'OKC_API.G_RET_STS_ERROR',
1348 x_msg_count,
1349 x_msg_data,
1351 );
1352 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1353 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1354 (
1355 l_api_name,
1356 G_PKG_NAME,
1357 'OKC_API.G_RET_STS_UNEXP_ERROR',
1358 x_msg_count,
1359 x_msg_data,
1360 '_PVT'
1361 );
1362 WHEN OTHERS THEN
1363 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1364 (
1365 l_api_name,
1366 G_PKG_NAME,
1367 'OTHERS',
1368 x_msg_count,
1369 x_msg_data,
1370 '_PVT'
1371 );
1372 END insert_row;
1373 -------------------------------------
1374 -- insert_row for :OKC_RULE_DEFS_V --
1375 -------------------------------------
1376 PROCEDURE insert_row(
1377 p_api_version IN NUMBER,
1378 p_init_msg_list IN VARCHAR2,
1379 x_return_status OUT NOCOPY VARCHAR2,
1380 x_msg_count OUT NOCOPY NUMBER,
1381 x_msg_data OUT NOCOPY VARCHAR2,
1382 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
1383 x_okc_rule_defs_v_rec OUT NOCOPY okc_rule_defs_v_rec_type) IS
1384
1385 l_api_version CONSTANT NUMBER := 1;
1386 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1387 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1388 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
1389 l_def_okc_rule_defs_v_rec okc_rule_defs_v_rec_type;
1390 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
1391 lx_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
1392 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
1393 lx_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
1394 -------------------------------
1395 -- FUNCTION fill_who_columns --
1396 -------------------------------
1397 FUNCTION fill_who_columns (
1398 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
1399 ) RETURN okc_rule_defs_v_rec_type IS
1400 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
1401 BEGIN
1402 l_okc_rule_defs_v_rec.CREATION_DATE := SYSDATE;
1403 l_okc_rule_defs_v_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1404 l_okc_rule_defs_v_rec.LAST_UPDATE_DATE := l_okc_rule_defs_v_rec.CREATION_DATE;
1405 l_okc_rule_defs_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1406 l_okc_rule_defs_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1407 RETURN(l_okc_rule_defs_v_rec);
1408 END fill_who_columns;
1409 ----------------------------------------
1410 -- Set_Attributes for:OKC_RULE_DEFS_V --
1411 ----------------------------------------
1412 FUNCTION Set_Attributes (
1413 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
1414 x_okc_rule_defs_v_rec OUT NOCOPY okc_rule_defs_v_rec_type
1415 ) RETURN VARCHAR2 IS
1416 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1417 BEGIN
1418 x_okc_rule_defs_v_rec := p_okc_rule_defs_v_rec;
1419 x_okc_rule_defs_v_rec.OBJECT_VERSION_NUMBER := 1;
1420 x_okc_rule_defs_v_rec.SFWT_FLAG := 'N';
1421 RETURN(l_return_status);
1422 END Set_Attributes;
1423 BEGIN
1424 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1425 G_PKG_NAME,
1426 p_init_msg_list,
1427 l_api_version,
1428 p_api_version,
1429 '_PVT',
1430 x_return_status);
1431 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1432 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1433 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1434 RAISE OKC_API.G_EXCEPTION_ERROR;
1435 END IF;
1436 l_okc_rule_defs_v_rec := null_out_defaults(p_okc_rule_defs_v_rec);
1437 -- Set primary key value
1438 -- Error: Primary Key Column "RULE_CODE"
1439 -- Does not have a NUMBER datatype, cannot assign get_seq_id
1440 -- Setting item attributes
1441 l_return_Status := Set_Attributes(
1442 l_okc_rule_defs_v_rec, -- IN
1443 l_def_okc_rule_defs_v_rec); -- OUT
1444 --- If any errors happen abort API
1445 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1446 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1447 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1448 RAISE OKC_API.G_EXCEPTION_ERROR;
1449 END IF;
1450 l_def_okc_rule_defs_v_rec := fill_who_columns(l_def_okc_rule_defs_v_rec);
1451 --- Validate all non-missing attributes (Item Level Validation)
1452 l_return_status := Validate_Attributes(l_def_okc_rule_defs_v_rec);
1453 --- If any errors happen abort API
1454 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1455 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1456 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1457 RAISE OKC_API.G_EXCEPTION_ERROR;
1458 END IF;
1459 l_return_status := Validate_Record(l_def_okc_rule_defs_v_rec);
1460 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1461 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1465 -----------------------------------------
1462 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1463 RAISE OKC_API.G_EXCEPTION_ERROR;
1464 END IF;
1466 -- Move VIEW record to "Child" records --
1467 -----------------------------------------
1468 migrate(l_def_okc_rule_defs_v_rec, l_okc_rule_defs_b_rec);
1469 migrate(l_def_okc_rule_defs_v_rec, l_okc_rule_defs_tl_rec);
1470 -----------------------------------------------
1471 -- Call the INSERT_ROW for each child record --
1472 -----------------------------------------------
1473 insert_row(
1474 p_init_msg_list,
1475 l_return_status,
1476 x_msg_count,
1477 x_msg_data,
1478 l_okc_rule_defs_b_rec,
1479 lx_okc_rule_defs_b_rec
1480 );
1481 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_ERROR;
1485 END IF;
1486 migrate(lx_okc_rule_defs_b_rec, l_def_okc_rule_defs_v_rec);
1487 insert_row(
1488 p_init_msg_list,
1489 l_return_status,
1490 x_msg_count,
1491 x_msg_data,
1492 l_okc_rule_defs_tl_rec,
1493 lx_okc_rule_defs_tl_rec
1494 );
1495 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1496 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1498 RAISE OKC_API.G_EXCEPTION_ERROR;
1499 END IF;
1500 migrate(lx_okc_rule_defs_tl_rec, l_def_okc_rule_defs_v_rec);
1501 -- Set OUT values
1502 x_okc_rule_defs_v_rec := l_def_okc_rule_defs_v_rec;
1503 x_return_status := l_return_status;
1504 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1505 EXCEPTION
1506 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1507 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1508 (
1509 l_api_name,
1510 G_PKG_NAME,
1511 'OKC_API.G_RET_STS_ERROR',
1512 x_msg_count,
1513 x_msg_data,
1514 '_PVT'
1515 );
1516 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1517 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1518 (
1519 l_api_name,
1520 G_PKG_NAME,
1521 'OKC_API.G_RET_STS_UNEXP_ERROR',
1522 x_msg_count,
1523 x_msg_data,
1524 '_PVT'
1525 );
1526 WHEN OTHERS THEN
1527 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1528 (
1529 l_api_name,
1530 G_PKG_NAME,
1531 'OTHERS',
1532 x_msg_count,
1533 x_msg_data,
1534 '_PVT'
1535 );
1536 END insert_row;
1537 ---------------------------------------------------
1538 -- PL/SQL TBL insert_row for:OKC_RULE_DEFS_V_TBL --
1539 ---------------------------------------------------
1540 PROCEDURE insert_row(
1541 p_api_version IN NUMBER,
1542 p_init_msg_list IN VARCHAR2,
1543 x_return_status OUT NOCOPY VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
1547 x_okc_rule_defs_v_tbl OUT NOCOPY okc_rule_defs_v_tbl_type,
1548 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1549
1550 l_api_version CONSTANT NUMBER := 1;
1551 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1552 i NUMBER := 0;
1553 BEGIN
1554 OKC_API.init_msg_list(p_init_msg_list);
1555 -- Make sure PL/SQL table has records in it before passing
1556 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
1557 i := p_okc_rule_defs_v_tbl.FIRST;
1558 LOOP
1559 DECLARE
1560 l_error_rec OKC_API.ERROR_REC_TYPE;
1561 BEGIN
1562 l_error_rec.api_name := l_api_name;
1563 l_error_rec.api_package := G_PKG_NAME;
1564 l_error_rec.idx := i;
1565 insert_row (
1566 p_api_version => p_api_version,
1567 p_init_msg_list => OKC_API.G_FALSE,
1568 x_return_status => l_error_rec.error_type,
1569 x_msg_count => l_error_rec.msg_count,
1570 x_msg_data => l_error_rec.msg_data,
1571 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_tbl(i),
1572 x_okc_rule_defs_v_rec => x_okc_rule_defs_v_tbl(i));
1573 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1574 l_error_rec.sqlcode := SQLCODE;
1575 load_error_tbl(l_error_rec, px_error_tbl);
1576 ELSE
1577 x_msg_count := l_error_rec.msg_count;
1578 x_msg_data := l_error_rec.msg_data;
1579 END IF;
1580 EXCEPTION
1581 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1582 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1583 l_error_rec.sqlcode := SQLCODE;
1584 load_error_tbl(l_error_rec, px_error_tbl);
1585 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1586 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1587 l_error_rec.sqlcode := SQLCODE;
1588 load_error_tbl(l_error_rec, px_error_tbl);
1592 load_error_tbl(l_error_rec, px_error_tbl);
1589 WHEN OTHERS THEN
1590 l_error_rec.error_type := 'OTHERS';
1591 l_error_rec.sqlcode := SQLCODE;
1593 END;
1594 EXIT WHEN (i = p_okc_rule_defs_v_tbl.LAST);
1595 i := p_okc_rule_defs_v_tbl.NEXT(i);
1596 END LOOP;
1597 END IF;
1598 -- Loop through the error_tbl to find the error with the highest severity
1599 -- and return it.
1600 x_return_status := find_highest_exception(px_error_tbl);
1601 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1602 EXCEPTION
1603 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1604 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1605 (
1606 l_api_name,
1607 G_PKG_NAME,
1608 'OKC_API.G_RET_STS_ERROR',
1609 x_msg_count,
1610 x_msg_data,
1611 '_PVT'
1612 );
1613 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1614 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1615 (
1616 l_api_name,
1617 G_PKG_NAME,
1618 'OKC_API.G_RET_STS_UNEXP_ERROR',
1619 x_msg_count,
1620 x_msg_data,
1621 '_PVT'
1622 );
1623 WHEN OTHERS THEN
1624 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1625 (
1626 l_api_name,
1627 G_PKG_NAME,
1628 'OTHERS',
1629 x_msg_count,
1630 x_msg_data,
1631 '_PVT'
1632 );
1633 END insert_row;
1634
1635 ---------------------------------------------------
1636 -- PL/SQL TBL insert_row for:OKC_RULE_DEFS_V_TBL --
1637 ---------------------------------------------------
1638 PROCEDURE insert_row(
1639 p_api_version IN NUMBER,
1640 p_init_msg_list IN VARCHAR2,
1641 x_return_status OUT NOCOPY VARCHAR2,
1642 x_msg_count OUT NOCOPY NUMBER,
1643 x_msg_data OUT NOCOPY VARCHAR2,
1644 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
1645 x_okc_rule_defs_v_tbl OUT NOCOPY okc_rule_defs_v_tbl_type) IS
1646
1647 l_api_version CONSTANT NUMBER := 1;
1648 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1649 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1650 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1651 BEGIN
1652 OKC_API.init_msg_list(p_init_msg_list);
1653 -- Make sure PL/SQL table has records in it before passing
1654 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
1655 insert_row (
1656 p_api_version => p_api_version,
1657 p_init_msg_list => OKC_API.G_FALSE,
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data,
1661 p_okc_rule_defs_v_tbl => p_okc_rule_defs_v_tbl,
1662 x_okc_rule_defs_v_tbl => x_okc_rule_defs_v_tbl,
1663 px_error_tbl => l_error_tbl);
1664 END IF;
1665 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1666 EXCEPTION
1667 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1668 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1669 (
1670 l_api_name,
1671 G_PKG_NAME,
1672 'OKC_API.G_RET_STS_ERROR',
1673 x_msg_count,
1674 x_msg_data,
1675 '_PVT'
1676 );
1677 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1678 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1679 (
1680 l_api_name,
1681 G_PKG_NAME,
1682 'OKC_API.G_RET_STS_UNEXP_ERROR',
1683 x_msg_count,
1684 x_msg_data,
1685 '_PVT'
1686 );
1687 WHEN OTHERS THEN
1688 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1689 (
1690 l_api_name,
1691 G_PKG_NAME,
1692 'OTHERS',
1693 x_msg_count,
1694 x_msg_data,
1695 '_PVT'
1696 );
1697 END insert_row;
1698
1699 ---------------------------------------------------------------------------
1700 -- PROCEDURE lock_row
1701 ---------------------------------------------------------------------------
1702 ----------------------------------
1703 -- lock_row for:OKC_RULE_DEFS_B --
1704 ----------------------------------
1705 PROCEDURE lock_row(
1706 p_init_msg_list IN VARCHAR2,
1707 x_return_status OUT NOCOPY VARCHAR2,
1708 x_msg_count OUT NOCOPY NUMBER,
1709 x_msg_data OUT NOCOPY VARCHAR2,
1710 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type) IS
1711
1712 E_Resource_Busy EXCEPTION;
1713 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1714 CURSOR lock_csr (p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type) IS
1715 SELECT OBJECT_VERSION_NUMBER
1716 FROM OKC_RULE_DEFS_B
1717 WHERE RULE_CODE = p_okc_rule_defs_b_rec.rule_code
1718 AND OBJECT_VERSION_NUMBER = p_okc_rule_defs_b_rec.object_version_number
1719 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1720
1721 CURSOR lchk_csr (p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type) IS
1722 SELECT OBJECT_VERSION_NUMBER
1723 FROM OKC_RULE_DEFS_B
1724 WHERE RULE_CODE = p_okc_rule_defs_b_rec.rule_code;
1728 l_object_version_number OKC_RULE_DEFS_B.OBJECT_VERSION_NUMBER%TYPE;
1725 l_api_version CONSTANT NUMBER := 1;
1726 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1727 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1729 lc_object_version_number OKC_RULE_DEFS_B.OBJECT_VERSION_NUMBER%TYPE;
1730 l_row_notfound BOOLEAN := FALSE;
1731 lc_row_notfound BOOLEAN := FALSE;
1732 BEGIN
1733 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1734 p_init_msg_list,
1735 '_PVT',
1736 x_return_status);
1737 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1738 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1739 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1740 RAISE OKC_API.G_EXCEPTION_ERROR;
1741 END IF;
1742 BEGIN
1743 OPEN lock_csr(p_okc_rule_defs_b_rec);
1744 FETCH lock_csr INTO l_object_version_number;
1745 l_row_notfound := lock_csr%NOTFOUND;
1746 CLOSE lock_csr;
1747 EXCEPTION
1748 WHEN E_Resource_Busy THEN
1749 IF (lock_csr%ISOPEN) THEN
1750 CLOSE lock_csr;
1751 END IF;
1752 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1753 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1754 END;
1755
1756 IF ( l_row_notfound ) THEN
1757 OPEN lchk_csr(p_okc_rule_defs_b_rec);
1758 FETCH lchk_csr INTO lc_object_version_number;
1759 lc_row_notfound := lchk_csr%NOTFOUND;
1760 CLOSE lchk_csr;
1761 END IF;
1762 IF (lc_row_notfound) THEN
1763 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1764 RAISE OKC_API.G_EXCEPTION_ERROR;
1765 ELSIF lc_object_version_number > p_okc_rule_defs_b_rec.object_version_number THEN
1766 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1767 RAISE OKC_API.G_EXCEPTION_ERROR;
1768 ELSIF lc_object_version_number <> p_okc_rule_defs_b_rec.object_version_number THEN
1769 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1770 RAISE OKC_API.G_EXCEPTION_ERROR;
1771 ELSIF lc_object_version_number = -1 THEN
1772 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1773 RAISE OKC_API.G_EXCEPTION_ERROR;
1774 END IF;
1775 x_return_status := l_return_status;
1776 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1777 EXCEPTION
1778 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1779 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1780 (
1781 l_api_name,
1782 G_PKG_NAME,
1783 'OKC_API.G_RET_STS_ERROR',
1784 x_msg_count,
1785 x_msg_data,
1786 '_PVT'
1787 );
1788 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1789 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1790 (
1791 l_api_name,
1792 G_PKG_NAME,
1793 'OKC_API.G_RET_STS_UNEXP_ERROR',
1794 x_msg_count,
1795 x_msg_data,
1796 '_PVT'
1797 );
1798 WHEN OTHERS THEN
1799 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1800 (
1801 l_api_name,
1802 G_PKG_NAME,
1803 'OTHERS',
1804 x_msg_count,
1805 x_msg_data,
1806 '_PVT'
1807 );
1808 END lock_row;
1809 -----------------------------------
1810 -- lock_row for:OKC_RULE_DEFS_TL --
1811 -----------------------------------
1812 PROCEDURE lock_row(
1813 p_init_msg_list IN VARCHAR2,
1814 x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_count OUT NOCOPY NUMBER,
1816 x_msg_data OUT NOCOPY VARCHAR2,
1817 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type) IS
1818
1819 E_Resource_Busy EXCEPTION;
1820 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1821 CURSOR lock_csr (p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type) IS
1822 SELECT *
1823 FROM OKC_RULE_DEFS_TL
1824 WHERE RULE_CODE = p_okc_rule_defs_tl_rec.rule_code
1825 FOR UPDATE NOWAIT;
1826
1827 l_api_version CONSTANT NUMBER := 1;
1828 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1829 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1830 l_lock_var lock_csr%ROWTYPE;
1831 l_row_notfound BOOLEAN := FALSE;
1832 lc_row_notfound BOOLEAN := FALSE;
1833 BEGIN
1834 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1835 p_init_msg_list,
1836 '_PVT',
1837 x_return_status);
1838 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1839 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1840 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1841 RAISE OKC_API.G_EXCEPTION_ERROR;
1842 END IF;
1843 BEGIN
1844 OPEN lock_csr(p_okc_rule_defs_tl_rec);
1845 FETCH lock_csr INTO l_lock_var;
1846 l_row_notfound := lock_csr%NOTFOUND;
1847 CLOSE lock_csr;
1848 EXCEPTION
1849 WHEN E_Resource_Busy THEN
1850 IF (lock_csr%ISOPEN) THEN
1851 CLOSE lock_csr;
1855 END;
1852 END IF;
1853 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1854 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1856
1857 IF ( l_row_notfound ) THEN
1858 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1859 RAISE OKC_API.G_EXCEPTION_ERROR;
1860 END IF;
1861 x_return_status := l_return_status;
1862 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1863 EXCEPTION
1864 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1865 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1866 (
1867 l_api_name,
1868 G_PKG_NAME,
1869 'OKC_API.G_RET_STS_ERROR',
1870 x_msg_count,
1871 x_msg_data,
1872 '_PVT'
1873 );
1874 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1875 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1876 (
1877 l_api_name,
1878 G_PKG_NAME,
1879 'OKC_API.G_RET_STS_UNEXP_ERROR',
1880 x_msg_count,
1881 x_msg_data,
1882 '_PVT'
1883 );
1884 WHEN OTHERS THEN
1885 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1886 (
1887 l_api_name,
1888 G_PKG_NAME,
1889 'OTHERS',
1890 x_msg_count,
1891 x_msg_data,
1892 '_PVT'
1893 );
1894 END lock_row;
1895 -----------------------------------
1896 -- lock_row for: OKC_RULE_DEFS_V --
1897 -----------------------------------
1898 PROCEDURE lock_row(
1899 p_api_version IN NUMBER,
1900 p_init_msg_list IN VARCHAR2,
1901 x_return_status OUT NOCOPY VARCHAR2,
1902 x_msg_count OUT NOCOPY NUMBER,
1903 x_msg_data OUT NOCOPY VARCHAR2,
1904 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type) IS
1905
1906 l_api_version CONSTANT NUMBER := 1;
1907 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1908 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1909 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
1910 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
1911 BEGIN
1912 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1913 G_PKG_NAME,
1914 p_init_msg_list,
1915 l_api_version,
1916 p_api_version,
1917 '_PVT',
1918 x_return_status);
1919 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1920 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1921 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1922 RAISE OKC_API.G_EXCEPTION_ERROR;
1923 END IF;
1924 -----------------------------------------
1925 -- Move VIEW record to "Child" records --
1926 -----------------------------------------
1927 migrate(p_okc_rule_defs_v_rec, l_okc_rule_defs_b_rec);
1928 migrate(p_okc_rule_defs_v_rec, l_okc_rule_defs_tl_rec);
1929 ---------------------------------------------
1930 -- Call the LOCK_ROW for each child record --
1931 ---------------------------------------------
1932 lock_row(
1933 p_init_msg_list,
1934 l_return_status,
1935 x_msg_count,
1936 x_msg_data,
1937 l_okc_rule_defs_b_rec
1938 );
1939 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1940 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1941 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1942 RAISE OKC_API.G_EXCEPTION_ERROR;
1943 END IF;
1944 lock_row(
1945 p_init_msg_list,
1946 l_return_status,
1947 x_msg_count,
1948 x_msg_data,
1949 l_okc_rule_defs_tl_rec
1950 );
1951 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1952 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1953 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1954 RAISE OKC_API.G_EXCEPTION_ERROR;
1955 END IF;
1956 x_return_status := l_return_status;
1957 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1958 EXCEPTION
1959 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1960 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1961 (
1962 l_api_name,
1963 G_PKG_NAME,
1964 'OKC_API.G_RET_STS_ERROR',
1965 x_msg_count,
1966 x_msg_data,
1967 '_PVT'
1968 );
1969 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1970 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1971 (
1972 l_api_name,
1973 G_PKG_NAME,
1974 'OKC_API.G_RET_STS_UNEXP_ERROR',
1975 x_msg_count,
1976 x_msg_data,
1977 '_PVT'
1978 );
1979 WHEN OTHERS THEN
1980 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1981 (
1982 l_api_name,
1983 G_PKG_NAME,
1984 'OTHERS',
1985 x_msg_count,
1986 x_msg_data,
1987 '_PVT'
1988 );
1989 END lock_row;
1990 -------------------------------------------------
1991 -- PL/SQL TBL lock_row for:OKC_RULE_DEFS_V_TBL --
1992 -------------------------------------------------
1993 PROCEDURE lock_row(
1997 x_msg_count OUT NOCOPY NUMBER,
1994 p_api_version IN NUMBER,
1995 p_init_msg_list IN VARCHAR2,
1996 x_return_status OUT NOCOPY VARCHAR2,
1998 x_msg_data OUT NOCOPY VARCHAR2,
1999 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
2000 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2001
2002 l_api_version CONSTANT NUMBER := 1;
2003 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2004 i NUMBER := 0;
2005 BEGIN
2006 OKC_API.init_msg_list(p_init_msg_list);
2007 -- Make sure PL/SQL table has recrods in it before passing
2008 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
2009 i := p_okc_rule_defs_v_tbl.FIRST;
2010 LOOP
2011 DECLARE
2012 l_error_rec OKC_API.ERROR_REC_TYPE;
2013 BEGIN
2014 l_error_rec.api_name := l_api_name;
2015 l_error_rec.api_package := G_PKG_NAME;
2016 l_error_rec.idx := i;
2017 lock_row(
2018 p_api_version => p_api_version,
2019 p_init_msg_list => OKC_API.G_FALSE,
2020 x_return_status => l_error_rec.error_type,
2021 x_msg_count => l_error_rec.msg_count,
2022 x_msg_data => l_error_rec.msg_data,
2023 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_tbl(i));
2024 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2025 l_error_rec.sqlcode := SQLCODE;
2026 load_error_tbl(l_error_rec, px_error_tbl);
2027 ELSE
2028 x_msg_count := l_error_rec.msg_count;
2029 x_msg_data := l_error_rec.msg_data;
2030 END IF;
2031 EXCEPTION
2032 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2033 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2034 l_error_rec.sqlcode := SQLCODE;
2035 load_error_tbl(l_error_rec, px_error_tbl);
2036 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2037 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2038 l_error_rec.sqlcode := SQLCODE;
2039 load_error_tbl(l_error_rec, px_error_tbl);
2040 WHEN OTHERS THEN
2041 l_error_rec.error_type := 'OTHERS';
2042 l_error_rec.sqlcode := SQLCODE;
2043 load_error_tbl(l_error_rec, px_error_tbl);
2044 END;
2045 EXIT WHEN (i = p_okc_rule_defs_v_tbl.LAST);
2046 i := p_okc_rule_defs_v_tbl.NEXT(i);
2047 END LOOP;
2048 END IF;
2049 -- Loop through the error_tbl to find the error with the highest severity
2050 -- and return it.
2051 x_return_status := find_highest_exception(px_error_tbl);
2052 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2053 EXCEPTION
2054 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2055 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2056 (
2057 l_api_name,
2058 G_PKG_NAME,
2059 'OKC_API.G_RET_STS_ERROR',
2060 x_msg_count,
2061 x_msg_data,
2062 '_PVT'
2063 );
2064 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2065 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2066 (
2067 l_api_name,
2068 G_PKG_NAME,
2069 'OKC_API.G_RET_STS_UNEXP_ERROR',
2070 x_msg_count,
2071 x_msg_data,
2072 '_PVT'
2073 );
2074 WHEN OTHERS THEN
2075 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2076 (
2077 l_api_name,
2078 G_PKG_NAME,
2079 'OTHERS',
2080 x_msg_count,
2081 x_msg_data,
2082 '_PVT'
2083 );
2084 END lock_row;
2085 -------------------------------------------------
2086 -- PL/SQL TBL lock_row for:OKC_RULE_DEFS_V_TBL --
2087 -------------------------------------------------
2088 PROCEDURE lock_row(
2089 p_api_version IN NUMBER,
2090 p_init_msg_list IN VARCHAR2,
2091 x_return_status OUT NOCOPY VARCHAR2,
2092 x_msg_count OUT NOCOPY NUMBER,
2093 x_msg_data OUT NOCOPY VARCHAR2,
2094 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type) IS
2095
2096 l_api_version CONSTANT NUMBER := 1;
2097 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2098 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2099 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2100 BEGIN
2101 OKC_API.init_msg_list(p_init_msg_list);
2102 -- Make sure PL/SQL table has recrods in it before passing
2103 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
2104 lock_row(
2105 p_api_version => p_api_version,
2106 p_init_msg_list => OKC_API.G_FALSE,
2107 x_return_status => x_return_status,
2108 x_msg_count => x_msg_count,
2109 x_msg_data => x_msg_data,
2110 p_okc_rule_defs_v_tbl => p_okc_rule_defs_v_tbl,
2111 px_error_tbl => l_error_tbl);
2112 END IF;
2113 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2114 EXCEPTION
2115 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2119 G_PKG_NAME,
2116 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2117 (
2118 l_api_name,
2120 'OKC_API.G_RET_STS_ERROR',
2121 x_msg_count,
2122 x_msg_data,
2123 '_PVT'
2124 );
2125 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2126 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2127 (
2128 l_api_name,
2129 G_PKG_NAME,
2130 'OKC_API.G_RET_STS_UNEXP_ERROR',
2131 x_msg_count,
2132 x_msg_data,
2133 '_PVT'
2134 );
2135 WHEN OTHERS THEN
2136 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2137 (
2138 l_api_name,
2139 G_PKG_NAME,
2140 'OTHERS',
2141 x_msg_count,
2142 x_msg_data,
2143 '_PVT'
2144 );
2145 END lock_row;
2146 ---------------------------------------------------------------------------
2147 -- PROCEDURE update_row
2148 ---------------------------------------------------------------------------
2149 ------------------------------------
2150 -- update_row for:OKC_RULE_DEFS_B --
2151 ------------------------------------
2152 PROCEDURE update_row(
2153 p_init_msg_list IN VARCHAR2,
2154 x_return_status OUT NOCOPY VARCHAR2,
2155 x_msg_count OUT NOCOPY NUMBER,
2156 x_msg_data OUT NOCOPY VARCHAR2,
2157 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
2158 x_okc_rule_defs_b_rec OUT NOCOPY okc_rule_defs_b_rec_type) IS
2159
2160 l_api_version CONSTANT NUMBER := 1;
2161 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2162 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2163 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type := p_okc_rule_defs_b_rec;
2164 l_def_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
2165 l_row_notfound BOOLEAN := TRUE;
2166 ----------------------------------
2167 -- FUNCTION populate_new_record --
2168 ----------------------------------
2169 FUNCTION populate_new_record (
2170 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
2171 x_okc_rule_defs_b_rec OUT NOCOPY okc_rule_defs_b_rec_type
2172 ) RETURN VARCHAR2 IS
2173 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
2174 l_row_notfound BOOLEAN := TRUE;
2175 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2176 BEGIN
2177 x_okc_rule_defs_b_rec := p_okc_rule_defs_b_rec;
2178 -- Get current database values
2179 l_okc_rule_defs_b_rec := get_rec(p_okc_rule_defs_b_rec, l_return_status);
2180 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2181 IF (x_okc_rule_defs_b_rec.rule_code = OKC_API.G_MISS_CHAR)
2182 THEN
2183 x_okc_rule_defs_b_rec.rule_code := l_okc_rule_defs_b_rec.rule_code;
2184 END IF;
2185 IF (x_okc_rule_defs_b_rec.application_id = OKC_API.G_MISS_NUM)
2186 THEN
2187 x_okc_rule_defs_b_rec.application_id := l_okc_rule_defs_b_rec.application_id;
2188 END IF;
2189 IF (x_okc_rule_defs_b_rec.descriptive_flexfield_name = OKC_API.G_MISS_CHAR)
2190 THEN
2191 x_okc_rule_defs_b_rec.descriptive_flexfield_name := l_okc_rule_defs_b_rec.descriptive_flexfield_name;
2192 END IF;
2193 IF (x_okc_rule_defs_b_rec.object_version_number = OKC_API.G_MISS_NUM)
2194 THEN
2195 x_okc_rule_defs_b_rec.object_version_number := l_okc_rule_defs_b_rec.object_version_number;
2196 END IF;
2197 IF (x_okc_rule_defs_b_rec.created_by = OKC_API.G_MISS_NUM)
2198 THEN
2199 x_okc_rule_defs_b_rec.created_by := l_okc_rule_defs_b_rec.created_by;
2200 END IF;
2201 IF (x_okc_rule_defs_b_rec.creation_date = OKC_API.G_MISS_DATE)
2202 THEN
2203 x_okc_rule_defs_b_rec.creation_date := l_okc_rule_defs_b_rec.creation_date;
2204 END IF;
2205 IF (x_okc_rule_defs_b_rec.last_updated_by = OKC_API.G_MISS_NUM)
2206 THEN
2207 x_okc_rule_defs_b_rec.last_updated_by := l_okc_rule_defs_b_rec.last_updated_by;
2208 END IF;
2209 IF (x_okc_rule_defs_b_rec.last_update_date = OKC_API.G_MISS_DATE)
2210 THEN
2211 x_okc_rule_defs_b_rec.last_update_date := l_okc_rule_defs_b_rec.last_update_date;
2212 END IF;
2213 IF (x_okc_rule_defs_b_rec.last_update_login = OKC_API.G_MISS_NUM)
2214 THEN
2215 x_okc_rule_defs_b_rec.last_update_login := l_okc_rule_defs_b_rec.last_update_login;
2216 END IF;
2217 END IF;
2218 RETURN(l_return_status);
2219 END populate_new_record;
2220 ----------------------------------------
2221 -- Set_Attributes for:OKC_RULE_DEFS_B --
2222 ----------------------------------------
2223 FUNCTION Set_Attributes (
2224 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type,
2225 x_okc_rule_defs_b_rec OUT NOCOPY okc_rule_defs_b_rec_type
2226 ) RETURN VARCHAR2 IS
2227 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2228 BEGIN
2229 x_okc_rule_defs_b_rec := p_okc_rule_defs_b_rec;
2230 x_okc_rule_defs_b_rec.OBJECT_VERSION_NUMBER := p_okc_rule_defs_b_rec.OBJECT_VERSION_NUMBER + 1;
2231 RETURN(l_return_status);
2232 END Set_Attributes;
2233 BEGIN
2237 x_return_status);
2234 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2235 p_init_msg_list,
2236 '_PVT',
2238 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2239 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2240 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2241 RAISE OKC_API.G_EXCEPTION_ERROR;
2242 END IF;
2243 --- Setting item attributes
2244 l_return_status := Set_Attributes(
2245 p_okc_rule_defs_b_rec, -- IN
2246 l_okc_rule_defs_b_rec); -- OUT
2247 --- If any errors happen abort API
2248 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2249 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2250 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2251 RAISE OKC_API.G_EXCEPTION_ERROR;
2252 END IF;
2253 l_return_status := populate_new_record(l_okc_rule_defs_b_rec, l_def_okc_rule_defs_b_rec);
2254 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2255 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2256 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2257 RAISE OKC_API.G_EXCEPTION_ERROR;
2258 END IF;
2259 UPDATE OKC_RULE_DEFS_B
2260 SET APPLICATION_ID = l_def_okc_rule_defs_b_rec.application_id,
2261 DESCRIPTIVE_FLEXFIELD_NAME = l_def_okc_rule_defs_b_rec.descriptive_flexfield_name,
2262 OBJECT_VERSION_NUMBER = l_def_okc_rule_defs_b_rec.object_version_number,
2263 CREATED_BY = l_def_okc_rule_defs_b_rec.created_by,
2264 CREATION_DATE = l_def_okc_rule_defs_b_rec.creation_date,
2265 LAST_UPDATED_BY = l_def_okc_rule_defs_b_rec.last_updated_by,
2266 LAST_UPDATE_DATE = l_def_okc_rule_defs_b_rec.last_update_date,
2267 LAST_UPDATE_LOGIN = l_def_okc_rule_defs_b_rec.last_update_login
2268 WHERE RULE_CODE = l_def_okc_rule_defs_b_rec.rule_code;
2269
2270 x_okc_rule_defs_b_rec := l_okc_rule_defs_b_rec;
2271 x_return_status := l_return_status;
2272 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2273 EXCEPTION
2274 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2275 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OKC_API.G_RET_STS_ERROR',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2286 (
2287 l_api_name,
2288 G_PKG_NAME,
2289 'OKC_API.G_RET_STS_UNEXP_ERROR',
2290 x_msg_count,
2291 x_msg_data,
2292 '_PVT'
2293 );
2294 WHEN OTHERS THEN
2295 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2296 (
2297 l_api_name,
2298 G_PKG_NAME,
2299 'OTHERS',
2300 x_msg_count,
2301 x_msg_data,
2302 '_PVT'
2303 );
2304 END update_row;
2305 -------------------------------------
2306 -- update_row for:OKC_RULE_DEFS_TL --
2307 -------------------------------------
2308 PROCEDURE update_row(
2309 p_init_msg_list IN VARCHAR2,
2310 x_return_status OUT NOCOPY VARCHAR2,
2311 x_msg_count OUT NOCOPY NUMBER,
2312 x_msg_data OUT NOCOPY VARCHAR2,
2313 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
2314 x_okc_rule_defs_tl_rec OUT NOCOPY okc_rule_defs_tl_rec_type) IS
2315
2316 l_api_version CONSTANT NUMBER := 1;
2317 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2318 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2319 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type := p_okc_rule_defs_tl_rec;
2320 l_def_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
2321 l_row_notfound BOOLEAN := TRUE;
2322 ----------------------------------
2323 -- FUNCTION populate_new_record --
2324 ----------------------------------
2325 FUNCTION populate_new_record (
2326 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
2327 x_okc_rule_defs_tl_rec OUT NOCOPY okc_rule_defs_tl_rec_type
2328 ) RETURN VARCHAR2 IS
2329 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
2330 l_row_notfound BOOLEAN := TRUE;
2331 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2332 BEGIN
2333 x_okc_rule_defs_tl_rec := p_okc_rule_defs_tl_rec;
2334 -- Get current database values
2335 l_okc_rule_defs_tl_rec := get_rec(p_okc_rule_defs_tl_rec, l_return_status);
2336 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2337 IF (x_okc_rule_defs_tl_rec.rule_code = OKC_API.G_MISS_CHAR)
2338 THEN
2339 x_okc_rule_defs_tl_rec.rule_code := l_okc_rule_defs_tl_rec.rule_code;
2340 END IF;
2341 IF (x_okc_rule_defs_tl_rec.meaning = OKC_API.G_MISS_CHAR)
2342 THEN
2343 x_okc_rule_defs_tl_rec.meaning := l_okc_rule_defs_tl_rec.meaning;
2344 END IF;
2345 IF (x_okc_rule_defs_tl_rec.description = OKC_API.G_MISS_CHAR)
2346 THEN
2347 x_okc_rule_defs_tl_rec.description := l_okc_rule_defs_tl_rec.description;
2348 END IF;
2349 IF (x_okc_rule_defs_tl_rec.language = OKC_API.G_MISS_CHAR)
2350 THEN
2354 THEN
2351 x_okc_rule_defs_tl_rec.language := l_okc_rule_defs_tl_rec.language;
2352 END IF;
2353 IF (x_okc_rule_defs_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2355 x_okc_rule_defs_tl_rec.source_lang := l_okc_rule_defs_tl_rec.source_lang;
2356 END IF;
2357 IF (x_okc_rule_defs_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2358 THEN
2359 x_okc_rule_defs_tl_rec.sfwt_flag := l_okc_rule_defs_tl_rec.sfwt_flag;
2360 END IF;
2361 IF (x_okc_rule_defs_tl_rec.created_by = OKC_API.G_MISS_NUM)
2362 THEN
2363 x_okc_rule_defs_tl_rec.created_by := l_okc_rule_defs_tl_rec.created_by;
2364 END IF;
2365 IF (x_okc_rule_defs_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2366 THEN
2367 x_okc_rule_defs_tl_rec.creation_date := l_okc_rule_defs_tl_rec.creation_date;
2368 END IF;
2369 IF (x_okc_rule_defs_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2370 THEN
2371 x_okc_rule_defs_tl_rec.last_updated_by := l_okc_rule_defs_tl_rec.last_updated_by;
2372 END IF;
2373 IF (x_okc_rule_defs_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2374 THEN
2375 x_okc_rule_defs_tl_rec.last_update_date := l_okc_rule_defs_tl_rec.last_update_date;
2376 END IF;
2377 IF (x_okc_rule_defs_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2378 THEN
2379 x_okc_rule_defs_tl_rec.last_update_login := l_okc_rule_defs_tl_rec.last_update_login;
2380 END IF;
2381 END IF;
2382 RETURN(l_return_status);
2383 END populate_new_record;
2384 -----------------------------------------
2385 -- Set_Attributes for:OKC_RULE_DEFS_TL --
2386 -----------------------------------------
2387 FUNCTION Set_Attributes (
2388 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type,
2389 x_okc_rule_defs_tl_rec OUT NOCOPY okc_rule_defs_tl_rec_type
2390 ) RETURN VARCHAR2 IS
2391 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2392 BEGIN
2393 x_okc_rule_defs_tl_rec := p_okc_rule_defs_tl_rec;
2394 x_okc_rule_defs_tl_rec.LANGUAGE := USERENV('LANG');
2395 x_okc_rule_defs_tl_rec.LANGUAGE := USERENV('LANG');
2396 RETURN(l_return_status);
2397 END Set_Attributes;
2398 BEGIN
2399 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2400 p_init_msg_list,
2401 '_PVT',
2402 x_return_status);
2403 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2404 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2405 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2406 RAISE OKC_API.G_EXCEPTION_ERROR;
2407 END IF;
2408 --- Setting item attributes
2409 l_return_status := Set_Attributes(
2410 p_okc_rule_defs_tl_rec, -- IN
2411 l_okc_rule_defs_tl_rec); -- OUT
2412 --- If any errors happen abort API
2413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2416 RAISE OKC_API.G_EXCEPTION_ERROR;
2417 END IF;
2418 l_return_status := populate_new_record(l_okc_rule_defs_tl_rec, l_def_okc_rule_defs_tl_rec);
2419 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2421 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2422 RAISE OKC_API.G_EXCEPTION_ERROR;
2423 END IF;
2424 UPDATE OKC_RULE_DEFS_TL
2425 SET MEANING = l_def_okc_rule_defs_tl_rec.meaning,
2426 DESCRIPTION = l_def_okc_rule_defs_tl_rec.description,
2427 CREATED_BY = l_def_okc_rule_defs_tl_rec.created_by,
2428 CREATION_DATE = l_def_okc_rule_defs_tl_rec.creation_date,
2429 LAST_UPDATED_BY = l_def_okc_rule_defs_tl_rec.last_updated_by,
2430 LAST_UPDATE_DATE = l_def_okc_rule_defs_tl_rec.last_update_date,
2431 LAST_UPDATE_LOGIN = l_def_okc_rule_defs_tl_rec.last_update_login
2432 WHERE RULE_CODE = l_def_okc_rule_defs_tl_rec.rule_code
2433 AND SOURCE_LANG = USERENV('LANG');
2434
2435 UPDATE OKC_RULE_DEFS_TL
2436 SET SFWT_FLAG = 'Y'
2437 WHERE RULE_CODE = l_def_okc_rule_defs_tl_rec.rule_code
2438 AND SOURCE_LANG <> USERENV('LANG');
2439
2440 x_okc_rule_defs_tl_rec := l_okc_rule_defs_tl_rec;
2441 x_return_status := l_return_status;
2442 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2443 EXCEPTION
2444 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2445 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2446 (
2447 l_api_name,
2448 G_PKG_NAME,
2449 'OKC_API.G_RET_STS_ERROR',
2450 x_msg_count,
2451 x_msg_data,
2452 '_PVT'
2453 );
2454 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2455 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2456 (
2457 l_api_name,
2458 G_PKG_NAME,
2459 'OKC_API.G_RET_STS_UNEXP_ERROR',
2460 x_msg_count,
2461 x_msg_data,
2462 '_PVT'
2463 );
2464 WHEN OTHERS THEN
2465 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2466 (
2467 l_api_name,
2468 G_PKG_NAME,
2469 'OTHERS',
2470 x_msg_count,
2471 x_msg_data,
2472 '_PVT'
2473 );
2477 ------------------------------------
2474 END update_row;
2475 ------------------------------------
2476 -- update_row for:OKC_RULE_DEFS_V --
2478 PROCEDURE update_row(
2479 p_api_version IN NUMBER,
2480 p_init_msg_list IN VARCHAR2,
2481 x_return_status OUT NOCOPY VARCHAR2,
2482 x_msg_count OUT NOCOPY NUMBER,
2483 x_msg_data OUT NOCOPY VARCHAR2,
2484 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
2485 x_okc_rule_defs_v_rec OUT NOCOPY okc_rule_defs_v_rec_type) IS
2486
2487 l_api_version CONSTANT NUMBER := 1;
2488 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2489 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2490 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
2491 l_def_okc_rule_defs_v_rec okc_rule_defs_v_rec_type;
2492 l_db_okc_rule_defs_v_rec okc_rule_defs_v_rec_type;
2493 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
2494 lx_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
2495 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
2496 lx_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
2497 -------------------------------
2498 -- FUNCTION fill_who_columns --
2499 -------------------------------
2500 FUNCTION fill_who_columns (
2501 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
2502 ) RETURN okc_rule_defs_v_rec_type IS
2503 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
2504 BEGIN
2505 l_okc_rule_defs_v_rec.LAST_UPDATE_DATE := SYSDATE;
2506 l_okc_rule_defs_v_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2507 l_okc_rule_defs_v_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2508 RETURN(l_okc_rule_defs_v_rec);
2509 END fill_who_columns;
2510 ----------------------------------
2511 -- FUNCTION populate_new_record --
2512 ----------------------------------
2513 FUNCTION populate_new_record (
2514 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
2515 x_okc_rule_defs_v_rec OUT NOCOPY okc_rule_defs_v_rec_type
2516 ) RETURN VARCHAR2 IS
2517 l_row_notfound BOOLEAN := TRUE;
2518 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2519 BEGIN
2520 x_okc_rule_defs_v_rec := p_okc_rule_defs_v_rec;
2521 -- Get current database values
2522 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2523 -- so it may be verified through LOCK_ROW.
2524 l_db_okc_rule_defs_v_rec := get_rec(p_okc_rule_defs_v_rec, l_return_status);
2525 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2526 IF (x_okc_rule_defs_v_rec.application_id = OKC_API.G_MISS_NUM)
2527 THEN
2528 x_okc_rule_defs_v_rec.application_id := l_db_okc_rule_defs_v_rec.application_id;
2529 END IF;
2530 IF (x_okc_rule_defs_v_rec.application_name = OKC_API.G_MISS_CHAR)
2531 THEN
2532 x_okc_rule_defs_v_rec.application_name := l_db_okc_rule_defs_v_rec.application_name;
2533 END IF;
2534 IF (x_okc_rule_defs_v_rec.rule_code = OKC_API.G_MISS_CHAR)
2535 THEN
2536 x_okc_rule_defs_v_rec.rule_code := l_db_okc_rule_defs_v_rec.rule_code;
2537 END IF;
2538 IF (x_okc_rule_defs_v_rec.descriptive_flexfield_name = OKC_API.G_MISS_CHAR)
2539 THEN
2540 x_okc_rule_defs_v_rec.descriptive_flexfield_name := l_db_okc_rule_defs_v_rec.descriptive_flexfield_name;
2541 END IF;
2542 IF (x_okc_rule_defs_v_rec.meaning = OKC_API.G_MISS_CHAR)
2543 THEN
2544 x_okc_rule_defs_v_rec.meaning := l_db_okc_rule_defs_v_rec.meaning;
2545 END IF;
2546 IF (x_okc_rule_defs_v_rec.description = OKC_API.G_MISS_CHAR)
2547 THEN
2548 x_okc_rule_defs_v_rec.description := l_db_okc_rule_defs_v_rec.description;
2549 END IF;
2550 IF (x_okc_rule_defs_v_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2551 THEN
2552 x_okc_rule_defs_v_rec.sfwt_flag := l_db_okc_rule_defs_v_rec.sfwt_flag;
2553 END IF;
2554 IF (x_okc_rule_defs_v_rec.created_by = OKC_API.G_MISS_NUM)
2555 THEN
2556 x_okc_rule_defs_v_rec.created_by := l_db_okc_rule_defs_v_rec.created_by;
2557 END IF;
2558 IF (x_okc_rule_defs_v_rec.creation_date = OKC_API.G_MISS_DATE)
2559 THEN
2560 x_okc_rule_defs_v_rec.creation_date := l_db_okc_rule_defs_v_rec.creation_date;
2561 END IF;
2562 IF (x_okc_rule_defs_v_rec.last_updated_by = OKC_API.G_MISS_NUM)
2563 THEN
2564 x_okc_rule_defs_v_rec.last_updated_by := l_db_okc_rule_defs_v_rec.last_updated_by;
2565 END IF;
2566 IF (x_okc_rule_defs_v_rec.last_update_date = OKC_API.G_MISS_DATE)
2567 THEN
2568 x_okc_rule_defs_v_rec.last_update_date := l_db_okc_rule_defs_v_rec.last_update_date;
2569 END IF;
2570 IF (x_okc_rule_defs_v_rec.last_update_login = OKC_API.G_MISS_NUM)
2571 THEN
2572 x_okc_rule_defs_v_rec.last_update_login := l_db_okc_rule_defs_v_rec.last_update_login;
2573 END IF;
2574 END IF;
2575 RETURN(l_return_status);
2576 END populate_new_record;
2577 ----------------------------------------
2578 -- Set_Attributes for:OKC_RULE_DEFS_V --
2579 ----------------------------------------
2583 ) RETURN VARCHAR2 IS
2580 FUNCTION Set_Attributes (
2581 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type,
2582 x_okc_rule_defs_v_rec OUT NOCOPY okc_rule_defs_v_rec_type
2584 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2585 BEGIN
2586 x_okc_rule_defs_v_rec := p_okc_rule_defs_v_rec;
2587 RETURN(l_return_status);
2588 END Set_Attributes;
2589 BEGIN
2590 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2591 G_PKG_NAME,
2592 p_init_msg_list,
2593 l_api_version,
2594 p_api_version,
2595 '_PVT',
2596 x_return_status);
2597 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2598 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2599 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2600 RAISE OKC_API.G_EXCEPTION_ERROR;
2601 END IF;
2602 --- Setting item attributes
2603 l_return_status := Set_Attributes(
2604 p_okc_rule_defs_v_rec, -- IN
2605 x_okc_rule_defs_v_rec); -- OUT
2606 --- If any errors happen abort API
2607 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2608 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2609 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2610 RAISE OKC_API.G_EXCEPTION_ERROR;
2611 END IF;
2612 l_return_status := populate_new_record(l_okc_rule_defs_v_rec, l_def_okc_rule_defs_v_rec);
2613 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2614 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2615 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2616 RAISE OKC_API.G_EXCEPTION_ERROR;
2617 END IF;
2618 l_def_okc_rule_defs_v_rec := fill_who_columns(l_def_okc_rule_defs_v_rec);
2619 --- Validate all non-missing attributes (Item Level Validation)
2620 l_return_status := Validate_Attributes(l_def_okc_rule_defs_v_rec);
2621 --- If any errors happen abort API
2622 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2623 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2624 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2625 RAISE OKC_API.G_EXCEPTION_ERROR;
2626 END IF;
2627 l_return_status := Validate_Record(l_def_okc_rule_defs_v_rec, l_db_okc_rule_defs_v_rec);
2628 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2629 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2630 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2631 RAISE OKC_API.G_EXCEPTION_ERROR;
2632 END IF;
2633
2634 -- Lock the Record
2635 lock_row(
2636 p_api_version => p_api_version,
2637 p_init_msg_list => p_init_msg_list,
2638 x_return_status => l_return_status,
2639 x_msg_count => x_msg_count,
2640 x_msg_data => x_msg_data,
2641 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_rec);
2642 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2643 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2644 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2645 RAISE OKC_API.G_EXCEPTION_ERROR;
2646 END IF;
2647
2648 -----------------------------------------
2649 -- Move VIEW record to "Child" records --
2650 -----------------------------------------
2651 migrate(l_def_okc_rule_defs_v_rec, l_okc_rule_defs_b_rec);
2652 migrate(l_def_okc_rule_defs_v_rec, l_okc_rule_defs_tl_rec);
2653 -----------------------------------------------
2654 -- Call the UPDATE_ROW for each child record --
2655 -----------------------------------------------
2656 update_row(
2657 p_init_msg_list,
2658 l_return_status,
2659 x_msg_count,
2660 x_msg_data,
2661 l_okc_rule_defs_b_rec,
2662 lx_okc_rule_defs_b_rec
2663 );
2664 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2665 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2666 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2667 RAISE OKC_API.G_EXCEPTION_ERROR;
2668 END IF;
2669 migrate(lx_okc_rule_defs_b_rec, l_def_okc_rule_defs_v_rec);
2670 update_row(
2671 p_init_msg_list,
2672 l_return_status,
2673 x_msg_count,
2674 x_msg_data,
2675 l_okc_rule_defs_tl_rec,
2676 lx_okc_rule_defs_tl_rec
2677 );
2678 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2679 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2680 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2681 RAISE OKC_API.G_EXCEPTION_ERROR;
2682 END IF;
2683 migrate(lx_okc_rule_defs_tl_rec, l_def_okc_rule_defs_v_rec);
2684 x_okc_rule_defs_v_rec := l_def_okc_rule_defs_v_rec;
2685 x_return_status := l_return_status;
2686 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2687 EXCEPTION
2688 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2689 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2690 (
2691 l_api_name,
2692 G_PKG_NAME,
2693 'OKC_API.G_RET_STS_ERROR',
2694 x_msg_count,
2695 x_msg_data,
2696 '_PVT'
2697 );
2698 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2699 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2700 (
2704 x_msg_count,
2701 l_api_name,
2702 G_PKG_NAME,
2703 'OKC_API.G_RET_STS_UNEXP_ERROR',
2705 x_msg_data,
2706 '_PVT'
2707 );
2708 WHEN OTHERS THEN
2709 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2710 (
2711 l_api_name,
2712 G_PKG_NAME,
2713 'OTHERS',
2714 x_msg_count,
2715 x_msg_data,
2716 '_PVT'
2717 );
2718 END update_row;
2719 ---------------------------------------------------
2720 -- PL/SQL TBL update_row for:okc_rule_defs_v_tbl --
2721 ---------------------------------------------------
2722 PROCEDURE update_row(
2723 p_api_version IN NUMBER,
2724 p_init_msg_list IN VARCHAR2,
2725 x_return_status OUT NOCOPY VARCHAR2,
2726 x_msg_count OUT NOCOPY NUMBER,
2727 x_msg_data OUT NOCOPY VARCHAR2,
2728 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
2729 x_okc_rule_defs_v_tbl OUT NOCOPY okc_rule_defs_v_tbl_type,
2730 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2731
2732 l_api_version CONSTANT NUMBER := 1;
2733 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2734 i NUMBER := 0;
2735 BEGIN
2736 OKC_API.init_msg_list(p_init_msg_list);
2737 -- Make sure PL/SQL table has records in it before passing
2738 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
2739 i := p_okc_rule_defs_v_tbl.FIRST;
2740 LOOP
2741 DECLARE
2742 l_error_rec OKC_API.ERROR_REC_TYPE;
2743 BEGIN
2744 l_error_rec.api_name := l_api_name;
2745 l_error_rec.api_package := G_PKG_NAME;
2746 l_error_rec.idx := i;
2747 update_row (
2748 p_api_version => p_api_version,
2749 p_init_msg_list => OKC_API.G_FALSE,
2750 x_return_status => l_error_rec.error_type,
2751 x_msg_count => l_error_rec.msg_count,
2752 x_msg_data => l_error_rec.msg_data,
2753 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_tbl(i),
2754 x_okc_rule_defs_v_rec => x_okc_rule_defs_v_tbl(i));
2755 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2756 l_error_rec.sqlcode := SQLCODE;
2757 load_error_tbl(l_error_rec, px_error_tbl);
2758 ELSE
2759 x_msg_count := l_error_rec.msg_count;
2760 x_msg_data := l_error_rec.msg_data;
2761 END IF;
2762 EXCEPTION
2763 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2764 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2765 l_error_rec.sqlcode := SQLCODE;
2766 load_error_tbl(l_error_rec, px_error_tbl);
2767 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2768 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2769 l_error_rec.sqlcode := SQLCODE;
2770 load_error_tbl(l_error_rec, px_error_tbl);
2771 WHEN OTHERS THEN
2772 l_error_rec.error_type := 'OTHERS';
2773 l_error_rec.sqlcode := SQLCODE;
2774 load_error_tbl(l_error_rec, px_error_tbl);
2775 END;
2776 EXIT WHEN (i = p_okc_rule_defs_v_tbl.LAST);
2777 i := p_okc_rule_defs_v_tbl.NEXT(i);
2778 END LOOP;
2779 END IF;
2780 -- Loop through the error_tbl to find the error with the highest severity
2781 -- and return it.
2782 x_return_status := find_highest_exception(px_error_tbl);
2783 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2784 EXCEPTION
2785 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2786 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2787 (
2788 l_api_name,
2789 G_PKG_NAME,
2790 'OKC_API.G_RET_STS_ERROR',
2791 x_msg_count,
2792 x_msg_data,
2793 '_PVT'
2794 );
2795 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2796 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2797 (
2798 l_api_name,
2799 G_PKG_NAME,
2800 'OKC_API.G_RET_STS_UNEXP_ERROR',
2801 x_msg_count,
2802 x_msg_data,
2803 '_PVT'
2804 );
2805 WHEN OTHERS THEN
2806 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2807 (
2808 l_api_name,
2809 G_PKG_NAME,
2810 'OTHERS',
2811 x_msg_count,
2812 x_msg_data,
2813 '_PVT'
2814 );
2815 END update_row;
2816
2817 ---------------------------------------------------
2818 -- PL/SQL TBL update_row for:OKC_RULE_DEFS_V_TBL --
2819 ---------------------------------------------------
2820 PROCEDURE update_row(
2821 p_api_version IN NUMBER,
2822 p_init_msg_list IN VARCHAR2,
2823 x_return_status OUT NOCOPY VARCHAR2,
2824 x_msg_count OUT NOCOPY NUMBER,
2825 x_msg_data OUT NOCOPY VARCHAR2,
2826 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
2827 x_okc_rule_defs_v_tbl OUT NOCOPY okc_rule_defs_v_tbl_type) IS
2828
2829 l_api_version CONSTANT NUMBER := 1;
2833 BEGIN
2830 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2831 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2832 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2834 OKC_API.init_msg_list(p_init_msg_list);
2835 -- Make sure PL/SQL table has records in it before passing
2836 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
2837 update_row (
2838 p_api_version => p_api_version,
2839 p_init_msg_list => OKC_API.G_FALSE,
2840 x_return_status => x_return_status,
2841 x_msg_count => x_msg_count,
2842 x_msg_data => x_msg_data,
2843 p_okc_rule_defs_v_tbl => p_okc_rule_defs_v_tbl,
2844 x_okc_rule_defs_v_tbl => x_okc_rule_defs_v_tbl,
2845 px_error_tbl => l_error_tbl);
2846 END IF;
2847 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2848 EXCEPTION
2849 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2850 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2851 (
2852 l_api_name,
2853 G_PKG_NAME,
2854 'OKC_API.G_RET_STS_ERROR',
2855 x_msg_count,
2856 x_msg_data,
2857 '_PVT'
2858 );
2859 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2861 (
2862 l_api_name,
2863 G_PKG_NAME,
2864 'OKC_API.G_RET_STS_UNEXP_ERROR',
2865 x_msg_count,
2866 x_msg_data,
2867 '_PVT'
2868 );
2869 WHEN OTHERS THEN
2870 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2871 (
2872 l_api_name,
2873 G_PKG_NAME,
2874 'OTHERS',
2875 x_msg_count,
2876 x_msg_data,
2877 '_PVT'
2878 );
2879 END update_row;
2880
2881 ---------------------------------------------------------------------------
2882 -- PROCEDURE delete_row
2883 ---------------------------------------------------------------------------
2884 ------------------------------------
2885 -- delete_row for:OKC_RULE_DEFS_B --
2886 ------------------------------------
2887 PROCEDURE delete_row(
2888 p_init_msg_list IN VARCHAR2,
2889 x_return_status OUT NOCOPY VARCHAR2,
2890 x_msg_count OUT NOCOPY NUMBER,
2891 x_msg_data OUT NOCOPY VARCHAR2,
2892 p_okc_rule_defs_b_rec IN okc_rule_defs_b_rec_type) IS
2893
2894 l_api_version CONSTANT NUMBER := 1;
2895 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2896 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2897 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type := p_okc_rule_defs_b_rec;
2898 l_row_notfound BOOLEAN := TRUE;
2899 BEGIN
2900 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2901 p_init_msg_list,
2902 '_PVT',
2903 x_return_status);
2904 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2905 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2906 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2907 RAISE OKC_API.G_EXCEPTION_ERROR;
2908 END IF;
2909
2910 DELETE FROM OKC_RULE_DEFS_B
2911 WHERE RULE_CODE = p_okc_rule_defs_b_rec.rule_code;
2912
2913 x_return_status := l_return_status;
2914 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2915 EXCEPTION
2916 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2917 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2918 (
2919 l_api_name,
2920 G_PKG_NAME,
2921 'OKC_API.G_RET_STS_ERROR',
2922 x_msg_count,
2923 x_msg_data,
2924 '_PVT'
2925 );
2926 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2927 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2928 (
2929 l_api_name,
2930 G_PKG_NAME,
2931 'OKC_API.G_RET_STS_UNEXP_ERROR',
2932 x_msg_count,
2933 x_msg_data,
2934 '_PVT'
2935 );
2936 WHEN OTHERS THEN
2937 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2938 (
2939 l_api_name,
2940 G_PKG_NAME,
2941 'OTHERS',
2942 x_msg_count,
2943 x_msg_data,
2944 '_PVT'
2945 );
2946 END delete_row;
2947 -------------------------------------
2948 -- delete_row for:OKC_RULE_DEFS_TL --
2949 -------------------------------------
2950 PROCEDURE delete_row(
2951 p_init_msg_list IN VARCHAR2,
2952 x_return_status OUT NOCOPY VARCHAR2,
2953 x_msg_count OUT NOCOPY NUMBER,
2954 x_msg_data OUT NOCOPY VARCHAR2,
2955 p_okc_rule_defs_tl_rec IN okc_rule_defs_tl_rec_type) IS
2956
2957 l_api_version CONSTANT NUMBER := 1;
2958 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2959 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2960 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type := p_okc_rule_defs_tl_rec;
2961 l_row_notfound BOOLEAN := TRUE;
2962 BEGIN
2966 x_return_status);
2963 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2964 p_init_msg_list,
2965 '_PVT',
2967 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 END IF;
2972
2973 DELETE FROM OKC_RULE_DEFS_TL
2974 WHERE RULE_CODE = p_okc_rule_defs_tl_rec.rule_code;
2975
2976 x_return_status := l_return_status;
2977 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2978 EXCEPTION
2979 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2980 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2981 (
2982 l_api_name,
2983 G_PKG_NAME,
2984 'OKC_API.G_RET_STS_ERROR',
2985 x_msg_count,
2986 x_msg_data,
2987 '_PVT'
2988 );
2989 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2990 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2991 (
2992 l_api_name,
2993 G_PKG_NAME,
2994 'OKC_API.G_RET_STS_UNEXP_ERROR',
2995 x_msg_count,
2996 x_msg_data,
2997 '_PVT'
2998 );
2999 WHEN OTHERS THEN
3000 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3001 (
3002 l_api_name,
3003 G_PKG_NAME,
3004 'OTHERS',
3005 x_msg_count,
3006 x_msg_data,
3007 '_PVT'
3008 );
3009 END delete_row;
3010 ------------------------------------
3011 -- delete_row for:OKC_RULE_DEFS_V --
3012 ------------------------------------
3013 PROCEDURE delete_row(
3014 p_api_version IN NUMBER,
3015 p_init_msg_list IN VARCHAR2,
3016 x_return_status OUT NOCOPY VARCHAR2,
3017 x_msg_count OUT NOCOPY NUMBER,
3018 x_msg_data OUT NOCOPY VARCHAR2,
3019 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type) IS
3020
3021 l_api_version CONSTANT NUMBER := 1;
3022 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3024 l_okc_rule_defs_v_rec okc_rule_defs_v_rec_type := p_okc_rule_defs_v_rec;
3025 l_okc_rule_defs_tl_rec okc_rule_defs_tl_rec_type;
3026 l_okc_rule_defs_b_rec okc_rule_defs_b_rec_type;
3027 BEGIN
3028 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3029 G_PKG_NAME,
3030 p_init_msg_list,
3031 l_api_version,
3032 p_api_version,
3033 '_PVT',
3034 x_return_status);
3035 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3038 RAISE OKC_API.G_EXCEPTION_ERROR;
3039 END IF;
3040 -----------------------------------------
3041 -- Move VIEW record to "Child" records --
3042 -----------------------------------------
3043 migrate(l_okc_rule_defs_v_rec, l_okc_rule_defs_tl_rec);
3044 migrate(l_okc_rule_defs_v_rec, l_okc_rule_defs_b_rec);
3045 -----------------------------------------------
3046 -- Call the DELETE_ROW for each child record --
3047 -----------------------------------------------
3048 delete_row(
3049 p_init_msg_list,
3050 l_return_status,
3051 x_msg_count,
3052 x_msg_data,
3053 l_okc_rule_defs_tl_rec
3054 );
3055 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3056 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3057 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3058 RAISE OKC_API.G_EXCEPTION_ERROR;
3059 END IF;
3060 delete_row(
3061 p_init_msg_list,
3062 l_return_status,
3063 x_msg_count,
3064 x_msg_data,
3065 l_okc_rule_defs_b_rec
3066 );
3067 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3068 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3069 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3070 RAISE OKC_API.G_EXCEPTION_ERROR;
3071 END IF;
3072 x_return_status := l_return_status;
3073 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3074 EXCEPTION
3075 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3076 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3077 (
3078 l_api_name,
3079 G_PKG_NAME,
3080 'OKC_API.G_RET_STS_ERROR',
3081 x_msg_count,
3082 x_msg_data,
3083 '_PVT'
3084 );
3085 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3086 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3087 (
3088 l_api_name,
3089 G_PKG_NAME,
3090 'OKC_API.G_RET_STS_UNEXP_ERROR',
3091 x_msg_count,
3092 x_msg_data,
3093 '_PVT'
3094 );
3095 WHEN OTHERS THEN
3096 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3097 (
3098 l_api_name,
3099 G_PKG_NAME,
3103 '_PVT'
3100 'OTHERS',
3101 x_msg_count,
3102 x_msg_data,
3104 );
3105 END delete_row;
3106 -----------------------------------------------
3107 -- PL/SQL TBL delete_row for:OKC_RULE_DEFS_V --
3108 -----------------------------------------------
3109 PROCEDURE delete_row(
3110 p_api_version IN NUMBER,
3111 p_init_msg_list IN VARCHAR2,
3112 x_return_status OUT NOCOPY VARCHAR2,
3113 x_msg_count OUT NOCOPY NUMBER,
3114 x_msg_data OUT NOCOPY VARCHAR2,
3115 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type,
3116 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
3117
3118 l_api_version CONSTANT NUMBER := 1;
3119 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3120 i NUMBER := 0;
3121 BEGIN
3122 OKC_API.init_msg_list(p_init_msg_list);
3123 -- Make sure PL/SQL table has records in it before passing
3124 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
3125 i := p_okc_rule_defs_v_tbl.FIRST;
3126 LOOP
3127 DECLARE
3128 l_error_rec OKC_API.ERROR_REC_TYPE;
3129 BEGIN
3130 l_error_rec.api_name := l_api_name;
3131 l_error_rec.api_package := G_PKG_NAME;
3132 l_error_rec.idx := i;
3133 delete_row (
3134 p_api_version => p_api_version,
3135 p_init_msg_list => OKC_API.G_FALSE,
3136 x_return_status => l_error_rec.error_type,
3137 x_msg_count => l_error_rec.msg_count,
3138 x_msg_data => l_error_rec.msg_data,
3139 p_okc_rule_defs_v_rec => p_okc_rule_defs_v_tbl(i));
3140 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3141 l_error_rec.sqlcode := SQLCODE;
3142 load_error_tbl(l_error_rec, px_error_tbl);
3143 ELSE
3144 x_msg_count := l_error_rec.msg_count;
3145 x_msg_data := l_error_rec.msg_data;
3146 END IF;
3147 EXCEPTION
3148 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3149 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3150 l_error_rec.sqlcode := SQLCODE;
3151 load_error_tbl(l_error_rec, px_error_tbl);
3152 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3153 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3154 l_error_rec.sqlcode := SQLCODE;
3155 load_error_tbl(l_error_rec, px_error_tbl);
3156 WHEN OTHERS THEN
3157 l_error_rec.error_type := 'OTHERS';
3158 l_error_rec.sqlcode := SQLCODE;
3159 load_error_tbl(l_error_rec, px_error_tbl);
3160 END;
3161 EXIT WHEN (i = p_okc_rule_defs_v_tbl.LAST);
3162 i := p_okc_rule_defs_v_tbl.NEXT(i);
3163 END LOOP;
3164 END IF;
3168 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3165 -- Loop through the error_tbl to find the error with the highest severity
3166 -- and return it.
3167 x_return_status := find_highest_exception(px_error_tbl);
3169 EXCEPTION
3170 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3171 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3172 (
3173 l_api_name,
3174 G_PKG_NAME,
3175 'OKC_API.G_RET_STS_ERROR',
3176 x_msg_count,
3177 x_msg_data,
3178 '_PVT'
3179 );
3180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3181 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3182 (
3183 l_api_name,
3184 G_PKG_NAME,
3185 'OKC_API.G_RET_STS_UNEXP_ERROR',
3186 x_msg_count,
3187 x_msg_data,
3188 '_PVT'
3189 );
3190 WHEN OTHERS THEN
3191 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3192 (
3193 l_api_name,
3194 G_PKG_NAME,
3195 'OTHERS',
3196 x_msg_count,
3197 x_msg_data,
3198 '_PVT'
3199 );
3200 END delete_row;
3201
3202 -----------------------------------------------
3203 -- PL/SQL TBL delete_row for:OKC_RULE_DEFS_V --
3204 -----------------------------------------------
3205 PROCEDURE delete_row(
3206 p_api_version IN NUMBER,
3207 p_init_msg_list IN VARCHAR2,
3208 x_return_status OUT NOCOPY VARCHAR2,
3209 x_msg_count OUT NOCOPY NUMBER,
3210 x_msg_data OUT NOCOPY VARCHAR2,
3211 p_okc_rule_defs_v_tbl IN okc_rule_defs_v_tbl_type) IS
3212
3213 l_api_version CONSTANT NUMBER := 1;
3214 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3215 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3216 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3217 BEGIN
3218 OKC_API.init_msg_list(p_init_msg_list);
3219 -- Make sure PL/SQL table has records in it before passing
3220 IF (p_okc_rule_defs_v_tbl.COUNT > 0) THEN
3221 delete_row (
3222 p_api_version => p_api_version,
3223 p_init_msg_list => OKC_API.G_FALSE,
3224 x_return_status => x_return_status,
3225 x_msg_count => x_msg_count,
3226 x_msg_data => x_msg_data,
3227 p_okc_rule_defs_v_tbl => p_okc_rule_defs_v_tbl,
3228 px_error_tbl => l_error_tbl);
3229 END IF;
3230 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3231 EXCEPTION
3232 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3233 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3234 (
3235 l_api_name,
3236 G_PKG_NAME,
3237 'OKC_API.G_RET_STS_ERROR',
3238 x_msg_count,
3239 x_msg_data,
3240 '_PVT'
3241 );
3242 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3243 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3244 (
3245 l_api_name,
3246 G_PKG_NAME,
3247 'OKC_API.G_RET_STS_UNEXP_ERROR',
3248 x_msg_count,
3249 x_msg_data,
3250 '_PVT'
3251 );
3252 WHEN OTHERS THEN
3253 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3254 (
3255 l_api_name,
3256 G_PKG_NAME,
3257 'OTHERS',
3258 x_msg_count,
3259 x_msg_data,
3260 '_PVT'
3261 );
3262 END delete_row;
3263
3264 PROCEDURE set_RULE_DEFS (
3265 rule_code IN VARCHAR2
3266 ) IS
3267
3268 CURSOR c1(r_c IN varchar2) IS
3269 SELECT application_id ,descriptive_flexfield_name
3270 FROM okc_rule_defs_b
3271 where rule_code = r_c;
3272
3273 BEGIN
3274 if rule_code is not NULL then
3275 p_rule_code:=rule_code;
3276 open c1(rule_code);
3277 fetch c1
3278 into p_appl_id, p_dff_name;
3279 close c1;
3280 end if;
3281 return;
3282 EXCEPTION
3283 WHEN NO_DATA_FOUND then
3284 p_appl_id:=NULL;
3285 p_dff_name:=NULL;
3286 END set_RULE_DEFS;
3287
3288 function get_appl_id(rule_code IN VARCHAR2)
3289 return number
3290 is
3291 begin
3292 if rule_code is null then
3293 return NULL;
3294 elsif rule_code = p_rule_code then
3295 return p_appl_id;
3296 else
3297 set_rule_defs(rule_code);
3298 return p_appl_id;
3299 end if;
3300 end get_appl_id;
3301
3302 function get_dff_name(rule_code IN VARCHAR2)
3303 return varchar2
3304 is
3305 begin
3306 if rule_code is null then
3307 return NULL;
3308 elsif rule_code = p_rule_code then
3309 return p_dff_name;
3310 else
3311 set_rule_defs(rule_code);
3312 return p_dff_name;
3313 end if;
3314 end get_dff_name;
3315
3316 FUNCTION Validate_dff (
3317 p_okc_rule_defs_v_rec IN okc_rule_defs_v_rec_type
3318 ) RETURN VARCHAR2 IS
3319
3320 cursor c1(rule_code IN varchar2,
3321 appl_id IN number,
3322 dff_name IN varchar2) is
3323 SELECT
3324 'Y'
3325 FROM
3326 FND_DESCRIPTIVE_FLEXS B,
3327 FND_DESCR_FLEX_CONTEXTS A
3328 WHERE
3329 A.DESCRIPTIVE_FLEX_CONTEXT_CODE = rule_code
3330 and (A.APPLICATION_ID = B.APPLICATION_ID
3331 and B.APPLICATION_ID = appl_id)
3332 and (A.DESCRIPTIVE_FLEXFIELD_NAME = B.DESCRIPTIVE_FLEXFIELD_NAME
3333 and B.DESCRIPTIVE_FLEXFIELD_NAME= dff_name);
3334
3335 enabled_flag varchar2(3):='N';
3336
3337 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3338
3339 BEGIN
3340
3341 open c1(
3342 p_okc_rule_defs_v_rec.rule_code,
3343 p_okc_rule_defs_v_rec.application_id,
3344 p_okc_rule_defs_v_rec.descriptive_flexfield_name);
3345 fetch c1 into enabled_flag;
3346 close c1;
3347 if enabled_flag <> 'Y' then l_return_status := OKC_API.G_RET_STS_ERROR;
3348 end if;
3349 RETURN (l_return_status);
3350
3351 EXCEPTION
3352 when NO_DATA_FOUND then
3353 l_return_status := OKC_API.G_RET_STS_ERROR;
3354 close c1;
3355 return(l_return_status);
3356 WHEN OTHERS THEN
3357 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3358 close c1;
3359 return(l_return_status);
3360 END Validate_dff;
3361
3362 END OKC_RLD_PVT;