Need SQL Help

I am trying to get a value for QtyBacked.  It should take the value of qty_to_pick and subtract qty_picked.  Here is what I have, but I cannot seem to get this to work.  Any suggestions?

 


/* $Header: /ApplicationDB/Stored Procedures/Rpt_PickListSp.sp 17    6/28/17 10:13p Lchen3 $ */
/*
***************************************************************
*                           NOTICE                            *
*                                                             *
*   THIS SOFTWARE IS THE PROPERTY OF AND CONTAINS             *
*   CONFIDENTIAL INFORMATION OF INFOR AND/OR ITS AFFILIATES   *
*   OR SUBSIDIARIES AND SHALL NOT BE DISCLOSED WITHOUT PRIOR  *
*   WRITTEN PERMISSION. LICENSED CUSTOMERS MAY COPY AND       *
*   ADAPT THIS SOFTWARE FOR THEIR OWN USE IN ACCORDANCE WITH  *
*   THE TERMS OF THEIR SOFTWARE LICENSE AGREEMENT.            *
*   ALL OTHER RIGHTS RESERVED.                                *
*                                                             *
*   (c) COPYRIGHT 2011 INFOR.  ALL RIGHTS RESERVED.           *
*   THE WORD AND DESIGN MARKS SET FORTH HEREIN ARE            *
*   TRADEMARKS AND/OR REGISTERED TRADEMARKS OF INFOR          *
*   AND/OR ITS AFFILIATES AND SUBSIDIARIES. ALL RIGHTS        *
*   RESERVED.  ALL OTHER TRADEMARKS LISTED HEREIN ARE         *
*   THE PROPERTY OF THEIR RESPECTIVE OWNERS.                  *
*                                                             *
***************************************************************
*/
/* $Archive: /ApplicationDB/Stored Procedures/Rpt_PickListSp.sp $
 *
 * SL9.01.00 17 230350 Lchen3 Wed Jun 28 22:13:36 2017
 * Pick Pack Ship Pick List is not showing Non-Inventory Item Description
 * issue 230350
 * use item description on co line
 *
 * SL9.01 16 220966 Lchen3 Mon Oct 17 01:28:36 2016
 * Pick List print wrong quantity when 'Assign Locations' is not selected on Pick Workbench
 * issue 220966
 * update sql to get good performance
 *
 * SL9.01 15 218984 jzhou Thu Sep 01 05:35:47 2016
 * SL9.01 RC7+ - Pick List generated from Pick Workbench is INCORRECT.
 * Issue 218984:
 * Change the logic to get the Picked Qty for serial tracked item.
 *
 * SL9.01 14 218984 jzhou Tue Aug 30 04:26:40 2016
 * SL9.01 RC7+ - Pick List generated from Pick Workbench is INCORRECT.
 * Issue 218984:
 * Change the condition to update the qty for the pick lists which are not assigned location.
 *
 * SL9.01 13 RS6298 Lqian2 Wed Dec 23 04:08:35 2015
 * RS6298
 *
 * SL9.01 12 RS6137 jzhou Mon Oct 19 22:44:49 2015
 * RS6137:
 * Change the logic to get the fields for non-inventory item.
 *
 * SL9.01 11 193145 Ltaylor2 Fri Apr 17 10:17:41 2015
 * Pick workbench report "Pick list" printed shows wrong quantity when printing Customer orders two ore more lines for same Serial track Item.
 * Issue 193145 - revamped logic to address qty_to_pick issues
 *
 * SL9.01 10 189885 Ltaylor2 Thu Jan 22 11:32:39 2015
 * Pick List report doubles amount to pick
 * Issue 189885 - add sequence to pll0/loc join
 *
 * SL9.00 9 180243 pgross Thu Jul 10 10:41:00 2014
 * Pick list generated using Pick Pack Ship problems for multiple lines for same item number with Apar 179819 loaded
 * group quanities to pick across lines
 *
 * SL8.04 8 RS4615 Lpeng Thu Dec 27 01:51:25 2012
 * RS4615:Adjust format
 *
 * SL8.04 7 RS4615 Lliu Wed Dec 26 22:19:30 2012
 * RS4615: Multi - Add Site within a Site Functionality.
 *
 * SL8.04 5 155924 Ehe Tue Dec 04 00:31:24 2012
 * with assign locations not checked the pick list shows 0.00 to pick.
 * Issue:155924
 * Update the Qty to show the correct value when user uncheck the assign location.
 *
 * SL8.04 4 150394 Ltaylor2 Mon Jun 18 18:17:47 2012
 * Generated Serial Number choices are not saved when you use a Serial Number tracked item, but they do save when you use a Serial Number/Lot Tracked item
 * Not showing serials for lot tracked items
 *
 * SL8.04 3 150240 Ltaylor2 Mon Jun 18 08:05:27 2012
 * Pick Lists and Bulk Pick Lists do not print
 * Issue 150240 Changed to use Picklist IDO instead of TmpShip IDO
 *
 * SL8.03 2 147564 Ltaylor2 Tue Apr 24 14:35:55 2012
 * RS5325 coding
 * Schema changes
 *
 * SL8.03 1 147564 Ltaylor2 Fri Apr 06 10:03:21 2012
 * RS5325 coding
 * RS5325 - Original
 *
 * $NoKeywords: $
 */
