DROP PROCEDURE ""."ZCONCAT_EKKO_EBN";

CREATE PROCEDURE ""."ZCONCAT_EKKO_EBN"(OUT RESULT ""."OUT_SPLIT_EK_EBN")

LANGUAGE SQLSCRIPT AS

BEGIN

    DECLARE _ZHETONG NVARCHAR(1000) ARRAY;

    DECLARE _ZEBELN  NVARCHAR(5000) ARRAY;

    DECLARE _ZHT NVARCHAR(1000); 

    DECLARE _INDEX INTEGER := 1;

    DECLARE _IND1 INTEGER := 1;

    DECLARE CURSOR C_CURSOR1 FOR

      SELECT DISTINCT ZHETONG,EBELN FROM "CTNT_ECC"."EKKO" WHERE BUKRS LIKE '21%' AND ZHETONG NOT IN ('','','','TS','无')

      ORDER BY ZHETONG,EBELN;

    FOR CUR_ROW1 AS C_CURSOR1 DO 

      IF _IND1 = 1 THEN

        _ZHT := CUR_ROW1.ZHETONG;

        _ZHETONG[:_INDEX] := '';

        _ZEBELN[:_INDEX] := '';

      END IF; 

      IF CUR_ROW1.ZHETONG = :_ZHT THEN

       _ZHETONG[:_INDEX] := CUR_ROW1.ZHETONG;

       _ZEBELN[:_INDEX] := :_ZEBELN[:_INDEX]||CUR_ROW1.EBELN||',';

      ELSE

      _INDEX := :_INDEX + 1;

      _ZHETONG[:_INDEX]  := CUR_ROW1.ZHETONG;

      _ZEBELN[:_INDEX] := CUR_ROW1.EBELN ||',';

      _ZHT := CUR_ROW1.ZHETONG;

      END IF;

       _IND1 := :_IND1 + 1;

    END FOR;  

      RST = UNNEST(:_ZHETONG, :_ZEBELN) AS ("ZHETONG", "EBELN");

      RESULT = SELECT ZHETONG,

                      LEFT(EBELN,LENGTH(EBELN)-1) AS EBELN

                 FROM :RST;

END;

CALL "ZB_CHENWH"."ZCONCAT_EKKO_EBN"(?) ;