REQ_CZ_TMP_BBM_job_MVT

mail@pastecode.io avatar
unknown
plain_text
2 years ago
40 kB
5
Indexable
Never
create or replace PROCEDURE REQ_CZ_TMP_BBM_job_MVT (
     START_DATE_IN	    IN      VARCHAR2
	,END_DATE_IN	    IN      VARCHAR2
	,RESULTS1           OUT     SYS_REFCURSOR
)
AS
    DEPOT_CODE	    VARCHAR2(3);
    ACTIVITY_CODE   VARCHAR2(3);
    START_DATE      VARCHAR2(14);
    END_DATE        VARCHAR2(14);
BEGIN
    DEPOT_CODE      := 'TMP';
    ACTIVITY_CODE   := 'BBM';
    START_DATE      := REPLACE(REPLACE(REPLACE(START_DATE_IN, ':', ''), '-', ''), ' ', '');
    END_DATE        := REPLACE(REPLACE(REPLACE(END_DATE_IN, ':', ''), '-', ''), ' ', '');
--    START_DATE      := '20221130104040';
--    END_DATE        := '20221130121052';

OPEN results1 FOR

SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO' ) THEN
            TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))
        WHEN HL_MVT_GEI.VGCMES IN ( 'ZUG', 'BRU', 'VER', 'MHD', 'ARI' ) THEN
            TO_CHAR(HL_MVT_GEI.VGQMVG)
        ELSE
            TO_CHAR(TO_NUMBER(HL_MVT_GEI.VGSMVG || HL_MVT_GEI.VGQMVG))
    END                             AS QUANTITY,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ABG', 'BRU', 'WEK', 'VER', 'MHD', 'ARO')
                THEN 'PRAG_EXT'
    END                             AS FROMLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ZUG', 'ARI')
                THEN 'PRAG_EXT'
        WHEN HL_MVT_GEI.VGCMES IN ('BRU', 'VER', 'MHD')
                THEN 'SCHROTT'
    END                             AS TOLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES = 'ABG' THEN
            'ABGANG'
        WHEN HL_MVT_GEI.VGCMES = 'ZUG' THEN
            'ZUGANG'
        WHEN HL_MVT_GEI.VGCMES = 'BRU' THEN
            'BRUCH'
        WHEN HL_MVT_GEI.VGCMES = 'WEK' THEN
            'WEKORREKT'
        WHEN HL_MVT_GEI.VGCMES = 'VER' THEN
            'VERSCHROTT'
        WHEN HL_MVT_GEI.VGCMES = 'MHD' THEN
            'MHD'
        WHEN HL_MVT_GEI.VGCMES = 'ARO' THEN
            'ARTAEAB'
        WHEN HL_MVT_GEI.VGCMES = 'ARI' THEN
            'ARTAEZU'
        ELSE 'BLEDNY REASON CODE'
    END                             AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN

ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT
AND LTRIM(RTRIM(ART_EAN."BATCH")) = LTRIM(RTRIM(HL_MVT_GEI.VGLOTF))

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO', 'ZUG', 'BRU', 'VER', 'ARI')
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NOT NULL

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL

SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO' ) THEN
            TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))
        WHEN HL_MVT_GEI.VGCMES IN ( 'ZUG', 'BRU', 'VER', 'MHD', 'ARI' ) THEN
            TO_CHAR(HL_MVT_GEI.VGQMVG)
        ELSE
            TO_CHAR(TO_NUMBER(HL_MVT_GEI.VGSMVG || HL_MVT_GEI.VGQMVG))
    END                             AS QUANTITY,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ABG', 'BRU', 'WEK', 'VER', 'MHD', 'ARO')
                THEN 'PRAG_EXT'
    END                             AS FROMLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ZUG', 'ARI')
                THEN 'PRAG_EXT'
        WHEN HL_MVT_GEI.VGCMES IN ('BRU', 'VER', 'MHD')
                THEN 'SCHROTT'
    END                             AS TOLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES = 'ABG' THEN
            'ABGANG'
        WHEN HL_MVT_GEI.VGCMES = 'ZUG' THEN
            'ZUGANG'
        WHEN HL_MVT_GEI.VGCMES = 'BRU' THEN
            'BRUCH'
        WHEN HL_MVT_GEI.VGCMES = 'WEK' THEN
            'WEKORREKT'
        WHEN HL_MVT_GEI.VGCMES = 'VER' THEN
            'VERSCHROTT'
        WHEN HL_MVT_GEI.VGCMES = 'MHD' THEN
            'MHD'
        WHEN HL_MVT_GEI.VGCMES = 'ARO' THEN
            'ARTAEAB'
        WHEN HL_MVT_GEI.VGCMES = 'ARI' THEN
            'ARTAEZU'
        ELSE 'BLEDNY REASON CODE'
    END                             AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO', 'ZUG', 'BRU', 'ARI')
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NULL

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL
-- MHD
SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO' ) THEN
            TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))
        WHEN HL_MVT_GEI.VGCMES IN ( 'ZUG', 'BRU', 'VER', 'MHD', 'ARI' ) THEN
            TO_CHAR(HL_MVT_GEI.VGQMVG)
        ELSE
            TO_CHAR(TO_NUMBER(HL_MVT_GEI.VGSMVG || HL_MVT_GEI.VGQMVG))
    END                             AS QUANTITY,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ABG', 'BRU', 'WEK', 'VER', 'MHD', 'ARO')
                THEN 'PRAG_EXT'
    END                             AS FROMLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ZUG', 'ARI')
                THEN 'PRAG_EXT'
        WHEN HL_MVT_GEI.VGCMES IN ('BRU', 'VER', 'MHD')
                THEN 'SCHROTT'
    END                             AS TOLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES = 'ABG' THEN
            'ABGANG'
        WHEN HL_MVT_GEI.VGCMES = 'ZUG' THEN
            'ZUGANG'
        WHEN HL_MVT_GEI.VGCMES = 'BRU' THEN
            'BRUCH'
        WHEN HL_MVT_GEI.VGCMES = 'WEK' THEN
            'WEKORREKT'
        WHEN HL_MVT_GEI.VGCMES = 'VER' THEN
            'VERSCHROTT'
        WHEN HL_MVT_GEI.VGCMES = 'MHD' THEN
            'MHD'
        WHEN HL_MVT_GEI.VGCMES = 'ARO' THEN
            'ARTAEAB'
        WHEN HL_MVT_GEI.VGCMES = 'ARI' THEN
            'ARTAEZU'
        ELSE 'BLEDNY REASON CODE'
    END                             AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN

ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT
AND LTRIM(RTRIM(ART_EAN."BATCH")) = LTRIM(RTRIM(HL_MVT_GEI.VGLOTF))

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ('MHD')
          AND HL_MVT_GEI.VGSMVG = '-'
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NOT NULL

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL

SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ( 'ABG', 'WEK', 'ARO' ) THEN
            TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))
        WHEN HL_MVT_GEI.VGCMES IN ( 'ZUG', 'BRU', 'VER', 'MHD', 'ARI' ) THEN
            TO_CHAR(HL_MVT_GEI.VGQMVG)
        ELSE
            TO_CHAR(TO_NUMBER(HL_MVT_GEI.VGSMVG || HL_MVT_GEI.VGQMVG))
    END                             AS QUANTITY,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ABG', 'BRU', 'WEK', 'VER', 'MHD', 'ARO')
                THEN 'PRAG_EXT'
    END                             AS FROMLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES IN ('ZUG', 'ARI')
                THEN 'PRAG_EXT'
        WHEN HL_MVT_GEI.VGCMES IN ('BRU', 'VER', 'MHD')
                THEN 'SCHROTT'
    END                             AS TOLOCATIONCODE,
    CASE
        WHEN HL_MVT_GEI.VGCMES = 'ABG' THEN
            'ABGANG'
        WHEN HL_MVT_GEI.VGCMES = 'ZUG' THEN
            'ZUGANG'
        WHEN HL_MVT_GEI.VGCMES = 'BRU' THEN
            'BRUCH'
        WHEN HL_MVT_GEI.VGCMES = 'WEK' THEN
            'WEKORREKT'
        WHEN HL_MVT_GEI.VGCMES = 'VER' THEN
            'VERSCHROTT'
        WHEN HL_MVT_GEI.VGCMES = 'MHD' THEN
            'MHD'
        WHEN HL_MVT_GEI.VGCMES = 'ARO' THEN
            'ARTAEAB'
        WHEN HL_MVT_GEI.VGCMES = 'ARI' THEN
            'ARTAEZU'
        ELSE 'BLEDNY REASON CODE'
    END                             AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ('MHD')
          AND HL_MVT_GEI.VGSMVG = '-'
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NULL

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL
-- ZUI - WITH BATCH
SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(HL_MVT_GEI.VGQMVG)      AS QUANTITY,
    NULL                            AS FROMLOCATIONCODE,
    'PRAG_EXT'                      AS TOLOCATIONCODE,
    'ZUGANGINV'                     AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT
