CREATE  FUNCTION apps.SGDF_NEXT_SUM_AMOUNT(
     V_CUSTOMER_NUMBER IN VARCHAR2
   , V_ITEM  IN VARCHAR2
   , V_CURR  IN VARCHAR2
   , V_MONTH IN VARCHAR2
   )
   
 RETURN NUMBER IS SUM_AMOUNT NUMBER :=0 ;
 
BEGIN

  SELECT
    SUM( case when otnl.NAME like 'LINE 退貨%'
          or otnl.NAME like'LINE 銷貨折讓%'
          or otnl.NAME like'LINE_折讓%'
          or otnl.NAME like'LINE_退貨%'
          or otnl.NAME = 'LINE_模具訂單折讓'
          or otnl.NAME = 'LINE_重工費收入(折讓)'
          or otnl.NAME like'LINE%- 調帳(-)'
       then -(L.ordered_quantity)
       else L.ordered_quantity end  *L.UNIT_SELLING_PRICE ) INTO SUM_AMOUNT
  
      FROM   ONT.OE_ORDER_LINES_ALL L ,
             ONT.OE_ORDER_HEADERS_ALL H,
             INV.MTL_SYSTEM_ITEMS_B MSI,
             ONT.OE_TRANSACTION_TYPES_TL O,
             APPS.RA_CUSTOMERS RC,
             ONT.OE_TRANSACTION_TYPES_TL OTNL
            
     WHERE     H.HEADER_ID = L.HEADER_ID
           AND NVL(L.CANCELLED_FLAG,'N')<>'Y'
           AND NVL(H.CANCELLED_FLAG,'N')<>'Y'
           AND O.TRANSACTION_TYPE_ID IN (2855,2859,2873,2875)
       --    AND (O.NAME LIKE 'SGD DG%' OR O.NAME LIKE 'SGD TW%MST%')
            AND L.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
            AND H.ORDER_TYPE_ID = O.TRANSACTION_TYPE_ID
            AND MSI.ORGANIZATION_ID = 425
           
            AND H.FLOW_STATUS_CODE<>'CANCELLED'
            AND L.FLOW_STATUS_CODE<>'CANCELLED'
            AND L.CANCELLED_FLAG !='Y'
            AND H.SOLD_TO_ORG_ID = RC.CUSTOMER_ID
            AND L.line_type_id = otnl.transaction_type_id(+)
           
            AND TO_CHAR(H.CREATION_DATE,'YYYY-MM')=V_MONTH -- DECODE( V_MONTH,'未下單','',SUBSTR(V_MONTH,1,7) )
            AND H.TRANSACTIONAL_CURR_CODE= V_CURR 
            AND MSI.SEGMENT1= V_ITEM
            AND RC.CUSTOMER_NUMBER = V_CUSTOMER_NUMBER ;
           
   RETURN(SUM_AMOUNT);
  
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RETURN '0';
  
END SGDF_NEXT_SUM_AMOUNT ;