1 PACKAGE BODY IGS_CA_VAL_DAIP AS
2 /* $Header: IGSCA10B.pls 115.3 2002/11/28 22:58:27 nsidana ship $ */
3 -- Validate dt alias instance pair related value.
4 FUNCTION calp_val_daip_value(
5 p_dt_alias IN VARCHAR2 ,
6 p_dai_sequence_number IN NUMBER ,
7 p_cal_type IN VARCHAR2 ,
8 p_ci_sequence_number IN NUMBER ,
9 p_related_dt_alias IN VARCHAR2 ,
10 p_related_dai_sequence_number IN NUMBER ,
11 p_related_cal_type IN VARCHAR2 ,
12 p_related_ci_sequence_number IN NUMBER ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- calp_val_daip_value
17 -- This module validate IGS_AD_PS_APLINSTUNT unit version.
18 DECLARE
19 v_original_date IGS_CA_DA_INST_V.alias_val%TYPE;
20 v_related_date IGS_CA_DA_INST_V.alias_val%TYPE;
21 CURSOR c_daiv_org IS
22 SELECT alias_val
23 FROM IGS_CA_DA_INST_V daiv
24 WHERE daiv.dt_alias = p_dt_alias AND
25 daiv.sequence_number = p_dai_sequence_number AND
26 daiv.cal_type = p_cal_type AND
27 daiv.ci_sequence_number = p_ci_sequence_number;
28 CURSOR c_daiv_new IS
29 SELECT alias_val
30 FROM IGS_CA_DA_INST_V daiv
31 WHERE daiv.dt_alias = p_related_dt_alias AND
32 daiv.sequence_number = p_related_dai_sequence_number AND
33 daiv.cal_type = p_related_cal_type AND
34 daiv.ci_sequence_number = p_related_ci_sequence_number;
35 BEGIN
36 -- Set the default message number
37 p_message_name := NULL;
38 -- 1. Check parameters :
39 IF p_dt_alias IS NULL OR
40 p_dai_sequence_number IS NULL OR
41 p_cal_type IS NULL OR
42 p_ci_sequence_number IS NULL OR
43 p_related_dt_alias IS NULL OR p_related_dai_sequence_number IS NULL OR
44 p_related_cal_type IS NULL OR p_related_ci_sequence_number IS NULL THEN
45 p_message_name := NULL;
46 RETURN TRUE;
47 END IF;
48 -- 2. Determine the value of each date alias instance in the record
49 OPEN c_daiv_org;
50 FETCH c_daiv_org INTO v_original_date;
51 CLOSE c_daiv_org;
52 OPEN c_daiv_new;
53 FETCH c_daiv_new INTO v_related_date;
54 CLOSE c_daiv_new;
55 IF v_related_date <= v_original_date THEN
56 p_message_name := 'IGS_GE_REL_DT_GT_DT';
57 RETURN FALSE;
58 END IF;
59 -- 3. Return no error:
60 RETURN TRUE;
61 END;
62 END calp_val_daip_value;
63 --
64 -- Validate dt alias instance pair calendar type.
65 FUNCTION calp_val_daip_ct(
66 p_cal_type IN VARCHAR2 ,
67 p_related_cal_type IN VARCHAR2 ,
68 p_message_name OUT NOCOPY VARCHAR2 )
69 RETURN BOOLEAN AS
70 gv_other_detail VARCHAR2(255);
71 BEGIN -- calp_val_daip_ct
72 -- Validate that IGS_CA_DA_INST_PAIR.cal_type and
73 -- IGS_CA_DA_INST_PAIR.related_cal_type are the same.
74 DECLARE
75 BEGIN
76 -- 1. Check parameters
77 IF (p_cal_type IS NULL OR
78 p_related_cal_type IS NULL) THEN
79 p_message_name := NULL;
80 RETURN TRUE;
81 END IF;
82 -- 2. Check that calendar types are the same
83 IF (p_cal_type <> p_related_cal_type) THEN
84 p_message_name := 'IGS_CA_CALTYPE_SAME_DTALIAS';
85 RETURN FALSE;
86 END IF;
87 -- 3. Return no error
88 p_message_name := NULL;
89 RETURN TRUE;
90 END;
91 END calp_val_daip_ct;
92 --
93 -- Validate dt alias instance pair values are different.
94 FUNCTION calp_val_daip_dai(
95 p_dt_alias IN VARCHAR2 ,
96 p_dai_sequence_number IN NUMBER ,
97 p_cal_type IN VARCHAR2 ,
98 p_ci_sequence_number IN NUMBER ,
99 p_related_dt_alias IN VARCHAR2 ,
100 p_related_dai_sequence_number IN NUMBER ,
101 p_related_cal_type IN VARCHAR2 ,
102 p_related_ci_sequence_number IN NUMBER ,
103 p_message_name OUT NOCOPY VARCHAR2 )
104 RETURN BOOLEAN AS
105 gv_other_detail VARCHAR2(255);
106 BEGIN -- calp_val_daip_dai
107 -- Validate that the related IGS_CA_DA_INST is different to the parent
108 -- IGS_CA_DA_INST
109 DECLARE
110 BEGIN
111 -- Set the default message number
112 p_message_name := NULL;
113 -- 1. Check parameters :
114 IF p_dt_alias IS NULL OR
115 p_dai_sequence_number IS NULL OR
116 p_cal_type IS NULL OR
117 p_ci_sequence_number IS NULL OR
118 p_related_dt_alias IS NULL OR
119 p_related_dai_sequence_number IS NULL OR
120 p_related_cal_type IS NULL OR
121 p_related_ci_sequence_number IS NULL THEN
122 RETURN TRUE;
123 END IF;
124 -- 2. Determine if the dt_alias_instances are the same.
125 IF p_dt_alias = p_related_dt_alias AND
126 p_dai_sequence_number = p_related_dai_sequence_number AND
127 p_cal_type = p_related_cal_type AND
128 p_ci_sequence_number = p_related_ci_sequence_number THEN
129 p_message_name := 'IGS_CA_RELATED_DTALIAS_DIFF';
130 RETURN FALSE;
131 END IF;
132 -- 3. Return no error:
133 RETURN TRUE;
134 END;
135 END calp_val_daip_dai;
136 --
137 -- Validate only one date alias instance pair exists.
138 FUNCTION calp_val_daip_unique(
139 p_dt_alias IN VARCHAR2 ,
140 p_dai_sequence_number IN NUMBER ,
141 p_cal_type IN VARCHAR2 ,
142 p_ci_sequence_number IN NUMBER ,
143 p_related_dt_alias IN VARCHAR2 ,
144 p_related_dai_sequence_number IN NUMBER ,
145 p_related_cal_type IN VARCHAR2 ,
146 p_related_ci_sequence_number IN NUMBER ,
147 p_message_name OUT NOCOPY VARCHAR2 )
148 RETURN BOOLEAN AS
149 gv_other_detail VARCHAR2(255);
150 BEGIN -- calp_val_daip_unique
151 -- Validate that only one IGS_CA_DA_INST_PAIR record is defined for each
152 -- IGS_CA_DA_INST.
153 DECLARE
154 CURSOR c_daip IS
155 SELECT 'x'
156 FROM IGS_CA_DA_INST_PAIR daip
157 WHERE daip.dt_alias = p_dt_alias AND
158 daip.dai_sequence_number = p_dai_sequence_number AND
159 daip.cal_type = p_cal_type AND
160 daip.ci_sequence_number = p_ci_sequence_number AND
161 (daip.related_dt_alias <> p_related_dt_alias OR
162 daip.related_dai_sequence_number <> p_related_dai_sequence_number OR
163 daip.related_cal_type <> p_related_cal_type OR
164 daip.related_ci_sequence_number <> p_related_ci_sequence_number);
165 v_rec VARCHAR2(1) := NULL;
166 BEGIN
167 -- 1. Check parameters :
168 IF p_dt_alias IS NULL OR
169 p_dai_sequence_number IS NULL OR
170 p_cal_type IS NULL OR
171 p_ci_sequence_number IS NULL OR
172 p_related_dt_alias IS NULL OR
173 p_related_dai_sequence_number IS NULL OR
174 p_related_cal_type IS NULL OR
175 p_related_ci_sequence_number IS NULL THEN
176 p_message_name := NULL;
177 RETURN TRUE;
178 END IF;
179 -- 2. Determine if a record already exists (other than the current record)
180 OPEN c_daip;
181 FETCH c_daip INTO v_rec;
182 IF (c_daip%FOUND) THEN
183 CLOSE c_daip;
184 p_message_name := 'IGS_CA_ONE_DTALIAS_DEFIINED';
185 RETURN FALSE;
186 END IF;
187 CLOSE c_daip;
188 -- 3. Return no error:
189 p_message_name := NULL;
190 RETURN TRUE;
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF (c_daip%ISOPEN) THEN
194 CLOSE c_daip;
195 END IF;
196 END;
197
198 END calp_val_daip_unique;
199 END IGS_CA_VAL_DAIP;