eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_RS_CONC_WF_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 112


      SELECT team.team_id, team.start_date_active, team.end_date_active
      FROM   jtf_rs_teams_b team, wf_local_user_roles wlur
      WHERE  NVL(TRUNC(team.end_date_active),l_sysdate) < l_sysdate
      AND    wlur.role_orig_system_id = team.team_id
      AND    wlur.role_orig_system    = l_team_orig_system
      AND    wlur.role_name           = l_team_orig_system||':'||to_char(team.team_id)
      AND    NVL(TRUNC(wlur.expiration_date),l_sysdate) >= l_sysdate;

Line: 126


      SELECT team.team_id, team.team_name, team.email_address
            ,team.start_date_active, team.end_date_active
      FROM   jtf_rs_teams_vl team, wf_local_roles wlr
      WHERE  NVL(TRUNC(team.end_date_active),l_sysdate) < l_sysdate
      AND    wlr.orig_system_id = team.team_id
      AND    wlr.orig_system    = l_team_orig_system
      AND    wlr.name           = l_team_orig_system||':'||to_char(team.team_id)
      AND    (wlr.status  = l_active
              OR NVL(TRUNC(wlr.expiration_date),l_sysdate) >= l_sysdate);

Line: 142


      SELECT team.team_id, team.team_name, team.email_address
            ,team.end_date_active, team.start_date_active
            ,wlr.start_date wlr_start_date, wlr.expiration_date wlr_exp_date
      FROM   jtf_rs_teams_vl team, wf_local_roles wlr
      WHERE  NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
      AND    wlr.orig_system_id = team.team_id
      AND    wlr.orig_system    = l_team_orig_system
      AND    wlr.name           = l_team_orig_system||':'||to_char(team.team_id)
      AND    (wlr.display_name <> team.team_name      OR
              NVL(wlr.email_address, l_no_email)
              <> NVL(team.email_address, l_no_email)  OR
              wlr.start_date IS NULL OR wlr.start_date <> team.start_date_active OR
              (wlr.expiration_date IS NULL AND team.end_date_active IS NOT NULL)   OR
              (wlr.expiration_date IS NOT NULL AND team.end_date_active IS NULL)   OR
              wlr.expiration_date <> team.end_date_active);

Line: 164


      SELECT team.team_id, team.start_date_active, team.end_date_active
      FROM   jtf_rs_teams_b team, wf_local_user_roles wlur
      WHERE  NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
      AND    wlur.role_orig_system_id =  team.team_id
      AND    wlur.role_orig_system    = l_team_orig_system
      AND    wlur.role_name           = l_team_orig_system||':'||to_char(team.team_id)
      AND    (wlur.start_date IS NULL OR wlur.start_date <> team.start_date_active OR
              (wlur.expiration_date IS NULL AND team.end_date_active IS NOT NULL)   OR
              (wlur.expiration_date IS NOT NULL AND team.end_date_active IS NULL)   OR
              wlur.expiration_date <> team.end_date_active);

