Share
Report
280We do all our shipping from one GP company called Windy. We have a few other companies where the orders get copied over to Windy, shipped, and then the original order in the source company is updated.
We have 2 companies where this works perfectly. A third company, however has a problem. The order comes back from Windy and the freight does get added into the document, and the document amount is increased by the freight amount. But, when transferred to invoice, the freight dissapears.
Am I missing some other field that needs the freight populated or something?
Here is a copy of the SP that brings back the shipped order info:
ALTER PROCEDURE [dbo].[Nextec_UpdateWebOrders_SAM]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Process all cross database queries in one transaction so that if errors arise, all can be rolled back.
BEGIN TRANSACTION
-- =============================================
-- Add batch if it doesn't exist
-- =============================================
DECLARE @strdate CHAR(20)
DECLARE @noteindex NUMERIC(19, 5)
DECLARE @newbachnumb CHAR(15)
-- Set batch number variable
SELECT @newbachnumb = 'SAMTIN SHIPPED'
-- Check if new batch number exists in batch table
-- if not, insert a dummy row which we'll later update
-- with actual values
IF NOT EXISTS ( SELECT bachnumb
FROM SAMTI.dbo.sy00500
WHERE bachnumb = @newbachnumb )
BEGIN
-- To satisfy check constraints, GLPOSTDT, MODIFDT, and CREATDDT cannot include time (hour, minute, second,
-- millisecond must be 0); therefore, convert the date returned by GETDATE function.
SET @strdate = CONVERT(CHAR(20), GETDATE(), 101)
-- get next note index to assign to the new batch
SELECT @noteindex = noteindx
FROM dynamics..sy01500
WHERE interid = DB_NAME()
UPDATE dynamics..sy01500
SET noteindx = noteindx + 1
WHERE interid = DB_NAME()
INSERT INTO SAMTI.dbo.SY00500
(
GLPOSTDT,
BCHSOURC,
BACHNUMB,
SERIES,
BACHFREQ,
BCHCOMNT,
CHEKBKID,
MODIFDT,
CREATDDT,
NOTEINDX,
BCHSTTUS,
ORIGIN
)
VALUES (
@strdate,
'Sales Entry',
@newbachnumb,
3,
1,
'Shipped or partially shipped from Windy Hill',
'CHARTER ONE',
@strdate,
@strdate,
@noteindex,
0,
1
)
END
-- =============================================
-- Check for existing tracking info in SAMTI
-- for each order, if none exists, insert
-- info from WINDY
-- =============================================
INSERT INTO SAMTI.dbo.SOP10107
(
SOPNUMBE,
SOPTYPE,
Tracking_Number
)
SELECT W1.SOPNUMBE,
W1.SOPTYPE,
W1.Tracking_Number
FROM WINDY.dbo.Nextec_WebOrder_TrackingInfo_SAM W1
LEFT OUTER JOIN SAMTI.dbo.SOP10107 P1 ON W1.SOPNUMBE = P1.SOPNUMBE
AND W1.SOPTYPE = P1.SOPTYPE
AND W1.Tracking_Number = P1.Tracking_Number
WHERE P1.SOPNUMBE IS NULL
-- =============================================
-- Update SAMTI Line Items with WINDY Data
-- =============================================
UPDATE SAMTI.dbo.SOP10200
SET QTYTBAOR = W1.QTYUNFF,
QTYFULFI = W1.QTYFULFI,
QTYTOINV = W1.QTYTOINV,
ACTLSHIP = W1.ACTLSHIP
FROM SAMTI.dbo.SOP10200 P1
INNER JOIN ( SELECT T1.SOPTYPE,
T1.SOPNUMBE,
T1.ITEMNMBR,
T1.LNITMSEQ,
T1.QTYUNFF,
T1.QTYFULFI,
T1.QTYTOINV,
T1.ACTLSHIP
FROM WINDY.dbo.Nextec_WebOrders_SAM T1
) W1 ON P1.SOPTYPE = W1.SOPTYPE
AND P1.SOPNUMBE = W1.SOPNUMBE
AND P1.ITEMNMBR = W1.ITEMNMBR
AND P1.LNITMSEQ = W1.LNITMSEQ
-- =============================================
-- Update SAMTI Order Headers with WINDY Data
-- =============================================
UPDATE SAMTI.dbo.SOP10100
SET ACTLSHIP = W1.ACTLSHIP,
BACHNUMB = @newbachnumb,
FRTAMNT = W1.FRTAMNT,
/* SUBTOTAL = W1.SUBTOTAL + W1.FRTAMNT, */
DOCAMNT = P1.DOCAMNT + W1.FRTAMNT
FROM SAMTI.dbo.SOP10100 P1
INNER JOIN ( SELECT DISTINCT
T1.SOPTYPE,
T1.SOPNUMBE,
T1.ACTLSHIP,
T1.BACHNUMB,
T1.FRTAMNT,
T1.SUBTOTAL,
T1.DOCAMNT
FROM WINDY.dbo.Nextec_WebOrders_SAM T1
) W1 ON P1.SOPTYPE = W1.SOPTYPE
AND P1.SOPNUMBE = W1.SOPNUMBE
-- =============================================
-- Ensure the transaction returns no errors,
-- otherwise undo changes and return error
-- =============================================
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN @@ERROR
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
*This post is locked for comments
All responses (
Answers (
