@DbViewRowLevelSecurity NotRequired
@Overtake Core
VIEW Project_Access_On_Act_Level IS
SELECT sp.project_id                  project_id,
       sp.sub_project_id              sub_project_id,
       a.activity_seq                 activity_seq,
       a.rowid                        objid
FROM   SUB_PROJECT_TAB sp, ACTIVITY_TAB a
WHERE  a.project_id     = sp.project_id
AND    a.sub_project_id = sp.sub_project_id
AND    a.node_type = 'ACTIVITY'
AND ((EXISTS (SELECT 1 FROM PROJECT_TAB P
              WHERE  p.access_on_off   = 0
              AND    p.project_id      = sp.project_id))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f
              WHERE f.Fnd_user = FND_SESSION_API.Get_App_Owner))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f, Project_tab p, Person_Info_tab pi
              WHERE  p.access_on_off   = 1
              AND    p.project_id      = sp.project_id
              AND    pi.person_id      = p.manager
              AND    pi.user_id        = F.Fnd_User))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f, project_access_person_tab pap, person_info_tab pi
              WHERE pi.user_id         = f.Fnd_User
              AND   pap.person_id      = pi.person_id
              AND   pap.project_id     = sp.project_id
              AND   pap.sub_project_id = sp.sub_project_id))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f, project_access_team_tab pat, person_info_tab pi, team_member_tab tm
              WHERE pi.user_id         = f.Fnd_User
              AND   pat.project_id     = sp.project_id
              AND   pat.sub_project_id = sp.sub_project_id
              AND   pat.team_id        = tm.team_id
              AND   tm.person_id       = pi.person_id))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f, project_access_team_tab pat, person_info_tab pi, company_emp_tab ce, team_member_tab tm
              WHERE pi.user_id         = f.Fnd_User
              AND   pat.project_id     = sp.project_id
              AND   pat.sub_project_id = sp.sub_project_id
              AND   pat.team_id        = tm.team_id
              AND   ce.company         = tm.company
              AND   tm.employee_id     = '%'
              AND   ce.person_id       = pi.person_id))
OR   (EXISTS (SELECT 1 FROM Fnd_Session f, project_access_team_tab pat, person_info_tab pi, company_emp_tab ce, team_member_tab tm
              WHERE pi.user_id         = f.Fnd_User
              AND   pat.project_id     = sp.project_id
              AND   pat.sub_project_id = sp.sub_project_id
              AND   pat.team_id        = tm.team_id
              AND   ce.company         = tm.company
              AND   tm.employee_id     = '%'
              AND   ce.person_id       = pi.person_id))
);