AND LTRIM(RTRIM(ART_EAN."BATCH")) = LTRIM(RTRIM(HL_MVT_GEI.VGLOTF))

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NOT NULL
          -- ZUI
        AND HL_MVT_GEI.VGSMVG = '+'
        AND HL_MVT_GEI.VGCTST = '200'
        AND HL_MVT_GEI.VGCTVG = '410'
        AND HL_MVT_GEI.VGCTMS = '320'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL
-- ZUI - NO BATCH
SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(HL_MVT_GEI.VGQMVG)      AS QUANTITY,
    NULL                            AS FROMLOCATIONCODE,
    'PRAG_EXT'                      AS TOLOCATIONCODE,
    'ZUGANGINV'                     AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NULL
          -- ZUI
        AND HL_MVT_GEI.VGSMVG = '+'
        AND HL_MVT_GEI.VGCTST = '200'
        AND HL_MVT_GEI.VGCTVG = '410'
        AND HL_MVT_GEI.VGCTMS = '320'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL
-- ABI - WITH BATCH
SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))      AS QUANTITY,
    'PRAG_EXT'                      AS FROMLOCATIONCODE,
    NULL                      		AS TOLOCATIONCODE,
    'ABGANGINV'                     AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT
AND LTRIM(RTRIM(ART_EAN."BATCH")) = LTRIM(RTRIM(HL_MVT_GEI.VGLOTF))

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NOT NULL
          -- ABI
		  AND HL_MVT_GEI.VGSMVG = '-'
		  AND HL_MVT_GEI.VGCTST = '200'
		  AND HL_MVT_GEI.VGCTVG = '410'
		  AND HL_MVT_GEI.VGCTMS = '340'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL
-- ABI - NO BATCH
SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(TO_NUMBER('-' || HL_MVT_GEI.VGQMVG))      AS QUANTITY,
    'PRAG_EXT'                      AS FROMLOCATIONCODE,
    NULL                            AS TOLOCATIONCODE,
    'ABGANGINV'                     AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NULL
          -- ABI
		  AND HL_MVT_GEI.VGSMVG = '-'
		  AND HL_MVT_GEI.VGCTST = '200'
		  AND HL_MVT_GEI.VGCTVG = '410'
		  AND HL_MVT_GEI.VGCTMS = '340'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL

SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(HL_MVT_GEI.VGQMVG)		AS QUANTITY,
    'PRAG_EXT'						AS FROMLOCATIONCODE,
    'SCHROTT'						AS TOLOCATIONCODE,
    'VERSCHROTT'					AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN

ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT
AND LTRIM(RTRIM(ART_EAN."BATCH")) = LTRIM(RTRIM(HL_MVT_GEI.VGLOTF))

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ('VER')
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NOT NULL
		  AND HL_MVT_GEI.VGSMVG = '-'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

UNION ALL

