Thursday, July 21, 2016

Query to find Requisitions Approval Pending

Subject:Requisition Approval Pending more than 30 Days

Select   Q.org,Q.Requestor_Name, Q.Status, Count (*) No_Of_Lines
    From (Select Prla.Destination_Organization_Id,
(SELECT ORGANIZATION_CODE FROM apps.org_organization_definitions
                         where ORGANIZATION_ID=Prla.Destination_Organization_Id)Org,
            (Select Last_Name
                    From Apps.Per_All_People_F Papf
                   Where Papf.Person_Id = Prla.To_Person_Id
                     And Sysdate Between Effective_Start_Date
                                     And Effective_End_Date) Requestor_Name,
                 Prha.Authorization_Status Status
            From Apps.Po_Requisition_Headers_All Prha,
                 Apps.Po_Requisition_Lines_All Prla
           Where Prha.Requisition_Header_Id = Prla.Requisition_Header_Id
             And Prha.Creation_Date < Sysdate - 30
             And Prha.Authorization_Status Not In ('APPROVED', 'CANCELLED')
             And Nvl (Prla.Closed_Code, 'OPEN') <> 'FINALLY CLOSED'
             And Prla.Destination_Organization_Id = :P_Organization_Id
             And Prha.Org_Id = 687) Q
Group By Q.org,Q.Requestor_Name, Q.Status
Order By Q.org,Q.Requestor_Name, Q.Status

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...