Hello,
why are some views missing on which provision.vProvisionTeil1BDM depends on ?
Kind regards,
Ralph
Parent Dependencies (objects that provision.vProvisionTeil1BDM depends on)
SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO
setuser
N'provision'
GO
CREATE
VIEW provision.vProvisionTeil1BDM
AS
SELECT
MonthKey=
CALC.MonthKey,
PersonKey=
CALC.PersonKey,
ArtikelKey=
CALC.ArtikelKey,
Provision=
CAST(CALC.ProvisionsSatz * CALC.ProvisionsSatzFaktor * CALC.MargeISTLJ
* MFAKT. MargenMultiplikatorAS Decimal(22, 6)),
ProvisionsSatz= CAST(CALC. ProvisionsSatzAS Decimal(17, 6)),
ProvisionsSatzFaktor= CAST(CALC. ProvisionsSatzFaktorAS Decimal(5, 1)),
UmsatzISTLJ=
CAST(CALC. UmsatzISTLJAS Decimal(22, 6)),
MargeISTLJ=
CAST(CALC. MargeISTLJAS Decimal(22, 6)),
MargeISTLJRel=
CAST(CALC. MargeISTLJRelAS Decimal(22, 6)),
UmsatzISTVJ=
CAST(CALC. UmsatzISTVJAS Decimal(22, 6)),
MargeISTVJ=
CAST(CALC. MargeISTVJAS Decimal(22, 6)),
MargeISTVJRel=
CAST(CALC. MargeISTVJRelAS Decimal(22, 6)),
UmsatzPLANLJ=
CAST(CALC. UmsatzPLANLJAS Decimal(22, 6)),
MargePLANLJ=
CAST(CALC. MargePLANLJAS Decimal(22, 6)),
MargePLANLJRel= CAST(CALC. MargePLANLJRelAS Decimal(22, 6)),
MargeZielRel=
CAST(CALC. MargeZielRelAS Decimal(22, 6)),
MargeRelDelta=
CAST(CALC. MargeRelDeltaAS Decimal(22, 6)),
MargenMultiplikator= CAST(MFAKT. MargenMultiplikatorAS Decimal(4, 2))
FROM
(
SELECT
MonthKey,
PersonKey,
ArtikelKey,
ProvisionsSatz,
ProvisionsSatzFaktor,
UmsatzISTLJ,
MargeISTLJ,
MargeISTLJRel,
UmsatzISTVJ,
MargeISTVJ,
MargeISTVJRel,
UmsatzPLANLJ,
MargePLANLJ,
MargePLANLJRel,
MargeZielRel=
provision.Get_MargeZielRel(
'BDM',
UmsatzISTVJ, MargeISTVJ,
UmsatzPLANLJ, MargePLANLJ
),
MargeRelDelta=
provision.Get_MargeRelDelta(
'BDM',
UmsatzISTLJ, MargeISTLJ,
UmsatzISTVJ, MargeISTVJ,
UmsatzPLANLJ, MargePLANLJ
)
FROM
(
SELECT
MonthKey=
ZEIT.MonthKey,
PersonKey=
PERSON.PersonKey,
ArtikelKey=
KLGRs.ArtikelKey,
ProvisionsSatz= SATZ.ProvisionsSatzBDM,
ProvisionsSatzFaktor= ISNULL(PFAKT.Faktor, 1),
UmsatzISTLJ=
ISTLJ.Umsatz,
MargeISTLJ=
ISTLJ.Marge,
MargeISTLJRel=
CASE WHEN (ISTLJ.Umsatz = 0) OR (ISTLJ.Marge = 0) THEN 0 ELSE ISTLJ.Marge / ISTLJ.Umsatz END,
UmsatzISTVJ=
ISNULL(ISTVJ.Umsatz, 0),
MargeISTVJ=
ISNULL(ISTVJ.Marge, 0),
MargeISTVJRel=
CASE WHEN (ISNULL(ISTVJ.Umsatz, 0) = 0) OR (ISNULL(ISTVJ.Marge, 0) = 0) THEN 0 ELSE ISTVJ.Marge / ISTVJ.Umsatz END,
UmsatzPLANLJ=
ISNULL(PLANLJ.Umsatz, 0),
MargePLANLJ=
ISNULL(PLANLJ.Marge, 0),
MargePLANLJRel= CASE WHEN (ISNULL(PLANLJ.Umsatz, 0) = 0) OR (ISNULL(PLANLJ.Marge, 0) = 0) THEN 0 ELSE PLANLJ.Marge / PLANLJ.Umsatz END
FROM Rutronik_Star.provision.vProvisionSaetze SATZ
CROSS JOIN
Rutronik_Star.provision.vDimMonate
ZEIT
INNER JOIN
(
SELECT
MonthKey=
FactISTLJ.MonthKey,
PersonKuerzel=
Z_BDM_VI.PersonKuerzel,
KLGR= FactISTLJ.KLGR,
Umsatz= SUM(FactISTLJ.Umsatz),
Marge= SUM(FactISTLJ.Marge)
FROM
Rutronik_Star.provision.FactISTLJ
INNER JOIN
Rutronik_Star.provision.vZuordnungBDMzuVI Z_BDM_VI ON
FactISTLJ. Mandant= Z_BDM_VI.Mandant
AND
FactISTLJ. Vertreter=
Z_BDM_VI.Vertreter
INNER JOIN
Rutronik_Star.provision.vZuordnungBDMzuKLGR Z_BDM_KLGR ON
Z_BDM_VI. PersonKuerzel=
Z_BDM_KLGR.PersonKuerzel
AND
FactISTLJ. KLGR= Z_BDM_KLGR.KLGR
GROUP BY
FactISTLJ.MonthKey,
Z_BDM_VI.PersonKuerzel,
FactISTLJ.KLGR
) ISTLJ ON
ZEIT. MonthKey=
ISTLJ.MonthKey
AND
SATZ. PersonKuerzel=
ISTLJ.PersonKuerzel
INNER JOIN
Rutronik_Star.provision.vDimMitarbeiter PERSON ON
ISTLJ.PersonKuerzel = PERSON.PersonKuerzel
INNER JOIN
Rutronik_Star.dbo.vKLGRs KLGRs ON
ISTLJ. KLGR= KLGRs.KLGR
LEFT OUTER
JOIN Rutronik_Star.provision.vProvisionsfaktor PFAKT ON
ISTLJ. PersonKuerzel=
PFAKT.PersonKuerzel
AND
ISTLJ. KLGR= PFAKT.KLGR
LEFT OUTER
JOIN
(
SELECT
YearKey,
KLGR,
Umsatz= SUM(Umsatz),
Marge= SUM(Marge_LOKAL)
FROM
Rutronik_Star.provision.FactPLANLJ
GROUP BY
YearKey,
KLGR
) PLANLJ ON
ZEIT. YearKey= PLANLJ.YearKey
AND
ISTLJ. KLGR= PLANLJ.KLGR
LEFT OUTER
JOIN
(
SELECT
YearKey= FactISTVJ.YearKey,
PersonKuerzel=
Z_BDM_VI.PersonKuerzel,
KLGR= FactISTVJ.KLGR,
Umsatz= SUM(FactISTVJ.Umsatz),
Marge= SUM(FactISTVJ.Marge)
FROM
Rutronik_Star.provision.FactISTVJ
INNER JOIN
Rutronik_Star.provision.vZuordnungBDMzuVI Z_BDM_VI ON
FactISTVJ. Mandant= Z_BDM_VI.Mandant
AND
FactISTVJ. Vertreter=
Z_BDM_VI.Vertreter
INNER JOIN
Rutronik_Star.provision.vZuordnungBDMzuKLGR Z_BDM_KLGR ON
Z_BDM_VI. PersonKuerzel=
Z_BDM_KLGR.PersonKuerzel
AND
FactISTVJ. KLGR= Z_BDM_KLGR.KLGR
GROUP BY
FactISTVJ.YearKey,
Z_BDM_VI.PersonKuerzel,
FactISTVJ.KLGR
) ISTVJ ON
ZEIT.YearKey -
1=
ISTVJ.YearKey
AND
ISTLJ. PersonKuerzel=
ISTVJ.PersonKuerzel
AND
ISTLJ. KLGR= ISTVJ.KLGR
WHERE
ZEIT.MonthKey =
provision.Get_WorkMonthKey()
)
INNER_CALC
) CALC
INNER
JOIN
Rutronik_Star.provision.vMargenFaktor
MFAKT ON
( /* First Row
*/
(CALC.MargeRelDelta >= MFAKT.FROM_Value AND
MFAKT.TO_Value
IS NULL)
AND
(1 = CASE WHEN (MFAKT.FROM_Exclude =
1) AND (MFAKT.FROM_Value =
CALC.MargeRelDelta)
THEN 0
ELSE 1
END)
)
OR
( /* Middle Rows
*/
(CALC.MargeRelDelta BETWEEN MFAKT.FROM_Value AND
MFAKT.TO_Value)
AND
(1 = CASE WHEN (MFAKT.FROM_Exclude =
1) AND (MFAKT.FROM_Value =
CALC.MargeRelDelta)
THEN 0
ELSE 1
END)
AND
(1 = CASE WHEN (MFAKT.TO_Exclude =
1) AND (MFAKT.TO_Value =
CALC.MargeRelDelta)
THEN 0
ELSE 1
END)
)
OR
( /* Last Row
*/
(CALC.MargeRelDelta <= MFAKT.TO_Value AND
MFAKT.FROM_Value
IS NULL)
AND
(1 = CASE WHEN (MFAKT.TO_Exclude =
1) AND (MFAKT.TO_Value =
CALC.MargeRelDelta)
THEN 0
ELSE 1
END)
)
/*
SELECT TOP 15 *
FROM Rutronik_Star.provision.vProvisionTeil1BDM ORDER BY NEWID()
*/
GO
setuser
GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO