Script to add responsibility to a FND USER

DECLARE
  v_user_name  VARCHAR2(30) := upper('GK0714');
  CURSOR ABC
  IS
  select application_short_name,responsibility_key,responsibility_id
  from
   fnd_responsibility_vl a,
   fnd_application_vl b
  where a.application_id = b.application_id
  and responsibility_name in ('CRM Administrator'
                              'Customer Support',
                              'Alert Manager',
                              'General Ledger Super User');
  CURSOR CDE
  IS
  select responsibility_id
  from fnd_user_resp_groups a,
   fnd_user b
  where a.user_id  = b.user_id
  and b.user_name = v_user_name;
 
BEGIN

FOR CBA IN ABC LOOP
 FOR EDC IN CDE LOOP
   IF (EDC.responsibility_id <> CBA.responsibility_id) THEN
    fnd_user_pkg.addresp (username         => v_user_name,
                         resp_app         => cba.application_short_name,
                         resp_key         => cba.responsibility_key,
                         security_group   => 'STANDARD',
                         description      => NULL,
                         start_date       => SYSDATE-1,
                         end_date         => NULL);
  END IF;
 END LOOP;
END LOOP;
COMMIT;
END;

Script to Update the Servive Request in CRM

DECLARE
l_return_status              VARCHAR2(1);
l_msg_count                  NUMBER;
l_msg_data                   VARCHAR2(1000);
l_msg_dummy                  VARCHAR2 (4000) ;
l_output                     VARCHAR2 (15000);
l_last_update_date           DATE   := SYSDATE ;
l_service_request_rec        cs_servicerequest_pub.service_request_rec_type;
l_notes                      cs_servicerequest_pub.notes_table;
l_contacts                   cs_servicerequest_pub.contacts_table;
l_sr_update_out_rec          cs_servicerequest_pub.sr_update_out_rec_type;
l_workflow_process_id       NUMBER;
l_interaction_id            NUMBER;
l_user_id                   NUMBER;
l_resp_id                   NUMBER;
l_resp_appl_id              NUMBER;
BEGIN
   BEGIN
      SELECT user_id
      INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'CONVERSION';
   EXCEPTION
     WHEN OTHERS THEN
       l_user_id := NULL;
   END;
  
   BEGIN
      SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_vl
      WHERE responsibility_name = 'Customer Support';
   EXCEPTION
     WHEN OTHERS THEN
       l_resp_id       := NULL;
       l_resp_appl_id  := NULL;
   END;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
MO_GLOBAL.INIT ('CS');
cs_servicerequest_pub.initialize_rec( l_service_request_rec);
l_service_request_rec.request_attribute_8 := 'Y';
CS_ServiceRequest_PUB.Update_ServiceRequest (p_api_version            => 3.0,
                                             p_init_msg_list          => FND_API.G_FALSE,
                                             p_commit                 => FND_API.G_FALSE,
                                             x_return_status          => l_return_status,
                                             x_msg_count              => l_msg_count,
                                             x_msg_data               => l_msg_data,
                                             p_request_id             => &incident_id,
                                             p_request_number         => NULL,
                                             p_audit_comments         => NULL,
                                             p_object_version_number  => &object_version_number,
                                             p_resp_appl_id           => NULL,                                                                                                                                                                                                                                         --fnd_global.resp_appl_id,
                                             p_resp_id                => NULL,                                                                                                                                                                                                                                                                                           --fnd_global.resp_id,
                                             p_last_updated_by        => NULL,                                                                                                                                                                                                                                                                                   --fnd_global.user_id,
                                             p_last_update_login      => NULL,
                                             p_workflow_process_id    => NULL,
                                             p_last_update_date       => l_last_update_date,
                                             p_service_request_rec    => l_service_request_rec,
                                             p_notes                  => l_notes,
                                             p_contacts               => l_contacts,
                                             p_called_by_workflow     => FND_API.G_FALSE,
                                             x_workflow_process_id    => l_workflow_process_id,
                                             x_interaction_id         => l_interaction_id );
      IF (l_return_status = 'S') then
        COMMIT;
      ELSE
        ROLLBACK;
         BEGIN
           FOR i IN 1 .. l_msg_count
           LOOP
               fnd_msg_pub.get (i,
                                fnd_api.g_false,
                                l_msg_data,
                                l_msg_dummy);
                l_output := (TO_CHAR (i) || ': ' || l_msg_data);
           END LOOP;
         END;
               DBMS_OUTPUT.PUT_LINE ('Error :'||l_output);
       END IF;
    DBMS_OUTPUT.PUT_LINE ('Status :'||l_return_status);
END;
/

Script to update Service Request Note in CRM

SET SERVEROUTPUT ON;
DECLARE
 lc_return_status             VARCHAR2(1);
 ln_msg_count                 NUMBER;
 lc_msg_data                  VARCHAR2(5000);
 lc_msg_dummy                 VARCHAR2(5000);
 lc_output                    VARCHAR2(5000);