SELECT
    HL_MVT_GEI.VGCART               AS ITEMNO,
    HL_MVT_GEI.VGLOTF               AS VARIANTCODE,
    ART_EAN."EAN"                   AS ITEMNOWMS,
    ART_EAN."DES"                   AS DESCRIPTION,
    'STCK'                          AS UNITOFMEASURE,
    TO_CHAR(HL_MVT_GEI.VGQMVG)		AS QUANTITY,
    'PRAG_EXT'						AS FROMLOCATIONCODE,
    'SCHROTT'						AS TOLOCATIONCODE,
    'VERSCHROTT'					AS REASONCODE,
    HL_MVT_GEI.VGRMVS               AS DOCUMENTNO,
    TO_CHAR(HL_MVT_GEI.VGSCRE
            || HL_MVT_GEI.VGACRE
            || '-'
            || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
            || '-'
            || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
            || 'T'
            ||
            CASE
                WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                    '0'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                ELSE
                    SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                    || ':'
                    || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
            END
    )                               AS MOVEMENTDATETIME

         FROM SD_HLMVTGP HL_MVT_GEI

JOIN
(SELECT HL_ARTICLE.ARCART              "ART"
       ,HL_ARTICLE.ARCACT              "ACT"
       ,MAX(HL_ART_VL_IDENT.VICIVL)    "EAN"
       ,HL_COMMENTAIRE.COTXTC          "BATCH"
       ,HL_ARTICLE.ARLART              "DES"

  FROM SD_HLARTIP HL_ARTICLE

  JOIN SD_HLVLIDP HL_ART_VL_IDENT
    ON HL_ARTICLE.ARCART = HL_ART_VL_IDENT.VICART
   AND HL_ARTICLE.ARCACT = HL_ART_VL_IDENT.VICACT
   AND HL_ART_VL_IDENT.VICVLA   = '10'
   AND HL_ART_VL_IDENT.VICTYI   = 'EAN13'

LEFT JOIN SD_HLCOMMP HL_COMMENTAIRE
       ON HL_ART_VL_IDENT.VINCOM = HL_COMMENTAIRE.CONCOM
      AND HL_COMMENTAIRE.COCFCO  = 'VAR'

    WHERE HL_ARTICLE.ARCACT = 'BBM'

GROUP BY HL_ARTICLE.ARCART, HL_ARTICLE.ARCACT, HL_COMMENTAIRE.COTXTC, HL_ARTICLE.ARLART) ART_EAN
ON ART_EAN."ART"    = HL_MVT_GEI.VGCART
AND ART_EAN."ACT"   = HL_MVT_GEI.VGCACT

        WHERE HL_MVT_GEI.VGCDPO = DEPOT_CODE
          AND HL_MVT_GEI.VGCACT = ACTIVITY_CODE
          AND HL_MVT_GEI.VGCMES IN ('VER')
          AND HL_MVT_GEI.VGCPRP != 'BBF'        -- FOR CHINA MILK NOT SEND MVT MSG
          AND LTRIM(RTRIM(HL_MVT_GEI.VGLOTF)) IS NULL
		  AND HL_MVT_GEI.VGSMVG = '-'

          AND TO_CHAR(HL_MVT_GEI.VGSCRE
                || HL_MVT_GEI.VGACRE
                || LPAD(HL_MVT_GEI.VGMCRE, 2, '0')
                || LPAD(HL_MVT_GEI.VGJCRE, 2, '0')
                ||
                CASE
                    WHEN LENGTH(HL_MVT_GEI.VGHCRE) = 5 THEN
                        '0'
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 1, 1)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 2, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 4, 2)
                    ELSE
                        SUBSTR(HL_MVT_GEI.VGHCRE, 1, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 3, 2)
                        || SUBSTR(HL_MVT_GEI.VGHCRE, 5, 2)
                END
    ) BETWEEN START_DATE AND END_DATE

ORDER BY MOVEMENTDATETIME
--ORDER BY   HL_MVT_GEI.VGSCRE
--        || HL_MVT_GEI.VGACRE
--        || HL_MVT_GEI.VGMCRE
--        || HL_MVT_GEI.VGJCRE
--        || HL_MVT_GEI.VGHCRE DESC
    ;

END;