Line: 180


      SELECT team.team_id, team.team_name, team.email_address
            ,team.end_date_active, team.start_date_active
      FROM   jtf_rs_teams_vl team
      WHERE  NVL(TRUNC(team.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_roles wlr
                        WHERE wlr.orig_system_id = team.team_id
                        AND   wlr.orig_system    = l_team_orig_system
                        AND   wlr.name           = l_team_orig_system||':'||to_char(team.team_id));

Line: 259


        l_list.DELETE;

Line: 263


          l_list.DELETE;

Line: 298


       l_list.DELETE;

Line: 302


          l_list.DELETE;

Line: 391


       l_list.DELETE;

Line: 395


          l_list.DELETE;

Line: 451


      SELECT grp.group_id, grp.start_date_active, grp.end_date_active
      FROM   jtf_rs_groups_b grp, wf_local_user_roles wlur
      WHERE  NVL(TRUNC(grp.end_date_active),l_sysdate) < l_sysdate
      AND    ((wlur.role_orig_system_id  =  grp.group_id
               AND wlur.role_orig_system = l_grp_orig_system
               AND wlur.role_name        = l_grp_orig_system||':'||to_char(grp.group_id))
              OR
              (wlur.user_orig_system_id  =  grp.group_id
               AND wlur.user_orig_system = l_grp_orig_system
               AND wlur.user_name        = l_grp_orig_system||':'||to_char(grp.group_id)))
      AND    NVL(TRUNC(wlur.expiration_date),l_sysdate) >= l_sysdate;

Line: 470


      SELECT /*+ use_hash(grp.t) use_hash(grp.b) use_hash(wlr) parallel(grp) parallel(wlr) */
             grp.group_id, grp.group_name, grp.email_address
            ,grp.start_date_active, grp.end_date_active
      FROM   jtf_rs_groups_vl grp, wf_local_roles wlr
      WHERE  NVL(TRUNC(grp.end_date_active),l_sysdate) < l_sysdate
      AND    wlr.orig_system_id = grp.group_id
      AND    wlr.orig_system    = l_grp_orig_system
      AND    wlr.name           = l_grp_orig_system||':'||to_char(grp.group_id)
      AND    (wlr.status        = l_active
              OR NVL(TRUNC(wlr.expiration_date),l_sysdate) >= l_sysdate);

Line: 488


      SELECT /*+ use_hash(grp.t) use_hash(grp.b) use_hash(wlr) parallel(grp) parallel(wlr) */
             grp.group_id, grp.group_name, grp.email_address
            ,grp.end_date_active, grp.start_date_active
            ,wlr.start_date wlr_start_date, wlr.expiration_date wlr_exp_date
      FROM   jtf_rs_groups_vl grp, wf_local_roles wlr
      WHERE  NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
      AND    wlr.orig_system_id = grp.group_id
      AND    wlr.orig_system    = l_grp_orig_system
      AND    wlr.name           = l_grp_orig_system||':'||to_char(grp.group_id)
      AND    (wlr.display_name <> grp.group_name      OR
              NVL(wlr.email_address, l_no_email)
              <> NVL(grp.email_address, l_no_email)   OR
              (wlr.start_date IS NULL OR wlr.start_date <> grp.start_date_active) OR
              (wlr.expiration_date is null AND grp.end_date_active is not null) OR
              (wlr.expiration_date is not null AND grp.end_date_active is null) OR
              wlr.expiration_date <> grp.end_date_active);

Line: 511


      SELECT grp.group_id, grp.start_date_active, grp.end_date_active
      FROM   jtf_rs_groups_b grp, wf_local_user_roles wlur
      WHERE  NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
      AND    ((wlur.role_orig_system_id  = grp.group_id
               AND wlur.role_orig_system = l_grp_orig_system
               AND wlur.role_name        = l_grp_orig_system||':'||to_char(grp.group_id))
              OR
              (wlur.user_orig_system_id  = grp.group_id
               AND wlur.user_orig_system = l_grp_orig_system
               AND wlur.user_name        = l_grp_orig_system||':'||to_char(grp.group_id)))
      AND    (wlur.start_date IS NULL OR wlur.start_date <> grp.start_date_active OR
              (wlur.expiration_date IS NULL AND grp.end_date_active IS NOT NULL)   OR
              (wlur.expiration_date IS NOT NULL AND grp.end_date_active IS NULL)   OR
              wlur.expiration_date <> grp.end_date_active);

Line: 532


      SELECT /*+ use_hash(grp.t) use_hash(grp.b) parallel(grp) */
             grp.group_id, grp.group_name, grp.email_address
            ,grp.end_date_active, grp.start_date_active
      FROM   jtf_rs_groups_vl grp
      WHERE  NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_roles wlr
                        WHERE wlr.orig_system    = l_grp_orig_system
                        AND   wlr.orig_system_id = grp.group_id
                        AND   wlr.name           = l_grp_orig_system||':'||to_char(group_id));

Line: 612


         l_list.DELETE;

Line: 616


            l_list.DELETE;

Line: 651


       l_list.DELETE;

Line: 655


          l_list.DELETE;

Line: 743


       l_list.DELETE;

Line: 747


          l_list.DELETE;

Line: 797


      SELECT resource_id, category, start_date_active, end_date_active
            ,source_id, source_email, resource_name
      FROM jtf_rs_resource_extns_vl;

Line: 802


      SELECT role_orig_system_id, role_orig_system, role_name
            ,start_date, expiration_date
      FROM   wf_local_user_roles
      WHERE  user_orig_system_id = l_user_orig_system_id
      AND    user_orig_system    = l_user_orig_system
      AND    user_name           = l_user_name
      AND    NVL (expiration_date, l_sysdate) >= l_sysdate;

Line: 811


      SELECT role_orig_system_id, role_orig_system, role_name
            ,start_date, expiration_date
      FROM   wf_local_user_roles
      WHERE  user_orig_system_id = l_user_orig_system_id
      AND    user_orig_system    = l_user_orig_system
      AND    user_name           = l_user_name
      AND    role_orig_system IN ('JRES_IND','JRES_GRP','JRES_TEAM')
      AND    NVL (expiration_date, l_sysdate) >= l_sysdate;

Line: 821


      SELECT display_name, email_address, start_date, expiration_date
      FROM  wf_local_roles
      WHERE orig_system_id = l_orig_system_id
      AND   orig_system    = l_orig_system
      AND   name           = l_name
      AND   NVL (expiration_date, l_sysdate) >= l_sysdate;

Line: 896


              l_list.DELETE;

Line: 899


                l_list.DELETE;

Line: 951


              l_list.DELETE;

Line: 954


                l_list.DELETE;

Line: 987


            ELSE -- Its a member record. Update with the correct greatest and least dates.
              l_start_date := greatest (i.start_date_active, NVL (c.start_date, i.start_date_active));

Line: 1048


                l_list.DELETE;

Line: 1054


                  l_list.DELETE;

Line: 1079


              l_list.DELETE;

Line: 1082


                l_list.DELETE;

Line: 1130


      SELECT wlur.user_orig_system, wlur.user_orig_system_id, mem.group_id,mem.resource_id
      FROM   wf_local_user_roles wlur, jtf_rs_group_members mem
      WHERE  NVL (mem.delete_flag,'N') = 'Y'
      AND    wlur.role_orig_system     =  l_grp_orig_system
      AND    wlur.role_orig_system_id  =  mem.group_id
      AND    wlur.role_name            =  l_grp_orig_system ||':'|| mem.group_id
      AND    wlur.user_orig_system     <> l_grp_orig_system
      AND    NVL(wlur.expiration_date, l_sysdate) >= l_sysdate;

Line: 1146


      SELECT /*+ use_hash(ext) use_hash(mem) use_hash(grp) parallel(ext)
                 parallel(mem) parallel(grp) */
             mem.resource_id, mem.group_id
            ,greatest (ext.start_date_active, grp.start_date_active) m_start_date_active
            ,least (NVL (grp.end_date_active, l_fnd_date) ,
                    NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
      FROM   jtf_rs_resource_extns_vl ext, jtf_rs_group_members mem, jtf_rs_groups_b grp
      WHERE  mem.resource_id = ext.resource_id
      AND    mem.group_id    = grp.group_id
      AND    ext.category IN ('OTHER','TBH')
      AND    ext.resource_name IS NOT NULL
      AND    NVL (mem.delete_flag,'N') <> 'Y'
      AND    NVL(TRUNC(ext.end_date_active),l_sysdate) >= l_sysdate
      AND    NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
                      WHERE wlur.user_orig_system_id = mem.resource_id
                      AND   wlur.role_orig_system_id = mem.group_id
                      AND   wlur.user_orig_system    = l_ind_orig_system
                      AND   wlur.role_orig_system    = l_grp_orig_system);

