API to Delete Assignment Set

*** Before going to Delete Assignment Set first we have to delete the Assignment Amendments after that only we have to delete the Assignment Set.

/*------------------------------------------------------
1) API to Delete Assignment Set Amendments
-------------------------------------------------------*/
DECLARE
   CURSOR c1
   IS
      SELECT hasa.ROWID, has.assignment_set_name, hasa.*
        FROM hr_assignment_set_amendments hasa, hr_assignment_sets has
       WHERE has.assignment_set_id = hasa.assignment_set_id
             AND has.assignment_set_name = 'Leave_Salary_FEB_20';
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         hr_assignment_set_amds_pkg.delete_row (i.ROWID);
         COMMIT;
         DBMS_OUTPUT.
          put_line (
               i.assignment_id
            || ' has been Removed from Assignment Set '
            || i.assignment_set_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;



/*---------------------------------------
2) API to Delete Assignment Set
------------------------------------------*/

DECLARE
   CURSOR c1
   IS
      SELECT ROWID, has.*
        FROM hr_assignment_sets has
       WHERE has.assignment_set_name = 'Leave_Salary_FEB_20';
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         hr_assignment_sets_pkg.delete_row (i.ROWID);
         COMMIT;
         DBMS_OUTPUT.
          put_line (
            i.assignment_set_name || ' has been Deleted Successfully !!!');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

API to Create Assignment Set

***The following API used to create the Assignment Set and Assign the Employees to the Assignment Set Amendments.


PROCEDURE main (
      p_emp_num   IN       VARCHAR2,
      p_period    IN       VARCHAR2
   )
   IS
      CURSOR c1
      IS
         SELECT x.*, pam.assignment_id
           FROM sawaeed.xx_payroll_leave_type_vacation x,
                per_all_people_f ppf,
                per_all_assignments_f pam
          WHERE 1 = 1
            AND x.file_no = ppf.employee_number
            AND ppf.person_id = pam.person_id
            AND SYSDATE BETWEEN ppf.effective_start_date
                            AND ppf.effective_end_date
            AND SYSDATE BETWEEN pam.effective_start_date
                            AND pam.effective_end_date
            AND x.file_no = NVL (p_emp_num, x.file_no)
            AND UPPER (x.period) = UPPER (p_period);

      --
      l_assignment_set_id   NUMBER;
   BEGIN
      --Creating Assignment Set
      BEGIN
         LOG (   'Creating Assignment Set: Leave_Salary_'
              || TO_CHAR (TO_DATE (UPPER (p_period), 'MON-YYYY'), 'MON_YY')
             );
         hr_jp_ast_utility_pkg.create_asg_set
                (p_assignment_set_name      =>    'Leave_Salary_'
                                               || TO_CHAR
                                                     (TO_DATE
                                                             (UPPER (p_period),
                                                              'MON-YYYY'
                                                             ),
                                                      'MON_YY'
                                                     ),
                 p_business_group_id        => 82,
                 p_payroll_id               => null,--You Can Pass Payroll ID also if you want.
                 p_assignment_set_id        => l_assignment_set_id
                );
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            LOG ('Error at Assignment Set Creation:' || SQLERRM);
      END;

      IF l_assignment_set_id IS NOT NULL
      THEN
         LOG ('Assigning Employees to the Assignment Set');

         FOR i IN c1
         LOOP
            hr_jp_ast_utility_pkg.create_asg_set_amd
                                 (p_assignment_set_id       => l_assignment_set_id,
                                  p_assignment_id           => i.assignment_id,
                                  p_include_or_exclude      => 'I'
                                 );
            COMMIT;
         END LOOP;

             ELSE
         LOG (' Assignment Set Not Created');
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         LOG ('Error at Main;' || SQLERRM);
   END;