APPS.POS_REG_WF_PKG SQL Statements

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

Line: 91

           'select role_name
            from wf_user_roles
            where
                role_name in
            (
               select role_name
               from wf_user_roles
               where user_name in (' ||  p_list_of_users || ')
               group by role_name
               having count(role_name) = :1
            )
           group by role_name
           having count(role_name) = :2 '
         using p_num_users, p_num_users;

Line: 150

   SELECT function_id
   INTO l_approver_func_id
   FROM fnd_form_functions
   WHERE function_name = 'POS_REG_APPROVE_EXT_USERS';

Line: 156

      'SELECT DISTINCT fu.user_name
      FROM fnd_user fu,
           fnd_responsibility fr,
           wf_user_roles wur
      WHERE fr.menu_id in
            (SELECT     fme.menu_id
             FROM       fnd_menu_entries fme
             START WITH fme.function_id = :1
             CONNECT BY PRIOR menu_id = sub_menu_id
             )
      AND fr.application_id = 177
      AND wur.role_name like ''FND_RESP|%|%|STANDARD''
      AND WUR.ROLE_ORIG_SYSTEM = ''FND_RESP''
      AND WUR.ROLE_ORIG_SYSTEM_ID = FR.RESPONSIBILITY_ID
      AND WUR.ASSIGNMENT_TYPE IN (''D'', ''B'')
      AND wur.user_name = fu.user_name'
   using l_approver_func_id;

Line: 540

  FND_REGISTRATION_PKG.insert_fnd_reg_details(
      ln_reg_id, ln_app_id, lv_reg_type, p_itemtype_fieldname,
      'VARCHAR2', NULL, p_itemtype, NULL, NULL);

Line: 544

  FND_REGISTRATION_PKG.insert_fnd_reg_details(
      ln_reg_id, ln_app_id, lv_reg_type, p_itemkey_fieldname,
      'VARCHAR2', NULL, p_itemkey, NULL, NULL);

Line: 618

  SELECT registration_status
  INTO   lv_reg_status
  FROM   fnd_registrations
  WHERE  registration_key = lv_reg_key
  FOR UPDATE;

Line: 719

  SELECT registration_status
  INTO   lv_reg_status
  FROM   fnd_registrations
  WHERE  registration_key = lv_reg_key
  FOR UPDATE;

Line: 811

     Workflow activity function. Update registration details with the itemtype
     and item key

  PARAMS:
    WF Standard API.

  RETURN:
    COMPLETE : always return COMPLETE. do not raise errors.

----------------------------------------*/

