1 PACKAGE BODY IGS_OR_VAL_OUR AS
2 /* $Header: IGSOR11B.pls 115.7 2002/11/29 01:48:17 nsidana ship $ */
3
4 --
5 -- Validate the organisational IGS_PS_UNIT relationship.
6 FUNCTION orgp_val_our(
7 p_parent_org_unit_cd IN VARCHAR2 ,
8 p_parent_start_dt IN DATE ,
9 p_child_org_unit_cd IN VARCHAR2 ,
10 p_child_start_dt IN DATE ,
11 p_message_name OUT NOCOPY VARCHAR2 )
12 RETURN BOOLEAN AS
13 CURSOR c_ou (
14 cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
15 cp_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
16 SELECT org_unit_cd
17 FROM IGS_OR_UNIT
18 WHERE org_unit_cd = cp_org_unit_cd
19 AND start_dt = cp_start_dt;
20 --
21 CURSOR c_ou_os (
22 cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
23 cp_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
24 SELECT s_org_status
25 FROM IGS_OR_UNIT,
26 IGS_OR_STATUS
27 WHERE org_unit_cd = cp_org_unit_cd
28 AND start_dt = cp_start_dt
29 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
30 AND s_org_status = 'INACTIVE';
31 --
32 v_org_unit_exists BOOLEAN DEFAULT FALSE;
33 v_other_detail VARCHAR2(255);
34 --
35 -- Local function to perform recursive loop.
36 FUNCTION orgp_val_our_loop (
37 p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,
38 p_start_dt IN IGS_OR_UNIT.start_dt%TYPE)
39 RETURN BOOLEAN
40 IS
41 CURSOR c_our IS
42 SELECT parent_org_unit_cd,
43 parent_start_dt
44 FROM IGS_OR_UNIT_REL
45 WHERE child_org_unit_cd = p_org_unit_cd
46 AND child_start_dt = p_start_dt
47 AND logical_delete_dt IS NULL;
48 --
49 v_valid BOOLEAN DEFAULT TRUE;
50 BEGIN
51 FOR our IN c_our LOOP
52 IF (our.parent_org_unit_cd = p_child_org_unit_cd AND
53 our.parent_start_dt = p_child_start_dt) THEN
54 v_valid := FALSE;
55 EXIT;
56 END IF;
57 IF orgp_val_our_loop (our.parent_org_unit_cd,
58 our.parent_start_dt) = FALSE THEN
59 v_valid := FALSE;
60 EXIT;
61 END IF;
62 END LOOP;
63 RETURN v_valid;
64 EXCEPTION
65 WHEN OTHERS THEN
66 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
67 App_Exception.Raise_Exception ;
68 END orgp_val_our_loop;
69 --
70 BEGIN
71 p_message_name := NULL;
72 -- Validate the parent org IGS_PS_UNIT exists.
73 FOR ou IN c_ou (p_parent_org_unit_cd, p_parent_start_dt) LOOP
74 v_org_unit_exists := TRUE;
75 END LOOP;
76 IF v_org_unit_exists = FALSE THEN
77 p_message_name := 'IGS_OR_PARENT_UNIT_NOT_EXIST';
78 RETURN FALSE;
79 END IF;
80 -- Validate the child org IGS_PS_UNIT exists.
81 v_org_unit_exists := FALSE;
82 FOR ou IN c_ou (p_child_org_unit_cd, p_child_start_dt) LOOP
83 v_org_unit_exists := TRUE;
84 END LOOP;
85 IF v_org_unit_exists = FALSE THEN
86 p_message_name := 'IGS_OR_CHILD_UNIT_NOT_EXIST';
87 RETURN FALSE;
88 END IF;
89 -- Validate the system status for the parent organisational IGS_PS_UNIT.
90 --ssawhney 2040057. message changed from invalid value to something meaningful.
91 FOR ou_os IN c_ou_os (p_parent_org_unit_cd, p_parent_start_dt) LOOP
92 p_message_name := 'IGS_OR_INACTIVE_REL';
93 RETURN FALSE;
94 END LOOP;
95 -- Validate the system status for the child organisational IGS_PS_UNIT.
96 FOR ou_os IN c_ou_os (p_child_org_unit_cd, p_child_start_dt) LOOP
97 p_message_name := 'IGS_OR_INACTIVE_REL';
98 RETURN FALSE;
99 END LOOP;
100 -- Validate the child and parent organisational units are not the same.
101 IF p_parent_org_unit_cd = p_child_org_unit_cd AND
102 p_parent_start_dt = p_child_start_dt THEN
103 p_message_name := 'IGS_OR_SUP_SUB_REL_XS';
104 RETURN FALSE;
105 END IF;
106 -- Validate the organisational structure to ensure the child
107 -- organisational IGS_PS_UNIT does not appear further up the tree.
108 IF orgp_val_our_loop (p_parent_org_unit_cd,
109 p_parent_start_dt) = FALSE THEN
110 p_message_name := 'IGS_OR_SUP_SUB_REL_XS';
111 RETURN FALSE;
112 END IF;
113 RETURN TRUE;
114 EXCEPTION
115 WHEN OTHERS THEN
116 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
117 App_Exception.Raise_Exception ;
118 END orgp_val_our;
119 END IGS_OR_VAL_OUR;
[Home] [Help]