[Home] [Help]
PACKAGE BODY: APPS.OKC_IMP_TERMS_TEMPLATES_PVT
Source
1 PACKAGE BODY okc_imp_terms_templates_pvt
2 /*$Header: OKCVITTB.pls 120.2.12020000.6 2013/02/26 12:26:00 aksgoyal ship $*/
3 AS
4 CURSOR cur_val_lookup (p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2)
5 IS
6 SELECT 'Y'
7 FROM fnd_lookups
8 WHERE lookup_type = p_lookup_type
9 AND lookup_code = p_lookup_code
10 AND enabled_flag = 'Y'
11 AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
12 AND NVL (TRUNC (end_date_active),
13 TRUNC (SYSDATE)
14 );
15
16 CURSOR cur_lookup_meaning (
17 p_lookup_type IN VARCHAR2,
18 p_lookup_code IN VARCHAR2
19 )
20 IS
21 SELECT 'Y', meaning
22 FROM fnd_lookups
23 WHERE lookup_type = p_lookup_type
24 AND lookup_code = p_lookup_code
25 AND enabled_flag = 'Y'
26 AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
27 AND NVL (TRUNC (end_date_active),
28 TRUNC (SYSDATE)
29 );
30
31 CURSOR cur_validate_ou (p_org_id IN NUMBER)
32 IS
33 SELECT 'Y'
34 FROM hr_operating_units ou, hr_organization_information oi
35 WHERE mo_global.check_access (ou.organization_id) = 'Y'
36 AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
37 AND oi.organization_id = ou.organization_id
38 AND NVL (date_to, SYSDATE) >= SYSDATE
39 AND ou.organization_id = p_org_id;
40
41 g_template_id NUMBER;
42 g_template_intent VARCHAR2 (60);
43 g_template_status_code VARCHAR2 (60);
44 g_template_start_date DATE;
45 g_template_end_date DATE;
46 g_template_org_id NUMBER;
47 g_clause_update_allowed VARCHAR2 (1);
48 g_deliverable_update_allowed VARCHAR2 (1);
49 g_headerinfo_update_allowed VARCHAR2 (1);
50 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
51 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
52 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
53
54 PROCEDURE create_section (
55 p_section_rec IN OUT NOCOPY section_rec_type,
56 p_commit IN VARCHAR2 := fnd_api.g_false
57 );
58
59 PROCEDURE create_article (
60 p_article_rec IN OUT NOCOPY k_article_rec_type,
61 p_commit IN VARCHAR2 := fnd_api.g_false
62 );
63
64 PROCEDURE create_deliverable (
65 p_deliverable_rec IN OUT NOCOPY deliverable_rec_type,
66 p_commit IN VARCHAR2 := fnd_api.g_false
67 );
68
69 PROCEDURE create_template (
70 p_template_rec IN OUT NOCOPY terms_template_rec_type,
71 p_commit IN VARCHAR2 := fnd_api.g_false
72 );
73
74 PROCEDURE update_template (
75 p_template_rec IN OUT NOCOPY terms_template_rec_type
76 );
77
78 PROCEDURE create_tmpl_usage (
79 p_template_id IN NUMBER,
80 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
81 );
82
83 PROCEDURE validate_tmpl_usage (
84 p_template_id IN NUMBER,
85 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type,
86 p_mode IN VARCHAR2 := 'CREATE'
87 );
88
89 PROCEDURE update_tmpl_usage (
90 p_template_id IN NUMBER,
91 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
92 );
93
94 PROCEDURE delete_tmpl_usage (
95 p_template_id IN NUMBER,
96 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
97 );
98
99 ---------------------------------------------------------------
100 PROCEDURE set_proc_error_message (p_proc IN VARCHAR2)
101 IS
102 BEGIN
103 okc_api.set_message (p_app_name => g_app_name,
104 p_msg_name => 'OKC_I_ERROR_PROCEDURE',
105 p_token1 => 'PROCEDURE',
106 p_token1_value => p_proc
107 );
108 END set_proc_error_message;
109
110 PROCEDURE set_rec_num_message (p_rec_num IN NUMBER)
111 IS
112 BEGIN
113 okc_api.set_message (p_app_name => g_app_name,
114 p_msg_name => 'OKC_I_RECORD_NUM',
115 p_token1 => 'RECORD_NUM',
116 p_token1_value => p_rec_num
117 );
118 END set_rec_num_message;
119
120 -------------------------------------------------------------
121 PROCEDURE read_message (x_message IN OUT NOCOPY VARCHAR2)
122 IS
123 l_message VARCHAR2 (2000);
124 BEGIN
125 FOR i IN 1 .. fnd_msg_pub.count_msg
126 LOOP
127 l_message := fnd_msg_pub.get (i, p_encoded => fnd_api.g_false);
128
129 IF (LENGTH (l_message) + LENGTH (NVL (x_message, ' '))) <= 2500
130 THEN
131 x_message := x_message || l_message;
132 ELSE
133 EXIT;
134 END IF;
135 END LOOP;
136 EXCEPTION
137 WHEN OTHERS
138 THEN
139 NULL;
140 END read_message;
141
142 -------------------
143 -- Deliverable Helper procedures/functions
144 FUNCTION getuomvalue (p_duration IN NUMBER, p_uom IN VARCHAR2)
145 RETURN VARCHAR2
146 IS
147 BEGIN
148 IF p_duration IS NOT NULL AND p_duration > 1 AND 'MTH' = p_uom
149 THEN
150 RETURN 'MTHS';
151 ELSE
152 IF p_duration IS NOT NULL AND p_duration <= 1 AND 'MTH' = p_uom
153 THEN
154 RETURN 'MTH';
155 END IF;
156 END IF;
157
158 IF p_duration IS NOT NULL AND p_duration > 1 AND 'WK' = p_uom
159 THEN
160 RETURN 'WKS';
161 ELSE
162 IF p_duration IS NOT NULL AND p_duration <= 1 AND 'WK' = p_uom
163 THEN
164 RETURN 'WK';
165 END IF;
166 END IF;
167
168 IF p_duration IS NOT NULL AND p_duration > 1 AND 'DAY' = p_uom
169 THEN
170 RETURN 'DAYS';
171 ELSE
172 IF p_duration IS NOT NULL AND p_duration <= 1 AND 'DAY' = p_uom
173 THEN
174 RETURN 'DAY';
175 END IF;
176 END IF;
177
178 RETURN p_uom;
179 EXCEPTION
180 WHEN OTHERS
181 THEN
182 RAISE;
183 END getuomvalue;
184
185 FUNCTION getdeldisplaysequence (p_deliverable_id IN NUMBER)
186 RETURN NUMBER
187 IS
188 l_disp_sequence NUMBER;
189 BEGIN
190 l_disp_sequence := REMAINDER (p_deliverable_id, 1000);
191
192 IF l_disp_sequence < 0
193 THEN
194 RETURN l_disp_sequence + 1000;
195 ELSE
196 RETURN l_disp_sequence;
197 END IF;
198 END getdeldisplaysequence;
199
200 FUNCTION isvalidcontact (p_contact_id IN NUMBER)
201 RETURN VARCHAR2
202 IS
203 CURSOR cur_val_contact
204 IS
205 SELECT 'Y'
206 FROM per_all_people_f e
207 WHERE e.current_employee_flag = 'Y'
208 AND TRUNC (SYSDATE) BETWEEN NVL (e.effective_start_date,
209 SYSDATE - 1
210 )
211 AND NVL (e.effective_end_date,
212 SYSDATE + 1)
213 AND person_id = p_contact_id;
214
215 -- CWK contract worket
216 CURSOR cur_val_contact2
217 IS
218 SELECT 'Y'
219 FROM per_all_people_f e
220 WHERE e.current_npw_flag = 'Y'
221 AND TRUNC (SYSDATE) BETWEEN NVL (e.effective_start_date,
222 SYSDATE - 1
223 )
224 AND NVL (e.effective_end_date,
225 SYSDATE + 1)
226 AND person_id = p_contact_id;
227
228 l_valid_contact VARCHAR2 (1) := 'Y';
229 BEGIN
230 OPEN cur_val_contact;
231
232 FETCH cur_val_contact
233 INTO l_valid_contact;
234
235 CLOSE cur_val_contact;
236
237 IF NVL (l_valid_contact, 'N') <> 'Y'
238 THEN
239 RETURN 'Y';
240 ELSE
241 IF NVL (fnd_profile.VALUE ('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'
242 THEN
243 l_valid_contact := NULL;
244
245 OPEN cur_val_contact;
246
247 FETCH cur_val_contact
248 INTO l_valid_contact;
249
250 CLOSE cur_val_contact;
251
252 IF NVL (l_valid_contact, 'N') <> 'Y'
253 THEN
254 RETURN 'Y';
255 END IF;
256 END IF;
257 END IF;
258
259 RETURN 'N';
260 EXCEPTION
261 WHEN OTHERS
262 THEN
263 RETURN 'N';
264 END isvalidcontact;
265
266 FUNCTION isvalidstartbusdocevent (
267 p_document_type IN VARCHAR2,
268 p_deliverable_type IN VARCHAR2,
269 p_bus_doc_event_id IN NUMBER
270 )
271 RETURN VARCHAR2
272 IS
273 CURSOR cur_val_event
274 IS
275 SELECT 'Y'
276 FROM okc_bus_doc_events_v evts,
277 okc_bus_doc_types_b doctypes,
278 okc_del_bus_doc_combxns delcomb,
279 okc_deliverable_types_b deltypes
280 WHERE evts.bus_doc_event_id = p_bus_doc_event_id
281 AND 'TEMPLATE' = p_document_type
282 AND deltypes.deliverable_type_code = p_deliverable_type
283 AND doctypes.document_type_class = delcomb.document_type_class
284 AND deltypes.deliverable_type_code = delcomb.deliverable_type_code
285 AND doctypes.document_type = evts.bus_doc_type
286 AND ( doctypes.document_type IN (
287 SELECT target_response_doc_type
288 FROM okc_bus_doc_types_b
289 WHERE document_type_class =
290 delcomb.document_type_class
291 AND target_response_doc_type IS NOT NULL)
292 OR doctypes.show_in_lov_yn = 'Y'
293 )
294 AND ( evts.start_end_qualifier = 'BOTH'
295 OR evts.start_end_qualifier = 'START'
296 );
297
298 l_valid_flag VARCHAR2 (1);
299 BEGIN
300 OPEN cur_val_event;
301
302 FETCH cur_val_event
303 INTO l_valid_flag;
304
305 CLOSE cur_val_event;
306
307 RETURN NVL (l_valid_flag, 'N');
308 EXCEPTION
309 WHEN OTHERS
310 THEN
311 RETURN 'N';
312 END isvalidstartbusdocevent;
313
314 FUNCTION isvalidendbusdocevent (
315 p_document_type IN VARCHAR2,
316 p_deliverable_type IN VARCHAR2,
317 p_bus_doc_event_id IN NUMBER
318 )
319 RETURN VARCHAR2
320 IS
321 CURSOR cur_val_event
322 IS
323 SELECT 'Y'
324 FROM okc_bus_doc_events_v evts,
325 okc_bus_doc_types_b doctypes,
326 okc_del_bus_doc_combxns delcomb,
327 okc_deliverable_types_b deltypes
328 WHERE 'TEMPLATE' = p_document_type
329 AND evts.bus_doc_event_id = p_bus_doc_event_id
330 AND deltypes.deliverable_type_code = p_deliverable_type
331 --- :selectedDeliverableType
332 AND doctypes.document_type_class = delcomb.document_type_class
333 AND deltypes.deliverable_type_code = delcomb.deliverable_type_code
334 AND doctypes.show_in_lov_yn = 'Y'
335 AND doctypes.document_type = evts.bus_doc_type
336 AND ( evts.start_end_qualifier = 'BOTH'
337 OR evts.start_end_qualifier = 'END'
338 );
339
340 l_valid_flag VARCHAR2 (1);
341 BEGIN
342 OPEN cur_val_event;
343
344 FETCH cur_val_event
345 INTO l_valid_flag;
346
347 CLOSE cur_val_event;
348
349 RETURN NVL (l_valid_flag, 'N');
350 EXCEPTION
351 WHEN OTHERS
352 THEN
353 RETURN 'N';
354 END isvalidendbusdocevent;
355
356 PROCEDURE get_event_details (
357 p_event_id IN NUMBER,
358 x_before_after OUT NOCOPY VARCHAR2
359 )
360 IS
361 BEGIN
362 SELECT before_after
363 INTO x_before_after
364 FROM okc_bus_doc_events_b
365 WHERE bus_doc_event_id = p_event_id;
366 EXCEPTION
367 WHEN OTHERS
368 THEN
369 NULL;
370 END get_event_details;
371
372 FUNCTION getprintduedatemsgname (
373 p_recurring_flag IN VARCHAR2,
374 p_start_fixed_flag IN VARCHAR2,
375 p_end_fixed_flag IN VARCHAR2,
376 p_repeating_frequency_uom IN VARCHAR2,
377 p_relative_st_date_uom IN VARCHAR2,
378 p_relative_end_date_uom IN VARCHAR2,
379 p_start_evt_before_after IN VARCHAR2,
380 p_end_evt_before_after IN VARCHAR2
381 )
382 RETURN VARCHAR2
383 IS
384 CURSOR cur_print_due_dt_msg_name
385 IS
386 SELECT message_name
387 FROM okc_del_messages
388 WHERE 1 = 1
389 AND recurring_flag = p_recurring_flag
390 AND start_fixed_flag = p_start_fixed_flag
391 AND end_fixed_flag = p_end_fixed_flag
392 AND repeating_frequency_uom = p_repeating_frequency_uom
393 AND relative_st_date_uom = p_relative_st_date_uom
394 AND relative_end_date_uom = p_relative_end_date_uom
395 AND start_evt_before_after = p_start_evt_before_after
396 AND end_evt_before_after = p_end_evt_before_after;
397
398 l_msg_name VARCHAR2 (60);
399 BEGIN
400 OPEN cur_print_due_dt_msg_name;
401
402 FETCH cur_print_due_dt_msg_name
403 INTO l_msg_name;
404
405 CLOSE cur_print_due_dt_msg_name;
406
407 RETURN l_msg_name;
408 EXCEPTION
409 WHEN OTHERS
410 THEN
411 RETURN NULL;
412 END getprintduedatemsgname;
413
414 FUNCTION isvalidstendeventsmatch (
415 p_st_event_id IN NUMBER,
416 p_end_event_id IN NUMBER
417 )
418 RETURN VARCHAR2
419 IS
420 CURSOR cur_val_event
421 IS
422 SELECT 'Y'
423 FROM okc_bus_doc_events_b start_event,
424 okc_bus_doc_events_b end_event
425 WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
426 AND end_event.bus_doc_event_id = p_end_event_id --end event id
427 AND start_event.bus_doc_type = end_event.bus_doc_type
428 UNION
429 SELECT 'Y'
430 FROM okc_bus_doc_events_b start_event,
431 okc_bus_doc_events_b end_event,
432 okc_bus_doc_types_b end_type
433 WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
434 AND end_event.bus_doc_event_id = p_end_event_id --end event id
435 AND end_type.document_type = end_event.bus_doc_type
436 AND start_event.bus_doc_type = end_type.target_response_doc_type
437 UNION
438 SELECT 'Y'
439 FROM okc_bus_doc_events_b start_event,
440 okc_bus_doc_events_b end_event,
441 okc_bus_doc_types_b start_type
442 WHERE start_event.bus_doc_event_id = p_st_event_id --start event id
443 AND end_event.bus_doc_event_id = p_end_event_id --end event id
444 AND start_type.document_type = start_event.bus_doc_type
445 AND end_event.bus_doc_type = start_type.target_response_doc_type;
446
447 l_val_flag VARCHAR2 (1);
448 BEGIN
449 OPEN cur_val_event;
450
451 FETCH cur_val_event
452 INTO l_val_flag;
453
454 CLOSE cur_val_event;
455
456 RETURN NVL (l_val_flag, 'N');
457 EXCEPTION
458 WHEN OTHERS
459 THEN
460 RETURN 'N';
461 END isvalidstendeventsmatch;
462
463 -------------------
464 FUNCTION inittemplateinfo (p_template_id IN NUMBER)
465 RETURN VARCHAR2
466 IS
467 BEGIN
468 /*IF p_template_id = Nvl(g_template_id,'-1') THEN
469 RETURN 'Y';
470 END IF;*/
471 SELECT template_id, intent, status_code,
472 start_date, end_date, org_id
473 INTO g_template_id, g_template_intent, g_template_status_code,
474 g_template_start_date, g_template_end_date, g_template_org_id
475 FROM okc_terms_templates_all
476 WHERE template_id = p_template_id;
477
478 IF g_template_status_code IN ('DRAFT', 'REVISION', 'REJECTED')
479 THEN
480 g_clause_update_allowed := 'Y';
481 g_deliverable_update_allowed := 'Y';
482 g_headerinfo_update_allowed := 'Y';
483 ELSE
484 g_clause_update_allowed := 'N';
485 g_deliverable_update_allowed := 'N';
486
487 IF g_template_status_code = 'APPROVED'
488 THEN
489 g_headerinfo_update_allowed := 'Y';
490 ELSE
491 g_headerinfo_update_allowed := 'N';
492 END IF;
493 END IF;
494
495 RETURN 'Y';
496 EXCEPTION
497 WHEN OTHERS
498 THEN
499 RETURN 'N';
500 END inittemplateinfo;
501
502 FUNCTION isvalidou (p_org_id IN NUMBER)
503 RETURN VARCHAR2
504 IS
505 l_flag VARCHAR2 (1);
506 BEGIN
507 OPEN cur_validate_ou (p_org_id);
508
509 FETCH cur_validate_ou
510 INTO l_flag;
511
512 CLOSE cur_validate_ou;
513
514 RETURN NVL (l_flag, 'N');
515 EXCEPTION
516 WHEN OTHERS
517 THEN
518 IF cur_validate_ou%ISOPEN
519 THEN
520 CLOSE cur_validate_ou;
521 END IF;
522
523 RETURN 'N';
524 END isvalidou;
525
526 FUNCTION isvalidtemplate (p_template_id IN NUMBER, p_org_id IN NUMBER)
527 RETURN VARCHAR2
528 IS
529 l_flag VARCHAR2 (1);
530 BEGIN
531 SELECT 'Y'
532 INTO l_flag
533 FROM okc_terms_templates_all
534 WHERE template_id = p_template_id AND org_id = p_org_id;
535
536 RETURN NVL (l_flag, 'N');
537 EXCEPTION
538 WHEN OTHERS
539 THEN
540 RETURN 'N';
541 END isvalidtemplate;
542
543 FUNCTION isvalidlookup (p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2)
544 RETURN VARCHAR2
545 IS
546 l_flag VARCHAR2 (1);
547 BEGIN
548 OPEN cur_val_lookup (p_lookup_type, p_lookup_code);
549
550 FETCH cur_val_lookup
551 INTO l_flag;
552
553 CLOSE cur_val_lookup;
554
555 RETURN NVL (l_flag, 'N');
556 EXCEPTION
557 WHEN OTHERS
558 THEN
559 RETURN 'N';
560 END isvalidlookup;
561
562 FUNCTION isvalidlookup (
563 p_lookup_type IN VARCHAR2,
564 p_lookup_code IN VARCHAR2,
565 x_meaning OUT NOCOPY VARCHAR2
566 )
567 RETURN VARCHAR2
568 IS
569 l_flag VARCHAR2 (1);
570 --l_lookup_meaning VARCHAR2(80);
571 BEGIN
572 OPEN cur_lookup_meaning (p_lookup_type, p_lookup_code);
573
574 FETCH cur_lookup_meaning
575 INTO l_flag, x_meaning;
576
577 CLOSE cur_lookup_meaning;
578
579 RETURN NVL (l_flag, 'N');
580 EXCEPTION
581 WHEN OTHERS
582 THEN
583 RETURN 'N';
584 END isvalidlookup;
585
586 FUNCTION isvalidsection (p_template_id IN NUMBER, p_scn_id IN NUMBER)
587 RETURN VARCHAR2
588 IS
589 CURSOR cur_validate_section
590 IS
591 SELECT 'Y'
592 FROM okc_sections_b
593 WHERE document_type = 'TEMPLATE'
594 AND document_id = p_template_id
595 AND ID = p_scn_id;
596
597 l_flag VARCHAR2 (1);
598 BEGIN
599 OPEN cur_validate_section;
600
601 FETCH cur_validate_section
602 INTO l_flag;
603
604 CLOSE cur_validate_section;
605
606 RETURN NVL (l_flag, 'N');
607 EXCEPTION
608 WHEN OTHERS
609 THEN
610 RETURN 'N';
611 END isvalidsection;
612
613 FUNCTION isvalidclause (
614 p_article_id IN NUMBER,
615 p_org_id IN NUMBER,
616 p_intent IN VARCHAR2
617 )
618 RETURN VARCHAR2
619 IS
620 l_art_flag VARCHAR2 (1);
621 l_adopt_art_flag VARCHAR2 (1);
622
623 CURSOR cur_val_article
624 IS
625 SELECT 'Y'
626 FROM okc_articles_all
627 WHERE 1 = 1
628 AND standard_yn = 'Y'
629 AND org_id = p_org_id
630 AND article_intent = p_intent
631 AND article_id = p_article_id;
632
633 CURSOR cur_val_adopted_article
634 IS
635 SELECT 'Y'
636 FROM okc_articles_all art,
637 okc_article_versions ver,
638 okc_article_adoptions adp
639 WHERE art.article_id = ver.article_id
640 AND art.standard_yn = 'Y'
641 AND ver.global_yn = 'Y'
642 AND ver.article_status = 'APPROVED'
643 AND adp.global_article_version_id = ver.article_version_id
644 AND adp.adoption_type = 'ADOPTED'
645 AND adp.adoption_status = 'APPROVED'
646 AND art.article_id = p_article_id
647 AND art.article_intent = p_intent
648 AND adp.local_org_id = p_org_id;
649 BEGIN
650 OPEN cur_val_article;
651
652 FETCH cur_val_article
653 INTO l_art_flag;
654
655 CLOSE cur_val_article;
656
657 IF NVL (l_art_flag, 'N') = 'Y'
658 THEN
659 RETURN 'Y';
660 END IF;
661
662 OPEN cur_val_adopted_article;
663
664 FETCH cur_val_adopted_article
665 INTO l_adopt_art_flag;
666
667 CLOSE cur_val_adopted_article;
668
669 RETURN NVL (l_adopt_art_flag, 'N');
670 EXCEPTION
671 WHEN OTHERS
672 THEN
673 RETURN 'N';
674 END isvalidclause;
675
676 FUNCTION validate_bus_doc_type (p_bus_doc_type IN VARCHAR2)
677 RETURN VARCHAR2
678 IS
679 l_valid_bus_doc_type VARCHAR2 (1);
680 l_bus_doc_type VARCHAR2 (120) := p_bus_doc_type;
681 l_tmpl_intent VARCHAR2 (120) := g_template_intent;
682 BEGIN
683 SELECT 'Y'
684 INTO l_valid_bus_doc_type
685 FROM okc_bus_doc_types_b bus, fnd_lookups fnd
686 WHERE bus.document_type <> 'TEMPLATE'
687 AND DECODE (bus.document_type_class,
688 'REPOSITORY', NVL (bus.enable_contract_terms_yn, 'N'),
689 NVL (bus.show_in_lov_yn, 'Y')
690 ) = 'Y'
691 AND bus.intent = fnd.lookup_code
692 AND fnd.lookup_type = 'OKC_TERMS_INTENT'
693 AND bus.intent = l_tmpl_intent
694 AND bus.document_type = l_bus_doc_type;
695
696 RETURN NVL (l_valid_bus_doc_type, 'N');
697 EXCEPTION
698 WHEN OTHERS
699 THEN
700 RETURN 'N';
701 END validate_bus_doc_type;
702
703 FUNCTION validate_tmpl_usage_id (
704 p_template_id IN NUMBER,
705 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type,
706 p_mode IN VARCHAR2 DEFAULT 'UPDATE'
707 )
708 RETURN VARCHAR2
709 IS
710 l_valid_tmpl_usage_id VARCHAR2 (1) := 'Y';
711 l_document_type VARCHAR2 (30);
712 l_object_version_number NUMBER;
713 l_db_rec okc_allowed_tmpl_usages%ROWTYPE;
714 BEGIN
715 SELECT *
716 INTO l_db_rec
717 FROM okc_allowed_tmpl_usages
718 WHERE template_id = p_template_id
719 AND allowed_tmpl_usages_id = p_tmpl_usage_rec.allowed_tmpl_usages_id;
720
721 IF (p_tmpl_usage_rec.object_version_number = okc_api.g_miss_num)
722 THEN
723 p_tmpl_usage_rec.object_version_number :=
724 l_db_rec.object_version_number;
725 END IF;
726
727 IF p_mode = 'UPDATE'
728 THEN
729 IF (p_tmpl_usage_rec.document_type = okc_api.g_miss_char)
730 THEN
731 p_tmpl_usage_rec.document_type := l_db_rec.document_type;
732 END IF;
733
734 IF p_tmpl_usage_rec.default_yn = okc_api.g_miss_char
735 THEN
736 p_tmpl_usage_rec.default_yn := l_db_rec.default_yn;
737 END IF;
738
739 IF p_tmpl_usage_rec.attribute_category = okc_api.g_miss_char
740 THEN
741 p_tmpl_usage_rec.attribute_category :=
742 l_db_rec.attribute_category;
743 END IF;
744
745 IF p_tmpl_usage_rec.attribute1 = okc_api.g_miss_char
746 THEN
747 p_tmpl_usage_rec.attribute1 := l_db_rec.attribute1;
748 END IF;
749
750 IF p_tmpl_usage_rec.attribute2 = okc_api.g_miss_char
751 THEN
752 p_tmpl_usage_rec.attribute2 := l_db_rec.attribute2;
753 END IF;
754
755 IF p_tmpl_usage_rec.attribute3 = okc_api.g_miss_char
756 THEN
757 p_tmpl_usage_rec.attribute3 := l_db_rec.attribute3;
758 END IF;
759
760 IF p_tmpl_usage_rec.attribute4 = okc_api.g_miss_char
761 THEN
762 p_tmpl_usage_rec.attribute4 := l_db_rec.attribute4;
763 END IF;
764
765 IF p_tmpl_usage_rec.attribute5 = okc_api.g_miss_char
766 THEN
767 p_tmpl_usage_rec.attribute5 := l_db_rec.attribute5;
768 END IF;
769
770 IF p_tmpl_usage_rec.attribute6 = okc_api.g_miss_char
771 THEN
772 p_tmpl_usage_rec.attribute6 := l_db_rec.attribute6;
773 END IF;
774
775 IF p_tmpl_usage_rec.attribute7 = okc_api.g_miss_char
776 THEN
777 p_tmpl_usage_rec.attribute7 := l_db_rec.attribute7;
778 END IF;
779
780 IF p_tmpl_usage_rec.attribute8 = okc_api.g_miss_char
781 THEN
782 p_tmpl_usage_rec.attribute8 := l_db_rec.attribute8;
783 END IF;
784
785 IF p_tmpl_usage_rec.attribute9 = okc_api.g_miss_char
786 THEN
787 p_tmpl_usage_rec.attribute9 := l_db_rec.attribute9;
788 END IF;
789
790 IF p_tmpl_usage_rec.attribute10 = okc_api.g_miss_char
791 THEN
792 p_tmpl_usage_rec.attribute10 := l_db_rec.attribute10;
793 END IF;
794
795 IF p_tmpl_usage_rec.attribute11 = okc_api.g_miss_char
796 THEN
797 p_tmpl_usage_rec.attribute11 := l_db_rec.attribute11;
798 END IF;
799
800 IF p_tmpl_usage_rec.attribute12 = okc_api.g_miss_char
801 THEN
802 p_tmpl_usage_rec.attribute12 := l_db_rec.attribute12;
803 END IF;
804
805 IF p_tmpl_usage_rec.attribute13 = okc_api.g_miss_char
806 THEN
807 p_tmpl_usage_rec.attribute13 := l_db_rec.attribute13;
808 END IF;
809
810 IF p_tmpl_usage_rec.attribute14 = okc_api.g_miss_char
811 THEN
812 p_tmpl_usage_rec.attribute14 := l_db_rec.attribute14;
813 END IF;
814
815 IF p_tmpl_usage_rec.attribute15 = okc_api.g_miss_char
816 THEN
817 p_tmpl_usage_rec.attribute15 := l_db_rec.attribute15;
818 END IF;
819 END IF;
820
821 RETURN NVL (l_valid_tmpl_usage_id, 'N');
822 EXCEPTION
823 WHEN NO_DATA_FOUND
824 THEN
825 RETURN 'N';
826 WHEN OTHERS
827 THEN
828 RETURN 'N';
829 END validate_tmpl_usage_id;
830
831 --------------------------------------------------------------
832 PROCEDURE validate_template (
833 p_template_rec IN OUT NOCOPY terms_template_rec_type
834 )
835 IS
836 CURSOR cur_val_ou (p_org_id IN NUMBER)
837 IS
838 SELECT 'X'
839 FROM hr_operating_units ou, hr_organization_information oi
840 WHERE mo_global.check_access (ou.organization_id) = 'Y'
841 AND oi.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
842 AND oi.organization_id = ou.organization_id
843 AND NVL (date_to, SYSDATE) >= SYSDATE
844 AND ou.organization_id = p_org_id;
845
846 l_val_lookup VARCHAR2 (1);
847 l_proc VARCHAR2 (60) := 'VALIDATE_TEMPLATE';
848 BEGIN
849 -- Validate the required fields
850 IF p_template_rec.template_name IS NULL
851 THEN
852 okc_api.set_message (p_app_name => g_app_name,
853 p_msg_name => 'OKC_I_NOT_NULL',
854 p_token1 => 'FIELD',
855 p_token1_value => 'TEMPLATE_NAME'
856 );
857 RAISE fnd_api.g_exc_error;
858 END IF;
859
860 IF p_template_rec.intent IS NULL
861 THEN
862 okc_api.set_message (p_app_name => g_app_name,
863 p_msg_name => 'OKC_I_NOT_NULL',
864 p_token1 => 'FIELD',
865 p_token1_value => 'INTENT'
866 );
867 RAISE fnd_api.g_exc_error;
868 END IF;
869
870 IF p_template_rec.contract_expert_enabled = 'Y'
871 AND p_template_rec.xprt_scn_code IS NULL
872 THEN
873 okc_api.set_message (p_app_name => g_app_name,
874 p_msg_name => 'OKC_I_NOT_NULL',
875 p_token1 => 'FIELD',
876 p_token1_value => 'XPRT_SCN_CODE'
877 );
878 RAISE fnd_api.g_exc_error;
879 END IF;
880
881 IF p_template_rec.contract_expert_enabled IS NULL
882 AND p_template_rec.xprt_scn_code IS NOT NULL
883 THEN
884 okc_api.set_message (p_app_name => g_app_name,
885 p_msg_name => 'OKC_I_NOT_NULL',
886 p_token1 => 'FIELD',
887 p_token1_value => 'CONTRACT_EXPERT_ENABLED'
888 );
889 RAISE fnd_api.g_exc_error;
890 END IF;
891
892 -- Validate the input attributes
893 IF p_template_rec.intent NOT IN ('B', 'S')
894 THEN
895 okc_api.set_message (p_app_name => g_app_name,
896 p_msg_name => 'OKC_I_INVALID_VALUE',
897 p_token1 => 'FIELD',
898 p_token1_value => 'INTENT'
899 );
900 RAISE fnd_api.g_exc_error;
901 END IF;
902
903 -- validate section code
904 IF p_template_rec.xprt_scn_code IS NOT NULL
905 THEN
906 OPEN cur_val_lookup ('OKC_ARTICLE_SECTION',
907 p_template_rec.xprt_scn_code
908 );
909
910 FETCH cur_val_lookup
911 INTO l_val_lookup;
912
913 CLOSE cur_val_lookup;
914
915 IF NVL (l_val_lookup, 'X') <> 'Y'
916 THEN
917 okc_api.set_message (p_app_name => g_app_name,
918 p_msg_name => 'OKC_I_INVALID_VALUE',
919 p_token1 => 'FIELD',
920 p_token1_value => 'XPRT_SCN_CODE'
921 );
922 RAISE fnd_api.g_exc_error;
923 END IF;
924
925 l_val_lookup := NULL;
926 END IF;
927
928 -- Validate OU, OU must be in the current
929
930 -- Global flag can be applicable only for global org
931 IF p_template_rec.global_flag = 'Y'
932 THEN
933 IF p_template_rec.org_id <> fnd_profile.VALUE ('OKC_GLOBAL_ORG_ID')
934 THEN
935 okc_api.set_message (p_app_name => g_app_name,
936 p_msg_name => 'OKC_I_INVALID_VALUE',
937 p_token1 => 'FIELD',
938 p_token1_value => 'GLOBAL_ORG_ID'
939 );
940 RAISE fnd_api.g_exc_error;
941 END IF;
942 END IF;
943 -- Validate numbering scheme
944 EXCEPTION
945 WHEN OTHERS
946 THEN
947 set_proc_error_message (p_proc => l_proc);
948 RAISE;
949 END validate_template;
950
951 -------------------------------------------------------------
952 PROCEDURE create_template (
953 p_template_rec IN OUT NOCOPY terms_template_rec_type,
954 p_commit IN VARCHAR2 := fnd_api.g_false
955 )
956 IS
957 x_return_status VARCHAR2 (1);
958 x_msg_count NUMBER;
959 x_msg_data VARCHAR2 (2000);
960 l_operating_unit NUMBER;
961 l_validate_flag VARCHAR2 (1) := 'Y';
962
963 PROCEDURE default_template (
964 p_template_rec IN OUT NOCOPY terms_template_rec_type
965 )
966 IS
967 BEGIN
968 IF p_template_rec.template_id = okc_api.g_miss_num
969 THEN
970 p_template_rec.template_id := NULL;
971 END IF;
972
973 IF p_template_rec.template_name = okc_api.g_miss_char
974 THEN
975 p_template_rec.template_name := NULL;
976 END IF;
977
978 IF p_template_rec.intent = okc_api.g_miss_char
979 THEN
980 p_template_rec.intent := NULL;
981 END IF;
982
983 -- IF p_template_rec.status_code = okc_api.g_miss_char
984 -- THEN
985 p_template_rec.status_code := 'DRAFT';
986
987 -- END IF;
988 IF p_template_rec.start_date = okc_api.g_miss_date
989 THEN
990 p_template_rec.start_date := SYSDATE;
991 END IF;
992
993 IF p_template_rec.end_date = okc_api.g_miss_date
994 THEN
995 p_template_rec.end_date := NULL;
996 END IF;
997
998 IF p_template_rec.global_flag = okc_api.g_miss_char
999 THEN
1000 p_template_rec.global_flag := 'N';
1001 END IF;
1002
1003 IF p_template_rec.instruction_text = okc_api.g_miss_char
1004 THEN
1005 p_template_rec.instruction_text := NULL;
1006 END IF;
1007
1008 IF p_template_rec.description = okc_api.g_miss_char
1009 THEN
1010 p_template_rec.description := NULL;
1011 END IF;
1012
1013 IF p_template_rec.working_copy_flag = okc_api.g_miss_char
1014 THEN
1015 p_template_rec.working_copy_flag := 'N';
1016 END IF;
1017
1018 IF p_template_rec.parent_template_id = okc_api.g_miss_num
1019 THEN
1020 p_template_rec.parent_template_id := NULL;
1021 END IF;
1022
1023 IF p_template_rec.contract_expert_enabled = okc_api.g_miss_char
1024 THEN
1025 p_template_rec.contract_expert_enabled := 'N';
1026 END IF;
1027
1028 IF p_template_rec.template_model_id = okc_api.g_miss_num
1029 THEN
1030 p_template_rec.template_model_id := NULL;
1031 END IF;
1032
1033 IF p_template_rec.tmpl_numbering_scheme = okc_api.g_miss_num
1034 THEN
1035 p_template_rec.tmpl_numbering_scheme := NULL;
1036 END IF;
1037
1038 IF p_template_rec.print_template_id = okc_api.g_miss_num
1039 THEN
1040 p_template_rec.print_template_id := NULL;
1041 END IF;
1042
1043 IF p_template_rec.approval_wf_key = okc_api.g_miss_char
1044 THEN
1045 p_template_rec.approval_wf_key := NULL;
1046 END IF;
1047
1048 IF p_template_rec.cz_export_wf_key = okc_api.g_miss_char
1049 THEN
1050 p_template_rec.cz_export_wf_key := NULL;
1051 END IF;
1052
1053 IF p_template_rec.org_id = okc_api.g_miss_num
1054 THEN
1055 p_template_rec.org_id := mo_utils.get_default_org_id;
1056 END IF;
1057
1058 IF p_template_rec.orig_system_reference_code = okc_api.g_miss_char
1059 THEN
1060 p_template_rec.orig_system_reference_code := NULL;
1061 END IF;
1062
1063 IF p_template_rec.orig_system_reference_id1 = okc_api.g_miss_num
1064 THEN
1065 p_template_rec.orig_system_reference_id1 := NULL;
1066 END IF;
1067
1068 IF p_template_rec.orig_system_reference_id2 = okc_api.g_miss_num
1069 THEN
1070 p_template_rec.orig_system_reference_id2 := NULL;
1071 END IF;
1072
1073 IF p_template_rec.object_version_number = okc_api.g_miss_num
1074 THEN
1075 p_template_rec.object_version_number := 1;
1076 END IF;
1077
1078 IF p_template_rec.hide_yn = okc_api.g_miss_char
1079 THEN
1080 p_template_rec.hide_yn := NULL;
1081 END IF;
1082
1083 IF p_template_rec.attribute_category = okc_api.g_miss_char
1084 THEN
1085 p_template_rec.attribute_category := NULL;
1086 END IF;
1087
1088 IF p_template_rec.attribute1 = okc_api.g_miss_char
1089 THEN
1090 p_template_rec.attribute1 := NULL;
1091 END IF;
1092
1093 IF p_template_rec.attribute2 = okc_api.g_miss_char
1094 THEN
1095 p_template_rec.attribute2 := NULL;
1096 END IF;
1097
1098 IF p_template_rec.attribute3 = okc_api.g_miss_char
1099 THEN
1100 p_template_rec.attribute3 := NULL;
1101 END IF;
1102
1103 IF p_template_rec.attribute4 = okc_api.g_miss_char
1104 THEN
1105 p_template_rec.attribute4 := NULL;
1106 END IF;
1107
1108 IF p_template_rec.attribute5 = okc_api.g_miss_char
1109 THEN
1110 p_template_rec.attribute5 := NULL;
1111 END IF;
1112
1113 IF p_template_rec.attribute6 = okc_api.g_miss_char
1114 THEN
1115 p_template_rec.attribute6 := NULL;
1116 END IF;
1117
1118 IF p_template_rec.attribute7 = okc_api.g_miss_char
1119 THEN
1120 p_template_rec.attribute7 := NULL;
1121 END IF;
1122
1123 IF p_template_rec.attribute8 = okc_api.g_miss_char
1124 THEN
1125 p_template_rec.attribute8 := NULL;
1126 END IF;
1127
1128 IF p_template_rec.attribute9 = okc_api.g_miss_char
1129 THEN
1130 p_template_rec.attribute9 := NULL;
1131 END IF;
1132
1133 IF p_template_rec.attribute10 = okc_api.g_miss_char
1134 THEN
1135 p_template_rec.attribute10 := NULL;
1136 END IF;
1137
1138 IF p_template_rec.attribute11 = okc_api.g_miss_char
1139 THEN
1140 p_template_rec.attribute11 := NULL;
1141 END IF;
1142
1143 IF p_template_rec.attribute12 = okc_api.g_miss_char
1144 THEN
1145 p_template_rec.attribute12 := NULL;
1146 END IF;
1147
1148 IF p_template_rec.attribute13 = okc_api.g_miss_char
1149 THEN
1150 p_template_rec.attribute13 := NULL;
1151 END IF;
1152
1153 IF p_template_rec.attribute14 = okc_api.g_miss_char
1154 THEN
1155 p_template_rec.attribute14 := NULL;
1156 END IF;
1157
1158 IF p_template_rec.attribute15 = okc_api.g_miss_char
1159 THEN
1160 p_template_rec.attribute15 := NULL;
1161 END IF;
1162
1163 IF p_template_rec.xprt_request_id = okc_api.g_miss_num
1164 THEN
1165 p_template_rec.xprt_request_id := NULL;
1166 END IF;
1167
1168 IF p_template_rec.xprt_clause_mandatory_flag = okc_api.g_miss_char
1169 THEN
1170 p_template_rec.xprt_clause_mandatory_flag := 'N';
1171 END IF;
1172
1173 IF p_template_rec.xprt_scn_code = okc_api.g_miss_char
1174 THEN
1175 p_template_rec.xprt_scn_code := NULL;
1176 END IF;
1177
1178 IF p_template_rec.LANGUAGE = okc_api.g_miss_char
1179 THEN
1180 p_template_rec.LANGUAGE := NULL;
1181 END IF;
1182
1183 IF p_template_rec.translated_from_tmpl_id = okc_api.g_miss_num
1184 THEN
1185 p_template_rec.translated_from_tmpl_id := NULL;
1186 END IF;
1187 END default_template;
1188 BEGIN
1189 -- Default Row
1190 default_template (p_template_rec => p_template_rec);
1191 fnd_msg_pub.initialize;
1192
1193 -- Set the policy context
1194 IF p_template_rec.org_id IS NOT NULL
1195 THEN
1196 IF isvalidou (p_org_id => p_template_rec.org_id) <> 'Y'
1197 THEN
1198 okc_api.set_message (p_app_name => g_app_name,
1199 p_msg_name => 'OKC_I_INVALID_VALUE',
1200 p_token1 => 'FIELD',
1201 p_token1_value => 'ORG_ID'
1202 );
1203 RAISE fnd_api.g_exc_error;
1204 END IF;
1205
1206 -- Set Policy context
1207 mo_global.set_policy_context ('S', TO_CHAR (p_template_rec.org_id));
1208 ELSE
1209 okc_api.set_message (p_app_name => g_app_name,
1210 p_msg_name => 'OKC_I_NOT_NULL',
1211 p_token1 => 'FIELD',
1212 p_token1_value => 'ORG_ID'
1213 );
1214 RAISE fnd_api.g_exc_error;
1215 END IF;
1216
1217 -- Validate Row
1218 BEGIN
1219 validate_template (p_template_rec => p_template_rec);
1220 EXCEPTION
1221 WHEN fnd_api.g_exc_error
1222 THEN
1223 p_template_rec.status := g_ret_sts_error;
1224 RAISE;
1225 WHEN OTHERS
1226 THEN
1227 p_template_rec.status := g_ret_sts_error;
1228 RAISE;
1229 END;
1230
1231 -- Call OKC_TERMS_TEMPLATES_GRP Insert
1232 /*
1233 OKC_TERMS_TEMPLATES_ALL_S => Sequnce value will derived in the grp package
1234 WHO columns,Object version numberr also derived in the package.
1235 */
1236 BEGIN
1237 okc_terms_templates_grp.create_template
1238 (p_api_version => 1,
1239 p_init_msg_list => fnd_api.g_true,
1240 p_validation_level => fnd_api.g_valid_level_full,
1241 p_commit => fnd_api.g_false,
1242 x_return_status => x_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data,
1245 p_template_name => p_template_rec.template_name,
1246 p_template_id => p_template_rec.template_id,
1247 p_working_copy_flag => p_template_rec.working_copy_flag,
1248 p_intent => p_template_rec.intent,
1249 p_status_code => p_template_rec.status_code,
1250 p_start_date => p_template_rec.start_date,
1251 p_end_date => p_template_rec.end_date,
1252 p_global_flag => p_template_rec.global_flag,
1253 p_parent_template_id => p_template_rec.parent_template_id,
1254 p_print_template_id => p_template_rec.print_template_id,
1255 p_contract_expert_enabled => p_template_rec.contract_expert_enabled,
1256 p_xprt_clause_mandatory_flag => p_template_rec.xprt_clause_mandatory_flag,
1257 p_xprt_scn_code => p_template_rec.xprt_scn_code,
1258 p_template_model_id => p_template_rec.template_model_id,
1259 p_instruction_text => p_template_rec.instruction_text,
1260 p_tmpl_numbering_scheme => p_template_rec.tmpl_numbering_scheme,
1261 p_description => p_template_rec.description,
1262 p_approval_wf_key => p_template_rec.approval_wf_key,
1263 p_cz_export_wf_key => p_template_rec.cz_export_wf_key,
1264 p_orig_system_reference_code => p_template_rec.orig_system_reference_code,
1265 p_orig_system_reference_id1 => p_template_rec.orig_system_reference_id1,
1266 p_orig_system_reference_id2 => p_template_rec.orig_system_reference_id2,
1267 p_org_id => p_template_rec.org_id,
1268 p_attribute_category => p_template_rec.attribute_category,
1269 p_attribute1 => p_template_rec.attribute1,
1270 p_attribute2 => p_template_rec.attribute2,
1271 p_attribute3 => p_template_rec.attribute3,
1272 p_attribute4 => p_template_rec.attribute4,
1273 p_attribute5 => p_template_rec.attribute5,
1274 p_attribute6 => p_template_rec.attribute6,
1275 p_attribute7 => p_template_rec.attribute7,
1276 p_attribute8 => p_template_rec.attribute8,
1277 p_attribute9 => p_template_rec.attribute9,
1278 p_attribute10 => p_template_rec.attribute10,
1279 p_attribute11 => p_template_rec.attribute11,
1280 p_attribute12 => p_template_rec.attribute12,
1281 p_attribute13 => p_template_rec.attribute13,
1282 p_attribute14 => p_template_rec.attribute14,
1283 p_attribute15 => p_template_rec.attribute15,
1284 p_translated_from_tmpl_id => p_template_rec.translated_from_tmpl_id,
1285 p_language => p_template_rec.LANGUAGE,
1286 x_template_id => p_template_rec.template_id
1287 );
1288
1289 IF x_return_status <> g_ret_sts_success
1290 THEN
1291 p_template_rec.status := g_ret_sts_error;
1292 RAISE fnd_api.g_exc_error;
1293 ELSE
1294 p_template_rec.status := g_ret_sts_success;
1295 END IF;
1296 EXCEPTION
1297 WHEN OTHERS
1298 THEN
1299 RAISE;
1300 END;
1301 -- All existing Contract Expert rules with the Apply to All Templates option
1302 -- selected are assigned to the template automatically.
1303 -- In display VO will take care the above case.
1304 EXCEPTION
1305 WHEN OTHERS
1306 THEN
1307 p_template_rec.status := g_ret_sts_error;
1308 RAISE;
1309 END create_template;
1310
1311 PROCEDURE update_template (
1312 p_template_rec IN OUT NOCOPY terms_template_rec_type
1313 )
1314 IS
1315 l_template_db_rec okc_terms_templates_all%ROWTYPE;
1316 l_progress VARCHAR2 (3) := '000';
1317 x_return_status VARCHAR2 (1);
1318 x_msg_count NUMBER;
1319 x_msg_data VARCHAR2 (2000);
1320
1321 PROCEDURE default_row (
1322 p_template_rec IN OUT NOCOPY terms_template_rec_type,
1323 p_template_db_rec IN okc_terms_templates_all%ROWTYPE
1324 )
1325 IS
1326 BEGIN
1327 IF p_template_rec.template_id = okc_api.g_miss_num
1328 THEN
1329 p_template_rec.template_id := p_template_db_rec.template_id;
1330 END IF;
1331
1332 IF p_template_rec.template_name = okc_api.g_miss_char
1333 THEN
1334 p_template_rec.template_name := p_template_db_rec.template_name;
1335 END IF;
1336
1337 IF p_template_rec.intent = okc_api.g_miss_char
1338 THEN
1339 p_template_rec.intent := p_template_db_rec.intent;
1340 END IF;
1341
1342 IF p_template_rec.status_code = okc_api.g_miss_char
1343 THEN
1344 p_template_rec.status_code := p_template_db_rec.status_code;
1345 END IF;
1346
1347 IF p_template_rec.start_date = okc_api.g_miss_date
1348 THEN
1349 p_template_rec.start_date := p_template_db_rec.start_date;
1350 END IF;
1351
1352 IF p_template_rec.end_date = okc_api.g_miss_date
1353 THEN
1354 p_template_rec.end_date := p_template_db_rec.end_date;
1355 END IF;
1356
1357 IF p_template_rec.global_flag = okc_api.g_miss_char
1358 THEN
1359 p_template_rec.global_flag := p_template_db_rec.global_flag;
1360 END IF;
1361
1362 IF p_template_rec.instruction_text = okc_api.g_miss_char
1363 THEN
1364 p_template_rec.instruction_text :=
1365 p_template_db_rec.instruction_text;
1366 END IF;
1367
1368 IF p_template_rec.description = okc_api.g_miss_char
1369 THEN
1370 p_template_rec.description := p_template_db_rec.description;
1371 END IF;
1372
1373 IF p_template_rec.working_copy_flag = okc_api.g_miss_char
1374 THEN
1375 p_template_rec.working_copy_flag :=
1376 p_template_db_rec.working_copy_flag;
1377 END IF;
1378
1379 IF p_template_rec.parent_template_id = okc_api.g_miss_num
1380 THEN
1381 p_template_rec.parent_template_id :=
1382 p_template_db_rec.parent_template_id;
1383 END IF;
1384
1385 IF p_template_rec.contract_expert_enabled = okc_api.g_miss_char
1386 THEN
1387 p_template_rec.contract_expert_enabled :=
1388 p_template_db_rec.contract_expert_enabled;
1389 END IF;
1390
1391 IF p_template_rec.template_model_id = okc_api.g_miss_num
1392 THEN
1393 p_template_rec.template_model_id :=
1394 p_template_db_rec.template_model_id;
1395 END IF;
1396
1397 IF p_template_rec.tmpl_numbering_scheme = okc_api.g_miss_num
1398 THEN
1399 p_template_rec.tmpl_numbering_scheme :=
1400 p_template_db_rec.tmpl_numbering_scheme;
1401 END IF;
1402
1403 IF p_template_rec.print_template_id = okc_api.g_miss_num
1404 THEN
1405 p_template_rec.print_template_id :=
1406 p_template_db_rec.print_template_id;
1407 END IF;
1408
1409 IF p_template_rec.approval_wf_key = okc_api.g_miss_char
1410 THEN
1411 p_template_rec.approval_wf_key :=
1412 p_template_db_rec.approval_wf_key;
1413 END IF;
1414
1415 IF p_template_rec.cz_export_wf_key = okc_api.g_miss_char
1416 THEN
1417 p_template_rec.cz_export_wf_key :=
1418 p_template_db_rec.cz_export_wf_key;
1419 END IF;
1420
1421 IF p_template_rec.last_update_login = okc_api.g_miss_num
1422 THEN
1423 p_template_rec.last_update_login :=
1424 p_template_db_rec.last_update_login;
1425 END IF;
1426
1427 IF p_template_rec.creation_date = okc_api.g_miss_date
1428 THEN
1429 p_template_rec.creation_date := p_template_db_rec.creation_date;
1430 END IF;
1431
1432 IF p_template_rec.created_by = okc_api.g_miss_num
1433 THEN
1434 p_template_rec.created_by := p_template_db_rec.created_by;
1435 END IF;
1436
1437 IF p_template_rec.last_updated_by = okc_api.g_miss_num
1438 THEN
1439 p_template_rec.last_updated_by :=
1440 p_template_db_rec.last_updated_by;
1441 END IF;
1442
1443 IF p_template_rec.last_update_date = okc_api.g_miss_date
1444 THEN
1445 p_template_rec.last_update_date :=
1446 p_template_db_rec.last_update_date;
1447 END IF;
1448
1449 IF p_template_rec.org_id = okc_api.g_miss_num
1450 THEN
1451 p_template_rec.org_id := p_template_db_rec.org_id;
1452 END IF;
1453
1454 IF p_template_rec.orig_system_reference_code = okc_api.g_miss_char
1455 THEN
1456 p_template_rec.orig_system_reference_code :=
1457 p_template_db_rec.orig_system_reference_code;
1458 END IF;
1459
1460 IF p_template_rec.orig_system_reference_id1 = okc_api.g_miss_num
1461 THEN
1462 p_template_rec.orig_system_reference_id1 :=
1463 p_template_db_rec.orig_system_reference_id1;
1464 END IF;
1465
1466 IF p_template_rec.orig_system_reference_id2 = okc_api.g_miss_num
1467 THEN
1468 p_template_rec.orig_system_reference_id2 :=
1469 p_template_db_rec.orig_system_reference_id2;
1470 END IF;
1471
1472 IF p_template_rec.object_version_number = okc_api.g_miss_num
1473 THEN
1474 p_template_rec.object_version_number :=
1475 p_template_db_rec.object_version_number;
1476 END IF;
1477
1478 IF p_template_rec.hide_yn = okc_api.g_miss_char
1479 THEN
1480 p_template_rec.hide_yn := p_template_db_rec.hide_yn;
1481 END IF;
1482
1483 IF p_template_rec.attribute_category = okc_api.g_miss_char
1484 THEN
1485 p_template_rec.attribute_category :=
1486 p_template_db_rec.attribute_category;
1487 END IF;
1488
1489 IF p_template_rec.attribute1 = okc_api.g_miss_char
1490 THEN
1491 p_template_rec.attribute1 := p_template_db_rec.attribute1;
1492 END IF;
1493
1494 IF p_template_rec.attribute2 = okc_api.g_miss_char
1495 THEN
1496 p_template_rec.attribute2 := p_template_db_rec.attribute2;
1497 END IF;
1498
1499 IF p_template_rec.attribute3 = okc_api.g_miss_char
1500 THEN
1501 p_template_rec.attribute3 := p_template_db_rec.attribute3;
1502 END IF;
1503
1504 IF p_template_rec.attribute4 = okc_api.g_miss_char
1505 THEN
1506 p_template_rec.attribute4 := p_template_db_rec.attribute4;
1507 END IF;
1508
1509 IF p_template_rec.attribute5 = okc_api.g_miss_char
1510 THEN
1511 p_template_rec.attribute5 := p_template_db_rec.attribute5;
1512 END IF;
1513
1514 IF p_template_rec.attribute6 = okc_api.g_miss_char
1515 THEN
1516 p_template_rec.attribute6 := p_template_db_rec.attribute6;
1517 END IF;
1518
1519 IF p_template_rec.attribute7 = okc_api.g_miss_char
1520 THEN
1521 p_template_rec.attribute7 := p_template_db_rec.attribute7;
1522 END IF;
1523
1524 IF p_template_rec.attribute8 = okc_api.g_miss_char
1525 THEN
1526 p_template_rec.attribute8 := p_template_db_rec.attribute8;
1527 END IF;
1528
1529 IF p_template_rec.attribute9 = okc_api.g_miss_char
1530 THEN
1531 p_template_rec.attribute9 := p_template_db_rec.attribute9;
1532 END IF;
1533
1534 IF p_template_rec.attribute10 = okc_api.g_miss_char
1535 THEN
1536 p_template_rec.attribute10 := p_template_db_rec.attribute10;
1537 END IF;
1538
1539 IF p_template_rec.attribute11 = okc_api.g_miss_char
1540 THEN
1541 p_template_rec.attribute11 := p_template_db_rec.attribute11;
1542 END IF;
1543
1544 IF p_template_rec.attribute12 = okc_api.g_miss_char
1545 THEN
1546 p_template_rec.attribute12 := p_template_db_rec.attribute12;
1547 END IF;
1548
1549 IF p_template_rec.attribute13 = okc_api.g_miss_char
1550 THEN
1551 p_template_rec.attribute13 := p_template_db_rec.attribute13;
1552 END IF;
1553
1554 IF p_template_rec.attribute14 = okc_api.g_miss_char
1555 THEN
1556 p_template_rec.attribute14 := p_template_db_rec.attribute14;
1557 END IF;
1558
1559 IF p_template_rec.attribute15 = okc_api.g_miss_char
1560 THEN
1561 p_template_rec.attribute15 := p_template_db_rec.attribute15;
1562 END IF;
1563
1564 IF p_template_rec.xprt_request_id = okc_api.g_miss_num
1565 THEN
1566 p_template_rec.xprt_request_id :=
1567 p_template_db_rec.xprt_request_id;
1568 END IF;
1569
1570 IF p_template_rec.xprt_clause_mandatory_flag = okc_api.g_miss_char
1571 THEN
1572 p_template_rec.xprt_clause_mandatory_flag :=
1573 p_template_db_rec.xprt_clause_mandatory_flag;
1574 END IF;
1575
1576 IF p_template_rec.xprt_scn_code = okc_api.g_miss_char
1577 THEN
1578 p_template_rec.xprt_scn_code := p_template_db_rec.xprt_scn_code;
1579 END IF;
1580
1581 IF p_template_rec.LANGUAGE = okc_api.g_miss_char
1582 THEN
1583 p_template_rec.LANGUAGE := p_template_db_rec.LANGUAGE;
1584 END IF;
1585
1586 IF p_template_rec.translated_from_tmpl_id = okc_api.g_miss_num
1587 THEN
1588 p_template_rec.translated_from_tmpl_id :=
1589 p_template_db_rec.translated_from_tmpl_id;
1590 END IF;
1591 END default_row;
1592 BEGIN
1593 -- Detect what values are changed and throw exception if the update is not allowed
1594
1595 -- Get the values from the db
1596 -- Compare it with the record
1597
1598 --
1599 BEGIN
1600 l_progress := '010';
1601
1602 SELECT *
1603 INTO l_template_db_rec
1604 FROM okc_terms_templates_all
1605 WHERE template_id = p_template_rec.template_id;
1606 EXCEPTION
1607 WHEN OTHERS
1608 THEN
1609 okc_api.set_message (p_app_name => g_app_name,
1610 p_msg_name => 'OKC_I_INVALID_VALUE',
1611 p_token1 => 'FIELD',
1612 p_token1_value => 'TEMPLATE_ID'
1613 );
1614 RAISE fnd_api.g_exc_error;
1615 END;
1616
1617 --
1618 IF inittemplateinfo (p_template_rec.template_id) <> 'Y'
1619 THEN
1620 okc_api.set_message (p_app_name => g_app_name,
1621 p_msg_name => 'OKC_I_INVALID_VALUE',
1622 p_token1 => 'FIELD',
1623 p_token1_value => 'TEMPLATE_ID'
1624 );
1625 RAISE fnd_api.g_exc_error;
1626 END IF;
1627
1628 --
1629 IF g_headerinfo_update_allowed = 'N'
1630 THEN
1631 l_progress := '020';
1632 -- Can't update anything just return the error.
1633 okc_api.set_message (p_app_name => g_app_name,
1634 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD',
1635 p_token1 => 'STATUS',
1636 p_token1_value => l_template_db_rec.status_code
1637 );
1638 RAISE fnd_api.g_exc_error;
1639 ELSE
1640 -- Org Id Can't be changed
1641 IF p_template_rec.org_id <> okc_api.g_miss_num
1642 AND NVL (p_template_rec.org_id, -1) <> l_template_db_rec.org_id
1643 THEN
1644 -- You can not change ORG_ID
1645 okc_api.set_message
1646 (p_app_name => g_app_name,
1647 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1648 p_token1 => 'STATUS',
1649 p_token1_value => l_template_db_rec.status_code,
1650 p_token2 => 'FIELD',
1651 p_token2_value => 'ORG_ID'
1652 );
1653 RAISE fnd_api.g_exc_error;
1654 END IF;
1655
1656 -- Intent Can't be changed
1657 IF p_template_rec.intent <> okc_api.g_miss_char
1658 AND NVL (p_template_rec.intent, 'ABC') <> l_template_db_rec.intent
1659 THEN
1660 -- You can not change Intent
1661 okc_api.set_message
1662 (p_app_name => g_app_name,
1663 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1664 p_token1 => 'STATUS',
1665 p_token1_value => l_template_db_rec.status_code,
1666 p_token2 => 'FIELD',
1667 p_token2_value => 'INTENT'
1668 );
1669 RAISE fnd_api.g_exc_error;
1670 END IF;
1671
1672 -- Status Can't be changed
1673 IF p_template_rec.status_code <> okc_api.g_miss_char
1674 AND NVL (p_template_rec.status_code, 'ABC') <>
1675 l_template_db_rec.status_code
1676 THEN
1677 -- You can not change Status
1678 okc_api.set_message (p_app_name => g_app_name,
1679 p_msg_name => 'OKC_I _TEMP_STS_CHANGE'
1680 );
1681 RAISE fnd_api.g_exc_error;
1682 END IF;
1683
1684 IF l_template_db_rec.status_code = 'APPROVED'
1685 THEN
1686 -- Name Can't be changed
1687 IF p_template_rec.template_name <> okc_api.g_miss_char
1688 AND NVL (p_template_rec.template_name, 'ABC') <>
1689 l_template_db_rec.template_name
1690 THEN
1691 -- You can not change TEmplate NAme
1692 okc_api.set_message
1693 (p_app_name => g_app_name,
1694 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1695 p_token1 => 'STATUS',
1696 p_token1_value => l_template_db_rec.status_code,
1697 p_token2 => 'FIELD',
1698 p_token2_value => 'TEMPLATE_NAME'
1699 );
1700 RAISE fnd_api.g_exc_error;
1701 END IF;
1702
1703 -- Start Date Can't be changed
1704 IF p_template_rec.start_date <> okc_api.g_miss_date
1705 AND NVL (p_template_rec.start_date, okc_api.g_miss_date) <>
1706 l_template_db_rec.start_date
1707 THEN
1708 -- You can not change Start Date
1709 okc_api.set_message
1710 (p_app_name => g_app_name,
1711 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1712 p_token1 => 'STATUS',
1713 p_token1_value => l_template_db_rec.status_code,
1714 p_token2 => 'FIELD',
1715 p_token2_value => 'START_DATE'
1716 );
1717 RAISE fnd_api.g_exc_error;
1718 END IF;
1719
1720 -- Contract Expert enabled can't be changed
1721 IF p_template_rec.contract_expert_enabled <>
1722 okc_api.g_miss_char
1723 AND NVL (p_template_rec.contract_expert_enabled, 'ABC') <>
1724 l_template_db_rec.contract_expert_enabled
1725 THEN
1726 -- You can not change Contract Expert Enabled
1727 okc_api.set_message
1728 (p_app_name => g_app_name,
1729 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1730 p_token1 => 'STATUS',
1731 p_token1_value => l_template_db_rec.status_code,
1732 p_token2 => 'FIELD',
1733 p_token2_value => 'CONTRACT_EXPERT_ENABLED'
1734 );
1735 RAISE fnd_api.g_exc_error;
1736 END IF;
1737
1738 --
1739 IF p_template_rec.xprt_scn_code <> okc_api.g_miss_char
1740 AND NVL (p_template_rec.xprt_scn_code, 'ABC') <>
1741 l_template_db_rec.xprt_scn_code
1742 THEN
1743 -- You can not change xprt_scn_code
1744 okc_api.set_message
1745 (p_app_name => g_app_name,
1746 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1747 p_token1 => 'STATUS',
1748 p_token1_value => l_template_db_rec.status_code,
1749 p_token2 => 'FIELD',
1750 p_token2_value => 'XPRT_SCN_CODE'
1751 );
1752 RAISE fnd_api.g_exc_error;
1753 END IF;
1754
1755 IF p_template_rec.xprt_clause_mandatory_flag <>
1756 okc_api.g_miss_char
1757 AND NVL (p_template_rec.xprt_clause_mandatory_flag, 'ABC') <>
1758 l_template_db_rec.xprt_clause_mandatory_flag
1759 THEN
1760 -- You can not change xprt_scn_code
1761 okc_api.set_message
1762 (p_app_name => g_app_name,
1763 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD_FIELD',
1764 p_token1 => 'STATUS',
1765 p_token1_value => l_template_db_rec.status_code,
1766 p_token2 => 'FIELD',
1767 p_token2_value => 'XPRT_CLAUSE_MANDATORY_FLAG'
1768 );
1769 RAISE fnd_api.g_exc_error;
1770 END IF;
1771 END IF; -- l_template_db_rec.status_code = 'APPROVED'
1772 END IF; -- g_headerinfo_update_allowed = 'N'
1773
1774 l_progress := '100';
1775 -- set the policy context
1776 mo_global.set_policy_context ('S', TO_CHAR (l_template_db_rec.org_id));
1777 l_progress := '110';
1778 -- Default values for coulmns from table for which the values are not provided by user
1779 default_row (p_template_rec => p_template_rec,
1780 p_template_db_rec => l_template_db_rec
1781 );
1782 l_progress := '120';
1783
1784 -- Validate Row
1785 BEGIN
1786 validate_template (p_template_rec => p_template_rec);
1787 EXCEPTION
1788 WHEN OTHERS
1789 THEN
1790 RAISE;
1791 END;
1792
1793 -- update_row
1794 BEGIN
1795 okc_terms_templates_grp.update_template
1796 (p_api_version => 1,
1797 p_init_msg_list => fnd_api.g_true,
1798 p_validation_level => fnd_api.g_valid_level_full,
1799 p_commit => fnd_api.g_false,
1800 x_return_status => x_return_status,
1801 x_msg_count => x_msg_count,
1802 x_msg_data => x_msg_data,
1803 p_template_name => p_template_rec.template_name,
1804 p_template_id => p_template_rec.template_id,
1805 p_working_copy_flag => p_template_rec.working_copy_flag,
1806 p_intent => p_template_rec.intent,
1807 p_status_code => p_template_rec.status_code,
1808 p_start_date => p_template_rec.start_date,
1809 p_end_date => p_template_rec.end_date,
1810 p_global_flag => p_template_rec.global_flag,
1811 p_parent_template_id => p_template_rec.parent_template_id,
1812 p_print_template_id => p_template_rec.print_template_id,
1813 p_contract_expert_enabled => p_template_rec.contract_expert_enabled,
1814 p_xprt_clause_mandatory_flag => p_template_rec.xprt_clause_mandatory_flag,
1815 p_xprt_scn_code => p_template_rec.xprt_scn_code,
1816 p_template_model_id => p_template_rec.template_model_id,
1817 p_instruction_text => p_template_rec.instruction_text,
1818 p_tmpl_numbering_scheme => p_template_rec.tmpl_numbering_scheme,
1819 p_description => p_template_rec.description,
1820 p_approval_wf_key => p_template_rec.approval_wf_key,
1821 p_cz_export_wf_key => p_template_rec.cz_export_wf_key,
1822 p_orig_system_reference_code => p_template_rec.orig_system_reference_code,
1823 p_orig_system_reference_id1 => p_template_rec.orig_system_reference_id1,
1824 p_orig_system_reference_id2 => p_template_rec.orig_system_reference_id2,
1825 p_org_id => p_template_rec.org_id,
1826 p_attribute_category => p_template_rec.attribute_category,
1827 p_attribute1 => p_template_rec.attribute1,
1828 p_attribute2 => p_template_rec.attribute2,
1829 p_attribute3 => p_template_rec.attribute3,
1830 p_attribute4 => p_template_rec.attribute4,
1831 p_attribute5 => p_template_rec.attribute5,
1832 p_attribute6 => p_template_rec.attribute6,
1833 p_attribute7 => p_template_rec.attribute7,
1834 p_attribute8 => p_template_rec.attribute8,
1835 p_attribute9 => p_template_rec.attribute9,
1836 p_attribute10 => p_template_rec.attribute10,
1837 p_attribute11 => p_template_rec.attribute11,
1838 p_attribute12 => p_template_rec.attribute12,
1839 p_attribute13 => p_template_rec.attribute13,
1840 p_attribute14 => p_template_rec.attribute14,
1841 p_attribute15 => p_template_rec.attribute15,
1842 p_translated_from_tmpl_id => p_template_rec.translated_from_tmpl_id,
1843 p_language => p_template_rec.LANGUAGE,
1844 p_object_version_number => p_template_rec.object_version_number
1845 );
1846
1847 IF x_return_status <> g_ret_sts_success
1848 THEN
1849 p_template_rec.status := g_ret_sts_error;
1850 RAISE fnd_api.g_exc_error;
1851 ELSE
1852 p_template_rec.status := g_ret_sts_success;
1853 END IF;
1854 EXCEPTION
1855 WHEN OTHERS
1856 THEN
1857 RAISE;
1858 END;
1859 EXCEPTION
1860 WHEN OTHERS
1861 THEN
1862 RAISE;
1863 END update_template;
1864
1865 PROCEDURE create_template (
1866 p_template_tbl IN OUT NOCOPY terms_template_tbl_type,
1867 p_commit IN VARCHAR2 := fnd_api.g_false
1868 )
1869 IS
1870 l_success_count NUMBER := 0;
1871 l_error_count NUMBER := 0;
1872 l_input_count NUMBER := p_template_tbl.COUNT;
1873 l_error_message VARCHAR2 (2500);
1874 l_proc VARCHAR2 (60) := 'CREATE_TEMPLATE';
1875 l_failed_rec_num NUMBER := 0;
1876 BEGIN
1877 IF p_template_tbl.COUNT > 0
1878 THEN
1879 FOR i IN p_template_tbl.FIRST .. p_template_tbl.LAST
1880 LOOP
1881 l_failed_rec_num := i;
1882
1883 BEGIN
1884 SAVEPOINT create_template_sp;
1885 create_template (p_template_rec => p_template_tbl (i));
1886
1887 IF p_template_tbl (i).status = g_ret_sts_success
1888 THEN
1889 l_success_count := l_success_count + 1;
1890
1891 IF fnd_api.to_boolean (p_commit)
1892 THEN
1893 COMMIT;
1894 END IF;
1895 ELSE
1896 l_error_count := l_error_count + 1;
1897 ROLLBACK TO create_template_sp;
1898 END IF;
1899 EXCEPTION
1900 WHEN fnd_api.g_exc_error
1901 THEN
1902 p_template_tbl (i).status := g_ret_sts_error;
1903 set_proc_error_message (p_proc => l_proc);
1904 set_rec_num_message (p_rec_num => l_failed_rec_num);
1905 read_message (l_error_message);
1906 p_template_tbl (i).errmsg := l_error_message;
1907 ROLLBACK TO create_template_sp;
1908 fnd_msg_pub.initialize;
1909 WHEN OTHERS
1910 THEN
1911 p_template_tbl (i).status := g_ret_sts_error;
1912 set_proc_error_message (p_proc => l_proc);
1913 set_rec_num_message (p_rec_num => l_failed_rec_num);
1914 okc_api.set_message (p_app_name => g_app_name,
1915 p_msg_name => g_unexpected_error,
1916 p_token1 => g_sqlcode_token,
1917 p_token1_value => SQLCODE,
1918 p_token2 => g_sqlerrm_token,
1919 p_token2_value => SQLERRM
1920 );
1921 read_message (l_error_message);
1922 p_template_tbl (i).errmsg := l_error_message;
1923 ROLLBACK TO create_template_sp;
1924 fnd_msg_pub.initialize;
1925 END;
1926 END LOOP;
1927 END IF;
1928 EXCEPTION
1929 WHEN OTHERS
1930 THEN
1931 ROLLBACK TO create_template_sp;
1932 RAISE;
1933 END create_template;
1934
1935 PROCEDURE update_template (
1936 p_template_tbl IN OUT NOCOPY okc_imp_terms_templates_pvt.terms_template_tbl_type,
1937 p_commit IN VARCHAR2 := fnd_api.g_false
1938 )
1939 IS
1940 l_success_count NUMBER := 0;
1941 l_error_count NUMBER := 0;
1942 l_input_count NUMBER := p_template_tbl.COUNT;
1943 l_error_message VARCHAR2 (2500);
1944 l_proc VARCHAR2 (60) := 'UPDATE_TEMPLATE';
1945 l_failed_rec_num NUMBER := 0;
1946 BEGIN
1947 IF p_template_tbl.COUNT > 0
1948 THEN
1949 FOR i IN p_template_tbl.FIRST .. p_template_tbl.LAST
1950 LOOP
1951 l_failed_rec_num := i;
1952
1953 BEGIN
1954 SAVEPOINT update_template_sp;
1955 update_template (p_template_rec => p_template_tbl (i));
1956
1957 IF p_template_tbl (i).status = g_ret_sts_success
1958 THEN
1959 l_success_count := l_success_count + 1;
1960
1961 IF fnd_api.to_boolean (p_commit)
1962 THEN
1963 COMMIT;
1964 END IF;
1965 ELSE
1966 l_error_count := l_error_count + 1;
1967 ROLLBACK TO update_template_sp;
1968 END IF;
1969 EXCEPTION
1970 WHEN fnd_api.g_exc_error
1971 THEN
1972 p_template_tbl (i).status := g_ret_sts_error;
1973 set_proc_error_message (p_proc => l_proc);
1974 set_rec_num_message (p_rec_num => l_failed_rec_num);
1975 read_message (l_error_message);
1976 p_template_tbl (i).errmsg := l_error_message;
1977 ROLLBACK TO update_template_sp;
1978 fnd_msg_pub.initialize;
1979 WHEN OTHERS
1980 THEN
1981 p_template_tbl (i).status := g_ret_sts_error;
1982 set_proc_error_message (p_proc => l_proc);
1983 set_rec_num_message (p_rec_num => l_failed_rec_num);
1984 okc_api.set_message (p_app_name => g_app_name,
1985 p_msg_name => g_unexpected_error,
1986 p_token1 => g_sqlcode_token,
1987 p_token1_value => SQLCODE,
1988 p_token2 => g_sqlerrm_token,
1989 p_token2_value => SQLERRM
1990 );
1991 read_message (l_error_message);
1992 p_template_tbl (i).errmsg := l_error_message;
1993 ROLLBACK TO update_template_sp;
1994 fnd_msg_pub.initialize;
1995 END;
1996 END LOOP;
1997 END IF;
1998 EXCEPTION
1999 WHEN OTHERS
2000 THEN
2001 ROLLBACK TO update_template_sp;
2002 RAISE;
2003 END update_template;
2004
2005 PROCEDURE create_section (
2006 p_section_rec IN OUT NOCOPY section_rec_type,
2007 p_commit IN VARCHAR2 := fnd_api.g_false
2008 )
2009 IS
2010 x_return_status VARCHAR2 (1);
2011 x_msg_count NUMBER;
2012 x_msg_data VARCHAR2 (2000);
2013 l_error_message VARCHAR2 (2500);
2014 l_proc VARCHAR2 (60) := 'CREATE_SECTION';
2015
2016 PROCEDURE default_row (p_section_rec IN OUT NOCOPY section_rec_type)
2017 IS
2018 BEGIN
2019 IF p_section_rec.ID = okc_api.g_miss_num
2020 THEN
2021 /* SELECT okc_sections_b_s.NEXTVAL
2022 INTO p_section_rec.ID
2023 FROM DUAL; */
2024 p_section_rec.ID := NULL;
2025 END IF;
2026
2027 -- Indicates if the section refers to a contract header or a standard clause set.
2028 IF p_section_rec.scn_type = okc_api.g_miss_char
2029 THEN
2030 p_section_rec.scn_type := NULL;
2031 END IF;
2032
2033 -- Contract header id for this section. If -99, then the section refers to a standard clause set.
2034 IF p_section_rec.chr_id = okc_api.g_miss_num
2035 THEN
2036 p_section_rec.chr_id := NULL;
2037 END IF;
2038
2039 -- Standard Clause Set code, for formatting standard clause sets.
2040 -- If -99, then this section refers to a contract.
2041 IF p_section_rec.sat_code = okc_api.g_miss_char
2042 THEN
2043 p_section_rec.sat_code := NULL;
2044 END IF;
2045
2046 -- Sequence number for the section
2047 IF p_section_rec.section_sequence = okc_api.g_miss_num
2048 THEN
2049 p_section_rec.section_sequence := NULL;
2050 END IF;
2051
2052 -- Sequential number set at 1 on insert and incremented on update.
2053 -- Used by APIs to ensure current record is passed.
2054 IF p_section_rec.object_version_number = okc_api.g_miss_num
2055 THEN
2056 p_section_rec.object_version_number := 1;
2057 END IF;
2058
2059 IF p_section_rec.created_by = okc_api.g_miss_num
2060 THEN
2061 p_section_rec.created_by := fnd_global.user_id;
2062 END IF;
2063
2064 IF p_section_rec.creation_date = okc_api.g_miss_date
2065 THEN
2066 p_section_rec.creation_date := SYSDATE;
2067 END IF;
2068
2069 IF p_section_rec.last_updated_by = okc_api.g_miss_num
2070 THEN
2071 p_section_rec.last_updated_by := fnd_global.user_id;
2072 END IF;
2073
2074 IF p_section_rec.last_update_date = okc_api.g_miss_date
2075 THEN
2076 p_section_rec.last_update_date := SYSDATE;
2077 END IF;
2078
2079 IF p_section_rec.last_update_login = okc_api.g_miss_num
2080 THEN
2081 p_section_rec.last_update_login := fnd_global.login_id;
2082 END IF;
2083
2084 -- The label to be printed for the section, such as III. or 3.
2085 IF p_section_rec.label = okc_api.g_miss_char
2086 THEN
2087 p_section_rec.label := NULL;
2088 END IF;
2089
2090 -- Indicates parent section for this subsection
2091 IF p_section_rec.scn_id = okc_api.g_miss_num
2092 THEN
2093 p_section_rec.scn_id := NULL;
2094 END IF;
2095
2096 IF p_section_rec.attribute_category = okc_api.g_miss_char
2097 THEN
2098 p_section_rec.attribute_category := NULL;
2099 END IF;
2100
2101 IF p_section_rec.attribute1 = okc_api.g_miss_char
2102 THEN
2103 p_section_rec.attribute1 := NULL;
2104 END IF;
2105
2106 IF p_section_rec.attribute2 = okc_api.g_miss_char
2107 THEN
2108 p_section_rec.attribute2 := NULL;
2109 END IF;
2110
2111 IF p_section_rec.attribute3 = okc_api.g_miss_char
2112 THEN
2113 p_section_rec.attribute3 := NULL;
2114 END IF;
2115
2116 IF p_section_rec.attribute4 = okc_api.g_miss_char
2117 THEN
2118 p_section_rec.attribute4 := NULL;
2119 END IF;
2120
2121 IF p_section_rec.attribute5 = okc_api.g_miss_char
2122 THEN
2123 p_section_rec.attribute5 := NULL;
2124 END IF;
2125
2126 IF p_section_rec.attribute6 = okc_api.g_miss_char
2127 THEN
2128 p_section_rec.attribute6 := NULL;
2129 END IF;
2130
2131 IF p_section_rec.attribute7 = okc_api.g_miss_char
2132 THEN
2133 p_section_rec.attribute7 := NULL;
2134 END IF;
2135
2136 IF p_section_rec.attribute8 = okc_api.g_miss_char
2137 THEN
2138 p_section_rec.attribute8 := NULL;
2139 END IF;
2140
2141 IF p_section_rec.attribute9 = okc_api.g_miss_char
2142 THEN
2143 p_section_rec.attribute9 := NULL;
2144 END IF;
2145
2146 IF p_section_rec.attribute10 = okc_api.g_miss_char
2147 THEN
2148 p_section_rec.attribute10 := NULL;
2149 END IF;
2150
2151 IF p_section_rec.attribute11 = okc_api.g_miss_char
2152 THEN
2153 p_section_rec.attribute11 := NULL;
2154 END IF;
2155
2156 IF p_section_rec.attribute12 = okc_api.g_miss_char
2157 THEN
2158 p_section_rec.attribute12 := NULL;
2159 END IF;
2160
2161 IF p_section_rec.attribute13 = okc_api.g_miss_char
2162 THEN
2163 p_section_rec.attribute13 := NULL;
2164 END IF;
2165
2166 IF p_section_rec.attribute14 = okc_api.g_miss_char
2167 THEN
2168 p_section_rec.attribute14 := NULL;
2169 END IF;
2170
2171 IF p_section_rec.attribute15 = okc_api.g_miss_char
2172 THEN
2173 p_section_rec.attribute15 := NULL;
2174 END IF;
2175
2176 IF p_section_rec.security_group_id = okc_api.g_miss_num
2177 THEN
2178 p_section_rec.security_group_id := NULL;
2179 END IF;
2180
2181 IF p_section_rec.old_id = okc_api.g_miss_num
2182 THEN
2183 p_section_rec.old_id := NULL;
2184 END IF;
2185
2186 -- Business document type. Refers to document_type in okc_bus_doc_types_b
2187 IF p_section_rec.document_type = okc_api.g_miss_char
2188 THEN
2189 p_section_rec.document_type := 'TEMPLATE';
2190 END IF;
2191
2192 -- Business document identifier. Refers to ID in various business document header tables
2193 IF p_section_rec.document_id = okc_api.g_miss_num
2194 THEN
2195 p_section_rec.document_id := NULL;
2196 END IF;
2197
2198 -- section identifier
2199 IF p_section_rec.scn_code = okc_api.g_miss_char
2200 THEN
2201 p_section_rec.scn_code := NULL;
2202 END IF;
2203
2204 -- Text to capture Section description
2205 IF p_section_rec.description = okc_api.g_miss_char
2206 THEN
2207 p_section_rec.description := NULL;
2208 END IF;
2209
2210 IF p_section_rec.amendment_description = okc_api.g_miss_char
2211 THEN
2212 p_section_rec.amendment_description := NULL;
2213 END IF;
2214
2215 IF p_section_rec.amendment_operation_code = okc_api.g_miss_char
2216 THEN
2217 p_section_rec.amendment_operation_code := NULL;
2218 END IF;
2219
2220 IF p_section_rec.orig_system_reference_code = okc_api.g_miss_char
2221 THEN
2222 p_section_rec.orig_system_reference_code := NULL;
2223 END IF;
2224
2225 IF p_section_rec.orig_system_reference_id1 = okc_api.g_miss_num
2226 THEN
2227 p_section_rec.orig_system_reference_id1 := NULL;
2228 END IF;
2229
2230 IF p_section_rec.orig_system_reference_id2 = okc_api.g_miss_num
2231 THEN
2232 p_section_rec.orig_system_reference_id2 := NULL;
2233 END IF;
2234
2235 -- Indicator for printing section when the document is printed. Valid values are Y and N.
2236 IF p_section_rec.print_yn = okc_api.g_miss_char
2237 THEN
2238 p_section_rec.print_yn := 'Y';
2239 END IF;
2240
2241 IF p_section_rec.summary_amend_operation_code = okc_api.g_miss_char
2242 THEN
2243 p_section_rec.summary_amend_operation_code := NULL;
2244 END IF;
2245
2246 -- Section title. This column is migrated from okc_sections_tl table
2247 IF p_section_rec.heading = okc_api.g_miss_char
2248 THEN
2249 p_section_rec.heading := NULL;
2250 END IF;
2251
2252 IF p_section_rec.last_amended_by = okc_api.g_miss_num
2253 THEN
2254 p_section_rec.last_amended_by := NULL;
2255 END IF;
2256
2257 IF p_section_rec.last_amendment_date = okc_api.g_miss_date
2258 THEN
2259 p_section_rec.last_amendment_date := NULL;
2260 END IF;
2261 END default_row;
2262
2263 PROCEDURE validate_row (p_section_rec IN OUT NOCOPY section_rec_type)
2264 IS
2265 l_val_lookup VARCHAR2 (1);
2266 l_exists_flag VARCHAR2 (1);
2267 l_proc VARCHAR2 (60) := 'VALIDATE_SECTION';
2268 BEGIN
2269 -- not null
2270 IF p_section_rec.section_sequence IS NULL
2271 THEN
2272 okc_api.set_message (p_app_name => g_app_name,
2273 p_msg_name => 'OKC_I_NOT_NULL',
2274 p_token1 => 'FIELD',
2275 p_token1_value => 'SECTION_SEQUENCE'
2276 );
2277 RAISE fnd_api.g_exc_error;
2278 END IF;
2279
2280 IF p_section_rec.document_id IS NULL
2281 THEN
2282 okc_api.set_message (p_app_name => g_app_name,
2283 p_msg_name => 'OKC_I_NOT_NULL',
2284 p_token1 => 'FIELD',
2285 p_token1_value => 'TEMPLATE_ID'
2286 -- since the flow is for only templates
2287 );
2288 RAISE fnd_api.g_exc_error;
2289 END IF;
2290
2291 IF p_section_rec.scn_code IS NULL
2292 THEN
2293 okc_api.set_message (p_app_name => g_app_name,
2294 p_msg_name => 'OKC_I_NOT_NULL',
2295 p_token1 => 'FIELD',
2296 p_token1_value => 'SCN_CODE'
2297 );
2298 RAISE fnd_api.g_exc_error;
2299 END IF;
2300
2301 -- Validate Docuemnt Type
2302 IF p_section_rec.document_type <> 'TEMPLATE'
2303 THEN
2304 okc_api.set_message (p_app_name => g_app_name,
2305 p_msg_name => 'OKC_I_INVALID_VALUE',
2306 p_token1 => 'FIELD',
2307 p_token1_value => 'TEMPLATE'
2308 );
2309 RAISE fnd_api.g_exc_error;
2310 END IF;
2311
2312 /*IF inittemplateinfo (p_template_id => p_section_rec.document_id) <>
2313 'Y'
2314 THEN
2315 okc_api.set_message (p_app_name => g_app_name,
2316 p_msg_name => 'OKC_I_INVALID_VALUE',
2317 p_token1 => 'FIELD',
2318 p_token1_value => 'TEMPLATE_ID'
2319 );
2320 RAISE fnd_api.g_exc_error;
2321 END IF; */
2322
2323 -- Validate SCN ID for sub-sections
2324 IF p_section_rec.scn_id IS NOT NULL
2325 THEN
2326 IF isvalidsection (p_template_id => p_section_rec.document_id,
2327 p_scn_id => p_section_rec.scn_id
2328 ) <> 'Y'
2329 THEN
2330 okc_api.set_message (p_app_name => g_app_name,
2331 p_msg_name => 'OKC_I_INVALID_VALUE',
2332 p_token1 => 'FIELD',
2333 p_token1_value => 'SCN_ID'
2334 );
2335 RAISE fnd_api.g_exc_error;
2336 END IF;
2337 END IF;
2338
2339 -- Validate SCN_CODE
2340 IF isvalidlookup (p_lookup_type => 'OKC_ARTICLE_SECTION',
2341 p_lookup_code => p_section_rec.scn_code,
2342 x_meaning => p_section_rec.description
2343 ) <> 'Y'
2344 THEN
2345 okc_api.set_message (p_app_name => g_app_name,
2346 p_msg_name => 'OKC_I_INVALID_VALUE',
2347 p_token1 => 'FIELD',
2348 p_token1_value => 'SCN_CODE'
2349 );
2350 RAISE fnd_api.g_exc_error;
2351 END IF;
2352 -- Add validation for sequence
2353 EXCEPTION
2354 WHEN OTHERS
2355 THEN
2356 set_proc_error_message (p_proc => l_proc);
2357 RAISE;
2358 END validate_row;
2359 BEGIN
2360 SAVEPOINT create_section_sp;
2361 fnd_msg_pub.initialize;
2362
2363 -- PRE - VALIDATION
2364 IF p_section_rec.document_id <> NVL (g_template_id, -1)
2365 THEN
2366 IF inittemplateinfo (p_template_id => p_section_rec.document_id) <>
2367 'Y'
2368 THEN
2369 okc_api.set_message (p_app_name => g_app_name,
2370 p_msg_name => 'OKC_I_INVALID_VALUE',
2371 p_token1 => 'FIELD',
2372 p_token1_value => 'TEMPLATE_ID'
2373 );
2374 RAISE fnd_api.g_exc_error;
2375 END IF;
2376 END IF;
2377
2378 IF g_clause_update_allowed <> 'Y'
2379 THEN
2380 okc_api.set_message (p_app_name => g_app_name,
2381 p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
2382 p_token1 => 'STATUS',
2383 p_token1_value => g_template_status_code,
2384 p_token2 => 'OBJECT',
2385 p_token2_value => 'SECTION'
2386 );
2387 RAISE fnd_api.g_exc_error;
2388 END IF;
2389
2390 -- Default Row
2391 default_row (p_section_rec => p_section_rec);
2392
2393 -- Set Org Context
2394
2395 -- Validate Row
2396 BEGIN
2397 fnd_msg_pub.initialize;
2398 validate_row (p_section_rec => p_section_rec);
2399 EXCEPTION
2400 WHEN OTHERS
2401 THEN
2402 ROLLBACK TO create_section_sp;
2403 RAISE;
2404 END;
2405
2406 fnd_msg_pub.initialize;
2407 -- Call to Insert Row
2408 okc_terms_sections_grp.create_section
2409 (p_api_version => 1,
2410 p_init_msg_list => fnd_api.g_true,
2411 p_validation_level => fnd_api.g_valid_level_full,
2412 p_commit => fnd_api.g_false,
2413 x_return_status => x_return_status,
2414 x_msg_count => x_msg_count,
2415 x_msg_data => x_msg_data,
2416 p_mode => 'NORMAL', --'AMEND' or 'NORMAL'
2417 p_id => p_section_rec.ID,
2418 p_section_sequence => p_section_rec.section_sequence,
2419 p_label => p_section_rec.label,
2420 p_scn_id => p_section_rec.scn_id,
2421 p_heading => p_section_rec.description,
2422 -- Mimicking the front end functionality
2423 p_description => p_section_rec.description,
2424 p_document_type => p_section_rec.document_type,
2425 p_document_id => p_section_rec.document_id,
2426 p_scn_code => p_section_rec.scn_code,
2427 p_amendment_description => p_section_rec.amendment_description,
2428 p_orig_system_reference_code => p_section_rec.orig_system_reference_code,
2429 p_orig_system_reference_id1 => p_section_rec.orig_system_reference_id1,
2430 p_orig_system_reference_id2 => p_section_rec.orig_system_reference_id2,
2431 p_print_yn => p_section_rec.print_yn,
2432 p_attribute_category => p_section_rec.attribute_category,
2433 p_attribute1 => p_section_rec.attribute1,
2434 p_attribute2 => p_section_rec.attribute2,
2435 p_attribute3 => p_section_rec.attribute3,
2436 p_attribute4 => p_section_rec.attribute4,
2437 p_attribute5 => p_section_rec.attribute5,
2438 p_attribute6 => p_section_rec.attribute6,
2439 p_attribute7 => p_section_rec.attribute7,
2440 p_attribute8 => p_section_rec.attribute8,
2441 p_attribute9 => p_section_rec.attribute9,
2442 p_attribute10 => p_section_rec.attribute10,
2443 p_attribute11 => p_section_rec.attribute11,
2444 p_attribute12 => p_section_rec.attribute12,
2445 p_attribute13 => p_section_rec.attribute13,
2446 p_attribute14 => p_section_rec.attribute14,
2447 p_attribute15 => p_section_rec.attribute15,
2448 x_id => p_section_rec.ID
2449 );
2450
2451 IF x_return_status = g_ret_sts_success
2452 THEN
2453 p_section_rec.status := x_return_status;
2454 ELSE
2455 p_section_rec.status := g_ret_sts_error;
2456 ROLLBACK TO create_section_sp;
2457 RAISE fnd_api.g_exc_error;
2458 END IF;
2459
2460 IF fnd_api.to_boolean (p_commit)
2461 THEN
2462 COMMIT;
2463 END IF;
2464 EXCEPTION
2465 WHEN fnd_api.g_exc_error
2466 THEN
2467 p_section_rec.status := g_ret_sts_error;
2468 set_proc_error_message (p_proc => l_proc);
2469 read_message (l_error_message);
2470 p_section_rec.errmsg := l_error_message;
2471 ROLLBACK TO create_section_sp;
2472 fnd_msg_pub.initialize;
2473 WHEN OTHERS
2474 THEN
2475 p_section_rec.status := g_ret_sts_error;
2476 set_proc_error_message (p_proc => l_proc);
2477 okc_api.set_message (p_app_name => g_app_name,
2478 p_msg_name => g_unexpected_error,
2479 p_token1 => g_sqlcode_token,
2480 p_token1_value => SQLCODE,
2481 p_token2 => g_sqlerrm_token,
2482 p_token2_value => SQLERRM
2483 );
2484 read_message (l_error_message);
2485 p_section_rec.errmsg := l_error_message;
2486 ROLLBACK TO create_section_sp;
2487 fnd_msg_pub.initialize;
2488 END create_section;
2489
2490 ---------------------------------------------------------------------------
2491 ---------------------------------------------------------------------------
2492 PROCEDURE create_article (
2493 p_article_rec IN OUT NOCOPY k_article_rec_type,
2494 p_commit IN VARCHAR2 := fnd_api.g_false
2495 )
2496 IS
2497 x_return_status VARCHAR2 (1);
2498 x_msg_count NUMBER;
2499 x_msg_data VARCHAR2 (2000);
2500 l_error_message VARCHAR2 (2500);
2501 l_proc VARCHAR2 (60) := 'CREATE_ARTICLE';
2502
2503 -------------SUB PROC-----------------------------------------------
2504 PROCEDURE default_row (p_article_rec IN OUT NOCOPY k_article_rec_type)
2505 IS
2506 BEGIN
2507 /*
2508 IF p_article_rec.ID = okc_api.g_miss_num
2509 THEN
2510 SELECT
2511 INTO p_article_rec.ID
2512 FROM DUAL;
2513 END IF; */
2514 IF p_article_rec.sav_sae_id = okc_api.g_miss_num
2515 THEN
2516 p_article_rec.sav_sae_id := NULL;
2517 END IF;
2518
2519 IF p_article_rec.sav_sav_release = okc_api.g_miss_char
2520 THEN
2521 p_article_rec.sav_sav_release := NULL;
2522 END IF;
2523
2524 IF p_article_rec.sbt_code = okc_api.g_miss_char
2525 THEN
2526 p_article_rec.sbt_code := NULL;
2527 END IF;
2528
2529 IF p_article_rec.cat_type = okc_api.g_miss_char
2530 THEN
2531 p_article_rec.cat_type := NULL;
2532 END IF;
2533
2534 IF p_article_rec.chr_id = okc_api.g_miss_num
2535 THEN
2536 p_article_rec.chr_id := NULL;
2537 END IF;
2538
2539 IF p_article_rec.cle_id = okc_api.g_miss_num
2540 THEN
2541 p_article_rec.cle_id := NULL;
2542 END IF;
2543
2544 IF p_article_rec.cat_id = okc_api.g_miss_num
2545 THEN
2546 p_article_rec.cat_id := NULL;
2547 END IF;
2548
2549 IF p_article_rec.dnz_chr_id = okc_api.g_miss_num
2550 THEN
2551 p_article_rec.dnz_chr_id := NULL;
2552 END IF;
2553
2554 IF p_article_rec.object_version_number = okc_api.g_miss_num
2555 THEN
2556 p_article_rec.object_version_number := 1;
2557 END IF;
2558
2559 IF p_article_rec.created_by = okc_api.g_miss_num
2560 THEN
2561 p_article_rec.created_by := fnd_global.user_id;
2562 END IF;
2563
2564 IF p_article_rec.creation_date = okc_api.g_miss_date
2565 THEN
2566 p_article_rec.creation_date := SYSDATE;
2567 END IF;
2568
2569 IF p_article_rec.last_updated_by = okc_api.g_miss_num
2570 THEN
2571 p_article_rec.last_updated_by := fnd_global.user_id;
2572 END IF;
2573
2574 IF p_article_rec.last_update_date = okc_api.g_miss_date
2575 THEN
2576 p_article_rec.last_update_date := SYSDATE;
2577 END IF;
2578
2579 IF p_article_rec.fulltext_yn = okc_api.g_miss_char
2580 THEN
2581 p_article_rec.fulltext_yn := NULL;
2582 END IF;
2583
2584 IF p_article_rec.last_update_login = okc_api.g_miss_num
2585 THEN
2586 p_article_rec.last_update_login := fnd_global.login_id;
2587 END IF;
2588
2589 IF p_article_rec.attribute_category = okc_api.g_miss_char
2590 THEN
2591 p_article_rec.attribute_category := NULL;
2592 END IF;
2593
2594 IF p_article_rec.attribute1 = okc_api.g_miss_char
2595 THEN
2596 p_article_rec.attribute1 := NULL;
2597 END IF;
2598
2599 IF p_article_rec.attribute2 = okc_api.g_miss_char
2600 THEN
2601 p_article_rec.attribute2 := NULL;
2602 END IF;
2603
2604 IF p_article_rec.attribute3 = okc_api.g_miss_char
2605 THEN
2606 p_article_rec.attribute3 := NULL;
2607 END IF;
2608
2609 IF p_article_rec.attribute4 = okc_api.g_miss_char
2610 THEN
2611 p_article_rec.attribute4 := NULL;
2612 END IF;
2613
2614 IF p_article_rec.attribute5 = okc_api.g_miss_char
2615 THEN
2616 p_article_rec.attribute5 := NULL;
2617 END IF;
2618
2619 IF p_article_rec.attribute6 = okc_api.g_miss_char
2620 THEN
2621 p_article_rec.attribute6 := NULL;
2622 END IF;
2623
2624 IF p_article_rec.attribute7 = okc_api.g_miss_char
2625 THEN
2626 p_article_rec.attribute7 := NULL;
2627 END IF;
2628
2629 IF p_article_rec.attribute8 = okc_api.g_miss_char
2630 THEN
2631 p_article_rec.attribute8 := NULL;
2632 END IF;
2633
2634 IF p_article_rec.attribute9 = okc_api.g_miss_char
2635 THEN
2636 p_article_rec.attribute9 := NULL;
2637 END IF;
2638
2639 IF p_article_rec.attribute10 = okc_api.g_miss_char
2640 THEN
2641 p_article_rec.attribute10 := NULL;
2642 END IF;
2643
2644 IF p_article_rec.attribute11 = okc_api.g_miss_char
2645 THEN
2646 p_article_rec.attribute11 := NULL;
2647 END IF;
2648
2649 IF p_article_rec.attribute12 = okc_api.g_miss_char
2650 THEN
2651 p_article_rec.attribute12 := NULL;
2652 END IF;
2653
2654 IF p_article_rec.attribute13 = okc_api.g_miss_char
2655 THEN
2656 p_article_rec.attribute13 := NULL;
2657 END IF;
2658
2659 IF p_article_rec.attribute14 = okc_api.g_miss_char
2660 THEN
2661 p_article_rec.attribute14 := NULL;
2662 END IF;
2663
2664 IF p_article_rec.attribute15 = okc_api.g_miss_char
2665 THEN
2666 p_article_rec.attribute15 := NULL;
2667 END IF;
2668
2669 IF p_article_rec.security_group_id = okc_api.g_miss_num
2670 THEN
2671 p_article_rec.security_group_id := NULL;
2672 END IF;
2673
2674 IF p_article_rec.old_id = okc_api.g_miss_num
2675 THEN
2676 p_article_rec.old_id := NULL;
2677 END IF;
2678
2679 IF p_article_rec.document_type = okc_api.g_miss_char
2680 OR p_article_rec.document_type IS NULL
2681 THEN
2682 p_article_rec.document_type := 'TEMPLATE';
2683 END IF;
2684
2685 IF p_article_rec.document_id = okc_api.g_miss_num
2686 THEN
2687 p_article_rec.document_id := NULL;
2688 END IF;
2689
2690 IF p_article_rec.source_flag = okc_api.g_miss_char
2691 THEN
2692 p_article_rec.source_flag := NULL;
2693 END IF;
2694
2695 IF p_article_rec.mandatory_yn = okc_api.g_miss_char
2696 THEN
2697 p_article_rec.mandatory_yn := 'N';
2698 END IF;
2699
2700 -- Rwa changes start
2701 IF p_article_rec.mandatory_rwa = okc_api.g_miss_char
2702 THEN
2703 p_article_rec.mandatory_rwa := NULL;
2704 END IF;
2705
2706 -- Rwa changes end
2707 IF p_article_rec.scn_id = okc_api.g_miss_num
2708 THEN
2709 p_article_rec.scn_id := NULL;
2710 END IF;
2711
2712 IF p_article_rec.label = okc_api.g_miss_char
2713 THEN
2714 p_article_rec.label := NULL;
2715 END IF;
2716
2717 IF p_article_rec.display_sequence = okc_api.g_miss_num
2718 THEN
2719 p_article_rec.display_sequence := NULL;
2720 END IF;
2721
2722 IF p_article_rec.amendment_description = okc_api.g_miss_char
2723 THEN
2724 p_article_rec.amendment_description := NULL;
2725 END IF;
2726
2727 IF p_article_rec.article_version_id = okc_api.g_miss_num
2728 THEN
2729 p_article_rec.article_version_id := NULL;
2730 END IF;
2731
2732 IF p_article_rec.orig_system_reference_code = okc_api.g_miss_char
2733 THEN
2734 p_article_rec.orig_system_reference_code := NULL;
2735 END IF;
2736
2737 IF p_article_rec.orig_system_reference_id1 = okc_api.g_miss_num
2738 THEN
2739 p_article_rec.orig_system_reference_id1 := NULL;
2740 END IF;
2741
2742 IF p_article_rec.orig_system_reference_id2 = okc_api.g_miss_num
2743 THEN
2744 p_article_rec.orig_system_reference_id2 := NULL;
2745 END IF;
2746
2747 IF p_article_rec.amendment_operation_code = okc_api.g_miss_char
2748 THEN
2749 p_article_rec.amendment_operation_code := NULL;
2750 END IF;
2751
2752 IF p_article_rec.summary_amend_operation_code = okc_api.g_miss_char
2753 THEN
2754 p_article_rec.summary_amend_operation_code := NULL;
2755 END IF;
2756
2757 IF p_article_rec.change_nonstd_yn = okc_api.g_miss_char
2758 THEN
2759 p_article_rec.change_nonstd_yn := 'N';
2760 END IF;
2761
2762 IF p_article_rec.print_text_yn = okc_api.g_miss_char
2763 THEN
2764 p_article_rec.print_text_yn := 'N';
2765 END IF;
2766
2767 IF p_article_rec.ref_article_id = okc_api.g_miss_num
2768 THEN
2769 p_article_rec.ref_article_id := NULL;
2770 END IF;
2771
2772 IF p_article_rec.ref_article_version_id = okc_api.g_miss_num
2773 THEN
2774 p_article_rec.ref_article_version_id := NULL;
2775 END IF;
2776
2777 IF p_article_rec.orig_article_id = okc_api.g_miss_num
2778 THEN
2779 p_article_rec.orig_article_id := p_article_rec.sav_sae_id;
2780 END IF;
2781
2782 IF p_article_rec.last_amended_by = okc_api.g_miss_num
2783 THEN
2784 p_article_rec.last_amended_by := NULL;
2785 END IF;
2786
2787 IF p_article_rec.last_amendment_date = okc_api.g_miss_date
2788 THEN
2789 p_article_rec.last_amendment_date := TO_DATE (NULL);
2790 END IF;
2791 END default_row;
2792
2793 ------------- SUB PROC END -----------------------------------------------
2794 PROCEDURE validate_row (p_article_rec IN OUT NOCOPY k_article_rec_type)
2795 IS
2796 l_proc VARCHAR2 (60) := 'VALIDATE_ARTICLE';
2797 BEGIN
2798 -- Validate required fields can't be null
2799 IF p_article_rec.sav_sae_id IS NULL
2800 THEN
2801 okc_api.set_message (p_app_name => g_app_name,
2802 p_msg_name => 'OKC_I_NOT_NULL',
2803 p_token1 => 'FIELD',
2804 p_token1_value => 'SAV_SAE_ID'
2805 );
2806 RAISE fnd_api.g_exc_error;
2807 END IF;
2808
2809 IF p_article_rec.document_type IS NULL
2810 THEN
2811 okc_api.set_message (p_app_name => g_app_name,
2812 p_msg_name => 'OKC_I_NOT_NULL',
2813 p_token1 => 'FIELD',
2814 p_token1_value => 'DOCUMENT_TYPE'
2815 );
2816 RAISE fnd_api.g_exc_error;
2817 END IF;
2818
2819 IF p_article_rec.document_id IS NULL
2820 THEN
2821 okc_api.set_message (p_app_name => g_app_name,
2822 p_msg_name => 'OKC_I_NOT_NULL',
2823 p_token1 => 'FIELD',
2824 p_token1_value => 'DOCUMENT_ID'
2825 );
2826 RAISE fnd_api.g_exc_error;
2827 END IF;
2828
2829 IF p_article_rec.scn_id IS NULL
2830 THEN
2831 okc_api.set_message (p_app_name => g_app_name,
2832 p_msg_name => 'OKC_I_NOT_NULL',
2833 p_token1 => 'FIELD',
2834 p_token1_value => 'SCN_ID'
2835 );
2836 RAISE fnd_api.g_exc_error;
2837 END IF;
2838
2839 IF p_article_rec.display_sequence IS NULL
2840 THEN
2841 okc_api.set_message (p_app_name => g_app_name,
2842 p_msg_name => 'OKC_I_NOT_NULL',
2843 p_token1 => 'FIELD',
2844 p_token1_value => 'DISPLAY_SEQUENCE'
2845 );
2846 RAISE fnd_api.g_exc_error;
2847 END IF;
2848
2849 -- Validate the input values
2850 /*
2851 IF inittemplateinfo (p_template_id => p_article_rec.document_id) <>
2852 'Y'
2853 THEN
2854 okc_api.set_message (p_app_name => g_app_name,
2855 p_msg_name => 'OKC_I_INVALID_VALUE',
2856 p_token1 => 'FIELD',
2857 p_token1_value => 'TEMPLATE_ID'
2858 );
2859 RAISE fnd_api.g_exc_error;
2860 END IF; */
2861 IF isvalidou (p_org_id => g_template_org_id) <> 'Y'
2862 THEN
2863 -- Can'not access template from this resp
2864 okc_api.set_message
2865 (p_app_name => g_app_name,
2866 p_msg_name => 'Can not access Template. Please change the responsibility'
2867 );
2868 RAISE fnd_api.g_exc_error;
2869 END IF;
2870
2871 -- Set the policy context
2872
2873 -- Validate the sav_sae_id
2874 IF (isvalidclause (p_article_id => p_article_rec.sav_sae_id,
2875 p_org_id => g_template_org_id,
2876 p_intent => g_template_intent
2877 )
2878 ) <> 'Y'
2879 THEN
2880 okc_api.set_message (p_app_name => g_app_name,
2881 p_msg_name => 'OKC_I_INVALID_VALUE',
2882 p_token1 => 'FIELD',
2883 p_token1_value => 'SAV_SAE_ID'
2884 );
2885 RAISE fnd_api.g_exc_error;
2886 END IF;
2887
2888 -- Validate
2889
2890 -- Validate Display sequence
2891 -- As of now skip this validation
2892
2893 -- Validate SCN_ID Section ID
2894 IF isvalidsection (p_template_id => p_article_rec.document_id,
2895 p_scn_id => p_article_rec.scn_id
2896 ) <> 'Y'
2897 THEN
2898 okc_api.set_message (p_app_name => g_app_name,
2899 p_msg_name => 'OKC_I_INVALID_VALUE',
2900 p_token1 => 'FIELD',
2901 p_token1_value => 'SCN_ID'
2902 );
2903 RAISE fnd_api.g_exc_error;
2904 END IF;
2905
2906 /* RWA Changes Start */
2907 IF p_article_rec.mandatory_yn IS NOT NULL
2908 AND p_article_rec.mandatory_yn NOT IN ('Y', 'N')
2909 THEN
2910 okc_api.set_message (p_app_name => g_app_name,
2911 p_msg_name => 'OKC_I_INVALID_VALUE',
2912 p_token1 => 'FIELD',
2913 p_token1_value => 'MANDATORY_YN'
2914 );
2915 RAISE fnd_api.g_exc_error;
2916 END IF;
2917
2918 IF p_article_rec.mandatory_rwa IS NOT NULL
2919 AND isvalidlookup ('OKC_CLAUSE_RWA', p_article_rec.mandatory_rwa) =
2920 'N'
2921 THEN
2922 okc_api.set_message (p_app_name => g_app_name,
2923 p_msg_name => 'OKC_I_INVALID_VALUE',
2924 p_token1 => 'FIELD',
2925 p_token1_value => 'MANDATORY_RWA'
2926 );
2927 RAISE fnd_api.g_exc_error;
2928 END IF;
2929 /* RWA Changes End */
2930 EXCEPTION
2931 WHEN OTHERS
2932 THEN
2933 set_proc_error_message (p_proc => l_proc);
2934 RAISE;
2935 END validate_row;
2936 ------------- SUB PROC END -----------------------------------------------
2937 BEGIN
2938 SAVEPOINT create_article_sp;
2939 fnd_msg_pub.initialize;
2940
2941 IF p_article_rec.document_id <> NVL (g_template_id, -1)
2942 THEN
2943 IF inittemplateinfo (p_template_id => p_article_rec.document_id) <>
2944 'Y'
2945 THEN
2946 okc_api.set_message (p_app_name => g_app_name,
2947 p_msg_name => 'OKC_I_INVALID_VALUE',
2948 p_token1 => 'FIELD',
2949 p_token1_value => 'TEMPLATE_ID'
2950 );
2951 RAISE fnd_api.g_exc_error;
2952 END IF;
2953 END IF;
2954
2955 IF g_clause_update_allowed <> 'Y'
2956 THEN
2957 okc_api.set_message (p_app_name => g_app_name,
2958 p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
2959 p_token1 => 'STATUS',
2960 p_token1_value => g_template_status_code,
2961 p_token2 => 'OBJECT',
2962 p_token2_value => 'ARTICLE'
2963 );
2964 RAISE fnd_api.g_exc_error;
2965 END IF;
2966
2967 -- default_row
2968 default_row (p_article_rec => p_article_rec);
2969
2970 -- Validate Row
2971 BEGIN
2972 fnd_msg_pub.initialize;
2973 validate_row (p_article_rec => p_article_rec);
2974 EXCEPTION
2975 WHEN OTHERS
2976 THEN
2977 ROLLBACK TO create_article_sp;
2978 RAISE;
2979 END;
2980
2981 -- Call Insert Statement.
2982 INSERT INTO okc_k_articles_b
2983 (ID, sav_sae_id,
2984 sav_sav_release, sbt_code,
2985 cat_type, chr_id,
2986 cle_id, cat_id,
2987 dnz_chr_id,
2988 object_version_number,
2989 created_by, creation_date,
2990 last_updated_by,
2991 last_update_date, fulltext_yn,
2992 last_update_login,
2993 attribute_category,
2994 attribute1, attribute2,
2995 attribute3, attribute4,
2996 attribute5, attribute6,
2997 attribute7, attribute8,
2998 attribute9, attribute10,
2999 attribute11, attribute12,
3000 attribute13, attribute14,
3001 attribute15,
3002 security_group_id, old_id,
3003 document_type, document_id,
3004 source_flag, mandatory_yn,
3005 scn_id, label,
3006 display_sequence,
3007 amendment_description,
3008 article_version_id,
3009 orig_system_reference_code,
3010 orig_system_reference_id1,
3011 orig_system_reference_id2,
3012 amendment_operation_code,
3013 summary_amend_operation_code,
3014 change_nonstd_yn,
3015 print_text_yn, ref_article_id,
3016 ref_article_version_id,
3017 orig_article_id,
3018 last_amended_by,
3019 last_amendment_date,
3020 mandatory_rwa
3021 )
3022 VALUES (okc_k_articles_b_s.NEXTVAL, p_article_rec.sav_sae_id,
3023 p_article_rec.sav_sav_release, p_article_rec.sbt_code,
3024 p_article_rec.cat_type, p_article_rec.chr_id,
3025 p_article_rec.cle_id, p_article_rec.cat_id,
3026 p_article_rec.dnz_chr_id,
3027 p_article_rec.object_version_number,
3028 p_article_rec.created_by, p_article_rec.creation_date,
3029 p_article_rec.last_updated_by,
3030 p_article_rec.last_update_date, p_article_rec.fulltext_yn,
3031 p_article_rec.last_update_login,
3032 p_article_rec.attribute_category,
3033 p_article_rec.attribute1, p_article_rec.attribute2,
3034 p_article_rec.attribute3, p_article_rec.attribute4,
3035 p_article_rec.attribute5, p_article_rec.attribute6,
3036 p_article_rec.attribute7, p_article_rec.attribute8,
3037 p_article_rec.attribute9, p_article_rec.attribute10,
3038 p_article_rec.attribute11, p_article_rec.attribute12,
3039 p_article_rec.attribute13, p_article_rec.attribute14,
3040 p_article_rec.attribute15,
3041 p_article_rec.security_group_id, p_article_rec.old_id,
3042 p_article_rec.document_type, p_article_rec.document_id,
3043 p_article_rec.source_flag, p_article_rec.mandatory_yn,
3044 p_article_rec.scn_id, p_article_rec.label,
3045 p_article_rec.display_sequence,
3046 p_article_rec.amendment_description,
3047 p_article_rec.article_version_id,
3048 p_article_rec.orig_system_reference_code,
3049 p_article_rec.orig_system_reference_id1,
3050 p_article_rec.orig_system_reference_id2,
3051 p_article_rec.amendment_operation_code,
3052 p_article_rec.summary_amend_operation_code,
3053 p_article_rec.change_nonstd_yn,
3054 p_article_rec.print_text_yn, p_article_rec.ref_article_id,
3055 p_article_rec.ref_article_version_id,
3056 p_article_rec.orig_article_id,
3057 p_article_rec.last_amended_by,
3058 p_article_rec.last_amendment_date,
3059 p_article_rec.mandatory_rwa
3060 )
3061 RETURNING ID
3062 INTO p_article_rec.ID;
3063
3064 p_article_rec.status := g_ret_sts_success;
3065
3066 IF fnd_api.to_boolean (p_commit)
3067 THEN
3068 COMMIT;
3069 END IF;
3070 EXCEPTION
3071 WHEN fnd_api.g_exc_error
3072 THEN
3073 p_article_rec.status := g_ret_sts_error;
3074 set_proc_error_message (p_proc => l_proc);
3075 read_message (l_error_message);
3076 p_article_rec.errmsg := l_error_message;
3077 ROLLBACK TO create_article_sp;
3078 fnd_msg_pub.initialize;
3079 WHEN OTHERS
3080 THEN
3081 p_article_rec.status := g_ret_sts_error;
3082 set_proc_error_message (p_proc => l_proc);
3083 okc_api.set_message (p_app_name => g_app_name,
3084 p_msg_name => g_unexpected_error,
3085 p_token1 => g_sqlcode_token,
3086 p_token1_value => SQLCODE,
3087 p_token2 => g_sqlerrm_token,
3088 p_token2_value => SQLERRM
3089 );
3090 read_message (l_error_message);
3091 p_article_rec.errmsg := l_error_message;
3092 ROLLBACK TO create_article_sp;
3093 fnd_msg_pub.initialize;
3094 END create_article;
3095
3096 PROCEDURE create_section (
3097 p_section_tbl IN OUT NOCOPY section_tbl_type,
3098 p_commit IN VARCHAR2 := fnd_api.g_false
3099 )
3100 IS
3101 BEGIN
3102 FOR i IN 1 .. p_section_tbl.COUNT
3103 LOOP
3104 create_section (p_section_rec => p_section_tbl (i));
3105 END LOOP;
3106 END create_section;
3107
3108 PROCEDURE create_article (
3109 p_article_tbl IN OUT NOCOPY k_article_tbl_type,
3110 p_commit IN VARCHAR2 := fnd_api.g_false
3111 )
3112 IS
3113 BEGIN
3114 FOR i IN 1 .. p_article_tbl.COUNT
3115 LOOP
3116 create_article (p_article_rec => p_article_tbl (i));
3117 END LOOP;
3118 END create_article;
3119
3120 PROCEDURE create_template_revision (
3121 p_template_id IN NUMBER,
3122 p_copy_deliverables IN VARCHAR2 DEFAULT 'Y',
3123 p_commit IN VARCHAR2 := fnd_api.g_false,
3124 x_new_template_id OUT NOCOPY NUMBER,
3125 x_return_status OUT NOCOPY VARCHAR2,
3126 x_msg_data OUT NOCOPY VARCHAR2,
3127 x_msg_count OUT NOCOPY NUMBER
3128 )
3129 IS
3130 l_proc VARCHAR2 (120) := 'CREATE_TEMPLATE_REVISION';
3131 BEGIN
3132 fnd_msg_pub.initialize;
3133
3134 -- PRE-VALIDATION
3135 IF inittemplateinfo (p_template_id => p_template_id) <> 'Y'
3136 THEN
3137 okc_api.set_message (p_app_name => g_app_name,
3138 p_msg_name => 'OKC_I_INVALID_VALUE',
3139 p_token1 => 'FIELD',
3140 p_token1_value => 'TEMPLATE_ID'
3141 );
3142 RAISE fnd_api.g_exc_error;
3143 END IF;
3144
3145 IF g_template_status_code <> 'APPROVED'
3146 THEN
3147 okc_api.set_message (p_app_name => g_app_name,
3148 p_msg_name => 'OKC_TERMS_REV_TMPL_INVALID',
3149 p_token1 => 'STATUS',
3150 p_token1_value => g_template_status_code
3151 );
3152 RAISE fnd_api.g_exc_error;
3153 END IF;
3154
3155 -- Call 2 API
3156 okc_terms_copy_grp.create_template_revision
3157 (p_api_version => 1,
3158 p_init_msg_list => fnd_api.g_true,
3159 p_commit => p_commit,
3160 p_template_id => p_template_id,
3161 p_copy_deliverables => p_copy_deliverables,
3162 x_template_id => x_new_template_id,
3163 x_return_status => x_return_status,
3164 x_msg_data => x_msg_data,
3165 x_msg_count => x_msg_count
3166 );
3167 EXCEPTION
3168 WHEN fnd_api.g_exc_error
3169 THEN
3170 x_return_status := g_ret_sts_error;
3171 set_proc_error_message (p_proc => l_proc);
3172 read_message (x_msg_data);
3173 fnd_msg_pub.initialize;
3174 WHEN OTHERS
3175 THEN
3176 x_return_status := g_ret_sts_error;
3177 set_proc_error_message (p_proc => l_proc);
3178 okc_api.set_message (p_app_name => g_app_name,
3179 p_msg_name => g_unexpected_error,
3180 p_token1 => g_sqlcode_token,
3181 p_token1_value => SQLCODE,
3182 p_token2 => g_sqlerrm_token,
3183 p_token2_value => SQLERRM
3184 );
3185 read_message (x_msg_data);
3186 fnd_msg_pub.initialize;
3187 END create_template_revision;
3188
3189 PROCEDURE delete_articles (
3190 p_template_id IN NUMBER,
3191 p_k_article_id_tbl IN k_article_id_tbl_type,
3192 p_commit IN VARCHAR2 := fnd_api.g_false,
3193 x_k_article_id_tbl OUT NOCOPY k_article_id_tbl_type,
3194 x_return_status OUT NOCOPY VARCHAR2,
3195 x_msg_data OUT NOCOPY VARCHAR2
3196 )
3197 IS
3198 l_proc VARCHAR2 (60) := 'DELETE_ARTICLES';
3199 BEGIN
3200 fnd_msg_pub.initialize;
3201
3202 IF p_template_id <> NVL (g_template_id, -1)
3203 THEN
3204 IF inittemplateinfo (p_template_id => p_template_id) <> 'Y'
3205 THEN
3206 okc_api.set_message (p_app_name => g_app_name,
3207 p_msg_name => 'OKC_I_INVALID_VALUE',
3208 p_token1 => 'FIELD',
3209 p_token1_value => 'TEMPLATE_ID'
3210 );
3211 RAISE fnd_api.g_exc_error;
3212 END IF;
3213 END IF;
3214
3215 -- Validate Template OU also:
3216 IF g_clause_update_allowed <> 'Y'
3217 THEN
3218 okc_api.set_message (p_app_name => g_app_name,
3219 p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
3220 p_token1 => 'STATUS',
3221 p_token1_value => g_template_status_code,
3222 p_token2 => 'OBJECT',
3223 p_token2_value => 'ARTICLE'
3224 );
3225 RAISE fnd_api.g_exc_error;
3226 ELSE
3227 FORALL i IN 1 .. p_k_article_id_tbl.COUNT
3228 DELETE FROM okc_k_articles_b
3229 WHERE ID = p_k_article_id_tbl (i)
3230 AND document_type = 'TEMPLATE'
3231 AND document_id = p_template_id
3232 RETURNING ID
3233 BULK COLLECT INTO x_k_article_id_tbl;
3234 END IF;
3235
3236 IF fnd_api.to_boolean (p_commit)
3237 THEN
3238 COMMIT;
3239 END IF;
3240
3241 x_return_status := g_ret_sts_success;
3242 EXCEPTION
3243 WHEN fnd_api.g_exc_error
3244 THEN
3245 x_return_status := g_ret_sts_error;
3246 set_proc_error_message (p_proc => l_proc);
3247 read_message (x_msg_data);
3248
3249 IF fnd_api.to_boolean (p_commit)
3250 THEN
3251 COMMIT;
3252 END IF;
3253
3254 fnd_msg_pub.initialize;
3255 WHEN OTHERS
3256 THEN
3257 x_return_status := g_ret_sts_error;
3258 set_proc_error_message (p_proc => l_proc);
3259 okc_api.set_message (p_app_name => g_app_name,
3260 p_msg_name => g_unexpected_error,
3261 p_token1 => g_sqlcode_token,
3262 p_token1_value => SQLCODE,
3263 p_token2 => g_sqlerrm_token,
3264 p_token2_value => SQLERRM
3265 );
3266 read_message (x_msg_data);
3267
3268 IF fnd_api.to_boolean (p_commit)
3269 THEN
3270 COMMIT;
3271 END IF;
3272
3273 fnd_msg_pub.initialize;
3274 END delete_articles;
3275
3276 PROCEDURE delete_sections (
3277 p_template_id IN NUMBER,
3278 p_section_id_tbl IN section_id_tbl_type,
3279 p_commit IN VARCHAR2 := fnd_api.g_false,
3280 x_section_id_tbl OUT NOCOPY section_id_tbl_type,
3281 x_return_status OUT NOCOPY VARCHAR2,
3282 x_msg_data OUT NOCOPY VARCHAR2
3283 )
3284 IS
3285 --l_k_article_id_tbl k_article_id_tbl_type
3286 l_proc VARCHAR2 (60) := 'DELETE_SECTIONS';
3287 BEGIN
3288 fnd_msg_pub.initialize;
3289
3290 IF p_template_id <> NVL (g_template_id, -1)
3291 THEN
3292 IF inittemplateinfo (p_template_id => p_template_id) <> 'Y'
3293 THEN
3294 okc_api.set_message (p_app_name => g_app_name,
3295 p_msg_name => 'OKC_I_INVALID_VALUE',
3296 p_token1 => 'FIELD',
3297 p_token1_value => 'TEMPLATE_ID'
3298 );
3299 RAISE fnd_api.g_exc_error;
3300 END IF;
3301 END IF;
3302
3303 IF g_clause_update_allowed <> 'Y'
3304 THEN
3305 okc_api.set_message (p_app_name => g_app_name,
3306 p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
3307 p_token1 => 'STATUS',
3308 p_token1_value => g_template_status_code,
3309 p_token2 => 'OBJECT',
3310 p_token2_value => 'SECTION'
3311 );
3312 RAISE fnd_api.g_exc_error;
3313 ELSE
3314 FORALL i IN 1 .. p_section_id_tbl.COUNT
3315 DELETE FROM okc_sections_b
3316 WHERE ID = p_section_id_tbl (i)
3317 AND document_type = 'TEMPLATE'
3318 AND document_id = p_template_id
3319 RETURNING ID
3320 BULK COLLECT INTO x_section_id_tbl;
3321 END IF;
3322
3323 -- Delete Articles associated with the section
3324 FORALL i IN 1 .. x_section_id_tbl.COUNT
3325 DELETE FROM okc_k_articles_b
3326 WHERE scn_id = x_section_id_tbl (i)
3327 AND document_type = 'TEMPLATE'
3328 AND document_id = p_template_id;
3329 x_return_status := g_ret_sts_success;
3330
3331 IF fnd_api.to_boolean (p_commit)
3332 THEN
3333 COMMIT;
3334 END IF;
3335 EXCEPTION
3336 WHEN fnd_api.g_exc_error
3337 THEN
3338 x_return_status := g_ret_sts_error;
3339 set_proc_error_message (p_proc => l_proc);
3340 read_message (x_msg_data);
3341
3342 IF fnd_api.to_boolean (p_commit)
3343 THEN
3344 COMMIT;
3345 END IF;
3346
3347 fnd_msg_pub.initialize;
3348 WHEN OTHERS
3349 THEN
3350 x_return_status := g_ret_sts_error;
3351 set_proc_error_message (p_proc => l_proc);
3352 okc_api.set_message (p_app_name => g_app_name,
3353 p_msg_name => g_unexpected_error,
3354 p_token1 => g_sqlcode_token,
3355 p_token1_value => SQLCODE,
3356 p_token2 => g_sqlerrm_token,
3357 p_token2_value => SQLERRM
3358 );
3359 read_message (x_msg_data);
3360
3361 IF fnd_api.to_boolean (p_commit)
3362 THEN
3363 COMMIT;
3364 END IF;
3365
3366 fnd_msg_pub.initialize;
3367 END delete_sections;
3368
3369 /**
3370 * Copy the p_deliverable_rec.deliverable_id to p_deliverable_rec.original_deliverable_id
3371 * p_deliverable_rec.display_sequence is p_deliverable_rec.deliverable_id%1000 => remainder
3372 * Populate the internal ORg
3373 */
3374 PROCEDURE create_deliverable (
3375 p_deliverable_rec IN OUT NOCOPY deliverable_rec_type,
3376 p_commit IN VARCHAR2 := fnd_api.g_false
3377 )
3378 IS
3379 l_start_date_fixed VARCHAR2 (1);
3380 l_end_date_fixed VARCHAR2 (1);
3381 l_start_evt_before_after VARCHAR2 (1);
3382 l_end_evt_before_after VARCHAR2 (1);
3383 l_repeating_frequency_uom VARCHAR2 (30);
3384 l_relative_st_date_uom VARCHAR2 (30);
3385 l_relative_end_date_uom VARCHAR2 (30);
3386 l_proc VARCHAR2 (60) := 'CREATE_DELIVERABLE';
3387 l_error_message VARCHAR2 (2500);
3388
3389 PROCEDURE default_row (
3390 p_deliverable_rec IN OUT NOCOPY deliverable_rec_type
3391 )
3392 IS
3393 BEGIN
3394 IF p_deliverable_rec.deliverable_id = okc_api.g_miss_num
3395 THEN
3396 SELECT okc_deliverable_id_s.NEXTVAL
3397 INTO p_deliverable_rec.deliverable_id
3398 FROM DUAL;
3399 END IF;
3400
3401 IF p_deliverable_rec.business_document_type = okc_api.g_miss_char
3402 THEN
3403 p_deliverable_rec.business_document_type := 'TEMPLATE';
3404 END IF;
3405
3406 IF p_deliverable_rec.business_document_id = okc_api.g_miss_num
3407 THEN
3408 p_deliverable_rec.business_document_id := NULL;
3409 END IF;
3410
3411 IF p_deliverable_rec.business_document_number = okc_api.g_miss_char
3412 THEN
3413 p_deliverable_rec.business_document_number := NULL;
3414 END IF;
3415
3416 IF p_deliverable_rec.deliverable_type = okc_api.g_miss_char
3417 THEN
3418 p_deliverable_rec.deliverable_type := NULL;
3419 END IF;
3420
3421 IF p_deliverable_rec.responsible_party = okc_api.g_miss_char
3422 THEN
3423 p_deliverable_rec.responsible_party := NULL;
3424 END IF;
3425
3426 IF p_deliverable_rec.internal_party_contact_id = okc_api.g_miss_num
3427 THEN
3428 p_deliverable_rec.internal_party_contact_id := NULL;
3429 END IF;
3430
3431 IF p_deliverable_rec.external_party_contact_id = okc_api.g_miss_num
3432 THEN
3433 p_deliverable_rec.external_party_contact_id := NULL;
3434 END IF;
3435
3436 IF p_deliverable_rec.deliverable_name = okc_api.g_miss_char
3437 THEN
3438 p_deliverable_rec.deliverable_name := NULL;
3439 END IF;
3440
3441 IF p_deliverable_rec.description = okc_api.g_miss_char
3442 THEN
3443 p_deliverable_rec.description := NULL;
3444 END IF;
3445
3446 IF p_deliverable_rec.comments = okc_api.g_miss_char
3447 THEN
3448 p_deliverable_rec.comments := NULL;
3449 END IF;
3450
3451 IF p_deliverable_rec.display_sequence = okc_api.g_miss_num
3452 THEN
3453 p_deliverable_rec.display_sequence :=
3454 getdeldisplaysequence (p_deliverable_rec.deliverable_id);
3455 END IF;
3456
3457 IF p_deliverable_rec.fixed_due_date_yn = okc_api.g_miss_char
3458 THEN
3459 p_deliverable_rec.fixed_due_date_yn := 'Y';
3460 END IF;
3461
3462 IF p_deliverable_rec.actual_due_date = okc_api.g_miss_date
3463 THEN
3464 p_deliverable_rec.actual_due_date := NULL;
3465 END IF;
3466
3467 IF p_deliverable_rec.print_due_date_msg_name = okc_api.g_miss_char
3468 THEN
3469 p_deliverable_rec.print_due_date_msg_name := NULL;
3470 END IF;
3471
3472 IF p_deliverable_rec.recurring_yn = okc_api.g_miss_char
3473 THEN
3474 p_deliverable_rec.recurring_yn := 'N';
3475 END IF;
3476
3477 IF p_deliverable_rec.notify_prior_due_date_value = okc_api.g_miss_num
3478 THEN
3479 p_deliverable_rec.notify_prior_due_date_value := NULL;
3480 END IF;
3481
3482 IF p_deliverable_rec.notify_prior_due_date_uom = okc_api.g_miss_char
3483 THEN
3484 p_deliverable_rec.notify_prior_due_date_uom := NULL;
3485 END IF;
3486
3487 IF p_deliverable_rec.notify_prior_due_date_yn = okc_api.g_miss_char
3488 THEN
3489 p_deliverable_rec.notify_prior_due_date_yn := 'N';
3490 END IF;
3491
3492 IF p_deliverable_rec.notify_completed_yn = okc_api.g_miss_char
3493 THEN
3494 p_deliverable_rec.notify_completed_yn := 'N';
3495 END IF;
3496
3497 IF p_deliverable_rec.notify_overdue_yn = okc_api.g_miss_char
3498 THEN
3499 p_deliverable_rec.notify_overdue_yn := 'N';
3500 END IF;
3501
3502 IF p_deliverable_rec.notify_escalation_yn = okc_api.g_miss_char
3503 THEN
3504 p_deliverable_rec.notify_escalation_yn := 'N';
3505 END IF;
3506
3507 IF p_deliverable_rec.notify_escalation_value = okc_api.g_miss_num
3508 THEN
3509 p_deliverable_rec.notify_escalation_value := NULL;
3510 END IF;
3511
3512 IF p_deliverable_rec.notify_escalation_uom = okc_api.g_miss_char
3513 THEN
3514 p_deliverable_rec.notify_escalation_uom := NULL;
3515 END IF;
3516
3517 IF p_deliverable_rec.escalation_assignee = okc_api.g_miss_num
3518 THEN
3519 p_deliverable_rec.escalation_assignee := NULL;
3520 END IF;
3521
3522 IF p_deliverable_rec.amendment_operation = okc_api.g_miss_char
3523 THEN
3524 p_deliverable_rec.amendment_operation := NULL;
3525 END IF;
3526
3527 IF p_deliverable_rec.prior_notification_id = okc_api.g_miss_num
3528 THEN
3529 p_deliverable_rec.prior_notification_id := NULL;
3530 END IF;
3531
3532 IF p_deliverable_rec.amendment_notes = okc_api.g_miss_char
3533 THEN
3534 p_deliverable_rec.amendment_notes := NULL;
3535 END IF;
3536
3537 IF p_deliverable_rec.completed_notification_id = okc_api.g_miss_num
3538 THEN
3539 p_deliverable_rec.completed_notification_id := NULL;
3540 END IF;
3541
3542 IF p_deliverable_rec.overdue_notification_id = okc_api.g_miss_num
3543 THEN
3544 p_deliverable_rec.overdue_notification_id := NULL;
3545 END IF;
3546
3547 IF p_deliverable_rec.escalation_notification_id = okc_api.g_miss_num
3548 THEN
3549 p_deliverable_rec.escalation_notification_id := NULL;
3550 END IF;
3551
3552 IF p_deliverable_rec.LANGUAGE = okc_api.g_miss_char
3553 THEN
3554 p_deliverable_rec.LANGUAGE := USERENV ('Lang');
3555 END IF;
3556
3557 IF p_deliverable_rec.original_deliverable_id = okc_api.g_miss_num
3558 THEN
3559 p_deliverable_rec.original_deliverable_id :=
3560 p_deliverable_rec.deliverable_id;
3561 END IF;
3562
3563 IF p_deliverable_rec.requester_id = okc_api.g_miss_num
3564 THEN
3565 p_deliverable_rec.requester_id := NULL;
3566 END IF;
3567
3568 IF p_deliverable_rec.external_party_id = okc_api.g_miss_num
3569 THEN
3570 p_deliverable_rec.external_party_id := NULL;
3571 END IF;
3572
3573 IF p_deliverable_rec.recurring_del_parent_id = okc_api.g_miss_num
3574 THEN
3575 p_deliverable_rec.recurring_del_parent_id := NULL;
3576 END IF;
3577
3578 --IF p_deliverable_rec.business_document_version = okc_api.g_miss_num
3579 --THEN
3580 p_deliverable_rec.business_document_version := -99;
3581
3582 --END IF;
3583 IF p_deliverable_rec.relative_st_date_duration = okc_api.g_miss_num
3584 THEN
3585 p_deliverable_rec.relative_st_date_duration := NULL;
3586 END IF;
3587
3588 IF p_deliverable_rec.relative_st_date_uom = okc_api.g_miss_char
3589 THEN
3590 p_deliverable_rec.relative_st_date_uom := NULL;
3591 END IF;
3592
3593 IF p_deliverable_rec.relative_st_date_event_id = okc_api.g_miss_num
3594 THEN
3595 p_deliverable_rec.relative_st_date_event_id := NULL;
3596 END IF;
3597
3598 IF p_deliverable_rec.relative_end_date_duration = okc_api.g_miss_num
3599 THEN
3600 p_deliverable_rec.relative_end_date_duration := NULL;
3601 END IF;
3602
3603 IF p_deliverable_rec.relative_end_date_uom = okc_api.g_miss_char
3604 THEN
3605 p_deliverable_rec.relative_end_date_uom := NULL;
3606 END IF;
3607
3608 IF p_deliverable_rec.relative_end_date_event_id = okc_api.g_miss_num
3609 THEN
3610 p_deliverable_rec.relative_end_date_event_id := NULL;
3611 END IF;
3612
3613 IF p_deliverable_rec.repeating_day_of_month = okc_api.g_miss_char
3614 THEN
3615 p_deliverable_rec.repeating_day_of_month := NULL;
3616 END IF;
3617
3618 IF p_deliverable_rec.repeating_day_of_week = okc_api.g_miss_char
3619 THEN
3620 p_deliverable_rec.repeating_day_of_week := NULL;
3621 END IF;
3622
3623 IF p_deliverable_rec.repeating_frequency_uom = okc_api.g_miss_char
3624 THEN
3625 p_deliverable_rec.repeating_frequency_uom := NULL;
3626 END IF;
3627
3628 IF p_deliverable_rec.repeating_duration = okc_api.g_miss_num
3629 THEN
3630 p_deliverable_rec.repeating_duration := NULL;
3631 END IF;
3632
3633 IF p_deliverable_rec.fixed_start_date = okc_api.g_miss_date
3634 THEN
3635 p_deliverable_rec.fixed_start_date := NULL;
3636 END IF;
3637
3638 IF p_deliverable_rec.fixed_end_date = okc_api.g_miss_date
3639 THEN
3640 p_deliverable_rec.fixed_end_date := NULL;
3641 END IF;
3642
3643 IF p_deliverable_rec.manage_yn = okc_api.g_miss_char
3644 THEN
3645 p_deliverable_rec.manage_yn := 'N';
3646 END IF;
3647
3648 IF p_deliverable_rec.internal_party_id = okc_api.g_miss_num
3649 THEN
3650 p_deliverable_rec.internal_party_id := NULL;
3651 END IF;
3652
3653 --IF p_deliverable_rec.deliverable_status = okc_api.g_miss_char
3654 --THEN
3655 p_deliverable_rec.deliverable_status := 'INACTIVE';
3656
3657 --END IF;
3658 IF p_deliverable_rec.status_change_notes = okc_api.g_miss_char
3659 THEN
3660 p_deliverable_rec.status_change_notes := NULL;
3661 END IF;
3662
3663 IF p_deliverable_rec.created_by = okc_api.g_miss_num
3664 THEN
3665 p_deliverable_rec.created_by := fnd_global.user_id;
3666 END IF;
3667
3668 IF p_deliverable_rec.creation_date = okc_api.g_miss_date
3669 THEN
3670 p_deliverable_rec.creation_date := SYSDATE;
3671 END IF;
3672
3673 IF p_deliverable_rec.last_updated_by = okc_api.g_miss_num
3674 THEN
3675 p_deliverable_rec.last_updated_by := fnd_global.user_id;
3676 END IF;
3677
3678 IF p_deliverable_rec.last_update_date = okc_api.g_miss_date
3679 THEN
3680 p_deliverable_rec.last_update_date := SYSDATE;
3681 END IF;
3682
3683 IF p_deliverable_rec.last_update_login = okc_api.g_miss_num
3684 THEN
3685 p_deliverable_rec.last_update_login := fnd_global.login_id;
3686 END IF;
3687
3688 IF p_deliverable_rec.object_version_number = okc_api.g_miss_num
3689 THEN
3690 p_deliverable_rec.object_version_number := 1;
3691 END IF;
3692
3693 IF p_deliverable_rec.attribute_category = okc_api.g_miss_char
3694 THEN
3695 p_deliverable_rec.attribute_category := NULL;
3696 END IF;
3697
3698 IF p_deliverable_rec.attribute1 = okc_api.g_miss_char
3699 THEN
3700 p_deliverable_rec.attribute1 := NULL;
3701 END IF;
3702
3703 IF p_deliverable_rec.attribute2 = okc_api.g_miss_char
3704 THEN
3705 p_deliverable_rec.attribute2 := NULL;
3706 END IF;
3707
3708 IF p_deliverable_rec.attribute3 = okc_api.g_miss_char
3709 THEN
3710 p_deliverable_rec.attribute3 := NULL;
3711 END IF;
3712
3713 IF p_deliverable_rec.attribute4 = okc_api.g_miss_char
3714 THEN
3715 p_deliverable_rec.attribute4 := NULL;
3716 END IF;
3717
3718 IF p_deliverable_rec.attribute5 = okc_api.g_miss_char
3719 THEN
3720 p_deliverable_rec.attribute5 := NULL;
3721 END IF;
3722
3723 IF p_deliverable_rec.attribute6 = okc_api.g_miss_char
3724 THEN
3725 p_deliverable_rec.attribute6 := NULL;
3726 END IF;
3727
3728 IF p_deliverable_rec.attribute7 = okc_api.g_miss_char
3729 THEN
3730 p_deliverable_rec.attribute7 := NULL;
3731 END IF;
3732
3733 IF p_deliverable_rec.attribute8 = okc_api.g_miss_char
3734 THEN
3735 p_deliverable_rec.attribute8 := NULL;
3736 END IF;
3737
3738 IF p_deliverable_rec.attribute9 = okc_api.g_miss_char
3739 THEN
3740 p_deliverable_rec.attribute9 := NULL;
3741 END IF;
3742
3743 IF p_deliverable_rec.attribute10 = okc_api.g_miss_char
3744 THEN
3745 p_deliverable_rec.attribute10 := NULL;
3746 END IF;
3747
3748 IF p_deliverable_rec.attribute11 = okc_api.g_miss_char
3749 THEN
3750 p_deliverable_rec.attribute11 := NULL;
3751 END IF;
3752
3753 IF p_deliverable_rec.attribute12 = okc_api.g_miss_char
3754 THEN
3755 p_deliverable_rec.attribute12 := NULL;
3756 END IF;
3757
3758 IF p_deliverable_rec.attribute13 = okc_api.g_miss_char
3759 THEN
3760 p_deliverable_rec.attribute13 := NULL;
3761 END IF;
3762
3763 IF p_deliverable_rec.attribute14 = okc_api.g_miss_char
3764 THEN
3765 p_deliverable_rec.attribute14 := NULL;
3766 END IF;
3767
3768 IF p_deliverable_rec.attribute15 = okc_api.g_miss_char
3769 THEN
3770 p_deliverable_rec.attribute15 := NULL;
3771 END IF;
3772
3773 IF p_deliverable_rec.disable_notifications_yn = okc_api.g_miss_char
3774 THEN
3775 p_deliverable_rec.disable_notifications_yn := 'N';
3776 END IF;
3777
3778 IF p_deliverable_rec.last_amendment_date = okc_api.g_miss_date
3779 THEN
3780 p_deliverable_rec.last_amendment_date := NULL;
3781 END IF;
3782
3783 IF p_deliverable_rec.business_document_line_id = okc_api.g_miss_num
3784 THEN
3785 p_deliverable_rec.business_document_line_id := NULL;
3786 END IF;
3787
3788 IF p_deliverable_rec.external_party_site_id = okc_api.g_miss_num
3789 THEN
3790 p_deliverable_rec.external_party_site_id := NULL;
3791 END IF;
3792
3793 IF p_deliverable_rec.start_event_date = okc_api.g_miss_date
3794 THEN
3795 p_deliverable_rec.start_event_date := NULL;
3796 END IF;
3797
3798 IF p_deliverable_rec.end_event_date = okc_api.g_miss_date
3799 THEN
3800 p_deliverable_rec.end_event_date := NULL;
3801 END IF;
3802
3803 IF p_deliverable_rec.summary_amend_operation_code =
3804 okc_api.g_miss_char
3805 THEN
3806 p_deliverable_rec.summary_amend_operation_code := NULL;
3807 END IF;
3808
3809 IF p_deliverable_rec.external_party_role = okc_api.g_miss_char
3810 THEN
3811 p_deliverable_rec.external_party_role := NULL;
3812 END IF;
3813
3814 IF p_deliverable_rec.pay_hold_prior_due_date_yn = okc_api.g_miss_char
3815 THEN
3816 p_deliverable_rec.pay_hold_prior_due_date_yn := NULL;
3817 END IF;
3818
3819 IF p_deliverable_rec.pay_hold_prior_due_date_value =
3820 okc_api.g_miss_num
3821 THEN
3822 p_deliverable_rec.pay_hold_prior_due_date_value := NULL;
3823 END IF;
3824
3825 IF p_deliverable_rec.pay_hold_prior_due_date_uom =
3826 okc_api.g_miss_char
3827 THEN
3828 p_deliverable_rec.pay_hold_prior_due_date_uom := NULL;
3829 END IF;
3830
3831 IF p_deliverable_rec.pay_hold_overdue_yn = okc_api.g_miss_char
3832 THEN
3833 p_deliverable_rec.pay_hold_overdue_yn := NULL;
3834 END IF;
3835
3836 IF p_deliverable_rec.raise_completion_event_yn = okc_api.g_miss_char
3837 THEN
3838 p_deliverable_rec.raise_completion_event_yn := NULL;
3839 END IF;
3840 END default_row;
3841
3842 PROCEDURE validate_row (
3843 p_deliverable_rec IN OUT NOCOPY deliverable_rec_type
3844 )
3845 IS
3846 l_fixedstartdateyn VARCHAR2 (30);
3847 l_starteventcode VARCHAR2 (240);
3848 l_startba VARCHAR2 (240);
3849 l_endeventcode VARCHAR2 (240);
3850 l_endba VARCHAR2 (240);
3851 l_continue VARCHAR2 (1);
3852 l_startduration NUMBER;
3853 l_endduration NUMBER;
3854 l_uom VARCHAR2 (120);
3855 l_proc VARCHAR2 (60) := 'VALIDATE_DELIVERABLE';
3856
3857 --Acq Plan Message Cleanup
3858 l_resolved_msg_name VARCHAR2(30);
3859 l_resolved_token VARCHAR2(100);
3860 BEGIN
3861 -- Validate Header Info
3862
3863 -- Business Document Type must be 'TEMPLATE'
3864 IF p_deliverable_rec.business_document_type <> 'TEMPLATE'
3865 THEN
3866 okc_api.set_message (p_app_name => g_app_name,
3867 p_msg_name => 'OKC_I_INVALID_VALUE',
3868 p_token1 => 'FIELD',
3869 p_token1_value => 'BUSINESS_DOCUMENT_TYPE'
3870 );
3871 RAISE fnd_api.g_exc_error;
3872 END IF;
3873
3874 -- Validate the Deliverable Type
3875 IF p_deliverable_rec.deliverable_type NOT IN
3876 ('CONTRACTUAL', 'INTERNAL_PURCHASING', 'SOURCING')
3877 THEN
3878 okc_api.set_message (p_app_name => g_app_name,
3879 p_msg_name => 'OKC_I_INVALID_VALUE',
3880 p_token1 => 'FIELD',
3881 p_token1_value => 'DELIVERABLE_TYPE'
3882 );
3883 RAISE fnd_api.g_exc_error;
3884 END IF;
3885
3886 -- Validate the responsible_party
3887 IF ( ( p_deliverable_rec.deliverable_type = 'CONTRACTUAL'
3888 AND p_deliverable_rec.responsible_party NOT IN
3889 ('SUPPLIER_ORG', 'BUYER_ORG')
3890 )
3891 OR ( p_deliverable_rec.deliverable_type IN
3892 ('INTERNAL_PURCHASING', 'SOURCING')
3893 AND p_deliverable_rec.responsible_party <> 'BUYER_ORG'
3894 )
3895 )
3896 THEN
3897 okc_api.set_message (p_app_name => g_app_name,
3898 p_msg_name => 'OKC_I_INVALID_VALUE',
3899 p_token1 => 'FIELD',
3900 p_token1_value => 'RESPONSIBLE_PARTY'
3901 );
3902 RAISE fnd_api.g_exc_error;
3903 END IF;
3904
3905 IF p_deliverable_rec.internal_party_contact_id IS NOT NULL
3906 AND isvalidcontact (p_deliverable_rec.internal_party_contact_id) <>
3907 'Y'
3908 THEN
3909 okc_api.set_message (p_app_name => g_app_name,
3910 p_msg_name => 'OKC_I_INVALID_VALUE',
3911 p_token1 => 'FIELD',
3912 p_token1_value => 'RESPONSIBLE_PARTY'
3913 );
3914 RAISE fnd_api.g_exc_error;
3915 END IF;
3916
3917 IF p_deliverable_rec.requester_id IS NOT NULL
3918 AND isvalidcontact (p_deliverable_rec.requester_id) <> 'Y'
3919 THEN
3920 okc_api.set_message (p_app_name => g_app_name,
3921 p_msg_name => 'OKC_I_INVALID_VALUE',
3922 p_token1 => 'FIELD',
3923 p_token1_value => 'REQUESTER_ID'
3924 );
3925 RAISE fnd_api.g_exc_error;
3926 END IF;
3927
3928 IF p_deliverable_rec.deliverable_name IS NULL
3929 THEN
3930 okc_api.set_message (p_app_name => g_app_name,
3931 p_msg_name => 'OKC_I_NOT_NULL',
3932 p_token1 => 'FIELD',
3933 p_token1_value => 'DELIVERABLE_NAME'
3934 );
3935 RAISE fnd_api.g_exc_error;
3936 END IF;
3937
3938 IF p_deliverable_rec.fixed_due_date_yn = 'N'
3939 THEN
3940 IF p_deliverable_rec.relative_st_date_event_id IS NULL
3941 THEN
3942 okc_api.set_message
3943 (p_app_name => g_app_name,
3944 p_msg_name => 'OKC_I_NOT_NULL',
3945 p_token1 => 'FIELD',
3946 p_token1_value => 'RELATIVE_ST_DATE_EVENT_ID'
3947 );
3948 RAISE fnd_api.g_exc_error;
3949 END IF;
3950
3951 IF p_deliverable_rec.relative_st_date_duration IS NULL
3952 THEN
3953 okc_api.set_message
3954 (p_app_name => g_app_name,
3955 p_msg_name => 'OKC_I_NOT_NULL',
3956 p_token1 => 'FIELD',
3957 p_token1_value => 'RELATIVE_ST_DATE_DURATION'
3958 );
3959 RAISE fnd_api.g_exc_error;
3960 END IF;
3961
3962 IF p_deliverable_rec.relative_st_date_uom IS NULL
3963 THEN
3964 okc_api.set_message (p_app_name => g_app_name,
3965 p_msg_name => 'OKC_I_NOT_NULL',
3966 p_token1 => 'FIELD',
3967 p_token1_value => 'RELATIVE_ST_DATE_UOM'
3968 );
3969 RAISE fnd_api.g_exc_error;
3970 END IF;
3971
3972 IF p_deliverable_rec.relative_st_date_event_id IS NOT NULL
3973 THEN
3974 IF isvalidstartbusdocevent
3975 (p_document_type => p_deliverable_rec.business_document_type,
3976 p_deliverable_type => p_deliverable_rec.deliverable_type,
3977 p_bus_doc_event_id => p_deliverable_rec.relative_st_date_event_id
3978 ) <> 'Y'
3979 THEN
3980 okc_api.set_message
3981 (p_app_name => g_app_name,
3982 p_msg_name => 'OKC_I_INVALID_VALUE',
3983 p_token1 => 'FIELD',
3984 p_token1_value => 'RELATIVE_ST_DATE_EVENT_ID'
3985 );
3986 RAISE fnd_api.g_exc_error;
3987 END IF;
3988 END IF;
3989 END IF;
3990
3991 /* Need to add validation for UOM
3992 */
3993 IF p_deliverable_rec.notify_prior_due_date_uom IS NOT NULL
3994 AND p_deliverable_rec.notify_prior_due_date_uom NOT IN
3995 ('MTH', 'DAY', 'WK')
3996 THEN
3997 okc_api.set_message
3998 (p_app_name => g_app_name,
3999 p_msg_name => 'OKC_I_INVALID_VALUE',
4000 p_token1 => 'FIELD',
4001 p_token1_value => 'NOTIFY_PRIOR_DUE_DATE_UOM'
4002 );
4003 END IF;
4004
4005 IF p_deliverable_rec.notify_escalation_uom IS NOT NULL
4006 AND p_deliverable_rec.notify_escalation_uom NOT IN
4007 ('MTH', 'DAY', 'WK')
4008 THEN
4009 okc_api.set_message (p_app_name => g_app_name,
4010 p_msg_name => 'OKC_I_INVALID_VALUE',
4011 p_token1 => 'FIELD',
4012 p_token1_value => 'NOTIFY_ESCALATION_UOM'
4013 );
4014 END IF;
4015
4016 IF p_deliverable_rec.relative_st_date_uom IS NOT NULL
4017 AND p_deliverable_rec.relative_st_date_uom NOT IN
4018 ('MTH', 'DAY', 'WK')
4019 THEN
4020 okc_api.set_message (p_app_name => g_app_name,
4021 p_msg_name => 'OKC_I_INVALID_VALUE',
4022 p_token1 => 'FIELD',
4023 p_token1_value => 'RELATIVE_ST_DATE_UOM'
4024 );
4025 END IF;
4026
4027 IF p_deliverable_rec.relative_end_date_uom IS NOT NULL
4028 AND p_deliverable_rec.relative_end_date_uom NOT IN
4029 ('MTH', 'DAY', 'WK')
4030 THEN
4031 okc_api.set_message (p_app_name => g_app_name,
4032 p_msg_name => 'OKC_I_INVALID_VALUE',
4033 p_token1 => 'FIELD',
4034 p_token1_value => 'RELATIVE_END_DATE_UOM'
4035 );
4036 END IF;
4037
4038 IF p_deliverable_rec.repeating_frequency_uom IS NOT NULL
4039 AND isvalidlookup ('OKC_DEL_REPEAT_FREQ',
4040 p_deliverable_rec.repeating_frequency_uom
4041 ) <> 'Y'
4042 THEN
4043 okc_api.set_message (p_app_name => g_app_name,
4044 p_msg_name => 'OKC_I_INVALID_VALUE',
4045 p_token1 => 'FIELD',
4046 p_token1_value => 'REPEATING_FREQUENCY_UOM'
4047 );
4048 END IF;
4049
4050 IF p_deliverable_rec.pay_hold_prior_due_date_uom IS NOT NULL
4051 AND p_deliverable_rec.pay_hold_prior_due_date_uom NOT IN
4052 ('MTH', 'DAY', 'WK')
4053 THEN
4054 okc_api.set_message
4055 (p_app_name => g_app_name,
4056 p_msg_name => 'OKC_I_INVALID_VALUE',
4057 p_token1 => 'FIELD',
4058 p_token1_value => 'PAY_HOLD_PRIOR_DUE_DATE_UOM'
4059 );
4060 END IF;
4061
4062 ----------------------------------
4063 /* if user specified repeat until information
4064 //1. user selects fixed start date radio and enters relative start date duration
4065 //2. user selects relative start date radio and enters fixed start date
4066 //3. user selects fixed end date radio and enters relative end date duration
4067 //4. user selects relative end date radio and enters fixed end date
4068 */
4069 IF ( p_deliverable_rec.recurring_yn = 'N'
4070 AND ( p_deliverable_rec.relative_end_date_duration IS NOT NULL
4071 OR p_deliverable_rec.fixed_end_date IS NOT NULL
4072 OR p_deliverable_rec.repeating_duration IS NOT NULL
4073 )
4074 )
4075 OR ( p_deliverable_rec.relative_st_date_event_id IS NULL
4076 AND p_deliverable_rec.relative_st_date_duration IS NOT NULL
4077 )
4078 OR ( p_deliverable_rec.relative_st_date_event_id IS NOT NULL
4079 AND p_deliverable_rec.fixed_start_date IS NOT NULL
4080 )
4081 OR ( p_deliverable_rec.relative_end_date_event_id IS NULL
4082 AND p_deliverable_rec.relative_end_date_duration IS NOT NULL
4083 )
4084 OR ( p_deliverable_rec.relative_end_date_event_id IS NOT NULL
4085 AND p_deliverable_rec.fixed_end_date IS NOT NULL
4086 )
4087 THEN
4088 okc_api.set_message
4089 (p_app_name => g_app_name,
4090 p_msg_name => 'OKC_DEL_INCONSISTENT_DUE_DATES'
4091 );
4092 RAISE fnd_api.g_exc_error;
4093 END IF;
4094
4095 -- it is ok to save a deliverable without due date
4096 -- but if the user enters some due date information, we need to make sure that it is complete
4097 ----------------------------------
4098 /* IF p_deliverable_rec.recurring_yn = 'Y'
4099 THEN
4100 IF p_deliverable_rec.fixed_due_date_yn = 'N'
4101 AND p_deliverable_rec.fixed_start_date IS NULL
4102 THEN
4103 okc_api.set_message
4104 (p_app_name => g_app_name,
4105 p_msg_name => 'OKC_DEL_NULL_FIXED_ST_DATE_UI'
4106 );
4107 RAISE fnd_api.g_exc_error;
4108 END IF;
4109 END IF; */
4110
4111 ----------------------------------
4112 IF p_deliverable_rec.fixed_due_date_yn = 'N'
4113 THEN
4114 IF p_deliverable_rec.relative_st_date_duration IS NULL
4115 THEN
4116
4117 --Acq Plan Message Cleanup
4118 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_REL_ST_DUR_UI',p_deliverable_rec.business_document_type);
4119 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4120
4121 /* okc_api.set_message
4122 (p_app_name => g_app_name,
4123 p_msg_name => 'OKC_DEL_NULL_REL_ST_DUR_UI'
4124 ); */
4125
4126 okc_api.set_message
4127 (p_app_name => g_app_name,
4128 p_msg_name => l_resolved_msg_name,
4129 p_token1 => 'DEL_TOKEN',
4130 p_token1_value => l_resolved_token
4131 );
4132 RAISE fnd_api.g_exc_error;
4133 END IF;
4134
4135 IF p_deliverable_rec.relative_st_date_duration < 0
4136 THEN
4137 --Acq Plan Message Cleanup
4138 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NEG_REL_ST_DUR_UI',p_deliverable_rec.business_document_type);
4139 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4140
4141 okc_api.set_message (p_app_name => g_app_name,
4142 p_msg_name => l_resolved_msg_name,
4143 p_token1 => 'DEL_TOKEN',
4144 p_token1_value => l_resolved_token
4145 );
4146 RAISE fnd_api.g_exc_error;
4147 END IF;
4148
4149 IF p_deliverable_rec.relative_st_date_event_id IS NULL
4150 THEN
4151 --Acq Plan Message Cleanup
4152 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_REL_ST_EVENT_UI',p_deliverable_rec.business_document_type);
4153 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4154
4155 okc_api.set_message (p_app_name => g_app_name,
4156 p_msg_name => l_resolved_msg_name,
4157 p_token1 => 'DEL_TOKEN',
4158 p_token1_value => l_resolved_token
4159 );
4160
4161 RAISE fnd_api.g_exc_error;
4162 END IF;
4163
4164 IF p_deliverable_rec.relative_st_date_event_id IS NOT NULL
4165 THEN
4166 IF isvalidstartbusdocevent
4167 (p_document_type => p_deliverable_rec.business_document_type,
4168 p_deliverable_type => p_deliverable_rec.deliverable_type,
4169 p_bus_doc_event_id => p_deliverable_rec.relative_st_date_event_id
4170 ) <> 'Y'
4171 THEN
4172 okc_api.set_message
4173 (p_app_name => g_app_name,
4174 p_msg_name => 'OKC_I_INVALID_VALUE',
4175 p_token1 => 'FIELD',
4176 p_token1_value => 'RELATIVE_ST_DATE_EVENT_ID'
4177 );
4178 RAISE fnd_api.g_exc_error;
4179 END IF;
4180 END IF;
4181 END IF;
4182
4183 ----------------------------------
4184 IF p_deliverable_rec.recurring_yn = 'Y'
4185 AND p_deliverable_rec.repeating_frequency_uom = 'WK'
4186 THEN
4187 IF p_deliverable_rec.repeating_duration IS NULL
4188 THEN
4189 okc_api.set_message
4190 (p_app_name => g_app_name,
4191 p_msg_name => 'OKC_DEL_NULL_REPEAT_WEEK_UI'
4192 );
4193 RAISE fnd_api.g_exc_error;
4194 END IF;
4195
4196 IF p_deliverable_rec.repeating_duration < 0
4197 THEN
4198 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NEG_REPEAT_WEEK_UI',p_deliverable_rec.business_document_type);
4199 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4200
4201 okc_api.set_message (p_app_name => g_app_name,
4202 p_msg_name => l_resolved_msg_name,
4203 p_token1 => 'DEL_TOKEN',
4204 p_token1_value => l_resolved_token
4205 );
4206
4207 RAISE fnd_api.g_exc_error;
4208 END IF;
4209
4210 IF isvalidlookup
4211 (p_lookup_type => 'DAY_OF_WEEK',
4212 p_lookup_code => TO_CHAR
4213 (p_deliverable_rec.repeating_day_of_week
4214 )
4215 ) <> 'Y'
4216 THEN
4217 okc_api.set_message
4218 (p_app_name => g_app_name,
4219 p_msg_name => 'OKC_DEL_INVALID_DAY_OF_WEEK'
4220 );
4221 RAISE fnd_api.g_exc_error;
4222 END IF;
4223 END IF;
4224
4225 IF p_deliverable_rec.recurring_yn = 'Y'
4226 AND p_deliverable_rec.repeating_frequency_uom = 'MTH'
4227 THEN
4228 IF p_deliverable_rec.repeating_duration IS NULL
4229 THEN
4230 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_REPEAT_MONTH_UI',p_deliverable_rec.business_document_type);
4231 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4232
4233 okc_api.set_message (p_app_name => g_app_name,
4234 p_msg_name => l_resolved_msg_name,
4235 p_token1 => 'DEL_TOKEN',
4236 p_token1_value => l_resolved_token
4237 );
4238
4239 RAISE fnd_api.g_exc_error;
4240 END IF;
4241
4242 IF p_deliverable_rec.repeating_duration < 0
4243 THEN
4244 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NEG_REPEAT_MONTH_UI',p_deliverable_rec.business_document_type);
4245 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4246
4247 okc_api.set_message (p_app_name => g_app_name,
4248 p_msg_name => l_resolved_msg_name,
4249 p_token1 => 'DEL_TOKEN',
4250 p_token1_value => l_resolved_token
4251 );
4252 RAISE fnd_api.g_exc_error;
4253 END IF;
4254
4255 IF isvalidlookup
4256 (p_lookup_type => 'OKC_DAY_OF_MONTH',
4257 p_lookup_code => TO_CHAR
4258 (p_deliverable_rec.repeating_day_of_month
4259 )
4260 ) <> 'Y'
4261 THEN
4262 okc_api.set_message
4263 (p_app_name => g_app_name,
4264 p_msg_name => 'OKC_DEL_INVALID_DAY_OF_MONTH'
4265 );
4266 RAISE fnd_api.g_exc_error;
4267 END IF;
4268 END IF;
4269
4270 /*
4271 //check on a template, if the deliverable has relative start and end event
4272 //the two events should belong to the same business document type
4273
4274 */
4275 IF p_deliverable_rec.recurring_yn = 'Y'
4276 AND p_deliverable_rec.relative_st_date_event_id IS NOT NULL
4277 AND p_deliverable_rec.relative_end_date_event_id IS NOT NULL
4278 THEN
4279 IF isvalidstendeventsmatch
4280 (p_deliverable_rec.relative_st_date_event_id,
4281 p_deliverable_rec.relative_end_date_event_id
4282 ) <> 'Y'
4283 THEN
4284 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_INVLD_EVENT_DOCTYPE_UI',p_deliverable_rec.business_document_type);
4285 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4286 /* okc_api.set_message
4287 (p_app_name => g_app_name,
4288 p_msg_name => 'OKC_DEL_INVLD_EVENT_DOCTYPE_UI'
4289 );*/
4290 okc_api.set_message (p_app_name => g_app_name,
4291 p_msg_name => l_resolved_msg_name,
4292 p_token1 => 'DEL_TOKEN',
4293 p_token1_value => l_resolved_token
4294 );
4295
4296 RAISE fnd_api.g_exc_error;
4297 END IF;
4298 END IF;
4299
4300 IF ( p_deliverable_rec.recurring_yn = 'Y'
4301 AND p_deliverable_rec.relative_end_date_event_id IS NULL
4302 )
4303 AND p_deliverable_rec.fixed_end_date IS NULL
4304 THEN
4305
4306 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_FIXD_END_DATE_UI',p_deliverable_rec.business_document_type);
4307 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4308 okc_api.set_message (p_app_name => g_app_name,
4309 p_msg_name => l_resolved_msg_name,
4310 p_token1 => 'DEL_TOKEN',
4311 p_token1_value => l_resolved_token
4312 );
4313
4314 RAISE fnd_api.g_exc_error;
4315 END IF;
4316
4317 IF ( p_deliverable_rec.recurring_yn = 'Y'
4318 AND p_deliverable_rec.relative_end_date_event_id IS NULL
4319 )
4320 AND p_deliverable_rec.fixed_end_date IS NOT NULL
4321 THEN
4322 IF p_deliverable_rec.fixed_end_date < SYSDATE
4323 THEN
4324 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_WRONG_FIXD_END_DATE_UI',p_deliverable_rec.business_document_type);
4325 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4326
4327 okc_api.set_message (p_app_name => g_app_name,
4328 p_msg_name => l_resolved_msg_name,
4329 p_token1 => 'DEL_TOKEN',
4330 p_token1_value => l_resolved_token
4331 );
4332
4333 RAISE fnd_api.g_exc_error;
4334 END IF;
4335 END IF;
4336
4337 IF p_deliverable_rec.recurring_yn = 'Y'
4338 THEN
4339 IF p_deliverable_rec.relative_end_date_duration IS NULL
4340 THEN
4341 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_REL_END_DUR_UI',p_deliverable_rec.business_document_type);
4342 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4343
4344 /* okc_api.set_message
4345 (p_app_name => g_app_name,
4346 p_msg_name => 'OKC_DEL_NULL_REL_END_DUR_UI'
4347 );*/
4348 okc_api.set_message (p_app_name => g_app_name,
4349 p_msg_name => l_resolved_msg_name,
4350 p_token1 => 'DEL_TOKEN',
4351 p_token1_value => l_resolved_token
4352 );
4353
4354
4355 RAISE fnd_api.g_exc_error;
4356 END IF;
4357
4358 IF p_deliverable_rec.relative_end_date_duration < 0
4359 THEN
4360 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NEG_REL_END_DUR_UI',p_deliverable_rec.business_document_type);
4361 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4362
4363
4364 /*okc_api.set_message
4365 (p_app_name => g_app_name,
4366 p_msg_name => 'OKC_DEL_NEG_REL_END_DUR_UI'
4367 ); */
4368
4369 okc_api.set_message (p_app_name => g_app_name,
4370 p_msg_name => l_resolved_msg_name,
4371 p_token1 => 'DEL_TOKEN',
4372 p_token1_value => l_resolved_token
4373 );
4374
4375 RAISE fnd_api.g_exc_error;
4376 END IF;
4377
4378 IF p_deliverable_rec.relative_end_date_event_id IS NULL
4379 THEN
4380 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_NULL_REL_END_EVENT_UI',p_deliverable_rec.business_document_type);
4381 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4382
4383 /* okc_api.set_message
4384 (p_app_name => g_app_name,
4385 p_msg_name => 'OKC_DEL_NULL_REL_END_EVENT_UI'
4386 );*/
4387
4388 okc_api.set_message (p_app_name => g_app_name,
4389 p_msg_name => l_resolved_msg_name,
4390 p_token1 => 'DEL_TOKEN',
4391 p_token1_value => l_resolved_token
4392 );
4393
4394 RAISE fnd_api.g_exc_error;
4395 END IF;
4396
4397 IF p_deliverable_rec.relative_end_date_event_id IS NOT NULL
4398 THEN
4399 IF isvalidendbusdocevent
4400 (p_document_type => p_deliverable_rec.business_document_type,
4401 p_deliverable_type => p_deliverable_rec.deliverable_type,
4402 p_bus_doc_event_id => p_deliverable_rec.relative_end_date_event_id
4403 ) <> 'Y'
4404 THEN
4405 okc_api.set_message
4406 (p_app_name => g_app_name,
4407 p_msg_name => 'OKC_I_INVALID_VALUE',
4408 p_token1 => 'FIELD',
4409 p_token1_value => 'RELATIVE_END_DATE_EVENT_ID'
4410 );
4411 RAISE fnd_api.g_exc_error;
4412 END IF;
4413 END IF;
4414 END IF;
4415
4416 IF p_deliverable_rec.recurring_yn = 'Y'
4417 AND p_deliverable_rec.relative_end_date_event_id IS NULL
4418 AND p_deliverable_rec.relative_st_date_event_id IS NULL
4419 THEN
4420 IF p_deliverable_rec.fixed_start_date >
4421 p_deliverable_rec.fixed_end_date
4422 THEN
4423 --Acq Plan Message Cleanup
4424 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_END_BEFORE_START_UI',p_deliverable_rec.business_document_type);
4425
4426 /* okc_api.set_message
4427 (p_app_name => g_app_name,
4428 p_msg_name => 'OKC_I_INVALID_VALUE',
4429 p_token1 => 'FIELD',
4430 p_token1_value => 'OKC_DEL_END_BEFORE_START_UI'
4431 );*/
4432 okc_api.set_message
4433 (p_app_name => g_app_name,
4434 p_msg_name => 'OKC_I_INVALID_VALUE',
4435 p_token1 => 'FIELD',
4436 p_token1_value => l_resolved_msg_name
4437 );
4438 RAISE fnd_api.g_exc_error;
4439 END IF;
4440 END IF;
4441
4442 /*
4443 //if deliverable is recurring and the start event and the end event are the same
4444 //we should do the following checks:
4445 //(1) If both the dates are before the event then
4446 //Validation: Start duration > End duration
4447 //(e.g. 10 days should be greater than 1 week)
4448 //
4449 //(2) If Start date is before the event and End date is after the event then
4450 //Validation: No problem
4451 //
4452 //(3) If Start date is after the event and End date is before the event then
4453 //Validation: Error Start date should be before the End date
4454 //
4455 //(4) If both the dates are after the event then
4456 //Validation: Start duration < End duration
4457 */
4458 IF p_deliverable_rec.recurring_yn = 'Y'
4459 AND p_deliverable_rec.relative_st_date_event_id IS NOT NULL
4460 AND p_deliverable_rec.relative_end_date_event_id IS NOT NULL
4461 AND p_deliverable_rec.relative_st_date_duration IS NOT NULL
4462 AND p_deliverable_rec.relative_end_date_duration IS NOT NULL
4463 AND p_deliverable_rec.relative_st_date_uom IS NOT NULL
4464 AND p_deliverable_rec.relative_end_date_uom IS NOT NULL
4465 THEN
4466 BEGIN
4467 SELECT business_event_code, before_after, 'Y'
4468 INTO l_starteventcode, l_startba, l_continue
4469 FROM okc_bus_doc_events_b
4470 WHERE bus_doc_event_id =
4471 p_deliverable_rec.relative_st_date_event_id;
4472 EXCEPTION
4473 WHEN NO_DATA_FOUND
4474 THEN
4475 l_continue := 'N';
4476 END;
4477
4478 IF l_continue = 'Y'
4479 THEN
4480 BEGIN
4481 SELECT business_event_code, before_after, 'Y'
4482 INTO l_endeventcode, l_endba, l_continue
4483 FROM okc_bus_doc_events_b
4484 WHERE bus_doc_event_id =
4485 p_deliverable_rec.relative_end_date_event_id;
4486 EXCEPTION
4487 WHEN NO_DATA_FOUND
4488 THEN
4489 l_continue := 'N';
4490 END;
4491
4492 IF l_continue = 'Y'
4493 THEN
4494 IF l_starteventcode IS NOT NULL
4495 AND l_starteventcode = l_endeventcode
4496 THEN
4497 /*
4498 //if the getDays method cannot find a match, it will return -1
4499 //so if startDuration or endDuration is less than 0, we know that we didn't find a match
4500 //in this case we won't compare, because we can't
4501 */
4502 l_uom := p_deliverable_rec.relative_st_date_uom;
4503 l_startduration :=
4504 TO_NUMBER
4505 (p_deliverable_rec.relative_st_date_duration)
4506 * (CASE l_uom
4507 WHEN 'DAY'
4508 THEN 1
4509 WHEN 'WK'
4510 THEN 7
4511 WHEN 'MTH'
4512 THEN 30
4513 ELSE -1
4514 END
4515 );
4516 l_endduration :=
4517 TO_NUMBER
4518 (p_deliverable_rec.relative_end_date_duration)
4519 * (CASE p_deliverable_rec.relative_end_date_uom
4520 WHEN 'DAY'
4521 THEN 1
4522 WHEN 'WK'
4523 THEN 7
4524 WHEN 'MTH'
4525 THEN 30
4526 ELSE -1
4527 END
4528 );
4529
4530 IF l_startduration >= 0 AND l_endduration >= 0
4531 THEN
4532 -- Scenario 1
4533 IF ( 'B' = l_startba
4534 AND 'B' = 'l_endBA'
4535 AND (l_startduration < l_endduration)
4536 )
4537 THEN
4538 --Acq Plan Message Cleanup
4539 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_END_BEFORE_START_UI',p_deliverable_rec.business_document_type);
4540 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4541
4542 /* okc_api.set_message
4543 (p_app_name => g_app_name,
4544 p_msg_name => 'OKC_DEL_END_BEFORE_START_UI'
4545 );*/
4546 okc_api.set_message
4547 (p_app_name => g_app_name,
4548 p_msg_name => l_resolved_msg_name,
4549 p_token1 => 'DEL_TOKEN',
4550 p_token1_value => l_resolved_token
4551 );
4552 RAISE fnd_api.g_exc_error;
4553 END IF;
4554
4555 -- Scenario 2 is always valid no need to check
4556
4557 -- Scenario 3
4558 IF ('A' = l_startba AND 'B' = l_endba)
4559 THEN
4560 --Acq Plan Message Cleanup
4561 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_END_BEFORE_START_UI',p_deliverable_rec.business_document_type);
4562 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4563
4564 /* okc_api.set_message
4565 (p_app_name => g_app_name,
4566 p_msg_name => 'OKC_DEL_END_BEFORE_START_UI'
4567 );*/
4568 okc_api.set_message
4569 (p_app_name => g_app_name,
4570 p_msg_name => l_resolved_msg_name,
4571 p_token1 => 'DEL_TOKEN',
4572 p_token1_value => l_resolved_token
4573 );
4574
4575 RAISE fnd_api.g_exc_error;
4576 END IF;
4577
4578 IF ( 'A' = l_startba
4579 AND 'A' = l_endba
4580 AND l_startduration > l_endduration
4581 )
4582 THEN
4583 --Acq Plan Message Cleanup
4584 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_END_BEFORE_START_UI',p_deliverable_rec.business_document_type);
4585 l_resolved_token := OKC_API.resolve_del_token(p_deliverable_rec.business_document_type);
4586
4587 /* okc_api.set_message
4588 (p_app_name => g_app_name,
4589 p_msg_name => 'OKC_DEL_END_BEFORE_START_UI'
4590 );*/
4591 okc_api.set_message
4592 (p_app_name => g_app_name,
4593 p_msg_name => l_resolved_msg_name,
4594 p_token1 => 'DEL_TOKEN',
4595 p_token1_value => l_resolved_token
4596 );
4597
4598 RAISE fnd_api.g_exc_error;
4599 END IF;
4600 END IF;
4601 END IF;
4602 END IF;
4603 END IF;
4604 END IF;
4605
4606 IF p_deliverable_rec.escalation_assignee IS NOT NULL
4607 THEN
4608 IF isvalidcontact (p_deliverable_rec.escalation_assignee) <> 'Y'
4609 THEN
4610 okc_api.set_message (p_app_name => g_app_name,
4611 p_msg_name => 'OKC_I_INVALID_VALUE',
4612 p_token1 => 'FIELD',
4613 p_token1_value => 'ESCALATION_ASSIGNEE'
4614 );
4615 RAISE fnd_api.g_exc_error;
4616 END IF;
4617 END IF;
4618 EXCEPTION
4619 WHEN OTHERS
4620 THEN
4621 set_proc_error_message (p_proc => l_proc);
4622 RAISE;
4623 END validate_row;
4624 BEGIN
4625 SAVEPOINT create_deliverable_sp;
4626 fnd_msg_pub.initialize;
4627
4628 IF p_deliverable_rec.business_document_id <> NVL (g_template_id, -1)
4629 THEN
4630 IF inittemplateinfo
4631 (p_template_id => p_deliverable_rec.business_document_id) <>
4632 'Y'
4633 THEN
4634 okc_api.set_message (p_app_name => g_app_name,
4635 p_msg_name => 'OKC_I_INVALID_VALUE',
4636 p_token1 => 'FIELD',
4637 p_token1_value => 'TEMPLATE_ID'
4638 );
4639 RAISE fnd_api.g_exc_error;
4640 END IF;
4641 END IF;
4642
4643 IF g_template_intent = 'S'
4644 THEN
4645 okc_api.set_message (p_app_name => g_app_name,
4646 p_msg_name => 'OKC_I_INVALID_VALUE',
4647 p_token1 => 'FIELD',
4648 p_token1_value => 'TEMPLATE_INTENT'
4649 );
4650 RAISE fnd_api.g_exc_error;
4651 END IF;
4652
4653 IF g_deliverable_update_allowed <> 'Y'
4654 THEN
4655 okc_api.set_message (p_app_name => g_app_name,
4656 p_msg_name => 'OKC_I_TEMP_STS_NO_INS_OBJ',
4657 p_token1 => 'STATUS',
4658 p_token1_value => g_template_status_code,
4659 p_token2 => 'OBJECT',
4660 p_token2_value => 'DELIVERABLE'
4661 );
4662 RAISE fnd_api.g_exc_error;
4663 END IF;
4664
4665 default_row (p_deliverable_rec => p_deliverable_rec);
4666
4667 BEGIN
4668 fnd_msg_pub.initialize;
4669 validate_row (p_deliverable_rec => p_deliverable_rec);
4670 EXCEPTION
4671 WHEN OTHERS
4672 THEN
4673 RAISE;
4674 END;
4675
4676 IF p_deliverable_rec.recurring_yn IS NOT NULL
4677 AND p_deliverable_rec.repeating_frequency_uom IS NOT NULL
4678 AND p_deliverable_rec.relative_st_date_uom IS NOT NULL
4679 AND p_deliverable_rec.relative_end_date_uom IS NOT NULL
4680 THEN
4681 IF p_deliverable_rec.relative_st_date_event_id IS NOT NULL
4682 THEN
4683 l_start_date_fixed := 'N';
4684 get_event_details (p_deliverable_rec.relative_st_date_event_id,
4685 l_start_evt_before_after
4686 );
4687 ELSE
4688 l_start_date_fixed := 'Y';
4689 END IF;
4690
4691 IF p_deliverable_rec.relative_end_date_event_id IS NOT NULL
4692 THEN
4693 l_end_date_fixed := 'N';
4694 get_event_details (p_deliverable_rec.relative_end_date_event_id,
4695 l_end_evt_before_after
4696 );
4697 ELSE
4698 l_end_date_fixed := 'Y';
4699 END IF;
4700
4701 l_repeating_frequency_uom :=
4702 getuomvalue (p_duration => p_deliverable_rec.repeating_duration,
4703 p_uom => p_deliverable_rec.repeating_frequency_uom
4704 );
4705 l_relative_st_date_uom :=
4706 getuomvalue
4707 (p_duration => p_deliverable_rec.relative_st_date_duration,
4708 p_uom => p_deliverable_rec.relative_st_date_uom
4709 );
4710 l_relative_end_date_uom :=
4711 getuomvalue
4712 (p_duration => p_deliverable_rec.relative_end_date_duration,
4713 p_uom => p_deliverable_rec.relative_end_date_uom
4714 );
4715 p_deliverable_rec.print_due_date_msg_name :=
4716 getprintduedatemsgname
4717 (p_recurring_flag => p_deliverable_rec.recurring_yn,
4718 p_start_fixed_flag => l_start_date_fixed,
4719 p_end_fixed_flag => l_end_date_fixed,
4720 p_repeating_frequency_uom => l_repeating_frequency_uom,
4721 p_relative_st_date_uom => l_relative_st_date_uom,
4722 p_relative_end_date_uom => l_relative_end_date_uom,
4723 p_start_evt_before_after => l_start_evt_before_after,
4724 p_end_evt_before_after => l_end_evt_before_after
4725 );
4726 END IF;
4727
4728 INSERT INTO okc_deliverables
4729 (deliverable_id,
4730 business_document_type,
4731 business_document_id,
4732 business_document_number,
4733 deliverable_type,
4734 responsible_party,
4735 internal_party_contact_id,
4736 external_party_contact_id,
4737 deliverable_name,
4738 description, comments,
4739 display_sequence,
4740 fixed_due_date_yn,
4741 actual_due_date,
4742 print_due_date_msg_name,
4743 recurring_yn,
4744 notify_prior_due_date_value,
4745 notify_prior_due_date_uom,
4746 notify_prior_due_date_yn,
4747 notify_completed_yn,
4748 notify_overdue_yn,
4749 notify_escalation_yn,
4750 notify_escalation_value,
4751 notify_escalation_uom,
4752 escalation_assignee,
4753 amendment_operation,
4754 prior_notification_id,
4755 amendment_notes,
4756 completed_notification_id,
4757 overdue_notification_id,
4758 escalation_notification_id,
4759 LANGUAGE,
4760 original_deliverable_id,
4761 requester_id,
4762 external_party_id,
4763 recurring_del_parent_id,
4764 business_document_version,
4765 relative_st_date_duration,
4766 relative_st_date_uom,
4767 relative_st_date_event_id,
4768 relative_end_date_duration,
4769 relative_end_date_uom,
4770 relative_end_date_event_id,
4771 repeating_day_of_month,
4772 repeating_day_of_week,
4773 repeating_frequency_uom,
4774 repeating_duration,
4775 fixed_start_date,
4776 fixed_end_date,
4777 manage_yn,
4778 internal_party_id,
4779 deliverable_status,
4780 status_change_notes,
4781 created_by,
4782 creation_date,
4783 last_updated_by,
4784 last_update_date,
4785 last_update_login,
4786 object_version_number,
4787 attribute_category,
4788 attribute1,
4789 attribute2,
4790 attribute3,
4791 attribute4,
4792 attribute5,
4793 attribute6,
4794 attribute7,
4795 attribute8,
4796 attribute9,
4797 attribute10,
4798 attribute11,
4799 attribute12,
4800 attribute13,
4801 attribute14,
4802 attribute15,
4803 disable_notifications_yn,
4804 last_amendment_date,
4805 business_document_line_id,
4806 external_party_site_id,
4807 start_event_date,
4808 end_event_date,
4809 summary_amend_operation_code,
4810 external_party_role,
4811 pay_hold_prior_due_date_yn,
4812 pay_hold_prior_due_date_value,
4813 pay_hold_prior_due_date_uom,
4814 pay_hold_overdue_yn,
4815 raise_completion_event_yn
4816 )
4817 VALUES (p_deliverable_rec.deliverable_id,
4818 p_deliverable_rec.business_document_type,
4819 p_deliverable_rec.business_document_id,
4820 TO_CHAR (p_deliverable_rec.business_document_id),
4821 -- business_document_number,
4822 p_deliverable_rec.deliverable_type,
4823 p_deliverable_rec.responsible_party,
4824 p_deliverable_rec.internal_party_contact_id,
4825 p_deliverable_rec.external_party_contact_id,
4826 p_deliverable_rec.deliverable_name,
4827 p_deliverable_rec.description, p_deliverable_rec.comments,
4828 p_deliverable_rec.display_sequence,
4829 p_deliverable_rec.fixed_due_date_yn,
4830 p_deliverable_rec.actual_due_date,
4831 p_deliverable_rec.print_due_date_msg_name,
4832 p_deliverable_rec.recurring_yn,
4833 p_deliverable_rec.notify_prior_due_date_value,
4834 p_deliverable_rec.notify_prior_due_date_uom,
4835 p_deliverable_rec.notify_prior_due_date_yn,
4836 p_deliverable_rec.notify_completed_yn,
4837 p_deliverable_rec.notify_overdue_yn,
4838 p_deliverable_rec.notify_escalation_yn,
4839 p_deliverable_rec.notify_escalation_value,
4840 p_deliverable_rec.notify_escalation_uom,
4841 p_deliverable_rec.escalation_assignee,
4842 p_deliverable_rec.amendment_operation,
4843 p_deliverable_rec.prior_notification_id,
4844 p_deliverable_rec.amendment_notes,
4845 p_deliverable_rec.completed_notification_id,
4846 p_deliverable_rec.overdue_notification_id,
4847 p_deliverable_rec.escalation_notification_id,
4848 p_deliverable_rec.LANGUAGE,
4849 p_deliverable_rec.original_deliverable_id,
4850 p_deliverable_rec.requester_id,
4851 p_deliverable_rec.external_party_id,
4852 p_deliverable_rec.recurring_del_parent_id,
4853 p_deliverable_rec.business_document_version,
4854 p_deliverable_rec.relative_st_date_duration,
4855 p_deliverable_rec.relative_st_date_uom,
4856 p_deliverable_rec.relative_st_date_event_id,
4857 p_deliverable_rec.relative_end_date_duration,
4858 p_deliverable_rec.relative_end_date_uom,
4859 p_deliverable_rec.relative_end_date_event_id,
4860 p_deliverable_rec.repeating_day_of_month,
4861 p_deliverable_rec.repeating_day_of_week,
4862 p_deliverable_rec.repeating_frequency_uom,
4863 p_deliverable_rec.repeating_duration,
4864 p_deliverable_rec.fixed_start_date,
4865 p_deliverable_rec.fixed_end_date,
4866 p_deliverable_rec.manage_yn,
4867 p_deliverable_rec.internal_party_id,
4868 p_deliverable_rec.deliverable_status,
4869 p_deliverable_rec.status_change_notes,
4870 p_deliverable_rec.created_by,
4871 p_deliverable_rec.creation_date,
4872 p_deliverable_rec.last_updated_by,
4873 p_deliverable_rec.last_update_date,
4874 p_deliverable_rec.last_update_login,
4875 p_deliverable_rec.object_version_number,
4876 p_deliverable_rec.attribute_category,
4877 p_deliverable_rec.attribute1,
4878 p_deliverable_rec.attribute2,
4879 p_deliverable_rec.attribute3,
4880 p_deliverable_rec.attribute4,
4881 p_deliverable_rec.attribute5,
4882 p_deliverable_rec.attribute6,
4883 p_deliverable_rec.attribute7,
4884 p_deliverable_rec.attribute8,
4885 p_deliverable_rec.attribute9,
4886 p_deliverable_rec.attribute10,
4887 p_deliverable_rec.attribute11,
4888 p_deliverable_rec.attribute12,
4889 p_deliverable_rec.attribute13,
4890 p_deliverable_rec.attribute14,
4891 p_deliverable_rec.attribute15,
4892 p_deliverable_rec.disable_notifications_yn,
4893 p_deliverable_rec.last_amendment_date,
4894 p_deliverable_rec.business_document_line_id,
4895 p_deliverable_rec.external_party_site_id,
4896 p_deliverable_rec.start_event_date,
4897 p_deliverable_rec.end_event_date,
4898 p_deliverable_rec.summary_amend_operation_code,
4899 p_deliverable_rec.external_party_role,
4900 p_deliverable_rec.pay_hold_prior_due_date_yn,
4901 p_deliverable_rec.pay_hold_prior_due_date_value,
4902 p_deliverable_rec.pay_hold_prior_due_date_uom,
4903 p_deliverable_rec.pay_hold_overdue_yn,
4904 p_deliverable_rec.raise_completion_event_yn
4905 );
4906
4907 p_deliverable_rec.status := g_ret_sts_success;
4908
4909 IF fnd_api.to_boolean (p_commit)
4910 THEN
4911 COMMIT;
4912 END IF;
4913 EXCEPTION
4914 WHEN fnd_api.g_exc_error
4915 THEN
4916 p_deliverable_rec.status := g_ret_sts_error;
4917 set_proc_error_message (p_proc => l_proc);
4918 read_message (l_error_message);
4919 p_deliverable_rec.errmsg := l_error_message;
4920 ROLLBACK TO create_deliverable_sp;
4921 fnd_msg_pub.initialize;
4922 WHEN OTHERS
4923 THEN
4924 p_deliverable_rec.status := g_ret_sts_error;
4925 set_proc_error_message (p_proc => l_proc);
4926 okc_api.set_message (p_app_name => g_app_name,
4927 p_msg_name => g_unexpected_error,
4928 p_token1 => g_sqlcode_token,
4929 p_token1_value => SQLCODE,
4930 p_token2 => g_sqlerrm_token,
4931 p_token2_value => SQLERRM
4932 );
4933 read_message (l_error_message);
4934 p_deliverable_rec.errmsg := l_error_message;
4935 ROLLBACK TO create_deliverable_sp;
4936 fnd_msg_pub.initialize;
4937 END create_deliverable;
4938
4939 PROCEDURE create_deliverable (
4940 p_deliverable_tbl IN OUT NOCOPY deliverable_tbl_type,
4941 p_commit IN VARCHAR2 := fnd_api.g_false
4942 )
4943 IS
4944 BEGIN
4945 IF p_deliverable_tbl.COUNT > 0
4946 THEN
4947 FOR i IN p_deliverable_tbl.FIRST .. p_deliverable_tbl.LAST
4948 LOOP
4949 BEGIN
4950 create_deliverable (p_deliverable_rec => p_deliverable_tbl
4951 (i));
4952 EXCEPTION
4953 WHEN OTHERS
4954 THEN
4955 p_deliverable_tbl (i).status := g_ret_sts_error;
4956 p_deliverable_tbl (i).errmsg := SQLERRM;
4957 END;
4958 END LOOP;
4959 END IF;
4960 EXCEPTION
4961 WHEN OTHERS
4962 THEN
4963 RAISE;
4964 END create_deliverable;
4965
4966 PROCEDURE delete_deliverables (
4967 p_template_id IN NUMBER,
4968 p_deliverable_id_tbl IN deliverable_id_tbl_type,
4969 p_commit IN VARCHAR2 := fnd_api.g_false,
4970 x_deliverable_id_tbl OUT NOCOPY deliverable_id_tbl_type,
4971 x_return_status OUT NOCOPY VARCHAR2,
4972 x_msg_data OUT NOCOPY VARCHAR2
4973 )
4974 IS
4975 --l_k_article_id_tbl k_article_id_tbl_type
4976 l_proc VARCHAR2 (60) := 'DELETE_DELIVERABLES';
4977 BEGIN
4978 fnd_msg_pub.initialize;
4979
4980 IF p_template_id <> NVL (g_template_id, -1)
4981 THEN
4982 IF inittemplateinfo (p_template_id => p_template_id) <> 'Y'
4983 THEN
4984 okc_api.set_message (p_app_name => g_app_name,
4985 p_msg_name => 'OKC_I_INVALID_VALUE',
4986 p_token1 => 'FIELD',
4987 p_token1_value => 'TEMPLATE_ID'
4988 );
4989 RAISE fnd_api.g_exc_error;
4990 END IF;
4991 END IF;
4992
4993 IF g_deliverable_update_allowed <> 'Y'
4994 THEN
4995 okc_api.set_message (p_app_name => g_app_name,
4996 p_msg_name => 'OKC_I_TEMP_STS_NO_DEL_OBJ',
4997 p_token1 => 'STATUS',
4998 p_token1_value => g_template_status_code,
4999 p_token2 => 'OBJECT',
5000 p_token2_value => 'DELIVERABLE'
5001 );
5002 RAISE fnd_api.g_exc_error;
5003 ELSE
5004 FORALL i IN 1 .. p_deliverable_id_tbl.COUNT
5005 DELETE FROM okc_deliverables
5006 WHERE deliverable_id = p_deliverable_id_tbl (i)
5007 AND business_document_type = 'TEMPLATE'
5008 AND business_document_id = p_template_id
5009 RETURNING deliverable_id
5010 BULK COLLECT INTO x_deliverable_id_tbl;
5011 END IF;
5012
5013 x_return_status := g_ret_sts_success;
5014
5015 IF fnd_api.to_boolean (p_commit)
5016 THEN
5017 COMMIT;
5018 END IF;
5019 EXCEPTION
5020 WHEN fnd_api.g_exc_error
5021 THEN
5022 x_return_status := g_ret_sts_error;
5023 set_proc_error_message (p_proc => l_proc);
5024 read_message (x_msg_data);
5025
5026 IF fnd_api.to_boolean (p_commit)
5027 THEN
5028 COMMIT;
5029 END IF;
5030
5031 fnd_msg_pub.initialize;
5032 WHEN OTHERS
5033 THEN
5034 x_return_status := g_ret_sts_error;
5035 set_proc_error_message (p_proc => l_proc);
5036 okc_api.set_message (p_app_name => g_app_name,
5037 p_msg_name => g_unexpected_error,
5038 p_token1 => g_sqlcode_token,
5039 p_token1_value => SQLCODE,
5040 p_token2 => g_sqlerrm_token,
5041 p_token2_value => SQLERRM
5042 );
5043 read_message (x_msg_data);
5044
5045 IF fnd_api.to_boolean (p_commit)
5046 THEN
5047 COMMIT;
5048 END IF;
5049
5050 fnd_msg_pub.initialize;
5051 END delete_deliverables;
5052
5053 PROCEDURE validate_tmpl_usage (
5054 p_template_id IN NUMBER,
5055 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type,
5056 p_mode IN VARCHAR2 := 'CREATE'
5057 )
5058 IS
5059 l_progress VARCHAR2 (3);
5060 l_proc VARCHAR2 (60) := 'VALIDATE_TMPL_USAGE';
5061 l_doc_intent VARCHAR2 (1);
5062
5063 CURSOR l_doc_intent_csr
5064 IS
5065 SELECT intent
5066 FROM okc_bus_doc_types_b
5067 WHERE document_type = p_tmpl_usage_rec.document_type;
5068 BEGIN
5069 l_progress := '010';
5070
5071 IF p_template_id IS NULL
5072 THEN
5073 okc_api.set_message (g_app_name,
5074 okc_api.g_required_value,
5075 okc_api.g_col_name_token,
5076 'TEMPLATE_ID'
5077 );
5078 RAISE fnd_api.g_exc_error;
5079 -- Template id must not be null and it must be a valid template
5080 ELSIF inittemplateinfo (p_template_id) <> 'Y'
5081 THEN
5082 okc_api.set_message (g_app_name,
5083 okc_api.g_invalid_value,
5084 okc_api.g_col_name_token,
5085 'TEMPLATE_ID'
5086 );
5087 RAISE fnd_api.g_exc_error;
5088 END IF;
5089
5090 -- Validate template status
5091 IF g_headerinfo_update_allowed = 'N'
5092 THEN
5093 l_progress := '020';
5094 -- Can't update anything just return the error.
5095 okc_api.set_message (p_app_name => g_app_name,
5096 p_msg_name => 'OKC_I_TEMP_STS_NO_UPD',
5097 p_token1 => 'STATUS',
5098 p_token1_value => g_template_status_code
5099 );
5100 RAISE fnd_api.g_exc_error;
5101 END IF;
5102
5103 -- If mode is update or delete, validate the allowed_tmpl_usages_id with template id
5104 -- Get object version number and document type details if the user has not already provided.
5105 IF (p_mode = 'UPDATE' OR p_mode = 'DELETE')
5106 THEN
5107 IF p_tmpl_usage_rec.allowed_tmpl_usages_id IS NULL
5108 THEN
5109 okc_api.set_message (g_app_name,
5110 okc_api.g_required_value,
5111 okc_api.g_col_name_token,
5112 'ALLOWED_TMPL_USAGES_ID'
5113 );
5114 RAISE fnd_api.g_exc_error;
5115 -- Validate and initialize the p_tmpl_usage_rec
5116 ELSIF validate_tmpl_usage_id (p_template_id, p_tmpl_usage_rec,
5117 p_mode) <> 'Y'
5118 THEN
5119 okc_api.set_message (g_app_name,
5120 okc_api.g_invalid_value,
5121 okc_api.g_col_name_token,
5122 'ALLOWED_TMPL_USAGES_ID'
5123 );
5124 RAISE fnd_api.g_exc_error;
5125 END IF;
5126 END IF;
5127
5128 -- If the mode is delete do not validate the document_type
5129 IF p_mode <> 'DELETE'
5130 THEN
5131 -- Validate document Type
5132 IF p_tmpl_usage_rec.document_type IS NULL
5133 THEN
5134 okc_api.set_message (g_app_name,
5135 okc_api.g_required_value,
5136 okc_api.g_col_name_token,
5137 'DOCUMENT_TYPE'
5138 );
5139 RAISE fnd_api.g_exc_error;
5140 ELSE
5141 l_doc_intent := NULL;
5142
5143 OPEN l_doc_intent_csr;
5144
5145 FETCH l_doc_intent_csr
5146 INTO l_doc_intent;
5147
5148 CLOSE l_doc_intent_csr;
5149
5150 IF g_template_intent <> l_doc_intent
5151 THEN
5152 okc_api.set_message (g_app_name,
5153 'OKC_TMPL_ALWD_USG_WRONG_INTENT',
5154 'DOCUMENT_TYPE',
5155 p_tmpl_usage_rec.document_type
5156 );
5157 RAISE fnd_api.g_exc_error;
5158 END IF;
5159
5160 IF validate_bus_doc_type (p_tmpl_usage_rec.document_type) <> 'Y'
5161 THEN
5162 okc_api.set_message (g_app_name,
5163 okc_api.g_invalid_value,
5164 okc_api.g_col_name_token,
5165 'DOCUMENT_TYPE'
5166 );
5167 RAISE fnd_api.g_exc_error;
5168 END IF;
5169 END IF;
5170 END IF;
5171 EXCEPTION
5172 WHEN OTHERS
5173 THEN
5174 set_proc_error_message (p_proc => l_proc);
5175 RAISE;
5176 END validate_tmpl_usage;
5177
5178 PROCEDURE create_tmpl_usage (
5179 p_template_id IN NUMBER,
5180 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
5181 )
5182 IS
5183 x_return_status VARCHAR2 (1);
5184 x_msg_count NUMBER;
5185 x_msg_data VARCHAR2 (2000);
5186 l_proc VARCHAR2 (60) := 'CREATE_TMPL_USAGE';
5187
5188 PROCEDURE default_row (
5189 p_template_id IN NUMBER,
5190 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
5191 )
5192 IS
5193 l_proc VARCHAR2 (60) := 'DEFAULT_ROW';
5194 BEGIN
5195 -- IF p_tmpl_usage_rec.ALLOWED_TMPL_USAGES_ID = OKC_API.G_MISS_NUM THEN p_tmpl_usage_rec.ALLOWED_TMPL_USAGES_ID:= NULL; END IF;
5196 IF p_tmpl_usage_rec.document_type = okc_api.g_miss_char
5197 THEN
5198 p_tmpl_usage_rec.document_type := NULL;
5199 END IF;
5200
5201 IF p_tmpl_usage_rec.default_yn = okc_api.g_miss_char
5202 THEN
5203 p_tmpl_usage_rec.default_yn := 'N';
5204 END IF;
5205
5206 IF p_tmpl_usage_rec.last_update_login = okc_api.g_miss_num
5207 THEN
5208 p_tmpl_usage_rec.last_update_login := fnd_global.login_id;
5209 END IF;
5210
5211 IF p_tmpl_usage_rec.creation_date = okc_api.g_miss_date
5212 THEN
5213 p_tmpl_usage_rec.creation_date := SYSDATE;
5214 END IF;
5215
5216 IF p_tmpl_usage_rec.created_by = okc_api.g_miss_num
5217 THEN
5218 p_tmpl_usage_rec.created_by := fnd_global.user_id;
5219 END IF;
5220
5221 IF p_tmpl_usage_rec.last_updated_by = okc_api.g_miss_num
5222 THEN
5223 p_tmpl_usage_rec.last_updated_by := fnd_global.user_id;
5224 END IF;
5225
5226 IF p_tmpl_usage_rec.last_update_date = okc_api.g_miss_date
5227 THEN
5228 p_tmpl_usage_rec.last_update_date := SYSDATE;
5229 END IF;
5230
5231 IF p_tmpl_usage_rec.object_version_number = okc_api.g_miss_num
5232 THEN
5233 p_tmpl_usage_rec.object_version_number := 1;
5234 END IF;
5235
5236 IF p_tmpl_usage_rec.attribute_category = okc_api.g_miss_char
5237 THEN
5238 p_tmpl_usage_rec.attribute_category := NULL;
5239 END IF;
5240
5241 IF p_tmpl_usage_rec.attribute1 = okc_api.g_miss_char
5242 THEN
5243 p_tmpl_usage_rec.attribute1 := NULL;
5244 END IF;
5245
5246 IF p_tmpl_usage_rec.attribute2 = okc_api.g_miss_char
5247 THEN
5248 p_tmpl_usage_rec.attribute2 := NULL;
5249 END IF;
5250
5251 IF p_tmpl_usage_rec.attribute3 = okc_api.g_miss_char
5252 THEN
5253 p_tmpl_usage_rec.attribute3 := NULL;
5254 END IF;
5255
5256 IF p_tmpl_usage_rec.attribute4 = okc_api.g_miss_char
5257 THEN
5258 p_tmpl_usage_rec.attribute4 := NULL;
5259 END IF;
5260
5261 IF p_tmpl_usage_rec.attribute5 = okc_api.g_miss_char
5262 THEN
5263 p_tmpl_usage_rec.attribute5 := NULL;
5264 END IF;
5265
5266 IF p_tmpl_usage_rec.attribute6 = okc_api.g_miss_char
5267 THEN
5268 p_tmpl_usage_rec.attribute6 := NULL;
5269 END IF;
5270
5271 IF p_tmpl_usage_rec.attribute7 = okc_api.g_miss_char
5272 THEN
5273 p_tmpl_usage_rec.attribute7 := NULL;
5274 END IF;
5275
5276 IF p_tmpl_usage_rec.attribute8 = okc_api.g_miss_char
5277 THEN
5278 p_tmpl_usage_rec.attribute8 := NULL;
5279 END IF;
5280
5281 IF p_tmpl_usage_rec.attribute9 = okc_api.g_miss_char
5282 THEN
5283 p_tmpl_usage_rec.attribute9 := NULL;
5284 END IF;
5285
5286 IF p_tmpl_usage_rec.attribute10 = okc_api.g_miss_char
5287 THEN
5288 p_tmpl_usage_rec.attribute10 := NULL;
5289 END IF;
5290
5291 IF p_tmpl_usage_rec.attribute11 = okc_api.g_miss_char
5292 THEN
5293 p_tmpl_usage_rec.attribute11 := NULL;
5294 END IF;
5295
5296 IF p_tmpl_usage_rec.attribute12 = okc_api.g_miss_char
5297 THEN
5298 p_tmpl_usage_rec.attribute12 := NULL;
5299 END IF;
5300
5301 IF p_tmpl_usage_rec.attribute13 = okc_api.g_miss_char
5302 THEN
5303 p_tmpl_usage_rec.attribute13 := NULL;
5304 END IF;
5305
5306 IF p_tmpl_usage_rec.attribute14 = okc_api.g_miss_char
5307 THEN
5308 p_tmpl_usage_rec.attribute14 := NULL;
5309 END IF;
5310
5311 IF p_tmpl_usage_rec.attribute15 = okc_api.g_miss_char
5312 THEN
5313 p_tmpl_usage_rec.attribute15 := NULL;
5314 END IF;
5315 EXCEPTION
5316 WHEN OTHERS
5317 THEN
5318 set_proc_error_message (p_proc => l_proc);
5319 RAISE;
5320 END default_row;
5321 BEGIN
5322 -- Default the Row
5323 default_row (p_template_id => p_template_id,
5324 p_tmpl_usage_rec => p_tmpl_usage_rec
5325 );
5326 -- Validate the Row
5327 validate_tmpl_usage (p_template_id => p_template_id,
5328 p_tmpl_usage_rec => p_tmpl_usage_rec,
5329 p_mode => 'CREATE'
5330 );
5331 -- Insert the Row
5332 okc_allowed_tmpl_usages_grp.create_allowed_tmpl_usages
5333 (p_api_version => 1,
5334 p_init_msg_list => fnd_api.g_true,
5335 p_validation_level => fnd_api.g_valid_level_full,
5336 p_commit => fnd_api.g_false,
5337 x_return_status => x_return_status,
5338 x_msg_count => x_msg_count,
5339 x_msg_data => x_msg_data,
5340 p_template_id => p_template_id,
5341 p_document_type => p_tmpl_usage_rec.document_type,
5342 p_default_yn => p_tmpl_usage_rec.default_yn,
5343 p_allowed_tmpl_usages_id => NULL,
5344 p_attribute_category => p_tmpl_usage_rec.attribute_category,
5345 p_attribute1 => p_tmpl_usage_rec.attribute1,
5346 p_attribute2 => p_tmpl_usage_rec.attribute2,
5347 p_attribute3 => p_tmpl_usage_rec.attribute3,
5348 p_attribute4 => p_tmpl_usage_rec.attribute4,
5349 p_attribute5 => p_tmpl_usage_rec.attribute5,
5350 p_attribute6 => p_tmpl_usage_rec.attribute6,
5351 p_attribute7 => p_tmpl_usage_rec.attribute7,
5352 p_attribute8 => p_tmpl_usage_rec.attribute8,
5353 p_attribute9 => p_tmpl_usage_rec.attribute9,
5354 p_attribute10 => p_tmpl_usage_rec.attribute10,
5355 p_attribute11 => p_tmpl_usage_rec.attribute11,
5356 p_attribute12 => p_tmpl_usage_rec.attribute12,
5357 p_attribute13 => p_tmpl_usage_rec.attribute13,
5358 p_attribute14 => p_tmpl_usage_rec.attribute14,
5359 p_attribute15 => p_tmpl_usage_rec.attribute15,
5360 x_allowed_tmpl_usages_id => p_tmpl_usage_rec.allowed_tmpl_usages_id
5361 );
5362
5363 IF x_return_status <> g_ret_sts_success
5364 THEN
5365 p_tmpl_usage_rec.status := g_ret_sts_error;
5366 RAISE fnd_api.g_exc_error;
5367 END IF;
5368 EXCEPTION
5369 WHEN OTHERS
5370 THEN
5371 set_proc_error_message (p_proc => l_proc);
5372 RAISE;
5373 END create_tmpl_usage;
5374
5375 PROCEDURE create_tmpl_usage (
5376 p_template_id IN NUMBER,
5377 p_tmpl_usage_tbl IN OUT NOCOPY tmpl_usage_tbl_type,
5378 p_commit IN VARCHAR2 := fnd_api.g_false
5379 )
5380 IS
5381 l_error_message VARCHAR2 (2000);
5382 BEGIN
5383 IF p_tmpl_usage_tbl.COUNT > 0
5384 THEN
5385 FOR i IN p_tmpl_usage_tbl.FIRST .. p_tmpl_usage_tbl.LAST
5386 LOOP
5387 BEGIN
5388 fnd_msg_pub.initialize;
5389 SAVEPOINT create_tmpl_usage_sp;
5390 create_tmpl_usage (p_template_id => p_template_id,
5391 p_tmpl_usage_rec => p_tmpl_usage_tbl
5392 (i)
5393 );
5394 p_tmpl_usage_tbl (i).status := g_ret_sts_success;
5395
5396 IF fnd_api.to_boolean (p_commit)
5397 THEN
5398 COMMIT;
5399 END IF;
5400 EXCEPTION
5401 WHEN fnd_api.g_exc_error
5402 THEN
5403 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5404 read_message (l_error_message);
5405 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5406 ROLLBACK TO create_tmpl_usage_sp;
5407 fnd_msg_pub.initialize;
5408 WHEN OTHERS
5409 THEN
5410 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5411 okc_api.set_message (p_app_name => g_app_name,
5412 p_msg_name => g_unexpected_error,
5413 p_token1 => g_sqlcode_token,
5414 p_token1_value => SQLCODE,
5415 p_token2 => g_sqlerrm_token,
5416 p_token2_value => SQLERRM
5417 );
5418 read_message (l_error_message);
5419 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5420 ROLLBACK TO create_tmpl_usage_sp;
5421 fnd_msg_pub.initialize;
5422 END;
5423 END LOOP;
5424 END IF;
5425 EXCEPTION
5426 WHEN OTHERS
5427 THEN
5428 RAISE;
5429 END create_tmpl_usage;
5430
5431 PROCEDURE update_tmpl_usage (
5432 p_template_id IN NUMBER,
5433 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
5434 )
5435 IS
5436 l_proc VARCHAR2 (60) := 'UPDATE_TMPL_USAGE';
5437 x_return_status VARCHAR2 (1);
5438 x_msg_count NUMBER;
5439 x_msg_data VARCHAR2 (2000);
5440 BEGIN
5441 -- Do Basic Validation
5442 validate_tmpl_usage (p_template_id, p_tmpl_usage_rec, 'UPDATE');
5443 -- Call API to validate and Update
5444 okc_allowed_tmpl_usages_grp.update_allowed_tmpl_usages
5445 (p_api_version => 1,
5446 p_init_msg_list => fnd_api.g_true,
5447 p_validation_level => fnd_api.g_valid_level_full,
5448 p_commit => fnd_api.g_false,
5449 x_return_status => x_return_status,
5450 x_msg_count => x_msg_count,
5451 x_msg_data => x_msg_data,
5452 p_template_id => p_template_id,
5453 p_document_type => p_tmpl_usage_rec.document_type,
5454 p_default_yn => p_tmpl_usage_rec.default_yn,
5455 p_allowed_tmpl_usages_id => p_tmpl_usage_rec.allowed_tmpl_usages_id,
5456 p_attribute_category => p_tmpl_usage_rec.attribute_category,
5457 p_attribute1 => p_tmpl_usage_rec.attribute1,
5458 p_attribute2 => p_tmpl_usage_rec.attribute2,
5459 p_attribute3 => p_tmpl_usage_rec.attribute3,
5460 p_attribute4 => p_tmpl_usage_rec.attribute4,
5461 p_attribute5 => p_tmpl_usage_rec.attribute5,
5462 p_attribute6 => p_tmpl_usage_rec.attribute6,
5463 p_attribute7 => p_tmpl_usage_rec.attribute7,
5464 p_attribute8 => p_tmpl_usage_rec.attribute8,
5465 p_attribute9 => p_tmpl_usage_rec.attribute9,
5466 p_attribute10 => p_tmpl_usage_rec.attribute10,
5467 p_attribute11 => p_tmpl_usage_rec.attribute11,
5468 p_attribute12 => p_tmpl_usage_rec.attribute12,
5469 p_attribute13 => p_tmpl_usage_rec.attribute13,
5470 p_attribute14 => p_tmpl_usage_rec.attribute14,
5471 p_attribute15 => p_tmpl_usage_rec.attribute15,
5472 p_object_version_number => p_tmpl_usage_rec.object_version_number
5473 );
5474
5475 IF x_return_status <> g_ret_sts_success
5476 THEN
5477 p_tmpl_usage_rec.status := g_ret_sts_error;
5478 RAISE fnd_api.g_exc_error;
5479 END IF;
5480 EXCEPTION
5481 WHEN OTHERS
5482 THEN
5483 set_proc_error_message (p_proc => l_proc);
5484 RAISE;
5485 END update_tmpl_usage;
5486
5487 PROCEDURE update_tmpl_usage (
5488 p_template_id IN NUMBER,
5489 p_tmpl_usage_tbl IN OUT NOCOPY tmpl_usage_tbl_type,
5490 p_commit IN VARCHAR2 := fnd_api.g_false
5491 )
5492 IS
5493 l_error_message VARCHAR2 (2000);
5494 BEGIN
5495 IF p_tmpl_usage_tbl.COUNT > 0
5496 THEN
5497 FOR i IN p_tmpl_usage_tbl.FIRST .. p_tmpl_usage_tbl.LAST
5498 LOOP
5499 BEGIN
5500 fnd_msg_pub.initialize;
5501 SAVEPOINT update_tmpl_usage_sp;
5502 update_tmpl_usage (p_template_id => p_template_id,
5503 p_tmpl_usage_rec => p_tmpl_usage_tbl
5504 (i)
5505 );
5506 p_tmpl_usage_tbl (i).status := g_ret_sts_success;
5507
5508 IF fnd_api.to_boolean (p_commit)
5509 THEN
5510 COMMIT;
5511 END IF;
5512 EXCEPTION
5513 WHEN fnd_api.g_exc_error
5514 THEN
5515 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5516 read_message (l_error_message);
5517 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5518 ROLLBACK TO update_tmpl_usage_sp;
5519 fnd_msg_pub.initialize;
5520 WHEN OTHERS
5521 THEN
5522 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5523 okc_api.set_message (p_app_name => g_app_name,
5524 p_msg_name => g_unexpected_error,
5525 p_token1 => g_sqlcode_token,
5526 p_token1_value => SQLCODE,
5527 p_token2 => g_sqlerrm_token,
5528 p_token2_value => SQLERRM
5529 );
5530 read_message (l_error_message);
5531 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5532 ROLLBACK TO create_tmpl_usage_sp;
5533 fnd_msg_pub.initialize;
5534 END;
5535 END LOOP;
5536 END IF;
5537 EXCEPTION
5538 WHEN OTHERS
5539 THEN
5540 RAISE;
5541 END update_tmpl_usage;
5542
5543 PROCEDURE delete_tmpl_usage (
5544 p_template_id IN NUMBER,
5545 p_tmpl_usage_rec IN OUT NOCOPY tmpl_usage_rec_type
5546 )
5547 IS
5548 l_proc VARCHAR2 (60) := 'DELETE_TMPL_USAGE';
5549 x_return_status VARCHAR2 (1);
5550 x_msg_count NUMBER;
5551 x_msg_data VARCHAR2 (2000);
5552 BEGIN
5553 -- Do Basic Validation
5554 validate_tmpl_usage (p_template_id, p_tmpl_usage_rec, 'DELETE');
5555 -- Call API to validate and Update
5556 okc_allowed_tmpl_usages_grp.delete_allowed_tmpl_usages
5557 (p_api_version => 1,
5558 p_init_msg_list => fnd_api.g_true,
5559 p_commit => fnd_api.g_false,
5560 x_return_status => x_return_status,
5561 x_msg_count => x_msg_count,
5562 x_msg_data => x_msg_data,
5563 p_allowed_tmpl_usages_id => p_tmpl_usage_rec.allowed_tmpl_usages_id,
5564 p_object_version_number => p_tmpl_usage_rec.object_version_number
5565 );
5566
5567 IF x_return_status <> g_ret_sts_success
5568 THEN
5569 p_tmpl_usage_rec.status := g_ret_sts_error;
5570 RAISE fnd_api.g_exc_error;
5571 END IF;
5572 EXCEPTION
5573 WHEN OTHERS
5574 THEN
5575 set_proc_error_message (p_proc => l_proc);
5576 RAISE;
5577 END delete_tmpl_usage;
5578
5579 PROCEDURE delete_tmpl_usage (
5580 p_template_id IN NUMBER,
5581 p_tmpl_usage_tbl IN OUT NOCOPY tmpl_usage_tbl_type,
5582 p_commit IN VARCHAR2 := fnd_api.g_false
5583 )
5584 IS
5585 l_error_message VARCHAR2 (2000);
5586 BEGIN
5587 IF p_tmpl_usage_tbl.COUNT > 0
5588 THEN
5589 FOR i IN p_tmpl_usage_tbl.FIRST .. p_tmpl_usage_tbl.LAST
5590 LOOP
5591 BEGIN
5592 fnd_msg_pub.initialize;
5593 SAVEPOINT delete_tmpl_usage_sp;
5594 delete_tmpl_usage (p_template_id => p_template_id,
5595 p_tmpl_usage_rec => p_tmpl_usage_tbl
5596 (i)
5597 );
5598 p_tmpl_usage_tbl (i).status := g_ret_sts_success;
5599
5600 IF fnd_api.to_boolean (p_commit)
5601 THEN
5602 COMMIT;
5603 END IF;
5604 EXCEPTION
5605 WHEN fnd_api.g_exc_error
5606 THEN
5607 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5608 read_message (l_error_message);
5609 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5610 ROLLBACK TO delete_tmpl_usage_sp;
5611 fnd_msg_pub.initialize;
5612 WHEN OTHERS
5613 THEN
5614 p_tmpl_usage_tbl (i).status := g_ret_sts_error;
5615 okc_api.set_message (p_app_name => g_app_name,
5616 p_msg_name => g_unexpected_error,
5617 p_token1 => g_sqlcode_token,
5618 p_token1_value => SQLCODE,
5619 p_token2 => g_sqlerrm_token,
5620 p_token2_value => SQLERRM
5621 );
5622 read_message (l_error_message);
5623 p_tmpl_usage_tbl (i).errmsg := l_error_message;
5624 ROLLBACK TO delete_tmpl_usage_sp;
5625 fnd_msg_pub.initialize;
5626 END;
5627 END LOOP;
5628 END IF;
5629 EXCEPTION
5630 WHEN OTHERS
5631 THEN
5632 RAISE;
5633 END delete_tmpl_usage;
5634 END okc_imp_terms_templates_pvt;