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