[Home] [Help]
PACKAGE BODY: APPS.OKC_PROCESS_PVT
Source
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1 package body okc_process_pvt as
2 /* $Header: OKCCPDFB.pls 120.0 2005/05/26 09:55:34 appldev noship $ */
3
5
6 PROCEDURE add_language IS
7 Begin
8 okc_pdf_pvt.add_language;
9 okc_pdp_pvt.add_language;
10 End;
11
12 --Object type procedure for insert
13 PROCEDURE create_process_def(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 ,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_pdfv_rec IN pdfv_rec_type,
20 p_pdpv_tbl IN pdpv_tbl_type,
21 x_pdfv_rec OUT NOCOPY pdfv_rec_type,
22 x_pdpv_tbl OUT NOCOPY pdpv_tbl_type) IS
23
24 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
25 l_pdfv_rec pdfv_rec_type;
26 l_pdpv_tbl pdpv_tbl_type := p_pdpv_tbl;
27 i NUMBER;
28 begin
29 --Populate the Master
30 create_proc_def(
31 p_api_version,
32 p_init_msg_list,
33 x_return_status,
34 x_msg_count,
35 x_msg_data,
36 p_pdfv_rec,
37 x_pdfv_rec);
38 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
39 raise G_EXCEPTION_HALT_VALIDATION;
40 ELSE
41 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
42 l_return_status := x_return_status;
43 END IF;
44 END IF;
45
46 -- Populate the foreign key for the detail
47 IF (l_pdpv_tbl.COUNT > 0) THEN
48 i := l_pdpv_tbl.FIRST;
49 LOOP
50 l_pdpv_tbl(i).pdf_id := x_pdfv_rec.id;
51 EXIT WHEN (i = l_pdpv_tbl.LAST);
52 i := l_pdpv_tbl.NEXT(i);
53 END LOOP;
54 END IF;
55
56 --Populate the detail
57 create_proc_def_parms(
58 p_api_version,
59 p_init_msg_list,
60 x_return_status,
61 x_msg_count,
62 x_msg_data,
63 l_pdpv_tbl,
64 x_pdpv_tbl);
65 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
66 raise G_EXCEPTION_HALT_VALIDATION;
67 ELSE
68 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
69 l_return_status := x_return_status;
70 END IF;
71 END IF;
72
73 EXCEPTION
74 WHEN G_EXCEPTION_HALT_VALIDATION THEN
75 Null;
76
77 WHEN OTHERS THEN
78 OKC_API.set_message(p_app_name => g_app_name,
79 p_msg_name => g_unexpected_error,
80 p_token1 => g_sqlcode_token,
81 p_token1_value => sqlcode,
82 p_token2 => g_sqlerrm_token,
83 p_token2_value => sqlerrm);
84 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
85 End create_process_def;
86
87 --Object type procedure for update
88 PROCEDURE update_process_def(
89 p_api_version IN NUMBER,
90 p_init_msg_list IN VARCHAR2 ,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_pdfv_rec IN pdfv_rec_type,
95 p_pdpv_tbl IN pdpv_tbl_type,
96 x_pdfv_rec OUT NOCOPY pdfv_rec_type,
97 x_pdpv_tbl OUT NOCOPY pdpv_tbl_type) IS
98 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
99 begin
100 --Update the Master
101 update_proc_def(
102 p_api_version,
103 p_init_msg_list,
104 x_return_status,
105 x_msg_count,
106 x_msg_data,
107 p_pdfv_rec,
108 x_pdfv_rec);
109 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
110 raise G_EXCEPTION_HALT_VALIDATION;
111 ELSE
112 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
113 l_return_status := x_return_status;
114 END IF;
115 END IF;
116
117 --Update the detail
118 update_proc_def_parms(
119 p_api_version,
120 p_init_msg_list,
121 x_return_status,
122 x_msg_count,
123 x_msg_data,
124 p_pdpv_tbl,
125 x_pdpv_tbl);
126 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
127 raise G_EXCEPTION_HALT_VALIDATION;
128 ELSE
129 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
130 l_return_status := x_return_status;
131 END IF;
132 END IF;
133
134 EXCEPTION
135 WHEN G_EXCEPTION_HALT_VALIDATION THEN
136 Null;
137
138 WHEN OTHERS THEN
139 OKC_API.set_message(p_app_name => g_app_name,
140 p_msg_name => g_unexpected_error,
141 p_token1 => g_sqlcode_token,
142 p_token1_value => sqlcode,
143 p_token2 => g_sqlerrm_token,
144 p_token2_value => sqlerrm);
145 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
146 end update_process_def;
147
148 --Object type procedure for validate
149 PROCEDURE validate_process_def(
150 p_api_version IN NUMBER,
151 p_init_msg_list IN VARCHAR2 ,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 p_pdfv_rec IN pdfv_rec_type,
156 p_pdpv_tbl IN pdpv_tbl_type) IS
157 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
158 begin
159 --Validate the Master
160 validate_proc_def(
161 p_api_version,
162 p_init_msg_list,
163 x_return_status,
164 x_msg_count,
165 x_msg_data,
166 p_pdfv_rec);
167 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
168 raise G_EXCEPTION_HALT_VALIDATION;
169 ELSE
170 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
171 l_return_status := x_return_status;
172 END IF;
173 END IF;
174
175 --Validate the Detail
176 validate_proc_def_parms(
177 p_api_version,
178 p_init_msg_list,
179 x_return_status,
180 x_msg_count,
181 x_msg_data,
182 p_pdpv_tbl);
183 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
184 raise G_EXCEPTION_HALT_VALIDATION;
185 ELSE
186 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
187 l_return_status := x_return_status;
188 END IF;
189 END IF;
190
191 EXCEPTION
192 WHEN G_EXCEPTION_HALT_VALIDATION THEN
193 Null;
194
195 WHEN OTHERS THEN
196 OKC_API.set_message(p_app_name => g_app_name,
197 p_msg_name => g_unexpected_error,
198 p_token1 => g_sqlcode_token,
199 p_token1_value => sqlcode,
200 p_token2 => g_sqlerrm_token,
201 p_token2_value => sqlerrm);
202 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
203 End validate_process_def;
204
205 --Procedures for Process Definitions
206
207 PROCEDURE create_proc_def(
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 ,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_pdfv_rec IN pdfv_rec_type,
214 x_pdfv_rec OUT NOCOPY pdfv_rec_type) IS
215 begin
216 okc_pdf_pvt.insert_row(
217 p_api_version,
218 p_init_msg_list,
219 x_return_status,
220 x_msg_count,
221 x_msg_data,
222 p_pdfv_rec,
223 x_pdfv_rec);
224 End create_proc_def;
225
226 PROCEDURE create_proc_def(
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 ,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2,
232 p_pdfv_tbl IN pdfv_tbl_type,
233 x_pdfv_tbl OUT NOCOPY pdfv_tbl_type) IS
234 begin
235 okc_pdf_pvt.insert_row(
236 p_api_version,
237 p_init_msg_list,
238 x_return_status,
239 x_msg_count,
240 x_msg_data,
241 p_pdfv_tbl,
242 x_pdfv_tbl);
243 End create_proc_def;
244
245 PROCEDURE lock_proc_def(
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 ,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 p_pdfv_rec IN pdfv_rec_type) IS
252 begin
253 okc_pdf_pvt.lock_row(
254 p_api_version,
255 p_init_msg_list,
256 x_return_status,
257 x_msg_count,
258 x_msg_data,
259 p_pdfv_rec);
260 End lock_proc_def;
261
262 PROCEDURE lock_proc_def(
263 p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2 ,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_pdfv_tbl IN pdfv_tbl_type) IS
269 begin
270 okc_pdf_pvt.lock_row(
271 p_api_version,
272 p_init_msg_list,
273 x_return_status,
274 x_msg_count,
275 x_msg_data,
276 p_pdfv_tbl);
277 End lock_proc_def;
278
279 PROCEDURE update_proc_def(
280 p_api_version IN NUMBER,
281 p_init_msg_list IN VARCHAR2 ,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 p_pdfv_rec IN pdfv_rec_type,
286 x_pdfv_rec OUT NOCOPY pdfv_rec_type) IS
287 l_pdpv_tbl OKC_PROCESS_PUB.pdpv_tbl_type;
288 v_pdpv_tbl OKC_PROCESS_PUB.pdpv_tbl_type;
289 l_api_version NUMBER := 1;
290 l_init_msg_list VARCHAR2(1) := 'T';
291 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
292 l_msg_count NUMBER;
293 l_msg_data VARCHAR2(200);
294 l_app_id1 NUMBER;
295 l_cnt NUMBER := 0;
296 CURSOR l_id_cur is
297 SELECT *
298 FROM okc_process_def_parameters_v
299 WHERE pdf_id = p_pdfv_rec.id;
300 begin
301 SELECT application_id INTO l_app_id1
302 FROM OKC_PROCESS_DEFS_B
303 WHERE id = p_pdfv_rec.id;
304
305 okc_pdf_pvt.update_row(
306 p_api_version,
307 p_init_msg_list,
308 x_return_status,
309 x_msg_count,
310 x_msg_data,
311 p_pdfv_rec,
312 x_pdfv_rec);
313 IF x_return_status = 'S' THEN
314 If nvl(p_pdfv_rec.application_id,-99) <> nvl(l_app_id1,-99) THEN
315 l_pdpv_tbl.delete;
316 v_pdpv_tbl.delete;
317 FOR l_pdpv_rec in l_id_cur
318 LOOP
319 l_cnt := l_cnt + 1;
320 l_pdpv_tbl(l_cnt).id := l_pdpv_rec.id;
321 l_pdpv_tbl(l_cnt).object_version_number := l_pdpv_rec.object_version_number;
322 l_pdpv_tbl(l_cnt).sfwt_flag := l_pdpv_rec.sfwt_flag;
323 l_pdpv_tbl(l_cnt).pdf_id := l_pdpv_rec.pdf_id;
324 l_pdpv_tbl(l_cnt).name := l_pdpv_rec.name;
325 l_pdpv_tbl(l_cnt).data_type := l_pdpv_rec.data_type;
326 l_pdpv_tbl(l_cnt).default_value := l_pdpv_rec.default_value;
327 l_pdpv_tbl(l_cnt).required_yn := l_pdpv_rec.required_yn;
328 l_pdpv_tbl(l_cnt).description := l_pdpv_rec.description;
329 l_pdpv_tbl(l_cnt).application_id := p_pdfv_rec.application_id;
330 l_pdpv_tbl(l_cnt).seeded_flag := l_pdpv_rec.seeded_flag;
331 l_pdpv_tbl(l_cnt).created_by := l_pdpv_rec.created_by;
332 l_pdpv_tbl(l_cnt).creation_date := l_pdpv_rec.creation_date;
333 l_pdpv_tbl(l_cnt).last_updated_by := l_pdpv_rec.last_updated_by;
334 l_pdpv_tbl(l_cnt).last_update_date := l_pdpv_rec.last_update_date;
335 l_pdpv_tbl(l_cnt).last_update_login := l_pdpv_rec.last_update_login;
336 l_pdpv_tbl(l_cnt).jtot_object_code := l_pdpv_rec.jtot_object_code;
337 l_pdpv_tbl(l_cnt).name_column := l_pdpv_rec.name_column;
338 l_pdpv_tbl(l_cnt).description_column := l_pdpv_rec.description_column;
339 END LOOP;
340 okc_process_pub.update_proc_def_parms(
341 p_api_version => l_api_version,
342 p_init_msg_list => l_init_msg_list,
343 x_return_status => l_return_status,
344 x_msg_count => l_msg_count,
345 x_msg_data => l_msg_data,
346 p_pdpv_tbl => l_pdpv_tbl,
347 x_pdpv_tbl => v_pdpv_tbl);
348 END IF;
349 END IF;
350 End update_proc_def;
351
352 PROCEDURE update_proc_def(
353 p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 ,
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358 p_pdfv_tbl IN pdfv_tbl_type,
359 x_pdfv_tbl OUT NOCOPY pdfv_tbl_type) IS
360 begin
361 okc_pdf_pvt.update_row(
362 p_api_version,
363 p_init_msg_list,
364 x_return_status,
365 x_msg_count,
366 x_msg_data,
367 p_pdfv_tbl,
368 x_pdfv_tbl);
369 End update_proc_def;
370
371 --Procedure for Cascade Delete
372 PROCEDURE delete_proc_def(
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 ,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2,
378 p_pdfv_rec IN pdfv_rec_type) IS
379
380 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
381 i NUMBER := 0;
382 c NUMBER := 0;
383 l_pdpv_tbl pdpv_tbl_type;
384 l_condition VARCHAR2(1) := '?';
385 l_qa VARCHAR2(1) := '?';
386 l_process VARCHAR2(1) := '?';
387
391 from okc_process_def_parameters_v pdp
388 --Fetch all the process definition parameter id's for a specific process definition
389 Cursor p_cur is
390 select pdp.id
392 where pdp.pdf_id = p_pdfv_rec.id;
393
394 --Check if the process definition is used by Conditions
395 Cursor check_condition_csr(p_pdf_id IN NUMBER) is
396 select '1'
397 from okc_process_defs_v pdf,
398 okc_outcomes_v out,
399 okc_condition_headers_v cnh
400 where pdf.id = out.pdf_id
401 and pdf.id = p_pdf_id
402 and out.cnh_id = cnh.id;
403
404 Cursor check_qa_csr(p_pdf_id IN NUMBER) is
405 select '1'
406 from okc_process_defs_v pdf,
407 okc_qa_list_processes qa
408 where pdf.id = qa.pdf_id
409 and pdf.id = p_pdf_id;
410
411 Cursor check_k_process_csr(p_pdf_id IN NUMBER) is
412 select '1'
413 from okc_process_defs_v pdf,
414 okc_k_processes k
415 where pdf.id = k.pdf_id
416 and pdf.id = p_pdf_id;
417
418 begin
419 OPEN check_condition_csr(p_pdfv_rec.id);
420 FETCH check_condition_csr INTO l_condition;
421 CLOSE check_condition_csr;
422
423 OPEN check_qa_csr(p_pdfv_rec.id);
424 FETCH check_qa_csr INTO l_qa;
425 CLOSE check_qa_csr;
426
427 OPEN check_k_process_csr(p_pdfv_rec.id);
428 FETCH check_k_process_csr INTO l_process;
429 CLOSE check_k_process_csr;
430
431 --Process definition cannot be deleted while being used in conditions
432 IF l_condition = '1' OR l_qa = '1' OR l_process = '1' THEN
433 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
434 p_msg_name => g_delete_proc_def);
435 x_return_status := OKC_API.G_RET_STS_ERROR;
436 raise G_EXCEPTION_HALT_VALIDATION;
437 ELSE
438 --populate the Foreign key of the detail
439 For p_rec in p_cur loop
440 i := i + 1;
441 l_pdpv_tbl(i).id := p_rec.id;
442 End loop;
443
444 --Delete the details
445 -- call Public delete procedure
446 okc_process_pub.delete_proc_def_parms(
447 p_api_version,
448 p_init_msg_list,
449 x_return_status,
450 x_msg_count,
451 x_msg_data,
452 l_pdpv_tbl);
453 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
454 raise G_EXCEPTION_HALT_VALIDATION;
455 ELSE
456 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
457 l_return_status := x_return_status;
458 END IF;
459 END IF;
460
461 --Delete the Master
462 okc_pdf_pvt.delete_row(
463 p_api_version,
464 p_init_msg_list,
465 x_return_status,
466 x_msg_count,
467 x_msg_data,
468 p_pdfv_rec);
469 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
470 raise G_EXCEPTION_HALT_VALIDATION;
471 ELSE
472 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
473 l_return_status := x_return_status;
474 END IF;
475 END IF;
476 END IF;
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_VALIDATION THEN
479 Null;
480
481 WHEN OTHERS THEN
482 OKC_API.set_message(p_app_name => g_app_name,
483 p_msg_name => g_unexpected_error,
484 p_token1 => g_sqlcode_token,
485 p_token1_value => sqlcode,
486 p_token2 => g_sqlerrm_token,
487 p_token2_value => sqlerrm);
488 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
489 End delete_proc_def;
490
491 PROCEDURE delete_proc_def(
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 ,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_pdfv_tbl IN pdfv_tbl_type) IS
498 i NUMBER :=0;
499 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
500 begin
501 --Initialize the return status
502 x_return_status := OKC_API.G_RET_STS_SUCCESS;
503
504 IF (p_pdfv_tbl.COUNT > 0) THEN
505 i := p_pdfv_tbl.FIRST;
506 LOOP
507 delete_proc_def(
508 p_api_version,
509 p_init_msg_list,
510 x_return_status,
511 x_msg_count,
512 x_msg_data,
513 p_pdfv_tbl(i));
514 EXIT WHEN (i = p_pdfv_tbl.LAST);
515 i := p_pdfv_tbl.NEXT(i);
516 END LOOP;
517 END IF;
518 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
519 raise G_EXCEPTION_HALT_VALIDATION;
520 ELSE
521 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
522 l_return_status := x_return_status;
523 END IF;
524 END IF;
525 EXCEPTION
526 WHEN G_EXCEPTION_HALT_VALIDATION THEN
527 Null;
528
529 WHEN OTHERS THEN
530 OKC_API.set_message(p_app_name => g_app_name,
531 p_msg_name => g_unexpected_error,
532 p_token1 => g_sqlcode_token,
533 p_token1_value => sqlcode,
534 p_token2 => g_sqlerrm_token,
535 p_token2_value => sqlerrm);
536 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
537 End delete_proc_def;
538
539 PROCEDURE validate_proc_def(
540 p_api_version IN NUMBER,
541 p_init_msg_list IN VARCHAR2 ,
542 x_return_status OUT NOCOPY VARCHAR2,
543 x_msg_count OUT NOCOPY NUMBER,
544 x_msg_data OUT NOCOPY VARCHAR2,
545 p_pdfv_rec IN pdfv_rec_type) IS
546 begin
547 okc_pdf_pvt.validate_row(
548 p_api_version,
549 p_init_msg_list,
550 x_return_status,
551 x_msg_count,
552 x_msg_data,
553 p_pdfv_rec);
554 End validate_proc_def;
555
556 PROCEDURE validate_proc_def(
557 p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2 ,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2,
562 p_pdfv_tbl IN pdfv_tbl_type) IS
563 begin
564 okc_pdf_pvt.validate_row(
565 p_api_version,
566 p_init_msg_list,
567 x_return_status,
568 x_msg_count,
569 x_msg_data,
570 p_pdfv_tbl);
571 End validate_proc_def;
572
573 --Procedures for Process Definition Parameters
574
575 PROCEDURE create_proc_def_parms(
576 p_api_version IN NUMBER,
577 p_init_msg_list IN VARCHAR2 ,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2,
581 p_pdpv_rec IN pdpv_rec_type,
582 x_pdpv_rec OUT NOCOPY pdpv_rec_type) IS
583 l_app_id NUMBER;
584 l_pdpv_rec pdpv_rec_type := p_pdpv_rec;
585 begin
586 SELECT application_id into l_app_id
587 FROM OKC_PROCESS_DEFS_B
588 WHERE ID = l_pdpv_rec.pdf_id;
589 l_pdpv_rec.application_id := l_app_id;
590 okc_pdp_pvt.insert_row(
591 p_api_version,
592 p_init_msg_list,
593 x_return_status,
594 x_msg_count,
595 x_msg_data,
596 l_pdpv_rec,
597 x_pdpv_rec);
598 End create_proc_def_parms;
599
600 PROCEDURE create_proc_def_parms(
601 p_api_version IN NUMBER,
602 p_init_msg_list IN VARCHAR2 ,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 p_pdpv_tbl IN pdpv_tbl_type,
607 x_pdpv_tbl OUT NOCOPY pdpv_tbl_type) IS
608 begin
609 okc_pdp_pvt.insert_row(
610 p_api_version,
611 p_init_msg_list,
612 x_return_status,
613 x_msg_count,
614 x_msg_data,
615 p_pdpv_tbl,
616 x_pdpv_tbl);
617 End create_proc_def_parms;
618
619 PROCEDURE lock_proc_def_parms(
620 p_api_version IN NUMBER,
621 p_init_msg_list IN VARCHAR2 ,
622 x_return_status OUT NOCOPY VARCHAR2,
623 x_msg_count OUT NOCOPY NUMBER,
624 x_msg_data OUT NOCOPY VARCHAR2,
625 p_pdpv_rec IN pdpv_rec_type) IS
626 begin
627 okc_pdp_pvt.lock_row(
628 p_api_version,
629 p_init_msg_list,
630 x_return_status,
631 x_msg_count,
632 x_msg_data,
633 p_pdpv_rec);
634 End lock_proc_def_parms;
635
636 PROCEDURE lock_proc_def_parms(
637 p_api_version IN NUMBER,
638 p_init_msg_list IN VARCHAR2 ,
639 x_return_status OUT NOCOPY VARCHAR2,
640 x_msg_count OUT NOCOPY NUMBER,
641 x_msg_data OUT NOCOPY VARCHAR2,
642 p_pdpv_tbl IN pdpv_tbl_type) IS
643 begin
644 okc_pdp_pvt.lock_row(
645 p_api_version,
646 p_init_msg_list,
647 x_return_status,
648 x_msg_count,
649 x_msg_data,
650 p_pdpv_tbl);
651 End lock_proc_def_parms;
652
653 PROCEDURE update_proc_def_parms(
654 p_api_version IN NUMBER,
655 p_init_msg_list IN VARCHAR2 ,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 p_pdpv_rec IN pdpv_rec_type,
660 x_pdpv_rec OUT NOCOPY pdpv_rec_type) IS
661 begin
662 okc_pdp_pvt.update_row(
663 p_api_version,
664 p_init_msg_list,
665 x_return_status,
666 x_msg_count,
667 x_msg_data,
668 p_pdpv_rec,
669 x_pdpv_rec);
670 End update_proc_def_parms;
671
672 PROCEDURE update_proc_def_parms(
673 p_api_version IN NUMBER,
674 p_init_msg_list IN VARCHAR2 ,
675 x_return_status OUT NOCOPY VARCHAR2,
676 x_msg_count OUT NOCOPY NUMBER,
677 x_msg_data OUT NOCOPY VARCHAR2,
678 p_pdpv_tbl IN pdpv_tbl_type,
679 x_pdpv_tbl OUT NOCOPY pdpv_tbl_type) IS
680 begin
681 okc_pdp_pvt.update_row(
682 p_api_version,
683 p_init_msg_list,
684 x_return_status,
685 x_msg_count,
686 x_msg_data,
687 p_pdpv_tbl,
688 x_pdpv_tbl);
689 End update_proc_def_parms;
690
691 PROCEDURE delete_proc_def_parms(
692 p_api_version IN NUMBER,
693 p_init_msg_list IN VARCHAR2 ,
694 x_return_status OUT NOCOPY VARCHAR2,
695 x_msg_count OUT NOCOPY NUMBER,
696 x_msg_data OUT NOCOPY VARCHAR2,
697 p_pdpv_rec IN pdpv_rec_type) IS
698 begin
699 okc_pdp_pvt.delete_row(
700 p_api_version,
701 p_init_msg_list,
702 x_return_status,
703 x_msg_count,
704 x_msg_data,
705 p_pdpv_rec);
706 End delete_proc_def_parms;
707
708 PROCEDURE delete_proc_def_parms(
709 p_api_version IN NUMBER,
710 p_init_msg_list IN VARCHAR2 ,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_count OUT NOCOPY NUMBER,
713 x_msg_data OUT NOCOPY VARCHAR2,
714 p_pdpv_tbl IN pdpv_tbl_type) IS
715 begin
716 okc_pdp_pvt.delete_row(
717 p_api_version,
718 p_init_msg_list,
719 x_return_status,
720 x_msg_count,
721 x_msg_data,
722 p_pdpv_tbl);
723 End delete_proc_def_parms;
724
725 PROCEDURE validate_proc_def_parms(
726 p_api_version IN NUMBER,
727 p_init_msg_list IN VARCHAR2 ,
728 x_return_status OUT NOCOPY VARCHAR2,
729 x_msg_count OUT NOCOPY NUMBER,
730 x_msg_data OUT NOCOPY VARCHAR2,
731 p_pdpv_rec IN pdpv_rec_type) IS
732 begin
733 okc_pdp_pvt.validate_row(
734 p_api_version,
735 p_init_msg_list,
736 x_return_status,
737 x_msg_count,
738 x_msg_data,
739 p_pdpv_rec);
740 End validate_proc_def_parms;
741
742 PROCEDURE validate_proc_def_parms(
743 p_api_version IN NUMBER,
744 p_init_msg_list IN VARCHAR2 ,
745 x_return_status OUT NOCOPY VARCHAR2,
746 x_msg_count OUT NOCOPY NUMBER,
747 x_msg_data OUT NOCOPY VARCHAR2,
748 p_pdpv_tbl IN pdpv_tbl_type) IS
749 begin
750 okc_pdp_pvt.validate_row(
751 p_api_version,
752 p_init_msg_list,
753 x_return_status,
754 x_msg_count,
755 x_msg_data,
756 p_pdpv_tbl);
757 End validate_proc_def_parms;
758
759 /* ===========================================================================
760 | PROCEDURE validate_dbnames |
761 | INPUT: process defintion record pdpv_rec_type |
762 | PROCESS: validates that either the workflow name and process name |
763 | (WF_NAME and WF_PROCESS_NAME) exists in the WF_ACTIVITIES_VL) |
764 | OR package/procedure name exists in ALL_ATTRIBUTES. |
765 | OUTPUT: error message |
766 | OKC_INVALID_WF_NAME
767 | OKC_INVALID_PACK_NAME
768 | ===========================================================================
769 */
770
771 PROCEDURE validate_dbnames(
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 ,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 p_pdfv_rec IN pdfv_rec_type) IS
778
779 CURSOR wf (wfname VARCHAR2, wfproc VARCHAR2) IS
780 SELECT RUNNABLE_FLAG
781 FROM wf_activities_vl w
782 WHERE w.item_type = UPPER(wfname)
783 AND w.name = UPPER(wfproc)
784 AND w.version = (SELECT max(version)
785 FROM wf_activities_vl sq
786 WHERE sq.item_type = UPPER(wfname)
787 AND sq.name = UPPER(wfproc) );
788
789 CURSOR pp (packname VARCHAR2, procname VARCHAR2) IS
790 SELECT 1
791 FROM user_arguments a,
792 user_objects o
793 WHERE o.object_type = 'PACKAGE'
794 and o.object_name = UPPER(packname)
795 and a.object_id = o.object_id
796 AND a.object_name = UPPER(procname);
797
798 l_pack_count NUMBER := 0;
799 l_run_flag VARCHAR2(4);
800
801 BEGIN
802 --
803 -- Setup the successful message
804 --
805 x_return_status := OKC_API.G_RET_STS_SUCCESS;
806 FND_MESSAGE.SET_NAME(application => g_app_name,
807 name => g_validate_dbname_success);
808
809 x_msg_count := 0;
810 x_msg_data := null;
811 l_run_flag := null;
812 l_pack_count := 0;
813
814 IF (p_pdfv_rec.wf_name IS NOT NULL) AND (p_pdfv_rec.wf_process_name IS NOT NULL) THEN
815 OPEN wf (p_pdfv_rec.wf_name, p_pdfv_rec.wf_process_name);
816 FETCH wf INTO l_run_flag;
817 IF wf%NOTFOUND THEN
818 CLOSE wf;
819 FND_MESSAGE.SET_NAME(application => g_app_name,
820 name => g_validate_dbname_notfound);
821 FND_MESSAGE.SET_TOKEN(token => 'DATANAME',
822 value => g_validate_dbname_wf_pair,
823 translate => TRUE);
824 x_return_status := OKC_API.G_RET_STS_ERROR;
825 ELSE
826 CLOSE wf;
827 IF l_run_flag <> 'Y' THEN
828 x_return_status := OKC_API.G_RET_STS_ERROR;
829 FND_MESSAGE.SET_NAME(application => g_app_name,
830 name => g_validate_dbname_notrun);
831 FND_MESSAGE.SET_TOKEN(token => 'DATANAME',
832 value => g_validate_dbname_wf_pair,
833 translate => TRUE);
834 END IF;
835 END IF;
836
837 ELSIF (p_pdfv_rec.procedure_name IS NOT NULL) AND (p_pdfv_rec.package_name IS NOT NULL) THEN
838 OPEN pp(p_pdfv_rec.package_name, p_pdfv_rec.procedure_name);
839 FETCH pp INTO l_pack_count;
840 IF pp%NOTFOUND THEN
841 x_return_status := OKC_API.G_RET_STS_ERROR;
842 FND_MESSAGE.SET_NAME(application => g_app_name,
843 name => g_validate_dbname_notfound);
844 FND_MESSAGE.SET_TOKEN(token => 'DATANAME',
845 value => g_validate_dbname_pp_pair,
846 translate => TRUE);
847 END IF;
848
849 CLOSE pp;
850
851 END IF;
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 IF wf%ISOPEN THEN
856 CLOSE wf;
857 ELSIF pp%ISOPEN THEN
858 CLOSE pp;
859 END IF;
860
861 FND_MESSAGE.SET_NAME(application => g_app_name,
862 name => g_unexpected_error);
863 FND_MESSAGE.SET_TOKEN(token => g_sqlcode_token,
864 value => sqlcode);
865 FND_MESSAGE.SET_TOKEN(token => g_sqlerrm_token,
866 value => sqlerrm);
867 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
868
869 END validate_dbnames;
870
871
872
873
874 END okc_process_pvt;