0 Replies Latest reply on Oct 3, 2007 3:17 PM by Arbi Sookazian

    Reverse engineer stored proc to JPA entity beans?

    Arbi Sookazian Master

      Is it possible to do this and generate a ResultSet? Or is it recommended to just use the existing proc with JSF/Seam/EJB3? thx.

      SET NOCOUNT ON
      declare @networkID varchar(50)
      declare @seedHireLevel int
      DECLARE @rptPARAM varchar(20)
      
      set @networkID = 'corp\btkach'--@paraNetworkId
      set @networkID = right(@networkID,(LEN(@networkID )-5) )
      
      SELECT
      UserId
      ,ISNULL(LEN(EmployeeID),0) AS [len_EmpId]
      ,EmployeeID
      ,FirstName + ' ' + LastName as FullName
      ,Title
      ,NetworkID
      ,BillingLogin
      ,(SELECT [A2VNAF]
       FROM [coxDSS].[dbo].[ORG_CBURIDPF]
      WHERE
      [A2USER] = BillingLogin) as SecurityGroup
      ,(SELECT
       ABS([AUNUM2]) AS AdjAmount
      FROM
       [coxDSS].[dbo].[ORG_AUTUSRPF]
      WHERE
       [AUUSER] = (SELECT [A2VNAF]
       FROM [coxDSS].[dbo].[ORG_CBURIDPF]
      WHERE
      [A2USER] = BillingLogin)
       AND
       [AUOPTN] = 'AA'
       AND
       [AUENTR] = 'Y'
       AND
       ABS([AUNUM1]) > 0) AS adjAmount
      
      ,(SELECT
       [AUFUNC] AS AdjFunc
      FROM
       [coxDSS].[dbo].[ORG_AUTUSRPF]
      WHERE
       [AUUSER] = (SELECT [A2VNAF]
       FROM [coxDSS].[dbo].[ORG_CBURIDPF]
      WHERE
      [A2USER] = BillingLogin)
       AND
       [AUOPTN] = 'AA'
       AND
       [AUENTR] = 'Y'
       AND
       ABS([AUNUM1]) > 0) AS AdjFunc
      from coxIM.dbo.u_tbUser tbU
      WHERE
      HierarchyPath LIKE '%' + @networkID + '\'
      ORDER BY
      LastName