taRMApply Wrapper

It seems that every day I get a call to do something new. 

Today's newness is this: The client is in the habit of writing returns that have the same document number as the corresponding invoice. In practice, most invoices have a small return. 

These are all sitting in the RM Open table, unapplied. Lots of them. 

Task: Create a stored procedure that will search the RM20101 (RM Open) table and return all matching unapplied credits and invoices, and then automatically apply them. 

Care to comment on my approach? Can you improve the code?

The working stored procedure is below, fully commented. 

Related Articles

... and you 'll find more on the eConnect Menu

 

IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_RMApply') begin
DROP proc dd_RMApply
end
 
GO
  
CREATE proc dd_RMApply
 
  
AS
 
set transaction isolation level read uncommitted
  
DECLARE @docnumbr varchar(21),
    @CreditType int,
    @CreditAmt numeric(19,5),
    @InvoiceAmt numeric(19,5),
    @ApplyAmt numeric(19,5)
DECLARE @O_iErrorState INT
DECLARE @oErrString VARCHAR(255)
 
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
 
select
        --restricted to one line for testing
        top 1
        rt.DOCNUMBR, rt.RMDTYPAL ,
        rt.CURTRXAM, rt2.CURTRXAM
    from rm20101 rt
        --assume that the credit has the same docnumbr as the invoice. make sure the customer matchers
        join rm20101 rt2 on rt2.DOCNUMBR = rt.DOCNUMBR and rt2.RMDTYPAL <> rt.RMDTYPAL and rt.CUSTNMBR = rt2.CUSTNMBR
    where
        --apply to invoices only
        rt.rmdtypal <> 1
        --accept any type of credit document
        and rt2.rmdtypal > 5
        -- be sure that both the credit and debit docs need to have something applied
        and rt.CURTRXAM > 0
        and rt2.CURTRXAM > 0
    order by 2,1
 
 
OPEN curName
 
WHILE 1=1
 
BEGIN
 
    FETCH NEXT FROM curName INTO @docnumbr, @CreditType, @Creditamt, @InvoiceAmt
    if @@fetch_status <> 0 begin
        break
    end
    select @O_iErrorState = 0,
        @oErrString = ''
 
    --apply the lower amount
    if @CreditAmt > @InvoiceAmt begin
        set @ApplyAmt = @InvoiceAmt
    end else begin
        set @ApplyAmt = @CreditAmt
    end
 
    --call the eConnect RMApply stored proceture
    EXEC taRMApply
        @I_vAPTODCNM = @docnumbr,
        @I_vAPTODCTY = 1,
        @I_vAPFRDCNM = @docnumbr,
        @I_vAPFRDCTY = @CreditType,
        @I_vAPPTOAMT = @InvoiceAmt,
        @O_iErrorState = @O_iErrorState OUTPUT,
        @oErrString = @oErrString OUTPUT
 
    --if there was an error, look up the correspondintg error text
    if @O_iErrorState > 0 begin
        select @oErrString = ec.ErrorDesc
            from dynamics..taErrorCode ec
            where ec.ErrorCode = @O_iErrorState
    end
 
    --as an auditing feature, we log successes and failures
    insert into ddapplylog (aptodcnm , aptodcty, APFRDCNM , APFRDCTY   , ApplyAmt , ErrorText)
        values             (@docnumbr, 1       , @docnumbr, @CreditType, @ApplyAmt, @oErrString)
END
 
CLOSE curName
 
DEALLOCATE curName
  
GO
  
GRANT EXEC ON dd_RMApply TO PUBLIC
--  select * from ddapplylog order by rowid desc

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences