if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_INCRLOAD_CUSTOMER_PROCESS_TH]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_INCRLOAD_CUSTOMER_PROCESS_TH]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE USP_INCRLOAD_CUSTOMER_PROCESS_TH
 
AS
BEGIN
SET NOCOUNT ON
-------------------------------------------------------------------------------
-- Author :  KH Kwan
-- Purpose : Updates the CUSTOMER Dimension (Incremental)
-- Date Created : Feb 21 2005
-- Version :
-- 21 Feb 2005 Version 0.1 - Update the Vintage / Age and Beneficiary/Holder/Insured Flags
--
--    
--------------------------------------------------------------------------------
--------------------------------------------------------
-- Declare all variables here
--------------------------------------------------------
DECLARE @is_Success tinyint
DECLARE @intRowCount integer
DECLARE @intErrorCode integer
DECLARE @sourceSystemID nvarchar(5)
DECLARE @sourceFileName nvarchar(50)
DECLARE @PackageName nvarchar(50)
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
DECLARE @ProcessingCount integer
DECLARE @Status nvarchar(50)
DECLARE @Remarks nvarchar(1000)
DECLARE @iReturn integer
--------------------------------------------------------
-- Set all the required variables here
--------------------------------------------------------
SELECT @sourceSystemID = ''
SELECT @sourceFileName = ''
SELECT @PackageName = 'USP_INCRLOAD_CUSTOMER_PROCESS_TH'
SELECT @DateStart = getdate()
SELECT @DateEnd = getdate()
SELECT @ProcessingCount = 0
SELECT @Status = 'Job Started'
SELECT @Remarks = 'Incremental Load for Customer - Update the Customer Vintage/Age/Policy Profile Flags'
SELECT @is_Success = 1
----------------------------------------------------------------------------------------
-- Start of job and log job details
----------------------------------------------------------------------------------------
     EXEC @iReturn = dbo.USP_LOG_JOBDETAILS  'I', 
       @sourceSystemID,
       @sourceFileName,
       @PackageName,
       @DateStart,
         @DateEnd,
       @ProcessingCount,
       @Status,
       @Remarks,
       @iReturn 
          
      PRINT @iReturn
----------------------------------------------------------------------------------------
-- Update the Preferred Flag
--- Based on the POLICY EXPIRY / ISSUE DATE
----------------------------------------------------------------------------------------
--RECALCULATE THE AGE
   UPDATE CVM_STAGE_TH.dbo.CUSTOMER
   SET AGE = FLOOR(
                DATEDIFF (
  DAY,
  DOB,
  GETDATE()
       ) / 365.25),
    TIMESTAMP_ID = GetDate() 
FROM CUSTOMER
WHERE
    FLOOR(
        DATEDIFF (
       DAY,
     DOB,
     GETDATE()
          ) / 365.25
         ) <> AGE
AND DOB IS NOT NULL AND DOB <> '1900-01-01'
----------------------------------------------------------------------------------------
-- Update the Flags and Vintage
---
----------------------------------------------------------------------------------------
/* SELECTS 'Y' FOR THE Y/N FLAG ATTRIBUTES BELOW. MERGING RULE IS: IF ONE SET TO 'Y' THEN 'Y' ELSE 'N' */
UPDATE CUSTOMER
SET IS_INSURED = vs.IS_INSURED,
    IS_BENEFICIARY = vs.IS_BENEFICIARY,
    IS_HOLDER = vs.IS_HOLDER,
    IS_ACTIVE = vs.IS_ACTIVE,
    CUSTOMER_INACTIVE_DATE = vs.CUSTOMER_INACTIVE_DATE,   
    EFFECTIVE_DATE = vs.EFFECTIVE_DATE, 
    VINTAGE = vs.VINTAGE, 
    VINTAGE_MONTHS = vs.VINTAGE_MONTHS,
    TIMESTAMP_ID = GetDate() 
FROM
CUSTOMER c
    INNER JOIN
