[Home] [Help]
PACKAGE BODY: APPS.XLE_ASSOC_VALIDATIONS_PVT
Source
1 PACKAGE BODY XLE_ASSOC_VALIDATIONS_PVT AS
2 /* $Header: xleassvb.pls 120.6 2006/03/09 09:16:32 apbalakr ship $ */
3 -- ==========================================================================
4 -- PROCEDURE
5 -- Validate_Mandatory
6 --
7 -- DESCRIPTION
8 -- Check whether the parameter has a value
9 --
10 -- ARGUMENTS :
11 -- IN : p_param_name
12 -- p_param_value
13 --
14 -- MODIFICATION HISTORY
15 --
16 -- ===========================================================================
17
18 PROCEDURE Validate_Mandatory (
19 p_param_name IN VARCHAR2,
20 p_param_value IN VARCHAR2)
21 IS
22 BEGIN
23
24 IF (p_param_value IS NULL OR p_param_value = FND_API.G_MISS_CHAR) THEN
25 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_MISSING_PARAM');
26 FND_MSG_PUB.ADD;
27 RAISE FND_API.G_EXC_ERROR;
28 END IF;
29 EXCEPTION
30 WHEN OTHERS THEN
31 RAISE;
32 END Validate_Mandatory;
33
34
35 -- ==========================================================================
36 -- PROCEDURE
37 -- Validate_Context
38 --
39 -- DESCRIPTION
40 -- Check whether the context is valid
41 --
42 -- ARGUMENTS :
43 -- IN : p_context
44 --
45 -- MODIFICATION HISTORY
46 --
47 -- ===========================================================================
48
49
50 PROCEDURE Validate_Context (
51 p_context IN VARCHAR2)
52
53 IS
54 CURSOR Context_Cursor IS
55 SELECT Association_Type_Id
56 FROM XLE_ASSOCIATION_TYPES
57 WHERE CONTEXT = upper(p_context);
58 l_association_type_id NUMBER;
59 BEGIN
60
61 OPEN Context_Cursor;
62 FETCH Context_Cursor INTO l_association_type_id;
63
64 IF (Context_Cursor%NOTFOUND) THEN
65 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_PARAM');
66 FND_MESSAGE.SET_TOKEN ('PARAM', 'Context');
67 FND_MSG_PUB.ADD;
68 RAISE FND_API.G_EXC_ERROR;
69 END IF;
70 CLOSE Context_Cursor;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 RAISE;
75 END Validate_Context;
76
77
78 -- ==========================================================================
79 -- PROCEDURE
80 -- Validate_Object
81 --
82 -- DESCRIPTION
83 -- Check whether the Object Type and ID are valid
84 --
85 -- ARGUMENTS :
86 -- IN : p_object_type (Defined in XLE_ASSOC_OBJECT_TYPES)
87 -- p_object_id
88 -- p_param1_name (Object Type Parameter Name - Used in Error Message)
89 -- p_param2_name (Object Id Parameter Name - Used in Error Message)
90 --
91 -- OUT : x_OBJECT_type_id
92 --
93 -- IN/OUT :
94 --
95 -- MODIFICATION HISTORY
96 --
97 -- ===========================================================================
98
99
100 PROCEDURE Validate_Object (
101 p_object_type IN VARCHAR2,
102 p_object_id IN NUMBER ,
103 p_param1_name IN VARCHAR2,
104 p_param2_name IN VARCHAR2,
105 x_OBJECT_type_id OUT NOCOPY NUMBER )
106 IS
107 l_select_statement VARCHAR2(1000);
108 l_OBJECT_type_rec XLE_ASSOC_OBJECT_TYPES%ROWTYPE;
109 l_cursor INTEGER;
110 l_dummy INTEGER;
111
112 CURSOR OBJECT_Type_Cursor IS
113 SELECT *
114 FROM XLE_ASSOC_OBJECT_TYPES
115 WHERE name = upper(p_object_type);
116
117 BEGIN
118
119 OPEN OBJECT_Type_Cursor;
120 FETCH OBJECT_Type_Cursor INTO l_OBJECT_type_rec;
121
122 IF (OBJECT_Type_Cursor%NOTFOUND) THEN
123 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_PARAM');
124 FND_MESSAGE.SET_TOKEN ('PARAM', p_param1_name);
125 FND_MSG_PUB.ADD;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128 CLOSE OBJECT_Type_Cursor;
129
130 l_select_statement := 'SELECT 1 FROM ' || l_OBJECT_type_rec.source_table ||
131 ' WHERE ' || l_OBJECT_type_rec.source_column1 || ' =:pk_id';
132
133 IF (l_OBJECT_type_rec.where_clause IS NOT NULL) THEN
134 l_select_statement := l_select_statement || ' AND ' || l_OBJECT_type_rec.where_clause;
135 END IF;
136
137 l_cursor := DBMS_SQL.OPEN_CURSOR;
138
139 DBMS_SQL.PARSE(l_cursor, l_select_statement, DBMS_SQL.V7);
140 DBMS_SQL.BIND_VARIABLE (l_cursor, ':pk_id', p_object_id);
141
142 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
143
144 IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
145 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_PARAM');
146 FND_MESSAGE.SET_TOKEN ('PARAM', p_param2_name);
147 FND_MSG_PUB.ADD;
148 RAISE FND_API.G_EXC_ERROR;
149 END IF;
150 DBMS_SQL.CLOSE_CURSOR (l_cursor);
151 x_OBJECT_type_id := l_OBJECT_type_rec.OBJECT_TYPE_ID;
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 -- dbms_output.put_line('-- SQLCODE : ' || SQLCODE || ' ' || SQLERRM);
156 RAISE;
157 END Validate_Object;
158
159 -- ==========================================================================
160 -- PROCEDURE
161 -- Validate_Association_Id
162 --
163 -- DESCRIPTION
164 -- Check whether the association ID is valid
165 -- Return the Association Type, Subject ID and Object ID
166 --
167 -- ARGUMENTS :
168 -- IN : p_association_id
169 -- OUT : p_association_type_id
170 -- p_subject_id
171 -- p_object_id
172 --
173 -- MODIFICATION HISTORY
174 --
175 -- ===========================================================================
176
177
178 PROCEDURE Validate_Association_Id (
179 p_association_id IN NUMBER,
180 p_association_type_id OUT NOCOPY NUMBER,
181 p_subject_id OUT NOCOPY NUMBER,
182 p_object_id OUT NOCOPY NUMBER)
183 IS
184
185 BEGIN
186 SELECT association_type_id, subject_id, object_id
187 INTO p_association_type_id, p_subject_id, p_object_id
188 FROM XLE_ASSOCIATIONS
189 WHERE ASSOCIATION_ID = p_association_id;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_PARAM');
194 FND_MESSAGE.SET_TOKEN ('PARAM', 'Association Id');
195 FND_MSG_PUB.ADD;
196 RAISE FND_API.G_EXC_ERROR;
197 WHEN OTHERS THEN
198 -- dbms_output.put_line('-- SQLCODE : ' || SQLCODE || ' ' || SQLERRM);
199 RAISE;
200 END Validate_Association_Id;
201
202
203 -- ==========================================================================
204 -- PROCEDURE
205 -- Default_Association_Type
206 --
207 -- DESCRIPTION
208 -- Check whether the Combination of Subject ID, Object ID, Context is valid
209 -- Find corresponding Association Type
210 --
211 -- ARGUMENTS :
212 -- IN : p_context
213 -- p_subject_type (ID)
214 -- p_object_type (ID)
215 --
216 -- OUT : x_association_type_id
217 --
218 -- MODIFICATION HISTORY
219 --
220 -- ===========================================================================
221
222 PROCEDURE Default_Association_Type (
223 p_context IN VARCHAR2,
224 p_subject_type IN NUMBER ,
225 p_object_type IN NUMBER ,
226 x_association_type_id OUT NOCOPY NUMBER )
227
228 IS
229
230 BEGIN
231 SELECT ASSOCIATION_TYPE_ID
232 INTO x_association_type_id
233 FROM XLE_ASSOCIATION_TYPES
234 WHERE CONTEXT = upper(p_context)
235 AND SUBJECT_TYPE_ID = p_subject_type
236 AND OBJECT_TYPE_ID = p_object_type;
237
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 FND_MESSAGE.SET_NAME ('XLE', 'XLE_NO_ASSOCIATION_TYPE');
241 FND_MSG_PUB.ADD;
242 RAISE FND_API.G_EXC_ERROR;
243 WHEN OTHERS THEN
244 RAISE;
245
246 END Default_Association_Type;
247 -- ==========================================================================
248 -- PROCEDURE
249 -- Default_Association_Type
250 --
251 -- DESCRIPTION
252 -- Check whether the Combination of Subject Name, Object Name, Context is valid
253 -- Find corresponding Association Type
254 --
255 -- ARGUMENTS :
256 -- IN : p_context
257 -- p_subject_type (NAME)
258 -- p_object_type (NAME)
259 --
260 -- OUT : x_association_type_id
261 --
262 -- MODIFICATION HISTORY
263 --
264 -- ===========================================================================
265
266 PROCEDURE Default_Association_Type (
267 p_context IN VARCHAR2,
268 p_subject_type IN VARCHAR2,
269 p_object_type IN VARCHAR2,
270 x_association_type_id OUT NOCOPY NUMBER )
271
272 IS
273
274 BEGIN
275 SELECT AT.ASSOCIATION_TYPE_ID
276 INTO x_association_type_id
277 FROM XLE_ASSOCIATION_TYPES AT,
278 XLE_ASSOC_OBJECT_TYPES ST,
279 XLE_ASSOC_OBJECT_TYPES OT
280 WHERE AT.CONTEXT = p_context
281 AND AT.SUBJECT_TYPE_ID = ST.object_type_id
282 AND ST.NAME = upper (P_SUBJECT_TYPE)
283 AND AT.OBJECT_TYPE_ID = OT.object_type_id
284 AND OT.NAME = upper (P_OBJECT_TYPE);
285
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 FND_MESSAGE.SET_NAME ('XLE', 'XLE_NO_ASSOCIATION_TYPE');
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_ERROR;
291 WHEN OTHERS THEN
292 RAISE;
293
294 END Default_Association_Type;
295
296
297
298 -- ==========================================================================
299 -- PROCEDURE
300 -- Validate_Cardinality
301 --
302 -- DESCRIPTION
303 -- Check whether the association cardinality is respected
304 --
305 -- ARGUMENTS
306 -- IN : p_association_type_id
307 -- p_subject_id
308 -- p_object_id
309 --
310 -- MODIFICATION HISTORY
311 --
312 -- ===========================================================================
313
314 PROCEDURE Validate_Cardinality (
315 p_association_type_id IN NUMBER ,
316 p_subject_type IN VARCHAR2,
317 p_subject_id IN NUMBER ,
318 p_object_type IN VARCHAR2,
319 p_object_id IN NUMBER )
320
321 IS
322
323 CURSOR Association_Type_Cursor
324 IS
325 SELECT *
326 FROM XLE_ASSOCIATION_TYPES
327 WHERE ASSOCIATION_TYPE_ID = p_association_type_id;
328
329 CURSOR Association_Subject_Cursor
330 IS
331 SELECT *
332 FROM XLE_ASSOCIATIONS
333 WHERE ASSOCIATION_TYPE_ID = p_association_type_id
334 AND SUBJECT_ID = p_subject_id
335 AND EFFECTIVE_TO IS NULL;
336
337 CURSOR Association_Object_Cursor
338 IS
339 SELECT *
340 FROM XLE_ASSOCIATIONS
341 WHERE ASSOCIATION_TYPE_ID = p_association_type_id
342 AND OBJECT_ID = p_object_id
343 AND EFFECTIVE_TO IS NULL;
344
345 l_association_type_rec XLE_ASSOCIATION_TYPES%ROWTYPE;
346 l_association_subject_rec XLE_ASSOCIATIONS%ROWTYPE;
347 l_association_object_rec XLE_ASSOCIATIONS%ROWTYPE;
348
349
350 BEGIN
351
352 OPEN Association_Type_Cursor;
353 FETCH Association_Type_Cursor INTO l_association_type_rec;
354
355 IF (Association_Type_Cursor%NOTFOUND) THEN
356 FND_MESSAGE.SET_NAME ('XLE', 'XLE_NO_ASSOCIATION_TYPE');
357 FND_MSG_PUB.ADD;
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360 CLOSE Association_Type_Cursor;
361
362 IF (l_association_type_rec.cardinality in ('MO','OO')) THEN
363 OPEN Association_Subject_Cursor;
367 IF (l_association_subject_rec.object_id <> p_object_id) THEN
364 FETCH Association_Subject_Cursor INTO l_association_subject_rec;
365
366 IF (Association_Subject_Cursor%FOUND) THEN
368 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_CARDINALITY');
369 FND_MESSAGE.SET_TOKEN ('OBJECT1' , p_subject_id);
370 FND_MESSAGE.SET_TOKEN ('TYPE1', p_subject_type);
371 FND_MESSAGE.SET_TOKEN ('OBJECT2' , l_association_subject_rec.object_id);
372 FND_MESSAGE.SET_TOKEN ('TYPE2', p_object_type);
373 FND_MSG_PUB.ADD;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376 END IF;
377 CLOSE Association_Subject_Cursor;
378 END IF;
379
380 IF (l_association_type_rec.cardinality in ('OM','OO')) THEN
381 OPEN Association_Object_Cursor;
382 FETCH Association_Object_Cursor INTO l_association_object_rec;
383
384 IF (Association_Object_Cursor%FOUND) THEN
385 IF (l_association_object_rec.subject_id <> p_subject_id) THEN
386 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_CARDINALITY');
387 FND_MESSAGE.SET_TOKEN ('TYPE1', p_object_type);
388 FND_MESSAGE.SET_TOKEN ('OBJECT1' , p_object_id);
389 FND_MESSAGE.SET_TOKEN ('TYPE2', p_subject_type);
390 FND_MESSAGE.SET_TOKEN ('OBJECT2' , l_association_object_rec.subject_id);
391 FND_MSG_PUB.ADD;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394 END IF;
395 CLOSE Association_Object_Cursor;
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 RAISE;
401
402 END Validate_Cardinality;
403
404 -- ==========================================================================
405 -- PROCEDURE
406 -- Get_Effective_From_Date
407 --
408 -- DESCRIPTION
409 -- Retrieves the effective from date of an existing association
410 --
411 -- ARGUMENTS :
412 -- IN : p_association_id
413 --
414 -- OUT : p_effective_from
415 --
416 -- MODIFICATION HISTORY
417 --
418 -- ===========================================================================
419
420 PROCEDURE Get_Effective_From_Date (
421 p_association_id IN NUMBER ,
422 p_effective_from OUT NOCOPY DATE )
423 IS
424 BEGIN
425 SELECT effective_from
426 INTO p_effective_from
427 FROM XLE_ASSOCIATIONS
428 WHERE ASSOCIATION_ID = p_association_id;
429
430 EXCEPTION
431 WHEN NO_DATA_FOUND THEN
432 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_PARAM');
433 FND_MESSAGE.SET_TOKEN ('PARAM', 'Association Id');
434 FND_MSG_PUB.ADD;
435 RAISE FND_API.G_EXC_ERROR;
436 WHEN OTHERS THEN
437 RAISE;
438 END Get_Effective_From_Date;
439
440 -- ==========================================================================
441 -- FUNCTION
442 -- Is_Date_Overlap
443 --
444 -- DESCRIPTION
445 -- Returns true if period [s1,e1] overlaps [s2,e2], false otherwise
446 --
447 -- ARGUMENTS :
448 -- IN : start_date1 Start date of period 1
449 -- end_date1 End date of period 1
450 -- start_date2 Start date of period 2
451 -- end_date2 End date of period 2
452 --
453 -- MODIFICATION HISTORY
454 --
455 -- ===========================================================================
456
457
458 FUNCTION Is_date_overlap (
459 start_date1 IN DATE ,
460 end_date1 IN DATE ,
461 start_date2 IN DATE ,
462 end_date2 IN DATE )
463 RETURN BOOLEAN
464
465 IS
466
467 BEGIN
468
469 IF (start_date1 between start_date2 and nvl(end_date2, start_date1)) OR
470 (start_date2 between start_date1 and nvl(end_date1, start_date2)) THEN
471 RETURN true;
472 ELSE
473 RETURN false;
474 END IF;
475 EXCEPTION
476 WHEN OTHERS THEN
477 RAISE;
478 END is_date_overlap;
479
480
481 -- ==========================================================================
482 -- PROCEDURE
483 -- Validate_Effective_Dates
484 --
485 -- DESCRIPTION
486 -- Checks that the Association Effective dates are between the Association
487 -- Type Effective dates.
488 --
489 -- ARGUMENTS :
490 -- IN : p_association_type_id
491 -- p_effective_from
492 -- p_effective_to
493 --
494 -- MODIFICATION HISTORY
495 --
496 -- ===========================================================================
497
498 PROCEDURE Validate_Effective_Dates (
499 p_association_type_id IN NUMBER ,
500 p_effective_from IN DATE ,
501 p_effective_to IN DATE := NULL )
502
503 IS
504
505 CURSOR Association_Type_Cursor
506 IS
507 SELECT *
508 FROM XLE_ASSOCIATION_TYPES
509 WHERE ASSOCIATION_TYPE_ID = p_association_type_id;
510
511 l_association_type_rec XLE_ASSOCIATION_TYPES%ROWTYPE;
512
513 BEGIN
514
515 OPEN Association_Type_Cursor;
516 FETCH Association_Type_Cursor INTO l_association_type_rec;
517
518 IF (Association_Type_Cursor%NOTFOUND) THEN
519 FND_MESSAGE.SET_NAME ('XLE', 'XLE_NO_ASSOCIATION_TYPE');
520 FND_MSG_PUB.ADD;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523 CLOSE Association_Type_Cursor;
524
525 -- Validates the Association Effective From Date is not posterior to the Effective To Date
526 IF (trunc(p_effective_from) > NVL(p_effective_to, p_effective_from)) THEN
530 END IF;
527 FND_MESSAGE.SET_NAME ('XLE', 'XLE_EFF_FROM_TO_DATE_ERR');
528 FND_MSG_PUB.ADD;
529 RAISE FND_API.G_EXC_ERROR;
531
532 -- Validates the Association Effective Dates are between the Association Types Effective Dates
533 IF (trunc(p_effective_from) NOT BETWEEN trunc(l_association_type_rec.effective_from) AND NVL(l_association_type_rec.effective_to, trunc(p_effective_from))) OR
534 (p_effective_to IS NOT NULL AND
535 p_effective_to NOT BETWEEN trunc(l_association_type_rec.effective_from) AND NVL(l_association_type_rec.effective_to, p_effective_to)) THEN
536 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_INVALID_EFF_DATE');
537 FND_MSG_PUB.ADD;
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 RAISE;
545 END Validate_Effective_Dates;
546
547
548 -- ==========================================================================
549 -- PROCEDURE
550 -- Validate_Overlap_Dates
551 --
552 -- DESCRIPTION
553 -- Check there is no time overlap among associations with same
554 -- Association Type, Subject_Id, Object_Id
555 --
556 -- ARGUMENTS :
557 -- IN : p_association_type_id
558 -- p_subject_id
559 -- p_object_id
560 -- p_effective_from
561 -- p_effective_to
562 --
563 -- MODIFICATION HISTORY
564 --
565 -- ===========================================================================
566
567 PROCEDURE Validate_Overlap_Dates (
568 p_association_id IN NUMBER := NULL,
569 p_association_type_id IN NUMBER ,
570 p_subject_id IN NUMBER ,
571 p_object_id IN NUMBER ,
572 p_effective_from IN DATE ,
573 p_effective_to IN DATE := NULL)
574
575 IS
576
577 CURSOR Association_Cursor
578 IS
579 SELECT *
580 FROM XLE_ASSOCIATIONS
581 WHERE ASSOCIATION_TYPE_ID = p_association_type_id
582 AND SUBJECT_ID = p_subject_id
583 AND OBJECT_ID = p_object_id;
584
585
586 l_association_rec XLE_ASSOCIATIONS%ROWTYPE;
587 l_effective_from DATE;
588 l_effective_to DATE;
589
590 BEGIN
591
592 IF (p_effective_from = FND_API.G_MISS_DATE) THEN
593 l_effective_from := NULL;
594 ELSE
595 l_effective_from := p_effective_from;
596 END IF;
597
598 IF (p_effective_to = FND_API.G_MISS_DATE) THEN
599 l_effective_to := NULL;
600 ELSE
601 l_effective_to := p_effective_to;
602 END IF;
603
604
605 FOR l_association_rec IN Association_Cursor
606 LOOP
607 IF (p_association_id IS NULL AND l_association_rec.effective_to IS NULL) THEN
608 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_EXISTS_WARN');
609 FND_MSG_PUB.ADD;
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 IF (p_association_id IS NULL) OR (p_association_id IS NOT NULL AND l_association_rec.association_id <> p_association_id) THEN
614 IF (is_date_overlap (l_effective_from, l_effective_to,
615 l_association_rec.effective_from, l_association_rec.effective_to)) THEN
616 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_OVERLAP_DATES');
617 FND_MSG_PUB.ADD;
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620 END IF;
621 END LOOP;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 RAISE;
626
627 END Validate_Overlap_Dates;
628
629
630 -- ==========================================================================
631 -- PROCEDURE
632 -- Get_Parent_Id
633 --
634 -- DESCRIPTION
635 -- Find the Parent Legal Entity for a given Establishment
636 --
637 -- ARGUMENTS :
638 -- IN : p_object_type
639 -- p_object_id
640 --
641 -- OUT : x_object_parent_id
642 --
643 -- MODIFICATION HISTORY
644 --
645 -- ===========================================================================
646
647
648 PROCEDURE Get_Parent_Id (
649 p_object_type IN VARCHAR2,
650 p_object_id IN NUMBER ,
651 x_object_parent_id OUT NOCOPY NUMBER )
652 IS
653
654 BEGIN
655 IF (upper(p_object_type) = 'ESTABLISHMENT') THEN
656 SELECT legal_entity_id
657 INTO x_object_parent_id
658 FROM XLE_ETB_PROFILES
659 WHERE establishment_id = p_object_id;
660 END IF;
661
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 x_object_parent_id := NULL;
665 WHEN OTHERS THEN
666 RAISE;
667 END Get_Parent_Id;
668
669 -- ==========================================================================
670 -- PROCEDURE
671 -- Validate_Parameter_Combination
672 --
673 -- DESCRIPTION
674 -- Validations of the context, subject and object types and IDs
675 -- Finds the corresponding Association Type
676 --
677 -- ARGUMENTS :
678 -- IN : p_context
679 -- p_subject_type
680 -- p_subject_id
681 -- p_object_type
682 -- p_object_id
683 --
684 -- OUT : x_association_type_id
685 --
686 -- MODIFICATION HISTORY
687 --
688 -- ===========================================================================
689
690
691 PROCEDURE Validate_Parameter_Combination (
692 p_context IN VARCHAR2,
693 p_subject_type IN VARCHAR2,
694 p_subject_id IN NUMBER ,
698 IS
695 p_object_type IN VARCHAR2,
696 p_object_id IN NUMBER ,
697 x_association_type_id OUT NOCOPY NUMBER )
699 l_subject_type_id NUMBER;
700 l_object_type_id NUMBER;
701
702 BEGIN
703
704 -- **** Validate that all the mandatory input parameters are provided ****
705
706 Validate_Mandatory ('Context' , p_context);
707 Validate_Mandatory ('Subject Type', p_subject_type);
708 Validate_Mandatory ('Subject ID' , p_subject_id);
709 Validate_Mandatory ('Object Type' , p_object_type);
710 Validate_Mandatory ('Object ID' , p_object_id);
711
712 -- **** Validate the context, Subject and Object Types and IDs ****
713
714 Validate_Context(p_context);
715 Validate_Object (p_subject_type, p_subject_id, 'Subject Type','Subject_Id',l_subject_type_id);
716 Validate_Object (p_object_type, p_object_id, 'Object Type', 'Object_Id', l_object_type_id);
717
718 -- **** Defaults the Association Type
719
720 Default_Association_Type (p_context, l_subject_type_id, l_object_type_id, x_association_type_id);
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 RAISE;
725
726 END Validate_Parameter_Combination;
727
728 -- ==============================================================================
729 -- PROCEDURE
730 -- Get_Association_Id
731 --
732 -- DESCRIPTION
733 -- Find an association based on the context, subject and object types and Ids
734 --
735 -- ARGUMENTS :
736 -- IN : p_context
737 -- p_subject_type
738 -- p_subject_id
739 -- p_object_type
740 -- p_object_id
741 --
742 -- OUT : x_association_id
743 --
744 -- MODIFICATION HISTORY
745 --
746 -- ==============================================================================
747
748
749 PROCEDURE Get_Association_Id (
750 p_subject_id IN NUMBER,
751 p_object_id IN NUMBER,
752 p_association_type_id IN NUMBER,
753 x_association_id OUT NOCOPY NUMBER)
754 IS
755
756 BEGIN
757
758 SELECT association_id
759 INTO x_association_id
760 FROM XLE_ASSOCIATIONS
761 WHERE association_type_id = p_association_type_id
762 AND subject_id = p_subject_id
763 AND object_id = p_object_id
764 AND effective_to IS NULL;
765
766 EXCEPTION
767 WHEN NO_DATA_FOUND THEN
768 FND_MESSAGE.SET_NAME ('XLE', 'XLE_NO_ASSOCIATION');
769 FND_MSG_PUB.ADD;
770 RAISE FND_API.G_EXC_ERROR;
771 WHEN OTHERS THEN
772 RAISE;
773 END Get_Association_Id;
774
775 -- ==========================================================================
776 -- PROCEDURE
777 -- Validate_Intercompany
778 --
779 -- DESCRIPTION
780 -- Validations for Intercompany
781 --
782 -- ARGUMENTS :
783 -- IN : p_subject_id
784 -- p_object_id
785 --
786 -- OUT : x_association_type_id
787 -- x_subject_parent_id
788 --
789 -- MODIFICATION HISTORY
790 --
791 -- ====================================================
792
793 PROCEDURE Validate_Intercompany (
794 p_subject_id IN NUMBER,
795 p_object_id IN NUMBER)
796 IS
797
798 le2_name VARCHAR(80);
799 le1_transacting VARCHAR(1);
800 le2_transacting VARCHAR(1);
801
802 BEGIN
803
804 SELECT le2.name, le1.transacting_entity_flag, le2.transacting_entity_flag
805 INTO le2_name, le1_transacting, le2_transacting
806 FROM XLE_ENTITY_PROFILES le1,
807 XLE_ENTITY_PROFILES le2
808 WHERE le1.legal_entity_id = p_subject_id
809 AND le2.legal_entity_id = p_object_id;
810
811 IF (le1_transacting <> 'Y' AND le2_transacting <> 'Y') THEN
812 FND_MESSAGE.SET_TOKEN('NAME', le2_name);
813 FND_MESSAGE.SET_NAME('XLE', 'XLE_REL_IC_ENABLED_WARN');
814 FND_MSG_PUB.ADD;
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 RAISE;
822 END Validate_Intercompany;
823
824
825 -- ==========================================================================
826 -- PROCEDURE
827 -- Validate_Create_Association
828 --
829 -- DESCRIPTION
830 -- Validations for Association Creation
831 --
832 -- ARGUMENTS :
833 -- IN : p_context
834 -- p_subject_type
835 -- p_subject_id
836 -- p_object_type
837 -- p_object_id
838 -- p_effective_from
839 -- p_assoc_information_context
840 -- p_assoc_information1
841 -- p_assoc_information2
842 -- p_assoc_information3
843 -- p_assoc_information4
844 -- p_assoc_information5
845 -- p_assoc_information6
846 -- p_assoc_information7
847 -- p_assoc_information8
848 -- p_assoc_information9
849 -- p_assoc_information10
850 -- p_assoc_information11
851 -- p_assoc_information12
852 -- p_assoc_information13
853 -- p_assoc_information14
854 -- p_assoc_information15
855 -- p_assoc_information16
856 -- p_assoc_information17
857 -- p_assoc_information18
858 -- p_assoc_information19
859 -- p_assoc_information20
860 --
864 -- MODIFICATION HISTORY
861 -- OUT : x_association_type_id
862 -- x_subject_parent_id
863 --
865 --
866 -- ===========================================================================
867
868
869 PROCEDURE Validate_Create_Association (
870 p_context IN VARCHAR2,
871 p_subject_type IN VARCHAR2,
872 p_subject_id IN NUMBER ,
873 p_object_type IN VARCHAR2,
874 p_object_id IN NUMBER ,
875 p_effective_from IN DATE ,
876 p_assoc_information_context IN VARCHAR2,
877 p_assoc_information1 IN VARCHAR2,
878 p_assoc_information2 IN VARCHAR2,
879 p_assoc_information3 IN VARCHAR2,
880 p_assoc_information4 IN VARCHAR2,
881 p_assoc_information5 IN VARCHAR2,
882 p_assoc_information6 IN VARCHAR2,
883 p_assoc_information7 IN VARCHAR2,
884 p_assoc_information8 IN VARCHAR2,
885 p_assoc_information9 IN VARCHAR2,
886 p_assoc_information10 IN VARCHAR2,
887 p_assoc_information11 IN VARCHAR2,
888 p_assoc_information12 IN VARCHAR2,
889 p_assoc_information13 IN VARCHAR2,
890 p_assoc_information14 IN VARCHAR2,
891 p_assoc_information15 IN VARCHAR2,
892 p_assoc_information16 IN VARCHAR2,
893 p_assoc_information17 IN VARCHAR2,
894 p_assoc_information18 IN VARCHAR2,
895 p_assoc_information19 IN VARCHAR2,
896 p_assoc_information20 IN VARCHAR2,
897 x_association_type_id OUT NOCOPY NUMBER ,
898 x_subject_parent_id OUT NOCOPY NUMBER )
899 IS
900 l_subject_type_id NUMBER;
901 l_object_type_id NUMBER;
902 l_subject_parent_id NUMBER;
903 l_association_type_id NUMBER;
904
905 BEGIN
906
907 -- **** Validates input parameters and finds the corresponding association type
908 Validate_Parameter_Combination (p_context, p_subject_type, p_subject_id, p_object_type, p_object_id, x_association_type_id);
909
910
911 -- **** Validates effective dates
912 Validate_Mandatory ('Effective From Date',p_effective_from);
913 Validate_Effective_Dates (x_association_type_id, p_effective_from);
914
915
916 -- **** Validates if the Association Type Cardinality is respected
917 Validate_Cardinality (x_association_type_id, p_subject_type, p_subject_id, p_object_type, p_object_id);
918
919
920 -- **** Validates Dates Overlap
921 Validate_Overlap_Dates (NULL, x_association_type_id, p_subject_id, p_object_id, p_effective_from);
922
923
924 -- **** Find the Parent Legal Entity if the subject of the Association is an Establishment
925 Get_Parent_ID (p_subject_type, p_subject_id, x_subject_parent_id);
926
927 EXCEPTION
928 WHEN OTHERS THEN
929 RAISE;
930 END Validate_Create_Association;
931
932
933 -- ==========================================================================
934 -- PROCEDURE
935 -- Validate_Update_Association
936 --
937 -- DESCRIPTION
938 -- Validations for Association Update
939 --
940 -- ARGUMENTS :
941 -- IN : p_context
942 -- p_subject_type
943 -- p_subject_id
944 -- p_object_type
945 -- p_object_id
946 -- p_effective_from
947 -- p_assoc_information_context
948 -- p_assoc_information1
949 -- p_assoc_information2
950 -- p_assoc_information3
951 -- p_assoc_information4
952 -- p_assoc_information5
953 -- p_assoc_information6
954 -- p_assoc_information7
955 -- p_assoc_information8
956 -- p_assoc_information9
957 -- p_assoc_information10
958 -- p_assoc_information11
959 -- p_assoc_information12
960 -- p_assoc_information13
961 -- p_assoc_information14
962 -- p_assoc_information15
963 -- p_assoc_information16
964 -- p_assoc_information17
965 -- p_assoc_information18
966 -- p_assoc_information19
967 -- p_assoc_information20
968 --
969 -- OUT : x_association_type_id
970 -- x_subject_parent_id
971 --
972 -- MODIFICATION HISTORY
973 --
974 -- ===========================================================================
975
976
977
978 PROCEDURE Validate_Update_Association (
979 p_association_id IN OUT NOCOPY NUMBER,
980 p_context IN VARCHAR2,
981 p_subject_type IN VARCHAR2,
982 p_subject_id IN NUMBER ,
983 p_object_type IN VARCHAR2,
984 p_object_id IN NUMBER ,
985 p_effective_from IN DATE ,
986 p_effective_to IN DATE ,
987 p_assoc_information_context IN VARCHAR2,
988 p_assoc_information1 IN VARCHAR2,
989 p_assoc_information2 IN VARCHAR2,
990 p_assoc_information3 IN VARCHAR2,
991 p_assoc_information4 IN VARCHAR2,
992 p_assoc_information5 IN VARCHAR2,
993 p_assoc_information6 IN VARCHAR2,
994 p_assoc_information7 IN VARCHAR2,
995 p_assoc_information8 IN VARCHAR2,
996 p_assoc_information9 IN VARCHAR2,
997 p_assoc_information10 IN VARCHAR2,
998 p_assoc_information11 IN VARCHAR2,
999 p_assoc_information12 IN VARCHAR2,
1000 p_assoc_information13 IN VARCHAR2,
1001 p_assoc_information14 IN VARCHAR2,
1002 p_assoc_information15 IN VARCHAR2,
1003 p_assoc_information16 IN VARCHAR2,
1007 p_assoc_information20 IN VARCHAR2)
1004 p_assoc_information17 IN VARCHAR2,
1005 p_assoc_information18 IN VARCHAR2,
1006 p_assoc_information19 IN VARCHAR2,
1008
1009 IS
1010 l_association_type_id NUMBER := NULL;
1011 l_effective_from DATE := NULL;
1012 l_effective_to DATE := NULL;
1013 l_object_id NUMBER := NULL;
1014 l_subject_id NUMBER := NULL;
1015
1016 BEGIN
1017
1018 IF (p_association_id IS NULL) OR (p_association_id = FND_API.G_MISS_NUM) THEN
1019 Validate_Parameter_Combination (
1020 p_context,
1021 p_subject_type,
1022 p_subject_id,
1023 p_object_type,
1024 p_object_id,
1025 l_association_type_id);
1026 Get_Association_Id (p_subject_id, p_object_id, l_association_type_id, p_association_id);
1027 l_subject_id := p_subject_id;
1028 l_object_id := p_object_id;
1029 ELSE
1030 Validate_Association_Id (p_association_id, l_association_type_id, l_subject_id, l_object_id);
1031 END IF;
1032
1033 -- **** Check Effective From Date is not set to NULL
1034
1035 IF (p_effective_from = FND_API.G_MISS_DATE) THEN
1036 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_MISSING_PARAM');
1037 FND_MSG_PUB.ADD;
1038 RAISE FND_API.G_EXC_ERROR;
1039 END IF;
1040
1041 -- **** Check that at least one new Effective Date (From or To) is provided or Developer Flexfields
1042
1043 IF (p_effective_from IS NULL) AND (p_effective_to = FND_API.G_MISS_DATE OR p_effective_to IS NULL) AND
1044 (p_assoc_information_context IS NULL) THEN
1045 FND_MESSAGE.SET_NAME ('XLE', 'XLE_ASSOC_MISSING_PARAM');
1046 FND_MSG_PUB.ADD;
1047 RAISE FND_API.G_EXC_ERROR;
1048 END IF;
1049
1050 IF (p_effective_to = FND_API.G_MISS_DATE) THEN
1051 l_effective_to := NULL;
1052 ELSE
1053 l_effective_to := p_effective_to;
1054 END IF;
1055
1056 IF (p_effective_from IS NOT NULL) THEN
1057 l_effective_from := p_effective_from;
1058 ELSE
1059 Get_Effective_From_Date (p_association_id, l_effective_from);
1060 END IF;
1061
1062 -- **** Validates effective dates
1063 Validate_Effective_Dates (l_association_type_id, l_effective_from, l_effective_to);
1064
1065
1066 -- **** Validates Dates Overlap
1067 Validate_Overlap_Dates (p_association_id, l_association_type_id, l_subject_id, l_object_id, l_effective_from, l_effective_to);
1068
1069
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 RAISE;
1074 END Validate_Update_Association;
1075
1076
1077 END XLE_ASSOC_VALIDATIONS_PVT;
1078