- Below are the sequence of steps to create Element and its Retro Components
- API to Create Element Type & Parent Element
- API to Create Element Type for Retro & Child Element
- API to Create Retro Component Usage
- API to Create Element Span Usages
- 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 = 'US';
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 = 'US_ONCE_EACH_PERIOD';
pay_element_types_api.
create_element_type (
p_validate => FALSE,
p_effective_date => TO_DATE ('01-JAN-2020', 'DD-MON-YYYY'),
p_classification_id => l_classification_id,
p_element_name => l_element_name,
p_input_currency_code => 'USD',
p_output_currency_code => 'USD',
p_multiple_entries_allowed_fla => 'N',
p_processing_type => 'N' ,
--N -> Non Recurring
--R -> Recurring
p_business_group_id => 101,
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;
- 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 = 'US';
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE formula_name = 'US_ONCE_EACH_PERIOD';
pay_element_types_api.
create_element_type (
p_validate => FALSE,
p_effective_date => TO_DATE ('01-JAN-2020', 'DD-MON-YYYY'),
p_classification_id => l_classification_id,
p_element_name => l_element_name,
p_input_currency_code => 'USD',
p_output_currency_code => 'USD',
p_multiple_entries_allowed_fla => 'N',
p_processing_type => 'N' ,
--N -> Non Recurring
--R -> Recurring
p_business_group_id => 101,
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;
- 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-2020', '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 => 101,
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;
- 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-2020', '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 => 101,
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;
