[Home] [Help]
PACKAGE BODY: APPS.GMD_MBR_UTIL_PKG
Source
1 PACKAGE BODY GMD_MBR_UTIL_PKG AS
2 /* $Header: GMDMBRUB.pls 120.3 2006/10/02 21:15:41 srpuri noship $ */
3
4 PROCEDURE GET_ACTIVITY_FACTOR(p_RECIPE_ID number,
5 p_ROUTINGSTEP_ID number,
6 p_OPRN_LINE_ID number,
7 p_DOCUMENT_id VARCHAR2,
8 p_Default_factor number,
9 x_org_specific_factor OUT NOCOPY NUMBER) AS
10 CURSOR CUR_GET_ACTIVITY_FACTOR IS
11 SELECT activity_factor
12 FROM gmd_recipe_orgn_activities
13 WHERE RECIPE_ID = P_RECIPE_ID
14 AND ROUTINGSTEP_ID = P_ROUTINGSTEP_ID
15 AND OPRN_LINE_ID = P_OPRN_LINE_ID
16 AND ORGANIZATION_ID = substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1,
17 decode(instrb(P_DOCUMENT_ID,'-',1,2),0,
18 length(substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1)),
19 instrb(P_DOCUMENT_ID,'-',1,2)-(instrb(P_DOCUMENT_ID,'-',1,1)+1)));
20 BEGIN
21 OPEN CUR_GET_ACTIVITY_FACTOR;
22 FETCH CUR_GET_ACTIVITY_FACTOR INTO x_org_specific_factor;
23 IF CUR_GET_ACTIVITY_FACTOR%NOTFOUND THEN
24 x_org_specific_factor := p_Default_factor;
25 END IF;
26 CLOSE CUR_GET_ACTIVITY_FACTOR;
27 END GET_ACTIVITY_FACTOR;
28
29
30 PROCEDURE GET_RECIPE_RSRC_ORGN_OVERRIDES(
31 P_RECIPE_ID number
32 ,P_ROUTINGSTEP_ID number
33 ,P_OPRN_LINE_ID number
34 ,P_RESOURCES VARCHAR2
35 ,P_PROCESS_UOM VARCHAR2
36 ,P_USAGE_UM VARCHAR2
37 ,P_RESOURCE_USAGE number
38 ,P_PROCESS_QTY number
39 ,P_DOCUMENT_ID VARCHAR2
40 ,P_MIN_CAPACITY number
41 ,P_MAX_CAPACITY number
42 ,P_CAPACITY_UM VARCHAR2
43 ,x_PROCESS_UOM OUT NOCOPY VARCHAR2
44 ,x_USAGE_UM OUT NOCOPY VARCHAR2
45 ,x_RESOURCE_USAGE OUT NOCOPY VARCHAR2
46 ,x_PROCESS_QTY OUT NOCOPY number
47 ,x_MIN_CAPACITY OUT NOCOPY number
48 ,x_MAX_CAPACITY OUT NOCOPY number
49 ,x_CAPACITY_UM OUT NOCOPY VARCHAR2) IS
50 CURSOR CUR_GET_RSRC_OVERRIDES IS
51 SELECT MIN_CAPACITY
52 ,MAX_CAPACITY
53 ,PROCESS_UM
54 ,USAGE_UOM
55 ,RESOURCE_USAGE
56 ,PROCESS_QTY
57 FROM gmd_recipe_orgn_resources
58 WHERE
59 ORGANIZATION_ID = substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1,
60 decode(instrb(P_DOCUMENT_ID,'-',1,2),0,
61 length(substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1)),
62 instrb(P_DOCUMENT_ID,'-',1,2)-(instrb(P_DOCUMENT_ID,'-',1,1)+1)))
63 AND RECIPE_ID = P_RECIPE_ID
64 AND ROUTINGSTEP_ID = P_ROUTINGSTEP_ID
65 AND OPRN_LINE_ID = P_OPRN_LINE_ID
66 AND RESOURCES = P_RESOURCES ;
67
68 CURSOR CUR_GET_RSRC_ORG_DEF IS
69 SELECT
70 MIN_CAPACITY
71 ,MAX_CAPACITY
72 ,CAPACITY_UM
73 FROM CR_RSRC_DTL
74 WHERE RESOURCES = P_RESOURCES
75 AND ORGANIZATION_ID = substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1,
76 decode(instrb(P_DOCUMENT_ID,'-',1,2),0,
77 length(substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1)),
78 instrb(P_DOCUMENT_ID,'-',1,2)-(instrb(P_DOCUMENT_ID,'-',1,1)+1)));
79
80 BEGIN
81 OPEN CUR_GET_RSRC_OVERRIDES;
82 FETCH CUR_GET_RSRC_OVERRIDES into
83 x_MIN_CAPACITY
84 ,x_MAX_CAPACITY
85 ,x_PROCESS_UOM
86 ,x_USAGE_UM
87 ,x_RESOURCE_USAGE
88 ,x_PROCESS_QTY;
89 IF CUR_GET_RSRC_OVERRIDES%NOTFOUND THEN
90 OPEN CUR_GET_RSRC_ORG_DEF;
91 FETCH CUR_GET_RSRC_ORG_DEF INTO x_MIN_CAPACITY,x_MAX_CAPACITY,x_CAPACITY_UM;
92 IF CUR_GET_RSRC_ORG_DEF%NOTFOUND THEN
93 x_MIN_CAPACITY := P_MIN_CAPACITY;
94 x_MAX_CAPACITY := P_MAX_CAPACITY;
95 x_PROCESS_UOM := P_PROCESS_UOM;
96 x_USAGE_UM := P_USAGE_UM;
97 x_RESOURCE_USAGE := P_RESOURCE_USAGE;
98 x_PROCESS_QTY := P_PROCESS_QTY;
99 x_CAPACITY_UM := P_CAPACITY_UM;
100 ELSE
104 END IF;
101 x_PROCESS_UOM := P_PROCESS_UOM;
102 x_USAGE_UM := P_USAGE_UM;
103 x_RESOURCE_USAGE := P_RESOURCE_USAGE;
105 CLOSE CUR_GET_RSRC_ORG_DEF;
106 ELSE
107 x_CAPACITY_UM := P_CAPACITY_UM;
108 END IF;
109 CLOSE CUR_GET_RSRC_OVERRIDES;
110 END GET_RECIPE_RSRC_ORGN_OVERRIDES;
111
112 FUNCTION GET_SAMPLING_PLAN(P_INVENTORY_ITEM_ID IN NUMBER
113 ,P_GRADE_CODE IN VARCHAR2
114 ,P_ORGANIZATION_ID IN VARCHAR2
115 ,P_BATCH_ID IN VARCHAR2
116 ,P_RECIPE_ID IN VARCHAR2
117 ,P_RECIPE_NO IN VARCHAR2
118 ,P_RECIPE_VERSION IN VARCHAR2
119 ,P_FORMULA_ID IN VARCHAR2
120 ,P_FORMULALINE_ID IN VARCHAR2
121 ,P_FORMULA_NO IN VARCHAR2
122 ,P_FORMULA_VERS IN VARCHAR2
123 ,P_ROUTING_ID IN VARCHAR2
124 ,P_ROUTING_NO IN VARCHAR2
125 ,P_ROUTING_VERS IN VARCHAR2
126 ,P_STEP_ID IN VARCHAR2
127 ,P_STEP_NO IN VARCHAR2
128 ,P_OPRN_ID IN VARCHAR2
129 ,P_OPRN_NO IN VARCHAR2
130 ,P_OPRN_VERS IN VARCHAR2
131 ,P_CHARGE IN VARCHAR2
132 ,P_DATE_EFFECTIVE IN DATE
133 ,P_EXACT_MATCH IN VARCHAR2
134 ,P_LOT_NUMBER IN VARCHAR2
135 ,P_FIND_SPEC_WITH_STEP IN VARCHAR2) RETURN NUMBER IS
136
137 ret_value BOOLEAN;
138 p_wip_spec_rec GMD_SPEC_MATCH_GRP.WIP_SPEC_REC_TYPE;
139 x_return_status VARCHAR2(4000);
140 x_message_data VARCHAR2(4000);
141 l_spec_id NUMBER := null;
142 l_spec_vr_id NUMBER := null;
143 l_smpl_plan_id number := null;
144 CURSOR get_sampling_plan_id is
145 SELECT SAMPLING_PLAN_ID
146 from gmd_wip_spec_vrs
147 WHERE SPEC_VR_ID = l_spec_vr_id;
148 CURSOR get_item_revison IS
149 SELECT REVISION
150 from fm_matl_dtl
151 where FORMULALINE_ID = P_FORMULALINE_ID;
152 BEGIN
153 p_wip_spec_rec.INVENTORY_ITEM_ID := P_INVENTORY_ITEM_ID;
154 p_wip_spec_rec.GRADE_CODE := P_GRADE_CODE;
155 p_wip_spec_rec.ORGANIZATION_ID := P_ORGANIZATION_ID;
156 p_wip_spec_rec.BATCH_ID := P_BATCH_ID;
157 p_wip_spec_rec.RECIPE_ID := P_RECIPE_ID;
158 p_wip_spec_rec.RECIPE_NO := P_RECIPE_NO;
159 p_wip_spec_rec.RECIPE_VERSION := P_RECIPE_VERSION;
160 p_wip_spec_rec.FORMULA_ID := P_FORMULA_ID;
161 p_wip_spec_rec.FORMULALINE_ID := P_FORMULALINE_ID;
162 p_wip_spec_rec.FORMULA_NO := P_FORMULA_NO;
163 p_wip_spec_rec.FORMULA_VERS := P_FORMULA_VERS;
164 p_wip_spec_rec.ROUTING_ID := P_ROUTING_ID;
165 p_wip_spec_rec.ROUTING_NO := P_ROUTING_NO;
166 p_wip_spec_rec.ROUTING_VERS := P_ROUTING_VERS;
167 p_wip_spec_rec.STEP_ID := P_STEP_ID;
168 p_wip_spec_rec.STEP_NO := P_STEP_NO;
169 p_wip_spec_rec.OPRN_ID := P_OPRN_ID;
170 p_wip_spec_rec.OPRN_NO := P_OPRN_NO;
171 p_wip_spec_rec.OPRN_VERS := P_OPRN_VERS;
172 p_wip_spec_rec.CHARGE := P_CHARGE;
173 p_wip_spec_rec.DATE_EFFECTIVE := NVL(P_DATE_EFFECTIVE,SYSDATE);
174 p_wip_spec_rec.EXACT_MATCH := P_EXACT_MATCH;
175 p_wip_spec_rec.LOT_NUMBER := P_LOT_NUMBER;
176 p_wip_spec_rec.FIND_SPEC_WITH_STEP := P_FIND_SPEC_WITH_STEP;
177 open get_item_revison;
178 fetch get_item_revison into p_wip_spec_rec.revision;
179 close get_item_revison;
180 ret_value := gmd_spec_match_grp.find_wip_spec(p_wip_spec_rec,l_spec_id,l_spec_vr_id,x_return_status,x_message_data);
181 IF RET_VALUE
182 THEN
183 OPEN get_sampling_plan_id;
184 FETCH get_sampling_plan_id INTO l_smpl_plan_id;
185 CLOSE get_sampling_plan_id;
186 RETURN l_smpl_plan_id;
187 ELSE
188 l_spec_id := null;
189 l_spec_vr_id := null;
190 RETURN null;
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 l_spec_id := null;
195 l_spec_vr_id := null;
196 RETURN null;
197 END GET_SAMPLING_PLAN;
198
199
200
201 PROCEDURE Get_ORGN_Process_parameters(p_RECIPE_ID number,
202 p_ROUTINGSTEP_ID number,
203 p_OPRN_LINE_ID number,
204 p_resource VARCHAR2,
205 p_DOCUMENT_id VARCHAR2,
206 P_PARAMETER_ID Number,
207 P_TARGET_VALUE VARCHAR2,
208 P_MINIMUM_VALUE Number,
209 P_MAXIMUM_VALUE Number,
210 X_TARGET_VALUE OUT NOCOPY VARCHAR2,
211 X_MINIMUM_VALUE OUT NOCOPY Number,
212 X_MAXIMUM_VALUE OUT NOCOPY Number,
213 X_PARAMETER_NAME OUT NOCOPY VARCHAR2) IS
214 CURSOR Get_Process_param_overrides IS
215 SELECT TARGET_VALUE,MINIMUM_VALUE,MAXIMUM_VALUE
216 FROM gmd_recipe_process_parameters
217 WHERE recipe_id = p_recipe_id
218 AND organization_id = substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1,
219 decode(instrb(P_DOCUMENT_ID,'-',1,2),0,
220 length(substrb(P_DOCUMENT_ID,instrb(P_DOCUMENT_ID,'-',1,1)+1)),
221 instrb(P_DOCUMENT_ID,'-',1,2)-(instrb(P_DOCUMENT_ID,'-',1,1)+1)))
222 AND routingstep_id = p_ROUTINGSTEP_ID
223 AND oprn_line_id = p_OPRN_LINE_ID
224 AND resources = p_resource
225 AND parameter_id = P_PARAMETER_ID ;
226
227 CURSOR GET_PARAMETER_NAME IS
228 SELECT PARAMETER_NAME
229 FROM GMP_PROCESS_PARAMETERS
230 WHERE PARAMETER_ID = P_PARAMETER_ID;
231 BEGIN
232 open Get_Process_param_overrides;
233 FETCH Get_Process_param_overrides INTO X_TARGET_VALUE,X_MINIMUM_VALUE,X_MAXIMUM_VALUE;
234 IF Get_Process_param_overrides%NOTFOUND THEN
235 X_TARGET_VALUE := P_TARGET_VALUE;
236 X_MINIMUM_VALUE := P_MINIMUM_VALUE ;
237 X_MAXIMUM_VALUE := P_MAXIMUM_VALUE ;
238 END IF;
239 CLOSE Get_Process_param_overrides;
240 OPEN GET_PARAMETER_NAME;
241 FETCH GET_PARAMETER_NAME INTO X_PARAMETER_NAME;
242 CLOSE GET_PARAMETER_NAME;
243 END Get_ORGN_Process_parameters;
244
245 Function Get_Step_qty (p_RECIPE_ID number,
246 p_ROUTINGSTEP_ID number,
247 p_step_qty number) return number IS
248 BEGIN
249 return p_step_qty;
250 END;
251
252 END GMD_MBR_UTIL_PKG;