[Home] [Help]
PACKAGE BODY: APPS.OE_HEADER_ADJ_PCFWK
Source
1 PACKAGE BODY OE_HEADER_ADJ_PCFWK AS
2 /* $Header: OEXKHADB.pls 120.0 2005/05/31 22:35:46 appldev noship $ */
3
4 -- Globals
5 -------------------------------------------
6 g_application_id constant number := 660;
7 g_entity_id constant number := 6;
8 g_entity_short_name constant varchar2(15) := 'HEADER_ADJ';
9 -------------------------------------------
10 PROCEDURE Validate_Constraint
11 (
12 p_constraint_id in number
13 ,x_condition_count out nocopy number
14
15 ,x_valid_condition_group out nocopy number
16
17 ,x_result out nocopy number
18
19 )
20 IS
21
22 --Cursors
23 CURSOR C_R
24 IS SELECT
25 condition_id,
26 group_number,
27 modifier_flag,
28 validation_application_id,
29 validation_entity_short_name,
30 validation_tmplt_short_name,
31 record_set_short_name,
32 scope_op,
33 validation_pkg,
34 validation_proc
35 FROM oe_pc_conditions_v
36 WHERE constraint_id = p_constraint_id
37 ORDER BY group_number;
38
39
40 TYPE ConstraintRule_Rec_Type IS RECORD
41 (
42 condition_id number,
43 group_number number,
44 modifier_flag varchar2(1),
45 validation_application_id number,
46 validation_entity_short_name varchar2(15),
47 validation_tmplt_short_name varchar2(8),
48 record_set_short_name varchar2(8),
49 scope_op varchar2(3),
50 validation_pkg varchar2(30),
51 validation_proc varchar2(30)
52 );
53
54 l_constraintRuleRec ConstraintRule_Rec_Type;
55 l_dsqlCursor integer;
56 l_dynamicSqlString varchar2(2000);
57 l_rule_count number;
58 l_ConstrainedStatus number;
59 l_dummy integer;
60 i number;
61 l_tempResult boolean;
62 l_result_01 number;
63 l_currGrpNumber number;
64 l_currGrpResult boolean;
65 --
66 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
67 --
68 BEGIN
69
70 l_ConstrainedStatus := OE_PC_GLOBALS.NO;
71 l_rule_count := 0;
72 i := 0;
73 l_currGrpNumber := -1;
74 l_currGrpResult := FALSE;
75
76 OPEN C_R;
77 LOOP -- validatate constraining conditions
78 -- fetch all the validation procedure_names assigned to the constraint and
79 -- build the dynamic sql string
80 FETCH C_R into
81 l_constraintRuleRec.condition_id,
82 l_constraintRuleRec.group_number,
83 l_constraintRuleRec.modifier_flag,
84 l_constraintRuleRec.validation_application_id,
85 l_constraintRuleRec.validation_entity_short_name,
86 l_constraintRuleRec.validation_tmplt_short_name,
87 l_constraintRuleRec.record_set_short_name,
88 l_constraintRuleRec.scope_op,
89 l_constraintRuleRec.validation_pkg,
90 l_constraintRuleRec.validation_proc;
91
92 -- EXIT from loop
93 IF (C_R%NOTFOUND) THEN
94 IF (l_currGrpNumber <> -1 AND l_currGrpResult = TRUE) THEN
95 l_ConstrainedStatus := OE_PC_GLOBALS.YES;
96 END IF;
97 EXIT; -- exit the loop
98 END IF;
99
100 IF (l_currGrpNumber <> l_constraintRuleRec.group_number) THEN
101
102 -- we are entering the new group of conditions..
103 -- groups are ORd together, so if the previous group was evaluated
107 EXIT; -- exit the loop
104 -- to TRUE (OE_PC_GLOBALS.YES) then no need to evaluvate this group.
105 IF (l_currGrpResult = TRUE) THEN
106 l_ConstrainedStatus := OE_PC_GLOBALS.YES;
108 END IF;
109
110 -- previous group did not evaluvate to TRUE, so lets pursue this new group
111 l_currGrpNumber := l_constraintRuleRec.group_number;
112 l_currGrpResult := FALSE;
113 i := 0;
114 END IF;
115 -- we have a got a record, increment the count by 1
116 l_rule_count := l_rule_count+1;
117
118 -- pkg.function(p1, p2, ...)
119 l_dynamicSqlString := ' begin ';
120 l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_pkg ||'.';
121 l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_proc;
122
123 -- IN Parameters
124 l_dynamicSqlString := l_dynamicSqlString || '( ';
125 l_dynamicSqlString := l_dynamicSqlString || ':t_application_id, ';
126 l_dynamicSqlString := l_dynamicSqlString || ':t_entity_short_name, ';
127 l_dynamicSqlString := l_dynamicSqlString || ':t_validation_entity_short_name, ';
128 l_dynamicSqlString := l_dynamicSqlString || ':t_validation_tmplt_short_name, ';
129 l_dynamicSqlString := l_dynamicSqlString || ':t_record_set_short_name, ';
130 l_dynamicSqlString := l_dynamicSqlString || ':t_scope, ';
131
132 -- OUT Parameters
133 l_dynamicSqlString := l_dynamicSqlString || ':t_result );';
134 l_dynamicSqlString := l_dynamicSqlString || ' end; ';
135
136
137 -- EXECUTE THE DYNAMIC SQL
138 EXECUTE IMMEDIATE l_dynamicSqlString USING IN g_application_id,
139 IN g_entity_short_name,
140 IN l_constraintRuleRec.validation_entity_short_name,
141 IN l_constraintRuleRec.validation_tmplt_short_name,
142 IN l_constraintRuleRec.record_set_short_name,
143 IN l_constraintRuleRec.scope_op,
144 OUT l_result_01;
145
146
147 IF (l_result_01 = 0) THEN
148 l_tempResult := FALSE;
149 ELSE
150 l_tempResult := TRUE;
151 END IF;
152 -- apply the modifier on the result
153 if(l_constraintRuleRec.modifier_flag = OE_PC_GLOBALS.YES_FLAG) then
154 l_tempResult := NOT(l_tempResult);
155 end if;
156
157 IF (i = 0) THEN
158 l_currGrpResult := l_tempResult;
159 ELSE
160 l_currGrpResult := l_currGrpResult AND l_tempResult;
161 END IF;
162
163 -- increment the index
164 i := i+1;
165 END LOOP; -- end validatate validators
166 CLOSE C_R;
167 -- did we validate any constraint rules?. if there is none then the
168 -- constraint is valid and we will return YES
169 IF (l_rule_count = 0) THEN
170 x_condition_count := 0;
171 x_valid_condition_group := -1;
172 x_result := OE_PC_GLOBALS.YES;
173 ELSE
174 x_condition_count := l_rule_count;
175 x_valid_condition_group := l_currGrpNumber;
176 x_result := l_ConstrainedStatus;
177 END IF;
178 -------------------------------------------
179 EXCEPTION
180 WHEN OTHERS THEN
181 x_result := OE_PC_GLOBALS.ERROR;
182 END Validate_Constraint;
183 -------------------------------------------
184 -------------------------------------------
185 FUNCTION Is_Op_Constrained
186 (
187 p_responsibility_id in number
188 ,p_operation in varchar2
189 ,p_column_name in varchar2 default NULL
190 ,p_record in OE_AK_HEADER_PRCADJS_V%ROWTYPE
191 ,p_check_all_cols_constraint in varchar2 default 'Y'
192 ,p_is_caller_defaulting in varchar2 default 'N'
193 ,x_constraint_id out nocopy number
194
195 ,x_constraining_conditions_grp out nocopy number
196
197 ,x_on_operation_action out nocopy number
198
199 )
200 RETURN NUMBER
201
202 IS
203
204 --Cursors
205 -------------------------------------------
206 CURSOR C_C
207 IS
208 SELECT DISTINCT
209 c.constraint_id, c.entity_id
210 ,c.on_operation_action
211 FROM oe_pc_constraints c,
212 oe_pc_assignments a
213 WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)
214 AND a.constraint_id = c.constraint_id
215 AND c.entity_id = G_ENTITY_ID
216 AND c.constrained_operation = p_operation
217 -- if caller is defaulting then DO NOT CHECK those constraints
218 -- that have honored_by_def_flag = 'N'
219 AND decode(honored_by_def_flag,'N',decode(p_is_caller_defaulting,'Y','N','Y'),
220 nvl(honored_by_def_flag,'Y')) = 'Y'
221 AND decode(c.column_name, '',decode(p_check_all_cols_constraint,'Y',
222 nvl(p_column_name,'#NULL'),'#NULL'),c.column_name) = nvl(p_column_name,'#NULL')
223 AND NOT EXISTS (
224 SELECT 'EXISTS'
225 FROM OE_PC_EXCLUSIONS e
226 WHERE e.responsibility_id = p_responsibility_id
227 AND e.assignment_id = a.assignment_id
228 );
229 -- Cursor to select all update constraints that are applicable to insert
230 -- operations as well.
231 CURSOR C_CREATE_OP
232 IS
233 SELECT DISTINCT
234 c.constraint_id, c.entity_id
235 ,c.on_operation_action
236 FROM oe_pc_constraints c,
237 oe_pc_assignments a
238 WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)
239 AND a.constraint_id = c.constraint_id
240 AND c.entity_id = G_ENTITY_ID
241 AND ( ( c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
242 and p_column_name is null )
243 OR ( c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
244 AND c.check_on_insert_flag = 'Y'
245 AND nvl(c.column_name, '#NULL') = NVL(p_column_name,'#NULL') )
246 )
247 -- if caller is defaulting then DO NOT CHECK those constraints
248 -- that have honored_by_def_flag = 'N'
249 AND decode(honored_by_def_flag,'N',decode(p_is_caller_defaulting,'Y','N','Y'),
250 nvl(honored_by_def_flag,'Y')) = 'Y'
251 AND NOT EXISTS (
252 SELECT 'EXISTS'
253 FROM OE_PC_EXCLUSIONS e
254 WHERE e.responsibility_id = p_responsibility_id
255 AND e.assignment_id = a.assignment_id
256 );
257 --Local Variables
258 -------------------------------------------
259 l_validation_result number;
260 l_condition_count number;
261 l_valid_condition_group number;
262 BEGIN
263 g_record := p_record;
264 l_validation_result := OE_PC_GLOBALS.NO;
265 IF p_operation = OE_PC_GLOBALS.CREATE_OP THEN
266
267 FOR c_rec in C_CREATE_OP LOOP
268 Validate_Constraint (
269 p_constraint_id => c_rec.constraint_id
270 ,x_condition_count => l_condition_count
271 ,x_valid_condition_group => l_valid_condition_group
272 ,x_result => l_validation_result
273 );
274 IF (l_condition_count = 0 OR l_validation_result = OE_PC_GLOBALS.YES) then
275 x_constraint_id := c_rec.constraint_id;
276 x_on_operation_action := c_rec.on_operation_action;
277 x_constraining_conditions_grp := l_valid_condition_group;
278
279 EXIT;
280
281 end if;
282 end loop;
283
284 else
285
286 FOR c_rec in C_C LOOP
287 Validate_Constraint (
288 p_constraint_id => c_rec.constraint_id
289 ,x_condition_count => l_condition_count
290 ,x_valid_condition_group => l_valid_condition_group
291 ,x_result => l_validation_result
292 );
293 IF (l_condition_count = 0 OR l_validation_result = OE_PC_GLOBALS.YES) then
294 x_constraint_id := c_rec.constraint_id;
295 x_on_operation_action := c_rec.on_operation_action;
296 x_constraining_conditions_grp := l_valid_condition_group;
297 EXIT;
298 END IF;
299 END LOOP;
300 end if;
301
302 return l_validation_result;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 RETURN OE_PC_GLOBALS.ERROR;
307 END Is_Op_Constrained;
308 -------------------------------------------
309 END OE_HEADER_ADJ_PCFWK;