· 8 years ago · Dec 08, 2017, 04:14 PM
1/***************************************************************************************************************************************************************************
2ID : v5.0.153.1
3Author : Sharad Nair
4Date : 1/10/2017
5This script modifies some of the existing dws views, triggers and stored procedures to follow a new naming conventions
6Naming convention : dms_vw_xxx for views
7 : dms_sp_xxx for stored procedures
8 : dms_tr_xxx for triggers
9***************************************************************************************************************************************************************************/
10IF EXISTS
11(
12 SELECT 1
13 FROM sys.views
14 WHERE name = 'vw_dws_accountview'
15 AND type = 'v'
16)
17 DROP VIEW vw_dws_accountview;
18GO
19
20IF EXISTS
21(
22 SELECT 1
23 FROM sys.triggers
24 WHERE name = 'dws_accountviewtrigger'
25 AND type = 'TR'
26)
27 DROP TRIGGER dws_accountviewtrigger;
28GO
29
30IF EXISTS
31(
32 SELECT 1
33 FROM sys.procedures
34 WHERE name = 'dws_gettransactions_for_invoicing'
35 AND type = 'P'
36)
37 DROP PROCEDURE dws_gettransactions_for_invoicing;
38GO
39
40/***************************************************************************************************************************************************************************
41Object: Table [dbo].[ce_ims_items] Script Date: 20/09/2017 08:54:42
42***************************************************************************************************************************************************************************/
43IF COL_LENGTH('ce_ims_items','ASNPalletNo') IS NULL
44BEGIN
45 ALTER table ce_ims_items ADD ASNPalletNo nvarchar(50)
46END
47GO
48
49/***************************************************************************************************************************************************************************
50Object: Table [dbo].[dws_invoice_line] Script Date: 27/09/2017 10:55:42
51***************************************************************************************************************************************************************************/
52IF COL_LENGTH('dws_invoice_line','UnitPrice') IS NULL
53BEGIN
54 ALTER TABLE dbo.dws_invoice_line ADD UnitPrice decimal(18, 6) NULL
55 ALTER TABLE dbo.dws_invoice_line ADD CONSTRAINT DF_dws_invoice_line_UnitPrice DEFAULT 0 FOR UnitPrice
56END
57GO
58
59/***************************************************************************************************************************************************************************
60Object: Table [dbo].[dws_invoice_header] Script Date: 27/09/2017 10:55:42
61***************************************************************************************************************************************************************************/
62IF COL_LENGTH('dws_invoice_header','IsProforma') IS NULL
63BEGIN
64 ALTER TABLE dbo.dws_invoice_header ADD IsProforma bit default(0)
65 ALTER TABLE dbo.dws_invoice_header ADD CONSTRAINT DF_dws_invoice_header_IsProforma DEFAULT 0 FOR IsProforma
66END
67GO
68
69/***************************************************************************************************************************************************************************
70Object: View [dbo].[dws_vw_account] Script Date: 06/09/2017 08:54:42
71***************************************************************************************************************************************************************************/
72IF EXISTS
73(
74 SELECT 1
75 FROM sys.views
76 WHERE name = 'dws_vw_account'
77 AND type = 'v'
78)
79 DROP VIEW dws_vw_account;
80GO
81
82CREATE VIEW [dbo].[dws_vw_account]
83AS
84 SELECT dfd.RecordID AS DataFileDataRecordID,
85 dfd.DatafileDataID,
86 dfd.DatafileID,
87 dfd.Code,
88 dfd.Text1,
89 dfd.Text2,
90 dfd.Text3,
91 dfd.Text4,
92 dfd.Active,
93 trd.*,
94 ctrd.NominalCode,
95 ctrd.LocalCode,
96 ctrd.GLN,
97 ctrd.Address,
98 ctrd.CountryCode,
99 ctrd.PostalCode,
100 ctrd.ContactName,
101 ctrd.AccountIsOnHold,
102 ctrd.CreditLimit,
103 ctrd.DefaultOrderPriority,
104 ctrd.OwnerAccountCode,
105 cdfd.GS1Code,
106 cdfd.NValue1,
107 cdfd.NValue2,
108 cdfd.NValue3,
109 cdfd.NValue4,
110 cdfd.NValue5,
111 cdfd.NValue6,
112 cdfd.BNValue1,
113 cdfd.BNValue2,
114 cdfd.DNValue1,
115 cdfd.DNValue2,
116 cdfd.DNValue3,
117 cdfd.DNValue4,
118 cdfd.AValue1,
119 cdfd.AValue2,
120 cdfd.AValue3,
121 cdfd.AValue4,
122 cdfd.AValue5,
123 cdfd.AValue6,
124 cdfd.AValue7,
125 cdfd.AValue8,
126 cdfd.Date1,
127 cdfd.Date2,
128 cdfd.Flag1,
129 cdfd.Flag2,
130 cdfd.Flag3,
131 cdfd.Flag4,
132 cdfd.ImageGUID1,
133 cdfd.ImageGUID2,
134 cdfd.eDocumentGUID1,
135 cdfd.eDocumentGUID2,
136 cdfd.Image1,
137 cdfd.Image2,
138 cdfd.StatusCode,
139 cdfd.LastUpdatedTimestamp,
140 cdfd.LastUpdatedBy,
141 cdfd.ERPIsMaster
142 FROM
143 dbo.datafiles_data dfd
144 JOIN
145 dbo.ce_datafiles_data cdfd
146 ON dfd.DatafileDataID = cdfd.DatafileDataID
147 JOIN
148 dbo.datafiles df
149 ON dfd.DatafileID = df.DatafileID
150 AND cdfd.DatafileDataID = dfd.DatafileDataID
151 JOIN
152 dbo.traders trd
153 ON dfd.DatafileDataID = trd.TraderID
154 JOIN
155 dbo.ce_traders ctrd
156 ON trd.TraderID = ctrd.TraderID
157 WHERE df.DatafileID = 1312;
158GO
159
160/***************************************************************************************************************************************************************************
161Object: Trigger [dbo].[dws_tr_accountview] Script Date: 06/09/2017 08:54:42
162***************************************************************************************************************************************************************************/
163IF EXISTS
164(
165 SELECT 1
166 FROM sys.triggers
167 WHERE name = 'dws_tr_vw_account'
168 AND type = 'TR'
169)
170 DROP TRIGGER dws_tr_vw_account;
171GO
172
173CREATE TRIGGER [dbo].[dws_tr_vw_account]
174ON [dbo].[dws_vw_account]
175INSTEAD OF INSERT, UPDATE
176AS
177 BEGIN
178 --Insert or update the datafile_data
179 MERGE dbo.datafiles_data WITH (HOLDLOCK) AS dfd
180 USING INSERTED AS ins
181 ON ins.DatafileDataID = dfd.DatafileDataID
182 WHEN MATCHED THEN UPDATE SET Code = ins.AccountNo,
183 Text1 = ins.Text1,
184 Text2 = ins.Text2,
185 Text3 = ins.Text3,
186 Text4 = ins.Text4,
187 Active = ins.Active
188 WHEN NOT MATCHED BY TARGET
189 THEN INSERT
190 (
191 DatafileID,
192 Code,
193 Text1,
194 Text2,
195 Text3,
196 Text4,
197 Active
198 )
199 VALUES
200 (ins.DatafileID, ins.AccountNo, ins.Text1, ins.Text2, ins.Text3, ins.Text4, ins.Active);
201
202 SET NOCOUNT ON;
203 DECLARE @DatafileDataID INT;
204 SELECT @DatafileDataID = SCOPE_IDENTITY();
205 SET NOCOUNT OFF;
206
207 --Insert or update the ce_datafile_data
208 MERGE dbo.ce_datafiles_data WITH (HOLDLOCK) AS ce
209 USING INSERTED AS ins
210 ON ins.DatafileDataID = ce.DatafileDataID
211 WHEN NOT MATCHED BY TARGET
212 THEN INSERT
213 (
214 DatafileDataID,
215 GS1Code,
216 NValue1,
217 NValue2,
218 NValue3,
219 NValue4,
220 NValue5,
221 NValue6,
222 BNValue1,
223 BNValue2,
224 DNValue1,
225 DNValue2,
226 DNValue3,
227 DNValue4,
228 AValue1,
229 AValue2,
230 AValue3,
231 AValue4,
232 AValue5,
233 AValue6,
234 AValue7,
235 AValue8,
236 Date1,
237 Date2,
238 Flag1,
239 Flag2,
240 Flag3,
241 Flag4,
242 ImageGUID1,
243 ImageGUID2,
244 eDocumentGUID1,
245 eDocumentGUID2,
246 Image1,
247 Image2,
248 StatusCode,
249 LastUpdatedTimestamp,
250 LastUpdatedBy,
251 ERPIsMaster
252 )
253 VALUES
254 ( @DatafileDataID,
255 ins.GS1Code,
256 ins.NValue1,
257 ins.NValue2,
258 ins.NValue3,
259 ins.NValue4,
260 ins.NValue5,
261 ins.NValue6,
262 ins.BNValue1,
263 ins.BNValue2,
264 ins.DNValue1,
265 ins.DNValue2,
266 ins.DNValue3,
267 ins.DNValue4,
268 ins.AValue1,
269 ins.AValue2,
270 ins.AValue3,
271 ins.AValue4,
272 ins.AValue5,
273 ins.AValue6,
274 ins.AValue7,
275 ins.AValue8,
276 ins.Date1,
277 ins.Date2,
278 ins.Flag1,
279 ins.Flag2,
280 ins.Flag3,
281 ins.Flag4,
282 ins.ImageGUID1,
283 ins.ImageGUID2,
284 ins.eDocumentGUID1,
285 ins.eDocumentGUID2,
286 ins.Image1,
287 ins.Image2,
288 ins.StatusCode,
289 ins.LastUpdatedTimestamp,
290 ins.LastUpdatedBy,
291 ins.ERPIsMaster
292 )
293 WHEN MATCHED
294 THEN UPDATE SET GS1Code = ins.GS1Code,
295 NValue1 = ins.NValue1,
296 NValue2 = ins.NValue2,
297 NValue3 = ins.NValue3,
298 NValue4 = ins.NValue4,
299 NValue5 = ins.NValue5,
300 NValue6 = ins.NValue6,
301 BNValue1 = ins.BNValue1,
302 BNValue2 = ins.BNValue2,
303 DNValue1 = ins.DNValue1,
304 DNValue2 = ins.DNValue2,
305 DNValue3 = ins.DNValue3,
306 DNValue4 = ins.DNValue4,
307 AValue1 = ins.AValue1,
308 AValue2 = ins.AValue2,
309 AValue3 = ins.AValue3,
310 AValue4 = ins.AValue4,
311 AValue5 = ins.AValue5,
312 AValue6 = ins.AValue6,
313 AValue7 = ins.AValue7,
314 AValue8 = ins.AValue8,
315 Date1 = ins.Date1,
316 Date2 = ins.Date2,
317 Flag1 = ins.Flag1,
318 Flag2 = ins.Flag2,
319 Flag3 = ins.Flag3,
320 Flag4 = ins.Flag4,
321 ImageGUID1 = ins.ImageGUID1,
322 ImageGUID2 = ins.ImageGUID2,
323 eDocumentGUID1 = ins.eDocumentGUID1,
324 eDocumentGUID2 = ins.eDocumentGUID2,
325 Image1 = ins.Image1,
326 Image2 = ins.Image2,
327 StatusCode = ins.StatusCode,
328 LastUpdatedTimestamp = ins.LastUpdatedTimestamp,
329 LastUpdatedBy = ins.LastUpdatedBy,
330 ERPIsMaster = ins.ERPIsMaster;
331 -- Insert or update into the trader file
332 MERGE dbo.traders WITH (HOLDLOCK) AS td
333 USING INSERTED AS ins
334 ON ins.DatafileDataID = td.TraderID
335 WHEN NOT MATCHED BY TARGET
336 THEN INSERT
337 (
338 TraderID,
339 VATNo,
340 WebAddress,
341 eMailAddress,
342 PhoneNo,
343 FaxNo,
344 AccountNo,
345 DefaultVATRateCode,
346 PlantRegistrationNo,
347 DefaultCurrencyCode,
348 ANA,
349 Comments,
350 CreatedDate
351 )
352 VALUES
353 ( @DatafileDataID,
354 ins.VATNo,
355 ins.WebAddress,
356 ins.eMailAddress,
357 ins.PhoneNo,
358 ins.FaxNo,
359 ins.AccountNo,
360 ins.DefaultVATRateCode,
361 ins.PlantRegistrationNo,
362 ins.DefaultCurrencyCode,
363 ins.ANA,
364 ins.Comments,
365 ins.CreatedDate
366 )
367 WHEN MATCHED
368 THEN UPDATE SET VATNo = ins.VATNo,
369 WebAddress = ins.WebAddress,
370 eMailAddress = ins.eMailAddress,
371 PhoneNo = ins.PhoneNo,
372 FaxNo = ins.FaxNo,
373 AccountNo = ins.AccountNo,
374 DefaultVATRateCode = ins.DefaultVATRateCode,
375 td.DefaultCurrencyCode = ins.DefaultCurrencyCode,
376 PlantRegistrationNo = ins.PlantRegistrationNo,
377 ANA = ins.ANA,
378 Comments = ins.Comments;
379
380 -- Insert or update into the ce_trader file
381 MERGE dbo.ce_traders WITH (HOLDLOCK) AS ctd
382 USING INSERTED AS ins
383 ON ins.DatafileDataID = ctd.TraderID
384 WHEN NOT MATCHED BY TARGET
385 THEN INSERT
386 (
387 TraderID,
388 NominalCode,
389 LocalCode,
390 GLN,
391 Address,
392 CountryCode,
393 PostalCode,
394 ContactName,
395 AccountIsOnHold,
396 CreditLimit,
397 DefaultOrderPriority,
398 OwnerAccountCode
399 )
400 VALUES
401 ( @DatafileDataID,
402 ins.NominalCode,
403 ins.LocalCode,
404 ins.GLN,
405 ins.Address,
406 ins.CountryCode,
407 ins.PostalCode,
408 ins.ContactName,
409 ins.AccountIsOnHold,
410 ins.CreditLimit,
411 ins.DefaultOrderPriority,
412 ins.OwnerAccountCode
413 )
414 WHEN MATCHED
415 THEN UPDATE SET NominalCode = ins.NominalCode,
416 LocalCode = ins.LocalCode,
417 GLN = ins.GLN,
418 Address = ins.Address,
419 CountryCode = ins.CountryCode,
420 PostalCode = ins.PostalCode,
421 AccountIsOnHold = ins.AccountIsOnHold,
422 CreditLimit = ins.CreditLimit,
423 DefaultOrderPriority = ins.DefaultOrderPriority,
424 OwnerAccountCode = ins.OwnerAccountCode;
425 END;
426GO
427
428/***************************************************************************************************************************************************************************
429Object: StoredProcedure [dbo].[dws_sp_gettransactions_for_invoicing] Script Date: 06/09/2017 17:08:16 ******
430Returns a list of transactions for a given account code and a date range which can are outstanding for invoicing
431**************************************************************************************************************************************************************************/
432IF EXISTS
433(
434 SELECT 1
435 FROM sys.procedures
436 WHERE name = 'dws_sp_gettransactions_for_invoicing' AND type = 'P'
437)
438 DROP PROCEDURE [dws_sp_gettransactions_for_invoicing];
439GO
440
441CREATE PROCEDURE [dbo].[dws_sp_gettransactions_for_invoicing]
442 -- Add the parameters for the stored procedure here
443 @AccountCode VARCHAR(30),
444 @StartDate DATETIME,
445 @EndDate DATETIME
446AS
447 BEGIN
448 -- SET NOCOUNT ON added to prevent extra result sets from
449 -- interfering with SELECT statements.
450 SET NOCOUNT ON;
451
452 -- Insert statements for procedure here
453 SELECT trn.AccountCode,
454 adf.DatafileDataID AS AccountID,
455 TransactionType,
456 tdf.NValue1 AS ServiceId,
457 sdf.Code AS ServiceCode,
458 sdf.Text1 AS ServiceDescription,
459 SUM(Units) AS TotalUnits,
460 SUM(trn.GrossWeight) AS TotalGrossWeight,
461 SUM(trn.NetWeight) AS TotalNetWeight,
462 SUM(trn.NoOfCases) AS NoOfCases,
463 SUM(trn.NoOfPacks) AS NoOfPacks,
464 SUM(sdf.DNValue1 * trn.NoOfCases) AS TotalChargeAmount
465 FROM
466 dbo.dws_vw_ce_dms_transactions trn
467 JOIN
468 vw_ce_datafiles_data tdf
469 ON tdf.Code = trn.TransactionType
470 JOIN
471 dbo.vw_ce_datafiles_data sdf
472 ON sdf.DatafileDataID = tdf.NValue1
473 JOIN
474 dbo.vw_ce_datafiles_data adf
475 ON adf.Code = trn.AccountCode
476 WHERE tdf.DatafileID = 1601
477 AND sdf.DatafileID = 1600
478 AND trn.AccountCode = @AccountCode
479 AND CONVERT(DATE, trn.TransactionDateTime)
480 BETWEEN @StartDate AND @EndDate
481 GROUP BY trn.AccountCode,
482 adf.DatafileDataID,
483 trn.TransactionType,
484 tdf.NValue1,
485 sdf.Code,
486 sdf.Text1;
487 END;
488GO
489
490/***************************************************************************************************************************************************************************
491Object: StoredProcedure [dbo].[dws_sp_get_available_locations] Script Date: 06/09/2017 17:08:16 ******
492Returns a list of locations that don't contain stock
493**************************************************************************************************************************************************************************/
494IF EXISTS
495(
496 SELECT 1
497 FROM sys.procedures
498 WHERE name = 'dws_sp_get_available_locations'
499 AND type = 'P'
500)
501 DROP PROCEDURE [dws_sp_get_available_locations];
502GO
503
504CREATE PROCEDURE [dbo].[dws_sp_get_available_locations]
505@DepartmentCode varchar(5)
506AS
507BEGIN
508 -- SET NOCOUNT ON added to prevent extra result sets from
509 -- interfering with SELECT statements.
510 SET NOCOUNT ON;
511
512 select l.* from vw_df_plant_locations_file l
513 left outer join vw_ce_ims_items i
514 on l.locationcode = i.locationcode
515 and l.departmentcode = i.warehousecode
516 and i.state IN('ok', 'in_transit') -- added in transit to reserved locations
517 and i.WarehouseCode = @DepartmentCode
518 where i.locationcode is null
519 and l.DepartmentCode = @DepartmentCode
520 order by LocationCode
521END
522GO
523
524IF COL_LENGTH('ce_oms_wo_header','Transporter') IS NULL
525 BEGIN
526 ALTER table ce_oms_wo_header ADD [Transporter][VARCHAR](50) NULL;
527END
528GO
529
530IF COL_LENGTH('ce_oms_wo_header','SealNo') IS NULL
531 BEGIN
532 ALTER table ce_oms_wo_header ADD [SealNo][VARCHAR](20) NULL;
533END
534GO
535
536IF COL_LENGTH('ce_oms_wo_header','VehicleRegistration') IS NULL
537 BEGIN
538 ALTER table ce_oms_wo_header ADD [VehicleRegistration] [VARCHAR](20) NULL;
539END
540GO
541
542IF COL_LENGTH('ce_oms_wo_header','DriverName') IS NULL
543 BEGIN
544 ALTER table ce_oms_wo_header ADD [DriverName] [VARCHAR](50) NULL;
545END
546GO
547
548IF COL_LENGTH('ce_oms_wo_header','DriverNo') IS NULL
549 BEGIN
550 ALTER table ce_oms_wo_header ADD [DriverNo] [VARCHAR](20) NULL;
551END
552GO
553
554IF COL_LENGTH('ce_oms_wo_header','LoadingBayID') IS NULL
555 BEGIN
556 ALTER table ce_oms_wo_header ADD [LoadingBayID] [INT] NULL;
557END
558GO
559
560IF COL_LENGTH('ce_oms_wo_header','ShipmentWONo') IS NULL
561 BEGIN
562 ALTER table ce_oms_wo_header ADD [ShipmentWONo] [VARCHAR](20) NULL;
563END
564GO
565
566IF COL_LENGTH('ce_oms_wo_header','Temperature') IS NULL
567 BEGIN
568 ALTER table ce_oms_wo_header ADD [Temperature] [DECIMAL](18,3) NULL;
569END
570GO
571
572IF COL_LENGTH('ce_oms_wo_header','HaulierID') IS NULL
573 BEGIN
574 ALTER table ce_oms_wo_header ADD [HaulierID] [INT] NULL;
575END
576GO
577
578IF COL_LENGTH('ce_oms_wo_header','ContainerNo') IS NULL
579 BEGIN
580 ALTER table ce_oms_wo_header ADD [ContainerNo] [VARCHAR](20) NULL;
581END
582GO
583
584IF COL_LENGTH('ce_oms_wo_header','CertNo') IS NULL
585 BEGIN
586 ALTER table ce_oms_wo_header ADD [CertNo] [VARCHAR](20) NULL;
587END
588GO
589
590IF COL_LENGTH('ce_oms_wo_header','FridgeNo') IS NULL
591 BEGIN
592 ALTER table ce_oms_wo_header ADD [FridgeNo] [VARCHAR](20) NULL;
593END
594GO
595
596IF COL_LENGTH('ce_oms_wo_header','EstimatedDispatchDateTime') IS NULL
597 BEGIN
598 ALTER table ce_oms_wo_header ADD [EstimatedDispatchDateTime] [DATETIME] NULL;
599END
600GO
601
602/***************************************************************************************************************************************************************************
603Object: View [dbo].[dws_vw_ce_oms_order_headert] Script Date: 06/09/2017 08:54:42
604***************************************************************************************************************************************************************************/
605IF EXISTS
606(
607 SELECT 1
608 FROM sys.views
609 WHERE name = 'dws_vw_ce_oms_order_header' AND type = 'v'
610)
611 DROP VIEW dws_vw_ce_oms_order_header;
612GO
613
614CREATE VIEW [dbo].[dws_vw_ce_oms_order_header]
615AS
616SELECT wo.WOType
617 ,wo.WONo
618 ,wo.WOVersion
619 ,cwo.OwnerAccountCode
620 ,cwo.PlantCode
621 ,wo.WODescription
622 ,wo.WOSubType
623 ,wo.WOStatusCode
624 ,wo.WOStateCode
625 ,wo.WOSectionCode
626 ,wo.WOPriority
627 ,wo.WOValidFromDate
628 ,wo.WOValidToDate
629 ,wo.WOCreatedTimestamp
630 ,wo.WOCreatedBy
631 ,wo.WOIsTemplate
632 ,wo.WOTemplateWONo
633 ,wo.WOAllowOverProduce
634 ,wo.WOAllowUnderProduce
635 ,wo.WOAllowSubstitution
636 ,wo.WONotes
637 ,wo.WOLocking
638 ,wo.WOUserAcksNewList
639 ,wo.WOUserAcksUpdatedList
640 ,ord.TraderID
641 ,ord.PickerID
642 ,ISNULL(ord.LocalOrderNo, wo.WONo) AS LocalOrderNo
643 ,ord.TraderOrderRefNo
644 ,ord.TraderNotes
645 ,ord.DeliverToAddressCode
646 ,ord.EstDispatchDate
647 ,ord.EstDeliverDate
648 ,ord.DeliveryDocketNo
649 ,ord.ReleasedToERPDate
650 ,CAST(COALESCE (cwo.Flag3, 0) AS BIT) AS TransferOrder
651 ,CAST(COALESCE (cwo.Flag2, 0) AS BIT) AS IsOnHold
652 ,COALESCE(cwo.AValue1, '') AS RouteCode
653 ,COALESCE(cwo.AValue3,'') AS Depot
654 ,ord.NominalAccountCode AS AccountCode
655 ,cwo.Date1 AS PostedReceiptDate
656 ,cwo.SealNo
657 ,cwo.Transporter
658 ,cwo.Temperature
659 ,cwo.VehicleRegistration
660FROM dbo.oms_wo_header wo LEFT OUTER JOIN
661 dbo.ce_oms_wo_header cwo ON wo.WOType = cwo.WOType AND wo.WONo = cwo.WONo AND wo.WOVersion = cwo.WOVersion LEFT OUTER JOIN
662 dbo.oms_order_header ord ON wo.WOType = ord.OrderType AND wo.WONo = ord.OrderNo AND wo.WOVersion = ord.OrderVersion
663WHERE wo.WOVersion = 0
664GO
665
666IF EXISTS
667(
668 SELECT 1
669 FROM sys.procedures
670 WHERE name = 'sp_ce_dispatch_reports_support' AND type = 'P'
671)
672 DROP PROCEDURE sp_ce_dispatch_reports_support;
673GO
674
675CREATE PROCEDURE [dbo].[sp_ce_dispatch_reports_support]
676
677 @Operation VARCHAR(60),
678 @WONo VARCHAR(40) = NULL,
679 @FromDate DATETIME = NULL,
680 @ToDate DATETIME = NULL,
681 @ReturnResult BIT = 1
682
683AS
684
685 DECLARE @SetRtn INT = 1
686 DECLARE @Message VARCHAR(200)
687
688 IF @Operation = 'GET_DISPATCH_SUMMARY' GOTO SP_GET_DISPATCH_SUMMARY
689 IF @Operation = 'GET_DISPATCH_SUMMARY_PALLETS' GOTO SP_GET_DISPATCH_SUMMARY_PALLETS
690
691 SET @Message = 'Unknown Operation ' + @Operation
692 GOTO GeneralErr
693
694 /**********************/
695 SP_GET_DISPATCH_SUMMARY:
696
697 SELECT cont.WONo
698 , ContainerDescription
699 , ContainerHaulierID
700 , ContainerNo
701 , SealNo
702 , CertNo
703 , RegNo
704 , plb.LoadingBayCode
705 , haul.HaulierName
706 , ISNULL(plb.LoadingBayDisplayDescription, '<NOT DEFINED>') AS LoadingBayDisplayDescription
707 , SUM(dms.Units) AS ShippedQty
708 , SUM(dms.NetWeight) AS ShippedNetWeight
709 , COUNT(DISTINCT ToParentItemID) AS NoOfPallets
710 , COUNT(DISTINCT dms.WONo) AS NoOfOrders
711 FROM vw_ce_dms_transactions AS dms
712 JOIN vw_ce_oms_so_containers AS cont ON dms.AVAlue4 = cont.ContainerNo
713 LEFT JOIN vw_df_haulier_file AS haul ON haul.HaulierID = cont.ContainerHaulierID
714 LEFT JOIN vw_df_plant_loading_bays_file AS plb ON plb.LoadingBayCode = cont.LoadingBayCode
715 WHERE dms.TransactionDateTime BETWEEN @FromDate AND @ToDate
716 AND dms.TransactionType IN ('DISPATCH_LOADING')
717 AND ContainerVersion = 0
718 AND dms.Cancelled = 0
719 GROUP BY cont.WONo, ContainerDescription, ContainerHaulierID, haul.HaulierName, ContainerNo, SealNo, CertNo, RegNo, plb.LoadingBayCode, plb.LoadingBayDisplayDescription
720
721 GOTO ExitSP
722
723
724 /******************************/
725 SP_GET_DISPATCH_SUMMARY_PALLETS:
726
727 SELECT ipn.LoadingPalletNo
728 , dms.WONo
729 , SUM(dms.Units) AS ShippedQty
730 , SUM(dms.NetWeight) AS ShippedNetWeight
731 , hdr.LocalOrderNo
732 , hdr.WOStatusCode
733 , hdr.TraderOrderRefNo
734 , hdr.WODescription
735 , dms.ToParentItemID AS IPNID
736 , RIGHT(dms.ToParentItemID, 6) AS PalletNo
737 , ipn.PickingPalletNo
738 FROM vw_ce_dms_transactions AS dms
739 LEFT JOIN vw_ce_oms_order_header AS cont ON dms.AValue3 = cont.WONo AND cont.WOType = 'SOC'
740 LEFT JOIN vw_ce_oms_order_header AS hdr ON dms.WONo = hdr.WONo AND hdr.WOType = dms.WOType
741 LEFT OUTER JOIN vw_ce_ims_ipn_inventory_base AS ipn ON dms.ToParentItemID = ipn.IPNID
742 WHERE dms.TransactionType IN ('DISPATCH_LOADING')
743 AND dms.TransactionDateTime BETWEEN @FromDate AND @ToDate
744 AND dms.AValue3 = @WONo
745 AND hdr.WOVersion = 0
746 AND Cancelled = 0
747 GROUP BY ipn.LoadingPalletNo, dms.WONo, hdr.WOStatusCode, hdr.LocalOrderNo, hdr.TraderOrderRefNo, dms.ToParentItemID, ipn.PickingPalletNo, hdr.WODescription
748 ORDER BY ipn.LoadingPalletNo
749
750 GOTO ExitSP
751
752
753 /*********/
754 GeneralErr:
755
756 PRINT @Message
757 SET @SetRtn = -1
758 GOTO ExitSP
759
760
761 /*****/
762 ExitSP:
763
764 IF @ReturnResult = 1 OR @SetRtn < 0
765 SELECT @SetRtn AS Result, @Message AS [Message]
766
767 RETURN @SetRtn
768GO
769
770IF COL_LENGTH('ce_oms_wo_header','Transporter') IS NULL
771 BEGIN
772 ALTER table ce_oms_wo_header ADD [Transporter][VARCHAR](50) NULL;
773END
774GO
775
776IF COL_LENGTH('ce_oms_wo_header','SealNo') IS NULL
777 BEGIN
778 ALTER table ce_oms_wo_header ADD [SealNo][VARCHAR](20) NULL;
779END
780GO
781
782IF COL_LENGTH('ce_oms_wo_header','VehicleRegistration') IS NULL
783 BEGIN
784 ALTER table ce_oms_wo_header ADD [VehicleRegistration] [VARCHAR](20) NULL;
785END
786GO
787
788IF COL_LENGTH('ce_oms_wo_header','DriverName') IS NULL
789 BEGIN
790 ALTER table ce_oms_wo_header ADD [DriverName] [VARCHAR](50) NULL;
791END
792GO
793
794IF COL_LENGTH('ce_oms_wo_header','DriverNo') IS NULL
795 BEGIN
796 ALTER table ce_oms_wo_header ADD [DriverNo] [VARCHAR](20) NULL;
797END
798GO
799
800IF COL_LENGTH('ce_oms_wo_header','LoadingBayID') IS NULL
801 BEGIN
802 ALTER table ce_oms_wo_header ADD [LoadingBayID] [INT] NULL;
803END
804GO
805
806IF COL_LENGTH('ce_oms_wo_header','ShipmentWONo') IS NULL
807 BEGIN
808 ALTER table ce_oms_wo_header ADD [ShipmentWONo] [VARCHAR](20) NULL;
809END
810GO
811
812IF COL_LENGTH('ce_oms_wo_header','Temperature') IS NULL
813 BEGIN
814 ALTER table ce_oms_wo_header ADD [Temperature] [DECIMAL](18,3) NULL;
815END
816GO
817
818IF COL_LENGTH('ce_dms_transactions','InvoiceNumber') IS NULL
819 BEGIN
820 ALTER table ce_dms_transactions ADD InvoiceNumber nvarchar(15) NULL;
821END
822GO
823
824IF COL_LENGTH('dws_invoice_header','PostedDate') IS NULL
825 BEGIN
826 ALTER table dws_invoice_header ADD PostedDate datetime NULL;
827END
828GO
829
830IF COL_LENGTH('ce_ims_snapshot_items','ASNPalletNo') IS NOT NULL
831 BEGIN
832 ALTER table ce_ims_snapshot_items ALTER COLUMN ASNPalletNo nvarchar(50) NULL;
833END
834GO
835
836IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'dws_invoice_line')
837BEGIN
838IF NOT EXISTS(SELECT 1 FROM sys.columns
839 WHERE Name = N'ProductID'
840 AND Object_ID = Object_ID(N'dbo.dws_invoice_line'))
841BEGIN
842Â Â ALTER TABLE dws_invoice_line ADD ProductID INT NULL CONSTRAINT FK_dws_invoice_line_datafiles_data_productid FOREIGN KEY REFERENCES datafiles_data(DatafileDataID)
843END
844IF NOT EXISTS(SELECT 1 FROM sys.columns
845 WHERE Name = N'ChargeBasis'
846 AND Object_ID = Object_ID(N'dbo.dws_invoice_line'))
847BEGIN
848Â Â ALTER TABLE dws_invoice_line ADD ChargeBasis INT NULL
849END
850IF NOT EXISTS(SELECT 1 FROM sys.columns
851 WHERE Name = N'BoxQuantity '
852 AND Object_ID = Object_ID(N'dbo.dws_invoice_line'))
853BEGIN
854Â Â ALTER TABLE dws_invoice_line ADD BoxQuantity INT NULL
855END
856
857IF NOT EXISTS(SELECT 1 FROM sys.columns
858 WHERE Name = N'IsSystemGenerated'
859 AND Object_ID = Object_ID(N'dbo.dws_invoice_line'))
860BEGIN
861Â Â alter table dws_invoice_line add IsSystemGenerated bit not null default(0)
862END
863
864IF NOT EXISTS(SELECT 1 FROM sys.columns
865 WHERE Name = N'ServiceDate '
866 AND Object_ID = Object_ID(N'dbo.dws_invoice_line'))
867BEGIN
868Â Â ALTER TABLE dws_invoice_line ADD ServiceDate datetime NULL
869END
870END
871GO
872
873IF NOT EXISTS(SELECT 1 FROM sys.columns
874 WHERE Name = N'PortID'
875 AND Object_ID = Object_ID(N'dbo.ce_oms_wo_header'))
876BEGIN
877Â Â ALTER TABLE ce_oms_wo_header ADD PortID INT NULL CONSTRAINT FK_ce_oms_wo_header_datafiles_data_portid FOREIGN KEY REFERENCES datafiles_data(DatafileDataID)
878END
879GO
880
881
882IF EXISTS
883(
884 SELECT 1
885 FROM sys.views
886 WHERE name = 'vw_ce_oms_so_containers'
887 AND type = 'v'
888)
889 DROP VIEW vw_ce_oms_so_containers;
890GO
891
892CREATE VIEW [dbo].[vw_ce_oms_so_containers] AS
893SELECT
894dbo.oms_wo_header.WOType,
895dbo.oms_wo_header.WONo,
896dbo.oms_wo_header.WOVersion AS ContainerVersion,
897dbo.oms_wo_header.WODescription AS ContainerDescription,
898dbo.oms_wo_header.WOSubType,
899dbo.oms_wo_header.WOStatusCode,
900dbo.oms_wo_header.WOStateCode,
901dbo.oms_wo_header.WOSectionCode,
902dbo.oms_wo_header.WOPriority,
903ISNULL(dbo.oms_wo_header.WOValidFromDate, GETDATE()) AS ContainerLoadingFromTime,
904ISNULL(dbo.oms_wo_header.WOValidToDate, GETDATE()+1) AS ContainerLoadingToTime,
905dbo.oms_wo_header.WOCreatedTimestamp,
906dbo.oms_wo_header.WOCreatedBy,
907dbo.oms_wo_header.WOIsTemplate,
908dbo.oms_wo_header.WOTemplateWONo,
909dbo.oms_wo_header.WOAllowOverProduce,
910dbo.oms_wo_header.WOAllowUnderProduce,
911dbo.oms_wo_header.WOAllowSubstitution,
912dbo.oms_wo_header.WONotes,
913dbo.oms_wo_header.WOLocking,
914dbo.oms_wo_header.WOUserAcksNewList,
915dbo.oms_wo_header.WOUserAcksUpdatedList,
916
917dbo.ce_oms_wo_header.Transporter,
918dbo.ce_oms_wo_header.DriverName,
919dbo.ce_oms_wo_header.DriverNo,
920dbo.ce_oms_wo_header.ContainerNo,
921dbo.ce_oms_wo_header.SealNo,
922dbo.ce_oms_wo_header.CertNo,
923dbo.ce_oms_wo_header.VehicleRegistration,
924dbo.ce_oms_wo_header.VehicleRegistration AS RegNo,
925dbo.ce_oms_wo_header.FridgeNo,
926dbo.ce_oms_wo_header.EstimatedDispatchDateTime,
927dbo.ce_oms_wo_header.PortID,
928
929ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayID,0) AS LoadingBayID,
930ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayCode,'') AS LoadingBayCode,
931ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayDescription,'') AS LoadingBayDescription,
932ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayDisplayDescription,'') AS LoadingBayDisplayDescription,
933ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayArea,'') AS LoadingBayArea,
934ISNULL(dbo.vw_df_plant_loading_bays_file.LoadingBayActive, 0) AS LoadingBayActive,
935ISNULL(dbo.vw_df_plant_loading_bays_file.Active, 0) AS Active,
936
937ISNULL(dbo.ce_oms_wo_header.HaulierID,0) AS ContainerHaulierID,
938ISNULL(dbo.vw_df_haulier_file.HaulierID,0) AS HaulierID,
939ISNULL(dbo.vw_df_haulier_file.HaulierCode,'') AS HaulierCode,
940ISNULL(dbo.vw_df_haulier_file.HaulierName,'') AS HaulierName,
941ISNULL(dbo.vw_df_haulier_file.HaulierDisplayName,'') AS HaulierDisplayName,
942ISNULL(dbo.vw_df_haulier_file.HaulierDisplayText,'') AS HaulierDisplayText,
943
944dbo.vw_df_haulier_file.HaulierAddress,
945dbo.vw_df_haulier_file.HaulierCountryCode,
946dbo.vw_df_haulier_file.HaulierPostalCode,
947dbo.vw_df_haulier_file.HaulierContactName,
948dbo.vw_df_haulier_file.HauliereMailAddress,
949dbo.vw_df_haulier_file.HaulierPhoneNo,
950dbo.vw_df_haulier_file.HaulierFaxNo,
951dbo.vw_df_haulier_file.HaulierWebAddress,
952dbo.vw_df_haulier_file.HaulierDefaultVATRateCode,
953dbo.vw_df_haulier_file.HaulierDefaultCurrencyCode,
954dbo.vw_df_haulier_file.HaulierComments
955FROM
956dbo.oms_wo_header
957LEFT JOIN dbo.ce_oms_wo_header ON dbo.ce_oms_wo_header.WOType = dbo.oms_wo_header.WOType AND dbo.ce_oms_wo_header.WONo = dbo.oms_wo_header.WONo AND dbo.ce_oms_wo_header.WOVersion = dbo.oms_wo_header.WOVersion
958LEFT JOIN dbo.vw_df_plant_loading_bays_file ON dbo.ce_oms_wo_header.LoadingBayID = dbo.vw_df_plant_loading_bays_file.LoadingBayID
959LEFT JOIN dbo.vw_df_haulier_file ON dbo.ce_oms_wo_header.HaulierID = dbo.vw_df_haulier_file.HaulierID
960WHERE
961(dbo.oms_wo_header.WOType = 'SOC')
962
963GO
964
965
966DROP PROCEDURE IF EXISTS dbo.[dws_invoice_report_service_summary]
967GO
968CREATE procedure [dbo].[dws_invoice_report_service_summary]
969@Start datetime,
970@End datetime
971as
972begin
973
974select ServiceCode,
975 ISNULL(sum(quantity),0) AS Quantity,
976 ISNULL(sum(lines.netamount),0) AS NetAmount,
977 ISNULL(sum(lines.netamount) / NULLIF(sum(quantity),0),0) AS Average
978
979from vw_df_invoice_services services
980left join dws_invoice_line lines on services.ServiceID = lines.ServiceID
981left join dws_invoice_header header on header.RecordID = lines.InvoiceID
982where header.InvoiceDate between @Start and @End
983group by ServiceCode
984
985end
986GO
987
988DROP PROCEDURE IF EXISTS dbo.[dws_invoice_report_servicetype_summary]
989GO
990CREATE procedure [dbo].[dws_invoice_report_servicetype_summary]
991@Start datetime,
992@End datetime,
993@ServiceType int
994as begin
995
996select account.AccountCode,
997 AccountName,
998 ProductGroupCode,
999 ServiceCode,
1000 header.InvoiceNo,
1001 ISNULL(sum(quantity),0) AS Quantity,
1002 ISNULL(sum(lines.netamount),0) AS NetAmount,
1003 ISNULL(sum(lines.netamount) / NULLIF(sum(quantity),0),0) AS Average,
1004 AVG(lines.UnitPrice) as ChargeRate
1005from vw_df_invoice_services services
1006left join dws_invoice_line lines on services.ServiceID = lines.ServiceID
1007left join dws_invoice_header header on header.RecordID = lines.InvoiceID
1008left join vw_df_product_file product on lines.ProductID = product.ProductID
1009left join vw_df_account_file account on account.AccountID = header.AccountID
1010where header.InvoiceDate between @Start and @End
1011and services.ServiceType = @ServiceType
1012group by account.AccountCode,
1013 account.AccountName,
1014 product.ProductGroupCode,
1015 services.ServiceCode,
1016 header.InvoiceNo
1017order by AccountCode, InvoiceNo, ServiceCode
1018end
1019
1020GO
1021
1022
1023IF EXISTS
1024(
1025 SELECT 1
1026 FROM sys.views
1027 WHERE name = 'vw_df_invoice_services'
1028 AND type = 'v'
1029)
1030DROP VIEW vw_df_invoice_services
1031GO
1032CREATE VIEW vw_df_invoice_services
1033AS
1034 SELECT datafiledataid AS ServiceID,
1035 code AS ServiceCode,
1036 text1 AS ServiceDescription,
1037 active AS Active,
1038 nvalue1 AS ChargeBasis,
1039 nvalue2 AS ServiceType,
1040 dnvalue1 AS ChargeRate
1041 FROM vw_ce_datafiles_data
1042 WHERE NAME = 'INVOICING_CHARGES'
1043
1044GO
1045IF EXISTS(SELECT 1
1046 FROM INFORMATION_SCHEMA.ROUTINES
1047 WHERE ROUTINE_NAME = 'sp_dws_system'
1048 AND SPECIFIC_SCHEMA = 'dbo')
1049 BEGIN
1050 DROP PROCEDURE sp_dws_system
1051 END
1052GO
1053
1054CREATE PROCEDURE [dbo].[sp_dws_system]
1055 @Operation VARCHAR(60),
1056 @ReturnResult BIT = 1,
1057 @wotype VARCHAR(20) = '',
1058 @wono VARCHAR(50) = '',
1059 @wolineno INT = 0,
1060 @plu VARCHAR(20) = '',
1061 @bulk VARCHAR(10) = 0,
1062 @dept VARCHAR(20) = '',
1063 @ItemTypeCode VARCHAR(10) = '',
1064 @ItemID BIGINT = 0,
1065 @UOMType VARCHAR(10) = '',
1066 @AccountNo VARCHAR(20) = '',
1067 @IncludeIncompleteIntakes BIT = 1
1068AS
1069BEGIN
1070 DECLARE @SetRtn INT;
1071 DECLARE @Message VARCHAR(200);
1072 SET @SetRtn = 0;
1073 SET @Message = 'OK';
1074
1075 /**********************************************************************************************/
1076 /* SELECT ITEM UPDATE OPTION */
1077 /**********************************************************************************************/
1078 Start:
1079 IF (@Operation = 'SET_RESERVE_STOCK')
1080 GOTO SP_SetReserveStock;
1081 IF (@Operation = 'SET_UNRESERVE_STOCK')
1082 GOTO SP_SetUnReserveStock;
1083 IF (@Operation = 'SET_UNRESERVE_STOCK_FOR_WONO')
1084 GOTO SP_SetUnReserveStockForWONo;
1085 IF (@Operation = 'GET_UNRESERVE_STOCK_FOR_WONO')
1086 GOTO SP_GetUnReserveStockForWONo;
1087 IF (@Operation = 'GET_RESERVED_STOCK')
1088 GOTO SP_GetReservedStock;
1089 IF (@Operation = 'GET_UNRESERVED_STOCK')
1090 GOTO SP_GetUnReservedStock;
1091 IF (@Operation = 'GET_ACCOUNT_STOCK_FOR_ORDERS_FULFILL')
1092 GOTO SP_GetStockForOrderFulfill;
1093 IF (@Operation = 'REPORT_PICKINGLIST')
1094 GOTO SP_ReportPickingList;
1095 IF (@Operation = 'REPORT_PICKINGLIST_PRODUCTIONDATES')
1096 GOTO SP_ReportPickingList;
1097
1098 /* Error : Unknown SubSP */
1099 SET @Message = 'Unknown OMS Operation ' + @Operation;
1100 GOTO GeneralErr;
1101
1102 /**********************************************************************************************/
1103 /* Reserve Stock */
1104 /**********************************************************************************************/
1105 SP_SetReserveStock:
1106 UPDATE vw_ce_ims_items
1107 SET ReservedWONo = @wono,
1108 ReservedWOType = @wotype,
1109 ReservedWOLineNo = @wolineno
1110 WHERE ItemID IN (
1111 SELECT ChildItemID
1112 FROM dbo.ims_associations a
1113 INNER JOIN vw_ce_ims_items b
1114 ON (
1115 a.ChildItemID = b.ItemID
1116 AND a.ChildItemTypeCode = b.ItemTypeCode
1117 AND b.Destroyed = 0
1118 )
1119 WHERE ParentItemID = @ItemID
1120 AND b.PLUID = @plu
1121 AND IMSAssociationTypeCode = 'IPN_BOXES'
1122 )
1123 AND ItemTypeCode = 'BOX'
1124 AND State = 'OK';
1125 SET @SetRtn = 0;
1126 SET @ReturnResult = 0;
1127 GOTO ExitSP;
1128
1129 /**********************************************************************************************/
1130 /* UnReserve Stock */
1131 /**********************************************************************************************/
1132 SP_SetUnReserveStock:
1133 UPDATE vw_ce_ims_items
1134 SET ReservedWONo = NULL,
1135 ReservedWOType = NULL,
1136 ReservedWOLineNo = NULL
1137 WHERE ItemID IN (
1138 SELECT ChildItemID
1139 FROM dbo.ims_associations a
1140 INNER JOIN vw_ce_ims_items b
1141 ON (
1142 a.ChildItemID = b.ItemID
1143 AND a.ChildItemTypeCode = b.ItemTypeCode
1144 AND b.Destroyed = 0
1145 )
1146 WHERE ParentItemID = @ItemID
1147 AND b.PLUID = @plu
1148 AND IMSAssociationTypeCode = 'IPN_BOXES'
1149 )
1150 AND ItemTypeCode = 'BOX'
1151 AND State = 'OK';
1152 SET @SetRtn = 0;
1153 SET @ReturnResult = 0;
1154 GOTO ExitSP;
1155
1156 /**********************************************************************************************/
1157 /* UnReserve Stock */
1158 /**********************************************************************************************/
1159 SP_GetUnReserveStockForWONo:
1160 SELECT DISTINCT
1161 a.ReservedWONo,
1162 a.ReservedWOType,
1163 a.ReservedWOLineNo,
1164 a.PLUID,
1165 b.ParentItemID,
1166 a.AccountCode
1167 FROM vw_ce_ims_items a
1168 LEFT JOIN ims_associations b
1169 ON (
1170 b.ChildItemID = a.ItemID
1171 AND b.ChildItemTypeCode = a.ItemTypeCode
1172 AND b.IMSAssociationTypeCode = 'IPN_BOXES'
1173 )
1174 WHERE ReservedWONo = @wono
1175 AND ReservedWOType = @wotype
1176 AND ItemTypeCode = 'BOX'
1177 AND State = 'OK';
1178 SET @SetRtn = 0;
1179 SET @ReturnResult = 0;
1180 GOTO ExitSP;
1181
1182 /**********************************************************************************************/
1183 /* UnReserve Stock */
1184 /**********************************************************************************************/
1185 SP_SetUnReserveStockForWONo:
1186 UPDATE vw_ce_ims_items
1187 SET ReservedWONo = NULL,
1188 ReservedWOType = NULL,
1189 ReservedWOLineNo = NULL
1190 WHERE ReservedWONo = @wono
1191 AND ReservedWOType = @wotype
1192 AND ItemTypeCode = 'BOX'
1193 AND State = 'OK';
1194 SET @SetRtn = 0;
1195 SET @ReturnResult = 0;
1196 GOTO ExitSP;
1197
1198 /**********************************************************************************************/
1199 /* Get Get Reserved Stock */
1200 /**********************************************************************************************/
1201 SP_GetReservedStock:
1202 SET NOCOUNT ON;
1203 SELECT @plu AS PLUID,
1204 b.ParentItemID AS IPNID,
1205 b.ParentItemID AS IPNID,
1206 b.ParentItemID AS ColdStoreID,
1207 a.PlantCode AS SiteCode,
1208 a.ASNPalletNo,
1209 MIN(a.PackedDate) AS MinPackDate,
1210 MIN(a.UseByDate) AS MinUseByDate,
1211 SUM(a.Units) AS NoUnits,
1212 SUM(a.NetWeight) AS NetWeight,
1213 SUM(a.GrossWeight) AS GrossWeight,
1214 a.SupplierBatchNo
1215 FROM dbo.vw_ce_ims_items a
1216 INNER JOIN ims_associations b
1217 ON (
1218 a.ItemID = b.ChildItemID
1219 AND a.ItemTypeCode = b.ChildItemTypeCode
1220 AND b.IMSAssociationTypeCode = 'IPN_BOXES'
1221 )
1222 WHERE --a.Destroyed = 0 AND
1223 a.PLUID = CONVERT(BIGINT, @plu)
1224 AND a.ReservedWONo = @wono
1225 AND a.ReservedWOType = @wotype
1226 AND a.ReservedWOLineNo = @wolineno
1227 GROUP BY b.ParentItemID,
1228 b.ParentItemID,
1229 a.PlantCode,
1230 a.ASNPalletNo,
1231 a.SupplierBatchNo;
1232 SET @SetRtn = 0;
1233 SET @ReturnResult = 0;
1234 GOTO ExitSP;
1235
1236 /**********************************************************************************************/
1237 /* Get Get Reserved Stock For WO */
1238 /**********************************************************************************************/
1239 SP_ReportPickingList:
1240 SET NOCOUNT ON;
1241 DECLARE @LineActualsWOs AS TABLE (WONo VARCHAR(30));
1242 INSERT INTO @LineActualsWOs
1243 SELECT DISTINCT
1244 WONo
1245 FROM vw_ce_oms_wo_header
1246 WHERE (
1247 ISNULL(@wotype, '') = ''
1248 OR WOType IN (
1249 SELECT * FROM dbo.fn_fw_split_string(@wotype, ',')
1250 )
1251 )
1252 AND (
1253 ISNULL(@wono, '') = ''
1254 OR WONo IN (
1255 SELECT * FROM dbo.fn_fw_split_string(@wono, ',')
1256 )
1257 )
1258 AND WOVersion = 0
1259 AND WOIsTemplate = 0;
1260 DECLARE @WOLineActuals AS TABLE
1261 (
1262 WONo VARCHAR(30),
1263 WOLineDFID INT,
1264 WoLineNo INT,
1265 TotalOrderedQuantity INT,
1266 TotalOrderedWeight FLOAT,
1267 TotalActualQuantity INT,
1268 TotalActualWeight FLOAT,
1269 TotalReservedQuantity INT,
1270 TotalReservedWeight FLOAT
1271 );
1272 INSERT INTO @WOLineActuals
1273 SELECT WONo,
1274 WOLineDFID,
1275 WoLineNo,
1276 SUM(WOLineQuantity),
1277 SUM(WOLineWeight),
1278 SUM(ActualQuantity),
1279 SUM(ActualWeight),
1280 SUM(WOLineQuantityReserved),
1281 SUM(WOLineWeightReserved)
1282 FROM
1283 (
1284 SELECT WOType,
1285 WONo,
1286 WOLineDFID,
1287 WOLineNo,
1288 SUM(WOLineQuantity) AS WOLineQuantity,
1289 SUM(WOLineWeight) AS WOLineWeight,
1290 SUM(ActualQuantity) AS ActualQuantity,
1291 SUM(ActualWeight) AS ActualWeight,
1292 0 AS WOLineQuantityReserved,
1293 0 AS WOLineWeightReserved
1294 FROM vw_ce_oms_wo_line_actuals
1295 WHERE (
1296 ISNULL(@wotype, '') = ''
1297 OR WOType IN (
1298 SELECT * FROM dbo.fn_fw_split_string(@wotype, ',')
1299 )
1300 )
1301 AND WONo IN (
1302 SELECT WONo FROM @LineActualsWOs
1303 )
1304 AND WOVersion = 0
1305 AND WOLineNo < 1000
1306 GROUP BY WOType,
1307 WONo,
1308 WOLineDFID,
1309 WOLineNo
1310 UNION
1311 SELECT WOType,
1312 WONo,
1313 WOLineDFID,
1314 WOLineNo,
1315 0 AS WOLineQuantity,
1316 0 AS WOLineWeight,
1317 0 AS ActualQuantity,
1318 0 AS ActualWeight,
1319 SUM(WOLineQuantityReserved) AS WOLineQuantityReserved,
1320 SUM(WOLineWeightReserved) AS WOLineWeightReserved
1321 FROM vw_ce_oms_wo_line_actuals
1322 WHERE (
1323 ISNULL(@wotype, '') = ''
1324 OR WOType IN (
1325 SELECT * FROM dbo.fn_fw_split_string(@wotype, ',')
1326 )
1327 )
1328 AND WONo IN (
1329 SELECT WONo FROM @LineActualsWOs
1330 )
1331 AND WOVersion = 0
1332 AND WOLineNo > 1000
1333 GROUP BY WOType,
1334 WONo,
1335 WOLineDFID,
1336 WOLineNo
1337 ) p
1338 GROUP BY WOType,
1339 WONo,
1340 WOLineDFID,
1341 WoLineNo;
1342 DECLARE @TempTable TABLE
1343 (
1344 WONo VARCHAR(20),
1345 WOType VARCHAR(10),
1346 WOLineDFID INT,
1347 WoLineNo INT,
1348 TraderName VARCHAR(100),
1349 ProductCode VARCHAR(20),
1350 ProductName VARCHAR(100),
1351 TotalOrderedQuantity INT,
1352 TotalOrderedWeight NUMERIC(15, 4),
1353 TotalActualQuantity INT,
1354 TotalActualWeight NUMERIC(15, 4)
1355 );
1356 INSERT INTO @TempTable
1357 (
1358 WONo,
1359 WOType,
1360 WOLineDFID,
1361 WoLineNo,
1362 TraderName,
1363 ProductCode,
1364 ProductName,
1365 TotalOrderedQuantity,
1366 TotalOrderedWeight,
1367 TotalActualQuantity,
1368 TotalActualWeight
1369 )
1370 SELECT act.WONo,
1371 woh.WOType,
1372 act.WOLineDFID,
1373 act.WoLineNo,
1374 trd.Text1 AS TraderName,
1375 prd.ProductCode,
1376 prd.ProductDescription,
1377 ISNULL(act.TotalOrderedQuantity, 0) AS TotalOrderedQuantity,
1378 ISNULL(act.TotalOrderedWeight, 0) AS TotalOrderedWeight,
1379 ISNULL(act.TotalActualQuantity, 0) AS TotalActualQuantity,
1380 ISNULL(act.TotalActualWeight, 0) AS TotalActualWeight
1381 FROM dbo.vw_ce_oms_wo_header woh
1382 LEFT OUTER JOIN dbo.oms_order_header ord
1383 ON woh.WOType = ord.OrderType
1384 AND woh.WONo = ord.OrderNo
1385 AND woh.WOVersion = ord.OrderVersion
1386 LEFT OUTER JOIN dbo.vw_df_trader_file trd
1387 ON ord.TraderID = trd.TraderID
1388 LEFT JOIN @WOLineActuals act
1389 ON act.WONo = woh.WONo
1390 LEFT JOIN dbo.vw_df_products_list prd
1391 ON act.WOLineDFID = prd.ProductID
1392 LEFT JOIN dbo.vw_df_plant_departments_file dpt
1393 ON dpt.DepartmentCode = ISNULL(
1394 prd.PackingDepartmentCode,
1395 dbo.fn_ce_process_department_code(prd.UsedInProcessList)
1396 )
1397 CROSS APPLY
1398 (
1399 SELECT TOP 1
1400 WOCreatedBy
1401 FROM oms_wo_header
1402 WHERE oms_wo_header.WOType = woh.WOType
1403 AND oms_wo_header.WONo = woh.WONo
1404 ORDER BY WOVersion DESC
1405 ) createdBy
1406 WHERE (
1407 ISNULL(@wotype, '') = ''
1408 OR woh.WOType IN (
1409 SELECT * FROM dbo.fn_fw_split_string(@wotype, ',')
1410 )
1411 )
1412 AND woh.WONo IN (
1413 SELECT WONo FROM @LineActualsWOs
1414 )
1415 AND woh.WOVersion = 0;
1416
1417
1418 IF @Operation = 'REPORT_PICKINGLIST_PRODUCTIONDATES'
1419 BEGIN
1420 SELECT e.TraderName,
1421 e.ProductCode,
1422 e.ProductName,
1423 e.TotalOrderedQuantity,
1424 e.TotalOrderedWeight,
1425 e.TotalActualQuantity,
1426 e.TotalActualWeight,
1427 d.ProductCode,
1428 b.ParentItemID AS IPNID,
1429 b.ParentItemID AS IPNID,
1430 b.ParentItemID AS ColdStoreID,
1431 a.PlantCode AS SiteCode,
1432 a.ASNPalletNo,
1433 a.LocationCode,
1434 a.PackedDate,
1435 f.LocationDisplayDescription,
1436 MIN(a.PackedDate) AS MinPackDate,
1437 MIN(a.UseByDate) AS MinUseByDate,
1438 SUM(a.Units) AS NoUnits,
1439 SUM(a.NetWeight) AS NetWeight
1440 FROM @TempTable e
1441 LEFT JOIN dbo.vw_ce_ims_items a
1442 ON (
1443 e.WONo = a.ReservedWONo
1444 AND e.WOType = a.ReservedWOType
1445 AND e.WOLineDFID = a.PLUID
1446 AND e.WoLineNo = a.ReservedWOLineNo
1447 )
1448 LEFT JOIN ims_associations b
1449 ON (
1450 a.ItemID = b.ChildItemID
1451 AND a.ItemTypeCode = b.ChildItemTypeCode
1452 AND b.IMSAssociationTypeCode = 'IPN_BOXES'
1453 )
1454 LEFT JOIN vw_ce_ims_items c
1455 ON (
1456 c.ParentIMSItemID = b.ParentItemID
1457 AND c.ItemTypeCode = 'BOX'
1458 )
1459 LEFT JOIN dbo.vw_df_product_file d
1460 ON (a.PLUID = d.ProductID)
1461 LEFT JOIN dbo.vw_df_plant_locations_file f
1462 ON (a.LocationCode = f.LocationCode)
1463 WHERE a.Destroyed = 0
1464 AND c.Destroyed = 0
1465 AND e.WONo = @wono
1466 AND e.WOType = @wotype
1467 GROUP BY e.TraderName,
1468 e.ProductCode,
1469 e.ProductName,
1470 e.TotalOrderedQuantity,
1471 e.TotalOrderedWeight,
1472 e.TotalActualQuantity,
1473 e.TotalActualWeight,
1474 d.ProductCode,
1475 b.ParentItemID,
1476 b.ParentItemID,
1477 b.ParentItemID,
1478 a.PlantCode,
1479 a.ASNPalletNo,
1480 a.LocationCode,
1481 a.PackedDate,
1482 f.LocationDisplayDescription;
1483 SET @SetRtn = 0;
1484 SET @ReturnResult = 0;
1485 GOTO ExitSP;
1486 END
1487 ELSE
1488 BEGIN
1489 SELECT e.TraderName,
1490 e.ProductCode,
1491 e.ProductName,
1492 e.TotalOrderedQuantity,
1493 e.TotalOrderedWeight,
1494 e.TotalActualQuantity,
1495 e.TotalActualWeight,
1496 d.ProductCode,
1497 b.ParentItemID AS IPNID,
1498 b.ParentItemID AS IPNID,
1499 b.ParentItemID AS ColdStoreID,
1500 a.PlantCode AS SiteCode,
1501 a.ASNPalletNo,
1502 a.LocationCode,
1503 f.LocationDisplayDescription,
1504 MIN(a.PackedDate) AS MinPackDate,
1505 MAX(a.PackedDate) AS MaxPackDate,
1506 MIN(a.UseByDate) AS MinUseByDate,
1507 SUM(a.Units) AS NoUnits,
1508 SUM(a.NetWeight) AS NetWeight,
1509 c.Barcode AS Pallet
1510 FROM @TempTable e
1511 LEFT JOIN dbo.vw_ce_ims_items a
1512 ON (
1513 e.WONo = a.ReservedWONo
1514 AND e.WOType = a.ReservedWOType
1515 AND e.WOLineDFID = a.PLUID
1516 AND e.WoLineNo = a.ReservedWOLineNo
1517 )
1518 LEFT JOIN ims_associations b
1519 ON (
1520 a.ItemID = b.ChildItemID
1521 AND a.ItemTypeCode = b.ChildItemTypeCode
1522 AND b.IMSAssociationTypeCode = 'IPN_BOXES'
1523 )
1524 LEFT JOIN vw_ce_ims_items c
1525 ON (
1526 c.ItemID = b.ParentItemID
1527 AND c.ItemTypeCode = 'IPN'
1528 )
1529 LEFT JOIN dbo.vw_df_product_file d
1530 ON (a.PLUID = d.ProductID)
1531 LEFT JOIN dbo.vw_df_plant_locations_file f
1532 ON (a.LocationCode = f.LocationCode)
1533 WHERE a.Destroyed = 0
1534 AND c.Destroyed = 0
1535 AND e.WONo = @wono
1536 AND e.WOType = @wotype
1537 GROUP BY e.TraderName,
1538 e.ProductCode,
1539 e.ProductName,
1540 e.TotalOrderedQuantity,
1541 e.TotalOrderedWeight,
1542 e.TotalActualQuantity,
1543 e.TotalActualWeight,
1544 d.ProductCode,
1545 b.ParentItemID,
1546 b.ParentItemID,
1547 b.ParentItemID,
1548 a.PlantCode,
1549 a.ASNPalletNo,
1550 a.LocationCode,
1551 f.LocationDisplayDescription,
1552 c.Barcode;
1553 SET @SetRtn = 0;
1554 SET @ReturnResult = 0;
1555 GOTO ExitSP;
1556 END
1557
1558
1559 /**********************************************************************************************/
1560 /* Get Un Reserved Stock */
1561 /**********************************************************************************************/
1562 SP_GetUnReservedStock:
1563 ;with cte as(
1564 SELECT @plu AS PLUID,
1565 b.ParentItemID AS IPNID,
1566 b.ParentItemID AS ColdStoreID,
1567 a.PlantCode AS SiteCode,
1568 a.ASNPalletNo,
1569 MIN(a.PackedDate) AS MinPackDate,
1570 MIN(a.UseByDate) AS MinUseByDate,
1571 SUM(a.Units) AS NoUnits,
1572 SUM(a.NetWeight) AS NetWeight,
1573 SUM(a.GrossWeight) AS GrossWeight,
1574 a.SupplierBatchNo
1575 FROM dbo.vw_ce_ims_items a
1576 INNER JOIN ims_associations b
1577 ON (
1578 a.ItemID = b.ChildItemID
1579 AND a.ItemTypeCode = b.ChildItemTypeCode
1580 AND b.IMSAssociationTypeCode = 'IPN_BOXES'
1581 )
1582 WHERE a.Destroyed = 0
1583 AND a.PLUID = CONVERT(BIGINT, @plu)
1584 AND a.ReservedWONo IS NULL
1585 AND a.ReservedWOType IS NULL
1586 AND a.ReservedWOLineNo IS NULL
1587 AND a.[State] = 'OK'
1588 AND ISNULL(a.QCHold, 0) = 0
1589 GROUP BY b.ParentItemID,
1590 b.ParentItemID,
1591 a.PlantCode,
1592 a.ASNPalletNo,
1593 a.SupplierBatchNo
1594 )
1595
1596 SELECT * FROM cte WHERE @IncludeIncompleteIntakes = 1
1597 UNION ALL
1598 SELECT cte.* FROM cte
1599 LEFT JOIN vw_ce_dms_transactions trans ON trans.ToParentItemID = cte.IPNID
1600 LEFT JOIN oms_wo_header woh ON woh.WONo = trans.WONo AND woh.WOType = trans.WOType
1601 WHERE @IncludeIncompleteIntakes = 0
1602 AND trans.TransactionType = 'TRANSFER_IN'
1603 AND trans.Cancelled = 0
1604 AND woh.WOStatusCode in ('COMPLETE', 'CLOSED')
1605 AND woh.WOVersion = 0
1606 GROUP BY PLUID, IPNID, ColdStoreID, SiteCode, ASNPalletNo, MinPackDate, MinUseByDate, NoUnits, cte.NetWeight, cte.GrossWeight, SupplierBatchNo
1607 SET @SetRtn = 0;
1608 SET @ReturnResult = 0;
1609 GOTO ExitSP;
1610
1611 /**********************************************************************************************/
1612 /* GET STOCK ON ORDERS YET TO FULFILL */
1613 /**********************************************************************************************/
1614 SP_GetStockForOrderFulfill:
1615 DECLARE @QtyPendingToFulfillOnOrders DECIMAL;
1616 SELECT @QtyPendingToFulfillOnOrders = ISNULL(SUM(t1.QtyToFulfill), 0)
1617 FROM
1618 (
1619 SELECT CASE
1620 WHEN @UOMType = 'CASE' THEN
1621 CASE
1622 WHEN WOLineByWeight = 1 THEN
1623 0
1624 ELSE
1625 SUM(WOLineQuantity) - SUM(ActualQuantity)
1626 END
1627 ELSE
1628 CASE
1629 WHEN @UOMType = 'PALLET' THEN
1630 CASE
1631 WHEN WOLineByWeight = 1 THEN
1632 0
1633 ELSE
1634 (SUM(WOLineQuantity) - SUM(ActualQuantity) / ISNULL(CasesPerPallet, 1))
1635 END --can only assume 1 CASE TO 1 pallet if Not configured
1636 ELSE
1637 CASE
1638 WHEN @UOMType = 'WEIGHT' THEN
1639 CASE
1640 WHEN SUM(WOLineWeight) - SUM(ActualWeight) < 0 THEN
1641 0
1642 ELSE
1643 SUM(WOLineWeight) - SUM(ActualWeight)
1644 END
1645 END
1646 END
1647 END AS QtyToFulfill
1648 FROM dbo.vw_ce_oms_wo_line_actuals
1649 INNER JOIN dbo.vw_ce_oms_order_header
1650 ON vw_ce_oms_order_header.WOType = 'SO'
1651 AND vw_ce_oms_wo_line_actuals.WONo = vw_ce_oms_order_header.WONo
1652 AND AccountCode = @AccountNo
1653 AND vw_ce_oms_order_header.WOVersion = vw_ce_oms_wo_line_actuals.WOVersion
1654 AND vw_ce_oms_order_header.WOType = vw_ce_oms_wo_line_actuals.WOType
1655 LEFT JOIN dbo.vw_df_product_file
1656 ON vw_df_product_file.AccountCode = vw_ce_oms_order_header.AccountCode
1657 AND ProductID = WOLineDFID
1658 WHERE vw_ce_oms_wo_line_actuals.WOType = 'SO'
1659 AND vw_ce_oms_wo_line_actuals.WOVersion = 0
1660 AND WOStatusCode IN ( 'NEW', 'READY', 'WIP' )
1661 GROUP BY ISNULL(CasesPerPallet, 1),
1662 WOLineByWeight
1663 ) AS t1;
1664 IF @UOMType = 'CASE'
1665 SELECT ISNULL(SUM(Units) - @QtyPendingToFulfillOnOrders, 0) AS QtyAfterOrderFulfill,
1666 ISNULL(SUM(Units), 0) AS QtyPreOrderFulfill,
1667 @QtyPendingToFulfillOnOrders AS QtyOnOrdersOutstanding
1668 FROM dbo.vw_ce_ims_items
1669 WHERE ItemTypeCode = 'BOX'
1670 AND AccountCode = @AccountNo
1671 AND State = 'OK';
1672 IF @UOMType = 'PALLET'
1673 SELECT ISNULL(COUNT(DISTINCT ParentItemID) - @QtyPendingToFulfillOnOrders, 0) AS QtyAfterOrderFulfill,
1674 ISNULL(COUNT(DISTINCT ParentItemID), 0) AS QtyPreOrderFulfill,
1675 @QtyPendingToFulfillOnOrders AS QtyOnOrdersOutstanding
1676 FROM ims_associations b
1677 INNER JOIN vw_ce_ims_items c
1678 ON (
1679 b.ChildItemID = c.ItemID
1680 AND c.ItemTypeCode = b.ChildItemTypeCode
1681 )
1682 WHERE c.AccountCode = @AccountNo
1683 AND c.ItemTypeCode = 'BOX'
1684 AND c.State = 'OK';
1685 IF @UOMType = 'WEIGHT'
1686 SELECT ISNULL(SUM(NetWeight) - @QtyPendingToFulfillOnOrders, 0) AS QtyAfterOrderFulfill,
1687 ISNULL(SUM(NetWeight), 0) AS QtyPreOrderFulfill,
1688 @QtyPendingToFulfillOnOrders AS QtyOnOrdersOutstanding
1689 FROM dbo.vw_ce_ims_items
1690 WHERE ItemTypeCode = 'BOX'
1691 AND AccountCode = @AccountNo
1692 AND State = 'OK';
1693 SET @SetRtn = 0;
1694 SET @ReturnResult = 0;
1695 GOTO ExitSP;
1696
1697 /* Return General Error */
1698 GeneralErr:
1699 PRINT @Message;
1700 SET @SetRtn = -1;
1701 GOTO ExitSP;
1702
1703 /* Return Results */
1704 ExitSP:
1705 IF @ReturnResult = 1
1706 OR @SetRtn < 0
1707 SELECT @SetRtn AS Result,
1708 @Message AS Message;
1709 RETURN @SetRtn;
1710END;