APPS.GMD_FORMULA_SECURITY_ACCESS SQL Statements

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

Line: 17

     SELECT user_id, responsibility_id, other_organization_id
     FROM  GMD_formula_security
     WHERE organization_id = p_organization_id
     AND   formula_id = p_formula_id;

Line: 23

  DELETE FROM gmd_formula_security_temp;

Line: 31

  SELECT responsibility_id INTO v_resp_id
  FROM   fnd_responsibility
  WHERE  responsibility_key = 'GMD_SECURITY_PROFILE_MGR';

Line: 39

  INSERT INTO gmd_formula_security_temp( assign_method_ind,
                                         activated_ind,
                                         access_type_ind,
                                         organization_id,
                                         user_id,
                                         responsibility_id,
                                         other_organization_id)
  SELECT p.assign_method_ind,
         'N',
         p.access_type_ind,
         p.organization_id,
         p.user_id,
         p.responsibility_id,
         p.other_organization_id
  FROM  gmd_security_profiles p
  WHERE organization_id = l_organization_id
  AND assign_method_ind = 'M'
  --Added following clause to avoid duplicate records w.r.t. bug 3495409
  AND NOT EXISTS
     ( SELECT 1 FROM GMD_formula_security fs
       WHERE fs.formula_id                 = l_formula_id
       AND   fs.organization_id            = p.organization_id
       AND   NVL(fs.user_id,-1)            = NVL(p.user_id,-1)
       AND   NVL(fs.responsibility_id,-1)  = NVL(p.responsibility_id,-1)
       AND   ((fs.other_organization_id = p.other_organization_id) OR
              (fs.other_organization_id IS NULL AND p.other_organization_id IS NULL))
      );

Line: 70

           UPDATE GMD_formula_security_temp
           SET    activated_ind   = 'A'
           WHERE  user_id         = l_users.user_id
           AND    organization_id = p_organization_id
           AND    other_organization_id IS NULL;

Line: 76

           UPDATE GMD_formula_security_temp
           SET    activated_ind     = 'A'
           WHERE  responsibility_id = l_users.responsibility_id
           AND    organization_id   = p_organization_id
           AND    other_organization_id IS NULL;

Line: 82

           UPDATE GMD_formula_security_temp
           SET    activated_ind = 'A'
           WHERE  user_id       = l_users.user_id
           AND    organization_id = p_organization_id
           AND    other_organization_id = l_users.other_organization_id;

Line: 88

           UPDATE GMD_formula_security_temp
           SET    activated_ind     = 'A'
           WHERE  responsibility_id = l_users.responsibility_id
           AND    organization_id   = p_organization_id
           AND    other_organization_id = l_users.other_organization_id;

Line: 97

 INSERT INTO gmd_formula_security_temp( formula_id,
                                        assign_method_ind,
                                        activated_ind,
                                        access_type_ind,
                                        organization_id,
                                        user_id,
                                        responsibility_id,
                                        other_organization_id)
  SELECT p.formula_id,
         'M', --Modified 'A' to 'M' w.r.t. bug 3495409
         'A',
         p.access_type_ind,
         p.organization_id,
         p.user_id,
         p.responsibility_id,
         p.other_organization_id
  FROM  gmd_formula_security p
  WHERE organization_id = l_organization_id
  AND   formula_id = l_formula_id;