1 Package Body GMD_STATUS_CODE AS
2 /* $Header: GMDSTATB.pls 120.1 2005/07/20 23:41:23 kkillams noship $ */
3
4 /* Purpose: The package has code used in status management */
5 /* The package will usually be called from the Change Status form */
6 /* */
7 /* */
8 /* Check_Dependent_Status FUNCTION */
9 /* */
10 /* MODIFICATION HISTORY */
11 /* Person Date Comments */
12 /* --------- ------ ------------------------------------------ */
13 /* L.Jackson 15Mar2001 Start */
14
15 FUNCTION CHECK_DEPENDENT_STATUS
16 ( P_Entity_Type NUMBER,
17 P_Entity_id NUMBER,
18 P_Current_Status VARCHAR2,
19 P_To_Status VARCHAR2) RETURN BOOLEAN IS
20
21 /* Before the status of a higher entity can be changed, the status of dependent
22 * entities must be checked. Ex. Before a routing can be changed from new to
23 * approved for lab, all of its operations must be at least approved for lab, and
24 * none of the operations can be on hold or obsoleted.
25 */
26
27 /* This check only applies if entity TO_STATUS is between 200 and 499 or
28 * between 500 and 799. It does not matter what dependent entities' status
29 * are if higher entity is being changed to Frozen, On-Hold, Obsolete or New
30 * This check only applies to routings, recipes and validity rules.
31 *
32 * Set a local variable to the lowest approval allowed for dependent entities.
33 * ie. if routing is requesting lab approval, all operations must be at least 400,
34 * approved for lab.
35 *
36 * IF TO_STATUS between 200 and 799 THEN
37 * Start the boolean Dependent_Status_Ok as TRUE.
38 * IF the entity is Routings THEN
39 * select operation_status from gmd_operations_vl where oprn_id is in
40 * (select oprn_id from fm_rout_dtl where routing_id is current routing)
41 * IF any operation_status returned by select is On-hold, Obsolete or less than
42 * the TO_STATUS of the routing, set the boolean to FALSE.
43 * IF the entity is Recipes THEN
44 * select formula_status from fm_form_mst where formula_id is in
45 * (select formula_id from gmd_recipes where recipe_id is current recipe)
46 * select routing_status from fm_rout_hdr where routing_id is in
47 * (select routing_id from gmd_recipes where recipe_id is current recipe)
48 * IF either formula or routing status returned by select is on-hold, obsolete
49 * or less than the TO_STATUS of the recipe, set the boolean to FALSE.
50 * IF the entity is Validity Rules THEN
51 * select recipe_status from gmd_recipes where recipe_id is in
52 * (select recipe_id from gmd_recipe_validity_rules where
53 * recipe_validity_rule_id is current recipe_validity_rule_id)
54 * IF recipe_status returned by select is on-hold, obsolete or
55 * less than the TO_STATUS of the validity_rule, set the boolean to FALSE.
56 * END IF;
57 * Return the boolean Dependent_Status_Ok
58 */
59
60 TYPE Status_ref_cur IS REF CURSOR;
61 Oprn_Status_cur Status_ref_cur;
62 Formula_Status_cur Status_ref_cur;
63 Routing_Status_cur Status_ref_cur;
64 Recipe_Status_cur Status_ref_cur;
65
66 l_cur_dependent_status GMD_STATUS.STATUS_CODE%TYPE;
67 l_cur_routing_status GMD_STATUS.STATUS_CODE%TYPE;
68 l_lowest_allowed_status GMD_STATUS.STATUS_CODE%TYPE;
69 l_routing_defined FM_ROUT_HDR.ROUTING_ID%TYPE;
70 Dependent_Status_OK BOOLEAN := TRUE;
71
72 BEGIN
73 /* entity_types: 3=recipe, 4=routing, 5=validity rule*/
74 IF P_entity_type in (3, 4, 5)
75 AND (P_TO_STATUS between 200 and 499
76 OR
77 P_TO_STATUS between 500 and 799) THEN
78
79 IF P_TO_STATUS between 200 and 499 THEN
80 l_lowest_allowed_status := 400;
81 ELSE
82 /* P_TO_STATUS between 500 and 799) */
83 l_lowest_allowed_status := 700;
84 END IF;
85
86 /* *************** ROUTING ***************** */
87 IF P_entity_type = 4 THEN
88 /* if entity is routing, check operations */
89 OPEN Oprn_Status_cur FOR
90 Select o.operation_status
91 From gmd_operations_vl o, fm_rout_dtl r
92 Where o.oprn_id = r.oprn_id
93 and r.routing_id = P_Entity_id;
94
95 /* Fetch 1st record */
96 FETCH Oprn_Status_cur into l_cur_dependent_status;
97
98 WHILE Dependent_Status_OK and Oprn_Status_cur%FOUND LOOP
99 IF (l_cur_dependent_status between 800 and 899)
100 OR (l_cur_dependent_status between 1000 and 1099)
101 OR (l_cur_dependent_status < l_lowest_allowed_status)
102 THEN
103 Dependent_Status_OK := FALSE;
104 ELSE
105 FETCH Oprn_Status_cur into l_cur_dependent_status;
106 END IF;
107 END LOOP;
108
109 CLOSE Oprn_Status_cur;
110
111 /* *************** RECIPE ***************** */
112 ELSIF P_entity_type = 3 THEN
113 /* if entity is recipe, check formula and routing */
114 OPEN Formula_Status_cur FOR
115 Select f.formula_status, g.routing_id
116 From fm_form_mst f, gmd_recipes g
117 Where g.formula_id = f.formula_id
118 and g.recipe_id = P_Entity_id;
119 FETCH Formula_Status_cur into l_cur_dependent_status, l_routing_defined;
120 IF (l_cur_dependent_status between 800 and 899)
121 OR (l_cur_dependent_status between 1000 and 1099)
122 OR (l_cur_dependent_status < l_lowest_allowed_status)
123 THEN
124 Dependent_Status_OK := FALSE;
125 END IF;
126 CLOSE Formula_Status_cur;
127
128 /* If a routing was defined for recipe, and the formula status is ok,
129 * get the routing status */
130 IF Dependent_Status_OK AND l_routing_defined is not NULL THEN
131 OPEN Routing_Status_cur FOR
132 Select r.routing_status
133 From fm_rout_hdr r
134 Where r.routing_id = l_routing_defined;
135
136 FETCH Routing_Status_cur into l_cur_routing_status;
137 IF (l_cur_routing_status between 800 and 899)
138 OR (l_cur_routing_status between 1000 and 1099)
139 OR (l_cur_routing_status < l_lowest_allowed_status)
140 THEN
141 Dependent_Status_OK := FALSE;
142 END IF;
143 CLOSE Routing_Status_cur;
144 END IF;
145 /* end if routing is defined*/
146
147 /* *************** VALIDITY RULE ***************** */
148 ELSIF P_entity_type = 5 THEN
149 /* if entity is validity_rule, check recipe */
150 OPEN Recipe_Status_cur FOR
151 Select g.recipe_status
152 From gmd_recipe_validity_rules vr, gmd_recipes g
153 Where vr.recipe_id = g.recipe_id
154 and vr.recipe_validity_rule_id = P_Entity_id;
155 FETCH Recipe_Status_cur into l_cur_dependent_status;
156 IF (l_cur_dependent_status between 800 and 899)
157 OR (l_cur_dependent_status between 1000 and 1099)
158 OR (l_cur_dependent_status < l_lowest_allowed_status)
159 THEN
160 Dependent_Status_OK := FALSE;
161 END IF;
162 CLOSE Recipe_Status_cur;
163
164 END IF; /* end if this is routing, recipe or validity rule */
165 END IF; /* end if this is a TO_status which needs to be checked */
166
167 /* *************** VALIDITY RULE STATUS AGAINST RECIPE STATUS ***************** */
168 --KKILLAMS, Bug# 3283888: Validating the rule status aganist recipe status if recipe status is 'NEW'
169 --Set FALSE to "dependent_status_ok" variable if recipe status is NEW and validate rule status is FROZEN.
170 IF (p_entity_type =5) THEN
171 OPEN Recipe_Status_cur FOR
172 SELECT g.recipe_status FROM gmd_recipe_validity_rules vr, gmd_recipes_b g
173 WHERE vr.recipe_id = g.recipe_id
174 AND vr.recipe_validity_rule_id = p_Entity_id;
175 FETCH recipe_status_cur INTO l_cur_dependent_status;
176 CLOSE recipe_status_cur;
177 IF (l_cur_dependent_status BETWEEN 100 AND 199) AND
178 (p_to_status BETWEEN 900 AND 999) THEN
179 Dependent_Status_OK := FALSE;
180 END IF;
181 END IF;
182
183 RETURN Dependent_Status_OK;
184 END Check_Dependent_status;
185
186
187
188 /* Before the status of a lower entity can be changed, the status of parent
189 * entities must be checked. Ex. Before a routing can be obsoleted, a check should be
190 * made if the parent ie recipe is approved for general use or frozen or approved for lab use.
191 * if it is, the system should not allow the change of status to obsolete unless parent is
192 obsoleted. Returns FALSE if one exist otherwise returns TRUE */
193
194
195
196 FUNCTION Check_parent_status (pentity_name VARCHAR2,pentity_id NUMBER) RETURN BOOLEAN IS
197
198 CURSOR Cur_Check_Sts(l_recipe_id NUMBER) IS
199 SELECT COUNT(*)
200 FROM gmd_recipes r,gmd_recipe_validity_rules v
201 WHERE r.recipe_id = l_recipe_id
202 AND r.recipe_id = v.recipe_id
203 AND (r.recipe_status BETWEEN 700 AND 799
204 OR r.recipe_Status BETWEEN 400 AND 499
205 OR r.recipe_Status BETWEEN 900 AND 999)
206 AND EXISTS (select 1
207 from gme_batch_header
208 Where batch_status IN (1,2,3,-3)
209 AND recipe_validity_rule_id = v.recipe_validity_rule_id);
210
211 CURSOR Cur_Check_routing_recipe(l_routing_id NUMBER) IS
212 SELECT Count(*)
213 FROM gmd_recipes
214 WHERE routing_id = l_routing_id
215 AND (recipe_status between 700 and 799
216 OR recipe_status between 400 and 499
217 OR recipe_status between 900 and 999) ;
218
219
220 CURSOR Cur_Check_oprn_recp(l_oprn_id NUMBER) IS
221 SELECT count(*)
222 FROM fm_rout_hdr h,fm_rout_dtl d
223 WHERE h.routing_id = d.routing_id
224 AND d.oprn_id = l_oprn_id
225 AND (h.routing_status between 700 and 799
226 OR h.routing_Status between 400 and 499
227 OR h.routing_Status between 900 and 999);
228
229 CURSOR Cur_Check_oprn_batch_steps(l_oprn_id NUMBER) IS
230 SELECT count(*)
231 FROM gmd_operations_vl o,gme_batch_steps s
232 WHERE o.oprn_id = l_oprn_id
233 AND o.oprn_id = s.oprn_id
234 AND (o.operation_status BETWEEN 400 and 499
235 OR o.operation_status BETWEEN 700 AND 799
236 OR o.operation_status BETWEEN 900 AND 999);
237
238
239 CURSOR Cur_check_form_recipe(l_formula_id NUMBER) IS
240 SELECT count(*)
241 FROM gmd_recipes
242 WHERE formula_id = l_formula_id
243 AND (recipe_status between 700 and 799
244 OR recipe_status between 400 and 499
245 OR recipe_status between 900 and 999);
246
247 CURSOR Cur_check_valrle_batch(l_validity_rule_id NUMBER) IS
248 SELECT count(*)
249 FROM gme_batch_header
250 WHERE recipe_validity_rule_id = l_validity_rule_id
251 AND batch_status IN (1,2,3,-3);
252
253 l_count NUMBER;
254
255 BEGIN
256 IF (pentity_name = 'RECIPE') THEN
257 OPEN Cur_Check_sts(pentity_id);
258 FETCH Cur_Check_sts INTO l_count;
259 CLOSE Cur_Check_sts;
260 IF (l_count > 0) THEN
261 RETURN(FALSE);
262 ELSE
263 RETURN(TRUE);
264 END IF;
265 ELSIF(pentity_name = 'ROUTING') THEN
266
267 OPEN Cur_Check_routing_recipe(pentity_id);
268 FETCH Cur_Check_routing_recipe INTO l_count;
269 CLOSE Cur_Check_routing_recipe;
270
271 IF (l_count > 0) THEN -- This routing is used in recipes which are approved for general use.
272 RETURN(FALSE);
273 ELSE
274 RETURN(TRUE);
275 END IF;
276
277 ELSIF(pentity_name = 'OPERATION') THEN
278
279 OPEN Cur_Check_oprn_recp(pentity_id);
280 FETCH Cur_Check_oprn_recp INTO l_count;
281 CLOSE Cur_Check_oprn_recp;
282 IF (l_count > 0) THEN
283 RETURN(FALSE);
284 ELSE
285 RETURN(TRUE);
286 END IF;
287
288 OPEN Cur_Check_oprn_batch_steps(pentity_id);
289 FETCH Cur_check_oprn_batch_steps INTO l_count;
290 CLOSE Cur_check_oprn_batch_steps;
291
292 IF (l_count > 0) THEN
293 RETURN(FALSE);
294 ELSE
295 RETURN(TRUE);
296 END IF;
297 ELSIF(pentity_name = 'FORMULA') THEN
298 OPEN Cur_check_form_recipe(pentity_id);
299 FETCH Cur_check_form_recipe INTO l_count;
300 CLOSE Cur_check_form_recipe;
301 IF (l_count > 0) THEN
302 RETURN(FALSE);
303 ELSE
304 RETURN(TRUE);
305 END IF;
306 ELSIF(pentity_name like 'VALIDITY%') THEN
307 OPEN Cur_check_valrle_batch(pentity_id);
308 FETCH Cur_check_valrle_batch INTO l_count;
309 CLOSE Cur_check_valrle_batch;
310 IF (l_count > 0) THEN
311 RETURN(FALSE);
312 ELSE
313 RETURN(TRUE);
314 END IF;
315 ELSE --bug 4479101
316 RETURN(TRUE);
317 END IF;
318 END check_parent_status;
319
320 FUNCTION GET_REWORK_STATUS(p_from_status VARCHAR2,
321 p_to_status VARCHAR2)
322 RETURN VARCHAR2
323 IS
324 CURSOR Cur_get_rework IS
325 SELECT rework_status
326 FROM GMD_STATUS_NEXT
327 WHERE current_status = p_from_status
328 AND target_status = p_to_status
329 AND pending_status IS NOT NULL;
330
331 l_rework_status VARCHAR2(30);
332 BEGIN
333 OPEN Cur_get_rework;
334 FETCH Cur_get_rework INTO l_rework_status;
335 CLOSE Cur_get_rework;
336 RETURN (l_rework_status);
337
338 END get_rework_status;
339
340
341 FUNCTION GET_PENDING_STATUS(p_from_status VARCHAR2,
342 p_to_status VARCHAR2)
343 RETURN VARCHAR2
344 IS
345 CURSOR Cur_get_pending IS
346 SELECT pending_status
347 FROM GMD_STATUS_NEXT
348 WHERE current_status = p_from_status
349 AND target_status = p_to_status;
350
351 l_pending_status VARCHAR2(30);
352 BEGIN
353 OPEN Cur_get_pending;
354 FETCH Cur_get_pending INTO l_pending_status;
355 CLOSE Cur_get_pending;
356 RETURN (l_pending_status);
357
358 END get_pending_status;
359
360 END; -- Package Body GMD_STATUS_CODE
[Home] [Help]