validating the mandatory columns code by using ssis

USE [Vue_Amerilife_Temp]
GO
/****** Object: StoredProcedure [dbo].[usp_SAVE_ContractExceptionRecords] Script Date: 01/28/2010 14:03:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
Author Name: Sreenivas.M
Reviewed By:
Date: 06/24/2009
Description: This Procedure will Handle Exceptions in Agent Role File
Usage: This Procedure is used for Agent Role conversion

EXEC usp_SAVE_ContractExceptionRecords 31, 3
*********************************************************************/

ALTER PROCEDURE [dbo].[usp_SAVE_ContractExceptionRecords]
@PFILEID INT,
@PROCESSERR INT
AS

--Insert MTOPTION values
INSERT INTO AL_MTOPTION (CATEGORY, ITEM, ISACTIVE, DONOTSHOW, DISTRIBUTIONID, INSERTBY, INSERTDATE)
SELECT DISTINCT 'Contract Name', CONTRACTNAME, 1, 0, 1, 'Initial Load', CONVERT(VARCHAR, GETDATE(), 101)
FROM TEMP_AGENTCONTRACT T WHERE NOT EXISTS (SELECT 1
FROM AL_MTOPTION M WHERE M.CATEGORY = 'Contract Name' AND T.CONTRACTNAME = M.ITEM)

INSERT INTO AL_MTOPTION (CATEGORY, ITEM, ISACTIVE, INSERTBY, INSERTDATE)
SELECT DISTINCT 'Contract Number', CONTRACTNUMBER, 1, 'Initial Load', CONVERT(VARCHAR, GETDATE(), 101)
FROM TEMP_AGENTCONTRACT T WHERE NOT EXISTS (SELECT 1
FROM AL_MTOPTION M WHERE M.CATEGORY = 'Contract Number' AND T.CONTRACTNUMBER = M.ITEM)

--Agent Code
INSERT INTO UPLOADRECORDEXCEPTIONS(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)

SELECT
RECORDNO,
@PFILEID,
'AGENTID DOES NOT EXISTS ',
'AGENTID DOES NOT EXISTS FOR AGENTCODE : ' + AGENTCODE,
GETDATE(),
@PROCESSERR
FROM
TEMP_AGENTCONTRACT
where AGENTCODE is not null and
AGENTCODE not in (
select AGENTCODE from AL_AGENT )

--Effective From Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid CONTRACTEFFECTIVEFROM' as Category,
'CONTRACTEFFECTIVEFROM Should be in mm/dd/yyyy :' + CONTRACTEFFECTIVEFROM as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(CONTRACTEFFECTIVEFROM,'12/31/9999') )=0

--Effective Till Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid CONTRACTEFFECTIVETILL' as Category,
'CONTRACTEFFECTIVETILL Should be in mm/dd/yyyy :' + CONTRACTEFFECTIVETILL as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(CONTRACTEFFECTIVETILL,'12/31/9999') )=0

--Status Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid STATUSDATE' as Category,
'STATUSDATE Should be in mm/dd/yyyy :' + STATUSREASONDATE as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(STATUSDATE,'12/31/9999') )=0

--Status Reason Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid STATUSREASONDATE' as Category,
'STATUSREASONDATE Should be in mm/dd/yyyy :' + STATUSREASONDATE as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(STATUSREASONDATE,'12/31/9999') )=0

--Received Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid RECEIVEDDATE' as Category,
'RECEIVEDDATE Should be in mm/dd/yyyy :' + RECEIVEDDATE as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(RECEIVEDDATE,'12/31/9999') )=0

--Received Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid CONTRACTACTIVATIONDATE' as Category,
'CONTRACTACTIVATIONDATE Should be in mm/dd/yyyy :' + CONTRACTACTIVATIONDATE as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(CONTRACTACTIVATIONDATE,'12/31/9999') )=0

