[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_FETCH_PUB
Source
1 PACKAGE BODY GMD_RECIPE_FETCH_PUB AS
2 /* $Header: GMDPRCFB.pls 120.10.12010000.2 2008/09/26 07:24:44 kannavar 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, dtl.text_code, dtl.routingstep_id,dtl.steprelease_type,
839 dtl.minimum_transfer_qty, 0 RECIPE_ID, dtl.creation_date, dtl.created_by,dtl.last_updated_by,
840 dtl.last_update_date, dtl.last_update_login, dtl.attribute_category,
841 dtl.attribute1, dtl.attribute2, dtl.attribute3, dtl.attribute4,
842 dtl.attribute5, dtl.attribute6, dtl.attribute7, dtl.attribute8,
843 dtl.attribute9, dtl.attribute10, dtl.attribute11, dtl.attribute12,
844 dtl.attribute13, dtl.attribute14, dtl.attribute15, dtl.attribute16,
845 dtl.attribute17, dtl.attribute18, dtl.attribute19, dtl.attribute20,
846 dtl.attribute21, dtl.attribute22, dtl.attribute23, dtl.attribute24,
847 dtl.attribute25, dtl.attribute26, dtl.attribute27, dtl.attribute28,
848 dtl.attribute29, dtl.attribute30
849 FROM fm_rout_dtl dtl, gmd_recipes_b recp , gmd_operations_vl oprn
850 WHERE recp.recipe_id = p_recipe_id
851 AND dtl.routing_id = l_routing_id
852 AND oprn.oprn_id = dtl.oprn_id
853 AND dtl.routingstep_id NOT IN (SELECT routingstep_id
854 FROM gmd_recipe_routing_steps
855 WHERE recipe_id = p_recipe_id
856 AND l_auto_calc = 0)
857 ORDER BY routingstep_no;
858
859 /*Bug# 3612365 - Thomas Daniel */
860 /*Added the following cursor to pass back the resource causing the charge on the step */
861
862 CURSOR Cur_get_charge_resource (V_routingstep_id NUMBER, V_max_capacity NUMBER) IS
863 SELECT resources
864 FROM gmd_recipe_orgn_resources
865 WHERE routingstep_id = V_routingstep_id
866 AND recipe_id = p_recipe_id
867 AND organization_id = P_organization_id
868 AND max_capacity = V_max_capacity
869 UNION
870 SELECT r.resources
871 FROM fm_rout_dtl d, gmd_operation_resources r,
872 gmd_operation_activities a, cr_rsrc_dtl d
873 WHERE d.routingstep_id = V_routingstep_id
874 AND d.oprn_id = a.oprn_id
875 AND a.oprn_line_id = r.oprn_line_id
876 AND r.resources = d.resources
877 AND organization_id = P_organization_id
878 AND d.max_capacity = V_max_capacity
879 AND capacity_constraint = 1
880 UNION
881 SELECT r.resources
882 FROM fm_rout_dtl d, gmd_operation_resources r,
883 gmd_operation_activities a, cr_rsrc_mst m
884 WHERE d.routingstep_id = V_routingstep_id
885 AND d.oprn_id = a.oprn_id
886 AND a.oprn_line_id = r.oprn_line_id
887 AND r.resources = m.resources
888 AND m.max_capacity = V_max_capacity
889 AND capacity_constraint = 1;
890
891 CURSOR get_orgn_code IS
892 SELECT organization_code
893 FROM org_access_view
894 WHERE organization_id = p_organization_id;
895
896 BEGIN
897 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
898 l_api_name, G_PKG_NAME) THEN
899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
900 END IF;
901
902 IF FND_API.to_Boolean(p_init_msg_list) THEN
903 FND_MSG_PUB.initialize;
904 END IF;
905
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907
908 -- call the get_routing_id procedure to check the routing exists or not
909 OPEN Cur_get_recipe;
910 FETCH Cur_get_recipe INTO l_routing_id, l_calculate_step_qty;
911 CLOSE Cur_get_recipe;
912
913 IF (p_routing_id IS NOT NULL) THEN
914 l_routing_id := p_routing_id;
915 END IF;
916
917 IF l_routing_id IS NOT NULL THEN
918
919 IF l_calculate_step_qty = 1 THEN
920 gmd_auto_step_calc.calc_step_qty(p_parent_id => P_recipe_id,
921 p_step_tbl => l_step_tbl,
922 p_msg_count => l_msg_count,
923 p_msg_stack => l_msg_data,
924 p_return_status => l_return_status,
925 p_ignore_mass_conv => TRUE,
926 p_ignore_vol_conv => TRUE,
927 p_scale_factor => NVL(P_val_scale_factor,1),
928 p_process_loss => NVL(p_process_loss, 0),
929 p_organization_id => p_organization_id);
930 IF l_return_status <> FND_API.g_ret_sts_success THEN
931 RAISE FND_API.G_EXC_ERROR;
932 END IF;
933 ELSE
934 l_rout_scale_factor := GMD_COMMON_VAL.Get_Routing_Scale_Factor
935 (vRecipe_Id => p_recipe_id
936 ,x_return_status => l_return_status);
937 END IF; /* If l_calculate_step_qty = 1 */
938
939 FOR get_rec IN get_recipe_step (l_calculate_step_qty) LOOP
940 i := i + 1;
941 x_recipe_step_out(i).recipe_id := get_rec.recipe_id ;
942 x_recipe_step_out(i).routingstep_no := get_rec.routingstep_no ;
943 x_recipe_step_out(i).routingstep_id := get_rec.routingstep_id ;
944 x_recipe_step_out(i).oprn_id := get_rec.oprn_id ;
945 x_recipe_step_out(i).oprn_no := get_rec.oprn_no ;
946 x_recipe_step_out(i).oprn_vers := get_rec.oprn_vers ;
947 x_recipe_step_out(i).oprn_desc := get_rec.oprn_desc ;
948 x_recipe_step_out(i).process_qty_uom := get_rec.process_qty_uom ;
949 x_recipe_step_out(i).steprelease_type := get_rec.steprelease_type ;
950 x_recipe_step_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty ;
951
952 IF l_calculate_step_qty = 1 THEN
953 x_recipe_step_out(i).step_qty := l_step_tbl(i).step_qty;
954 ELSE
955 IF get_rec.recipe_id = 0 THEN
956 /* This implies that the step qty in get rec is from the routing */
957 x_recipe_step_out(i).step_qty := get_rec.step_qty * NVL(l_rout_scale_factor, 1)
958 * NVL(p_val_scale_factor, 1);
959 ELSE
960 /* This implies that the step qty in get rec is from the recipe */
961 x_recipe_step_out(i).step_qty := get_rec.step_qty * NVL(p_val_scale_factor, 1);
962 END IF;
963
964 l_step_tbl(i).step_id := x_recipe_step_out(i).routingstep_id;
965 l_step_tbl(i).step_no := x_recipe_step_out(i).routingstep_no;
966 l_step_tbl(i).step_qty := x_recipe_step_out(i).step_qty;
967 l_step_tbl(i).step_qty_uom := x_recipe_step_out(i).process_qty_uom;
968 END IF; /* If l_calculate_step_qty = 1 */
969
970 x_recipe_step_out(i).text_code := get_rec.text_code ;
971 x_recipe_step_out(i).last_updated_by := get_rec.last_updated_by ;
972 x_recipe_step_out(i).created_by := get_rec.created_by ;
973 x_recipe_step_out(i).last_update_date := get_rec.last_update_date ;
974 x_recipe_step_out(i).creation_date := get_rec.creation_date ;
975 x_recipe_step_out(i).last_update_login := get_rec.last_update_login ;
976 x_recipe_step_out(i).attribute1 := get_rec.attribute1 ;
977 x_recipe_step_out(i).attribute2 := get_rec.attribute2 ;
978 x_recipe_step_out(i).attribute3 := get_rec.attribute3 ;
979 x_recipe_step_out(i).attribute4 := get_rec.attribute4 ;
980 x_recipe_step_out(i).attribute5 := get_rec.attribute5 ;
981 x_recipe_step_out(i).attribute6 := get_rec.attribute6 ;
982 x_recipe_step_out(i).attribute7 := get_rec.attribute7 ;
983 x_recipe_step_out(i).attribute8 := get_rec.attribute8 ;
984 x_recipe_step_out(i).attribute9 := get_rec.attribute9 ;
985 x_recipe_step_out(i).attribute10 := get_rec.attribute10 ;
986 x_recipe_step_out(i).attribute11 := get_rec.attribute11 ;
987 x_recipe_step_out(i).attribute12 := get_rec.attribute12 ;
988 x_recipe_step_out(i).attribute13 := get_rec.attribute13 ;
989 x_recipe_step_out(i).attribute14 := get_rec.attribute14 ;
990 x_recipe_step_out(i).attribute15 := get_rec.attribute15 ;
991 x_recipe_step_out(i).attribute16 := get_rec.attribute16 ;
992 x_recipe_step_out(i).attribute17 := get_rec.attribute17 ;
993 x_recipe_step_out(i).attribute18 := get_rec.attribute18 ;
994 x_recipe_step_out(i).attribute19 := get_rec.attribute19 ;
995 x_recipe_step_out(i).attribute20 := get_rec.attribute20 ;
996 x_recipe_step_out(i).attribute21 := get_rec.attribute21 ;
997 x_recipe_step_out(i).attribute22 := get_rec.attribute22 ;
998 x_recipe_step_out(i).attribute23 := get_rec.attribute23 ;
999 x_recipe_step_out(i).attribute24 := get_rec.attribute24 ;
1000 x_recipe_step_out(i).attribute25 := get_rec.attribute25 ;
1001 x_recipe_step_out(i).attribute26 := get_rec.attribute26 ;
1002 x_recipe_step_out(i).attribute27 := get_rec.attribute27 ;
1003 x_recipe_step_out(i).attribute28 := get_rec.attribute28 ;
1004 x_recipe_step_out(i).attribute29 := get_rec.attribute29 ;
1005 x_recipe_step_out(i).attribute30 := get_rec.attribute30 ;
1006 END LOOP;
1007
1008 IF p_organization_id IS NOT NULL THEN
1009 OPEN get_orgn_code;
1010 FETCH get_orgn_code INTO l_orgn_code;
1011 CLOSE get_orgn_code;
1012 END IF;
1013
1014 -- call the charges procedure to get the max_capacity for the step.
1015 gmd_common_val.Calculate_Step_Charges (
1016 P_recipe_id => p_recipe_id ,
1017 P_tolerance => 0 ,
1018 P_orgn_id => p_organization_id ,
1019 P_step_tbl => l_step_tbl ,
1020 x_charge_tbl => l_charge_tbl ,
1021 x_return_status => l_return_status
1022 ) ;
1023
1024 FOR j IN 1..x_recipe_step_out.COUNT LOOP
1025 FOR k IN 1..l_charge_tbl.COUNT LOOP
1026 IF x_recipe_step_out(j).routingstep_id = l_charge_tbl(k).routingstep_id THEN
1027 x_recipe_step_out(j).max_capacity := l_charge_tbl(k).max_capacity;
1028 x_recipe_step_out(j).capacity_uom := l_charge_tbl(k).capacity_uom;
1029 x_recipe_step_out(j).charge := l_charge_tbl(k).charge;
1030
1031 /*Bug# 3612365 - Thomas Daniel */
1032 /*Added the following condition to populate the resource causing the charge */
1033 IF l_charge_tbl(k).max_capacity IS NOT NULL THEN
1034 -- Bug#5258672 use the capacity value in resource UOM
1035 OPEN Cur_get_charge_resource(l_charge_tbl(k).routingstep_id, l_charge_tbl(k).max_capacity_in_res_UOM);
1036 FETCH Cur_get_charge_resource INTO X_recipe_step_out(j).resources;
1037 CLOSE Cur_get_charge_resource;
1038 END IF;
1039 EXIT;
1040 END IF;
1041 END LOOP; /* FOR k IN 1..l_charge_tbl.COUNT */
1042 END LOOP; /* FOR j IN 1..x_recipe_step_out.COUNT */
1043
1044 END IF; /* If routing id is not null */
1045
1046 /* standard call to get msge cnt, and if cnt is 1, get mesg info*/
1047 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1048
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 X_return_code := SQLCODE;
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1054
1055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1056 X_return_code := SQLCODE;
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1059
1060 WHEN OTHERS THEN
1061 X_return_code := SQLCODE;
1062 x_return_status := FND_API.G_RET_STS_ERROR;
1063 FND_MSG_PUB.add_exc_msg ('GMD_RECIPE_FETCH_PUB', 'GET_RECIPE_STEP_DETAILS');
1064 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1065
1066 END get_recipe_step_details;
1067
1068 /*******************************************************************************
1069 * Procedure get_step_depd_details
1070 *
1071 * Procedure:- This returns the step dependency for information based on the
1072 * recipe_id passed to it.
1073 *
1074 *
1075 * Author :Pawan Kumar
1076 *
1077 *********************************************************************************/
1078
1079 PROCEDURE get_step_depd_details
1080
1081 ( p_api_version IN NUMBER ,
1082 p_init_msg_list IN VARCHAR2 ,
1083 p_recipe_id IN NUMBER ,
1084 x_return_status OUT NOCOPY VARCHAR2 ,
1085 x_msg_count OUT NOCOPY NUMBER ,
1086 x_msg_data OUT NOCOPY VARCHAR2 ,
1087 x_return_code OUT NOCOPY NUMBER ,
1088 x_routing_depd_tbl OUT NOCOPY routing_depd_tbl
1089 ) IS
1090
1091
1092 CURSOR get_depd IS
1093 SELECT routingstep_no,dep_routingstep_no, routing_id, dep_type, rework_code,
1094 standard_delay, minimum_delay, max_delay, transfer_qty, RoutingStep_No_uom,
1095 transfer_pct, text_code, creation_date, created_by,last_updated_by,
1096 last_update_date, last_update_login,chargeable_ind
1097 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
1098 --Added chargable_ind column to the select statement
1099 FROM fm_rout_dep
1100 WHERE routing_id = (SELECT routing_id
1101 FROM gmd_recipes_b
1102 WHERE recipe_id = p_recipe_id) ;
1103
1104
1105 depd_rec fm_rout_dep%rowtype;
1106
1107 /*** local Variables ***/
1108 l_api_name CONSTANT VARCHAR2(30) := 'get_step_depd_details';
1109 l_api_version CONSTANT NUMBER := 1.0;
1110 i NUMBER := 0;
1111 l_routing_id NUMBER;
1112 l_return_status VARCHAR2(30);
1113 l_msg_count NUMBER;
1114 l_return_code NUMBER ;
1115 l_msg_data VARCHAR2(2000) ;
1116
1117 BEGIN
1118 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1119 l_api_name, G_PKG_NAME) THEN
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122 IF FND_API.to_Boolean(p_init_msg_list) THEN
1123 FND_MSG_PUB.initialize;
1124 END IF;
1125 x_return_status := FND_API.G_RET_STS_SUCCESS;
1126 /*Check for circular step dependency */
1127 -- call the get_routing_id procedure to check the routing exists or not
1128
1129 get_routing_id (
1130 p_api_version => 1.0 ,
1131 p_recipe_no => NULL ,
1132 p_recipe_version => NULL ,
1133 p_recipe_id => p_recipe_id ,
1134 x_return_status => l_return_status ,
1135 x_msg_count => l_msg_count ,
1136 x_msg_data => l_msg_data ,
1137 x_return_code => l_return_code ,
1138 x_routing_id => l_routing_id);
1139
1140 IF l_routing_id IS NOT NULL THEN
1141 IF gmdrtval_pub.circular_dependencies_exist(l_routing_id) then
1142 x_return_status := 'U' ;
1143
1144 ELSE
1145
1146 FOR get_rec IN get_depd LOOP
1147 i := i + 1;
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149 x_routing_depd_tbl(i).dep_routingstep_no := get_rec.dep_routingstep_no ;
1150 x_routing_depd_tbl(i).routingstep_no := get_rec.routingstep_no ;
1151 x_routing_depd_tbl(i).routing_id := get_rec.routing_id;
1152 x_routing_depd_tbl(i).dep_type := get_rec.dep_type;
1153 x_routing_depd_tbl(i).rework_code := get_rec.rework_code ;
1154 x_routing_depd_tbl(i).standard_delay := get_rec.standard_delay ;
1155 x_routing_depd_tbl(i).minimum_delay := get_rec.minimum_delay ;
1156 x_routing_depd_tbl(i).max_delay := get_rec.max_delay ;
1157 x_routing_depd_tbl(i).transfer_qty := get_rec.transfer_qty ;
1158 x_routing_depd_tbl(i).RoutingStep_No_uom := get_rec.RoutingStep_No_uom;
1159 x_routing_depd_tbl(i).transfer_pct := get_rec.transfer_pct ;
1160 x_routing_depd_tbl(i).text_code := get_rec.text_code ;
1161 x_routing_depd_tbl(i).last_updated_by := get_rec.last_updated_by ;
1162 x_routing_depd_tbl(i).created_by := get_rec.created_by ;
1163 x_routing_depd_tbl(i).last_update_date := get_rec.last_update_date ;
1164 x_routing_depd_tbl(i).creation_date := get_rec.creation_date ;
1165 x_routing_depd_tbl(i).last_update_login := get_rec.last_update_login;
1166
1167 --Sriram.S APS K Enhancements 03March2004 Bug# 3410379
1168 x_routing_depd_tbl(i).chargeable_ind := get_rec.chargeable_ind;
1169 END LOOP;
1170
1171 IF i = 0 THEN
1172 RAISE fnd_api.g_exc_error;
1173 END IF;
1174 END IF;
1175 END IF;
1176
1177 /* standard call to get msge cnt, and if cnt is 1, get mesg info*/
1178 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1179
1180 EXCEPTION
1181 WHEN FND_API.G_EXC_ERROR THEN
1182 X_return_code := SQLCODE;
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1185
1186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1187 X_return_code := SQLCODE;
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1190
1191 WHEN OTHERS THEN
1192 X_return_code := SQLCODE;
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1195
1196 END get_step_depd_details;
1197
1198 /*******************************************************************************
1199 * Procedure get_oprn_act_detl
1200 *
1201 * Procedure:- This returns the step, operation and activities details for a given recipe
1202 * based on the recipe_id passed to it.
1203 *
1204 *
1205 * Author :Pawan Kumar
1206 * History
1207 * Rameshwar 09-DEC-2002 BUG#2686887
1208 * Modified the order by clause of the cursor get_recp_act.
1209 * S.Dulyk 11-MAR-2003 Bug 2845110 MTW enhancement - added material_ind
1210 *********************************************************************************/
1211
1212
1213 PROCEDURE get_oprn_act_detl
1214 ( p_api_version IN NUMBER ,
1215 p_init_msg_list IN VARCHAR2 ,
1216 p_recipe_id IN NUMBER ,
1217 p_organization_id IN NUMBER ,
1218 x_return_status OUT NOCOPY VARCHAR2 ,
1219 x_msg_count OUT NOCOPY NUMBER ,
1220 x_msg_data OUT NOCOPY VARCHAR2 ,
1221 x_return_code OUT NOCOPY NUMBER ,
1222 x_oprn_act_out OUT NOCOPY oprn_act_tbl
1223 ) IS
1224
1225 /* local Variables */
1226 l_api_name VARCHAR2(30) := 'get_oprn_act_detl';
1227 l_api_version NUMBER := 1.0;
1228 i NUMBER := 0;
1229
1230 --BEGIN BUG #2686887 Rameshwar
1231 --Modified the order by clause from 2,9 to 1,9.
1232 CURSOR get_recp_act IS
1233
1234 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,
1235 a.activity, fm.activity_desc, ra.oprn_line_id oprnline_id, ra.activity_factor, a.offset_interval,
1236 a.break_ind, a.max_break,a.material_ind, a.sequence_dependent_ind, ra.recipe_id,
1237 ra.text_code,ra.creation_date, ra.created_by,ra.last_updated_by,
1238 ra.last_update_date, ra.last_update_login, ra.attribute_category,
1239 ra.attribute1, ra.attribute2, ra.attribute3, ra.attribute4,
1240 ra.attribute5, ra.attribute6, ra.attribute7, ra.attribute8,
1241 ra.attribute9, ra.attribute10, ra.attribute11, ra.attribute12,
1242 ra.attribute13, ra.attribute14, ra.attribute15, ra.attribute16,
1243 ra.attribute17, ra.attribute18, ra.attribute19, ra.attribute20,
1244 ra.attribute21, ra.attribute22, ra.attribute23, ra.attribute24,
1245 ra.attribute25, ra.attribute26, ra.attribute27, ra.attribute28,
1246 ra.attribute29, ra.attribute30, 1 recipe_override
1247 FROM gmd_recipe_orgn_activities ra, fm_rout_dtl d,
1248 gmd_operations_vl o, gmd_operation_activities a , fm_actv_mst fm
1249 WHERE ra.recipe_id = p_recipe_id
1250 AND d.routingstep_id = ra.routingstep_id
1251 AND d.oprn_id = o.oprn_id
1252 AND a.activity = fm.activity
1253 AND ra.oprn_line_id = a.oprn_line_id
1254 AND (p_organization_id IS NULL OR ra.organization_id = p_organization_id)
1255
1256 UNION
1257 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,
1258 a.activity,fm.activity_desc, a.oprn_line_id oprnline_id, a.activity_factor,a.offset_interval,
1259 a.break_ind, a.max_break, a.material_ind,a.sequence_dependent_ind, r.RECIPE_ID,
1260 a.text_code, a.creation_date, a.created_by,a.last_updated_by,
1261 a.last_update_date, a.last_update_login, a.attribute_category,
1262 a.attribute1, a.attribute2, a.attribute3, a.attribute4,
1263 a.attribute5, a.attribute6, a.attribute7, a.attribute8,
1264 a.attribute9, a.attribute10, a.attribute11, a.attribute12,
1265 a.attribute13, a.attribute14, a.attribute15, a.attribute16,
1266 a.attribute17, a.attribute18, a.attribute19, a.attribute20,
1267 a.attribute21, a.attribute22, a.attribute23, a.attribute24,
1268 a.attribute25, a.attribute26, a.attribute27, a.attribute28,
1269 a.attribute29, a.attribute30, 0 recipe_override
1270 FROM fm_rout_dtl d, gmd_recipes_b r , gmd_operations_vl o, gmd_operation_activities a, fm_actv_mst fm
1271 WHERE r.recipe_id = p_recipe_id
1272 AND d.routing_id = r.routing_id
1273 AND o.oprn_id = d.oprn_id
1274 AND a.oprn_id = o.oprn_id
1275 AND a.activity = fm.activity
1276 AND a.oprn_line_id NOT IN (SELECT oprn_line_id
1277 FROM gmd_recipe_orgn_activities
1278 WHERE recipe_id = p_recipe_id
1279 AND (p_organization_id IS NULL or organization_id = p_organization_id))
1280 ORDER BY routing_step_no, oprnline_id;
1281 -- END BUG#2686887
1282
1283 BEGIN
1284
1285 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1286 l_api_name, G_PKG_NAME) THEN
1287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1288 END IF;
1289 IF FND_API.to_Boolean(p_init_msg_list) THEN
1290 FND_MSG_PUB.initialize;
1291 END IF;
1292
1293 FOR get_rec IN get_recp_act LOOP
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295 i := i + 1;
1296 x_oprn_act_out(i).routingstep_no := get_rec.routing_step_no ;
1297 x_oprn_act_out(i).routingstep_id := get_rec.routingstep_id ;
1298 x_oprn_act_out(i).oprn_no := get_rec.oprn_no ;
1299 x_oprn_act_out(i).oprn_desc := get_rec.oprn_desc ;
1300 x_oprn_act_out(i).oprn_vers := get_rec.oprn_vers ;
1301 x_oprn_act_out(i).oprn_id := get_rec.oprn_id;
1302 x_oprn_act_out(i).minimum_transfer_qty := get_rec.minimum_transfer_qty;
1303 x_oprn_act_out(i).activity := get_rec.activity ;
1304 x_oprn_act_out(i).activity_desc := get_rec.activity_desc ;
1305 x_oprn_act_out(i).oprn_line_id := get_rec.oprnline_id ;
1306 x_oprn_act_out(i).activity_factor := get_rec.activity_factor;
1307 x_oprn_act_out(i).sequence_dependent_ind := get_rec.sequence_dependent_ind;
1308 x_oprn_act_out(i).recipe_override := get_rec.recipe_override;
1309 x_oprn_act_out(i).offset_interval := get_rec.offset_interval;
1310 x_oprn_act_out(i).break_ind := get_rec.break_ind;
1311 x_oprn_act_out(i).max_break := get_rec.max_break;
1312 x_oprn_act_out(i).material_ind := get_rec.material_ind;
1313 x_oprn_act_out(i).text_code := get_rec.text_code ;
1314 x_oprn_act_out(i).creation_date := get_rec.creation_date ;
1315 x_oprn_act_out(i).created_by := get_rec.created_by ;
1316 x_oprn_act_out(i).last_updated_by := get_rec.last_updated_by ;
1317 x_oprn_act_out(i).last_update_date := get_rec.last_update_date ;
1318 x_oprn_act_out(i).last_update_login := get_rec.last_update_login;
1319 x_oprn_act_out(i).attribute_category := get_rec.attribute_category;
1320 x_oprn_act_out(i).attribute1 := get_rec.attribute1;
1321 x_oprn_act_out(i).attribute2 := get_rec.attribute2;
1322 x_oprn_act_out(i).attribute3 := get_rec.attribute3;
1323 x_oprn_act_out(i).attribute4 := get_rec.attribute4;
1324 x_oprn_act_out(i).attribute5 := get_rec.attribute5;
1325 x_oprn_act_out(i).attribute6 := get_rec.attribute6;
1326 x_oprn_act_out(i).attribute7 := get_rec.attribute7;
1327 x_oprn_act_out(i).attribute8 := get_rec.attribute8;
1328 x_oprn_act_out(i).attribute9 := get_rec.attribute9;
1329 x_oprn_act_out(i).attribute10 := get_rec.attribute10;
1330 x_oprn_act_out(i).attribute11 := get_rec.attribute11;
1331 x_oprn_act_out(i).attribute12 := get_rec.attribute12;
1332 x_oprn_act_out(i).attribute13 := get_rec.attribute13;
1333 x_oprn_act_out(i).attribute14 := get_rec.attribute14;
1334 x_oprn_act_out(i).attribute15 := get_rec.attribute15;
1335 x_oprn_act_out(i).attribute16 := get_rec.attribute16;
1336 x_oprn_act_out(i).attribute17 := get_rec.attribute17;
1337 x_oprn_act_out(i).attribute18 := get_rec.attribute18;
1338 x_oprn_act_out(i).attribute19 := get_rec.attribute19;
1339 x_oprn_act_out(i).attribute20 := get_rec.attribute20;
1340 x_oprn_act_out(i).attribute21 := get_rec.attribute21;
1341 x_oprn_act_out(i).attribute22 := get_rec.attribute22;
1342 x_oprn_act_out(i).attribute23 := get_rec.attribute23;
1343 x_oprn_act_out(i).attribute24 := get_rec.attribute24;
1344 x_oprn_act_out(i).attribute25 := get_rec.attribute25;
1345 x_oprn_act_out(i).attribute26 := get_rec.attribute26;
1346 x_oprn_act_out(i).attribute27 := get_rec.attribute27;
1347 x_oprn_act_out(i).attribute28 := get_rec.attribute28;
1348 x_oprn_act_out(i).attribute29 := get_rec.attribute29;
1349 x_oprn_act_out(i).attribute30 := get_rec.attribute30;
1350
1351 END LOOP;
1352
1353 IF i = 0 THEN
1354 RAISE fnd_api.g_exc_error;
1355 END IF;
1356
1357 /*standard call to get msge cnt, and if cnt is 1, get mesg info*/
1358 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1359
1360 EXCEPTION
1361 WHEN FND_API.G_EXC_ERROR THEN
1362 X_return_code := SQLCODE;
1363 x_return_status := FND_API.G_RET_STS_ERROR;
1364 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1365
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 X_return_code := SQLCODE;
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1370
1371 WHEN OTHERS THEN
1372 X_return_code := SQLCODE;
1373 x_return_status := FND_API.G_RET_STS_ERROR;
1374 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1375
1376 END get_oprn_act_detl;
1377
1378 /*******************************************************************************
1379 * Procedure get_oprn_resc_detl
1380 *
1381 * Procedure:- This returns the step, operation and activities, resources details for\
1382 * a given recipe based on the recipe_id passed to it.
1383 *
1384 *
1385 * Author :Pawan Kumar
1386 * History: Teresa Wong 7/17/2002 B2221515 Changed order by clause for cursor
1387 * get_recp_resc to include 9th column (oprn_line_id).
1388 * RajaSekhar 11/14/2002 BUG#2621411 Added code to retrieve 'capacity_tolerance'
1389 * and to assign the same to X_oprn_resc_rec of oprn_resc_tbl type.
1390 * History
1391 * Rameshwar 09-DEC-2002 BUG#2686887
1392 * Modified the order by clause of the cursor get_recp_resc.
1393 * Swapna - 26-SEP-2008 Bug No.7426185
1394 * Changed <AND conditon> in the cursor get_recp_resc, to verify whether p_organization_id
1395 * is NULL
1396 *********************************************************************************/
1397
1398
1399
1400 PROCEDURE get_oprn_resc_detl
1401 ( p_api_version IN NUMBER ,
1402 p_init_msg_list IN VARCHAR2 ,
1403 p_recipe_id IN NUMBER ,
1404 p_organization_id IN NUMBER ,
1405 x_return_status OUT NOCOPY VARCHAR2 ,
1406 x_msg_count OUT NOCOPY NUMBER ,
1407 x_msg_data OUT NOCOPY VARCHAR2 ,
1408 x_return_code OUT NOCOPY NUMBER ,
1409 X_oprn_resc_rec OUT NOCOPY oprn_resc_tbl
1410 )
1411 IS
1412 /* local Variables */
1413 l_api_name CONSTANT VARCHAR2(30) := 'get_recipe_id';
1414 l_api_version CONSTANT NUMBER := 1.0;
1415 i NUMBER := 0;
1416
1417
1418 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
1419 --BEGIN BUG #2686887 Rameshwar
1420 --Modified the order by clause from 1,9 to 1,3 ,9.
1421 CURSOR get_recp_resc IS
1422 SELECT r.recipe_id recipeid,
1423 d.routingstep_id , d.routingstep_no routing_step_no,
1424 o.oprn_id,o.oprn_no,o.oprn_vers, o.oprn_desc,
1425 a.activity,
1426 ror.oprn_line_id oprnline_id,ror.resources, ror.resource_usage, res.resource_count,
1427 ror.process_qty, res.prim_rsrc_ind, res.scale_type, res.cost_analysis_code,
1428 res.cost_cmpntcls_id, ror.usage_uom , res.offset_interval,
1429 ror.max_capacity, ror.min_capacity, m.capacity_um,m.capacity_constraint,
1430 m.capacity_tolerance,
1431 ror.process_um process_uom,
1432 /*
1433 ror.PROCESS_PARAMETER_1, ror.PROCESS_PARAMETER_2,
1434 ror.PROCESS_PARAMETER_3,ror.PROCESS_PARAMETER_4, ror.PROCESS_PARAMETER_5,
1435 */
1436 ror.text_code, ror.created_by,ror.last_updated_by,
1437 ror.last_update_date, ror.creation_date, ror.last_update_login,
1438 ror.attribute_category,
1439 ror.attribute1, ror.attribute2, ror.attribute3, ror.attribute4,
1440 ror.attribute5, ror.attribute6, ror.attribute7, ror.attribute8,
1441 ror.attribute9, ror.attribute10, ror.attribute11, ror.attribute12,
1442 ror.attribute13, ror.attribute14, ror.attribute15, ror.attribute16,
1443 ror.attribute17, ror.attribute18, ror.attribute19, ror.attribute20,
1444 ror.attribute21, ror.attribute22,ror.attribute23, ror.attribute24,
1445 ror.attribute25, ror.attribute26, ror.attribute27, ror.attribute28,
1446 ror.attribute29, ror.attribute30, 1 recipe_override
1447 FROM gmd_recipes_b r, fm_rout_dtl d,gmd_operations_vl o,
1448 gmd_operation_activities a, gmd_recipe_orgn_resources ror,
1449 gmd_operation_resources res, cr_rsrc_mst_b m
1450 WHERE r.recipe_id = p_recipe_id
1451 AND d.routing_id = r.routing_id
1452 AND d.oprn_id = o.oprn_id
1453 AND a.oprn_id = d.oprn_id
1454 AND a.oprn_line_id = res.oprn_line_id
1455 AND ror.resources = res.resources
1456 AND res.resources = m.resources
1457 AND ror.oprn_line_id = res.oprn_line_id
1458 AND ror.recipe_id = r.recipe_id
1459 -- AND (ror.organization_id = p_organization_id OR organization_id IS NULL)
1460 AND (ror.organization_id = p_organization_id OR p_organization_id IS NULL) /*Bug#7426185*/
1461
1462 UNION
1463
1464 SELECT r.recipe_id recipeid,
1465 d.routingstep_id , d.routingstep_no routing_step_no,
1466 o.oprn_id,o.oprn_no,o.oprn_vers, o.oprn_desc,
1467 a.activity,
1468 res.oprn_line_id oprnline_id,res.resources, res.resource_usage, res.resource_count,
1469 res.process_qty, prim_rsrc_ind, scale_type, cost_analysis_code, res.cost_cmpntcls_id,
1470 res.resource_usage_uom usage_uom, res.offset_interval, nvl(l.max_capacity,m.max_capacity) max_capacity,
1471 nvl(l.min_capacity, m.min_capacity) min_capacity,
1472 nvl(l.capacity_um,m.capacity_um) capacity_um,
1473 nvl(l.capacity_constraint, m.capacity_constraint) capacity_constraint,
1474 nvl(l.capacity_tolerance, m.capacity_tolerance) capacity_tolerance,
1475 res.resource_process_uom process_uom,
1476 /*
1477 PROCESS_PARAMETER_1, PROCESS_PARAMETER_2,
1478 PROCESS_PARAMETER_3,PROCESS_PARAMETER_4, PROCESS_PARAMETER_5,
1479 */
1480 res.text_code, res.created_by,res.last_updated_by,
1481 res.last_update_date, res.creation_date, res.last_update_login,
1482 res.attribute_category,
1483 res.attribute1, res.attribute2, res.attribute3, res.attribute4,
1484 res.attribute5, res.attribute6, res.attribute7, res.attribute8,
1485 res.attribute9, res.attribute10, res.attribute11, res.attribute12,
1486 res.attribute13, res.attribute14, res.attribute15, res.attribute16,
1487 res.attribute17, res.attribute18, res.attribute19, res.attribute20,
1488 res.attribute21, res.attribute22,res.attribute23, res.attribute24,
1489 res.attribute25, res.attribute26, res.attribute27, res.attribute28,
1490 res.attribute29, res.attribute30, 0 recipe_override
1491
1492 FROM gmd_recipes_b r, fm_rout_dtl d, gmd_operations_vl o,gmd_operation_activities a,
1493 gmd_operation_resources res, cr_rsrc_mst_b m, cr_rsrc_dtl l
1494 WHERE r.recipe_id = p_recipe_id
1495 AND d.routing_id = r.routing_id
1496 AND d.oprn_id = o.oprn_id
1497 AND o.oprn_id = a.oprn_id
1498 AND a.oprn_line_id = res.oprn_line_id
1499 AND m.resources = res.resources
1500 AND m.resources = l.resources (+)
1501 AND l.organization_id (+) = p_organization_id
1502 AND (res.oprn_line_id, res.resources)
1503 NOT IN ( SELECT oprn_line_id, resources
1504 FROM gmd_recipe_orgn_resources
1505 WHERE recipe_id = p_recipe_id
1506 AND (p_organization_id IS NULL OR organization_id = p_organization_id))
1507 ORDER BY recipeid, routing_step_no, oprnline_id ;
1508
1509 --END BUG #2686887
1510
1511 BEGIN
1512 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1513 l_api_name, G_PKG_NAME) THEN
1514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1515 END IF;
1516 IF FND_API.to_Boolean(p_init_msg_list) THEN
1517 FND_MSG_PUB.initialize;
1518 END IF;
1519 x_return_status := FND_API.G_RET_STS_SUCCESS;
1520
1521 /* BUG#2621411 RajaSekhar Added capacity_tolerance field */
1522
1523 FOR get_rec IN get_recp_resc LOOP
1524 i := i + 1;
1525
1526 x_oprn_resc_rec(i).recipe_id := get_rec.recipeid ;
1527 x_oprn_resc_rec(i).routingstep_id := get_rec.routingstep_id;
1528 x_oprn_resc_rec(i).routingstep_no := get_rec.routing_step_no;
1529 x_oprn_resc_rec(i).oprn_id := get_rec.oprn_id;
1530 x_oprn_resc_rec(i).oprn_no := get_rec.oprn_no;
1531 x_oprn_resc_rec(i).oprn_vers := get_rec.oprn_vers;
1532 x_oprn_resc_rec(i).oprn_desc := get_rec.oprn_desc;
1533 x_oprn_resc_rec(i).activity := get_rec.activity;
1534 x_oprn_resc_rec(i).oprn_line_id := get_rec.oprnline_id ;
1535 x_oprn_resc_rec(i).resources := get_rec.resources ;
1536 x_oprn_resc_rec(i).resource_usage := get_rec.resource_usage ;
1537 x_oprn_resc_rec(i).resource_count := get_rec.resource_count ;
1538 x_oprn_resc_rec(i).process_qty := get_rec.process_qty ;
1539 x_oprn_resc_rec(i).prim_rsrc_ind := get_rec.prim_rsrc_ind ;
1540 x_oprn_resc_rec(i).scale_type := get_rec.scale_type ;
1541 x_oprn_resc_rec(i).cost_analysis_code := get_rec.cost_analysis_code ;
1542 x_oprn_resc_rec(i).cost_cmpntcls_id := get_rec.cost_cmpntcls_id ;
1543 x_oprn_resc_rec(i).capacity_constraint := get_rec.capacity_constraint ;
1544 x_oprn_resc_rec(i).capacity_tolerance := get_rec.capacity_tolerance ;
1545 x_oprn_resc_rec(i).usage_um := get_rec.usage_uom ;
1546 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
1547 x_oprn_resc_rec(i).min_capacity := get_rec.min_capacity;
1548 x_oprn_resc_rec(i).max_capacity := get_rec.max_capacity;
1549 x_oprn_resc_rec(i).capacity_uom := get_rec.capacity_um;
1550 x_oprn_resc_rec(i).process_uom := get_rec.process_uom;
1551 x_oprn_resc_rec(i).offset_interval := get_rec.offset_interval ;
1552 /*
1553 x_oprn_resc_rec(i).process_parameter_1 := get_rec.process_parameter_1 ;
1554 x_oprn_resc_rec(i).process_parameter_2 := get_rec.process_parameter_2 ;
1555 x_oprn_resc_rec(i).process_parameter_3 := get_rec.process_parameter_3 ;
1556 x_oprn_resc_rec(i).process_parameter_4 := get_rec.process_parameter_4 ;
1557 x_oprn_resc_rec(i).process_parameter_5 := get_rec.process_parameter_5 ;
1558 */
1559 x_oprn_resc_rec(i).recipe_override := get_rec.recipe_override;
1560 x_oprn_resc_rec(i).text_code := get_rec.text_code ;
1561 x_oprn_resc_rec(i).last_updated_by := get_rec.last_updated_by ;
1562 x_oprn_resc_rec(i).created_by := get_rec.created_by ;
1563 x_oprn_resc_rec(i).last_update_date := get_rec.last_update_date ;
1564 x_oprn_resc_rec(i).creation_date := get_rec.creation_date ;
1565 x_oprn_resc_rec(i).last_update_login := get_rec.last_update_login;
1566 x_oprn_resc_rec(i).attribute_category := get_rec.attribute_category;
1567 x_oprn_resc_rec(i).attribute1 := get_rec.attribute1;
1568 x_oprn_resc_rec(i).attribute2 := get_rec.attribute2;
1569 x_oprn_resc_rec(i).attribute3 := get_rec.attribute3;
1570 x_oprn_resc_rec(i).attribute4 := get_rec.attribute4;
1571 x_oprn_resc_rec(i).attribute5 := get_rec.attribute5;
1572 x_oprn_resc_rec(i).attribute6 := get_rec.attribute6;
1573 x_oprn_resc_rec(i).attribute7 := get_rec.attribute7;
1574 x_oprn_resc_rec(i).attribute8 := get_rec.attribute8;
1575 x_oprn_resc_rec(i).attribute9 := get_rec.attribute9;
1576 x_oprn_resc_rec(i).attribute10 := get_rec.attribute10;
1577 x_oprn_resc_rec(i).attribute11 := get_rec.attribute11;
1578 x_oprn_resc_rec(i).attribute12 := get_rec.attribute12;
1579 x_oprn_resc_rec(i).attribute13 := get_rec.attribute13;
1580 x_oprn_resc_rec(i).attribute14 := get_rec.attribute14;
1581 x_oprn_resc_rec(i).attribute15 := get_rec.attribute15;
1582 x_oprn_resc_rec(i).attribute16 := get_rec.attribute16;
1583 x_oprn_resc_rec(i).attribute17 := get_rec.attribute17;
1584 x_oprn_resc_rec(i).attribute18 := get_rec.attribute18;
1585 x_oprn_resc_rec(i).attribute19 := get_rec.attribute19;
1586 x_oprn_resc_rec(i).attribute20 := get_rec.attribute20;
1587 x_oprn_resc_rec(i).attribute21 := get_rec.attribute21;
1588 x_oprn_resc_rec(i).attribute22 := get_rec.attribute22;
1589 x_oprn_resc_rec(i).attribute23 := get_rec.attribute23;
1590 x_oprn_resc_rec(i).attribute24 := get_rec.attribute24;
1591 x_oprn_resc_rec(i).attribute25 := get_rec.attribute25;
1592 x_oprn_resc_rec(i).attribute26 := get_rec.attribute26;
1593 x_oprn_resc_rec(i).attribute27 := get_rec.attribute27;
1594 x_oprn_resc_rec(i).attribute28 := get_rec.attribute28;
1595 x_oprn_resc_rec(i).attribute29 := get_rec.attribute29;
1596 x_oprn_resc_rec(i).attribute30 := get_rec.attribute30;
1597
1598
1599 END LOOP;
1600
1601 IF i = 0 THEN
1602 RAISE fnd_api.g_exc_error;
1603 END IF; -- end if recipe_id not found
1604
1605 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
1606 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1607
1608 EXCEPTION
1609 WHEN FND_API.G_EXC_ERROR THEN
1610 X_return_code := SQLCODE;
1611 x_return_status := FND_API.G_RET_STS_ERROR;
1612 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1613
1614 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1615 X_return_code := SQLCODE;
1616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1617 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1618
1619 WHEN OTHERS THEN
1620 X_return_code := SQLCODE;
1621 x_return_status := FND_API.G_RET_STS_ERROR;
1622 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
1623
1624 END get_oprn_resc_detl;
1625
1626
1627 PROCEDURE get_recipe_process_param_detl
1628 ( p_api_version IN NUMBER ,
1629 p_init_msg_list IN VARCHAR2 ,
1630 p_recipe_id IN NUMBER ,
1631 p_organization_id IN NUMBER ,
1632 x_return_status OUT NOCOPY VARCHAR2 ,
1633 x_msg_count OUT NOCOPY NUMBER ,
1634 x_msg_data OUT NOCOPY VARCHAR2 ,
1635 X_recp_resc_proc_param_tbl OUT NOCOPY recp_resc_proc_param_tbl
1636 ) IS
1637
1638 CURSOR Cur_get_recp_rsrc IS
1639 SELECT dtl.routingstep_id, dtl.routingstep_no, act.oprn_line_id, res.resources
1640 FROM gmd_recipes_b rcp, fm_rout_dtl dtl,
1641 gmd_operation_activities act , gmd_operation_resources res
1642 WHERE rcp.recipe_id = p_recipe_id
1643 AND dtl.routing_id = rcp.routing_id
1644 AND dtl.oprn_id = act.oprn_id
1645 AND act.oprn_line_id = res.oprn_line_id;
1646
1647
1648 /* Parameters at the generic resource level */
1649 CURSOR Cur_get_gen_rsrc (V_resources VARCHAR2) IS
1650 SELECT p.parameter_id, parameter_name, parameter_description,
1651 units, r.target_value, r.minimum_value, r.maximum_value,p.parameter_type,r.sequence_no,
1652 r.created_by, r.creation_date, r.last_updated_by, r.last_update_date, r.last_update_login
1653 FROM gmp_resource_parameters r, gmp_process_parameters p
1654 WHERE p.parameter_id = r.parameter_id
1655 AND r.resources = V_resources
1656 ORDER BY r.sequence_no;
1657
1658 /* Parameters at the recipe resource level */
1659 CURSOR Cur_get_oprn_rsrc (V_oprn_line_id NUMBER,
1660 V_resources VARCHAR2, V_parameter_id NUMBER) IS
1661 SELECT *
1662 FROM gmd_oprn_process_parameters
1663 WHERE oprn_line_id = V_oprn_line_id
1664 AND resources = V_resources
1665 AND parameter_id = V_parameter_id;
1666
1667 l_oprn_rec Cur_get_oprn_rsrc%ROWTYPE;
1668
1669 /* Parameters at the operation resource level */
1670 CURSOR Cur_get_rcp_rsrc (V_routingstep_id NUMBER, V_oprn_line_id NUMBER,
1671 V_resources VARCHAR2, V_parameter_id NUMBER) IS
1672 SELECT *
1673 FROM gmd_recipe_process_parameters
1674 WHERE recipe_id = p_recipe_id
1675 AND organization_id = p_organization_id
1676 AND routingstep_id = V_routingstep_id
1677 AND oprn_line_id = V_oprn_line_id
1678 AND resources = V_resources
1679 AND parameter_id = V_parameter_id;
1680
1681 l_rcp_rec Cur_get_rcp_rsrc%ROWTYPE;
1682
1683 /* Parameters at the plant resource level */
1684 CURSOR Cur_get_plnt_rsrc (V_resources VARCHAR2, V_parameter_id NUMBER) IS
1685 SELECT p.*
1686 FROM gmp_plant_rsrc_parameters p, cr_rsrc_dtl c
1687 WHERE p.resource_id = c.resource_id
1688 AND organization_id = p_organization_id
1689 AND resources = V_resources
1690 AND parameter_id = V_parameter_id;
1691
1692 l_plnt_rec Cur_get_plnt_rsrc%ROWTYPE;
1693
1694 X_row NUMBER DEFAULT 0;
1695 X_found NUMBER(5) DEFAULT 0;
1696 X_override NUMBER(5) DEFAULT 0;
1697 X_target_value gmd_recipe_process_parameters.target_value%type ;
1698 X_minimum_value NUMBER ;
1699 X_maximum_value NUMBER ;
1700 X_created_by gmd_recipe_process_parameters.created_by%type ;
1701 X_last_updated_by gmd_recipe_process_parameters.last_updated_by%type ;
1702 X_last_update_date gmd_recipe_process_parameters.last_update_date%type ;
1703 X_creation_date gmd_recipe_process_parameters.creation_date%type ;
1704 X_last_update_login gmd_recipe_process_parameters.last_update_login%type ;
1705 BEGIN
1706 FOR l_rcp_res_rec IN Cur_get_recp_rsrc LOOP
1707 FOR l_rec IN Cur_get_gen_rsrc (l_rcp_res_rec.resources) LOOP
1708
1709 X_target_value := l_rec.target_value;
1710 X_minimum_value := l_rec.minimum_value;
1711 X_maximum_value := l_rec.maximum_value;
1712 X_created_by := l_rec.created_by;
1713 X_last_updated_by := l_rec.last_updated_by;
1714 X_creation_date := l_rec.creation_date;
1715 X_last_update_date := l_rec.last_update_date;
1716 X_last_update_login := l_rec.last_update_login;
1717
1718 /* Now let us check for overrides at recipe level */
1719 IF p_organization_id IS NOT NULL THEN
1720 OPEN Cur_get_rcp_rsrc (l_rcp_res_rec.routingstep_id, l_rcp_res_rec.oprn_line_id,
1721 l_rcp_res_rec.resources, l_rec.parameter_id);
1722 FETCH Cur_get_rcp_rsrc INTO l_rcp_rec;
1723 IF Cur_get_rcp_rsrc%FOUND THEN
1724 X_found := 1;
1725 X_override := 1;
1726 X_target_value := l_rcp_rec.target_value;
1727 X_minimum_value := l_rcp_rec.minimum_value;
1728 X_maximum_value := l_rcp_rec.maximum_value;
1729 X_created_by := l_rcp_rec.created_by;
1730 X_last_updated_by := l_rcp_rec.last_updated_by;
1731 X_creation_date := l_rcp_rec.creation_date;
1732 X_last_update_date := l_rcp_rec.last_update_date;
1733 X_last_update_login := l_rcp_rec.last_update_login;
1734 END IF;
1735 CLOSE Cur_get_rcp_rsrc;
1736 END IF; /* IF p_orgn_code IS NOT NULL */
1737
1738 /* Now let us check for overrides at operation level */
1739 IF X_found = 0 THEN
1740 OPEN Cur_get_oprn_rsrc (l_rcp_res_rec.oprn_line_id,
1741 l_rcp_res_rec.resources, l_rec.parameter_id);
1742 FETCH Cur_get_oprn_rsrc INTO l_oprn_rec;
1743 IF Cur_get_oprn_rsrc%FOUND THEN
1744 X_found := 1;
1745 X_target_value := l_oprn_rec.target_value;
1746 X_minimum_value := l_oprn_rec.minimum_value;
1747 X_maximum_value := l_oprn_rec.maximum_value;
1748 X_created_by := l_oprn_rec.created_by;
1749 X_last_updated_by := l_oprn_rec.last_updated_by;
1750 X_creation_date := l_oprn_rec.creation_date;
1751 X_last_update_date := l_oprn_rec.last_update_date;
1752 X_last_update_login := l_oprn_rec.last_update_login;
1753 END IF;
1754 CLOSE Cur_get_oprn_rsrc;
1755 END IF; /* IF X_found = 0 */
1756
1757 /* Now let us check for overrides at plant resource level */
1758 IF X_found = 0 AND
1759 p_organization_id IS NOT NULL THEN
1760 OPEN Cur_get_plnt_rsrc (l_rcp_res_rec.resources, l_rec.parameter_id);
1761 FETCH Cur_get_plnt_rsrc INTO l_plnt_rec;
1762 IF Cur_get_plnt_rsrc%FOUND THEN
1763 X_found := 1;
1764 X_target_value := l_plnt_rec.target_value;
1765 X_minimum_value := l_plnt_rec.minimum_value;
1766 X_maximum_value := l_plnt_rec.maximum_value;
1767 X_created_by := l_plnt_rec.created_by;
1768 X_last_updated_by := l_plnt_rec.last_updated_by;
1769 X_creation_date := l_plnt_rec.creation_date;
1770 X_last_update_date := l_plnt_rec.last_update_date;
1771 X_last_update_login := l_plnt_rec.last_update_login;
1772 END IF;
1773 CLOSE Cur_get_plnt_rsrc;
1774 END IF; /* IF X_found = 0 */
1775
1776 X_row := X_row + 1;
1777
1778 X_recp_resc_proc_param_tbl(X_row).recipe_id := p_recipe_id;
1779 X_recp_resc_proc_param_tbl(X_row).routingstep_id := l_rcp_res_rec.routingstep_id;
1780 X_recp_resc_proc_param_tbl(X_row).routingstep_no := l_rcp_res_rec.routingstep_no;
1781 X_recp_resc_proc_param_tbl(X_row).oprn_line_id := l_rcp_res_rec.oprn_line_id;
1782 X_recp_resc_proc_param_tbl(X_row).resources := l_rcp_res_rec.resources;
1783 X_recp_resc_proc_param_tbl(X_row).parameter_id := l_rec.parameter_id;
1784 X_recp_resc_proc_param_tbl(X_row).parameter_name := l_rec.parameter_name;
1785 X_recp_resc_proc_param_tbl(X_row).parameter_description := l_rec.parameter_description;
1786 X_recp_resc_proc_param_tbl(X_row).units := l_rec.units;
1787 X_recp_resc_proc_param_tbl(X_row).target_value := X_target_value;
1788 X_recp_resc_proc_param_tbl(X_row).minimum_value := X_minimum_value;
1789 X_recp_resc_proc_param_tbl(X_row).maximum_value := X_maximum_value;
1790 X_recp_resc_proc_param_tbl(X_row).parameter_type := l_rec.parameter_type;
1791 X_recp_resc_proc_param_tbl(X_row).sequence_no := l_rec.sequence_no;
1792 X_recp_resc_proc_param_tbl(X_row).created_by := X_created_by;
1793 X_recp_resc_proc_param_tbl(X_row).creation_date := X_creation_date;
1794 X_recp_resc_proc_param_tbl(X_row).last_updated_by := X_last_updated_by;
1795 X_recp_resc_proc_param_tbl(X_row).last_update_date := X_last_update_date;
1796 X_recp_resc_proc_param_tbl(X_row).last_update_login := X_last_update_login;
1797 X_recp_resc_proc_param_tbl(X_row).recipe_override := X_override;
1798 X_found := 0;
1799 END LOOP; /* FOR l_rec IN Cur_get_gen_rsrc */
1800 END LOOP; /* FOR l_rcp_res_rec IN Cur_get_recp_rsrc */
1801
1802 END get_recipe_process_param_detl;
1803
1804 /*======================================================================
1805 -- PROCEDURE :
1806 -- get_proc_param_desc
1807 --
1808 -- DESCRIPTION:
1809 -- This PL/SQL procedure is responsible for getting the
1810 -- description for a given process parameter.
1811 --
1812 -- REQUIREMENTS
1813 --
1814 -- SYNOPSIS:
1815 -- get_routing_no (100, x_parameter_desc);
1816 --
1817 --===================================================================== */
1818 PROCEDURE get_proc_param_desc(p_parameter_id IN NUMBER, x_parameter_desc OUT NOCOPY VARCHAR2) IS
1819 CURSOR get_proc_param_desc IS
1820 SELECT parameter_description
1821 FROM gmp_process_parameters_tl
1822 WHERE parameter_id = p_parameter_id
1823 AND language = USERENV('LANG');
1824 BEGIN
1825 OPEN get_proc_param_desc;
1826 FETCH get_proc_param_desc INTO x_parameter_desc;
1827 CLOSE get_proc_param_desc;
1828 END get_proc_param_desc;
1829
1830 /*======================================================================
1831 -- PROCEDURE :
1832 -- get_proc_param_units
1833 --
1834 -- DESCRIPTION:
1835 -- This PL/SQL procedure is responsible for getting the
1836 -- units for a given process parameter.
1837 --
1838 -- REQUIREMENTS
1839 --
1840 -- SYNOPSIS:
1841 -- get_proc_param_units (100, X_units);
1842 --
1843 --===================================================================== */
1844 PROCEDURE get_proc_param_units(p_parameter_id IN NUMBER, x_units OUT NOCOPY VARCHAR2) IS
1845 CURSOR get_proc_param_units IS
1846 SELECT units
1847 FROM gmp_process_parameters_b
1848 WHERE parameter_id = p_parameter_id;
1849 BEGIN
1850 OPEN get_proc_param_units;
1851 FETCH get_proc_param_units INTO x_units;
1852 CLOSE get_proc_param_units;
1853 END get_proc_param_units;
1854
1855 /*======================================================================
1856 -- PROCEDURE :
1857 -- fetch_contiguous_ind
1858 --
1859 -- DESCRIPTION:
1860 -- This PL/SQL procedure is responsible for getting the contiguous indicator
1861 -- value set at Recipe - Orgn level or at the Recipe level in order based on the
1862 -- i/p parameters
1863 --
1864 -- HISTORY
1865 -- Sriram.S 21Feb2005 Contiguous Indicator ME
1866 --
1867 -- SYNOPSIS:
1868 -- fetch_contiguous_ind (p_recipe_id, p_orgn_id, p_recipe_validity_rule_id,
1869 -- x_contiguous_ind, x_return_status);
1870 --
1871 --===================================================================== */
1872
1873 PROCEDURE FETCH_CONTIGUOUS_IND (
1874 p_recipe_id IN NUMBER
1875 ,p_orgn_id IN NUMBER
1876 ,p_recipe_validity_rule_id IN NUMBER
1877 ,x_contiguous_ind OUT NOCOPY NUMBER
1878 ,x_return_status OUT NOCOPY VARCHAR2) IS
1879
1880
1881 -- Cursor to get recipe_id and organization
1882 CURSOR get_recp_orgn_id IS
1883 SELECT recipe_id, organization_id
1884 FROM gmd_recipe_validity_rules
1885 WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
1886
1887 -- Cursor to fetch contiguous indicator at recp-orgn level
1888 CURSOR get_recp_orgn_cont_ind( l_recp_id NUMBER, l_orgn_id NUMBER) IS
1889 SELECT contiguous_ind
1890 FROM gmd_recipe_process_loss
1891 WHERE recipe_id = l_recp_id
1892 AND organization_id = l_orgn_id;
1893
1894 -- Cursor to fetch contiguous indicator at recipe level
1895 CURSOR get_recp_cont_ind( l_recp_id NUMBER) IS
1896 SELECT contiguous_ind
1897 FROM gmd_recipes_b
1898 WHERE recipe_id = l_recp_id;
1899
1900 l_recipe_id NUMBER;
1901 l_orgn_id NUMBER;
1902 l_cont_ind NUMBER;
1903
1904 INVALID_DATA EXCEPTION;
1905
1906 BEGIN
1907
1908 /* Set return status to success initially */
1909 x_return_status := FND_API.G_RET_STS_SUCCESS;
1910
1911 /* Chk. whether the recipe id or the validity rule id is passed */
1912 IF (p_recipe_id IS NULL AND p_recipe_validity_rule_id IS NULL) THEN
1913 RAISE INVALID_DATA;
1914 END IF;
1915
1916 -- If Validity Rule id is passed, fetch the corresponding recipe_id and orgn_id
1917 IF p_recipe_validity_rule_id IS NOT NULL THEN
1918 OPEN get_recp_orgn_id;
1919 FETCH get_recp_orgn_id INTO l_recipe_id, l_orgn_id;
1920 IF get_recp_orgn_id%NOTFOUND THEN
1921 CLOSE get_recp_orgn_id;
1922 RAISE INVALID_DATA;
1923 END IF;
1924 CLOSE get_recp_orgn_id;
1925
1926 -- If l_orgn_id is NULL (Global Validity rule) and if p_orgn_id is passed
1927 -- then use p_orgn_id to retrieve contiguous ind.
1928 IF (l_orgn_id IS NULL AND p_orgn_id IS NOT NULL) THEN
1929 l_orgn_id := p_orgn_id;
1930 END IF;
1931 ELSE
1932 -- If Validity Rule id is not passed, use the recipe and orgn id i/p parameters
1933 l_recipe_id := p_recipe_id;
1934 l_orgn_id := p_orgn_id;
1935 END IF;
1936
1937 -- Verify that recipe id is NOT NULL
1938 IF (l_recipe_id IS NULL) THEN
1939 RAISE INVALID_DATA;
1940 END IF;
1941
1942 IF (l_recipe_id IS NOT NULL AND l_orgn_id IS NOT NULL) THEN
1943 -- Try to fetch the contiguous ind set at the recipe - orgn level
1944 OPEN get_recp_orgn_cont_ind(l_recipe_id, l_orgn_id);
1945 FETCH get_recp_orgn_cont_ind INTO l_cont_ind;
1946 CLOSE get_recp_orgn_cont_ind;
1947 END IF;
1948
1949 IF (l_cont_ind IS NULL) THEN
1950 -- Cont Ind. value was not found at recipe-orgn level. Try fetching at recipe level.
1951 OPEN get_recp_cont_ind(l_recipe_id);
1952 FETCH get_recp_cont_ind INTO l_cont_ind;
1953 CLOSE get_recp_cont_ind;
1954 END IF;
1955
1956 IF (l_cont_ind IS NULL) THEN
1957 -- Cont Ind. value was not found at recipe-orgn level and recipe levels.
1958 x_contiguous_ind := 0;
1959 ELSE
1960 -- Assign cont ind. to the OUT parameter
1961 x_contiguous_ind := l_cont_ind;
1962 END IF;
1963
1964 EXCEPTION
1965
1966 WHEN INVALID_DATA THEN
1967 x_contiguous_ind := NULL;
1968 fnd_message.set_name ('GMI', 'GMI_MISSING');
1969 fnd_message.set_token ('MISSING', 'RECIPE_ID');
1970 fnd_msg_pub.add;
1971 x_return_status := FND_API.G_RET_STS_ERROR;
1972
1973 END FETCH_CONTIGUOUS_IND;
1974
1975 /*======================================================================
1976 -- PROCEDURE :
1977 -- fetch_enhanced_pi_ind
1978 --
1979 -- DESCRIPTION:
1980 -- This PL/SQL procedure is responsible for getting the Enhanced PI Indicator
1981 -- value set at Recipe header level
1982 --
1983 -- HISTORY
1984 -- Sriram.S 03MayFeb2005 GMD-GMO Integration
1985 --
1986 -- SYNOPSIS:
1987 -- fetch_enhanced_pi_ind (p_recipe_id, p_recipe_validity_rule_id,
1988 -- x_enhanced_pi_ind, x_return_status);
1989 --
1990 --===================================================================== */
1991
1992 PROCEDURE FETCH_ENHANCED_PI_IND (
1993 p_recipe_id IN NUMBER
1994 ,p_recipe_validity_rule_id IN NUMBER
1995 ,x_enhanced_pi_ind OUT NOCOPY VARCHAR2
1996 ,x_return_status OUT NOCOPY VARCHAR2) IS
1997
1998 -- Cursor to get recipe_id from validity_rule_id
1999 CURSOR get_recp_id IS
2000 SELECT recipe_id
2001 FROM gmd_recipe_validity_rules
2002 WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
2003
2004 -- Cursor to fetch enhanced PI flag at recipe level
2005 CURSOR get_pi_flag( l_recp_id NUMBER) IS
2006 SELECT enhanced_pi_ind
2007 FROM gmd_recipes_b
2008 WHERE recipe_id = l_recp_id;
2009
2010 l_recipe_id NUMBER;
2011 l_pi_ind VARCHAR2(1);
2012
2013 INVALID_DATA EXCEPTION;
2014
2015 BEGIN
2016
2017 /* Set return status to success initially */
2018 x_return_status := FND_API.G_RET_STS_SUCCESS;
2019
2020 /* Chk. whether the recipe id or the validity rule id is passed */
2021 IF (p_recipe_id IS NULL AND p_recipe_validity_rule_id IS NULL) THEN
2022 RAISE INVALID_DATA;
2023 END IF;
2024
2025 -- If Validity Rule id is passed, fetch the corresponding recipe_id
2026 IF p_recipe_validity_rule_id IS NOT NULL THEN
2027 OPEN get_recp_id;
2028 FETCH get_recp_id INTO l_recipe_id;
2029 CLOSE get_recp_id;
2030 ELSE
2031 -- If Validity Rule id is not passed, use the recipe id i/p parameter
2032 l_recipe_id := p_recipe_id;
2033 END IF;
2034
2035 -- Verify that recipe id is NOT NULL
2036 IF (l_recipe_id IS NULL) THEN
2037 RAISE INVALID_DATA;
2038 END IF;
2039
2040 -- Get the PI flag for the recipe id
2041 OPEN get_pi_flag(l_recipe_id);
2042 FETCH get_pi_flag INTO l_pi_ind;
2043 CLOSE get_pi_flag;
2044
2045 IF (l_pi_ind IS NULL) THEN
2046 x_enhanced_pi_ind := 'N';
2047 ELSE
2048 -- Assign PI indicator value to the OUT parameter
2049 x_enhanced_pi_ind := l_pi_ind;
2050 END IF;
2051
2052 EXCEPTION
2053
2054 WHEN INVALID_DATA THEN
2055 x_enhanced_pi_ind := NULL;
2056 fnd_message.set_name ('GMI', 'GMI_MISSING');
2057 fnd_message.set_token ('MISSING', 'RECIPE_ID');
2058 fnd_msg_pub.add;
2059 x_return_status := FND_API.G_RET_STS_ERROR;
2060
2061 END FETCH_ENHANCED_PI_IND;
2062
2063 END GMD_RECIPE_FETCH_PUB ;