Update element entry costing information in Apps R12 HRMS

Requirement : To update the Costing information of all elements in one payroll to other.
Step1: creating staging table XX_UPDATE_LINK_COSTING_NOL.
step2: insert into staging table using select script with required columns.
Step3: run the plsql block or procdure.

1.
CREATE TABLE APPS.XX_UPDATE_LINK_COSTING_NOL
(
  ELEMENT_TYPE_ID   NUMBER,
  COST_SEGMENT1     NUMBER,
  COST_SEGMENT2     NUMBER,
  COST_SEGMENT3     NUMBER,
  COST_SEGMENT4     NUMBER,
  COST_SEGMENT5     NUMBER,
  COST_SEGMENT6     NUMBER,
  COST_SEGMENT7     NUMBER,
  BALANCE_SEGMENT1  NUMBER,
  BALANCE_SEGMENT2  NUMBER,
  BALANCE_SEGMENT3  NUMBER,
  BALANCE_SEGMENT4  NUMBER,
  BALANCE_SEGMENT5  NUMBER,
  BALANCE_SEGMENT6  NUMBER,
  BALANCE_SEGMENT7  NUMBER,
  VALID_FLAG        VARCHAR2(1 BYTE), --Default 'N'
  ERR               VARCHAR2(2000 BYTE),
  EXTRA_INFO1       VARCHAR2(200 BYTE), --Payroll _Id
  EXTRA_INFO2       VARCHAR2(200 BYTE),
  EXTRA_INFO3       VARCHAR2(200 BYTE)
)

2.

insert into xx_update_link_costing_nol
SELECT element_type_id,
cost.segment1,cost.segment2, cost.segment3, cost.segment4, cost.segment5, cost.segment6, cost.segment7,
balance.segment1,balance.segment2, balance.segment3, balance.segment4, balance.segment5, balance.segment6, balance.segment7,
'N', --default value
null,
61,--Payroll id to which we are going to update
null,
null
FROM
PAY_COST_ALLOCATION_KEYFLEX cost,
PAY_COST_ALLOCATION_KEYFLEX balance,
PAY_ELEMENT_LINKS_F pelf
WHERE
pelf.COST_ALLOCATION_KEYFLEX_ID = cost.COST_ALLOCATION_KEYFLEX_ID
and pelf.BALANCING_KEYFLEX_ID  = balance.COST_ALLOCATION_KEYFLEX_ID
and ELEMENT_LINK_ID = 1922--taking only element or testing

COMMIT

3.



CREATE OR REPLACE PROCEDURE P_UPDATE_ELEMENT_ENTRY
AS
--DECLARE
   p_effective_date                DATE;
   v_err                           VARCHAR2 (1000);
   p_object_version_number         NUMBER;
   p_cost_allocation_keyflex_id    NUMBER;
   p_balancing_keyflex_id          NUMBER;
   p_cost_concat_segments_out      VARCHAR2 (250);
   p_balance_concat_segments_out   VARCHAR2 (250);
   p_effective_start_date          DATE;
   p_effective_end_date            DATE;
-------------------------------
   p_cost_segment1                 VARCHAR2 (50)   := NULL;
   p_cost_segment2                 VARCHAR2 (50)   := NULL;
   p_cost_segment3                 VARCHAR2 (50)   := NULL;
   p_cost_segment4                 VARCHAR2 (50)   := NULL;
   p_cost_segment5                 VARCHAR2 (50)   := NULL;
   p_cost_segment6                 VARCHAR2 (50)   := NULL;
   p_cost_segment7                 VARCHAR2 (50)   := NULL;
 
   p_balance_segment1              VARCHAR2 (50)   := NULL;
   p_balance_segment2              VARCHAR2 (50)   := NULL;
   p_balance_segment3              VARCHAR2 (50)   := NULL;
   p_balance_segment4              VARCHAR2 (50)   := NULL;
   p_balance_segment5              VARCHAR2 (50)   := NULL;
   p_balance_segment6              VARCHAR2 (50)   := NULL;
   p_balance_segment7              VARCHAR2 (50)   := NULL;
   --DBMS_OUTPUT.PUT_LINE('AFTER LOCAL VAR -1');
 
   CURSOR c1
   IS
      (SELECT *
         FROM xx_update_link_costing_nol xx
        WHERE xx.valid_flag = 'N' AND xx.element_type_id IS NOT NULL)
      FOR UPDATE;
   v_c1                            c1%ROWTYPE;
