1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202
|
ALTER PROCEDURE [dbo].[CU_Customer_Allocation_BIS]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Delete previously allocated records from the allocation table
DELETE CU_CustomerAllocation WHERE Allocated = 1;
-- Mark records from the previous allocation as Allocated (to keep track of them)
UPDATE CU_CustomerAllocation SET Allocated = 1 WHERE Allocated = 0;
create table #explode
(
katnr int not null,
code int not null,
scode int not null,
code2 int not null,
primary key (katnr, code, scode, code2)
);
insert into #explode
select 35, 0, 0, 0
union all
select katnr, 0, 0, code
from te_isi_ka where katnr = 35 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, 0, code
from te_isi_ka where katnr = 35 and SpracheNr = 0 and LosKZ = 0
union all
select 275, 0, 0, 0
union all
select katnr, 0, 0, code
from te_isi_ka where katnr = 275 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, 0, code
from te_isi_ka where katnr = 275 and SpracheNr = 0 and LosKZ = 0
union all
select 1031, 0, 0, 0
union all
select katnr, 0, 0, code
from te_isi_ka where katnr = 1031 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, 0, code
from te_isi_ka where katnr = 1031 and SpracheNr = 0 and LosKZ = 0
union all
select 1032, 0, 0, 0
union all
select katnr, 0, unterkat1, code
from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, unterkat1, 0
from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, unterkat1, code
from te_isi_ka where katnr = 1032 and SpracheNr = 0 and LosKZ = 0
union all
select 1046, 0, 0, 0
union all
select katnr, 0, 0, code
from te_isi_ka where katnr = 1046 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, code, 0, code
from te_isi_ka where katnr = 1046 and SpracheNr = 0 and LosKZ = 0
union all
select 1085, 0, 0, 0
union all
select katnr, 0, 0, cast(extkey as int)
from te_isi_ka where katnr = 1085 and SpracheNr = 0 and LosKZ = 0
union all
select katnr, cast(extkey as int), 0, cast(extkey as int)
from te_isi_ka where katnr = 1085 and SpracheNr = 0 and LosKZ = 0
;
-- Insert new records in the allocation table
INSERT INTO CU_CustomerAllocation (
Mode, FI_StaNo, FI_SerNo, FI_ExtSys, FI_ExtKey,
Rep, Rep_ExtSys, Rep_ExtKey, Date)
SELECT distinct 'NEW',
DBO.CU_ID_To_StaNo(FI.ID) CompanyStaNo,
DBO.CU_ID_To_SerNo(FI.ID) CompanySerNo,
FI.ExtSystem,
FI.ExtKey,
ID.ID RepID,
ID.ExtSystem,
ID.ExtKey,
GETDATE()
FROM te_isi_C024 C024
INNER JOIN te_isi_ID ID ON -- Rep
ID.ID = C024.F7000
AND ID.Inaktiv = 0 -- Only active users
AND ID.LosKZ = 0 -- Not deleted
inner join #explode kt on kt.katnr = 1085 and kt.code = c024.F7004
inner join #explode kn1 on kn1.katnr = 1031 and kn1.code = c024.F7007
inner join #explode kn2 on kn2.katnr = 1032 and kn2.code = c024.F7008 and kn2.scode = kn1.code
inner join #explode kg on kg.katnr = 35 and kg.code = c024.F7009
inner join #explode ks on ks.katnr = 275 and ks.code = c024.F7010
inner join #explode ke on ke.katnr = 1046 and ke.code = c024.F7006
INNER JOIN te_isi_FI FI ON -- Company
FI.MandNr = kt.code2 -- Tenant
AND FI.F7020 = kn1.code2 -- Network 1
AND FI.F7021 = kn2.code2 -- Network 2
AND FI.Gebiet = kg.code2 -- Geographical Area
AND FI.F7019 = ks.code2 -- Sales Channel
AND FI.F7004 between isnull(C024.F7011, FI.F7004) -- INSEE Code Start
and isnull(C024.F7012, FI.F7004) -- INSEE Code End
AND ( FI.Fityp = 2 -- CoType Customer
OR FI.Fityp2 = 2 -- CoType Customer
OR FI.Fityp3 = 2 -- CoType Customer
OR FI.Fityp4 = 2 -- CoType Customer
OR FI.Fityp5 = 2) -- CoType Customer
AND FI.LosKZ = 0 -- Not deleted
AND FI.MandNr <> 0 -- Children FI have a tenant
AND FI.ID_FI_250 <> 0 -- Children FI have a parent
INNER JOIN te_isi_FI FI_GLO on -- Global Company
FI_GLO.MandNr = 0 -- Parent doesn't have a tenant
AND FI_GLO.ID = FI.ID_FI_250
AND FI.LosKZ = 0
INNER JOIN te_isi_C022 SP on -- Company Production Species
SP.F7003 = ke.code2
AND SP.ID_FI = FI_GLO.ID
AND SP.DEL = 0
WHERE C024.DEL = 0
AND case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013 -- Allocation Start
AND case when C024.F7014 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7014 end <= C024.F7014 -- Allocation End
AND NOT EXISTS ( -- No need to create/modify the SB if it already exists
select *
from te_isi_SB SB
where SB.ID_FI = FI.ID
AND SB.BearbeiterId = C024.F7000
AND SB.LosKZ = 0
);
-- Insert records in the allocation table to remove SB not anymore applicable
INSERT INTO CU_CustomerAllocation (
Mode, FI_StaNo, FI_SerNo, FI_ExtSys, FI_ExtKey,
Rep, Rep_ExtSys, Rep_ExtKey, Date)
SELECT 'DEL',
DBO.CU_ID_To_StaNo(SBFI.ID) CompanyStaNo,
DBO.CU_ID_To_SerNo(SBFI.ID) CompanySerNo,
SBFI.ExtSystem,
SBFI.ExtKey,
SBID.ID RepID,
SBID.ExtSystem,
SBID.ExtKey,
GETDATE()
FROM te_isi_SB SB
INNER JOIN te_isi_FI SBFI ON -- Allocated Company
SBFI.ID = SB.ID_FI
INNER JOIN te_isi_ID SBID ON -- Allocated Rep
SBID.ID = SB.BearbeiterId
LEFT OUTER JOIN (
te_isi_C024 C024
inner join #explode kt on kt.katnr = 1085 and kt.code = c024.F7004
inner join #explode kn1 on kn1.katnr = 1031 and kn1.code = c024.F7007
inner join #explode kn2 on kn2.katnr = 1032 and kn2.code = c024.F7008 and kn2.scode = kn1.code
inner join #explode kg on kg.katnr = 35 and kg.code = c024.F7009
inner join #explode ks on ks.katnr = 275 and ks.code = c024.F7010
inner join #explode ke on ke.katnr = 1046 and ke.code = c024.F7006
) ON -- Customer Allocation
C024.F7000 = SB.BearbeiterId
AND case when C024.F7013 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7013 end >= C024.F7013 -- Allocation Start
AND case when C024.F7014 = 0 then dbo.CU_Date_To_CRMDate(getdate()) else C024.F7014 end <= C024.F7014 -- Allocation End
AND C024.DEL = 0
LEFT OUTER JOIN te_isi_FI FI ON -- Already allocated Companies
FI.MandNr = kt.code2 -- Tenant
AND FI.F7020 = kn1.code2 -- Network 1
AND FI.F7021 = kn2.code2 -- Network 2
AND FI.Gebiet = kg.code2 -- Geographical Area
AND FI.F7019 = ks.code2 -- Sales Channel
AND FI.F7004 between isnull(C024.F7011, FI.F7004) -- INSEE Code Start
and isnull(C024.F7012, FI.F7004) -- INSEE Code End
AND FI.ID = SB.ID_FI -- Already allocated
AND FI.LosKZ = 0 -- Not deleted
AND FI.MandNr <> 0 -- Children FI have a tenant
AND FI.ID_FI_250 <> 0 -- Children FI have a parent
LEFT OUTER JOIN te_isi_FI FI_GLO on -- Global Company (Tenant = 0)
FI_GLO.MandNr = 0
AND FI_GLO.ID = FI.ID_FI_250
AND FI.LosKZ = 0
LEFT OUTER JOIN te_isi_C022 SP on -- Company Production Species
SP.F7003 = ke.code2
AND SP.ID_FI = FI_GLO.ID
AND SP.DEL = 0
WHERE SB.LosKZ = 0
AND SB.F7007 = 1 -- Delete only records created by Automatic Allocation
AND (
C024.F7006 IS NULL -- Delete even if Species criteria is not set
OR SP.F7003 IS NOT NULL -- Species criteria is set: Record deleted in HAVING instruction if needed
OR SBID.Inaktiv = 1 -- Delete allocation of inactive users
)
GROUP BY SBFI.ID, SBFI.ExtSystem, SBFI.ExtKey, SBID.ID, SBID.ExtSystem, SBID.ExtKey
HAVING COUNT(C024.ID) = 0 -- Delete if allocation is no longer relevant
OR COUNT(FI.ID) = 0 -- or allocated company does not match allocation criterias anymore
OR COUNT(SP.ID) = 0 -- or allocated company does not match species criterias anymore
;
drop table #explode;
END |
Partager