Receivables Transaction Inquiry problem 1

[bustercoder]

Hello,

My automated process pulls shipment information and updates Great Plains. Via a trigger on my custom import table, the order is updated with the Freight Amount:

if exists(SELECT 1 FROM [dbo].[TBZ_Shipments_Import]
INNER JOIN [dbo].[SOP10100]
ON [dbo].[TBZ_Shipments_Import].[ORDER_ID] = [dbo].[SOP10100].[SOPNUMBE])
Begin
UPDATE [SOP10100]
SET [SOP10100].[FRTAMNT] = [TBZ_Shipments_Import].[FREIGHT_AMOUNT],
[SOP10100].[ORFRTAMT] = [TBZ_Shipments_Import].[FREIGHT_AMOUNT]
FROM [SOP10100] INNER JOIN [TBZ_Shipments_Import]
ON [SOP10100].[SOPNUMBE] = [TBZ_Shipments_Import].[ORDER_ID]
End

Via DTS Packages/Stored procs, the Freight Distribution Line Item is being populated:

INSERT SOP10102 (SOPTYPE, SOPNUMBE, SEQNUMBR, DISTTYPE, DistRef, ACTINDX, DEBITAMT, ORDBTAMT, CRDTAMNT, ORCRDAMT, CURRNIDX, TRXSORCE, POSTED)
SELECT DISTINCT 3,ORDER_ID,100,7,'',83,0,0,FREIGHT_AMOUNT,FREIGHT_AMOUNT,2999,'',0
FROM TBZ_Shipments_Import tsi INNER JOIN SOP10102 on tsi.ORDER_ID = SOP10102.SOPNUMBE

and the Recv Distribution Line Item is being updated with the Freight Amount:

if exists(SELECT 1 FROM [dbo].[TBZ_Shipments_Import]
INNER JOIN [dbo].[SOP10102]
ON [dbo].[TBZ_Shipments_Import].[ORDER_ID] = [dbo].[SOP10102].[SOPNUMBE])

UPDATE [SOP10102]
SET [SOP10102].[DEBITAMT] = tsi.[FREIGHT_AMOUNT] + [SOP10102].[DEBITAMT],
[SOP10102].[ORDBTAMT] = tsi.[FREIGHT_AMOUNT] + [SOP10102].[ORDBTAMT]
FROM SOP10100 INNER JOIN
SOP10102 ON SOP10100.SOPTYPE = SOP10102.SOPTYPE AND SOP10100.SOPNUMBE = SOP10102.SOPNUMBE INNER JOIN
TBZ_Shipments_Import tsi ON SOP10102.SOPNUMBE = tsi.ORDER_ID
WHERE SOP10100.DOCAMNT <> 0 AND SOP10102.DISTTYPE = 2

Everything is updating and working fine in GP, the Edit Lists report no errors. But on the Receivables Transaction Inquiry window, I see that the Freight Amount is not being added to the Amount Remaining. Unless the customer has a partial payment or a credit or debit has been applied to the Invoice, the Document Amount should always equal the Amount Remaining.

I have the Information Flow and Posting book by Richard Whaley which is great, but it seems like I might not be updating a table (value) somewhere. If anyone has an idea of what I might be missing, I'd really appreciate the help.

Thanks,
Buster

 

[RobertC22]

Buster,

Firstly you mention that you are updating an Order.

Yet your insert from the DTS package specifies SOPTYPE = 3 which is Invoice.

An invoice when posted in SOP moves the SOP10100 record to SOP30200 table and the SOP10200 records to the SOP30300 table.
There is no equivalent History table for all other SOP101xx tables - in your case SOP10102.
Further when the SOP posting occurs it creates a record in the RM10301 (RM10101 for the distributions) which when automatically posted will be in the RM20101.

It is the RM20101 table that is used to display the records in the Receivables Transaction Inquiry window.
When you zoom on a record in this inquiry window, it identifies that the invoice originated from SOP thereby opening the "Sales Transaction Inquiry Zoom" window.

Therefore, the updates to the SOP10102 after the SOP invoice is posted would NOT flowing through to the Receivables documents, nor to the Financial series.

In essence, the update to the SOP10102 should only occur if the SOP invoice is NOT posted. This can be checked by the presence of the SOP document in the SOP10100 table.

------
Robert

 

[bustercoder]

Hi Robert,

The problem was that the acctamnt and oractamt were not having their totals updated with the freight amount.

Thanks,
Buster