BEGIN
  --INSERT INTO fnd_sessions        VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
   OPEN c1;
   LOOP
      FETCH c1
       INTO v_c1;
      EXIT WHEN c1%NOTFOUND;
      FOR i IN (SELECT element_link_id
                  FROM pay_element_links_f lnk
                 WHERE lnk.business_group_id = 81
                   AND lnk.element_type_id = v_c1.element_type_id
                   AND lnk.payroll_id = v_c1.extra_info1
                   AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY'))
      LOOP
         BEGIN
            SELECT lnk.effective_start_date, lnk.object_version_number
              INTO p_effective_date, p_object_version_number
              FROM pay_element_links_f lnk
             WHERE lnk.business_group_id = 81
               AND lnk.element_link_id = i.element_link_id
               AND lnk.effective_end_date =  TO_DATE ('31/12/4712', 'DD/MM/YYYY');
            BEGIN
              
               p_cost_segment1 := NVL(TO_CHAR(v_c1.cost_segment1, 'fm099999'),'023800');--NVL(v_c1.cost_segment1,'023800');
               p_cost_segment2 := NVL(NULL,'000000');
               p_cost_segment3 := NVL(TO_CHAR(v_c1.cost_segment3, 'fm0999999'),'000000');
               p_cost_segment4 := NVL(NULL,'00');
               p_cost_segment5 := NVL(NULL,'0000');
               p_cost_segment6 := NVL(NULL,'0000');
               p_cost_segment7 := NVL(NULL,'0000');
              
              
           
               p_balance_segment1 := NVL(TO_CHAR(v_c1.balance_segment1, 'fm099999'),'023800');--NVL (v_c1.balance_segment1, '023800');
               p_balance_segment2 := NVL (NULL, '000000');
               p_balance_segment3 := NVL (TO_CHAR(v_c1.balance_segment3, 'fm0999999'), '000000');
               p_balance_segment4 := NVL (NULL, '00');
               p_balance_segment5 := NVL (NULL, '0000');
               p_balance_segment6 := NVL (NULL, '0000');
               p_balance_segment7 := NVL (NULL, '0000');
              
            
-------------
               pay_element_link_api.update_element_link
                  (p_validate                         => FALSE,
                   p_effective_date                   => p_effective_date,
                   p_element_link_id                  => i.element_link_id,
                   p_datetrack_mode                   => 'CORRECTION',
                   p_transfer_to_gl_flag              => 'Y',
                   p_costable_type                    => 'C',
                   p_cost_segment1                    => p_cost_segment1,
                   p_cost_segment2                    => p_cost_segment2,
                   p_cost_segment3                    => p_cost_segment3,
                   p_cost_segment4                    => p_cost_segment4,
                   p_cost_segment5                    => p_cost_segment5,
                   p_cost_segment6                    => p_cost_segment6,
                   p_cost_segment7                    => p_cost_segment7,
                                                        
                   p_balance_segment1                 => p_balance_segment1,
                   p_balance_segment2                 => p_balance_segment2,
                   p_balance_segment3                 => p_balance_segment3,
                   p_balance_segment4                 => p_balance_segment4,
                   p_balance_segment5                 => p_balance_segment5,
                   p_balance_segment6                 => p_balance_segment6,
                   p_balance_segment7                 => p_balance_segment7,
                  
                  
                   p_object_version_number            => p_object_version_number,
                   p_cost_allocation_keyflex_id       => p_cost_allocation_keyflex_id,
                   p_balancing_keyflex_id             => p_balancing_keyflex_id,
                   p_cost_concat_segments_out         => p_cost_concat_segments_out,
                   p_balance_concat_segments_out      => p_balance_concat_segments_out,
                   p_effective_start_date             => p_effective_start_date,
                   p_effective_end_date               => p_effective_end_date
                  );
               UPDATE xx_update_link_costing_nol xx
                  SET xx.err = 'Done',
                      xx.valid_flag = 'Y'
                WHERE CURRENT OF c1;
                DBMS_OUTPUT.PUT_LINE('UNDER API-1');
            EXCEPTION
               WHEN OTHERS
               THEN
                  p_cost_segment1 := NULL;
                  p_cost_segment2 := NULL;
                  p_cost_segment3 := NULL;
                  p_cost_segment4 := NULL;
                  p_cost_segment5 := NULL;
                  p_cost_segment6 := NULL;
                  p_cost_segment7 := NULL;
                 
                  p_balance_segment1 := NULL;
                  p_balance_segment2 := NULL;
                  p_balance_segment3 := NULL;
                  p_balance_segment4 := NULL;
                  p_balance_segment5 := NULL;
                  p_balance_segment6 := NULL;
                  p_balance_segment7 := NULL;
                 
                  p_effective_date := NULL;
                  p_object_version_number := NULL;
                  p_cost_allocation_keyflex_id := NULL;
                  p_balancing_keyflex_id := NULL;
                  p_cost_concat_segments_out := NULL;
                  p_balance_concat_segments_out := NULL;
                  p_effective_start_date := NULL;
                  p_effective_end_date := NULL;
                  v_err := NULL;
                  v_err := (SQLERRM);
                  UPDATE xx_update_link_costing_nol xx
                     SET xx.err = v_err,
                         xx.valid_flag = 'N'
                   WHERE CURRENT OF c1;
                   DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -1');
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
                  p_cost_segment1 := NULL;
                  p_cost_segment2 := NULL;
                  p_cost_segment3 := NULL;
                  p_cost_segment4 := NULL;
                  p_cost_segment5 := NULL;
                  p_cost_segment6 := NULL;
                  p_cost_segment7 := NULL;
                 
                  p_balance_segment1 := NULL;
                  p_balance_segment2 := NULL;
                  p_balance_segment3 := NULL;
                  p_balance_segment4 := NULL;
                  p_balance_segment5 := NULL;
                  p_balance_segment6 := NULL;
                  p_balance_segment7 := NULL;
                 
               p_effective_date := NULL;
               p_object_version_number := NULL;
               p_cost_allocation_keyflex_id := NULL;
               p_balancing_keyflex_id := NULL;
               p_cost_concat_segments_out := NULL;
               p_balance_concat_segments_out := NULL;
               p_effective_start_date := NULL;
               p_effective_end_date := NULL;
               v_err := NULL;
               UPDATE xx_update_link_costing_nol xx
                  SET xx.err = 'Element Link Select Error',
                      xx.valid_flag = 'N'
                WHERE CURRENT OF c1;
                DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -2');
         END;
      END LOOP;
   END LOOP;
   CLOSE c1;
   COMMIT;
END;

--select 1 from dual

Script to copy responsibilities of one user account to another user account in Oracle Apps R12

/**************************************** *
PURPOSE: To copy responsibilities of one user account to another user account * *
AUTHOR: Afzal * ***********************/ 
DECLARE --
resp_count NUMBER := 0; --
CURSOR src_user_resp_details IS

SELECT DISTINCT fa.application_short_name, fr.responsibility_key , fsg.security_group_key FROM fnd_application fa ,
fnd_responsibility fr , fnd_user fu , fnd_user_resp_groups_all furga, fnd_security_groups fsg WHERE 1 = 1
AND fu.user_name = 'EG_AYMAN' AND fu.user_id = furga.user_id
AND fa.application_id = fr.application_id AND furga.responsibility_id = fr.responsibility_id
AND furga.responsibility_application_id = fa.application_id AND fsg.security_group_id = furga.security_group_id
-- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
AND furga.end_date IS NULL;

-- --
BEGIN FOR user_resp_details_rec IN src_user_resp_details
LOOP
BEGIN
--
fnd_user_pkg.addresp (username => 'EG_OSAMA',
resp_app => user_resp_details_rec.application_short_name,
resp_key => user_resp_details_rec.responsibility_key,
security_group => user_resp_details_rec.security_group_key,
description => NULL, start_date => SYSDATE, end_date => NULL );
--
resp_count := resp_count + 1;
--
EXCEPTION WHEN OTHERS THEN
--
DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
--
END;
END LOOP;
--
DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
--
COMMIT;
END;

Update Assignment Status in Oracle HRMS as per the leave applied

Requirement :
Updating the assignment status as per the leave applied to custom status(ex: Annual Leave, Unpaid Leave status), and once employee joined again updating status to 'Active'.

This is just part of the whole procedure, but the main api's, syntax can be derived from it.
Package: dt_api.find_dt_upd_modes is used find the update modes in HRMS.
Package: HR_ASSIGNMENT_API.SUSPEND_EMP_ASG is used to update the status (Unpaid Leave status or Annual Leave status, this is custom statuses)
Package : HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG is used to update the status to active.

Above 3 packages examples are given below as syntax.....

PROCEDURE UPDATE_EMP_ASSIG_ST_2(ERRBUF      OUT NOCOPY VARCHAR2,
                              RETCODE     OUT NOCOPY VARCHAR2,
                              P_BUSINESS_GROUP_ID IN NUMBER,
                              P_PERSON_ID IN NUMBER,
                              P_ABSENCE_ATTENDANCE_ID IN NUMBER) IS
                           
--Commented the Session month (before and after) so to trigger particular absence_attendance_id and added as of sysdate
/*
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
--      05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
--      26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
--      26 dec 2014 Suspend
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave
--      26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave
--      26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as  Long Unpaid Leave
--      26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
--      26 dec 2014 Suspend
*/
------------------------------------------------------------------------------------
------------CURSOR TO GET 'Advanced Pay Information' DETAILS.......................
--local variablese
--declare
   lb_correction                  BOOLEAN;
   lb_update                      BOOLEAN;
   lb_update_override             BOOLEAN;
   lb_update_change_insert        BOOLEAN;
   lc_dt_ud_mode varchar2(200);
--parameter vairables  
  --                            P_BUSINESS_GROUP_ID number := 81;
--                              EVENT_TYPE varchar2(200) := '';--'insert';--'';
--                              P_PERSON_ID number :=null;--26876;
--                              P_ABSENCE_ATTENDANCE_ID number :=null;--3700945;
--cursor to bring the advance leave employee details  
CURSOR GET_CHANGED_EMP IS

         SELECT ASG.ASSIGNMENT_ID
          ,PPL.LAST_NAME NAME
          ,PPL.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
          ,PPL.FULL_NAME FULL_NAME
          ,ENTS.ELEMENT_ENTRY_ID X_ID
          ,FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE) DATE_START
          ,FND_DATE.canonical_to_date(vals2.SCREEN_ENTRY_VALUE) DATE_END
          ,FND_DATE.canonical_to_date(vals3.SCREEN_ENTRY_VALUE) UNPAID_DATE
          ,VALS4.SCREEN_ENTRY_VALUE ADV_FLAG
          ,ENTS.EFFECTIVE_START_DATE X_S_D
          ,ENTS.EFFECTIVE_END_DATE X_E_D
     FROM PER_ALL_PEOPLE_F           PPL
         ,PER_ALL_ASSIGNMENTS_F      ASG
         ,PAY_ELEMENT_TYPES_F        TYP
         ,PAY_INPUT_VALUES_F         INP1
         ,PAY_INPUT_VALUES_F         INP2
         ,PAY_INPUT_VALUES_F         INP3
         ,PAY_INPUT_VALUES_F         INP4
         ,PAY_ELEMENT_ENTRIES_F      ENTS
         ,PAY_ELEMENT_ENTRY_VALUES_F VALS1
         ,PAY_ELEMENT_ENTRY_VALUES_F VALS2
         ,PAY_ELEMENT_ENTRY_VALUES_F VALS3
         ,PAY_ELEMENT_ENTRY_VALUES_F VALS4
    WHERE inp1.element_type_id = typ.element_type_id
      AND INP2.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
      AND INP3.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADDED BY ENGY
      AND INP4.ELEMENT_TYPE_ID = TYP.ELEMENT_TYPE_ID --ADded by engy
      AND ents.element_type_id = typ.element_type_id
      AND vals1.element_entry_id = ents.element_entry_id --added by engy
      AND vals2.element_entry_id = ents.element_entry_id --added by engy
      AND vals3.element_entry_id = ents.element_entry_id --added by engy
      AND vals4.element_entry_id = ents.element_entry_id --added by engy
      AND ppl.person_id = asg.person_id
      AND ents.assignment_id = asg.assignment_id
      AND ASG.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND vals1.input_value_id =inp1.input_value_id
      and vals2.input_value_id=inp2.input_value_id --added by engy
      and vals3.input_value_id=inp3.input_value_id  --added by engy
      and vals4.input_value_id=inp4.input_value_id  --added by engy
      AND ents.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
      AND ents.effective_start_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
      AND ents.effective_start_date BETWEEN typ.effective_start_date AND typ.effective_end_date
      AND ents.effective_start_date BETWEEN inp1.effective_start_date AND inp1.effective_end_date
      AND ents.effective_start_date between ents.effective_start_date and ents.effective_end_date
      and ents.effective_start_date between vals1.effective_start_date and vals1.effective_end_date--added by engy
      and ents.effective_start_date between vals2.effective_start_date and vals2.effective_end_date--added by engy
      and ents.effective_start_date between vals3.effective_start_date and vals3.effective_end_date --added by engy
      and ents.effective_start_date between vals4.effective_start_date and vals4.effective_end_date --added by engy
      --AND (UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID, P_DATE => FND_DATE.canonical_to_date(VALS1.SCREEN_ENTRY_VALUE)))
      --NOT IN ('ANNUAL LEAVE ADVANCE PAY','ADVANCED PAID SALARY'))
      --OR UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => ASG.ASSIGNMENT_ID,          P_DATE => FND_DATE.canonical_to_date(VALS2.SCREEN_ENTRY_VALUE))) <> 'ANNUAL LEAVE ADVANCE PAY')
      --AND ASG.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14
      --TO GET ALL EMPLOYEES IN GIVEN DATE IF PERSON_ID IS NOT ENTERED IN A GIVEN PERIOD.
      AND PPL.PERSON_ID = NVL(P_PERSON_ID,PPL.PERSON_ID)
      AND typ.element_name = 'Advanced Pay Information'
      AND inp1.name = 'Annual Start Date'
      AND inp2.name = 'Annual End Date'
      AND inp3.name = 'Unpaid End Date'
      AND INP4.NAME = 'Pay Advanced Period'
--      AND FND_DATE.CANONICAL_TO_DATE(VALS1.SCREEN_ENTRY_VALUE) BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
  --    AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
      AND (TRUNC(ENTS.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ENTS.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
      ;
-------------------------- CURSOR OF LEAVE SCREEN ----------------------------

CURSOR GET_ABSENCE IS

SELECT ABA.DATE_START DATE_START, ABA.DATE_END DATE_END,PER.PERSON_ID,
          ABA.ABSENCE_DAYS,PAA.ASSIGNMENT_ID,ATA.NAME,PER.EMPLOYEE_NUMBER,PER.FULL_NAME        
     FROM PER_ABSENCE_ATTENDANCES ABA,
          PER_ALL_PEOPLE_F PER,
          PER_ALL_ASSIGNMENTS_F PAA,
          PER_ABSENCE_ATTENDANCE_TYPES ATA         
    WHERE PER.PERSON_ID = PAA.PERSON_ID
      AND PER.PERSON_ID = ABA.PERSON_ID
      --AND DECODE(P_PERSON_ID,null
      --,( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))
      --,DECODE(EVENT_TYPE,'insert',UPPER( XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START))))
      --AND XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)
      --NOT IN ('Annual Leave','Maternity Leave')
      AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
      AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
      AND UPPER(ATA.NAME) IN ('ANNUAL LEAVE','DELIVERY MATERNITY LEAVE','HAJJ LEAVE')
      --ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
      AND( ABA.ABSENCE_DAYS > XXPY_CONSALIDATIONS.GET_GLOBAL_VALUE(P_BGID => P_BUSINESS_GROUP_ID,
                                                                  P_GLOBAL_NAME => 'MIN_ANN_LEAVE_DAYS',
                                                                  P_DATE => ABA.DATE_START)
      OR PAA.PAYROLL_ID IN (61,62,63,64,81))--afzal 17sep14 adding 'or' condition for govt payrolls                                                                 
      AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
                                                    P_PAYROLL_ID => PAA.PAYROLL_ID,
                                                    P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
                                                    P_ELEMENT_NAME => 'Advanced Pay Information',
                                                    P_INPUT_NAME => 'Annual Start Date',
                                                    P_DATE => ABA.DATE_START) = 'N'                                                 
      AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
      AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
      AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
     --AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR')