PROCEDURE UpdInvTypeKey(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
lv_proc_name VARCHAR2(30) := 'UpdInvTypeKey';

Line: 873

     Workflow activity function. Update registration details with the itemtype
     and item key

  PARAMS:
    WF Standard API.

  RETURN:
    COMPLETE : always return COMPLETE. do not raise errors.

----------------------------------------*/

PROCEDURE UpdRegTypeKey(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
lv_proc_name VARCHAR2(30) := 'UpdRegTypeKey';

Line: 933

     Workflow activity function. Update registration details with the itemtype
     and item key

  PARAMS:
    WF Standard API.

  RETURN:
    COMPLETE : always return COMPLETE. do not raise errors.

----------------------------------------*/

PROCEDURE UpdApprvTypeKey(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
lv_proc_name VARCHAR2(30) := 'UpdApprvTypeKey';

Line: 993

     Workflow activity function. Update registration details with the itemtype
     and item key

  PARAMS:
    WF Standard API.

  RETURN:
    COMPLETE : always return COMPLETE. do not raise errors.

----------------------------------------*/

PROCEDURE UpdRjctTypeKey(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
lv_proc_name VARCHAR2(30) := 'UpdRjctTypeKey';

Line: 1120

    SELECT NVL(language_code, 'US'), email
    INTO   lv_registrant_lang, lv_registrant_email
    FROM   fnd_registrations
    WHERE  registration_id = ln_reg_id;

Line: 1281

    SELECT first_name, last_name
    INTO   lv_first_name, lv_last_name
    FROM   fnd_registrations
    WHERE  registration_id = ln_reg_id;

Line: 1346

    SELECT user_name
    INTO   lv_approver_role
    FROM   fnd_user
    WHERE  user_id = ln_approver_id;

Line: 1474

       * POS_SELECTED                * PON_SELECTED
       * FIRST_NAME                  * LAST_NAME
       * REGISTRANT_TITLE            * REGISTRANT_PHONE
       * REGISTRANT_PHONE_EXT        * REGISTRANT_FAX
       * REGISTRANT_JOB_TITLE        * MIDDLE_NAME
       * SC_SELECTED

  PARAMS:
    WF Standard API.

  RETURN:
    COMPLETE:SUCCESS - if everything is ok.
    COMPLETE:ERROR   - if critical attribute cannot be set.

----------------------------------------*/

PROCEDURE SetApprvItemAttrValues(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS

lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;

Line: 1555

    SELECT requested_user_name, email, first_name, last_name, middle_name, user_title, phone, phone_extension, fax
    INTO   lv_requested_user_name, lv_user_email, lv_first_name, lv_last_name, lv_middle_name, lv_title, lv_phone, lv_phone_ext, lv_fax
    FROM   fnd_registrations
    WHERE  registration_id = ln_reg_id;

Line: 1636

    SELECT email_address
    INTO   lv_contact_email
    FROM   fnd_user
    WHERE  user_id = ln_approver_id;

Line: 1713

  WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'POS_SELECTED', lv_pos_flag);

Line: 1714

  WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'PON_SELECTED', lv_pon_flag);

Line: 1715

  WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'SC_SELECTED', lv_sc_flag);

Line: 1854

    SELECT NVL(language_code, 'US'), email
    INTO   lv_registrant_lang, lv_registrant_email
    FROM   fnd_registrations
    WHERE  registration_id = ln_reg_id;

Line: 1928

    SELECT email_address
    INTO   lv_contact_email
    FROM   fnd_user
    WHERE  user_id = ln_approver_id;

Line: 2012

  SELECT nls_language
  INTO   lv_nls_lang
  FROM   fnd_languages
  WHERE  language_code = lv_user_language;

Line: 2075

  public PROCEDURE DeleteLocalUser

     Workflow activity function. Delete a workflow ad-hoc (local) user.
     Do not raise exception. If the deletion fails, just skip it.

  PARAMS:
    WF Standard API.

  RETURN:
    WF Standard API.

----------------------------------------*/

PROCEDURE DeleteLocalUser(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS

lv_user_name WF_LOCAL_USERS.NAME%TYPE;

Line: 2105

  DELETE FROM wf_local_users
  WHERE       name = lv_user_name;

Line: 2116

END DeleteLocalUser;

Line: 2144

	  SELECT responsibility_id
	  FROM   fnd_responsibility
	  WHERE  application_id = p_app_id
	  AND    responsibility_key = p_resp_key
	  AND    (end_date IS NULL OR end_date > start_date);

Line: 2165

   FND_USER_RESP_GROUPS_API.insert_assignment
     ( user_id => p_user_id,
       responsibility_id => ln_resp_id,
       responsibility_application_id => p_resp_app_id,
       security_group_id => 0,
       start_date => sysdate,
       end_date => NULL,
       description => p_resp_key);

Line: 2237

	  p_last_updated_by => fnd_global.user_id,
	  p_last_update_date => sysdate,
	  p_last_update_login => fnd_global.login_id);

Line: 2317

	SELECT 1
	  FROM ak_resp_security_attributes
	  WHERE responsibility_id = p_resp_id AND
	  resp_application_id = p_resp_appl_id AND
	  attribute_code = p_sec_attr_code AND
	  attribute_application_id = p_sec_attr_appl_id;

Line: 2346

   INSERT INTO ak_resp_security_attributes
     (responsibility_id,
      resp_application_id,
      attribute_code,
      attribute_application_id,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
      )
     VALUES
     (p_resp_id,
      p_resp_appl_id,
      p_sec_attr_code,
      p_sec_attr_appl_id,
      fnd_global.user_id,
      Sysdate,
      fnd_global.user_id,
      Sysdate,
      fnd_global.login_id
      );

Line: 2420

	SELECT 1
	  FROM ak_resp_security_attr_values
	  WHERE responsibility_id = p_resp_id AND
	  resp_application_id = p_resp_appl_id AND
	  attribute_code = p_sec_attr_code AND
	  attribute_application_id = p_sec_attr_appl_id;

Line: 2449

   INSERT INTO ak_resp_security_attr_values
     (responsibility_id,
      resp_application_id,
      attribute_code,
      attribute_application_id,
      varchar2_value,
      date_value,
      number_value
      )
     VALUES
     (p_resp_id,
      p_resp_appl_id,
      p_sec_attr_code,
      p_sec_attr_appl_id,
      p_varchar2_value,
      p_date_value,
      p_number_value
      );

Line: 2513

	SELECT application_id
	  FROM   fnd_application
	  WHERE  application_short_name = 'POS';

Line: 2603

	SELECT responsibility_id
	  FROM   fnd_responsibility
	  WHERE  application_id = p_appl_id
	  AND    responsibility_key = p_resp_key
	  AND    (end_date IS NULL OR end_date > start_date);

Line: 2632

       * insert FND_USER
       * insert TCA
       * assign user responsibility
       * set user security attributes

     Two item attribute values are set:

       * ASSIGNED_USER_NAME                * FIRST_LOGON_KEY

  PARAMS:
    WF Standard API.

  RETURN:
    WF Standard API.

----------------------------------------*/

PROCEDURE CreateUser(
  itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS

lv_reg_key WF_ITEM_ATTRIBUTE_VALUES.TEXT_VALUE%TYPE;

Line: 2685

lv_is_pos_selected VARCHAR2(1) := 'N';

Line: 2686

lv_is_pon_selected VARCHAR2(1) := 'N';

Line: 2687

lv_is_msc_selected VARCHAR2(1) := 'N';

Line: 2711

   SELECT application_id
   FROM   fnd_application
   WHERE  application_short_name = p_app_short_name;

Line: 2716

	 SELECT field_value_number
	 FROM   fnd_registration_details
	 WHERE  registration_id = p_reg_id
	 AND    field_name like p_field_name||'%';

Line: 2722

	 SELECT field_value_string
	 FROM   fnd_registration_details
	 WHERE  registration_id = p_reg_id
	 AND    field_name like p_field_name||'%';

Line: 2728

   SELECT * FROM fnd_user WHERE user_id = l_user_id;

Line: 2781

  lv_is_pos_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'POS_SELECTED');

Line: 2782

  lv_is_pon_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'PON_SELECTED');

Line: 2783

  lv_is_msc_selected := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'SC_SELECTED');

