REQ_CZ_TMP_BBM_job_MVT
unknown
plain_text
3 years ago
40 kB
7
Indexable
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;Editor is loading...