Note: Follow the sequence
as said below to create Element and its Retro Components
1) API to Create Element Type àParent Element
2) API to Create Element Type for Retro à Child Element
3) API to Create Retro Component Usage
4) API to Create Element Span Usages
1) API to Create Element Type àParent Element
DECLARE
l_classification_id NUMBER := NULL;
l_event_group_id NUMBER := NULL;
l_formula_id NUMBER := NULL;
l_element_name VARCHAR2 (500) := 'Misc Allowance';
l_element_type_id NUMBER := NULL;
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_object_version_number NUMBER
:= NULL;
l_comment_id NUMBER := NULL;
l_processing_priority_warning BOOLEAN
:= NULL;
BEGIN
SELECT classification_id
INTO l_classification_id
FROM pay_element_classifications
WHERE UPPER (classification_name) = 'EARNINGS'
AND legislation_code = 'AE';
SELECT event_group_id
INTO l_event_group_id
FROM pay_event_groups
WHERE UPPER (event_group_name) = 'ENTRY CHANGES';
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE formula_name = 'AE_ONCE_EACH_PERIOD';
pay_element_types_api.
create_element_type (
p_validate => FALSE,
p_effective_date => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
p_classification_id => l_classification_id,
p_element_name => l_element_name,
p_input_currency_code =>
'AED',
p_output_currency_code =>
'AED',
p_multiple_entries_allowed_fla =>
'N',
p_processing_type => 'N' --N -> Non Recurring R
-> Recurring
,
p_business_group_id => 81,
p_legislation_code => NULL,
p_formula_id => l_formula_id,
p_reporting_name => l_element_name,
p_description => l_element_name,
p_recalc_event_group_id =>
l_event_group_id,
p_element_type_id => l_element_type_id,
p_effective_start_date =>
l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number =>
l_object_version_number,
p_comment_id => l_comment_id,
p_processing_priority_warning =>
l_processing_priority_warning);
COMMIT;
DBMS_OUTPUT.
put_line (l_element_type_id || ' has been created Successfully
!!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
2) API to Create Element Type for Retro à Child Element
DECLARE
l_classification_id NUMBER := NULL;
l_event_group_id NUMBER := NULL;
l_formula_id NUMBER := NULL;
l_element_name VARCHAR2 (500) := 'Misc Allowance Retro';
l_element_type_id NUMBER := NULL;
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
l_object_version_number NUMBER
:= NULL;
l_comment_id NUMBER := NULL;
l_processing_priority_warning BOOLEAN
:= NULL;
BEGIN
SELECT classification_id
INTO l_classification_id
FROM pay_element_classifications
WHERE UPPER (classification_name) = 'EARNINGS'
AND legislation_code = 'AE';
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE formula_name = 'AE_ONCE_EACH_PERIOD';
pay_element_types_api.
create_element_type (
p_validate => FALSE,
p_effective_date => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
p_classification_id => l_classification_id,
p_element_name => l_element_name,
p_input_currency_code =>
'AED',
p_output_currency_code =>
'AED',
p_multiple_entries_allowed_fla =>
'N',
p_processing_type => 'N' --N -> Non Recurring R
-> Recurring
,
p_business_group_id => 81,
p_legislation_code => NULL,
p_formula_id => l_formula_id,
p_reporting_name => l_element_name,
p_description => l_element_name,
p_recalc_event_group_id =>
l_event_group_id,
p_element_type_id =>
l_element_type_id,
p_effective_start_date =>
l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number =>
l_object_version_number,
p_comment_id => l_comment_id,
p_processing_priority_warning =>
l_processing_priority_warning);
COMMIT;
DBMS_OUTPUT.
put_line (l_element_type_id || ' has been created Successfully
!!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
3) API to Create Retro Component Usage
DECLARE
l_retro_component_id NUMBER
:= NULL;
l_element_type_id NUMBER
:= NULL;
l_reprocess_type VARCHAR2 (50) := NULL;
l_retro_component_usage_id NUMBER
:= NULL;
l_object_version_number NUMBER
:= NULL;
BEGIN
SELECT retro_component_id
INTO l_retro_component_id
FROM pay_retro_components
WHERE UPPER (short_name) = 'STANDARD';
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f
WHERE UPPER (element_name) = 'MISC ALLOWANCE';
SELECT hl.lookup_code
INTO l_reprocess_type
FROM hr_lookups hl
WHERE hl.lookup_type = 'RETRO_REPROCESS_TYPE'
AND UPPER (hl.meaning) = 'REPROCESS';
PAY_RCU_INS.
ins (p_effective_date => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
p_retro_component_id =>
l_retro_component_id,
p_creator_id => l_element_type_id,
p_creator_type => 'ET',
p_default_component =>
'Y',
p_reprocess_type => l_reprocess_type,
p_business_group_id =>
81,
p_retro_component_usage_id =>
l_retro_component_usage_id,
p_object_version_number =>
l_object_version_number,
p_replace_run_flag =>
'N',
p_use_override_dates =>
'N');
COMMIT;
DBMS_OUTPUT.
put_line (
l_retro_component_usage_id || ' has been created Successfully
!!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
4) API to Create Element Span Usages
DECLARE
l_time_span_id NUMBER := NULL;
l_retro_component_usage_id NUMBER
:= NULL;
l_retro_element_type_id NUMBER
:= NULL;
l_element_span_usage_id NUMBER
:= NULL;
l_object_version_number NUMBER
:= NULL;
BEGIN
SELECT time_span_id
INTO l_time_span_id
FROM pay_time_spans
WHERE CREATOR_ID = 1;
SELECT prcu.retro_component_usage_id
INTO l_retro_component_usage_id
FROM pay_retro_component_usages prcu, pay_element_types_f petf
WHERE petf.element_type_id = prcu.creator_id
AND UPPER (petf.element_name) = 'MISC ALLOWANCE';
SELECT petf.element_type_id
INTO l_retro_element_type_id
FROM pay_element_types_f petf
WHERE UPPER (petf.element_name) = 'MISC ALLOWANCE RETRO';
PAY_ESU_INS.
ins (p_effective_date => TO_DATE ('01-JAN-1951', 'DD-MON-YYYY'),
p_time_span_id =>
l_time_span_id,
p_retro_component_usage_id =>
l_retro_component_usage_id,
p_retro_element_type_id =>
l_retro_element_type_id,
p_business_group_id =>
81,
p_element_span_usage_id =>
l_element_span_usage_id,
p_object_version_number =>
l_object_version_number);
COMMIT;
DBMS_OUTPUT.
put_line (
l_retro_component_usage_id || ' has been created Successfully
!!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
No comments:
Post a Comment