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.
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