[Home] [Help]
PACKAGE BODY: APPS.GMO_VBATCH_PVT
Source
1 PACKAGE BODY GMO_VBATCH_PVT AS
2 /* $Header: GMOVVBTB.pls 120.27.12020000.2 2012/07/17 09:16:14 maychen ship $ */
3
4 GMO_INVALID_ENTITY_ERR exception;
5 GMO_DISABLED_ERR EXCEPTION;
6 GMO_NOT_ENHANCED_PI_ERR EXCEPTION;
7 GMO_INVALID_RECIPE_ERR EXCEPTION;
8
9 function is_batch_enhanced_pi (P_ENTITY_NAME IN VARCHAR2, P_ENTITY_KEY IN VARCHAR2) RETURN BOOLEAN
10 IS
11
12 l_batch_id number;
13 NO_BATCH_FOUND_ERR exception;
14 cursor is_enhanced_pi is select count(*) from gme_batch_header where enhanced_pi_ind = 'Y' and batch_id = l_batch_id;
15 l_count number;
16 l_return_status varchar2(100);
17 l_msg_count number;
18 l_msg_data varchar2(4000);
19
20 BEGIN
21
22 get_batch_id_for_entity
23 (
24 p_entity_name => p_entity_name,
25 p_entity_key => p_entity_key,
26 x_batch_id => l_batch_id,
27 x_return_status => l_return_status,
28 x_msg_count => l_msg_count,
29 x_msg_data => l_msg_data
30 );
31 if L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
32 RAISE NO_BATCH_FOUND_ERR;
33 end if;
34
35 open is_enhanced_pi;
36 fetch is_enhanced_pi into l_count;
37 close is_enhanced_pi;
38
39 IF (l_count = 0) THEN
40 return false;
41 ELSE
42 return true;
43 END IF;
44 END is_batch_enhanced_pi;
45
46 --This function would verify if the step is locked or not.
47
48 function IS_STEP_LOCKED (P_BATCHSTEP_ID IN NUMBER,
49 P_REQUESTER IN NUMBER DEFAULT NULL) RETURN VARCHAR2
50 IS
51
52 l_count number;
53 l_locked varchar2(1);
54 cursor is_step_locked_by_user is select count(*) from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id and lock_requester = p_requester;
55 cursor is_step_locked is select count(*) from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id;
56
57 BEGIN
58
59 IF (P_REQUESTER is null) THEN
60 open is_step_locked;
61 fetch is_step_locked into l_count;
62 close is_step_locked;
63 ELSE
64 open is_step_locked_by_user;
65 fetch is_step_locked_by_user into l_count;
66 close is_step_locked_by_user;
67 END IF;
68
69 IF (l_count = 0) THEN
70 l_locked := GMO_CONSTANTS_GRP.NO;
71 ELSE
72 l_locked := GMO_CONSTANTS_GRP.YES;
73 END IF;
74
75 return l_locked;
76
77 END IS_STEP_LOCKED;
78
79 function IS_STEP_LOCKED (P_VBATCH_MODE IN VARCHAR2,
80 P_BATCHSTEP_ID IN NUMBER,
81 P_REQUESTER IN NUMBER) RETURN VARCHAR2
82 IS
83 l_locked varchar2(1);
84
85 BEGIN
86 IF (P_VBATCH_MODE = GMO_CONSTANTS_GRP.VBATCH_ADMIN_MODE) then
87 l_locked := IS_STEP_LOCKED (P_BATCHSTEP_ID => P_BATCHSTEP_ID);
88 else
89 l_locked := IS_STEP_LOCKED (P_BATCHSTEP_ID => P_BATCHSTEP_ID, P_REQUESTER => P_REQUESTER);
90 end if;
91 return l_locked;
92
93 END IS_STEP_LOCKED;
94
95 --This function would return the reserved quantity for the material.
96
97 function GET_MATERIAL_RESERVATION_QTY (P_ORGANIZATION_ID IN NUMBER,
98 P_BATCH_ID IN NUMBER,
99 P_MATERIAL_DETAIL_ID IN NUMBER) RETURN NUMBER
100 IS
101
102 MATERIAL_RESERVATION_ERR EXCEPTION;
103
104 l_return_status varchar2(10);
105 l_reserved_quantity number;
106 l_reservation_tbl GME_COMMON_PVT.reservations_tab;
107 l_msg_count number;
108 l_msg_data varchar2(4000);
109 l_detail_uom varchar2(3);
110 l_reserved_quantity_t number;
111
112 BEGIN
113 l_reserved_quantity := 0;
114 l_reserved_quantity_t := 0;
115
116 GME_API_GRP.GET_MATERIAL_RESERVATIONS (
117 X_MSG_COUNT => l_msg_count,
118 X_MSG_DATA => l_msg_data,
119 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
120 P_BATCH_ID => P_BATCH_ID,
121 P_MATERIAL_DETAIL_ID => P_MATERIAL_DETAIL_ID,
122 X_RETURN_STATUS => l_return_status,
123 X_RESERVATIONS_TBL => l_reservation_tbl
124 );
125
126 if L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
127 RAISE MATERIAL_RESERVATION_ERR;
128 end if;
129
130 --Bug 4609365: start
131 select dtl_um into l_detail_uom from gme_material_details where material_detail_id = P_MATERIAL_DETAIL_ID;
132 --Bug 4609365: end
133
134 for i in 1 .. l_reservation_tbl.count loop
135 --Bug 4609365: start
136 l_reserved_quantity_t := 0;
137 if ( l_detail_uom is not null and l_detail_uom <> l_reservation_tbl(i).primary_uom_code) then
138 /* It is safe to assume that conversion will not fail because this is an internal
139 procedure and will only be called where conversion do exist and l_detail_uom is not null.
140 Therefore no need to check the failure (-99999 return value)
141 */
142 l_reserved_quantity_t := inv_convert.inv_um_convert
143 (
144 ITEM_ID => l_reservation_tbl(i).inventory_item_id,
145 LOT_NUMBER => l_reservation_tbl(i).lot_number,
146 ORGANIZATION_ID => l_reservation_tbl(i).organization_id,
147 PRECISION => 5,
148 FROM_QUANTITY => l_reservation_tbl(i).PRIMARY_RESERVATION_QUANTITY,
149 FROM_UNIT => l_reservation_tbl(i).primary_uom_code,
150 TO_UNIT => l_detail_uom,
151 FROM_NAME => null,
152 TO_NAME => null
153 );
154 else
155 l_reserved_quantity_t := l_reservation_tbl(i).PRIMARY_RESERVATION_QUANTITY;
156 end if;
157 --l_reserved_quantity := l_reserved_quantity + l_reservation_tbl(i).PRIMARY_RESERVATION_QUANTITY;
158 l_reserved_quantity := l_reserved_quantity + l_reserved_quantity_t;
159 --Bug 4609365: end
160 end loop;
161
162 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
163
164 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_MAT_RSRV_QTY_MSG');
165 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',P_ORGANIZATION_ID);
166 FND_MESSAGE.SET_TOKEN('BATCH_ID',P_BATCH_ID);
167 FND_MESSAGE.SET_TOKEN('MATERIAL_DETAIL_ID',P_MATERIAL_DETAIL_ID);
168 FND_MESSAGE.SET_TOKEN('RESERVED_QUANTITY',l_reserved_quantity);
169
170 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.get_material_reservation_qty', FALSE);
171 end if;
172
173 return l_reserved_quantity;
174
175 EXCEPTION
176 WHEN MATERIAL_RESERVATION_ERR THEN
177
178 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_MAT_RSRV_QTY_ERR');
179 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',P_ORGANIZATION_ID);
180 FND_MESSAGE.SET_TOKEN('BATCH_ID',P_BATCH_ID);
181 FND_MESSAGE.SET_TOKEN('MATERIAL_DETAIL_ID',P_MATERIAL_DETAIL_ID);
182 FND_MESSAGE.SET_TOKEN('RESERVED_QUANTITY',l_reserved_quantity);
183 FND_MESSAGE.SET_TOKEN('GME_RETURN_STATUS', L_RETURN_STATUS);
184 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
185 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_material_reservation_qty', FALSE);
186 end if;
187 APP_EXCEPTION.RAISE_EXCEPTION;
188 WHEN OTHERS THEN
189 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
190 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
191 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
192 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
193 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.get_material_reservation_qty', FALSE);
194 end if;
195 APP_EXCEPTION.RAISE_EXCEPTION;
196 END GET_MATERIAL_RESERVATION_QTY;
197
198 --Bug 4609365: start
199 --This function is a wrapper function over GMO_DISPENSE_PVT.GET_NET_MTL_DISPENSED_QTY
200 FUNCTION GET_MATERIAL_DISPENSED_QTY (P_MATERIAL_DETAIL_ID NUMBER, P_UOM VARCHAR2) RETURN NUMBER
201 IS
202
203 BEGIN
204
205 return GMO_DISPENSE_PVT.GET_NET_MTL_DISPENSED_QTY (P_MATERIAL_DETAIL_ID => P_MATERIAL_DETAIL_ID, P_UOM => P_UOM);
206
207 END GET_MATERIAL_DISPENSED_QTY;
208 --Bug 4609365: end
209 --Bug : start
210 --This function is return actual quantity amidst of transactions
211 --function get_actual_quantity(P_MATERIAL_DETAIL_ID NUMBER, P_INVENTORY_ITEM_ID NUMBER) RETURN NUMBER;
212 --
213
214 function get_actual_quantity(p_material_detail_id NUMBER, p_inventory_item_id NUMBER) RETURN NUMBER
215 IS
216
217 l_actual_quantity NUMBER;
218 l_transaction_quantity NUMBER :=0;
219 cv_transaction_qty NUMBER :=0;
220 l_line_type NUMBER;
221 l_total_actual_quantity NUMBER;
222
223 CURSOR c_transaction_qty(cp_inventory_item_id NUMBER,cp_material_detail_id NUMBER) IS
224 SELECT transaction_quantity
225 FROM mtl_material_transactions_temp
226 WHERE inventory_item_id=cp_inventory_item_id
227 AND trx_source_line_id= cp_material_detail_id
228 AND transaction_source_type_id=5 ;
229
230
231 BEGIN
232
233 SELECT actual_qty ,line_type
234 INTO l_actual_quantity,l_line_type
235 FROM gme_material_details
236 WHERE material_detail_id=P_MATERIAL_DETAIL_ID
237 AND inventory_item_id= P_INVENTORY_ITEM_ID;
238
239 OPEN c_transaction_qty(P_INVENTORY_ITEM_ID,P_MATERIAL_DETAIL_ID);
240 LOOP
241 FETCH c_transaction_qty INTO cv_transaction_qty;
242 EXIT WHEN c_transaction_qty%NOTFOUND;
243 IF(l_line_type=-1) THEN
244 cv_transaction_qty := cv_transaction_qty*(-1); -- Ingredients
245 END IF;
246 l_transaction_quantity := l_transaction_quantity+cv_transaction_qty;
247 END LOOP;
248 CLOSE c_transaction_qty;
249 l_total_actual_quantity := l_actual_quantity+l_transaction_quantity;
250
251 RETURN l_total_actual_quantity;
252
253 END get_actual_quantity;
254
255 --This procedure would verify if batch step material is available.
256
257 PROCEDURE GET_STEP_MATERIAL_AVAILABILITY (P_BATCHSTEP_ID IN NUMBER,
258 X_MATERIAL_AVAILABLE OUT NOCOPY VARCHAR2,
259 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
260 X_MSG_COUNT OUT NOCOPY NUMBER,
261 X_MSG_DATA OUT NOCOPY VARCHAR2)
262 IS
263 l_unavail_mat fnd_table_of_varchar2_255;
264 BEGIN
265 GET_STEP_MATERIAL_AVAILABILITY (
266 P_BATCHSTEP_ID => P_BATCHSTEP_ID,
267 X_MATERIAL_AVAILABLE => X_MATERIAL_AVAILABLE,
268 X_UNAVAL_MATERIAL_ID => l_unavail_mat,
269 X_RETURN_STATUS => X_RETURN_STATUS,
270 X_MSG_COUNT => X_MSG_COUNT,
271 X_MSG_DATA => X_MSG_DATA
272 );
273
274 END;
275
276 PROCEDURE GET_STEP_MATERIAL_AVAILABILITY (P_BATCHSTEP_ID IN NUMBER,
277 X_MATERIAL_AVAILABLE OUT NOCOPY VARCHAR2,
278 X_UNAVAL_MATERIAL_ID OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
279 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
280 X_MSG_COUNT OUT NOCOPY NUMBER,
281 X_MSG_DATA OUT NOCOPY VARCHAR2)
282
283 IS
284
285 l_organization_id number;
286 l_batch_id number;
287 l_return_status varchar2(10);
288 l_exception_tbl gme_common_pvt.exceptions_tab;
289 l_material_detail_id number;
290 l_count number;
291 l_material_unavailable number;
292 l_material_available varchar2(1);
293 BATCH_SHORTAGE_ERR exception;
294 l_msg_count number;
295 l_msg_data varchar2(4000);
296 k number;
297
298 cursor c_get_batch_details is select a.batch_id, a.organization_id from gme_batch_header a, gme_batch_steps b where a.batch_id = b.batch_id and b.batchstep_id = p_batchstep_id;
299 cursor c_is_material_reqd_for_step is select count(*) from gme_batch_step_items where material_detail_id = l_material_detail_id and batchstep_id = p_batchstep_id;
300 BEGIN
301
302 open c_get_batch_details;
303 fetch c_get_batch_details into l_batch_id, l_organization_id;
304 close c_get_batch_details;
305
306 l_count := 0;
307 l_material_unavailable := 0;
308
309
310 GME_API_GRP.GET_BATCH_SHORTAGES(
311 X_MSG_COUNT => l_msg_count,
312 X_MSG_DATA => l_msg_data,
313 P_ORGANIZATION_ID => l_organization_id,
314 P_BATCH_ID => l_batch_id,
315 P_INVOKE_MODE => 'O',
316 P_TREE_MODE => 2,
317 X_RETURN_STATUS => l_return_status,
318 X_EXCEPTION_TBL => l_exception_tbl
319 );
320
321 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
322 RAISE BATCH_SHORTAGE_ERR;
323 end if;
324
325 X_UNAVAL_MATERIAL_ID := new FND_TABLE_OF_VARCHAR2_255();
326 k := 0;
327
328 for i in 1 .. l_exception_tbl.count loop
329 l_material_detail_id := l_exception_tbl(i).MATERIAL_DETAIL_ID;
330 open c_is_material_reqd_for_step;
331 fetch c_is_material_reqd_for_step into l_count;
332 close c_is_material_reqd_for_step;
333
334 if (l_count > 0) then
335 X_UNAVAL_MATERIAL_ID.extend;
336 k := k+1;
337 X_UNAVAL_MATERIAL_ID(k) := l_material_detail_id;
338 l_material_unavailable := l_material_unavailable + 1;
339 end if;
340 end loop;
341
342 if (l_material_unavailable > 0) then
343 l_material_available := GMO_CONSTANTS_GRP.NO;
344 else
345 l_material_available := GMO_CONSTANTS_GRP.YES;
346 end if;
347
348 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
349
350 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_STEP_MAT_AVBL_MSG');
351 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',P_BATCHSTEP_ID);
352 FND_MESSAGE.SET_TOKEN('MATERIAL_AVAILABLE',l_material_available);
353 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.batchstep_material_available', FALSE);
354 end if;
355
356 X_MATERIAL_AVAILABLE := l_material_available;
357
358 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
359 EXCEPTION
360 WHEN BATCH_SHORTAGE_ERR THEN
361 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
362
363 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_STEP_MAT_AVBL_ERR');
364 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',P_BATCHSTEP_ID);
365 FND_MESSAGE.SET_TOKEN('MATERIAL_AVAILABLE',l_material_available);
366 FND_MESSAGE.SET_TOKEN('GME_RETURN_STATUS', L_RETURN_STATUS);
367 FND_MSG_PUB.ADD;
368 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
369
370 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
371 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.batchstep_material_available', FALSE);
372 end if;
373
374 WHEN OTHERS THEN
375
376 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
377 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
378 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
379 FND_MESSAGE.SET_TOKEN('ERROR_CODE',SQLCODE);
380 FND_MSG_PUB.ADD;
381 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
382 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.batchstep_material_available', FALSE);
383 end if;
384 END GET_STEP_MATERIAL_AVAILABILITY;
385
386
387
388 --This function would return the name for the user who has locked the step.
389
390 function GET_STEP_LOCKED_BY (P_BATCHSTEP_ID IN NUMBER) RETURN VARCHAR2
391
392 IS
393
394 cursor c_get_step_lock_by is select lock_requester from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id;
395 l_user_id number;
396 l_user_display_name varchar2(1000);
397 begin
398
399 open c_get_step_lock_by;
400 fetch c_get_step_lock_by into l_user_id;
401 close c_get_step_lock_by;
402
403 return gmo_utilities.get_user_display_name (p_user_id => l_user_id);
404
405 END GET_STEP_LOCKED_BY;
406
407
408 --This procdeure would lock the step.
409 procedure LOCK_STEP (P_BATCHSTEP_ID IN NUMBER,
410 P_REQUESTER IN NUMBER,
411 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
412 X_MSG_COUNT OUT NOCOPY NUMBER,
413 X_MSG_DATA OUT NOCOPY VARCHAR2)
414
415 IS PRAGMA AUTONOMOUS_TRANSACTION;
416
417 l_batch_id number;
418 cursor c_get_batch_id is select batch_id from gme_batch_steps where batchstep_id = P_BATCHSTEP_ID;
419 STEP_ALREADY_LOCKED_ERR exception;
420 NO_BATCH_FOUND_ERR exception;
421
422 BEGIN
423 IF (IS_STEP_LOCKED (P_BATCHSTEP_ID => P_BATCHSTEP_ID, P_REQUESTER => P_REQUESTER) = 'Y') THEN
424 RAISE STEP_ALREADY_LOCKED_ERR;
425 END IF;
426
427 open c_get_batch_id;
428 fetch c_get_batch_id into l_batch_id;
429 close c_get_batch_id;
430
431 IF (l_batch_id is null) THEN
432 RAISE NO_BATCH_FOUND_ERR;
433 END IF;
434
435
436 INSERT into GMO_BATCH_STEP_LOCK_DETAILS (BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
437 VALUES (l_batch_id, P_BATCHSTEP_ID, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);
438
439 commit;
440
441 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
442 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_LOCKSTEP_REQ_MSG');
443 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',P_BATCHSTEP_ID);
444 FND_MESSAGE.SET_TOKEN('REQUESTER',P_REQUESTER);
445 FND_MESSAGE.SET_TOKEN('REQUEST','LOCK');
446 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
447 end if;
448
449 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
450
451 EXCEPTION
452 WHEN STEP_ALREADY_LOCKED_ERR THEN
453 rollback;
454 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
455 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_LOCK_ERR');
456 FND_MSG_PUB.ADD;
457 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
458 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
459 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
460 end if;
461 WHEN NO_BATCH_FOUND_ERR THEN
462 rollback;
463 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
464 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
465 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','OPERATION');
466 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_BATCHSTEP_ID);
467 FND_MSG_PUB.ADD;
468 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
469 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
470 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
471 end if;
472 WHEN OTHERS THEN
473 rollback;
474 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
475 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
476 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
477 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
478 FND_MSG_PUB.ADD;
479 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
480 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
481 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
482 end if;
483 END LOCK_STEP;
484
485 --This procdeure would lock the (array) step and do an autonomous commit..
486
487 procedure LOCK_STEP (P_BATCHSTEP_ID IN FND_TABLE_OF_VARCHAR2_255,
488 P_REQUESTER IN NUMBER,
489 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
490 X_MSG_COUNT OUT NOCOPY NUMBER,
491 X_MSG_DATA OUT NOCOPY VARCHAR2)
492
493 IS PRAGMA AUTONOMOUS_TRANSACTION;
494
495 l_batch_id number;
496 l_batchstep_id number;
497 cursor c_get_batch_id is select batch_id from gme_batch_steps where batchstep_id = l_batchstep_id;
498 STEP_ALREADY_LOCKED_ERR exception;
499 NO_BATCH_FOUND_ERR exception;
500
501 BEGIN
502
503 for i in 1 .. P_BATCHSTEP_ID.count loop
504
505 l_batchstep_id := to_number (P_BATCHSTEP_ID (i));
506
507 IF (IS_STEP_LOCKED (P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => P_REQUESTER) = 'Y') THEN
508 RAISE STEP_ALREADY_LOCKED_ERR;
509 END IF;
510
511 open c_get_batch_id;
512 fetch c_get_batch_id into l_batch_id;
513 close c_get_batch_id;
514
515 IF (l_batch_id is null) THEN
516 RAISE NO_BATCH_FOUND_ERR;
517 END IF;
518
519
520 INSERT into GMO_BATCH_STEP_LOCK_DETAILS (BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
521 VALUES (l_batch_id, l_batchstep_id, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);
522
523 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
524 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_LOCKSTEP_REQ_MSG');
525 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',l_batchstep_id);
526 FND_MESSAGE.SET_TOKEN('REQUESTER',P_REQUESTER);
527 FND_MESSAGE.SET_TOKEN('REQUEST','LOCK');
528 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
529 end if;
530
531
532 end loop;
533
534 commit;
535
536 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
537
538 EXCEPTION
539 WHEN STEP_ALREADY_LOCKED_ERR THEN
540 rollback;
541 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
542 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_LOCK_ERR');
543 FND_MSG_PUB.ADD;
544 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
545 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
546 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
547 end if;
548 WHEN NO_BATCH_FOUND_ERR THEN
549 rollback;
550 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
551 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
552 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','OPERATION');
553 FND_MESSAGE.SET_TOKEN('ENTITY_KEY', l_batchstep_id);
554 FND_MSG_PUB.ADD;
555 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
556 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
557 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
558 end if;
559 WHEN OTHERS THEN
560 rollback;
561 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
562 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
563 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
564 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
565 FND_MSG_PUB.ADD;
566 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
567 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
568 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.lock_step', FALSE);
569 end if;
570 END LOCK_STEP;
571
572 --This procdeure would unlock the step.
573
574 procedure UNLOCK_STEP (P_BATCHSTEP_ID IN NUMBER,
575 P_REQUESTER IN NUMBER,
576 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
577 X_MSG_COUNT OUT NOCOPY NUMBER,
578 X_MSG_DATA OUT NOCOPY VARCHAR2)
579
580 IS PRAGMA AUTONOMOUS_TRANSACTION;
581
582 l_batch_id number;
583 l_lock_requester number;
584 l_lock_date date;
585 l_locked varchar2(1);
586 cursor c_get_lock_details is select batch_id, lock_requester,lock_date from gmo_batch_step_lock_details where batchstep_id = P_BATCHSTEP_ID;
587
588
589 STEP_NOT_LOCKED_ERR exception;
590
591 BEGIN
592 l_locked := IS_STEP_LOCKED (P_BATCHSTEP_ID => P_BATCHSTEP_ID);
593
594 IF (l_locked = 'N') THEN
595 RAISE STEP_NOT_LOCKED_ERR;
596 END IF;
597
598 open c_get_lock_details;
599 fetch c_get_lock_details into l_batch_id, l_lock_requester, l_lock_date;
600 close c_get_lock_details;
601
602
603 INSERT into GMO_BATCH_STEP_LOCK_HIST (LOCK_HIST_SEQ, BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, UNLOCK_REQUESTER, UNLOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
604 VALUES (GMO_BATCH_STEP_LOCK_HIST_S.NEXTVAL, l_batch_id, P_BATCHSTEP_ID, l_lock_requester, l_lock_date, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);
605
606 DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = P_BATCHSTEP_ID;
607
608 commit;
609
610 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
611 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNLOCKSTEP_REQ_MSG');
612 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',P_BATCHSTEP_ID);
613 FND_MESSAGE.SET_TOKEN('REQUESTER',P_REQUESTER);
614 FND_MESSAGE.SET_TOKEN('REQUEST','UNLOCK');
615 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
616 end if;
617
618
619 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
620
621 EXCEPTION
622 WHEN STEP_NOT_LOCKED_ERR THEN
623 rollback;
624 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
625 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNLOCK_ERR');
626 FND_MSG_PUB.ADD;
627 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
628 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
629 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
630 end if;
631 WHEN OTHERS THEN
632 rollback;
633 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
634 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
635 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
636 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
637 FND_MSG_PUB.ADD;
638 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
639 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
640 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
641 end if;
642 END UNLOCK_STEP;
643
644 --This procdeure would unlock the step.
645
646 procedure UNLOCK_STEP (P_BATCHSTEP_ID IN FND_TABLE_OF_VARCHAR2_255,
647 P_REQUESTER IN NUMBER,
648 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
649 X_MSG_COUNT OUT NOCOPY NUMBER,
650 X_MSG_DATA OUT NOCOPY VARCHAR2)
651
652 IS PRAGMA AUTONOMOUS_TRANSACTION;
653
654 l_batch_id number;
655 l_batchstep_id number;
656 l_lock_requester number;
657 l_lock_date date;
658 l_locked varchar2(1);
659 cursor c_get_lock_details is select batch_id, lock_requester,lock_date from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id;
660
661
662 STEP_NOT_LOCKED_ERR exception;
663
664 BEGIN
665
666 for i in 1 .. P_BATCHSTEP_ID.count loop
667
668 l_batchstep_id := to_number (p_batchstep_id (i));
669
670 l_locked := IS_STEP_LOCKED (P_BATCHSTEP_ID => l_batchstep_id);
671
672 IF (l_locked = 'N') THEN
673 RAISE STEP_NOT_LOCKED_ERR;
674 END IF;
675
676 open c_get_lock_details;
677 fetch c_get_lock_details into l_batch_id, l_lock_requester, l_lock_date;
678 close c_get_lock_details;
679
680
681 INSERT into GMO_BATCH_STEP_LOCK_HIST (LOCK_HIST_SEQ, BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, UNLOCK_REQUESTER, UNLOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
682 VALUES (GMO_BATCH_STEP_LOCK_HIST_S.NEXTVAL, l_batch_id, l_batchstep_id, l_lock_requester, l_lock_date, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);
683
684 DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = l_batchstep_id;
685
686 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
687 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_STEP_REQ_MSG');
688 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',l_batchstep_id);
689 FND_MESSAGE.SET_TOKEN('REQUESTER',P_REQUESTER);
690 FND_MESSAGE.SET_TOKEN('REQUEST','UNLOCK');
691 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
692 end if;
693
694
695 end loop;
696
697 commit;
698
699 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
700
701 EXCEPTION
702 WHEN STEP_NOT_LOCKED_ERR THEN
703 rollback;
704 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
705 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNLOCK_ERR');
706 FND_MSG_PUB.ADD;
707 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
708 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
709 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
710 end if;
711 WHEN OTHERS THEN
712 rollback;
713 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
714 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
715 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
716 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
717 FND_MSG_PUB.ADD;
718 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
719 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
720 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.unlock_step', FALSE);
721 end if;
722 END UNLOCK_STEP;
723
724 -- this procedure returns the batch id for the entity
725
726 procedure GET_BATCH_ID_FOR_ENTITY (P_ENTITY_NAME IN VARCHAR2,
727 P_ENTITY_KEY IN VARCHAR2,
728 X_BATCH_ID OUT NOCOPY NUMBER,
729 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
730 X_MSG_COUNT OUT NOCOPY NUMBER,
731 X_MSG_DATA OUT NOCOPY VARCHAR2)
732
733 IS
734
735 l_batch_id number;
736
737 cursor get_batch_id_for_step is select batch_id from gme_batch_steps where batchstep_id = p_entity_key;
738 cursor get_batch_id_for_resource is select batch_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;
739 cursor get_batch_id_for_activity is select batch_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;
740 cursor get_batch_id_for_material is select batch_id from gme_material_details where material_detail_id = p_entity_key;
741
742 NO_BATCH_FOUND_ERR exception;
743
744 BEGIN
745
746 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN
747 l_batch_id := to_number (P_ENTITY_KEY);
748 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
749 open get_batch_id_for_step;
750 fetch get_batch_id_for_step into l_batch_id;
751 close get_batch_id_for_step;
752 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
753 open get_batch_id_for_activity;
754 fetch get_batch_id_for_activity into l_batch_id;
755 close get_batch_id_for_activity;
756 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
757 open get_batch_id_for_resource;
758 fetch get_batch_id_for_resource into l_batch_id;
759 close get_batch_id_for_resource;
760 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) THEN
761 open get_batch_id_for_material;
762 fetch get_batch_id_for_material into l_batch_id;
763 close get_batch_id_for_material;
764 ELSE
765 RAISE gmo_invalid_entity_err;
766 END IF;
767
768 IF (l_batch_id is null) THEN
769 RAISE NO_BATCH_FOUND_ERR;
770 END IF;
771
772 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
773 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ENTITY_BATCH_MSG');
774 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
775 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
776 FND_MESSAGE.SET_TOKEN('BATCH_ID',l_batch_id);
777 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.get_batch_id_for_entity', FALSE);
778 end if;
779
780 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
781 X_BATCH_ID := l_batch_id;
782
783 EXCEPTION
784 WHEN NO_BATCH_FOUND_ERR THEN
785 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
786 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY_OPRN');
787 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
788 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
789 FND_MESSAGE.SET_TOKEN('OPRN','GET_BATCH_ID');
790 FND_MSG_PUB.ADD;
791 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
792 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
793 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_batch_id_for_entity', FALSE);
794 end if;
795 WHEN GMO_INVALID_ENTITY_ERR THEN
796 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
797 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
798 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
799 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
800 FND_MSG_PUB.ADD;
801 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
802 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
803 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_batch_id_for_entity', FALSE);
804 end if;
805 WHEN OTHERS THEN
806 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
807 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
808 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
809 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
810 FND_MSG_PUB.ADD;
811 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
812 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
813 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.get_batch_id_for_entity', FALSE);
814 end if;
815 END GET_BATCH_ID_FOR_ENTITY;
816
817 procedure GET_BATCHSTEP_ID_FOR_ENTITY (P_ENTITY_NAME IN VARCHAR2,
818 P_ENTITY_KEY IN VARCHAR2,
819 X_BATCHSTEP_ID OUT NOCOPY NUMBER,
820 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
821 X_MSG_COUNT OUT NOCOPY NUMBER,
822 X_MSG_DATA OUT NOCOPY VARCHAR2)
823
824 IS
825
826 l_batchstep_id number;
827 l_batch_operation varchar2(1000);
828
829 cursor get_batchstep_id_for_resource is select batchstep_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;
830 cursor get_batchstep_id_for_activity is select batchstep_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;
831 cursor get_batchstep_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = p_entity_key;
832
833 NO_BATCHSTEP_FOUND_ERR exception;
834
835 BEGIN
836
837 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
838 l_batchstep_id := to_number (P_ENTITY_KEY);
839 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
840 open get_batchstep_id_for_activity;
841 fetch get_batchstep_id_for_activity into l_batchstep_id;
842 close get_batchstep_id_for_activity;
843 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
844 open get_batchstep_id_for_resource;
845 fetch get_batchstep_id_for_resource into l_batchstep_id;
846 close get_batchstep_id_for_resource;
847 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) THEN
848 open get_batchstep_id_for_material;
849 fetch get_batchstep_id_for_material into l_batchstep_id;
850 close get_batchstep_id_for_material;
851 ELSE
852 RAISE gmo_invalid_entity_err;
853 END IF;
854
855 IF (l_batchstep_id is null) THEN
856 RAISE NO_BATCHSTEP_FOUND_ERR;
857 END IF;
858
859 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
860 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ENTITY_BATCH_MSG');
861 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
862 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
863 FND_MESSAGE.SET_TOKEN('BATCHSTEP_ID',l_batchstep_id);
864 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.get_batchstep_id_for_entity', FALSE);
865 end if;
866
867
868 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
869 X_BATCHSTEP_ID := l_batchstep_id;
870
871 EXCEPTION
872 WHEN NO_BATCHSTEP_FOUND_ERR THEN
873 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
874 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY_OPRN');
875 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
876 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
877 FND_MESSAGE.SET_TOKEN('OPRN','GET_BATCHSTEP_ID');
878 FND_MSG_PUB.ADD;
879 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
880 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
881 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_batchstep_id_for_entity', FALSE);
882 end if;
883 WHEN GMO_INVALID_ENTITY_ERR THEN
884 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
885 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
886 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
887 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
888 FND_MSG_PUB.ADD;
889 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
890 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
891 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_batchstep_id_for_entity', FALSE);
892 end if;
893 WHEN OTHERS THEN
894 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
895 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
896 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
897 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
898 FND_MSG_PUB.ADD;
899 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
900 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
901 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.get_batchstep_id_for_entity', FALSE);
902 end if;
903 END GET_BATCHSTEP_ID_FOR_ENTITY;
904
905 -- this function is internally used by the apis to
906 -- check if gmo is enabled or not.
907 function is_gmo_enabled return boolean is
908 begin
909 if (GMO_SETUP_GRP.IS_GMO_ENABLED = GMO_CONSTANTS_GRP.YES) then
910 return true;
911 else
912 return false;
913 end if;
914 end;
915
916 procedure update_task_attribute
917 (
918 P_ENTITY_NAME IN VARCHAR2,
919 P_ENTITY_KEY IN VARCHAR2,
920 P_INSTRUCTION_SET_ID IN NUMBER,
921 X_ATTRIBUTE_STATUS OUT NOCOPY VARCHAR2,
922 X_INVALID_INSTR OUT NOCOPY FND_TABLE_OF_VARCHAR2_255
923 )
924
925 IS
926
927 l_batch_id number;
928 l_batchstep_id number;
929 l_batchstep_activity_id number;
930 l_batchstep_resource_id number;
931 l_material_detail_id number;
932 l_formula_line_id number;
933 l_oprn_line_id number;
934 l_oprn_id number;
935 l_resources varchar2(100);
936 l_process_param_id number;
937 l_param_id number;
938 l_param_id_vchar varchar2(4000);
939 l_inventory_item_id number;
940
941 l_instruction_id number;
942 l_task_attribute varchar2(4000);
943 l_task_attribute_id_inst varchar2(4000);
944 l_instr_number varchar2(200);
945
946 l_min_assoc varchar2(1);
947
948 l_invalid_instr fnd_table_of_varchar2_255;
949 j binary_integer;
950
951 l_return_status varchar2(1);
952 l_msg_count number;
953 l_msg_data varchar2(4000);
954 l_task varchar2(500);
955 l_task_id number;
956
957 cursor get_activity is select batchstep_activity_id from gme_batch_step_activities where batchstep_id = l_batchstep_id and oprn_line_id = l_oprn_line_id;
958 cursor get_min_activity is select min(batchstep_activity_id) from gme_batch_step_activities where batchstep_id = l_batchstep_id;
959
960 cursor get_resource is select batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id and resources = l_resources;
961 cursor get_min_resource is select min(batchstep_resource_id) from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id;
962
963 cursor get_param is select process_param_id from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id and parameter_id = l_param_id;
964 cursor get_min_param is select (process_param_id) from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id;
965
966 cursor get_material is select material_detail_id from gme_material_details where formulaline_id = l_formula_line_id and batch_id = l_batch_id;
967 cursor get_min_material_for_step is select min(material_detail_id) from gme_batch_step_items where batchstep_id = l_batchstep_id;
968
969 cursor get_instr_details is select instr_number, instruction_id, task_id, task_attribute_id from gmo_instr_instance_b where instruction_set_id = P_INSTRUCTION_SET_ID;
970
971 cursor get_task is select task_name from gmo_instr_task_defn_b where task_id = l_task_id;
972
973 BEGIN
974
975 l_invalid_instr := fnd_table_of_varchar2_255();
976 j := 0;
977
978 open get_instr_details;
979 loop
980 fetch get_instr_details into l_instr_number, l_instruction_id, l_task_id, l_task_attribute;
981 exit when get_instr_details%NOTFOUND;
982
983 l_min_assoc := 'N';
984 l_task_attribute_id_inst := '';
985 l_process_param_id := null;
986 l_material_detail_id := null;
987 l_batchstep_activity_id := null;
988 l_batchstep_id := null;
989 l_batchstep_resource_id := null;
990 l_inventory_item_id := null;
991 l_batch_id := null;
992
993 l_task := '';
994 open get_task;
995 fetch get_task into l_task;
996 close get_task;
997
998
999 IF (L_TASK = 'UPDATE_ACTIVITY' or L_TASK='VIEW_ACTIVITY') then
1000 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) then
1001 l_batchstep_id := to_number(p_entity_key);
1002 if (l_task_attribute is not null and l_task_attribute <> gmo_constants_grp.all_attribute) then
1003 l_oprn_line_id := to_number (l_task_attribute);
1004 open get_activity;
1005 fetch get_activity into l_batchstep_activity_id;
1006 close get_activity;
1007 else
1008 open get_min_activity;
1009 fetch get_min_activity into l_batchstep_activity_id;
1010 close get_min_activity;
1011
1012 l_min_assoc := 'Y';
1013 end if;
1014 l_task_attribute_id_inst := l_batchstep_activity_id;
1015 end if;
1016
1017 ELSIF (L_TASK = 'UPDATE_RESOURCE' or L_TASK='VIEW_RESOURCE' or L_TASK = GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION) then
1018
1019 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY ) THEN
1020 -- pattern = OprnLineId$Resources
1021 l_oprn_line_id := to_number (substr(l_task_attribute, 1, instr (l_task_attribute, '$') - 1));
1022 l_resources := substr(l_task_attribute, instr (l_task_attribute, '$') + 1);
1023
1024 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) then
1025 l_batchstep_id := to_number(p_entity_key);
1026 open get_activity;
1027 fetch get_activity into l_batchstep_activity_id;
1028 close get_activity;
1029 ELSE
1030 l_batchstep_activity_id := to_number(p_entity_key);
1031 END IF;
1032
1033 if (l_resources <> gmo_constants_grp.all_attribute) then
1034 open get_resource;
1035 fetch get_resource into l_batchstep_resource_id;
1036 close get_resource;
1037
1038 else
1039 open get_min_resource;
1040 fetch get_min_resource into l_batchstep_resource_id;
1041 close get_min_resource;
1042
1043 l_min_assoc := 'Y';
1044 end if;
1045 l_task_attribute_id_inst := l_batchstep_resource_id;
1046
1047 END IF;
1048
1049 ELSIF (L_TASK = GMO_CONSTANTS_GRP.TASK_PROCESS_PARAMETER) then
1050
1051 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY
1052 or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
1053 -- pattern = OprnLineId$Resources$ParameterId
1054 l_oprn_line_id := to_number (substr(l_task_attribute, 1, instr (l_task_attribute, '$') - 1));
1055 l_resources := substr(l_task_attribute, instr (l_task_attribute, '$') + 1, (instr (l_task_attribute, '$', 1,2 )-1) - instr (l_task_attribute, '$'));
1056 l_param_id_vchar := substr(l_task_attribute, instr (l_task_attribute, '$', 1,2) + 1);
1057
1058 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) then
1059 l_batchstep_id := to_number(p_entity_key);
1060 open get_activity;
1061 fetch get_activity into l_batchstep_activity_id;
1062 close get_activity;
1063
1064 open get_resource;
1065 fetch get_resource into l_batchstep_resource_id;
1066 close get_resource;
1067 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
1068 l_batchstep_activity_id := to_number(p_entity_key);
1069
1070 open get_resource;
1071 fetch get_resource into l_batchstep_resource_id;
1072 close get_resource;
1073 ELSE
1074 l_batchstep_resource_id := to_number(p_entity_key);
1075 END IF;
1076
1077 if (l_param_id_vchar <> gmo_constants_grp.all_attribute) then
1078 l_param_id := to_number(l_param_id_vchar);
1079 open get_param;
1080 fetch get_param into l_process_param_id;
1081 close get_param;
1082 else
1083 open get_min_param;
1084 fetch get_min_param into l_process_param_id;
1085 close get_min_param;
1086
1087 l_min_assoc := 'Y';
1088 end if;
1089 l_task_attribute_id_inst := l_process_param_id;
1090
1091 END IF;
1092
1093 ELSIF (L_TASK = 'MATERIAL' OR L_TASK = 'RESERVATIONS' OR L_TASK = 'VIEW_MATERIAL' OR L_TASK = 'UPDATE_MATERIAL' OR L_TASK='MATERIAL_TRANSACTIONS' OR L_TASK = 'QUALITY') THEN
1094
1095 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY
1096 or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
1097
1098 GET_BATCH_ID_FOR_ENTITY(P_ENTITY_NAME => P_ENTITY_NAME,
1099 P_ENTITY_KEY => P_ENTITY_KEY,
1100 X_BATCH_ID => l_batch_id,
1101 X_RETURN_STATUS => l_return_status,
1102 X_MSG_COUNT => l_msg_count,
1103 X_MSG_DATA => l_msg_data);
1104
1105 GET_BATCHSTEP_ID_FOR_ENTITY(P_ENTITY_NAME => P_ENTITY_NAME,
1106 P_ENTITY_KEY => P_ENTITY_KEY,
1107 X_BATCHSTEP_ID => l_batchstep_id,
1108 X_RETURN_STATUS => l_return_status,
1109 X_MSG_COUNT => l_msg_count,
1110 X_MSG_DATA => l_msg_data);
1111
1112 if (l_task_attribute is not null and l_task_attribute <> gmo_constants_grp.all_attribute) then
1113 l_formula_line_id := to_number(l_task_attribute);
1114 open get_material;
1115 fetch get_material into l_material_detail_id;
1116 close get_material;
1117
1118 else
1119 open get_min_material_for_step;
1120 fetch get_min_material_for_step into l_material_detail_id;
1121 close get_min_material_for_step;
1122
1123 l_min_assoc := 'Y';
1124 end if;
1125
1126 l_task_attribute_id_inst := l_material_detail_id;
1127 END IF;
1128 end if;
1129
1130 if (l_task_attribute is not null) then
1131 if (l_task_attribute_id_inst is not null) then
1132 update gmo_instr_instance_b set task_attribute_id = l_task_attribute_id_inst
1133 where instruction_set_id = P_INSTRUCTION_SET_ID
1134 and instruction_id = l_instruction_id;
1135 elsif (l_min_assoc = 'N') then
1136 j := j + 1;
1137 l_invalid_instr.extend;
1138 l_invalid_instr(j) := l_instr_number;
1139 end if;
1140 end if;
1141 end loop;
1142 close get_instr_details;
1143
1144 if (j = 0) then
1145 X_ATTRIBUTE_STATUS := 'S';
1146 else
1147 X_ATTRIBUTE_STATUS := 'E';
1148 end if;
1149 X_INVALID_INSTR := l_invalid_instr;
1150
1151 END update_task_attribute;
1152
1153 --This procdeure would instantiate the process instructions for the batch.
1154 procedure INSTANTIATE_ADVANCED_PI (P_ENTITY_NAME IN VARCHAR2,
1155 P_ENTITY_KEY IN VARCHAR2,
1156 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1157 X_MSG_COUNT OUT NOCOPY NUMBER,
1158 X_MSG_DATA OUT NOCOPY VARCHAR2)
1159
1160 IS
1161
1162 l_recipe_id number;
1163 l_batchstep_id number;
1164 l_routingstep_id number;
1165 l_oprn_id number;
1166 l_oprn_line_id number;
1167 l_resources varchar2(16);
1168 l_material_detail_id number;
1169 l_formulaline_id number;
1170 l_inventory_item_id number;
1171 l_batchstep_activity_id number;
1172 l_batchstep_resource_id number;
1173 l_instruction_set_id number;
1174 l_return_status varchar2(100);
1175 l_msg_count number;
1176 l_msg_data varchar2(4000);
1177 instruction_create_err exception;
1178
1179 l_entity_display_name varchar2(255);
1180 l_invalid_instr fnd_table_of_varchar2_255;
1181 l_invalid_instr_master fnd_table_of_varchar2_255;
1182 l_invalid_entity_master fnd_table_of_varchar2_255;
1183
1184 j binary_integer;
1185 k binary_integer;
1186 l_attribute_Status varchar2(1);
1187 INVALID_ATTRIBUTE_ASSOC_ERR exception;
1188
1189 cursor c_get_recipe is
1190 select grv.recipe_id
1191 from gme_batch_header gbh,
1192 gmd_recipe_validity_rules grv
1193 where gbh.recipe_validity_rule_id = grv.recipe_validity_rule_id and gbh.batch_id = p_entity_key;
1194
1195 cursor c_get_steps is
1196 select
1197 batchstep_id,
1198 routingstep_id,
1199 a.oprn_id,
1200 batchstep_no || '-' || b.oprn_no
1201 from gme_batch_steps a, gmd_operations_b b where a.oprn_id = b.oprn_id and batch_id = p_entity_key;
1202
1203 cursor c_get_activities is
1204 select batchstep_activity_id,
1205 routingstep_id,
1206 oprn_line_id ,
1207 activity
1208 from gme_batch_step_activities gbsa,
1209 gme_batch_steps gbs
1210 where gbsa.batchstep_id = gbs.batchstep_id and gbsa.batch_id = p_entity_key;
1211
1212 cursor c_get_resources is
1213 select batchstep_resource_id,
1214 routingstep_id,
1215 oprn_line_id,
1216 resources ,
1217 gbsa.activity || '-' || resources
1218 from gme_batch_step_resources gbsr,
1219 gme_batch_step_Activities gbsa,
1220 gme_batch_steps gbs
1221 where gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
1222 and gbsa.batchstep_id = gbs.batchstep_id and gbsr.batch_id = p_entity_key;
1223
1224 cursor c_get_materials is
1225 select a.material_detail_id, a.formulaline_id, a.inventory_item_id, b.concatenated_segments from gme_material_details a, mtl_system_items_kfv b
1226 where a.organization_id = b.organization_id
1227 and a.inventory_item_id = b.inventory_item_id
1228 and a.batch_id = p_entity_key;
1229
1230 BEGIN
1231
1232 IF (NOT IS_GMO_ENABLED) THEN
1233 RAISE GMO_DISABLED_ERR;
1234 END IF;
1235
1236 IF (NOT IS_BATCH_ENHANCED_PI (P_ENTITY_NAME => P_ENTITY_NAME, P_ENTITY_KEY => P_ENTITY_KEY) ) THEN
1237 RAISE GMO_NOT_ENHANCED_PI_ERR;
1238 END IF;
1239
1240 IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_BATCH OR p_entity_key is null) THEN
1241 RAISE GMO_INVALID_ENTITY_ERR;
1242 END IF;
1243
1244 open c_get_recipe;
1245 fetch c_get_recipe into l_recipe_id;
1246 close c_get_recipe;
1247 if l_recipe_id is null then
1248 -- Kapil ME LCF-GMO
1249 -- For LCF Batches, instantiate PI through the follwoing Procedure.
1250 INSTANTIATE_INSTR_FOR_LCF(P_ENTITY_NAME => P_ENTITY_NAME,
1251 P_ENTITY_KEY => P_ENTITY_KEY,
1252 X_RETURN_STATUS => X_RETURN_STATUS,
1253 X_MSG_COUNT => X_MSG_COUNT,
1254 X_MSG_DATA => X_MSG_DATA);
1255 else
1256
1257
1258 j := 0;
1259 k := 0;
1260 l_invalid_entity_master := fnd_table_of_varchar2_255();
1261 l_invalid_instr_master := fnd_table_of_varchar2_255();
1262
1263 open c_get_steps;
1264 loop
1265 fetch c_get_steps into l_batchstep_id, l_routingstep_id, l_oprn_id, l_entity_display_name;
1266 exit when c_get_steps%NOTFOUND;
1267
1268 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
1269 P_API_VERSION => 1.0,
1270 X_RETURN_STATUS => l_return_status,
1271 X_MSG_COUNT => l_msg_count,
1272 X_MSG_DATA => l_msg_data,
1273 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
1274 P_DEFINITION_ENTITY_KEY => l_recipe_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_id,
1275 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
1276 P_INSTANCE_ENTITY_KEY => l_batchstep_id,
1277 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
1278 X_INSTRUCTION_SET_ID => l_instruction_set_id
1279 );
1280
1281 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1282 RAISE instruction_create_err;
1283 end if;
1284 update_task_attribute
1285 (
1286 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
1287 P_ENTITY_KEY => l_batchstep_id,
1288 P_INSTRUCTION_SET_ID => l_instruction_set_id,
1289 X_ATTRIBUTE_STATUS => l_attribute_status,
1290 X_INVALID_INSTR => l_invalid_instr
1291 );
1292 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
1293 for i in 1 .. l_invalid_instr.count loop
1294 j := j + 1;
1295 l_invalid_instr_master.extend;
1296 l_invalid_instr_master (j) := l_invalid_instr(i);
1297 end loop;
1298 k := k+1;
1299 l_invalid_entity_master.extend;
1300 l_invalid_entity_master (k) := l_entity_display_name;
1301 end if;
1302
1303 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1304 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
1305 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1306 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1307 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','STEP');
1308 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_id);
1309 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1310 end if;
1311
1312 end loop;
1313 close c_get_steps;
1314
1315 open c_get_activities;
1316 loop
1317 fetch c_get_activities into l_batchstep_activity_id, l_routingstep_id, l_oprn_line_id, l_entity_display_name;
1318 exit when c_get_activities%NOTFOUND;
1319
1320 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
1321 P_API_VERSION => 1.0,
1322 X_RETURN_STATUS => l_return_status,
1323 X_MSG_COUNT => l_msg_count,
1324 X_MSG_DATA => l_msg_data,
1325 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
1326 P_DEFINITION_ENTITY_KEY => l_recipe_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_line_id,
1327 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
1328 P_INSTANCE_ENTITY_KEY => l_batchstep_activity_id,
1329 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
1330 X_INSTRUCTION_SET_ID => l_instruction_set_id
1331 );
1332
1333 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1334 RAISE instruction_create_err;
1335 end if;
1336
1337 update_task_attribute
1338 (
1339 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
1340 P_ENTITY_KEY => l_batchstep_activity_id,
1341 P_INSTRUCTION_SET_ID => l_instruction_set_id,
1342 X_ATTRIBUTE_STATUS => l_attribute_status,
1343 X_INVALID_INSTR => l_invalid_instr
1344 );
1345 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
1346 for i in 1 .. l_invalid_instr.count loop
1347 j := j + 1;
1348 l_invalid_instr_master.extend;
1349 l_invalid_instr_master (j) := l_invalid_instr(i);
1350 end loop;
1351 k := k+1;
1352 l_invalid_entity_master.extend;
1353 l_invalid_entity_master (k) := l_entity_display_name;
1354 end if;
1355
1356 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1357 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
1358 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1359 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1360 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','ACTIVITY');
1361 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_activity_id);
1362 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1363 end if;
1364
1365 end loop;
1366 close c_get_activities;
1367
1368 open c_get_resources;
1369 loop
1370 fetch c_get_resources into l_batchstep_resource_id, l_routingstep_id, l_oprn_line_id, l_resources, l_entity_display_name;
1371 exit when c_get_resources%NOTFOUND;
1372
1373 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
1374 P_API_VERSION => 1.0,
1375 X_RETURN_STATUS => l_return_status,
1376 X_MSG_COUNT => l_msg_count,
1377 X_MSG_DATA => l_msg_data,
1378 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
1379 P_DEFINITION_ENTITY_KEY => l_recipe_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_line_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_resources,
1380 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
1381 P_INSTANCE_ENTITY_KEY => l_batchstep_resource_id,
1382 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
1383 X_INSTRUCTION_SET_ID => l_instruction_set_id
1384 );
1385
1386 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1387 RAISE instruction_create_err;
1388 end if;
1389
1390 update_task_attribute
1391 (
1392 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
1393 P_ENTITY_KEY => l_batchstep_resource_id,
1394 P_INSTRUCTION_SET_ID => l_instruction_set_id,
1395 X_ATTRIBUTE_STATUS => l_attribute_status,
1396 X_INVALID_INSTR => l_invalid_instr
1397 );
1398 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
1399 for i in 1 .. l_invalid_instr.count loop
1400 j := j + 1;
1401 l_invalid_instr_master.extend;
1402 l_invalid_instr_master (j) := l_invalid_instr(i);
1403 end loop;
1404 k := k+1;
1405 l_invalid_entity_master.extend;
1406 l_invalid_entity_master (k) := l_entity_display_name;
1407 end if;
1408
1409 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1410 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
1411 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1412 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1413 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','RESOURCE');
1414 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_resource_id);
1415 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1416 end if;
1417
1418 end loop;
1419 close c_get_resources;
1420
1421 open c_get_materials;
1422 loop
1423 fetch c_get_materials into l_material_detail_id, l_formulaline_id, l_inventory_item_id, l_entity_display_name;
1424 exit when c_get_materials%NOTFOUND;
1425
1426 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
1427 P_API_VERSION => 1.0,
1428 X_RETURN_STATUS => l_return_status,
1429 X_MSG_COUNT => l_msg_count,
1430 X_MSG_DATA => l_msg_data,
1431 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
1432 P_DEFINITION_ENTITY_KEY => l_recipe_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_formulaline_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_inventory_item_id,
1433 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
1434 P_INSTANCE_ENTITY_KEY => l_material_detail_id,
1435 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
1436 X_INSTRUCTION_SET_ID => l_instruction_set_id
1437 );
1438
1439 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1440 RAISE instruction_create_err;
1441 end if;
1442
1443 update_task_attribute
1444 (
1445 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
1446 P_ENTITY_KEY => l_material_detail_id,
1447 P_INSTRUCTION_SET_ID => l_instruction_set_id,
1448 X_ATTRIBUTE_STATUS => l_attribute_status,
1449 X_INVALID_INSTR => l_invalid_instr
1450 );
1451 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
1452 for i in 1 .. l_invalid_instr.count loop
1453 j := j + 1;
1454 l_invalid_instr_master.extend;
1455 l_invalid_instr_master (j) := l_invalid_instr(i);
1456 end loop;
1457 k := k+1;
1458 l_invalid_entity_master.extend;
1459 l_invalid_entity_master (k) := l_entity_display_name;
1460 end if;
1461
1462 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1463 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
1464 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1465 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1466 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','MATERIAL');
1467 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_material_detail_id);
1468 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1469 end if;
1470
1471 end loop;
1472 close c_get_materials;
1473
1474 if (l_invalid_entity_master.count > 0) then
1475 RAISE INVALID_ATTRIBUTE_ASSOC_ERR;
1476 end if;
1477
1478 end if;
1479
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481
1482 EXCEPTION
1483 WHEN instruction_create_err then
1484 x_return_status := FND_API.G_RET_STS_ERROR;
1485 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1486 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1487 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1488 end if;
1489 WHEN GMO_NOT_ENHANCED_PI_ERR THEN
1490 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1491 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_NOT_ENHANCED_PI_ERR');
1492 FND_MSG_PUB.ADD;
1493 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1494 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1495 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1496 end if;
1497 WHEN GMO_INVALID_RECIPE_ERR THEN
1498 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1499 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_RECIPE');
1500 FND_MSG_PUB.ADD;
1501 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1502 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1503 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1504 end if;
1505 WHEN GMO_DISABLED_ERR THEN
1506 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1507 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_DISABLED_ERR');
1508 FND_MSG_PUB.ADD;
1509 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1510 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1511 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1512 end if;
1513 WHEN GMO_INVALID_ENTITY_ERR THEN
1514 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1515 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
1516 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1517 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1518 FND_MSG_PUB.ADD;
1519 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1520 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1521 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1522 end if;
1523 WHEN INVALID_ATTRIBUTE_ASSOC_ERR THEN
1524 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1525 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ATTR_ASSOC');
1526 FND_MSG_PUB.ADD;
1527 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1528 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1529 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1530 end if;
1531 WHEN OTHERS THEN
1532 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1533 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
1534 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1535 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
1536 FND_MSG_PUB.ADD;
1537 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1538 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1539 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
1540 end if;
1541
1542 END INSTANTIATE_ADVANCED_PI;
1543
1544
1545 --This procdeure would get the context information for the task.
1546
1547 procedure ON_TASK_LOAD (P_FROM_MODULE IN VARCHAR2,
1548 P_ENTITY_NAME IN VARCHAR2,
1549 P_ENTITY_KEY IN VARCHAR2,
1550 P_TASK IN VARCHAR2,
1551 P_TASK_ATTRIBUTE IN VARCHAR2,
1552 P_INSTRUCTION_ID IN NUMBER,
1553 P_INSTRUCTION_PROCESS_ID IN NUMBER,
1554 P_REQUESTER IN NUMBER,
1555 P_VBATCH_MODE IN VARCHAR2,
1556 X_TASK_ENTITY_NAME OUT NOCOPY VARCHAR2,
1557 X_TASK_ENTITY_KEY OUT NOCOPY VARCHAR2,
1558 X_TASK_NAME OUT NOCOPY VARCHAR2,
1559 X_TASK_KEY OUT NOCOPY VARCHAR2,
1560 X_READ_ONLY OUT NOCOPY VARCHAR2,
1561 X_CONTEXT_PARAMS_TBL OUT NOCOPY GMO_DATATYPES_GRP.CONTEXT_PARAMS_TBL_TYPE,
1562 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1563 X_MSG_COUNT OUT NOCOPY NUMBER,
1564 X_MSG_DATA OUT NOCOPY VARCHAR2
1565 )
1566 IS
1567
1568 l_batch_id number;
1569 l_batchstep_id number;
1570 l_batchstep_activity_id number;
1571 l_batchstep_resource_id number;
1572 l_material_detail_id number;
1573 l_formula_line_id number;
1574 l_oprn_line_id number;
1575 l_oprn_id number;
1576 l_resources varchar2(100);
1577 l_process_param_id number;
1578 l_param_id number;
1579 l_param_id_vchar varchar2(4000);
1580 l_inventory_item_id number;
1581
1582 l_org_id number;
1583 l_org_code varchar2(10);
1584
1585 l_vbatch_mode varchar2(1);
1586 l_step_locked varchar2(1);
1587 l_requester number;
1588 l_return_status varchar2(100);
1589 l_msg_count number;
1590 l_msg_data varchar2(4000);
1591
1592 l_line_type number;
1593 l_line_no number;
1594 l_invoke_mode varchar2(1);
1595
1596 l_context_params_rec_type GMO_DATATYPES_GRP.CONTEXT_PARAMS_REC_TYPE;
1597
1598 cursor get_step_detail is select batch_id, batchstep_id from gme_batch_steps where batchstep_id = l_batchstep_id;
1599
1600 cursor get_activity_detail is select batch_id, batchstep_id, batchstep_activity_id from gme_batch_step_activities where batchstep_id = l_batchstep_id and oprn_line_id = l_oprn_line_id;
1601 cursor get_activity_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id from gme_batch_step_activities where batchstep_activity_id = l_batchstep_activity_id;
1602
1603 cursor get_resource_detail is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id and resources = l_resources;
1604 cursor get_resource_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id from gme_batch_step_resources where batchstep_resource_id = l_batchstep_resource_id;
1605
1606 cursor get_param_detail is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id, process_param_id from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id and parameter_id = l_param_id;
1607 cursor get_param_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id, process_param_id from gme_process_parameters where process_param_id = l_process_param_id;
1608
1609
1610 cursor get_material_detail is select batch_id, material_detail_id, inventory_item_id, line_no, line_type from gme_material_details where formulaline_id = l_formula_line_id and batch_id = l_batch_id;
1611 cursor get_material_detail_from_id is select batch_id, material_detail_id, inventory_item_id, line_no, line_type from gme_material_details where material_detail_id = l_material_detail_id;
1612 cursor get_step_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = l_material_detail_id;
1613
1614 cursor get_org_detail is select a.organization_id, a.organization_code from mtl_parameters a, gme_batch_header b where a.organization_id = b.organization_id and b.batch_id = l_batch_id;
1615
1616 GMO_INVALID_TASK_ERR exception;
1617 GMO_INSTR_TASK_PARAM_ERR exception;
1618 NO_BATCH_FOUND_ERR exception;
1619 GMO_INVALID_MODULE_ERR exception;
1620 l_advanced_pi boolean;
1621
1622 BEGIN
1623 IF (NOT IS_GMO_ENABLED) THEN
1624 RAISE GMO_DISABLED_ERR;
1625 END IF;
1626
1627
1628 --validte if the module is in vbatch or pi
1629 IF (P_FROM_MODULE <> GMO_CONSTANTS_GRP.FROM_MODULE_PI AND P_FROM_MODULE <> GMO_CONSTANTS_GRP.FROM_MODULE_VBATCH) THEN
1630 RAISE GMO_INVALID_MODULE_ERR;
1631 END IF;
1632
1633
1634 l_advanced_pi := IS_BATCH_ENHANCED_PI (P_ENTITY_NAME => P_ENTITY_NAME, P_ENTITY_KEY => P_ENTITY_KEY);
1635
1636 --we should check the admin privleges from the function access instead
1637 --of banking on the parameter.
1638 /*
1639
1640 if (P_VBATCH_MODE = GMO_CONSTANTS_GRP.VBATCH_ADMIN_MODE) then
1641 l_vbatch_mode := GMO_CONSTANTS_GRP.VBATCH_ADMIN_MODE;
1642 else
1643 l_vbatch_mode := GMO_CONSTANTS_GRP.VBATCH_NORMAL_MODE;
1644 end if;
1645
1646 if (P_FROM_MODULE = GMO_CONSTANTS_GRP.FROM_MODULE_PI) then
1647
1648 GMO_INSTRUCTION_GRP.GET_TASK_PARAMETER
1649 (
1650 P_API_VERSION => 1.0,
1651 X_RETURN_STATUS => l_return_status,
1652 X_MSG_COUNT => l_msg_count,
1653 X_MSG_DATA => l_msg_data,
1654 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
1655 P_ATTRIBUTE_NAME => GMO_CONSTANTS_GRP.VBATCH_MODE_PARAMETER,
1656 X_ATTRIBUTE_VALUE => l_vbatch_mode
1657 );
1658
1659
1660 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1661 RAISE gmo_instr_task_param_err;
1662 end if;
1663
1664 end if;
1665 --if (l_vbatch_mode is null) then
1666 */
1667 if (FND_FUNCTION.TEST('GMO_VBATCH_DETAIL_ADMIN')) then
1668 l_vbatch_mode := GMO_CONSTANTS_GRP.VBATCH_ADMIN_MODE;
1669 else
1670 l_vbatch_mode := GMO_CONSTANTS_GRP.VBATCH_NORMAL_MODE;
1671 end if;
1672
1673 l_requester := P_REQUESTER;
1674
1675 IF (l_requester is null) THEN
1676 l_requester := FND_GLOBAL.USER_ID;
1677 END IF;
1678
1679 IF (P_TASK = 'VIEW_BATCH' or P_TASK = 'UPDATE_BATCH') THEN
1680
1681 IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN
1682 RAISE GMO_INVALID_TASK_ERR;
1683 END IF;
1684
1685 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_BATCH;
1686 X_TASK_ENTITY_KEY := P_ENTITY_KEY;
1687 X_TASK_NAME := null;
1688 X_TASK_KEY := null;
1689
1690 IF (P_TASK = 'UPDATE_BATCH') THEN
1691 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1692 ELSE
1693 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1694
1695 END IF;
1696
1697 ELSIF (P_TASK = 'VIEW_OPERATION' or P_TASK = 'UPDATE_OPERATION' or P_TASK = 'RELEASE_STEP' or P_TASK = 'CLOSE_STEP') THEN
1698
1699 IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
1700 RAISE GMO_INVALID_TASK_ERR;
1701 END IF;
1702
1703 l_batchstep_id := to_number (p_entity_key);
1704 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_OPERATION;
1705 X_TASK_ENTITY_KEY := P_ENTITY_KEY;
1706 X_TASK_NAME := null;
1707 X_TASK_KEY := null;
1708
1709 IF (P_TASK = 'VIEW_OPERATION') THEN
1710 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1711 ELSE
1712 l_step_locked := GMO_VBATCH_PVT.IS_STEP_LOCKED (P_VBATCH_MODE => l_vbatch_mode, P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => l_requester);
1713
1714 IF (not l_advanced_pi or l_step_locked = GMO_CONSTANTS_GRP.YES) THEN
1715 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1716 ELSE
1717 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1718 END IF;
1719
1720 END IF;
1721
1722 ELSIF (P_TASK = 'UPDATE_ACTIVITY' OR P_TASK = 'VIEW_ACTIVITY') THEN
1723
1724 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
1725 l_batchstep_id := to_number (p_entity_key);
1726 l_batchstep_activity_id := to_number(p_task_attribute);
1727
1728 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
1729 l_batchstep_activity_id := to_number (p_entity_key);
1730
1731 ELSE
1732 RAISE GMO_INVALID_TASK_ERR;
1733 END IF;
1734
1735 open get_activity_detail_from_id;
1736 fetch get_activity_detail_from_id into l_batch_id, l_batchstep_id, l_batchstep_activity_id;
1737 close get_activity_detail_from_id;
1738
1739 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_OPERATION;
1740 X_TASK_ENTITY_KEY := l_batchstep_id;
1741 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_ACTIVITY;
1742 X_TASK_KEY := l_batchstep_activity_id;
1743
1744 IF (P_TASK = 'VIEW_ACTIVITY') THEN
1745 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1746 ELSE
1747
1748 l_step_locked := GMO_VBATCH_PVT.IS_STEP_LOCKED (P_VBATCH_MODE => l_vbatch_mode, P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => l_requester);
1749
1750 IF (not l_advanced_pi or l_step_locked = GMO_CONSTANTS_GRP.YES) THEN
1751 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1752 ELSE
1753 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1754 END IF;
1755
1756 END IF;
1757
1758
1759 ELSIF (P_TASK = 'UPDATE_RESOURCE' OR P_TASK = 'VIEW_RESOURCE' OR P_TASK = GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION) THEN
1760
1761 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
1762 l_batchstep_id := to_number (p_entity_key);
1763 l_batchstep_resource_id := to_number(p_task_attribute);
1764
1765 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
1766
1767 l_batchstep_activity_id := to_number (p_entity_key);
1768 l_batchstep_resource_id := to_number(p_task_attribute);
1769
1770 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
1771
1772 l_batchstep_resource_id := to_number(p_entity_key);
1773
1774 ELSE
1775 RAISE GMO_INVALID_TASK_ERR;
1776 END IF;
1777
1778 open get_resource_detail_from_id;
1779 fetch get_resource_detail_from_id into l_batch_id, l_batchstep_id, l_batchstep_activity_id, l_batchstep_resource_id;
1780 close get_resource_detail_from_id;
1781
1782 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_ACTIVITY;
1783 X_TASK_ENTITY_KEY := l_batchstep_activity_id;
1784 IF (P_TASK = GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION) THEN
1785 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION;
1786 ELSE
1787 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_RESOURCE;
1788 END IF;
1789 X_TASK_KEY := l_batchstep_resource_id;
1790
1791 IF (P_TASK = 'VIEW_RESOURCE') THEN
1792 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1793 ELSE
1794
1795 l_step_locked := GMO_VBATCH_PVT.IS_STEP_LOCKED (P_VBATCH_MODE => l_vbatch_mode, P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => l_requester);
1796
1797 IF (not l_advanced_pi or l_step_locked = GMO_CONSTANTS_GRP.YES) THEN
1798 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1799 ELSE
1800 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1801 END IF;
1802
1803 END IF;
1804
1805 ELSIF (P_TASK = GMO_CONSTANTS_GRP.TASK_PROCESS_PARAMETER) THEN
1806
1807 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
1808 l_batchstep_id := to_number (p_entity_key);
1809
1810 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
1811 l_batchstep_activity_id := to_number (p_entity_key);
1812
1813 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
1814 l_batchstep_resource_id := to_number(p_entity_key);
1815
1816 ELSE
1817 RAISE GMO_INVALID_TASK_ERR;
1818 END IF;
1819 l_process_param_id := to_number(p_task_attribute);
1820
1821 open get_param_detail_from_id;
1822 fetch get_param_detail_from_id into l_batch_id, l_batchstep_id, l_batchstep_activity_id, l_batchstep_resource_id, l_process_param_id;
1823 close get_param_detail_from_id;
1824
1825 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_RESOURCE;
1826 X_TASK_ENTITY_KEY := l_batchstep_resource_id;
1827 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_PROCESS_PARAMETER;
1828 X_TASK_KEY := l_process_param_id;
1829
1830 l_step_locked := GMO_VBATCH_PVT.IS_STEP_LOCKED (P_VBATCH_MODE => l_vbatch_mode, P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => l_requester);
1831
1832 IF (not l_advanced_pi or l_step_locked = GMO_CONSTANTS_GRP.YES) THEN
1833 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1834 ELSE
1835 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1836 END IF;
1837
1838 ELSIF (P_TASK = 'MATERIAL' OR P_TASK = 'RESERVATIONS' OR P_TASK = 'VIEW_MATERIAL' OR P_TASK = 'UPDATE_MATERIAL' OR P_TASK='MATERIAL_TRANSACTIONS' OR P_TASK = 'QUALITY') THEN
1839
1840
1841 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN
1842
1843 l_batch_id := to_number (p_entity_key);
1844
1845 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
1846 l_batchstep_id := to_number (p_entity_key);
1847 l_material_detail_id := to_number(p_task_attribute);
1848
1849 open get_step_detail;
1850 fetch get_step_detail into l_batch_id, l_batchstep_id;
1851 close get_step_detail;
1852
1853 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
1854
1855 l_batchstep_activity_id := to_number(p_entity_key);
1856 l_material_detail_id := to_number(p_task_attribute);
1857
1858 open get_activity_detail_from_id;
1859 fetch get_activity_detail_from_id into l_batch_id, l_batchstep_id, l_batchstep_activity_id;
1860 close get_activity_detail_from_id;
1861
1862 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
1863
1864 l_batchstep_resource_id := to_number(p_entity_key);
1865 l_material_detail_id := to_number(p_task_attribute);
1866
1867 open get_resource_detail_from_id;
1868 fetch get_resource_detail_from_id into l_batch_id, l_batchstep_id, l_batchstep_activity_id, l_batchstep_resource_id;
1869 close get_resource_detail_from_id;
1870
1871 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) THEN
1872
1873 l_material_detail_id := to_number(p_entity_key);
1874
1875 open get_step_id_for_material;
1876 fetch get_step_id_for_material into l_batchstep_id;
1877 close get_step_id_for_material;
1878
1879 ELSE
1880 RAISE GMO_INVALID_TASK_ERR;
1881 END IF;
1882
1883 open get_material_detail_from_id;
1884 fetch get_material_detail_from_id into l_batch_id, l_material_detail_id, l_inventory_item_id, l_line_no, l_line_type;
1885 close get_material_detail_from_id;
1886
1887 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH OR P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_MATERIAL OR P_TASK = 'QUALITY') THEN
1888 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_BATCH;
1889 X_TASK_ENTITY_KEY := l_batch_id;
1890 ELSE
1891 X_TASK_ENTITY_NAME := GMO_CONSTANTS_GRP.ENTITY_OPERATION;
1892 X_TASK_ENTITY_KEY := l_batchstep_id;
1893 END IF;
1894
1895 IF (P_TASK= GMO_CONSTANTS_GRP.TASK_MATERIAL_TRANSACTIONS) THEN
1896 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_MATERIAL_TRANSACTIONS;
1897 l_invoke_mode := 'T';
1898 X_TASK_KEY := l_material_detail_id;
1899
1900 ELSIF (P_TASK = 'QUALITY') THEN
1901 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_QUALITY;
1902 X_TASK_KEY := l_inventory_item_id;
1903
1904 ELSE
1905 X_TASK_NAME := GMO_CONSTANTS_GRP.TASK_MATERIAL;
1906 X_TASK_KEY := l_material_detail_id;
1907 END IF;
1908
1909 --Bug 5023801: start
1910 IF (P_TASK = 'VIEW_MATERIAL') THEN
1911 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1912 --Bug 5023801: end
1913 ELSIF (X_TASK_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH or l_batchstep_id is null ) THEN
1914 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1915 ELSE
1916
1917 l_step_locked := GMO_VBATCH_PVT.IS_STEP_LOCKED (P_VBATCH_MODE => l_vbatch_mode, P_BATCHSTEP_ID => l_batchstep_id, P_REQUESTER => l_requester);
1918
1919 IF (not l_advanced_pi or l_step_locked = GMO_CONSTANTS_GRP.YES) THEN
1920 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
1921 ELSE
1922 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_YES;
1923 END IF;
1924
1925 END IF;
1926
1927 ELSE
1928 RAISE GMO_INVALID_TASK_ERR;
1929 END IF;
1930
1931 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1932 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ON_TASK_LOAD_MSG');
1933 FND_MESSAGE.SET_TOKEN('FROM_MODULE',P_FROM_MODULE);
1934 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1935 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
1936 FND_MESSAGE.SET_TOKEN('TASK',P_TASK);
1937 FND_MESSAGE.SET_TOKEN('TASK_ATTRIBUTE',P_TASK_ATTRIBUTE);
1938 FND_MESSAGE.SET_TOKEN('TASK_ENTITY_NAME',X_TASK_ENTITY_NAME);
1939 FND_MESSAGE.SET_TOKEN('TASK_ENTITY_KEY',X_TASK_ENTITY_KEY);
1940 FND_MESSAGE.SET_TOKEN('TASK_NAME',X_TASK_NAME);
1941 FND_MESSAGE.SET_TOKEN('TASK_KEY',X_TASK_KEY);
1942 FND_MESSAGE.SET_TOKEN('READ_ONLY',X_READ_ONLY);
1943 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
1944 end if;
1945
1946 if (l_batch_id is null) then
1947 get_batch_id_for_entity
1948 (
1949 p_entity_name => p_entity_name,
1950 p_entity_key => p_entity_key,
1951 x_batch_id => l_batch_id,
1952 x_return_status => l_return_status,
1953 x_msg_count => l_msg_count,
1954 x_msg_data => l_msg_data
1955 );
1956 if L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1957 RAISE NO_BATCH_FOUND_ERR;
1958 end if;
1959 end if;
1960
1961 open get_org_detail;
1962 fetch get_org_detail into l_org_id, l_org_code;
1963 close get_org_detail;
1964
1965 l_context_params_rec_type.NAME := 'ORGN_CODE';
1966 l_context_params_rec_type.VALUE := l_org_code;
1967 X_CONTEXT_PARAMS_TBL(1) := l_context_params_rec_type;
1968
1969 l_context_params_rec_type.NAME := 'ORGN_ID';
1970 l_context_params_rec_type.VALUE := l_org_id;
1971 X_CONTEXT_PARAMS_TBL(2) := l_context_params_rec_type;
1972
1973
1974 l_context_params_rec_type.NAME := 'BATCH_ID';
1975 l_context_params_rec_type.VALUE := l_batch_id;
1976 X_CONTEXT_PARAMS_TBL(3) := l_context_params_rec_type;
1977
1978
1979 l_context_params_rec_type.NAME := 'BATCHSTEP_ID';
1980 l_context_params_rec_type.VALUE := l_batchstep_id;
1981 X_CONTEXT_PARAMS_TBL(4) := l_context_params_rec_type;
1982
1983 l_context_params_rec_type.NAME := 'BATCHSTEP_ACTIVITY_ID';
1984 l_context_params_rec_type.VALUE := l_batchstep_activity_id;
1985 X_CONTEXT_PARAMS_TBL(5) := l_context_params_rec_type;
1986
1987 l_context_params_rec_type.NAME := 'BATCHSTEP_RESOURCE_ID';
1988 l_context_params_rec_type.VALUE := l_batchstep_resource_id;
1989 X_CONTEXT_PARAMS_TBL(6) := l_context_params_rec_type;
1990
1991 l_context_params_rec_type.NAME := 'MATERIAL_DETAIL_ID';
1992 l_context_params_rec_type.VALUE := l_material_detail_id;
1993 X_CONTEXT_PARAMS_TBL(7) := l_context_params_rec_type;
1994
1995 l_context_params_rec_type.NAME := 'PROCESS_PARAM_ID';
1996 l_context_params_rec_type.VALUE := l_process_param_id;
1997 X_CONTEXT_PARAMS_TBL(8) := l_context_params_rec_type;
1998
1999 --Bug 4893056: start
2000 l_context_params_rec_type.NAME := 'INVOKE_MODE';
2001 l_context_params_rec_type.VALUE := l_invoke_mode;
2002 X_CONTEXT_PARAMS_TBL(9) := l_context_params_rec_type;
2003
2004 l_context_params_rec_type.NAME := 'LINE_TYPE';
2005 l_context_params_rec_type.VALUE := l_line_type;
2006 X_CONTEXT_PARAMS_TBL(10) := l_context_params_rec_type;
2007
2008 l_context_params_rec_type.NAME := 'LINE_NO';
2009 l_context_params_rec_type.VALUE := l_line_no;
2010 X_CONTEXT_PARAMS_TBL(11) := l_context_params_rec_type;
2011 --Bug 4893056: end
2012
2013 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2014
2015 EXCEPTION
2016 WHEN GMO_DISABLED_ERR THEN
2017 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2018 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_DISABLED_ERR');
2019 FND_MSG_PUB.ADD;
2020 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2021 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2022 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2023 end if;
2024 WHEN GMO_NOT_ENHANCED_PI_ERR THEN
2025 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2026 X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;
2027 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_NOT_ENHANCED_PI_ERR');
2028 FND_MSG_PUB.ADD;
2029 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2030 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2031 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2032 end if;
2033
2034 WHEN GMO_INVALID_MODULE_ERR THEN
2035 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2036 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_MODULE');
2037 FND_MSG_PUB.ADD;
2038 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2039 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2040 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2041 end if;
2042
2043 WHEN GMO_INSTR_TASK_PARAM_ERR THEN
2044 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2045 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_PARAM_ERR');
2046 FND_MSG_PUB.ADD;
2047 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2048 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2049 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2050 end if;
2051
2052 WHEN GMO_INVALID_TASK_ERR THEN
2053 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2054 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_TASK');
2055 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2056 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2057 FND_MESSAGE.SET_TOKEN('TASK',P_TASK);
2058 FND_MSG_PUB.ADD;
2059 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2060 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2061 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2062 end if;
2063 WHEN GMO_INVALID_ENTITY_ERR THEN
2064 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2065 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
2066 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2067 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2068 FND_MSG_PUB.ADD;
2069 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2070 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2071 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2072 end if;
2073 WHEN OTHERS THEN
2074 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2075 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
2076 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2077 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
2078 FND_MSG_PUB.ADD;
2079 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2080 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2081 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.on_task_load', FALSE);
2082 end if;
2083 END ON_TASK_LOAD;
2084
2085
2086
2087
2088 --This procdeure would process the action performed by the task.
2089
2090 procedure ON_TASK_ACTION (P_ENTITY_NAME IN VARCHAR2,
2091 P_ENTITY_KEY IN VARCHAR2,
2092 P_TASK IN VARCHAR2,
2093 P_TASK_ATTRIBUTE IN VARCHAR2,
2094 P_REQUESTER IN NUMBER,
2095 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2096 X_MSG_COUNT OUT NOCOPY NUMBER,
2097 X_MSG_DATA OUT NOCOPY VARCHAR2)
2098
2099 IS
2100
2101 l_batchstep_id number;
2102 l_material_detail_id number;
2103 l_batchstep_activity_id number;
2104 l_batchstep_resource_id number;
2105 l_instruction_set_id number;
2106 l_return_status varchar2(100);
2107 l_msg_count number;
2108 l_msg_data varchar2(4000);
2109 instruction_create_err exception;
2110 instruction_nullify_err exception;
2111
2112 cursor c_get_steps is select batchstep_id from gme_batch_steps where batch_id = p_entity_key;
2113
2114 cursor c_get_activities is select batchstep_activity_id from gme_batch_step_activities where batch_id = p_entity_key;
2115
2116 cursor c_get_resources is select batchstep_resource_id from gme_batch_step_resources where batch_id = p_entity_key;
2117
2118 cursor c_get_materials is select material_detail_id from gme_material_details where batch_id = p_entity_key;
2119
2120
2121 cursor c_get_step_activities is select batchstep_activity_id from gme_batch_step_activities where batchstep_id = p_entity_key;
2122 cursor c_get_step_resources is select batchstep_resource_id from gme_batch_step_resources where batchstep_id = p_entity_key;
2123
2124 cursor c_get_step_material is select material_detail_id from gme_batch_step_items where batchstep_id = p_entity_key;
2125
2126 cursor c_get_activity_resources is select batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id;
2127
2128
2129 BEGIN
2130
2131 IF (NOT IS_GMO_ENABLED) THEN
2132 RAISE GMO_DISABLED_ERR;
2133 END IF;
2134
2135 IF (NOT IS_BATCH_ENHANCED_PI (P_ENTITY_NAME => P_ENTITY_NAME, P_ENTITY_KEY => P_ENTITY_KEY) ) THEN
2136 RAISE GMO_NOT_ENHANCED_PI_ERR;
2137 END IF;
2138
2139 IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_BATCH AND P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_OPERATION OR p_entity_key is null) THEN
2140 RAISE GMO_INVALID_ENTITY_ERR;
2141 END IF;
2142
2143 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2144 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ON_TASK_ACTION_MSG');
2145 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2146 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2147 FND_MESSAGE.SET_TOKEN('TASK',P_TASK);
2148 FND_MESSAGE.SET_TOKEN('TASK_ATTRIBUTE',P_TASK_ATTRIBUTE);
2149 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2150 end if;
2151
2152
2153 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN
2154
2155 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_TERMINATE OR P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2156 open c_get_steps;
2157 loop
2158 fetch c_get_steps into l_batchstep_id;
2159 exit when c_get_steps%NOTFOUND;
2160
2161 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2162 P_API_VERSION => 1.0,
2163 X_RETURN_STATUS => l_return_status,
2164 X_MSG_COUNT => l_msg_count,
2165 X_MSG_DATA => l_msg_data,
2166 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
2167 P_ENTITY_KEY => l_batchstep_id,
2168 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2169 );
2170
2171 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2172 RAISE instruction_nullify_err;
2173 end if;
2174
2175 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2176
2177
2178 gmo_instruction_grp.create_instance_from_instance (
2179 P_API_VERSION => 1.0,
2180 X_RETURN_STATUS => l_return_status,
2181 X_MSG_COUNT => l_msg_count,
2182 X_MSG_DATA => l_msg_data,
2183 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
2184 P_SOURCE_ENTITY_KEY => l_batchstep_id,
2185 P_TARGET_ENTITY_KEY => l_batchstep_id,
2186 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2187 X_INSTRUCTION_SET_ID => l_instruction_set_id
2188 );
2189 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2190 RAISE instruction_create_err;
2191 end if;
2192 end if;
2193 end loop;
2194 close c_get_steps;
2195
2196 open c_get_activities;
2197 loop
2198 fetch c_get_activities into l_batchstep_activity_id;
2199 exit when c_get_activities%NOTFOUND;
2200
2201 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2202 P_API_VERSION => 1.0,
2203 X_RETURN_STATUS => l_return_status,
2204 X_MSG_COUNT => l_msg_count,
2205 X_MSG_DATA => l_msg_data,
2206 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
2207 P_ENTITY_KEY => l_batchstep_activity_id,
2208 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2209 );
2210
2211 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2212 RAISE instruction_nullify_err;
2213 end if;
2214
2215 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2216 gmo_instruction_grp.create_instance_from_instance (
2217 P_API_VERSION => 1.0,
2218 X_RETURN_STATUS => l_return_status,
2219 X_MSG_COUNT => l_msg_count,
2220 X_MSG_DATA => l_msg_data,
2221 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
2222 P_SOURCE_ENTITY_KEY => l_batchstep_activity_id,
2223 P_TARGET_ENTITY_KEY => l_batchstep_activity_id,
2224 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2225 X_INSTRUCTION_SET_ID => l_instruction_set_id
2226 );
2227 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2228 RAISE instruction_create_err;
2229 end if;
2230 end if;
2231
2232 end loop;
2233 close c_get_activities;
2234
2235 open c_get_resources;
2236 loop
2237 fetch c_get_resources into l_batchstep_resource_id;
2238 exit when c_get_resources%NOTFOUND;
2239
2240 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2241 P_API_VERSION => 1.0,
2242 X_RETURN_STATUS => l_return_status,
2243 X_MSG_COUNT => l_msg_count,
2244 X_MSG_DATA => l_msg_data,
2245 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2246 P_ENTITY_KEY => l_batchstep_resource_id,
2247 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2248 );
2249
2250 if X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2251 RAISE instruction_nullify_err;
2252 end if;
2253
2254 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2255 gmo_instruction_grp.create_instance_from_instance (
2256 P_API_VERSION => 1.0,
2257 X_RETURN_STATUS => l_return_status,
2258 X_MSG_COUNT => l_msg_count,
2259 X_MSG_DATA => l_msg_data,
2260 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2261 P_SOURCE_ENTITY_KEY => l_batchstep_resource_id,
2262 P_TARGET_ENTITY_KEY => l_batchstep_resource_id,
2263 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2264 X_INSTRUCTION_SET_ID => l_instruction_set_id
2265 );
2266 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2267 RAISE instruction_create_err;
2268 end if;
2269 end if;
2270
2271 end loop;
2272 close c_get_resources;
2273
2274 open c_get_materials;
2275 loop
2276 fetch c_get_materials into l_material_detail_id;
2277 exit when c_get_materials%NOTFOUND;
2278
2279 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2280 P_API_VERSION => 1.0,
2281 X_RETURN_STATUS => l_return_status,
2282 X_MSG_COUNT => l_msg_count,
2283 X_MSG_DATA => l_msg_data,
2284 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2285 P_ENTITY_KEY => l_material_detail_id,
2286 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2287 );
2288
2289 if X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2290 RAISE instruction_nullify_err;
2291 end if;
2292
2293 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2294 gmo_instruction_grp.create_instance_from_instance (
2295 P_API_VERSION => 1.0,
2296 X_RETURN_STATUS => l_return_status,
2297 X_MSG_COUNT => l_msg_count,
2298 X_MSG_DATA => l_msg_data,
2299 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2300 P_SOURCE_ENTITY_KEY => l_material_detail_id,
2301 P_TARGET_ENTITY_KEY => l_material_detail_id,
2302 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2303 X_INSTRUCTION_SET_ID => l_instruction_set_id
2304 );
2305 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2306 RAISE instruction_create_err;
2307 end if;
2308 end if;
2309 end loop;
2310 close c_get_materials;
2311
2312 ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN
2313
2314 l_material_detail_id := P_TASK_ATTRIBUTE;
2315 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2316 P_API_VERSION => 1.0,
2317 X_RETURN_STATUS => l_return_status,
2318 X_MSG_COUNT => l_msg_count,
2319 X_MSG_DATA => l_msg_data,
2320 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2321 P_ENTITY_KEY => l_material_detail_id,
2322 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2323 );
2324
2325 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2326 RAISE instruction_nullify_err;
2327 end if;
2328
2329 END IF; -- end IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_TERMINATE OR P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2330
2331 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
2332
2333 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_OPERATION OR P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2334
2335 l_batchstep_id := p_entity_key;
2336 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2337 P_API_VERSION => 1.0,
2338 X_RETURN_STATUS => l_return_status,
2339 X_MSG_COUNT => l_msg_count,
2340 X_MSG_DATA => l_msg_data,
2341 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
2342 P_ENTITY_KEY => l_batchstep_id,
2343 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2344 );
2345
2346 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2347 RAISE instruction_nullify_err;
2348 end if;
2349
2350 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2351
2352
2353 gmo_instruction_grp.create_instance_from_instance (
2354 P_API_VERSION => 1.0,
2355 X_RETURN_STATUS => l_return_status,
2356 X_MSG_COUNT => l_msg_count,
2357 X_MSG_DATA => l_msg_data,
2358 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
2359 P_SOURCE_ENTITY_KEY => l_batchstep_id,
2360 P_TARGET_ENTITY_KEY => l_batchstep_id,
2361 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2362 X_INSTRUCTION_SET_ID => l_instruction_set_id
2363 );
2364 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2365 RAISE instruction_create_err;
2366 end if;
2367 end if;
2368
2369
2370 open c_get_step_material;
2371 loop
2372 fetch c_get_step_material into l_material_detail_id;
2373 exit when c_get_step_material%NOTFOUND;
2374
2375 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2376 P_API_VERSION => 1.0,
2377 X_RETURN_STATUS => l_return_status,
2378 X_MSG_COUNT => l_msg_count,
2379 X_MSG_DATA => l_msg_data,
2380 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2381 P_ENTITY_KEY => l_material_detail_id,
2382 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2383 );
2384
2385 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2386 RAISE instruction_nullify_err;
2387 end if;
2388
2389 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2390 gmo_instruction_grp.create_instance_from_instance (
2391 P_API_VERSION => 1.0,
2392 X_RETURN_STATUS => l_return_status,
2393 X_MSG_COUNT => l_msg_count,
2394 X_MSG_DATA => l_msg_data,
2395 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2396 P_SOURCE_ENTITY_KEY => l_material_detail_id,
2397 P_TARGET_ENTITY_KEY => l_material_detail_id,
2398 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2399 X_INSTRUCTION_SET_ID => l_instruction_set_id
2400 );
2401 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2402 RAISE instruction_create_err;
2403 end if;
2404 end if;
2405
2406 end loop;
2407 close c_get_step_material;
2408
2409
2410 open c_get_step_activities;
2411 loop
2412 fetch c_get_step_activities into l_batchstep_activity_id;
2413 exit when c_get_step_activities%NOTFOUND;
2414
2415 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2416 P_API_VERSION => 1.0,
2417 X_RETURN_STATUS => l_return_status,
2418 X_MSG_COUNT => l_msg_count,
2419 X_MSG_DATA => l_msg_data,
2420 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
2421 P_ENTITY_KEY => l_batchstep_activity_id,
2422 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2423 );
2424
2425 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2426 RAISE instruction_nullify_err;
2427 end if;
2428
2429 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2430 gmo_instruction_grp.create_instance_from_instance (
2431 P_API_VERSION => 1.0,
2432 X_RETURN_STATUS => l_return_status,
2433 X_MSG_COUNT => l_msg_count,
2434 X_MSG_DATA => l_msg_data,
2435 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
2436 P_SOURCE_ENTITY_KEY => l_batchstep_activity_id,
2437 P_TARGET_ENTITY_KEY => l_batchstep_activity_id,
2438 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2439 X_INSTRUCTION_SET_ID => l_instruction_set_id
2440 );
2441 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2442 RAISE instruction_create_err;
2443 end if;
2444 end if;
2445
2446 end loop;
2447 close c_get_step_activities;
2448
2449 open c_get_step_resources;
2450 loop
2451 fetch c_get_step_resources into l_batchstep_resource_id;
2452 exit when c_get_step_resources%NOTFOUND;
2453
2454 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2455 P_API_VERSION => 1.0,
2456 X_RETURN_STATUS => l_return_status,
2457 X_MSG_COUNT => l_msg_count,
2458 X_MSG_DATA => l_msg_data,
2459 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2460 P_ENTITY_KEY => l_batchstep_resource_id,
2461 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2462 );
2463
2464 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2465 RAISE instruction_nullify_err;
2466 end if;
2467
2468 IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN
2469 gmo_instruction_grp.create_instance_from_instance (
2470 P_API_VERSION => 1.0,
2471 X_RETURN_STATUS => l_return_status,
2472 X_MSG_COUNT => l_msg_count,
2473 X_MSG_DATA => l_msg_data,
2474 P_SOURCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2475 P_SOURCE_ENTITY_KEY => l_batchstep_resource_id,
2476 P_TARGET_ENTITY_KEY => l_batchstep_resource_id,
2477 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
2478 X_INSTRUCTION_SET_ID => l_instruction_set_id
2479 );
2480 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2481 RAISE instruction_create_err;
2482 end if;
2483 end if;
2484
2485 end loop;
2486 close c_get_step_resources;
2487
2488 ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_ACTIVITY) THEN
2489
2490 l_batchstep_activity_id := P_TASK_ATTRIBUTE;
2491 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2492 P_API_VERSION => 1.0,
2493 X_RETURN_STATUS => l_return_status,
2494 X_MSG_COUNT => l_msg_count,
2495 X_MSG_DATA => l_msg_data,
2496 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
2497 P_ENTITY_KEY => l_batchstep_activity_id,
2498 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2499 );
2500
2501 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2502 RAISE instruction_nullify_err;
2503 end if;
2504
2505 open c_get_activity_resources;
2506 loop
2507 fetch c_get_activity_resources into l_batchstep_resource_id;
2508 exit when c_get_activity_resources%NOTFOUND;
2509
2510 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2511 P_API_VERSION => 1.0,
2512 X_RETURN_STATUS => l_return_status,
2513 X_MSG_COUNT => l_msg_count,
2514 X_MSG_DATA => l_msg_data,
2515 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2516 P_ENTITY_KEY => l_batchstep_resource_id,
2517 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2518 );
2519
2520 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2521 RAISE instruction_nullify_err;
2522 end if;
2523
2524 end loop;
2525 close c_get_activity_resources;
2526
2527 ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_RESOURCE) THEN
2528
2529 l_batchstep_resource_id := P_TASK_ATTRIBUTE;
2530 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2531 P_API_VERSION => 1.0,
2532 X_RETURN_STATUS => l_return_status,
2533 X_MSG_COUNT => l_msg_count,
2534 X_MSG_DATA => l_msg_data,
2535 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
2536 P_ENTITY_KEY => l_batchstep_resource_id,
2537 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2538 );
2539
2540 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2541 RAISE instruction_nullify_err;
2542 end if;
2543
2544 ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN
2545
2546 l_material_detail_id := P_TASK_ATTRIBUTE;
2547 gmo_instruction_grp.NULLIFY_INSTR_FOR_ENTITY (
2548 P_API_VERSION => 1.0,
2549 X_RETURN_STATUS => l_return_status,
2550 X_MSG_COUNT => l_msg_count,
2551 X_MSG_DATA => l_msg_data,
2552 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
2553 P_ENTITY_KEY => l_material_detail_id,
2554 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE
2555 );
2556
2557 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2558 RAISE instruction_nullify_err;
2559 end if;
2560 END IF;
2561
2562
2563 END IF;
2564
2565 x_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 EXCEPTION
2568 WHEN GMO_DISABLED_ERR THEN
2569 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2570 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_DISABLED_ERR');
2571 FND_MSG_PUB.ADD;
2572 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2573 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2574 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2575 end if;
2576 WHEN GMO_NOT_ENHANCED_PI_ERR THEN
2577 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2578 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_NOT_ENHANCED_PI_ERR');
2579 FND_MSG_PUB.ADD;
2580 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2581 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2582 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2583 end if;
2584 WHEN instruction_nullify_err then
2585 x_return_status := FND_API.G_RET_STS_ERROR;
2586 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2587 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2588 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2589 end if;
2590 WHEN instruction_create_err then
2591 x_return_status := FND_API.G_RET_STS_ERROR;
2592 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2593 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2594 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2595 end if;
2596 WHEN GMO_INVALID_ENTITY_ERR THEN
2597 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2598 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
2599 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2600 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2601 FND_MSG_PUB.ADD;
2602 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2603 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2604 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2605 end if;
2606 WHEN OTHERS THEN
2607 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2608 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
2609 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2610 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
2611 FND_MSG_PUB.ADD;
2612 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2613 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2614 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.on_task_action', FALSE);
2615 end if;
2616 END ON_TASK_ACTION;
2617
2618
2619 --This procdeure would process the save event of the task.
2620
2621 procedure ON_TASK_SAVE (P_FROM_MODULE IN VARCHAR2,
2622 P_ENTITY_NAME IN VARCHAR2,
2623 P_ENTITY_KEY IN VARCHAR2,
2624 P_TASK IN VARCHAR2,
2625 P_TASK_ATTRIBUTE IN VARCHAR2 ,
2626 P_INSTRUCTION_ID IN NUMBER ,
2627 P_INSTRUCTION_PROCESS_ID IN NUMBER ,
2628 P_TASK_IDENTIFIER IN FND_TABLE_OF_VARCHAR2_255,
2629 P_TASK_VALUE IN FND_TABLE_OF_VARCHAR2_255,
2630 P_TASK_ERECORD IN FND_TABLE_OF_VARCHAR2_255,
2631 P_REQUESTER IN NUMBER,
2632 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2633 X_MSG_COUNT OUT NOCOPY NUMBER,
2634 X_MSG_DATA OUT NOCOPY VARCHAR2)
2635
2636 IS
2637
2638 l_return_status varchar2(100);
2639 l_msg_count number;
2640 l_msg_data varchar2(4000);
2641 task_Acknowledgement_err exception;
2642
2643 BEGIN
2644
2645 IF (NOT IS_GMO_ENABLED) THEN
2646 RAISE GMO_DISABLED_ERR;
2647 END IF;
2648
2649 IF (NOT IS_BATCH_ENHANCED_PI (P_ENTITY_NAME => P_ENTITY_NAME, P_ENTITY_KEY => P_ENTITY_KEY) ) THEN
2650 RAISE GMO_NOT_ENHANCED_PI_ERR;
2651 END IF;
2652
2653 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2654 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ON_TASK_SAVE_MSG');
2655 FND_MESSAGE.SET_TOKEN('FROM_MODULE',P_FROM_MODULE);
2656 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2657 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2658 FND_MESSAGE.SET_TOKEN('TASK',P_TASK);
2659 FND_MESSAGE.SET_TOKEN('TASK_ATTRIBUTE',P_TASK_ATTRIBUTE);
2660 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.on_task_save', FALSE);
2661 end if;
2662
2663
2664 IF (P_FROM_MODULE = GMO_CONSTANTS_GRP.FROM_MODULE_PI) THEN
2665
2666 gmo_instruction_grp.SEND_TASK_ACKN (
2667 P_API_VERSION => 1.0,
2668 X_RETURN_STATUS => l_return_status,
2669 X_MSG_COUNT => l_msg_count,
2670 X_MSG_DATA => l_msg_data,
2671 P_INSTRUCTION_ID => P_INSTRUCTION_ID,
2672 P_INSTRUCTION_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
2673 P_ENTITY_KEY => P_ENTITY_KEY,
2674 P_TASK_ERECORD_ID => P_TASK_ERECORD,
2675 P_TASK_IDENTIFIER => P_TASK_IDENTIFIER,
2676 P_TASK_VALUE => P_TASK_VALUE,
2677 P_DISABLE_TASK => GMO_CONSTANTS_GRP.DISABLE_TASK_NO
2678 );
2679
2680
2681 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2682 RAISE task_Acknowledgement_err;
2683 end if;
2684
2685 END IF;
2686
2687 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2688
2689 EXCEPTION
2690 WHEN GMO_NOT_ENHANCED_PI_ERR THEN
2691 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2692 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_NOT_ENHANCED_PI_ERR');
2693 FND_MSG_PUB.ADD;
2694 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2695 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2696 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_save', FALSE);
2697 end if;
2698 WHEN task_Acknowledgement_err then
2699 x_return_status := FND_API.G_RET_STS_ERROR;
2700 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2701 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2702 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_save', FALSE);
2703 end if;
2704 WHEN GMO_DISABLED_ERR THEN
2705 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2706 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_DISABLED_ERR');
2707 FND_MSG_PUB.ADD;
2708 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2709 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2710 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.on_task_save', FALSE);
2711 end if;
2712 WHEN OTHERS THEN
2713 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2714 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
2715 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2716 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
2717 FND_MSG_PUB.ADD;
2718 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2719 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2720 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.on_task_save', FALSE);
2721 end if;
2722
2723 END ON_TASK_SAVE;
2724
2725
2726 --This procdeure would check if the step is locked or not
2727
2728 procedure GET_ENTITY_LOCK_STATUS (P_ENTITY_NAME IN VARCHAR2,
2729 P_ENTITY_KEY IN VARCHAR2,
2730 P_REQUESTER IN NUMBER,
2731 X_LOCK_STATUS OUT NOCOPY VARCHAR2,
2732 X_LOCKED_BY_STATUS OUT NOCOPY VARCHAR2,
2733 X_LOCK_ALLOWED OUT NOCOPY VARCHAR2,
2734 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2735 X_MSG_COUNT OUT NOCOPY NUMBER,
2736 X_MSG_DATA OUT NOCOPY VARCHAR2)
2737
2738 IS
2739
2740 l_batchstep_id number;
2741 l_count number;
2742 l_step_status number;
2743 cursor is_step_locked is select count(*) from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id;
2744 cursor is_step_locked_by is select count(*) from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id and lock_requester = P_REQUESTER;
2745
2746 cursor get_step_id_for_resource is select batchstep_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;
2747 cursor get_step_id_for_activity is select batchstep_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;
2748 cursor get_step_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = p_entity_key;
2749
2750 cursor get_step_detail is select step_status from gme_batch_steps where batchstep_id = l_batchstep_id;
2751
2752 no_step_found_err exception;
2753
2754 BEGIN
2755
2756 IF (NOT IS_GMO_ENABLED) THEN
2757 RAISE GMO_DISABLED_ERR;
2758 END IF;
2759
2760 IF (NOT IS_BATCH_ENHANCED_PI (P_ENTITY_NAME => P_ENTITY_NAME, P_ENTITY_KEY => P_ENTITY_KEY) ) THEN
2761 RAISE GMO_NOT_ENHANCED_PI_ERR;
2762 END IF;
2763
2764 IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
2765 l_batchstep_id := P_ENTITY_KEY;
2766 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY) THEN
2767 open get_step_id_for_activity;
2768 fetch get_step_id_for_activity into l_batchstep_id;
2769 close get_step_id_for_activity;
2770 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN
2771 open get_step_id_for_resource;
2772 fetch get_step_id_for_resource into l_batchstep_id;
2773 close get_step_id_for_resource;
2774 ELSIF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_MATERIAL) THEN
2775 open get_step_id_for_material;
2776 fetch get_step_id_for_material into l_batchstep_id;
2777 close get_step_id_for_material;
2778 ELSE
2779 RAISE gmo_invalid_entity_err;
2780 END IF;
2781
2782 IF (l_batchstep_id is null) THEN
2783 RAISE no_step_found_err;
2784 END IF;
2785
2786 open is_step_locked;
2787 fetch is_step_locked into l_count;
2788 close is_step_locked;
2789
2790 IF (l_count = 0) THEN
2791 X_LOCK_STATUS := GMO_CONSTANTS_GRP.NO;
2792 ELSE
2793 X_LOCK_STATUS := GMO_CONSTANTS_GRP.YES;
2794 END IF;
2795
2796 open is_step_locked_by;
2797 fetch is_step_locked_by into l_count;
2798 close is_step_locked_by;
2799
2800 IF (l_count = 0) THEN
2801 X_LOCKED_BY_STATUS := GMO_CONSTANTS_GRP.NO;
2802 ELSE
2803 X_LOCKED_BY_STATUS := GMO_CONSTANTS_GRP.YES;
2804 END IF;
2805
2806 open get_step_detail;
2807 fetch get_step_detail into l_step_status;
2808 close get_step_detail;
2809
2810 IF (l_step_status = GMO_CONSTANTS_GRP.STEP_WIP_STATUS and X_LOCKED_BY_STATUS <> GMO_CONSTANTS_GRP.YES and X_LOCK_STATUS <> GMO_CONSTANTS_GRP.YES) THEN
2811 X_LOCK_ALLOWED := GMO_CONSTANTS_GRP.YES;
2812 ELSE
2813 X_LOCK_ALLOWED := GMO_CONSTANTS_GRP.NO;
2814 END IF;
2815
2816 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2817 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_ENTITY_LOCK_MSG');
2818 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2819 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2820 FND_MESSAGE.SET_TOKEN('REQUESTER',P_REQUESTER);
2821 FND_MESSAGE.SET_TOKEN('LOCK_STATUS',X_LOCK_STATUS);
2822 FND_MESSAGE.SET_TOKEN('LOCKED_BY_STATUS',X_LOCKED_BY_STATUS);
2823 FND_MESSAGE.SET_TOKEN('LOCK_ALLOWED',X_LOCK_ALLOWED);
2824 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2825 end if;
2826
2827 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2828
2829 EXCEPTION
2830 WHEN GMO_NOT_ENHANCED_PI_ERR THEN
2831 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2832 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_NOT_ENHANCED_PI_ERR');
2833 FND_MSG_PUB.ADD;
2834 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2835 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2836 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2837 end if;
2838 WHEN GMO_DISABLED_ERR THEN
2839 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2840 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_DISABLED_ERR');
2841 FND_MSG_PUB.ADD;
2842 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2843 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2844 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2845 end if;
2846 WHEN NO_STEP_FOUND_ERR THEN
2847 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2848 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
2849 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2850 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2851 FND_MSG_PUB.ADD;
2852 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2853 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2854 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2855 end if;
2856 WHEN GMO_INVALID_ENTITY_ERR THEN
2857 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2858 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INVALID_ENTITY');
2859 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
2860 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
2861 FND_MSG_PUB.ADD;
2862 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2863 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2864 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2865 end if;
2866 WHEN OTHERS THEN
2867 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
2868 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_UNEXPECTED_DB_ERR');
2869 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2870 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
2871 FND_MSG_PUB.ADD;
2872 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2873 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2874 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.get_entity_lock_status', FALSE);
2875 end if;
2876 END GET_ENTITY_LOCK_STATUS;
2877
2878 --Bug 5224634: start
2879 procedure GET_BATCHSTEP_NODES ( P_BATCH_ID IN NUMBER,
2880 P_BATCHSTEP_ID IN NUMBER,
2881 X_NAME OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
2882 X_ENTITY_NAME OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
2883 X_ENTITY_KEY OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
2884 X_ENTITY_LEVEL OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
2885 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2886 X_MSG_COUNT OUT NOCOPY NUMBER,
2887 X_MSG_DATA OUT NOCOPY VARCHAR2)
2888 IS
2889
2890 t_name fnd_table_of_varchar2_255;
2891 t_entity_name fnd_table_of_varchar2_255;
2892 t_entity_key fnd_table_of_varchar2_255;
2893 t_entity_level fnd_table_of_varchar2_255;
2894
2895 l_entity_name varchar2(255);
2896 l_entity_key varchar2(255);
2897
2898 l_batchstep_activity_id number;
2899 l_oprn_no varchar2(255);
2900 l_batchstep_id number;
2901 l_activity varchar2(255);
2902 l_batchstep_resource_id number;
2903 l_resources varchar2(255);
2904 l_material_detail_id number;
2905 l_material_desc varchar2(255);
2906
2907 l_counter number;
2908 l_instr_count number;
2909
2910 cursor c_get_oprn is select a.batchstep_no || '-' || oprn_no, batchstep_id from
2911 gme_batch_steps a, gmd_operations_b b where a.oprn_id = b.oprn_id and
2912 batchstep_id= P_BATCHSTEP_ID and batch_id = P_BATCH_ID;
2913
2914 cursor c_get_activity is select activity, batchstep_activity_id from
2915 gme_batch_step_activities where batchstep_id=P_BATCHSTEP_ID and batch_id =
2916 P_BATCH_ID;
2917
2918 cursor c_get_resource is select resources, batchstep_resource_id from
2919 gme_batch_step_resources where batchstep_id=P_BATCHSTEP_ID and batch_id =
2920 P_BATCH_ID and batchstep_activity_id = l_batchstep_activity_id;
2921
2922 cursor c_get_material is select e.Concatenated_segments, a.material_detail_id
2923 from gme_material_details a, gme_batch_step_items b, MTL_SYSTEM_ITEMS_KFV e
2924 where a.material_detail_id = b.material_detail_id
2925 and (a.inventory_item_id = e.inventory_item_id and a.organization_id =
2926 e.organization_id)
2927 and b.batchstep_id = P_BATCHSTEP_ID and a.batch_id = P_BATCH_ID;
2928
2929
2930 begin
2931
2932 t_name := fnd_table_of_varchar2_255();
2933 t_entity_name := fnd_table_of_varchar2_255();
2934 t_entity_key := fnd_table_of_varchar2_255();
2935 t_entity_level := fnd_table_of_varchar2_255();
2936
2937 x_name := fnd_table_of_varchar2_255();
2938 x_entity_name := fnd_table_of_varchar2_255();
2939 x_entity_key := fnd_table_of_varchar2_255();
2940 x_entity_level := fnd_table_of_varchar2_255();
2941
2942 l_counter := 0;
2943
2944 open c_get_oprn;
2945 fetch c_get_oprn into l_oprn_no, l_batchstep_id;
2946 close c_get_oprn;
2947
2948 t_name.extend;
2949 t_entity_name.extend;
2950 t_entity_key.extend;
2951 t_entity_level.extend;
2952 l_counter := l_counter + 1;
2953
2954 t_name(l_counter) := l_oprn_no;
2955 t_entity_name(l_counter) := GMO_CONSTANTS_GRP.ENTITY_OPERATION;
2956 t_entity_key(l_counter) := l_batchstep_id;
2957 t_entity_level(l_counter) := '1';
2958
2959
2960
2961 open c_get_material;
2962 loop
2963 fetch c_get_material into l_material_desc, l_material_detail_id;
2964 exit when c_get_material%NOTFOUND;
2965
2966 t_name.extend;
2967 t_entity_name.extend;
2968 t_entity_key.extend;
2969 t_entity_level.extend;
2970 l_counter := l_counter + 1;
2971
2972 t_name(l_counter) := l_material_desc;
2973 t_entity_name(l_counter) := GMO_CONSTANTS_GRP.ENTITY_MATERIAL;
2974 t_entity_key(l_counter) := l_material_detail_id;
2975 t_entity_level(l_counter) := '2';
2976
2977
2978 end loop;
2979 close c_get_material;
2980
2981 open c_get_activity;
2982 loop
2983 fetch c_get_activity into l_activity, l_batchstep_activity_id;
2984 exit when c_get_activity%NOTFOUND;
2985
2986 t_name.extend;
2987 t_entity_name.extend;
2988 t_entity_key.extend;
2989 t_entity_level.extend;
2990 l_counter := l_counter + 1;
2991
2992 t_name(l_counter) := l_activity;
2993 t_entity_name(l_counter) := GMO_CONSTANTS_GRP.ENTITY_ACTIVITY;
2994 t_entity_key(l_counter) := l_batchstep_activity_id;
2995 t_entity_level(l_counter) := '2';
2996
2997
2998 open c_get_resource;
2999 loop
3000 fetch c_get_resource into l_resources, l_batchstep_resource_id;
3001 exit when c_get_resource%NOTFOUND;
3002
3003 t_name.extend;
3004 t_entity_name.extend;
3005 t_entity_key.extend;
3006 t_entity_level.extend;
3007 l_counter := l_counter + 1;
3008
3009 t_name(l_counter) := l_resources;
3010 t_entity_name(l_counter) := GMO_CONSTANTS_GRP.ENTITY_RESOURCE;
3011 t_entity_key(l_counter) := l_batchstep_resource_id;
3012 t_entity_level(l_counter) := '3';
3013
3014
3015 end loop;
3016 close c_get_resource;
3017
3018
3019
3020 end loop;
3021 close c_get_activity;
3022
3023 l_counter := 0;
3024
3025 for i in 1 .. t_name.count loop
3026
3027 l_entity_name := t_entity_name(i);
3028 l_entity_key := t_entity_key(i);
3029
3030
3031 x_name.extend;
3032 x_entity_name.extend;
3033 x_entity_key.extend;
3034 x_entity_level.extend;
3035 l_counter := l_counter + 1;
3036
3037 x_name(l_counter) := t_name(i);
3038 x_entity_name(l_counter) := t_entity_name(i);
3039 x_entity_key(l_counter) := t_entity_key(i);
3040 x_entity_level(l_counter) := t_entity_level(i);
3041
3042 end loop;
3043
3044 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3045
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3049 FND_MESSAGE.SET_NAME('GMO','GET_BATCHSTEP_NODES');
3050 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
3051 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
3052 FND_MSG_PUB.ADD;
3053 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
3054 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3055 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,'gmo.plsql.gmo_vbatch_pvt.get_batchstep_nodes', FALSE);
3056 end if;
3057 END GET_BATCHSTEP_NODES;
3058
3059 --Bug 5224634: end
3060
3061
3062 -- Kapil ME LCF-GMO
3063 -- This procedure instantiates PI for LCF Batches
3064 procedure INSTANTIATE_INSTR_FOR_LCF (P_ENTITY_NAME IN VARCHAR2,
3065 P_ENTITY_KEY IN VARCHAR2,
3066 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3067 X_MSG_COUNT OUT NOCOPY NUMBER,
3068 X_MSG_DATA OUT NOCOPY VARCHAR2)
3069 IS
3070
3071 l_recipe_id number;
3072 l_batchstep_id number;
3073 l_routingstep_id number;
3074 l_oprn_id number;
3075 l_oprn_line_id number;
3076 l_resources varchar2(16);
3077 l_material_detail_id number;
3078 l_formulaline_id number;
3079 l_inventory_item_id number;
3080 l_batchstep_activity_id number;
3081 l_batchstep_resource_id number;
3082 l_instruction_set_id number;
3083 l_return_status varchar2(100);
3084 l_msg_count number;
3085 l_msg_data varchar2(4000);
3086 instruction_create_err exception;
3087
3088 l_entity_display_name varchar2(255);
3089 l_invalid_instr fnd_table_of_varchar2_255;
3090 l_invalid_instr_master fnd_table_of_varchar2_255;
3091 l_invalid_entity_master fnd_table_of_varchar2_255;
3092
3093 j binary_integer;
3094 k binary_integer;
3095 l_attribute_Status varchar2(1);
3096 INVALID_ATTRIBUTE_ASSOC_ERR exception;
3097
3098 cursor c_get_steps is
3099 select
3100 batchstep_id,
3101 routingstep_id,
3102 a.oprn_id,
3103 batchstep_no || '-' || b.oprn_no
3104 from gme_batch_steps a, gmd_operations_b b where a.oprn_id = b.oprn_id and batch_id = p_entity_key;
3105
3106 cursor c_get_activities is
3107 select batchstep_activity_id,
3108 routingstep_id,
3109 oprn_line_id ,
3110 activity
3111 from gme_batch_step_activities gbsa,
3112 gme_batch_steps gbs
3113 where gbsa.batchstep_id = gbs.batchstep_id and gbsa.batch_id = p_entity_key;
3114
3115 cursor c_get_resources is
3116 select batchstep_resource_id,
3117 routingstep_id,
3118 oprn_line_id,
3119 resources ,
3120 gbsa.activity || '-' || resources
3121 from gme_batch_step_resources gbsr,
3122 gme_batch_step_Activities gbsa,
3123 gme_batch_steps gbs
3124 where gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
3125 and gbsa.batchstep_id = gbs.batchstep_id and gbsr.batch_id = p_entity_key;
3126
3127
3128 BEGIN
3129
3130 j := 0;
3131 k := 0;
3132 l_invalid_entity_master := fnd_table_of_varchar2_255();
3133 l_invalid_instr_master := fnd_table_of_varchar2_255();
3134
3135 open c_get_steps;
3136 loop
3137 fetch c_get_steps into l_batchstep_id, l_routingstep_id, l_oprn_id, l_entity_display_name;
3138 exit when c_get_steps%NOTFOUND;
3139
3140 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
3141 P_API_VERSION => 1.0,
3142 X_RETURN_STATUS => l_return_status,
3143 X_MSG_COUNT => l_msg_count,
3144 X_MSG_DATA => l_msg_data,
3145 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
3146 P_DEFINITION_ENTITY_KEY => l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_id,
3147 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
3148 P_INSTANCE_ENTITY_KEY => l_batchstep_id,
3149 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
3150 X_INSTRUCTION_SET_ID => l_instruction_set_id
3151 );
3152
3153 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3154 RAISE instruction_create_err;
3155 end if;
3156 update_task_attribute
3157 (
3158 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
3159 P_ENTITY_KEY => l_batchstep_id,
3160 P_INSTRUCTION_SET_ID => l_instruction_set_id,
3161 X_ATTRIBUTE_STATUS => l_attribute_status,
3162 X_INVALID_INSTR => l_invalid_instr
3163 );
3164 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
3165 for i in 1 .. l_invalid_instr.count loop
3166 j := j + 1;
3167 l_invalid_instr_master.extend;
3168 l_invalid_instr_master (j) := l_invalid_instr(i);
3169 end loop;
3170 k := k+1;
3171 l_invalid_entity_master.extend;
3172 l_invalid_entity_master (k) := l_entity_display_name;
3173 end if;
3174
3175 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3176 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
3177 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
3178 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
3179 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','STEP');
3180 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_id);
3181 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
3182 end if;
3183
3184 end loop;
3185 close c_get_steps;
3186
3187 open c_get_activities;
3188 loop
3189 fetch c_get_activities into l_batchstep_activity_id, l_routingstep_id, l_oprn_line_id, l_entity_display_name;
3190 exit when c_get_activities%NOTFOUND;
3191
3192 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
3193 P_API_VERSION => 1.0,
3194 X_RETURN_STATUS => l_return_status,
3195 X_MSG_COUNT => l_msg_count,
3196 X_MSG_DATA => l_msg_data,
3197 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
3198 P_DEFINITION_ENTITY_KEY => l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_line_id,
3199 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
3200 P_INSTANCE_ENTITY_KEY => l_batchstep_activity_id,
3201 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
3202 X_INSTRUCTION_SET_ID => l_instruction_set_id
3203 );
3204
3205 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3206 RAISE instruction_create_err;
3207 end if;
3208
3209 update_task_attribute
3210 (
3211 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
3212 P_ENTITY_KEY => l_batchstep_activity_id,
3213 P_INSTRUCTION_SET_ID => l_instruction_set_id,
3214 X_ATTRIBUTE_STATUS => l_attribute_status,
3215 X_INVALID_INSTR => l_invalid_instr
3216 );
3217 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
3218 for i in 1 .. l_invalid_instr.count loop
3219 j := j + 1;
3220 l_invalid_instr_master.extend;
3221 l_invalid_instr_master (j) := l_invalid_instr(i);
3222 end loop;
3223 k := k+1;
3224 l_invalid_entity_master.extend;
3225 l_invalid_entity_master (k) := l_entity_display_name;
3226 end if;
3227
3228 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3229 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
3230 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
3231 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
3232 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','ACTIVITY');
3233 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_activity_id);
3234 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
3235 end if;
3236
3237 end loop;
3238 close c_get_activities;
3239
3240 open c_get_resources;
3241 loop
3242 fetch c_get_resources into l_batchstep_resource_id, l_routingstep_id, l_oprn_line_id, l_resources, l_entity_display_name;
3243 exit when c_get_resources%NOTFOUND;
3244
3245 gmo_instruction_grp.CREATE_INSTANCE_FROM_DEFN (
3246 P_API_VERSION => 1.0,
3247 X_RETURN_STATUS => l_return_status,
3248 X_MSG_COUNT => l_msg_count,
3249 X_MSG_DATA => l_msg_data,
3250 P_DEFINITION_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
3251 P_DEFINITION_ENTITY_KEY => l_routingstep_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_oprn_line_id || GMO_CONSTANTS_GRP.ENTITY_KEY_SEPARATOR || l_resources,
3252 P_INSTANCE_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
3253 P_INSTANCE_ENTITY_KEY => l_batchstep_resource_id,
3254 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
3255 X_INSTRUCTION_SET_ID => l_instruction_set_id
3256 );
3257
3258 if l_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3259 RAISE instruction_create_err;
3260 end if;
3261
3262 update_task_attribute
3263 (
3264 P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
3265 P_ENTITY_KEY => l_batchstep_resource_id,
3266 P_INSTRUCTION_SET_ID => l_instruction_set_id,
3267 X_ATTRIBUTE_STATUS => l_attribute_status,
3268 X_INVALID_INSTR => l_invalid_instr
3269 );
3270 if (l_attribute_status <> 'S' and l_invalid_instr.count > 0) then
3271 for i in 1 .. l_invalid_instr.count loop
3272 j := j + 1;
3273 l_invalid_instr_master.extend;
3274 l_invalid_instr_master (j) := l_invalid_instr(i);
3275 end loop;
3276 k := k+1;
3277 l_invalid_entity_master.extend;
3278 l_invalid_entity_master (k) := l_entity_display_name;
3279 end if;
3280
3281 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3282 FND_MESSAGE.SET_NAME('GMO','GMO_VBATCH_INSTR_CREATE_MSG');
3283 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
3284 FND_MESSAGE.SET_TOKEN('ENTITY_KEY',P_ENTITY_KEY);
3285 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_NAME','RESOURCE');
3286 FND_MESSAGE.SET_TOKEN('INSTR_ENTITY_KEY',l_batchstep_resource_id);
3287 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,'gmo.plsql.gmo_vbatch_pvt.instantiate_advanced_pi', FALSE);
3288 end if;
3289
3290 end loop;
3291 close c_get_resources;
3292
3293 if (l_invalid_entity_master.count > 0) then
3294 RAISE INVALID_ATTRIBUTE_ASSOC_ERR;
3295 end if;
3296
3297 x_return_status := FND_API.G_RET_STS_SUCCESS;
3298
3299 END INSTANTIATE_INSTR_FOR_LCF;
3300
3301
3302 END GMO_VBATCH_PVT;