[Home] [Help]
PACKAGE BODY: APPS.OKC_PDF_PVT
Source
1 PACKAGE BODY OKC_PDF_PVT AS
2 /* $Header: OKCSPDFB.pls 120.0 2005/05/25 22:40:58 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_DEFS_TL T
48 WHERE NOT EXISTS (
49 SELECT NULL
50 FROM OKC_PROCESS_DEFS_B B
51 WHERE B.ID = T.ID
52 );
53
54 UPDATE OKC_PROCESS_DEFS_TL T SET (
55 NAME,
56 DESCRIPTION,
57 SHORT_DESCRIPTION,
58 COMMENTS) = (SELECT
59 B.NAME,
60 B.DESCRIPTION,
61 B.SHORT_DESCRIPTION,
62 B.COMMENTS
63 FROM OKC_PROCESS_DEFS_TL B
64 WHERE B.ID = T.ID
65 AND B.LANGUAGE = T.SOURCE_LANG)
66 WHERE (
67 T.ID,
68 T.LANGUAGE)
69 IN (SELECT
70 SUBT.ID,
71 SUBT.LANGUAGE
72 FROM OKC_PROCESS_DEFS_TL SUBB, OKC_PROCESS_DEFS_TL SUBT
73 WHERE SUBB.ID = SUBT.ID
74 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
75 AND (SUBB.NAME <> SUBT.NAME
76 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
77 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
78 OR SUBB.COMMENTS <> SUBT.COMMENTS
79 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
80 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
81 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
82 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
83 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
84 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
85 ));
86
87 INSERT INTO OKC_PROCESS_DEFS_TL (
88 ID,
89 LANGUAGE,
90 SOURCE_LANG,
91 SFWT_FLAG,
92 NAME,
93 DESCRIPTION,
94 SHORT_DESCRIPTION,
95 COMMENTS,
96 CREATED_BY,
97 CREATION_DATE,
98 LAST_UPDATED_BY,
99 LAST_UPDATE_DATE,
100 LAST_UPDATE_LOGIN)
101 SELECT
102 B.ID,
103 L.LANGUAGE_CODE,
104 B.SOURCE_LANG,
105 B.SFWT_FLAG,
106 B.NAME,
107 B.DESCRIPTION,
108 B.SHORT_DESCRIPTION,
109 B.COMMENTS,
110 B.CREATED_BY,
111 B.CREATION_DATE,
112 B.LAST_UPDATED_BY,
113 B.LAST_UPDATE_DATE,
114 B.LAST_UPDATE_LOGIN
115 FROM OKC_PROCESS_DEFS_TL B, FND_LANGUAGES L
116 WHERE L.INSTALLED_FLAG IN ('I', 'B')
117 AND B.LANGUAGE = USERENV('LANG')
118 AND NOT EXISTS(
119 SELECT NULL
120 FROM OKC_PROCESS_DEFS_TL T
121 WHERE T.ID = B.ID
122 AND T.LANGUAGE = L.LANGUAGE_CODE
123 );
124
125 END add_language;
126
127 ---------------------------------------------------------------------------
128 -- FUNCTION get_rec for: OKC_PROCESS_DEFS_TL
129 ---------------------------------------------------------------------------
130 FUNCTION get_rec (
131 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
132 x_no_data_found OUT NOCOPY BOOLEAN
133 ) RETURN okc_process_defs_tl_rec_type IS
134 CURSOR okc_process_defs_tl_pk_csr (p_id IN NUMBER,
135 p_language IN VARCHAR2) IS
136 SELECT
137 ID,
138 LANGUAGE,
139 SOURCE_LANG,
140 SFWT_FLAG,
141 NAME,
142 DESCRIPTION,
143 SHORT_DESCRIPTION,
144 COMMENTS,
145 CREATED_BY,
146 CREATION_DATE,
147 LAST_UPDATED_BY,
148 LAST_UPDATE_DATE,
149 LAST_UPDATE_LOGIN
150 FROM Okc_Process_Defs_Tl
151 WHERE okc_process_defs_tl.id = p_id
152 AND okc_process_defs_tl.language = p_language;
153 l_okc_process_defs_tl_pk okc_process_defs_tl_pk_csr%ROWTYPE;
154 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
155 BEGIN
156 x_no_data_found := TRUE;
157 -- Get current database values
158 OPEN okc_process_defs_tl_pk_csr (p_okc_process_defs_tl_rec.id,
159 p_okc_process_defs_tl_rec.language);
160 FETCH okc_process_defs_tl_pk_csr INTO
161 l_okc_process_defs_tl_rec.ID,
162 l_okc_process_defs_tl_rec.LANGUAGE,
163 l_okc_process_defs_tl_rec.SOURCE_LANG,
164 l_okc_process_defs_tl_rec.SFWT_FLAG,
165 l_okc_process_defs_tl_rec.NAME,
166 l_okc_process_defs_tl_rec.DESCRIPTION,
167 l_okc_process_defs_tl_rec.SHORT_DESCRIPTION,
168 l_okc_process_defs_tl_rec.COMMENTS,
169 l_okc_process_defs_tl_rec.CREATED_BY,
170 l_okc_process_defs_tl_rec.CREATION_DATE,
171 l_okc_process_defs_tl_rec.LAST_UPDATED_BY,
172 l_okc_process_defs_tl_rec.LAST_UPDATE_DATE,
173 l_okc_process_defs_tl_rec.LAST_UPDATE_LOGIN;
174 x_no_data_found := okc_process_defs_tl_pk_csr%NOTFOUND;
175 CLOSE okc_process_defs_tl_pk_csr;
176 RETURN(l_okc_process_defs_tl_rec);
177 END get_rec;
178
179 FUNCTION get_rec (
180 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type
181 ) RETURN okc_process_defs_tl_rec_type IS
182 l_row_notfound BOOLEAN := TRUE;
183 BEGIN
184 RETURN(get_rec(p_okc_process_defs_tl_rec, l_row_notfound));
185 END get_rec;
186 ---------------------------------------------------------------------------
187 -- FUNCTION get_rec for: OKC_PROCESS_DEFS_B
188 ---------------------------------------------------------------------------
189 FUNCTION get_rec (
190 p_pdf_rec IN pdf_rec_type,
191 x_no_data_found OUT NOCOPY BOOLEAN
192 ) RETURN pdf_rec_type IS
193 CURSOR okc_process_defs_b_pk_csr (p_id IN NUMBER) IS
194 SELECT
195 ID,
196 PDF_TYPE,
197 OBJECT_VERSION_NUMBER,
198 CREATED_BY,
199 USAGE,
200 CREATION_DATE,
201 BEGIN_DATE,
202 LAST_UPDATED_BY,
203 LAST_UPDATE_DATE,
204 WF_NAME,
205 WF_PROCESS_NAME,
206 PROCEDURE_NAME,
207 PACKAGE_NAME,
208 LAST_UPDATE_LOGIN,
209 END_DATE,
210 ATTRIBUTE_CATEGORY,
211 ATTRIBUTE1,
212 ATTRIBUTE2,
213 ATTRIBUTE3,
214 ATTRIBUTE4,
215 ATTRIBUTE5,
216 ATTRIBUTE6,
217 ATTRIBUTE7,
218 ATTRIBUTE8,
219 ATTRIBUTE9,
220 ATTRIBUTE10,
221 ATTRIBUTE11,
222 ATTRIBUTE12,
223 ATTRIBUTE13,
224 ATTRIBUTE14,
225 ATTRIBUTE15,
226 APPLICATION_ID,
227 SEEDED_FLAG,
228 MESSAGE_NAME,
229 SCRIPT_NAME
230 FROM Okc_Process_Defs_B
231 WHERE okc_process_defs_b.id = p_id;
232 l_okc_process_defs_b_pk okc_process_defs_b_pk_csr%ROWTYPE;
233 l_pdf_rec pdf_rec_type;
234 BEGIN
235 x_no_data_found := TRUE;
236 -- Get current database values
237 OPEN okc_process_defs_b_pk_csr (p_pdf_rec.id);
238 FETCH okc_process_defs_b_pk_csr INTO
239 l_pdf_rec.ID,
240 l_pdf_rec.PDF_TYPE,
241 l_pdf_rec.OBJECT_VERSION_NUMBER,
242 l_pdf_rec.CREATED_BY,
243 l_pdf_rec.USAGE,
244 l_pdf_rec.CREATION_DATE,
245 l_pdf_rec.BEGIN_DATE,
246 l_pdf_rec.LAST_UPDATED_BY,
247 l_pdf_rec.LAST_UPDATE_DATE,
248 l_pdf_rec.WF_NAME,
249 l_pdf_rec.WF_PROCESS_NAME,
250 l_pdf_rec.PROCEDURE_NAME,
251 l_pdf_rec.PACKAGE_NAME,
252 l_pdf_rec.LAST_UPDATE_LOGIN,
253 l_pdf_rec.END_DATE,
254 l_pdf_rec.ATTRIBUTE_CATEGORY,
255 l_pdf_rec.ATTRIBUTE1,
256 l_pdf_rec.ATTRIBUTE2,
257 l_pdf_rec.ATTRIBUTE3,
258 l_pdf_rec.ATTRIBUTE4,
259 l_pdf_rec.ATTRIBUTE5,
260 l_pdf_rec.ATTRIBUTE6,
261 l_pdf_rec.ATTRIBUTE7,
262 l_pdf_rec.ATTRIBUTE8,
263 l_pdf_rec.ATTRIBUTE9,
264 l_pdf_rec.ATTRIBUTE10,
265 l_pdf_rec.ATTRIBUTE11,
266 l_pdf_rec.ATTRIBUTE12,
267 l_pdf_rec.ATTRIBUTE13,
268 l_pdf_rec.ATTRIBUTE14,
269 l_pdf_rec.ATTRIBUTE15,
270 l_pdf_rec.APPLICATION_ID,
271 l_pdf_rec.SEEDED_FLAG,
272 l_pdf_rec.MESSAGE_NAME,
273 l_pdf_rec.SCRIPT_NAME;
274 x_no_data_found := okc_process_defs_b_pk_csr%NOTFOUND;
275 CLOSE okc_process_defs_b_pk_csr;
276 RETURN(l_pdf_rec);
277 END get_rec;
278
279 FUNCTION get_rec (
280 p_pdf_rec IN pdf_rec_type
281 ) RETURN pdf_rec_type IS
282 l_row_notfound BOOLEAN := TRUE;
283 BEGIN
284 RETURN(get_rec(p_pdf_rec, l_row_notfound));
285 END get_rec;
286 ---------------------------------------------------------------------------
287 -- FUNCTION get_rec for: OKC_PROCESS_DEFS_V
288 ---------------------------------------------------------------------------
289 FUNCTION get_rec (
290 p_pdfv_rec IN pdfv_rec_type,
291 x_no_data_found OUT NOCOPY BOOLEAN
292 ) RETURN pdfv_rec_type IS
293 CURSOR okc_pdfv_pk_csr (p_id IN NUMBER) IS
294 SELECT
295 ID,
296 OBJECT_VERSION_NUMBER,
297 SFWT_FLAG,
298 DESCRIPTION,
299 SHORT_DESCRIPTION,
300 COMMENTS,
301 USAGE,
302 NAME,
303 WF_NAME,
304 WF_PROCESS_NAME,
305 PROCEDURE_NAME,
306 PACKAGE_NAME,
307 PDF_TYPE,
308 APPLICATION_ID,
309 SEEDED_FLAG,
310 ATTRIBUTE_CATEGORY,
311 ATTRIBUTE1,
312 ATTRIBUTE2,
313 ATTRIBUTE3,
314 ATTRIBUTE4,
315 ATTRIBUTE5,
316 ATTRIBUTE6,
317 ATTRIBUTE7,
318 ATTRIBUTE8,
319 ATTRIBUTE9,
320 ATTRIBUTE10,
321 ATTRIBUTE11,
322 ATTRIBUTE12,
323 ATTRIBUTE13,
324 ATTRIBUTE14,
325 ATTRIBUTE15,
326 CREATED_BY,
327 BEGIN_DATE,
328 END_DATE,
329 CREATION_DATE,
330 LAST_UPDATED_BY,
331 LAST_UPDATE_DATE,
332 LAST_UPDATE_LOGIN,
333 MESSAGE_NAME,
334 SCRIPT_NAME
335 FROM Okc_Process_Defs_V
336 WHERE okc_process_defs_v.id = p_id;
337 l_okc_pdfv_pk okc_pdfv_pk_csr%ROWTYPE;
338 l_pdfv_rec pdfv_rec_type;
339 BEGIN
340 x_no_data_found := TRUE;
341 -- Get current database values
342 OPEN okc_pdfv_pk_csr (p_pdfv_rec.id);
343 FETCH okc_pdfv_pk_csr INTO
344 l_pdfv_rec.ID,
345 l_pdfv_rec.OBJECT_VERSION_NUMBER,
346 l_pdfv_rec.SFWT_FLAG,
347 l_pdfv_rec.DESCRIPTION,
348 l_pdfv_rec.SHORT_DESCRIPTION,
349 l_pdfv_rec.COMMENTS,
350 l_pdfv_rec.USAGE,
351 l_pdfv_rec.NAME,
352 l_pdfv_rec.WF_NAME,
353 l_pdfv_rec.WF_PROCESS_NAME,
354 l_pdfv_rec.PROCEDURE_NAME,
355 l_pdfv_rec.PACKAGE_NAME,
356 l_pdfv_rec.PDF_TYPE,
357 l_pdfv_rec.APPLICATION_ID,
358 l_pdfv_rec.SEEDED_FLAG,
359 l_pdfv_rec.ATTRIBUTE_CATEGORY,
360 l_pdfv_rec.ATTRIBUTE1,
361 l_pdfv_rec.ATTRIBUTE2,
362 l_pdfv_rec.ATTRIBUTE3,
363 l_pdfv_rec.ATTRIBUTE4,
364 l_pdfv_rec.ATTRIBUTE5,
365 l_pdfv_rec.ATTRIBUTE6,
366 l_pdfv_rec.ATTRIBUTE7,
367 l_pdfv_rec.ATTRIBUTE8,
368 l_pdfv_rec.ATTRIBUTE9,
369 l_pdfv_rec.ATTRIBUTE10,
370 l_pdfv_rec.ATTRIBUTE11,
371 l_pdfv_rec.ATTRIBUTE12,
372 l_pdfv_rec.ATTRIBUTE13,
373 l_pdfv_rec.ATTRIBUTE14,
374 l_pdfv_rec.ATTRIBUTE15,
375 l_pdfv_rec.CREATED_BY,
376 l_pdfv_rec.BEGIN_DATE,
377 l_pdfv_rec.END_DATE,
378 l_pdfv_rec.CREATION_DATE,
379 l_pdfv_rec.LAST_UPDATED_BY,
380 l_pdfv_rec.LAST_UPDATE_DATE,
381 l_pdfv_rec.LAST_UPDATE_LOGIN,
382 l_pdfv_rec.MESSAGE_NAME,
383 l_pdfv_rec.SCRIPT_NAME;
384 x_no_data_found := okc_pdfv_pk_csr%NOTFOUND;
385 CLOSE okc_pdfv_pk_csr;
386 RETURN(l_pdfv_rec);
387 END get_rec;
388
389 FUNCTION get_rec (
390 p_pdfv_rec IN pdfv_rec_type
391 ) RETURN pdfv_rec_type IS
392 l_row_notfound BOOLEAN := TRUE;
393 BEGIN
394 RETURN(get_rec(p_pdfv_rec, l_row_notfound));
395 END get_rec;
396
397 --------------------------------------------------------
398 -- FUNCTION null_out_defaults for: OKC_PROCESS_DEFS_V --
399 --------------------------------------------------------
400 FUNCTION null_out_defaults (
401 p_pdfv_rec IN pdfv_rec_type
402 ) RETURN pdfv_rec_type IS
403 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
404 BEGIN
405 IF (l_pdfv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
406 l_pdfv_rec.object_version_number := NULL;
407 END IF;
408 IF (l_pdfv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
409 l_pdfv_rec.sfwt_flag := NULL;
410 END IF;
411 IF (l_pdfv_rec.description = OKC_API.G_MISS_CHAR) THEN
412 l_pdfv_rec.description := NULL;
413 END IF;
414 IF (l_pdfv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
415 l_pdfv_rec.short_description := NULL;
416 END IF;
417 IF (l_pdfv_rec.comments = OKC_API.G_MISS_CHAR) THEN
418 l_pdfv_rec.comments := NULL;
419 END IF;
420 IF (l_pdfv_rec.usage = OKC_API.G_MISS_CHAR) THEN
421 l_pdfv_rec.usage := NULL;
422 END IF;
423 IF (l_pdfv_rec.name = OKC_API.G_MISS_CHAR) THEN
424 l_pdfv_rec.name := NULL;
425 END IF;
426 IF (l_pdfv_rec.wf_name = OKC_API.G_MISS_CHAR) THEN
427 l_pdfv_rec.wf_name := NULL;
428 END IF;
429 IF (l_pdfv_rec.wf_process_name = OKC_API.G_MISS_CHAR) THEN
430 l_pdfv_rec.wf_process_name := NULL;
431 END IF;
432 IF (l_pdfv_rec.procedure_name = OKC_API.G_MISS_CHAR) THEN
433 l_pdfv_rec.procedure_name := NULL;
434 END IF;
435 IF (l_pdfv_rec.package_name = OKC_API.G_MISS_CHAR) THEN
436 l_pdfv_rec.package_name := NULL;
437 END IF;
438 IF (l_pdfv_rec.pdf_type = OKC_API.G_MISS_CHAR) THEN
439 l_pdfv_rec.pdf_type := NULL;
440 END IF;
441 IF (l_pdfv_rec.application_id = OKC_API.G_MISS_NUM) THEN
442 l_pdfv_rec.application_id := NULL;
443 END IF;
444 IF (l_pdfv_rec.seeded_flag = OKC_API.G_MISS_CHAR) THEN
445 l_pdfv_rec.seeded_flag := NULL;
446 END IF;
447 IF (l_pdfv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
448 l_pdfv_rec.attribute_category := NULL;
449 END IF;
450 IF (l_pdfv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
451 l_pdfv_rec.attribute1 := NULL;
452 END IF;
453 IF (l_pdfv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
454 l_pdfv_rec.attribute2 := NULL;
455 END IF;
456 IF (l_pdfv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
457 l_pdfv_rec.attribute3 := NULL;
458 END IF;
459 IF (l_pdfv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
460 l_pdfv_rec.attribute4 := NULL;
461 END IF;
462 IF (l_pdfv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
463 l_pdfv_rec.attribute5 := NULL;
464 END IF;
465 IF (l_pdfv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
466 l_pdfv_rec.attribute6 := NULL;
467 END IF;
468 IF (l_pdfv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
469 l_pdfv_rec.attribute7 := NULL;
470 END IF;
471 IF (l_pdfv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
472 l_pdfv_rec.attribute8 := NULL;
473 END IF;
474 IF (l_pdfv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
475 l_pdfv_rec.attribute9 := NULL;
476 END IF;
477 IF (l_pdfv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
478 l_pdfv_rec.attribute10 := NULL;
479 END IF;
480 IF (l_pdfv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
481 l_pdfv_rec.attribute11 := NULL;
482 END IF;
483 IF (l_pdfv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
484 l_pdfv_rec.attribute12 := NULL;
485 END IF;
486 IF (l_pdfv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
487 l_pdfv_rec.attribute13 := NULL;
488 END IF;
489 IF (l_pdfv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
490 l_pdfv_rec.attribute14 := NULL;
491 END IF;
492 IF (l_pdfv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
493 l_pdfv_rec.attribute15 := NULL;
494 END IF;
495 IF (l_pdfv_rec.created_by = OKC_API.G_MISS_NUM) THEN
496 l_pdfv_rec.created_by := NULL;
497 END IF;
498 IF (l_pdfv_rec.begin_date = OKC_API.G_MISS_DATE) THEN
499 l_pdfv_rec.begin_date := NULL;
500 END IF;
501 IF (l_pdfv_rec.end_date = OKC_API.G_MISS_DATE) THEN
502 l_pdfv_rec.end_date := NULL;
503 END IF;
504 IF (l_pdfv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
505 l_pdfv_rec.creation_date := NULL;
506 END IF;
507 IF (l_pdfv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
508 l_pdfv_rec.last_updated_by := NULL;
509 END IF;
510 IF (l_pdfv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
511 l_pdfv_rec.last_update_date := NULL;
512 END IF;
513 IF (l_pdfv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
514 l_pdfv_rec.last_update_login := NULL;
515 END IF;
516 IF (l_pdfv_rec.message_name = OKC_API.G_MISS_CHAR) THEN
517 l_pdfv_rec.message_name := NULL;
518 END IF;
519 IF (l_pdfv_rec.script_name = OKC_API.G_MISS_CHAR) THEN
520 l_pdfv_rec.script_name := NULL;
521 END IF;
522 RETURN(l_pdfv_rec);
523 END null_out_defaults;
524
525 /******** Commented out nocopy generated code in favor of hand written code
526 ---------------------------------------------------------------------------
527 -- PROCEDURE Validate_Attributes
528 ---------------------------------------------------------------------------
529 ------------------------------------------------
530 -- Validate_Attributes for:OKC_PROCESS_DEFS_V --
531 ------------------------------------------------
532 FUNCTION Validate_Attributes (
533 p_pdfv_rec IN pdfv_rec_type
534 ) RETURN VARCHAR2 IS
535 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
536 BEGIN
537 IF p_pdfv_rec.id = OKC_API.G_MISS_NUM OR
538 p_pdfv_rec.id IS NULL
539 THEN
540 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
541 l_return_status := OKC_API.G_RET_STS_ERROR;
542 ELSIF p_pdfv_rec.object_version_number = OKC_API.G_MISS_NUM OR
543 p_pdfv_rec.object_version_number IS NULL
544 THEN
545 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
546 l_return_status := OKC_API.G_RET_STS_ERROR;
547 ELSIF p_pdfv_rec.usage = OKC_API.G_MISS_CHAR OR
548 p_pdfv_rec.usage IS NULL
549 THEN
550 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'usage');
551 l_return_status := OKC_API.G_RET_STS_ERROR;
552 ELSIF p_pdfv_rec.name = OKC_API.G_MISS_CHAR OR
553 p_pdfv_rec.name IS NULL
554 THEN
555 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'name');
556 l_return_status := OKC_API.G_RET_STS_ERROR;
557 ELSIF p_pdfv_rec.pdf_type = OKC_API.G_MISS_CHAR OR
558 p_pdfv_rec.pdf_type IS NULL
559 THEN
560 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'pdf_type');
561 l_return_status := OKC_API.G_RET_STS_ERROR;
562 ELSIF p_pdfv_rec.begin_date = OKC_API.G_MISS_DATE OR
563 p_pdfv_rec.begin_date IS NULL
564 THEN
565 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'begin_date');
566 l_return_status := OKC_API.G_RET_STS_ERROR;
567 END IF;
568 RETURN(l_return_status);
569 END Validate_Attributes;
570
571 ---------------------------------------------------------------------------
572 -- PROCEDURE Validate_Record
573 ---------------------------------------------------------------------------
574 --------------------------------------------
575 -- Validate_Record for:OKC_PROCESS_DEFS_V --
576 --------------------------------------------
577 FUNCTION Validate_Record (
578 p_pdfv_rec IN pdfv_rec_type
579 ) RETURN VARCHAR2 IS
580 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
581 BEGIN
582 RETURN (l_return_status);
583 END Validate_Record;
584
585 ******************** End Generated Code **********************************/
586
587 /******************* Begin Hand Written Code *****************************/
588 ---------------------------------------------------------------------------
589 -- PROCEDURE Validate_Attributes
590 ---------------------------------------------------------------------------
591 ------------------------------------------------
592 -- Validate_Attributes for:OKC_PROCESS_DEFS_V --
593 ------------------------------------------------
594 -- Start of comments
595 -- Procedure Name : validate_object_version_number
596 -- Description : Check if object_version_number is null
597 -- Version : 1.0
598 -- End of comments
599
600 PROCEDURE validate_object_version_number(
601 x_return_status OUT NOCOPY VARCHAR2,
602 p_pdfv_rec IN pdfv_rec_type) IS
603 BEGIN
604 -- initialize return status
605 x_return_status := OKC_API.G_RET_STS_SUCCESS;
606
607 --Check if the data is null
608 IF p_pdfv_rec.object_version_number = OKC_API.G_MISS_NUM OR p_pdfv_rec.object_version_number IS NULL THEN
609 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
610 p_msg_name => g_required_value,
611 p_token1 => g_col_name_token,
612 p_token1_value => 'object_version_number');
613 x_return_status := OKC_API.G_RET_STS_ERROR;
614 raise G_EXCEPTION_HALT_VALIDATION;
615 END IF;
616 EXCEPTION
617 when G_EXCEPTION_HALT_VALIDATION then
618 -- no processing necessary; validation can continue
619 -- with the next column
620 null;
621
622 when OTHERS then
623 -- store SQL error message on message stack for caller
624 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
625 p_msg_name => g_unexpected_error,
626 p_token1 => g_sqlcode_token,
627 p_token1_value => sqlcode,
628 p_token2 => g_sqlerrm_token,
629 p_token2_value => sqlerrm);
630 -- notify caller of an UNEXPECTED error
631 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
632 END validate_object_version_number;
633
634 -- Start of comments
635 -- Procedure Name : validate_sfwt_flag
636 -- Description : Checks if column SFWT_FLAG is 'Y' or 'N' only
637 -- Version : 1.0
638 -- End of comments
639
640 PROCEDURE validate_sfwt_flag(
641 x_return_status OUT NOCOPY VARCHAR2,
642 p_pdfv_rec IN pdfv_rec_type) IS
643 BEGIN
644 -- initialize return status
645 x_return_status := OKC_API.G_RET_STS_SUCCESS;
646
647 --Check if the data is null
648 IF p_pdfv_rec.sfwt_flag = OKC_API.G_MISS_CHAR OR p_pdfv_rec.sfwt_flag IS NULL THEN
649 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
650 p_msg_name => g_required_value,
651 p_token1 => g_col_name_token,
652 p_token1_value => 'sfwt_flag');
653 x_return_status := OKC_API.G_RET_STS_ERROR;
654 raise G_EXCEPTION_HALT_VALIDATION;
655 END IF;
656
657 -- Check if sfwt_flag is Y or N
658 IF UPPER(p_pdfv_rec.sfwt_flag) NOT IN ('Y', 'N') THEN
659 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
660 p_msg_name => g_invalid_value,
661 p_token1 => g_col_name_token,
662 p_token1_value => 'sfwt_flag');
663 x_return_status := OKC_API.G_RET_STS_ERROR;
664 raise G_EXCEPTION_HALT_VALIDATION;
665 END IF;
666
667 --Check if the data is in upper case
668 IF p_pdfv_rec.sfwt_flag <> UPPER(p_pdfv_rec.sfwt_flag) THEN
669 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
670 p_msg_name => g_uppercase_required,
671 p_token1 => g_col_name_token,
672 p_token1_value => 'sfwt_flag');
673 x_return_status := OKC_API.G_RET_STS_ERROR;
674 raise G_EXCEPTION_HALT_VALIDATION;
675 END IF;
676 EXCEPTION
677 when G_EXCEPTION_HALT_VALIDATION then
678 -- no processing necessary; validation can continue
679 -- with the next column
680 null;
681
682 when OTHERS then
683 -- store SQL error message on message stack for caller
684 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
685 p_msg_name => g_unexpected_error,
686 p_token1 => g_sqlcode_token,
687 p_token1_value => sqlcode,
688 p_token2 => g_sqlerrm_token,
689 p_token2_value => sqlerrm);
690 -- notify caller of an UNEXPECTED error
691 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
692 END validate_sfwt_flag;
693
694 -- Start of comments
695 -- Procedure Name : validate_seeded_flag
696 -- Description : Checks if column SEEDED_FLAG is 'Y' or 'N' only
697 -- Version : 1.0
698 -- End of comments
699
700 PROCEDURE validate_seeded_flag(
701 x_return_status OUT NOCOPY VARCHAR2,
702 p_pdfv_rec IN pdfv_rec_type) IS
703 l_y VARCHAR2(1) := 'Y';
704 l_n VARCHAR2(1) := 'N';
705 BEGIN
706 -- initialize return status
707 x_return_status := OKC_API.G_RET_STS_SUCCESS;
708 -- Check if seeded_flag is Y or N
709 IF UPPER(p_pdfv_rec.seeded_flag) NOT IN ('Y', 'N') THEN
710 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
711 p_msg_name => g_invalid_value,
712 p_token1 => g_col_name_token,
713 p_token1_value => 'seeded_flag');
714 x_return_status := OKC_API.G_RET_STS_ERROR;
715 raise G_EXCEPTION_HALT_VALIDATION;
716 END IF;
717
718 --Check if the data is in upper case
719 IF p_pdfv_rec.seeded_flag <> UPPER(p_pdfv_rec.seeded_flag) THEN
720 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
721 p_msg_name => g_uppercase_required,
722 p_token1 => g_col_name_token,
723 p_token1_value => 'seeded_flag');
724 x_return_status := OKC_API.G_RET_STS_ERROR;
725 raise G_EXCEPTION_HALT_VALIDATION;
726 END IF;
727 EXCEPTION
728 when G_EXCEPTION_HALT_VALIDATION then
729 -- no processing necessary; validation can continue
730 -- with the next column
731 null;
732
733 when OTHERS then
734 -- store SQL error message on message stack for caller
735 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
736 p_msg_name => g_unexpected_error,
737 p_token1 => g_sqlcode_token,
738 p_token1_value => sqlcode,
739 p_token2 => g_sqlerrm_token,
740 p_token2_value => sqlerrm);
741 -- notify caller of an UNEXPECTED error
742 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
743 END validate_seeded_flag;
744
745 -- Start of comments
746 -- Procedure Name : validate_application_id
747 -- Description : Checks if application_id exists in fnd_application
748 -- Version : 1.0
749 -- End of comments
750
751 PROCEDURE validate_application_id(
752 x_return_status OUT NOCOPY VARCHAR2,
753 p_pdfv_rec IN pdfv_rec_type) IS
754 Cursor application_id_cur(p_application_id IN NUMBER) IS
755 select '1'
756 from fnd_application
757 where application_id = p_application_id;
758 l_dummy VARCHAR2(1) := '?';
759 BEGIN
760 -- initialize return status
761 x_return_status := OKC_API.G_RET_STS_SUCCESS;
762
763 IF p_pdfv_rec.application_id IS NOT NULL THEN
764 --Check if application id exists in the fnd_application or not
765 OPEN application_id_cur(p_pdfv_rec.application_id);
766 FETCH application_id_cur INTO l_dummy;
767 CLOSE application_id_cur ;
768 IF l_dummy = '?' THEN
769 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
770 p_msg_name => g_invalid_value,
771 p_token1 => g_col_name_token,
772 p_token1_value => 'application_id');
773 x_return_status := OKC_API.G_RET_STS_ERROR;
774 raise G_EXCEPTION_HALT_VALIDATION;
775 END IF;
776 END IF;
777 EXCEPTION
778 when G_EXCEPTION_HALT_VALIDATION then
779 -- no processing necessary; validation can continue
780 -- with the next column
781 null;
782
783 when OTHERS then
784 -- store SQL error message on message stack for caller
785 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
786 p_msg_name => g_unexpected_error,
787 p_token1 => g_sqlcode_token,
788 p_token1_value => sqlcode,
789 p_token2 => g_sqlerrm_token,
790 p_token2_value => sqlerrm);
791 -- notify caller of an UNEXPECTED error
792 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
793 END validate_application_id;
794
795 -- Start of comments
796 -- Procedure Name : validate_usage
797 -- Description : Checks for valid usage and uppercase
798 -- Version : 1.0
799 -- End of comments
800
801 PROCEDURE validate_usage(
802 x_return_status OUT NOCOPY VARCHAR2,
803 p_pdfv_rec IN pdfv_rec_type) IS
804
805 Cursor usage_cur(p_usage IN VARCHAR2) IS
806 select lookup_code
807 from fnd_lookups
808 where lookup_type = 'OKC_PROCESS_USAGE_TYPES'
809 and lookup_code = p_usage;
810 l_usage VARCHAR2(250);
811 BEGIN
812 -- initialize return status
813 x_return_status := OKC_API.G_RET_STS_SUCCESS;
814
815 --Check if the data is null
816 IF p_pdfv_rec.usage = OKC_API.G_MISS_CHAR OR p_pdfv_rec.usage IS NULL THEN
817 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
818 p_msg_name => g_required_value,
819 p_token1 => g_col_name_token,
820 p_token1_value => 'purpose');
821 x_return_status := OKC_API.G_RET_STS_ERROR;
822 raise G_EXCEPTION_HALT_VALIDATION;
823 END IF;
824
825 -- Check for valid usage
826 OPEN usage_cur(p_pdfv_rec.usage);
827 FETCH usage_cur into l_usage;
828 CLOSE usage_cur;
829 IF l_usage IS NULL THEN
830 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
831 p_msg_name => g_required_value,
832 p_token1 => g_col_name_token,
833 p_token1_value => 'purpose');
834 x_return_status := OKC_API.G_RET_STS_ERROR;
835 raise G_EXCEPTION_HALT_VALIDATION;
836 END IF;
837
838 --Check if the data is in upper case
839 IF p_pdfv_rec.usage <> UPPER(p_pdfv_rec.usage) THEN
840 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
841 p_msg_name => g_uppercase_required,
842 p_token1 => g_col_name_token,
843 p_token1_value => 'purpose');
844 x_return_status := OKC_API.G_RET_STS_ERROR;
845 raise G_EXCEPTION_HALT_VALIDATION;
846 END IF;
847 EXCEPTION
848 when G_EXCEPTION_HALT_VALIDATION then
849 -- no processing necessary; validation can continue
850 -- with the next column
851 null;
852
853 when OTHERS then
854 -- store SQL error message on message stack for caller
855 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
856 p_msg_name => g_unexpected_error,
857 p_token1 => g_sqlcode_token,
858 p_token1_value => sqlcode,
859 p_token2 => g_sqlerrm_token,
860 p_token2_value => sqlerrm);
861
862 -- notify caller of an UNEXPECTED error
863 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
864 END validate_usage;
865
866 -- Start of comments
867 -- Procedure Name : validate_name
868 -- Description : Checks for unique name
869 -- Version : 1.0
870 -- End of comments
871
872 PROCEDURE validate_name(
873 x_return_status OUT NOCOPY VARCHAR2,
874 p_pdfv_rec IN pdfv_rec_type) IS
875
876 CURSOR l_unq_cur(p_name VARCHAR2) IS
877 SELECT id FROM OKC_PROCESS_DEFS_V
878 WHERE name = p_name;
879
880 l_id NUMBER := OKC_API.G_MISS_NUM;
881 BEGIN
882 -- initialize return status
883 x_return_status := OKC_API.G_RET_STS_SUCCESS;
884
885 --Check if the data is null
886 IF p_pdfv_rec.name = OKC_API.G_MISS_CHAR OR p_pdfv_rec.name IS NULL THEN
887 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
888 p_msg_name => g_required_value,
889 p_token1 => g_col_name_token,
890 p_token1_value => 'name');
891 x_return_status := OKC_API.G_RET_STS_ERROR;
892 raise G_EXCEPTION_HALT_VALIDATION;
893 END IF;
894
895 --Check if the data is unique
896 --Bug 1699203 - Removed check_unique
897 OPEN l_unq_cur(p_pdfv_rec.name);
898 FETCH l_unq_cur INTO l_id;
899 CLOSE l_unq_cur;
900 If (l_id <> OKC_API.G_MISS_NUM AND l_id <> nvl(p_pdfv_rec.id,0)) THEN
901 x_return_status := OKC_API.G_RET_STS_ERROR;
902 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
903 p_msg_name => 'OKC_DUP_PROCESS_NAME');
904 END IF;
905 /*
906 IF p_pdfv_rec.name IS NOT NULL THEN
907 OKC_UTIL.check_Unique(G_VIEW
908 ,'NAME'
909 ,p_pdfv_rec.name
910 ,p_pdfv_rec.id
911 ,x_return_status);
912 If x_return_status<>OKC_API.G_RET_STS_SUCCESS Then
913 return;
914 End If;
915 END IF;
916 */
917
918 EXCEPTION
919 when G_EXCEPTION_HALT_VALIDATION then
920 -- no processing necessary; validation can continue
921 -- with the next column
922 null;
923
924 when OTHERS then
925 -- store SQL error message on message stack for caller
926 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
927 p_msg_name => g_unexpected_error,
928 p_token1 => g_sqlcode_token,
929 p_token1_value => sqlcode,
930 p_token2 => g_sqlerrm_token,
931 p_token2_value => sqlerrm);
932
933 -- notify caller of an UNEXPECTED error
934 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
935 END validate_name;
936
937 -- Start of comments
938 -- Procedure Name : validate_wf_name
939 -- Description : Checks for valid length, Uniqueness and upper case for column WF_NAME
940 -- Version : 1.0
941 -- End of comments
942
943 PROCEDURE validate_wf_name(
944 x_return_status OUT NOCOPY VARCHAR2,
945 p_pdfv_rec IN pdfv_rec_type) IS
946 BEGIN
947 -- initialize return status
948 x_return_status := OKC_API.G_RET_STS_SUCCESS;
949
950 --Check if the data is in uppercase
951 IF p_pdfv_rec.wf_name <> UPPER(p_pdfv_rec.wf_name) THEN
952 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
953 p_msg_name => g_uppercase_required,
954 p_token1 => g_col_name_token,
955 p_token1_value => 'wf_name');
956 x_return_status := OKC_API.G_RET_STS_ERROR;
957 raise G_EXCEPTION_HALT_VALIDATION;
958 END IF;
959 EXCEPTION
960 when G_EXCEPTION_HALT_VALIDATION then
961 -- no processing necessary; validation can continue
962 -- with the next column
963 null;
964
965 when OTHERS then
966 -- store SQL error message on message stack for caller
967 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
968 p_msg_name => g_unexpected_error,
969 p_token1 => g_sqlcode_token,
970 p_token1_value => sqlcode,
971 p_token2 => g_sqlerrm_token,
972 p_token2_value => sqlerrm);
973
974 -- notify caller of an UNEXPECTED error
975 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
976 END validate_wf_name;
977
978 -- Start of comments
979 -- Procedure Name : validate_wf_process_name
980 -- Description : Checks for valid length, Uniqueness and upper case for column WF_PROCESS_NAME
981 -- Version : 1.0
982 -- End of comments
983
984 PROCEDURE validate_wf_process_name(
985 x_return_status OUT NOCOPY VARCHAR2,
986 p_pdfv_rec IN pdfv_rec_type) IS
987 BEGIN
988 -- initialize return status
989 x_return_status := OKC_API.G_RET_STS_SUCCESS;
990
991 --Check if the data is in upper case
992 IF p_pdfv_rec.wf_process_name <> UPPER(p_pdfv_rec.wf_process_name) THEN
993 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
994 p_msg_name => g_uppercase_required,
995 p_token1 => g_col_name_token,
996 p_token1_value => 'wf_process_name');
997 x_return_status := OKC_API.G_RET_STS_ERROR;
998 RAISE G_EXCEPTION_HALT_VALIDATION;
999 END IF;
1000 EXCEPTION
1001 when G_EXCEPTION_HALT_VALIDATION then
1002 -- no processing necessary; validation can continue
1003 -- with the next column
1004 null;
1005
1006 when OTHERS then
1007 -- store SQL error message on message stack for caller
1008 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1009 p_msg_name => g_unexpected_error,
1010 p_token1 => g_sqlcode_token,
1011 p_token1_value => sqlcode,
1012 p_token2 => g_sqlerrm_token,
1013 p_token2_value => sqlerrm);
1014
1015 -- notify caller of an UNEXPECTED error
1016 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1017 END validate_wf_process_name;
1018
1019 -- Start of comments
1020 -- Procedure Name : validate_procedure_name
1021 -- Description : Checks for valid length, Uniqueness and upper case for column PROCEDURE_NAME
1022 -- Version : 1.0
1023 -- End of comments
1024
1025 PROCEDURE validate_procedure_name(
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 p_pdfv_rec IN pdfv_rec_type) IS
1028
1029 BEGIN
1030 -- initialize return status
1031 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1032
1033 --Check if the data is in upper case
1034 IF p_pdfv_rec.procedure_name <> UPPER(p_pdfv_rec.procedure_name) THEN
1035 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1036 p_msg_name => g_uppercase_required,
1037 p_token1 => g_col_name_token,
1038 p_token1_value => 'procedure_name');
1039 x_return_status := OKC_API.G_RET_STS_ERROR;
1040 RAISE G_EXCEPTION_HALT_VALIDATION;
1041 END IF;
1042 EXCEPTION
1043 when G_EXCEPTION_HALT_VALIDATION then
1044 -- no processing necessary; validation can continue
1045 -- with the next column
1046 null;
1047
1048 when OTHERS then
1049 -- store SQL error message on message stack for caller
1050 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1051 p_msg_name => g_unexpected_error,
1052 p_token1 => g_sqlcode_token,
1053 p_token1_value => sqlcode,
1054 p_token2 => g_sqlerrm_token,
1055 p_token2_value => sqlerrm);
1056 -- notify caller of an UNEXPECTED error
1057 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1058 END validate_procedure_name;
1059
1060 -- Start of comments
1061 -- Procedure Name : validate_package_name
1062 -- Description : Checks for valid length, Uniqueness and upper case for column PACKAGE_NAME
1063 -- Version : 1.0
1064 -- End of comments
1065
1066 PROCEDURE validate_package_name(
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 p_pdfv_rec IN pdfv_rec_type) IS
1069
1070 BEGIN
1071 -- initialize return status
1072 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1073
1074 --Check if the data is in upper case
1075 IF p_pdfv_rec.package_name <> UPPER(p_pdfv_rec.package_name) THEN
1076 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1077 p_msg_name => g_uppercase_required,
1078 p_token1 => g_col_name_token,
1079 p_token1_value => 'package_name');
1080 x_return_status := OKC_API.G_RET_STS_ERROR;
1081 raise G_EXCEPTION_HALT_VALIDATION;
1082 END IF;
1083 EXCEPTION
1084 when G_EXCEPTION_HALT_VALIDATION then
1085 -- no processing necessary; validation can continue
1086 -- with the next column
1087 null;
1088
1089 when OTHERS then
1090 -- store SQL error message on message stack for caller
1091 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1092 p_msg_name => g_unexpected_error,
1093 p_token1 => g_sqlcode_token,
1094 p_token1_value => sqlcode,
1095 p_token2 => g_sqlerrm_token,
1096 p_token2_value => sqlerrm);
1097
1098 -- notify caller of an UNEXPECTED error
1099 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1100 END validate_package_name;
1101
1102 -- Start of comments
1103 -- Procedure Name : validate_pdf_type
1104 -- Description : Checks for valid pdf_type
1105 -- Version : 1.0
1106 -- End of comments
1107
1108 PROCEDURE validate_pdf_type(
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 p_pdfv_rec IN pdfv_rec_type) IS
1111 BEGIN
1112 -- initialize return status
1113 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1114
1115 --Check if pdf_type is null
1116 IF p_pdfv_rec.pdf_type IS NULL THEN
1117 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1118 p_msg_name => g_required_value,
1119 p_token1 => g_col_name_token,
1120 p_token1_value => 'type');
1121 x_return_status := OKC_API.G_RET_STS_ERROR;
1122 raise G_EXCEPTION_HALT_VALIDATION;
1123 END IF;
1124
1125 -- Check if pdf_type is Workflow or plsql procedure
1126 If p_pdfv_rec.pdf_type not in ('PPS', 'WPS','ALERT', 'SCRIPT') then
1127 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1128 p_msg_name => g_invalid_value,
1129 p_token1 => g_col_name_token,
1130 p_token1_value => 'type');
1131 x_return_status := OKC_API.G_RET_STS_ERROR;
1132 raise G_EXCEPTION_HALT_VALIDATION;
1133 END IF;
1134 EXCEPTION
1135 when G_EXCEPTION_HALT_VALIDATION then
1136 -- no processing necessary; validation can continue
1137 -- with the next column
1138 null;
1139
1140 when OTHERS then
1141 -- store SQL error message on message stack for caller
1142 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1143 p_msg_name => g_unexpected_error,
1144 p_token1 => g_sqlcode_token,
1145 p_token1_value => sqlcode,
1146 p_token2 => g_sqlerrm_token,
1147 p_token2_value => sqlerrm);
1148 -- notify caller of an UNEXPECTED error
1149 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1150 END validate_pdf_type;
1151
1152 -- Start of comments
1153 -- Procedure Name : validate_begin_date
1154 -- Description : Check if begin_date is null
1155 -- Version : 1.0
1156 -- End of comments
1157
1158 PROCEDURE validate_begin_date(
1159 x_return_status OUT NOCOPY VARCHAR2,
1160 p_pdfv_rec IN pdfv_rec_type) IS
1161 BEGIN
1162 -- initialize return status
1163 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1164
1165 -- Check if the data is null
1166 IF p_pdfv_rec.begin_date = OKC_API.G_MISS_DATE OR p_pdfv_rec.begin_date IS NULL THEN
1167 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1168 p_msg_name => g_required_value,
1169 p_token1 => g_col_name_token,
1170 p_token1_value => 'begin_date');
1171 x_return_status := OKC_API.G_RET_STS_ERROR;
1172 raise G_EXCEPTION_HALT_VALIDATION;
1173 END IF;
1174 EXCEPTION
1175 when G_EXCEPTION_HALT_VALIDATION then
1176 -- no processing necessary; validation can continue
1177 -- with the next column
1178 null;
1179
1180 when OTHERS then
1181 -- store SQL error message on message stack for caller
1182 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1183 p_msg_name => g_unexpected_error,
1184 p_token1 => g_sqlcode_token,
1185 p_token1_value => sqlcode,
1186 p_token2 => g_sqlerrm_token,
1187 p_token2_value => sqlerrm);
1188 -- notify caller of an UNEXPECTED error
1189 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1190 END validate_begin_date;
1191
1192 FUNCTION validate_attributes (
1193 p_pdfv_rec IN pdfv_rec_type
1194 ) RETURN VARCHAR2 IS
1195 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1196 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1197 BEGIN
1198 validate_object_version_number(x_return_status
1199 ,p_pdfv_rec);
1200 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1201 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1202 l_return_status := x_return_status;
1203 RAISE G_EXCEPTION_HALT_VALIDATION;
1204 ELSE
1205 l_return_status := x_return_status; -- record that there was an error
1206 END IF;
1207 END IF;
1208
1209 validate_sfwt_flag(x_return_status
1210 ,p_pdfv_rec);
1211 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1212 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1213 l_return_status := x_return_status;
1214 RAISE G_EXCEPTION_HALT_VALIDATION;
1215 ELSE
1216 l_return_status := x_return_status; -- record that there was an error
1217 END IF;
1218 END IF;
1219
1220 validate_seeded_flag(x_return_status
1221 ,p_pdfv_rec);
1222 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1223 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1224 l_return_status := x_return_status;
1225 RAISE G_EXCEPTION_HALT_VALIDATION;
1226 ELSE
1227 l_return_status := x_return_status; -- record that there was an error
1228 END IF;
1229 END IF;
1230
1231 validate_application_id(x_return_status
1232 ,p_pdfv_rec);
1233 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1234 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1235 l_return_status := x_return_status;
1236 RAISE G_EXCEPTION_HALT_VALIDATION;
1237 ELSE
1238 l_return_status := x_return_status; -- record that there was an error
1239 END IF;
1240 END IF;
1241
1242 validate_usage(x_return_status
1243 ,p_pdfv_rec);
1244 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1245 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1246 l_return_status := x_return_status;
1247 RAISE G_EXCEPTION_HALT_VALIDATION;
1248 ELSE
1249 l_return_status := x_return_status; -- record that there was an error
1250 END IF;
1251 END IF;
1252
1253 validate_name(x_return_status
1254 ,p_pdfv_rec);
1255 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1256 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1257 l_return_status := x_return_status;
1258 RAISE G_EXCEPTION_HALT_VALIDATION;
1259 ELSE
1260 l_return_status := x_return_status; -- record that there was an error
1261 END IF;
1262 END IF;
1263
1264 validate_wf_name(x_return_status
1265 ,p_pdfv_rec);
1266 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1267 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1268 l_return_status := x_return_status;
1269 RAISE G_EXCEPTION_HALT_VALIDATION;
1270 ELSE
1271 l_return_status := x_return_status; -- record that there was an error
1272 END IF;
1273 END IF;
1274
1275 validate_wf_process_name(x_return_status
1276 ,p_pdfv_rec);
1277 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1278 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1279 l_return_status := x_return_status;
1280 RAISE G_EXCEPTION_HALT_VALIDATION;
1281 ELSE
1282 l_return_status := x_return_status; -- record that there was an error
1283 END IF;
1284 END IF;
1285
1286 validate_procedure_name(x_return_status
1287 ,p_pdfv_rec);
1288 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1289 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1290 l_return_status := x_return_status;
1291 RAISE G_EXCEPTION_HALT_VALIDATION;
1292 ELSE
1293 l_return_status := x_return_status; -- record that there was an error
1294 END IF;
1295 END IF;
1296
1297 validate_package_name(x_return_status
1298 ,p_pdfv_rec);
1299 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1300 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1301 l_return_status := x_return_status;
1302 RAISE G_EXCEPTION_HALT_VALIDATION;
1303 ELSE
1304 l_return_status := x_return_status; -- record that there was an error
1305 END IF;
1306 END IF;
1307
1308 validate_pdf_type(x_return_status
1309 ,p_pdfv_rec);
1310 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1311 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1312 l_return_status := x_return_status;
1313 RAISE G_EXCEPTION_HALT_VALIDATION;
1314 ELSE
1315 l_return_status := x_return_status; -- record that there was an error
1316 END IF;
1317 END IF;
1318
1319 validate_begin_date(x_return_status
1320 ,p_pdfv_rec);
1321 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1322 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1323 l_return_status := x_return_status;
1324 RAISE G_EXCEPTION_HALT_VALIDATION;
1325 ELSE
1326 l_return_status := x_return_status; -- record that there was an error
1327 END IF;
1328 END IF;
1329
1330 RETURN (l_return_status);
1331
1332 EXCEPTION
1333 When G_EXCEPTION_HALT_VALIDATION then
1334 --just come out with return status
1335 null;
1336 RETURN(l_return_status);
1337
1338 when OTHERS then
1339 -- store SQL error message on message stack for caller
1340 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1341 p_msg_name => g_unexpected_error,
1342 p_token1 => g_sqlcode_token,
1343 p_token1_value => sqlcode,
1344 p_token2 => g_sqlerrm_token,
1345 p_token2_value => sqlerrm);
1346
1347 -- notify caller of an UNEXPECTED error
1348 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1349 RETURN(l_return_status);
1350 END validate_attributes;
1351
1352 ---------------------------------------------------------------------------
1353 -- PROCEDURE Validate_Record
1354 ---------------------------------------------------------------------------
1355 --------------------------------------------
1356 -- Validate_Record for:OKC_PROCESS_DEFS_V --
1357 --------------------------------------------
1358 --------------------------------------------------------------------------------
1359 -- Start of comments
1360 -- Procedure Name : validate_unique_pack_proc
1361 -- Description : Checks for unique procedure name and package name
1362 -- Version : 1.0
1363 -- End of comments
1364 --------------------------------------------------------------------------------
1365
1366 PROCEDURE validate_unique_pack_proc(
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 p_pdfv_rec IN pdfv_rec_type) IS
1369
1370 --l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1371 --l_unq_tbl OKC_UTIL.unq_tbl_type;
1372 l_dummy VARCHAR2(1);
1373 l_row_found Boolean := False;
1374 CURSOR c1(p_package_name okc_process_defs_v.package_name%TYPE,
1375 p_procedure_name okc_process_defs_v.procedure_name%TYPE) is
1376 SELECT 1
1377 FROM okc_process_defs_b
1378 WHERE package_name = p_package_name
1379 AND procedure_name = p_procedure_name
1380 AND id <> nvl(p_pdfv_rec.id,-99999);
1381
1382 BEGIN
1383
1384 -- initialize return status
1385 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1386
1387 /*Bug 1636056:The following code commented out nocopy since it was not using bind
1388 variables and parsing was taking place.Replaced with explicit cursor
1389 as above
1390 -- initialize return status
1391 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1392
1393 -- initialize columns of unique concatenated key
1394 l_unq_tbl(1).p_col_name := 'package_name';
1395 l_unq_tbl(1).p_col_val := p_pdfv_rec.package_name;
1396 l_unq_tbl(2).p_col_name := 'procedure_name';
1397 l_unq_tbl(2).p_col_val := p_pdfv_rec.procedure_name;
1398
1399 If p_pdfv_rec.package_name is not null or p_pdfv_rec.procedure_name is not null then
1400 OKC_UTIL.check_comp_unique(G_VIEW
1401 ,p_col_tbl => l_unq_tbl
1402 ,p_id => p_pdfv_rec.id
1403 ,x_return_status => l_return_status);
1404 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1405 x_return_status := OKC_API.G_RET_STS_ERROR;
1406 END IF;
1407 End if; */
1408 If p_pdfv_rec.package_name is not null or p_pdfv_rec.procedure_name is not null then
1409 OPEN c1(p_pdfv_rec.package_name,
1410 p_pdfv_rec.procedure_name);
1411 FETCH c1 into l_dummy;
1412 l_row_found := c1%FOUND;
1413 CLOSE c1;
1414 End if;
1415 IF l_row_found then
1416 --OKC_API.set_message(G_APP_NAME,G_UNQS,G_COL_NAME_TOKEN1,'procedure_name',G_COL_NAME_TOKEN2,'package_name');
1417 OKC_API.set_message(G_APP_NAME,G_UNQS1);
1418 x_return_status := OKC_API.G_RET_STS_ERROR;
1419 END IF;
1420
1421 EXCEPTION
1422 when OTHERS then
1423 -- store SQL error message on message stack for caller
1424 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1425 p_msg_name => g_unexpected_error,
1426 p_token1 => g_sqlcode_token,
1427 p_token1_value => sqlcode,
1428 p_token2 => g_sqlerrm_token,
1429 p_token2_value => sqlerrm);
1430 -- notify caller of an UNEXPECTED error
1431 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1432 END validate_unique_pack_proc;
1433 --------------------------------------------------------------------------------
1434 -- Start of comments
1435 -- Procedure Name : validate_unique_wf_process
1436 -- Description : Checks for unique workflow name and workflow process name
1437 -- Version : 1.0
1438 -- End of comments
1439 --------------------------------------------------------------------------------
1440
1441 PROCEDURE validate_unique_wf_process(
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 p_pdfv_rec IN pdfv_rec_type) IS
1444
1445 --l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1446 --l_unq_tbl OKC_UTIL.unq_tbl_type;
1447 l_dummy VARCHAR2(1);
1448 l_row_found Boolean := False;
1449 CURSOR c1(p_wf_name okc_process_defs_v.wf_name%TYPE,
1450 p_wf_process_name okc_process_defs_v.wf_process_name%TYPE) is
1451 SELECT 1
1452 FROM okc_process_defs_v
1453 WHERE wf_name = p_wf_name
1454 AND wf_process_name = p_wf_process_name
1455 AND id <> nvl(p_pdfv_rec.id,-99999);
1456
1457 BEGIN
1458 -- initialize return status
1459 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1460
1461
1462 /*Bug 1636056:The following code commented out nocopy sinct it was not using bind
1463 variables and parsing was taking place.Replced with explicit cursor
1464 as above
1465 -- initialize return status
1466 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1467
1468 -- initialize columns of unique concatenated key
1469 l_unq_tbl(1).p_col_name := 'wf_name';
1470 l_unq_tbl(1).p_col_val := p_pdfv_rec.wf_name;
1471 l_unq_tbl(2).p_col_name := 'wf_process_name';
1472 l_unq_tbl(2).p_col_val := p_pdfv_rec.wf_process_name;
1473
1474 If p_pdfv_rec.wf_name is not null or p_pdfv_rec.wf_process_name is not null then
1475 OKC_UTIL.check_comp_unique(G_VIEW
1476 ,p_col_tbl => l_unq_tbl
1477 ,p_id => p_pdfv_rec.id
1478 ,x_return_status => l_return_status);
1479 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1480 x_return_status := OKC_API.G_RET_STS_ERROR;
1481 END IF;
1482 End if;
1483 */
1484 If p_pdfv_rec.wf_name is not null or p_pdfv_rec.wf_process_name is not null then
1485 OPEN c1(p_pdfv_rec.wf_name,
1486 p_pdfv_rec.wf_process_name);
1487 FETCH c1 into l_dummy;
1488 l_row_found := c1%FOUND;
1489 CLOSE c1;
1490 End if;
1491 IF l_row_found then
1492 --OKC_API.set_message(G_APP_NAME,G_UNQS,G_COL_NAME_TOKEN1,'wf_name',G_COL_NAME_TOKEN2,'wf_process_name');
1493 OKC_API.set_message(G_APP_NAME,G_UNQS2);
1494 x_return_status := OKC_API.G_RET_STS_ERROR;
1495 END IF;
1496
1497 EXCEPTION
1498 when OTHERS then
1499 -- store SQL error message on message stack for caller
1500 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1501 p_msg_name => g_unexpected_error,
1502 p_token1 => g_sqlcode_token,
1503 p_token1_value => sqlcode,
1504 p_token2 => g_sqlerrm_token,
1505 p_token2_value => sqlerrm);
1506 -- notify caller of an UNEXPECTED error
1507 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1508 END validate_unique_wf_process;
1509
1510 -- Start of comments
1511 -- Procedure Name : validate_wf_plsql
1512 -- Description : Checks if workflow and PL/SQL procedures are mutually exclusive
1513 -- Version : 1.0
1514 -- End of comments
1515
1516 PROCEDURE validate_wf_plsql(
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 p_pdfv_rec IN pdfv_rec_type) IS
1519
1520 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1521
1522 BEGIN
1523 -- initialize return status
1524 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1525
1526 -- Check if Process definition type is either workflow or PL/SQL Process
1527 IF p_pdfv_rec.usage IN ( 'APPROVE', 'CHG_REQ_APPROVE', 'API') THEN
1528 IF p_pdfv_rec.pdf_type IN ('PPS') THEN
1529 IF p_pdfv_rec.procedure_name IS NULL AND p_pdfv_rec.package_name IS NULL THEN
1530 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1531 p_msg_name => g_arc_mandatory,
1532 p_token1 => g_col_name1,
1533 p_token1_value => 'package',
1534 p_token2 => g_col_name2,
1535 p_token2_value => 'procedure');
1536 x_return_status := OKC_API.G_RET_STS_ERROR;
1537 raise G_EXCEPTION_HALT_VALIDATION;
1538 ELSIF p_pdfv_rec.procedure_name IS NULL AND p_pdfv_rec.package_name IS NOT NULL THEN
1539 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1540 p_msg_name => g_required_value,
1541 p_token1 => g_col_name_token,
1542 p_token1_value => 'procedure');
1543 x_return_status := OKC_API.G_RET_STS_ERROR;
1544 raise G_EXCEPTION_HALT_VALIDATION;
1545 END IF;
1546 END IF;
1547 END IF;
1548 IF p_pdfv_rec.usage IN ( 'APPROVE', 'CHG_REQ_APPROVE', 'API') THEN
1549 IF p_pdfv_rec.pdf_type IN ('WPS') THEN
1550 IF p_pdfv_rec.wf_name IS NULL AND p_pdfv_rec.wf_process_name IS NULL THEN
1551 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1552 p_msg_name => g_arc_mandatory,
1553 p_token1 => g_col_name1,
1554 p_token1_value => 'workflow name',
1555 p_token2 => g_col_name2,
1556 p_token2_value => 'workflow process');
1557 x_return_status := OKC_API.G_RET_STS_ERROR;
1558 raise G_EXCEPTION_HALT_VALIDATION;
1559 ELSIF p_pdfv_rec.wf_name IS NULL AND p_pdfv_rec.wf_process_name IS NOT NULL THEN
1560 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1561 p_msg_name => g_required_value,
1562 p_token1 => g_col_name_token,
1563 p_token1_value => 'workflow name');
1564 x_return_status := OKC_API.G_RET_STS_ERROR;
1565 raise G_EXCEPTION_HALT_VALIDATION;
1566 END IF;
1567 END IF;
1568 END IF;
1569 EXCEPTION
1570 When G_EXCEPTION_HALT_VALIDATION then
1571 --just come out with return status
1572 null;
1573
1574 when OTHERS then
1575 -- store SQL error message on message stack for caller
1576 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1577 p_msg_name => g_unexpected_error,
1578 p_token1 => g_sqlcode_token,
1579 p_token1_value => sqlcode,
1580 p_token2 => g_sqlerrm_token,
1581 p_token2_value => sqlerrm);
1582
1583 -- notify caller of an UNEXPECTED error
1584 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1585
1586 END validate_wf_plsql;
1587
1588 -- Start of comments
1589 -- Procedure Name : validate_outcome
1590 -- Description : validation for outcome
1591 -- Version : 1.0
1592 -- End of comments
1593
1594 PROCEDURE validate_outcome(
1595 x_return_status OUT NOCOPY VARCHAR2,
1596 p_pdfv_rec IN pdfv_rec_type) IS
1597
1598 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1599
1600 BEGIN
1601 -- initialize return status
1602 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1603
1604 -- Check if pdf_type = 'ALERT' or 'SCRIPT' then wf and plsql should be null
1605 IF p_pdfv_rec.usage = 'OUTCOME' THEN
1606 IF p_pdfv_rec.pdf_type = 'ALERT' THEN
1607 IF p_pdfv_rec.message_name is null THEN
1608 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1609 p_msg_name => g_required_value,
1610 p_token1 => g_col_name_token,
1611 p_token1_value => 'Alert Name');
1612 x_return_status := OKC_API.G_RET_STS_ERROR;
1613 raise G_EXCEPTION_HALT_VALIDATION;
1614 END IF;
1615 -- if ALERT is the outcome then all other outcome types should be null
1616 IF p_pdfv_rec.wf_name IS NOT NULL OR p_pdfv_rec.wf_process_name IS NOT NULL
1617 OR p_pdfv_rec.procedure_name IS NOT NULL
1618 OR p_pdfv_rec.package_name IS NOT NULL
1619 OR p_pdfv_rec.script_name IS NOT NULL THEN
1620 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1621 p_msg_name => g_invalid_value,
1622 p_token1 => g_col_name_token,
1623 p_token1_value => 'workflow name',
1624 p_token2 => g_col_name_token,
1625 p_token2_value => 'workflow process name',
1626 p_token3 => g_col_name_token,
1627 p_token3_value => 'procedure name',
1628 p_token4 => g_col_name_token,
1629 p_token4_value => 'package name',
1630 p_token5 => g_col_name_token,
1631 p_token5_value => 'script name'
1632 );
1633 x_return_status := OKC_API.G_RET_STS_ERROR;
1634 raise G_EXCEPTION_HALT_VALIDATION;
1635 END IF;
1636
1637 END IF; -- p_pdfv_rec.pdf_type = 'ALERT'
1638 IF p_pdfv_rec.pdf_type = 'SCRIPT' THEN
1639 IF p_pdfv_rec.script_name is null THEN
1640 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1641 p_msg_name => g_required_value,
1642 p_token1 => g_col_name_token,
1643 p_token1_value => 'Script name');
1644 x_return_status := OKC_API.G_RET_STS_ERROR;
1645 raise G_EXCEPTION_HALT_VALIDATION;
1646 END IF;
1647 -- if SCRIPT is the outcome then all other outcome types should be null
1648 IF p_pdfv_rec.wf_name IS NOT NULL OR p_pdfv_rec.wf_process_name IS NOT NULL
1649 OR p_pdfv_rec.procedure_name IS NOT NULL
1650 OR p_pdfv_rec.package_name IS NOT NULL
1651 OR p_pdfv_rec.message_name IS NOT NULL THEN
1652 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1653 p_msg_name => g_invalid_value,
1654 p_token1 => g_col_name_token,
1655 p_token1_value => 'workflow name',
1656 p_token2 => g_col_name_token,
1657 p_token2_value => 'workflow process name',
1658 p_token3 => g_col_name_token,
1659 p_token3_value => 'procedure name',
1660 p_token4 => g_col_name_token,
1661 p_token4_value => 'package name',
1662 p_token5 => g_col_name_token,
1663 p_token5_value => 'alert name'
1664 );
1665 x_return_status := OKC_API.G_RET_STS_ERROR;
1666 raise G_EXCEPTION_HALT_VALIDATION;
1667 END IF;
1668
1669
1670 END IF; -- p_pdfv_rec.pdf_type = 'SCRIPT'
1671
1672 IF p_pdfv_rec.pdf_type = 'PPS' THEN
1673 IF p_pdfv_rec.procedure_name IS NULL AND p_pdfv_rec.package_name IS NULL THEN
1674 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1675 p_msg_name => g_arc_mandatory,
1676 p_token1 => g_col_name1,
1677 p_token1_value => 'package',
1678 p_token2 => g_col_name2,
1679 p_token2_value => 'procedure');
1680 x_return_status := OKC_API.G_RET_STS_ERROR;
1681 raise G_EXCEPTION_HALT_VALIDATION;
1682 ELSIF p_pdfv_rec.procedure_name IS NULL AND p_pdfv_rec.package_name IS NOT NULL THEN
1683 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1684 p_msg_name => g_required_value,
1685 p_token1 => g_col_name_token,
1686 p_token1_value => 'procedure');
1687 x_return_status := OKC_API.G_RET_STS_ERROR;
1688 raise G_EXCEPTION_HALT_VALIDATION;
1689 END IF;
1690 -- if PPS is the outcome then all other outcome types should be null
1691 IF p_pdfv_rec.wf_name IS NOT NULL OR p_pdfv_rec.wf_process_name IS NOT NULL
1692 OR p_pdfv_rec.script_name IS NOT NULL
1693 OR p_pdfv_rec.message_name IS NOT NULL THEN
1694 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1695 p_msg_name => g_invalid_value,
1696 p_token1 => g_col_name_token,
1697 p_token1_value => 'workflow name',
1698 p_token2 => g_col_name_token,
1699 p_token2_value => 'workflow process name',
1700 p_token3 => g_col_name_token,
1701 p_token3_value => 'script name',
1702 p_token4 => g_col_name_token,
1703 p_token4_value => 'alert name'
1704 );
1705 x_return_status := OKC_API.G_RET_STS_ERROR;
1706 raise G_EXCEPTION_HALT_VALIDATION;
1707 END IF;
1708
1709 END IF; -- p_pdfv_rec.pdf_type = 'PPS'
1710
1711 IF p_pdfv_rec.pdf_type = 'WPS' THEN
1712 IF p_pdfv_rec.wf_name IS NULL AND p_pdfv_rec.wf_process_name IS NULL THEN
1713 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1714 p_msg_name => g_arc_mandatory,
1715 p_token1 => g_col_name1,
1716 p_token1_value => 'workflow name',
1717 p_token2 => g_col_name2,
1718 p_token2_value => 'workflow process');
1719 x_return_status := OKC_API.G_RET_STS_ERROR;
1720 raise G_EXCEPTION_HALT_VALIDATION;
1721 ELSIF p_pdfv_rec.wf_name IS NULL AND p_pdfv_rec.wf_process_name IS NOT NULL THEN
1722 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1723 p_msg_name => g_required_value,
1724 p_token1 => g_col_name_token,
1725 p_token1_value => 'workflow name');
1726 x_return_status := OKC_API.G_RET_STS_ERROR;
1727 raise G_EXCEPTION_HALT_VALIDATION;
1728 END IF;
1729 -- if WPS is the outcome then all other outcome types should be null
1730 IF p_pdfv_rec.procedure_name IS NOT NULL
1731 OR p_pdfv_rec.package_name IS NOT NULL
1732 OR p_pdfv_rec.script_name IS NOT NULL
1733 OR p_pdfv_rec.message_name IS NOT NULL THEN
1734 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1735 p_msg_name => g_invalid_value,
1736 p_token1 => g_col_name_token,
1737 p_token1_value => 'script name',
1738 p_token2 => g_col_name_token,
1739 p_token2_value => 'procedure name',
1740 p_token3 => g_col_name_token,
1741 p_token3_value => 'package name',
1742 p_token4 => g_col_name_token,
1743 p_token4_value => 'alert name'
1744 );
1745 x_return_status := OKC_API.G_RET_STS_ERROR;
1746 raise G_EXCEPTION_HALT_VALIDATION;
1747 END IF;
1748
1749 END IF; -- p_pdfv_rec.pdf_type = 'WPS'
1750 END IF; -- p_pdfv_rec.usage = 'OUTCOME'
1751 EXCEPTION
1752 When G_EXCEPTION_HALT_VALIDATION then
1753 --just come out with return status
1754 null;
1755
1756 when OTHERS then
1757 -- store SQL error message on message stack for caller
1758 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1759 p_msg_name => g_unexpected_error,
1760 p_token1 => g_sqlcode_token,
1761 p_token1_value => sqlcode,
1762 p_token2 => g_sqlerrm_token,
1763 p_token2_value => sqlerrm);
1764
1765 -- notify caller of an UNEXPECTED error
1766 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1767 END validate_outcome;
1768
1769 -- Start of comments
1770 -- Procedure Name : validate_qa
1771 -- Description : Checks if usage is QA then it has to be a PL/SQL process
1772 -- Version : 1.0
1773 -- End of comments
1774
1775 PROCEDURE validate_qa(
1776 x_return_status OUT NOCOPY VARCHAR2,
1777 p_pdfv_rec IN pdfv_rec_type) IS
1778
1779 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1780
1781 BEGIN
1782 -- initialize return status
1783 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1784
1785 -- Check if QA Process is only PL/SQL
1786 IF p_pdfv_rec.usage IN ('QA','AUTONUMBERING') THEN
1787 IF p_pdfv_rec.procedure_name IS NULL
1788 AND p_pdfv_rec.package_name IS NULL THEN
1789 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1790 p_msg_name => g_arc_mandatory,
1791 p_token1 => g_col_name1,
1792 p_token1_value => 'package',
1793 p_token2 => g_col_name2,
1794 p_token2_value => 'procedure');
1795 x_return_status := OKC_API.G_RET_STS_ERROR;
1796 raise G_EXCEPTION_HALT_VALIDATION;
1797 ELSIF p_pdfv_rec.procedure_name IS NULL
1798 AND p_pdfv_rec.package_name IS NOT NULL THEN
1799 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1800 p_msg_name => g_required_value,
1801 p_token1 => g_col_name_token,
1802 p_token1_value => 'procedure');
1803 x_return_status := OKC_API.G_RET_STS_ERROR;
1804 raise G_EXCEPTION_HALT_VALIDATION;
1805 END IF;
1806 END IF;
1807 EXCEPTION
1808 When G_EXCEPTION_HALT_VALIDATION then
1809 --just come out with return status
1810 null;
1811
1812 when OTHERS then
1813 -- store SQL error message on message stack for caller
1814 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1815 p_msg_name => g_unexpected_error,
1816 p_token1 => g_sqlcode_token,
1817 p_token1_value => sqlcode,
1818 p_token2 => g_sqlerrm_token,
1819 p_token2_value => sqlerrm);
1820
1821 -- notify caller of an UNEXPECTED error
1822 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1823
1824 END validate_qa;
1825
1826 -- Start of comments
1827 -- Procedure Name : validate_function
1828 -- Description : Checks if usage is FUNCTION then it has to be a PL/SQL Function
1829 -- Version : 1.0
1830 -- End of comments
1831
1832 PROCEDURE validate_function(
1833 x_return_status OUT NOCOPY VARCHAR2,
1834 p_pdfv_rec IN pdfv_rec_type) IS
1835
1836 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1837
1838 BEGIN
1839 -- initialize return status
1840 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1841
1842 -- Check if FUNCTION is only PL/SQL Function
1843 IF p_pdfv_rec.usage = 'FUNCTION' THEN
1844 IF p_pdfv_rec.procedure_name IS NULL
1845 AND p_pdfv_rec.package_name IS NULL THEN
1846 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1847 p_msg_name => g_arc_mandatory,
1848 p_token1 => g_col_name1,
1849 p_token1_value => 'package',
1850 p_token2 => g_col_name2,
1851 p_token2_value => 'procedure');
1852 x_return_status := OKC_API.G_RET_STS_ERROR;
1853 raise G_EXCEPTION_HALT_VALIDATION;
1854 ELSIF p_pdfv_rec.procedure_name IS NULL
1855 AND p_pdfv_rec.package_name IS NOT NULL THEN
1856 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1857 p_msg_name => g_required_value,
1858 p_token1 => g_col_name_token,
1859 p_token1_value => 'procedure');
1860 x_return_status := OKC_API.G_RET_STS_ERROR;
1861 raise G_EXCEPTION_HALT_VALIDATION;
1862 END IF;
1863 END IF;
1864 EXCEPTION
1865 When G_EXCEPTION_HALT_VALIDATION then
1866 --just come out with return status
1867 null;
1868
1869 when OTHERS then
1870 -- store SQL error message on message stack for caller
1871 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1872 p_msg_name => g_unexpected_error,
1873 p_token1 => g_sqlcode_token,
1874 p_token1_value => sqlcode,
1875 p_token2 => g_sqlerrm_token,
1876 p_token2_value => sqlerrm);
1877
1878 -- notify caller of an UNEXPECTED error
1879 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1880
1881 END validate_function;
1882
1883 -- Start of comments
1884 -- Procedure Name : validate_end_date
1885 -- Description : Checks if end date is greater than begin date
1886 -- Version : 1.0
1887 -- End of comments
1888
1889 PROCEDURE validate_end_date(
1890 x_return_status OUT NOCOPY VARCHAR2,
1891 p_pdfv_rec IN pdfv_rec_type) IS
1892
1893 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1894
1895 BEGIN
1896 -- initialize return status
1897 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1898
1899 -- Check if end date is greater than begin date
1900 IF p_pdfv_rec.end_date < p_pdfv_rec.begin_date THEN
1901 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1902 p_msg_name => g_invalid_end_date,
1903 p_token1 => g_col_name_token,
1904 p_token1_value => 'begin_date',
1905 p_token2 => g_col_name_token,
1906 p_token2_value => 'end_date');
1907 x_return_status := OKC_API.G_RET_STS_ERROR;
1908 raise G_EXCEPTION_HALT_VALIDATION;
1909 END IF;
1910
1911 EXCEPTION
1912 When G_EXCEPTION_HALT_VALIDATION then
1913 --just come out with return status
1914 null;
1915
1916 when OTHERS then
1917 -- store SQL error message on message stack for caller
1918 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1919 p_msg_name => g_unexpected_error,
1920 p_token1 => g_sqlcode_token,
1921 p_token1_value => sqlcode,
1922 p_token2 => g_sqlerrm_token,
1923 p_token2_value => sqlerrm);
1924
1925 -- notify caller of an UNEXPECTED error
1926 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1927
1928 END validate_end_date;
1929
1930 FUNCTION Validate_Record (
1931 p_pdfv_rec IN pdfv_rec_type
1932 ) RETURN VARCHAR2 IS
1933 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1934 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1935
1936 BEGIN
1937 validate_unique_pack_proc(x_return_status
1938 ,p_pdfv_rec);
1939 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1940 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1941 l_return_status := x_return_status;
1942 RAISE G_EXCEPTION_HALT_VALIDATION;
1943 ELSE
1944 l_return_status := x_return_status; -- record that there was an error
1945 END IF;
1946 END IF;
1947
1948 validate_unique_wf_process(x_return_status
1949 ,p_pdfv_rec);
1950 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1951 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1952 l_return_status := x_return_status;
1953 RAISE G_EXCEPTION_HALT_VALIDATION;
1954 ELSE
1955 l_return_status := x_return_status; -- record that there was an error
1956 END IF;
1957 END IF;
1958
1959 validate_wf_plsql(x_return_status
1960 ,p_pdfv_rec);
1961 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1962 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1963 l_return_status := x_return_status;
1964 RAISE G_EXCEPTION_HALT_VALIDATION;
1965 ELSE
1966 l_return_status := x_return_status; -- record that there was an error
1967 END IF;
1968 END IF;
1969
1970 validate_outcome(x_return_status
1971 ,p_pdfv_rec);
1972 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1973 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1974 l_return_status := x_return_status;
1975 RAISE G_EXCEPTION_HALT_VALIDATION;
1976 ELSE
1977 l_return_status := x_return_status; -- record that there was an error
1978 END IF;
1979 END IF;
1980
1981 validate_qa(x_return_status
1982 ,p_pdfv_rec);
1983 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1984 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1985 l_return_status := x_return_status;
1986 RAISE G_EXCEPTION_HALT_VALIDATION;
1987 ELSE
1988 l_return_status := x_return_status; -- record that there was an error
1989 END IF;
1990 END IF;
1991
1992 validate_function(x_return_status
1993 ,p_pdfv_rec);
1994 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1995 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
1996 l_return_status := x_return_status;
1997 RAISE G_EXCEPTION_HALT_VALIDATION;
1998 ELSE
1999 l_return_status := x_return_status; -- record that there was an error
2000 END IF;
2001 END IF;
2002
2003 validate_end_date(x_return_status
2004 ,p_pdfv_rec);
2005 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2006 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
2007 l_return_status := x_return_status;
2008 RAISE G_EXCEPTION_HALT_VALIDATION;
2009 ELSE
2010 l_return_status := x_return_status; -- record that there was an error
2011 END IF;
2012 END IF;
2013 RETURN (l_return_status);
2014 EXCEPTION
2015 When G_EXCEPTION_HALT_VALIDATION then
2016 --just come out with return status
2017 null;
2018 RETURN(l_return_status);
2019
2020 when OTHERS then
2021 -- store SQL error message on message stack for caller
2022 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
2023 p_msg_name => g_unexpected_error,
2024 p_token1 => g_sqlcode_token,
2025 p_token1_value => sqlcode,
2026 p_token2 => g_sqlerrm_token,
2027 p_token2_value => sqlerrm);
2028
2029 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2030 RETURN (l_return_status);
2031 END Validate_Record;
2032
2033 /******************* End Hand Written Code *****************************/
2034
2035 ---------------------------------------------------------------------------
2036 -- PROCEDURE Migrate
2037 ---------------------------------------------------------------------------
2038 PROCEDURE migrate (
2039 p_from IN pdfv_rec_type,
2040 p_to OUT NOCOPY okc_process_defs_tl_rec_type
2041 ) IS
2042 BEGIN
2043 p_to.id := p_from.id;
2044 p_to.sfwt_flag := p_from.sfwt_flag;
2045 p_to.name := p_from.name;
2046 p_to.description := p_from.description;
2047 p_to.short_description := p_from.short_description;
2048 p_to.comments := p_from.comments;
2049 p_to.created_by := p_from.created_by;
2050 p_to.creation_date := p_from.creation_date;
2051 p_to.last_updated_by := p_from.last_updated_by;
2052 p_to.last_update_date := p_from.last_update_date;
2053 p_to.last_update_login := p_from.last_update_login;
2054 END migrate;
2055 PROCEDURE migrate (
2056 p_from IN okc_process_defs_tl_rec_type,
2057 p_to IN OUT NOCOPY pdfv_rec_type
2058 ) IS
2059 BEGIN
2060 p_to.id := p_from.id;
2061 p_to.sfwt_flag := p_from.sfwt_flag;
2062 p_to.name := p_from.name;
2063 p_to.description := p_from.description;
2064 p_to.short_description := p_from.short_description;
2065 p_to.comments := p_from.comments;
2066 p_to.created_by := p_from.created_by;
2067 p_to.creation_date := p_from.creation_date;
2068 p_to.last_updated_by := p_from.last_updated_by;
2069 p_to.last_update_date := p_from.last_update_date;
2070 p_to.last_update_login := p_from.last_update_login;
2071 END migrate;
2072 PROCEDURE migrate (
2073 p_from IN pdfv_rec_type,
2074 p_to OUT NOCOPY pdf_rec_type
2075 ) IS
2076 BEGIN
2077 p_to.id := p_from.id;
2078 p_to.pdf_type := p_from.pdf_type;
2079 p_to.object_version_number := p_from.object_version_number;
2080 p_to.created_by := p_from.created_by;
2081 p_to.usage := p_from.usage;
2082 p_to.creation_date := p_from.creation_date;
2083 p_to.begin_date := p_from.begin_date;
2084 p_to.last_updated_by := p_from.last_updated_by;
2085 p_to.last_update_date := p_from.last_update_date;
2086 p_to.wf_name := p_from.wf_name;
2087 p_to.wf_process_name := p_from.wf_process_name;
2088 p_to.procedure_name := p_from.procedure_name;
2089 p_to.package_name := p_from.package_name;
2090 p_to.last_update_login := p_from.last_update_login;
2091 p_to.end_date := p_from.end_date;
2092 p_to.attribute_category := p_from.attribute_category;
2093 p_to.attribute1 := p_from.attribute1;
2094 p_to.attribute2 := p_from.attribute2;
2095 p_to.attribute3 := p_from.attribute3;
2096 p_to.attribute4 := p_from.attribute4;
2097 p_to.attribute5 := p_from.attribute5;
2098 p_to.attribute6 := p_from.attribute6;
2099 p_to.attribute7 := p_from.attribute7;
2100 p_to.attribute8 := p_from.attribute8;
2101 p_to.attribute9 := p_from.attribute9;
2102 p_to.attribute10 := p_from.attribute10;
2103 p_to.attribute11 := p_from.attribute11;
2104 p_to.attribute12 := p_from.attribute12;
2105 p_to.attribute13 := p_from.attribute13;
2106 p_to.attribute14 := p_from.attribute14;
2107 p_to.attribute15 := p_from.attribute15;
2108 p_to.application_id := p_from.application_id;
2109 p_to.seeded_flag := p_from.seeded_flag;
2110 p_to.message_name := p_from.message_name;
2111 p_to.script_name := p_from.script_name;
2112 END migrate;
2113 PROCEDURE migrate (
2114 p_from IN pdf_rec_type,
2115 p_to IN OUT NOCOPY pdfv_rec_type
2116 ) IS
2117 BEGIN
2118 p_to.id := p_from.id;
2119 p_to.pdf_type := p_from.pdf_type;
2120 p_to.object_version_number := p_from.object_version_number;
2121 p_to.created_by := p_from.created_by;
2122 p_to.usage := p_from.usage;
2123 p_to.creation_date := p_from.creation_date;
2124 p_to.begin_date := p_from.begin_date;
2125 p_to.last_updated_by := p_from.last_updated_by;
2126 p_to.last_update_date := p_from.last_update_date;
2127 p_to.wf_name := p_from.wf_name;
2128 p_to.wf_process_name := p_from.wf_process_name;
2129 p_to.procedure_name := p_from.procedure_name;
2130 p_to.package_name := p_from.package_name;
2131 p_to.last_update_login := p_from.last_update_login;
2132 p_to.end_date := p_from.end_date;
2133 p_to.attribute_category := p_from.attribute_category;
2134 p_to.attribute1 := p_from.attribute1;
2135 p_to.attribute2 := p_from.attribute2;
2136 p_to.attribute3 := p_from.attribute3;
2137 p_to.attribute4 := p_from.attribute4;
2138 p_to.attribute5 := p_from.attribute5;
2139 p_to.attribute6 := p_from.attribute6;
2140 p_to.attribute7 := p_from.attribute7;
2141 p_to.attribute8 := p_from.attribute8;
2142 p_to.attribute9 := p_from.attribute9;
2143 p_to.attribute10 := p_from.attribute10;
2144 p_to.attribute11 := p_from.attribute11;
2145 p_to.attribute12 := p_from.attribute12;
2146 p_to.attribute13 := p_from.attribute13;
2147 p_to.attribute14 := p_from.attribute14;
2148 p_to.attribute15 := p_from.attribute15;
2149 p_to.application_id := p_from.application_id;
2150 p_to.seeded_flag := p_from.seeded_flag;
2151 p_to.message_name := p_from.message_name;
2152 p_to.script_name := p_from.script_name;
2153 END migrate;
2154
2155 ---------------------------------------------------------------------------
2156 -- PROCEDURE validate_row
2157 ---------------------------------------------------------------------------
2158 -----------------------------------------
2159 -- validate_row for:OKC_PROCESS_DEFS_V --
2160 -----------------------------------------
2161 PROCEDURE validate_row(
2162 p_api_version IN NUMBER,
2163 p_init_msg_list IN VARCHAR2,
2164 x_return_status OUT NOCOPY VARCHAR2,
2165 x_msg_count OUT NOCOPY NUMBER,
2166 x_msg_data OUT NOCOPY VARCHAR2,
2167 p_pdfv_rec IN pdfv_rec_type) IS
2168
2169 l_api_version CONSTANT NUMBER := 1;
2170 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2171 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2172 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
2173 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
2174 l_pdf_rec pdf_rec_type;
2175 BEGIN
2176 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2177 G_PKG_NAME,
2178 p_init_msg_list,
2179 l_api_version,
2180 p_api_version,
2181 '_PVT',
2182 x_return_status);
2183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2185 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2186 RAISE OKC_API.G_EXCEPTION_ERROR;
2187 END IF;
2188 --- Validate all non-missing attributes (Item Level Validation)
2189 l_return_status := Validate_Attributes(l_pdfv_rec);
2190 --- If any errors happen abort API
2191 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2192 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2193 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2194 RAISE OKC_API.G_EXCEPTION_ERROR;
2195 END IF;
2196 l_return_status := Validate_Record(l_pdfv_rec);
2197 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2199 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2200 RAISE OKC_API.G_EXCEPTION_ERROR;
2201 END IF;
2202 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2203 EXCEPTION
2204 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2205 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2206 (
2207 l_api_name,
2208 G_PKG_NAME,
2209 'OKC_API.G_RET_STS_ERROR',
2210 x_msg_count,
2211 x_msg_data,
2212 '_PVT'
2213 );
2214 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2215 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2216 (
2217 l_api_name,
2218 G_PKG_NAME,
2219 'OKC_API.G_RET_STS_UNEXP_ERROR',
2220 x_msg_count,
2221 x_msg_data,
2222 '_PVT'
2223 );
2224 WHEN OTHERS THEN
2225 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2226 (
2227 l_api_name,
2228 G_PKG_NAME,
2229 'OTHERS',
2230 x_msg_count,
2231 x_msg_data,
2232 '_PVT'
2233 );
2234 END validate_row;
2235 ------------------------------------------
2236 -- PL/SQL TBL validate_row for:PDFV_TBL --
2237 ------------------------------------------
2238 PROCEDURE validate_row(
2239 p_api_version IN NUMBER,
2240 p_init_msg_list IN VARCHAR2,
2241 x_return_status OUT NOCOPY VARCHAR2,
2242 x_msg_count OUT NOCOPY NUMBER,
2243 x_msg_data OUT NOCOPY VARCHAR2,
2244 p_pdfv_tbl IN pdfv_tbl_type) IS
2245
2246 l_api_version CONSTANT NUMBER := 1;
2247 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2248 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2249 i NUMBER := 0;
2250 BEGIN
2251 OKC_API.init_msg_list(p_init_msg_list);
2252 -- Make sure PL/SQL table has records in it before passing
2253 IF (p_pdfv_tbl.COUNT > 0) THEN
2254 i := p_pdfv_tbl.FIRST;
2255 LOOP
2256 validate_row (
2257 p_api_version => p_api_version,
2258 p_init_msg_list => OKC_API.G_FALSE,
2259 x_return_status => x_return_status,
2260 x_msg_count => x_msg_count,
2261 x_msg_data => x_msg_data,
2262 p_pdfv_rec => p_pdfv_tbl(i));
2263 EXIT WHEN (i = p_pdfv_tbl.LAST);
2264 i := p_pdfv_tbl.NEXT(i);
2265 END LOOP;
2266 END IF;
2267 EXCEPTION
2268 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2269 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2270 (
2271 l_api_name,
2272 G_PKG_NAME,
2273 'OKC_API.G_RET_STS_ERROR',
2274 x_msg_count,
2275 x_msg_data,
2276 '_PVT'
2277 );
2278 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2279 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2280 (
2281 l_api_name,
2282 G_PKG_NAME,
2283 'OKC_API.G_RET_STS_UNEXP_ERROR',
2284 x_msg_count,
2285 x_msg_data,
2286 '_PVT'
2287 );
2288 WHEN OTHERS THEN
2289 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2290 (
2291 l_api_name,
2292 G_PKG_NAME,
2293 'OTHERS',
2294 x_msg_count,
2295 x_msg_data,
2296 '_PVT'
2297 );
2298 END validate_row;
2299
2300 ---------------------------------------------------------------------------
2301 -- PROCEDURE insert_row
2302 ---------------------------------------------------------------------------
2303 ----------------------------------------
2304 -- insert_row for:OKC_PROCESS_DEFS_TL --
2305 ----------------------------------------
2306 PROCEDURE insert_row(
2307 p_init_msg_list IN VARCHAR2,
2308 x_return_status OUT NOCOPY VARCHAR2,
2309 x_msg_count OUT NOCOPY NUMBER,
2310 x_msg_data OUT NOCOPY VARCHAR2,
2311 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
2312 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type) IS
2313
2314 l_api_version CONSTANT NUMBER := 1;
2315 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
2316 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2317 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type := p_okc_process_defs_tl_rec;
2318 ldefokcprocessdefstlrec okc_process_defs_tl_rec_type;
2319 CURSOR get_languages IS
2320 SELECT *
2321 FROM FND_LANGUAGES
2322 WHERE INSTALLED_FLAG IN ('I', 'B');
2323 --------------------------------------------
2324 -- Set_Attributes for:OKC_PROCESS_DEFS_TL --
2325 --------------------------------------------
2326 FUNCTION Set_Attributes (
2327 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
2328 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type
2329 ) RETURN VARCHAR2 IS
2330 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2331 BEGIN
2332 x_okc_process_defs_tl_rec := p_okc_process_defs_tl_rec;
2333 x_okc_process_defs_tl_rec.LANGUAGE := l_lang;
2334 x_okc_process_defs_tl_rec.SOURCE_LANG := l_lang;
2335 RETURN(l_return_status);
2336 END Set_Attributes;
2337 BEGIN
2338 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2339 p_init_msg_list,
2340 '_PVT',
2341 x_return_status);
2342 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2343 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2344 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2345 RAISE OKC_API.G_EXCEPTION_ERROR;
2346 END IF;
2347 --- Setting item attributes
2348 l_return_status := Set_Attributes(
2349 p_okc_process_defs_tl_rec, -- IN
2350 l_okc_process_defs_tl_rec); -- OUT
2351 --- If any errors happen abort API
2352 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2353 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2354 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2355 RAISE OKC_API.G_EXCEPTION_ERROR;
2356 END IF;
2357 FOR l_lang_rec IN get_languages LOOP
2358 l_okc_process_defs_tl_rec.language := l_lang_rec.language_code;
2359 INSERT INTO OKC_PROCESS_DEFS_TL(
2360 id,
2361 language,
2362 source_lang,
2363 sfwt_flag,
2364 name,
2365 description,
2366 short_description,
2367 comments,
2368 created_by,
2369 creation_date,
2370 last_updated_by,
2371 last_update_date,
2372 last_update_login)
2373 VALUES (
2374 l_okc_process_defs_tl_rec.id,
2375 l_okc_process_defs_tl_rec.language,
2376 l_okc_process_defs_tl_rec.source_lang,
2377 l_okc_process_defs_tl_rec.sfwt_flag,
2378 l_okc_process_defs_tl_rec.name,
2379 l_okc_process_defs_tl_rec.description,
2380 l_okc_process_defs_tl_rec.short_description,
2381 l_okc_process_defs_tl_rec.comments,
2382 l_okc_process_defs_tl_rec.created_by,
2383 l_okc_process_defs_tl_rec.creation_date,
2384 l_okc_process_defs_tl_rec.last_updated_by,
2385 l_okc_process_defs_tl_rec.last_update_date,
2386 l_okc_process_defs_tl_rec.last_update_login);
2387 END LOOP;
2388 -- Set OUT values
2389 x_okc_process_defs_tl_rec := l_okc_process_defs_tl_rec;
2390 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2391 EXCEPTION
2392 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2393 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2394 (
2395 l_api_name,
2396 G_PKG_NAME,
2397 'OKC_API.G_RET_STS_ERROR',
2398 x_msg_count,
2399 x_msg_data,
2400 '_PVT'
2401 );
2402 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2403 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2404 (
2405 l_api_name,
2406 G_PKG_NAME,
2407 'OKC_API.G_RET_STS_UNEXP_ERROR',
2408 x_msg_count,
2409 x_msg_data,
2410 '_PVT'
2411 );
2412 WHEN OTHERS THEN
2413 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2414 (
2415 l_api_name,
2416 G_PKG_NAME,
2417 'OTHERS',
2418 x_msg_count,
2419 x_msg_data,
2420 '_PVT'
2421 );
2422 END insert_row;
2423 ---------------------------------------
2424 -- insert_row for:OKC_PROCESS_DEFS_B --
2425 ---------------------------------------
2426 PROCEDURE insert_row(
2427 p_init_msg_list IN VARCHAR2,
2428 x_return_status OUT NOCOPY VARCHAR2,
2429 x_msg_count OUT NOCOPY NUMBER,
2430 x_msg_data OUT NOCOPY VARCHAR2,
2431 p_pdf_rec IN pdf_rec_type,
2432 x_pdf_rec OUT NOCOPY pdf_rec_type) IS
2433
2434 l_api_version CONSTANT NUMBER := 1;
2435 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2436 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2437 l_pdf_rec pdf_rec_type := p_pdf_rec;
2438 l_def_pdf_rec pdf_rec_type;
2439 -------------------------------------------
2440 -- Set_Attributes for:OKC_PROCESS_DEFS_B --
2441 -------------------------------------------
2442 FUNCTION Set_Attributes (
2443 p_pdf_rec IN pdf_rec_type,
2444 x_pdf_rec OUT NOCOPY pdf_rec_type
2445 ) RETURN VARCHAR2 IS
2446 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2447 BEGIN
2448 x_pdf_rec := p_pdf_rec;
2449 RETURN(l_return_status);
2450 END Set_Attributes;
2451 BEGIN
2452 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2453 p_init_msg_list,
2454 '_PVT',
2455 x_return_status);
2456 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2457 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2458 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2459 RAISE OKC_API.G_EXCEPTION_ERROR;
2460 END IF;
2461 --- Setting item attributes
2462 l_return_status := Set_Attributes(
2463 p_pdf_rec, -- IN
2464 l_pdf_rec); -- OUT
2465 --- If any errors happen abort API
2466 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2467 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2468 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2469 RAISE OKC_API.G_EXCEPTION_ERROR;
2470 END IF;
2471 INSERT INTO OKC_PROCESS_DEFS_B(
2472 id,
2473 pdf_type,
2474 object_version_number,
2475 created_by,
2476 usage,
2477 creation_date,
2478 begin_date,
2479 last_updated_by,
2480 last_update_date,
2481 wf_name,
2482 wf_process_name,
2483 procedure_name,
2484 package_name,
2485 last_update_login,
2486 end_date,
2487 attribute_category,
2488 attribute1,
2489 attribute2,
2490 attribute3,
2491 attribute4,
2492 attribute5,
2493 attribute6,
2494 attribute7,
2495 attribute8,
2496 attribute9,
2497 attribute10,
2498 attribute11,
2499 attribute12,
2500 attribute13,
2501 attribute14,
2502 attribute15,
2503 application_id,
2504 seeded_flag,
2505 message_name,
2506 script_name)
2507 VALUES (
2508 l_pdf_rec.id,
2509 l_pdf_rec.pdf_type,
2510 l_pdf_rec.object_version_number,
2511 l_pdf_rec.created_by,
2512 l_pdf_rec.usage,
2513 l_pdf_rec.creation_date,
2514 l_pdf_rec.begin_date,
2515 l_pdf_rec.last_updated_by,
2516 l_pdf_rec.last_update_date,
2517 l_pdf_rec.wf_name,
2518 l_pdf_rec.wf_process_name,
2519 l_pdf_rec.procedure_name,
2520 l_pdf_rec.package_name,
2521 l_pdf_rec.last_update_login,
2522 l_pdf_rec.end_date,
2523 l_pdf_rec.attribute_category,
2524 l_pdf_rec.attribute1,
2525 l_pdf_rec.attribute2,
2526 l_pdf_rec.attribute3,
2527 l_pdf_rec.attribute4,
2528 l_pdf_rec.attribute5,
2529 l_pdf_rec.attribute6,
2530 l_pdf_rec.attribute7,
2531 l_pdf_rec.attribute8,
2532 l_pdf_rec.attribute9,
2533 l_pdf_rec.attribute10,
2534 l_pdf_rec.attribute11,
2535 l_pdf_rec.attribute12,
2536 l_pdf_rec.attribute13,
2537 l_pdf_rec.attribute14,
2538 l_pdf_rec.attribute15,
2539 l_pdf_rec.application_id,
2540 l_pdf_rec.seeded_flag,
2541 l_pdf_rec.message_name,
2542 l_pdf_rec.script_name);
2543 -- Set OUT values
2544 x_pdf_rec := l_pdf_rec;
2545 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2546 EXCEPTION
2547 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2548 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2549 (
2550 l_api_name,
2551 G_PKG_NAME,
2552 'OKC_API.G_RET_STS_ERROR',
2553 x_msg_count,
2554 x_msg_data,
2555 '_PVT'
2556 );
2557 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2558 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2559 (
2560 l_api_name,
2561 G_PKG_NAME,
2562 'OKC_API.G_RET_STS_UNEXP_ERROR',
2563 x_msg_count,
2564 x_msg_data,
2565 '_PVT'
2566 );
2567 WHEN OTHERS THEN
2568 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2569 (
2570 l_api_name,
2571 G_PKG_NAME,
2572 'OTHERS',
2573 x_msg_count,
2574 x_msg_data,
2575 '_PVT'
2576 );
2577 END insert_row;
2578 ---------------------------------------
2579 -- insert_row for:OKC_PROCESS_DEFS_V --
2580 ---------------------------------------
2581 PROCEDURE insert_row(
2582 p_api_version IN NUMBER,
2583 p_init_msg_list IN VARCHAR2,
2584 x_return_status OUT NOCOPY VARCHAR2,
2585 x_msg_count OUT NOCOPY NUMBER,
2586 x_msg_data OUT NOCOPY VARCHAR2,
2587 p_pdfv_rec IN pdfv_rec_type,
2588 x_pdfv_rec OUT NOCOPY pdfv_rec_type) IS
2589
2590 l_api_version CONSTANT NUMBER := 1;
2591 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2592 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2593 l_pdfv_rec pdfv_rec_type;
2594 l_def_pdfv_rec pdfv_rec_type;
2595 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
2596 lx_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
2597 l_pdf_rec pdf_rec_type;
2598 l_id NUMBER;
2599 lx_pdf_rec pdf_rec_type;
2600 -------------------------------
2601 -- FUNCTION fill_who_columns --
2602 -------------------------------
2603 FUNCTION fill_who_columns (
2604 p_pdfv_rec IN pdfv_rec_type
2605 ) RETURN pdfv_rec_type IS
2606 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
2607 BEGIN
2608 l_pdfv_rec.CREATION_DATE := SYSDATE;
2609 l_pdfv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2610 l_pdfv_rec.LAST_UPDATE_DATE := l_pdfv_rec.CREATION_DATE;
2611 l_pdfv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2612 l_pdfv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2613 RETURN(l_pdfv_rec);
2614 END fill_who_columns;
2615 -------------------------------------------
2616 -- Set_Attributes for:OKC_PROCESS_DEFS_V --
2617 -------------------------------------------
2618 FUNCTION Set_Attributes (
2619 p_pdfv_rec IN pdfv_rec_type,
2620 x_pdfv_rec OUT NOCOPY pdfv_rec_type
2621 ) RETURN VARCHAR2 IS
2622 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2623 BEGIN
2624 x_pdfv_rec := p_pdfv_rec;
2625 x_pdfv_rec.OBJECT_VERSION_NUMBER := 1;
2626 x_pdfv_rec.SFWT_FLAG := 'N';
2627 RETURN(l_return_status);
2628 END Set_Attributes;
2629 BEGIN
2630 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2631 G_PKG_NAME,
2632 p_init_msg_list,
2633 l_api_version,
2634 p_api_version,
2635 '_PVT',
2636 x_return_status);
2637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2640 RAISE OKC_API.G_EXCEPTION_ERROR;
2641 END IF;
2642 l_pdfv_rec := null_out_defaults(p_pdfv_rec);
2643 -- Set primary key value
2644 -- If process definition is created by seed then use sequence generated id
2645 IF l_pdfv_rec.CREATED_BY = 1 THEN
2646 SELECT OKC_PROCESS_DEFS_S1.nextval INTO l_id FROM dual;
2647 l_pdfv_rec.ID := l_id;
2648 l_pdfv_rec.seeded_flag := 'Y';
2649 ELSE
2650 l_pdfv_rec.ID := get_seq_id;
2651 l_pdfv_rec.seeded_flag := 'N';
2652 END IF;
2653 --l_pdfv_rec.ID := get_seq_id;
2654 --- Setting item attributes
2655 l_return_status := Set_Attributes(
2656 l_pdfv_rec, -- IN
2657 l_def_pdfv_rec); -- OUT
2658 --- If any errors happen abort API
2659 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2660 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2661 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2662 RAISE OKC_API.G_EXCEPTION_ERROR;
2663 END IF;
2664 l_def_pdfv_rec := fill_who_columns(l_def_pdfv_rec);
2665 --- Validate all non-missing attributes (Item Level Validation)
2666 l_return_status := Validate_Attributes(l_def_pdfv_rec);
2667 --- If any errors happen abort API
2668 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2669 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2670 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2671 RAISE OKC_API.G_EXCEPTION_ERROR;
2672 END IF;
2673 l_return_status := Validate_Record(l_def_pdfv_rec);
2674 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2675 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2676 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2677 RAISE OKC_API.G_EXCEPTION_ERROR;
2678 END IF;
2679 --------------------------------------
2680 -- Move VIEW record to "Child" records
2681 --------------------------------------
2682 migrate(l_def_pdfv_rec, l_okc_process_defs_tl_rec);
2683 migrate(l_def_pdfv_rec, l_pdf_rec);
2684 --------------------------------------------
2685 -- Call the INSERT_ROW for each child record
2686 --------------------------------------------
2687 insert_row(
2688 p_init_msg_list,
2689 x_return_status,
2690 x_msg_count,
2691 x_msg_data,
2692 l_okc_process_defs_tl_rec,
2693 lx_okc_process_defs_tl_rec
2694 );
2695 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2696 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2697 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2698 RAISE OKC_API.G_EXCEPTION_ERROR;
2699 END IF;
2700 migrate(lx_okc_process_defs_tl_rec, l_def_pdfv_rec);
2701 insert_row(
2702 p_init_msg_list,
2703 x_return_status,
2704 x_msg_count,
2705 x_msg_data,
2706 l_pdf_rec,
2707 lx_pdf_rec
2708 );
2709 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2710 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2711 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2712 RAISE OKC_API.G_EXCEPTION_ERROR;
2713 END IF;
2714 migrate(lx_pdf_rec, l_def_pdfv_rec);
2715 -- Set OUT values
2716 x_pdfv_rec := l_def_pdfv_rec;
2717 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2718 EXCEPTION
2719 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2720 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2721 (
2722 l_api_name,
2723 G_PKG_NAME,
2724 'OKC_API.G_RET_STS_ERROR',
2725 x_msg_count,
2726 x_msg_data,
2727 '_PVT'
2728 );
2729 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2730 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2731 (
2732 l_api_name,
2733 G_PKG_NAME,
2734 'OKC_API.G_RET_STS_UNEXP_ERROR',
2735 x_msg_count,
2736 x_msg_data,
2737 '_PVT'
2738 );
2739 WHEN OTHERS THEN
2740 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2741 (
2742 l_api_name,
2743 G_PKG_NAME,
2744 'OTHERS',
2745 x_msg_count,
2746 x_msg_data,
2747 '_PVT'
2748 );
2749 END insert_row;
2750 ----------------------------------------
2751 -- PL/SQL TBL insert_row for:PDFV_TBL --
2752 ----------------------------------------
2753 PROCEDURE insert_row(
2754 p_api_version IN NUMBER,
2755 p_init_msg_list IN VARCHAR2,
2756 x_return_status OUT NOCOPY VARCHAR2,
2757 x_msg_count OUT NOCOPY NUMBER,
2758 x_msg_data OUT NOCOPY VARCHAR2,
2759 p_pdfv_tbl IN pdfv_tbl_type,
2760 x_pdfv_tbl OUT NOCOPY pdfv_tbl_type) IS
2761
2762 l_api_version CONSTANT NUMBER := 1;
2763 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2764 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2765 i NUMBER := 0;
2766 BEGIN
2767 OKC_API.init_msg_list(p_init_msg_list);
2768 -- Make sure PL/SQL table has records in it before passing
2769 IF (p_pdfv_tbl.COUNT > 0) THEN
2770 i := p_pdfv_tbl.FIRST;
2771 LOOP
2772 insert_row (
2773 p_api_version => p_api_version,
2774 p_init_msg_list => OKC_API.G_FALSE,
2775 x_return_status => x_return_status,
2776 x_msg_count => x_msg_count,
2777 x_msg_data => x_msg_data,
2778 p_pdfv_rec => p_pdfv_tbl(i),
2779 x_pdfv_rec => x_pdfv_tbl(i));
2780 EXIT WHEN (i = p_pdfv_tbl.LAST);
2781 i := p_pdfv_tbl.NEXT(i);
2782 END LOOP;
2783 END IF;
2784 EXCEPTION
2785 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2786 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2787 (
2788 l_api_name,
2789 G_PKG_NAME,
2790 'OKC_API.G_RET_STS_ERROR',
2791 x_msg_count,
2792 x_msg_data,
2793 '_PVT'
2794 );
2795 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2796 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2797 (
2798 l_api_name,
2799 G_PKG_NAME,
2800 'OKC_API.G_RET_STS_UNEXP_ERROR',
2801 x_msg_count,
2802 x_msg_data,
2803 '_PVT'
2804 );
2805 WHEN OTHERS THEN
2806 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2807 (
2808 l_api_name,
2809 G_PKG_NAME,
2810 'OTHERS',
2811 x_msg_count,
2812 x_msg_data,
2813 '_PVT'
2814 );
2815 END insert_row;
2816
2817 ---------------------------------------------------------------------------
2818 -- PROCEDURE lock_row
2819 ---------------------------------------------------------------------------
2820 --------------------------------------
2821 -- lock_row for:OKC_PROCESS_DEFS_TL --
2822 --------------------------------------
2823 PROCEDURE lock_row(
2824 p_init_msg_list IN VARCHAR2,
2825 x_return_status OUT NOCOPY VARCHAR2,
2826 x_msg_count OUT NOCOPY NUMBER,
2827 x_msg_data OUT NOCOPY VARCHAR2,
2828 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type) IS
2829
2830 E_Resource_Busy EXCEPTION;
2831 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2832 CURSOR lock_csr (p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type) IS
2833 SELECT *
2834 FROM OKC_PROCESS_DEFS_TL
2835 WHERE ID = p_okc_process_defs_tl_rec.id
2836 FOR UPDATE NOWAIT;
2837
2838 l_api_version CONSTANT NUMBER := 1;
2839 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2840 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2841 l_lock_var lock_csr%ROWTYPE;
2842 l_row_notfound BOOLEAN := FALSE;
2843 lc_row_notfound BOOLEAN := FALSE;
2844 BEGIN
2845 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2846 p_init_msg_list,
2847 '_PVT',
2848 x_return_status);
2849 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2850 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2851 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2852 RAISE OKC_API.G_EXCEPTION_ERROR;
2853 END IF;
2854 BEGIN
2855 OPEN lock_csr(p_okc_process_defs_tl_rec);
2856 FETCH lock_csr INTO l_lock_var;
2857 l_row_notfound := lock_csr%NOTFOUND;
2858 CLOSE lock_csr;
2859 EXCEPTION
2860 WHEN E_Resource_Busy THEN
2861 IF (lock_csr%ISOPEN) THEN
2862 CLOSE lock_csr;
2863 END IF;
2864 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2865 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2866 END;
2867
2868 IF ( l_row_notfound ) THEN
2869 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2870 RAISE OKC_API.G_EXCEPTION_ERROR;
2871 END IF;
2872 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2873 EXCEPTION
2874 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2875 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2876 (
2877 l_api_name,
2878 G_PKG_NAME,
2879 'OKC_API.G_RET_STS_ERROR',
2880 x_msg_count,
2881 x_msg_data,
2882 '_PVT'
2883 );
2884 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2885 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2886 (
2887 l_api_name,
2888 G_PKG_NAME,
2889 'OKC_API.G_RET_STS_UNEXP_ERROR',
2890 x_msg_count,
2891 x_msg_data,
2892 '_PVT'
2893 );
2894 WHEN OTHERS THEN
2895 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2896 (
2897 l_api_name,
2898 G_PKG_NAME,
2899 'OTHERS',
2900 x_msg_count,
2901 x_msg_data,
2902 '_PVT'
2903 );
2904 END lock_row;
2905 -------------------------------------
2906 -- lock_row for:OKC_PROCESS_DEFS_B --
2907 -------------------------------------
2908 PROCEDURE lock_row(
2909 p_init_msg_list IN VARCHAR2,
2910 x_return_status OUT NOCOPY VARCHAR2,
2911 x_msg_count OUT NOCOPY NUMBER,
2912 x_msg_data OUT NOCOPY VARCHAR2,
2913 p_pdf_rec IN pdf_rec_type) IS
2914
2915 E_Resource_Busy EXCEPTION;
2916 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2917 CURSOR lock_csr (p_pdf_rec IN pdf_rec_type) IS
2918 SELECT OBJECT_VERSION_NUMBER
2919 FROM OKC_PROCESS_DEFS_B
2920 WHERE ID = p_pdf_rec.id
2921 AND OBJECT_VERSION_NUMBER = p_pdf_rec.object_version_number
2922 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2923
2924 CURSOR lchk_csr (p_pdf_rec IN pdf_rec_type) IS
2925 SELECT OBJECT_VERSION_NUMBER
2926 FROM OKC_PROCESS_DEFS_B
2927 WHERE ID = p_pdf_rec.id;
2928 l_api_version CONSTANT NUMBER := 1;
2929 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2930 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2931 l_object_version_number OKC_PROCESS_DEFS_B.OBJECT_VERSION_NUMBER%TYPE;
2932 lc_object_version_number OKC_PROCESS_DEFS_B.OBJECT_VERSION_NUMBER%TYPE;
2933 l_row_notfound BOOLEAN := FALSE;
2934 lc_row_notfound BOOLEAN := FALSE;
2935 BEGIN
2936 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2937 p_init_msg_list,
2938 '_PVT',
2939 x_return_status);
2940 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2941 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2942 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2943 RAISE OKC_API.G_EXCEPTION_ERROR;
2944 END IF;
2945 BEGIN
2946 OPEN lock_csr(p_pdf_rec);
2947 FETCH lock_csr INTO l_object_version_number;
2948 l_row_notfound := lock_csr%NOTFOUND;
2949 CLOSE lock_csr;
2950 EXCEPTION
2951 WHEN E_Resource_Busy THEN
2952 IF (lock_csr%ISOPEN) THEN
2953 CLOSE lock_csr;
2954 END IF;
2955 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2956 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2957 END;
2958
2959 IF ( l_row_notfound ) THEN
2960 OPEN lchk_csr(p_pdf_rec);
2961 FETCH lchk_csr INTO lc_object_version_number;
2962 lc_row_notfound := lchk_csr%NOTFOUND;
2963 CLOSE lchk_csr;
2964 END IF;
2965 IF (lc_row_notfound) THEN
2966 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2967 RAISE OKC_API.G_EXCEPTION_ERROR;
2968 ELSIF lc_object_version_number > p_pdf_rec.object_version_number THEN
2969 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 ELSIF lc_object_version_number <> p_pdf_rec.object_version_number THEN
2972 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2973 RAISE OKC_API.G_EXCEPTION_ERROR;
2974 ELSIF lc_object_version_number = -1 THEN
2975 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2976 RAISE OKC_API.G_EXCEPTION_ERROR;
2977 END IF;
2978 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2979 EXCEPTION
2980 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2981 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2982 (
2983 l_api_name,
2984 G_PKG_NAME,
2985 'OKC_API.G_RET_STS_ERROR',
2986 x_msg_count,
2987 x_msg_data,
2988 '_PVT'
2989 );
2990 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2991 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2992 (
2993 l_api_name,
2994 G_PKG_NAME,
2995 'OKC_API.G_RET_STS_UNEXP_ERROR',
2996 x_msg_count,
2997 x_msg_data,
2998 '_PVT'
2999 );
3000 WHEN OTHERS THEN
3001 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3002 (
3003 l_api_name,
3004 G_PKG_NAME,
3005 'OTHERS',
3006 x_msg_count,
3007 x_msg_data,
3008 '_PVT'
3009 );
3010 END lock_row;
3011 -------------------------------------
3012 -- lock_row for:OKC_PROCESS_DEFS_V --
3013 -------------------------------------
3014 PROCEDURE lock_row(
3015 p_api_version IN NUMBER,
3016 p_init_msg_list IN VARCHAR2,
3017 x_return_status OUT NOCOPY VARCHAR2,
3018 x_msg_count OUT NOCOPY NUMBER,
3019 x_msg_data OUT NOCOPY VARCHAR2,
3020 p_pdfv_rec IN pdfv_rec_type) IS
3021
3022 l_api_version CONSTANT NUMBER := 1;
3023 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3024 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3025 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
3026 l_pdf_rec pdf_rec_type;
3027 BEGIN
3028 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3029 G_PKG_NAME,
3030 p_init_msg_list,
3031 l_api_version,
3032 p_api_version,
3033 '_PVT',
3034 x_return_status);
3035 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3038 RAISE OKC_API.G_EXCEPTION_ERROR;
3039 END IF;
3040 --------------------------------------
3041 -- Move VIEW record to "Child" records
3042 --------------------------------------
3043 migrate(p_pdfv_rec, l_okc_process_defs_tl_rec);
3044 migrate(p_pdfv_rec, l_pdf_rec);
3045 --------------------------------------------
3046 -- Call the LOCK_ROW for each child record
3047 --------------------------------------------
3048 lock_row(
3049 p_init_msg_list,
3050 x_return_status,
3051 x_msg_count,
3052 x_msg_data,
3053 l_okc_process_defs_tl_rec
3054 );
3055 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3056 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3057 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3058 RAISE OKC_API.G_EXCEPTION_ERROR;
3059 END IF;
3060 lock_row(
3061 p_init_msg_list,
3062 x_return_status,
3063 x_msg_count,
3064 x_msg_data,
3065 l_pdf_rec
3066 );
3067 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3068 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3069 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3070 RAISE OKC_API.G_EXCEPTION_ERROR;
3071 END IF;
3072 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3073 EXCEPTION
3074 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3075 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3076 (
3077 l_api_name,
3078 G_PKG_NAME,
3079 'OKC_API.G_RET_STS_ERROR',
3080 x_msg_count,
3081 x_msg_data,
3082 '_PVT'
3083 );
3084 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3085 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3086 (
3087 l_api_name,
3088 G_PKG_NAME,
3089 'OKC_API.G_RET_STS_UNEXP_ERROR',
3090 x_msg_count,
3091 x_msg_data,
3092 '_PVT'
3093 );
3094 WHEN OTHERS THEN
3095 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3096 (
3097 l_api_name,
3098 G_PKG_NAME,
3099 'OTHERS',
3100 x_msg_count,
3101 x_msg_data,
3102 '_PVT'
3103 );
3104 END lock_row;
3105 --------------------------------------
3106 -- PL/SQL TBL lock_row for:PDFV_TBL --
3107 --------------------------------------
3108 PROCEDURE lock_row(
3109 p_api_version IN NUMBER,
3110 p_init_msg_list IN VARCHAR2,
3111 x_return_status OUT NOCOPY VARCHAR2,
3112 x_msg_count OUT NOCOPY NUMBER,
3113 x_msg_data OUT NOCOPY VARCHAR2,
3114 p_pdfv_tbl IN pdfv_tbl_type) IS
3115
3116 l_api_version CONSTANT NUMBER := 1;
3117 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3118 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3119 i NUMBER := 0;
3120 BEGIN
3121 OKC_API.init_msg_list(p_init_msg_list);
3122 -- Make sure PL/SQL table has records in it before passing
3123 IF (p_pdfv_tbl.COUNT > 0) THEN
3124 i := p_pdfv_tbl.FIRST;
3125 LOOP
3126 lock_row (
3127 p_api_version => p_api_version,
3128 p_init_msg_list => OKC_API.G_FALSE,
3129 x_return_status => x_return_status,
3130 x_msg_count => x_msg_count,
3131 x_msg_data => x_msg_data,
3132 p_pdfv_rec => p_pdfv_tbl(i));
3133 EXIT WHEN (i = p_pdfv_tbl.LAST);
3134 i := p_pdfv_tbl.NEXT(i);
3135 END LOOP;
3136 END IF;
3137 EXCEPTION
3138 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3139 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3140 (
3141 l_api_name,
3142 G_PKG_NAME,
3143 'OKC_API.G_RET_STS_ERROR',
3144 x_msg_count,
3145 x_msg_data,
3146 '_PVT'
3147 );
3148 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3149 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3150 (
3151 l_api_name,
3152 G_PKG_NAME,
3153 'OKC_API.G_RET_STS_UNEXP_ERROR',
3154 x_msg_count,
3155 x_msg_data,
3156 '_PVT'
3157 );
3158 WHEN OTHERS THEN
3159 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3160 (
3161 l_api_name,
3162 G_PKG_NAME,
3163 'OTHERS',
3164 x_msg_count,
3165 x_msg_data,
3166 '_PVT'
3167 );
3168 END lock_row;
3169
3170 ---------------------------------------------------------------------------
3171 -- PROCEDURE update_row
3172 ---------------------------------------------------------------------------
3173 ----------------------------------------
3174 -- update_row for:OKC_PROCESS_DEFS_TL --
3175 ----------------------------------------
3176 PROCEDURE update_row(
3177 p_init_msg_list IN VARCHAR2,
3178 x_return_status OUT NOCOPY VARCHAR2,
3179 x_msg_count OUT NOCOPY NUMBER,
3180 x_msg_data OUT NOCOPY VARCHAR2,
3181 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
3182 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type) IS
3183
3184 l_api_version CONSTANT NUMBER := 1;
3185 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3186 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3187 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type := p_okc_process_defs_tl_rec;
3188 ldefokcprocessdefstlrec okc_process_defs_tl_rec_type;
3189 l_row_notfound BOOLEAN := TRUE;
3190 ----------------------------------
3191 -- FUNCTION populate_new_record --
3192 ----------------------------------
3193 FUNCTION populate_new_record (
3194 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
3195 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type
3196 ) RETURN VARCHAR2 IS
3197 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
3198 l_row_notfound BOOLEAN := TRUE;
3199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3200 BEGIN
3201 x_okc_process_defs_tl_rec := p_okc_process_defs_tl_rec;
3202 -- Get current database values
3203 l_okc_process_defs_tl_rec := get_rec(p_okc_process_defs_tl_rec, l_row_notfound);
3204 IF (l_row_notfound) THEN
3205 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3206 END IF;
3207 IF (x_okc_process_defs_tl_rec.id = OKC_API.G_MISS_NUM)
3208 THEN
3209 x_okc_process_defs_tl_rec.id := l_okc_process_defs_tl_rec.id;
3210 END IF;
3211 IF (x_okc_process_defs_tl_rec.language = OKC_API.G_MISS_CHAR)
3212 THEN
3213 x_okc_process_defs_tl_rec.language := l_okc_process_defs_tl_rec.language;
3214 END IF;
3215 IF (x_okc_process_defs_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
3216 THEN
3217 x_okc_process_defs_tl_rec.source_lang := l_okc_process_defs_tl_rec.source_lang;
3218 END IF;
3219 IF (x_okc_process_defs_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3220 THEN
3221 x_okc_process_defs_tl_rec.sfwt_flag := l_okc_process_defs_tl_rec.sfwt_flag;
3222 END IF;
3223 IF (x_okc_process_defs_tl_rec.name = OKC_API.G_MISS_CHAR)
3224 THEN
3225 x_okc_process_defs_tl_rec.name := l_okc_process_defs_tl_rec.name;
3226 END IF;
3227 IF (x_okc_process_defs_tl_rec.description = OKC_API.G_MISS_CHAR)
3228 THEN
3229 x_okc_process_defs_tl_rec.description := l_okc_process_defs_tl_rec.description;
3230 END IF;
3231 IF (x_okc_process_defs_tl_rec.short_description = OKC_API.G_MISS_CHAR)
3232 THEN
3233 x_okc_process_defs_tl_rec.short_description := l_okc_process_defs_tl_rec.short_description;
3234 END IF;
3235 IF (x_okc_process_defs_tl_rec.comments = OKC_API.G_MISS_CHAR)
3236 THEN
3237 x_okc_process_defs_tl_rec.comments := l_okc_process_defs_tl_rec.comments;
3238 END IF;
3239 IF (x_okc_process_defs_tl_rec.created_by = OKC_API.G_MISS_NUM)
3240 THEN
3241 x_okc_process_defs_tl_rec.created_by := l_okc_process_defs_tl_rec.created_by;
3242 END IF;
3243 IF (x_okc_process_defs_tl_rec.creation_date = OKC_API.G_MISS_DATE)
3244 THEN
3245 x_okc_process_defs_tl_rec.creation_date := l_okc_process_defs_tl_rec.creation_date;
3246 END IF;
3247 IF (x_okc_process_defs_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3248 THEN
3249 x_okc_process_defs_tl_rec.last_updated_by := l_okc_process_defs_tl_rec.last_updated_by;
3250 END IF;
3251 IF (x_okc_process_defs_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
3252 THEN
3253 x_okc_process_defs_tl_rec.last_update_date := l_okc_process_defs_tl_rec.last_update_date;
3254 END IF;
3255 IF (x_okc_process_defs_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
3256 THEN
3257 x_okc_process_defs_tl_rec.last_update_login := l_okc_process_defs_tl_rec.last_update_login;
3258 END IF;
3259 RETURN(l_return_status);
3260 END populate_new_record;
3261 --------------------------------------------
3262 -- Set_Attributes for:OKC_PROCESS_DEFS_TL --
3263 --------------------------------------------
3264 FUNCTION Set_Attributes (
3265 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
3266 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type
3267 ) RETURN VARCHAR2 IS
3268 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3269 BEGIN
3270 x_okc_process_defs_tl_rec := p_okc_process_defs_tl_rec;
3271 x_okc_process_defs_tl_rec.LANGUAGE := l_lang;
3272 x_okc_process_defs_tl_rec.SOURCE_LANG := l_lang;
3273 RETURN(l_return_status);
3274 END Set_Attributes;
3275 BEGIN
3276 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3277 p_init_msg_list,
3278 '_PVT',
3279 x_return_status);
3280 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3281 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3282 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3283 RAISE OKC_API.G_EXCEPTION_ERROR;
3284 END IF;
3285 --- Setting item attributes
3286 l_return_status := Set_Attributes(
3287 p_okc_process_defs_tl_rec, -- IN
3288 l_okc_process_defs_tl_rec); -- OUT
3289 --- If any errors happen abort API
3290 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3291 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3292 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3293 RAISE OKC_API.G_EXCEPTION_ERROR;
3294 END IF;
3295 l_return_status := populate_new_record(l_okc_process_defs_tl_rec, ldefokcprocessdefstlrec);
3296 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3297 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3298 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3299 RAISE OKC_API.G_EXCEPTION_ERROR;
3300 END IF;
3301 UPDATE OKC_PROCESS_DEFS_TL
3302 SET NAME = ldefokcprocessdefstlrec.name,
3303 SOURCE_LANG = ldefokcprocessdefstlrec.source_lang,
3304 DESCRIPTION = ldefokcprocessdefstlrec.description,
3305 SHORT_DESCRIPTION = ldefokcprocessdefstlrec.short_description,
3306 COMMENTS = ldefokcprocessdefstlrec.comments,
3307 CREATED_BY = ldefokcprocessdefstlrec.created_by,
3308 CREATION_DATE = ldefokcprocessdefstlrec.creation_date,
3309 LAST_UPDATED_BY = ldefokcprocessdefstlrec.last_updated_by,
3310 LAST_UPDATE_DATE = ldefokcprocessdefstlrec.last_update_date,
3311 LAST_UPDATE_LOGIN = ldefokcprocessdefstlrec.last_update_login
3312 WHERE ID = ldefokcprocessdefstlrec.id
3313 AND USERENV('LANG') IN (SOURCE_LANG, LANGUAGE);
3314 --AND SOURCE_LANG = USERENV('LANG');
3315
3316 UPDATE OKC_PROCESS_DEFS_TL
3317 SET SFWT_FLAG = 'Y'
3318 WHERE ID = ldefokcprocessdefstlrec.id
3319 AND SOURCE_LANG <> USERENV('LANG');
3320
3321 x_okc_process_defs_tl_rec := ldefokcprocessdefstlrec;
3322 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3323 EXCEPTION
3324 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3325 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3326 (
3327 l_api_name,
3328 G_PKG_NAME,
3329 'OKC_API.G_RET_STS_ERROR',
3330 x_msg_count,
3331 x_msg_data,
3332 '_PVT'
3333 );
3334 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3335 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3336 (
3337 l_api_name,
3338 G_PKG_NAME,
3339 'OKC_API.G_RET_STS_UNEXP_ERROR',
3340 x_msg_count,
3341 x_msg_data,
3342 '_PVT'
3343 );
3344 WHEN OTHERS THEN
3345 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3346 (
3347 l_api_name,
3348 G_PKG_NAME,
3349 'OTHERS',
3350 x_msg_count,
3351 x_msg_data,
3352 '_PVT'
3353 );
3354 END update_row;
3355 ---------------------------------------
3356 -- update_row for:OKC_PROCESS_DEFS_B --
3357 ---------------------------------------
3358 PROCEDURE update_row(
3359 p_init_msg_list IN VARCHAR2,
3360 x_return_status OUT NOCOPY VARCHAR2,
3361 x_msg_count OUT NOCOPY NUMBER,
3362 x_msg_data OUT NOCOPY VARCHAR2,
3363 p_pdf_rec IN pdf_rec_type,
3364 x_pdf_rec OUT NOCOPY pdf_rec_type) IS
3365
3366 l_api_version CONSTANT NUMBER := 1;
3367 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3368 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3369 l_pdf_rec pdf_rec_type := p_pdf_rec;
3370 l_def_pdf_rec pdf_rec_type;
3371 l_row_notfound BOOLEAN := TRUE;
3372 ----------------------------------
3373 -- FUNCTION populate_new_record --
3374 ----------------------------------
3375 FUNCTION populate_new_record (
3376 p_pdf_rec IN pdf_rec_type,
3377 x_pdf_rec OUT NOCOPY pdf_rec_type
3378 ) RETURN VARCHAR2 IS
3379 l_pdf_rec pdf_rec_type;
3380 l_row_notfound BOOLEAN := TRUE;
3381 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3382 BEGIN
3383 x_pdf_rec := p_pdf_rec;
3384 -- Get current database values
3385 l_pdf_rec := get_rec(p_pdf_rec, l_row_notfound);
3386 IF (l_row_notfound) THEN
3387 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3388 END IF;
3389 IF (x_pdf_rec.id = OKC_API.G_MISS_NUM)
3390 THEN
3391 x_pdf_rec.id := l_pdf_rec.id;
3392 END IF;
3393 IF (x_pdf_rec.pdf_type = OKC_API.G_MISS_CHAR)
3394 THEN
3395 x_pdf_rec.pdf_type := l_pdf_rec.pdf_type;
3396 END IF;
3397 IF (x_pdf_rec.object_version_number = OKC_API.G_MISS_NUM)
3398 THEN
3399 x_pdf_rec.object_version_number := l_pdf_rec.object_version_number;
3400 END IF;
3401 IF (x_pdf_rec.created_by = OKC_API.G_MISS_NUM)
3402 THEN
3403 x_pdf_rec.created_by := l_pdf_rec.created_by;
3404 END IF;
3405 IF (x_pdf_rec.usage = OKC_API.G_MISS_CHAR)
3406 THEN
3407 x_pdf_rec.usage := l_pdf_rec.usage;
3408 END IF;
3409 IF (x_pdf_rec.creation_date = OKC_API.G_MISS_DATE)
3410 THEN
3411 x_pdf_rec.creation_date := l_pdf_rec.creation_date;
3412 END IF;
3413 IF (x_pdf_rec.begin_date = OKC_API.G_MISS_DATE)
3414 THEN
3415 x_pdf_rec.begin_date := l_pdf_rec.begin_date;
3416 END IF;
3417 IF (x_pdf_rec.last_updated_by = OKC_API.G_MISS_NUM)
3418 THEN
3419 x_pdf_rec.last_updated_by := l_pdf_rec.last_updated_by;
3420 END IF;
3421 IF (x_pdf_rec.last_update_date = OKC_API.G_MISS_DATE)
3422 THEN
3423 x_pdf_rec.last_update_date := l_pdf_rec.last_update_date;
3424 END IF;
3425 IF (x_pdf_rec.wf_name = OKC_API.G_MISS_CHAR)
3426 THEN
3427 x_pdf_rec.wf_name := l_pdf_rec.wf_name;
3428 END IF;
3429 IF (x_pdf_rec.wf_process_name = OKC_API.G_MISS_CHAR)
3430 THEN
3431 x_pdf_rec.wf_process_name := l_pdf_rec.wf_process_name;
3432 END IF;
3433 IF (x_pdf_rec.procedure_name = OKC_API.G_MISS_CHAR)
3434 THEN
3435 x_pdf_rec.procedure_name := l_pdf_rec.procedure_name;
3436 END IF;
3437 IF (x_pdf_rec.package_name = OKC_API.G_MISS_CHAR)
3438 THEN
3439 x_pdf_rec.package_name := l_pdf_rec.package_name;
3440 END IF;
3441 IF (x_pdf_rec.last_update_login = OKC_API.G_MISS_NUM)
3442 THEN
3443 x_pdf_rec.last_update_login := l_pdf_rec.last_update_login;
3444 END IF;
3445 IF (x_pdf_rec.end_date = OKC_API.G_MISS_DATE)
3446 THEN
3447 x_pdf_rec.end_date := l_pdf_rec.end_date;
3448 END IF;
3449 IF (x_pdf_rec.attribute_category = OKC_API.G_MISS_CHAR)
3450 THEN
3451 x_pdf_rec.attribute_category := l_pdf_rec.attribute_category;
3452 END IF;
3453 IF (x_pdf_rec.attribute1 = OKC_API.G_MISS_CHAR)
3454 THEN
3455 x_pdf_rec.attribute1 := l_pdf_rec.attribute1;
3456 END IF;
3457 IF (x_pdf_rec.attribute2 = OKC_API.G_MISS_CHAR)
3458 THEN
3459 x_pdf_rec.attribute2 := l_pdf_rec.attribute2;
3460 END IF;
3461 IF (x_pdf_rec.attribute3 = OKC_API.G_MISS_CHAR)
3462 THEN
3463 x_pdf_rec.attribute3 := l_pdf_rec.attribute3;
3464 END IF;
3465 IF (x_pdf_rec.attribute4 = OKC_API.G_MISS_CHAR)
3466 THEN
3467 x_pdf_rec.attribute4 := l_pdf_rec.attribute4;
3468 END IF;
3469 IF (x_pdf_rec.attribute5 = OKC_API.G_MISS_CHAR)
3470 THEN
3471 x_pdf_rec.attribute5 := l_pdf_rec.attribute5;
3472 END IF;
3473 IF (x_pdf_rec.attribute6 = OKC_API.G_MISS_CHAR)
3474 THEN
3475 x_pdf_rec.attribute6 := l_pdf_rec.attribute6;
3476 END IF;
3477 IF (x_pdf_rec.attribute7 = OKC_API.G_MISS_CHAR)
3478 THEN
3479 x_pdf_rec.attribute7 := l_pdf_rec.attribute7;
3480 END IF;
3481 IF (x_pdf_rec.attribute8 = OKC_API.G_MISS_CHAR)
3482 THEN
3483 x_pdf_rec.attribute8 := l_pdf_rec.attribute8;
3484 END IF;
3485 IF (x_pdf_rec.attribute9 = OKC_API.G_MISS_CHAR)
3486 THEN
3487 x_pdf_rec.attribute9 := l_pdf_rec.attribute9;
3488 END IF;
3489 IF (x_pdf_rec.attribute10 = OKC_API.G_MISS_CHAR)
3490 THEN
3491 x_pdf_rec.attribute10 := l_pdf_rec.attribute10;
3492 END IF;
3493 IF (x_pdf_rec.attribute11 = OKC_API.G_MISS_CHAR)
3494 THEN
3495 x_pdf_rec.attribute11 := l_pdf_rec.attribute11;
3496 END IF;
3497 IF (x_pdf_rec.attribute12 = OKC_API.G_MISS_CHAR)
3498 THEN
3499 x_pdf_rec.attribute12 := l_pdf_rec.attribute12;
3500 END IF;
3501 IF (x_pdf_rec.attribute13 = OKC_API.G_MISS_CHAR)
3502 THEN
3503 x_pdf_rec.attribute13 := l_pdf_rec.attribute13;
3504 END IF;
3505 IF (x_pdf_rec.attribute14 = OKC_API.G_MISS_CHAR)
3506 THEN
3507 x_pdf_rec.attribute14 := l_pdf_rec.attribute14;
3508 END IF;
3509 IF (x_pdf_rec.attribute15 = OKC_API.G_MISS_CHAR)
3510 THEN
3511 x_pdf_rec.attribute15 := l_pdf_rec.attribute15;
3512 END IF;
3513 IF (x_pdf_rec.application_id = OKC_API.G_MISS_NUM)
3514 THEN
3515 x_pdf_rec.application_id := l_pdf_rec.application_id;
3516 END IF;
3517 IF (x_pdf_rec.seeded_flag = OKC_API.G_MISS_CHAR)
3518 THEN
3519 x_pdf_rec.seeded_flag := l_pdf_rec.seeded_flag;
3520 END IF;
3521 IF (x_pdf_rec.message_name = OKC_API.G_MISS_CHAR)
3522 THEN
3523 x_pdf_rec.message_name := l_pdf_rec.message_name;
3524 END IF;
3525 IF (x_pdf_rec.script_name = OKC_API.G_MISS_CHAR)
3526 THEN
3527 x_pdf_rec.script_name := l_pdf_rec.script_name;
3528 END IF;
3529 RETURN(l_return_status);
3530 END populate_new_record;
3531 -------------------------------------------
3532 -- Set_Attributes for:OKC_PROCESS_DEFS_B --
3533 -------------------------------------------
3534 FUNCTION Set_Attributes (
3535 p_pdf_rec IN pdf_rec_type,
3536 x_pdf_rec OUT NOCOPY pdf_rec_type
3537 ) RETURN VARCHAR2 IS
3538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3539 BEGIN
3540 x_pdf_rec := p_pdf_rec;
3541 RETURN(l_return_status);
3542 END Set_Attributes;
3543 BEGIN
3544 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3545 p_init_msg_list,
3546 '_PVT',
3547 x_return_status);
3548 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3551 RAISE OKC_API.G_EXCEPTION_ERROR;
3552 END IF;
3553 --- Setting item attributes
3554 l_return_status := Set_Attributes(
3555 p_pdf_rec, -- IN
3556 l_pdf_rec); -- OUT
3557 --- If any errors happen abort API
3558 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3559 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3560 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3561 RAISE OKC_API.G_EXCEPTION_ERROR;
3562 END IF;
3563 l_return_status := populate_new_record(l_pdf_rec, l_def_pdf_rec);
3564 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3566 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3567 RAISE OKC_API.G_EXCEPTION_ERROR;
3568 END IF;
3569 UPDATE OKC_PROCESS_DEFS_B
3570 SET PDF_TYPE = l_def_pdf_rec.pdf_type,
3571 OBJECT_VERSION_NUMBER = l_def_pdf_rec.object_version_number,
3572 CREATED_BY = l_def_pdf_rec.created_by,
3573 USAGE = l_def_pdf_rec.usage,
3574 CREATION_DATE = l_def_pdf_rec.creation_date,
3575 BEGIN_DATE = l_def_pdf_rec.begin_date,
3576 LAST_UPDATED_BY = l_def_pdf_rec.last_updated_by,
3577 LAST_UPDATE_DATE = l_def_pdf_rec.last_update_date,
3578 WF_NAME = l_def_pdf_rec.wf_name,
3579 WF_PROCESS_NAME = l_def_pdf_rec.wf_process_name,
3580 PROCEDURE_NAME = l_def_pdf_rec.procedure_name,
3581 PACKAGE_NAME = l_def_pdf_rec.package_name,
3582 LAST_UPDATE_LOGIN = l_def_pdf_rec.last_update_login,
3583 END_DATE = l_def_pdf_rec.end_date,
3584 ATTRIBUTE_CATEGORY = l_def_pdf_rec.attribute_category,
3585 ATTRIBUTE1 = l_def_pdf_rec.attribute1,
3586 ATTRIBUTE2 = l_def_pdf_rec.attribute2,
3587 ATTRIBUTE3 = l_def_pdf_rec.attribute3,
3588 ATTRIBUTE4 = l_def_pdf_rec.attribute4,
3589 ATTRIBUTE5 = l_def_pdf_rec.attribute5,
3590 ATTRIBUTE6 = l_def_pdf_rec.attribute6,
3591 ATTRIBUTE7 = l_def_pdf_rec.attribute7,
3592 ATTRIBUTE8 = l_def_pdf_rec.attribute8,
3593 ATTRIBUTE9 = l_def_pdf_rec.attribute9,
3594 ATTRIBUTE10 = l_def_pdf_rec.attribute10,
3595 ATTRIBUTE11 = l_def_pdf_rec.attribute11,
3596 ATTRIBUTE12 = l_def_pdf_rec.attribute12,
3597 ATTRIBUTE13 = l_def_pdf_rec.attribute13,
3598 ATTRIBUTE14 = l_def_pdf_rec.attribute14,
3599 ATTRIBUTE15 = l_def_pdf_rec.attribute15,
3600 APPLICATION_ID = l_def_pdf_rec.application_id,
3601 SEEDED_FLAG = l_def_pdf_rec.seeded_flag,
3602 MESSAGE_NAME = l_def_pdf_rec.MESSAGE_NAME,
3603 SCRIPT_NAME = l_def_pdf_rec.SCRIPT_NAME
3604 WHERE ID = l_def_pdf_rec.id;
3605
3606 x_pdf_rec := l_def_pdf_rec;
3607 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3608 EXCEPTION
3609 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3610 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3611 (
3612 l_api_name,
3613 G_PKG_NAME,
3614 'OKC_API.G_RET_STS_ERROR',
3615 x_msg_count,
3616 x_msg_data,
3617 '_PVT'
3618 );
3619 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3620 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3621 (
3622 l_api_name,
3623 G_PKG_NAME,
3624 'OKC_API.G_RET_STS_UNEXP_ERROR',
3625 x_msg_count,
3626 x_msg_data,
3627 '_PVT'
3628 );
3629 WHEN OTHERS THEN
3630 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OTHERS',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 END update_row;
3640 ---------------------------------------
3641 -- update_row for:OKC_PROCESS_DEFS_V --
3642 ---------------------------------------
3643 PROCEDURE update_row(
3644 p_api_version IN NUMBER,
3645 p_init_msg_list IN VARCHAR2,
3646 x_return_status OUT NOCOPY VARCHAR2,
3647 x_msg_count OUT NOCOPY NUMBER,
3648 x_msg_data OUT NOCOPY VARCHAR2,
3649 p_pdfv_rec IN pdfv_rec_type,
3650 x_pdfv_rec OUT NOCOPY pdfv_rec_type) IS
3651
3652 l_api_version CONSTANT NUMBER := 1;
3653 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3654 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3655 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
3656 l_def_pdfv_rec pdfv_rec_type;
3657 l_pdf_rec pdf_rec_type;
3658 lx_pdf_rec pdf_rec_type;
3659 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
3660 lx_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
3661 -------------------------------
3662 -- FUNCTION fill_who_columns --
3663 -------------------------------
3664 FUNCTION fill_who_columns (
3665 p_pdfv_rec IN pdfv_rec_type
3666 ) RETURN pdfv_rec_type IS
3667 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
3668 BEGIN
3669 l_pdfv_rec.LAST_UPDATE_DATE := SYSDATE;
3670 l_pdfv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3671 l_pdfv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3672 RETURN(l_pdfv_rec);
3673 END fill_who_columns;
3674 ----------------------------------
3675 -- FUNCTION populate_new_record --
3676 ----------------------------------
3677 FUNCTION populate_new_record (
3678 p_pdfv_rec IN pdfv_rec_type,
3679 x_pdfv_rec OUT NOCOPY pdfv_rec_type
3680 ) RETURN VARCHAR2 IS
3681 l_pdfv_rec pdfv_rec_type;
3682 l_row_notfound BOOLEAN := TRUE;
3683 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3684 BEGIN
3685 x_pdfv_rec := p_pdfv_rec;
3686 -- Get current database values
3687 l_pdfv_rec := get_rec(p_pdfv_rec, l_row_notfound);
3688 IF (l_row_notfound) THEN
3689 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3690 END IF;
3691 IF (x_pdfv_rec.id = OKC_API.G_MISS_NUM)
3692 THEN
3693 x_pdfv_rec.id := l_pdfv_rec.id;
3694 END IF;
3695 IF (x_pdfv_rec.object_version_number = OKC_API.G_MISS_NUM)
3696 THEN
3697 x_pdfv_rec.object_version_number := l_pdfv_rec.object_version_number;
3698 END IF;
3699 IF (x_pdfv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3700 THEN
3701 x_pdfv_rec.sfwt_flag := l_pdfv_rec.sfwt_flag;
3702 END IF;
3703 IF (x_pdfv_rec.description = OKC_API.G_MISS_CHAR)
3704 THEN
3705 x_pdfv_rec.description := l_pdfv_rec.description;
3706 END IF;
3707 IF (x_pdfv_rec.short_description = OKC_API.G_MISS_CHAR)
3708 THEN
3709 x_pdfv_rec.short_description := l_pdfv_rec.short_description;
3710 END IF;
3711 IF (x_pdfv_rec.comments = OKC_API.G_MISS_CHAR)
3712 THEN
3713 x_pdfv_rec.comments := l_pdfv_rec.comments;
3714 END IF;
3715 IF (x_pdfv_rec.usage = OKC_API.G_MISS_CHAR)
3716 THEN
3717 x_pdfv_rec.usage := l_pdfv_rec.usage;
3718 END IF;
3719 IF (x_pdfv_rec.name = OKC_API.G_MISS_CHAR)
3720 THEN
3721 x_pdfv_rec.name := l_pdfv_rec.name;
3722 END IF;
3723 IF (x_pdfv_rec.wf_name = OKC_API.G_MISS_CHAR)
3724 THEN
3725 x_pdfv_rec.wf_name := l_pdfv_rec.wf_name;
3726 END IF;
3727 IF (x_pdfv_rec.wf_process_name = OKC_API.G_MISS_CHAR)
3728 THEN
3729 x_pdfv_rec.wf_process_name := l_pdfv_rec.wf_process_name;
3730 END IF;
3731 IF (x_pdfv_rec.procedure_name = OKC_API.G_MISS_CHAR)
3732 THEN
3733 x_pdfv_rec.procedure_name := l_pdfv_rec.procedure_name;
3734 END IF;
3735 IF (x_pdfv_rec.package_name = OKC_API.G_MISS_CHAR)
3736 THEN
3737 x_pdfv_rec.package_name := l_pdfv_rec.package_name;
3738 END IF;
3739 IF (x_pdfv_rec.pdf_type = OKC_API.G_MISS_CHAR)
3740 THEN
3741 x_pdfv_rec.pdf_type := l_pdfv_rec.pdf_type;
3742 END IF;
3743 IF (x_pdfv_rec.application_id = OKC_API.G_MISS_NUM)
3744 THEN
3745 x_pdfv_rec.application_id := l_pdfv_rec.application_id;
3746 END IF;
3747 IF (x_pdfv_rec.seeded_flag = OKC_API.G_MISS_CHAR)
3748 THEN
3749 x_pdfv_rec.seeded_flag := l_pdfv_rec.seeded_flag;
3750 END IF;
3751 IF (x_pdfv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3752 THEN
3753 x_pdfv_rec.attribute_category := l_pdfv_rec.attribute_category;
3754 END IF;
3755 IF (x_pdfv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3756 THEN
3757 x_pdfv_rec.attribute1 := l_pdfv_rec.attribute1;
3758 END IF;
3759 IF (x_pdfv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3760 THEN
3761 x_pdfv_rec.attribute2 := l_pdfv_rec.attribute2;
3762 END IF;
3763 IF (x_pdfv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3764 THEN
3765 x_pdfv_rec.attribute3 := l_pdfv_rec.attribute3;
3766 END IF;
3767 IF (x_pdfv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3768 THEN
3769 x_pdfv_rec.attribute4 := l_pdfv_rec.attribute4;
3770 END IF;
3771 IF (x_pdfv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3772 THEN
3773 x_pdfv_rec.attribute5 := l_pdfv_rec.attribute5;
3774 END IF;
3775 IF (x_pdfv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3776 THEN
3777 x_pdfv_rec.attribute6 := l_pdfv_rec.attribute6;
3778 END IF;
3779 IF (x_pdfv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3780 THEN
3781 x_pdfv_rec.attribute7 := l_pdfv_rec.attribute7;
3782 END IF;
3783 IF (x_pdfv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3784 THEN
3785 x_pdfv_rec.attribute8 := l_pdfv_rec.attribute8;
3786 END IF;
3787 IF (x_pdfv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3788 THEN
3789 x_pdfv_rec.attribute9 := l_pdfv_rec.attribute9;
3790 END IF;
3791 IF (x_pdfv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3792 THEN
3793 x_pdfv_rec.attribute10 := l_pdfv_rec.attribute10;
3794 END IF;
3795 IF (x_pdfv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3796 THEN
3797 x_pdfv_rec.attribute11 := l_pdfv_rec.attribute11;
3798 END IF;
3799 IF (x_pdfv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3800 THEN
3801 x_pdfv_rec.attribute12 := l_pdfv_rec.attribute12;
3802 END IF;
3803 IF (x_pdfv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3804 THEN
3805 x_pdfv_rec.attribute13 := l_pdfv_rec.attribute13;
3806 END IF;
3807 IF (x_pdfv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3808 THEN
3809 x_pdfv_rec.attribute14 := l_pdfv_rec.attribute14;
3810 END IF;
3811 IF (x_pdfv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3812 THEN
3813 x_pdfv_rec.attribute15 := l_pdfv_rec.attribute15;
3814 END IF;
3815 IF (x_pdfv_rec.created_by = OKC_API.G_MISS_NUM)
3816 THEN
3817 x_pdfv_rec.created_by := l_pdfv_rec.created_by;
3818 END IF;
3819 IF (x_pdfv_rec.begin_date = OKC_API.G_MISS_DATE)
3820 THEN
3821 x_pdfv_rec.begin_date := l_pdfv_rec.begin_date;
3822 END IF;
3823 IF (x_pdfv_rec.end_date = OKC_API.G_MISS_DATE)
3824 THEN
3825 x_pdfv_rec.end_date := l_pdfv_rec.end_date;
3826 END IF;
3827 IF (x_pdfv_rec.creation_date = OKC_API.G_MISS_DATE)
3828 THEN
3829 x_pdfv_rec.creation_date := l_pdfv_rec.creation_date;
3830 END IF;
3831 IF (x_pdfv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3832 THEN
3833 x_pdfv_rec.last_updated_by := l_pdfv_rec.last_updated_by;
3834 END IF;
3835 IF (x_pdfv_rec.last_update_date = OKC_API.G_MISS_DATE)
3836 THEN
3837 x_pdfv_rec.last_update_date := l_pdfv_rec.last_update_date;
3838 END IF;
3839 IF (x_pdfv_rec.last_update_login = OKC_API.G_MISS_NUM)
3840 THEN
3841 x_pdfv_rec.last_update_login := l_pdfv_rec.last_update_login;
3842 END IF;
3843 IF (x_pdfv_rec.message_name = OKC_API.G_MISS_CHAR)
3844 THEN
3845 x_pdfv_rec.message_name := l_pdfv_rec.message_name;
3846 END IF;
3847 IF (x_pdfv_rec.script_name = OKC_API.G_MISS_CHAR)
3848 THEN
3849 x_pdfv_rec.script_name := l_pdfv_rec.script_name;
3850 END IF;
3851 RETURN(l_return_status);
3852 END populate_new_record;
3853 -------------------------------------------
3854 -- Set_Attributes for:OKC_PROCESS_DEFS_V --
3855 -------------------------------------------
3856 FUNCTION Set_Attributes (
3857 p_pdfv_rec IN pdfv_rec_type,
3858 x_pdfv_rec OUT NOCOPY pdfv_rec_type
3859 ) RETURN VARCHAR2 IS
3860 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3861 BEGIN
3862 x_pdfv_rec := p_pdfv_rec;
3863 x_pdfv_rec.OBJECT_VERSION_NUMBER := NVL(x_pdfv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3864 RETURN(l_return_status);
3865 END Set_Attributes;
3866 BEGIN
3867 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3868 G_PKG_NAME,
3869 p_init_msg_list,
3870 l_api_version,
3871 p_api_version,
3872 '_PVT',
3873 x_return_status);
3874 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3875 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3877 RAISE OKC_API.G_EXCEPTION_ERROR;
3878 END IF;
3879 --
3880 -- Seed data should not be updated unless it is updated by datamerge
3881 --
3882 IF l_pdfv_rec.last_updated_by <> 1 THEN
3883 IF l_pdfv_rec.seeded_flag = 'Y' THEN
3884
3885 IF x_pdfv_rec.end_date = l_pdfv_rec.end_date THEN
3886
3887 OKC_API.set_message(p_app_name => G_APP_NAME,
3888 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
3889 RAISE OKC_API.G_EXCEPTION_ERROR;
3890 END IF;
3891 END IF;
3892 END IF;
3893 --- Setting item attributes
3894 l_return_status := Set_Attributes(
3895 p_pdfv_rec, -- IN
3896 l_pdfv_rec); -- OUT
3897 --- If any errors happen abort API
3898 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3899 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3900 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3901 RAISE OKC_API.G_EXCEPTION_ERROR;
3902 END IF;
3903 l_return_status := populate_new_record(l_pdfv_rec, l_def_pdfv_rec);
3904 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3905 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3906 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3907 RAISE OKC_API.G_EXCEPTION_ERROR;
3908 END IF;
3909 l_def_pdfv_rec := fill_who_columns(l_def_pdfv_rec);
3910 --- Validate all non-missing attributes (Item Level Validation)
3911 l_return_status := Validate_Attributes(l_def_pdfv_rec);
3912 --- If any errors happen abort API
3913 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3914 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3915 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3916 RAISE OKC_API.G_EXCEPTION_ERROR;
3917 END IF;
3918 l_return_status := Validate_Record(l_def_pdfv_rec);
3919 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3920 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3921 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3922 RAISE OKC_API.G_EXCEPTION_ERROR;
3923 END IF;
3924
3925 --------------------------------------
3926 -- Move VIEW record to "Child" records
3927 --------------------------------------
3928 migrate(l_def_pdfv_rec, l_pdf_rec);
3929 migrate(l_def_pdfv_rec, l_okc_process_defs_tl_rec);
3930 --------------------------------------------
3931 -- Call the UPDATE_ROW for each child record
3932 --------------------------------------------
3933 update_row(
3934 p_init_msg_list,
3935 x_return_status,
3936 x_msg_count,
3937 x_msg_data,
3938 l_pdf_rec,
3939 lx_pdf_rec
3940 );
3941 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3942 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3943 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3944 RAISE OKC_API.G_EXCEPTION_ERROR;
3945 END IF;
3946 migrate(lx_pdf_rec, l_def_pdfv_rec);
3947 update_row(
3948 p_init_msg_list,
3949 x_return_status,
3950 x_msg_count,
3951 x_msg_data,
3952 l_okc_process_defs_tl_rec,
3953 lx_okc_process_defs_tl_rec
3954 );
3955 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3956 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3957 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3958 RAISE OKC_API.G_EXCEPTION_ERROR;
3959 END IF;
3960 migrate(lx_okc_process_defs_tl_rec, l_def_pdfv_rec);
3961 x_pdfv_rec := l_def_pdfv_rec;
3962 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3963 EXCEPTION
3964 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3965 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3966 (
3967 l_api_name,
3968 G_PKG_NAME,
3969 'OKC_API.G_RET_STS_ERROR',
3970 x_msg_count,
3971 x_msg_data,
3972 '_PVT'
3973 );
3974 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3975 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3976 (
3977 l_api_name,
3978 G_PKG_NAME,
3979 'OKC_API.G_RET_STS_UNEXP_ERROR',
3980 x_msg_count,
3981 x_msg_data,
3982 '_PVT'
3983 );
3984 WHEN OTHERS THEN
3985 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3986 (
3987 l_api_name,
3988 G_PKG_NAME,
3989 'OTHERS',
3990 x_msg_count,
3991 x_msg_data,
3992 '_PVT'
3993 );
3994 END update_row;
3995 ----------------------------------------
3996 -- PL/SQL TBL update_row for:PDFV_TBL --
3997 ----------------------------------------
3998 PROCEDURE update_row(
3999 p_api_version IN NUMBER,
4000 p_init_msg_list IN VARCHAR2,
4001 x_return_status OUT NOCOPY VARCHAR2,
4002 x_msg_count OUT NOCOPY NUMBER,
4003 x_msg_data OUT NOCOPY VARCHAR2,
4004 p_pdfv_tbl IN pdfv_tbl_type,
4005 x_pdfv_tbl OUT NOCOPY pdfv_tbl_type) IS
4006
4007 l_api_version CONSTANT NUMBER := 1;
4008 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4009 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4010 i NUMBER := 0;
4011 BEGIN
4012 OKC_API.init_msg_list(p_init_msg_list);
4013 -- Make sure PL/SQL table has records in it before passing
4014 IF (p_pdfv_tbl.COUNT > 0) THEN
4015 i := p_pdfv_tbl.FIRST;
4016 LOOP
4017 update_row (
4018 p_api_version => p_api_version,
4019 p_init_msg_list => OKC_API.G_FALSE,
4020 x_return_status => x_return_status,
4021 x_msg_count => x_msg_count,
4022 x_msg_data => x_msg_data,
4023 p_pdfv_rec => p_pdfv_tbl(i),
4024 x_pdfv_rec => x_pdfv_tbl(i));
4025 EXIT WHEN (i = p_pdfv_tbl.LAST);
4026 i := p_pdfv_tbl.NEXT(i);
4027 END LOOP;
4028 END IF;
4029 EXCEPTION
4030 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4031 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4032 (
4033 l_api_name,
4034 G_PKG_NAME,
4035 'OKC_API.G_RET_STS_ERROR',
4036 x_msg_count,
4037 x_msg_data,
4038 '_PVT'
4039 );
4040 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4042 (
4043 l_api_name,
4044 G_PKG_NAME,
4045 'OKC_API.G_RET_STS_UNEXP_ERROR',
4046 x_msg_count,
4047 x_msg_data,
4048 '_PVT'
4049 );
4050 WHEN OTHERS THEN
4051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4052 (
4053 l_api_name,
4054 G_PKG_NAME,
4055 'OTHERS',
4056 x_msg_count,
4057 x_msg_data,
4058 '_PVT'
4059 );
4060 END update_row;
4061
4062 ---------------------------------------------------------------------------
4063 -- PROCEDURE delete_row
4064 ---------------------------------------------------------------------------
4065 ----------------------------------------
4066 -- delete_row for:OKC_PROCESS_DEFS_TL --
4067 ----------------------------------------
4068 PROCEDURE delete_row(
4069 p_init_msg_list IN VARCHAR2,
4070 x_return_status OUT NOCOPY VARCHAR2,
4071 x_msg_count OUT NOCOPY NUMBER,
4072 x_msg_data OUT NOCOPY VARCHAR2,
4073 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type) IS
4074
4075 l_api_version CONSTANT NUMBER := 1;
4076 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
4077 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4078 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type:= p_okc_process_defs_tl_rec;
4079 l_row_notfound BOOLEAN := TRUE;
4080 --------------------------------------------
4081 -- Set_Attributes for:OKC_PROCESS_DEFS_TL --
4082 --------------------------------------------
4083 FUNCTION Set_Attributes (
4084 p_okc_process_defs_tl_rec IN okc_process_defs_tl_rec_type,
4085 x_okc_process_defs_tl_rec OUT NOCOPY okc_process_defs_tl_rec_type
4086 ) RETURN VARCHAR2 IS
4087 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4088 BEGIN
4089 x_okc_process_defs_tl_rec := p_okc_process_defs_tl_rec;
4090 x_okc_process_defs_tl_rec.LANGUAGE := l_lang;
4091 RETURN(l_return_status);
4092 END Set_Attributes;
4093 BEGIN
4094 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4095 p_init_msg_list,
4096 '_PVT',
4097 x_return_status);
4098 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4099 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4100 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4101 RAISE OKC_API.G_EXCEPTION_ERROR;
4102 END IF;
4103 --- Setting item attributes
4104 l_return_status := Set_Attributes(
4105 p_okc_process_defs_tl_rec, -- IN
4106 l_okc_process_defs_tl_rec); -- OUT
4107 --- If any errors happen abort API
4108 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4109 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4110 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4111 RAISE OKC_API.G_EXCEPTION_ERROR;
4112 END IF;
4113 DELETE FROM OKC_PROCESS_DEFS_TL
4114 WHERE ID = l_okc_process_defs_tl_rec.id;
4115
4116 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4117 EXCEPTION
4118 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4119 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4120 (
4121 l_api_name,
4122 G_PKG_NAME,
4123 'OKC_API.G_RET_STS_ERROR',
4124 x_msg_count,
4125 x_msg_data,
4126 '_PVT'
4127 );
4128 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4129 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4130 (
4131 l_api_name,
4132 G_PKG_NAME,
4133 'OKC_API.G_RET_STS_UNEXP_ERROR',
4134 x_msg_count,
4135 x_msg_data,
4136 '_PVT'
4137 );
4138 WHEN OTHERS THEN
4139 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4140 (
4141 l_api_name,
4142 G_PKG_NAME,
4143 'OTHERS',
4144 x_msg_count,
4145 x_msg_data,
4146 '_PVT'
4147 );
4148 END delete_row;
4149 ---------------------------------------
4150 -- delete_row for:OKC_PROCESS_DEFS_B --
4151 ---------------------------------------
4152 PROCEDURE delete_row(
4153 p_init_msg_list IN VARCHAR2,
4154 x_return_status OUT NOCOPY VARCHAR2,
4155 x_msg_count OUT NOCOPY NUMBER,
4156 x_msg_data OUT NOCOPY VARCHAR2,
4157 p_pdf_rec IN pdf_rec_type) IS
4158
4159 l_api_version CONSTANT NUMBER := 1;
4160 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4161 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4162 l_pdf_rec pdf_rec_type:= p_pdf_rec;
4163 l_row_notfound BOOLEAN := TRUE;
4164 BEGIN
4165 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4166 p_init_msg_list,
4167 '_PVT',
4168 x_return_status);
4169 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4170 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4172 RAISE OKC_API.G_EXCEPTION_ERROR;
4173 END IF;
4174 DELETE FROM OKC_PROCESS_DEFS_B
4175 WHERE ID = l_pdf_rec.id;
4176
4177 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4178 EXCEPTION
4179 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4180 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4181 (
4182 l_api_name,
4183 G_PKG_NAME,
4184 'OKC_API.G_RET_STS_ERROR',
4185 x_msg_count,
4186 x_msg_data,
4187 '_PVT'
4188 );
4189 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4190 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4191 (
4192 l_api_name,
4193 G_PKG_NAME,
4194 'OKC_API.G_RET_STS_UNEXP_ERROR',
4195 x_msg_count,
4196 x_msg_data,
4197 '_PVT'
4198 );
4199 WHEN OTHERS THEN
4200 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4201 (
4202 l_api_name,
4203 G_PKG_NAME,
4204 'OTHERS',
4205 x_msg_count,
4206 x_msg_data,
4207 '_PVT'
4208 );
4209 END delete_row;
4210 ---------------------------------------
4211 -- delete_row for:OKC_PROCESS_DEFS_V --
4212 ---------------------------------------
4213 PROCEDURE delete_row(
4214 p_api_version IN NUMBER,
4215 p_init_msg_list IN VARCHAR2,
4216 x_return_status OUT NOCOPY VARCHAR2,
4217 x_msg_count OUT NOCOPY NUMBER,
4218 x_msg_data OUT NOCOPY VARCHAR2,
4219 p_pdfv_rec IN pdfv_rec_type) IS
4220
4221 l_api_version CONSTANT NUMBER := 1;
4222 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
4223 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4224 l_pdfv_rec pdfv_rec_type := p_pdfv_rec;
4225 l_pdf_rec pdf_rec_type;
4226 l_okc_process_defs_tl_rec okc_process_defs_tl_rec_type;
4227 BEGIN
4228 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4229 G_PKG_NAME,
4230 p_init_msg_list,
4231 l_api_version,
4232 p_api_version,
4233 '_PVT',
4234 x_return_status);
4235 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4236 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4237 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4238 RAISE OKC_API.G_EXCEPTION_ERROR;
4239 END IF;
4240 --
4241 -- Seed data should not be deleted unless it is deleted by datamerge
4242 --
4243 IF l_pdfv_rec.last_updated_by <> 1 THEN
4244 IF l_pdfv_rec.seeded_flag = 'Y' THEN
4245 OKC_API.set_message(p_app_name => G_APP_NAME,
4246 p_msg_name => 'OKC_NOT_DELETE_SEEDED');
4247 RAISE OKC_API.G_EXCEPTION_ERROR;
4248 END IF;
4249 END IF;
4250 --------------------------------------
4251 -- Move VIEW record to "Child" records
4252 --------------------------------------
4253 migrate(l_pdfv_rec, l_pdf_rec);
4254 migrate(l_pdfv_rec, l_okc_process_defs_tl_rec);
4255 --------------------------------------------
4256 -- Call the DELETE_ROW for each child record
4257 --------------------------------------------
4258 delete_row(
4259 p_init_msg_list,
4260 x_return_status,
4261 x_msg_count,
4262 x_msg_data,
4263 l_pdf_rec
4264 );
4265 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4266 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4267 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4268 RAISE OKC_API.G_EXCEPTION_ERROR;
4269 END IF;
4270 delete_row(
4271 p_init_msg_list,
4272 x_return_status,
4273 x_msg_count,
4274 x_msg_data,
4275 l_okc_process_defs_tl_rec
4276 );
4277 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4278 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4279 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4280 RAISE OKC_API.G_EXCEPTION_ERROR;
4281 END IF;
4282 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4283 EXCEPTION
4284 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4285 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4286 (
4287 l_api_name,
4288 G_PKG_NAME,
4289 'OKC_API.G_RET_STS_ERROR',
4290 x_msg_count,
4291 x_msg_data,
4292 '_PVT'
4293 );
4294 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4295 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4296 (
4297 l_api_name,
4298 G_PKG_NAME,
4299 'OKC_API.G_RET_STS_UNEXP_ERROR',
4300 x_msg_count,
4301 x_msg_data,
4302 '_PVT'
4303 );
4304 WHEN OTHERS THEN
4305 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4306 (
4307 l_api_name,
4308 G_PKG_NAME,
4309 'OTHERS',
4310 x_msg_count,
4311 x_msg_data,
4312 '_PVT'
4313 );
4314 END delete_row;
4315 ----------------------------------------
4316 -- PL/SQL TBL delete_row for:PDFV_TBL --
4317 ----------------------------------------
4318 PROCEDURE delete_row(
4319 p_api_version IN NUMBER,
4320 p_init_msg_list IN VARCHAR2,
4321 x_return_status OUT NOCOPY VARCHAR2,
4322 x_msg_count OUT NOCOPY NUMBER,
4323 x_msg_data OUT NOCOPY VARCHAR2,
4324 p_pdfv_tbl IN pdfv_tbl_type) IS
4325
4326 l_api_version CONSTANT NUMBER := 1;
4327 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4328 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4329 i NUMBER := 0;
4330 BEGIN
4331 OKC_API.init_msg_list(p_init_msg_list);
4332 -- Make sure PL/SQL table has records in it before passing
4333 IF (p_pdfv_tbl.COUNT > 0) THEN
4334 i := p_pdfv_tbl.FIRST;
4335 LOOP
4336 delete_row (
4337 p_api_version => p_api_version,
4338 p_init_msg_list => OKC_API.G_FALSE,
4339 x_return_status => x_return_status,
4340 x_msg_count => x_msg_count,
4341 x_msg_data => x_msg_data,
4342 p_pdfv_rec => p_pdfv_tbl(i));
4343 EXIT WHEN (i = p_pdfv_tbl.LAST);
4344 i := p_pdfv_tbl.NEXT(i);
4345 END LOOP;
4346 END IF;
4347 EXCEPTION
4348 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4349 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4350 (
4351 l_api_name,
4352 G_PKG_NAME,
4353 'OKC_API.G_RET_STS_ERROR',
4354 x_msg_count,
4355 x_msg_data,
4356 '_PVT'
4357 );
4358 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4359 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4360 (
4361 l_api_name,
4362 G_PKG_NAME,
4363 'OKC_API.G_RET_STS_UNEXP_ERROR',
4364 x_msg_count,
4365 x_msg_data,
4366 '_PVT'
4367 );
4368 WHEN OTHERS THEN
4369 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4370 (
4371 l_api_name,
4372 G_PKG_NAME,
4373 'OTHERS',
4374 x_msg_count,
4375 x_msg_data,
4376 '_PVT'
4377 );
4378 END delete_row;
4379 END OKC_PDF_PVT;