Search This Blog

Tuesday, December 7, 2010

How to load monthly timesheet details

PROCEDURE xx_element_creation_proc (
   p_errbuf                 OUT NOCOPY      VARCHAR2,
   p_retcode                OUT NOCOPY      NUMBER,
   p_effective_start_date   IN              VARCHAR2,
   p_effective_end_date     IN              VARCHAR2
)
AS
   CURSOR cur_asg_emp (
      p_effective_start_date   IN   DATE,
      p_effective_end_date     IN   DATE
   )
   IS
      SELECT DISTINCT emp_no
                 FROM xx_time_sheet
                WHERE work_date BETWEEN TO_DATE (p_effective_start_date)
                                    AND TO_DATE (p_effective_end_date)
                  AND last_run_date IS NULL;
--Declare Variables
   lv_proceed                 VARCHAR2 (1)   := 'Y';
   ln_ot                      NUMBER         := 0;
   ln_sot                     NUMBER         := 0;
   ln_tardi                   NUMBER         := 0;
   ln_flexi                   NUMBER         := 0;
   lv_error_message           VARCHAR2 (300) := NULL;
   ln_group_code              NUMBER;
   ln_assignment_id           NUMBER;
   ln_tardi_time              NUMBER;
   ln_tardi_hrs               NUMBER;
   ln_ot_hrs                  NUMBER;
   ln_sot_hrs                 NUMBER;
   ln_input_value_ot          NUMBER;
   ln_input_value_sot         NUMBER;
   ln_input_value_tar         NUMBER;
   ln_ot_link_id              NUMBER;
   ld_effective_start_date    DATE;
   ld_effective_end_date      DATE;
   ln_element_entry_id        NUMBER;
   ln_object_version_number   NUMBER;
   lb_create_warning          BOOLEAN;
   ln_sot_link_id             NUMBER;
   ln_tardi_link_id           NUMBER;
   ln_bg_id                   NUMBER
                                    := fnd_profile.VALUE ('Business_Group_ID');
   ld_effective_date          DATE;
   lv_ot_eligibility          VARCHAR2 (4);
   ld_doj                     DATE;
BEGIN
   SELECT TRUNC (TO_DATE (p_effective_end_date), 'Month')
     INTO ld_effective_date
     FROM DUAL;
   FOR cur_rec IN cur_asg_emp (p_effective_start_date, p_effective_end_date)
   LOOP