(
SELECT  ECM.CUSTOMER_ID,
 ISNULL(( TTT.IS_INSURED),('N')) AS  IS_INSURED, 
 ISNULL(( TTT.IS_BENEFICIARY),('N')) AS  IS_BENEFICIARY, 
 ISNULL(( TTT.IS_HOLDER),('N')) AS  IS_HOLDER,
 CASE
    WHEN EP2.ANY_INFORCE = 1
    THEN 'Y'
    ELSE 'N'
 END     AS   IS_ACTIVE, 
 CASE
    WHEN EP2.ANY_INFORCE = 1
    THEN NULL
    ELSE TTT.CUSTOMER_INACTIVE_DATE
 END     AS   CUSTOMER_INACTIVE_DATE, 
 ISNULL((CONVERT(VARCHAR(10),(TTT.ISSUE_DATE),101)),( '01/01/1900')) AS  EFFECTIVE_DATE,
 TTT.ISSUE_DATE     ISSUE_DATE,
 TTT.EXPIRY_DATE     EXPIRY_DATE, 
 /* TTT.ISSUE DATE IS THE EARLIEST POLICY ISSUE DATE FOR THE DEDUPED CUSTOMER */
-- Call HKS106360: START
 CEILING(CAST(
 DATEDIFF
  (mm, --YEAR, 
  CASE WHEN  TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE
          THEN GETDATE()
       ELSE TTT.ISSUE_DATE
  END,
  CASE WHEN  
 /* CVM-101, KHO, 7-OCT-03 */
    EP2.ANY_INFORCE = 1 OR
 /* CVM-101, KHO, 7-OCT-03 */     
    TTT.EXPIRY_DATE IS NULL OR
    TTT.EXPIRY_DATE > GETDATE() OR
    TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE     
       THEN GETDATE()
       ELSE TTT.EXPIRY_DATE
                END ) AS DECIMAL) / 12.0)   AS VINTAGE,  
-- Call HKS106360: END    

 
 DATEDIFF(MONTH,
  CASE WHEN  TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE
       THEN GETDATE()
       ELSE TTT.ISSUE_DATE END,
  CASE WHEN  
 /* CVM-101, KHO, 7-OCT-03 */
    EP2.ANY_INFORCE = 1 OR
 /* CVM-101, KHO, 7-OCT-03 */
    TTT.EXPIRY_DATE IS NULL OR
    TTT.EXPIRY_DATE > GETDATE() OR
    TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE          
  THEN GETDATE()
  ELSE TTT.EXPIRY_DATE
  END)     AS VINTAGE_MONTHS
FROM CUSTOMER ECM
LEFT OUTER JOIN
  (
  SELECT
     pp.ROLE_ID         CUSTOMER_SSKEY,
     MAX(ep.POLICY_ID)        POLICY_ID, 
     MIN(pp.TRANS_DATE)        ISSUE_DATE,
     CASE WHEN MIN(ep.CANCEL_DATE) is NULL THEN GetDate()
          WHEN MAX(ep.CANCEL_DATE) >= GetDate() THEN GetDate()
          ELSE MAX(ep.CANCEL_DATE) END      EXPIRY_DATE, 
     MAX(ep.CANCEL_DATE)        CUSTOMER_INACTIVE_DATE,
     MAX(CASE WHEN pp.POLICY_PROFILE_TYPE = 2000 THEN 'Y' ELSE 'N' END)  IS_HOLDER,
     MAX(CASE WHEN pp.POLICY_PROFILE_TYPE = 2001 THEN 'Y' ELSE 'N' END)  IS_BENEFICIARY,
     MAX(CASE WHEN pp.POLICY_PROFILE_TYPE = 2003 THEN 'Y' ELSE 'N' END)  IS_INSURED 
  FROM
     POLICY_PROFILE pp
   LEFT OUTER JOIN CVM_STAGE_TH.dbo.POLICY ep
    ON pp.POLICY_ID = ep.POLICY_ID
  GROUP BY ROLE_ID
  ) TTT 
   ON  ECM.CUSTOMER_ID = TTT.CUSTOMER_SSKEY
    
LEFT OUTER JOIN
   (SELECT pc.CUSTOMER_ID,
---     MAX(CASE
---      WHEN p.CANCEL_DATE IS NULL
--      THEN 1 ELSE 0 END) ANY_INFORCE
-- STDize to use the Policy Status to determine in-force policies
       MAX(CASE
      WHEN p.STATUS IN ( '01', '02', '03', '04', '05')
      THEN 1 ELSE 0 END) ANY_INFORCE
     FROM CVM_STAGE_TH.dbo.POLICY p
--    INNER JOIN CVM_STAGE_TH.dbo.POLICY_CUSTOMER pc
    INNER JOIN CVM_STAGE_TH.dbo.CUST_POLICY_RELATION pc
    ON p.POLICY_ID = pc.POLICY_ID
    GROUP BY  pc.CUSTOMER_ID
   ) EP2
  ON ECM.CUSTOMER_ID = EP2.CUSTOMER_ID
WHERE
TTT.POLICY_ID IS NOT NULL
AND
(
ISNULL(( TTT.IS_INSURED),('N')) <> ECM.IS_INSURED OR
ISNULL(( TTT.IS_BENEFICIARY),('N')) <> ECM.IS_BENEFICIARY OR
ISNULL(( TTT.IS_HOLDER),('N')) <> ECM.IS_HOLDER OR
CASE
    WHEN EP2.ANY_INFORCE = 1
    THEN 'Y'
    ELSE 'N'
END <> ECM.IS_ACTIVE
OR
ISNULL((CONVERT(VARCHAR(10),(TTT.CUSTOMER_INACTIVE_DATE),101)),( '01/01/1900')) <> ECM.CUSTOMER_INACTIVE_DATE
OR
ISNULL((CONVERT(VARCHAR(10),(TTT.ISSUE_DATE),101)),( '01/01/1900')) <> ECM.EFFECTIVE_DATE
OR
-- Call HKS106360 START
CEILING(CAST(
DATEDIFF
  (mm, --YEAR, 
  CASE WHEN  TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE
          THEN GETDATE()
       ELSE TTT.ISSUE_DATE
  END,
  CASE WHEN  
 /* CVM-101, KHO, 7-OCT-03 */
    EP2.ANY_INFORCE = 1 OR
 /* CVM-101, KHO, 7-OCT-03 */     
    TTT.EXPIRY_DATE IS NULL OR
    TTT.EXPIRY_DATE > GETDATE() OR
    TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE     
       THEN GETDATE()
       ELSE TTT.EXPIRY_DATE
                END ) AS DECIMAL) / 12.0)
-- Call HKS106360 END
<> ECM.VINTAGE
OR
DATEDIFF(MONTH,
  CASE WHEN  TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE
       THEN GETDATE()
       ELSE TTT.ISSUE_DATE END,
  CASE WHEN  
 /* CVM-101, KHO, 7-OCT-03 */
    EP2.ANY_INFORCE = 1 OR
 /* CVM-101, KHO, 7-OCT-03 */
    TTT.EXPIRY_DATE IS NULL OR
    TTT.EXPIRY_DATE > GETDATE() OR
    TTT.ISSUE_DATE IS NULL OR
    TTT.ISSUE_DATE = '1900-01-01' OR
    TTT.ISSUE_DATE > TTT.EXPIRY_DATE          
  THEN GETDATE()
  ELSE TTT.EXPIRY_DATE
  END)    
<>
ECM.VINTAGE_MONTHS
)
) vs
ON c.CUSTOMER_ID = vs.CUSTOMER_ID
SELECT @intRowCount = @@ROWCOUNT, @intErrorCode = @@ERROR
IF @intErrorCode <> 0
    BEGIN
  SELECT @remarks = 'Error Encountered During Insert. Error Code: ' + CAST(@intErrorCode as varchar(5))
  SELECT @is_Success = 0
  SELECT @Status = 'Job Failed'
    END 
ELSE
          BEGIN
  SELECT @remarks =  'Number of Updated Records :' + CAST(@intRowCount as varchar(5))
  SELECT @Status = 'Job Ended'
   END  
----------------------------------------------------------------------------------------
-- End of job and log job details
----------------------------------------------------------------------------------------
SELECT @DateEnd = getdate()
SELECT @ProcessingCount = @intRowCount
 EXEC @iReturn = dbo.USP_LOG_JOBDETAILS  'U', 
       @sourceSystemID,
       @sourceFileName,
       @PackageName,
       @DateStart,
         @DateEnd,
       @ProcessingCount,
       @Status,
       @Remarks,
       @iReturn 
          
     
RETURN @is_Success
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO