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