[Home] [Help]
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.
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
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;
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;
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,
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;
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:
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 */
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: /* ======================================= */
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 */
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;
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
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:
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:
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
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 '
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 '
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 );
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 '
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 */
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;
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);
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
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 */
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: /* ========================================= */
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');
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
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;
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
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
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 '
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 '
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 );
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 '
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:
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: /* =================================== */
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:
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');
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: /* ======================== */
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;
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: /* ================================================= */
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:
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;
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
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
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
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 );
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;