--                        AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
     AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
     AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
--     AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')--on 17-6
      --AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA  WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET) AFZAL 29-06-14

UNION ALL

SELECT    ABA.DATE_START DATE_START,
          ABA.DATE_END DATE_END,
          PER.PERSON_ID,
          ABA.ABSENCE_DAYS,
          PAA.ASSIGNMENT_ID,
          ATA.NAME,
          PER.EMPLOYEE_NUMBER,
          PER.FULL_NAME
     FROM PER_ABSENCE_ATTENDANCES ABA,
          PER_ALL_PEOPLE_F PER,
          PER_ALL_ASSIGNMENTS_F PAA,
          PER_ABSENCE_ATTENDANCE_TYPES ATA         
    WHERE PER.PERSON_ID = PAA.PERSON_ID
      AND PER.PERSON_ID = ABA.PERSON_ID
      --AND  XXPY_UPDATE_EMP_ASSIGNMENT.GET_ANNUAL_ASS_STATUS(P_ASSIGNEMNT_ID => PAA.ASSIGNMENT_ID,P_DATE => DATE_START)<> 'Long Unpaid Leave'
      AND ABA.ABSENCE_ATTENDANCE_TYPE_ID = ATA.ABSENCE_ATTENDANCE_TYPE_ID
      AND ABA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
      AND ATA.BUSINESS_GROUP_ID = ABA.BUSINESS_GROUP_ID
      AND UPPER(ATA.NAME) = 'UNPAID LEAVE'
    /*  AND XXPY_ABSENCE_CALC.GET_ABSENCE_ID(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
                                           P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
                                           P_DATE =>FFFUNC.ADD_DAYS(ABA.DATE_START,-1)) = 62*/
      AND XXPY_CONSALIDATIONS.IS_ELEMENT_EXIST(P_BUSINESS_GROUP_ID =>P_BUSINESS_GROUP_ID,
                                                    P_PAYROLL_ID => PAA.PAYROLL_ID,
                                                    P_ASSIGNMENT_ID => PAA.ASSIGNMENT_ID,
                                                    P_ELEMENT_NAME => 'Advanced Pay Information',
                                                    P_INPUT_NAME => 'Unpaid Start Date',
                                                    P_DATE => ABA.DATE_START) = 'N'
      AND ABA.DATE_START BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE
      AND ABA.DATE_START BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
      --AND DATE_START BETWEEN TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-4),'DD-MM-RRRR')      AND TO_DATE(ADD_MONTHS( XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
      --AND DATE_START BETWEEN TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),-3),'DD-MM-RRRR') AND TO_DATE(ADD_MONTHS(XXPY_UPDATE_EMP_ASSIGNMENT.GET_SESSION_DATE(),2),'DD-MM-RRRR')
      AND (TRUNC(ABA.CREATION_DATE) = TRUNC(SYSDATE) OR TRUNC(ABA.LAST_UPDATE_DATE) = TRUNC(SYSDATE))
      AND PER.PERSON_ID = NVL(P_PERSON_ID,PER.PERSON_ID)--AFZAL
      AND ABA.ABSENCE_ATTENDANCE_ID = NVL(P_ABSENCE_ATTENDANCE_ID,ABA.ABSENCE_ATTENDANCE_ID)--AFZAL
      ;
                    
      --AND PAA.ASSIGNMENT_ID IN (SELECT HASA.ASSIGNMENT_ID FROM HR_ASSIGNMENT_SET_AMENDMENTS HASA WHERE HASA.ASSIGNMENT_SET_ID = P_ASSIGNMENT_SET);
