[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_FETCH_PUB
Source
1 PACKAGE BODY GMD_RECIPE_FETCH_PUB AS
2 /* $Header: GMDPRCFB.pls 120.15.12020000.2 2012/07/17 10:04:01 mtou ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FETCH_PUB';
5
6 /* IN Parameters:
7 P_api_version - standard parameter
8 P_init_msg_list - standard parameter Should be FND_API.G_FALSE
9 P_commit - standard parameter. Should be FND_API.G_FALSE
10 This procedure does no insert/update/delete
11 P_validation_level - standard parameter
12 OUT Parameters:
13 x_return_status - standard parameter. S=success,E=expected error,
14 U=unexpected error
15 x_msg_count - standard parameter. Num of messages generated
16 x_msg_data - standard parameter. If only1 msg, here it is
17 x_return_code - num rows returned or SQLCODE (Database error number)*/
18 /*******************************************************************************
19 * Procedure get_recipe_id
20 *
21 * Procedure:- This returns the recipe_id based on the validity_rules_id
22 * passed to it.
23 *
24 * Author :Pawan Kumar
25 *
26 *********************************************************************************/
27 PROCEDURE get_recipe_id(
28 p_api_version IN NUMBER ,
29 p_init_msg_list IN VARCHAR2 ,
30 p_recipe_validity_rule_id IN NUMBER ,
31 x_return_status OUT NOCOPY VARCHAR2 ,
32 x_msg_count OUT NOCOPY NUMBER ,
33 x_msg_data OUT NOCOPY VARCHAR2 ,
34 x_return_code OUT NOCOPY NUMBER ,
35 X_recipe_id OUT NOCOPY NUMBER
36 ) IS
37
38 /** local cursor to fetch the recipe_id from recipe_validity_rules table **/
39 CURSOR get_recp IS
40 SELECT recipe_id
41 FROM gmd_recipe_validity_rules
42 WHERE recipe_Validity_rule_id = p_recipe_Validity_rule_id ;
43
44
45 /*** local Variables ***/
46 l_api_name CONSTANT VARCHAR2(30) := 'get_recipe_id';
47 l_api_version CONSTANT NUMBER := 1.0;
48
49 BEGIN
50 IF (NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
51 l_api_name, G_PKG_NAME)) THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54 IF FND_API.to_Boolean(p_init_msg_list) THEN
55 FND_MSG_PUB.initialize;
56 END IF;
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 OPEN get_recp;
60 FETCH get_recp into x_recipe_id;
61 IF get_recp%NOTFOUND THEN
62 RAISE fnd_api.g_exc_error;
63 END IF;
64 CLOSE get_recp;
65
66 -- standard call to get msge cnt, and if cnt is 1, get mesg info
67 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
68
69 EXCEPTION
70 WHEN FND_API.G_EXC_ERROR THEN
71 X_return_code := SQLCODE;
72 x_return_status := FND_API.G_RET_STS_ERROR;
73 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
74
75 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
76 X_return_code := SQLCODE;
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
79
80 WHEN OTHERS THEN
81
82 X_return_code := SQLCODE;
83 x_return_status := FND_API.G_RET_STS_ERROR;
84 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
85
86 END get_recipe_id;
87
88
89 /*******************************************************************************
90 * Procedure get_routing_id
91 *
92 * Procedure:- This returns the routing_id attached to the given recipe_id
93 *
94 *
95 * Author :Pawan Kumar
96 *
97 *********************************************************************************/
98
99 PROCEDURE get_routing_id
100 ( p_api_version IN NUMBER ,
101 p_init_msg_list IN VARCHAR2 ,
102 p_recipe_no IN Varchar2 ,
103 p_recipe_version IN NUMBER ,
104 p_recipe_id IN NUMBER ,
105 x_return_status OUT NOCOPY VARCHAR2 ,
106 x_msg_count OUT NOCOPY NUMBER ,
107 x_msg_data OUT NOCOPY VARCHAR2 ,
108 x_return_code OUT NOCOPY NUMBER ,
109 x_routing_id OUT NOCOPY NUMBER
110 ) IS
111
112 -- local cursor to fetch the routing_id from gmd_recipes table
113
114 CURSOR get_rout IS
115 SELECT routing_id
116 FROM gmd_recipes_b
117 WHERE recipe_id = p_recipe_id OR
118 (recipe_no = p_recipe_no AND recipe_version = p_recipe_version);
119
120
121 /*** local Variables ***/
122 l_api_name CONSTANT VARCHAR2(30) := 'get_routing_id';
123 l_api_version CONSTANT NUMBER := 1.0;
124
125 BEGIN
126 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
127 l_api_name, G_PKG_NAME) THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130 IF FND_API.to_Boolean(p_init_msg_list) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134
135 OPEN get_rout;
136 FETCH get_rout into x_routing_id;
137 IF get_rout%NOTFOUND THEN
138 RAISE fnd_api.g_exc_error;
139 END IF;
140 CLOSE get_rout;
141
142 -- standard call to get msge cnt, and if cnt is 1, get mesg info
143 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
144
145 EXCEPTION
146 WHEN FND_API.G_EXC_ERROR THEN
147 X_return_code := SQLCODE;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
150
151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
152 X_return_code := SQLCODE;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
155
156 WHEN OTHERS THEN
157
158 X_return_code := SQLCODE;
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
161
162 END get_routing_id;
163
164 /*******************************************************************************
165 * Procedure get_rout_hdr
166 *
167 * Procedure:- This returns the total rout header information based on the
168 * recipe_id passed to it.
169 *
170 *
171 * Author :Pawan Kumar
172 *
173 *********************************************************************************/
174
175
176 PROCEDURE get_rout_hdr
177 ( p_api_version IN NUMBER ,
178 p_init_msg_list IN VARCHAR2 ,
179 p_recipe_id IN NUMBER ,
180 x_return_status OUT NOCOPY VARCHAR2 ,
181 x_msg_count OUT NOCOPY NUMBER ,
182 x_msg_data OUT NOCOPY VARCHAR2 ,
183 x_return_code OUT NOCOPY NUMBER ,
184 x_rout_out OUT NOCOPY recipe_rout_tbl
185 )
186 IS
187
188 /* local Variables */
189 l_api_name VARCHAR2(30) := 'get_rout_hdr';
190 l_api_version NUMBER := 1.0;
191 i NUMBER := 0;
192
193 CURSOR cur_rout_hdr IS
194
195 select routing_id, routing_no,routing_vers, routing_desc, routing_class, routing_qty,
196 routing_uom, delete_mark,text_code,inactive_ind,enforce_step_dependency,in_use,creation_date,created_by,
197 last_update_login, last_update_date , last_updated_by,process_loss, contiguous_ind,
198 effective_start_date, effective_end_date,owner_id,routing_status,OWNER_ORGANIZATION_ID,attribute_category,attribute1,
199 attribute2, attribute3,attribute4, attribute5, attribute6,
200 attribute7, attribute8, attribute9, attribute10,
201 attribute11, attribute12, attribute13, attribute14,
202 attribute15, attribute16, attribute17, attribute18,
203 attribute19, attribute20, attribute21, attribute22,
204 attribute23, attribute24, attribute25, attribute26,
205 attribute27, attribute28, attribute29, attribute30
206 from fm_rout_hdr
207 where routing_id = (select routing_id from gmd_recipes_b where recipe_id = p_recipe_id)
208
209 ORDER BY routing_id;
210
211 begin
212
213 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
214 l_api_name, G_PKG_NAME) THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 IF FND_API.to_Boolean(p_init_msg_list) THEN
218 FND_MSG_PUB.initialize;
219 END IF;
220
221 For get_rec IN cur_rout_hdr LOOP
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 i := i + 1;
225
226 x_rout_out(i).routing_id := get_rec.routing_id ;
227 x_rout_out(i).routing_no := get_rec.routing_no ;
228 x_rout_out(i).routing_vers := get_rec.routing_vers ;
229 x_rout_out(i).routing_desc := get_rec.routing_desc ;
230 x_rout_out(i).routing_class := get_rec.routing_class ;
231 x_rout_out(i).routing_qty := get_rec.routing_qty ;
232 x_rout_out(i).routing_uom := get_rec.routing_uom ;
233 x_rout_out(i).delete_mark := get_rec.delete_mark ;
234 x_rout_out(i).process_loss := get_rec.process_loss ;
235 x_rout_out(i).effective_start_date := get_rec.effective_start_date ;
236 x_rout_out(i).effective_end_date := get_rec.effective_end_date ;
237 x_rout_out(i).owner_id := get_rec.owner_id ;
238 x_rout_out(i).routing_status := get_rec.routing_status ;
239 x_rout_out(i).OWNER_ORGANIZATION_ID := get_rec.owner_organization_id ;
240 x_rout_out(i).inactive_ind := get_rec.inactive_ind ;
241 x_rout_out(i).enforce_step_dependency := get_rec.enforce_step_dependency ;
242 x_rout_out(i).contiguous_ind := get_rec.contiguous_ind ;
243 x_rout_out(i).text_code := get_rec.text_code ;
244 x_rout_out(i).creation_date := get_rec.creation_date ;
245 x_rout_out(i).created_by := get_rec.created_by ;
246 x_rout_out(i).last_updated_by := get_rec.last_updated_by ;
247 x_rout_out(i).last_update_date := get_rec.last_update_date ;
248 x_rout_out(i).last_update_login := get_rec.last_update_login ;
249 x_rout_out(i).attribute_category := get_rec.attribute_category ;
250 x_rout_out(i).attribute1 := get_rec.attribute1 ;
251 x_rout_out(i).attribute2 := get_rec.attribute2 ;
252 x_rout_out(i).attribute3 := get_rec.attribute3 ;
253 x_rout_out(i).attribute4 := get_rec.attribute4 ;
254 x_rout_out(i).attribute5 := get_rec.attribute5 ;
255 x_rout_out(i).attribute6 := get_rec.attribute6 ;
256 x_rout_out(i).attribute7 := get_rec.attribute7 ;
257 x_rout_out(i).attribute8 := get_rec.attribute8 ;
258 x_rout_out(i).attribute9 := get_rec.attribute9 ;
259 x_rout_out(i).attribute10 := get_rec.attribute10 ;
260 x_rout_out(i).attribute11 := get_rec.attribute11 ;
261 x_rout_out(i).attribute12 := get_rec.attribute12 ;
262 x_rout_out(i).attribute13 := get_rec.attribute13 ;
263 x_rout_out(i).attribute14 := get_rec.attribute14 ;
264 x_rout_out(i).attribute15 := get_rec.attribute15 ;
265 x_rout_out(i).attribute16 := get_rec.attribute16 ;
266 x_rout_out(i).attribute17 := get_rec.attribute17 ;
267 x_rout_out(i).attribute18 := get_rec.attribute18 ;
268 x_rout_out(i).attribute19 := get_rec.attribute19 ;
269 x_rout_out(i).attribute20 := get_rec.attribute20 ;
270 x_rout_out(i).attribute21 := get_rec.attribute21 ;
271 x_rout_out(i).attribute22 := get_rec.attribute22 ;
272 x_rout_out(i).attribute23 := get_rec.attribute23 ;
273 x_rout_out(i).attribute24 := get_rec.attribute24 ;
274 x_rout_out(i).attribute25 := get_rec.attribute25 ;
275 x_rout_out(i).attribute26 := get_rec.attribute26 ;
276 x_rout_out(i).attribute27 := get_rec.attribute27 ;
277 x_rout_out(i).attribute28 := get_rec.attribute28 ;
278 x_rout_out(i).attribute29 := get_rec.attribute29 ;
279 x_rout_out(i).attribute30 := get_rec.attribute30 ;
280
281 END LOOP;
282
283 IF i= 0 THEN
284 RAISE fnd_api.g_exc_error;
285 END IF;
286
287 -- standard call to get msge cnt, and if cnt is 1, get mesg info
288 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
289
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 X_return_code := SQLCODE;
293 x_return_status := FND_API.G_RET_STS_ERROR;
294 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
295
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 X_return_code := SQLCODE;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
300
301 WHEN OTHERS THEN
302 X_return_code := SQLCODE;
303 x_return_status := FND_API.G_RET_STS_ERROR;
304 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
305
306 END get_rout_hdr;
307
308 /*******************************************************************************
309 * Procedure get_formula_id
310 *
311 * Procedure:- This returns the formula_id information based on the
312 * recipe_id passed to it.
313 *
314 *
315 * Author :Pawan Kumar
316 *
317 *********************************************************************************/
318
319 PROCEDURE get_formula_id
320
321 ( p_api_version IN NUMBER ,
322 p_init_msg_list IN VARCHAR2 ,
323 p_recipe_no IN VARCHAR2 ,
324 p_recipe_version IN NUMBER ,
325 p_recipe_id IN NUMBER ,
326 x_return_status OUT NOCOPY VARCHAR2 ,
327 x_msg_count OUT NOCOPY NUMBER ,
328 x_msg_data OUT NOCOPY VARCHAR2 ,
329 x_return_code OUT NOCOPY NUMBER ,
330 x_formula_id OUT NOCOPY NUMBER
331 ) IS
332
333 -- local cursor to fetch the formula_id from gmd_recipes table
334
335 CURSOR get_form IS
336 select formula_id
337 from gmd_recipes_b
338 where recipe_id = p_recipe_id OR
339 (recipe_no = p_recipe_no and recipe_version = p_recipe_version);
340
341 /*** local Variables ***/
342 l_api_name CONSTANT VARCHAR2(30) := 'get_formula_id';
343 l_api_version CONSTANT NUMBER := 1.0;
344
345 BEGIN
346 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
347 l_api_name, G_PKG_NAME) THEN
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350 IF FND_API.to_Boolean(p_init_msg_list) THEN
351 FND_MSG_PUB.initialize;
352 END IF;
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354
355 OPEN get_form;
356 FETCH get_form into x_formula_id;
357
358 IF get_form%NOTFOUND THEN
359 RAISE fnd_api.g_exc_error;
360 END IF; -- end if formula_id not found
361
362 CLOSE get_form;
363
364 -- standard call to get msge cnt, and if cnt is 1, get mesg info
365 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 X_return_code := SQLCODE;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
372
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 X_return_code := SQLCODE;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
377
378 WHEN OTHERS THEN
379
380 X_return_code := SQLCODE;
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
383
384 END get_formula_id;
385
386 /*******************************************************************************
387 * Procedure get_process_loss
388 *
389 * Procedure:- This returns the process loss for a particular recipe if a
390 * routing is attached to a given recipe.
391 *
392 *
393 * Author :Pawan Kumar
394 *
395 *********************************************************************************/
396
397 PROCEDURE get_process_loss
398 ( p_api_version IN NUMBER ,
399 p_init_msg_list IN VARCHAR2 ,
400 p_recipe_no IN VARCHAR2 ,
401 p_recipe_version IN NUMBER ,
402 p_recipe_id IN NUMBER ,
403 p_organization_id IN NUMBER ,
404 x_return_status OUT NOCOPY VARCHAR2 ,
405 x_msg_count OUT NOCOPY NUMBER ,
406 x_msg_data OUT NOCOPY VARCHAR2 ,
407 x_return_code OUT NOCOPY NUMBER ,
408 x_process_loss OUT NOCOPY NUMBER
409 ) IS
410
411 -- local cursor to fetch the process_loss from gmd_recipe_process_loss table
412
413 CURSOR get_proc IS
414 SELECT process_loss
415 FROM gmd_recipe_process_loss
416 WHERE recipe_id = p_recipe_id
417 AND organization_id = p_organization_id ;
418
419 -- local Variables
420 l_api_name CONSTANT VARCHAR2(30) := 'get_process_loss';
421 l_api_version CONSTANT NUMBER := 1.0;
422 l_routing_id NUMBER;
423 l_return_status VARCHAR2(30);
424 l_msg_count NUMBER ;
425 l_return_code NUMBER ;
426 l_msg_data VARCHAR2(2000) ;
427
428 BEGIN
429 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
430 l_api_name, G_PKG_NAME) THEN
431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432 END IF;
433 IF FND_API.to_Boolean(p_init_msg_list) THEN
434 FND_MSG_PUB.initialize;
435 END IF;
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 -- call the get_routing_id procedure to check the routing exists or not
439 get_routing_id
440 ( p_api_version => 1.0 ,
441 p_recipe_no => p_recipe_no ,
442 p_recipe_version => p_recipe_version ,
443 p_recipe_id => p_recipe_id ,
444 x_return_status => l_return_status ,
445 x_msg_count => l_msg_count ,
446 x_msg_data => l_msg_data ,
447 x_return_code => l_return_code ,
448 x_routing_id => l_routing_id
449 ) ;
450
451 -- check for process loss only if a routing is attached to the recipe
452 IF l_routing_id IS not null then
453 OPEN get_proc;
454 FETCH get_proc into x_process_loss;
455 ELSE
456 RAISE fnd_api.g_exc_error;
457 END IF;
458 /* IF get_proc%NOTFOUND THEN
459 RAISE fnd_api.g_exc_error;
460 END IF; -- end if recipe_id not found */
461
462 CLOSE get_proc;
463
464 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
465 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
466
467 EXCEPTION
468 WHEN FND_API.G_EXC_ERROR THEN
469 X_return_code := SQLCODE;
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
472
473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
474 X_return_code := SQLCODE;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
477
478 WHEN OTHERS THEN
479
480 X_return_code := SQLCODE;
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
483
484 END get_process_loss;
485
486 /*******************************************************************************
487 * Procedure get_rout_material
488 *
489 * Procedure:- This returns the material - step information based on the
490 * recipe_id passed to it.
491 *
492 *
493 * Author :Pawan Kumar
494 * --Rajesh Patangya DFF Enhancement 03Jan2008 Bug# 6195829
495 *
496 *********************************************************************************/
497
498 PROCEDURE get_rout_material
499
500 ( p_api_version IN NUMBER ,
501 p_init_msg_list IN VARCHAR2 ,
502 p_recipe_id IN NUMBER ,
503 x_return_status OUT NOCOPY VARCHAR2 ,
504 x_msg_count OUT NOCOPY NUMBER ,
505 x_msg_data OUT NOCOPY VARCHAR2 ,
506 x_return_code OUT NOCOPY NUMBER ,
507 x_recipe_rout_matl_tbl OUT NOCOPY recipe_rout_matl_tbl
508 ) IS
509
510
511 CURSOR get_matl IS
512 SELECT recipe_id, formulaline_id, routingstep_id, text_code,
513 creation_date, created_by,last_updated_by,
514 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
515 --Added the following columns to the select statement
516 minimum_transfer_qty, minimum_delay, maximum_delay,
517 last_update_date, last_update_login ,
518 --Rajesh Patangya DFF Enhancement 03Jan2008 Bug# 6195829
519 ATTRIBUTE_CATEGORY,
520 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
521 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
522 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
523 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,
524 ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30
525 FROM gmd_recipe_step_materials
526 WHERE recipe_id = p_recipe_id ;
527
528 /*** local Variables ***/
529 l_api_name CONSTANT VARCHAR2(30) := 'get_rout_material';
530 l_api_version CONSTANT NUMBER := 1.0;
531 i NUMBER := 0;
532
533 BEGIN
534 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
535 l_api_name, G_PKG_NAME) THEN
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538 IF FND_API.to_Boolean(p_init_msg_list) THEN
539 FND_MSG_PUB.initialize;
540 END IF;
541 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543
544 FOR get_rec IN get_matl LOOP
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546 i := i + 1;
547
548 x_recipe_rout_matl_tbl(i).recipe_id := get_rec.recipe_id;
549 x_recipe_rout_matl_tbl(i).formulaline_id := get_rec.formulaline_id ;
550 x_recipe_rout_matl_tbl(i).routingstep_id := get_rec.routingstep_id ;
551 x_recipe_rout_matl_tbl(i).text_code := get_rec.text_code ;
552 x_recipe_rout_matl_tbl(i).creation_date := get_rec.creation_date ;
553 x_recipe_rout_matl_tbl(i).created_by := get_rec.created_by ;
554 x_recipe_rout_matl_tbl(i).last_updated_by := get_rec.last_updated_by ;
555 x_recipe_rout_matl_tbl(i).last_update_date := get_rec.last_update_date ;
556 x_recipe_rout_matl_tbl(i).last_update_login := get_rec.last_update_login;
557
558 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
559 x_recipe_rout_matl_tbl(i).minimum_transfer_qty := get_rec.minimum_transfer_qty;
560 x_recipe_rout_matl_tbl(i).minimum_delay := get_rec.minimum_delay;
561 x_recipe_rout_matl_tbl(i).maximum_delay := get_rec.maximum_delay;
562
563 --Rajesh Patangya DFF Enhancement 03Jan2008 Bug# 6195829
564 x_recipe_rout_matl_tbl(i).ATTRIBUTE_CATEGORY := get_rec.ATTRIBUTE_CATEGORY ;
565 x_recipe_rout_matl_tbl(i).ATTRIBUTE1 := get_rec.ATTRIBUTE1;
566 x_recipe_rout_matl_tbl(i).ATTRIBUTE2 := get_rec.ATTRIBUTE2;
567 x_recipe_rout_matl_tbl(i).ATTRIBUTE3 := get_rec.ATTRIBUTE3;
568 x_recipe_rout_matl_tbl(i).ATTRIBUTE4 := get_rec.ATTRIBUTE4;
569 x_recipe_rout_matl_tbl(i).ATTRIBUTE5 := get_rec.ATTRIBUTE5;
570 x_recipe_rout_matl_tbl(i).ATTRIBUTE6 := get_rec.ATTRIBUTE6;
571 x_recipe_rout_matl_tbl(i).ATTRIBUTE7 := get_rec.ATTRIBUTE7;
572 x_recipe_rout_matl_tbl(i).ATTRIBUTE8 := get_rec.ATTRIBUTE8;
573 x_recipe_rout_matl_tbl(i).ATTRIBUTE9 := get_rec.ATTRIBUTE9;
574 x_recipe_rout_matl_tbl(i).ATTRIBUTE10 := get_rec.ATTRIBUTE10;
575 x_recipe_rout_matl_tbl(i).ATTRIBUTE11 := get_rec.ATTRIBUTE11;
576 x_recipe_rout_matl_tbl(i).ATTRIBUTE12 := get_rec.ATTRIBUTE12;
577 x_recipe_rout_matl_tbl(i).ATTRIBUTE13 := get_rec.ATTRIBUTE13;
578 x_recipe_rout_matl_tbl(i).ATTRIBUTE14 := get_rec.ATTRIBUTE14;
579 x_recipe_rout_matl_tbl(i).ATTRIBUTE15 := get_rec.ATTRIBUTE15;
580 x_recipe_rout_matl_tbl(i).ATTRIBUTE16 := get_rec.ATTRIBUTE16;
581 x_recipe_rout_matl_tbl(i).ATTRIBUTE17 := get_rec.ATTRIBUTE17;
582 x_recipe_rout_matl_tbl(i).ATTRIBUTE18 := get_rec.ATTRIBUTE18;
583 x_recipe_rout_matl_tbl(i).ATTRIBUTE19 := get_rec.ATTRIBUTE19;
584 x_recipe_rout_matl_tbl(i).ATTRIBUTE20 := get_rec.ATTRIBUTE20;
585 x_recipe_rout_matl_tbl(i).ATTRIBUTE21 := get_rec.ATTRIBUTE21;
586 x_recipe_rout_matl_tbl(i).ATTRIBUTE22 := get_rec.ATTRIBUTE22;
587 x_recipe_rout_matl_tbl(i).ATTRIBUTE23 := get_rec.ATTRIBUTE23;
588 x_recipe_rout_matl_tbl(i).ATTRIBUTE24 := get_rec.ATTRIBUTE24;
589 x_recipe_rout_matl_tbl(i).ATTRIBUTE25 := get_rec.ATTRIBUTE25;
590 x_recipe_rout_matl_tbl(i).ATTRIBUTE26 := get_rec.ATTRIBUTE26;
591 x_recipe_rout_matl_tbl(i).ATTRIBUTE27 := get_rec.ATTRIBUTE27;
592 x_recipe_rout_matl_tbl(i).ATTRIBUTE28 := get_rec.ATTRIBUTE28;
593 x_recipe_rout_matl_tbl(i).ATTRIBUTE29 := get_rec.ATTRIBUTE29;
594 x_recipe_rout_matl_tbl(i).ATTRIBUTE30 := get_rec.ATTRIBUTE30;
595
596 END LOOP;
597
598 IF i = 0 THEN
599 RAISE fnd_api.g_exc_error;
600 END IF; -- end if recipe_id not found
601
602 /* standard call to get msge cnt, and if cnt is 1, get mesg info*/
603 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
604
605 EXCEPTION
606 WHEN FND_API.G_EXC_ERROR THEN
607 X_return_code := SQLCODE;
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
610
611 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
612 X_return_code := SQLCODE;
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
614 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
615
616 WHEN OTHERS THEN
617 X_return_code := SQLCODE;
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
620
621 END get_rout_material;
622
623 /*******************************************************************************
624 * Procedure get_routing_step_details
625 *
626 * Procedure:- This returns the routing step information based on the
627 * routing_id passed to it.This information is for populating
628 * the data before the recipe_id is created.
629 *
630 *
631 * Author :Pawan Kumar
632 *
633 * History
634 * James Bernard 07-NOV-2002 BUG#2330056
635 * Code is commented so that text code of the routing step does not get
636 * fetched and copied to the newly created recipe.
637 *********************************************************************************/
638
639
640 PROCEDURE get_routing_step_details
641 ( p_api_version IN NUMBER ,
642 p_init_msg_list IN VARCHAR2 ,
643 p_routing_id IN NUMBER ,
644 x_return_status OUT NOCOPY VARCHAR2 ,
645 x_msg_count OUT NOCOPY NUMBER ,
646 x_msg_data OUT NOCOPY VARCHAR2 ,
647 x_return_code OUT NOCOPY NUMBER ,
648 x_routing_step_out OUT NOCOPY routing_step_tbl
649 ) IS
650
651 /*** local Variables ***/
652 l_api_name CONSTANT VARCHAR2(30) := 'get_step_details';
653 l_api_version CONSTANT NUMBER := 1.0;
654 i NUMBER := 0;
655
656
657 --BUG#2330056 James Bernard
658 --Removed "d.text_code" from the following Select Statement as it was not being used anywhere.
659 CURSOR get_routing_step IS
660 SELECT o.process_qty_uom ,d.routing_id,d.routingstep_id, d.routingstep_no, d.oprn_id, step_qty,
661 d.steprelease_type,d.minimum_transfer_qty, o.oprn_no, o.oprn_vers, o.oprn_desc, d.creation_date,
662 d.created_by,d.last_updated_by, d.last_update_date, d.last_update_login,
663 d.attribute_category,d.attribute1, d.attribute2, d.attribute3,
664 d.attribute4, d.attribute5, d.attribute6,
665 d.attribute7, d.attribute8, d.attribute9, d.attribute10,
666 d.attribute11, d.attribute12, d.attribute13, d.attribute14,
667 d.attribute15, d.attribute16, d.attribute17, d.attribute18,
668 d.attribute19, d.attribute20, d.attribute21, d.attribute22,
669 d.attribute23, d.attribute24, d.attribute25, d.attribute26,
670 d.attribute27, d.attribute28, d.attribute29, d.attribute30
671 FROM fm_rout_dtl d, gmd_operations_vl o
672 WHERE d.routing_id = p_routing_id
673 AND d.oprn_id = o.oprn_id ;
674 --END BUG#2330056
675
676 BEGIN
677 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
678 l_api_name, G_PKG_NAME) THEN
679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
680 END IF;
681 IF FND_API.to_Boolean(p_init_msg_list) THEN
682 FND_MSG_PUB.initialize;
683 END IF;
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685
686 For get_rec IN get_routing_step LOOP
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688 i := i + 1;
689
690 x_routing_step_out(i).routingstep_no := get_rec.routingstep_no ;
691 x_routing_step_out(i).routingstep_id := get_rec.routingstep_id ;
692 x_routing_step_out(i).oprn_id := get_rec.oprn_id ;
693 x_routing_step_out(i).oprn_no := get_rec.oprn_no ;
694 x_routing_step_out(i).oprn_vers := get_rec.oprn_vers ;
695 x_routing_step_out(i).oprn_desc := get_rec.oprn_desc ;
696 x_routing_step_out(i).process_qty_uom := get_rec.process_qty_uom ;
697 x_routing_step_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty;
698
699 x_routing_step_out(i).step_qty := get_rec.step_qty ;
700 x_routing_step_out(i).steprelease_type := get_rec.steprelease_type ;
701 --BEGIN BUG#2330056 James Bernard
702 --Text code should not be copied over to newly created Recipe, commenting
703 --following assignment as text_code is not getting fetched in the cursor now.
704 --x_routing_step_out(i).text_code := get_rec.text_code ;
705 --END BUG#2330056
706 x_routing_step_out(i).last_updated_by := get_rec.last_updated_by ;
707 x_routing_step_out(i).created_by := get_rec.created_by ;
708 x_routing_step_out(i).last_update_date := get_rec.last_update_date ;
709 x_routing_step_out(i).creation_date := get_rec.creation_date ;
710 x_routing_step_out(i).last_update_login := get_rec.last_update_login;
711 x_routing_step_out(i).attribute1 := get_rec.attribute1;
712 x_routing_step_out(i).attribute2 := get_rec.attribute2;
713 x_routing_step_out(i).attribute3 := get_rec.attribute3;
714 x_routing_step_out(i).attribute4 := get_rec.attribute4;
715 x_routing_step_out(i).attribute5 := get_rec.attribute5;
716 x_routing_step_out(i).attribute6 := get_rec.attribute6;
717 x_routing_step_out(i).attribute7 := get_rec.attribute7;
718 x_routing_step_out(i).attribute8 := get_rec.attribute8;
719 x_routing_step_out(i).attribute9 := get_rec.attribute9;
720 x_routing_step_out(i).attribute10 := get_rec.attribute10;
721 x_routing_step_out(i).attribute11 := get_rec.attribute11;
722 x_routing_step_out(i).attribute12 := get_rec.attribute12;
723 x_routing_step_out(i).attribute13 := get_rec.attribute13;
724 x_routing_step_out(i).attribute14 := get_rec.attribute14;
725 x_routing_step_out(i).attribute15 := get_rec.attribute15;
726 x_routing_step_out(i).attribute16 := get_rec.attribute16;
727 x_routing_step_out(i).attribute17 := get_rec.attribute17;
728 x_routing_step_out(i).attribute18 := get_rec.attribute18;
729 x_routing_step_out(i).attribute19 := get_rec.attribute19;
730 x_routing_step_out(i).attribute20 := get_rec.attribute20;
731 x_routing_step_out(i).attribute21 := get_rec.attribute21;
732 x_routing_step_out(i).attribute22 := get_rec.attribute22;
733 x_routing_step_out(i).attribute23 := get_rec.attribute23;
734 x_routing_step_out(i).attribute24 := get_rec.attribute24;
735 x_routing_step_out(i).attribute25 := get_rec.attribute25;
736 x_routing_step_out(i).attribute26 := get_rec.attribute26;
737 x_routing_step_out(i).attribute27 := get_rec.attribute27;
738 x_routing_step_out(i).attribute28 := get_rec.attribute28;
739 x_routing_step_out(i).attribute29 := get_rec.attribute29;
740 x_routing_step_out(i).attribute30 := get_rec.attribute30;
741 END LOOP;
742
743 IF i = 0 THEN
744 RAISE fnd_api.g_exc_error;
745 END IF; -- end if recipe_id not found
746
747 -- standard call to get msge cnt, and if cnt is 1, get mesg info
748 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
749
750 EXCEPTION
751 WHEN FND_API.G_EXC_ERROR THEN
752 X_return_code := SQLCODE;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
755
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 X_return_code := SQLCODE;
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
760
761 WHEN OTHERS THEN
762 X_return_code := SQLCODE;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
765
766 END get_routing_step_details;
767
768 /*******************************************************************************
769 * Procedure get_recipe_step_details
770 *
771 * Procedure:- This returns the recipe step information based on the
772 * recipe_id passed to it.This information is for populating
773 * the data after the recipe_id is created.
774 *
775 *
776 * Author :Pawan Kumar
777 *
778 *********************************************************************************/
779
780
781 PROCEDURE get_recipe_step_details
782 ( p_api_version IN NUMBER ,
783 p_init_msg_list IN VARCHAR2 ,
784 p_recipe_id IN NUMBER ,
785 p_organization_id IN NUMBER DEFAULT NULL ,
786 x_return_status OUT NOCOPY VARCHAR2 ,
787 x_msg_count OUT NOCOPY NUMBER ,
788 x_msg_data OUT NOCOPY VARCHAR2 ,
789 x_return_code OUT NOCOPY NUMBER ,
790 x_recipe_step_out OUT NOCOPY recipe_step_tbl ,
791 p_val_scale_factor IN NUMBER ,
792 p_process_loss IN NUMBER,
793 p_routing_id IN NUMBER DEFAULT NULL
794 )
795 IS
796
797 /*** local Variables ***/
798 l_api_name CONSTANT VARCHAR2(30) := 'get_step_details';
799 l_api_version CONSTANT NUMBER := 1.0;
800
801 l_routing_id NUMBER;
802 l_return_status VARCHAR2(30);
803 l_msg_count NUMBER ;
804 l_return_code NUMBER ;
805 i NUMBER(10) DEFAULT 0;
806 l_msg_data VARCHAR2(2000) ;
807 l_charge_tbl gmd_common_val.charge_tbl ;
808 l_step_tbl gmd_auto_step_calc.step_rec_tbl;
809 l_calculate_step_qty NUMBER(5);
810 l_rout_scale_factor NUMBER;
811 l_orgn_code VARCHAR2(4);
812
813 CURSOR Cur_get_recipe IS
814 SELECT routing_id, calculate_step_quantity
815 FROM gmd_recipes_b
816 WHERE recipe_id = p_recipe_id;
817
818 CURSOR get_recipe_step (l_auto_calc NUMBER) IS
819 SELECT dtl.routingstep_no, oprn.oprn_id, oprn.oprn_no, oprn.oprn_desc, oprn.oprn_vers,
820 stp.step_qty, oprn.process_qty_uom, stp.text_code, stp.routingstep_id, dtl.steprelease_type,
821 dtl.minimum_transfer_qty, stp.recipe_id, stp.creation_date, stp.created_by,stp.last_updated_by,
822 stp.last_update_date, stp.last_update_login, stp.attribute_category,
823 stp.attribute1, stp.attribute2, stp.attribute3, stp.attribute4,
824 stp.attribute5, stp.attribute6, stp.attribute7, stp.attribute8,
825 stp.attribute9, stp.attribute10, stp.attribute11, stp.attribute12,
826 stp.attribute13, stp.attribute14, stp.attribute15, stp.attribute16,
827 stp.attribute17, stp.attribute18, stp.attribute19, stp.attribute20,
828 stp.attribute21, stp.attribute22, stp.attribute23, stp.attribute24,
829 stp.attribute25, stp.attribute26, stp.attribute27, stp.attribute28,
830 stp.attribute29, stp.attribute30
831 FROM gmd_recipe_routing_steps stp, fm_rout_dtl dtl, gmd_operations_vl oprn
832 WHERE l_auto_calc = 0
833 AND stp.recipe_id = p_recipe_id
834 AND dtl.routingstep_id = stp.routingstep_id
835 AND dtl.oprn_id = oprn.oprn_id
836 UNION
837 SELECT dtl.routingstep_no, oprn.oprn_id, oprn.oprn_no, oprn.oprn_desc, oprn.oprn_vers,
838 dtl.step_qty, oprn.process_qty_uom,
839 -- dtl.text_code,
840 nvl(grrs.text_code,dtl.text_code),
841 dtl.routingstep_id,dtl.steprelease_type,
842 dtl.minimum_transfer_qty, 0 RECIPE_ID,
843 /*dtl.creation_date, dtl.created_by,dtl.last_updated_by,
844 dtl.last_update_date, dtl.last_update_login, dtl.attribute_category,
845 dtl.attribute1, dtl.attribute2, dtl.attribute3, dtl.attribute4,
846 dtl.attribute5, dtl.attribute6, dtl.attribute7, dtl.attribute8,
847 dtl.attribute9, dtl.attribute10, dtl.attribute11, dtl.attribute12,
848 dtl.attribute13, dtl.attribute14, dtl.attribute15, dtl.attribute16,
849 dtl.attribute17, dtl.attribute18, dtl.attribute19, dtl.attribute20,
850 dtl.attribute21, dtl.attribute22, dtl.attribute23, dtl.attribute24,
851 dtl.attribute25, dtl.attribute26, dtl.attribute27, dtl.attribute28,
852 dtl.attribute29, dtl.attribute30*/
853 nvl(grrs.creation_date,dtl.creation_date),
854 nvl(grrs.created_by,dtl.created_by),
855 nvl(grrs.last_updated_by,dtl.last_updated_by),
856 nvl(grrs.last_update_date,dtl.last_update_date),
857 nvl(grrs.last_update_login,dtl.last_update_login),
858 nvl(grrs.attribute_category,dtl.attribute_category),
859 nvl(grrs.attribute1,dtl.attribute1),
860 nvl(grrs.attribute2,dtl.attribute2),
861 nvl(grrs.attribute3,dtl.attribute3),
862 nvl(grrs.attribute4,dtl.attribute4),
863 nvl(grrs.attribute5,dtl.attribute5),
864 nvl(grrs.attribute6,dtl.attribute6),
865 nvl(grrs.attribute7,dtl.attribute7),
866 nvl(grrs.attribute8,dtl.attribute8),
867 nvl(grrs.attribute9,dtl.attribute9),
868 nvl(grrs.attribute10,dtl.attribute10),
869 nvl(grrs.attribute11,dtl.attribute11),
870 nvl(grrs.attribute12,dtl.attribute12),
871 nvl(grrs.attribute13,dtl.attribute13),
872 nvl(grrs.attribute14,dtl.attribute14),
873 nvl(grrs.attribute15,dtl.attribute15),
874 nvl(grrs.attribute16,dtl.attribute16),
875 nvl(grrs.attribute17,dtl.attribute17),
876 nvl(grrs.attribute18,dtl.attribute18),
877 nvl(grrs.attribute19,dtl.attribute19),
878 nvl(grrs.attribute20,dtl.attribute20),
879 nvl(grrs.attribute21,dtl.attribute21),
880 nvl(grrs.attribute22,dtl.attribute22),
881 nvl(grrs.attribute23,dtl.attribute23),
882 nvl(grrs.attribute24,dtl.attribute24),
883 nvl(grrs.attribute25,dtl.attribute25),
884 nvl(grrs.attribute26,dtl.attribute26),
885 nvl(grrs.attribute27,dtl.attribute27),
886 nvl(grrs.attribute28,dtl.attribute28),
887 nvl(grrs.attribute29,dtl.attribute29),
888 nvl(grrs.attribute30,dtl.attribute30)
889 FROM fm_rout_dtl dtl, gmd_recipes_b recp , gmd_operations_vl oprn,
890 gmd_recipe_routing_steps grrs /* Added in Bug No.8428182 */
891 WHERE recp.recipe_id = p_recipe_id
892 AND grrs.recipe_id(+) = p_recipe_id /* Added in Bug No.8428182 */
893 AND grrs.routingstep_id(+) = dtl.routingstep_id /* Added in Bug No.8428182 */
894 AND dtl.routing_id = l_routing_id
895 AND oprn.oprn_id = dtl.oprn_id
896 AND dtl.routingstep_id NOT IN (SELECT routingstep_id
897 FROM gmd_recipe_routing_steps
898 WHERE recipe_id = p_recipe_id
899 AND l_auto_calc = 0)
900 ORDER BY routingstep_no;
901
902 /*Bug# 3612365 - Thomas Daniel */
903 /*Added the following cursor to pass back the resource causing the charge on the step */
904
905 CURSOR Cur_get_charge_resource (V_routingstep_id NUMBER, V_max_capacity NUMBER) IS
906 SELECT resources
907 FROM gmd_recipe_orgn_resources
908 WHERE routingstep_id = V_routingstep_id
909 AND recipe_id = p_recipe_id
910 AND organization_id = P_organization_id
911 AND max_capacity = V_max_capacity
912 UNION
913 SELECT r.resources
914 FROM fm_rout_dtl d, gmd_operation_resources r,
915 gmd_operation_activities a, cr_rsrc_dtl d
916 WHERE d.routingstep_id = V_routingstep_id
917 AND d.oprn_id = a.oprn_id
918 AND a.oprn_line_id = r.oprn_line_id
919 AND r.resources = d.resources
920 AND organization_id = P_organization_id
921 AND d.max_capacity = V_max_capacity
922 AND capacity_constraint = 1
923 UNION
924 SELECT r.resources
925 FROM fm_rout_dtl d, gmd_operation_resources r,
926 gmd_operation_activities a, cr_rsrc_mst m
927 WHERE d.routingstep_id = V_routingstep_id
928 AND d.oprn_id = a.oprn_id
929 AND a.oprn_line_id = r.oprn_line_id
930 AND r.resources = m.resources
931 AND m.max_capacity = V_max_capacity
932 AND capacity_constraint = 1;
933
934 CURSOR get_orgn_code IS
935 SELECT organization_code
936 FROM org_access_view
937 WHERE organization_id = p_organization_id;
938
939 BEGIN
940 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
941 l_api_name, G_PKG_NAME) THEN
942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
943 END IF;
944
945 IF FND_API.to_Boolean(p_init_msg_list) THEN
946 FND_MSG_PUB.initialize;
947 END IF;
948
949 x_return_status := FND_API.G_RET_STS_SUCCESS;
950
951 -- call the get_routing_id procedure to check the routing exists or not
952 OPEN Cur_get_recipe;
953 FETCH Cur_get_recipe INTO l_routing_id, l_calculate_step_qty;
954 CLOSE Cur_get_recipe;
955
956 IF (p_routing_id IS NOT NULL) THEN
957 l_routing_id := p_routing_id;
958 END IF;
959
960 IF l_routing_id IS NOT NULL THEN
961
962 IF l_calculate_step_qty = 1 THEN
963 gmd_auto_step_calc.calc_step_qty(p_parent_id => P_recipe_id,
964 p_step_tbl => l_step_tbl,
965 p_msg_count => l_msg_count,
966 p_msg_stack => l_msg_data,
967 p_return_status => l_return_status,
968 p_ignore_mass_conv => TRUE,
969 p_ignore_vol_conv => TRUE,
970 p_scale_factor => NVL(P_val_scale_factor,1),
971 p_process_loss => NVL(p_process_loss, 0),
972 p_organization_id => p_organization_id);
973 IF l_return_status <> FND_API.g_ret_sts_success THEN
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976 ELSE
977 l_rout_scale_factor := GMD_COMMON_VAL.Get_Routing_Scale_Factor
978 (vRecipe_Id => p_recipe_id
979 ,x_return_status => l_return_status);
980 END IF; /* If l_calculate_step_qty = 1 */
981
982 FOR get_rec IN get_recipe_step (l_calculate_step_qty) LOOP
983 i := i + 1;
984 x_recipe_step_out(i).recipe_id := get_rec.recipe_id ;
985 x_recipe_step_out(i).routingstep_no := get_rec.routingstep_no ;
986 x_recipe_step_out(i).routingstep_id := get_rec.routingstep_id ;
987 x_recipe_step_out(i).oprn_id := get_rec.oprn_id ;
988 x_recipe_step_out(i).oprn_no := get_rec.oprn_no ;
989 x_recipe_step_out(i).oprn_vers := get_rec.oprn_vers ;
990 x_recipe_step_out(i).oprn_desc := get_rec.oprn_desc ;
991 x_recipe_step_out(i).process_qty_uom := get_rec.process_qty_uom ;
992 x_recipe_step_out(i).steprelease_type := get_rec.steprelease_type ;
993 x_recipe_step_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty ;
994
995 IF l_calculate_step_qty = 1 THEN
996 x_recipe_step_out(i).step_qty := l_step_tbl(i).step_qty;
997 ELSE
998 IF get_rec.recipe_id = 0 THEN
999 /* This implies that the step qty in get rec is from the routing */
1000 x_recipe_step_out(i).step_qty := get_rec.step_qty * NVL(l_rout_scale_factor, 1)
1001 * NVL(p_val_scale_factor, 1);
1002 ELSE
1003 /* This implies that the step qty in get rec is from the recipe */
1004 x_recipe_step_out(i).step_qty := get_rec.step_qty * NVL(p_val_scale_factor, 1);
1005 END IF;
1006
1007 l_step_tbl(i).step_id := x_recipe_step_out(i).routingstep_id;
1008 l_step_tbl(i).step_no := x_recipe_step_out(i).routingstep_no;
1009 l_step_tbl(i).step_qty := x_recipe_step_out(i).step_qty;
1010 l_step_tbl(i).step_qty_uom := x_recipe_step_out(i).process_qty_uom;
1011 END IF; /* If l_calculate_step_qty = 1 */
1012
1013 x_recipe_step_out(i).text_code := get_rec.text_code ;
1014 x_recipe_step_out(i).last_updated_by := get_rec.last_updated_by ;
1015 x_recipe_step_out(i).created_by := get_rec.created_by ;
1016 x_recipe_step_out(i).last_update_date := get_rec.last_update_date ;
1017 x_recipe_step_out(i).creation_date := get_rec.creation_date ;
1018 x_recipe_step_out(i).last_update_login := get_rec.last_update_login ;
1019 x_recipe_step_out(i).attribute1 := get_rec.attribute1 ;
1020 x_recipe_step_out(i).attribute2 := get_rec.attribute2 ;
1021 x_recipe_step_out(i).attribute3 := get_rec.attribute3 ;
1022 x_recipe_step_out(i).attribute4 := get_rec.attribute4 ;
1023 x_recipe_step_out(i).attribute5 := get_rec.attribute5 ;
1024 x_recipe_step_out(i).attribute6 := get_rec.attribute6 ;
1025 x_recipe_step_out(i).attribute7 := get_rec.attribute7 ;
1026 x_recipe_step_out(i).attribute8 := get_rec.attribute8 ;
1027 x_recipe_step_out(i).attribute9 := get_rec.attribute9 ;
1028 x_recipe_step_out(i).attribute10 := get_rec.attribute10 ;
1029 x_recipe_step_out(i).attribute11 := get_rec.attribute11 ;
1030 x_recipe_step_out(i).attribute12 := get_rec.attribute12 ;
1031 x_recipe_step_out(i).attribute13 := get_rec.attribute13 ;
1032 x_recipe_step_out(i).attribute14 := get_rec.attribute14 ;
1033 x_recipe_step_out(i).attribute15 := get_rec.attribute15 ;
1034 x_recipe_step_out(i).attribute16 := get_rec.attribute16 ;
1035 x_recipe_step_out(i).attribute17 := get_rec.attribute17 ;
1036 x_recipe_step_out(i).attribute18 := get_rec.attribute18 ;
1037 x_recipe_step_out(i).attribute19 := get_rec.attribute19 ;
1038 x_recipe_step_out(i).attribute20 := get_rec.attribute20 ;
1039 x_recipe_step_out(i).attribute21 := get_rec.attribute21 ;
1040 x_recipe_step_out(i).attribute22 := get_rec.attribute22 ;
1041 x_recipe_step_out(i).attribute23 := get_rec.attribute23 ;
1042 x_recipe_step_out(i).attribute24 := get_rec.attribute24 ;
1043 x_recipe_step_out(i).attribute25 := get_rec.attribute25 ;
1044 x_recipe_step_out(i).attribute26 := get_rec.attribute26 ;
1045 x_recipe_step_out(i).attribute27 := get_rec.attribute27 ;
1046 x_recipe_step_out(i).attribute28 := get_rec.attribute28 ;
1047 x_recipe_step_out(i).attribute29 := get_rec.attribute29 ;
1048 x_recipe_step_out(i).attribute30 := get_rec.attribute30 ;
1049 x_recipe_step_out(i).attribute_category := get_rec.attribute_category ; /* Added in Bug No.8428182 */
1050
1051 END LOOP;
1052
1053 IF p_organization_id IS NOT NULL THEN
1054 OPEN get_orgn_code;
1055 FETCH get_orgn_code INTO l_orgn_code;
1056 CLOSE get_orgn_code;
1057 END IF;
1058
1059 -- call the charges procedure to get the max_capacity for the step.
1060 gmd_common_val.Calculate_Step_Charges (
1061 P_recipe_id => p_recipe_id ,
1062 P_tolerance => 0 ,
1063 P_orgn_id => p_organization_id ,
1064 P_step_tbl => l_step_tbl ,
1065 x_charge_tbl => l_charge_tbl ,
1066 x_return_status => l_return_status
1067 ) ;
1068
1069 FOR j IN 1..x_recipe_step_out.COUNT LOOP
1070 FOR k IN 1..l_charge_tbl.COUNT LOOP
1071 IF x_recipe_step_out(j).routingstep_id = l_charge_tbl(k).routingstep_id THEN
1072 x_recipe_step_out(j).max_capacity := l_charge_tbl(k).max_capacity;
1073 x_recipe_step_out(j).capacity_uom := l_charge_tbl(k).capacity_uom;
1074 x_recipe_step_out(j).charge := l_charge_tbl(k).charge;
1075
1076 /*Bug# 3612365 - Thomas Daniel */
1077 /*Added the following condition to populate the resource causing the charge */
1078 IF l_charge_tbl(k).max_capacity IS NOT NULL THEN
1079 -- Bug#5258672 use the capacity value in resource UOM
1080 OPEN Cur_get_charge_resource(l_charge_tbl(k).routingstep_id, l_charge_tbl(k).max_capacity_in_res_UOM);
1081 FETCH Cur_get_charge_resource INTO X_recipe_step_out(j).resources;
1082 CLOSE Cur_get_charge_resource;
1083 END IF;
1084 EXIT;
1085 END IF;
1086 END LOOP; /* FOR k IN 1..l_charge_tbl.COUNT */
1087 END LOOP; /* FOR j IN 1..x_recipe_step_out.COUNT */
1088
1089 END IF; /* If routing id is not null */
1090
1091 /* standard call to get msge cnt, and if cnt is 1, get mesg info*/
1092 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1093
1094 EXCEPTION
1095 WHEN FND_API.G_EXC_ERROR THEN
1096 X_return_code := SQLCODE;
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1099
1100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1101 X_return_code := SQLCODE;
1102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1103 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1104
1105 WHEN OTHERS THEN
1106 X_return_code := SQLCODE;
1107 x_return_status := FND_API.G_RET_STS_ERROR;
1108 FND_MSG_PUB.add_exc_msg ('GMD_RECIPE_FETCH_PUB', 'GET_RECIPE_STEP_DETAILS');
1109 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1110
1111 END get_recipe_step_details;
1112
1113 /*******************************************************************************
1114 * Procedure get_step_depd_details
1115 *
1116 * Procedure:- This returns the step dependency for information based on the
1117 * recipe_id passed to it.
1118 *
1119 *
1120 * Author :Pawan Kumar
1121 *
1122 *********************************************************************************/
1123
1124 PROCEDURE get_step_depd_details
1125
1126 ( p_api_version IN NUMBER ,
1127 p_init_msg_list IN VARCHAR2 ,
1128 p_recipe_id IN NUMBER ,
1129 x_return_status OUT NOCOPY VARCHAR2 ,
1130 x_msg_count OUT NOCOPY NUMBER ,
1131 x_msg_data OUT NOCOPY VARCHAR2 ,
1132 x_return_code OUT NOCOPY NUMBER ,
1133 x_routing_depd_tbl OUT NOCOPY routing_depd_tbl
1134 ) IS
1135
1136
1137 CURSOR get_depd IS
1138 SELECT routingstep_no,dep_routingstep_no, routing_id, dep_type, rework_code,
1139 standard_delay, minimum_delay, max_delay, transfer_qty, RoutingStep_No_uom,
1140 transfer_pct, text_code, creation_date, created_by,last_updated_by,
1141 last_update_date, last_update_login,chargeable_ind
1142 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
1143 --Added chargable_ind column to the select statement
1144 FROM fm_rout_dep
1145 WHERE routing_id = (SELECT routing_id
1146 FROM gmd_recipes_b
1147 WHERE recipe_id = p_recipe_id) ;
1148
1149
1150 depd_rec fm_rout_dep%rowtype;
1151
1152 /*** local Variables ***/
1153 l_api_name CONSTANT VARCHAR2(30) := 'get_step_depd_details';
1154 l_api_version CONSTANT NUMBER := 1.0;
1155 i NUMBER := 0;
1156 l_routing_id NUMBER;
1157 l_return_status VARCHAR2(30);
1158 l_msg_count NUMBER;
1159 l_return_code NUMBER ;
1160 l_msg_data VARCHAR2(2000) ;
1161
1162 BEGIN
1163 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1164 l_api_name, G_PKG_NAME) THEN
1165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 END IF;
1167 IF FND_API.to_Boolean(p_init_msg_list) THEN
1168 FND_MSG_PUB.initialize;
1169 END IF;
1170 x_return_status := FND_API.G_RET_STS_SUCCESS;
1171 /*Check for circular step dependency */
1172 -- call the get_routing_id procedure to check the routing exists or not
1173
1174 get_routing_id (
1175 p_api_version => 1.0 ,
1176 p_recipe_no => NULL ,
1177 p_recipe_version => NULL ,
1178 p_recipe_id => p_recipe_id ,
1179 x_return_status => l_return_status ,
1180 x_msg_count => l_msg_count ,
1181 x_msg_data => l_msg_data ,
1182 x_return_code => l_return_code ,
1183 x_routing_id => l_routing_id);
1184
1185 IF l_routing_id IS NOT NULL THEN
1186 IF gmdrtval_pub.circular_dependencies_exist(l_routing_id) then
1187 x_return_status := 'U' ;
1188
1189 ELSE
1190
1191 FOR get_rec IN get_depd LOOP
1192 i := i + 1;
1193 x_return_status := FND_API.G_RET_STS_SUCCESS;
1194 x_routing_depd_tbl(i).dep_routingstep_no := get_rec.dep_routingstep_no ;
1195 x_routing_depd_tbl(i).routingstep_no := get_rec.routingstep_no ;
1196 x_routing_depd_tbl(i).routing_id := get_rec.routing_id;
1197 x_routing_depd_tbl(i).dep_type := get_rec.dep_type;
1198 x_routing_depd_tbl(i).rework_code := get_rec.rework_code ;
1199 x_routing_depd_tbl(i).standard_delay := get_rec.standard_delay ;
1200 x_routing_depd_tbl(i).minimum_delay := get_rec.minimum_delay ;
1201 x_routing_depd_tbl(i).max_delay := get_rec.max_delay ;
1202 x_routing_depd_tbl(i).transfer_qty := get_rec.transfer_qty ;
1203 x_routing_depd_tbl(i).RoutingStep_No_uom := get_rec.RoutingStep_No_uom;
1204 x_routing_depd_tbl(i).transfer_pct := get_rec.transfer_pct ;
1205 x_routing_depd_tbl(i).text_code := get_rec.text_code ;
1206 x_routing_depd_tbl(i).last_updated_by := get_rec.last_updated_by ;
1207 x_routing_depd_tbl(i).created_by := get_rec.created_by ;
1208 x_routing_depd_tbl(i).last_update_date := get_rec.last_update_date ;
1209 x_routing_depd_tbl(i).creation_date := get_rec.creation_date ;
1210 x_routing_depd_tbl(i).last_update_login := get_rec.last_update_login;
1211
1212 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
1213 x_routing_depd_tbl(i).chargeable_ind := get_rec.chargeable_ind;
1214 END LOOP;
1215
1216 IF i = 0 THEN
1217 RAISE fnd_api.g_exc_error;
1218 END IF;
1219 END IF;
1220 END IF;
1221
1222 /* standard call to get msge cnt, and if cnt is 1, get mesg info*/
1223 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1224
1225 EXCEPTION
1226 WHEN FND_API.G_EXC_ERROR THEN
1227 X_return_code := SQLCODE;
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1230
1231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1232 X_return_code := SQLCODE;
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1235
1236 WHEN OTHERS THEN
1237 X_return_code := SQLCODE;
1238 x_return_status := FND_API.G_RET_STS_ERROR;
1239 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1240
1241 END get_step_depd_details;
1242
1243 /*******************************************************************************
1244 * Procedure get_oprn_act_detl
1245 *
1246 * Procedure:- This returns the step, operation and activities details for a given recipe
1247 * based on the recipe_id passed to it.
1248 *
1249 *
1250 * Author :Pawan Kumar
1251 * History
1252 * Rameshwar 09-DEC-2002 BUG#2686887
1253 * Modified the order by clause of the cursor get_recp_act.
1254 * S.Dulyk 11-MAR-2003 Bug 2845110 MTW enhancement - added material_ind
1255 *********************************************************************************/
1256
1257
1258 PROCEDURE get_oprn_act_detl
1259 ( p_api_version IN NUMBER ,
1260 p_init_msg_list IN VARCHAR2 ,
1261 p_recipe_id IN NUMBER ,
1262 p_organization_id IN NUMBER ,
1263 x_return_status OUT NOCOPY VARCHAR2 ,
1264 x_msg_count OUT NOCOPY NUMBER ,
1265 x_msg_data OUT NOCOPY VARCHAR2 ,
1266 x_return_code OUT NOCOPY NUMBER ,
1267 x_oprn_act_out OUT NOCOPY oprn_act_tbl
1268 ) IS
1269
1270 /* local Variables */
1271 l_api_name VARCHAR2(30) := 'get_oprn_act_detl';
1272 l_api_version NUMBER := 1.0;
1273 i NUMBER := 0;
1274
1275 --BEGIN BUG #2686887 Rameshwar
1276 --Modified the order by clause from 2,9 to 1,9.
1277 CURSOR get_recp_act IS
1278
1279 SELECT d.routingstep_no routing_step_no,d.routingstep_id, o.oprn_no, o.oprn_desc, o.oprn_vers, o.oprn_id, o.minimum_transfer_qty,
1280 a.activity, fm.activity_desc, ra.oprn_line_id oprnline_id, ra.activity_factor, a.offset_interval,
1281 a.break_ind, a.max_break,a.material_ind, a.sequence_dependent_ind, ra.recipe_id,
1282 ra.text_code,ra.creation_date, ra.created_by,ra.last_updated_by,
1283 ra.last_update_date, ra.last_update_login, ra.attribute_category,
1284 ra.attribute1, ra.attribute2, ra.attribute3, ra.attribute4,
1285 ra.attribute5, ra.attribute6, ra.attribute7, ra.attribute8,
1286 ra.attribute9, ra.attribute10, ra.attribute11, ra.attribute12,
1287 ra.attribute13, ra.attribute14, ra.attribute15, ra.attribute16,
1288 ra.attribute17, ra.attribute18, ra.attribute19, ra.attribute20,
1289 ra.attribute21, ra.attribute22, ra.attribute23, ra.attribute24,
1290 ra.attribute25, ra.attribute26, ra.attribute27, ra.attribute28,
1291 ra.attribute29, ra.attribute30, 1 recipe_override
1292 FROM gmd_recipe_orgn_activities ra, fm_rout_dtl d,
1293 gmd_operations_vl o, gmd_operation_activities a , fm_actv_mst fm
1294 WHERE ra.recipe_id = p_recipe_id
1295 AND d.routingstep_id = ra.routingstep_id
1296 AND d.oprn_id = o.oprn_id
1297 AND a.activity = fm.activity
1298 AND ra.oprn_line_id = a.oprn_line_id
1299 AND (p_organization_id IS NULL OR ra.organization_id = p_organization_id)
1300
1301 UNION
1302 SELECT d.routingstep_no routing_step_no,d.routingstep_id, o.oprn_no, o.oprn_desc, o.oprn_vers,o.oprn_id,o.minimum_transfer_qty,
1303 a.activity,fm.activity_desc, a.oprn_line_id oprnline_id, a.activity_factor,a.offset_interval,
1304 a.break_ind, a.max_break, a.material_ind,a.sequence_dependent_ind, r.RECIPE_ID,
1305 a.text_code, a.creation_date, a.created_by,a.last_updated_by,
1306 a.last_update_date, a.last_update_login, a.attribute_category,
1307 a.attribute1, a.attribute2, a.attribute3, a.attribute4,
1308 a.attribute5, a.attribute6, a.attribute7, a.attribute8,
1309 a.attribute9, a.attribute10, a.attribute11, a.attribute12,
1310 a.attribute13, a.attribute14, a.attribute15, a.attribute16,
1311 a.attribute17, a.attribute18, a.attribute19, a.attribute20,
1312 a.attribute21, a.attribute22, a.attribute23, a.attribute24,
1313 a.attribute25, a.attribute26, a.attribute27, a.attribute28,
1314 a.attribute29, a.attribute30, 0 recipe_override
1315 FROM fm_rout_dtl d, gmd_recipes_b r , gmd_operations_vl o, gmd_operation_activities a, fm_actv_mst fm
1316 WHERE r.recipe_id = p_recipe_id
1317 AND d.routing_id = r.routing_id
1318 AND o.oprn_id = d.oprn_id
1319 AND a.oprn_id = o.oprn_id
1320 AND a.activity = fm.activity
1321 AND a.oprn_line_id NOT IN (SELECT oprn_line_id
1322 FROM gmd_recipe_orgn_activities
1323 WHERE recipe_id = p_recipe_id
1324 AND (p_organization_id IS NULL or organization_id = p_organization_id))
1325 --Added the order by for the bug 12613037.
1326 ORDER BY routing_step_no,offset_interval, activity, oprnline_id;
1327 -- END BUG#2686887
1328
1329 BEGIN
1330
1331 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1332 l_api_name, G_PKG_NAME) THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335 IF FND_API.to_Boolean(p_init_msg_list) THEN
1336 FND_MSG_PUB.initialize;
1337 END IF;
1338
1339 FOR get_rec IN get_recp_act LOOP
1340 x_return_status := FND_API.G_RET_STS_SUCCESS;
1341 i := i + 1;
1342 x_oprn_act_out(i).routingstep_no := get_rec.routing_step_no ;
1343 x_oprn_act_out(i).routingstep_id := get_rec.routingstep_id ;
1344 x_oprn_act_out(i).oprn_no := get_rec.oprn_no ;
1345 x_oprn_act_out(i).oprn_desc := get_rec.oprn_desc ;
1346 x_oprn_act_out(i).oprn_vers := get_rec.oprn_vers ;
1347 x_oprn_act_out(i).oprn_id := get_rec.oprn_id;
1348 x_oprn_act_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty;
1349 x_oprn_act_out(i).activity := get_rec.activity ;
1350 x_oprn_act_out(i).activity_desc := get_rec.activity_desc ;
1351 x_oprn_act_out(i).oprn_line_id := get_rec.oprnline_id ;
1352 x_oprn_act_out(i).activity_factor := get_rec.activity_factor;
1353 x_oprn_act_out(i).sequence_dependent_ind := get_rec.sequence_dependent_ind;
1354 x_oprn_act_out(i).recipe_override := get_rec.recipe_override;
1355 x_oprn_act_out(i).offset_interval := get_rec.offset_interval;
1356 x_oprn_act_out(i).break_ind := get_rec.break_ind;
1357 x_oprn_act_out(i).max_break := get_rec.max_break;
1358 x_oprn_act_out(i).material_ind := get_rec.material_ind;
1359 x_oprn_act_out(i).text_code := get_rec.text_code ;
1360 x_oprn_act_out(i).creation_date := get_rec.creation_date ;
1361 x_oprn_act_out(i).created_by := get_rec.created_by ;
1362 x_oprn_act_out(i).last_updated_by := get_rec.last_updated_by ;
1363 x_oprn_act_out(i).last_update_date := get_rec.last_update_date ;
1364 x_oprn_act_out(i).last_update_login := get_rec.last_update_login;
1365 x_oprn_act_out(i).attribute_category := get_rec.attribute_category;
1366 x_oprn_act_out(i).attribute1 := get_rec.attribute1;
1367 x_oprn_act_out(i).attribute2 := get_rec.attribute2;
1368 x_oprn_act_out(i).attribute3 := get_rec.attribute3;
1369 x_oprn_act_out(i).attribute4 := get_rec.attribute4;
1370 x_oprn_act_out(i).attribute5 := get_rec.attribute5;
1371 x_oprn_act_out(i).attribute6 := get_rec.attribute6;
1372 x_oprn_act_out(i).attribute7 := get_rec.attribute7;
1373 x_oprn_act_out(i).attribute8 := get_rec.attribute8;
1374 x_oprn_act_out(i).attribute9 := get_rec.attribute9;
1375 x_oprn_act_out(i).attribute10 := get_rec.attribute10;
1376 x_oprn_act_out(i).attribute11 := get_rec.attribute11;
1377 x_oprn_act_out(i).attribute12 := get_rec.attribute12;
1378 x_oprn_act_out(i).attribute13 := get_rec.attribute13;
1379 x_oprn_act_out(i).attribute14 := get_rec.attribute14;
1380 x_oprn_act_out(i).attribute15 := get_rec.attribute15;
1381 x_oprn_act_out(i).attribute16 := get_rec.attribute16;
1382 x_oprn_act_out(i).attribute17 := get_rec.attribute17;
1383 x_oprn_act_out(i).attribute18 := get_rec.attribute18;
1384 x_oprn_act_out(i).attribute19 := get_rec.attribute19;
1385 x_oprn_act_out(i).attribute20 := get_rec.attribute20;
1386 x_oprn_act_out(i).attribute21 := get_rec.attribute21;
1387 x_oprn_act_out(i).attribute22 := get_rec.attribute22;
1388 x_oprn_act_out(i).attribute23 := get_rec.attribute23;
1389 x_oprn_act_out(i).attribute24 := get_rec.attribute24;
1390 x_oprn_act_out(i).attribute25 := get_rec.attribute25;
1391 x_oprn_act_out(i).attribute26 := get_rec.attribute26;
1392 x_oprn_act_out(i).attribute27 := get_rec.attribute27;
1393 x_oprn_act_out(i).attribute28 := get_rec.attribute28;
1394 x_oprn_act_out(i).attribute29 := get_rec.attribute29;
1395 x_oprn_act_out(i).attribute30 := get_rec.attribute30;
1396
1397 END LOOP;
1398
1399 IF i = 0 THEN
1400 RAISE fnd_api.g_exc_error;
1401 END IF;
1402
1403 /*standard call to get msge cnt, and if cnt is 1, get mesg info*/
1404 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1405
1406 EXCEPTION
1407 WHEN FND_API.G_EXC_ERROR THEN
1408 X_return_code := SQLCODE;
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1411
1412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1413 X_return_code := SQLCODE;
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1415 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1416
1417 WHEN OTHERS THEN
1418 X_return_code := SQLCODE;
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1421
1422 END get_oprn_act_detl;
1423
1424 /*******************************************************************************
1425 * Procedure get_oprn_resc_detl
1426 *
1427 * Procedure:- This returns the step, operation and activities, resources details for\
1428 * a given recipe based on the recipe_id passed to it.
1429 *
1430 *
1431 * Author :Pawan Kumar
1432 * History: Teresa Wong 7/17/2002 B2221515 Changed order by clause for cursor
1433 * get_recp_resc to include 9th column (oprn_line_id).
1434 * RajaSekhar 11/14/2002 BUG#2621411 Added code to retrieve 'capacity_tolerance'
1435 * and to assign the same to X_oprn_resc_rec of oprn_resc_tbl type.
1436 * History
1437 * Rameshwar 09-DEC-2002 BUG#2686887
1438 * Modified the order by clause of the cursor get_recp_resc.
1439 * Swapna - 26-SEP-2008 Bug No.7426185
1440 * Changed <AND conditon> in the cursor get_recp_resc, to verify whether p_organization_id
1441 * is NULL
1442 * Kishore - 20-Jan-2009 Bug No.7652625
1443 * Added Routingstep_id condition for the cursor get_recp_resc in the procedure, get_oprn_resc_detl.
1444 *********************************************************************************/
1445
1446
1447
1448 PROCEDURE get_oprn_resc_detl
1449 ( p_api_version IN NUMBER ,
1450 p_init_msg_list IN VARCHAR2 ,
1451 p_recipe_id IN NUMBER ,
1452 p_organization_id IN NUMBER ,
1453 x_return_status OUT NOCOPY VARCHAR2 ,
1454 x_msg_count OUT NOCOPY NUMBER ,
1455 x_msg_data OUT NOCOPY VARCHAR2 ,
1456 x_return_code OUT NOCOPY NUMBER ,
1457 X_oprn_resc_rec OUT NOCOPY oprn_resc_tbl
1458 )
1459 IS
1460 /* local Variables */
1461 l_api_name CONSTANT VARCHAR2(30) := 'get_recipe_id';
1462 l_api_version CONSTANT NUMBER := 1.0;
1463 i NUMBER := 0;
1464
1465
1466 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
1467 --BEGIN BUG #2686887 Rameshwar
1468 --Modified the order by clause from 1,9 to 1,3 ,9.
1469 CURSOR get_recp_resc IS
1470 SELECT r.recipe_id recipeid,
1471 d.routingstep_id , d.routingstep_no routing_step_no,
1472 o.oprn_id,o.oprn_no,o.oprn_vers, o.oprn_desc,
1473 a.activity,
1474 ror.oprn_line_id oprnline_id,ror.resources, ror.resource_usage, res.resource_count,
1475 ror.process_qty, res.prim_rsrc_ind, res.scale_type, res.cost_analysis_code,
1476 res.cost_cmpntcls_id, ror.usage_uom , a.offset_interval act_int, res.offset_interval res_int,
1477 ror.max_capacity, ror.min_capacity, m.capacity_um,m.capacity_constraint,
1478 m.capacity_tolerance,
1479 ror.process_um process_uom,
1480 /*
1481 ror.PROCESS_PARAMETER_1, ror.PROCESS_PARAMETER_2,
1482 ror.PROCESS_PARAMETER_3,ror.PROCESS_PARAMETER_4, ror.PROCESS_PARAMETER_5,
1483 */
1484 ror.text_code, ror.created_by,ror.last_updated_by,
1485 ror.last_update_date, ror.creation_date, ror.last_update_login,
1486 ror.attribute_category,
1487 ror.attribute1, ror.attribute2, ror.attribute3, ror.attribute4,
1488 ror.attribute5, ror.attribute6, ror.attribute7, ror.attribute8,
1489 ror.attribute9, ror.attribute10, ror.attribute11, ror.attribute12,
1490 ror.attribute13, ror.attribute14, ror.attribute15, ror.attribute16,
1491 ror.attribute17, ror.attribute18, ror.attribute19, ror.attribute20,
1492 ror.attribute21, ror.attribute22,ror.attribute23, ror.attribute24,
1493 ror.attribute25, ror.attribute26, ror.attribute27, ror.attribute28,
1494 ror.attribute29, ror.attribute30, 1 recipe_override,
1495 nvl(l.max_capacity,m.max_capacity)*(1+nvl(l.capacity_tolerance, m.capacity_tolerance)/100) max_calc_capacity,/*Added in bug13440294*/
1496 nvl(l.min_capacity, m.min_capacity)*(1-nvl(l.capacity_tolerance, m.capacity_tolerance)/100) min_calc_capacity /*Added in bug13440294*/
1497 FROM gmd_recipes_b r, fm_rout_dtl d,gmd_operations_vl o,
1498 gmd_operation_activities a, gmd_recipe_orgn_resources ror,
1499 gmd_operation_resources res, cr_rsrc_mst_b m
1500 , cr_rsrc_dtl l /*Added in bug13440294*/
1501 WHERE r.recipe_id = p_recipe_id
1502 AND d.routing_id = r.routing_id
1503 AND d.oprn_id = o.oprn_id
1504 AND a.oprn_id = d.oprn_id
1505 AND a.oprn_line_id = res.oprn_line_id
1506 AND d.routingstep_id = ror.routingstep_id -- Bug No.7652625
1507 AND ror.resources = res.resources
1508 AND res.resources = m.resources
1509 AND ror.oprn_line_id = res.oprn_line_id
1510 AND ror.recipe_id = r.recipe_id
1511 and m.resources = l.resources (+) /*Added in bug13440294*/
1512 -- AND (ror.organization_id = p_organization_id OR organization_id IS NULL)
1513 AND (ror.organization_id = p_organization_id OR p_organization_id IS NULL) /*Bug#7426185*/
1514
1515 UNION
1516
1517 SELECT r.recipe_id recipeid,
1518 d.routingstep_id , d.routingstep_no routing_step_no,
1519 o.oprn_id,o.oprn_no,o.oprn_vers, o.oprn_desc,
1520 a.activity,
1521 res.oprn_line_id oprnline_id,res.resources, res.resource_usage, res.resource_count,
1522 res.process_qty, prim_rsrc_ind, scale_type, cost_analysis_code, res.cost_cmpntcls_id,
1523 res.resource_usage_uom usage_uom, a.offset_interval act_int, res.offset_interval res_int,
1524 nvl(l.max_capacity,m.max_capacity) max_capacity,
1525 nvl(l.min_capacity, m.min_capacity) min_capacity,
1526 nvl(l.capacity_um,m.capacity_um) capacity_um,
1527 nvl(l.capacity_constraint, m.capacity_constraint) capacity_constraint,
1528 nvl(l.capacity_tolerance, m.capacity_tolerance) capacity_tolerance,
1529 res.resource_process_uom process_uom,
1530 /*
1531 PROCESS_PARAMETER_1, PROCESS_PARAMETER_2,
1532 PROCESS_PARAMETER_3,PROCESS_PARAMETER_4, PROCESS_PARAMETER_5,
1533 */
1534 res.text_code, res.created_by,res.last_updated_by,
1535 res.last_update_date, res.creation_date, res.last_update_login,
1536 res.attribute_category,
1537 res.attribute1, res.attribute2, res.attribute3, res.attribute4,
1538 res.attribute5, res.attribute6, res.attribute7, res.attribute8,
1539 res.attribute9, res.attribute10, res.attribute11, res.attribute12,
1540 res.attribute13, res.attribute14, res.attribute15, res.attribute16,
1541 res.attribute17, res.attribute18, res.attribute19, res.attribute20,
1542 res.attribute21, res.attribute22,res.attribute23, res.attribute24,
1543 res.attribute25, res.attribute26, res.attribute27, res.attribute28,
1544 res.attribute29, res.attribute30, 0 recipe_override,
1545 nvl(l.max_capacity,m.max_capacity)*(1+nvl(l.capacity_tolerance, m.capacity_tolerance)/100) max_calc_capacity,/*Added in bug13440294*/
1546 nvl(l.min_capacity, m.min_capacity)*(1-nvl(l.capacity_tolerance, m.capacity_tolerance)/100) min_calc_capacity /*Added in bug13440294*/
1547
1548 FROM gmd_recipes_b r, fm_rout_dtl d, gmd_operations_vl o,gmd_operation_activities a,
1549 gmd_operation_resources res, cr_rsrc_mst_b m, cr_rsrc_dtl l
1550 WHERE r.recipe_id = p_recipe_id
1551 AND d.routing_id = r.routing_id
1552 AND d.oprn_id = o.oprn_id
1553 AND o.oprn_id = a.oprn_id
1554 AND a.oprn_line_id = res.oprn_line_id
1555 AND m.resources = res.resources
1556 AND m.resources = l.resources (+)
1557 AND l.organization_id (+) = p_organization_id
1558 AND (res.oprn_line_id, res.resources)
1559 NOT IN ( SELECT oprn_line_id, resources
1560 FROM gmd_recipe_orgn_resources ror
1561 WHERE recipe_id = p_recipe_id
1562 AND (p_organization_id IS NULL OR organization_id = p_organization_id)
1563 AND d.routingstep_id = ror.routingstep_id) -- Bug No.7652625
1564 --Added the order by for the bug 12613037.
1565 ORDER BY recipeid, routing_step_no,act_int,activity, res_int,resources, oprnline_id ;
1566 --ORDER BY recipeid, routing_step_no, oprnline_id ;
1567
1568 --END BUG #2686887
1569
1570 BEGIN
1571 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1572 l_api_name, G_PKG_NAME) THEN
1573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1574 END IF;
1575 IF FND_API.to_Boolean(p_init_msg_list) THEN
1576 FND_MSG_PUB.initialize;
1577 END IF;
1578 x_return_status := FND_API.G_RET_STS_SUCCESS;
1579
1580 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
1581
1582 FOR get_rec IN get_recp_resc LOOP
1583 i := i + 1;
1584
1585 x_oprn_resc_rec(i).recipe_id := get_rec.recipeid ;
1586 x_oprn_resc_rec(i).routingstep_id := get_rec.routingstep_id;
1587 x_oprn_resc_rec(i).routingstep_no := get_rec.routing_step_no;
1588 x_oprn_resc_rec(i).oprn_id := get_rec.oprn_id;
1589 x_oprn_resc_rec(i).oprn_no := get_rec.oprn_no;
1590 x_oprn_resc_rec(i).oprn_vers := get_rec.oprn_vers;
1591 x_oprn_resc_rec(i).oprn_desc := get_rec.oprn_desc;
1592 x_oprn_resc_rec(i).activity := get_rec.activity;
1593 x_oprn_resc_rec(i).oprn_line_id := get_rec.oprnline_id ;
1594 x_oprn_resc_rec(i).resources := get_rec.resources ;
1595 x_oprn_resc_rec(i).resource_usage := get_rec.resource_usage ;
1596 x_oprn_resc_rec(i).resource_count := get_rec.resource_count ;
1597 x_oprn_resc_rec(i).process_qty := get_rec.process_qty ;
1598 x_oprn_resc_rec(i).prim_rsrc_ind := get_rec.prim_rsrc_ind ;
1599 x_oprn_resc_rec(i).scale_type := get_rec.scale_type ;
1600 x_oprn_resc_rec(i).cost_analysis_code := get_rec.cost_analysis_code ;
1601 x_oprn_resc_rec(i).cost_cmpntcls_id := get_rec.cost_cmpntcls_id ;
1602 x_oprn_resc_rec(i).capacity_constraint := get_rec.capacity_constraint ;
1603 x_oprn_resc_rec(i).capacity_tolerance := get_rec.capacity_tolerance ;
1604 x_oprn_resc_rec(i).usage_um := get_rec.usage_uom ;
1605 x_oprn_resc_rec(i).offset_interval := get_rec.res_int ;
1606 x_oprn_resc_rec(i).min_capacity := get_rec.min_capacity;
1607 x_oprn_resc_rec(i).max_capacity := get_rec.max_capacity;
1608 x_oprn_resc_rec(i).min_calc_capacity := get_rec.min_calc_capacity; /*Added in bug13440294*/
1609 x_oprn_resc_rec(i).max_calc_capacity := get_rec.max_calc_capacity; /*Added in bug13440294*/
1610 x_oprn_resc_rec(i).capacity_uom := get_rec.capacity_um;
1611 x_oprn_resc_rec(i).process_uom := get_rec.process_uom;
1612 --x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
1613 /*
1614 x_oprn_resc_rec(i).process_parameter_1 := get_rec.process_parameter_1 ;
1615 x_oprn_resc_rec(i).process_parameter_2 := get_rec.process_parameter_2 ;
1616 x_oprn_resc_rec(i).process_parameter_3 := get_rec.process_parameter_3 ;
1617 x_oprn_resc_rec(i).process_parameter_4 := get_rec.process_parameter_4 ;
1618 x_oprn_resc_rec(i).process_parameter_5 := get_rec.process_parameter_5 ;
1619 */
1620 x_oprn_resc_rec(i).recipe_override := get_rec.recipe_override;
1621 x_oprn_resc_rec(i).text_code := get_rec.text_code ;
1622 x_oprn_resc_rec(i).last_updated_by := get_rec.last_updated_by ;
1623 x_oprn_resc_rec(i).created_by := get_rec.created_by ;
1624 x_oprn_resc_rec(i).last_update_date := get_rec.last_update_date ;
1625 x_oprn_resc_rec(i).creation_date := get_rec.creation_date ;
1626 x_oprn_resc_rec(i).last_update_login := get_rec.last_update_login;
1627 x_oprn_resc_rec(i).attribute_category := get_rec.attribute_category;
1628 x_oprn_resc_rec(i).attribute1 := get_rec.attribute1;
1629 x_oprn_resc_rec(i).attribute2 := get_rec.attribute2;
1630 x_oprn_resc_rec(i).attribute3 := get_rec.attribute3;
1631 x_oprn_resc_rec(i).attribute4 := get_rec.attribute4;
1632 x_oprn_resc_rec(i).attribute5 := get_rec.attribute5;
1633 x_oprn_resc_rec(i).attribute6 := get_rec.attribute6;
1634 x_oprn_resc_rec(i).attribute7 := get_rec.attribute7;
1635 x_oprn_resc_rec(i).attribute8 := get_rec.attribute8;
1636 x_oprn_resc_rec(i).attribute9 := get_rec.attribute9;
1637 x_oprn_resc_rec(i).attribute10 := get_rec.attribute10;
1638 x_oprn_resc_rec(i).attribute11 := get_rec.attribute11;
1639 x_oprn_resc_rec(i).attribute12 := get_rec.attribute12;
1640 x_oprn_resc_rec(i).attribute13 := get_rec.attribute13;
1641 x_oprn_resc_rec(i).attribute14 := get_rec.attribute14;
1642 x_oprn_resc_rec(i).attribute15 := get_rec.attribute15;
1643 x_oprn_resc_rec(i).attribute16 := get_rec.attribute16;
1644 x_oprn_resc_rec(i).attribute17 := get_rec.attribute17;
1645 x_oprn_resc_rec(i).attribute18 := get_rec.attribute18;
1646 x_oprn_resc_rec(i).attribute19 := get_rec.attribute19;
1647 x_oprn_resc_rec(i).attribute20 := get_rec.attribute20;
1648 x_oprn_resc_rec(i).attribute21 := get_rec.attribute21;
1649 x_oprn_resc_rec(i).attribute22 := get_rec.attribute22;
1650 x_oprn_resc_rec(i).attribute23 := get_rec.attribute23;
1651 x_oprn_resc_rec(i).attribute24 := get_rec.attribute24;
1652 x_oprn_resc_rec(i).attribute25 := get_rec.attribute25;
1653 x_oprn_resc_rec(i).attribute26 := get_rec.attribute26;
1654 x_oprn_resc_rec(i).attribute27 := get_rec.attribute27;
1655 x_oprn_resc_rec(i).attribute28 := get_rec.attribute28;
1656 x_oprn_resc_rec(i).attribute29 := get_rec.attribute29;
1657 x_oprn_resc_rec(i).attribute30 := get_rec.attribute30;
1658
1659
1660 END LOOP;
1661
1662 IF i = 0 THEN
1663 RAISE fnd_api.g_exc_error;
1664 END IF; -- end if recipe_id not found
1665
1666 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
1667 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1668
1669 EXCEPTION
1670 WHEN FND_API.G_EXC_ERROR THEN
1671 X_return_code := SQLCODE;
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1674
1675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1676 X_return_code := SQLCODE;
1677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1678 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1679
1680 WHEN OTHERS THEN
1681 X_return_code := SQLCODE;
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1684
1685 END get_oprn_resc_detl;
1686
1687
1688 PROCEDURE get_recipe_process_param_detl
1689 ( p_api_version IN NUMBER ,
1690 p_init_msg_list IN VARCHAR2 ,
1691 p_recipe_id IN NUMBER ,
1692 p_organization_id IN NUMBER ,
1693 x_return_status OUT NOCOPY VARCHAR2 ,
1694 x_msg_count OUT NOCOPY NUMBER ,
1695 x_msg_data OUT NOCOPY VARCHAR2 ,
1696 X_recp_resc_proc_param_tbl OUT NOCOPY recp_resc_proc_param_tbl
1697 ) IS
1698
1699 CURSOR Cur_get_recp_rsrc IS
1700 SELECT dtl.routingstep_id, dtl.routingstep_no, act.oprn_line_id, res.resources
1701 FROM gmd_recipes_b rcp, fm_rout_dtl dtl,
1702 gmd_operation_activities act , gmd_operation_resources res
1703 WHERE rcp.recipe_id = p_recipe_id
1704 AND dtl.routing_id = rcp.routing_id
1705 AND dtl.oprn_id = act.oprn_id
1706 AND act.oprn_line_id = res.oprn_line_id;
1707
1708
1709 /* Parameters at the generic resource level */
1710 CURSOR Cur_get_gen_rsrc (V_resources VARCHAR2) IS
1711 SELECT p.parameter_id, parameter_name, parameter_description,
1712 units, r.target_value, r.minimum_value, r.maximum_value,p.parameter_type,r.sequence_no,
1713 r.created_by, r.creation_date, r.last_updated_by, r.last_update_date, r.last_update_login
1714 FROM gmp_resource_parameters r, gmp_process_parameters p
1715 WHERE p.parameter_id = r.parameter_id
1716 AND r.resources = V_resources
1717 ORDER BY r.sequence_no;
1718
1719 /* Parameters at the recipe resource level */
1720 CURSOR Cur_get_oprn_rsrc (V_oprn_line_id NUMBER,
1721 V_resources VARCHAR2, V_parameter_id NUMBER) IS
1722 SELECT *
1723 FROM gmd_oprn_process_parameters
1724 WHERE oprn_line_id = V_oprn_line_id
1725 AND resources = V_resources
1726 AND parameter_id = V_parameter_id;
1727
1728 l_oprn_rec Cur_get_oprn_rsrc%ROWTYPE;
1729
1730 /* Parameters at the operation resource level */
1731 CURSOR Cur_get_rcp_rsrc (V_routingstep_id NUMBER, V_oprn_line_id NUMBER,
1732 V_resources VARCHAR2, V_parameter_id NUMBER) IS
1733 SELECT *
1734 FROM gmd_recipe_process_parameters
1735 WHERE recipe_id = p_recipe_id
1736 AND organization_id = p_organization_id
1737 AND routingstep_id = V_routingstep_id
1738 AND oprn_line_id = V_oprn_line_id
1739 AND resources = V_resources
1740 AND parameter_id = V_parameter_id;
1741
1742 l_rcp_rec Cur_get_rcp_rsrc%ROWTYPE;
1743
1744 /* Parameters at the plant resource level */
1745 CURSOR Cur_get_plnt_rsrc (V_resources VARCHAR2, V_parameter_id NUMBER) IS
1746 SELECT p.*
1747 FROM gmp_plant_rsrc_parameters p, cr_rsrc_dtl c
1748 WHERE p.resource_id = c.resource_id
1749 AND organization_id = p_organization_id
1750 AND resources = V_resources
1751 AND parameter_id = V_parameter_id;
1752
1753 l_plnt_rec Cur_get_plnt_rsrc%ROWTYPE;
1754
1755 X_row NUMBER DEFAULT 0;
1756 X_found NUMBER(5) DEFAULT 0;
1757 X_override NUMBER(5) DEFAULT 0;
1758 X_target_value gmd_recipe_process_parameters.target_value%type ;
1759 X_minimum_value NUMBER ;
1760 X_maximum_value NUMBER ;
1761 X_created_by gmd_recipe_process_parameters.created_by%type ;
1762 X_last_updated_by gmd_recipe_process_parameters.last_updated_by%type ;
1763 X_last_update_date gmd_recipe_process_parameters.last_update_date%type ;
1764 X_creation_date gmd_recipe_process_parameters.creation_date%type ;
1765 X_last_update_login gmd_recipe_process_parameters.last_update_login%type ;
1766 BEGIN
1767 FOR l_rcp_res_rec IN Cur_get_recp_rsrc LOOP
1768 FOR l_rec IN Cur_get_gen_rsrc (l_rcp_res_rec.resources) LOOP
1769
1770 X_target_value := l_rec.target_value;
1771 X_minimum_value := l_rec.minimum_value;
1772 X_maximum_value := l_rec.maximum_value;
1773 X_created_by := l_rec.created_by;
1774 X_last_updated_by := l_rec.last_updated_by;
1775 X_creation_date := l_rec.creation_date;
1776 X_last_update_date := l_rec.last_update_date;
1777 X_last_update_login := l_rec.last_update_login;
1778
1779 /* Now let us check for overrides at recipe level */
1780 IF p_organization_id IS NOT NULL THEN
1781 OPEN Cur_get_rcp_rsrc (l_rcp_res_rec.routingstep_id, l_rcp_res_rec.oprn_line_id,
1782 l_rcp_res_rec.resources, l_rec.parameter_id);
1783 FETCH Cur_get_rcp_rsrc INTO l_rcp_rec;
1784 IF Cur_get_rcp_rsrc%FOUND THEN
1785 X_found := 1;
1786 X_override := 1;
1787 X_target_value := l_rcp_rec.target_value;
1788 X_minimum_value := l_rcp_rec.minimum_value;
1789 X_maximum_value := l_rcp_rec.maximum_value;
1790 X_created_by := l_rcp_rec.created_by;
1791 X_last_updated_by := l_rcp_rec.last_updated_by;
1792 X_creation_date := l_rcp_rec.creation_date;
1793 X_last_update_date := l_rcp_rec.last_update_date;
1794 X_last_update_login := l_rcp_rec.last_update_login;
1795 END IF;
1796 CLOSE Cur_get_rcp_rsrc;
1797 END IF; /* IF p_orgn_code IS NOT NULL */
1798
1799 /* Now let us check for overrides at operation level */
1800 IF X_found = 0 THEN
1801 OPEN Cur_get_oprn_rsrc (l_rcp_res_rec.oprn_line_id,
1802 l_rcp_res_rec.resources, l_rec.parameter_id);
1803 FETCH Cur_get_oprn_rsrc INTO l_oprn_rec;
1804 IF Cur_get_oprn_rsrc%FOUND THEN
1805 X_found := 1;
1806 X_target_value := l_oprn_rec.target_value;
1807 X_minimum_value := l_oprn_rec.minimum_value;
1808 X_maximum_value := l_oprn_rec.maximum_value;
1809 X_created_by := l_oprn_rec.created_by;
1810 X_last_updated_by := l_oprn_rec.last_updated_by;
1811 X_creation_date := l_oprn_rec.creation_date;
1812 X_last_update_date := l_oprn_rec.last_update_date;
1813 X_last_update_login := l_oprn_rec.last_update_login;
1814 END IF;
1815 CLOSE Cur_get_oprn_rsrc;
1816 END IF; /* IF X_found = 0 */
1817
1818 /* Now let us check for overrides at plant resource level */
1819 IF X_found = 0 AND
1820 p_organization_id IS NOT NULL THEN
1821 OPEN Cur_get_plnt_rsrc (l_rcp_res_rec.resources, l_rec.parameter_id);
1822 FETCH Cur_get_plnt_rsrc INTO l_plnt_rec;
1823 IF Cur_get_plnt_rsrc%FOUND THEN
1824 X_found := 1;
1825 X_target_value := l_plnt_rec.target_value;
1826 X_minimum_value := l_plnt_rec.minimum_value;
1827 X_maximum_value := l_plnt_rec.maximum_value;
1828 X_created_by := l_plnt_rec.created_by;
1829 X_last_updated_by := l_plnt_rec.last_updated_by;
1830 X_creation_date := l_plnt_rec.creation_date;
1831 X_last_update_date := l_plnt_rec.last_update_date;
1832 X_last_update_login := l_plnt_rec.last_update_login;
1833 END IF;
1834 CLOSE Cur_get_plnt_rsrc;
1835 END IF; /* IF X_found = 0 */
1836
1837 X_row := X_row + 1;
1838
1839 X_recp_resc_proc_param_tbl(X_row).recipe_id := p_recipe_id;
1840 X_recp_resc_proc_param_tbl(X_row).routingstep_id := l_rcp_res_rec.routingstep_id;
1841 X_recp_resc_proc_param_tbl(X_row).routingstep_no := l_rcp_res_rec.routingstep_no;
1842 X_recp_resc_proc_param_tbl(X_row).oprn_line_id := l_rcp_res_rec.oprn_line_id;
1843 X_recp_resc_proc_param_tbl(X_row).resources := l_rcp_res_rec.resources;
1844 X_recp_resc_proc_param_tbl(X_row).parameter_id := l_rec.parameter_id;
1845 X_recp_resc_proc_param_tbl(X_row).parameter_name := l_rec.parameter_name;
1846 X_recp_resc_proc_param_tbl(X_row).parameter_description := l_rec.parameter_description;
1847 X_recp_resc_proc_param_tbl(X_row).units := l_rec.units;
1848 X_recp_resc_proc_param_tbl(X_row).target_value := X_target_value;
1849 X_recp_resc_proc_param_tbl(X_row).minimum_value := X_minimum_value;
1850 X_recp_resc_proc_param_tbl(X_row).maximum_value := X_maximum_value;
1851 X_recp_resc_proc_param_tbl(X_row).parameter_type := l_rec.parameter_type;
1852 X_recp_resc_proc_param_tbl(X_row).sequence_no := l_rec.sequence_no;
1853 X_recp_resc_proc_param_tbl(X_row).created_by := X_created_by;
1854 X_recp_resc_proc_param_tbl(X_row).creation_date := X_creation_date;
1855 X_recp_resc_proc_param_tbl(X_row).last_updated_by := X_last_updated_by;
1856 X_recp_resc_proc_param_tbl(X_row).last_update_date := X_last_update_date;
1857 X_recp_resc_proc_param_tbl(X_row).last_update_login := X_last_update_login;
1858 X_recp_resc_proc_param_tbl(X_row).recipe_override := X_override;
1859 X_found := 0;
1860 END LOOP; /* FOR l_rec IN Cur_get_gen_rsrc */
1861 END LOOP; /* FOR l_rcp_res_rec IN Cur_get_recp_rsrc */
1862
1863 END get_recipe_process_param_detl;
1864
1865 /*======================================================================
1866 -- PROCEDURE :
1867 -- get_proc_param_desc
1868 --
1869 -- DESCRIPTION:
1870 -- This PL/SQL procedure is responsible for getting the
1871 -- description for a given process parameter.
1872 --
1873 -- REQUIREMENTS
1874 --
1875 -- SYNOPSIS:
1876 -- get_routing_no (100, x_parameter_desc);
1877 --
1878 --===================================================================== */
1879 PROCEDURE get_proc_param_desc(p_parameter_id IN NUMBER, x_parameter_desc OUT NOCOPY VARCHAR2) IS
1880 CURSOR get_proc_param_desc IS
1881 SELECT parameter_description
1882 FROM gmp_process_parameters_tl
1883 WHERE parameter_id = p_parameter_id
1884 AND language = USERENV('LANG');
1885 BEGIN
1886 OPEN get_proc_param_desc;
1887 FETCH get_proc_param_desc INTO x_parameter_desc;
1888 CLOSE get_proc_param_desc;
1889 END get_proc_param_desc;
1890
1891 /*======================================================================
1892 -- PROCEDURE :
1893 -- get_proc_param_units
1894 --
1895 -- DESCRIPTION:
1896 -- This PL/SQL procedure is responsible for getting the
1897 -- units for a given process parameter.
1898 --
1899 -- REQUIREMENTS
1900 --
1901 -- SYNOPSIS:
1902 -- get_proc_param_units (100, X_units);
1903 --
1904 --===================================================================== */
1905 PROCEDURE get_proc_param_units(p_parameter_id IN NUMBER, x_units OUT NOCOPY VARCHAR2) IS
1906 CURSOR get_proc_param_units IS
1907 SELECT units
1908 FROM gmp_process_parameters_b
1909 WHERE parameter_id = p_parameter_id;
1910 BEGIN
1911 OPEN get_proc_param_units;
1912 FETCH get_proc_param_units INTO x_units;
1913 CLOSE get_proc_param_units;
1914 END get_proc_param_units;
1915
1916 /*======================================================================
1917 -- PROCEDURE :
1918 -- fetch_contiguous_ind
1919 --
1920 -- DESCRIPTION:
1921 -- This PL/SQL procedure is responsible for getting the contiguous indicator
1922 -- value set at Recipe - Orgn level or at the Recipe level in order based on the
1923 -- i/p parameters
1924 --
1925 -- HISTORY
1926 -- Sriram.S 21Feb2005 Contiguous Indicator ME
1927 --
1928 -- SYNOPSIS:
1929 -- fetch_contiguous_ind (p_recipe_id, p_orgn_id, p_recipe_validity_rule_id,
1930 -- x_contiguous_ind, x_return_status);
1931 --
1932 --===================================================================== */
1933
1934 PROCEDURE FETCH_CONTIGUOUS_IND (
1935 p_recipe_id IN NUMBER
1936 ,p_orgn_id IN NUMBER
1937 ,p_recipe_validity_rule_id IN NUMBER
1938 ,x_contiguous_ind OUT NOCOPY NUMBER
1939 ,x_return_status OUT NOCOPY VARCHAR2) IS
1940
1941
1942 -- Cursor to get recipe_id and organization
1943 CURSOR get_recp_orgn_id IS
1944 SELECT recipe_id, organization_id
1945 FROM gmd_recipe_validity_rules
1946 WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
1947
1948 -- Cursor to fetch contiguous indicator at recp-orgn level
1949 CURSOR get_recp_orgn_cont_ind( l_recp_id NUMBER, l_orgn_id NUMBER) IS
1950 SELECT contiguous_ind
1951 FROM gmd_recipe_process_loss
1952 WHERE recipe_id = l_recp_id
1953 AND organization_id = l_orgn_id;
1954
1955 -- Cursor to fetch contiguous indicator at recipe level
1956 CURSOR get_recp_cont_ind( l_recp_id NUMBER) IS
1957 SELECT contiguous_ind
1958 FROM gmd_recipes_b
1959 WHERE recipe_id = l_recp_id;
1960
1961 l_recipe_id NUMBER;
1962 l_orgn_id NUMBER;
1963 l_cont_ind NUMBER;
1964
1965 INVALID_DATA EXCEPTION;
1966
1967 BEGIN
1968
1969 /* Set return status to success initially */
1970 x_return_status := FND_API.G_RET_STS_SUCCESS;
1971
1972 /* Chk. whether the recipe id or the validity rule id is passed */
1973 IF (p_recipe_id IS NULL AND p_recipe_validity_rule_id IS NULL) THEN
1974 RAISE INVALID_DATA;
1975 END IF;
1976
1977 -- If Validity Rule id is passed, fetch the corresponding recipe_id and orgn_id
1978 IF p_recipe_validity_rule_id IS NOT NULL THEN
1979 OPEN get_recp_orgn_id;
1980 FETCH get_recp_orgn_id INTO l_recipe_id, l_orgn_id;
1981 IF get_recp_orgn_id%NOTFOUND THEN
1982 CLOSE get_recp_orgn_id;
1983 RAISE INVALID_DATA;
1984 END IF;
1985 CLOSE get_recp_orgn_id;
1986
1987 -- If l_orgn_id is NULL (Global Validity rule) and if p_orgn_id is passed
1988 -- then use p_orgn_id to retrieve contiguous ind.
1989 IF (l_orgn_id IS NULL AND p_orgn_id IS NOT NULL) THEN
1990 l_orgn_id := p_orgn_id;
1991 END IF;
1992 ELSE
1993 -- If Validity Rule id is not passed, use the recipe and orgn id i/p parameters
1994 l_recipe_id := p_recipe_id;
1995 l_orgn_id := p_orgn_id;
1996 END IF;
1997
1998 -- Verify that recipe id is NOT NULL
1999 IF (l_recipe_id IS NULL) THEN
2000 RAISE INVALID_DATA;
2001 END IF;
2002
2003 IF (l_recipe_id IS NOT NULL AND l_orgn_id IS NOT NULL) THEN
2004 -- Try to fetch the contiguous ind set at the recipe - orgn level
2005 OPEN get_recp_orgn_cont_ind(l_recipe_id, l_orgn_id);
2006 FETCH get_recp_orgn_cont_ind INTO l_cont_ind;
2007 CLOSE get_recp_orgn_cont_ind;
2008 END IF;
2009
2010 IF (l_cont_ind IS NULL) THEN
2011 -- Cont Ind. value was not found at recipe-orgn level. Try fetching at recipe level.
2012 OPEN get_recp_cont_ind(l_recipe_id);
2013 FETCH get_recp_cont_ind INTO l_cont_ind;
2014 CLOSE get_recp_cont_ind;
2015 END IF;
2016
2017 IF (l_cont_ind IS NULL) THEN
2018 -- Cont Ind. value was not found at recipe-orgn level and recipe levels.
2019 x_contiguous_ind := 0;
2020 ELSE
2021 -- Assign cont ind. to the OUT parameter
2022 x_contiguous_ind := l_cont_ind;
2023 END IF;
2024
2025 EXCEPTION
2026
2027 WHEN INVALID_DATA THEN
2028 x_contiguous_ind := NULL;
2029 fnd_message.set_name ('GMI', 'GMI_MISSING');
2030 fnd_message.set_token ('MISSING', 'RECIPE_ID');
2031 fnd_msg_pub.add;
2032 x_return_status := FND_API.G_RET_STS_ERROR;
2033
2034 END FETCH_CONTIGUOUS_IND;
2035
2036 /*======================================================================
2037 -- PROCEDURE :
2038 -- fetch_enhanced_pi_ind
2039 --
2040 -- DESCRIPTION:
2041 -- This PL/SQL procedure is responsible for getting the Enhanced PI Indicator
2042 -- value set at Recipe header level
2043 --
2044 -- HISTORY
2045 -- Sriram.S 03MayFeb2005 GMD-GMO Integration
2046 --
2047 -- SYNOPSIS:
2048 -- fetch_enhanced_pi_ind (p_recipe_id, p_recipe_validity_rule_id,
2049 -- x_enhanced_pi_ind, x_return_status);
2050 --
2051 --===================================================================== */
2052
2053 PROCEDURE FETCH_ENHANCED_PI_IND (
2054 p_recipe_id IN NUMBER
2055 ,p_recipe_validity_rule_id IN NUMBER
2056 ,x_enhanced_pi_ind OUT NOCOPY VARCHAR2
2057 ,x_return_status OUT NOCOPY VARCHAR2) IS
2058
2059 -- Cursor to get recipe_id from validity_rule_id
2060 CURSOR get_recp_id IS
2061 SELECT recipe_id
2062 FROM gmd_recipe_validity_rules
2063 WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
2064
2065 -- Cursor to fetch enhanced PI flag at recipe level
2066 CURSOR get_pi_flag( l_recp_id NUMBER) IS
2067 SELECT enhanced_pi_ind
2068 FROM gmd_recipes_b
2069 WHERE recipe_id = l_recp_id;
2070
2071 l_recipe_id NUMBER;
2072 l_pi_ind VARCHAR2(1);
2073
2074 INVALID_DATA EXCEPTION;
2075
2076 BEGIN
2077
2078 /* Set return status to success initially */
2079 x_return_status := FND_API.G_RET_STS_SUCCESS;
2080
2081 /* Chk. whether the recipe id or the validity rule id is passed */
2082 IF (p_recipe_id IS NULL AND p_recipe_validity_rule_id IS NULL) THEN
2083 RAISE INVALID_DATA;
2084 END IF;
2085
2086 -- If Validity Rule id is passed, fetch the corresponding recipe_id
2087 IF p_recipe_validity_rule_id IS NOT NULL THEN
2088 OPEN get_recp_id;
2089 FETCH get_recp_id INTO l_recipe_id;
2090 CLOSE get_recp_id;
2091 ELSE
2092 -- If Validity Rule id is not passed, use the recipe id i/p parameter
2093 l_recipe_id := p_recipe_id;
2094 END IF;
2095
2096 -- Verify that recipe id is NOT NULL
2097 IF (l_recipe_id IS NULL) THEN
2098 RAISE INVALID_DATA;
2099 END IF;
2100
2101 -- Get the PI flag for the recipe id
2102 OPEN get_pi_flag(l_recipe_id);
2103 FETCH get_pi_flag INTO l_pi_ind;
2104 CLOSE get_pi_flag;
2105
2106 IF (l_pi_ind IS NULL) THEN
2107 x_enhanced_pi_ind := 'N';
2108 ELSE
2109 -- Assign PI indicator value to the OUT parameter
2110 x_enhanced_pi_ind := l_pi_ind;
2111 END IF;
2112
2113 EXCEPTION
2114
2115 WHEN INVALID_DATA THEN
2116 x_enhanced_pi_ind := NULL;
2117 fnd_message.set_name ('GMI', 'GMI_MISSING');
2118 fnd_message.set_token ('MISSING', 'RECIPE_ID');
2119 fnd_msg_pub.add;
2120 x_return_status := FND_API.G_RET_STS_ERROR;
2121
2122 END FETCH_ENHANCED_PI_IND;
2123
2124 END GMD_RECIPE_FETCH_PUB ;