/*
	HANA Detection code for Note 1461889
	version 0.4 
	released 11.5.2015
	--results per each detection limited to 10,000 rows - if necessary please increase in procedure call "Note_1461889" last input 
	--if necessary please increase in procedure call "Note_1461889" last input parameter topNum
*/


CREATE PROCEDURE "Note_1461889_CreateTables"()
LANGUAGE sqlscript AS
	isExist integer;
BEGIN


SELECT COUNT(1) 
INTO isExist 
FROM SYS.M_TABLES  WHERE "SCHEMA_NAME" = CURRENT_SCHEMA AND "TABLE_NAME" = N'UserInterAction_Fields';
	
	IF :isExist > 0 THEN
		DROP TABLE "UserInterAction_Fields";
	END IF;
	
	CREATE COLUMN TABLE "UserInterAction_Fields" 
		(
		  "FieldName"	VARCHAR(50)
		);	

SELECT COUNT(1) 
INTO isExist 
FROM SYS.M_TABLES  WHERE "SCHEMA_NAME" = CURRENT_SCHEMA AND "TABLE_NAME" = N'UserInterAction_TmpTable';
	
	IF :isExist > 0 THEN
		DROP TABLE "UserInterAction_TmpTable";
	END IF;
	
	CREATE COLUMN TABLE "UserInterAction_TmpTable" 
		(
		    "Scenarios"		VARCHAR(200),
		    "Result"	VARCHAR(100),
		    "Result_2" VARCHAR(100),
		    "Details"		VARCHAR(200)
    	);

END;

CALL "Note_1461889_CreateTables";


	INSERT INTO "UserInterAction_Fields" VALUES ('"SaleCostAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"TransferAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"RevenuesAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"VarianceAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"DecreasAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"IncreasAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExpensesAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"EURevenuAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"EUExpensAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"FrRevenuAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"FrExpensAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExmptIncom"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"PriceDifAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExchangeAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"BalanceAcc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"PurchaseAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"PurchOfsAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"PAReturnAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ShpdGdsAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"VatRevAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"DecresGlAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"IncresGlAc"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"StokRvlAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"StkOffsAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"WipAcct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"WipVarAcct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"CostRvlAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"CstOffsAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExpClrAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExpOfstAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMFrnAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMEUAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMExpAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMFrnAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMEUAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"RevRetAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"NegStckAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"StkInTnAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"PurBalAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"WhICenAct"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"WhOCenAct"');




CREATE PROCEDURE "Note_1461889"(IN ErrorMsgIN nvarchar(200),
								IN DispFields1IN nvarchar(20),
								IN DispFields2IN nvarchar(20),
								IN InventoryActIN nvarchar(20), 
								IN SalesReturnActIN nvarchar(20),
								IN TableNameIN varchar(4),
								IN topNum integer)
	LANGUAGE sqlscript AS 
	QueryTempl nvarchar(4096);
	query nvarchar(4096);
	ret nvarchar(1);
	retFinal nvarchar(1);
	FieldName varchar(50);
	TableName varchar(4);
	isExist integer;
	DispFields1 nvarchar(20);
	DispFields2 nvarchar(20);
	InventoryAct nvarchar(20);
	SalesReturnAct nvarchar(20);
	ErrorMsg nvarchar(600);
	CURSOR zx FOR
		SELECT "FieldName" FROM "UserInterAction_Fields";
		
	BEGIN
	
	
	DispFields1 := :DispFields1IN;
	DispFields2 := :DispFields2IN;
	InventoryAct := :InventoryActIN;
	SalesReturnAct := :SalesReturnActIN;
	TableName := :TableNameIN;
	ErrorMsg := :ErrorMsgIN;
	
	OPEN zX;
	FETCH zX INTO FieldName;
		
	WHILE NOT zX::NOTFOUND DO
	

 			query := 	'
 			INSERT INTO "UserInterAction_TmpTable"
 			Select top 100 '''||:ErrorMsg||''' as "Scenarios", 

