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