APPS.JTF_RS_GROUP_REPORT_PUB SQL Statements

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

Line: 8

   15-JUN-2004    nsinghai   added dummy parameters for selective enabling
                             disbaling of fields.

 *****************************************************************************/

 TYPE REL_RECORD_TYPE IS RECORD
  ( p_group_id           JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
    p_related_group_id   JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
    p_start_date_active  DATE,
    p_end_date_active    DATE,
    p_relation_start_date  DATE,
    p_relation_end_date    DATE,
    level                NUMBER);

Line: 43

  SELECT 1
  FROM FND_DESCRIPTIVE_FLEXS_VL
  WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'JTF_RS_GROUPS'
        AND FREEZE_FLEX_DEFINITION_FLAG = 'Y';

Line: 197

 * Hereafter code is inserted at appropriate places to invoke the above procedures.
 ***********************************************************************************************/




PROCEDURE query_group(ERRBUF  OUT NOCOPY VARCHAR2,
                      RETCODE OUT NOCOPY VARCHAR2,
                      P_REP_TYPE   IN  VARCHAR2 ,
                      P_DUMMY_1    IN VARCHAR2 ,
                      P_DUMMY_2    IN VARCHAR2 ,
                      P_DUMMY_3    IN VARCHAR2 ,
                      P_GROUP_ID   IN NUMBER ,
                      P_RES_ID     IN NUMBER ,
                      P_USAGE      IN VARCHAR2 ,
                      P_USR_NAME   IN VARCHAR2 ,
                      P_DATE_OPT   IN  VARCHAR2 ,
                      P_START_DATE IN varchar2 ,
                      P_END_DATE   IN varchar2 ,
                      P_NO_OF_DAYS IN number )
IS
begin
  query_group (ERRBUF, RETCODE, P_REP_TYPE, P_DUMMY_1, P_DUMMY_2, P_DUMMY_3,  P_GROUP_ID, P_RES_ID, P_USAGE, P_USR_NAME, P_DATE_OPT, P_START_DATE, P_END_DATE, P_NO_OF_DAYS, 'N') ;

Line: 255

 select /*+ INDEX(usg jtf_rs_group_usages_n1) */ group_id
  from  jtf_rs_group_usages usg
 where  usg.usage = p_usage
   and  not exists (select group_id
                     from  jtf_rs_grp_relations rel
                    where  rel.group_id = usg.group_id
                     and   nvl(delete_flag, 'N') <> 'Y') ;

Line: 265

 select /*+ INDEX(usg jtf_rs_group_usages_n1) */ group_id
  from  jtf_rs_group_usages usg
 where  usg.usage = p_usage
   and  usg.group_id = p_group_id;

Line: 275

    select
        meaning
    from
        fnd_lookups
    where  lookup_type = 'JTF_RS_USAGE'
    and lookup_code = p_usage;

Line: 286

  select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
       --: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
       g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
       --: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       ext.resource_number,
       ext.resource_name,
       ext.category,
       lkp.name category_name,
       to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
       fnd.user_name   created_by,
       decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
       decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
       decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name,
       ext.resource_id,
       g1.group_id
  from
       jtf_rs_groups_vl g2,
       jtf_rs_groups_vl g3,
       jtf_rs_resource_extns_vl ext,
       jtf_objects_vl lkp ,
       fnd_user fnd,
       jtf_rs_group_members_aud gm,
       jtf_rs_group_members  mem,
       jtf_rs_groups_vl g1
 where g1.group_id = l_group
 and   mem.group_id  = g1.group_id
 and   gm.group_member_id = mem.group_member_id
 and   ( gm.new_group_id   = g1.group_id
           or gm.old_group_id = g1.group_id )
 and  gm.creation_date between nvl(l_start_date,sysdate)
                           and  nvl(l_end_date,sysdate)
 and   gm.new_group_id  =   g2.group_id(+)
 and   gm.old_group_id  =   g3.group_id(+)
 and  fnd.user_name like nvl(l_usr_name, '%')
 and  fnd.user_id = gm.created_by
 and  mem.resource_id = ext.resource_id
 and  ext.category = lkp.object_code
 order by g1.group_name , resource_name, gm.creation_date , fnd.user_name;

