OrdNum   Location    Customer EnteredBy   OrderContract
A0001    L1          C001     1111        100-1
A0002    L2          C003     1111        10A-1
A0003    L3          C021     1111        120-1
A0004    L1          C041     1111        1B1-1
A0005    L2          C003     1111        1DD-2
A0005    L2          C013     1111        1DD-5
A0005    L2          C013     1111        1DA-5
A0005    L2          C013     1111        1DE-5
A0215    -1          C021     8790        120-1 --non allocated Order
A0415    -1          C123     8790        AA4-2 --non allocated Order

UserLocation
UserID          Location           AllLoc
8790             L1                    1
8790             L2                    0
8790             L3                    0

UserCust
UserID          Location           Customer      AllCont
8790             L2                    C003      1
8790             L2                    C013      0
8790             L3                    C021      0

UserCont
UserID          Location           Customer   AllCont
8790             L2                 C013      1DD-5
8790             L2                 C013      1DA-5
8790             L3                 C021      120-1

I have to display the OrderHistory based on the user logged in.
If you see all three tables have similar data but it permissions based.
The last column in these tables starting with 'All' if that is true then show all the contracts for that region for that user. If the last column is false then for that region see in UserCust table which customers user has access to. Show all the contracts for those customer where flag is true. If it is false then for that region + customer in AllCont table show only those contract listed. Show the data based on permissions. First Region then by region+customer and then by Region+customer+contract.
Also, if the user has entered that contract even if he doesn't have access but the EnteredBy = @UserID and Location = -1 then show those records also.

For the given example here would be the output-

A0001    L1      C001      100-1
A0004    L1      C041      1B1-1
A0002    L2      C003      10A-1
A0005    L2      C003      1DD-2
A0005    L2      C013      1DD-5
A0005    L2      C013      1DA-5
A0215    -1      C021      120-1 User has no access but entered cont.
A0415    -1      C123      AA4-2 User has no access but entered cont.

SELECT OH.OrdNum, OH.Location, OH.Customer, OH.OrderContract
  FROM OrdHistory AS OH 
       LEFT OUTER JOIN UserLocation AS UL 
               ON OH.Location = UL.Location 
       LEFT OUTER JOIN UserCust AS UC 
               ON UL.UserID = UC.UserID AND OH.Location = UC.Location
                                        AND OH.Customer = UC.Customer
       LEFT OUTER JOIN UserCont AS UCO
               ON UC.UserID = UCO.UserID AND OH.Location = UCO.Location 
                                         AND OH.Customer = UCO.Customer
                                         AND OH.OrderContract = UCO.AllCont
WHERE (OH.EnteredBy = 8790 AND OH.Location = '-1')
   OR (    UL.UserID = 8790
       AND UL.AllLoc IS NOT NULL
       AND (   UL.AllLoc = 1 
            OR (    UC.AllCont IS NOT NULL
                AND (   UC.AllCont = 1
                     OR UCO.AllCont IS NOT NULL))))
ORDER BY OH.Location, OH.Customer, OH.OrderContract, OH.OrdNum