[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select Distinct NATIONAL_IDENTIFIER SS_Number --S30.G01.00.001 SS Number --expections report also needs it
, DECODE(SEX, 'F', DECODE(NVL(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), '-1'), '-1', LAST_NAME,NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME))
, 'M', NVL(PREVIOUS_LAST_NAME,LAST_NAME), LAST_NAME) Birth_Name--S30.G01.00.002 Birth Name
, ppf.first_name first_name
, ppf.MIDDLE_NAMES middle_names --Christian_Names--S30.G01.00.003 Christian Names
, ppf.KNOWN_AS preferred_name --First Name generally used--S30.G01.00.005
, DECODE(SEX, 'F', DECODE(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), LAST_NAME, NULL,LAST_NAME)
, 'M', DECODE( PREVIOUS_LAST_NAME, LAST_NAME, NULL, LAST_NAME)) Name--Name S30.G01.00.004/Nickname S30.G01.00.006
, ppf.title Title, ppf.sex sex, ppf.Marital_status marital_status--Title S30.G01.00.007
, ppf.full_name full_name --used in exception report
, pa.address_line2 Complement --Address (complement) S30.G01.00.008.001
, pa.address_line1 Street --Nature and name of the street S30.G01.00.008.006
, pa.region_2 INSEE_code -- INSEE Code of the town S30.G01.00.008.007
, pa.region_3 Small_Town -- Name of the town --S30.G01.00.008.009
, pa.postal_code Postal_code --Zip Code S30.G01.00.008.010
, upper(pa.town_or_city) Town --Town S30.G01.00.008.012
, null Country_Code -- Country Code -- null for the timebeing
, ft_tl.territory_short_name Country_name --Country Name S30.G01.00.008.014
, Null tot_address --Total Address Code --to be left void for the time being
, to_char(ppf.date_of_birth,'DDMMYYYY') Date_of_birth --Date of Birth S30.G01.00.009
, ppf.Town_of_birth Town_of_birth --Town of Birth S30.G01.00.010
, ppf.region_of_birth region_of_birth --Birth_code --Region_of_birth --Birth Department Code S30.G01.00.011 region_of_birth
, ft1_tl.territory_short_name country_of_birth_name --Town of Birth S30.G01.00.010 need this for validation and expections report
, ppf.country_of_birth country_of_birth --Town of Birth S30.G01.00.010 need this for validation and expections report
, ppf.nationality Nationality -- Nationality S30.G01.00.013.013
, ppf.person_id person_id-- used to send as a parameter to s41 structure.
, paf.location_id location_id--used to send as a parameter in s41 Structure
, paf.assignment_id assignment_id -- assignment_id , p_id
, ppf.employee_number employee_number -- this will be the id2 column
From per_all_people_f ppf
, per_all_assignments_f paf
, pay_assignment_actions paa
, per_addresses pa
, fnd_territories ft
, fnd_territories_tl ft_tl
, fnd_territories ft1
, fnd_territories_tl ft1_tl
, hr_organization_information org_est
, per_periods_of_service pps
Where paa.assignment_action_id = g_assign_action_id
And paf.assignment_id = paa.assignment_id
And paf.establishment_id = org_est.organization_id
And org_est.org_information1 = p_org_id
And paf.person_id = ppf.person_id
And pps.person_id = paf.person_id
And pa.person_id(+) = ppf.person_id
And pa.style(+) = 'FR'
And ((g_effective_date between paf.effective_start_date and paf.effective_end_date)
Or (pps.actual_termination_date
between paf.effective_start_date and paf.effective_end_date))
And nvl(ft.territory_code, 'FR') = nvl(pa.country, 'FR')
And ppf.country_of_birth = ft1.territory_code(+)
And ft_tl.territory_code (+) = ft.territory_code
And ft_tl.language (+) = userenv('LANG')
And ft1_tl.territory_code (+) = ft1.territory_code
And ft1_tl.language (+) = userenv('LANG');
select scl.segment2 emp_cat
, greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
||to_number(to_char(asg.effective_start_date, 'YYYY'))
|| ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
,g_param_start_date) start_date
,least(asg.effective_end_date, g_effective_date) end_date
,paa.assignment_action_id
,org_est.org_information1
from per_all_assignments_f asg
,pay_assignment_actions paa
,hr_soft_coding_keyflex scl
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and asg.effective_end_date >= g_param_start_date
and asg.effective_start_date <= g_effective_date
and asg.assignment_id = paa.assignment_id
and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and asg.establishment_id = org_est.organization_id
and org_est.org_information1 = p_org_id
and asg.person_id = p_person_id
and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
Order By Start_Date;
select scl.segment16 prof_code
, greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
||to_number(to_char(asg.effective_start_date, 'YYYY'))
|| ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
,c_param_start_date) start_date
,asg.effective_start_date effective_start_date
,least(asg.effective_end_date, c_end_date) end_date
,paa.assignment_action_id
,org_est.org_information1
from per_all_assignments_f asg
,pay_assignment_actions paa
,hr_soft_coding_keyflex scl
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and asg.effective_start_date >= c_param_start_date
and asg.effective_start_date <= c_end_date
and asg.assignment_id = paa.assignment_id
and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and asg.establishment_id = org_est.organization_id
and org_est.org_information1 = p_org_id
and asg.person_id = p_person_id
and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
Order By effective_start_date, Start_Date;
select distinct pee.entry_information1 pen_cat
, greatest(to_date('01-' ||to_number(to_char(pee.effective_start_date, 'MM')) ||'-'
||to_number(to_char(pee.effective_start_date, 'YYYY'))
|| ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
,g_param_start_date) start_date
,greatest(pee.effective_start_date, g_param_start_date) asg_start_date
,least(pee.effective_end_Date, g_effective_date) end_date
from pay_element_entries_f pee
,per_all_assignments_f asg
,hr_organization_information org_est
,pay_assignment_actions paa
where paa.assignment_action_id = g_assign_action_id
and pee.assignment_id = asg.assignment_id
and asg.assignment_id = paa.assignment_id
and asg.establishment_id = org_est.organization_id
and org_est.org_information1 = p_org_id
and asg.person_id = p_person_id
and pee.entry_information_category = 'FR_PENSION INFORMATION'
and pee.effective_start_date <= g_effective_date
and pee.effective_start_date >= g_param_start_date
order by start_date;
select distinct pac.context_value process_type
,nvl(paa_date.start_date, (ppa.date_earned - to_number(to_char(ppa.date_earned, 'DD')) + 1)) calc_start_date
,nvl(paa_date.end_date, ppa.date_earned) end_date
from pay_action_contexts pac
,ff_contexts fc
,pay_assignment_actions paa
,pay_assignment_actions paa_date
,pay_payroll_actions ppa
where pac.assignment_id = paa.assignment_id
and pac.assignment_action_id = paa.assignment_action_id
and fc.context_id = pac.context_id
and fc.context_name = 'SOURCE_TEXT'
and paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa_date.assignment_id = paa.assignment_id
and paa.source_action_id = paa_date.assignment_action_id
and ppa.date_earned between c_start_date and g_effective_date
order by calc_start_date;
select distinct pcf.ctr_information2 contract
, pcf.status status
, pcf.contract_id contract_id
, asg.effective_start_date start_date
, least(pcf.effective_end_date, c_end_date) end_date
from pay_assignment_actions paa
,per_all_assignments_f asg
,per_contracts_f pcf
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and org_est.org_information1 = p_org_id
and asg.person_id = p_person_id
and asg.assignment_id = paa.assignment_id
and asg.establishment_id = org_est.organization_id
and pcf.contract_id = asg.contract_id
and asg.effective_start_date <= c_end_date
and asg.effective_start_date >= c_param_start_date
and asg.effective_start_date between pcf.effective_start_date
and pcf.effective_end_date
Order by start_date;
Select asg.employment_category category
, greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
||to_number(to_char(asg.effective_start_date, 'YYYY'))
|| ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
,c_param_start_date) start_date
,asg.effective_start_date asg_start_date
,least(asg.effective_end_date,c_end_date) asg_end_date
From pay_assignment_actions paa
,per_all_assignments_f asg
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and asg.effective_start_date <= c_end_date
and asg.effective_start_date >= c_param_start_date
and asg.assignment_id = paa.assignment_id
and asg.establishment_id = org_est.organization_id
and org_est.org_information1 = p_org_id
and asg.person_id = p_person_id
And asg.assignment_id = paa.assignment_id
Order by asg_start_date;
(select asg.establishment_id, org_est.org_information1 company_id
,asg.effective_start_date start_date, 'FIRST' type
from pay_assignment_actions paa
,per_all_assignments_f asg
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and asg.effective_end_date >= g_param_start_date-1
and asg.effective_start_date <= g_param_start_date-1
and asg.establishment_id = org_est.ORGANIZATION_ID
and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO')
union all
-- Get all the changes in company id
(select asg.establishment_id, org_est.org_information1 company_id
,asg.effective_start_date start_date, 'HIST' type
from pay_assignment_actions paa
,per_all_assignments_f asg
,hr_organization_information org_est
where paa.assignment_action_id = g_assign_action_id
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and asg.effective_end_date >= g_param_start_date
and asg.effective_start_date <= g_effective_date
and asg.establishment_id = org_est.ORGANIZATION_ID
and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO')
ORDER by type, start_date;
Select asg.effective_start_date start_date, asg.establishment_id
, substr(org_est.org_information2,length(org_est.org_information2)-4, 5)
From pay_assignment_actions paa
,per_all_assignments_f asg
,hr_organization_information org_est
Where paa.assignment_action_id = g_assign_action_id
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
And asg.effective_start_date >= g_param_start_date
And asg.effective_start_date <= g_effective_date
And asg.establishment_id = org_est.ORGANIZATION_ID
And org_est.org_information1 = p_org_id
And org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
Order by start_date desc;
Select Distinct org_est.org_information3 hrorg
,org_est.org_information4 risk_code_month_hours
,org_est.org_information5 order_number
,org_est.org_information6 section_code
,org_est.org_information7 office_code
,org_est.ORG_INFORMATION_CONTEXT information_context
,substr(org_est.org_information2,length(org_est.org_information2)-4, 5) siret_number
-- all Work accident info details
,asg.establishment_id estab_id--
,asg.location_id asg_location
,hla.location_id est_location
/* Getting the issuing establishment Name
,hou_issue_estab_tl.name issue_estab_name
,hloc_asg_estab_tl.location_code location_name
,asg_estab_tl.name asg_estab_name
From per_all_assignments_f asg
,pay_assignment_actions paa
,hr_organization_information org_est
,hr_all_organization_units hou
,hr_locations_all hla
,hr_locations_all_tl hloc_asg_estab_tl
-- Getting the Establishment Name
,hr_all_organization_units_tl hou_issue_estab_tl
-- Getting the Assignment Establishment's Name
,hr_all_organization_units asg_estab_tl
Where paa.assignment_action_id = g_assign_action_id
and asg.person_id = p_person_id
And asg.effective_end_date between g_param_start_date and g_effective_date
And asg.assignment_id = paa.assignment_id
And asg.establishment_id = org_est.ORGANIZATION_ID
And hla.location_id(+) = hou.location_id
And asg.establishment_id = org_est.ORGANIZATION_ID
And hou.organization_id(+) = org_est.ORGANIZATION_ID
And org_est.ORG_INFORMATION_CONTEXT In ('FR_ESTAB_WORK_ACCIDENT', 'FR_ESTAB_INFO')
--
and hloc_asg_estab_tl.location_id(+) = asg.location_id
and hloc_asg_estab_tl.language(+) = userenv('LANG')
--
AND hou_issue_estab_tl.organization_id = org_est.organization_id
AND hou_issue_estab_tl.language = userenv('LANG')
--
AND asg.establishment_id = asg_estab_tl.organization_id (+);
Select distinct paa.assignment_action_id
,org_est.org_information3 hrorg
,org_est.org_information4 risk_code_month_hours
,org_est.org_information5 order_number
,org_est.org_information6 section_code
,org_est.org_information7 office_code
,org_est.ORG_INFORMATION_CONTEXT information_context
,substr(org_est.org_information2,length(org_est.org_information2)-4, 5) siret_number
,asg.establishment_id estab_id
,asg.location_id asg_location
,hla.location_id est_location
,hou_issue_estab_tl.name issue_estab_name
,hloc_asg_estab_tl.location_code location_name
,asg_estab_tl.name asg_estab_name
From pay_assignment_actions paa
,per_all_assignments_f asg
,hr_organization_information org_est
,hr_locations_all hla
,hr_locations_all_tl hloc_asg_estab_tl
,hr_all_organization_units_tl hou_issue_estab_tl
,hr_all_organization_units asg_estab_tl
,hr_all_organization_units hou
Where paa.assignment_action_id = g_assign_action_id
And paa.assignment_id = asg.assignment_id
And asg.person_id = p_person_id
And asg.establishment_id = org_est.ORGANIZATION_ID
And hla.location_id(+) = hou.location_id
And asg.establishment_id = org_est.ORGANIZATION_ID
And hou.organization_id(+) = org_est.ORGANIZATION_ID
And org_est.ORG_INFORMATION_CONTEXT In ('FR_ESTAB_WORK_ACCIDENT', 'FR_ESTAB_INFO')
and hloc_asg_estab_tl.location_id(+) = asg.location_id
and hloc_asg_estab_tl.language(+) = userenv('LANG')
AND hou_issue_estab_tl.organization_id = org_est.organization_id
AND hou_issue_estab_tl.language(+) = userenv('LANG')
AND asg.establishment_id = asg_estab_tl.organization_id (+)
and asg.effective_start_date <= p_effective_date;
Select pj.Name job_name --job Flex field as given in assedic report
, pj.job_definition_id job_definition_id
, pj.job_id job_id
, pj.job_information1 pcs_code -- Open issue, Now fetching as mentioned in assedic report, issue is closed and all the characters are retreived
, decode(pc.type,'APPRENTICESHIP','05','YOUNG_PERSON','06',
decode(pc.ctr_information2
,'FIXED_TERM','02','PERMANENT','01',Null)) contract_type --Work Contract Code
, pc.type contract_type_1 -- used to validate seasonal worker for activity type code
, sck.SEGMENT16 prof_status_code --Professional Status Code
, sck.SEGMENT14 border_worker -- used to determine border worker code
, sck.SEGMENT12 detache --used to determine border worker code
, pc.type act_type--Used for activity type code (Seasonal Worker has not been defined in seed yet)
, paf.employee_number person_number -- employee number
, asg.normal_hours norm_asg_hours-- Used for Percentage of part_time
, asg.frequency asg_frequency-- Used for Percentage of part_time
-- added for time analysis
, pc.ctr_information13 ctr_frequency -- Used for Percentage of part_time -- need to check whether its monthly or not
, pc.ctr_information12 ctr_units -- used in percentage part time calculation
, pc.ctr_information11 norm_ctr_hours -- Used for Percentage of part_time
--
, asg.effective_start_date
, asg.employment_category emp_cat -- Used for activity_type_code and percentage of part_time
, paf.full_name Name
, pca.CAG_INFORMATION1 col_aggr_code --S41.G01.00.016
, asg.assignment_id assignment_id -- used for fetch conventional classfication --S41.G01.00.017
/* DECODE(SEX, 'F', DECODE(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), LAST_NAME, NULL,LAST_NAME) , 'M', DECODE( PREVIOUS_LAST_NAME, LAST_NAME, NULL, LAST_NAME)) Name --Getting the employee full name*/
From per_all_assignments_f asg
,pay_assignment_actions paa
, per_jobs pj
, per_all_people_f paf
, per_contracts_f pc
, HR_SOFT_CODING_KEYFLEX sck
, hr_organization_information org_est
, per_collective_agreements pca
Where paa.assignment_action_id = g_assign_action_id
and asg.person_id = paf.person_id
And asg.person_id = p_person_id
And asg.assignment_id = paa.assignment_id
And org_est.org_information1 = p_org_id
And asg.establishment_id = org_est.organization_id
And p_effective_date between asg.effective_start_date
And asg.effective_end_date
And asg.job_id = pj.job_id(+)
And asg.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
And asg.contract_id=pc.contract_id(+)
And asg.COLLECTIVE_AGREEMENT_ID = pca.COLLECTIVE_AGREEMENT_ID(+)
And nvl(pca.CAG_INFORMATION_CATEGORY, 'FR') = 'FR'
Order By asg.effective_start_date Desc;
Select pef.entry_information1 pension_info--Used in employment cat code
From pay_element_entries_f pef
, pay_element_links_f pel
, pay_element_types_f pet
, per_all_assignments_f asg
, hr_organization_information org_est
,pay_assignment_actions paa
Where paa.assignment_action_id = g_assign_action_id
and pef.assignment_id = asg.assignment_id
and paa.assignment_id = asg.assignment_id
And asg.person_id = p_person_id
And asg.establishment_id = org_est.organization_id
And org_est.org_information1 = p_org_id
And pef.entry_information_category = 'FR_PENSION INFORMATION'
And p_date Between pef.effective_start_date
And pef.effective_end_date
And pef.element_link_id = pel.element_link_id
And pel.element_type_id = pet.Element_type_id
And pet.element_name = 'FR_PENSION'
Order By pef.effective_start_date Desc;
Select hla.address_line_2 complement
,hla.address_line_1 street_name
,hla.region_2 insee_code
,hla.region_3 small_town
,hla.postal_code zip_code
,upper(hla.town_or_city) town
,hla.country country_code
,ft_tl.territory_short_name country_name
From hr_locations_all hla
,fnd_territories ft
,fnd_territories_tl ft_tl
Where location_id = p_asg_location
And ft.territory_code = hla.country
And ft_tl.territory_code (+) = ft.territory_code
And ft_tl.language (+) = userenv('LANG');
Select pps.Date_start start_date
,pps.Actual_termination_date term_date
,pps.Final_process_date final_date
,pps.Leaving_reason leav_reason
From per_periods_of_service pps
,per_all_assignments_f asg
,hr_organization_information org_est
,pay_assignment_actions paa
Where paa.assignment_action_id = g_assign_action_id
and org_est.org_information1 = p_org_id
And pps.person_id = asg.person_id
And asg.person_id = p_person_id
and asg.assignment_id = paa.assignment_id
And asg.establishment_id = org_est.organization_id
And pps.date_start between asg.effective_start_date and asg.effective_end_date;
select assignment_id
,greatest(g_param_start_date,effective_start_date) effective_start_date
,least(g_effective_date, effective_end_date) effective_end_date
from per_all_assignments_f paf
where person_id = p_person_id
And g_effective_date Between effective_start_date And effective_end_date;
Select pef.element_entry_id element_entry_id
From pay_element_entries_f pef
,pay_element_links_f pel
,pay_element_types_f pet
Where pef.assignment_id = p_assignment_id
And pef.element_link_id = pel.element_link_id
And pel.element_type_id = pet.Element_type_id
And pet.element_name = 'FR_MULTIPLE_EMPLOYER'
And p_effective_date Between pet.effective_start_date
And pet.effective_end_date
And p_effective_date Between pef.effective_end_date
And pef.effective_end_date;
Select gqual.segment_attribute_type qualifier
,substr(CAGR.segment1,1,60) conv_classfication
from
per_all_assignments_f asg
,per_cagr_grades_def cagr
,fnd_id_flex_segments seg
,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_effective_date between asg.effective_start_date and asg.effective_end_date
and gqual.application_column_name = 'SEGMENT1'
and gqual.segment_attribute_type = 'COEFFICIENT'
order by seg.segment_num;
select application_id
from fnd_application
where application_short_name = 'PER';
select ptp.end_date
,ptp.start_date
from pay_action_interlocks pai
, pay_assignment_actions paa
, pay_payroll_actions ppa
, per_time_periods ptp
where pai.locking_action_id = g_assign_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and ptp.start_date > g_param_start_date
and ptp.end_date < p_effective_date
Order By ptp.start_date desc;
l_hist.DELETE;
select max(effective_start_date)
into l_param_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date < g_param_start_date;
select max(ppa.date_earned)
into c_start_date
from pay_assignment_actions paa
,pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned < g_param_start_date
and ppa.action_status = 'C'
and ppa.action_type In ('R', 'Q');
select status
into l_21_con_status
from per_contracts_f
where contract_id = l_21_con_old
and csr_contract_rec.start_date between effective_Start_date
and effective_end_Date;
l_hist.Delete(l_counter);
hr_utility.set_location('ADJUST_HISTORY DELETED:'||l_counter,6666);
l_hist.Delete(l_hist.Next(l_counter));
hr_utility.set_location('ADJUST_HISTORY DELETED 1:'||l_counter,6666);
l_hist.Delete(l_counter);
l_hist.Delete(l_counter);
hr_utility.set_location('DELETE WHERE END DATE IS NOT PROPER:'|| sqlerrm,8888);
hr_utility.set_location('S41 FR_DADS_POSITIVE_OFFSET Inserted',113);
select count(session_id)
into l_session_id
from fnd_sessions
where session_id = userenv('sessionid');
/* 4172068 insert a record only if there is no record exists in fnd_sessions table */
-- insert a row into fnd_sessions for the DBI value to be retrieved
Insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'), sysdate);
Delete from fnd_sessions where session_id = userenv('sessionid');
l_hist.DELETE;
l_hist.DELETE;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance_name
AND pbd.database_item_suffix = '_ASG_PRO_RUN'
AND pdb.legislation_code = 'FR';
select ppa.payroll_action_id,
nvl(paa_pro.end_date, ppa.date_earned) date_earned,
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_assignment_actions paa_pro,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
and paa_pro.assignment_action_id (+) = paa.source_action_id
and paa_pro.end_date(+) is not null
and paa.end_date is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_status = 'C'
/* exclude reversal results and reversed run results */
and ppa.action_type In ('R', 'Q', 'I')
and NOT EXISTS
(SELECT NULL
FROM pay_payroll_actions RPACT
, pay_assignment_actions RASSACT
, pay_action_interlocks RINTLK
where paa.assignment_action_id = RINTLK.locked_action_id
and RINTLK.locking_action_id = RASSACT.assignment_action_id
and RPACT.payroll_action_id = RASSACT.payroll_action_id
and RPACT.action_type = 'V')
and ((paa_pro.end_date between p_balance_start_date and p_balance_end_date)
or (paa_pro.end_date is null and
ppa.date_earned between p_balance_start_date and p_balance_end_date))
and (ppa.action_type = 'I' or paa.source_action_id is not null)
group by ppa.payroll_action_id, nvl(paa_pro.end_date, ppa.date_earned);