[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_GENERATE
Source
1 PACKAGE BODY GMD_RECIPE_GENERATE AS
2 /*$Header: GMDARGEB.pls 120.1.12000000.2 2007/02/09 11:15:08 kmotupal ship $*/
3
4 /* Global variables */
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_GENERATE';
6 G_CREATE_VALIDITY BOOLEAN DEFAULT FALSE;
7
8 /*+========================================================================+
9 ** Name : generate
10 **
11 ** Notes : This procedure receives as input autorecipe record and
12 ** does the calculations.
13 **
14 ** If everything is fine then OUT parameter
15 ** x_return_status is set to 'S' else appropriate
16 ** error message is put on the stack and error
17 ** is returned.
18 **
19 ** HISTORY
20 ** Ger Kelly 1 March Created.
21 ** G.Kelly 14 May 2004 Made changes due so that the code can run
22 ** for formula/recipe/validity statuses of New
23 ** G.Kelly 25 May 2004 B3642992 Added a new procedure calculate_preference
24 ** which checks whether the managing validity rules is set up for different options
25 ** and updates accordingly.
26 ** Rewrote some of the code for workflow
27 ** kkillams 01-DEC-2004 4004501, replaced p_orgn_code with p_orgn_id.
28 ** Kapil M 03-JAN-2007 LCF-GMO ME : Bug#5458666- Added the parameters routing-id and pi-indicator
29 ** Passed the parameters to create_recipe.
30 +========================================================================+*/
31 PROCEDURE recipe_generate(p_orgn_id IN NUMBER,
32 p_formula_id IN NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_recipe_no OUT NOCOPY VARCHAR2,
35 x_recipe_version OUT NOCOPY NUMBER,
36 p_event_signed IN BOOLEAN,
37 p_routing_id IN NUMBER DEFAULT NULL,
38 p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL) IS
39
40 /* Cursors */
41 CURSOR c_get_recipe_info IS
42 SELECT *
43 FROM gmd_recipe_generation
44 WHERE organization_id = p_orgn_id
45 OR organization_id IS NULL
46 ORDER BY organization_id;
47
48 LocalInfoRecord c_get_recipe_info%ROWTYPE;
49
50 CURSOR c_get_formula_status IS
51 SELECT formula_status
52 FROM fm_form_mst_b
53 WHERE formula_id = p_formula_id;
54
55 LocalFormRecord c_get_formula_status%ROWTYPE;
56
57 /* Local variables */
58
59 l_recipe_id NUMBER(15);
60 x_recipe_id NUMBER(15);
61
62 l_default_status gmd_api_grp.status_rec_type;
63 l_default_recipe_status gmd_api_grp.status_rec_type;
64 l_default_vr_status gmd_api_grp.status_rec_type;
65 l_formula_status VARCHAR2(30);
66 l_end_status VARCHAR2(30);
67 l_recipe_status VARCHAR2(30);
68 l_creation_type NUMBER;
69 l_recipe_use NUMBER;
70 l_enable_wf VARCHAR2(1);
71
72 /* Exceptions */
73 Create_Recipe_Err EXCEPTION;
74
75
76 BEGIN
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 OPEN c_get_formula_status;
80 FETCH c_get_formula_status INTO l_formula_status;
81 CLOSE c_get_formula_status;
82
83 gmd_api_grp.get_status_details (V_entity_type => 'FORMULA',
84 V_orgn_id => p_orgn_id,
85 X_entity_status => l_default_status);
86
87 IF l_default_status.entity_status <> l_formula_status THEN
88 RETURN;
89 END IF;
90
91 OPEN c_get_recipe_info;
92 FETCH c_get_recipe_info INTO LocalInfoRecord;
93 IF c_get_recipe_info%FOUND THEN
94
95 create_recipe(p_formula_id => p_formula_id,
96 p_formula_status => l_default_status.entity_status,
97 p_orgn_id => p_orgn_id,
98 x_end_status => l_end_status,
99 x_recipe_no => x_recipe_no,
100 x_recipe_version => x_recipe_version,
101 x_recipe_id => x_recipe_id,
102 x_return_status => x_return_status,
103 p_event_signed => p_event_signed,
104 -- Kapil LCF-GMO ME : Bug#5458666
105 p_routing_id => p_routing_id,
106 p_enhanced_pi_ind => p_enhanced_pi_ind);
107 IF X_return_status <> FND_API.g_ret_sts_success THEN
108 RAISE Create_Recipe_Err;
109 END IF;
110
111 gmd_api_grp.get_status_details (V_entity_type => 'RECIPE',
112 V_orgn_id => p_orgn_id,
113 X_entity_status => l_default_recipe_status);
114
115 IF (l_end_status = l_default_recipe_status.entity_status) THEN
116 create_validity_rule_set(p_recipe_id => x_recipe_id,
117 p_recipe_no => x_recipe_no,
118 p_recipe_version => x_recipe_version,
119 p_formula_id => p_formula_id,
120 p_orgn_id => p_orgn_id,
121 p_recipe_use_prod => LocalInfoRecord.recipe_use_prod,
122 p_recipe_use_plan => LocalInfoRecord.recipe_use_plan,
123 p_recipe_use_cost => LocalInfoRecord.recipe_use_cost,
124 p_recipe_use_reg => LocalInfoRecord.recipe_use_reg,
125 p_recipe_use_tech => LocalInfoRecord.recipe_use_tech,
126 p_manage_validity_rules => LocalInfoRecord.managing_validity_rules,
127 p_event_signed => p_event_signed,
128 x_return_status => x_return_status);
129 END IF; /* IF (l_end_status = l_default_recipe_status.entity_status) */
130 END IF; /* g_get_recipe %found*/
131 CLOSE c_get_recipe_info;
132
133 EXCEPTION
134 WHEN CREATE_RECIPE_ERR THEN
135 X_return_status := FND_API.G_RET_STS_ERROR;
136 WHEN OTHERS THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138
139 END recipe_generate;
140
141 /*+========================================================================+
142 ** Name : create_validity_rule_set
143 ** Notes : This procedure receives as input autorecipe record and
144 ** creates validity rules records.
145 **
146 ** If everything is fine then OUT parameter
147 ** x_return_status is set to 'S' else appropriate
148 ** error message is put on the stack and error
149 ** is returned.
150 **
151 ** HISTORY
152 ** Thomas Daniel 1 June Created.
153 ** kkillams 01-dec-2004 p_orgn_code parameter is replaced with
154 ** p_orgn_id w.r.t. 4004501
155 ** PARAMETERS
156 ** p_recipe_status status of the recipe
157 ** p_recipe_id indicator of a recipe
158 ** p_recipe_no recipe no
159 ** p_recipe_verison recipe version
160 ** p_formula_id
161 ** p_formula_status
162 ** p_orgn_code
163 ** p_recipe_use indicate the validity rules created for entity
164 **+========================================================================+*/
165
166
167 PROCEDURE create_validity_rule_set(p_recipe_id IN NUMBER,
168 p_recipe_no IN VARCHAR2,
169 p_recipe_version IN NUMBER,
170 p_formula_id IN NUMBER,
171 p_orgn_id IN NUMBER,
172 p_manage_validity_rules IN NUMBER,
173 p_recipe_use_prod IN NUMBER,
174 p_recipe_use_plan IN NUMBER,
175 p_recipe_use_cost IN NUMBER,
176 p_recipe_use_reg IN NUMBER,
177 p_recipe_use_tech IN NUMBER,
178 p_event_signed IN BOOLEAN,
179 x_return_status OUT NOCOPY VARCHAR2) IS
180 l_recipe_use NUMBER(5);
181 l_end_status VARCHAR2(40);
182 BEGIN
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 IF p_recipe_use_prod = 1 THEN
186 l_recipe_use := 0;
187 create_validity_rule(p_recipe_id => p_recipe_id,
188 p_recipe_no => p_recipe_no,
189 p_recipe_version => p_recipe_version,
190 p_formula_id => p_formula_id,
191 p_orgn_id => p_orgn_id,
192 p_recipe_use => l_recipe_use,
193 p_manage_validity_rules => p_manage_validity_rules,
194 x_end_status => l_end_status,
195 x_return_status => x_return_status,
196 p_event_signed => p_event_signed);
197 END IF; /* IF p_recipe_use_prod = 1 */
198
199 IF p_recipe_use_plan = 1 THEN
200 l_recipe_use := 1;
201 create_validity_rule(p_recipe_id => p_recipe_id,
202 p_recipe_no => p_recipe_no,
203 p_recipe_version => p_recipe_version,
204 p_formula_id => p_formula_id,
205 p_orgn_id => p_orgn_id,
206 p_recipe_use => l_recipe_use,
207 p_manage_validity_rules => p_manage_validity_rules,
208 x_end_status => l_end_status,
209 x_return_status => x_return_status,
210 p_event_signed => p_event_signed);
211 END IF; /* IF p_recipe_use_plan = 1 */
212
213 IF p_recipe_use_cost = 1 THEN
214 l_recipe_use := 2;
215 create_validity_rule(p_recipe_id => p_recipe_id,
216 p_recipe_no => p_recipe_no,
217 p_recipe_version => p_recipe_version,
218 p_formula_id => p_formula_id,
219 p_orgn_id => p_orgn_id,
220 p_recipe_use => l_recipe_use,
221 p_manage_validity_rules => p_manage_validity_rules,
222 x_end_status => l_end_status,
223 x_return_status => x_return_status,
224 p_event_signed => p_event_signed);
225 END IF; /* IF p_recipe_use_cost = 1 */
226
227 IF p_recipe_use_reg = 1 THEN
228 l_recipe_use := 3;
229 create_validity_rule(p_recipe_id => p_recipe_id,
230 p_recipe_no => p_recipe_no,
231 p_recipe_version => p_recipe_version,
232 p_formula_id => p_formula_id,
233 p_orgn_id => p_orgn_id,
234 p_recipe_use => l_recipe_use,
235 p_manage_validity_rules => p_manage_validity_rules,
236 x_end_status => l_end_status,
237 x_return_status => x_return_status,
238 p_event_signed => p_event_signed);
239 END IF; /* IF p_recipe_use_reg = 1 */
240
241 IF p_recipe_use_tech = 1 THEN
242 l_recipe_use := 4;
243 create_validity_rule(p_recipe_id => p_recipe_id,
244 p_recipe_no => p_recipe_no,
245 p_recipe_version => p_recipe_version,
246 p_formula_id => p_formula_id,
247 p_orgn_id => p_orgn_id,
248 p_recipe_use => l_recipe_use,
249 p_manage_validity_rules => p_manage_validity_rules,
250 x_end_status => l_end_status,
251 x_return_status => x_return_status,
252 p_event_signed => p_event_signed);
253 END IF; /* IF p_recipe_use_tech = 1 */
254 END create_validity_rule_set;
255
256 /*+========================================================================+
257 ** Name : create_validity_rule
258 ** Notes : This procedure receives as input autorecipe record and
259 ** creates validity rules records.
260 **
261 ** If everything is fine then OUT parameter
262 ** x_return_status is set to 'S' else appropriate
263 ** error message is put on the stack and error
264 ** is returned.
265 **
266 ** HISTORY
267 ** Thomas Daniel 1 June Created.
268 ** kkillams 01-dec-2004 p_orgn_code parameter is replaced with
269 ** p_orgn_id w.r.t. 4004501
270 ** PARAMETERS
271 ** p_recipe_status status of the recipe
272 ** p_recipe_id indicator of a recipe
273 ** p_recipe_no recipe no
274 ** p_recipe_verison recipe version
275 ** p_formula_id
276 ** p_formula_status
277 ** p_orgn_code
278 ** p_recipe_use indicate the validity rules created for entity
279 **+========================================================================+*/
280 PROCEDURE create_validity_rule( p_recipe_id IN NUMBER,
281 p_recipe_no IN VARCHAR2,
282 p_recipe_version IN NUMBER,
283 p_formula_id IN NUMBER,
284 p_orgn_id IN NUMBER,
285 p_recipe_use IN NUMBER,
286 p_manage_validity_rules IN NUMBER,
287 x_end_status OUT NOCOPY VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 p_event_signed IN BOOLEAN) IS
290
291 CURSOR Cur_get_validity_status (V_validity_rule_id NUMBER) IS
292 SELECT validity_rule_status
293 FROM gmd_recipe_validity_rules
294 WHERE recipe_validity_rule_id = V_validity_rule_id;
295
296
297 l_vr_id NUMBER;
298 l_default_vr_status gmd_api_grp.status_rec_type;
299 l_return_status VARCHAR2(1);
300 x_msg_count NUMBER;
301 x_msg_data VARCHAR2(2000);
302
303 BEGIN
304 x_return_status := FND_API.G_RET_STS_SUCCESS;
305
306 GMD_API_GRP.retrieve_vr(p_formula_id => p_formula_id,
307 l_recipe_vr_tbl => l_recipe_vr_tbl,
308 l_vr_flex => l_vr_flex,
309 x_return_status => x_return_status,
310 p_recipe_use => p_recipe_use);
311
312 l_recipe_vr_tbl.recipe_id := p_recipe_id;
313
314 l_recipe_vr_tbl.recipe_no := p_recipe_no;
315 l_recipe_vr_tbl.recipe_version := p_recipe_version;
316 l_recipe_vr_tbl.recipe_use := p_recipe_use;
317 l_recipe_vr_tbl.organization_id := p_orgn_id;
318 -- Bug# 5206449 Kapil M
319 -- Setting status 100 and then modify the status
320 l_recipe_vr_tbl.validity_rule_status :=100;
321
322 IF p_manage_validity_rules IN (1,2) THEN
323 manage_existing_validity(p_item_id => l_recipe_vr_tbl.inventory_item_id,
324 p_orgn_id => p_orgn_id,
325 p_recipe_use => p_recipe_use,
326 p_start_date => l_recipe_vr_tbl.start_date,
327 p_end_date => l_recipe_vr_tbl.end_date,
328 p_inv_min_qty => l_recipe_vr_tbl.inv_min_qty,
329 p_inv_max_qty => l_recipe_vr_tbl.inv_max_qty,
330 p_manage_validity_rules => p_manage_validity_rules);
331 END IF;
332
333 GMD_RECIPE_DETAIL_PVT.create_recipe_vr (p_recipe_vr_rec => l_recipe_vr_tbl
334 ,p_recipe_vr_flex_rec => l_vr_flex
335 ,x_return_status => l_return_status);
336 IF l_return_status <> FND_API.g_ret_sts_success THEN
337
338 RAISE FND_API.g_exc_error;
339 ELSE
340 COMMIT WORK;
341 END IF;
342
343 GMD_API_GRP.get_status_details (V_entity_type => 'VALIDITY',
344 V_orgn_id => p_orgn_id,
345 X_entity_status => l_default_vr_status);
346
347 IF (l_default_vr_status.entity_status <> 100) THEN
348 l_vr_id := GMD_RECIPE_DETAIL_PVT.pkg_recipe_validity_rule_id;
349 IF p_event_signed THEN
350
351 UPDATE gmd_recipe_validity_rules
352 SET validity_rule_status = l_default_vr_status.entity_status
353 WHERE recipe_validity_rule_id = l_vr_id;
354 ELSE -- IF p_event_signed
355
356 GMD_STATUS_PUB.modify_status ( p_api_version => 1
357 , p_init_msg_list => TRUE
358 , p_entity_name => 'VALIDITY'
359 , p_entity_id => l_vr_id
360 , p_entity_no => NULL
361 , p_entity_version => NULL
362 , p_to_status => l_default_vr_status.entity_status
363 , p_ignore_flag => FALSE
364 , x_message_count => x_msg_count
365 , x_message_list => x_msg_data
366 , x_return_status => l_return_status);
367 IF l_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
368 RAISE fnd_api.g_exc_error;
369 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
370 END IF; -- IF p_event_signed
371 END IF;--l_entity_status.entity_status
372
373 OPEN Cur_get_validity_status (l_vr_id);
374 FETCH Cur_get_validity_status INTO X_end_status;
375 CLOSE Cur_get_validity_status;
376
377 EXCEPTION
378 WHEN FND_API.g_exc_error THEN
379 X_return_status := l_return_status;
380 WHEN OTHERS THEN
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 END create_validity_rule;
383
384
385 /*+========================================================================+
386 ** Name : create_recipe
387 ** Notes : This procedure receives as input autorecipe record and
388 ** creates recipe records. |
389 **
390 ** If everything is fine then OUT parameter
391 ** x_return_status is set to 'S' else appropriate
392 ** error message is put on the stack and error
393 ** is returned.
394 **
395 ** HISTORY
396 ** Ger Kelly 1 March Created.
397 ** PARAMETERS
398 ** p_formula_id
399 ** p_formula_status
400 ** p_orgn_code
401 ** kkillams 01-dec-2004 p_orgn_code parameter is replaced with p_orgn_id w.r.t. 4004501
402 ** Kapil M 03-JAN-2007 LCF-GMO ME : Bug#5458666- Added the parameters routing-id and pi-indicator
403 ** Passed the parameters to create_recipe.
404 **+========================================================================+*/
405
406
407 PROCEDURE create_recipe(p_formula_id IN NUMBER,
408 p_formula_status IN VARCHAR2,
409 p_orgn_id IN NUMBER,
410 x_end_status OUT NOCOPY VARCHAR2,
411 x_recipe_no OUT NOCOPY VARCHAR2,
412 x_recipe_version OUT NOCOPY NUMBER,
413 x_recipe_id OUT NOCOPY NUMBER,
414 x_return_status OUT NOCOPY VARCHAR2,
415 p_event_signed IN BOOLEAN,
416 p_routing_id IN NUMBER DEFAULT NULL,
417 p_enhanced_pi_ind IN VARCHAR2 DEFAULT NULL) IS
418
419
420 CURSOR Cur_get_recipe_status (V_recipe_no VARCHAR2, V_recipe_version NUMBER)IS
421 SELECT recipe_status
422 FROM gmd_recipes_b
423 WHERE recipe_no = V_recipe_no
424 AND recipe_version = V_recipe_version;
425
426 -- Kapil LCF-GMO ME : Bug#5458666
427 CURSOR Cur_get_routing_status(V_routing_id NUMBER) IS
428 SELECT ROUTING_STATUS
429 FROM GMD_ROUTINGS_B
430 WHERE routing_id = V_routing_id;
431 l_routing_status gmd_api_grp.status_rec_type;
432
433 CURSOR Cur_get_routing_details(V_routing_id NUMBER) IS
434 SELECT ROUTING_NO, ROUTING_VERS
435 FROM GMD_ROUTINGS_B
436 WHERE routing_id = V_routing_id;
437 l_routing_no VARCHAR2(32);
438 l_routing_vers NUMBER;
439
440 -- Local Variables
441 l_default_recipe_status gmd_api_grp.status_rec_type;
442 l_return_status VARCHAR2(1);
443 x_msg_count NUMBER;
444 x_msg_data VARCHAR2(2000);
445
446 -- Exceptions
447 create_recipe_err EXCEPTION;
448 default_status_err EXCEPTION;
449 BEGIN
450
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453 -- Kapil LCF-GMO ME : Bug#5458666
454 -- Pass the routing_id to retrieve the recipe details.
455 GMD_API_GRP.retrieve_recipe(p_formula_id,
456 p_routing_id,
457 l_recipe_tbl,
458 l_recipe_flex,
459 x_return_status);
460 IF x_return_status <> FND_API.g_ret_sts_success THEN
461 RAISE create_recipe_err;
462 END IF;
463 x_recipe_no := l_recipe_tbl.recipe_no;
464 x_recipe_version := l_recipe_tbl.recipe_version;
465 x_recipe_id := l_recipe_tbl.recipe_id;
466 -- Bug# 5206449 Kapil M
467 -- Setting status 100 and then modify the status
468 l_recipe_tbl.recipe_status := 100;
469
470 -- Kapil LCF-GMO ME : Bug#5458666
471 -- Pass the enhanced_pi_ind to create the recipe.
472 l_recipe_tbl.enhanced_pi_ind := p_enhanced_pi_ind;
473 GMD_RECIPE_HEADER_PVT.create_recipe_header (p_recipe_header_rec => l_recipe_tbl
474 ,p_recipe_hdr_flex_rec => l_recipe_flex
475 ,x_return_status => l_return_status);
476 IF l_return_status <> FND_API.g_ret_sts_success THEN
477 RAISE create_recipe_err;
478 ELSE
479 COMMIT WORK;
480 END IF;
481
482 -- Kapil LCF-GMO ME : Bug#5458666
483 -- To copy the PI to the Recipe levels from routing step level.
484 IF GMO_SETUP_GRP.IS_GMO_ENABLED = 'Y' THEN
485
486 GMD_PROCESS_INSTR_UTILS.COPY_PROCESS_INSTR (p_entity_name => 'RECIPE' ,
487 p_entity_id => l_recipe_tbl.recipe_id ,
488 x_return_status => l_return_status ,
489 x_msg_count => x_msg_count ,
490 x_msg_data => x_msg_data );
491
492 IF l_return_status <> 'S' THEN
493 RAISE create_recipe_err;
494 END IF;
495 END IF;
496
497
498 gmd_api_grp.get_status_details (V_entity_type => 'RECIPE',
499 V_orgn_id => p_orgn_id,
500 X_entity_status => l_default_recipe_status);
501
502 -- Kapil LCF-GMO ME : Bug#5458666
503 -- validation of Default Recipe status and Routing Status and raise the error accordingly.
504 IF p_routing_id IS NOT NULL THEN
505 -- get the routing Status
506 OPEN Cur_get_routing_status(p_routing_id);
507 FETCH Cur_get_routing_status INTO l_routing_status.entity_status;
508 CLOSE Cur_get_routing_status;
509 IF l_default_recipe_status.entity_status > l_routing_status.entity_status THEN
510 OPEN Cur_get_routing_details (p_routing_id);
511 FETCH Cur_get_routing_details INTO l_routing_no, l_routing_vers;
512 CLOSE Cur_get_routing_details ;
513 -- Raise the Note Message.
514 FND_MESSAGE.SET_NAME('GMD', 'GMD_DF_ROUTING_STAT_NOT_VALID');
515 FND_MESSAGE.SET_TOKEN('STATUS', l_default_recipe_status.entity_status);
516 FND_MESSAGE.SET_TOKEN('ROUTING_NO', l_routing_no);
517 FND_MESSAGE.SET_TOKEN('ROUTING_VERS', l_routing_vers);
518 FND_MSG_PUB.ADD;
519 X_return_status := FND_API.G_RET_STS_SUCCESS;
520 RETURN;
521 END IF;
522 END IF;
523
524 IF (l_default_recipe_status.entity_status <> 100) THEN
525 IF p_event_signed THEN
526 UPDATE gmd_recipes_b
527 SET recipe_status = l_default_recipe_status.entity_status
528 WHERE recipe_no = l_recipe_tbl.recipe_no
529 AND recipe_version = l_recipe_tbl.recipe_version;
530 ELSE -- IF p_event_signed
531 G_Create_Validity := TRUE;
532 GMD_STATUS_PUB.modify_status ( p_api_version => 1
533 , p_init_msg_list => TRUE
534 , p_entity_name => 'RECIPE'
535 , p_entity_id => NULL
536 , p_entity_no => l_recipe_tbl.recipe_no
537 , p_entity_version => l_recipe_tbl.recipe_version
538 , p_to_status => l_default_recipe_status.entity_status
539 , p_ignore_flag => FALSE
540 , x_message_count => x_msg_count
541 , x_message_list => x_msg_data
542 , x_return_status => l_return_status);
543 G_Create_Validity := FALSE;
544 IF l_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
545 RAISE default_status_err;
546 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
547 END IF; -- IF p_event_signed
548 END IF;--l_entity_status.entity_status
549
550 OPEN Cur_get_recipe_status (l_recipe_tbl.recipe_no, l_recipe_tbl.recipe_version);
551 FETCH Cur_get_recipe_status INTO X_end_status;
552 CLOSE Cur_get_recipe_status;
553
554 EXCEPTION
555 WHEN create_recipe_err OR default_status_err THEN
556 X_return_status := l_return_status;
557 END create_recipe;
558
559
560 /*+========================================================================+
561 ** Name : calculate_date
562 ** Notes : This procedure calculates the end date based on the num days
563 **
564 ** HISTORY
565 **
566 ** PARAMETERS
567 ** p_start_date
568 ** p_num_days
569 ** x_end_date
570 **+========================================================================+*/
571 PROCEDURE calculate_date (p_start_date IN DATE,
572 p_num_days IN NUMBER,
573 x_end_date OUT NOCOPY DATE) IS
574
575 l_num NUMBER;
576
577 l_date DATE;
578
579
580 BEGIN
581
582 l_num := p_num_days;
583 l_date := p_start_date;
584
585 x_end_date := (l_date + p_num_days + 1) - 1/86400;
586
587 END calculate_date;
588
589 /*+========================================================================+
590 ** Name : manage_existing_validity
591 ** Notes : This procedure is used to update the existing validity rules
592 ** based on the recipe generation setup record.
593 ** HISTORY
594 ** Thomas Daniel 1 June Created.
595 ** PARAMETERS
596 **
597 ** kkillams 01-dec-2004 p_orgn_code parameter is replaced with p_orgn_id w.r.t. 4004501
598 **+========================================================================+*/
599 PROCEDURE manage_existing_validity(p_item_id IN NUMBER,
600 p_orgn_id IN NUMBER,
601 p_recipe_use IN NUMBER,
602 p_start_date IN DATE,
603 p_end_date IN DATE,
604 p_inv_min_qty IN NUMBER,
605 p_inv_max_qty IN NUMBER,
606 p_manage_validity_rules IN VARCHAR2) IS
607 BEGIN
608 /* If Managing Validity Rules is set as First Preference */
609 IF p_manage_validity_rules = 1 THEN
610 /* We need to increase the preference for all the validity rules that are valid */
611 /* in the current validity rules validity dates */
612 UPDATE gmd_recipe_validity_rules
613 SET preference = preference + 1,
614 last_updated_by = g_user_id,
615 last_update_date = sysdate,
616 last_update_login = g_login_id
617 WHERE inventory_item_id = p_item_id
618 AND organization_id = p_orgn_id
619 AND recipe_use = p_recipe_use
620 AND NVL(end_date, p_start_date) >= p_start_date
621 AND start_date <= NVL(p_end_date, start_date)
622 AND inv_max_qty >= p_inv_min_qty
623 AND inv_min_qty <= p_inv_max_qty
624 AND validity_rule_status < 800
625 AND delete_mark = 0;
626 /* If Managing Validity Rules is set as End Date */
627 ELSIF p_manage_validity_rules = 2 THEN
628 /* We need to expire only those validity rules where the end date is less */
629 /* than or equal to the current validity rule */
630 UPDATE gmd_recipe_validity_rules
631 SET end_date = sysdate,
632 last_updated_by = g_user_id,
633 last_update_date = sysdate,
634 last_update_login = g_login_id
635 WHERE inventory_item_id = p_item_id
636 AND organization_id = p_orgn_id
637 AND recipe_use = p_recipe_use
638 AND ((end_date IS NULL AND p_end_date IS NULL) OR
639 (end_date <= NVL(p_end_date, end_date)))
640 AND NVL(end_date, p_start_date) >= p_start_date
641 AND inv_max_qty <= p_inv_max_qty
642 AND inv_max_qty >= p_inv_min_qty
643 AND validity_rule_status < 800
644 AND delete_mark = 0;
645 END IF;
646 END manage_existing_validity;
647
648 /*+========================================================================+
649 ** Name : create_validity
650 ** Notes : This function returns TRUE if validity rule has to be created.
651 ** This function will be invoked by GMD_ERES_UTILS to pass to the
652 ** post operation API of the recipe change status event.
653 ** HISTORY
654 **
655 ** PARAMETERS
656 **
657 **+========================================================================+*/
658 FUNCTION Create_Validity RETURN BOOLEAN IS
659 BEGIN
660 IF G_Create_Validity THEN
661 RETURN TRUE;
662 ELSE
663 RETURN FALSE;
664 END IF;
665 END Create_Validity;
666
667 END GMD_RECIPE_GENERATE;