Line: 335

  select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
       --: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
       g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
       --: Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       ext.resource_number,
       ext.resource_name,
       ext.category,
       lkp.name category_name,
       to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
       fnd.user_name   created_by,
       decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
       decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
       decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name
 from
       jtf_rs_groups_vl g2,
       jtf_rs_groups_vl g3,
       fnd_user fnd,
       jtf_objects_vl lkp,
       jtf_rs_groups_vl g1,
       jtf_rs_group_members_aud gm,
       jtf_rs_group_members  mem,
       jtf_rs_resource_extns_vl ext
  where  ext.resource_id = l_res_id
    and  mem.resource_id = ext.resource_id
    and  gm.group_member_id = mem.group_member_id
    and  gm.creation_date between nvl(l_start_date,sysdate)
                           and   nvl(l_end_date,sysdate)
    and  gm.created_by = fnd.user_id
    and  fnd.user_name like nvl(l_usr_name, '%')
    and  g1.group_id = mem.group_id
    and  g2.group_id(+)=  gm.new_group_id
    and  g3.group_id(+) = gm.old_group_id
    and  ext.category = lkp.object_code
  order by resource_name, g1.group_name , gm.creation_date , fnd.user_name;

Line: 381

  select gm.group_member_id,
        g1.group_id,
        g1.group_name,
        g1.group_number
 from   jtf_rs_group_members_aud gm,
         jtf_rs_groups_vl g1
 where  gm.old_group_id = l_group_id
 and    gm.new_resource_id = l_resource_id
 and     g1.group_id = gm.new_group_id;

Line: 429

        all_segment_details_tab.delete;

Line: 520

            fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);

Line: 641

            fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);

Line: 754

            fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',27, ' ')||r_grp.created_by);

Line: 851

            fnd_file.put_line(fnd_file.log,rpad('  Updated By: ',29, ' ')||r_res.created_by);

Line: 868

    all_segment_details_tab.delete;

