Creating a User_Rebuild stored procedure

You can create your own user datamart to perform site specific SQL tasks over the Infomart database.
  1. Create a User_Rebuild stored procedure:
    1. If you are using SQL Server, use this template:
      if exists (select name from sysobjects where uid in (select uid from sysusers where name = 'infodbo') and name = 'User_Rebuild' and type='P')
      	Drop procedure [infodbo].[User_Rebuild]
      GO
      
      Create procedure [infodbo].[User_Rebuild]
      @job int = 0
      as
      set nocount on
      declare @error_var int
      exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','Start User SQL.'
      
      --  ******* Start User area for SQL statement
      Print 'Start User SQL...'
      
      -- INSERT YOUR SQL STATEMENTS HERE.  AFTER EACH STATEMENT YOU SHOULD CHECK THE STATUS OF @@ERROR...
      SET @error_var = @@ERROR
      if @error_var <> 0
      	exec [infodbo].[log_entry] @job,'E',@error_var,'User_Rebuild','Error occurred.'
      
      
      -- IF YOU WISH TO CALL ANOTHER USER STORED PROCEDURE THE USE THE STATEMENT BELOW...
      --  1. Change the 'USER_000' value to your SP name.
      --  2. Uncomment the line. 
      -- exec [infodbo].[rebuild_procedure] @job_id, 'USER_000'
      
      
      Print 'End User SQL.'
      
      --  ******* End User area for SQL statement
      exec [infodbo].[log_entry] @job,'I',0,'Vicval_10','End User SQL.'
      
      GO
      
    2. If you are using Oracle, use this template:
      CREATE OR REPLACE PROCEDURE User_Rebuild
      (
          p_JobId         INTEGER,
          p_Status    OUT INTEGER
      )
      -- Populate Animals
      IS
          v_Procedure     VARCHAR2(20) := 'User_Rebuild';
          v_Parm_Value    VARCHAR2(80);
          v_Sysdate       DATE     := SYSDATE;
      BEGIN
      -- 0010
          -- Set Status to ERROR, reset on completion
          p_Status := -1;
      
          -- Checkpoint 0000
          SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0000');
      
          -- INSERT YOUR SQL STATEMENT HERE. 
      
          -- Checkpoint 0010
          SYSTEM.LOG(p_JobId,'C',0,v_Procedure,'CP0010');
       
          -- INSERT YOUR NEXT SQL STATEMENT HERE. 
      
          --  ...
      
          -- Set Completion Status to Good
          p_Status := 1;
      --  error check
      EXCEPTION
          -- log error
          WHEN OTHERS THEN
               COMMIT;
               SYSTEM.LOG(p_JobId,'E',SQLCODE,v_Procedure,NULL);
      END;
      
  2. Modify the USER_REBUILD.SQL script.
  3. Execute the script in a Pathway session when connected to the Infomart database:
    1. On the sidebar, select System Administration > Infomart > Rebuilding > Datamart Maintenance.
    2. Check the last record in the Datamart grid, which is USER datamart.
    3. Select the Rebuild check box for this record.
    4. Click Save.
    5. Select System Administration > Infomart > Rebuilding > Rebuild Infomart.
    6. Clear the Rebuild check boxes for datamarts that are not to be run.
    7. Select the USER datamart, then click Submit.
    Note: The customer is responsible for developing and maintaining the script. Infor Professional Services can assist as required.