IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_dt430_dt472]'))
	DROP VIEW [dbo].[v_dt430_dt472]
GO
CREATE VIEW [dbo].[v_dt430_dt472] 
-- define column names
(
   DATUM_KAL, 
   PR, 
   VG, 
   RESS, 
   KOA, 
   OE_RESS, 
   DATE_CREATION, 
   DATE_MODIFY, 
   RWDU, 
   USER_CREATION, 
   USER_MODIFY, 
   OWNER_LICENSE, 
   OSC, 
   OWNER, 
   [group], 
   [any], 
   BEL_IST, 
   POS, 
   ABR_DATUM,
   SAP_STATE
)
AS 

SELECT 
      pr_temp.DATUM_KAL, 
      pr_temp.PR, 
      pr_temp.VG, 
      pr_temp.RESS, 
      pr_temp.KoA, 
      pr_temp.OE_RESS, 
      pr_temp.date_creation, 
      pr_temp.date_modify, 
      pr_temp.rwdu, 
      pr_temp.user_creation, 
      pr_temp.user_modify, 
      pr_temp.owner_license, 
      pr_temp.osc, 
      pr_temp.owner, 
      pr_temp.[group], 
      pr_temp.[any], 
      DT472.bel_ist_472 AS BEL_Ist, 
      CAST(DT472.pos_472 AS NUMERIC(5,0)) AS Pos, 
      DT472.abr_datum_472 AS ABR_DATUM,
      CAST(DT472.sap_state_472 AS NUMERIC(5,0)) AS SAP_STATE
   FROM 
      (
         SELECT 
            DT430.DI000997 AS DATUM_KAL, 
            DT466.DI001390 AS PR, 
            DT466.DI001391 AS VG, 
            DT466.DI001392 AS RESS, 
            DT466.DI001393 AS KoA, 
            DT466.DI009063 AS OE_RESS, 
            DT466.DI001401 AS date_creation, 
            DT466.DI001402 AS date_modify, 
            DT466.DI001403 AS rwdu, 
            DT466.DI001404 AS user_creation, 
            DT466.DI001405 AS user_modify, 
            DT466.DI001406 AS owner_license, 
            DT466.DI000596 AS osc, 
            DT466.DI001407 AS owner, 
            DT466.DI001408 AS [group], 
            DT466.DI001409 AS [any]
         FROM 
         -- DT430 (basic calendar-periods)
         dbo.DT430, 
         -- DT466 (resources assignment)
         dbo.DT466
         WHERE DT430.DI000475 IN 
            (
               SELECT DT452.DI003107
               -- DT452 (global variable)
               FROM dbo.DT452
               WHERE DT452.DI003109 = 150
            )
      )  AS pr_temp 
         -- DT472 (exposure)
         LEFT JOIN
          (SELECT DI001519 DATUM_472,
            DI001395 PR_472,
            DI001516 VG_472,
            DI001517 RESS_472,
            SUM(DI001510) bel_ist_472,
              MAX(ISNULL(DI001400,0)) pos_472,
              MAX(ISNULL(SAP_STATE,0)) sap_state_472,
            MAX(DI002673) abr_datum_472
          FROM DT472
          GROUP BY DI001519,
            DI001395,
            DI001516,
            DI001517
          ) DT472 ON DT472.DATUM_472 =DATUM_KAL AND DT472.PR_472=PR AND DT472.VG_472=VG AND RESS_472=RESS
