/*
	Detection code for Note 1461889
	version 0.4 
	released 11.5.2015

*/

IF object_id('[tempdb]..##Fields') IS NOT NULL DROP TABLE [##Fields]

CREATE TABLE ##Fields
(
    FieldName VARCHAR(50)
);

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

DECLARE  @QueryTempl NVARCHAR(MAX)
DECLARE  @sql NVARCHAR(MAX)
DECLARE  @ret NVARCHAR(1)

SET @QueryTempl =
N'
DECLARE @FieldName varchar(50)
DECLARE @num int
SET @num = 0

DECLARE zX CURSOR SCROLL
FOR SELECT FieldName FROM ##Fields

/*first record*/
OPEN zX
FETCH NEXT FROM zX INTO @FieldName

IF object_id(''[tempdb]..##TmpTable'') IS NOT NULL DROP TABLE [##TmpTable]

/*for */
WHILE @@FETCH_STATUS = 0
BEGIN 
DECLARE  @sql1 NVARCHAR(MAX)
DECLARE  @sql2 NVARCHAR(MAX) 

SET @sql1 =
''
Select ''$ErrorMsg'' as ''''Scenarios'''', $DispFields1, ''''$FieldName same as Inventory Account       '''' as ''''Details''''
INTO ##TmpTable
from $Table
where (isnull($InventoryAct, '''''''') <> '''''''' and $InventoryAct= $FieldName)

INSERT INTO ##TmpTable
Select ''$ErrorMsg'' as ''''Scenarios'''', $DispFields1, ''''$FieldName same as Sales Return Account'''' as ''''Details''''
from $Table
where (isnull($SalesReturnAct, '''''''') <> '''''''' and $SalesReturnAct= $FieldName)
'' 
SET @sql2 =
''
INSERT INTO ##TmpTable
Select ''$ErrorMsg'' as ''''Scenarios'''', $DispFields1, ''''$FieldName same as Inventory Account'''' as ''''Details''''
from $Table
where (isnull($InventoryAct, '''''''') <> '''''''' and $InventoryAct= $FieldName)

INSERT INTO ##TmpTable
Select ''$ErrorMsg'' as ''''Scenarios'''', $DispFields1, ''''$FieldName same as Sales Return Account'''' as ''''Details''''
from $Table
where (isnull($SalesReturnAct, '''''''') <> '''''''' and $SalesReturnAct= $FieldName)
'' 

IF exists (select tab.name, col.name from syscolumns col join sysobjects tab on col.id = tab.id
where tab.xtype = ''u''
and ltrim(rtrim(tab.name)) = ltrim(rtrim(''$Table''))
and ltrim(rtrim(col.name)) = ltrim(rtrim(@FieldName))
) 
BEGIN
IF(@num = 0)
BEGIN
SET @sql1 = REPLACE(@sql1, ''$FieldName'', @FieldName)
EXEC(@sql1)
SET @num = 1
END
ELSE
BEGIN 
SET @sql2 = REPLACE(@sql2, ''$FieldName'', @FieldName)
EXEC(@sql2)
END 
END

FETCH NEXT FROM zX INTO @FieldName 
END
CLOSE zX
DEALLOCATE zX

SELECT DISTINCT * from ##TmpTable order by Scenarios, $DispFields1, Details

SET @ret = N''0''
IF ( select top 1 1 from ##TmpTable ) is not null
BEGIN
SET @ret = N''1''
PRINT $ErrorMsg 
END

DROP TABLE ##TmpTable
'

--OITW
SET @sql = Replace(@QueryTempl,'$DispFields1','ItemCode,WhsCode')
SET @sql = Replace(@sql,'$Table','OITW')
SET @sql = Replace(@sql,'$ErrorMsg','''inventory account is wrongly used in profit/loss or offset account for item account managed by Item Level''')
SET @sql = Replace(@sql,'$InventoryAct','BalInvntAc')
SET @sql = Replace(@sql,'$SalesReturnAct','ReturnAc')

exec sp_executesql @sql, N'@ret nvarchar(1) OUTPUT', @ret OUTPUT
/*IF ('1' = @ret)
BEGIN
SET @CntScenarios = @CntScenarios + 1
END */

--OWHS
SET @sql = Replace(@QueryTempl,'$DispFields1','WhsCode')
SET @sql = Replace(@sql,'$Table','OWHS')
SET @sql = Replace(@sql,'$ErrorMsg','''inventory account is wrongly used in profit/loss or offset account for item account managed by Warehouse''')
SET @sql = Replace(@sql,'$InventoryAct','BalInvntAc')
SET @sql = Replace(@sql,'$SalesReturnAct','ReturnAc')

exec sp_executesql @sql, N'@ret nvarchar(1) OUTPUT', @ret OUTPUT
/*IF ('1' = @ret)
BEGIN
SET @CntScenarios = @CntScenarios + 1
END */

--OITB
SET @sql = Replace(@QueryTempl,'$DispFields1','ItmsGrpNam')
SET @sql = Replace(@sql,'$Table','OITB')
SET @sql = Replace(@sql,'$ErrorMsg','''inventory account is wrongly used in profit/loss or offset account for item account managed by Item Groups''')
SET @sql = Replace(@sql,'$InventoryAct','BalInvntAc')
SET @sql = Replace(@sql,'$SalesReturnAct','ReturnAc')

exec sp_executesql @sql, N'@ret nvarchar(1) OUTPUT', @ret OUTPUT
/*IF ('1' = @ret)
BEGIN
SET @CntScenarios = @CntScenarios + 1
END */

--OGAR - Advanced GL Account Determination
--available from 9.0 versions only
DECLARE @SQLString nvarchar(3000); 
DECLARE @ParmDef nvarchar(100);

--variable to store info if the DB is using Advanced GL Determination 
--by default it is set to Not using 
--via Dynamic SQL it is set only on those database 9.0 and higher
DECLARE @newAcctActive char(1)  
SET @newAcctActive = 'N'

SET @SQLString = N'
SELECT @newAcctDe = NewAcctDe  FROM OADM
'
SET @ParmDef=N'@newAcctDe char(1) OUTPUT'

DECLARE @sboVer integer
SELECT @sboVer=Version FROM CINF
IF @sboVer >= 900000
BEGIN
	EXECUTE sp_executesql	@SQLString,  
							@ParmDef, 
							@newAcctDe = @newAcctActive OUTPUT ;


--in case DB is 9.0 or higher and Advanced GL determination are activated following part will be executed
IF (SELECT Version from cinf) >= 900000 AND @newAcctActive = 'Y'
BEGIN

	TRUNCATE TABLE ##Fields
	INSERT INTO ##Fields VALUES  ('DfltExpn'         )
	INSERT INTO ##Fields VALUES  ('DfltIncom'         )
	INSERT INTO ##Fields VALUES  ('ExmptIncom'         )
	INSERT INTO ##Fields VALUES  ('COGM_Act'         )
	INSERT INTO ##Fields VALUES  ('AlocCstAct'         )
	INSERT INTO ##Fields VALUES  ('VariancAct'         )
	INSERT INTO ##Fields VALUES  ('PricDifAct'         )
	INSERT INTO ##Fields VALUES  ('NegStckAct'         )
	INSERT INTO ##Fields VALUES  ('DfltLoss'         )
	INSERT INTO ##Fields VALUES  ('DfltProfit'         )
	INSERT INTO ##Fields VALUES  ('ECIncome'         )
	INSERT INTO ##Fields VALUES  ('ECExepnses'         )
	INSERT INTO ##Fields VALUES  ('ForgnIncm'         )
	INSERT INTO ##Fields VALUES  ('ForgnExpn'         )
	INSERT INTO ##Fields VALUES  ('PurchseAct'         )
	INSERT INTO ##Fields VALUES  ('PaReturnAc'         )
	INSERT INTO ##Fields VALUES  ('PaOffsetAc'         )
	INSERT INTO ##Fields VALUES  ('ExDiffAct'         )
	INSERT INTO ##Fields VALUES  ('BalanceAct'         )
	INSERT INTO ##Fields VALUES  ('DecresGlAc'         )
	INSERT INTO ##Fields VALUES  ('IncresGlAc'         )
	INSERT INTO ##Fields VALUES  ('WipAcct'         )
	INSERT INTO ##Fields VALUES  ('WipVarAcct'         )
	INSERT INTO ##Fields VALUES  ('WipOffset'         )
	INSERT INTO ##Fields VALUES  ('StockOffst'         )
	INSERT INTO ##Fields VALUES  ('StockRvAct'         )
	INSERT INTO ##Fields VALUES  ('StkRvOfAct'         )
	INSERT INTO ##Fields VALUES  ('CostRevAct'         )
	INSERT INTO ##Fields VALUES  ('CostOffAct'         )
	INSERT INTO ##Fields VALUES  ('ExpClrAct'         )
	INSERT INTO ##Fields VALUES  ('ExpOfstAct'         )
	INSERT INTO ##Fields VALUES  ('StkInTnAct'         )
	INSERT INTO ##Fields VALUES  ('ShpdGdsAct'         )
	INSERT INTO ##Fields VALUES  ('VatRevAct'         )
	INSERT INTO ##Fields VALUES  ('ARCMAct'         )
	INSERT INTO ##Fields VALUES  ('APCMAct'         )
	INSERT INTO ##Fields VALUES  ('ARCMExpAct'         )
	INSERT INTO ##Fields VALUES  ('ARCMFrnAct'         )
	INSERT INTO ##Fields VALUES  ('APCMFrnAct'         )
	INSERT INTO ##Fields VALUES  ('ARCMEUAct'         )
	INSERT INTO ##Fields VALUES  ('APCMEUAct'         )
	INSERT INTO ##Fields VALUES  ('PurBalAct'         )
	INSERT INTO ##Fields VALUES  ('WhICenAct'         )
	INSERT INTO ##Fields VALUES  ('WhOCenAct'         )

	--OGAR
	SET @sql = Replace(@QueryTempl,'$DispFields1','RuleCode, FinancYear')
	SET @sql = Replace(@sql,'$Table','OGAR')
	SET @sql = Replace(@sql,'$ErrorMsg','''inventory account is wrongly used in profit/loss or offset account in Advanced G/L Determination rule''')
	SET @sql = Replace(@sql,'$InventoryAct','StockAct')
	SET @sql = Replace(@sql,'$SalesReturnAct','RturnngAct')

	exec sp_executesql @sql, N'@ret nvarchar(1) OUTPUT', @ret OUTPUT
END
/*IF ('1' = @ret)
BEGIN
SET @CntScenarios = @CntScenarios + 1
END */

--OACP
TRUNCATE TABLE ##Fields
INSERT INTO ##Fields VALUES ('wipvaracct'         )
INSERT INTO ##Fields VALUES ('decresglac'         )
INSERT INTO ##Fields VALUES ('incresglac'         )
INSERT INTO ##Fields VALUES ('dfltprofit'         )
INSERT INTO ##Fields VALUES ('dfltloss '          )
INSERT INTO ##Fields VALUES ('negstckact'         )
INSERT INTO ##Fields VALUES ('stkintnact'         )
INSERT INTO ##Fields VALUES ('exdiffact '         )
INSERT INTO ##Fields VALUES ('balanceact'         )
INSERT INTO ##Fields VALUES ('cogm_act '          )
INSERT INTO ##Fields VALUES ('variancact'         )
INSERT INTO ##Fields VALUES ('pricdifact'         )
INSERT INTO ##Fields VALUES ('aloccstact'         )
INSERT INTO ##Fields VALUES ('expclract '         )
INSERT INTO ##Fields VALUES ('expvaract '         )

SET @sql = Replace(@QueryTempl,'$DispFields1','FinancYear')
SET @sql = Replace(@sql,'$Table','OACP')
SET @sql = Replace(@sql,'$ErrorMsg','''inventory account is wrongly used in profit/loss or offset account in G/L Account Determination''')
SET @sql = Replace(@sql,'$InventoryAct','stockact')
SET @sql = Replace(@sql,'$SalesReturnAct','rturnngact')

exec sp_executesql @sql, N'@ret nvarchar(1) OUTPUT', @ret OUTPUT
/*IF ('1' = @ret)
BEGIN
SET @CntScenarios = @CntScenarios + 1
END */

DROP TABLE ##fields
end