SELECT *
FROM   Shop_Ord
WHERE  Contract IN (SELECT Contract
                    FROM   Site_Public
                    WHERE  Contract = User_Allowed_Site_aPi.Authorized(Contract))
       AND Order_Code_db IN ('M',
                             'F',
                             'P')
       AND (EXISTS (SELECT 1
                    FROM   Shop_Material_Alloc sMa
                    WHERE  (qty_Issued + sMa.qty_Assigned) < qty_Required
                           AND Shop_Ord.Release_No = sMa.Release_No
                           AND Shop_Ord.Order_No = sMa.Order_No
                           AND (sMa.Part_No IN (SELECT Inventory_Part_Location.Part_No
                                                FROM   Inventory_Part_Location
                                                WHERE  Inventory_Part_Location.qty_OnHand - Inventory_Part_Location.qty_Reserved > 0)))
            AND (objState != 'Closed')
            AND (Revised_Due_Date < SYSDATE + 30))

SELECT Contract
FROM   Site_Public
WHERE  Contract = User_Allowed_Site_aPi.Authorized(Contract));