USE [ALPLA]

SELECT Distinct r.[POPRequisitionNumber] as req, 
r.[ApprovalStatus] as approvalStatus,
r.[Requested By] requestedBy,
format(t.[Created Date], 'yyyy-MM-dd') as  createdAt,
format(r.[Requisition Date], 'MM/dd/yyyy') as expectedDate,
r.[Requisition Amount] as glAccount,
case when r.[Account Segment 2] is null or r.[Account Segment 2] = '' then '999' else cast(r.[Account Segment 2] as varchar) end as plant
,t.Status as status
,t.[Document Status] as docStatus
,t.[Workflow Status] as reqState
,CASE 
    WHEN [Workflow Status] = 'Completed' 
        THEN 'Pending APO convertion'
    WHEN [Workflow Status] = 'Pending User Action' 
         AND r.[ApprovalStatus] = 'Pending Approval' 
        THEN 'Pending plant approver'
   WHEN [Workflow Status] = '' 
         AND r.[ApprovalStatus] = 'Not Submitted' 
        THEN 'Req not submited'
    ELSE 'Unknown reason' 
END AS approvedStatus

FROM [dbo].[PORequisitions] r  (nolock)



left join 
[dbo].[PurchaseRequisitions] as t (nolock) on
t.[Requisition Number] = r.[POPRequisitionNumber]


--where ApprovalStatus = 'Pending Approval'
--and [Account Segment 2] = 80

where r.POPRequisitionNumber in ([reqsToCheck])

Order By r.POPRequisitionNumber