The following is the copy of my SP. Could you enlighten me with the grouping function in SP? i am not sure where should I start. Thank you very much.
CREATE PROCEDURE dbo.dt_receiver21
@recNumber varchar(30),
@POSTED int -- 0 not posted, 1 posted
AS
if @POSTED = 0
SELECT JPUS.dbo.POP10310.POPRCTNM AS POPRCTNM,
JPUS.dbo.POP10310.RCPTLNNM AS RCPTLNNM,
JPUS.dbo.POP10310.PONUMBER AS PONUMBER,
JPUS.dbo.POP10310.ITEMNMBR AS ItemNumber,
JPUS.dbo.POP10310.ITEMDESC AS ItemDesc,
JPUS.dbo.POP10310.UMQTYINB AS UMQTYINB,
JPUS.dbo.POP10310.UNITCOST AS UnitCost,
JPUS.dbo.POP10310.EXTDCOST AS ExtCost,
JPUS.dbo.POP10300.VENDORID AS VendorID,
JPUS.dbo.POP10300.PYMTRMID AS PayMethod,
JPUS.dbo.POP10300.USER2ENT AS USER2ENT,
JPUS.dbo.POP10500.QTYSHPPD AS QtyShipped,
JPUS.dbo.PM00200.VENDNAME AS VendorName,
JPUS.dbo.PM00200.VNDCNTCT AS VendorContact,
JPUS.dbo.PM00200.ADDRESS1 AS VendorADD1,
JPUS.dbo.PM00200.ADDRESS2 AS VendorADD2,
JPUS.dbo.PM00200.CITY AS VendorCity,
JPUS.dbo.PM00200.STATE AS VendorState,
JPUS.dbo.PM00200.ZIPCODE AS VendorZip,
JPUS.dbo.PM00200.COUNTRY AS VendorCountry,
JPUS.dbo.PM00200.PHNUMBR1 AS PHONE,
JPUS.dbo.PM00200.FAXNUMBR AS FAX,
JPUS.dbo.PM00200.SHIPMTHD AS SHIPMTHD,
JPUS.dbo.POP10110.QTYORDER AS QTYORDER,
dbo.tblPoFile.PONUMBER AS PONUMBER,
dbo.tblPoFile.FILENUMBER AS FILENUMBER,
dbo.GP6purline.lineSeq as lineSeq,
dbo.GP6purline.application as app,
dbo.GP6purline.manufacturer as mang,
dbo.GP6purline.condition as con,
dbo.GP6purline.notes as notes,
dbo.GP6purline.docReq as docReq,
dbo.GP6purline.inspector as insp,
dbo.GP6purline.inspection_date as inspdate,
dbo.GP6purline.tagNumber as tag,
dbo.GP6purline.itemNumber as itemNo,
DYNAMICS.dbo.SY01400.USERNAME AS USERNAME,
OrderBy = ' '
FROM JPUS.dbo.POP10310 INNER JOIN JPUS.dbo.POP10300 ON JPUS.dbo.POP10310.POPRCTNM = JPUS.dbo.POP10300.POPRCTNM
INNER JOIN JPUS.dbo.POP10500 ON JPUS.dbo.POP10310.POPRCTNM = JPUS.dbo.POP10500.POPRCTNM
FULL JOIN DYNAMICS.dbo.SY01400 ON JPUS.dbo.POP10300.USER2ENT = DYNAMICS.dbo.SY01400.USERID
FULL JOIN JPUS.dbo.PM00200 ON JPUS.dbo.POP10300.VENDORID = JPUS.dbo.PM00200.VENDORID
FULL JOIN dbo.GP6purline ON JPUS.dbo.POP10310.PONUMBER = dbo.GP6purline.docNumber AND JPUS.dbo.POP10500.POLNENUM = dbo.GP6purline.lineSeq
INNER JOIN dbo.tblPoFile ON JPUS.dbo.POP10310.PONUMBER = dbo.tblPoFile.PONUMBER
INNER JOIN JPUS.dbo.POP10110 ON JPUS.dbo.POP10310.PONUMBER = JPUS.dbo.POP10110.PONUMBER
else --posted
SELECT JPUS.dbo.POP30390.POPRCTNM AS ReceiptNumber,
JPUS.dbo.POP30390.SEQNUMBR AS SEQNUMBR,
JPUS.dbo.POP30390.DISTTYPE AS DISTTYPE,
JPUS.dbo.POP30310.PONUMBER AS PONUMBER,
JPUS.dbo.POP30310.ITEMNMBR AS ITEMNUMBR,
JPUS.dbo.POP30310.ITEMDESC AS ITEMDESC,
JPUS.dbo.POP30310.ACTLSHIP AS ActualShipDate,
JPUS.dbo.POP30310.UNITCOST AS UNITCOST,
JPUS.dbo.POP30310.EXTDCOST AS EXTDCOST,
JPUS.dbo.POP30300.ACTLSHIP AS ActualShipDate2,
JPUS.dbo.POP30300.receiptdate AS ReceiptDate,
JPUS.dbo.POP30300.VENDORID AS VENDORID,
JPUS.dbo.POP30300.VENDNAME AS VENDNAME,
JPUS.dbo.POP30300.PYMTRMID AS PaymentMethod,
JPUS.dbo.POP30300.USER2ENT AS UserEnterID,
JPUS.dbo.POP30300.BACHNUMB AS BachNumber,
JPUS.dbo.IVC30101.INVCNMBR AS InvoiceNumber,
JPUS.dbo.IVC30101.DOCTYPE AS DocType,
JPUS.dbo.POP10110.QTYORDER AS QTYORDER,
JPUS.dbo.POP10110.UOFM AS UOFM,
JPUS.dbo.PM00200.VENDNAME AS VENDNAME,
JPUS.dbo.PM00200.VNDCNTCT AS VNDCNTCT,
JPUS.dbo.PM00200.ADDRESS1 AS ADD1,
JPUS.dbo.PM00200.ADDRESS2 AS ADD2,
JPUS.dbo.PM00200.CITY AS CITY,
JPUS.dbo.PM00200.STATE AS STATE,
JPUS.dbo.PM00200.ZIPCODE AS ZIPCODE,
JPUS.dbo.PM00200.COUNTRY AS COUNTRY,
JPUS.dbo.PM00200.PHNUMBR1 AS PHONE,
JPUS.dbo.PM00200.FAXNUMBR AS FAX,
JPUS.dbo.PM00200.SHIPMTHD AS SHIPMTHD,
JPUS.dbo.GL00105.ACTINDX AS AccountIndex,
JPUS.dbo.GL00105.ACTNUMST AS AccountNumber,
JPUS.dbo.POP10500.QTYSHPPD AS QTYSHIP,
JPUS.dbo.POP10500.DATERECD AS DateReceived,
JPUS.dbo.POP10500.NOTEINDX AS NoteIndex,
JPUS.dbo.SY03900.TXTFIELD AS NoteText,
dbo.tblPoFile.PONUMBER AS PONUMBER,
dbo.tblPoFile.FILENUMBER AS FILENUMBER,
dbo.GP6purline.lineSeq as lineSeq,
dbo.GP6purline.application as app,
dbo.GP6purline.manufacturer as mang,
dbo.GP6purline.condition as con,
dbo.GP6purline.notes as notes,
dbo.GP6purline.docReq as docReq,
dbo.GP6purline.inspector as insp,
dbo.GP6purline.inspection_date as inspdate,
dbo.GP6purline.tagNumber as tag,
dbo.GP6purline.itemNumber as itemNo,
DYNAMICS.dbo.SY01400.USERNAME AS USERNAME,
OrderBy = 'POSTED'
FROM JPUS.dbo.POP30390 INNER JOIN JPUS.dbo.POP30310 ON JPUS.dbo.POP30390.POPRCTNM = JPUS.dbo.POP30310.POPRCTNM
INNER JOIN JPUS.dbo.POP10500 ON JPUS.dbo.POP30390.POPRCTNM = JPUS.dbo.POP10500.POPRCTNM
FULL JOIN JPUS.dbo.SY03900 ON JPUS.dbo.POP10500.NOTEINDX = JPUS.dbo.SY03900.NOTEINDX
INNER JOIN JPUS.dbo.POP30300 ON JPUS.dbo.POP30310.POPRCTNM = JPUS.dbo.POP30300.POPRCTNM
FULL JOIN JPUS.dbo.IVC30101 ON JPUS.dbo.POP30300.BACHNUMB = JPUS.dbo.IVC30101.BACHNUMB --INNER JOIN find out exact matches
FULL JOIN JPUS.dbo.POP10110 ON JPUS.dbo.POP30310.PONUMBER = JPUS.dbo.POP10110.PONUMBER
INNER JOIN JPUS.dbo.GL00105 ON JPUS.dbo.POP30390.ACTINDX = JPUS.dbo.GL00105.ACTINDX
FULL JOIN dbo.tblPoFile ON JPUS.dbo.POP30310.PONUMBER = dbo.tblPoFile.PONUMBER
FULL JOIN dbo.GP6purline ON JPUS.dbo.POP30310.PONUMBER = dbo.GP6purline.docNumber AND JPUS.dbo.POP30390.SEQNUMBR = dbo.GP6purline.lineSeq
INNER JOIN JPUS.dbo.PM00200 ON JPUS.dbo.POP30300.VENDORID = JPUS.dbo.PM00200.VENDORID
INNER JOIN DYNAMICS.dbo.SY01400 ON JPUS.dbo.POP30300.USER2ENT = DYNAMICS.dbo.SY01400.USERID
GO