[FUNCTION]GET_JAJEA_BALJU

CREATE OR REPLACE FUNCTION SAPHEE.GET_JAJEA_BALJU (
IN_MANDT VARCHAR2, IN_POSID VARCHAR2, IN_MATNR VARCHAR2, IN_EBELN VARCHAR2,
IN_EBELP NUMBER )

RETURN  NUMBER

IS
  RT NUMBER(13, 3);
  P_MANDT VARCHAR2(72) ;
  P_POSID VARCHAR2(72) ;
  P_MATNR VARCHAR2(72) ;
  P_WBS VARCHAR2(72) ;
  P_LN NUMBER(13, 3) ;
  P_A_MENGE NUMBER(13, 3) ;
  P_B_MENGE NUMBER(13, 3) ;
  P_C_MENGE NUMBER(13, 3) ;

BEGIN
 P_MANDT := RTRIM(IN_MANDT);
 P_POSID := RTRIM(IN_POSID);
  P_MATNR := RTRIM(IN_MATNR);
  P_LN := INSTR(P_POSID,'-P');

  IF (P_LN = 0) THEN P_WBS := P_POSID;
  ELSE P_WBS := SUBSTR(P_POSID,1,P_LN-1);
  END IF;

 

  SELECT SUM( ( CASE WHEN A.PRCTYP='D' THEN 0 ELSE A.MENGE END) * ( CASE WHEN B.PRCTYP IS NULL THEN 1 WHEN B.PRCTYP='D' THEN 0 ELSE B.MENGE END)
      ) INTO P_A_MENGE FROM (

         SELECT * FROM (
             SELECT MANDT ,WOKNUM ,MATNR ,IDNRK ,MENGE ,PRCTYP ,
             ROW_NUMBER() OVER(PARTITION BY MANDT,WOKNUM,MATNR,IDNRK, ITEM_SEQ,MATKL
              ORDER BY MANDT,WOKNUM,MATNR,IDNRK, ITEM_SEQ,CRDAT DESC,SEQNO DESC
          ) ROWNO FROM SAPHEE.ZPPT004 WHERE MANDT = P_MANDT
          AND WOKNUM = P_WBS
          AND IDNRK = P_MATNR )  A
          WHERE ROWNO=1

      ) A
      LEFT OUTER JOIN (
          SELECT * FROM (  SELECT MANDT ,WOKNUM ,MATNR ,IDNRK ,MENGE ,PRCTYP ,
           ROW_NUMBER() OVER(PARTITION BY MANDT,WOKNUM,MATNR,
          IDNRK,ITEM_SEQ,MATKL ORDER BY MANDT,WOKNUM,MATNR,IDNRK ,ITEM_SEQ,CRDAT DESC,SEQNO DESC
          )  ROWNO FROM SAPHEE.ZPPT004  A
      WHERE MANDT = P_MANDT AND WOKNUM = P_WBS
      AND EXISTS ( SELECT 'X' FROM SAPHEE.ZPPT004 B  WHERE B.MANDT = P_MANDT
      AND B.WOKNUM = P_WBS
      AND B.IDNRK = P_MATNR
      AND B.MATNR = A.IDNRK )  ) B WHERE ROWNO=1
  ) B ON B.MANDT = A.MANDT AND B.WOKNUM= A.WOKNUM AND B.IDNRK = A.MATNR ;
 
  SELECT SUM(MENGE)  INTO P_B_MENGE FROM SAPHEE.ZMMT013 WHERE MANDT = P_MANDT
  AND POSID = P_POSID AND MATNR = P_MATNR AND DDATE = '00000000'  AND WERKS = '1000';

  SELECT SUM(MENGE) INTO P_C_MENGE FROM SAPHEE.ZMMT013
  WHERE MANDT = P_MANDT AND EBELN = IN_EBELN AND EBELP = IN_EBELP
  AND DDATE = '00000000'     AND WERKS = '1000';

  SELECT  ( NVL(P_A_MENGE,0) - CASE WHEN P_A_MENGE IS NULL THEN 0 ELSE NVL(P_B_MENGE,0) + 0 END + NVL(P_C_MENGE,0)) INTO RT
  FROM DUAL;

RETURN RT;

END;
/