8 Replies Latest reply on Jan 26, 2009 3:34 PM by Jason ORCL

    Supplier User Management-> Register Supplier User

    Jason ORCL
      Supplier User Management > Register Supplier User

      I do this on the form and it sets up a supplier user with no problem. It even sets up the securing attribute for the user with the iSupplier Portal id.

      Is there a way to do this registering on the backend? An API I don't know about? Or some other way to do this?

      Thanks
        • 1. Re: Supplier User Management-> Register Supplier User
          SachinAhuja
          Hi Jason,

          I even need help from you. Could you please suggest what are the Base tables that gets updated when we register the Supplier User.

          Thanks in advance for your help.

          Regards
          Sachin
          • 2. Re: Supplier User Management-> Register Supplier User
            Jason ORCL
            Sachin-

            Unfortunately no. You are asking the same question I asked.

            I do not know. If anyone knows either what API or workflow is kicked off, or what base tables are updates, that would be very very helpful.

            Thank You!
            • 3. Re: Supplier User Management-> Register Supplier User
              user16854
              Not sure about APIsor WFlows, but I did some work on iSupplier a while ago, and found this SQL useful.
              /*##############################################################################
              #       iSUPPLIER APPS.PO_SUPPLIER_USERS_V VIEW
                      GENERATES LIST OF iSUPPLIER USERS
                      LINKED TO SUPPLIER TABLES
              /*############################################################################*/
              SELECT *
                FROM apps.po_supplier_users_v;

              /*##############################################################################
              #        iSUPPLIER EXTERNAL SUPPLIER REGISTRATIONS
              /*############################################################################*/
              SELECT   *
                  FROM pos.pos_supplier_registrations psr
              ORDER BY psr.creation_date DESC;

              /*##############################################################################
              #        iSUPPLIER FND REGISTRATIONS
              /*############################################################################*/

              SELECT fr.registration_id
                   , fr.creation_date
                   , DECODE(
                        fr.registration_type
                      , 'POS_REG', 'BY_CCC'
                      , 'POS_SUPP_REG', 'ONLINE'
                     ) status
                   , fr.registration_status
                   , fr.user_title
                   , fr.first_name
                   , fr.middle_name
                   , fr.last_name
                   , fr.email
                   , fr.phone
                   , fr.requested_user_name
                FROM apps.fnd_registrations fr;

              /*##############################################################################
              #        iSUPPLIER PRODUCTS AND SERVICES
              /*############################################################################*/
              SELECT *
                FROM apps.pos_sup_products_services psps
                   , apps.fnd_lookup_values_vl
              WHERE psps.vendor_id = '65352'
                 AND fnd_lookup_values_vl.lookup_type = 'POS_SUP_PROD_SVC_STATUS'
                 AND fnd_lookup_values_vl.lookup_code = psps.status
                 AND fnd_lookup_values_vl.enabled_flag = 'Y'
                 AND fnd_lookup_values_vl.start_date_active < SYSDATE
                 AND (
                         fnd_lookup_values_vl.end_date_active IS NULL
                      OR fnd_lookup_values_vl.end_date_active > SYSDATE
                     );

              /*##############################################################################
              #        iSUPPLIER BANK ACCOUNT CHECKING
              /*############################################################################*/
              SELECT   psbar.creation_date
                     , psbar.request_status
                     , fu.description supplier_user
                     , fu.user_name supplier_username
                     , pv.vendor_name supplier
                     , psbar.last_update_date
                     , fu2.user_name last_updated_by_userid
                     , fu2.description last_updated_by_name
                     , psbar.bank_name
                     , psbar.bank_number
                     , psbar.bank_branch_name
                     , psbar.bank_branch_number
                     , psbar.bank_branch_type
                     , psbar.bank_account_name
                     , psbar.bank_account_number
                     , psbar.account_description
                     , psbar.account_type
                     , psbar.account_holder_name
                     , psbar.notes_from_supplier
                     , psbar.notes_from_buyer
                     , psbar.address_line1
                     , psbar.address_line2
                     , psbar.address_line3
                     , psbar.address_line4
                     , psbar.city
                     , psbar.county
                     , psbar.state
                     , psbar.zip
                  FROM pos.pos_sup_bank_account_requests psbar
                     , applsys.fnd_user fu
                     , applsys.fnd_user fu2
                     , po.po_vendors pv
                 WHERE psbar.created_by = fu.user_id
                   AND psbar.last_updated_by = fu2.user_id
                   AND psbar.vendor_id = pv.vendor_id
                   AND pvsa.purchasing_site_flag = 'Y'
              ORDER BY 1 DESC;

              /*##############################################################################
              #       iSUPPLIER APPS.PO_SUPPLIER_USERS_V VIEW
                      GENERATES LIST OF iSUPPLIER USERS
                      LINKED TO SUPPLIER TABLES
              /*############################################################################*/
              SELECT   fnd_user.user_name user_name
                     , fnd_user.creation_date user_creation_date
                     , fnd_user.last_logon_date
                     , po_vendors.vendor_name supplier
                     , po_vendors.vendor_id supplier_id
                     , (
                        user_parties.person_first_name || ' '
                        || user_parties.person_last_name
                       ) user_party_full_name
                     , user_parties.email_address
                  FROM apps.fnd_user
                     , apps.hz_parties user_parties
                     , apps.hz_parties company_parties
                     , apps.po_vendors
                     , apps.hz_relationships vendor_relationship
                     , apps.hz_relationships employment_relationship
                 WHERE fnd_user.person_party_id = user_parties.party_id
                   AND employment_relationship.object_id = company_parties.party_id
                   AND employment_relationship.subject_id = user_parties.party_id
                   AND employment_relationship.relationship_type = 'POS_EMPLOYMENT'
                   AND employment_relationship.relationship_code = 'EMPLOYEE_OF'
                   AND employment_relationship.start_date <= SYSDATE
                   AND employment_relationship.end_date >= SYSDATE
                   AND vendor_relationship.object_id = po_vendors.vendor_id
                   AND vendor_relationship.subject_id = company_parties.party_id
                   AND vendor_relationship.relationship_type = 'POS_VENDOR_PARTY'
                   AND vendor_relationship.relationship_code = 'PARTY_OF_VENDOR'
                   AND vendor_relationship.start_date <= SYSDATE
                   AND vendor_relationship.end_date >= SYSDATE
                   AND fnd_user.last_logon_date IS NOT NULL
              ORDER BY 2 DESC;

              /*##############################################################################
              #        iSUPPLIER SPECIFIC PROFILES
              /*############################################################################*/
              SELECT DECODE(
                        fpov.level_id
                      , 10001, 'Site'
                      , 10002, 'Application'
                      , 10003, 'Responsibility'
                      , 10004, 'User'
                      , NULL, 'Not Set'
                     ) profile_level
                   , mw_level_values.mw_set_against set_against_id
                   , fu.description person
                   , fpot.user_profile_option_name
                   , fpot.description
                   , fpo.profile_option_name
                   , fpov.profile_option_value
                   , fpov.last_update_date
                   , fpov.last_updated_by
                FROM applsys.fnd_profile_option_values fpov
                   , applsys.fnd_profile_options fpo
                   , applsys.fnd_profile_options_tl fpot
                   , applsys.fnd_user fu
                   -- TABLE BELOW GROUPS ALL DATA INTO A BIG UNION FOR USE LATER ON
                   -- ALL RESPS, ALL APPLICATIONS, AND ALL USERS
              ,      (SELECT '10001 0' mw_level_id
                           , 'Set at Site Level' mw_set_against
                        FROM DUAL
                      UNION
                      SELECT '10002 ' || fat.application_id
                           , fat.application_name
                        FROM applsys.fnd_application_tl fat
                      UNION
                      SELECT '10003 ' || frt.responsibility_id
                           , frt.responsibility_name
                        FROM applsys.fnd_responsibility_tl frt
                      UNION
                      SELECT '10004 ' || fu.user_id
                           , fu.user_name
                        FROM applsys.fnd_user fu) mw_level_values
              WHERE fpo.profile_option_id = fpov.profile_option_id(+)
                 AND fpot.profile_option_name = fpo.profile_option_name
                 AND fpov.level_id || ' ' || fpov.level_value = mw_level_values.mw_level_id(+)
                 AND mw_level_values.mw_set_against = fu.user_name(+)
                 AND fpo.end_date_active IS NULL
              --   AND LOWER(mw_level_values.mw_set_against) LIKE
              --                           '%ccc internet procurement catalog administration%'
              --   AND LOWER(fpov.profile_option_value) LIKE '%gov%'
              --   AND LOWER(fpot.user_profile_option_name) LIKE '%one%time%'
              --   AND fpot.profile_option_name LIKE '%FND_OA_ENABLE_DEFAULTS%'
                 AND fpot.user_profile_option_name IN
                        ('POS: External Responsibility Flag', 'Apps Servlet Agent'
                       , 'Application Framework Agent', 'Applications Servlet Agent'
                       , 'Applications JSP Agent', 'Applications Web Agent'
                       , 'Default Country', 'Node Trust Level', 'HZ: Generate Party Number'
                       , 'Applications Portal Logout', 'GUEST_USER_PWD'
                       , 'iSP Default Responsibility For External User'
                       , 'POS: Allow Invoice Backdating'
                       , 'POS: Default Responsibility for Newly Registered Supplier Users'
                       , 'PON: External Application Framework Agent', 'POS: External URL'
                       , 'Sourcing Default Responsibility For External User'
                       , 'Responsibility Trust Level', 'Default Country'
                       , 'GL Set of Books Name', 'Applications Portal Logout');
              • 4. Re: Supplier User Management-> Register Supplier User
                Jason ORCL
                that looks great...thanks

                But does anyone know what exactly happens when you "Register Supplier User"?

                I don't want to miss anything. What tables get updated?
                Is it only FND tables?
                • 5. Re: Supplier User Management-> Register Supplier User
                  NitinSDarji
                  iSupplier user is a normal fnd user, with a securing attribute(ICX_SUPPLIER_ORG_ID) assigned at user level to link the user id to the supplier.

                  Nitin S. Darji
                  • 6. Re: Supplier User Management-> Register Supplier User
                    Jason ORCL
                    Nitin, thanks!

                    I understand. But can you, or anyone else, elaborate a bit? How to I actually set that securing attribute(ICX_SUPPLIER_ORG_ID)?

                    What is the process? Where is this attribute located?

                    Thanks.
                    • 7. Re: Supplier User Management-> Register Supplier User
                      Jason ORCL
                      Still at a loss...

                      Anyone know how to assign the securing attribute? Is there an Update API? What table/column should I be looking at?

                      Thanks!
                      • 8. Re: Supplier User Management-> Register Supplier User
                        Jason ORCL
                        I hate to do this but I still do not have an answer. Does anyone know how to register a iSupplier user programatically? Please? Thanks!!