[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;
361: IF l_formula_calc_flag = 'Y' AND p_formula_detail_rec.line_type = 1
362: AND p_formula_detail_rec.scale_type_dtl =1 AND p_formula_detail_rec.prod_percent IS NULL THEN
363: /* Error to be raised for Proportional Products when Percentages are not passed */
364: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_YES');
365: FND_MSG_PUB.Add;
366: RAISE FND_API.G_EXC_ERROR;
367: ELSIF l_formula_calc_flag = 'Y' AND (p_formula_detail_rec.prod_percent IS NOT NULL )
368: AND ( p_formula_detail_rec.line_type IN (-1,2) OR
369: ( p_formula_detail_rec.line_type = 1 AND p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
369: ( p_formula_detail_rec.line_type = 1 AND p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
370: /* Error to be raised when Percentages are passed for Ingredients/By-Products or Fixed
371: Products */
372: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_CANNOT');
373: FND_MSG_PUB.Add;
374: RAISE FND_API.G_EXC_ERROR;
375: END IF;
376: END IF;
377:
379: AND p_formula_detail_rec.prod_percent IS NOT NULL THEN
380: /* Error to be raised when Percentages are passed when Parameter is not Set tp
381: Calculate Product qty */
382: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_NO');
383: FND_MSG_PUB.Add;
384: RAISE FND_API.G_EXC_ERROR;
385: END IF;
386: -- Kapil ME Auto-Prod :Bug#5716318
387:
508: Commit;
509: END IF;
510:
511: /* Get the message count and information */
512: FND_MSG_PUB.Count_And_Get (
513: p_count => x_msg_count,
514: p_data => x_msg_data );
515:
516: EXCEPTION
516: EXCEPTION
517: WHEN FND_API.G_EXC_ERROR THEN
518: ROLLBACK to Insert_FormulaDetail;
519: x_return_status := FND_API.G_RET_STS_ERROR;
520: FND_MSG_PUB.Count_And_Get (
521: p_count => x_msg_count,
522: p_data => x_msg_data );
523: IF (l_debug = 'Y') THEN
524: gmd_debug.put_line(' In Formula Detail Pub - In Error Exception Section '
528:
529: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530: ROLLBACK to Insert_FormulaDetail;
531: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532: FND_MSG_PUB.Count_And_Get (
533: p_count => x_msg_count,
534: p_data => x_msg_data );
535: IF (l_debug = 'Y') THEN
536: gmd_debug.put_line(' In Formula Detail Pub - In Unexpected Exception Section '
539: END IF;
540:
541: WHEN OTHERS THEN
542: ROLLBACK to Insert_FormulaDetail;
543: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
544: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545: FND_MSG_PUB.Count_And_Get (
546: p_count => x_msg_count,
547: p_data => x_msg_data );
541: WHEN OTHERS THEN
542: ROLLBACK to Insert_FormulaDetail;
543: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
544: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545: FND_MSG_PUB.Count_And_Get (
546: p_count => x_msg_count,
547: p_data => x_msg_data );
548: IF (l_debug = 'Y') THEN
549: gmd_debug.put_line(' In Formula Detail Pub - In OTHERS Exception Section '
695: GMDFMVAL_PUB.p_called_from_forms := p_called_from_forms;
696:
697: /* Initialize message list if p_init_msg_list is set to TRUE */
698: IF FND_API.to_Boolean( p_init_msg_list ) THEN
699: FND_MSG_PUB.initialize;
700: END IF;
701:
702: /* 1. Does validation */
703: /* 2. Call the private API that does the database updates */
740: l_formula_id, l_return_val);
741: IF (l_return_val <> 0) THEN
742: IF (p_formula_detail_rec.formula_no IS NULL) THEN
743: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
744: FND_MSG_PUB.Add;
745: RAISE FND_API.G_EXC_ERROR;
746: ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
747: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
748: FND_MSG_PUB.Add;
744: FND_MSG_PUB.Add;
745: RAISE FND_API.G_EXC_ERROR;
746: ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
747: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
748: FND_MSG_PUB.Add;
749: RAISE FND_API.G_EXC_ERROR;
750: ELSE
751: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
752: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
749: RAISE FND_API.G_EXC_ERROR;
750: ELSE
751: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
752: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
753: FND_MSG_PUB.Add;
754: RAISE FND_API.G_EXC_ERROR;
755: END IF;
756: END IF;
757: ELSE
770: FND_MESSAGE.SET_TOKEN('NO', p_formula_detail_rec.formula_no);
771: FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
772: FND_MESSAGE.SET_TOKEN('STATUS',
773: GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
774: FND_MSG_PUB.Add;
775: RAISE FND_API.G_EXC_ERROR;
776: END IF;
777:
778: /* Check if there is a valid userid */
783: -- Bug 4603060 User the user_id from context
784: l_user_id := FND_GLOBAL.user_id;
785: IF (l_user_id IS NULL) THEN
786: FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
787: FND_MSG_PUB.Add;
788: RAISE FND_API.G_EXC_ERROR;
789: END IF;
790:
791: /* ========================================= */
799: IF (p_formula_detail_rec.formulaline_id IS NOT NULL) THEN /* if invalid formula no */
800: GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
801: IF (l_return_val <> 0) THEN
802: FND_MESSAGE.SET_NAME('GMD','FM_INVALID_FMLINE_ID');
803: FND_MSG_PUB.Add;
804: RAISE FND_API.G_EXC_ERROR;
805: END IF;
806: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN /* missing formula no */
807: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
804: RAISE FND_API.G_EXC_ERROR;
805: END IF;
806: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN /* missing formula no */
807: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
808: FND_MSG_PUB.Add;
809: RAISE FND_API.G_EXC_ERROR;
810: END IF;
811:
812: -- Bug# 5554631 KMOTUPAL - TO prevent Update of Item Revision
812: -- Bug# 5554631 KMOTUPAL - TO prevent Update of Item Revision
813: IF (p_formula_detail_rec.revision IS NOT NULL) THEN
814: FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_COL_UPDATES');
815: FND_MESSAGE.SET_TOKEN('NAME','REVISION');
816: FND_MSG_PUB.Add;
817: END IF;
818:
819: OPEN C_get_orgid (l_formula_id);
820: FETCH C_get_orgid INTO l_org_id;
945: (p_formula_detail_rec.line_type = 1 And p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
946: /* Error to be raised if Percentages are passed for Ingredients/By-Products or when
947: when the Parameter is not set to calculate Product Qty */
948: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGES_NOT');
949: FND_MSG_PUB.Add;
950: RAISE FND_API.G_EXC_ERROR;
951: END IF;
952: END IF;
953: -- Kapil ME Auto-Prod :Bug#5716318
1503: Commit;
1504: END IF;
1505:
1506: /* Get the message count and information */
1507: FND_MSG_PUB.Count_And_Get (
1508: p_count => x_msg_count,
1509: p_data => x_msg_data );
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1512: ROLLBACK to Update_FormulaDetail;
1513: x_return_status := FND_API.G_RET_STS_ERROR;
1514: FND_MSG_PUB.Count_And_Get (
1515: p_count => x_msg_count,
1516: p_data => x_msg_data );
1517: IF (l_debug = 'Y') THEN
1518: gmd_debug.put_line(' In Formula Detail pub - In error Exception Section '
1521: END IF;
1522: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523: ROLLBACK to Update_FormulaDetail;
1524: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525: FND_MSG_PUB.Count_And_Get (
1526: p_count => x_msg_count,
1527: p_data => x_msg_data );
1528: IF (l_debug = 'Y') THEN
1529: gmd_debug.put_line(' In Formula Detail pub - In unexpected Exception Section '
1531: ||x_return_status);
1532: END IF;
1533: WHEN OTHERS THEN
1534: ROLLBACK to Update_FormulaDetail;
1535: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537: FND_MSG_PUB.Count_And_Get (
1538: p_count => x_msg_count,
1539: p_data => x_msg_data );
1533: WHEN OTHERS THEN
1534: ROLLBACK to Update_FormulaDetail;
1535: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537: FND_MSG_PUB.Count_And_Get (
1538: p_count => x_msg_count,
1539: p_data => x_msg_data );
1540: IF (l_debug = 'Y') THEN
1541: gmd_debug.put_line(' In Formula Detail pub - In OTHERS Exception Section '
1640: END IF;
1641:
1642: /* Initialize message list if p_init_msg_list is set to TRUE */
1643: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644: FND_MSG_PUB.initialize;
1645: END IF;
1646: /* Initialize API return status to success */
1647: x_return_status := FND_API.G_RET_STS_SUCCESS;
1648:
1664: -- Bug 4603060
1665: l_user_id := FND_GLOBAL.user_id;
1666: IF (l_user_id IS NULL) THEN
1667: FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
1668: FND_MSG_PUB.Add;
1669: RAISE FND_API.G_EXC_ERROR;
1670: END IF;
1671:
1672: /* =================================== */
1676: IF (p_formula_detail_rec.record_type <> 'D')THEN
1677: FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_ACTION');
1678: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1679: FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_detail_rec.formula_vers);
1680: FND_MSG_PUB.Add;
1681: RAISE FND_API.G_EXC_ERROR;
1682: END IF;
1683:
1684:
1690:
1691: GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
1692: IF (l_return_val <> 0) THEN
1693: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1694: FND_MSG_PUB.Add;
1695: RAISE FND_API.G_EXC_ERROR;
1696: END IF;
1697: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN
1698: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1695: RAISE FND_API.G_EXC_ERROR;
1696: END IF;
1697: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN
1698: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1699: FND_MSG_PUB.Add;
1700: RAISE FND_API.G_EXC_ERROR;
1701: END IF;
1702:
1703: /* ======================== */
1713: FETCH get_formula_id INTO l_formula_id;
1714: IF (get_formula_id%NOTFOUND) THEN
1715: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
1716: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1717: FND_MSG_PUB.Add;
1718: CLOSE get_formula_id;
1719: RAISE FND_API.G_EXC_ERROR;
1720: END IF;
1721: CLOSE get_formula_id;
1730: FND_MESSAGE.SET_TOKEN('NO', p_formula_detail_rec.formula_no);
1731: FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
1732: FND_MESSAGE.SET_TOKEN('STATUS',
1733: GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
1734: FND_MSG_PUB.Add;
1735: RAISE FND_API.G_EXC_ERROR;
1736: END IF;
1737:
1738: /* ================================================= */
1748: FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_PRODUCT');
1749: ELSE
1750: FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_INGREDIENT');
1751: END IF;
1752: FND_MSG_PUB.Add;
1753: RAISE FND_API.G_EXC_ERROR;
1754: END IF;
1755: CLOSE check_num_details;
1756:
1762: FETCH check_validity_rules INTO l_count;
1763: /* If there are valid validity rules, stop the delete */
1764: IF (check_validity_rules%FOUND) THEN
1765: FND_MESSAGE.SET_NAME('GMD', 'GMD_VALID_VALIDITY');
1766: FND_MSG_PUB.Add;
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769: CLOSE check_validity_rules;
1770: END IF;
1833: Commit;
1834: END IF;
1835:
1836: /* Get the message count and information */
1837: FND_MSG_PUB.Count_And_Get (
1838: p_count => x_msg_count,
1839: p_data => x_msg_data );
1840: EXCEPTION
1841: WHEN FND_API.G_EXC_ERROR THEN
1840: EXCEPTION
1841: WHEN FND_API.G_EXC_ERROR THEN
1842: ROLLBACK to Delete_FormulaDetail;
1843: x_return_status := FND_API.G_RET_STS_ERROR;
1844: FND_MSG_PUB.Count_And_Get (
1845: p_count => x_msg_count,
1846: p_data => x_msg_data );
1847:
1848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1847:
1848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849: ROLLBACK to Delete_FormulaDetail;
1850: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851: FND_MSG_PUB.Count_And_Get (
1852: p_count => x_msg_count,
1853: p_data => x_msg_data );
1854:
1855: WHEN OTHERS THEN
1853: p_data => x_msg_data );
1854:
1855: WHEN OTHERS THEN
1856: ROLLBACK to Delete_FormulaDetail;
1857: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1858: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859: FND_MSG_PUB.Count_And_Get (
1860: p_count => x_msg_count,
1861: p_data => x_msg_data );
1855: WHEN OTHERS THEN
1856: ROLLBACK to Delete_FormulaDetail;
1857: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1858: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859: FND_MSG_PUB.Count_And_Get (
1860: p_count => x_msg_count,
1861: p_data => x_msg_data );
1862:
1863: END Delete_FormulaDetail;