Business Partner Activities !!

[Former Member]

Hello Experts,

I have got a problem for which i am looking for advice. SBO user in our company uses BP and BP Activities extensively because we are a service type organization. If each account manager has 300 customers in the DB then its very difficult to to keep an eye on every BP.

Scenario 1.

No calls on few Business Partners from past 30 days -

---

need to be pop-ed up on the screen

Scenario 2.

Forgot to create activity by mistake.. then that particular BP will go in dark clouds.

I am looking for a query where i can tell every user to check all those BP's who are inactive in terms of receiving calls or you have not contacted them from past 30 days.

by this we can squeeze the possibility of BP Avoidance.

tell me the way to develop that kind of report

please help.

[former_member204969 · Accepted solution]

Try this query to get the partners without activities in the last 30 days:

SELECT  T0.[CardCode],
 max(T0.[Recontact]) 'Last activity'
FROM OCLG T0 
WHERE T0.[CardCode] is not null and  T0.[CardCode] <>''
Group by T0.[CardCode]
Having min(datediff(d,T0.[Recontact],getdate()))>30

[Former Member · Accepted solution]

Hi,

It is throughing syntax error due to ' ' before Group by and i need this detail by sales employee wise.

[former_member204969 · Accepted solution]

Try this one:

declare @slp int
set @slp=(SELECT T.[SlpCode] FROM  OSLP T
Where  T.[SlpName] =[%0])
SELECT  T0.[CardCode],
 max(T0.[Recontact]) 'Last activity'
FROM OCLG T0 
WHERE T0.[CardCode] is not null and  T0.[CardCode] !='' 
 and (Select T.SlpCode from OCRD T where T.CardCode=T0.CardCode)=@slp
Group by T0.[CardCode]
Having min(datediff(d,T0.[Recontact],getdate()))>30

(The error was caused by the interface, which does not transfer the other form of not equal expression.)

[Former Member · Accepted solution]

hi this Query seems to be fine but the only thing is i need the golden arrow besides the customer code so that we can click and access the details.

thanks in advance

[former_member204969 · Accepted solution]

Then try this:

SELECT  T0.[CardCode],
 max(T0.[Recontact]) 'Last activity'
FROM OCLG T0
 Inner join OCRD p on p.CardCode=T0.CardCode
 Inner join OSLP s on s.SlpCode=p.SlpCode
WHERE T0.[CardCode] is not null and  T0.[CardCode] !='' 
 and s.SlpName ='[%0]'
Group by T0.[CardCode]
Having min(datediff(d,T0.[Recontact],getdate()))>30

[Former Member · Accepted solution]

Everything perfect.. but i dnt know how you wrote this query coz i am not able able to understand it.. though i am not expert but know a bit..

i am asking you to make the changes but if i would have understood the same then i would have done it myself.

please last thing.. kindly add the name of customer as well in the second column.

Cheers !!

[former_member204969 · Accepted solution]

SELECT  T0.[CardCode],min(p.CardName),
 max(T0.[Recontact]) 'Last activity'
FROM OCLG T0
 Inner join OCRD p on p.CardCode=T0.CardCode
 Inner join OSLP s on s.SlpCode=p.SlpCode
WHERE T0.[CardCode] is not null and  T0.[CardCode] !='' 
 and s.SlpName ='[%0]'
Group by T0.[CardCode]
Having min(datediff(d,T0.[Recontact],getdate()))>30

[Former Member · Accepted solution]

Hi there,

i was also looking for this type of report so i got the answer but just a quick question can we add the last activity number and a drill down option to open that activity from this report.

any way thank you very much for this query.

Thanks,

Prachi.

[former_member204969 · Accepted solution]

You can display the last activity number replacing the first line:

SELECT  T0.[CardCode],min(p.CardName),max(T0.ClgCode),

but only without the yellow arrow!

[Former Member · Accepted solution]

Hi istvan,

Thank you very much for your help.

Thanks,

Prachi.

[Former Member · Accepted solution]

Hi Prachi,

Are you working with OM LOGISTICS LTD ?

[Former Member · Accepted solution]

Hi Isha,

NO i am working in the UK

Thanks,

Prachi.

[Former Member · Accepted solution]

can i have your e-mail id ?? i would like to get in touch or you can send me the details at

ishchopra at g mail dot com

[Former Member · Accepted solution]

hi Isha,

Sent you the mail

Prachi