Line: 1174


      SELECT /*+ use_hash(ext) use_hash(mem) use_hash(grp) parallel(ext)
                 parallel(mem) parallel(grp) */
             mem.resource_id, mem.group_id, ext.category, ext.resource_name
            ,greatest (ext.start_date_active, grp.start_date_active) m_start_date_active
            ,least (NVL (grp.end_date_active, l_fnd_date) ,
                    NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
      FROM   jtf_rs_resource_extns_vl ext, jtf_rs_group_members mem, jtf_rs_groups_b grp
      WHERE  mem.resource_id = ext.resource_id
      AND    mem.group_id    = grp.group_id
      AND    ext.category IN ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
      AND    NVL (mem.delete_flag,'N') <> 'Y'
      AND    ext.resource_name IS NOT NULL
      AND    NVL(TRUNC(ext.end_date_active),l_sysdate) >= l_sysdate
      AND    NVL(TRUNC(grp.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
                      WHERE wlur.role_orig_system_id = mem.group_id
                      AND   wlur.user_orig_system    = l_hz_orig_system
                      AND   wlur.role_orig_system    = l_grp_orig_system);

Line: 1406


      SELECT wlur.user_orig_system, wlur.user_orig_system_id, mem.team_id
      FROM   wf_local_user_roles wlur, jtf_rs_team_members mem
      WHERE  NVL (mem.delete_flag,'N') = 'Y'
      AND    wlur.role_orig_system     =  l_team_orig_system
      AND    wlur.role_orig_system_id  =  mem.team_id
      AND    wlur.role_name            =  l_team_orig_system ||':'|| mem.team_id
      AND    wlur.user_orig_system     <> l_team_orig_system
      AND    NVL(wlur.expiration_date, l_sysdate) >= l_sysdate;

Line: 1423


      SELECT mem.team_resource_id resource_id, mem.team_id
            ,greatest (team.start_date_active, ext.start_date_active) m_start_date_active
            ,least (NVL (team.end_date_active, l_fnd_date) ,
                    NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
      FROM   jtf_rs_resource_extns_vl ext, jtf_rs_team_members mem, jtf_rs_teams_b team
      WHERE  NVL (mem.delete_flag,'N') <> 'Y'
      AND    mem.team_resource_id = ext.resource_id
      AND    mem.resource_type    = 'INDIVIDUAL'
      AND    mem.team_id          = team.team_id
      AND    ext.category IN ('OTHER','TBH')
      AND    ext.resource_name IS NOT NULL
      AND  NVL (TRUNC (ext.end_date_active),l_sysdate)  >= l_sysdate
      AND  NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
                      WHERE mem.resource_type        = 'INDIVIDUAL'
                      AND wlur.user_orig_system_id   = mem.team_resource_id
                      AND   wlur.user_orig_system    = l_ind_orig_system
                      AND   wlur.role_orig_system_id = mem.team_id
                      AND   wlur.role_orig_system    = l_team_orig_system);

Line: 1451


      SELECT mem.team_resource_id resource_id, mem.team_id, ext.category, ext.resource_name
            ,greatest (team.start_date_active, ext.start_date_active) m_start_date_active
            ,least (NVL (team.end_date_active, l_fnd_date) ,
                    NVL (ext.end_date_active, l_fnd_date)) m_end_date_active
      FROM   jtf_rs_resource_extns_vl ext, jtf_rs_team_members mem, jtf_rs_teams_b team
      WHERE  NVL (mem.delete_flag,'N') <> 'Y'
      AND    mem.team_resource_id = ext.resource_id
      AND    mem.resource_type    = 'INDIVIDUAL'
      AND    mem.team_id          = team.team_id
      AND    ext.resource_name IS NOT NULL
      AND    ext.category IN ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
      AND  NVL (TRUNC (ext.end_date_active),l_sysdate)  >= l_sysdate
      AND  NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
                      WHERE mem.resource_type        = 'INDIVIDUAL'
                      AND   wlur.role_orig_system_id = mem.team_id
                      AND   wlur.role_orig_system    = l_team_orig_system
                      AND   wlur.user_orig_system    = l_hz_orig_system);

Line: 1477


      SELECT mem.team_resource_id group_id, mem.team_id
            ,greatest (team.start_date_active, grp.start_date_active) m_start_date_active
            ,least (NVL (team.end_date_active, l_fnd_date) ,
                    NVL (grp.end_date_active, l_fnd_date)) m_end_date_active
      FROM   jtf_rs_groups_b grp, jtf_rs_team_members mem, jtf_rs_teams_b team
      WHERE  NVL (mem.delete_flag,'N') <> 'Y'
      AND    mem.team_resource_id = grp.group_id
      AND    mem.resource_type    = 'GROUP'
      AND    mem.team_id          = team.team_id
      AND  NVL (TRUNC (grp.end_date_active),l_sysdate)  >= l_sysdate
      AND  NVL (TRUNC (team.end_date_active),l_sysdate) >= l_sysdate
      AND NOT EXISTS (SELECT 1 FROM wf_local_user_roles wlur
                      WHERE mem.resource_type        = 'GROUP'
                      AND   wlur.user_orig_system_id = mem.team_resource_id
                      AND   wlur.user_orig_system    = l_grp_orig_system
                      AND   wlur.role_orig_system_id = mem.team_id
                      AND   wlur.role_orig_system    = l_team_orig_system);