Alert Salespersons when stock is booked

[Former Member]

Hi Support

Can I please get some help writing query for required Alert setup.

I need Alert to trigger relevant assigned Salespersons when stock is booked in.

Meaning... alert to notify user/s when stock is received into warehouse "01" from PO. Can be done via Goods Receipt and/or AP invoice process.

I appreciate your help.

regards

Kelly

[Former Member]

Hi Suda

No I only require to pick up qty received into warehouse once. The process can be a combination ie sometimes from PO to AP Invoice and other times from PO to GRPO then AP Invoice.

Pls advise back

thanks heaps

Kelly

[former_member583013]

Ok.

I am going to give you the base query which can be altered based on your information requirements

SELECT DISTINCT T0.DOCNUM, T0.DOCDATE, T1.ITEMCODE, T1.QUANTITY

FROM [DBO\].[OPDN\] INNER JOIN [DBO\].[PDN1\] T1 ON T1.DOCENTRY = T0.DOCENTRY

WHERE T1.WHSCODE = '01' AND T0.DOCDATE = GETDATE()

UNION ALL

SELECT DISTINCT T0.DOCNUM, T0.DOCDATE, T1.ITEMCODE, T1.QUANTITY

FROM [DBO\].[OPCH\] INNER JOIN [DBO\].[PCH1\] T1 ON T1.DOCENTRY = T0.DOCENTRY

WHERE T1.WHSCODE = '01' AND T0.DOCDATE = GETDATE() AND T1.BASETYPE = 22

The above query will display all GRPO's and all AP Invoices which have been added that day. In the where clause the condition T0.DOCDATE = GETDATE() filters this.

Suda

[Former Member]

hi

Basically I need query to report on stock coming in to warehouse "01" via GRPO and AP Invoice process from base document PO.

Alert to pick up this query and will be set to applicable user/s to alert them of rthe stock in warehouse that has arrived.

I hope this helps.

Please advise query to use.

thks

Kelly

[former_member583013]

Kelly,

Could you please answer the following. If you want to pick the quantities from GRPO and AP Invoice they will show duplicate quantities if they are accounted twice.

Please confirm iyour Purchase Process steps. Is it PO > GRPO > AP Invoice

Or PO > AP Inv

If it is both then the query will have to be different.

Also let me know what information you want to display on the ALERT

Suda

[former_member583013]

Kelly,

Writing a simple query like Jimmy has shown is the easy part but when you talk about alerts to a particular assigned Salesperson what you need to understand is Alerts unless chosen to go a particular user cannot automatically determine the SalesRep on the document.

Determining the baseline logic of how the query is going to deliver the Alert is important.

If you could explain your scenario in a little more detail with an example perhaps would benefit address the solutions

Best wishes

Suda

[former_member186095]

Kelly,

Do you want good receipt PO (GRPO) or good receipt ?

try to use this query if it is A/P invoice and good receipts (under inventory transactions) :

SELECT T0.[Base_ref] as 'Doc No.', T0.[DocDate], 
T0.[ItemCode], T0.[Warehouse] FROM 
OINM T0 WHERE (T0.[TransType]  = '18' or 
 T0.[TransType] = '59') and  T0.[Warehouse]  = '01'

if it is GRPO, just change '59' to be '20'.

Rgds,