------------------------- GET ASSIGNMENT PERIODS -----------------------------

CURSOR C_Asg(P_ASSIGNMENT_ID NUMBER,P_DATE DATE) IS

  /*SELECT ASG.EFFECTIVE_START_DATE
       , ASG.EFFECTIVE_END_DATE
       , ASG.OBJECT_VERSION_NUMBER
  FROM PER_ALL_ASSIGNMENTS_F ASG
  WHERE ASG.ASSIGNMENT_TYPE='E'
  AND   ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
  AND   P_DATE < ASG.EFFECTIVE_END_DATE;
  */
    SELECT MAX(ASG.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER
  FROM PER_ALL_ASSIGNMENTS_F ASG
  WHERE ASG.ASSIGNMENT_TYPE='E'
  AND   ASG.ASSIGNMENT_ID = P_ASSIGNMENT_ID
  AND   P_DATE >= ASG.EFFECTIVE_START_DATE
  AND   P_DATE <= ASG.EFFECTIVE_END_DATE;

---------------------------- PARAMETERS ----------------------------------
V_EFFECTIVE_START_DATE            DATE;
V_EFFECTIVE_END_DATE              DATE;
V_DATE                            DATE;
V_TRACK                           VARCHAR2(100);
V_DATE_U                          DATE;
V_OBJECT_VERSION_NUMBER           PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
ERROR_FLAG                        VARCHAR2(5) := 'N';
ADVANCED_START_DATE               DATE;
UNPAID_START                      DATE;
V_DATE_UNPAID                     DATE;     
--BEGIN
BEGIN
FOR I IN GET_CHANGED_EMP LOOP
         
ADVANCED_START_DATE := FFFUNC.ADD_DAYS(I.X_E_D,1);
  
   --28-02-2015 + 1

------------------------------------------------------------------------------------
--1.
--for Annual Leave Adv Pay and Adv sal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--logic 01 dec 2014 to 04 dec 2014 status as Adv Paid Sal
--      05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
--      26 dec 2014 Suspend
--2.
--for Annual Leave Adv Pay only (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave Adv Pay Only
--      26 dec 2014 Suspend


IF I.ADV_FLAG = 'Y' AND I.DATE_START <> ADVANCED_START_DATE THEN

FOR C IN C_Asg(I.ASSIGNMENT_ID,ADVANCED_START_DATE) LOOP

dt_api.find_dt_upd_modes
   (    p_effective_date                  => ADVANCED_START_DATE,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => I.ASSIGNMENT_ID,--ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert  );
 IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;

BEGIN
      HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>ADVANCED_START_DATE,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 7096,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);                                       
COMMIT;
EXCEPTION WHEN OTHERS THEN
ADVANCED_START_DATE := I.DATE_START;
END;
END LOOP;
END IF;

FOR C IN C_Asg(I.ASSIGNMENT_ID,I.DATE_START) LOOP
dt_api.find_dt_upd_modes
   (    p_effective_date                  => I.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => I.ASSIGNMENT_ID,--ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert  );


   IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;

BEGIN
----------------------------- ADVANCED ANNUAL LEAVE ASSIGNMENT STATUS ----------------------
      HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>I.DATE_START,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 4097,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
------------------------------------ UNPAID ASSIGNMENT STATUS UPDATE -------------------------
EXCEPTION
WHEN OTHERS THEN
    ERROR_FLAG := 'Y';
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || I.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || I.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' ||'Annual Leave' );

 
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');

END;

IF ERROR_FLAG = 'N' THEN
BEGIN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || I.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || I.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' ||'Annual Leave' );


    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END LOOP;

BEGIN
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO   V_OBJECT_VERSION_NUMBER
FROM   PER_ALL_ASSIGNMENTS_F ASG
WHERE 
       ASG.ASSIGNMENT_ID = I.ASSIGNMENT_ID
AND    FFFUNC.ADD_DAYS(I.DATE_END,1) >= ASG.EFFECTIVE_START_DATE
AND    FFFUNC.ADD_DAYS(I.DATE_END,1) <= ASG.EFFECTIVE_END_DATE;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || I.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || I.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' ||'Annual Leave' );
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_NUMBER||'-'||SQLERRM);

 
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;

