API Hook to Validate Payroll Element Entry Value

CREATE OR REPLACE PACKAGE APPS.xx_py_api_hooks

AS

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER);

   PROCEDURE check_entry_value (p_element_entry_id NUMBER);

END;

/

CREATE OR REPLACE PACKAGE BODY apps.xx_py_api_hooks

AS

   /***

    *       Author:         Faraz Anwar

    *       Desciption:     Element Entry Level Validations

    ***/

  /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert

   * Purpose            = Restrict Element Entries for Employees on Vacation

   **/

  

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER)

   AS

      l_element_type_id     NUMBER;

      l_emp_cat             VARCHAR2 (30);

      l_classification_id   NUMBER;

      CURSOR csr_vac

      IS

         SELECT employment_category

           FROM per_assignments_f paf, fnd_sessions sesh

          WHERE assignment_id = p_assignment_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;

      CURSOR csr_clasf

      IS

         SELECT classification_id

           FROM pay_element_types_f pet, fnd_sessions sesh

          WHERE element_type_id = p_element_type_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date

            AND classification_id IN (SELECT classification_id

                                        FROM pay_element_classifications);

   /*WHERE (classification_name) IN

            ('Voluntary Deductions', 'Involuntary Deductions', 'Employer Charges', 'Statutory Deductions',

             'Pre-Statutory Deductions')

     AND legislation_code = 'SA');*/

   BEGIN

      OPEN csr_vac;

      FETCH csr_vac

       INTO l_emp_cat;

      CLOSE csr_vac;

      OPEN csr_clasf;

      FETCH csr_clasf

       INTO l_classification_id;

      CLOSE csr_clasf;

      IF (l_classification_id IS NOT NULL) AND (l_emp_cat = 'VAC')

      THEN

         fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

         fnd_message.set_token ('MESSAGE', 'Employee on Vacation. Element Entries are not Allowed.');

         app_exception.raise_exception;

      END IF;

   END;

    /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert, Before Update

   * Purpose            = Validate Entries for Recovery Elements

   **/

   PROCEDURE check_entry_value (p_element_entry_id NUMBER)

   IS

      /* Cursor to Fetch Screen Entry Value */

      CURSOR c_curr_entry_value (p_element_entry_id NUMBER, p_input_name VARCHAR2, p_element_type_id NUMBER, p_effective_date DATE)

      IS

         SELECT peev.screen_entry_value

           FROM pay_element_entry_values_f peev, pay_input_values_f piv

          WHERE peev.element_entry_id = p_element_entry_id

            AND piv.NAME = p_input_name

            AND peev.input_value_id = piv.input_value_id

            AND piv.element_type_id = p_element_type_id

            AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date

            AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

      /* Cursor to Fetch Element Type Id */

      CURSOR c_elem_type (p_element_entry_id NUMBER, p_effective_date DATE)

      IS

         SELECT pel.element_type_id

           FROM pay_element_entries_f pee, pay_element_links_f pel

          WHERE pee.element_link_id = pel.element_link_id

            AND pee.element_entry_id = p_element_entry_id

            AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date

            AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date;

      CURSOR c_elem_set (p_element_type_id NUMBER)

      IS

         SELECT 'X'

           FROM apps.xx_py_element_set_members

          WHERE element_set_id = 13071 AND element_type_id = p_element_type_id;

      --

      --Element Set ID 13071 equals "Recovery Elements Entry Validation Set"

      --

      l_installment_amt      NUMBER;

      l_total_recovery_amt   NUMBER;

      l_repmt_amt            NUMBER;

      l_override_amt         NUMBER;

      l_total_due            NUMBER;

      l_effective_date       DATE;

      l_element_type_id      NUMBER;

      l_recovery_type        VARCHAR2(1);

   BEGIN

     

      l_effective_date := hr_general.effective_date;

      OPEN c_elem_type (p_element_entry_id, l_effective_date);

      FETCH c_elem_type

       INTO l_element_type_id;

      CLOSE c_elem_type;

      OPEN c_elem_set (L_element_type_id);

      FETCH c_elem_set

       INTO l_recovery_type;

      

      CLOSE c_elem_set;

      /* For Recovery Elements */

      IF l_recovery_type is not null                                                                 

      THEN

         OPEN c_curr_entry_value (p_element_entry_id, 'Total Recovery Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_total_recovery_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Installment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_installment_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Repayment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_repmt_amt;

         CLOSE c_curr_entry_value;

         /*

         open c_curr_entry_value (p_element_entry_id, 'Override Installment', p_element_type_id, l_effective_date);

         fetch c_curr_entry_value into  l_override_amt;

         close c_curr_entry_value;

         */

         /* verify that amounts entered are positive */

         IF (l_total_recovery_amt < 0) OR (l_installment_amt < 0) OR (l_repmt_amt < 0)

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Negative Amount not Allowed');

            app_exception.raise_exception;

         END IF;

         /* verify that amounts entered are logically correct */

         IF l_installment_amt > l_total_recovery_amt

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Installment Amount cannot be greater than Total Recovery Amount');

            app_exception.raise_exception;

         ELSIF l_repmt_amt > l_total_due

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Repayment Amount cannot be greater than Total Due');

            app_exception.raise_exception;

         END IF;

      END IF;

   END;

END;

/