1 PACKAGE BODY GMD_FORMULA_DETAIL_PUB AS
2 /* $Header: GMDPFMDB.pls 120.12.12020000.3 2012/11/13 06:47:26 mtou ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FORMULA_DETAIL_PUB' ;
5 pRecord_in GMDFMVAL_PUB.formula_info_in;
6 pTable_out GMDFMVAL_PUB.formula_table_out;
7 lreturn VARCHAR2(1);
8
9 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
10 --Forward declaration.
11 FUNCTION set_debug_flag RETURN VARCHAR2;
12 l_debug VARCHAR2(1) := set_debug_flag;
13
14 FUNCTION set_debug_flag RETURN VARCHAR2 IS
15 l_debug VARCHAR2(1):= 'N';
16 BEGIN
17 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
18 l_debug := 'Y';
19 END IF;
20 RETURN l_debug;
21 END set_debug_flag;
22 --Bug 3222090, NSRIVAST 20-FEB-2004, END
23
24 FUNCTION get_fm_status_meaning(vFormula_id NUMBER) RETURN VARCHAR2 IS
25 CURSOR get_status_meaning(P_Status_code VARCHAR2) IS
26 SELECT meaning
27 FROM gmd_status
28 WHERE status_code = P_status_code;
29
30 l_status_meaning GMD_STATUS.meaning%TYPE;
31
32 BEGIN
33 FOR C_status_code IN (Select formula_status from fm_form_mst_b
34 where formula_id = vFormula_id) LOOP
35 OPEN get_status_meaning(C_status_code.formula_status);
36 FETCH get_status_meaning INTO l_status_meaning;
37 CLOSE get_status_meaning;
38
39 END LOOP;
40
41 RETURN l_status_meaning;
42
43 END get_fm_status_meaning;
44
45 /* ======================================================================== */
46 /* Procedure: */
47 /* Insert_FormulaDetail */
48 /* */
49 /* DESCRIPTION: */
50 /* This PL/SQL procedure is responsible for */
51 /* inserting a formula detail. */
52 /* HISTORY: */
53 /* 10-Apr-2003 P.Raghu Bug#2893682 Modified the code such that */
54 /* p_formula_detail_rec.item_no is correctly set to */
55 /* ITEM_NO TOKEN. Uncommented the assigment statement*/
56 /* of GMDFMVAL_PUB.p_called_from_forms package */
57 /* variable in Insert_FormulaDetail procedure. */
58 /* 18-Apr-2003 J. Baird Bug #2908311 Uncommented initialization of */
59 /* x_return_status */
60 /* 18-Apr-2003 J. Baird Bug #2906124 Was not setting the TO_UOM token. */
61 /* ======================================================================== */
62 PROCEDURE Insert_FormulaDetail
63 ( p_api_version IN NUMBER
64 ,p_init_msg_list IN VARCHAR2
65 ,p_commit IN VARCHAR2
66 ,p_called_from_forms IN VARCHAR2 := 'NO'
67 ,x_return_status OUT NOCOPY VARCHAR2
68 ,x_msg_count OUT NOCOPY NUMBER
69 ,x_msg_data OUT NOCOPY VARCHAR2
70 ,p_formula_detail_tbl IN formula_insert_dtl_tbl_type
71 )
72 IS
73 /* Local Variables definitions */
74 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULADETAIL';
75 l_api_version CONSTANT NUMBER := 1.0;
76 l_user_id fnd_user.user_id%TYPE := 0;
77 l_return_val NUMBER := 0;
78 l_item_id mtl_system_items.inventory_item_id%TYPE := 0;
79 l_inv_uom mtl_system_items.primary_uom_code%TYPE := NULL;
80 l_formula_id fm_matl_dtl.formula_id%TYPE := 0;
81 l_surrogate fm_matl_dtl.formulaline_id%TYPE := 0;
82
83 /* Record type definition */
84 l_fm_matl_dtl_rec fm_matl_dtl%ROWTYPE;
85 p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
86 X_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_insert_rec_type;
87
88 CURSOR C_get_orgid (V_formula_id NUMBER) IS
89 SELECT owner_organization_id
90 FROM fm_form_mst_b
91 WHERE formula_id = V_formula_id;
92 l_org_id NUMBER;
93
94 -- Kapil ME Auto-Prod :Bug#5716318
95 l_auto_calc VARCHAR2(1);
96 l_formula_calc_flag VARCHAR2(1);
97
98 CURSOR C_get_auto_parameter (V_formula_id NUMBER) IS
99 SELECT AUTO_PRODUCT_CALC
100 FROM FM_FORM_MST_B
101 WHERE FORMULA_ID = V_formula_id;
102
103 v_item_no varchar2(30); -- Added in Bug No.6799624
104 v_recipe_enabled varchar2(1); -- Added in Bug No.6799624
105
106 new_line_no Number(5) ; /* Added in Bug No.7328802 */
107
108 BEGIN
109 /* Define Savepoint */
110 SAVEPOINT Insert_FormulaDetail;
111
112 /* Standard Check for API compatibility */
113 IF NOT FND_API.Compatible_API_Call(l_api_version
114 ,p_api_version
115 ,l_api_name
116 ,G_PKG_NAME )
117 THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120
121 /* Initialize message list if p_init_msg_list is set to TRUE */
122 IF FND_API.to_Boolean( p_init_msg_list ) THEN
123 FND_MSG_PUB.initialize;
124 END IF;
125
126 --Set the formula validation pkg variable GMDFMVAL_PUB
127 --variable p_called_from_form same as that passed in.
128 --When API is called from forms the parameter p_called_from_forms is set
129 --to 'YES' and the same parameter is set to 'YES' within the validation pkg.
130 --When API is not called from forms the parameter is 'NO'.
131
132 --BEGIN BUG#2893682 P.Raghu
133 --Uncommenting the following statement such that the actual value
134 --is passed to the GMDFMVAL_PUB API.
135 GMDFMVAL_PUB.p_called_from_forms := p_called_from_forms;
136 --END BUG#2893682
137
138 /* API body */
139 /* 1. Does validation when not called from forms because from forms all
140 field level validation is already done */
141 /* 2. Call the private API that does the database inserts/ updates */
142 IF (p_formula_detail_tbl.count = 0) THEN
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 /* Start looping through the table */
147 FOR i in 1 .. p_formula_detail_tbl.count LOOP
148
149 /* Initialize API return status to success for every line */
150 x_return_status := FND_API.G_RET_STS_SUCCESS;
151
152 IF (l_debug = 'Y') THEN
153 gmd_debug.put_line(' ');
154 gmd_debug.put_line(' ');
155 END IF;
156
157 IF (l_debug = 'Y') THEN
158 gmd_debug.put_line(' In Formula Detail Pub - Entering loop with row # '||i);
159 END IF;
160
161 p_formula_detail_rec := p_formula_detail_tbl(i);
162
163 /* New record to get different entity values */
164 pRecord_in.formula_no := p_formula_detail_rec.formula_no;
165 pRecord_in.formula_vers := p_formula_detail_rec.formula_vers;
166 pRecord_in.formula_id := p_formula_detail_rec.formula_id;
167 -- Bug 4603060 pRecord_in.user_name := p_formula_detail_rec.user_name;
168
169 /* Procedure get_element based on the element_name return all
170 information about it. For e.g. if element_name is formula
171 and if we input the formula_id in pRecord_in it returns the
172 formula_no and vers information and visa versa too */
173 /* ================================ */
174 /* Get the formula id if it is NULL */
175 /* ================================ */
176 IF (l_debug = 'Y') THEN
177 gmd_debug.put_line(' In Formula Detail Pub - Before formula id val '
178 ||x_return_status);
179 END IF;
180
181 IF (p_formula_detail_rec.formula_id is NULL) THEN
182 GMDFMVAL_PUB.get_element(pElement_name => 'FORMULA',
183 pRecord_in => pRecord_in,
184 xTable_out => pTable_out,
185 xReturn => x_return_status);
186 IF (x_return_status <> 'S') THEN
187 IF (p_formula_detail_rec.formula_no IS NULL) THEN
188 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
189 FND_MSG_PUB.Add;
190 ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
191 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
192 FND_MSG_PUB.Add;
193 ELSE
194 FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
195 FND_MESSAGE.SET_TOKEN('FORMULA_NO',p_formula_detail_rec.formula_no);
196 FND_MSG_PUB.Add;
197 END IF;
198 RAISE FND_API.G_EXC_ERROR;
199 ELSE
200 l_formula_id := pTable_out(1).formula_id;
201 END IF; /* end condition for x_ret)status <> 'S' */
202 ELSE
203 l_formula_id := p_formula_detail_rec.formula_id;
204 END IF;
205
206 OPEN C_get_orgid (l_formula_id);
207 FETCH C_get_orgid INTO l_org_id;
208 CLOSE C_get_orgid;
209
210 p_formula_detail_rec.owner_organization_id := l_org_id;
211
212 IF (p_formula_detail_rec.inventory_item_id is NULL AND p_formula_detail_rec.item_no IS NULL) THEN
213 FND_MESSAGE.SET_NAME('GMI', 'GMI_API_ITEM_NOT_FOUND');
214 FND_MSG_PUB.Add;
215 ELSE
216 GMDFMVAL_PUB.get_item_id(pitem_no => p_formula_detail_rec.item_no,
217 pinventory_item_id => p_formula_detail_rec.inventory_item_id,
218 porganization_id => l_org_id,
219 xitem_id => l_item_id,
220 xitem_um => l_inv_uom,
221 xreturn_code => l_return_val);
222 IF (l_return_val < 0) THEN
223 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
224 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_formula_detail_rec.item_no);
225 FND_MSG_PUB.Add;
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228 END IF;
229 p_formula_detail_rec.inventory_item_id := l_item_id;
230
231 /* Bug No.6799624 - Start */
232
233 BEGIN
234 SELECT segment1,recipe_enabled_flag INTO v_item_no, v_recipe_enabled
235 FROM mtl_system_items_b
236 WHERE inventory_item_id = p_formula_detail_rec.inventory_item_id AND
237 organization_id = p_formula_detail_rec.owner_organization_id;
238 EXCEPTION
239 WHEN others THEN
240 ROLLBACK to Insert_FormulaDetail;
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 FND_MSG_PUB.Count_And_Get (
243 p_count => x_msg_count,
244 p_data => x_msg_data );
245 END;
246
247 IF v_recipe_enabled <> 'Y' THEN
248 FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_NOT_RECIPE_ENABLED');
249 FND_MESSAGE.SET_TOKEN('ITEM_NO', v_item_no);
250 FND_MSG_PUB.Add;
251 RAISE FND_API.G_EXC_ERROR;
252 END IF ;
253
254 /* Bug No. 6799624 - End */
255
256 IF (l_debug = 'Y') THEN
257 gmd_debug.put_line(' In Formula Detail Pub - Before User_id val');
258 END IF;
259 -- Bug 4603060 Use the user from context
260 l_user_id := FND_GLOBAL.user_id;
261 IF (l_user_id IS NULL) THEN
262 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
263 FND_MSG_PUB.Add;
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 /* ======================================= */
268 /* Check if the same line no and type */
269 /* for that formula does no already exists */
270 /* ======================================= */
271 IF (l_debug = 'Y') THEN
272 gmd_debug.put_line(' In Formula Detail Pub - Before detail lines val '
273 ||x_return_status);
274 END IF;
275
276
277 /* Bug No.7328802 - Start */
278
279 IF NVL(p_called_from_forms,'NO') <> 'YES' THEN
280
281 SELECT nvl(max(line_no),0)+1 INTO new_line_no FROM fm_matl_dtl
282 WHERE formula_id = l_formula_id AND
283 line_type = p_formula_detail_rec.line_type;
284
285 p_formula_detail_rec.line_no := new_line_no;
286
287 END IF;
288
289 /* Bug No.7328802 - End */
290
291 l_return_val := GMDFMVAL_PUB.detail_line_val
292 (l_formula_id,
293 p_formula_detail_rec.line_no,
294 p_formula_detail_rec.line_type);
295 IF (l_return_val <> 0) THEN
296 FND_MESSAGE.SET_NAME('GMD','FM_DUPLICATE_LINE_NO');
297 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_formula_detail_rec.item_no);
298 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
299 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',p_formula_detail_rec.formula_vers );
300 FND_MSG_PUB.Add;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 /* Get the item_id which is based on the item no */
305 IF (l_debug = 'Y') THEN
306 gmd_debug.put_line(' In Formula Detail Pub - Before item id val '
307 ||x_return_status);
308 END IF;
309
310 /* Get the formula line id which is a surrogate key */
311 IF (l_debug = 'Y') THEN
312 gmd_debug.put_line(' In Formula Detail Pub - Get the surrogate key '
313 ||' fmline id = '
314 ||p_formula_detail_rec.formulaline_id
315 ||' - '
316 ||x_return_status);
317 END IF;
318 IF (p_formula_detail_rec.formulaline_id IS NULL) THEN
319 l_surrogate := GMDSURG.get_surrogate('formulaline_id');
320 /* Call for private API */
321 IF (l_surrogate < 1) THEN
322 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_FMLINE_ID');
323 FND_MSG_PUB.Add;
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326 ELSE
327 l_surrogate := p_formula_detail_rec.formulaline_id;
328 END IF;
329
330 /* Beyond this all validations are made ONLY WHEN THIS API IS
331 NOT CALLED BY FORMS */
332 /* When coming from forms all these validations are already
333 done, so we can skip the validations below. */
334
335
336 IF (NVL(p_called_from_forms,'NO') = 'NO') THEN
337 GMDFMVAL_PUB.validate_insert_record (P_formula_dtl => P_formula_detail_rec,
338 X_formula_dtl => X_formula_detail_rec,
339 xReturn => X_return_status);
340 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
341 RAISE FND_API.G_EXC_ERROR;
342 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345 ELSE
346 X_formula_detail_rec := P_formula_detail_rec;
347 END IF;
348
349 -- Kapil ME Auto-Prod :Bug#5716318
350 /* Get the Organization Parameter and the Parameter set at the Formula level */
351 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_org_id,
352 P_parm_name => 'GMD_AUTO_PROD_CALC',
353 P_parm_value => l_auto_calc,
354 X_return_status => X_return_status );
355
356 OPEN C_get_auto_parameter (l_formula_id);
357 FETCH C_get_auto_parameter INTO l_formula_calc_flag ;
358 CLOSE C_get_auto_parameter;
359
360 IF l_auto_calc = 'Y' THEN
361 IF l_formula_calc_flag = 'Y' AND p_formula_detail_rec.line_type = 1
362 AND p_formula_detail_rec.scale_type_dtl =1 AND p_formula_detail_rec.prod_percent IS NULL THEN
363 /* Error to be raised for Proportional Products when Percentages are not passed */
364 FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_YES');
365 FND_MSG_PUB.Add;
366 RAISE FND_API.G_EXC_ERROR;
367 ELSIF l_formula_calc_flag = 'Y' AND (p_formula_detail_rec.prod_percent IS NOT NULL )
368 AND ( p_formula_detail_rec.line_type IN (-1,2) OR
369 ( p_formula_detail_rec.line_type = 1 AND p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
370 /* Error to be raised when Percentages are passed for Ingredients/By-Products or Fixed
371 Products */
372 FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_CANNOT');
373 FND_MSG_PUB.Add;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376 END IF;
377
378 IF ( ( l_auto_calc IS NULL OR l_auto_calc = 'N') OR (l_formula_calc_flag IS NULL OR l_formula_calc_flag = 'N' ))
379 AND p_formula_detail_rec.prod_percent IS NOT NULL THEN
380 /* Error to be raised when Percentages are passed when Parameter is not Set tp
381 Calculate Product qty */
382 FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_NO');
383 FND_MSG_PUB.Add;
384 RAISE FND_API.G_EXC_ERROR;
385 END IF;
386 -- Kapil ME Auto-Prod :Bug#5716318
387
388 /* Assigning values to formula detail rec and passing to the private API */
389 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
390 l_fm_matl_dtl_rec.formulaline_id := l_surrogate;
391 l_fm_matl_dtl_rec.formula_id := l_formula_id;
392 l_fm_matl_dtl_rec.line_type := p_formula_detail_rec.line_type;
393 l_fm_matl_dtl_rec.line_no := p_formula_detail_rec.line_no;
394 l_fm_matl_dtl_rec.inventory_item_id := p_formula_detail_rec.inventory_item_id;
395 l_fm_matl_dtl_rec.organization_id := p_formula_detail_rec.owner_organization_id;
396 l_fm_matl_dtl_rec.revision := X_formula_detail_rec.revision;
397 l_fm_matl_dtl_rec.qty := p_formula_detail_rec.qty;
398 l_fm_matl_dtl_rec.detail_uom := X_formula_detail_rec.detail_uom;
399 l_fm_matl_dtl_rec.release_type := X_formula_detail_rec.release_type;
400 l_fm_matl_dtl_rec.scrap_factor := p_formula_detail_rec.scrap_factor;
401 l_fm_matl_dtl_rec.scale_type := p_formula_detail_rec.scale_type_dtl;
402 l_fm_matl_dtl_rec.cost_alloc := X_formula_detail_rec.cost_alloc;
403 l_fm_matl_dtl_rec.phantom_type := p_formula_detail_rec.phantom_type;
404 l_fm_matl_dtl_rec.buffer_ind := p_formula_detail_rec.buffer_ind;
405 l_fm_matl_dtl_rec.rework_type := 0;
406 l_fm_matl_dtl_rec.tpformula_id := p_formula_detail_rec.tpformula_id;
407 l_fm_matl_dtl_rec.iaformula_id := p_formula_detail_rec.iaformula_id;
408 l_fm_matl_dtl_rec.scale_multiple := p_formula_detail_rec.scale_multiple;
409 l_fm_matl_dtl_rec.contribute_yield_ind := p_formula_detail_rec.contribute_yield_ind;
410 l_fm_matl_dtl_rec.scale_uom := p_formula_detail_rec.scale_uom;
411 l_fm_matl_dtl_rec.contribute_step_qty_ind := p_formula_detail_rec.contribute_step_qty_ind;
412 l_fm_matl_dtl_rec.scale_rounding_variance := p_formula_detail_rec.scale_rounding_variance;
413 l_fm_matl_dtl_rec.rounding_direction := p_formula_detail_rec.rounding_direction;
414 /*Bug 2509076 - Thomas Daniel QM Integration new field */
415 l_fm_matl_dtl_rec.by_product_type := X_formula_detail_rec.by_product_type;
416 l_fm_matl_dtl_rec.ingredient_end_date := p_formula_detail_rec.ingredient_end_date; --Bug 4479101
417 l_fm_matl_dtl_rec.text_code := p_formula_detail_rec.text_code_dtl;
418 l_fm_matl_dtl_rec.created_by := l_user_id; -- Bug 4603060
419 l_fm_matl_dtl_rec.creation_date := NVL(p_formula_detail_rec.creation_date, SYSDATE);
420 l_fm_matl_dtl_rec.last_update_date := NVL(p_formula_detail_rec.last_update_date, SYSDATE);
421 l_fm_matl_dtl_rec.last_update_login := NVL(p_formula_detail_rec.last_update_login, l_user_id);-- Bug No.6672176 l_user_id; -- Bug 4603060
422 l_fm_matl_dtl_rec.last_updated_by := l_user_id; -- Bug 4603060
423 /*Bug 3837470 - Thomas Daniel */
424 /*Changed the following assignment from attribute_category to dtl_attribute_category*/
425 l_fm_matl_dtl_rec.attribute_category := p_formula_detail_rec.dtl_attribute_category;
426 l_fm_matl_dtl_rec.attribute1 := p_formula_detail_rec.dtl_attribute1;
427 l_fm_matl_dtl_rec.attribute2 := p_formula_detail_rec.dtl_attribute2;
428 l_fm_matl_dtl_rec.attribute3 := p_formula_detail_rec.dtl_attribute3;
429 l_fm_matl_dtl_rec.attribute4 := p_formula_detail_rec.dtl_attribute4;
430 l_fm_matl_dtl_rec.attribute5 := p_formula_detail_rec.dtl_attribute5;
431 l_fm_matl_dtl_rec.attribute6 := p_formula_detail_rec.dtl_attribute6;
432 l_fm_matl_dtl_rec.attribute7 := p_formula_detail_rec.dtl_attribute7;
433 l_fm_matl_dtl_rec.attribute8 := p_formula_detail_rec.dtl_attribute8;
434 l_fm_matl_dtl_rec.attribute9 := p_formula_detail_rec.dtl_attribute9;
435 l_fm_matl_dtl_rec.attribute10 := p_formula_detail_rec.dtl_attribute10;
436 l_fm_matl_dtl_rec.attribute11 := p_formula_detail_rec.dtl_attribute11;
437 l_fm_matl_dtl_rec.attribute12 := p_formula_detail_rec.dtl_attribute12;
438 l_fm_matl_dtl_rec.attribute13 := p_formula_detail_rec.dtl_attribute13;
439 l_fm_matl_dtl_rec.attribute14 := p_formula_detail_rec.dtl_attribute14;
440 l_fm_matl_dtl_rec.attribute15 := p_formula_detail_rec.dtl_attribute15;
441 l_fm_matl_dtl_rec.attribute16 := p_formula_detail_rec.dtl_attribute16;
442 l_fm_matl_dtl_rec.attribute17 := p_formula_detail_rec.dtl_attribute17;
443 l_fm_matl_dtl_rec.attribute18 := p_formula_detail_rec.dtl_attribute18;
444 l_fm_matl_dtl_rec.attribute19 := p_formula_detail_rec.dtl_attribute19;
445 l_fm_matl_dtl_rec.attribute20 := p_formula_detail_rec.dtl_attribute20;
446 l_fm_matl_dtl_rec.attribute21 := p_formula_detail_rec.dtl_attribute21;
447 l_fm_matl_dtl_rec.attribute22 := p_formula_detail_rec.dtl_attribute22;
448 l_fm_matl_dtl_rec.attribute23 := p_formula_detail_rec.dtl_attribute23;
449 l_fm_matl_dtl_rec.attribute24 := p_formula_detail_rec.dtl_attribute24;
450 l_fm_matl_dtl_rec.attribute25 := p_formula_detail_rec.dtl_attribute25;
451 l_fm_matl_dtl_rec.attribute26 := p_formula_detail_rec.dtl_attribute26;
452 l_fm_matl_dtl_rec.attribute27 := p_formula_detail_rec.dtl_attribute27;
453 l_fm_matl_dtl_rec.attribute28 := p_formula_detail_rec.dtl_attribute28;
454 l_fm_matl_dtl_rec.attribute29 := p_formula_detail_rec.dtl_attribute29;
455 l_fm_matl_dtl_rec.attribute30 := p_formula_detail_rec.dtl_attribute30;
456 -- Kapil ME Auto-Prod :Bug#5716318
457 l_fm_matl_dtl_rec.prod_percent := p_formula_detail_rec.prod_percent;
458 /* Call the private API */
459 IF (l_debug = 'Y') THEN
460 gmd_debug.put_line(' In Formula Detail Pub - '
461 ||' About to call the line Pvt API '
462 ||' - '
463 ||x_return_status);
464 END IF;
465 GMD_FORMULA_DETAIL_PVT.Insert_FormulaDetail
466 ( p_api_version => p_api_version
467 ,p_init_msg_list => p_init_msg_list
468 ,p_commit => FND_API.G_FALSE
469 ,x_return_status => x_return_status
470 ,x_msg_count => x_msg_count
471 ,x_msg_data => x_msg_data
472 ,p_formula_detail_rec => l_fm_matl_dtl_rec
473 );
474
475 IF (l_debug = 'Y') THEN
476 gmd_debug.put_line(' In Formula Detail Pub - '
477 ||' After calling the line Pvt API '
478 ||' - '
479 ||x_return_status);
480 END IF;
481
482 END IF; -- if x_return_status = 'S'
483
484 -- Kapil ME Auto-Prod :Bug#5716318
485 /* Product Qty Calculation after Inserting a Record */
486 IF l_formula_calc_flag = 'Y' THEN
487 GMD_COMMON_VAL.Calculate_Total_Product_Qty( p_formula_id =>l_formula_id ,
488 x_return_status => x_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data);
491 END IF;
492
493 /* IF creation of a line fails - Raise an exception
494 rather than trying to insert other lines */
495 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
496 RAISE FND_API.G_EXC_ERROR;
497 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500
501 END LOOP; -- for number of lines to be inserted
502
503 /* End of API body */
504
505 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
506 (FND_API.To_Boolean(p_commit)) THEN
507 /* Check if p_commit is set to TRUE */
508 Commit;
509 END IF;
510
511 /* Get the message count and information */
512 FND_MSG_PUB.Count_And_Get (
513 p_count => x_msg_count,
514 p_data => x_msg_data );
515
516 EXCEPTION
517 WHEN FND_API.G_EXC_ERROR THEN
518 ROLLBACK to Insert_FormulaDetail;
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 FND_MSG_PUB.Count_And_Get (
521 p_count => x_msg_count,
522 p_data => x_msg_data );
523 IF (l_debug = 'Y') THEN
524 gmd_debug.put_line(' In Formula Detail Pub - In Error Exception Section '
525 ||' - '
526 ||x_return_status);
527 END IF;
528
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530 ROLLBACK to Insert_FormulaDetail;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 FND_MSG_PUB.Count_And_Get (
533 p_count => x_msg_count,
534 p_data => x_msg_data );
535 IF (l_debug = 'Y') THEN
536 gmd_debug.put_line(' In Formula Detail Pub - In Unexpected Exception Section '
537 ||' - '
538 ||x_return_status);
539 END IF;
540
541 WHEN OTHERS THEN
542 ROLLBACK to Insert_FormulaDetail;
543 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 FND_MSG_PUB.Count_And_Get (
546 p_count => x_msg_count,
547 p_data => x_msg_data );
548 IF (l_debug = 'Y') THEN
549 gmd_debug.put_line(' In Formula Detail Pub - In OTHERS Exception Section '
550 ||' - '
551 ||x_return_status);
552 END IF;
553
554 END Insert_FormulaDetail;
555
556
557 /* ======================================================================== */
558 /* Procedure: */
559 /* Update_FormulaDetail */
560 /* */
561 /* DESCRIPTION: */
562 /* This PL/SQL procedure is responsible for updating a formula. */
563 /* details. */
564 /* HISTORY: */
565 /* 10-Apr-2003 P.Raghu Bug#2893682 Modified the code such that */
566 /* p_formula_detail_rec.item_no is correctly set */
567 /* to ITEM_NO TOKEN. */
568 /* 07-MAR-2006 Kapil M Bug#4603056 Added the check for update of revision*/
569 /* of non-revision controlled item */
570 /* ======================================================================== */
571 PROCEDURE Update_FormulaDetail
572 ( p_api_version IN NUMBER
573 ,p_init_msg_list IN VARCHAR2
574 ,p_commit IN VARCHAR2
575 ,p_called_from_forms IN VARCHAR2 := 'NO'
576 ,x_return_status OUT NOCOPY VARCHAR2
577 ,x_msg_count OUT NOCOPY NUMBER
578 ,x_msg_data OUT NOCOPY VARCHAR2
579 ,p_formula_detail_tbl IN formula_update_dtl_tbl_type
580 )
581 IS
582 /* Local Variables definitions */
583 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULADETAIL';
584 l_api_version CONSTANT NUMBER := 2.0;
585 l_user_id fnd_user.user_id%TYPE := 0;
586 l_return_val NUMBER := 0;
587 l_item_id mtl_system_items.inventory_item_id%TYPE := 0;
588 l_inv_uom mtl_system_items.primary_uom_code%TYPE := NULL;
589 l_formula_id fm_matl_dtl.formula_id%TYPE := 0;
590 l_fm_matl_dtl_rec fm_matl_dtl%ROWTYPE;
591 p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
592 X_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
593
594 l_by_product_type fm_matl_dtl.by_product_type%TYPE;
595
596 l_cost_alloc fm_matl_dtl.cost_alloc%TYPE;
597 l_text_code fm_matl_dtl.text_code%TYPE;
598 l_tpformula_id fm_matl_dtl.tpformula_id%TYPE;
599 l_iaformula_id fm_matl_dtl.iaformula_id%TYPE;
600 l_scale_multiple fm_matl_dtl.scale_multiple%TYPE;
601 l_contribute_yield_ind fm_matl_dtl.contribute_yield_ind%TYPE;
602 l_scale_uom fm_matl_dtl.scale_uom%TYPE;
603 l_contribute_step_qty_ind fm_matl_dtl.contribute_step_qty_ind%TYPE;
604 l_scale_rounding_variance fm_matl_dtl.scale_rounding_variance%TYPE;
605 l_rounding_direction fm_matl_dtl.rounding_direction%TYPE;
606 l_ingredient_end_date fm_matl_dtl.ingredient_end_date%TYPE; --bug 4479101
607 l_attribute_category fm_matl_dtl.attribute_category%TYPE;
608
609 l_attribute1 fm_matl_dtl.attribute1%TYPE;
610 l_attribute2 fm_matl_dtl.attribute2%TYPE;
611 l_attribute3 fm_matl_dtl.attribute3%TYPE;
612 l_attribute4 fm_matl_dtl.attribute4%TYPE;
613 l_attribute5 fm_matl_dtl.attribute5%TYPE;
614 l_attribute6 fm_matl_dtl.attribute6%TYPE;
615 l_attribute7 fm_matl_dtl.attribute7%TYPE;
616 l_attribute8 fm_matl_dtl.attribute8%TYPE;
617 l_attribute9 fm_matl_dtl.attribute9%TYPE;
618 l_attribute10 fm_matl_dtl.attribute10%TYPE;
619 l_attribute11 fm_matl_dtl.attribute11%TYPE;
620 l_attribute12 fm_matl_dtl.attribute12%TYPE;
621 l_attribute13 fm_matl_dtl.attribute13%TYPE;
622 l_attribute14 fm_matl_dtl.attribute14%TYPE;
623 l_attribute15 fm_matl_dtl.attribute15%TYPE;
624 l_attribute16 fm_matl_dtl.attribute16%TYPE;
625 l_attribute17 fm_matl_dtl.attribute17%TYPE;
626 l_attribute18 fm_matl_dtl.attribute18%TYPE;
627 l_attribute19 fm_matl_dtl.attribute19%TYPE;
628 l_attribute20 fm_matl_dtl.attribute20%TYPE;
629 l_attribute21 fm_matl_dtl.attribute21%TYPE;
630 l_attribute22 fm_matl_dtl.attribute22%TYPE;
631 l_attribute23 fm_matl_dtl.attribute23%TYPE;
632 l_attribute24 fm_matl_dtl.attribute24%TYPE;
633 l_attribute25 fm_matl_dtl.attribute25%TYPE;
634 l_attribute26 fm_matl_dtl.attribute26%TYPE;
635 l_attribute27 fm_matl_dtl.attribute27%TYPE;
636 l_attribute28 fm_matl_dtl.attribute28%TYPE;
637 l_attribute29 fm_matl_dtl.attribute29%TYPE;
638 l_attribute30 fm_matl_dtl.attribute30%TYPE;
639
640 fm_matl_dtl_rec fm_matl_dtl%ROWTYPE;
641
642 l_to_uom varchar2(4);
643
644 /* Define cursor */
645 CURSOR get_detail_rec(vFormulaline_id NUMBER) IS
646 SELECT * from fm_matl_dtl
647 WHERE formulaline_id = vFormulaline_id;
648
649 CURSOR C_get_orgid (V_formula_id NUMBER) IS
650 SELECT owner_organization_id
651 FROM fm_form_mst_b
652 WHERE formula_id = V_formula_id;
653 l_org_id NUMBER;
654
655 CURSOR C_get_item_id (V_formulaline_id NUMBER) IS
656 SELECT inventory_item_id
657 FROM fm_matl_dtl
658 WHERE formulaline_id = V_formulaline_id;
659
660 CURSOR C_get_item_no (V_item_id NUMBER) IS
661 SELECT concatenated_segments
662 FROM mtl_system_items_kfv
663 WHERE inventory_item_id = V_item_id;
664 l_item_no VARCHAR2(2000);
665
666 -- Kapil ME Auto-Prod :Bug#5716318
667 l_auto_calc VARCHAR2(1);
668 l_formula_calc_flag VARCHAR2(1);
669
670 CURSOR C_get_auto_parameter (V_formula_id NUMBER) IS
671 SELECT AUTO_PRODUCT_CALC
672 FROM FM_FORM_MST_B
673 WHERE FORMULA_ID = V_formula_id;
674
675 BEGIN
676 /* Define Savepoint */
677 SAVEPOINT Update_FormulaDetail;
678
679 /* Standard Check for API compatibility */
680 IF NOT FND_API.Compatible_API_Call ( l_api_version
681 ,p_api_version
682 ,l_api_name
683 ,G_PKG_NAME )
684 THEN
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 END IF;
687
688 /* Set the formula validation pkg variable GMDFMVAL_PUB */
689 /* variable p_called_from_form same as that passed in. */
690 /* When API is called from forms the parameter p_called_from_forms is set
691 to 'YES' and the same parameter is set to 'YES' within the validation pkg.
692 When API is not called from forms the parameter is 'NO'.
693 */
694
695 GMDFMVAL_PUB.p_called_from_forms := p_called_from_forms;
696
697 /* Initialize message list if p_init_msg_list is set to TRUE */
698 IF FND_API.to_Boolean( p_init_msg_list ) THEN
699 FND_MSG_PUB.initialize;
700 END IF;
701
702 /* 1. Does validation */
703 /* 2. Call the private API that does the database updates */
704 IF (p_formula_detail_tbl.count = 0) THEN
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 FOR i IN 1 .. p_formula_detail_tbl.count LOOP
709
710 /* Initialize API return status to success */
711 x_return_status := FND_API.G_RET_STS_SUCCESS;
712
713 IF (l_debug = 'Y') THEN
714 gmd_debug.put_line(' In Formula Detail Update Pub - Entering loop with row # '||i);
715 END IF;
716
717 p_formula_detail_rec := p_formula_detail_tbl(i);
718
719 /* New record to get different entity values */
720 pRecord_in.formula_no := p_formula_detail_rec.formula_no;
721 pRecord_in.formula_vers := p_formula_detail_rec.formula_vers;
722 pRecord_in.formula_id := p_formula_detail_rec.formula_id;
723 -- Bug 4603060 pRecord_in.user_name := p_formula_detail_rec.user_name;
724
725 /* Procedure get_element based on the element_name return all
726 information about it. For e.g. if element_name is formula
727 and if we input the formula_id in pRecord_in it returns the
728 formula_no and vers information and visa versa too
729 */
730 /* ======================== */
731 /* Get the formula id */
732 /* ======================== */
733 IF (l_debug = 'Y') THEN
734 gmd_debug.put_line(' In Formula Detail Pub - '
735 ||' Before formula validation - '||x_return_status);
736 END IF;
737 IF (p_formula_detail_rec.formula_id is NULL) THEN
738 GMDFMVAL_PUB.get_formula_id(p_formula_detail_rec.formula_no,
739 p_formula_detail_rec.formula_vers,
740 l_formula_id, l_return_val);
741 IF (l_return_val <> 0) THEN
742 IF (p_formula_detail_rec.formula_no IS NULL) THEN
743 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
744 FND_MSG_PUB.Add;
745 RAISE FND_API.G_EXC_ERROR;
746 ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
747 FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
748 FND_MSG_PUB.Add;
749 RAISE FND_API.G_EXC_ERROR;
750 ELSE
751 FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
752 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
753 FND_MSG_PUB.Add;
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756 END IF;
757 ELSE
758 l_formula_id := p_formula_detail_rec.formula_id;
759 END IF;
760
761 /* New - added this condition below by Shyam */
762 /* Check if this formula can be changed - if this formula is
763 On-Hold or Obsolete or Frozen or Requested for Approval -
764 the change of this formula is prevented */
765 /* Check if update is allowed */
766 IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
767 FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
768 FND_MESSAGE.SET_TOKEN('NAME', 'formula');
769 FND_MESSAGE.SET_TOKEN('ID', l_formula_id);
770 FND_MESSAGE.SET_TOKEN('NO', p_formula_detail_rec.formula_no);
771 FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
772 FND_MESSAGE.SET_TOKEN('STATUS',
773 GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
774 FND_MSG_PUB.Add;
775 RAISE FND_API.G_EXC_ERROR;
776 END IF;
777
778 /* Check if there is a valid userid */
779 IF (l_debug = 'Y') THEN
780 gmd_debug.put_line(' In Formula Detail Pub - '
781 ||' - Before user validation ');
782 END IF;
783 -- Bug 4603060 User the user_id from context
784 l_user_id := FND_GLOBAL.user_id;
785 IF (l_user_id IS NULL) THEN
786 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
787 FND_MSG_PUB.Add;
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790
791 /* ========================================= */
792 /* Ensure that the formulaline id exists */
793 /* User is forced to pass the formulaline_id */
794 /* ========================================== */
795 IF (l_debug = 'Y') THEN
796 gmd_debug.put_line(' In Formula Detail Pub - '
797 ||' Before formulaline validation - '||x_return_status);
798 END IF;
799 IF (p_formula_detail_rec.formulaline_id IS NOT NULL) THEN /* if invalid formula no */
800 GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
801 IF (l_return_val <> 0) THEN
802 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_FMLINE_ID');
803 FND_MSG_PUB.Add;
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN /* missing formula no */
807 FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
808 FND_MSG_PUB.Add;
809 RAISE FND_API.G_EXC_ERROR;
810 END IF;
811
812 -- Bug# 5554631 KMOTUPAL - TO prevent Update of Item Revision
813 IF (p_formula_detail_rec.revision IS NOT NULL) THEN
814 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_COL_UPDATES');
815 FND_MESSAGE.SET_TOKEN('NAME','REVISION');
816 FND_MSG_PUB.Add;
817 END IF;
818
819 OPEN C_get_orgid (l_formula_id);
820 FETCH C_get_orgid INTO l_org_id;
821 CLOSE C_get_orgid;
822
823 p_formula_detail_rec.owner_organization_id := l_org_id;
824
825
826 IF (p_formula_detail_rec.inventory_item_id is NULL) THEN --ADD if condition in bug14710339
827
828 OPEN C_get_item_id(p_formula_detail_rec.formulaline_id);
829 FETCH C_get_item_id INTO l_item_id;
830 CLOSE C_get_item_id;
831
832 p_formula_detail_rec.inventory_item_id := l_item_id;
833
834
835 END IF;--ADD if condition in bug14710339
836
837
838
839 OPEN C_get_item_no(l_item_id);
840 FETCH C_get_item_no INTO l_item_no;
841 CLOSE C_get_item_no;
842
843 p_formula_detail_rec.item_no := l_item_no;
844
845
846 /* ================================================================= */
847 /* Get all not null values from the from the formula line table */
848 /* (fm_matl_dtl). If any field value is not provided, update it */
849 /* with what exists in the db */
850 /* ================================================================= */
851 IF (l_debug = 'Y') THEN
852 gmd_debug.put_line(' In Formula Detail Pub - '
853 ||' Retrieving all not null columns '
854 ||' for formula line id = '
855 ||p_formula_detail_rec.formulaline_id
856 ||' - '
857 ||x_return_status);
858 END IF;
859
860 FOR fmline_not_null IN get_detail_rec(p_formula_detail_rec.formulaline_id)
861 LOOP
862 IF (p_formula_detail_rec.line_type IS NULL) THEN
863 p_formula_detail_rec.line_type := fmline_not_null.line_type;
864 END IF;
865
866 IF (p_formula_detail_rec.line_no IS NULL) THEN
867 p_formula_detail_rec.line_no := fmline_not_null.line_no;
868 END IF;
869
870 IF (p_formula_detail_rec.qty IS NULL) THEN
871 p_formula_detail_rec.qty := fmline_not_null.qty;
872 END IF;
873
874 -- Bug# 5554631 KMOTUPAL - To pass the old revision as update of revision is not allowed.
875 -- IF (p_formula_detail_rec.revision IS NULL) THEN
876 p_formula_detail_rec.revision := fmline_not_null.revision;
877 -- END IF;
878
879
880 IF (p_formula_detail_rec.detail_uom IS NULL) THEN
881 p_formula_detail_rec.detail_uom := fmline_not_null.detail_uom;
882 END IF;
883
884 IF (p_formula_detail_rec.release_type IS NULL) THEN
885 p_formula_detail_rec.release_type := fmline_not_null.release_type;
886 END IF;
887
888 IF (p_formula_detail_rec.scrap_factor IS NULL) THEN
889 p_formula_detail_rec.scrap_factor := fmline_not_null.scrap_factor;
890 END IF;
891
892 IF (p_formula_detail_rec.scale_type_dtl IS NULL) THEN
893 p_formula_detail_rec.scale_type_dtl := fmline_not_null.scale_type;
894 END IF;
895
896 IF (p_formula_detail_rec.phantom_type IS NULL) THEN
897 p_formula_detail_rec.phantom_type := fmline_not_null.phantom_type;
898 END IF;
899
900 IF (p_formula_detail_rec.buffer_ind IS NULL) THEN
901 p_formula_detail_rec.buffer_ind := fmline_not_null.buffer_ind;
902 END IF;
903
904 IF (p_formula_detail_rec.rework_type IS NULL) THEN
905 p_formula_detail_rec.rework_type := fmline_not_null.rework_type;
906 END IF;
907
908 -- Bug 4603060
909 p_formula_detail_rec.last_updated_by := l_user_id;
910
911
912 IF (p_formula_detail_rec.created_by IS NULL) THEN
913 p_formula_detail_rec.created_by := fmline_not_null.created_by;
914 END IF;
915
916 IF (p_formula_detail_rec.last_update_date IS NULL) THEN
917 p_formula_detail_rec.last_update_date := SYSDATE;
918 END IF;
919
920 IF (p_formula_detail_rec.creation_date IS NULL) THEN
921 p_formula_detail_rec.creation_date := fmline_not_null.creation_date;
922 END IF;
923
924 IF (p_formula_detail_rec.last_update_login IS NULL) THEN
925 p_formula_detail_rec.last_update_login := fmline_not_null.last_update_login;
926 END IF;
927
928 -- Kapil ME Auto-Prod :Bug#5716318
929 /* Get the Organization Parameter and the Parameter set at the Formula level */
930 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_org_id,
931 P_parm_name => 'GMD_AUTO_PROD_CALC',
932 P_parm_value => l_auto_calc,
933 X_return_status => X_return_status );
934
935 OPEN C_get_auto_parameter (l_formula_id);
936 FETCH C_get_auto_parameter INTO l_formula_calc_flag ;
937 CLOSE C_get_auto_parameter;
938
939 /* Get the Percentage value if Not passed */
940 IF (p_formula_detail_rec.prod_percent IS NULL) THEN
941 p_formula_detail_rec.prod_percent := fmline_not_null.prod_percent;
942 ELSE
943 IF l_auto_calc IS NULL OR l_auto_calc = 'N' OR l_formula_calc_flag = 'N' OR l_formula_calc_flag IS NULL
944 OR (fmline_not_null.line_type IN (-1,2) OR
945 (p_formula_detail_rec.line_type = 1 And p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
946 /* Error to be raised if Percentages are passed for Ingredients/By-Products or when
947 when the Parameter is not set to calculate Product Qty */
948 FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGES_NOT');
949 FND_MSG_PUB.Add;
950 RAISE FND_API.G_EXC_ERROR;
951 END IF;
952 END IF;
953 -- Kapil ME Auto-Prod :Bug#5716318
954
955 END LOOP; -- end loop for all not column assignment
956
957
958 /* Beyond this all validations are made */
959 /* When coming from forms all these validations are already */
960 /* done, so we can skip the validations below. */
961
962 /* Procedure validate_formula_record for all the elements*/
963
964 IF (NVL(p_called_from_forms,'NO') = 'NO') THEN
965 GMDFMVAL_PUB.validate_update_record (P_formula_dtl => P_formula_detail_rec,
966 X_formula_dtl => X_formula_detail_rec,
967 xReturn => X_return_status);
968 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
969 RAISE FND_API.G_EXC_ERROR;
970 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973 ELSE
974 X_formula_detail_rec := P_formula_detail_rec;
975 END IF; /* Validations end when not called from forms */
976
977 /* Validate all optional parameters passed. */
978
979 IF (l_debug = 'Y') THEN
980 gmd_debug.put_line(' In Formula Detail Pub - '
981 ||' Validation of G-MISS '
982 ||' - '
983 ||x_return_status);
984 END IF;
985
986 OPEN get_detail_rec(p_formula_detail_rec.formulaline_id);
987 FETCH get_detail_rec into fm_matl_dtl_rec;
988
989 /* Shyam Sitaraman - Bug 2652200 */
990 /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
991 /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
992 /* as the user is intending to update the field to NULL */
993 IF (get_detail_rec%FOUND) THEN
994 /*Bug 2509076 - Thomas Daniel */
995 /* QM Integration */
996 IF (p_formula_detail_rec.by_product_type = FND_API.G_MISS_CHAR) THEN
997 l_by_product_type := NULL;
998 ELSIF (p_formula_detail_rec.by_product_type IS NULL) THEN
999 l_by_product_type := fm_matl_dtl_rec.by_product_type;
1000 ELSE
1001 l_by_product_type := X_formula_detail_rec.by_product_type;
1002 END IF;
1003
1004 /* Added some more - with FM API cleanup */
1005 IF (p_formula_detail_rec.rounding_direction = FND_API.G_MISS_NUM) THEN
1006 l_rounding_direction := NULL;
1007 ELSIF (p_formula_detail_rec.rounding_direction IS NULL) THEN
1008 l_rounding_direction := fm_matl_dtl_rec.rounding_direction;
1009 ELSE
1010 l_rounding_direction := p_formula_detail_rec.rounding_direction;
1011 END IF;
1012
1013 IF (p_formula_detail_rec.text_code_dtl = FND_API.G_MISS_NUM) THEN
1014 l_text_code := NULL;
1015 ELSIF (p_formula_detail_rec.text_code_dtl IS NULL) THEN
1016 l_text_code := fm_matl_dtl_rec.text_code;
1017 ELSE
1018 l_text_code := p_formula_detail_rec.text_code_dtl;
1019 END IF;
1020
1021 IF (p_formula_detail_rec.cost_alloc = FND_API.G_MISS_NUM) THEN
1022 l_cost_alloc := NULL;
1023 ELSIF (p_formula_detail_rec.cost_alloc IS NULL) THEN
1024 l_cost_alloc := fm_matl_dtl_rec.cost_alloc;
1025 ELSE
1026 l_cost_alloc := X_formula_detail_rec.cost_alloc;
1027 END IF;
1028
1029 IF (p_formula_detail_rec.tpformula_id = FND_API.G_MISS_NUM) THEN
1030 l_tpformula_id := NULL;
1031 ELSIF (p_formula_detail_rec.tpformula_id IS NULL) THEN
1032 l_tpformula_id := fm_matl_dtl_rec.tpformula_id;
1033 ELSE
1034 l_tpformula_id := p_formula_detail_rec.tpformula_id;
1035 END IF;
1036
1037 IF (p_formula_detail_rec.tpformula_id = FND_API.G_MISS_NUM) THEN
1038 l_tpformula_id := NULL;
1039 ELSIF (p_formula_detail_rec.tpformula_id IS NULL) THEN
1040 l_tpformula_id := fm_matl_dtl_rec.tpformula_id;
1041 ELSE
1042 l_tpformula_id := p_formula_detail_rec.tpformula_id;
1043 END IF;
1044
1045 IF (p_formula_detail_rec.iaformula_id = FND_API.G_MISS_NUM) THEN
1046 l_iaformula_id := NULL;
1047 ELSIF (p_formula_detail_rec.iaformula_id IS NULL) THEN
1048 l_iaformula_id := fm_matl_dtl_rec.iaformula_id;
1049 ELSE
1050 l_iaformula_id := p_formula_detail_rec.iaformula_id;
1051 END IF;
1052
1053 IF (p_formula_detail_rec.scale_multiple = FND_API.G_MISS_NUM) THEN
1054 l_scale_multiple := NULL;
1055 ELSIF (p_formula_detail_rec.scale_multiple IS NULL) THEN
1056 l_scale_multiple := fm_matl_dtl_rec.scale_multiple;
1057 ELSE
1058 l_scale_multiple := p_formula_detail_rec.scale_multiple;
1059 END IF;
1060
1061 IF (p_formula_detail_rec.scale_rounding_variance = FND_API.G_MISS_NUM) THEN
1062 l_scale_rounding_variance := NULL;
1063 ELSIF (p_formula_detail_rec.scale_rounding_variance IS NULL) THEN
1064 l_scale_rounding_variance := fm_matl_dtl_rec.scale_rounding_variance;
1065 ELSE
1066 l_scale_rounding_variance := p_formula_detail_rec.scale_rounding_variance;
1067 END IF;
1068
1069 IF (p_formula_detail_rec.contribute_yield_ind = FND_API.G_MISS_CHAR) THEN
1070 l_contribute_yield_ind := NULL;
1071 IF (l_debug = 'Y') THEN
1072 gmd_debug.put_line(' In Formula Header Pub - '
1073 ||' Cond 1');
1074 END IF;
1075 ELSIF (p_formula_detail_rec.contribute_yield_ind IS NULL) THEN
1076 l_contribute_yield_ind := fm_matl_dtl_rec.contribute_yield_ind;
1077 IF (l_debug = 'Y') THEN
1078 gmd_debug.put_line(' In Formula Header Pub - '
1079 ||' Cond 2');
1080 END IF;
1081 ELSE
1082 l_contribute_yield_ind := p_formula_detail_rec.contribute_yield_ind;
1083 IF (l_debug = 'Y') THEN
1084 gmd_debug.put_line(' In Formula Header Pub - '
1085 ||' Cond 3');
1086 END IF;
1087 END IF;
1088
1089 IF (l_debug = 'Y') THEN
1090 gmd_debug.put_line(' In Formula Header Pub - '
1091 ||' p_formula_detail_rec.contribute_yield_ind = '
1092 ||p_formula_detail_rec.contribute_yield_ind
1093 ||' fm_matl_dtl_rec.contribute_yield_ind = '
1094 ||fm_matl_dtl_rec.contribute_yield_ind
1095 ||' l_contribute_yield_ind = '
1096 ||l_contribute_yield_ind
1097 ||' - '
1098 ||x_return_status);
1099 END IF;
1100
1101 IF (p_formula_detail_rec.scale_uom = FND_API.G_MISS_CHAR) THEN
1102 l_scale_uom := NULL;
1103 ELSIF (p_formula_detail_rec.scale_uom IS NULL) THEN
1104 l_scale_uom := fm_matl_dtl_rec.scale_uom;
1105 ELSE
1106 l_scale_uom := p_formula_detail_rec.scale_uom;
1107 END IF;
1108
1109 IF (p_formula_detail_rec.contribute_step_qty_ind = FND_API.G_MISS_CHAR) THEN
1110 l_contribute_step_qty_ind := NULL;
1111 ELSIF (p_formula_detail_rec.contribute_step_qty_ind IS NULL) THEN
1112 l_contribute_step_qty_ind := fm_matl_dtl_rec.contribute_step_qty_ind;
1113 ELSE
1114 l_contribute_step_qty_ind := p_formula_detail_rec.contribute_step_qty_ind;
1115 END IF;
1116
1117
1118 IF (p_formula_detail_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1119 l_attribute_category := NULL;
1120 ELSIF (p_formula_detail_rec.attribute_category IS NULL) THEN
1121 l_attribute_category := fm_matl_dtl_rec.attribute_category;
1122 ELSE
1123 l_attribute_category := p_formula_detail_rec.attribute_category;
1124 END IF;
1125
1126 IF (p_formula_detail_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1127 l_attribute1 := NULL;
1128 ELSIF (p_formula_detail_rec.attribute1 IS NULL) THEN
1129 l_attribute1 := fm_matl_dtl_rec.attribute1;
1130 ELSE
1131 l_attribute1 := p_formula_detail_rec.attribute1;
1132 END IF;
1133
1134 IF (p_formula_detail_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1135 l_attribute2 := NULL;
1136 ELSIF (p_formula_detail_rec.attribute2 IS NULL) THEN
1137 l_attribute2 := fm_matl_dtl_rec.attribute2;
1138 ELSE
1139 l_attribute2 := p_formula_detail_rec.attribute2;
1140 END IF;
1141
1142 IF (p_formula_detail_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1143 l_attribute3 := NULL;
1144 ELSIF (p_formula_detail_rec.attribute3 IS NULL) THEN
1145 l_attribute3 := fm_matl_dtl_rec.attribute3;
1146 ELSE
1147 l_attribute3 := p_formula_detail_rec.attribute3;
1148 END IF;
1149
1150 IF (p_formula_detail_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1151 l_attribute4 := NULL;
1152 ELSIF (p_formula_detail_rec.attribute4 IS NULL) THEN
1153 l_attribute4 := fm_matl_dtl_rec.attribute4;
1154 ELSE
1155 l_attribute4 := p_formula_detail_rec.attribute4;
1156 END IF;
1157
1158 IF (p_formula_detail_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1159 l_attribute5 := NULL;
1160 ELSIF (p_formula_detail_rec.attribute5 IS NULL) THEN
1161 l_attribute5 := fm_matl_dtl_rec.attribute5;
1162 ELSE
1163 l_attribute5 := p_formula_detail_rec.attribute5;
1164 END IF;
1165
1166 IF (p_formula_detail_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1167 l_attribute6 := NULL;
1168 ELSIF (p_formula_detail_rec.attribute6 IS NULL) THEN
1169 l_attribute6 := fm_matl_dtl_rec.attribute6;
1170 ELSE
1171 l_attribute6 := p_formula_detail_rec.attribute6;
1172 END IF;
1173
1174 IF (p_formula_detail_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1175 l_attribute7 := NULL;
1176 ELSIF (p_formula_detail_rec.attribute7 IS NULL) THEN
1177 l_attribute7 := fm_matl_dtl_rec.attribute7;
1178 ELSE
1179 l_attribute7 := p_formula_detail_rec.attribute7;
1180 END IF;
1181
1182 IF (p_formula_detail_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1183 l_attribute8 := NULL;
1184 ELSIF (p_formula_detail_rec.attribute8 IS NULL) THEN
1185 l_attribute8 := fm_matl_dtl_rec.attribute8;
1186 ELSE
1187 l_attribute8 := p_formula_detail_rec.attribute8;
1188 END IF;
1189
1190 IF (p_formula_detail_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1191 l_attribute9 := NULL;
1192 ELSIF (p_formula_detail_rec.attribute9 IS NULL) THEN
1193 l_attribute9 := fm_matl_dtl_rec.attribute9;
1194 ELSE
1195 l_attribute9 := p_formula_detail_rec.attribute9;
1196 END IF;
1197
1198 IF (p_formula_detail_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1199 l_attribute10 := NULL;
1200 ELSIF (p_formula_detail_rec.attribute10 IS NULL) THEN
1201 l_attribute10 := fm_matl_dtl_rec.attribute10;
1202 ELSE
1203 l_attribute10 := p_formula_detail_rec.attribute10;
1204 END IF;
1205
1206 IF (p_formula_detail_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1207 l_attribute11 := NULL;
1208 ELSIF (p_formula_detail_rec.attribute11 IS NULL) THEN
1209 l_attribute11 := fm_matl_dtl_rec.attribute11;
1210 ELSE
1211 l_attribute11 := p_formula_detail_rec.attribute11;
1212 END IF;
1213
1214 IF (p_formula_detail_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1215 l_attribute12 := NULL;
1216 ELSIF (p_formula_detail_rec.attribute2 IS NULL) THEN
1217 l_attribute12 := fm_matl_dtl_rec.attribute12;
1218 ELSE
1219 l_attribute12 := p_formula_detail_rec.attribute12;
1220 END IF;
1221
1222 IF (p_formula_detail_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1223 l_attribute13 := NULL;
1224 ELSIF (p_formula_detail_rec.attribute13 IS NULL) THEN
1225 l_attribute13 := fm_matl_dtl_rec.attribute13;
1226 ELSE
1227 l_attribute13 := p_formula_detail_rec.attribute13;
1228 END IF;
1229
1230 IF (p_formula_detail_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1231 l_attribute14 := NULL;
1232 ELSIF (p_formula_detail_rec.attribute14 IS NULL) THEN
1233 l_attribute14 := fm_matl_dtl_rec.attribute14;
1234 ELSE
1235 l_attribute14 := p_formula_detail_rec.attribute14;
1236 END IF;
1237
1238 IF (p_formula_detail_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1239 l_attribute15 := NULL;
1240 ELSIF (p_formula_detail_rec.attribute15 IS NULL) THEN
1241 l_attribute15 := fm_matl_dtl_rec.attribute15;
1242 ELSE
1243 l_attribute15 := p_formula_detail_rec.attribute15;
1244 END IF;
1245
1246 IF (p_formula_detail_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1247 l_attribute16 := NULL;
1248 ELSIF (p_formula_detail_rec.attribute16 IS NULL) THEN
1249 l_attribute16 := fm_matl_dtl_rec.attribute16;
1250 ELSE
1251 l_attribute16 := p_formula_detail_rec.attribute16;
1252 END IF;
1253
1254 IF (p_formula_detail_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1255 l_attribute17 := NULL;
1256 ELSIF (p_formula_detail_rec.attribute17 IS NULL) THEN
1257 l_attribute17 := fm_matl_dtl_rec.attribute17;
1258 ELSE
1259 l_attribute17 := p_formula_detail_rec.attribute17;
1260 END IF;
1261
1262 IF (p_formula_detail_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1263 l_attribute18 := NULL;
1264 ELSIF (p_formula_detail_rec.attribute18 IS NULL) THEN
1265 l_attribute18 := fm_matl_dtl_rec.attribute18;
1266 ELSE
1267 l_attribute18 := p_formula_detail_rec.attribute18;
1268 END IF;
1269
1270 IF (p_formula_detail_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1271 l_attribute19 := NULL;
1272 ELSIF (p_formula_detail_rec.attribute19 IS NULL) THEN
1273 l_attribute19 := fm_matl_dtl_rec.attribute19;
1274 ELSE
1275 l_attribute19 := p_formula_detail_rec.attribute19;
1276 END IF;
1277
1278 IF (p_formula_detail_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1279 l_attribute20 := NULL;
1280 ELSIF (p_formula_detail_rec.attribute20 IS NULL) THEN
1281 l_attribute20 := fm_matl_dtl_rec.attribute20;
1282 ELSE
1283 l_attribute20 := p_formula_detail_rec.attribute20;
1284 END IF;
1285
1286 IF (p_formula_detail_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1287 l_attribute21 := NULL;
1288 ELSIF (p_formula_detail_rec.attribute21 IS NULL) THEN
1289 l_attribute21 := fm_matl_dtl_rec.attribute21;
1290 ELSE
1291 l_attribute21 := p_formula_detail_rec.attribute21;
1292 END IF;
1293
1294 IF (p_formula_detail_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1295 l_attribute22 := NULL;
1296 ELSIF (p_formula_detail_rec.attribute22 IS NULL) THEN
1297 l_attribute22 := fm_matl_dtl_rec.attribute22;
1298 ELSE
1299 l_attribute22 := p_formula_detail_rec.attribute22;
1300 END IF;
1301
1302 IF (p_formula_detail_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1303 l_attribute23 := NULL;
1304 ELSIF (p_formula_detail_rec.attribute23 IS NULL) THEN
1305 l_attribute23 := fm_matl_dtl_rec.attribute23;
1306 ELSE
1307 l_attribute23 := p_formula_detail_rec.attribute23;
1308 END IF;
1309
1310 IF (p_formula_detail_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1311 l_attribute24 := NULL;
1312 ELSIF (p_formula_detail_rec.attribute24 IS NULL) THEN
1313 l_attribute24 := fm_matl_dtl_rec.attribute24;
1314 ELSE
1315 l_attribute24 := p_formula_detail_rec.attribute24;
1316 END IF;
1317
1318 IF (p_formula_detail_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1319 l_attribute25 := NULL;
1320 ELSIF (p_formula_detail_rec.attribute25 IS NULL) THEN
1321 l_attribute25 := fm_matl_dtl_rec.attribute25;
1322 ELSE
1323 l_attribute25 := p_formula_detail_rec.attribute25;
1324 END IF;
1325
1326 IF (p_formula_detail_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1327 l_attribute26 := NULL;
1328 ELSIF (p_formula_detail_rec.attribute26 IS NULL) THEN
1329 l_attribute26 := fm_matl_dtl_rec.attribute26;
1330 ELSE
1331 l_attribute26 := p_formula_detail_rec.attribute26;
1332 END IF;
1333
1334 IF (p_formula_detail_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1335 l_attribute27 := NULL;
1336 ELSIF (p_formula_detail_rec.attribute27 IS NULL) THEN
1337 l_attribute27 := fm_matl_dtl_rec.attribute27;
1338 ELSE
1339 l_attribute27 := p_formula_detail_rec.attribute27;
1340 END IF;
1341
1342 IF (p_formula_detail_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1343 l_attribute28 := NULL;
1344 ELSIF (p_formula_detail_rec.attribute28 IS NULL) THEN
1345 l_attribute28 := fm_matl_dtl_rec.attribute28;
1346 ELSE
1347 l_attribute28 := p_formula_detail_rec.attribute28;
1348 END IF;
1349
1350 IF (p_formula_detail_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1351 l_attribute29 := NULL;
1352 ELSIF (p_formula_detail_rec.attribute29 IS NULL) THEN
1353 l_attribute29 := fm_matl_dtl_rec.attribute29;
1354 ELSE
1355 l_attribute29 := p_formula_detail_rec.attribute29;
1356 END IF;
1357
1358 IF (p_formula_detail_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1359 l_attribute30 := NULL;
1360 ELSIF (p_formula_detail_rec.attribute30 IS NULL) THEN
1361 l_attribute30 := fm_matl_dtl_rec.attribute30;
1362 ELSE
1363 l_attribute30 := p_formula_detail_rec.attribute30;
1364 END IF;
1365 --Bug 4479101
1366 IF (p_formula_detail_rec.ingredient_end_date = FND_API.G_MISS_DATE) THEN
1367 l_ingredient_end_date := NULL;
1368 ELSIF (p_formula_detail_rec.ingredient_end_date IS NULL) THEN
1369 l_ingredient_end_date := fm_matl_dtl_rec.ingredient_end_date;
1370 ELSE
1371 l_ingredient_end_date := p_formula_detail_rec.ingredient_end_date;
1372 END IF;
1373
1374 END IF;
1375
1376 CLOSE get_detail_rec;
1377
1378 /* Call for private API */
1379 IF (l_debug = 'Y') THEN
1380 gmd_debug.put_line(' In Formula Detail Pub - '
1381 ||' Assigning values prior to pvt API call '
1382 ||' - '
1383 ||x_return_status);
1384 END IF;
1385 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1386 l_fm_matl_dtl_rec.formulaline_id := p_formula_detail_rec.formulaline_id;
1387 l_fm_matl_dtl_rec.formula_id := l_formula_id;
1388 l_fm_matl_dtl_rec.line_type := p_formula_detail_rec.line_type;
1389 l_fm_matl_dtl_rec.line_no := p_formula_detail_rec.line_no;
1390 l_fm_matl_dtl_rec.organization_id := p_formula_detail_rec.owner_organization_id;
1391 l_fm_matl_dtl_rec.inventory_item_id := p_formula_detail_rec.inventory_item_id;--add in bug14710339
1392 l_fm_matl_dtl_rec.revision := X_formula_detail_rec.revision; l_fm_matl_dtl_rec.qty := p_formula_detail_rec.qty;
1393 l_fm_matl_dtl_rec.detail_uom := X_formula_detail_rec.detail_uom;
1394 l_fm_matl_dtl_rec.release_type := X_formula_detail_rec.release_type;
1395 l_fm_matl_dtl_rec.scrap_factor := p_formula_detail_rec.scrap_factor;
1396 l_fm_matl_dtl_rec.scale_type := p_formula_detail_rec.scale_type_dtl;
1397 l_fm_matl_dtl_rec.cost_alloc := l_cost_alloc;
1398 l_fm_matl_dtl_rec.phantom_type := p_formula_detail_rec.phantom_type;
1399 l_fm_matl_dtl_rec.buffer_ind := p_formula_detail_rec.buffer_ind;
1400 l_fm_matl_dtl_rec.rework_type := 0;
1401 l_fm_matl_dtl_rec.tpformula_id := l_tpformula_id;
1402 l_fm_matl_dtl_rec.iaformula_id := l_iaformula_id;
1403 l_fm_matl_dtl_rec.scale_multiple := l_scale_multiple;
1404 l_fm_matl_dtl_rec.contribute_yield_ind := l_contribute_yield_ind;
1405 l_fm_matl_dtl_rec.scale_uom := l_scale_uom;
1406 l_fm_matl_dtl_rec.contribute_step_qty_ind := l_contribute_step_qty_ind;
1407 l_fm_matl_dtl_rec.scale_rounding_variance := l_scale_rounding_variance;
1408 l_fm_matl_dtl_rec.rounding_direction := l_rounding_direction;
1409 /*Bug 2509076 - Thomas Daniel QM Integration new field */
1410 l_fm_matl_dtl_rec.by_product_type := l_by_product_type;
1411 l_fm_matl_dtl_rec.ingredient_end_date := l_ingredient_end_date; --Bug 4479101
1412 l_fm_matl_dtl_rec.text_code := l_text_code;
1413 l_fm_matl_dtl_rec.created_by := p_formula_detail_rec.created_by;
1414 l_fm_matl_dtl_rec.creation_date := p_formula_detail_rec.creation_date;
1415 l_fm_matl_dtl_rec.last_update_date := p_formula_detail_rec.last_update_date;
1416 l_fm_matl_dtl_rec.last_update_login := p_formula_detail_rec.last_update_login;
1417 l_fm_matl_dtl_rec.last_updated_by := p_formula_detail_rec.last_updated_by;
1418 l_fm_matl_dtl_rec.attribute_category := l_attribute_category;
1419 l_fm_matl_dtl_rec.attribute1 := l_attribute1;
1420 l_fm_matl_dtl_rec.attribute2 := l_attribute2;
1421 l_fm_matl_dtl_rec.attribute3 := l_attribute3;
1422 l_fm_matl_dtl_rec.attribute4 := l_attribute4;
1423 l_fm_matl_dtl_rec.attribute5 := l_attribute5;
1424 l_fm_matl_dtl_rec.attribute6 := l_attribute6;
1425 l_fm_matl_dtl_rec.attribute7 := l_attribute7;
1426 l_fm_matl_dtl_rec.attribute8 := l_attribute8;
1427 l_fm_matl_dtl_rec.attribute9 := l_attribute9;
1428 l_fm_matl_dtl_rec.attribute10 := l_attribute10;
1429 l_fm_matl_dtl_rec.attribute11 := l_attribute11;
1430 l_fm_matl_dtl_rec.attribute12 := l_attribute12;
1431 l_fm_matl_dtl_rec.attribute13 := l_attribute13;
1432 l_fm_matl_dtl_rec.attribute14 := l_attribute14;
1433 l_fm_matl_dtl_rec.attribute15 := l_attribute15;
1434 l_fm_matl_dtl_rec.attribute16 := l_attribute16;
1435 l_fm_matl_dtl_rec.attribute17 := l_attribute17;
1436 l_fm_matl_dtl_rec.attribute18 := l_attribute18;
1437 l_fm_matl_dtl_rec.attribute19 := l_attribute19;
1438 l_fm_matl_dtl_rec.attribute20 := l_attribute20;
1439 l_fm_matl_dtl_rec.attribute21 := l_attribute21;
1440 l_fm_matl_dtl_rec.attribute22 := l_attribute22;
1441 l_fm_matl_dtl_rec.attribute23 := l_attribute23;
1442 l_fm_matl_dtl_rec.attribute24 := l_attribute24;
1443 l_fm_matl_dtl_rec.attribute25 := l_attribute25;
1444 l_fm_matl_dtl_rec.attribute26 := l_attribute26;
1445 l_fm_matl_dtl_rec.attribute27 := l_attribute27;
1446 l_fm_matl_dtl_rec.attribute28 := l_attribute28;
1447 l_fm_matl_dtl_rec.attribute29 := l_attribute29;
1448 l_fm_matl_dtl_rec.attribute30 := l_attribute30;
1449 -- Kapil ME Auto-Prod :Bug#5716318
1450 l_fm_matl_dtl_rec.prod_percent := p_formula_detail_rec.prod_percent;
1451
1452 /* Call the private API */
1453 IF (l_debug = 'Y') THEN
1454 gmd_debug.put_line(' In Formula Detail Pub - '
1455 ||' Before Updtae Pvt API call '
1456 ||' - '
1457 ||x_return_status);
1458 END IF;
1459 GMD_FORMULA_DETAIL_PVT.Update_FormulaDetail
1460 ( p_api_version => 1.0
1461 ,p_init_msg_list => p_init_msg_list
1462 ,p_commit => FND_API.G_FALSE
1463 ,x_return_status => x_return_status
1464 ,x_msg_count => x_msg_count
1465 ,x_msg_data => x_msg_data
1466 ,p_formula_detail_rec => l_fm_matl_dtl_rec
1467 );
1468
1469 IF (l_debug = 'Y') THEN
1470 gmd_debug.put_line(' In Formula Detail Pub - '
1471 ||' After Update Pvt API call '
1472 ||' - '
1473 ||x_return_status);
1474 END IF;
1475
1476 END IF;
1477
1478 -- Kapil ME Auto-Prod :Bug#5716318
1479 /* To calculate Product Quantity after updating a record if the Parameter is set to yes */
1480 IF l_auto_calc = 'Y' THEN
1481 IF l_formula_calc_flag = 'Y' THEN
1482 GMD_COMMON_VAL.Calculate_Total_Product_Qty( p_formula_id =>l_formula_id ,
1483 x_return_status => x_return_status,
1484 x_msg_count => x_msg_count,
1485 x_msg_data => x_msg_data);
1486 END IF;
1487 END IF;
1488 -- Kapil ME Auto-Prod :Bug#5716318
1489
1490 /* IF update of a line fails - Raise an exception
1491 rather than trying to insert other lines */
1492 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1493 RAISE FND_API.G_EXC_ERROR;
1494 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496 END IF;
1497
1498 END LOOP; -- End of main update loop
1499
1500 /* Check if p_commit is set to TRUE */
1501 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1502 (FND_API.To_Boolean( p_commit ) ) THEN
1503 Commit;
1504 END IF;
1505
1506 /* Get the message count and information */
1507 FND_MSG_PUB.Count_And_Get (
1508 p_count => x_msg_count,
1509 p_data => x_msg_data );
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 ROLLBACK to Update_FormulaDetail;
1513 x_return_status := FND_API.G_RET_STS_ERROR;
1514 FND_MSG_PUB.Count_And_Get (
1515 p_count => x_msg_count,
1516 p_data => x_msg_data );
1517 IF (l_debug = 'Y') THEN
1518 gmd_debug.put_line(' In Formula Detail pub - In error Exception Section '
1519 ||' - '
1520 ||x_return_status);
1521 END IF;
1522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523 ROLLBACK to Update_FormulaDetail;
1524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525 FND_MSG_PUB.Count_And_Get (
1526 p_count => x_msg_count,
1527 p_data => x_msg_data );
1528 IF (l_debug = 'Y') THEN
1529 gmd_debug.put_line(' In Formula Detail pub - In unexpected Exception Section '
1530 ||' - '
1531 ||x_return_status);
1532 END IF;
1533 WHEN OTHERS THEN
1534 ROLLBACK to Update_FormulaDetail;
1535 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 FND_MSG_PUB.Count_And_Get (
1538 p_count => x_msg_count,
1539 p_data => x_msg_data );
1540 IF (l_debug = 'Y') THEN
1541 gmd_debug.put_line(' In Formula Detail pub - In OTHERS Exception Section '
1542 ||' - '
1543 ||x_return_status);
1544 END IF;
1545
1546 END Update_FormulaDetail;
1547
1548
1549 /* ============================================= */
1550 /* Procedure: */
1551 /* Delete_FormulaDetail */
1552 /* */
1553 /* DESCRIPTION: */
1554 /* This PL/SQL procedure is responsible for */
1555 /* delete a formula detail. */
1556 /* */
1557 /* HISTORY */
1558 /* 06-Nov-2001 M. Grosser BUGS 1922679, 1981755 - Modified procedure Delete_FormulaDetail */
1559 /* to not allow the deletion of a product with a valid */
1560 /* validity rule against it and to not delete the only */
1561 /* ingredient or product in a formula */
1562 /* ============================================= */
1563 PROCEDURE Delete_FormulaDetail
1564 ( p_api_version IN NUMBER
1565 ,p_init_msg_list IN VARCHAR2
1566 ,p_commit IN VARCHAR2
1567 ,p_called_from_forms IN VARCHAR2 := 'NO'
1568 ,x_return_status OUT NOCOPY VARCHAR2
1569 ,x_msg_count OUT NOCOPY NUMBER
1570 ,x_msg_data OUT NOCOPY VARCHAR2
1571 ,p_formula_detail_tbl IN formula_update_dtl_tbl_type
1572 )
1573 IS
1574 /* Local Variables definitions */
1575 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMULADETAIL';
1576 l_api_version CONSTANT NUMBER := 1.1;
1577 l_user_id fnd_user.user_id%TYPE := 0;
1578 l_return_val NUMBER := 0;
1579 l_item_id ic_item_mst.item_id%TYPE := 0;
1580 l_inv_uom ic_item_mst.item_um%TYPE := NULL;
1581 l_fm_matl_dtl_rec fm_matl_dtl%ROWTYPE;
1582 p_formula_detail_rec GMD_FORMULA_COMMON_PUB.formula_update_rec_type;
1583 l_count NUMBER := 0;
1584
1585 l_formula_id NUMBER;
1586
1587 /* Define Cursors */
1588 -- Bug 4603060 removed user_id cursor
1589
1590 CURSOR check_num_details(pformula_id number, pline_type number) IS
1591 SELECT count(*)
1592 FROM fm_matl_dtl
1593 WHERE formula_id = pformula_id
1594 AND line_type = pline_type;
1595
1596 CURSOR get_formula_id(vFormulaLine_id NUMBER) IS
1597 SELECT formula_id
1598 FROM fm_matl_dtl
1599 WHERE formulaline_id = vFormulaLine_id;
1600 --Added inventory_item_id instead of item_id
1601 CURSOR check_validity_rules(pformula_id number, pitem_no varchar2 ) IS
1602 SELECT 1
1603 FROM gmd_recipes_b rcp,
1604 gmd_recipe_validity_rules vr,
1605 mtl_system_items_vl it
1606 WHERE vr.delete_mark = 0
1607 AND vr.validity_rule_status < 1000
1608 AND (vr.end_date IS NULL OR vr.end_date >= SYSDATE)
1609 AND it.concatenated_segments = pitem_no
1610 AND vr.inventory_item_id = it.inventory_item_id
1611 AND vr.recipe_id = rcp.recipe_id
1612 AND rcp.formula_id = pformula_id;
1613
1614 -- Kapil ME Auto-Prod :Bug#5716318
1615 l_auto_calc VARCHAR2(1);
1616 l_auto_calc_flag VARCHAR2(1);
1617 l_org_id NUMBER;
1618
1619 CURSOR C_get_org_id (V_formula_id NUMBER) IS
1620 SELECT OWNER_ORGANIZATION_ID
1621 FROM FM_FORM_MST_B
1622 WHERE FORMULA_ID = V_formula_id;
1623
1624 CURSOR C_get_auto_parameter (V_formula_id NUMBER) IS
1625 SELECT AUTO_PRODUCT_CALC
1626 FROM FM_FORM_MST_B
1627 WHERE FORMULA_ID = V_formula_id;
1628
1629 BEGIN
1630 /* Define Savepoint */
1631 SAVEPOINT Delete_FormulaDetail;
1632
1633 /* Standard Check for API compatibility */
1634 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1635 p_api_version ,
1636 l_api_name ,
1637 G_PKG_NAME )
1638 THEN
1639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640 END IF;
1641
1642 /* Initialize message list if p_init_msg_list is set to TRUE */
1643 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644 FND_MSG_PUB.initialize;
1645 END IF;
1646 /* Initialize API return status to success */
1647 x_return_status := FND_API.G_RET_STS_SUCCESS;
1648
1649 /* API body */
1650 /* 1. Does minimum validation */
1651 /* 2. Call the private API that does the database inserts/ updates */
1652
1653 IF (p_formula_detail_tbl.count = 0) THEN
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656
1657 FOR i in 1 .. p_formula_detail_tbl.count LOOP
1658
1659 x_return_status := FND_API.G_RET_STS_SUCCESS;
1660
1661 p_formula_detail_rec := p_formula_detail_tbl(i);
1662
1663 /* Check if there is a valid userid */
1664 -- Bug 4603060
1665 l_user_id := FND_GLOBAL.user_id;
1666 IF (l_user_id IS NULL) THEN
1667 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
1668 FND_MSG_PUB.Add;
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671
1672 /* =================================== */
1673 /* Check if an appropriate action_code */
1674 /* has been supplied */
1675 /* ================================== */
1676 IF (p_formula_detail_rec.record_type <> 'D')THEN
1677 FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_ACTION');
1678 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1679 FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_detail_rec.formula_vers);
1680 FND_MSG_PUB.Add;
1681 RAISE FND_API.G_EXC_ERROR;
1682 END IF;
1683
1684
1685 /* ============================ */
1686 /* Formulaline_id Validation */
1687 /* Must be passed and should exist */
1688 /* ============================ */
1689 IF (p_formula_detail_rec.formulaline_id IS NOT NULL) THEN /* if invalid formulaline no */
1690
1691 GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
1692 IF (l_return_val <> 0) THEN
1693 FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1694 FND_MSG_PUB.Add;
1695 RAISE FND_API.G_EXC_ERROR;
1696 END IF;
1697 ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN
1698 FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1699 FND_MSG_PUB.Add;
1700 RAISE FND_API.G_EXC_ERROR;
1701 END IF;
1702
1703 /* ======================== */
1704 /* Get the formula id */
1705 /* ======================== */
1706 IF (l_debug = 'Y') THEN
1707 gmd_debug.put_line(' In Formula Detail Pub - '
1708 ||' Before formula validation - '||x_return_status);
1709 END IF;
1710
1711 IF (p_formula_detail_rec.formula_id is NULL) THEN
1712 OPEN get_formula_id(p_formula_detail_rec.formulaline_id);
1713 FETCH get_formula_id INTO l_formula_id;
1714 IF (get_formula_id%NOTFOUND) THEN
1715 FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
1716 FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1717 FND_MSG_PUB.Add;
1718 CLOSE get_formula_id;
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721 CLOSE get_formula_id;
1722 ELSE
1723 l_formula_id := p_formula_detail_rec.formula_id;
1724 END IF;
1725
1726 IF NOT GMD_COMMON_VAL.Update_Allowed('FORMULA',l_formula_id) THEN
1727 FND_MESSAGE.SET_NAME('GMD','GMD_CANNOT_UPD_ENTITY');
1728 FND_MESSAGE.SET_TOKEN('NAME', 'formula');
1729 FND_MESSAGE.SET_TOKEN('ID', l_formula_id);
1730 FND_MESSAGE.SET_TOKEN('NO', p_formula_detail_rec.formula_no);
1731 FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
1732 FND_MESSAGE.SET_TOKEN('STATUS',
1733 GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
1734 FND_MSG_PUB.Add;
1735 RAISE FND_API.G_EXC_ERROR;
1736 END IF;
1737
1738 /* ================================================= */
1739 /* Checked if only 1 line if an ingredient or product */
1740 /* ================================================= */
1741 IF p_formula_detail_rec.line_type in (-1,1) THEN
1742 IF (NVL(p_called_from_forms,'NO') = 'NO') THEN
1743 OPEN check_num_details(l_formula_id,p_formula_detail_rec.line_type);
1744 FETCH check_num_details INTO l_count;
1745 /* If there s only 1 ingredient or product, stop the delete */
1746 IF (l_count < 2) THEN
1747 IF p_formula_detail_rec.line_type = 1 THEN
1748 FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_PRODUCT');
1749 ELSE
1750 FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_INGREDIENT');
1751 END IF;
1752 FND_MSG_PUB.Add;
1753 RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755 CLOSE check_num_details;
1756
1757 /* ================================================= */
1758 /* Checked for valid validity rule if a product */
1759 /* ================================================= */
1760 IF p_formula_detail_rec.line_type = 1 THEN
1761 OPEN check_validity_rules (l_formula_id,p_formula_detail_rec.item_no);
1762 FETCH check_validity_rules INTO l_count;
1763 /* If there are valid validity rules, stop the delete */
1764 IF (check_validity_rules%FOUND) THEN
1765 FND_MESSAGE.SET_NAME('GMD', 'GMD_VALID_VALIDITY');
1766 FND_MSG_PUB.Add;
1767 RAISE FND_API.G_EXC_ERROR;
1768 END IF;
1769 CLOSE check_validity_rules;
1770 END IF;
1771 END IF; /* If not called by a form */
1772 END IF; /* If this is an ingredient or product */
1773
1774 /* Call for private API */
1775 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1776
1777 l_fm_matl_dtl_rec.formulaline_id := p_formula_detail_rec.formulaline_id;
1778 l_fm_matl_dtl_rec.formula_id := l_formula_id;
1779
1780 /* New - added this condition below by Shyam */
1781 /* Check if this formula can be changed - if this formula is
1782 On-Hold or Obsolete or Frozen or Requested for Approval -
1783 the change of this formula is prevented */
1784
1785 GMD_FORMULA_DETAIL_PVT.Delete_FormulaDetail
1786 ( p_api_version => p_api_version
1787 ,p_init_msg_list => p_init_msg_list
1788 ,p_commit => FND_API.G_FALSE
1789 ,x_return_status => x_return_status
1790 ,x_msg_count => x_msg_count
1791 ,x_msg_data => x_msg_data
1792 ,p_formula_detail_rec => l_fm_matl_dtl_rec
1793 );
1794
1795 END IF; -- When return status is sucess
1796
1797 -- Kapil ME Auto-Prod :Bug#5716318
1798 /* Calculate Product Qty after deleting a record if the Parameter is set to Yes */
1799 OPEN C_get_org_id (l_formula_id);
1800 FETCH C_get_org_id INTO l_org_id;
1801 CLOSE C_get_org_id;
1802 GMD_API_GRP.FETCH_PARM_VALUES(P_orgn_id => l_org_id,
1803 P_parm_name => 'GMD_AUTO_PROD_CALC',
1804 P_parm_value => l_auto_calc,
1805 X_return_status => X_return_status );
1806 IF l_auto_calc = 'Y' THEN
1807 OPEN C_get_auto_parameter (l_formula_id);
1808 FETCH C_get_auto_parameter INTO l_auto_calc_flag;
1809 CLOSE C_get_auto_parameter;
1810 IF l_auto_calc_flag = 'Y' THEN
1811 GMD_COMMON_VAL.Calculate_Total_Product_Qty( p_formula_id =>l_formula_id ,
1812 x_return_status => x_return_status,
1813 x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data);
1815 END IF;
1816 END IF;
1817 -- Kapil ME Auto-Prod :Bug#5716318
1818
1819 /* End of API body */
1820 /* IF delete of a line fails - Raise an exception
1821 rather than trying to deleting other lines */
1822 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1823 RAISE FND_API.G_EXC_ERROR;
1824 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1825 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826 END IF;
1827
1828 END LOOP; -- End of main delete loop
1829
1830 /* Check if p_commit is set to TRUE */
1831 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1832 (FND_API.To_Boolean( p_commit ) ) THEN
1833 Commit;
1834 END IF;
1835
1836 /* Get the message count and information */
1837 FND_MSG_PUB.Count_And_Get (
1838 p_count => x_msg_count,
1839 p_data => x_msg_data );
1840 EXCEPTION
1841 WHEN FND_API.G_EXC_ERROR THEN
1842 ROLLBACK to Delete_FormulaDetail;
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844 FND_MSG_PUB.Count_And_Get (
1845 p_count => x_msg_count,
1846 p_data => x_msg_data );
1847
1848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849 ROLLBACK to Delete_FormulaDetail;
1850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851 FND_MSG_PUB.Count_And_Get (
1852 p_count => x_msg_count,
1853 p_data => x_msg_data );
1854
1855 WHEN OTHERS THEN
1856 ROLLBACK to Delete_FormulaDetail;
1857 fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859 FND_MSG_PUB.Count_And_Get (
1860 p_count => x_msg_count,
1861 p_data => x_msg_data );
1862
1863 END Delete_FormulaDetail;
1864
1865 END GMD_FORMULA_DETAIL_PUB;
[Home] [Help]