/* 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;
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.