--Received Date
INSERT INTO
UPLOADRECORDEXCEPTIONS
(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Invalid CONTRACTSIGNEDDATE' as Category,
'CONTRACTSIGNEDDATE Should be in mm/dd/yyyy :' + CONTRACTSIGNEDDATE as Description,
Getdate(),
@PROCESSERR
FROM TEMP_AGENTCONTRACT
WHERE isdate(isnull(CONTRACTSIGNEDDATE,'12/31/9999') )=0

--Field Office Code
INSERT INTO UPLOADRECORDEXCEPTIONS(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)

SELECT
RECORDNO,
@PFILEID,
'FIELD OFFICE DOES NOT EXISTS ',
'FIELD OFFICE NOT EXISTS FOR : ' + AGENTCODE,
GETDATE(),
@PROCESSERR
FROM
TEMP_AGENTCONTRACT
where FIELDOFFICECODE is not null and
FIELDOFFICECODE not in (
select CODE from AL_FIELDOFFICE )

--Start date > End Date
INSERT INTO UPLOADRECORDEXCEPTIONS(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)

SELECT
RECORDNO,
@PFILEID,
'Start Date is Greater than End Date ',
'Start Date is Greater than End Date ' + AGENTCODE,
GETDATE(),
@PROCESSERR
FROM
TEMP_AGENTCONTRACT
where CONVERT(DATETIME, CONTRACTEFFECTIVEFROM, 101) > CONVERT(DATETIME, CONTRACTEFFECTIVETILL, 101)

INSERT INTO UPLOADRECORDEXCEPTIONS(
RECORDNO,
FILEID,
CATEGORY,
DESCRIPTION,
INSERTDATE,
TYPEID
)
SELECT
RECORDNO,
@PFILEID,
'Duplicate Agent Contract',
'Agent Contract From Date and To Date overlaping with other contract : ' + AGENTCODE,
GETDATE(),
@PROCESSERR
FROM
TEMP_AGENTCONTRACT
where RECORDNO IN
(
SELECT
tc1.RECORDNO
FROM
(SELECT AGENTCODE, FIELDOFFICECODE
FROM TEMP_AGENTCONTRACT
GROUP BY AGENTCODE, FIELDOFFICECODE
HAVING COUNT(*) > 1) TMP
INNER JOIN TEMP_AGENTCONTRACT tc ON tc.AGENTCODE = TMP.AGENTCODE AND tc.FIELDOFFICECODE = TMP.FIELDOFFICECODE
INNER JOIN TEMP_AGENTCONTRACT tc1 ON tc1.AGENTCODE = tc.AGENTCODE AND tc1.FIELDOFFICECODE = TMP.FIELDOFFICECODE AND
((CONVERT(DATETIME, ISNULL(tc1.CONTRACTEFFECTIVEFROM, '1/1/9999'))
BETWEEN CONVERT(DATETIME, ISNULL(tc.CONTRACTEFFECTIVEFROM, '1/1/9999'))
AND CONVERT(DATETIME, ISNULL(tc.CONTRACTEFFECTIVETILL, '1/1/9999')))
OR (CONVERT(DATETIME, ISNULL(tc1.CONTRACTEFFECTIVETILL, '1/1/9999'))
BETWEEN CONVERT(DATETIME, ISNULL(tc.CONTRACTEFFECTIVEFROM, '1/1/9999'))
AND CONVERT(DATETIME, ISNULL(tc.CONTRACTEFFECTIVETILL, '1/1/9999'))))
AND tc.RECORDNO != tc1.RECORDNO
) ORDER BY AGENTCODE


UPDATE TEMP_AGENTCONTRACT
SET Processstatus = @PROCESSERR
FROM TEMP_AGENTCONTRACT INNER JOIN
UPLOADRECORDEXCEPTIONS AS U ON TEMP_AGENTCONTRACT.RECORDNO = U.RECORDNO
AND U.FILEID = @PFILEID AND U.TYPEID = @PROCESSERR