The detail page stored procedure will return data needed by the detail page. Depending on the requirements, this SP can return multiple tables. When you make changes to the base stored procedure, it is recommended that you save the stored procedure with a new name, as to not overwrite the base information.
All Column Names and Table names used in the Stored Procedure should be strictly written in UPPERCASE. To implement a dynamic module like Member directory, Personify has done a lot of XML parsing. Since XML is case sensitive, having Columns and Tables in any other case will lead to inconsistent results.
For a complete sample stored procedure, please see Sample Member Directory Detail Page Stored Procedure.
To create the detail page stored procedure:
1. The stored procedure name should start with USP_MEMBERDIRECTORY_DETAIL_SEARCH_XXXX
CREATE PROCEDURE [dbo].[usp_MEMBERDIRECTORY_DETAIL_SEARCH_XXXXX]
2. Decide the input parameters based on requirements. The @SCHEMA_ONLY, @IP_ORG_UNIT_ID, @IP_IDENTITY_SCID are mandatory input parameters and will be passed by the web controls. The rest of the parameters are passed to the SP when the user clicks on the record on the search page
(
@SCHEMA_ONLY BIT = 0, --EITHER THIS CAN BE STORE ONLY 0 AND 1,
@IP_ORG_ID ORG =null,
@IP_ORG_UNIT_ID ORG =null,
@IP_IDENTITY_MCID CUSTOMER_ID = null,
@IP_IDENTITY_SCID SUB_SEQUENCE =null,
@IP_RECORD_TYPE VARCHAR(10) = null
)
AS
BEGIN
3. The following part must be defined to return the schema of the data the sP is returning. In this example we are returning the data from the following tables
· FTS_CUSTOMER_INFO
o IMAGEID, LABEL_NAME, PRIMARY_JOB_TITLE, EMP_NAME, ADDR_INFO, CONTACT_LABEL_NAME
· CUS_ADDRESS_VW
o FORMATTED_DETAIL, FORMATTED_ADDRESS
· CUS_COMMUNICATION
o FORMATTED_HTML_RECORD
· CUS_EDUCATION
o EDUCATION
· CUS_BIOGRAPHY
o BIOGRAPHY_TEXT
· CUS_SPECIALTY
o CUS_SPECIALTY_CODE
· CUS_PRODUCT
o PRODUCT_BRAND_NAME
· CUS_RELATED_EMPLOYEES
o RECIPROCAL_CODE
o RELATED_NAME
· CUS_RELATED_SUBSIDIARIES
o RECIPROCAL_CODE
o RELATED_NAME
IF @SCHEMA_ONLY = 1
BEGIN
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'IMAGEID' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'LABEL_NAME' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'PRIMARY_JOB_TITLE' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'EMP_NAME' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'ADDR_INFO' COLUMN_NAME
UNION ALL
SELECT 'FTS_CUSTOMER_INFO' TABLE_NAME, 'CONTACT_LABEL_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_ADDRESS_VW' TABLE_NAME, 'FORMATTED_DETAIL' COLUMN_NAME
UNION ALL
SELECT 'CUS_ADDRESS_VW' TABLE_NAME, 'FORMATTED_ADDRESS' COLUMN_NAME
UNION ALL
SELECT 'CUS_COMMUNICATION' TABLE_NAME, 'FORMATTED_HTML_RECORD' COLUMN_NAME
UNION ALL
SELECT 'CUS_EDUCATION' TABLE_NAME, 'EDUCATION' COLUMN_NAME
UNION ALL
SELECT 'CUS_BIOGRAPHY' TABLE_NAME, 'BIOGRAPHY_TEXT' COLUMN_NAME
UNION ALL
SELECT 'CUS_SPECIALTY' TABLE_NAME, 'CUS_SPECIALTY_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_PRODUCT' TABLE_NAME, 'PRODUCT_BRAND_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_EMPLOYEES' TABLE_NAME, 'RECIPROCAL_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_EMPLOYEES' TABLE_NAME, 'RELATED_NAME' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_SUBSIDIARIES' TABLE_NAME, 'RECIPROCAL_CODE' COLUMN_NAME
UNION ALL
SELECT 'CUS_RELATED_SUBSIDIARIES' TABLE_NAME, 'RELATED_NAME' COLUMN_NAME
return;
END
4. The following code selects data for the header section of the detail page:
select F.IMAGEID,F.LABEL_NAME, F.PRIMARY_JOB_TITLE , F.DISPLAY_LINE_2 as EMP_NAME,
F.DISPLAY_LINE_1 + '|' + F.COUNTRY_CODE AS ADDR_INFO , CONTACT_LABEL_NAME
from FTS_CUSTOMER_INFO F with (nolock)
where F.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and F.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
5. The following code select address information:
select CA.FORMATTED_DETAIL, CA.FORMATTED_ADDRESS
from CUS_ADDRESS_VW CA with (nolock)
where CA.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CA.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CA.CONFIDENTIAL_FLAG = 'N'
and CA.WEB_MOBILE_DIRECTORY_FLAG = 'Y'
order by CA.DIRECTORY_PRIORITY
6. The following code selects communication records. This example highlights how organizations can form the HTML in the data base and the control will pick up the HTML and render accordingly. Note the path of the image is specified in the SP. Organizations will have to make sure this is correct path.
-- For fetch communication records
declare @lv_htmlEmail VARCHAR(500)
declare @lv_htmlPhone VARCHAR(500)
declare @lv_htmlFacebook VARCHAR(500)
declare @lv_htmlLinkedIn VARCHAR(500)
declare @lv_htmlPinterest VARCHAR(500)
declare @lv_htmlSocial VARCHAR(500)
-- Set to change the image URL
declare @lv_imageURL VARCHAR(50) = '../../images/SocialShare/'
-- Icon names for varios records
declare @lv_EmailIconName Varchar(50) = 'mailto16.png'
declare @lv_PhoneIconName Varchar(50) = 'phone16.png'
declare @lv_FacebookIconName Varchar(50) = 'facebook16.png'
declare @lv_LinkedInIconName Varchar(50) = 'twitter16.png'
declare @lv_PinterestIconName Varchar(50) = 'pinterest16.png'
--
declare @lv_BlankIconName Varchar(50) = 'blank16.png'
-- Style for all images and hyper links
declare @lv_AnchorCssClass Varchar(50) = 'membership-dir-detail-link'
declare @lv_ImageCssClass Varchar(50) = 'membership-dir-detail-contact-icon'
-- HTML for EMAIL, PHONE and other web sites (Social or Web)
set @lv_htmlEmail = '<a class="{AnchorCssClass}" href="mailto:{formatted_text}"><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="{AltText}" " >{formatted_text}</a>'
set @lv_htmlPhone = '<a class="{AnchorCssClass}" ><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="Phone" >{formatted_text}</a>'
set @lv_htmlSocial = '<a class="{AnchorCssClass}" href="{formatted_text}" target=_Blank><img class="{ImageCssClass}" src="{Image_URL}{Image_Name}" alt="{AltText}" >{formatted_text}</a>'
select
case A.OPTION_1
When 'PHONE' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlPhone ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_PhoneIconName),
'{AltText}','Phone'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
When 'WEB' then
case A.OPTION_2
When 'EMAIL' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlEmail ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_EmailIconName),
'{AltText}','Send Email'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
Else
Case C.COMM_LOCATION_CODE
When 'LINKEDIN' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_LinkedInIconName),
'{AltText}','Linked In'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
When 'FACEBOOK' then
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_FacebookIconName),
'{AltText}','Go to Facebook'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
Else -- Blank Icon
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(@lv_htmlSocial ,
'{formatted_text}' , C.FORMATTED_PHONE_ADDRESS ),
'{Image_URL}', @lv_imageURL),
'{Image_Name}',@lv_BlankIconName),
'{AltText}','Go to Web Site'),
'{AnchorCssClass}',@lv_AnchorCssClass),
'{ImageCssClass}',@lv_ImageCssClass)
End
end
End as FORMATTED_HTML_RECORD
from CUS_COMMUNICATION C
inner join APP_CODE A on A.SUBSYSTEM = 'CUS' and A.TYPE = 'COMM_TYPE' and A.CODE = C.COMM_TYPE_CODE
where C.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and C.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and C.WEB_MOBILE_DIRECTORY_FLAG = 'Y'
7. The following code selects Education, Biography, Speciality, Related Products, Related Staff, Related Subsidiaries. If the requirement is to return certain data only for companies, the where clause and @IP_RECORD_TYPE = 'C' can be appended to the select statement.
Select CE.PROG_DEGREE_CODE + ', ' + CE.INSTITUTION_NAME as EDUCATION
From CUS_EDUCATION CE with (nolock)
where CE.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CE.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
Select CB.BIOGRAPHY_TEXT
From CUS_BIOGRAPHY CB with (nolock)
where CB.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CB.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
--and @IP_RECORD_TYPE = 'I'
Select AC.DESCR as CUS_SPECIALTY_CODE
From CUS_SPECIALTY CS with (nolock)
inner join APP_CODE AC on AC.SUBSYSTEM = 'CUS' and AC.TYPE = 'CUS_SPECIALTY' and CS.CUS_SPECIALTY_CODE = AC.CODE
where CS.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CS.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and @IP_RECORD_TYPE = 'I'
Select CP.PRODUCT_BRAND_NAME
From CUS_PRODUCT CP with (nolock)
where CP.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CP.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
--and @IP_RECORD_TYPE = 'C'
--related staff
select CR.RECIPROCAL_CODE, CR.RELATED_NAME from CUS_RELATIONSHIP CR
Where CR.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CR.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CR.RELATIONSHIP_TYPE = 'EMPLOYMENT'
and CR.ACTIVE_FLAG = 'Y'
and @IP_RECORD_TYPE = 'C'
order by CR.RELATED_NAME
-- subsidiaries
select CR.RECIPROCAL_CODE , CR.RELATED_NAME from CUS_RELATIONSHIP CR
Where CR.MASTER_CUSTOMER_ID = @IP_IDENTITY_MCID
and CR.SUB_CUSTOMER_ID = @IP_IDENTITY_SCID
and CR.RELATIONSHIP_TYPE = 'CORPORATE'
and CR.ACTIVE_FLAG = 'Y'
and @IP_RECORD_TYPE = 'C'
order by CR.RELATED_NAME
END