create or replace PROCEDURE create_element_link_api

AS
l_element_link_id apps.pay_element_links_f.element_link_id%TYPE;
l_comment_id apps.pay_element_links_f.comment_id%TYPE;
l_object_version_number apps.pay_element_links_f.object_version_number%TYPE
:= 1;
l_effective_start_date apps.pay_element_links_f.effective_start_date%TYPE;
l_effective_end_date apps.pay_element_links_f.effective_end_date%TYPE;
l_element_type_id apps.pay_element_types_f.element_type_id%TYPE;
l_standard_flag VARCHAR2 (3);
l_error_msg VARCHAR2 (2000);
--Costing Variables
l_element_name VARCHAR2 (500);
l_costing_flag VARCHAR2 (10);
l_cost_segment1 VARCHAR2 (20);
l_cost_segment2 VARCHAR2 (20);
l_cost_segment3 VARCHAR2 (20);
l_cost_segment4 VARCHAR2 (20);
l_cost_segment5 VARCHAR2 (20);
l_cost_segment6 VARCHAR2 (20);
l_cost_segment7 VARCHAR2 (20);
l_bal_segment1 VARCHAR2 (20);
l_bal_segment2 VARCHAR2 (20);
l_bal_segment3 VARCHAR2 (20);
l_bal_segment4 VARCHAR2 (20);
l_bal_segment5 VARCHAR2 (20);
l_bal_segment6 VARCHAR2 (20);
l_bal_segment7 VARCHAR2 (20);

CURSOR c_element_links
IS
select rowid,c.* from BTC_CREATE_ELEMENT_LINK_T_5jan c
where nvl(process_flag,'E') = 'E'
--and nvl(standard_flag,'No') = 'No'
and element_name = 'Free Trade Union Subscription Cost'
;
BEGIN
FOR x IN c_element_links
LOOP
l_element_type_id := NULL;
l_standard_flag := NULL;

BEGIN
SELECT pet.element_type_id
INTO l_element_type_id
FROM pay_element_types_f pet
WHERE to_date('01-01-1951','dd-mm-rrrr') BETWEEN
pet.effective_start_date
AND pet.effective_end_date
AND pet.business_group_id = 81
AND pet.element_name = x.element_name;

pay_element_link_api.create_element_link
(p_validate => FALSE,
p_effective_date => to_date('01-01-1951','dd-
mm-rrrr'),
p_element_type_id => l_element_type_id,
 p_business_group_id => 81,
p_costable_type => 'N',--l_costing_flag,
p_payroll_id => x.payroll_name,
p_job_id => NULL,--x.job_id,
p_position_id => NULL,--x.position_id,
p_people_group_id => x.ppg_id,
p_cost_allocation_keyflex_id => NULL, --Costing
p_organization_id => NULL,--x.organization_id,
p_location_id => NULL,--x.location_id,
p_grade_id => NULL,--x.grade_id,
p_balancing_keyflex_id => NULL, --Costing
p_element_set_id => NULL,
--x.element_set_id,
p_pay_basis_id => NULL,--x.pay_basis_id,
p_link_to_all_payrolls_flag => 'N',--
x.link_to_all_payrolls_flag,
p_standard_link_flag => NULL,--
x.standard_link_flag,
--Dont Change ; Should be 'N' ******
p_transfer_to_gl_flag => null,--
x.transfer_to_gl_flag,
--x.transfer_to_gl_flag,
p_comments => NULL, --****
p_employment_category => x.EMPLOYEE_ATEGORY,
p_qualifying_age => NULL,
--x.qualifying_age,
p_qualifying_length_of_service => NULL,
--x.qualifying_length_of_service,
p_qualifying_units => NULL,
--x.qualifying_units,
p_attribute_category => NULL,
--x.attribute_category,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
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_cost_segment8 => NULL,
p_cost_segment9 => NULL,
p_cost_segment10 => NULL,
p_cost_segment11 => NULL,
p_cost_segment12 => NULL,
p_cost_segment13 => NULL,
p_cost_segment14 => NULL,
p_cost_segment15 => NULL,
p_cost_segment16 => NULL,
p_cost_segment17 => NULL,
p_cost_segment18 => NULL,
p_cost_segment19 => NULL,
p_cost_segment20 => NULL,
p_cost_segment21 => NULL,
p_cost_segment22 => NULL,
p_cost_segment23 => NULL,
p_cost_segment24 => NULL,
p_cost_segment25 => NULL,
p_cost_segment26 => NULL,
p_cost_segment27 => NULL,
p_cost_segment28 => NULL,
p_cost_segment29 => NULL,
p_cost_segment30 => 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_balance_segment8 => NULL,
p_balance_segment9 => NULL,
p_balance_segment10 => NULL,
p_balance_segment11 => NULL,
p_balance_segment12 => NULL,
p_balance_segment13 => NULL,
p_balance_segment14 => NULL,
p_balance_segment15 => NULL,
p_balance_segment16 => NULL,
p_balance_segment17 => NULL,
p_balance_segment18 => NULL,
p_balance_segment19 => NULL,
p_balance_segment20 => NULL,
p_balance_segment21 => NULL,
p_balance_segment22 => NULL,
p_balance_segment23 => NULL,
p_balance_segment24 => NULL,
p_balance_segment25 => NULL,
p_balance_segment26 => NULL,
p_balance_segment27 => NULL,
p_balance_segment28 => NULL,
p_balance_segment29 => NULL,
p_balance_segment30 => NULL,
p_cost_concat_segments => NULL,
p_balance_concat_segments => NULL,
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;

UPDATE BTC_CREATE_ELEMENT_LINK_T_5jan
SET process_flag = 'Y',
element_link_id = l_element_link_id
WHERE ROWID = x.ROWID;

COMMIT;
DBMS_OUTPUT.put_line ( 'Element Link Created for Element : '
|| x.element_name
|| ' :with elemtn element link id :'
|| l_element_link_id
);
EXCEPTION
WHEN OTHERS
THEN
l_error_msg := SUBSTR (SQLERRM, 1, 1999);

UPDATE BTC_CREATE_ELEMENT_LINK_T_5jan
SET process_flag = 'E',message = l_error_msg
WHERE ROWID = x.ROWID;

COMMIT;
DBMS_OUTPUT.put_line ( 'Element Link Created for Element : '
|| l_error_msg
);
END;
END LOOP;
end;