'||:DispFields1||','||:DispFields2||', '''||:FieldName||' same as Inventory Account'' as "Details"
			from '||:TableName||'
			where	(IFNULL('||:InventoryAct||', '''') <> '''' and 

'||:InventoryAct||' = '||:FieldName||')';
			--SELECT :query FROM DUMMY;
   			EXEC(:query);
			
			query := 	'
 			INSERT INTO "UserInterAction_TmpTable"
 			Select top 100 '''||:ErrorMsg||''' as "Scenarios", 

'||:DispFields1||','||:DispFields2||', '''||:FieldName||' same as Inventory Account'' as "Details"
			from '||:TableName||'
			where	(IFNULL('||:SalesReturnAct||', '''') <> '''' and 

'||:SalesReturnAct||' = '||:FieldName||')';
			--SELECT :query FROM DUMMY;
   			EXEC(:query);



		FETCH zX INTO FieldName;
	END WHILE;
	CLOSE zX;
	
	ret := N'0';
	
	--EXECUTE IMMEDIATE 'SELECT TOP 100 * FROM "UserInterAction_TmpTable"';
	
	--select COUNT(1) INTO isExist FROM "UserInterAction_TmpTable";
	
	--IF ( :isExist > 0) THEN
	--	ret := N'1';
		--EXECUTE IMMEDIATE 'SELECT ''inventory account is wrongly used in profit/loss or 


	--END IF;

	--DELETE FROM "UserInterAction_TmpTable";
	
END;


CREATE PROCEDURE "Note_1461889_IsActive"
										(OUT NewAcctDeOUT char(1))
LANGUAGE sqlscript AS
	
	
BEGIN

	SELECT "NewAcctDe" INTO NewAcctDeOUT FROM "OADM";

END;



CREATE PROCEDURE "Note_1461889_Execute"()
LANGUAGE sqlscript AS

	b1Version integer;
	NewAcctDe char(1);
BEGIN
	
	NewAcctDe := 'N';
	SELECT "Version" INTO b1Version FROM CINF;
	

--OITW
CALL  "Note_1461889"('inventory account is wrongly used in profit/loss or offset account for item account managed by Item Level', 
					 '"ItemCode"',
					 '"WhsCode"',
					 '"BalInvntAc"', 
					 '"ReturnAc"', 
					 'OITW',
					 '10000');

--OWHS
CALL  "Note_1461889"('inventory account is wrongly used in profit/loss or offset account for item account managed by Warehouse', 
					 '"WhsCode"',
					 '''-''',
					 '"BalInvntAc"', 
					 '"ReturnAc"', 
					 'OWHS',
					 '10000');

--OITB
CALL  "Note_1461889"('inventory account is wrongly used in profit/loss or offset account for item account managed by Item Group', 
					  '"ItmsGrpNam"',
					  '''-''',
					  '"BalInvntAc"', 
					  '"ReturnAc"', 
					  'OITB',
             		  '10000');
             		  
--OACP
DELETE FROM "UserInterAction_Fields" WHERE 1 = 1;
INSERT INTO "UserInterAction_Fields" VALUES ('"WipVarAcct"');
INSERT INTO "UserInterAction_Fields" VALUES ('"DecresGlAc"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"IncresGlAc"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"DfltProfit"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"DfltLoss"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"NegStckAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"StkInTnAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"ExDiffAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"BalanceAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"COGM_Act"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"VariancAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"PricDifAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"AlocCstAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"ExpClrAct"');  
INSERT INTO "UserInterAction_Fields" VALUES ('"ExpVarAct"');  
             		  
CALL  "Note_1461889"('inventory account is wrongly used in profit/loss or offset account in G/L Account Determination', 
					  '"FinancYear"',
					  '''-''',
					  '"StockAct"', 
					  '"RturnngAct"', 
					  'OACP',
             		  '10000');  		

IF :b1Version >= 900000 
THEN
		CALL "Note_1461889_IsActive"(NewAcctDe);
END IF;

--OGAR
IF :b1Version >= 900000 AND :NewAcctDe = 'Y' 

THEN        		    
            		  

	DELETE FROM "UserInterAction_Fields" WHERE 1 = 1;
	INSERT INTO "UserInterAction_Fields" VALUES ('"DfltExpn"');
	INSERT INTO "UserInterAction_Fields" VALUES ('"DfltIncom"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"COGM_Act"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"AlocCstAct"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"VariancAct"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"NegStckAct"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"DfltLoss"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"ECIncome"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"ECExepnses"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"ForgnIncm"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"ForgnExpn"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"PurchseAct"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"PaReturnAc"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"PaOffsetAc"');  
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExDiffAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"BalanceAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"DecresGlAc"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"IncresGlAc"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"WipAcct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"WipVarAcct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"WipOffset"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"StockOffst"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"StockRvAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"StkRvOfAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"CostRevAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"CostOffAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExpClrAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ExpOfstAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"StkInTnAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ShpdGdsAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"VatRevAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMExpAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMFrnAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMFrnAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"ARCMEUAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"APCMEUAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"PurBalAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"WhICenAct"'); 
	INSERT INTO "UserInterAction_Fields" VALUES ('"WhOCenAct"'); 
	
	CALL  "Note_1461889"('inventory account is wrongly used in profit/loss or offset account in Advanced G/L Determination rule', 
						  '"RuleCode"',
						  '"FinancYear"',
						  '"StockAct"', 
						  '"RturnngAct"', 
						  'OGAR',
	             		  '10000'); 
END IF;             		  

--Get results
SELECT DISTINCT * 
	FROM "UserInterAction_TmpTable"
	ORDER BY "Scenarios", "Result","Result_2", "Details";

END;      

CALL "Note_1461889_Execute";   		  
												 
CREATE PROCEDURE "Note_1461889_DropTables"()
LANGUAGE sqlscript AS
	isExist integer;
BEGIN


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


SELECT COUNT(1) INTO isExist FROM SYS.M_TABLES  
				WHERE "SCHEMA_NAME" = CURRENT_SCHEMA AND "TABLE_NAME" = N'B1ObjectsToTest';
				
	IF :isExist > 0 THEN
		DROP TABLE "UserInterAction_TmpTable";
	END IF;
	

END;
 
DELETE FROM "UserInterAction_TmpTable";
CALL "Note_1461889_DropTables";

DROP PROCEDURE "Note_1461889";
DROP PROCEDURE "Note_1461889_CreateTables";
DROP PROCEDURE "Note_1461889_DropTables";
DROP PROCEDURE "Note_1461889_IsActive";
DROP PROCEDURE "Note_1461889_Execute";