Line: 2792

	 SELECT field_value_string
	 INTO   l_encrypted_initial_password
	 FROM   fnd_registration_details
	 WHERE  registration_id = ln_reg_id
	 AND    field_name  = 'Initial Pass';

Line: 2826

    SELECT 1
    INTO   ln_counter
    FROM   fnd_user
    WHERE  user_name = lv_user_name
    AND    ROWNUM = 1;

Line: 2843

        fnd_log.string(fnd_log.level_exception, g_log_module_name || '.' || lv_proc_name || '.selectfnduser', Sqlerrm);

Line: 2859

  SELECT party_id INTO l_vendor_party_id FROM po_vendors WHERE vendor_id = ln_vendor_id;

Line: 3333

  SELECT DECODE(wn.mail_status, 'MAIL', 'COMPLETE:N', 'COMPLETE:Y')
  INTO   resultout
  FROM   wf_item_activity_statuses_v ws, wf_notifications wn
  WHERE  ws.item_type = itemtype
  AND    ws.item_key  = itemkey
  AND    ws.notification_id = wn.notification_id;

Line: 3453

    select r.requested_user_name, d1.field_value_number, d2.field_value_number
    into   l_user_name, l_osn_req_id, l_internal_id
    from fnd_registrations r,
         fnd_registration_details d1, fnd_registration_details d2
    where r.registration_id = l_reg_id
    and   d1.registration_id = l_reg_id
    and   d2.registration_id = l_reg_id
    and   d1.field_name = 'OSN Request ID'
    and   d2.field_name = 'OSN Request InternalID';

Line: 3468

      select d.field_value_string, r.requested_user_name, r.registration_key
      into   l_encrypted_initial_password, l_user_name, l_reg_key
      from   fnd_registration_details d, fnd_registrations r
      where  r.registration_id = l_reg_id
      and  d.registration_id = l_reg_id
      and    d.field_name  = 'Initial Pass';