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