Declare
  
      l_user_id         NUMBER;
      v_customer_id       NUMBER;
      l_email           VARCHAR2 (240);
      l_error_message   VARCHAR2 (240);
      l_record_status   BOOLEAN;

      CURSOR c1
      IS
         SELECT     *
               FROM XX_CONC_USERS
              where status = ‘R’
              and error_mesg is null
              FOR UPDATE;
   BEGIN
      FOR i IN c1
      LOOP
         l_record_status := TRUE;
         l_error_message := ”;

         BEGIN
            SELECT a.OBJECT_ID
            into v_customer_id
             FROM hz_relationships a, hz_parties b
          WHERE a.subject_id =
                            (SELECT party_id
                               FROM hz_parties
                              WHERE  upper(party_name) =  upper(i.vendor_name))
            AND LOWER (b.party_name) LIKE LOWER (i.conc_last_name|| ‘%’)
            AND a.party_id = b.party_id
            AND b.party_type = ‘PARTY_RELATIONSHIP’;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_record_status := FALSE;
               l_error_message := ‘Employee not exist…’;
         END;

         IF l_record_status = TRUE
         THEN
            BEGIN
               fnd_user_pkg.createuser (x_user_name                 => LTRIM
                                                                          (RTRIM
                                                                              (i.conc_email
                                                                              )
                                                                          ),
                                        x_owner                     => NULL,
                                        x_unencrypted_password      => ‘123456’,
                                        x_description               => NULL,
                                        x_customer_id               => v_customer_id, —- OBJECT_ID of the Person  
                                        x_email_address             => i.conc_email      —– HZ_CONTACT_POINTS Email  
                                       );

               BEGIN
                  SELECT user_id
                    INTO l_user_id
                    FROM fnd_user
                   WHERE user_name = upper(i.conc_email);
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_user_id := NULL;
               END;

               IF l_user_id IS NOT NULL
               THEN
                  fnd_user_resp_groups_api.insert_assignment
                                       (user_id                            => l_user_id,
                                        responsibility_id                  => 23415,
                                        responsibility_application_id      => 396,
                                        security_group_id                  => 0,
                                        start_date                         => TRUNC
                                                                                 (SYSDATE
                                                                                 ),
                                        end_date                           => NULL,
                                        description                        => NULL
                                       );

                  UPDATE XX_CONC_USERS
                     SET status = ‘S’,
                         error_mesg = NULL
                   WHERE CURRENT OF c1;
               ELSE
                  UPDATE XX_CONC_USERS
                     SET status = ‘R’,
                         error_mesg = ‘User Not Exist’
                   WHERE CURRENT OF c1;
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_message := SUBSTR (SQLERRM, 1, 200);

                  UPDATE XX_CONC_USERS
                     SET status = ‘R’,
                         error_mesg = l_error_message
                   WHERE CURRENT OF c1;
            END;
         ELSE
            UPDATE XX_CONC_USERS
               SET status = ‘E’,
                   error_mesg = l_error_message
             WHERE CURRENT OF c1;
         END IF;
      END LOOP;

      COMMIT;
   END xx_create_user;
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply