1 PACKAGE BODY GMD_RECIPE_DETAIL AS
2 /* $Header: GMDPRCDB.pls 120.6.12010000.2 2008/11/12 18:25:50 rnalla ship $ */
3
4 /* Define any variable specific to this package */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_DETAIL' ;
6
7 /* ============================================= */
8 /* Procedure: */
9 /* Create_Recipe_Process_loss */
10 /* */
11 /* DESCRIPTION: */
12 /* This PL/SQL procedure is responsible for */
13 /* inserting a recipe */
14 /* ============================================= */
15 /* Start of commments */
16 /* API name : Create_Recipe_Process_loss */
17 /* Type : Public */
18 /* Function : */
19 /* parameters : */
20 /* IN : p_api_version IN NUMBER Required */
21 /* p_init_msg_list IN Varchar2 Optional */
22 /* p_commit IN Varchar2 Optional */
23 /* p_recipe_tbl IN Required */
24 /* */
25 /* OUT x_return_status OUT NOCOPY varchar2(1) */
26 /* x_msg_count OUT NOCOPY Number */
27 /* x_msg_data OUT NOCOPY varchar2(2000) */
28 /* */
29 /* Version : Current Version 1.0 */
30 /* */
31 /* Notes : p_called_from_forms parameter not currently used */
32 /* originally included for returning error messages */
33 /* */
34 /* End of comments */
35
36 PROCEDURE CREATE_RECIPE_PROCESS_LOSS
37 ( p_api_version IN NUMBER
38 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
39 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
40 ,p_called_from_forms IN VARCHAR2 := 'NO'
41 ,x_return_status OUT NOCOPY VARCHAR2
42 ,x_msg_count OUT NOCOPY NUMBER
43 ,x_msg_data OUT NOCOPY VARCHAR2
44 ,p_recipe_detail_tbl IN recipe_detail_tbl
45 ) IS
46 /* Defining all local variables */
47 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_PROCESS_LOSS';
48 l_api_version CONSTANT NUMBER := 1.0;
49
50 l_user_id fnd_user.user_id%TYPE := 0;
51 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
52
53 /* Variables used for defining status */
54 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
55 l_return_code NUMBER := 0;
56
57 /* Error message count and data */
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR2(2000);
60
61 /* Record types for data manipulation */
62 p_recipe_detail_rec recipe_dtl;
63
64 /* Define Exceptions */
65 recipe_pr_loss_ins_failure EXCEPTION;
66 setup_failure EXCEPTION;
67
68 BEGIN
69 /* Define Savepoint */
70 SAVEPOINT Insert_Recipe_Process_loss;
71
72 /* Standard Check for API compatibility */
73 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
74 p_api_version ,
75 l_api_name ,
76 G_PKG_NAME )
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80
81 /* Initialize message list if p_init_msg_list is set to TRUE */
82 IF FND_API.to_Boolean( p_init_msg_list ) THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85
86 IF (p_recipe_detail_tbl.Count = 0) THEN
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89
90 /* Intialize the setup fields */
91 IF NOT gmd_api_grp.setup_done THEN
92 gmd_api_grp.setup_done := gmd_api_grp.setup;
93 END IF;
94 IF NOT gmd_api_grp.setup_done THEN
95 RAISE setup_failure;
96 END IF;
97
98 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
99 /* Initialization of all status */
100 /* If a record fails in validation we store this message in error stack */
101 /* and loop thro records */
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104 /* Assign each row from the PL/SQL table to a row. */
105 p_recipe_detail_rec := p_recipe_detail_tbl(i);
106
107 /* ================================== */
108 /* Check if recipe id exists */
109 /* Either recipe_id or recipe_no/vers */
110 /* has to be provided */
111 /* ================================== */
112 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
113 GMD_RECIPE_VAL.recipe_name
114 ( p_api_version => 1.0,
115 p_init_msg_list => FND_API.G_FALSE,
116 p_commit => FND_API.G_FALSE,
117 p_recipe_no => p_recipe_detail_rec.recipe_no,
118 p_recipe_version => p_recipe_detail_rec.recipe_version,
119 x_return_status => l_return_status,
120 x_msg_count => l_msg_count,
121 x_msg_data => l_msg_data,
122 x_return_code => l_return_code,
123 x_recipe_id => l_recipe_id);
124
125 IF (l_recipe_id IS NULL) THEN
126 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXISTS');
127 FND_MSG_PUB.ADD;
128 RAISE recipe_pr_loss_ins_failure;
129 ELSE
130 p_recipe_detail_rec.recipe_id := l_recipe_id;
131 END IF;
132 END IF;
133
134 /* Validate if this Recipe can be modified by this user */
135 /* Recipe Security fix */
136 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
137 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
138 RAISE recipe_pr_loss_ins_failure;
139 END IF;
140
141 /* validate if the process loss orgn code is passed */
142 IF p_recipe_detail_rec.process_loss IS NULL THEN
143 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
144 FND_MESSAGE.SET_TOKEN ('MISSING', 'PROCESS_LOSS');
145 FND_MSG_PUB.ADD;
146 RAISE recipe_pr_loss_ins_failure;
147 END IF;
148
149 /* validate if the process loss orgn code is passed */
150 IF p_recipe_detail_rec.organization_id IS NULL THEN
151 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
152 FND_MESSAGE.SET_TOKEN ('MISSING', 'OWNER_ORGANIZATION_ID');
153 FND_MSG_PUB.ADD;
154 RAISE recipe_pr_loss_ins_failure;
155 ELSE
156 --Check the organization id passed is process enabled if not raise an error message
157 IF NOT (gmd_api_grp.check_orgn_status(p_recipe_detail_rec.organization_id)) THEN
158 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
159 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_recipe_detail_rec.organization_id);
160 FND_MSG_PUB.Add;
161 RAISE FND_API.G_EXC_ERROR;
162 END IF;
163 END IF;
164
165 /* Validate if user has access to the process loss orgn code */
166 IF NOT (GMD_API_GRP.OrgnAccessible
167 (powner_orgn_id => p_recipe_detail_rec.organization_id) ) THEN
168 RAISE recipe_pr_loss_ins_failure;
169 END IF;
170
171
172 /* Assign contiguous Ind as 0, if it not passed */
173 IF (p_recipe_detail_rec.contiguous_ind IS NULL) THEN
174 p_recipe_detail_rec.contiguous_ind := 0;
175 END IF;
176
177 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
178 GMD_RECIPE_DETAIL_PVT.create_recipe_process_loss (p_recipe_detail_rec => p_recipe_detail_rec
179 ,x_return_status => x_return_status);
180 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183 END IF;
184
185 END LOOP;
186
187 IF FND_API.To_Boolean( p_commit ) THEN
188 Commit;
189 END IF;
190
191 /* Get the message count and information */
192 FND_MSG_PUB.Count_And_Get (
193 p_count => x_msg_count,
194 p_data => x_msg_data );
195
196 EXCEPTION
197 WHEN FND_API.G_EXC_ERROR THEN
198 ROLLBACK to Insert_Recipe_Process_loss;
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 FND_MSG_PUB.Count_And_Get (
201 p_count => x_msg_count,
202 p_data => x_msg_data );
203 WHEN recipe_pr_loss_ins_failure OR setup_failure THEN
204 ROLLBACK to Insert_Recipe_Process_loss;
205 x_return_status := FND_API.G_RET_STS_ERROR;
206 fnd_msg_pub.count_and_get (
207 p_count => x_msg_count
208 ,p_encoded => FND_API.g_false
209 ,p_data => x_msg_data);
210 WHEN OTHERS THEN
211 ROLLBACK to Insert_Recipe_Process_loss;
212 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 FND_MSG_PUB.Count_And_Get (
215 p_count => x_msg_count,
216 p_data => x_msg_data );
217
218 END CREATE_RECIPE_PROCESS_LOSS;
219
220 /* ============================================= */
221 /* Procedure: */
222 /* Create_Recipe_Customers */
223 /* */
224 /* DESCRIPTION: */
225 /* This PL/SQL procedure is responsible for */
226 /* inserting a recipe */
227 /* */
228 /* ============================================= */
229 /* Start of commments */
230 /* API name : Create_Recipe_Customers */
231 /* Type : Public */
232 /* Function : */
233 /* Parameters : */
234 /* IN : p_api_version IN NUMBER Required */
235 /* p_init_msg_list IN Varchar2 Optional */
236 /* p_commit IN Varchar2 Optional */
237 /* p_recipe_tbl IN Required */
238 /* */
239 /* OUT x_return_status OUT NOCOPY varchar2(1) */
240 /* x_msg_count OUT NOCOPY Number */
241 /* x_msg_data OUT NOCOPY varchar2(2000) */
242 /* */
243 /* Version : Current Version 1.0 */
244 /* */
245 /* Notes : p_called_from_forms parameter not currently used */
246 /* originally included for returning error messages */
247 /* */
248 /* End of comments */
249
250 PROCEDURE CREATE_RECIPE_CUSTOMERS
251 (p_api_version IN NUMBER ,
252 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
253 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
254 p_called_from_forms IN VARCHAR2 := 'NO' ,
255 x_return_status OUT NOCOPY VARCHAR2 ,
256 x_msg_count OUT NOCOPY NUMBER ,
257 x_msg_data OUT NOCOPY VARCHAR2 ,
258 p_recipe_detail_tbl IN recipe_detail_tbl
259 ) IS
260 /* Defining all local variables */
261 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_CUSTOMERS';
262 l_api_version CONSTANT NUMBER := 1.0;
263
264 l_user_id fnd_user.user_id%TYPE := 0;
265 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
266 l_customer_id NUMBER := 0;
267 l_site_id NUMBER := 0;
268 l_org_id NUMBER := 0;
269
270 /* Variables used for defining status */
271 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
272 l_return_code NUMBER := 0;
273
274 /* Error message count and data */
275 l_msg_count NUMBER;
276 l_msg_data VARCHAR2(2000);
277
278 /* Record types for data manipulation */
279 p_recipe_detail_rec recipe_dtl;
280
281 setup_failure EXCEPTION;
282 Recipe_Cust_ins_failure EXCEPTION;
283 BEGIN
284 /* Define Savepoint */
285 SAVEPOINT Insert_Recipe_Customers;
286
287 /* Standard Check for API compatibility */
288 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
289 p_api_version ,
290 l_api_name ,
291 G_PKG_NAME )
292 THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295
296 /* Initialize message list if p_init_msg_list is set to TRUE */
297 IF FND_API.to_Boolean( p_init_msg_list ) THEN
298 FND_MSG_PUB.initialize;
299 END IF;
300
301 /* Intialize the setup fields */
302 IF NOT gmd_api_grp.setup_done THEN
303 gmd_api_grp.setup_done := gmd_api_grp.setup;
304 END IF;
305 IF NOT gmd_api_grp.setup_done THEN
306 RAISE setup_failure;
307 END IF;
308
309 IF (p_recipe_detail_tbl.Count = 0) THEN
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
314
315 /* Initialization of all status */
316 /* If a record fails in validation we store this message in error stack */
317 /* and loop thro records */
318 x_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 /* Assign each row from the PL/SQL table to a row. */
321 p_recipe_detail_rec := p_recipe_detail_tbl(i);
322
323 /* ================================ */
324 /* Check if recipe id exists */
325 /* ================================= */
326 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
327 GMD_RECIPE_VAL.recipe_name
328 ( p_api_version => 1.0,
329 p_init_msg_list => FND_API.G_FALSE,
330 p_commit => FND_API.G_FALSE,
331 p_recipe_no => p_recipe_detail_rec.recipe_no,
332 p_recipe_version => p_recipe_detail_rec.recipe_version,
333 x_return_status => l_return_status,
334 x_msg_count => l_msg_count,
335 x_msg_data => l_msg_data,
336 x_return_code => l_return_code,
337 x_recipe_id => l_recipe_id);
338
339 IF (l_recipe_id IS NULL) THEN
340 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
341 FND_MSG_PUB.ADD;
342 RAISE Recipe_Cust_ins_failure;
343 ELSE
344 p_recipe_detail_rec.recipe_id := l_recipe_id;
345 END IF;
346 END IF;
347
348 /* Validate if this Recipe can be modified by this user */
349 /* Recipe Security fix */
350 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
351 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
352 RAISE Recipe_Cust_ins_failure;
353 END IF;
354
355 /* ======================================= */
356 /* Based on the customer no, Check if this */
357 /* is a valid customer */
358 /* ======================================= */
359 IF (p_recipe_detail_rec.customer_id IS NULL) THEN
360 GMD_COMMON_VAL.get_customer_id
361 ( PCUSTOMER_NO => p_recipe_detail_rec.customer_no,
362 XCUST_ID => l_customer_id,
363 XSITE_ID => l_site_id,
364 XORG_ID => l_org_id,
365 XRETURN_CODE => l_return_code);
366
367 IF (l_customer_id IS NULL) THEN
368 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_CUSTOMER_INVALID');
369 FND_MSG_PUB.ADD;
370 RAISE Recipe_Cust_ins_failure;
371 ELSE
372 p_recipe_detail_rec.customer_id := l_customer_id;
373 END IF;
374 END IF;
375
376 /* ======================================= */
377 /* Based on the site_id, Check if this */
378 /* is a valid site */
379 /* ======================================= */
380 IF (p_recipe_detail_rec.site_id IS NULL) THEN
381 GMD_COMMON_VAL.get_customer_id
382 ( PCUSTOMER_NO => p_recipe_detail_rec.customer_no,
383 XCUST_ID => l_customer_id,
384 XSITE_ID => l_site_id,
385 XORG_ID => l_org_id,
386 XRETURN_CODE => l_return_code);
387
388 IF (l_site_id IS NULL) THEN
389 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_SITE_INVALID');
390 FND_MSG_PUB.ADD;
391 RAISE Recipe_Cust_ins_failure;
392 ELSE
393 p_recipe_detail_rec.site_id := l_site_id;
394 END IF;
395 END IF;
396
397 /* ======================================= */
398 /* Based on the org id, Check if this */
399 /* is a valid customer */
400 /* ======================================= */
401 IF (p_recipe_detail_rec.org_id IS NULL) THEN
402 GMD_COMMON_VAL.get_customer_id
403 ( PCUSTOMER_NO => p_recipe_detail_rec.customer_no,
404 XCUST_ID => l_customer_id,
405 XSITE_ID => l_site_id,
406 XORG_ID => l_org_id,
407 XRETURN_CODE => l_return_code);
408
409 IF (l_org_id IS NULL) THEN
410 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORG_INVALID');
411 FND_MSG_PUB.ADD;
412 RAISE Recipe_Cust_ins_failure;
413 ELSE
414 p_recipe_detail_rec.org_id := l_org_id;
415 END IF;
416 END IF;
417
418 IF (p_recipe_detail_rec.customer_id IS NULL) THEN
419 GMD_COMMON_VAL.customer_exists
420 ( p_api_version => 1.0,
421 p_init_msg_list => FND_API.G_FALSE,
422 p_commit => FND_API.G_FALSE,
423 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
424 p_customer_id => p_recipe_detail_rec.customer_id,
425 p_site_id => p_recipe_detail_rec.site_id,
426 p_org_id => p_recipe_detail_rec.org_id,
427 p_customer_no => p_recipe_detail_rec.customer_no,
428 x_return_status => l_return_status,
429 x_msg_count => l_msg_count,
430 x_msg_data => l_msg_data,
431 x_return_code => l_return_code,
432 x_customer_id => l_customer_id);
433 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_CUSTOMER_INVALID');
436 FND_MSG_PUB.ADD;
437 END IF;
438 END IF;
439
440 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
441 GMD_RECIPE_DETAIL_PVT.create_recipe_customers (p_recipe_detail_rec => p_recipe_detail_rec
442 ,x_return_status => x_return_status);
443 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
444 RAISE Recipe_Cust_ins_failure;
445 END IF;
446 END IF;
447
448 END LOOP;
449
450 IF FND_API.To_Boolean (p_commit) THEN
451 Commit;
452 END IF;
453
454 /* Get the message count and information */
455 FND_MSG_PUB.Count_And_Get (
456 p_count => x_msg_count,
457 p_data => x_msg_data );
458
459
460 EXCEPTION
461 WHEN FND_API.G_EXC_ERROR THEN
462 ROLLBACK to Insert_Recipe_Customers;
463 x_return_status := FND_API.G_RET_STS_ERROR;
464 FND_MSG_PUB.Count_And_Get (
465 p_count => x_msg_count,
466 p_data => x_msg_data );
467
468 WHEN setup_failure OR Recipe_Cust_ins_failure THEN
469 ROLLBACK to Insert_Recipe_Customers;
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 fnd_msg_pub.count_and_get (
472 p_count => x_msg_count
473 ,p_encoded => FND_API.g_false
474 ,p_data => x_msg_data);
475 WHEN OTHERS THEN
476 ROLLBACK to Insert_Recipe_Customers;
477 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 FND_MSG_PUB.Count_And_Get (
480 p_count => x_msg_count,
481 p_data => x_msg_data );
482 END CREATE_RECIPE_CUSTOMERS;
483
484 /* ============================================= */
485 /* Procedure: */
486 /* Create_Recipe_VR */
487 /* */
488 /* DESCRIPTION: */
489 /* This PL/SQL procedure is responsible for */
490 /* inserting a recipe */
491 /* ============================================= */
492 /* Start of commments */
493 /* API name : Create_Recipe_VR */
494 /* Type : Public */
495 /* Function : */
496 /* parameters : */
497 /* IN : p_api_version IN NUMBER Required */
498 /* p_init_msg_list IN Varchar2 Optional */
499 /* p_commit IN Varchar2 Optional */
500 /* p_recipe_tbl IN Required */
501 /* */
502 /* OUT x_return_status OUT NOCOPY varchar2(1) */
503 /* x_msg_count OUT NOCOPY Number */
504 /* x_msg_data OUT NOCOPY varchar2(2000) */
505 /* */
506 /* Version : Current Version 1.0 */
507 /* */
508 /* Notes : p_called_from_forms parameter not currently used */
509 /* originally included for returning error messages */
510 /* kkillams 23-03-2004 Added call to modify_status to set recipe */
511 /* status to default status if default status is*/
512 /* defined organization level w.r.t. bug 3408799*/
513 /* */
514 /* End of comments */
515
516 PROCEDURE CREATE_RECIPE_VR
517 ( p_api_version IN NUMBER
518 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
519 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
520 ,p_called_from_forms IN VARCHAR2 := 'NO'
521 ,x_return_status OUT NOCOPY VARCHAR2
522 ,x_msg_count OUT NOCOPY NUMBER
523 ,x_msg_data OUT NOCOPY VARCHAR2
524 ,p_recipe_vr_tbl IN recipe_vr_tbl
525 ,p_recipe_vr_flex IN recipe_flex
526 ) IS
527
528 /* Define all variables specific to this procedure */
529 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_VR';
530 l_api_version CONSTANT NUMBER := 1.0;
531
532 l_user_id fnd_user.user_id%TYPE := 0;
533 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
534
535 /* Variables used for defining status */
536 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
537 l_return_code NUMBER := 0;
538
539 /* Error message count and data */
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR2(2000);
542
543 /* Record types for data manipulation */
544 p_recipe_vr_rec RECIPE_VR;
545 p_recipe_vr_flex_rec FLEX;
546
547 l_def_item_id NUMBER;
548 l_std_qty NUMBER;
549 l_std_qty_um VARCHAR2(32);
550 l_prim_item_um VARCHAR2(32);
551 l_inv_min_qty NUMBER;
552 l_inv_max_qty NUMBER;
553 l_fixed_scale NUMBER;
554
555 /* Get the matl dtl for the main product */
556 Cursor get_certain_VR_defaults(vRecipe_id NUMBER) IS
557 -- NPD Conv.
558 SELECT inventory_item_id, qty, detail_uom
559 FROM fm_matl_dtl f, gmd_recipes_b r
560 WHERE f.formula_id = r.formula_id
561 AND r.recipe_id = vRecipe_id
562 AND f.line_type = 1
563 AND f.line_no = 1;
564
565 /* get the matl details for the item passed in */
566 Cursor get_specific_VR_details(vRecipe_id NUMBER, vItem_id NUMBER) IS
567 SELECT qty, detail_uom
568 FROM fm_matl_dtl f, gmd_recipes_b r
569 WHERE f.formula_id = r.formula_id
570 AND r.recipe_id = vRecipe_id
571 AND f.line_type IN (1,2)
572 AND f.inventory_item_id = vItem_id
573 AND rownum = 1;
574
575 /* Get the primary item um for the item passed in */
576 -- NPD Conv.
577 Cursor get_primary_um(vItem_id NUMBER) IS
578 SELECT primary_uom_code
579 FROM mtl_system_items
580 WHERE inventory_item_id = vItem_id;
581
582 /* Chcek VR dates against Routing dates */
583 CURSOR Get_Routing_Details(vRecipe_id NUMBER) IS
584 SELECT rt.Effective_Start_Date,
585 rt.Effective_End_Date
586 FROM gmd_routings_b rt, gmd_recipes_b rc
587 WHERE rc.routing_id = rt.routing_id AND
588 rc.recipe_id = vRecipe_id AND
589 rt.delete_mark = 0;
590
591 CURSOR check_fmhdr_fixed_scale(vRecipe_id NUMBER) IS
592 SELECT 1
593 FROM sys.dual
594 WHERE EXISTS (Select h.formula_id
595 From fm_form_mst h, gmd_recipes_b r
596 WHERE r.formula_id = h.formula_id AND
597 r.recipe_id = vRecipe_id AND
598 h.scale_type = 0);
599
600 --kkillams,bug 3408799
601 l_entity_status GMD_API_GRP.status_rec_type;
602 default_status_err EXCEPTION;
603 setup_failure EXCEPTION;
604 Recipe_VR_insert_failure EXCEPTION;
605
606 BEGIN
607 /* Define Savepoint */
608 SAVEPOINT Insert_Recipe_VR;
609
610 /* Standard Check for API compatibility */
611 IF NOT FND_API.Compatible_API_Call ( l_api_version,
612 p_api_version,
613 l_api_name ,
614 G_PKG_NAME )
615 THEN
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 END IF;
618
619 /* Initialize message list if p_init_msg_list is set to TRUE */
620 IF FND_API.to_Boolean( p_init_msg_list ) THEN
621 FND_MSG_PUB.initialize;
622 END IF;
623
624 /* Intialize the setup fields */
625 IF NOT gmd_api_grp.setup_done THEN
626 gmd_api_grp.setup_done := gmd_api_grp.setup;
627 END IF;
628 IF NOT gmd_api_grp.setup_done THEN
629 RAISE setup_failure;
630 END IF;
631
632 IF (p_recipe_vr_tbl.Count = 0) THEN
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635
636 FOR i IN 1 .. p_recipe_vr_tbl.count LOOP
637 /* Initialization of all status */
638 /* If a record fails in validation we store this message in error stack */
639 /* and loop thro records */
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 /* Assign each row from the PL/SQL table to a row. */
643 p_recipe_vr_rec := p_recipe_vr_tbl(i);
644
645 IF (p_recipe_vr_flex.count = 0) THEN
646 p_recipe_vr_flex_rec := NULL;
647 ELSE
648 p_recipe_vr_flex_rec := p_recipe_vr_flex(i);
649 END IF;
650
651 /* ================================ */
652 /* Check if recipe id exists */
653 /* ================================= */
654 IF (p_recipe_vr_rec.recipe_id IS NULL) THEN
655 GMD_RECIPE_VAL.recipe_name
656 ( p_api_version => 1.0,
657 p_init_msg_list => FND_API.G_FALSE,
658 p_commit => FND_API.G_FALSE,
659 p_recipe_no => p_recipe_vr_rec.recipe_no,
660 p_recipe_version => p_recipe_vr_rec.recipe_version,
661 x_return_status => l_return_status,
662 x_msg_count => l_msg_count,
663 x_msg_data => l_msg_data,
664 x_return_code => l_return_code,
665 x_recipe_id => l_recipe_id);
666
667 IF (l_recipe_id IS NULL) THEN
668 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
669 FND_MSG_PUB.ADD;
670 RAISE Recipe_VR_insert_failure;
671 ELSE
672 p_recipe_vr_rec.recipe_id := l_recipe_id;
673 END IF;
674 END IF;
675
676 /* Validate if this Recipe can be modified by this user */
677 /* Recipe Security fix */
678 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
679 ,Entity_id => p_recipe_vr_rec.recipe_id) THEN
680 RAISE Recipe_VR_insert_failure;
681 END IF;
682
683 /* Validate if the orgn code used for creation can be accessed
684 by user */
685 --Commented the code vr security will be based on recipe owner orgn code
686 /*IF (p_recipe_vr_rec.orgn_code IS NOT NULL) THEN
687 IF NOT (gmd_api_grp.isUserOrgnAccessible
688 (powner_id => gmd_api_grp.user_id
689 ,powner_orgn => p_recipe_vr_rec.orgn_code)) THEN
690 RAISE Recipe_VR_insert_failure;
691 END IF;
692 END IF;*/
693
694 /* Assign default values */
695 p_recipe_vr_rec.min_qty := NVL(p_recipe_vr_rec.min_qty,0);
696 p_recipe_vr_rec.max_qty := NVL(p_recipe_vr_rec.max_qty,999999);
697 p_recipe_vr_rec.preference := NVL(p_recipe_vr_rec.preference,1);
698 p_recipe_vr_rec.recipe_use := NVL(p_recipe_vr_rec.recipe_use,0);
699 p_recipe_vr_rec.start_date := NVL(p_recipe_vr_rec.start_date,sysdate);
700 p_recipe_vr_rec.validity_rule_status := '100'; -- always create VR as new
701
702 /* Get the default values for std_qty, inv_min and max_qty
703 item id and item um */
704 IF (p_recipe_vr_rec.inventory_item_id IS NULL) THEN
705 OPEN get_certain_VR_defaults(p_recipe_vr_rec.recipe_id);
706 FETCH get_certain_VR_defaults INTO l_def_item_id, l_std_qty, l_std_qty_um;
707 CLOSE get_certain_VR_defaults;
708 ELSE -- Item id is given
709 OPEN get_specific_VR_details(p_recipe_vr_rec.recipe_id,p_recipe_vr_rec.inventory_item_id);
710 FETCH get_specific_VR_details INTO l_std_qty, l_std_qty_um;
711 IF get_specific_VR_details%NOTFOUND THEN
712 CLOSE get_specific_VR_details;
713 FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_IS_PRODUCT');
714 fnd_msg_pub.add;
715 RAISE Recipe_VR_insert_failure;
716 END IF;
717 CLOSE get_specific_VR_details;
718 END IF;
719
720 -- NPD Conv.
721 p_recipe_vr_rec.inventory_item_id := NVL(p_recipe_vr_rec.inventory_item_id, l_def_item_id);
722 p_recipe_vr_rec.std_qty := NVL(p_recipe_vr_rec.std_qty, l_std_qty);
723 p_recipe_vr_rec.detail_uom := NVL(p_recipe_vr_rec.detail_uom, l_std_qty_um);
724
725 /* Get the inventory primary um for calc inv_min and max qty */
726 OPEN get_primary_um(p_recipe_vr_rec.inventory_item_id);
727 FETCH get_primary_um INTO l_prim_item_um;
728 CLOSE get_primary_um;
729
730 /* Call Recipe val pkg for getting the inv min and max qty */
731 IF ((p_recipe_vr_rec.inv_min_qty IS NULL OR p_recipe_vr_rec.inv_min_qty IS NULL)) THEN
732 GMD_RECIPE_VAL.calc_inv_qtys (P_inv_item_um => l_prim_item_um,
733 P_item_um => p_recipe_vr_rec.detail_uom,
734 P_item_id => p_recipe_vr_rec.inventory_item_id,
735 P_min_qty => p_recipe_vr_rec.min_qty,
736 P_max_qty => p_recipe_vr_rec.max_qty,
737 X_inv_min_qty => p_recipe_vr_rec.inv_min_qty,
738 X_inv_max_qty => p_recipe_vr_rec.inv_max_qty,
739 x_return_status => x_return_status) ;
740 IF (x_return_status <> 'S') THEN
741 RAISE Recipe_VR_insert_failure;
742 END IF;
743 END IF;
744
745 /* added a few validation prior to creating VRs */
746
747 /* Validate start and end dates for VR with Routiing start and end dates */
748 FOR get_routing_rec in Get_Routing_Details(p_recipe_vr_rec.recipe_id) LOOP
749 -- Get the routing start date if applicable
750 GMD_RECIPE_VAL.validate_start_date
751 (P_disp_start_date => p_recipe_vr_rec.start_date,
752 P_routing_start_date => get_routing_rec.effective_start_date,
753 x_return_status => x_return_status);
754 IF (x_return_status <> 'S') THEN
755 RAISE Recipe_VR_insert_failure;
756 END IF;
757
758 GMD_RECIPE_VAL.validate_end_date
759 (P_end_date => p_recipe_vr_rec.end_date,
760 P_routing_end_date => get_routing_rec.effective_end_date,
761 x_return_status => x_return_status);
762
763 IF (x_return_status <> 'S') THEN
764 RAISE Recipe_VR_insert_failure;
765 END IF;
766 END LOOP;
767
768 /* If the formula header has fixed scale then set the std qty, min and max
769 qty as same */
770 OPEN check_fmhdr_fixed_scale(p_recipe_vr_rec.Recipe_id);
771 FETCH check_fmhdr_fixed_scale INTO l_fixed_scale;
772 CLOSE check_fmhdr_fixed_scale;
773
774 IF (l_fixed_scale = 1) THEN
775 p_recipe_vr_rec.min_qty := p_recipe_vr_rec.std_qty;
776 p_recipe_vr_rec.max_qty := p_recipe_vr_rec.std_qty;
777 END IF;
778
779 /* Insert into the recipe validity rules table */
780 gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := NULL;
781 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
782 GMD_RECIPE_DETAIL_PVT.create_recipe_vr (p_recipe_vr_rec => p_recipe_vr_rec
783 ,p_recipe_vr_flex_rec => p_recipe_vr_flex_rec
784 ,x_return_status => x_return_status);
785 IF x_return_status <> FND_API.g_ret_sts_success THEN
786 RAISE Recipe_VR_insert_failure;
787 END IF;
788 END IF;
789 END LOOP;
790
791 IF FND_API.To_Boolean( p_commit ) THEN
792 COMMIT;
793 --kkillams,bug 3408799
794 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
795 SAVEPOINT default_status_sp;
796 gmd_api_grp.get_status_details (V_entity_type => 'VALIDITY',
797 V_orgn_id => p_recipe_vr_rec.organization_id, --w.r.t. bug 4004501 INVCONV kkillams.
798 X_entity_status => l_entity_status);
799 --Add this code after the call to gmd_recipes_mls.insert_row.
800 IF (l_entity_status.entity_status <> 100) THEN
801 Gmd_status_pub.modify_status ( p_api_version => 1
802 , p_init_msg_list => TRUE
803 , p_entity_name => 'VALIDITY'
804 , p_entity_id => gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id
805 , p_entity_no => NULL
806 , p_entity_version => NULL
807 , p_to_status => l_entity_status.entity_status
808 , p_ignore_flag => FALSE
809 , x_message_count => x_msg_count
810 , x_message_list => x_msg_data
811 , x_return_status => X_return_status);
812 gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := NULL;
813 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
814 RAISE default_status_err;
815 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
816 END IF;--l_entity_status.entity_status
817 COMMIT;
818 END IF;
819
820 /* Get the message count and information */
821 FND_MSG_PUB.Count_And_Get (
822 p_count => x_msg_count,
823 p_data => x_msg_data );
824
825 EXCEPTION
826 WHEN FND_API.G_EXC_ERROR THEN
827 ROLLBACK to Insert_Recipe_VR;
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 FND_MSG_PUB.Count_And_Get (
830 p_count => x_msg_count,
831 p_data => x_msg_data );
832
833 WHEN setup_failure OR Recipe_VR_insert_failure THEN
834 ROLLBACK to Insert_Recipe_VR;
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 fnd_msg_pub.count_and_get (
837 p_count => x_msg_count
838 ,p_encoded => FND_API.g_false
839 ,p_data => x_msg_data);
840
841 WHEN default_status_err THEN
842 ROLLBACK TO default_status_sp;
843 x_return_status := FND_API.G_RET_STS_ERROR;
844 FND_MSG_PUB.Count_And_Get (
845 p_count => x_msg_count,
846 p_data => x_msg_data );
847
848 WHEN OTHERS THEN
849 ROLLBACK to Insert_Recipe_VR;
850 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 FND_MSG_PUB.Count_And_Get (
853 p_count => x_msg_count,
854 p_data => x_msg_data );
855
856 END CREATE_RECIPE_VR;
857
858 /* ============================================= */
859 /* Procedure: */
860 /* Create_Recipe_Mtl */
861 /* */
862 /* DESCRIPTION: */
863 /* This PL/SQL procedure is responsible for */
864 /* inserting a recipe */
865 /* */
866 /* ============================================= */
867 /* Start of commments */
868 /* API name : Create_Recipe_Mtl */
869 /* Type : Public */
870 /* Function : */
871 /* parameters : */
872 /* IN : p_api_version IN NUMBER Required */
873 /* p_init_msg_list IN Varchar2 Optional */
874 /* p_commit IN Varchar2 Optional */
875 /* p_recipe_tbl IN Required */
876 /* */
877 /* OUT x_return_status OUT NOCOPY varchar2(1) */
878 /* x_msg_count OUT NOCOPY Number */
879 /* x_msg_data OUT NOCOPY varchar2(2000) */
880 /* */
881 /* Version : Current Version 1.0 */
882 /* */
883 /* Notes : p_called_from_forms parameter not currently used */
884 /* originally included for returning error messages */
885 /* */
886 /* End of comments */
887
888 PROCEDURE CREATE_RECIPE_MTL
889 ( p_api_version IN NUMBER ,
890 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
891 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
892 p_called_from_forms IN VARCHAR2 := 'NO' ,
893 x_return_status OUT NOCOPY VARCHAR2 ,
894 x_msg_count OUT NOCOPY NUMBER ,
895 x_msg_data OUT NOCOPY VARCHAR2 ,
896 p_recipe_mtl_tbl IN recipe_mtl_tbl ,
897 p_recipe_mtl_flex IN recipe_flex
898 ) IS
899 /* Define all variables specific to this procedure */
900 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_MTL';
901 l_api_version CONSTANT NUMBER := 1.0;
902
903 l_user_id fnd_user.user_id%TYPE := 0;
904 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
905
906 /* Variables used for defining status */
907 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
908 l_return_code NUMBER := 0;
909
910 /* Error message count and data */
911 l_msg_count NUMBER;
912 l_msg_data VARCHAR2(2000);
913
914 /* Record types for data manipulation */
915 p_recipe_mtl_rec RECIPE_MATERIAL;
916 p_recipe_mtl_flex_rec FLEX;
917
918 setup_failure EXCEPTION;
919 insert_rcp_mat_failure EXCEPTION;
920 BEGIN
921 /* Define Savepoint */
922 SAVEPOINT Insert_Recipe_Materials;
923
924 /* Standard Check for API compatibility */
925 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
926 p_api_version ,
927 l_api_name ,
928 G_PKG_NAME )
929 THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932
933 /* Initialize message list if p_init_msg_list is set to TRUE */
934 IF FND_API.to_Boolean( p_init_msg_list ) THEN
935 FND_MSG_PUB.initialize;
936 END IF;
937
938 /* Intialize the setup fields */
939 IF NOT gmd_api_grp.setup_done THEN
940 gmd_api_grp.setup_done := gmd_api_grp.setup;
941 END IF;
942 IF NOT gmd_api_grp.setup_done THEN
943 RAISE setup_failure;
944 END IF;
945
946 IF (p_recipe_mtl_tbl.Count = 0) THEN
947 RAISE FND_API.G_EXC_ERROR;
948 END IF;
949
950 FOR i IN 1 .. p_recipe_mtl_tbl.count LOOP
951
952 /* Initialization of all status */
953 /* If a record fails in validation we store this message in error stack */
954 /* and loop thro records */
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956
957 /* Assign each row from the PL/SQL table to a row. */
958 p_recipe_mtl_rec := p_recipe_mtl_tbl(i);
959
960 IF (p_recipe_mtl_flex.count = 0) THEN
961 p_recipe_mtl_flex_rec := NULL;
962 ELSE
963 p_recipe_mtl_flex_rec := p_recipe_mtl_flex(i);
964 END IF;
965
966 /* ================================ */
967 /* Check if recipe id exists */
968 /* ================================= */
969 IF (p_recipe_mtl_rec.recipe_id IS NULL) THEN
970 GMD_RECIPE_VAL.recipe_name
971 ( p_api_version => 1.0,
972 p_init_msg_list => FND_API.G_FALSE,
973 p_commit => FND_API.G_FALSE,
974 p_recipe_no => p_recipe_mtl_rec.recipe_no,
975 p_recipe_version => p_recipe_mtl_rec.recipe_version,
976 x_return_status => l_return_status,
977 x_msg_count => l_msg_count,
978 x_msg_data => l_msg_data,
979 x_return_code => l_return_code,
980 x_recipe_id => l_recipe_id);
981
982 IF (l_recipe_id IS NULL) THEN
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
985 FND_MSG_PUB.ADD;
986 ELSE
987 p_recipe_mtl_rec.recipe_id := l_recipe_id;
988 END IF;
989 END IF;
990
991 /* Validate if this Recipe can be modified by this user */
992 /* Recipe Security fix */
993 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
994 ,Entity_id => p_recipe_mtl_rec.recipe_id) THEN
995 RAISE insert_rcp_mat_failure;
996 END IF;
997
998 /* ==================================== */
999 /* Routing step line must exists */
1000 /* Routing details must be provided */
1001 /* Use the validation to check if */
1002 /* the routingstep_id has been provided */
1003 /* ==================================== */
1004 IF (p_recipe_mtl_rec.routingstep_id IS NULL) THEN
1005 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1006 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
1007 FND_MSG_PUB.ADD;
1008 RAISE insert_rcp_mat_failure;
1009 END IF;
1010
1011 /* validate this routing step id */
1012 /* i.e check if this routing step exists */
1013 /* for this routing_id */
1014
1015 /* ======================================= */
1016 /* Formula line must be associated with */
1017 /* this routing */
1018 /* check if the formula line is valid and */
1019 /* exists */
1020 /* ======================================= */
1021 IF (p_recipe_mtl_rec.formulaline_id IS NULL) THEN
1022 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1023 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULALINE_ID');
1024 FND_MSG_PUB.ADD;
1025 RAISE insert_rcp_mat_failure;
1026 END IF;
1027
1028 /* Insert into the recipe materials table */
1029 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1030 GMD_RECIPE_DETAIL_PVT.create_recipe_mtl (p_recipe_mtl_rec => p_recipe_mtl_rec
1031 ,p_recipe_mtl_flex_rec => p_recipe_mtl_flex_rec
1032 ,x_return_status => x_return_status);
1033 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1034 RAISE insert_rcp_mat_failure;
1035 END IF;
1036 END IF;
1037
1038 END LOOP;
1039
1040 IF FND_API.To_Boolean( p_commit ) THEN
1041 Commit;
1042 END IF;
1043
1044 /* Get the message count and information */
1045 FND_MSG_PUB.Count_And_Get (
1046 p_count => x_msg_count,
1047 p_data => x_msg_data );
1048
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 ROLLBACK to Insert_Recipe_Materials;
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 FND_MSG_PUB.Count_And_Get (
1054 p_count => x_msg_count,
1055 p_data => x_msg_data );
1056 WHEN setup_failure OR insert_rcp_mat_failure THEN
1057 ROLLBACK to Insert_Recipe_Materials;
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 fnd_msg_pub.count_and_get (
1060 p_count => x_msg_count
1061 ,p_encoded => FND_API.g_false
1062 ,p_data => x_msg_data);
1063 WHEN OTHERS THEN
1064 ROLLBACK to Insert_Recipe_Materials;
1065 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 FND_MSG_PUB.Count_And_Get (
1068 p_count => x_msg_count,
1069 p_data => x_msg_data );
1070
1071 END CREATE_RECIPE_MTL;
1072
1073 /* ============================================= */
1074 /* Procedure: */
1075 /* Update_Recipe_Process_Loss */
1076 /* */
1077 /* DESCRIPTION: */
1078 /* This PL/SQL procedure is responsible for */
1079 /* updating recipe process loss */
1080 /* */
1081 /* ============================================= */
1082 /* Start of commments */
1083 /* API name : Update_Recipe_Process_loss */
1084 /* Type : Public */
1085 /* Function : */
1086 /* parameters : */
1087 /* IN : p_api_version IN NUMBER Required */
1088 /* p_init_msg_list IN Varchar2 Optional */
1089 /* p_commit IN Varchar2 Optional */
1090 /* p_recipe_detail_tbl IN Required */
1091 /* */
1092 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1093 /* x_msg_count OUT NOCOPY Number */
1094 /* x_msg_data OUT NOCOPY varchar2(2000) */
1095 /* */
1096 /* Version : Current Version 1.0 */
1097 /* */
1098 /* Notes : p_called_from_forms parameter not currently used */
1099 /* originally included for returning error messages */
1100 /* */
1101 /* End of comments */
1102
1103 PROCEDURE UPDATE_RECIPE_PROCESS_LOSS
1104 (p_api_version IN NUMBER ,
1105 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1106 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1107 p_called_from_forms IN VARCHAR2 := 'NO' ,
1108 x_return_status OUT NOCOPY VARCHAR2 ,
1109 x_msg_count OUT NOCOPY NUMBER ,
1110 x_msg_data OUT NOCOPY VARCHAR2 ,
1111 p_recipe_detail_tbl IN recipe_detail_tbl
1112 ) IS
1113 /* Defining all local variables */
1114 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_PROCESS_LOSS';
1115 l_api_version CONSTANT NUMBER := 1.0;
1116
1117 l_user_id fnd_user.user_id%TYPE := 0;
1118 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1119
1120 /* Variables used for defining status */
1121 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1122 l_return_code NUMBER := 0;
1123
1124 /* Error message count and data */
1125 l_msg_count NUMBER;
1126 l_msg_data VARCHAR2(2000);
1127
1128 CURSOR get_recipe_pr_details(vProcess_loss_id NUMBER) IS
1129 Select *
1130 From gmd_recipe_process_loss
1131 Where Recipe_process_loss_id = VProcess_loss_id;
1132
1133 /* Record types for data manipulation */
1134 p_recipe_pr_loss_rec gmd_recipe_process_loss%ROWTYPE;
1135
1136 p_recipe_detail_rec recipe_dtl;
1137 update_pr_loss_failure EXCEPTION;
1138 setup_failure EXCEPTION;
1139 BEGIN
1140 /* Define Savepoint */
1141 SAVEPOINT Update_Recipe_Process_loss;
1142
1143 /* Standard Check for API compatibility */
1144 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1145 p_api_version ,
1146 l_api_name ,
1147 G_PKG_NAME )
1148 THEN
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151
1152 /* Initialize message list if p_init_msg_list is set to TRUE */
1153 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1154 FND_MSG_PUB.initialize;
1155 END IF;
1156
1157 /* Intialize the setup fields */
1158 IF NOT gmd_api_grp.setup_done THEN
1159 gmd_api_grp.setup_done := gmd_api_grp.setup;
1160 END IF;
1161 IF NOT gmd_api_grp.setup_done THEN
1162 RAISE setup_failure;
1163 END IF;
1164
1165 IF (p_recipe_detail_tbl.Count = 0) THEN
1166 RAISE FND_API.G_EXC_ERROR;
1167 END IF;
1168
1169 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
1170
1171 /* Initialization of all status */
1172 /* If a record fails in validation we store this message in error stack */
1173 /* and loop thro records */
1174 x_return_status := FND_API.G_RET_STS_SUCCESS;
1175
1176 /* Assign each row from the PL/SQL table to a row. */
1177 p_recipe_detail_rec := p_recipe_detail_tbl(i);
1178
1179 /* ================================== */
1180 /* For updates we expect the surrogate */
1181 /* key to be provided */
1182 /* ================================== */
1183 IF (p_recipe_detail_rec.recipe_process_loss_id IS NULL) THEN
1184 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1185 FND_MESSAGE.SET_TOKEN ('MISSING', 'RECIPE_PROCESS_LOSS_ID');
1186 FND_MSG_PUB.ADD;
1187 RAISE update_pr_loss_failure;
1188 END IF;
1189
1190 /* ================================== */
1191 /* Check if recipe id exists */
1192 /* Either recipe_id or recipe_no/vers */
1193 /* has to be provided or process loss id */
1194 /* ================================== */
1195 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1196 OPEN get_recipe_pr_details(p_recipe_detail_rec.recipe_process_loss_id);
1197 FETCH get_recipe_pr_details INTO p_recipe_pr_loss_rec;
1198 CLOSE get_recipe_pr_details;
1199 END IF;
1200
1201 /* Assign all default values */
1202 IF (p_recipe_detail_rec.process_loss = FND_API.G_MISS_NUM) THEN
1203 p_recipe_detail_rec.process_loss := NULL;
1204 ELSIF (p_recipe_detail_rec.process_loss IS NULL) THEN
1205 p_recipe_detail_rec.process_loss := p_recipe_pr_loss_rec.process_loss;
1206 END IF;
1207 /* B6811759 */
1208 IF (p_recipe_detail_rec.fixed_process_loss = FND_API.G_MISS_NUM) THEN
1209 p_recipe_detail_rec.fixed_process_loss := NULL;
1210 ELSIF (p_recipe_detail_rec.fixed_process_loss IS NULL) THEN
1211 p_recipe_detail_rec.fixed_process_loss_uom := p_recipe_pr_loss_rec.fixed_process_loss_uom;
1212 END IF;
1213
1214 IF (p_recipe_detail_rec.fixed_process_loss = FND_API.G_MISS_CHAR) THEN
1215 p_recipe_detail_rec.fixed_process_loss := NULL;
1216 ELSIF (p_recipe_detail_rec.fixed_process_loss IS NULL) THEN
1217 p_recipe_detail_rec.fixed_process_loss_uom := p_recipe_pr_loss_rec.fixed_process_loss_uom;
1218 END IF;
1219
1220
1221
1222 /* Assign contiguous Ind as 0, if it not passed */
1223 IF (p_recipe_detail_rec.contiguous_ind IS NULL) THEN
1224 p_recipe_detail_rec.contiguous_ind := 0;
1225 END IF;
1226
1227 IF (p_recipe_detail_rec.organization_id IS NULL) THEN
1228 p_recipe_detail_rec.organization_id := p_recipe_pr_loss_rec.organization_id;
1229 END IF;
1230
1231 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1232 p_recipe_detail_rec.recipe_id := p_recipe_pr_loss_rec.recipe_id;
1233 END IF;
1234
1235 IF (p_recipe_detail_rec.text_code IS NULL) THEN
1236 p_recipe_detail_rec.text_code := p_recipe_pr_loss_rec.text_code;
1237 END IF;
1238
1239 /* Validate if this Recipe can be modified by this user */
1240 /* Recipe Security fix */
1241 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1242 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
1243 RAISE update_pr_loss_failure;
1244 END IF;
1245
1246 IF NOT GMD_API_GRP.OrgnAccessible(powner_orgn_id => p_recipe_detail_rec.organization_id) THEN
1247 RAISE update_pr_loss_failure;
1248 END IF;
1249
1250 /* Update into the recipe process loss table */
1251 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1252 GMD_RECIPE_DETAIL_PVT.update_recipe_process_loss (p_recipe_detail_rec => p_recipe_detail_rec
1253 ,x_return_status => x_return_status);
1254 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1255 RAISE update_pr_loss_failure;
1256 END IF;
1257 END IF;
1258
1259 END LOOP;
1260
1261 IF FND_API.To_Boolean( p_commit ) THEN
1262 Commit;
1263 END IF;
1264
1265 /* Get the message count and information */
1266 FND_MSG_PUB.Count_And_Get (
1267 p_count => x_msg_count,
1268 p_data => x_msg_data );
1269
1270 EXCEPTION
1271 WHEN FND_API.G_EXC_ERROR THEN
1272 ROLLBACK to Update_Recipe_Process_loss;
1273 x_return_status := FND_API.G_RET_STS_ERROR;
1274 FND_MSG_PUB.Count_And_Get (
1275 p_count => x_msg_count,
1276 p_data => x_msg_data );
1277
1278 WHEN setup_failure OR update_pr_loss_failure THEN
1279 x_return_status := FND_API.G_RET_STS_ERROR;
1280 ROLLBACK to Update_Recipe_Process_loss;
1281 fnd_msg_pub.count_and_get (
1282 p_count => x_msg_count
1283 ,p_encoded => FND_API.g_false
1284 ,p_data => x_msg_data);
1285 WHEN OTHERS THEN
1286 ROLLBACK to Update_Recipe_Process_loss;
1287 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1289 FND_MSG_PUB.Count_And_Get (
1290 p_count => x_msg_count,
1291 p_data => x_msg_data );
1292
1293 END UPDATE_RECIPE_PROCESS_LOSS;
1294
1295 /* ============================================= */
1296 /* Procedure: */
1297 /* Update_Recipe_Customers */
1298 /* */
1299 /* DESCRIPTION: */
1300 /* This PL/SQL procedure is responsible for */
1301 /* updating recipe process loss */
1302 /* */
1303 /* ============================================= */
1304 /* Start of commments */
1305 /* API name : Update_Recipe_Customers */
1306 /* Type : Public */
1307 /* Function : */
1308 /* parameters : */
1309 /* IN : p_api_version IN NUMBER Required */
1310 /* p_init_msg_list IN Varchar2 Optional */
1311 /* p_commit IN Varchar2 Optional */
1312 /* p_recipe_detail_tbl IN Required */
1313 /* */
1314 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1315 /* x_msg_count OUT NOCOPY Number */
1316 /* x_msg_data OUT NOCOPY varchar2(2000) */
1317 /* */
1318 /* Version : Current Version 1.0 */
1319 /* */
1320 /* Notes : p_called_from_forms parameter not currently used */
1321 /* originally included for returning error messages */
1322 /* */
1323 /* End of comments */
1324
1325 PROCEDURE UPDATE_RECIPE_CUSTOMERS
1326 (p_api_version IN NUMBER ,
1327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1328 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1329 p_called_from_forms IN VARCHAR2 := 'NO' ,
1330 x_return_status OUT NOCOPY VARCHAR2 ,
1331 x_msg_count OUT NOCOPY NUMBER ,
1332 x_msg_data OUT NOCOPY VARCHAR2 ,
1333 p_recipe_detail_tbl IN recipe_detail_tbl
1334 ) IS
1335 /* Defining all local variables */
1336 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_CUSTOMERS';
1337 l_api_version CONSTANT NUMBER := 1.0;
1338
1339 l_user_id fnd_user.user_id%TYPE := 0;
1340 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1341 l_customer_id NUMBER := 0;
1342 l_site_id NUMBER := 0;
1343 l_org_id NUMBER := 0;
1344
1345
1346 /* Variables used for defining status */
1347 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1348 l_return_code NUMBER := 0;
1349
1350 /* Error message count and data */
1351 l_msg_count NUMBER;
1352 l_msg_data VARCHAR2(2000);
1353
1354
1355 Cursor get_rc_text_code(rc_id NUMBER, Cust_id NUMBER) IS
1356 Select text_code
1357 from gmd_recipe_customers
1358 where recipe_id = rc_id
1359 and customer_id = cust_id;
1360
1361 /* Record types for data manipulation */
1362 p_recipe_detail_rec recipe_dtl;
1363
1364 setup_failure EXCEPTION;
1365 update_rcp_cust_failure EXCEPTION;
1366
1367 BEGIN
1368 /* Define Savepoint */
1369 SAVEPOINT Update_Recipe_Customers;
1370
1371 /* Standard Check for API compatibility */
1372 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1373 p_api_version ,
1374 l_api_name ,
1375 G_PKG_NAME )
1376 THEN
1377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1378 END IF;
1379
1380 /* Initialize message list if p_init_msg_list is set to TRUE */
1381 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1382 FND_MSG_PUB.initialize;
1383 END IF;
1384
1385 /* Intialize the setup fields */
1386 IF NOT gmd_api_grp.setup_done THEN
1387 gmd_api_grp.setup_done := gmd_api_grp.setup;
1388 END IF;
1389 IF NOT gmd_api_grp.setup_done THEN
1390 RAISE setup_failure;
1391 END IF;
1392
1393 IF (p_recipe_detail_tbl.Count = 0) THEN
1394 RAISE FND_API.G_EXC_ERROR;
1395 END IF;
1396
1397 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
1398
1399 /* Initialization of all status */
1400 /* If a record fails in validation we store this message in error stack */
1401 /* and loop thro records */
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 /* Assign each row from the PL/SQL table to a row. */
1405 p_recipe_detail_rec := p_recipe_detail_tbl(i);
1406
1407 /* ================================ */
1408 /* Check if recipe id exists */
1409 /* ================================= */
1410 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1411 GMD_RECIPE_VAL.recipe_name
1412 ( p_api_version => 1.0,
1413 p_init_msg_list => FND_API.G_FALSE,
1414 p_commit => FND_API.G_FALSE,
1415 p_recipe_no => p_recipe_detail_rec.recipe_no,
1416 p_recipe_version => p_recipe_detail_rec.recipe_version,
1417 x_return_status => l_return_status,
1418 x_msg_count => l_msg_count,
1419 x_msg_data => l_msg_data,
1420 x_return_code => l_return_code,
1421 x_recipe_id => l_recipe_id);
1422
1423 IF (l_recipe_id IS NULL) THEN
1424 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
1425 FND_MSG_PUB.ADD;
1426 RAISE update_rcp_cust_failure;
1427 ELSE
1428 p_recipe_detail_rec.recipe_id := l_recipe_id;
1429 END IF;
1430 END IF;
1431
1432 /* Validate if this Recipe can be modified by this user */
1433 /* Recipe Security fix */
1434 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1435 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
1436 RAISE update_rcp_cust_failure;
1437 END IF;
1438
1439 /* ======================================= */
1440 /* Based on the customer no, Check if this */
1441 /* is a valid customer */
1442 /* ======================================= */
1443 IF (p_recipe_detail_rec.customer_id IS NULL) THEN
1444 GMD_COMMON_VAL.get_customer_id
1445 ( PCUSTOMER_NO => p_recipe_detail_rec.customer_no,
1446 XCUST_ID => l_customer_id,
1447 XSITE_ID => l_site_id,
1448 XORG_ID => l_org_id,
1449 XRETURN_CODE => l_return_code);
1450
1451 IF (l_customer_id IS NULL) THEN
1452 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_CUSTOMER_INVALID');
1453 FND_MSG_PUB.ADD;
1454 RAISE update_rcp_cust_failure;
1455 ELSE
1456 p_recipe_detail_rec.customer_id := l_customer_id;
1457 END IF;
1458 END IF;
1459
1460 /* Only updateable field is text code */
1461 IF (p_recipe_detail_rec.text_Code IS NULL) THEN
1462 OPEN get_rc_text_code(p_recipe_detail_rec.recipe_id,
1463 p_recipe_detail_rec.customer_id);
1464 FETCH get_rc_text_code INTO p_recipe_detail_rec.text_code;
1465 CLOSE get_rc_text_code;
1466 ELSIF (p_recipe_detail_rec.text_Code = fnd_Api.g_miss_char) THEN
1467 p_recipe_detail_rec.text_code := null;
1468 END IF;
1469
1470 /* Update the recipe customer table */
1471 /* only who columns needs to be updated */
1472 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1473 GMD_RECIPE_DETAIL_PVT.update_recipe_customers (p_recipe_detail_rec => p_recipe_detail_rec
1474 ,x_return_status => x_return_status);
1475 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1476 RAISE update_rcp_cust_failure;
1477 END IF;
1478 END IF;
1479
1480 END LOOP;
1481 IF FND_API.To_Boolean( p_commit ) THEN
1482 Commit;
1483 END IF;
1484
1485 /* Get the message count and information */
1486 FND_MSG_PUB.Count_And_Get (
1487 p_count => x_msg_count,
1488 p_data => x_msg_data );
1489
1490 EXCEPTION
1491 WHEN FND_API.G_EXC_ERROR THEN
1492 ROLLBACK to Update_Recipe_Customers;
1493 x_return_status := FND_API.G_RET_STS_ERROR;
1494 FND_MSG_PUB.Count_And_Get (
1495 p_count => x_msg_count,
1496 p_data => x_msg_data );
1497
1498 WHEN setup_failure OR update_rcp_cust_failure THEN
1499 ROLLBACK to Update_Recipe_Customers;
1500 x_return_status := FND_API.G_RET_STS_ERROR;
1501 fnd_msg_pub.count_and_get (
1502 p_count => x_msg_count
1503 ,p_encoded => FND_API.g_false
1504 ,p_data => x_msg_data);
1505 WHEN OTHERS THEN
1506 ROLLBACK to Update_Recipe_Customers;
1507 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1509 FND_MSG_PUB.Count_And_Get (
1510 p_count => x_msg_count,
1511 p_data => x_msg_data );
1512
1513 END UPDATE_RECIPE_CUSTOMERS;
1514
1515 /* ============================================= */
1516 /* Procedure: */
1517 /* Update_Recipe_VR */
1518 /* */
1519 /* DESCRIPTION: */
1520 /* This PL/SQL procedure is responsible for */
1521 /* updating recipe Validity Rules */
1522 /* */
1523 /* ============================================= */
1524 /* Start of commments */
1525 /* API name : Update_Recipe_VR */
1526 /* Type : Public */
1527 /* Function : */
1528 /* parameters : */
1529 /* IN : p_api_version IN NUMBER Required */
1530 /* p_init_msg_list IN Varchar2 Optional */
1531 /* p_commit IN Varchar2 Optional */
1532 /* p_recipe_detail_tbl IN Required */
1533 /* */
1534 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1535 /* x_msg_count OUT NOCOPY Number */
1536 /* x_msg_data OUT NOCOPY varchar2(2000) */
1537 /* */
1538 /* Version : Current Version 1.0 */
1539 /* */
1540 /* Notes : p_called_from_forms parameter not currently used */
1541 /* originally included for returning error messages */
1542 /* */
1543 /* End of comments */
1544 PROCEDURE UPDATE_RECIPE_VR
1545 ( p_api_version IN NUMBER
1546 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1547 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1548 ,p_called_from_forms IN VARCHAR2 := 'NO'
1549 ,x_return_status OUT NOCOPY VARCHAR2
1550 ,x_msg_count OUT NOCOPY NUMBER
1551 ,x_msg_data OUT NOCOPY VARCHAR2
1552 ,p_recipe_vr_tbl IN recipe_vr_tbl
1553 ,p_recipe_update_flex IN recipe_update_flex
1554 ) IS
1555 /* Define all variables specific to this procedure */
1556 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_VR';
1557 l_api_version CONSTANT NUMBER := 2.0;
1558
1559 l_user_id fnd_user.user_id%TYPE;
1560 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1561
1562 /* Variables used for defining status */
1563 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1564 l_return_code NUMBER := 0;
1565 l_plant_ind NUMBER;
1566
1567 /* Error message count and data */
1568 l_msg_count NUMBER;
1569 l_msg_data VARCHAR2(2000);
1570
1571 /* Record types for data manipulation */
1572 p_recipe_vr_rec RECIPE_VR;
1573
1574 p_flex_update_rec UPDATE_FLEX;
1575 /* used for g_miss_char logic */
1576 l_flex_update_rec update_flex;
1577
1578 /* Define a cursor for dealing with updates */
1579 CURSOR Flex_cur(vRecipe_VR_id NUMBER) IS
1580 SELECT attribute_category, attribute1, attribute2, attribute3, attribute4,
1581 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
1582 attribute11, attribute12, attribute13, attribute14, attribute15,
1583 attribute16, attribute17, attribute18, attribute19, attribute20,
1584 attribute21, attribute22, attribute23, attribute24,attribute25,
1585 attribute26, attribute27, attribute28, attribute29, attribute30
1586 FROM gmd_recipe_validity_rules
1587 WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
1588
1589
1590 /* Define a cursor for dealing with updates */
1591 CURSOR update_vr_cur(vRecipe_VR_id NUMBER) IS
1592 SELECT recipe_id, orgn_code, end_date, planned_process_loss
1593 FROM gmd_recipe_validity_rules
1594 WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
1595
1596 /* Cursor to get the item id when item no is passed */
1597 CURSOR get_item_id(pItem_no VARCHAR2) IS
1598 SELECT inventory_item_id
1599 FROM mtl_system_items_kfv
1600 WHERE concatenated_segments = pItem_no;
1601 -- And delete_mark = 0;
1602
1603 Update_VR_Failure EXCEPTION;
1604 setup_failure EXCEPTION;
1605
1606 BEGIN
1607 /* Define Savepoint */
1608 SAVEPOINT Update_Recipe_VR;
1609
1610 /* Standard Check for API compatibility */
1611 IF NOT FND_API.Compatible_API_Call ( l_api_version
1612 ,p_api_version
1613 ,l_api_name
1614 ,G_PKG_NAME )
1615 THEN
1616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1617 END IF;
1618
1619 /* Initialize message list if p_init_msg_list is set to TRUE */
1620 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1621 FND_MSG_PUB.initialize;
1622 END IF;
1623
1624 /* Intialize the setup fields */
1625 IF NOT gmd_api_grp.setup_done THEN
1626 gmd_api_grp.setup_done := gmd_api_grp.setup;
1627 END IF;
1628 IF NOT gmd_api_grp.setup_done THEN
1629 RAISE setup_failure;
1630 END IF;
1631
1632 /* Initialization of all status */
1633 /* If a record fails in validation we store this message in error stack */
1634 /* and loop thro records */
1635 x_return_status := FND_API.G_RET_STS_SUCCESS;
1636
1637 FOR i IN 1 .. p_recipe_vr_tbl.count LOOP
1638 BEGIN
1639 /* Assign each row from the PL/SQL table to a row. */
1640 p_recipe_vr_rec := p_recipe_vr_tbl(i);
1641
1642 /* ======================================== */
1643 /* Send an error message if surrogate key */
1644 /* value is not provided */
1645 /* ======================================== */
1646 If (p_recipe_vr_rec.recipe_validity_rule_id IS NULL) THEN
1647 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1648 FND_MESSAGE.SET_TOKEN ('MISSING', 'RECIPE_VALIDITY_RULE_ID');
1649 FND_MSG_PUB.ADD;
1650 RAISE Update_VR_Failure;
1651 END IF;
1652
1653 /* Thomas Daniel - Bug 2652200 */
1654 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
1655 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
1656 /* as the user is intending to update the field to NULL */
1657 FOR update_rec IN update_vr_Cur(p_recipe_vr_rec.recipe_validity_rule_id) LOOP
1658 IF (p_recipe_vr_rec.orgn_code = FND_API.G_MISS_CHAR) THEN
1659 p_recipe_vr_rec.orgn_code := NULL;
1660 ELSIF (p_recipe_vr_rec.orgn_code IS NULL) THEN
1661 p_recipe_vr_rec.orgn_code := update_rec.orgn_code;
1662 END IF;
1663
1664 IF (p_recipe_vr_rec.planned_process_loss = FND_API.G_MISS_NUM) THEN
1665 p_recipe_vr_rec.planned_process_loss := NULL;
1666 ELSIF (p_recipe_vr_rec.planned_process_loss IS NULL) THEN
1667 p_recipe_vr_rec.planned_process_loss := update_rec.planned_process_loss;
1668 END IF;
1669
1670 IF (p_recipe_vr_rec.end_date = FND_API.G_MISS_DATE) THEN
1671 p_recipe_vr_rec.end_date := NULL;
1672 ELSIF (p_recipe_vr_rec.end_date IS NULL) THEN
1673 p_recipe_vr_rec.end_date := update_rec.end_date;
1674 END IF;
1675
1676 IF (p_recipe_vr_rec.recipe_id IS NULL) THEN
1677 p_recipe_vr_rec.recipe_id := update_rec.recipe_id;
1678 END IF;
1679 END LOOP;
1680
1681 /* Validate if this Recipe can be modified by this user */
1682 /* Recipe Security fix */
1683 --Commented the code vr security will be based on recipe owner orgn code
1684 /*IF NOT GMD_API_GRP.isUserOrgnAccessible(powner_id => gmd_api_grp.user_id
1685 ,powner_orgn => p_recipe_vr_rec.orgn_code) THEN
1686 RAISE Update_VR_Failure;
1687 END IF;*/
1688
1689 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1690 ,Entity_id => p_recipe_vr_rec.recipe_id) THEN
1691 RAISE Update_VR_Failure;
1692 END IF;
1693
1694 /* VR Security fix */
1695 --Commented the code vr security will be based on recipe owner orgn code
1696 /*IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'VALIDITY'
1697 ,Entity_id => p_recipe_vr_rec.recipe_validity_rule_id)
1698 THEN
1699 RAISE Update_VR_Failure;
1700 END IF;*/
1701
1702 /* ========================================= */
1703 /* Get item id if user passes in the */
1704 /* Item no */
1705 /* ========================================= */
1706 IF p_recipe_vr_rec.item_no IS NOT NULL THEN
1707 OPEN get_item_id(p_recipe_vr_rec.Item_no);
1708 FETCH get_item_id INTO p_recipe_vr_rec.inventory_item_id;
1709 IF get_item_id%NOTFOUND THEN
1710 CLOSE get_item_id;
1711 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1712 FND_MESSAGE.SET_TOKEN ('MISSING', 'ITEM_ID');
1713 FND_MSG_PUB.ADD;
1714 RAISE Update_VR_Failure;
1715 END IF;
1716 CLOSE get_item_id;
1717 END IF;
1718
1719 OPEN Flex_cur(p_recipe_vr_rec.recipe_validity_rule_id);
1720 FETCH Flex_cur INTO l_flex_update_rec;
1721 CLOSE Flex_cur;
1722
1723 /* If no flex field is updated retain the old values */
1724 IF (p_recipe_update_flex.count = 0) THEN
1725 p_flex_update_rec := l_flex_update_rec;
1726 ELSE
1727 p_flex_update_rec := p_recipe_update_flex(i);
1728 END IF;
1729
1730 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1731 p_flex_update_rec.attribute1 := NULL;
1732 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
1733 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
1734 END IF;
1735
1736 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1737 p_flex_update_rec.attribute2 := NULL;
1738 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
1739 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
1740 END IF;
1741
1742 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1743 p_flex_update_rec.attribute3 := NULL;
1744 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
1745 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
1746 END IF;
1747
1748 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1749 p_flex_update_rec.attribute4 := NULL;
1750 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
1751 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
1752 END IF;
1753
1754 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1755 p_flex_update_rec.attribute5 := NULL;
1756 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
1757 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
1758 END IF;
1759
1760 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1761 p_flex_update_rec.attribute6 := NULL;
1762 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
1763 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
1764 END IF;
1765
1766 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1767 p_flex_update_rec.attribute7 := NULL;
1768 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
1769 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
1770 END IF;
1771
1772 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1773 p_flex_update_rec.attribute8 := NULL;
1774 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
1775 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
1776 END IF;
1777
1778 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1779 p_flex_update_rec.attribute9 := NULL;
1780 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
1781 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
1782 END IF;
1783
1784 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1785 p_flex_update_rec.attribute10 := NULL;
1786 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
1787 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
1788 END IF;
1789
1790 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1791 p_flex_update_rec.attribute11 := NULL;
1792 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
1793 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
1794 END IF;
1795
1796 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1797 p_flex_update_rec.attribute12 := NULL;
1798 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
1799 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
1800 END IF;
1801
1802 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1803 p_flex_update_rec.attribute13 := NULL;
1804 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
1805 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
1806 END IF;
1807
1808 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1809 p_flex_update_rec.attribute14 := NULL;
1810 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
1811 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
1812 END IF;
1813
1814 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1815 p_flex_update_rec.attribute15 := NULL;
1816 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
1817 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
1818 END IF;
1819
1820 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1821 p_flex_update_rec.attribute16 := NULL;
1822 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
1823 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
1824 END IF;
1825
1826 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1827 p_flex_update_rec.attribute17 := NULL;
1828 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
1829 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
1830 END IF;
1831
1832 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1833 p_flex_update_rec.attribute18 := NULL;
1834 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
1835 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
1836 END IF;
1837
1838 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1839 p_flex_update_rec.attribute19 := NULL;
1840 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
1841 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
1842 END IF;
1843
1844 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1845 p_flex_update_rec.attribute20 := NULL;
1846 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
1847 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
1848 END IF;
1849
1850 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1851 p_flex_update_rec.attribute21 := NULL;
1852 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
1853 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
1854 END IF;
1855
1856 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1857 p_flex_update_rec.attribute22 := NULL;
1858 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
1859 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
1860 END IF;
1861
1862 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1863 p_flex_update_rec.attribute23 := NULL;
1864 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
1865 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
1866 END IF;
1867
1868 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1869 p_flex_update_rec.attribute24 := NULL;
1870 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
1871 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
1872 END IF;
1873
1874 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1875 p_flex_update_rec.attribute25 := NULL;
1876 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
1877 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
1878 END IF;
1879
1880 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1881 p_flex_update_rec.attribute26 := NULL;
1882 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
1883 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
1884 END IF;
1885
1886 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1887 p_flex_update_rec.attribute27 := NULL;
1888 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
1889 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
1890 END IF;
1891
1892 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1893 p_flex_update_rec.attribute28 := NULL;
1894 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
1895 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
1896 END IF;
1897
1898 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1899 p_flex_update_rec.attribute29 := NULL;
1900 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
1901 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
1902 END IF;
1903
1904 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1905 p_flex_update_rec.attribute30 := NULL;
1906 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
1907 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
1908 END IF;
1909
1910 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1911 p_flex_update_rec.attribute_category := NULL;
1912 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
1913 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
1914 END IF;
1915
1916 /* Update recipe validity rules table */
1917 GMD_RECIPE_DETAIL_PVT.UPDATE_RECIPE_VR(p_recipe_vr_rec => p_recipe_vr_rec
1918 ,p_flex_update_rec => p_flex_update_rec
1919 ,x_return_status => x_return_status);
1920 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1921 RAISE Update_VR_Failure;
1922 END IF;
1923
1924 EXCEPTION
1925 WHEN Update_VR_Failure THEN
1926 x_return_status := FND_API.G_RET_STS_ERROR;
1927 /* Get the message count and information */
1928 FND_MSG_PUB.Count_And_Get (
1929 p_count => x_msg_count
1930 ,p_data => x_msg_data );
1931 END;
1932 END LOOP; -- Loops thro all VR that needs to be updated
1933
1934 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1935 IF FND_API.To_Boolean( p_commit ) THEN
1936 Commit;
1937 END IF;
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN FND_API.G_EXC_ERROR THEN
1942 ROLLBACK to Update_Recipe_VR;
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 FND_MSG_PUB.Count_And_Get (
1945 p_count => x_msg_count,
1946 p_data => x_msg_data );
1947 WHEN setup_failure THEN
1948 ROLLBACK to Update_Recipe_VR;
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950 fnd_msg_pub.count_and_get (
1951 p_count => x_msg_count
1952 ,p_encoded => FND_API.g_false
1953 ,p_data => x_msg_data);
1954 WHEN OTHERS THEN
1955 ROLLBACK to Update_Recipe_VR;
1956 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1958 FND_MSG_PUB.Count_And_Get (
1959 p_count => x_msg_count,
1960 p_data => x_msg_data );
1961
1962 END UPDATE_RECIPE_VR;
1963
1964
1965 /* ============================================= */
1966 /* Procedure: */
1967 /* Recipe_Routing_Steps */
1968 /* */
1969 /* DESCRIPTION: */
1970 /* This PL/SQL procedure is responsible for */
1971 /* inserting and updating recipe Routing steps */
1972 /* */
1973 /* ============================================= */
1974 /* Start of commments */
1975 /* API name : Recipe_Routing_Steps */
1976 /* Type : Public */
1977 /* Function : */
1978 /* parameters : */
1979 /* p_called_from_forms parameter not currently used */
1980 /* originally included for returning error messages */
1981 /* IN : p_api_version IN NUMBER Required */
1982 /* p_init_msg_list IN Varchar2 Optional */
1983 /* p_commit IN Varchar2 Optional */
1984 /* p_recipe_detail_tbl IN Required */
1985 /* */
1986 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1987 /* x_msg_count OUT NOCOPY Number */
1988 /* x_msg_data OUT NOCOPY varchar2(2000) */
1989 /* */
1990 /* Version : Current Version 1.1 */
1991 /* */
1992 /* Notes : 24Jul2001 L.R.Jackson Added mass and volume fields. */
1993 /* Changed routing step id validation */
1994 /* Increased the version to 1.1 */
1995 /* Removed the detail record. Just use table(i) */
1996 /* Removed check of user id/user name. There is */
1997 /* no userid in this table. WHO columns are */
1998 /* passed in, not derived here. */
1999 /* Changed call to RECIPE_NAME to RECIPE_EXISTS. */
2000 /* */
2001 /* End of comments */
2002
2003 PROCEDURE RECIPE_ROUTING_STEPS
2004 ( p_api_version IN NUMBER ,
2005 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2006 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2007 p_called_from_forms IN VARCHAR2 := 'NO' ,
2008 x_return_status OUT NOCOPY VARCHAR2 ,
2009 x_msg_count OUT NOCOPY NUMBER ,
2010 x_msg_data OUT NOCOPY VARCHAR2 ,
2011 p_recipe_detail_tbl IN recipe_detail_tbl ,
2012 p_recipe_insert_flex IN recipe_flex ,
2013 p_recipe_update_flex IN recipe_update_flex
2014 ) IS
2015
2016 /* Define all variables specific to this procedure */
2017 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ROUTING_STEPS';
2018 l_api_version CONSTANT NUMBER := 2.0;
2019
2020 l_user_id fnd_user.user_id%TYPE := 0;
2021 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
2022
2023 /* Variables used for defining status */
2024 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
2025 l_return_code NUMBER := 0;
2026
2027 /* Error message count and data */
2028 l_msg_count NUMBER;
2029 l_msg_data VARCHAR2(2000);
2030
2031 /* Record types for data manipulation */
2032 p_recipe_detail_rec recipe_dtl;
2033
2034 /* flex field records for inserts and updates */
2035 p_flex_insert_rec flex;
2036 p_flex_update_rec update_flex;
2037
2038 /* used for g_miss_char logic */
2039 l_flex_update_rec update_flex;
2040
2041 /* Define a cursor for dealing with updates */
2042 CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
2043 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
2044 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
2045 attribute11, attribute12, attribute13, attribute14, attribute15,
2046 attribute16, attribute17, attribute18, attribute19, attribute20,
2047 attribute21, attribute22, attribute23, attribute24,attribute25,
2048 attribute26, attribute27, attribute28, attribute29, attribute30
2049 From gmd_recipe_routing_steps
2050 where recipe_id = NVL(vRecipe_id,-1) AND
2051 RoutingStep_id = NVL(vRoutingStep_id,-1);
2052
2053 CURSOR update_rt_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
2054 Select mass_qty, volume_qty, mass_std_uom, volume_std_uom
2055 From gmd_recipe_routing_steps
2056 where recipe_id = NVL(vRecipe_id,-1) AND
2057 RoutingStep_id = NVL(vRoutingStep_id,-1);
2058
2059 setup_failure EXCEPTION;
2060
2061 BEGIN
2062 /* Updating recipe routing step for first time is in fact inserting a new record */
2063 /* in gmd_recipe_routing_step table. [Form initially shows values from */
2064 /* fm_rout_dtl. When user "changes" values, they are saved in recipe table.] */
2065
2066 /* Define Savepoint */
2067 SAVEPOINT Recipe_Routing_Steps;
2068
2069 /* Standard Check for API compatibility */
2070 IF NOT FND_API.Compatible_API_Call
2071 ( l_api_version ,
2072 p_api_version ,
2073 l_api_name ,
2074 G_PKG_NAME )
2075 THEN
2076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2077 END IF;
2078
2079 /* Initialize message list if p_init_msg_list is set to TRUE */
2080 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2081 FND_MSG_PUB.initialize;
2082 END IF;
2083
2084 /* Intialize the setup fields */
2085 IF NOT gmd_api_grp.setup_done THEN
2086 gmd_api_grp.setup_done := gmd_api_grp.setup;
2087 END IF;
2088 IF NOT gmd_api_grp.setup_done THEN
2089 RAISE setup_failure;
2090 END IF;
2091
2092 IF (p_recipe_detail_tbl.Count = 0) THEN
2093 RAISE FND_API.G_EXC_ERROR;
2094 END IF;
2095
2096 /* Initialization of status. */
2097 /* If a record fails in validation we store the message in error stack */
2098 /* and continue to loop through records */
2099 x_return_status := FND_API.G_RET_STS_SUCCESS;
2100
2101 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
2102
2103 /* Assign each row from the PL/SQL table to a row. */
2104 p_recipe_detail_rec := p_recipe_detail_tbl(i);
2105
2106 /* ========================== */
2107 /* Check if recipe id exists */
2108 /* ========================== */
2109 GMD_RECIPE_VAL.recipe_exists
2110 ( p_api_version => 1.0,
2111 p_init_msg_list => FND_API.G_FALSE,
2112 p_commit => FND_API.G_FALSE,
2113 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
2114 p_recipe_id => p_recipe_detail_tbl(i).recipe_id,
2115 p_recipe_no => p_recipe_detail_tbl(i).recipe_no,
2116 p_recipe_version => p_recipe_detail_tbl(i).recipe_version,
2117 x_return_status => l_return_status,
2118 x_msg_count => l_msg_count,
2119 x_msg_data => l_msg_data,
2120 x_return_code => l_return_code,
2121 x_recipe_id => l_recipe_id);
2122
2123 IF (l_recipe_id IS NULL) OR x_return_status <> 'S' THEN
2124 x_return_status := FND_API.G_RET_STS_ERROR;
2125 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
2126 FND_MSG_PUB.ADD;
2127 RAISE FND_API.G_EXC_ERROR;
2128 ELSE
2129 p_recipe_detail_rec.recipe_id := l_recipe_id;
2130 END IF;
2131
2132 /* Validate if this Recipe can be modified by this user */
2133 /* Recipe Security fix */
2134 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
2135 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
2136 RAISE FND_API.G_EXC_ERROR;
2137 END IF;
2138
2139 IF (p_recipe_detail_tbl(i).routingstep_id IS NULL) THEN
2140 x_return_status := FND_API.G_RET_STS_ERROR;
2141 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
2142 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
2143 FND_MSG_PUB.ADD;
2144 END IF;
2145
2146 IF (p_recipe_insert_flex.count = 0) THEN
2147 p_flex_insert_rec := NULL;
2148 ELSE
2149 p_flex_insert_rec := p_recipe_insert_flex(i);
2150 END IF;
2151
2152 FOR update_rec IN update_rt_cur(p_recipe_detail_rec.recipe_id,
2153 p_recipe_detail_tbl(i).routingstep_id)
2154 LOOP
2155
2156 IF (p_recipe_detail_rec.mass_qty = FND_API.G_MISS_NUM) THEN
2157 p_recipe_detail_rec.mass_qty := NULL;
2158 ELSIF (p_recipe_detail_rec.mass_qty IS NULL) THEN
2159 p_recipe_detail_rec.mass_qty := update_rec.mass_qty;
2160 END IF;
2161
2162 IF (p_recipe_detail_rec.volume_qty = FND_API.G_MISS_NUM) THEN
2163 p_recipe_detail_rec.volume_qty := NULL;
2164 ELSIF (p_recipe_detail_rec.volume_qty IS NULL) THEN
2165 p_recipe_detail_rec.volume_qty := update_rec.volume_qty;
2166 END IF;
2167
2168 IF (p_recipe_detail_rec.mass_std_uom = FND_API.G_MISS_CHAR) THEN
2169 p_recipe_detail_rec.mass_std_uom := NULL;
2170 ELSIF (p_recipe_detail_rec.mass_std_uom IS NULL) THEN
2171 p_recipe_detail_rec.mass_std_uom := update_rec.mass_std_uom;
2172 END IF;
2173
2174 IF (p_recipe_detail_rec.volume_std_uom = FND_API.G_MISS_CHAR) THEN
2175 p_recipe_detail_rec.volume_std_uom := NULL;
2176 ELSIF (p_recipe_detail_rec.volume_std_uom IS NULL) THEN
2177 p_recipe_detail_rec.volume_std_uom := update_rec.volume_std_uom;
2178 END IF;
2179
2180 END LOOP;
2181
2182 /* Assign flex fields */
2183 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,p_recipe_detail_tbl(i).routingstep_id);
2184 FETCH Flex_cur INTO l_flex_update_rec;
2185 IF Flex_cur%FOUND THEN
2186 /* If no flex field is updated retain the old values */
2187 IF (p_recipe_update_flex.count = 0) THEN
2188 p_flex_update_rec := l_flex_update_rec;
2189 ELSE
2190 p_flex_update_rec := p_recipe_update_flex(i);
2191
2192 /* ================================ */
2193 /* Check for all G_MISS_CHAR values */
2194 /* for nullable fields in */
2195 /* gmd_recipe_routing_steps table */
2196 /* ================================= */
2197
2198 /* Thomas Daniel - Bug 2652200 */
2199 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
2200 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
2201 /* as the user is intending to update the field to NULL */
2202 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
2203 p_flex_update_rec.attribute1 := NULL;
2204 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
2205 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
2206 END IF;
2207
2208 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
2209 p_flex_update_rec.attribute2 := NULL;
2210 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
2211 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
2212 END IF;
2213
2214 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
2215 p_flex_update_rec.attribute3 := NULL;
2216 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
2217 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
2218 END IF;
2219
2220 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
2221 p_flex_update_rec.attribute4 := NULL;
2222 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
2223 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
2224 END IF;
2225
2226 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
2227 p_flex_update_rec.attribute5 := NULL;
2228 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
2229 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
2230 END IF;
2231
2232 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
2233 p_flex_update_rec.attribute6 := NULL;
2234 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
2235 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
2236 END IF;
2237
2238 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
2239 p_flex_update_rec.attribute7 := NULL;
2240 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
2241 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
2242 END IF;
2243
2244 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
2245 p_flex_update_rec.attribute8 := NULL;
2246 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
2247 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
2248 END IF;
2249
2250 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
2251 p_flex_update_rec.attribute9 := NULL;
2252 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
2253 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
2254 END IF;
2255
2256 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
2257 p_flex_update_rec.attribute10 := NULL;
2258 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
2259 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
2260 END IF;
2261
2262 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
2263 p_flex_update_rec.attribute11 := NULL;
2264 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
2265 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
2266 END IF;
2267
2268 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
2269 p_flex_update_rec.attribute12 := NULL;
2270 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
2271 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
2272 END IF;
2273
2274 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
2275 p_flex_update_rec.attribute13 := NULL;
2276 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
2277 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
2278 END IF;
2279
2280 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
2281 p_flex_update_rec.attribute14 := NULL;
2282 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
2283 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
2284 END IF;
2285
2286 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
2287 p_flex_update_rec.attribute15 := NULL;
2288 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
2289 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
2290 END IF;
2291
2292 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
2293 p_flex_update_rec.attribute16 := NULL;
2294 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
2295 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
2296 END IF;
2297
2298 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
2299 p_flex_update_rec.attribute17 := NULL;
2300 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
2301 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
2302 END IF;
2303
2304 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
2305 p_flex_update_rec.attribute18 := NULL;
2306 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
2307 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
2308 END IF;
2309
2310 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
2311 p_flex_update_rec.attribute19 := NULL;
2312 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
2313 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
2314 END IF;
2315
2316 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
2317 p_flex_update_rec.attribute20 := NULL;
2318 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
2319 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
2320 END IF;
2321
2322 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
2323 p_flex_update_rec.attribute21 := NULL;
2324 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
2325 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
2326 END IF;
2327
2328 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
2329 p_flex_update_rec.attribute22 := NULL;
2330 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
2331 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
2332 END IF;
2333
2334 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
2335 p_flex_update_rec.attribute23 := NULL;
2336 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
2337 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
2338 END IF;
2339
2340 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
2341 p_flex_update_rec.attribute24 := NULL;
2342 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
2343 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
2344 END IF;
2345
2346 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
2347 p_flex_update_rec.attribute25 := NULL;
2348 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
2349 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
2350 END IF;
2351
2352 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
2353 p_flex_update_rec.attribute26 := NULL;
2354 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
2355 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
2356 END IF;
2357
2358 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
2359 p_flex_update_rec.attribute27 := NULL;
2360 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
2361 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
2362 END IF;
2363
2364 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
2365 p_flex_update_rec.attribute28 := NULL;
2366 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
2367 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
2368 END IF;
2369
2370 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
2371 p_flex_update_rec.attribute29 := NULL;
2372 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
2373 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
2374 END IF;
2375
2376 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
2377 p_flex_update_rec.attribute30 := NULL;
2378 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
2379 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
2380 END IF;
2381
2382 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
2383 p_flex_update_rec.attribute_category := NULL;
2384 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
2385 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
2386 END IF;
2387 END IF;
2388
2389 END IF;
2390 CLOSE Flex_cur;
2391
2392 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2393 GMD_RECIPE_DETAIL_PVT.RECIPE_ROUTING_STEPS (p_recipe_detail_rec => p_recipe_detail_rec
2394 ,p_flex_insert_rec => p_flex_insert_rec
2395 ,p_flex_update_rec => p_flex_update_rec
2396 ,x_return_status => x_return_status);
2397 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2398 RAISE FND_API.G_EXC_ERROR;
2399 END IF;
2400 END IF;
2401
2402 END LOOP;
2403
2404 IF FND_API.To_Boolean( p_commit ) THEN
2405 Commit;
2406 END IF;
2407
2408 /* Get the message count and information */
2409 FND_MSG_PUB.Count_And_Get (
2410 p_count => x_msg_count,
2411 p_data => x_msg_data );
2412
2413 EXCEPTION
2414 WHEN FND_API.G_EXC_ERROR THEN
2415 ROLLBACK to Recipe_Routing_Steps;
2416 x_return_status := FND_API.G_RET_STS_ERROR;
2417 FND_MSG_PUB.Count_And_Get (
2418 p_count => x_msg_count,
2419 p_data => x_msg_data );
2420
2421 WHEN setup_failure THEN
2422 ROLLBACK to Recipe_Routing_Steps;
2423 x_return_status := FND_API.G_RET_STS_ERROR;
2424 fnd_msg_pub.count_and_get (
2425 p_count => x_msg_count
2426 ,p_encoded => FND_API.g_false
2427 ,p_data => x_msg_data);
2428 WHEN OTHERS THEN
2429 ROLLBACK to Recipe_Routing_Steps;
2430 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
2431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2432 FND_MSG_PUB.Count_And_Get (
2433 p_count => x_msg_count,
2434 p_data => x_msg_data );
2435
2436 END Recipe_Routing_Steps;
2437
2438 /* ============================================= */
2439 /* Procedure: */
2440 /* Recipe_Orgn_Operations */
2441 /* */
2442 /* DESCRIPTION: */
2443 /* This PL/SQL procedure is responsible for */
2444 /* inserting and updating recipe orgn activities */
2445 /* */
2446 /* ============================================= */
2447 /* Start of commments */
2448 /* API name : Recipe_Orgn_operations */
2449 /* Type : Public */
2450 /* Function : */
2451 /* Parameters : */
2452 /* IN : p_api_version IN NUMBER Required */
2453 /* p_init_msg_list IN Varchar2 Optional */
2454 /* p_commit IN Varchar2 Optional */
2455 /* p_recipe_detail_tbl IN Required */
2456 /* */
2457 /* OUT x_return_status OUT NOCOPY varchar2(1) */
2458 /* x_msg_count OUT NOCOPY Number */
2459 /* x_msg_data OUT NOCOPY varchar2(2000) */
2460 /* */
2461 /* Version : Current Version 1.0 */
2462 /* */
2463 /* Notes : p_called_from_forms parameter not currently used */
2464 /* originally included for returning error messages */
2465 /* */
2466 /* End of comments */
2467 PROCEDURE RECIPE_ORGN_OPERATIONS
2468 ( p_api_version IN NUMBER ,
2469 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2470 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2471 p_called_from_forms IN VARCHAR2 := 'NO' ,
2472 x_return_status OUT NOCOPY VARCHAR2 ,
2473 x_msg_count OUT NOCOPY NUMBER ,
2474 x_msg_data OUT NOCOPY VARCHAR2 ,
2475 p_recipe_detail_tbl IN recipe_detail_tbl ,
2476 p_recipe_insert_flex IN recipe_flex ,
2477 p_recipe_update_flex IN recipe_update_flex
2478 ) IS
2479
2480 /* Define all variables specific to this procedure */
2481 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_OPERATIONS';
2482 l_api_version CONSTANT NUMBER := 2.0;
2483 l_rowid VARCHAR2(32);
2484 l_user_id fnd_user.user_id%TYPE := 0;
2485 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
2486
2487 /* Variables used for defining status */
2488 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
2489 l_return_code NUMBER := 0;
2490
2491 /* Error message count and data */
2492 l_msg_count NUMBER;
2493 l_msg_data VARCHAR2(2000);
2494
2495 /* Record types for data manipulation */
2496 p_recipe_detail_rec recipe_dtl;
2497
2498 /* flex field records for inserts and updates */
2499 p_flex_insert_rec flex;
2500 p_flex_update_rec update_flex;
2501
2502 /* used for g_miss_char logic */
2503 l_flex_update_rec update_flex;
2504
2505 /* Define a cursor for dealing with updates */
2506 CURSOR Flex_cur(vRecipe_Id NUMBER, vRoutingstep_Id NUMBER,
2507 vOprn_Line_Id Number, vOrgn_id NUMBER) IS
2508 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
2509 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
2510 attribute11, attribute12, attribute13, attribute14, attribute15,
2511 attribute16, attribute17, attribute18, attribute19, attribute20,
2512 attribute21, attribute22, attribute23, attribute24,attribute25,
2513 attribute26, attribute27, attribute28, attribute29, attribute30
2514 From gmd_recipe_orgn_activities
2515 where recipe_id = NVL(vRecipe_id,-1) AND
2516 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
2517 oprn_line_id = NVL(vOprn_line_id,-1) AND
2518 organization_id = vOrgn_id;
2519
2520 setup_failure EXCEPTION;
2521
2522 BEGIN
2523 /* Updating recipe orgn activity for forst time infact insert a new record in */
2524 /* gmd_recipe_orgn activities table */
2525
2526 /* Define Savepoint */
2527 SAVEPOINT Recipe_Orgn_Activities;
2528
2529 /* Standard Check for API compatibility */
2530 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2531 p_api_version ,
2532 l_api_name ,
2533 G_PKG_NAME )
2534 THEN
2535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2536 END IF;
2537
2538 /* Initialize message list if p_init_msg_list is set to TRUE */
2539 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2540 FND_MSG_PUB.initialize;
2541 END IF;
2542
2543 /* Intialize the setup fields */
2544 IF NOT gmd_api_grp.setup_done THEN
2545 gmd_api_grp.setup_done := gmd_api_grp.setup;
2546 END IF;
2547 IF NOT gmd_api_grp.setup_done THEN
2548 RAISE setup_failure;
2549 END IF;
2550
2551 IF (p_recipe_detail_tbl.Count = 0) THEN
2552 RAISE FND_API.G_EXC_ERROR;
2553 END IF;
2554
2555 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
2556
2557 /* Initialization of all status */
2558 /* If a record fails in validation we store this message in error stack */
2559 /* and loop thro records */
2560 x_return_status := FND_API.G_RET_STS_SUCCESS;
2561
2562 /* Assign each row from the PL/SQL table to a row. */
2563 p_recipe_detail_rec := p_recipe_detail_tbl(i);
2564
2565 /* ========================== */
2566 /* Check if recipe id exists */
2567 /* ========================== */
2568 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
2569 GMD_RECIPE_VAL.recipe_name
2570 ( p_api_version => 1.0,
2571 p_init_msg_list => FND_API.G_FALSE,
2572 p_commit => FND_API.G_FALSE,
2573 p_recipe_no => p_recipe_detail_rec.recipe_no,
2574 p_recipe_version => p_recipe_detail_rec.recipe_version,
2575 x_return_status => l_return_status,
2576 x_msg_count => l_msg_count,
2577 x_msg_data => l_msg_data,
2578 x_return_code => l_return_code,
2579 x_recipe_id => l_recipe_id);
2580
2581 IF (l_recipe_id IS NULL) THEN
2582 x_return_status := FND_API.G_RET_STS_ERROR;
2583 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
2584 FND_MSG_PUB.ADD;
2585 ELSE
2586 p_recipe_detail_rec.recipe_id := l_recipe_id;
2587 END IF;
2588 END IF;
2589
2590 /* Validate if this Recipe can be modified by this user */
2591 /* Recipe Security fix */
2592 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
2593 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
2594 RAISE FND_API.G_EXC_ERROR;
2595 END IF;
2596
2597 /* ================================ */
2598 /* Check if a valid routing and */
2599 /* routing step exists */
2600 /* ================================ */
2601 IF (p_recipe_detail_rec.routingstep_id IS NULL) THEN
2602 x_return_status := FND_API.G_RET_STS_ERROR;
2603 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
2604 FND_MSG_PUB.ADD;
2605 END IF;
2606
2607 /* ==================================== */
2608 /* Check if a valid oprn line id exists */
2609 /* ===================================== */
2610 IF (p_recipe_detail_rec.oprn_line_id IS NULL) THEN
2611 x_return_status := FND_API.G_RET_STS_ERROR;
2612 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
2613 FND_MSG_PUB.ADD;
2614 END IF;
2615
2616 /* Assign flex fields */
2617 IF (p_recipe_insert_flex.count = 0) THEN
2618 p_flex_insert_rec := NULL;
2619 ELSE
2620 p_flex_insert_rec := p_recipe_insert_flex(i);
2621 END IF;
2622
2623 /* Assign flex fields */
2624 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,
2625 p_recipe_detail_rec.routingstep_id,
2626 p_recipe_detail_rec.oprn_line_id,
2627 p_recipe_detail_rec.organization_id);
2628 FETCH Flex_cur INTO l_flex_update_rec;
2629 IF flex_cur%FOUND THEN
2630
2631 /* If no flex field is updated retain the old values */
2632 IF (p_recipe_update_flex.count = 0) THEN
2633 p_flex_update_rec := l_flex_update_rec;
2634 ELSE
2635 p_flex_update_rec := p_recipe_update_flex(i);
2636
2637 /* ================================ */
2638 /* Check for all G_MISS_CHAR values */
2639 /* for nullable fields in */
2640 /* gmd_recipe_routing_steps table */
2641 /* ================================= */
2642
2643 /* Thomas Daniel - Bug 2652200 */
2644 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
2645 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
2646 /* as the user is intending to update the field to NULL */
2647 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
2648 p_flex_update_rec.attribute1 := NULL;
2649 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
2650 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
2651 END IF;
2652
2653 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
2654 p_flex_update_rec.attribute2 := NULL;
2655 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
2656 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
2657 END IF;
2658
2659 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
2660 p_flex_update_rec.attribute3 := NULL;
2661 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
2662 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
2663 END IF;
2664
2665 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
2666 p_flex_update_rec.attribute4 := NULL;
2667 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
2668 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
2669 END IF;
2670
2671 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
2672 p_flex_update_rec.attribute5 := NULL;
2673 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
2674 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
2675 END IF;
2676
2677 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
2678 p_flex_update_rec.attribute6 := NULL;
2679 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
2680 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
2681 END IF;
2682
2683 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
2684 p_flex_update_rec.attribute7 := NULL;
2685 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
2686 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
2687 END IF;
2688
2689 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
2690 p_flex_update_rec.attribute8 := NULL;
2691 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
2692 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
2693 END IF;
2694
2695 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
2696 p_flex_update_rec.attribute9 := NULL;
2697 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
2698 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
2699 END IF;
2700
2701 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
2702 p_flex_update_rec.attribute10 := NULL;
2703 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
2704 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
2705 END IF;
2706
2707 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
2708 p_flex_update_rec.attribute11 := NULL;
2709 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
2710 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
2711 END IF;
2712
2713 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
2714 p_flex_update_rec.attribute12 := NULL;
2715 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
2716 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
2717 END IF;
2718
2719 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
2720 p_flex_update_rec.attribute13 := NULL;
2721 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
2722 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
2723 END IF;
2724
2725 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
2726 p_flex_update_rec.attribute14 := NULL;
2727 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
2728 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
2729 END IF;
2730
2731 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
2732 p_flex_update_rec.attribute15 := NULL;
2733 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
2734 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
2735 END IF;
2736
2737 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
2738 p_flex_update_rec.attribute16 := NULL;
2739 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
2740 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
2741 END IF;
2742
2743 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
2744 p_flex_update_rec.attribute17 := NULL;
2745 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
2746 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
2747 END IF;
2748
2749 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
2750 p_flex_update_rec.attribute18 := NULL;
2751 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
2752 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
2753 END IF;
2754
2755 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
2756 p_flex_update_rec.attribute19 := NULL;
2757 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
2758 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
2759 END IF;
2760
2761 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
2762 p_flex_update_rec.attribute20 := NULL;
2763 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
2764 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
2765 END IF;
2766
2767 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
2768 p_flex_update_rec.attribute21 := NULL;
2769 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
2770 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
2771 END IF;
2772
2773 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
2774 p_flex_update_rec.attribute22 := NULL;
2775 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
2776 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
2777 END IF;
2778
2779 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
2780 p_flex_update_rec.attribute23 := NULL;
2781 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
2782 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
2783 END IF;
2784
2785 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
2786 p_flex_update_rec.attribute24 := NULL;
2787 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
2788 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
2789 END IF;
2790
2791 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
2792 p_flex_update_rec.attribute25 := NULL;
2793 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
2794 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
2795 END IF;
2796
2797 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
2798 p_flex_update_rec.attribute26 := NULL;
2799 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
2800 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
2801 END IF;
2802
2803 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
2804 p_flex_update_rec.attribute27 := NULL;
2805 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
2806 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
2807 END IF;
2808
2809 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
2810 p_flex_update_rec.attribute28 := NULL;
2811 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
2812 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
2813 END IF;
2814
2815 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
2816 p_flex_update_rec.attribute29 := NULL;
2817 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
2818 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
2819 END IF;
2820
2821 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
2822 p_flex_update_rec.attribute30 := NULL;
2823 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
2824 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
2825 END IF;
2826
2827 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
2828 p_flex_update_rec.attribute_category := NULL;
2829 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
2830 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
2831 END IF;
2832 END IF;
2833
2834 END IF; /* end of flex_cur%FOUND */
2835 CLOSE Flex_cur; -- Bug 6972110
2836
2837 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2838 GMD_RECIPE_DETAIL_PVT.RECIPE_ORGN_OPERATIONS (p_recipe_detail_rec => p_recipe_detail_rec
2839 ,p_flex_insert_rec => p_flex_insert_rec
2840 ,p_flex_update_rec => p_flex_update_rec
2841 ,x_return_status => x_return_status);
2842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2843 RAISE FND_API.G_EXC_ERROR;
2844 END IF;
2845 END IF;
2846
2847 END LOOP;
2848
2849 IF FND_API.To_Boolean( p_commit ) THEN
2850 Commit;
2851 END IF;
2852
2853 /* Get the message count and information */
2854 FND_MSG_PUB.Count_And_Get (
2855 p_count => x_msg_count,
2856 p_data => x_msg_data );
2857
2858 EXCEPTION
2859 WHEN FND_API.G_EXC_ERROR THEN
2860 ROLLBACK to Recipe_Orgn_Activities;
2861 x_return_status := FND_API.G_RET_STS_ERROR;
2862 FND_MSG_PUB.Count_And_Get (
2863 p_count => x_msg_count,
2864 p_data => x_msg_data );
2865
2866 WHEN setup_failure THEN
2867 x_return_status := FND_API.G_RET_STS_ERROR;
2868 fnd_msg_pub.count_and_get (
2869 p_count => x_msg_count
2870 ,p_encoded => FND_API.g_false
2871 ,p_data => x_msg_data);
2872 WHEN OTHERS THEN
2873 ROLLBACK to Recipe_Orgn_Activities;
2874 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
2875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2876 FND_MSG_PUB.Count_And_Get (
2877 p_count => x_msg_count,
2878 p_data => x_msg_data );
2879
2880 END RECIPE_ORGN_OPERATIONS;
2881
2882
2883 /* ============================================= */
2884 /* Procedure: */
2885 /* Recipe_Orgn_Resources */
2886 /* */
2887 /* DESCRIPTION: */
2888 /* This PL/SQL procedure is responsible for */
2889 /* inserting and updating recipe orgn resources */
2890 /* */
2891 /* ============================================= */
2892 /* Start of commments */
2893 /* API name : Recipe_Orgn_Resources */
2894 /* Type : Public */
2895 /* Function : */
2896 /* parameters : */
2897 /* IN : p_api_version IN NUMBER Required */
2898 /* p_init_msg_list IN Varchar2 Optional */
2899 /* p_commit IN Varchar2 Optional */
2900 /* p_recipe_detail_tbl IN Required */
2901 /* */
2902 /* OUT x_return_status OUT NOCOPY varchar2(1) */
2903 /* x_msg_count OUT NOCOPY Number */
2904 /* x_msg_data OUT NOCOPY varchar2(2000) */
2905 /* */
2906 /* Version : Current Version 1.0 */
2907 /* */
2908 /* Notes : p_called_from_forms parameter not currently used */
2909 /* originally included for returning error messages */
2910 /* */
2911 /* End of comments */
2912 PROCEDURE RECIPE_ORGN_RESOURCES
2913 ( p_api_version IN NUMBER ,
2914 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2915 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2916 p_called_from_forms IN VARCHAR2 := 'NO' ,
2917 x_return_status OUT NOCOPY VARCHAR2 ,
2918 x_msg_count OUT NOCOPY NUMBER ,
2919 x_msg_data OUT NOCOPY VARCHAR2 ,
2920 p_recipe_detail_tbl IN recipe_detail_tbl ,
2921 p_recipe_insert_flex IN recipe_flex ,
2922 p_recipe_update_flex IN recipe_update_flex
2923 ) IS
2924
2925 /* Define all variables specific to this procedure */
2926 l_dml_type VARCHAR2(1) := 'I';
2927 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_RESOURCES';
2928 l_api_version CONSTANT NUMBER := 2.0;
2929
2930 l_user_id fnd_user.user_id%TYPE := 0;
2931 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
2932
2933 /* Variables used for defining status */
2934 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
2935 l_return_code NUMBER := 0;
2936
2937 /* Error message count and data */
2938 l_msg_count NUMBER;
2939 l_msg_data VARCHAR2(2000);
2940
2941 /* Record types for data manipulation */
2942 p_recipe_detail_rec recipe_dtl;
2943
2944 /* flex field records for inserts and updates */
2945 p_flex_insert_rec flex;
2946 p_flex_update_rec update_flex;
2947
2948 /* used for g_miss_char logic */
2949 l_flex_update_rec update_flex;
2950
2951 /* Define a cursor for dealing with updates */
2952 CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
2953 vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
2954 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
2955 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
2956 attribute11, attribute12, attribute13, attribute14, attribute15,
2957 attribute16, attribute17, attribute18, attribute19, attribute20,
2958 attribute21, attribute22, attribute23, attribute24,attribute25,
2959 attribute26, attribute27, attribute28, attribute29, attribute30
2960 From gmd_recipe_orgn_resources
2961 where recipe_id = NVL(vRecipe_id,-1) AND
2962 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
2963 oprn_line_id = NVL(vOprn_line_id,-1) AND
2964 resources = vResources AND
2965 organization_id = vOrgn_id;
2966
2967 CURSOR update_res_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
2968 vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
2969 Select min_capacity, max_capacity, process_qty, usage_uom,
2970 resource_usage
2971 From gmd_recipe_orgn_resources
2972 where recipe_id = NVL(vRecipe_id,-1) AND
2973 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
2974 oprn_line_id = NVL(vOprn_line_id,-1) AND
2975 resources = vResources AND
2976 organization_id = vOrgn_id;
2977
2978 setup_failure EXCEPTION;
2979 BEGIN
2980 /* Updating recipe orgn resources for forst time infact insert a new record in */
2981 /* gmd_recipe_orgn_resources table */
2982
2983 /* Define Savepoint */
2984 SAVEPOINT Recipe_Orgn_Resources;
2985
2986 /* Standard Check for API compatibility */
2987 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2988 p_api_version ,
2989 l_api_name ,
2990 G_PKG_NAME )
2991 THEN
2992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2993 END IF;
2994
2995 /* Initialize message list if p_init_msg_list is set to TRUE */
2996 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2997 FND_MSG_PUB.initialize;
2998 END IF;
2999
3000 /* Intialize the setup fields */
3001 IF NOT gmd_api_grp.setup_done THEN
3002 gmd_api_grp.setup_done := gmd_api_grp.setup;
3003 END IF;
3004 IF NOT gmd_api_grp.setup_done THEN
3005 RAISE setup_failure;
3006 END IF;
3007
3008 IF (p_recipe_detail_tbl.Count = 0) THEN
3009 RAISE FND_API.G_EXC_ERROR;
3010 END IF;
3011
3012 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
3013
3014 /* Initialization of all status */
3015 /* If a record fails in validation we store this message in error stack */
3016 /* and loop thro records */
3017 x_return_status := FND_API.G_RET_STS_SUCCESS;
3018
3019 /* Assign each row from the PL/SQL table to a row. */
3020 p_recipe_detail_rec := p_recipe_detail_tbl(i);
3021
3022 /* ========================== */
3023 /* Check if recipe id exists */
3024 /* ========================== */
3025 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
3026 GMD_RECIPE_VAL.recipe_name
3027 ( p_api_version => 1.0,
3028 p_init_msg_list => FND_API.G_FALSE,
3029 p_commit => FND_API.G_FALSE,
3030 p_recipe_no => p_recipe_detail_rec.recipe_no,
3031 p_recipe_version => p_recipe_detail_rec.recipe_version,
3032 x_return_status => l_return_status,
3033 x_msg_count => l_msg_count,
3034 x_msg_data => l_msg_data,
3035 x_return_code => l_return_code,
3036 x_recipe_id => l_recipe_id);
3037
3038 IF (l_recipe_id IS NULL) THEN
3039 x_return_status := FND_API.G_RET_STS_ERROR;
3040 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
3041 FND_MSG_PUB.ADD;
3042 ELSE
3043 p_recipe_detail_rec.recipe_id := l_recipe_id;
3044 END IF;
3045 END IF;
3046
3047 /* Validate if this Recipe can be modified by this user */
3048 /* Recipe Security fix */
3049 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
3050 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
3051 RAISE FND_API.G_EXC_ERROR;
3052 END IF;
3053
3054
3055 /* ================================ */
3056 /* Check if a valid routing and */
3057 /* routing step exists */
3058 /* ================================ */
3059 IF (p_recipe_detail_rec.routingstep_id IS NULL) THEN
3060 x_return_status := FND_API.G_RET_STS_ERROR;
3061 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3062 FND_MSG_PUB.ADD;
3063 END IF;
3064
3065 /* ==================================== */
3066 /* Check if a valid oprn line id exists */
3067 /* ===================================== */
3068 IF (p_recipe_detail_rec.oprn_line_id IS NULL) THEN
3069 x_return_status := FND_API.G_RET_STS_ERROR;
3070 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3071 FND_MSG_PUB.ADD;
3072 END IF;
3073
3074 /* ===================================== */
3075 /* Check if a valid resource exists */
3076 /* ================================== */
3077 IF (p_recipe_detail_rec.resources IS NULL) THEN
3078 x_return_status := FND_API.G_RET_STS_ERROR;
3079 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3080 FND_MSG_PUB.ADD;
3081 END IF;
3082
3083 FOR update_rec IN update_res_cur (p_recipe_detail_rec.recipe_id,
3084 p_recipe_detail_rec.routingstep_id,
3085 p_recipe_detail_rec.oprn_line_id,
3086 p_recipe_detail_rec.resources,
3087 p_recipe_detail_rec.organization_id) LOOP
3088
3089 IF (p_recipe_detail_rec.min_capacity = FND_API.G_MISS_NUM) THEN
3090 p_recipe_detail_rec.min_capacity := NULL;
3091 ELSIF (p_recipe_detail_rec.min_capacity IS NULL) THEN
3092 p_recipe_detail_rec.min_capacity := update_rec.min_capacity;
3093 END IF;
3094
3095 IF (p_recipe_detail_rec.max_capacity = FND_API.G_MISS_NUM) THEN
3096 p_recipe_detail_rec.max_capacity := NULL;
3097 ELSIF (p_recipe_detail_rec.max_capacity IS NULL) THEN
3098 p_recipe_detail_rec.max_capacity := update_rec.max_capacity;
3099 END IF;
3100
3101 IF (p_recipe_detail_rec.process_qty = FND_API.G_MISS_NUM) THEN
3102 p_recipe_detail_rec.process_qty := NULL;
3103 ELSIF (p_recipe_detail_rec.process_qty IS NULL) THEN
3104 p_recipe_detail_rec.process_qty := update_rec.process_qty;
3105 END IF;
3106
3107 IF (p_recipe_detail_rec.resource_usage = FND_API.G_MISS_NUM) THEN
3108 p_recipe_detail_rec.resource_usage := NULL;
3109 ELSIF (p_recipe_detail_rec.resource_usage IS NULL) THEN
3110 p_recipe_detail_rec.resource_usage := update_rec.resource_usage;
3111 END IF;
3112
3113 IF (p_recipe_detail_rec.usage_uom = FND_API.G_MISS_CHAR) THEN
3114 p_recipe_detail_rec.usage_uom := NULL;
3115 ELSIF (p_recipe_detail_rec.usage_uom IS NULL) THEN
3116 p_recipe_detail_rec.usage_uom := update_rec.usage_uom;
3117 END IF;
3118
3119
3120 END LOOP;
3121
3122
3123 /* Assign flex fields */
3124 IF (p_recipe_insert_flex.count = 0) THEN
3125 p_flex_insert_rec := NULL;
3126 ELSE
3127 p_flex_insert_rec := p_recipe_insert_flex(i);
3128 END IF;
3129
3130 /* Assign flex fields */
3131 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,
3132 p_recipe_detail_rec.routingstep_id,
3133 p_recipe_detail_rec.oprn_line_id,
3134 p_recipe_detail_rec.resources,
3135 p_recipe_detail_rec.organization_id);
3136 FETCH Flex_cur INTO l_flex_update_rec;
3137 IF flex_cur%FOUND THEN
3138
3139 /* If no flex field is updated retain the old values */
3140 IF (p_recipe_update_flex.count = 0) THEN
3141 p_flex_update_rec := l_flex_update_rec;
3142 ELSE
3143 p_flex_update_rec := p_recipe_update_flex(i);
3144
3145 /* ================================ */
3146 /* Check for all G_MISS_CHAR values */
3147 /* for nullable fields in */
3148 /* gmd_recipe_routing_steps table */
3149 /* ================================= */
3150
3151 /* Thomas Daniel - Bug 2652200 */
3152 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
3153 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
3154 /* as the user is intending to update the field to NULL */
3155 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
3156 p_flex_update_rec.attribute1 := NULL;
3157 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
3158 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
3159 END IF;
3160
3161 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
3162 p_flex_update_rec.attribute2 := NULL;
3163 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
3164 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
3165 END IF;
3166
3167 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
3168 p_flex_update_rec.attribute3 := NULL;
3169 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
3170 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
3171 END IF;
3172
3173 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
3174 p_flex_update_rec.attribute4 := NULL;
3175 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
3176 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
3177 END IF;
3178
3179 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
3180 p_flex_update_rec.attribute5 := NULL;
3181 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
3182 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
3183 END IF;
3184
3185 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
3186 p_flex_update_rec.attribute6 := NULL;
3187 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
3188 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
3189 END IF;
3190
3191 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
3192 p_flex_update_rec.attribute7 := NULL;
3193 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
3194 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
3195 END IF;
3196
3197 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
3198 p_flex_update_rec.attribute8 := NULL;
3199 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
3200 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
3201 END IF;
3202
3203 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
3204 p_flex_update_rec.attribute9 := NULL;
3205 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
3206 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
3207 END IF;
3208
3209 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
3210 p_flex_update_rec.attribute10 := NULL;
3211 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
3212 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
3213 END IF;
3214
3215 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
3216 p_flex_update_rec.attribute11 := NULL;
3217 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
3218 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
3219 END IF;
3220
3221 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
3222 p_flex_update_rec.attribute12 := NULL;
3223 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
3224 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
3225 END IF;
3226
3227 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
3228 p_flex_update_rec.attribute13 := NULL;
3229 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
3230 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
3231 END IF;
3232
3233 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
3234 p_flex_update_rec.attribute14 := NULL;
3235 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
3236 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
3237 END IF;
3238
3239 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
3240 p_flex_update_rec.attribute15 := NULL;
3241 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
3242 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
3243 END IF;
3244
3245 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
3246 p_flex_update_rec.attribute16 := NULL;
3247 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
3248 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
3249 END IF;
3250
3251 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
3252 p_flex_update_rec.attribute17 := NULL;
3253 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
3254 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
3255 END IF;
3256
3257 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
3258 p_flex_update_rec.attribute18 := NULL;
3259 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
3260 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
3261 END IF;
3262
3263 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
3264 p_flex_update_rec.attribute19 := NULL;
3265 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
3266 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
3267 END IF;
3268
3269 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
3270 p_flex_update_rec.attribute20 := NULL;
3271 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
3272 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
3273 END IF;
3274
3275 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
3276 p_flex_update_rec.attribute21 := NULL;
3277 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
3278 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
3279 END IF;
3280
3281 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
3282 p_flex_update_rec.attribute22 := NULL;
3283 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
3284 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
3285 END IF;
3286
3287 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
3288 p_flex_update_rec.attribute23 := NULL;
3289 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
3290 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
3291 END IF;
3292
3293 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
3294 p_flex_update_rec.attribute24 := NULL;
3295 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
3296 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
3297 END IF;
3298
3299 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
3300 p_flex_update_rec.attribute25 := NULL;
3301 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
3302 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
3303 END IF;
3304
3305 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
3306 p_flex_update_rec.attribute26 := NULL;
3307 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
3308 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
3309 END IF;
3310
3311 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
3312 p_flex_update_rec.attribute27 := NULL;
3313 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
3314 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
3315 END IF;
3316
3317 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
3318 p_flex_update_rec.attribute28 := NULL;
3319 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
3320 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
3321 END IF;
3322
3323 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
3324 p_flex_update_rec.attribute29 := NULL;
3325 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
3326 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
3327 END IF;
3328
3329 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
3330 p_flex_update_rec.attribute30 := NULL;
3331 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
3332 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
3333 END IF;
3334
3335 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
3336 p_flex_update_rec.attribute_category := NULL;
3337 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
3338 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
3339 END IF;
3340 END IF;
3341
3342 END IF; /* end of flex_cur%FOUND */
3343 CLOSE Flex_cur; -- Bug 6972110
3344
3345 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3346 GMD_RECIPE_DETAIL_PVT.recipe_orgn_resources (p_recipe_detail_rec => p_recipe_detail_rec
3347 ,p_flex_insert_rec => p_flex_insert_rec
3348 ,p_flex_update_rec => p_flex_update_rec
3349 ,x_return_status => x_return_status);
3350 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3351 RAISE FND_API.G_EXC_ERROR;
3352 END IF;
3353 END IF;
3354
3355 END LOOP;
3356
3357 IF FND_API.To_Boolean( p_commit ) THEN
3358 Commit;
3359 END IF;
3360
3361 /* Get the message count and information */
3362 FND_MSG_PUB.Count_And_Get (
3363 p_count => x_msg_count,
3364 p_data => x_msg_data );
3365
3366 EXCEPTION
3367 WHEN FND_API.G_EXC_ERROR THEN
3368 ROLLBACK to Recipe_Orgn_Resources;
3369 x_return_status := FND_API.G_RET_STS_ERROR;
3370 FND_MSG_PUB.Count_And_Get (
3371 p_count => x_msg_count,
3372 p_data => x_msg_data );
3373
3374 WHEN setup_failure THEN
3375 x_return_status := FND_API.G_RET_STS_ERROR;
3376 fnd_msg_pub.count_and_get (
3377 p_count => x_msg_count
3378 ,p_encoded => FND_API.g_false
3379 ,p_data => x_msg_data);
3380
3381 WHEN OTHERS THEN
3382 ROLLBACK to Recipe_Orgn_Resources;
3383 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
3384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3385 FND_MSG_PUB.Count_And_Get (
3386 p_count => x_msg_count,
3387 p_data => x_msg_data );
3388
3389 END RECIPE_ORGN_RESOURCES;
3390
3391 END GMD_RECIPE_DETAIL; /* Package end */
[Home] [Help]