CREATE PROCEDURE dbo.extgen_Rpt_PickListSp (
    @ProcessId NVARCHAR(255)
   ,@pSite     SiteType       =  NULL
)
as
-- End of CallALTETPs.exe generated code.

   DECLARE @RptSessionID RowPointerType
   EXEC dbo.InitSessionContextSp
        @ContextName = 'Rpt_PickListSp'
      , @SessionID   = @RptSessionID OUTPUT
      , @Site        = @pSite
 
 declare
 @pick_list_id   PickListIdType
,@picker         UserNameType
,@cust_num       CustNumType
,@cust_seq       CustSeqType
,@whse           WhseType
,@pack_loc       LocType
,@qty_to_pick    QtyUnitType
,@qty_picked     QtyUnitType
,@item           ItemType
,@description    DescriptionType
,@u_m            UMType
,@loc            LocType
,@lot            LotType
,@ser_num        SerNumType
,@LotTracked     ListYesNoType
,@SerTracked     ListYesNoType 
,@RefNum         EmpJobCoPoRmaProjPsTrnNumType
,@RefLineSuf     Int
,@RefRelease     Int
,@CoItemRowPointer  RowPointerType --BH01
,@QtyBacked  QtyUnitType --JFOSTER
Declare
 @QtyUnitFormat     nvarchar(60)
,@PlacesQtyUnit     tinyint
SELECT @QtyUnitFormat = qty_unit_format,
       @PlacesQtyUnit = places_qty_unit
FROM invparms WITH (READUNCOMMITTED)
SET @QtyUnitFormat = dbo.FixMaskForCrystal( @QtyUnitFormat, dbo.GetWinRegDecGroup() )
declare @RptSet table(
 pick_list_id   PickListIdType
,picker         UserNameType
,cust_num       CustNumType
,cust_seq       CustSeqType
,whse           WhseType
,pack_loc       LocType
,qty_to_pick    QtyUnitType
,qty_picked     QtyUnitType
,item           ItemType
,description    DescriptionType
,u_m            UMType
,loc            LocType
,lot            LotType
,ser_num        SerNumType
,QtyUnitFormat  nvarchar(60)
,PlacesQtyUnit  tinyint
,LotTracked     ListYesNoType
,SerTracked     ListYesNoType  
,RefNum         EmpJobCoPoRmaProjPsTrnNumType
,RefLineSuf     Int
,RefRelease     Int
,CoItemRowPointer  RowPointerType --BH01
,QtyBacked  QtyUnitType  --JFOSTER
)
declare curs cursor local static for
select
 pick_list.pick_list_id
,pick_list.picker
,pick_list.cust_num
,pick_list.cust_seq
,pick_list.whse
,pick_list.pack_loc
,isnull((select sum(pll0.qty_to_pick) from pick_list_ref as plr0
   inner join coitem as c0 on
      c0.co_num = plr0.ref_num
      and c0.co_line = plr0.ref_line_suf
      and c0.co_release = plr0.ref_release
      and c0.item = coitem.item
   inner join pick_list_loc as pll0 on
      pll0.pick_list_id = loc.pick_list_id
      and ISNULL(pll0.loc,'') = ISNULL(loc.loc,'')
      and isnull(pll0.lot, '') = isnull(loc.lot, '')
      and pll0.sequence = loc.sequence
   where plr0.pick_list_id = loc.pick_list_id
   and plr0.sequence = loc.sequence
   ),isnull(ref.qty_to_pick, 0))
, CASE WHEN serial.ser_num IS NOT NULL THEN 1 ELSE ISNULL(loc.qty_picked,0) END
,coitem.item
,coitem.description
,ISNULL(item.u_m,coitem.u_m)
,loc.loc
,loc.lot
,serial.ser_num
,@QtyUnitFormat as 'QtyUnitFormat'
,@PlacesQtyUnit as 'PlacesQtyUnit'
,ISNULL(item.lot_tracked,0)
,ISNULL(item.serial_tracked,0)
,ref.ref_num
,ref.ref_line_suf
,ref.ref_release
,coitem.RowPointer      --BH01
,sum(isnull(ref.qty_to_pick, 0) - ISNULL(loc.qty_picked,0)) --JFOSTER
from tmp_pick_list
join pick_list on pick_list.pick_list_id = tmp_pick_list.pick_list_id
join pick_list_ref ref on ref.pick_list_id = pick_list.pick_list_id
left outer join pick_list_loc loc on loc.pick_list_id = ref.pick_list_id
                                 and loc.sequence = ref.sequence                                
