1 PACKAGE BODY GMD_RECIPE_DETAIL AS
2 /* $Header: GMDPRCDB.pls 120.11.12020000.4 2013/01/24 11:33:27 guobxu 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 --bug16072538
607 invalid_vr_detail_uom EXCEPTION;
608 l_vr_detail_um VARCHAR2(32);
609 CURSOR check_vr_detail_uom(vDetail_uom VARCHAR2) IS
610 SELECT uom_code
611 FROM mtl_units_of_measure_vl
612 WHERE uom_code = vDetail_uom;
613
614 BEGIN
615 /* Define Savepoint */
616 SAVEPOINT Insert_Recipe_VR;
617
618 /* Standard Check for API compatibility */
619 IF NOT FND_API.Compatible_API_Call ( l_api_version,
620 p_api_version,
621 l_api_name ,
622 G_PKG_NAME )
623 THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 END IF;
626
627 /* Initialize message list if p_init_msg_list is set to TRUE */
628 IF FND_API.to_Boolean( p_init_msg_list ) THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631
632 /* Intialize the setup fields */
633 IF NOT gmd_api_grp.setup_done THEN
634 gmd_api_grp.setup_done := gmd_api_grp.setup;
635 END IF;
636 IF NOT gmd_api_grp.setup_done THEN
637 RAISE setup_failure;
638 END IF;
639
640 IF (p_recipe_vr_tbl.Count = 0) THEN
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 FOR i IN 1 .. p_recipe_vr_tbl.count LOOP
645 /* Initialization of all status */
646 /* If a record fails in validation we store this message in error stack */
647 /* and loop thro records */
648 x_return_status := FND_API.G_RET_STS_SUCCESS;
649
650 /* Assign each row from the PL/SQL table to a row. */
651 p_recipe_vr_rec := p_recipe_vr_tbl(i);
652
653 IF (p_recipe_vr_flex.count = 0) THEN
654 p_recipe_vr_flex_rec := NULL;
655 ELSE
656 p_recipe_vr_flex_rec := p_recipe_vr_flex(i);
657 END IF;
658
659 /* ================================ */
660 /* Check if recipe id exists */
661 /* ================================= */
662 IF (p_recipe_vr_rec.recipe_id IS NULL) THEN
663 GMD_RECIPE_VAL.recipe_name
664 ( p_api_version => 1.0,
665 p_init_msg_list => FND_API.G_FALSE,
666 p_commit => FND_API.G_FALSE,
667 p_recipe_no => p_recipe_vr_rec.recipe_no,
668 p_recipe_version => p_recipe_vr_rec.recipe_version,
669 x_return_status => l_return_status,
670 x_msg_count => l_msg_count,
671 x_msg_data => l_msg_data,
672 x_return_code => l_return_code,
673 x_recipe_id => l_recipe_id);
674
675 IF (l_recipe_id IS NULL) THEN
676 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
677 FND_MSG_PUB.ADD;
678 RAISE Recipe_VR_insert_failure;
679 ELSE
680 p_recipe_vr_rec.recipe_id := l_recipe_id;
681 END IF;
682 END IF;
683
684 /* Validate if this Recipe can be modified by this user */
685 /* Recipe Security fix */
686 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
687 ,Entity_id => p_recipe_vr_rec.recipe_id) THEN
688 RAISE Recipe_VR_insert_failure;
689 END IF;
690
691 /* Validate if the orgn code used for creation can be accessed
692 by user */
693 --Commented the code vr security will be based on recipe owner orgn code
694 /*IF (p_recipe_vr_rec.orgn_code IS NOT NULL) THEN
695 IF NOT (gmd_api_grp.isUserOrgnAccessible
696 (powner_id => gmd_api_grp.user_id
697 ,powner_orgn => p_recipe_vr_rec.orgn_code)) THEN
698 RAISE Recipe_VR_insert_failure;
699 END IF;
700 END IF;*/
701
702 /* Assign default values */
703 p_recipe_vr_rec.min_qty := NVL(p_recipe_vr_rec.min_qty,0);
704 p_recipe_vr_rec.max_qty := NVL(p_recipe_vr_rec.max_qty,999999999);
705 p_recipe_vr_rec.preference := NVL(p_recipe_vr_rec.preference,1);
706 p_recipe_vr_rec.recipe_use := NVL(p_recipe_vr_rec.recipe_use,0);
707 p_recipe_vr_rec.start_date := NVL(p_recipe_vr_rec.start_date,sysdate);
708 p_recipe_vr_rec.validity_rule_status := '100'; -- always create VR as new
709
710 --bug16072538
711 IF p_recipe_vr_rec.detail_uom is not null THEN
712 OPEN check_vr_detail_uom(p_recipe_vr_rec.detail_uom);
713 FETCH check_vr_detail_uom INTO l_vr_detail_um;
714 IF check_vr_detail_uom%NOTFOUND THEN
715 CLOSE check_vr_detail_uom;
716 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_VR_DETAIL_UOM');
717 FND_MESSAGE.SET_TOKEN ('DETAIL_UOM', p_recipe_vr_rec.detail_uom);
718 FND_MSG_PUB.ADD;
719 raise invalid_vr_detail_uom;
720 END IF;
721 CLOSE check_vr_detail_uom;
722 END IF;
723
724 /* Get the default values for std_qty, inv_min and max_qty
725 item id and item um */
726 IF (p_recipe_vr_rec.inventory_item_id IS NULL) THEN
727 OPEN get_certain_VR_defaults(p_recipe_vr_rec.recipe_id);
728 FETCH get_certain_VR_defaults INTO l_def_item_id, l_std_qty, l_std_qty_um;
729 CLOSE get_certain_VR_defaults;
730 ELSE -- Item id is given
731 OPEN get_specific_VR_details(p_recipe_vr_rec.recipe_id,p_recipe_vr_rec.inventory_item_id);
732 FETCH get_specific_VR_details INTO l_std_qty, l_std_qty_um;
733 IF get_specific_VR_details%NOTFOUND THEN
734 CLOSE get_specific_VR_details;
735 FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_IS_PRODUCT');
736 fnd_msg_pub.add;
737 RAISE Recipe_VR_insert_failure;
738 END IF;
739 CLOSE get_specific_VR_details;
740 END IF;
741
742 -- NPD Conv.
743 p_recipe_vr_rec.inventory_item_id := NVL(p_recipe_vr_rec.inventory_item_id, l_def_item_id);
744 p_recipe_vr_rec.std_qty := NVL(p_recipe_vr_rec.std_qty, l_std_qty);
745 p_recipe_vr_rec.detail_uom := NVL(p_recipe_vr_rec.detail_uom, l_std_qty_um);
746
747 /* Get the inventory primary um for calc inv_min and max qty */
748 OPEN get_primary_um(p_recipe_vr_rec.inventory_item_id);
749 FETCH get_primary_um INTO l_prim_item_um;
750 CLOSE get_primary_um;
751
752 /* Call Recipe val pkg for getting the inv min and max qty */
753 IF ((p_recipe_vr_rec.inv_min_qty IS NULL OR p_recipe_vr_rec.inv_min_qty IS NULL)) THEN
754 GMD_RECIPE_VAL.calc_inv_qtys (P_inv_item_um => l_prim_item_um,
755 P_item_um => p_recipe_vr_rec.detail_uom,
756 P_item_id => p_recipe_vr_rec.inventory_item_id,
757 P_min_qty => p_recipe_vr_rec.min_qty,
758 P_max_qty => p_recipe_vr_rec.max_qty,
759 X_inv_min_qty => p_recipe_vr_rec.inv_min_qty,
760 X_inv_max_qty => p_recipe_vr_rec.inv_max_qty,
761 x_return_status => x_return_status) ;
762 IF (x_return_status <> 'S') THEN
763 RAISE Recipe_VR_insert_failure;
764 END IF;
765 END IF;
766
767 /* added a few validation prior to creating VRs */
768
769 /* Validate start and end dates for VR with Routiing start and end dates */
770 FOR get_routing_rec in Get_Routing_Details(p_recipe_vr_rec.recipe_id) LOOP
771 -- Get the routing start date if applicable
772 GMD_RECIPE_VAL.validate_start_date
773 (P_disp_start_date => p_recipe_vr_rec.start_date,
774 P_routing_start_date => get_routing_rec.effective_start_date,
775 x_return_status => x_return_status);
776 IF (x_return_status <> 'S') THEN
777 RAISE Recipe_VR_insert_failure;
778 END IF;
779
780 GMD_RECIPE_VAL.validate_end_date
781 (P_end_date => p_recipe_vr_rec.end_date,
782 P_routing_end_date => get_routing_rec.effective_end_date,
783 x_return_status => x_return_status);
784
785 IF (x_return_status <> 'S') THEN
786 RAISE Recipe_VR_insert_failure;
787 END IF;
788 END LOOP;
789
790 /* If the formula header has fixed scale then set the std qty, min and max
791 qty as same */
792 OPEN check_fmhdr_fixed_scale(p_recipe_vr_rec.Recipe_id);
793 FETCH check_fmhdr_fixed_scale INTO l_fixed_scale;
794 CLOSE check_fmhdr_fixed_scale;
795
796 IF (l_fixed_scale = 1) THEN
797 p_recipe_vr_rec.min_qty := p_recipe_vr_rec.std_qty;
798 p_recipe_vr_rec.max_qty := p_recipe_vr_rec.std_qty;
799 END IF;
800
801 /* Insert into the recipe validity rules table */
802 gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := NULL;
803 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
804 GMD_RECIPE_DETAIL_PVT.create_recipe_vr (p_recipe_vr_rec => p_recipe_vr_rec
805 ,p_recipe_vr_flex_rec => p_recipe_vr_flex_rec
806 ,x_return_status => x_return_status);
807 IF x_return_status <> FND_API.g_ret_sts_success THEN
808 RAISE Recipe_VR_insert_failure;
809 END IF;
810 END IF;
811 END LOOP;
812
813 IF FND_API.To_Boolean( p_commit ) THEN
814 COMMIT;
815 --kkillams,bug 3408799
816 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
817 SAVEPOINT default_status_sp;
818 gmd_api_grp.get_status_details (V_entity_type => 'VALIDITY',
819 V_orgn_id => p_recipe_vr_rec.organization_id, --w.r.t. bug 4004501 INVCONV kkillams.
820 X_entity_status => l_entity_status);
821 --Add this code after the call to gmd_recipes_mls.insert_row.
822 IF (l_entity_status.entity_status <> 100) THEN
823 Gmd_status_pub.modify_status ( p_api_version => 1
824 , p_init_msg_list => TRUE
825 , p_entity_name => 'VALIDITY'
826 , p_entity_id => gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id
827 , p_entity_no => NULL
828 , p_entity_version => NULL
829 , p_to_status => l_entity_status.entity_status
830 , p_ignore_flag => FALSE
831 , x_message_count => x_msg_count
832 , x_message_list => x_msg_data
833 , x_return_status => X_return_status);
834 gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := NULL;
835 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
836 RAISE default_status_err;
837 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
838 END IF;--l_entity_status.entity_status
839 COMMIT;
840 END IF;
841
842 /* Get the message count and information */
843 FND_MSG_PUB.Count_And_Get (
844 p_count => x_msg_count,
845 p_data => x_msg_data );
846
847 EXCEPTION
848 WHEN FND_API.G_EXC_ERROR THEN
849 ROLLBACK to Insert_Recipe_VR;
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 FND_MSG_PUB.Count_And_Get (
852 p_count => x_msg_count,
853 p_data => x_msg_data );
854
855 WHEN setup_failure OR Recipe_VR_insert_failure THEN
856 ROLLBACK to Insert_Recipe_VR;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 fnd_msg_pub.count_and_get (
859 p_count => x_msg_count
860 ,p_encoded => FND_API.g_false
861 ,p_data => x_msg_data);
862
863 WHEN default_status_err THEN
864 ROLLBACK TO default_status_sp;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 FND_MSG_PUB.Count_And_Get (
867 p_count => x_msg_count,
868 p_data => x_msg_data );
869
870 --bug16072538
871 WHEN invalid_vr_detail_uom THEN
872 ROLLBACK to Insert_Recipe_VR;
873 x_return_status := FND_API.G_RET_STS_ERROR;
874 FND_MSG_PUB.Count_And_Get (
875 p_count => x_msg_count,
876 p_data => x_msg_data );
877
878 WHEN OTHERS THEN
879 ROLLBACK to Insert_Recipe_VR;
880 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882 FND_MSG_PUB.Count_And_Get (
883 p_count => x_msg_count,
884 p_data => x_msg_data );
885
886 END CREATE_RECIPE_VR;
887
888 /* ============================================= */
889 /* Procedure: */
890 /* Create_Recipe_Mtl */
891 /* */
892 /* DESCRIPTION: */
893 /* This PL/SQL procedure is responsible for */
894 /* inserting a recipe */
895 /* */
896 /* ============================================= */
897 /* Start of commments */
898 /* API name : Create_Recipe_Mtl */
899 /* Type : Public */
900 /* Function : */
901 /* parameters : */
902 /* IN : p_api_version IN NUMBER Required */
903 /* p_init_msg_list IN Varchar2 Optional */
904 /* p_commit IN Varchar2 Optional */
905 /* p_recipe_tbl IN Required */
906 /* */
907 /* OUT x_return_status OUT NOCOPY varchar2(1) */
908 /* x_msg_count OUT NOCOPY Number */
909 /* x_msg_data OUT NOCOPY varchar2(2000) */
910 /* */
911 /* Version : Current Version 1.0 */
912 /* */
913 /* Notes : p_called_from_forms parameter not currently used */
914 /* originally included for returning error messages */
915 /* */
916 /* End of comments */
917
918 PROCEDURE CREATE_RECIPE_MTL
919 ( p_api_version IN NUMBER ,
920 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
921 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
922 p_called_from_forms IN VARCHAR2 := 'NO' ,
923 x_return_status OUT NOCOPY VARCHAR2 ,
924 x_msg_count OUT NOCOPY NUMBER ,
925 x_msg_data OUT NOCOPY VARCHAR2 ,
926 p_recipe_mtl_tbl IN recipe_mtl_tbl ,
927 p_recipe_mtl_flex IN recipe_flex
928 ) IS
929 /* Define all variables specific to this procedure */
930 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_MTL';
931 l_api_version CONSTANT NUMBER := 1.0;
932
933 l_user_id fnd_user.user_id%TYPE := 0;
934 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
935
936 /* Variables used for defining status */
937 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
938 l_return_code NUMBER := 0;
939
940 /* Error message count and data */
941 l_msg_count NUMBER;
942 l_msg_data VARCHAR2(2000);
943
944 /* Record types for data manipulation */
945 p_recipe_mtl_rec RECIPE_MATERIAL;
946 p_recipe_mtl_flex_rec FLEX;
947
948 --bug 10109808 -- adding a new record type start
949
950 /* Record types to Validate the line_type */
951 l_line_type fm_matl_dtl.line_type%TYPE;
952
953 CURSOR c_line_type (cp_formulaline_id fm_matl_dtl.formulaline_id%TYPE) is
954 SELECT line_type
955 FROM fm_matl_dtl f
956 WHERE f.FORMULALINE_ID= cp_formulaline_id;
957
958 --bug 10109808 -- adding a new record type end
959 setup_failure EXCEPTION;
960 insert_rcp_mat_failure EXCEPTION;
961 undesired_values_exception EXCEPTION;
962 negative_values_exception EXCEPTION;
963 BEGIN
964 /* Define Savepoint */
965 SAVEPOINT Insert_Recipe_Materials;
966
967 /* Standard Check for API compatibility */
968 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
969 p_api_version ,
970 l_api_name ,
971 G_PKG_NAME )
972 THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975
976 /* Initialize message list if p_init_msg_list is set to TRUE */
977 IF FND_API.to_Boolean( p_init_msg_list ) THEN
978 FND_MSG_PUB.initialize;
979 END IF;
980
981 /* Intialize the setup fields */
982 IF NOT gmd_api_grp.setup_done THEN
983 gmd_api_grp.setup_done := gmd_api_grp.setup;
984 END IF;
985 IF NOT gmd_api_grp.setup_done THEN
986 RAISE setup_failure;
987 END IF;
988
989 IF (p_recipe_mtl_tbl.Count = 0) THEN
990 RAISE FND_API.G_EXC_ERROR;
991 END IF;
992
993 FOR i IN 1 .. p_recipe_mtl_tbl.count LOOP
994
995 /* Initialization of all status */
996 /* If a record fails in validation we store this message in error stack */
997 /* and loop thro records */
998 x_return_status := FND_API.G_RET_STS_SUCCESS;
999
1000 /* Assign each row from the PL/SQL table to a row. */
1001 p_recipe_mtl_rec := p_recipe_mtl_tbl(i);
1002
1003 IF (p_recipe_mtl_flex.count = 0) THEN
1004 p_recipe_mtl_flex_rec := NULL;
1005 ELSE
1006 p_recipe_mtl_flex_rec := p_recipe_mtl_flex(i);
1007 END IF;
1008
1009 /* ================================ */
1010 /* Check if recipe id exists */
1011 /* ================================= */
1012 IF (p_recipe_mtl_rec.recipe_id IS NULL) THEN
1013 GMD_RECIPE_VAL.recipe_name
1014 ( p_api_version => 1.0,
1015 p_init_msg_list => FND_API.G_FALSE,
1016 p_commit => FND_API.G_FALSE,
1017 p_recipe_no => p_recipe_mtl_rec.recipe_no,
1018 p_recipe_version => p_recipe_mtl_rec.recipe_version,
1019 x_return_status => l_return_status,
1020 x_msg_count => l_msg_count,
1021 x_msg_data => l_msg_data,
1022 x_return_code => l_return_code,
1023 x_recipe_id => l_recipe_id);
1024
1025 IF (l_recipe_id IS NULL) THEN
1026 x_return_status := FND_API.G_RET_STS_ERROR;
1027 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
1028 FND_MSG_PUB.ADD;
1029 ELSE
1030 p_recipe_mtl_rec.recipe_id := l_recipe_id;
1031 END IF;
1032 END IF;
1033
1034 /* Validate if this Recipe can be modified by this user */
1035 /* Recipe Security fix */
1036 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1037 ,Entity_id => p_recipe_mtl_rec.recipe_id) THEN
1038 RAISE insert_rcp_mat_failure;
1039 END IF;
1040
1041 /* ==================================== */
1042 /* Routing step line must exists */
1043 /* Routing details must be provided */
1044 /* Use the validation to check if */
1045 /* the routingstep_id has been provided */
1046 /* ==================================== */
1047 IF (p_recipe_mtl_rec.routingstep_id IS NULL) THEN
1048 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1049 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
1050 FND_MSG_PUB.ADD;
1051 RAISE insert_rcp_mat_failure;
1052 END IF;
1053
1054 /* validate this routing step id */
1055 /* i.e check if this routing step exists */
1056 /* for this routing_id */
1057
1058 /* ======================================= */
1059 /* Formula line must be associated with */
1060 /* this routing */
1061 /* check if the formula line is valid and */
1062 /* exists */
1063 /* ======================================= */
1064 IF (p_recipe_mtl_rec.formulaline_id IS NULL) THEN
1065 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1066 FND_MESSAGE.SET_TOKEN ('MISSING', 'FORMULALINE_ID');
1067 FND_MSG_PUB.ADD;
1068 RAISE insert_rcp_mat_failure;
1069 END IF;
1070 /* ======================================= */
1071 /* Validations so as to insert minimum_transfer_qty,minimum_delay,maximum_delay. Bug : 10109808 */
1072 /* Minimum_delay<Maximum_delay*/
1073 /* All the three fields should with values only if the line_id is 1 (for type=product)*/
1074 /* Else they must be Null */
1075 /* By Ramakrishna Borpatla , Dated : 25-OCT-2010 */
1076 /* ======================================= */
1077
1078 --Only if Minimum DELAY is present, then enable maximum quantity
1079 IF(NVL(p_recipe_mtl_rec.MINIMUM_DELAY,0)=0) THEN
1080 p_recipe_mtl_rec.MAXIMUM_DELAY := null;
1081 END IF;
1082
1083 --Minimum delay, Maximum delay and minimum_transfer_quantity cannot be negative
1084 IF( (NVL(p_recipe_mtl_rec.MINIMUM_DELAY,0)<0) or (NVL(p_recipe_mtl_rec.MAXIMUM_DELAY,0)<0) or (NVL(p_recipe_mtl_rec.MINIMUM_TRANSFER_QTY,0)<0) ) THEN
1085 FND_MESSAGE.SET_NAME('GMD','GMD_NEG_VALUE_FOUND');
1086 FND_MSG_PUB.Add;
1087 RAISE negative_values_exception;
1088 END IF;
1089
1090
1091 --Minimum delay must be less than to maximum delay
1092 IF ((NVL(p_recipe_mtl_rec.MINIMUM_DELAY,0)) > (NVL(p_recipe_mtl_rec.MAXIMUM_DELAY,0))) THEN
1093 FND_MESSAGE.SET_NAME('GMD','GMD_UNEXP_VALUES_FOUND');
1094 FND_MSG_PUB.Add;
1095 RAISE undesired_values_exception;
1096 END IF;
1097
1098 OPEN c_line_type (p_recipe_mtl_rec.formulaline_id);
1099 FETCH c_line_type INTO l_line_type;
1100 CLOSE c_line_type;
1101
1102 IF (l_line_type <> 1) THEN
1103 p_recipe_mtl_rec.MINIMUM_TRANSFER_QTY := null;
1104 p_recipe_mtl_rec.MINIMUM_DELAY := null;
1105 p_recipe_mtl_rec.MAXIMUM_DELAY := null;
1106 END IF;
1107
1108
1109 /* ======================================= */
1110 /* END of Validations so as to insert minimum_transfer_qty,minimum_delay,maximum_delay. Bug : 10109808 */
1111 /* ======================================= */
1112
1113 /* Insert into the recipe materials table */
1114 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1115 GMD_RECIPE_DETAIL_PVT.create_recipe_mtl (p_recipe_mtl_rec => p_recipe_mtl_rec
1116 ,p_recipe_mtl_flex_rec => p_recipe_mtl_flex_rec
1117 ,x_return_status => x_return_status);
1118 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1119 RAISE insert_rcp_mat_failure;
1120 END IF;
1121 END IF;
1122
1123 END LOOP;
1124
1125 IF FND_API.To_Boolean( p_commit ) THEN
1126 Commit;
1127 END IF;
1128
1129 /* Get the message count and information */
1130 FND_MSG_PUB.Count_And_Get (
1131 p_count => x_msg_count,
1132 p_data => x_msg_data );
1133
1134 EXCEPTION
1135 WHEN FND_API.G_EXC_ERROR THEN
1136 ROLLBACK to Insert_Recipe_Materials;
1137 x_return_status := FND_API.G_RET_STS_ERROR;
1138 FND_MSG_PUB.Count_And_Get (
1139 p_count => x_msg_count,
1140 p_data => x_msg_data );
1141 WHEN setup_failure OR insert_rcp_mat_failure THEN
1142 ROLLBACK to Insert_Recipe_Materials;
1143 x_return_status := FND_API.G_RET_STS_ERROR;
1144 fnd_msg_pub.count_and_get (
1145 p_count => x_msg_count
1146 ,p_encoded => FND_API.g_false
1147 ,p_data => x_msg_data);
1148 WHEN negative_values_exception THEN
1149 ROLLBACK to Insert_Recipe_Materials;
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 fnd_msg_pub.count_and_get (
1152 p_count => x_msg_count
1153 ,p_encoded => FND_API.g_false
1154 ,p_data => x_msg_data);
1155 WHEN undesired_values_exception THEN
1156 ROLLBACK to Insert_Recipe_Materials;
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1158 fnd_msg_pub.count_and_get (
1159 p_count => x_msg_count
1160 ,p_encoded => FND_API.g_false
1161 ,p_data => x_msg_data);
1162 WHEN OTHERS THEN
1163 ROLLBACK to Insert_Recipe_Materials;
1164 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 FND_MSG_PUB.Count_And_Get (
1167 p_count => x_msg_count,
1168 p_data => x_msg_data );
1169
1170 END CREATE_RECIPE_MTL;
1171
1172 /* ============================================= */
1173 /* Procedure: */
1174 /* Update_Recipe_Process_Loss */
1175 /* */
1176 /* DESCRIPTION: */
1177 /* This PL/SQL procedure is responsible for */
1178 /* updating recipe process loss */
1179 /* */
1180 /* ============================================= */
1181 /* Start of commments */
1182 /* API name : Update_Recipe_Process_loss */
1183 /* Type : Public */
1184 /* Function : */
1185 /* parameters : */
1186 /* IN : p_api_version IN NUMBER Required */
1187 /* p_init_msg_list IN Varchar2 Optional */
1188 /* p_commit IN Varchar2 Optional */
1189 /* p_recipe_detail_tbl IN Required */
1190 /* */
1191 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1192 /* x_msg_count OUT NOCOPY Number */
1193 /* x_msg_data OUT NOCOPY varchar2(2000) */
1194 /* */
1195 /* Version : Current Version 1.0 */
1196 /* */
1197 /* Notes : p_called_from_forms parameter not currently used */
1198 /* originally included for returning error messages */
1199 /* */
1200 /* End of comments */
1201
1202 PROCEDURE UPDATE_RECIPE_PROCESS_LOSS
1203 (p_api_version IN NUMBER ,
1204 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1205 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1206 p_called_from_forms IN VARCHAR2 := 'NO' ,
1207 x_return_status OUT NOCOPY VARCHAR2 ,
1208 x_msg_count OUT NOCOPY NUMBER ,
1209 x_msg_data OUT NOCOPY VARCHAR2 ,
1210 p_recipe_detail_tbl IN recipe_detail_tbl
1211 ) IS
1212 /* Defining all local variables */
1213 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_PROCESS_LOSS';
1214 l_api_version CONSTANT NUMBER := 1.0;
1215
1216 l_user_id fnd_user.user_id%TYPE := 0;
1217 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1218
1219 /* Variables used for defining status */
1220 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1221 l_return_code NUMBER := 0;
1222
1223 /* Error message count and data */
1224 l_msg_count NUMBER;
1225 l_msg_data VARCHAR2(2000);
1226
1227 CURSOR get_recipe_pr_details(vProcess_loss_id NUMBER) IS
1228 Select *
1229 From gmd_recipe_process_loss
1230 Where Recipe_process_loss_id = VProcess_loss_id;
1231
1232 /* Record types for data manipulation */
1233 p_recipe_pr_loss_rec gmd_recipe_process_loss%ROWTYPE;
1234
1235 p_recipe_detail_rec recipe_dtl;
1236 update_pr_loss_failure EXCEPTION;
1237 setup_failure EXCEPTION;
1238 BEGIN
1239 /* Define Savepoint */
1240 SAVEPOINT Update_Recipe_Process_loss;
1241
1242 /* Standard Check for API compatibility */
1243 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1244 p_api_version ,
1245 l_api_name ,
1246 G_PKG_NAME )
1247 THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250
1251 /* Initialize message list if p_init_msg_list is set to TRUE */
1252 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1253 FND_MSG_PUB.initialize;
1254 END IF;
1255
1256 /* Intialize the setup fields */
1257 IF NOT gmd_api_grp.setup_done THEN
1258 gmd_api_grp.setup_done := gmd_api_grp.setup;
1259 END IF;
1260 IF NOT gmd_api_grp.setup_done THEN
1261 RAISE setup_failure;
1262 END IF;
1263
1264 IF (p_recipe_detail_tbl.Count = 0) THEN
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267
1268 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
1269
1270 /* Initialization of all status */
1271 /* If a record fails in validation we store this message in error stack */
1272 /* and loop thro records */
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 /* Assign each row from the PL/SQL table to a row. */
1276 p_recipe_detail_rec := p_recipe_detail_tbl(i);
1277
1278 /* ================================== */
1279 /* For updates we expect the surrogate */
1280 /* key to be provided */
1281 /* ================================== */
1282 IF (p_recipe_detail_rec.recipe_process_loss_id IS NULL) THEN
1283 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1284 FND_MESSAGE.SET_TOKEN ('MISSING', 'RECIPE_PROCESS_LOSS_ID');
1285 FND_MSG_PUB.ADD;
1286 RAISE update_pr_loss_failure;
1287 END IF;
1288
1289 /* ================================== */
1290 /* Check if recipe id exists */
1291 /* Either recipe_id or recipe_no/vers */
1292 /* has to be provided or process loss id */
1293 /* ================================== */
1294 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1295 OPEN get_recipe_pr_details(p_recipe_detail_rec.recipe_process_loss_id);
1296 FETCH get_recipe_pr_details INTO p_recipe_pr_loss_rec;
1297 CLOSE get_recipe_pr_details;
1298 END IF;
1299
1300 /* Assign all default values */
1301 IF (p_recipe_detail_rec.process_loss = FND_API.G_MISS_NUM) THEN
1302 p_recipe_detail_rec.process_loss := NULL;
1303 ELSIF (p_recipe_detail_rec.process_loss IS NULL) THEN
1304 p_recipe_detail_rec.process_loss := p_recipe_pr_loss_rec.process_loss;
1305 END IF;
1306 /* B6811759 */
1307 IF (p_recipe_detail_rec.fixed_process_loss = FND_API.G_MISS_NUM) THEN
1308 p_recipe_detail_rec.fixed_process_loss := NULL;
1309 ELSIF (p_recipe_detail_rec.fixed_process_loss IS NULL) THEN
1310 p_recipe_detail_rec.fixed_process_loss_uom := p_recipe_pr_loss_rec.fixed_process_loss_uom;
1311 END IF;
1312
1313 IF (p_recipe_detail_rec.fixed_process_loss = FND_API.G_MISS_CHAR) THEN
1314 p_recipe_detail_rec.fixed_process_loss := NULL;
1315 ELSIF (p_recipe_detail_rec.fixed_process_loss IS NULL) THEN
1316 p_recipe_detail_rec.fixed_process_loss_uom := p_recipe_pr_loss_rec.fixed_process_loss_uom;
1317 END IF;
1318
1319
1320
1321 /* Assign contiguous Ind as 0, if it not passed */
1322 IF (p_recipe_detail_rec.contiguous_ind IS NULL) THEN
1323 p_recipe_detail_rec.contiguous_ind := 0;
1324 END IF;
1325
1326 IF (p_recipe_detail_rec.organization_id IS NULL) THEN
1327 p_recipe_detail_rec.organization_id := p_recipe_pr_loss_rec.organization_id;
1328 END IF;
1329
1330 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1331 p_recipe_detail_rec.recipe_id := p_recipe_pr_loss_rec.recipe_id;
1332 END IF;
1333
1334 IF (p_recipe_detail_rec.text_code IS NULL) THEN
1335 p_recipe_detail_rec.text_code := p_recipe_pr_loss_rec.text_code;
1336 END IF;
1337
1338 /* Validate if this Recipe can be modified by this user */
1339 /* Recipe Security fix */
1340 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1341 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
1342 RAISE update_pr_loss_failure;
1343 END IF;
1344
1345 IF NOT GMD_API_GRP.OrgnAccessible(powner_orgn_id => p_recipe_detail_rec.organization_id) THEN
1346 RAISE update_pr_loss_failure;
1347 END IF;
1348
1349 /* Update into the recipe process loss table */
1350 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1351 GMD_RECIPE_DETAIL_PVT.update_recipe_process_loss (p_recipe_detail_rec => p_recipe_detail_rec
1352 ,x_return_status => x_return_status);
1353 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1354 RAISE update_pr_loss_failure;
1355 END IF;
1356 END IF;
1357
1358 END LOOP;
1359
1360 IF FND_API.To_Boolean( p_commit ) THEN
1361 Commit;
1362 END IF;
1363
1364 /* Get the message count and information */
1365 FND_MSG_PUB.Count_And_Get (
1366 p_count => x_msg_count,
1367 p_data => x_msg_data );
1368
1369 EXCEPTION
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 ROLLBACK to Update_Recipe_Process_loss;
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373 FND_MSG_PUB.Count_And_Get (
1374 p_count => x_msg_count,
1375 p_data => x_msg_data );
1376
1377 WHEN setup_failure OR update_pr_loss_failure THEN
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379 ROLLBACK to Update_Recipe_Process_loss;
1380 fnd_msg_pub.count_and_get (
1381 p_count => x_msg_count
1382 ,p_encoded => FND_API.g_false
1383 ,p_data => x_msg_data);
1384 WHEN OTHERS THEN
1385 ROLLBACK to Update_Recipe_Process_loss;
1386 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388 FND_MSG_PUB.Count_And_Get (
1389 p_count => x_msg_count,
1390 p_data => x_msg_data );
1391
1392 END UPDATE_RECIPE_PROCESS_LOSS;
1393
1394 /* ============================================= */
1395 /* Procedure: */
1396 /* Update_Recipe_Customers */
1397 /* */
1398 /* DESCRIPTION: */
1399 /* This PL/SQL procedure is responsible for */
1400 /* updating recipe process loss */
1401 /* */
1402 /* ============================================= */
1403 /* Start of commments */
1404 /* API name : Update_Recipe_Customers */
1405 /* Type : Public */
1406 /* Function : */
1407 /* parameters : */
1408 /* IN : p_api_version IN NUMBER Required */
1409 /* p_init_msg_list IN Varchar2 Optional */
1410 /* p_commit IN Varchar2 Optional */
1411 /* p_recipe_detail_tbl IN Required */
1412 /* */
1413 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1414 /* x_msg_count OUT NOCOPY Number */
1415 /* x_msg_data OUT NOCOPY varchar2(2000) */
1416 /* */
1417 /* Version : Current Version 1.0 */
1418 /* */
1419 /* Notes : p_called_from_forms parameter not currently used */
1420 /* originally included for returning error messages */
1421 /* */
1422 /* End of comments */
1423
1424 PROCEDURE UPDATE_RECIPE_CUSTOMERS
1425 (p_api_version IN NUMBER ,
1426 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1427 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1428 p_called_from_forms IN VARCHAR2 := 'NO' ,
1429 x_return_status OUT NOCOPY VARCHAR2 ,
1430 x_msg_count OUT NOCOPY NUMBER ,
1431 x_msg_data OUT NOCOPY VARCHAR2 ,
1432 p_recipe_detail_tbl IN recipe_detail_tbl
1433 ) IS
1434 /* Defining all local variables */
1435 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_CUSTOMERS';
1436 l_api_version CONSTANT NUMBER := 1.0;
1437
1438 l_user_id fnd_user.user_id%TYPE := 0;
1439 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1440 l_customer_id NUMBER := 0;
1441 l_site_id NUMBER := 0;
1442 l_org_id NUMBER := 0;
1443
1444
1445 /* Variables used for defining status */
1446 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1447 l_return_code NUMBER := 0;
1448
1449 /* Error message count and data */
1450 l_msg_count NUMBER;
1451 l_msg_data VARCHAR2(2000);
1452
1453
1454 Cursor get_rc_text_code(rc_id NUMBER, Cust_id NUMBER) IS
1455 Select text_code
1456 from gmd_recipe_customers
1457 where recipe_id = rc_id
1458 and customer_id = cust_id;
1459
1460 /* Record types for data manipulation */
1461 p_recipe_detail_rec recipe_dtl;
1462
1463 setup_failure EXCEPTION;
1464 update_rcp_cust_failure EXCEPTION;
1465
1466 BEGIN
1467 /* Define Savepoint */
1468 SAVEPOINT Update_Recipe_Customers;
1469
1470 /* Standard Check for API compatibility */
1471 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1472 p_api_version ,
1473 l_api_name ,
1474 G_PKG_NAME )
1475 THEN
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 END IF;
1478
1479 /* Initialize message list if p_init_msg_list is set to TRUE */
1480 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1481 FND_MSG_PUB.initialize;
1482 END IF;
1483
1484 /* Intialize the setup fields */
1485 IF NOT gmd_api_grp.setup_done THEN
1486 gmd_api_grp.setup_done := gmd_api_grp.setup;
1487 END IF;
1488 IF NOT gmd_api_grp.setup_done THEN
1489 RAISE setup_failure;
1490 END IF;
1491
1492 IF (p_recipe_detail_tbl.Count = 0) THEN
1493 RAISE FND_API.G_EXC_ERROR;
1494 END IF;
1495
1496 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
1497
1498 /* Initialization of all status */
1499 /* If a record fails in validation we store this message in error stack */
1500 /* and loop thro records */
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502
1503 /* Assign each row from the PL/SQL table to a row. */
1504 p_recipe_detail_rec := p_recipe_detail_tbl(i);
1505
1506 /* ================================ */
1507 /* Check if recipe id exists */
1508 /* ================================= */
1509 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
1510 GMD_RECIPE_VAL.recipe_name
1511 ( p_api_version => 1.0,
1512 p_init_msg_list => FND_API.G_FALSE,
1513 p_commit => FND_API.G_FALSE,
1514 p_recipe_no => p_recipe_detail_rec.recipe_no,
1515 p_recipe_version => p_recipe_detail_rec.recipe_version,
1516 x_return_status => l_return_status,
1517 x_msg_count => l_msg_count,
1518 x_msg_data => l_msg_data,
1519 x_return_code => l_return_code,
1520 x_recipe_id => l_recipe_id);
1521
1522 IF (l_recipe_id IS NULL) THEN
1523 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
1524 FND_MSG_PUB.ADD;
1525 RAISE update_rcp_cust_failure;
1526 ELSE
1527 p_recipe_detail_rec.recipe_id := l_recipe_id;
1528 END IF;
1529 END IF;
1530
1531 /* Validate if this Recipe can be modified by this user */
1532 /* Recipe Security fix */
1533 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1534 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
1535 RAISE update_rcp_cust_failure;
1536 END IF;
1537
1538 /* ======================================= */
1539 /* Based on the customer no, Check if this */
1540 /* is a valid customer */
1541 /* ======================================= */
1542 IF (p_recipe_detail_rec.customer_id IS NULL) THEN
1543 GMD_COMMON_VAL.get_customer_id
1544 ( PCUSTOMER_NO => p_recipe_detail_rec.customer_no,
1545 XCUST_ID => l_customer_id,
1546 XSITE_ID => l_site_id,
1547 XORG_ID => l_org_id,
1548 XRETURN_CODE => l_return_code);
1549
1550 IF (l_customer_id IS NULL) THEN
1551 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_CUSTOMER_INVALID');
1552 FND_MSG_PUB.ADD;
1553 RAISE update_rcp_cust_failure;
1554 ELSE
1555 p_recipe_detail_rec.customer_id := l_customer_id;
1556 END IF;
1557 END IF;
1558
1559 /* Only updateable field is text code */
1560 IF (p_recipe_detail_rec.text_Code IS NULL) THEN
1561 OPEN get_rc_text_code(p_recipe_detail_rec.recipe_id,
1562 p_recipe_detail_rec.customer_id);
1563 FETCH get_rc_text_code INTO p_recipe_detail_rec.text_code;
1564 CLOSE get_rc_text_code;
1565 ELSIF (p_recipe_detail_rec.text_Code = fnd_Api.g_miss_char) THEN
1566 p_recipe_detail_rec.text_code := null;
1567 END IF;
1568
1569 /* Update the recipe customer table */
1570 /* only who columns needs to be updated */
1571 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1572 GMD_RECIPE_DETAIL_PVT.update_recipe_customers (p_recipe_detail_rec => p_recipe_detail_rec
1573 ,x_return_status => x_return_status);
1574 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1575 RAISE update_rcp_cust_failure;
1576 END IF;
1577 END IF;
1578
1579 END LOOP;
1580 IF FND_API.To_Boolean( p_commit ) THEN
1581 Commit;
1582 END IF;
1583
1584 /* Get the message count and information */
1585 FND_MSG_PUB.Count_And_Get (
1586 p_count => x_msg_count,
1587 p_data => x_msg_data );
1588
1589 EXCEPTION
1590 WHEN FND_API.G_EXC_ERROR THEN
1591 ROLLBACK to Update_Recipe_Customers;
1592 x_return_status := FND_API.G_RET_STS_ERROR;
1593 FND_MSG_PUB.Count_And_Get (
1594 p_count => x_msg_count,
1595 p_data => x_msg_data );
1596
1597 WHEN setup_failure OR update_rcp_cust_failure THEN
1598 ROLLBACK to Update_Recipe_Customers;
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600 fnd_msg_pub.count_and_get (
1601 p_count => x_msg_count
1602 ,p_encoded => FND_API.g_false
1603 ,p_data => x_msg_data);
1604 WHEN OTHERS THEN
1605 ROLLBACK to Update_Recipe_Customers;
1606 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1608 FND_MSG_PUB.Count_And_Get (
1609 p_count => x_msg_count,
1610 p_data => x_msg_data );
1611
1612 END UPDATE_RECIPE_CUSTOMERS;
1613
1614 /* ============================================= */
1615 /* Procedure: */
1616 /* Update_Recipe_VR */
1617 /* */
1618 /* DESCRIPTION: */
1619 /* This PL/SQL procedure is responsible for */
1620 /* updating recipe Validity Rules */
1621 /* */
1622 /* ============================================= */
1623 /* Start of commments */
1624 /* API name : Update_Recipe_VR */
1625 /* Type : Public */
1626 /* Function : */
1627 /* parameters : */
1628 /* IN : p_api_version IN NUMBER Required */
1629 /* p_init_msg_list IN Varchar2 Optional */
1630 /* p_commit IN Varchar2 Optional */
1631 /* p_recipe_detail_tbl IN Required */
1632 /* */
1633 /* OUT x_return_status OUT NOCOPY varchar2(1) */
1634 /* x_msg_count OUT NOCOPY Number */
1635 /* x_msg_data OUT NOCOPY varchar2(2000) */
1636 /* */
1637 /* Version : Current Version 1.0 */
1638 /* */
1639 /* Notes : p_called_from_forms parameter not currently used */
1640 /* originally included for returning error messages */
1641 /* */
1642 /* End of comments */
1643 PROCEDURE UPDATE_RECIPE_VR
1644 ( p_api_version IN NUMBER
1645 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1646 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1647 ,p_called_from_forms IN VARCHAR2 := 'NO'
1648 ,x_return_status OUT NOCOPY VARCHAR2
1649 ,x_msg_count OUT NOCOPY NUMBER
1650 ,x_msg_data OUT NOCOPY VARCHAR2
1651 ,p_recipe_vr_tbl IN recipe_vr_tbl
1652 ,p_recipe_update_flex IN recipe_update_flex
1653 ) IS
1654 /* Define all variables specific to this procedure */
1655 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_VR';
1656 l_api_version CONSTANT NUMBER := 2.0;
1657
1658 l_user_id fnd_user.user_id%TYPE;
1659 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
1660
1661 /* Variables used for defining status */
1662 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1663 l_return_code NUMBER := 0;
1664 l_plant_ind NUMBER;
1665
1666 /* Error message count and data */
1667 l_msg_count NUMBER;
1668 l_msg_data VARCHAR2(2000);
1669
1670 /* Record types for data manipulation */
1671 p_recipe_vr_rec RECIPE_VR;
1672
1673 p_flex_update_rec UPDATE_FLEX;
1674 /* used for g_miss_char logic */
1675 l_flex_update_rec update_flex;
1676
1677 /* Define a cursor for dealing with updates */
1678 CURSOR Flex_cur(vRecipe_VR_id NUMBER) IS
1679 SELECT attribute_category, attribute1, attribute2, attribute3, attribute4,
1680 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
1681 attribute11, attribute12, attribute13, attribute14, attribute15,
1682 attribute16, attribute17, attribute18, attribute19, attribute20,
1683 attribute21, attribute22, attribute23, attribute24,attribute25,
1684 attribute26, attribute27, attribute28, attribute29, attribute30
1685 FROM gmd_recipe_validity_rules
1686 WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
1687
1688
1689 /* Define a cursor for dealing with updates */
1690 CURSOR update_vr_cur(vRecipe_VR_id NUMBER) IS
1691 SELECT recipe_id, orgn_code, end_date, planned_process_loss
1692 FROM gmd_recipe_validity_rules
1693 WHERE Recipe_Validity_Rule_id = NVL(vRecipe_VR_id,-1);
1694
1695 /* Cursor to get the item id when item no is passed */
1696 CURSOR get_item_id(pItem_no VARCHAR2) IS
1697 SELECT inventory_item_id
1698 FROM mtl_system_items_kfv
1699 WHERE concatenated_segments = pItem_no;
1700 -- And delete_mark = 0;
1701
1702 Update_VR_Failure EXCEPTION;
1703 setup_failure EXCEPTION;
1704
1705 -- bug16204615
1706 invalid_vr_detail_uom EXCEPTION;
1707 l_vr_detail_um VARCHAR2(32);
1708 CURSOR check_vr_detail_uom(vDetail_uom VARCHAR2) IS
1709 SELECT uom_code
1710 FROM mtl_units_of_measure_vl
1711 WHERE uom_code = vDetail_uom;
1712
1713 BEGIN
1714 /* Define Savepoint */
1715 SAVEPOINT Update_Recipe_VR;
1716
1717 /* Standard Check for API compatibility */
1718 IF NOT FND_API.Compatible_API_Call ( l_api_version
1719 ,p_api_version
1720 ,l_api_name
1721 ,G_PKG_NAME )
1722 THEN
1723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1724 END IF;
1725
1726 /* Initialize message list if p_init_msg_list is set to TRUE */
1727 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1728 FND_MSG_PUB.initialize;
1729 END IF;
1730
1731 /* Intialize the setup fields */
1732 IF NOT gmd_api_grp.setup_done THEN
1733 gmd_api_grp.setup_done := gmd_api_grp.setup;
1734 END IF;
1735 IF NOT gmd_api_grp.setup_done THEN
1736 RAISE setup_failure;
1737 END IF;
1738
1739 /* Initialization of all status */
1740 /* If a record fails in validation we store this message in error stack */
1741 /* and loop thro records */
1742 x_return_status := FND_API.G_RET_STS_SUCCESS;
1743
1744 FOR i IN 1 .. p_recipe_vr_tbl.count LOOP
1745 BEGIN
1746 /* Assign each row from the PL/SQL table to a row. */
1747 p_recipe_vr_rec := p_recipe_vr_tbl(i);
1748
1749 /* ======================================== */
1750 /* Send an error message if surrogate key */
1751 /* value is not provided */
1752 /* ======================================== */
1753 If (p_recipe_vr_rec.recipe_validity_rule_id IS NULL) THEN
1754 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1755 FND_MESSAGE.SET_TOKEN ('MISSING', 'RECIPE_VALIDITY_RULE_ID');
1756 FND_MSG_PUB.ADD;
1757 RAISE Update_VR_Failure;
1758 END IF;
1759
1760 -- bug16204615
1761 IF p_recipe_vr_rec.detail_uom is not null THEN
1762 OPEN check_vr_detail_uom(p_recipe_vr_rec.detail_uom);
1763 FETCH check_vr_detail_uom INTO l_vr_detail_um;
1764 IF check_vr_detail_uom%NOTFOUND THEN
1765 CLOSE check_vr_detail_uom;
1766 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_VR_DETAIL_UOM');
1767 FND_MESSAGE.SET_TOKEN ('DETAIL_UOM', p_recipe_vr_rec.detail_uom);
1768 FND_MSG_PUB.ADD;
1769 raise invalid_vr_detail_uom;
1770 END IF;
1771 CLOSE check_vr_detail_uom;
1772 END IF;
1773
1774 /* Thomas Daniel - Bug 2652200 */
1775 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
1776 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
1777 /* as the user is intending to update the field to NULL */
1778 FOR update_rec IN update_vr_Cur(p_recipe_vr_rec.recipe_validity_rule_id) LOOP
1779 IF (p_recipe_vr_rec.orgn_code = FND_API.G_MISS_CHAR) THEN
1780 p_recipe_vr_rec.orgn_code := NULL;
1781 ELSIF (p_recipe_vr_rec.orgn_code IS NULL) THEN
1782 p_recipe_vr_rec.orgn_code := update_rec.orgn_code;
1783 END IF;
1784
1785 IF (p_recipe_vr_rec.planned_process_loss = FND_API.G_MISS_NUM) THEN
1786 p_recipe_vr_rec.planned_process_loss := NULL;
1787 ELSIF (p_recipe_vr_rec.planned_process_loss IS NULL) THEN
1788 p_recipe_vr_rec.planned_process_loss := update_rec.planned_process_loss;
1789 END IF;
1790
1791 IF (p_recipe_vr_rec.end_date = FND_API.G_MISS_DATE) THEN
1792 p_recipe_vr_rec.end_date := NULL;
1793 ELSIF (p_recipe_vr_rec.end_date IS NULL) THEN
1794 p_recipe_vr_rec.end_date := update_rec.end_date;
1795 END IF;
1796
1797 IF (p_recipe_vr_rec.recipe_id IS NULL) THEN
1798 p_recipe_vr_rec.recipe_id := update_rec.recipe_id;
1799 END IF;
1800 END LOOP;
1801
1802 /* Validate if this Recipe can be modified by this user */
1803 /* Recipe Security fix */
1804 --Commented the code vr security will be based on recipe owner orgn code
1805 /*IF NOT GMD_API_GRP.isUserOrgnAccessible(powner_id => gmd_api_grp.user_id
1806 ,powner_orgn => p_recipe_vr_rec.orgn_code) THEN
1807 RAISE Update_VR_Failure;
1808 END IF;*/
1809
1810 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
1811 ,Entity_id => p_recipe_vr_rec.recipe_id) THEN
1812 RAISE Update_VR_Failure;
1813 END IF;
1814
1815 /* VR Security fix */
1816 --Commented the code vr security will be based on recipe owner orgn code
1817 /*IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'VALIDITY'
1818 ,Entity_id => p_recipe_vr_rec.recipe_validity_rule_id)
1819 THEN
1820 RAISE Update_VR_Failure;
1821 END IF;*/
1822
1823 /* ========================================= */
1824 /* Get item id if user passes in the */
1825 /* Item no */
1826 /* ========================================= */
1827 IF p_recipe_vr_rec.item_no IS NOT NULL THEN
1828 OPEN get_item_id(p_recipe_vr_rec.Item_no);
1829 FETCH get_item_id INTO p_recipe_vr_rec.inventory_item_id;
1830 IF get_item_id%NOTFOUND THEN
1831 CLOSE get_item_id;
1832 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1833 FND_MESSAGE.SET_TOKEN ('MISSING', 'ITEM_ID');
1834 FND_MSG_PUB.ADD;
1835 RAISE Update_VR_Failure;
1836 END IF;
1837 CLOSE get_item_id;
1838 END IF;
1839
1840 OPEN Flex_cur(p_recipe_vr_rec.recipe_validity_rule_id);
1841 FETCH Flex_cur INTO l_flex_update_rec;
1842 CLOSE Flex_cur;
1843
1844 /* If no flex field is updated retain the old values */
1845 IF (p_recipe_update_flex.count = 0) THEN
1846 p_flex_update_rec := l_flex_update_rec;
1847 ELSE
1848 p_flex_update_rec := p_recipe_update_flex(i);
1849 END IF;
1850
1851 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1852 p_flex_update_rec.attribute1 := NULL;
1853 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
1854 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
1855 END IF;
1856
1857 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1858 p_flex_update_rec.attribute2 := NULL;
1859 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
1860 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
1861 END IF;
1862
1863 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1864 p_flex_update_rec.attribute3 := NULL;
1865 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
1866 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
1867 END IF;
1868
1869 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1870 p_flex_update_rec.attribute4 := NULL;
1871 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
1872 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
1873 END IF;
1874
1875 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1876 p_flex_update_rec.attribute5 := NULL;
1877 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
1878 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
1879 END IF;
1880
1881 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1882 p_flex_update_rec.attribute6 := NULL;
1883 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
1884 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
1885 END IF;
1886
1887 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1888 p_flex_update_rec.attribute7 := NULL;
1889 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
1890 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
1891 END IF;
1892
1893 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1894 p_flex_update_rec.attribute8 := NULL;
1895 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
1896 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
1897 END IF;
1898
1899 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1900 p_flex_update_rec.attribute9 := NULL;
1901 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
1902 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
1903 END IF;
1904
1905 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1906 p_flex_update_rec.attribute10 := NULL;
1907 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
1908 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
1909 END IF;
1910
1911 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1912 p_flex_update_rec.attribute11 := NULL;
1913 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
1914 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
1915 END IF;
1916
1917 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1918 p_flex_update_rec.attribute12 := NULL;
1919 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
1920 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
1921 END IF;
1922
1923 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1924 p_flex_update_rec.attribute13 := NULL;
1925 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
1926 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
1927 END IF;
1928
1929 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1930 p_flex_update_rec.attribute14 := NULL;
1931 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
1932 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
1933 END IF;
1934
1935 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1936 p_flex_update_rec.attribute15 := NULL;
1937 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
1938 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
1939 END IF;
1940
1941 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1942 p_flex_update_rec.attribute16 := NULL;
1943 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
1944 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
1945 END IF;
1946
1947 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1948 p_flex_update_rec.attribute17 := NULL;
1949 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
1950 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
1951 END IF;
1952
1953 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1954 p_flex_update_rec.attribute18 := NULL;
1955 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
1956 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
1957 END IF;
1958
1959 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1960 p_flex_update_rec.attribute19 := NULL;
1961 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
1962 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
1963 END IF;
1964
1965 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1966 p_flex_update_rec.attribute20 := NULL;
1967 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
1968 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
1969 END IF;
1970
1971 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1972 p_flex_update_rec.attribute21 := NULL;
1973 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
1974 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
1975 END IF;
1976
1977 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1978 p_flex_update_rec.attribute22 := NULL;
1979 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
1980 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
1981 END IF;
1982
1983 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1984 p_flex_update_rec.attribute23 := NULL;
1985 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
1986 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
1987 END IF;
1988
1989 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1990 p_flex_update_rec.attribute24 := NULL;
1991 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
1992 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
1993 END IF;
1994
1995 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1996 p_flex_update_rec.attribute25 := NULL;
1997 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
1998 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
1999 END IF;
2000
2001 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
2002 p_flex_update_rec.attribute26 := NULL;
2003 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
2004 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
2005 END IF;
2006
2007 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
2008 p_flex_update_rec.attribute27 := NULL;
2009 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
2010 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
2011 END IF;
2012
2013 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
2014 p_flex_update_rec.attribute28 := NULL;
2015 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
2016 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
2017 END IF;
2018
2019 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
2020 p_flex_update_rec.attribute29 := NULL;
2021 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
2022 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
2023 END IF;
2024
2025 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
2026 p_flex_update_rec.attribute30 := NULL;
2027 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
2028 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
2029 END IF;
2030
2031 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
2032 p_flex_update_rec.attribute_category := NULL;
2033 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
2034 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
2035 END IF;
2036
2037 /* Update recipe validity rules table */
2038 GMD_RECIPE_DETAIL_PVT.UPDATE_RECIPE_VR(p_recipe_vr_rec => p_recipe_vr_rec
2039 ,p_flex_update_rec => p_flex_update_rec
2040 ,x_return_status => x_return_status);
2041 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2042 RAISE Update_VR_Failure;
2043 END IF;
2044
2045 EXCEPTION
2046 WHEN Update_VR_Failure THEN
2047 x_return_status := FND_API.G_RET_STS_ERROR;
2048 /* Get the message count and information */
2049 FND_MSG_PUB.Count_And_Get (
2050 p_count => x_msg_count
2051 ,p_data => x_msg_data );
2052 -- bug16204615
2053 WHEN invalid_vr_detail_uom THEN
2054 x_return_status := FND_API.G_RET_STS_ERROR;
2055 FND_MSG_PUB.Count_And_Get (
2056 p_count => x_msg_count,
2057 p_data => x_msg_data );
2058 END;
2059 END LOOP; -- Loops thro all VR that needs to be updated
2060
2061 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2062 IF FND_API.To_Boolean( p_commit ) THEN
2063 Commit;
2064 END IF;
2065 END IF;
2066
2067 EXCEPTION
2068 WHEN FND_API.G_EXC_ERROR THEN
2069 ROLLBACK to Update_Recipe_VR;
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071 FND_MSG_PUB.Count_And_Get (
2072 p_count => x_msg_count,
2073 p_data => x_msg_data );
2074 WHEN setup_failure THEN
2075 ROLLBACK to Update_Recipe_VR;
2076 x_return_status := FND_API.G_RET_STS_ERROR;
2077 fnd_msg_pub.count_and_get (
2078 p_count => x_msg_count
2079 ,p_encoded => FND_API.g_false
2080 ,p_data => x_msg_data);
2081 WHEN OTHERS THEN
2082 ROLLBACK to Update_Recipe_VR;
2083 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
2084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2085 FND_MSG_PUB.Count_And_Get (
2086 p_count => x_msg_count,
2087 p_data => x_msg_data );
2088
2089 END UPDATE_RECIPE_VR;
2090
2091
2092 /* ============================================= */
2093 /* Procedure: */
2094 /* Recipe_Routing_Steps */
2095 /* */
2096 /* DESCRIPTION: */
2097 /* This PL/SQL procedure is responsible for */
2098 /* inserting and updating recipe Routing steps */
2099 /* */
2100 /* ============================================= */
2101 /* Start of commments */
2102 /* API name : Recipe_Routing_Steps */
2103 /* Type : Public */
2104 /* Function : */
2105 /* parameters : */
2106 /* p_called_from_forms parameter not currently used */
2107 /* originally included for returning error messages */
2108 /* IN : p_api_version IN NUMBER Required */
2109 /* p_init_msg_list IN Varchar2 Optional */
2110 /* p_commit IN Varchar2 Optional */
2111 /* p_recipe_detail_tbl IN Required */
2112 /* */
2113 /* OUT x_return_status OUT NOCOPY varchar2(1) */
2114 /* x_msg_count OUT NOCOPY Number */
2115 /* x_msg_data OUT NOCOPY varchar2(2000) */
2116 /* */
2117 /* Version : Current Version 1.1 */
2118 /* */
2119 /* Notes : 24Jul2001 L.R.Jackson Added mass and volume fields. */
2120 /* Changed routing step id validation */
2121 /* Increased the version to 1.1 */
2122 /* Removed the detail record. Just use table(i) */
2123 /* Removed check of user id/user name. There is */
2124 /* no userid in this table. WHO columns are */
2125 /* passed in, not derived here. */
2126 /* Changed call to RECIPE_NAME to RECIPE_EXISTS. */
2127 /* */
2128 /* End of comments */
2129
2130 PROCEDURE RECIPE_ROUTING_STEPS
2131 ( p_api_version IN NUMBER ,
2132 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2133 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2134 p_called_from_forms IN VARCHAR2 := 'NO' ,
2135 x_return_status OUT NOCOPY VARCHAR2 ,
2136 x_msg_count OUT NOCOPY NUMBER ,
2137 x_msg_data OUT NOCOPY VARCHAR2 ,
2138 p_recipe_detail_tbl IN recipe_detail_tbl ,
2139 p_recipe_insert_flex IN recipe_flex ,
2140 p_recipe_update_flex IN recipe_update_flex
2141 ) IS
2142
2143 /* Define all variables specific to this procedure */
2144 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ROUTING_STEPS';
2145 l_api_version CONSTANT NUMBER := 2.0;
2146
2147 l_user_id fnd_user.user_id%TYPE := 0;
2148 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
2149
2150 /* Variables used for defining status */
2151 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
2152 l_return_code NUMBER := 0;
2153
2154 /* Error message count and data */
2155 l_msg_count NUMBER;
2156 l_msg_data VARCHAR2(2000);
2157
2158 /* Record types for data manipulation */
2159 p_recipe_detail_rec recipe_dtl;
2160
2161 /* flex field records for inserts and updates */
2162 p_flex_insert_rec flex;
2163 p_flex_update_rec update_flex;
2164
2165 /* used for g_miss_char logic */
2166 l_flex_update_rec update_flex;
2167
2168 /* Define a cursor for dealing with updates */
2169 CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
2170 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
2171 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
2172 attribute11, attribute12, attribute13, attribute14, attribute15,
2173 attribute16, attribute17, attribute18, attribute19, attribute20,
2174 attribute21, attribute22, attribute23, attribute24,attribute25,
2175 attribute26, attribute27, attribute28, attribute29, attribute30
2176 From gmd_recipe_routing_steps
2177 where recipe_id = NVL(vRecipe_id,-1) AND
2178 RoutingStep_id = NVL(vRoutingStep_id,-1);
2179
2180 CURSOR update_rt_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
2181 Select mass_qty, volume_qty, mass_std_uom, volume_std_uom
2182 From gmd_recipe_routing_steps
2183 where recipe_id = NVL(vRecipe_id,-1) AND
2184 RoutingStep_id = NVL(vRoutingStep_id,-1);
2185
2186 setup_failure EXCEPTION;
2187
2188 BEGIN
2189 /* Updating recipe routing step for first time is in fact inserting a new record */
2190 /* in gmd_recipe_routing_step table. [Form initially shows values from */
2191 /* fm_rout_dtl. When user "changes" values, they are saved in recipe table.] */
2192
2193 /* Define Savepoint */
2194 SAVEPOINT Recipe_Routing_Steps;
2195
2196 /* Standard Check for API compatibility */
2197 IF NOT FND_API.Compatible_API_Call
2198 ( l_api_version ,
2199 p_api_version ,
2200 l_api_name ,
2201 G_PKG_NAME )
2202 THEN
2203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204 END IF;
2205
2206 /* Initialize message list if p_init_msg_list is set to TRUE */
2207 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2208 FND_MSG_PUB.initialize;
2209 END IF;
2210
2211 /* Intialize the setup fields */
2212 IF NOT gmd_api_grp.setup_done THEN
2213 gmd_api_grp.setup_done := gmd_api_grp.setup;
2214 END IF;
2215 IF NOT gmd_api_grp.setup_done THEN
2216 RAISE setup_failure;
2217 END IF;
2218
2219 IF (p_recipe_detail_tbl.Count = 0) THEN
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222
2223 /* Initialization of status. */
2224 /* If a record fails in validation we store the message in error stack */
2225 /* and continue to loop through records */
2226 x_return_status := FND_API.G_RET_STS_SUCCESS;
2227
2228 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
2229
2230 /* Assign each row from the PL/SQL table to a row. */
2231 p_recipe_detail_rec := p_recipe_detail_tbl(i);
2232
2233 /* ========================== */
2234 /* Check if recipe id exists */
2235 /* ========================== */
2236 GMD_RECIPE_VAL.recipe_exists
2237 ( p_api_version => 1.0,
2238 p_init_msg_list => FND_API.G_FALSE,
2239 p_commit => FND_API.G_FALSE,
2240 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
2241 p_recipe_id => p_recipe_detail_tbl(i).recipe_id,
2242 p_recipe_no => p_recipe_detail_tbl(i).recipe_no,
2243 p_recipe_version => p_recipe_detail_tbl(i).recipe_version,
2244 x_return_status => l_return_status,
2245 x_msg_count => l_msg_count,
2246 x_msg_data => l_msg_data,
2247 x_return_code => l_return_code,
2248 x_recipe_id => l_recipe_id);
2249
2250 IF (l_recipe_id IS NULL) OR x_return_status <> 'S' THEN
2251 x_return_status := FND_API.G_RET_STS_ERROR;
2252 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
2253 FND_MSG_PUB.ADD;
2254 RAISE FND_API.G_EXC_ERROR;
2255 ELSE
2256 p_recipe_detail_rec.recipe_id := l_recipe_id;
2257 END IF;
2258
2259 /* Validate if this Recipe can be modified by this user */
2260 /* Recipe Security fix */
2261 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
2262 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
2263 RAISE FND_API.G_EXC_ERROR;
2264 END IF;
2265
2266 IF (p_recipe_detail_tbl(i).routingstep_id IS NULL) THEN
2267 x_return_status := FND_API.G_RET_STS_ERROR;
2268 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
2269 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
2270 FND_MSG_PUB.ADD;
2271 END IF;
2272
2273 IF (p_recipe_insert_flex.count = 0) THEN
2274 p_flex_insert_rec := NULL;
2275 ELSE
2276 p_flex_insert_rec := p_recipe_insert_flex(i);
2277 END IF;
2278
2279 FOR update_rec IN update_rt_cur(p_recipe_detail_rec.recipe_id,
2280 p_recipe_detail_tbl(i).routingstep_id)
2281 LOOP
2282
2283 IF (p_recipe_detail_rec.mass_qty = FND_API.G_MISS_NUM) THEN
2284 p_recipe_detail_rec.mass_qty := NULL;
2285 ELSIF (p_recipe_detail_rec.mass_qty IS NULL) THEN
2286 p_recipe_detail_rec.mass_qty := update_rec.mass_qty;
2287 END IF;
2288
2289 IF (p_recipe_detail_rec.volume_qty = FND_API.G_MISS_NUM) THEN
2290 p_recipe_detail_rec.volume_qty := NULL;
2291 ELSIF (p_recipe_detail_rec.volume_qty IS NULL) THEN
2292 p_recipe_detail_rec.volume_qty := update_rec.volume_qty;
2293 END IF;
2294
2295 IF (p_recipe_detail_rec.mass_std_uom = FND_API.G_MISS_CHAR) THEN
2296 p_recipe_detail_rec.mass_std_uom := NULL;
2297 ELSIF (p_recipe_detail_rec.mass_std_uom IS NULL) THEN
2298 p_recipe_detail_rec.mass_std_uom := update_rec.mass_std_uom;
2299 END IF;
2300
2301 IF (p_recipe_detail_rec.volume_std_uom = FND_API.G_MISS_CHAR) THEN
2302 p_recipe_detail_rec.volume_std_uom := NULL;
2303 ELSIF (p_recipe_detail_rec.volume_std_uom IS NULL) THEN
2304 p_recipe_detail_rec.volume_std_uom := update_rec.volume_std_uom;
2305 END IF;
2306
2307 END LOOP;
2308
2309 /* Assign flex fields */
2310 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,p_recipe_detail_tbl(i).routingstep_id);
2311 FETCH Flex_cur INTO l_flex_update_rec;
2312 IF Flex_cur%FOUND THEN
2313 /* If no flex field is updated retain the old values */
2314 IF (p_recipe_update_flex.count = 0) THEN
2315 p_flex_update_rec := l_flex_update_rec;
2316 ELSE
2317 p_flex_update_rec := p_recipe_update_flex(i);
2318
2319 /* ================================ */
2320 /* Check for all G_MISS_CHAR values */
2321 /* for nullable fields in */
2322 /* gmd_recipe_routing_steps table */
2323 /* ================================= */
2324
2325 /* Thomas Daniel - Bug 2652200 */
2326 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
2327 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
2328 /* as the user is intending to update the field to NULL */
2329 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
2330 p_flex_update_rec.attribute1 := NULL;
2331 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
2332 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
2333 END IF;
2334
2335 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
2336 p_flex_update_rec.attribute2 := NULL;
2337 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
2338 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
2339 END IF;
2340
2341 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
2342 p_flex_update_rec.attribute3 := NULL;
2343 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
2344 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
2345 END IF;
2346
2347 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
2348 p_flex_update_rec.attribute4 := NULL;
2349 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
2350 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
2351 END IF;
2352
2353 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
2354 p_flex_update_rec.attribute5 := NULL;
2355 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
2356 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
2357 END IF;
2358
2359 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
2360 p_flex_update_rec.attribute6 := NULL;
2361 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
2362 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
2363 END IF;
2364
2365 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
2366 p_flex_update_rec.attribute7 := NULL;
2367 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
2368 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
2369 END IF;
2370
2371 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
2372 p_flex_update_rec.attribute8 := NULL;
2373 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
2374 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
2375 END IF;
2376
2377 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
2378 p_flex_update_rec.attribute9 := NULL;
2379 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
2380 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
2381 END IF;
2382
2383 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
2384 p_flex_update_rec.attribute10 := NULL;
2385 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
2386 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
2387 END IF;
2388
2389 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
2390 p_flex_update_rec.attribute11 := NULL;
2391 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
2392 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
2393 END IF;
2394
2395 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
2396 p_flex_update_rec.attribute12 := NULL;
2397 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
2398 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
2399 END IF;
2400
2401 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
2402 p_flex_update_rec.attribute13 := NULL;
2403 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
2404 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
2405 END IF;
2406
2407 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
2408 p_flex_update_rec.attribute14 := NULL;
2409 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
2410 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
2411 END IF;
2412
2413 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
2414 p_flex_update_rec.attribute15 := NULL;
2415 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
2416 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
2417 END IF;
2418
2419 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
2420 p_flex_update_rec.attribute16 := NULL;
2421 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
2422 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
2423 END IF;
2424
2425 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
2426 p_flex_update_rec.attribute17 := NULL;
2427 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
2428 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
2429 END IF;
2430
2431 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
2432 p_flex_update_rec.attribute18 := NULL;
2433 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
2434 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
2435 END IF;
2436
2437 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
2438 p_flex_update_rec.attribute19 := NULL;
2439 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
2440 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
2441 END IF;
2442
2443 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
2444 p_flex_update_rec.attribute20 := NULL;
2445 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
2446 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
2447 END IF;
2448
2449 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
2450 p_flex_update_rec.attribute21 := NULL;
2451 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
2452 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
2453 END IF;
2454
2455 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
2456 p_flex_update_rec.attribute22 := NULL;
2457 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
2458 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
2459 END IF;
2460
2461 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
2462 p_flex_update_rec.attribute23 := NULL;
2463 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
2464 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
2465 END IF;
2466
2467 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
2468 p_flex_update_rec.attribute24 := NULL;
2469 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
2470 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
2471 END IF;
2472
2473 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
2474 p_flex_update_rec.attribute25 := NULL;
2475 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
2476 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
2477 END IF;
2478
2479 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
2480 p_flex_update_rec.attribute26 := NULL;
2481 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
2482 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
2483 END IF;
2484
2485 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
2486 p_flex_update_rec.attribute27 := NULL;
2487 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
2488 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
2489 END IF;
2490
2491 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
2492 p_flex_update_rec.attribute28 := NULL;
2493 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
2494 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
2495 END IF;
2496
2497 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
2498 p_flex_update_rec.attribute29 := NULL;
2499 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
2500 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
2501 END IF;
2502
2503 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
2504 p_flex_update_rec.attribute30 := NULL;
2505 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
2506 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
2507 END IF;
2508
2509 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
2510 p_flex_update_rec.attribute_category := NULL;
2511 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
2512 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
2513 END IF;
2514 END IF;
2515
2516 END IF;
2517 CLOSE Flex_cur;
2518
2519 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2520 GMD_RECIPE_DETAIL_PVT.RECIPE_ROUTING_STEPS (p_recipe_detail_rec => p_recipe_detail_rec
2521 ,p_flex_insert_rec => p_flex_insert_rec
2522 ,p_flex_update_rec => p_flex_update_rec
2523 ,x_return_status => x_return_status);
2524 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2525 RAISE FND_API.G_EXC_ERROR;
2526 END IF;
2527 END IF;
2528
2529 END LOOP;
2530
2531 IF FND_API.To_Boolean( p_commit ) THEN
2532 Commit;
2533 END IF;
2534
2535 /* Get the message count and information */
2536 FND_MSG_PUB.Count_And_Get (
2537 p_count => x_msg_count,
2538 p_data => x_msg_data );
2539
2540 EXCEPTION
2541 WHEN FND_API.G_EXC_ERROR THEN
2542 ROLLBACK to Recipe_Routing_Steps;
2543 x_return_status := FND_API.G_RET_STS_ERROR;
2544 FND_MSG_PUB.Count_And_Get (
2545 p_count => x_msg_count,
2546 p_data => x_msg_data );
2547
2548 WHEN setup_failure THEN
2549 ROLLBACK to Recipe_Routing_Steps;
2550 x_return_status := FND_API.G_RET_STS_ERROR;
2551 fnd_msg_pub.count_and_get (
2552 p_count => x_msg_count
2553 ,p_encoded => FND_API.g_false
2554 ,p_data => x_msg_data);
2555 WHEN OTHERS THEN
2556 ROLLBACK to Recipe_Routing_Steps;
2557 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
2558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2559 FND_MSG_PUB.Count_And_Get (
2560 p_count => x_msg_count,
2561 p_data => x_msg_data );
2562
2563 END Recipe_Routing_Steps;
2564
2565 /* ============================================= */
2566 /* Procedure: */
2567 /* Recipe_Orgn_Operations */
2568 /* */
2569 /* DESCRIPTION: */
2570 /* This PL/SQL procedure is responsible for */
2571 /* inserting and updating recipe orgn activities */
2572 /* */
2573 /* ============================================= */
2574 /* Start of commments */
2575 /* API name : Recipe_Orgn_operations */
2576 /* Type : Public */
2577 /* Function : */
2578 /* Parameters : */
2579 /* IN : p_api_version IN NUMBER Required */
2580 /* p_init_msg_list IN Varchar2 Optional */
2581 /* p_commit IN Varchar2 Optional */
2582 /* p_recipe_detail_tbl IN Required */
2583 /* */
2584 /* OUT x_return_status OUT NOCOPY varchar2(1) */
2585 /* x_msg_count OUT NOCOPY Number */
2586 /* x_msg_data OUT NOCOPY varchar2(2000) */
2587 /* */
2588 /* Version : Current Version 1.0 */
2589 /* */
2590 /* Notes : p_called_from_forms parameter not currently used */
2591 /* originally included for returning error messages */
2592 /* */
2593 /* End of comments */
2594 PROCEDURE RECIPE_ORGN_OPERATIONS
2595 ( p_api_version IN NUMBER ,
2596 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2597 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2598 p_called_from_forms IN VARCHAR2 := 'NO' ,
2599 x_return_status OUT NOCOPY VARCHAR2 ,
2600 x_msg_count OUT NOCOPY NUMBER ,
2601 x_msg_data OUT NOCOPY VARCHAR2 ,
2602 p_recipe_detail_tbl IN recipe_detail_tbl ,
2603 p_recipe_insert_flex IN recipe_flex ,
2604 p_recipe_update_flex IN recipe_update_flex
2605 ) IS
2606
2607 /* Define all variables specific to this procedure */
2608 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_OPERATIONS';
2609 l_api_version CONSTANT NUMBER := 2.0;
2610 l_rowid VARCHAR2(32);
2611 l_user_id fnd_user.user_id%TYPE := 0;
2612 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
2613
2614 /* Variables used for defining status */
2615 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
2616 l_return_code NUMBER := 0;
2617
2618 /* Error message count and data */
2619 l_msg_count NUMBER;
2620 l_msg_data VARCHAR2(2000);
2621
2622 /* Record types for data manipulation */
2623 p_recipe_detail_rec recipe_dtl;
2624
2625 /* flex field records for inserts and updates */
2626 p_flex_insert_rec flex;
2627 p_flex_update_rec update_flex;
2628
2629 /* used for g_miss_char logic */
2630 l_flex_update_rec update_flex;
2631
2632 /* Define a cursor for dealing with updates */
2633 CURSOR Flex_cur(vRecipe_Id NUMBER, vRoutingstep_Id NUMBER,
2634 vOprn_Line_Id Number, vOrgn_id NUMBER) IS
2635 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
2636 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
2637 attribute11, attribute12, attribute13, attribute14, attribute15,
2638 attribute16, attribute17, attribute18, attribute19, attribute20,
2639 attribute21, attribute22, attribute23, attribute24,attribute25,
2640 attribute26, attribute27, attribute28, attribute29, attribute30
2641 From gmd_recipe_orgn_activities
2642 where recipe_id = NVL(vRecipe_id,-1) AND
2643 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
2644 oprn_line_id = NVL(vOprn_line_id,-1) AND
2645 organization_id = vOrgn_id;
2646
2647 setup_failure EXCEPTION;
2648
2649 BEGIN
2650 /* Updating recipe orgn activity for forst time infact insert a new record in */
2651 /* gmd_recipe_orgn activities table */
2652
2653 /* Define Savepoint */
2654 SAVEPOINT Recipe_Orgn_Activities;
2655
2656 /* Standard Check for API compatibility */
2657 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2658 p_api_version ,
2659 l_api_name ,
2660 G_PKG_NAME )
2661 THEN
2662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2663 END IF;
2664
2665 /* Initialize message list if p_init_msg_list is set to TRUE */
2666 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2667 FND_MSG_PUB.initialize;
2668 END IF;
2669
2670 /* Intialize the setup fields */
2671 IF NOT gmd_api_grp.setup_done THEN
2672 gmd_api_grp.setup_done := gmd_api_grp.setup;
2673 END IF;
2674 IF NOT gmd_api_grp.setup_done THEN
2675 RAISE setup_failure;
2676 END IF;
2677
2678 IF (p_recipe_detail_tbl.Count = 0) THEN
2679 RAISE FND_API.G_EXC_ERROR;
2680 END IF;
2681
2682 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
2683
2684 /* Initialization of all status */
2685 /* If a record fails in validation we store this message in error stack */
2686 /* and loop thro records */
2687 x_return_status := FND_API.G_RET_STS_SUCCESS;
2688
2689 /* Assign each row from the PL/SQL table to a row. */
2690 p_recipe_detail_rec := p_recipe_detail_tbl(i);
2691
2692 /* ========================== */
2693 /* Check if recipe id exists */
2694 /* ========================== */
2695 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
2696 GMD_RECIPE_VAL.recipe_name
2697 ( p_api_version => 1.0,
2698 p_init_msg_list => FND_API.G_FALSE,
2699 p_commit => FND_API.G_FALSE,
2700 p_recipe_no => p_recipe_detail_rec.recipe_no,
2701 p_recipe_version => p_recipe_detail_rec.recipe_version,
2702 x_return_status => l_return_status,
2703 x_msg_count => l_msg_count,
2704 x_msg_data => l_msg_data,
2705 x_return_code => l_return_code,
2706 x_recipe_id => l_recipe_id);
2707
2708 IF (l_recipe_id IS NULL) THEN
2709 x_return_status := FND_API.G_RET_STS_ERROR;
2710 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
2711 FND_MSG_PUB.ADD;
2712 ELSE
2713 p_recipe_detail_rec.recipe_id := l_recipe_id;
2714 END IF;
2715 END IF;
2716
2717 /* Validate if this Recipe can be modified by this user */
2718 /* Recipe Security fix */
2719 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
2720 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
2721 RAISE FND_API.G_EXC_ERROR;
2722 END IF;
2723
2724 /* ================================ */
2725 /* Check if a valid routing and */
2726 /* routing step exists */
2727 /* ================================ */
2728 IF (p_recipe_detail_rec.routingstep_id IS NULL) THEN
2729 x_return_status := FND_API.G_RET_STS_ERROR;
2730 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
2731 FND_MSG_PUB.ADD;
2732 END IF;
2733
2734 /* ==================================== */
2735 /* Check if a valid oprn line id exists */
2736 /* ===================================== */
2737 IF (p_recipe_detail_rec.oprn_line_id IS NULL) THEN
2738 x_return_status := FND_API.G_RET_STS_ERROR;
2739 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
2740 FND_MSG_PUB.ADD;
2741 END IF;
2742
2743 /* Assign flex fields */
2744 IF (p_recipe_insert_flex.count = 0) THEN
2745 p_flex_insert_rec := NULL;
2746 ELSE
2747 p_flex_insert_rec := p_recipe_insert_flex(i);
2748 END IF;
2749
2750 /* Assign flex fields */
2751 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,
2752 p_recipe_detail_rec.routingstep_id,
2753 p_recipe_detail_rec.oprn_line_id,
2754 p_recipe_detail_rec.organization_id);
2755 FETCH Flex_cur INTO l_flex_update_rec;
2756 IF flex_cur%FOUND THEN
2757
2758 /* If no flex field is updated retain the old values */
2759 IF (p_recipe_update_flex.count = 0) THEN
2760 p_flex_update_rec := l_flex_update_rec;
2761 ELSE
2762 p_flex_update_rec := p_recipe_update_flex(i);
2763
2764 /* ================================ */
2765 /* Check for all G_MISS_CHAR values */
2766 /* for nullable fields in */
2767 /* gmd_recipe_routing_steps table */
2768 /* ================================= */
2769
2770 /* Thomas Daniel - Bug 2652200 */
2771 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
2772 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
2773 /* as the user is intending to update the field to NULL */
2774 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
2775 p_flex_update_rec.attribute1 := NULL;
2776 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
2777 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
2778 END IF;
2779
2780 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
2781 p_flex_update_rec.attribute2 := NULL;
2782 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
2783 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
2784 END IF;
2785
2786 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
2787 p_flex_update_rec.attribute3 := NULL;
2788 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
2789 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
2790 END IF;
2791
2792 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
2793 p_flex_update_rec.attribute4 := NULL;
2794 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
2795 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
2796 END IF;
2797
2798 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
2799 p_flex_update_rec.attribute5 := NULL;
2800 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
2801 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
2802 END IF;
2803
2804 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
2805 p_flex_update_rec.attribute6 := NULL;
2806 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
2807 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
2808 END IF;
2809
2810 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
2811 p_flex_update_rec.attribute7 := NULL;
2812 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
2813 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
2814 END IF;
2815
2816 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
2817 p_flex_update_rec.attribute8 := NULL;
2818 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
2819 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
2820 END IF;
2821
2822 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
2823 p_flex_update_rec.attribute9 := NULL;
2824 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
2825 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
2826 END IF;
2827
2828 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
2829 p_flex_update_rec.attribute10 := NULL;
2830 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
2831 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
2832 END IF;
2833
2834 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
2835 p_flex_update_rec.attribute11 := NULL;
2836 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
2837 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
2838 END IF;
2839
2840 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
2841 p_flex_update_rec.attribute12 := NULL;
2842 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
2843 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
2844 END IF;
2845
2846 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
2847 p_flex_update_rec.attribute13 := NULL;
2848 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
2849 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
2850 END IF;
2851
2852 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
2853 p_flex_update_rec.attribute14 := NULL;
2854 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
2855 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
2856 END IF;
2857
2858 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
2859 p_flex_update_rec.attribute15 := NULL;
2860 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
2861 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
2862 END IF;
2863
2864 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
2865 p_flex_update_rec.attribute16 := NULL;
2866 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
2867 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
2868 END IF;
2869
2870 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
2871 p_flex_update_rec.attribute17 := NULL;
2872 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
2873 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
2874 END IF;
2875
2876 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
2877 p_flex_update_rec.attribute18 := NULL;
2878 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
2879 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
2880 END IF;
2881
2882 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
2883 p_flex_update_rec.attribute19 := NULL;
2884 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
2885 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
2886 END IF;
2887
2888 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
2889 p_flex_update_rec.attribute20 := NULL;
2890 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
2891 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
2892 END IF;
2893
2894 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
2895 p_flex_update_rec.attribute21 := NULL;
2896 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
2897 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
2898 END IF;
2899
2900 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
2901 p_flex_update_rec.attribute22 := NULL;
2902 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
2903 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
2904 END IF;
2905
2906 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
2907 p_flex_update_rec.attribute23 := NULL;
2908 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
2909 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
2910 END IF;
2911
2912 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
2913 p_flex_update_rec.attribute24 := NULL;
2914 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
2915 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
2916 END IF;
2917
2918 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
2919 p_flex_update_rec.attribute25 := NULL;
2920 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
2921 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
2922 END IF;
2923
2924 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
2925 p_flex_update_rec.attribute26 := NULL;
2926 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
2927 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
2928 END IF;
2929
2930 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
2931 p_flex_update_rec.attribute27 := NULL;
2932 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
2933 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
2934 END IF;
2935
2936 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
2937 p_flex_update_rec.attribute28 := NULL;
2938 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
2939 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
2940 END IF;
2941
2942 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
2943 p_flex_update_rec.attribute29 := NULL;
2944 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
2945 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
2946 END IF;
2947
2948 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
2949 p_flex_update_rec.attribute30 := NULL;
2950 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
2951 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
2952 END IF;
2953
2954 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
2955 p_flex_update_rec.attribute_category := NULL;
2956 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
2957 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
2958 END IF;
2959 END IF;
2960
2961 END IF; /* end of flex_cur%FOUND */
2962 CLOSE Flex_cur; -- Bug 6972110
2963
2964 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2965 GMD_RECIPE_DETAIL_PVT.RECIPE_ORGN_OPERATIONS (p_recipe_detail_rec => p_recipe_detail_rec
2966 ,p_flex_insert_rec => p_flex_insert_rec
2967 ,p_flex_update_rec => p_flex_update_rec
2968 ,x_return_status => x_return_status);
2969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2970 RAISE FND_API.G_EXC_ERROR;
2971 END IF;
2972 END IF;
2973
2974 END LOOP;
2975
2976 IF FND_API.To_Boolean( p_commit ) THEN
2977 Commit;
2978 END IF;
2979
2980 /* Get the message count and information */
2981 FND_MSG_PUB.Count_And_Get (
2982 p_count => x_msg_count,
2983 p_data => x_msg_data );
2984
2985 EXCEPTION
2986 WHEN FND_API.G_EXC_ERROR THEN
2987 ROLLBACK to Recipe_Orgn_Activities;
2988 x_return_status := FND_API.G_RET_STS_ERROR;
2989 FND_MSG_PUB.Count_And_Get (
2990 p_count => x_msg_count,
2991 p_data => x_msg_data );
2992
2993 WHEN setup_failure THEN
2994 x_return_status := FND_API.G_RET_STS_ERROR;
2995 fnd_msg_pub.count_and_get (
2996 p_count => x_msg_count
2997 ,p_encoded => FND_API.g_false
2998 ,p_data => x_msg_data);
2999 WHEN OTHERS THEN
3000 ROLLBACK to Recipe_Orgn_Activities;
3001 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
3002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3003 FND_MSG_PUB.Count_And_Get (
3004 p_count => x_msg_count,
3005 p_data => x_msg_data );
3006
3007 END RECIPE_ORGN_OPERATIONS;
3008
3009
3010 /* ============================================= */
3011 /* Procedure: */
3012 /* Recipe_Orgn_Resources */
3013 /* */
3014 /* DESCRIPTION: */
3015 /* This PL/SQL procedure is responsible for */
3016 /* inserting and updating recipe orgn resources */
3017 /* */
3018 /* ============================================= */
3019 /* Start of commments */
3020 /* API name : Recipe_Orgn_Resources */
3021 /* Type : Public */
3022 /* Function : */
3023 /* parameters : */
3024 /* IN : p_api_version IN NUMBER Required */
3025 /* p_init_msg_list IN Varchar2 Optional */
3026 /* p_commit IN Varchar2 Optional */
3027 /* p_recipe_detail_tbl IN Required */
3028 /* */
3029 /* OUT x_return_status OUT NOCOPY varchar2(1) */
3030 /* x_msg_count OUT NOCOPY Number */
3031 /* x_msg_data OUT NOCOPY varchar2(2000) */
3032 /* */
3033 /* Version : Current Version 1.0 */
3034 /* */
3035 /* Notes : p_called_from_forms parameter not currently used */
3036 /* originally included for returning error messages */
3037 /* */
3038 /* End of comments */
3039 PROCEDURE RECIPE_ORGN_RESOURCES
3040 ( p_api_version IN NUMBER ,
3041 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3042 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3043 p_called_from_forms IN VARCHAR2 := 'NO' ,
3044 x_return_status OUT NOCOPY VARCHAR2 ,
3045 x_msg_count OUT NOCOPY NUMBER ,
3046 x_msg_data OUT NOCOPY VARCHAR2 ,
3047 p_recipe_detail_tbl IN recipe_detail_tbl ,
3048 p_recipe_insert_flex IN recipe_flex ,
3049 p_recipe_update_flex IN recipe_update_flex
3050 ) IS
3051
3052 /* Define all variables specific to this procedure */
3053 l_dml_type VARCHAR2(1) := 'I';
3054 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_RESOURCES';
3055 l_api_version CONSTANT NUMBER := 2.0;
3056
3057 l_user_id fnd_user.user_id%TYPE := 0;
3058 l_recipe_id GMD_RECIPES.recipe_id%TYPE := 0;
3059
3060 /* Variables used for defining status */
3061 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
3062 l_return_code NUMBER := 0;
3063
3064 /* Error message count and data */
3065 l_msg_count NUMBER;
3066 l_msg_data VARCHAR2(2000);
3067
3068 /* Record types for data manipulation */
3069 p_recipe_detail_rec recipe_dtl;
3070
3071 /* flex field records for inserts and updates */
3072 p_flex_insert_rec flex;
3073 p_flex_update_rec update_flex;
3074
3075 /* used for g_miss_char logic */
3076 l_flex_update_rec update_flex;
3077
3078 /* Define a cursor for dealing with updates */
3079 CURSOR Flex_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
3080 vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
3081 Select attribute_category, attribute1, attribute2, attribute3, attribute4,
3082 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
3083 attribute11, attribute12, attribute13, attribute14, attribute15,
3084 attribute16, attribute17, attribute18, attribute19, attribute20,
3085 attribute21, attribute22, attribute23, attribute24,attribute25,
3086 attribute26, attribute27, attribute28, attribute29, attribute30
3087 From gmd_recipe_orgn_resources
3088 where recipe_id = NVL(vRecipe_id,-1) AND
3089 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
3090 oprn_line_id = NVL(vOprn_line_id,-1) AND
3091 resources = vResources AND
3092 organization_id = vOrgn_id;
3093
3094 CURSOR update_res_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
3095 vOprn_line_id NUMBER, vResources VARCHAR2, vOrgn_id NUMBER) IS
3096 Select min_capacity, max_capacity, process_qty, usage_uom,
3097 resource_usage
3098 From gmd_recipe_orgn_resources
3099 where recipe_id = NVL(vRecipe_id,-1) AND
3100 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
3101 oprn_line_id = NVL(vOprn_line_id,-1) AND
3102 resources = vResources AND
3103 organization_id = vOrgn_id;
3104
3105 setup_failure EXCEPTION;
3106
3107 /* Bug No.6339150 - Start */
3108
3109 /* Cursor to fetch OPERATION UOM */
3110 CURSOR operation_uom_cur(vOprn_line_id NUMBER) IS
3111 SELECT gos.process_qty_uom
3112 FROM gmd_operation_activities goa, gmd_operations gos
3113 WHERE goa.oprn_line_id = vOprn_line_id
3114 AND gos.oprn_id = goa.oprn_id;
3115
3116 l_oprn_uom VARCHAR2(10);
3117
3118 /* Bug No.6339150 - End */
3119
3120
3121 BEGIN
3122 /* Updating recipe orgn resources for forst time infact insert a new record in */
3123 /* gmd_recipe_orgn_resources table */
3124
3125 /* Define Savepoint */
3126 SAVEPOINT Recipe_Orgn_Resources;
3127
3128 /* Standard Check for API compatibility */
3129 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
3130 p_api_version ,
3131 l_api_name ,
3132 G_PKG_NAME )
3133 THEN
3134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3135 END IF;
3136
3137 /* Initialize message list if p_init_msg_list is set to TRUE */
3138 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3139 FND_MSG_PUB.initialize;
3140 END IF;
3141
3142 /* Intialize the setup fields */
3143 IF NOT gmd_api_grp.setup_done THEN
3144 gmd_api_grp.setup_done := gmd_api_grp.setup;
3145 END IF;
3146 IF NOT gmd_api_grp.setup_done THEN
3147 RAISE setup_failure;
3148 END IF;
3149
3150 IF (p_recipe_detail_tbl.Count = 0) THEN
3151 RAISE FND_API.G_EXC_ERROR;
3152 END IF;
3153
3154 FOR i IN 1 .. p_recipe_detail_tbl.count LOOP
3155
3156 /* Initialization of all status */
3157 /* If a record fails in validation we store this message in error stack */
3158 /* and loop thro records */
3159 x_return_status := FND_API.G_RET_STS_SUCCESS;
3160
3161 /* Assign each row from the PL/SQL table to a row. */
3162 p_recipe_detail_rec := p_recipe_detail_tbl(i);
3163
3164 /* ========================== */
3165 /* Check if recipe id exists */
3166 /* ========================== */
3167 IF (p_recipe_detail_rec.recipe_id IS NULL) THEN
3168 GMD_RECIPE_VAL.recipe_name
3169 ( p_api_version => 1.0,
3170 p_init_msg_list => FND_API.G_FALSE,
3171 p_commit => FND_API.G_FALSE,
3172 p_recipe_no => p_recipe_detail_rec.recipe_no,
3173 p_recipe_version => p_recipe_detail_rec.recipe_version,
3174 x_return_status => l_return_status,
3175 x_msg_count => l_msg_count,
3176 x_msg_data => l_msg_data,
3177 x_return_code => l_return_code,
3178 x_recipe_id => l_recipe_id);
3179
3180 IF (l_recipe_id IS NULL) THEN
3181 x_return_status := FND_API.G_RET_STS_ERROR;
3182 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
3183 FND_MSG_PUB.ADD;
3184 ELSE
3185 p_recipe_detail_rec.recipe_id := l_recipe_id;
3186 END IF;
3187 END IF;
3188
3189 /* Validate if this Recipe can be modified by this user */
3190 /* Recipe Security fix */
3191 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
3192 ,Entity_id => p_recipe_detail_rec.recipe_id) THEN
3193 RAISE FND_API.G_EXC_ERROR;
3194 END IF;
3195
3196
3197 /* ================================ */
3198 /* Check if a valid routing and */
3199 /* routing step exists */
3200 /* ================================ */
3201 IF (p_recipe_detail_rec.routingstep_id IS NULL) THEN
3202 x_return_status := FND_API.G_RET_STS_ERROR;
3203 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3204 FND_MSG_PUB.ADD;
3205 END IF;
3206
3207 /* ==================================== */
3208 /* Check if a valid oprn line id exists */
3209 /* ===================================== */
3210 IF (p_recipe_detail_rec.oprn_line_id IS NULL) THEN
3211 x_return_status := FND_API.G_RET_STS_ERROR;
3212 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3213 FND_MSG_PUB.ADD;
3214 END IF;
3215
3216 /* ===================================== */
3217 /* Check if a valid resource exists */
3218 /* ================================== */
3219 IF (p_recipe_detail_rec.resources IS NULL) THEN
3220 x_return_status := FND_API.G_RET_STS_ERROR;
3221 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3222 FND_MSG_PUB.ADD;
3223 END IF;
3224
3225
3226 /* Bug No.6339150 - Start */
3227
3228 /* To copy the OPERATION process_qty_um to Orgn. Resource */
3229 l_oprn_uom := NULL;
3230
3231 OPEN operation_uom_cur(p_recipe_detail_rec.oprn_line_id);
3232 FETCH operation_uom_cur INTO l_oprn_uom;
3233 CLOSE operation_uom_cur;
3234
3235 IF l_oprn_uom IS NOT NULL THEN
3236 p_recipe_detail_rec.PROCESS_UM := l_oprn_uom;
3237 ELSE
3238 x_return_status := FND_API.G_RET_STS_ERROR;
3239 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
3240 FND_MSG_PUB.ADD;
3241 END IF;
3242
3243 /* Bug No.6339150 - End */
3244
3245
3246 FOR update_rec IN update_res_cur (p_recipe_detail_rec.recipe_id,
3247 p_recipe_detail_rec.routingstep_id,
3248 p_recipe_detail_rec.oprn_line_id,
3249 p_recipe_detail_rec.resources,
3250 p_recipe_detail_rec.organization_id) LOOP
3251
3252 IF (p_recipe_detail_rec.min_capacity = FND_API.G_MISS_NUM) THEN
3253 p_recipe_detail_rec.min_capacity := NULL;
3254 ELSIF (p_recipe_detail_rec.min_capacity IS NULL) THEN
3255 p_recipe_detail_rec.min_capacity := update_rec.min_capacity;
3256 END IF;
3257
3258 IF (p_recipe_detail_rec.max_capacity = FND_API.G_MISS_NUM) THEN
3259 p_recipe_detail_rec.max_capacity := NULL;
3260 ELSIF (p_recipe_detail_rec.max_capacity IS NULL) THEN
3261 p_recipe_detail_rec.max_capacity := update_rec.max_capacity;
3262 END IF;
3263
3264 IF (p_recipe_detail_rec.process_qty = FND_API.G_MISS_NUM) THEN
3265 p_recipe_detail_rec.process_qty := NULL;
3266 ELSIF (p_recipe_detail_rec.process_qty IS NULL) THEN
3267 p_recipe_detail_rec.process_qty := update_rec.process_qty;
3268 END IF;
3269
3270 IF (p_recipe_detail_rec.resource_usage = FND_API.G_MISS_NUM) THEN
3271 p_recipe_detail_rec.resource_usage := NULL;
3272 ELSIF (p_recipe_detail_rec.resource_usage IS NULL) THEN
3273 p_recipe_detail_rec.resource_usage := update_rec.resource_usage;
3274 END IF;
3275
3276 IF (p_recipe_detail_rec.usage_uom = FND_API.G_MISS_CHAR) THEN
3277 p_recipe_detail_rec.usage_uom := NULL;
3278 ELSIF (p_recipe_detail_rec.usage_uom IS NULL) THEN
3279 p_recipe_detail_rec.usage_uom := update_rec.usage_uom;
3280 END IF;
3281
3282
3283 END LOOP;
3284
3285
3286 /* Assign flex fields */
3287 IF (p_recipe_insert_flex.count = 0) THEN
3288 p_flex_insert_rec := NULL;
3289 ELSE
3290 p_flex_insert_rec := p_recipe_insert_flex(i);
3291 END IF;
3292
3293 /* Assign flex fields */
3294 OPEN Flex_cur(p_recipe_detail_rec.recipe_id,
3295 p_recipe_detail_rec.routingstep_id,
3296 p_recipe_detail_rec.oprn_line_id,
3297 p_recipe_detail_rec.resources,
3298 p_recipe_detail_rec.organization_id);
3299 FETCH Flex_cur INTO l_flex_update_rec;
3300 IF flex_cur%FOUND THEN
3301
3302 /* If no flex field is updated retain the old values */
3303 IF (p_recipe_update_flex.count = 0) THEN
3304 p_flex_update_rec := l_flex_update_rec;
3305 ELSE
3306 p_flex_update_rec := p_recipe_update_flex(i);
3307
3308 /* ================================ */
3309 /* Check for all G_MISS_CHAR values */
3310 /* for nullable fields in */
3311 /* gmd_recipe_routing_steps table */
3312 /* ================================= */
3313
3314 /* Thomas Daniel - Bug 2652200 */
3315 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
3316 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
3317 /* as the user is intending to update the field to NULL */
3318 IF (p_flex_update_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
3319 p_flex_update_rec.attribute1 := NULL;
3320 ELSIF (p_flex_update_rec.attribute1 IS NULL) THEN
3321 p_flex_update_rec.attribute1 := l_flex_update_rec.attribute1;
3322 END IF;
3323
3324 IF (p_flex_update_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
3325 p_flex_update_rec.attribute2 := NULL;
3326 ELSIF (p_flex_update_rec.attribute2 IS NULL) THEN
3327 p_flex_update_rec.attribute2 := l_flex_update_rec.attribute2;
3328 END IF;
3329
3330 IF (p_flex_update_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
3331 p_flex_update_rec.attribute3 := NULL;
3332 ELSIF (p_flex_update_rec.attribute3 IS NULL) THEN
3333 p_flex_update_rec.attribute3 := l_flex_update_rec.attribute3;
3334 END IF;
3335
3336 IF (p_flex_update_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
3337 p_flex_update_rec.attribute4 := NULL;
3338 ELSIF (p_flex_update_rec.attribute4 IS NULL) THEN
3339 p_flex_update_rec.attribute4 := l_flex_update_rec.attribute4;
3340 END IF;
3341
3342 IF (p_flex_update_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
3343 p_flex_update_rec.attribute5 := NULL;
3344 ELSIF (p_flex_update_rec.attribute5 IS NULL) THEN
3345 p_flex_update_rec.attribute5 := l_flex_update_rec.attribute5;
3346 END IF;
3347
3348 IF (p_flex_update_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
3349 p_flex_update_rec.attribute6 := NULL;
3350 ELSIF (p_flex_update_rec.attribute6 IS NULL) THEN
3351 p_flex_update_rec.attribute6 := l_flex_update_rec.attribute6;
3352 END IF;
3353
3354 IF (p_flex_update_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
3355 p_flex_update_rec.attribute7 := NULL;
3356 ELSIF (p_flex_update_rec.attribute7 IS NULL) THEN
3357 p_flex_update_rec.attribute7 := l_flex_update_rec.attribute7;
3358 END IF;
3359
3360 IF (p_flex_update_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
3361 p_flex_update_rec.attribute8 := NULL;
3362 ELSIF (p_flex_update_rec.attribute8 IS NULL) THEN
3363 p_flex_update_rec.attribute8 := l_flex_update_rec.attribute8;
3364 END IF;
3365
3366 IF (p_flex_update_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
3367 p_flex_update_rec.attribute9 := NULL;
3368 ELSIF (p_flex_update_rec.attribute9 IS NULL) THEN
3369 p_flex_update_rec.attribute9 := l_flex_update_rec.attribute9;
3370 END IF;
3371
3372 IF (p_flex_update_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
3373 p_flex_update_rec.attribute10 := NULL;
3374 ELSIF (p_flex_update_rec.attribute10 IS NULL) THEN
3375 p_flex_update_rec.attribute10 := l_flex_update_rec.attribute10;
3376 END IF;
3377
3378 IF (p_flex_update_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
3379 p_flex_update_rec.attribute11 := NULL;
3380 ELSIF (p_flex_update_rec.attribute11 IS NULL) THEN
3381 p_flex_update_rec.attribute11 := l_flex_update_rec.attribute11;
3382 END IF;
3383
3384 IF (p_flex_update_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
3385 p_flex_update_rec.attribute12 := NULL;
3386 ELSIF (p_flex_update_rec.attribute12 IS NULL) THEN
3387 p_flex_update_rec.attribute12 := l_flex_update_rec.attribute12;
3388 END IF;
3389
3390 IF (p_flex_update_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
3391 p_flex_update_rec.attribute13 := NULL;
3392 ELSIF (p_flex_update_rec.attribute13 IS NULL) THEN
3393 p_flex_update_rec.attribute13 := l_flex_update_rec.attribute13;
3394 END IF;
3395
3396 IF (p_flex_update_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
3397 p_flex_update_rec.attribute14 := NULL;
3398 ELSIF (p_flex_update_rec.attribute14 IS NULL) THEN
3399 p_flex_update_rec.attribute14 := l_flex_update_rec.attribute14;
3400 END IF;
3401
3402 IF (p_flex_update_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
3403 p_flex_update_rec.attribute15 := NULL;
3404 ELSIF (p_flex_update_rec.attribute15 IS NULL) THEN
3405 p_flex_update_rec.attribute15 := l_flex_update_rec.attribute15;
3406 END IF;
3407
3408 IF (p_flex_update_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
3409 p_flex_update_rec.attribute16 := NULL;
3410 ELSIF (p_flex_update_rec.attribute16 IS NULL) THEN
3411 p_flex_update_rec.attribute16 := l_flex_update_rec.attribute16;
3412 END IF;
3413
3414 IF (p_flex_update_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
3415 p_flex_update_rec.attribute17 := NULL;
3416 ELSIF (p_flex_update_rec.attribute17 IS NULL) THEN
3417 p_flex_update_rec.attribute17 := l_flex_update_rec.attribute17;
3418 END IF;
3419
3420 IF (p_flex_update_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
3421 p_flex_update_rec.attribute18 := NULL;
3422 ELSIF (p_flex_update_rec.attribute18 IS NULL) THEN
3423 p_flex_update_rec.attribute18 := l_flex_update_rec.attribute18;
3424 END IF;
3425
3426 IF (p_flex_update_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
3427 p_flex_update_rec.attribute19 := NULL;
3428 ELSIF (p_flex_update_rec.attribute19 IS NULL) THEN
3429 p_flex_update_rec.attribute19 := l_flex_update_rec.attribute19;
3430 END IF;
3431
3432 IF (p_flex_update_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
3433 p_flex_update_rec.attribute20 := NULL;
3434 ELSIF (p_flex_update_rec.attribute20 IS NULL) THEN
3435 p_flex_update_rec.attribute20 := l_flex_update_rec.attribute20;
3436 END IF;
3437
3438 IF (p_flex_update_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
3439 p_flex_update_rec.attribute21 := NULL;
3440 ELSIF (p_flex_update_rec.attribute21 IS NULL) THEN
3441 p_flex_update_rec.attribute21 := l_flex_update_rec.attribute21;
3442 END IF;
3443
3444 IF (p_flex_update_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
3445 p_flex_update_rec.attribute22 := NULL;
3446 ELSIF (p_flex_update_rec.attribute22 IS NULL) THEN
3447 p_flex_update_rec.attribute22 := l_flex_update_rec.attribute22;
3448 END IF;
3449
3450 IF (p_flex_update_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
3451 p_flex_update_rec.attribute23 := NULL;
3452 ELSIF (p_flex_update_rec.attribute23 IS NULL) THEN
3453 p_flex_update_rec.attribute23 := l_flex_update_rec.attribute23;
3454 END IF;
3455
3456 IF (p_flex_update_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
3457 p_flex_update_rec.attribute24 := NULL;
3458 ELSIF (p_flex_update_rec.attribute24 IS NULL) THEN
3459 p_flex_update_rec.attribute24 := l_flex_update_rec.attribute24;
3460 END IF;
3461
3462 IF (p_flex_update_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
3463 p_flex_update_rec.attribute25 := NULL;
3464 ELSIF (p_flex_update_rec.attribute25 IS NULL) THEN
3465 p_flex_update_rec.attribute25 := l_flex_update_rec.attribute25;
3466 END IF;
3467
3468 IF (p_flex_update_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
3469 p_flex_update_rec.attribute26 := NULL;
3470 ELSIF (p_flex_update_rec.attribute26 IS NULL) THEN
3471 p_flex_update_rec.attribute26 := l_flex_update_rec.attribute26;
3472 END IF;
3473
3474 IF (p_flex_update_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
3475 p_flex_update_rec.attribute27 := NULL;
3476 ELSIF (p_flex_update_rec.attribute27 IS NULL) THEN
3477 p_flex_update_rec.attribute27 := l_flex_update_rec.attribute27;
3478 END IF;
3479
3480 IF (p_flex_update_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
3481 p_flex_update_rec.attribute28 := NULL;
3482 ELSIF (p_flex_update_rec.attribute28 IS NULL) THEN
3483 p_flex_update_rec.attribute28 := l_flex_update_rec.attribute28;
3484 END IF;
3485
3486 IF (p_flex_update_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
3487 p_flex_update_rec.attribute29 := NULL;
3488 ELSIF (p_flex_update_rec.attribute29 IS NULL) THEN
3489 p_flex_update_rec.attribute29 := l_flex_update_rec.attribute29;
3490 END IF;
3491
3492 IF (p_flex_update_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
3493 p_flex_update_rec.attribute30 := NULL;
3494 ELSIF (p_flex_update_rec.attribute30 IS NULL) THEN
3495 p_flex_update_rec.attribute30 := l_flex_update_rec.attribute30;
3496 END IF;
3497
3498 IF (p_flex_update_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
3499 p_flex_update_rec.attribute_category := NULL;
3500 ELSIF (p_flex_update_rec.attribute_category IS NULL) THEN
3501 p_flex_update_rec.attribute_category := l_flex_update_rec.attribute_category;
3502 END IF;
3503 END IF;
3504
3505 END IF; /* end of flex_cur%FOUND */
3506 CLOSE Flex_cur; -- Bug 6972110
3507
3508 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3509 GMD_RECIPE_DETAIL_PVT.recipe_orgn_resources (p_recipe_detail_rec => p_recipe_detail_rec
3510 ,p_flex_insert_rec => p_flex_insert_rec
3511 ,p_flex_update_rec => p_flex_update_rec
3512 ,x_return_status => x_return_status);
3513 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3514 RAISE FND_API.G_EXC_ERROR;
3515 END IF;
3516 END IF;
3517
3518 END LOOP;
3519
3520 IF FND_API.To_Boolean( p_commit ) THEN
3521 Commit;
3522 END IF;
3523
3524 /* Get the message count and information */
3525 FND_MSG_PUB.Count_And_Get (
3526 p_count => x_msg_count,
3527 p_data => x_msg_data );
3528
3529 EXCEPTION
3530 WHEN FND_API.G_EXC_ERROR THEN
3531 ROLLBACK to Recipe_Orgn_Resources;
3532 x_return_status := FND_API.G_RET_STS_ERROR;
3533 FND_MSG_PUB.Count_And_Get (
3534 p_count => x_msg_count,
3535 p_data => x_msg_data );
3536
3537 WHEN setup_failure THEN
3538 x_return_status := FND_API.G_RET_STS_ERROR;
3539 fnd_msg_pub.count_and_get (
3540 p_count => x_msg_count
3541 ,p_encoded => FND_API.g_false
3542 ,p_data => x_msg_data);
3543
3544 WHEN OTHERS THEN
3545 ROLLBACK to Recipe_Orgn_Resources;
3546 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
3547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3548 FND_MSG_PUB.Count_And_Get (
3549 p_count => x_msg_count,
3550 p_data => x_msg_data );
3551
3552 END RECIPE_ORGN_RESOURCES;
3553
3554 END GMD_RECIPE_DETAIL; /* Package end */
[Home] [Help]