img1

This is the second blog post of the 5 part series. In this post I am going to discuss the vendor payment settlement. It is a very common scenario that vendor invoice gets blocked because it has been included in some payment journal which has not been posted. AX blocks (and rightly so) any further settlement of this invoice until the open payment journal has been posted. In this blog post I am going to show how you can find the payment journal number in which the invoice has been marked for settlement.

img1

SET NOCOUNT ON

DECLARE     @DATAAREAID VARCHAR(200),

@VENDORID   VARCHAR(200),

@INVOICE    VARCHAR(200)

SELECT      @DATAAREAID = ‘CEU’     –Required

SELECT      @VENDORID   = ”  –Can be left as blank or NULL

SELECT      @INVOICE    = NULL      –Can be left as blank or NULL

 

SELECT      VENDOR_ID = T1.ACCOUNTNUM,

PAYMENT_JOURNAL = T4.JOURNALNUM,

PAYMENT_COMPANY = T3.SPECCOMPANY,

T1.INVOICE,

INVOICE_DATE = CONVERT(VARCHAR(50),T1.TRANSDATE,101),

AMOUNT_TO_SETTLE = T3.BALANCE01,

PAYMWNT_JOURNAL_CREATED_DATE = CONVERT(VARCHAR(50),T3.CREATEDDATETIME,101)

FROM VENDTRANS    T1

INNER JOIN VENDTRANSOPEN T2 ON T1.RECID = T2.REFRECID

INNER JOINSPECTRANS T3 ON T3.REFRECID = T2.RECID AND T3.REFTABLEID = 866

AND T3.SPECTABLEID = 212

INNER JOIN LEDGERJOURNALTRANS T4 ON T4.RECID = T3.SPECRECID

AND T4.DATAAREAID = T3.SPECCOMPANY

WHERE T1.DATAAREAID = @DATAAREAID

AND T1.ACCOUNTNUM = CASE WHEN ((@VENDORID IS NULL) OR (@VENDORID = ”)) THEN T1.ACCOUNTNUM ELSE @VENDORID END

AND T1.INVOICE = CASE WHEN ((@INVOICE IS NULL) OR (@INVOICE = ”)) THEN T1.INVOICE ELSE @INVOICE END

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Comments

    Archives

    Categories