[FUNCTION]GET_MPASS

CREATE OR REPLACE FUNCTION SAPHEE.GET_MPASS (IN_MANDT VARCHAR2, IN_EBELN VARCHAR2, IN_EBELP VARCHAR2 )

RETURN DECIMAL IS
        RT DECIMAL(13, 3) DEFAULT 0.0;
        P_MANDT VARCHAR(9) DEFAULT ''; 
        P_EBELN VARCHAR(30) DEFAULT '';
        P_EBELP VARCHAR(30) DEFAULT '';

        BEGIN
                P_MANDT := RTRIM(IN_MANDT);
                P_EBELN := RTRIM(IN_EBELN);
                P_EBELP := RTRIM(IN_EBELP);
               
                SELECT  SUM(MPASS)
                INTO    RT
                FROM     ( SELECT  MANDT AS MANDT
                                  ,EBELN AS EBELN
                                  ,EBELP AS EBELP
                                  , CASE
                                           WHEN PRO_F = '4'
                                           THEN COALESCE(SUM(MENGE),0)
                                           ELSE 0
                                  END AS MPASS
                         FROM     SAPHEE.ZMMT017
                         WHERE    MANDT  = P_MANDT
                         AND      EBELN  = P_EBELN
                         AND      EBELP  = P_EBELP
                         AND      PRO_F >= '1'
                         GROUP BY MANDT
                                  , EBELN
                                  , EBELP
                                  , PRO_F
                         ) S
                WHERE    1=1
                GROUP BY S.MANDT,S.EBELN,S.EBELP;
               
                RETURN RT;
        END;