CREATE OR REPLACE FORCE VIEW V_DT430_DT472 ("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 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
  FROM
    (SELECT DATUM_KAL AS DATUM_KAL,
      DI001390        AS PR,
      DI001391        AS VG,
      DI001392        AS RESS,
      DI001393        AS KoA,
      DI009063        AS OE_RESS,
      DI001401        AS date_creation,
      DI001402        AS date_modify,
      DI001403        AS rwdu,
      DI001404        AS user_creation,
      DI001405        AS user_modify,
      DI001406        AS owner_license,
      DI000596        AS osc,
      DI001407        AS owner,
      DI001408        AS "group",
      DI001409        AS "any",
      (SELECT NVL(SUM(NVL(DI001510,0)),0) bel_ist_472
      FROM DT472
      WHERE DI001519 = DATUM_KAL
      AND DI001395   = DI001390
      AND DI001516   = DI001391
      AND DI001517   = DI001392
      ) BEL_Ist,
      (SELECT NVL(MAX(NVL(DI001400,0)),0) pos_472
      FROM DT472
      WHERE DI001519 = DATUM_KAL
      AND DI001395   = DI001390
      AND DI001516   = DI001391
      AND DI001517   = DI001392
      ) Pos,
      (SELECT NVL(MAX(NVL(DI002673,TO_DATE('01.01.1970', 'DD.MM.YYYY'))),TO_DATE('01.01.1970', 'DD.MM.YYYY')) ABR_DATUM
      FROM DT472
      WHERE DI001519 = DATUM_KAL
      AND DI001395   = DI001390
      AND DI001516   = DI001391
      AND DI001517   = DI001392
      ) ABR_DATUM,
      (SELECT NVL(MAX(NVL(SAP_STATE,0)),0) SAP_STATE
      FROM DT472
      WHERE DI001519 = DATUM_KAL
      AND DI001395   = DI001390
      AND DI001516   = DI001391
      AND DI001517   = DI001392
      ) SAP_STATE
    FROM DT466
    INNER JOIN dt461
    ON (DT461.DI001001 = DT466.DI001390
    AND DT461.DI001042 = 1)
    FULL OUTER JOIN
      (SELECT TRUNC(add_months(TO_DATE(TO_CHAR(sysdate, 'DD.MM.YYYY'), 'DD.MM.YYYY'),-24)) + INTERVAL '1' DAY * rownum DATUM_KAL
      FROM DT345
        CONNECT BY LEVEL <= 2*366
      ) KAL
    ON di001422 = to_date('01.01.1970', 'DD.MM.YYYY')
    )
/

CREATE OR REPLACE TRIGGER "CREATE_DT472" INSTEAD OF
UPDATE ON "V_DT430_DT472" FOR EACH ROW
BEGIN
-- update:
  UPDATE DT472 SET
    DI001510 = :NEW.BEL_IST,
    DI001501 = :NEW.DATE_MODIFY,
    di001504 = :NEW.user_modify
  WHERE DI001517 = :NEW.RESS
    AND DI001395 = :NEW.PR
    AND DI001516 = :NEW.VG
    AND DI001519 = :NEW.DATUM_KAL
    AND (
    -- if there is exactly one row with a load for that day;
    (ABS(COALESCE(DI001510,0)) > 0.0009
    AND (
      SELECT COUNT(*) FROM DT472
      WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND ABS(COALESCE(DI001510,0)) > 0.0009
      ) = 1)
    OR (
    -- or if there is no row with a load for that day,
      SELECT COUNT(*) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND COALESCE(di001510,0) > 0.0009
      ) = 0 AND (
    -- and there is at least one row with no load for that day,
    -- take the row with the highest pos. no.
      SELECT COALESCE(MAX(DI001400),0) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND COALESCE(di001510,0) = 0
      ) = DI001400)
  ;
  -- if there exists more than one row with a load, do nothing;
  -- user has to distribute load to the data sets on his/her own.

-- finally, if there are no rows with a load yet, insert one, if BEL_IST <> 0:
  INSERT INTO DT472
    (
      DI001395,
      DI001516,
      DI001517,
      DI001518,
      DI001400,
      DI001519,
      DI001510,
      DI001438,
      DI001445,
      DI007783,
      DI001500,
      DI001501,
      DI001502,
      DI001503,
      DI001504,
      DI001505,
      DI000602,
      DI001506,
      DI001507,
      DI001508
    )
  SELECT
    :NEW.PR,
    :NEW.VG,
    :NEW.RESS,
    :NEW.KoA,
    get_new_auto_id('001400'),
    :NEW.DATUM_KAL,
    :NEW.BEL_Ist,
    '''',
    :NEW.OE_Ress,
    :NEW.OE_Ress,
    :NEW.date_modify, -- modify date is create date for load record:
    :NEW.date_modify,
    :NEW.rwdu,
    :NEW.user_modify, -- modify user is create user for load record:
    :NEW.user_modify,
    :NEW.owner_license,
    :NEW.osc,
    :NEW.owner,
    :NEW."group",
    :NEW."any"
  FROM DT345
  -- only if load > 0
  WHERE COALESCE(:NEW.BEL_IST,0) != 0
    AND (
    -- and only if no row with a load for that day already exists:
      SELECT COUNT(*) FROM DT472 WHERE DI001395 = :NEW.PR
        AND DI001516   = :NEW.VG
        AND DI001517   = :NEW.RESS
        AND DI001519   = :NEW.DATUM_KAL
        AND ABS(COALESCE(DI001510,0)) > 0.0009
      ) = 0;
END;
/
