[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_DETAIL_PVT
Source
1 PACKAGE BODY GMD_RECIPE_DETAIL_PVT AS
2 /* $Header: GMDVRCDB.pls 120.9.12010000.2 2008/11/12 18:15:04 rnalla ship $ */
3
4 /* Define any variable specific to this package */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_DETAIL_PVT' ;
6
7 /* ================================================== */
8 /* Procedure: */
9 /* Create_Recipe_Process_loss */
10 /* */
11 /* DESCRIPTION: */
12 /* This PL/SQL procedure is responsible for */
13 /* inserting a recipe */
14 /* ================================================== */
15 /* Start of commments */
16 /* API name : Create_Recipe_Process_loss */
17 /* Type : Private */
18 /* Function : */
19 /* parameters : */
20 /* */
21 /* End of comments */
22
23 PROCEDURE CREATE_RECIPE_PROCESS_LOSS
24 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
25 x_return_status OUT NOCOPY VARCHAR2
26 ) IS
27 /* Defining all local variables */
28 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_PROCESS_LOSS';
29
30 l_rowid VARCHAR2(32);
31 l_plant_ind NUMBER;
32 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
33
34 l_recipe_process_loss_id NUMBER := 0;
35
36 /* Variables used for defining status */
37 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
38 l_return_code NUMBER := 0;
39
40 /* Error message count and data */
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000);
43 l_return_stat VARCHAR2(10);
44
45 BEGIN
46 /* Initialization of all status */
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 /* ==================================== */
50 /* Validate orgn code if it has been */
51 /* provided */
52 /* ==================================== */
53 IF (p_recipe_detail_rec.organization_id IS NOT NULL) THEN
54
55 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_detail_rec.organization_id,
56 X_out_rec => l_out_rec,
57 X_return_status => l_return_stat);
58
59 /*IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
62 FND_MSG_PUB.ADD;
63 END IF;*/
64 END IF;
65
66 /* ================================== */
67 /* Generate RECIPE_PROCESS_LOSS_ID */
68 /* based on sequence number generator */
69 /* ================================== */
70 IF (p_recipe_detail_rec.recipe_process_loss_id IS NULL) THEN
71 SELECT gmd_recipe_process_loss_id_s.nextval
72 INTO l_recipe_process_loss_id
73 FROM sys.dual;
74 ELSE
75 l_recipe_process_loss_id := p_recipe_detail_rec.recipe_process_loss_id;
76 END IF;
77
78 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
79 INSERT INTO GMD_RECIPE_PROCESS_LOSS(
80 recipe_process_loss_id
81 ,recipe_id
82 ,organization_id
83 ,process_loss
84 ,contiguous_ind
85 ,text_code
86 ,creation_date
87 ,created_by
88 ,last_updated_by
89 ,last_update_date
90 ,last_update_login
91 , fixed_process_loss /* 6811759 */
92 , fixed_process_loss_uom )
93 VALUES ( l_recipe_process_loss_id
94 ,p_recipe_detail_rec.recipe_id
95 ,p_recipe_detail_rec.organization_id
96 ,p_recipe_detail_rec.process_loss
97 ,NVL(p_recipe_detail_rec.contiguous_ind,0)
98 ,p_recipe_detail_rec.text_code
99 ,NVL(p_recipe_detail_rec.creation_date,SYSDATE)
100 ,NVL(p_recipe_detail_rec.created_by,
101 gmd_api_grp.user_id)
102 ,NVL(p_recipe_detail_rec.last_updated_by ,
103 gmd_api_grp.user_id)
104 ,NVL(p_recipe_detail_rec.last_update_date,
105 SYSDATE)
106 ,NVL(p_recipe_detail_rec.last_update_login,
107 gmd_api_grp.login_id)
108 ,p_recipe_detail_rec.fixed_process_loss
109 ,p_recipe_detail_rec.fixed_process_loss_uom
110 );
111
112 END IF;
113 EXCEPTION
114 WHEN FND_API.G_EXC_ERROR THEN
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 WHEN OTHERS THEN
117 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 END CREATE_RECIPE_PROCESS_LOSS;
120
121 /* ================================================== */
122 /* Procedure: */
123 /* Create_Recipe_Customers */
124 /* */
125 /* DESCRIPTION: */
126 /* This PL/SQL procedure is responsible for */
127 /* inserting a recipe */
128 /* */
129 /* ================================================== */
130 /* Start of commments */
131 /* API name : Create_Recipe_Customers */
132 /* Type : Private */
133 /* Procedure : */
134 /* End of comments */
135
136 PROCEDURE CREATE_RECIPE_CUSTOMERS
137 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
138 x_return_status OUT NOCOPY VARCHAR2
139 ) IS
140
141 /* Defining all local variables */
142 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_CUSTOMERS';
143
144 /* Variables used for defining status */
145 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
146 l_return_code NUMBER := 0;
147
148 /* Error message count and data */
149 l_msg_count NUMBER;
150 l_msg_data VARCHAR2(2000);
151
152 BEGIN
153 /* Initialization of all status */
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 /* ====================================== */
157 /* Check if this customer exists in our */
158 /* Recipe Customer table */
159 /* ====================================== */
160 GMD_RECIPE_VAL.RECIPE_CUST_EXISTS (
161 P_API_VERSION => 1.0 ,
162 P_RECIPE_ID => p_recipe_detail_rec.recipe_id ,
163 P_CUSTOMER_ID => p_recipe_detail_rec.customer_ID ,
164 X_RETURN_STATUS => l_return_status ,
165 X_MSG_COUNT => l_msg_count ,
166 X_MSG_DATA => l_msg_data ,
167 X_RETURN_CODE => l_return_code);
168
169 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_CUSTOMER_INVALID');
172 FND_MSG_PUB.ADD;
173 END IF;
174
175 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
176 INSERT INTO GMD_RECIPE_CUSTOMERS(
177 recipe_id ,
178 customer_id ,
179 site_id ,
180 org_id ,
181 text_code ,
182 creation_date ,
183 created_by ,
184 last_updated_by ,
185 last_update_date ,
186 last_update_login )
187 VALUES ( p_recipe_detail_rec.recipe_id ,
188 p_recipe_detail_rec.customer_id ,
189 p_recipe_detail_rec.site_id ,
190 p_recipe_detail_rec.org_id ,
191 p_recipe_detail_rec.text_code ,
192 NVL(p_recipe_detail_rec.creation_date,
193 SYSDATE) ,
194 NVL(p_recipe_detail_rec.created_by,
195 gmd_api_grp.user_id) ,
196 p_recipe_detail_rec.last_updated_by ,
197 NVL(p_recipe_detail_rec.last_update_date,
198 SYSDATE) ,
199 NVL(p_recipe_detail_rec.last_update_login,
200 gmd_api_grp.login_id)
201 );
202 END IF;
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 WHEN OTHERS THEN
208 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 END CREATE_RECIPE_CUSTOMERS;
211
212 /* ================================================== */
213 /* Procedure: */
214 /* Create_Recipe_VR */
215 /* */
216 /* DESCRIPTION: */
217 /* This PL/SQL procedure is responsible for */
218 /* inserting a recipe */
219 /* ================================================== */
220 /* Start of commments */
221 /* API name : Create_Recipe_VR */
222 /* Type : Private */
223 /* Procedure : */
224 /* End of comments */
225
226 PROCEDURE CREATE_RECIPE_VR
227 ( p_recipe_vr_rec IN GMD_RECIPE_DETAIL.recipe_vr
228 ,p_recipe_vr_flex_rec IN GMD_RECIPE_DETAIL.flex
229 ,x_return_status OUT NOCOPY VARCHAR2
230 ) IS
231
232 /* Define all variables specific to this procedure */
233 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_VR';
234
235 l_recipe_vr_id NUMBER := 0;
236 l_item_id NUMBER := 0;
237
238 /* Variables used for defining status */
239 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
240 l_return_code NUMBER := 0;
241 l_plant_ind NUMBER;
242 l_lab_ind NUMBER;
243
244 /* Error message count and data */
245 l_msg_count NUMBER;
246 l_msg_data VARCHAR2(2000);
247
248 /* NPD Conv. */
249 CURSOR get_item_id(pItem_no Varchar2, pOrganization_id NUMBER) IS
250 SELECT inventory_item_id
251 FROM mtl_system_items_kfv
252 WHERE concatenated_segments = pItem_no AND
253 organization_id = pOrganization_id;
254
255 Setup_Failure EXCEPTION;
256 BEGIN
257 /* Initialization of all status */
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259
260 /* Intialize the setup fields */
261 IF NOT gmd_api_grp.setup_done THEN
262 gmd_api_grp.setup_done := gmd_api_grp.setup;
263 END IF;
264 IF NOT gmd_api_grp.setup_done THEN
265 RAISE setup_failure;
266 END IF;
267
268 /* ============================================== */
269 /* Validate Orgn Code */
270 /* Organization should be either a Plant/Lab type */
271 /* Can orgn_code be a null value ? */
272 /* Set the required indicator */
273 /* ============================================== */
274 GMD_RECIPE_VAL.recipe_orgn_code(
275 p_api_version => 1.0,
276 p_init_msg_list => FND_API.G_FALSE,
277 p_commit => FND_API.G_FALSE,
278 g_orgn_id => p_recipe_vr_rec.organization_id,
279 g_user_id => NVL(p_recipe_vr_rec.created_by,
280 gmd_api_grp.user_id),
281 p_required_ind => 'N',
282 x_return_status => l_return_status,
283 x_msg_count => l_msg_count,
284 x_msg_data => l_msg_data,
285 x_return_code => l_return_code,
286 x_plant_ind => l_plant_ind,
287 x_lab_ind => l_lab_ind);
288
289 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
292 FND_MSG_PUB.ADD;
293 END IF;
294
295 /* NPD Conv. Added the below */
296 IF (p_recipe_vr_rec.inventory_item_id IS NULL) THEN
297 OPEN get_item_id(p_recipe_vr_rec.Item_no, p_recipe_vr_rec.organization_id);
298 FETCH get_item_id INTO l_item_id;
299 CLOSE get_item_id;
300 ELSE
301 l_item_id := p_recipe_vr_rec.inventory_item_id;
302 END IF;
303
304 /* Insert into the recipe validity rules table */
305 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
306 /* ======================================== */
307 /* Generate the Validity Rule Id */
308 /* Sequence number */
309 /* ======================================== */
310 SELECT gmd_recipe_validity_id_s.nextval
311 INTO l_recipe_vr_id
312 FROM sys.dual;
313
314 --Added as part of Default Status Build 3408799
315 gmd_recipe_detail_pvt.pkg_recipe_validity_rule_id := l_recipe_vr_id;
316
317 INSERT INTO GMD_RECIPE_VALIDITY_RULES(
318 RECIPE_VALIDITY_RULE_ID
319 ,RECIPE_ID
320 ,ORGN_CODE
321 ,ORGANIZATION_ID -- NPD Conv.
322 ,INVENTORY_ITEM_ID
323 ,REVISION -- End NPD Conv.
324 ,RECIPE_USE
325 ,PREFERENCE
326 ,START_DATE
327 ,END_DATE
328 ,MIN_QTY
329 ,MAX_QTY
330 ,STD_QTY
331 ,DETAIL_UOM -- NPD Conv.
332 ,INV_MIN_QTY
333 ,INV_MAX_QTY
334 ,TEXT_CODE
335 ,ATTRIBUTE_CATEGORY
336 ,ATTRIBUTE1
337 ,ATTRIBUTE2
338 ,ATTRIBUTE3
339 ,ATTRIBUTE4
340 ,ATTRIBUTE5
341 ,ATTRIBUTE6
342 ,ATTRIBUTE7
343 ,ATTRIBUTE8
344 ,ATTRIBUTE9
345 ,ATTRIBUTE10
346 ,ATTRIBUTE11
347 ,ATTRIBUTE12
348 ,ATTRIBUTE13
349 ,ATTRIBUTE14
350 ,ATTRIBUTE15
351 ,ATTRIBUTE16
352 ,ATTRIBUTE17
353 ,ATTRIBUTE18
354 ,ATTRIBUTE19
355 ,ATTRIBUTE20
356 ,ATTRIBUTE21
357 ,ATTRIBUTE22
358 ,ATTRIBUTE23
359 ,ATTRIBUTE24
360 ,ATTRIBUTE25
361 ,ATTRIBUTE26
362 ,ATTRIBUTE27
363 ,ATTRIBUTE28
364 ,ATTRIBUTE29
365 ,ATTRIBUTE30
366 ,CREATED_BY
367 ,CREATION_DATE
368 ,LAST_UPDATED_BY
369 ,LAST_UPDATE_DATE
370 ,LAST_UPDATE_LOGIN
371 ,DELETE_MARK
372 ,VALIDITY_RULE_STATUS
373 ,PLANNED_PROCESS_LOSS /* Added for Bug No.5954361*/
374 , FIXED_PROCESS_LOSS /* RLNAGARA B6997624*/
375 , FIXED_PROCESS_LOSS_UOM /* RLNAGARA B6997624*/
376 )
377 VALUES ( l_RECIPE_VR_ID
378 ,p_recipe_vr_rec.RECIPE_ID
379 ,p_recipe_vr_rec.ORGN_CODE
380 ,p_recipe_vr_rec.ORGANIZATION_ID
381 ,l_ITEM_ID
382 ,p_recipe_vr_rec.REVISION
383 ,p_recipe_vr_rec.RECIPE_USE
384 ,p_recipe_vr_rec.PREFERENCE
385 ,p_recipe_vr_rec.START_DATE
386 ,p_recipe_vr_rec.END_DATE
387 ,p_recipe_vr_rec.MIN_QTY
388 ,p_recipe_vr_rec.MAX_QTY
389 ,p_recipe_vr_rec.STD_QTY
390 ,p_recipe_vr_rec.DETAIL_UOM
391 ,p_recipe_vr_rec.INV_MIN_QTY
392 ,p_recipe_vr_rec.INV_MAX_QTY
393 ,p_recipe_vr_rec.TEXT_CODE
394 ,p_recipe_vr_flex_rec.ATTRIBUTE_CATEGORY
395 ,p_recipe_vr_flex_rec.ATTRIBUTE1
396 ,p_recipe_vr_flex_rec.ATTRIBUTE2
397 ,p_recipe_vr_flex_rec.ATTRIBUTE3
398 ,p_recipe_vr_flex_rec.ATTRIBUTE4
399 ,p_recipe_vr_flex_rec.ATTRIBUTE5
400 ,p_recipe_vr_flex_rec.ATTRIBUTE6
401 ,p_recipe_vr_flex_rec.ATTRIBUTE7
402 ,p_recipe_vr_flex_rec.ATTRIBUTE8
403 ,p_recipe_vr_flex_rec.ATTRIBUTE9
404 ,p_recipe_vr_flex_rec.ATTRIBUTE10
405 ,p_recipe_vr_flex_rec.ATTRIBUTE11
406 ,p_recipe_vr_flex_rec.ATTRIBUTE12
407 ,p_recipe_vr_flex_rec.ATTRIBUTE13
408 ,p_recipe_vr_flex_rec.ATTRIBUTE14
409 ,p_recipe_vr_flex_rec.ATTRIBUTE15
410 ,p_recipe_vr_flex_rec.ATTRIBUTE16
411 ,p_recipe_vr_flex_rec.ATTRIBUTE17
412 ,p_recipe_vr_flex_rec.ATTRIBUTE18
413 ,p_recipe_vr_flex_rec.ATTRIBUTE19
414 ,p_recipe_vr_flex_rec.ATTRIBUTE20
415 ,p_recipe_vr_flex_rec.ATTRIBUTE21
416 ,p_recipe_vr_flex_rec.ATTRIBUTE22
417 ,p_recipe_vr_flex_rec.ATTRIBUTE23
418 ,p_recipe_vr_flex_rec.ATTRIBUTE24
419 ,p_recipe_vr_flex_rec.ATTRIBUTE25
420 ,p_recipe_vr_flex_rec.ATTRIBUTE26
421 ,p_recipe_vr_flex_rec.ATTRIBUTE27
422 ,p_recipe_vr_flex_rec.ATTRIBUTE28
423 ,p_recipe_vr_flex_rec.ATTRIBUTE29
424 ,p_recipe_vr_flex_rec.ATTRIBUTE30
425 ,NVL(p_recipe_vr_rec.CREATED_BY , gmd_api_grp.user_id )
426 ,NVL(p_recipe_vr_rec.CREATION_DATE, SYSDATE)
427 ,NVL(p_recipe_vr_rec.LAST_UPDATED_BY , gmd_api_grp.user_id )
428 ,NVL(p_recipe_vr_rec.LAST_UPDATE_DATE, SYSDATE)
429 ,NVL(p_recipe_vr_rec.LAST_UPDATE_LOGIN , gmd_api_grp.login_id )
430 ,NVL(p_recipe_vr_rec.DELETE_MARK , 0)
431 ,p_recipe_vr_rec.VALIDITY_RULE_STATUS
432 ,p_recipe_vr_rec.PLANNED_PROCESS_LOSS /* Added for Bug No.5954361*/
433 ,p_recipe_vr_rec.FIXED_PROCESS_LOSS /* RLNAGARA B6997624*/
434 ,p_recipe_vr_rec.FIXED_PROCESS_LOSS_UOM /* RLNAGARA B6997624*/
435 );
436
437 END IF;
438 EXCEPTION
439 WHEN FND_API.G_EXC_ERROR OR setup_failure THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 WHEN OTHERS THEN
442 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 END CREATE_RECIPE_VR;
445
446 /* ================================================== */
447 /* Procedure: */
448 /* Create_Recipe_Mtl */
449 /* */
450 /* DESCRIPTION: */
451 /* This PL/SQL procedure is responsible for */
452 /* inserting a recipe */
453 /* */
454 /* ================================================== */
455 /* Start of commments */
456 /* API name : Create_Recipe_Mtl */
457 /* Type : Private */
458 /* Procedure : */
459 /* End of comments */
460
461 PROCEDURE CREATE_RECIPE_MTL
462 ( p_recipe_mtl_rec IN GMD_RECIPE_DETAIL.recipe_material,
463 p_recipe_mtl_flex_rec IN GMD_RECIPE_DETAIL.flex,
464 x_return_status OUT NOCOPY VARCHAR2
465 ) IS
466
467 /* Define all variables specific to this procedure */
468 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECIPE_MTL';
469
470 BEGIN
471 /* Initialization of all status */
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 /* Insert into the recipe materials table */
475 INSERT INTO GMD_RECIPE_STEP_MATERIALS(
476 recipe_id
477 ,routingstep_id
478 ,formulaline_id
479 ,text_code
480 ,creation_date
481 ,created_by
482 ,last_updated_by
483 ,last_update_date
484 ,last_update_login
485 ,ATTRIBUTE_CATEGORY
486 ,ATTRIBUTE1
487 ,ATTRIBUTE2
488 ,ATTRIBUTE3
489 ,ATTRIBUTE4
490 ,ATTRIBUTE5
491 ,ATTRIBUTE6
492 ,ATTRIBUTE7
493 ,ATTRIBUTE8
494 ,ATTRIBUTE9
495 ,ATTRIBUTE10
496 ,ATTRIBUTE11
497 ,ATTRIBUTE12
498 ,ATTRIBUTE13
499 ,ATTRIBUTE14
500 ,ATTRIBUTE15
501 ,ATTRIBUTE16
502 ,ATTRIBUTE17
503 ,ATTRIBUTE18
504 ,ATTRIBUTE19
505 ,ATTRIBUTE20
506 ,ATTRIBUTE21
507 ,ATTRIBUTE22
508 ,ATTRIBUTE23
509 ,ATTRIBUTE24
510 ,ATTRIBUTE25
511 ,ATTRIBUTE26
512 ,ATTRIBUTE27
513 ,ATTRIBUTE28
514 ,ATTRIBUTE29
515 ,ATTRIBUTE30)
516 VALUES ( p_recipe_mtl_rec.recipe_id
517 ,p_recipe_mtl_rec.ROUTINGSTEP_ID
518 ,p_recipe_mtl_rec.FORMULALINE_ID
519 ,p_recipe_mtl_rec.text_code
520 ,NVL(p_recipe_mtl_rec.creation_date,SYSDATE)
521 ,NVL(p_recipe_mtl_rec.created_by,gmd_Api_grp.user_id)
522 ,NVL(p_recipe_mtl_rec.last_updated_by,gmd_api_grp.user_id)
523 ,NVL(p_recipe_mtl_rec.last_update_date,sysdate)
524 ,NVL(p_recipe_mtl_rec.last_update_login,gmd_Api_grp.login_id)
525 ,p_recipe_mtl_flex_rec.ATTRIBUTE_CATEGORY
526 ,p_recipe_mtl_flex_rec.ATTRIBUTE1
527 ,p_recipe_mtl_flex_rec.ATTRIBUTE2
528 ,p_recipe_mtl_flex_rec.ATTRIBUTE3
529 ,p_recipe_mtl_flex_rec.ATTRIBUTE4
530 ,p_recipe_mtl_flex_rec.ATTRIBUTE5
531 ,p_recipe_mtl_flex_rec.ATTRIBUTE6
532 ,p_recipe_mtl_flex_rec.ATTRIBUTE7
533 ,p_recipe_mtl_flex_rec.ATTRIBUTE8
534 ,p_recipe_mtl_flex_rec.ATTRIBUTE9
535 ,p_recipe_mtl_flex_rec.ATTRIBUTE10
536 ,p_recipe_mtl_flex_rec.ATTRIBUTE11
537 ,p_recipe_mtl_flex_rec.ATTRIBUTE12
538 ,p_recipe_mtl_flex_rec.ATTRIBUTE13
539 ,p_recipe_mtl_flex_rec.ATTRIBUTE14
540 ,p_recipe_mtl_flex_rec.ATTRIBUTE15
541 ,p_recipe_mtl_flex_rec.ATTRIBUTE16
542 ,p_recipe_mtl_flex_rec.ATTRIBUTE17
543 ,p_recipe_mtl_flex_rec.ATTRIBUTE18
544 ,p_recipe_mtl_flex_rec.ATTRIBUTE19
545 ,p_recipe_mtl_flex_rec.ATTRIBUTE20
546 ,p_recipe_mtl_flex_rec.ATTRIBUTE21
547 ,p_recipe_mtl_flex_rec.ATTRIBUTE22
548 ,p_recipe_mtl_flex_rec.ATTRIBUTE23
549 ,p_recipe_mtl_flex_rec.ATTRIBUTE24
550 ,p_recipe_mtl_flex_rec.ATTRIBUTE25
551 ,p_recipe_mtl_flex_rec.ATTRIBUTE26
552 ,p_recipe_mtl_flex_rec.ATTRIBUTE27
553 ,p_recipe_mtl_flex_rec.ATTRIBUTE28
554 ,p_recipe_mtl_flex_rec.ATTRIBUTE29
555 ,p_recipe_mtl_flex_rec.ATTRIBUTE30);
556 EXCEPTION
557 WHEN FND_API.G_EXC_ERROR THEN
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 WHEN OTHERS THEN
560 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 END CREATE_RECIPE_MTL;
563
564 /* ================================================== */
565 /* Procedure: */
566 /* Update_Recipe_Process_Loss */
567 /* */
568 /* DESCRIPTION: */
569 /* This PL/SQL procedure is responsible for */
570 /* updating recipe process loss */
571 /* */
572 /* ================================================== */
573 /* Start of commments */
574 /* API name : Update_Recipe_Process_loss */
575 /* Type : Private */
576 /* Procedure : */
577 /* End of comments */
578
579 PROCEDURE UPDATE_RECIPE_PROCESS_LOSS
580 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
581 x_return_status OUT NOCOPY VARCHAR2
582 ) IS
583 /* Defining all local variables */
584 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_PROCESS_LOSS';
585
586 l_rowid VARCHAR2(32);
587 l_plant_ind NUMBER;
588 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
589
590 l_recipe_process_loss_id NUMBER := 0;
591
592 /* Variables used for defining status */
593 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
594 l_return_code NUMBER := 0;
595
596 /* Error message count and data */
597 l_msg_count NUMBER;
598 l_msg_data VARCHAR2(2000);
599 l_return_stat VARCHAR2(10);
600 BEGIN
601 /* Initialization of all status */
602 x_return_status := FND_API.G_RET_STS_SUCCESS;
603
604 /* ==================================== */
605 /* Validate orgn code if it has been */
606 /* provided */
607 /* ==================================== */
608
609 IF (p_recipe_detail_rec.organization_id IS NOT NULL) THEN
610 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_detail_rec.organization_id,
611 X_out_rec => l_out_rec,
612 X_return_status => l_return_stat);
613
614 /*IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
617 FND_MSG_PUB.ADD;
618 END IF;*/
619 END IF;
620
621 /* Update into the recipe process loss table */
622 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
623 UPDATE gmd_recipe_process_loss
624 SET recipe_id = p_recipe_detail_rec.recipe_id ,
625 organization_id = p_recipe_detail_rec.organization_id,
626 text_code = p_recipe_detail_rec.text_code,
627 process_loss = p_recipe_detail_rec.process_loss,
628 contiguous_ind = NVL(p_recipe_detail_rec.contiguous_ind,0),
629 last_updated_by = NVL(p_recipe_detail_rec.last_updated_by,
630 gmd_api_grp.user_id),
631 last_update_date = NVL(p_recipe_detail_rec.last_update_date,
632 sysdate),
633 last_update_login = NVL(p_recipe_detail_rec.last_update_login,
634 gmd_api_grp.login_id),
635 fixed_process_loss = p_recipe_detail_rec.fixed_process_loss, /* 6811759 */
636 fixed_process_loss_uom = p_recipe_detail_rec.fixed_process_loss_uom
637 WHERE recipe_process_loss_id = p_recipe_detail_rec.recipe_process_loss_id;
638 END IF;
639
640 EXCEPTION
641 WHEN FND_API.G_EXC_ERROR THEN
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 WHEN OTHERS THEN
644 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
646 END UPDATE_RECIPE_PROCESS_LOSS;
647
648 /* ================================================== */
649 /* Procedure: */
650 /* Update_Recipe_Customers */
651 /* */
652 /* DESCRIPTION: */
653 /* This PL/SQL procedure is responsible for */
654 /* updating recipe process loss */
655 /* */
656 /* ================================================== */
657 /* Start of commments */
658 /* API name : Update_Recipe_Customers */
659 /* Type : Private */
660 /* Procedure : */
661 /* End of comments */
662
663 PROCEDURE UPDATE_RECIPE_CUSTOMERS
664 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
665 x_return_status OUT NOCOPY VARCHAR2
666 ) IS
667 /* Defining all local variables */
668 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_CUSTOMERS';
669
670 /* Variables used for defining status */
671 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
672 l_return_code NUMBER := 0;
673
674 /* Error message count and data */
675 l_msg_count NUMBER;
676 l_msg_data VARCHAR2(2000);
677
678 BEGIN
679 /* Initialization of all status */
680 x_return_status := FND_API.G_RET_STS_SUCCESS;
681
682 /* Update the recipe customer table */
683 /* only who columns needs to be updated */
684 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
685 UPDATE GMD_RECIPE_CUSTOMERS
686 SET text_code = p_recipe_detail_rec.text_code,
687 last_updated_by = NVL(p_recipe_detail_rec.last_updated_by,
688 gmd_api_grp.user_id),
689 last_update_date = NVL(p_recipe_detail_rec.last_update_date,
690 sysdate),
691 last_update_login = NVL(p_recipe_detail_rec.last_update_login,
692 gmd_api_grp.login_id)
693 WHERE recipe_id = p_recipe_detail_rec.recipe_id AND
694 customer_id = p_recipe_detail_rec.customer_id;
695 END IF;
696 EXCEPTION
697 WHEN FND_API.G_EXC_ERROR THEN
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 WHEN OTHERS THEN
700 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702 END UPDATE_RECIPE_CUSTOMERS;
703
704 /* ================================================== */
705 /* Procedure: */
706 /* Update_Recipe_VR */
707 /* */
708 /* DESCRIPTION: */
709 /* This PL/SQL procedure is responsible for */
710 /* updating recipe Validity Rules */
711 /* */
712 /* ================================================= */
713 /* Start of commments */
714 /* API name : Update_Recipe_VR */
715 /* Type : Private */
716 /* Procedure : */
717 /* End of comments */
718 PROCEDURE UPDATE_RECIPE_VR
719 (p_recipe_vr_rec IN GMD_RECIPE_DETAIL.recipe_vr ,
720 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
721 x_return_status OUT NOCOPY VARCHAR2
722 ) IS
723
724 /* Define all variables specific to this procedure */
725 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_VR';
726 l_vr_db_rec gmd_recipe_validity_rules%ROWTYPE;
727
728 p_vr_update_tbl GMD_VALIDITY_RULES_PVT.update_tbl_type;
729 l_row_cnt NUMBER := 1;
730
731 l_msg_cnt NUMBER;
732 l_msg_list VARCHAR2(2000);
733
734 Cursor get_db_vr_rec(pVR_id NUMBER) IS
735 Select *
736 From gmd_recipe_validity_rules
737 Where recipe_validity_rule_id = pVR_id;
738
739 BEGIN
740 /* Initialization of all status */
741 x_return_status := FND_API.G_RET_STS_SUCCESS;
742
743 OPEN get_db_vr_rec(p_recipe_vr_rec.recipe_validity_rule_id);
744 FETCH get_db_vr_rec INTO l_vr_db_rec;
745 CLOSE get_db_vr_rec;
746
747 /* setting up the table type p_vr_update_tbl */
748 /* Populate p_vr_table only when the values are different from the one in db */
749 IF ((l_vr_db_rec.orgn_code IS NULL) AND (p_recipe_vr_rec.orgn_code IS NOT NULL)) THEN
750 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
751 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.orgn_code;
752 l_row_cnt := l_row_cnt + 1;
753 ELSIF ((l_vr_db_rec.orgn_code IS NOT NULL) AND (p_recipe_vr_rec.orgn_code IS NULL)) THEN
754 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
755 p_vr_update_tbl(l_row_cnt).p_value := Null;
756 l_row_cnt := l_row_cnt + 1;
757 ELSIF (l_vr_db_rec.ORGN_CODE <> p_recipe_vr_rec.orgn_code) THEN
758 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
759 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.orgn_code;
760 l_row_cnt := l_row_cnt + 1;
761 END IF;
762
763 -- NPD Conv. Added the below for organization_id and revision columns.
764 IF ((l_vr_db_rec.organization_id IS NULL) AND (p_recipe_vr_rec.organization_id IS NOT NULL)) THEN
765 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
766 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.organization_id;
767 l_row_cnt := l_row_cnt + 1;
768 ELSIF ((l_vr_db_rec.organization_id IS NOT NULL) AND (p_recipe_vr_rec.organization_id IS NULL)) THEN
769 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
770 p_vr_update_tbl(l_row_cnt).p_value := Null;
771 l_row_cnt := l_row_cnt + 1;
772 ELSIF (l_vr_db_rec.organization_id <> p_recipe_vr_rec.organization_id) THEN
773 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
774 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.organization_id;
775 l_row_cnt := l_row_cnt + 1;
776 END IF;
777
778 IF ((l_vr_db_rec.revision IS NULL) AND (p_recipe_vr_rec.revision IS NOT NULL)) THEN
779 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
780 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.revision;
781 l_row_cnt := l_row_cnt + 1;
782 ELSIF ((l_vr_db_rec.revision IS NOT NULL) AND (p_recipe_vr_rec.revision IS NULL)) THEN
783 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
784 p_vr_update_tbl(l_row_cnt).p_value := Null;
785 l_row_cnt := l_row_cnt + 1;
786 ELSIF (l_vr_db_rec.revision <> p_recipe_vr_rec.revision) THEN
787 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
788 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.revision;
789 l_row_cnt := l_row_cnt + 1;
790 END IF;
791 -- End NPD Conv.
792
793 IF ((l_vr_db_rec.planned_process_loss IS NULL)
794 AND (p_recipe_vr_rec.planned_process_loss IS NOT NULL)) THEN
795 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
796 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.planned_process_loss;
797 l_row_cnt := l_row_cnt + 1;
798 ELSIF ((l_vr_db_rec.planned_process_loss IS NOT NULL)
799 AND (p_recipe_vr_rec.planned_process_loss IS NULL)) THEN
800 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
801 p_vr_update_tbl(l_row_cnt).p_value := Null;
802 l_row_cnt := l_row_cnt + 1;
803 ELSIF (l_vr_db_rec.planned_process_loss <> p_recipe_vr_rec.planned_process_loss) THEN
804 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
805 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.planned_process_loss;
806 l_row_cnt := l_row_cnt + 1;
807 END IF;
808
809 --RLNAGARA Start Bug6997624 Added code to update Fixed Process Loss and UOM
810 IF ((l_vr_db_rec.fixed_process_loss IS NULL)
811 AND (p_recipe_vr_rec.fixed_process_loss IS NOT NULL)) THEN
812 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
813 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss;
814 l_row_cnt := l_row_cnt + 1;
815 ELSIF ((l_vr_db_rec.fixed_process_loss IS NOT NULL)
816 AND (p_recipe_vr_rec.fixed_process_loss IS NULL)) THEN
817 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
818 p_vr_update_tbl(l_row_cnt).p_value := Null;
819 l_row_cnt := l_row_cnt + 1;
820 ELSIF (l_vr_db_rec.fixed_process_loss <> p_recipe_vr_rec.fixed_process_loss) THEN
821 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
822 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss;
823 l_row_cnt := l_row_cnt + 1;
824 END IF;
825
826 IF (l_vr_db_rec.FIXED_PROCESS_LOSS_UOM <> p_recipe_vr_rec.FIXED_PROCESS_LOSS_UOM) THEN
827 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS_UOM';
828 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss_uom;
829 l_row_cnt := l_row_cnt + 1;
830 END IF;
831
832 --RLNAGARA Bug6997624 End
833
834 -- NPD Conv. Replaced item_id with inventory_item_id
835 IF (l_vr_db_rec.INVENTORY_ITEM_ID <> p_recipe_vr_rec.INVENTORY_ITEM_ID) THEN
836 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INVENTORY_ITEM_ID';
837 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INVENTORY_ITEM_ID;
838 l_row_cnt := l_row_cnt + 1;
839 END IF;
840
841 IF (l_vr_db_rec.RECIPE_USE <> p_recipe_vr_rec.RECIPE_USE) THEN
842 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'RECIPE_USE';
843 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.RECIPE_USE;
844 l_row_cnt := l_row_cnt + 1;
845 END IF;
846
847 IF (l_vr_db_rec.PREFERENCE <> p_recipe_vr_rec.PREFERENCE) THEN
848 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PREFERENCE';
849 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.PREFERENCE;
850 l_row_cnt := l_row_cnt + 1;
851 END IF;
852
853 --Bug 3797002, kkillams
854 --FND_DATE.DATE_TO_CANONICAL function is added to conver the date into character value.
855 IF (l_vr_db_rec.START_DATE <> p_recipe_vr_rec.START_DATE) THEN
856 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'START_DATE';
857 p_vr_update_tbl(l_row_cnt).p_value :=FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.START_DATE);
858 l_row_cnt := l_row_cnt + 1;
859 END IF;
860
861 IF ((l_vr_db_rec.END_DATE IS NULL) AND (p_recipe_vr_rec.END_DATE IS NOT NULL)) THEN
862 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
863 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
864 l_row_cnt := l_row_cnt + 1;
865 ELSIF ((l_vr_db_rec.END_DATE IS NOT NULL) AND (p_recipe_vr_rec.END_DATE IS NULL)) THEN
866 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
867 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
868 l_row_cnt := l_row_cnt + 1;
869 ELSIF (l_vr_db_rec.END_DATE <> p_recipe_vr_rec.END_DATE) THEN
870 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
871 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
872 l_row_cnt := l_row_cnt + 1;
873 END IF;
874
875 IF ( l_vr_db_rec.MIN_QTY <> p_recipe_vr_rec.MIN_QTY) THEN
876 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'MIN_QTY';
877 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.MIN_QTY;
878 l_row_cnt := l_row_cnt + 1;
879 END IF;
880
881 IF (l_vr_db_rec.MAX_QTY <> p_recipe_vr_rec.MAX_QTY ) THEN
882 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'MAX_QTY';
883 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.MAX_QTY;
884 l_row_cnt := l_row_cnt + 1;
885 END IF;
886
887 IF (l_vr_db_rec.STD_QTY <> p_recipe_vr_rec.STD_QTY) THEN
888 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'STD_QTY';
889 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.STD_QTY;
890 l_row_cnt := l_row_cnt + 1;
891 END IF;
892
893 IF (l_vr_db_rec.DETAIL_UOM <> p_recipe_vr_rec.DETAIL_UOM) THEN
894 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'DETAIL_UOM';
895 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.DETAIL_UOM;
896 l_row_cnt := l_row_cnt + 1;
897 END IF;
898
899 IF (l_vr_db_rec.INV_MIN_QTY <> p_recipe_vr_rec.INV_MIN_QTY) THEN
900 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INV_MIN_QTY';
901 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INV_MIN_QTY;
902 l_row_cnt := l_row_cnt + 1;
903 END IF;
904
905 IF (l_vr_db_rec.INV_MAX_QTY <> p_recipe_vr_rec.INV_MAX_QTY ) THEN
906 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INV_MAX_QTY';
907 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INV_MAX_QTY;
908 l_row_cnt := l_row_cnt + 1;
909 END IF;
910 -- KSHUKLA added NVL conditions for the 5079519
911 IF (NVL(l_vr_db_rec.TEXT_CODE, '-9999') <> NVL(p_recipe_vr_rec.TEXT_CODE, '-9999')) THEN
912 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'TEXT_CODE';
913 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.TEXT_CODE;
914 l_row_cnt := l_row_cnt + 1;
915 END IF;
916
917 -- Bug# 4134275 Kapil M
918 -- To update WHO columns
919 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATED_BY';
920 p_vr_update_tbl(l_row_cnt).p_value := NVL(p_recipe_vr_rec.last_updated_by, fnd_global.USER_ID);
921 l_row_cnt := l_row_cnt + 1;
922
923 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATE_DATE';
924 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(NVL(p_recipe_vr_rec.last_update_date,SYSDATE));
925 l_row_cnt := l_row_cnt + 1;
926
927 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATE_LOGIN';
928 p_vr_update_tbl(l_row_cnt).p_value := NVL(p_recipe_vr_rec.last_update_login, gmd_api_grp.login_id);
929 l_row_cnt := l_row_cnt + 1;
930
931 -- 4134275
932 -- KSHUKLA added NVL conditions for the 5079519
933 IF (NVL(l_vr_db_rec.attribute1, '-9999') <> NVL(p_flex_update_rec.attribute1, '-9999')) THEN
934 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE1';
935 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute1;
936 l_row_cnt := l_row_cnt + 1;
937 END IF;
938
939 IF (NVL(l_vr_db_rec.attribute2 , '-9999') <> NVL(p_flex_update_rec.attribute2, '-9999')) THEN
940 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE2';
941 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute2;
942 l_row_cnt := l_row_cnt + 1;
943 END IF;
944
945 IF (NVL(l_vr_db_rec.attribute3, '-9999') <> NVL(p_flex_update_rec.attribute3, '-9999')) THEN
946 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE3';
947 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute3;
948 l_row_cnt := l_row_cnt + 1;
949 END IF;
950
951 IF (NVL(l_vr_db_rec.attribute4, '-9999') <> NVL(p_flex_update_rec.attribute4, '-9999')) THEN
952 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE4';
953 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute4;
954 l_row_cnt := l_row_cnt + 1;
955 END IF;
956
957 IF (NVL(l_vr_db_rec.attribute5 , '-9999') <> NVL(p_flex_update_rec.attribute5, '-9999')) THEN
958 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE5';
959 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute5;
960 l_row_cnt := l_row_cnt + 1;
961 END IF;
962
963 IF (NVL(l_vr_db_rec.attribute6, '-9999') <> NVL(p_flex_update_rec.attribute6, '-9999')) THEN
964 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE6';
965 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute6;
966 l_row_cnt := l_row_cnt + 1;
967 END IF;
968
969 IF (NVL(l_vr_db_rec.attribute7, '-9999') <> NVL(p_flex_update_rec.attribute7, '-9999')) THEN
970 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE7';
971 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute7;
972 l_row_cnt := l_row_cnt + 1;
973 END IF;
974
975 IF (NVL(l_vr_db_rec.attribute8 , '-9999') <> NVL(p_flex_update_rec.attribute8, '-9999')) THEN
976 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE8';
977 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute8;
978 l_row_cnt := l_row_cnt + 1;
979 END IF;
980
981 IF (NVL(l_vr_db_rec.attribute9 , '-9999') <> NVL(p_flex_update_rec.attribute9, '-9999')) THEN
982 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE9';
983 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute9;
984 l_row_cnt := l_row_cnt + 1;
985 END IF;
986
987 IF (NVL(l_vr_db_rec.attribute10, '-9999') <> NVL(p_flex_update_rec.attribute10, '-9999')) THEN
988 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE10';
989 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute10;
990 l_row_cnt := l_row_cnt + 1;
991 END IF;
992
993 IF (NVL(l_vr_db_rec.attribute11, '-9999') <> NVL(p_flex_update_rec.attribute11, '-9999')) THEN
994 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE11';
995 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute11;
996 l_row_cnt := l_row_cnt + 1;
997 END IF;
998
999 IF (NVL(l_vr_db_rec.attribute12, '-9999') <> NVL(p_flex_update_rec.attribute12, '-9999')) THEN
1000 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE12';
1001 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute12;
1002 l_row_cnt := l_row_cnt + 1;
1003 END IF;
1004
1005 IF (NVL(l_vr_db_rec.attribute13, '-9999') <> NVL(p_flex_update_rec.attribute13, '-9999')) THEN
1006 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE13';
1007 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute13;
1008 l_row_cnt := l_row_cnt + 1;
1009 END IF;
1010
1011 IF (NVL(l_vr_db_rec.attribute14 , '-9999') <> NVL(p_flex_update_rec.attribute14, '-9999')) THEN
1012 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE14';
1013 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute14;
1014 l_row_cnt := l_row_cnt + 1;
1015 END IF;
1016
1017 IF (NVL(l_vr_db_rec.attribute15, '-9999') <> NVL(p_flex_update_rec.attribute15, '-9999')) THEN
1018 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE15';
1019 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute15;
1020 l_row_cnt := l_row_cnt + 1;
1021 END IF;
1022
1023 IF (NVL(l_vr_db_rec.attribute16, '-9999') <> NVL(p_flex_update_rec.attribute16, '-9999')) THEN
1024 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE16';
1025 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute16;
1026 l_row_cnt := l_row_cnt + 1;
1027 END IF;
1028
1029 IF (NVL(l_vr_db_rec.attribute17 , '-9999') <> NVL(p_flex_update_rec.attribute17, '-9999')) THEN
1030 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE17';
1031 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute17;
1032 l_row_cnt := l_row_cnt + 1;
1033 END IF;
1034
1035 IF (NVL(l_vr_db_rec.attribute18 , '-9999') <> NVL(p_flex_update_rec.attribute18, '-9999')) THEN
1036 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE18';
1037 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute18;
1038 l_row_cnt := l_row_cnt + 1;
1039 END IF;
1040
1041 IF (NVL(l_vr_db_rec.attribute19, '-9999') <> NVL(p_flex_update_rec.attribute19, '-9999')) THEN
1042 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE19';
1043 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute19;
1044 l_row_cnt := l_row_cnt + 1;
1045 END IF;
1046
1047 IF (NVL(l_vr_db_rec.attribute20 , '-9999') <> NVL(p_flex_update_rec.attribute20, '-9999')) THEN
1048 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE20';
1049 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute20;
1050 l_row_cnt := l_row_cnt + 1;
1051 END IF;
1052
1053
1054 IF (NVL(l_vr_db_rec.attribute21, '-9999') <> NVL(p_flex_update_rec.attribute21, '-9999')) THEN
1055 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE21';
1056 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute21;
1057 l_row_cnt := l_row_cnt + 1;
1058 END IF;
1059
1060 IF (NVL(l_vr_db_rec.attribute22 , '-9999') <> NVL(p_flex_update_rec.attribute22, '-9999')) THEN
1061 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE22';
1062 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute22;
1063 l_row_cnt := l_row_cnt + 1;
1064 END IF;
1065
1066 IF (NVL(l_vr_db_rec.attribute23, '-9999') <> NVL(p_flex_update_rec.attribute23, '-9999')) THEN
1067 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE23';
1068 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute23;
1069 l_row_cnt := l_row_cnt + 1;
1070 END IF;
1071
1072 IF (NVL(l_vr_db_rec.attribute24, '-9999') <> NVL(p_flex_update_rec.attribute24, '-9999')) THEN
1073 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE24';
1074 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute24;
1075 l_row_cnt := l_row_cnt + 1;
1076 END IF;
1077
1078 IF (NVL(l_vr_db_rec.attribute25, '-9999') <> NVL(p_flex_update_rec.attribute25, '-9999')) THEN
1079 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE25';
1080 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute25;
1081 l_row_cnt := l_row_cnt + 1;
1082 END IF;
1083
1084 IF (NVL(l_vr_db_rec.attribute26, '-9999') <> NVL(p_flex_update_rec.attribute26, '-9999')) THEN
1085 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE26';
1086 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute26;
1087 l_row_cnt := l_row_cnt + 1;
1088 END IF;
1089
1090 IF (NVL(l_vr_db_rec.attribute27, '-9999') <> NVL(p_flex_update_rec.attribute27, '-9999')) THEN
1091 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE27';
1092 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute27;
1093 l_row_cnt := l_row_cnt + 1;
1094 END IF;
1095
1096 IF (NVL(l_vr_db_rec.attribute28, '-9999') <> NVL(p_flex_update_rec.attribute28, '-9999')) THEN
1097 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE28';
1098 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute28;
1099 l_row_cnt := l_row_cnt + 1;
1100 END IF;
1101
1102 IF (NVL(l_vr_db_rec.attribute29, '-9999') <> NVL(p_flex_update_rec.attribute29, '-9999')) THEN
1103 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE29';
1104 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute29;
1105 l_row_cnt := l_row_cnt + 1;
1106 END IF;
1107
1108 IF (NVL(l_vr_db_rec.attribute30 , '-9999') <> NVL(p_flex_update_rec.attribute30, '-9999')) THEN
1109 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE30';
1110 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute30;
1111 l_row_cnt := l_row_cnt + 1;
1112 END IF;
1113
1114
1115 IF p_vr_update_tbl.count > 0 THEN
1116 /* update recipe validity rules table */
1117 GMD_VALIDITY_RULES_PVT.update_validity_rules
1118 ( p_validity_rule_id => p_recipe_vr_rec.recipe_validity_rule_id
1119 , p_update_table => p_vr_update_tbl
1120 , x_message_count => l_msg_cnt
1121 , x_message_list => l_msg_list
1122 , x_return_status => x_return_status
1123 );
1124 END IF;
1125
1126 /* Get the messgae list and count generated by this API */
1127 fnd_msg_pub.count_and_get (
1128 p_count => l_msg_cnt
1129 ,p_encoded => FND_API.g_false
1130 ,p_data => l_msg_list);
1131
1132 EXCEPTION
1133 WHEN FND_API.G_EXC_ERROR THEN
1134 x_return_status := FND_API.G_RET_STS_ERROR;
1135 WHEN OTHERS THEN
1136 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 END UPDATE_RECIPE_VR;
1139
1140 /* ===================================================*/
1141 /* Procedure: */
1142 /* Recipe_Routing_Steps */
1143 /* */
1144 /* DESCRIPTION: */
1145 /* This PL/SQL procedure is responsible for */
1146 /* inserting and updating recipe Routing steps */
1147 /* */
1148 /* ===================================================*/
1149 /* Start of commments */
1150 /* API name : Recipe_Routing_Steps */
1151 /* Type : Private */
1152 /* Procedure : */
1153 /* End of comments */
1154
1155 PROCEDURE RECIPE_ROUTING_STEPS
1156 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1157 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1158 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1159 x_return_status OUT NOCOPY VARCHAR2
1160 ) IS
1161 /* Define all variables specific to this procedure */
1162 l_dml_type VARCHAR2(1) := 'I';
1163 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ROUTING_STEPS';
1164
1165 l_rc_id NUMBER := 0;
1166 l_rcst_id NUMBER := 0;
1167
1168 /* Null values should not passed as a paramter */
1169 /* This cursor decides whether to insert or update. */
1170 CURSOR recipe_rout_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
1171 SELECT recipe_id, routingstep_id
1172 FROM gmd_recipe_routing_steps
1173 where recipe_id = NVL(vRecipe_id,-1) AND
1174 RoutingStep_id = NVL(vRoutingStep_id,-1);
1175 BEGIN
1176 /* Updating recipe routing step for first time is in fact inserting a new record */
1177 /* in gmd_recipe_routing_step table. [Form initially shows values from */
1178 /* fm_rout_dtl. When user "changes" values, they are saved in recipe table.] */
1179
1180 /* Initialization of status. */
1181 x_return_status := FND_API.G_RET_STS_SUCCESS;
1182
1183 OPEN recipe_rout_cur(p_recipe_detail_rec.recipe_id,
1184 p_recipe_detail_rec.routingstep_id);
1185 FETCH recipe_rout_cur INTO l_rc_id, l_rcst_id;
1186 IF (recipe_rout_cur%NOTFOUND) then
1187 l_dml_type := 'I';
1188 ELSE
1189 l_dml_type := 'U';
1190 END IF;
1191 CLOSE recipe_rout_cur;
1192
1193 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1194 IF (l_dml_type = 'I') THEN
1195 /* Assign flex fields */
1196 INSERT INTO GMD_RECIPE_ROUTING_STEPS (
1197 RECIPE_ID ,
1198 ROUTINGSTEP_ID ,
1199 STEP_QTY ,
1200 TEXT_CODE ,
1201 MASS_QTY ,
1202 MASS_STD_UOM ,
1203 VOLUME_QTY ,
1204 VOLUME_STD_UOM ,
1205 CREATED_BY ,
1206 CREATION_DATE ,
1207 LAST_UPDATE_DATE ,
1208 LAST_UPDATE_LOGIN ,
1209 LAST_UPDATED_BY ,
1210 ATTRIBUTE1 ,
1211 ATTRIBUTE2 ,
1212 ATTRIBUTE3 ,
1213 ATTRIBUTE4 ,
1214 ATTRIBUTE5 ,
1215 ATTRIBUTE6 ,
1216 ATTRIBUTE7 ,
1217 ATTRIBUTE8 ,
1218 ATTRIBUTE9 ,
1219 ATTRIBUTE10 ,
1220 ATTRIBUTE11 ,
1221 ATTRIBUTE12 ,
1222 ATTRIBUTE13 ,
1223 ATTRIBUTE14 ,
1224 ATTRIBUTE15 ,
1225 ATTRIBUTE16 ,
1226 ATTRIBUTE17 ,
1227 ATTRIBUTE18 ,
1228 ATTRIBUTE19 ,
1229 ATTRIBUTE20 ,
1230 ATTRIBUTE21 ,
1231 ATTRIBUTE22 ,
1232 ATTRIBUTE23 ,
1233 ATTRIBUTE24 ,
1234 ATTRIBUTE25 ,
1235 ATTRIBUTE26 ,
1236 ATTRIBUTE27 ,
1237 ATTRIBUTE28 ,
1238 ATTRIBUTE29 ,
1239 ATTRIBUTE30 ,
1240 ATTRIBUTE_CATEGORY )
1241 VALUES (
1242 p_recipe_detail_rec.recipe_id ,
1243 p_recipe_detail_rec.routingstep_id ,
1244 p_recipe_detail_rec.step_qty ,
1245 p_recipe_detail_rec.TEXT_CODE ,
1246 p_recipe_detail_rec.MASS_QTY ,
1247 p_recipe_detail_rec.MASS_STD_UOM ,
1248 p_recipe_detail_rec.VOLUME_QTY ,
1249 p_recipe_detail_rec.VOLUME_STD_UOM ,
1250 NVL(p_recipe_detail_rec.CREATED_BY, gmd_api_grp.user_id) ,
1251 NVL(p_recipe_detail_rec.CREATION_DATE,sysdate) ,
1252 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE,sysdate),
1253 NVL(p_recipe_detail_rec.LAST_UPDATE_LOGIN, gmd_api_grp.login_id),
1254 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1255 p_flex_insert_rec.ATTRIBUTE1 ,
1256 p_flex_insert_rec.ATTRIBUTE2 ,
1257 p_flex_insert_rec.ATTRIBUTE3 ,
1258 p_flex_insert_rec.ATTRIBUTE4 ,
1259 p_flex_insert_rec.ATTRIBUTE5 ,
1260 p_flex_insert_rec.ATTRIBUTE6 ,
1261 p_flex_insert_rec.ATTRIBUTE7 ,
1262 p_flex_insert_rec.ATTRIBUTE8 ,
1263 p_flex_insert_rec.ATTRIBUTE9 ,
1264 p_flex_insert_rec.ATTRIBUTE10 ,
1265 p_flex_insert_rec.ATTRIBUTE11 ,
1266 p_flex_insert_rec.ATTRIBUTE12 ,
1267 p_flex_insert_rec.ATTRIBUTE13 ,
1268 p_flex_insert_rec.ATTRIBUTE14 ,
1269 p_flex_insert_rec.ATTRIBUTE15 ,
1270 p_flex_insert_rec.ATTRIBUTE16 ,
1271 p_flex_insert_rec.ATTRIBUTE17 ,
1272 p_flex_insert_rec.ATTRIBUTE18 ,
1273 p_flex_insert_rec.ATTRIBUTE19 ,
1274 p_flex_insert_rec.ATTRIBUTE20 ,
1275 p_flex_insert_rec.ATTRIBUTE21 ,
1276 p_flex_insert_rec.ATTRIBUTE22 ,
1277 p_flex_insert_rec.ATTRIBUTE23 ,
1278 p_flex_insert_rec.ATTRIBUTE24 ,
1279 p_flex_insert_rec.ATTRIBUTE25 ,
1280 p_flex_insert_rec.ATTRIBUTE26 ,
1281 p_flex_insert_rec.ATTRIBUTE27 ,
1282 p_flex_insert_rec.ATTRIBUTE28 ,
1283 p_flex_insert_rec.ATTRIBUTE29 ,
1284 p_flex_insert_rec.ATTRIBUTE30 ,
1285 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1286
1287 END IF; /* end of dml type */
1288
1289 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1290 IF (l_dml_type = 'U') THEN
1291 UPDATE GMD_RECIPE_ROUTING_STEPS
1292 SET
1293 STEP_QTY = p_recipe_detail_rec.step_qty,
1294 MASS_QTY = p_recipe_detail_rec.mass_qty,
1295 MASS_STD_UOM = p_recipe_detail_rec.mass_std_uom,
1296 VOLUME_QTY = p_recipe_detail_rec.volume_qty,
1297 VOLUME_STD_UOM = p_recipe_detail_rec.volume_std_uom,
1298 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1299 TEXT_CODE = p_recipe_detail_rec.text_code,
1300 LAST_UPDATED_BY = NVL(p_recipe_detail_rec.last_updated_by, gmd_api_grp.user_id),
1301 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date,sysdate) ,
1302 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1303 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1304 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1305 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1306 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1307 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1308 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1309 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1310 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1311 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1312 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1313 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1314 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1315 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1316 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1317 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1318 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1319 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1320 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1321 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1322 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1323 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1324 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1325 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1326 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1327 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1328 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1329 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1330 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1331 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1332 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1333 WHERE recipe_id = p_recipe_detail_rec.recipe_id
1334 AND routingstep_id = p_recipe_detail_rec.routingstep_id;
1335 END IF; /* ends dml type */
1336 EXCEPTION
1337 WHEN FND_API.G_EXC_ERROR THEN
1338 x_return_status := FND_API.G_RET_STS_ERROR;
1339 WHEN OTHERS THEN
1340 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1342 END Recipe_Routing_Steps;
1343
1344 /* ================================================== */
1345 /* Procedure: */
1346 /* Recipe_Orgn_Operations */
1347 /* */
1348 /* DESCRIPTION: */
1349 /* This PL/SQL procedure is responsible for */
1350 /* inserting and updating recipe orgn activities */
1351 /* */
1352 /* ===================================================*/
1353 /* Start of commments */
1354 /* API name : Recipe_Orgn_operations */
1355 /* Type : Private */
1356 /* Procedure : */
1357 /* End of comments */
1358
1359 PROCEDURE RECIPE_ORGN_OPERATIONS
1360 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1361 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1362 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1363 x_return_status OUT NOCOPY VARCHAR2
1364 ) IS
1365
1366 /* Define all variables specific to this procedure */
1367 l_dml_type VARCHAR2(1) := 'I';
1368 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_OPERATIONS';
1369 l_rowid VARCHAR2(40);
1370
1371 l_roact_id NUMBER;
1372
1373 /* This cursor decides whether to insert or update. */
1374 CURSOR recipe_activity_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
1375 vOprn_line_id NUMBER, vOrgn_id NUMBER) IS
1376 SELECT rowid
1377 FROM gmd_recipe_orgn_activities
1378 where recipe_id = NVL(vRecipe_id,-1) AND
1379 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
1380 oprn_line_id = NVL(vOprn_line_id,-1) AND
1381 organization_id = vOrgn_id;
1382 BEGIN
1383 /* Updating recipe orgn activity for forst time infact insert a new record in */
1384 /* gmd_recipe_orgn activities table */
1385
1386 /* Initialization of all status */
1387 x_return_status := FND_API.G_RET_STS_SUCCESS;
1388
1389 /* To decide on the operation to perform */
1390 /* If activity already exists then it is a update */
1391 OPEN recipe_activity_cur(p_recipe_detail_rec.recipe_id,
1392 p_recipe_detail_rec.routingstep_id,
1393 p_recipe_detail_rec.oprn_line_id,
1394 p_recipe_detail_rec.organization_id);
1395 FETCH recipe_activity_cur INTO l_rowid;
1396 IF (recipe_activity_cur%NOTFOUND) THEN
1397 l_dml_type := 'I';
1398 ELSE
1399 l_dml_type := 'U';
1400 END IF;
1401 CLOSE recipe_activity_cur;
1402
1403 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1404 IF (l_dml_type = 'I') THEN
1405 INSERT INTO GMD_RECIPE_ORGN_ACTIVITIES (
1406 RECIPE_ID ,
1407 ROUTINGSTEP_ID ,
1408 OPRN_LINE_ID ,
1409 ACTIVITY_FACTOR ,
1410 ORGANIZATION_ID ,
1411 CREATED_BY ,
1412 CREATION_DATE ,
1413 LAST_UPDATE_DATE ,
1414 LAST_UPDATE_LOGIN ,
1415 TEXT_CODE ,
1416 LAST_UPDATED_BY ,
1417 ATTRIBUTE1 ,
1418 ATTRIBUTE2 ,
1419 ATTRIBUTE3 ,
1420 ATTRIBUTE4 ,
1421 ATTRIBUTE5 ,
1422 ATTRIBUTE6 ,
1423 ATTRIBUTE7 ,
1424 ATTRIBUTE8 ,
1425 ATTRIBUTE9 ,
1426 ATTRIBUTE10 ,
1427 ATTRIBUTE11 ,
1428 ATTRIBUTE12 ,
1429 ATTRIBUTE13 ,
1430 ATTRIBUTE14 ,
1431 ATTRIBUTE15 ,
1432 ATTRIBUTE16 ,
1433 ATTRIBUTE17 ,
1434 ATTRIBUTE18 ,
1435 ATTRIBUTE19 ,
1436 ATTRIBUTE20 ,
1437 ATTRIBUTE21 ,
1438 ATTRIBUTE22 ,
1439 ATTRIBUTE23 ,
1440 ATTRIBUTE24 ,
1441 ATTRIBUTE25 ,
1442 ATTRIBUTE26 ,
1443 ATTRIBUTE27 ,
1444 ATTRIBUTE28 ,
1445 ATTRIBUTE29 ,
1446 ATTRIBUTE30 ,
1447 ATTRIBUTE_CATEGORY )
1448 VALUES (
1449 p_recipe_detail_rec.recipe_id ,
1450 p_recipe_detail_rec.routingstep_id ,
1451 p_recipe_detail_rec.oprn_line_id ,
1452 p_recipe_detail_rec.activity_factor ,
1453 p_recipe_detail_rec.organization_id ,
1454 NVL(p_recipe_detail_rec.CREATED_BY, gmd_api_grp.user_id) ,
1455 NVL(p_recipe_detail_rec.CREATION_DATE,sysdate) ,
1456 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE,sysdate) ,
1457 NVL (p_recipe_detail_rec.LAST_UPDATE_LOGIN , gmd_api_grp.login_id) ,
1458 p_recipe_detail_rec.TEXT_CODE ,
1459 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1460 p_flex_insert_rec.ATTRIBUTE1 ,
1461 p_flex_insert_rec.ATTRIBUTE2 ,
1462 p_flex_insert_rec.ATTRIBUTE3 ,
1463 p_flex_insert_rec.ATTRIBUTE4 ,
1464 p_flex_insert_rec.ATTRIBUTE5 ,
1465 p_flex_insert_rec.ATTRIBUTE6 ,
1466 p_flex_insert_rec.ATTRIBUTE7 ,
1467 p_flex_insert_rec.ATTRIBUTE8 ,
1468 p_flex_insert_rec.ATTRIBUTE9 ,
1469 p_flex_insert_rec.ATTRIBUTE10 ,
1470 p_flex_insert_rec.ATTRIBUTE11 ,
1471 p_flex_insert_rec.ATTRIBUTE12 ,
1472 p_flex_insert_rec.ATTRIBUTE13 ,
1473 p_flex_insert_rec.ATTRIBUTE14 ,
1474 p_flex_insert_rec.ATTRIBUTE15 ,
1475 p_flex_insert_rec.ATTRIBUTE16 ,
1476 p_flex_insert_rec.ATTRIBUTE17 ,
1477 p_flex_insert_rec.ATTRIBUTE18 ,
1478 p_flex_insert_rec.ATTRIBUTE19 ,
1479 p_flex_insert_rec.ATTRIBUTE20 ,
1480 p_flex_insert_rec.ATTRIBUTE21 ,
1481 p_flex_insert_rec.ATTRIBUTE22 ,
1482 p_flex_insert_rec.ATTRIBUTE23 ,
1483 p_flex_insert_rec.ATTRIBUTE24 ,
1484 p_flex_insert_rec.ATTRIBUTE25 ,
1485 p_flex_insert_rec.ATTRIBUTE26 ,
1486 p_flex_insert_rec.ATTRIBUTE27 ,
1487 p_flex_insert_rec.ATTRIBUTE28 ,
1488 p_flex_insert_rec.ATTRIBUTE29 ,
1489 p_flex_insert_rec.ATTRIBUTE30 ,
1490 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1491 END IF; /* end of dml type */
1492
1493 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1494 IF (l_dml_type = 'U') THEN
1495 UPDATE GMD_RECIPE_ORGN_ACTIVITIES
1496 SET ACTIVITY_FACTOR = p_recipe_detail_rec.activity_factor,
1497 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date,sysdate),
1498 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1499 TEXT_CODE = p_recipe_detail_rec.text_code,
1500 LAST_UPDATED_BY = p_recipe_detail_rec.last_updated_by,
1501 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1502 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1503 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1504 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1505 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1506 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1507 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1508 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1509 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1510 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1511 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1512 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1513 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1514 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1515 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1516 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1517 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1518 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1519 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1520 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1521 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1522 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1523 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1524 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1525 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1526 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1527 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1528 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1529 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1530 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1531 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1532 WHERE rowid = l_rowid;
1533 END IF; /* ends dml type */
1534 EXCEPTION
1535 WHEN FND_API.G_EXC_ERROR THEN
1536 x_return_status := FND_API.G_RET_STS_ERROR;
1537 WHEN OTHERS THEN
1538 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540 END RECIPE_ORGN_OPERATIONS;
1541
1542
1543 /* ================================================== */
1544 /* Procedure: */
1545 /* Recipe_Orgn_Resources */
1546 /* */
1547 /* DESCRIPTION: */
1548 /* This PL/SQL procedure is responsible for */
1549 /* inserting and updating recipe orgn resources */
1550 /* */
1551 /* ===================================================*/
1552 /* Start of commments */
1553 /* API name : Recipe_Orgn_Resources */
1554 /* Type : Private */
1555 /* Procedure : */
1556 /* End of comments */
1557
1558 PROCEDURE RECIPE_ORGN_RESOURCES
1559 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1560 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1561 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1562 x_return_status OUT NOCOPY VARCHAR2
1563 ) IS
1564
1565 /* Define all variables specific to this procedure */
1566 l_dml_type VARCHAR2(1) := 'I';
1567 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_RESOURCES';
1568
1569 l_rores_id NUMBER;
1570 l_rowid VARCHAR2(32);
1571
1572 /* This cursor decides whether to insert or update. */
1573 CURSOR recipe_resources_cur(vRecipe_id NUMBER,
1574 vRoutingStep_id NUMBER,
1575 vOprn_line_id NUMBER,
1576 vResources VARCHAR2,
1577 vOrgn_id NUMBER) IS
1578 SELECT rowid
1579 FROM gmd_recipe_orgn_resources
1580 where recipe_id = NVL(vRecipe_id,-1) AND
1581 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
1582 oprn_line_id = NVL(vOprn_line_id,-1) AND
1583 resources = vResources AND
1584 organization_id = vOrgn_id;
1585
1586 BEGIN
1587 /* Updating recipe orgn resources for forst time infact insert a new record in */
1588 /* gmd_recipe_orgn_resources table */
1589
1590 /* Initialization of all status */
1591 x_return_status := FND_API.G_RET_STS_SUCCESS;
1592
1593 /* To decide on the operation to perform */
1594 /* If resource already exists then it is a update */
1595 OPEN recipe_resources_cur(p_recipe_detail_rec.recipe_id, p_recipe_detail_rec.routingstep_id,
1596 p_recipe_detail_rec.oprn_line_id,
1597 p_recipe_detail_rec.resources,
1598 p_recipe_detail_rec.organization_id);
1599 FETCH recipe_resources_cur INTO l_rowid;
1600 IF (recipe_resources_cur%NOTFOUND) THEN
1601 l_dml_type := 'I';
1602 ELSE
1603 l_dml_type := 'U';
1604 END IF;
1605 CLOSE recipe_resources_cur;
1606
1607 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1608 IF (l_dml_type = 'I') THEN
1609 INSERT INTO GMD_RECIPE_ORGN_RESOURCES (
1610 RECIPE_ID ,
1611 ORGANIZATION_ID ,
1612 ROUTINGSTEP_ID ,
1613 OPRN_LINE_ID ,
1614 RESOURCES ,
1615 MIN_CAPACITY ,
1616 MAX_CAPACITY ,
1617 -- CAPACITY_UOM ,
1618 PROCESS_PARAMETER_5 ,
1619 PROCESS_PARAMETER_4 ,
1620 PROCESS_PARAMETER_3 ,
1621 PROCESS_PARAMETER_2 ,
1622 PROCESS_PARAMETER_1 ,
1623 PROCESS_UM ,
1624 USAGE_UOM ,
1625 RESOURCE_USAGE ,
1626 PROCESS_QTY ,
1627 CREATED_BY ,
1628 CREATION_DATE ,
1629 LAST_UPDATE_DATE ,
1630 LAST_UPDATE_LOGIN ,
1631 TEXT_CODE ,
1632 LAST_UPDATED_BY ,
1633 ATTRIBUTE1 ,
1634 ATTRIBUTE2 ,
1635 ATTRIBUTE3 ,
1636 ATTRIBUTE4 ,
1637 ATTRIBUTE5 ,
1638 ATTRIBUTE6 ,
1639 ATTRIBUTE7 ,
1640 ATTRIBUTE8 ,
1641 ATTRIBUTE9 ,
1642 ATTRIBUTE10 ,
1643 ATTRIBUTE11 ,
1644 ATTRIBUTE12 ,
1645 ATTRIBUTE13 ,
1646 ATTRIBUTE14 ,
1647 ATTRIBUTE15 ,
1648 ATTRIBUTE16 ,
1649 ATTRIBUTE17 ,
1650 ATTRIBUTE18 ,
1651 ATTRIBUTE19 ,
1652 ATTRIBUTE20 ,
1653 ATTRIBUTE21 ,
1654 ATTRIBUTE22 ,
1655 ATTRIBUTE23 ,
1656 ATTRIBUTE24 ,
1657 ATTRIBUTE25 ,
1658 ATTRIBUTE26 ,
1659 ATTRIBUTE27 ,
1660 ATTRIBUTE28 ,
1661 ATTRIBUTE29 ,
1662 ATTRIBUTE30 ,
1663 ATTRIBUTE_CATEGORY )
1664 VALUES (
1665 p_recipe_detail_rec.recipe_id ,
1666 p_recipe_detail_rec.organization_id ,
1667 p_recipe_detail_rec.ROUTINGSTEP_ID ,
1668 p_recipe_detail_rec.OPRN_LINE_ID ,
1669 p_recipe_detail_rec.RESOURCES ,
1670 p_recipe_detail_rec.MIN_CAPACITY ,
1671 p_recipe_detail_rec.MAX_CAPACITY ,
1672 -- p_recipe_detail_rec.CAPACITY_UOM ,
1673 p_recipe_detail_rec.PROCESS_PARAMETER_5 ,
1674 p_recipe_detail_rec.PROCESS_PARAMETER_4 ,
1675 p_recipe_detail_rec.PROCESS_PARAMETER_3 ,
1676 p_recipe_detail_rec.PROCESS_PARAMETER_2 ,
1677 p_recipe_detail_rec.PROCESS_PARAMETER_1 ,
1678 p_recipe_detail_rec.PROCESS_UM ,
1679 p_recipe_detail_rec.USAGE_UOM ,
1680 p_recipe_detail_rec.RESOURCE_USAGE ,
1681 p_recipe_detail_rec.PROCESS_QTY ,
1682 NVL(p_recipe_detail_rec.CREATED_BY , gmd_api_grp.user_id) ,
1683 NVL(p_recipe_detail_rec.CREATION_DATE, sysdate) ,
1684 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE, sysdate) ,
1685 NVL(p_recipe_detail_rec.LAST_UPDATE_LOGIN , gmd_api_grp.login_id) ,
1686 p_recipe_detail_rec.TEXT_CODE ,
1687 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1688 p_flex_insert_rec.ATTRIBUTE1 ,
1689 p_flex_insert_rec.ATTRIBUTE2 ,
1690 p_flex_insert_rec.ATTRIBUTE3 ,
1691 p_flex_insert_rec.ATTRIBUTE4 ,
1692 p_flex_insert_rec.ATTRIBUTE5 ,
1693 p_flex_insert_rec.ATTRIBUTE6 ,
1694 p_flex_insert_rec.ATTRIBUTE7 ,
1695 p_flex_insert_rec.ATTRIBUTE8 ,
1696 p_flex_insert_rec.ATTRIBUTE9 ,
1697 p_flex_insert_rec.ATTRIBUTE10 ,
1698 p_flex_insert_rec.ATTRIBUTE11 ,
1699 p_flex_insert_rec.ATTRIBUTE12 ,
1700 p_flex_insert_rec.ATTRIBUTE13 ,
1701 p_flex_insert_rec.ATTRIBUTE14 ,
1702 p_flex_insert_rec.ATTRIBUTE15 ,
1703 p_flex_insert_rec.ATTRIBUTE16 ,
1704 p_flex_insert_rec.ATTRIBUTE17 ,
1705 p_flex_insert_rec.ATTRIBUTE18 ,
1706 p_flex_insert_rec.ATTRIBUTE19 ,
1707 p_flex_insert_rec.ATTRIBUTE20 ,
1708 p_flex_insert_rec.ATTRIBUTE21 ,
1709 p_flex_insert_rec.ATTRIBUTE22 ,
1710 p_flex_insert_rec.ATTRIBUTE23 ,
1711 p_flex_insert_rec.ATTRIBUTE24 ,
1712 p_flex_insert_rec.ATTRIBUTE25 ,
1713 p_flex_insert_rec.ATTRIBUTE26 ,
1714 p_flex_insert_rec.ATTRIBUTE27 ,
1715 p_flex_insert_rec.ATTRIBUTE28 ,
1716 p_flex_insert_rec.ATTRIBUTE29 ,
1717 p_flex_insert_rec.ATTRIBUTE30 ,
1718 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1719 END IF; /* end of dml type */
1720
1721 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1722 IF (l_dml_type = 'U') THEN
1723 UPDATE GMD_RECIPE_ORGN_RESOURCES
1724 SET MIN_CAPACITY = p_recipe_detail_rec.min_capacity,
1725 MAX_CAPACITY = p_recipe_detail_rec.max_capacity,
1726 PROCESS_PARAMETER_5 = p_recipe_detail_rec.PROCESS_PARAMETER_5,
1727 PROCESS_PARAMETER_4 = p_recipe_detail_rec.PROCESS_PARAMETER_4,
1728 PROCESS_PARAMETER_3 = p_recipe_detail_rec.PROCESS_PARAMETER_3,
1729 PROCESS_PARAMETER_2 = p_recipe_detail_rec.PROCESS_PARAMETER_2,
1730 PROCESS_PARAMETER_1 = p_recipe_detail_rec.PROCESS_PARAMETER_1,
1731 PROCESS_UM = p_recipe_detail_rec.PROCESS_UM ,
1732 USAGE_UOM = p_recipe_detail_rec.USAGE_UOM ,
1733 RESOURCE_USAGE = p_recipe_detail_rec.RESOURCE_USAGE,
1734 PROCESS_QTY = p_recipe_detail_rec.PROCESS_QTY ,
1735 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date, sysdate),
1736 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1737 TEXT_CODE = p_recipe_detail_rec.text_code,
1738 LAST_UPDATED_BY = NVL(p_recipe_detail_rec.last_updated_by, gmd_api_grp.user_id) ,
1739 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1740 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1741 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1742 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1743 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1744 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1745 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1746 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1747 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1748 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1749 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1750 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1751 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1752 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1753 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1754 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1755 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1756 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1757 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1758 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1759 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1760 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1761 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1762 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1763 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1764 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1765 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1766 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1767 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1768 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1769 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1770 WHERE rowid = l_rowid;
1771
1772 END IF; /* ends dml type */
1773 EXCEPTION
1774 WHEN FND_API.G_EXC_ERROR THEN
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776 WHEN OTHERS THEN
1777 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1779 END RECIPE_ORGN_RESOURCES;
1780
1781 END GMD_RECIPE_DETAIL_PVT; /* Package end */