SELECT dbo.somast.[fcompany],
       dbo.somast.[fcustno],
       dbo.somast.[forderdate],
       sorels.fsono,
       dbo.somast.[fsorev],
       dbo.somast.[fcustpono],
       sorels.identity_column,
       sorels.fsono,
       sorels.fenumber,
       sorels.frelease,
       ( SORELS.fshipbook + SORELS.fshipbuy + sorels.fshipmake ) AS shipped,
       sorels.finvqty,
       sorels.forderqty,
       COALESCE( DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                            + sorels.finumber
                                            + sorels.frelease),
                 0 )
          AS SHIPPEDNOTINVOICED,
       ( SORELS.forderqty - SORELS.finvqty
       - COALESCE( DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                              + sorels.finumber
                                              + sorels.frelease),
                   0 ) )
     * SORELS.funetprice
          AS NewFNBOAMT
  FROM       SORELS
          JOIN
             SOMAST
          ON SOMAST.FSONO = SORELS.FSONO
       JOIN
          SOITEM
       ON SOITEM.FSONO = SORELS.FSONO AND SOITEM.FINUMBER = SORELS.FINUMBER
 WHERE SOMAST.FSTATUS = 'OPEN'
   AND FMASTERREL = 0
   AND somast.forderdate >= CONVERT( DATETIME, '01/01/2002' )

/****** Object: User-Defined Function [dbo].[GETSHIPPEDNOTINVOICEDQTY]   Script Date: 3/10/2009 2:55:13 PM ******/
USE [M2MData01];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [dbo].[GETSHIPPEDNOTINVOICEDQTY]
(@ItemKey varchar(12))
RETURNS numeric(20, 5)
AS
BEGIN
DECLARE @RetVal NUMERIC(20, 5)
    SELECT  @RetVal = sum( Shitem.fshipqty - Shitem.finvqty )
    FROM    shmast,
            shitem
            LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
                                AND inmast.frev = shitem.frev
                                AND inmast.fac = shitem.fac
    WHERE   shitem.fsokey = @ItemKey
            AND Shitem.fshipno = Shmast.fshipno
            AND fconfirm = 'Y'
            AND ftype <> 'JO'
            AND ftype <> 'VE'
            AND ftype <> 'MI'
            AND shitem.fitemtype <> 'M'
            AND flisinv = 0
            AND fcstatus <> 'C'
            AND LEN(Shitem.IDONO) = 0
            AND ( Shitem.fshipqty - Shitem.finvqty ) > 0
    RETURN @RetVal
END
GO


GRANT EXECUTE ON [dbo].[GETSHIPPEDNOTINVOICEDQTY] TO [CRServices];
GO
GRANT EXECUTE ON [dbo].[GETSHIPPEDNOTINVOICEDQTY] TO [Reports];
GO

CREATE FUNCTION dbo.GETSHIPPEDNOTINVOICEDQTY
(
    @ItemKey varchar(12)
)
RETURNS numeric(20, 5)
AS
BEGIN
    return (SELECT  sum( Shitem.fshipqty - Shitem.finvqty )
        FROM    shmast,
                shitem
               LEFT JOIN inmast ON inmast.fpartno = shitem.fpartno
                                AND inmast.frev = shitem.frev
                                AND inmast.fac = shitem.fac
    WHERE   shitem.fsokey = @ItemKey
            AND Shitem.fshipno = Shmast.fshipno
            AND fconfirm = 'Y'
            AND ftype not in ('JO', 'VE', 'MI')
            AND shitem.fitemtype <> 'M'
            AND flisinv = 0
            AND fcstatus <> 'C'
            AND Shitem.IDONO = ''
            AND Shitem.fshipqty > Shitem.finvqty 
    )
END

quote:

---

Originally posted by DavidChel

Thanks Peso, but won't that alter the database as well? Won't it save the UDF into the database?

---

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='GETSHIPPEDNOTINVOICEDQTY' AND ROUTINE_TYPE='FUNCTION') 
 DROP FUNCTION GETSHIPPEDNOTINVOICEDQTY

CREATE FUNCTION
..........