1 PACKAGE BODY GMD_FORMULA_PUB AS
2 /* $Header: GMDPFMHB.pls 120.22.12020000.2 2012/07/17 10:03:27 mtou ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FORMULA_PUB' ;
6 pRecord_in GMDFMVAL_PUB.formula_info_in;
7 pTable_out GMDFMVAL_PUB.formula_table_out;
8 lreturn varchar2(1);
9
10 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
11 --Forward declaration.
12 FUNCTION set_debug_flag RETURN VARCHAR2;
13 l_debug VARCHAR2(1) := set_debug_flag;
14
15 FUNCTION set_debug_flag RETURN VARCHAR2 IS
16 l_debug VARCHAR2(1):= 'N';
17 BEGIN
18 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
19 l_debug := 'Y';
20 END IF;
21 RETURN l_debug;
22 END set_debug_flag;
23 --Bug 3222090, NSRIVAST 20-FEB-2004, END
24
25 FUNCTION get_fm_status_meaning(vFormula_id NUMBER) RETURN VARCHAR2 IS
26 CURSOR get_status_meaning(P_Status_code VARCHAR2) IS
27 SELECT meaning
28 FROM gmd_status
29 WHERE status_code = P_status_code;
30
31 l_status_meaning GMD_STATUS.meaning%TYPE;
32
33 BEGIN
34 FOR C_status_code IN (Select formula_status from fm_form_mst_b
35 where formula_id = vFormula_id) LOOP
36 OPEN get_status_meaning(C_status_code.formula_status);
37 FETCH get_status_meaning INTO l_status_meaning;
38 CLOSE get_status_meaning;
39
40 END LOOP;
41
42 RETURN l_status_meaning;
43
44 END get_fm_status_meaning;
45
46
47 /* ======================================================================== */
48 /* Procedure: */
49 /* Insert_FormulaDetail */
50 /* */
51 /* DESCRIPTION: */
52 /* This PL/SQL procedure is responsible for inserting a formula. */
53 /* ======================================================================== */
54
55 /* ======================================================================== */
56 /* Start of commments */
57 /* API name : Insert_Formula */
58 /* Type : Public */
59 /* Function : */
60 /* Paramaters : */
61 /* IN : p_api_version IN NUMBER Required */
62 /* p_init_msg_list IN Varchar2 Optional */
63 /* p_commit IN Varchar2 Optional */
64 /* p_called_from_forms IN VARCHAR2 DEFAULT 'NO' */
65 /* p_formula_header_tbl_type IN Required */
66 /* BUG#2868184 p_allow_zero_ing_qty IN VARCHAR2 DEFAULT 'FALSE' */
67 /* */
68 /* OUT x_return_status OUT varchar2(1) */
69 /* x_msg_count OUT Number */
70 /* x_msg_data OUT varchar2(2000) */
71 /* */
72 /* Version : Current Version 1.0 */
73 /* */
74 /* Notes : */
75 /* */
76 /* History: */
77 /* V. Ajay Kumar 08/25/2003 BUG#2930523 Added code such that a message */
78 /* is displayed if the user tries to create an */
79 /* exisiting formula/version. */
80 /* Jeff Baird 09/26/2003 Bug #3119000 Changed values returned */
81 /* kkillams 23-03-2004 Added call to modify_status to set formula */
82 /* status to default status if default status is*/
83 /* defined organization level w.r.t. bug 3408799*/
84 /* G Kelly 10-MAY-2004 Bug# 3604554 Added functionality for Recipe */
85 /* Generation to the procedure after modify_status */
86 /* Kapil ME 05-FEB-2007 Bug# 5716318- Added the new Auto_product_calc*/
87 /* fields for Auto -Product Qty ME */
88 /* Michael Tou 21-Sep-2011 FP Bug No.6658328 */
89 /* Added code to re-initialize the table l_formula_detail_tbl */
90 /* End of comments */
91 /* ======================================================================== */
92 PROCEDURE Insert_Formula
93 ( p_api_version IN NUMBER
94 ,p_init_msg_list IN VARCHAR2
95 ,p_commit IN VARCHAR2
96 ,p_called_from_forms IN VARCHAR2 := 'NO'
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_msg_count OUT NOCOPY NUMBER
99 ,x_msg_data OUT NOCOPY VARCHAR2
100 ,p_formula_header_tbl IN FORMULA_INSERT_HDR_TBL_TYPE
101 ,p_allow_zero_ing_qty IN VARCHAR2 := 'FALSE'
102 )
103 IS
104 /* Local Variables definitions */
105 --BEGIN BUG#2868184
106 --Created a new variables to hold the profile value and Flag.
107 l_profile NUMBER;
108 l_flag VARCHAR2(1);
109 --END BUG#2868184
110 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULA';
111 l_api_version CONSTANT NUMBER := 1.0;
112 l_user_id fnd_user.user_id%TYPE := FND_GLOBAL.user_id; -- Bug 4603060
113 l_return_val NUMBER := 0;
114 l_item_id ic_item_mst.item_id%TYPE := 0;
115 l_inv_uom ic_item_mst.item_um%TYPE := NULL;
116 l_formula_id fm_form_mst.formula_id%TYPE := 0;
117 l_surrogate fm_form_mst.formula_id%TYPE := 0;
118 l_header_exists_flag VARCHAR2(1) ;
119 l_orgn_code VARCHAR2(4) ;
120
121 /* Extra variables for validating line type existence */
122 l_formula_no fm_form_mst.formula_no%TYPE;
123 l_formula_vers fm_form_mst.formula_vers%TYPE;
124 l_line_type fm_matl_dtl.line_type%TYPE;
125 l_line_type_counter NUMBER := 0;
126
127 /* Variables used for defining status */
128 l_return_status varchar2(1) ;
129 l_return_status_for_dtl varchar2(1) ;
130 l_return_status_for_eff varchar2(1) ;
131
132 /* GK B3604554 Variables used for recipe generation */
133 x_recipe_no varchar2(32);
134 x_recipe_version number(5);
135
136 -- Kapil ME Auto-PRod
137 l_auto_calc VARCHAR2(1);
138
139
140 /* Record definition */
141 l_fm_form_mst_rec fm_form_mst%ROWTYPE;
142 p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
143
144 /* Table definition */
145 l_formula_detail_tbl GMD_FORMULA_DETAIL_PUB.formula_insert_dtl_tbl_type;
146
147 --kkillams, bug 3408799
148 TYPE rec_formula IS RECORD
149 (formula_id FM_FORM_MST_B.FORMULA_ID%TYPE
150 ,owner_organization_id FM_FORM_MST_B.owner_organization_id%TYPE
151 );
152 TYPE tbl_formula_id IS TABLE OF rec_formula INDEX BY BINARY_INTEGER;
153
154 l_entity_status gmd_api_grp.status_rec_type;
155 l_tbl_formula_id tbl_formula_id;
156 l_tbl_cnt NUMBER :=0;
157
158
159 CURSOR Check_formula_exists(vFormula_id NUMBER) IS
160 SELECT formula_id
161 FROM fm_form_mst
162 WHERE formula_id = vFormula_id;
163
164
165
166 /* Exceptions */
167 default_status_err EXCEPTION;
168 RECIPE_GENERATE_ERROR EXCEPTION;
169
170 -- v_item_no varchar2(30); -- Added in Bug No.6799624
171 -- v_recipe_enabled varchar2(1); -- Added in Bug No.6799624
172
173 /* Bug No.8753171 - Start */
174 formula_ids Fm_Id := Fm_Id();
175 l_cnt Number(10) := 0;
176 l_temp_fm_id fm_form_mst.formula_id%TYPE ;
177 /* Bug No.8753171 -End */
178
179 BEGIN
180
181 /* Define Savepoint */
182 SAVEPOINT Insert_FormulaHeader_PUB;
183
184 /* Standard Check for API compatibility */
185 IF NOT FND_API.Compatible_API_Call ( l_api_version
186 ,p_api_version
187 ,l_api_name
188 ,G_PKG_NAME )
189 THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 /* Initialize message list if p_init_msg_list is set to TRUE */
194 IF FND_API.to_Boolean( p_init_msg_list ) THEN
195 FND_MSG_PUB.initialize;
196 END IF;
197
198 /* Start the loop - Error out if the table is empty */
199 IF (p_formula_header_tbl.count = 0) THEN
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 l_return_status := FND_API.G_RET_STS_SUCCESS;
204 l_return_status_for_dtl := FND_API.G_RET_STS_SUCCESS;
205 l_return_status_for_eff := FND_API.G_RET_STS_SUCCESS;
206
207 FOR i IN 1 .. p_formula_header_tbl.count LOOP
208
209 IF (l_debug = 'Y') THEN
210 gmd_debug.put_line(' ');
211 gmd_debug.put_line(' ');
212 END IF;
213
214 IF (l_debug = 'Y') THEN
215 gmd_debug.put_line(' In Formula Header Pub - Entering loop with row # '||i);
216 END IF;
217
218 /* Initialize return status for every header row */
219 x_return_status := FND_API.G_RET_STS_SUCCESS;
220
221 /* Header flag is used to check if the header already exists */
222 /* Since each header could have multiple detail lines, */
223 /* for any record even if header exists we may need to insert its */
224 /* detail lines and associted effectivity. */
225 l_header_exists_flag := 'N';
226
227 /* Line counter is used to validate the existence of atleast a */
228 /* product and ingredient while inserting a formula header. */
229 /* While looping thro each record if we come across a byproduct (line_type =2) */
230 /* we set the line counter = 2 and for ingredient or product (line_type = 1 or -1) */
231 /* we set this counter = 1 */
232 l_line_type_counter := 0;
233
234 /* Assign each row from the PL/SQL table to a row. */
235 p_formula_header_rec := p_formula_header_tbl(i);
236
237 -- Assigning Formulaline to formula detail table type
238 l_formula_detail_tbl(1) := p_formula_header_tbl(i);
239
240 IF (l_debug = 'Y') THEN
241 gmd_debug.put_line(' In Formula Header Pub - Before User_id val');
242 END IF;
243
244
245 /* ======================================= */
246 /* Check if there is a valid userid/ownerid */
247 /* ======================================== */
248 /* Bug 4603060 check if the user context is set */
249 IF (l_user_id IS NULL) THEN
250 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
251 FND_MSG_PUB.Add;
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254
255 IF (l_debug = 'Y') THEN
256 gmd_debug.put_line(' In Formula Header Pub - Before formula id val '
257 ||p_formula_header_rec.formula_no||' - '
258 ||p_formula_header_rec.formula_vers||' - '
259 ||l_formula_id);
260 END IF;
261 /* ================================================================== */
262 /* Formula_id validation : If the formula header has not been created */
263 /* we create one. If one already exists then we assume that the API */
264 /* is trying to create formulalines for the this header */
265 /* BAsed on the initial design the formula input table has both */
266 /* header and detail information */
267 /* ================================================================== */
268 IF (p_formula_header_rec.formula_id is NULL) THEN
269 GMDFMVAL_PUB.get_formula_id(p_formula_header_rec.formula_no,
270 p_formula_header_rec.formula_vers,
271 l_formula_id, l_return_val);
272 IF (l_return_val <> 0) THEN
273 IF (p_formula_header_rec.formula_no IS NULL) THEN
274 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
275 FND_MSG_PUB.Add;
276 RAISE FND_API.G_EXC_ERROR;
277 ELSE
278 l_formula_no := p_formula_header_rec.formula_no;
279 END IF;
280
281 IF (p_formula_header_rec.formula_vers IS NULL) THEN
282 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
283 FND_MSG_PUB.Add;
284 RAISE FND_API.G_EXC_ERROR;
285 ELSE
286 l_formula_vers := p_formula_header_rec.formula_vers;
287 END IF;
288 ELSE
289 /* Since we need the header info to create */
290 /* details we cannot error out if header already exists. */
291 /* Provide a flag that is set if header exists and */
292 /* and do validate before inserting the header info */
293 l_header_exists_flag := 'Y';
294
295 --BEGIN BUG#2930523 V. Ajay Kumar
296 --Added code such that a message is displayed if the user tries to
297 --create an exisiting formula/version thru API.
298 -- FND_MESSAGE.SET_NAME('GMD', 'FM_ALREADYEXISTS');
299 -- FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
300 -- FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
301 -- FND_MSG_PUB.Add;
302 --END BUG#2930523
303
304 BEGIN
305
306 l_temp_fm_id := -1;
307
308 FOR m in 1 .. formula_ids.count LOOP
309 IF formula_ids(m) = l_formula_id THEN
310 l_temp_fm_id := 999;
311 EXIT;
312 END IF;
313 END LOOP;
314 IF l_temp_fm_id = -1 THEN
315 FND_MESSAGE.SET_NAME('GMD', 'FM_ALREADYEXISTS');
316 FND_MESSAGE.SET_TOKEN('FORMULA_NO',
317 p_formula_header_rec.formula_no);
318 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',
319 p_formula_header_rec.formula_vers);
320 FND_MSG_PUB.Add;
321 END IF;
322 EXCEPTION
323 WHEN others THEN
324 NULL;
325 END;
326
327 END IF;
328 ELSE -- formula header exists
329 OPEN check_formula_exists(p_formula_header_rec.formula_id);
330 FETCH check_formula_exists INTO l_formula_id;
331 IF (check_formula_exists%NOTFOUND) THEN
332 l_header_exists_flag := 'N';
333 ELSE
334 l_header_exists_flag := 'Y';
335 END IF;
336 CLOSE check_formula_exists;
337 END IF;
338
339 l_formula_no := p_formula_header_rec.formula_no;
340 l_formula_vers := p_formula_header_rec.formula_vers;
341
342
343 IF (l_debug = 'Y') THEN
344 gmd_debug.put_line(' In Formula Header Pub - '
345 ||'Checking for 1 prod and 1 ingr - Scenario 1 '
346 ||x_return_status);
347 END IF;
348 /* ================================ */
349 /* A formula header should have atleast */
350 /* a product and an ingredient to work with. */
351 /* Has two scenarios that we need to handle */
352 /* ===================================== */
353 /* Scenario 1 - If we come across a by-product first */
354 /* We need to loop through all other records until */
355 /* we find atleast a product and an ingredient */
356 IF (p_formula_header_rec.line_type = 2) THEN
357 l_line_type_counter := 2; /* initialized */
358 FOR j IN 1 .. p_formula_header_tbl.count LOOP
359 p_formula_header_rec := p_formula_header_tbl(j);
360 /* loop thro till we come across either a product/ingredient */
361 IF (p_formula_header_rec.formula_no = l_formula_no) AND
362 (p_formula_header_rec.formula_vers = l_formula_vers) AND
363 (p_formula_header_rec.line_type IN (1,-1)) THEN
364 l_line_type_counter := l_line_type_counter - 1;
365 EXIT WHEN l_line_type_counter <= 0;
366 l_line_type := p_formula_header_rec.line_type;
367
368 FOR k IN 1 .. p_formula_header_tbl.count LOOP
369 p_formula_header_rec := p_formula_header_tbl(k);
370 /* in earlier loop if we found a product */
371 /* loop thro again to find an ingredient. */
372 IF (p_formula_header_rec.formula_no = l_formula_no) AND
373 (p_formula_header_rec.formula_vers = l_formula_vers) AND
374 (p_formula_header_rec.line_type = -1) AND
375 (l_line_type = 1) THEN
376 l_line_type_counter := l_line_type_counter - 1;
377 EXIT WHEN l_line_type_counter = 0;
378 END IF;
379 /* do vice versa if we found an ingredient. */
380 IF (p_formula_header_rec.formula_no = l_formula_no) AND
381 (p_formula_header_rec.formula_vers = l_formula_vers) AND
382 (p_formula_header_rec.line_type = 1) AND
383 (l_line_type = -1) THEN
384 l_line_type_counter := l_line_type_counter - 1;
385 EXIT WHEN l_line_type_counter = 0;
386 END IF;
387 END LOOP; -- for k IN 1 ..
388 END IF;
389 END LOOP; -- for j IN ..
390
391 IF (l_line_type_counter > 0) THEN
392 FND_MESSAGE.SET_NAME('GMD', 'FM_SAVE_FORMULA_ERR');
393 FND_MESSAGE.SET_TOKEN('FORMULA_NO', l_formula_no);
394 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', l_formula_vers);
395 -- Bug #3119000 (JKB) Changed values returned above to l_.
396 FND_MSG_PUB.Add;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399 END IF;
400
401 IF (l_debug = 'Y') THEN
402 gmd_debug.put_line(' In Formula Header Pub - '
403 ||'Checking for 1 prod and 1 ingr - Scenario 2 '
404 ||x_return_status);
405 END IF;
406 /* Scenario 2 we come across a product or ingredient */
407 IF (p_formula_header_rec.line_type IN (1,-1)) THEN
408 l_line_type_counter := 1;
409 l_line_type := p_formula_header_rec.line_type;
410
411 /* If we find a product first then loop thro to */
412 /* find an ingredient and do vice versa if we find */
413 /* an ingredient first. */
414 FOR k IN 1 .. p_formula_header_tbl.count LOOP
415 p_formula_header_rec := p_formula_header_tbl(k);
416 IF (p_formula_header_rec.formula_no = l_formula_no) AND
417 (p_formula_header_rec.formula_vers = l_formula_vers) AND
418 (p_formula_header_rec.line_type = -1) AND
419 (l_line_type = 1) THEN
420 l_line_type_counter := l_line_type_counter - 1;
421 EXIT WHEN l_line_type_counter = 0;
422 END IF;
423 IF (p_formula_header_rec.formula_no = l_formula_no) AND
424 (p_formula_header_rec.formula_vers = l_formula_vers) AND
425 (p_formula_header_rec.line_type = 1) AND
426 (l_line_type = -1) THEN
427 l_line_type_counter := l_line_type_counter - 1;
428 EXIT WHEN l_line_type_counter = 0;
429 END IF;
430 END LOOP;
431
432 IF (l_line_type_counter > 0) THEN
433 FND_MESSAGE.SET_NAME('GMD', 'FM_SAVE_FORMULA_ERR');
434 FND_MESSAGE.SET_TOKEN('FORMULA_NO', l_formula_no);
435 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', l_formula_vers);
436 -- Bug #3119000 (JKB) Changed values returned above to l_.
437 FND_MSG_PUB.Add;
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440 END IF;
441
442 /* Bug No.6800659 - Start (Included this fix in patch, 6799624) */
443
444 p_formula_header_rec := p_formula_header_tbl(i);
445
446 /* Bug No.6800659 - End */
447
448 -- Added by Shyam
449 -- If user does not provide the orgn code the information
450 -- then we get this value from default user level profile option
451 -- Bug 4603060 removed owner_id reference
452 IF (l_debug = 'Y') THEN
453 gmd_debug.put_line(' In Formula Header Pub - '
454 ||'Before deriving the orgn_code Owner id / User id = '
455 ||l_user_id
456 ||' / '
457 ||l_user_id);
458 END IF;
459
460 --Check that organization id is not null if raise an error message
461 IF (p_formula_header_rec.owner_organization_id IS NULL) THEN
462 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
463 FND_MSG_PUB.Add;
464 RAISE FND_API.G_EXC_ERROR;
465 ELSE
466 --Check the organization id passed is process enabled if not raise an error message
467 IF NOT (gmd_api_grp.check_orgn_status(p_formula_header_rec.owner_organization_id)) THEN
468 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
469 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_formula_header_rec.owner_organization_id);
470 FND_MSG_PUB.Add;
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473 -- Kapil ME Auto-Prod :Bug# 5716318
474 /* Fetch the Organization Auto-Product qty calculate Parameter*/
475 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => p_formula_header_rec.owner_organization_id,
476 P_parm_name => 'GMD_AUTO_PROD_CALC',
477 P_parm_value => l_auto_calc,
478 X_return_status => X_return_status );
479 IF (l_auto_calc IS NULL OR l_auto_calc = 'N') AND p_formula_header_rec.AUTO_PRODUCT_CALC = 'Y' THEN
480 /* Error raised when passing Formula Parameter as Yes when organziation parmater is NO */
481 FND_MESSAGE.SET_NAME('GMD', 'GMD_AUTO_PRODUCT_OFF');
482 FND_MSG_PUB.Add;
483 RAISE FND_API.G_EXC_ERROR;
484 END IF;
485 /* Error to be raised when Setting Scaling Allowed to No */
486 IF p_formula_header_rec.auto_product_calc = 'Y'
487 AND p_formula_header_rec.scale_type_hdr = 0 THEN
488 FND_MESSAGE.SET_NAME('GMD', 'GMD_SCALE_AUTO_NO');
489 FND_MSG_PUB.Add;
490 RAISE FND_API.G_EXC_ERROR;
491 END IF;
492 END IF;
493
494 /* Bug No.7519202 - Commented the below fix as the checking is being done at pkg, GMD_FORMULA_DETAIL_PUB */
495
496 /* Bug No.6799624 - Start */
497
498
499 /* FOR j IN 1 .. p_formula_header_tbl.count LOOP
500 BEGIN
501 SELECT segment1,recipe_enabled_flag INTO v_item_no, v_recipe_enabled
502 FROM mtl_system_items_b
503 WHERE (inventory_item_id = NVL(p_formula_header_tbl(j).inventory_item_id, -9999) OR
504 segment1 = p_formula_header_tbl(j).item_no) AND
505 organization_id = p_formula_header_tbl(j).owner_organization_id;
506 EXCEPTION
507 WHEN others THEN
508 ROLLBACK to Insert_FormulaHeader_PUB;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 FND_MSG_PUB.Count_And_Get (
511 p_count => x_msg_count,
512 p_data => x_msg_data );
513 END;
514
515 IF v_recipe_enabled <> 'Y' THEN
516 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_NOT_RECIPE_ENABLED');
517 FND_MESSAGE.SET_TOKEN('ITEM_NO', v_item_no);
518 FND_MSG_PUB.Add;
519 RAISE FND_API.G_EXC_ERROR;
520 END IF ;
521 END LOOP; */
522 /* Bug No.6799624 - End */
523
524 IF (l_debug = 'Y') THEN
525 gmd_debug.put_line(' In Formula Header Pub - '
526 ||'Before chceking for header exists flag '
527 ||l_header_exists_flag);
528 END IF;
529
530 IF (l_header_exists_flag = 'N') THEN
531
532 /* ===================================================== */
533 /* Validation for formula description */
534 /* Only in Inserts */
535 /* To insert a header it should have formula description */
536 /* ===================================================== */
537 IF (l_debug = 'Y') THEN
538 gmd_debug.put_line(' In Formula Header Pub - '
539 ||'Before validation of formula desc '
540 ||p_formula_header_rec.formula_desc1
541 ||' - '
542 ||x_return_status);
543 END IF;
544 IF (p_formula_header_rec.formula_desc1 IS NULL) THEN
545 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_DESC');
546 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
547 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
548 FND_MSG_PUB.Add;
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552 /* ===================================== */
553 /* Validate the formula type */
554 /* ===================================== */
555 IF (l_debug = 'Y') THEN
556 gmd_debug.put_line(' In Formula Header Pub - '
557 ||'Before validation of formula type '
558 ||p_formula_header_rec.formula_type
559 ||' - '
560 ||x_return_status);
561 END IF;
562 IF (p_formula_header_rec.formula_type <> 0) AND
563 (p_formula_header_rec.formula_type <> 1) THEN
564 FND_MESSAGE.SET_NAME('GMD', 'FM_WRONG_TYPE');
565 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
566 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
567 FND_MSG_PUB.Add;
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570
571 /* ======================================= */
572 /* Check the scale type for formula header */
573 /* ======================================= */
574 IF (l_debug = 'Y') THEN
575 gmd_debug.put_line(' In Formula Header Pub - '
576 ||'Before validation of header scale type '
577 ||p_formula_header_rec.scale_type_hdr
578 ||' - '
579 ||x_return_status);
580 END IF;
581 IF (p_formula_header_rec.scale_type_hdr IS NULL) THEN
582 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_SCALE_TYPE');
583 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
584 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
585 FND_MSG_PUB.Add;
586 RAISE FND_API.G_EXC_ERROR;
587 ELSIF (p_formula_header_rec.scale_type_hdr NOT IN (0,1)) THEN
588 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALETYPERR');
589 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
590 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
591 FND_MSG_PUB.Add;
592 RAISE FND_API.G_EXC_ERROR;
593 END IF;
594
595 /* ====================== */
596 /* Validate formula_class */
597 /* ====================== */
598 IF (l_debug = 'Y') THEN
599 gmd_debug.put_line(' In Formula Header Pub - '
600 ||'Before validation of formula class '
601 ||p_formula_header_rec.formula_class
602 ||' - '
603 ||x_return_status);
604 END IF;
605 IF (p_formula_header_rec.formula_class IS NOT NULL) THEN
606 l_return_val := GMDFMVAL_PUB.formula_class_val(p_formula_header_rec.formula_class);
607 IF (l_return_val <> 0) THEN
608 FND_MESSAGE.SET_NAME('GMD', 'FM_INVCLASS');
609 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
610 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
611 FND_MSG_PUB.Add;
612 RAISE FND_API.G_EXC_ERROR;
613 END IF;
614 END IF;
615
616 /* ================================================== */
617 /* Need to get the surrogate key value for formula id */
618 /* ================================================== */
619 IF (l_debug = 'Y') THEN
620 gmd_debug.put_line(' In Formula Header Pub - '
621 ||'Getting the Surrogate key : The formula id = '
622 ||p_formula_header_rec.formula_id
623 ||' - '
624 ||x_return_status);
625 END IF;
626 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
627 IF (p_formula_header_rec.formula_id IS NULL) THEN
628 l_return_val := GMDSURG.get_surrogate('formula_id');
629 IF (l_return_val < 1) THEN
630 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_FORMULA_ID');
631 FND_MSG_PUB.Add;
632 RAISE FND_API.G_EXC_ERROR;
633 ELSE
634 l_surrogate := l_return_val;
635 END IF;
636 ELSE
637 l_surrogate := p_formula_header_rec.formula_id;
638 END IF;
639 END IF;
640
641 /* ================================================== */
642 /* Call the private API to insert header information */
643 /* ================================================== */
644 IF (l_debug = 'Y') THEN
645 gmd_debug.put_line(' In Formula Header Pub - '
646 ||'About to assign values before calling pvt API '
647 ||' - '
648 ||x_return_status);
649 END IF;
650
651 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
652
653 --kkillams, bug 3408799
654 --Putting formula id and organization code into temporary table.
655 IF NVL(UPPER(p_called_from_forms),'NO') <> 'YES' THEN
656 l_tbl_cnt := l_tbl_cnt + 1;
657 l_tbl_formula_id(l_tbl_cnt).formula_id := l_surrogate;
658 l_tbl_formula_id(l_tbl_cnt).owner_organization_id := p_formula_header_rec.owner_organization_id;
659 END IF; --NVL(UPPER(p_called_from_forms),'NO') <> 'YES'
660
661 l_fm_form_mst_rec.formula_id := l_surrogate;
662 l_fm_form_mst_rec.formula_no := p_formula_header_rec.formula_no;
663 l_fm_form_mst_rec.formula_vers := p_formula_header_rec.formula_vers;
664 l_fm_form_mst_rec.formula_type := p_formula_header_rec.formula_type;
665 l_fm_form_mst_rec.master_formula_id := p_formula_header_rec.master_formula_id;
666 l_fm_form_mst_rec.scale_type := p_formula_header_rec.scale_type_hdr;
667 l_fm_form_mst_rec.formula_desc1 := p_formula_header_rec.formula_desc1;
668 l_fm_form_mst_rec.formula_desc2 := p_formula_header_rec.formula_desc2;
669 l_fm_form_mst_rec.formula_class := p_formula_header_rec.formula_class;
670 l_fm_form_mst_rec.fmcontrol_class := p_formula_header_rec.fmcontrol_class;
671 l_fm_form_mst_rec.in_use := 0;
672 l_fm_form_mst_rec.inactive_ind := p_formula_header_rec.inactive_ind;
673 l_fm_form_mst_rec.owner_organization_id := p_formula_header_rec.owner_organization_id;
674 l_fm_form_mst_rec.total_input_qty := p_formula_header_rec.total_input_qty ;
675 l_fm_form_mst_rec.total_output_qty := p_formula_header_rec.total_output_qty ;
676 l_fm_form_mst_rec.yield_uom := p_formula_header_rec.yield_uom;
677 l_fm_form_mst_rec.formula_status := p_formula_header_rec.formula_status ;
678 l_fm_form_mst_rec.owner_id := nvl(p_formula_header_rec.owner_id,l_user_id) ; /* akaruppa B5702796 */
679 l_fm_form_mst_rec.attribute1 := p_formula_header_rec.attribute1;
680 l_fm_form_mst_rec.attribute2 := p_formula_header_rec.attribute2;
681 l_fm_form_mst_rec.attribute3 := p_formula_header_rec.attribute3;
682 l_fm_form_mst_rec.attribute4 := p_formula_header_rec.attribute4;
683 l_fm_form_mst_rec.attribute5 := p_formula_header_rec.attribute5;
684 l_fm_form_mst_rec.attribute6 := p_formula_header_rec.attribute6;
685 l_fm_form_mst_rec.attribute7 := p_formula_header_rec.attribute7;
686 l_fm_form_mst_rec.attribute8 := p_formula_header_rec.attribute8;
687 l_fm_form_mst_rec.attribute9 := p_formula_header_rec.attribute9;
688 l_fm_form_mst_rec.attribute10 := p_formula_header_rec.attribute10;
689 l_fm_form_mst_rec.attribute11 := p_formula_header_rec.attribute11;
690 l_fm_form_mst_rec.attribute12 := p_formula_header_rec.attribute12;
691 l_fm_form_mst_rec.attribute13 := p_formula_header_rec.attribute13;
692 l_fm_form_mst_rec.attribute14 := p_formula_header_rec.attribute14;
693 l_fm_form_mst_rec.attribute15 := p_formula_header_rec.attribute15;
694 l_fm_form_mst_rec.attribute16 := p_formula_header_rec.attribute16;
695 l_fm_form_mst_rec.attribute17 := p_formula_header_rec.attribute17;
696 l_fm_form_mst_rec.attribute18 := p_formula_header_rec.attribute18;
697 l_fm_form_mst_rec.attribute19 := p_formula_header_rec.attribute19;
698 l_fm_form_mst_rec.attribute20 := p_formula_header_rec.attribute20;
699 l_fm_form_mst_rec.attribute21 := p_formula_header_rec.attribute21;
700 l_fm_form_mst_rec.attribute22 := p_formula_header_rec.attribute22;
701 l_fm_form_mst_rec.attribute23 := p_formula_header_rec.attribute23;
702 l_fm_form_mst_rec.attribute24 := p_formula_header_rec.attribute24;
703 l_fm_form_mst_rec.attribute25 := p_formula_header_rec.attribute25;
704 l_fm_form_mst_rec.attribute26 := p_formula_header_rec.attribute26;
705 l_fm_form_mst_rec.attribute27 := p_formula_header_rec.attribute27;
706 l_fm_form_mst_rec.attribute28 := p_formula_header_rec.attribute28;
707 l_fm_form_mst_rec.attribute29 := p_formula_header_rec.attribute29;
708 l_fm_form_mst_rec.attribute30 := p_formula_header_rec.attribute30;
709 l_fm_form_mst_rec.attribute_category := p_formula_header_rec.attribute_category;
710 l_fm_form_mst_rec.text_code := p_formula_header_rec.text_code_hdr;
711 l_fm_form_mst_rec.delete_mark := p_formula_header_rec.delete_mark;
712 l_fm_form_mst_rec.created_by := l_user_id; -- 4603060
713 l_fm_form_mst_rec.creation_date := NVL(p_formula_header_rec.creation_date, SYSDATE);
714 l_fm_form_mst_rec.last_update_date := NVL(p_formula_header_rec.last_update_date, SYSDATE);
715 l_fm_form_mst_rec.last_update_login := NVL(p_formula_header_rec.last_update_login, l_user_id);
716 l_fm_form_mst_rec.last_updated_by := l_user_id; -- 4603060
717 -- Kapil Auto ME :Bug# 5716318
718 l_fm_form_mst_rec.auto_product_calc := p_formula_header_rec.auto_product_calc;
719
720 IF (l_debug = 'Y') THEN
721 gmd_debug.put_line(' In Formula Header Pub - '
722 ||'About to call the FM Pvt API '
723 ||l_fm_form_mst_rec.formula_id
724 ||' - '
725 ||x_return_status);
726 END IF;
727
728 GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
729 ( p_api_version => p_api_version
730 ,p_init_msg_list => p_init_msg_list
731 ,p_commit => FND_API.G_FALSE
732 ,x_return_status => x_return_status
733 ,x_msg_count => x_msg_count
734 ,x_msg_data => x_msg_data
735 ,p_formula_header_rec => l_fm_form_mst_rec
736 );
737
738 /* Bug No.8753171 - Start */
739 l_cnt := l_cnt + 1;
740 formula_ids.EXTEND ;
741 formula_ids(l_cnt) := l_fm_form_mst_rec.formula_id ;
742 /* Bug No.8753171 - End */
743 IF (l_debug = 'Y') THEN
744 gmd_debug.put_line(' In Formula Header Pub - '
745 ||'After the FM Pvt API call '
746 ||' - '
747 ||x_return_status);
748 END IF;
749
750 END IF; /* end after formula header insert */
751
752 END IF; /* end for condition if header flag does not exists */
753
754
755 /* BEGIN BUG#2868184 Rameshwar Retrieving the value of profile and setting the flag value. */
756 /* For '0' and '2' with parameter value as False flag is set to 'F'. */
757 /* For 2 and paramter value True Flag is set to 'W' */
758
759 gmd_api_grp.fetch_parm_values(P_orgn_id => p_formula_header_rec.owner_organization_id,
760 P_parm_name => 'GMD_ZERO_INGREDIENT_QTY',
761 P_parm_value => l_profile,
762 X_return_status => X_return_status);
763
764 IF l_profile =0 THEN
765 l_flag:='F';
766 ELSIF l_profile = 1 THEN
767 NULL;
768 ELSIF l_profile = 2 AND p_allow_zero_ing_qty ='FALSE' THEN
769 l_flag:='F';
770 ELSIF l_profile = 2 AND p_allow_zero_ing_qty ='TRUE' THEN
771 l_flag:='W';
772 END IF;
773 /*END BUG#2868184*/
774
775
776 /* BEGIN BUG#2868184 Based on the ingredient quantity and parameter value */
777 /* Formula/Formula's are either rolled back or inserted with warning */
778
779 IF l_flag = 'F' AND l_formula_detail_tbl(1).line_type = -1 AND l_formula_detail_tbl(1).qty = 0 THEN
780 FND_MESSAGE.SET_NAME('GMD','GMD_ZERO_INGREDIENT_QTY');
781 FND_MESSAGE.SET_TOKEN('FORMULA_NO', l_formula_no);
782 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', l_formula_vers);
783 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_formula_detail_tbl(1).item_no );
784 FND_MSG_PUB.Add;
785 RAISE FND_API.G_EXC_ERROR;
786 ELSIF l_flag = 'W' AND l_formula_detail_tbl(1).line_type = -1 AND l_formula_detail_tbl(1).qty = 0 THEN
787 FND_MESSAGE.SET_NAME('GMD','GMD_ALLOW_ZERO_QTY');
788 FND_MSG_PUB.Add;
789 END IF;
790 /* END BUG#2868184 */
791
792 /* Based on return codes we need to insert formula details too */
793 /* If header inserts had failed for some reason either during */
794 /* validation or insertion we do not load fomula detail information. */
795
796
797 /* Create formulalines only if the header is succesfully created */
798 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
799 IF (l_debug = 'Y') THEN
800 gmd_debug.put_line(' In Formula Header Pub - '
801 ||'About to call the Formula line Pub API '
802 ||' - '
803 ||x_return_status);
804 END IF;
805
806
807 /* Bug No.8753171 - Start */
808 BEGIN
809
810 l_temp_fm_id := -1;
811
812 SELECT fm.formula_id
813 INTO l_temp_fm_id
814 FROM fm_form_mst fm
815 WHERE fm.formula_no = p_formula_header_rec.formula_no
816 AND fm.formula_vers = p_formula_header_rec.formula_vers;
817
818 FOR m in 1..formula_ids.count LOOP
819
820 IF formula_ids(m) = l_temp_fm_id THEN
821 GMD_FORMULA_DETAIL_PUB.Insert_FormulaDetail(p_api_version => l_api_version,
822 p_init_msg_list => p_init_msg_list,
823 p_called_from_forms => p_called_from_forms,
824 p_commit => FND_API.G_FALSE,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data,
828 p_formula_detail_tbl => l_formula_detail_tbl);
829
830 l_formula_detail_tbl.delete; /* Added this statement in FP Bug No.6658328 */
831
832 EXIT;
833 END IF;
834 END LOOP;
835 EXCEPTION
836 WHEN others THEN
837 NULL;
838 END;
839 /* Bug No.8753171 - End */
840
841 IF (l_debug = 'Y') THEN
842 gmd_debug.put_line(' In Formula Header Pub - '
843 ||'After the Formula line Pub API call '
844 ||' - '
845 ||x_return_status);
846 END IF;
847 END IF;
848
849 /* IF creating a header and/or line fails - Raise an exception
850 rather than trying to insert other header / lines */
851 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
852 RAISE FND_API.G_EXC_ERROR;
853 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 END IF;
856
857 END LOOP; /* Completes looping thro all rows in the PL/SQL table */
858
859 IF (x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') ) THEN
860
861 --Bug 9563603 commented the following code
862 /*AND (FND_API.To_Boolean(p_commit)) THEN
863 Check if p_commit is set to TRUE
864 Commit; */
865 -- End of bug 9563603
866
867 --kkillams 19-FBE-2004 w.r.t. bug 3408799
868 SAVEPOINT default_status_sp;
869 FOR i IN 1 .. l_tbl_formula_id.COUNT
870
871 LOOP
872 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
873 gmd_api_grp.get_status_details (V_entity_type => 'FORMULA',
874 V_orgn_id => l_tbl_formula_id(i).owner_organization_id,
875 X_entity_status => l_entity_status);
876 -- Check for any experimental items when formula status is apfgu.
877 IF (l_entity_status.status_type = 700) THEN
878 IF (gmdfmval_pub.check_expr_items(l_tbl_formula_id(i).formula_id)) THEN
879 FND_MESSAGE.SET_NAME('GMD','GMD_EXPR_ITEMS_FOUND');
880 FND_MSG_PUB.ADD;
881 RAISE FND_API.G_EXC_ERROR;
882 END IF; -- IF (gmdfmval_pub.check_expr_items(p_formula_header_rec.formula_id) THEN
883 END IF; -- IF (X_status_type = 700) THEN
884
885 --Check any inactive items in formula before changing the status
886 IF (l_entity_status.status_type IN (400,700)) THEN
887 IF (gmdfmval_pub.inactive_items(l_tbl_formula_id(i).formula_id)) THEN
888 FND_MESSAGE.SET_NAME('GMI','IC_ITEM_INACTIVE');
889 FND_MSG_PUB.ADD;
890 RAISE FND_API.G_EXC_ERROR;
891 END IF; -- IF (gmdfmval_pub.inactive_items(V_formula_id) THEN
892 END IF; --l_entity_status.status_type IN (400,700)
893 --kkillams,bug 3408799
894
895 IF (l_entity_status.entity_status <> 100) THEN
896 gmd_status_pub.modify_status ( p_api_version => 1
897 , p_init_msg_list => TRUE
898 , p_entity_name => 'FORMULA'
899 , p_entity_id => l_tbl_formula_id(i).formula_id
900 , p_entity_no => NULL
901 , p_entity_version => NULL
902 , p_to_status => l_entity_status.entity_status
903 , p_ignore_flag => FALSE
904 , x_message_count => x_msg_count
905 , x_message_list => x_msg_data
906 , x_return_status => X_return_status);
907 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
908 RAISE default_status_err;
909 END IF; --x_return_status
910 END IF; --l_entity_status.entity_status <> 100
911
912 IF (p_formula_header_rec.formula_status = l_entity_status.entity_status) OR
913 (p_formula_header_rec.formula_status = '100') THEN
914
915 GMD_RECIPE_GENERATE.recipe_generate(l_tbl_formula_id(i).owner_organization_id, l_tbl_formula_id(i).formula_id,
916 l_return_status, x_recipe_no, x_recipe_version, FND_API.To_Boolean(p_commit)); --Modified by Michael tou for bug#6658328.
917 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
918 RAISE RECIPE_GENERATE_ERROR;
919 END IF;
920
921 END IF;
922 END LOOP; --i IN 1 .. l_tbl_formula_id.COUNT
923
924 /* Check if p_commit is set to TRUE */
925 --9563603 Added this if condition
926 IF (FND_API.To_Boolean(p_commit)) THEN
927 Commit;
928 END IF;
929 END IF;
930
931 /* Get the message count and information */
932 FND_MSG_PUB.Count_And_Get (
933 p_count => x_msg_count,
934 p_data => x_msg_data );
935 EXCEPTION
936 WHEN default_status_err THEN
937 ROLLBACK TO default_status_sp;
938 FND_MSG_PUB.Count_And_Get (
939 p_count => x_msg_count,
940 p_data => x_msg_data );
941 WHEN FND_API.G_EXC_ERROR THEN
942 ROLLBACK to Insert_FormulaHeader_PUB;
943 x_return_status := FND_API.G_RET_STS_ERROR;
944 FND_MSG_PUB.Count_And_Get (
945 p_count => x_msg_count,
946 p_data => x_msg_data );
947 IF (l_debug = 'Y') THEN
948 gmd_debug.put_line(' In Formula Header Pub - In Error Exception Section '
949 ||' - '
950 ||x_return_status);
951 END IF;
952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
953 ROLLBACK to Insert_FormulaHeader_PUB;
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 FND_MSG_PUB.Count_And_Get (
956 p_count => x_msg_count,
957 p_data => x_msg_data );
958 IF (l_debug = 'Y') THEN
959 gmd_debug.put_line(' In Formula Header Pub - In unexpected Exception Section '
960 ||' - '
961 ||x_return_status);
962 END IF;
963 WHEN RECIPE_GENERATE_ERROR THEN
964 ROLLBACK to Insert_FormulaHeader_PUB;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 FND_MSG_PUB.Count_And_Get (
967 p_count => x_msg_count,
968 p_data => x_msg_data );
969 IF (l_debug = 'Y') THEN
970 gmd_debug.put_line(' In Formula Header Pub - In Recipe Generate Exception Section '
971 ||' - '
972 ||x_return_status);
973 END IF;
974 WHEN OTHERS THEN
975 ROLLBACK to Insert_FormulaHeader_PUB;
976 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.Count_And_Get (
979 p_count => x_msg_count,
980 p_data => x_msg_data );
981 IF (l_debug = 'Y') THEN
982 gmd_debug.put_line(' In Formula Header Pub - In Others Exception Section '
983 ||' - '
984 ||x_return_status);
985 END IF;
986 END Insert_Formula;
987
988
989 /* ======================================================================== */
990 /* Procedure: */
991 /* Update_FormulaHeader */
992 /* */
993 /* DESCRIPTION: */
994 /* This PL/SQL procedure is responsible for updating a formula. */
995 /* HISTORY : */
996 /* Kapil M Bug# 5716318 - Changes for Auto -Product Qty Calculation ME */
997 /* ======================================================================== */
998 PROCEDURE Update_FormulaHeader
999 ( p_api_version IN NUMBER
1000 ,p_init_msg_list IN VARCHAR2
1001 ,p_commit IN VARCHAR2
1002 ,p_called_from_forms IN VARCHAR2 := 'NO'
1003 ,x_return_status OUT NOCOPY VARCHAR2
1004 ,x_msg_count OUT NOCOPY NUMBER
1005 ,x_msg_data OUT NOCOPY VARCHAR2
1006 ,p_formula_header_tbl IN FORMULA_UPDATE_HDR_TBL_TYPE
1007 )
1008 IS
1009 /* Local Variables definitions */
1010 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAHEADER';
1011 l_api_version CONSTANT NUMBER := 2.0;
1012 l_user_id fnd_user.user_id%TYPE := FND_GLOBAL.user_id; -- Bug 4603060
1013 l_return_val NUMBER := 0;
1014 l_item_id ic_item_mst.item_id%TYPE := 0;
1015 l_inv_uom ic_item_mst.item_um%TYPE := NULL;
1016 l_formula_id fm_form_mst.formula_id%TYPE := 0;
1017
1018 l_return_status VARCHAR2(1);
1019 l_fm_form_mst_rec fm_form_mst%ROWTYPE;
1020 p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1021
1022 /* Added by shyam */
1023 fm_form_mst_rec fm_form_mst%ROWTYPE;
1024 l_formula_desc2 fm_form_mst.formula_desc2%TYPE;
1025 l_formula_class fm_form_mst.formula_class%TYPE;
1026 l_text_code fm_form_mst.text_code%TYPE;
1027
1028 l_attribute1 fm_form_mst.attribute1%TYPE;
1029 l_attribute2 fm_form_mst.attribute2%TYPE;
1030 l_attribute3 fm_form_mst.attribute3%TYPE;
1031 l_attribute4 fm_form_mst.attribute4%TYPE;
1032 l_attribute5 fm_form_mst.attribute5%TYPE;
1033 l_attribute6 fm_form_mst.attribute6%TYPE;
1034 l_attribute7 fm_form_mst.attribute7%TYPE;
1035 l_attribute8 fm_form_mst.attribute8%TYPE;
1036 l_attribute9 fm_form_mst.attribute9%TYPE;
1037 l_attribute10 fm_form_mst.attribute10%TYPE;
1038 l_attribute11 fm_form_mst.attribute11%TYPE;
1039 l_attribute12 fm_form_mst.attribute12%TYPE;
1040 l_attribute13 fm_form_mst.attribute13%TYPE;
1041 l_attribute14 fm_form_mst.attribute14%TYPE;
1042 l_attribute15 fm_form_mst.attribute15%TYPE;
1043 l_attribute16 fm_form_mst.attribute16%TYPE;
1044 l_attribute17 fm_form_mst.attribute17%TYPE;
1045 l_attribute18 fm_form_mst.attribute18%TYPE;
1046 l_attribute19 fm_form_mst.attribute19%TYPE;
1047 l_attribute20 fm_form_mst.attribute20%TYPE;
1048 l_attribute21 fm_form_mst.attribute21%TYPE;
1049 l_attribute22 fm_form_mst.attribute22%TYPE;
1050 l_attribute23 fm_form_mst.attribute23%TYPE;
1051 l_attribute24 fm_form_mst.attribute24%TYPE;
1052 l_attribute25 fm_form_mst.attribute25%TYPE;
1053 l_attribute26 fm_form_mst.attribute26%TYPE;
1054 l_attribute27 fm_form_mst.attribute27%TYPE;
1055 l_attribute28 fm_form_mst.attribute28%TYPE;
1056 l_attribute29 fm_form_mst.attribute29%TYPE;
1057 l_attribute30 fm_form_mst.attribute30%TYPE;
1058
1059 l_attribute_category fm_form_mst.attribute_category%TYPE;
1060 --Raju Bug 4218488
1061 l_dbdelete_mark FM_FORM_MST.DELETE_MARK%TYPE;
1062 l_lastformula_id FM_FORM_MST.FORMULA_ID%TYPE;
1063 l_update_chk_col VARCHAR2(30);
1064
1065 /* Define cursor */
1066 CURSOR get_header_rec(vFormula_id NUMBER) IS
1067 SELECT * from fm_form_mst
1068 WHERE formula_id = vFormula_id;
1069
1070 /* Cursor for retrieving Not Null column values */
1071 CURSOR get_formula_in_db (vFormula_id fm_form_mst.formula_id%TYPE) IS
1072 Select *
1073 From fm_form_mst
1074 Where formula_id = vFormula_id;
1075
1076 BEGIN
1077
1078 /* Define Savepoint */
1079 SAVEPOINT Update_FormulaHeader_PUB;
1080
1081 /* Standard Check for API compatibility */
1082 IF NOT FND_API.Compatible_API_Call (l_api_version
1083 ,p_api_version
1084 ,l_api_name
1085 ,G_PKG_NAME )
1086 THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 /* Initialize message list if p_init_msg_list is set to TRUE */
1091 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1092 FND_MSG_PUB.initialize;
1093 END IF;
1094
1095 /* Start looping through the table */
1096 IF (p_formula_header_tbl.count = 0) THEN
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099
1100 FOR i in 1 .. p_formula_header_tbl.count LOOP
1101
1102 /* Initialize API return status to success */
1103 x_return_status := FND_API.G_RET_STS_SUCCESS;
1104
1105 IF (l_debug = 'Y') THEN
1106 gmd_debug.put_line(' In Formula Header Pub - Entering loop with row # '||i);
1107 END IF;
1108
1109 p_formula_header_rec := p_formula_header_tbl(i);
1110
1111 /* ======================================= */
1112 /* Check if there is a valid userid/ownerid */
1113 /* ======================================== */
1114 IF (l_debug = 'Y') THEN
1115 gmd_debug.put_line(' In Formula Header Pub - Before user validation ');
1116 END IF;
1117 -- Bug 4603060
1118 IF (l_user_id IS NULL) THEN
1119 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
1120 FND_MSG_PUB.Add;
1121 RAISE FND_API.G_EXC_ERROR;
1122 END IF;
1123
1124 /* ==================== */
1125 /* Get the formula id */
1126 /* For updates we must */
1127 /* have a formula id */
1128 /* ==================== */
1129 IF (l_debug = 'Y') THEN
1130 gmd_debug.put_line(' Before formula validation - '||x_return_status);
1131 END IF;
1132 IF (p_formula_header_rec.formula_id is NULL) THEN
1133 GMDFMVAL_PUB.get_formula_id(p_formula_header_rec.formula_no,
1134 p_formula_header_rec.formula_vers,
1135 l_formula_id, l_return_val);
1136 IF (l_return_val <> 0) THEN
1137 IF (p_formula_header_rec.formula_no IS NULL) THEN
1138 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
1139 FND_MSG_PUB.Add;
1140 RAISE FND_API.G_EXC_ERROR;
1141 ELSIF (p_formula_header_rec.formula_vers IS NULL) THEN
1142 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
1143 FND_MSG_PUB.Add;
1144 RAISE FND_API.G_EXC_ERROR;
1145 ELSE
1146 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_FORMULA_ID');
1147 FND_MSG_PUB.Add;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150 END IF;
1151 ELSE
1152 l_formula_id := p_formula_header_rec.formula_id;
1153 END IF;
1154
1155 /* ==================================== */
1156 /* Get all not null values from the */
1157 /* from the formula table. If any */
1158 /* is not provided, update it with what */
1159 /* exists in the db */
1160 /* ==================================== */
1161 IF (l_debug = 'Y') THEN
1162 gmd_debug.put_line(' Assigning all not nulls from db - '||x_return_status);
1163 END IF;
1164
1165 FOR update_not_null_rec IN get_formula_in_db(l_formula_id)
1166 LOOP
1167 IF (p_formula_header_rec.formula_no IS NULL) THEN
1168 p_formula_header_rec.formula_no := update_not_null_rec.formula_no;
1169 END IF;
1170
1171 IF (p_formula_header_rec.formula_vers IS NULL) THEN
1172 p_formula_header_rec.formula_vers := update_not_null_rec.formula_vers;
1173 END IF;
1174
1175 IF (p_formula_header_rec.formula_desc1 IS NULL) THEN
1176 p_formula_header_rec.formula_desc1 := update_not_null_rec.formula_desc1;
1177 END IF;
1178
1179 IF (p_formula_header_rec.owner_organization_id IS NULL) THEN
1180 p_formula_header_rec.owner_organization_id := update_not_null_rec.owner_organization_id;
1181 END IF;
1182
1183 IF (p_formula_header_rec.owner_id IS NULL) THEN
1184 p_formula_header_rec.owner_id := update_not_null_rec.owner_id;
1185 END IF;
1186
1187 IF (p_formula_header_rec.formula_status IS NULL) THEN
1188 p_formula_header_rec.formula_status
1189 := update_not_null_rec.formula_status;
1190 END IF;
1191
1192 IF (p_formula_header_rec.formula_type IS NULL) THEN
1193 p_formula_header_rec.formula_type
1194 := update_not_null_rec.formula_type;
1195 END IF;
1196
1197 IF (p_formula_header_rec.scale_type_hdr IS NULL) THEN
1198 p_formula_header_rec.scale_type_hdr
1199 := update_not_null_rec.scale_type;
1200 END IF;
1201
1202 IF (p_formula_header_rec.inactive_ind IS NULL) THEN
1203 p_formula_header_rec.inactive_ind
1204 := update_not_null_rec.inactive_ind;
1205 END IF;
1206
1207 IF (p_formula_header_rec.delete_mark IS NULL) THEN
1208 p_formula_header_rec.delete_mark
1209 := update_not_null_rec.delete_mark;
1210 END IF;
1211
1212 IF (p_formula_header_rec.created_by IS NULL) THEN
1213 p_formula_header_rec.created_by
1214 := update_not_null_rec.created_by;
1215 END IF;
1216
1217 IF (p_formula_header_rec.creation_date IS NULL) THEN
1218 p_formula_header_rec.creation_date
1219 := update_not_null_rec.creation_date;
1220 END IF;
1221
1222 -- Bug 4603060 removed if condition
1223 p_formula_header_rec.last_updated_by
1224 := l_user_id;
1225
1226 IF (p_formula_header_rec.last_update_date IS NULL) THEN
1227 p_formula_header_rec.last_update_date
1228 := SYSDATE;
1229 END IF;
1230 l_dbdelete_mark := update_not_null_rec.delete_mark;
1231
1232 -- Kapil ME Auto-Prod :Bug# 5716318
1233 IF (p_formula_header_rec.auto_product_calc IS NULL) THEN
1234 p_formula_header_rec.auto_product_calc
1235 := update_not_null_rec.auto_product_calc;
1236 ELSE
1237 /* Update of the Flag is prevented form the API as the user cannot specify the Percentages. */
1238 IF NVL(UPPER(p_called_from_forms),'NO') <> 'YES' THEN
1239 FND_MESSAGE.SET_NAME('GMD', 'GMD_CANNOT_AUTO_FLAG');
1240 FND_MSG_PUB.Add;
1241 RAISE FND_API.G_EXC_ERROR;
1242 END IF;
1243 END IF;
1244 END LOOP;
1245
1246 --Check that organization id is not null if raise an error message
1247 IF (p_formula_header_rec.owner_organization_id IS NOT NULL) THEN
1248 --Check the organization id passed is process enabled if not raise an error message
1249 IF NOT (gmd_api_grp.check_orgn_status(p_formula_header_rec.owner_organization_id)) THEN
1250 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
1251 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_formula_header_rec.owner_organization_id);
1252 FND_MSG_PUB.Add;
1253 RAISE FND_API.G_EXC_ERROR;
1254 END IF;
1255 END IF;
1256
1257 /* Check if update is allowed */
1258 IF NVL( l_lastformula_id, -1) <> l_formula_id THEN -- Do this check if the formulaid is different from the one last validated
1259 IF (l_dbdelete_mark = 1 AND p_formula_header_rec.delete_mark = 0) THEN
1260 l_update_chk_col := 'DELETE_MARK';
1261 ELSE
1262 l_update_chk_col := NULL;
1263 END IF;
1264 --Check whether record is update allowed or not
1265 IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id, l_update_chk_col) THEN
1266 FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
1267 FND_MESSAGE.SET_TOKEN('NAME', 'formula');
1268 FND_MESSAGE.SET_TOKEN('ID', l_formula_id);
1269 FND_MESSAGE.SET_TOKEN('NO', p_formula_header_rec.formula_no);
1270 FND_MESSAGE.SET_TOKEN('VERS', p_formula_header_rec.formula_vers);
1271 FND_MESSAGE.SET_TOKEN('STATUS',get_fm_status_meaning(l_formula_id));
1272 FND_MSG_PUB.Add;
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275
1276 END IF;
1277
1278 /* ===================================== */
1279 /* Validate the formula type */
1280 /* ===================================== */
1281 IF (l_debug = 'Y') THEN
1282 gmd_debug.put_line(' Before formula type validation - '
1283 ||p_formula_header_rec.formula_type
1284 ||' - '
1285 ||x_return_status);
1286 END IF;
1287 IF (p_formula_header_rec.formula_type <> 0) AND
1288 (p_formula_header_rec.formula_type <> 1) THEN
1289 FND_MESSAGE.SET_NAME('GMD', 'FM_WRONG_TYPE');
1290 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1291 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1292 FND_MSG_PUB.Add;
1293 RAISE FND_API.G_EXC_ERROR;
1294 END IF;
1295
1296 /* ==================== */
1297 /* Check the scale type */
1298 /* ==================== */
1299 IF (l_debug = 'Y') THEN
1300 gmd_debug.put_line(' Before scale type validation - '
1301 ||p_formula_header_rec.scale_type_hdr
1302 ||' - '
1303 ||x_return_status);
1304 END IF;
1305 IF (p_formula_header_rec.scale_type_hdr IS NULL) THEN
1306 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_SCALE_TYPE');
1307 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1308 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1309 FND_MSG_PUB.Add;
1310 RAISE FND_API.G_EXC_ERROR;
1311 ELSIF (p_formula_header_rec.scale_type_hdr NOT IN (0,1)) THEN
1312 FND_MESSAGE.SET_NAME('GMD', 'FM_SCALETYPERR');
1313 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1314 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1315 FND_MSG_PUB.Add;
1316 RAISE FND_API.G_EXC_ERROR;
1317 -- SCALING KAPIL
1318 ELSIF p_formula_header_rec.scale_type_hdr = 0 THEN
1319 IF p_formula_header_rec.auto_product_calc = 'Y' THEN
1320 p_formula_header_rec.auto_product_calc := 'N';
1321 FND_MESSAGE.SET_NAME('GMD', 'GMD_SCALE_SET_AUTO_OFF');
1322 FND_MSG_PUB.Add;
1323 END IF;
1324 END IF;
1325
1326 /* ====================== */
1327 /* Validate formula_class */
1328 /* ====================== */
1329 IF (l_debug = 'Y') THEN
1330 gmd_debug.put_line(' Before formula class validation - '
1331 ||p_formula_header_rec.formula_class
1332 ||' - '
1333 ||x_return_status);
1334 END IF;
1335 IF (p_formula_header_rec.formula_class IS NOT NULL) THEN
1336 l_return_val := GMDFMVAL_PUB.formula_class_val(
1337 p_formula_header_rec.formula_class);
1338 IF (l_return_val <> 0) THEN
1339 FND_MESSAGE.SET_NAME('GMD', 'FM_INVCLASS');
1340 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1341 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1342 FND_MSG_PUB.Add;
1343 RAISE FND_API.G_EXC_ERROR;
1344 END IF;
1345 END IF;
1346
1347 OPEN get_header_rec(p_formula_header_rec.formula_id);
1348 FETCH get_header_rec INTO fm_form_mst_rec;
1349
1350 /* Shyam Sitaraman - Bug 2652200 */
1351 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
1352 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
1353 /* as the user is intending to update the field to NULL */
1354 /* Validate all optional parameters passed */
1355 IF (l_debug = 'Y') THEN
1356 gmd_debug.put_line(' Before G-MISS validation - '
1357 ||p_formula_header_rec.formula_id
1358 ||' - '
1359 ||x_return_status);
1360 END IF;
1361
1362 IF (get_header_rec%FOUND) THEN
1363
1364 IF (p_formula_header_rec.formula_desc2 = FND_API.G_MISS_CHAR) THEN
1365 l_formula_desc2 := NULL;
1366 ELSIF (p_formula_header_rec.formula_desc2 IS NULL) THEN
1367 l_formula_desc2 := fm_form_mst_rec.formula_desc2;
1368 ELSE
1369 l_formula_desc2 := p_formula_header_rec.formula_desc2;
1370 END IF;
1371
1372 IF (p_formula_header_rec.formula_class = FND_API.G_MISS_CHAR) THEN
1373 l_formula_class := NULL;
1374 ELSIF (p_formula_header_rec.formula_class IS NULL) THEN
1375 l_formula_class := fm_form_mst_rec.formula_class;
1376 ELSE
1377 l_formula_class := p_formula_header_rec.formula_class;
1378 END IF;
1379
1380 IF (p_formula_header_rec.text_code_hdr = FND_API.G_MISS_NUM) THEN
1381 l_text_code := NULL;
1382 ELSIF (p_formula_header_rec.text_code_hdr IS NULL) THEN
1383 l_text_code := fm_form_mst_rec.text_code;
1384 ELSE
1385 l_text_code := p_formula_header_rec.text_code_hdr;
1386 END IF;
1387
1388 IF (p_formula_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1389 l_attribute_category := NULL;
1390 ELSIF (p_formula_header_rec.attribute_category IS NULL) THEN
1391 l_attribute_category := fm_form_mst_rec.attribute_category;
1392 ELSE
1393 l_attribute_category := p_formula_header_rec.attribute_category;
1394 END IF;
1395
1396 IF (p_formula_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1397 l_attribute1 := NULL;
1398 ELSIF (p_formula_header_rec.attribute1 IS NULL) THEN
1399 l_attribute1 := fm_form_mst_rec.attribute1;
1400 ELSE
1401 l_attribute1 := p_formula_header_rec.attribute1;
1402 END IF;
1403
1404 IF (p_formula_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1405 l_attribute2 := NULL;
1406 ELSIF (p_formula_header_rec.attribute2 IS NULL) THEN
1407 l_attribute2 := fm_form_mst_rec.attribute2;
1408 ELSE
1409 l_attribute2 := p_formula_header_rec.attribute2;
1410 END IF;
1411
1412 IF (p_formula_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1413 l_attribute3 := NULL;
1414 ELSIF (p_formula_header_rec.attribute3 IS NULL) THEN
1415 l_attribute3 := fm_form_mst_rec.attribute3;
1416 ELSE
1417 l_attribute3 := p_formula_header_rec.attribute3;
1418 END IF;
1419
1420 IF (p_formula_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1421 l_attribute4 := NULL;
1422 ELSIF (p_formula_header_rec.attribute4 IS NULL) THEN
1423 l_attribute4 := fm_form_mst_rec.attribute4;
1424 ELSE
1425 l_attribute4 := p_formula_header_rec.attribute4;
1426 END IF;
1427
1428 IF (p_formula_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1429 l_attribute5 := NULL;
1430 ELSIF (p_formula_header_rec.attribute5 IS NULL) THEN
1431 l_attribute5 := fm_form_mst_rec.attribute5;
1432 ELSE
1433 l_attribute5 := p_formula_header_rec.attribute5;
1434 END IF;
1435
1436 IF (p_formula_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1437 l_attribute6 := NULL;
1438 ELSIF (p_formula_header_rec.attribute6 IS NULL) THEN
1439 l_attribute6 := fm_form_mst_rec.attribute6;
1440 ELSE
1441 l_attribute6 := p_formula_header_rec.attribute6;
1442 END IF;
1443
1444 IF (p_formula_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1445 l_attribute7 := NULL;
1446 ELSIF (p_formula_header_rec.attribute7 IS NULL) THEN
1447 l_attribute7 := fm_form_mst_rec.attribute7;
1448 ELSE
1449 l_attribute7 := p_formula_header_rec.attribute7;
1450 END IF;
1451
1452 IF (p_formula_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1453 l_attribute8 := NULL;
1454 ELSIF (p_formula_header_rec.attribute8 IS NULL) THEN
1455 l_attribute8 := fm_form_mst_rec.attribute8;
1456 ELSE
1457 l_attribute8 := p_formula_header_rec.attribute8;
1458 END IF;
1459
1460 IF (p_formula_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1461 l_attribute9 := NULL;
1462 ELSIF (p_formula_header_rec.attribute9 IS NULL) THEN
1463 l_attribute9 := fm_form_mst_rec.attribute9;
1464 ELSE
1465 l_attribute9 := p_formula_header_rec.attribute9;
1466 END IF;
1467
1468 IF (p_formula_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1469 l_attribute10 := NULL;
1470 ELSIF (p_formula_header_rec.attribute10 IS NULL) THEN
1471 l_attribute10 := fm_form_mst_rec.attribute10;
1472 ELSE
1473 l_attribute10 := p_formula_header_rec.attribute10;
1474 END IF;
1475
1476 IF (p_formula_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1477 l_attribute11 := NULL;
1478 ELSIF (p_formula_header_rec.attribute11 IS NULL) THEN
1479 l_attribute11 := fm_form_mst_rec.attribute11;
1480 ELSE
1481 l_attribute11 := p_formula_header_rec.attribute11;
1482 END IF;
1483 --Bug13542669 modified the attribute2 to attribute12.
1484 IF (p_formula_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1485 l_attribute12 := NULL;
1486 ELSIF (p_formula_header_rec.attribute12 IS NULL) THEN
1487 l_attribute12 := fm_form_mst_rec.attribute12;
1488 ELSE
1489 l_attribute12 := p_formula_header_rec.attribute12;
1490 END IF;
1491
1492 IF (p_formula_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1493 l_attribute13 := NULL;
1494 ELSIF (p_formula_header_rec.attribute13 IS NULL) THEN
1495 l_attribute13 := fm_form_mst_rec.attribute13;
1496 ELSE
1497 l_attribute13 := p_formula_header_rec.attribute13;
1498 END IF;
1499
1500 IF (p_formula_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1501 l_attribute14 := NULL;
1502 ELSIF (p_formula_header_rec.attribute14 IS NULL) THEN
1503 l_attribute14 := fm_form_mst_rec.attribute14;
1504 ELSE
1505 l_attribute14 := p_formula_header_rec.attribute14;
1506 END IF;
1507
1508 IF (p_formula_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1509 l_attribute15 := NULL;
1510 ELSIF (p_formula_header_rec.attribute15 IS NULL) THEN
1511 l_attribute15 := fm_form_mst_rec.attribute15;
1512 ELSE
1513 l_attribute15 := p_formula_header_rec.attribute15;
1514 END IF;
1515
1516 IF (p_formula_header_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1517 l_attribute16 := NULL;
1518 ELSIF (p_formula_header_rec.attribute16 IS NULL) THEN
1519 l_attribute16 := fm_form_mst_rec.attribute16;
1520 ELSE
1521 l_attribute16 := p_formula_header_rec.attribute16;
1522 END IF;
1523
1524 IF (p_formula_header_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1525 l_attribute17 := NULL;
1526 ELSIF (p_formula_header_rec.attribute17 IS NULL) THEN
1527 l_attribute17 := fm_form_mst_rec.attribute17;
1528 ELSE
1529 l_attribute17 := p_formula_header_rec.attribute17;
1530 END IF;
1531
1532 IF (p_formula_header_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1533 l_attribute18 := NULL;
1534 ELSIF (p_formula_header_rec.attribute18 IS NULL) THEN
1535 l_attribute18 := fm_form_mst_rec.attribute18;
1536 ELSE
1537 l_attribute18 := p_formula_header_rec.attribute18;
1538 END IF;
1539
1540 IF (p_formula_header_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1541 l_attribute19 := NULL;
1542 ELSIF (p_formula_header_rec.attribute19 IS NULL) THEN
1543 l_attribute19 := fm_form_mst_rec.attribute19;
1544 ELSE
1545 l_attribute19 := p_formula_header_rec.attribute19;
1546 END IF;
1547
1548 IF (p_formula_header_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1549 l_attribute20 := NULL;
1550 ELSIF (p_formula_header_rec.attribute20 IS NULL) THEN
1551 l_attribute20 := fm_form_mst_rec.attribute20;
1552 ELSE
1553 l_attribute20 := p_formula_header_rec.attribute20;
1554 END IF;
1555
1556 IF (p_formula_header_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1557 l_attribute21 := NULL;
1558 ELSIF (p_formula_header_rec.attribute21 IS NULL) THEN
1559 l_attribute21 := fm_form_mst_rec.attribute21;
1560 ELSE
1561 l_attribute21 := p_formula_header_rec.attribute21;
1562 END IF;
1563
1564 IF (p_formula_header_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1565 l_attribute22 := NULL;
1566 ELSIF (p_formula_header_rec.attribute22 IS NULL) THEN
1567 l_attribute22 := fm_form_mst_rec.attribute22;
1568 ELSE
1569 l_attribute22 := p_formula_header_rec.attribute22;
1570 END IF;
1571
1572 IF (p_formula_header_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1573 l_attribute23 := NULL;
1574 ELSIF (p_formula_header_rec.attribute23 IS NULL) THEN
1575 l_attribute23 := fm_form_mst_rec.attribute23;
1576 ELSE
1577 l_attribute23 := p_formula_header_rec.attribute23;
1578 END IF;
1579
1580 IF (p_formula_header_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1581 l_attribute24 := NULL;
1582 ELSIF (p_formula_header_rec.attribute24 IS NULL) THEN
1583 l_attribute24 := fm_form_mst_rec.attribute24;
1584 ELSE
1585 l_attribute24 := p_formula_header_rec.attribute24;
1586 END IF;
1587
1588 IF (p_formula_header_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1589 l_attribute25 := NULL;
1590 ELSIF (p_formula_header_rec.attribute25 IS NULL) THEN
1591 l_attribute25 := fm_form_mst_rec.attribute25;
1592 ELSE
1593 l_attribute25 := p_formula_header_rec.attribute25;
1594 END IF;
1595
1596 IF (p_formula_header_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1597 l_attribute26 := NULL;
1598 ELSIF (p_formula_header_rec.attribute26 IS NULL) THEN
1599 l_attribute26 := fm_form_mst_rec.attribute26;
1600 ELSE
1601 l_attribute26 := p_formula_header_rec.attribute26;
1602 END IF;
1603
1604 IF (p_formula_header_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1605 l_attribute27 := NULL;
1606 ELSIF (p_formula_header_rec.attribute27 IS NULL) THEN
1607 l_attribute27 := fm_form_mst_rec.attribute27;
1608 ELSE
1609 l_attribute27 := p_formula_header_rec.attribute27;
1610 END IF;
1611
1612 IF (p_formula_header_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1613 l_attribute28 := NULL;
1614 ELSIF (p_formula_header_rec.attribute28 IS NULL) THEN
1615 l_attribute28 := fm_form_mst_rec.attribute28;
1616 ELSE
1617 l_attribute28 := p_formula_header_rec.attribute28;
1618 END IF;
1619
1620 IF (p_formula_header_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1621 l_attribute29 := NULL;
1622 ELSIF (p_formula_header_rec.attribute29 IS NULL) THEN
1623 l_attribute29 := fm_form_mst_rec.attribute29;
1624 ELSE
1625 l_attribute29 := p_formula_header_rec.attribute29;
1626 END IF;
1627
1628 IF (p_formula_header_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1629 l_attribute30 := NULL;
1630 ELSIF (p_formula_header_rec.attribute30 IS NULL) THEN
1631 l_attribute30 := fm_form_mst_rec.attribute30;
1632 ELSE
1633 l_attribute30 := p_formula_header_rec.attribute30;
1634 END IF;
1635
1636 END IF;
1637
1638 CLOSE get_header_rec;
1639
1640
1641
1642 /* ========================================================= */
1643 /* Validate formula_class KSHUKLA added as per as bug 5111320*/
1644 /* ========================================================== */
1645 IF (l_debug = 'Y') THEN
1646 gmd_debug.put_line(' Before formula class validation - '
1647 ||p_formula_header_rec.formula_class
1648 ||' - '
1649 ||x_return_status);
1650 END IF;
1651 IF (l_formula_class IS NOT NULL ) THEN
1652 l_return_val := GMDFMVAL_PUB.formula_class_val(
1653 l_formula_class);
1654 IF (l_return_val <> 0) THEN
1655 FND_MESSAGE.SET_NAME('GMD', 'FM_INVCLASS');
1656 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1657 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1658 FND_MSG_PUB.Add;
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661 END IF;
1662
1663 /* Call the private API to update the header info */
1664 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1665 l_fm_form_mst_rec.formula_id := l_formula_id;
1666 l_fm_form_mst_rec.formula_no := p_formula_header_rec.formula_no;
1667 l_fm_form_mst_rec.formula_vers := p_formula_header_rec.formula_vers;
1668 l_fm_form_mst_rec.formula_type := p_formula_header_rec.formula_type;
1669 l_fm_form_mst_rec.scale_type := p_formula_header_rec.scale_type_hdr;
1670 l_fm_form_mst_rec.formula_desc1 := p_formula_header_rec.formula_desc1;
1671 l_fm_form_mst_rec.formula_desc2 := l_formula_desc2;
1672 l_fm_form_mst_rec.formula_class := l_formula_class;
1673 l_fm_form_mst_rec.fmcontrol_class := p_formula_header_rec.fmcontrol_class;
1674 l_fm_form_mst_rec.in_use := 0;
1675 l_fm_form_mst_rec.inactive_ind := p_formula_header_rec.inactive_ind;
1676 l_fm_form_mst_rec.owner_organization_id := p_formula_header_rec.owner_organization_id;
1677 l_fm_form_mst_rec.total_input_qty := p_formula_header_rec.total_input_qty ;
1678 l_fm_form_mst_rec.total_output_qty := p_formula_header_rec.total_output_qty ;
1679 l_fm_form_mst_rec.yield_uom := p_formula_header_rec.yield_uom;
1680 l_fm_form_mst_rec.formula_status := p_formula_header_rec.formula_status ;
1681 l_fm_form_mst_rec.owner_id := p_formula_header_rec.owner_id ;
1682 l_fm_form_mst_rec.attribute1 := l_attribute1;
1683 l_fm_form_mst_rec.attribute2 := l_attribute2;
1684 l_fm_form_mst_rec.attribute3 := l_attribute3;
1685 l_fm_form_mst_rec.attribute4 := l_attribute4;
1686 l_fm_form_mst_rec.attribute5 := l_attribute5;
1687 l_fm_form_mst_rec.attribute6 := l_attribute6;
1688 l_fm_form_mst_rec.attribute7 := l_attribute7;
1689 l_fm_form_mst_rec.attribute8 := l_attribute8;
1690 l_fm_form_mst_rec.attribute9 := l_attribute9;
1691 l_fm_form_mst_rec.attribute10 := l_attribute10;
1692 l_fm_form_mst_rec.attribute11 := l_attribute11;
1693 l_fm_form_mst_rec.attribute12 := l_attribute12;
1694 l_fm_form_mst_rec.attribute13 := l_attribute13;
1695 l_fm_form_mst_rec.attribute14 := l_attribute14;
1696 l_fm_form_mst_rec.attribute15 := l_attribute15;
1697 l_fm_form_mst_rec.attribute16 := l_attribute16;
1698 l_fm_form_mst_rec.attribute17 := l_attribute17;
1699 l_fm_form_mst_rec.attribute18 := l_attribute18;
1700 l_fm_form_mst_rec.attribute19 := l_attribute19;
1701 l_fm_form_mst_rec.attribute20 := l_attribute20;
1702 l_fm_form_mst_rec.attribute21 := l_attribute21;
1703 l_fm_form_mst_rec.attribute22 := l_attribute22;
1704 l_fm_form_mst_rec.attribute23 := l_attribute23;
1705 l_fm_form_mst_rec.attribute24 := l_attribute24;
1706 l_fm_form_mst_rec.attribute25 := l_attribute25;
1707 l_fm_form_mst_rec.attribute26 := l_attribute26;
1708 l_fm_form_mst_rec.attribute27 := l_attribute27;
1709 l_fm_form_mst_rec.attribute28 := l_attribute28;
1710 l_fm_form_mst_rec.attribute29 := l_attribute29;
1711 l_fm_form_mst_rec.attribute30 := l_attribute30;
1712 l_fm_form_mst_rec.attribute_category := l_attribute_category;
1713 l_fm_form_mst_rec.text_code := l_text_code;
1714 l_fm_form_mst_rec.delete_mark := p_formula_header_rec.delete_mark; /* Important */
1715 l_fm_form_mst_rec.created_by := p_formula_header_rec.created_by;
1716 l_fm_form_mst_rec.creation_date := p_formula_header_rec.creation_date;
1717 l_fm_form_mst_rec.last_update_date := p_formula_header_rec.last_update_date;
1718 l_fm_form_mst_rec.last_update_login := p_formula_header_rec.last_update_login;
1719 l_fm_form_mst_rec.last_updated_by := p_formula_header_rec.last_updated_by;
1720 -- Kapil ME Auto-Prod :Bug# 5716318
1721 l_fm_form_mst_rec.auto_product_calc := p_formula_header_rec.auto_product_calc;
1722
1723 /* Validate all optional parameters passed */
1724 IF (l_debug = 'Y') THEN
1725 gmd_debug.put_line(' Before calling the private fm update API - '
1726 ||x_return_status);
1727 END IF;
1728 GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
1729 ( p_api_version => 1.0
1730 ,p_init_msg_list => p_init_msg_list
1731 ,p_commit => FND_API.G_FALSE
1732 ,x_return_status => x_return_status
1733 ,x_msg_count => x_msg_count
1734 ,x_msg_data => x_msg_data
1735 ,p_formula_header_rec => l_fm_form_mst_rec
1736 );
1737 IF (l_debug = 'Y') THEN
1738 gmd_debug.put_line('After the private fm update API - '
1739 ||x_return_status);
1740 END IF;
1741
1742 l_lastformula_id := l_formula_id; -- Assign the last formula header updated.
1743
1744 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
1745 p_formula_header_rec.auto_product_calc = 'N' THEN
1746 update FM_MATL_DTL
1747 SET PROD_PERCENT = NULL
1748 where formula_id = l_formula_id
1749 and line_type = 1
1750 and scale_type = 1;
1751 IF p_formula_header_rec.scale_type_hdr = 0 THEN
1752 UPDATE FM_MATL_DTL
1753 SET SCALE_TYPE = 0
1754 WHERE formula_id = l_formula_id;
1755 END IF;
1756 END IF;
1757
1758 END IF; /* end after update of header */
1759
1760 /* IF updating a header fails - we Raise an exception
1761 rather than trying to update other header details */
1762 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1763 RAISE FND_API.G_EXC_ERROR;
1764 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF;
1767
1768 END LOOP;
1769
1770 /* End of API body */
1771 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') THEN
1772 /* Check if p_commit is set to TRUE */
1773 IF FND_API.To_Boolean( p_commit ) THEN
1774 Commit;
1775 END IF;
1776 END IF;
1777
1778 /* Get the message count and information */
1779 FND_MSG_PUB.Count_And_Get (
1780 p_count => x_msg_count,
1781 p_data => x_msg_data );
1782
1783 EXCEPTION
1784 WHEN FND_API.G_EXC_ERROR THEN
1785 ROLLBACK to Update_FormulaHeader_PUB;
1786 x_return_status := FND_API.G_RET_STS_ERROR;
1787 FND_MSG_PUB.Count_And_Get (
1788 p_count => x_msg_count,
1789 p_data => x_msg_data );
1790 IF (l_debug = 'Y') THEN
1791 gmd_debug.put_line(' In Formula Header Pub - In Error Exception Section '
1792 ||' - '
1793 ||x_return_status);
1794 END IF;
1795 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1796 ROLLBACK to Update_FormulaHeader_PUB;
1797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798 FND_MSG_PUB.Count_And_Get (
1799 p_count => x_msg_count,
1800 p_data => x_msg_data );
1801 IF (l_debug = 'Y') THEN
1802 gmd_debug.put_line(' In Formula Header Pub - In unexpected Exception Section '
1803 ||' - '
1804 ||x_return_status);
1805 END IF;
1806 WHEN OTHERS THEN
1807 ROLLBACK to Update_FormulaHeader_PUB;
1808 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810 FND_MSG_PUB.Count_And_Get (
1811 p_count => x_msg_count,
1812 p_data => x_msg_data );
1813 IF (l_debug = 'Y') THEN
1814 gmd_debug.put_line(' In Formula Header Pub - In OTHERs Exception Section '
1815 ||' - '
1816 ||x_return_status);
1817 END IF;
1818 END Update_FormulaHeader;
1819
1820
1821 /* ======================================================================== */
1822 /* Procedure: */
1823 /* Delete_FormulaHeader */
1824 /* */
1825 /* DESCRIPTION: */
1826 /* This PL/SQL procedure is responsible for deleting a formula. */
1827 /* ======================================================================== */
1828
1829 PROCEDURE Delete_FormulaHeader
1830 ( p_api_version IN NUMBER
1831 ,p_init_msg_list IN VARCHAR2
1832 ,p_commit IN VARCHAR2
1833 ,p_called_from_forms IN VARCHAR2
1834 ,x_return_status OUT NOCOPY VARCHAR2
1835 ,x_msg_count OUT NOCOPY NUMBER
1836 ,x_msg_data OUT NOCOPY VARCHAR2
1837 ,p_formula_header_tbl IN FORMULA_UPDATE_HDR_TBL_TYPE
1838 )
1839 IS
1840 /* Local Variables definitions */
1841 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMULAHEADER';
1842 l_api_version CONSTANT NUMBER := 1.0;
1843 l_user_id fnd_user.user_id%TYPE := FND_GLOBAL.user_id; -- Bug 4603060
1844 l_return_val NUMBER := 0;
1845 l_item_id ic_item_mst.item_id%TYPE := 0;
1846 l_inv_uom ic_item_mst.item_um%TYPE := NULL;
1847 l_formula_id fm_form_mst.formula_id%TYPE := 0;
1848
1849 l_fm_form_mst_rec fm_form_mst%ROWTYPE;
1850 l_return_status VARCHAR2(1);
1851 p_formula_header_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1852
1853 CURSOR get_fm_db_rec(vFormula_id NUMBER) IS
1854 SELECT *
1855 FROM fm_form_mst
1856 WHERE formula_id = vFormula_id;
1857
1858
1859 BEGIN
1860 /* Define Savepoint */
1861 SAVEPOINT Delete_FormulaHeader_PUB;
1862
1863 /* Standard Check for API compatibility */
1864 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1865 p_api_version ,
1866 l_api_name ,
1867 G_PKG_NAME )
1868 THEN
1869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1870 END IF;
1871
1872 /* Initialize message list if p_init_msg_list is set to TRUE */
1873 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1874 FND_MSG_PUB.initialize;
1875 END IF;
1876 /* Initialize API return status to success */
1877 x_return_status := FND_API.G_RET_STS_SUCCESS;
1878
1879 /* Start looping through the table */
1880 IF (p_formula_header_tbl.count = 0) THEN
1881 RAISE FND_API.G_EXC_ERROR;
1882 END IF;
1883
1884 FOR i IN 1 .. p_formula_header_tbl.count LOOP
1885
1886 p_formula_header_rec := p_formula_header_tbl(i);
1887
1888 /* ======================================= */
1889 /* Check if there is a valid userid/ownerid */
1890 /* ======================================== */
1891 -- Bug 4603060
1892 IF (l_user_id IS NULL) THEN
1893 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
1894 FND_MSG_PUB.Add;
1895 RAISE FND_API.G_EXC_ERROR;
1896 END IF;
1897
1898
1899 /* =================================== */
1900 /* Check if an appropriate action_code */
1901 /* has been supplied */
1902 /* ================================== */
1903 IF (p_formula_header_rec.record_type <> 'D') THEN
1904 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_ACTION');
1905 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_header_rec.formula_no);
1906 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_header_rec.formula_vers);
1907 FND_MSG_PUB.Add;
1908 RAISE FND_API.G_EXC_ERROR;
1909 END IF;
1910
1911 /* ======================== */
1912 /* Get the formula id value */
1913 /* We need a formula id to */
1914 /* delete a header */
1915 /* ======================= */
1916 IF (p_formula_header_rec.formula_id is NULL) THEN
1917 GMDFMVAL_PUB.get_formula_id(p_formula_header_rec.formula_no,
1918 p_formula_header_rec.formula_vers,
1919 l_formula_id, l_return_val);
1920 IF (l_return_val <> 0) THEN
1921 IF (p_formula_header_rec.formula_no IS NULL) THEN
1922 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
1923 FND_MSG_PUB.Add;
1924 RAISE FND_API.G_EXC_ERROR;
1925 ELSIF (p_formula_header_rec.formula_vers IS NULL) THEN
1926 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
1927 FND_MSG_PUB.Add;
1928 RAISE FND_API.G_EXC_ERROR;
1929 ELSE
1930 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_FORMULA_ID');
1931 FND_MSG_PUB.Add;
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 END IF;
1935 ELSE
1936 l_formula_id := p_formula_header_rec.formula_id;
1937 END IF;
1938
1939 IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
1940 FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
1941 FND_MESSAGE.SET_TOKEN('NAME', 'formula');
1942 FND_MESSAGE.SET_TOKEN('ID', l_formula_id);
1943 FND_MESSAGE.SET_TOKEN('NO', p_formula_header_rec.formula_no);
1944 FND_MESSAGE.SET_TOKEN('VERS', p_formula_header_rec.formula_vers);
1945 FND_MESSAGE.SET_TOKEN('STATUS',get_fm_status_meaning(l_formula_id));
1946 FND_MSG_PUB.Add;
1947 RAISE FND_API.G_EXC_ERROR;
1948 END IF;
1949
1950 /* Call the private API to update the header info */
1951 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1952 FOR l_formula_rec IN get_fm_db_rec(l_formula_id) LOOP
1953
1954 l_fm_form_mst_rec.formula_id := l_formula_id;
1955 l_fm_form_mst_rec.formula_no := l_formula_rec.formula_no;
1956 l_fm_form_mst_rec.formula_vers := l_formula_rec.formula_vers;
1957 l_fm_form_mst_rec.formula_type := l_formula_rec.formula_type;
1958 l_fm_form_mst_rec.scale_type := l_formula_rec.scale_type;
1959 l_fm_form_mst_rec.formula_desc1 := l_formula_rec.formula_desc1;
1960 l_fm_form_mst_rec.formula_desc2 := l_formula_rec.formula_desc2;
1961 l_fm_form_mst_rec.formula_class := l_formula_rec.formula_class;
1962 l_fm_form_mst_rec.fmcontrol_class := l_formula_rec.fmcontrol_class;
1963 l_fm_form_mst_rec.in_use := 0;
1964 l_fm_form_mst_rec.inactive_ind := l_formula_rec.inactive_ind;
1965 l_fm_form_mst_rec.owner_organization_id := l_formula_rec.owner_organization_id;
1966 l_fm_form_mst_rec.total_input_qty := l_formula_rec.total_input_qty ;
1967 l_fm_form_mst_rec.total_output_qty := l_formula_rec.total_output_qty ;
1968 l_fm_form_mst_rec.yield_uom := l_formula_rec.yield_uom;
1969 l_fm_form_mst_rec.formula_status := l_formula_rec.formula_status ;
1970 l_fm_form_mst_rec.owner_id := l_formula_rec.owner_id ;
1971 l_fm_form_mst_rec.attribute1 := l_formula_rec.attribute1;
1972 l_fm_form_mst_rec.attribute2 := l_formula_rec.attribute2;
1973 l_fm_form_mst_rec.attribute3 := l_formula_rec.attribute3;
1974 l_fm_form_mst_rec.attribute4 := l_formula_rec.attribute4;
1975 l_fm_form_mst_rec.attribute5 := l_formula_rec.attribute5;
1976 l_fm_form_mst_rec.attribute6 := l_formula_rec.attribute6;
1977 l_fm_form_mst_rec.attribute7 := l_formula_rec.attribute7;
1978 l_fm_form_mst_rec.attribute8 := l_formula_rec.attribute8;
1979 l_fm_form_mst_rec.attribute9 := l_formula_rec.attribute9;
1980 l_fm_form_mst_rec.attribute10 := l_formula_rec.attribute10;
1981 l_fm_form_mst_rec.attribute11 := l_formula_rec.attribute11;
1982 l_fm_form_mst_rec.attribute12 := l_formula_rec.attribute12;
1983 l_fm_form_mst_rec.attribute13 := l_formula_rec.attribute13;
1984 l_fm_form_mst_rec.attribute14 := l_formula_rec.attribute14;
1985 l_fm_form_mst_rec.attribute15 := l_formula_rec.attribute15;
1986 l_fm_form_mst_rec.attribute16 := l_formula_rec.attribute16;
1987 l_fm_form_mst_rec.attribute17 := l_formula_rec.attribute17;
1988 l_fm_form_mst_rec.attribute18 := l_formula_rec.attribute18;
1989 l_fm_form_mst_rec.attribute19 := l_formula_rec.attribute19;
1990 l_fm_form_mst_rec.attribute20 := l_formula_rec.attribute20;
1991 l_fm_form_mst_rec.attribute21 := l_formula_rec.attribute21;
1992 l_fm_form_mst_rec.attribute22 := l_formula_rec.attribute22;
1993 l_fm_form_mst_rec.attribute23 := l_formula_rec.attribute23;
1994 l_fm_form_mst_rec.attribute24 := l_formula_rec.attribute24;
1995 l_fm_form_mst_rec.attribute25 := l_formula_rec.attribute25;
1996 l_fm_form_mst_rec.attribute26 := l_formula_rec.attribute26;
1997 l_fm_form_mst_rec.attribute27 := l_formula_rec.attribute27;
1998 l_fm_form_mst_rec.attribute28 := l_formula_rec.attribute28;
1999 l_fm_form_mst_rec.attribute29 := l_formula_rec.attribute29;
2000 l_fm_form_mst_rec.attribute30 := l_formula_rec.attribute30;
2001 l_fm_form_mst_rec.attribute_category := l_formula_rec.attribute_category;
2002 l_fm_form_mst_rec.text_code := l_formula_rec.text_code;
2003 l_fm_form_mst_rec.delete_mark := 1; /* Important */
2004 l_fm_form_mst_rec.created_by := l_formula_rec.created_by;
2005 l_fm_form_mst_rec.creation_date := l_formula_rec.creation_date;
2006 l_fm_form_mst_rec.last_update_date := l_formula_rec.last_update_date;
2007 l_fm_form_mst_rec.last_update_login := l_formula_rec.last_update_login;
2008 l_fm_form_mst_rec.last_updated_by := l_formula_rec.last_updated_by;
2009
2010 END LOOP;
2011
2012 GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
2013 ( p_api_version => p_api_version
2014 ,p_init_msg_list => p_init_msg_list
2015 ,p_commit => FND_API.G_FALSE
2016 ,x_return_status => x_return_status
2017 ,x_msg_count => x_msg_count
2018 ,x_msg_data => x_msg_data
2019 ,p_formula_header_rec => l_fm_form_mst_rec
2020 );
2021
2022 END IF;
2023
2024 /* IF deleting a header fails - we Raise an exception
2025 rather than trying to delete other header details */
2026 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2027 RAISE FND_API.G_EXC_ERROR;
2028 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2030 END IF;
2031
2032 END LOOP;
2033
2034 /* End of API body */
2035 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') THEN
2036 /* Check if p_commit is set to TRUE */
2037 IF FND_API.To_Boolean( p_commit ) THEN
2038 Commit;
2039 END IF;
2040 END IF;
2041
2042 /* Get the message count and information */
2043 FND_MSG_PUB.Count_And_Get (
2044 p_count => x_msg_count,
2045 p_data => x_msg_data );
2046
2047 EXCEPTION
2048 WHEN FND_API.G_EXC_ERROR THEN
2049 ROLLBACK to Delete_FormulaHeader_PUB;
2050 x_return_status := FND_API.G_RET_STS_ERROR;
2051 FND_MSG_PUB.Count_And_Get (
2052 p_count => x_msg_count,
2053 p_data => x_msg_data );
2054
2055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2056 ROLLBACK to Delete_FormulaHeader_PUB;
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058 FND_MSG_PUB.Count_And_Get (
2059 p_count => x_msg_count,
2060 p_data => x_msg_data );
2061
2062 WHEN OTHERS THEN
2063 ROLLBACK to Delete_FormulaHeader_PUB;
2064 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 FND_MSG_PUB.Count_And_Get (
2067 p_count => x_msg_count,
2068 p_data => x_msg_data );
2069
2070 END Delete_FormulaHeader;
2071
2072 END GMD_FORMULA_PUB;
[Home] [Help]