ProTech-Online.com

Microsoft SQL code example to pull Syteline receiving information.

USE [testsite_live_App]
GO
/****** Object:  StoredProcedure [MYSCHEMA].[_chad_receiving_report]    Script Date: 06/24/2013 23:22:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_receiving_report]
      @StartDate DateTime
      ,@EndDate DateTime
AS
BEGIN
      SET NOCOUNT ON;
      SET @EndDate = DATEADD(D,1,@EndDate)
      SELECT porc.rcvd_date AS RcvdDate
            ,po.vend_num AS PoVendNum
            ,venadd.name AS VenaddName
            ,porc.po_num AS PoNum
            ,po.CreatedBy AS CreatedBy
            ,porc.po_line AS PoLine
            ,poi.item AS PoItem
            ,poi.description AS PoiDescription
            ,ISNULL(dbo.UomConvQty(porc.qty_received
                  , ISNULL(dbo.Getumcf(poi.u_m, poi.item, porc.vend_num, 'P'), 1)
                  ,'From Base'), 0) AS DerQtyReceivedConv
            ,SpecNotes.NoteContent AS NoteContent
      FROM po_rcpt AS porc
            LEFT OUTER JOIN po AS po
                  ON po.po_num = porc.po_num
            LEFT OUTER JOIN vendaddr AS venadd
                  ON venadd.vend_num = po.vend_num
            LEFT OUTER JOIN poitem AS poi
                  ON poi.po_num = porc.po_num
                        AND poi.po_line = porc.po_line
                        AND poi.po_release = porc.po_release
            LEFT OUTER JOIN ObjectNotes AS ObjNote
                  ON ObjNote.RefRowPointer = poi.RowPointer
            LEFT OUTER JOIN SpecificNotes AS SpecNotes
                  ON SpecNotes.SpecificNoteToken = ObjNote.SpecificNoteToken
      WHERE porc.rcvd_date BETWEEN @StartDate AND @EndDate
            AND po.stat <> 'H'

END