BEGIN
dt_api.find_dt_upd_modes
   (    p_effective_date                  => FFFUNC.ADD_DAYS(I.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => I.ASSIGNMENT_ID,--ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert  );


   IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;
  
  
     HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(I.DATE_END,1),--TRUNC(V_DATE_U),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',
                                        P_ASSIGNMENT_ID => I.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 2,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || I.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || I.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(I.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' ||'Annual Leave' );

 
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
                  
---------------------------------- ABSENCE SCREEN ASSIGNMENT UPDATE -------------------------------
FOR X IN GET_ABSENCE LOOP
--3.
--for Annual Leave Normal (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave
--      26 dec 2014 Suspend
--4.
--for Hajj Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as Annual Leave
--      26 dec 2014 Suspend
--5.
--for Unpaid Leave (emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as  Long Unpaid Leave
--      26 dec 2014 Suspend
--6.
--for DELIVERY MATERNITY LEAVE(emp 9213 from 05 dec 2014 to 25 dec 2014)
--      05 dec 2014 to 25 dec 2014 status as DELIVERY MATERNITY LEAVE
--      26 dec 2014 Suspend
FOR C IN C_Asg(X.ASSIGNMENT_ID,X.DATE_START) LOOP

dt_api.find_dt_upd_modes
   (    p_effective_date                  => X.DATE_START,--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => X.ASSIGNMENT_ID,--ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert  );

 IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;
 
BEGIN
IF UPPER(X.NAME) in ('ANNUAL LEAVE' ,'HAJJ LEAVE') THEN
--ADDED HAJJ LEAVE ON 17NOV14 BY AFZAL
      HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 4096,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;

ELSIF UPPER(X.NAME) = 'DELIVERY MATERNITY LEAVE'THEN
      HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 9096,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;

ELSIF UPPER(X.NAME) = UPPER('UNPAID LEAVE') THEN
      HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>X.DATE_START,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 5096,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
                                                     
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
    ERROR_FLAG := 'Y';
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE ERRORS UPDATE================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || X.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || X.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' || X.NAME );

    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');

--END;
IF ERROR_FLAG = 'N' THEN
BEGIN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===============THESE EMPLOYEES HAVE MODIFIED===============');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || X.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || X.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' || X.NAME );
   
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END IF;
END;
END LOOP;

BEGIN
--SELECT ASG.OBJECT_VERSION_NUMBER
SELECT max(ASG.OBJECT_VERSION_NUMBER)
INTO   V_OBJECT_VERSION_NUMBER
FROM   PER_ALL_ASSIGNMENTS_F ASG
WHERE  ASG.ASSIGNMENT_TYPE='E'
AND    ASG.ASSIGNMENT_ID = X.ASSIGNMENT_ID
AND    FFFUNC.ADD_DAYS(X.DATE_END,1)< ASG.EFFECTIVE_END_DATE;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES_HAVE ERRORS================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || X.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || X.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' || X.NAME );

    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;

BEGIN

dt_api.find_dt_upd_modes
   (    p_effective_date                  => FFFUNC.ADD_DAYS(X.DATE_END,1),--FFFUNC.ADD_DAYS(X.DATE_END,1),--TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value               => X.ASSIGNMENT_ID,--ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert  );


   IF ( lb_update_override = TRUE  )
   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;
   IF (lb_update_change_insert = TRUE)
   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';
   END IF;
  IF ( lb_correction = TRUE )
  THEN lc_dt_ud_mode := 'CORRECTION';
  END IF;
  IF ( lb_update = TRUE )
  THEN lc_dt_ud_mode := 'UPDATE';
   END IF;
  
  
     HR_ASSIGNMENT_API.SUSPEND_EMP_ASG(P_EFFECTIVE_DATE =>FFFUNC.ADD_DAYS(X.DATE_END,1),--X.DATE_START,--TRUNC(V_DATE),
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--'UPDATE',--lc_dt_ud_mode,--lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => X.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 2,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'================THESE EMPLOYEES HAVE_ERRORS================');
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Number    :' || X.EMPLOYEE_NUMBER);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Employee Name      :' || X.FULL_NAME);
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Start Date   :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_START));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave End Date     :' || FND_DATE.CHARDATE_TO_DATE(X.DATE_END));
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Leave Type         :' || X.NAME );
    fnd_file.PUT_LINE(FND_FILE.OUTPUT,'===========================================================');
END;
END LOOP;
END;
END;
--------------------------Procedure ends here-----------------------------------------

/*--------------------------------------------------*/
Another package to activate the Assignment status.
HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG(P_EFFECTIVE_DATE
=>V_RETURN_DATE,--TRUNC(V_DATE), --V_RETURN_DATE modified by afzal 18 SEP 2014
                                        P_DATETRACK_UPDATE_MODE => lc_dt_ud_mode,--V_TRACK,
                                        P_ASSIGNMENT_ID => REC.ASSIGNMENT_ID ,
                                        P_OBJECT_VERSION_NUMBER => C.OBJECT_VERSION_NUMBER,
                                        P_ASSIGNMENT_STATUS_TYPE_ID => 3095,
                                        P_EFFECTIVE_START_DATE => V_EFFECTIVE_START_DATE,
                                        P_EFFECTIVE_END_DATE => V_EFFECTIVE_END_DATE);