Archives

SQL Script – Purchase Order Status/History (RPPSTA)


                SELECT POM.fcompany AS [Vendor],
                       POM.fpono AS [PO No],
                       POM.fstatus AS [Status],
                       POI.fac AS [Facility],
                       POI.fpartno AS [Part No],
                       POI.frev AS [Part Rev],
                       CASE
                          WHEN INM.fluseudrev IS NOT NULL AND INM.fluseudrev = 1
                          THEN
                             POI.fcudrev
                          ELSE
                             POI.frev
                       END
                          AS [Disp Rev],
                       POI.fcategory AS [Category],
                       POI.fdescript AS [Part Desc],
                       POI.flstpdate AS [Last Prom Dte],
                       POI.fordqty AS [Order Qty],
                       POI.frcpqty AS [Recv Qty],
                       POI.fucost AS [Unit Cost],
                       POI.fitemno AS [Item No],
                       POI.frcpdate [Recv Date],
                       POM.fcsnphone AS [Phone],
                       POM.fporev AS [PO Rev],
                       POI.frelsno AS [Rels No],
                       POI.fjokey AS [JO Key],
                       POI.fsokey AS [SO Key],
                       POM.fmethod AS [PO Create Method],
                       POM.fccurid AS [Trans Curr],
                       POI.fmultirls AS [Multi Rels],
                       POM.fcsncountr AS [Country]
                  FROM poitem POI
                       JOIN pomast POM
                       ON POM.fpono = POI.fpono
                       LEFT OUTER JOIN inmast INM
                       ON     INM.fac = POI.fac
                          AND INM.fpartno = POI.fpartno
                          AND INM.frev = POI.frev
                 WHERE (ltrim (rtrim (POI.fmultirls)) = 'N'
                        OR CAST (POI.frelsno AS INTEGER) > 0)
                       AND ltrim (rtrim ( (POM.fstatus))) <> 'CANCELLED'

Related posts:

1 comment to SQL Script – Purchase Order Status/History (RPPSTA)

  • Bottomscratcher

    Not sure what the purpose of this post is. Perhaps it’s a bad example of the use of ltrim(rtrim()). Why would you use ltrim(rtrim (anything)) on a single character field?

    Even though the PoMast.fStatus field is 20 characters, I get the same result set when I use

    select PoMast.fPoNo
    from PoMast
    where ltrim(rtrim(PoMast.fStatus)) = ‘open’

    as I do when using

    select PoMast.fPoNo
    from PoMast
    where Pomast.fStatus = ‘open’

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>