IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].TS_pRP_Filtered') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].TS_pRP_Filtered
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.TS_pRP_Filtered
@owner_co_id int,
@user_id int,
@Document_id int,
@Document_version int,
@Document_type int,
@SQLFilter varchar(4000) = NULL,
@ContractNo nvarchar(100) = NULL,
@SelectedSuppNo nvarchar(100) = NULL,
@ContractType nvarchar(100) = NULL,
@ContractDescription nvarchar(100) = NULL,
@ContractManager nvarchar(100) = NULL,
@ContractorName nvarchar(100) = NULL,
@OrgUnit nvarchar(100) = NULL,
@ContractStartDateEval1 nvarchar(100) = NULL,
@ContractStartDate1 nvarchar(100) = NULL,
@ContractStartDateEval2 nvarchar(100) = NULL,
@ContractStartDate2 nvarchar(100) = NULL,
@ContractStartEndEval1 nvarchar(100) = NULL,
@ContractEndDate1 nvarchar(100) = NULL,
@ContractStartEndEval2 nvarchar(100) = NULL,
@ContractEndDate2 nvarchar(100) = NULL,
@Language nvarchar(100) = NULL
AS
DECLARE @SQLString nvarchar(4000)
Declare @SQL nvarchar(max)
DECLARE @doc_id int
DECLARE @doc_vers_id int
DECLARE @doc_type_id int
DECLARE @CRole Varchar(50)
SET @doc_id = @Document_id
SET @doc_vers_id = @Document_version
SET @doc_type_id = @Document_type
SET NOCOUNT ON
IF (@owner_co_id IS NOT NULL)
BEGIN
Set @SQL = 'SELECT Distinct
ch.po_contract_id,
ch.supplement_no,
0 AS selected_row,
ISNULL(ch.po_contract_no,'''') AS po_contract_no,
ISNULL(ch.start_dt,'''') AS start_dt,
ISNULL(ch.end_dt,'''') AS end_dt,
ISNULL(rct.contract_type_cd,'''') AS contract_type_cd,
ISNULL(OU.org_unit_cd,'''') AS org_unit_cd,
ISNULL(th.template_cd,'''') AS template_cd
FROM CN_Contract_Header ch
INNER JOIN CN_Ref_Contract_Type rct
ON ch.contract_type_id = rct.contract_type_id
Inner join CM_Ref_language RL
ON RL.language_id = ch.language_id
INNER JOIN cm_ber_users U
ON CH.contracting_user_id = U.user_id
INNER JOIN CN_Template_Org_Unit tou
ON tu.org_unit_template_id = tou.org_unit_template_id
INNER JOIN CN_Template_Header th
ON tou.template_id = th.template_id
INNER JOIN CM_BER_User_Role_Membership urm
ON urm.user_id = u.user_id
LEFT OUTER JOIN CM_UDF_Value uv1
ON ch.po_contract_id = uv1.record_id
AND ch.supplement_no = uv1.record_version_id
AND uv1.custom_info_id = (SELECT custom_info_id FROM CM_UDF_Custom_Info
WHERE column_name = ''Contract_Classification''
AND extended_table_cd = ''Contract_Hdr''
AND owner_co_id = '''+CAST(@owner_co_id AS varchar) +''')
LEFT OUTER JOIN CM_UDF_Value uv9
ON ch.po_contract_id = uv9.record_id
AND ch.supplement_no = uv9.record_version_id
AND uv9.custom_info_id = (SELECT custom_info_id FROM CM_UDF_Custom_Info
WHERE column_name = ''Approver''
AND extended_table_cd = ''Contract_Hdr''
AND owner_co_id = '''+CAST(@owner_co_id AS varchar) +''')
WHERE CH.supplement_no = (SELECT max(supplement_no)
FROM CN_CONTRACT_HEADER
WHERE po_contract_id = CH.po_contract_id and deleted = ''N'')
AND CH.deleted = ''N''
AND 1=1'
IF ISNULL(@ContractNo,'') <> ''
SELECT @sql = @sql + ' AND ch.po_contract_no LIKE ''%'+@ContractNo+'%'''
IF ISNULL(@SelectedSuppNo,'') <> ''
SELECT @sql = @sql + ' AND ch.supplement_no = '+@SelectedSuppNo
IF ISNULL(@ContractType,'') <> ''
SELECT @sql = @sql + ' AND rct.contract_type_cd LIKE ''%'+@ContractType+'%'''
IF ISNULL(@ContractDescription,'') <> ''
SELECT @sql = @sql + ' AND ch.po_contract_descr LIKE ''%'+@ContractDescription+'%'''
IF ISNULL(@ContractManager,'') <> ''
SELECT @sql = @sql + ' AND u.user_id = '+@ContractManager
IF ISNULL(@ContractorName,'') <> ''
SELECT @sql = @sql + ' AND CP.company_name LIKE ''%'+@ContractorName+'%'''
IF ISNULL(@OrgUnit,'') <> ''
SELECT @sql = @sql + ' AND OU.org_unit_id = '+@OrgUnit
IF ISNULL(@ContractStartDateEval1,'') <> '' AND ISNULL(@ContractStartDate1,'') <> ''
SELECT @sql = @sql + ' AND ch.start_dt '+@ContractStartDateEval1+' '''+@ContractStartDate1+''''
IF ISNULL(@ContractStartDateEval2,'') <> '' AND ISNULL(@ContractStartDate2,'') <> ''
SELECT @sql = @sql + ' AND ch.start_dt '+@ContractStartDateEval2+' '''+@ContractStartDate2+''''
IF ISNULL(@ContractStartEndEval1,'') <> '' AND ISNULL(@ContractEndDate1,'') <> ''
SELECT @sql = @sql + ' AND ch.end_dt '+@ContractStartEndEval1+' '''+@ContractEndDate1+''''
IF ISNULL(@ContractStartEndEval2,'') <>'' AND ISNULL(@ContractEndDate2,'') <> ''
SELECT @sql = @sql + ' AND ch.end_dt '+@ContractStartEndEval2+' '''+@ContractEndDate2+''''
IF ISNULL(@Language,'') <> ''
SELECT @sql = @sql + ' AND RL.language_id = '+@Language
No comments:
Post a Comment