[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT pre_assact.assignment_action_id assignment_action_id
,pre_assact.assignment_id assignment_id
,pre_assact.tax_unit_id establishment_id
FROM per_all_assignments_f asg
,pay_assignment_actions pre_assact
,pay_payroll_actions pre_payact
WHERE asg.person_id between p_stperson and p_endperson
and asg.period_of_service_id is not null
and g_param_effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_id = pre_assact.assignment_id
and pre_assact.source_action_id is null /* not a child */
and pre_assact.action_status = 'C'
and pre_payact.payroll_action_id = pre_assact.payroll_action_id
and pre_payact.action_status = 'C'
and pre_payact.action_type in ('P','U')
and pre_payact.payroll_id = g_param_payroll_id
and pre_payact.effective_date between g_param_start_date
and g_param_effective_date;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
select context_id
from ff_contexts
where context_name = p_name;
select element_type_id
from pay_element_types_f
where element_name = p_element_name
and legislation_code = 'FR';
select balance_dimension_id
from pay_balance_dimensions
where legislation_code = 'FR'
and dimension_name = p_name;
select application_id
from fnd_application
where application_short_name = 'PER';
select balance_category_id
from pay_balance_categories_f
where legislation_code = 'FR'
and category_name = p_name
order by effective_start_date asc;
select classification_id
from pay_element_classifications
where legislation_code = 'FR'
and classification_name = p_name;
SELECT child_pre.assignment_action_id assignment_action_id
,child_pre.assignment_id assignment_id
,child_pre.tax_unit_id establishment_id
,master_arc.chunk_number chunk_number
,master_arc.payroll_action_id payroll_action_id
,pay_assignment_actions_s.nextval new_ass_act_id
FROM pay_assignment_Actions master_arc
,pay_action_interlocks lok
,pay_assignment_Actions child_pre
WHERE master_arc.assignment_action_id = p_assactid
and lok.locking_Action_id = master_arc.assignment_action_id
and lok.locked_action_id = child_pre.source_action_id;
update pay_assignment_actions
set action_status = 'C'
where assignment_action_id = child.new_ass_act_id;
pay_action_information_api.update_action_information (
p_action_information_id => l_ee_info_id
,p_object_version_number => l_ovn
,p_action_information16 => 'Y');
select urssaf_info.org_information1 code,
substr(urssaf.name,1,150) urssaf_name
from hr_all_organization_units_tl urssaf,
hr_organization_information urssaf_info
where urssaf.language = userenv('lang')
and urssaf.organization_id = urssaf_info.organization_id
and urssaf_info.org_information_context = 'FR_URSSAF_CENTR_INFO'
and urssaf_info.organization_id = p_urssaf_id;
select /*+ORDERED USE_NL(est_info comp_info) */
distinct comp.organization_id,
substr(comptl.name,1,150) name,
comp_info.org_information1 siren,
comp.location_id,
comp_info.org_information3 urssaf_org_id,
comp_info.org_information2 naf_code,
substr(addr.address_line_1,1,150) address_line_1
from (select distinct tax_unit_id id
from pay_assignment_actions paa1
where paa1.payroll_action_id = p_payroll_action_id) tax_unit,
hr_organization_information est_info,
hr_all_organization_units comp,
hr_all_organization_units_tl comptl,
hr_organization_information comp_info,
hr_locations_all addr
where addr.location_id(+) = comp.location_id
and comptl.organization_id = comp.organization_id
and comptl.language = userenv('lang')
and comp_info.organization_id(+) = comp.organization_id
and comp_info.org_information_context(+) = 'FR_COMP_INFO'
and est_info.org_information_context = 'FR_ESTAB_INFO'
and est_info.org_information1 = comp.organization_id
and est_info.organization_id = tax_unit.id;
select distinct
est.organization_id organization_id,
substr(esttl.name,1,150) name,
est_info.org_information2 siret_info,
est_info.org_information1 company_org_id,
est.location_id
from hr_all_organization_units est
, hr_organization_information est_info
, hr_all_organization_units_tl esttl
, pay_assignment_Actions paa
where paa.payroll_action_id = p_payroll_action_id
and est.organization_id = paa.tax_unit_id
and est_info.organization_id(+) = est.organization_id
and est_info.org_information_context(+) = 'FR_ESTAB_INFO'
and esttl.organization_id = est.organization_id
and esttl.language = userenv('lang')
and est.business_group_id = p_business_group_id;
select /*+ordered*/
distinct addr.location_id,
substr(addr.address_line_1,1,150) address_line_1,
substr(addr.address_line_2,1,150) address_line_2,
substr(addr.address_line_3,1,150) address_line_3,
substr(addr.region_2,1,150) region_2,
substr(addr.region_3,1,150) region_3,
addr.town_or_city,
addr.postal_code
from pay_action_information pai,
hr_locations_all addr
where addr.location_id = pai.ACTION_INFORMATION2
and pai.action_context_type = 'PA'
and pai.action_information_category in ('FR_SOE_COMPANY_DETAILS',
'FR_SOE_ESTAB_INFORMATION')
and pai.action_context_id = p_pay_act_id;
Delete from pay_action_information
Where action_context_id = p_payroll_Action_id
And action_context_type = 'PA'
And action_information_category in ('FR_SOE_COMPANY_DETAILS'
,'FR_SOE_ESTAB_INFORMATION'
,'FR_SOE_ER_ADDRESSES');
select paa.tax_unit_id, paa.assignment_id, asg.person_id, asg.payroll_id
from pay_assignment_actions paa
,per_assignments asg
where paa.assignment_action_id = p_paa_id
and paa.assignment_id = asg.assignment_id;
SELECT run_payact.effective_date, run_assact.action_sequence,
run_assact.assignment_action_id, proc_type.context_value
FROM pay_action_interlocks arc_interlock
, pay_action_interlocks pre_interlock
, pay_assignment_actions run_assact
, pay_payroll_actions run_payact
, pay_action_contexts proc_type /*will exclude proration actions*/
WHERE arc_interlock.locking_action_id = p_archive_action_id
and pre_interlock.locking_action_id = arc_interlock.locked_action_id
and pre_interlock.locked_action_id = run_assact.assignment_action_id
and proc_type.assignment_id = run_assact.assignment_id
and proc_type.assignment_action_id = run_assact.assignment_action_id
and proc_type.context_id = g_source_text
and run_payact.payroll_action_id = run_assact.payroll_action_id
and run_payact.action_type in ('Q','R')
and run_assact.run_type_id is not null
order by run_payact.effective_date desc
,run_assact.action_sequence desc ;
SELECT 1 /* if the run action is reversed exclude it */
FROM pay_action_interlocks rev_interlock
, pay_assignment_actions rev_assact
, pay_payroll_actions rev_payact
WHERE rev_interlock.locked_action_id = p_run_act_id
AND rev_interlock.locking_action_id = rev_assact.assignment_action_id
AND rev_assact.action_status = 'C'
AND rev_payact.payroll_action_id = rev_assact.payroll_action_id
AND rev_payact.action_type = 'V'
AND rev_payact.action_status = 'C';
select min(asg1.effective_start_date)
from per_all_assignments_f asg1
,per_all_assignments_f asg2
where asg1.assignment_id = p_assignment_id
and asg2.assignment_id = p_assignment_id
and asg1.establishment_id = asg2.establishment_id
and asg2.effective_start_date >= asg1.effective_start_date
and not exists /* no in between row with different estab id */
(select null
from per_all_assignments_f asg
where asg.effective_start_date > asg1.effective_end_Date
and asg.effective_end_date < asg2.effective_start_date
and asg.establishment_id <> asg1.establishment_id
and asg.assignment_id = asg1.assignment_id)
and asg2.effective_start_date =
(select max(effective_Start_Date)
from per_all_assignments_f
where assignment_id = p_assignment_id
and establishment_id = p_establishment_id
and effective_Start_Date <= p_date_earned);
select max(asg4.effective_end_date)
from per_all_assignments_f asg3
,per_all_assignments_f asg4
where asg3.assignment_id = p_assignment_id
and asg4.assignment_id = p_assignment_id
and asg3.establishment_id = asg4.establishment_id
and asg4.effective_start_date >= asg3.effective_start_date
and not exists /* no in between row with different estab id */
(select null
from per_all_assignments_f asg
where asg.effective_start_date < asg4.effective_start_Date
and asg.effective_start_date > asg3.effective_start_date
and asg.establishment_id <> asg4.establishment_id
and asg.assignment_id = asg4.assignment_id)
and asg4.effective_start_date =
(select max(effective_Start_Date)
from per_all_assignments_f
where assignment_id = p_assignment_id
and establishment_id = p_establishment_id
and effective_Start_Date <= p_date_earned);
select default_dd_date
from per_time_periods
where payroll_id = p_payroll_id
and p_latest_date_earned between start_date and end_date;
select m_ppa.overriding_dd_date
from pay_action_interlocks a_lock
,pay_payroll_actions a_ppa
,pay_assignment_Actions a_asg
,pay_assignment_actions m_asg
,pay_assignment_actions p_asg
,pay_payroll_actions m_ppa
,pay_action_interlocks m_lock
where a_lock.locking_Action_id = p_assignment_action_id
and a_lock.locked_action_id = a_asg.assignment_Action_id
and a_asg.payroll_action_id = a_ppa.payroll_action_id
and a_asg.assignment_id = p_assignment_id
and a_ppa.action_type = 'U'
and a_ppa.action_status = 'C'
and a_ppa.payroll_action_id = p_asg.payroll_action_id
and p_asg.assignment_id = p_assignment_id
and p_asg.assignment_Action_id = m_lock.locked_action_id
and m_lock.locking_action_id = m_asg.assignment_action_id
and m_asg.assignment_id = p_assignment_id
and m_asg.payroll_action_id = m_ppa.payroll_action_id
and m_ppa.action_type = 'M'
and m_ppa.action_status = 'C';
select svc.actual_termination_date ATD
,nvl(svc.adjusted_svc_date, svc.date_start)
,hr_general.decode_lookup('LEAV_REAS', svc.leaving_reason)
,svc.pds_information11 final_pay_schedule
,fnd_date.canonical_to_date(svc.pds_information10) LWD
from per_periods_of_service svc
,per_all_assignments_f asg
where asg.period_of_service_id = svc.period_of_service_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = p_assignment_id;
select addr.address_line1, addr.address_line2, addr.address_line3
,addr.region_2, addr.region_3, addr.town_or_city, addr.postal_code
from per_addresses addr
where addr.person_id = p_person_id
and addr.primary_flag = 'Y'
and p_asat_date >= addr.date_from
and p_asat_date <= nvl(addr.date_to, p_asat_date);
Select peo.national_identifier
,peo.last_name
,peo.first_name
,peo.full_name
,job.job_id
,job.job_definition_id
,postl.name
,cag.name
,to_number(estinfo.org_information1)
,asg.payroll_id
,peo.previous_last_name
,asg.assignment_number
,orgtl.name
,ctr.ctr_information10 fixed_working_time
,ctr.ctr_information11 Con_Amount
,ctr.ctr_information12 Con_Units
,hrl.meaning Con_Unit_mean
,ctr.ctr_information13 Con_Frequency
,hrl1.meaning Con_freq_mean
from
per_all_assignments_f asg
,per_all_people_f peo
,per_jobs job
,hr_all_positions_f_tl postl
,per_collective_agreements cag
,hr_all_organization_units est
,hr_organization_information estinfo
,hr_all_organization_units_tl orgtl
,per_contracts_f ctr
,fnd_lookup_values hrl
,fnd_lookup_values hrl1
where asg.assignment_id = p_assignment_id
and p_asat_date between asg.effective_start_date
and asg.effective_end_date
and asg.establishment_id = est.organization_id
and p_asat_date between est.date_from
and nvl(est.date_to, p_asat_date)
and estinfo.organization_id = est.organization_id
and estinfo.org_information_context = 'FR_ESTAB_INFO'
and asg.collective_agreement_id = cag.collective_agreement_id(+)
and asg.person_id = peo.person_id
and asg.position_id = postl.position_id(+)
and postl.language(+) = userenv('LANG')
and asg.job_id = job.job_id(+)
and orgtl.organization_id = asg.organization_id
and orgtl.language = userenv('LANG')
and p_asat_date between peo.effective_start_date
and peo.effective_end_date
and asg.contract_id = ctr.contract_id
and p_asat_date between ctr.effective_start_date
and ctr.effective_end_date
and hrl.lookup_type = 'FR_FIXED_TIME_UNITS'
and hrl.view_application_id = 3
and hrl.security_group_id = g_sec_grp_id_fixed_time_units
and hrl.language = userenv('LANG')
and hrl.lookup_code = ctr.ctr_information12
and hrl1.lookup_type = 'FR_FIXED_TIME_FREQUENCY'
and hrl1.view_application_id = 3
and hrl1.security_group_id = g_sec_grp_id_fixed_time_freq
and hrl1.language = userenv('LANG')
and hrl1.lookup_code = ctr.ctr_information13;
Select gqual.segment_attribute_type qualifier
,seg_tl.Form_left_prompt seg_name
,substr(decode(gqual.application_column_name
,'SEGMENT1', CAGR.segment1
,'SEGMENT2', CAGR.segment2
,'SEGMENT3', CAGR.segment3
,'SEGMENT4', CAGR.segment4
,'SEGMENT5', CAGR.segment5
,'SEGMENT6', CAGR.segment6
,'SEGMENT7', CAGR.segment7
,'SEGMENT8', CAGR.segment8
,'SEGMENT9', CAGR.segment9
,'SEGMENT10', CAGR.segment10
,'SEGMENT11', CAGR.segment11
,'SEGMENT12', CAGR.segment12
,'SEGMENT13', CAGR.segment13
,'SEGMENT14', CAGR.segment14
,'SEGMENT15', CAGR.segment15
,'SEGMENT16', CAGR.segment16
,'SEGMENT17', CAGR.segment17
,'SEGMENT18', CAGR.segment18
,'SEGMENT19', CAGR.segment19
,'SEGMENT20', CAGR.segment20, null),1,60) seg_value
from
per_all_assignments_f asg
,per_cagr_grades_def cagr
,fnd_id_flex_segments seg
,fnd_id_flex_segments_tl seg_tl
,fnd_segment_attribute_values gqual
where asg.assignment_id = p_assignment_id
and asg.cagr_grade_def_id = CAGR.cagr_grade_def_id (+)
and gqual.id_flex_num(+) = CAGR.id_flex_num
and gqual.id_flex_code(+) = 'CAGR'
and gqual.attribute_value(+) = 'Y'
and seg.id_flex_code = 'CAGR'
and seg.id_flex_num = asg.cagr_id_flex_num
and seg.application_id = p_per_id
and gqual.application_id = p_per_id
and seg.application_column_name = gqual.application_column_name
and p_asat_date between asg.effective_start_date and asg.effective_end_date
and (gqual.segment_attribute_type = 'COEFFICIENT'
or
gqual.segment_attribute_type = 'GRADE')
and seg_tl.application_id = seg.application_id
and seg_tl.id_flex_code = 'CAGR'
and seg_tl.id_flex_num = asg.cagr_id_flex_num
and seg_tl.application_column_name = seg.application_column_name
and seg_tl.language = userenv('LANG')
order by seg.segment_num;
select pppmf.external_account_id external_account_id
,ppp.pre_payment_id pre_payment_id
,opmtl.org_payment_method_name payment_method
,ppp.value amount
,count(decode(all_arc_lock.locking_action_id
,this_arc_lock.locking_action_id,null
,all_arc_lock.locking_action_id)) num_previous
from pay_action_interlocks this_arc_lock
,pay_action_interlocks all_arc_lock
,pay_assignment_actions arc_assact
,pay_payroll_actions arc_payact
,pay_pre_payments ppp
,pay_personal_payment_methods_f pppmf
,pay_org_payment_methods_f_tl opmtl
where this_arc_lock.locking_action_id = p_archive_action_id
and all_arc_lock.locked_action_id = this_arc_lock.locked_action_id
and arc_assact.assignment_action_id = all_arc_lock.locking_action_id
and arc_payact.payroll_action_id = arc_assact.payroll_action_id
and arc_payact.action_type = 'X'
and arc_payact.report_qualifier = 'FR'
and arc_payact.report_category = 'SOE_ARCHIVE'
and arc_payact.report_type = 'SOE_ARCHIVE'
and this_arc_lock.locked_action_id = ppp.assignment_action_id
and ppp.value <> 0
and opmtl.org_payment_method_id = ppp.org_payment_method_id
and opmtl.language = userenv('LANG')
and pppmf.personal_payment_method_id(+) = ppp.personal_payment_method_id
and p_asat_date between pppmf.effective_start_date(+)
and pppmf.effective_end_date(+)
group by opmtl.org_payment_method_name, ppp.value, ppp.pre_payment_id,
pppmf.external_account_id
order by opmtl.org_payment_method_name, ppp.value;
select bank.meaning bank_name
,substr(pxa.segment2, 1, 5) bank_code
,substr(pxa.segment3, 1, 5) branch_code
,substr(pxa.segment5, 1, 14) account_number
from pay_external_accounts pxa
,hr_lookups bank
where bank.lookup_type(+) = 'FR_BANK'
and bank.lookup_code(+) = pxa.segment1
and pxa.external_account_id = p_account_id;
select substrb(run_payact.pay_advice_message, 1, 240) message
from pay_payroll_actions pre_payact
,pay_assignment_actions pre_assact
,pay_payroll_actions run_payact
,pay_assignment_actions run_assact
,pay_action_interlocks arc_lock
,pay_action_interlocks pre_lock
where
arc_lock.locking_action_id = p_archive_assignment_action_id
and arc_lock.locked_action_id = pre_assact.assignment_action_id
and pre_lock.locking_action_id = pre_assact.assignment_action_id
and pre_lock.locked_action_id = run_assact.assignment_action_id
and pre_payact.payroll_action_id = pre_assact.payroll_action_id
and run_payact.payroll_action_id = run_assact.payroll_action_id
and run_payact.action_type in ('Q', 'R')
and pre_payact.action_type in ('P', 'U')
and run_payact.pay_advice_message is not null
order by run_payact.action_sequence;
select result_rollup.rubric_code Rubric
,result_rollup.description Description
,result_rollup.classification_name Class
,result_rollup.element_information1 Group_Code
,result_rollup.element_type_id Element
,result_rollup.process_type Process
,sum(base_value) Base
,rate_value Rate
,sum(pay_value) Amount
,base_units_meaning base_units
,week_end_date week_end_date
,factor factor
,label label
,absattid absence_attendance_id
,decode(classification_name, 'Benefits', run_result_id, 0) distinct_ben
,decode(classification_name, 'Earnings Adjustment', -1, 1) adjust_sign
/* get the correct dates for non-retro - input value names or proration dates if they exist */
,nvl(result_rollup.start_date, fnd_date.date_to_canonical(result_rollup.prorate_start_date)) std_start_date
,nvl(result_rollup.end_date, fnd_date.date_to_canonical(result_rollup.prorate_end_date)) std_end_Date
/* get the correct dates for retro - retro proration dates or original period */
,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date)) retro_pro_start
,decode(creator_type,'EE', nvl(result_rollup.retro_pro_end, ptp.end_date) , 'RR', nvl(result_rollup.retro_pro_end, ptp.end_date)) retro_pro_end
,creator_type
from (
select
pee.creator_type creator_type,
max(decode(piv.name,p_fr_start_name,prrv.result_value,
p_us_start_name,prrv.result_value)) start_date,
max(decode(piv.name,p_fr_end_name,prrv.result_value,
p_us_end_name,prrv.result_value)) end_date,
decode(pee.creator_type,
'RR',rr_ret.assignment_action_id,
'EE',pee.source_asg_action_id) retro_act_id,
nvl(user_rubric.tag,seed_rubric.tag) rubric_code,
nvl(user_rubric.meaning,seed_rubric.meaning)
|| decode(pee.creator_type,'EE',' '|| p_retro_tl,
'RR',' '|| p_retro_tl) description,
pec.classification_name,
pet.element_information1,
pet.element_type_id,
max(decode(piv.name,'Process_Type',prrv.result_value)) process_type,
max(decode(piv.name,p_fr_base_name,prrv.result_value,
p_us_base_name,prrv.result_value)) base_value,
max(decode(piv.name,p_fr_rate_name,prrv.result_value,
p_us_rate_name,prrv.result_value)) rate_value,
max(decode(piv.name,p_fr_pay_value_name,prrv.result_value,
p_us_pay_value_name,prrv.result_value)) pay_value,
base_units.meaning base_units_meaning,
max(decode(piv.name,'Week End Date',prrv.result_value)) week_end_date,
max(decode(piv.name,'Overtime Factor',prrv.result_value)) factor,
max(decode(piv.name,'Label',prrv.result_value)) label,
max(decode(piv.name,'Absence Attendance ID',prrv.result_value)) absattid,
prrv.run_result_id,
prr.start_date prorate_start_date,
prr.end_date prorate_end_date,
decode(pee.creator_type,
'EE',pee.source_start_date,
'RR',rr_ret.start_date) retro_pro_start,
decode(pee.creator_type,
'EE',pee.source_end_date,
'RR',rr_ret.end_date) retro_pro_end
from pay_run_result_values prrv
,pay_run_results prr
,pay_element_types_f pet
,pay_element_classifications pec
,pay_input_values_f_tl piv
,fnd_lookup_values user_rubric
,fnd_lookup_values seed_rubric
,fnd_lookup_values base_units
,pay_payroll_actions pre_payact
,pay_assignment_actions pre_assact
,pay_payroll_actions run_payact
,pay_assignment_actions run_assact
,pay_action_interlocks arc_lock
,pay_action_interlocks pre_lock
,pay_element_entries_f pee
,pay_run_results rr_ret
where pee.element_entry_id(+) = prr.source_id
and rr_ret.run_result_id(+) = pee.source_id
and prrv.run_result_id = prr.run_result_id
and prr.element_type_id = pet.element_type_id
and prr.element_type_id = pet.element_type_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.classification_id = pec.classification_id
and pec.classification_name in (p_ee_class1,p_ee_class2
,p_ee_class3,p_ee_class4
,p_ee_class5,p_ee_class6)
and piv.input_value_id = prrv.input_value_id
and piv.language = userenv('lang')
and piv.name in (p_us_pay_value_name, p_fr_pay_value_name
,p_us_base_name, p_fr_base_name
,p_us_rate_name, p_fr_rate_name
,p_us_start_name, p_fr_start_name
,p_us_end_name, p_fr_end_name
,'Overtime Factor','Label'
,'Process_Type','Absence Attendance ID', 'Week End Date')
and prr.assignment_action_id = run_assact.assignment_action_id
and arc_lock.locking_action_id = p_archive_assignment_action_id
and arc_lock.locked_action_id = pre_assact.assignment_action_id
and pre_lock.locking_action_id = pre_assact.assignment_action_id
and pre_lock.locked_action_id = run_assact.assignment_action_id
and pre_payact.payroll_action_id = pre_assact.payroll_action_id
and run_payact.payroll_action_id = run_assact.payroll_action_id
and run_payact.action_type in ('Q', 'R')
and pre_payact.action_type in ('P', 'U')
and user_rubric.lookup_code(+) = pet.element_information1
and user_rubric.lookup_type(+) = 'FR_USER_ELEMENT_GROUP'
and user_rubric.LANGUAGE(+) = USERENV('LANG')
and user_rubric.security_group_id(+) = g_sec_grp_id_user_element_grp
and user_rubric.VIEW_APPLICATION_ID(+) = 3
and seed_rubric.lookup_code(+) = pet.element_information1
and seed_rubric.lookup_type(+) = 'FR_ELEMENT_GROUP'
and seed_rubric.LANGUAGE(+) = USERENV('LANG')
and seed_rubric.security_group_id(+) = g_sec_grp_id_element_grp
and seed_rubric.VIEW_APPLICATION_ID(+) = 3
and base_units.lookup_type(+) = 'FR_BASE_UNIT'
and base_units.lookup_code(+) = pet.element_information2
and base_units.LANGUAGE(+) = USERENV('LANG') /*bug 3683906*/
and base_units.security_group_id(+) = g_sec_grp_id_base_unit
and base_units.VIEW_APPLICATION_ID(+) = 3
and prrv.result_value is not null
group by nvl(user_rubric.tag,seed_rubric.tag),
nvl(user_rubric.meaning,seed_rubric.meaning) || decode(pee.creator_type,'EE',' '|| p_retro_tl,'RR',' '|| p_retro_tl),
pec.classification_name,
pet.element_information1,
pet.element_type_id,
base_units.meaning,
prrv.run_result_id,
prr.start_date,
prr.end_date,
decode(pee.creator_type,
'RR',rr_ret.assignment_action_id,
'EE',pee.source_asg_action_id),
pee.creator_type,
decode(pee.creator_type,
'EE',pee.source_start_date,
'RR',rr_ret.start_date),
decode(pee.creator_type,
'EE',pee.source_end_date,
'RR',rr_ret.end_date)
) result_rollup
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_time_periods ptp
where paa.assignment_action_id (+) = result_rollup.retro_act_id
and ppa.payroll_action_id (+) = paa.payroll_action_id
and ptp.time_period_id (+) = ppa.time_period_id
group by result_rollup.start_date
,result_rollup.end_date
,result_rollup.rubric_code
,result_rollup.description
,result_rollup.classification_name
,result_rollup.element_information1
,result_rollup.element_type_id
,result_rollup.process_type
,result_rollup.rate_value
,result_rollup.base_units_meaning
,result_rollup.week_end_date
,result_rollup.factor
,result_rollup.label
,result_rollup.absattid
,decode(classification_name, 'Benefits', run_result_id, 0)
,decode(classification_name, 'Earnings Adjustment', -1, 1)
,result_rollup.prorate_start_date
,result_rollup.prorate_end_date
,result_rollup.creator_type
,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date))
,decode(creator_type,'EE', nvl(result_rollup.retro_pro_end, ptp.end_date) , 'RR', nvl(result_rollup.retro_pro_end, ptp.end_date))
,nvl(result_rollup.start_date, fnd_date.date_to_canonical(result_rollup.prorate_start_date))
,nvl(result_rollup.end_date, fnd_date.date_to_canonical(result_rollup.prorate_end_date))
order by decode(absattid,null,null,result_rollup.start_date)
,rubric_code
,result_rollup.description desc
,week_end_date
,label
,decode(creator_type,'EE', nvl(result_rollup.retro_pro_start, ptp.start_date), 'RR', nvl(result_rollup.retro_pro_start, ptp.start_date))
,decode(creator_type,'EE',2, 'RR',1,3)
,process_type
,sum(base_value);
select upper(meaning)
from fnd_lookup_values
where lookup_type = 'FR_PROCESS_TYPE'
and view_application_id = 3
and lookup_code = p_process_type
and security_group_id = g_sec_grp_id_process_type
and language = userenv('LANG');
select action_information_id, object_version_number
from pay_action_information
where action_context_id = p_action_context_id
and action_context_type = p_action_context_type
and action_information_category = p_action_information_category
and action_information1 = p_context_prefix
and action_information2 = p_rubric
and action_information3 = to_char(p_rubric_sort);
pay_action_information_api.update_action_information (
p_action_information_id => l_action_info_id
-- , p_action_context_id => p_action_context_id
-- , p_action_context_type => p_action_context_type
, p_object_version_number => l_ovn
, p_action_information1 => nvl(p_context_prefix, hr_api.g_varchar2)
, p_action_information2 => nvl(p_rubric, hr_api.g_varchar2)
, p_action_information3 => nvl(to_char(p_rubric_sort), hr_api.g_varchar2)
-- , p_tax_unit_id => p_tax_unit_id
-- , p_action_information_category => p_action_information_category
, p_action_information4 => nvl(p_action_information4, hr_api.g_varchar2)
, p_action_information5 => nvl(p_action_information5, hr_api.g_varchar2)
, p_action_information6 => nvl(p_action_information6, hr_api.g_varchar2)
, p_action_information7 => nvl(p_action_information7, hr_api.g_varchar2)
, p_action_information8 => nvl(p_action_information8, hr_api.g_varchar2)
, p_action_information9 => nvl(p_action_information9, hr_api.g_varchar2)
, p_action_information10 => nvl(p_action_information10, hr_api.g_varchar2)
, p_action_information11 => nvl(p_action_information11, hr_api.g_varchar2)
, p_action_information12 => nvl(p_action_information12, hr_api.g_varchar2)
, p_action_information13 => nvl(p_action_information13, hr_api.g_varchar2));
select class
,element_type_id Element_type
,sum(fnd_number.canonical_to_number(base_value)) Base
,rate_value Rate
,sum(fnd_number.canonical_to_number(pay_value)) Amount
,rubric Rubric
,description Description
,sum(source_id) Source_id
,base_units
from (
select prr.run_result_id
,decode(pet.classification_id
,p_ee_classification, 'EE', 'ER') Class
,pet.element_type_id element_type_id
,max(decode(piv.name
,p_fr_base_name,prrv.result_value
,p_us_base_name,prrv.result_value)) Base_value
,max(decode(piv.name
,p_fr_rate_name,prrv.result_value
,p_us_rate_name,prrv.result_value)) Rate_value
,max(decode(piv.name
,p_fr_pay_value_name,prrv.result_value
,p_us_pay_value_name,prrv.result_value)) pay_value
,prr.source_id Source_id
,nvl(user_rubric.tag,seed_rubric.tag) Rubric
,nvl(user_rubric.meaning,seed_rubric.meaning) Description
,base_units.meaning BASE_UNITS
from pay_element_types_f pet
,pay_run_results prr
,pay_input_values_f piv
,pay_run_result_values prrv
,fnd_lookup_values user_rubric
,fnd_lookup_values seed_rubric
,fnd_lookup_values base_units
,pay_action_interlocks arc_lock
,pay_action_interlocks pre_lock
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
where pet.classification_id in (p_ee_classification, p_er_classification)
and (pet.legislation_code = 'FR' or pet.legislation_code is null)
and pet.element_type_id = prr.element_Type_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
and prr.assignment_action_id = run_assact.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and piv.element_type_id = pet.element_Type_id
and p_effective_date between piv.effective_start_date
and piv.effective_end_date
and piv.input_value_id = prrv.input_value_id
and piv.name in (p_us_pay_value_name, p_fr_pay_value_name
,p_us_base_name, p_fr_base_name
,p_us_rate_name, p_fr_rate_name)
and arc_lock.locking_action_id = p_archive_assignment_action_id
and arc_lock.locked_action_id = pre_lock.locking_action_id
and pre_lock.locked_action_id = run_assact.assignment_action_id
and run_payact.payroll_action_id = run_assact.payroll_action_id
and run_payact.action_type in ('Q', 'R')
and user_rubric.lookup_code(+) = pet.element_information1
and user_rubric.lookup_type(+) = 'FR_USER_ELEMENT_GROUP'
and user_rubric.LANGUAGE(+) = USERENV('LANG')
and user_rubric.security_group_id(+) = g_sec_grp_id_user_element_grp
and user_rubric.VIEW_APPLICATION_ID(+) = 3
and seed_rubric.lookup_code(+) = pet.element_information1
and seed_rubric.lookup_type(+) = 'FR_ELEMENT_GROUP'
and seed_rubric.LANGUAGE(+) = USERENV('LANG')
and seed_rubric.security_group_id(+) = g_sec_grp_id_element_grp
and seed_rubric.VIEW_APPLICATION_ID(+) = 3
and base_units.lookup_code(+) = pet.element_information2
and base_units.lookup_type(+) = 'FR_BASE_UNIT'
and base_units.LANGUAGE(+) = USERENV('LANG')
and base_units.security_group_id(+) = g_sec_grp_id_base_unit
and base_units.VIEW_APPLICATION_ID(+) = 3
group by
decode(pet.classification_id, p_ee_classification, 'EE', 'ER')
,pet.element_type_id
,prr.source_id
,nvl(user_rubric.tag,seed_rubric.tag)
,nvl(user_rubric.meaning,seed_rubric.meaning)
,base_units.meaning
,prr.run_result_id) result_rollup
group by
rubric
,description
,class
,element_type_id
,rate_value
,base_units
order by rubric
,source_id;
SELECT pay_fr_arc_pkg.get_parameter(legislative_parameters, 'PAYROLL_ID')
,pay_fr_arc_pkg.get_parameter(legislative_parameters, 'ASSIGNMENT_ID')
,pay_fr_arc_pkg.get_parameter(legislative_parameters, 'ASSIGNMENT_SET')
,business_group_id
,start_date
,effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT defined_balance_id
INTO l_defined_balance_id
FROM pay_defined_balances db
,pay_balance_types b
,pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id
AND d.legislation_code = 'FR';
select action_information4 name
,ppa.payroll_id payroll_id
from pay_action_information pai
,pay_payroll_actions ppa
where pai.action_information_category in('FR_SOE_ESTAB_INFORMATION', 'FR_SOE_COMPANY_DETAILS')
and pai.action_information2 is null /* address id */
and pai.action_context_id = p_payroll_action_id
and pai.action_context_id = ppa.payroll_Action_id
and pai.action_context_type = 'PA';
select rb.tax_unit_id /* estab */
, rb.source_text process_type
, rb.source_number rate
, rb.source_id cu_id
, decode(b.balance_category_id
,g_Stat_ER_Charges ,'ER'
,g_Conv_ER_Charges ,'ER'
,g_Rebates ,'ER'
,'EE') EE_ER
, decode(b.balance_category_id
,g_Rebates ,'CONTRIBUTIONS_REBATE'
,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
,'CONTRIBUTIONS') balcat
, db2.defined_balance_id defbal
, max(aa.assignment_action_id) assignment_action_id
, nvl(fcu.group_code, pet.element_information1) element_group
from pay_action_interlocks arclck
, pay_action_interlocks prelck
, pay_balance_types b
, pay_run_balances rb
, pay_defined_balances db
, pay_payroll_actions pa
, pay_assignment_actions aa
, pay_defined_balances db2
, pay_fr_contribution_usages fcu
, pay_input_values_f piv
, pay_element_types_f pet
where arclck.locking_action_id = p_archive_assignment_action_id
and prelck.locking_action_id = arclck.locked_action_id
and aa.assignment_action_id = prelck.locked_action_id
and b.balance_category_id in (g_Income_Tax_Excess
,g_Stat_EE_Deductions
,g_Stat_ER_Charges
,g_Conv_ER_Charges
,g_Conv_EE_Deductions
,g_Rebates
,g_CSG_non_Deductible)
and db.balance_type_id = b.balance_type_id
and db.balance_dimension_id in (g_asg_et_pr_ra_cu_run
,g_asg_et_pr_cu_run
,g_asg_run)
and (db.balance_dimension_id <> g_asg_run or not exists (select 1
from pay_defined_balances db1
where db1.balance_type_id = db.balance_type_id
and db1.balance_dimension_id in (g_asg_et_pr_ra_cu_run
,g_asg_et_pr_cu_run)))
and rb.defined_balance_id = db.defined_balance_id
and ((db.business_group_id is null and db.legislation_code = 'FR') or
(db.business_group_id = g_param_business_group_id))
and rb.assignment_action_id = aa.assignment_action_id
and pa.action_type in ('Q','R')
and pa.payroll_action_id = aa.payroll_action_id
and aa.run_type_id is not null
and fcu.contribution_usage_id(+) = rb.source_id
and b.input_value_id = piv.input_value_id(+)
and p_effective_Date between piv.effective_start_date(+)
and piv.effective_end_date(+)
and piv.element_type_id = pet.element_type_id(+)
and p_effective_Date between pet.effective_start_date(+)
and pet.effective_end_date(+)
and db2.balance_type_id = b.balance_type_id
and db2.balance_dimension_id = decode(db.balance_dimension_id
,g_asg_et_pr_ra_cu_run
,g_asg_et_pr_ra_cu_pro_run
,g_asg_et_pr_cu_run
,g_asg_et_pr_cu_pro_run
,g_asg_run
,g_asg_pro_run)
and (db2.business_group_id = g_param_business_group_id or
(db2.business_group_id is null and db2.legislation_code = 'FR'))
group by aa.source_action_id
, nvl(fcu.group_code, pet.element_information1)
, rb.source_text
, rb.tax_unit_id
, rb.source_number /* rate */
, rb.source_id /* cu_id */
, decode(b.balance_category_id
,g_Stat_ER_Charges ,'ER'
,g_Conv_ER_Charges ,'ER'
,g_Rebates ,'ER'
,'EE')
, decode(b.balance_category_id
,g_Rebates ,'CONTRIBUTIONS_REBATE'
,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
,'CONTRIBUTIONS')
, db2.defined_balance_id
order by max(aa.assignment_action_id)
, nvl(fcu.group_code, pet.element_information1)
, rb.source_text
, decode(b.balance_category_id
,g_Rebates ,'CONTRIBUTIONS_REBATE'
,g_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'
,g_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
,'CONTRIBUTIONS');
select
pcu.group_code group_code
, fnd_number.canonical_to_number(result.base_value) base_value
, sum(decode(result.EE_ER
,'EE',fnd_number.canonical_to_number(result.rate))) ee_rate
, sum(decode(result.EE_ER
,'EE',fnd_number.canonical_to_number(result.amount))) ee_amount
, sum(decode(result.EE_ER
,'ER',fnd_number.canonical_to_number(result.rate))) er_rate
, sum(decode(result.EE_ER
,'ER',fnd_number.canonical_to_number(result.amount))) er_amount
, result.process_type process
, elecls
, ptp_start_date
, ptp_end_date
from pay_fr_contribution_usages pcu,
(
select
max(decode(piv.name,'Process_Type',prrv.result_value)) process_type
,max(decode(piv.name,g_fr_name_base,prrv.result_value,
g_us_name_base,prrv.result_value)) base_value
,max(decode(piv.name,g_fr_name_rate,prrv.result_value,
g_us_name_rate,prrv.result_value)) rate
,max(decode(piv.name,g_fr_name_pay_value,prrv.result_value,
g_us_name_pay_value,prrv.result_value)) amount
,max(decode(piv.name,'Contribution_Usage_ID',prrv.result_value)) cu_id
,prrv.run_result_id
,decode(pet.classification_id
,g_ele_class_Stat_EE_Deductions ,'EE'
,g_ele_class_Stat_ER_Charges ,'ER'
,g_ele_class_Conv_EE_Deductions ,'EE'
,g_ele_class_Conv_ER_Charges ,'ER'
,g_ele_class_CSG_non_Deductible ,'EE'
,g_ele_class_Rebates ,'ER'
,g_ele_class_Income_Tax_Excess ,'EE') EE_ER
,decode(pet.classification_id
,g_ele_class_Stat_EE_Deductions ,'CONTRIBUTIONS'
,g_ele_class_Stat_ER_Charges ,'CONTRIBUTIONS'
,g_ele_class_Conv_EE_Deductions ,'CONTRIBUTIONS'
,g_ele_class_Conv_ER_Charges ,'CONTRIBUTIONS'
,g_ele_class_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
,g_ele_class_Rebates ,'CONTRIBUTIONS_REBATE'
,g_ele_class_Income_Tax_Excess ,'CONTRIBUTIONS_TAX') elecls
,ptp_date.start_date ptp_start_date
,ptp_date.end_date ptp_end_date
from pay_run_result_values prrv
,pay_run_results prr
,pay_element_types_f pet
,pay_input_values_f_tl piv
,pay_payroll_actions run_payact
,pay_assignment_actions run_assact
,pay_action_interlocks arc_lock
,pay_action_interlocks pre_lock
,pay_entry_process_details epd
,per_time_periods ptp_date
,pay_payroll_actions ppa_date
,pay_assignment_actions paa_date
where epd.element_entry_id = prr.element_entry_id
and epd.retro_component_id is not null
and prrv.run_result_id = prr.run_result_id
and prr.element_type_id = pet.element_type_id
and run_payact.date_earned between pet.effective_start_date
and pet.effective_end_date
and pet.classification_id in (g_ele_class_Conv_EE_Deductions
,g_ele_class_Conv_ER_Charges
,g_ele_class_Stat_EE_Deductions
,g_ele_class_Stat_ER_Charges
,g_ele_class_CSG_non_Deductible
,g_ele_class_Rebates
,g_ele_class_Income_Tax_Excess)
and piv.input_value_id = prrv.input_value_id
and piv.language = userenv('lang')
and piv.name in ( g_us_name_pay_value, g_fr_name_pay_value
,g_us_name_base, g_fr_name_base
,g_us_name_rate, g_fr_name_rate
,'Contribution_Usage_ID', 'Process_Type')
and prr.assignment_action_id = run_assact.assignment_action_id
and arc_lock.locking_action_id = p_archive_assignment_action_id
and arc_lock.locked_action_id = pre_lock.locking_action_id
and pre_lock.locked_action_id = run_assact.assignment_action_id
and run_payact.payroll_action_id = run_assact.payroll_action_id
and run_payact.action_type in ('Q', 'R')
and prrv.result_value is not null
and epd.source_asg_action_id = paa_date.assignment_action_id
and ppa_date.payroll_action_id = paa_date.payroll_action_id
and ptp_date.time_period_id = ppa_date.time_period_id
group by
prrv.run_result_id,
decode(pet.classification_id
,g_ele_class_Stat_EE_Deductions ,'EE'
,g_ele_class_Stat_ER_Charges ,'ER'
,g_ele_class_Conv_EE_Deductions ,'EE'
,g_ele_class_Conv_ER_Charges ,'ER'
,g_ele_class_CSG_non_Deductible ,'EE'
,g_ele_class_Rebates ,'ER'
,g_ele_class_Income_Tax_Excess ,'EE'),
decode(pet.classification_id
,g_ele_class_Stat_EE_Deductions ,'CONTRIBUTIONS'
,g_ele_class_Stat_ER_Charges ,'CONTRIBUTIONS'
,g_ele_class_Conv_EE_Deductions ,'CONTRIBUTIONS'
,g_ele_class_Conv_ER_Charges ,'CONTRIBUTIONS'
,g_ele_class_CSG_non_Deductible ,'CONTRIBUTIONS_CSG'
,g_ele_class_Rebates ,'CONTRIBUTIONS_REBATE'
,g_ele_class_Income_Tax_Excess ,'CONTRIBUTIONS_TAX'),
ptp_date.start_date,
ptp_date.end_date
) result
where pcu.contribution_usage_id = result.cu_id
group by pcu.group_code
, result.process_type
, result.base_value
, result.ptp_start_date
, result.ptp_end_date
, result.elecls
order by 1,7,2;
select upper(meaning)
from fnd_lookup_values
where lookup_type = 'FR_PROCESS_TYPE'
and view_application_id = 3
and lookup_code = p_process_type
and security_group_id = g_sec_grp_id_process_type
and language = userenv('LANG');
select tag ,meaning
from fnd_lookup_values
where ((lookup_type = 'FR_ELEMENT_GROUP'
and security_group_id = p_sec_grp_id_ele_grp)
OR (lookup_type = 'FR_USER_ELEMENT_GROUP'
and security_group_id = p_sec_grp_id_user_ele_grp))
and lookup_code = p_group_code
and LANGUAGE = USERENV('LANG')
and VIEW_APPLICATION_ID = 3
order by lookup_type desc;
tbl_tax_unit_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_process_type.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_ee_rate.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_cu_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_EE_ER.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_cxt_prefix.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_ee_amount.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_action_id.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_group_code.delete(l_grouped_rate_ptr+1,l_current_ptr-1);
tbl_retrieval_list.delete;
tbl_pos_idx.delete;
tbl_action_id.delete(l_action_ptr,l_grouped_rate_ptr-1);
tbl_process_type.delete(l_grouped_rate_ptr+1,tbl_process_type.last);
tbl_ee_rate.delete(l_grouped_rate_ptr+1,tbl_ee_rate.last);
tbl_cxt_prefix.delete(l_grouped_rate_ptr+1,tbl_cxt_prefix.last);
tbl_ee_amount.delete(l_grouped_rate_ptr+1,tbl_ee_amount.last);
tbl_action_id.delete(l_grouped_rate_ptr+1,tbl_action_id.last);
tbl_group_code.delete(l_grouped_rate_ptr+1,tbl_group_code.last);
tbl_cu_id.delete;
tbl_tax_unit_id.delete;
tbl_EE_ER.delete;
tbl_process_type.delete(l_current_ptr);
tbl_ee_rate.delete(l_current_ptr);
tbl_er_rate.delete(l_current_ptr);
tbl_cxt_prefix.delete(l_current_ptr);
tbl_ee_amount.delete(l_current_ptr);
tbl_er_amount.delete(l_current_ptr);
tbl_group_code.delete(l_current_ptr);
tbl_row_base.delete(l_current_ptr);
tbl_row_base.delete;
tbl_ee_rate.delete;
tbl_ee_amount.delete;
tbl_er_rate.delete;
tbl_er_amount.delete;
tbl_cxt_prefix.delete;
tbl_process_type.delete;
tbl_group_code.delete;
tbl_action_id.delete;