VIEW: APPS.HR_FR_WORK_CERT_V

Source

View Text - Preformatted

SELECT p.first_name, p.last_name, p.title, estorg.name, comporg.name, estinfo.org_information2, estinfo.org_information3, pay_fr_settlement.format_address(addr.address_line1, addr.address_line2, addr.region_3, addr.postal_code, addr.town_or_city), pay_fr_settlement.format_address(comploc.address_line_1, comploc.address_line_2, comploc.region_3, comploc.postal_code, comploc.town_or_city), pay_fr_settlement.format_address(estloc.address_line_1, estloc.address_line_2, estloc.region_3, estloc.postal_code, estloc.town_or_city), a.assignment_id, a.business_group_id, p.person_id person_id, to_char(pds.date_start), to_char(pds.actual_termination_date), pds.period_of_service_id period_of_service_id, SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, 'JN1,') + 4),(INSTR(job_details.concat_jobs, ',JSD1,') - ((INSTR(job_details.concat_jobs, 'JN1,') + 4)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD1,') + 6),(INSTR(job_details.concat_jobs, ',JED1,') - ((INSTR(job_details.concat_jobs, ',JSD1,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED1,') + 6),(INSTR(job_details.concat_jobs, ',JN2,') - ((INSTR(job_details.concat_jobs, ',JED1,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN2,') + 5),(INSTR(job_details.concat_jobs, ',JSD2,') - ((INSTR(job_details.concat_jobs, ',JN2,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD2,') + 6),(INSTR(job_details.concat_jobs, ',JED2,') - ((INSTR(job_details.concat_jobs, ',JSD2,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED2,') + 6),(INSTR(job_details.concat_jobs, ',JN3,') - ((INSTR(job_details.concat_jobs, ',JED2,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN3,') + 5),(INSTR(job_details.concat_jobs, ',JSD3,') - ((INSTR(job_details.concat_jobs, ',JN3,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD3,') + 6),(INSTR(job_details.concat_jobs, ',JED3,') - ((INSTR(job_details.concat_jobs, ',JSD3,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED3,') + 6),(INSTR(job_details.concat_jobs, ',JN4,') - ((INSTR(job_details.concat_jobs, ',JED3,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN4,') + 5),(INSTR(job_details.concat_jobs, ',JSD4,') - ((INSTR(job_details.concat_jobs, ',JN4,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD4,') + 6),(INSTR(job_details.concat_jobs, ',JED4,') - ((INSTR(job_details.concat_jobs, ',JSD4,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED4,') + 6),(INSTR(job_details.concat_jobs, ',JN5,') - ((INSTR(job_details.concat_jobs, ',JED4,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN5,') + 5),(INSTR(job_details.concat_jobs, ',JSD5,') - ((INSTR(job_details.concat_jobs, ',JN5,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD5,') + 6),(INSTR(job_details.concat_jobs, ',JED5,') - ((INSTR(job_details.concat_jobs, ',JSD5,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED5,') + 6),(INSTR(job_details.concat_jobs, ',JN6,') - ((INSTR(job_details.concat_jobs, ',JED5,') + 6)))), null, pay_fr_settlement.format_full_name(p.title, p.first_name, p.last_name), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, 'JN6,') + 5),(INSTR(job_details.concat_jobs, ',JSD6,') - ((INSTR(job_details.concat_jobs, 'JN6,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD6,') + 6),(INSTR(job_details.concat_jobs, ',JED6,') - ((INSTR(job_details.concat_jobs, ',JSD6,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED6,') + 6),(INSTR(job_details.concat_jobs, ',JN7,') - ((INSTR(job_details.concat_jobs, ',JED6,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN7,') + 5),(INSTR(job_details.concat_jobs, ',JSD7,') - ((INSTR(job_details.concat_jobs, ',JN7,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD7,') + 6),(INSTR(job_details.concat_jobs, ',JED7,') - ((INSTR(job_details.concat_jobs, ',JSD7,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED7,') + 6),(INSTR(job_details.concat_jobs, ',JN8,') - ((INSTR(job_details.concat_jobs, ',JED7,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN8,') + 5),(INSTR(job_details.concat_jobs, ',JSD8,') - ((INSTR(job_details.concat_jobs, ',JN8,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD8,') + 6),(INSTR(job_details.concat_jobs, ',JED8,') - ((INSTR(job_details.concat_jobs, ',JSD8,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED8,') + 6),(INSTR(job_details.concat_jobs, ',JN9,') - ((INSTR(job_details.concat_jobs, ',JED8,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN9,') + 5),(INSTR(job_details.concat_jobs, ',JSD9,') - ((INSTR(job_details.concat_jobs, ',JN9,') + 5)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD9,') + 6),(INSTR(job_details.concat_jobs, ',JED9,') - ((INSTR(job_details.concat_jobs, ',JSD9,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED9,') + 6),(INSTR(job_details.concat_jobs, ',JN10,') - ((INSTR(job_details.concat_jobs, ',JED9,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JN10,') + 6),(INSTR(job_details.concat_jobs, ',JSD10,') - ((INSTR(job_details.concat_jobs, ',JN10,') + 6)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JSD10,') + 7),(INSTR(job_details.concat_jobs, ',JED10,') - ((INSTR(job_details.concat_jobs, ',JSD10,') + 7)))), SUBSTR(job_details.concat_jobs,(INSTR(job_details.concat_jobs, ',JED10,') + 7),(INSTR(job_details.concat_jobs, ',END') - ((INSTR(job_details.concat_jobs, ',JED10,') + 7)))) FROM per_all_people_f p, per_addresses addr, per_all_assignments_f a, per_periods_of_service pds, hr_all_organization_units comporg, hr_all_organization_units estorg, hr_locations comploc, hr_locations estloc, hr_organization_information estinfo, (SELECT concatenated_job_details concat_jobs, person_id person_id, period_of_service_id pos_id FROM hr_fr_work_cert_jobs_v work_cert) job_details WHERE job_details.person_id = p.person_id and job_details.pos_id = pds.period_of_service_id and a.primary_flag = 'Y' and decode(pds.pds_information11, 'LAST_DAY_WORKED' , fnd_date.canonical_to_date(pds.pds_information10) , pds.actual_termination_date) between p.effective_start_date and p.effective_end_date and p.person_id = a.person_id and decode(pds.pds_information11, 'LAST_DAY_WORKED' , fnd_date.canonical_to_date(pds.pds_information10) , pds.actual_termination_date) between a.effective_start_date and a.effective_end_date and a.period_of_service_id = pds.period_of_service_id and a.establishment_id = estorg.organization_id and estinfo.org_information_context = 'FR_ESTAB_INFO' and estorg.organization_id = estinfo.organization_id and to_number(estinfo.org_information1) = comporg.organization_id and p.person_id = addr.person_id(+) and addr.primary_flag(+) = 'Y' and comporg.location_id = comploc.location_id(+) and estorg.location_id = estloc.location_id(+)

View Text - HTML Formatted

SELECT P.FIRST_NAME
, P.LAST_NAME
, P.TITLE
, ESTORG.NAME
, COMPORG.NAME
, ESTINFO.ORG_INFORMATION2
, ESTINFO.ORG_INFORMATION3
, PAY_FR_SETTLEMENT.FORMAT_ADDRESS(ADDR.ADDRESS_LINE1
, ADDR.ADDRESS_LINE2
, ADDR.REGION_3
, ADDR.POSTAL_CODE
, ADDR.TOWN_OR_CITY)
, PAY_FR_SETTLEMENT.FORMAT_ADDRESS(COMPLOC.ADDRESS_LINE_1
, COMPLOC.ADDRESS_LINE_2
, COMPLOC.REGION_3
, COMPLOC.POSTAL_CODE
, COMPLOC.TOWN_OR_CITY)
, PAY_FR_SETTLEMENT.FORMAT_ADDRESS(ESTLOC.ADDRESS_LINE_1
, ESTLOC.ADDRESS_LINE_2
, ESTLOC.REGION_3
, ESTLOC.POSTAL_CODE
, ESTLOC.TOWN_OR_CITY)
, A.ASSIGNMENT_ID
, A.BUSINESS_GROUP_ID
, P.PERSON_ID PERSON_ID
, TO_CHAR(PDS.DATE_START)
, TO_CHAR(PDS.ACTUAL_TERMINATION_DATE)
, PDS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, 'JN1
, ') + 4)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD1
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, 'JN1
, ') + 4))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD1
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED1
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD1
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED1
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN2
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED1
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN2
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD2
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN2
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD2
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED2
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD2
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED2
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN3
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED2
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN3
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD3
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN3
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD3
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED3
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD3
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED3
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN4
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED3
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN4
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD4
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN4
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD4
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED4
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD4
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED4
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN5
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED4
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN5
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD5
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN5
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD5
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED5
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD5
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED5
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN6
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED5
, ') + 6))))
, NULL
, PAY_FR_SETTLEMENT.FORMAT_FULL_NAME(P.TITLE
, P.FIRST_NAME
, P.LAST_NAME)
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, 'JN6
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD6
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, 'JN6
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD6
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED6
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD6
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED6
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN7
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED6
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN7
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD7
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN7
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD7
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED7
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD7
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED7
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN8
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED7
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN8
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD8
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN8
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD8
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED8
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD8
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED8
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN9
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED8
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN9
, ') + 5)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD9
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN9
, ') + 5))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD9
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED9
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD9
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED9
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN10
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED9
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN10
, ') + 6)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD10
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JN10
, ') + 6))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD10
, ') + 7)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED10
, ') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JSD10
, ') + 7))))
, SUBSTR(JOB_DETAILS.CONCAT_JOBS
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED10
, ') + 7)
, (INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, END') - ((INSTR(JOB_DETAILS.CONCAT_JOBS
, '
, JED10
, ') + 7))))
FROM PER_ALL_PEOPLE_F P
, PER_ADDRESSES ADDR
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE PDS
, HR_ALL_ORGANIZATION_UNITS COMPORG
, HR_ALL_ORGANIZATION_UNITS ESTORG
, HR_LOCATIONS COMPLOC
, HR_LOCATIONS ESTLOC
, HR_ORGANIZATION_INFORMATION ESTINFO
, (SELECT CONCATENATED_JOB_DETAILS CONCAT_JOBS
, PERSON_ID PERSON_ID
, PERIOD_OF_SERVICE_ID POS_ID
FROM HR_FR_WORK_CERT_JOBS_V WORK_CERT) JOB_DETAILS
WHERE JOB_DETAILS.PERSON_ID = P.PERSON_ID
AND JOB_DETAILS.POS_ID = PDS.PERIOD_OF_SERVICE_ID
AND A.PRIMARY_FLAG = 'Y'
AND DECODE(PDS.PDS_INFORMATION11
, 'LAST_DAY_WORKED'
, FND_DATE.CANONICAL_TO_DATE(PDS.PDS_INFORMATION10)
, PDS.ACTUAL_TERMINATION_DATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND P.PERSON_ID = A.PERSON_ID
AND DECODE(PDS.PDS_INFORMATION11
, 'LAST_DAY_WORKED'
, FND_DATE.CANONICAL_TO_DATE(PDS.PDS_INFORMATION10)
, PDS.ACTUAL_TERMINATION_DATE) BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND A.PERIOD_OF_SERVICE_ID = PDS.PERIOD_OF_SERVICE_ID
AND A.ESTABLISHMENT_ID = ESTORG.ORGANIZATION_ID
AND ESTINFO.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
AND ESTORG.ORGANIZATION_ID = ESTINFO.ORGANIZATION_ID
AND TO_NUMBER(ESTINFO.ORG_INFORMATION1) = COMPORG.ORGANIZATION_ID
AND P.PERSON_ID = ADDR.PERSON_ID(+)
AND ADDR.PRIMARY_FLAG(+) = 'Y'
AND COMPORG.LOCATION_ID = COMPLOC.LOCATION_ID(+)
AND ESTORG.LOCATION_ID = ESTLOC.LOCATION_ID(+)