Topic
1 reply Latest Post - ‏2013-04-25T16:53:22Z by Anil_Abraham
DR1R_Stuart_Norton
DR1R_Stuart_Norton
1 Post
ACCEPTED ANSWER

Pinned topic Type 2 User Exit example?

‏2013-04-12T02:57:50Z |
Hi all,
 
The RedBook explains the basic process for implementing Type 2 SCD updates using LiveAudit and a user exit, but does anyone know of a sample SP that shows it done?
 
Cheers,
Stuart.
 
  • Anil_Abraham
    Anil_Abraham
    3 Posts
    ACCEPTED ANSWER

    Re: Type 2 User Exit example?

    ‏2013-04-25T16:53:22Z  in response to DR1R_Stuart_Norton

    Sample  exit  of  the STP

    Below procedure was used in conjunction with JDE application

     

    USE [MDB01]
    GO
     
    /****** Object:  StoredProcedure [dbo].[sp_userexitABO_new]    Script Date: 03/26/2013 09:07:11 ******/
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO

    alter procedure [dbo].[sp_userexitABO_new]
    ( @result INT OUTPUT,
      @returnMsg VARCHAR(255)
    OUTPUT,
    @s$entry INT,
    @s$srcSysId VARCHAR(50),
    @s$srcTabId VARCHAR(50),
    @s$tgtTabId VARCHAR(50),
    @j$ENTT CHAR(2),
    @a$Parentnumber Numeric,@a$Addressnumber Numeric, @a$Userexitstatus Int, @a$ErrorCode  Int
    @d$Parentnumber Numeric,@d$Addressnumber Numeric, @d$Userexitstatus Int, @d$ErrorCode  Int)

    declare @ENTRYPOINT VARCHAR(50);

    BEGIN

    select @ENTRYPOINT = case @s$entry
    WHEN 3 THEN 'User Exit program called Before Insert'
    WHEN 4 THEN 'User Exit program called After Insert'
    WHEN 5 THEN 'User Exit program called Before Update'
    WHEN 6 THEN 'User Exit program called After Update'

    END

    set @d$Parentnumber = @a$Parentnumber;
    set @d$Addressnumber = @a$Addressnumber;
    set @d$Userexitstatus = @a$Userexitstatus;
    set @d$ErrorCode = @a$ErrorCode;

    -------------------------------------------------------------------
    --  If Insert then add to  
    -------------------------------------------------------------------
    If (@s$entry = 4)  
       Begin
     
    --    Declare @Parentnumber Numeric,@Addressnumber Numeric, @Userexitstatus Int, @ErrorCode  Int
        --  Check If record already exists in MDB01.dbo.JDECustomerInfo then you need to update parentnumber  
        IF Exists(Select * from MooreDB01.dbo.JDECustomerInfo CI Inner Join mdb01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber))
        Begin
            
             Update MDB01.dbo.JDECustomerInfo  
             Set  
                Parentnumber = Upd.ParentNumber
             From MDB01.dbo.JDECustomerInfo CI
             Inner Join MDB01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber)
     
            /* 8/2003 User exit Error Tracking */
            SELECT @a$ErrorCode = @@Error
            IF (@ErrorCode = 0)
            Begin
                SET @d$Userexitstatus = 1;
            End
            ELSE
            Begin
                SET @d$Userexitstatus = 0;
            End
        
            SELECT @d$Addressnumber = Addressnumber FROM MDB01_UE.dbo.F50;
     
            INSERT INTO MDB01_UE.dbo.Track_userexits(Addressnumber,userexit_name,userexit_action,Track_date,Fields_updated,Target_table,Userexit_status,Userexit_Errorcode)  
            VALUES(@d$Addressnumber,'sp_userexitABO_new', @s$entry, Getdate(), 'Parentnumber','JDECustomerinfo', @d$Userexitstatus,@ErrorCode);
     
     
        End  
     
        --  Check If record already exists in MooreDB01.dbo.JDECustomerInfoOut then you need to update parentnumber  
        IF Exists(Select * from MDB01.dbo.JDECustomerInfoOut CI Inner Join mdb01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber))
        Begin
            
             Update MDB01.dbo.JDECustomerInfoOut
             Set  
                Parentnumber = Upd.ParentNumber
             From MDB01.dbo.JDECustomerInfoOut CI
             Inner Join MDB01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber)
     
     
            /* 8/2003 User exit Error Tracking */
            SELECT @d$ErrorCode = @@Error
            IF (@d$ErrorCode = 0)
            Begin
                SET @d$Userexitstatus = 1;
            End
            ELSE
            Begin
                SET @d$Userexitstatus = 0;
            End
        
            SELECT @d$Addressnumber = Addressnumber FROM MDB01_UE.dbo.F50;
     
            INSERT INTO MDB01_UE.dbo.Track_userexits(Addressnumber,userexit_name,userexit_action,Track_date,Fields_updated,Target_table,Userexit_status,Userexit_Errorcode)  
            VALUES(@d$Addressnumber,'sp_userexitABO_new', @s$entry, Getdate(), 'Parentnumber','JDECustomerinfoOut', @d$Userexitstatus,@d$ErrorCode);
     
     
     
        End  
     
        /* If the attached B record not available in web customerinfo table (Pull B record from JDE) */
        IF Not Exists(Select * from MDB01.dbo.JDECustomerInfo CI Inner Join mdb01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.Parentnumber))
        Begin
        
        Select @d$Parentnumber = Parentnumber From MDB01_UE.dbo.F50
        Execute  JDENewCustomer @d$Parentnumber
     
        End  
     
     
    End
     
    -------------------------------------------------------------------
    --  If and Update then Update The record
    -------------------------------------------------------------------
    Else IF (@Entry = 6)    
     Begin
     
            /* Customer License  Update */
            Update  MDB01.dbo.JDECustomerInfo  
            Set    
                ParentNumber = Upd.ParentNumber
            From  MDB01.dbo.JDECustomerInfo CI
            Inner Join MDB01_ue.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber)
     
            /* 8/2003 User exit Error Tracking */
            SELECT @d$ErrorCode = @@Error
            IF (@d$ErrorCode = 0)
            Begin
                SET @d$Userexitstatus = 1;
            End
            ELSE
            Begin
                SET @d$Userexitstatus = 0;
            End
        
            SELECT @d$Addressnumber = Addressnumber FROM MDB01_UE.dbo.F50;
     
            INSERT INTO MDB01_UE.dbo.Track_userexits(Addressnumber,userexit_name,userexit_action,Track_date,Fields_updated,Target_table,Userexit_status,Userexit_Errorcode)  
            VALUES(@d$Addressnumber,'sp_userexitABO_new', @s$entry, Getdate(), 'Parentnumber','JDECustomerinfo', @d$Userexitstatus,@d$ErrorCode);
     
     
        /* Update the JDECustomerInfoOut table */
        IF Exists(Select * from MDB01.dbo.JDECustomerInfoout CI Inner Join mDB01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber))
        Begin
     
            /* Customer LicenseUpdate*/
            Update  MDB01.dbo.JDECustomerInfoout  
            Set  
                ParentNumber = Upd.ParentNumber
            From  MDB01.dbo.JDECustomerInfoout CI
            Inner Join MDB01_UE.dbo.F50 Upd ON (CI.AddressNumber = Upd.AddressNumber)
     
            /* 8/2003 User exit Error Tracking */
            SELECT @d$ErrorCode = @@Error
            IF (@d$ErrorCode = 0)
            Begin
                SET @d$Userexitstatus = 1;
            End
            ELSE
            Begin
                SET @d$Userexitstatus = 0;
            End
        
            SELECT @d$Addressnumber = Addressnumber FROM MDB01_UE.dbo.F50;
     
            INSERT INTO MDB01_UE.dbo.Track_userexits(Addressnumber,userexit_name,userexit_action,Track_date,Fields_updated,Target_table,Userexit_status,Userexit_Errorcode)  
            VALUES(@d$Addressnumber,'sp_userexitABO_new', @s$entry, Getdate(), 'Parentnumber','JDECustomerinfoOut', @d$Userexitstatus,@d$ErrorCode);
     
        End
     
      End
     
    GO