BEGIN
fnd_global. apps_initialize (1238, 57180, 514);
JTF_NOTES_PUB.UPDATE_NOTE
                         ( p_api_version           => 1.0,
                           p_init_msg_list         => 'F',
                           p_commit                => 'F',
                           x_return_status         => lc_return_status,
                           x_msg_count             => ln_msg_count,
                           x_msg_data              => lc_msg_data,
                           p_jtf_note_id           => 14934254,
                           p_entered_by            => 24209,
                           p_last_updated_by       => 1238,               -- 'CONVERSION' User
                           p_notes                 => 'Distributor has sent the SO141656 to send the sapre part to them . MIR.',
                           p_notes_detail          => NULL,
                           p_note_status           => 'E',
                           p_note_type             => 'CS_PROBLEM'
);
  IF (lc_return_status = 'S') THEN
   dbms_output.put_line('Note Updated Sucessfully');
   COMMIT;
  ELSE
      BEGIN
          FOR i IN 1 .. ln_msg_count
          LOOP
            fnd_msg_pub.get (i, fnd_api.g_false, lc_msg_data, lc_msg_dummy);
            lc_output := (TO_CHAR (i) || ': ' || lc_msg_data);
          END LOOP;
            dbms_output.put_line('Error :'||lc_output);
      END;
    ROLLBACK;
   END IF;
END;
/

Script to import a user as a CRM resource

DECLARE
 lc_user_name                 FND_USER.USER_NAME%TYPE           := 'GK0714';
 lc_sex                       PER_ALL_PEOPLE_F.SEX%TYPE         := 'M';
 lc_first_name                PER_ALL_PEOPLE_F.FIRST_NAME%TYPE  := 'Gaurav';
 lc_last_name                 PER_ALL_PEOPLE_F.LAST_NAME%TYPE   := 'Kumar';
 lc_employee_number           PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
 ln_person_id                 PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
 ln_assignment_id             PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
 ln_object_ver_number         PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
 ln_asg_ovn                   NUMBER;
 ld_per_effective_start_date  PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
 ld_per_effective_end_date    PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
 lc_full_name                 PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
 ln_per_comment_id            PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
 ln_assignment_sequence       PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
 lc_assignment_number         PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
 lb_name_combination_warning  BOOLEAN;
 lb_assign_payroll_warning    BOOLEAN;
 lb_orig_hire_warning         BOOLEAN;
 ln_user_id                   FND_USER.USER_ID%TYPE;
 lc_return_status             VARCHAR2(1);
 ln_msg_count                 NUMBER;
 lc_msg_data                  VARCHAR2(5000);
 lc_msg_dummy                 VARCHAR2(5000);
 lc_output                    VARCHAR2(5000);
 ln_resource_id               JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
 lc_resource_number           JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
 ln_responsibility_id         NUMBER;
 ln_application_id            NUMBER;
BEGIN
      BEGIN
           hr_employee_api.create_employee
           (   p_hire_date                       => SYSDATE,
               p_business_group_id               => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                       => lc_last_name,
               p_first_name                      => lc_first_name,
               p_sex                             => lc_sex,
               p_employee_number                 => lc_employee_number,
               p_person_id                       => ln_person_id,
               p_assignment_id                   => ln_assignment_id,
               p_per_object_version_number       => ln_object_ver_number,
               p_asg_object_version_number       => ln_asg_ovn,
               p_per_effective_start_date        => ld_per_effective_start_date,
               p_per_effective_end_date          => ld_per_effective_end_date,
               p_full_name                       => lc_full_name,
               p_per_comment_id                  => ln_per_comment_id,
               p_assignment_sequence             => ln_assignment_sequence,
               p_assignment_number               => lc_assignment_number,
               p_name_combination_warning        => lb_name_combination_warning,
               p_assign_payroll_warning          => lb_assign_payroll_warning,
               p_orig_hire_warning               => lb_orig_hire_warning
        );
             DBMS_OUTPUT.PUT_LINE ('Person has created with Name :'||lc_full_name);
      EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While creating the Employee :'||SQLERRM);
      END;     
  IF (ln_person_id IS NOT NULL) THEN
      BEGIN
           fnd_user_pkg.updateuser
           (   x_user_name                       => lc_user_name,
               x_owner                           => '',
               x_employee_id                     => ln_person_id
           );
      EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While updating the User Name :'||SQLERRM);
       ROLLBACK;
      END;
    BEGIN
    SELECT user_id
    INTO ln_user_id
    FROM fnd_user
    WHERE user_name = lc_user_name;
    SELECT responsibility_id, application_id
    INTO ln_responsibility_id, ln_application_id
    FROM fnd_responsibility_vl
    WHERE responsibility_name = 'CRM Administrator';
    END;
    BEGIN
       fnd_global. apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);
           jtf_rs_resource_pub.create_resource
           (   p_api_version                 => 1.0,
               p_category                     => 'EMPLOYEE',
               p_start_date_active             => SYSDATE,
               p_source_id                    => ln_person_id,
               p_user_id                    => ln_user_id,
               p_user_name                  => lc_user_name,
               p_resource_name              => lc_full_name,
               p_source_number              => lc_employee_number,
               p_source_name                => lc_full_name,
               p_source_first_name          => lc_first_name,
               p_source_last_name           => lc_last_name,
               p_source_business_grp_id        => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),  
               x_return_status                 => lc_return_status,
               x_msg_count                     => ln_msg_count,
               x_msg_data                     => lc_msg_data,
               x_resource_id                 => ln_resource_id,
               x_resource_number             => lc_resource_number
           );
         IF (lc_return_status <> 'S') THEN
            BEGIN
               FOR i IN 1 .. ln_msg_count
               LOOP
                  fnd_msg_pub.get (i, fnd_api.g_false, lc_msg_data, lc_msg_dummy);
                  lc_output := (TO_CHAR (i) || ': ' || lc_msg_data);
               END LOOP;
                  dbms_output.put_line('Error :'||lc_output);
            END;
            ROLLBACK;
         ELSE
         dbms_output.put_line('Resource imported sucessfully.');
         COMMIT;
         END IF;
    EXCEPTION
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE ('Error While importing the Resource :'||SQLERRM);
       ROLLBACK;
      END;
  END IF;
END;
/