Need to get Default Contact Person, mobile No, Telephone No,Email in SPT

[Former Member]

Hi experts

i had created a SP for Activity Reprot which is listed below

i need to get the Default Contact Person's Mobile No, Telephone No, Email in the query ? How is it possible?

pls any one could edit it to sort it out

-------------------------------------------------------------------------------

USE [SSC_Production]

GO

/****** Object:  StoredProcedure [dbo].[MarktActivityReport]    Script Date: 10/05/2015 10:40:25 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[MarktActivityReport]

@CntctDateFrom datetime,

@CntctDateTo   datetime

AS

Begin

select  T0.ClgCode AS 'ActivityNo',T0.CardCode as 'LeadCode',T1.CardName as 'Lead Name',(SELECT TOP 1 Notes FROM OCLG WHERE ClgCode=T0.ClgCode)  as 'Activity Status',Phone1,T0.Details as 'Reason/Status',

T1.CntctPrsn as 'Contact Person',T0.CntctDate,T3.Name,T0.DocNum as 'Document No',

T4.U_NAME as 'Activity Assigned To',Recontact as 'Start Date',Cellular as 'Mobile No',T0.prevActvty AS 'Pre Act No',

                       CASE T0.DocType

                       when (23)then 'Sales Quotation'

    else 'Invalid Parameters' end AS 'Document Type (1)',

                       CASE T0.Action

                       When ('C') then 'Phone Call'

                       when ('M') then 'Meeting'

                       when ('T')then 'Task'

                       when ('E')then 'Note'

                       when ('P')then 'Campaign'

                       when ('N')then 'Other'

else 'Invalid Parameters' end AS 'Activity Type (1)',T5.Name AS 'Activity Subject',

                       CASE T0.CntctType

                       when (-1) then 'General'

                       when (1) then 'E-Mail Received'

                       when (3) then 'Phone'

                       when (2) then 'E-Mail Send'

                       when (4) then 'Face to Face'

                       when (5) then 'Meeting'

                       when (6) then 'Quotation'

                       When (7) then 'Follow Up'

else 'Invalid Parameters' end AS 'Activity Type(2)',

                       CASE T0.Priority

                       WHEN (2) then 'High'

                       WHEN (0) then 'Low'

                       WHEN (1) then 'Normal'

else 'Invalid Parameters' end AS 'Activity Priority'

from OCLG T0

inner join OCRD T1 on T0.CardCode=T1.CardCode

inner join OCPR T2 on T1.CntctPrsn=T2.Name

Left Outer join OCLO T3 ON T0.Location = T3.Code

inner join OUSR T4 on T0.AttendUser = T4.USERID

inner join OCLS T5 on T0.CntctSbjct=T5.Code

where Recontact between  @CntctDateFrom and @CntctDateTo

GROUP by

T0.ClgCode,T0.CardCode,T1.CardName,T0.Tel,T0.Details,

T1.CntctPrsn,T0.CntctDate,T3.Name,T0.DocNum,

T4.U_NAME, T0.DocType,T0.Action,T0.CntctType,T0.Priority,Recontact,Cellular,Phone1,T5.Name,T0.prevActvty

End

-------------------------------------------------------------------------------------

Regards

[Former Member · Accepted solution]

use this

ALTER PROCEDURE [dbo].[MarktActivityReport]

@CntctDateFrom datetime,

@CntctDateTo   datetime

AS

Begin

select  T0.ClgCode AS 'ActivityNo',T0.CardCode as 'LeadCode',T1.CardName as 'Lead Name',(SELECT TOP 1 Notes FROM OCLG WHERE ClgCode=T0.ClgCode)  as 'Activity Status',Phone1,T0.Details as 'Reason/Status',

T1.CntctPrsn as 'Contact Person',T0.CntctDate,T3.Name,T0.DocNum as 'Document No',

T4.U_NAME as 'Activity Assigned To',Recontact as 'Start Date',Cellular as 'Mobile No',T0.prevActvty AS 'Pre Act No',

t2.Cellolar'Contact Person Mobile No',t2.tel1'Contact Person Tel1',t2.tel2'Contact Person Tel2',t2.E_MailL 'Contact Person Mail-id',

                       CASE T0.DocType

                       when (23)then 'Sales Quotation'

    else 'Invalid Parameters' end AS 'Document Type (1)',

                       CASE T0.Action

                       When ('C') then 'Phone Call'

                       when ('M') then 'Meeting'

                       when ('T')then 'Task'

                       when ('E')then 'Note'

                       when ('P')then 'Campaign'

                       when ('N')then 'Other'

else 'Invalid Parameters' end AS 'Activity Type (1)',T5.Name AS 'Activity Subject',

                       CASE T0.CntctType

                       when (-1) then 'General'

                       when (1) then 'E-Mail Received'

                       when (3) then 'Phone'

                       when (2) then 'E-Mail Send'

                       when (4) then 'Face to Face'

                       when (5) then 'Meeting'

                       when (6) then 'Quotation'

                       When (7) then 'Follow Up'

else 'Invalid Parameters' end AS 'Activity Type(2)',

                       CASE T0.Priority

                       WHEN (2) then 'High'

                       WHEN (0) then 'Low'

                       WHEN (1) then 'Normal'

else 'Invalid Parameters' end AS 'Activity Priority'

from OCLG T0

inner join OCRD T1 on T0.CardCode=T1.CardCode

inner join OCPR T2 on T1.CntctPrsn=T2.Name

Left Outer join OCLO T3 ON T0.Location = T3.Code

inner join OUSR T4 on T0.AttendUser = T4.USERID

inner join OCLS T5 on T0.CntctSbjct=T5.Code

where Recontact between  @CntctDateFrom and @CntctDateTo

GROUP by

T0.ClgCode,T0.CardCode,T1.CardName,T0.Tel,T0.Details,

T1.CntctPrsn,T0.CntctDate,T3.Name,T0.DocNum,

T4.U_NAME, T0.DocType,T0.Action,T0.CntctType,T0.Priority,Recontact,Cellular,Phone1,T5.Name,T0.prevActvty

End

[Former Member · Accepted solution]

Hi raja

Thanks for the support

am having a issue when am running the report with the modified SPT you had provided

for one Activity, who does not have Contact person, is appearing more that once with some other Contact persons Details

Regards

[Former Member · Accepted solution]

In join replace the following things

from OCLG T0

inner join OCRD T1 on T0.CardCode=T1.CardCode

inner join OCPR T2 on T1.CntctPrsn=T2.Name and T1.CardCode=T2.CardCode

Left Outer join OCLO T3 ON T0.Location = T3.Code

inner join OUSR T4 on T0.AttendUser = T4.USERID

inner join OCLS T5 on T0.CntctSbjct=T5.Code