on 09-11-2012 5:58 AM
Dear All,
I am facing a problem in using a query for generating alerts....
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser, t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval' and t1.U_Status = 'Open' and t2.USERID = $[USER]
This query is poping up only for 'manager' user...Not for any other user...Please help me regarding this... What type of permission should this user have..?
Any kind of help would be appreciated....
Thanks
Hello Ankit,
In the alert management window, you have defined the users that you want to receive that alert. So make sure that all those users are logged into the system (including the manager if he is defined to receive the alert).
Wait for the alert to be triggered following the rule you set up in the Alert Management Window (hourly, daily, monthly, etc).
Check if the alert is being sent.
I had this same problem, and I found the behaviour.
Kind regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What is you purpose using the $[user] expression? The $[user] returns the code of the actual user only if it is executed in a formatted search!
If you want to send the alert to the user defined by the t2.userid, you should create separate queries for every user with hard-coded userid!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
The purpose behind using the $[USER] was as follows:
See if there are 12 Entries for a document.
User1 made 6 of them, User2 made 3 of them, User3 made 2 and User4 made 1. Total 12 are there.
But then User1 should get the alert of approval only for those document, to which he has created. As User1 added the 6 document. Then alert should be only for those 6 documents..
Hope you Understood..
Thanks
Hi,
In your UDT @REQUISITIONMASTER, you should have an Integer field to match USERID column in OUSR to link.
Link by text is always problematic.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Change the Query
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser, t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM dbo.[@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval' and t1.U_Status = 'Open' and t2.USERID = $[USER]
Check it
Thanks,
Nithi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I think this is an authorization issue.... Pls check this link. It may help you....
http://www.scribd.com/doc/52389761/4/General-Authorizations
and Just try with the Full Authorization in Query Manager.
Good Luck...
Regards,
Priya
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try This
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser,
t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval'
and t1.U_Status = 'Open' and t2.Internal_k = $[USER]
Regards
Kennedy
HI Ankit,
Save This Query and execute first if it produce the result then assign to alert...
Declare @d1 as int
set @d1 = ( Select INTERNAL_K from OUSR where INTERNAL_K=$[USER])
select @d1
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser,
t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval'
and t1.U_Status = 'Open' and t2.Internal_k = @d1
Regards
Kennedy
Declare @d1 as int
set @d1 = ( Select INTERNAL_K from OUSR where INTERNAL_K=$[USER])
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser,
t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval'
and t1.U_Status = 'Open' and t2.Internal_k = @d1
Small mistake try this
Hey
i think this will work out... First save the query and then execute and check does the query retrive the correct output.. if it retrieves out put then try to alert....
Declare @d1 as int
set @d1 = ( Select INTERNAL_K from OUSR where INTERNAL_K=$[USER])
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser,
t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
inner join OUSR t2 on t1.U_SAPUser=t2.U_NAME Where t1.U_Approval = 'Approval'
and t1.U_Status = 'Open' and t2.Internal_k = @d1
Hope helpful.
Regards
Kennedy
Try This
Declare @d1 as nvarchar(100)
set @d1 = ( Select U_NAME from OUSR where INTERNAL_K=$[USER])
SELECT t1.DocNum as 'Requisition No.', t1.U_RequestBy, t1.U_SAPUser,
t1.U_Status, t1.U_Approval, t1.U_Remarks, t1.U_ISType, t1.U_ReqTypeM FROM [@REQUISITIONMASTER] t1
Where t1.U_Approval = 'Approval'
and t1.U_Status = 'Open' and t1.U_SAPUser = @d1
Regards
kennedy
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
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.