Alert - AP Goods Receipt & AP Invoice

[Former Member]

Hi,

Wondering if I could get some assistance on query required for an Alert.

- Alert is to notify applicable users of AP goods received into warehouse "01". Goods are received via AP invoice mostly but also random AP goods receipts (where Landed costs are affected). User only needs to be alerted of documents added and from there they can drill down to inquire on products.

thanks and I appreciate any help.

regards

Kelly

[Former Member]

It is possible to create an alert for the below infromation to trigger each time a 'new' GRPO has been created? (as opposed to daily).

SELECT T1.DocNum, T1.CardCode, T1.CardName FROM PDN1 T0 inner JOIN dbo.OPDN T1 ON T1.DocEntry = T0.DocEntry WHERE T0.WhsCode = 01 AND T1.CreateDate = cast(CONVERT(nvarchar(16), getdate(), 112) as datetime)

[Former Member]

Hi thanks for your reply,

Upon testing I have found alert displays with multiple lines can we have it so result is only 1 line per transaction. Currently it displays multiple times as per the number of lines on GRPO - eg if GRPO consists of 2 lines then the alert displays same transaction x 2.

I appreciate your advise.

thks

regards

Kelly

[former_member583013]

Kelly

Which Query are you using.

Trying to Add a DISTINCT after the SELECT

SELECT DISTINCT T0.DOCNUM, T0.DOCDATE, ....

[former_member583013]

Kelly,

I have the following suggession since you mentioned that either a Goods Receipt or a AP Invoice could be used to receive the goods.

To get both the document you will need to also query the OPCH table (AP Invoices). Something like this would be a good start

SELECT T0.DOCNUM, T0.DOCDATE, T0.CARDCODE, T0.CARDNAME FROM [DBO\].[OPDN\] T0 INNER JOIN [DBO\].[PDN1\] T1 ON T0.DOCENTRY = T1.DOCENTRY WHERE T1.BASETYPE = '22'

UNION ALL

SELECT T0.DOCNUM, T0.DOCDATE, T0.CARDCODE, T0.CARDNAME FROM [DBO\].[OPCH\] T0 INNER JOIN [DBO\].[PCH1\] T1 ON T0.DOCENTRY = T1.DOCENTRY WHERE T1.BASETYPE = '22'

In the above query I am using a UNION clause to fetch the results from both AP Invoices and Goods Receipts. Also I am checking if they are copied from Purchase Orders by the BaseType check.

Suda

[anna_shao]

Hi,

Try this query and you can modify it according to your demands.

SELECT

T1.[DocNum], T1.[CardCode], T1.[CardName]

FROM [dbo].[PDN1] T0 inner JOIN [dbo].[OPDN] T1

ON T1.[DocEntry] = T0.DocEntry

WHERE T0.[WhsCode] = 'XXXX' AND T1.[CreateDate] = cast(CONVERT(nvarchar(16), getdate(), 112) as datetime)

Replace XXXX by the warehouse you want to watch.

This query will list all AP goods receipt purchase order created current day.

you could also limit to open GRPO only all else like this.