--Reset Variables
      lv_proceed := 'Y';
      ln_ot := 0;
      ln_sot := 0;
      ln_tardi := 0;
      ln_flexi := 0;
      lv_error_message := NULL;
      ln_group_code := NULL;
      ln_assignment_id := NULL;
      ln_tardi_time := 0;
      ln_tardi_hrs := 0;
      ln_ot_hrs := 0;
      ln_sot_hrs := 0;
      ln_input_value_ot := NULL;
      ln_input_value_sot := NULL;
      ln_input_value_tar := NULL;
      ln_ot_link_id := NULL;
      ld_effective_start_date := NULL;
      ld_effective_end_date := NULL;
      ln_element_entry_id := NULL;
      ln_object_version_number := NULL;
      lb_create_warning := NULL;
      ln_sot_link_id := NULL;
      ln_tardi_link_id := NULL;
      lv_ot_eligibility := NULL;
      ld_doj := NULL;
      -----Calculate Timesheet Data
      BEGIN
         SELECT   ROUND
                     (xx_seconds_to_hrs
                                   (SUM (xx_hours_in_seconds (xxm.over_time)
                                        )
                                   ),
                      2
                     ) over_time,
                  ROUND
                     (xx_seconds_to_hrs
                           (SUM (xx_hours_in_seconds (xxm.special_over_time)
                                )
                           ),
                      2
                     ) special_over_time,
                  ROUND
                     (xx_seconds_to_hrs
                              (SUM (xx_hours_in_seconds (xxm.tardiness_time)
                                   )
                              ),
                      2
                     ) tardiness_time,
                  ROUND
                     (xx_seconds_to_hrs
                                  (SUM (xx_hours_in_seconds (xxm.flex_hours)
                                       )
                                  ),
                      2
                     ) flexi_hours
             INTO ln_ot,
                  ln_sot,
                  ln_tardi,
                  ln_flexi
             FROM xx_time_sheet xxm
            WHERE emp_no = cur_rec.emp_no
              AND work_date BETWEEN TO_DATE (p_effective_start_date)
                                AND TO_DATE (p_effective_end_date)
         GROUP BY emp_no;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_message := 'Timesheet Data Issue. Check the data';
            lv_proceed := 'N';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
      END;
      --Decode for group id
      BEGIN
         SELECT NVL (DECODE (paaf.people_group_id,
                             61, 3,
                             62, 2,
                             63, 1,
                             paaf.people_group_id
                            ),
                     0
                    ) "group_code",
                paaf.assignment_id, paaf.ass_attribute1 ot_eligibility,
                papf.original_date_of_hire
           INTO ln_group_code,
                ln_assignment_id, lv_ot_eligibility,
                ld_doj
           FROM per_all_people_f papf, per_all_assignments_f paaf
          WHERE paaf.person_id = papf.person_id
            AND p_effective_end_date BETWEEN papf.effective_start_date
                                         AND papf.effective_end_date
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
            AND p_effective_end_date BETWEEN paaf.effective_start_date
                                         AND paaf.effective_end_date
            AND papf.employee_number = cur_rec.emp_no;
         IF ln_group_code = 0
         THEN
            lv_error_message := 'Please enter people group for the employee';
            lv_proceed := 'N';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
         END IF;
         IF ld_doj > ld_effective_date
         THEN
            ld_effective_date := ld_doj;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_error_message :=
                              'Error in getting people group. Check the data';
            lv_proceed := 'N';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
      END;
      IF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'N'
      THEN
         ln_tardi_time := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
         lv_proceed := 'N';
         IF ln_tardi_time > 0
         THEN
            lv_proceed := 'Y';
            ln_tardi_hrs := ln_tardi_time;
         END IF;
       ELSIF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'Y'
      THEN
         IF ln_ot >= ln_flexi
         THEN
            ln_ot_hrs := ln_ot - ln_flexi;
            ln_sot_hrs := ln_sot;
            ln_tardi_hrs := ln_tardi;
         ELSIF ln_ot < ln_flexi
         THEN
            IF (ln_ot + ln_sot) >= ln_flexi
            THEN
               ln_ot_hrs := 0;
               ln_sot_hrs := (ln_ot + ln_sot) - ln_flexi;
               ln_tardi_hrs := ln_tardi;
            ELSE
               ln_ot_hrs := 0;
               ln_sot_hrs := 0;
               ln_tardi_hrs := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
            END IF;
         END IF;
      ELSIF ln_group_code = 1
      THEN
         lv_error_message := 'No Element Creation for Managers';
         lv_proceed := 'N';
         fnd_file.put_line (fnd_file.LOG,
                            cur_rec.emp_no || ' ~ ' || lv_error_message
                           );
      ELSE
         ln_ot_hrs := ln_ot;
         ln_sot_hrs := ln_sot;
         ln_tardi_hrs := ln_tardi;
      END IF;
      BEGIN
         SELECT input_value_id
           INTO ln_input_value_ot
           FROM pay_input_values_f pi, pay_element_types_f pe
          WHERE pi.NAME = 'Total Hours'
            AND pi.element_type_id = pe.element_type_id
            AND pe.element_name = 'Overtime';
      EXCEPTION
         WHEN TOO_MANY_ROWS
         THEN
            lv_error_message := 'More than one Hours input is defined';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
         WHEN OTHERS
         THEN
            lv_error_message := 'Error in getting Hours Input ID';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
      END;
      BEGIN
         SELECT input_value_id
           INTO ln_input_value_sot
           FROM pay_input_values_f pi, pay_element_types_f pe
          WHERE pi.NAME = 'Total Hours'
            AND pi.element_type_id = pe.element_type_id
            AND pe.element_name = 'Special Overtime';
      EXCEPTION
         WHEN TOO_MANY_ROWS
         THEN
            lv_error_message := 'More than one Hours input is defined';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
         WHEN OTHERS
         THEN
            lv_error_message := 'Error in getting Hours Input ID';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
      END;
      BEGIN
         SELECT input_value_id
           INTO ln_input_value_tar
           FROM pay_input_values_f pi, pay_element_types_f pe
          WHERE pi.NAME = 'Hours'
            AND pi.element_type_id = pe.element_type_id
            AND pe.element_name = 'Tardiness';
      EXCEPTION
         WHEN TOO_MANY_ROWS
         THEN
            lv_error_message := 'More than one Hours input is defined';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
         WHEN OTHERS
         THEN
            lv_error_message := 'Error in getting Hours Input ID';
            fnd_file.put_line (fnd_file.LOG,
                               cur_rec.emp_no || ' ~ ' || lv_error_message
                              );
            lv_proceed := 'N';
      END;
      IF ln_ot_hrs > 0 AND lv_proceed = 'Y'
      THEN                               --Create Element entry for Over Time.
         BEGIN
            SELECT pelf.element_link_id
              INTO ln_ot_link_id
              FROM pay_element_types_f petf, pay_element_links_f pelf
             WHERE petf.element_type_id = pelf.element_type_id
               AND petf.element_name = 'Overtime';
         EXCEPTION
            WHEN TOO_MANY_ROWS
            THEN
               lv_error_message :=
                                  'More than one Overtime element is defined';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               --lv_proceed = 'N' ;
               NULL;
            WHEN OTHERS
            THEN
               lv_error_message := 'Error in getting OT link ID';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               -- lv_proceed = 'N' ;
               NULL;
         END;
         --Call element creation API
         BEGIN
            pay_element_entry_api.create_element_entry
               (p_effective_date             => ld_effective_date,
                p_business_group_id          => ln_bg_id,
                p_assignment_id              => ln_assignment_id
                                                             --p_assignment_id
                                                                ,
                p_element_link_id            => ln_ot_link_id,
                p_entry_type                 => 'E'                --For Entry
                                                   ,
                p_input_value_id1            => ln_input_value_ot
                                                    --Input Value ID for Hours
                                                                 ,
                p_entry_value1               => ln_ot_hrs,
                p_effective_start_date       => ld_effective_start_date,
                p_effective_end_date         => ld_effective_end_date,
                p_element_entry_id           => ln_element_entry_id,
                p_object_version_number      => ln_object_version_number,
                p_create_warning             => lb_create_warning
               );
            UPDATE xx_time_sheet
               SET last_run_date = SYSDATE
             WHERE emp_no = cur_rec.emp_no;
            fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no
                               || ' ~ '
                               || 'Overtime Element Created ~ '
                               || ln_ot_hrs
                              );
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_message := 'Error in OT element creation - API Error';
               fnd_file.put_line (fnd_file.LOG,
                                     cur_rec.emp_no
                                  || ' ~ '
                                  || lv_error_message
                                  || ' ~ '
                                  || SUBSTR (SQLERRM, 1, 90)
                                 );
         END;
      END IF;
      IF ln_sot_hrs > 0 AND lv_proceed = 'Y'
      THEN                       --Create element entry for Special Over Time.
         BEGIN
            SELECT pelf.element_link_id
              INTO ln_sot_link_id
              FROM pay_element_types_f petf, pay_element_links_f pelf
             WHERE petf.element_type_id = pelf.element_type_id
               AND petf.element_name = 'Special Overtime';
         EXCEPTION
            WHEN TOO_MANY_ROWS
            THEN
               lv_error_message :=
                          'More than one Special Overtime element is defined';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               --lv_proceed = 'N' ;
               NULL;
            WHEN OTHERS
            THEN
               lv_error_message := 'Error in getting SOT link ID';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               -- lv_proceed = 'N' ;
               NULL;
         END;
         BEGIN
            pay_element_entry_api.create_element_entry
               (p_effective_date             => ld_effective_date,
                p_business_group_id          => ln_bg_id,
                p_assignment_id              => ln_assignment_id
                                                             --p_assignment_id
                                                                ,
                p_element_link_id            => ln_sot_link_id,
                p_entry_type                 => 'E'                --For entry
                                                   ,
                p_input_value_id1            => ln_input_value_sot
                                                    --Input Value ID for Hours
                                                                  ,
                p_entry_value1               => ln_sot_hrs,
                p_effective_start_date       => ld_effective_start_date,
                p_effective_end_date         => ld_effective_end_date,
                p_element_entry_id           => ln_element_entry_id,
                p_object_version_number      => ln_object_version_number,
                p_create_warning             => lb_create_warning
               );
            UPDATE xx_time_sheet
               SET last_run_date = SYSDATE
             WHERE emp_no = cur_rec.emp_no;
            fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no
                               || ' ~ '
                               || 'Special Overtime Element Created ~ '
                               || ln_sot_hrs
                              );
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_message :=
                                  'Error in SOT element creation - API Error';
               fnd_file.put_line (fnd_file.LOG,
                                     cur_rec.emp_no
                                  || ' ~ '
                                  || lv_error_message
                                  || ' ~ '
                                  || SUBSTR (SQLERRM, 1, 90)
                                 );
         END;
      END IF;
      IF ln_tardi_hrs > 0 AND lv_proceed = 'Y'
      THEN                       --Create element entry for Special Over Time.
         BEGIN
            SELECT pelf.element_link_id
              INTO ln_tardi_link_id
              FROM pay_element_types_f petf, pay_element_links_f pelf
             WHERE petf.element_type_id = pelf.element_type_id
               AND petf.element_name = 'Tardiness';
         EXCEPTION
            WHEN TOO_MANY_ROWS
            THEN
               lv_error_message :=
                                 'More than one Tardiness element is defined';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               --lv_proceed = 'N' ;
               NULL;
            WHEN OTHERS
            THEN
               lv_error_message := 'Error in getting OT link ID';
               fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no || ' ~ ' || lv_error_message
                                 );
               -- lv_proceed = 'N' ;
               NULL;
         END;
         BEGIN
            pay_element_entry_api.create_element_entry
               (p_effective_date             => ld_effective_date,
                p_business_group_id          => ln_bg_id,
                p_assignment_id              => ln_assignment_id
                                                             --p_assignment_id
                                                                ,
                p_element_link_id            => ln_tardi_link_id,
                p_entry_type                 => 'E'                --For Entry
                                                   ,
                p_input_value_id1            => ln_input_value_tar
                                                    --Input Value ID for Hours
                                                                  ,
                p_entry_value1               => ln_tardi_hrs,
                p_effective_start_date       => ld_effective_start_date,
                p_effective_end_date         => ld_effective_end_date,
                p_element_entry_id           => ln_element_entry_id,
                p_object_version_number      => ln_object_version_number,
                p_create_warning             => lb_create_warning
               );
            UPDATE xx_time_sheet
               SET last_run_date = SYSDATE
             WHERE emp_no = cur_rec.emp_no;
            fnd_file.put_line (fnd_file.LOG,
                                  cur_rec.emp_no
                               || ' ~ '
                               || 'Tardiness Element Created ~ '
                               || ln_tardi_hrs
                              );
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_error_message :=
                                'Error in Tardi element creation - API Error';
               fnd_file.put_line (fnd_file.LOG,
                                     cur_rec.emp_no
                                  || ' ~ '
                                  || lv_error_message
                                  || ' ~ '
                                  || SUBSTR (SQLERRM, 1, 90)
                                 );
         END;
      END IF;
   END LOOP;
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG,
                         'Fatal Error' || ' ~ ' || SUBSTR (SQLERRM, 1, 60)
                        );
      p_retcode := 3;
