[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_DETAIL_PVT
Source
1 PACKAGE BODY GMD_RECIPE_DETAIL_PVT AS
2 /* $Header: GMDVRCDB.pls 120.12 2010/10/29 14:11:10 rborpatl 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 --Bug 10109808 : Adding Additional parameters in Insert Statement Starts
486 ,MINIMUM_TRANSFER_QTY
487 ,MINIMUM_DELAY
488 ,MAXIMUM_DELAY
489 --Bug 10109808 : Adding Additional parameters in Insert Statement Ends
490 ,ATTRIBUTE_CATEGORY
491 ,ATTRIBUTE1
492 ,ATTRIBUTE2
493 ,ATTRIBUTE3
494 ,ATTRIBUTE4
495 ,ATTRIBUTE5
496 ,ATTRIBUTE6
497 ,ATTRIBUTE7
498 ,ATTRIBUTE8
499 ,ATTRIBUTE9
500 ,ATTRIBUTE10
501 ,ATTRIBUTE11
502 ,ATTRIBUTE12
503 ,ATTRIBUTE13
504 ,ATTRIBUTE14
505 ,ATTRIBUTE15
506 ,ATTRIBUTE16
507 ,ATTRIBUTE17
508 ,ATTRIBUTE18
509 ,ATTRIBUTE19
510 ,ATTRIBUTE20
511 ,ATTRIBUTE21
512 ,ATTRIBUTE22
513 ,ATTRIBUTE23
514 ,ATTRIBUTE24
515 ,ATTRIBUTE25
516 ,ATTRIBUTE26
517 ,ATTRIBUTE27
518 ,ATTRIBUTE28
519 ,ATTRIBUTE29
520 ,ATTRIBUTE30)
521 VALUES ( p_recipe_mtl_rec.recipe_id
522 ,p_recipe_mtl_rec.ROUTINGSTEP_ID
523 ,p_recipe_mtl_rec.FORMULALINE_ID
524 ,p_recipe_mtl_rec.text_code
525 ,NVL(p_recipe_mtl_rec.creation_date,SYSDATE)
526 ,NVL(p_recipe_mtl_rec.created_by,gmd_Api_grp.user_id)
527 ,NVL(p_recipe_mtl_rec.last_updated_by,gmd_api_grp.user_id)
528 ,NVL(p_recipe_mtl_rec.last_update_date,sysdate)
529 ,NVL(p_recipe_mtl_rec.last_update_login,gmd_Api_grp.login_id)
530 --Bug 10109808 : Adding Additional parameters in Insert Statement Starts
531 ,p_recipe_mtl_rec.MINIMUM_TRANSFER_QTY
532 ,p_recipe_mtl_rec.MINIMUM_DELAY
533 ,p_recipe_mtl_rec.MAXIMUM_DELAY
534 --Bug 10109808 : Adding Additional parameters in Insert Statement Ends
535 ,p_recipe_mtl_flex_rec.ATTRIBUTE_CATEGORY
536 ,p_recipe_mtl_flex_rec.ATTRIBUTE1
537 ,p_recipe_mtl_flex_rec.ATTRIBUTE2
538 ,p_recipe_mtl_flex_rec.ATTRIBUTE3
539 ,p_recipe_mtl_flex_rec.ATTRIBUTE4
540 ,p_recipe_mtl_flex_rec.ATTRIBUTE5
541 ,p_recipe_mtl_flex_rec.ATTRIBUTE6
542 ,p_recipe_mtl_flex_rec.ATTRIBUTE7
543 ,p_recipe_mtl_flex_rec.ATTRIBUTE8
544 ,p_recipe_mtl_flex_rec.ATTRIBUTE9
545 ,p_recipe_mtl_flex_rec.ATTRIBUTE10
546 ,p_recipe_mtl_flex_rec.ATTRIBUTE11
547 ,p_recipe_mtl_flex_rec.ATTRIBUTE12
548 ,p_recipe_mtl_flex_rec.ATTRIBUTE13
549 ,p_recipe_mtl_flex_rec.ATTRIBUTE14
550 ,p_recipe_mtl_flex_rec.ATTRIBUTE15
551 ,p_recipe_mtl_flex_rec.ATTRIBUTE16
552 ,p_recipe_mtl_flex_rec.ATTRIBUTE17
553 ,p_recipe_mtl_flex_rec.ATTRIBUTE18
554 ,p_recipe_mtl_flex_rec.ATTRIBUTE19
555 ,p_recipe_mtl_flex_rec.ATTRIBUTE20
556 ,p_recipe_mtl_flex_rec.ATTRIBUTE21
557 ,p_recipe_mtl_flex_rec.ATTRIBUTE22
558 ,p_recipe_mtl_flex_rec.ATTRIBUTE23
559 ,p_recipe_mtl_flex_rec.ATTRIBUTE24
560 ,p_recipe_mtl_flex_rec.ATTRIBUTE25
561 ,p_recipe_mtl_flex_rec.ATTRIBUTE26
562 ,p_recipe_mtl_flex_rec.ATTRIBUTE27
563 ,p_recipe_mtl_flex_rec.ATTRIBUTE28
564 ,p_recipe_mtl_flex_rec.ATTRIBUTE29
565 ,p_recipe_mtl_flex_rec.ATTRIBUTE30);
566 EXCEPTION
567 WHEN FND_API.G_EXC_ERROR THEN
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 WHEN OTHERS THEN
570 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
572 END CREATE_RECIPE_MTL;
573
574 /* ================================================== */
575 /* Procedure: */
576 /* Update_Recipe_Process_Loss */
577 /* */
578 /* DESCRIPTION: */
579 /* This PL/SQL procedure is responsible for */
580 /* updating recipe process loss */
581 /* */
582 /* ================================================== */
583 /* Start of commments */
584 /* API name : Update_Recipe_Process_loss */
585 /* Type : Private */
586 /* Procedure : */
587 /* End of comments */
588
589 PROCEDURE UPDATE_RECIPE_PROCESS_LOSS
590 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
591 x_return_status OUT NOCOPY VARCHAR2
592 ) IS
593 /* Defining all local variables */
594 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_PROCESS_LOSS';
595
596 l_rowid VARCHAR2(32);
597 l_plant_ind NUMBER;
598 l_out_rec gmd_parameters_dtl_pkg.parameter_rec_type;
599
600 l_recipe_process_loss_id NUMBER := 0;
601
602 /* Variables used for defining status */
603 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
604 l_return_code NUMBER := 0;
605
606 /* Error message count and data */
607 l_msg_count NUMBER;
608 l_msg_data VARCHAR2(2000);
609 l_return_stat VARCHAR2(10);
610 BEGIN
611 /* Initialization of all status */
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 /* ==================================== */
615 /* Validate orgn code if it has been */
616 /* provided */
617 /* ==================================== */
618
619 IF (p_recipe_detail_rec.organization_id IS NOT NULL) THEN
620 gmd_api_grp.fetch_parm_values ( P_orgn_id => p_recipe_detail_rec.organization_id,
621 X_out_rec => l_out_rec,
622 X_return_status => l_return_stat);
623
624 /*IF (l_out_rec.plant_ind <> 1 OR l_out_rec.lab_ind <> 1) THEN
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ORGN_INVALID');
627 FND_MSG_PUB.ADD;
628 END IF;*/
629 END IF;
630
631 /* Update into the recipe process loss table */
632 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
633 UPDATE gmd_recipe_process_loss
634 SET recipe_id = p_recipe_detail_rec.recipe_id ,
635 organization_id = p_recipe_detail_rec.organization_id,
636 text_code = p_recipe_detail_rec.text_code,
637 process_loss = p_recipe_detail_rec.process_loss,
638 contiguous_ind = NVL(p_recipe_detail_rec.contiguous_ind,0),
639 last_updated_by = NVL(p_recipe_detail_rec.last_updated_by,
640 gmd_api_grp.user_id),
641 last_update_date = NVL(p_recipe_detail_rec.last_update_date,
642 sysdate),
643 last_update_login = NVL(p_recipe_detail_rec.last_update_login,
644 gmd_api_grp.login_id),
645 fixed_process_loss = p_recipe_detail_rec.fixed_process_loss, /* 6811759 */
646 fixed_process_loss_uom = p_recipe_detail_rec.fixed_process_loss_uom
647 WHERE recipe_process_loss_id = p_recipe_detail_rec.recipe_process_loss_id;
648 END IF;
649
650 EXCEPTION
651 WHEN FND_API.G_EXC_ERROR THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 WHEN OTHERS THEN
654 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
656 END UPDATE_RECIPE_PROCESS_LOSS;
657
658 /* ================================================== */
659 /* Procedure: */
660 /* Update_Recipe_Customers */
661 /* */
662 /* DESCRIPTION: */
663 /* This PL/SQL procedure is responsible for */
664 /* updating recipe process loss */
665 /* */
666 /* ================================================== */
667 /* Start of commments */
668 /* API name : Update_Recipe_Customers */
669 /* Type : Private */
670 /* Procedure : */
671 /* End of comments */
672
673 PROCEDURE UPDATE_RECIPE_CUSTOMERS
674 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl,
675 x_return_status OUT NOCOPY VARCHAR2
676 ) IS
677 /* Defining all local variables */
678 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_CUSTOMERS';
679
680 /* Variables used for defining status */
681 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
682 l_return_code NUMBER := 0;
683
684 /* Error message count and data */
685 l_msg_count NUMBER;
686 l_msg_data VARCHAR2(2000);
687
688 BEGIN
689 /* Initialization of all status */
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691
692 /* Update the recipe customer table */
693 /* only who columns needs to be updated */
694 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
695 UPDATE GMD_RECIPE_CUSTOMERS
696 SET text_code = p_recipe_detail_rec.text_code,
697 last_updated_by = NVL(p_recipe_detail_rec.last_updated_by,
698 gmd_api_grp.user_id),
699 last_update_date = NVL(p_recipe_detail_rec.last_update_date,
700 sysdate),
701 last_update_login = NVL(p_recipe_detail_rec.last_update_login,
702 gmd_api_grp.login_id)
703 WHERE recipe_id = p_recipe_detail_rec.recipe_id AND
704 customer_id = p_recipe_detail_rec.customer_id;
705 END IF;
706 EXCEPTION
707 WHEN FND_API.G_EXC_ERROR THEN
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 WHEN OTHERS THEN
710 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712 END UPDATE_RECIPE_CUSTOMERS;
713
714 /* ================================================== */
715 /* Procedure: */
716 /* Update_Recipe_VR */
717 /* */
718 /* DESCRIPTION: */
719 /* This PL/SQL procedure is responsible for */
720 /* updating recipe Validity Rules */
721 /* */
722 /* ================================================= */
723 /* Start of commments */
724 /* API name : Update_Recipe_VR */
725 /* Type : Private */
726 /* Procedure : */
727 /* End of comments */
728 PROCEDURE UPDATE_RECIPE_VR
729 (p_recipe_vr_rec IN GMD_RECIPE_DETAIL.recipe_vr ,
730 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
731 x_return_status OUT NOCOPY VARCHAR2
732 ) IS
733
734 /* Define all variables specific to this procedure */
735 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RECIPE_VR';
736 l_vr_db_rec gmd_recipe_validity_rules%ROWTYPE;
737
738 p_vr_update_tbl GMD_VALIDITY_RULES_PVT.update_tbl_type;
739 l_row_cnt NUMBER := 1;
740
741 l_msg_cnt NUMBER;
742 l_msg_list VARCHAR2(2000);
743
744 Cursor get_db_vr_rec(pVR_id NUMBER) IS
745 Select *
746 From gmd_recipe_validity_rules
747 Where recipe_validity_rule_id = pVR_id;
748
749 BEGIN
750 /* Initialization of all status */
751 x_return_status := FND_API.G_RET_STS_SUCCESS;
752
753 OPEN get_db_vr_rec(p_recipe_vr_rec.recipe_validity_rule_id);
754 FETCH get_db_vr_rec INTO l_vr_db_rec;
755 CLOSE get_db_vr_rec;
756
757 /* setting up the table type p_vr_update_tbl */
758 /* Populate p_vr_table only when the values are different from the one in db */
759 IF ((l_vr_db_rec.orgn_code IS NULL) AND (p_recipe_vr_rec.orgn_code IS NOT NULL)) THEN
760 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
761 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.orgn_code;
762 l_row_cnt := l_row_cnt + 1;
763 ELSIF ((l_vr_db_rec.orgn_code IS NOT NULL) AND (p_recipe_vr_rec.orgn_code IS NULL)) THEN
764 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
765 p_vr_update_tbl(l_row_cnt).p_value := Null;
766 l_row_cnt := l_row_cnt + 1;
767 ELSIF (l_vr_db_rec.ORGN_CODE <> p_recipe_vr_rec.orgn_code) THEN
768 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGN_CODE';
769 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.orgn_code;
770 l_row_cnt := l_row_cnt + 1;
771 END IF;
772
773 -- NPD Conv. Added the below for organization_id and revision columns.
774 IF ((l_vr_db_rec.organization_id IS NULL) AND (p_recipe_vr_rec.organization_id IS NOT NULL)) THEN
775 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
776 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.organization_id;
777 l_row_cnt := l_row_cnt + 1;
778 ELSIF ((l_vr_db_rec.organization_id IS NOT NULL) AND (p_recipe_vr_rec.organization_id IS NULL)) THEN
779 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
780 p_vr_update_tbl(l_row_cnt).p_value := Null;
781 l_row_cnt := l_row_cnt + 1;
782 ELSIF (l_vr_db_rec.organization_id <> p_recipe_vr_rec.organization_id) THEN
783 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ORGANIZATION_ID';
784 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.organization_id;
785 l_row_cnt := l_row_cnt + 1;
786 END IF;
787
788 IF ((l_vr_db_rec.revision IS NULL) AND (p_recipe_vr_rec.revision IS NOT NULL)) THEN
789 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
790 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.revision;
791 l_row_cnt := l_row_cnt + 1;
792 ELSIF ((l_vr_db_rec.revision IS NOT NULL) AND (p_recipe_vr_rec.revision IS NULL)) THEN
793 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
794 p_vr_update_tbl(l_row_cnt).p_value := Null;
795 l_row_cnt := l_row_cnt + 1;
796 ELSIF (l_vr_db_rec.revision <> p_recipe_vr_rec.revision) THEN
797 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'REVISION';
798 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.revision;
799 l_row_cnt := l_row_cnt + 1;
800 END IF;
801 -- End NPD Conv.
802
803 IF ((l_vr_db_rec.planned_process_loss IS NULL)
804 AND (p_recipe_vr_rec.planned_process_loss IS NOT NULL)) THEN
805 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
806 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.planned_process_loss;
807 l_row_cnt := l_row_cnt + 1;
808 ELSIF ((l_vr_db_rec.planned_process_loss IS NOT NULL)
809 AND (p_recipe_vr_rec.planned_process_loss IS NULL)) THEN
810 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
811 p_vr_update_tbl(l_row_cnt).p_value := Null;
812 l_row_cnt := l_row_cnt + 1;
813 ELSIF (l_vr_db_rec.planned_process_loss <> p_recipe_vr_rec.planned_process_loss) THEN
814 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PLANNED_PROCESS_LOSS';
815 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.planned_process_loss;
816 l_row_cnt := l_row_cnt + 1;
817 END IF;
818
819 --RLNAGARA Start Bug6997624 Added code to update Fixed Process Loss and UOM
820 IF ((l_vr_db_rec.fixed_process_loss IS NULL)
821 AND (p_recipe_vr_rec.fixed_process_loss IS NOT NULL)) THEN
822 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
823 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss;
824 l_row_cnt := l_row_cnt + 1;
825 ELSIF ((l_vr_db_rec.fixed_process_loss IS NOT NULL)
826 AND (p_recipe_vr_rec.fixed_process_loss IS NULL)) THEN
827 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
828 p_vr_update_tbl(l_row_cnt).p_value := Null;
829 l_row_cnt := l_row_cnt + 1;
830 ELSIF (l_vr_db_rec.fixed_process_loss <> p_recipe_vr_rec.fixed_process_loss) THEN
831 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS';
832 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss;
833 l_row_cnt := l_row_cnt + 1;
834 END IF;
835
836 IF (l_vr_db_rec.FIXED_PROCESS_LOSS_UOM <> p_recipe_vr_rec.FIXED_PROCESS_LOSS_UOM) THEN
837 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'FIXED_PROCESS_LOSS_UOM';
838 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.fixed_process_loss_uom;
839 l_row_cnt := l_row_cnt + 1;
840 END IF;
841
842 --RLNAGARA Bug6997624 End
843
844 -- NPD Conv. Replaced item_id with inventory_item_id
845 IF (l_vr_db_rec.INVENTORY_ITEM_ID <> p_recipe_vr_rec.INVENTORY_ITEM_ID) THEN
846 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INVENTORY_ITEM_ID';
847 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INVENTORY_ITEM_ID;
848 l_row_cnt := l_row_cnt + 1;
849 END IF;
850
851 IF (l_vr_db_rec.RECIPE_USE <> p_recipe_vr_rec.RECIPE_USE) THEN
852 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'RECIPE_USE';
853 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.RECIPE_USE;
854 l_row_cnt := l_row_cnt + 1;
855 END IF;
856
857 IF (l_vr_db_rec.PREFERENCE <> p_recipe_vr_rec.PREFERENCE) THEN
858 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'PREFERENCE';
859 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.PREFERENCE;
860 l_row_cnt := l_row_cnt + 1;
861 END IF;
862
863 --Bug 3797002, kkillams
864 --FND_DATE.DATE_TO_CANONICAL function is added to conver the date into character value.
865 IF (l_vr_db_rec.START_DATE <> p_recipe_vr_rec.START_DATE) THEN
866 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'START_DATE';
867 p_vr_update_tbl(l_row_cnt).p_value :=FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.START_DATE);
868 l_row_cnt := l_row_cnt + 1;
869 END IF;
870
871 IF ((l_vr_db_rec.END_DATE IS NULL) AND (p_recipe_vr_rec.END_DATE IS NOT NULL)) THEN
872 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
873 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
874 l_row_cnt := l_row_cnt + 1;
875 ELSIF ((l_vr_db_rec.END_DATE IS NOT NULL) AND (p_recipe_vr_rec.END_DATE IS NULL)) THEN
876 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
877 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
878 l_row_cnt := l_row_cnt + 1;
879 ELSIF (l_vr_db_rec.END_DATE <> p_recipe_vr_rec.END_DATE) THEN
880 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'END_DATE';
881 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(p_recipe_vr_rec.END_DATE);
882 l_row_cnt := l_row_cnt + 1;
883 END IF;
884
885 IF ( l_vr_db_rec.MIN_QTY <> p_recipe_vr_rec.MIN_QTY) THEN
886 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'MIN_QTY';
887 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.MIN_QTY;
888 l_row_cnt := l_row_cnt + 1;
889 END IF;
890
891 IF (l_vr_db_rec.MAX_QTY <> p_recipe_vr_rec.MAX_QTY ) THEN
892 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'MAX_QTY';
893 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.MAX_QTY;
894 l_row_cnt := l_row_cnt + 1;
895 END IF;
896
897 IF (l_vr_db_rec.STD_QTY <> p_recipe_vr_rec.STD_QTY) THEN
898 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'STD_QTY';
899 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.STD_QTY;
900 l_row_cnt := l_row_cnt + 1;
901 END IF;
902
903 IF (l_vr_db_rec.DETAIL_UOM <> p_recipe_vr_rec.DETAIL_UOM) THEN
904 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'DETAIL_UOM';
905 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.DETAIL_UOM;
906 l_row_cnt := l_row_cnt + 1;
907 END IF;
908
909 IF (l_vr_db_rec.INV_MIN_QTY <> p_recipe_vr_rec.INV_MIN_QTY) THEN
910 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INV_MIN_QTY';
911 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INV_MIN_QTY;
912 l_row_cnt := l_row_cnt + 1;
913 END IF;
914
915 IF (l_vr_db_rec.INV_MAX_QTY <> p_recipe_vr_rec.INV_MAX_QTY ) THEN
916 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'INV_MAX_QTY';
917 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.INV_MAX_QTY;
918 l_row_cnt := l_row_cnt + 1;
919 END IF;
920 -- KSHUKLA added NVL conditions for the 5079519
921 IF (NVL(l_vr_db_rec.TEXT_CODE, '-9999') <> NVL(p_recipe_vr_rec.TEXT_CODE, '-9999')) THEN
922 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'TEXT_CODE';
923 p_vr_update_tbl(l_row_cnt).p_value := p_recipe_vr_rec.TEXT_CODE;
924 l_row_cnt := l_row_cnt + 1;
925 END IF;
926
927 -- Bug# 4134275 Kapil M
928 -- To update WHO columns
929 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATED_BY';
930 p_vr_update_tbl(l_row_cnt).p_value := NVL(p_recipe_vr_rec.last_updated_by, fnd_global.USER_ID);
931 l_row_cnt := l_row_cnt + 1;
932
933 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATE_DATE';
934 p_vr_update_tbl(l_row_cnt).p_value := FND_DATE.DATE_TO_CANONICAL(NVL(p_recipe_vr_rec.last_update_date,SYSDATE));
935 l_row_cnt := l_row_cnt + 1;
936
937 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'LAST_UPDATE_LOGIN';
938 p_vr_update_tbl(l_row_cnt).p_value := NVL(p_recipe_vr_rec.last_update_login, gmd_api_grp.login_id);
939 l_row_cnt := l_row_cnt + 1;
940
941 -- 4134275
942 -- KSHUKLA added NVL conditions for the 5079519
943 IF (NVL(l_vr_db_rec.attribute1, '-9999') <> NVL(p_flex_update_rec.attribute1, '-9999')) THEN
944 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE1';
945 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute1;
946 l_row_cnt := l_row_cnt + 1;
947 END IF;
948
949 IF (NVL(l_vr_db_rec.attribute2 , '-9999') <> NVL(p_flex_update_rec.attribute2, '-9999')) THEN
950 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE2';
951 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute2;
952 l_row_cnt := l_row_cnt + 1;
953 END IF;
954
955 IF (NVL(l_vr_db_rec.attribute3, '-9999') <> NVL(p_flex_update_rec.attribute3, '-9999')) THEN
956 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE3';
957 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute3;
958 l_row_cnt := l_row_cnt + 1;
959 END IF;
960
961 IF (NVL(l_vr_db_rec.attribute4, '-9999') <> NVL(p_flex_update_rec.attribute4, '-9999')) THEN
962 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE4';
963 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute4;
964 l_row_cnt := l_row_cnt + 1;
965 END IF;
966
967 IF (NVL(l_vr_db_rec.attribute5 , '-9999') <> NVL(p_flex_update_rec.attribute5, '-9999')) THEN
968 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE5';
969 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute5;
970 l_row_cnt := l_row_cnt + 1;
971 END IF;
972
973 IF (NVL(l_vr_db_rec.attribute6, '-9999') <> NVL(p_flex_update_rec.attribute6, '-9999')) THEN
974 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE6';
975 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute6;
976 l_row_cnt := l_row_cnt + 1;
977 END IF;
978
979 IF (NVL(l_vr_db_rec.attribute7, '-9999') <> NVL(p_flex_update_rec.attribute7, '-9999')) THEN
980 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE7';
981 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute7;
982 l_row_cnt := l_row_cnt + 1;
983 END IF;
984
985 IF (NVL(l_vr_db_rec.attribute8 , '-9999') <> NVL(p_flex_update_rec.attribute8, '-9999')) THEN
986 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE8';
987 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute8;
988 l_row_cnt := l_row_cnt + 1;
989 END IF;
990
991 IF (NVL(l_vr_db_rec.attribute9 , '-9999') <> NVL(p_flex_update_rec.attribute9, '-9999')) THEN
992 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE9';
993 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute9;
994 l_row_cnt := l_row_cnt + 1;
995 END IF;
996
997 IF (NVL(l_vr_db_rec.attribute10, '-9999') <> NVL(p_flex_update_rec.attribute10, '-9999')) THEN
998 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE10';
999 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute10;
1000 l_row_cnt := l_row_cnt + 1;
1001 END IF;
1002
1003 IF (NVL(l_vr_db_rec.attribute11, '-9999') <> NVL(p_flex_update_rec.attribute11, '-9999')) THEN
1004 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE11';
1005 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute11;
1006 l_row_cnt := l_row_cnt + 1;
1007 END IF;
1008
1009 IF (NVL(l_vr_db_rec.attribute12, '-9999') <> NVL(p_flex_update_rec.attribute12, '-9999')) THEN
1010 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE12';
1011 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute12;
1012 l_row_cnt := l_row_cnt + 1;
1013 END IF;
1014
1015 IF (NVL(l_vr_db_rec.attribute13, '-9999') <> NVL(p_flex_update_rec.attribute13, '-9999')) THEN
1016 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE13';
1017 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute13;
1018 l_row_cnt := l_row_cnt + 1;
1019 END IF;
1020
1021 IF (NVL(l_vr_db_rec.attribute14 , '-9999') <> NVL(p_flex_update_rec.attribute14, '-9999')) THEN
1022 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE14';
1023 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute14;
1024 l_row_cnt := l_row_cnt + 1;
1025 END IF;
1026
1027 IF (NVL(l_vr_db_rec.attribute15, '-9999') <> NVL(p_flex_update_rec.attribute15, '-9999')) THEN
1028 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE15';
1029 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute15;
1030 l_row_cnt := l_row_cnt + 1;
1031 END IF;
1032
1033 IF (NVL(l_vr_db_rec.attribute16, '-9999') <> NVL(p_flex_update_rec.attribute16, '-9999')) THEN
1034 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE16';
1035 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute16;
1036 l_row_cnt := l_row_cnt + 1;
1037 END IF;
1038
1039 IF (NVL(l_vr_db_rec.attribute17 , '-9999') <> NVL(p_flex_update_rec.attribute17, '-9999')) THEN
1040 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE17';
1041 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute17;
1042 l_row_cnt := l_row_cnt + 1;
1043 END IF;
1044
1045 IF (NVL(l_vr_db_rec.attribute18 , '-9999') <> NVL(p_flex_update_rec.attribute18, '-9999')) THEN
1046 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE18';
1047 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute18;
1048 l_row_cnt := l_row_cnt + 1;
1049 END IF;
1050
1051 IF (NVL(l_vr_db_rec.attribute19, '-9999') <> NVL(p_flex_update_rec.attribute19, '-9999')) THEN
1052 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE19';
1053 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute19;
1054 l_row_cnt := l_row_cnt + 1;
1055 END IF;
1056
1057 IF (NVL(l_vr_db_rec.attribute20 , '-9999') <> NVL(p_flex_update_rec.attribute20, '-9999')) THEN
1058 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE20';
1059 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute20;
1060 l_row_cnt := l_row_cnt + 1;
1061 END IF;
1062
1063
1064 IF (NVL(l_vr_db_rec.attribute21, '-9999') <> NVL(p_flex_update_rec.attribute21, '-9999')) THEN
1065 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE21';
1066 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute21;
1067 l_row_cnt := l_row_cnt + 1;
1068 END IF;
1069
1070 IF (NVL(l_vr_db_rec.attribute22 , '-9999') <> NVL(p_flex_update_rec.attribute22, '-9999')) THEN
1071 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE22';
1072 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute22;
1073 l_row_cnt := l_row_cnt + 1;
1074 END IF;
1075
1076 IF (NVL(l_vr_db_rec.attribute23, '-9999') <> NVL(p_flex_update_rec.attribute23, '-9999')) THEN
1077 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE23';
1078 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute23;
1079 l_row_cnt := l_row_cnt + 1;
1080 END IF;
1081
1082 IF (NVL(l_vr_db_rec.attribute24, '-9999') <> NVL(p_flex_update_rec.attribute24, '-9999')) THEN
1083 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE24';
1084 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute24;
1085 l_row_cnt := l_row_cnt + 1;
1086 END IF;
1087
1088 IF (NVL(l_vr_db_rec.attribute25, '-9999') <> NVL(p_flex_update_rec.attribute25, '-9999')) THEN
1089 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE25';
1090 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute25;
1091 l_row_cnt := l_row_cnt + 1;
1092 END IF;
1093
1094 IF (NVL(l_vr_db_rec.attribute26, '-9999') <> NVL(p_flex_update_rec.attribute26, '-9999')) THEN
1095 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE26';
1096 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute26;
1097 l_row_cnt := l_row_cnt + 1;
1098 END IF;
1099
1100 IF (NVL(l_vr_db_rec.attribute27, '-9999') <> NVL(p_flex_update_rec.attribute27, '-9999')) THEN
1101 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE27';
1102 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute27;
1103 l_row_cnt := l_row_cnt + 1;
1104 END IF;
1105
1106 IF (NVL(l_vr_db_rec.attribute28, '-9999') <> NVL(p_flex_update_rec.attribute28, '-9999')) THEN
1107 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE28';
1108 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute28;
1109 l_row_cnt := l_row_cnt + 1;
1110 END IF;
1111
1112 IF (NVL(l_vr_db_rec.attribute29, '-9999') <> NVL(p_flex_update_rec.attribute29, '-9999')) THEN
1113 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE29';
1114 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute29;
1115 l_row_cnt := l_row_cnt + 1;
1116 END IF;
1117
1118 IF (NVL(l_vr_db_rec.attribute30 , '-9999') <> NVL(p_flex_update_rec.attribute30, '-9999')) THEN
1119 p_vr_update_tbl(l_row_cnt).p_col_to_update := 'ATTRIBUTE30';
1120 p_vr_update_tbl(l_row_cnt).p_value := p_flex_update_rec.attribute30;
1121 l_row_cnt := l_row_cnt + 1;
1122 END IF;
1123
1124
1125 IF p_vr_update_tbl.count > 0 THEN
1126 /* update recipe validity rules table */
1127 GMD_VALIDITY_RULES_PVT.update_validity_rules
1128 ( p_validity_rule_id => p_recipe_vr_rec.recipe_validity_rule_id
1129 , p_update_table => p_vr_update_tbl
1130 , x_message_count => l_msg_cnt
1131 , x_message_list => l_msg_list
1132 , x_return_status => x_return_status
1133 );
1134 END IF;
1135
1136 /* Get the messgae list and count generated by this API */
1137 fnd_msg_pub.count_and_get (
1138 p_count => l_msg_cnt
1139 ,p_encoded => FND_API.g_false
1140 ,p_data => l_msg_list);
1141
1142 EXCEPTION
1143 WHEN FND_API.G_EXC_ERROR THEN
1144 x_return_status := FND_API.G_RET_STS_ERROR;
1145 WHEN OTHERS THEN
1146 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1148 END UPDATE_RECIPE_VR;
1149
1150 /* ===================================================*/
1151 /* Procedure: */
1152 /* Recipe_Routing_Steps */
1153 /* */
1154 /* DESCRIPTION: */
1155 /* This PL/SQL procedure is responsible for */
1156 /* inserting and updating recipe Routing steps */
1157 /* */
1158 /* ===================================================*/
1159 /* Start of commments */
1160 /* API name : Recipe_Routing_Steps */
1161 /* Type : Private */
1162 /* Procedure : */
1163 /* End of comments */
1164
1165 PROCEDURE RECIPE_ROUTING_STEPS
1166 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1167 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1168 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1169 x_return_status OUT NOCOPY VARCHAR2
1170 ) IS
1171 /* Define all variables specific to this procedure */
1172 l_dml_type VARCHAR2(1) := 'I';
1173 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ROUTING_STEPS';
1174
1175 l_rc_id NUMBER := 0;
1176 l_rcst_id NUMBER := 0;
1177
1178 /* Null values should not passed as a paramter */
1179 /* This cursor decides whether to insert or update. */
1180 CURSOR recipe_rout_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER) IS
1181 SELECT recipe_id, routingstep_id
1182 FROM gmd_recipe_routing_steps
1183 where recipe_id = NVL(vRecipe_id,-1) AND
1184 RoutingStep_id = NVL(vRoutingStep_id,-1);
1185 BEGIN
1186 /* Updating recipe routing step for first time is in fact inserting a new record */
1187 /* in gmd_recipe_routing_step table. [Form initially shows values from */
1188 /* fm_rout_dtl. When user "changes" values, they are saved in recipe table.] */
1189
1190 /* Initialization of status. */
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192
1193 OPEN recipe_rout_cur(p_recipe_detail_rec.recipe_id,
1194 p_recipe_detail_rec.routingstep_id);
1195 FETCH recipe_rout_cur INTO l_rc_id, l_rcst_id;
1196 IF (recipe_rout_cur%NOTFOUND) then
1197 l_dml_type := 'I';
1198 ELSE
1199 l_dml_type := 'U';
1200 END IF;
1201 CLOSE recipe_rout_cur;
1202
1203 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1204 IF (l_dml_type = 'I') THEN
1205 /* Assign flex fields */
1206 INSERT INTO GMD_RECIPE_ROUTING_STEPS (
1207 RECIPE_ID ,
1208 ROUTINGSTEP_ID ,
1209 STEP_QTY ,
1210 TEXT_CODE ,
1211 MASS_QTY ,
1212 MASS_STD_UOM ,
1213 VOLUME_QTY ,
1214 VOLUME_STD_UOM ,
1215 CREATED_BY ,
1216 CREATION_DATE ,
1217 LAST_UPDATE_DATE ,
1218 LAST_UPDATE_LOGIN ,
1219 LAST_UPDATED_BY ,
1220 ATTRIBUTE1 ,
1221 ATTRIBUTE2 ,
1222 ATTRIBUTE3 ,
1223 ATTRIBUTE4 ,
1224 ATTRIBUTE5 ,
1225 ATTRIBUTE6 ,
1226 ATTRIBUTE7 ,
1227 ATTRIBUTE8 ,
1228 ATTRIBUTE9 ,
1229 ATTRIBUTE10 ,
1230 ATTRIBUTE11 ,
1231 ATTRIBUTE12 ,
1232 ATTRIBUTE13 ,
1233 ATTRIBUTE14 ,
1234 ATTRIBUTE15 ,
1235 ATTRIBUTE16 ,
1236 ATTRIBUTE17 ,
1237 ATTRIBUTE18 ,
1238 ATTRIBUTE19 ,
1239 ATTRIBUTE20 ,
1240 ATTRIBUTE21 ,
1241 ATTRIBUTE22 ,
1242 ATTRIBUTE23 ,
1243 ATTRIBUTE24 ,
1244 ATTRIBUTE25 ,
1245 ATTRIBUTE26 ,
1246 ATTRIBUTE27 ,
1247 ATTRIBUTE28 ,
1248 ATTRIBUTE29 ,
1249 ATTRIBUTE30 ,
1250 ATTRIBUTE_CATEGORY )
1251 VALUES (
1252 p_recipe_detail_rec.recipe_id ,
1253 p_recipe_detail_rec.routingstep_id ,
1254 p_recipe_detail_rec.step_qty ,
1255 p_recipe_detail_rec.TEXT_CODE ,
1256 p_recipe_detail_rec.MASS_QTY ,
1257 p_recipe_detail_rec.MASS_STD_UOM ,
1258 p_recipe_detail_rec.VOLUME_QTY ,
1259 p_recipe_detail_rec.VOLUME_STD_UOM ,
1260 NVL(p_recipe_detail_rec.CREATED_BY, gmd_api_grp.user_id) ,
1261 NVL(p_recipe_detail_rec.CREATION_DATE,sysdate) ,
1262 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE,sysdate),
1263 NVL(p_recipe_detail_rec.LAST_UPDATE_LOGIN, gmd_api_grp.login_id),
1264 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1265 p_flex_insert_rec.ATTRIBUTE1 ,
1266 p_flex_insert_rec.ATTRIBUTE2 ,
1267 p_flex_insert_rec.ATTRIBUTE3 ,
1268 p_flex_insert_rec.ATTRIBUTE4 ,
1269 p_flex_insert_rec.ATTRIBUTE5 ,
1270 p_flex_insert_rec.ATTRIBUTE6 ,
1271 p_flex_insert_rec.ATTRIBUTE7 ,
1272 p_flex_insert_rec.ATTRIBUTE8 ,
1273 p_flex_insert_rec.ATTRIBUTE9 ,
1274 p_flex_insert_rec.ATTRIBUTE10 ,
1275 p_flex_insert_rec.ATTRIBUTE11 ,
1276 p_flex_insert_rec.ATTRIBUTE12 ,
1277 p_flex_insert_rec.ATTRIBUTE13 ,
1278 p_flex_insert_rec.ATTRIBUTE14 ,
1279 p_flex_insert_rec.ATTRIBUTE15 ,
1280 p_flex_insert_rec.ATTRIBUTE16 ,
1281 p_flex_insert_rec.ATTRIBUTE17 ,
1282 p_flex_insert_rec.ATTRIBUTE18 ,
1283 p_flex_insert_rec.ATTRIBUTE19 ,
1284 p_flex_insert_rec.ATTRIBUTE20 ,
1285 p_flex_insert_rec.ATTRIBUTE21 ,
1286 p_flex_insert_rec.ATTRIBUTE22 ,
1287 p_flex_insert_rec.ATTRIBUTE23 ,
1288 p_flex_insert_rec.ATTRIBUTE24 ,
1289 p_flex_insert_rec.ATTRIBUTE25 ,
1290 p_flex_insert_rec.ATTRIBUTE26 ,
1291 p_flex_insert_rec.ATTRIBUTE27 ,
1292 p_flex_insert_rec.ATTRIBUTE28 ,
1293 p_flex_insert_rec.ATTRIBUTE29 ,
1294 p_flex_insert_rec.ATTRIBUTE30 ,
1295 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1296
1297 END IF; /* end of dml type */
1298
1299 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1300 IF (l_dml_type = 'U') THEN
1301 UPDATE GMD_RECIPE_ROUTING_STEPS
1302 SET
1303 STEP_QTY = p_recipe_detail_rec.step_qty,
1304 MASS_QTY = p_recipe_detail_rec.mass_qty,
1305 MASS_STD_UOM = p_recipe_detail_rec.mass_std_uom,
1306 VOLUME_QTY = p_recipe_detail_rec.volume_qty,
1307 VOLUME_STD_UOM = p_recipe_detail_rec.volume_std_uom,
1308 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1309 TEXT_CODE = p_recipe_detail_rec.text_code,
1310 LAST_UPDATED_BY = NVL(p_recipe_detail_rec.last_updated_by, gmd_api_grp.user_id),
1311 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date,sysdate) ,
1312 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1313 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1314 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1315 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1316 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1317 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1318 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1319 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1320 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1321 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1322 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1323 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1324 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1325 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1326 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1327 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1328 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1329 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1330 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1331 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1332 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1333 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1334 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1335 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1336 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1337 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1338 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1339 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1340 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1341 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1342 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1343 WHERE recipe_id = p_recipe_detail_rec.recipe_id
1344 AND routingstep_id = p_recipe_detail_rec.routingstep_id;
1345 END IF; /* ends dml type */
1346 EXCEPTION
1347 WHEN FND_API.G_EXC_ERROR THEN
1348 x_return_status := FND_API.G_RET_STS_ERROR;
1349 WHEN OTHERS THEN
1350 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1352 END Recipe_Routing_Steps;
1353
1354 /* ================================================== */
1355 /* Procedure: */
1356 /* Recipe_Orgn_Operations */
1357 /* */
1358 /* DESCRIPTION: */
1359 /* This PL/SQL procedure is responsible for */
1360 /* inserting and updating recipe orgn activities */
1361 /* */
1362 /* ===================================================*/
1363 /* Start of commments */
1364 /* API name : Recipe_Orgn_operations */
1365 /* Type : Private */
1366 /* Procedure : */
1367 /* End of comments */
1368
1369 PROCEDURE RECIPE_ORGN_OPERATIONS
1370 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1371 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1372 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1373 x_return_status OUT NOCOPY VARCHAR2
1374 ) IS
1375
1376 /* Define all variables specific to this procedure */
1377 l_dml_type VARCHAR2(1) := 'I';
1378 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_OPERATIONS';
1379 l_rowid VARCHAR2(40);
1380
1381 l_roact_id NUMBER;
1382
1383 /* This cursor decides whether to insert or update. */
1384 CURSOR recipe_activity_cur(vRecipe_id NUMBER, vRoutingStep_id NUMBER,
1385 vOprn_line_id NUMBER, vOrgn_id NUMBER) IS
1386 SELECT rowid
1387 FROM gmd_recipe_orgn_activities
1388 where recipe_id = NVL(vRecipe_id,-1) AND
1389 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
1390 oprn_line_id = NVL(vOprn_line_id,-1) AND
1391 organization_id = vOrgn_id;
1392 BEGIN
1393 /* Updating recipe orgn activity for forst time infact insert a new record in */
1394 /* gmd_recipe_orgn activities table */
1395
1396 /* Initialization of all status */
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398
1399 /* To decide on the operation to perform */
1400 /* If activity already exists then it is a update */
1401 OPEN recipe_activity_cur(p_recipe_detail_rec.recipe_id,
1402 p_recipe_detail_rec.routingstep_id,
1403 p_recipe_detail_rec.oprn_line_id,
1404 p_recipe_detail_rec.organization_id);
1405 FETCH recipe_activity_cur INTO l_rowid;
1406 IF (recipe_activity_cur%NOTFOUND) THEN
1407 l_dml_type := 'I';
1408 ELSE
1409 l_dml_type := 'U';
1410 END IF;
1411 CLOSE recipe_activity_cur;
1412
1413 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1414 IF (l_dml_type = 'I') THEN
1415 INSERT INTO GMD_RECIPE_ORGN_ACTIVITIES (
1416 RECIPE_ID ,
1417 ROUTINGSTEP_ID ,
1418 OPRN_LINE_ID ,
1419 ACTIVITY_FACTOR ,
1420 ORGANIZATION_ID ,
1421 CREATED_BY ,
1422 CREATION_DATE ,
1423 LAST_UPDATE_DATE ,
1424 LAST_UPDATE_LOGIN ,
1425 TEXT_CODE ,
1426 LAST_UPDATED_BY ,
1427 ATTRIBUTE1 ,
1428 ATTRIBUTE2 ,
1429 ATTRIBUTE3 ,
1430 ATTRIBUTE4 ,
1431 ATTRIBUTE5 ,
1432 ATTRIBUTE6 ,
1433 ATTRIBUTE7 ,
1434 ATTRIBUTE8 ,
1435 ATTRIBUTE9 ,
1436 ATTRIBUTE10 ,
1437 ATTRIBUTE11 ,
1438 ATTRIBUTE12 ,
1439 ATTRIBUTE13 ,
1440 ATTRIBUTE14 ,
1441 ATTRIBUTE15 ,
1442 ATTRIBUTE16 ,
1443 ATTRIBUTE17 ,
1444 ATTRIBUTE18 ,
1445 ATTRIBUTE19 ,
1446 ATTRIBUTE20 ,
1447 ATTRIBUTE21 ,
1448 ATTRIBUTE22 ,
1449 ATTRIBUTE23 ,
1450 ATTRIBUTE24 ,
1451 ATTRIBUTE25 ,
1452 ATTRIBUTE26 ,
1453 ATTRIBUTE27 ,
1454 ATTRIBUTE28 ,
1455 ATTRIBUTE29 ,
1456 ATTRIBUTE30 ,
1457 ATTRIBUTE_CATEGORY )
1458 VALUES (
1459 p_recipe_detail_rec.recipe_id ,
1460 p_recipe_detail_rec.routingstep_id ,
1461 p_recipe_detail_rec.oprn_line_id ,
1462 p_recipe_detail_rec.activity_factor ,
1463 p_recipe_detail_rec.organization_id ,
1464 NVL(p_recipe_detail_rec.CREATED_BY, gmd_api_grp.user_id) ,
1465 NVL(p_recipe_detail_rec.CREATION_DATE,sysdate) ,
1466 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE,sysdate) ,
1467 NVL (p_recipe_detail_rec.LAST_UPDATE_LOGIN , gmd_api_grp.login_id) ,
1468 p_recipe_detail_rec.TEXT_CODE ,
1469 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1470 p_flex_insert_rec.ATTRIBUTE1 ,
1471 p_flex_insert_rec.ATTRIBUTE2 ,
1472 p_flex_insert_rec.ATTRIBUTE3 ,
1473 p_flex_insert_rec.ATTRIBUTE4 ,
1474 p_flex_insert_rec.ATTRIBUTE5 ,
1475 p_flex_insert_rec.ATTRIBUTE6 ,
1476 p_flex_insert_rec.ATTRIBUTE7 ,
1477 p_flex_insert_rec.ATTRIBUTE8 ,
1478 p_flex_insert_rec.ATTRIBUTE9 ,
1479 p_flex_insert_rec.ATTRIBUTE10 ,
1480 p_flex_insert_rec.ATTRIBUTE11 ,
1481 p_flex_insert_rec.ATTRIBUTE12 ,
1482 p_flex_insert_rec.ATTRIBUTE13 ,
1483 p_flex_insert_rec.ATTRIBUTE14 ,
1484 p_flex_insert_rec.ATTRIBUTE15 ,
1485 p_flex_insert_rec.ATTRIBUTE16 ,
1486 p_flex_insert_rec.ATTRIBUTE17 ,
1487 p_flex_insert_rec.ATTRIBUTE18 ,
1488 p_flex_insert_rec.ATTRIBUTE19 ,
1489 p_flex_insert_rec.ATTRIBUTE20 ,
1490 p_flex_insert_rec.ATTRIBUTE21 ,
1491 p_flex_insert_rec.ATTRIBUTE22 ,
1492 p_flex_insert_rec.ATTRIBUTE23 ,
1493 p_flex_insert_rec.ATTRIBUTE24 ,
1494 p_flex_insert_rec.ATTRIBUTE25 ,
1495 p_flex_insert_rec.ATTRIBUTE26 ,
1496 p_flex_insert_rec.ATTRIBUTE27 ,
1497 p_flex_insert_rec.ATTRIBUTE28 ,
1498 p_flex_insert_rec.ATTRIBUTE29 ,
1499 p_flex_insert_rec.ATTRIBUTE30 ,
1500 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1501 END IF; /* end of dml type */
1502
1503 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1504 IF (l_dml_type = 'U') THEN
1505 UPDATE GMD_RECIPE_ORGN_ACTIVITIES
1506 SET ACTIVITY_FACTOR = p_recipe_detail_rec.activity_factor,
1507 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date,sysdate),
1508 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1509 TEXT_CODE = p_recipe_detail_rec.text_code,
1510 LAST_UPDATED_BY = p_recipe_detail_rec.last_updated_by,
1511 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1512 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1513 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1514 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1515 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1516 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1517 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1518 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1519 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1520 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1521 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1522 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1523 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1524 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1525 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1526 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1527 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1528 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1529 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1530 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1531 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1532 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1533 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1534 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1535 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1536 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1537 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1538 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1539 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1540 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1541 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1542 WHERE rowid = l_rowid;
1543 END IF; /* ends dml type */
1544 EXCEPTION
1545 WHEN FND_API.G_EXC_ERROR THEN
1546 x_return_status := FND_API.G_RET_STS_ERROR;
1547 WHEN OTHERS THEN
1548 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550 END RECIPE_ORGN_OPERATIONS;
1551
1552
1553 /* ================================================== */
1554 /* Procedure: */
1555 /* Recipe_Orgn_Resources */
1556 /* */
1557 /* DESCRIPTION: */
1558 /* This PL/SQL procedure is responsible for */
1559 /* inserting and updating recipe orgn resources */
1560 /* */
1561 /* ===================================================*/
1562 /* Start of commments */
1563 /* API name : Recipe_Orgn_Resources */
1564 /* Type : Private */
1565 /* Procedure : */
1566 /* End of comments */
1567
1568 PROCEDURE RECIPE_ORGN_RESOURCES
1569 ( p_recipe_detail_rec IN GMD_RECIPE_DETAIL.recipe_dtl ,
1570 p_flex_insert_rec IN GMD_RECIPE_DETAIL.flex ,
1571 p_flex_update_rec IN GMD_RECIPE_DETAIL.update_flex ,
1572 x_return_status OUT NOCOPY VARCHAR2
1573 ) IS
1574
1575 /* Define all variables specific to this procedure */
1576 l_dml_type VARCHAR2(1) := 'I';
1577 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_RESOURCES';
1578
1579 l_rores_id NUMBER;
1580 l_rowid VARCHAR2(32);
1581
1582 /* This cursor decides whether to insert or update. */
1583 CURSOR recipe_resources_cur(vRecipe_id NUMBER,
1584 vRoutingStep_id NUMBER,
1585 vOprn_line_id NUMBER,
1586 vResources VARCHAR2,
1587 vOrgn_id NUMBER) IS
1588 SELECT rowid
1589 FROM gmd_recipe_orgn_resources
1590 where recipe_id = NVL(vRecipe_id,-1) AND
1591 RoutingStep_id = NVL(vRoutingStep_id,-1) AND
1592 oprn_line_id = NVL(vOprn_line_id,-1) AND
1593 resources = vResources AND
1594 organization_id = vOrgn_id;
1595
1596 BEGIN
1597 /* Updating recipe orgn resources for forst time infact insert a new record in */
1598 /* gmd_recipe_orgn_resources table */
1599
1600 /* Initialization of all status */
1601 x_return_status := FND_API.G_RET_STS_SUCCESS;
1602
1603 /* To decide on the operation to perform */
1604 /* If resource already exists then it is a update */
1605 OPEN recipe_resources_cur(p_recipe_detail_rec.recipe_id, p_recipe_detail_rec.routingstep_id,
1606 p_recipe_detail_rec.oprn_line_id,
1607 p_recipe_detail_rec.resources,
1608 p_recipe_detail_rec.organization_id);
1609 FETCH recipe_resources_cur INTO l_rowid;
1610 IF (recipe_resources_cur%NOTFOUND) THEN
1611 l_dml_type := 'I';
1612 ELSE
1613 l_dml_type := 'U';
1614 END IF;
1615 CLOSE recipe_resources_cur;
1616
1617 /* ++++++++++++++++INSERTs+++++++++++++++++++++++++ */
1618 IF (l_dml_type = 'I') THEN
1619 INSERT INTO GMD_RECIPE_ORGN_RESOURCES (
1620 RECIPE_ID ,
1621 ORGANIZATION_ID ,
1622 ROUTINGSTEP_ID ,
1623 OPRN_LINE_ID ,
1624 RESOURCES ,
1625 MIN_CAPACITY ,
1626 MAX_CAPACITY ,
1627 -- CAPACITY_UOM ,
1628 PROCESS_PARAMETER_5 ,
1629 PROCESS_PARAMETER_4 ,
1630 PROCESS_PARAMETER_3 ,
1631 PROCESS_PARAMETER_2 ,
1632 PROCESS_PARAMETER_1 ,
1633 PROCESS_UM ,
1634 USAGE_UOM ,
1635 RESOURCE_USAGE ,
1636 PROCESS_QTY ,
1637 CREATED_BY ,
1638 CREATION_DATE ,
1639 LAST_UPDATE_DATE ,
1640 LAST_UPDATE_LOGIN ,
1641 TEXT_CODE ,
1642 LAST_UPDATED_BY ,
1643 ATTRIBUTE1 ,
1644 ATTRIBUTE2 ,
1645 ATTRIBUTE3 ,
1646 ATTRIBUTE4 ,
1647 ATTRIBUTE5 ,
1648 ATTRIBUTE6 ,
1649 ATTRIBUTE7 ,
1650 ATTRIBUTE8 ,
1651 ATTRIBUTE9 ,
1652 ATTRIBUTE10 ,
1653 ATTRIBUTE11 ,
1654 ATTRIBUTE12 ,
1655 ATTRIBUTE13 ,
1656 ATTRIBUTE14 ,
1657 ATTRIBUTE15 ,
1658 ATTRIBUTE16 ,
1659 ATTRIBUTE17 ,
1660 ATTRIBUTE18 ,
1661 ATTRIBUTE19 ,
1662 ATTRIBUTE20 ,
1663 ATTRIBUTE21 ,
1664 ATTRIBUTE22 ,
1665 ATTRIBUTE23 ,
1666 ATTRIBUTE24 ,
1667 ATTRIBUTE25 ,
1668 ATTRIBUTE26 ,
1669 ATTRIBUTE27 ,
1670 ATTRIBUTE28 ,
1671 ATTRIBUTE29 ,
1672 ATTRIBUTE30 ,
1673 ATTRIBUTE_CATEGORY )
1674 VALUES (
1675 p_recipe_detail_rec.recipe_id ,
1676 p_recipe_detail_rec.organization_id ,
1677 p_recipe_detail_rec.ROUTINGSTEP_ID ,
1678 p_recipe_detail_rec.OPRN_LINE_ID ,
1679 p_recipe_detail_rec.RESOURCES ,
1680 p_recipe_detail_rec.MIN_CAPACITY ,
1681 p_recipe_detail_rec.MAX_CAPACITY ,
1682 -- p_recipe_detail_rec.CAPACITY_UOM ,
1683 p_recipe_detail_rec.PROCESS_PARAMETER_5 ,
1684 p_recipe_detail_rec.PROCESS_PARAMETER_4 ,
1685 p_recipe_detail_rec.PROCESS_PARAMETER_3 ,
1686 p_recipe_detail_rec.PROCESS_PARAMETER_2 ,
1687 p_recipe_detail_rec.PROCESS_PARAMETER_1 ,
1688 p_recipe_detail_rec.PROCESS_UM ,
1689 p_recipe_detail_rec.USAGE_UOM ,
1690 p_recipe_detail_rec.RESOURCE_USAGE ,
1691 p_recipe_detail_rec.PROCESS_QTY ,
1692 NVL(p_recipe_detail_rec.CREATED_BY , gmd_api_grp.user_id) ,
1693 NVL(p_recipe_detail_rec.CREATION_DATE, sysdate) ,
1694 NVL(p_recipe_detail_rec.LAST_UPDATE_DATE, sysdate) ,
1695 NVL(p_recipe_detail_rec.LAST_UPDATE_LOGIN , gmd_api_grp.login_id) ,
1696 p_recipe_detail_rec.TEXT_CODE ,
1697 NVL(p_recipe_detail_rec.LAST_UPDATED_BY, gmd_api_grp.user_id) ,
1698 p_flex_insert_rec.ATTRIBUTE1 ,
1699 p_flex_insert_rec.ATTRIBUTE2 ,
1700 p_flex_insert_rec.ATTRIBUTE3 ,
1701 p_flex_insert_rec.ATTRIBUTE4 ,
1702 p_flex_insert_rec.ATTRIBUTE5 ,
1703 p_flex_insert_rec.ATTRIBUTE6 ,
1704 p_flex_insert_rec.ATTRIBUTE7 ,
1705 p_flex_insert_rec.ATTRIBUTE8 ,
1706 p_flex_insert_rec.ATTRIBUTE9 ,
1707 p_flex_insert_rec.ATTRIBUTE10 ,
1708 p_flex_insert_rec.ATTRIBUTE11 ,
1709 p_flex_insert_rec.ATTRIBUTE12 ,
1710 p_flex_insert_rec.ATTRIBUTE13 ,
1711 p_flex_insert_rec.ATTRIBUTE14 ,
1712 p_flex_insert_rec.ATTRIBUTE15 ,
1713 p_flex_insert_rec.ATTRIBUTE16 ,
1714 p_flex_insert_rec.ATTRIBUTE17 ,
1715 p_flex_insert_rec.ATTRIBUTE18 ,
1716 p_flex_insert_rec.ATTRIBUTE19 ,
1717 p_flex_insert_rec.ATTRIBUTE20 ,
1718 p_flex_insert_rec.ATTRIBUTE21 ,
1719 p_flex_insert_rec.ATTRIBUTE22 ,
1720 p_flex_insert_rec.ATTRIBUTE23 ,
1721 p_flex_insert_rec.ATTRIBUTE24 ,
1722 p_flex_insert_rec.ATTRIBUTE25 ,
1723 p_flex_insert_rec.ATTRIBUTE26 ,
1724 p_flex_insert_rec.ATTRIBUTE27 ,
1725 p_flex_insert_rec.ATTRIBUTE28 ,
1726 p_flex_insert_rec.ATTRIBUTE29 ,
1727 p_flex_insert_rec.ATTRIBUTE30 ,
1728 p_flex_insert_rec.ATTRIBUTE_CATEGORY );
1729 END IF; /* end of dml type */
1730
1731 /* +++++++++++++++UPDATE+++++++++++++++++++++++++++ */
1732 IF (l_dml_type = 'U') THEN
1733 UPDATE GMD_RECIPE_ORGN_RESOURCES
1734 SET MIN_CAPACITY = p_recipe_detail_rec.min_capacity,
1735 MAX_CAPACITY = p_recipe_detail_rec.max_capacity,
1736 PROCESS_PARAMETER_5 = p_recipe_detail_rec.PROCESS_PARAMETER_5,
1737 PROCESS_PARAMETER_4 = p_recipe_detail_rec.PROCESS_PARAMETER_4,
1738 PROCESS_PARAMETER_3 = p_recipe_detail_rec.PROCESS_PARAMETER_3,
1739 PROCESS_PARAMETER_2 = p_recipe_detail_rec.PROCESS_PARAMETER_2,
1740 PROCESS_PARAMETER_1 = p_recipe_detail_rec.PROCESS_PARAMETER_1,
1741 PROCESS_UM = p_recipe_detail_rec.PROCESS_UM ,
1742 USAGE_UOM = p_recipe_detail_rec.USAGE_UOM ,
1743 RESOURCE_USAGE = p_recipe_detail_rec.RESOURCE_USAGE,
1744 PROCESS_QTY = p_recipe_detail_rec.PROCESS_QTY ,
1745 LAST_UPDATE_DATE = NVL(p_recipe_detail_rec.last_update_date, sysdate),
1746 LAST_UPDATE_LOGIN = NVL(p_recipe_detail_rec.last_update_login, gmd_api_grp.login_id),
1747 TEXT_CODE = p_recipe_detail_rec.text_code,
1748 LAST_UPDATED_BY = NVL(p_recipe_detail_rec.last_updated_by, gmd_api_grp.user_id) ,
1749 ATTRIBUTE1 = p_flex_update_rec.attribute1,
1750 ATTRIBUTE2 = p_flex_update_rec.attribute2,
1751 ATTRIBUTE3 = p_flex_update_rec.attribute3,
1752 ATTRIBUTE4 = p_flex_update_rec.attribute4,
1753 ATTRIBUTE5 = p_flex_update_rec.attribute5,
1754 ATTRIBUTE6 = p_flex_update_rec.attribute6,
1755 ATTRIBUTE7 = p_flex_update_rec.attribute7,
1756 ATTRIBUTE8 = p_flex_update_rec.attribute8,
1757 ATTRIBUTE9 = p_flex_update_rec.attribute9,
1758 ATTRIBUTE10 = p_flex_update_rec.attribute10,
1759 ATTRIBUTE11 = p_flex_update_rec.attribute11,
1760 ATTRIBUTE12 = p_flex_update_rec.attribute12,
1761 ATTRIBUTE13 = p_flex_update_rec.attribute13,
1762 ATTRIBUTE14 = p_flex_update_rec.attribute14,
1763 ATTRIBUTE15 = p_flex_update_rec.attribute15,
1764 ATTRIBUTE16 = p_flex_update_rec.attribute16,
1765 ATTRIBUTE17 = p_flex_update_rec.attribute17,
1766 ATTRIBUTE18 = p_flex_update_rec.attribute18,
1767 ATTRIBUTE19 = p_flex_update_rec.attribute19,
1768 ATTRIBUTE20 = p_flex_update_rec.attribute20,
1769 ATTRIBUTE21 = p_flex_update_rec.attribute21,
1770 ATTRIBUTE22 = p_flex_update_rec.attribute22,
1771 ATTRIBUTE23 = p_flex_update_rec.attribute23,
1772 ATTRIBUTE24 = p_flex_update_rec.attribute24,
1773 ATTRIBUTE25 = p_flex_update_rec.attribute25,
1774 ATTRIBUTE26 = p_flex_update_rec.attribute26,
1775 ATTRIBUTE27 = p_flex_update_rec.attribute27,
1776 ATTRIBUTE28 = p_flex_update_rec.attribute28,
1777 ATTRIBUTE29 = p_flex_update_rec.attribute29,
1778 ATTRIBUTE30 = p_flex_update_rec.attribute30,
1779 ATTRIBUTE_CATEGORY = p_flex_update_rec.attribute_category
1780 WHERE rowid = l_rowid;
1781
1782 END IF; /* ends dml type */
1783 EXCEPTION
1784 WHEN FND_API.G_EXC_ERROR THEN
1785 x_return_status := FND_API.G_RET_STS_ERROR;
1786 WHEN OTHERS THEN
1787 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
1788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1789 END RECIPE_ORGN_RESOURCES;
1790
1791 END GMD_RECIPE_DETAIL_PVT; /* Package end */