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'