VIEW: APPS.HR_FR_WORK_CERT_JOBS_V

Source

View Text - Preformatted

SELECT p.first_name, p.last_name, p.full_name, 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, pds.date_start, to_char(decode(pds.pds_information11,'LAST_DAY_WORKED' , fnd_date.canonical_to_date(pds.pds_information10) , pds.actual_termination_date)), pds.period_of_service_id, per_fr_work_cert.get_job_details(p.person_id, pds.period_of_service_id) concat_jobs 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 WHERE 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.FULL_NAME
, 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
, PDS.DATE_START
, TO_CHAR(DECODE(PDS.PDS_INFORMATION11
, 'LAST_DAY_WORKED'
, FND_DATE.CANONICAL_TO_DATE(PDS.PDS_INFORMATION10)
, PDS.ACTUAL_TERMINATION_DATE))
, PDS.PERIOD_OF_SERVICE_ID
, PER_FR_WORK_CERT.GET_JOB_DETAILS(P.PERSON_ID
, PDS.PERIOD_OF_SERVICE_ID) CONCAT_JOBS
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
WHERE 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(+)