1 PACKAGE BODY GMD_VALIDITY_RULES_PVT AS
2 /* $Header: GMDVRVRB.pls 120.2.12010000.2 2008/11/12 18:10:31 rnalla ship $ */
3
4
5 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18 --Bug 3222090, NSRIVAST 20-FEB-2004, END
19
20 /*#####################################################
21 # NAME
22 # Determine_Product
23 # SYNOPSIS
24 # Proc Determine_Product
25 # DESCRIPTION
26 # This procedure validates if the item that is being modified
27 # is a valid product or a co-product
28 # HISTORY
29 #####################################################*/
30 FUNCTION Determine_Product(pRecipe_id NUMBER, pItem_id NUMBER) RETURN BOOLEAN IS
31 CURSOR product_cur(vRecipe_id NUMBER, vItem_id NUMBER) IS
32 Select 1 from fm_matl_dtl fm
33 Where exists (Select 1
34 From gmd_recipes_b rc
35 Where fm.formula_id = rc.formula_id
36 And fm.line_type IN (1,2)
37 And fm.inventory_item_id = vItem_id -- NPD Conv.
38 And rc.recipe_id = vRecipe_id);
39 l_count NUMBER;
40 BEGIN
41
42 OPEN product_cur(pRecipe_id, pItem_id);
43 FETCH product_cur INTO l_count;
44 IF (product_cur%NOTFOUND) THEN
45 CLOSE product_cur;
46 Return FALSE;
47 END IF;
48 CLOSE product_cur;
49
50 Return TRUE;
51 END;
52
53 /*#####################################################
54 # NAME
55 # validate_start_date
56 # SYNOPSIS
57 # Proc validate_start_date
58 # DESCRIPTION
59 # This procedure validates that start date is no earlier
60 # than any routing start date.
61 # HISTORY
62 #####################################################*/
63 PROCEDURE validate_start_date (P_disp_start_date Date,
64 P_routing_start_date Date,
65 x_return_status OUT NOCOPY VARCHAR2) IS
66 l_api_name VARCHAR2(100) := 'validate_start_date' ;
67 BEGIN
68 x_return_status := 'S';
69
70 IF P_disp_start_date < P_routing_start_date THEN
71 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
72 FND_MSG_PUB.ADD;
73 x_return_status := 'E';
74 END IF;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
79 x_return_status := FND_API.g_ret_sts_unexp_error;
80 END validate_start_date;
81
82 /*#####################################################
83 # NAME
84 # validate_end_date
85 # SYNOPSIS
86 # Proc validate_end_date
87 # DESCRIPTION
88 # This procedure validates that end date is no later
89 # than any routing end date.
90 # Also validates date entered against sys max date.
91 # HISTORY
92 #####################################################*/
93 PROCEDURE validate_end_date (P_end_date Date,
94 P_routing_end_date Date,
95 x_return_status OUT NOCOPY VARCHAR2) IS
96 l_api_name VARCHAR2(100) := 'validate_end_date' ;
97 BEGIN
98 x_return_status := 'S';
99 IF (P_end_date IS NOT NULL) AND
100 (P_routing_end_date IS NOT NULL) AND
101 (P_end_date > P_routing_end_date) THEN
102 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
103 FND_MSG_PUB.ADD;
104 x_return_status := 'E';
105 END IF;
106
107 -- Routing end date is finite but Vr end date is infinite
108 IF (P_routing_end_date IS NOT NULL) AND
109 (P_end_date IS NULL) THEN
110 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
111 FND_MSG_PUB.ADD;
112 x_return_status := 'E';
113 END IF;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
118 x_return_status := FND_API.g_ret_sts_unexp_error;
119 END validate_end_date;
120
121
122 /*#####################################################
123 # NAME
124 # effective_dates
125 # SYNOPSIS
126 # Proc effective_dates
127 # DESCRIPTION
128 # Validates dates to be within proper ranges.
129 # HISTORY
130 #####################################################*/
131 PROCEDURE effective_dates ( P_start_date DATE,
132 P_end_date DATE,
133 x_return_status OUT NOCOPY VARCHAR2) IS
134 l_api_name VARCHAR2(100) := 'effective_dates' ;
135 BEGIN
136 x_return_status := 'S';
137
138 IF (P_end_date IS NOT NULL AND P_start_date IS NOT NULL) THEN
139 IF (P_end_date < P_start_date) THEN
140 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
141 FND_MSG_PUB.ADD;
142 x_return_status := 'E';
143 END IF;
144 END IF;
145 EXCEPTION
146 WHEN OTHERS THEN
147 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
148 x_return_status := FND_API.g_ret_sts_unexp_error;
149 END effective_dates;
150
151 /*###################################################################
152 # NAME
153 # std_qty
154 # SYNOPSIS
155 # proc std_qty
156 # Called from when-val-record trigger
157 # DESCRIPTION
158 # Checks for std_qty is in between min_qty and max_qty
159 # Std qty cannot be negative
160 #
161 ###################################################################*/
162 PROCEDURE std_qty(P_std_qty NUMBER,
163 P_min_qty NUMBER,
164 P_max_qty NUMBER,
165 x_return_status OUT NOCOPY VARCHAR2) IS
166 l_api_name VARCHAR2(100) := 'std_qty' ;
167 BEGIN
168 x_return_status := 'S';
169 IF P_std_qty IS NOT NULL THEN
170 IF (P_std_qty < P_min_qty
171 OR P_std_qty > P_max_qty)
172 OR P_std_qty <= 0 THEN
173 IF P_std_qty <= 0 THEN
174 FND_MESSAGE.SET_NAME('GMD','FM_INV_STD_QTY');
175 FND_MSG_PUB.ADD;
176 x_return_status := 'E';
177 ELSE
178 FND_MESSAGE.SET_NAME('GMD','FM_INV_STD_RANGE');
179 FND_MSG_PUB.ADD;
180 x_return_status := 'E';
181 END IF; -- end if std qty is the problem, or the range
182 END IF; -- end if std qty not within range
183 END IF; -- end if std qty is not null
184 EXCEPTION
185 WHEN OTHERS THEN
186 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
187 x_return_status := FND_API.g_ret_sts_unexp_error;
188 END std_qty;
189
190 /*#####################################################
191 # NAME
192 # max_qty
193 # SYNOPSIS
194 # proc max_qty
195 # Called from when-val-record trigger
196 # DESCRIPTION
197 # Checks for max_qty is greater than min_qty
198 #
199 #######################################################*/
200 PROCEDURE max_qty(P_min_qty NUMBER,
201 P_max_qty NUMBER,
202 x_return_status OUT NOCOPY VARCHAR2) IS
203 l_api_name VARCHAR2(100) := 'max_qty' ;
204 BEGIN
205 x_return_status := 'S';
206 IF P_max_qty IS NOT NULL THEN
207 IF (P_max_qty < P_min_qty
208 OR P_min_qty < 0) THEN
209 IF P_min_qty < 0 THEN
210 FND_MESSAGE.SET_NAME('GMD','FM_INV_MIN_QTY');
211 FND_MSG_PUB.ADD;
212 x_return_status := 'E';
213 ELSE
214 FND_MESSAGE.SET_NAME('GMD','FM_INV_MIN_MAX');
215 FND_MSG_PUB.ADD;
216 x_return_status := 'E';
217 END IF; -- end if qty is the problem, or the range
218 END IF; -- IF (P_max_qty < P_min_qty
219 END IF; -- IF P_max_qty IS NOT NULL
220 EXCEPTION
221 WHEN OTHERS THEN
222 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
223 x_return_status := FND_API.g_ret_sts_unexp_error;
224 END max_qty;
225
226
227 /*#####################################################
228 # NAME
229 # calc_inv_qtys
230 # SYNOPSIS
231 # proc calc_inv_qtys
232 # Parms
233 # DESCRIPTION
234 # Checks for item_uom with standard item UOM, if different
235 # Converts the quantity from the initial UOM to the
236 # final UOM.
237 #######################################################*/
238 PROCEDURE calc_inv_qtys (P_inv_item_um VARCHAR2,
239 P_item_um VARCHAR2,
240 P_item_id NUMBER,
241 P_min_qty NUMBER,
242 P_max_qty NUMBER,
243 X_inv_min_qty OUT NOCOPY NUMBER,
244 X_inv_max_qty OUT NOCOPY NUMBER,
245 x_return_status OUT NOCOPY VARCHAR2
246 ) IS
247 l_api_name VARCHAR2(100) := 'calc_inv_qtys' ;
248 BEGIN
249 x_return_status := 'S';
250
251 IF P_inv_item_um = P_item_um THEN
252 X_inv_min_qty := P_min_qty;
253 X_inv_max_qty := P_max_qty;
254 ELSE
255
256 /*########################################################
257 # Stored Procedure call made here for the UOM conversion
258 # between two different UOM's
259 #########################################################*/
260
261
262 /* NPD Conv. Changed the call to INV_CONVERT.inv_um_convert from gmicuom.uom_conversion */
263
264 X_inv_min_qty := INV_CONVERT.inv_um_convert( item_id => P_item_id ,
265 precision => 5 ,
266 from_quantity => P_min_qty ,
267 from_unit => P_item_um ,
268 to_unit => P_inv_item_um,
269 from_name => NULL ,
270 to_name => NULL);
271
272
273
274 X_inv_max_qty := INV_CONVERT.inv_um_convert( item_id => P_item_id ,
275 precision => 5 ,
276 from_quantity => P_max_qty ,
277 from_unit => P_item_um ,
278 to_unit => P_inv_item_um,
279 from_name => NULL ,
280 to_name => NULL);
281
282
283 END IF;
284 X_inv_min_qty := ROUND(X_inv_min_qty,5); --NPD Conv. Round upto 5 digits
285 X_inv_max_qty := ROUND(X_inv_max_qty,5);
286 EXCEPTION
287 WHEN OTHERS THEN
288 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
289 x_return_status := FND_API.g_ret_sts_unexp_error;
290 END calc_inv_qtys;
291
292 /*#####################################################
293 # NAME
294 # calculate_process_loss
295 # SYNOPSIS
296 # Proc calculate_process_loss
297 # DESCRIPTION
298 # derives theoretical and planned process loss
299 #####################################################*/
300 PROCEDURE calculate_process_loss( V_assign IN NUMBER DEFAULT 1
301 ,P_vr_id IN NUMBER
302 ,X_TPL OUT NOCOPY NUMBER
303 ,X_PPL OUT NOCOPY NUMBER
304 ,x_return_status OUT NOCOPY VARCHAR2) IS
305
306 process_loss_rec GMD_COMMON_VAL.process_loss_rec;
307 l_process_loss GMD_PROCESS_LOSS.process_loss%TYPE;
308 l_recipe_theo_loss GMD_PROCESS_LOSS.process_loss%TYPE;
309 x_msg_cnt NUMBER;
310 x_msg_dat VARCHAR2(2000);
311
312 l_std_qty gmd_recipe_validity_rules.std_qty%TYPE;
313 l_detail_uom gmd_recipe_validity_rules.detail_uom%TYPE;
314 l_item_id gmd_recipe_validity_rules.inventory_item_id%TYPE;
315 l_orgn_code gmd_recipe_validity_rules.orgn_code%TYPE;
316
317 CURSOR get_other_vr_details(V_vr_id NUMBER) IS
318 SELECT std_qty, inventory_item_id, detail_uom, orgn_code
319 FROM gmd_recipe_validity_rules
320 WHERE recipe_validity_rule_id = V_vr_id;
321
322 l_api_name VARCHAR2(100) := 'calculate_process_loss' ;
323
324 BEGIN
325 x_return_status := 'S';
326
327 OPEN get_other_vr_details(p_vr_id);
328 FETCH get_other_vr_details INTO l_std_qty, l_item_id, l_detail_uom, l_orgn_code;
329 CLOSE get_other_vr_details;
330
331 process_loss_rec.validity_rule_id := p_vr_id;
332 process_loss_rec.qty := l_std_qty;
333 process_loss_rec.uom := l_detail_uom;
334 process_loss_rec.orgn_code := l_orgn_code;
335 process_loss_rec.item_id := l_item_id;
336
337 gmd_common_val.calculate_process_loss(process_loss => process_loss_rec,
338 Entity_type => 'VALIDITY',
339 x_recipe_theo_loss => X_TPL,
340 x_process_loss => X_PPL,
341 x_return_status => x_return_status,
342 x_msg_count => X_msg_cnt,
343 x_msg_data => X_msg_dat);
344
345 X_TPL := TRUNC(X_TPL,2);
346 X_PPL := TRUNC(X_PPL,2);
347
348 IF (V_assign = 1) THEN
349 IF X_PPL IS NULL THEN
350 X_PPL := X_TPL;
351 END IF;
352 END IF;
353 EXCEPTION
354 WHEN OTHERS THEN
355 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
356 x_return_status := FND_API.g_ret_sts_unexp_error;
357 END calculate_process_loss;
358
359 /*#####################################################
360 # NAME
361 # check_for_duplicate
362 # SYNOPSIS
363 # Proc check_for_duplicate
364 # Parms
365 # DESCRIPTION
366 # check duplication of record
367 #
368 # Bug 4134275 18-NOV-2005 Added one more parameter p_recipe_validity_rule_id
369 # This is used as the duplicate check should check that the updated record is not going
370 # to create a duplicate record, i.e. the same details should not match any
371 # other record(corresponding to p_recipe_validity_rule_id other than this one)
372 #####################################################*/
373 PROCEDURE check_for_duplicate(p_recipe_validity_rule_id NUMBER -- 4134275 Added the validity_rule_id condition for duplicate check
374 ,pRecipe_id NUMBER
375 ,pitem_id NUMBER
376 ,pOrgn_code VARCHAR2 DEFAULT NULL
377 -- NPD Conv.
378 ,pOrganization_id NUMBER
379 ,pRecipe_Use NUMBER
380 ,pPreference NUMBER
381 ,pstd_qty NUMBER
382 ,pmin_qty NUMBER
383 ,pmax_qty NUMBER
384 ,pinv_max_qty NUMBER
385 ,pinv_min_qty NUMBER
386 ,pitem_um VARCHAR2
387 ,pValidity_Rule_Status VARCHAR2
388 ,pstart_date DATE
389 ,pend_date DATE DEFAULT NULL
390 ,pPlanned_process_loss NUMBER DEFAULT NULL
391 ,x_return_status OUT NOCOPY VARCHAR2
392 ) IS
393
394 CURSOR Cur_check_dup_upd IS
395 SELECT recipe_validity_rule_id
396 FROM gmd_recipe_validity_rules
397 WHERE recipe_id = pRecipe_id
398 AND inventory_item_id = pitem_id -- NPD Conv.
399 AND ((orgn_code = pOrgn_code) OR
400 (orgn_code IS NULL AND pOrgn_code IS NULL))
401 -- NPD Conv.
402 AND ((organization_id = pOrganization_id) OR
403 (organization_id IS NULL AND pOrganization_id IS NULL))
404 AND recipe_use = pRecipe_Use
405 AND preference = pPreference
406 AND std_qty = pstd_qty
407 AND min_qty = pmin_qty
408 AND max_qty = pmax_qty
409 AND inv_max_qty = pinv_max_qty
410 AND inv_min_qty = pinv_min_qty
411 AND detail_uom = pitem_um
412
413 AND validity_rule_status = pValidity_Rule_status
414 AND ((pPlanned_process_loss IS NULL AND Planned_process_loss IS NULL) OR
415 (planned_process_loss = pPlanned_process_loss))
416 AND start_date = pstart_date
417 AND ((end_date = pend_date) OR (end_date is NULL and pend_date is NULL))
418 AND recipe_validity_rule_id <> p_recipe_validity_rule_id;
419
420 l_api_name VARCHAR2(100) := 'check_for_duplicate' ;
421 BEGIN
422 x_return_status := 'S';
423 FOR VR_dup_rec IN Cur_check_dup_upd LOOP
424 IF (l_debug = 'Y') THEN
425 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
426 ||': Duplicate VR id = '||VR_dup_rec.recipe_validity_rule_id);
427
428 END IF;
429 FND_MESSAGE.SET_NAME('GMD','GMD_DUP_VR_EXIST');
430 FND_MSG_PUB.ADD;
431 x_return_status := 'E';
432 END LOOP;
433 EXCEPTION
434 WHEN OTHERS THEN
435 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
436 x_return_status := FND_API.g_ret_sts_unexp_error;
437 END check_for_duplicate;
438
439
440 /* =============================================================== */
441 /* Procedure: */
442 /* update_validity_rules */
443 /* */
444 /* DESCRIPTION: */
445 /* */
446 /* */
447 /* History : */
448 /* Shyam 07/29/2002 Initial implementation */
449 /* Kapil M 18-NOV-2005 Bug # 4134275 */
450 /* Changed the call from fnd_date.CHARDATE_TO_DATE to */
451 /* fnd_date.CANONICAL_TO_DATE
452 /* =============================================================== */
453 PROCEDURE update_validity_rules
454 ( p_validity_rule_id IN gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE
455 , p_update_table IN gmd_validity_rules_pvt.update_tbl_type
456 , x_message_count OUT NOCOPY NUMBER
457 , x_message_list OUT NOCOPY VARCHAR2
458 , x_return_status OUT NOCOPY VARCHAR2
459 ) IS
460
461 /* Local variable section */
462 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_VALIDITY_RULES';
463 l_db_date DATE;
464 l_inv_item_um mtl_units_of_measure.uom_code%TYPE; -- NPD Conv.
465 l_tpl NUMBER;
466 l_fixed_scale NUMBER;
467
468 -- NPD Conv. Added the following local var's
469 l_formula_id NUMBER;
470 l_recp_use NUMBER;
471
472 /* Define record type that hold the routing data */
473 l_old_vr_rec gmd_recipe_validity_rules%ROWTYPE;
474
475 /* Define Exceptions */
476 VR_update_failure EXCEPTION;
477 last_update_date_failure EXCEPTION;
478 invalid_version EXCEPTION;
479 setup_failure EXCEPTION;
480
481 /* Define cursor section */
482 CURSOR get_old_vr_rec(vValidity_rule_id
483 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
484 Select *
485 From gmd_recipe_validity_rules
486 Where recipe_validity_rule_id = vValidity_rule_id;
487
488 CURSOR Get_db_last_update_date(vValidity_rule_id
489 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
490 Select last_update_date
491 From gmd_recipe_validity_rules
492 Where recipe_validity_rule_id = vValidity_rule_id;
493
494 CURSOR Get_Routing_Details(vValidity_rule_id
495 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
496 Select rt.Effective_Start_Date,
497 rt.Effective_End_Date
498 From gmd_routings_b rt, gmd_recipes_b rc,
499 gmd_recipe_validity_rules vr
500 Where vr.recipe_id = rc.recipe_id AND
501 rc.routing_id = rt.routing_id AND
502 vr.recipe_validity_rule_id = vValidity_rule_id AND
503 rt.delete_mark = 0;
504
505 CURSOR check_fmdtl_fixed_scale(vRecipe_id gmd_recipes_b.recipe_id%TYPE
506 ,vItem_id ic_item_mst_b.item_id%TYPE) IS
507 SELECT 1
508 FROM sys.dual
509 WHERE EXISTS (Select d.formula_id
510 From fm_matl_dtl d, gmd_recipes_b r
511 WHERE r.formula_id = d.formula_id AND
512 r.recipe_id = vRecipe_id AND
513 d.line_type = 1 AND
514 d.inventory_item_id = vItem_id AND -- NPD Conv.
515 d.scale_type = 0);
516
517 CURSOR check_fmhdr_fixed_scale(vRecipe_id gmd_recipes_b.recipe_id%TYPE) IS
518 SELECT 1
519 FROM sys.dual
520 WHERE EXISTS (Select h.formula_id
521 From fm_form_mst h, gmd_recipes_b r
522 WHERE r.formula_id = h.formula_id AND
523 r.recipe_id = vRecipe_id AND
524 h.scale_type = 0);
525
526 -- Cursor to fetch recipe use and formula id of the VR
527 CURSOR get_recp_dets IS
528 SELECT r.formula_id, v.recipe_use
529 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
530 WHERE v.recipe_validity_rule_id = p_validity_rule_id
531 AND v.recipe_id = r.recipe_id;
532
533 BEGIN
534 /* Intialize the setup fields */
535
536 IF NOT gmd_api_grp.setup_done THEN
537 gmd_api_grp.setup_done := gmd_api_grp.setup;
538 END IF;
539
540 IF NOT gmd_api_grp.setup_done THEN
541 RAISE setup_failure;
542 END IF;
543
544 /* Set the return status to success initially */
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 IF (l_debug = 'Y') THEN
548 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
549 ||' : About to get the db VR record for VR id = '
550 ||p_validity_rule_id);
551 END IF;
552
553 /* Get the old routing rec value */
554 OPEN get_old_vr_rec(p_validity_rule_id);
555 FETCH get_old_vr_rec INTO l_old_vr_rec;
556 IF get_old_vr_rec%NOTFOUND THEN
557 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_INVALID');
558 FND_MSG_PUB.ADD;
559 CLOSE get_old_vr_rec;
560 RAISE vr_update_failure;
561 END IF;
562 CLOSE get_old_vr_rec;
563
564 /* Loop thro' every column in p_update_table table and for each column name
565 assign or replace the old value with the table value */
566 FOR i IN 1 .. p_update_table.count LOOP
567
568 IF (l_debug = 'Y') THEN
569 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||' : The column to be updated = '
570 ||p_update_table(i).p_col_to_update||' and value = '
571 ||p_update_table(i).p_value);
572 END IF;
573
574 -- IF (UPPER(p_update_table(i).p_col_to_update) = 'RECIPE_ID') THEN
575 -- l_old_vr_rec.RECIPE_ID := p_update_table(i).p_value;
576 -- ELSE
577
578 IF (UPPER(p_update_table(i).p_col_to_update) = 'ORGN_CODE') THEN
579 l_old_vr_rec.ORGN_CODE := p_update_table(i).p_value;
580 -- NPD Conv.
581 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ORGANIZATION_ID') THEN
582 l_old_vr_rec.ORGANIZATION_ID := p_update_table(i).p_value;
583 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'INVENTORY_ITEM_ID') THEN
584 l_old_vr_rec.INVENTORY_ITEM_ID := p_update_table(i).p_value;
585 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'REVISION') THEN
586 l_old_vr_rec.REVISION := p_update_table(i).p_value;
587 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DETAIL_UOM') THEN
588 l_old_vr_rec.DETAIL_UOM := p_update_table(i).p_value;
589 -- End NPD Conv.
590 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
591 l_old_vr_rec.TEXT_CODE := p_update_table(i).p_value;
592 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'RECIPE_USE') THEN
593 l_old_vr_rec.RECIPE_USE := p_update_table(i).p_value;
594 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PREFERENCE') THEN
595 l_old_vr_rec.PREFERENCE := p_update_table(i).p_value;
596 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'START_DATE') THEN
597 IF (l_debug = 'Y') THEN
598 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||
599 ' : Before conversion of Start date - '||
600 ' CharDT to Date Format ');
601 END IF;
602 l_old_vr_rec.START_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
603 IF (l_debug = 'Y') THEN
604 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
605 ||' : After conversion of CharDT to Date '||
606 ' Start Date = '||l_old_vr_rec.START_DATE);
607 END IF;
608 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'END_DATE') THEN
609 IF (l_debug = 'Y') THEN
610 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
611 ||' : Before conversion of end date - '
612 ||' CharDT to Date Format '||p_update_table(i).p_value);
613 END IF;
614 l_old_vr_rec.END_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
615 IF (l_debug = 'Y') THEN
616 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
617 ||' : After conversion of CharDT to Date '||
618 ' End Date = '||l_old_vr_rec.END_DATE);
619 END IF;
620
621 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PLANNED_PROCESS_LOSS') THEN
622 l_old_vr_rec.PLANNED_PROCESS_LOSS := p_update_table(i).p_value;
623 --RLNAGARA start B6997624 Added code to update Fixed Process Loss and UOM
624 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS') THEN
625 l_old_vr_rec.FIXED_PROCESS_LOSS := p_update_table(i).p_value;
626 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM') THEN
627 l_old_vr_rec.FIXED_PROCESS_LOSS_UOM := p_update_table(i).p_value;
628 --RLNAGARA end B6997624
629 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') THEN
630 l_old_vr_rec.STD_QTY := p_update_table(i).p_value;
631 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MIN_QTY') THEN
632 l_old_vr_rec.MIN_QTY := p_update_table(i).p_value;
633 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MAX_QTY') THEN
634 l_old_vr_rec.MAX_QTY := p_update_table(i).p_value;
635 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK') THEN
636 l_old_vr_rec.DELETE_MARK := p_update_table(i).p_value;
637 -- Bug #4134275 Kapil M
638 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
639 l_old_vr_rec.LAST_UPDATED_BY := NVL(p_update_table(i).p_value, fnd_global.user_id);
640 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
641 l_old_vr_rec.LAST_UPDATE_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
642 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
643 l_old_vr_rec.LAST_UPDATE_LOGIN := NVL(p_update_table(i).p_value,gmd_api_grp.login_id);
644 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
645 l_old_vr_rec.ATTRIBUTE1 := p_update_table(i).p_value;
646 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
647 l_old_vr_rec.ATTRIBUTE2 := p_update_table(i).p_value;
648 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
649 l_old_vr_rec.ATTRIBUTE3 := p_update_table(i).p_value;
650 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
651 l_old_vr_rec.ATTRIBUTE4 := p_update_table(i).p_value;
652 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
653 l_old_vr_rec.ATTRIBUTE5 := p_update_table(i).p_value;
654 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
655 l_old_vr_rec.ATTRIBUTE6 := p_update_table(i).p_value;
656 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
657 l_old_vr_rec.ATTRIBUTE7 := p_update_table(i).p_value;
658 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
659 l_old_vr_rec.ATTRIBUTE8 := p_update_table(i).p_value;
660 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
661 l_old_vr_rec.ATTRIBUTE9 := p_update_table(i).p_value;
662 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
663 l_old_vr_rec.ATTRIBUTE10 := p_update_table(i).p_value;
664 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
665 l_old_vr_rec.ATTRIBUTE11 := p_update_table(i).p_value;
666 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
667 l_old_vr_rec.ATTRIBUTE12 := p_update_table(i).p_value;
668 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
669 l_old_vr_rec.ATTRIBUTE13 := p_update_table(i).p_value;
670 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
671 l_old_vr_rec.ATTRIBUTE14 := p_update_table(i).p_value;
672 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
673 l_old_vr_rec.ATTRIBUTE15 := p_update_table(i).p_value;
674 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
675 l_old_vr_rec.ATTRIBUTE16 := p_update_table(i).p_value;
676 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
677 l_old_vr_rec.ATTRIBUTE17 := p_update_table(i).p_value;
678 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
679 l_old_vr_rec.ATTRIBUTE18 := p_update_table(i).p_value;
680 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
681 l_old_vr_rec.ATTRIBUTE19 := p_update_table(i).p_value;
682 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
683 l_old_vr_rec.ATTRIBUTE20 := p_update_table(i).p_value;
684 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
685 l_old_vr_rec.ATTRIBUTE21 := p_update_table(i).p_value;
686 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
687 l_old_vr_rec.ATTRIBUTE22 := p_update_table(i).p_value;
688 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
689 l_old_vr_rec.ATTRIBUTE23 := p_update_table(i).p_value;
690 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
691 l_old_vr_rec.ATTRIBUTE24 := p_update_table(i).p_value;
692 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
693 l_old_vr_rec.ATTRIBUTE25 := p_update_table(i).p_value;
694 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
695 l_old_vr_rec.ATTRIBUTE26 := p_update_table(i).p_value;
696 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
697 l_old_vr_rec.ATTRIBUTE27 := p_update_table(i).p_value;
698 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
699 l_old_vr_rec.ATTRIBUTE28 := p_update_table(i).p_value;
700 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
701 l_old_vr_rec.ATTRIBUTE29 := p_update_table(i).p_value;
702 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
703 l_old_vr_rec.ATTRIBUTE30 := p_update_table(i).p_value;
704 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
705 l_old_vr_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
706 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'VALIDITY_RULE_STATUS') THEN
707 -- Users should be prompted to use change status API
708 -- Since Change Status API commits work it cannot be
709 -- called from here directly
710 FND_MESSAGE.set_name('GMD','GMD_NOT_USE_API_UPD_STATUS');
711 FND_MSG_PUB.ADD;
712 RAISE vr_update_failure;
713 END IF;
714
715 IF (l_debug = 'Y') THEN
716 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': '||
717 'Assigned all values - Now performing indv validations ');
718 END IF;
719
720 /* Chcek if update is allowed */
721 IF NOT GMD_COMMON_VAL.update_allowed('VALIDITY'
722 ,p_Validity_rule_id
723 ,UPPER(p_update_table(i).p_col_to_update) ) THEN
724 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_CANNOT_UPD');
725 FND_MSG_PUB.ADD;
726 RAISE vr_update_failure;
727 END IF;
728
729 /* Compare Dates - if the last update date passed in via the API is less than
730 the last update in the db - it indicates someelse has updated this row after this
731 row was selected */
732 IF (l_debug = 'Y') THEN
733 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 1 '||
734 'Comparing last updates to check if there any locking issues ');
735 END IF;
736
737 OPEN Get_db_last_update_date(p_Validity_rule_id);
738 FETCH Get_db_last_update_date INTO l_db_date;
739 IF Get_db_last_update_date%NOTFOUND THEN
740 CLOSE Get_db_last_update_date;
741 RAISE vr_update_failure;
742 END IF;
743 CLOSE Get_db_last_update_date;
744
745 -- Validation are done here
746 -- it might have to moved to a PUB layer !!!!
747 IF l_old_vr_rec.LAST_UPDATE_DATE < l_db_date THEN
748 RAISE last_update_date_failure;
749 END IF;
750
751 IF (l_debug = 'Y') THEN
752 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2 '||
753 'Start and End Date validation '||l_old_vr_rec.start_date||
754 ' - '||l_old_vr_rec.end_date);
755 END IF;
756
757 -- Check if the item being modified is either a product or a by-product
758 IF (UPPER(p_update_table(i).p_col_to_update) = 'ITEM_ID') THEN
759 IF NOT Determine_Product(l_old_vr_rec.RECIPE_ID, l_old_vr_rec.INVENTORY_ITEM_ID) THEN -- NPD Conv.
760 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_IS_PRODUCT');
761 FND_MSG_PUB.ADD;
762 x_return_status := 'E';
763 RAISE vr_update_failure;
764 END IF;
765 END IF;
766
767 -- Validity rule date validation with routing dates
768 IF (UPPER(p_update_table(i).p_col_to_update) IN ('START_DATE','END_DATE')) THEN
769
770 -- Validity rule start and end date validation
771 IF (l_debug = 'Y') THEN
772 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2c '||
773 'Checking if end date ( '||l_old_vr_rec.end_date||' ) '||
774 ' > '||' start date ( '||l_old_vr_rec.start_date||' ) ');
775 END IF;
776 -- Comparing Vr start and End dates
777 effective_dates ( P_start_date => l_old_vr_rec.start_date,
778 P_end_date => l_old_vr_rec.end_date,
779 x_return_status => x_return_status);
780
781 IF (x_return_status <> 'S') THEN
782 RAISE vr_update_failure;
783 END IF;
784
785 -- Comparing Vr dates with Routing Dates
786 IF (l_debug = 'Y') THEN
787 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2a0 '||
788 'Comparing Vr dates with Routing Dates ');
789 END IF;
790
791 FOR get_routing_rec in Get_Routing_Details(p_validity_rule_id) LOOP
792 -- Get the routing start date if applicable
793 IF (l_debug = 'Y') THEN
794 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
795 ||': The vr start date = '||l_old_vr_rec.start_date
796 ||' rout start date = '||get_routing_rec.effective_start_date);
797 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
798 ||': The Vr end date = '||l_old_vr_rec.end_date
799 ||' rout end date = '||get_routing_rec.effective_end_date);
800 END IF;
801
802 IF (l_debug = 'Y') THEN
803 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2a '||
804 'Checking if VR start date > Routing start date ');
805 END IF;
806
807 validate_start_date (P_disp_start_date => l_old_vr_rec.start_date,
808 P_routing_start_date => get_routing_rec.effective_start_date,
809 x_return_status => x_return_status);
810 IF (x_return_status <> 'S') THEN
811 RAISE vr_update_failure;
812 END IF;
813
814 -- Get the routing start date if applicable
815 IF (l_debug = 'Y') THEN
816 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2b '||
817 'Chceking if VR end date < Routing end date ');
818 END IF;
819 validate_end_date (P_end_date => l_old_vr_rec.end_date,
820 P_routing_end_date => get_routing_rec.effective_end_date,
821 x_return_status => x_return_status);
822
823 IF (x_return_status <> 'S') THEN
824 RAISE vr_update_failure;
825 END IF;
826 END LOOP;
827
828 END IF; -- When start or end dates are updated
829
830 IF (l_debug = 'Y') THEN
831 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '
832 ||' : About to validate std qty '
833 ||'The min qty < max qty < std qty = '
834 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
835 ||' - '||l_old_vr_rec.std_qty);
836 END IF;
837
838 -- Min, MAx and Std qty validation
839 IF (UPPER(p_update_table(i).p_col_to_update)
840 IN ('STD_QTY','MIN_QTY','MAX_QTY')) THEN
841 -- Check if scale type at formula header is fixed, if yes then
842 -- the qty's fields cannot be updated
843 IF (l_debug = 'Y') THEN
844 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
845 'Checking if formula hdr is fixed scaled ');
846 END IF;
847
848 OPEN check_fmhdr_fixed_scale(l_old_vr_rec.Recipe_id);
849 FETCH check_fmhdr_fixed_scale INTO l_fixed_scale;
850 CLOSE check_fmhdr_fixed_scale;
851
852 IF (l_fixed_scale = 1) THEN
853 FND_MESSAGE.SET_NAME('GMD', 'GMD_FXD_HDR_FOR_VR');
854 FND_MSG_PUB.ADD;
855 RAISE vr_update_failure;
856 END IF;
857
858 -- Check if scale type at formula dtl for the VR product is fixed, if yes then
859 -- the qty's fields cannot be updated
860 IF (l_debug = 'Y') THEN
861 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
862 'Checking if formula dtl is fixed scaled ');
863 END IF;
864
865 OPEN check_fmdtl_fixed_scale(l_old_vr_rec.Recipe_id, l_old_vr_rec.inventory_item_id); -- NPD Conv.
866 FETCH check_fmdtl_fixed_scale INTO l_fixed_scale;
867 CLOSE check_fmdtl_fixed_scale;
868
869 IF (l_fixed_scale = 1) THEN
870 FND_MESSAGE.SET_NAME('GMD', 'GMD_FXD_HDR_FOR_VR');
871 FND_MSG_PUB.ADD;
872 RAISE vr_update_failure;
873 END IF;
874 END IF;
875 -- Bug 5024092 KapilM
876 -- Moved the stnd qty and min max qty check out of this loop.
877 -- The inv_min and max qty changes only ifthe min or max_qty value is changed
878 IF (UPPER(p_update_table(i).p_col_to_update) IN ('MIN_QTY','MAX_QTY')) THEN
879 SELECT UNIQUE primary_uom_code
880 INTO l_inv_item_um
881 FROM mtl_system_items
882 WHERE inventory_item_id = l_old_vr_rec.inventory_item_id;
883
884 IF (l_debug = 'Y') THEN
885 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
886 ||': Val 4: About to calc inv min/max qty '
887 ||'The min qty, max qty = '
888 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty);
889 END IF;
890
891 calc_inv_qtys (P_inv_item_um => l_inv_item_um,
892 P_item_um => l_old_vr_rec.detail_uom,
893 P_item_id => l_old_vr_rec.inventory_item_id,
894 P_min_qty => l_old_vr_rec.min_qty,
895 P_max_qty => l_old_vr_rec.max_qty,
896 X_inv_min_qty => l_old_vr_rec.inv_min_qty,
897 X_inv_max_qty => l_old_vr_rec.inv_max_qty,
898 x_return_status => x_return_status);
899
900 IF (x_return_status <> 'S') THEN
901 RAISE vr_update_failure;
902 END IF;
903 END IF;
904
905 IF (l_debug = 'Y') THEN
906 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to calc process loss '
907 ||'The min qty, max qty and std qty = '
908 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
909 ||' - '||l_old_vr_rec.std_qty);
910 END IF;
911
912 IF ((UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') AND
913 (UPPER(p_update_table(i).p_col_to_update) <> 'PLANNED_PROCESS_LOSS')) THEN
914 calculate_process_loss( V_assign => 1
915 ,P_vr_id => p_validity_rule_id
916 ,X_TPL => l_tpl
917 ,X_PPL => l_old_vr_rec.planned_process_loss
918 ,x_return_status => x_return_status);
919 IF (x_return_status <> 'S') THEN
920 --Ignore this error, its ok to update VR without Process loss
921 x_return_status := 'S';
922 --RAISE vr_update_failure;
923 END IF;
924 END IF;
925
926 IF (l_debug = 'Y') THEN
927 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to check for duplicates ');
928 END IF;
929
930 IF (l_debug = 'Y') THEN
931 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 6: About to check for duplicates '
932 ||'The min qty, max qty and std qty = '
933 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
934 ||' - '||l_old_vr_rec.std_qty);
935 END IF;
936
937 -- Check for duplicate VR
938 -- bug 4134275
939 check_for_duplicate ( p_recipe_validity_rule_id => p_validity_rule_id
940 ,pRecipe_id => l_old_vr_rec.recipe_id
941 ,pitem_id => l_old_vr_rec.inventory_item_id
942 ,pOrgn_code => l_old_vr_rec.orgn_code
943 -- NPD Conv.
944 ,pOrganization_id => l_old_vr_rec.organization_id
945 ,pRecipe_Use => l_old_vr_rec.recipe_use
946 ,pPreference => l_old_vr_rec.preference
947 ,pstd_qty => l_old_vr_rec.std_qty
948 ,pmin_qty => l_old_vr_rec.min_qty
949 ,pmax_qty => l_old_vr_rec.max_qty
950 ,pinv_max_qty => l_old_vr_rec.inv_max_qty
951 ,pinv_min_qty => l_old_vr_rec.inv_min_qty
952 ,pitem_um => l_old_vr_rec.detail_uom
953 ,pValidity_Rule_status => l_old_vr_rec.validity_rule_status
954 ,pstart_date => l_old_vr_rec.start_date
955 ,pend_date => l_old_vr_rec.end_date
956 ,pplanned_process_loss => l_old_vr_rec.planned_process_loss
957 ,x_return_status => x_return_status
958 );
959
960 IF (x_return_status <> 'S') THEN
961 RAISE vr_update_failure;
962 END IF;
963
964 IF (l_debug = 'Y') THEN
965 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
966 ||': Before Final update : About to Update Val Rules '
967 ||'The min qty, max qty and std qty = '
968 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
969 ||' - '||l_old_vr_rec.std_qty);
970 END IF;
971
972 -- NPD Conv.
973 /* Check if for the updated organization - formula items remain valid */
974 IF (UPPER(p_update_table(i).p_col_to_update) IN ('ORGN_CODE','ORGANIZATION_ID')) THEN
975
976 -- Get the formula_id and recipe_use for the VR
977 OPEN get_recp_dets;
978 FETCH get_recp_dets INTO l_formula_id, l_recp_use;
979 CLOSE get_recp_dets;
980 -- If recipe_use is for production ( =0), pass production flag as TRUE to check_item_exists
981 IF (NVL(l_recp_use,0) = 0) THEN
982 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id,
983 x_return_status => x_return_status,
984 p_organization_id => l_old_vr_rec.organization_id,
985 p_orgn_code => l_old_vr_rec.orgn_code,
986 p_production_check => TRUE);
987
988 ELSE
989 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id,
990 x_return_status => x_return_status,
991 p_organization_id => l_old_vr_rec.organization_id,
992 p_orgn_code => l_old_vr_rec.orgn_code,
993 p_production_check => FALSE);
994
995 END IF;
996
997 IF (x_return_status <> 'S') THEN
998 RAISE vr_update_failure;
999 END IF;
1000
1001 END IF;
1002 END LOOP;
1003
1004 -- Bug 5024092 Kapil M
1005 -- Moved the stnd qty and min max qty check out of above loop.
1006 IF (l_debug = 'Y') THEN
1007 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
1008 'Checking if min qty < std qty < max qty ');
1009 END IF;
1010 -- Checks if std_qty is between min and max qty
1011
1012
1013 std_qty(P_std_qty => l_old_vr_rec.std_qty,
1014 P_min_qty => l_old_vr_rec.min_qty,
1015 P_max_qty => l_old_vr_rec.max_qty,
1016 x_return_status => x_return_status);
1017
1018 IF (x_return_status <> 'S') THEN
1019 RAISE vr_update_failure;
1020 END IF;
1021
1022 IF (l_debug = 'Y') THEN
1023 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to validate max qty '
1024 ||'The min qty, max qty = '
1025 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty);
1026 END IF;
1027
1028 -- Min, Max qty validation
1029 max_qty(P_min_qty => l_old_vr_rec.min_qty,
1030 P_max_qty => l_old_vr_rec.max_qty,
1031 x_return_status => x_return_status);
1032
1033 IF (x_return_status <> 'S') THEN
1034 RAISE vr_update_failure;
1035 END IF;
1036
1037 /* Number of times this routine is equal to number of rows in the p_update_table */
1038 UPDATE GMD_RECIPE_VALIDITY_RULES
1039 SET
1040 recipe_id = l_old_vr_rec.recipe_id
1041 , orgn_code = l_old_vr_rec.orgn_code
1042 -- NPD Conv.
1043 , organization_id = l_old_vr_rec.organization_id
1044 , inventory_item_id = l_old_vr_rec.inventory_item_id
1045 , revision = l_old_vr_rec.revision
1046 , detail_uom = l_old_vr_rec.detail_uom
1047 -- End NPD Conv.
1048 , recipe_use = l_old_vr_rec.recipe_use
1049 , preference = l_old_vr_rec.preference
1050 , start_date = l_old_vr_rec.start_date
1051 , end_date = l_old_vr_rec.end_date
1052 , min_qty = l_old_vr_rec.min_qty
1053 , max_qty = l_old_vr_rec.max_qty
1054 , std_qty = l_old_vr_rec.std_qty
1055 , inv_min_qty = l_old_vr_rec.inv_min_qty
1056 , inv_max_qty = l_old_vr_rec.inv_max_qty
1057 , text_code = l_old_vr_rec.text_code
1058 , attribute_category = l_old_vr_rec.attribute_category
1059 , attribute1 = l_old_vr_rec.attribute1
1060 , attribute2 = l_old_vr_rec.attribute2
1061 , attribute3 = l_old_vr_rec.attribute3
1062 , attribute4 = l_old_vr_rec.attribute4
1063 , attribute5 = l_old_vr_rec.attribute5
1064 , attribute6 = l_old_vr_rec.attribute6
1065 , attribute7 = l_old_vr_rec.attribute7
1066 , attribute8 = l_old_vr_rec.attribute8
1067 , attribute9 = l_old_vr_rec.attribute9
1068 , attribute10 = l_old_vr_rec.attribute10
1069 , attribute11 = l_old_vr_rec.attribute11
1070 , attribute12 = l_old_vr_rec.attribute12
1071 , attribute13 = l_old_vr_rec.attribute13
1072 , attribute14 = l_old_vr_rec.attribute14
1073 , attribute15 = l_old_vr_rec.attribute15
1074 , attribute16 = l_old_vr_rec.attribute16
1075 , attribute17 = l_old_vr_rec.attribute17
1076 , attribute18 = l_old_vr_rec.attribute18
1077 , attribute19 = l_old_vr_rec.attribute19
1078 , attribute20 = l_old_vr_rec.attribute20
1079 , attribute21 = l_old_vr_rec.attribute21
1080 , attribute23 = l_old_vr_rec.attribute23
1081 , attribute22 = l_old_vr_rec.attribute22
1082 , attribute24 = l_old_vr_rec.attribute24
1083 , attribute25 = l_old_vr_rec.attribute25
1084 , attribute26 = l_old_vr_rec.attribute26
1085 , attribute27 = l_old_vr_rec.attribute27
1086 , attribute28 = l_old_vr_rec.attribute28
1087 , attribute29 = l_old_vr_rec.attribute29
1088 , attribute30 = l_old_vr_rec.attribute30
1089 , created_by = l_old_vr_rec.created_by
1090 , creation_date = l_old_vr_rec.creation_date
1091 , last_updated_by = l_old_vr_rec.last_updated_by
1092 , last_update_date = l_old_vr_rec.last_update_date
1093 , last_update_login = l_old_vr_rec.last_update_login
1094 , delete_mark = l_old_vr_rec.delete_mark
1095 , validity_rule_status = l_old_vr_rec.validity_rule_status
1096 , lab_type = l_old_vr_rec.lab_type
1097 , planned_process_loss = l_old_vr_rec.planned_process_loss
1098 , fixed_process_loss = l_old_vr_rec.fixed_process_loss /* RLNAGARA Bug6997624 */
1099 , fixed_process_loss_uom = l_old_vr_rec.fixed_process_loss_uom /* RLNAGARA Bug6997624 */
1100 where recipe_validity_rule_id = p_validity_rule_id;
1101
1102 IF (sql%notfound) THEN
1103 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_UPD_NO_ACCESS');
1104 FND_MSG_PUB.ADD;
1105 RAISE vr_update_failure;
1106 END IF;
1107
1108 IF (l_debug = 'Y') THEN
1109 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': After Update of Val Rules ');
1110 END IF;
1111
1112 /* Check if work was done */
1113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1114 RAISE vr_update_failure;
1115 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1116
1117 /* Get the messgae list and count generated by this API */
1118 fnd_msg_pub.count_and_get (
1119 p_count => x_message_count
1120 ,p_encoded => FND_API.g_false
1121 ,p_data => x_message_list);
1122
1123 IF (l_debug = 'Y') THEN
1124 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
1125 ||' Completed '||l_api_name
1126 ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1127 END IF;
1128 EXCEPTION
1129 WHEN vr_update_failure OR invalid_version THEN
1130 IF (l_debug = 'Y') THEN
1131 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1132 END IF;
1133 x_return_status := FND_API.G_RET_STS_ERROR;
1134 fnd_msg_pub.count_and_get (
1135 p_count => x_message_count
1136 ,p_encoded => FND_API.g_false
1137 ,p_data => x_message_list);
1138 WHEN last_update_date_failure THEN
1139 x_return_status := FND_API.G_RET_STS_ERROR;
1140 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1141 FND_MSG_PUB.ADD;
1142 fnd_msg_pub.count_and_get (
1143 p_count => x_message_count
1144 ,p_encoded => FND_API.g_false
1145 ,p_data => x_message_list);
1146 WHEN setup_failure THEN
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148 fnd_msg_pub.count_and_get (
1149 p_count => x_message_count
1150 ,p_encoded => FND_API.g_false
1151 ,p_data => x_message_list);
1152 WHEN OTHERS THEN
1153 IF (l_debug = 'Y') THEN
1154 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1155 END IF;
1156 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
1157 x_return_status := FND_API.g_ret_sts_unexp_error;
1158 fnd_msg_pub.count_and_get (
1159 p_count => x_message_count
1160 ,p_encoded => FND_API.g_false
1161 ,p_data => x_message_list);
1162 END update_validity_rules;
1163
1164 END GMD_VALIDITY_RULES_PVT;
[Home] [Help]