1 PACKAGE BODY GMD_VALIDITY_RULES_PVT AS
2 /* $Header: GMDVRVRB.pls 120.5.12020000.3 2012/09/29 08:40:53 maychen 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 /* Michael Tou 25-Oct-2011 Bug# 6713805 Added ITEM_UM in order to re-calculate */
453 /* inv_min_qty, inv_max_qty whenever UOM updates */
454 /* =============================================================== */
455 PROCEDURE update_validity_rules
456 ( p_validity_rule_id IN gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE
457 , p_update_table IN gmd_validity_rules_pvt.update_tbl_type
458 , x_message_count OUT NOCOPY NUMBER
459 , x_message_list OUT NOCOPY VARCHAR2
460 , x_return_status OUT NOCOPY VARCHAR2
461 ) IS
462
463 /* Local variable section */
464 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_VALIDITY_RULES';
465 l_db_date DATE;
466 l_inv_item_um mtl_units_of_measure.uom_code%TYPE; -- NPD Conv.
467 l_tpl NUMBER;
468 l_fixed_scale NUMBER;
469
470 -- NPD Conv. Added the following local var's
471 l_formula_id NUMBER;
472 l_recp_use NUMBER;
473
474 /* Define record type that hold the routing data */
475 l_old_vr_rec gmd_recipe_validity_rules%ROWTYPE;
476 l_organization_id number := null; -- Bug 14690148 pal
477
478 /* Define Exceptions */
479 VR_update_failure EXCEPTION;
480 last_update_date_failure EXCEPTION;
481 invalid_version EXCEPTION;
482 setup_failure EXCEPTION;
483
484 /* Define cursor section */
485 CURSOR get_old_vr_rec(vValidity_rule_id
486 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
487 Select *
488 From gmd_recipe_validity_rules
489 Where recipe_validity_rule_id = vValidity_rule_id;
490
491 CURSOR Get_db_last_update_date(vValidity_rule_id
492 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
493 Select last_update_date
494 From gmd_recipe_validity_rules
495 Where recipe_validity_rule_id = vValidity_rule_id;
496
497 CURSOR Get_Routing_Details(vValidity_rule_id
498 gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
499 Select rt.Effective_Start_Date,
500 rt.Effective_End_Date
501 From gmd_routings_b rt, gmd_recipes_b rc,
502 gmd_recipe_validity_rules vr
503 Where vr.recipe_id = rc.recipe_id AND
504 rc.routing_id = rt.routing_id AND
505 vr.recipe_validity_rule_id = vValidity_rule_id AND
506 rt.delete_mark = 0;
507
508 CURSOR check_fmdtl_fixed_scale(vRecipe_id gmd_recipes_b.recipe_id%TYPE
509 ,vItem_id ic_item_mst_b.item_id%TYPE) IS
510 SELECT 1
511 FROM sys.dual
512 WHERE EXISTS (Select d.formula_id
513 From fm_matl_dtl d, gmd_recipes_b r
514 WHERE r.formula_id = d.formula_id AND
515 r.recipe_id = vRecipe_id AND
516 d.line_type = 1 AND
517 d.inventory_item_id = vItem_id AND -- NPD Conv.
518 d.scale_type = 0);
519
520 CURSOR check_fmhdr_fixed_scale(vRecipe_id gmd_recipes_b.recipe_id%TYPE) IS
521 SELECT 1
522 FROM sys.dual
523 WHERE EXISTS (Select h.formula_id
524 From fm_form_mst h, gmd_recipes_b r
525 WHERE r.formula_id = h.formula_id AND
526 r.recipe_id = vRecipe_id AND
527 h.scale_type = 0);
528
529 -- Cursor to fetch recipe use and formula id of the VR
530 CURSOR get_recp_dets IS
531 SELECT r.formula_id, v.recipe_use
532 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
533 WHERE v.recipe_validity_rule_id = p_validity_rule_id
534 AND v.recipe_id = r.recipe_id;
535
536 BEGIN
537 /* Intialize the setup fields */
538
539 IF NOT gmd_api_grp.setup_done THEN
540 gmd_api_grp.setup_done := gmd_api_grp.setup;
541 END IF;
542
543 IF NOT gmd_api_grp.setup_done THEN
544 RAISE setup_failure;
545 END IF;
546
547 /* Set the return status to success initially */
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549
550 IF (l_debug = 'Y') THEN
551 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
552 ||' : About to get the db VR record for VR id = '
553 ||p_validity_rule_id);
554 END IF;
555
556 /* Get the old routing rec value */
557 OPEN get_old_vr_rec(p_validity_rule_id);
558 FETCH get_old_vr_rec INTO l_old_vr_rec;
559 IF get_old_vr_rec%NOTFOUND THEN
560 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_INVALID');
561 FND_MSG_PUB.ADD;
562 CLOSE get_old_vr_rec;
563 RAISE vr_update_failure;
564 END IF;
565 CLOSE get_old_vr_rec;
566
567 /* Loop thro' every column in p_update_table table and for each column name
568 assign or replace the old value with the table value */
569 FOR i IN 1 .. p_update_table.count LOOP
570
571 IF (l_debug = 'Y') THEN
572 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||' : The column to be updated = '
573 ||p_update_table(i).p_col_to_update||' and value = '
574 ||p_update_table(i).p_value);
575 END IF;
576
577 -- IF (UPPER(p_update_table(i).p_col_to_update) = 'RECIPE_ID') THEN
578 -- l_old_vr_rec.RECIPE_ID := p_update_table(i).p_value;
579 -- ELSE
580
581 IF (UPPER(p_update_table(i).p_col_to_update) = 'ORGN_CODE') THEN
582 l_old_vr_rec.ORGN_CODE := p_update_table(i).p_value;
583 -- NPD Conv.
584 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ORGANIZATION_ID') THEN
585 l_old_vr_rec.ORGANIZATION_ID := p_update_table(i).p_value;
586 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'INVENTORY_ITEM_ID') THEN
587 l_old_vr_rec.INVENTORY_ITEM_ID := p_update_table(i).p_value;
588 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'REVISION') THEN
589 l_old_vr_rec.REVISION := p_update_table(i).p_value;
590 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DETAIL_UOM') THEN
591 l_old_vr_rec.DETAIL_UOM := p_update_table(i).p_value;
592 -- End NPD Conv.
593 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
594 l_old_vr_rec.TEXT_CODE := p_update_table(i).p_value;
595 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'RECIPE_USE') THEN
596 l_old_vr_rec.RECIPE_USE := p_update_table(i).p_value;
597 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PREFERENCE') THEN
598 l_old_vr_rec.PREFERENCE := p_update_table(i).p_value;
599 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'START_DATE') THEN
600 IF (l_debug = 'Y') THEN
601 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||
602 ' : Before conversion of Start date - '||
603 ' CharDT to Date Format ');
604 END IF;
605 l_old_vr_rec.START_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
606 IF (l_debug = 'Y') THEN
607 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
608 ||' : After conversion of CharDT to Date '||
609 ' Start Date = '||l_old_vr_rec.START_DATE);
610 END IF;
611 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'END_DATE') THEN
612 IF (l_debug = 'Y') THEN
613 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
614 ||' : Before conversion of end date - '
615 ||' CharDT to Date Format '||p_update_table(i).p_value);
616 END IF;
617 l_old_vr_rec.END_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
618 IF (l_debug = 'Y') THEN
619 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
620 ||' : After conversion of CharDT to Date '||
621 ' End Date = '||l_old_vr_rec.END_DATE);
622 END IF;
623
624 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PLANNED_PROCESS_LOSS') THEN
625 l_old_vr_rec.PLANNED_PROCESS_LOSS := p_update_table(i).p_value;
626 --RLNAGARA start B6997624 Added code to update Fixed Process Loss and UOM
627 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS') THEN
628 l_old_vr_rec.FIXED_PROCESS_LOSS := p_update_table(i).p_value;
629 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM') THEN
630 l_old_vr_rec.FIXED_PROCESS_LOSS_UOM := p_update_table(i).p_value;
631 --RLNAGARA end B6997624
632 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') THEN
633 l_old_vr_rec.STD_QTY := p_update_table(i).p_value;
634 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MIN_QTY') THEN
635 l_old_vr_rec.MIN_QTY := p_update_table(i).p_value;
636 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MAX_QTY') THEN
637 l_old_vr_rec.MAX_QTY := p_update_table(i).p_value;
638 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK') THEN
639 l_old_vr_rec.DELETE_MARK := p_update_table(i).p_value;
640 -- Bug #4134275 Kapil M
641 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
642 l_old_vr_rec.LAST_UPDATED_BY := NVL(p_update_table(i).p_value, fnd_global.user_id);
643 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
644 l_old_vr_rec.LAST_UPDATE_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
645 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
646 l_old_vr_rec.LAST_UPDATE_LOGIN := NVL(p_update_table(i).p_value,gmd_api_grp.login_id);
647 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
648 l_old_vr_rec.ATTRIBUTE1 := p_update_table(i).p_value;
649 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
650 l_old_vr_rec.ATTRIBUTE2 := p_update_table(i).p_value;
651 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
652 l_old_vr_rec.ATTRIBUTE3 := p_update_table(i).p_value;
653 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
654 l_old_vr_rec.ATTRIBUTE4 := p_update_table(i).p_value;
655 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
656 l_old_vr_rec.ATTRIBUTE5 := p_update_table(i).p_value;
657 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
658 l_old_vr_rec.ATTRIBUTE6 := p_update_table(i).p_value;
659 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
660 l_old_vr_rec.ATTRIBUTE7 := p_update_table(i).p_value;
661 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
662 l_old_vr_rec.ATTRIBUTE8 := p_update_table(i).p_value;
663 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
664 l_old_vr_rec.ATTRIBUTE9 := p_update_table(i).p_value;
665 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
666 l_old_vr_rec.ATTRIBUTE10 := p_update_table(i).p_value;
667 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
668 l_old_vr_rec.ATTRIBUTE11 := p_update_table(i).p_value;
669 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
670 l_old_vr_rec.ATTRIBUTE12 := p_update_table(i).p_value;
671 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
672 l_old_vr_rec.ATTRIBUTE13 := p_update_table(i).p_value;
673 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
674 l_old_vr_rec.ATTRIBUTE14 := p_update_table(i).p_value;
675 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
676 l_old_vr_rec.ATTRIBUTE15 := p_update_table(i).p_value;
677 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
678 l_old_vr_rec.ATTRIBUTE16 := p_update_table(i).p_value;
679 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
680 l_old_vr_rec.ATTRIBUTE17 := p_update_table(i).p_value;
681 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
682 l_old_vr_rec.ATTRIBUTE18 := p_update_table(i).p_value;
683 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
684 l_old_vr_rec.ATTRIBUTE19 := p_update_table(i).p_value;
685 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
686 l_old_vr_rec.ATTRIBUTE20 := p_update_table(i).p_value;
687 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
688 l_old_vr_rec.ATTRIBUTE21 := p_update_table(i).p_value;
689 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
690 l_old_vr_rec.ATTRIBUTE22 := p_update_table(i).p_value;
691 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
692 l_old_vr_rec.ATTRIBUTE23 := p_update_table(i).p_value;
693 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
694 l_old_vr_rec.ATTRIBUTE24 := p_update_table(i).p_value;
695 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
696 l_old_vr_rec.ATTRIBUTE25 := p_update_table(i).p_value;
697 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
698 l_old_vr_rec.ATTRIBUTE26 := p_update_table(i).p_value;
699 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
700 l_old_vr_rec.ATTRIBUTE27 := p_update_table(i).p_value;
701 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
702 l_old_vr_rec.ATTRIBUTE28 := p_update_table(i).p_value;
703 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
704 l_old_vr_rec.ATTRIBUTE29 := p_update_table(i).p_value;
705 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
706 l_old_vr_rec.ATTRIBUTE30 := p_update_table(i).p_value;
707 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
708 l_old_vr_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
709 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'VALIDITY_RULE_STATUS') THEN
710 -- Users should be prompted to use change status API
711 -- Since Change Status API commits work it cannot be
712 -- called from here directly
713 FND_MESSAGE.set_name('GMD','GMD_NOT_USE_API_UPD_STATUS');
714 FND_MSG_PUB.ADD;
715 RAISE vr_update_failure;
716 END IF;
717
718 IF (l_debug = 'Y') THEN
719 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': '||
720 'Assigned all values - Now performing indv validations ');
721 END IF;
722
723 /* Chcek if update is allowed */
724 IF NOT GMD_COMMON_VAL.update_allowed('VALIDITY'
725 ,p_Validity_rule_id
726 ,UPPER(p_update_table(i).p_col_to_update) ) THEN
727 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_CANNOT_UPD');
728 FND_MSG_PUB.ADD;
729 RAISE vr_update_failure;
730 END IF;
731
732 /* Compare Dates - if the last update date passed in via the API is less than
733 the last update in the db - it indicates someelse has updated this row after this
734 row was selected */
735 IF (l_debug = 'Y') THEN
736 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 1 '||
737 'Comparing last updates to check if there any locking issues ');
738 END IF;
739
740 OPEN Get_db_last_update_date(p_Validity_rule_id);
741 FETCH Get_db_last_update_date INTO l_db_date;
742 IF Get_db_last_update_date%NOTFOUND THEN
743 CLOSE Get_db_last_update_date;
744 RAISE vr_update_failure;
745 END IF;
746 CLOSE Get_db_last_update_date;
747
748 -- Validation are done here
749 -- it might have to moved to a PUB layer !!!!
750 IF l_old_vr_rec.LAST_UPDATE_DATE < l_db_date THEN
751 RAISE last_update_date_failure;
752 END IF;
753
754 IF (l_debug = 'Y') THEN
755 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2 '||
756 'Start and End Date validation '||l_old_vr_rec.start_date||
757 ' - '||l_old_vr_rec.end_date);
758 END IF;
759
760 -- Check if the item being modified is either a product or a by-product
761 IF (UPPER(p_update_table(i).p_col_to_update) = 'ITEM_ID') THEN
762 IF NOT Determine_Product(l_old_vr_rec.RECIPE_ID, l_old_vr_rec.INVENTORY_ITEM_ID) THEN -- NPD Conv.
763 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_IS_PRODUCT');
764 FND_MSG_PUB.ADD;
765 x_return_status := 'E';
766 RAISE vr_update_failure;
767 END IF;
768 END IF;
769
770 -- Validity rule date validation with routing dates
771 IF (UPPER(p_update_table(i).p_col_to_update) IN ('START_DATE','END_DATE')) THEN
772
773 -- Validity rule start and end date validation
774 IF (l_debug = 'Y') THEN
775 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2c '||
776 'Checking if end date ( '||l_old_vr_rec.end_date||' ) '||
777 ' > '||' start date ( '||l_old_vr_rec.start_date||' ) ');
778 END IF;
779 -- Comparing Vr start and End dates
780 effective_dates ( P_start_date => l_old_vr_rec.start_date,
781 P_end_date => l_old_vr_rec.end_date,
782 x_return_status => x_return_status);
783
784 IF (x_return_status <> 'S') THEN
785 RAISE vr_update_failure;
786 END IF;
787
788 -- Comparing Vr dates with Routing Dates
789 IF (l_debug = 'Y') THEN
790 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2a0 '||
791 'Comparing Vr dates with Routing Dates ');
792 END IF;
793
794 FOR get_routing_rec in Get_Routing_Details(p_validity_rule_id) LOOP
795 -- Get the routing start date if applicable
796 IF (l_debug = 'Y') THEN
797 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
798 ||': The vr start date = '||l_old_vr_rec.start_date
799 ||' rout start date = '||get_routing_rec.effective_start_date);
800 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
801 ||': The Vr end date = '||l_old_vr_rec.end_date
802 ||' rout end date = '||get_routing_rec.effective_end_date);
803 END IF;
804
805 IF (l_debug = 'Y') THEN
806 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2a '||
807 'Checking if VR start date > Routing start date ');
808 END IF;
809
810 validate_start_date (P_disp_start_date => l_old_vr_rec.start_date,
811 P_routing_start_date => get_routing_rec.effective_start_date,
812 x_return_status => x_return_status);
813 IF (x_return_status <> 'S') THEN
814 RAISE vr_update_failure;
815 END IF;
816
817 -- Get the routing start date if applicable
818 IF (l_debug = 'Y') THEN
819 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2b '||
820 'Chceking if VR end date < Routing end date ');
821 END IF;
822 validate_end_date (P_end_date => l_old_vr_rec.end_date,
823 P_routing_end_date => get_routing_rec.effective_end_date,
824 x_return_status => x_return_status);
825
826 IF (x_return_status <> 'S') THEN
827 RAISE vr_update_failure;
828 END IF;
829 END LOOP;
830
831 END IF; -- When start or end dates are updated
832
833 IF (l_debug = 'Y') THEN
834 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '
835 ||' : About to validate std qty '
836 ||'The min qty < max qty < std qty = '
837 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
838 ||' - '||l_old_vr_rec.std_qty);
839 END IF;
840
841 -- Min, MAx and Std qty validation
842 IF (UPPER(p_update_table(i).p_col_to_update)
843 IN ('STD_QTY','MIN_QTY','MAX_QTY')) THEN
844 -- Check if scale type at formula header is fixed, if yes then
845 -- the qty's fields cannot be updated
846 IF (l_debug = 'Y') THEN
847 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
848 'Checking if formula hdr is fixed scaled ');
849 END IF;
850
851 OPEN check_fmhdr_fixed_scale(l_old_vr_rec.Recipe_id);
852 FETCH check_fmhdr_fixed_scale INTO l_fixed_scale;
853 CLOSE check_fmhdr_fixed_scale;
854
855 IF (l_fixed_scale = 1) THEN
856 FND_MESSAGE.SET_NAME('GMD', 'GMD_FXD_HDR_FOR_VR');
857 FND_MSG_PUB.ADD;
858 RAISE vr_update_failure;
859 END IF;
860
861 -- Check if scale type at formula dtl for the VR product is fixed, if yes then
862 -- the qty's fields cannot be updated
863 IF (l_debug = 'Y') THEN
864 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
865 'Checking if formula dtl is fixed scaled ');
866 END IF;
867
868 OPEN check_fmdtl_fixed_scale(l_old_vr_rec.Recipe_id, l_old_vr_rec.inventory_item_id); -- NPD Conv.
869 FETCH check_fmdtl_fixed_scale INTO l_fixed_scale;
870 CLOSE check_fmdtl_fixed_scale;
871
872 IF (l_fixed_scale = 1) THEN
873 FND_MESSAGE.SET_NAME('GMD', 'GMD_FXD_HDR_FOR_VR');
874 FND_MSG_PUB.ADD;
875 RAISE vr_update_failure;
876 END IF;
877 END IF;
878 -- Bug 5024092 KapilM
879 -- Moved the stnd qty and min max qty check out of this loop.
880 -- The inv_min and max qty changes only ifthe min or max_qty value is changed
881 IF (UPPER(p_update_table(i).p_col_to_update) IN ('MIN_QTY','MAX_QTY','ITEM_UM','DETAIL_UOM')) THEN/*Bug #6713805 -Added ITEM_UM also. */
882 -- Bug 14690148 pal check for null organization_id , if so need to get the owner_organization_id from the recipe
883
884 IF l_old_vr_rec.organization_id is NULL then
885 SELECT owner_organization_id
886 INTO l_organization_id
887 FROM GMD_RECIPES
888 WHERE RECIPE_ID = l_old_vr_rec.RECIPE_ID;
889
890 END IF; -- IF l_old_vr_rec.organization_id is NULL then
891 SELECT UNIQUE primary_uom_code
892 INTO l_inv_item_um
893 FROM mtl_system_items
894 WHERE inventory_item_id = l_old_vr_rec.inventory_item_id
895 and organization_id = nvl(l_old_vr_rec.organization_id, l_organization_id); -- Bug 14690148 pal need to stripe with org
896
897 IF (l_debug = 'Y') THEN
898 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
899 ||': Val 4: About to calc inv min/max qty '
900 ||'The min qty, max qty = '
901 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty);
902 END IF;
903
904 calc_inv_qtys (P_inv_item_um => l_inv_item_um,
905 P_item_um => l_old_vr_rec.detail_uom,
906 P_item_id => l_old_vr_rec.inventory_item_id,
907 P_min_qty => l_old_vr_rec.min_qty,
908 P_max_qty => l_old_vr_rec.max_qty,
909 X_inv_min_qty => l_old_vr_rec.inv_min_qty,
910 X_inv_max_qty => l_old_vr_rec.inv_max_qty,
911 x_return_status => x_return_status);
912
913 IF (x_return_status <> 'S') THEN
914 RAISE vr_update_failure;
915 END IF;
916 END IF;
917
918 IF (l_debug = 'Y') THEN
919 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to calc process loss '
920 ||'The min qty, max qty and std qty = '
921 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
922 ||' - '||l_old_vr_rec.std_qty);
923 END IF;
924 --Bug 12744266 Planned process loss is passed by user no need to calculate here
925 /* IF ((UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') AND
926 (UPPER(p_update_table(i).p_col_to_update) <> 'PLANNED_PROCESS_LOSS')) THEN
927 calculate_process_loss( V_assign => 1
928 ,P_vr_id => p_validity_rule_id
929 ,X_TPL => l_tpl
930 ,X_PPL => l_old_vr_rec.planned_process_loss
931 ,x_return_status => x_return_status);
932 IF (x_return_status <> 'S') THEN
933 --Ignore this error, its ok to update VR without Process loss
934 x_return_status := 'S';
935 --RAISE vr_update_failure;
936 END IF;
937 END IF;*/
938
939 IF (l_debug = 'Y') THEN
940 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to check for duplicates ');
941 END IF;
942
943 IF (l_debug = 'Y') THEN
944 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 6: About to check for duplicates '
945 ||'The min qty, max qty and std qty = '
946 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
947 ||' - '||l_old_vr_rec.std_qty);
948 END IF;
949
950 -- Check for duplicate VR
951 -- bug 4134275
952 check_for_duplicate ( p_recipe_validity_rule_id => p_validity_rule_id
953 ,pRecipe_id => l_old_vr_rec.recipe_id
954 ,pitem_id => l_old_vr_rec.inventory_item_id
955 ,pOrgn_code => l_old_vr_rec.orgn_code
956 -- NPD Conv.
957 ,pOrganization_id => l_old_vr_rec.organization_id
958 ,pRecipe_Use => l_old_vr_rec.recipe_use
959 ,pPreference => l_old_vr_rec.preference
960 ,pstd_qty => l_old_vr_rec.std_qty
961 ,pmin_qty => l_old_vr_rec.min_qty
962 ,pmax_qty => l_old_vr_rec.max_qty
963 ,pinv_max_qty => l_old_vr_rec.inv_max_qty
964 ,pinv_min_qty => l_old_vr_rec.inv_min_qty
965 ,pitem_um => l_old_vr_rec.detail_uom
966 ,pValidity_Rule_status => l_old_vr_rec.validity_rule_status
967 ,pstart_date => l_old_vr_rec.start_date
968 ,pend_date => l_old_vr_rec.end_date
969 ,pplanned_process_loss => l_old_vr_rec.planned_process_loss
970 ,x_return_status => x_return_status
971 );
972
973 IF (x_return_status <> 'S') THEN
974 RAISE vr_update_failure;
975 END IF;
976
977 IF (l_debug = 'Y') THEN
978 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
979 ||': Before Final update : About to Update Val Rules '
980 ||'The min qty, max qty and std qty = '
981 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
982 ||' - '||l_old_vr_rec.std_qty);
983 END IF;
984
985 -- NPD Conv.
986 /* Check if for the updated organization - formula items remain valid */
987 IF (UPPER(p_update_table(i).p_col_to_update) IN ('ORGN_CODE','ORGANIZATION_ID')) THEN
988
989 -- Get the formula_id and recipe_use for the VR
990 OPEN get_recp_dets;
991 FETCH get_recp_dets INTO l_formula_id, l_recp_use;
992 CLOSE get_recp_dets;
993 -- If recipe_use is for production ( =0), pass production flag as TRUE to check_item_exists
994 IF (NVL(l_recp_use,0) = 0) THEN
995 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id,
996 x_return_status => x_return_status,
997 p_organization_id => l_old_vr_rec.organization_id,
998 p_orgn_code => l_old_vr_rec.orgn_code,
999 p_production_check => TRUE);
1000
1001 ELSE
1002 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id,
1003 x_return_status => x_return_status,
1004 p_organization_id => l_old_vr_rec.organization_id,
1005 p_orgn_code => l_old_vr_rec.orgn_code,
1006 p_production_check => FALSE);
1007
1008 END IF;
1009
1010 IF (x_return_status <> 'S') THEN
1011 RAISE vr_update_failure;
1012 END IF;
1013
1014 END IF;
1015 END LOOP;
1016
1017 -- Bug 5024092 Kapil M
1018 -- Moved the stnd qty and min max qty check out of above loop.
1019 IF (l_debug = 'Y') THEN
1020 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
1021 'Checking if min qty < std qty < max qty ');
1022 END IF;
1023 -- Checks if std_qty is between min and max qty
1024
1025
1026 std_qty(P_std_qty => l_old_vr_rec.std_qty,
1027 P_min_qty => l_old_vr_rec.min_qty,
1028 P_max_qty => l_old_vr_rec.max_qty,
1029 x_return_status => x_return_status);
1030
1031 IF (x_return_status <> 'S') THEN
1032 RAISE vr_update_failure;
1033 END IF;
1034
1035 IF (l_debug = 'Y') THEN
1036 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': About to validate max qty '
1037 ||'The min qty, max qty = '
1038 ||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty);
1039 END IF;
1040
1041 -- Min, Max qty validation
1042 max_qty(P_min_qty => l_old_vr_rec.min_qty,
1043 P_max_qty => l_old_vr_rec.max_qty,
1044 x_return_status => x_return_status);
1045
1046 IF (x_return_status <> 'S') THEN
1047 RAISE vr_update_failure;
1048 END IF;
1049
1050 /* Number of times this routine is equal to number of rows in the p_update_table */
1051 UPDATE GMD_RECIPE_VALIDITY_RULES
1052 SET
1053 recipe_id = l_old_vr_rec.recipe_id
1054 , orgn_code = l_old_vr_rec.orgn_code
1055 -- NPD Conv.
1056 , organization_id = l_old_vr_rec.organization_id
1057 , inventory_item_id = l_old_vr_rec.inventory_item_id
1058 , revision = l_old_vr_rec.revision
1059 , detail_uom = l_old_vr_rec.detail_uom
1060 -- End NPD Conv.
1061 , recipe_use = l_old_vr_rec.recipe_use
1062 , preference = l_old_vr_rec.preference
1063 , start_date = l_old_vr_rec.start_date
1064 , end_date = l_old_vr_rec.end_date
1065 , min_qty = l_old_vr_rec.min_qty
1066 , max_qty = l_old_vr_rec.max_qty
1067 , std_qty = l_old_vr_rec.std_qty
1068 , inv_min_qty = l_old_vr_rec.inv_min_qty
1069 , inv_max_qty = l_old_vr_rec.inv_max_qty
1070 , text_code = l_old_vr_rec.text_code
1071 , attribute_category = l_old_vr_rec.attribute_category
1072 , attribute1 = l_old_vr_rec.attribute1
1073 , attribute2 = l_old_vr_rec.attribute2
1074 , attribute3 = l_old_vr_rec.attribute3
1075 , attribute4 = l_old_vr_rec.attribute4
1076 , attribute5 = l_old_vr_rec.attribute5
1077 , attribute6 = l_old_vr_rec.attribute6
1078 , attribute7 = l_old_vr_rec.attribute7
1079 , attribute8 = l_old_vr_rec.attribute8
1080 , attribute9 = l_old_vr_rec.attribute9
1081 , attribute10 = l_old_vr_rec.attribute10
1082 , attribute11 = l_old_vr_rec.attribute11
1083 , attribute12 = l_old_vr_rec.attribute12
1084 , attribute13 = l_old_vr_rec.attribute13
1085 , attribute14 = l_old_vr_rec.attribute14
1086 , attribute15 = l_old_vr_rec.attribute15
1087 , attribute16 = l_old_vr_rec.attribute16
1088 , attribute17 = l_old_vr_rec.attribute17
1089 , attribute18 = l_old_vr_rec.attribute18
1090 , attribute19 = l_old_vr_rec.attribute19
1091 , attribute20 = l_old_vr_rec.attribute20
1092 , attribute21 = l_old_vr_rec.attribute21
1093 , attribute23 = l_old_vr_rec.attribute23
1094 , attribute22 = l_old_vr_rec.attribute22
1095 , attribute24 = l_old_vr_rec.attribute24
1096 , attribute25 = l_old_vr_rec.attribute25
1097 , attribute26 = l_old_vr_rec.attribute26
1098 , attribute27 = l_old_vr_rec.attribute27
1099 , attribute28 = l_old_vr_rec.attribute28
1100 , attribute29 = l_old_vr_rec.attribute29
1101 , attribute30 = l_old_vr_rec.attribute30
1102 , created_by = l_old_vr_rec.created_by
1103 , creation_date = l_old_vr_rec.creation_date
1104 , last_updated_by = l_old_vr_rec.last_updated_by
1105 , last_update_date = l_old_vr_rec.last_update_date
1106 , last_update_login = l_old_vr_rec.last_update_login
1107 , delete_mark = l_old_vr_rec.delete_mark
1108 , validity_rule_status = l_old_vr_rec.validity_rule_status
1109 , lab_type = l_old_vr_rec.lab_type
1110 , planned_process_loss = l_old_vr_rec.planned_process_loss
1111 , fixed_process_loss = l_old_vr_rec.fixed_process_loss /* RLNAGARA Bug6997624 */
1112 , fixed_process_loss_uom = l_old_vr_rec.fixed_process_loss_uom /* RLNAGARA Bug6997624 */
1113 where recipe_validity_rule_id = p_validity_rule_id;
1114
1115 IF (sql%notfound) THEN
1116 FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_UPD_NO_ACCESS');
1117 FND_MSG_PUB.ADD;
1118 RAISE vr_update_failure;
1119 END IF;
1120
1121 IF (l_debug = 'Y') THEN
1122 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': After Update of Val Rules ');
1123 END IF;
1124
1125 /* Check if work was done */
1126 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1127 RAISE vr_update_failure;
1128 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1129
1130 /* Get the messgae list and count generated by this API */
1131 fnd_msg_pub.count_and_get (
1132 p_count => x_message_count
1133 ,p_encoded => FND_API.g_false
1134 ,p_data => x_message_list);
1135
1136 IF (l_debug = 'Y') THEN
1137 gmd_debug.put_line(m_pkg_name||'.'||l_api_name
1138 ||' Completed '||l_api_name
1139 ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1140 END IF;
1141 EXCEPTION
1142 WHEN vr_update_failure OR invalid_version THEN
1143 IF (l_debug = 'Y') THEN
1144 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1145 END IF;
1146 x_return_status := FND_API.G_RET_STS_ERROR;
1147 fnd_msg_pub.count_and_get (
1148 p_count => x_message_count
1149 ,p_encoded => FND_API.g_false
1150 ,p_data => x_message_list);
1151 WHEN last_update_date_failure THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1154 FND_MSG_PUB.ADD;
1155 fnd_msg_pub.count_and_get (
1156 p_count => x_message_count
1157 ,p_encoded => FND_API.g_false
1158 ,p_data => x_message_list);
1159 WHEN setup_failure THEN
1160 x_return_status := FND_API.G_RET_STS_ERROR;
1161 fnd_msg_pub.count_and_get (
1162 p_count => x_message_count
1163 ,p_encoded => FND_API.g_false
1164 ,p_data => x_message_list);
1165 WHEN OTHERS THEN
1166 IF (l_debug = 'Y') THEN
1167 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1168 END IF;
1169 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
1170 x_return_status := FND_API.g_ret_sts_unexp_error;
1171 fnd_msg_pub.count_and_get (
1172 p_count => x_message_count
1173 ,p_encoded => FND_API.g_false
1174 ,p_data => x_message_list);
1175 END update_validity_rules;
1176
1177 END GMD_VALIDITY_RULES_PVT;
[Home] [Help]