DBA Data[Home] [Help]

APPS.MSD_DEM_EVENT SQL Statements

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

Line: 57

      x_update_user_sql VARCHAR2(2000)	:= NULL;
Line: 70

        SELECT table_name
        FROM all_tables
        WHERE  owner = upper(p_schema_name)
        AND table_name = 'MDP_MATRIX';
Line: 77

        SELECT to_char(user_id)
        FROM fnd_user
        WHERE user_name = p_user_name;
Line: 127

          OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
          OR eventName = 'oracle.apps.fnd.user.delete'
          OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
          OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
      THEN
         x_curr_name := p_event.getValueForParameter('USER_NAME');
Line: 171

           ' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
             (SELECT sum(a) FROM (SELECT 1 a
                                     FROM dual
                                     WHERE EXISTS ( SELECT 1
                                                       FROM fnd_user_resp_groups_all fug,
                                                            fnd_responsibility fr,
                                                            fnd_menu_entries fme,
                                                            fnd_form_functions fff
                                                       WHERE
                                                               fug.user_id = :user_id1
                                                           AND fug.responsibility_application_id = 722
                                                           AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
                                                           AND fr.application_id = 722
                                                           AND fr.responsibility_id = fug.responsibility_id
                                                           AND fme.menu_id = fr.menu_id
                                                           AND fme.grant_flag = ''Y''
                                                           AND fme.sub_menu_id IS NULL
                                                           AND fff.function_id = fme.function_id
                                                           AND fff.function_name = ''MSD_DEM_DEMPLANR'')
                                  UNION ALL
                                  SELECT 2 a
                                     FROM dual
                                     WHERE EXISTS ( SELECT 1
                                                       FROM fnd_user_resp_groups_all fug,
                                                            fnd_responsibility fr,
                                                            fnd_menu_entries fme,
                                                            fnd_form_functions fff
                                                       WHERE
                                                               fug.user_id = :user_id2
                                                           AND fug.responsibility_application_id = 722
                                                           AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
                                                           AND fr.application_id = 722
                                                           AND fr.responsibility_id = fug.responsibility_id
                                                           AND fme.menu_id = fr.menu_id
                                                           AND fme.grant_flag = ''Y''
                                                           AND fme.sub_menu_id IS NULL
                                                           AND fff.function_id = fme.function_id
                                                           AND fff.function_name = ''MSD_DEM_WF_MGR''))) user_type,
             ( select first_name
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) first_name,
            ( select last_name
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) last_name,
            ( select name
              from hr_all_organization_units
              where business_group_id in
              (select pap.business_group_id
              from per_all_people_f pap
              where (pap.person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (pap.party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (pap.party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (pap.party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum < 2) company,
            ( select work_telephone
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) phone_num,
             fu.fax,
             decode(fu.email_address,
               null,
               (select pap.email_address
               from per_all_people_f pap
               where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
               or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
               or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
               or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
               and pap.email_address is not null
               and rownum <2),
               fu.email_address) email_address,
             1 product
      	from fnd_user fu
      	where fu.user_id = :userid3';
Line: 281

      IF (eventName = 'oracle.apps.fnd.user.delete')
      THEN
         p_user_name := x_curr_name;
Line: 306

      IF (eventName = 'oracle.apps.fnd.user.delete')
      THEN
         NULL;
Line: 315

      ELSIF (   eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
             OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
      THEN
         eventName := 'oracle.apps.fnd.user.resp.delete';
Line: 325

      SELECT meaning
         INTO x_component_name
         FROM fnd_lookup_values_vl
         WHERE lookup_type = 'MSD_DEM_COMPONENTS'
           AND lookup_code = 'DEMAND_MANAGEMENT';
Line: 350

      /* UPDATE USER */
      x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
                                                      ' ''' || p_user_name || ''' , ' ||
                                                      ' ''' || p_user_pwd || ''' , ' ||
                                                      ' ''' || x_user_permission || ''' , ' ||
                                                      ' ''' || p_user_fname || ''' , ' ||
                                                      ' ''' || p_user_lname || ''' , ' ||
                                                      ' ''' || p_user_org_name || ''' , ' ||
                                                      ' ''' || p_user_wrkphone || ''' , ' ||
                                                      ' ''' || p_user_fax || ''' , ' ||
                                                      ' ''' || p_user_email || ''' , ' ||
                                                      ' ''0'' , ' ||
                                                      ' null, ' ||
                                                      ' ''' || x_component_name || ''' , ' ||
                                                      ' null, ' ||
                                                      ' ''UPDATE''); END;';
Line: 372

      DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
Line: 379

      DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
Line: 384

      /*  insert/Update responsibility for user  */
      If (   eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
          OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN

         /* User does not exist in Demantra... Add the user */
         If Duser_cnt = 0 Then

            /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
            log_debug ('Insert/Update Responsibility - Creating User');
Line: 397

         /* User exists in Demantra, Update the responsibility */
         Elsif Duser_cnt > 0 Then

            /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
            log_debug ('Insert/Update Responsibility - Updating User');
Line: 402

            log_debug (x_update_user_sql);
Line: 404

            EXECUTE IMMEDIATE x_update_user_sql;
Line: 408

      /*  Update Existing User  */
      Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN

         /* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
         If (p_user_valid = 2 OR p_user_resp  = 0) Then

            /* invoke API_DROP_ORA_DEM_USER(user name) */
            log_debug ('User Update - Deleting User');
Line: 426

                log_debug ('User Update - Creating User');
Line: 431

             /* User exists in Demantra and effective date is enabled....Update user */
             Else

                /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
                log_debug('User Update - Updating User');
Line: 436

                log_debug (x_update_user_sql);
Line: 438

                EXECUTE IMMEDIATE x_update_user_sql;
Line: 443

      /*   Delete existing User  */
      Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN

         /* invoke API_DROP_ORA_DEM_USER(user name) */
         log_debug('User Delete - Deleting User');
Line: 452

      /*   Delete responsibility  */
      Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN

         /*  User exists in Demantra  */
         If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then

            /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
            log_debug ('Responsibility Delete - Updating user');
Line: 460

            log_debug (x_update_user_sql);
Line: 462

            EXECUTE IMMEDIATE x_update_user_sql;
Line: 466

            log_debug('Responsibility Delete - Deleting user');
Line: 603

      x_update_user_sql VARCHAR2(2000)	:= NULL;
Line: 612

         SELECT to_char(user_id)
            FROM fnd_user
            WHERE user_name = p_user_name;
Line: 630

          OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
          OR eventName = 'oracle.apps.fnd.user.delete'
          OR eventName = 'oracle.apps.fnd.wf.ds.user.updated'
          OR eventName = 'oracle.apps.fnd.wf.ds.user.nameChanged')
      THEN
         x_curr_name := p_event.getValueForParameter('USER_NAME');
Line: 674

           ' select fu.user_name, decode(sign(fu.end_date - sysdate),-1,2,1),
             (SELECT sum(a) FROM (SELECT 1 a
                                     FROM dual
                                     WHERE EXISTS ( SELECT 1
                                                       FROM fnd_user_resp_groups_all fug,
                                                            fnd_responsibility fr,
                                                            fnd_menu_entries fme,
                                                            fnd_form_functions fff
                                                       WHERE
                                                               fug.user_id = :user_id1
                                                           AND fug.responsibility_application_id = 722
                                                           AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
                                                           AND fr.application_id = 722
                                                           AND fr.responsibility_id = fug.responsibility_id
                                                           AND fme.menu_id = fr.menu_id
                                                           AND fme.grant_flag = ''Y''
                                                           AND fme.sub_menu_id IS NULL
                                                           AND fff.function_id = fme.function_id
                                                           AND fff.function_name = ''MSD_DEM_SOP_SOPPLANR'')
                                  UNION ALL
                                  SELECT 2 a
                                     FROM dual
                                     WHERE EXISTS ( SELECT 1
                                                       FROM fnd_user_resp_groups_all fug,
                                                            fnd_responsibility fr,
                                                            fnd_menu_entries fme,
                                                            fnd_form_functions fff
                                                       WHERE
                                                               fug.user_id = :user_id2
                                                           AND fug.responsibility_application_id = 722
                                                           AND decode(sign(fug.end_date - sysdate), -1, 2, 1) = 1
                                                           AND fr.application_id = 722
                                                           AND fr.responsibility_id = fug.responsibility_id
                                                           AND fme.menu_id = fr.menu_id
                                                           AND fme.grant_flag = ''Y''
                                                           AND fme.sub_menu_id IS NULL
                                                           AND fff.function_id = fme.function_id
                                                           AND fff.function_name = ''MSD_DEM_SOP_WF_MGR''))) user_type,
             ( select first_name
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) first_name,
            ( select last_name
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) last_name,
            ( select name
              from hr_all_organization_units
              where business_group_id in
              (select pap.business_group_id
              from per_all_people_f pap
              where (pap.person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (pap.party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (pap.party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (pap.party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum < 2) company,
            ( select work_telephone
              from per_all_people_f
              where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
              or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
              or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
              or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
              and rownum <2) phone_num,
             fu.fax,
             decode(fu.email_address,
               null,
               (select pap.email_address
               from per_all_people_f pap
               where ((person_id = fu.employee_id
                     and fu.employee_id is not null)
               or
                    (party_id = fu.person_party_id
                     and fu.person_party_id is not null)
               or    (party_id = fu.supplier_id
                     and fu.supplier_id is not null)
               or    (party_id = fu.customer_id
                     and fu.customer_id is not null))
               and pap.email_address is not null
               and rownum <2),
               fu.email_address) email_address,
             1 product
      	from fnd_user fu
      	where fu.user_id = :userid3';
Line: 784

      IF (eventName = 'oracle.apps.fnd.user.delete')
      THEN
         p_user_name := x_curr_name;
Line: 809

      IF (eventName = 'oracle.apps.fnd.user.delete')
      THEN
         NULL;
Line: 818

      ELSIF (   eventName = 'oracle.apps.fnd.wf.ds.userRole.updated'
             OR eventName = 'oracle.apps.fnd.wf.ds.user.updated')
      THEN
         eventName := 'oracle.apps.fnd.user.resp.delete';
Line: 830

      SELECT meaning
         INTO x_component_name
         FROM fnd_lookup_values_vl
         WHERE lookup_type = 'MSD_DEM_COMPONENTS'
           AND lookup_code = 'SOP';
Line: 859

      /* UPDATE USER */
      x_update_user_sql := 'BEGIN ' || x_schema || '.API_CREATE_ORA_DEM_USER ( ' ||
                                                      ' ''' || p_user_name || ''' , ' ||
                                                      ' ''' || p_user_pwd || ''' , ' ||
                                                      ' ''' || x_user_permission || ''' , ' ||
                                                      ' ''' || p_user_fname || ''' , ' ||
                                                      ' ''' || p_user_lname || ''' , ' ||
                                                      ' ''' || p_user_org_name || ''' , ' ||
                                                      ' ''' || p_user_wrkphone || ''' , ' ||
                                                      ' ''' || p_user_fax || ''' , ' ||
                                                      ' ''' || p_user_email || ''' , ' ||
                                                      ' ''0'' , ' ||
                                                      ' null, ' ||
                                                      ' ''' || x_component_name || ''' , ' ||
                                                      ' null, ' ||
                                                      ' ''UPDATE''); END;';
Line: 881

      DuserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||p_user_name||'''';
Line: 888

      DOlduserQuery := 'select count(user_name) from ' || x_schema || '.user_id where user_name = '''||x_old_name||'''';
Line: 893

      /*  insert/Update responsibility for user  */
      If (   eventName = 'oracle.apps.fnd.wf.ds.userRole.created'
          OR eventName = 'oracle.apps.fnd.wf.ds.userRole.updated') THEN

         /* User does not exist in Demantra... Add the user */
         If Duser_cnt = 0 Then

            /* invoke API_CREATE_ORA_DEM_USER.(ADD) */
            log_debug ('Insert/Update Responsibility - Creating User');
Line: 906

         /* User exists in Demantra, Update the responsibility */
         Elsif Duser_cnt > 0 Then

            /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
            log_debug ('Insert/Update Responsibility - Updating User');
Line: 911

            log_debug (x_update_user_sql);
Line: 913

            EXECUTE IMMEDIATE x_update_user_sql;
Line: 917

      /*  Update Existing User  */
      Elsif (eventName = 'oracle.apps.fnd.wf.ds.user.updated') THEN

         /* Effective date has been disabled OR DM responsibilities have been disabled for user....delete user */
         If (p_user_valid = 2 OR p_user_resp  = 0) Then

            /* invoke API_DROP_ORA_DEM_USER(user name) */
            log_debug ('User Update - Deleting User');
Line: 935

                log_debug ('User Update - Creating User');
Line: 940

             /* User exists in Demantra and effective date is enabled....Update user */
             Else

                /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
                log_debug('User Update - Updating User');
Line: 945

                log_debug (x_update_user_sql);
Line: 947

                EXECUTE IMMEDIATE x_update_user_sql;
Line: 952

      /*   Delete existing User  */
      Elsif (eventName = 'oracle.apps.fnd.user.delete') THEN

         /* invoke API_DROP_ORA_DEM_USER(user name) */
         log_debug('User Delete - Deleting User');
Line: 961

      /*   Delete responsibility  */
      Elsif (eventName = 'oracle.apps.fnd.user.resp.delete') THEN

         /*  User exists in Demantra  */
         If (Duser_cnt > 0 AND x_user_permission IS NOT NULL) Then

            /* invoke API_CREATE_ORA_DEM_USER (UPDATE) */
            log_debug ('Responsibility Delete - Updating user');
Line: 969

            log_debug (x_update_user_sql);
Line: 971

            EXECUTE IMMEDIATE x_update_user_sql;
Line: 975

            log_debug('Responsibility Delete - Deleting user');