SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc uspImportSAPCodesTEST as
declare @rc int, @LE_ErrCode int, @LE_ErrMsg nvarchar(4000)
begin tran ImportSapCodes
--truncate table tblTreeviewTEST
--Compare the incoming codes with the Codes already existing in the
--system and do one of the following:
--1.Insert SAP codes which exist on the Import File but Not on this system
--2.Flag as active thoses codes which are flagged as deleted but are now in the Import file
--3.Flag as Deleted those codes which are no longer on the Import file but are in the system as Active
/*
---------------------------------------------
| SAPIMPORT | TREEVIEW | FLAG | ACTION |
=============================================
| | | | |
Scenario 1| Yes | No | n/a | Insert |
=============================================
| Yes | Yes | 0 | No action|
Scenario 2| Yes | Yes | 1 | 0 |
=============================================
| No | Yes | 0 | 1 |
Scenario 3| No | Yes | 1 | No action|
---------------------------------------------
*/
/*
=============================================================================
Scenario1
=============================================================================
level 1
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct 0, SAP.Customer
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item
where L1Item is null
Set @rc = @@error
print @rc
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Customer'
goto Err
End
print 'End of Scenario1 Level1'
/*
=============================================================================
level 2
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct TV1.TV_Id, SAP.[Work Type]
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item
inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Customer]
where L2Item is null
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Work Type'
goto Err
End
print 'End of Scenario1 Level2'
/*
=============================================================================
level 3
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct TV1.TV_Id, SAP.[Work Sub Type]
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item
inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Work Type]
inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
TV2.tv_item = SAP.customer
where L3Item is null
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Work Sub Type'
goto Err
End
print 'End of Scenario1 Level3'
/*
=============================================================================
level 4
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct TV1.TV_Id, SAP.[Service Order / Network]
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item
inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Work Sub Type]
inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
TV2.tv_item = SAP.[Work Type]
inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
TV3.tv_item = SAP.[Customer]
where L4Item is null
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Service Order/Network'
goto Err
End
print 'End of Scenario1 Level4'
/*
=============================================================================
level 5
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct TV1.TV_Id, SAP.[Description]
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item and
SAP.[description] = L5Item
inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Service Order / Network]
inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
TV2.tv_item = SAP.[Work Sub Type]
inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
TV3.tv_item = SAP.[Work Type]
inner join tblTreeviewTEST TV4 on TV4.TV_Id = TV3.TV_TV_Id and
TV4.tv_item = SAP.[Customer]
where L5Item is null
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Description'
goto Err
End
print 'End of Scenario1 Level5'
/*
=============================================================================
level 6
=============================================================================
*/
insert into tblTreeviewTEST (TV_TV_Id, TV_Item)
select distinct TV1.TV_Id, SAP.[Code]
from tblSAPCodesImport as SAP
left join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item and
SAP.[description] = L5Item and
SAP.[Code] = L6Item
inner join tblTreeviewTEST TV1 on TV1.tv_item = SAP.[Description]
inner join tblTreeviewTEST TV2 on TV2.TV_Id = TV1.TV_TV_Id and
TV2.tv_item = SAP.[Service Order / Network]
inner join tblTreeviewTEST TV3 on TV3.TV_Id = TV2.TV_TV_Id and
TV3.tv_item = SAP.[Work Sub Type]
inner join tblTreeviewTEST TV4 on TV4.TV_Id = TV3.TV_TV_Id and
TV4.tv_item = SAP.[Work Type]
inner join tblTreeviewTEST TV5 on TV5.TV_Id = TV4.TV_TV_Id and
TV5.tv_item = SAP.[Customer]
where L6Item is null
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Inserting new Code'
goto Err
End
print 'End of Scenario1 Level6'
/*
=============================================================================
Scenario 2
=============================================================================
level 1
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item
inner join tblTreeviewTEST on
(TV_Id1 = TV_Id)
-- TV_Id2 = TV_Id or
-- TV_Id3 = TV_Id or
-- TV_Id4 = TV_Id or
-- TV_Id5 = TV_Id or
-- TV_Id6 = TV_Id )
where TV_ClosedF1 = 1
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Customer'
goto Err
End
print 'End of Scenario2 Level1'
/*
=============================================================================
level 2
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item
inner join tblTreeviewTEST on
(TV_Id2 = TV_Id )
-- TV_Id3 = TV_Id or
-- TV_Id4 = TV_Id or
-- TV_Id5 = TV_Id or
-- TV_Id6 = TV_Id )
where TV_ClosedF2 = 1
set @rc = @@Error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Work Type'
goto Err
End
print 'End of Scenario2 Level2'
/*
=============================================================================
level 3
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item
inner join tblTreeviewTEST on
(TV_Id3 = TV_Id )
-- TV_Id4 = TV_Id or
-- TV_Id5 = TV_Id or
-- TV_Id6 = TV_Id )
where TV_ClosedF3 = 1
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Work Sub Type'
goto Err
End
print 'End of Scenario2 Level3'
/*
=============================================================================
level 4
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAp.[Service Order / Network] = L4Item
inner join tblTreeviewTEST on
(TV_Id4 = TV_Id )
-- TV_Id5 = TV_Id or
-- TV_Id6 = TV_Id )
where TV_ClosedF4 = 1
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Service Order/Network'
goto Err
End
print 'End of Scenario2 Level4'
/*
=============================================================================
level 5
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAp.[Service Order / Network] = L4Item and
SAP.[Description] = L5Item
inner join tblTreeviewTEST on
(TV_Id5 = TV_Id )
-- TV_Id6 = TV_Id )
where TV_ClosedF5 = 1
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging description '
goto Err
End
print 'End of Scenario2 Level5'
/*
=============================================================================
level 6
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 0
from tblSAPCodesImport as SAP
inner join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAp.[Service Order / Network] = L4Item and
SAP.[Description] = L5Item and
SAP.[Code] = L6Item
inner join tblTreeviewTEST on
(TV_Id6 = TV_Id )
where TV_ClosedF6 = 1
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Unflagging Code'
goto Err
End
print 'End of Scenario2 Level6'
/*
=============================================================================
Scenario 3
=============================================================================
level 1
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item
inner join tblTreeviewTEST on
(TV_Id1 = TV_Id or
TV_Id2 = TV_Id or
TV_Id3 = TV_Id or
TV_Id4 = TV_Id or
TV_Id5 = TV_Id or
TV_Id6 = TV_Id )
where customer is null and
TV_ClosedF1 = 0
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Customer'
goto Err
End
print 'End of Scenario3 Level1'
/*
=============================================================================
level 2
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item
inner join tblTreeviewTEST on
(TV_Id2 = TV_Id or
TV_Id3 = TV_Id or
TV_Id4 = TV_Id or
TV_Id5 = TV_Id or
TV_Id6 = TV_Id )
where customer is null and
[work type] is null and
TV_ClosedF2 = 0
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Work ype'
goto Err
End
print 'End of Scenario3 Level2'
/*
=============================================================================
level 3
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item
inner join tblTreeviewTEST on
(TV_Id3 = TV_Id or
TV_Id4 = TV_Id or
TV_Id5 = TV_Id or
TV_Id6 = TV_Id )
where customer is null and
[work type] is null and
[Work Sub Type] is null and
TV_ClosedF3 = 0
set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Work SubType'
goto Err
End
print 'End of Scenario3 Level3'
/*
=============================================================================
level 4
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item
inner join tblTreeviewTEST on
(TV_Id4 = TV_Id or
TV_Id5 = TV_Id or
TV_Id6 = TV_Id )
where customer is null and
[work type] is null and
[Work Sub Type] is null and
[Service Order / Network] is null and
TV_ClosedF4 = 0
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Service Order/Newtork'
goto Err
End
print 'End of Scenario3 Level4'
/*
=============================================================================
level 5
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item and
SAP.[Description] = L5Item
inner join tblTreeviewTEST on
(TV_Id5 = TV_Id or
TV_Id6 = TV_Id )
where customer is null and
[work type] is null and
[Work Sub Type] is null and
[Service Order / Network] is null and
[Description] is null and
TV_ClosedF5 = 0
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Dsecription'
goto Err
End
print 'End of Scenario3 Level5'
/*
=============================================================================
level 6
=============================================================================
*/
Update tblTreeviewTEST
set TV_ClosedF = 1
from tblSAPCodesImport as SAP
right join vTreeviewTEST on SAP.Customer = L1Item and
SAP.[Work Type] = L2Item and
SAP.[Work Sub Type] = L3Item and
SAP.[Service Order / Network] = L4Item and
SAP.[Description] = L5Item and
SAP.[Code] = L6Item
inner join tblTreeviewTEST on
(TV_Id6 = TV_Id )
where customer is null and
[work type] is null and
[Work Sub Type] is null and
[Service Order / Network] is null and
[Description] is null and
[Code] is null and
TV_ClosedF6 = 0
Set @rc = @@error
If @rc <> 0
Begin
set @LE_ErrCode = @rc
set @LE_ErrMsg = 'uspImportSAPCodes : Error on Flagging Code'
goto Err
End
print 'End of Scenario3 Level6'
commit tran ImportSapCodes
return @rc
Err:
if @@trancount > 0 rollback tran ImportSapCodes
exec uspInsertLogError @LE_ErrCode, @LE_ErrMsg
print 'There has been an error !'
return @rc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO