0 Replies Latest reply: Jan 8, 2012 12:04 AM by kr61665 RSS

    Not able to load Recipe Validity rules(OPM) using API

    kr61665
      Hi All,

      I am new to Oracle Apps and not able to load data for Recipe validity Rules

      API = gmd_recipe_detail.create_recipe_vr

      Target table = GMD_RECIPE_VALIDITY_RULES

      Could anyone please tell me what are the fields to be used to load.

      Please find my code given below:



      declare
      l_msg_data VARCHAR2(500);
      l_msg_count NUMBER;
      l_return_status VARCHAR2(1);
      l_sqlcode NUMBER;
      l_sqlerrm VARCHAR2(250);
      c_proc_name CONSTANT VARCHAR2(50) := 'import_data';
      c_routine CONSTANT VARCHAR2(100) := 'gmd_p2p' || '.' || c_proc_name;
      l_VR VARCHAR2(100) := 'declare';
      l_message VARCHAR2(4000);

      /***********************************************************************
      Cursor c_Recipe_VR_cur data from staging table where status is 'V'
      ***********************************************************************/
      CURSOR c_Recipe_VR_cur IS
      SELECT
      RECIPE_VALIDITY_RULE_ID
      ,RECIPE_ID
      ,RECIPE_NO
      ,RECIPE_VERSION
      USER_ID
      ,USER_NAME
      ,ORGN_CODE
      ,INVENTORY_ITEM_ID
      , NULL as REVISION
      ,ITEM_NO
      ---,ITEM_UM
      ,RECIPE_USE
      ,PREFERENCE
      ,START_DATE
      ,END_DATE
      ,MIN_QTY
      ,MAX_QTY
      ,STD_QTY
      ,INV_MIN_QTY
      ,INV_MAX_QTY
      ---,NULL as TEXT_CODE
      ,CREATED_BY
      ,CREATION_DATE
      ,LAST_UPDATED_BY
      ,LAST_UPDATE_DATE
      ,LAST_UPDATE_LOGIN
      ,DELETE_MARK
      ---,PLANNED_PROCESS_LOSS
      ,VALIDITY_RULE_STATUS
      ,ORGN_ID
      FROM XXARS.XXP2P_CNV_OPM_RECIPE_VR_STG /*Staging table from which am fetching the data*/
      WHERE process_flag ='V';



      TYPE cur_Recipe_VR_rec IS TABLE OF c_Recipe_VR_cur%rowtype INDEX BY BINARY_INTEGER;

      R_recipe_vr_tbl cur_Recipe_VR_rec;

      a_recipe_vr_tbl gmd_recipe_detail.recipe_vr_tbl;

      R_recipe_flex GMD_RECIPE_DETAIL.recipe_flex ;



      BEGIN
      xxars_log_pkg.log_proc_start(c_routine);
      l_VR := 'Beginging of For Loop';

      SELECT
      NULL as ATTRIBUTE_CATEGORY
      ,NULL AS ATTRIBUTE1
      ,NULL AS ATTRIBUTE2
      ,NULL AS ATTRIBUTE3
      ,NULL AS ATTRIBUTE4
      ,NULL AS ATTRIBUTE5
      ,NULL AS ATTRIBUTE6
      ,NULL AS ATTRIBUTE7
      ,NULL AS ATTRIBUTE8
      ,NULL AS ATTRIBUTE9
      ,NULL AS ATTRIBUTE10
      ,NULL AS ATTRIBUTE11
      ,NULL AS ATTRIBUTE12
      ,NULL AS ATTRIBUTE13
      ,NULL AS ATTRIBUTE14
      ,NULL AS ATTRIBUTE15
      ,NULL AS ATTRIBUTE16
      ,NULL AS ATTRIBUTE17
      ,NULL AS ATTRIBUTE18
      ,NULL AS ATTRIBUTE19
      ,NULL AS ATTRIBUTE20
      ,NULL AS ATTRIBUTE21
      ,NULL AS ATTRIBUTE22
      ,NULL AS ATTRIBUTE23
      ,NULL AS ATTRIBUTE24
      ,NULL AS ATTRIBUTE25
      ,NULL AS ATTRIBUTE26
      ,NULL AS ATTRIBUTE27
      ,NULL AS ATTRIBUTE28
      ,NULL AS ATTRIBUTE29
      ,NULL AS ATTRIBUTE30
      INTO R_recipe_flex(1)
      from dual;


      OPEN c_Recipe_VR_cur;

      FETCH c_Recipe_VR_cur
      BULK COLLECT
      INTO R_recipe_vr_tbl;



      for i in 1..R_recipe_vr_tbl.count
      loop
      l_msg_data := NULL;
      l_return_status := NULL;



      -- a_recipe_vr_tbl(i) :=R_recipe_vr_tbl(i); /*This statement is not working*/

      a_recipe_vr_tbl(i).RECIPE_VALIDITY_RULE_ID := R_recipe_vr_tbl(i).RECIPE_VALIDITY_RULE_ID;
      a_recipe_vr_tbl(i).RECIPE_ID := R_recipe_vr_tbl(i).RECIPE_ID;

      ---DBMS_OUTPUT.PUT_LINE(R_recipe_vr_tbl.RECIPE_ID);

      a_recipe_vr_tbl(i).RECIPE_NO := R_recipe_vr_tbl(i).RECIPE_NO;
      ---a_recipe_vr_tbl(i).RECIPE_VERSION := R_recipe_vr_tbl(i).RECIPE_VERSION;
      a_recipe_vr_tbl(i).USER_ID := R_recipe_vr_tbl(i).USER_ID;
      a_recipe_vr_tbl(i).USER_NAME := R_recipe_vr_tbl(i).USER_NAME;
      a_recipe_vr_tbl(i).ORGN_CODE := R_recipe_vr_tbl(i).ORGN_CODE;
      a_recipe_vr_tbl(i).inventory_ITEM_ID := R_recipe_vr_tbl(i).INVENTORY_ITEM_ID;
      a_recipe_vr_tbl(i).revision := R_recipe_vr_tbl(i).revision;
      a_recipe_vr_tbl(i).ITEM_NO := R_recipe_vr_tbl(i).ITEM_NO;
      ---a_recipe_vr_tbl(i).ITEM_UM := R_recipe_vr_tbl(i).ITEM_UM;
      a_recipe_vr_tbl(i).RECIPE_USE := R_recipe_vr_tbl(i).RECIPE_USE;
      a_recipe_vr_tbl(i).PREFERENCE := R_recipe_vr_tbl(i).PREFERENCE;
      a_recipe_vr_tbl(i).START_DATE := R_recipe_vr_tbl(i).START_DATE;
      a_recipe_vr_tbl(i).END_DATE := R_recipe_vr_tbl(i).END_DATE ;
      a_recipe_vr_tbl(i).MIN_QTY := R_recipe_vr_tbl(i).MIN_QTY;
      a_recipe_vr_tbl(i).MAX_QTY := R_recipe_vr_tbl(i).MAX_QTY;
      a_recipe_vr_tbl(i).STD_QTY := R_recipe_vr_tbl(i).STD_QTY;
      ---a_recipe_vr_tbl(i).detail_UOM := R_recipe_vr_tbl(i).ITEM_UM;
      a_recipe_vr_tbl(i).INV_MIN_QTY := R_recipe_vr_tbl(i).INV_MIN_QTY;
      a_recipe_vr_tbl(i).INV_MAX_QTY := R_recipe_vr_tbl(i).INV_MAX_QTY;
      ---a_recipe_vr_tbl(i). TEXT_CODE := R_recipe_vr_tbl(i).TEXT_CODE;
      a_recipe_vr_tbl(i).CREATED_BY := R_recipe_vr_tbl(i).CREATED_BY;
      a_recipe_vr_tbl(i).CREATION_DATE := R_recipe_vr_tbl(i).CREATION_DATE;
      a_recipe_vr_tbl(i).LAST_UPDATED_BY := R_recipe_vr_tbl(i).LAST_UPDATED_BY;
      a_recipe_vr_tbl(i).LAST_UPDATE_DATE := R_recipe_vr_tbl(i).LAST_UPDATE_DATE;
      a_recipe_vr_tbl(i).LAST_UPDATE_LOGIN := R_recipe_vr_tbl(i).LAST_UPDATE_LOGIN;
      a_recipe_vr_tbl(i).DELETE_MARK := R_recipe_vr_tbl(i).DELETE_MARK;
      ---a_recipe_vr_tbl(i).PLANNED_PROCESS_LOSS := R_recipe_vr_tbl(i).PLANNED_PROCESS_LOSS;
      a_recipe_vr_tbl(i).VALIDITY_RULE_STATUS := R_recipe_vr_tbl(i).VALIDITY_RULE_STATUS;
      a_recipe_vr_tbl(i).organization_id := R_recipe_vr_tbl(i).ORGN_ID;


      /******************************************************************************
      After Validating Data Insert into GMD_RECIPE_DETAIL.Create_Recipe_VR API
      ******************************************************************************/
      l_VR := 'CREATE RECIPE VALIDITY RULES API';
      *apps.gmd_recipe_detail.create_recipe_vr(
      p_api_version => 1.0
      ,p_init_msg_list => 'T'
      ,p_commit => 'T'
      ,p_called_from_forms => 'NO'
      ,x_return_status => l_return_status
      ,x_msg_count => l_msg_count
      ,x_msg_data => l_msg_data
      ,p_recipe_vr_tbl => a_recipe_vr_tbl
      ,p_recipe_vr_flex => R_recipe_flex
      );*

      EXIT WHEN c_Recipe_VR_cur%NOTFOUND;
      COMMIT;
      END LOOP;

      end;


      Thanks in Advance.