[Home] [Help]
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
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
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
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
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(' ');
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
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:
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;
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:
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 */
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
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;
336: IF (NVL(p_called_from_forms,'NO') = 'NO') THEN
337: GMDFMVAL_PUB.validate_insert_record (P_formula_dtl => P_formula_detail_rec,
338: X_formula_dtl => X_formula_detail_rec,
339: xReturn => X_return_status);
340: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
341: RAISE FND_API.G_EXC_ERROR;
342: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344: END IF;
337: GMDFMVAL_PUB.validate_insert_record (P_formula_dtl => P_formula_detail_rec,
338: X_formula_dtl => X_formula_detail_rec,
339: xReturn => X_return_status);
340: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
341: RAISE FND_API.G_EXC_ERROR;
342: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344: END IF;
345: ELSE
338: X_formula_dtl => X_formula_detail_rec,
339: xReturn => X_return_status);
340: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
341: RAISE FND_API.G_EXC_ERROR;
342: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344: END IF;
345: ELSE
346: X_formula_detail_rec := P_formula_detail_rec;
339: xReturn => X_return_status);
340: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
341: RAISE FND_API.G_EXC_ERROR;
342: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344: END IF;
345: ELSE
346: X_formula_detail_rec := P_formula_detail_rec;
347: END IF;
362: AND p_formula_detail_rec.scale_type_dtl =1 AND p_formula_detail_rec.prod_percent IS NULL THEN
363: /* Error to be raised for Proportional Products when Percentages are not passed */
364: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_YES');
365: FND_MSG_PUB.Add;
366: RAISE FND_API.G_EXC_ERROR;
367: ELSIF l_formula_calc_flag = 'Y' AND (p_formula_detail_rec.prod_percent IS NOT NULL )
368: AND ( p_formula_detail_rec.line_type IN (-1,2) OR
369: ( p_formula_detail_rec.line_type = 1 AND p_formula_detail_rec.scale_type_dtl = 0 ) ) THEN
370: /* Error to be raised when Percentages are passed for Ingredients/By-Products or Fixed
370: /* Error to be raised when Percentages are passed for Ingredients/By-Products or Fixed
371: Products */
372: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_CANNOT');
373: FND_MSG_PUB.Add;
374: RAISE FND_API.G_EXC_ERROR;
375: END IF;
376: END IF;
377:
378: IF ( ( l_auto_calc IS NULL OR l_auto_calc = 'N') OR (l_formula_calc_flag IS NULL OR l_formula_calc_flag = 'N' ))
380: /* Error to be raised when Percentages are passed when Parameter is not Set tp
381: Calculate Product qty */
382: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGE_NO');
383: FND_MSG_PUB.Add;
384: RAISE FND_API.G_EXC_ERROR;
385: END IF;
386: -- Kapil ME Auto-Prod :Bug#5716318
387:
388: /* Assigning values to formula detail rec and passing to the private API */
385: END IF;
386: -- Kapil ME Auto-Prod :Bug#5716318
387:
388: /* Assigning values to formula detail rec and passing to the private API */
389: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
390: l_fm_matl_dtl_rec.formulaline_id := l_surrogate;
391: l_fm_matl_dtl_rec.formula_id := l_formula_id;
392: l_fm_matl_dtl_rec.line_type := p_formula_detail_rec.line_type;
393: l_fm_matl_dtl_rec.line_no := p_formula_detail_rec.line_no;
464: END IF;
465: GMD_FORMULA_DETAIL_PVT.Insert_FormulaDetail
466: ( p_api_version => p_api_version
467: ,p_init_msg_list => p_init_msg_list
468: ,p_commit => FND_API.G_FALSE
469: ,x_return_status => x_return_status
470: ,x_msg_count => x_msg_count
471: ,x_msg_data => x_msg_data
472: ,p_formula_detail_rec => l_fm_matl_dtl_rec
491: END IF;
492:
493: /* IF creation of a line fails - Raise an exception
494: rather than trying to insert other lines */
495: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
496: RAISE FND_API.G_EXC_ERROR;
497: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
498: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499: END IF;
492:
493: /* IF creation of a line fails - Raise an exception
494: rather than trying to insert other lines */
495: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
496: RAISE FND_API.G_EXC_ERROR;
497: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
498: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499: END IF;
500:
493: /* IF creation of a line fails - Raise an exception
494: rather than trying to insert other lines */
495: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
496: RAISE FND_API.G_EXC_ERROR;
497: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
498: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499: END IF;
500:
501: END LOOP; -- for number of lines to be inserted
494: rather than trying to insert other lines */
495: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
496: RAISE FND_API.G_EXC_ERROR;
497: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
498: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499: END IF;
500:
501: END LOOP; -- for number of lines to be inserted
502:
501: END LOOP; -- for number of lines to be inserted
502:
503: /* End of API body */
504:
505: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
506: (FND_API.To_Boolean(p_commit)) THEN
507: /* Check if p_commit is set to TRUE */
508: Commit;
509: END IF;
502:
503: /* End of API body */
504:
505: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
506: (FND_API.To_Boolean(p_commit)) THEN
507: /* Check if p_commit is set to TRUE */
508: Commit;
509: END IF;
510:
513: p_count => x_msg_count,
514: p_data => x_msg_data );
515:
516: EXCEPTION
517: WHEN FND_API.G_EXC_ERROR THEN
518: ROLLBACK to Insert_FormulaDetail;
519: x_return_status := FND_API.G_RET_STS_ERROR;
520: FND_MSG_PUB.Count_And_Get (
521: p_count => x_msg_count,
515:
516: EXCEPTION
517: WHEN FND_API.G_EXC_ERROR THEN
518: ROLLBACK to Insert_FormulaDetail;
519: x_return_status := FND_API.G_RET_STS_ERROR;
520: FND_MSG_PUB.Count_And_Get (
521: p_count => x_msg_count,
522: p_data => x_msg_data );
523: IF (l_debug = 'Y') THEN
525: ||' - '
526: ||x_return_status);
527: END IF;
528:
529: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530: ROLLBACK to Insert_FormulaDetail;
531: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532: FND_MSG_PUB.Count_And_Get (
533: p_count => x_msg_count,
527: END IF;
528:
529: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530: ROLLBACK to Insert_FormulaDetail;
531: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532: FND_MSG_PUB.Count_And_Get (
533: p_count => x_msg_count,
534: p_data => x_msg_data );
535: IF (l_debug = 'Y') THEN
540:
541: WHEN OTHERS THEN
542: ROLLBACK to Insert_FormulaDetail;
543: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
544: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545: FND_MSG_PUB.Count_And_Get (
546: p_count => x_msg_count,
547: p_data => x_msg_data );
548: IF (l_debug = 'Y') THEN
676: /* Define Savepoint */
677: SAVEPOINT Update_FormulaDetail;
678:
679: /* Standard Check for API compatibility */
680: IF NOT FND_API.Compatible_API_Call ( l_api_version
681: ,p_api_version
682: ,l_api_name
683: ,G_PKG_NAME )
684: THEN
681: ,p_api_version
682: ,l_api_name
683: ,G_PKG_NAME )
684: THEN
685: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686: END IF;
687:
688: /* Set the formula validation pkg variable GMDFMVAL_PUB */
689: /* variable p_called_from_form same as that passed in. */
694:
695: GMDFMVAL_PUB.p_called_from_forms := p_called_from_forms;
696:
697: /* Initialize message list if p_init_msg_list is set to TRUE */
698: IF FND_API.to_Boolean( p_init_msg_list ) THEN
699: FND_MSG_PUB.initialize;
700: END IF;
701:
702: /* 1. Does validation */
701:
702: /* 1. Does validation */
703: /* 2. Call the private API that does the database updates */
704: IF (p_formula_detail_tbl.count = 0) THEN
705: RAISE FND_API.G_EXC_ERROR;
706: END IF;
707:
708: FOR i IN 1 .. p_formula_detail_tbl.count LOOP
709:
707:
708: FOR i IN 1 .. p_formula_detail_tbl.count LOOP
709:
710: /* Initialize API return status to success */
711: x_return_status := FND_API.G_RET_STS_SUCCESS;
712:
713: IF (l_debug = 'Y') THEN
714: gmd_debug.put_line(' In Formula Detail Update Pub - Entering loop with row # '||i);
715: END IF;
741: IF (l_return_val <> 0) THEN
742: IF (p_formula_detail_rec.formula_no IS NULL) THEN
743: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_NO');
744: FND_MSG_PUB.Add;
745: RAISE FND_API.G_EXC_ERROR;
746: ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
747: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
748: FND_MSG_PUB.Add;
749: RAISE FND_API.G_EXC_ERROR;
745: RAISE FND_API.G_EXC_ERROR;
746: ELSIF (p_formula_detail_rec.formula_vers IS NULL) THEN
747: FND_MESSAGE.SET_NAME('GMD', 'FM_MISSING_FORMULA_VERS');
748: FND_MSG_PUB.Add;
749: RAISE FND_API.G_EXC_ERROR;
750: ELSE
751: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
752: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
753: FND_MSG_PUB.Add;
750: ELSE
751: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
752: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
753: FND_MSG_PUB.Add;
754: RAISE FND_API.G_EXC_ERROR;
755: END IF;
756: END IF;
757: ELSE
758: l_formula_id := p_formula_detail_rec.formula_id;
771: FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
772: FND_MESSAGE.SET_TOKEN('STATUS',
773: GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
774: FND_MSG_PUB.Add;
775: RAISE FND_API.G_EXC_ERROR;
776: END IF;
777:
778: /* Check if there is a valid userid */
779: IF (l_debug = 'Y') THEN
784: l_user_id := FND_GLOBAL.user_id;
785: IF (l_user_id IS NULL) THEN
786: FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
787: FND_MSG_PUB.Add;
788: RAISE FND_API.G_EXC_ERROR;
789: END IF;
790:
791: /* ========================================= */
792: /* Ensure that the formulaline id exists */
800: GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
801: IF (l_return_val <> 0) THEN
802: FND_MESSAGE.SET_NAME('GMD','FM_INVALID_FMLINE_ID');
803: FND_MSG_PUB.Add;
804: RAISE FND_API.G_EXC_ERROR;
805: END IF;
806: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN /* missing formula no */
807: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
808: FND_MSG_PUB.Add;
805: END IF;
806: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN /* missing formula no */
807: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
808: FND_MSG_PUB.Add;
809: RAISE FND_API.G_EXC_ERROR;
810: END IF;
811:
812: -- Bug# 5554631 KMOTUPAL - TO prevent Update of Item Revision
813: IF (p_formula_detail_rec.revision IS NOT NULL) THEN
946: /* Error to be raised if Percentages are passed for Ingredients/By-Products or when
947: when the Parameter is not set to calculate Product Qty */
948: FND_MESSAGE.SET_NAME('GMD', 'GMD_ENTER_PERCENTAGES_NOT');
949: FND_MSG_PUB.Add;
950: RAISE FND_API.G_EXC_ERROR;
951: END IF;
952: END IF;
953: -- Kapil ME Auto-Prod :Bug#5716318
954:
964: IF (NVL(p_called_from_forms,'NO') = 'NO') THEN
965: GMDFMVAL_PUB.validate_update_record (P_formula_dtl => P_formula_detail_rec,
966: X_formula_dtl => X_formula_detail_rec,
967: xReturn => X_return_status);
968: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
969: RAISE FND_API.G_EXC_ERROR;
970: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972: END IF;
965: GMDFMVAL_PUB.validate_update_record (P_formula_dtl => P_formula_detail_rec,
966: X_formula_dtl => X_formula_detail_rec,
967: xReturn => X_return_status);
968: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
969: RAISE FND_API.G_EXC_ERROR;
970: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972: END IF;
973: ELSE
966: X_formula_dtl => X_formula_detail_rec,
967: xReturn => X_return_status);
968: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
969: RAISE FND_API.G_EXC_ERROR;
970: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972: END IF;
973: ELSE
974: X_formula_detail_rec := P_formula_detail_rec;
967: xReturn => X_return_status);
968: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
969: RAISE FND_API.G_EXC_ERROR;
970: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972: END IF;
973: ELSE
974: X_formula_detail_rec := P_formula_detail_rec;
975: END IF; /* Validations end when not called from forms */
986: OPEN get_detail_rec(p_formula_detail_rec.formulaline_id);
987: FETCH get_detail_rec into fm_matl_dtl_rec;
988:
989: /* Shyam Sitaraman - Bug 2652200 */
990: /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
991: /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
992: /* as the user is intending to update the field to NULL */
993: IF (get_detail_rec%FOUND) THEN
994: /*Bug 2509076 - Thomas Daniel */
987: FETCH get_detail_rec into fm_matl_dtl_rec;
988:
989: /* Shyam Sitaraman - Bug 2652200 */
990: /* Reversed the handling of FND_API.G_MISS_CHAR, now if the user */
991: /* passes in FND_API.G_MISS_CHAR for an attribute it would be handled */
992: /* as the user is intending to update the field to NULL */
993: IF (get_detail_rec%FOUND) THEN
994: /*Bug 2509076 - Thomas Daniel */
995: /* QM Integration */
992: /* as the user is intending to update the field to NULL */
993: IF (get_detail_rec%FOUND) THEN
994: /*Bug 2509076 - Thomas Daniel */
995: /* QM Integration */
996: IF (p_formula_detail_rec.by_product_type = FND_API.G_MISS_CHAR) THEN
997: l_by_product_type := NULL;
998: ELSIF (p_formula_detail_rec.by_product_type IS NULL) THEN
999: l_by_product_type := fm_matl_dtl_rec.by_product_type;
1000: ELSE
1001: l_by_product_type := X_formula_detail_rec.by_product_type;
1002: END IF;
1003:
1004: /* Added some more - with FM API cleanup */
1005: IF (p_formula_detail_rec.rounding_direction = FND_API.G_MISS_NUM) THEN
1006: l_rounding_direction := NULL;
1007: ELSIF (p_formula_detail_rec.rounding_direction IS NULL) THEN
1008: l_rounding_direction := fm_matl_dtl_rec.rounding_direction;
1009: ELSE
1009: ELSE
1010: l_rounding_direction := p_formula_detail_rec.rounding_direction;
1011: END IF;
1012:
1013: IF (p_formula_detail_rec.text_code_dtl = FND_API.G_MISS_NUM) THEN
1014: l_text_code := NULL;
1015: ELSIF (p_formula_detail_rec.text_code_dtl IS NULL) THEN
1016: l_text_code := fm_matl_dtl_rec.text_code;
1017: ELSE
1017: ELSE
1018: l_text_code := p_formula_detail_rec.text_code_dtl;
1019: END IF;
1020:
1021: IF (p_formula_detail_rec.cost_alloc = FND_API.G_MISS_NUM) THEN
1022: l_cost_alloc := NULL;
1023: ELSIF (p_formula_detail_rec.cost_alloc IS NULL) THEN
1024: l_cost_alloc := fm_matl_dtl_rec.cost_alloc;
1025: ELSE
1025: ELSE
1026: l_cost_alloc := X_formula_detail_rec.cost_alloc;
1027: END IF;
1028:
1029: IF (p_formula_detail_rec.tpformula_id = FND_API.G_MISS_NUM) THEN
1030: l_tpformula_id := NULL;
1031: ELSIF (p_formula_detail_rec.tpformula_id IS NULL) THEN
1032: l_tpformula_id := fm_matl_dtl_rec.tpformula_id;
1033: ELSE
1033: ELSE
1034: l_tpformula_id := p_formula_detail_rec.tpformula_id;
1035: END IF;
1036:
1037: IF (p_formula_detail_rec.tpformula_id = FND_API.G_MISS_NUM) THEN
1038: l_tpformula_id := NULL;
1039: ELSIF (p_formula_detail_rec.tpformula_id IS NULL) THEN
1040: l_tpformula_id := fm_matl_dtl_rec.tpformula_id;
1041: ELSE
1041: ELSE
1042: l_tpformula_id := p_formula_detail_rec.tpformula_id;
1043: END IF;
1044:
1045: IF (p_formula_detail_rec.iaformula_id = FND_API.G_MISS_NUM) THEN
1046: l_iaformula_id := NULL;
1047: ELSIF (p_formula_detail_rec.iaformula_id IS NULL) THEN
1048: l_iaformula_id := fm_matl_dtl_rec.iaformula_id;
1049: ELSE
1049: ELSE
1050: l_iaformula_id := p_formula_detail_rec.iaformula_id;
1051: END IF;
1052:
1053: IF (p_formula_detail_rec.scale_multiple = FND_API.G_MISS_NUM) THEN
1054: l_scale_multiple := NULL;
1055: ELSIF (p_formula_detail_rec.scale_multiple IS NULL) THEN
1056: l_scale_multiple := fm_matl_dtl_rec.scale_multiple;
1057: ELSE
1057: ELSE
1058: l_scale_multiple := p_formula_detail_rec.scale_multiple;
1059: END IF;
1060:
1061: IF (p_formula_detail_rec.scale_rounding_variance = FND_API.G_MISS_NUM) THEN
1062: l_scale_rounding_variance := NULL;
1063: ELSIF (p_formula_detail_rec.scale_rounding_variance IS NULL) THEN
1064: l_scale_rounding_variance := fm_matl_dtl_rec.scale_rounding_variance;
1065: ELSE
1065: ELSE
1066: l_scale_rounding_variance := p_formula_detail_rec.scale_rounding_variance;
1067: END IF;
1068:
1069: IF (p_formula_detail_rec.contribute_yield_ind = FND_API.G_MISS_CHAR) THEN
1070: l_contribute_yield_ind := NULL;
1071: IF (l_debug = 'Y') THEN
1072: gmd_debug.put_line(' In Formula Header Pub - '
1073: ||' Cond 1');
1097: ||' - '
1098: ||x_return_status);
1099: END IF;
1100:
1101: IF (p_formula_detail_rec.scale_uom = FND_API.G_MISS_CHAR) THEN
1102: l_scale_uom := NULL;
1103: ELSIF (p_formula_detail_rec.scale_uom IS NULL) THEN
1104: l_scale_uom := fm_matl_dtl_rec.scale_uom;
1105: ELSE
1105: ELSE
1106: l_scale_uom := p_formula_detail_rec.scale_uom;
1107: END IF;
1108:
1109: IF (p_formula_detail_rec.contribute_step_qty_ind = FND_API.G_MISS_CHAR) THEN
1110: l_contribute_step_qty_ind := NULL;
1111: ELSIF (p_formula_detail_rec.contribute_step_qty_ind IS NULL) THEN
1112: l_contribute_step_qty_ind := fm_matl_dtl_rec.contribute_step_qty_ind;
1113: ELSE
1114: l_contribute_step_qty_ind := p_formula_detail_rec.contribute_step_qty_ind;
1115: END IF;
1116:
1117:
1118: IF (p_formula_detail_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1119: l_attribute_category := NULL;
1120: ELSIF (p_formula_detail_rec.attribute_category IS NULL) THEN
1121: l_attribute_category := fm_matl_dtl_rec.attribute_category;
1122: ELSE
1122: ELSE
1123: l_attribute_category := p_formula_detail_rec.attribute_category;
1124: END IF;
1125:
1126: IF (p_formula_detail_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1127: l_attribute1 := NULL;
1128: ELSIF (p_formula_detail_rec.attribute1 IS NULL) THEN
1129: l_attribute1 := fm_matl_dtl_rec.attribute1;
1130: ELSE
1130: ELSE
1131: l_attribute1 := p_formula_detail_rec.attribute1;
1132: END IF;
1133:
1134: IF (p_formula_detail_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1135: l_attribute2 := NULL;
1136: ELSIF (p_formula_detail_rec.attribute2 IS NULL) THEN
1137: l_attribute2 := fm_matl_dtl_rec.attribute2;
1138: ELSE
1138: ELSE
1139: l_attribute2 := p_formula_detail_rec.attribute2;
1140: END IF;
1141:
1142: IF (p_formula_detail_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1143: l_attribute3 := NULL;
1144: ELSIF (p_formula_detail_rec.attribute3 IS NULL) THEN
1145: l_attribute3 := fm_matl_dtl_rec.attribute3;
1146: ELSE
1146: ELSE
1147: l_attribute3 := p_formula_detail_rec.attribute3;
1148: END IF;
1149:
1150: IF (p_formula_detail_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1151: l_attribute4 := NULL;
1152: ELSIF (p_formula_detail_rec.attribute4 IS NULL) THEN
1153: l_attribute4 := fm_matl_dtl_rec.attribute4;
1154: ELSE
1154: ELSE
1155: l_attribute4 := p_formula_detail_rec.attribute4;
1156: END IF;
1157:
1158: IF (p_formula_detail_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1159: l_attribute5 := NULL;
1160: ELSIF (p_formula_detail_rec.attribute5 IS NULL) THEN
1161: l_attribute5 := fm_matl_dtl_rec.attribute5;
1162: ELSE
1162: ELSE
1163: l_attribute5 := p_formula_detail_rec.attribute5;
1164: END IF;
1165:
1166: IF (p_formula_detail_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1167: l_attribute6 := NULL;
1168: ELSIF (p_formula_detail_rec.attribute6 IS NULL) THEN
1169: l_attribute6 := fm_matl_dtl_rec.attribute6;
1170: ELSE
1170: ELSE
1171: l_attribute6 := p_formula_detail_rec.attribute6;
1172: END IF;
1173:
1174: IF (p_formula_detail_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1175: l_attribute7 := NULL;
1176: ELSIF (p_formula_detail_rec.attribute7 IS NULL) THEN
1177: l_attribute7 := fm_matl_dtl_rec.attribute7;
1178: ELSE
1178: ELSE
1179: l_attribute7 := p_formula_detail_rec.attribute7;
1180: END IF;
1181:
1182: IF (p_formula_detail_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1183: l_attribute8 := NULL;
1184: ELSIF (p_formula_detail_rec.attribute8 IS NULL) THEN
1185: l_attribute8 := fm_matl_dtl_rec.attribute8;
1186: ELSE
1186: ELSE
1187: l_attribute8 := p_formula_detail_rec.attribute8;
1188: END IF;
1189:
1190: IF (p_formula_detail_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1191: l_attribute9 := NULL;
1192: ELSIF (p_formula_detail_rec.attribute9 IS NULL) THEN
1193: l_attribute9 := fm_matl_dtl_rec.attribute9;
1194: ELSE
1194: ELSE
1195: l_attribute9 := p_formula_detail_rec.attribute9;
1196: END IF;
1197:
1198: IF (p_formula_detail_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1199: l_attribute10 := NULL;
1200: ELSIF (p_formula_detail_rec.attribute10 IS NULL) THEN
1201: l_attribute10 := fm_matl_dtl_rec.attribute10;
1202: ELSE
1202: ELSE
1203: l_attribute10 := p_formula_detail_rec.attribute10;
1204: END IF;
1205:
1206: IF (p_formula_detail_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1207: l_attribute11 := NULL;
1208: ELSIF (p_formula_detail_rec.attribute11 IS NULL) THEN
1209: l_attribute11 := fm_matl_dtl_rec.attribute11;
1210: ELSE
1210: ELSE
1211: l_attribute11 := p_formula_detail_rec.attribute11;
1212: END IF;
1213:
1214: IF (p_formula_detail_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1215: l_attribute12 := NULL;
1216: ELSIF (p_formula_detail_rec.attribute2 IS NULL) THEN
1217: l_attribute12 := fm_matl_dtl_rec.attribute12;
1218: ELSE
1218: ELSE
1219: l_attribute12 := p_formula_detail_rec.attribute12;
1220: END IF;
1221:
1222: IF (p_formula_detail_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1223: l_attribute13 := NULL;
1224: ELSIF (p_formula_detail_rec.attribute13 IS NULL) THEN
1225: l_attribute13 := fm_matl_dtl_rec.attribute13;
1226: ELSE
1226: ELSE
1227: l_attribute13 := p_formula_detail_rec.attribute13;
1228: END IF;
1229:
1230: IF (p_formula_detail_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1231: l_attribute14 := NULL;
1232: ELSIF (p_formula_detail_rec.attribute14 IS NULL) THEN
1233: l_attribute14 := fm_matl_dtl_rec.attribute14;
1234: ELSE
1234: ELSE
1235: l_attribute14 := p_formula_detail_rec.attribute14;
1236: END IF;
1237:
1238: IF (p_formula_detail_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1239: l_attribute15 := NULL;
1240: ELSIF (p_formula_detail_rec.attribute15 IS NULL) THEN
1241: l_attribute15 := fm_matl_dtl_rec.attribute15;
1242: ELSE
1242: ELSE
1243: l_attribute15 := p_formula_detail_rec.attribute15;
1244: END IF;
1245:
1246: IF (p_formula_detail_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1247: l_attribute16 := NULL;
1248: ELSIF (p_formula_detail_rec.attribute16 IS NULL) THEN
1249: l_attribute16 := fm_matl_dtl_rec.attribute16;
1250: ELSE
1250: ELSE
1251: l_attribute16 := p_formula_detail_rec.attribute16;
1252: END IF;
1253:
1254: IF (p_formula_detail_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1255: l_attribute17 := NULL;
1256: ELSIF (p_formula_detail_rec.attribute17 IS NULL) THEN
1257: l_attribute17 := fm_matl_dtl_rec.attribute17;
1258: ELSE
1258: ELSE
1259: l_attribute17 := p_formula_detail_rec.attribute17;
1260: END IF;
1261:
1262: IF (p_formula_detail_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1263: l_attribute18 := NULL;
1264: ELSIF (p_formula_detail_rec.attribute18 IS NULL) THEN
1265: l_attribute18 := fm_matl_dtl_rec.attribute18;
1266: ELSE
1266: ELSE
1267: l_attribute18 := p_formula_detail_rec.attribute18;
1268: END IF;
1269:
1270: IF (p_formula_detail_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1271: l_attribute19 := NULL;
1272: ELSIF (p_formula_detail_rec.attribute19 IS NULL) THEN
1273: l_attribute19 := fm_matl_dtl_rec.attribute19;
1274: ELSE
1274: ELSE
1275: l_attribute19 := p_formula_detail_rec.attribute19;
1276: END IF;
1277:
1278: IF (p_formula_detail_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1279: l_attribute20 := NULL;
1280: ELSIF (p_formula_detail_rec.attribute20 IS NULL) THEN
1281: l_attribute20 := fm_matl_dtl_rec.attribute20;
1282: ELSE
1282: ELSE
1283: l_attribute20 := p_formula_detail_rec.attribute20;
1284: END IF;
1285:
1286: IF (p_formula_detail_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1287: l_attribute21 := NULL;
1288: ELSIF (p_formula_detail_rec.attribute21 IS NULL) THEN
1289: l_attribute21 := fm_matl_dtl_rec.attribute21;
1290: ELSE
1290: ELSE
1291: l_attribute21 := p_formula_detail_rec.attribute21;
1292: END IF;
1293:
1294: IF (p_formula_detail_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1295: l_attribute22 := NULL;
1296: ELSIF (p_formula_detail_rec.attribute22 IS NULL) THEN
1297: l_attribute22 := fm_matl_dtl_rec.attribute22;
1298: ELSE
1298: ELSE
1299: l_attribute22 := p_formula_detail_rec.attribute22;
1300: END IF;
1301:
1302: IF (p_formula_detail_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1303: l_attribute23 := NULL;
1304: ELSIF (p_formula_detail_rec.attribute23 IS NULL) THEN
1305: l_attribute23 := fm_matl_dtl_rec.attribute23;
1306: ELSE
1306: ELSE
1307: l_attribute23 := p_formula_detail_rec.attribute23;
1308: END IF;
1309:
1310: IF (p_formula_detail_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1311: l_attribute24 := NULL;
1312: ELSIF (p_formula_detail_rec.attribute24 IS NULL) THEN
1313: l_attribute24 := fm_matl_dtl_rec.attribute24;
1314: ELSE
1314: ELSE
1315: l_attribute24 := p_formula_detail_rec.attribute24;
1316: END IF;
1317:
1318: IF (p_formula_detail_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1319: l_attribute25 := NULL;
1320: ELSIF (p_formula_detail_rec.attribute25 IS NULL) THEN
1321: l_attribute25 := fm_matl_dtl_rec.attribute25;
1322: ELSE
1322: ELSE
1323: l_attribute25 := p_formula_detail_rec.attribute25;
1324: END IF;
1325:
1326: IF (p_formula_detail_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1327: l_attribute26 := NULL;
1328: ELSIF (p_formula_detail_rec.attribute26 IS NULL) THEN
1329: l_attribute26 := fm_matl_dtl_rec.attribute26;
1330: ELSE
1330: ELSE
1331: l_attribute26 := p_formula_detail_rec.attribute26;
1332: END IF;
1333:
1334: IF (p_formula_detail_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1335: l_attribute27 := NULL;
1336: ELSIF (p_formula_detail_rec.attribute27 IS NULL) THEN
1337: l_attribute27 := fm_matl_dtl_rec.attribute27;
1338: ELSE
1338: ELSE
1339: l_attribute27 := p_formula_detail_rec.attribute27;
1340: END IF;
1341:
1342: IF (p_formula_detail_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1343: l_attribute28 := NULL;
1344: ELSIF (p_formula_detail_rec.attribute28 IS NULL) THEN
1345: l_attribute28 := fm_matl_dtl_rec.attribute28;
1346: ELSE
1346: ELSE
1347: l_attribute28 := p_formula_detail_rec.attribute28;
1348: END IF;
1349:
1350: IF (p_formula_detail_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1351: l_attribute29 := NULL;
1352: ELSIF (p_formula_detail_rec.attribute29 IS NULL) THEN
1353: l_attribute29 := fm_matl_dtl_rec.attribute29;
1354: ELSE
1354: ELSE
1355: l_attribute29 := p_formula_detail_rec.attribute29;
1356: END IF;
1357:
1358: IF (p_formula_detail_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1359: l_attribute30 := NULL;
1360: ELSIF (p_formula_detail_rec.attribute30 IS NULL) THEN
1361: l_attribute30 := fm_matl_dtl_rec.attribute30;
1362: ELSE
1362: ELSE
1363: l_attribute30 := p_formula_detail_rec.attribute30;
1364: END IF;
1365: --Bug 4479101
1366: IF (p_formula_detail_rec.ingredient_end_date = FND_API.G_MISS_DATE) THEN
1367: l_ingredient_end_date := NULL;
1368: ELSIF (p_formula_detail_rec.ingredient_end_date IS NULL) THEN
1369: l_ingredient_end_date := fm_matl_dtl_rec.ingredient_end_date;
1370: ELSE
1381: ||' Assigning values prior to pvt API call '
1382: ||' - '
1383: ||x_return_status);
1384: END IF;
1385: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1386: l_fm_matl_dtl_rec.formulaline_id := p_formula_detail_rec.formulaline_id;
1387: l_fm_matl_dtl_rec.formula_id := l_formula_id;
1388: l_fm_matl_dtl_rec.line_type := p_formula_detail_rec.line_type;
1389: l_fm_matl_dtl_rec.line_no := p_formula_detail_rec.line_no;
1458: END IF;
1459: GMD_FORMULA_DETAIL_PVT.Update_FormulaDetail
1460: ( p_api_version => 1.0
1461: ,p_init_msg_list => p_init_msg_list
1462: ,p_commit => FND_API.G_FALSE
1463: ,x_return_status => x_return_status
1464: ,x_msg_count => x_msg_count
1465: ,x_msg_data => x_msg_data
1466: ,p_formula_detail_rec => l_fm_matl_dtl_rec
1488: -- Kapil ME Auto-Prod :Bug#5716318
1489:
1490: /* IF update of a line fails - Raise an exception
1491: rather than trying to insert other lines */
1492: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1493: RAISE FND_API.G_EXC_ERROR;
1494: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496: END IF;
1489:
1490: /* IF update of a line fails - Raise an exception
1491: rather than trying to insert other lines */
1492: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1493: RAISE FND_API.G_EXC_ERROR;
1494: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496: END IF;
1497:
1490: /* IF update of a line fails - Raise an exception
1491: rather than trying to insert other lines */
1492: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1493: RAISE FND_API.G_EXC_ERROR;
1494: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496: END IF;
1497:
1498: END LOOP; -- End of main update loop
1491: rather than trying to insert other lines */
1492: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1493: RAISE FND_API.G_EXC_ERROR;
1494: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496: END IF;
1497:
1498: END LOOP; -- End of main update loop
1499:
1497:
1498: END LOOP; -- End of main update loop
1499:
1500: /* Check if p_commit is set to TRUE */
1501: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1502: (FND_API.To_Boolean( p_commit ) ) THEN
1503: Commit;
1504: END IF;
1505:
1498: END LOOP; -- End of main update loop
1499:
1500: /* Check if p_commit is set to TRUE */
1501: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1502: (FND_API.To_Boolean( p_commit ) ) THEN
1503: Commit;
1504: END IF;
1505:
1506: /* Get the message count and information */
1507: FND_MSG_PUB.Count_And_Get (
1508: p_count => x_msg_count,
1509: p_data => x_msg_data );
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1512: ROLLBACK to Update_FormulaDetail;
1513: x_return_status := FND_API.G_RET_STS_ERROR;
1514: FND_MSG_PUB.Count_And_Get (
1515: p_count => x_msg_count,
1509: p_data => x_msg_data );
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1512: ROLLBACK to Update_FormulaDetail;
1513: x_return_status := FND_API.G_RET_STS_ERROR;
1514: FND_MSG_PUB.Count_And_Get (
1515: p_count => x_msg_count,
1516: p_data => x_msg_data );
1517: IF (l_debug = 'Y') THEN
1518: gmd_debug.put_line(' In Formula Detail pub - In error Exception Section '
1519: ||' - '
1520: ||x_return_status);
1521: END IF;
1522: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523: ROLLBACK to Update_FormulaDetail;
1524: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525: FND_MSG_PUB.Count_And_Get (
1526: p_count => x_msg_count,
1520: ||x_return_status);
1521: END IF;
1522: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523: ROLLBACK to Update_FormulaDetail;
1524: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525: FND_MSG_PUB.Count_And_Get (
1526: p_count => x_msg_count,
1527: p_data => x_msg_data );
1528: IF (l_debug = 'Y') THEN
1532: END IF;
1533: WHEN OTHERS THEN
1534: ROLLBACK to Update_FormulaDetail;
1535: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1536: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537: FND_MSG_PUB.Count_And_Get (
1538: p_count => x_msg_count,
1539: p_data => x_msg_data );
1540: IF (l_debug = 'Y') THEN
1630: /* Define Savepoint */
1631: SAVEPOINT Delete_FormulaDetail;
1632:
1633: /* Standard Check for API compatibility */
1634: IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1635: p_api_version ,
1636: l_api_name ,
1637: G_PKG_NAME )
1638: THEN
1635: p_api_version ,
1636: l_api_name ,
1637: G_PKG_NAME )
1638: THEN
1639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640: END IF;
1641:
1642: /* Initialize message list if p_init_msg_list is set to TRUE */
1643: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640: END IF;
1641:
1642: /* Initialize message list if p_init_msg_list is set to TRUE */
1643: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644: FND_MSG_PUB.initialize;
1645: END IF;
1646: /* Initialize API return status to success */
1647: x_return_status := FND_API.G_RET_STS_SUCCESS;
1643: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644: FND_MSG_PUB.initialize;
1645: END IF;
1646: /* Initialize API return status to success */
1647: x_return_status := FND_API.G_RET_STS_SUCCESS;
1648:
1649: /* API body */
1650: /* 1. Does minimum validation */
1651: /* 2. Call the private API that does the database inserts/ updates */
1650: /* 1. Does minimum validation */
1651: /* 2. Call the private API that does the database inserts/ updates */
1652:
1653: IF (p_formula_detail_tbl.count = 0) THEN
1654: RAISE FND_API.G_EXC_ERROR;
1655: END IF;
1656:
1657: FOR i in 1 .. p_formula_detail_tbl.count LOOP
1658:
1655: END IF;
1656:
1657: FOR i in 1 .. p_formula_detail_tbl.count LOOP
1658:
1659: x_return_status := FND_API.G_RET_STS_SUCCESS;
1660:
1661: p_formula_detail_rec := p_formula_detail_tbl(i);
1662:
1663: /* Check if there is a valid userid */
1665: l_user_id := FND_GLOBAL.user_id;
1666: IF (l_user_id IS NULL) THEN
1667: FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_CONTEXT_NOT_SET');
1668: FND_MSG_PUB.Add;
1669: RAISE FND_API.G_EXC_ERROR;
1670: END IF;
1671:
1672: /* =================================== */
1673: /* Check if an appropriate action_code */
1677: FND_MESSAGE.SET_NAME('GMD', 'FM_INVALID_ACTION');
1678: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1679: FND_MESSAGE.SET_TOKEN('FORMULA_VERS', p_formula_detail_rec.formula_vers);
1680: FND_MSG_PUB.Add;
1681: RAISE FND_API.G_EXC_ERROR;
1682: END IF;
1683:
1684:
1685: /* ============================ */
1691: GMDFMVAL_PUB.get_formulaline_id(p_formula_detail_rec.formulaline_id,l_return_val);
1692: IF (l_return_val <> 0) THEN
1693: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1694: FND_MSG_PUB.Add;
1695: RAISE FND_API.G_EXC_ERROR;
1696: END IF;
1697: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN
1698: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1699: FND_MSG_PUB.Add;
1696: END IF;
1697: ELSIF (p_formula_detail_rec.formulaline_id IS NULL) THEN
1698: FND_MESSAGE.SET_NAME('GMD','FM_MISSING_FMLINE_ID');
1699: FND_MSG_PUB.Add;
1700: RAISE FND_API.G_EXC_ERROR;
1701: END IF;
1702:
1703: /* ======================== */
1704: /* Get the formula id */
1715: FND_MESSAGE.SET_NAME('GMD', 'FM_INVFORMULANO');
1716: FND_MESSAGE.SET_TOKEN('FORMULA_NO', p_formula_detail_rec.formula_no);
1717: FND_MSG_PUB.Add;
1718: CLOSE get_formula_id;
1719: RAISE FND_API.G_EXC_ERROR;
1720: END IF;
1721: CLOSE get_formula_id;
1722: ELSE
1723: l_formula_id := p_formula_detail_rec.formula_id;
1731: FND_MESSAGE.SET_TOKEN('VERS', p_formula_detail_rec.formula_vers);
1732: FND_MESSAGE.SET_TOKEN('STATUS',
1733: GMD_FORMULA_DETAIL_PUB.get_fm_status_meaning(l_formula_id));
1734: FND_MSG_PUB.Add;
1735: RAISE FND_API.G_EXC_ERROR;
1736: END IF;
1737:
1738: /* ================================================= */
1739: /* Checked if only 1 line if an ingredient or product */
1749: ELSE
1750: FND_MESSAGE.SET_NAME('GMD', 'GMD_MUST_HAVE_INGREDIENT');
1751: END IF;
1752: FND_MSG_PUB.Add;
1753: RAISE FND_API.G_EXC_ERROR;
1754: END IF;
1755: CLOSE check_num_details;
1756:
1757: /* ================================================= */
1763: /* If there are valid validity rules, stop the delete */
1764: IF (check_validity_rules%FOUND) THEN
1765: FND_MESSAGE.SET_NAME('GMD', 'GMD_VALID_VALIDITY');
1766: FND_MSG_PUB.Add;
1767: RAISE FND_API.G_EXC_ERROR;
1768: END IF;
1769: CLOSE check_validity_rules;
1770: END IF;
1771: END IF; /* If not called by a form */
1771: END IF; /* If not called by a form */
1772: END IF; /* If this is an ingredient or product */
1773:
1774: /* Call for private API */
1775: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1776:
1777: l_fm_matl_dtl_rec.formulaline_id := p_formula_detail_rec.formulaline_id;
1778: l_fm_matl_dtl_rec.formula_id := l_formula_id;
1779:
1784:
1785: GMD_FORMULA_DETAIL_PVT.Delete_FormulaDetail
1786: ( p_api_version => p_api_version
1787: ,p_init_msg_list => p_init_msg_list
1788: ,p_commit => FND_API.G_FALSE
1789: ,x_return_status => x_return_status
1790: ,x_msg_count => x_msg_count
1791: ,x_msg_data => x_msg_data
1792: ,p_formula_detail_rec => l_fm_matl_dtl_rec
1818:
1819: /* End of API body */
1820: /* IF delete of a line fails - Raise an exception
1821: rather than trying to deleting other lines */
1822: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1823: RAISE FND_API.G_EXC_ERROR;
1824: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1825: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826: END IF;
1819: /* End of API body */
1820: /* IF delete of a line fails - Raise an exception
1821: rather than trying to deleting other lines */
1822: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1823: RAISE FND_API.G_EXC_ERROR;
1824: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1825: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826: END IF;
1827:
1820: /* IF delete of a line fails - Raise an exception
1821: rather than trying to deleting other lines */
1822: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1823: RAISE FND_API.G_EXC_ERROR;
1824: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1825: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826: END IF;
1827:
1828: END LOOP; -- End of main delete loop
1821: rather than trying to deleting other lines */
1822: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1823: RAISE FND_API.G_EXC_ERROR;
1824: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1825: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826: END IF;
1827:
1828: END LOOP; -- End of main delete loop
1829:
1827:
1828: END LOOP; -- End of main delete loop
1829:
1830: /* Check if p_commit is set to TRUE */
1831: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1832: (FND_API.To_Boolean( p_commit ) ) THEN
1833: Commit;
1834: END IF;
1835:
1828: END LOOP; -- End of main delete loop
1829:
1830: /* Check if p_commit is set to TRUE */
1831: IF x_return_status IN (FND_API.G_RET_STS_SUCCESS,'Q') AND
1832: (FND_API.To_Boolean( p_commit ) ) THEN
1833: Commit;
1834: END IF;
1835:
1836: /* Get the message count and information */
1837: FND_MSG_PUB.Count_And_Get (
1838: p_count => x_msg_count,
1839: p_data => x_msg_data );
1840: EXCEPTION
1841: WHEN FND_API.G_EXC_ERROR THEN
1842: ROLLBACK to Delete_FormulaDetail;
1843: x_return_status := FND_API.G_RET_STS_ERROR;
1844: FND_MSG_PUB.Count_And_Get (
1845: p_count => x_msg_count,
1839: p_data => x_msg_data );
1840: EXCEPTION
1841: WHEN FND_API.G_EXC_ERROR THEN
1842: ROLLBACK to Delete_FormulaDetail;
1843: x_return_status := FND_API.G_RET_STS_ERROR;
1844: FND_MSG_PUB.Count_And_Get (
1845: p_count => x_msg_count,
1846: p_data => x_msg_data );
1847:
1844: FND_MSG_PUB.Count_And_Get (
1845: p_count => x_msg_count,
1846: p_data => x_msg_data );
1847:
1848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849: ROLLBACK to Delete_FormulaDetail;
1850: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851: FND_MSG_PUB.Count_And_Get (
1852: p_count => x_msg_count,
1846: p_data => x_msg_data );
1847:
1848: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849: ROLLBACK to Delete_FormulaDetail;
1850: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851: FND_MSG_PUB.Count_And_Get (
1852: p_count => x_msg_count,
1853: p_data => x_msg_data );
1854:
1854:
1855: WHEN OTHERS THEN
1856: ROLLBACK to Delete_FormulaDetail;
1857: fnd_msg_pub.add_exc_msg (G_PKG_NAME, l_api_name);
1858: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859: FND_MSG_PUB.Count_And_Get (
1860: p_count => x_msg_count,
1861: p_data => x_msg_data );
1862: