VIEW: APPS.HR_FR_TERMINATION_V

Source

View Text - Preformatted

SELECT p.first_name, p.last_name, p.title, estorg.name, estinfo.org_information2, estinfo.org_information3, comporg.name, 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), to_char(decode(pds.pds_information11, 'LAST_DAY_WORKED', fnd_date.canonical_to_date(pds.pds_information10), pds.actual_termination_date)), pay_ca_amt_in_words.pay_amount_in_words(pay_fr_settlement.get_payment(a.assignment_id, decode(pds.pds_information11,'LAST_DAY_WORKED', fnd_date.canonical_to_date(pds.pds_information10), pds.actual_termination_date)), userenv('LANG'), 'EUROS', 'CENTS'), a.assignment_id, a.business_group_id, pds.date_start, pds.period_of_service_id, null, p.person_id, pay_fr_settlement.format_full_name(p.title, p.first_name, p.last_name) FROM per_all_people_f p, per_all_assignments_f a, per_periods_of_service pds, hr_organization_information estinfo, hr_all_organization_units estorg, hr_all_organization_units comporg, per_addresses addr, hr_locations comploc, hr_locations estloc 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.TITLE
, ESTORG.NAME
, ESTINFO.ORG_INFORMATION2
, ESTINFO.ORG_INFORMATION3
, COMPORG.NAME
, 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)
, TO_CHAR(DECODE(PDS.PDS_INFORMATION11
, 'LAST_DAY_WORKED'
, FND_DATE.CANONICAL_TO_DATE(PDS.PDS_INFORMATION10)
, PDS.ACTUAL_TERMINATION_DATE))
, PAY_CA_AMT_IN_WORDS.PAY_AMOUNT_IN_WORDS(PAY_FR_SETTLEMENT.GET_PAYMENT(A.ASSIGNMENT_ID
, DECODE(PDS.PDS_INFORMATION11
, 'LAST_DAY_WORKED'
, FND_DATE.CANONICAL_TO_DATE(PDS.PDS_INFORMATION10)
, PDS.ACTUAL_TERMINATION_DATE))
, USERENV('LANG')
, 'EUROS'
, 'CENTS')
, A.ASSIGNMENT_ID
, A.BUSINESS_GROUP_ID
, PDS.DATE_START
, PDS.PERIOD_OF_SERVICE_ID
, NULL
, P.PERSON_ID
, PAY_FR_SETTLEMENT.FORMAT_FULL_NAME(P.TITLE
, P.FIRST_NAME
, P.LAST_NAME)
FROM PER_ALL_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, PER_PERIODS_OF_SERVICE PDS
, HR_ORGANIZATION_INFORMATION ESTINFO
, HR_ALL_ORGANIZATION_UNITS ESTORG
, HR_ALL_ORGANIZATION_UNITS COMPORG
, PER_ADDRESSES ADDR
, HR_LOCATIONS COMPLOC
, HR_LOCATIONS ESTLOC
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(+)