Group Team
This query can be used to get the Approved Supplier List information for the items.

Query is tested in R12 instance.

SELECT ood.organization_code "ORGANIZATION CODE",
              msib.segment1 "ITEM_NUMBER",
              pv.vendor_name "SUPPLIER NAME",
              pv.segment1 "VENDOR NUMBER",
              pvsa.vendor_site_code "SUPPLIER SITE CODE",
              pas.status "SOURCE_ASL_STATUS",
              paa.consigned_from_supplier_flag "CONSIGNED FROM SUPPLIER?"
  FROM APPS.po_approved_supplier_list asl,
              APPS.po_vendors pv,
              APPS.po_vendor_sites_all pvsa,
              APPS.org_organization_definitions ood,
              APPS.mtl_system_items_b msib,
              APPS.po_asl_attributes paa,
             APPS.po_asl_statuses pas
WHERE pv.vendor_id = asl.vendor_id
   AND pvsa.vendor_site_id = asl.vendor_site_id
   AND ood.organization_id = asl.using_organization_id
   AND ood.operating_unit = pvsa.org_id
   AND asl.item_id = msib.inventory_item_id
   AND asl.using_organization_id = msib.organization_id
   AND ood.organization_id = msib.organization_id
   AND asl.asl_id = paa.asl_id
   AND asl.using_organization_id = paa.using_organization_id
   AND msib.organization_id = paa.using_organization_id
   AND ood.organization_id = paa.using_organization_id
   AND asl.asl_status_id = pas.status_id
   AND msib.segment1 = :ITEM NUMBER
Group Team

This article helps in deleting a Responsibility to an Oracle user using API FND_USER_PKG.DELRESP.

This API is tested in R12.1.3

We will try to delete responsibility ‘System Administrator’ for the user ‘TEST_USER’ using the API.
Now lets run the below API

DECLARE
   v_user_name                  VARCHAR2 (100) := 'TEST_USER';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                     
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
      AND    frt.LANGUAGE = USERENV ('LANG')
      AND    frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.delresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is removed from the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while deleting responsibilty from the user and the error is '
          || SQLERRM
         );
END;

Output we got after execution is
Responsiblity System Administrator is removed from the user TEST_USER Successfully.

We can cross verify if the responsibility is attached to the user ‘TEST_USER’ by using the following query

SELECT  count(1)
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'TEST_USER'
   AND    frt.responsibility_name = ‘System Administrator’



Labels: , 3 comments | edit post
Group Team

This article helps in attaching a Responsibility to an Oracle user using API FND_USER_PKG.ADDRESP.

This API is tested in R12.1.3

We will try to add responsibility ‘System Administrator’ for the user ‘TEST_USER’ using the API.
Now lets run the below API

DECLARE
   v_user_name                  VARCHAR2 (100) := 'TEST_USER';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
   v_description                  VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                     
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
      AND    frt.LANGUAGE = USERENV ('LANG')
      AND    frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.addresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group,
                                              description        => v_description,
                                              start_date          => SYSDATE,
                                              end_date            => NULL
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while attaching responsibilty to the user and the error is '
          || SQLERRM
         );
END;

Output we got after execution is
Responsiblity System Administrator is attached to the user TEST_USER Successfully.

We can cross verify if the responsibility is attached to the user ‘TEST_USER’ by using the following query

SELECT  fu.user_name,
               frt.responsibility_name
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'TEST_USER'



Labels: , 0 comments | edit post
Group Team

This article helps in updating an existing Oracle user using API FND_USER_PKG.UPDATEUSER.

This API is tested in R12.1.3

We will try to update the email address for an existing user ‘TEST_USER’ using the API. There was no email_address populated to the user ‘TEST_USER’ prior to running the API.

Now lets run the below API

DECLARE
   v_user_name     VARCHAR2 (100)    := 'TEST_USER';
   v_email_address  VARCHAR2 (100) := 'test@xyz.com';
BEGIN
   fnd_user_pkg.updateuser
                           (x_user_name                               => v_user_name,
                            x_owner                                        => NULL,
                            x_unencrypted_password         => NULL,
                            x_session_number                      => 0,
                            x_start_date                                 => NULL,
                            x_end_date                                   => NULL,
                            x_last_logon_date                       => NULL,
                            x_description                                => NULL,
                            x_password_date                         => NULL,
                            x_password_accesses_left         => NULL,
                            x_password_lifespan_accesses => NULL,
                            x_password_lifespan_days        => NULL,
                            x_employee_id                             => NULL,
                            x_email_address                          => v_email_address,
                            x_fax                                               => NULL,
                            x_customer_id                             => NULL,
                            x_supplier_id                               => NULL,
                            x_user_guid                                  => NULL,
                            x_change_source                        => NULL
                           );
   --COMMIT;
   DBMS_OUTPUT.put_line ('User ' || v_user_name || ' is Updated successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                (   'Error encountered while updating the user and the error is '|| SQLERRM
                );
END;
Output we got after execution is
   User TEST_USER is Updated successfully.

We can cross verify if the email_address is update for the user ‘TEST_USER’ by using the following query

SELECT   user_id,
                user_name,
                creation_date,
                start_date,
                end_date,
                description,
email_address
  FROM  fnd_user
 WHERE user_name = 'TEST_USER'


Labels: , 1 comments | edit post