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