Skip to Content
0

sales opportunity activity report

Feb 16, 2017 at 10:10 AM

27

avatar image

error.jpg

Hi Friends

my client having report of sales opportunity activity report

This report is working all the sales employees but particularly one sales employee code is not working

please guide me

--ALL DUE OPEN ACTIVITIES FOR sales8 LINKED TO OPEN OPPORTUNITIES FOR ANY SALES PERSON

SELECT bp.CardCode AS 'BP Code', bp.CardName AS 'BP Name', Cast(opp.OpprId AS NVARCHAR) AS 'Opp ID', stn.descript AS 'Customer Status', (SELECT usr2.U_NAME FROM OSLP sal2, OUSR usr2, OOPR opp2 WHERE sal2.U_BOY_50_BRKO = usr2.USER_CODE AND opp2.SlpCode = sal2.SlpCode AND opp2.OpprId = opp.OpprId) AS 'Opp Owner', opp.MaxSumLoc AS 'Opp Value', Cast(act.ClgCode AS NVARCHAR) AS 'Act ID', usr.U_NAME AS 'Act Owner', act.Priority AS Priority, act.Details AS 'Activity Notes', act.Recontact AS 'Act Date', Right('0'+Left(act.BeginTime,(Len(act.BeginTime)-2))+':'+ Right(act.BeginTime,2),5) AS 'Act Time', cont.[E_MailL] AS 'Contact Email' FROM OCLG act, OSLP sal, OUSR usr, OCRD bp, OOPR opp, OPR1 sta, OOST stn, OCPR cont WHERE act.SlpCode = sal.SlpCode AND sal.U_BOY_50_BRKO = usr.USER_CODE AND act.CardCode = bp.CardCode AND act.CntctCode = cont.CntctCode AND opp.OpprId = sta.OpprId AND sta.Step_Id = stn.Num AND act.parentType = 97 AND act.parentId = opp.OpprId AND act.Closed = 'N' AND opp.Status = 'O' AND DATEDIFF(dd,act.Recontact,GETDATE()) >=0 AND act.Recontact > '2012-05-31' AND usr.USER_CODE = 'sales8'

--ALL DUE OPEN ACTIVITIES FOR ANY USER LINKED TO OPEN OPPORTUNITIES FOR sales8

UNION SELECT bp.CardCode AS 'BP Code', bp.CardName AS 'BP Name', Cast(opp.OpprId AS NVARCHAR) AS 'Opp ID', stn.descript AS 'Customer Status', usr.U_NAME AS 'Opp Owner', opp.MaxSumLoc AS 'Opp Value', Cast(act.ClgCode AS varchar) AS 'Act ID', (SELECT usr2.U_NAME FROM OSLP sal2, OUSR usr2, OCLG act2 WHERE sal2.U_BOY_50_BRKO = usr2.USER_CODE AND act2.SlpCode = sal2.SlpCode AND act2.ClgCode = act.ClgCode) AS 'Act Owner', act.Priority AS Priority, act.Details AS 'Activity Notes', act.Recontact AS 'Act Date', Right('0'+Left(act.BeginTime,(Len(act.BeginTime)-2))+':'+Right(act.BeginTime,2),5) AS 'Act Time', cont.[E_MailL] AS 'Contact Email' FROM OCLG act, OSLP sal, OUSR usr, OCRD bp, OOPR opp, OPR1 sta, OOST stn, OCPR cont WHERE opp.SlpCode = sal.SlpCode AND sal.U_BOY_50_BRKO = usr.USER_CODE AND opp.CardCode = bp.CardCode AND act.CntctCode = cont.CntctCode AND opp.OpprId = sta.OpprId AND sta.Step_Id = stn.Num AND act.parentType = 97 AND act.parentId = opp.OpprId AND act.Closed = 'N' AND opp.Status = 'O' AND DATEDIFF(dd,act.Recontact,GETDATE()) >=0 AND act.Recontact > '2012-05-31' AND usr.USER_CODE = 'sales8'

--ALL OPEN ACTIVITIES FOR sales8 WIHOUT A LINKED OPPORTUNITY UNION

SELECT bp.CardCode AS 'BP Code', bp.CardName AS 'BP Name', 'N/a' AS 'Opp ID', '' AS 'Customer Status', 'N/a' AS 'Opp Owner', 0 AS 'Opp Value', Cast(act.ClgCode AS NVARCHAR) AS 'Act ID', usr.U_NAME AS 'Act Owner', act.Priority AS Priority, act.Details AS 'Activity Notes', act.Recontact AS 'Act Date', Right('0'+Left(act.BeginTime,(Len(act.BeginTime)-2))+':' +Right(act.BeginTime,2),5) AS 'Act Time', cont.[E_MailL] AS 'Contact Email' FROM OSLP sal, OUSR usr, OCRD bp, OCLG act, OCPR cont WHERE act.SlpCode = sal.SlpCode AND sal.U_BOY_50_BRKO = usr.USER_CODE AND act.CardCode = bp.CardCode AND act.CntctCode = cont.CntctCode AND act.Closed = 'N' AND usr.USER_CODE = 'sales8' AND NOT EXISTS (SELECT opp3.OpprId FROM OOPR opp3 WHERE opp3.OpprId = act.parentId AND act.parentType = 97) AND act.Recontact > '2012-05-31' ORDER BY [Act Date], Priority DESC, [Opp Value] DESC

error.jpg (14.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers