-- B1 DEPENDS: AFTER:PT:PROCESS_END AFTER:SP:_TmSp_BootCreateGlobalTempTables2
--Due to the structure of HANA store procedure? IVI_SupportInteractionRequiredQuery_Sub1 is hard to migrate into TmSp_IVSR_SupportInteractionRequiredQuery, 
--so the error codes 515 and 260 are hard to add into IVSR.

CREATE PROCEDURE TmSp_IVSR_SupportInteractionRequiredQuery
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS
openQty	 DECIMAL(21,6);
qtySum DECIMAL(21,6);
transNum int;
instance int;
lastTransNum int;
lastInstance int;
qty_decimals int;
sum_decimals int;
Main_Currency nvarchar(3);
DB_Impacted nvarchar(1);
Recalc_By_OILM nvarchar(1);
roundmthd nvarchar(1);
OCRNdeciamls int;
directrate nvarchar(1); 
Update_before nvarchar(1);
DBImpacted nvarchar(1);
stockissue nvarchar(1);
firstTranstionDate datetime;
cha TIMESTAMP;
rowcount int;
rownum int;
rowcount1 int;
count1 integer;
count2 integer;						
stockissue88 char(1);					   
rownum1 int;
isExist INTEGER;
ver91PL02 integer;
ver90PL06 integer;

sqlStr VARCHAR(40000);
cmpVersionNum integer;
max07Msg integer;
temp_var_0 integer;
Min_526_Reorder TIMESTAMP;
Max_526_Reorder TIMESTAMP;
hasActualCostingItems char;



BEGIN

	DELETE from "TMP_IVSR_SupInterReq_Results";
	rowcount := 0;

	firstTranstionDate := '1950-01-01'; 
	select "QtyDec" into qty_decimals from "OADM";
	select "SumDec" into sum_decimals from "OADM";
	select "MainCurncy" into main_currency from "OADM";
	select "RoundMthd" into roundmthd from "OADM";
	select "Decimals" into OCRNdeciamls from ocrn where "CurrCode" = :main_currency;
	select "DirectRate" into directrate from "OADM";
	SELECT "Version" INTO cmpVersionNum FROM "CINF";

	select COUNT(1) INTO rowcount from livi where "UpdateDate" is not null;
	if :rowcount = 1 then 
		Update_before := 'Y';
	else 
		Update_before := 'N';
	end if;
	if :roundmthd = 'Y' and :OCRNdeciamls <> -1 then
		sum_decimals := :OCRNdeciamls;
	end if;

	SELECT COUNT(1) INTO rowCount FROM "CIVI" WHERE "ReorderFrm" IS NULL;
	IF :rowCount = 1 THEN 
		stockissue88 := 'N';
	ELSE 
		stockissue88 := 'Y';
	END IF;																	  
	-- ==================================================================================================
	-- ==================================================================================================
	-- ==================================================================================================
	--							SOURCE: IVI_SupportInteractionRequiredQuery.sql
	-- ==================================================================================================

------------------------------------------------------------------
/* Recalculation BY OILM & recalculation BY UILM _ Shared part*/
------------------------------------------------------------------
/**
 * Supp_310_Item is defined as Assembly/Sales bom AND as an inventory item
 *
 * 
*/	
	select COUNT(1) INTO rowcount
	from  "OITM" t0
	where "TreeType" in ( 'A', 'S' ) and "InvntItem" = 'Y';
	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 310');
	
	end if;

/**
 * Supp_401_Evaluation system IN MRV is incorrect
 *
 * Scripts detects MRV1.Evalsystem which holds the value 'N'
	*/
	select COUNT(1) INTO rowcount from "MRV1" where "EvalSystem" = 'N';
	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 401.');
	
	end if;

 /**
 * Supp_501_Rate is zero when Price Currency is FC_for_-2_58_OILM_UILM
 * --- Rate is zero when Price Currency is FC, FROM 400 query
 * Since TransType 58,-2 will be copied FROM OILM to UILM directly during reorder step,
 * it is necessary to have the same check for these 2 TransTypes IN "BY uilm" as "BY OILM" IN support query.
 * 
*/
	SELECT COUNT(t0."MessageID") into rowcount 
	FROM   "OILM" t0 
	INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
	                     AND t0."ItemCode" = t1."ItemCode" 
						 AND t1."InvntItem" = 'Y' 								   
		AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
		AND t0."Price" <> 0 
		AND IFNULL(t0."PriceCurr",'') <> :Main_Currency 
		AND IFNULL(t0."PriceRate",0) = 0 
		AND (t0."TransType" IN (-2,58) 
			AND t0."ActionType" <> 2);

	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 501.');
	
	end if;

/**
 * Supp_502_TotalLC is zero AND TotalSC/TotalFC NOT zero_for_-2_58_OILM_UILM
 * --- TotalLC is zero AND TotalSC/TotalFC NOT zero, FROM 400 query
 * Since TransType 58,-2 will be copied FROM OILM to UILM directly during reorder step,
 * it is necessary to have the same check for these 2 TransTypes IN "BY uilm" as "BY OILM" IN support query.
 * 
*/  
	SELECT COUNT(t0."MessageID") INTO rowcount
	FROM   "OILM" t0 
	INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
	                     AND t0."ItemCode" = t1."ItemCode" 
						 AND t1."InvntItem" = 'Y' 										
		AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
		AND IFNULL(t0."TotalLC",0) = 0 -- TotalLC is zero and TotalSC/TotalFC not zero 
		AND (IFNULL(t0."TotalSC",0) <> 0 
			OR IFNULL(t0."TotalFC",0) <> 0) 
		AND (t0."TransType" IN (-2,58) 
			AND t0."ActionType" <> 2);

	IF :rowcount > 0 then 
		DB_Impacted := 'Y'; 
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 502.');
	
	end if; 
/**
 * Supp_503_TotalLC_is_zero_AND_Price/EffectQty_not_zero_for_-2_58_OILM_UILM
 * --- TotalLC_is_zero_AND_Price/EffectQty_not_zero, FROM 400 query
 * Since TransType 58,-2 will be copied FROM OILM to UILM directly during reorder step,
 * it is necessary to have the same check for these 2 TransTypes IN "BY uilm" as "BY OILM" IN support query.
 * 
*/										
	SELECT COUNT(t0."MessageID") INTO rowcount 
	FROM   "OILM" t0 
	INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
	                  AND t0."ItemCode" = t1."ItemCode" 
					  AND t1."InvntItem" = 'Y'
					  AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
					  AND IFNULL(t0."Price",0) <> 0 --- TotalLC is zero and Price/EffectQty not zero in OILM 
					AND IFNULL(t0."TotalLC",0) = 0 
					AND (t0."TransType" IN (-2,58) 
					AND t0."ActionType" <> 2) 
					AND Round(t0."Price" * t0."Quantity",:sum_decimals) <> 0;

	IF  :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 503.');		     
	END IF;


/**
 * Supp_1_18_Missing Header - Documents lines with no header
 * except WKO WOR MRV part
 * 
*/	  
	--WKO
	SELECT count(T0."OrderNum") into rowcount
	FROM   "WKO1" T0
	INNER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode"
		and T1."InvntItem" = 'Y'
	WHERE  T0."OrderNum" not in ( Select "OrderNum" from   "OWKO" );

	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 15.');		     
	end if;
	--WOR
	SELECT count(T0."DocEntry") into rowcount
	FROM   "WOR1" T0
	INNER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode" and T1."InvntItem" = 'Y'
	WHERE  T0."DocEntry" not in ( Select "DocEntry" from   "OWOR" );

	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 17.');
	end if;

	--MRV
	SELECT count(T0."DocEntry") into rowcount
	FROM   "MRV1" T0
	INNER JOIN "OITM" T1 ON T0."ItemCode" = T1."ItemCode"
		and T1."InvntItem" = 'Y'
	WHERE  T0."DocEntry" not in ( Select "DocEntry" from "OMRV" );

	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 18.');
	end if;


/**
 * Supp_19_37_Missing rows IN documents - Document header with no rows
 *
 * Documents with freights only will be ignored since it is a valid document
 * WKO WOR MRV part
*/	 
	--WKO
	SELECT count(T0."OrderNum") into rowcount
	FROM   OWKO T0
	WHERE  T0."OrderNum" not in ( Select "OrderNum" from WKO1 );

	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 34.');
	end if;

	--WOR
	select COUNT(t0."DocEntry") INTO rowcount
	from   "OWOR" t0 
	where  t0."DocEntry" not in (select "DocEntry" from   "WOR1") 
		and (exists (select 1 from   "IGN1" t1 where  t1."BaseEntry" = t0."DocEntry") 
			or exists (select 1 from   "IGE1" t2 where  t2."BaseEntry" = t0."DocEntry"));
		
	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 35.');
	end if; 
	--MRV

	SELECT count(T0."DocEntry") into rowcount
	FROM   "OMRV" T0
	WHERE  T0."DocEntry" not in ( Select "DocEntry" from   "MRV1" );

	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 37.');
	end if;
/**
*	Supp_180 ~ 183
*	
*	First Scenario: 
*
*	The root cause is incorrect upgrade IN CSN 3408870 2006 (2005B) OR similar fix IN 2007AC
*
*	This fix meant to switch OWOR primary key FROM   OWOR.Docnum to OWOR.DocEntry.
*	Part of the upgrade was switching IGN1/IGE1.BaseEntry  & OINM.Appobjabs FROM reference to OWOR.Docnum INTO reference to OWOR.DocEntry.
*	As a result of a bug, the IGN/IGE.BaseEntry was incorrectly linked to a different OWOR.
*
*	e.g.:
*	The IGN DocEntry 1245 which is a receipt for production order DocEntry 621 was incorrectly linked to a different production order.
*	IGN1.BaseEntry is 46 AND the baseref is 621, which is wrong (46 is DocEntry of  production order docnum 621).
*
*	Second Scenario (Identified only once IN an extremely corrupted db):
*
*	Base entry for all lines is incorrect
*	IGN1 is based on production orders 1-25
*	When drawing these 25 production orders INTO ign, production orders were grouped INTO 1 line BY ItemCode.
*	qty was grouped as well.
*
*	e.g.: production order 1-5 were grouped IN IGN1 INTO line 11.
*	The related appobjabs IN inm/ilm is correct
*	The production orders IN inm/ilm were NOT grouped AND have consecutive DocLineNum which is inconsistent with
*	IGN1.LineNum.
*/
/**
 * Supp_180_Base entry IN Receipt FROM production is incorrect
 *
 * 
*/   
	select COUNT(t0."DocEntry") INTO rowcount from  "IGN1" t0
	inner join "OWOR" t1 on t0."BaseType" = 202 
	                     and t0."BaseEntry" = t1."DocEntry" 
						 and t0."BaseLine" is null 
						 and t0."ItemCode" <> t1."ItemCode";										  

	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 180.');
	
	end if;
/**
 * Supp_181_Base entry IN Issue for production is incorrect
 *
 * 
*/

	select COUNT(t0."DocEntry") INTO rowcount from "IGE1" t0
	inner join "OWOR" t1 on t0."BaseType" = 202 
	                     and t0."BaseEntry" = t1."DocEntry" 
						 and t0."BaseLine" is null 
						 and t0."ItemCode" <> t1."ItemCode";

	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 181.');
	
	end if;
 /**
 * Supp_182_Base entry IN Receipt FROM production is incorrect - returned components
 *
 * 
*/  
	select count(t0."DocEntry") into rowcount from "IGN1" t0
	        inner join "WOR1" t1 on t0."BaseType" = 202 
			             and t0."BaseEntry" = t1."DocEntry" 
						 and t0."BaseLine" = t1."LineNum" 
						 and t0."ItemCode" <> t1."ItemCode" 
            inner join (select "BaseAbsEnt", ifnull(max("VersionNum"),'') as  "OILM_Max_VersionNum"
						 from oilm
						 where "BaseType" = 202 
						 group by "BaseAbsEnt"
                    ) t2
                    on t1."DocEntry" = t2."BaseAbsEnt"                           
                    inner join oitm t3
                    on t3."ItemCode" = t0."ItemCode" and t3."InvntItem" = 'Y'
                    and (
                    		(t2."OILM_Max_VersionNum" >= CASE
															WHEN :cmpVersionNum >= 1000000
															THEN '09.10.000.00'
															ELSE '9.10.000.00' 
															END

                                   and t0."ItemCode" not in 
                                   (select u0."ItemCode"
                                         from oivl u0
                                         inner join oilm u1
                                         on u0."MessageID" = u1."MessageID"
                                               where u1."BaseType" = 202
                                                     and u1."AccumType" = 1
                                                     and u1."AppObjType" = 'C'
                                                     and u1."BaseAbsEnt" = t1."DocEntry"
                                                     and u1."AppObjLine" = t1."LineNum"
                                               group by u0."ItemCode", u1."BaseAbsEnt", u1."AppObjLine"
                                               having sum(u0."InQty"-u0."OutQty") = 0 )
                         		or
                     		(t2."OILM_Max_VersionNum" < CASE
															WHEN :cmpVersionNum >= 1000000
															THEN '09.10.000.00'
															ELSE '9.10.000.00' 
															END)));

	if :rowcount > 0 THEN 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 182.');
	end IF;

/**
 * Supp_183_Base entry IN Issue for production is incorrect - returned components
 *
 * 
*/
	select COUNT(t0."DocEntry") INTO rowcount from "IGE1" t0
	        inner join "WOR1" t1 on t0."BaseType" = 202 
			             and t0."BaseEntry" = t1."DocEntry" 
						 and t0."BaseLine" = t1."LineNum" 
						 and t0."ItemCode" <> t1."ItemCode"
					inner join
                    (select "BaseAbsEnt", ifnull(max("VersionNum"),'') as  "OILM_Max_VersionNum"
						 from oilm
						 where "BaseType" = 202 
						 group by "BaseAbsEnt"
                    ) t2
                    on t1."DocEntry" = t2."BaseAbsEnt"                           
                    inner join oitm t3
                    on t3."ItemCode" = t0."ItemCode" and t3."InvntItem" = 'Y'
                    and (
                    		(t2."OILM_Max_VersionNum" >= CASE
															WHEN :cmpVersionNum >= 1000000
															THEN '09.10.000.00'
															ELSE '9.10.000.00' 
															END
                                   and t0."ItemCode" not in 
                                   (select u0."ItemCode"
                                         from oivl u0
                                         inner join oilm u1
                                         on u0."MessageID" = u1."MessageID"
                                               where u1."BaseType" = 202
                                                     and u1."AccumType" = 1
                                                     and u1."AppObjType" = 'C'
                                                     and u1."BaseAbsEnt" = t1."DocEntry"
                                                     and u1."AppObjLine" = t1."LineNum"
                                               group by u0."ItemCode", u1."BaseAbsEnt", u1."AppObjLine"
                                               having sum(u0."InQty"-u0."OutQty") = 0 )
                         		or
                     		(t2."OILM_Max_VersionNum" < CASE
															WHEN :cmpVersionNum >= 1000000
															THEN '09.10.000.00'
															ELSE '9.10.000.00' 
															END)));
															
	if :rowcount > 0 then 
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 183.');
	
	end if;
/**
 * Supp_230_250_CreateDate is IN the future
 * MRV WOR WKO part
 * 
*/
	---OMRV 	
	Select count(t0."CreateDate") INTO rowcount 
	from   "OMRV" t0
	where  t0."CreateDate" > CURRENT_TIMESTAMP;

	if :rowcount > 0 THEN
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 248.');
	end IF;

	---OWOR	
	Select count(t0."CreateDate") into rowcount from   "OWOR" t0
	
	where  t0."CreateDate" > CURRENT_TIMESTAMP;

	if :rowcount > 0 THEN
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 249.');
	end IF;

	---OWKO	
	Select count(t0."CreateDate") into rowcount from "OWKO" t0
	
	where t0."CreateDate" > CURRENT_TIMESTAMP or t0."FinishDate" > CURRENT_TIMESTAMP;

	if :rowcount > 0 THEN
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 250.');
	end IF;

--removed 260

/**
 * Supp_270_Null IN FinishDate IN old work order
 *
 * 
*/
	SELECT COUNT(T0."OrderNum") INTO rowcount
	FROM  "OWKO" T0
	LEFT OUTER JOIN "OINM" T1 ON T0."OrderNum" = T1."CreatedBy"
		AND T1."TransType" = 68
	LEFT OUTER JOIN "OJDT" T2 ON T0."OrderNum" = T2."CreatedBy"
		AND T2."TransType" = N'68'
	WHERE  T1."TransSeq" IS NULL 
	       AND T2."TransId" IS NULL 
		   and T0."Status" = 'E' 
		   and IFNULL(t0."FinishDate", '') = '';									  

	if :rowcount > 0 THEN
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 270.');
	end IF;

/**
 * Supp_504_505_WOR1.comptotal missing check
 * This is added for incident 21405
 * Solution : Fill the comptotal value BY the support, customer interaction may be needed.
*/
	SELECT COUNT(t1."ItemCode") INTO rowcount 
	FROM   "WOR1" t1 
		INNER JOIN "OITM" t2 ON t1."ItemCode" = t2."ItemCode" 
		INNER JOIN "OITW" t3 ON t1."ItemCode" = t3."ItemCode" AND t1."wareHouse" = t3."WhsCode" 
	WHERE t2."InvntItem" = 'N' 
	      AND t2."TreeType" = 'A'
		  AND t2."Phantom" = 'N'
	      AND IFNULL(t1."CompTotal",0) = 0 
		  AND Round((CASE 
		             WHEN (SELECT "PriceSys" FROM "OADM") = 'N' 
						THEN t2."AvgPrice" 
					 ELSE t3."AvgPrice" 
					 END)
					 * t1."IssuedQty", :sum_decimals) <> 0;
																			
	IF :rowcount > 0 then 
		DB_Impacted := 'Y'; 
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 504.');
	END if; 

	SELECT COUNT(t1."ItemCode") INTO rowcount
	FROM   "UWOR1" t1 
		INNER JOIN "OITM" t2 ON t1."ItemCode" = t2."ItemCode" 
		INNER JOIN "OITW" t3 ON t1."ItemCode" = t3."ItemCode" AND t1."wareHouse" = t3."WhsCode" 
	WHERE t2."InvntItem" = 'N' 
	      AND t2."TreeType" = 'A'
		  AND t2."Phantom" = 'N'
	      AND IFNULL(t1."CompTotal",0) = 0 
		  AND Round((CASE 
		             WHEN (SELECT "PriceSys" FROM "OADM") = 'N' 
						THEN t2."AvgPrice" 
					 ELSE t3."AvgPrice" 
					 END)
					 * t1."IssuedQty", :sum_decimals) <> 0;

	IF :rowcount > 0 then 
		DB_Impacted := 'Y'; 
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 505.');
	END if;
------------------------------------------------
/**
 * Supp_506_Inconsistency between OILM.BaseAbsEnt AND OILM.AppObjAbs of receipt/issue for prodution order
 * Solution: Update OILM set AppObjAbs = BaseAbsEnt where OILM.BaseAbsEnt <> OILM.AppObjAbs AND OILM.BaseType=202
 * As a result of issue reported IN Task 15508
*/

    select count("MessageID") into rowcount FROM  oilm WHERE  oilm."BaseAbsEnt" <> oilm."AppObjAbs" AND oilm."BaseType" = 202;
	
	if :rowcount > 0 then	
		
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 506.');
	
	end if;
	
/**
 * Supp_507_Missing production order's message(Production order was created before 8.8)
 * Solution: before fixing IN code, please remove all OILM messages for affected production order
 * As a result of issue reported IN Task 17177,17176(Code Fixing)
*/  
	select count(T0."DocEntry") into rowcount from WOR1 t0 
	    join oitm t1 on t0."ItemCode" = t1."ItemCode"
	    left join oilm t2 on t0."DocEntry" = t2."DocEntry" and t2."TransType" = 202 and t0."LineNum" = t2."DocLineNum"
	where t1."InvntItem" = 'Y' 
	      and t0."DocEntry" in (select "DocEntry" from OILM where "TransType"=202) 
		  and t2."MessageID" is null;
		
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 507.]]]');
	END if;
	

/**
 * Supp_508_Quantity is different FROM effectqty IN OILM for production order
 * Solution: set Quantity as effectqty IN OILM for production order
 * As a result of issue reported IN Task 17177
*/	
	select count("MessageID") into rowcount
	FROM OILM t0 
	WHERE t0."TransType" = 202 
	      and t0."Quantity" <> t0."EffectQty"
		  and not exists (SELECT 1 FROM OILM t1 
		                  WHERE t1."MessageID" < t0."MessageID"
								and T1."TransType" in (59,60) 
								and T1."AppObjAbs" = t0."DocEntry" 
								and T1."AppObjLine" = t0."DocLineNum") 
		and not (T0."DocLineNum" = -1 and T0."EffectQty" = 0) 
		and t0."AccumType" in (2,3);

	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 508.]]]');
	END if;
/**
 * Supp_509_ Duplicate records IN DLN14 for non assembly BoM 
 * Solution: Please refer note 1598583 
 * As a result of issue reported IN Task 18897
*/  
	SELECT SUM("rowCount") INTO rowCount 
	FROM (SELECT COUNT(1) AS "rowCount" FROM DLN1 T1
			JOIN OILM T0 on T0."TransType" = 15 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN DLN14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType" <> 'A' OR T0."TreeType"<> 'A') AND (T0."VersionNum"< CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum" IS NULL)
		UNION SELECT COUNT(1) AS "rowCount" FROM INV1 T1
			JOIN OILM T0 on  T0."TransType" = 13 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN INV14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType"<> 'A' OR T0."TreeType"<> 'A') AND (T0."VersionNum"=  CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum"=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.239.21'
																												ELSE '8.80.239.21' 
																												END)
		UNION SELECT COUNT(1) AS "rowCount" FROM RDN1 T1
			JOIN OILM T0 on  T0."TransType" = 16 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN RDN14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType"<> 'A' OR T0."TreeType"<> 'A') AND (T0."VersionNum"=  CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum"=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.239.21'
																												ELSE '8.80.239.21' 
																												END)
		UNION SELECT COUNT(1) AS "rowCount" FROM RIN1 T1
			JOIN OILM T0 on  T0."TransType" = 14 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN RIN14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType"<> 'A' OR T0."TreeType"<> 'A') AND (T0."VersionNum"=  CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum"=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.239.21'
																												ELSE '8.80.239.21' 
																												END)
		UNION SELECT COUNT(1) AS "rowCount" FROM CSI1 T1
			JOIN OILM T0 on  T0."TransType" = 165 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN CSI14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType"<> 'A' OR T0."TreeType" <> 'A') AND (T0."VersionNum"=  CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum"=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.239.21'
																												ELSE '8.80.239.21' 
																												END)
		UNION SELECT COUNT(1) AS "rowCount" FROM CSV1 T1
			JOIN OILM T0 on T0."TransType" = 166 AND T1."DocEntry" = T0."DocEntry" AND T1."LineNum" = T0."DocLineNum"
			JOIN CSV14 T3 on T3."DocEntry" = T1."DocEntry" AND T3."LineNum" = T1."LineNum"
		where (T1."TreeType"<> 'A' OR T0."TreeType"<> 'A') AND (T0."VersionNum"=  CASE
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.81.313.05'
																					ELSE '8.81.313.05' 
																					END OR T0."VersionNum"=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.239.21'
																												ELSE '8.80.239.21' 
																												END));
																												
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 509.]]]');
	
	END if;
	
/* Supp_510_Quantity is negative IN OILM for production order's child with 
negative Quantity on document. 
* Solution: set Quantity as effectqty IN OILM for production order 
* As a result of issue reported IN Task 17304 
*/
	select count("MessageID") into rowcount from OILM 
	where "Quantity" < 0 and "DocLineNum" <> -1 and "TransType" = 202 and "AccumType" = 3 and 
	"ItemCode" not in (select distinct t1."ItemCode" from oilm t1 where t1."BaseType" = 202 and t1."BaseAbsEnt" = "BaseAbsEnt" and t1."AppObjType" = 'B');
		
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 510.]]]');
	
	END if;
/* Supp_511 & 512 & 513_Inconsistencies between table WKO1 AND OILM
 * There are several inconsistencies between table WKO1 AND OILM for some workorders.
 * This query check for these inconsistencies AND RETURN theirs DocEntry AND DocLineNum.
 * It also checks for inconsistency Quantity sign vs IN/out ActionType
 * Solution: update OR delete OR create inconsistent OWKO, OITT, ITT1 OR create BoM
 * Result of investigation on CRM 455009 2011, ID 25833, ID 26333
*/
-- WKO1.OrderNum=OILM.DocEntry AND WKO1.LineID AND OILM.DocLineNum AND OILM.TransType=68 there is inconsistency IN ItemCodes
	select count(1) into rowcount 
	from (WKO1 t0 
	    LEFT OUTER JOIN OILM t1 on t0."OrderNum" = t1."DocEntry" 
		                        and t0."LineID" = t1."DocLineNum" 
								and t0."ItemCode" = t1."ItemCode" 
								-- check corresponding lines
			                    and	((t0."Quantity" > 0 and t1."ActionType" = 1) or (t0."Quantity" < 0 and t1."ActionType" = 2)) 
								-- find corresponding line FROM OILM according to Quantity AND ActionType
								and	t1."TransType" = 68) 
		where t1."MessageID" is null
		--check only TransType 68 lines, IF the MessageID is NULL, it means there is an inconsistency between OILM AND wko1
			  and exists (select count(1) 
						  from OILM t2 
						  where t2."DocEntry" = t0."OrderNum" and t2."DocLineNum" = t0."LineID" and t2."TransType" = 68);
        -- make sure that there is at least one TransType 68
		--order BY OrderNum, LineId
		
		if :rowcount > 0 then
		    DB_Impacted := 'Y';
		
		    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 511.]]]');
			
	End if;

	-- check if any WKO1 order has quantity inconsistency with OILM transtype=68 and actiontype (2 for quantity>0 and 1 for quantity<0)
	
	select count(1) into rowcount 
	from WKO1 t0 
	    inner join 
		(SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "DocEntry", "DocLineNum" ORDER BY "MessageID") AS "seq" FROM OILM where "TransType" = 68) p0 WHERE p0."seq" = 1) t1
			ON t0."OrderNum" = t1."DocEntry" AND t0."LineID" = t1."DocLineNum" 
			   AND t0."ItemCode" = t1."ItemCode" 
			   AND ((t0."Quantity" > 0 AND t1."ActionType" = 2)
						OR (t0."Quantity" < 0 AND t1."ActionType" = 1));
		
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 512.]]]');
	End if;
	-- check for some work orders in OILM that are having only one action type (in or out)
	select count(1) into rowcount 
	from (select count(*) from OILM where "TransType" = 68 group by "DocEntry", "DocLineNum" having COUNT(distinct "ActionType") = 1);
	
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 513.]]]');
	End if;

/**
 * Supp_514_FIFO items received via transactions 58 OR '-2' have zero Total LC/FC/SC
 * CRM 200230 2012 - find more info AND fix proposals 
 * 
*/

	SELECT COUNT(1) INTO rowCount 
	FROM OILM T0 
		INNER JOIN OIVL T1 ON T0."MessageID" = T1."MessageID"
		INNER JOIN IVL1 t2 ON t1."TransSeq" = T2."TransSeq"
	WHERE t0."TransType" IN (58,-2)
		  AND t0."TotalLC" = 0
		  AND t2."TransValue" <> 0
		  AND T1."InQty" <> 0
		  AND T1."CostMethod" = 'F';
		  
	IF :rowCount > 0 THEN
		DB_Impacted := 'Y';
		
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 514.]]]');

	END IF;
	
--removed 515
	/**  
	 * Supp_516_Corrupted OILM messages are created added into OILM during ATP upgrade to 9.0 PL09
	 *  E.g., messages for Sales Order, Purchase Orders, Reserve Invoices added in versions prior 8.8 
	 *  and affecting committed and ordered accumulators.  
	 *	(1) 'createdate' of such record might be set to date of the upgrade; 
	 *	(b) and 'versionnum' is set to NULL ; 
	 *	(c) finally, message is placed incorrectly in OIML with 'messageid' higher than last message 
	*/ 
       IF(:cmpVersionNum >= 890000) THEN
                    SELECT (IFNULL(MIN("MessageID"), 0) - 1) INTO max07Msg FROM OILM WHERE "VersionNum" >=  CASE
																												WHEN :cmpVersionNum >= 1000000
																												THEN '08.80.000.00'
																												ELSE '8.80.000.00' 
																												END;
                    SELECT COUNT(1) INTO rowCount 
                    FROM OILM
                    WHERE "MessageID" > :max07Msg
                             AND IFNULL("VersionNum", '') < CASE
															WHEN :cmpVersionNum >= 1000000
															THEN '08.80.000.00'
															ELSE '8.80.000.00' 
															END;
       END IF;
             
         IF :rowCount >0 THEN
                    DB_Impacted := 'Y';
 
                    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 516.]]]');

         END IF;
             
    /** 
    * 
	* Supp_517_Incorrect lenght of user column in U tables after upgrade 
	* Solution: Increase lenght of user def.column in U table according to O table 
	* More detail in SAP Note 1645439 
    */
    IF(:cmpVersionNum >= 881319) THEN
        SELECT 
        (SELECT COUNT(*)
       		FROM 
        	TABLE_COLUMNS t0 
            INNER JOIN 
            TABLE_COLUMNS t1 
            ON 
            t0.COLUMN_NAME = t1.COLUMN_NAME 
            AND  t0.TABLE_NAME <> t1.TABLE_NAME 
            AND t0.SCHEMA_NAME = t1.SCHEMA_NAME
        	AND t0.SCHEMA_NAME IN (SELECT current_schema FROM DUMMY)
        			WHERE t0.TABLE_NAME IN ('OITM', 'UITM', 'OITW', 'UITW', 'OWKO', 'UWKO', 'OWOR', 'UWOR')
        			AND
        			t1.TABLE_NAME IN ('OITM', 'UITM', 'OITW', 'UITW', 'OWKO', 'UWKO', 'OWOR', 'UWOR')
        			AND RIGHT(t1.TABLE_NAME, 3) = RIGHT(t0.TABLE_NAME, 3) 
        			AND t1.COLUMN_NAME LIKE 'U\_%' ESCAPE '\' 
                    AND t1.LENGTH < t0.LENGTH   ) INTO rowCount FROM DUMMY;
                    
           END IF;         
                    
        IF :rowCount >0 THEN
                    DB_Impacted := 'Y';

                    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 517.]]]');
            
        
   		END IF; 
		
	/** 
    * 
	* Supp_518_Missing warehouse 
	* Solution: add missing warehouse to Item master data or OITW 
	* see IM 100011 / 2014 / IVI Fails after OITW deletion 
    */
	IF(:cmpVersionNum >= 902000 AND :cmpVersionNum <= 902002) THEN
	        SELECT 
	        (SELECT COUNT(*)
	      		 from oivl l 
				full outer join oitw w 
					on l."ItemCode"=w."ItemCode" 
					and l."LocCode"=w."WhsCode"
					where w."WhsCode" is null) 
	        INTO rowCount FROM DUMMY;
                    
        END IF;
		
	if :rowcount > 0 then
		DB_Impacted := 'Y';
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 518.]]]');
	End if;
	
/** 
	* Supp_519_Note_1320969 - Upgrade Wizard Precheck: Triggers check 
	* Remove any triggers before IVI 
	* rel 9.5.2014 
	*************************************************
	* on HANA there are some triggers used on many tables due some system feature - therefore this check cannot be used the same way as on SQL
	* therefore I am skipping this check for the moment till clear rules where triggers can be will not be set 
	* to avoid false detection - seems that system created triggers do not affect recalculation
	*/ 
	
	/*
    *
	* Supp_520_Stock Counting document line set to Freez
	* Solution: Un-tick the Freeze check-box on all detected Stock Counting document lines. DO NOT use SQL update query.
	* After recalculation update the documents to original state
	* Or upgrade to 9.1 PL05 or 9.0 PL16 as it is fixed there
	
    */
    
    IF((:cmpVersionNum >= 900046 AND :cmpVersionNum <= 902250) 
    	OR (:cmpVersionNum >= 910105 AND :cmpVersionNum <= 910140))
    THEN
    
   		SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont';
    	IF :temp_var_0 > 0 THEN 
       		 DROP TABLE "StcCont";
        END IF;
        CREATE COLUMN TABLE "StcCont" ("recCount" integer);
        
        sqlStr := '
					 INSERT INTO "StcCont"
						SELECT COUNT(*)
						FROM OITW
						WHERE "Freezed" = ''Y''
					 
        ';
        EXEC(:sqlStr);
        
        SELECT 
        (SELECT sum("recCount") 
       		 FROM "StcCont"
	 	 where "recCount"<> 0) 
		INTO rowCount FROM DUMMY;
    END IF;
        
        IF :rowCount >0 THEN
                    DB_Impacted := 'Y';

                    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 520.]]]');
 
        END IF;
                         
        SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont';
    	IF :temp_var_0 > 0 THEN 
       		 DROP TABLE "StcCont";
        END IF;

/**
 * Supp_522-523_Note_933476_Missing Header - Documents lines with no header
 * new document types Inventory Posting and Initial Quantities
 * for more detail refer to Note 933476
*/
--OIQR
	SELECT COUNT(T0."DocEntry") INTO rowCount 
             FROM   IQR1 T0
                    INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
                                          and T1."InvntItem" = 'Y'
             WHERE  T0."DocEntry" not in ( Select "DocEntry"
                                         from   OIQR );
        
    IF :rowCount > 0 THEN
		DB_Impacted := 'Y';

		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 522.]]]');
		
	END IF;

--OIQI
	SELECT COUNT(T0."DocEntry") INTO rowCount 
             FROM   IQI1 T0
                    INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode"
                                          and T1."InvntItem" = 'Y'
             WHERE  T0."DocEntry" not in ( Select "DocEntry"
                                         from   OIQI );
        
    IF :rowCount > 0 THEN
		DB_Impacted := 'Y';

		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 523.]]]');
		
	END IF;

/**
 * Supp_524-525_Note_933476_Missing Rows - Documents header with no rows
 * new document types Inventory Posting and Initial Quantities
 * for more detail refer to Note 933476
*/

--OIQR
	SELECT COUNT(T0."DocEntry") INTO rowCount 
             FROM   OIQR T0
             WHERE  T0."DocEntry" not in ( Select "DocEntry"
                                         from   IQR1 );
    IF :rowCount > 0 THEN
		DB_Impacted := 'Y';
		
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 524.]]]');
				
	END IF;

--OIQI
	SELECT COUNT(T0."DocEntry") INTO rowCount 
             FROM   OIQI T0
             WHERE  T0."DocEntry" not in ( Select "DocEntry"
                                         from   IQI1 );
          
    IF :rowCount > 0 THEN
		DB_Impacted := 'Y';

		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 525.]]]');
		
	END IF;
	
/**
 * Supp_526_Parent changed from MAP to FIFO valuation method
 * Parent was not FIFO, but currently it is FIFO, therefore IVI makes all transactions as FIFO
 * Production order closing needs to have FIFO layers in OILM, therefore rebuild of UILM is necessary
 * for more detail refer to JIRA Debug Task SBO92-14589
*/	

SELECT COUNT(1) INTO rowCount
from OIVL T0
 INNER JOIN OITM T1
 on T0."ItemCode" = T1."ItemCode"
 where T0."CostMethod" <> T1."EvalSystem"
 and T1."EvalSystem" = 'F' and exists (select top 1 1 from oivl where "TransType" = 202);


IF :rowCount > 0 THEN
			
		SELECT COUNT(1) INTO rowCount FROM SYS.TABLES WHERE "SCHEMA_NAME" = CURRENT_SCHEMA AND "TABLE_NAME" = N'TEMP_526_OWOR_LIST';
			IF :rowCount > 0 THEN
				DROP TABLE "TEMP_526_OWOR_LIST";
			END IF;	

		 	--temporary table to store affected WOR from detection query 
			create table "TEMP_526_OWOR_LIST" ("CreatedBy" int);
			 
			sqlStr := ' insert into "TEMP_526_OWOR_LIST"
			 			select distinct oivl."CreatedBy"
			 			from oivl 
			 			inner join oitm 
			 			on oitm."ItemCode" = oivl."ItemCode"
			 			where oivl."MessageID" in 
			 			(select "MessageID" from oilm where "TransType"=202 and "ActionType" in (7,8) and "DocAction"=5)
			 			and
			 			oivl."CostMethod" <> oitm."EvalSystem" and oitm."EvalSystem" in (''F'')';

			EXEC(:sqlStr);

		
		 SELECT
		 min(oilm."CreateDate"), 
		 max(oilm."CreateDate") 
		 INTO 
		 Min_526_Reorder, 
		 Max_526_Reorder
		 from OILM
		 where 1 = 1
		 AND
		 (
			 --min max createdates from OILM for transactions linked to affected production orders
			 ("ApplObj" = 202 and "AppObjAbs" in (select "CreatedBy" from "TEMP_526_OWOR_LIST"))
				 OR
			 --min max createds from OILM from TT 202 entries of the affected production orders
			 ("TransType" = 202 and "DocEntry" in (select "CreatedBy" from "TEMP_526_OWOR_LIST"))
		 );


		SELECT COUNT(1) INTO rowCount
			from "TEMP_526_OWOR_LIST"
			 where
			 --checking if reodering is not already set in CIVI for affected range of documents
			 (
				(select TOP 1 ifnull("ReorderFrm", now()) from CIVI) > :Min_526_Reorder
				 or
				(select TOP 1 ifnull("ReorderTo", '1950-01-01') from CIVI) < :Max_526_Reorder
			 );

				IF :rowCount > 0 THEN
						DB_Impacted := 'Y';
						
						INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 526.]]] Set CIVI.ReorderFrm: ' || cast(:Min_526_Reorder AS DATE) || ' ' || 'ReorderTo: ' || cast(:Max_526_Reorder  AS DATE));
						
						
					end if;
		  	
			SELECT COUNT(1) INTO rowCount FROM SYS.TABLES WHERE "SCHEMA_NAME" = CURRENT_SCHEMA AND "TABLE_NAME" = N'TEMP_526_OWOR_LIST';
			IF :rowCount > 0 THEN
				DROP TABLE "TEMP_526_OWOR_LIST";
			END IF;	

 end if;	

/**
 * Supp_527_Closed_GRPO_Based_on_Purchase_Order_with_SnB_Item
 * Internal scenario in case there is any Closed GRPO based on Purchase Order with SnB item
 * recalculation on 9.2 until PL06 would probably fail
 * for more detail refer to JIRA SBO92-15140
*/	 
 
IF( (:cmpVersionNum >= 920000 AND :cmpVersionNum <= 920150) and hasActualCostingItems = 'Y')

		THEN
		
		SELECT COUNT(1) INTO rowCount
		FROM OILM t0
			   WHERE 
			   t0."TransType" = 20
			   --only GRPO which are based on Purchase Orders 
			   AND t0."BaseType" = 22
			   --it has to be in foreign currency
			   AND t0."PriceRate" <> 0
			   --GRPO needs to be closed
			   AND t0."ActionType" = 9
			   --item must be using Actual costing
			   AND EXISTS (
					 SELECT 1
							FROM oitm
							WHERE "ItemCode" =  t0."ItemCode"
							AND "EvalSystem" = 'B' );
								   
			IF :rowCount > 0 THEN
					DB_Impacted := 'Y';						
					INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Recalculation will probably fail see SBO92-15140. For IVI use 9.2 PL06 or higher');
					
					
			END IF;			
END IF;

/**
 * Supp_528_ILM3_Incorrect link to production order
 * Solution: Run following update query
 * 	update t0 
 *	set t0."POLine" = t1."BaseLine" 
 *	from ilm3 t0 inner join ige1 t1 
 *	on t0."BaseAbsEnt" = t1."DocEntry" and t0."BaseLine" = t1."LineNum" 
 *	where t0."POLine" <> t1."BaseLine" 
 *  JIRA SBO92-23017
*/ 
	
	IF(:cmpVersionNum >= 910105)
		THEN
			
			SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont528';
			IF :temp_var_0 > 0 THEN 
				DROP TABLE "StcCont528";
			END IF;
			
			CREATE COLUMN TABLE "StcCont528" ("recCount" integer);
		
			sqlStr := '
			insert into "StcCont528"
				select count(*)
					from ilm3 t0 
					inner join ige1 
					t1 on t0."BaseAbsEnt" = t1."DocEntry" and t0."BaseLine" = t1."LineNum" 
					where t0."POLine" <> t1."BaseLine" 
			';
			
			EXEC(:sqlStr);
			
			SELECT 
				(SELECT sum("recCount") 
					FROM "StcCont528"
				where "recCount"<> 0) 
				INTO rowCount FROM DUMMY;
			
			END IF;
        
        IF :rowCount >0 THEN
                    DB_Impacted := 'Y';

                    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 528.]]]');
 
        END IF;
                         
        SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont528';
    	IF :temp_var_0 > 0 THEN 
       		 DROP TABLE "StcCont528";
		END IF;

/**
* Supp_529_MRV2_Incorrect_INMSubLine
* Solution: Run update queries from JIRA
*     		JIRA SBO92-37213       
*/

 SELECT COUNT(MRV2."DocEntry") INTO rowCount
 from mrv2 inner join mrv1 on MRV2."DocEntry"=mrv1."DocEntry" and mrv1."LineNum"=mrv2."LineNum"
 inner join oitm on mrv1."ItemCode"=oitm."ItemCode"
 where oitm."EvalSystem"='F' and mrv2."INMTransTy"=202 and mrv2."INMSubLine"=-1;

 IF :rowCount > 0 THEN
		DB_Impacted := 'Y';
		
		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 529.]]]');
				
 END IF;
 
 /**
* Supp_530_Open_Item_List_Production_Order_Closing_Bug
* affects 9.2PL05_9.2PL10
* SAP Notes 2581955  and   2561522    		       
*/
-- First WOR closing of FIFO parent can write revaluation, later the owner is WOR not Receipt for production anymore
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#FIRSTFIFOWOR_REVALUATION' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #FIRSTFIFOWOR_REVALUATION;
		END IF;
		
		
CREATE LOCAL TEMPORARY TABLE #FIRSTFIFOWOR_REVALUATION AS (select oivl."CreatedBy",min(oivl."MessageID") as "FirstFIFOReval" from oivl 
where oivl."CostMethod"= 'F' and oivl."TransType"=202
group by oivl."CreatedBy");

-- First WOR closing of FIFO parent can write revaluation, later the owner is WOR not Receipt for production anymore
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#FIFO_ORDER_COMMIT_AFTER_REVAL' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #FIFO_ORDER_COMMIT_AFTER_REVAL;
		END IF;
		
CREATE LOCAL TEMPORARY TABLE #FIFO_ORDER_COMMIT_AFTER_REVAL AS (select "MessageID", "CreatedBy" from oilm inner join #FIRSTFIFOWOR_REVALUATION t 
on oilm."TransType"=202 and oilm."DocEntry"=t."CreatedBy" and oilm."MessageID">t."FirstFIFOReval" and oilm."AccumType" in (2,3));

-- Open production orders having revaluation
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#OPEN_WORS_HAVING_REVALUATION' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #OPEN_WORS_HAVING_REVALUATION;
		END IF;

CREATE LOCAL TEMPORARY TABLE #OPEN_WORS_HAVING_REVALUATION AS (select distinct OILM."DocEntry" from OILM
inner join OWOR on OWOR."DocEntry"=OILM."DocEntry"
where OWOR."Status" <> 'L' and OILM."TransType"=202 and OILM."ActionType" in (7,8));

-- Open production orders having journal entry
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#OPEN_WORS_HAVING_JE' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #OPEN_WORS_HAVING_JE;
		END IF;

CREATE LOCAL TEMPORARY TABLE #OPEN_WORS_HAVING_JE AS (select distinct OWOR."DocEntry" from OWOR
inner join OJDT on OWOR."DocEntry"=OJDT."CreatedBy"
where OWOR."Status" <> 'L' and OJDT."TransType"=202);

-- Closed production orders having duplicate revaluation for MAP/SnB
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#CLOSED_WORS_HAVING_DUPLICATEDREVALUATION' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #CLOSED_WORS_HAVING_DUPLICATEDREVALUATION;
		END IF;

CREATE LOCAL TEMPORARY TABLE #CLOSED_WORS_HAVING_DUPLICATEDREVALUATION AS (
select distinct OILM."DocEntry" from OILM
inner join OWOR on OWOR."DocEntry"=OILM."DocEntry"
where OWOR."Status" = 'L' and OILM."TransType"=202 and OILM."ActionType" in (7,8)
group by OILM."DocEntry", OILM."ActionType", OILM."LocCode", OILM."SubLineNum", OILM."TransSeqRf"
having count(*) > 1
union 
select distinct "CreatedBy" from #FIFO_ORDER_COMMIT_AFTER_REVAL);


-- Closed production orders having duplicate journal entry
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#CLOSED_WORS_HAVING_DUPLICATEDJE' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #CLOSED_WORS_HAVING_DUPLICATEDJE;
		END IF;
		
CREATE LOCAL TEMPORARY TABLE #CLOSED_WORS_HAVING_DUPLICATEDJE AS (
select distinct OJDT."CreatedBy" as "DocEntry" from OJDT
inner join OWOR on OJDT."CreatedBy"=OWOR."DocEntry"
where OJDT."TransType"=202 and OWOR."Status" = 'L'
group by OJDT."CreatedBy"
having count(*) > 1);

-- ====================================================================================================================================
-- ====================================================================================================================================

-- Open production orders having journal entry excluding cases with revaluation (manually check and fix OILM)
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#OPEN_WORS_HAVING_JE_WITHOUT_REVALUATION' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #OPEN_WORS_HAVING_JE_WITHOUT_REVALUATION;
		END IF;
		
CREATE LOCAL TEMPORARY TABLE #OPEN_WORS_HAVING_JE_WITHOUT_REVALUATION AS (
select distinct OWOR."DocEntry" from OWOR
inner join OJDT on OWOR."DocEntry"=OJDT."CreatedBy"
where OWOR."Status" <> 'L' and OJDT."TransType"=202 
and OWOR."DocEntry" not in (select "DocEntry" from #OPEN_WORS_HAVING_REVALUATION));

-- Closed production orders having duplicate journal entry excluding cases with revaluation (manually check and fix OILM)
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#CLOSED_WORS_HAVING_DUPLICATEDJE_WITHOUT_REVALUATION' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #CLOSED_WORS_HAVING_DUPLICATEDJE_WITHOUT_REVALUATION;
		END IF;

CREATE LOCAL TEMPORARY TABLE #CLOSED_WORS_HAVING_DUPLICATEDJE_WITHOUT_REVALUATION as (
select distinct OJDT."CreatedBy" as "DocEntry" from OJDT
inner join OWOR on OJDT."CreatedBy"=OWOR."DocEntry"
where OJDT."TransType"=202 and OWOR."Status" = 'L' and OJDT."CreatedBy" not in (select "DocEntry" from #CLOSED_WORS_HAVING_DUPLICATEDREVALUATION)
group by OJDT."CreatedBy"
having count(*) > 1);
-- ====================================================================================================================================
-- ====================================================================================================================================
-- Store in temp table all problematic wors 
select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#T_DOC' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #T_DOC;
		END IF;

CREATE LOCAL TEMPORARY TABLE #T_DOC as (	
select t."DocEntry" from
(select * from #OPEN_WORS_HAVING_REVALUATION
union
select * from #OPEN_WORS_HAVING_JE
union
select * from #CLOSED_WORS_HAVING_DUPLICATEDREVALUATION
union
select * from #CLOSED_WORS_HAVING_DUPLICATEDJE) t);


-- ====================================================================================================================================
-- ====================================================================================================================================
-- Store in temp table all problematic JEs (filtered to specific docs)

select count (*) into rowCount from "PUBLIC"."M_TEMPORARY_TABLES" where TABLE_NAME = '#T_JE' AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION;
		IF :rowCount > 0 THEN
			DROP TABLE #T_JE;
		END IF;
		
CREATE LOCAL TEMPORARY TABLE #T_JE AS (
select t."TransId" from
(
select "TransId" from OJDT
inner join #OPEN_WORS_HAVING_JE on #OPEN_WORS_HAVING_JE."DocEntry" = OJDT."CreatedBy"
where "TransType" = 202
) t);
	
--final results IVI
select count (*) into rowCount
from 
(select top 1 "DocEntry" from #T_DOC
	   union
select top 1 "TransId" from #T_JE);
	
 IF :rowCount > 0 THEN
		DB_Impacted := 'Y';

		INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 530.]]]');
		
 END IF;


/**
 * Supp_531_ILM3_Incorrect POLine
 * Solution: First upgrade DB to 9.3 PL05 or higher patch and then run fixing query from  
 * JIRA SBO93-18203 then run IVI
*/ 
	
	IF(:cmpVersionNum >= 910105)
		THEN
			
			SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont531';
			IF :temp_var_0 > 0 THEN 
				DROP TABLE "StcCont531";
			END IF;
			
			CREATE COLUMN TABLE "StcCont531" ("recCount" integer);
		
			sqlStr := '
			insert into "StcCont531"
				select count(*)
					from
					(select 
					t0."MessageID",t0."LineID", t0."POLine",
					lag(t0."POLine",1,0) over (order by t0."MessageID", t0."LineID") as "Prev_POLine", 
					lag(t0."ItemType",1,0) over (order by t0."MessageID", t0."LineID") as "Prev_ItemType",
					lag(t0."ItemCode",1,'||'''0'''||') over (order by t0."MessageID", t0."LineID") as "Prev_ItemCode",
					lag(t0."LocType",1,0) over (order by t0."MessageID", t0."LineID") as "Prev_LocType",
					lag(t0."LocCode",1,'||'''0'''||') over (order by t0."MessageID", t0."LineID") as "Prev_LocCode",
					lag(t0."BaseAbsEnt",1,0) over (order by t0."MessageID", t0."LineID") as "Prev_BaseAbsEnt",
					t2.* from ilm3 t0
					inner join oilm t1 on
					t0."MessageID"= t1."MessageID"
					left join wor1 t2 on
					t2."DocEntry"	= t1."BaseAbsEnt" and
					t2."LineNum"	= t0."POLine" and 
					t2."ItemCode"	= t0."ItemCode" and 
					t2."ItemType"	= t0."ItemType" and
					t2."wareHouse"	= t0."LocCode"
					) T
					Where T."Prev_BaseAbsEnt" > 0 and T."DocEntry" is NULL 
			';
			
			EXEC(:sqlStr);
			
			SELECT 
				(SELECT sum("recCount") 
					FROM "StcCont531"
				where "recCount"<> 0) 
				INTO rowCount FROM DUMMY;
			
			END IF;
        
        IF :rowCount >0 THEN
                    DB_Impacted := 'Y';
                    
                    INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 531.]]]');
                       
        END IF;
                         
        SELECT COUNT(1) INTO temp_var_0 
    		FROM M_tables 
    			WHERE "SCHEMA_NAME" = (SELECT CURRENT_SCHEMA FROM dummy) 
    			AND TABLE_NAME = 'StcCont531';
    	IF :temp_var_0 > 0 THEN 
       		 DROP TABLE "StcCont531";
	END IF;	
	
	
------------------------------------------End of Recalculation by OILM & recalculation by UILM _ shared part
------------------------------------------------------------------
/* Recalculation BY OILM */
------------------------------------------------------------------										
		  
		  
	IF (:Recalc_By_OILM = 'Y' OR :stockissue88 = 'Y') THEN 
/**
 * Supp_402_Qty in target document is greater than in base document in OILM
 *
 * 
*/					
		  
		SELECT COUNT(t0."MessageID") INTO rowcount 
		FROM   "OILM" t0 
		INNER JOIN "OILM" t1 ON t0."BaseType" = t1."TransType" 
				AND t0."BaseAbsEnt" = t1."DocEntry" 
				AND t0."BaseLine" = t1."DocLineNum" 
				AND t0."ItemCode" = t1."ItemCode" 
				AND t0."AccumType" = 1 
				AND t1."AccumType" = 1 
				AND t0."Quantity" > t1."Quantity" 
				AND NOT (t0."TransType" = 67 AND t0."BaseType" = 20) 
				AND NOT (t0."BaseType" in (163, 164,165,166,202))
				AND NOT (t0."TransType" in (163, 164,165,166));

		if :rowcount > 0 then 
			DB_Impacted := 'Y';							   
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 402.');
		END if;
/**
 * Supp_405_Production Information is NOT consistent IN OILM
 * Base type AND appl type is NOT consistent, The missing link FROM OILM to wor are as a result of IVU recalculation AND update on IVU 2007/2005 which clears the links FROM OINM to wor for componnets which were removed FROM wor.
 * Issue was reported to ibd: Csn 144255 2010.
 * 
*/														
		SELECT COUNT("DocEntry") INTO rowcount 
		FROM   "OILM" 
		WHERE  "TransType" IN (59,60) AND "BaseType" = 202 
		       AND "ApplObj" IS NULL;					 
		
		IF :rowcount > 0 then 
		    DB_Impacted := 'Y';
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 405.');
			
		END if; 

/**
 * Supp_408_OIVL_Inqty_Outqty_both_nonzero_for_WKO
 * DocLineNum for type 68 was set to zero for all wko1 lines due to old bug IN IVU 2005sp1. It will
 * bring the result that the OIVL have InQty AND OutQty AND cause IVI run fail. Task 13604
 * The fix should NOT forget to adjust corresponding OILM records qty.
 * 
*/		
		SELECT COUNT (1) INTO rowCount
			FROM OILM
			WHERE CASE
					WHEN :cmpVersionNum >= 1000000
					THEN '08.80.000.00'
					ELSE '8.80.000.00' 
					END <= IFNULL("VersionNum", '')
			      AND IFNULL("VersionNum", '') < CASE
													WHEN :cmpVersionNum >= 1000000
													THEN '08.80.228.09'
													ELSE '8.80.228.09' 
													END;
if :Update_before = 'Y' and :rowcount > 0 then 
				select count("TransSeq") into rownum
			from   "OIVL" 
			where  "TransType" = 68 
			       and "InQty" <> 0 
				   and "OutQty" <> 0;
			if :rownum > 0 then 
				DB_Impacted := 'Y';
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 408.');
				
			end if;	     
		end if;            
/**
 * Supp_409_TotalLC_is_zero_AND_Price/EffectQty_not_zero
 * the below check when recalculation is BY OILM will fail support interaction for no reason under the below scenario:
		Db started before 2005 sp1
		Db was recalculated IN the past
	When recalculation is BY OILM AND document EXISTS, OILM.TotalLC is populated BY the recalculated doc1.stocksum. IF doesnott exist (after DA) , it will be populated BY qty*Price (the feature "update OILM.TotalLC during ivi".
	I think we should restrict the below check to document version >=2005sp1/2005B.

	--- TotalLC is zero AND Price/EffectQty NOT zero IN OILM
													(t0.Price <> 0
													  AND t0.TotalLC = 0)
 * 
*/
/********************************************************/
			   
		DB_Impacted := 'N';
		DELETE FROM "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero";
	    insert into "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero"
		SELECT t0.*
		FROM   "OILM" t0 
		INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
			AND t0."ItemCode" = t1."ItemCode" 
			AND  t1."InvntItem" = 'Y' 
			AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
			AND (IFNULL(t0."Price",0) <> 0 
				AND IFNULL(t0."TotalLC",0) = 0 
				AND (t0."TransType" IN (18,19,20,163,164,69) 
					OR (t0."TransType" = 21 
						AND t0."BaseType" <> 20) 			   
					OR (t0."TransType" = 59 
						AND t0."BaseType" <> 202) 
					OR (t0."TransType" IN (/*-2,58,--in 503*/310000001,10000071) 		  
						AND t0."ActionType" <> 2)) 
				AND Round(t0."Price" * t0."Quantity", :sum_decimals) <> 0); 
				
		SELECT Count(1) INTO rowcount
		FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero";
IF :rowcount <> 0 THEN 														   
            --JOIN the document table to see IF version >=2005SP1/2005B  
			--OPCH
			SELECT COUNT(t0."MessageID") INTO rownum 
			FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
			   INNER JOIN "OPCH" t1 ON t0."TransType" = 18 
				                    AND t0."DocEntry" = t1."DocEntry" 
				                    AND ((IFNULL(t1."VersionNum", '') >= CASE 
										 				                   WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END AND IFNULL(t1."VersionNum", '') <  CASE 
																				 										WHEN :cmpVersionNum >= 1000000
																														THEN '06.80.999.00'
																														ELSE '6.80.999.00' 
																														END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
											 										WHEN :cmpVersionNum >= 1000000
																					THEN '07.40.000.00'
																					ELSE '7.40.000.00' 
																					END AND IFNULL(t1."VersionNum", '') < CASE 
																					 										WHEN :cmpVersionNum >= 1000000
																															THEN '07.40.999.00'
																															ELSE '7.40.999.00' 
																															END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
										 										WHEN :cmpVersionNum >= 1000000
																				THEN '08.00.000.00'
																				ELSE '8.00.000.00' 
																				END))  
			inner join "PCH1" t2 on t0."DocEntry" = t2."DocEntry"
				and t0."DocLineNum" = t2."LineNum"
				and Round(t2."Price" * t2."Quantity",:sum_decimals) <> 0;

			IF :rownum > 0 then
				DB_Impacted := 'Y'; 
			END if;										 
			
			--ORCP
			SELECT COUNT(t0."MessageID") INTO rownum 
			FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
			    INNER JOIN "ORPC" t1 ON t0."TransType" = 19 
				                     AND t0."DocEntry" = t1."DocEntry" 
								     AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '07.40.000.00'
																					ELSE '7.40.000.00' 
																					END AND IFNULL(t1."VersionNum", '') < 
																				  CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '07.40.999.00'
																					ELSE '7.40.999.00' 
																					END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.00.000.00'
																					ELSE '8.00.000.00' 
																					END))  
			inner join "RPC1" t2 on t0."DocEntry" = t2."DocEntry"
				and t0."DocLineNum" = t2."LineNum"
				and Round(t2."Price" * t2."Quantity",:sum_decimals) <> 0;
				
			IF :rownum > 0 then 														 
				DB_Impacted := 'Y'; 											 
			END if;
			--OPDN							
			SELECT COUNT(t0."MessageID") INTO rownum 
			FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
				INNER JOIN "OPDN" t1 ON t0."TransType" = 20 
									 AND t0."DocEntry" = t1."DocEntry" 
									 AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '07.40.000.00'
																					ELSE '7.40.000.00' 
																					END AND IFNULL(t1."VersionNum", '') < 
																				  CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '07.40.999.00'
																					ELSE '7.40.999.00' 
																					END)
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
																					WHEN :cmpVersionNum >= 1000000
																					THEN '08.00.000.00'
																					ELSE '8.00.000.00' 
																					END)) 
			inner join "PDN1" t2 on t0."DocEntry" = t2."DocEntry"
				and t0."DocLineNum" = t2."LineNum"
				and Round(t2."Price" * t2."Quantity",:sum_decimals) <> 0;
			IF :rownum > 0 then	 
				DB_Impacted := 'Y';
			END if;

			--OCPI 										 
			SELECT COUNT(t0."MessageID") INTO rownum 
			FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
				INNER JOIN "OCPI" t1 ON t0."TransType" = 163 
									 AND t0."DocEntry" = t1."DocEntry" 
				AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
									     OR (IFNULL(t1."VersionNum", '') >= CASE 
																			  WHEN :cmpVersionNum >= 1000000
																			  THEN '07.40.000.00'
																			  ELSE '7.40.000.00' 
																			  END AND IFNULL(t1."VersionNum", '') < 
																			CASE 
																			  WHEN :cmpVersionNum >= 1000000
																			  THEN '07.40.999.00'
																			  ELSE '7.40.999.00' 
																			  END)
									     OR (IFNULL(t1."VersionNum", '') >= CASE 
																			  WHEN :cmpVersionNum >= 1000000
																			  THEN '08.00.000.00'
																			  ELSE '8.00.000.00' 
																			  END));
					
  IF :rownum > 0 then	 
				DB_Impacted := 'Y';
  end if;
				--OCPV 
				SELECT COUNT(t0."MessageID")  INTO rownum 
				FROM  "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
					INNER JOIN "OCPV" t1 ON t0."TransType" = 164 
										 AND t0."DocEntry" = t1."DocEntry" 
										 AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.000.00'
																					  ELSE '7.40.000.00' 
																					  END AND IFNULL(t1."VersionNum", '') < 
																					CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.999.00'
																					  ELSE '7.40.999.00' 
																					  END)
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
											   										  WHEN :cmpVersionNum >= 1000000
																					  THEN '08.00.000.00'
																					  ELSE '8.00.000.00' 
																					  END)); 
				IF :rownum > 0 then	 
					DB_Impacted := 'Y';
				END if; 
				SELECT COUNT(t0."MessageID") INTO rownum 
			    FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
					INNER JOIN "OIPF" t1 ON t0."TransType" = 69 
										 AND t0."DocEntry" = t1."DocEntry" 
										AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.000.00'
																					  ELSE '7.40.000.00' 
																					  END AND IFNULL(t1."VersionNum", '') < 
																					CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.999.00'
																					  ELSE '7.40.999.00' 
																					  END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
											   										  WHEN :cmpVersionNum >= 1000000
																					  THEN '08.00.000.00'
																					  ELSE '8.00.000.00' 
																					  END));
				IF :rownum > 0 then	 
					DB_Impacted := 'Y';
				END if;

				--ORPD 												
				SELECT COUNT(t0."MessageID") INTO rownum  
				FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
					INNER JOIN "ORPD" t1 ON t0."TransType" = 21 
										 AND t0."DocEntry" = t1."DocEntry" 
										 AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.000.00'
																					  ELSE '7.40.000.00' 
																					  END AND IFNULL(t1."VersionNum", '') < 
																					CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.999.00'
																					  ELSE '7.40.999.00' 
																					  END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
											   										  WHEN :cmpVersionNum >= 1000000
																					  THEN '08.00.000.00'
																					  ELSE '8.00.000.00' 
																					  END))
				inner join "RPD1" t2 on t0."DocEntry" = t2."DocEntry"
										and t0."DocLineNum" = t2."LineNum"
										and Round(t2."Price" * t2."Quantity", :sum_decimals) <> 0;
				IF :rownum > 0 then	 
					DB_Impacted := 'Y';
		        END IF;
				SELECT COUNT(t0."MessageID") INTO rownum  
				FROM   "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
					INNER JOIN "OIGN" t1 ON t0."TransType" = 59 
										 AND t0."DocEntry" = t1."DocEntry" 
										 AND ((IFNULL(t1."VersionNum", '') >= CASE 
											 									WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.200.00'
																				ELSE '6.80.200.00' 
																				END 
																				AND IFNULL(t1."VersionNum", '') < 
																			  CASE
																			  	WHEN :cmpVersionNum >= 1000000
																				THEN '06.80.999.00'
																				ELSE '6.80.999.00' 
																				END) 
											   OR (IFNULL(t1."VersionNum", '') >=  CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.000.00'
																					  ELSE '7.40.000.00' 
																					  END AND IFNULL(t1."VersionNum", '') < 
																					CASE 
																					  WHEN :cmpVersionNum >= 1000000
																					  THEN '07.40.999.00'
																					  ELSE '7.40.999.00' 
																					  END)
											   OR (IFNULL(t1."VersionNum", '') >= CASE 
											   										  WHEN :cmpVersionNum >= 1000000
																					  THEN '08.00.000.00'
																					  ELSE '8.00.000.00' 
																					  END))
				inner join "IGN1" t2 on t0."DocEntry" = t2."DocEntry"
									 and t0."DocLineNum" = t2."LineNum"
									 and Round(t2."Price" * t2."Quantity",:sum_decimals) <> 0;
				IF :rownum > 0 then	 
					DB_Impacted := 'Y';
				END if;
				--no version check for -2 ,58 
				SELECT COUNT(t0."MessageID") INTO rownum
				FROM  "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero" t0 
				WHERE t0."TransType" IN (/*-2,58,--in 503*/310000001,10000071) AND t0."ActionType" <> 2;
				
				IF :rownum > 0 then	 
					DB_Impacted := 'Y';
				END if; 
END IF; --rownum <> 0
			DELETE FROM "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero"; 
		IF :DB_Impacted = 'Y' then 
			
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 409.');
			
			
		END if;
/**
 * Supp_410_Rate is zero when Price Currency is FC_OILM
 * --- Rate is zero when Price Currency is FC, FROM 400 query
 * 
 * 
*/							 

        SELECT COUNT(t0."MessageID") INTO rowcount 
		FROM   "OILM" t0 
		INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
			AND t0."ItemCode" = t1."ItemCode" 
			AND t1."InvntItem" = 'Y' 
			AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
			AND IFNULL(t0."Price",0) <> 0  
			AND IFNULL(t0."PriceCurr",'') <> :Main_Currency
			AND IFNULL(t0."PriceRate",0) = 0 
			AND (t0."TransType" IN (18,19,20,21,163,164,69) 
				OR (t0."TransType" = 59	AND t0."BaseType" <> 202) 
				OR (t0."TransType" IN (/*-2,58,--in 501*/310000001,10000071) AND t0."ActionType" <> 2))
			AND Ifnull(T0."TotalLC", 0) <> Ifnull(T0."ExpensesLC", 0);

		IF :rowcount > 0 then 
			DB_Impacted := 'Y';
			
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 410.');
		
		END if;
/**
 * Supp_411_TotalLC is zero AND TotalSC_TotalFC NOT zero IN OILM_OILM
 * --- TotalLC is zero AND TotalSC/TotalFC NOT zero IN OILM, FROM 400 query
 * 
 * 
*/
			SELECT COUNT(t0."MessageID") INTO rowcount 
			FROM   "OILM" t0 
			INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
								 AND t0."ItemCode" = t1."ItemCode" 
								 AND t1."InvntItem" = 'Y' 
								 AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
								 AND	(IFNULL(t0."TotalLC",0) = 0 
								 AND (IFNULL(t0."TotalFC",0) <> 0) 
				AND (t0."TransType" IN (18,19,20,21,163,164,69) 
					OR (t0."TransType" = 59 
					AND t0."BaseType" <> 202) 
					OR (t0."TransType" IN (/*-2,58,--in 502*/310000001,10000071) 
						AND t0."ActionType" <> 2)));

		IF :rowcount > 0 then 
			DB_Impacted := 'Y';						 
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 411.');
		END if;

/**
 * Supp_413_Loccode CreateDate is wrong IN OILM
 * --- FROM 400 query
 * 
 * 
*/
		
			SELECT COUNT(t0."MessageID") INTO rowcount
			FROM   "OILM" t0 
			INNER JOIN "OITM" t1 ON t0."AccumType" = 1 
								 AND t0."ItemCode" = t1."ItemCode" 
								 AND t1."InvntItem" = 'Y' 
								 AND "LocCode" NOT IN (SELECT "WhsCode" FROM "OWHS" WHERE  "DropShip" = 'Y') 
								 AND (IFNULL(t0."LocCode",'') = '' OR (t0."CreateDate" > CURRENT_TIMESTAMP));
				
		IF :rowcount > 0 then
            DB_Impacted := 'Y';
			
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 413.');
			
		END if; 
/** 
	 * Supp_414_CPV Base info wrongin OILM 
	 * --- it should be fixed IN upgrade PL10, however keep it now.
	 *  
*/ 

		SELECT COUNT(t0."MessageID") INTO rowcount
		FROM   "OILM" t0 
		INNER JOIN "CPV1" t1 ON t0."TransType" = 164 
			AND t0."DocEntry" = t1."DocEntry" 
			AND t0."DocLineNum" = t1."LineNum" 
			AND (IFNULL(t0."BaseAbsEnt", -1) <> IFNULL(t1."BaseEntry", -1) 
				OR IFNULL(t0."BaseType", -1) <> IFNULL(t1."BaseType", -1));
				
		IF :rowcount > 0 then 
			DB_Impacted := 'Y';
			
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 414.');
		END if;

/** 
	 * Supp_415_Csv Base info wrongin OILM 
	 * --- it should be fixed IN upgrade PL10, however keep it now.
	 *  
*/ 
	
		SELECT COUNT(t0."MessageID") INTO rowcount
		FROM   "OILM" t0 
		INNER JOIN "CSV1" t1 ON t0."TransType" = 166 
			AND t0."DocEntry" = t1."DocEntry" 
			AND t0."DocLineNum" = t1."LineNum" 
			AND (IFNULL(t0."BaseAbsEnt", -1) <> IFNULL(t1."BaseEntry", -1) 
				OR IFNULL(t0."BaseType", -1) <> IFNULL(t1."BaseType", -1));
			
		IF :rowcount > 0 then 
			DB_Impacted := 'Y';
		
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 415.');
			
		END if;

/**  
 * Supp_420_423_CI missing message check for task 13010 issue4 
 * Solution: 
 *********************************IVI*********************************
 * Update base info as oppline
 * IF CIVI.reorderfrm is NULL, Set CIVI.msgsource='10000162'
 * IF CIVI.reorderfrm is NOT NULL, set CIVI.reorderfrm = '1950-01-01'
 *********************************IVI*********************************
 *
 *********************************IVSR*********************************
 * Running IVI
 *********************************IVSR********************************* 
*/
--- As a result of issue reported IN Task 13010   
		
		SELECT COUNT("VersionNum") INTO rowcount 
		FROM   "OILM" 
		WHERE  "VersionNum" >= CASE 
									WHEN :cmpVersionNum >= 1000000
									THEN '08.80.000.00'
									ELSE '8.80.000.00' 
									END
				  AND "VersionNum" <= CASE 
									WHEN :cmpVersionNum >= 1000000
									THEN '08.80.228.09'
									ELSE '8.80.228.09' 
									END;
			
		IF :rowcount > 0 then 
			SELECT count(t0."DocEntry") INTO rownum
			FROM   "CPI1" t0, "CPI1" t1 
			WHERE  t0."DocEntry" = t1."DocEntry" 
				AND t0."LineNum" = t1."CiOppLineN" 
				AND ( t0."BaseType" = ( -1 ) OR t0."BaseType" IS NULL ) 
				AND (t1."BaseType" IS NOT NULL AND t1."BaseType" > ( -1 ) ); 
					
			IF :rownum > 0 then 
				DB_Impacted := 'Y'; 
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 420.');
				
			END if;

            ---CPV1
			SELECT count(t0."DocEntry") INTO rownum
			FROM   "CPV1" t0, "CPV1" t1 
			WHERE  t0."DocEntry" = t1."DocEntry" 
				AND t0."LineNum" = t1."CiOppLineN" 
				AND ( t0."BaseType" = ( -1 ) OR t0."BaseType" IS NULL ) 
				AND (t1."BaseType" IS NOT NULL AND t1."BaseType" > ( -1 ) ); 
					
			IF :rownum > 0 then 
				DB_Impacted := 'Y'; 
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 421.');
			END if;

			--- CSI1															
			SELECT count(t0."DocEntry") INTO rownum
			FROM   "CSI1" t0, "CSI1" t1 
			WHERE  t0."DocEntry" = t1."DocEntry" 
				AND t0."LineNum" = t1."CiOppLineN" 
				AND (t0."BaseType" = ( -1 ) OR t0."BaseType" IS NULL) 
				AND (t1."BaseType" IS NOT NULL AND t1."BaseType" > ( -1 ));

			IF :rownum > 0 then 
				DB_Impacted := 'Y'; 
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 422.');
			END if;

			--- [CSV1]		
			SELECT count(t0."DocEntry") INTO rownum
			FROM   "CSV1" t0, "CSV1" t1 
			WHERE  t0."DocEntry" = t1."DocEntry" 
				AND t0."LineNum" = t1."CiOppLineN" 
				AND (t0."BaseType" = ( -1 ) OR t0."BaseType" IS NULL ) 
				AND (t1."BaseType" IS NOT NULL AND t1."BaseType" > ( -1 ) ); 

			IF :rownum > 0 then 
				DB_Impacted := 'Y'; 
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'Db is impacted, please contact SAP support. ErrorCode = 423.');
				
			END if; 
		END if; --rowcount > 0
/**
 * Supp_425_Missing documents IN OILM
 * Solution: 
 *********************************IVI*********************************
 * Rebuild the UILM table
 *********************************IVI*********************************
 *
 *********************************IVSR*********************************
 * Running IVI
 *********************************IVSR*********************************
*/
		select COUNT(T0."MessageID") INTO rownum 
		from "OILM" T0
		left join "OILM" T1	on T0."BaseAbsEnt" = T1."DocEntry" 
		                    and T0."BaseType" = T1."TransType"
		where (T0."BaseAbsEnt" > 0 or T0."BaseType" > 0) 
		    and T0."BaseType" not in (17,22,23,202,67,540000006,1250000001)
			AND T0."AccumType" = 1 
			AND T0."ActionType" not in (3,4)
			AND NOT (T0."BaseType"=18 AND T0."BaseAbsEnt" IN (select "DocEntry" from "OPCH" where "UpdInvnt" = 'O')) --AP Res. Inv -> XXX
			AND NOT (T0."BaseType"=13 AND T0."BaseAbsEnt" IN (select "DocEntry" from "OINV" where "UpdInvnt" = 'C')) --AR Res. Inv -> XXX
			AND NOT (T0."BaseType"=18 AND T0."TransType" = 20) --Res. Inv -> GRPO (but Res. Inv was deleted)
			AND NOT (T0."BaseType"=13 AND T0."TransType" = 15) --Res. Inv -> DLN (but Res. Inv was deleted) 
			AND NOT (T0."BaseType"=163 AND T0."TransType" = 20) --Res. Inv -> CI -> GRPO (GRPO is calculated as not based within IVI/IVSR)  
			AND NOT (T0."BaseType"=165 AND T0."TransType" = 15) --Res. Inv -> CI -> DLN (DLN is calculated as not based within IVI/IVSR)   
			AND NOT (T0."BaseType" in (67,20,59) AND T0."TransType" = 67) -- WTR is handled as non based doc.
			AND NOT (T0."BaseType"=234000031 AND T0."TransType" = 16) -- RRR->RDN
			AND NOT (T0."BaseType"=234000031 AND T0."TransType" = 14) -- RRR->RIN
			AND NOT (T0."BaseType"=234000032 AND T0."TransType" = 19) -- RRR->RPC
			AND NOT (T0."BaseType"=234000032 AND T0."TransType" = 21) -- RRR->RPD
			AND t0."TransType" <> 202 --Production order create/update - basetype=1 by known bug
			AND (IFNULL(T1."DocEntry",0) = 0 or IFNULL(T1."TransType",0) = 0);
			
		IF :rownum > 0 then 
			DB_Impacted := 'Y'; 
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'DB is impacted, please contact SAP support. ErrorCode = 425.');
		END if;

/**  
 * Supp_426_Null TotalLC IN OILM for lANDed cost
 * As a result of issue reported IN Task 15365 for 8.8 AND CSN 2259123 2010 for 8.81
 * Solution: 
 *********************************IVI*********************************
 * Rebuild the UILM table
 *********************************IVI*********************************
 *
 *********************************IVSR*********************************
 * Running IVI
 *********************************IVSR*********************************
*/
		
		SELECT count("MessageID") into rownum 
		from oilm	
		where "TransType" = 69 
		      and ifnull("TotalLC",0) = 0 
			  and ifnull("VersionNum", '') < CASE 
													WHEN :cmpVersionNum >= 1000000
													THEN '08.00.000.00'
													ELSE '8.00.000.00' 
													END;
			IF :rownum > 0 then
			DB_Impacted := 'Y';
			
			INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 426.]]]');
		
		end if;
		
--Special Treatment for Supp_416_419 as IVI/IVSR has different condition









--- Assembly BoM subline numbers were wrongly updated for several scenarios.  
--- Assembly BoM qty could be wrongly calculated.  
--- As a result of issue reported in Task 13010 
		SELECT COUNT(1) INTO rowCount FROM
			   (SELECT "VersionNum"
				FROM "OILM"
				WHERE "VersionNum" >= CASE 
										WHEN :cmpVersionNum >= 1000000
										THEN '08.80.000.00'
										ELSE '8.80.000.00' 
										END
					AND "VersionNum" <= CASE 
											WHEN :cmpVersionNum >= 1000000
											THEN '08.80.228.09'
											ELSE '8.80.228.09' 
											END --- DB upgraded to 8.8 befor PL 10  
			   );
	
			SELECT COUNT(1) INTO count1 FROM	
			   (SELECT "AbsEntry"
			    FROM "CIVI"
				WHERE "ReorderFrm" <> :firstTranstionDate--NOT FROM the first date 
			   );
				
			SELECT COUNT(1) INTO count2 FROM
			   (SELECT 1
			    FROM "CIVI"
				WHERE "MsgSource" = '10000162'--NOT BY uilm 
			   );
			
			IF :rowCount > 0 
			   AND ((:stockissue88 = 'Y' AND count1 > 0)
					 OR stockissue88 = 'N' AND count2 = 0)
			THEN
				SELECT COUNT(1) INTO rowCount
				FROM CSI1 t0 
					INNER JOIN OCSI t1 
					      ON t0."DocEntry" = t1."DocEntry"
					INNER JOIN OITM t2 
						  ON t0."ItemCode" = t2."ItemCode" 
				WHERE IFNULL(t1."VersionNum", '') < CASE 
														WHEN :cmpVersionNum >= 1000000
														THEN '08.80.000.00'
														ELSE '8.80.000.00' 
														END 
					  AND t1."Instance" = 0 ---Document was NOT created via year transfer    
					  AND t2."TreeType" = 'A'; ---Item is assembly BOM  
				
				IF :rowCount > 0 THEN
				
				DB_Impacted := 'Y'; 
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 416.]]]');
				
			END if;
---AR Correction Reversal  
				---AR Correction Reversal 
			SELECT COUNT(t0."DocEntry") INTO rownum1 FROM   "CSV1" t0 
				INNER JOIN "OCSV" t1 ON t0."DocEntry" = t1."DocEntry" 
				INNER JOIN "OITM" t2 ON t0."ItemCode" = t2."ItemCode"
			WHERE IFNULL(t1."VersionNum", '') < CASE 
														WHEN :cmpVersionNum >= 1000000
														THEN '08.80.000.00'
														ELSE '8.80.000.00' 
														END
				AND t1."Instance" = 0     
				AND t2."TreeType" = 'A';  
			IF :rownum1 > 0 then 
				DB_Impacted := 'Y'; 
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 417.]]]');
				
			END if;
			--- AP AND AR Reserve Corr. Invoices AND Db was upgraded to 8.8 before PL10   
			--- AR Reserve Correction Invocie  			
			SELECT COUNT(t0."DocEntry") into rownum1 FROM   "CSI1" t0 
				INNER JOIN "OCSI" t1 ON t0."DocEntry" = t1."DocEntry" 
				INNER JOIN "OITM" t2 ON t0."ItemCode" = t2."ItemCode"
			WHERE IFNULL(t1."VersionNum", '') < CASE 
														WHEN :cmpVersionNum >= 1000000
														THEN '08.80.000.00'
														ELSE '8.80.000.00' 
														END
				AND t1."isIns" = 'Y' 
				AND ( t2."InvntItem" = 'Y' OR t2."TreeType" = 'A' ) 
				AND t1."Instance" = 0 ; 

			IF :rownum1 > 0 then 
				DB_Impacted := 'Y'; 
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 418.]]]');
				
			END if;
			--- AP Reserve Correction Invocie
			SELECT COUNT(t0."DocEntry") into rownum1 FROM   "CPI1" t0 
				INNER JOIN "OCPI" t1 ON t0."DocEntry" = t1."DocEntry" 
				INNER JOIN "OITM" t2 ON t0."ItemCode" = t2."ItemCode"
			WHERE  IFNULL(t1."VersionNum", '') < CASE 
														WHEN :cmpVersionNum >= 1000000
														THEN '08.80.000.00'
														ELSE '8.80.000.00' 
														END 
				AND t1."isIns" = 'Y' 
				AND ( t2."InvntItem" = 'Y' OR t2."TreeType" = 'A' ) 
				AND t1."Instance" = 0 ; 

			IF :rownum1 > 0 then 
				DB_Impacted := 'Y'; 
				
				INSERT INTO "TMP_IVSR_SupInterReq_Results" VALUES (N'TODO[[[db is impacted, please contact SAP support. ErrorCode = 419.]]]');
				
			END if; 
		END if; --416_419
	END IF; --Recalc_By_OILM
	
	DELETE FROM "TMP_IVSR_SupInterReq_TotalLcZeroPQNotZero";
	SELECT * FROM "TMP_IVSR_SupInterReq_Results";
	DELETE FROM "TMP_IVSR_SupInterReq_Results";
END;