USE [Core]
GO
/****** Object:  StoredProcedure [lib].[process_type_1_2]    Script Date: 12/18/2017 5:29:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- *****************************************************************
-- Description: This is the function to process 01, 02 relation_code for
--				Tmp_Cust_Relationship by business date
--
--
-- Input Parameters: N/A
--
-- Output Parameters: N/A
--
-- Error Conditions Raised: N/A
--
-- Author:      Hant82
--
-- Revision History
-- Date            Author       Reason for Change
-- ----------------------------------------------------------------
-- 04 Dec 2017		Hant82		Created.
-- *****************************************************************
ALTER PROCEDURE [lib].[process_type_1_2]
(
	@d_business_date date
)
AS
BEGIN
	DECLARE @v_Type1 VARCHAR(2);
	DECLARE @v_Type2 VARCHAR(2);
	DECLARE @v_Type13 VARCHAR(2);
	DECLARE @v_Type14 VARCHAR(2);

	SET @v_Type1 = '01'
	SET @v_Type2 = '02'
	SET @v_Type13 = '13'
	SET @v_Type14 = '14'

	BEGIN TRANSACTION process_data
	IF (Object_Id('Tempdb..#tmp_cte2') IS NOT NULL) DROP TABLE #tmp_cte2;
	IF (Object_Id('Tempdb..#tmp_cte3') IS NOT NULL) DROP TABLE #tmp_cte3;

	DECLARE @tmp TABLE (
		[child_id] INT,
		[parent_id] INT
		);
	WITH tmp_data
	AS (
		SELECT iv1.parent_id AS child_id,
			NULL AS parent_id
		FROM Provision..Cust_Relation iv1
		WHERE NOT EXISTS (
				SELECT 1
				FROM Provision..Cust_Relation
				WHERE Child_ID = iv1.parent_id
				)
		UNION
		SELECT child_id,
			parent_id
		FROM Provision..Cust_Relation
		)
	INSERT INTO @tmp
	SELECT *
	FROM tmp_data;

	WITH cte
	AS (
		SELECT child_id,
			parent_id,
			child_id AS group_id,
			1 AS LEVEL
		FROM @tmp
		WHERE parent_id IS NULL

		UNION ALL

		SELECT child.child_id,
			child.parent_id,
			parent.group_id,
			parent.LEVEL + 1 AS LEVEL
		FROM @tmp child
		INNER JOIN cte parent ON child.parent_id = parent.child_id
		)

	SELECT * INTO #tmp_cte2 FROM cte;

	SELECT child_id,
		parent_id,
		-9999 AS group_id
	INTO #tmp_cte3
	FROM #tmp_cte2
	GROUP BY child_id,
		parent_id
	HAVING count(1) > 1

	UPDATE tmp2
	SET group_id = -9999
	FROM #tmp_cte2 tmp2
	WHERE EXISTS (
			SELECT *
			FROM #tmp_cte3 b
			WHERE tmp2.child_id = b.child_id
				AND b.parent_id = tmp2.parent_id
			)

	INSERT INTO Provision..Tmp_Cust_Relationship
	SELECT cr.Cust_Nature_Key,
		cr.FK_Cust_ID,
		cr.FK_Rel_Cust_ID,
		cr.Relationship_Code,
		tmp.group_id,
		1,
		cr.Business_Date,
		cr.Business_Rule_Status
	FROM Provision..Tmp_Cust_Relationship cr
	INNER JOIN #tmp_cte2 tmp ON cr.FK_Cust_ID = tmp.child_id
		AND cr.FK_Rel_Cust_ID = tmp.parent_id
	WHERE cr.Relationship_Code IN (@v_Type1, @v_Type13)
		AND cr.Business_Date = @d_Business_Date
		AND Processed = 0
	UNION
	SELECT cr.Cust_Nature_Key,
		cr.FK_Cust_ID,
		cr.FK_Rel_Cust_ID,
		cr.Relationship_Code,
		tmp.group_id,
		1,
		cr.Business_Date,
		cr.Business_Rule_Status
	FROM Provision..Tmp_Cust_Relationship cr
	INNER JOIN #tmp_cte2 tmp ON cr.FK_Cust_ID = tmp.parent_id
		AND cr.FK_Rel_Cust_ID = tmp.child_id
	WHERE Relationship_Code IN (@v_Type2, @v_Type14)
		AND cr.Business_Date = @d_Business_Date
		AND Processed = 0

	IF (Object_Id('Tempdb..#tmp_cte2') IS NOT NULL) DROP TABLE #tmp_cte2;
	IF (Object_Id('Tempdb..#tmp_cte3') IS NOT NULL) DROP TABLE #tmp_cte3;

	DELETE FROM Provision..Tmp_Cust_Relationship WHERE Relationship_Code IN (@v_Type1, @v_Type2, @v_Type13, @v_Type14) AND Processed = 0 AND Business_Date = @d_Business_Date
	COMMIT TRANSACTION process_data
END