[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_DESIGNER_PKG
Source
1 PACKAGE BODY GMD_FORMULA_DESIGNER_PKG AS
2 /* $Header: GMDFRDDB.pls 120.16.12000000.4 2007/05/02 10:49:04 kmotupal ship $ */
3 /*============================================================================
4 | Copyright (c) 2002 Oracle Corporation
5 | Redwood Shores, California, USA
6 | All rights reserved
7 ============================================================================================
8 | FILENAME
9 | GMDFRDDB.pls
10 |
11 | DESCRIPTION
12 | Package body containing the procedures used by the Formula Designer
13 |
14 | NOTES
15 |
16 | HISTORY
17 | 05-SEP-2002 Eddie Oumerretane Created.
18 | 02-APR-2003 Eddie Oumerretane Bug 2883871 Call version 2 of
19 | Update_FormulaDetail API.
20 | 16-APR-2003 Eddie Oumerretane. Implemented call to
21 | gmd_api_grp.Update_allowed_with_fmsec function in
22 | order to get formula user access defined in
23 | Formula Security.
24 | 30-MAR-2004 kkillams
25 | Modified Get_Formula_Mode procedure w.r.t. bug 3344335.
26 | 27-APR-2004 Sriram.S Bug# 3408799
27 | Added SET_DEFAULT_STATUS procedure for Default Status Build.
28 | 23-JUN-2004 Sriram.S Bug# 3702561
29 | Added validation to check for ingredient with zero qty in
30 | Validate_Formula_Details procedure.
31 | 23-JUN-2004 Sriram.S Bug# 3700829
32 | Added procedure CHECK_USR_HAS_FSEC_RESP to check if user has formula
33 | security responsibility (i-e) Product development security manager.
34 | 29-SEP-2004 Sriram.S Bug# 3761032
35 | Added validation to check for experimental items if formula status in
36 | in (600,700).
37 | 16-Dec-2005 TDaniel Bug#4771255
38 | Added code to handle the return status of Q in insert_formula_detail
39 | and update_formula_detail routines.
40 | 27-Jan-2006 TDaniel Bug#4720080
41 | Added code to pass back return code as "W" for validateCostAlloc.
42 | 05-FEB-2007 Kapil M Bug# 5716318
43 | Changes for Auto-Product Qty Calculation ME. Added the new procedure
44 | CALCULATE_TOTAL_PRODUCT_QTY and CHECK_AUTO_PRODUCT. Changed made to pass
45 | the newly added fields.
46 ==============================================================================================
47 */
48
49 /* Api start of comments
50 +============================================================================
51 | PROCEDURE NAME
52 | Get_Formula_Mode
53 |
54 | DESCRIPTION
55 | Determine whether the user has access to this formula and in which
56 | mode (view or update/create mode).
57 |
58 | INPUT PARAMETERS
59 | p_formula_id NUMBER
60 |
61 | OUTPUT PARAMETERS
62 | x_formula_mode VARCHAR2
63 | x_create_allowed VARCHAR2
64 | x_return_code VARCHAR2
65 | x_error_msg VARCHAR2
66 |
67 | HISTORY
68 | 05-SEP-2002 Eddie Oumerretane Created.
69 | 16-APR-2003 Eddie Oumerretane. Implemented call to
70 | gmd_api_grp.Update_allowed_with_fmsec function in
71 | order to get formula user access defined in
72 | Formula Security.
73 | 30-03-2004kkillamsCalling GMD_API_GRP.get_formula_access_type api to get the
74 | formula access type w.r.t. bug 3344335.
75 | 29-Jul-2005 Tdaniel Added organization_id for convergence changes.
76 +=============================================================================
77 Api end of comments
78 */
79 PROCEDURE Get_Formula_Mode (p_formula_id IN NUMBER,
80 p_organization_id IN NUMBER,
81 x_formula_mode OUT NOCOPY VARCHAR2,
82 x_create_allowed OUT NOCOPY VARCHAR2,
83 x_return_code OUT NOCOPY VARCHAR2,
84 x_error_msg OUT NOCOPY VARCHAR2) IS
85
86 l_return_code VARCHAR2(1);
87 l_status VARCHAR2(30);
88
89 --3344335
90 l_formula_access VARCHAR2(1);
91 l_fm_orgn_id fm_form_mst_b.owner_organization_id%TYPE;
92
93 --Get's the organization code of the formula.
94 CURSOR get_formula_orgn_code(vFormula_id NUMBER) IS
95 SELECT owner_organization_id
96 FROM fm_form_mst_b
97 WHERE formula_id = vFormula_id;
98
99 BEGIN
100
101 x_return_code := 'S';
102 x_error_msg := '';
103
104 --- Check user access to the given formula as defined in Formula Security
105 IF NVL(p_formula_id, -1) <> -1 THEN
106 OPEN get_formula_orgn_code(p_formula_id);
107 FETCH get_formula_orgn_code INTO l_fm_orgn_id;
108 CLOSE get_formula_orgn_code;
109 ELSE
110 l_fm_orgn_id := p_organization_id;
111 END IF;
112
113 -- to be changed
114 l_formula_access := 'U';
115 l_formula_access :=GMD_API_GRP.get_formula_access_type(p_formula_id => p_formula_id,
116 p_owner_organization_id => l_fm_orgn_id);
117
118 IF (l_formula_access ='U') THEN
119 --- Assume that user can update current formula and create new ones
120 x_formula_mode := 'U';
121 x_create_allowed := 'Y';
122 ELSE
123 x_formula_mode := 'Q';
124 x_create_allowed := 'N';
125 END IF;
126
127 --- If user has update access to the given formula based on Formula
128 --- Security, check whether he/she can actually update the formula based on
129 --- status, owning organization ...
130
131 IF x_formula_mode = 'U' THEN
132
133 IF GMD_COMMON_VAL.Update_Allowed(entity => 'FORMULA',
134 entity_id => p_formula_id) THEN
135 x_formula_mode := 'U';
136 ELSE
137 x_formula_mode := 'Q';
138 END IF;
139
140 END IF;
141
142 END Get_Formula_Mode;
143
144 /* Api start of comments
145 +============================================================================
146 | PROCEDURE NAME
147 | Is_Formula_Used_In_Recipes
148 |
149 | DESCRIPTION
150 | Determine whether the formula is used in one or more recipes.
151 |
152 | INPUT PARAMETERS
153 | p_formula_id NUMBER
154 |
155 | OUTPUT PARAMETERS
156 | x_used_in_recipes VARCHAR2(1)
157 | x_return_code VARCHAR2(1)
158 | x_error_msg VARCHAR2(100)
159 |
160 | HISTORY
161 | 05-SEP-2002 Eddie Oumerretane Created.
162 |
163 +=============================================================================
164 Api end of comments
165 */
166 PROCEDURE Is_Formula_Used_In_Recipes (p_formula_id IN NUMBER,
167 x_used_in_recipes OUT NOCOPY VARCHAR2,
168 x_return_code OUT NOCOPY VARCHAR2,
169 x_error_msg OUT NOCOPY VARCHAR2) IS
170
171 BEGIN
172
173 x_return_code := 'S';
174 x_error_msg := '';
175
176 x_used_in_recipes := 'N';
177
178 -- Return TRUE if this formula is used by one or more recipes
179 IF NOT GMD_STATUS_CODE.Check_Parent_Status('FORMULA', p_formula_id) THEN
180 x_used_in_recipes := 'Y';
181 END IF;
182
183 END Is_Formula_Used_In_Recipes;
184
185 /* Api start of comments
186 +============================================================================
187 | PROCEDURE NAME
188 | Update_Formula_Header
189 |
190 | DESCRIPTION
191 | Update formula header
192 |
193 | INPUT PARAMETERS
194 | p_formula_id NUMBER
195 | p_formula_no VARCHAR2
196 | p_formula_vers NUMBER
197 | p_formula_desc VARCHAR2
198 | p_formula_desc2 VARCHAR2
199 | p_formula_status VARCHAR2
200 | p_formula_class VARCHAR2
201 | p_owner_organization_id NUMBER
202 | p_owner_id NUMBER
203 | p_formula_type NUMBER
204 | p_scale_type NUMBER
205 | p_text_code NUMBER
206 | p_last_update_date DATE
207 | p_user_id NUMBER
208 | p_last_update_date_orig DATE
209 | p_auto_product_calc VARCHAR2
210 |
211 | OUTPUT PARAMETERS
212 | x_return_code VARCHAR2(1)
213 | x_error_msg VARCHAR2(100)
214 |
215 | HISTORY
216 | 05-SEP-2002 Eddie Oumerretane Created.
217 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
218 | Added the new column auto_product_calc
219 +=============================================================================
220 Api end of comments
221 */
222 PROCEDURE Update_Formula_Header ( p_formula_id IN NUMBER,
223 p_formula_no IN VARCHAR2,
224 p_formula_vers IN NUMBER,
225 p_formula_desc IN VARCHAR2,
226 p_formula_desc2 IN VARCHAR2,
227 p_formula_status IN VARCHAR2,
228 p_formula_class IN VARCHAR2,
229 p_owner_organization_id IN NUMBER,
230 p_owner_id IN NUMBER,
231 p_formula_type IN NUMBER,
232 p_scale_type IN NUMBER,
233 p_text_code IN NUMBER,
234 p_last_update_date IN DATE,
235 p_user_id IN NUMBER,
236 p_last_update_date_orig IN DATE,
237 p_auto_product_calc IN VARCHAR2,
238 x_return_code OUT NOCOPY VARCHAR2,
239 x_error_msg OUT NOCOPY VARCHAR2) IS
240 CURSOR Cur_get_formula IS
241 SELECT *
242 FROM fm_form_mst
243 WHERE formula_id = p_formula_id AND
244 last_update_date = p_last_update_date_orig;
245
246 UPDATE_FORMULA_EXCEPTION EXCEPTION;
247 RECORD_CHANGED_EXCEPTION EXCEPTION;
248 l_text_code NUMBER(10);
249 l_rec Cur_get_formula%ROWTYPE;
250 l_return_status VARCHAR2(2);
251 l_message_count NUMBER;
252 l_msg_data VARCHAR2(2000);
253 l_message VARCHAR2(1000);
254 l_dummy NUMBER;
255 l_update_table GMD_FORMULA_PUB.formula_update_hdr_tbl_type;
256
257 BEGIN
258
259 x_error_msg := '';
260 x_return_code := FND_API.G_RET_STS_SUCCESS;
261
262
263 OPEN Cur_get_formula;
264 FETCH Cur_get_formula INTO l_rec;
265
266 IF Cur_get_formula%NOTFOUND THEN
267 CLOSE Cur_get_formula;
268 RAISE RECORD_CHANGED_EXCEPTION;
269 END IF;
270
271 CLOSE Cur_get_formula;
272
273 IF p_text_code <= 0 THEN
274 l_text_code := NULL;
275 ELSE
276 l_text_code := p_text_code;
277 END IF;
278
279 l_update_table(1).fmcontrol_class := l_rec.fmcontrol_class;
280 l_update_table(1).inactive_ind := l_rec.inactive_ind;
281 l_update_table(1).total_input_qty := l_rec.total_input_qty;
282 l_update_table(1).total_output_qty := l_rec.total_output_qty;
283 l_update_table(1).yield_uom := l_rec.yield_uom;
284 l_update_table(1).formula_id := p_formula_id;
285 l_update_table(1).formula_no := l_rec.formula_no;
286 l_update_table(1).formula_vers := l_rec.formula_vers;
287 l_update_table(1).formula_desc1 := p_formula_desc;
288 l_update_table(1).formula_desc2 := p_formula_desc2;
289 l_update_table(1).formula_status := p_formula_status;
290 l_update_table(1).formula_class := p_formula_class;
291 -- l_update_table(1).orgn_code := p_owner_organization;
292 -- Commented the above line and added below for NPD Conv.
293 l_update_table(1).owner_organization_id := p_owner_organization_id;
294 -- l_rec.owner_organization_id;
295 l_update_table(1).owner_id := p_owner_id;
296 l_update_table(1).user_id := p_user_id;
297 l_update_table(1).formula_type := p_formula_type;
298 l_update_table(1).scale_type_hdr := p_scale_type;
299 l_update_table(1).text_code_hdr := l_text_code;
300 l_update_table(1).last_update_date := p_last_update_date;
301 l_update_table(1).last_updated_by := p_user_id;
302 l_update_table(1).last_update_login := p_user_id;
303
304 l_update_table(1).delete_mark := l_rec.delete_mark;
305 l_update_table(1).created_by := l_rec.created_by;
306 l_update_table(1).creation_date := l_rec.last_update_date;
307 l_update_table(1).attribute1 := l_rec.attribute1;
308 l_update_table(1).attribute2 := l_rec.attribute2;
309 l_update_table(1).attribute3 := l_rec.attribute3;
310 l_update_table(1).attribute4 := l_rec.attribute4;
311 l_update_table(1).attribute5 := l_rec.attribute5;
312 l_update_table(1).attribute6 := l_rec.attribute6;
313 l_update_table(1).attribute7 := l_rec.attribute7;
314 l_update_table(1).attribute8 := l_rec.attribute8;
315 l_update_table(1).attribute9 := l_rec.attribute9;
316 l_update_table(1).attribute10 := l_rec.attribute10;
317 l_update_table(1).attribute11 := l_rec.attribute11;
318 l_update_table(1).attribute12 := l_rec.attribute12;
319 l_update_table(1).attribute13 := l_rec.attribute13;
320 l_update_table(1).attribute14 := l_rec.attribute14;
321 l_update_table(1).attribute15 := l_rec.attribute15;
322 l_update_table(1).attribute16 := l_rec.attribute16;
323 l_update_table(1).attribute17 := l_rec.attribute17;
324 l_update_table(1).attribute18 := l_rec.attribute18;
325 l_update_table(1).attribute19 := l_rec.attribute19;
326 l_update_table(1).attribute20 := l_rec.attribute20;
327 l_update_table(1).attribute21 := l_rec.attribute21;
328 l_update_table(1).attribute22 := l_rec.attribute22;
329 l_update_table(1).attribute23 := l_rec.attribute23;
330 l_update_table(1).attribute24 := l_rec.attribute24;
331 l_update_table(1).attribute25 := l_rec.attribute25;
332 l_update_table(1).attribute26 := l_rec.attribute26;
333 l_update_table(1).attribute27 := l_rec.attribute27;
334 l_update_table(1).attribute28 := l_rec.attribute28;
335 l_update_table(1).attribute29 := l_rec.attribute29;
336 l_update_table(1).attribute30 := l_rec.attribute30;
337 l_update_table(1).attribute_category := l_rec.attribute_category;
338 -- Kapil ME Auto-Prod
339 l_update_table(1).auto_product_calc := p_auto_product_calc;
340
341 GMD_FORMULA_PUB.Update_FormulaHeader
342 ( p_api_version => 2
343 , p_init_msg_list => FND_API.G_TRUE
344 , p_commit => FND_API.G_FALSE
345 , p_called_from_forms => 'YES'
346 , x_return_status => l_return_status
347 , x_msg_count => l_message_count
348 , x_msg_data => l_msg_data
349 , p_formula_header_tbl => l_update_table);
350
351 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
352 RAISE UPDATE_FORMULA_EXCEPTION;
353 END IF;
354
355 --- If formula number and/or version have changed, we need to update them. This
356 --- happens when creating a new formula, because a dummy formula header is created in
357 --- the database. User is then prompted to enter a valid formula number/version prior
358 --- to saving.
359
360 IF l_rec.formula_no <> p_formula_no OR
361 l_rec.formula_vers <> p_formula_vers THEN
362
363 UPDATE
364 FM_FORM_MST_B
365 SET
366 formula_no = p_formula_no,
367 formula_vers = p_formula_vers
368 WHERE
369 formula_id = p_formula_id;
370
371 END IF;
372
373
374 EXCEPTION
375 WHEN UPDATE_FORMULA_EXCEPTION THEN
376 FND_MSG_PUB.GET( p_msg_index => 1,
377 p_data => l_message,
378 p_encoded => 'F',
379 p_msg_index_out => l_dummy);
380
381 x_return_code := 'F';
382 x_error_msg := l_message;
383
384 WHEN RECORD_CHANGED_EXCEPTION THEN
385 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
386 x_return_code := 'F';
387 x_error_msg := gmd_api_grp.get_message;
388
389 WHEN OTHERS THEN
390 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
391 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
392 x_return_code := 'F';
393 x_error_msg := gmd_api_grp.get_message;
394
395 END Update_Formula_Header;
396
397 /* Api start of comments
398 +============================================================================
399 | PROCEDURE NAME
400 | Insert_Formula_Detail
401 |
402 | DESCRIPTION
403 | Create a formula line
404 |
405 | INPUT PARAMETERS
406 | p_formula_id IN NUMBER
407 | p_formulaline_id IN NUMBER
408 | p_line_type IN NUMBER
409 | p_line_no IN NUMBER
410 | p_item_id IN NUMBER
411 | p_item_no IN VARCHAR2
412 | p_qty IN NUMBER
413 | p_item_um IN VARCHAR2
414 | p_release_type IN NUMBER
415 | p_scrap_factor IN NUMBER
416 | p_scale_type IN NUMBER
417 | p_cost_alloc IN NUMBER
418 | p_phantom_type IN NUMBER
419 | p_rework_type IN NUMBER
420 | p_text_code IN NUMBER
421 | p_tp_formula_id IN NUMBER
422 | p_iaformula_id IN NUMBER
423 | p_scale_uom IN VARCHAR2
424 | p_contribute_step_qty_ind IN VARCHAR2
425 | p_contribute_yield_ind IN VARCHAR2
426 | p_scale_multiple IN NUMBER
427 | p_scale_rounding_variance IN NUMBER
428 | p_rounding_direction IN NUMBER
429 | p_by_product_type IN VARCHAR2
430 | p_text_code IN NUMBER
431 | p_last_update_date IN DATE
432 | p_user_id IN NUMBER
433 | p_prod_percent IN NUMBER
434 |
435 | OUTPUT PARAMETERS
436 | x_return_code VARCHAR2(1)
437 | x_error_msg VARCHAR2(100)
438 |
439 | HISTORY
440 | 05-SEP-2002 Eddie Oumerretane Created.
441 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
442 | Added the new column prod_percent
443 |
444 +=============================================================================
445 Api end of comments
446 */
447 PROCEDURE Insert_Formula_Detail ( p_formula_id IN NUMBER
448 ,p_formulaline_id IN NUMBER
449 ,p_line_type IN NUMBER
450 ,p_line_no IN NUMBER
451 ,p_item_id IN NUMBER
452 ,p_item_no IN VARCHAR2
453 ,p_revision IN VARCHAR2
454 ,p_qty IN NUMBER
455 ,p_item_um IN VARCHAR2
456 ,p_release_type IN NUMBER
457 ,p_scrap_factor IN NUMBER
458 ,p_scale_type IN NUMBER
459 ,p_cost_alloc IN NUMBER
460 ,p_phantom_type IN NUMBER
461 ,p_rework_type IN NUMBER
462 ,p_text_code IN NUMBER
463 ,p_tp_formula_id IN NUMBER
464 ,p_iaformula_id IN NUMBER
465 ,p_scale_uom IN VARCHAR2
466 ,p_contribute_step_qty_ind IN VARCHAR2
467 ,p_contribute_yield_ind IN VARCHAR2
468 ,p_scale_multiple IN NUMBER
469 ,p_scale_rounding_variance IN NUMBER
470 ,p_rounding_direction IN NUMBER
471 ,p_by_product_type IN VARCHAR2
472 ,p_last_update_date IN DATE
473 ,p_user_id IN NUMBER
474 ,p_prod_percent IN NUMBER
475 ,x_return_code OUT NOCOPY VARCHAR2
476 ,x_error_msg OUT NOCOPY VARCHAR2) IS
477
478 CURSOR Cur_get_formula IS
479 SELECT formula_no,
480 formula_vers , auto_product_calc -- Kapil ME Auto-Prod
481 FROM fm_form_mst
482 WHERE formula_id = p_formula_id;
483
484 l_auto_product_calc VARCHAR2(1);
485
486 l_text_code NUMBER(10);
487 l_return_status VARCHAR2(2);
488 l_message_count NUMBER;
489 l_message_list VARCHAR2(2000);
490 l_message VARCHAR2(1000);
491 l_dummy NUMBER;
492 INSERT_DTL_EXCEPTION EXCEPTION;
493 RECORD_CHANGED_EXCEPTION EXCEPTION;
494 l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
495
496 BEGIN
497
498 SAVEPOINT Add_Item;
499
500 x_error_msg := '';
501 x_return_code := FND_API.G_RET_STS_SUCCESS;
502
503 IF p_text_code <= 0 THEN
504 l_text_code := NULL;
505 ELSE
506 l_text_code := p_text_code;
507 END IF;
508
509 l_formula_dtl_rec(1).record_type := 'I';
510
511 OPEN Cur_get_formula;
512 FETCH Cur_get_formula INTO l_formula_dtl_rec(1).formula_no,
513 l_formula_dtl_rec(1).formula_vers,
514 l_auto_product_calc;
515
516 IF Cur_get_formula%NOTFOUND THEN
517 CLOSE Cur_get_formula;
518 RAISE RECORD_CHANGED_EXCEPTION;
519 END IF;
520
521 CLOSE Cur_get_formula;
522
523 IF p_tp_formula_id < 0 THEN
524 l_formula_dtl_rec(1).tpformula_id := NULL;
525 ELSE
526 l_formula_dtl_rec(1).tpformula_id := p_tp_formula_id;
527 END IF;
528
529 IF p_iaformula_id < 0 THEN
530 l_formula_dtl_rec(1).iaformula_id := NULL;
531 ELSE
532 l_formula_dtl_rec(1).iaformula_id := p_iaformula_id;
533 END IF;
534
535 IF p_rework_type < -1 THEN
536 l_formula_dtl_rec(1).rework_type := NULL;
537 ELSE
538 l_formula_dtl_rec(1).rework_type := p_rework_type;
539 END IF;
540
541 -- Scaling attributes are relevant if Integer scale type selected
542 IF p_scale_type = 2 THEN
543 l_formula_dtl_rec(1).scale_multiple := p_scale_multiple;
544 l_formula_dtl_rec(1).scale_rounding_variance := p_scale_rounding_variance;
545 l_formula_dtl_rec(1).rounding_direction := p_rounding_direction;
546 IF p_scale_uom = ' 'THEN
547 l_formula_dtl_rec(1).scale_uom := NULL;
548 ELSE
549 l_formula_dtl_rec(1).scale_uom := p_scale_uom;
550 END IF;
551 ELSE
552 l_formula_dtl_rec(1).scale_multiple := NULL;
553 l_formula_dtl_rec(1).scale_rounding_variance := NULL;
554 l_formula_dtl_rec(1).rounding_direction := NULL;
555 l_formula_dtl_rec(1).scale_uom := NULL;
556 END IF;
557
558 -- Kapil ME Auto-Prod
559 -- Rework for Bug# 5903531 and 5903157
560 IF (p_line_type = 1 AND p_scale_type = 1) AND l_auto_product_calc = 'Y' THEN
561 l_formula_dtl_rec(1).prod_percent := p_prod_percent;
562 ELSE
563 l_formula_dtl_rec(1).prod_percent := NULL;
564 END IF;
565
566 l_formula_dtl_rec(1).formula_id := p_formula_id;
567 l_formula_dtl_rec(1).item_no := p_item_no;
568 l_formula_dtl_rec(1).revision := TRIM(p_revision);
569 l_formula_dtl_rec(1).user_id := p_user_id;
570 l_formula_dtl_rec(1).text_code_dtl := l_text_code;
571 l_formula_dtl_rec(1).formulaline_id := p_formulaline_id;
572 l_formula_dtl_rec(1).line_type := p_line_type;
573 l_formula_dtl_rec(1).line_no := p_line_no;
574 l_formula_dtl_rec(1).qty := p_qty;
575 l_formula_dtl_rec(1).detail_uom := p_item_um; -- NPD Conv.
576 l_formula_dtl_rec(1).release_type := p_release_type;
577 l_formula_dtl_rec(1).scrap_factor := p_scrap_factor;
578 l_formula_dtl_rec(1).scale_type_dtl := p_scale_type;
579 l_formula_dtl_rec(1).cost_alloc := p_cost_alloc;
580 l_formula_dtl_rec(1).phantom_type := p_phantom_type;
581 l_formula_dtl_rec(1).last_updated_by := p_user_id;
582 l_formula_dtl_rec(1).created_by := p_user_id;
583 l_formula_dtl_rec(1).last_update_date := p_last_update_date;
584 l_formula_dtl_rec(1).creation_date := p_last_update_date;
585 l_formula_dtl_rec(1).last_update_login := p_user_id;
586 l_formula_dtl_rec(1).contribute_step_qty_ind := p_contribute_step_qty_ind;
587 l_formula_dtl_rec(1).contribute_yield_ind := p_contribute_yield_ind;
588
589 IF p_by_product_type = ' ' OR p_line_type <> 2 THEN
590 l_formula_dtl_rec(1).by_product_type := NULL;
591 ELSE
592 l_formula_dtl_rec(1).by_product_type := p_by_product_type;
593 END IF;
594
595 GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail
596 ( p_api_version => 1.0
597 , p_init_msg_list => FND_API.G_TRUE
598 , p_commit => FND_API.G_FALSE
599 , p_called_from_forms => 'NO'
600 , x_return_status => l_return_status
601 , x_msg_count => l_message_count
602 , x_msg_data => l_message_list
603 , p_formula_detail_tbl => l_formula_dtl_rec);
604
605 /*B4771255 Changed the return status checking to include the toq warning */
606 IF (l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, 'Q')) THEN
607 RAISE INSERT_DTL_EXCEPTION;
608 END IF;
609
610 EXCEPTION
611 WHEN INSERT_DTL_EXCEPTION THEN
612 ROLLBACK TO Add_Item;
613 FND_MSG_PUB.GET( p_msg_index => 1,
614 p_data => l_message,
615 p_encoded => 'F',
616 p_msg_index_out => l_dummy);
617
618 x_return_code := 'F';
619 x_error_msg := l_message;
620
621 WHEN RECORD_CHANGED_EXCEPTION THEN
622 ROLLBACK TO Add_Item;
623 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
624 x_return_code := 'F';
625 x_error_msg := gmd_api_grp.get_message;
626
627 WHEN OTHERS THEN
628 ROLLBACK TO Add_Item;
629 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
630 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
631 x_return_code := 'F';
632 x_error_msg := gmd_api_grp.get_message;
633
634 END Insert_Formula_Detail;
635
636 /* Api start of comments
637 +============================================================================
638 | PROCEDURE NAME
639 | Update_Formula_Detail
640 |
641 | DESCRIPTION
642 | Update formula detail line
643 |
644 | INPUT PARAMETERS
645 | p_formula_id IN NUMBER
646 | p_formulaline_id IN NUMBER
647 | p_line_type IN NUMBER
648 | p_line_no IN NUMBER
649 | p_item_id IN NUMBER
650 | p_item_no IN VARCHAR2
651 | p_qty IN NUMBER
652 | p_item_um IN VARCHAR2
653 | p_release_type IN NUMBER
654 | p_scrap_factor IN NUMBER
655 | p_scale_type IN NUMBER
656 | p_cost_alloc IN NUMBER
657 | p_phantom_type IN NUMBER
658 | p_rework_type IN NUMBER
659 | p_text_code IN NUMBER
660 | p_tp_formula_id IN NUMBER
661 | p_iaformula_id IN NUMBER
662 | p_scale_uom IN VARCHAR2
663 | p_contribute_step_qty_ind IN VARCHAR2
664 | p_contribute_yield_ind IN VARCHAR2
665 | p_scale_multiple IN NUMBER
666 | p_scale_rounding_variance IN NUMBER
667 | p_rounding_direction IN NUMBER
668 | p_by_product_type IN VARCHAR2
669 | p_text_code IN NUMBER
670 | p_last_update_date IN DATE
671 | p_user_id IN NUMBER
672 | p_text_code IN NUMBER
673 | p_last_update_date IN DATE
674 | p_user_id IN NUMBER
675 | p_last_update_date_orig IN DATE
676 | p_prod_percent IN NUMBER
677 |
678 | OUTPUT PARAMETERS
679 | x_return_code VARCHAR2(1)
680 | x_error_msg VARCHAR2(100)
681 |
682 | HISTORY
683 | 05-SEP-2002 Eddie Oumerretane Created.
684 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
685 | Added the new column prod_percent
686 |
687 |
688 +=============================================================================
689 Api end of comments
690 */
691 PROCEDURE Update_Formula_Detail ( p_formula_id IN NUMBER
692 ,p_formulaline_id IN NUMBER
693 ,p_line_type IN NUMBER
694 ,p_line_no IN NUMBER
695 ,p_item_id IN NUMBER
696 ,p_item_no IN VARCHAR2
697 ,p_revision IN VARCHAR2
698 ,p_qty IN NUMBER
699 ,p_item_um IN VARCHAR2
700 ,p_release_type IN NUMBER
701 ,p_scrap_factor IN NUMBER
702 ,p_scale_type IN NUMBER
703 ,p_cost_alloc IN NUMBER
704 ,p_phantom_type IN NUMBER
705 ,p_rework_type IN NUMBER
706 ,p_text_code IN NUMBER
707 ,p_tp_formula_id IN NUMBER
708 ,p_iaformula_id IN NUMBER
709 ,p_scale_uom IN VARCHAR2
710 ,p_contribute_step_qty_ind IN VARCHAR2
711 ,p_contribute_yield_ind IN VARCHAR2
712 ,p_scale_multiple IN NUMBER
713 ,p_scale_rounding_variance IN NUMBER
714 ,p_rounding_direction IN NUMBER
715 ,p_by_product_type IN VARCHAR2
716 ,p_last_update_date IN DATE
717 ,p_user_id IN NUMBER
718 ,p_last_update_date_orig IN DATE
719 ,p_prod_percent IN NUMBER
720 ,x_return_code OUT NOCOPY VARCHAR2
721 ,x_error_msg OUT NOCOPY VARCHAR2) IS
722
723 l_text_code NUMBER(10);
724 l_return_status VARCHAR2(2);
725 l_message_count NUMBER;
726 l_message_list VARCHAR2(2000);
727 l_message VARCHAR2(1000);
728 l_dummy NUMBER;
729 l_qty NUMBER;
730 l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
731 UPDATE_DTL_EXCEPTION EXCEPTION;
732 RECORD_CHANGED_EXCEPTION EXCEPTION;
733
734 CURSOR Cur_get_formula IS
735 SELECT formula_no,
736 formula_vers, auto_product_calc -- Kapil ME Auto-Prod
737 FROM fm_form_mst
738 WHERE formula_id = p_formula_id;
739
740 CURSOR Cur_get_line IS
741 SELECT *
742 FROM fm_matl_dtl
743 WHERE formulaline_id = p_formulaline_id AND
744 last_update_date = p_last_update_date_orig;
745
746 l_line_rec Cur_get_line%ROWTYPE;
747
748 l_auto_product_calc VARCHAR2(1);
749
750 FUNCTION Get_Scaled_Qty (p_line_no NUMBER,
751 p_item_id NUMBER,
752 p_line_type NUMBER) RETURN NUMBER IS
753
754 l_scaled_qty NUMBER;
755
756 BEGIN
757
758 FOR i IN 1.. G_SCALE_REC.COUNT LOOP
759
760 IF G_SCALE_REC(i).line_no = p_line_no AND
761 G_SCALE_REC(i).inventory_item_id = p_item_id AND -- NPD Conv.
762 G_SCALE_REC(i).line_type = p_line_type THEN
763 l_scaled_qty := G_SCALE_REC(i).qty;
764 G_SCALE_REC(i).line_no := -1;
765 G_SCALE_REC(i).inventory_item_id := -1; -- NPD Conv.
766 EXIT;
767 END IF;
768
769 END LOOP;
770
771 RETURN l_scaled_qty;
772
773 END Get_Scaled_Qty;
774
775
776 BEGIN
777
778 x_error_msg := '';
779 x_return_code := FND_API.G_RET_STS_SUCCESS;
780
781 IF p_last_update_date_orig IS NOT NULL THEN
782
783 OPEN Cur_get_line;
784 FETCH Cur_get_line INTO l_line_rec;
785
786 IF Cur_get_line%NOTFOUND THEN
787 CLOSE Cur_get_line;
788 RAISE RECORD_CHANGED_EXCEPTION;
789 END IF;
790 CLOSE Cur_get_line;
791
792 END IF;
793
794
795 IF p_text_code <= 0 THEN
796 l_text_code := NULL;
797 ELSE
798 l_text_code := p_text_code;
799 END IF;
800
801 l_formula_dtl_rec(1).record_type := 'U';
802
803 OPEN Cur_get_formula;
804 FETCH Cur_get_formula INTO l_formula_dtl_rec(1).formula_no,
805 l_formula_dtl_rec(1).formula_vers,
806 l_auto_product_calc;
807
808 IF Cur_get_formula%NOTFOUND THEN
809 CLOSE Cur_get_formula;
810 RAISE RECORD_CHANGED_EXCEPTION;
811 END IF;
812
813 CLOSE Cur_get_formula;
814
815 --- G_SCALE_REC contains scaled qties after the user has performed a
816 --- scale or a theoretical yield.
817 IF G_SCALE_REC.COUNT > 0 THEN
818
819 l_qty := Get_Scaled_Qty (p_line_no,
820 p_item_id,
821 p_line_type);
822 ELSE
823 l_qty := p_qty;
824 END IF;
825
826 IF p_tp_formula_id < 0 THEN
827 l_formula_dtl_rec(1).tpformula_id := NULL;
828 ELSE
829 l_formula_dtl_rec(1).tpformula_id := p_tp_formula_id;
830 END IF;
831
832 IF P_iaformula_id < 0 THEN
833 l_formula_dtl_rec(1).iaformula_id := NULL;
834 ELSE
835 l_formula_dtl_rec(1).iaformula_id := p_iaformula_id;
836 END IF;
837
838 -- Scaling attributes are relevant if Integer scale type selected
839 IF p_scale_type = 2 THEN
840 l_formula_dtl_rec(1).scale_multiple := p_scale_multiple;
841 l_formula_dtl_rec(1).scale_rounding_variance := p_scale_rounding_variance;
842 l_formula_dtl_rec(1).rounding_direction := p_rounding_direction;
843 IF p_scale_uom = ' 'THEN
844 l_formula_dtl_rec(1).scale_uom := NULL;
845 ELSE
846 l_formula_dtl_rec(1).scale_uom := p_scale_uom;
847 END IF;
848 ELSE
849 l_formula_dtl_rec(1).scale_multiple := NULL;
850 l_formula_dtl_rec(1).scale_rounding_variance := NULL;
851 l_formula_dtl_rec(1).rounding_direction := NULL;
852 l_formula_dtl_rec(1).scale_uom := NULL;
853 END IF;
854
855 l_formula_dtl_rec(1).formula_id := p_formula_id;
856 l_formula_dtl_rec(1).item_no := p_item_no;
857 l_formula_dtl_rec(1).revision := TRIM(p_revision);
858 l_formula_dtl_rec(1).user_id := p_user_id;
859 l_formula_dtl_rec(1).text_code_dtl := l_text_code;
860 l_formula_dtl_rec(1).formulaline_id := p_formulaline_id;
861 l_formula_dtl_rec(1).line_type := p_line_type;
862 l_formula_dtl_rec(1).line_no := p_line_no;
863 l_formula_dtl_rec(1).qty := l_qty;
864 l_formula_dtl_rec(1).detail_uom := p_item_um; --NPD Conv.
865 l_formula_dtl_rec(1).release_type := p_release_type;
866 l_formula_dtl_rec(1).scrap_factor := p_scrap_factor;
867 l_formula_dtl_rec(1).scale_type_dtl := p_scale_type;
868 l_formula_dtl_rec(1).cost_alloc := p_cost_alloc;
869 l_formula_dtl_rec(1).rework_type := p_rework_type;
870 l_formula_dtl_rec(1).phantom_type := p_phantom_type;
871 l_formula_dtl_rec(1).last_updated_by := p_user_id;
872 l_formula_dtl_rec(1).created_by := p_user_id;
873 l_formula_dtl_rec(1).last_update_date := p_last_update_date;
874 l_formula_dtl_rec(1).creation_date := p_last_update_date;
875 l_formula_dtl_rec(1).last_update_login := p_user_id;
876 l_formula_dtl_rec(1).contribute_step_qty_ind := p_contribute_step_qty_ind;
877 l_formula_dtl_rec(1).contribute_yield_ind := p_contribute_yield_ind;
878
879 IF p_by_product_type = ' ' OR p_line_type <> 2 THEN
880 l_formula_dtl_rec(1).by_product_type := NULL;
881 ELSE
882 l_formula_dtl_rec(1).by_product_type := p_by_product_type;
883 END IF;
884
885 -- Kapil ME Auto-Prod :Bug# 5716318
886 /* Validations for the the value passed from the Designer */
887 IF (p_line_type = 1 AND p_scale_type = 1 ) AND l_auto_product_calc = 'Y' THEN
888 l_formula_dtl_rec(1).prod_percent := p_prod_percent;
889 ELSE
890 l_formula_dtl_rec(1).prod_percent := NULL;
891 END IF;
892
893 /* If Auto-Product Qty calculation is set then, all Percentage value are made 0
894 This is called when the user sets the parameter and enters the % value for a
895 product. Then for other products, the % is made 0 so that later validation does
896 not fail. */
897 if l_auto_product_calc = 'Y' THEN
898 update fm_matl_dtl
899 set prod_percent = 0
900 where formula_id = p_formula_id
901 and formulaline_id <> p_formulaline_id
902 and line_type = 1
903 and scale_type <> 0
904 and prod_percent IS NULL ;
905 END if;
906
907
908 --- p_called_from_forms parameter is set to 'Yes' so that the API does not
909 --- perform validation on line number. This validation fails when
910 --- resequencing item line numbers.
911 GMD_FORMULA_DETAIL_PUB.Update_FormulaDetail
912 ( p_api_version => 2
913 , p_init_msg_list => FND_API.G_TRUE
914 , p_commit => FND_API.G_FALSE
915 , p_called_from_forms => 'YES'
916 , x_return_status => l_return_status
917 , x_msg_count => l_message_count
918 , x_msg_data => l_message_list
919 , p_formula_detail_tbl => l_formula_dtl_rec);
920
921 /*B4771255 Changed the return status checking to include the toq warning */
922 IF (l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS, 'Q')) THEN
923 RAISE UPDATE_DTL_EXCEPTION;
924 END IF;
925
926
927 EXCEPTION
928 WHEN UPDATE_DTL_EXCEPTION THEN
929 FND_MSG_PUB.GET( p_msg_index => 1,
930 p_data => l_message,
931 p_encoded => 'F',
932 p_msg_index_out => l_dummy);
933
934 x_return_code := 'F';
935 x_error_msg := l_message;
936
937 WHEN RECORD_CHANGED_EXCEPTION THEN
938 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
939 x_return_code := 'F';
940 x_error_msg := gmd_api_grp.get_message;
941
942 WHEN OTHERS THEN
943 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
944 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
945 x_return_code := 'F';
946 x_error_msg := gmd_api_grp.get_message;
947
948 END Update_Formula_Detail;
949
950 /* Api start of comments
951 +============================================================================
952 | PROCEDURE NAME
953 | Create_formula_Header
954 |
955 | DESCRIPTION
956 | Create formula header
957 |
958 | INPUT PARAMETERS
959 | p_user_id IN NUMBER
960 |
961 | OUTPUT PARAMETERS
962 | x_formula_id NUMBER
963 | x_return_code VARCHAR2(1)
964 | x_error_msg VARCHAR2(100)
965 |
966 | HISTORY
967 | 05-SEP-2002 Eddie Oumerretane Created.
968 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
969 | Added the new column auto_product_calc
970 |
971 +=============================================================================
972 Api end of comments
973 */
974 PROCEDURE Create_Formula_Header ( p_formula_no IN VARCHAR2,
975 p_formula_vers IN NUMBER,
976 p_formula_desc IN VARCHAR2,
977 p_formula_desc2 IN VARCHAR2,
978 p_formula_class IN VARCHAR2,
979 p_owner_organization_id IN NUMBER,
980 p_owner_id IN NUMBER,
981 p_formula_type IN NUMBER,
982 p_scale_type IN NUMBER,
983 p_text_code IN NUMBER,
984 p_last_update_date IN DATE,
985 p_auto_product_calc IN VARCHAR2,
986 x_formula_id OUT NOCOPY NUMBER,
987 x_return_code OUT NOCOPY VARCHAR2,
988 x_error_msg OUT NOCOPY VARCHAR2) IS
989
990 l_return_status VARCHAR2(5);
991 l_timestamp DATE;
992 l_formula_no VARCHAR2(32);
993 l_fm_form_mst_rec FM_FORM_MST%ROWTYPE;
994 INSERT_FORMULA_EXCEPTION EXCEPTION;
995 GET_SURROGATE_EXCEPTION EXCEPTION;
996
997 l_message_count NUMBER;
998 l_msg_data VARCHAR2(2000);
999 l_message VARCHAR2(1000);
1000 l_dummy NUMBER;
1001
1002 BEGIN
1003
1004 x_return_code := FND_API.G_RET_STS_SUCCESS;
1005 x_error_msg := '';
1006
1007 x_formula_id := GMDSURG.get_surrogate('formula_id');
1008 IF (x_formula_id < 1) THEN
1009 RAISE GET_SURROGATE_EXCEPTION;
1010 END IF;
1011
1012 IF p_text_code <= 0 THEN
1013 l_fm_form_mst_rec.text_code := NULL;
1014 ELSE
1015 l_fm_form_mst_rec.text_code := p_text_code;
1016 END IF;
1017
1018 --- If formula number is not passed, then we need to create a dummy
1019 --- formula number. This can happen when the user just want to
1020 --- 'play' in the Designer and build a formula without entering header
1021 --- information. If the user decides to save the new formula, he
1022 --- will be prompted to enter header information and this will then
1023 --- be updated (including formula number and version - see
1024 --- Update_Formula_Header procedure).
1025
1026 IF p_formula_no IS NULL THEN
1027 l_fm_form_mst_rec.formula_no := x_formula_id || '#' ||
1028 TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
1029 l_fm_form_mst_rec.formula_vers := 1;
1030 ELSE
1031 l_fm_form_mst_rec.formula_no := p_formula_no;
1032 l_fm_form_mst_rec.formula_vers := p_formula_vers;
1033 END IF;
1034
1035
1036 l_fm_form_mst_rec.formula_id := x_formula_id;
1037 l_fm_form_mst_rec.formula_type := p_formula_type;
1038 l_fm_form_mst_rec.scale_type := p_scale_type;
1039 l_fm_form_mst_rec.formula_desc1 := p_formula_desc;
1040 l_fm_form_mst_rec.formula_desc2 := p_formula_desc2;
1041 l_fm_form_mst_rec.formula_class := p_formula_class;
1042 l_fm_form_mst_rec.fmcontrol_class := NULL;
1043 l_fm_form_mst_rec.in_use := 0;
1044 l_fm_form_mst_rec.inactive_ind := 0;
1045 l_fm_form_mst_rec.owner_organization_id := p_owner_organization_id;
1046 l_fm_form_mst_rec.TOTAL_INPUT_QTY := 0;
1047 l_fm_form_mst_rec.TOTAL_OUTPUT_QTY := 0;
1048 l_fm_form_mst_rec.yield_uom := NULL;
1049 l_fm_form_mst_rec.FORMULA_STATUS := '100';
1050 l_fm_form_mst_rec.OWNER_ID := p_owner_id;
1051 l_fm_form_mst_rec.attribute1 := NULL;
1052 l_fm_form_mst_rec.attribute2 := NULL;
1053 l_fm_form_mst_rec.attribute3 := NULL;
1054 l_fm_form_mst_rec.attribute4 := NULL;
1055 l_fm_form_mst_rec.attribute5 := NULL;
1056 l_fm_form_mst_rec.attribute6 := NULL;
1057 l_fm_form_mst_rec.attribute7 := NULL;
1058 l_fm_form_mst_rec.attribute8 := NULL;
1059 l_fm_form_mst_rec.attribute9 := NULL;
1060 l_fm_form_mst_rec.attribute10 := NULL;
1061 l_fm_form_mst_rec.attribute11 := NULL;
1062 l_fm_form_mst_rec.attribute12 := NULL;
1063 l_fm_form_mst_rec.attribute13 := NULL;
1064 l_fm_form_mst_rec.attribute14 := NULL;
1065 l_fm_form_mst_rec.attribute15 := NULL;
1066 l_fm_form_mst_rec.attribute16 := NULL;
1067 l_fm_form_mst_rec.attribute17 := NULL;
1068 l_fm_form_mst_rec.attribute18 := NULL;
1069 l_fm_form_mst_rec.attribute19 := NULL;
1070 l_fm_form_mst_rec.attribute20 := NULL;
1071 l_fm_form_mst_rec.attribute21 := NULL;
1072 l_fm_form_mst_rec.attribute22 := NULL;
1073 l_fm_form_mst_rec.attribute23 := NULL;
1074 l_fm_form_mst_rec.attribute24 := NULL;
1075 l_fm_form_mst_rec.attribute25 := NULL;
1076 l_fm_form_mst_rec.attribute26 := NULL;
1077 l_fm_form_mst_rec.attribute27 := NULL;
1078 l_fm_form_mst_rec.attribute28 := NULL;
1079 l_fm_form_mst_rec.attribute29 := NULL;
1080 l_fm_form_mst_rec.attribute30 := NULL;
1081 l_fm_form_mst_rec.attribute_category := NULL;
1082 l_fm_form_mst_rec.delete_mark := 0;
1083 l_fm_form_mst_rec.created_by := g_created_by;
1084 l_fm_form_mst_rec.creation_date := p_last_update_date;
1085 l_fm_form_mst_rec.last_update_date := p_last_update_date;
1086 l_fm_form_mst_rec.last_update_login := g_login_id;
1087 l_fm_form_mst_rec.last_updated_by := g_created_by;
1088 -- Kapil ME Auto-Prod
1089 l_fm_form_mst_rec.auto_product_calc := p_auto_product_calc;
1090
1091
1092 GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
1093 ( p_api_version => 1.0
1094 , p_init_msg_list => FND_API.G_TRUE
1095 , p_commit => FND_API.G_FALSE
1096 , x_return_status => l_return_status
1097 , x_msg_count => l_message_count
1098 , x_msg_data => l_msg_data
1099 , p_formula_header_rec => l_fm_form_mst_rec
1100 );
1101
1102 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1103 RAISE INSERT_FORMULA_EXCEPTION;
1104 END IF;
1105
1106 EXCEPTION
1107 WHEN INSERT_FORMULA_EXCEPTION THEN
1108 FND_MSG_PUB.GET( p_msg_index => 1,
1109 p_data => l_message,
1110 p_encoded => 'F',
1111 p_msg_index_out => l_dummy);
1112
1113 x_return_code := 'F';
1114 x_error_msg := l_message;
1115
1116 WHEN GET_SURROGATE_EXCEPTION THEN
1117 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_FORMULA_ID');
1118 x_return_code := 'F';
1119 x_error_msg := gmd_api_grp.get_message;
1120
1121 WHEN OTHERS THEN
1122 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1123 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1124 x_return_code := 'F';
1125 x_error_msg := gmd_api_grp.get_message;
1126
1127 END Create_Formula_Header;
1128
1129
1130 /* Api start of comments
1131 +============================================================================
1132 | PROCEDURE NAME
1133 | Delete_Formula_Detail
1134 |
1135 | DESCRIPTION
1136 | Delete a formula line
1137 |
1138 | INPUT PARAMETERS
1139 | p_formula_id NUMBER
1140 | p_formulaline_id NUMBER
1141 | p_line_type NUMBER
1142 | p_last_update_date DATE
1143 |
1144 | OUTPUT PARAMETERS
1145 | x_return_code VARCHAR2(1)
1146 | x_error_msg VARCHAR2(100)
1147 |
1148 | HISTORY
1149 | 05-SEP-2002 Eddie Oumerretane Created.
1150 |
1151 +=============================================================================
1152 Api end of comments
1153 */
1154 PROCEDURE Delete_Formula_Detail(p_formula_id IN NUMBER,
1155 p_formulaline_id IN NUMBER,
1156 p_line_type IN NUMBER,
1157 p_last_update_date IN DATE,
1158 x_return_code OUT NOCOPY VARCHAR2,
1159 x_error_msg OUT NOCOPY VARCHAR2) IS
1160
1161 CURSOR Cur_get_dtl IS
1162 SELECT formulaline_id
1163 FROM fm_matl_dtl
1164 WHERE formula_id = p_formula_id AND
1165 formulaline_id = p_formulaline_id AND
1166 last_update_date = p_last_update_date;
1167
1168 l_return_status VARCHAR2(2);
1169 l_message_count NUMBER;
1170 l_message_list VARCHAR2(2000);
1171 l_message VARCHAR2(1000);
1172 l_dummy NUMBER;
1173 l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
1174 DELETE_LINE_EXCEPTION EXCEPTION;
1175 RECORD_CHANGED_EXCEPTION EXCEPTION;
1176
1177 BEGIN
1178
1179 x_return_code := 'S';
1180 x_error_msg := '';
1181
1182 OPEN Cur_get_dtl;
1183 FETCH Cur_get_dtl INTO l_dummy;
1184
1185 IF Cur_get_dtl%NOTFOUND THEN
1186 CLOSE Cur_get_dtl;
1187 RAISE RECORD_CHANGED_EXCEPTION;
1188 END IF;
1189
1190 CLOSE Cur_get_dtl;
1191
1192 l_formula_dtl_rec(1).record_type := 'D';
1193 l_formula_dtl_rec(1).line_type := p_line_type;
1194 l_formula_dtl_rec(1).formula_id := p_formula_id;
1195 l_formula_dtl_rec(1).formulaline_id := p_formulaline_id;
1196 l_formula_dtl_rec(1).user_id := G_CREATED_BY;
1197
1198 GMD_FORMULA_DETAIL_PUB.Delete_FormulaDetail
1199 ( p_api_version => 1.1
1200 , p_init_msg_list => FND_API.G_TRUE
1201 , p_commit => FND_API.G_FALSE
1202 , p_called_from_forms => 'NO'
1203 , x_return_status => l_return_status
1204 , x_msg_count => l_message_count
1205 , x_msg_data => l_message_list
1206 , p_formula_detail_tbl => l_formula_dtl_rec);
1207
1208 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1209 RAISE DELETE_LINE_EXCEPTION;
1210 END IF;
1211
1212 EXCEPTION
1213 WHEN DELETE_LINE_EXCEPTION THEN
1214 FND_MSG_PUB.GET( p_msg_index => 1,
1215 p_data => l_message,
1216 p_encoded => 'F',
1217 p_msg_index_out => l_dummy);
1218
1219 x_return_code := 'F';
1220 x_error_msg := l_message;
1221 WHEN RECORD_CHANGED_EXCEPTION THEN
1222 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1223 x_return_code := 'F';
1224 x_error_msg := gmd_api_grp.get_message;
1225 WHEN OTHERS THEN
1226 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1227 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1228 x_return_code := 'F';
1229 x_error_msg := gmd_api_grp.get_message;
1230
1231 END Delete_Formula_Detail;
1232
1233 /* Api start of comments
1234 +============================================================================
1235 | PROCEDURE NAME
1236 | Del_Formula_Detail_With_No_Val
1237 |
1238 | DESCRIPTION
1239 | Delete a formula line without performing any validations
1240 |
1241 | INPUT PARAMETERS
1242 | p_formula_id NUMBER
1243 | p_formulaline_id NUMBER
1244 | p_line_type NUMBER
1245 | p_last_update_date DATE
1246 |
1247 | OUTPUT PARAMETERS
1248 | x_return_code VARCHAR2(1)
1249 | x_error_msg VARCHAR2(100)
1250 |
1251 | HISTORY
1252 | 26-SEP-2002 Eddie Oumerretane Created.
1253 |
1254 +=============================================================================
1255 Api end of comments
1256 */
1257 PROCEDURE Del_Formula_Detail_With_No_Val(p_formula_id IN NUMBER,
1258 p_formulaline_id IN NUMBER,
1259 p_line_type IN NUMBER,
1260 p_last_update_date IN DATE,
1261 x_return_code OUT NOCOPY VARCHAR2,
1262 x_error_msg OUT NOCOPY VARCHAR2) IS
1263
1264 CURSOR Cur_get_dtl IS
1265 SELECT formulaline_id
1266 FROM fm_matl_dtl
1267 WHERE formula_id = p_formula_id AND
1268 formulaline_id = p_formulaline_id AND
1269 last_update_date = p_last_update_date;
1270
1271 l_return_status VARCHAR2(2);
1272 l_message_count NUMBER;
1273 l_message_list VARCHAR2(2000);
1274 l_message VARCHAR2(1000);
1275 l_dummy NUMBER;
1276 l_formula_dtl_rec GMD_FORMULA_DETAIL_PUB.formula_update_dtl_tbl_type;
1277 DELETE_LINE_EXCEPTION EXCEPTION;
1278 RECORD_CHANGED_EXCEPTION EXCEPTION;
1279
1280 BEGIN
1281
1282 x_return_code := 'S';
1283 x_error_msg := '';
1284
1285 OPEN Cur_get_dtl;
1286 FETCH Cur_get_dtl INTO l_dummy;
1287
1288 IF Cur_get_dtl%NOTFOUND THEN
1289 CLOSE Cur_get_dtl;
1290 RAISE RECORD_CHANGED_EXCEPTION;
1291 END IF;
1292
1293 CLOSE Cur_get_dtl;
1294
1295 DELETE FROM
1296 fm_matl_dtl
1297 WHERE
1298 formulaline_id = p_formulaline_id;
1299
1300 EXCEPTION
1301 WHEN RECORD_CHANGED_EXCEPTION THEN
1302 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1303 x_return_code := 'F';
1304 x_error_msg := gmd_api_grp.get_message;
1305 WHEN OTHERS THEN
1306 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1307 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1308 x_return_code := 'F';
1309 x_error_msg := gmd_api_grp.get_message;
1310
1311 END Del_Formula_Detail_With_No_Val;
1312
1313 /* Api start of comments
1314 +============================================================================
1315 | PROCEDURE NAME
1316 | Validate_Cost_Allocation
1317 |
1318 | DESCRIPTION
1319 | Make sure cost allocation is <= 1
1320 |
1321 | INPUT PARAMETERS
1322 | p_formula_id NUMBER
1323 | p_formulaline_id NUMBER
1324 | p_cost_alloc NUMBER
1325 |
1326 | OUTPUT PARAMETERS
1327 | x_return_code VARCHAR2(1)
1328 | x_error_msg VARCHAR2(100)
1329 |
1330 | HISTORY
1331 | 09-SEP-2002 Eddie Oumerretane Created.
1332 |
1333 +=============================================================================
1334 Api end of comments
1335 */
1336 PROCEDURE Validate_Cost_Allocation(p_formula_id IN NUMBER,
1337 p_formulaline_id IN NUMBER,
1338 p_cost_alloc IN NUMBER,
1339 x_return_code OUT NOCOPY VARCHAR2,
1340 x_error_msg OUT NOCOPY VARCHAR2) IS
1341
1342 l_cost_alloc NUMBER(5);
1343 COST_ALLOC_EXCEPTION EXCEPTION;
1344
1345 CURSOR Cur_cost_alloc IS
1346 SELECT SUM(cost_alloc)
1347 FROM fm_matl_dtl
1348 WHERE line_type = 1
1349 AND formula_id = p_formula_id
1350 AND formulaline_id <> p_formulaline_id;
1351
1352 BEGIN
1353
1354 x_return_code := 'S';
1355 -- x_error_msg := '';
1356
1357 OPEN Cur_cost_alloc;
1358 FETCH Cur_cost_alloc INTO l_cost_alloc;
1359 CLOSE Cur_cost_alloc;
1360
1361 IF ((NVL(l_cost_alloc, 0) + p_cost_alloc) > 1) OR
1362 --bug 3336945, if formula line is then total cost should be equal to 1
1363 (p_formulaline_id = -1 AND l_cost_alloc <> 1) THEN
1364 RAISE COST_ALLOC_EXCEPTION;
1365 END IF;
1366
1367 EXCEPTION
1368 WHEN COST_ALLOC_EXCEPTION THEN
1369 FND_MESSAGE.SET_NAME ('GMD','FM_SUM_ALLOC <> 1');
1370 x_return_code := 'W';
1371 x_error_msg := gmd_api_grp.get_message;
1372
1373 WHEN OTHERS THEN
1374 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1375 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1376 x_return_code := 'F';
1377 x_error_msg := gmd_api_grp.get_message;
1378
1379 END Validate_Cost_Allocation;
1380
1381 /* Api start of comments
1382 +============================================================================
1383 | PROCEDURE NAME
1384 | Validate_Item_Uom
1385 |
1386 | DESCRIPTION
1387 | Make sure uom is convertible to item inventory uom
1388 |
1389 | INPUT PARAMETERS
1390 | p_item_id NUMBER
1391 | p_item_um VARCHAR
1392 |
1393 | OUTPUT PARAMETERS
1394 | x_return_code VARCHAR2(1)
1395 | x_error_msg VARCHAR2(100)
1396 |
1397 | HISTORY
1398 | 09-SEP-2002 Eddie Oumerretane Created.
1399 |
1400 +=============================================================================
1401 Api end of comments
1402 */
1403 PROCEDURE Validate_Item_Uom (p_item_id IN NUMBER,
1404 p_item_uom IN VARCHAR2,
1405 x_return_code OUT NOCOPY VARCHAR2,
1406 x_error_msg OUT NOCOPY VARCHAR2) IS
1407
1408 l_qty NUMBER;
1409 l_inv_uom VARCHAR2(4);
1410 CONV_ITEM_EXCEPTION EXCEPTION;
1411 ITEM_UM_EXCEPTION EXCEPTION;
1412
1413 CURSOR Get_Item_Uom IS
1414 SELECT primary_uom_code
1415 FROM mtl_system_items_b
1416 WHERE inventory_item_id = p_item_id;
1417
1418 CURSOR Cur_check_uom IS
1419 SELECT 1
1420 FROM sys.dual
1421 WHERE EXISTS (SELECT 1
1422 FROM mtl_units_of_measure
1423 WHERE uom_code = p_item_uom);
1424 l_exists BINARY_INTEGER;
1425
1426 BEGIN
1427
1428 x_return_code := 'S';
1429 x_error_msg := '';
1430
1431 -- Validate Item UOM first
1432 OPEN Cur_check_uom;
1433 FETCH Cur_check_uom INTO l_exists;
1434 IF Cur_check_uom%NOTFOUND THEN
1435 CLOSE Cur_check_uom;
1436 RAISE ITEM_UM_EXCEPTION;
1437 END IF;
1438 CLOSE Cur_check_uom;
1439
1440 OPEN Get_Item_Uom;
1441 FETCH Get_Item_Uom INTO l_inv_uom;
1442 CLOSE Get_Item_Uom;
1443
1444 l_qty := INV_CONVERT.inv_um_convert( item_id => p_item_id
1445 ,precision => 5
1446 ,from_quantity => 1
1447 ,from_unit => p_item_uom
1448 ,to_unit => l_inv_uom
1449 ,from_name => NULL
1450 ,to_name => NULL);
1451
1452 IF (l_qty = -1) THEN
1453 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_PACKAGE_ERR');
1454 RAISE CONV_ITEM_EXCEPTION;
1455 ELSIF (l_qty = -3) THEN
1456 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURUMTYPE_ERR');
1457 RAISE CONV_ITEM_EXCEPTION;
1458 ELSIF (l_qty = -4) THEN
1459 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NEWUMTYPE_ERR');
1460 RAISE CONV_ITEM_EXCEPTION;
1461 ELSIF (l_qty = -5) THEN
1462 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1463 FND_MESSAGE.set_token('FROMUOM',p_item_uom);
1464 FND_MESSAGE.set_token('TOUOM',l_inv_uom);
1465 RAISE CONV_ITEM_EXCEPTION;
1466 ELSIF (l_qty = -6) THEN
1467 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUMTYPE_ERR');
1468 RAISE CONV_ITEM_EXCEPTION;
1469 ELSIF (l_qty = -7) THEN
1470 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_CURFACTOR_ERR');
1471 RAISE CONV_ITEM_EXCEPTION;
1472 ELSIF (l_qty = -10) THEN
1473 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_INVUOMTYPE_ERR2');
1474 FND_MESSAGE.set_token('FROMUOM',p_item_uom);
1475 FND_MESSAGE.set_token('TOUOM',l_inv_uom);
1476 RAISE CONV_ITEM_EXCEPTION;
1477 ELSIF (l_qty = -11) THEN
1478 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_NOITEMID_ERR');
1479 RAISE CONV_ITEM_EXCEPTION;
1480 ELSIF (l_qty < -11) THEN
1481 FND_MESSAGE.set_name('GMI', 'IC_UOMCV_FATAL_ERR');
1482 RAISE CONV_ITEM_EXCEPTION;
1483 END IF;
1484 EXCEPTION
1485 WHEN ITEM_UM_EXCEPTION THEN
1486 FND_MESSAGE.SET_NAME('GMA','SY_INVALID_UM_CODE');
1487 x_return_code := 'F';
1488 x_error_msg := gmd_api_grp.get_message;
1489 WHEN CONV_ITEM_EXCEPTION THEN
1490 x_return_code := 'F';
1491 x_error_msg := gmd_api_grp.get_message;
1492 WHEN OTHERS THEN
1493 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1494 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1495 x_return_code := 'F';
1496 x_error_msg := gmd_api_grp.get_message;
1497
1498 END Validate_Item_Uom;
1499
1500 /* Api start of comments
1501 +============================================================================
1502 | PROCEDURE NAME
1503 | Check_Item_Used_In_Recipe
1504 |
1505 | DESCRIPTION
1506 | Check whether the given item is used in recipes
1507 |
1508 | INPUT PARAMETERS
1509 | p_formulaline_id NUMBER
1510 |
1511 | OUTPUT PARAMETERS
1512 | x_return_code VARCHAR2
1513 | x_warning_message VARCHAR2
1514 | x_error_msg VARCHAR2
1515 |
1516 | HISTORY
1517 | 20-SEP-2002 Eddie Oumerretane Created.
1518 |
1519 +=============================================================================
1520 Api end of comments
1521 */
1522 PROCEDURE Check_Item_Used_In_Recipe( p_formulaline_id IN NUMBER,
1523 x_nb_recipes OUT NOCOPY NUMBER,
1524 x_warning_message OUT NOCOPY VARCHAR2,
1525 x_return_code OUT NOCOPY VARCHAR2,
1526 x_error_msg OUT NOCOPY VARCHAR2) IS
1527
1528 l_calculatable_rec GMD_AUTO_STEP_CALC.CALCULATABLE_REC_TYPE;
1529 l_recipe_tbl GMD_AUTO_STEP_CALC.RECIPE_ID_TBL;
1530 l_check_step_mat GMD_AUTO_STEP_CALC.CHECK_STEP_MAT_TYPE;
1531 l_return_status VARCHAR2(2);
1532 l_message_count NUMBER;
1533 l_msg_data VARCHAR2(2000);
1534 l_message VARCHAR2(1000);
1535 l_dummy NUMBER;
1536 CHECK_ITEM_EXCEPTION EXCEPTION;
1537
1538 BEGIN
1539
1540 x_error_msg := '';
1541 x_warning_message := '';
1542 x_return_code := FND_API.G_RET_STS_SUCCESS;
1543
1544 l_calculatable_rec.formulaline_id := p_formulaline_id;
1545
1546 -- Count recipes where this formulaline exists in step/mat association,
1547 -- and where calculate_step_qty flag IS set (ASQC=Yes)
1548 -- and where delete_mark is NOT set
1549 -- and the recipe is NOT marked obsolete.
1550 GMD_AUTO_STEP_CALC.Check_Del_From_Step_Mat(p_check => l_calculatable_rec,
1551 p_recipe_tbl => l_recipe_tbl,
1552 p_check_step_mat => l_check_step_mat,
1553 p_msg_count => l_message_count,
1554 p_msg_stack => l_msg_data,
1555 p_return_status => l_return_status);
1556
1557 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1558 RAISE CHECK_ITEM_EXCEPTION;
1559 END IF;
1560
1561 --- Store these variables for Cascade_Update_Recipes
1562 g_calculatable_rec := l_calculatable_rec;
1563 g_recipe_tbl := l_recipe_tbl;
1564 g_check_step_mat := l_check_step_mat;
1565
1566 x_nb_recipes := 0;
1567
1568 IF l_check_step_mat.asqc_recipes > 0 THEN
1569 x_nb_recipes := l_check_step_mat.asqc_recipes;
1570 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORM_DEL_RECALC_AUTO_STEP');
1571 FND_MESSAGE.SET_TOKEN('RECIPE_NO', x_nb_recipes);
1572 x_warning_message := gmd_api_grp.get_message;
1573 ELSE
1574 -- Else Check if there are any rows in gmd_recipe_step_materials with this
1575 -- formulaline_id, regardless of ASQC flag
1576 IF l_check_step_mat.step_assoc_recipes > 0 THEN
1577 x_nb_recipes := l_check_step_mat.step_assoc_recipes;
1578 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORM_DEL_REVIEW_STEP_QTY');
1579 FND_MESSAGE.SET_TOKEN('RECIPE_NO', x_nb_recipes);
1580 x_warning_message := gmd_api_grp.get_message;
1581 END IF;
1582 END IF;
1583
1584 EXCEPTION
1585 WHEN CHECK_ITEM_EXCEPTION THEN
1586 FND_MSG_PUB.GET( p_msg_index => 1,
1587 p_data => l_message,
1588 p_encoded => 'F',
1589 p_msg_index_out => l_dummy);
1590
1591 x_return_code := 'F';
1592 x_error_msg := l_message;
1593
1594 WHEN OTHERS THEN
1595 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1596 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1597 x_return_code := 'F';
1598 x_error_msg := gmd_api_grp.get_message;
1599
1600 END Check_Item_Used_In_Recipe;
1601
1602
1603
1604 /* Api start of comments
1605 +============================================================================
1606 | PROCEDURE NAME
1607 | Cascade_Update_Recipes
1608 |
1609 | DESCRIPTION
1610 | Update all recipes impacted by the deletion of an item in the formula
1611 | or a step in the routing. Check_Item_Used_In_Recipe must be called
1612 | prior to invoking this procedure.
1613 |
1614 | INPUT PARAMETERS
1615 |
1616 | OUTPUT PARAMETERS
1617 | x_return_code VARCHAR2
1618 | x_warning_message VARCHAR2
1619 | x_error_msg VARCHAR2
1620 |
1621 | HISTORY
1622 | 20-SEP-2002 Eddie Oumerretane Created.
1623 |
1624 +=============================================================================
1625 Api end of comments
1626 */
1627 PROCEDURE Cascade_Update_Recipes(x_return_code OUT NOCOPY VARCHAR2,
1628 x_error_msg OUT NOCOPY VARCHAR2) IS
1629
1630 l_calculatable_rec GMD_AUTO_STEP_CALC.CALCULATABLE_REC_TYPE;
1631 l_recipe_tbl GMD_AUTO_STEP_CALC.RECIPE_ID_TBL;
1632 l_check_step_mat GMD_AUTO_STEP_CALC.CHECK_STEP_MAT_TYPE;
1633 l_return_status VARCHAR2(2);
1634 l_message_count NUMBER;
1635 l_msg_data VARCHAR2(2000);
1636 l_message VARCHAR2(1000);
1637 l_dummy NUMBER;
1638 l_date DATE;
1639 UPDATE_RECIPE_EXCEPTION EXCEPTION;
1640
1641 BEGIN
1642
1643 x_error_msg := '';
1644 x_return_code := FND_API.G_RET_STS_SUCCESS;
1645
1646 l_date := SYSDATE;
1647 g_calculatable_rec.created_by := g_created_by;
1648 g_calculatable_rec.last_updated_by := g_created_by;
1649 g_calculatable_rec.last_update_login := g_login_id;
1650 g_calculatable_rec.creation_date := l_date;
1651 g_calculatable_rec.last_update_date := l_date;
1652
1653 GMD_AUTO_STEP_CALC.Cascade_Del_To_Step_Mat(p_check => g_calculatable_rec,
1654 p_recipe_tbl => g_recipe_tbl,
1655 p_check_step_mat => g_check_step_mat,
1656 p_msg_count => l_message_count,
1657 p_msg_stack => l_msg_data,
1658 p_return_status => l_return_status,
1659 P_organization_id => null);
1660
1661 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1662 RAISE UPDATE_RECIPE_EXCEPTION;
1663 END IF;
1664
1665 EXCEPTION
1666 WHEN UPDATE_RECIPE_EXCEPTION THEN
1667 FND_MSG_PUB.GET( p_msg_index => 1,
1668 p_data => l_message,
1669 p_encoded => 'F',
1670 p_msg_index_out => l_dummy);
1671
1672 x_return_code := 'F';
1673 x_error_msg := l_message;
1674 WHEN OTHERS THEN
1675 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1676 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1677 x_return_code := 'F';
1678 x_error_msg := gmd_api_grp.get_message;
1679
1680 END Cascade_Update_Recipes;
1681
1682 /* Api start of comments
1683 +============================================================================
1684 | PROCEDURE NAME
1685 | Calculate_Theoretical_Yield
1686 |
1687 | DESCRIPTION
1688 | Calculate theoretical yield.
1689 |
1690 | INPUT PARAMETERS
1691 | p_formula_id NUMBER
1692 | p_scale_factor NUMBER
1693 |
1694 | OUTPUT PARAMETERS
1695 | x_return_code VARCHAR2
1696 | x_error_msg VARCHAR2
1697 |
1698 | HISTORY
1699 | 24-OCT-2002 Eddie Oumerretane Created.
1700 |
1701 +=============================================================================
1702 Api end of comments
1703 */
1704 PROCEDURE Calculate_Theoretical_yield(p_formula_id IN NUMBER,
1705 p_scale_factor IN NUMBER,
1706 x_return_code OUT NOCOPY VARCHAR2,
1707 x_error_msg OUT NOCOPY VARCHAR2) IS
1708
1709 CURSOR Get_Materials IS
1710 SELECT
1711 line_no,
1712 line_type,
1713 inventory_item_id, -- NPD Conv.
1714 qty,
1715 detail_uom, -- NPD Conv.
1716 scale_type,
1717 contribute_yield_ind,
1718 scale_multiple,
1719 scale_rounding_variance,
1720 rounding_direction
1721 FROM
1722 fm_matl_dtl
1723 WHERE
1724 formula_id = p_formula_id;
1725
1726 l_scale_tab GMD_COMMON_SCALE.scale_tab;
1727 l_return_status VARCHAR2(2);
1728 l_message_count NUMBER;
1729 l_msg_data VARCHAR2(2000);
1730 l_message VARCHAR2(1000);
1731 l_dummy NUMBER;
1732 THEORETICAL_YIELD_EXCEPTION EXCEPTION;
1733 TYPE LineNoTab IS TABLE OF FM_MATL_DTL.line_no%TYPE;
1734 TYPE LineTypeTab IS TABLE OF FM_MATL_DTL.line_type%TYPE;
1735 TYPE ItemIdTab IS TABLE OF FM_MATL_DTL.inventory_item_id%TYPE; -- NPD Conv.
1736 TYPE QtyTab IS TABLE OF FM_MATL_DTL.qty%TYPE;
1737 TYPE ItemUmTab IS TABLE OF FM_MATL_DTL.detail_uom%TYPE; -- NPD Conv.
1738 TYPE ScaleTypeTab IS TABLE OF FM_MATL_DTL.scale_type%TYPE;
1739 TYPE ContributeYieldIndTab IS TABLE OF FM_MATL_DTL.contribute_yield_ind%TYPE;
1740 TYPE ScaleMultipleTab IS TABLE OF FM_MATL_DTL.scale_multiple%TYPE;
1741 TYPE ScaleRoundingTab IS TABLE OF FM_MATL_DTL.scale_rounding_variance%TYPE;
1742 TYPE RoundingDirectionTab IS TABLE OF FM_MATL_DTL.rounding_direction%TYPE;
1743
1744 l_line_no LineNoTab;
1745 l_line_type LineTypeTab;
1746 l_inventory_item_id ItemIdTab; -- NPD Conv.
1747 l_qty QtyTab;
1748 l_detail_uom ItemUmTab; -- NPD Conv.
1749 l_scale_type ScaleTypeTab;
1750 l_contribute_yield_ind ContributeYieldIndTab;
1751 l_scale_multiple ScaleMultipleTab;
1752 l_scale_rounding_variance ScaleRoundingTab;
1753 l_rounding_direction RoundingDirectionTab;
1754
1755 -- NPD Conv.
1756 l_orgn_id NUMBER;
1757
1758 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1759 SELECT owner_organization_id
1760 FROM fm_form_mst
1761 WHERE formula_id = vformula_id;
1762
1763 BEGIN
1764
1765 x_error_msg := '';
1766 x_return_code := FND_API.G_RET_STS_SUCCESS;
1767
1768 OPEN Get_Materials;
1769
1770 FETCH Get_Materials
1771 BULK COLLECT INTO
1772 l_line_no,
1773 l_line_type,
1774 l_inventory_item_id,
1775 l_qty,
1776 l_detail_uom,
1777 l_scale_type,
1778 l_contribute_yield_ind,
1779 l_scale_multiple,
1780 l_scale_rounding_variance,
1781 l_rounding_direction;
1782
1783 CLOSE Get_Materials;
1784
1785 IF l_line_no.COUNT > 0 THEN
1786
1787 FOR i IN 1..l_line_no.COUNT LOOP
1788 l_scale_tab(i).line_no := l_line_no(i);
1789 l_scale_tab(i).line_type := l_line_type(i);
1790 l_scale_tab(i).inventory_item_id := l_inventory_item_id(i); -- NPD Conv.
1791 l_scale_tab(i).qty := l_qty(i);
1792 l_scale_tab(i).detail_uom := l_detail_uom(i); -- NPD Conv.
1793 l_scale_tab(i).scale_type := l_scale_type(i);
1794 l_scale_tab(i).contribute_yield_ind := l_contribute_yield_ind(i);
1795 l_scale_tab(i).scale_multiple := l_scale_multiple(i);
1796 l_scale_tab(i).scale_rounding_variance := l_scale_rounding_variance(i);
1797 l_scale_tab(i).rounding_direction := l_rounding_direction(i);
1798 END LOOP;
1799
1800 -- NPD Conv.
1801 OPEN get_formula_owner_orgn_id(p_formula_id);
1802 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1803 CLOSE get_formula_owner_orgn_id;
1804
1805 GMD_COMMON_SCALE.Theoretical_Yield ( p_scale_tab => l_scale_tab
1806 ,p_orgn_id => l_orgn_id
1807 ,p_scale_factor => p_scale_factor
1808 ,x_scale_tab => G_SCALE_REC
1809 ,x_return_status => l_return_status);
1810
1811 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1812 RAISE THEORETICAL_YIELD_EXCEPTION;
1813 END IF;
1814
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN THEORETICAL_YIELD_EXCEPTION THEN
1819 FND_MSG_PUB.GET( p_msg_index => 1,
1820 p_data => l_message,
1821 p_encoded => 'F',
1822 p_msg_index_out => l_dummy);
1823
1824 x_return_code := 'F';
1825 x_error_msg := l_message;
1826
1827 WHEN OTHERS THEN
1828 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1829 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1830 x_return_code := 'F';
1831 x_error_msg := gmd_api_grp.get_message;
1832
1833 END Calculate_Theoretical_yield;
1834
1835 /* Api start of comments
1836 +============================================================================
1837 | PROCEDURE NAME
1838 | Scale_Formula
1839 |
1840 | DESCRIPTION
1841 | Scale the formula.
1842 |
1843 | INPUT PARAMETERS
1844 | p_formula_id NUMBER
1845 | p_scale_factor NUMBER
1846 | p_primaries VARCHAR2
1847 |
1848 | OUTPUT PARAMETERS
1849 | x_return_code VARCHAR2
1850 | x_error_msg VARCHAR2
1851 |
1852 | HISTORY
1853 | 29-OCT-2002 Eddie Oumerretane Created.
1854 |
1855 +=============================================================================
1856 Api end of comments
1857 */
1858 PROCEDURE Scale_Formula(p_formula_id IN NUMBER,
1859 p_scale_factor IN NUMBER,
1860 p_primaries IN VARCHAR2,
1861 x_return_code OUT NOCOPY VARCHAR2,
1862 x_error_msg OUT NOCOPY VARCHAR2) IS
1863
1864 CURSOR Get_Materials IS
1865 SELECT
1866 line_no,
1867 line_type,
1868 inventory_item_id, -- NPD Conv.
1869 qty,
1870 detail_uom, -- NPD Conv.
1871 scale_type,
1872 contribute_yield_ind,
1873 scale_multiple,
1874 scale_rounding_variance,
1875 rounding_direction
1876 FROM
1877 fm_matl_dtl
1878 WHERE
1879 formula_id = p_formula_id;
1880
1881 l_scale_tab GMD_COMMON_SCALE.scale_tab;
1882 l_return_status VARCHAR2(2);
1883 l_message_count NUMBER;
1884 l_msg_data VARCHAR2(2000);
1885 l_message VARCHAR2(1000);
1886 l_dummy NUMBER;
1887 SCALE_EXCEPTION EXCEPTION;
1888 TYPE LineNoTab IS TABLE OF FM_MATL_DTL.line_no%TYPE;
1889 TYPE LineTypeTab IS TABLE OF FM_MATL_DTL.line_type%TYPE;
1890 TYPE ItemIdTab IS TABLE OF FM_MATL_DTL.inventory_item_id%TYPE; --NPD Conv.
1891 TYPE QtyTab IS TABLE OF FM_MATL_DTL.qty%TYPE;
1892 TYPE ItemUmTab IS TABLE OF FM_MATL_DTL.detail_uom%TYPE; --NPD Conv.
1893 TYPE ScaleTypeTab IS TABLE OF FM_MATL_DTL.scale_type%TYPE;
1894 TYPE ContributeYieldIndTab IS TABLE OF FM_MATL_DTL.contribute_yield_ind%TYPE;
1895 TYPE ScaleMultipleTab IS TABLE OF FM_MATL_DTL.scale_multiple%TYPE;
1896 TYPE ScaleRoundingTab IS TABLE OF FM_MATL_DTL.scale_rounding_variance%TYPE;
1897 TYPE RoundingDirectionTab IS TABLE OF FM_MATL_DTL.rounding_direction%TYPE;
1898 l_line_no LineNoTab;
1899 l_line_type LineTypeTab;
1900 l_inventory_item_id ItemIdTab; --NPD Conv.
1901 l_qty QtyTab;
1902 l_detail_uom ItemUmTab; --NPD Conv.
1903 l_scale_type ScaleTypeTab;
1904 l_contribute_yield_ind ContributeYieldIndTab;
1905 l_scale_multiple ScaleMultipleTab;
1906 l_scale_rounding_variance ScaleRoundingTab;
1907 l_rounding_direction RoundingDirectionTab;
1908
1909 -- NPD Conv.
1910 l_orgn_id NUMBER;
1911
1912 CURSOR get_formula_owner_orgn_id(vformula_id NUMBER) IS
1913 SELECT owner_organization_id
1914 FROM fm_form_mst
1915 WHERE formula_id = vformula_id;
1916
1917 BEGIN
1918
1919 x_error_msg := '';
1920 x_return_code := FND_API.G_RET_STS_SUCCESS;
1921
1922 OPEN Get_Materials;
1923
1924 FETCH Get_Materials
1925 BULK COLLECT INTO
1926 l_line_no,
1927 l_line_type,
1928 l_inventory_item_id, -- NPD Conv.
1929 l_qty,
1930 l_detail_uom, -- NPD Conv.
1931 l_scale_type,
1932 l_contribute_yield_ind,
1933 l_scale_multiple,
1934 l_scale_rounding_variance,
1935 l_rounding_direction;
1936
1937 CLOSE Get_Materials;
1938
1939 IF l_line_no.COUNT > 0 THEN
1940
1941 FOR i IN 1..l_line_no.COUNT LOOP
1942
1943 l_scale_tab(i).line_no := l_line_no(i);
1944 l_scale_tab(i).line_type := l_line_type(i);
1945 l_scale_tab(i).inventory_item_id := l_inventory_item_id(i); -- NPD Conv.
1946 l_scale_tab(i).qty := l_qty(i);
1947 l_scale_tab(i).detail_uom := l_detail_uom(i); -- NPD Conv.
1948 l_scale_tab(i).scale_type := l_scale_type(i);
1949
1950 IF l_line_type(i) = -1 THEN
1951 l_scale_tab(i).contribute_yield_ind := 'Y';
1952
1953 IF (l_scale_type(i) > 1) THEN
1954 l_scale_tab(i).scale_multiple := l_scale_multiple(i);
1955 l_scale_tab(i).scale_rounding_variance := l_scale_rounding_variance(i);
1956 l_scale_tab(i).rounding_direction := l_rounding_direction(i);
1957 END IF;
1958 ELSE
1959 l_scale_tab(i).contribute_yield_ind := l_contribute_yield_ind(i);
1960 END IF;
1961
1962 END LOOP;
1963
1964 -- NPD Conv.
1965 OPEN get_formula_owner_orgn_id(p_formula_id);
1966 FETCH get_formula_owner_orgn_id INTO l_orgn_id;
1967 CLOSE get_formula_owner_orgn_id;
1968
1969 GMD_COMMON_SCALE.Scale ( p_scale_tab => l_scale_tab
1970 ,p_orgn_id => l_orgn_id -- NPD Conv.
1971 ,p_scale_factor => p_scale_factor
1972 ,p_primaries => p_primaries
1973 ,x_scale_tab => G_SCALE_REC
1974 ,x_return_status => l_return_status);
1975
1976
1977 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1978 RAISE SCALE_EXCEPTION;
1979 END IF;
1980 END IF;
1981
1982 EXCEPTION
1983 WHEN SCALE_EXCEPTION THEN
1984 FND_MSG_PUB.GET( p_msg_index => 1,
1985 p_data => l_message,
1986 p_encoded => 'F',
1987 p_msg_index_out => l_dummy);
1988
1989 x_return_code := 'F';
1990 x_error_msg := l_message;
1991
1992 WHEN OTHERS THEN
1993 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1994 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1995 x_return_code := 'F';
1996 x_error_msg := gmd_api_grp.get_message;
1997
1998 END Scale_Formula;
1999
2000
2001 /* Api start of comments
2002 +============================================================================
2003 | PROCEDURE NAME
2004 | Validate_Formula_Details
2005 |
2006 | DESCRIPTION
2007 | Validate formula details
2008 |
2009 | INPUT PARAMETERS
2010 | p_formula_id NUMBER
2011 |
2012 | OUTPUT PARAMETERS
2013 | x_return_code VARCHAR2(1)
2014 | x_error_msg VARCHAR2(100)
2015 |
2016 | HISTORY
2017 | 18-NOV-2002 Eddie Oumerretane Created.
2018 | 23-JUN-2004 Sriram.S Bug# 3702561
2019 | Added validation to check for ingredient with zero qty.
2020 | 29-SEP-2004 Sriram.S Bug# 3761032
2021 | Added check for expr. items if formula status in (600,700).
2022 +=============================================================================
2023 Api end of comments
2024 */
2025 PROCEDURE Validate_Formula_Details ( p_formula_id IN VARCHAR2,
2026 x_return_code OUT NOCOPY VARCHAR2,
2027 x_error_msg OUT NOCOPY VARCHAR2) IS
2028 CURSOR check_num_details(p_line_type NUMBER) IS
2029 SELECT 1
2030 FROM fm_matl_dtl
2031 WHERE formula_id = p_formula_id AND
2032 line_type = p_line_type;
2033
2034 -- Sriram.S Bug# 3702561
2035 -- Added the below cursor to check for ingredients with zero qty.
2036 CURSOR check_for_zero_qty (l_line_type NUMBER) IS
2037 SELECT 1
2038 FROM fm_matl_dtl
2039 WHERE formula_id = p_formula_id AND
2040 qty = 0 AND
2041 line_type = l_line_type;
2042
2043 l_orgn_id NUMBER;
2044 l_return_status VARCHAR2(10);
2045 l_msg_count NUMBER;
2046 l_msg_index NUMBER;
2047 l_msg_data VARCHAR2(240);
2048 l_count NUMBER;
2049 l_product_qty NUMBER;
2050 l_ing_qty NUMBER;
2051 l_uom VARCHAR2(3);
2052
2053
2054 CURSOR get_orgn_id (l_formula_id NUMBER) IS
2055 SELECT owner_organization_id
2056 FROM fm_form_mst_b
2057 WHERE formula_id = l_formula_id;
2058
2059 BEGIN
2060
2061 x_error_msg := '';
2062 x_return_code := 'S';
2063
2064 -- Check product
2065 OPEN check_num_details(1);
2066 FETCH check_num_details INTO l_count;
2067 IF check_num_details%NOTFOUND THEN
2068 FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_PRODUCT');
2069 x_return_code := 'F';
2070 END IF;
2071 CLOSE check_num_details;
2072
2073 IF x_return_code = 'S' THEN
2074 -- Check ingredient
2075 OPEN check_num_details(-1);
2076 FETCH check_num_details INTO l_count;
2077 IF check_num_details%NOTFOUND THEN
2078 FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_INGREDIENT');
2079 x_return_code := 'F';
2080 -- Sriram.S Bug# 3702561 Check for ingr. with zero qty. based on profile.
2081 ELSIF (FND_PROFILE.VALUE('FM$ALLOW_ZERO_INGR_QTY')=0) THEN
2082 OPEN check_for_zero_qty(-1);
2083 FETCH check_for_zero_qty INTO l_count;
2084 IF check_for_zero_qty%FOUND THEN
2085 FND_MESSAGE.SET_NAME('GMD','GMD_ZERO_QTY');
2086 x_return_code := 'F';
2087 END IF;
2088 CLOSE check_for_zero_qty;
2089 END IF;
2090 CLOSE check_num_details;
2091 END IF;
2092
2093 --Check for formula orgn - item list match
2094 IF x_return_code = 'S' THEN
2095 OPEN get_orgn_id(p_formula_id);
2096 FETCH get_orgn_id INTO l_orgn_id;
2097 CLOSE get_orgn_id;
2098
2099 GMD_API_GRP.Check_Item_Exists( p_formula_id => p_formula_id,
2100 x_return_status => l_return_status,
2101 p_organization_id => l_orgn_id);
2102
2103 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2104 x_return_code := 'F';
2105 END IF;
2106 END IF;
2107
2108 GMD_COMMON_VAL.calculate_total_qty(
2109 formula_id => p_formula_id,
2110 x_product_qty => l_product_qty ,
2111 x_ingredient_qty => l_ing_qty ,
2112 x_uom => l_uom ,
2113 x_return_status => l_return_status ,
2114 x_msg_count => l_count ,
2115 x_msg_data => x_error_msg);
2116 IF l_return_status = 'Q' THEN
2117 X_return_code := 'W';
2118 END IF;
2119
2120 IF x_return_code <> 'S' THEN
2121 x_error_msg := gmd_api_grp.get_message;
2122 END IF;
2123
2124 --Bug 3336945
2125 --Function to validate the total cost of Formula
2126 IF x_return_code = 'S' THEN
2127 Validate_Cost_Allocation(p_formula_id => p_formula_id,
2128 p_formulaline_id => -1,
2129 p_cost_alloc => 0,
2130 x_return_code => x_return_code,
2131 x_error_msg => x_error_msg);
2132 END IF;
2133
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2137 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2138 x_return_code := 'F';
2139 x_error_msg := gmd_api_grp.get_message;
2140
2141 END Validate_Formula_Details;
2142
2143 /* Api start of comments
2144 +============================================================================
2145 | PROCEDURE NAME
2146 | Set_Save_Point
2147 |
2148 | DESCRIPTION
2149 | Establish a SAVEPOINT. This is used to provide the ability to
2150 | rollback a logical transaction performed by the Designer.
2151 |
2152 | INPUT PARAMETERS
2153 | None
2154 |
2155 | OUTPUT PARAMETERS
2156 | x_return_code VARCHAR2
2157 | x_error_msg VARCHAR2
2158 |
2159 | HISTORY
2160 | 03-DEC-2002 Eddie Oumerretane Created.
2161 |
2162 +=============================================================================
2163 Api end of comments
2164 */
2165 PROCEDURE Set_Save_Point ( x_return_code OUT NOCOPY VARCHAR2,
2166 x_error_msg OUT NOCOPY VARCHAR2) IS
2167 BEGIN
2168
2169 x_return_code := 'S';
2170 x_error_msg := '';
2171
2172 SAVEPOINT Start_Transaction;
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2177 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2178 x_return_code := 'F';
2179 x_error_msg := gmd_api_grp.get_message;
2180
2181 END Set_Save_Point;
2182
2183 /* Api start of comments
2184 +============================================================================
2185 | PROCEDURE NAME
2186 | Rollback_Save_Point
2187 |
2188 | DESCRIPTION
2189 | Rollback up to the save point established after a call to
2190 | Set_Save_Point.
2191 |
2192 | INPUT PARAMETERS
2193 | None
2194 |
2195 | OUTPUT PARAMETERS
2196 | x_return_code VARCHAR2
2197 | x_error_msg VARCHAR2
2198 |
2199 | HISTORY
2200 | 03-DEC-2002 Eddie Oumerretane Created.
2201 |
2202 +=============================================================================
2203 Api end of comments
2204 */
2205 PROCEDURE Rollback_Save_Point ( x_return_code OUT NOCOPY VARCHAR2,
2206 x_error_msg OUT NOCOPY VARCHAR2) IS
2207 BEGIN
2208
2209 x_return_code := 'S';
2210 x_error_msg := '';
2211
2212 ROLLBACK TO Start_Transaction;
2213
2214 EXCEPTION
2215 WHEN OTHERS THEN
2216 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2217 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2218 x_return_code := 'F';
2219 x_error_msg := gmd_api_grp.get_message;
2220
2221 END Rollback_Save_Point;
2222
2223 /* Api start of comments
2224 +============================================================================
2225 | PROCEDURE NAME
2226 | CHECK_USR_HAS_FSEC_RESP
2227 |
2228 | DESCRIPTION
2229 | Procedure to check if user has formula security responsibility.
2230 | (i-e) Product Development Security manager.
2231 |
2232 | INPUT PARAMETERS
2233 | None
2234 |
2235 | OUTPUT PARAMETERS
2236 | x_return_code VARCHAR2
2237 | x_error_msg VARCHAR2
2238 |
2239 | HISTORY
2240 | 23-JUN-2004 S.Sriram Created for Bug# 3700829
2241 |
2242 +=============================================================================
2243 Api end of comments
2244 */
2245
2246 PROCEDURE check_usr_has_fsec_resp (x_return_code OUT NOCOPY VARCHAR2,
2247 x_error_msg OUT NOCOPY VARCHAR2) IS
2248
2249 -- Cursor to check if user has formula security responsibility.
2250 CURSOR check_fsec_resp IS
2251 SELECT 1
2252 FROM FND_USER_RESP_GROUPS rg ,
2253 FND_RESPONSIBILITY rs
2254 WHERE rg.user_id = fnd_global.user_id
2255 AND rg.responsibility_id = rs.responsibility_id
2256 AND rs.responsibility_key = 'GMD_PD_SECURITY_MGR'
2257 AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE)
2258 AND SYSDATE BETWEEN rs.start_date AND NVL(rs.end_date, SYSDATE);
2259
2260 l_count NUMBER;
2261
2262 BEGIN
2263 x_return_code := 'N';
2264 x_error_msg := '';
2265
2266 OPEN check_fsec_resp;
2267 FETCH check_fsec_resp INTO l_count;
2268 IF check_fsec_resp%FOUND THEN
2269 x_return_code := 'Y';
2270 END IF;
2271 CLOSE check_fsec_resp;
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2276 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2277 x_return_code := 'N';
2278 x_error_msg := gmd_api_grp.get_message;
2279
2280 END check_usr_has_fsec_resp;
2281
2282 /* Api start of comments
2283 +============================================================================
2284 | PROCEDURE NAME
2285 | Check_fm_orgn_access
2286 |
2287 | DESCRIPTION
2288 | Procedure to check if user with appropriate responsibility
2289 | has accesss to the Formula based on its owning organization
2290 |
2291 | INPUT PARAMETERS
2292 | p_formula_id NUMBER
2293 |
2294 | OUTPUT PARAMETERS
2295 | x_return_code VARCHAR2
2296 |
2297 | HISTORY
2298 | 23-Aug-2005 Shyam Initial implementation
2299 |
2300 +=============================================================================
2301 Api end of comments
2302 */
2303 PROCEDURE Check_fm_orgn_access(p_formula_id IN NUMBER,
2304 x_return_code OUT NOCOPY VARCHAR2) IS
2305
2306 CURSOR Cur_get_orgn IS
2307 SELECT owner_organization_id
2308 FROM fm_form_mst_b
2309 WHERE formula_id = p_formula_id;
2310 --KSHUKLA changed the l_orgn_id data type from NUMBER(4) to Number
2311 l_orgn_id NUMBER;
2312
2313 BEGIN
2314 OPEN Cur_get_orgn;
2315 FETCH Cur_get_orgn INTO l_orgn_id;
2316 CLOSE Cur_get_orgn;
2317
2318 IF (l_orgn_id IS NOT NULL) THEN
2319 IF (GMD_API_GRP.setup AND GMD_API_GRP.OrgnAccessible(l_orgn_id) ) THEN
2320 x_return_code := 'S';
2321 ELSE
2322 x_return_code := 'F';
2323 END IF;
2324 ELSE
2325 x_return_code := 'S';
2326 END IF;
2327 END Check_fm_orgn_access;
2328
2329 /* Api start of comments
2330 +============================================================================
2331 | PROCEDURE NAME
2332 | Validate_Item_Revision
2333 |
2334 | DESCRIPTION
2335 | Make sure the item revision is valid
2336 |
2337 | INPUT PARAMETERS
2338 | p_organization_id NUMBER
2339 | p_item_id NUMBER
2340 | p_item_revision VARCHAR
2341 |
2342 | OUTPUT PARAMETERS
2343 | x_return_code VARCHAR2(1)
2344 | x_error_msg VARCHAR2(100)
2345 |
2346 | HISTORY
2347 | 01-JAN-2006 Thomas Daniel Created.
2348 |
2349 +=============================================================================
2350 Api end of comments
2351 */
2352 PROCEDURE Validate_Item_Revision (p_organization_id IN NUMBER,
2353 p_item_id IN NUMBER,
2354 p_item_revision IN VARCHAR2,
2355 x_return_code OUT NOCOPY VARCHAR2,
2356 x_error_msg OUT NOCOPY VARCHAR2) IS
2357 ITEM_REV_EXCEPTION EXCEPTION;
2358
2359 CURSOR Cur_check_revision IS
2360 SELECT 1
2361 FROM sys.dual
2362 WHERE EXISTS (SELECT 1
2363 FROM mtl_item_revisions
2364 WHERE organization_id = p_organization_id
2365 AND inventory_item_id = p_item_id
2366 AND revision = p_item_revision);
2367 l_exists BINARY_INTEGER;
2368
2369 BEGIN
2370 x_return_code := 'S';
2371 x_error_msg := '';
2372
2373 -- Validate Item Revision
2374 OPEN Cur_check_revision;
2375 FETCH Cur_check_revision INTO l_exists;
2376 IF Cur_check_revision%NOTFOUND THEN
2377 CLOSE Cur_check_revision;
2378 RAISE ITEM_REV_EXCEPTION;
2379 END IF;
2380 CLOSE Cur_check_revision;
2381 EXCEPTION
2382 WHEN ITEM_REV_EXCEPTION THEN
2383 FND_MESSAGE.SET_NAME('INV','INV_INT_REVEXP');
2384 x_return_code := 'F';
2385 x_error_msg := gmd_api_grp.get_message;
2386 WHEN OTHERS THEN
2387 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2388 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2389 x_return_code := 'F';
2390 x_error_msg := gmd_api_grp.get_message;
2391 END Validate_Item_Revision;
2392
2393
2394 /* Api start of comments
2395 +============================================================================
2396 | PROCEDURE NAME
2397 | check_item_exists
2398 |
2399 | DESCRIPTION
2400 | Make sure the items in the formula exists under the organization
2401 |
2402 | INPUT PARAMETERS
2403 | p_formula_id NUMBER
2404 | p_organization_id NUMBER
2405 |
2406 | OUTPUT PARAMETERS
2407 | x_return_status VARCHAR2(1)
2408 |
2409 | HISTORY
2410 | 27-JAN-2006 Thomas Daniel Created.
2411 | 14-JUN-2006 Kapil M Bug# 5240756 Get the top message from the stack
2412 |
2413 +=============================================================================
2414 Api end of comments
2415 */
2416 PROCEDURE Check_Item_Exists (p_formula_id IN NUMBER,
2417 p_organization_id IN NUMBER,
2418 x_return_status OUT NOCOPY VARCHAR2,
2419 x_error_msg OUT NOCOPY VARCHAR2) IS
2420 l_msg_txt VARCHAR2(2000);
2421 l_msg_index PLS_INTEGER;
2422 BEGIN
2423 GMD_API_GRP.check_item_exists (p_formula_id => p_formula_id
2424 ,x_return_status => x_return_status
2425 ,p_organization_id => p_organization_id);
2426 IF x_return_status <> FND_API.g_ret_sts_success THEN
2427 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.count_msg,
2428 p_data => X_error_msg,
2429 p_encoded => FND_API.G_FALSE,
2430 p_msg_index_out => l_msg_index);
2431 END IF;
2432 END Check_Item_Exists;
2433
2434 /* Api start of comments
2435 +============================================================================
2436 | PROCEDURE NAME
2437 | CHECK_FORMULA_ITEM_ACCESS
2438 |
2439 | DESCRIPTION
2440 | Checks If the Item is accessible to the formula
2441 |
2442 | INPUT PARAMETERS
2443 | p_formula_id NUMBER
2444 | p_organization_id NUMBER
2445 | prevision VARCHAR2
2446 |
2447 | OUTPUT PARAMETERS
2448 | x_return_status VARCHAR2(1)
2449 | x_error_msg VARCHAR2
2450 |
2451 | HISTORY
2452 | 04-AUG-2006 KapilM Created.
2453 |
2454 +=============================================================================
2455 Api end of comments
2456 */
2457 PROCEDURE CHECK_FORMULA_ITEM_ACCESS(pFormula_id IN NUMBER,
2458 pInventory_Item_ID IN NUMBER,
2459 x_return_status OUT NOCOPY VARCHAR2,
2460 x_error_msg OUT NOCOPY VARCHAR2,
2461 pRevision IN VARCHAR2 DEFAULT NULL) IS
2462 l_msg_index PLS_INTEGER;
2463 BEGIN
2464
2465 GMD_COMMON_VAL.CHECK_FORMULA_ITEM_ACCESS (pFormula_Id => pFormula_Id
2466 ,pInventory_Item_ID => pInventory_Item_ID
2467 ,x_return_status => x_return_status
2468 ,pRevision => pRevision );
2469 IF x_return_status <> FND_API.g_ret_sts_success THEN
2470 FND_MSG_PUB.Get(p_msg_index => FND_MSG_PUB.count_msg,
2471 p_data => X_error_msg,
2472 p_encoded => FND_API.G_FALSE,
2473 p_msg_index_out => l_msg_index);
2474 END IF;
2475
2476 END CHECK_FORMULA_ITEM_ACCESS;
2477
2478 -- Kapil ME Auto-prod :Bug# 5716318
2479 /* Api start of comments
2480 +============================================================================
2481 | PROCEDURE NAME
2482 | CHECK_AUTO_PRODUCT
2483 |
2484 | DESCRIPTION
2485 | Checks whether Automatic Product QTy Calculation parameter is set at Organization level.
2486 |
2487 | INPUT PARAMETERS
2488 | pOrgn_id NUMBER
2489 |
2490 | OUTPUT PARAMETERS
2491 | pAuto_calc VARCHAR2
2492 | x_return_status VARCHAR2
2493 | x_error_msg VARCHAR2
2494 |
2495 | HISTORY
2496 | 05-FEB-2007 Kapil M Bug# 5716318 Created.
2497 |
2498 +=============================================================================
2499 Api end of comments
2500 */
2501 PROCEDURE CHECK_AUTO_PRODUCT ( pOrgn_id IN NUMBER,
2502 pAuto_calc OUT NOCOPY VARCHAR2,
2503 x_return_status OUT NOCOPY VARCHAR2,
2504 x_error_msg OUT NOCOPY VARCHAR2) IS
2505
2506 BEGIN
2507 IF pOrgn_id IS NOT NULL THEN
2508 GMD_API_GRP.FETCH_PARM_VALUES ( P_orgn_id => pOrgn_id,
2509 P_parm_name => 'GMD_AUTO_PROD_CALC' ,
2510 P_parm_value => pAuto_calc ,
2511 X_return_status => x_return_status );
2512 END IF ;
2513 END CHECK_AUTO_PRODUCT;
2514
2515 /* Api start of comments
2516 +============================================================================
2517 | PROCEDURE NAME
2518 | CALCULATE_TOTAL_PRODUCT_QTY
2519 |
2520 | DESCRIPTION
2521 | Procedure to calculate Product Qty autmatically.
2522 |
2523 | INPUT PARAMETERS
2524 | pFormula_id NUMBER
2525 |
2526 | OUTPUT PARAMETERS
2527 | x_msg_data VARCHAR2
2528 | x_return_status VARCHAR2
2529 | x_msg_count NUMBER
2530 |
2531 | HISTORY
2532 | 05-FEB-2007 Kapil M Bug# 5716318 Created.
2533 |
2534 +=============================================================================
2535 Api end of comments
2536 */
2537
2538 PROCEDURE CALCULATE_TOTAL_PRODUCT_QTY( pFormula_id IN NUMBER,
2539 x_return_status OUT NOCOPY VARCHAR2,
2540 x_msg_count OUT NOCOPY NUMBER,
2541 x_msg_data OUT NOCOPY VARCHAR2) IS
2542
2543 BEGIN
2544 IF pFormula_id IS NOT NULL THEN
2545 GMD_COMMON_VAL.Calculate_Total_Product_Qty( p_formula_id => pFormula_id,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data);
2549 END IF;
2550 END CALCULATE_TOTAL_PRODUCT_QTY;
2551
2552
2553 END GMD_FORMULA_DESIGNER_PKG;