Update Element Link Costing Information

Below api is used to update the Element Link Information in Oracle HRMS R12:

pay_element_link_api.update_element_link

Requirement: To update all Element Link costing information to null (as new setup is in progress).

   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'

SELECT * FROM PAY_COST_ALLOCATION_KEYFLEX PCA --CONTAINS CONCATTED SEGMENTS, SEGMENT1, SEGMENT 2 ....
WHERE COST_ALLOCATION_KEYFLEX_ID = 4742 --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 = 4742 --from above step Balancing_keyflex_id

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

pl/sql block to update the element link:

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_balance_segment1              VARCHAR2 (50)   := NULL;
   p_balance_segment2              VARCHAR2 (50)   := NULL;
 
   CURSOR c1
   IS
     SELECT ELEMENT_LINK_ID,EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PAYROLL_ID, ORGANIZATION_ID,COST_ALLOCATION_KEYFLEX_ID,ELEMENT_TYPE_ID,ELEMENT_NAME,
CLASSIFICATION_NAME,CLASSIFICATION_COSTABLE_FLAG,BALANCING_KEYFLEX_ID,COSTABLE_TYPE,TRANSFER_TO_GL_FLAG
 FROM PAY_ELEMENT_LINKS_V
 WHERE
 --ELEMENT_LINK_ID = 1234  and 
 CLASSIFICATION_COSTABLE_FLAG = 'Y'
;
   v_c1                            c1%ROWTYPE;
BEGIN
   INSERT INTO fnd_sessions
        VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
      FOR i IN C1
      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--v_c1.business_group_id
               AND lnk.element_link_id = i.element_link_id
               AND lnk.effective_end_date =
                                          TO_DATE ('31/12/4712', 'DD/MM/YYYY');
            BEGIN
              
            
-------------
               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                    => 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_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
                  );
            EXCEPTION
               WHEN OTHERS
               THEN
                  v_err := NULL;
                  v_err := (SQLERRM);
                  dbms_output.put_line(v_err);
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_err := NULL;
                  v_err := (SQLERRM);
                  dbms_output.put_line(v_err);
         END;
      END LOOP;
   COMMIT;
END;