Queries for Costing in Payroll 2

   INSERT INTO fnd_sessions
        VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
      
        COMMIT


SELECT ELEMENT_LINK_ID,EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PAYROLL_ID, ORGANIZATION_ID,COST_ALLOCATION_KEYFLEX_ID, --Costing
ELEMENT_TYPE_ID,ELEMENT_NAME,
CLASSIFICATION_NAME,CLASSIFICATION_COSTABLE_FLAG,
BALANCING_KEYFLEX_ID, --Balancing
COSTABLE_TYPE,TRANSFER_TO_GL_FLAG
 FROM PAY_ELEMENT_LINKS_V
 WHERE
CLASSIFICATION_COSTABLE_FLAG = 'Y'
AND ELEMENT_NAME = 'Basic Salary'
AND PAYROLL_ID = 61

SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....
WHERE COST_ALLOCATION_KEYFLEX_ID = 1116 --from above step Cost_Allocation_keyflex_id


SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....
WHERE COST_ALLOCATION_KEYFLEX_ID = 4797 --from above step Balancing_keyflex_id

 SELECT * FROM FND_FLEX_VALUES_VL FND1--CONTAINS THE SEGMENT VALUES DESCRIPTION, VALUE



SELECT --ELEMENT_LINK_ID,EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PAYROLL_ID, ORGANIZATION_ID,PV.COST_ALLOCATION_KEYFLEX_ID, --Costing
--ELEMENT_TYPE_ID,
ELEMENT_NAME,
(SELECT ELEMENT_NAME FROM PAY_ELEMENT_TYPES_F_TL PAYTL
WHERE PAYTL.ELEMENT_TYPE_ID = PV.ELEMENT_TYPE_ID
AND LANGUAGE = 'AR')ARABIC_ELEMENT,
--CLASSIFICATION_NAME,--CLASSIFICATION_COSTABLE_FLAG,
PCOST.SEGMENT3 COST_ACCOUNT, PCOST.SEGMENT4 COST_SUBACCOUNT
,PBAL.SEGMENT3 BAL_ACCOUNT, PBAL.SEGMENT4 BAL_SUBACCOUNT
--BALANCING_KEYFLEX_ID, --Balancing
--COSTABLE_TYPE,TRANSFER_TO_GL_FLAG
 FROM PAY_ELEMENT_LINKS_V PV,
 PAY_COST_ALLOCATION_KEYFLEX PCOST,
 PAY_COST_ALLOCATION_KEYFLEX PBAL
 WHERE
CLASSIFICATION_COSTABLE_FLAG = 'Y'
--AND ELEMENT_NAME = 'Basic Salary'
AND PAYROLL_ID = 61
AND PV.COST_ALLOCATION_KEYFLEX_ID = PCOST.COST_ALLOCATION_KEYFLEX_ID
AND PV.BALANCING_KEYFLEX_ID = PBAL.COST_ALLOCATION_KEYFLEX_ID


SELECT HRV.NAME, HRV.ORGANIZATION_ID,
PCA.SEGMENT1 SECTOR,
PCA.SEGMENT2 DEPARTMENT,
PCA.SEGMENT5 PROGRAM
FROM
HR_ORGANIZATION_UNITS_V HRV,
PAY_COST_ALLOCATION_KEYFLEX PCA
WHERE
PCA.COST_ALLOCATION_KEYFLEX_ID = HRV.COST_ALLOCATION_KEYFLEX_ID
AND PCA.SEGMENT1 = '022000'--'022002'
--AND ORGANIZATION_ID = 108
ORDER BY 2

SELECT PAYROLL_ID, PAYROLL_NAME, PCA.SEGMENT1 COST_SECTOR, PCA.SEGMENT2 COST_DEPARTMENT,
PSUS.SEGMENT1 SUSPENSE_SECTOR,
PSUS.SEGMENT2 SUSPENSE_DEPT,
PSUS.SEGMENT3 SUSPENSE_ACC,
PSUS.SEGMENT4 SUSPENSE_SUB_ACC
FROM PAY_ALL_PAYROLLS_F PAPF,
PAY_COST_ALLOCATION_KEYFLEX PCA
,PAY_COST_ALLOCATION_KEYFLEX PSUS
WHERE
PCA.COST_ALLOCATION_KEYFLEX_ID = PAPF.COST_ALLOCATION_KEYFLEX_ID
AND PAYROLL_ID = 61
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PSUS.COST_ALLOCATION_KEYFLEX_ID = PAPF.SUSPENSE_ACCOUNT_KEYFLEX_ID



Query to get the Organization Information Attached as 'HR Organization'

SELECT HRV.ORGANIZATION_ID, HRV.NAME FROM
HR_ORGANIZATION_UNITS_V HRV
,HR_ORGANIZATION_INFORMATION_V HINFV
WHERE HRV.COST_ALLOCATION_KEYFLEX_ID IS NULL
AND HRV.DATE_TO IS NULL
AND HRV.ORGANIZATION_ID= HINFV.ORGANIZATION_ID
AND HINFV.ORG_INFORMATION1 = 'HR_ORG'


SELECT * FROM
HR_ORGANIZATION_INFORMATION_V
WHERE ORGANIZATION_ID = 108

SELECT HRV.ORGANIZATION_ID, HRV.NAME FROM
HR_ORGANIZATION_UNITS_V HRV
,HR_ORGANIZATION_INFORMATION_V HINFV
WHERE HRV.COST_ALLOCATION_KEYFLEX_ID IS NULL
AND HRV.DATE_TO IS NULL
AND HRV.ORGANIZATION_ID= HINFV.ORGANIZATION_ID
AND HINFV.ORG_INFORMATION1 = 'HR_ORG'
AND HRV.ORGANIZATION_ID IN
(SELECT DISTINCT PAAF.ORGANIZATION_ID,PERSON_ID FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.PAYROLL_iD = 61
AND ORGANIZATION_ID = 1707
)

