1 PACKAGE BODY GMD_RECIPE_HEADER AS
2 /* $Header: GMDPRCHB.pls 120.7.12020000.2 2012/07/17 10:05:32 mtou ship $ */
3
4 /* Define any variable specific to this package */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_HEADER' ;
6
7
8 /* ============================================= */
9 /* Procedure: */
10 /* Create_Recipe_Header */
11 /* DESCRIPTION: */
12 /* This PL/SQL procedure is responsible for */
13 /* inserting a recipe */
14 /* */
15 /* ============================================= */
16 /* Start of commments */
17 /* API name : Create_Recipe_Header */
18 /* Type : Public */
19 /* Function : */
20 /* Paramaters : */
21 /* IN : p_api_version IN NUMBER Required */
22 /* p_init_msg_list IN Varchar2 Optional */
23 /* p_commit IN Varchar2 Optional */
24 /* p_recipe_tbl IN Required */
25 /* */
26 /* OUT x_return_status OUT NOCOPY varchar2(1) */
27 /* x_msg_count OUT NOCOPY Number */
28 /* x_msg_data OUT NOCOPY varchar2(2000) */
29 /* */
30 /* Version : Current Version 1.1 */
31 /* kkillams 23-03-2004 Added call to modify_status to set recipe */
32 /* status to default status if default status is*/
33 /* defined organization level w.r.t. bug 3408799*/
34 /* */
35
36 PROCEDURE CREATE_RECIPE_HEADER
37 ( p_api_version IN NUMBER
38 ,p_init_msg_list IN VARCHAR2
39 ,p_commit IN VARCHAR2
40 ,p_called_from_forms IN VARCHAR2
41 ,x_return_status OUT NOCOPY VARCHAR2
42 ,x_msg_count OUT NOCOPY NUMBER
43 ,x_msg_data OUT NOCOPY VARCHAR2
44 ,p_recipe_header_tbl IN recipe_tbl
45 ,p_recipe_header_flex IN recipe_flex
46 ) IS
47 /* Defining all local variables */
48 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_HEADER';
49 l_api_version CONSTANT NUMBER := 1.1;
50
51 l_user_id fnd_user.user_id%TYPE := 0;
52
53 /* Variables used for defining status */
54 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
55 l_formula_id NUMBER(15);
56
57 /* Record types for data manipulation */
58 p_recipe_header_rec recipe_hdr;
59 p_recipe_hdr_flex_rec FLEX;
60
61 --kkillams,bug 3408799
62 l_entity_status GMD_API_GRP.status_rec_type;
63 default_status_err EXCEPTION;
64 create_recipe_err EXCEPTION;
65 setup_failure EXCEPTION;
66 BEGIN
67 /* Define Savepoint */
68 SAVEPOINT Insert_Recipe;
69
70 /* Standard Check for API compatibility */
71 IF NOT FND_API.Compatible_API_Call ( l_api_version
72 ,p_api_version
73 ,l_api_name
74 ,G_PKG_NAME )
75 THEN
76 RAISE FND_API.G_EXC_ERROR;
77 END IF;
78
79 /* Initialize message list if p_init_msg_list is set to TRUE */
80 IF FND_API.to_Boolean( p_init_msg_list ) THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 /* Initialize API return status to success */
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 /* Error out if the table is empty */
88 IF (p_recipe_header_tbl.Count = 0) THEN
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91
92 /* Intialize the setup fields */
93 IF NOT gmd_api_grp.setup_done THEN
94 gmd_api_grp.setup_done := gmd_api_grp.setup;
95 END IF;
96 IF NOT gmd_api_grp.setup_done THEN
97 RAISE setup_failure;
98 END IF;
99
100 FOR i IN 1 .. p_recipe_header_tbl.count LOOP
101
102 /* Assign each row from the PL/SQL table to a row. */
103 p_recipe_header_rec := p_recipe_header_tbl(i);
104
105 IF (p_recipe_header_rec.owner_organization_id IS NOT NULL AND
106 p_recipe_header_rec.creation_organization_id IS NULL) THEN
107 p_recipe_header_rec.creation_organization_id := p_recipe_header_rec.owner_organization_id;
108 END IF;
109
110 IF (p_recipe_header_rec.creation_organization_id IS NOT NULL AND
111 p_recipe_header_rec.owner_organization_id IS NULL) THEN
112 p_recipe_header_rec.owner_organization_id := p_recipe_header_rec.creation_organization_id;
113 END IF;
114
115 --Check that owner organization id is not null if raise an error message
116 IF (p_recipe_header_rec.owner_organization_id IS NULL) THEN
117 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
118 FND_MSG_PUB.Add;
119 RAISE FND_API.G_EXC_ERROR;
120 ELSE
121 --Check the owner organization id passed is process enabled if not raise an error message
122 IF NOT (gmd_api_grp.check_orgn_status(p_recipe_header_rec.owner_organization_id)) THEN
123 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
124 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_recipe_header_rec.owner_organization_id);
125 FND_MSG_PUB.Add;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128 END IF;
129
130 --Check that creation organization id is not null if raise an error message
131 IF (p_recipe_header_rec.creation_organization_id IS NULL) THEN
132 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
133 FND_MSG_PUB.Add;
134 RAISE FND_API.G_EXC_ERROR;
135 ELSE
136 --Check the creation organization id passed is process enabled if not raise an error message
137 IF NOT (gmd_api_grp.check_orgn_status(p_recipe_header_rec.creation_organization_id)) THEN
138 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
139 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_recipe_header_rec.creation_organization_id);
140 FND_MSG_PUB.Add;
141 RAISE FND_API.G_EXC_ERROR;
142 END IF;
143 END IF;
144
145 /* Assigning the owner_id, if it is not passed */
146 /* Bug 4603060 */
147 p_recipe_header_rec.owner_id := gmd_api_grp.user_id;
148
149 /* Assign contiguous Ind as 0, if it not passed */
150 IF (p_recipe_header_rec.contiguous_ind IS NULL) THEN
151 p_recipe_header_rec.contiguous_ind := 0;
152 END IF;
153
154 /* Assign Enhanced PI Ind as 0, if it not passed */
155 IF (p_recipe_header_rec.enhanced_pi_ind IS NULL) THEN
156 p_recipe_header_rec.enhanced_pi_ind := 'N';
157 END IF;
158
159 /* Validation for owner_orgn_code access by owner */
160 /* Recipe Security fix */
161 IF NOT (GMD_API_GRP.OrgnAccessible(powner_orgn_id => p_recipe_header_rec.owner_organization_id)) THEN
162 RAISE create_recipe_err;
163 END IF;
164
165 /* Validate the recipe_type field passed in */
166 IF p_recipe_header_rec.recipe_type IS NOT NULL THEN
167 IF p_recipe_header_rec.recipe_type NOT IN (0,1,2) THEN
168 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_RECIPE_TYPE');
169 FND_MSG_PUB.Add;
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172 ELSE
173 p_recipe_header_rec.recipe_type := GMD_API_GRP.get_recipe_type(p_organization_id => p_recipe_header_rec.owner_organization_id);
174 END IF;
175
176 /* Bug 4716923, 4716666 - Thomas Daniel */
177 /* Added the following code to validate the formula and the items in the formula */
178 -- Check for validity of the formula information passed
179 GMD_RECIPE_HEADER_PVT.validate_formula(p_formula_id => p_recipe_header_rec.formula_id
180 ,p_formula_no => p_recipe_header_rec.formula_no
181 ,p_formula_vers => p_recipe_header_rec.formula_vers
182 ,p_owner_organization_id => p_recipe_header_rec.owner_organization_id
183 ,x_formula_id => l_formula_id
184 ,x_return_status => x_return_status);
185 IF x_return_status <> FND_API.g_ret_sts_success THEN
186 RAISE create_recipe_err;
187 ELSE
188 p_recipe_header_rec.formula_id := l_formula_id;
189 END IF;
190
191 /* Assigning flexfield table values */
192 IF (p_recipe_header_flex.count = 0) THEN
193 p_recipe_hdr_flex_rec := NULL;
194 ELSE
195 p_recipe_hdr_flex_rec := p_recipe_header_flex(i);
196 END IF;
197
198 GMD_RECIPE_HEADER_PVT.create_recipe_header (p_recipe_header_rec => p_recipe_header_rec
199 ,p_recipe_hdr_flex_rec => p_recipe_hdr_flex_rec
200 ,x_return_status => x_return_status);
201 IF x_return_status <> FND_API.g_ret_sts_success THEN
202 RAISE create_recipe_err;
203 END IF;
204 END LOOP;
205
206 IF FND_API.To_Boolean( p_commit ) THEN
207 Commit;
208 SAVEPOINT default_status_sp;
209 FOR i IN 1 .. p_recipe_header_tbl.count LOOP
210 --kkillams,bug 3408799
211 /* Assign each row from the PL/SQL table to a row. */
212 p_recipe_header_rec := p_recipe_header_tbl(i);
213 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
214 gmd_api_grp.get_status_details (V_entity_type => 'RECIPE',
215 V_orgn_id => p_recipe_header_rec.owner_organization_id, --w.r.t. bug 4004501 INVCONV kkillams
216 X_entity_status => l_entity_status);
217 --Add this code after the call to gmd_recipes_mls.insert_row.
218 IF (l_entity_status.entity_status <> 100) THEN
219 Gmd_status_pub.modify_status ( p_api_version => 1
220 , p_init_msg_list => TRUE
221 , p_entity_name => 'RECIPE'
222 , p_entity_id => NULL
223 , p_entity_no => p_recipe_header_rec.recipe_no
224 , p_entity_version => p_recipe_header_rec.recipe_version
225 , p_to_status => l_entity_status.entity_status
226 , p_ignore_flag => FALSE
227 , x_message_count => x_msg_count
228 , x_message_list => x_msg_data
229 , x_return_status => X_return_status);
230 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
231 RAISE default_status_err;
232 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
233 END IF;--l_entity_status.entity_status
234 END LOOP;
235 Commit;
236 END IF; -- FND_API.To_Boolean( p_commit )
237 EXCEPTION
238 WHEN create_recipe_err THEN
239 ROLLBACK TO Insert_Recipe;
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 FND_MSG_PUB.Count_And_Get (
242 p_count => x_msg_count,
243 p_data => x_msg_data );
244 WHEN default_status_err THEN
245 ROLLBACK TO default_status_sp;
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 FND_MSG_PUB.Count_And_Get (
248 p_count => x_msg_count,
249 p_data => x_msg_data );
250 WHEN FND_API.G_EXC_ERROR OR setup_failure THEN
251 ROLLBACK to Insert_Recipe;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 FND_MSG_PUB.Count_And_Get (
254 p_count => x_msg_count,
255 p_data => x_msg_data );
256 WHEN OTHERS THEN
257 ROLLBACK to Insert_Recipe;
258 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
260 FND_MSG_PUB.Count_And_Get (
261 p_count => x_msg_count,
262 p_data => x_msg_data );
263 END CREATE_RECIPE_HEADER;
264
265
266 /* ============================================= */
267 /* Procedure: */
268 /* Update_Recipe_Header */
269 /* */
270 /* DESCRIPTION: */
271 /* This PL/SQL procedure is responsible for */
272 /* updating a recipe */
273 /* */
274 /* ============================================= */
275 /* Start of commments */
276 /* API name : Update_Recipe_Header */
277 /* Type : Public */
278 /* Function : */
279 /* Paramaters : */
280 /* IN : p_api_version IN NUMBER Required */
281 /* p_init_msg_list IN Varchar2 Optional */
282 /* p_commit IN Varchar2 Optional */
283 /* p_recipe_tbl IN Required */
284 /* */
285 /* OUT x_return_status OUT NOCOPY varchar2(1) */
286 /* x_msg_count OUT NOCOPY Number */
287 /* x_msg_data OUT NOCOPY varchar2(2000) */
288 /* */
289 /* Version : Current Version 2.0 */
290 /* */
291
292 PROCEDURE UPDATE_RECIPE_HEADER
293 ( p_api_version IN NUMBER ,
294 p_init_msg_list IN VARCHAR2 ,
295 p_commit IN VARCHAR2 ,
296 p_called_from_forms IN VARCHAR2 ,
297 x_return_status OUT NOCOPY VARCHAR2 ,
298 x_msg_count OUT NOCOPY NUMBER ,
299 x_msg_data OUT NOCOPY VARCHAR2 ,
300 p_recipe_header_tbl IN recipe_tbl ,
301 p_recipe_update_flex IN recipe_update_flex
302 ) IS
303
304 /* Defining all local variables */
305 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_HEADER';
306 l_api_version CONSTANT NUMBER := 2.0;
307
308 l_user_id fnd_user.user_id%TYPE := 0;
309 l_recipe_id NUMBER := 0;
310 l_formula_id NUMBER(15);
311
312 /* Variables used for defining status */
313 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
314 l_return_code NUMBER := 0;
315
316 /* Record types for data manipulation */
317 p_recipe_header_rec recipe_hdr;
318
319 p_flex_header_rec update_flex;
320 l_recipe_header_rec GMD_RECIPES%ROWTYPE;
321
322 /* Define a cursor for dealing with updates */
323 CURSOR Recipe_cur(pRecipe_id GMD_RECIPES.recipe_id%TYPE) IS
324 Select *
325 From GMD_RECIPES
326 Where Recipe_id = pRecipe_id;
327
328 update_recipe_err EXCEPTION;
329 setup_failure EXCEPTION;
330 BEGIN
331 /* Define Savepoint */
332 SAVEPOINT Update_Recipe;
333
334 /* Standard Check for API compatibility */
335 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
336 p_api_version ,
337 l_api_name ,
338 G_PKG_NAME )
339 THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342
343 /* Initialize message list if p_init_msg_list is set to TRUE */
344 IF FND_API.to_Boolean( p_init_msg_list ) THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 /* Intialize the setup fields */
349 IF NOT gmd_api_grp.setup_done THEN
350 gmd_api_grp.setup_done := gmd_api_grp.setup;
351 END IF;
352 IF NOT gmd_api_grp.setup_done THEN
353 RAISE setup_failure;
354 END IF;
355
356 /* Initialize API return status to success */
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 /* Start the loop - Error out if the table is empty */
360 IF (p_recipe_header_tbl.Count = 0) THEN
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 FOR i IN 1 .. p_recipe_header_tbl.count LOOP
365
366 /* Assign each row from the PL/SQL table to a row. */
367 p_recipe_header_rec := p_recipe_header_tbl(i);
368
369 /* ============================================ */
370 /* Recipe_id or Recipe_no/Version Combo has to be */
371 /* provided for updates and it must exists */
372 /* ============================================= */
373 GMD_RECIPE_VAL.RECIPE_EXISTS
374 ( P_API_VERSION => 1.0 ,
375 P_RECIPE_ID => p_recipe_header_rec.Recipe_id ,
376 P_RECIPE_NO => p_recipe_header_rec.Recipe_no ,
377 P_RECIPE_VERSION => p_recipe_header_rec.Recipe_version ,
378 X_RETURN_STATUS => X_return_status ,
379 X_MSG_COUNT => x_msg_count ,
380 X_MSG_DATA => x_msg_data ,
381 X_RETURN_CODE => l_return_code ,
382 X_RECIPE_ID => l_recipe_id
383 );
384 IF (X_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
385 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DOES_NOT_EXIST');
386 FND_MSG_PUB.ADD;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389
390 /* To be derived from gmd_recipes */
391 OPEN Recipe_cur(l_recipe_id);
392 FETCH Recipe_cur INTO l_recipe_header_rec;
393 CLOSE Recipe_cur;
394
395 /* Set this record for G_MISS_CHAR */
396 IF (p_recipe_update_flex.count <> 0) THEN
397 p_flex_header_rec := p_recipe_update_flex(i);
398 END IF;
399
400 /* Validate if this Recipe can be modified by this user */
401 /* Recipe Security fix */
402 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'RECIPE'
403 ,Entity_id => l_recipe_id) THEN
404 RAISE update_recipe_err;
405 END IF;
406
407 /* ==================================== */
408 /* Get all not null values from the */
409 /* from the recipe table. If any */
410 /* is not provided, update it with what */
411 /* exists in the db */
412 /* ==================================== */
413 IF (p_recipe_header_rec.recipe_no IS NULL) THEN
414 p_recipe_header_rec.recipe_no := l_recipe_header_rec.recipe_no;
415 END IF;
416
417 IF (p_recipe_header_rec.recipe_version IS NULL) THEN
418 p_recipe_header_rec.recipe_version := l_recipe_header_rec.recipe_version;
419 END IF;
420
421 IF (p_recipe_header_rec.recipe_description IS NULL) THEN
422 p_recipe_header_rec.recipe_description
423 := l_recipe_header_rec.recipe_description;
424 END IF;
425
426 IF (p_recipe_header_rec.recipe_status IS NULL) THEN
427 p_recipe_header_rec.recipe_status := l_recipe_header_rec.recipe_status;
428 END IF;
429
430 IF (p_recipe_header_rec.owner_organization_id IS NULL) THEN
431 p_recipe_header_rec.owner_organization_id
432 := l_recipe_header_rec.owner_organization_id;
433 ELSE
434 -- Validate if the new orgn code to be updated with has
435 -- user access
436 IF NOT GMD_API_GRP.OrgnAccessible
437 (powner_orgn_id => p_recipe_header_rec.owner_organization_id) THEN
438 RAISE update_recipe_err;
439 END IF;
440 END IF;
441
442 /* Bug 4716923, 4716666 - Thomas Daniel */
443 /* Added the following code to validate the formula and the items in the formula */
444 IF (p_recipe_header_rec.formula_id IS NOT NULL) OR
445 (p_recipe_header_rec.formula_no IS NOT NULL) THEN
446 -- Check for validity of the formula information passed
447 GMD_RECIPE_HEADER_PVT.validate_formula(p_formula_id => p_recipe_header_rec.formula_id
448 ,p_formula_no => p_recipe_header_rec.formula_no
449 ,p_formula_vers => p_recipe_header_rec.formula_vers
450 ,p_owner_organization_id => p_recipe_header_rec.owner_organization_id
451 ,x_formula_id => l_formula_id
452 ,x_return_status => x_return_status);
453 IF x_return_status <> FND_API.g_ret_sts_success THEN
454 RAISE update_recipe_err;
455 ELSE
456 p_recipe_header_rec.formula_id := l_formula_id;
457 END IF;
458 ELSE
459 p_recipe_header_rec.formula_id
460 := l_recipe_header_rec.formula_id;
461 /* If organization ID is being updated then we need to verify if the new organization */
462 /* has access to the fomula elements */
463 IF p_recipe_header_rec.owner_organization_id <> l_recipe_header_rec.owner_organization_id THEN
464 GMD_API_GRP.check_item_exists (p_formula_id => p_recipe_header_rec.formula_id
465 ,p_organization_id => p_recipe_header_rec.owner_organization_id
466 ,x_return_status => x_return_status);
467 IF x_return_status <> FND_API.g_ret_sts_success THEN
468 RAISE update_recipe_err;
469 END IF;
470 END IF;
471 END IF; /* IF (p_recipe_header_rec.formula_id IS NOT NULL) OR */
472
473 IF (p_recipe_header_rec.delete_mark IS NULL) THEN
474 p_recipe_header_rec.delete_mark
475 := l_recipe_header_rec.delete_mark;
476 END IF;
477 --Bug 14023266 is fixed by changing the orgn code to organization id.
478 IF (p_recipe_header_rec.creation_organization_id IS NULL) THEN
479 p_recipe_header_rec.creation_organization_id
480 := l_recipe_header_rec.creation_organization_id;
481 END IF;
482
483 IF (p_recipe_header_rec.creation_date IS NULL) THEN
484 p_recipe_header_rec.creation_date
485 := l_recipe_header_rec.creation_date;
486 END IF;
487
488 /* Bug 4603060 */
489 p_recipe_header_rec.created_by
490 := gmd_api_grp.user_id;
491
492 IF (p_recipe_header_rec.last_update_date IS NULL) THEN
493 p_recipe_header_rec.last_update_date
494 := sysdate;
495 END IF;
496
497 /* Bug 4603060 */
498 p_recipe_header_rec.last_updated_by
499 := gmd_api_grp.user_id;
500
501 IF (p_recipe_header_rec.last_update_login IS NULL) THEN
502 p_recipe_header_rec.last_update_login
503 := NVL(l_recipe_header_rec.last_update_login,
504 gmd_api_grp.login_id);
505 END IF;
506
507 /* Bug 4603060 */
508 p_recipe_header_rec.owner_id
509 := gmd_api_grp.user_id;
510
511 /* Thomas Daniel - Bug 2652200 */
512 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
513 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
514 /* as the user is intending to update the field to NULL */
515
516 IF (p_recipe_header_rec.routing_id = FND_API.G_MISS_NUM) THEN
517 p_recipe_header_rec.routing_id := NULL;
518 ELSIF (p_recipe_header_rec.routing_id IS NULL) THEN
519 p_recipe_header_rec.routing_id := l_recipe_header_rec.routing_id;
520 END IF;
521
522 IF (p_recipe_header_rec.planned_process_loss = FND_API.G_MISS_NUM) THEN
523 p_recipe_header_rec.planned_process_loss := NULL;
524 ELSIF (p_recipe_header_rec.planned_process_loss IS NULL) THEN
525 p_recipe_header_rec.planned_process_loss := l_recipe_header_rec.PLANNED_PROCESS_LOSS;
526 END IF;
527 /* B6811759 */
528 IF (p_recipe_header_rec.fixed_process_loss = FND_API.G_MISS_NUM) THEN
529 p_recipe_header_rec.fixed_process_loss := NULL;
530 ELSIF (p_recipe_header_rec.fixed_process_loss IS NULL) THEN
531 p_recipe_header_rec.fixed_process_loss := l_recipe_header_rec.FIXED_PROCESS_LOSS;
532 END IF;
533 IF (p_recipe_header_rec.fixed_process_loss_uom = FND_API.G_MISS_CHAR) THEN
534 p_recipe_header_rec.fixed_process_loss_uom := NULL;
535 ELSIF (p_recipe_header_rec.fixed_process_loss_uom IS NULL) THEN
536 p_recipe_header_rec.fixed_process_loss_uom := l_recipe_header_rec.FIXED_PROCESS_LOSS_UOM;
537 END IF;
538
539 IF (p_recipe_header_rec.contiguous_ind IS NULL) THEN
540 p_recipe_header_rec.contiguous_ind := l_recipe_header_rec.CONTIGUOUS_IND;
541 END IF;
542
543 IF (p_recipe_header_rec.enhanced_pi_ind IS NULL) THEN
544 p_recipe_header_rec.enhanced_pi_ind := l_recipe_header_rec.ENHANCED_PI_IND;
545 END IF;
546
547 /* Validate the recipe_type field passed in */
548 IF p_recipe_header_rec.recipe_type IS NOT NULL THEN
549 IF p_recipe_header_rec.recipe_type NOT IN (0,1,2) THEN
550 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_RECIPE_TYPE');
551 FND_MSG_PUB.Add;
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554 ELSE
555 p_recipe_header_rec.recipe_type := GMD_API_GRP.get_recipe_type(p_organization_id => p_recipe_header_rec.owner_organization_id);
556 END IF;
557
558 IF (p_recipe_header_rec.calculate_step_quantity = FND_API.G_MISS_NUM) THEN
559 p_recipe_header_rec.calculate_step_quantity := NULL;
560 ELSIF (p_recipe_header_rec.calculate_step_quantity IS NULL) THEN
561 p_recipe_header_rec.calculate_step_quantity := l_recipe_header_rec.calculate_step_quantity;
562 END IF;
563
564 IF (p_flex_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
565 p_flex_header_rec.attribute1 := NULL;
566 ELSIF (p_flex_header_rec.attribute1 IS NULL) THEN
567 p_flex_header_rec.attribute1 := l_recipe_header_rec.attribute1;
568 END IF;
569
570 IF (p_flex_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
571 p_flex_header_rec.attribute2 := NULL;
572 ELSIF (p_flex_header_rec.attribute2 IS NULL) THEN
573 p_flex_header_rec.attribute2 := l_recipe_header_rec.attribute2;
574 END IF;
575
576 IF (p_flex_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
577 p_flex_header_rec.attribute3 := NULL;
578 ELSIF (p_flex_header_rec.attribute3 IS NULL) THEN
579 p_flex_header_rec.attribute3 := l_recipe_header_rec.attribute3;
580 END IF;
581
582 IF (p_flex_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
583 p_flex_header_rec.attribute4 := NULL;
584 ELSIF (p_flex_header_rec.attribute4 IS NULL) THEN
585 p_flex_header_rec.attribute4 := l_recipe_header_rec.attribute4;
586 END IF;
587
588 IF (p_flex_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
589 p_flex_header_rec.attribute5 := NULL;
590 ELSIF (p_flex_header_rec.attribute5 IS NULL) THEN
591 p_flex_header_rec.attribute5 := l_recipe_header_rec.attribute5;
592 END IF;
593
594 IF (p_flex_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
595 p_flex_header_rec.attribute6 := NULL;
596 ELSIF (p_flex_header_rec.attribute6 IS NULL) THEN
597 p_flex_header_rec.attribute6 := l_recipe_header_rec.attribute6;
598 END IF;
599
600 IF (p_flex_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
601 p_flex_header_rec.attribute7 := NULL;
602 ELSIF (p_flex_header_rec.attribute7 IS NULL) THEN
603 p_flex_header_rec.attribute7 := l_recipe_header_rec.attribute7;
604 END IF;
605
606 IF (p_flex_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
607 p_flex_header_rec.attribute8 := NULL;
608 ELSIF (p_flex_header_rec.attribute8 IS NULL) THEN
609 p_flex_header_rec.attribute8 := l_recipe_header_rec.attribute8;
610 END IF;
611
612 IF (p_flex_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
613 p_flex_header_rec.attribute9 := NULL;
614 ELSIF (p_flex_header_rec.attribute9 IS NULL) THEN
615 p_flex_header_rec.attribute9 := l_recipe_header_rec.attribute9;
616 END IF;
617
618 IF (p_flex_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
619 p_flex_header_rec.attribute10 := NULL;
620 ELSIF (p_flex_header_rec.attribute10 IS NULL) THEN
621 p_flex_header_rec.attribute10 := l_recipe_header_rec.attribute10;
622 END IF;
623
624 IF (p_flex_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
625 p_flex_header_rec.attribute11 := NULL;
626 ELSIF (p_flex_header_rec.attribute11 IS NULL) THEN
627 p_flex_header_rec.attribute11 := l_recipe_header_rec.attribute11;
628 END IF;
629
630 IF (p_flex_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
631 p_flex_header_rec.attribute12 := NULL;
632 ELSIF (p_flex_header_rec.attribute12 IS NULL) THEN
633 p_flex_header_rec.attribute12 := l_recipe_header_rec.attribute12;
634 END IF;
635
636 IF (p_flex_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
637 p_flex_header_rec.attribute13 := NULL;
638 ELSIF (p_flex_header_rec.attribute13 IS NULL) THEN
639 p_flex_header_rec.attribute13 := l_recipe_header_rec.attribute13;
640 END IF;
641
642 IF (p_flex_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
643 p_flex_header_rec.attribute14 := NULL;
644 ELSIF (p_flex_header_rec.attribute14 IS NULL) THEN
645 p_flex_header_rec.attribute14 := l_recipe_header_rec.attribute14;
646 END IF;
647
648 IF (p_flex_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
649 p_flex_header_rec.attribute15 := NULL;
650 ELSIF (p_flex_header_rec.attribute15 IS NULL) THEN
651 p_flex_header_rec.attribute15 := l_recipe_header_rec.attribute15;
652 END IF;
653
654 IF (p_flex_header_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
655 p_flex_header_rec.attribute16 := NULL;
656 ELSIF (p_flex_header_rec.attribute16 IS NULL) THEN
657 p_flex_header_rec.attribute16 := l_recipe_header_rec.attribute16;
658 END IF;
659
660 IF (p_flex_header_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
661 p_flex_header_rec.attribute17 := NULL;
662 ELSIF (p_flex_header_rec.attribute17 IS NULL) THEN
663 p_flex_header_rec.attribute17 := l_recipe_header_rec.attribute17;
664 END IF;
665
666 IF (p_flex_header_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
667 p_flex_header_rec.attribute18 := NULL;
668 ELSIF (p_flex_header_rec.attribute18 IS NULL) THEN
669 p_flex_header_rec.attribute18 := l_recipe_header_rec.attribute18;
670 END IF;
671
672 IF (p_flex_header_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
673 p_flex_header_rec.attribute19 := NULL;
674 ELSIF (p_flex_header_rec.attribute19 IS NULL) THEN
675 p_flex_header_rec.attribute19 := l_recipe_header_rec.attribute19;
676 END IF;
677
678 IF (p_flex_header_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
679 p_flex_header_rec.attribute20 := NULL;
680 ELSIF (p_flex_header_rec.attribute20 IS NULL) THEN
681 p_flex_header_rec.attribute20 := l_recipe_header_rec.attribute20;
682 END IF;
683
684 IF (p_flex_header_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
685 p_flex_header_rec.attribute21 := NULL;
686 ELSIF (p_flex_header_rec.attribute21 IS NULL) THEN
687 p_flex_header_rec.attribute21 := l_recipe_header_rec.attribute21;
688 END IF;
689
690 IF (p_flex_header_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
691 p_flex_header_rec.attribute22 := NULL;
692 ELSIF (p_flex_header_rec.attribute22 IS NULL) THEN
693 p_flex_header_rec.attribute22 := l_recipe_header_rec.attribute22;
694 END IF;
695
696 IF (p_flex_header_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
697 p_flex_header_rec.attribute23 := NULL;
698 ELSIF (p_flex_header_rec.attribute23 IS NULL) THEN
699 p_flex_header_rec.attribute23 := l_recipe_header_rec.attribute23;
700 END IF;
701
702 IF (p_flex_header_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
703 p_flex_header_rec.attribute24 := NULL;
704 ELSIF (p_flex_header_rec.attribute24 IS NULL) THEN
705 p_flex_header_rec.attribute24 := l_recipe_header_rec.attribute24;
706 END IF;
707
708 IF (p_flex_header_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
709 p_flex_header_rec.attribute25 := NULL;
710 ELSIF (p_flex_header_rec.attribute25 IS NULL) THEN
711 p_flex_header_rec.attribute25 := l_recipe_header_rec.attribute25;
712 END IF;
713
714 IF (p_flex_header_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
715 p_flex_header_rec.attribute26 := NULL;
716 ELSIF (p_flex_header_rec.attribute26 IS NULL) THEN
717 p_flex_header_rec.attribute26 := l_recipe_header_rec.attribute26;
718 END IF;
719
720 IF (p_flex_header_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
721 p_flex_header_rec.attribute27 := NULL;
722 ELSIF (p_flex_header_rec.attribute27 IS NULL) THEN
723 p_flex_header_rec.attribute27 := l_recipe_header_rec.attribute27;
724 END IF;
725
726 IF (p_flex_header_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
727 p_flex_header_rec.attribute28 := NULL;
728 ELSIF (p_flex_header_rec.attribute28 IS NULL) THEN
729 p_flex_header_rec.attribute28 := l_recipe_header_rec.attribute28;
730 END IF;
731
732 IF (p_flex_header_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
733 p_flex_header_rec.attribute29 := NULL;
734 ELSIF (p_flex_header_rec.attribute29 IS NULL) THEN
735 p_flex_header_rec.attribute29 := l_recipe_header_rec.attribute29;
736 END IF;
737
738 IF (p_flex_header_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
739 p_flex_header_rec.attribute30 := NULL;
740 ELSIF (p_flex_header_rec.attribute30 IS NULL) THEN
741 p_flex_header_rec.attribute30 := l_recipe_header_rec.attribute30;
742 END IF;
743
744 IF (p_flex_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
745 p_flex_header_rec.attribute_category := NULL;
746 ELSIF (p_flex_header_rec.attribute_category IS NULL) THEN
747 p_flex_header_rec.attribute_category := l_recipe_header_rec.attribute_category;
748 END IF;
749
750
751 p_recipe_header_rec.recipe_id:=l_recipe_id;
752 GMD_RECIPE_HEADER_PVT.update_recipe_header (p_recipe_header_rec => p_recipe_header_rec
753 ,p_flex_header_rec => p_flex_header_rec
754 ,x_return_status => x_return_status);
755 IF x_return_status <> FND_API.g_ret_sts_success THEN
756 RAISE update_recipe_err;
757 END IF;
758
759 END LOOP;
760
761 IF FND_API.To_Boolean( p_commit ) THEN
762 Commit;
763 END IF;
764
765 EXCEPTION
766 WHEN update_recipe_err THEN
767 ROLLBACK TO Update_Recipe;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 FND_MSG_PUB.Count_And_Get (
770 p_count => x_msg_count,
771 p_data => x_msg_data );
772 WHEN FND_API.G_EXC_ERROR OR setup_failure THEN
773 ROLLBACK to Update_Recipe;
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 FND_MSG_PUB.Count_And_Get (
776 p_count => x_msg_count,
777 p_data => x_msg_data );
778 WHEN OTHERS THEN
779 ROLLBACK to Update_Recipe;
780 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 FND_MSG_PUB.Count_And_Get (
783 p_count => x_msg_count,
784 p_data => x_msg_data );
785 END UPDATE_RECIPE_HEADER;
786
787
788 PROCEDURE DELETE_RECIPE_HEADER
789 (p_api_version IN NUMBER
790 ,p_init_msg_list IN VARCHAR2
791 ,p_commit IN VARCHAR2
792 ,p_called_from_forms IN VARCHAR2
793 ,x_return_status OUT NOCOPY VARCHAR2
794 ,x_msg_count OUT NOCOPY NUMBER
795 ,x_msg_data OUT NOCOPY VARCHAR2
796 ,p_recipe_header_tbl IN recipe_tbl
797 ,p_recipe_update_flex IN recipe_update_flex
798 ) IS
799
800 BEGIN
801
802 /* Call the update API */
803 /* Delete in OPM world is not a physical delete. Its a logical delete */
804 /* i.e its an update with the delete_mark set to 1 */
805 /* Therefore prior to calling this procedure the delete_mark need to be set to 1 */
806 GMD_RECIPE_HEADER.UPDATE_RECIPE_HEADER
807 (p_api_version => p_api_version
808 ,p_init_msg_list => p_init_msg_list
809 ,p_commit => p_commit
810 ,p_called_from_forms => p_called_from_forms
811 ,x_return_status => x_return_status
812 ,x_msg_count => x_msg_count
813 ,x_msg_data => x_msg_data
814 ,p_recipe_header_tbl => p_recipe_header_tbl
815 ,p_recipe_update_flex => p_recipe_update_flex
816 );
817
818 END DELETE_RECIPE_HEADER;
819
820
821 END GMD_RECIPE_HEADER;
[Home] [Help]