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