APPS.PER_FR_ELECTION SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 104

Select ORG_INFORMATION2 SIRET
From hr_organization_information
Where organization_id = p_reporting_est_id
and   org_information_context = 'FR_ESTAB_INFO';

Line: 110

Select rpad(substr(ORG.NAME,1,ESTAB_NAME_LEN),ESTAB_NAME_LEN,' ') ESTAB_NAME
,      ORG.ORGANIZATION_ID ESTABLISHMENT_ID
,      EST.ORG_INFORMATION2 SIRET
,      rpad(nvl(substr(EST.ORG_INFORMATION3,1,NAF_CODE_LEN),' '),NAF_CODE_LEN,' ') NAF_CODE
,      decode(EST.ORG_INFORMATION7,'INDUSTRY','1',
                                   'COMMERCE','2',
                                   'AGRICULTURAL','3',
                                   'MANAGEMENT','5','4') SECTION
,        rpad(nvl(substr(LOC.ADDRESS_LINE_1,1,ADDRESS_LINE1_LEN),' '),ADDRESS_LINE1_LEN,' ') ADDRESS_LINE1
,        rpad(nvl(substr(LOC.ADDRESS_LINE_2,1,ADDRESS_LINE2_LEN),' '),ADDRESS_LINE2_LEN,' ') ADDRESS_LINE2
,        rpad(nvl(substr(LOC.REGION_2,1,INSEE_CODE_LEN),' '),INSEE_CODE_LEN,' ') INSEE_CODE
,        rpad(nvl(substr(LOC.REGION_3,1,TOWN_LEN),' '),TOWN_LEN,' ') TOWN
,        rpad(nvl(substr(LOC.POSTAL_CODE,1,POSTAL_CODE_LEN),' '),POSTAL_CODE_LEN,' ') POSTAL_CODE
,        rpad(nvl(substr(LOC.TOWN_OR_CITY,1,CITY_LEN),' '),CITY_LEN,' ') CITY
From hr_organization_information est
,    hr_all_organization_units org
,    hr_locations loc
Where ((p_establishment_id is not null
   And est.organization_id = p_establishment_id)
Or    (p_establishment_id is null
   And to_number(org_information1) = p_company_id))
And est.org_information_context = 'FR_ESTAB_INFO'
And org.organization_id = est.organization_id
And org.location_id = loc.location_id (+)
order by ORG.NAME;

Line: 137

Select
 rpad(nvl(substr(per.NATIONAL_IDENTIFIER,1,SS_NUMBER_LEN),' '),SS_NUMBER_LEN,' ') SS_NUMBER
,rpad(nvl(substr(per.NATIONAL_IDENTIFIER,SS_NUMBER_LEN+1,SS_KEY_LEN),' '),SS_KEY_LEN,' ') SS_KEY
,  per.DATE_OF_BIRTH DATE_OF_BIRTH
,  rpad(nvl(substr(nvl(Per.PER_INFORMATION1,per.last_name),1,BIRTH_NAME_LEN) ,' '),BIRTH_NAME_LEN,' ') BIRTH_NAME
,  rpad(nvl(substr(Per.REGION_OF_BIRTH,1,DEPARTMENT_OF_BIRTH_LEN) ,' '),DEPARTMENT_OF_BIRTH_LEN,' ') DEPARTMENT_OF_BIRTH
,  rpad(nvl(substr(Per.TOWN_OF_BIRTH,1,PLACE_OF_BIRTH_LEN) ,' '),PLACE_OF_BIRTH_LEN,' ') PLACE_OF_BIRTH
,  rpad(nvl(substr(Per.FIRST_NAME,1,FIRST_NAME_LEN),' '),FIRST_NAME_LEN,' ') FIRST_NAME
,  rpad(nvl(substr(Per.LAST_NAME,1,LAST_NAME_LEN),' '),LAST_NAME_LEN,' ') LAST_NAME
,  nvl(SCL.SEGMENT2 ,' ') EMPLOYEE_CATEGORY
-- Modified to select contract as 'active' for CWK
,  decode(asg.assignment_type, 'C','A-ACTIVE', nvl(CON.STATUS ,' ')) CONTRACT_STATUS
,  rpad(nvl(substr(ADDR.ADDRESS_LINE1,1,ADDRESS_LINE1_LEN),' '),ADDRESS_LINE1_LEN,' ') ADDRESS_LINE1
,  rpad(nvl(substr(ADDR.ADDRESS_LINE2,1,ADDRESS_LINE2_LEN),' '),ADDRESS_LINE2_LEN,' ') ADDRESS_LINE2
,  rpad(nvl(substr(ADDR.REGION_2,1,INSEE_CODE_LEN),' '),INSEE_CODE_LEN,' ') INSEE_CODE
,  rpad(nvl(substr(ADDR.REGION_3,1,TOWN_LEN),' '),TOWN_LEN,' ') TOWN
,  rpad(nvl(substr(ADDR.POSTAL_CODE,1,POSTAL_CODE_LEN),' '),POSTAL_CODE_LEN,' ') POSTAL_CODE
,  rpad(nvl(substr(ADDR.TOWN_OR_CITY,1,CITY_LEN),' '),CITY_LEN,' ') CITY
,  nvl(Pei.PEI_INFORMATION1,' ') EXCLUDE_FLAG
,  nvl(substr(Pei.PEI_INFORMATION2,1,1),'1') ELECTORAL_COLLEGE
,  nvl(substr(Pei.PEI_INFORMATION3,1,1),'1') VOTING_PLACE
from per_all_people_f per
,    per_all_assignments_f asg
,    per_assignment_status_types ast
,    hr_soft_coding_keyflex scl
,    per_contracts_f con
,    per_addresses addr
,    per_people_extra_info pei
where asg.establishment_id = p_establishment_id
and   asg.primary_flag = 'Y'
and   l_effective_date
        between asg.effective_start_date and asg.effective_end_date
-- modified to include CWK's assignment
and   asg.assignment_type in  ('E','C')
and   asg.assignment_status_type_id = ast.assignment_status_type_id
-- modified to include CWK's assignment status
and   ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'ACTIVE_CWK', 'SUSP_CWK_ASG')
and   asg.contract_id = con.contract_id(+)
and   l_effective_date
   between nvl(con.effective_start_date,l_effective_date)
   and     nvl(con.effective_end_date,l_effective_date)
and   asg.person_id = per.person_id
and   l_effective_date
        between per.effective_start_date and per.effective_end_date
and   asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
and   per.person_id = addr.person_id (+)
and   addr.primary_flag  (+) = 'Y'
and   per.person_id = pei.person_id(+)
and   pei.pei_information_category(+) = 'FR_WORKERS_ELECTION'
order by per.FULL_NAME;