[Home] [Help]
PACKAGE BODY: APPS.GMO_INSTR_ENTITY_PVT
Source
1 PACKAGE BODY GMO_INSTR_ENTITY_PVT AS
2 /* $Header: GMOVINEB.pls 120.5 2006/06/15 08:33:42 rahugupt noship $ */
3
4
5
6 function GET_ENTITYKEY_SEPARATOR_COUNT (P_ENTITY_KEY IN VARCHAR2) RETURN NUMBER
7
8 IS
9
10 BEGIN
11
12 return (length(replace(P_ENTITY_KEY, '$', '$$')) - length(P_ENTITY_KEY));
13
14 END GET_ENTITYKEY_SEPARATOR_COUNT;
15
16
17 --This function would verify if the step is locked or not.
18
19 function GET_ENTITY_DISPLAY_NAME (P_ENTITY_NAME IN VARCHAR2, P_ENTITY_KEY IN VARCHAR2) RETURN VARCHAR2
20
21 IS
22
23 l_entity_display_name varchar2(300);
24 l_entity_key_sep_count number;
25
26 l_inventory_item_id number;
27 l_organization_id number;
28
29 l_resources varchar2(300);
30 l_oprn_line_id number;
31 l_oprn_id number;
32 l_routingstep_id number;
33 l_recipe_id number;
34 l_formulaline_id number;
35
36
37 cursor get_oprn is
38 select oprn_no from gmd_operations
39 where oprn_id = l_oprn_id;
40
41 cursor get_routing_oprn is
42 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'ROUTING') || ' ' || c.routing_no || ' : ' || routingstep_no || '-' || oprn_no
43 from gmd_operations a, fm_rout_dtl b, gmd_routings c
44 where a.oprn_id = b.oprn_id
45 and c.routing_id = b.routing_id
46 and b.routingstep_id = l_routingstep_id
47 and a.oprn_id = l_oprn_id;
48
49 cursor get_recipe_oprn is
50
51 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'RECIPE') || ' ' || recipe_no || ' : ' || b.routingstep_no || '-' || a.oprn_no
52 from gmd_operations a, fm_rout_dtl b, gmd_recipes c
53 where a.oprn_id = b.oprn_id and b.routing_id = c.routing_id
54 and c.recipe_id = l_recipe_id and b.routingstep_id = l_routingstep_id and a.oprn_id = l_oprn_id;
55
56
57 cursor get_activity is
58 select activity from gmd_operation_activities where oprn_line_id = l_oprn_line_id;
59
60 cursor get_routing_activity is
61 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'ROUTING') || ' ' || c.routing_no || ' : ' || d.activity
62 from gmd_operations a, fm_rout_dtl b, gmd_routings c, gmd_operation_activities d
63 where a.oprn_id = b.oprn_id
64 and c.routing_id = b.routing_id
65 and a.oprn_id = d.oprn_id
66 and d.oprn_line_id = l_oprn_line_id
67 and b.routingstep_id = l_routingstep_id;
68
69 cursor get_recipe_activity is
70 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'RECIPE') || ' ' || recipe_no || ' : ' || d.activity
71 from gmd_operations a, fm_rout_dtl b, gmd_recipes c, gmd_operation_activities d
72 where a.oprn_id = b.oprn_id and b.routing_id = c.routing_id and a.oprn_id = d.oprn_id
73 and b.routingstep_id = l_routingstep_id
74 and c.recipe_id = l_recipe_id
75 and d.oprn_line_id = l_oprn_line_id;
76
77 cursor get_resource is
78 select b.resources
79 from gmd_operation_activities a, gmd_operation_resources b
80 where a.oprn_line_id = b.oprn_line_id
81 and a.oprn_line_id = l_oprn_line_id
82 and b.resources = l_resources;
83
84 cursor get_routing_resource is
85
86 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'ROUTING') || ' ' || c.routing_no || ' : ' || e.resources
87 from gmd_operations a, fm_rout_dtl b, gmd_routings c, gmd_operation_activities d, gmd_operation_resources e
88 where a.oprn_id = b.oprn_id
89 and c.routing_id = b.routing_id
90 and a.oprn_id = d.oprn_id
91 and d.oprn_line_id = e.oprn_line_id
92 and b.routingstep_id = l_routingstep_id
93 and d.oprn_line_id = l_oprn_line_id
94 and e.resources = l_resources;
95
96 cursor get_recipe_resource is
97 select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'RECIPE') || ' ' || recipe_no || ' : ' || e.resources
98 from gmd_operations a, fm_rout_dtl b, gmd_recipes c, gmd_operation_activities d, gmd_operation_resources e
99 where a.oprn_id = b.oprn_id and b.routing_id = c.routing_id and a.oprn_id = d.oprn_id and d.oprn_line_id = e.oprn_line_id
100 and c.recipe_id = l_recipe_id
101 and b.routingstep_id = l_routingstep_id
102 and e.oprn_line_id = l_oprn_line_id
103 and e.resources = l_resources;
104
105 cursor get_formula_material is
106 Select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'FORMULA') || ' ' || c.formula_no || ' : ' || b.Concatenated_segments
107 from fm_matl_dtl a, mtl_system_items_kfv b, fm_form_mst c
108 where a.inventory_item_id = b.inventory_item_id
109 and a.organization_id = b.organization_id
110 and a.formula_id = c.formula_id
111 and a.inventory_item_id = l_inventory_item_id
112 and a.formulaline_id = l_formulaline_id;
113
114 cursor get_recipe_material is
115 Select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'RECIPE') || ' ' || c.recipe_no || ' : ' || b.Concatenated_segments
116 from fm_matl_dtl a, mtl_system_items_kfv b, gmd_recipes c
117 where a.inventory_item_id = b.inventory_item_id
118 and a.organization_id=b.organization_id
119 and a.formula_id = c.formula_id
120 and a.inventory_item_id = l_inventory_item_id
121 and a.formulaline_id = l_formulaline_id
122 and c.recipe_id = l_recipe_id;
123
124 cursor get_dispense_item is
125 Select a.Concatenated_segments
126 from mtl_system_items_kfv a
127 where a.inventory_item_id = l_inventory_item_id;
128
129 cursor get_org_dispense_item is
130 Select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'ORGANIZATION') || ' ' || b.organization_code || ' : ' || a.Concatenated_segments
131 from mtl_system_items_kfv a, mtl_parameters b
132 where a.organization_id=b.organization_id
133 and a.inventory_item_id = l_inventory_item_id
134 and a.organization_id = l_organization_id;
135
136
137 cursor get_recipe_dispense_item is
138 Select gmo_utilities.get_lookup_meaning ('GMO_INSTR_ENTITY_CODES', 'RECIPE') || ' ' || c.recipe_no || ' : ' || a.Concatenated_segments
139 from mtl_system_items_kfv a, mtl_parameters b, gmd_recipes c, fm_matl_dtl d
140 where a.organization_id=b.organization_id
141 and d.formula_id = c.formula_id
142 and d.inventory_item_id = a.inventory_item_id
143 and a.inventory_item_id = l_inventory_item_id
144 and a.organization_id = l_organization_id
145 and c.recipe_id = l_recipe_id;
146
147 cursor get_dispense_config_details is
148 select inventory_item_id,
149 organization_id,
150 recipe_id
151 from gmo_dispense_config
152 where config_id = to_number(p_entity_key,'999999999999.999999');
153
154
155 BEGIN
156
157 l_entity_key_sep_count := GET_ENTITYKEY_SEPARATOR_COUNT(p_entity_key);
158
159 if (p_entity_name = 'OPERATION') then
160
161 if (l_entity_key_sep_count = 0) then
162
163 l_oprn_id := to_number(p_entity_key);
164 open get_oprn;
165 fetch get_oprn into l_entity_display_name;
166 close get_oprn;
167
168 elsif (l_entity_key_sep_count = 1) then
169
170 l_routingstep_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
171 l_oprn_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1));
172 open get_routing_oprn;
173 fetch get_routing_oprn into l_entity_display_name;
174 close get_routing_oprn;
175
176 elsif (l_entity_key_sep_count = 2) then
177
178 l_recipe_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
179 l_routingstep_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1, (instr (p_entity_key, '$', 1,2 )-1) - instr (p_entity_key, '$')));
180 l_oprn_id := to_number(substr(p_entity_key, instr (p_entity_key, '$', 1,2) + 1));
181
182 open get_recipe_oprn;
183 fetch get_recipe_oprn into l_entity_display_name;
184 close get_recipe_oprn;
185 end if;
186
187 elsif (p_entity_name = 'ACTIVITY') then
188
189 if (l_entity_key_sep_count = 0) then
190
191 l_oprn_line_id := to_number(p_entity_key);
192 open get_activity;
193 fetch get_activity into l_entity_display_name;
194 close get_activity;
195
196 elsif (l_entity_key_sep_count = 1) then
197
198 l_routingstep_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
199 l_oprn_line_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1));
200 open get_routing_activity;
201 fetch get_routing_activity into l_entity_display_name;
202 close get_routing_activity;
203
204 elsif (l_entity_key_sep_count = 2) then
205
206 l_recipe_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
207 l_routingstep_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1, (instr (p_entity_key, '$', 1,2 )-1) - instr (p_entity_key, '$')));
208 l_oprn_line_id := to_number(substr(p_entity_key, instr (p_entity_key, '$', 1,2) + 1));
209 open get_recipe_activity;
210 fetch get_recipe_activity into l_entity_display_name;
211 close get_recipe_activity;
212
213 end if;
214
215 elsif (p_entity_name = 'RESOURCE') then
216
217 if (l_entity_key_sep_count = 1) then
218
219 l_oprn_line_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
220 l_resources := substr(p_entity_key, instr (p_entity_key, '$') + 1);
221 open get_resource;
222 fetch get_resource into l_entity_display_name;
223 close get_resource;
224
225 elsif (l_entity_key_sep_count = 2) then
226
227 l_routingstep_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
228 l_oprn_line_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1, (instr (p_entity_key, '$', 1,2 )-1) - instr (p_entity_key, '$')));
229 l_resources := substr(p_entity_key, instr (p_entity_key, '$', 1,2) + 1);
230 open get_routing_resource;
231 fetch get_routing_resource into l_entity_display_name;
232 close get_routing_resource;
233
234 elsif (l_entity_key_sep_count = 3) then
235
236
237 l_recipe_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
238 l_routingstep_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1, (instr (p_entity_key, '$', 1,2 )-1) - instr (p_entity_key, '$')));
239 l_oprn_line_id := to_number (substr(p_entity_key, instr (p_entity_key, '$', 1,2) + 1, (instr (p_entity_key, '$', 1,3)-1) - instr (p_entity_key, '$', 1,2)));
240 l_resources := substr(p_entity_key, instr (p_entity_key, '$', 1,3) + 1);
241
242 open get_recipe_resource;
243 fetch get_recipe_resource into l_entity_display_name;
244 close get_recipe_resource;
245 end if;
246
247 elsif (p_entity_name = 'MATERIAL') then
248
249 if (l_entity_key_sep_count = 1) then
250
251 l_formulaline_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
252 l_inventory_item_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1));
253
254 open get_formula_material;
255 fetch get_formula_material into l_entity_display_name;
256 close get_formula_material;
257 elsif (l_entity_key_sep_count = 2) then
258
259 l_recipe_id := to_number (substr(p_entity_key, 1, instr (p_entity_key, '$') - 1));
260 l_formulaline_id := to_number (substr(p_entity_key, instr (p_entity_key, '$') + 1, (instr (p_entity_key, '$', 1,2 )-1) - instr (p_entity_key, '$')));
261 l_inventory_item_id := to_number(substr(p_entity_key, instr (p_entity_key, '$', 1,2) + 1));
262
263 open get_recipe_material;
264 fetch get_recipe_material into l_entity_display_name;
265 close get_recipe_material;
266
267 end if;
268 elsif (p_entity_name = 'DISPENSE_ITEM') then
269
270 open get_dispense_config_details;
271 fetch get_dispense_config_details into l_inventory_item_id,l_organization_id,l_recipe_id;
272 close get_dispense_config_details;
273
274 if (l_inventory_item_id is not null and l_organization_id is null and l_recipe_id is null) then
275
276 open get_dispense_item;
277 fetch get_dispense_item into l_entity_display_name;
278 close get_dispense_item;
279
280 elsif (l_inventory_item_id is not null and l_organization_id is not null and l_recipe_id is null) then
281
282 open get_org_dispense_item;
283 fetch get_org_dispense_item into l_entity_display_name;
284 close get_org_dispense_item;
285
286 elsif (l_inventory_item_id is not null and l_organization_id is not null and l_recipe_id is not null) then
287
288 open get_recipe_dispense_item;
289 fetch get_recipe_dispense_item into l_entity_display_name;
290 close get_recipe_dispense_item;
291 end if;
292
293 end if;
294
295 return l_entity_display_name;
296
297
298 END GET_ENTITY_DISPLAY_NAME;
299
300 --Bug 5203096: start
301 function GET_TARGET_TASK_ATTRIBUTE
302 (
303 P_ENTITY_NAME IN VARCHAR2,
304 P_SOURCE_ENTITY_KEY IN VARCHAR2,
305 P_TARGET_ENTITY_KEY IN VARCHAR2,
306 P_TASK_ID IN NUMBER,
307 P_TASK_ATTRIBUTE_ID IN VARCHAR2
308 )
309 RETURN VARCHAR2
310 IS
311 l_task_attribute_id varchar2(4000);
312 l_new_task_attribute_id varchar2(4000);
313 l_oprn_line_id number;
314 l_other varchar2(4000);
315 t_old_oprn_line_id fnd_table_of_varchar2_255;
316 t_new_oprn_line_id fnd_table_of_varchar2_255;
317 l_old_oprn_line_id number;
318 l_new_oprn_line_id number;
319 j binary_integer;
320 l_task_id number;
321 l_task_name varchar2(100);
322 l_source_entity_key_sep number;
323 l_target_entity_key_sep number;
324 l_source_oprn_id number;
325 l_target_oprn_id number;
326
327 cursor get_task_name is select task_name from gmo_instr_task_defn_b where task_id = P_TASK_ID;
328
329 cursor get_old_oprn_line is select oprn_line_id FROM gmd_operation_activities where oprn_id = l_source_oprn_id order by oprn_line_id;
330 cursor get_new_oprn_line is select oprn_line_id FROM gmd_operation_activities where oprn_id = l_target_oprn_id order by oprn_line_id;
331
332 BEGIN
333
334 l_task_name := '';
335
336 open get_task_name;
337 fetch get_task_name into l_task_name;
338 close get_task_name;
339
340 t_old_oprn_line_id := fnd_table_of_varchar2_255();
341 t_new_oprn_line_id := fnd_table_of_varchar2_255();
342 l_task_attribute_id := P_TASK_ATTRIBUTE_ID;
343
344 if ( (l_task_attribute_id is not null) and
345 (l_task_name = GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION or l_task_name = GMO_CONSTANTS_GRP.TASK_PROCESS_PARAMETER) and
346 (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION)
347 ) then
348
349 l_source_entity_key_sep := GET_ENTITYKEY_SEPARATOR_COUNT(P_SOURCE_ENTITY_KEY);
350 l_target_entity_key_sep := GET_ENTITYKEY_SEPARATOR_COUNT(P_TARGET_ENTITY_KEY);
351
352 --at operation level
353 if (l_source_entity_key_sep = 0) then
354 l_source_oprn_id := P_SOURCE_ENTITY_KEY;
355 --at routing level
356 elsif (l_source_entity_key_sep = 1) then
357 l_source_oprn_id := substr(P_SOURCE_ENTITY_KEY, instr(P_SOURCE_ENTITY_KEY, '$') + 1);
358 --at recipe level
359 elsif (l_source_entity_key_sep = 2) then
360 l_source_oprn_id := substr(P_SOURCE_ENTITY_KEY, instr (P_SOURCE_ENTITY_KEY, '$', 1,2) + 1);
361 end if;
362
363 if (l_target_entity_key_sep = 0) then
364 l_target_oprn_id := P_TARGET_ENTITY_KEY;
365 elsif (l_target_entity_key_sep = 1) then
366 l_target_oprn_id := substr(P_TARGET_ENTITY_KEY, instr(P_TARGET_ENTITY_KEY, '$') + 1);
367 elsif (l_target_entity_key_sep = 2) then
368 l_target_oprn_id := substr(P_TARGET_ENTITY_KEY, instr (P_TARGET_ENTITY_KEY, '$', 1,2) + 1);
369 end if;
370
371 -- pattern = OprnLineId$Resources or OprnLineId$Resources$ParamId
372 l_oprn_line_id := to_number (substr(l_task_attribute_id, 1, instr (l_task_attribute_id, '$') - 1));
373 l_other := substr(l_task_attribute_id, instr (l_task_attribute_id, '$') + 1);
374
375 l_new_task_attribute_id := null;
376
377 --we need to take care at operating level only
378 --at routing and recipe level, the oprnid remains same
379 if (l_source_entity_key_sep = 0) then
380
381 j := 0;
382 open get_old_oprn_line;
383 loop
384 fetch get_old_oprn_line into l_old_oprn_line_id;
385 exit when get_old_oprn_line%NOTFOUND;
386
387 t_old_oprn_line_id.extend;
388 j := j+1;
389 t_old_oprn_line_id(j) := l_old_oprn_line_id;
390 end loop;
391 close get_old_oprn_line;
392
393 j := 0;
394 open get_new_oprn_line;
395 loop
396 fetch get_new_oprn_line into l_new_oprn_line_id;
397 exit when get_new_oprn_line%NOTFOUND;
398
399 t_new_oprn_line_id.extend;
400 j := j+1;
401 t_new_oprn_line_id(j) := l_new_oprn_line_id;
402 end loop;
403 close get_new_oprn_line;
404
405 for i in 1 .. t_old_oprn_line_id.count loop
406 if l_oprn_line_id =to_number(t_old_oprn_line_id(i)) then
407 if (t_new_oprn_line_id.count >= i) then
408 l_new_task_attribute_id := t_new_oprn_line_id(i) || '$' || l_other;
409 end if;
410 end if;
411 end loop;
412
413 end if;
414 end if;
415 if (l_new_task_attribute_id is null) then
416 l_new_task_attribute_id := P_TASK_ATTRIBUTE_ID;
417 end if;
418 return l_new_task_attribute_id;
419 END GET_TARGET_TASK_ATTRIBUTE;
420
421 procedure UPDATE_TASK_ATTRIBUTE
422 (
423 P_INSTRUCTION_PROCESS_ID IN NUMBER,
424 P_INSTRUCTION_SET_ID IN NUMBER,
425 P_ENTITY_NAME IN VARCHAR2,
426 P_SOURCE_ENTITY_KEY IN VARCHAR2,
427 P_TARGET_ENTITY_KEY IN VARCHAR2
428 )
429 IS
430 l_instruction_id number;
431 l_task_attribute_id varchar2(4000);
432 l_new_task_attribute_id varchar2(4000);
433 l_task_id number;
434
435 cursor get_instr_details is select instruction_id, task_id, task_attribute_id from gmo_instr_defn_t where instruction_process_id = P_INSTRUCTION_PROCESS_ID and instruction_set_id=P_INSTRUCTION_SET_ID;
436
437
438 BEGIN
439 IF (P_SOURCE_ENTITY_KEY <> P_TARGET_ENTITY_KEY) THEN
440 open get_instr_details;
441 loop
442 fetch get_instr_details into l_instruction_id,l_task_id, l_task_attribute_id;
443 exit when get_instr_details%NOTFOUND;
444
445 if (l_task_attribute_id is not null) then
446 l_new_task_attribute_id := GET_TARGET_TASK_ATTRIBUTE (
447 P_ENTITY_NAME => P_ENTITY_NAME,
448 P_SOURCE_ENTITY_KEY => P_SOURCE_ENTITY_KEY,
449 P_TARGET_ENTITY_KEY => P_TARGET_ENTITY_KEY,
450 P_TASK_ID => L_TASK_ID,
451 P_TASK_ATTRIBUTE_ID => L_TASK_ATTRIBUTE_ID
452 );
453
454 update gmo_instr_defn_t set task_attribute_id = l_new_task_attribute_id
455 where instruction_process_id = P_INSTRUCTION_PROCESS_ID and instruction_id = l_instruction_id;
456 end if;
457
458 end loop;
459 close get_instr_details;
460 END IF;
461 END UPDATE_TASK_ATTRIBUTE;
462 --Bug 5203096: end
463
464 END GMO_INSTR_ENTITY_PVT;