1 PACKAGE BODY GMD_RECIPE_HEADER_PVT AS
2 /* $Header: GMDVRCHB.pls 120.10 2009/03/23 17:48:43 rnalla ship $ */
3
4
5 /* Define any variable specific to this package */
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_HEADER_PVT' ;
7
8 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
9 --Forward declaration.
10 FUNCTION set_debug_flag RETURN VARCHAR2;
11 l_debug VARCHAR2(1) := set_debug_flag;
12
13 FUNCTION set_debug_flag RETURN VARCHAR2 IS
14 l_debug VARCHAR2(1):= 'N';
15 BEGIN
16 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
17 l_debug := 'Y';
18 END IF;
19 RETURN l_debug;
20 END set_debug_flag;
21 --Bug 3222090, NSRIVAST 20-FEB-2004, END
22
23
24 /* ===================================================*/
25 /* Procedure: */
26 /* Create_Recipe_Header */
27 /* DESCRIPTION: */
28 /* This PL/SQL procedure is responsible for */
29 /* inserting a recipe */
30 /* HISTORY: */
31 /* 09/16/2003 Jeff Baird Bug #3136456 */
32 /* Changed owner from created_by. */
33 /* */
34 /* ===================================================*/
35 /* Start of commments */
36 /* API name : Create_Recipe_Header */
37 /* Type : Private */
38 /* Function : */
39 /* Paramaters : */
40 /* p_recipe_tbl IN Required */
41 /* */
42 /* OUT x_return_status */
43 /* */
44 /* */
45 /* Notes : */
46 /* End of comments */
47 /* ===================================================*/
48
49 PROCEDURE CREATE_RECIPE_HEADER
50 ( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
51 p_recipe_hdr_flex_rec IN GMD_RECIPE_HEADER.flex ,
52 x_return_status OUT NOCOPY VARCHAR2
53 ) IS
54
55 /* Defining all local variables */
56 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_HEADER';
57 l_user_id fnd_user.user_id%TYPE := 0;
58 l_surrogate gmd_recipes.recipe_id%TYPE := 0;
59
60
61 /* Recipe variables */
62 l_recipe_id gmd_recipes.recipe_id%TYPE := 0;
63
64 /* Formula variables */
65 l_formula_id fm_form_mst.formula_id%TYPE := 0;
66 l_formula_no fm_form_mst.formula_no%TYPE;
67 l_formula_vers fm_form_mst.formula_vers%TYPE;
68
69 /* Routing variables */
70 l_routing_id fm_rout_hdr.routing_id%TYPE := 0;
71 l_routing_no fm_rout_hdr.routing_no%TYPE;
72 l_routing_vers fm_rout_hdr.routing_vers%TYPE;
73
74 /* Organization related */
75 l_plant_ind NUMBER;
76 l_process_loss NUMBER;
77 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
78 l_fixed_process_loss NUMBER; /* B6811759 */
79 l_fixed_process_loss_uom VARCHAR2(3); /*B6811759 */
80 /* Variables used for defining status */
81 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
82 l_return_code NUMBER := 0;
83 l_rowid VARCHAR2(32);
84
85 /* Error message count and data */
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(2000);
88
89 l_val_rt_status BOOLEAN;
90 BEGIN
91 /* Initialize API return status to success */
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 -- Bug 2099699. Sukarna Reddy dt 02/19/02.
94 -- Check if proper routing is associated with recipe
95 IF (p_recipe_header_rec.routing_id IS NOT NULL) THEN
96 l_val_rt_status := gmd_recipe_val.check_routing_validity
97 (p_recipe_header_rec.routing_id,
98 NVL(p_recipe_header_rec.recipe_Status,
99 '100'));
100 IF (NOT l_val_rt_status) THEN
101 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
102 FND_MSG_PUB.ADD;
103 RAISE FND_API.G_EXC_ERROR;
104 END IF;
105 END IF;
106 -- End bug 2099699
107
108 /* ====================================== */
109 /* Either a recipe no or version must be */
110 /* passed tp create a recipe */
111 /* ====================================== */
112 If ((p_recipe_header_rec.recipe_no IS NULL )
113 OR (p_recipe_header_rec.recipe_version IS NULL)) THEN
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_NOT_VALID');
116 FND_MSG_PUB.Add;
117 END IF;
118
119 /* ================================ */
120 /* Based on Recipe_no and */
121 /* Recipe_version check if a recipe */
122 /* already exists in the database */
123 /* ================================= */
124 GMD_RECIPE_VAL.recipe_name
125 ( p_api_version => 1.0,
126 p_init_msg_list => FND_API.G_FALSE,
127 p_commit => FND_API.G_FALSE,
128 p_recipe_no => p_recipe_header_rec.recipe_no,
129 p_recipe_version => p_recipe_header_rec.recipe_version,
130 x_return_status => l_return_status,
131 x_msg_count => l_msg_count,
132 x_msg_data => l_msg_data,
133 x_return_code => l_return_code,
134 x_recipe_id => l_recipe_id);
135
136 IF (l_recipe_id IS NOT NULL) THEN
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MESSAGE.SET_NAME('GMD', 'GMD_DUP_RECIPE');
139 FND_MSG_PUB.ADD;
140 END IF;
141
142 l_formula_id := p_recipe_header_rec.formula_id;
143
144
145 /* ====================================== */
146 /* If Routing details are provided, */
147 /* Check if it exists and is valid. */
148 /* Procedure returns routing id if given */
149 /* only number and vers. Routing is optional in recipe. */
150 /* If all three details given, only */
151 /* routing no and vers will be validated. */
152 /* ====================================== */
153
154 /* set l_routing_id to routing_id in record. If Check_Routing procedure */
155 /* finds the id, l_routing_id will be overwritten/given a value. */
156 /* Bug 1745549 L.R.Jackson Apr2001 */
157 l_routing_id := p_recipe_header_rec.routing_id;
158
159 IF (p_recipe_header_rec.routing_id IS NOT NULL) OR
160 (p_recipe_header_rec.routing_no IS NOT NULL AND
161 p_recipe_header_rec.routing_vers IS NOT NULL)
162 THEN
163 GMDRTVAL_PUB.check_routing( pRouting_no => p_recipe_header_rec.routing_no,
164 pRouting_vers => p_recipe_header_rec.routing_vers,
165 xRouting_id => l_routing_id,
166 xReturn_status=> l_return_status);
167 /* if error returned OR if no id given but given number and not version
168 or version and no number THEN give error message */
169 IF (l_return_status <> 'S') OR
170 (p_recipe_header_rec.routing_id IS NULL AND
171 NOT (p_recipe_header_rec.routing_no IS NOT NULL AND
172 p_recipe_header_rec.routing_vers IS NOT NULL))
173 THEN
174 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
175 FND_MSG_PUB.ADD;
176 END IF; -- end setting error message
177 END IF; -- end if routing info is given
178
179 /* ==================================== */
180 /* Check if recipe description has been */
181 /* provided. */
182 /* ==================================== */
183 If (p_recipe_header_rec.recipe_description IS NOT NULL) THEN
184 GMD_RECIPE_VAL.recipe_description
185 ( p_api_version => 1.0,
186 p_init_msg_list => FND_API.G_FALSE,
187 p_commit => FND_API.G_FALSE,
188 p_recipe_description => p_recipe_header_rec.recipe_description,
189 x_return_status => l_return_status,
190 x_msg_count => l_msg_count,
191 x_msg_data => l_msg_data,
192 x_return_code => l_return_code);
193
194 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DESCRIPTION');
197 FND_MSG_PUB.ADD;
198 END IF;
199 ELSE
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_DECRIPTION');
202 FND_MSG_PUB.ADD;
203 END IF;
204
205 /* ============================================ */
206 /* Validate Owner and Creator Organization code. */
207 /* While creating a recipe the */
208 /* owner_orgn_code and creation_orgn_code */
209 /* is the creators orgn code. */
210 /* ============================================ */
211
212 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_header_rec.owner_organization_id,
213 X_out_rec => l_out_rec,
214 X_return_status => l_return_status);
215
216 /*IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
219 FND_MSG_PUB.ADD;
220 END IF;*/
221
222 IF (x_return_status = 'S') THEN
223 /* ===================================== */
224 /* Get the recipe_id */
225 /* Sequence generator creator creates a */
226 /* recipe id */
227 /* ===================================== */
228 IF (p_recipe_header_rec.recipe_id IS NULL) THEN
229 SELECT gmd_recipe_id_s.nextval INTO l_recipe_id
230 FROM sys.dual;
231 ELSE
232 l_recipe_id := p_recipe_header_rec.recipe_id;
233 END IF;
234
235 /* Making an insert into Recipe table */
236 /* To incorporate MLS, we need to call the */
237 /* on-insert pkg to insert into <Recipe_b> */
238 /* and <Recipe_Tl> table */
239 /* Text Code is handled by another package. */
240
241 GMD_RECIPES_MLS.INSERT_ROW(
242 X_ROWID => l_rowid,
243 X_RECIPE_ID => l_recipe_id,
244 X_RECIPE_NO => p_recipe_header_rec.recipe_no,
245 X_RECIPE_VERSION => p_recipe_header_rec.recipe_version,
246 X_OWNER_ORGANIZATION_ID => p_recipe_header_rec.owner_organization_id,
247 X_CREATION_ORGANIZATION_ID => p_recipe_header_rec.creation_organization_id,
248 X_FORMULA_ID => l_formula_id,
249 X_ROUTING_ID => l_routing_id,
250 X_PROJECT_ID => NULL,
251 X_RECIPE_STATUS => p_recipe_header_rec.recipe_status,
252 X_CALCULATE_STEP_QUANTITY => p_recipe_header_rec.calculate_step_quantity,
253 X_PLANNED_PROCESS_LOSS => p_recipe_header_rec.planned_process_loss,
254 X_CONTIGUOUS_IND => p_recipe_header_rec.contiguous_ind,
255 X_ENHANCED_PI_IND => p_recipe_header_rec.enhanced_pi_ind,
256 X_RECIPE_TYPE => p_recipe_header_rec.recipe_type,
257 X_RECIPE_DESCRIPTION => p_recipe_header_rec.recipe_description,
258 X_OWNER_LAB_TYPE => p_recipe_header_rec.owner_lab_type,
259 X_ATTRIBUTE_CATEGORY => p_recipe_hdr_flex_rec.attribute_category,
260 X_ATTRIBUTE1 => p_recipe_hdr_flex_rec.attribute1,
261 X_ATTRIBUTE2 => p_recipe_hdr_flex_rec.attribute2,
262 X_ATTRIBUTE3 => p_recipe_hdr_flex_rec.attribute3,
263 X_ATTRIBUTE4 => p_recipe_hdr_flex_rec.attribute4,
264 X_ATTRIBUTE5 => p_recipe_hdr_flex_rec.attribute5,
265 X_ATTRIBUTE6 => p_recipe_hdr_flex_rec.attribute6,
266 X_ATTRIBUTE7 => p_recipe_hdr_flex_rec.attribute7,
267 X_ATTRIBUTE8 => p_recipe_hdr_flex_rec.attribute8,
268 X_ATTRIBUTE9 => p_recipe_hdr_flex_rec.attribute9,
269 X_ATTRIBUTE10 => p_recipe_hdr_flex_rec.attribute10,
270 X_ATTRIBUTE11 => p_recipe_hdr_flex_rec.attribute11,
271 X_ATTRIBUTE12 => p_recipe_hdr_flex_rec.attribute12,
272 X_ATTRIBUTE13 => p_recipe_hdr_flex_rec.attribute13,
273 X_ATTRIBUTE14 => p_recipe_hdr_flex_rec.attribute14,
274 X_ATTRIBUTE15 => p_recipe_hdr_flex_rec.attribute15,
275 X_ATTRIBUTE16 => p_recipe_hdr_flex_rec.attribute16,
276 X_ATTRIBUTE17 => p_recipe_hdr_flex_rec.attribute17,
277 X_ATTRIBUTE18 => p_recipe_hdr_flex_rec.attribute18,
278 X_ATTRIBUTE19 => p_recipe_hdr_flex_rec.attribute19,
279 X_ATTRIBUTE20 => p_recipe_hdr_flex_rec.attribute20,
280 X_ATTRIBUTE21 => p_recipe_hdr_flex_rec.attribute21,
281 X_ATTRIBUTE22 => p_recipe_hdr_flex_rec.attribute22,
282 X_ATTRIBUTE23 => p_recipe_hdr_flex_rec.attribute23,
283 X_ATTRIBUTE24 => p_recipe_hdr_flex_rec.attribute24,
284 X_ATTRIBUTE25 => p_recipe_hdr_flex_rec.attribute25,
285 X_ATTRIBUTE26 => p_recipe_hdr_flex_rec.attribute26,
286 X_ATTRIBUTE27 => p_recipe_hdr_flex_rec.attribute27,
287 X_ATTRIBUTE28 => p_recipe_hdr_flex_rec.attribute28,
288 X_ATTRIBUTE29 => p_recipe_hdr_flex_rec.attribute29,
289 X_ATTRIBUTE30 => p_recipe_hdr_flex_rec.attribute30,
290 X_DELETE_MARK => 0,
291 X_TEXT_CODE => p_recipe_header_rec.text_code,
292 X_OWNER_ID => NVL(p_recipe_header_rec.owner_id
293 ,gmd_api_grp.user_id),
294 X_CREATION_DATE => NVL(p_recipe_header_rec.creation_date
295 ,SYSDATE),
296 X_CREATED_BY => NVL(p_recipe_header_rec.created_by
297 ,gmd_api_grp.user_id),
298 X_LAST_UPDATE_DATE => NVL(p_recipe_header_rec.last_update_date
299 ,SYSDATE),
300 X_LAST_UPDATED_BY => NVL(p_recipe_header_rec.last_updated_by
301 ,gmd_api_grp.user_id),
302 X_LAST_UPDATE_LOGIN => NVL(p_recipe_header_rec.last_update_login
303 ,gmd_api_grp.login_id),
304 X_FIXED_PROCESS_LOSS => p_recipe_header_rec.fixed_process_loss,
305 X_FIXED_PROCESS_LOSS_UOM => p_recipe_header_rec.fixed_process_loss_uom
306 );
307 END IF;
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 WHEN OTHERS THEN
312 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 END CREATE_RECIPE_HEADER;
315
316
317 /* ================================================== */
318 /* Procedure: */
319 /* Update_Recipe_Header */
320 /* */
321 /* DESCRIPTION: */
322 /* This PL/SQL procedure is responsible for */
323 /* updating a recipe */
324 /* */
325 /* ================================================== */
326 /* Start of commments */
327 /* API name : Update_Recipe_Header */
328 /* Type : Private */
329 /* Function : */
330 /* Paramaters : */
331 /* p_recipe_tbl IN Required */
332 /* */
333 /* OUT x_return_status */
334 /* */
335 /* */
336 /* Notes : */
337 /* Sukarna Reddy 03/14/02. Bug 2099699. Modified */
338 /* to include validation for routing. */
339 /* Vipul Vaish 02/12/04 BUG#3427313 */
340 /* Modified CURSOR cur_getrcprout - Added one more */
341 /* condition in the Where clause. */
342 /* KSHUKLA added as per as 5138316 to incorporate */
343 /* deletion of the records for step and step*/
344 /* material association if the routing is */
345 /* nullified. 10-APR-2006 */
346 /* End of comments */
347
348 PROCEDURE UPDATE_RECIPE_HEADER
349 ( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
350 p_flex_header_rec IN GMD_RECIPE_HEADER.update_flex ,
351 x_return_status OUT NOCOPY VARCHAR2
352 ) IS
353
354 /* Defining all local variables */
355 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_HEADER';
356
357 l_user_id fnd_user.user_id%TYPE := 0;
358 l_surrogate gmd_recipes.recipe_id%TYPE := 0;
359
360 l_meaning GMD_STATUS.meaning%TYPE;
361 l_description GMD_STATUS.description%TYPE;
362
363 /* Formula variables */
364 l_formula_id fm_form_mst.formula_id%TYPE := 0;
365 l_formula_no fm_form_mst.formula_no%TYPE;
366 l_formula_vers fm_form_mst.formula_vers%TYPE;
367
368 /* Routing variables */
369 l_routing_id fm_rout_hdr.routing_id%TYPE := 0;
370 l_routing_no fm_rout_hdr.routing_no%TYPE;
371 l_routing_vers fm_rout_hdr.routing_vers%TYPE;
372
373
374 /* Organization related */
375 l_plant_ind NUMBER;
376 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
377
378 /* Variables used for defining status */
379 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
380 l_return_code NUMBER := 0;
381 l_rowid VARCHAR2(32);
382
383 /* Error message count and data */
384 l_msg_count NUMBER;
385 l_msg_data VARCHAR2(2000);
386
387
388 CURSOR cur_getrcprout(pRecipe_id GMD_RECIPES.recipe_id%TYPE) IS
389 Select r.routing_id,b.status_type
390 From GMD_RECIPES r,gmd_status b
391 Where Recipe_id = pRecipe_id
392 And r.Recipe_status = b.Status_code;--BUG#3427313
393
394 -- Cursor and declaration as per as Bug 5138316 KSHUKLA
395 CURSOR Cur_get_formula(vRecipe_id NUMBER, vFormula_id NUMBER) IS
396 SELECT 1
397 FROM gmd_recipe_step_materials a, fm_matl_dtl b
398 WHERE a.recipe_id = vRecipe_id
399 AND a.formulaline_id = b.formulaline_id
400 AND b.formula_id <>vFormula_id ;
401
402 CURSOR Cur_get_formula_val(vRecipe_id NUMBER, vFormula_id NUMBER) IS
403 SELECT 1
404 FROM gmd_recipe_validity_rules a
405 WHERE a.recipe_id = vRecipe_id
406 AND item_id not in (select item_id
407 from fm_matl_dtl
408 where line_type = 1
409 and formula_id = vFormula_id);
410
411 CURSOR Cur_check_routing(vRecipe_id NUMBER, vRouting_id NUMBER) IS
412 SELECT 1
413 FROM gmd_recipe_step_materials a, fm_rout_dtl b
414 WHERE a.recipe_id = vRecipe_id
415 AND a.routingstep_id = b.routingstep_id
416 AND b.routing_id <> NVL(vRouting_id, 0);
417
418 v_dummy NUMBER;
419 l_deleteRoutDependent BOOLEAN := FALSE;
420 l_delValRule BOOLEAN := FALSE;
421 -- End of declaration as per as 5138316 KSHUKLA
422 l_val_rt_status BOOLEAN;
423 x_routing_id NUMBER;
424 l_status_type gmd_status.status_type%type;
425 BEGIN
426 /* Initialize API return status to success */
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- Bug 2099699. Sukarna Reddy dt 02/19/02.
430 -- Check if proper routing is associated with recipe
431 IF (p_recipe_header_rec.routing_id IS NOT NULL) THEN
432 -- Get the routing if one existed before for this recipe.
433 OPEN cur_getrcprout(p_recipe_header_rec.recipe_id);
434 FETCH Cur_getrcprout INTO x_routing_id,l_status_type;
435 CLOSE Cur_getrcprout;
436
437 --Check if we are associating routing with recipe for the first time.
438 -- Check if new routing is different from old routing if one
439 -- is already associated with recipe.
440 -- This check will work for third party and also when called from form.
441 IF (x_routing_id IS NULL) THEN
442 l_val_rt_status := gmd_recipe_val.check_routing_validity
443 (p_recipe_header_rec.routing_id,
444 p_recipe_header_rec.recipe_Status);
445 IF (NOT l_val_rt_status) THEN
446 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
447 FND_MSG_PUB.ADD;
448 RAISE FND_API.G_EXC_ERROR;
449 END IF;
450 ELSIF (l_status_type IN ('700','900')) THEN
451 IF (x_routing_id IS NOT NULL AND
452 p_recipe_header_rec.routing_id IS NOT NULL AND
453 x_routing_id <> p_recipe_header_rec.routing_id) THEN
454 FND_MESSAGE.SET_NAME('GMD','GMD_RECIPE_INVALID_MODE');
455 FND_MSG_PUB.ADD;
456 RAISE FND_API.G_EXC_ERROR;
457 END IF;
458 END IF;
459 END IF;
460 -- End bug 2099699
461
462 /* ============================================= */
463 /* If Recipe is frozen no updates can be made */
464 /* This needs to be a part of the GMD_COMMON_VAL , */
465 /* can be used by formulas and recipes */
466 /* ============================================= */
467 GMD_COMMON_VAL.Get_Status
468 ( Status_code => p_recipe_header_rec.Recipe_status ,
469 Meaning => l_meaning ,
470 Description => l_description ,
471 x_return_status => l_return_status
472 );
473
474 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 END IF;
477
478 IF (p_recipe_header_rec.Recipe_status BETWEEN '900' and '999') THEN
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_UPDATE_NOT_ALLOWED');
481 FND_MSG_PUB.ADD;
482 END IF;
483
484 l_return_status := 'S';
485
486 /* ====================================== */
487 /* If Routing details are provided, */
488 /* Check if it exists and is valid. */
489 /* Procedure returns routing id if given */
490 /* only number and vers. Routing is optional in recipe. */
491 /* If all three details given, only */
492 /* routing no and vers will be validated. */
493 /* ====================================== */
494
495 /* set l_routing_id to routing_id in record. If Check_Routing procedure */
496 /* finds the id, l_routing_id will be overwritten/given a value. */
497 l_routing_id := p_recipe_header_rec.routing_id;
498
499 IF (p_recipe_header_rec.routing_id IS NOT NULL) OR
500 (p_recipe_header_rec.routing_no IS NOT NULL AND
501 p_recipe_header_rec.routing_vers IS NOT NULL)
502 THEN
503 GMDRTVAL_PUB.check_routing
504 ( pRouting_no => p_recipe_header_rec.routing_no,
505 pRouting_vers => p_recipe_header_rec.routing_vers,
506 xRouting_id => l_routing_id,
507 xReturn_status=> l_return_status);
508 /* if error returned OR if no id given but given number and not version
509 or version and no number THEN give error message */
510 IF (l_return_status <> 'S') OR
511 (p_recipe_header_rec.routing_id IS NULL AND
512 NOT (p_recipe_header_rec.routing_no IS NOT NULL AND
513 p_recipe_header_rec.routing_vers IS NOT NULL))
514 THEN
515 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
516 FND_MSG_PUB.ADD;
517 END IF; -- end setting error message
518 END IF; -- end if routing info is given
519
520 /* ===================================== */
521 /* Validate Owner and Creators */
522 /* Organization code */
523 /* ===================================== */
524
525 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_header_rec.owner_organization_id,
526 X_out_rec => l_out_rec,
527 X_return_status => l_return_status);
528
529 /*IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
530 x_return_status := FND_API.G_RET_STS_ERROR;
531 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
532 FND_MSG_PUB.ADD;
533 END IF;*/
534
535 /* Making an updates into Recipe table */
536 /* To incorporate MLS, we need to call the */
537 /* on-insert pkg to insert into <Recipe_b> */
538 /* and <Recipe_Tl> table */
539 /* Text Code is handled by another package. */
540 IF (x_return_status = 'S') THEN
541 GMD_RECIPES_MLS.UPDATE_ROW(
542 X_RECIPE_ID => p_recipe_header_rec.recipe_id,
543 X_OWNER_ID => p_recipe_header_rec.owner_id,
544 X_OWNER_LAB_TYPE => p_recipe_header_rec.owner_lab_type,
545 X_DELETE_MARK => p_recipe_header_rec.delete_mark,
546 X_RECIPE_NO => p_recipe_header_rec.recipe_no,
547 X_RECIPE_VERSION => p_recipe_header_rec.recipe_version,
548 X_OWNER_ORGANIZATION_ID => p_recipe_header_rec.owner_organization_id,
549 X_CREATION_ORGANIZATION_ID => p_recipe_header_rec.creation_organization_id,
550 X_FORMULA_ID => p_recipe_header_rec.formula_id,
551 X_ROUTING_ID => l_routing_id,
552 X_PROJECT_ID => NULL,
553 X_RECIPE_STATUS => p_recipe_header_rec.recipe_status,
554 X_CALCULATE_STEP_QUANTITY => p_recipe_header_rec.calculate_step_quantity,
555 X_PLANNED_PROCESS_LOSS => p_recipe_header_rec.planned_process_loss,
556 X_CONTIGUOUS_IND => p_recipe_header_rec.contiguous_ind,
557 X_ENHANCED_PI_IND => p_recipe_header_rec.enhanced_pi_ind,
558 X_RECIPE_TYPE => p_recipe_header_rec.recipe_type,
559 X_RECIPE_DESCRIPTION => p_recipe_header_rec.recipe_description,
560 X_ATTRIBUTE_CATEGORY => p_flex_header_rec.attribute_category,
561 X_ATTRIBUTE1 => p_flex_header_rec.attribute1,
562 X_ATTRIBUTE2 => p_flex_header_rec.attribute2,
563 X_ATTRIBUTE3 => p_flex_header_rec.attribute3,
564 X_ATTRIBUTE4 => p_flex_header_rec.attribute4,
565 X_ATTRIBUTE5 => p_flex_header_rec.attribute5,
566 X_ATTRIBUTE6 => p_flex_header_rec.attribute6,
567 X_ATTRIBUTE7 => p_flex_header_rec.attribute7,
568 X_ATTRIBUTE8 => p_flex_header_rec.attribute8,
569 X_ATTRIBUTE9 => p_flex_header_rec.attribute9,
570 X_ATTRIBUTE10 => p_flex_header_rec.attribute10,
571 X_ATTRIBUTE11 => p_flex_header_rec.attribute11,
572 X_ATTRIBUTE12 => p_flex_header_rec.attribute12,
573 X_ATTRIBUTE13 => p_flex_header_rec.attribute13,
574 X_ATTRIBUTE14 => p_flex_header_rec.attribute14,
575 X_ATTRIBUTE15 => p_flex_header_rec.attribute15,
576 X_ATTRIBUTE16 => p_flex_header_rec.attribute16,
577 X_ATTRIBUTE17 => p_flex_header_rec.attribute17,
578 X_ATTRIBUTE18 => p_flex_header_rec.attribute18,
579 X_ATTRIBUTE19 => p_flex_header_rec.attribute19,
580 X_ATTRIBUTE20 => p_flex_header_rec.attribute20,
581 X_ATTRIBUTE21 => p_flex_header_rec.attribute21,
582 X_ATTRIBUTE22 => p_flex_header_rec.attribute22,
583 X_ATTRIBUTE23 => p_flex_header_rec.attribute23,
584 X_ATTRIBUTE24 => p_flex_header_rec.attribute24,
585 X_ATTRIBUTE25 => p_flex_header_rec.attribute25,
586 X_ATTRIBUTE26 => p_flex_header_rec.attribute26,
587 X_ATTRIBUTE27 => p_flex_header_rec.attribute27,
588 X_ATTRIBUTE28 => p_flex_header_rec.attribute28,
589 X_ATTRIBUTE29 => p_flex_header_rec.attribute29,
590 X_ATTRIBUTE30 => p_flex_header_rec.attribute30,
591 X_TEXT_CODE => p_recipe_header_rec.text_code,
592 X_LAST_UPDATE_DATE => NVL(p_recipe_header_rec.last_update_date
593 ,SYSDATE),
594 X_LAST_UPDATED_BY => p_recipe_header_rec.last_updated_by,
595 X_LAST_UPDATE_LOGIN => p_recipe_header_rec.last_update_login,
596 X_FIXED_PROCESS_LOSS => p_recipe_header_rec.fixed_process_loss,
597 X_FIXED_PROCESS_LOSS_UOM => p_recipe_header_rec.fixed_process_loss_uom
598 );
599
600 -- KSHUKLA added following as per as 5187046
601 l_formula_id := p_recipe_header_rec.formula_id;
602
603 /*-----------------------------------------
604 # KSHUKLA added the update statement
605 # While the recipe is deleted set the
606 # validity rules as deleted.
607 -------------------------------------------*/
608 IF p_recipe_header_rec.delete_mark =1 then
609 update GMD_RECIPE_VALIDITY_RULES
610 set DELETE_MARK = p_recipe_header_rec.delete_mark
611 WHERE recipe_id = p_recipe_header_rec.recipe_id;
612 END IF;
613
614 /*-----------------------------------------
615 # KSHUKLA added the delete statement
616 # as if the recipe is nullified
617 # delete the step and step material
618 # association records.
619 -------------------------------------------*/
620 -- bug 5138316 KSHUKLA
621
622 --Deleting the validity rules if formula_no or vers is updated
623 OPEN Cur_get_formula_val(p_recipe_header_rec.recipe_id, l_formula_id);
624 FETCH Cur_get_formula_val INTO v_dummy;
625 IF (Cur_get_formula_val%FOUND) THEN
626 l_delValRule := TRUE;
627 END IF;
628 CLOSE Cur_get_formula_val;
629
630 IF l_routing_id IS NULL THEN
631 l_deleteRoutDependent := TRUE;
632 ELSE
633 OPEN Cur_get_formula(p_recipe_header_rec.recipe_id, l_formula_id);
634 FETCH Cur_get_formula INTO v_dummy;
635 IF (Cur_get_formula%FOUND) THEN
636 l_deleteRoutDependent := TRUE;
637 ELSE
638 OPEN Cur_check_routing(p_recipe_header_rec.recipe_id, l_routing_id);
639 FETCH Cur_check_routing INTO v_dummy;
640 IF (Cur_check_routing%FOUND) THEN
641 l_deleteRoutDependent := TRUE;
642 END IF;
643 CLOSE Cur_check_routing;
644 END IF;
645 CLOSE Cur_get_formula;
646 END IF;
647 -- Execute the delete statements
648 IF l_deleteRoutDependent THEN
649 delete from gmd_recipe_routing_steps
650 WHERE recipe_id =p_recipe_header_rec.recipe_id;
651
652 delete from gmd_recipe_step_materials
653 WHERE recipe_id =p_recipe_header_rec.recipe_id;
654
655 END IF;
656
657 IF l_delValRule THEN
658 delete from gmd_recipe_validity_rules
659 where recipe_id =p_recipe_header_rec.recipe_id;
660 END IF;
661 --End bug 5138316 KSHUKLA
662
663 END IF; -- End if for x_return_status = 'S'
664 EXCEPTION
665 WHEN FND_API.G_EXC_ERROR THEN
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 WHEN OTHERS THEN
668 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 END UPDATE_RECIPE_HEADER;
671
672
673 PROCEDURE DELETE_RECIPE_HEADER
674 ( p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
675 p_flex_header_rec IN GMD_RECIPE_HEADER.update_flex ,
676 x_return_status OUT NOCOPY VARCHAR2
677 ) IS
678
679 BEGIN
680
681 /* Call the update API */
682 /* Delete in OPM world is not a physical delete. Its a logical delete */
683 /* i.e its an update with the delete_mark set to 1 */
684 /* Therefore prior to calling this procedure the delete_mark need to be set to 1 */
685
686 GMD_RECIPE_HEADER_PVT.UPDATE_RECIPE_HEADER
687 (p_recipe_header_rec => p_recipe_header_rec ,
688 p_flex_header_rec => p_flex_header_rec ,
689 x_return_status => x_return_status
690 );
691
692 END DELETE_RECIPE_HEADER;
693
694 /* ===================================================*/
695 /* Procedure: */
696 /* Copy_Recipe_Header */
697 /* DESCRIPTION: */
698 /* This PL/SQL procedure is responsible for */
699 /* inserting a recipe */
700 /* */
701 /* ===================================================*/
702 /* Start of commments */
703 /* API name : Copy_Recipe_Header */
704 /* Type : Private */
705 /* Function : */
706 /* Paramaters : */
707 /* p_recipe_tbl IN Required */
708 /* p_old_recipe_id */
709 /* p_recipe_header_rec */
710 /* p_recipe_hdr_flex_rec */
711 /* */
712 /* OUT x_return_status */
713 /* */
714 /* */
715 /* Notes : */
716 /* End of comments */
717 /* ===================================================*/
718
719 PROCEDURE COPY_RECIPE_HEADER
720 ( p_old_recipe_id IN GMD_RECIPES_B.recipe_id%TYPE ,
721 p_recipe_header_rec IN GMD_RECIPE_HEADER.recipe_hdr ,
722 p_recipe_hdr_flex_rec IN GMD_RECIPE_HEADER.flex ,
723 x_return_status OUT NOCOPY VARCHAR2
724 ) IS
725
726 CURSOR get_old_recipe_record(vRecipe_id GMD_RECIPES_B.recipe_id%TYPE) IS
727 SELECT *
728 FROM gmd_recipes
729 WHERE recipe_id = vRecipe_id;
730
731 CURSOR get_next_recipe_version (vRecipe_no GMD_RECIPES_B.recipe_no%TYPE) IS
732 SELECT max(Recipe_version) + 1
733 FROM gmd_recipes_b
734 WHERE Recipe_no = vRecipe_no;
735
736 CURSOR get_formula_no_and_vers(vFormula_id FM_FORM_MST_B.formula_id%TYPE) IS
737 SELECT formula_no, formula_vers
738 FROM fm_form_mst_b
739 WHERE formula_id = vFormula_id;
740
741 CURSOR get_formula_id(vFormula_no FM_FORM_MST_B.formula_no%TYPE,
742 vFormula_vers FM_FORM_MST_B.formula_vers%TYPE) IS
743 SELECT formula_id
744 FROM fm_form_mst_b
745 WHERE formula_no = vFormula_no
746 AND formula_vers = vFormula_vers;
747
748 CURSOR get_routing_id(vRouting_no GMD_ROUTINGS_B.routing_no%TYPE,
749 vRouting_vers GMD_ROUTINGS_B.routing_vers%TYPE) IS
750 SELECT routing_id
751 FROM gmd_routings_b
752 WHERE routing_no = vRouting_no
753 AND routing_vers = vRouting_vers;
754
755 CURSOR get_routing_no_and_vers(vRouting_id GMD_ROUTINGS_B.routing_id%TYPE) IS
756 SELECT routing_no, routing_vers
757 FROM gmd_routings_b
758 WHERE routing_id = vRouting_id;
759
760 /* Defining all local variables */
761 l_api_name CONSTANT VARCHAR2(30) := 'COPY_RECIPE_HEADER';
762 l_api_version CONSTANT NUMBER := 1.0;
763 l_return_code NUMBER;
764 l_msg_count NUMBER;
765 l_msg_data VARCHAR2(2000);
766 l_return_status VARCHAR2(1) := 'S';
767 l_rowcount NUMBER := 0;
768 l_plant_ind NUMBER;
769 l_changed_flag BOOLEAN := FALSE;
770
771 l_recipe_header_rec GMD_RECIPE_HEADER.recipe_hdr;
772 l_recipe_hdr_flex_rec GMD_RECIPE_HEADER.flex;
773
774 l_old_formula_no FM_FORM_MST_B.formula_no%TYPE;
775 l_old_formula_vers FM_FORM_MST_B.formula_vers%TYPE;
776 l_old_routing_no GMD_ROUTINGS_B.routing_no%TYPE;
777 l_old_routing_vers GMD_ROUTINGS_B.routing_vers%TYPE;
778 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
779
780 Copy_Recipe_Err EXCEPTION;
781
782 BEGIN
783
784 IF (l_debug = 'Y') THEN
785 gmd_debug.log_initialize('CopyRecipe');
786 END IF;
787
788 /* Get the old recipe record and compare with the new one that
789 is passed in. Compare the old record with the new record that
790 gets passed IN. If there are any difference (eg formula used in the new
791 Recipe might be different) replace the OLD value with the new one
792 (in this eg. we ould replace the old formula with the new one).
793 */
794 FOR old_recipe_rec IN get_old_recipe_record(p_old_recipe_id) LOOP
795 l_rowcount := l_rowcount + 1;
796 IF (l_debug = 'Y') THEN
797 gmd_debug.put_line('-- 1. Check for Recipe id ');
798 END IF;
799 IF p_recipe_header_rec.recipe_id = FND_API.G_MISS_NUM THEN
800 fnd_message.set_name ('GMI', 'GMI_MISSING');
801 fnd_message.set_token ('MISSING', 'RECIPE_ID');
802 fnd_msg_pub.add;
803 RAISE copy_recipe_err;
804 ELSE
805 IF p_recipe_header_rec.recipe_id IS NULL THEN
806 -- Generate Recipe Id from sequence
807 SELECT gmd_recipe_id_s.nextval
808 INTO l_recipe_header_rec.recipe_id
809 FROM sys.dual;
810 ELSE -- Recipe id has been provided
811 -- Chcek if Recipe id is not a negative number
812 IF p_recipe_header_rec.recipe_id < 0 THEN
813 fnd_message.set_name('GMD', 'GMD_RECIPE_NOT_VALID');
814 fnd_msg_pub.add;
815 RAISE copy_recipe_err;
816 END IF;
817
818 -- Check if this recipe id already exists in our system
819 gmd_recipe_val.recipe_exists
820 ( p_api_version => 1.0 ,
821 p_recipe_id => p_recipe_header_rec.Recipe_id ,
822 p_recipe_no => NULL ,
823 p_recipe_version => NULL ,
824 x_return_status => l_return_status ,
825 x_msg_count => l_msg_count ,
826 x_msg_data => l_msg_data ,
827 x_return_code => l_return_code ,
828 x_recipe_id => l_recipe_header_rec.recipe_id
829 );
830 -- Its ok only if the above validation returns an error.
831 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
832 l_recipe_header_rec.recipe_id := p_recipe_header_rec.recipe_id;
833 ELSE -- This is an invalid Recipe Id
834 fnd_message.set_name('GMD', 'GMD_RECIPE_NOT_VALID');
835 fnd_msg_pub.add;
836 RAISE copy_recipe_err;
837 END IF; -- End condition when x_ret_sts is error
838 END IF; -- End condition when recipe id is null
839 END IF; -- End condition when recipe id is G_miss...
840
841 IF (l_debug = 'Y') THEN
842 gmd_debug.put_line('-- 2. Check for Recipe Number (recipe_no) ');
843 END IF;
844
845 IF p_recipe_header_rec.recipe_no = FND_API.G_MISS_CHAR THEN
846 fnd_message.set_name ('GMI', 'GMI_MISSING');
847 fnd_message.set_token ('MISSING', 'RECIPE_NO');
848 fnd_msg_pub.add;
849 RAISE copy_recipe_err;
850 ELSE
851 IF p_recipe_header_rec.recipe_no IS NULL THEN
852 l_recipe_header_rec.recipe_no := old_recipe_rec.recipe_no;
853 ELSIF -- recipe_no value is different from that in db
854 p_recipe_header_rec.recipe_no <> old_recipe_rec.recipe_no THEN
855 gmd_recipe_val.recipe_name
856 ( p_api_version => 1.0
857 ,p_recipe_no => p_recipe_header_rec.recipe_no
858 ,p_recipe_version => p_recipe_header_rec.recipe_version
859 ,p_action_code => 'I'
860 ,x_return_status => l_return_status
861 ,x_msg_count => l_msg_count
862 ,x_msg_data => l_msg_data
863 ,x_return_code => l_return_code
864 ,x_recipe_id => l_recipe_header_rec.recipe_id
865 );
866
867 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
868 l_recipe_header_rec.recipe_no := p_recipe_header_rec.recipe_no;
869 l_changed_flag := TRUE;
870 ELSE
871 fnd_message.set_name('GMD', 'GMD_RECIPE_NOT_VALID');
872 fnd_msg_pub.add;
873 RAISE copy_recipe_err;
874 END IF;
875 ELSE -- recipe_no value is same as that in db
876 l_recipe_header_rec.recipe_no := old_recipe_rec.recipe_no ;
877 END IF; -- End condition when recipe no is null
878 END IF; -- End condition when recipe_no is G_miss...
879
880
881 IF (l_debug = 'Y') THEN
882 gmd_debug.put_line('-- 3. Check for Recipe Version (recipe_version) ');
883 END IF;
884
885 IF p_recipe_header_rec.recipe_version = FND_API.G_MISS_NUM THEN
886 fnd_message.set_name ('GMI', 'GMI_MISSING');
887 fnd_message.set_token ('MISSING', 'RECIPE_VERSION');
888 fnd_msg_pub.add;
889 RAISE copy_recipe_err;
890 ELSE
891 IF p_recipe_header_rec.recipe_version IS NULL THEN
892 l_recipe_header_rec.recipe_version
893 := old_recipe_rec.recipe_version;
894 ELSIF -- recipe_version value is different from that in db
895 p_recipe_header_rec.recipe_version
896 <> old_recipe_rec.recipe_version THEN
897 gmd_recipe_val.recipe_name
898 ( p_api_version => 1.0
899 ,p_recipe_no => p_recipe_header_rec.recipe_no
900 ,p_recipe_version => p_recipe_header_rec.recipe_version
901 ,p_action_code => 'I'
902 ,x_return_status => l_return_status
903 ,x_msg_count => l_msg_count
904 ,x_msg_data => l_msg_data
905 ,x_return_code => l_return_code
906 ,x_recipe_id => l_recipe_header_rec.recipe_id
907 );
908
909 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
910 l_recipe_header_rec.recipe_version
911 := p_recipe_header_rec.recipe_version;
912 l_changed_flag := TRUE;
913 ELSE
914 fnd_message.set_name('GMD', 'GMD_RECIPE_NOT_VALID');
915 fnd_msg_pub.add;
916 RAISE copy_recipe_err;
917 END IF;
918 ELSE -- recipe_version value is same as that in db
919 l_recipe_header_rec.recipe_version
920 := old_recipe_rec.recipe_version ;
921 END IF; -- End condition when recipe_version is null
922
923 -- IF recipe_nos in db and new record are the same
924 IF UPPER(l_recipe_header_rec.recipe_no)
925 = UPPER(old_recipe_rec.recipe_no) THEN
926 OPEN get_next_recipe_version(l_recipe_header_rec.recipe_no);
927 FETCH get_next_recipe_version
928 INTO l_recipe_header_rec.recipe_version ;
929 IF get_next_recipe_version%NOTFOUND THEN
930 CLOSE get_next_recipe_version;
931 fnd_message.set_name('GMD', 'GMD_RECIPE_NOT_VALID');
932 fnd_msg_pub.add;
933 RAISE copy_recipe_err;
934 END IF;
935 CLOSE get_next_recipe_version;
936 END IF;
937 END IF; -- End condition when recipe_version is G_miss...
938
939 -- At this stage we should have the recipe_no , versions and the
940 -- recipe_id. If not raise an exception.
941 IF (l_recipe_header_rec.recipe_no IS NULL
942 OR l_recipe_header_rec.recipe_version IS NULL
943 OR l_recipe_header_rec.recipe_id IS NULL) THEN
944 fnd_message.set_name ('GMI', 'GMI_MISSING');
945 fnd_message.set_token ('MISSING', 'RECIPE_NO OR RECIPE_VERSION OR RECIPE_ID');
946 fnd_msg_pub.add;
947 RAISE copy_recipe_err;
948 END IF;
949
950 IF (l_debug = 'Y') THEN
951 gmd_debug.put_line('-- 3.1. Check for Recipe Description ');
952 END IF;
953
954 IF p_recipe_header_rec.recipe_description = FND_API.G_MISS_CHAR THEN
955 fnd_message.set_name ('GMI', 'GMI_MISSING');
956 fnd_message.set_token ('MISSING', 'RECIPE_DESCRIPTION');
957 fnd_msg_pub.add;
958 RAISE copy_recipe_err;
959 ELSE
960 IF p_recipe_header_rec.recipe_description IS NULL THEN
961 l_recipe_header_rec.recipe_description
962 := old_recipe_rec.recipe_description;
963 ELSIF -- if the description is different from that in db
964 p_recipe_header_rec.recipe_description
965 <> old_recipe_rec.recipe_description THEN
966 l_recipe_header_rec.recipe_description
967 := p_recipe_header_rec.recipe_description;
968 l_changed_flag := TRUE;
969 ELSE -- if the description is same as that in db
970 l_recipe_header_rec.recipe_description
971 := old_recipe_rec.recipe_description ;
972 END IF; -- End condition when recipe_desc is null
973 END IF; -- End condition when recipe_desc is G_miss...
974
975 IF (l_debug = 'Y') THEN
976 gmd_debug.put_line('-- 4. Check for user id ');
977 END IF;
978 IF p_recipe_header_rec.user_id = FND_API.G_MISS_NUM THEN
979 fnd_message.set_name ('GMI', 'GMI_MISSING');
980 fnd_message.set_token ('MISSING', 'USER ID');
981 fnd_msg_pub.add;
982 RAISE copy_recipe_err;
983 ELSE
984 IF p_recipe_header_rec.user_id IS NULL THEN
985 IF p_recipe_header_rec.user_name IS NOT NULL THEN
986 GMA_GLOBAL_GRP.get_who(p_recipe_header_rec.user_name
987 ,l_recipe_header_rec.user_id);
988 ELSE
989 l_recipe_header_rec.user_id := old_recipe_rec.created_by;
990 END IF;
991 END IF; -- End condition when user id is null
992 END IF; -- End condition when user id is G_miss...
993
994 IF (l_debug = 'Y') THEN
995 gmd_debug.put_line('-- 5. Check for owner orgn code ');
996 END IF;
997
998 IF p_recipe_header_rec.owner_organization_id = FND_API.G_MISS_NUM THEN
999 fnd_message.set_name ('GMI', 'GMI_MISSING');
1000 fnd_message.set_token ('MISSING', 'OWNER_ORGANIZATION_ID');
1001 fnd_msg_pub.add;
1002 RAISE copy_recipe_err;
1003 ELSE
1004 IF p_recipe_header_rec.owner_organization_id IS NULL THEN
1005 l_recipe_header_rec.owner_organization_id
1006 := old_recipe_rec.owner_organization_id;
1007 ELSIF -- the owner orgn code is diff from that in db
1008 p_recipe_header_rec.owner_organization_id
1009 <> old_recipe_rec.owner_organization_id THEN
1010
1011 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_header_rec.owner_organization_id,
1012 X_out_rec => l_out_rec,
1013 X_return_status => l_return_status);
1014
1015 IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
1018 FND_MSG_PUB.ADD;
1019 ELSE
1020 l_recipe_header_rec.owner_organization_id
1021 := p_recipe_header_rec.owner_organization_id;
1022 l_changed_flag := TRUE;
1023 END IF;
1024 ELSE -- the owner orgn code is same as that in db
1025 l_recipe_header_rec.owner_organization_id
1026 := old_recipe_rec.owner_organization_id;
1027 END IF; -- End condition when owner orgn code is null
1028 END IF; -- End condition when owner_orgn_code is G_miss...
1029
1030
1031 IF (l_debug = 'Y') THEN
1032 gmd_debug.put_line('-- 6. Check for creation orgn code ');
1033 END IF;
1034 IF p_recipe_header_rec.creation_organization_id = FND_API.G_MISS_NUM THEN
1035 fnd_message.set_name ('GMI', 'GMI_MISSING');
1036 fnd_message.set_token ('MISSING', 'CREATION_ORGANIZATION_ID');
1037 fnd_msg_pub.add;
1038 RAISE copy_recipe_err;
1039 ELSE
1040 IF p_recipe_header_rec.creation_organization_id IS NULL THEN
1041 l_recipe_header_rec.creation_organization_id
1042 := old_recipe_rec.creation_organization_id;
1043 ELSIF -- the creation orgn code is diff from that in db
1044 p_recipe_header_rec.creation_organization_id
1045 <> old_recipe_rec.creation_organization_id THEN
1046
1047 gmd_api_grp.fetch_parm_values (P_orgn_id => p_recipe_header_rec.creation_organization_id,
1048 X_out_rec => l_out_rec,
1049 X_return_status => l_return_status);
1050
1051 IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
1054 FND_MSG_PUB.ADD;
1055 ELSE
1056 l_recipe_header_rec.creation_organization_id
1057 := p_recipe_header_rec.creation_organization_id;
1058 l_changed_flag := TRUE;
1059 END IF;
1060 ELSE -- the creation orgn code is same as that in db
1061 l_recipe_header_rec.creation_organization_id
1062 := old_recipe_rec.creation_organization_id;
1063 END IF; -- End condition when creation orgn code is null
1064 END IF; -- End condition when creation_orgn_code is G_miss...
1065
1066
1067 IF (l_debug = 'Y') THEN
1068 gmd_debug.put_line('-- 7. Check for formula id ');
1069 END IF;
1070 IF p_recipe_header_rec.formula_id = FND_API.G_MISS_NUM THEN
1071 fnd_message.set_name ('GMI', 'GMI_MISSING');
1072 fnd_message.set_token ('MISSING', 'FORMULA_ID');
1073 fnd_msg_pub.add;
1074 RAISE copy_recipe_err;
1075 ELSE
1076 IF p_recipe_header_rec.formula_id IS NULL THEN
1077 /* Check if formula_no and vers is passed */
1078 /* If yes then get the formula_id from the db */
1079 /* If not passed, then get the old formula id */
1080 IF (p_recipe_header_rec.formula_no IS NOT NULL) AND
1081 (p_recipe_header_rec.formula_vers IS NOT NULL) THEN
1082 OPEN get_formula_id(p_recipe_header_rec.formula_no,
1083 p_recipe_header_rec.formula_vers) ;
1084 FETCH get_formula_id INTO l_recipe_header_rec.formula_id;
1085 IF get_formula_id%NOTFOUND THEN
1086 l_recipe_header_rec.formula_id
1087 := old_recipe_rec.formula_id;
1088 END IF;
1089 CLOSE get_formula_id;
1090 ELSE -- Formula_no and/or version must be Null
1091 l_recipe_header_rec.formula_id
1092 := old_recipe_rec.formula_id;
1093 END IF; -- Condition ends when formula no, vers is not null
1094 ELSIF p_recipe_header_rec.formula_id
1095 <> old_recipe_rec.formula_id THEN
1096 l_recipe_header_rec.formula_id
1097 := p_recipe_header_rec.formula_id;
1098 l_changed_flag := TRUE;
1099 ELSE -- both the old and new formula id are the same
1100 l_recipe_header_rec.formula_id
1101 := old_recipe_rec.formula_id;
1102 END IF;
1103 END IF;
1104
1105
1106 IF (l_debug = 'Y') THEN
1107 gmd_debug.put_line('-- 8. Check for formula_no and version ');
1108 END IF;
1109 /* What if the calling function passes same formula_id
1110 but different formula_no and/or formula_vers?
1111 Then we need to the formula id that corresponds to this
1112 changed formula_no and/or formula_vers
1113 */
1114 IF (p_recipe_header_rec.formula_no = FND_API.G_MISS_CHAR) OR
1115 (p_recipe_header_rec.formula_vers = FND_API.G_MISS_NUM) THEN
1116 fnd_message.set_name ('GMI', 'GMI_MISSING');
1117 fnd_message.set_token ('MISSING', 'FORMULA_NO OR VERS');
1118 fnd_msg_pub.add;
1119 RAISE copy_recipe_err;
1120 ELSE
1121 IF (l_recipe_header_rec.formula_id
1122 = p_recipe_header_rec.formula_id) AND
1123 (p_recipe_header_rec.formula_no IS NOT NULL) AND
1124 (p_recipe_header_rec.formula_vers IS NOT NULL) THEN
1125
1126 -- Get the old formula_no and formula_version
1127 OPEN get_formula_no_and_vers(old_recipe_rec.formula_id);
1128 FETCH get_formula_no_and_vers INTO l_old_formula_no,
1129 l_old_formula_vers;
1130 IF get_formula_no_and_vers%NOTFOUND THEN
1131 -- Hmmm ... major issue
1132 Null;
1133 END IF;
1134 CLOSE get_formula_no_and_vers;
1135
1136 -- Compare this formula_no and version with old formula_no and vers
1137 IF ((p_recipe_header_rec.formula_no <> l_old_formula_no) OR
1138 (p_recipe_header_rec.formula_vers <> l_old_formula_vers)) THEN
1139 OPEN get_formula_id(p_recipe_header_rec.formula_no,
1140 p_recipe_header_rec.formula_vers) ;
1141 FETCH get_formula_id INTO l_recipe_header_rec.formula_id;
1142 IF get_formula_id%NOTFOUND THEN
1143 -- Is this possible? check.
1144 l_recipe_header_rec.formula_id
1145 := p_recipe_header_rec.formula_id;
1146 ELSE
1147 l_changed_flag := TRUE;
1148 END IF;
1149 CLOSE get_formula_id;
1150 END IF; -- Condition ends when formula no or vers is different
1151 -- from the old formula_no and formual_vers
1152 END IF; -- Condition ends when formula_id were same and ....
1153 END IF; -- Condition for G_Miss_....
1154
1155
1156 IF (l_debug = 'Y') THEN
1157 gmd_debug.put_line('-- 9. Check for routing_id ');
1158 END IF;
1159
1160 IF p_recipe_header_rec.routing_id = FND_API.G_MISS_NUM THEN
1161 l_recipe_header_rec.routing_id := NULL;
1162 ELSE
1163 IF p_recipe_header_rec.routing_id IS NULL THEN
1164 /* Check if Routing no and version is passed */
1165 /* then get the routing_id from the db */
1166 /* If not passed, then get the old routing id */
1167 IF (p_recipe_header_rec.routing_no IS NOT NULL) AND
1168 (p_recipe_header_rec.routing_vers IS NOT NULL) THEN
1169 OPEN get_routing_id(p_recipe_header_rec.routing_no,
1170 p_recipe_header_rec.routing_vers) ;
1171 FETCH get_routing_id INTO l_recipe_header_rec.routing_id;
1172 IF get_routing_id%NOTFOUND THEN
1173 l_recipe_header_rec.routing_id
1174 := old_recipe_rec.routing_id;
1175 END IF;
1176 CLOSE get_routing_id;
1177 ELSE
1178 l_recipe_header_rec.routing_id
1179 := old_recipe_rec.routing_id;
1180 END IF; -- Condition ends when routing no, vers is not null
1181 ELSIF p_recipe_header_rec.routing_id
1182 <> old_recipe_rec.routing_id THEN
1183 l_recipe_header_rec.routing_id
1184 := p_recipe_header_rec.routing_id;
1185 l_changed_flag := TRUE;
1186 ELSE -- both the old and new formula id are the same
1187 l_recipe_header_rec.routing_id
1188 := old_recipe_rec.routing_id;
1189 END IF; -- End condition when routing_id is null
1190 END IF; -- End condition when routing_id is G_miss...
1191
1192 IF (l_debug = 'Y') THEN
1193 gmd_debug.put_line('-- 10. Check for routing_no and version ');
1194 END IF;
1195 /* We need to the set the routing_id that corresponds to the
1196 id derived from routing_no and/or routing_vers passed in.
1197 We need to do this only if the old and new routing id are
1198 different.
1199 */
1200 IF (p_recipe_header_rec.routing_no = FND_API.G_MISS_CHAR) OR
1201 (p_recipe_header_rec.routing_vers = FND_API.G_MISS_NUM) THEN
1202 -- We set the Routing id to a Null value
1203 l_recipe_header_rec.routing_id := NULL;
1204 ELSE
1205 IF (l_recipe_header_rec.routing_id
1206 = p_recipe_header_rec.routing_id) AND
1207 (p_recipe_header_rec.routing_no IS NOT NULL) AND
1208 (p_recipe_header_rec.routing_vers IS NOT NULL) THEN
1209
1210 /* Get the old routing_no and version */
1211 OPEN get_routing_no_and_vers(old_recipe_rec.routing_id);
1212 FETCH get_routing_no_and_vers INTO l_old_routing_no,
1213 l_old_routing_vers;
1214 IF get_routing_no_and_vers%NOTFOUND THEN
1215 -- Maybe we need to flag an error msg
1216 Null;
1217 END IF;
1218 CLOSE get_routing_no_and_vers;
1219
1220 IF ((p_recipe_header_rec.routing_no
1221 <> l_old_routing_no) OR
1222 (p_recipe_header_rec.routing_vers
1223 <> l_old_routing_vers)) THEN
1224 OPEN get_routing_id(p_recipe_header_rec.routing_no,
1225 p_recipe_header_rec.routing_vers) ;
1226 FETCH get_routing_id INTO l_recipe_header_rec.routing_id;
1227 IF get_routing_id%NOTFOUND THEN
1228 -- Is this possible? check.
1229 l_recipe_header_rec.routing_id := NULL;
1230 END IF;
1231 CLOSE get_routing_id;
1232 l_changed_flag := TRUE;
1233 END IF; -- Condition ends when routing no or vers is different
1234 -- from the old routing_no and vers
1235 END IF; -- Condition ends when routing_id were same and ....
1236 END IF;
1237
1238 /* Recipe status should always be set to New */
1239 IF p_recipe_header_rec.recipe_status = FND_API.G_MISS_CHAR THEN
1240 fnd_message.set_name ('GMI', 'GMI_MISSING');
1241 fnd_message.set_token ('MISSING', 'RECIPE_STATUS');
1242 fnd_msg_pub.add;
1243 RAISE copy_recipe_err;
1244 ELSE
1245 l_recipe_header_rec.recipe_status := '100';
1246 END IF;
1247
1248 IF p_recipe_header_rec.planned_process_loss = FND_API.G_MISS_NUM THEN
1249 l_recipe_header_rec.planned_process_loss := NULL;
1250 ELSE
1251 IF p_recipe_header_rec.planned_process_loss IS NULL THEN
1252 l_recipe_header_rec.planned_process_loss
1253 := old_recipe_rec.planned_process_loss;
1254 ELSIF p_recipe_header_rec.planned_process_loss
1255 <> old_recipe_rec.planned_process_loss THEN
1256 l_recipe_header_rec.planned_process_loss
1257 := p_recipe_header_rec.planned_process_loss;
1258 ELSE
1259 l_recipe_header_rec.planned_process_loss
1260 := old_recipe_rec.planned_process_loss;
1261 END IF;
1262 END IF;
1263 /* B6811759 */
1264 IF p_recipe_header_rec.fixed_process_loss = FND_API.G_MISS_NUM THEN
1265 l_recipe_header_rec.fixed_process_loss := NULL;
1266 ELSE
1267 IF p_recipe_header_rec.fixed_process_loss IS NULL THEN
1268 l_recipe_header_rec.fixed_process_loss
1269 := old_recipe_rec.fixed_process_loss;
1270 ELSIF p_recipe_header_rec.fixed_process_loss
1271 <> old_recipe_rec.fixed_process_loss THEN
1272 l_recipe_header_rec.fixed_process_loss
1273 := p_recipe_header_rec.fixed_process_loss;
1274 ELSE
1275 l_recipe_header_rec.fixed_process_loss
1276 := old_recipe_rec.fixed_process_loss;
1277 END IF;
1278 END IF;
1279 IF p_recipe_header_rec.fixed_process_loss_uom = FND_API.G_MISS_CHAR THEN
1280 l_recipe_header_rec.fixed_process_loss_uom := NULL;
1281 ELSE
1282 IF p_recipe_header_rec.fixed_process_loss_uom IS NULL THEN
1283 l_recipe_header_rec.fixed_process_loss_uom
1284 := old_recipe_rec.fixed_process_loss_uom;
1285 ELSIF p_recipe_header_rec.fixed_process_loss_uom
1286 <> old_recipe_rec.fixed_process_loss_uom THEN
1287 l_recipe_header_rec.fixed_process_loss_uom
1288 := p_recipe_header_rec.fixed_process_loss_uom;
1289 ELSE
1290 l_recipe_header_rec.fixed_process_loss_uom
1291 := old_recipe_rec.fixed_process_loss_uom;
1292 END IF;
1293 END IF;
1294
1295 IF p_recipe_header_rec.contiguous_ind IS NULL THEN
1296 l_recipe_header_rec.contiguous_ind
1297 := old_recipe_rec.contiguous_ind;
1298 ELSIF p_recipe_header_rec.contiguous_ind
1299 <> old_recipe_rec.contiguous_ind THEN
1300 l_recipe_header_rec.contiguous_ind
1301 := p_recipe_header_rec.contiguous_ind;
1302 ELSE
1303 l_recipe_header_rec.contiguous_ind
1304 := old_recipe_rec.contiguous_ind;
1305 END IF;
1306
1307 IF p_recipe_header_rec.enhanced_pi_ind IS NULL THEN
1308 l_recipe_header_rec.enhanced_pi_ind
1309 := old_recipe_rec.enhanced_pi_ind;
1310 ELSIF p_recipe_header_rec.enhanced_pi_ind
1311 <> old_recipe_rec.enhanced_pi_ind THEN
1312 l_recipe_header_rec.enhanced_pi_ind
1313 := p_recipe_header_rec.enhanced_pi_ind;
1314 ELSE
1315 l_recipe_header_rec.enhanced_pi_ind
1316 := old_recipe_rec.enhanced_pi_ind;
1317 END IF;
1318
1319 IF p_recipe_header_rec.recipe_type IS NULL THEN
1320 l_recipe_header_rec.recipe_type
1321 := old_recipe_rec.recipe_type;
1322 ELSIF p_recipe_header_rec.recipe_type
1323 <> old_recipe_rec.recipe_type THEN
1324 l_recipe_header_rec.recipe_type
1325 := p_recipe_header_rec.recipe_type;
1326 ELSE
1327 l_recipe_header_rec.recipe_type
1328 := old_recipe_rec.recipe_type;
1329 END IF;
1330
1331
1332 IF p_recipe_header_rec.text_code = FND_API.G_MISS_NUM THEN
1333 l_recipe_header_rec.text_code := NULL;
1334 ELSE
1335 IF p_recipe_header_rec.text_code IS NULL THEN
1336 l_recipe_header_rec.text_code
1337 := old_recipe_rec.text_code;
1338 ELSIF p_recipe_header_rec.text_code
1339 <> old_recipe_rec.text_code THEN
1340 l_recipe_header_rec.text_code
1341 := p_recipe_header_rec.text_code;
1342 l_changed_flag := TRUE;
1343 ELSE
1344 l_recipe_header_rec.text_code
1345 := old_recipe_rec.text_code;
1346 END IF;
1347 END IF;
1348
1349 /* Delete Mark should always be set to 0 */
1350 IF p_recipe_header_rec.delete_mark = FND_API.G_MISS_NUM THEN
1351 fnd_message.set_name ('GMI', 'GMI_MISSING');
1352 fnd_message.set_token ('MISSING', 'DELETE_MARK');
1353 fnd_msg_pub.add;
1354 RAISE copy_recipe_err;
1355 ELSE
1356 l_recipe_header_rec.delete_mark := 0;
1357 END IF;
1358
1359 IF p_recipe_header_rec.creation_date = FND_API.G_MISS_DATE THEN
1360 fnd_message.set_name ('GMI', 'GMI_MISSING');
1361 fnd_message.set_token ('MISSING', 'CREATION_DATE');
1362 fnd_msg_pub.add;
1363 RAISE copy_recipe_err;
1364 ELSE
1365 IF p_recipe_header_rec.creation_date IS NULL THEN
1366 l_recipe_header_rec.creation_date
1367 := old_recipe_rec.creation_date;
1368 ELSIF p_recipe_header_rec.creation_date
1369 <> old_recipe_rec.creation_date THEN
1370 l_recipe_header_rec.creation_date
1371 := p_recipe_header_rec.creation_date;
1372 l_changed_flag := TRUE;
1373 ELSE
1374 l_recipe_header_rec.creation_date
1375 := old_recipe_rec.creation_date;
1376 END IF;
1377 END IF;
1378
1379 IF p_recipe_header_rec.created_by = FND_API.G_MISS_NUM THEN
1380 fnd_message.set_name ('GMI', 'GMI_MISSING');
1381 fnd_message.set_token ('MISSING', 'CREATED_BY');
1382 fnd_msg_pub.add;
1383 RAISE copy_recipe_err;
1384 ELSE
1385 IF p_recipe_header_rec.created_by IS NULL THEN
1386 l_recipe_header_rec.created_by
1387 := old_recipe_rec.created_by;
1388 ELSIF p_recipe_header_rec.created_by
1389 <> old_recipe_rec.created_by THEN
1390 l_recipe_header_rec.created_by
1391 := p_recipe_header_rec.created_by;
1392 l_changed_flag := TRUE;
1393 ELSE
1394 l_recipe_header_rec.created_by
1395 := old_recipe_rec.created_by;
1396 END IF;
1397 END IF;
1398
1399 IF p_recipe_header_rec.last_updated_by = FND_API.G_MISS_NUM THEN
1400 fnd_message.set_name ('GMI', 'GMI_MISSING');
1401 fnd_message.set_token ('MISSING', 'LAST_UPDATED_BY');
1402 fnd_msg_pub.add;
1403 RAISE copy_recipe_err;
1404 ELSE
1405 IF p_recipe_header_rec.last_updated_by IS NULL THEN
1406 l_recipe_header_rec.last_updated_by
1407 := old_recipe_rec.last_updated_by;
1408 ELSIF p_recipe_header_rec.last_updated_by
1409 <> old_recipe_rec.last_updated_by THEN
1410 l_recipe_header_rec.last_updated_by
1411 := p_recipe_header_rec.last_updated_by;
1412 l_changed_flag := TRUE;
1413 ELSE
1414 l_recipe_header_rec.last_updated_by
1415 := old_recipe_rec.last_updated_by;
1416 END IF;
1417 END IF;
1418
1419 IF p_recipe_header_rec.last_update_date = FND_API.G_MISS_DATE THEN
1420 fnd_message.set_name ('GMI', 'GMI_MISSING');
1421 fnd_message.set_token ('MISSING', 'LAST_UPDATE_DATE');
1422 fnd_msg_pub.add;
1423 RAISE copy_recipe_err;
1424 ELSE
1425 IF p_recipe_header_rec.last_update_date IS NULL THEN
1426 l_recipe_header_rec.last_update_date
1427 := old_recipe_rec.last_update_date;
1428 ELSIF p_recipe_header_rec.last_update_date
1429 <> old_recipe_rec.last_update_date THEN
1430 l_recipe_header_rec.last_update_date
1431 := p_recipe_header_rec.last_update_date;
1432 l_changed_flag := TRUE;
1433 ELSE
1434 l_recipe_header_rec.last_update_date
1435 := old_recipe_rec.last_update_date;
1436 END IF;
1437 END IF;
1438
1439 IF p_recipe_header_rec.last_update_login = FND_API.G_MISS_NUM THEN
1440 l_recipe_header_rec.last_update_login := NULL;
1441 ELSE
1442 IF p_recipe_header_rec.last_update_login IS NULL THEN
1443 l_recipe_header_rec.last_update_login
1444 := old_recipe_rec.last_update_login;
1445 ELSIF p_recipe_header_rec.last_update_login
1446 <> old_recipe_rec.last_update_login THEN
1447 l_recipe_header_rec.last_update_login
1448 := p_recipe_header_rec.last_update_login;
1449 l_changed_flag := TRUE;
1450 ELSE
1451 l_recipe_header_rec.last_update_login
1452 := old_recipe_rec.last_update_login;
1453 END IF;
1454 END IF;
1455
1456 IF p_recipe_header_rec.owner_id = FND_API.G_MISS_NUM THEN
1457 fnd_message.set_name ('GMI', 'GMI_MISSING');
1458 fnd_message.set_token ('MISSING', 'OWNER_ID');
1459 fnd_msg_pub.add;
1460 RAISE copy_recipe_err;
1461 ELSE
1462 IF p_recipe_header_rec.owner_id IS NULL THEN
1463 l_recipe_header_rec.owner_id
1464 := old_recipe_rec.owner_id;
1465 ELSIF p_recipe_header_rec.owner_id
1466 <> old_recipe_rec.owner_id THEN
1467 l_recipe_header_rec.owner_id
1468 := p_recipe_header_rec.owner_id;
1469 l_changed_flag := TRUE;
1470 ELSE
1471 l_recipe_header_rec.owner_id
1472 := old_recipe_rec.owner_id;
1473 END IF;
1474 END IF;
1475
1476 IF p_recipe_header_rec.owner_lab_type = FND_API.G_MISS_CHAR THEN
1477 l_recipe_header_rec.owner_lab_type := NULL;
1478 ELSE
1479 IF p_recipe_header_rec.owner_lab_type IS NULL THEN
1480 l_recipe_header_rec.owner_lab_type
1481 := old_recipe_rec.owner_lab_type;
1482 ELSIF p_recipe_header_rec.owner_lab_type
1483 <> old_recipe_rec.owner_lab_type THEN
1484 l_recipe_header_rec.owner_lab_type
1485 := p_recipe_header_rec.owner_lab_type;
1486 l_changed_flag := TRUE;
1487 ELSE
1488 l_recipe_header_rec.owner_lab_type
1489 := old_recipe_rec.owner_lab_type;
1490 END IF;
1491 END IF;
1492
1493 IF p_recipe_header_rec.calculate_step_quantity = FND_API.G_MISS_NUM THEN
1494 l_recipe_header_rec.calculate_step_quantity := NULL;
1495 ELSE
1496 IF p_recipe_header_rec.calculate_step_quantity IS NULL THEN
1497 l_recipe_header_rec.calculate_step_quantity
1498 := old_recipe_rec.calculate_step_quantity;
1499 ELSIF p_recipe_header_rec.calculate_step_quantity
1500 <> old_recipe_rec.calculate_step_quantity THEN
1501 l_recipe_header_rec.calculate_step_quantity
1502 := p_recipe_header_rec.calculate_step_quantity;
1503 l_changed_flag := TRUE;
1504 ELSE
1505 l_recipe_header_rec.calculate_step_quantity
1506 := old_recipe_rec.calculate_step_quantity;
1507 END IF;
1508 END IF;
1509
1510 -- Flex field attributes
1511 IF p_recipe_hdr_flex_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1512 l_recipe_hdr_flex_rec.attribute1 := NULL;
1513 ELSE
1514 IF p_recipe_hdr_flex_rec.attribute1 IS NULL THEN
1515 l_recipe_hdr_flex_rec.attribute1
1516 := old_recipe_rec.attribute1;
1517 ELSIF p_recipe_hdr_flex_rec.attribute1
1518 <> old_recipe_rec.attribute1 THEN
1519 l_recipe_hdr_flex_rec.attribute1
1520 := p_recipe_hdr_flex_rec.attribute1;
1521 l_changed_flag := TRUE;
1522 ELSE
1523 l_recipe_hdr_flex_rec.attribute1
1524 := old_recipe_rec.attribute1;
1525 END IF;
1526 END IF;
1527
1528 IF p_recipe_hdr_flex_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1529 l_recipe_hdr_flex_rec.attribute2 := NULL;
1530 ELSE
1531 IF p_recipe_hdr_flex_rec.attribute2 IS NULL THEN
1532 l_recipe_hdr_flex_rec.attribute2
1533 := old_recipe_rec.attribute2;
1534 ELSIF p_recipe_hdr_flex_rec.attribute2
1535 <> old_recipe_rec.attribute2 THEN
1536 l_recipe_hdr_flex_rec.attribute2
1537 := p_recipe_hdr_flex_rec.attribute2;
1538 l_changed_flag := TRUE;
1539 ELSE
1540 l_recipe_hdr_flex_rec.attribute2
1541 := old_recipe_rec.attribute2;
1542 END IF;
1543 END IF;
1544
1545 IF p_recipe_hdr_flex_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1546 l_recipe_hdr_flex_rec.attribute3 := NULL;
1547 ELSE
1548 IF p_recipe_hdr_flex_rec.attribute3 IS NULL THEN
1549 l_recipe_hdr_flex_rec.attribute3
1550 := old_recipe_rec.attribute3;
1551 ELSIF p_recipe_hdr_flex_rec.attribute3
1552 <> old_recipe_rec.attribute3 THEN
1553 l_recipe_hdr_flex_rec.attribute3
1554 := p_recipe_hdr_flex_rec.attribute3;
1555 l_changed_flag := TRUE;
1556 ELSE
1557 l_recipe_hdr_flex_rec.attribute3
1558 := old_recipe_rec.attribute3;
1559 END IF;
1560 END IF;
1561
1562 IF p_recipe_hdr_flex_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1563 l_recipe_hdr_flex_rec.attribute4 := NULL;
1564 ELSE
1565 IF p_recipe_hdr_flex_rec.attribute4 IS NULL THEN
1566 l_recipe_hdr_flex_rec.attribute4
1567 := old_recipe_rec.attribute4;
1568 ELSIF p_recipe_hdr_flex_rec.attribute4
1569 <> old_recipe_rec.attribute4 THEN
1570 l_recipe_hdr_flex_rec.attribute4
1571 := p_recipe_hdr_flex_rec.attribute4;
1572 l_changed_flag := TRUE;
1573 ELSE
1574 l_recipe_hdr_flex_rec.attribute4
1575 := old_recipe_rec.attribute4;
1576 END IF;
1577 END IF;
1578
1579 IF p_recipe_hdr_flex_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1580 l_recipe_hdr_flex_rec.attribute5 := NULL;
1581 ELSE
1582 IF p_recipe_hdr_flex_rec.attribute5 IS NULL THEN
1583 l_recipe_hdr_flex_rec.attribute5
1584 := old_recipe_rec.attribute5;
1585 ELSIF p_recipe_hdr_flex_rec.attribute5
1586 <> old_recipe_rec.attribute5 THEN
1587 l_recipe_hdr_flex_rec.attribute5
1588 := p_recipe_hdr_flex_rec.attribute5;
1589 l_changed_flag := TRUE;
1590 ELSE
1591 l_recipe_hdr_flex_rec.attribute5
1592 := old_recipe_rec.attribute5;
1593 END IF;
1594 END IF;
1595
1596 IF p_recipe_hdr_flex_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1597 l_recipe_hdr_flex_rec.attribute6 := NULL;
1598 ELSE
1599 IF p_recipe_hdr_flex_rec.attribute6 IS NULL THEN
1600 l_recipe_hdr_flex_rec.attribute6
1601 := old_recipe_rec.attribute6;
1602 ELSIF p_recipe_hdr_flex_rec.attribute6
1603 <> old_recipe_rec.attribute6 THEN
1604 l_recipe_hdr_flex_rec.attribute6
1605 := p_recipe_hdr_flex_rec.attribute6;
1606 l_changed_flag := TRUE;
1607 ELSE
1608 l_recipe_hdr_flex_rec.attribute6
1609 := old_recipe_rec.attribute6;
1610 END IF;
1611 END IF;
1612
1613 IF p_recipe_hdr_flex_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1614 l_recipe_hdr_flex_rec.attribute7 := NULL;
1615 ELSE
1616 IF p_recipe_hdr_flex_rec.attribute7 IS NULL THEN
1617 l_recipe_hdr_flex_rec.attribute7
1618 := old_recipe_rec.attribute7;
1619 ELSIF p_recipe_hdr_flex_rec.attribute7
1620 <> old_recipe_rec.attribute7 THEN
1621 l_recipe_hdr_flex_rec.attribute7
1622 := p_recipe_hdr_flex_rec.attribute7;
1623 l_changed_flag := TRUE;
1624 ELSE
1625 l_recipe_hdr_flex_rec.attribute7
1626 := old_recipe_rec.attribute7;
1627 END IF;
1628 END IF;
1629
1630 IF p_recipe_hdr_flex_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1631 l_recipe_hdr_flex_rec.attribute8 := NULL;
1632 ELSE
1633 IF p_recipe_hdr_flex_rec.attribute8 IS NULL THEN
1634 l_recipe_hdr_flex_rec.attribute8
1635 := old_recipe_rec.attribute8;
1636 ELSIF p_recipe_hdr_flex_rec.attribute8
1637 <> old_recipe_rec.attribute8 THEN
1638 l_recipe_hdr_flex_rec.attribute8
1639 := p_recipe_hdr_flex_rec.attribute8;
1640 l_changed_flag := TRUE;
1641 ELSE
1642 l_recipe_hdr_flex_rec.attribute8
1643 := old_recipe_rec.attribute8;
1644 END IF;
1645 END IF;
1646
1647 IF p_recipe_hdr_flex_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1648 l_recipe_hdr_flex_rec.attribute9 := NULL;
1649 ELSE
1650 IF p_recipe_hdr_flex_rec.attribute9 IS NULL THEN
1651 l_recipe_hdr_flex_rec.attribute9
1652 := old_recipe_rec.attribute9;
1653 ELSIF p_recipe_hdr_flex_rec.attribute9
1654 <> old_recipe_rec.attribute9 THEN
1655 l_recipe_hdr_flex_rec.attribute9
1656 := p_recipe_hdr_flex_rec.attribute9;
1657 l_changed_flag := TRUE;
1658 ELSE
1659 l_recipe_hdr_flex_rec.attribute9
1660 := old_recipe_rec.attribute9;
1661 END IF;
1662 END IF;
1663
1664 IF p_recipe_hdr_flex_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1665 l_recipe_hdr_flex_rec.attribute10 := NULL;
1666 ELSE
1667 IF p_recipe_hdr_flex_rec.attribute10 IS NULL THEN
1668 l_recipe_hdr_flex_rec.attribute10
1669 := old_recipe_rec.attribute10;
1670 ELSIF p_recipe_hdr_flex_rec.attribute10
1671 <> old_recipe_rec.attribute10 THEN
1672 l_recipe_hdr_flex_rec.attribute10
1673 := p_recipe_hdr_flex_rec.attribute10;
1674 l_changed_flag := TRUE;
1675 ELSE
1676 l_recipe_hdr_flex_rec.attribute10
1677 := old_recipe_rec.attribute10;
1678 END IF;
1679 END IF;
1680
1681 IF p_recipe_hdr_flex_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1682 l_recipe_hdr_flex_rec.attribute11 := NULL;
1683 ELSE
1684 IF p_recipe_hdr_flex_rec.attribute11 IS NULL THEN
1685 l_recipe_hdr_flex_rec.attribute11
1686 := old_recipe_rec.attribute11;
1687 ELSIF p_recipe_hdr_flex_rec.attribute11
1688 <> old_recipe_rec.attribute11 THEN
1689 l_recipe_hdr_flex_rec.attribute11
1690 := p_recipe_hdr_flex_rec.attribute11;
1691 l_changed_flag := TRUE;
1692 ELSE
1693 l_recipe_hdr_flex_rec.attribute11
1694 := old_recipe_rec.attribute11;
1695 END IF;
1696 END IF;
1697
1698 IF p_recipe_hdr_flex_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1699 l_recipe_hdr_flex_rec.attribute12 := NULL;
1700 ELSE
1701 IF p_recipe_hdr_flex_rec.attribute12 IS NULL THEN
1702 l_recipe_hdr_flex_rec.attribute12
1703 := old_recipe_rec.attribute12;
1704 ELSIF p_recipe_hdr_flex_rec.attribute12
1705 <> old_recipe_rec.attribute12 THEN
1706 l_recipe_hdr_flex_rec.attribute12
1707 := p_recipe_hdr_flex_rec.attribute12;
1708 l_changed_flag := TRUE;
1709 ELSE
1710 l_recipe_hdr_flex_rec.attribute12
1711 := old_recipe_rec.attribute2;
1712 END IF;
1713 END IF;
1714
1715 IF p_recipe_hdr_flex_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1716 l_recipe_hdr_flex_rec.attribute13 := NULL;
1717 ELSE
1718 IF p_recipe_hdr_flex_rec.attribute13 IS NULL THEN
1719 l_recipe_hdr_flex_rec.attribute13
1720 := old_recipe_rec.attribute13;
1721 ELSIF p_recipe_hdr_flex_rec.attribute13
1722 <> old_recipe_rec.attribute13 THEN
1723 l_recipe_hdr_flex_rec.attribute13
1724 := p_recipe_hdr_flex_rec.attribute13;
1725 l_changed_flag := TRUE;
1726 ELSE
1727 l_recipe_hdr_flex_rec.attribute13
1728 := old_recipe_rec.attribute13;
1729 END IF;
1730 END IF;
1731
1732 IF p_recipe_hdr_flex_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1733 l_recipe_hdr_flex_rec.attribute14 := NULL;
1734 ELSE
1735 IF p_recipe_hdr_flex_rec.attribute14 IS NULL THEN
1736 l_recipe_hdr_flex_rec.attribute14
1737 := old_recipe_rec.attribute14;
1738 ELSIF p_recipe_hdr_flex_rec.attribute14
1739 <> old_recipe_rec.attribute14 THEN
1740 l_recipe_hdr_flex_rec.attribute14
1741 := p_recipe_hdr_flex_rec.attribute14;
1742 l_changed_flag := TRUE;
1743 ELSE
1744 l_recipe_hdr_flex_rec.attribute14
1745 := old_recipe_rec.attribute14;
1746 END IF;
1747 END IF;
1748
1749 IF p_recipe_hdr_flex_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1750 l_recipe_hdr_flex_rec.attribute15 := NULL;
1751 ELSE
1752 IF p_recipe_hdr_flex_rec.attribute15 IS NULL THEN
1753 l_recipe_hdr_flex_rec.attribute15
1754 := old_recipe_rec.attribute15;
1755 ELSIF p_recipe_hdr_flex_rec.attribute15
1756 <> old_recipe_rec.attribute15 THEN
1757 l_recipe_hdr_flex_rec.attribute15
1758 := p_recipe_hdr_flex_rec.attribute15;
1759 l_changed_flag := TRUE;
1760 ELSE
1761 l_recipe_hdr_flex_rec.attribute15
1762 := old_recipe_rec.attribute15;
1763 END IF;
1764 END IF;
1765
1766 IF p_recipe_hdr_flex_rec.attribute16 = FND_API.G_MISS_CHAR THEN
1767 l_recipe_hdr_flex_rec.attribute16 := NULL;
1768 ELSE
1769 IF p_recipe_hdr_flex_rec.attribute16 IS NULL THEN
1770 l_recipe_hdr_flex_rec.attribute16
1771 := old_recipe_rec.attribute16;
1772 ELSIF p_recipe_hdr_flex_rec.attribute16
1773 <> old_recipe_rec.attribute16 THEN
1774 l_recipe_hdr_flex_rec.attribute16
1775 := p_recipe_hdr_flex_rec.attribute16;
1776 l_changed_flag := TRUE;
1777 ELSE
1778 l_recipe_hdr_flex_rec.attribute16
1779 := old_recipe_rec.attribute16;
1780 END IF;
1781 END IF;
1782
1783 IF p_recipe_hdr_flex_rec.attribute17 = FND_API.G_MISS_CHAR THEN
1784 l_recipe_hdr_flex_rec.attribute17 := NULL;
1785 ELSE
1786 IF p_recipe_hdr_flex_rec.attribute17 IS NULL THEN
1787 l_recipe_hdr_flex_rec.attribute17
1788 := old_recipe_rec.attribute7;
1789 ELSIF p_recipe_hdr_flex_rec.attribute17
1790 <> old_recipe_rec.attribute17 THEN
1791 l_recipe_hdr_flex_rec.attribute17
1792 := p_recipe_hdr_flex_rec.attribute17;
1793 l_changed_flag := TRUE;
1794 ELSE
1795 l_recipe_hdr_flex_rec.attribute17
1796 := old_recipe_rec.attribute17;
1797 END IF;
1798 END IF;
1799
1800 IF p_recipe_hdr_flex_rec.attribute18 = FND_API.G_MISS_CHAR THEN
1801 l_recipe_hdr_flex_rec.attribute18 := NULL;
1802 ELSE
1803 IF p_recipe_hdr_flex_rec.attribute18 IS NULL THEN
1804 l_recipe_hdr_flex_rec.attribute18
1805 := old_recipe_rec.attribute18;
1806 ELSIF p_recipe_hdr_flex_rec.attribute18
1807 <> old_recipe_rec.attribute18 THEN
1808 l_recipe_hdr_flex_rec.attribute18
1809 := p_recipe_hdr_flex_rec.attribute8;
1810 l_changed_flag := TRUE;
1811 ELSE
1812 l_recipe_hdr_flex_rec.attribute18
1813 := old_recipe_rec.attribute18;
1814 END IF;
1815 END IF;
1816
1817 IF p_recipe_hdr_flex_rec.attribute19 = FND_API.G_MISS_CHAR THEN
1818 l_recipe_hdr_flex_rec.attribute19 := NULL;
1819 ELSE
1820 IF p_recipe_hdr_flex_rec.attribute19 IS NULL THEN
1821 l_recipe_hdr_flex_rec.attribute19
1822 := old_recipe_rec.attribute19;
1823 ELSIF p_recipe_hdr_flex_rec.attribute19
1824 <> old_recipe_rec.attribute19 THEN
1825 l_recipe_hdr_flex_rec.attribute19
1826 := p_recipe_hdr_flex_rec.attribute19;
1827 l_changed_flag := TRUE;
1828 ELSE
1829 l_recipe_hdr_flex_rec.attribute19
1830 := old_recipe_rec.attribute19;
1831 END IF;
1832 END IF;
1833
1834 IF p_recipe_hdr_flex_rec.attribute20 = FND_API.G_MISS_CHAR THEN
1835 l_recipe_hdr_flex_rec.attribute20 := NULL;
1836 ELSE
1837 IF p_recipe_hdr_flex_rec.attribute20 IS NULL THEN
1838 l_recipe_hdr_flex_rec.attribute20
1839 := old_recipe_rec.attribute20;
1840 ELSIF p_recipe_hdr_flex_rec.attribute20
1841 <> old_recipe_rec.attribute20 THEN
1842 l_recipe_hdr_flex_rec.attribute20
1843 := p_recipe_hdr_flex_rec.attribute20;
1844 l_changed_flag := TRUE;
1845 ELSE
1846 l_recipe_hdr_flex_rec.attribute20
1847 := old_recipe_rec.attribute20;
1848 END IF;
1849 END IF;
1850
1851 IF p_recipe_hdr_flex_rec.attribute21 = FND_API.G_MISS_CHAR THEN
1852 l_recipe_hdr_flex_rec.attribute21 := NULL;
1853 ELSE
1854 IF p_recipe_hdr_flex_rec.attribute21 IS NULL THEN
1855 l_recipe_hdr_flex_rec.attribute21
1856 := old_recipe_rec.attribute21;
1857 ELSIF p_recipe_hdr_flex_rec.attribute21
1858 <> old_recipe_rec.attribute21 THEN
1859 l_recipe_hdr_flex_rec.attribute21
1860 := p_recipe_hdr_flex_rec.attribute21;
1861 l_changed_flag := TRUE;
1862 ELSE
1863 l_recipe_hdr_flex_rec.attribute21
1864 := old_recipe_rec.attribute21;
1865 END IF;
1866 END IF;
1867
1868 IF p_recipe_hdr_flex_rec.attribute22 = FND_API.G_MISS_CHAR THEN
1869 l_recipe_hdr_flex_rec.attribute22 := NULL;
1870 ELSE
1871 IF p_recipe_hdr_flex_rec.attribute22 IS NULL THEN
1872 l_recipe_hdr_flex_rec.attribute22
1873 := old_recipe_rec.attribute22;
1874 ELSIF p_recipe_hdr_flex_rec.attribute22
1875 <> old_recipe_rec.attribute22 THEN
1876 l_recipe_hdr_flex_rec.attribute22
1877 := p_recipe_hdr_flex_rec.attribute22;
1878 l_changed_flag := TRUE;
1879 ELSE
1880 l_recipe_hdr_flex_rec.attribute22
1881 := old_recipe_rec.attribute2;
1882 END IF;
1883 END IF;
1884
1885 IF p_recipe_hdr_flex_rec.attribute23 = FND_API.G_MISS_CHAR THEN
1886 l_recipe_hdr_flex_rec.attribute23 := NULL;
1887 ELSE
1888 IF p_recipe_hdr_flex_rec.attribute23 IS NULL THEN
1889 l_recipe_hdr_flex_rec.attribute23
1890 := old_recipe_rec.attribute23;
1891 ELSIF p_recipe_hdr_flex_rec.attribute23
1892 <> old_recipe_rec.attribute23 THEN
1893 l_recipe_hdr_flex_rec.attribute23
1894 := p_recipe_hdr_flex_rec.attribute23;
1895 l_changed_flag := TRUE;
1896 ELSE
1897 l_recipe_hdr_flex_rec.attribute23
1898 := old_recipe_rec.attribute23;
1899 END IF;
1900 END IF;
1901
1902 IF p_recipe_hdr_flex_rec.attribute24 = FND_API.G_MISS_CHAR THEN
1903 l_recipe_hdr_flex_rec.attribute24 := NULL;
1904 ELSE
1905 IF p_recipe_hdr_flex_rec.attribute24 IS NULL THEN
1906 l_recipe_hdr_flex_rec.attribute24
1907 := old_recipe_rec.attribute24;
1908 ELSIF p_recipe_hdr_flex_rec.attribute24
1909 <> old_recipe_rec.attribute24 THEN
1910 l_recipe_hdr_flex_rec.attribute24
1911 := p_recipe_hdr_flex_rec.attribute24;
1912 l_changed_flag := TRUE;
1913 ELSE
1914 l_recipe_hdr_flex_rec.attribute24
1915 := old_recipe_rec.attribute24;
1916 END IF;
1917 END IF;
1918
1919 IF p_recipe_hdr_flex_rec.attribute25 = FND_API.G_MISS_CHAR THEN
1920 l_recipe_hdr_flex_rec.attribute25 := NULL;
1921 ELSE
1922 IF p_recipe_hdr_flex_rec.attribute25 IS NULL THEN
1923 l_recipe_hdr_flex_rec.attribute25
1924 := old_recipe_rec.attribute25;
1925 ELSIF p_recipe_hdr_flex_rec.attribute25
1926 <> old_recipe_rec.attribute25 THEN
1927 l_recipe_hdr_flex_rec.attribute25
1928 := p_recipe_hdr_flex_rec.attribute25;
1929 l_changed_flag := TRUE;
1930 ELSE
1931 l_recipe_hdr_flex_rec.attribute25
1932 := old_recipe_rec.attribute25;
1933 END IF;
1934 END IF;
1935
1936 IF p_recipe_hdr_flex_rec.attribute26 = FND_API.G_MISS_CHAR THEN
1937 l_recipe_hdr_flex_rec.attribute26 := NULL;
1938 ELSE
1939 IF p_recipe_hdr_flex_rec.attribute26 IS NULL THEN
1940 l_recipe_hdr_flex_rec.attribute26
1941 := old_recipe_rec.attribute26;
1942 ELSIF p_recipe_hdr_flex_rec.attribute26
1943 <> old_recipe_rec.attribute26 THEN
1944 l_recipe_hdr_flex_rec.attribute26
1945 := p_recipe_hdr_flex_rec.attribute26;
1946 l_changed_flag := TRUE;
1947 ELSE
1948 l_recipe_hdr_flex_rec.attribute26
1949 := old_recipe_rec.attribute26;
1950 END IF;
1951 END IF;
1952
1953 IF p_recipe_hdr_flex_rec.attribute27 = FND_API.G_MISS_CHAR THEN
1954 l_recipe_hdr_flex_rec.attribute27 := NULL;
1955 ELSE
1956 IF p_recipe_hdr_flex_rec.attribute27 IS NULL THEN
1957 l_recipe_hdr_flex_rec.attribute27
1958 := old_recipe_rec.attribute27;
1959 ELSIF p_recipe_hdr_flex_rec.attribute27
1960 <> old_recipe_rec.attribute27 THEN
1961 l_recipe_hdr_flex_rec.attribute27
1962 := p_recipe_hdr_flex_rec.attribute27;
1963 l_changed_flag := TRUE;
1964 ELSE
1965 l_recipe_hdr_flex_rec.attribute27
1966 := old_recipe_rec.attribute27;
1967 END IF;
1968 END IF;
1969
1970 IF p_recipe_hdr_flex_rec.attribute28 = FND_API.G_MISS_CHAR THEN
1971 l_recipe_hdr_flex_rec.attribute28 := NULL;
1972 ELSE
1973 IF p_recipe_hdr_flex_rec.attribute28 IS NULL THEN
1974 l_recipe_hdr_flex_rec.attribute28
1975 := old_recipe_rec.attribute28;
1976 ELSIF p_recipe_hdr_flex_rec.attribute28
1977 <> old_recipe_rec.attribute28 THEN
1978 l_recipe_hdr_flex_rec.attribute28
1979 := p_recipe_hdr_flex_rec.attribute28;
1980 l_changed_flag := TRUE;
1981 ELSE
1982 l_recipe_hdr_flex_rec.attribute28
1983 := old_recipe_rec.attribute28;
1984 END IF;
1985 END IF;
1986
1987 IF p_recipe_hdr_flex_rec.attribute29 = FND_API.G_MISS_CHAR THEN
1988 l_recipe_hdr_flex_rec.attribute29 := NULL;
1989 ELSE
1990 IF p_recipe_hdr_flex_rec.attribute29 IS NULL THEN
1991 l_recipe_hdr_flex_rec.attribute29
1992 := old_recipe_rec.attribute29;
1993 ELSIF p_recipe_hdr_flex_rec.attribute29
1994 <> old_recipe_rec.attribute29 THEN
1995 l_recipe_hdr_flex_rec.attribute29
1996 := p_recipe_hdr_flex_rec.attribute29;
1997 l_changed_flag := TRUE;
1998 ELSE
1999 l_recipe_hdr_flex_rec.attribute29
2000 := old_recipe_rec.attribute29;
2001 END IF;
2002 END IF;
2003
2004 IF p_recipe_hdr_flex_rec.attribute30 = FND_API.G_MISS_CHAR THEN
2005 l_recipe_hdr_flex_rec.attribute30 := NULL;
2006 ELSE
2007 IF p_recipe_hdr_flex_rec.attribute30 IS NULL THEN
2008 l_recipe_hdr_flex_rec.attribute30
2009 := old_recipe_rec.attribute30;
2010 ELSIF p_recipe_hdr_flex_rec.attribute30
2011 <> old_recipe_rec.attribute30 THEN
2012 l_recipe_hdr_flex_rec.attribute30
2013 := p_recipe_hdr_flex_rec.attribute30;
2014 l_changed_flag := TRUE;
2015 ELSE
2016 l_recipe_hdr_flex_rec.attribute30
2017 := old_recipe_rec.attribute30;
2018 END IF;
2019 END IF;
2020
2021 IF p_recipe_hdr_flex_rec.attribute_category = FND_API.G_MISS_CHAR THEN
2022 l_recipe_hdr_flex_rec.attribute_category := NULL;
2023 ELSE
2024 IF p_recipe_hdr_flex_rec.attribute_category IS NULL THEN
2025 l_recipe_hdr_flex_rec.attribute_category
2026 := old_recipe_rec.attribute_category;
2027 ELSIF p_recipe_hdr_flex_rec.attribute_category
2028 <> old_recipe_rec.attribute_category THEN
2029 l_recipe_hdr_flex_rec.attribute_category
2030 := p_recipe_hdr_flex_rec.attribute_category;
2031 l_changed_flag := TRUE;
2032 ELSE
2033 l_recipe_hdr_flex_rec.attribute_category
2034 := old_recipe_rec.attribute_category;
2035 END IF;
2036 END IF;
2037
2038
2039 -- Creating a copy of the original Recipe
2040 IF l_changed_flag THEN
2041 GMD_RECIPE_HEADER_PVT.create_recipe_header
2042 (p_recipe_header_rec => l_recipe_header_rec
2043 ,p_recipe_hdr_flex_rec => p_recipe_hdr_flex_rec
2044 ,x_return_status => x_return_status);
2045 IF x_return_status <> FND_API.g_ret_sts_success THEN
2046 RAISE copy_recipe_err;
2047 END IF; -- End condition when create API return state is ...
2048 ELSE -- when l_changed_flag is False
2049 IF (l_debug = 'Y') THEN
2050 gmd_debug.put_line('Atleast one Recipe field should change ');
2051 END IF;
2052 fnd_message.set_name ('GMD', 'GMD_CANNOT_COPY');
2053 fnd_message.set_token ('ENTITY', 'recipe');
2054 fnd_msg_pub.add;
2055 RAISE copy_recipe_err;
2056 END IF; -- End condition when l_changed flag is TRUE
2057 END LOOP; -- End Loop condition for each recipe record passed IN
2058
2059 IF (l_rowcount = 0) THEN
2060 fnd_message.set_name ('GMD','GMD_RECIPE_DOES_NOT_EXIST');
2061 fnd_msg_pub.add;
2062 RAISE copy_recipe_err;
2063 END IF;
2064
2065
2066 EXCEPTION
2067 WHEN copy_recipe_err THEN
2068 x_return_status := FND_API.G_RET_STS_ERROR;
2069 WHEN OTHERS THEN
2070 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
2071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2072 END COPY_RECIPE_HEADER;
2073
2074 /* ================================================== */
2075 /* Procedure: */
2076 /* Validate_Formula */
2077 /* */
2078 /* DESCRIPTION: */
2079 /* This PL/SQL procedure is responsible for */
2080 /* validating the formula passed to the API */
2081 /* */
2082 /* */
2083 /* Notes : */
2084 /* Thomas Daniel 11/11/05. Bug 4716923. Added this */
2085 /* procedure to validate formula. */
2086 /* ================================================== */
2087
2088 PROCEDURE VALIDATE_FORMULA
2089 (p_formula_id IN NUMBER,
2090 p_formula_no IN VARCHAR2,
2091 p_formula_vers IN NUMBER,
2092 p_owner_organization_id IN NUMBER,
2093 x_return_status OUT NOCOPY VARCHAR2,
2094 x_formula_id OUT NOCOPY NUMBER
2095 ) IS
2096
2097 /* Defining all local variables */
2098 l_fm_rec_in GMDFMVAL_PUB.formula_info_in;
2099 l_fm_tab_out GMDFMVAL_PUB.formula_table_out;
2100 l_return_status VARCHAR2(1);
2101 BEGIN
2102 /* Initialize return status */
2103 x_return_status := FND_API.G_RET_STS_SUCCESS;
2104
2105 /* =================================== */
2106 /* Check if a formula_id OR */
2107 /* a formula_no and formula_vers combo */
2108 /* exists */
2109 /* =================================== */
2110 l_fm_rec_in.formula_no := p_formula_no;
2111 l_fm_rec_in.formula_vers := p_formula_vers;
2112 l_fm_rec_in.formula_id := p_formula_id;
2113
2114 GMDFMVAL_PUB.get_element(pElement_name => 'FORMULA',
2115 pRecord_in => l_fm_rec_in,
2116 xTable_out => l_fm_tab_out,
2117 xReturn => l_return_status);
2118 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2119 IF (p_formula_no IS NULL) AND
2120 (p_formula_id IS NULL) THEN
2121 x_return_status := FND_API.G_RET_STS_ERROR;
2122 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
2123 FND_MSG_PUB.Add;
2124 ELSIF (p_formula_vers IS NULL) AND
2125 (p_formula_id IS NULL) THEN
2126 x_return_status := FND_API.G_RET_STS_ERROR;
2127 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
2128 FND_MSG_PUB.Add;
2129 ELSE
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131 IF p_formula_id IS NOT NULL THEN
2132 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVFORMULA_ID');
2133 FND_MESSAGE.SET_TOKEN('FORMULA_ID', p_formula_id);
2134 ELSE
2135 FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULA_NO');
2136 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_no);
2137 END IF;
2138 FND_MSG_PUB.Add;
2139 END IF;
2140 ELSE
2141 x_formula_id := l_fm_tab_out(1).formula_id;
2142 END IF;
2143
2144 /* Check if all the items in the formula exists in the current organization */
2145 IF (p_owner_organization_id IS NOT NULL) AND
2146 (x_formula_id IS NOT NULL) THEN
2147 GMD_API_GRP.check_item_exists (p_formula_id => x_formula_id
2148 ,p_organization_id => p_owner_organization_id
2149 ,x_return_status => l_return_status);
2150 IF l_return_status <> FND_API.g_ret_sts_success THEN
2151 x_return_status := l_return_status;
2152 END IF;
2153 END IF;
2154 END validate_formula;
2155
2156
2157 END GMD_RECIPE_HEADER_PVT;
[Home] [Help]