Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Sunday, April 29, 2018

Move AR Records from History to Open - Reversing Apply ( After Paid Transactions Removal )

The apply process in Dynamics GP is different between the receivable and the payable modules, when payable transactions are fully applied they are "automatically" moved to history which means that the data is migrated from the open tables into the history tables. On the other hand, this is not the same behavior of the receivable module, in which fully applied documents are not moved to history at all unless a specific routine in the system which is called "Paid Transactions Removal" is run, upon which, the documents are moved from the open table to history. 


Apply Reversal
Due to the different behavior of the modules illustrated above, AR transactions can be applied and un-applied at any time provided that the "paid transaction removal" process is not run. On the other hand, this is not the case in the payable module, because historical transactions can not be un-applied using the "Apply" function, but it requires specific steps initiated by "voiding" the payment and releasing the invoices. 




AR - Move History to Open ( Yes, History to Open ... Reversing Apply ) - After Running Paid Transactions Removal

In certain cases, the apply processes gets interrupted and result with damaged applied records, in case the records have been moved to history, then records are stuck in the history tables, but in fact the document is not fully applied


In this essence, I have developed a procedure in order to reverse the apply process, and release the sales invoices documents by moving the records fro the history status to the open status. Below is the detailed steps on how to run this procedure:

--- Change the Status of Sales Documents, from History to Open

SELECT * FROM RM00401
WHERE DOCNUMBR IN ( 'XXXX' )

--- Replace 'XXXX' With the Sales Document which requires to be Released ( History to Open )

--UPDATE RM00401
--SET DCSTATUS = 2
--WHERE DOCNUMBR IN ( 'XXXX' )


--- MOVE RM Records From OPEN TO History
INSERT INTO dbo.RM20101
(
    CUSTNMBR,
    CPRCSTNM,
    DOCNUMBR,
    CHEKNMBR,
    BACHNUMB,
    BCHSOURC,
    TRXSORCE,
    RMDTYPAL,
    CSHRCTYP,
    CBKIDCRD,
    CBKIDCSH,
    CBKIDCHK,
    DUEDATE,
    DOCDATE,
    POSTDATE,
    PSTUSRID,
    GLPOSTDT,
    LSTEDTDT,
    LSTUSRED,
    ORTRXAMT,
    CURTRXAM,
    SLSAMNT,
    COSTAMNT,
    FRTAMNT,
    MISCAMNT,
    TAXAMNT,
    COMDLRAM,
    CASHAMNT,
    DISTKNAM,
    DISAVAMT,
    DISAVTKN,
    DISCRTND,
    DISCDATE,
    DSCDLRAM,
    DSCPCTAM,
    WROFAMNT,
    TRXDSCRN,
    CSPORNBR,
    SLPRSNID,
    SLSTERCD,
    DINVPDOF,
    PPSAMDED,
    GSTDSAMT,
    DELETE1,
    AGNGBUKT,
    VOIDSTTS,
    VOIDDATE,
    TAXSCHID,
    CURNCYID,
    PYMTRMID,
    SHIPMTHD,
    TRDISAMT,
    SLSCHDID,
    FRTSCHID,
    MSCSCHID,
    NOTEINDX,
    Tax_Date,
    APLYWITH,
    SALEDATE,
    CORRCTN,
    SIMPLIFD,
    Electronic,
    ECTRX,
    BKTSLSAM,
    BKTFRTAM,
    BKTMSCAM,
    BackoutTradeDisc,
    Factoring,
    DIRECTDEBIT,
    ADRSCODE,
    EFTFLAG,
    DEX_ROW_TS
)
SELECT CUSTNMBR,
       CPRCSTNM,
       DOCNUMBR,
       CHEKNMBR,
       BACHNUMB,
       BCHSOURC,
       TRXSORCE,
       RMDTYPAL,
       CSHRCTYP,
       '',
       '',
       '',
       DUEDATE,
       DOCDATE,
       POSTDATE,
       PSTUSRID,
       GLPOSTDT,
       LSTEDTDT,
       LSTUSRED,
       ORTRXAMT,
       0,
       SLSAMNT,
       COSTAMNT,
       FRTAMNT,
       MISCAMNT,
       TAXAMNT,
       COMDLRAM,
       CASHAMNT,
       DISTKNAM,
       DISAVAMT,
       0,
       DISCRTND,
       DISCDATE,
       DSCDLRAM,
       DSCPCTAM,
       WROFAMNT,
       TRXDSCRN,
       CSPORNBR,
       SLPRSNID,
       SLSTERCD,
       DINVPDOF,
       PPSAMDED,
       GSTDSAMT,
       DELETE1,
       '',
       VOIDSTTS,
       VOIDDATE,
       TAXSCHID,
       CURNCYID,
       PYMTRMID,
       SHIPMTHD,
       TRDISAMT,
       SLSCHDID,
       FRTSCHID,
       MSCSCHID,
       NOTEINDX,
       Tax_Date,
       APLYWITH,
       SALEDATE,
       CORRCTN,
       SIMPLIFD,
       Electronic,
       ECTRX,
       BKTSLSAM,
       BKTFRTAM,
       BKTMSCAM,
       BackoutTradeDisc,
       Factoring,
       DIRECTDEBIT,
       ADRSCODE,
       EFTFLAG,
       DEX_ROW_TS
