CREATE OR REPLACE PACKAGE BODY SON_CREATE_ASSGMT_PKG
  AS
  /***************************************************************
   **
   **
   **   Package        :   SON_CREATE_ASSGMT_PKG .pkb(package body)
   **   $Logfile       :   $
   **   $Creation Date         :   18/06/2009
   **   $Author        :   Monika Bhakuni
   **
   **  (C) Copyright:
   **
   ** ------------------------------------------------------------
   **
   ** Description : The package body contains procedure for
   **        Creating employee asignments data to Apps
   **        tables using the API procedure
   **
   ** EQHDOK
   ** ------------------------------------------------------------
   **
   **  $Log:   $
   **
   **************************************************************/
     PROCEDURE Create_assignment(errbuf out varchar2,retcode out varchar2)
     IS
             l_total_records                         NUMBER;
      l_success_records                       NUMBER          := 0;
      l_failure_records                       NUMBER          := 0;
      l_flag                                  CHAR            := 'Y';
      l_error_message                                        VARCHAR2(100);
      l_status                                                        VARCHAR2(20);
     x_object_version_number                         NUMBER;
     x_people_group_id                               NUMBER;
     x_soft_coding_keyflex_id                        NUMBER;
     x_concatenated_segments                         VARCHAR2 (500);
     x_special_ceiling_step_id                       NUMBER;
     x_group_name                                    VARCHAR2 (500);
     x_effective_start_date                          DATE;
     x_effective_end_date                            DATE;
     x_other_manager_warning                         BOOLEAN;
     x_org_now_no_manager_warning                    BOOLEAN;
     x_spp_delete_warning                            BOOLEAN;
     x_entries_changed_warning                       VARCHAR2 (500);
     x_tax_district_changed_warning                  BOOLEAN;
     x_assignment_id                                         NUMBER;
     x_assignment_number                             Varchar2(50);
     x_cagr_grade_def_id                             NUMBER;
     x_default_code_comb_id                                  NUMBER;
     x_set_of_books_id                                               NUMBER;
     x_comment_id                                                    NUMBER;
     x_no_managers_warning                                   BOOLEAN                        :=FALSE;
     l_assignment_id                        NUMBER;
     x_supervisor_id                        NUMBER;
     x_supervisor_assignment_id             NUMBER;
     l_employee_number                              per_all_People_f.employee_number%TYPE;
     l_person_id                                    per_all_People_f.person_id%TYPE;
     l_job_id                                               per_jobs.job_id%TYPE;
     l_job_check                                    VARCHAR(10);
     l_assign_mode                                  VARCHAR(20);
                    l_organization_id                              org_organization_definitions.organization_id%TYPE;
            G_USER_ID      number; --Gireesh
     G_LOGIN_ID      number; --Gireesh
     v_resp_id      number; --Gireesh
     v_resp_APPL_id     number; --Gireesh
     v_org_id      number; --Gireesh
     v_business_group_id    number; --Gireesh
     l_effective_start_date   date;
     l_object_version_number   number;
	lv_assignment_id				per_all_assignments_f.assignment_id%TYPE;
	l_effective_date			DATE;
	lv_effective_date			DATE;
	lv_object_version_number			per_all_assignments_f.object_version_number%TYPE;
        /* Cusror to get records from the temporary table */
  CURSOR emp_assn_cur
        IS
          SELECT employee_number,person_id,effective_date,assignment_id,object_version_number,
      supervisor_id,assignment_number,change_reason,assignment_status_type_id,--date_probation_ends,
      default_code_comb_id,frequency,probation_period,probation_unit,sal_review_period,
      sal_review_period_frequency,set_of_books_id,time_normal_finish,time_normal_start,contract_id,establishment_id,
      collective_agreement_id,cagr_id_flex_num,notice_period,notice_period_UOM,employee_category,supervisor_assignment_id,
      comment_id,effective_start_date,effective_end_date,concatenated_segments,grade_id,position_id,job_id,payroll_id,
      location_id,organization_id,pay_basis_id,special_ceiling_step_id,people_group_id,group_name,cagr_grade_def_id,
      cagr_concatenated_segments,soft_coding_keyflex_id,org_now_no_manager_warning,spp_delete_warning,entries_changed_warning,
      tax_district_changed_warning,no_managers_warning,other_managers_warning,hourly_salaried_warning,gsp_post_process_warning
           FROM xx_assignment_api_test;
     -- WHERE person_id IN(select person_id from per_all_people_f);
     BEGIN
	 FOR emp_assgn_loop IN emp_assn_cur 
  LOOP
        G_USER_ID := FND_PROFILE.VALUE('USER_ID');  --Gireesh
 G_LOGIN_ID := FND_PROFILE.VALUE('LOGIN_ID');--Gireesh
 v_org_id := FND_PROFILE.VALUE('ORG_ID');     --Gireesh
 v_resp_id := FND_PROFILE.VALUE('RESP_ID');  --Gireesh
 v_resp_APPL_id := FND_PROFILE.VALUE('RESP_APPL_ID'); --Gireesh
 v_business_group_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');--Gireesh
 FND_GLOBAL.APPS_INITIALIZE(G_USER_ID,v_resp_id,v_resp_APPL_id);--Gireesh
       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 = emp_assgn_loop.employee_number); 
                  -- EMPASSIGN_LOOP START
     /* Validating effective_end_date */
        IF emp_assgn_loop.effective_end_date IS NULL   -- EFF_END_DATE START
         THEN
                    l_status                                                         :='ERROR';
                    l_error_message                                          :='Effective end date can not be null';
           END IF;
           /* Validating effective_start_date */
            IF emp_assgn_loop.effective_start_date IS NULL   -- EFF_END_DATE START
         THEN
                    l_status                                                         :='ERROR';
                    l_error_message                                          :='Effective start date can not be null';
            END IF;
            IF (emp_assgn_loop.effective_end_date<emp_assgn_loop.effective_start_date)    -- EFF_END_DATE START
         THEN
                    l_status                                                         :='ERROR';
                    l_error_message                                          :='Effective end date can not be less than effective start date';
            END IF;
            /*Employee Number Validation */
            BEGIN
               SELECT employee_number
                     ,person_id
                     ,effective_start_date  --Gireesh
                     ,object_version_number --Gireesh
                      INTO l_employee_number
                           ,l_person_id
                           ,l_effective_start_date  --Gireesh
                           ,l_object_version_number  --Gireesh
                      FROM per_all_people_f
                      WHERE employee_number= TO_CHAR(emp_assgn_loop.employee_number)
               AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                          AND effective_end_date
               AND person_type_id = (SELECT person_type_id
                                          FROM per_person_types
                                         WHERE user_person_type = 'Employee')
               AND current_employee_flag = 'Y';
                      EXCEPTION
               WHEN OTHERS
               THEN
                             l_status                               :='ERROR';
                             l_error_message                :='Employee Number is not Valid';
           END;
   /*Job Validation */
    IF emp_assgn_loop.job_id IS NOT NULL
            THEN
               BEGIN
                  SELECT job_id
                    INTO l_job_id
                    FROM per_jobs
                   WHERE NAME = emp_assgn_loop.job_id;
                  l_job_check    := 'Y';
               EXCEPTION
                        WHEN NO_DATA_FOUND
                  THEN
                             l_status                               :='ERROR';
                             l_error_message                :='No Job Id Found';
                              WHEN OTHERS
                  THEN
                             l_status                               :='ERROR';
                             l_error_message                :='Job id is not Valid';
                           END;
           END IF;
           /*API Mode Selection */
            IF emp_assgn_loop.effective_start_date = TRUNC (SYSDATE)
            THEN
               l_assign_mode    := 'CORRECTION';
            ELSE
               l_assign_mode    := 'UPDATE';
            END IF;
   /*Location Validation */
        IF emp_assgn_loop.location_id IS NULL
                   THEN
                            l_status                          :='ERROR';
                            l_error_message           :='Location can not be NULL';
           END IF;
   /* Validating Payroll */
      IF emp_assgn_loop.payroll_id IS NULL
             THEN
                     l_status                              :='ERROR';
                     l_error_message               :='Payroll should be present for an employee';
      END IF;
      /* Validating Organization */
      IF emp_assgn_loop.organization_id IS NULL
             THEN
                     l_status                                       :='ERROR';
                     l_error_message                        :='No Organization is present for this employee';
      ELSE
                   BEGIN
                             SELECT organization_id
                             INTO l_organization_id
                             FROM org_organization_definitions
                             WHERE organization_id=emp_assgn_loop.organization_id;
                   EXCEPTION
                        WHEN OTHERS
                 THEN
                               l_status                             :='ERROR';
                                   l_error_message                  :='Organzation is not Valid';
               END;
      END IF;
   hr_assignment_api.update_emp_asg
         (p_validate                   =>FALSE
         ,p_effective_date             =>sysdate  --Gireesh
         ,p_datetrack_update_mode      =>'CORRECTION'  --Gireesh --l_assign_mode
         ,p_assignment_id              =>emp_assgn_loop.assignment_id
         ,p_object_version_number      =>l_object_version_number  --Gireesh  emp_assgn_loop.object_version_number
         ,p_supervisor_id              =>emp_assgn_loop.supervisor_id
         ,p_assignment_number              =>emp_assgn_loop.assignment_number
             ,p_default_code_comb_id       =>emp_assgn_loop.default_code_comb_id
             ,p_set_of_books_id            =>emp_assgn_loop.set_of_books_id
             ,p_supervisor_assignment_id                              =>emp_assgn_loop.supervisor_assignment_id
             ,p_concatenated_segments                                         =>emp_assgn_loop.concatenated_segments
             ,p_soft_coding_keyflex_id                                        =>emp_assgn_loop.soft_coding_keyflex_id
             ,p_comment_id                                                            =>emp_assgn_loop.comment_id
             ,p_effective_start_date                                          =>emp_assgn_loop.effective_start_date
             ,p_effective_end_date                                            =>emp_assgn_loop.effective_end_date
             ,p_no_managers_warning                                           =>x_no_managers_warning
             ,p_other_manager_warning                                         =>x_other_manager_warning );
 SELECT assignment_id
       ,effective_start_date
       ,object_version_number
       INTO lv_assignment_id
       ,lv_effective_date
       ,lv_object_version_number
       FROM per_all_assignments_f
       WHERE person_id =
       (SELECT person_id
       FROM per_all_people_f
       WHERE employee_number = emp_assgn_loop.employee_number); 
   hr_assignment_api.update_emp_asg_criteria
              (p_validate                    => FALSE
              ,p_effective_date              => l_effective_start_date -- l_effective_start_date --Gireesh --emp_assgn_loop.effective_date    -- Mandatory
              ,p_datetrack_update_mode       => 'CORRECTION' --l_assign_mode       -- Mandatory
              ,p_assignment_id               => emp_assgn_loop.assignment_id            -- Mandatory
              ,p_job_id                      => emp_assgn_loop.job_id
              ,p_position_id                 => emp_assgn_loop.position_id                  -- OPTIONAL
              ,p_location_id                 => emp_assgn_loop.location_id                  -- OPTIONAL
              ,p_organization_id             => emp_assgn_loop.organization_id                   -- OPTIONAL
              ,p_people_group_id             =>emp_assgn_loop.people_group_id
              ,p_soft_coding_keyflex_id      => emp_assgn_loop.soft_coding_keyflex_id
              ,p_concatenated_segments       => emp_assgn_loop.concatenated_segments
              ,p_object_version_number       => lv_object_version_number  --Gireesh emp_assgn_loop.object_version_number
                                                                    -- Mandatory
              ,p_special_ceiling_step_id     => emp_assgn_loop.special_ceiling_step_id
              ,p_group_name                  => emp_assgn_loop.group_name
              ,p_effective_start_date        => l_effective_start_date  --Gireesh --emp_assgn_loop.effective_start_date
              ,p_effective_end_date          => emp_assgn_loop.effective_end_date
              ,p_org_now_no_manager_warning  =>  x_org_now_no_manager_warning
              ,p_other_manager_warning       =>x_other_manager_warning
              ,p_spp_delete_warning          =>x_spp_delete_warning
              ,p_entries_changed_warning     => emp_assgn_loop.entries_changed_warning
              ,p_tax_district_changed_warning =>x_tax_district_changed_warning
              );
                     /*
           UPDATE xx_assignment_api_test
               SET status = 'Passed',
                   error_message = ''
             WHERE lineid = emp_assgn_loop.lineid;
                    */
     DBMS_OUTPUT.put_line ('SUCCESS');
	fnd_file.put_line
                                      (fnd_file.LOG,
					SQLERRM);
   commit;
   END LOOP;
  END Create_assignment;
  /*--End of package--------*/
 END SON_CREATE_ASSGMT_PKG;
/