API to Update Element Link - Costing

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 = 10549

             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 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              => i.bal_seg1,

            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              => l_bal_segment6,

            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;