END xx_element_creation_proc;
-----------------------------------------------------------------------------------------------------------------------------------
--Element Creation - End

5 comments:

  1. Hi! I just planned to discover you ever get complications with hackers?
    This previous blog (wordpress blogs) had been broken in to
    i appeared losing months of diligence due to simply no back.
    Do you possess virtually any methods to end cyberpunks?
    Here is my webpage ... Online tarot readings

    ReplyDelete
  2. *Timesheet plays a significant role in this as it processes the amount of time worked by the employee and based on this their wages are calculated. In the case of web based timesheet the data should be well encrypted before sending from the client system to the central server hence the software should use comparability secure "https" protocol for authentication and other data manipulation purpose

    ReplyDelete
  3. Monthly Timesheet Template When the payroll and/or billing cycle is monthly, you need a monthly timesheet to record such data. During that period of time, you can record detailed data about tasks completed in a project. Information which is important when it comes to billing your clients, paying employee salaries, managing costs, tracking projects and estimating completion times, and other tasks integral to business operations. Azor offers professionally designed monthly timesheet templates that may not only solve your time management problems. But with a little adaptation of such a template to suit the way work is done at your workplace, you will be able to bring in more productivity to the workplace.

    ReplyDelete
  4. Thank you for posting this blog I, in fact, cherished it and located up some precise blogs about oracle........go to our net internet page related to oracle fusion procurement all modules education
    Thanks for sharing this form of useful information
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  5. Simply wish to say your article is as astonishing. The clarity in your post is simply great, and I could assume you are an expert on this subject. Same as your blog i found another one Oracle Fusion Procurement .Actually I was looking for the same information on internet for Oracle Fusion Procurement and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.

    ReplyDelete