PROCEDURE "CT_PF_Costing_CleanUp"
(
ItemCode NVARCHAR(100)		--	when empty -> all
)
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS

sqlstr_exec nvarchar(4000);
_char char(1);
PriceSys CHAR (1);
CodeForItem table ("Code" nvarchar(50));

cnt int;

BEGIN



DECLARE CURSOR CURSOR_TBL2 FOR 
------------
SELECT 
	l."LogName"		as "LogNameL", 
	h."LogTable"	as "LogNameH" 
from
	"UDO1" as l
	inner join 
		"OUDO" as h 
		on	1=1 
where
	l."Code" in ('CT_PF_ItemCosting')
and	h."Code" in ('CT_PF_ItemCosting');


_char := char(39);
sqlstr_exec := '';
SELECT "PriceSys" into PriceSys FROM "OADM";


		
---------------------------------------------------------------------------------------------------------------------------------------------
---						:ItemCode = '' -> all Items
---------------------------------------------------------------------------------------------------------------------------------------------
IF :ItemCode = N''
THEN
	----------------------------------------------------------------------
	--Fix UpdateDate with time component in OITC	
	SELECT COUNT('A') INTO cnt 
	from "@CT_PF_OITC" as itc
	where itc."UpdateDate" != cast(itc."UpdateDate" as date);
					
	IF :cnt>0 
	THEN 
		update 
			itc
		set
			itc."UpdateDate" = cast(itc."UpdateDate" as date)
		from
			"@CT_PF_OITC" as itc
		where
			itc."UpdateDate" != cast(itc."UpdateDate" as date);
	END IF;
	----
	
	----------------------------------------------------------------------
	--Fix UpdateDate with time component in AITC
	SELECT COUNT('A') INTO cnt
	from "@CT_PF_AITC" as itc
	where itc."UpdateDate" != cast(itc."UpdateDate" as date);

	IF :cnt>0 
	THEN
		update 
			itc
		set
			itc."UpdateDate" = cast(itc."UpdateDate" as date)
		from
			"@CT_PF_AITC" as itc
		where
			itc."UpdateDate" != cast(itc."UpdateDate" as date);
	END IF;
	----
	
	----------------------------------------------------------------------
	--Remove records from archive child tables without parent in header	
	FOR cur_row as CURSOR_TBL2
	DO
		sqlstr_exec := 
					'delete from 
						"@' || cur_row."LogNameL" || '"
					where
						("Code", "LogInst") in
						(
						select
							l."Code", l."LogInst"
						from
							"@' || cur_row."LogNameL" || '" as l
							left join
								"@' || cur_row."LogNameH" || '" as itc
								on	l."Code"	= itc."Code"
								and	l."LogInst"	= itc."LogInst"
						where
							itc."Code" is null
						)';			
		--select :sqlstr_exec from dummy;
		exec (:sqlstr_exec);
				
	END FOR;
	----

	----------------------------------------------------------------------	
	---	remove unused warehouses (not defined in OITW) from Costing Details (ITC1) and Rolled Costs (ITC5 and ITC4 if exists).
		
	IF :PriceSys = 'N'
	THEN
		--remove invalid warehouse from ITC1
		SELECT COUNT('A') into cnt 
		FROM "@CT_PF_ITC1" as tcl 
		WHERE	"U_WhsCode" != 'ALL' ;
		
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC1" as tcl
			WHERE
				"U_WhsCode" != 'ALL';
		END IF;
		
		--remove invalid warehouse from ITC4
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC4" as tcl 
		WHERE	"U_WhsCode" != 'ALL' ;
		
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC4" as tcl
			WHERE
				"U_WhsCode" != 'ALL';
		END IF;
		
		--remove invalid warehouse from ITC5
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC5" as tcl 
		WHERE	"U_WhsCode" != 'ALL';
		
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC5" as tcl
			WHERE
				"U_WhsCode" != 'ALL'; 
		END IF;
		
		----------------------------------------------------------------------
	ELSE	---	@PriceSys != 'N'
		
		--remove invalid warehouse from ITC1
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC1" as tcl
		INNER JOIN	"@CT_PF_OITC" as itc ON	tcl."Code" = itc."Code"
		LEFT JOIN "OITW" as itw	ON	itw."ItemCode" = itc."U_ItemCode" AND	tcl."U_WhsCode" = itw."WhsCode"
		WHERE itw."ItemCode" is NULL;
		
		IF :cnt>0
		THEN 						
			DELETE 
			FROM
				"@CT_PF_ITC1"
			WHERE
				("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC1" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		
		--remove invalid warehouse from ITC4
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC4" as tcl
		INNER JOIN	"@CT_PF_OITC" as itc ON	tcl."Code" = itc."Code"
		LEFT JOIN "OITW" as itw	ON	itw."ItemCode" = itc."U_ItemCode" AND	tcl."U_WhsCode" = itw."WhsCode"
		WHERE itw."ItemCode" is NULL;
		
		IF :cnt>0
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC4"
			WHERE
				("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC4" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		
		--remove invalid warehouse from ITC5
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC5" as tcl
		INNER JOIN	"@CT_PF_OITC" as itc ON	tcl."Code" = itc."Code"
		LEFT JOIN "OITW" as itw	ON	itw."ItemCode" = itc."U_ItemCode" AND	tcl."U_WhsCode" = itw."WhsCode"
		WHERE itw."ItemCode" is NULL;
		
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC5"
			WHERE
				("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC5" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		----
		
	END IF;
	
	----------------------------------------------------------------------
	---	remove duplicated warehouses, warehouses / Routings from Costing Details (ITC1, //ITC2, ITC3//, ITC5 and ITC4). Keep those with min LineID number.
	
	--remove dupplicated warehouses from ITC1
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC1" as tcl
	LEFT JOIN (
			SELECT "Code", MIN("LineId") as min_LineId FROM	"@CT_PF_ITC1" GROUP BY "Code", "U_WhsCode"
			) as v ON	tcl."Code" = v."Code" AND	tcl."LineId" = min_LineId
	WHERE v."Code" is NULL;
	
	IF :cnt>0 
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC1"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC1" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC1"
					GROUP BY
						"Code",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC2
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC2" as tcl
	LEFT JOIN (
			SELECT "Code", MIN("LineId") as min_LineId FROM	"@CT_PF_ITC2" GROUP BY "Code", "U_WhsCode",	"U_SequenceNo"
			) as v ON	tcl."Code" = v."Code" AND	tcl."LineId" = min_LineId
	WHERE v."Code" is NULL;
	
	IF :cnt>0 
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC2"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC2" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC2"
					GROUP BY
						"Code",
						"U_WhsCode",
						"U_SequenceNo"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC3
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC3" as tcl
	LEFT JOIN (
			SELECT "Code", MIN("LineId") as min_LineId FROM	"@CT_PF_ITC3" GROUP BY "Code", "U_WhsCode",	"U_SequenceNo"
			) as v ON	tcl."Code" = v."Code" AND	tcl."LineId" = min_LineId
	WHERE v."Code" is NULL;
	
	IF :cnt>0 
	THEN 	
		DELETE
		FROM
			"@CT_PF_ITC3"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC3" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC3"
					GROUP BY
						"Code",
						"U_WhsCode",
						"U_SequenceNo"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC4
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC4" as tcl
	LEFT JOIN (
			SELECT "Code", MIN("LineId") as min_LineId FROM	"@CT_PF_ITC4" GROUP BY "Code", "U_RtgCode",	"U_WhsCode"
			) as v ON	tcl."Code" = v."Code" AND	tcl."LineId" = min_LineId
	WHERE v."Code" is NULL;
	
	IF :cnt>0
	THEN
		DELETE
		FROM
			"@CT_PF_ITC4"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC4" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC4"
					GROUP BY
						"Code",
						"U_RtgCode",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			); 
	END IF;
	
	--remove dupplicated warehouses from ITC5
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC5" as tcl
	LEFT JOIN (
			SELECT "Code", MIN("LineId") as min_LineId FROM	"@CT_PF_ITC5" GROUP BY "Code", "U_RtgCode",	"U_WhsCode"
			) as v ON	tcl."Code" = v."Code" AND	tcl."LineId" = min_LineId
	WHERE v."Code" is NULL;
	
	IF :cnt>0	
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC5"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC5" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC5"
					GROUP BY
						"Code",
						"U_RtgCode",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	----------------------------------------------------------------------
	---	Remove entries from ITC4 / ITC5 with Routings that have not been assigned to Revision
	
	--remove invalid routings from ITC4
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC4" as tcl
		INNER JOIN
			"@CT_PF_OITC" as itc
			ON	tcl."Code" = itc."Code"
			LEFT JOIN
				(
				select
					idt."U_ItemCode",
					dt1."U_Code",
					ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
				from
					"@CT_PF_OIDT" as idt
					INNER JOIN
						"@CT_PF_IDT1" as dt1
						ON	idt."Code" = dt1."Code"
						LEFT JOIN
							"@CT_PF_OBOM" as bom
							ON	bom."U_ItemCode" = idt."U_ItemCode"
							AND	bom."U_Revision" = dt1."U_Code"
							LEFT JOIN
								"@CT_PF_BOM11" as bom11
								ON	bom11."Code" = bom."Code"
				) as v
				ON	v."U_ItemCode" = itc."U_ItemCode"
				AND	v."U_Code" = itc."U_RevCode"
				AND	v."U_RtgCode" = tcl."U_RtgCode"
	WHERE
		v."U_ItemCode" is NULL;
	
	IF :cnt>0
	THEN 
		DELETE 
		FROM
			"@CT_PF_ITC4"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC4" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						(
						select
							idt."U_ItemCode",
							dt1."U_Code",
							ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
						from
							"@CT_PF_OIDT" as idt
							INNER JOIN
								"@CT_PF_IDT1" as dt1
								ON	idt."Code" = dt1."Code"
								LEFT JOIN
									"@CT_PF_OBOM" as bom
									ON	bom."U_ItemCode" = idt."U_ItemCode"
									AND	bom."U_Revision" = dt1."U_Code"
									LEFT JOIN
										"@CT_PF_BOM11" as bom11
										ON	bom11."Code" = bom."Code"
						) as v
						ON	v."U_ItemCode" = itc."U_ItemCode"
						AND	v."U_Code" = itc."U_RevCode"
						AND	v."U_RtgCode" = tcl."U_RtgCode"
			WHERE
				v."U_ItemCode" is NULL
			);
	END IF;
	
	--remove invalid routings from ITC5
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC5" as tcl
		INNER JOIN
			"@CT_PF_OITC" as itc
			ON	tcl."Code" = itc."Code"
			LEFT JOIN
				(
				select
					idt."U_ItemCode",
					dt1."U_Code",
					ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
				from
					"@CT_PF_OIDT" as idt
					INNER JOIN
						"@CT_PF_IDT1" as dt1
						ON	idt."Code" = dt1."Code"
						LEFT JOIN
							"@CT_PF_OBOM" as bom
							ON	bom."U_ItemCode" = idt."U_ItemCode"
							AND	bom."U_Revision" = dt1."U_Code"
							LEFT JOIN
								"@CT_PF_BOM11" as bom11
								ON	bom11."Code" = bom."Code"
				) as v
				ON	v."U_ItemCode" = itc."U_ItemCode"
				AND	v."U_Code" = itc."U_RevCode"
				AND	v."U_RtgCode" = tcl."U_RtgCode"
	WHERE
		v."U_ItemCode" is NULL;
	
	IF :cnt>0 
	THEN 
		DELETE 
		FROM
			"@CT_PF_ITC5"
		WHERE
			("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC5" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						(
						select
							idt."U_ItemCode",
							dt1."U_Code",
							ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
						from
							"@CT_PF_OIDT" as idt
							INNER JOIN
								"@CT_PF_IDT1" as dt1
								ON	idt."Code" = dt1."Code"
								LEFT JOIN
									"@CT_PF_OBOM" as bom
									ON	bom."U_ItemCode" = idt."U_ItemCode"
									AND	bom."U_Revision" = dt1."U_Code"
									LEFT JOIN
										"@CT_PF_BOM11" as bom11
										ON	bom11."Code" = bom."Code"
						) as v
						ON	v."U_ItemCode" = itc."U_ItemCode"
						AND	v."U_Code" = itc."U_RevCode"
						AND	v."U_RtgCode" = tcl."U_RtgCode"
			WHERE
				v."U_ItemCode" is NULL
			);
	END IF;
	
	----------------------------------------------------------------------
	--clear UpdateDate from OITC if no entry in AITC
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_OITC" oitc 
	LEFT JOIN "@CT_PF_AITC" aitc 
		ON oitc."Code" = aitc."Code" 
	WHERE 
		aitc."Code" IS NULL 
		AND oitc."UpdateDate" IS NOT NULL;
	
	IF :cnt>0 
	THEN	
		UPDATE oitc 
		SET 
			oitc."UpdateDate" = NULL
			, oitc."UpdateTime" = NULL 
		FROM "@CT_PF_OITC" oitc 
		LEFT JOIN "@CT_PF_AITC" aitc 
			ON oitc."Code" = aitc."Code" 
		WHERE 
			aitc."Code" IS NULL 
			AND oitc."UpdateDate" IS NOT NULL;
	END IF;
	
	----------------------------------------------------------------------
	--- Fix NULL values in costing tables for all iitem
	
	--fix NULL values in ITC1 
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC1" 
	WHERE  1=1 
		AND (
			"U_ItemCost" 	 IS NULL
			OR "U_FixOH" 		 IS NULL
			OR "U_FixOHPrct" 	 IS NULL
			OR "U_FixOHPrctCost" IS NULL
			OR "U_FixOHOther" 	 IS NULL
			OR "U_FixOHSubTotal" IS NULL
			OR "U_VarOH" 		 IS NULL
			OR "U_VarOHPrct" 	 IS NULL
			OR "U_VarOHPrctCost" IS NULL
			OR "U_VarOHOther" 	 IS NULL
			OR "U_VarOHSubTotal" IS NULL
			OR "U_Total" 		 IS NULL
			)
		--AND "Code" IN (select "Code" from :CodeForItem)
	;
	
	IF :cnt>0 
	THEN 
		UPDATE "@CT_PF_ITC1" SET "U_ItemCost" 		= 0.00	WHERE "U_ItemCost" 	IS NULL;		
		
		UPDATE "@CT_PF_ITC1" SET "U_FixOH" 			= 0.00	WHERE "U_FixOH" 	IS NULL; 	
		UPDATE "@CT_PF_ITC1" SET "U_FixOHPrct" 		= 0.00	WHERE "U_FixOHPrct" IS NULL; 	
		UPDATE "@CT_PF_ITC1" SET "U_FixOHPrctCost" 	= "U_ItemCost" * ("U_FixOHPrct"/100) 	WHERE "U_FixOHPrctCost" IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_FixOHOther"		= 0.00	WHERE "U_FixOHOther" IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_FixOHSubTotal"	= "U_FixOH" + "U_FixOHPrctCost" + "U_FixOHOther" 	WHERE "U_FixOHSubTotal" IS NULL; 
		
		UPDATE "@CT_PF_ITC1" SET "U_VarOH"			= 0.00	WHERE "U_VarOH"		IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_VarOHPrct"		= 0.00	WHERE "U_VarOHPrct"	IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_VarOHPrctCost"	= "U_ItemCost" * ("U_VarOHPrct"/100) 	WHERE "U_VarOHPrctCost" IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_VarOHOther"		= 0.00	WHERE "U_VarOHOther" IS NULL; 
		UPDATE "@CT_PF_ITC1" SET "U_VarOHSubTotal"	= "U_VarOH" + "U_VarOHPrctCost" + "U_VarOHOther" 	WHERE "U_VarOHSubTotal" IS NULL; 
		
		UPDATE "@CT_PF_ITC1" SET "U_Total" 		= "U_ItemCost" + "U_FixOHSubTotal" + "U_VarOHSubTotal" 	WHERE "U_Total" IS NULL;
	END IF;
	
	--Fix NULL values in ITC5
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC5" 
	WHERE 1=1  
		AND (
			COALESCE("U_ATLMaterial" , N'') = N'' 
			OR COALESCE("U_ATLResources", N'') = N'' 
			OR COALESCE("U_ATLFixOH"	, N'') = N'' 
			OR COALESCE("U_ATLVarOH"	, N'') = N'' 
			OR COALESCE("U_ATLTotal"	, N'') = N'' 
			OR COALESCE("U_LLMaterial"	, N'') = N'' 
			OR COALESCE("U_LLResources"	, N'') = N'' 
			OR COALESCE("U_LLFixOH"		, N'') = N'' 
			OR COALESCE("U_LLVarOH"		, N'') = N'' 
			OR COALESCE("U_LLTotal"		, N'') = N'' 
			OR COALESCE("U_CAMaterial"	, N'') = N'' 
			OR COALESCE("U_CAResources"	, N'') = N'' 
			OR COALESCE("U_CAFixOH"		, N'') = N'' 
			OR COALESCE("U_CAVarOH"		, N'') = N'' 
			OR COALESCE("U_CATotal"		, N'') = N'' 
			OR COALESCE("U_TCMaterial"	, N'') = N'' 
			OR COALESCE("U_TCResources"	, N'') = N'' 
			OR COALESCE("U_TCFixOH"		, N'') = N'' 
			OR COALESCE("U_TCVarOH"		, N'') = N'' 
			OR COALESCE("U_TCTotal"		, N'') = N'' 
			)
		--AND "Code" IN (select "Code" from @CodeForItem)
	;
	
	IF :cnt>0 
	THEN 
		UPDATE "@CT_PF_ITC5" SET "U_ATLMaterial"   = '0.00000' WHERE COALESCE("U_ATLMaterial" ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_ATLResources"  = '0.00000' WHERE COALESCE("U_ATLResources",'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_ATLFixOH"      = '0.00000' WHERE COALESCE("U_ATLFixOH"    ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_ATLVarOH"      = '0.00000' WHERE COALESCE("U_ATLVarOH"    ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_ATLTotal"      = 
				CAST("U_ATLMaterial"  AS DECIMAL(19,6))
				+ CAST("U_ATLResources" AS DECIMAL(19,6))
				+ CAST("U_ATLFixOH"     AS DECIMAL(19,6))
				+ CAST("U_ATLVarOH"     AS DECIMAL(19,6))
			WHERE COALESCE("U_ATLTotal",'') = ''; 
		
		UPDATE "@CT_PF_ITC5" SET "U_LLMaterial"    = '0.00000' WHERE COALESCE("U_LLMaterial"  ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_LLResources"   = '0.00000' WHERE COALESCE("U_LLResources" ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_LLFixOH"       = '0.00000' WHERE COALESCE("U_LLFixOH"     ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_LLVarOH"       = '0.00000' WHERE COALESCE("U_LLVarOH"     ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_LLTotal"       = 
				CAST("U_LLMaterial"   AS DECIMAL(19,6))
				+ CAST("U_LLResources"  AS DECIMAL(19,6))
				+ CAST("U_LLFixOH"      AS DECIMAL(19,6))
				+ CAST("U_LLVarOH"      AS DECIMAL(19,6))
			WHERE COALESCE("U_LLTotal",'') = ''; 
		
		UPDATE "@CT_PF_ITC5" SET "U_CAMaterial"    = '0.00000' WHERE COALESCE("U_CAMaterial"  ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_CAResources"   = '0.00000' WHERE COALESCE("U_CAResources" ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_CAFixOH"       = '0.00000' WHERE COALESCE("U_CAFixOH"     ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_CAVarOH"       = '0.00000' WHERE COALESCE("U_CAVarOH"     ,'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_CATotal"       = 
				CAST("U_CAMaterial"    AS DECIMAL(19,6))
				+ CAST("U_CAResources"   AS DECIMAL(19,6))
				+ CAST("U_CAFixOH"       AS DECIMAL(19,6))
				+ CAST("U_CAVarOH"       AS DECIMAL(19,6))
			WHERE COALESCE("U_CATotal",'') = ''; 
		
		UPDATE "@CT_PF_ITC5" SET "U_TCMaterial" 	= 
				CAST("U_ATLMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_CAMaterial" 	AS DECIMAL(19,6))
			WHERE COALESCE("U_TCMaterial",'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_TCResources" 	= 
				CAST("U_ATLResources" AS DECIMAL(19,6)) 
				+ CAST("U_LLResources" 	AS DECIMAL(19,6)) 
				+ CAST("U_CAResources" 	AS DECIMAL(19,6))
			WHERE COALESCE("U_TCResources",'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_TCFixOH" 		= 
				CAST("U_ATLFixOH" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLFixOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_CAFixOH" 		AS DECIMAL(19,6)) 
			WHERE COALESCE("U_TCFixOH",'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_TCVarOH" 		= 
				CAST("U_ATLVarOH" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLVarOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_CAVarOH" 		AS DECIMAL(19,6))		
			WHERE COALESCE("U_TCVarOH",'') = '' ;
		UPDATE "@CT_PF_ITC5" SET "U_TCTotal" 		= 
				CAST("U_TCMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_TCResources" 	AS DECIMAL(19,6)) 
				+ CAST("U_TCFixOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_TCVarOH" 		AS DECIMAL(19,6))
			WHERE COALESCE("U_TCTotal",'') = ''; 	
	END IF;
	
---------------------------------------------------------------------------------------------------------------------------------------------
---						:ItemCode != '' -> selected Item
---------------------------------------------------------------------------------------------------------------------------------------------
ELSE 
	
	CodeForItem = (
				select "Code" as "Code" 
				from "@CT_PF_OITC" 
				where "U_ItemCode" = :ItemCode
				);
	
	----------------------------------------------------------------------
	--Fix UpdateDate with time component in OITC for item
	SELECT COUNT('A') INTO cnt 
	from "@CT_PF_OITC" as itc
	where itc."UpdateDate" != cast(itc."UpdateDate" as date) 
		AND itc."Code" in (select "Code" from :CodeForItem);
		
	IF :cnt>0
	THEN 
		update 
			itc
		set
			itc."UpdateDate" = cast(itc."UpdateDate" as date)
		from
			"@CT_PF_OITC" as itc
		where
			itc."UpdateDate" != cast(itc."UpdateDate" as date)
			AND itc."Code" in (select "Code" from :CodeForItem);
	END IF;
	----
	
	----------------------------------------------------------------------
	--Fix UpdateDate with time component in AITC
	SELECT COUNT('A') INTO cnt
	from "@CT_PF_AITC" as itc
	where itc."UpdateDate" != cast(itc."UpdateDate" as date)
		AND itc."Code" in (select "Code" from :CodeForItem);

	IF :cnt>0 
	THEN
		update 
			itc
		set
			itc."UpdateDate" = cast(itc."UpdateDate" as date)
		from
			"@CT_PF_AITC" as itc
		where
			itc."UpdateDate" != cast(itc."UpdateDate" as date)
			AND itc."Code" in (select "Code" from :CodeForItem);
	END IF;
	----
	
	----------------------------------------------------------------------
	--Remove records from archive child tables without parent in header (deactivated for item)	
	FOR cur_row as CURSOR_TBL2
	DO
		sqlstr_exec := 
					'delete from 
						"@' || cur_row."LogNameL" || '"
					where
						("Code", "LogInst") in
						(
						select
							l."Code", l."LogInst"
						from
							"@' || cur_row."LogNameL" || '" as l
							left join
								"@' || cur_row."LogNameH" || '" as itc
								on	l."Code"	= itc."Code"
								and	l."LogInst"	= itc."LogInst"
						where
							itc."Code" is null
						)';			
		--select :sqlstr_exec from dummy;
		--exec (:sqlstr_exec);
				
	END FOR;
	----
	
	----------------------------------------------------------------------
	--remove unused warehouses (not defined in OITW) from Costing Details (ITC1) and Rolled Costs (ITC5 and ITC4 if exists).	
	IF :PriceSys = 'N'
	THEN
		--remove invalid warehouse from ITC1 for item
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC1" as tcl 
		WHERE "Code" in (select "Code" from :CodeForItem)
			and	"U_WhsCode" != 'ALL';
			
		IF :cnt>0
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC1" as tcl
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	"U_WhsCode" != 'ALL';
		END IF;
		
		--remove invalid warehouse from ITC4 for item
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC4" as tcl 
		WHERE	"Code" in (select "Code" from :CodeForItem) 
			AND "U_WhsCode" != 'ALL' ;
		
		IF :cnt>0 
		THEN
			DELETE 
			FROM
				"@CT_PF_ITC4" as tcl
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	"U_WhsCode" != 'ALL';
		END IF;
		
		--remove invalid warehouse from ITC5 for item
		SELECT COUNT('A') INTO cnt 
		FROM "@CT_PF_ITC5" as tcl 
		WHERE	"Code" in (select "Code" from :CodeForItem) 
			AND "U_WhsCode" != 'ALL';
			
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC5" as tcl
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	"U_WhsCode" != 'ALL';
		END IF;
		
		----------------------------------------------------------------------
	ELSE	---	@PriceSys != 'N'
		
		--remove invalid warehouse from ITC1 for item
		SELECT COUNT('A') INTO cnt 
		FROM
			"@CT_PF_ITC1"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC1" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						"OITW" as itw
						ON	itw."ItemCode" = itc."U_ItemCode"
						AND	tcl."U_WhsCode" = itw."WhsCode"
			WHERE
				itw."ItemCode" is NULL
			);
				
		IF :cnt>0 
		THEN 						
			DELETE 
			FROM
				"@CT_PF_ITC1"
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC1" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		
		--remove invalid warehouse from ITC4 for item
		SELECT COUNT('A') INTO cnt 
		FROM
			"@CT_PF_ITC4"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC4" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						"OITW" as itw
						ON	itw."ItemCode" = itc."U_ItemCode"
						AND	tcl."U_WhsCode" = itw."WhsCode"
			WHERE
				itw."ItemCode" is NULL
			);
			
		IF :cnt>0 
		THEN 		
			DELETE 
			FROM
				"@CT_PF_ITC4"
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC4" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		
		--remove invalid warehouse from ITC5 for item
		SELECT COUNT('A') INTO cnt 
		FROM
			"@CT_PF_ITC5"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC5" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						"OITW" as itw
						ON	itw."ItemCode" = itc."U_ItemCode"
						AND	tcl."U_WhsCode" = itw."WhsCode"
			WHERE
				itw."ItemCode" is NULL
			);
		
		IF :cnt>0 
		THEN 
			DELETE 
			FROM
				"@CT_PF_ITC5"
			WHERE
				"Code" in (select "Code" from :CodeForItem)
			and	("Code", "LineId") IN
				(
				SELECT
					tcl."Code", tcl."LineId"
				FROM
					"@CT_PF_ITC5" as tcl
					INNER JOIN
						"@CT_PF_OITC" as itc
						ON	tcl."Code" = itc."Code"
						LEFT JOIN
							"OITW" as itw
							ON	itw."ItemCode" = itc."U_ItemCode"
							AND	tcl."U_WhsCode" = itw."WhsCode"
				WHERE
					itw."ItemCode" is NULL
				);
		END IF;
		----
		
	END IF;
	
	----------------------------------------------------------------------
	---	remove duplicated warehouses, warehouses / Routings from Costing Details (ITC1, //ITC2, ITC3//, ITC5 and ITC4). Keep those with min LineID number.
	
	--remove dupplicated warehouses from ITC1
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC1"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC1" as tcl
			LEFT JOIN
				(
				SELECT
					"Code", MIN("LineId") as min_LineId
				FROM
					"@CT_PF_ITC1"
				GROUP BY
					"Code",
					"U_WhsCode"
				) as v
				ON	tcl."Code" = v."Code"
				AND	tcl."LineId" = min_LineId
		WHERE
			v."Code" is NULL
		);
	
	IF :cnt>0 
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC1"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC1" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC1"
					GROUP BY
						"Code",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC2	
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC2"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC2" as tcl
			LEFT JOIN
				(
				SELECT
					"Code", MIN("LineId") as min_LineId
				FROM
					"@CT_PF_ITC2"
				GROUP BY
					"Code",
					"U_WhsCode",
					"U_SequenceNo"
				) as v
				ON	tcl."Code" = v."Code"
				AND	tcl."LineId" = min_LineId
		WHERE
			v."Code" is NULL
		);
		
	IF :cnt>0 
	THEN 	
		DELETE
		FROM
			"@CT_PF_ITC2"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC2" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC2"
					GROUP BY
						"Code",
						"U_WhsCode",
						"U_SequenceNo"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC3
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC3"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC3" as tcl
			LEFT JOIN
				(
				SELECT
					"Code", MIN("LineId") as min_LineId
				FROM
					"@CT_PF_ITC3"
				GROUP BY
					"Code",
					"U_WhsCode",
					"U_SequenceNo"
				) as v
				ON	tcl."Code" = v."Code"
				AND	tcl."LineId" = min_LineId
		WHERE
			v."Code" is NULL
		);
		
	IF :cnt>0 
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC3"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC3" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC3"
					GROUP BY
						"Code",
						"U_WhsCode",
						"U_SequenceNo"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC4
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC4"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC4" as tcl
			LEFT JOIN
				(
				SELECT
					"Code", MIN("LineId") as min_LineId
				FROM
					"@CT_PF_ITC4"
				GROUP BY
					"Code",
					"U_RtgCode",
					"U_WhsCode"
				) as v
				ON	tcl."Code" = v."Code"
				AND	tcl."LineId" = min_LineId
		WHERE
			v."Code" is NULL
		);
	
	IF :cnt>0 
	THEN 
		DELETE
		FROM
			"@CT_PF_ITC4"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC4" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC4"
					GROUP BY
						"Code",
						"U_RtgCode",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	
	--remove dupplicated warehouses from ITC5 
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC5"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC5" as tcl
			LEFT JOIN
				(
				SELECT
					"Code", MIN("LineId") as min_LineId
				FROM
					"@CT_PF_ITC5"
				GROUP BY
					"Code",
					"U_RtgCode",
					"U_WhsCode"
				) as v
				ON	tcl."Code" = v."Code"
				AND	tcl."LineId" = min_LineId
		WHERE
			v."Code" is NULL
		);
		
	IF :cnt>0 
	THEN 	
		DELETE
		FROM
			"@CT_PF_ITC5"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC5" as tcl
				LEFT JOIN
					(
					SELECT
						"Code", MIN("LineId") as min_LineId
					FROM
						"@CT_PF_ITC5"
					GROUP BY
						"Code",
						"U_RtgCode",
						"U_WhsCode"
					) as v
					ON	tcl."Code" = v."Code"
					AND	tcl."LineId" = min_LineId
			WHERE
				v."Code" is NULL
			);
	END IF;
	----
	
	----------------------------------------------------------------------
	---	Remove entries from ITC4 / ITC5 with Routings that have not been assigned to Revision
	
	--remove invalid routings from ITC4
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC4"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC4" as tcl
			INNER JOIN
				"@CT_PF_OITC" as itc
				ON	tcl."Code" = itc."Code"
				LEFT JOIN
					(
					select
						idt."U_ItemCode",
						dt1."U_Code",
						ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
					from
						"@CT_PF_OIDT" as idt
						INNER JOIN
							"@CT_PF_IDT1" as dt1
							ON	idt."Code" = dt1."Code"
							LEFT JOIN
								"@CT_PF_OBOM" as bom
								ON	bom."U_ItemCode" = idt."U_ItemCode"
								AND	bom."U_Revision" = dt1."U_Code"
								LEFT JOIN
									"@CT_PF_BOM11" as bom11
									ON	bom11."Code" = bom."Code"
					) as v
					ON	v."U_ItemCode" = itc."U_ItemCode"
					AND	v."U_Code" = itc."U_RevCode"
					AND	v."U_RtgCode" = tcl."U_RtgCode"
		WHERE
			v."U_ItemCode" is NULL
		);
	
	IF :cnt>0 
	THEN 
		DELETE 
		FROM
			"@CT_PF_ITC4"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC4" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						(
						select
							idt."U_ItemCode",
							dt1."U_Code",
							ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
						from
							"@CT_PF_OIDT" as idt
							INNER JOIN
								"@CT_PF_IDT1" as dt1
								ON	idt."Code" = dt1."Code"
								LEFT JOIN
									"@CT_PF_OBOM" as bom
									ON	bom."U_ItemCode" = idt."U_ItemCode"
									AND	bom."U_Revision" = dt1."U_Code"
									LEFT JOIN
										"@CT_PF_BOM11" as bom11
										ON	bom11."Code" = bom."Code"
						) as v
						ON	v."U_ItemCode" = itc."U_ItemCode"
						AND	v."U_Code" = itc."U_RevCode"
						AND	v."U_RtgCode" = tcl."U_RtgCode"
			WHERE
				v."U_ItemCode" is NULL
			);
	END IF;
	
	--remove invalid routings from ITC5
	SELECT COUNT('A') INTO cnt 
	FROM
		"@CT_PF_ITC5"
	WHERE
		"Code" in (select "Code" from :CodeForItem)
	and	("Code", "LineId") IN
		(
		SELECT
			tcl."Code", tcl."LineId"
		FROM
			"@CT_PF_ITC5" as tcl
			INNER JOIN
				"@CT_PF_OITC" as itc
				ON	tcl."Code" = itc."Code"
				LEFT JOIN
					(
					select
						idt."U_ItemCode",
						dt1."U_Code",
						ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
					from
						"@CT_PF_OIDT" as idt
						INNER JOIN
							"@CT_PF_IDT1" as dt1
							ON	idt."Code" = dt1."Code"
							LEFT JOIN
								"@CT_PF_OBOM" as bom
								ON	bom."U_ItemCode" = idt."U_ItemCode"
								AND	bom."U_Revision" = dt1."U_Code"
								LEFT JOIN
									"@CT_PF_BOM11" as bom11
									ON	bom11."Code" = bom."Code"
					) as v
					ON	v."U_ItemCode" = itc."U_ItemCode"
					AND	v."U_Code" = itc."U_RevCode"
					AND	v."U_RtgCode" = tcl."U_RtgCode"
		WHERE
			v."U_ItemCode" is NULL
		);
		
	IF :cnt>0 
	THEN 	
		DELETE 
		FROM
			"@CT_PF_ITC5"
		WHERE
			"Code" in (select "Code" from :CodeForItem)
		and	("Code", "LineId") IN
			(
			SELECT
				tcl."Code", tcl."LineId"
			FROM
				"@CT_PF_ITC5" as tcl
				INNER JOIN
					"@CT_PF_OITC" as itc
					ON	tcl."Code" = itc."Code"
					LEFT JOIN
						(
						select
							idt."U_ItemCode",
							dt1."U_Code",
							ifnull(bom11."U_RtgCode", '') as "U_RtgCode"
						from
							"@CT_PF_OIDT" as idt
							INNER JOIN
								"@CT_PF_IDT1" as dt1
								ON	idt."Code" = dt1."Code"
								LEFT JOIN
									"@CT_PF_OBOM" as bom
									ON	bom."U_ItemCode" = idt."U_ItemCode"
									AND	bom."U_Revision" = dt1."U_Code"
									LEFT JOIN
										"@CT_PF_BOM11" as bom11
										ON	bom11."Code" = bom."Code"
						) as v
						ON	v."U_ItemCode" = itc."U_ItemCode"
						AND	v."U_Code" = itc."U_RevCode"
						AND	v."U_RtgCode" = tcl."U_RtgCode"
			WHERE
				v."U_ItemCode" is NULL
			);
	END IF;
	
	-----------------------------------------------------------------------
	--Fix NULL values in costing tables for item

	-- Fix NULL values from ITC1 for item		
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC1" 
	WHERE  1=1 
		AND (
			"U_ItemCost" 	 IS NULL
			OR "U_FixOH" 		 IS NULL
			OR "U_FixOHPrct" 	 IS NULL
			OR "U_FixOHPrctCost" IS NULL
			OR "U_FixOHOther" 	 IS NULL
			OR "U_FixOHSubTotal" IS NULL
			OR "U_VarOH" 		 IS NULL
			OR "U_VarOHPrct" 	 IS NULL
			OR "U_VarOHPrctCost" IS NULL
			OR "U_VarOHOther" 	 IS NULL
			OR "U_VarOHSubTotal" IS NULL
			OR "U_Total" 		 IS NULL
			)
		AND "Code" IN (select "Code" from :CodeForItem)
	;
	
	IF :cnt>0 
	THEN 
		UPDATE "@CT_PF_ITC1" SET "U_ItemCost" 		= 0.00	WHERE "U_ItemCost" 	IS NULL AND "Code" IN (select "Code" from :CodeForItem);
	
		UPDATE "@CT_PF_ITC1" SET "U_FixOH" 			= 0.00	WHERE "U_FixOH" 	IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_FixOHPrct" 		= 0.00	WHERE "U_FixOHPrct" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_FixOHPrctCost" 	= "U_ItemCost" * ("U_FixOHPrct"/100) 	WHERE "U_FixOHPrctCost" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_FixOHOther"		= 0.00	WHERE "U_FixOHOther" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_FixOHSubTotal"	= "U_FixOH" + "U_FixOHPrctCost" + "U_FixOHOther" 	WHERE "U_FixOHSubTotal" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		
		UPDATE "@CT_PF_ITC1" SET "U_VarOH"			= 0.00	WHERE "U_VarOH"		IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_VarOHPrct"		= 0.00	WHERE "U_VarOHPrct"	IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_VarOHPrctCost"	= "U_ItemCost" * ("U_VarOHPrct"/100) 	WHERE "U_VarOHPrctCost" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_VarOHOther"		= 0.00	WHERE "U_VarOHOther" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC1" SET "U_VarOHSubTotal"	= "U_VarOH" + "U_VarOHPrctCost" + "U_VarOHOther" 	WHERE "U_VarOHSubTotal" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
		
		UPDATE "@CT_PF_ITC1" SET "U_Total" 		= "U_ItemCost" + "U_FixOHSubTotal" + "U_VarOHSubTotal" 	WHERE "U_Total" IS NULL AND "Code" IN (select "Code" from :CodeForItem);
	END IF;
	----
	
	--Fix NULL values in ITC5 for item
	SELECT COUNT('A') INTO cnt 
	FROM "@CT_PF_ITC5" 
	WHERE 1=1  
		AND (
			COALESCE("U_ATLMaterial" , '') = '' 
			OR COALESCE("U_ATLResources", '') = '' 
			OR COALESCE("U_ATLFixOH"	, '') = '' 
			OR COALESCE("U_ATLVarOH"	, '') = '' 
			OR COALESCE("U_ATLTotal"	, '') = '' 
			OR COALESCE("U_LLMaterial"	, '') = '' 
			OR COALESCE("U_LLResources"	, '') = '' 
			OR COALESCE("U_LLFixOH"		, '') = '' 
			OR COALESCE("U_LLVarOH"		, '') = '' 
			OR COALESCE("U_LLTotal"		, '') = '' 
			OR COALESCE("U_CAMaterial"	, '') = '' 
			OR COALESCE("U_CAResources"	, '') = '' 
			OR COALESCE("U_CAFixOH"		, '') = '' 
			OR COALESCE("U_CAVarOH"		, '') = '' 
			OR COALESCE("U_CATotal"		, '') = '' 
			OR COALESCE("U_TCMaterial"	, '') = '' 
			OR COALESCE("U_TCResources"	, '') = '' 
			OR COALESCE("U_TCFixOH"		, '') = '' 
			OR COALESCE("U_TCVarOH"		, '') = '' 
			OR COALESCE("U_TCTotal"		, '') = '' 
			)
		AND "Code" IN (select "Code" from :CodeForItem)
	;
	
	IF :cnt>0 
	THEN 
		UPDATE "@CT_PF_ITC5" SET "U_ATLMaterial"   = '0.00000' WHERE COALESCE("U_ATLMaterial" ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_ATLResources"  = '0.00000' WHERE COALESCE("U_ATLResources",'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_ATLFixOH"      = '0.00000' WHERE COALESCE("U_ATLFixOH"    ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_ATLVarOH"      = '0.00000' WHERE COALESCE("U_ATLVarOH"    ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_ATLTotal"      = 
				CAST("U_ATLMaterial"  AS DECIMAL(19,6))
				+ CAST("U_ATLResources" AS DECIMAL(19,6))
				+ CAST("U_ATLFixOH"     AS DECIMAL(19,6))
				+ CAST("U_ATLVarOH"     AS DECIMAL(19,6))
			WHERE COALESCE("U_ATLTotal",'') = '' AND "Code" IN (select "Code" from :CodeForItem); 
		
		UPDATE "@CT_PF_ITC5" SET "U_LLMaterial"    = '0.00000' WHERE COALESCE("U_LLMaterial"  ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_LLResources"   = '0.00000' WHERE COALESCE("U_LLResources" ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_LLFixOH"       = '0.00000' WHERE COALESCE("U_LLFixOH"     ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_LLVarOH"       = '0.00000' WHERE COALESCE("U_LLVarOH"     ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_LLTotal"       = 
				CAST("U_LLMaterial"   AS DECIMAL(19,6))
				+ CAST("U_LLResources"  AS DECIMAL(19,6))
				+ CAST("U_LLFixOH"      AS DECIMAL(19,6))
				+ CAST("U_LLVarOH"      AS DECIMAL(19,6))
			WHERE COALESCE("U_LLTotal",'') = '' AND "Code" IN (select "Code" from :CodeForItem); 
		
		UPDATE "@CT_PF_ITC5" SET "U_CAMaterial"    = '0.00000' WHERE COALESCE("U_CAMaterial"  ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_CAResources"   = '0.00000' WHERE COALESCE("U_CAResources" ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_CAFixOH"       = '0.00000' WHERE COALESCE("U_CAFixOH"     ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_CAVarOH"       = '0.00000' WHERE COALESCE("U_CAVarOH"     ,'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_CATotal"       = 
				CAST("U_CAMaterial"    AS DECIMAL(19,6))
				+ CAST("U_CAResources"   AS DECIMAL(19,6))
				+ CAST("U_CAFixOH"       AS DECIMAL(19,6))
				+ CAST("U_CAVarOH"       AS DECIMAL(19,6))
			WHERE COALESCE("U_CATotal",'') = '' AND "Code" IN (select "Code" from :CodeForItem); 
		
		UPDATE "@CT_PF_ITC5" SET "U_TCMaterial" 	= 
				CAST("U_ATLMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_CAMaterial" 	AS DECIMAL(19,6))
			WHERE COALESCE("U_TCMaterial",'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_TCResources" 	= 
				CAST("U_ATLResources" AS DECIMAL(19,6)) 
				+ CAST("U_LLResources" 	AS DECIMAL(19,6)) 
				+ CAST("U_CAResources" 	AS DECIMAL(19,6))
			WHERE COALESCE("U_TCResources",'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_TCFixOH" 		= 
				CAST("U_ATLFixOH" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLFixOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_CAFixOH" 		AS DECIMAL(19,6)) 
			WHERE COALESCE("U_TCFixOH",'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_TCVarOH" 		= 
				CAST("U_ATLVarOH" 	AS DECIMAL(19,6)) 
				+ CAST("U_LLVarOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_CAVarOH" 		AS DECIMAL(19,6))		
			WHERE COALESCE("U_TCVarOH",'') = '' AND "Code" IN (select "Code" from :CodeForItem);
		UPDATE "@CT_PF_ITC5" SET "U_TCTotal" 		= 
				CAST("U_TCMaterial" 	AS DECIMAL(19,6)) 
				+ CAST("U_TCResources" 	AS DECIMAL(19,6)) 
				+ CAST("U_TCFixOH" 		AS DECIMAL(19,6)) 
				+ CAST("U_TCVarOH" 		AS DECIMAL(19,6))
			WHERE COALESCE("U_TCTotal",'') = '' AND "Code" IN (select "Code" from :CodeForItem); 
	END IF;
	----
	
	
	----------------------------------------------------------------------	
	
END IF;
	
	
	----------------------------------------------------------------------	
END; 

--------------===============================================================
--	CALL "CT_PF_Costing_CleanUp"('');