Skip to Content
0
Former Member
Jan 07, 2011 at 08:42 AM

Find approval date

460 Views

When item is purchased ,the GRPO has to be approved by the admin.I want to find the approval date and number of days required for approving a GRPO.But I can not relate the GRPO table and approval table i.e.OPDN and OWDD.I have used this query but it returns wrong result

SELECT  S1.SERIESNAME + '-' + CAST(OPOR.DOCNUM AS VARCHAR)  'PONUM',OPOR.DOCDATE 'PODATE',S2.SERIESNAME + '-' + CAST(OPDN.DOCNUM AS  VARCHAR)'GRPONUM',OPDN.DocDate 'GRPODATE', POR1.ITEMCODE + ' - ' + POR1.DSCRIPTION 'DSCRIPTION',POR1.QUANTITY,PDN1.OPENQTY, WDD1.UPDATEDATE,DATEDIFF(DD,OPDN.DocDate,WDD1.UPDATEDATE) AS  DAYS,OPOR.CARDCODE,OPOR.CARDNAME,(CASE WHEN OPDN.DOCSTATUS='O' THEN 'Open' ELSE 'Closed' END)'STATUS' FROM OPOR INNER JOIN POR1 ON OPOR.DOCENTRY=POR1.DOCENTRY  INNER JOIN PDN1 ON PDN1.BASEENTRY = POR1.DOCENTRY AND  PDN1.BASELINE  = POR1.LINENUM  INNER JOIN OPDN ON OPDN.DOCENTRY=PDN1.DOCENTRY  INNER JOIN OWDD ON OWDD.DOCENTRY=OPOR.DOCENTRY  INNER JOIN WDD1 ON WDD1.WDDCODE=OWDD.WDDCODE  INNER JOIN NNM1 S1 ON S1.SERIES=OPOR.SERIES  INNER JOIN NNM1 S2 ON S2.SERIES=OPDN.SERIES