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