[Home] [Help]
PACKAGE BODY: APPS.GMD_PROCESS_INSTR_UTILS
Source
4 /* Cursor Definitions */
1 PACKAGE BODY GMD_PROCESS_INSTR_UTILS AS
2 /* $Header: GMDPIUTB.pls 120.9 2006/07/12 18:08:07 txdaniel noship $ */
3
5
6 /* Cursor to get the routing_id attached with the recipe */
7 CURSOR Get_recipe_details (v_recipe_id NUMBER) IS
8 SELECT r.routing_id, r.formula_id
9 FROM gmd_recipes_b r
10 WHERE r.recipe_id = v_recipe_id;
11
12 /* Cursor to fetch formula item details */
13 CURSOR Get_formula_details (v_formula_id NUMBER) IS
14 SELECT f.formulaline_id, f.line_no, f.line_type, f.inventory_item_id, i.concatenated_segments
15 FROM fm_matl_dtl f, mtl_system_items_kfv i
16 WHERE f.formula_id = v_formula_id
17 AND f.inventory_item_id = i.inventory_item_id
18 AND f.organization_id = i.organization_id
19 ORDER BY f.line_type, f.line_no;
20
21 /* Cursor to fetch activity details for an operation */
22 CURSOR Get_activity_details (v_oprn_id NUMBER) IS
23 SELECT oprn_line_id, activity
24 FROM gmd_operation_activities
25 WHERE oprn_id = v_oprn_id
26 ORDER BY oprn_line_id;
27
28 /* Cursor to fetch routing details */
29 CURSOR Get_routing_details (v_routing_id NUMBER) IS
30 SELECT r.routingstep_id, r.routingstep_no, r.oprn_id, o.oprn_no
31 FROM fm_rout_dtl r, gmd_operations_b o
32 WHERE r.routing_id = v_routing_id
33 AND r.oprn_id = o.oprn_id
34 ORDER BY r.routingstep_no;
35
36 /* Cursor to fetch resource details for an activity */
37 CURSOR Get_resource_details (v_oprn_line_id NUMBER) IS
38 SELECT oprn_line_id, resources
39 FROM gmd_operation_resources
40 WHERE oprn_line_id = v_oprn_line_id
41 ORDER BY resources;
42
43
44 /*-------------------------------------------------------------------
45 -- NAME
46 -- Build_Array
47 --
48 -- SYNOPSIS
49 -- Procedure Build_Array
50 --
51 -- DESCRIPTION
55 -- B5305793 - Added this procedure to fix the issue reported.
52 -- This procedure is used to build the array to pass to GMO
53 --
54 -- HISTORY
56 --------------------------------------------------------------------*/
57
58 PROCEDURE Build_Array (
59 p_entity_name IN VARCHAR2 ,
60 p_entity_id IN NUMBER ,
61 x_name_array OUT NOCOPY GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
62 x_key_array OUT NOCOPY GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
63 x_return_status OUT NOCOPY VARCHAR2) IS
64
65 l_instruction_type VARCHAR2(10);
66 l_proc_instr_id NUMBER;
67
68 l_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
69 l_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
70 l_form_id FM_FORM_MST.FORMULA_ID%TYPE;
71 l_oprn_id NUMBER;
72 l_oprn_no VARCHAR2(200);
73 l_msg_cnt NUMBER;
74 l_msg_data VARCHAR2(2000);
75 l_return_code VARCHAR2(20);
76 l_status VARCHAR2(30);
77 l_entity_name VARCHAR2(200);
78 l_count NUMBER;
79 l_line_id NUMBER;
80 l_line_no NUMBER;
81 l_line_type NUMBER;
82 i NUMBER;
83 j NUMBER;
84
85 BEGIN
86
87 /* Default the process type to 'PROCESS' when called from NPD */
88 l_instruction_type := 'PROCESS';
89
90
91 IF p_entity_name = 'FORMULA' THEN
92
93 /* Set the Source entity name and key for Formula*/
94 i := 1;
95 FOR l_rec IN Get_formula_details(p_entity_id)
96 LOOP
97 X_name_array(i) := 'MATERIAL';
98 X_key_array(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
99 i := i + 1;
100 END LOOP;
101
102 ELSIF p_entity_name = 'OPERATION' THEN
103
104 /* Set the Source entity name and key for Operation*/
105 i := 1;
106 X_name_array(i) := 'OPERATION';
107 X_key_array(i) := p_entity_id;
108
109 -- Get all activity details for the operation
110 FOR l_rec IN Get_activity_details(p_entity_id)
111 LOOP
112 i := i + 1;
113 X_name_array(i) := 'ACTIVITY';
114 X_key_array(i) := TO_CHAR(l_rec.oprn_line_id);
115
116 -- Get all resource details for the activity
117 FOR l_rec_rsrc IN Get_resource_details(l_rec.oprn_line_id)
118 LOOP
119 i := i + 1;
120 X_name_array(i) := 'RESOURCE';
121 X_key_array(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
122 END LOOP;
123 END LOOP;
124 ELSIF p_entity_name = 'ROUTING' THEN
125 /* Set the Source entity name and key for Routing */
126 i := 1;
127 FOR l_rec IN Get_routing_details(p_entity_id)
128 LOOP
129 X_name_array(i) := 'OPERATION';
130 X_key_array(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
131
132 -- Get all activity details for the operation
133 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
134 LOOP
135 i := i + 1;
136 X_name_array(i) := 'ACTIVITY';
137 X_key_array(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
138
139 -- Get all resource details for the activity
140 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
141 LOOP
142 i := i + 1;
143 X_name_array(i) := 'RESOURCE';
144 X_key_array(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
145 END LOOP;
146 END LOOP;
147 i := i + 1;
148 END LOOP;
149 END IF;
150 END Build_Array;
151
152 /*-------------------------------------------------------------------
153 -- NAME
154 -- Copy_Process_Instructions
155 --
156 -- SYNOPSIS
157 -- Procedure Copy_Process_Instructions
158 --
159 -- DESCRIPTION
160 -- This procedure is called to copy the process instructions from
161 -- one entity to another
162 --
163 -- HISTORY
164 -- B5305793 - Added this procedure to fix the issue reported.
165 --------------------------------------------------------------------*/
166
167 PROCEDURE Copy_Process_Instructions (
168 p_source_name_array IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
169 p_source_key_array IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
170 p_target_name_array IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
171 p_target_key_array IN GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255,
172 x_return_status OUT NOCOPY VARCHAR2) IS
173 l_proc_instr_id NUMBER(15);
174 l_msg_count NUMBER(5);
175 l_msg_data VARCHAR2(2000);
176 BEGIN
177
178 /* Call the GMO Create defn from defn API */
179 FOR j IN 1..p_source_name_array.COUNT
180 LOOP
181 GMO_INSTRUCTION_GRP.CREATE_DEFN_FROM_DEFN
182 (
183 P_API_VERSION => 1.0 ,
184 P_INIT_MSG_LIST => FND_API.G_FALSE ,
185 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL ,
186 P_SOURCE_ENTITY_NAME => p_source_name_array(j) ,
187 P_SOURCE_ENTITY_KEY => p_source_key_array(j) ,
188 P_TARGET_ENTITY_NAME => p_target_name_array(j) ,
189 P_TARGET_ENTITY_KEY => p_target_key_array(j) ,
190 P_INSTRUCTION_TYPE => 'PROCESS' ,
191 X_INSTRUCTION_SET_ID => l_proc_instr_id ,
192 X_RETURN_STATUS => x_return_status ,
193 X_MSG_COUNT => l_msg_count ,
194 X_MSG_DATA => l_msg_data
195 );
196
197 END LOOP;
198
199 END Copy_Process_Instructions;
200
204 -- COPY_PROCESS_INSTR
201
202 /*-------------------------------------------------------------------
203 -- NAME
205 --
206 -- SYNOPSIS
207 -- Procedure COPY_PROCESS_INSTR
208 --
209 -- DESCRIPTION
210 -- This procedure is called to copy the process instructions from
211 -- one entity to another
212 --
213 -- HISTORY
214 -- Sriram 7/20/2005 Created for GMD-GMO Integration Build
215 --------------------------------------------------------------------*/
216
217 PROCEDURE COPY_PROCESS_INSTR (
218 p_entity_name IN VARCHAR2 ,
219 p_from_entity_id IN NUMBER ,
220 p_to_entity_id IN NUMBER ,
221 x_return_status OUT NOCOPY VARCHAR2 ,
222 x_msg_count OUT NOCOPY NUMBER ,
223 x_msg_data OUT NOCOPY VARCHAR2 ) IS
224
225 l_source_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
226 l_source_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
227 l_target_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
228 l_target_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
229 l_instruction_type VARCHAR2(10);
230 l_proc_instr_id NUMBER;
231
232 l_old_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
233 l_new_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
234 l_old_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
235 l_new_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
236 l_old_form_id FM_FORM_MST.FORMULA_ID%TYPE;
237 l_new_form_id FM_FORM_MST.FORMULA_ID%TYPE;
238 l_oprn_id NUMBER;
239 l_oprn_no VARCHAR2(200);
240 l_msg_cnt NUMBER;
241 l_msg_data VARCHAR2(2000);
242 l_return_code VARCHAR2(20);
243 l_status VARCHAR2(30);
244 l_entity_name VARCHAR2(200);
245 l_count NUMBER;
246 l_line_id NUMBER;
247 l_line_no NUMBER;
248 l_line_type NUMBER;
249 i NUMBER;
250 j NUMBER;
251
252 BEGIN
253
254 /* Default the process type to 'PROCESS' when called from NPD */
255 l_instruction_type := 'PROCESS';
256
257 IF p_entity_name = 'RECIPE' THEN
258
259 /* Get the old routing and formula id */
260 OPEN Get_recipe_details(p_from_entity_id);
261 FETCH Get_recipe_details INTO l_old_rout_id, l_old_form_id;
262 CLOSE Get_recipe_details;
263
264 /* Get the new routing and formula id */
265 OPEN Get_recipe_details(p_to_entity_id);
266 FETCH Get_recipe_details INTO l_new_rout_id, l_new_form_id;
267 CLOSE Get_recipe_details;
268
269 /* Set the Source entity name and key for Recipe*/
270 i := 1;
271 IF l_old_rout_id = l_new_rout_id THEN
272 GMD_RECIPE_FETCH_PUB.get_routing_step_details(
273 p_api_version => 1.0 ,
274 p_init_msg_list => 'F' ,
275 p_routing_id => l_old_rout_id ,
276 x_return_status => l_status ,
277 x_msg_count => l_msg_cnt ,
278 x_msg_data => l_msg_data ,
279 x_return_code => l_return_code ,
280 x_routing_step_out => l_old_rout_dets );
281
282 l_count := l_old_rout_dets.COUNT;
283
284 FOR j IN 1..l_count
285 LOOP
286 l_line_id := l_old_rout_dets(j).ROUTINGSTEP_ID;
287 l_oprn_id := l_old_rout_dets(j).OPRN_ID;
288
289 l_source_entity_name(i) := 'OPERATION';
290 l_source_entity_key(i) := TO_CHAR(p_from_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
291
292 -- Get all activity details for the operation
293 FOR l_rec_actv IN Get_activity_details(l_oprn_id)
294 LOOP
295 i := i + 1;
296 l_source_entity_name(i) := 'ACTIVITY';
297 l_source_entity_key(i) := TO_CHAR(p_from_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
298
299 -- Get all resource details for the activity
300 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
301 LOOP
302 i := i + 1;
303 l_source_entity_name(i) := 'RESOURCE';
304 l_source_entity_key(i) := TO_CHAR(p_from_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
305 END LOOP;
306 END LOOP;
307 i := i + 1;
308 END LOOP;
309 END IF; -- IF l_old_rout_id = l_new_rout_id THEN
310
311 IF l_old_form_id = l_new_form_id THEN
312 FOR l_rec IN Get_formula_details(l_old_form_id)
313 LOOP
314 l_source_entity_name(i) := 'MATERIAL';
315 l_source_entity_key(i) := TO_CHAR(p_from_entity_id) || '$' || TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
316 i := i + 1;
317 END LOOP;
318 END IF;
319
320 /* Set the Target entity name and key for Recipe*/
321
322 i := 1;
323 IF l_old_rout_id = l_new_rout_id THEN
324 GMD_RECIPE_FETCH_PUB.get_routing_step_details(
325 p_api_version => 1.0 ,
326 p_init_msg_list => 'F' ,
327 p_routing_id => l_new_rout_id ,
328 x_return_status => l_status ,
329 x_msg_count => l_msg_cnt ,
330 x_msg_data => l_msg_data ,
331 x_return_code => l_return_code ,
332 x_routing_step_out => l_new_rout_dets );
333
334 l_count := l_new_rout_dets.COUNT;
335
336 FOR j IN 1..l_count
337 LOOP
338 l_line_id := l_new_rout_dets(j).ROUTINGSTEP_ID;
339 l_oprn_id := l_new_rout_dets(j).OPRN_ID;
340
341 l_target_entity_name(i) := 'OPERATION';
342 l_target_entity_key(i) := TO_CHAR(p_to_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
343
344 -- Get all activity details for the operation
345 FOR l_rec_actv IN Get_activity_details(l_oprn_id)
346 LOOP
347 i := i + 1;
348 l_target_entity_name(i) := 'ACTIVITY';
352 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
349 l_target_entity_key(i) := TO_CHAR(p_to_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
350
351 -- Get all resource details for the activity
353 LOOP
354 i := i + 1;
355 l_target_entity_name(i) := 'RESOURCE';
356 l_target_entity_key(i) := TO_CHAR(p_to_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
357 END LOOP;
358 END LOOP;
359 i := i + 1;
360 END LOOP;
361 END IF; -- IF l_old_rout_id = l_new_rout_id THEN
362
363 IF l_old_form_id = l_new_form_id THEN
364 FOR l_rec IN Get_formula_details(l_new_form_id)
365 LOOP
366 l_target_entity_name(i) := 'MATERIAL';
367 l_target_entity_key(i) := TO_CHAR(p_to_entity_id) || '$' || TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
368 i := i + 1;
369 END LOOP;
370 END IF;
371
372 ELSIF p_entity_name = 'FORMULA' THEN
373
374 /* Set the Source entity name and key for Formula*/
375 i := 1;
376 FOR l_rec IN Get_formula_details(p_from_entity_id)
377 LOOP
378 l_source_entity_name(i) := 'MATERIAL';
379 l_source_entity_key(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
380 i := i + 1;
381 END LOOP;
382
383 /* Set the Target entity name and key for Formula*/
384 i := 1;
385 FOR l_rec IN Get_formula_details(p_to_entity_id)
386 LOOP
387 l_target_entity_name(i) := 'MATERIAL';
388 l_target_entity_key(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
389 i := i + 1;
390 END LOOP;
391
392 ELSIF p_entity_name = 'OPERATION' THEN
393
394 /* Set the Source entity name and key for Operation*/
395
396 i := 1;
397 l_source_entity_name(i) := 'OPERATION';
398 l_source_entity_key(i) := TO_CHAR(p_from_entity_id);
399
400
401 -- Get all activity details for the operation
402 FOR l_rec IN Get_activity_details(p_from_entity_id)
403 LOOP
404 i := i + 1;
405 l_source_entity_name(i) := 'ACTIVITY';
406 l_source_entity_key(i) := TO_CHAR(l_rec.oprn_line_id);
407
408 -- Get all resource details for the activity
409 FOR l_rec_rsrc IN Get_resource_details(l_rec.oprn_line_id)
410 LOOP
411 i := i + 1;
412 l_source_entity_name(i) := 'RESOURCE';
413 l_source_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
414 END LOOP;
415 END LOOP;
416
417 /* Set the Target entity name and key for Operation*/
418
419 i := 1;
420 l_target_entity_name(i) := 'OPERATION';
421 l_target_entity_key(i) := TO_CHAR(p_to_entity_id);
422
423 -- Get all activity details for the operation
424 FOR l_rec IN Get_activity_details(p_to_entity_id)
425 LOOP
426 i := i + 1;
427 l_target_entity_name(i) := 'ACTIVITY';
428 l_target_entity_key(i) := TO_CHAR(l_rec.oprn_line_id);
429
430 -- Get all resource details for the activity
431 FOR l_rec_rsrc IN Get_resource_details(l_rec.oprn_line_id)
432 LOOP
433 i := i + 1;
434 l_target_entity_name(i) := 'RESOURCE';
435 l_target_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
436 END LOOP;
437 END LOOP;
438
439 ELSIF p_entity_name = 'ROUTING' THEN
440
441 /* Set the Source entity name and key for Routing */
442
443 i := 1;
444 FOR l_rec IN Get_routing_details(p_from_entity_id)
445 LOOP
446 l_source_entity_name(i) := 'OPERATION';
447 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
448
449 -- Get all activity details for the operation
450 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
451 LOOP
452 i := i + 1;
453 l_source_entity_name(i) := 'ACTIVITY';
454 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
455
456 -- Get all resource details for the activity
457 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
458 LOOP
459 i := i + 1;
460 l_source_entity_name(i) := 'RESOURCE';
461 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
462 END LOOP;
463 END LOOP;
464 i := i + 1;
465 END LOOP;
466
467 /* Set the Target entity name and key for Routing */
468
469 i := 1;
470 FOR l_rec IN Get_routing_details(p_to_entity_id)
471 LOOP
472 l_target_entity_name(i) := 'OPERATION';
473 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
474
475 -- Get all activity details for the operation
476 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
477 LOOP
478 i := i + 1;
479 l_target_entity_name(i) := 'ACTIVITY';
480 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
481
482 -- Get all resource details for the activity
483 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
484 LOOP
485 i := i + 1;
486 l_target_entity_name(i) := 'RESOURCE';
487 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
488 END LOOP;
489 END LOOP;
490 i := i + 1;
491 END LOOP;
492
493 END IF;
494
495 /* Call the GMO Create defn from defn API */
496 FOR j IN 1..l_source_entity_name.COUNT
497 LOOP
498
499 GMO_INSTRUCTION_GRP.CREATE_DEFN_FROM_DEFN
500 (
501 P_API_VERSION => 1.0 ,
502 P_INIT_MSG_LIST => FND_API.G_FALSE ,
506 P_TARGET_ENTITY_NAME => l_target_entity_name(j) ,
503 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL ,
504 P_SOURCE_ENTITY_NAME => l_source_entity_name(j) ,
505 P_SOURCE_ENTITY_KEY => l_source_entity_key(j) ,
507 P_TARGET_ENTITY_KEY => l_target_entity_key(j) ,
508 P_INSTRUCTION_TYPE => l_instruction_type ,
509 X_INSTRUCTION_SET_ID => l_proc_instr_id ,
510 X_RETURN_STATUS => X_RETURN_STATUS ,
511 X_MSG_COUNT => X_MSG_COUNT ,
512 X_MSG_DATA => X_MSG_DATA
513 );
514
515 END LOOP;
516
517 END COPY_PROCESS_INSTR;
518
519 /*-------------------------------------------------------------------
520 -- NAME
521 -- COPY_PROCESS_INSTR
522 --
523 -- SYNOPSIS
524 -- Procedure COPY_PROCESS_INSTR
525 --
526 -- DESCRIPTION
527 -- This procedure is called to copy the process instructions from
528 -- child entites to parent entity
529 --
530 -- E.g When a reciipe is created, copy the PI's defined at routing and
531 -- formula level to recipe-routing and recipe-formula level
532 --
533 -- When a routing is created, copy the PI's defined at operation level to the
534 -- routing-operation level
535 --
536 -- HISTORY
537 -- Sriram 7/20/2005 Created for GMD-GMO Integration Build
538 --------------------------------------------------------------------*/
539
540 PROCEDURE COPY_PROCESS_INSTR (
541 p_entity_name IN VARCHAR2 ,
542 p_entity_id IN NUMBER ,
543 x_return_status OUT NOCOPY VARCHAR2 ,
544 x_msg_count OUT NOCOPY NUMBER ,
545 x_msg_data OUT NOCOPY VARCHAR2 ) IS
546
547 l_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
548 l_form_id FM_FORM_MST.FORMULA_ID%TYPE;
549 l_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
550
551 l_parent_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
552 l_parent_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
553 l_child_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
554 l_child_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
555 l_instruction_type VARCHAR2(10);
556 l_proc_instr_id NUMBER;
557
558
559 l_msg_cnt NUMBER;
560 l_msg_data VARCHAR2(2000);
561 l_return_code VARCHAR2(20);
562 l_status VARCHAR2(30);
563 l_line_id NUMBER;
564 l_oprn_id NUMBER;
565 i NUMBER;
566
567
568 BEGIN
569
570 /* Default the process type to 'PROCESS' when called from NPD */
571 l_instruction_type := 'PROCESS';
572
573 IF p_entity_name = 'RECIPE' THEN
574
575 OPEN Get_recipe_details(p_entity_id);
576 FETCH Get_recipe_details INTO l_rout_id, l_form_id;
577 CLOSE Get_recipe_details;
578
579 i := 1;
580
581 /* Copy routing level PI's to the recipe */
582 IF l_rout_id IS NOT NULL THEN
583 GMD_RECIPE_FETCH_PUB.get_routing_step_details(
584 p_api_version => 1.0 ,
585 p_init_msg_list => 'F' ,
586 p_routing_id => l_rout_id ,
587 x_return_status => l_status ,
588 x_msg_count => l_msg_cnt ,
589 x_msg_data => l_msg_data ,
590 x_return_code => l_return_code ,
591 x_routing_step_out => l_rout_dets );
592
593 FOR j IN 1..l_rout_dets.COUNT
594 LOOP
595 l_line_id := l_rout_dets(j).ROUTINGSTEP_ID;
596 l_oprn_id := l_rout_dets(j).OPRN_ID;
597
598 l_parent_entity_name(i) := 'OPERATION';
599 l_parent_entity_key(i) := TO_CHAR(p_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
600
601 l_child_entity_name(i) := 'OPERATION';
602 l_child_entity_key(i) := TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
603
604 -- Get all activity details for the operation
605 FOR l_rec_actv IN Get_activity_details(l_oprn_id)
606 LOOP
607 i := i + 1;
608 l_parent_entity_name(i) := 'ACTIVITY';
609 l_parent_entity_key(i) := TO_CHAR(p_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
610
611 l_child_entity_name(i) := 'ACTIVITY';
612 l_child_entity_key(i) := TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
613
614 -- Get all resource details for the activity
615 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
616 LOOP
617 i := i + 1;
618 l_parent_entity_name(i) := 'RESOURCE';
619 l_parent_entity_key(i) := TO_CHAR(p_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
620
621 l_child_entity_name(i) := 'RESOURCE';
622 l_child_entity_key(i) := TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
623 END LOOP;
624 END LOOP;
625 i := i + 1;
626 END LOOP;
627 END IF; -- IF l_rout_id IS NOT NULL
628
629 /* Copy formula level PI's to the recipe */
630 IF l_form_id IS NOT NULL THEN
631 FOR l_rec IN Get_formula_details(l_form_id)
632 LOOP
633 l_parent_entity_name(i) := 'MATERIAL';
634 l_parent_entity_key(i) := TO_CHAR(p_entity_id) || '$' || TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
635
636 l_child_entity_name(i) := 'MATERIAL';
637 l_child_entity_key(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
638
639 i := i + 1;
640 END LOOP;
641 END IF; -- IF l_form_id IS NOT NULL
642
643 ELSIF p_entity_name = 'ROUTING' THEN
644
645 i := 1;
646 FOR l_rec IN Get_routing_details(p_entity_id)
647 LOOP
648 l_parent_entity_name(i) := 'OPERATION';
649 l_parent_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
653 -- Get all activity details for the operation
650 l_child_entity_name(i) := 'OPERATION';
651 l_child_entity_key(i) := TO_CHAR(l_rec.oprn_id);
652
654 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
655 LOOP
656 i := i + 1;
657 l_parent_entity_name(i) := 'ACTIVITY';
658 l_parent_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
659 l_child_entity_name(i) := 'ACTIVITY';
660 l_child_entity_key(i) := TO_CHAR(l_rec_actv.oprn_line_id);
661
662 -- Get all resource details for the activity
663 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
664 LOOP
665 i := i + 1;
666 l_parent_entity_name(i) := 'RESOURCE';
667 l_parent_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
668 l_child_entity_name(i) := 'RESOURCE';
669 l_child_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
670 END LOOP;
671 END LOOP;
672 i := i + 1;
673 END LOOP;
674
675 END IF;
676
677 /* Call the GMO Create defn from defn API */
678 FOR j IN 1..l_parent_entity_name.COUNT
679 LOOP
680
681 GMO_INSTRUCTION_GRP.CREATE_DEFN_FROM_DEFN
682 (
683 P_API_VERSION => 1.0 ,
684 P_INIT_MSG_LIST => FND_API.G_FALSE ,
685 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL ,
686 P_SOURCE_ENTITY_NAME => l_child_entity_name(j) ,
687 P_SOURCE_ENTITY_KEY => l_child_entity_key(j) ,
688 P_TARGET_ENTITY_NAME => l_parent_entity_name(j) ,
689 P_TARGET_ENTITY_KEY => l_parent_entity_key(j) ,
690 P_INSTRUCTION_TYPE => l_instruction_type ,
691 X_INSTRUCTION_SET_ID => l_proc_instr_id ,
692 X_RETURN_STATUS => X_RETURN_STATUS ,
693 X_MSG_COUNT => X_MSG_COUNT ,
694 X_MSG_DATA => X_MSG_DATA
695 );
696
697 END LOOP;
698
699 END COPY_PROCESS_INSTR;
700
701 /*-----------------------------------------------------------------------------
702 -- NAME
703 -- COPY_PROCESS_INSTR_ROW
704 --
705 -- SYNOPSIS
706 -- Procedure COPY_PROCESS_INSTR_ROW
707 --
708 -- DESCRIPTION
709 -- This procedure is called to copy the process instructions of a single from
710 -- child entity to a parent entity
711 --
712 -- When a routing is updated by adding an operation, copy the PI's defined at
713 -- operation level to the routing-operation level
714 --
715 -- HISTORY
716 -- Kapil M 18-MAY-2006 Bug# 5173039
717 --------------------------------------------------------------------------------*/
718
719 PROCEDURE COPY_PROCESS_INSTR_ROW (
720 p_entity_name IN VARCHAR2 ,
721 p_entity_id IN NUMBER ,
722 x_return_status OUT NOCOPY VARCHAR2 ,
723 x_msg_count OUT NOCOPY NUMBER ,
724 x_msg_data OUT NOCOPY VARCHAR2 ) IS
725
726 /* Cursor to fetch activity details for an operation */
727 CURSOR Get_activity_details_t (v_oprn_id NUMBER) IS
728 SELECT oprn_line_id, activity
729 FROM gmd_operation_activities
730 WHERE oprn_id = v_oprn_id
731 ORDER BY oprn_line_id;
732
733 /* Cursor to fetch resource details for an activity */
734 CURSOR Get_resource_details_t (v_oprn_line_id NUMBER) IS
735 SELECT oprn_line_id, resources
736 FROM gmd_operation_resources
737 WHERE oprn_line_id = v_oprn_line_id
738 ORDER BY resources;
739
740 CURSOR Get_routingstep_details(v_routingstep_id NUMBER) IS
741 SELECT r.routingstep_id,r.oprn_id
742 FROM fm_rout_dtl r
743 WHERE r.routingstep_id = v_routingstep_id;
744
745
746 l_parent_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
747 l_parent_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
748 l_child_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
749 l_child_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
750
751 l_instruction_type VARCHAR2(10);
752 l_return_status VARCHAR2(10);
753 l_msg_count NUMBER;
754 l_msg_data VARCHAR2(2000);
755 l_proc_instr_id NUMBER;
756 i NUMBER;
757 j NUMBER;
758 l_routing_step_id NUMBER;
759 l_oprn_id NUMBER;
760
761 BEGIN
762
763 /* Default the process type to 'PROCESS' when called from NPD */
764 l_instruction_type := 'PROCESS';
765
766 IF p_entity_name = 'ROUTING' THEN
767 i := 1;
768 OPEN Get_routingstep_details(p_entity_id);
769 FETCH Get_routingstep_details INTO l_routing_step_id , l_oprn_id;
770 CLOSE Get_routingstep_details;
771
772 l_parent_entity_name(i) := 'OPERATION';
773 l_parent_entity_key(i) := TO_CHAR(l_routing_step_id) || '$' || TO_CHAR(l_oprn_id);
774 l_child_entity_name(i) := 'OPERATION';
775 l_child_entity_key(i) := TO_CHAR(l_oprn_id);
776
777
778 -- Get all activity details for the operation
779 FOR l_rec_actv IN Get_activity_details_t(l_oprn_id)
780 LOOP
781 i := i + 1;
782 l_parent_entity_name(i) := 'ACTIVITY';
783 l_parent_entity_key(i) := TO_CHAR(l_routing_step_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
784 l_child_entity_name(i) := 'ACTIVITY';
785 l_child_entity_key(i) := TO_CHAR(l_rec_actv.oprn_line_id);
786
787
788 -- Get all resource details for the activity
789 FOR l_rec_rsrc IN Get_resource_details_t(l_oprn_id)
790 LOOP
791 i := i + 1;
792 l_parent_entity_name(i) := 'RESOURCE';
793 l_parent_entity_key(i) := TO_CHAR(l_routing_step_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
794 l_child_entity_name(i) := 'RESOURCE';
798
795 l_child_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
796 END LOOP;
797 END LOOP;
799 FOR j IN 1..l_parent_entity_name.COUNT
800 LOOP
801 GMO_INSTRUCTION_GRP.CREATE_DEFN_FROM_DEFN
802 (
803 P_API_VERSION => 1.0 ,
804 P_COMMIT => 'F' ,
805 P_INIT_MSG_LIST => 'F' ,
806 P_VALIDATION_LEVEL => NULL ,
807 P_SOURCE_ENTITY_NAME => l_child_entity_name(j) ,
808 P_SOURCE_ENTITY_KEY => l_child_entity_key(j) ,
809 P_TARGET_ENTITY_NAME => l_parent_entity_name(j) ,
810 P_TARGET_ENTITY_KEY => l_parent_entity_key(j) ,
811 P_INSTRUCTION_TYPE => l_instruction_type ,
812 X_INSTRUCTION_SET_ID => l_proc_instr_id ,
813 X_RETURN_STATUS => l_RETURN_STATUS ,
814 X_MSG_COUNT => l_MSG_COUNT ,
815 X_MSG_DATA => l_MSG_DATA
816 );
817 END LOOP;
818
819 END IF;
820
821 END COPY_PROCESS_INSTR_ROW;
822
823
824
825 /*-------------------------------------------------------------------
826 -- NAME
827 -- SEND_PI_ACKN
828 --
829 -- SYNOPSIS
830 -- Procedure SEND_PI_ACKN
831 --
832 -- DESCRIPTION
833 -- This procedure is called to send acknowledgment to the PI framework
834 -- if version contrl is ON. The source and entity names and keys needs to
835 -- passed to copy the pending (current) changes from old entity to new entity.
836 --
837 --
838 -- HISTORY
839 -- Sriram 7/20/2005 Created for GMD-GMO Integration Build
840 --------------------------------------------------------------------*/
841
842 PROCEDURE SEND_PI_ACKN(
843 p_entity_name IN VARCHAR2 ,
844 p_INSTRUCTION_PROCESS_ID IN NUMBER ,
845 p_old_entity_id IN NUMBER ,
846 p_new_entity_id IN NUMBER ,
847 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
848 X_MSG_COUNT OUT NOCOPY NUMBER ,
849 X_MSG_DATA OUT NOCOPY VARCHAR2 ) IS
850
851 l_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
852 l_target_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
853 l_source_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
854 l_target_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
855
856 l_old_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
857 l_new_rout_dets GMD_RECIPE_FETCH_PUB.routing_step_tbl;
858 l_old_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
859 l_new_rout_id FM_ROUT_HDR.ROUTING_ID%TYPE;
860 l_old_form_id FM_FORM_MST.FORMULA_ID%TYPE;
861 l_new_form_id FM_FORM_MST.FORMULA_ID%TYPE;
862 l_oprn_id NUMBER;
863 l_oprn_no VARCHAR2(200);
864 l_msg_cnt NUMBER;
865 l_msg_data VARCHAR2(2000);
866 l_return_code VARCHAR2(20);
867 l_status VARCHAR2(30);
868 l_count NUMBER;
869 l_line_id NUMBER;
870 l_line_no NUMBER;
871 l_line_type NUMBER;
872 i NUMBER;
873 j NUMBER;
874
875 BEGIN
876
877 IF p_entity_name IN ('RECIPE', 'STEP_MAT') THEN
878
879 /* Get the old routing and formula id */
880 OPEN Get_recipe_details(p_old_entity_id);
881 FETCH Get_recipe_details INTO l_old_rout_id, l_old_form_id;
882 CLOSE Get_recipe_details;
883
884 /* Get the new routing and formula id */
885 OPEN Get_recipe_details(p_new_entity_id);
886 FETCH Get_recipe_details INTO l_new_rout_id, l_new_form_id;
887 CLOSE Get_recipe_details;
888
889 /* Set the Source entity name and key for Recipe*/
890 i := 1;
891 IF l_old_rout_id = l_new_rout_id THEN
892 GMD_RECIPE_FETCH_PUB.get_routing_step_details(
893 p_api_version => 1.0 ,
894 p_init_msg_list => 'F' ,
895 p_routing_id => l_old_rout_id ,
896 x_return_status => l_status ,
897 x_msg_count => l_msg_cnt ,
898 x_msg_data => l_msg_data ,
899 x_return_code => l_return_code ,
900 x_routing_step_out => l_old_rout_dets );
901
902 l_count := l_old_rout_dets.COUNT;
903
904 FOR j IN 1..l_count
905 LOOP
906 l_line_id := l_old_rout_dets(j).ROUTINGSTEP_ID;
907 l_oprn_id := l_old_rout_dets(j).OPRN_ID;
908
909 l_entity_name(i) := 'OPERATION';
910 l_source_entity_key(i) := TO_CHAR(p_old_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
911
912 -- Get all activity details for the operation
913 FOR l_rec_actv IN Get_activity_details(l_oprn_id)
914 LOOP
915 i := i + 1;
916 l_entity_name(i) := 'ACTIVITY';
917 l_source_entity_key(i) := TO_CHAR(p_old_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
918
919 -- Get all resource details for the activity
920 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
921 LOOP
922 i := i + 1;
923 l_entity_name(i) := 'RESOURCE';
924 l_source_entity_key(i) := TO_CHAR(p_old_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
925 END LOOP;
926 END LOOP;
927 i := i + 1;
928 END LOOP;
929 END IF; -- IF l_old_rout_id = l_new_rout_id THEN
930
931 IF l_old_form_id = l_new_form_id THEN
932 FOR l_rec IN Get_formula_details(l_old_form_id)
933 LOOP
934 l_entity_name(i) := 'MATERIAL';
935 l_source_entity_key(i) := TO_CHAR(p_old_entity_id) || '$' || TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
936 i := i + 1;
937 END LOOP;
938 END IF;
939
940 /* Set the Target entity name and key for Recipe*/
941
942 i := 1;
943 IF l_old_rout_id = l_new_rout_id THEN
944 GMD_RECIPE_FETCH_PUB.get_routing_step_details(
948 x_return_status => l_status ,
945 p_api_version => 1.0 ,
946 p_init_msg_list => 'F' ,
947 p_routing_id => l_new_rout_id ,
949 x_msg_count => l_msg_cnt ,
950 x_msg_data => l_msg_data ,
951 x_return_code => l_return_code ,
952 x_routing_step_out => l_new_rout_dets );
953
954 l_count := l_new_rout_dets.COUNT;
955
956 FOR j IN 1..l_count
957 LOOP
958 l_line_id := l_new_rout_dets(j).ROUTINGSTEP_ID;
959 l_oprn_id := l_new_rout_dets(j).OPRN_ID;
960
961 l_target_entity_name(i) := 'OPERATION';
962 l_target_entity_key(i) := TO_CHAR(p_new_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_oprn_id);
963
964 -- Get all activity details for the operation
965 FOR l_rec_actv IN Get_activity_details(l_oprn_id)
966 LOOP
967 i := i + 1;
968 l_target_entity_name(i) := 'ACTIVITY';
969 l_target_entity_key(i) := TO_CHAR(p_new_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
970
971 -- Get all resource details for the activity
972 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
973 LOOP
974 i := i + 1;
975 l_target_entity_name(i) := 'RESOURCE';
976 l_target_entity_key(i) := TO_CHAR(p_new_entity_id) || '$' || TO_CHAR(l_line_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
977 END LOOP;
978 END LOOP;
979 i := i + 1;
980 END LOOP;
981 END IF; -- IF l_old_rout_id = l_new_rout_id THEN
982
983 IF l_old_form_id = l_new_form_id THEN
984 FOR l_rec IN Get_formula_details(l_new_form_id)
985 LOOP
986 l_target_entity_name(i) := 'MATERIAL';
987 l_target_entity_key(i) := TO_CHAR(p_new_entity_id) || '$' || TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
988 i := i + 1;
989 END LOOP;
990 END IF;
991
992 ELSIF p_entity_name = 'FORMULA' THEN
993
994 /* Set the Source entity name and key for Formula*/
995 i := 1;
996 FOR l_rec IN Get_formula_details(p_old_entity_id)
997 LOOP
998 l_entity_name(i) := 'MATERIAL';
999 l_source_entity_key(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
1000 i := i + 1;
1001 END LOOP;
1002
1003 /* Set the Target entity name and key for Formula*/
1004 i := 1;
1005 FOR l_rec IN Get_formula_details(p_new_entity_id)
1006 LOOP
1007 l_target_entity_name(i) := 'MATERIAL';
1008 l_target_entity_key(i) := TO_CHAR(l_rec.FORMULALINE_ID) || '$' || TO_CHAR(l_rec.INVENTORY_ITEM_ID);
1009 i := i + 1;
1010 END LOOP;
1011
1012 ELSIF p_entity_name = 'OPERATION' THEN
1013
1014 /* Set the Source entity name and key for Operation*/
1015 i := 1;
1016 l_entity_name(i) := 'OPERATION';
1017 l_source_entity_key(i) := TO_CHAR(p_old_entity_id);
1018 i := i + 1;
1019
1020 -- Get all activity details for the operation
1021 FOR l_rec IN Get_activity_details(p_old_entity_id)
1022 LOOP
1023 l_entity_name(i) := 'ACTIVITY';
1024 l_source_entity_key(i) := TO_CHAR(l_rec.oprn_line_id);
1025
1026 -- Get all resource details for the activity
1027 FOR l_rec_rsrc IN Get_resource_details(l_rec.oprn_line_id)
1028 LOOP
1029 i := i + 1;
1030 l_entity_name(i) := 'RESOURCE';
1031 l_source_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
1032 END LOOP;
1033 i := i + 1;
1034 END LOOP;
1035
1036 /* Set the Target entity name and key for Operation*/
1037
1038 i := 1;
1039 l_target_entity_name(i) := 'OPERATION';
1040 l_target_entity_key(i) := TO_CHAR(p_new_entity_id);
1041 i := i + 1;
1042
1043 -- Get all activity details for the operation
1044 FOR l_rec IN Get_activity_details(p_new_entity_id)
1045 LOOP
1046 l_target_entity_name(i) := 'ACTIVITY';
1047 l_target_entity_key(i) := TO_CHAR(l_rec.oprn_line_id);
1048
1049 -- Get all resource details for the activity
1050 FOR l_rec_rsrc IN Get_resource_details(l_rec.oprn_line_id)
1051 LOOP
1052 i := i + 1;
1053 l_target_entity_name(i) := 'RESOURCE';
1054 l_target_entity_key(i) := TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
1055 END LOOP;
1056 i := i + 1;
1057 END LOOP;
1058
1059 ELSIF p_entity_name = 'ROUTING' THEN
1060
1061 /* Set the Source entity name and key for Routing */
1062
1063 i := 1;
1064 FOR l_rec IN Get_routing_details(p_old_entity_id)
1065 LOOP
1066 l_entity_name(i) := 'OPERATION';
1067 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
1068
1069 -- Get all activity details for the operation
1070 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
1071 LOOP
1072 i := i + 1;
1073 l_entity_name(i) := 'ACTIVITY';
1074 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
1075
1076 -- Get all resource details for the activity
1077 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
1078 LOOP
1079 i := i + 1;
1080 l_entity_name(i) := 'RESOURCE';
1081 l_source_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
1082 END LOOP;
1083 END LOOP;
1084 i := i + 1;
1085 END LOOP;
1086
1087 /* Set the Target entity name and key for Routing */
1088
1089 i := 1;
1090 FOR l_rec IN Get_routing_details(p_new_entity_id)
1091 LOOP
1092 l_target_entity_name(i) := 'OPERATION';
1093 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec.oprn_id);
1094
1095 -- Get all activity details for the operation
1096 FOR l_rec_actv IN Get_activity_details(l_rec.oprn_id)
1100 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_actv.oprn_line_id);
1097 LOOP
1098 i := i + 1;
1099 l_target_entity_name(i) := 'ACTIVITY';
1101
1102 -- Get all resource details for the activity
1103 FOR l_rec_rsrc IN Get_resource_details(l_rec_actv.oprn_line_id)
1104 LOOP
1105 i := i + 1;
1106 l_target_entity_name(i) := 'RESOURCE';
1107 l_target_entity_key(i) := TO_CHAR(l_rec.routingstep_id) || '$' || TO_CHAR(l_rec_rsrc.oprn_line_id) || '$' || l_rec_rsrc.resources;
1108 END LOOP;
1109 END LOOP;
1110 i := i + 1;
1111 END LOOP;
1112
1113 END IF;
1114
1115 -- Send the acknowledgment for the new entity created by version control
1116 GMO_INSTRUCTION_GRP.SEND_DEFN_ACKN(
1117 P_API_VERSION => 1.0 ,
1118 P_INIT_MSG_LIST => FND_API.G_FALSE ,
1119 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL ,
1120 X_RETURN_STATUS => X_RETURN_STATUS ,
1121 X_MSG_COUNT => X_MSG_COUNT ,
1122 X_MSG_DATA => X_MSG_DATA ,
1123 P_INSTRUCTION_PROCESS_ID => p_INSTRUCTION_PROCESS_ID ,
1124 P_ENTITY_NAME => l_entity_name ,
1125 P_SOURCE_ENTITY_KEY => l_source_entity_key ,
1126 P_TARGET_ENTITY_KEY => l_target_entity_key
1127 );
1128
1129 END SEND_PI_ACKN;
1130
1131 --API related Designer.
1132
1133 FUNCTION GET_DESG_INVOKE_PI_ID(p_entity_type VARCHAR2)
1134 /*-------------------------------------------------------------------
1135 -- NAME
1136 -- GET_DESG_INVOKE_PI_ID
1137 -- DESCRIPTION
1138 --
1139 --
1140 -- HISTORY
1141 -- kkillams 20-SEP-2005 Created for GMD-GMO Integration Build
1142 -------------------------------------------------------------------*/
1143 RETURN NUMBER AS
1144 BEGIN
1145 IF p_entity_type = 'FORMULA' THEN
1146 RETURN p_formula_instr_process_id;
1147 ELSIF p_entity_type = 'ROUTING' THEN
1148 RETURN p_routing_instr_process_id;
1149 ELSIF p_entity_type = 'RECIPE' THEN
1150 RETURN p_recipe_instr_process_id;
1151 ELSIF p_entity_type = 'STEP_MAT' THEN
1152 RETURN p_setp_instr_process_id;
1153 END IF;
1154 END;
1155 PROCEDURE SET_DESG_INVOKE_PI_ID(p_entity_type VARCHAR2,
1156 p_pi_entity_id NUMBER) AS
1157 /*-------------------------------------------------------------------
1158 -- NAME
1159 -- DESG_SEND_PI_ACKN
1160 -- DESCRIPTION
1161 --
1162 --
1163 -- HISTORY
1164 -- kkillams 20-SEP-2005 Created for GMD-GMO Integration Build
1165 -------------------------------------------------------------------*/
1166 BEGIN
1167 IF p_entity_type = 'FORMULA' THEN
1168 p_formula_instr_process_id := p_pi_entity_id;
1169 ELSIF p_entity_type = 'ROUTING' THEN
1170 p_routing_instr_process_id := p_pi_entity_id;
1171 ELSIF p_entity_type = 'RECIPE' THEN
1172 p_recipe_instr_process_id := p_pi_entity_id;
1173 ELSIF p_entity_type = 'STEP_MAT' THEN
1174 p_setp_instr_process_id := p_pi_entity_id;
1175 END IF;
1176 END;
1177
1178 PROCEDURE DESG_SEND_PI_ACKN(p_return_status OUT NOCOPY VARCHAR2) AS
1179 /*-------------------------------------------------------------------
1180 -- NAME
1181 -- DESG_SEND_PI_ACKN
1182 -- DESCRIPTION
1183 --
1184 --
1185 -- HISTORY
1186 -- kkillams 20-SEP-2005 Created for GMD-GMO Integration Build
1187 -------------------------------------------------------------------*/
1188 l_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1189 l_src_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1190 l_target_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1191 l_msg_count NUMBER;
1192 l_msg_data VARCHAR2(2000);
1193 l_return_status VARCHAR2(10);
1194
1195 BEGIN
1196 p_return_status := 'S';
1197 IF gmd_process_instr_utils.p_recipe_instr_process_id IS NOT NULL THEN
1198 gmo_instruction_pvt.send_defn_ackn
1199 ( p_instruction_process_id => gmd_process_instr_utils.p_recipe_instr_process_id,
1200 p_entity_name => l_entity_name,
1201 p_source_entity_key => l_src_entity_key,
1202 p_target_entity_key => l_target_entity_key,
1203 x_return_status => l_return_status,
1204 x_msg_count => l_msg_count,
1205 x_msg_data => l_msg_data );
1206 IF l_return_status <> 'S' THEN
1207 p_return_status := 'E';
1208 END IF;
1209 END IF;
1210 IF gmd_process_instr_utils.p_formula_instr_process_id IS NOT NULL THEN
1211 gmo_instruction_pvt.send_defn_ackn
1212 ( p_instruction_process_id => gmd_process_instr_utils.p_formula_instr_process_id,
1213 p_entity_name => l_entity_name,
1214 p_source_entity_key => l_src_entity_key,
1215 p_target_entity_key => l_target_entity_key,
1216 x_return_status => l_return_status,
1217 x_msg_count => l_msg_count,
1218 x_msg_data => l_msg_data );
1219 IF l_return_status <> 'S' THEN
1220 p_return_status := 'E';
1221 END IF;
1222 END IF;
1223 IF gmd_process_instr_utils.p_routing_instr_process_id IS NOT NULL THEN
1224 gmo_instruction_pvt.send_defn_ackn
1225 ( p_instruction_process_id => gmd_process_instr_utils.p_routing_instr_process_id,
1226 p_entity_name => l_entity_name,
1227 p_source_entity_key => l_src_entity_key,
1228 p_target_entity_key => l_target_entity_key,
1229 x_return_status => l_return_status,
1230 x_msg_count => l_msg_count,
1231 x_msg_data => l_msg_data );
1232 IF l_return_status <> 'S' THEN
1233 p_return_status := 'E';
1234 END IF;
1235 END IF;
1236 IF gmd_process_instr_utils.p_setp_instr_process_id IS NOT NULL THEN
1237 gmo_instruction_pvt.send_defn_ackn
1238 ( p_instruction_process_id => gmd_process_instr_utils.p_setp_instr_process_id,
1239 p_entity_name => l_entity_name,
1240 p_source_entity_key => l_src_entity_key,
1241 p_target_entity_key => l_target_entity_key,
1242 x_return_status => l_return_status,
1243 x_msg_count => l_msg_count,
1244 x_msg_data => l_msg_data );
1245 IF l_return_status <> 'S' THEN
1246 p_return_status := 'E';
1247 END IF;
1248 END IF;
1249
1250 p_recipe_instr_process_id := NULL;
1251 p_formula_instr_process_id := NULL;
1252 p_routing_instr_process_id := NULL;
1253 p_setp_instr_process_id := NULL;
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 p_return_status := 'U';
1258 END DESG_SEND_PI_ACKN;
1259
1260 /*-------------------------------------------------------------------
1261 -- NAME
1262 -- DESG_SEND_VER_PI_ACKN
1263 -- DESCRIPTION
1264 --
1265 --
1266 -- HISTORY
1267 -- kkillams 20-SEP-2005 Created for GMD-GMO Integration Build
1268 --------------------------------------------------------------------*/
1269 PROCEDURE DESG_SEND_VER_PI_ACKN(p_from_recipe_id IN VARCHAR2,
1270 p_from_formula_id IN VARCHAR2,
1271 p_from_routing_id IN VARCHAR2,
1272 p_to_recipe_id IN VARCHAR2,
1273 p_return_status OUT NOCOPY VARCHAR2) AS
1274 CURSOR Cur_get_rcp_hdr(cp_recipe_id NUMBER) IS
1275 SELECT formula_id, routing_id ,recipe_id
1276 FROM gmd_recipes
1277 WHERE recipe_id = cp_recipe_id;
1278
1279 rec_from_recipe Cur_get_rcp_hdr%ROWTYPE;
1280 rec_to_recipe Cur_get_rcp_hdr%ROWTYPE;
1281
1282 l_entity_name GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1283 l_src_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1284 l_target_entity_key GMO_DATATYPES_GRP.GMO_TABLE_OF_VARCHAR2_255;
1285 l_msg_count NUMBER;
1286 l_msg_data VARCHAR2(2000);
1287 l_return_status VARCHAR2(10);
1288
1289 BEGIN
1290
1291 rec_from_recipe.recipe_id := p_from_recipe_id;
1292 rec_from_recipe.formula_id := p_from_formula_id;
1293 rec_from_recipe.routing_id := p_from_routing_id;
1294
1295 OPEN Cur_get_rcp_hdr(p_to_recipe_id);
1296 FETCH Cur_get_rcp_hdr INTO rec_to_recipe;
1297 CLOSE Cur_get_rcp_hdr;
1298
1299 IF ( p_recipe_instr_process_id IS NOT NULL AND
1300 ( rec_from_recipe.recipe_id <> rec_to_recipe.recipe_id )) THEN
1301 GMD_PROCESS_INSTR_UTILS.SEND_PI_ACKN(
1302 p_entity_name => 'RECIPE' ,
1303 p_INSTRUCTION_PROCESS_ID => p_recipe_instr_process_id ,
1304 p_old_entity_id => rec_from_recipe.recipe_id ,
1305 p_new_entity_id => rec_to_recipe.recipe_id ,
1306 X_RETURN_STATUS => l_return_status ,
1307 X_MSG_COUNT => l_msg_count ,
1308 X_MSG_DATA => l_msg_data );
1309 IF l_return_status <> 'S' THEN
1310 p_return_status := 'E';
1311 END IF;
1312 p_recipe_instr_process_id := NULL;
1313 END IF;
1314 IF ( p_formula_instr_process_id IS NOT NULL AND
1315 ( rec_from_recipe.formula_id <> rec_to_recipe.formula_id )) THEN
1316 GMD_PROCESS_INSTR_UTILS.SEND_PI_ACKN(
1317 p_entity_name => 'FORMULA' ,
1318 p_INSTRUCTION_PROCESS_ID => p_formula_instr_process_id ,
1319 p_old_entity_id => rec_from_recipe.formula_id ,
1320 p_new_entity_id => rec_to_recipe.formula_id ,
1321 X_RETURN_STATUS => l_return_status ,
1322 X_MSG_COUNT => l_msg_count ,
1323 X_MSG_DATA => l_msg_data );
1324
1325 IF l_return_status <> 'S' THEN
1326 p_return_status := 'E';
1327 END IF;
1328 p_formula_instr_process_id := NULL;
1329 END IF;
1330
1331 IF ( p_routing_instr_process_id IS NOT NULL AND
1332 ( rec_from_recipe.routing_id <> rec_to_recipe.routing_id )) THEN
1333 GMD_PROCESS_INSTR_UTILS.SEND_PI_ACKN(
1334 p_entity_name => 'ROUTING' ,
1335 p_INSTRUCTION_PROCESS_ID => p_routing_instr_process_id ,
1336 p_old_entity_id => rec_from_recipe.routing_id ,
1337 p_new_entity_id => rec_to_recipe.routing_id ,
1338 X_RETURN_STATUS => l_return_status ,
1339 X_MSG_COUNT => l_msg_count ,
1340 X_MSG_DATA => l_msg_data );
1341
1342 IF l_return_status <> 'S' THEN
1343 p_return_status := 'E';
1344 END IF;
1345 p_routing_instr_process_id := NULL;
1346 END IF;
1347
1348 DESG_SEND_PI_ACKN (p_return_status => p_return_status);
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 p_return_status := 'U';
1352 END DESG_SEND_VER_PI_ACKN;
1353
1354 END GMD_PROCESS_INSTR_UTILS;