Create Element Link in Oracle Apps

 /* Formatted on 2010/12/30 16:10 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE apps.xx_sam_element_link_conversion
IS
/* *****************************************************************************
*******************************************************************************
        Created By Sameer Patil
*******************************************************************************
****************************************************************************** */
   l_status                  VARCHAR2 (50);
   l_error                   VARCHAR2 (250);
   l_element_link_id         NUMBER         := NULL;
   l_comment_id              NUMBER         := NULL;
   l_object_version_number   NUMBER         := NULL;
   l_effective_start_date   date;
   l_effective_end_date      date;
   l_ele_id                  NUMBER;
   l_id                      NUMBER;
   l_bg_id                   NUMBER;
   p_cost_segment1           VARCHAR2 (50)  := '01';
   p_cost_segment10          VARCHAR2 (50)  := '810';
   p_cost_segment20          VARCHAR2 (50)  := '6140';
   p_cost_segment2           VARCHAR2 (50)  := '1400';
   p_cost_segment3           VARCHAR2 (50)  := '000';
   p_balance_segment1        VARCHAR2 (50)  := '01';
   p_balance_segment10       VARCHAR2 (50)  := '810';
   p_balance_segment20       VARCHAR2 (50)  := '2420';
   p_balance_segment2        VARCHAR2 (50)  := '1400';
   p_balance_segment3        VARCHAR2 (50)  := '000';

   CURSOR ele_cur
   IS
      SELECT *
        FROM xx_sam_element_link;
BEGIN
   DBMS_OUTPUT.put_line
            ('##############################################################');
   DBMS_OUTPUT.put_line ('DATA MIGRATION FOR ELEMENT LINK');
   DBMS_OUTPUT.put_line
            ('##############################################################');

   FOR ele_ptr IN ele_cur
   LOOP
      l_status := 'E';
      l_error := NULL;

      BEGIN
         SELECT element_type_id
           INTO l_id
           FROM pay_element_types_f
          WHERE TRIM (UPPER (element_name)) =
                                           TRIM (UPPER (ele_ptr.element_name));
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line (   'No data in Table pls verify'
                                  || SUBSTR (SQLERRM, 1, 250)
                                 );
      END;

      BEGIN
         SELECT business_group_id
           INTO l_bg_id
           FROM per_business_groups
          WHERE UPPER (NAME) = UPPER (ele_ptr.org_name);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line
                              (   'No data in Table pls verify(Organization)'
                               || SUBSTR (SQLERRM, 1, 250)
                              );
      END;

      BEGIN
         SELECT element_type_id
           INTO l_ele_id
           FROM pay_element_types_f
          WHERE UPPER (TRIM (element_name)) =
                                           UPPER (TRIM (ele_ptr.element_name));

         IF l_ele_id IS NOT NULL
         THEN
            l_status := 'U';
         ELSE
            l_status := 'S';
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_status := 'S';
         WHEN OTHERS
         THEN
            l_status := 'E';
            l_error := 'Error at SQL';
      END;

     IF l_status = 'S'
      THEN
         BEGIN
            DBMS_OUTPUT.put_line ('DATA MIGRATION FOR ELEMENT LINK');
            pay_element_link_api.create_element_link
                       (p_validate                          => false,
                        p_effective_date                    => SYSDATE,
                        p_element_type_id                   => l_id,
                        p_business_group_id                 => l_bg_id,
                        p_costable_type                     => 'N',
                        p_organization_id                   => l_bg_id,
                        p_job_id                            => NULL,
                        p_position_id                       => NULL,
                        p_people_group_id                   => NULL,
                        p_cost_allocation_keyflex_id        => NULL,
                        p_link_to_all_payrolls_flag         => 'N',
                        p_standard_link_flag                => 'Y',
                        p_transfer_to_gl_flag               => 'Y',
                        p_comments                          => NULL,
                        -- p_organization_id                   => NULL,
                        p_location_id                       => NULL,
                        p_grade_id                          => NULL,
                        p_balancing_keyflex_id              => NULL,
                        p_element_set_id                    => NULL,
                        p_qualifying_age                    => NULL,
                        p_qualifying_length_of_service      => NULL,
                        p_qualifying_units                  => NULL,
                        p_cost_segment1                     => p_cost_segment1,
                        p_cost_segment10                    => p_cost_segment10,
                        p_cost_segment20                    => p_cost_segment20,
                        p_cost_segment2                     => p_cost_segment2,
                        p_cost_segment3                     => p_cost_segment3,
                        p_balance_segment1                  => p_balance_segment1,
                        p_balance_segment10                 => p_balance_segment10,
                        p_balance_segment20                 => p_balance_segment20,
                        p_balance_segment2                  => p_balance_segment2,
                        p_balance_segment3                  => p_balance_segment3,
                        p_balance_concat_segments           => (   p_balance_segment1
                                                                || '.'
                                                                || p_balance_segment10
                                                                || '.'
                                                                || p_balance_segment20
                                                                || '.'
                                                                || p_balance_segment2
                                                                || '.'
                                                                || p_balance_segment3
                                                               ),
                        p_cost_concat_segments              => (   p_cost_segment1
                                                                || '.'
                                                                || p_cost_segment10
                                                                || '.'
                                                                || p_cost_segment20
                                                                || '.'
                                                                || p_cost_segment2
                                                                || '.'
                                                                || p_cost_segment3
                                                               ),
                        --         p_comments                   => 'Test Migration For Element Link',
                        p_element_link_id                   => l_element_link_id,
                        p_comment_id                        => l_comment_id,
                        p_object_version_number             => l_object_version_number,
                        p_effective_start_date              => l_effective_start_date,
                        p_effective_end_date                => l_effective_end_date
                       );
            COMMIT;
            l_status := 'S';
            l_error := 'Success';
         EXCEPTION
            WHEN OTHERS
            THEN
               l_status := 'E';
               l_error :=
                        'Error Occured at IN api' || SUBSTR (SQLERRM, 1, 200);
         END;
    END IF;

      UPDATE xx_sam_element_link
         SET status = l_status,
             error = l_error
       WHERE element_name = ele_ptr.element_name;

      COMMIT;
   END LOOP;                                                     /*First FOR*/
END xx_sam_element_link_conversion;

Thanks and Regards

Sameer S Patil

"Team Work" More We Less Me.