Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Store Procedure example trying to undertand...

Posted on 2014-10-08
2
Medium Priority
?
281 Views
Last Modified: 2014-10-09
I have this store procedure bellow and I was wondering if someone can explain to me what it does, possible... I'm new to this...


USE [E1_INTEGRATE]
GO
/****** Object:  StoredProcedure [dbo].[usp_TGC_RPT_EstimateToActual]    Script Date: 10/08/2014 08:36:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

--exec usp_TGC_RPT_EstimateToActual   0, 970530, 0, '', 0, '00006'

--ALTER PROCEDURE [dbo].[usp_TGC_RPT_EstimateToActual]
-- ( @CustomerNbr float = 0, 
--   @JTNbr float = 0,
--   @WONbr float = 0,
--   @PlannerName varchar(100) = '',
--   @OnlyActualExceedsEstimate bit = 0,
--   @TGCCompany  nchar(5) = '00003'
--    )
-- AS
 
---- -------------------------------------------------------------------------------------------------------------
-- TGC Project manager estimate-to-actual report
--  
-- -------------------------------------------------------------------------------------------------------------
-- Modification Log
--    
--   Date          name           description
--
--   2012-12-07    egermano       created
--  
--
-- -------------------------------------------------------------------------------------------------------------

--Declare
-- @CustomerNbr float, 
-- @JTNbr float,
-- @WONbr float,
-- @PlannerName varchar(100),
-- @TGCCompany  nchar(5)
--  SET @CustomerNbr = null
--  SET @JTNbr = null
--  SET @WONbr = null
--  SET @PLannerName = ''
--  SET @TGCCompany = '00006'
--

IF @CustomerNbr = NULL
   SET @CustomerNbr = 0

IF @JTNbr = NULL
   SET @JTNbr = 0

IF @WONbr = null
   SET @WONbr = 0
----------------------------------------------------------------
-- Step 1: Call function to return list of work orders
--        inputs: 0 or customer nbr  
--                0 or JT number
--                0 or work order nbr
--                blank or JT planner name from 20/10
--                if all of the above are null, return all work orders for TGCCompany
--          
----------------------------------------------------------------

 Declare @WorkOrders Table
(   WONbr float, 
    WAAN8 float, 
    CustomerName nvarchar(200), 
    JTNumber float, 
    JTDesc nvarchar(200),
    WAITM float ,
    WOStatus int
 )

Insert @WorkOrders (WOnbr, WAAN8, CustomerName, JTNumber, JTDesc, WAITM, WOStatus) 
 Select WONbr, WAAN8, CustomerName, JTNumber, JTDesc, WAITM , WOStatus
       from dbo.f_TGC_Get_WorkOrders (@CustomerNbr, @JTNbr, @WONbr, @PlannerName, @TGCCompany)
  

----------------------------------------------------------------
-- Step 2: Fetch estimate/actual details for the work orders in question
--        F4801: work order header
--        F3003: routing master has operations and op desc 
--                 (f4801.waitm = f3003.irkit
--                  f3003.IRDSC, f3003.IROPSQ / 100, f3003.RUNL est hours 
--        F3112: summary work order routing -- not terribly useful because they want hours by person
--        F31122: individual hours by employee
--                f31122.wtkit = f3003.irkit, f31122.wtopsq = f3003.iropsq) 
--                f31122.wthrw is actual hours 
--                f31122.wtan8 = employee nbr (look up name in f0101) 
--          
----------------------------------------------------------------


 Declare @WORouting Table
(   WONbr float, 
    OpCode float, 
    OpCodeDesc nvarchar(100), 
    EstHours float, 
    ActHours float, 
    EmpNbr float, 
    EmpName nvarchar(100),
    WAITM float
 )


  Insert @WORouting
         (WONbr, 
          OpCode ,
          OpCodeDesc ,
          EstHours ,
          ActHours,
          EmpNbr ,
          EmpName ,
          WAITM 
 )
  Select wo.WONbr, 
         f3003.IROPSQ / 100, 
         f3003.IRDSC1, 
         f3003.IRRUNL, 
         sum(f31122.wthrw / 100), 
         f31122.wtan8,
         '',
         f3003.IRKIT
 
    from @WorkOrders wo
         inner join ps_prod.proddta.f3003 f3003 (nolock)
                 on wo.waitm = f3003.irkit  
                and ltrim(rtrim(f3003.IRMMCU)) = '308'
         inner join ps_prod.proddta.f31122 f31122 (nolock) 
                 on f3003.irkit = f31122.wtkit
                and f3003.iropsq = f31122.wtopsq
 group by wo.WONbr, 
         f3003.IROPSQ / 100, 
         f3003.IRDSC1, 
         f3003.IRRUNL, 
         f31122.wtan8,
         f3003.IRKIT




----------------------------------------------------------------
-- Step 3: Drop work orders that have no actual or estimated hours
----------------------------------------------------------------

 Delete @WORouting
  where EstHours = 0 
    and ActHours = 0

update @WORouting
   Set EmpName = f0101.abalph
  from @WORouting rt
       inner join ps_prod.proddta.f0101 f0101 (nolock)
               on rt.EmpNbr = f0101.aban8


----------------------------------------------------------------
-- Step 4: Aggregate data by employee & op code
--        - calculate percentage complete.  
--        - if filter specified, filter for actual > estimated with wo status < 95
--          
----------------------------------------------------------------


 Declare @Results Table
(   WONbr float, 
    CustomerNbr float, 
    CustomerName nvarchar(200), 
    JTNumber float, 
    JTDesc nvarchar(200),
    OpCode float, 
    OpCodeDesc nvarchar(100), 
    EstHours float, 
    ActHours float, 
    OpCodeEstimatedPctComplete decimal(3,1),
    EmpNbr float, 
    EmpName nvarchar(100),
    WOStatus int, 
    TotOpCodeEst float default 0, 
    TotOpCodeAct float default 0, 
    TotOpCodePct float default 0,
    TotOpCodePctstr varchar(10), 
    HasMultipleEmployeesOnOpCode bit default 0,
    EstOutsideCosts float default 0,
    ActOutsideCosts float default 0,
    VarianceOutsideCosts float default 0,
    CombinedNotes nvarchar(4000) default '' 


  )

  Insert @Results
(   WONbr ,
    CustomerNbr ,
    CustomerName,
    JTNumber ,
    JTDesc ,
    OpCode ,
    OpCodeDesc ,
    EstHours,
    ActHours ,
    EmpNbr ,
    EmpName ,
    WOStatus,
    TotOpCodeEst ,
    TotOpCodeAct ,
    TotOpCodePct
  )

 Select wo.WONbr, 
        wo.WAAN8, 
        wo.CustomerName, 
        wo.JTNumber, 
        wo.JTDesc, 
        wor.OpCode, 
        wor.OpCodeDesc, 
        wor.estHours, 
        wor.ActHours, 
        wor.EmpNbr, 
        wor.EmpName ,
        wo.WOStatus,
        0.0, 0.0, 0.0
  from @WorkOrders wo
       inner join  @WORouting wor
               on wo.WONbr = wor.WONbr
 
Update @Results
   set TotOpCodeEst = rsum.SumEst,
       TotOpCodeAct = rsum.SumAct
  from @Results r
       inner join (Select WoNbr, OpCode, sum(estHours) 'SumEst', sum(ActHours) 'SumAct' 
                     from @Results
                   group by WoNbr, OpCode) rsum
          on r.WoNbr = rsum.WoNbr
         and r.OpCode = rsum.OpCode

Update @Results
   set HasMultipleEmployeesOnOpCode = 1
  from @Results r
       inner join (Select WOnbr, OpCode
                     from @Results
                    group by WONbr, OpCode
                    having count(*) > 1)
        rcnt
        on r.WOnbr = rcnt.WOnbr
        and r.OpCode = rcnt.OpCode

 
-- debug code
--Update @Results set TotOpCodeEst = 100 where WONbr < 938000

-- user request is to show 71 actual against 100 estimated as 71%
-- user request is to show 113 actual against 100 estimated as +13%
-- at present users aren't entering any estimates 8/28/2012

 Update @Results
    set TotOpCodePct  = Case when TotOpCodeEst > 0  and TotOpCodeEst > TotOpCodeAct then (TotOpCodeAct / TotOpCodeEst) * 100.00  
                            when TotOpCodeEst > 0 then                                 (  (TotOpCodeAct / TotOpCodeEst) ) * 100.00  
                       else 0
                       end


  IF @OnlyActualExceedsEstimate = 1
     DELETE @Results
     where TotOpCodeAct <= TotOpCodeEst
       or WOStatus >= 95

 Update @Results 
    set TotOpCodePctstr = case when TotOpCodeEst = 0 then ''
                               when  TotOpCodePct  <= 100 then convert(varchar(10), Round(TotOpCodePct, 2)) + '%'
                               when  TotOpCodePct > 100 then '+' + convert(varchar(10), Round(TotOpCodePct - 100, 2)) + '%'
                          else ''
                          end

----------------------------------------------------------------
-- Step 5: Collect outside purchases at the job level
--         NOTE: to see data in the E1 front-end, 
--               Tweddle Group Italy -> Purchasing -> Enter PO (enter the oldoco nbr, find, row exit purchasing ledger)
----------------------------------------------------------------

Declare @PurLedger table
( olukid float,
  OrderNbr float,
  LineNbr float,
  NextStatus nchar(3),
  PurchPrice float, 
  AmtReceived float,
  JTNumber varchar(10), 
  PurchItem nchar(25),
  IsFinalPurchPrice bit default 0 
 )

 Insert @PurLedger
 (olukid,
  OrderNbr,
  LineNbr,
  NextStatus,
  PurchPrice,
  AmtReceived, 
  JtNumber,
  PurchItem
 )

 select  olukid,
         oldoco 'OrderNumber', 
         ollnid 'linenbr',
         ollttr 'nextStatus', 
         olprrc * .0001 'PurchPrice', 
         olarec * .01 'AmtReceived',                        -- yes, only 2 decicmal places
          Substring( ollitm, 
                    charindex('-', ollitm )  + 1,  
                    charindex('*', ollitm ) - charindex('-', ollitm ) - 1) 'JTNumber',  
          ollitm
  from  ps_prod.proddta.f43199 (nolock)
where  olkcoo = @TGCCompany
  and   ollitm like 'AUTHO%'
 and   ollitm like '%*OP%'

order by orderNumber, LInenbr, olukid,  nextStatus


Delete @PurLedger
where IsNumeric(JTNumber) = 0                    -- can't tie this back to a good jt number

Delete @PurLedger
where Ordernbr in (select oldoco from ps_prod.proddta.f43199 (nolock)
                   where olkcoo = @TGCCompany
                    and olcndj > 0               -- cancel date on one of the ledger lines
                   )

Update @PurLedger
  Set IsFinalPurchPrice = 1
  from @PurLedger ldg1
       inner join  (select OrderNbr, LineNbr, max(olukid) 'olukid'
                      from @PurLedger
                     where PurchPrice > 0
                     group by OrderNbr, LineNbr 
                   ) ldg2
               on ldg1.OrderNbr = ldg2.OrderNbr
              and ldg1.LineNbr = ldg2.LineNbr
              and ldg1.olukid = ldg2.olukid

 
 
Update @Results
  set EstOutsideCosts = SumPurch
  from @Results res
       Inner join (Select JTNumber, sum(PurchPrice) 'SumPurch'
                    from @PurLedger
                    where IsFinalPurchPrice = 1
                    Group by JTNumber
                  ) est
       on res.jtnumber = est.JTNumber


Update @Results
  set ActOutsideCosts = est.SumRcvd
  from @Results res
       Inner join (Select   JTNumber , sum(AmtReceived) 'SumRcvd'
                    from @PurLedger
                    Group by JTNumber
                  ) est
       on res.jtnumber = convert(float, est.JTNumber)

  
 Update @Results
    set VarianceOutsideCosts = (EstOutsidecosts - ActOutsideCosts) * 1.00

 
----------------------------------------------------------------
-- Step 6: Add estimated pct complete at op code level
----------------------------------------------------------------

Update @Results
  set OpCodeEstimatedPctComplete = pm.EstimatedPctComplete
  from @Results r
       inner join TGC_ProjectMgt pm
               on r.JTNumber = pm.JTNumber
              and r.WONbr = pm.WorkOrderNumber
              and r.OpCode = pm.OpCode


----------------------------------------------------------------
-- Step 8: Return report results
----------------------------------------------------------------

 select r.* 
  from @Results r
order by r.jtnumber, woNbr, opCode

Open in new window

0
Comment
Question by:yguyon28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:yguyon28
ID: 40368262
Like I'm more interested in what this does...

Declare @WORouting Table
(   WONbr float, 
    OpCode float, 
    OpCodeDesc nvarchar(100), 
    EstHours float, 
    ActHours float, 
    EmpNbr float, 
    EmpName nvarchar(100),
    WAITM float
 )


  Insert @WORouting
         (WONbr, 
          OpCode ,
          OpCodeDesc ,
          EstHours ,
          ActHours,
          EmpNbr ,
          EmpName ,
          WAITM 
 )
  Select wo.WONbr, 
         f3003.IROPSQ / 100, 
         f3003.IRDSC1, 
         f3003.IRRUNL, 
         sum(f31122.wthrw / 100), 
         f31122.wtan8,
         '',
         f3003.IRKIT
 
    from @WorkOrders wo
         inner join ps_prod.proddta.f3003 f3003 (nolock)
                 on wo.waitm = f3003.irkit  
                and ltrim(rtrim(f3003.IRMMCU)) = '308'
         inner join ps_prod.proddta.f31122 f31122 (nolock) 
                 on f3003.irkit = f31122.wtkit
                and f3003.iropsq = f31122.wtopsq
 group by wo.WONbr, 
         f3003.IROPSQ / 100, 
         f3003.IRDSC1, 
         f3003.IRRUNL, 
         f31122.wtan8,
         f3003.IRKIT




----------------------------------------------------------------
-- Step 3: Drop work orders that have no actual or estimated hours
----------------------------------------------------------------

 Delete @WORouting
  where EstHours = 0 
    and ActHours = 0

update @WORouting
   Set EmpName = f0101.abalph
  from @WORouting rt
       inner join ps_prod.proddta.f0101 f0101 (nolock)
               on rt.EmpNbr = f0101.aban8

Open in new window

0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40368264
This is not a stored procedure. Maybe it was before but someone commented the stored procedure header and now it's a T-SQL script with 7 steps (step 7 was skipped to 8) and each step it's identified and commented.
Here are the steps:
----------------------------------------------------------------
-- Step 1: Call function to return list of work orders
--        inputs: 0 or customer nbr  
--                0 or JT number
--                0 or work order nbr
--                blank or JT planner name from 20/10
--                if all of the above are null, return all work orders for TGCCompany
--          
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 2: Fetch estimate/actual details for the work orders in question
--        F4801: work order header
--        F3003: routing master has operations and op desc
--                 (f4801.waitm = f3003.irkit
--                  f3003.IRDSC, f3003.IROPSQ / 100, f3003.RUNL est hours
--        F3112: summary work order routing -- not terribly useful because they want hours by person
--        F31122: individual hours by employee
--                f31122.wtkit = f3003.irkit, f31122.wtopsq = f3003.iropsq)
--                f31122.wthrw is actual hours
--                f31122.wtan8 = employee nbr (look up name in f0101)
--          
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 3: Drop work orders that have no actual or estimated hours
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 4: Aggregate data by employee & op code
--        - calculate percentage complete.  
--        - if filter specified, filter for actual > estimated with wo status < 95
--          
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 5: Collect outside purchases at the job level
--         NOTE: to see data in the E1 front-end,
--               Tweddle Group Italy -> Purchasing -> Enter PO (enter the oldoco nbr, find, row exit purchasing ledger)
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 6: Add estimated pct complete at op code level
----------------------------------------------------------------
----------------------------------------------------------------
-- Step 8: Return report results
----------------------------------------------------------------
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question