CREATE PROCEDURE [dbo].[CHECK_UI_METADATA]
AS
BEGIN
DECLARE
@var_err_code int,
@var_err_table_name nvarchar(100),
@var_pk1_field_value nvarchar(100),
@var_pk2_field_value nvarchar(100),
@var_err_field_name nvarchar(100),
@var_err_field_value nvarchar(255),
@var_err_message nvarchar(255),
@var_err_resolution nvarchar(255),
@var_table_name nvarchar(32),
@var_field_name nvarchar(32),
@var_join_name nvarchar(100),
@var_class_name nvarchar(100),
@var_attribute_name nvarchar(100),
@var_actual_table_name nvarchar(32),
@var_actual_field_name nvarchar(32),
@var_data_type int,
@var_data_size int,
@var_field_required int,
@var_primary_key int,
@var_foreign_key int,
@var_class_alias nvarchar(100),
@var_class_template nvarchar(100),
@var_attribute_alias nvarchar(100),
@var_attribute_label nvarchar(255),
@var_relation_type int,
@var_attribute_type nvarchar(100),
@var_related_base_attribute nvarchar(100),
@var_form_id int,
@var_form_attribute_id int,
@var_formlet_name nvarchar(255),
@var_group_in nvarchar(255),
@var_attribute_path nvarchar(255),
@var_search_formlet nvarchar(255),
@var_position int,
@var_column_index int,
@var_ascx_path nvarchar(255),
@var_hlf_navigate_url nvarchar(255),
@var_duplicate_count int
SET @var_err_code = 700
SET @var_err_message = 'Warning: path_id not used in any personalization table'
SET @var_err_resolution = 'create personalization records'
SET @var_err_table_name = 'al_paths'
SET @var_err_field_name = 'path_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
SET @var_err_code = 800
SET @var_err_message = 'Warning: profile does not have any assigned pages/webparts'
SET @var_err_resolution = 'assign pages/webparts to profile'
SET @var_err_table_name = 'al_profile_def'
SET @var_err_field_name = 'profile_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_PROFILE_DEF.PROFILE_ID
FROM dbo.AL_PROFILE_DEF
WHERE AL_PROFILE_DEF.PROFILE_ID NOT IN
(
SELECT AL_LINK_PAGE_WEBPART_PROFILE.PROFILE_ID
FROM dbo.AL_LINK_PAGE_WEBPART_PROFILE
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 801
SET @var_err_message = 'Warning: profile does not have any assigned form attributes'
SET @var_err_resolution = 'assign form attributes to profile'
SET @var_err_table_name = 'al_profile_def'
SET @var_err_field_name = 'profile_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_PROFILE_DEF.PROFILE_ID
FROM dbo.AL_PROFILE_DEF
WHERE AL_PROFILE_DEF.PROFILE_ID NOT IN
(
SELECT AL_LINK_FORM_ATTRIBUTE_PROFILE.PROFILE_ID
FROM dbo.AL_LINK_FORM_ATTRIBUTE_PROFILE
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 900
SET @var_err_message = 'Warning: page not linked to any webparts'
SET @var_err_resolution = 'link page to a webpart'
SET @var_err_table_name = 'al_page_def'
SET @var_err_field_name = 'page_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_PAGE_DEF.PAGE_ID
FROM dbo.AL_PAGE_DEF
WHERE AL_PAGE_DEF.PAGE_ID NOT IN
(
SELECT AL_LINK_PAGE_WEBPART.PAGE_ID
FROM dbo.AL_LINK_PAGE_WEBPART
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 901
SET @var_err_message = 'page path has incorrect slash'
SET @var_err_resolution = 'use forwards slashes in page path'
SET @var_err_table_name = 'al_page_def'
SET @var_err_field_name = 'page_path'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_PAGE_DEF.PAGE_ID, AL_PAGE_DEF.PAGE_PATH
FROM dbo.AL_PAGE_DEF
WHERE AL_PAGE_DEF.PAGE_PATH LIKE '%\%'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1000
SET @var_err_message = 'Warning: webpart not linked to any pages'
SET @var_err_resolution = 'link webpart to a page'
SET @var_err_table_name = 'al_webpart_def'
SET @var_err_field_name = 'webpart_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_WEBPART_DEF.WEBPART_ID
FROM dbo.AL_WEBPART_DEF
WHERE AL_WEBPART_DEF.WEBPART_ID NOT IN
(
SELECT AL_LINK_PAGE_WEBPART.WEBPART_ID
FROM dbo.AL_LINK_PAGE_WEBPART
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1001
SET @var_err_message = 'ascx path has incorrect slash'
SET @var_err_resolution = 'use backwards slashes in ascx path'
SET @var_err_table_name = 'al_webpart_def'
SET @var_err_field_name = 'ascx_path'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_WEBPART_DEF.WEBPART_ID, AL_WEBPART_DEF.ASCX_PATH
FROM dbo.AL_WEBPART_DEF
WHERE AL_WEBPART_DEF.ASCX_PATH LIKE '%/%'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1100
SET @var_err_message = 'Warning: page/webpart is not assigned to any profiles'
SET @var_err_resolution = 'assign page/webpart to a profile'
SET @var_err_table_name = 'al_link_page_webpart'
SET @var_err_field_name = 'page_webpart_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_LINK_PAGE_WEBPART.PAGE_WEBPART_ID
FROM dbo.AL_LINK_PAGE_WEBPART
WHERE AL_LINK_PAGE_WEBPART.PAGE_WEBPART_ID NOT IN
(
SELECT AL_LINK_PAGE_WEBPART_PROFILE.PAGE_WEBPART_ID
FROM dbo.AL_LINK_PAGE_WEBPART_PROFILE
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1200
SET @var_err_message = 'Warning: form does not have any attributes'
SET @var_err_resolution = 'add attributes to form'
SET @var_err_table_name = 'al_form_def'
SET @var_err_field_name = 'form_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT AL_FORM_DEF.FORM_ID
FROM dbo.AL_FORM_DEF
WHERE AL_FORM_DEF.FORM_ID NOT IN
(
SELECT AL_FORM_ATTRIBUTE_DEF.FORM_ID
FROM dbo.AL_FORM_ATTRIBUTE_DEF
)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1201
SET @var_err_message = 'dependent params value does not match any attributes'
SET @var_err_resolution = 'check dependent params attribute value'
SET @var_err_table_name = 'al_form_def'
SET @var_err_field_name = 'form_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT f.FORM_ID, f.DEPENDENT_PARAMS
FROM
dbo.AL_FORM_DEF AS f
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS a
ON a.ATTRIBUTE_NAME = replace(f.DEPENDENT_PARAMS, 'QUERYSTRING:', NULL)
WHERE
f.DEPENDENT_PARAMS LIKE 'QUERYSTRING:%' AND
f.DEPENDENT_PARAMS NOT LIKE '%.%' AND
a.ATTRIBUTE_NAME IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1300
SET @var_err_message = 'Warning: form attribute is not assigned to any profiles'
SET @var_err_resolution = 'assign form attribute to a profile'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'form_attribute_id'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
LEFT OUTER JOIN dbo.AL_LINK_FORM_ATTRIBUTE_PROFILE AS l
ON l.FORM_ATTRIBUTE_ID = fad.FORM_ATTRIBUTE_ID
WHERE fad.FORM_ATTRIBUTE_ID IS NOT NULL AND l.FORM_ATTRIBUTE_ID IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1301
SET @var_err_message = 'search_formlet does not exist in al_form_def'
SET @var_err_resolution = 'correct value or add form record'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'search_formlet'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.SEARCH_FORMLET
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
LEFT OUTER JOIN dbo.AL_FORM_DEF AS fd2
ON fd2.FORMLET_NAME = fad.SEARCH_FORMLET
WHERE fad.SEARCH_FORMLET IS NOT NULL AND fd2.FORMLET_NAME IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1302
SET @var_err_message = 'form attribute is a duplicate'
SET @var_err_resolution = 'remove the duplicate attribute'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
SET @var_err_code = 1303
SET @var_err_message = 'Warning: form attribute position is a duplicate within a form'
SET @var_err_resolution = 'reorder positions'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'position'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
SET @var_err_code = 1304
SET @var_err_message = 'Warning: form attribute position is a duplicate within a group'
SET @var_err_resolution = 'reorder positions'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'position'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
SET @var_err_code = 1305
SET @var_err_message = 'Warning: column_index is greater than form columncount'
SET @var_err_resolution = 'correct column_index or increase columncount'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'column_index'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.COLUMN_INDEX
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.COLUMN_INDEX - 1 > fd.COLUMNCOUNT
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_WARNINGS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1306
SET @var_err_message = 'class_name does not exist in arg_class_def'
SET @var_err_resolution = 'correct value or add class record'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'class_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.CLASS_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
LEFT OUTER JOIN dbo.ARG_CLASS_DEF AS cd
ON cd.CLASS_NAME = fad.CLASS_NAME
WHERE fad.CLASS_NAME IS NOT NULL AND cd.CLASS_NAME IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1307
SET @var_err_message = 'class_name does not exist in SWCM class records'
SET @var_err_resolution = 'correct value, add class record, or check app_filter'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'class_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.CLASS_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
INNER JOIN dbo.AL_APPLICATIONS AS a
ON a.APPLICATION_ID = fd.APPLICATION_ID
LEFT OUTER JOIN dbo.ARG_CLASS_DEF AS cd
ON cd.CLASS_NAME = fad.CLASS_NAME AND cd.APP_FILTER IN ( 1, 3 )
WHERE
fad.CLASS_NAME IS NOT NULL AND
cd.CLASS_NAME IS NULL AND
a.APPLICATION_NAME = 'SWCM'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1308
SET @var_err_message = 'class_name does not exist in UAPM class records'
SET @var_err_resolution = 'correct value, add class record, or check app_filter'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'class_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.CLASS_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
INNER JOIN dbo.AL_APPLICATIONS AS a
ON a.APPLICATION_ID = fd.APPLICATION_ID
LEFT OUTER JOIN dbo.ARG_CLASS_DEF AS cd
ON cd.CLASS_NAME = fad.CLASS_NAME AND cd.APP_FILTER IN ( 2, 3 )
WHERE
fad.CLASS_NAME IS NOT NULL AND
cd.CLASS_NAME IS NULL AND
a.APPLICATION_NAME = 'UAPM'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1309
SET @var_err_message = 'attribute_name does not exist in arg_attribute_def'
SET @var_err_resolution = 'correct value or add attribute record'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.ATTRIBUTE_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
FULL OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad
ON fad.CLASS_NAME = ad.CLASS_NAME AND fad.ATTRIBUTE_NAME = ad.ATTRIBUTE_NAME
WHERE fad.ATTRIBUTE_PATH IS NULL AND ad.ATTRIBUTE_NAME IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1310
SET @var_err_message = 'attribute_name does not exist in SWCM attribute records'
SET @var_err_resolution = 'correct value, add attribute record, or check app_filter'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.ATTRIBUTE_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
INNER JOIN dbo.AL_APPLICATIONS AS a
ON a.APPLICATION_ID = fd.APPLICATION_ID
FULL OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad
ON
fad.CLASS_NAME = ad.CLASS_NAME AND
fad.ATTRIBUTE_NAME = ad.ATTRIBUTE_NAME AND
ad.APP_FILTER IN ( 1, 3 )
WHERE
fad.ATTRIBUTE_PATH IS NULL AND
ad.ATTRIBUTE_NAME IS NULL AND
a.APPLICATION_NAME = 'SWCM'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1311
SET @var_err_message = 'attribute_name does not exist in UAPM attribute records'
SET @var_err_resolution = 'correct value, add attribute record, or check app_filter'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.ATTRIBUTE_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
INNER JOIN dbo.AL_APPLICATIONS AS a
ON a.APPLICATION_ID = fd.APPLICATION_ID
FULL OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad
ON
fad.CLASS_NAME = ad.CLASS_NAME AND
fad.ATTRIBUTE_NAME = ad.ATTRIBUTE_NAME AND
ad.APP_FILTER IN ( 2, 3 )
WHERE
fad.ATTRIBUTE_PATH IS NULL AND
ad.ATTRIBUTE_NAME IS NULL AND
a.APPLICATION_NAME = 'UAPM'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1312
SET @var_err_message = 'hlf navigate url has incorrect slash'
SET @var_err_resolution = 'use forwards slashes in hlf navigate url'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'hlf_navigate_url'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.HLF_NAVIGATE_URL
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.HLF_NAVIGATE_URL LIKE '%\%'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1313
SET @var_err_message = 'group_in must be specified when search_formlet is not null'
SET @var_err_resolution = 'add a group_in value to the record'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'group_in'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.GROUP_IN
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.SEARCH_FORMLET IS NOT NULL AND fad.GROUP_IN IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1314
SET @var_err_message = 'hlf_navigate_url must be specified when control_type_id := 5'
SET @var_err_resolution = 'specify hlf_navigate_url values or check control_type_id'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'hlf_navigate_url'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.HLF_NAVIGATE_URL
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.CONTROL_TYPE_ID = 5 AND fad.HLF_NAVIGATE_URL IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1315
SET @var_err_message = 'hlf_navigate_url_fields must be specified when control_type_id := 5'
SET @var_err_resolution = 'specify hlf_navigate_url values or check control_type_id'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'hlf_navigate_url_fields'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.HLF_NAVIGATE_URL_FIELDS
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.CONTROL_TYPE_ID = 5 AND fad.HLF_NAVIGATE_URL_FIELDS IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1316
SET @var_err_message = 'attribute must be primary key field when is_datakey := 1'
SET @var_err_resolution = 'check attribute and is_datakey values'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.ATTRIBUTE_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad
ON fad.CLASS_NAME = ad.CLASS_NAME AND fad.ATTRIBUTE_NAME = ad.ATTRIBUTE_NAME
LEFT OUTER JOIN dbo.ARG_FIELD_DEF AS f
ON f.TABLE_NAME = ad.TABLE_NAME AND f.FIELD_NAME = ad.FIELD_NAME
WHERE
f.FIELD_NAME IS NOT NULL AND
f.PRIMARY_KEY <> 1 AND
fad.IS_DATAKEY = 1
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1317
SET @var_err_message = 'simple attribute in foreign_search_keys must exist in arg_attribute_def'
SET @var_err_resolution = 'correct foreign_search_keys value'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'foreign_search_keys'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.FOREIGN_SEARCH_KEYS
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad
ON ad.ATTRIBUTE_NAME = fad.FOREIGN_SEARCH_KEYS
WHERE
fad.FOREIGN_SEARCH_KEYS IS NOT NULL AND
ad.ATTRIBUTE_NAME IS NULL AND
fad.FOREIGN_SEARCH_KEYS NOT LIKE '%.%'
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1318
SET @var_err_message = 'foreign_display_type cannot be null when control_type_id := 13'
SET @var_err_resolution = 'specify foreign_display_type or check control_type_id value'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'foreign_display_type'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.FOREIGN_DISPLAY_TYPE
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.FOREIGN_DISPLAY_TYPE IS NULL AND fad.CONTROL_TYPE_ID = 13
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1319
SET @var_err_message = 'attribute_path must be null when control_type_id := 13'
SET @var_err_resolution = 'remove attribute_path or check control_type_id value'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_path'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.ATTRIBUTE_PATH
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE fad.ATTRIBUTE_PATH IS NOT NULL AND fad.CONTROL_TYPE_ID = 13
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1320
SET @var_err_message = 'foreign key attribute must have its associated id attribute also in the form'
SET @var_err_resolution = 'add id attribute for foreign key to form'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_name'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad13.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad13.ATTRIBUTE_NAME
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad13
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad13.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad13
ON ad13.CLASS_NAME = fad13.CLASS_NAME AND ad13.ATTRIBUTE_NAME = fad13.ATTRIBUTE_NAME
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad1
ON ad1.CLASS_NAME = ad13.CLASS_NAME AND ad1.ATTRIBUTE_NAME = ad13.RELATED_BASE_ATTRIBUTE
LEFT OUTER JOIN dbo.AL_FORM_ATTRIBUTE_DEF AS fad1
ON
fad1.CLASS_NAME = ad1.CLASS_NAME AND
fad1.ATTRIBUTE_NAME = ad1.ATTRIBUTE_NAME AND
fad1.FORM_ID = fad13.FORM_ID
WHERE
fad13.CONTROL_TYPE_ID = 13 AND
fad13.ATTRIBUTE_NAME IS NOT NULL AND
fad1.ATTRIBUTE_NAME IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1321
SET @var_err_message = 'associated id attribute for a foreign key must have a null attribute_path value'
SET @var_err_resolution = 'check values'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_path'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad13.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad1.ATTRIBUTE_PATH
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad13
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad13.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad13
ON ad13.CLASS_NAME = fad13.CLASS_NAME AND ad13.ATTRIBUTE_NAME = fad13.ATTRIBUTE_NAME
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad1
ON ad1.CLASS_NAME = ad13.CLASS_NAME AND ad1.ATTRIBUTE_NAME = ad13.RELATED_BASE_ATTRIBUTE
LEFT OUTER JOIN dbo.AL_FORM_ATTRIBUTE_DEF AS fad1
ON
fad1.CLASS_NAME = ad1.CLASS_NAME AND
fad1.ATTRIBUTE_NAME = ad1.ATTRIBUTE_NAME AND
fad1.FORM_ID = fad13.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ade
ON ade.ATTRIBUTE_NAME = ad13.CLASS_NAME
WHERE
fad13.CONTROL_TYPE_ID = 13 AND
fad13.ATTRIBUTE_NAME IS NOT NULL AND
fad1.ATTRIBUTE_NAME IS NOT NULL AND
fad1.ATTRIBUTE_PATH IS NOT NULL AND
(ade.RELATION_TYPE <> 3 OR ade.RELATION_TYPE IS NULL)
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1322
SET @var_err_message = 'associated id attribute for a foreign key must have a null attribute_path value'
SET @var_err_resolution = 'check values'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'attribute_path'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad13.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad1.FOREIGN_SEARCH_KEYS
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad13
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad13.FORM_ID
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad13
ON ad13.CLASS_NAME = fad13.CLASS_NAME AND ad13.ATTRIBUTE_NAME = fad13.ATTRIBUTE_NAME
LEFT OUTER JOIN dbo.ARG_ATTRIBUTE_DEF AS ad1
ON ad1.CLASS_NAME = ad13.CLASS_NAME AND ad1.ATTRIBUTE_NAME = ad13.RELATED_BASE_ATTRIBUTE
LEFT OUTER JOIN dbo.AL_FORM_ATTRIBUTE_DEF AS fad1
ON
fad1.CLASS_NAME = ad1.CLASS_NAME AND
fad1.ATTRIBUTE_NAME = ad1.ATTRIBUTE_NAME AND
fad1.FORM_ID = fad13.FORM_ID
WHERE
fad13.CONTROL_TYPE_ID = 13 AND
fad13.ATTRIBUTE_NAME IS NOT NULL AND
fad1.ATTRIBUTE_NAME IS NOT NULL AND
fad1.FOREIGN_SEARCH_KEYS IS NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1323
SET @var_err_message = 'foreign_search_keys must be specified if attribute_path is not null'
SET @var_err_resolution = 'add foreign_search_keys value'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'foreign_search_keys'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
BEGIN
DECLARE
err_records CURSOR LOCAL FOR
SELECT fad.FORM_ATTRIBUTE_ID, fd.FORMLET_NAME, fad.FOREIGN_SEARCH_KEYS
FROM
dbo.AL_FORM_ATTRIBUTE_DEF AS fad
INNER JOIN dbo.AL_FORM_DEF AS fd
ON fd.FORM_ID = fad.FORM_ID
WHERE
fad.ATTRIBUTE_PATH IS NOT NULL AND
fad.FOREIGN_SEARCH_KEYS IS NULL AND
fad.GROUP_IN IS NOT NULL
OPEN err_records
WHILE 1 = 1
BEGIN
FETCH err_records
INTO @var_pk1_field_value, @var_pk2_field_value, @var_err_field_value
IF @@FETCH_STATUS <> 0
BREAK
INSERT dbo.ARG_METADATA_ERRORS(
ERR_CODE,
ERR_TABLE_NAME,
PK1_FIELD_VALUE,
PK2_FIELD_VALUE,
ERR_FIELD_NAME,
ERR_FIELD_VALUE,
ERR_MESSAGE,
ERR_RESOLUTION)
VALUES (
@var_err_code,
@var_err_table_name,
@var_pk1_field_value,
@var_pk2_field_value,
@var_err_field_name,
@var_err_field_value,
@var_err_message,
@var_err_resolution)
END
CLOSE err_records
DEALLOCATE err_records
END
SET @var_err_code = 1324
SET @var_err_message = 'search formlet form does not have foreign search keys in its attributes'
SET @var_err_resolution = 'add foreign_search_keys to search form'
SET @var_err_table_name = 'al_form_attribute_def'
SET @var_err_field_name = 'foreign_search_keys'
SET @var_pk1_field_value = NULL
SET @var_pk2_field_value = NULL
SET @var_err_field_value = NULL
END
GO
[dbo].[CHECK_UI_METADATA]