Solved

Store Procedure example trying to undertand...

Posted on 2014-10-08
2
268 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
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now