[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select min(asg.effective_start_date) asg_start_date
,max(asg.effective_end_date) asg_end_date
from per_assignments_f asg,
per_assignment_status_types past
where asg.assignment_id = p_assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id
and asg.effective_start_date <= p_period_end_date
and nvl(asg.effective_end_date, p_period_end_date) >= p_period_start_date;
select asg.effective_end_date
from per_assignments_f asg,
per_assignment_status_types past
where asg.assignment_id = p_assignment_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and asg.assignment_status_type_id = past.assignment_status_type_id
and asg.effective_start_date =p_date;
select prrv.result_value from pay_run_result_values prrv,pay_run_results prr
where prr.assignment_action_id=p_assignment_action_id
and prr.element_type_id=p_element_type_id
and prr.run_result_id=p_run_result_id
and prrv.run_result_id=prr.run_result_id
and prrv.input_value_id=p_input_value_id;
select fnd_number.canonical_to_number(prrv.result_value) from pay_run_result_values prrv,pay_run_results prr
where prr.assignment_action_id=p_assignment_action_id
and prr.element_type_id=p_element_type_id
and prrv.run_result_id=prr.run_result_id
and prrv.input_value_id=p_input_value_id;
SELECT creator_type
FROM pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='RR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='NR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='PR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and paa.assignment_action_id=pee.source_asg_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='EE'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'NL' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
SELECT iv.input_value_id
from pay_input_values_f iv
where iv.name=lp_input_value_name
and iv.element_type_id=lp_element_type_id;
SELECT rrv.result_value
from pay_run_result_values rrv
where rrv.input_value_id=lp_input_value_id
and rrv.run_result_id=p_run_result_id;
SELECT prrv1.result_value si_type_name
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_types_f pet
,pay_run_results prr
,pay_run_result_values prrv
,pay_run_result_values prrv1
WHERE pbf.balance_type_id = pbt.balance_type_id
AND pbt.balance_type_id = lp_balance_type_id
AND piv.input_value_id = pbf.input_value_id
AND (piv.name ='Pay Value'
OR piv.name ='Days')
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <> (SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null)
AND piv1.element_type_id = pet.element_type_id
AND piv1.name = 'SI Type Name'
AND piv2.element_type_id = pet.element_type_id
AND piv2.name = 'SI Type'
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = lp_assgn_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = piv2.input_value_id
AND prrv.result_value = lp_si_type
AND prrv1.run_result_id = prrv.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND lp_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND lp_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND lp_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND lp_date_earned BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND lp_date_earned BETWEEN piv2.effective_start_date
AND piv2.effective_end_date;
/* Procedure to insert legislation rule via concurrent program*/
PROCEDURE insert_leg_rule(errbuf out nocopy varchar2, retcode out nocopy varchar2,p_retropay_method IN number) is
CURSOR c_leg_rule(p_rule_type varchar2) is
SELECT 'Y' FROM pay_legislation_rules
WHERE legislation_code='NL'
AND rule_type=p_rule_type;
fnd_file.put_line(FND_FILE.LOG,'Entering Procedure pay_nl_general.insert_leg_rule');
insert into pay_legislation_rules(legislation_code,rule_type,rule_mode) values ('NL','RETRO_COMP_DFLT_OVERRIDE','Y');
delete from pay_legislation_rules
where legislation_code='NL'
and rule_type='RETRO_COMP_DFLT_OVERRIDE';
fnd_file.put_line(FND_FILE.LOG,'Leaving Procedure pay_nl_general.insert_leg_rule');
END insert_leg_rule;
SELECT 'Y' FROM pay_legislation_rules
WHERE legislation_code='NL'
AND rule_type=p_rule_type;
SELECT prd.retro_definition_id
FROM pay_retro_definitions prd
WHERE prd.legislation_code = 'NL'
AND prd.definition_name='Standard Retropay';
SELECT prd.retro_definition_id
FROM pay_retro_definitions prd
WHERE prd.legislation_code = 'NL'
AND prd.definition_name='Replacement Retropay';
Select hoi.org_information2
from hr_organization_information hoi
where hoi.org_information_context = 'NL_BG_INFO'
and hoi.organization_id = p_business_group_id;
select GLOBAL_VALUE
from ff_globals_f
where global_name = l_global_name
and LEGISLATION_CODE = 'NL'
and BUSINESS_GROUP_ID IS NULL
and l_date_earned between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
select GLOBAL_VALUE
from ff_globals_f
where global_name = l_global_name
and LEGISLATION_CODE = 'NL'
and BUSINESS_GROUP_ID IS NULL
and l_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
select effective_date,date_earned
from pay_payroll_actions ppa
where ppa.payroll_action_id = l_payroll_action_id;
select legislation_code
from pay_legislation_rules
where rule_type = 'S'
and rule_mode = to_char (p_id_flex_num);
select SEG.application_column_name c_def_text,
replace (ltrim(rtrim(upper(SEG.segment_name))),' ','_') c_db_name,
SEG.created_by c_created_by,
SEG.last_update_login c_last_login
from fnd_id_flex_segments SEG
, fnd_segment_attribute_values VALUE
where SEG.application_id = 800
and SEG.id_flex_code = 'SCL'
and SEG.id_flex_num = p_id_flex_num
and SEG.enabled_flag = 'Y'
and VALUE.application_column_name = SEG.application_column_name
and VALUE.id_flex_code = 'SCL'
and VALUE.id_flex_num = p_id_flex_num
and VALUE.segment_attribute_type = p_attribute_type
and VALUE.attribute_value = 'Y';
l_record_inserted boolean;
hrdyndbi.insert_user_entity (p_route_name,
p_entity_name,
'route for SCL level : '|| p_attribute_type,
'Y',
'KF',
p_id_flex_num,
null, -- null business group id
p_leg_code,
l_created_by,
l_last_login,
l_record_inserted);
IF l_record_inserted THEN
--
-- insert the id flex num for the where clause filler
--
hrdyndbi.insert_parameter_value (p_id_flex_num, 1);
hrdyndbi.insert_database_item (substr(p_entity_name,1,8) || p_leg_code,
c1rec.c_db_name || '_DE' ,
'T', -- data type
'target.' || c1rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name);
hrdyndbi.insert_database_item (substr(p_entity_name,1,8) || p_leg_code,
c1rec.c_db_name || '_DP' ,
'T', -- data type
'target.' || c1rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name);
hrdyndbi.insert_database_item (p_entity_name,
c1rec.c_db_name ,
'T', -- data type
'target.' || c1rec.c_def_text,
'Y', -- null allowed
'database item for : ' || p_entity_name);
hrdyndbi.delete_keyflex_dict (p_id_flex_num,
'SCL',
l_legislation_code,
null);
delete from ff_user_entities
where creator_type = 'KF'
and creator_id <> p_id_flex_num
and user_entity_name like 'SCL%'
and nvl (legislation_code, ' ') = nvl (l_legislation_code, ' ')
and business_group_id is null;
SELECT 'Y'
FROM ff_formulas_f ff
,ff_compiled_info_f ffci
WHERE ff.formula_id = ffci.formula_id
AND ff.effective_start_date = ffci.effective_start_date
AND ff.effective_end_date = ffci.effective_end_date
AND ff.formula_id = p_formula_id
AND ff.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ff.effective_start_date
AND ff.effective_end_date;
SELECT ff.formula_id
FROM ff_formulas_f ff
WHERE ff.formula_name = p_formula_name
AND ff.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ff.effective_start_date
AND ff.effective_end_date;
select element_type_id into l_element_type_id
from pay_element_types_f
where element_name = p_element_name
and p_effective_date between effective_start_date and effective_end_date
and legislation_code='NL';
select input_value_id into l_input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id
and name = p_input_value_name
and p_effective_date between effective_start_date and effective_end_date
and legislation_code='NL';
SELECT pad.add_information13 house_number
,pad.add_information14 house_no_add
,pad.region_1 street_name
,pad.address_line1 address_line1
,pad.address_line2 address_line2
,pad.address_line3 address_line3
,get_postal_code(pad.postal_code) postcode --get the postal code and remove the space
,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
,pad.country country
FROM per_addresses pad
WHERE pad.person_id=p_person_id
AND p_effective_date between date_from AND NVL(date_to,hr_general.end_of_time)
AND ((pad.primary_flag = 'Y' and p_address_type is null)
or (p_address_type is not null and pad.address_type = p_address_type));
SELECT pad.add_information13 house_number
,pad.add_information14 house_no_add
,pad.region_1 street_name
,pad.address_line1 address_line1
,pad.address_line2 address_line2
,pad.address_line3 address_line3
,get_postal_code_new(pad.postal_code) postcode --introduce a space at the 5th position if there isn't one
,hr_general.decode_lookup('HR_NL_CITY',pad.town_or_city) city
,pad.country country
FROM per_addresses pad
WHERE pad.person_id=p_person_id
AND p_effective_date between date_from AND NVL(date_to,hr_general.end_of_time)
AND ((pad.primary_flag = 'Y' and p_address_type is null)
or (p_address_type is not null and pad.address_type = p_address_type));
SELECT
hlc.loc_information14 house_number
,hlc.loc_information15 house_number_add
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.region_1 street_name
,get_postal_code(hlc.postal_code) postcode --get the postal code and remove the space
,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
,hlc.country country
FROM hr_locations hlc
,hr_organization_units hou
WHERE hou.business_group_id = p_bg_id
AND hou.organization_id = p_org_id
AND hlc.location_id = hou.location_id;
SELECT
hlc.loc_information14 house_number
,hlc.loc_information15 house_number_add
,hlc.address_line_1 address_1
,hlc.address_line_2 address_2
,hlc.address_line_3 address_3
,hlc.region_1 street_name
,get_postal_code_new(hlc.postal_code) postcode --introduce a space at the 5th position if there isn't one
,hr_general.decode_lookup('HR_NL_CITY',hlc.town_or_city) city
,hlc.country country
FROM hr_locations hlc
,hr_organization_units hou
WHERE hou.business_group_id = p_bg_id
AND hou.organization_id = p_org_id
AND hlc.location_id = hou.location_id;
SELECT ter.territory_short_name
FROM fnd_territories_vl ter
WHERE ter.territory_code = p_territory_code;
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv
where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
-- and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
and prr.run_result_id=prv.run_result_id
and pet.element_type_id = prr.element_type_id
and pec.classification_id=pet.classification_id
and pec.legislation_code = 'NL'
and pet.element_type_id = piv.element_type_id
and piv.name ='Pay Value'
-- and piv.legislation_code='NL'
and prv.input_value_id = piv.input_value_id
and p_retro_period between piv.effective_start_date and piv.effective_end_date
and pec.classification_name= p_pri_class_name
-- and ppa.payroll_action_id= nvl(p_pact_id,ppa.payroll_action_id)
and paa.assignment_action_id= p_assact_id;
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_element_classifications pec,
pay_sub_classification_rules_f pecs,
pay_input_values_f piv
where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
and prr.run_result_id=prv.run_result_id
and pet.element_type_id = prr.element_type_id
and pec.legislation_code = 'NL'
and pecs.classification_id=pec.classification_id
and pecs.element_type_id=pet.element_type_Id
and pet.element_type_id = piv.element_type_id
and piv.name ='Pay Value'
-- and piv.legislation_code='NL'
and prv.input_value_id = piv.input_value_id
and p_retro_period between piv.effective_start_date and piv.effective_end_date
and pec.classification_name=p_sec_class_name
-- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
and paa.assignment_action_id= p_assact_id;
-- (select max(paa1.assignment_action_id) from
-- pay_assignment_actions paa1
-- where
-- paa1.payroll_action_id=ppa.payroll_action_id
-- and paa1.assignment_id=paa.assignment_id);
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_run_result_values prv1,
pay_action_contexts pac,
pay_input_values_f piv
where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and paa.assignment_action_id = pac.assignment_action_id
and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
and prr.run_result_id=prv.run_result_id
and prv.input_value_id = P_input_value_id
-- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
and prr.run_result_id=prv1.run_result_id
AND prv1.input_value_id = piv.input_value_id
and piv.name = 'SI Type'
and pac.context_value = prv1.result_value
and pac.context_value = p_context
and ppa.effective_date
between nvl(trunc(fnd_date.canonical_to_date(P_END_OF_YEAR),'Y'),ppa.effective_date)
and nvl(fnd_date.canonical_to_date(P_END_OF_YEAR),ppa.effective_date)
and ppa.effective_date between piv.effective_Start_Date and piv.effective_end_date
and paa.assignment_action_id=p_assact_id;
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_input_values_f piv
Where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
and prr.run_result_id=prv.run_result_id
and prr.element_type_id = p_element_type_id
and piv.element_type_id = p_element_type_id
and piv.name ='Pay Value'
and prv.input_value_id = piv.input_value_id
-- and ppa.payroll_action_id=nvl(p_pact_id,ppa.payroll_action_id)
and ppa.effective_date
between nvl(trunc(fnd_date.canonical_to_date(P_END_OF_YEAR),'Y'),ppa.effective_date)
and nvl(fnd_date.canonical_to_date(P_END_OF_YEAR),ppa.effective_date)
and paa.assignment_action_id=p_assact_id;
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv
where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
-- and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and ppa.effective_date = p_effective_date
-- and pay_nl_general.get_retro_period(prr.source_id,ppa.effective_date) = p_retro_period
and prr.run_result_id=prv.run_result_id
and pet.element_type_id = prr.element_type_id
and pet.element_type_id = piv.element_type_id
and piv.name ='Pay Value'
-- and piv. legislation_code='NL'
and prv.input_value_id = piv.input_value_id
and pec.classification_id=pet.classification_id
and pec.legislation_code = 'NL'
and pec.classification_name= p_pri_class_name
and p_effective_date between piv.effective_start_date and piv.effective_end_date
-- and ppa.payroll_action_id= nvl(p_pact_id,ppa.payroll_action_id)
and paa.assignment_action_id=p_assact_id;
SELECT count(*)
FROM pay_payroll_actions ppa
WHERE TRIM(PAY_NL_GENERAL.get_parameter(ppa.legislative_parameters
,'MAGTAPE_REPORT_ID'))= 'NL_PAYFILE'
AND action_type = 'M'
AND action_status = 'C'
AND effective_date = p_effective_date;
SELECT count(DISTINCT paf.assignment_id)
FROM per_all_assignments_f paf
,per_assignment_status_types pas
WHERE paf.assignment_type = 'E'
AND paf.PERSON_ID = p_person_id
AND p_effective_date between effective_start_date and effective_end_date
AND paf.assignment_status_type_id = pas.assignment_status_type_id
AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
select sum(nvl(fnd_number.canonical_to_number(prv.result_value),0)) value
from
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_element_classifications pec,
pay_sub_classification_rules_f pecs,
pay_input_values_f piv
where
paa.payroll_action_id=ppa.payroll_action_id
and paa.SOURCE_ACTION_ID is not null
and paa.action_status='C'
and prr.ASSIGNMENT_ACTION_ID=paa.assignment_action_id
and ppa.effective_date = p_effective_date
and prr.run_result_id=prv.run_result_id
and pet.element_type_id = prr.element_type_id
and pet.element_type_id = piv.element_type_id
and piv.name ='Pay Value'
and prv.input_value_id = piv.input_value_id
and pec.legislation_code = 'NL'
and pecs.classification_id=pec.classification_id
and pecs.element_type_id=pet.element_type_Id
and pec.classification_name= p_sec_class_name
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and paa.assignment_action_id= p_assact_id;
select date_earned
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select max(TPERIOD.period_num) from
pay_payroll_actions PACTION
,per_time_periods TPERIOD
where PACTION.payroll_action_id = c_payroll_action_id
and TPERIOD.payroll_id = PACTION.payroll_id
and to_char(PACTION.date_earned,'YYYY') = to_char(TPERIOD.regular_payment_date,'YYYY');
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = c_assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < c_start_date);
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status in ('C','P')
AND ppa.date_earned NOT BETWEEN c_start_date AND c_end_date
AND ppa.effective_date BETWEEN c_start_date AND c_end_date);
SELECT ptp.start_date
,ptp.end_date
,ppa.action_type
,ppa.action_status
,ppa.date_earned
,ppa.business_group_id
FROM pay_payroll_actions ppa
,per_time_periods ptp
WHERE ppa.payroll_action_id = c_payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT distinct pog2.parent_object_group_id parent_id
,pog2.source_id assignment_id
FROM pay_object_groups pog1
,pay_object_groups pog2
WHERE pog1.source_id = c_assignment_id
AND pog1.source_type = pog2.source_type
AND pog1.source_type = 'PAF'
AND pog1.parent_object_group_id = pog2.parent_object_group_id
AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date);
SELECT org_information7
FROM hr_organization_information
WHERE organization_id = c_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT 'Y'
FROM dual
WHERE exists (SELECT 1
FROM pay_object_groups pog
,pay_element_entries_f peef
,pay_element_types_f petf
WHERE pog.parent_object_group_id = c_parent_id
AND peef.assignment_id = pog.source_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_name = 'Late Hire Indicator'
AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = c_assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < c_start_date);
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status in ('C','P')
AND ppa.date_earned NOT BETWEEN c_start_date AND c_end_date
AND ppa.effective_date BETWEEN c_start_date AND c_end_date);
SELECT ptp.start_date
,ptp.end_date
,ppa.action_type
,ppa.action_status
,ppa.date_earned
,ppa.business_group_id
FROM pay_payroll_actions ppa
,per_time_periods ptp
WHERE ppa.payroll_action_id = c_payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT distinct pog2.parent_object_group_id parent_id
,pog2.source_id assignment_id
FROM pay_object_groups pog1
,pay_object_groups pog2
WHERE pog1.source_id = c_assignment_id
AND pog1.source_type = pog2.source_type
AND pog1.source_type = 'PAF'
AND pog1.parent_object_group_id = pog2.parent_object_group_id
AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date);
SELECT org_information7
FROM hr_organization_information
WHERE organization_id = c_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT 'Y'
FROM dual
WHERE exists (SELECT 1
FROM pay_object_groups pog
,pay_element_entries_f peef
,pay_element_types_f petf
WHERE pog.parent_object_group_id = c_parent_id
AND peef.assignment_id = pog.source_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_name = 'Late Hire Indicator'
AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = paa1.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < TRUNC(c_start_date,'Q'));
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = paa1.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < c_start_date
AND DECODE(trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12)) <>
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)));
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa1
,pay_payroll_actions ppa2
WHERE ppa1.payroll_action_id = c_payroll_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('Q','R')
AND ppa2.action_status in ('C','P')
AND TRUNC(ppa2.date_earned,'Q') <> TRUNC(c_start_date,'Q')
AND TRUNC(ppa2.effective_date,'Q') = TRUNC(c_start_date,'Q'));
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa1
,pay_payroll_actions ppa2
WHERE ppa1.payroll_action_id = c_payroll_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('Q','R')
AND ppa2.action_status in ('C','P')
AND DECODE(trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12)) <>
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12))
AND DECODE(trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12)) =
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)) );
SELECT ptp.start_date
,ptp.end_date
,ppa.date_earned
,business_group_id
FROM pay_payroll_actions ppa
,per_time_periods ptp
WHERE ppa.payroll_action_id = c_payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT '1' FROM DUAL
WHERE EXISTS (SELECT 1
FROM pay_object_groups pog1
,pay_object_groups pog2
WHERE pog1.source_id = c_assignment_id
AND pog1.source_type = pog2.source_type
AND pog1.source_type = 'PAF'
AND pog1.parent_object_group_id = pog2.parent_object_group_id
AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date));
SELECT org_information7
FROM hr_organization_information
WHERE organization_id = c_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT 'Y'
FROM dual
WHERE exists (SELECT 1
FROM pay_element_entries_f peef
,pay_element_types_f petf
WHERE peef.assignment_id = c_ass_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_name = 'Late Hire Indicator'
AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = paa1.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < TRUNC(c_start_date,'Q'));
SELECT 'DP' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa2.assignment_id = paa1.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa.action_type IN ('Q','R')
AND ppa.action_status = 'C'
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.effective_date < c_start_date
AND DECODE(trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa.effective_date,'IW'))-1)/12)) <>
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)));
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa1
,pay_payroll_actions ppa2
WHERE ppa1.payroll_action_id = c_payroll_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('Q','R')
AND ppa2.action_status in ('C','P')
AND TRUNC(ppa2.date_earned,'Q') <> TRUNC(c_start_date,'Q')
AND TRUNC(ppa2.effective_date,'Q') = TRUNC(c_start_date,'Q'));
SELECT 'DE' FROM dual
WHERE EXISTS (SELECT 1
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,pay_payroll_actions ppa1
,pay_payroll_actions ppa2
WHERE ppa1.payroll_action_id = c_payroll_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type IN ('Q','R')
AND ppa2.action_status in ('C','P')
AND DECODE(trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.date_earned,'IW'))-1)/12)) <>
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12))
AND DECODE(trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(ppa2.effective_date,'IW'))-1)/12)) =
DECODE(trunc((to_number(to_char(c_start_date,'IW'))-1)/12),4,3,trunc((to_number(to_char(c_start_date,'IW'))-1)/12)) );
SELECT ptp.start_date
,ptp.end_date
,ppa.date_earned
,business_group_id
FROM pay_payroll_actions ppa
,per_time_periods ptp
WHERE ppa.payroll_action_id = c_payroll_action_id
AND ptp.time_period_id = ppa.time_period_id;
SELECT '1' FROM DUAL
WHERE EXISTS (SELECT 1
FROM pay_object_groups pog1
,pay_object_groups pog2
WHERE pog1.source_id = c_assignment_id
AND pog1.source_type = pog2.source_type
AND pog1.source_type = 'PAF'
AND pog1.parent_object_group_id = pog2.parent_object_group_id
AND (pog1.start_date <= c_end_date AND pog1.end_date >= c_start_date)
AND (pog2.start_date <= c_end_date AND pog2.end_date >= c_start_date));
SELECT org_information7
FROM hr_organization_information
WHERE organization_id = c_business_group_id
AND org_information_context = 'NL_BG_INFO';
SELECT 'Y'
FROM dual
WHERE exists (SELECT 1
FROM pay_element_entries_f peef
,pay_element_types_f petf
WHERE peef.assignment_id = c_ass_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_name = 'Late Hire Indicator'
AND c_eff_date BETWEEN peef.effective_start_date AND peef.effective_end_date
AND c_eff_date BETWEEN petf.effective_start_date AND petf.effective_end_date);
select distinct pra.assignment_id, pra.retro_assignment_id
from
pay_retro_assignments pra,
per_all_assignments_f paaf
where
pra.assignment_id = paaf.assignment_id
and pra.reprocess_date >= fnd_date.canonical_to_date(p_reprocess_date) --from conc parameter
and pra.retro_assignment_action_id IS NULL
and pra.approval_status in ('A', 'P')
and paaf.payroll_id = NVL(p_payroll_id,paaf.payroll_id) --from conc parameter
and pra.reprocess_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.business_group_id = p_bg_id;
select pre.* from
pay_retro_entries pre,
pay_element_entries_f peef,
PAY_RETRO_COMPONENTS prc
where
pre.retro_assignment_id = p_retro_assignment_id
and pre.element_entry_id = peef.element_entry_id
and pre.reprocess_date between peef.effective_start_date and peef.effective_end_date
and prc.retro_component_id = pre.retro_component_id
and prc.component_name = 'Adjustment'
and prc.legislation_code = 'NL'
and EXISTS
( select 1
from pay_element_types_f pet
where
pet.business_group_id IS NULL
AND pet.legislation_code = 'NL'
AND peef.element_type_id = pet.element_type_id
AND (pet.element_name like '%ABP%' OR pet.element_name like '%PGGM%')
AND EXISTS
( select 1 from
pay_retro_component_usages prcu,
PAY_RETRO_COMPONENTS prc1
where
prcu.creator_id = pet.element_type_id
AND prcu.business_group_id IS NULL
AND prcu.legislation_code = prc1.legislation_code
AND prcu.legislation_code = 'NL'
AND prcu.retro_component_id = prc1.retro_component_id
AND
(
(prc1.component_name = 'Adjustment' AND prcu.default_component = 'Y' AND prcu.reprocess_type = 'R' ) --Adjustment
OR
(prc1.component_name = 'Correction' AND prcu.default_component = 'N' AND prcu.reprocess_type = 'S') --Correction
)
)
);
SELECT *
FROM pay_patch_status
WHERE patch_number = 9453856
AND patch_name = 'NL_9453856'
AND phase = 'C'
AND legislation_code = 'NL';
SELECT retro_component_id
FROM
PAY_RETRO_COMPONENTS
WHERE component_name = p_component_name
AND legislation_code = 'NL';
select 1
from
pay_retro_assignments pra,
pay_retro_entries pre,
pay_element_entries_f peef,
pay_element_types_f petf,
pay_retro_components prc
where
pra.RETRO_ASSIGNMENT_ID = pre.RETRO_ASSIGNMENT_ID
and peef.ELEMENT_ENTRY_ID = pre.ELEMENT_ENTRY_ID
and pre.reprocess_date between peef.effective_start_date and peef.effective_end_date
and peef.element_type_id = petf.element_type_id
--and (petf.element_name like '%ABP%' OR petf.element_name like '%PGGM%')
and (petf.element_name = 'ABP Pensions' OR
petf.element_name = 'ABP Pensions Part Time Percentage' OR
petf.element_name = 'ABP Pensions Reporting Part Time Percentage' OR
petf.element_name = 'ABP Pensions General Information' OR
petf.element_name = 'PGGM Pensions General Information' OR
petf.element_name = 'PGGM Pensions Part Time Percentage')
and petf.legislation_code = 'NL' --9453856
and pre.reprocess_date between petf.effective_start_date and petf.effective_end_date
and prc.legislation_code = 'NL'
and prc.RETRO_COMPONENT_ID = pre.RETRO_COMPONENT_ID
and prc.COMPONENT_NAME = 'Adjustment';
select *
from pay_element_types_f pet
where
pet.business_group_id IS NULL
AND pet.legislation_code = 'NL'
AND (pet.element_name like '%ABP%' OR pet.element_name like '%PGGM%')
AND EXISTS
( select 1 from
pay_retro_component_usages prcu,
PAY_RETRO_COMPONENTS prc1
where
prcu.creator_id = pet.element_type_id
AND prcu.business_group_id IS NULL
AND prcu.legislation_code = prc1.legislation_code
AND prcu.legislation_code = 'NL'
AND prcu.retro_component_id = prc1.retro_component_id
AND
(
(prc1.component_name = 'Adjustment' AND prcu.default_component = 'Y' AND prcu.reprocess_type = 'R' ) --Adjustment
OR
(prc1.component_name = 'Correction' AND prcu.default_component = 'N' AND prcu.reprocess_type = 'S') --Correction
)
);
SELECT *
FROM
PAY_RETRO_COMPONENT_USAGES
WHERE
CREATOR_ID = p_creator_id
AND RETRO_COMPONENT_ID = p_retro_component_id
AND LEGISLATION_CODE = 'NL'
AND CREATOR_TYPE = 'ET';
/* UPDATE pay_retro_component_usages
SET DEFAULT_COMPONENT = 'N', REPROCESS_TYPE = 'S'
WHERE
CREATOR_ID = rec_ele.element_type_id
AND RETRO_COMPONENT_ID = l_adj_component_id
AND CREATOR_TYPE = 'ET'
AND LEGISLATION_CODE = 'NL'
AND DEFAULT_COMPONENT = 'Y'
AND REPROCESS_TYPE = 'R'; */
--Adjustment span and usage need to delete.
FOR rec_usage IN C_RET_COMP_USAGE(rec_ele.element_type_id, l_adj_component_id)
LOOP
--
-- Delete from pay_element_span usages
--
DELETE FROM pay_element_span_usages
WHERE legislation_code = 'NL'
AND retro_component_usage_id = rec_usage.retro_component_usage_id;
-- Delete from pay_retro_component_usages
--
DELETE FROM pay_retro_component_usages
WHERE legislation_code = 'NL'
AND retro_component_usage_id = rec_usage.retro_component_usage_id;
UPDATE pay_retro_component_usages
SET DEFAULT_COMPONENT = 'Y'
WHERE
CREATOR_ID = rec_ele.element_type_id
AND RETRO_COMPONENT_ID = l_corr_component_id
AND CREATOR_TYPE = 'ET'
AND LEGISLATION_CODE = 'NL';
UPDATE pay_retro_component_usages
SET DEFAULT_COMPONENT = 'Y', REPROCESS_TYPE = 'R'
WHERE
CREATOR_ID = rec_ele.element_type_id
AND RETRO_COMPONENT_ID = l_corr_component_id
AND CREATOR_TYPE = 'ET'
AND LEGISLATION_CODE = 'NL';
INSERT INTO pay_patch_status
(id,patch_number,patch_name,phase,applied_date,legislation_code)
SELECT pay_patch_status_s.nextval,9453856,'NL_9453856','C',sysdate,'NL' FROM dual;
SELECT prt.shortname
FROM pay_run_types_f prt
,pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.payroll_action_id = c_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND prt.run_type_id = paa.run_type_id
AND paa.assignment_action_id = c_assignment_action_id
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date;