[Home] [Help]
PACKAGE BODY: APPS.GMD_FORMULA_DETAIL_PVT
Source
1 PACKAGE BODY GMD_FORMULA_DETAIL_PVT AS
2 /* $Header: GMDVFMDB.pls 120.3.12020000.3 2013/04/16 07:32:32 mtou ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_FORMULA_DETAIL_PVT';
5
6 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
7 --Forward declaration.
8 FUNCTION set_debug_flag RETURN VARCHAR2;
9 l_debug VARCHAR2(1) := set_debug_flag;
10
11 FUNCTION set_debug_flag RETURN VARCHAR2 IS
12 l_debug VARCHAR2(1):= 'N';
13 BEGIN
14 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
15 l_debug := 'Y';
16 END IF;
17 RETURN l_debug;
18 END set_debug_flag;
19 --Bug 3222090, NSRIVAST 20-FEB-2004, END
20
21 /* ======================================================================== */
22 /* Procedure: */
23 /* Insert_FormulaDetail */
24 /* */
25 /* DESCRIPTION: */
26 /* This PL/SQL procedure is responsible for */
27 /* inserting a formula detail. */
28 /* HISTORY: */
29 /* Kapil ME Bug# 5716318 - Added the new Percentage Fields for Auto -Prod*/
30 /* ME. */
31 /* ======================================================================== */
32 PROCEDURE Insert_FormulaDetail
33 ( p_api_version IN NUMBER
34 ,p_init_msg_list IN VARCHAR2
35 ,p_commit IN VARCHAR2
36 ,x_return_status OUT NOCOPY VARCHAR2
37 ,x_msg_count OUT NOCOPY NUMBER
38 ,x_msg_data OUT NOCOPY VARCHAR2
39 ,p_formula_detail_rec IN fm_matl_dtl%ROWTYPE
40 )
41 IS
42 /* Local Variables definitions */
43 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULADETAIL';
44 l_api_version CONSTANT NUMBER := 1.0;
45 X_msg_cnt NUMBER;
46 X_msg_dat VARCHAR2(100);
47 X_status VARCHAR2(1);
48 l_product_qty NUMBER;
49 l_ing_qty NUMBER;
50 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
51
52 BEGIN
53 /* Define Savepoint */
54 SAVEPOINT Insert_FormulaDetail_PVT;
55
56 /* Standard Check for API compatibility */
57 IF NOT FND_API.Compatible_API_Call( l_api_version
58 ,p_api_version
59 ,l_api_name
60 ,G_PKG_NAME )
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 /* Initialize message list if p_init_msg_list is set to TRUE */
66 IF FND_API.to_Boolean( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 /* Initialize API return status to success */
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 /* API Body */
74 /* Later on this insert should be changed to */
75 /* make insert on business view as opposed to tables directly. */
76
77 IF (l_debug = 'Y') THEN
78 gmd_debug.put_line(' ');
79 END IF;
80
81 IF (l_debug = 'Y') THEN
82 gmd_debug.put_line(' In formula detail Pvt - About to insert formulaline id = '
83 ||p_formula_detail_rec.formulaline_id);
84 END IF;
85
86 INSERT INTO fm_matl_dtl
87 (formulaline_id,
88 formula_id,
89 line_type,
90 line_no,
91 inventory_item_id,
92 organization_id,
93 revision,
94 qty,
95 detail_uom,
96 release_type,
97 scrap_factor,
98 scale_type,
99 cost_alloc,
100 phantom_type,
101 buffer_ind,
102 rework_type,
103 text_code,
104 tpformula_id,
105 iaformula_id,
106 scale_multiple,
107 contribute_yield_ind,
108 scale_uom,
109 contribute_step_qty_ind ,
110 scale_rounding_variance,
111 rounding_direction,
112 /*Bug 2509076 - Thomas Daniel QM Integration */
113 by_product_type,
114 ingredient_end_date, --Bug 4479101
115 created_by,
116 creation_date,
117 last_update_date,
118 last_updated_by,
119 last_update_login,
120 attribute1, attribute2,
121 attribute3, attribute4,
122 attribute5, attribute6,
123 attribute7, attribute8,
124 attribute9, attribute10,
125 attribute11, attribute12,
126 attribute13, attribute14,
127 attribute15, attribute16,
128 attribute17, attribute18,
129 attribute19, attribute20,
130 attribute21, attribute22,
131 attribute23, attribute24,
132 attribute25, attribute26,
133 attribute27, attribute28,
134 attribute29, attribute30,
135 attribute_category,
136 prod_percent)
137 VALUES
138 (p_formula_detail_rec.formulaline_id,
139 p_formula_detail_rec.formula_id,
140 p_formula_detail_rec.line_type,
141 p_formula_detail_rec.line_no ,
142 p_formula_detail_rec.inventory_item_id,
143 p_formula_detail_rec.organization_id,
144 p_formula_detail_rec.revision,
145 p_formula_detail_rec.qty,
146 p_formula_detail_rec.detail_uom,
147 p_formula_detail_rec.release_type,
148 p_formula_detail_rec.scrap_factor,
149 p_formula_detail_rec.scale_type,
150 p_formula_detail_rec.cost_alloc,
151 p_formula_detail_rec.phantom_type,
152 p_formula_detail_rec.buffer_ind,
153 p_formula_detail_rec.rework_type,
154 p_formula_detail_rec.text_code,
155 p_formula_detail_rec.tpformula_id,
156 p_formula_detail_rec.iaformula_id,
157 p_formula_detail_rec.scale_multiple,
158 p_formula_detail_rec.contribute_yield_ind,
159 p_formula_detail_rec.scale_uom,
160 p_formula_detail_rec.contribute_step_qty_ind ,
161 p_formula_detail_rec.scale_rounding_variance ,
162 p_formula_detail_rec.rounding_direction ,
163 /*Bug 2509076 - Thomas Daniel QM Integration */
164 p_formula_detail_rec.by_product_type,
165 p_formula_detail_rec.ingredient_end_date, --Bug 4479101
166 p_formula_detail_rec.created_by,
167 p_formula_detail_rec.creation_date,
168 p_formula_detail_rec.last_update_date,
169 p_formula_detail_rec.last_updated_by,
170 p_formula_detail_rec.last_update_login,
171 p_formula_detail_rec.attribute1, p_formula_detail_rec.attribute2,
172 p_formula_detail_rec.attribute3, p_formula_detail_rec.attribute4,
173 p_formula_detail_rec.attribute5, p_formula_detail_rec.attribute6,
174 p_formula_detail_rec.attribute7, p_formula_detail_rec.attribute8,
175 p_formula_detail_rec.attribute9, p_formula_detail_rec.attribute10,
176 p_formula_detail_rec.attribute11, p_formula_detail_rec.attribute12,
177 p_formula_detail_rec.attribute13, p_formula_detail_rec.attribute14,
178 p_formula_detail_rec.attribute15, p_formula_detail_rec.attribute16,
179 p_formula_detail_rec.attribute17, p_formula_detail_rec.attribute18,
180 p_formula_detail_rec.attribute19, p_formula_detail_rec.attribute20,
181 p_formula_detail_rec.attribute21, p_formula_detail_rec.attribute22,
182 p_formula_detail_rec.attribute23, p_formula_detail_rec.attribute24,
183 p_formula_detail_rec.attribute25, p_formula_detail_rec.attribute26,
184 p_formula_detail_rec.attribute27, p_formula_detail_rec.attribute28,
185 p_formula_detail_rec.attribute29, p_formula_detail_rec.attribute30,
186 p_formula_detail_rec.attribute_category,
187 p_formula_detail_rec.prod_percent);
188 /* END API Body */
189 IF (l_debug = 'Y') THEN
190 gmd_debug.put_line(' In formula detail Pvt - After insert formulaline insert ');
191 END IF;
192
193 IF (l_debug = 'Y') THEN
194 gmd_debug.put_line(' In formula detail Pvt - About to recalculate TOQ ');
195 END IF;
196 /* Recalculate the TOQ and TIQ */
197 GMD_COMMON_VAL.calculate_total_qty(
198 formula_id => p_formula_detail_rec.formula_id,
199 x_product_qty => l_product_qty ,
200 x_ingredient_qty => l_ing_qty ,
201 x_uom => l_uom ,
202 x_return_status => x_return_status ,
203 x_msg_count => X_msg_cnt ,
204 x_msg_data => x_msg_dat );
205
206 IF (l_debug = 'Y') THEN
207 gmd_debug.put_line(' In formula detail Pvt - Update header with TOQ '
208 ||' TIQ = '
209 ||l_ing_qty
210 ||' TOQ = '
211 ||l_product_qty);
212 END IF;
213 /* Update formula header table with TOQ and TIQ */
214 UPDATE fm_form_mst_b
215 SET total_output_qty = l_product_qty,
216 total_input_qty = l_ing_qty,
217 yield_uom = l_uom
218 WHERE formula_id = p_formula_detail_rec.formula_id;
219
220 /* Check if p_commit is set to TRUE */
221 IF FND_API.To_Boolean(p_commit) THEN
222 Commit;
223 END IF;
224
225 /* Get the message count and information */
226 FND_MSG_PUB.Count_And_Get (
227 p_count => x_msg_count,
228 p_data => x_msg_data );
229 EXCEPTION
230 WHEN FND_API.G_EXC_ERROR THEN
231 ROLLBACK to Insert_FormulaDetail_PVT;
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 FND_MSG_PUB.Count_And_Get (
234 p_count => x_msg_count,
235 p_data => x_msg_data );
236
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 ROLLBACK to Insert_FormulaDetail_PVT;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 FND_MSG_PUB.Count_And_Get (
241 p_count => x_msg_count,
242 p_data => x_msg_data );
243
244 WHEN OTHERS THEN
245 ROLLBACK to Insert_FormulaDetail_PVT;
246 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 IF (l_debug = 'Y') THEN
249 gmd_debug.put_line(' In Formula Detail Pvt - In OTHERS Exception Section '
250 ||' - '
251 ||x_return_status);
252 END IF;
253
254 END Insert_FormulaDetail;
255
256 /* ======================================================================== */
257 /* Procedure: */
258 /* Update_FormulaDetail */
259 /* */
260 /* DESCRIPTION: */
261 /* This PL/SQL procedure is responsible for updating a formula. */
262 /* details. */
263 /* HISTORY: */
264 /* Kapil ME Bug# 5716318 - Added the new Percentage Fields for Auto -Prod*/
265 /* ME. */
266 /* ======================================================================== */
267 PROCEDURE Update_FormulaDetail
268 ( p_api_version IN NUMBER
269 ,p_init_msg_list IN VARCHAR2
270 ,p_commit IN VARCHAR2
271 ,x_return_status OUT NOCOPY VARCHAR2
272 ,x_msg_count OUT NOCOPY NUMBER
273 ,x_msg_data OUT NOCOPY VARCHAR2
274 ,p_formula_detail_rec IN fm_matl_dtl%ROWTYPE
275 )
276 IS
277
278 /* Local Variables definitions */
279 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULADETAIL';
280 l_api_version CONSTANT NUMBER := 1.0;
281 l_line_no fm_matl_dtl.line_no%TYPE;
282 l_line_type fm_matl_dtl.line_type%TYPE;
283 l_qty fm_matl_dtl.qty%TYPE;
284 l_item_um fm_matl_dtl.detail_uom%TYPE;
285 l_release_type fm_matl_dtl.release_type%TYPE;
286 l_scrap_factor fm_matl_dtl.scrap_factor%TYPE;
287 l_scale_type fm_matl_dtl.scale_type%TYPE;
288 l_phantom_type fm_matl_dtl.phantom_type%TYPE;
289 X_msg_cnt NUMBER;
290 X_msg_dat VARCHAR2(100);
291 X_status VARCHAR2(1);
292 l_product_qty NUMBER;
293 l_ing_qty NUMBER;
294 l_uom VARCHAR2(3);
295 l_return_val NUMBER := 0;
296
297 CURSOR C_get_orgid (V_formula_id NUMBER) IS
298 SELECT owner_organization_id
299 FROM fm_form_mst_b
300 WHERE formula_id = V_formula_id;
301 l_org_id NUMBER;
302
303 BEGIN
304 /* Define Savepoint */
305 SAVEPOINT Update_FormulaDetail_PVT;
306
307 /* Standard Check for API compatibility */
308 IF NOT FND_API.Compatible_API_Call ( l_api_version
309 ,p_api_version
310 ,l_api_name
311 ,G_PKG_NAME )
312 THEN
313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
314 END IF;
315
316 /* Initialize message list if p_init_msg_list is set to TRUE */
317 IF FND_API.to_Boolean( p_init_msg_list ) THEN
318 FND_MSG_PUB.initialize;
319 END IF;
320 /* Initialize API return status to success */
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 IF (p_formula_detail_rec.organization_id IS NULL) THEN
324 OPEN C_get_orgid (p_formula_detail_rec.formula_id);
325 FETCH C_get_orgid INTO l_org_id;
326 CLOSE C_get_orgid;
327 ELSE
328 l_org_id := p_formula_detail_rec.organization_id;
329 END IF;
330
331 /* API body */
332 /* Later on to be changed to update a business view */
333 /* and not a table. */
334 IF (l_debug = 'Y') THEN
335 gmd_debug.put_line(' In Formula Detail PVT - '
336 ||' Before the update of fm_matl_dtl '
337 ||' formulaline id - '
338 ||p_formula_detail_rec.formulaline_id
339 ||' line no '
340 ||p_formula_detail_rec.line_no
341 ||' line_type '
342 ||p_formula_detail_rec.line_type
343 ||' Contrib in d '
344 ||p_formula_detail_rec.contribute_yield_ind
345 ||' - '
346 ||x_return_status);
347 END IF;
348
349 --bug16664160 fix start.
350 if (p_formula_detail_rec.inventory_item_id is null) then
351
352 UPDATE fm_matl_dtl SET
353 line_no = p_formula_detail_rec.line_no,
354 line_type = p_formula_detail_rec.line_type,
355 qty = p_formula_detail_rec.qty,
356 organization_id = l_org_id,
357 revision = p_formula_detail_rec.revision,
358 detail_uom = p_formula_detail_rec.detail_uom,
359 release_type = p_formula_detail_rec.release_type,
360 scale_type = p_formula_detail_rec.scale_type,
361 scrap_factor = p_formula_detail_rec.scrap_factor,
362 cost_alloc = p_formula_detail_rec.cost_alloc,
363 phantom_type = p_formula_detail_rec.phantom_type,
364 buffer_ind = p_formula_detail_rec.buffer_ind,
365 rework_type = p_formula_detail_rec.rework_type,
366 tpformula_id = p_formula_detail_rec.tpformula_id ,
367 iaformula_id = p_formula_detail_rec.iaformula_id ,
368 scale_multiple = p_formula_detail_rec.scale_multiple ,
369 contribute_yield_ind = p_formula_detail_rec.contribute_yield_ind ,
370 scale_uom = p_formula_detail_rec.scale_uom ,
371 contribute_step_qty_ind = p_formula_detail_rec.contribute_step_qty_ind,
372 scale_rounding_variance = p_formula_detail_rec.scale_rounding_variance,
373 rounding_direction = p_formula_detail_rec.rounding_direction,
374 /*Bug 2509076 - Thomas Daniel QM Integration */
375 by_product_type = p_formula_detail_rec.by_product_type,
376 ingredient_end_date = p_formula_detail_rec.ingredient_end_date, --bug 4479101
377 text_code = p_formula_detail_rec.text_code,
378 last_update_date = p_formula_detail_rec.last_update_date,
379 last_updated_by = p_formula_detail_rec.last_updated_by,
380 last_update_login = p_formula_detail_rec.last_update_login,
381 attribute1 = p_formula_detail_rec.attribute1,
382 attribute2 = p_formula_detail_rec.attribute2,
383 attribute3 = p_formula_detail_rec.attribute3,
384 attribute4 = p_formula_detail_rec.attribute4,
385 attribute5 = p_formula_detail_rec.attribute5,
386 attribute6 = p_formula_detail_rec.attribute6,
387 attribute7 = p_formula_detail_rec.attribute7,
388 attribute8 = p_formula_detail_rec.attribute8,
389 attribute9 = p_formula_detail_rec.attribute9,
390 attribute10 = p_formula_detail_rec.attribute10,
391 attribute11 = p_formula_detail_rec.attribute11,
392 attribute12 = p_formula_detail_rec.attribute12,
393 attribute13 = p_formula_detail_rec.attribute13,
394 attribute14 = p_formula_detail_rec.attribute14,
395 attribute15 = p_formula_detail_rec.attribute15,
396 attribute16 = p_formula_detail_rec.attribute16,
397 attribute17 = p_formula_detail_rec.attribute17,
398 attribute18 = p_formula_detail_rec.attribute18,
399 attribute19 = p_formula_detail_rec.attribute19,
400 attribute20 = p_formula_detail_rec.attribute20,
401 attribute21 = p_formula_detail_rec.attribute21,
402 attribute22 = p_formula_detail_rec.attribute22,
403 attribute23 = p_formula_detail_rec.attribute23,
404 attribute24 = p_formula_detail_rec.attribute24,
405 attribute25 = p_formula_detail_rec.attribute25,
406 attribute26 = p_formula_detail_rec.attribute26,
407 attribute27 = p_formula_detail_rec.attribute27,
408 attribute28 = p_formula_detail_rec.attribute28,
409 attribute29 = p_formula_detail_rec.attribute29,
410 attribute30 = p_formula_detail_rec.attribute30,
411 attribute_category = p_formula_detail_rec.attribute_category,
412 prod_percent = p_formula_detail_rec.prod_percent
413 WHERE
414 formulaline_id = p_formula_detail_rec.formulaline_id;
415 else
416 UPDATE fm_matl_dtl SET
417 line_no = p_formula_detail_rec.line_no,
418 line_type = p_formula_detail_rec.line_type,
419 qty = p_formula_detail_rec.qty,
420 organization_id = l_org_id,
421 inventory_item_id = p_formula_detail_rec.inventory_item_id, --add in bug14710339
422 revision = p_formula_detail_rec.revision,
423 detail_uom = p_formula_detail_rec.detail_uom,
424 release_type = p_formula_detail_rec.release_type,
425 scale_type = p_formula_detail_rec.scale_type,
426 scrap_factor = p_formula_detail_rec.scrap_factor,
427 cost_alloc = p_formula_detail_rec.cost_alloc,
428 phantom_type = p_formula_detail_rec.phantom_type,
429 buffer_ind = p_formula_detail_rec.buffer_ind,
430 rework_type = p_formula_detail_rec.rework_type,
431 tpformula_id = p_formula_detail_rec.tpformula_id ,
432 iaformula_id = p_formula_detail_rec.iaformula_id ,
433 scale_multiple = p_formula_detail_rec.scale_multiple ,
434 contribute_yield_ind = p_formula_detail_rec.contribute_yield_ind ,
435 scale_uom = p_formula_detail_rec.scale_uom ,
436 contribute_step_qty_ind = p_formula_detail_rec.contribute_step_qty_ind,
437 scale_rounding_variance = p_formula_detail_rec.scale_rounding_variance,
438 rounding_direction = p_formula_detail_rec.rounding_direction,
439 /*Bug 2509076 - Thomas Daniel QM Integration */
440 by_product_type = p_formula_detail_rec.by_product_type,
441 ingredient_end_date = p_formula_detail_rec.ingredient_end_date, --bug 4479101
442 text_code = p_formula_detail_rec.text_code,
443 last_update_date = p_formula_detail_rec.last_update_date,
444 last_updated_by = p_formula_detail_rec.last_updated_by,
445 last_update_login = p_formula_detail_rec.last_update_login,
446 attribute1 = p_formula_detail_rec.attribute1,
447 attribute2 = p_formula_detail_rec.attribute2,
448 attribute3 = p_formula_detail_rec.attribute3,
449 attribute4 = p_formula_detail_rec.attribute4,
450 attribute5 = p_formula_detail_rec.attribute5,
451 attribute6 = p_formula_detail_rec.attribute6,
452 attribute7 = p_formula_detail_rec.attribute7,
453 attribute8 = p_formula_detail_rec.attribute8,
454 attribute9 = p_formula_detail_rec.attribute9,
455 attribute10 = p_formula_detail_rec.attribute10,
456 attribute11 = p_formula_detail_rec.attribute11,
457 attribute12 = p_formula_detail_rec.attribute12,
458 attribute13 = p_formula_detail_rec.attribute13,
459 attribute14 = p_formula_detail_rec.attribute14,
460 attribute15 = p_formula_detail_rec.attribute15,
461 attribute16 = p_formula_detail_rec.attribute16,
462 attribute17 = p_formula_detail_rec.attribute17,
463 attribute18 = p_formula_detail_rec.attribute18,
464 attribute19 = p_formula_detail_rec.attribute19,
465 attribute20 = p_formula_detail_rec.attribute20,
466 attribute21 = p_formula_detail_rec.attribute21,
467 attribute22 = p_formula_detail_rec.attribute22,
468 attribute23 = p_formula_detail_rec.attribute23,
469 attribute24 = p_formula_detail_rec.attribute24,
470 attribute25 = p_formula_detail_rec.attribute25,
471 attribute26 = p_formula_detail_rec.attribute26,
472 attribute27 = p_formula_detail_rec.attribute27,
473 attribute28 = p_formula_detail_rec.attribute28,
474 attribute29 = p_formula_detail_rec.attribute29,
475 attribute30 = p_formula_detail_rec.attribute30,
476 attribute_category = p_formula_detail_rec.attribute_category,
477 prod_percent = p_formula_detail_rec.prod_percent
478 WHERE
479 formulaline_id = p_formula_detail_rec.formulaline_id;
480
481
482 end if;
483 --bug16664160 fix end
484
485 /* End API body */
486 /* Calculate the total input and output qty and update the formula header table */
487 IF (l_debug = 'Y') THEN
488 gmd_debug.put_line(' In Formula Detail PVT - '
489 ||' Before the TOQ calculation '
490 ||' - '
491 ||x_return_status);
492 END IF;
493 GMD_COMMON_VAL.calculate_total_qty(
494 FORMULA_ID => p_formula_detail_rec.formula_id,
495 X_PRODUCT_QTY => l_product_qty ,
496 X_INGREDIENT_QTY => l_ing_qty ,
497 X_UOM => l_uom ,
498 X_RETURN_STATUS => x_return_status ,
499 X_MSG_COUNT => X_msg_cnt ,
500 X_MSG_DATA => x_msg_dat );
501
502 IF (l_debug = 'Y') THEN
503 gmd_debug.put_line(' In Formula Detail PVT - '
504 ||' Before the update of fm header with toq and tiq = '
505 ||' TOQ = '
506 ||l_product_qty
507 ||' TIQ = '
508 ||l_ing_qty
509 ||' - '
510 ||x_return_status);
511 END IF;
512 UPDATE fm_form_mst_b
513 SET total_output_qty = l_product_qty,
514 total_input_qty = l_ing_qty,
515 yield_uom = l_uom
516 WHERE formula_id = p_formula_detail_rec.formula_id;
517
518
519 /* Check if p_commit is set to TRUE */
520 IF FND_API.To_Boolean( p_commit ) THEN
521 Commit;
522 END IF;
523
524 /* Get the message count and information */
525 FND_MSG_PUB.Count_And_Get (
526 p_count => x_msg_count,
527 p_data => x_msg_data );
528
529
530 EXCEPTION
531 WHEN FND_API.G_EXC_ERROR THEN
532 ROLLBACK to Update_FormulaDetail_PVT;
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 FND_MSG_PUB.Count_And_Get (
535 p_count => x_msg_count,
536 p_data => x_msg_data );
537
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 ROLLBACK to Update_FormulaDetail_PVT;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 FND_MSG_PUB.Count_And_Get (
542 p_count => x_msg_count,
543 p_data => x_msg_data );
544
545 WHEN OTHERS THEN
546 ROLLBACK to Update_FormulaDetail_PVT;
547 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 IF (l_debug = 'Y') THEN
550 gmd_debug.put_line(' In Formula Detail Pvt - In OTHERS Exception Section '
551 ||' - '
552 ||x_return_status);
553 END IF;
554 END Update_FormulaDetail;
555
556 /* ============================================= */
557 /* Procedure: */
558 /* Delete_FormulaDetail */
559 /* */
560 /* DESCRIPTION: */
561 /* This PL/SQL procedure is responsible for */
562 /* deleting formula detail. */
563 /* HISTORY: */
564 /* ============================================= */
565 PROCEDURE Delete_FormulaDetail
566 ( p_api_version IN NUMBER ,
567 p_init_msg_list IN VARCHAR2 ,
568 p_commit IN VARCHAR2 ,
569 x_return_status OUT NOCOPY VARCHAR2 ,
570 x_msg_count OUT NOCOPY NUMBER ,
571 x_msg_data OUT NOCOPY VARCHAR2 ,
572 p_formula_detail_rec IN fm_matl_dtl%ROWTYPE
573 )
574 IS
575
576 /* Local Variables definitions */
577 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FORMULADETAIL';
578 l_api_version CONSTANT NUMBER := 1.0;
579
580 X_msg_cnt NUMBER;
581 X_msg_dat VARCHAR2(100);
582 X_status varchar2(1);
583 l_product_qty NUMBER;
584 l_ing_qty NUMBER;
585 l_uom mtl_units_of_measure.unit_of_measure%TYPE;
586
587 BEGIN
588 /* Define Savepoint */
589 SAVEPOINT Delete_FormulaDetail_PVT;
590
591 /* Standard Check for API compatibility */
592 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
593 p_api_version ,
594 l_api_name ,
595 G_PKG_NAME )
596 THEN
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598 END IF;
599
600 /* Initialize message list if p_init_msg_list is set to TRUE */
601 IF FND_API.to_Boolean( p_init_msg_list ) THEN
602 FND_MSG_PUB.initialize;
603 END IF;
604 /* Initialize API return status to success */
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606
607 /* API body */
608 /* Later on to be changed to update a business view */
609 /* and not a table. */
610
611 DELETE FROM fm_matl_dtl
612 WHERE
613 formulaline_id = p_formula_detail_rec.formulaline_id;
614
615 IF(SQL%ROWCOUNT = 0) THEN
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;
618
619 /* End API body */
620
621 /* Calculate the total input and output qty and update the formula header table */
622 GMD_COMMON_VAL.calculate_total_qty(
623 FORMULA_ID => p_formula_detail_rec.formula_id,
624 X_PRODUCT_QTY => l_product_qty ,
625 X_INGREDIENT_QTY => l_ing_qty ,
626 X_UOM => l_uom ,
627 X_RETURN_STATUS => x_return_status ,
628 X_MSG_COUNT => X_msg_cnt ,
629 X_MSG_DATA => x_msg_dat );
630
631
632 update fm_form_mst_b
633 set total_output_qty = l_product_qty,
634 total_input_qty = l_ing_qty,
635 yield_uom = l_uom
636 where formula_id = p_formula_detail_rec.formula_id;
637
638 /* Check if p_commit is set to TRUE */
639 IF FND_API.To_Boolean( p_commit ) THEN
640 Commit;
641 END IF;
642
643 /* Get the message count and information */
644 FND_MSG_PUB.Count_And_Get (
645 p_count => x_msg_count,
646 p_data => x_msg_data );
647
648
649 EXCEPTION
650 WHEN FND_API.G_EXC_ERROR THEN
651 ROLLBACK to Delete_FormulaDetail_PVT;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 FND_MSG_PUB.Count_And_Get (
654 p_count => x_msg_count,
655 p_data => x_msg_data );
656
657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
658 ROLLBACK to Delete_FormulaDetail_PVT;
659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
660 FND_MSG_PUB.Count_And_Get (
661 p_count => x_msg_count,
662 p_data => x_msg_data );
663
664 WHEN OTHERS THEN
665 ROLLBACK to Delete_FormulaDetail_PVT;
666 fnd_msg_pub.add_exc_msg (G_pkg_name, l_api_name);
667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
668
669 END Delete_FormulaDetail;
670
671 END GMD_FORMULA_DETAIL_PVT;