3 Replies Latest reply on Dec 29, 2007 4:36 PM by 606129

    I am not able to assign job_id  in my Assignment api

    605649
      Hi

      Can anybody help me

      I am created one for create job using hr_job_api

      What am trying to assign my job_id to assignment api

      logic is

      If the job is not it creates job using job api,
      If job is already exists it assign to assignment api




      CREATE OR REPLACE PROCEDURE xxhr_job_proc IS

      l_assignment_number number;          
      l_assignment_id      number;          
      v_special_ceiling_step_id          number;          
      v_object_version_number           number;     
      V_concatenated_segments      number;     
      V_soft_coding_keyflex_id      number;     
      V_comment_id      number;     
      v_effective_start_date      date;     
      v_effective_end_date      date;     
      V_no_managers_warning      boolean;     
      V_other_manager_warning      boolean;     
      v_num number;     
      v_seg_conc VARCHAR2(2000);     
      l_change_reason                    varchar2(50);     
      l_date_probation_end                date;          
      l_default_code_comb_id               number;          
      l_set_of_books_id               number;     
      G_USER_ID                     number;     
      G_LOGIN_ID                    number;     
      v_resp_id                     number;     
      v_resp_APPL_id                    number;     
      v_assignment_id                    number;
      v_org_id number;
      l_object_version_number number;
      l_effective_date date;
      v_job_id                    NUMBER;
      v_job_definition_id                NUMBER;
      v_business_group_id               NUMBER;
      v_name                          VARCHAR2(240);
      l_job_id                    number;
      l_job_group_id                    number;


      CURSOR Job_emp Is
           SELECT      empl_id
                ,empl_rcd_nbr
                ,effective_date
                ,effective_sequence
                ,rehire_date
                ,probation_date
                ,department
                ,supervisor_id
                ,direct_manager_id
                ,location_code
                ,reason_code
                ,action
                ,salary_grade
                ,job_code
                ,full_or_part_time
                ,hr_responsible_id
           FROM xxhr_job_all;

      BEGIN
           G_USER_ID                := FND_PROFILE.VALUE('USER_ID');
           G_LOGIN_ID                := FND_PROFILE.VALUE('LOGIN_ID');
      v_org_id := FND_PROFILE.VALUE('ORG_ID');
           v_resp_id                := FND_PROFILE.VALUE('RESP_ID');
           v_resp_APPL_id               := FND_PROFILE.VALUE('RESP_APPL_ID');
      v_business_group_id          := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');

           FND_GLOBAL.APPS_INITIALIZE(G_USER_ID,v_resp_id,v_resp_APPL_id);
      -- ------------------------------------------------------------------------------------------------------------------------
      -- Open the Cursor for Validation
      -- ------------------------------------------------------------------------------------------------------------------------
      FOR v_job_emp IN job_emp LOOP

           SELECT fnd_flex_ext.get_ccid('PER',
                               'JOB',
                               101,
                               TO_CHAR(SYSDATE, 'DD-MON-YYYY'),
                               v_job_emp.job_code)
                               INTO v_job_definition_id
                               FROM dual;
      SELECT job_group_id
                INTO l_job_group_id
                     FROM PER_JOB_GROUPS
                          WHERE master_flag = 'Y';
      -- ------------------------------------------------------------------------------------------------------------------------
      -- Validation for job code
      -- ------------------------------------------------------------------------------------------------------------------------
      BEGIN
           SELECT job_id
                INTO v_job_id
           FROM per_jobs
           WHERE name = v_job_emp.job_code;

      EXCEPTION
           WHEN NO_DATA_FOUND THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,'There is no job id Exist For this job code'||v_job_emp.job_code);
      WHEN OTHERS THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Inserting a Record'||SQLCODE||SQLERRM);
      END;

      if v_job_id is null then

      hr_job_api.create_job
                (p_validate                => FALSE
                ,p_business_group_id      => v_business_group_id
                ,p_date_from                => SYSDATE
                ,p_job_group_id               => l_job_group_id
                ,p_segment1                => v_job_emp.job_code
                ,p_job_id                => v_job_id
                ,p_job_definition_id           => v_job_definition_id
                ,p_object_version_number      => v_object_version_number
                ,p_name                => v_name
                );

      ELSE
      -- ------------------------------------------------------------------------------------------------------------------------
      -- Validation for assignment id
      -- ------------------------------------------------------------------------------------------------------------------------          
           SELECT      assignment_id
                ,effective_start_date
                ,object_version_number
      INTO     l_assignment_id
                ,l_effective_date
                ,l_object_version_number
           FROM per_all_assignments_f
           WHERE person_id =
           (SELECT person_id
           FROM per_all_people_f
           WHERE employee_number = v_job_emp.empl_id);

      -- ------------------------------------------------------------------------------------------------------------------------
      -- Validation for set of book id
      -- ------------------------------------------------------------------------------------------------------------------------
           SELECT ho.org_information3
           INTO l_set_of_books_id
           FROM gl_sets_of_books gsb,
           hr_organization_information ho
           WHERE gsb.set_of_books_id = ho.org_information3
           AND ho.org_information_context='Operating Unit Information'
           AND ho.ORGANIZATION_ID = v_org_id;

      -- ------------------------------------------------------------------------------------------------------------------------
      -- Validation for code combination id
      -- ------------------------------------------------------------------------------------------------------------------------
      SELECT fnd_flex_ext.get_ccid('SQLGL',
                               'GL#',
                               50268,
                               TO_CHAR(SYSDATE,'DD-MON-YYYY'),
                               '50001.1820000281.7501.0000000000.00000.00000'
                               ) INTO l_default_code_comb_id
                               FROM dual;

      hr_assignment_api.update_emp_asg
      (p_validate => FALSE
      ,p_effective_date => sysdate
      ,p_datetrack_update_mode => 'CORRECTION'
      ,p_object_version_number => l_object_version_number
      ,p_assignment_number => NULL --l_assignment_number
           ,p_supervisor_id      => NULL
           ,p_assignment_id      => l_assignment_id
           ,p_default_code_comb_id           => l_default_code_comb_id
           ,p_set_of_books_id           => l_set_of_books_id
           ,p_title                => NULL
           ,p_ass_attribute_category     => v_business_group_id
           ,p_ass_attribute1          => v_job_emp.empl_rcd_nbr
           ,p_ass_attribute2          => v_job_emp.effective_date
           ,p_ass_attribute3          => v_job_emp.effective_sequence
           ,p_ass_attribute4          => v_job_emp.rehire_date
           ,p_ass_attribute5          => v_job_emp.probation_date
           ,p_ass_attribute6          => v_job_emp.department
           ,p_ass_attribute7          => v_job_emp.supervisor_id
           ,p_ass_attribute8          => v_job_emp.direct_manager_id
           ,p_ass_attribute9          => v_job_emp.location_code
           ,p_ass_attribute10          => v_job_emp.reason_code
           ,p_ass_attribute11          => v_job_emp.action
           ,p_ass_attribute12          => v_job_emp.salary_grade
           ,p_ass_attribute13          => v_job_emp.full_or_part_time
           ,p_ass_attribute14          => v_job_emp.hr_responsible_id
      ,p_effective_start_date => v_effective_start_date
      ,p_effective_end_date => v_effective_end_date
      ,p_no_managers_warning => v_no_managers_warning
      ,p_other_manager_warning => v_other_manager_warning
           ,p_comment_id               => V_comment_id
           ,p_soft_coding_keyflex_id     => v_soft_coding_keyflex_id
      ,p_cagr_grade_def_id => v_num
      ,p_cagr_concatenated_segments => v_seg_conc
           ,p_concatenated_segments => V_concatenated_segments
                );

      END IF;
           
      DBMS_OUTPUT.PUT_LINE('Ex:'||v_job_id||','||v_object_version_number);

      END LOOP;
      COMMIT;
      EXCEPTION
           WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
                
      END;
      /

      Regards
      Samarpan

      Message was edited by:
      Deekshitulu Mantha