left outer join pick_list_serial serial on serial.pick_list_id = loc.pick_list_id
                                       and serial.sequence = loc.sequence
                                       and serial.loc = loc.loc
                                       and isnull(serial.lot, '') = isnull(loc.lot, '')                                     
left outer join coitem on coitem.co_num = ref.ref_num
                      and coitem.co_line = ref.ref_line_suf
                      and coitem.co_release = ref.ref_release
left outer join item on item.item = coitem.item
where tmp_pick_list.process_id = @ProcessId
open curs
while 1 = 1
begin
    fetch curs into
     @pick_list_id 
    ,@picker        
    ,@cust_num      
    ,@cust_seq      
    ,@whse          
    ,@pack_loc      
    ,@qty_to_pick   
    ,@qty_picked    
    ,@item          
    ,@description   
    ,@u_m          
    ,@loc          
    ,@lot           
    ,@ser_num       
    ,@QtyUnitFormat 
    ,@PlacesQtyUnit 
    ,@LotTracked  
    ,@SerTracked
    ,@RefNum            
    ,@RefLineSuf
    ,@RefRelease  
 ,@CoItemRowPointer  --BH01 
 ,@QtyBacked  --JFOSTER
    if @@FETCH_STATUS <> 0 break
    if not exists (select 1 from @RptSet r
                   where r.pick_list_id = @pick_list_id
                     and r.RefNum = @RefNum
                     and r.RefLineSuf = @RefLineSuf
                     and r.RefRelease = @RefRelease
                     and r.loc = @loc
                     and isnull(r.lot,'') = isnull(@lot,'')
                     )
       insert into @RptSet
       (pick_list_id, picker ,cust_num, cust_seq, whse, pack_loc, qty_to_pick, qty_picked, item, description, u_m, loc ,lot, ser_num, QtyUnitFormat, PlacesQtyUnit, LotTracked, SerTracked, RefNum, RefLineSuf, RefRelease, CoItemRowPointer, QtyBacked)
       values
       (@pick_list_id, @picker, @cust_num, @cust_seq, @whse, @pack_loc, @qty_to_pick, @qty_picked, @item, @description, @u_m, @loc, @lot, @ser_num, @QtyUnitFormat, @PlacesQtyUnit, @LotTracked, @SerTracked, @RefNum, @RefLineSuf, @RefRelease, @CoItemRowPointer, @QtyBacked)
    else
       insert into @RptSet
       (pick_list_id, picker, cust_num, cust_seq, whse, pack_loc, qty_to_pick, qty_picked, item, description, u_m, loc ,lot, ser_num, QtyUnitFormat, PlacesQtyUnit, LotTracked, SerTracked, RefNum, RefLineSuf, RefRelease, CoItemRowPointer, QtyBacked)
       values
       (@pick_list_id, @picker, @cust_num, @cust_seq, @whse, @pack_loc, 0, @qty_picked, @item, @description, @u_m, @loc, @lot, @ser_num, @QtyUnitFormat, @PlacesQtyUnit, @LotTracked, @SerTracked, @RefNum, @RefLineSuf, @RefRelease, @CoItemRowPointer, @QtyBacked)
end
close curs
deallocate curs
update pick_list
set printed = 1
from @RptSet r
where r.pick_list_id = pick_list.pick_list_id
select * from @RptSet order by pick_list_id, item, loc, lot, ser_num
--select
--rs.pick_list_id  
--,rs.picker        
--,rs.cust_num     
--,rs.cust_seq      
--,rs.whse          
--,rs.pack_loc      
--,rs.qty_to_pick   
--,rs.qty_picked    
--,rs.item          
--,rs.description   
--,rs.u_m           
--,rs.loc          
--,rs.lot           
--,rs.ser_num       
--,rs.QtyUnitFormat 
--,rs.PlacesQtyUnit 
--,rs.LotTracked    
--,rs.SerTracked       
--,rs.RefNum        
--,rs.RefLineSuf    
--,rs.RefRelease 
--,jcmitem.RowPointer  
--from @RptSet rs
--INNER JOIN item jcmitem with (nolock) on jcmitem.item=rs.item
--order by rs.pick_list_id, rs.item, rs.loc, rs.lot, rs.ser_num
DELETE tmp_pick_list WHERE tmp_pick_list.process_id = @ProcessId
EXEC dbo.CloseSessionContextSp  @RptSessionID
  • At first glance I'm not seeing what's wrong, but shouldn't your loc.qty_picked take into consideration the Serial case?
    I see that loc.qty_picked gets returned by itself, so it is probably good, but I'd probably add ref.qty_to_pick somewhere by itself just to make sure it is bringing expected values back.