OPM – GMD Insert Formula Ingredient in R12

PROCEDURE insert_formuladetail(
    p_formula_id    NUMBER ,
    p_ingredient_id NUMBER ,
    p_new_qty       NUMBER ,
    p_uom           VARCHAR2 DEFAULT NULL,
    p_return_status OUT VARCHAR2 ,
    p_message OUT VARCHAR2 )
IS
  l_api_version       CONSTANT NUMBER := 1.0;
  l_init_msg_list     VARCHAR2 (1)    := FND_API.G_TRUE ;
  l_commit            VARCHAR2 (1)    := FND_API.G_FALSE;
  l_called_from_forms VARCHAR2 (10)   := ‘NO’;
  l_return_status     VARCHAR2 (1);
  l_msg_count         NUMBER ;
  l_msg_data          VARCHAR2 (400);
  L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_INSERT_DTL_TBL_TYPE;
  — l_formula_head fm_form_mst_b%rowtype;
  L_LINE_NO NUMBER ;
  — i pls_ integer := 1;
  no_proc_finish EXCEPTION ;
BEGIN
  IF (p_formula_id IS NULL) THEN
    p_message      := ‘P_formula_id parameter can not be empty ‘;
    raise no_proc_finish;
  END IF ;
  BEGIN
    SELECT *
    INTO l_formula_head
    FROM fm_form_mst_b fm
    WHERE fm.formula_id = p_formula_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Could not managed to find a formula ‘;
    raise no_proc_finish;
  END ;
  BEGIN
    SELECT MAX (fm.line_no)
    INTO l_line_no
    FROM fm_matl_dtl fm
    WHERE fm.FORMULA_ID = p_formula_id
    AND fm.line_type    = -1;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Could not find a string formula ‘;
    raise no_proc_finish;
  END ;
  gme_common_pvt.set_who;
  l_formula_detail_tbl(i).formula_id        := p_formula_id;
  l_formula_detail_tbl(i).formula_no        := l_formula_head.formula_no;
  l_formula_detail_tbl(i).formula_vers      := l_formula_head.formula_vers;
  l_formula_detail_tbl(i).line_type         :=           -1;
  l_formula_detail_tbl(i).line_no           := l_line_no + 1;
  l_formula_detail_tbl(i).inventory_item_id := p_ingredient_id;
  l_formula_detail_tbl(i).QTY               := p_new_qty;
  L_FORMULA_DETAIL_TBL(I).RELEASE_TYPE      := 1; — manually
  IF (p_uom                                 IS NOT NULL) THEN
    l_formula_detail_tbl(i).DETAIL_UOM      := p_uom;
  END IF ;
  gmd_formula_detail_pub.insert_formuladetail( p_api_version => l_api_version ,p_init_msg_list => l_init_msg_list ,p_commit => l_commit ,p_called_from_forms => l_called_from_forms ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data ,p_formula_detail_tbl => l_formula_detail_tbl );
  IF l_return_status <> FND_API.g_ret_sts_success THEN
    IF l_msg_count    = 1 THEN
      p_message      := FND_MSG_PUB.get(1,’F’);
      RAISE no_proc_finish;
    ELSE
      FOR l IN 1..l_msg_count
      LOOP
        FND_MSG_PUB.get (p_msg_index => l ,p_encoded => ‘ F ‘ ,p_data => p_message ,p_msg_index_out => l_msg_count);
      END LOOP ;
      RAISE no_proc_finish;
    END IF ;
  END IF ;
  p_return_status := ‘ S ‘;
EXCEPTION
WHEN no_proc_finish THEN
  p_return_status := ‘ E ‘;
WHEN OTHERS THEN
  p_message       := SQLERRM ;
  p_return_status := ‘ E ‘;
END ;