Line: 889

      SELECT rel.group_id,
           rel.related_group_id,
           trunc(greatest(rel.start_date_active,
                      nvl(p_greatest_start_date, rel.start_date_active))) greatest_start_date,
             /* Logic : end_date_active, p_least_end_date
                          NULL         , NULL   = NULL
                          NULL         , Value  = Value
                          Value        , NULL   = Value
                          Value1       , Value2 = least(value1, value2) */
           trunc(least(nvl(rel.end_date_active, p_least_end_date),
                   nvl(p_least_end_date, rel.end_date_active))) least_end_date,
             start_date_active relation_start_date,
             end_date_active relation_end_date
       FROM jtf_rs_grp_relations rel
       WHERE relation_type = 'PARENT_GROUP'
       AND rel.related_group_id = p_group_id
       AND NVL(rel.delete_flag, 'N') <> 'Y'
         /*
            mugsrin:
            Modified the following lines to comply with GSCC standard
            Original:
            AND least(nvl(end_date_active, to_date(l_g_miss_date)),
                   nvl(p_least_end_date, to_date(l_g_miss_date))) >=
            Modified as given below:
         */
            AND least(nvl(end_date_active, to_date(to_char(l_g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR')),
                nvl(p_least_end_date, to_date(to_char(l_g_miss_date,'DD-MM-RRRR'),'DD-MM-RRRR'))) >=
            trunc(greatest(start_date_active,nvl(p_greatest_start_date, start_date_active)));

Line: 943

     g_child_tab.delete;

Line: 974

 select group_id,
        -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
        attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
        attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
        -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
  from  jtf_rs_groups_vl
 where group_name = p_group_name;

Line: 985

select group_name,
       start_date_active,
      end_date_active,
        -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
        attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
        attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
        -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
from  jtf_rs_groups_vl
where group_id = l_group_id;

Line: 1003

select mem.resource_id,
       res.resource_name,
       obj.name     category_name
 from  jtf_rs_group_members mem,
       jtf_rs_resource_extns_vl res,
       jtf_objects_vl  obj
where  mem.group_id = l_group_id
  and  nvl(mem.delete_flag, 'N') <> 'Y'
  and  mem.resource_id = res.resource_id
  and  res.category    = obj.object_code;

Line: 1017

select mem.resource_id,
       res.resource_name,
       rol.role_name,
       obj.name     category_name,
       rol.res_rl_start_date,
       rol.res_rl_end_date
 from  jtf_rs_group_members mem,
       jtf_rs_resource_extns_vl res,
       jtf_rs_defresroles_vl rol,
       jtf_objects_vl  obj
where  mem.group_id = l_group_id
  and  nvl(mem.delete_flag, 'N') <> 'Y'
  and  mem.resource_id = res.resource_id
  and  mem.group_member_id = rol.role_resource_id(+)
  and  rol.role_resource_type(+) = 'RS_GROUP_MEMBER'
  and  nvl(rol.delete_flag, 'N') <> 'Y'
  -- and  trunc(sysdate) between nvl(rol.res_rl_start_date(+),sysdate) and nvl(rol.res_rl_end_date(+),sysdate)
  and  res.category    = obj.object_code;

Line: 1061

      all_segment_details_tab.delete;

Line: 1186

  all_segment_details_tab.delete;

Line: 1202

          SELECT grp.start_date_active,
                 grp.end_date_active
            FROM jtf_rs_groups_b grp
           WHERE grp.group_id = x_group_id;

Line: 1210

   select usg.usage
    from  jtf_rs_group_usages usg
   where  usg.usage = l_usage
     and  usg.group_id = l_group_id;

Line: 1223

  select /*+ INDEX(gm jtf_rs_group_members_aud_nu3) */ g1.group_name,
       -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       g1.attribute_category, g1.attribute1, g1.attribute2, g1.attribute3, g1.attribute4, g1.attribute5, g1.attribute6, g1.attribute7, g1.attribute8,
       g1.attribute9, g1.attribute10, g1.attribute11, g1.attribute12, g1.attribute13, g1.attribute14, g1.attribute15,
       -- Introduced for incorporating reporting of DFF fields Ref: ER# 2549463
       ext.resource_number,
       ext.resource_name,
       ext.category,
       lkp.name category_name,
       to_char(gm.creation_date, 'DD-MON-YYYY:HH24:MI:SS') creation_date,
       fnd.user_name   created_by,
       decode(gm.old_group_id, g1.group_id, 'OUT', 'IN') moved,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_number ) from_group_number,
       decode(gm.old_group_id, null, null, g1.group_id,null, g3.group_name ) from_group_name,
       decode(gm.old_group_id, g1.group_id, g2.group_number, null)    to_group_number,
       decode(gm.old_group_id, g1.group_id, g2.group_name, null)    to_group_name,
       ext.resource_id,
       g1.group_id
  from
       jtf_rs_groups_vl g2,
       jtf_rs_groups_vl g3,
       jtf_rs_resource_extns_vl ext,
       jtf_objects_vl lkp ,
       fnd_user fnd,
       jtf_rs_group_members_aud gm,
       jtf_rs_group_members  mem,
       jtf_rs_groups_vl g1
 where g1.group_id = l_group_id
 and   mem.group_id  = g1.group_id
 and   gm.group_member_id = mem.group_member_id
 and   ( gm.new_group_id   = g1.group_id
           or gm.old_group_id = g1.group_id )
 and  gm.creation_date between nvl(l_start_date,sysdate)
                           and  nvl(l_end_date,sysdate)
 and   gm.new_group_id  =   g2.group_id(+)
 and   gm.old_group_id  =   g3.group_id(+)
 and  fnd.user_name like nvl(l_usr_name, '%')
 and  fnd.user_id = gm.created_by
   and  mem.resource_id = ext.resource_id
 and  ext.category = lkp.object_code
 order by g1.group_name , resource_name, gm.creation_date , fnd.user_name;

Line: 1275

  select gm.group_member_id,
        g1.group_id,
        g1.group_name,
        g1.group_number
 from   jtf_rs_group_members_aud gm,
         jtf_rs_groups_vl g1
 where  gm.old_group_id = l_group_id
 and    gm.new_resource_id = l_resource_id
 and     g1.group_id = gm.new_group_id;

Line: 1416

                      fnd_file.put_line(fnd_file.log,lpad(rpad('  Updated By: ',27, ' '),15+l_indent, ' ')||r_grp_child.created_by);