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

--------------------------------------------------------------------------------------------------------------------------

Known issues:

check the segments 6 or 7.

sample below script to update 1 element.

--------------------------------------------------------------------------------------------------------------------------

DECLARE

   CURSOR c1

   IS

     

    SELECT pcak_bal.concatenated_segments bal,

             pcak_cost.concatenated_segments cost,

             pcak_cost.segment1 cost_seg1,

             pcak_cost.segment2 cost_seg2,

             pcak_cost.segment3 cost_seg3,

             pcak_cost.segment4 cost_seg4,

             pcak_cost.segment5 cost_seg5,

             pcak_cost.segment6 cost_seg6,

             pcak_cost.segment7 cost_seg7,

             pcak_bal.segment1 bal_seg1,

             pcak_bal.segment2 bal_seg2,

             pcak_bal.segment3 bal_seg3,

             pcak_bal.segment4 bal_seg4,

             pcak_bal.segment5 bal_seg5,

             pcak_bal.segment6 bal_seg6,

             pcak_bal.segment7 bal_seg7,

             pelf.*

        FROM pay_element_links_f pelf,

             pay_cost_allocation_keyflex pcak_cost,

             pay_cost_allocation_keyflex pcak_bal

       WHERE     business_group_id = 81

             AND TRUNC (SYSDATE) BETWEEN effective_start_date

                                     AND effective_end_date

             AND pelf.cost_allocation_keyflex_id =

                    pcak_cost.cost_allocation_keyflex_id

             AND pelf.balancing_keyflex_id =

                    pcak_bal.cost_allocation_keyflex_id

                    AND PELF.PAYROLL_ID = 141

                     AND PELF.ELEMENT_TYPE_ID NOT IN( 161)

                    ;

   --             AND pcak_bal.segment6 = '843000'

   l_datetrack_mode                VARCHAR2 (100) := 'CORRECTION';

   l_bal_segment6                  VARCHAR2 (100) := '000000';

   l_cost_allocation_keyflex_id    NUMBER := NULL;

   l_balancing_keyflex_id          NUMBER := NULL;

   l_cost_concat_segments_out      VARCHAR2 (500) := NULL;

   l_balance_concat_segments_out   VARCHAR2 (500) := NULL;

   l_effective_start_date          DATE := NULL;

   l_effective_end_date            DATE := NULL;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         l_cost_allocation_keyflex_id := NULL;

         l_balancing_keyflex_id := NULL;

         l_cost_concat_segments_out := NULL;

         l_balance_concat_segments_out := NULL;

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         pay_element_link_api.update_element_link (

            p_validate                      => FALSE,

            p_effective_date                => i.effective_start_date,

            p_element_link_id               => i.element_link_id,

            p_datetrack_mode                => l_datetrack_mode,

            p_transfer_to_gl_flag           => 'Y',

            p_costable_type                 => 'C',

            p_cost_segment1                 => i.cost_seg1,

            p_cost_segment2                 => i.cost_seg2,

            p_cost_segment3                 => i.cost_seg3,

            p_cost_segment4                 => i.cost_seg4,

            p_cost_segment5                 => i.cost_seg5,

            p_cost_segment6                 => i.cost_seg6,

--            p_cost_segment7                 => i.cost_seg7,

            p_balance_segment1              => '006',

            p_balance_segment2              => i.bal_seg2,

            p_balance_segment3              => i.bal_seg3,

            p_balance_segment4              => i.bal_seg4,

            p_balance_segment5              => i.bal_seg5,

            p_balance_segment6              => i.bal_seg6,

--            p_balance_segment7              => i.bal_seg7,

            p_object_version_number         => i.object_version_number,

            p_cost_allocation_keyflex_id    => l_cost_allocation_keyflex_id,

            p_balancing_keyflex_id          => l_balancing_keyflex_id,

            p_cost_concat_segments_out      => l_cost_concat_segments_out,

            p_balance_concat_segments_out   => l_balance_concat_segments_out,

            p_effective_start_date          => l_effective_start_date,

            p_effective_end_date            => l_effective_end_date);

         COMMIT;

         DBMS_OUTPUT.put_line (

            i.element_link_id || ' has been Updated  --> ');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               'Failure - ' || i.element_link_id || '-->' || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception ' || SQLERRM);

END;

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;