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
@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
@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 =
@ProcessIdopen 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 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 