0 Replies Latest reply: May 21, 2012 1:42 PM by 938383 RSS

    eng_eco_pub.process_Eco for UPDATE of effectivity_date on revised items

    938383
      Hi All,
      We are on Oracle Applications Rel 11.5.8.
      We are trying to update an existing ECO with an ‘UPDATE’ transaction_type using eng_Eco_pub API. Here is the code. We first execute this script with CREATE and ECO is created. However, when we run the same script to UPDATE with transaction_type = ‘UPDATE’, the bill_sequence_id in eng_revised_items is updated to g_miss_num (9.99E125). The API is returning ‘SUCCESS’ and updates the bill_sequence_id to 9.999E125. Not sure what we are missing here. Appreciate any inputs or sample code for UPDATING an already created ECO.

      REVISED_ITEM_ID BILL_SEQUENCE_ID CHANGE_NOTICE
      11668210 192981011 IK-API0316

      REVISED_ITEM_ID BILL_SEQUENCE_ID CHANGE_NOTICE
      11668210 9.99E125 IK-API0316

      Thank You!

      DECLARE
      l_eco_name VARCHAR2(10) := 'IK-API0000' ;
      l_org_code VARCHAR2(3) := 'GLO';
      l_transaction_type VARCHAR2(10) := 'CREATE'; -- or UPDATE for updating the same ECO
      l_rev_item_number1 VARCHAR2(20):= '16-2214-01';
      l_eff_date DATE := NULL;
      v_old_effective_date DATE;
      l_new_revised_item_revision VARCHAR2(3) := '-A0';

      l_eco_rec Eng_Eco_Pub.Eco_Rec_Type := Eng_Eco_Pub.g_miss_eco_rec;
      l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type := Eng_Eco_Pub.g_miss_eco_revision_tbl;
      l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type ;-- := Eng_Eco_Pub.g_miss_revised_item_tbl;
      l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type := Eng_Eco_Pub.g_miss_rev_component_tbl;
      l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type := Eng_Eco_Pub.g_miss_sub_component_tbl;
      l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type := Eng_Eco_Pub.g_miss_ref_designator_tbl;
      l_rev_operation_tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type := Eng_Eco_Pub.g_miss_rev_operation_tbl;
      l_rev_op_resource_tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type := Eng_Eco_Pub.g_miss_rev_op_resource_tbl;
      l_rev_sub_resource_tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type := Eng_Eco_Pub.g_miss_rev_sub_resource_tbl;

      -- API output variables
      x_eco_rec Eng_Eco_Pub.Eco_Rec_Type := Eng_Eco_Pub.g_miss_eco_rec;
      x_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type := Eng_Eco_Pub.g_miss_eco_revision_tbl;
      x_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type ;-- := Eng_Eco_Pub.g_miss_revised_item_tbl;
      x_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type := Eng_Eco_Pub.g_miss_rev_component_tbl;
      x_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type := Eng_Eco_Pub.g_miss_sub_component_tbl;
      x_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type := Eng_Eco_Pub.g_miss_ref_designator_tbl;
      x_rev_operation_tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type := Eng_Eco_Pub.g_miss_rev_operation_tbl;
      x_rev_op_resource_tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type := Eng_Eco_Pub.g_miss_rev_op_resource_tbl;
      x_rev_sub_resource_tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type := Eng_Eco_Pub.g_miss_rev_sub_resource_tbl;

      -- Other API variables for return status / error handling / debugging
      l_error_table Error_Handler.Error_Tbl_Type;
      l_return_status VARCHAR2(1) := NULL;
      l_msg_count NUMBER := 0;

      l_output_dir VARCHAR2(500) := '/apps/orarpt/SJDEV/utl';
      l_debug_filename VARCHAR2(60) := 'test.dbg';

      -- WHO columns
      l_user_id NUMBER := -1;
      l_resp_id NUMBER := -1;
      l_application_id NUMBER := -1;
      l_row_cnt NUMBER := 1;
      l_user_name VARCHAR2(30) := 'CA_ADMIN';
      l_resp_name VARCHAR2(30) := 'Engineering';
      l_comp_eff_date date := null;
      l_disable_date date := null;

      BEGIN

      -- Get the user_id
      SELECT user_id,sysdate + 10,sysdate
      INTO l_user_id,l_comp_eff_date,l_eff_date
      FROM fnd_user
      WHERE user_name = l_user_name;

      -- Get the application_id and responsibility_id
      SELECT application_id, responsibility_id
      INTO l_application_id, l_resp_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name = l_resp_name;


      FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- Mfg / Mfg & Dist Mgr / INV
      dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

      dbms_output.put_line('before initialixe');
      Error_Handler.Initialize;
      l_return_status := NULL;
      dbms_output.put_line('after initialixe');


      -- FOR UPDATE API to update effectivity date on Revised item.
      l_eco_rec.eco_name := l_eco_name;
      l_eco_rec.organization_code := l_org_code;
      l_eco_rec.change_type_code := 'DESIGN';
      l_eco_rec.eco_department_name := NULL;
      l_eco_rec.priority_code := NULL; --'Medium';
      l_eco_rec.approval_list_name := NULL; --'SU_ONLY';
      l_eco_rec.reason_code := NULL;
      l_eco_rec.Approval_Status_TYPE := 5 ;-- 'Approved'; -- will default to Not submitted for Approval
      l_eco_rec.Status_TYPE := 1; -- 1 to bring in SCHEDULE status
      l_eco_rec.description := l_eff_date || ' - ECO - '||l_eco_rec.eco_name ;
      l_eco_rec.transaction_type := l_transaction_type;
      l_eco_rec.return_status := NULL;

      IF l_transaction_type ='CREATE' THEN

      l_revised_item_tbl(l_row_cnt).transaction_type := l_transaction_type; -- transaction type : CREATE / UPDATE
      l_revised_item_tbl(l_row_cnt).eco_name := l_eco_name;
      l_revised_item_tbl(l_row_cnt).organization_code := l_org_code;
      l_revised_item_tbl(l_row_cnt).revised_item_name := l_rev_item_number1;
      l_revised_item_tbl(l_row_cnt).new_revised_item_revision := '-A0'; -- pass only if the previous unimplemented updating ECO has a new revision
      l_revised_item_tbl(l_row_cnt).Updated_Revised_Item_Revision := ''; -- l_new_revised_item_revision;
      l_revised_item_tbl(l_row_cnt).start_effective_date := l_eff_date;
      l_revised_item_tbl(l_row_cnt).new_effective_date := l_eff_date ; -- l_comp_eff_date;
      l_revised_item_tbl(l_row_cnt).alternate_bom_code := null;
      l_revised_item_tbl(l_row_cnt).status_type := 1; -- 'Open'
      l_revised_item_tbl(l_row_cnt).change_description := l_rev_item_number1 || ' --B0 ECO - '||l_eco_rec.eco_name ;
      l_revised_item_tbl(l_row_cnt).disposition_type :=1 ;
      l_revised_item_tbl(l_row_cnt).update_wip := 1;--2
      l_revised_item_tbl(l_row_cnt).mrp_active := 1;
      --l_revised_item_tbl(l_row_cnt).new_item_revision:= '';
      l_revised_item_tbl(l_row_cnt).new_routing_revision:= '';
      l_revised_item_tbl(l_row_cnt).from_end_item_unit_number:= ''; -- this field is mandatory
      l_revised_item_tbl(l_row_cnt).eco_for_production:=2;
      l_revised_item_tbl(l_row_cnt).earliest_effective_date := l_eff_date;
      l_revised_item_tbl(l_row_cnt).use_up_item_name := l_rev_item_number1; null;
      --l_revised_item_tbl(l_row_cnt).use_up_plan_name :=null;

      END IF;

      IF l_transaction_type = 'UPDATE' THEN
      FOR v_component in (SELECT * from eng_revised_items where change_notice = 'IK-API0000') LOOP

      l_revised_item_tbl(l_row_cnt).eco_name := l_eco_name;
      l_revised_item_tbl(l_row_cnt).organization_code := l_org_code;
      l_revised_item_tbl(l_row_cnt).revised_item_name := l_rev_item_number1;
      l_revised_item_tbl(l_row_cnt).new_revised_item_revision := '-A0';
      l_revised_item_tbl(l_row_cnt).New_Revised_Item_Rev_Desc := '-F0 ik added';
      l_revised_item_tbl(l_row_cnt).Updated_Revised_Item_Revision := '-F0';
      l_revised_item_tbl(l_row_cnt).Start_Effective_Date := l_eff_date;
      l_revised_item_tbl(l_row_cnt).New_Effective_Date := l_eff_date;
      l_revised_item_tbl(l_row_cnt).Alternate_Bom_Code := v_component.alternate_bom_designator;
      l_revised_item_tbl(l_row_cnt).Status_Type := v_component.status_type;
      l_revised_item_tbl(l_row_cnt).Mrp_Active := v_component.Mrp_Active;
      l_revised_item_tbl(l_row_cnt).Earliest_Effective_Date := sysdate;
      l_revised_item_tbl(l_row_cnt).Requestor := '';
      l_revised_item_tbl(l_row_cnt).Use_Up_Plan_Name := v_component.Use_Up_Plan_Name;
      l_revised_item_tbl(l_row_cnt).Disposition_Type := v_component.Disposition_Type;
      l_revised_item_tbl(l_row_cnt).Update_Wip := v_component.Update_Wip;
      l_revised_item_tbl(l_row_cnt).Cancel_Comments := v_component.Cancel_Comments;
      l_revised_item_tbl(l_row_cnt).Change_Description := 'TESTING ';
      l_revised_item_tbl(l_row_cnt).Attribute_category := v_component.Attribute_category;
      l_revised_item_tbl(l_row_cnt).Attribute1 := v_component.Attribute1;
      l_revised_item_tbl(l_row_cnt).Attribute2 := v_component.Attribute2;
      l_revised_item_tbl(l_row_cnt).Attribute3 := v_component.Attribute3;
      l_revised_item_tbl(l_row_cnt).Attribute4 := v_component.Attribute4;
      l_revised_item_tbl(l_row_cnt).Attribute5 := v_component.Attribute5;
      l_revised_item_tbl(l_row_cnt).Attribute6 := v_component.Attribute6;
      l_revised_item_tbl(l_row_cnt).Attribute7 := v_component.Attribute7;
      l_revised_item_tbl(l_row_cnt).Attribute8 := v_component.Attribute8;
      l_revised_item_tbl(l_row_cnt).Attribute9 := v_component.Attribute9;
      l_revised_item_tbl(l_row_cnt).Attribute10 := v_component.Attribute10;
      l_revised_item_tbl(l_row_cnt).Attribute11 := v_component.Attribute11;
      l_revised_item_tbl(l_row_cnt).Attribute12 := v_component.Attribute12;
      l_revised_item_tbl(l_row_cnt).Attribute13 := v_component.Attribute13;
      l_revised_item_tbl(l_row_cnt).Attribute14 := v_component.Attribute14;
      l_revised_item_tbl(l_row_cnt).Attribute15 := v_component.Attribute15;
      l_revised_item_tbl(l_row_cnt).From_End_Item_Unit_Number := v_component.From_End_Item_Unit_Number;
      l_revised_item_tbl(l_row_cnt).New_From_End_Item_Unit_Number := '';
      l_revised_item_tbl(l_row_cnt).Original_System_Reference :=v_component.Original_System_Reference;
      l_revised_item_tbl(l_row_cnt).Return_Status := '';
      l_revised_item_tbl(l_row_cnt).Transaction_Type := l_transaction_type;
      -- L1, the following is added for ECO enhancement
      l_revised_item_tbl(l_row_cnt).From_Work_Order := '';
      l_revised_item_tbl(l_row_cnt).To_Work_Order := '';
      l_revised_item_tbl(l_row_cnt).From_Cumulative_Quantity := v_component.from_cum_qty;
      l_revised_item_tbl(l_row_cnt).Lot_Number := v_component.Lot_Number;
      l_revised_item_tbl(l_row_cnt).Completion_Subinventory := v_component.Completion_Subinventory;
      l_revised_item_tbl(l_row_cnt).Completion_Location_Name :='';
      l_revised_item_tbl(l_row_cnt).Priority := v_component.Priority;
      l_revised_item_tbl(l_row_cnt).Ctp_Flag := v_component.Ctp_Flag;
      l_revised_item_tbl(l_row_cnt).New_Routing_Revision := v_component.New_Routing_Revision;
      l_revised_item_tbl(l_row_cnt).Updated_Routing_Revision := '';
      l_revised_item_tbl(l_row_cnt).Routing_Comment := v_component.Routing_Comment;
      -- L1, the above is added for ECO enhancement
      l_revised_item_tbl(l_row_cnt).Eco_For_Production := v_component.Eco_For_Production;
      END LOOP;
      END IF;

      Eng_Eco_PUB.Process_Eco( p_api_version_number => 1.0
      , p_init_msg_list => FALSE
      , x_return_status => l_return_status
      , x_msg_count => l_msg_count
      , p_bo_identifier => 'ECO'
      , p_eco_rec => l_eco_rec
      , p_eco_revision_tbl => l_eco_revision_tbl
      , p_revised_item_tbl => l_revised_item_tbl
      , p_rev_component_tbl => l_rev_component_tbl
      , p_ref_designator_tbl => l_ref_designator_tbl
      , p_sub_component_tbl => l_sub_component_tbl
      , p_rev_operation_tbl => l_rev_operation_tbl
      , p_rev_op_resource_tbl => l_rev_op_resource_tbl
      , p_rev_sub_resource_tbl => l_rev_sub_resource_tbl
      , x_eco_rec => x_eco_rec
      , x_eco_revision_tbl => x_eco_revision_tbl
      , x_revised_item_tbl => x_revised_item_tbl
      , x_rev_component_tbl => x_rev_component_tbl
      , x_ref_designator_tbl => x_ref_designator_tbl
      , x_sub_component_tbl => x_sub_component_tbl
      , x_rev_operation_tbl => x_rev_operation_tbl
      , x_rev_op_resource_tbl => x_rev_op_resource_tbl
      , x_rev_sub_resource_tbl => x_rev_sub_resource_tbl
      , p_debug => 'N'
      , p_output_dir => l_output_dir
      , p_debug_filename => l_debug_filename
      );

      dbms_output.put_line('after API call');
      DBMS_OUTPUT.PUT_LINE('=======================================================');
      DBMS_OUTPUT.PUT_LINE('=======================================================');
      DBMS_OUTPUT.PUT_LINE('Return Status: '||l_return_status);

      dbms_output.put_line('x_eco_rec.eco_name:'|| x_eco_rec.eco_name );
      dbms_output.put_line('x_eco_rec.org_code:'|| x_eco_rec.organization_code);

      IF (l_return_status = 'E') THEN
      dbms_output.put_line('x_msg_count:' || l_msg_count);
      Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
      DBMS_OUTPUT.PUT_LINE('Error Message Count :'||l_error_table.COUNT);

      FOR i IN 1..l_error_table.COUNT LOOP
      --DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).entity_index||':'||l_error_table(i).table_name);
      DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).entity_index||':');
      DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||substr(l_error_table(i).message_text,1,250));
      END LOOP;

      --ROLLBACK;
      ELSE
      DBMS_OUTPUT.PUT_LINE('==COMMITING==');
      --COMMIT;
      END IF;
      DBMS_OUTPUT.PUT_LINE('=======================================================');


      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Exception Occured :');
      DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
      DBMS_OUTPUT.PUT_LINE('=======================================================');
      RAISE;

      END;