[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
19-Jan-2009 avenkatk 115.8 5889919 Added Procedure - set_retro_status_rule. This procedure inserts/updates legislation rule for
updating assignment status using View-> Retropay Status page
09-Apr-2009 dduvvuri 115.9 8416815 Added two more parameters p_cost_allocation_keyflex_id and
p_balancing_keyflex_id in call pay_element_link_api.create_element_link
in procedure create_element
20-APR-2009 skshin 115.10 7665727 Modifed qualify_element and upgrade_element procedure to upgrade Earnings Spread classification.
Modifed create_element, create_element and create_ff_results procedures accordingly
Added component usage for HECS Deduction, SFSS Deduction, HECS Spread Deduction and SFSS Spread Deduction in create_enhanced_retro_defn procedure
21-MAY-2009 skshin 115.11 8406009 Added component usage for Spread Deduction in create_enhanced_retro_defn procedure
29-Sep-2009 avenkatk 115.12 8765082 Modified qualify_element and upgrade_element for Earnings Leave Loading classification.
10-Mar-2010 pmatamsr 115.13 9299082 Added Procedure - enable_au_retro_overlap
25-Mar-2010 pmatamsr 115.14 9299082 Added comments for the new procedure - enable_au_retro_overlap.
18-Jan-2011 skshin 115.16 10388533 Modifed create_enhanced_retro_defn prodcedures to add seeded retro event group.
25-May-2011 jmarupil 115.17 12586038 Modified create_enhanced_retro_defn prodcedure to add seeded retro event group for Rec Statutory PPL Payment.
07-Jun-2011 dduvvuri 115.19 12570937 Added code for creating retro component to Flood Levy Deduction element
02-Feb-2012 skshin 115.20 13362286 Added to create retro elements, links and formula results for Earnings Additional
24-Sep-2012 skshin 115.22 14273118 Modfied to create formula results for retro Leave Loading LT12 Curr
*/
gv_package_name VARCHAR2(100);
PROCEDURE insert_event_group(p_business_group_id IN NUMBER
,p_element_type_id IN NUMBER
,p_event_group_id IN NUMBER)
IS
/* Cursor fetches the information of retro element */
/* Bug 5749509 - Modified cursor for Time_definition_type.
'N' is not a valid value for Time Definition Type, set value as Null
*/
cursor c_get_retro_element_info
is
select
ELEMENT_TYPE_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
FORMULA_ID,
INPUT_CURRENCY_CODE,
OUTPUT_CURRENCY_CODE,
CLASSIFICATION_ID,
BENEFIT_CLASSIFICATION_ID,
ADDITIONAL_ENTRY_ALLOWED_FLAG,
ADJUSTMENT_ONLY_FLAG,
CLOSED_FOR_ENTRY_FLAG,
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
INDIRECT_ONLY_FLAG,
MULTIPLE_ENTRIES_ALLOWED_FLAG,
MULTIPLY_VALUE_FLAG,
POST_TERMINATION_RULE,
PROCESS_IN_RUN_FLAG,
PROCESSING_PRIORITY,
PROCESSING_TYPE,
STANDARD_LINK_FLAG,
COMMENT_ID,
LEGISLATION_SUBGROUP,
QUALIFYING_AGE,
QUALIFYING_LENGTH_OF_SERVICE,
QUALIFYING_UNITS,
ELEMENT_INFORMATION_CATEGORY,
ELEMENT_INFORMATION1,
ELEMENT_INFORMATION2,
ELEMENT_INFORMATION3,
THIRD_PARTY_PAY_ONLY_FLAG,
ITERATIVE_FLAG,
ITERATIVE_FORMULA_ID,
ITERATIVE_PRIORITY,
CREATOR_TYPE,
RETRO_SUMM_ELE_ID,
GROSSUP_FLAG,
PROCESS_MODE,
ADVANCE_INDICATOR,
ADVANCE_PAYABLE,
ADVANCE_DEDUCTION,
PROCESS_ADVANCE_ENTRY,
PRORATION_GROUP_ID,
PRORATION_FORMULA_ID,
RECALC_EVENT_GROUP_ID,
ONCE_EACH_PERIOD_FLAG,
decode(TIME_DEFINITION_TYPE,'N',NULL,TIME_DEFINITION_TYPE) TIME_DEFINITION_TYPE, /* Bug 5749509*/
TIME_DEFINITION_ID,
OBJECT_VERSION_NUMBER
from pay_element_types_f
where element_type_id = p_element_type_id
and business_group_id = p_business_group_id
ORDER BY effective_start_date;
lv_procedure_name := 'insert_event_group';
PAY_ELEMENT_TYPES_API.UPDATE_ELEMENT_TYPE
(p_effective_date => rec_element_types.EFFECTIVE_START_DATE
,p_datetrack_update_mode => 'CORRECTION'
,p_element_type_id => rec_element_types.ELEMENT_TYPE_ID
,p_object_version_number => l_ovn
,p_recalc_event_group_id => p_event_group_id
,p_formula_id => rec_element_types.FORMULA_ID
,p_benefit_classification_id => rec_element_types.BENEFIT_CLASSIFICATION_ID
,p_additional_entry_allowed_fla => rec_element_types.ADDITIONAL_ENTRY_ALLOWED_FLAG
,p_adjustment_only_flag => rec_element_types.ADJUSTMENT_ONLY_FLAG
,p_closed_for_entry_flag => rec_element_types.CLOSED_FOR_ENTRY_FLAG
,p_element_name => rec_element_types.ELEMENT_NAME
,p_reporting_name => rec_element_types.REPORTING_NAME
,p_description => rec_element_types.DESCRIPTION
,p_indirect_only_flag => rec_element_types.INDIRECT_ONLY_FLAG
,p_multiple_entries_allowed_fla => rec_element_types.MULTIPLE_ENTRIES_ALLOWED_FLAG
,p_multiply_value_flag => rec_element_types.MULTIPLY_VALUE_FLAG
,p_post_termination_rule => rec_element_types.POST_TERMINATION_RULE
,p_process_in_run_flag => rec_element_types.PROCESS_IN_RUN_FLAG
,p_processing_priority => rec_element_types.PROCESSING_PRIORITY
,p_standard_link_flag => rec_element_types.STANDARD_LINK_FLAG
,p_third_party_pay_only_flag => rec_element_types.THIRD_PARTY_PAY_ONLY_FLAG
,p_iterative_flag => rec_element_types.ITERATIVE_FLAG
,p_iterative_formula_id => rec_element_types.ITERATIVE_FORMULA_ID
,p_iterative_priority => rec_element_types.ITERATIVE_PRIORITY
,p_creator_type => rec_element_types.CREATOR_TYPE
,p_retro_summ_ele_id => rec_element_types.RETRO_SUMM_ELE_ID
,p_grossup_flag => rec_element_types.GROSSUP_FLAG
,p_process_mode => rec_element_types.PROCESS_MODE
,p_advance_indicator => rec_element_types.ADVANCE_INDICATOR
,p_advance_payable => rec_element_types.ADVANCE_PAYABLE
,p_advance_deduction => rec_element_types.ADVANCE_DEDUCTION
,p_process_advance_entry => rec_element_types.PROCESS_ADVANCE_ENTRY
,p_proration_group_id => rec_element_types.PRORATION_GROUP_ID
,p_proration_formula_id => rec_element_types.PRORATION_FORMULA_ID
,p_qualifying_age => rec_element_types.QUALIFYING_AGE
,p_qualifying_length_of_service => rec_element_types.QUALIFYING_LENGTH_OF_SERVICE
,p_qualifying_units => rec_element_types.QUALIFYING_UNITS
,p_element_information_category => rec_element_types.ELEMENT_INFORMATION_CATEGORY
,p_element_information1 => rec_element_types.ELEMENT_INFORMATION1
,p_element_information2 => rec_element_types.ELEMENT_INFORMATION2
,p_element_information3 => rec_element_types.ELEMENT_INFORMATION3
,p_once_each_period_flag => nvl(rec_element_types.ONCE_EACH_PERIOD_FLAG,'N')
,p_time_definition_type => rec_element_types.TIME_DEFINITION_TYPE
,p_time_definition_id => rec_element_types.TIME_DEFINITION_ID
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_comment_id => l_comment_id
,p_processing_priority_warning => l_processing_priority_warning
,p_element_name_warning => l_element_name_warning
,p_element_name_change_warning => l_element_name_change_warning
);
hr_utility.trace('Updated Event Group for Element: ' || p_element_type_id);
SELECT event_group_id
FROM pay_event_groups
WHERE business_group_id = p_business_group_id
AND event_group_name = 'AU Enhanced Retro Event Group';
select dated_table_id
from pay_dated_tables
where table_name = c_table_name;
/* Creates Date Tracked event of Type Update on column EFFECTIVE_START_DATE of table PAY_ELEMENT_ENTRIES_F */
pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
,p_event_group_id => l_event_group_id
,p_dated_table_id => l_ele_entry_table_id
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_START_DATE'
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
/* Creates Date Tracked event of Type Update on column EFFECTIVE_END_DATE of table PAY_ELEMENT_ENTRIES_F */
pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
,p_event_group_id => l_event_group_id
,p_dated_table_id => l_ele_entry_table_id
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_END_DATE'
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
,p_update_type => 'E'
,p_column_name => NULL
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
/* Creates Date Tracked event of Type insert on table PAY_ELEMENT_ENTRIES_F */
pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
,p_event_group_id => l_event_group_id
,p_dated_table_id => l_ele_entry_table_id
,p_update_type => 'I'
,p_column_name => NULL
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
/* Creates Date Tracked event of Type delete on table PAY_ELEMENT_ENTRIES_F */
pay_datetracked_events_api.create_datetracked_event(p_effective_date => l_effective_date
,p_event_group_id => l_event_group_id
,p_dated_table_id => l_ele_entry_table_id
,p_update_type => 'D'
,p_column_name => NULL
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
,p_update_type => 'C'
,p_column_name => 'SCREEN_ENTRY_VALUE'
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_datetracked_event_id
,p_object_version_number => l_ovn
);
SELECT ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name = 'AU_RETRO_PROCESSED_COUNT'
AND ff.legislation_code = 'AU';
SELECT ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name = 'AU_RETRO_ADDITIONAL_PROCESSED'
AND ff.legislation_code = 'AU';
SELECT pspr.status_processing_rule_id
FROM pay_status_processing_rules_f pspr
WHERE pspr.business_group_id = p_business_group_id
AND pspr.element_type_id = c_element_type_id;
select count(*)
from pay_status_processing_rules_f pssp,
pay_formula_result_rules_f pfrr
where pssp.element_type_id = c_element_type_id
and pfrr.status_processing_rule_id = pssp.status_processing_rule_id
AND pfrr.result_name = c_result_name
AND pfrr.result_rule_type = c_result_rule_type
AND DECODE(c_result_rule_type, 'M', '999', pfrr.input_value_id) = DECODE(c_result_rule_type, 'M', '999', c_input_value_id);
SELECT DISTINCT pet.element_type_id, piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE pet.element_name = c_element_name
AND pet.legislation_code = 'AU'
AND piv.element_type_id = pet.element_type_id
AND piv.NAME = c_name
AND piv.legislation_code = 'AU';
SELECT name,
input_value_id
FROM pay_input_values_f
WHERE business_group_id = p_business_group_id
AND element_type_id = p_retro_element_id;
SELECT balance_type_id, scale
FROM pay_balance_feeds_f
WHERE input_value_id = c_input_value_id
AND business_group_id = p_business_group_id;
select count(*)
from pay_balance_feeds_f
where balance_type_id = c_balance_type_id
and input_value_id = c_input_value_id;
SELECT input_value_id
FROM pay_input_values_f
WHERE NAME = c_name
AND element_type_id = p_element_type_id
AND business_group_id = p_business_group_id;
select
LOOKUP_TYPE,
BUSINESS_GROUP_ID,
FORMULA_ID,
DISPLAY_SEQUENCE,
GENERATE_DB_ITEMS_FLAG,
HOT_DEFAULT_FLAG,
MANDATORY_FLAG,
NAME,
UOM,
DEFAULT_VALUE,
MAX_VALUE,
MIN_VALUE,
WARNING_OR_ERROR,
VALUE_SET_ID
from pay_input_values_f
where element_type_id = p_retro_element_id
and business_group_id = p_business_group_id;
SELECT piv.input_value_id,effective_start_date,object_version_number
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND piv.NAME = c_input_value_name
AND piv.business_group_id = p_business_group_id;
/* Bug#5899688 updates display sequence of input value 'Pay Value' of GT12, LT12 elements
if sequence is different from retro element's input value */
PAY_INPUT_VALUE_API.UPDATE_INPUT_VALUE
( P_EFFECTIVE_DATE => rec_input_values_user.effective_start_date
,P_DATETRACK_MODE => 'CORRECTION'
,P_INPUT_VALUE_ID => rec_input_values_user.input_value_id
,P_OBJECT_VERSION_NUMBER => lv_ovn_invl
,P_DISPLAY_SEQUENCE => rec_input_values.DISPLAY_SEQUENCE
,P_EFFECTIVE_START_DATE => l_EFFECTIVE_START_DATE_invl
,P_EFFECTIVE_END_DATE => l_EFFECTIVE_END_DATE_invl
,P_DEFAULT_VAL_WARNING => l_DEFAULT_VAL_WARNING_invl
,P_MIN_MAX_WARNING => l_MIN_MAX_WARNING_invl
,P_LINK_INP_VAL_WARNING => l_LINK_INP_VAL_WARNING_invl
,P_PAY_BASIS_WARNING => l_PAY_BASIS_WARNING_invl
,P_FORMULA_WARNING => l_FORMULA_WARNING_invl
,P_ASSIGNMENT_ID_WARNING => l_ASSIGNMENT_ID_WARNING_invl
,P_FORMULA_MESSAGE => l_FORMULA_MESSAGE_invl
);
select
FORMULA_ID,
INPUT_CURRENCY_CODE,
OUTPUT_CURRENCY_CODE,
CLASSIFICATION_ID,
BENEFIT_CLASSIFICATION_ID,
ADDITIONAL_ENTRY_ALLOWED_FLAG,
ADJUSTMENT_ONLY_FLAG,
CLOSED_FOR_ENTRY_FLAG,
ELEMENT_NAME,
REPORTING_NAME,
DESCRIPTION,
INDIRECT_ONLY_FLAG,
MULTIPLE_ENTRIES_ALLOWED_FLAG,
MULTIPLY_VALUE_FLAG,
POST_TERMINATION_RULE,
PROCESS_IN_RUN_FLAG,
PROCESSING_PRIORITY,
PROCESSING_TYPE,
STANDARD_LINK_FLAG,
COMMENT_ID,
LEGISLATION_SUBGROUP,
QUALIFYING_AGE,
QUALIFYING_LENGTH_OF_SERVICE,
QUALIFYING_UNITS,
ELEMENT_INFORMATION_CATEGORY,
ELEMENT_INFORMATION1,
ELEMENT_INFORMATION2,
ELEMENT_INFORMATION3,
THIRD_PARTY_PAY_ONLY_FLAG,
ITERATIVE_FLAG,
ITERATIVE_FORMULA_ID,
ITERATIVE_PRIORITY,
CREATOR_TYPE,
RETRO_SUMM_ELE_ID,
GROSSUP_FLAG,
PROCESS_MODE,
ADVANCE_INDICATOR,
ADVANCE_PAYABLE,
ADVANCE_DEDUCTION,
PROCESS_ADVANCE_ENTRY,
PRORATION_GROUP_ID,
PRORATION_FORMULA_ID,
RECALC_EVENT_GROUP_ID,
ONCE_EACH_PERIOD_FLAG,
decode(TIME_DEFINITION_TYPE,'N',NULL,TIME_DEFINITION_TYPE) TIME_DEFINITION_TYPE,
TIME_DEFINITION_ID
from pay_element_types_f
where element_type_id = p_retro_element_id
and business_group_id = p_business_group_id
ORDER BY effective_start_date desc;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = rec_element_types.ELEMENT_NAME || ' ' || p_retro_type
AND pet.business_group_id = p_business_group_id;
SELECT pel.PAYROLL_ID,
pel.JOB_ID,
pel.POSITION_ID,
pel.PEOPLE_GROUP_ID,
pel.COST_ALLOCATION_KEYFLEX_ID,
pel.ORGANIZATION_ID,
pel.ELEMENT_TYPE_ID,
pel.LOCATION_ID,
pel.GRADE_ID,
pel.BALANCING_KEYFLEX_ID,
pel.BUSINESS_GROUP_ID,
pel.ELEMENT_SET_ID,
pel.PAY_BASIS_ID,
pel.COSTABLE_TYPE,
pel.LINK_TO_ALL_PAYROLLS_FLAG,
pel.MULTIPLY_VALUE_FLAG,
pel.STANDARD_LINK_FLAG,
pel.TRANSFER_TO_GL_FLAG,
pel.COMMENT_ID,
pel.EMPLOYMENT_CATEGORY,
pel.QUALIFYING_AGE,
pel.QUALIFYING_LENGTH_OF_SERVICE,
pel.QUALIFYING_UNITS,
greatest(pel.EFFECTIVE_START_DATE, to_date('2005/07/01','YYYY/MM/DD')) EFFECTIVE_START_DATE /* 5731490 */
,pel.EFFECTIVE_END_DATE
from pay_element_links_f pel
where pel.element_type_id = c_element_type_id
and pel.business_group_id = p_business_group_id
and pel.effective_start_date = (
select max(pel.effective_start_date)
from pay_element_links_f pel1
where pel.element_link_id=pel1.element_link_id
) /* 5731490 */
and ( p_effective_date between pel.effective_start_date and pel.effective_end_date
or pel.effective_start_date > p_effective_date) /* 5731490 */
order by pel.effective_start_date asc;
select rowid, sub_classification_rule_id
into l_rowid, l_sub_classification_rule_id
from pay_sub_classification_rules_f
where element_type_id = p_element_type_id
and business_group_id = p_business_group_id;
select classification_id
into l_classification_id
from pay_element_classifications
where classification_name = p_class_label /* Bug 8765082 */
and legislation_code = 'AU';
pay_sub_class_rules_pkg.DELETE_ROW (l_rowid,l_sub_classification_rule_id,'ZAP',l_effective_start_date,l_effective_end_date);
pay_sub_class_rules_pkg.insert_row (
dummy_rowid,
dummy_id,
l_effective_start_date,
l_effective_end_date,
p_element_type_id,
l_classification_id,
p_business_group_id,
'AU',
null,null,null,null,null);
pay_element_link_api.delete_element_link
(
p_effective_date => rec_element_links.effective_end_date
,p_element_link_id => l_element_link_id
,p_datetrack_delete_mode => 'DELETE'
,p_object_version_number => l_ovn
,p_effective_start_date => l_effective_start_date_li
,p_effective_end_date => l_effective_end_date_li
,p_entries_warning => l_entries_warning_li
);
PROCEDURE insert_retro_comp_usages
(p_business_group_id in number,
p_legislation_code in varchar2,
p_retro_component_id in number,
p_creator_id in number,
p_retro_comp_usage_id out nocopy number)
IS
ln_retro_component_usage_id NUMBER;
SELECT retro_component_usage_id
FROM pay_retro_component_usages
WHERE creator_id = p_creator_id
AND p_business_group_id = business_group_id
AND retro_component_id = p_retro_component_id;
lv_procedure_name := '.insert_retro_comp_usages';
select pay_retro_component_usages_s.nextval
into ln_retro_component_usage_id
from dual;
insert into pay_retro_component_usages
(retro_component_usage_id, retro_component_id, creator_id, creator_type,
default_component, reprocess_type, business_group_id, legislation_code,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, object_version_number)
values
(ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
sysdate, 2, sysdate, 2, 2, 1);
END insert_retro_comp_usages;
PROCEDURE insert_element_span_usages
(p_business_group_id in number,
p_retro_element_type_id in number,
p_legislation_code in varchar2,
p_time_span_id in number,
p_retro_comp_usage_id in number)
IS
lv_procedure_name VARCHAR2(100);
lv_procedure_name := '.insert_element_span_usages';
insert into pay_element_span_usages
(element_span_usage_id, business_group_id, time_span_id,
retro_component_usage_id, retro_element_type_id,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, object_version_number)
values
(pay_element_span_usages_s.nextval, p_business_group_id, p_time_span_id,
p_retro_comp_usage_id, p_retro_element_type_id,
sysdate, 2, sysdate, 2, 2, 1);
END insert_element_span_usages;
SELECT pet2.element_type_id,pet1.element_name,pet2.element_name
FROM pay_element_types_f pet1,
pay_element_types_f pet2
WHERE pet2.element_name in (pet1.element_name || ' '||'GT12' ,
pet1.element_name || ' '||'LT12 Prev',
pet1.element_name || ' '||'LT12 Curr' )
AND pet1.business_group_id = p_business_group_id
and pet1.business_group_id= pet2.business_group_id
and pet1.element_type_id=p_element_type_id;
select 'Y'
from pay_element_type_extra_info
where element_type_id= cp_element_type_id
and information_type='AU_RETRO_UPGRADE_INFO' ;
select distinct
pet.classification_id,
pet.element_name,
pet.legislation_code,
pet.business_group_id,
pec.classification_name,
pet.retro_summ_ele_id, /* Bug 5731490 */
decode(instr(pec.classification_name, 'Earnings'), 0, null,pec2.classification_name) ||
decode(instr(pec.classification_name, 'Deductions'), 0, null, pec.classification_name ) label
from pay_element_types_f pet
,pay_element_classifications pec
,pay_element_classifications pec2
,pay_sub_classification_rules_f pscr
where pet.element_type_id = cp_element_type_id
AND pet.classification_id = pec.classification_id
and pec.legislation_code = 'AU'
and (instr(pec.classification_name, 'Earnings') > 0
or instr(pec.classification_name, 'Pre Tax Deductions') > 0
OR pet.retro_summ_ele_id IS NOT NULL ) /* Bug 5731490 */
and pet.element_type_id = pscr.element_type_id (+)
and pscr.classification_id = pec2.classification_id(+)
and pec2.legislation_code (+)= 'AU'
AND NOT EXISTS
(SELECT '1'
FROM pay_element_type_extra_info etei
WHERE etei.element_type_id = pet.element_type_id
AND etei.information_type = 'AU_RETRO_UPGRADE_INFO')
AND NOT EXISTS
( SELECT '1'
FROM pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_input_values_f pivf
WHERE pbt.balance_type_id = pbf.balance_type_id
AND pbt.balance_name in ('Retro LT 12 Mths Curr Yr Amount',
'Retro LT 12 Mths Prev Yr Amount',
'Lump Sum E Payments',
'Retro Earnings Spread LT 12 Mths Curr Amount', /*Added for bug 7665727*/
'Retro Earnings Spread LT 12 Mths Prev Yr Amount',
'Retro Earnings Spread GT 12 Mths Amount',
'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount', /* Bug 8765082 */
'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount',
'Retro Earnings Leave Loading GT 12 Mths Amount')
AND pbf.input_value_id = pivf.input_value_id
AND pivf.name = 'Pay Value'
AND pivf.element_type_id = pet.element_type_id
) ;
select legislation_code
from per_business_groups
where business_group_id = cp_business_group_id;
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'I'
union all
select pes.element_set_id
from pay_ele_classification_rules pecr,
pay_element_types_f pet,
pay_element_sets pes
where pet.classification_id = pecr.classification_id
and pes.element_set_id = pecr.element_set_id
and (pes.business_group_id = pet.business_group_id
or pet.legislation_code = cp_legislation_code)
and pet.element_type_id = cp_element_type_id
and pecr.classification_id = cp_classification_id
minus
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'E'
;
select 1
from pay_payroll_actions ppa
where ppa.action_type = 'L'
and ppa.element_set_id = cp_element_set_id;
select 'Y'
from pay_legislation_rules
where legislation_code = cp_legislation_code
and rule_type = cp_rule_type;
ltt_rule_type(1) := 'RETRO_DELETE';
INSERT INTO pay_legislation_rules
(legislation_code, rule_type, rule_mode) VALUES
(lv_legislation_code, ltt_rule_type(i), ltt_rule_mode(i));
SELECT pet.business_group_id, pet.legislation_code, pet.classification_id,
nvl(pet.retro_summ_ele_id, pet.element_type_id),
pet.element_name, pec.classification_name
,pet.retro_summ_ele_id
,decode(instr(pec.classification_name, 'Earnings'), 0, null,pec2.classification_name) label
FROM pay_element_types_f pet,
pay_element_classifications pec
,pay_element_classifications pec2
,pay_sub_classification_rules_f pscr
WHERE pet.element_type_id = cp_element_type_id
AND pet.classification_id = pec.classification_id
AND pec.legislation_code = 'AU'
AND pet.element_type_id = pscr.element_type_id (+)
AND pscr.classification_id = pec2.classification_id(+)
AND pec2.legislation_code (+)= 'AU'
ORDER BY pet.effective_start_date DESC;
select legislation_code
from per_business_groups
where business_group_id = cp_business_group_id;
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'I'
union all
select pes.element_set_id
from pay_ele_classification_rules pecr,
pay_element_types_f pet,
pay_element_sets pes
where pet.classification_id = pecr.classification_id
and pes.element_set_id = pecr.element_set_id
and (pes.business_group_id = pet.business_group_id
or pet.legislation_code = cp_legislation_code)
and pet.element_type_id = cp_element_type_id
and pecr.classification_id = cp_classification_id
minus
select petr.element_set_id
from pay_element_type_rules petr
where petr.element_type_id = cp_element_type_id
and petr.include_or_exclude = 'E';
select hoi.organization_id
from hr_organization_information hoi,
hr_organization_information hoi2
where hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_BG'
and hoi.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'Business Group Information'
and hoi2.org_information9 = cp_legislation_code
and exists (select 1 from pay_payroll_actions ppa
where ppa.business_group_id = hoi.organization_id
and ppa.action_type = 'L'
and ppa.element_set_id = cp_element_set_id
);
select retro_component_id, pts.time_span_id, ptd.short_name, ptd2.short_name
from pay_retro_components prc,
pay_time_spans pts,
pay_time_definitions ptd,
pay_time_definitions ptd2
where pts.creator_id = prc.retro_component_id
and prc.legislation_code = 'AU'
and ptd.legislation_code = 'AU'
and ptd.time_definition_id = pts.start_time_def_id
and ptd2.legislation_code = 'AU'
and ptd2.time_definition_id = pts.end_time_def_id;
select count(*)
from pay_retro_component_usages prcu,
pay_retro_components prc
where prc.legislation_code = 'AU'
and prc.retro_component_id = prcu.retro_component_id
AND prcu.creator_id = p_element_type_id
order by prcu.creator_id;
SELECT balance_type_id
FROM pay_balance_types
WHERE legislation_code = 'AU'
AND balance_name = c_name;
select pet2.element_name
from pay_element_types_f pet1,
pay_element_types_f pet2
where pet1.element_type_id = cp_element_type_id
AND nvl(pet1.retro_summ_ele_id, pet1.element_type_id) = pet2.element_type_id;
insert_retro_comp_usages
(p_business_group_id => ln_ele_business_group_id
,p_legislation_code => null
,p_retro_component_id => ln_retro_component_id
,p_creator_id => p_element_type_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_element_span_usages
(p_business_group_id => ln_ele_business_group_id
,p_retro_element_type_id => retro_element_type_id
,p_legislation_code => null
,p_time_span_id => ln_time_span_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_event_group(ln_ele_business_group_id
,p_element_type_id
,ln_event_group_id);
insert_retro_comp_usages
(p_business_group_id => ln_ele_business_group_id
,p_legislation_code => null
,p_retro_component_id => ln_retro_component_id
,p_creator_id => p_element_type_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_element_span_usages
(p_business_group_id => ln_ele_business_group_id
,p_retro_element_type_id => ln_retro_summ_ele_id /* Bug 5731490 - retro element is summary element */
,p_legislation_code => null
,p_time_span_id => ln_time_span_id
,p_retro_comp_usage_id => ln_retro_comp_usage_id);
insert_event_group(ln_ele_business_group_id
,p_element_type_id
,ln_event_group_id);
SELECT rule_mode
FROM pay_legislation_rules
WHERE rule_type = 'ADVANCED_RETRO'
AND legislation_code = 'AU';
SELECT count(*)
FROM pay_legislation_rules
WHERE rule_type = 'ADVANCED_RETRO'
AND legislation_code = 'AU';
/* Insert the legislation rule */
OPEN csr_exists;
INSERT INTO pay_legislation_rules
(rule_type
,rule_mode
,legislation_code)
VALUES
('ADVANCED_RETRO'
,'Y'
,'AU');
UPDATE pay_legislation_rules
SET rule_mode = 'Y'
WHERE rule_type = 'ADVANCED_RETRO'
AND legislation_code = 'AU' ;
SELECT retro_definition_id
FROM pay_retro_definitions
WHERE short_name = p_short_name
AND legislation_code = g_legislation_code;
SELECT pay_retro_definitions_s.nextval
FROM dual;
INSERT INTO pay_retro_definitions
(retro_definition_id
,short_name
,definition_name
,legislation_code)
VALUES
(l_retro_definition_id
,p_short_name
,p_definition_name
,g_legislation_code);
SELECT retro_component_id
FROM pay_retro_components
WHERE short_name = p_short_name
AND legislation_code = g_legislation_code;
SELECT pay_retro_components_s.nextval
FROM dual;
INSERT INTO pay_retro_components
(retro_component_id
,short_name
,component_name
,retro_type
,legislation_code
,recalculation_style
,date_override_procedure)
VALUES
(l_retro_component_id
,p_short_name
,p_component_name
,p_retro_type
,g_legislation_code
,p_recalc_style
,p_date_override_proc);
UPDATE pay_retro_components
SET component_name = p_component_name
, retro_type = p_retro_type
, recalculation_style = p_recalc_style
, date_override_procedure = p_date_override_proc
WHERE retro_component_id = l_retro_component_id;
SELECT definition_component_id
FROM pay_retro_defn_components
WHERE retro_definition_id = p_retro_definition_id
AND retro_component_id = p_retro_component_id;
SELECT pay_retro_defn_components_s.nextval
from dual;
INSERT INTO pay_retro_defn_components
(definition_component_id
,retro_definition_id
,retro_component_id
,priority)
VALUES
(l_definition_component_id
,p_retro_definition_id
,p_retro_component_id
,p_priority);
UPDATE pay_retro_defn_components
SET priority = p_priority
WHERE definition_component_id = l_definition_component_id
AND retro_definition_id = p_retro_definition_id
AND retro_component_id = p_retro_component_id;
SELECT time_definition_id
FROM pay_time_definitions
WHERE short_name = p_short_name
AND period_type = p_period_type
AND legislation_code = g_legislation_code;
SELECT pay_time_definitions_s.nextval
from dual;
INSERT INTO pay_time_definitions
(time_definition_id
,short_name
,definition_name
,period_type
,period_unit
,day_adjustment
,dynamic_code
,business_group_id
,legislation_code)
VALUES
(l_time_definition_id
,p_short_name
,p_definition_name
,p_period_type
,p_period_unit
,p_day_adjustment
,p_dynamic_code
,null
,g_legislation_code);
UPDATE pay_time_definitions
SET definition_name = p_definition_name
, period_unit = p_period_unit
, day_adjustment = p_day_adjustment
, dynamic_code = p_dynamic_code
WHERE time_definition_id = l_time_definition_id;
SELECT time_span_id
FROM pay_time_spans
WHERE creator_id = p_creator_id
AND creator_type = p_creator_type
AND start_time_def_id = p_start_time_def_id
AND end_time_def_id = p_end_time_def_id;
select pay_time_spans_s.nextval
from dual;
INSERT INTO pay_time_spans
(time_span_id
,creator_id
,creator_type
,start_time_def_id
,end_time_def_id)
VALUES(l_time_span_id
, p_creator_id
, p_creator_type
, p_start_time_def_id
, p_end_time_def_id);
UPDATE pay_time_spans
SET start_time_def_id = p_start_time_def_id
, end_time_def_id = p_end_time_def_id
WHERE time_span_id = l_time_span_id;
hr_utility.trace('Error: While inserting time spans : ' || sqlerrm);
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_retro_element_name
AND legislation_code = g_legislation_code;
SELECT element_span_usage_id
FROM pay_element_span_usages pesu
WHERE pesu.retro_component_usage_id = p_retro_component_usage_id
AND pesu.time_span_id = p_time_span_id
AND pesu.adjustment_type IS NULL;
INSERT INTO pay_element_span_usages
(ELEMENT_SPAN_USAGE_ID
,LEGISLATION_CODE
,TIME_SPAN_ID
,RETRO_COMPONENT_USAGE_ID
,RETRO_ELEMENT_TYPE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
SELECT pay_element_span_usages_s.nextval
, g_legislation_code
, p_time_span_id
, p_retro_component_usage_id
, l_element_type_id
, sysdate
, 1
, sysdate
, 1
, -1
, 1
FROM dual;
hr_utility.trace('Inserted the required element');
SELECT c.retro_component_id
, e.element_type_id
FROM pay_retro_components c
, pay_element_types_f e
WHERE c.component_name = p_component_name
AND e.element_name = p_creator_name
AND e.legislation_code = g_legislation_code
AND c.legislation_code = g_legislation_code;
SELECT retro_component_usage_id
FROM pay_retro_component_usages prcu
WHERE prcu.retro_component_id = l_retro_component_id
AND prcu.creator_id = l_element_type_id
AND prcu.creator_type ='ET';
hr_utility.trace('Before inserting data into component usages');
INSERT INTO pay_retro_component_usages
(RETRO_COMPONENT_USAGE_ID
,RETRO_COMPONENT_ID
,CREATOR_ID
,CREATOR_TYPE
,DEFAULT_COMPONENT
,REPROCESS_TYPE
,LEGISLATION_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
SELECT
pay_retro_component_usages_s.nextval
,l_retro_component_id
,l_element_type_id
,'ET'
,p_default_component
,p_reprocess_type
,g_legislation_code
,sysdate
,1
,sysdate
,1
,-1
,1
FROM dual;
SELECT pay_retro_component_usages_s.currval
INTO l_retro_component_usage_id
from dual;
hr_utility.trace('Inserted retro component usage: ' || l_retro_component_usage_id);
hr_utility.trace('Inserted retro component: ' || p_retro_element_name);
SELECT time_definition_id
FROM pay_time_definitions
WHERE short_name = p_short_name
AND legislation_code = 'AU';
SELECT count(*)
FROM pay_legislation_rules
WHERE rule_type = 'RETRO_STATUS_USER_UPD'
AND legislation_code = 'AU';
/* Insert the legislation rule */
OPEN csr_exists;
INSERT INTO pay_legislation_rules
(rule_type
,rule_mode
,legislation_code)
VALUES
('RETRO_STATUS_USER_UPD'
,'Y'
,'AU');
UPDATE pay_legislation_rules
SET rule_mode = 'Y'
WHERE rule_type = 'RETRO_STATUS_USER_UPD'
AND legislation_code = 'AU' ;
Description : This procedure should be used to insert/update the Retro Definitions
and Retro components for using Enhanced Retropay.
*/
PROCEDURE create_enhanced_retro_defn
IS
l_retro_defn_id pay_retro_definitions.retro_definition_id%TYPE;
SELECT event_group_id
FROM pay_event_groups
WHERE business_group_id is null
AND legislation_code = 'AU'
AND event_group_name = 'AU Retro Enhanced Event Group';
SELECT count(recalc_event_group_id)
from pay_element_types_f
WHERE business_group_id is null
AND legislation_code = 'AU'
AND element_name = p_element_name;
Insert the Retropay Status Update Legislation Rule
*/
set_retro_status_rule;
Insert new time definitions and time spans required for Australia
*/
--
-- 1. Retro Payments Greater than 12 Months
---------------------------------------------------------
l_start_time_id := create_time_definitions
(p_short_name => 'START_OF_TIME'
,p_definition_name => 'Start of Time'
,p_period_type => 'START_OF_TIME'
,p_period_unit => '0'
,p_day_adjustment => 'CURRENT'
,p_dynamic_code => null);
/*bug 10388533 - inserting recal event group to Statutory PPL Payment element */
open c_get_event_group_le;
update pay_element_types_f
set recalc_event_group_id = l_event_group_id
where element_name = 'Statutory Paid Parental Leave Payment';
/* bug 12586038 - inserting recal event group to Rec Statutory PPL Payment element */
open c_get_recalc_event_group_id('Rec Statutory PPL Payment');
update pay_element_types_f
set recalc_event_group_id = l_event_group_id
where element_name = 'Rec Statutory PPL Payment';
/* Delete Concurrent programs */
l_del_prog_app_name := 'PAY';
SELECT rownum ROW_NUM,pbg.name BUS_GROUP_NAME
FROM per_business_groups pbg
WHERE pbg.legislation_code = g_legislation_code;
SELECT rule_mode
FROM pay_legislation_rules
WHERE legislation_code = g_legislation_code
AND rule_type = cp_rule_type;
SELECT count(*)
FROM pay_legislation_rules
WHERE rule_type = 'RETRO_OVERLAP'
AND legislation_code = 'AU';
SELECT rownum ROW_NUM,pbg.name BG_NAME
FROM per_business_groups pbg
WHERE pbg.legislation_code = g_legislation_code;
INSERT INTO pay_legislation_rules
( rule_type
,rule_mode
,legislation_code)
VALUES
('RETRO_OVERLAP'
,'N'
,'AU');
UPDATE pay_legislation_rules
SET RULE_MODE = 'N'
WHERE legislation_code = g_legislation_code
AND rule_type = 'RETRO_OVERLAP';