HRMS API: Insert Salary Proposal (hr_maintain_proposal_api.insert_salary_proposal)

DECLARE
   p_effective_date              DATE;
   p_business_group_id           NUMBER;
   p_assignment_id               NUMBER;
   p_element_link_id             NUMBER;
   p_element_type_id             NUMBER;

   p_entry_type                  VARCHAR2 (500);
   p_creator_type                VARCHAR2 (1)    DEFAULT 'F';
   p_input_value_id1             NUMBER;
   p_entry_value1                VARCHAR2 (500)  DEFAULT NULL;
   v_effective_start_date        DATE;
   v_effective_end_date          DATE;
   v_element_entry_id            NUMBER;
   v_object_version_number       NUMBER;
   v_create_warning              BOOLEAN;
   v_err                         VARCHAR2 (1000);
   v_element_entry_id_up         NUMBER;
   l_pay_proposal_id             NUMBER;
   v_inv_next_sal_date_warning   BOOLEAN;
   v_proposed_salary_warning     BOOLEAN;
   v_approved_warning            BOOLEAN;
   v_payroll_warning             BOOLEAN;
   p_element_entry_id            NUMBER;
BEGIN
   BEGIN
      FOR emp IN (SELECT *
                    FROM xx_upload_elem_entry
                   WHERE assignment_id IS NOT NULL
                     AND people_group_id IS NOT NULL
                     AND business_group_id IS NOT NULL)
      LOOP
         p_effective_date := '01-JAN-2010';
         FOR elem IN (SELECT *
                        FROM xx_elements_name
                       WHERE element_name IN
                                ('Basic Salary',
                                 'Transport Allowance',
                                 'Shift Allowance',
                                 'Other Earnings',
                                 'Special Increase 2007',
                                 'Special Increase 2008',
                                 'SI EE Contribution',
                                 'ADSL Deduction',
                                 'Family Medical Deduction'
                                ))
         LOOP
            BEGIN
               BEGIN
                  SELECT lnk.element_link_id, lnk.element_type_id
                    INTO p_element_link_id, p_element_type_id
                    FROM pay_element_types_f typ, pay_element_links_f lnk
                   WHERE typ.element_type_id = lnk.element_type_id
                     AND typ.business_group_id = emp.business_group_id
                     AND UPPER (typ.element_name) = UPPER (elem.element_name)
                     AND lnk.payroll_id = emp.payroll_id
                     AND typ.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
                     AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     BEGIN
                        SELECT lnk.element_link_id, lnk.element_type_id
                          INTO p_element_link_id, p_element_type_id
                          FROM pay_element_types_f typ,
                               pay_element_links_f lnk
                         WHERE typ.element_type_id = lnk.element_type_id
                           AND typ.business_group_id = emp.business_group_id
                           AND UPPER (typ.element_name) =
                                                     UPPER (elem.element_name)
                           AND lnk.people_group_id =
                                    xx_get_link_p_grp_id (emp.people_group_id)
                           AND typ.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
                           AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           BEGIN
                              SELECT lnk.element_link_id, lnk.element_type_id
                                INTO p_element_link_id, p_element_type_id
                                FROM pay_element_types_f typ,
                                     pay_element_links_f lnk
                               WHERE typ.element_type_id = lnk.element_type_id
                                 AND typ.business_group_id =
                                                         emp.business_group_id
                                 AND UPPER (typ.element_name) =
                                                     UPPER (elem.element_name)
                                 AND typ.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
                                 AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
                           END;
                     END;
               END;
               SELECT val.input_value_id
                 INTO p_input_value_id1
                 FROM pay_input_values_f val
                WHERE val.element_type_id = p_element_type_id
                  AND val.business_group_id = emp.business_group_id
                  AND UPPER (val.NAME) = UPPER (elem.input_value_name)
                  AND val.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
               p_entry_type := NULL;
               p_entry_value1 := NULL;
               IF elem.element_name = 'Advanced Payment'
               THEN
                  p_entry_type := 'E';
                  p_entry_value1 := emp.advanced_payment;
               ELSIF elem.element_name = 'Labour Law Bonus'
               THEN
                  p_entry_type := 'E';
                  p_entry_value1 := emp.labour_low_bonus;
               END IF;
               IF elem.element_name = 'Basic Salary'
               THEN
                  BEGIN
                     hr_maintain_proposal_api.insert_salary_proposal
                        (p_pay_proposal_id                => l_pay_proposal_id,
                         p_assignment_id                  => emp.assignment_id,
                         p_business_group_id              => emp.business_group_id,
                         p_object_version_number          => v_object_version_number,
                         p_change_date                    => p_effective_date,
                         p_proposed_salary_n              => emp.basic_salary,
                         p_date_to                        => TO_DATE
                                                                ('31/12/4712',
                                                                 'DD/MM/YYYY'
                                                                ),
                         p_approved                       => 'Y',
                         p_element_entry_id               => p_element_entry_id,
                         p_inv_next_sal_date_warning      => v_inv_next_sal_date_warning,
                         p_proposed_salary_warning        => v_proposed_salary_warning,
                         p_approved_warning               => v_approved_warning,
                         p_payroll_warning                => v_payroll_warning,
                         p_multiple_components            => 'N'
                        );
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        v_err := NULL;
                       v_err := SQLERRM;
                  END;
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  p_element_link_id := NULL;
                  p_element_type_id := NULL;
                  p_input_value_id1 := NULL;
                  p_entry_type := NULL;
                  p_entry_value1 := NULL;
                  v_element_entry_id := NULL;
                  p_business_group_id := NULL;
                  p_assignment_id := NULL;
                  p_element_link_id := NULL;
                  p_entry_type := NULL;
                  p_input_value_id1 := NULL;
                  p_entry_value1 := NULL;
                  l_pay_proposal_id := NULL;
                  v_inv_next_sal_date_warning := NULL;
                  v_proposed_salary_warning := NULL;
                  v_approved_warning := NULL;
                  v_payroll_warning := NULL;
                  p_element_entry_id := NULL;
                  
            END;
         END LOOP;
      END LOOP;
   END;
   COMMIT;
END;
/