on 2008 Jun 03 2:51 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
113 | |
9 | |
8 | |
6 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.