Query to get Contact / Relationship Information in Oracle HRMS R12

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
--AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
--AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
and trunc(sysdate) between papf.EFFECTIVE_START_DATE and papf.effective_end_date
and trunc(sysdate) between papf_cont.EFFECTIVE_START_DATE and papf_cont.effective_end_date
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
and papf.employee_number = '9213'

Requirement: to hide delete icon in the Salary screen(OAF) of HRMS using SPEL in Oracle Apps R12

Requirement: to hide delete icon in the Salary screen(OAF) of HRMS.

1. Creation Function ‘XX_HIRE_DATE_REAF_ONLY’ –Dummy function .
2. Attach function to new menu ‘FUJ_HIDE_YN_SECURITY’ as type ‘Permission Set’.
3. Create role and attach this function.
4. Personalize the OAF page.

Step1:

SSWA jsp function

OA.jsp?page=/dummy

Step 2:

Menu: FUJ_HIDE_YN_SECURITY

Function: XX_HIRE_DATE_REAF_ONLY (created in step1)

Step3:

Functional Administrator Resp> Create Grant>

I Want to allow all users from the UAE HRMS Responsibility.

Next>

Attach the Security Menu we created in step 2.

Next >Finish>

Step4:

Personalization>open salary screen from Assignment>

Click Correct in the Salary oaf page.

Click Personalize “Salary changes”

Click the ‘edit’ icon .

${oa.FunctionSecurity.XX_HIRE_DATE_REAF_ONLY}

Test with UAE HRMS Manager the delete option should show, and for other Responsibilties it should be hidden.

Queries Oracle R12 SSHR to check the status of transaction

 1. To get all the leaves applied by employee with item_keys.
 the same can be used for Valueset parameters.

 SELECT
          pat.NAME leave_name,
          NVL (information1, information3) date_start,
          NVL (information2, information4) date_end,
          information8 DURATION
          ,hrat.item_key, hrat.item_type
   FROM   pqh_ss_transaction_history hrat, pqh_ss_step_history hrst,PER_ABS_ATTENDANCE_TYPES_TL pat
    WHERE hrat.transaction_history_id = hrst.transaction_history_id
    and pat.ABSENCE_ATTENDANCE_TYPE_ID = information5
    AND LANGUAGE = USERENV('LANG')
    and selected_person_id = 26876 --pass the person_id here to get all the transactions for the employee
    order by NVL (information1, information3) desc
   
2. Details of Employee and Leave.

SELECT --hrat.transaction_history_id,
          employee_number, full_name,
          haou.name org_name,
          pat.NAME leave_name,
          NVL (information1, information3) date_start,
          NVL (information2, information4) date_end, --information5 leave_type,
          information8 DURATION, selected_person_id, hrat.item_key
     --FROM hr_api_transactions hrat, hr_api_transaction_steps hrats
   FROM   pqh_ss_transaction_history hrat
   ,pqh_ss_step_history hrst
   ,PER_ABS_ATTENDANCE_TYPES_TL pat
   ,per_all_people_f papf
   ,per_all_assignments_f paaf
   ,hr_all_organization_units haou
    WHERE hrat.transaction_history_id = hrst.transaction_history_id
    and hrat.item_key = '14791'--from step 1
    and hrat.ITEM_TYPE = :p_item_type and hrat.ITEM_KEY = :p_item_key
    and pat.ABSENCE_ATTENDANCE_TYPE_ID = information5
    AND LANGUAGE = USERENV('LANG')
    and papf.person_id = paaf.person_id
    and papf.person_id = selected_person_id
    and fnd_conc_date.string_to_date(NVL (information1, information3)) between papf.effective_start_date and papf.effective_end_date
    and fnd_conc_date.string_to_date(NVL (information1, information3)) between paaf.effective_start_date and paaf.effective_end_date
    and primary_flag = 'Y'
    and haou.organization_id = paaf.organization_id
   
   

3. Status of workflow
p_item_type, p_item_key from step 1

SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am')  l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
 (nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
 decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
 decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
 na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
 ,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
 AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
 UNION
 SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
 ,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
 ,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
 ,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
 ( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
 ,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
 ,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
 ,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
 FROM pqh_ss_approval_history pah ,wf_notifications wn
 WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
 AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
 AND rownum = 1 )
 UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
 -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
 and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER')
 UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
 and C.ACTION in('QUESTION', 'ANSWER')
 UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
 ,appr.text_value note ,begin_date l_date
 FROM wf_items i ,wf_item_attribute_values appr
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
 AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a

  
   

Oracle R12 Cancel/Delete Workflow in SSHR for Pending Approval Transactions

Requirement: to delete the transaction if leave applied by mistake or transaction hanging with no approvers.

As per the doc id (Doc ID 780290.1)

0. Go to Workflow Administrator > Status Monitor> search for the employee transactions> Select the transaction>
Click Workflow History button>
Click the Cancel Workflow Button
Copy the item key in the confirmation window.

1. Run 'Purge Obsolete Workflow Runtime Data' with appropriate age parameters such that the workflow item gets deleted.

Parameters:
Item Type : HR
Item Key : <from step 0>


2. Go to status monitor and make sure the item is deleted (select 'Any Status' and search)


3. Run "Complete Defunct HR Workflow Processes" with appropriate age parameters

Parameters:
Item Type : HR

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);