Here is the stored procedure that we created and called it from taRMCashReceiptInsertPost routine.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'dd_RM20201_Apply'
AND type = 'P')
DROP PROCEDURE dd_RM20201_Apply
GO
CREATE PROCEDURE dd_RM20201_Apply
-- dd_RM20201_Apply 'PYMNT000000000275', 9
@Payment AS VARCHAR(21), -- Payment Doc number
@PaymentType AS SMALLINT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @CUSTNMBR AS VARCHAR(15)
DECLARE @ORTRXAMT AS NUMERIC(19,5)
DECLARE @APFRDCDT AS DATE
DECLARE @ApplyFromGLPostDate AS DATE
DECLARE @APTODCDT AS DATE
DECLARE @ApplytOGLPostDate AS DATE
DECLARE @Invoice AS VARCHAR(21) -- Invoice Doc number
DECLARE @InvoiceType AS SMALLINT
DECLARE @CurrencyID AS VARCHAR(15)
DECLARE @DATE1 AS DATE
SET @DATE1 = CONVERT(VARCHAR(10), GETDATE(), 101)
-- Find customer number and amount for this document number and type
SELECT @CUSTNMBR = CUSTNMBR,
@ORTRXAMT = ORTRXAMT,
@APFRDCDT = DOCDATE,
@ApplyFromGLPostDate = GLPOSTDT
FROM RM10201 RO
WHERE RO.DOCNUMBR = @Payment
AND RO.RMDTYPAL = @PaymentType
-- Find oldest document (invoice) from customer with the sales amount
SELECT @Invoice = DOCNUMBR,
@InvoiceType = RMDTYPAL,
@CurrencyID = CURNCYID,
@APTODCDT = DOCDATE,
@ApplytOGLPostDate = POSTDATE
FROM RM20101 R
WHERE R.CUSTNMBR = @CUSTNMBR
AND R.ORTRXAMT = @ORTRXAMT
AND R.VOIDSTTS = 0
ORDER BY DOCDATE DESC – Toggle this for earliest or latest entry
IF NOT @Invoice IS NULL BEGIN
-- Create Apply row
INSERT INTO RM20201
( CUSTNMBR, DATE1, GLPOSTDT, POSTED, APTODCNM, APTODCTY, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPTOAMT, ORAPTOAM, APFRDCNM, APFRDCTY, APFRDCDT, ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, ActualApplyToAmount)
VALUES (@CUSTNMBR, @DATE1, @DATE1, '0', @Invoice, @InvoiceType, @APTODCDT, @ApplytOGLPostDate, @CurrencyID, '1007', @ORTRXAMT, @ORTRXAMT, @Payment, @PaymentType, @APFRDCDT, @ApplyFromGLPostDate, @CurrencyID, @ORTRXAMT, @ORTRXAMT)
END
GO
grant exec on dd_RM20201_Apply to public