[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select hoi_issue_comp.org_information1 SIREN_issue_estab,
substr(hoi_issue_estab.org_information2, length(hoi_issue_estab.org_information2)-4, 5) NIC_issue_estab,
hou_issue_estab_tl.name name_issue_estab,
hou_issue_estab.location_id location_id,
hloc_issue_estab_tl.location_code location_name,
--
hloc_issue_estab.address_line_2 addr_compl_issue_estab,
hloc_issue_estab.address_line_1 addr_nstreet_issue_estab,
hloc_issue_estab.region_2 addr_insee_issue_estab,
hloc_issue_estab.region_3 addr_town_issue_estab,
hloc_issue_estab.postal_code addr_zip_issue_estab,
hloc_issue_estab.town_or_city addr_towncity_issue_estab,
-- (Afnor codes) addr_cntrycode_issue_estab ,
ft_issue_estab.nls_territory addr_cntryname_issue_estab,
--
'02' Send_code_live,
'01' Send_code_test,
--
hoi_cre_comp_tl.name name_cre_comp,
hoi_cre_comp.org_information1 SIREN_cre_estab,
substr(hoi_cre_estab.org_information2, length(hoi_cre_estab.org_information2)-4, 5) NIC_cre_estab,
hoi_cre_dads.org_information1 type_media_cre_estab,
hoi_cre_dads.org_information2 address_cre_estab,
hoi_cre_estab.organization_id cre_estab_org_id,
hou_cre_estab_tl.name name_cre_estab
--
From hr_all_organization_units hou_issue_estab,
hr_all_organization_units_tl hou_issue_estab_tl,
hr_all_organization_units_tl hou_cre_estab_tl,
hr_all_organization_units_tl hoi_cre_comp_tl,
hr_organization_information hoi_issue_estab_cre,
hr_organization_information hoi_issue_estab,
hr_organization_information hoi_issue_comp,
hr_locations_all hloc_issue_estab,
hr_locations_all_tl hloc_issue_estab_tl,
--
hr_organization_information hoi_cre_estab,
hr_organization_information hoi_cre_comp,
hr_organization_information hoi_cre_dads,
--
fnd_territories ft_issue_estab
--
Where hou_issue_estab.organization_id = p_param_issuing_estab_id
and hou_issue_estab.business_group_id = p_param_business_group_id
--
and hou_issue_estab_tl.organization_id = hou_issue_estab.organization_id
and hou_issue_estab_tl.language = userenv('LANG')
--
and hoi_issue_estab.organization_id(+) = hou_issue_estab.organization_id
and hoi_issue_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hoi_issue_comp.organization_id(+) = hoi_issue_estab.org_information1
and hoi_issue_comp.org_information_context(+) = 'FR_COMP_INFO'
--
and hloc_issue_estab.location_id(+)= hou_issue_estab.location_id
and hloc_issue_estab.style(+) ='FR'
--
and ft_issue_estab.territory_code(+) = hloc_issue_estab.country
--
and hloc_issue_estab_tl.location_id(+) = hou_issue_estab.location_id
and hloc_issue_estab_tl.language(+) = userenv('LANG')
--
and hoi_issue_estab_cre.organization_id(+)= hou_issue_estab.organization_id
and hoi_issue_estab_cre.org_information_context(+) = 'FR_DADS_ISSUE_CRE_INFO'
and hoi_cre_estab.organization_id(+) = hoi_issue_estab_cre.org_information1
and hoi_cre_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hoi_cre_comp.organization_id(+) = hoi_cre_estab.org_information1
and hoi_cre_comp.org_information_context(+) = 'FR_COMP_INFO'
and hoi_cre_comp_tl.organization_id(+) = hoi_cre_comp.organization_id
and hoi_cre_comp_tl.language(+) = userenv('LANG')
--
and hoi_cre_dads.organization_id(+) = hoi_issue_estab_cre.org_information1
and hoi_cre_dads.org_information_context(+) = 'FR_DADS_CRE_INFO'
and hou_cre_estab_tl.organization_id(+) = hoi_issue_estab_cre.org_information1
and hou_cre_estab_tl.language(+) = userenv('LANG');
select hoi_issue_estab_cre.org_information1 person_g01,
hou_issue_estab_tl.name name_issue_estab,
hoi_issue_estab_cre.org_information2 domain_code_g01,
hoi_issue_estab_cre.org_information3 type_media_g01,
hoi_issue_estab_cre.org_information4 address_g01
from hr_organization_information hoi_issue_estab_cre,
hr_all_organization_units_tl hou_issue_estab_tl
where hoi_issue_estab_cre.organization_id = p_issuing_estab_id
and hoi_issue_estab_cre.org_information_context = 'FR_DADS_CONTACT_INFO'
and hou_issue_estab_tl.organization_id = p_issuing_estab_id
and hou_issue_estab_tl.language = userenv('LANG');
select hoi_comp.org_information1 siren_comp,
hou_comp_tl.name name_comp,
hou_hq_estab_tl.name name_hq_estab,
hou_hq_estab.location_id location_id,
hloc_hq_tl.location_code location_name,
to_char(p_dads_end_date,'YYYY') cal_year,
hoi_hq_estab.organization_id hq_estab_id,
substr(hoi_hq_estab.org_information2,
length(hoi_hq_estab.org_information2)-4, 5) NIC_HQ_estab,
hloc_hq.address_line_2 hq_addr_complement,
hloc_hq.address_line_1 hq_addr_nn_street,
hloc_hq.region_2 hq_addr_insee_code,
hloc_hq.region_3 hq_addr_town,
hloc_hq.postal_code hq_addr_zip_code,
hloc_hq.town_or_city hq_addr_town_city,/*
(Afnor codes) hq_addr_country_code,*/
ft_hq.nls_territory hq_addr_country_name,
hoi_fisc_estab.organization_id fisc_estab_id,
substr(hoi_fisc_estab.org_information2,
length(hoi_fisc_estab.org_information2)-4, 5) NIC_fisc_estab,
hloc_fisc_estab.location_id fisc_location_id,
hloc_fisc_estab_tl.location_code fisc_location_name,
hou_fisc_estab_tl.name name_fisc_estab,
hloc_fisc_estab.address_line_2 fisc_addr_complement,
hloc_fisc_estab.address_line_1 fisc_addr_nn_street,
hloc_fisc_estab.region_2 fisc_addr_insee_code,
hloc_fisc_estab.region_3 fisc_addr_town,
hloc_fisc_estab.postal_code fisc_addr_zip_code,
hloc_fisc_estab.town_or_city fisc_addr_town_city,/*
(Afnor Codes) fisc_addr_country_code,*/
ft_fisc_estab.nls_territory fisc_addr_country_name,
hoi_cre_estab.organization_id cre_estab_id,
hou_cre_estab_tl.name cre_name,
decode(hoi_comp.org_information17
,p_cre_estab_id_chr,null
,hoi_cre_comp.org_information1) cre_siren,
decode(hoi_comp.org_information17
,p_cre_estab_id_chr,null
,substr(hoi_cre_estab.org_information2
,length(hoi_cre_estab.org_information2)-4, 5)) cre_nic,
decode(hoi_comp.org_information17
,p_cre_estab_id_chr,null
,hoi_cre_dads.org_information1) cre_media,
decode(hoi_comp.org_information17
,p_cre_estab_id_chr,null
,hoi_cre_dads.org_information2) addr_to_use,
hoi_comp_pen_stmt.org_information1 pension_stmt_addr,
hoi_comp_pen_stmt.org_information2 pension_stmt_sort1,
hoi_comp_pen_stmt.org_information3 pension_stmt_sort2,
'AOO' periodicity_code_test
from hr_all_organization_units_tl hou_comp_tl,
hr_all_organization_units_tl hou_hq_estab_tl,
hr_all_organization_units_tl hou_fisc_estab_tl,
hr_all_organization_units_tl hou_cre_estab_tl,
hr_organization_information hoi_comp,
hr_organization_information hoi_comp_pen_stmt,
hr_organization_information hoi_hq_estab,
hr_organization_information hoi_fisc_estab,
hr_all_organization_units hou_hq_estab,
hr_all_organization_units hou_fisc_estab,
hr_locations_all hloc_hq,
hr_locations_all hloc_fisc_estab,
fnd_territories ft_hq,
fnd_territories ft_fisc_estab,
hr_locations_all_tl hloc_hq_tl,
hr_locations_all_tl hloc_fisc_estab_tl,
hr_organization_information hoi_cre_estab,
hr_organization_information hoi_cre_comp,
hr_organization_information hoi_cre_dads
where hou_comp_tl.organization_id = p_company_id
and hou_comp_tl.language = userenv('LANG')
and hoi_comp.organization_id = hou_comp_tl.organization_id
and hoi_comp.org_information_context = 'FR_COMP_INFO'
and hou_hq_estab.organization_id(+) = hoi_hq_estab.organization_id
and hoi_hq_estab.organization_id(+) = hoi_comp.org_information5
and hoi_hq_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hloc_hq.location_id(+) = hou_hq_estab.location_id
and hloc_hq.style(+) = 'FR'
and ft_hq.territory_code(+) = hloc_hq.country
and hloc_hq_tl.location_id(+) = hou_hq_estab.location_id
and hloc_hq_tl.language(+) = userenv('LANG')
and hou_hq_estab_tl.organization_id(+) = hou_hq_estab.organization_id
and hou_hq_estab_tl.language(+) = userenv('LANG')
and hou_fisc_estab.organization_id(+) = hoi_fisc_estab.organization_id
and hoi_fisc_estab.organization_id(+) = hoi_comp.org_information16
and hoi_fisc_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hou_fisc_estab_tl.organization_id(+) = hou_fisc_estab.organization_id
and hou_fisc_estab_tl.language(+) = userenv('LANG')
and hloc_fisc_estab.location_id(+) = hou_fisc_estab.location_id
and hloc_fisc_estab.style(+) = 'FR'
and ft_fisc_estab.territory_code(+) = hloc_fisc_estab.country
and hloc_fisc_estab_tl.location_id(+) = hou_fisc_estab.location_id
and hloc_fisc_estab_tl.language(+) = userenv('LANG')
and hoi_cre_estab.organization_id(+) = hoi_comp.org_information17
and hoi_cre_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hoi_cre_comp.organization_id(+) = hoi_cre_estab.org_information1
and hoi_cre_comp.org_information_context(+) = 'FR_COMP_INFO'
and hou_cre_estab_tl.organization_id(+) = hoi_cre_estab.organization_id
and hou_cre_estab_tl.language(+) = userenv('LANG')
and hoi_cre_dads.organization_id(+) = hoi_cre_estab.organization_id
and hoi_cre_dads.org_information_context(+) = 'FR_DADS_CRE_INFO'
and hoi_comp_pen_stmt.organization_id(+) = hou_comp_tl.organization_id
and hoi_comp_pen_stmt.org_information_context(+) =
'FR_DADS_COMP_PENSION_STMT';
select hoi_estab_comp.org_information1 SIREN_estab,
substr (hoi_estab.org_information2, length(hoi_estab.org_information2)-4,5) NIC_estab,
hou_estab_tl.name name_estab,
hoi_estab_comp_tl.name company_name,
hou_estab.location_id location_id,
hloc_estab_tl.location_code location_name,
--
hloc_estab.address_line_2 addr_compl_estab,
hloc_estab.address_line_1 addr_nstreet_estab,
hloc_estab.region_2 addr_insee_estab,
hloc_estab.region_3 addr_town_estab,
hloc_estab.postal_code addr_zip_estab,
hloc_estab.town_or_city addr_towncity_estab,
-- (Afnor codes) addr_cntrycode_estab ,
ft_estab.nls_territory addr_cntryname_estab,
--
decode(hoi_sal_tax.org_information3,null, '02', '100', '02', '01') estab_salary_tax_code
--
from hr_all_organization_units hou_estab,
hr_all_organization_units_tl hou_estab_tl,
hr_organization_information hoi_estab,
hr_organization_information hoi_estab_comp,
hr_all_organization_units_tl hoi_estab_comp_tl,
--
hr_locations_all hloc_estab,
hr_locations_all_tl hloc_estab_tl,
fnd_territories ft_estab,
--
hr_organization_information hoi_sal_tax
--
where hou_estab.organization_id = p_estab_id
and hou_estab_tl.organization_id = hou_estab.organization_id
and hou_estab_tl.language = userenv('LANG')
--
and hoi_estab_comp_tl.organization_id(+) = hoi_estab_comp.organization_id
and hoi_estab_comp_tl.language(+) = userenv('LANG')
--
and hoi_estab.organization_id(+) = hou_estab_tl.organization_id
and hoi_estab.org_information_context(+) = 'FR_ESTAB_INFO'
and hoi_estab_comp.organization_id(+) = hoi_estab.org_information1
and hoi_estab_comp.org_information_context(+) = 'FR_COMP_INFO'
--
and hloc_estab.location_id(+) = hou_estab.location_id
and hloc_estab.style(+) ='FR'
--
and ft_estab.territory_code(+) = hloc_estab.country
--
and hloc_estab_tl.location_id(+) = hloc_estab.location_id
and hloc_estab_tl.language(+) = userenv('LANG')
--
and hoi_sal_tax.organization_id(+) = hou_estab.organization_id
and hoi_sal_tax.org_information_context(+) = 'FR_ESTAB_SALARY_TAX_LIABILITY';
Select pai_part.action_information4 part_pct
from pay_action_information pai_part,
pay_assignment_actions pasac,
per_all_assignments_f pasg
where pai_part.action_information_category = 'FR_DADS_FILE_DATA'
and pai_part.action_context_id = pasac.assignment_action_id
and pai_part.action_context_type = 'AAP'
and pai_part.action_information1 ='S41.G01.00.020'
and pai_part.action_information8 = (Select max(pai_sub.action_information8)
from pay_action_information pai_sub
where pai_sub.action_information_category = 'FR_DADS_FILE_DATA'
and pai_sub.action_context_type = 'AAP'
and pai_sub.action_context_id = pasac.assignment_action_id
and pai_sub.action_information1 ='S41.G01.00.020')
and pasg.establishment_id = p_estab_id
and c_effective_date between
pasg.effective_start_date and pasg.effective_end_date
and pasg.assignment_id = pasac.assignment_id
and pasac.payroll_action_id = p_payroll_action_id;