FROM dbo.RM30101
WHERE DOCNUMBR IN ( 'XXXX' )

--DELETE FROM dbo.RM30101
--WHERE DOCNUMBR IN ( 'XXXX' );


-- Move RM Tax Work File , RM Tax History RM10601 > RM30601
INSERT INTO dbo.RM10601
(
    BACHNUMB,
    RMDTYPAL,
    DOCNUMBR,
    CUSTNMBR,
    TAXDTLID,
    TRXSORCE,
    ACTINDX,
    BKOUTTAX,
    TAXAMNT,
    ORTAXAMT,
    STAXAMNT,
    ORSLSTAX,
    FRTTXAMT,
    ORFRTTAX,
    MSCTXAMT,
    ORMSCTAX,
    TAXDTSLS,
    ORTOTSLS,
    TDTTXSLS,
    ORTXSLS,
    POSTED,
    SEQNUMBR,
    CURRNIDX
   -- TXDTLPCTAMT
)
SELECT BACHNUMB,
       RMDTYPAL,
       DOCNUMBR,
       CUSTNMBR,
       TAXDTLID,
       TRXSORCE,
       ACTINDX,
       0,
       TAXAMNT,
       ORTAXAMT,
       STAXAMNT,
       ORSLSTAX,
       FRTTXAMT,
       ORFRTTAX,
       MSCTXAMT,
       ORMSCTAX,
       TAXDTSLS,
       ORTOTSLS,
       TDTTXSLS,
       ORTXSLS,
       POSTED,
       SEQNUMBR,
       CURRNIDX
       --TXDTLPCTAMT
FROM RM30601
WHERE DOCNUMBR IN ( 'XXXX' );

--DELETE FROM RM30601
--WHERE DOCNUMBR IN ( 'XXXX' );

--- Move RM Distribution Details , RM Tax History RM10601 > RM30601
INSERT INTO dbo.RM10101
(
    TRXSORCE,
    POSTED,
    POSTEDDT,
    PSTGSTUS,
    CHANGED,
    DOCNUMBR,
    DCSTATUS,
    DISTTYPE,
    RMDTYPAL,
    SEQNUMBR,
    CUSTNMBR,
    DSTINDX,
    DEBITAMT,
    CRDTAMNT,
    PROJCTID,
    USERID,
    CATEGUSD,
    CURNCYID,
    CURRNIDX,
    ORCRDAMT,
    ORDBTAMT,
    DistRef
)
SELECT TRXSORCE,
       1,
       POSTEDDT,
       1,
       0,
       DOCNUMBR,
       1,
       DISTTYPE,
       RMDTYPAL,
       SEQNUMBR,
       CUSTNMBR,
       DSTINDX,
       DEBITAMT,
       CRDTAMNT,
       PROJCTID,
       USERID,
       CATEGUSD,
       CURNCYID,
       CURRNIDX,
       ORCRDAMT,
       ORDBTAMT,
       DistRef
FROM dbo.RM30301
WHERE DOCNUMBR IN ( 'XXXX' );

--DELETE FROM dbo.RM30301
--WHERE DOCNUMBR IN ( 'XXXX' );




Best Regards, 
Mahmoud M. AlSaadi