[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PERSON_ID,
to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD'),
to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD'),
ppf.BUSINESS_GROUP_ID,
hr_general.decode_organization(ppf.BUSINESS_GROUP_ID) BUSINESS_GROUP_DESC,
ppf.PERSON_TYPE_ID,
pptl.user_person_type,
ppt.system_person_type,
LAST_NAME,
to_char(START_DATE,'YYYY-MM-DD'),
APPLICANT_NUMBER,
BACKGROUND_CHECK_STATUS,
to_char(BACKGROUND_DATE_CHECK,'YYYY-MM-DD'),
BLOOD_TYPE,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='BLOOD_TYPE'
AND LOOKUP_CODE=BLOOD_TYPE AND p_eff_date between nvl(start_date_active,p_eff_date)
AND nvl(end_date_active,p_eff_Date)
AND enabled_flag = 'Y')
BLOOD_TYPE_DESC,
COMMENT_ID,
CORRESPONDENCE_LANGUAGE,
(SELECT DESCRIPTION FROM FND_LANGUAGES_VL
WHERE language_code= CORRESPONDENCE_LANGUAGE)
CORRESPONDENCE_LANGUAGE_DESC,
CURRENT_APPLICANT_FLAG,
CURRENT_EMP_OR_APL_FLAG,
CURRENT_EMPLOYEE_FLAG,
to_char(DATE_EMPLOYEE_DATA_VERIFIED,'YYYY-MM-DD'),
to_char(DATE_OF_BIRTH,'YYYY-MM-DD'),
EMAIL_ADDRESS,
EMPLOYEE_NUMBER,
EXPENSE_CHECK_SEND_TO_ADDRESS,
FAST_PATH_EMPLOYEE,
FIRST_NAME,
FTE_CAPACITY,
FULL_NAME,
to_char(HOLD_APPLICANT_DATE_UNTIL,'YYYY-MM-DD'),
HONORS,
INTERNAL_LOCATION,
KNOWN_AS,
LAST_MEDICAL_TEST_BY,
to_char(LAST_MEDICAL_TEST_DATE,'YYYY-MM-DD'),
MAILSTOP,
MARITAL_STATUS,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='MAR_STATUS'
AND LOOKUP_CODE=MARITAL_STATUS AND p_eff_date between nvl(start_date_active,p_eff_date)
AND nvl(end_date_active,p_eff_Date)
AND enabled_flag = 'Y')
MARITAL_STATUS_DESC,
MIDDLE_NAMES,
NATIONALITY,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='NATIONALITY'
AND LOOKUP_CODE=NATIONALITY AND p_eff_date between nvl(start_date_active,p_eff_date)
AND nvl(end_date_active,p_eff_Date)
AND enabled_flag = 'Y')
NATIONALITY_DESC,
NATIONAL_IDENTIFIER,
(select message_text from fnd_new_messages where message_name = 'HR_NATIONAL_ID_NUMBER_'|| (select to_char(org_information9) from
hr_organization_information where organization_id = p_business_group_id
and org_information_context = 'Business Group Information')
and language_code = USERENV('LANG') ) NATIONAL_ID_LABEL,
OFFICE_NUMBER,
ON_MILITARY_SERVICE,
ORDER_NAME,
PRE_NAME_ADJUNCT,
PREVIOUS_LAST_NAME,
to_char(PROJECTED_START_DATE,'YYYY-MM-DD'),
REHIRE_AUTHORIZOR,
REHIRE_REASON,
REHIRE_RECOMMENDATION,
RESUME_EXISTS,
to_char(RESUME_LAST_UPDATED,'YYYY-MM-DD'),
REGISTERED_DISABLED_FLAG,
SECOND_PASSPORT_EXISTS,
SEX,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='SEX'
AND LOOKUP_CODE=SEX AND p_eff_date between nvl(start_date_active,p_eff_date)
AND nvl(end_date_active,p_eff_Date)
AND enabled_flag = 'Y')
SEX_DESC,
STUDENT_STATUS,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='STUDENT_STATUS'
AND LOOKUP_CODE=STUDENT_STATUS AND p_eff_date between nvl(start_date_active,p_eff_date)
AND nvl(end_date_active,p_eff_Date)
AND enabled_flag = 'Y')
STUDENT_STATUS_DESC,
SUFFIX,
TITLE,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='TITLE'
AND LOOKUP_CODE=TITLE AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
TITLE_DESC, -- too many rows - date effective ?
VENDOR_ID,
WORK_SCHEDULE,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='WORK_SCHEDULE'
AND LOOKUP_CODE=WORK_SCHEDULE AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
WORK_SCHEDULE_DESC,
WORK_TELEPHONE,
COORD_BEN_MED_PLN_NO,
COORD_BEN_NO_CVG_FLAG,
to_char(DPDNT_ADOPTION_DATE,'YYYY-MM-DD'),
DPDNT_VLNTRY_SVCE_FLAG,
to_char(RECEIPT_OF_DEATH_CERT_DATE,'YYYY-MM-DD'),
USES_TOBACCO_FLAG,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='TOBACCO_USER'
AND LOOKUP_CODE=USES_TOBACCO_FLAG AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
USES_TOBACCO_FLAG_DESC,
BENEFIT_GROUP_ID,
(SELECT NAME FROM BEN_BENFTS_GRP
WHERE BENFTS_GRP_ID = BENEFIT_GROUP_ID)
BENEFIT_GROUP_NAME,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
PER_INFORMATION_CATEGORY,
PER_INFORMATION1,
PER_INFORMATION2,
PER_INFORMATION3,
PER_INFORMATION4,
PER_INFORMATION5,
PER_INFORMATION6,
PER_INFORMATION7,
PER_INFORMATION8,
PER_INFORMATION9,
PER_INFORMATION10,
PER_INFORMATION11,
PER_INFORMATION12,
PER_INFORMATION13,
PER_INFORMATION14,
PER_INFORMATION15,
PER_INFORMATION16,
PER_INFORMATION17,
PER_INFORMATION18,
PER_INFORMATION19,
PER_INFORMATION20,
PER_INFORMATION21,
PER_INFORMATION22,
PER_INFORMATION23,
PER_INFORMATION24,
PER_INFORMATION25,
PER_INFORMATION26,
PER_INFORMATION27,
PER_INFORMATION28,
PER_INFORMATION29,
PER_INFORMATION30,
to_char(DATE_OF_DEATH,'YYYY-MM-DD'),
to_char(ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD'),
TOWN_OF_BIRTH,
REGION_OF_BIRTH,
COUNTRY_OF_BIRTH,
(SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL
WHERE TERRITORY_CODE = COUNTRY_OF_BIRTH)
COUNTRY_OF_BIRTH_DESC,
GLOBAL_PERSON_ID,
COORD_BEN_MED_PL_NAME,
COORD_BEN_MED_INSR_CRR_NAME,
COORD_BEN_MED_INSR_CRR_IDENT,
COORD_BEN_MED_EXT_ER,
to_char(COORD_BEN_MED_CVG_STRT_DT,'YYYY-MM-DD'),
to_char(COORD_BEN_MED_CVG_END_DT,'YYYY-MM-DD'),
PARTY_ID,
NPW_NUMBER,
CURRENT_NPW_FLAG,
GLOBAL_NAME,
LOCAL_NAME
FROM PER_PEOPLE_F ppf,
per_person_types ppt,
per_person_types_v pptl
where ppf.person_id = p_person_id
and ppf.business_group_id = p_business_group_id
and ppt.person_type_id = pptl.person_type_id
and ppf.person_type_id = ppt.person_type_id
and ppf.business_group_id = ppt.business_group_id (+)
and p_eff_date between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'));
SELECT ASSIGNMENT_ID
, to_char(PAAF.EFFECTIVE_START_DATE,'YYYY-MM-DD')
, to_char(PAAF.EFFECTIVE_END_DATE,'YYYY-MM-DD')
, RECRUITER_ID
, GRADE_ID
, (SELECT NAME FROM PER_GRADES_TL PGT WHERE PGT.GRADE_ID = paaf.GRADE_ID
AND LANGUAGE = USERENV('LANG')) GRADE_DESC
, POSITION_ID
, (SELECT NAME FROM HR_ALL_POSITIONS_F_TL HAPFT WHERE HAPFT.POSITION_ID = paaf.POSITION_ID
AND LANGUAGE = USERENV('LANG')) POSITION_DESC
, JOB_ID
, (SELECT NAME FROM PER_JOBS_TL PJT WHERE PJT.JOB_ID = paaf.JOB_ID AND LANGUAGE = USERENV('LANG')) JOB_DESC
, PAAF.ASSIGNMENT_STATUS_TYPE_ID
, STTL.USER_STATUS
, ST.PAY_SYSTEM_STATUS
, ST.PER_SYSTEM_STATUS
, PAYROLL_ID
, (SELECT PAYROLL_NAME FROM PAY_PAYROLLS_F PPF WHERE PPF.PAYROLL_ID = PAAF.PAYROLL_ID
and p_eff_date between ppf.effective_start_date and
nvl(ppf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PAYROLL_DESC
, LOCATION_ID
, (SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAAF.LOCATION_ID
AND LANGUAGE = USERENV('LANG')) LOCATION_DESC
, PERSON_REFERRED_BY_ID
, SUPERVISOR_ID
, (SELECT EMPLOYEE_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
and p_eff_date between papf.effective_start_date and
nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_EMP_NUMBER
, (SELECT NPW_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
and p_eff_date between papf.effective_start_date and
nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_CWK_NUMBER
, (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PAAF.SUPERVISOR_ID
and p_eff_date between papf.effective_start_date and
nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) SUPERVISOR_NAME
, SPECIAL_CEILING_STEP_ID
, RECRUITMENT_ACTIVITY_ID
, SOURCE_ORGANIZATION_ID
, ORGANIZATION_ID
, (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS_TL HAOUT WHERE HAOUT.ORGANIZATION_ID =
PAAF.ORGANIZATION_ID AND LANGUAGE = USERENV('LANG')) ORGANIZATION_DESC
, PEOPLE_GROUP_ID
, SOFT_CODING_KEYFLEX_ID
, VACANCY_ID
, (SELECT NAME FROM PER_VACANCIES PV
WHERE PV.VACANCY_ID = PAAF.VACANCY_ID) VACANCY_DESC
, PAY_BASIS_ID
, (SELECT NAME FROM PER_PAY_BASES PPB
WHERE PPB.PAY_BASIS_ID = PAAF.PAY_BASIS_ID) PAY_BASIS_DESC
, ASSIGNMENT_SEQUENCE
, ASSIGNMENT_TYPE
, PAAF.PRIMARY_FLAG
, APPLICATION_ID
, ASSIGNMENT_NUMBER
, CHANGE_REASON
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE =
DECODE(PAAF.ASSIGNMENT_TYPE,'E','EMP_ASSIGN_REASON', 'C', 'CWK_ASSIGN_REASON','')
AND LOOKUP_CODE = PAAF.CHANGE_REASON AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y') CHANGE_REASON_DESC
, COMMENT_ID
, to_char(DATE_PROBATION_END,'YYYY-MM-DD')
, DEFAULT_CODE_COMB_ID
, EMPLOYMENT_CATEGORY
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'EMP_CAT'
AND LOOKUP_CODE = EMPLOYMENT_CATEGORY
AND p_eff_date between nvl(start_date_active,p_eff_date) and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
EMPLOYMENT_CATEGORY_DESC -- Too many values
, FREQUENCY
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
AND LOOKUP_CODE = PAAF.FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
FREQUENCY_DESC -- Too many values
, INTERNAL_ADDRESS_LINE
, MANAGER_FLAG
, NORMAL_HOURS
, PERF_REVIEW_PERIOD
, PERF_REVIEW_PERIOD_FREQUENCY
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
AND LOOKUP_CODE = PAAF.PERF_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
PERF_RP_FREQUENCY_DESC -- Too many values
, PERIOD_OF_SERVICE_ID
, PROBATION_PERIOD
, PROBATION_UNIT
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS'
AND LOOKUP_CODE = PAAF.PROBATION_UNIT AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y') PROBATION_UNIT_DESC
, SAL_REVIEW_PERIOD
, SAL_REVIEW_PERIOD_FREQUENCY
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'FREQUENCY'
AND LOOKUP_CODE = PAAF.SAL_REVIEW_PERIOD_FREQUENCY AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
SAL_RP_FREQUENCY_DESC -- Too many values
, SET_OF_BOOKS_ID
, SOURCE_TYPE
, TIME_NORMAL_FINISH
, TIME_NORMAL_START
, BARGAINING_UNIT_CODE
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'BARGAINING_UNIT_CODE'
AND LOOKUP_CODE = PAAF.BARGAINING_UNIT_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
BARGAINING_UNIT_DESC
, LABOUR_UNION_MEMBER_FLAG
, HOURLY_SALARIED_CODE
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'HOURLY_SALARIED_CODE'
AND LOOKUP_CODE = PAAF.HOURLY_SALARIED_CODE AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y')
HOURLY_SALARIED_DESC
, CONTRACT_ID
, COLLECTIVE_AGREEMENT_ID
, (SELECT NAME FROM PER_COLLECTIVE_AGREEMENTS PCA WHERE PCA.COLLECTIVE_AGREEMENT_ID = PAAF.COLLECTIVE_AGREEMENT_ID) COLLECTIVE_AGREEMENT_DESC
, CAGR_ID_FLEX_NUM
, CAGR_GRADE_DEF_ID
, ESTABLISHMENT_ID
, (SELECT NAME FROM HR_ESTABLISHMENTS_V HEV WHERE HEV.ESTABLISHMENT_ID = PAAF.ESTABLISHMENT_ID) ESTABLISHMENT_DESC
, ASS_ATTRIBUTE_CATEGORY
, ASS_ATTRIBUTE1
, ASS_ATTRIBUTE2
, ASS_ATTRIBUTE3
, ASS_ATTRIBUTE4
, ASS_ATTRIBUTE5
, ASS_ATTRIBUTE6
, ASS_ATTRIBUTE7
, ASS_ATTRIBUTE8
, ASS_ATTRIBUTE9
, ASS_ATTRIBUTE10
, ASS_ATTRIBUTE11
, ASS_ATTRIBUTE12
, ASS_ATTRIBUTE13
, ASS_ATTRIBUTE14
, ASS_ATTRIBUTE15
, ASS_ATTRIBUTE16
, ASS_ATTRIBUTE17
, ASS_ATTRIBUTE18
, ASS_ATTRIBUTE19
, ASS_ATTRIBUTE20
, ASS_ATTRIBUTE21
, ASS_ATTRIBUTE22
, ASS_ATTRIBUTE23
, ASS_ATTRIBUTE24
, ASS_ATTRIBUTE25
, ASS_ATTRIBUTE26
, ASS_ATTRIBUTE27
, ASS_ATTRIBUTE28
, ASS_ATTRIBUTE29
, ASS_ATTRIBUTE30
, TITLE
, NOTICE_PERIOD
, NOTICE_PERIOD_UOM
, EMPLOYEE_CATEGORY
, (select meaning from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
AND LOOKUP_CODE = PAAF.EMPLOYEE_CATEGORY AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y') EMPLOYEE_CATEGORY_DESC
, WORK_AT_HOME
, JOB_POST_SOURCE_NAME
, '' POSTING_CONTENT_ID -- column not exist in PER_ASSIGNMENTS_F2
, to_char(PERIOD_OF_PLACEMENT_DATE_START,'YYYY-MM-DD')
, VENDOR_ID
, VENDOR_EMPLOYEE_NUMBER
, VENDOR_ASSIGNMENT_NUMBER
, ASSIGNMENT_CATEGORY
, PROJECT_TITLE
, '' APPLICANT_RANK -- column not exist in PER_ASSIGNMENTS_F2
, VENDOR_SITE_ID
, PO_HEADER_ID
, PO_LINE_ID
, PROJECTED_ASSIGNMENT_END
, GRADE_LADDER_PGM_ID
, '' GRADE_LADDER_PGM_NAME
, SUPERVISOR_ASSIGNMENT_ID
FROM PER_ASSIGNMENTS_F2 paaf,
PER_ASSIGNMENT_STATUS_TYPES ST ,
PER_ASSIGNMENT_STATUS_TYPES_TL STTL
WHERE paaf.person_id = p_person_id
AND paaf.business_group_id = p_business_group_id
AND paaf.ASSIGNMENT_STATUS_TYPE_ID = ST.ASSIGNMENT_STATUS_TYPE_ID
AND ST.ASSIGNMENT_STATUS_TYPE_ID = STTL.ASSIGNMENT_STATUS_TYPE_ID
AND STTL.LANGUAGE = USERENV('LANG')
and p_eff_date between EFFECTIVE_START_DATE AND nvl(EFFECTIVE_END_DATE,to_date('31/12/4712','DD/MM/YYYY'));
SELECT LOCATION_ID,
STYLE,
( SELECT descriptive_flex_context_name
from fnd_descr_flex_contexts_vl
where descriptive_flexfield_name ='Address Structure'
AND descriptive_flex_context_code = STYLE) STYLE_DESC,
COUNTRY,
(SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOC_INFORMATION13,
LOC_INFORMATION14,
LOC_INFORMATION15,
LOC_INFORMATION16,
LOC_INFORMATION17,
LOC_INFORMATION18,
LOC_INFORMATION19,
LOC_INFORMATION20
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = P_LOCATION_ID;
SELECT CONTACT_RELATIONSHIP_ID
, CONTACT_PERSON_ID
, (SELECT FULL_NAME FROM PER_PEOPLE_F
WHERE PERSON_ID = CONTACT_PERSON_ID
AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND P_EFF_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) CONTACT_FULL_NAME
, CONTACT_TYPE
, (SELECT MEANING FROM hr_leg_lookups WHERE LOOKUP_TYPE='CONTACT'
AND LOOKUP_CODE= CONTACT_TYPE ) CONTACT_TYPE_DESC
, PRIMARY_CONTACT_FLAG
, to_char(DATE_START,'YYYY-MM-DD')
, START_LIFE_REASON_ID
, to_char(DATE_END,'YYYY-MM-DD')
, END_LIFE_REASON_ID
, RLTD_PER_RSDS_W_DSGNTR_FLAG
, PERSONAL_FLAG
, SEQUENCE_NUMBER
, CONT_ATTRIBUTE_CATEGORY
, CONT_ATTRIBUTE1
, CONT_ATTRIBUTE2
, CONT_ATTRIBUTE3
, CONT_ATTRIBUTE4
, CONT_ATTRIBUTE5
, CONT_ATTRIBUTE6
, CONT_ATTRIBUTE7
, CONT_ATTRIBUTE8
, CONT_ATTRIBUTE9
, CONT_ATTRIBUTE10
, CONT_ATTRIBUTE11
, CONT_ATTRIBUTE12
, CONT_ATTRIBUTE13
, CONT_ATTRIBUTE14
, CONT_ATTRIBUTE15
, CONT_ATTRIBUTE16
, CONT_ATTRIBUTE17
, CONT_ATTRIBUTE18
, CONT_ATTRIBUTE19
, CONT_ATTRIBUTE20
, THIRD_PARTY_PAY_FLAG
, BONDHOLDER_FLAG
, DEPENDENT_FLAG
, BENEFICIARY_FLAG
, PARTY_ID
, CONT_INFORMATION_CATEGORY
, CONT_INFORMATION1
, CONT_INFORMATION2
, CONT_INFORMATION3
, CONT_INFORMATION4
, CONT_INFORMATION5
, CONT_INFORMATION6
, CONT_INFORMATION7
, CONT_INFORMATION8
, CONT_INFORMATION9
, CONT_INFORMATION10
, CONT_INFORMATION11
, CONT_INFORMATION12
, CONT_INFORMATION13
, CONT_INFORMATION14
, CONT_INFORMATION15
, CONT_INFORMATION16
, CONT_INFORMATION17
, CONT_INFORMATION18
, CONT_INFORMATION19
, CONT_INFORMATION20
FROM PER_CONTACT_RELATIONSHIPS PCR
where PCR.PERSON_ID = p_person_id
and PCR.business_group_id = p_business_group_id
and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') );
SELECT CONTACT_RELATIONSHIP_ID
, CONTACT_PERSON_ID
, ADDRESS_ID
, to_char(DATE_FROM,'YYYY-MM-DD')
, PRIMARY_FLAG
, STYLE
, ( SELECT descriptive_flex_context_name
from fnd_descr_flex_contexts_vl
where descriptive_flexfield_name ='Address Structure'
AND descriptive_flex_context_code = STYLE) STYLE_DESC
, ADDRESS_LINE1
, ADDRESS_LINE2
, ADDRESS_LINE3
, ADDRESS_TYPE
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y') ADDRESS_TYPE_DESC
, COUNTRY
, (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
, to_char(DATE_TO,'YYYY-MM-DD')
, POSTAL_CODE
, REGION_1
, 'REGION_1' REGION_1_DESC
, REGION_2
, 'REGION_2' REGION_2_DESC
, REGION_3
, 'REGION_3' REGION_3_DESC
, TELEPHONE_NUMBER_1
, TELEPHONE_NUMBER_2
, TELEPHONE_NUMBER_3
, TOWN_OR_CITY
, 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
, ADDR_ATTRIBUTE_CATEGORY
, ADDR_ATTRIBUTE1
, ADDR_ATTRIBUTE2
, ADDR_ATTRIBUTE3
, ADDR_ATTRIBUTE4
, ADDR_ATTRIBUTE5
, ADDR_ATTRIBUTE6
, ADDR_ATTRIBUTE7
, ADDR_ATTRIBUTE8
, ADDR_ATTRIBUTE9
, ADDR_ATTRIBUTE10
, ADDR_ATTRIBUTE11
, ADDR_ATTRIBUTE12
, ADDR_ATTRIBUTE13
, ADDR_ATTRIBUTE14
, ADDR_ATTRIBUTE15
, ADDR_ATTRIBUTE16
, ADDR_ATTRIBUTE17
, ADDR_ATTRIBUTE18
, ADDR_ATTRIBUTE19
, ADDR_ATTRIBUTE20
, ADD_INFORMATION13
, ADD_INFORMATION14
, ADD_INFORMATION15
, ADD_INFORMATION16
, ADD_INFORMATION17
, ADD_INFORMATION18
, ADD_INFORMATION19
, ADD_INFORMATION20
, PA.PARTY_ID
, DERIVED_LOCALE
, GEOMETRY
, PA.COMMENTS
FROM PER_ADDRESSES PA, PER_CONTACT_RELATIONSHIPS PCR
WHERE PCR.PERSON_ID = p_person_id
AND PCR.CONTACT_PERSON_ID = PA.PERSON_ID
and p_eff_date between PCR.DATE_START and nvl(PCR.date_end,to_date('31/12/4712','DD/MM/YYYY'))
and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
CURSOR csr_address_record(p_person_id number,p_business_group_id number,p_eff_date date) is SELECT ADDRESS_ID
, to_char(DATE_FROM,'YYYY-MM-DD')
, PRIMARY_FLAG
, STYLE
, ( SELECT descriptive_flex_context_name
from fnd_descr_flex_contexts_vl
where descriptive_flexfield_name ='Address Structure'
AND descriptive_flex_context_code = STYLE) STYLE_DESC
, ADDRESS_LINE1
, ADDRESS_LINE2
, ADDRESS_LINE3
, ADDRESS_TYPE
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'ADDRESS_TYPE' AND LOOKUP_CODE = ADDRESS_TYPE
AND p_eff_date between nvl(start_date_active,p_eff_date)
and nvl(end_date_active,p_eff_Date)
and enabled_flag = 'Y') ADDRESS_TYPE_DESC
, COUNTRY
, (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = COUNTRY) COUNTRY_DESC
, to_char(DATE_TO,'YYYY-MM-DD')
, POSTAL_CODE
, REGION_1
, 'REGION_1' REGION_1_DESC
, REGION_2
, 'REGION_2' REGION_2_DESC
, REGION_3
, 'REGION_3' REGION_3_DESC
, TELEPHONE_NUMBER_1
, TELEPHONE_NUMBER_2
, TELEPHONE_NUMBER_3
, TOWN_OR_CITY
, 'TOWN_OR_CITY' TOWN_OR_CITY_DESC
, ADDR_ATTRIBUTE_CATEGORY
, ADDR_ATTRIBUTE1
, ADDR_ATTRIBUTE2
, ADDR_ATTRIBUTE3
, ADDR_ATTRIBUTE4
, ADDR_ATTRIBUTE5
, ADDR_ATTRIBUTE6
, ADDR_ATTRIBUTE7
, ADDR_ATTRIBUTE8
, ADDR_ATTRIBUTE9
, ADDR_ATTRIBUTE10
, ADDR_ATTRIBUTE11
, ADDR_ATTRIBUTE12
, ADDR_ATTRIBUTE13
, ADDR_ATTRIBUTE14
, ADDR_ATTRIBUTE15
, ADDR_ATTRIBUTE16
, ADDR_ATTRIBUTE17
, ADDR_ATTRIBUTE18
, ADDR_ATTRIBUTE19
, ADDR_ATTRIBUTE20
, ADD_INFORMATION13
, ADD_INFORMATION14
, ADD_INFORMATION15
, ADD_INFORMATION16
, ADD_INFORMATION17
, ADD_INFORMATION18
, ADD_INFORMATION19
, ADD_INFORMATION20
, PARTY_ID
, DERIVED_LOCALE
, GEOMETRY
, COMMENTS
FROM PER_ADDRESSES PA
WHERE PA.PERSON_ID = p_person_id
and p_eff_date between pa.date_from and nvl(pa.date_to,to_date('31/12/4712','DD/MM/YYYY'));
CURSOR csr_person_extra_info_record(p_person_id number,p_business_group_id number,p_eff_date date) is SELECT PEI.PERSON_EXTRA_INFO_ID
, PEI.INFORMATION_TYPE
, PEI.PEI_ATTRIBUTE_CATEGORY
, PEI.PEI_ATTRIBUTE1
, PEI.PEI_ATTRIBUTE2
, PEI.PEI_ATTRIBUTE3
, PEI.PEI_ATTRIBUTE4
, PEI.PEI_ATTRIBUTE5
, PEI.PEI_ATTRIBUTE6
, PEI.PEI_ATTRIBUTE7
, PEI.PEI_ATTRIBUTE8
, PEI.PEI_ATTRIBUTE9
, PEI.PEI_ATTRIBUTE10
, PEI.PEI_ATTRIBUTE11
, PEI.PEI_ATTRIBUTE12
, PEI.PEI_ATTRIBUTE13
, PEI.PEI_ATTRIBUTE14
, PEI.PEI_ATTRIBUTE15
, PEI.PEI_ATTRIBUTE16
, PEI.PEI_ATTRIBUTE17
, PEI.PEI_ATTRIBUTE18
, PEI.PEI_ATTRIBUTE19
, PEI.PEI_ATTRIBUTE20
, PEI.PEI_INFORMATION_CATEGORY
, PEI.PEI_INFORMATION1
, PEI.PEI_INFORMATION2
, PEI.PEI_INFORMATION3
, PEI.PEI_INFORMATION4
, PEI.PEI_INFORMATION5
, PEI.PEI_INFORMATION6
, PEI.PEI_INFORMATION7
, PEI.PEI_INFORMATION8
, PEI.PEI_INFORMATION9
, PEI.PEI_INFORMATION10
, PEI.PEI_INFORMATION11
, PEI.PEI_INFORMATION12
, PEI.PEI_INFORMATION13
, PEI.PEI_INFORMATION14
, PEI.PEI_INFORMATION15
, PEI.PEI_INFORMATION16
, PEI.PEI_INFORMATION17
, PEI.PEI_INFORMATION18
, PEI.PEI_INFORMATION19
, PEI.PEI_INFORMATION20
, PEI.PEI_INFORMATION21
, PEI.PEI_INFORMATION22
, PEI.PEI_INFORMATION23
, PEI.PEI_INFORMATION24
, PEI.PEI_INFORMATION25
, PEI.PEI_INFORMATION26
, PEI.PEI_INFORMATION27
, PEI.PEI_INFORMATION28
, PEI.PEI_INFORMATION29
, PEI.PEI_INFORMATION30
, PEI.PARTY_ID
FROM PER_PEOPLE_EXTRA_INFO PEI
where PEI.person_id = p_person_id;
SELECT PERSON_ANALYSIS_ID
, ppa.ANALYSIS_CRITERIA_ID
, to_char(DATE_FROM,'YYYY-MM-DD')
, to_char(DATE_TO,'YYYY-MM-DD')
, ppa.ID_FLEX_NUM
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
, SEGMENT11
, SEGMENT12
, SEGMENT13
, SEGMENT14
, SEGMENT15
, SEGMENT16
, SEGMENT17
, SEGMENT18
, SEGMENT19
, SEGMENT20
, SEGMENT21
, SEGMENT22
, SEGMENT23
, SEGMENT24
, SEGMENT25
, SEGMENT26
, SEGMENT27
, SEGMENT28
, SEGMENT29
, SEGMENT30
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, PARTY_ID
, COMMENTS
FROM PER_PERSON_ANALYSES ppa,PER_ANALYSIS_CRITERIA pac
where ppa.person_id = p_person_id
and ppa.business_group_id = p_business_group_id
and ppa.analysis_criteria_id = pac.analysis_criteria_id
and ppa.id_flex_num = pac.id_flex_num
and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
SELECT PREVIOUS_EMPLOYER_ID
, PARTY_ID
, to_char(START_DATE,'YYYY-MM-DD')
, to_char(END_DATE,'YYYY-MM-DD')
, PERIOD_YEARS
, PERIOD_DAYS
, EMPLOYER_NAME
, EMPLOYER_COUNTRY
, (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_VL WHERE TERRITORY_CODE = EMPLOYER_COUNTRY) EMPLOYER_COUNTRY_DESC
, EMPLOYER_ADDRESS
, EMPLOYER_TYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_TYPE' AND LOOKUP_CODE = EMPLOYER_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') EMPLOYER_TYPE_DESC
, EMPLOYER_SUBTYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'PREV_EMP_SUBTYPE' AND LOOKUP_CODE = EMPLOYER_SUBTYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') EMPLOYER_SUBTYPE_DESC
, DESCRIPTION
, PEM_ATTRIBUTE_CATEGORY
, PEM_ATTRIBUTE1
, PEM_ATTRIBUTE2
, PEM_ATTRIBUTE3
, PEM_ATTRIBUTE4
, PEM_ATTRIBUTE5
, PEM_ATTRIBUTE6
, PEM_ATTRIBUTE7
, PEM_ATTRIBUTE8
, PEM_ATTRIBUTE9
, PEM_ATTRIBUTE10
, PEM_ATTRIBUTE11
, PEM_ATTRIBUTE12
, PEM_ATTRIBUTE13
, PEM_ATTRIBUTE14
, PEM_ATTRIBUTE15
, PEM_ATTRIBUTE16
, PEM_ATTRIBUTE17
, PEM_ATTRIBUTE18
, PEM_ATTRIBUTE19
, PEM_ATTRIBUTE20
, PEM_ATTRIBUTE21
, PEM_ATTRIBUTE22
, PEM_ATTRIBUTE23
, PEM_ATTRIBUTE24
, PEM_ATTRIBUTE25
, PEM_ATTRIBUTE26
, PEM_ATTRIBUTE27
, PEM_ATTRIBUTE28
, PEM_ATTRIBUTE29
, PEM_ATTRIBUTE30
, PEM_INFORMATION_CATEGORY
, PEM_INFORMATION1
, PEM_INFORMATION2
, PEM_INFORMATION3
, PEM_INFORMATION4
, PEM_INFORMATION5
, PEM_INFORMATION6
, PEM_INFORMATION7
, PEM_INFORMATION8
, PEM_INFORMATION9
, PEM_INFORMATION10
, PEM_INFORMATION11
, PEM_INFORMATION12
, PEM_INFORMATION13
, PEM_INFORMATION14
, PEM_INFORMATION15
, PEM_INFORMATION16
, PEM_INFORMATION17
, PEM_INFORMATION18
, PEM_INFORMATION19
, PEM_INFORMATION20
, PEM_INFORMATION21
, PEM_INFORMATION22
, PEM_INFORMATION23
, PEM_INFORMATION24
, PEM_INFORMATION25
, PEM_INFORMATION26
, PEM_INFORMATION27
, PEM_INFORMATION28
, PEM_INFORMATION29
, PEM_INFORMATION30
, ALL_ASSIGNMENTS
, PERIOD_MONTHS
FROM PER_PREVIOUS_EMPLOYERS
WHERE PERSON_ID = p_person_id
and business_group_id = p_business_group_id
and p_eff_date between start_date and nvl(end_date,to_date('31/12/4712','DD/MM/YYYY'));
SELECT INCIDENT_ID
, INCIDENT_REFERENCE
, INCIDENT_TYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INCIDENT_TYPE' AND LOOKUP_CODE = INCIDENT_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') INCIDENT_TYPE_DESC
, to_char(INCIDENT_DATE,'YYYY-MM-DD')
, INCIDENT_TIME
, ASSIGNMENT_ID
, LOCATION
, AT_WORK_FLAG
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'AT_WORK_FLAG' AND LOOKUP_CODE = AT_WORK_FLAG
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') AT_WORK_FLAG_DESC
, to_char(LAST_WORK_DATE,'YYYY-MM-DD')
, LAST_WORK_TIME
, to_char(REPORT_DATE,'YYYY-MM-DD')
, REPORT_TIME
, REPORT_METHOD
, PERSON_REPORTED_BY
, (SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PERSON_REPORTED_BY
and p_eff_date between papf.effective_start_date and
nvl(papf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))) PERSON_REPORTED_BY_NAME
, PERSON_REPORTED_TO
, WITNESS_DETAILS
, DESCRIPTION
, INJURY_TYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'INJURY_TYPE' AND LOOKUP_CODE = INJURY_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') INJURY_TYPE_DESC
, DISEASE_TYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'DISEASE_TYPE' AND LOOKUP_CODE = DISEASE_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') DISEASE_TYPE_DESC
, HAZARD_TYPE
, (SELECT MEANING FROM HR_LEG_LOOKUPS WHERE LOOKUP_TYPE = 'HAZARD_TYPE' AND LOOKUP_CODE = HAZARD_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') HAZARD_TYPE_DESC
, BODY_PART
, TREATMENT_RECEIVED_FLAG
, HOSPITAL_DETAILS
, DOCTOR_ID
, NEXT_OF_KIN_ID
, ABSENCE_ID
, to_char(COMPENSATION_DATE,'YYYY-MM-DD')
, COMPENSATION_CURRENCY
, COMPENSATION_AMOUNT
, REMEDIAL_HS_ACTION
, NOTIFIED_HSREP_ID
, to_char(NOTIFIED_HSREP_DATE,'YYYY-MM-DD')
, NOTIFIED_UREP_ID
, to_char(NOTIFIED_UREP_DATE,'YYYY-MM-DD')
, PREVIOUS_INCIDENT_ID
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
, ATTRIBUTE21
, ATTRIBUTE22
, ATTRIBUTE23
, ATTRIBUTE24
, ATTRIBUTE25
, ATTRIBUTE26
, ATTRIBUTE27
, ATTRIBUTE28
, ATTRIBUTE29
, ATTRIBUTE30
, INC_INFORMATION_CATEGORY
, INC_INFORMATION1
, INC_INFORMATION2
, INC_INFORMATION3
, INC_INFORMATION4
, INC_INFORMATION5
, INC_INFORMATION6
, INC_INFORMATION7
, INC_INFORMATION8
, INC_INFORMATION9
, INC_INFORMATION10
, INC_INFORMATION11
, INC_INFORMATION12
, INC_INFORMATION13
, INC_INFORMATION14
, INC_INFORMATION15
, INC_INFORMATION16
, INC_INFORMATION17
, INC_INFORMATION18
, INC_INFORMATION19
, INC_INFORMATION20
, INC_INFORMATION21
, INC_INFORMATION22
, INC_INFORMATION23
, INC_INFORMATION24
, INC_INFORMATION25
, INC_INFORMATION26
, INC_INFORMATION27
, INC_INFORMATION28
, INC_INFORMATION29
, INC_INFORMATION30
, to_char(ORG_NOTIFIED_DATE,'YYYY-MM-DD')
, DOCTOR_NAME
, NOTIFIED_REP_ID
, to_char(NOTIFIED_REP_DATE,'YYYY-MM-DD')
, NOTIFIED_REP_ORG_ID
, RELATED_INCIDENT_ID
, OVER_TIME_FLAG
, ABSENCE_EXISTS_FLAG
, EMERGENCY_CODE
, PRIVACY_ISSUE
, OBJECTS_INVOLVED
, ACTIVITY_AT_TIME_OF_WORK
, HOSPITAL_ADDRESS
, DAYS_RESTRICTED_WORK
, HOSPITALIZED_FLAG
, to_char(DATE_OF_DEATH,'YYYY-MM-DD')
, DAYS_AWAY_FROM_WORK
, WORK_START_TIME
, REPORTING_PERSON_PHONE
, REPORTING_PERSON_TITLE
, REPORT_COMPLETED_BY
FROM PER_WORK_INCIDENTS
WHERE PERSON_ID = p_person_id;
SELECT DELIVERY_METHOD_ID
, to_char(DATE_START,'YYYY-MM-DD')
, to_char(DATE_END,'YYYY-MM-DD')
, COMM_DLVRY_METHOD
, (SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_CM_MTHD' AND LOOKUP_CODE = COMM_DLVRY_METHOD
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC
, PREFERRED_FLAG
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE16
, ATTRIBUTE17
, ATTRIBUTE18
, ATTRIBUTE19
, ATTRIBUTE20
FROM PER_PERSON_DLVRY_METHODS
where person_id = p_person_id;
SELECT ASSIGNMENT_EXTRA_INFO_ID
, ASSIGNMENT_ID
, INFORMATION_TYPE
, AEI_ATTRIBUTE_CATEGORY
, AEI_ATTRIBUTE1
, AEI_ATTRIBUTE2
, AEI_ATTRIBUTE3
, AEI_ATTRIBUTE4
, AEI_ATTRIBUTE5
, AEI_ATTRIBUTE6
, AEI_ATTRIBUTE7
, AEI_ATTRIBUTE8
, AEI_ATTRIBUTE9
, AEI_ATTRIBUTE10
, AEI_ATTRIBUTE11
, AEI_ATTRIBUTE12
, AEI_ATTRIBUTE13
, AEI_ATTRIBUTE14
, AEI_ATTRIBUTE15
, AEI_ATTRIBUTE16
, AEI_ATTRIBUTE17
, AEI_ATTRIBUTE18
, AEI_ATTRIBUTE19
, AEI_ATTRIBUTE20
, AEI_INFORMATION_CATEGORY
, AEI_INFORMATION1
, AEI_INFORMATION2
, AEI_INFORMATION3
, AEI_INFORMATION4
, AEI_INFORMATION5
, AEI_INFORMATION6
, AEI_INFORMATION7
, AEI_INFORMATION8
, AEI_INFORMATION9
, AEI_INFORMATION10
, AEI_INFORMATION11
, AEI_INFORMATION12
, AEI_INFORMATION13
, AEI_INFORMATION14
, AEI_INFORMATION15
, AEI_INFORMATION16
, AEI_INFORMATION17
, AEI_INFORMATION18
, AEI_INFORMATION19
, AEI_INFORMATION20
, AEI_INFORMATION21
, AEI_INFORMATION22
, AEI_INFORMATION23
, AEI_INFORMATION24
, AEI_INFORMATION25
, AEI_INFORMATION26
, AEI_INFORMATION27
, AEI_INFORMATION28
, AEI_INFORMATION29
, AEI_INFORMATION30
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE assignment_id in (SELECT assignment_id from PER_ASSIGNMENTS_F
WHERE person_id = p_person_id
and business_group_id = p_business_group_id
and p_eff_date between effective_start_date and nvl(effective_end_date,to_date('31/12/4712','Dd/MM/YYYY')));
SELECT CONTACT_EXTRA_INFO_ID
, to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD')
, to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD')
, CONTACT_RELATIONSHIP_ID
, INFORMATION_TYPE
, CEI_INFORMATION_CATEGORY
, CEI_INFORMATION1
, CEI_INFORMATION2
, CEI_INFORMATION3
, CEI_INFORMATION4
, CEI_INFORMATION5
, CEI_INFORMATION6
, CEI_INFORMATION7
, CEI_INFORMATION8
, CEI_INFORMATION9
, CEI_INFORMATION10
, CEI_INFORMATION11
, CEI_INFORMATION12
, CEI_INFORMATION13
, CEI_INFORMATION14
, CEI_INFORMATION15
, CEI_INFORMATION16
, CEI_INFORMATION17
, CEI_INFORMATION18
, CEI_INFORMATION19
, CEI_INFORMATION20
, CEI_INFORMATION21
, CEI_INFORMATION22
, CEI_INFORMATION23
, CEI_INFORMATION24
, CEI_INFORMATION25
, CEI_INFORMATION26
, CEI_INFORMATION27
, CEI_INFORMATION28
, CEI_INFORMATION29
, CEI_INFORMATION30
, CEI_ATTRIBUTE_CATEGORY
, CEI_ATTRIBUTE1
, CEI_ATTRIBUTE2
, CEI_ATTRIBUTE3
, CEI_ATTRIBUTE4
, CEI_ATTRIBUTE5
, CEI_ATTRIBUTE6
, CEI_ATTRIBUTE7
, CEI_ATTRIBUTE8
, CEI_ATTRIBUTE9
, CEI_ATTRIBUTE10
, CEI_ATTRIBUTE11
, CEI_ATTRIBUTE12
, CEI_ATTRIBUTE13
, CEI_ATTRIBUTE14
, CEI_ATTRIBUTE15
, CEI_ATTRIBUTE16
, CEI_ATTRIBUTE17
, CEI_ATTRIBUTE18
, CEI_ATTRIBUTE19
, CEI_ATTRIBUTE20
FROM PER_CONTACT_EXTRA_INFO_F
WHERE CONTACT_RELATIONSHIP_ID in (SELECT PCR.CONTACT_RELATIONSHIP_ID
FROM PER_CONTACT_RELATIONSHIPS PCR
where PCR.PERSON_ID = p_person_id
and PCR.business_group_id = p_business_group_id
and p_eff_date between DATE_START and nvl(date_end,to_date('31/12/4712','DD/MM/YYYY') ));
SELECT
PHONE_ID,
to_char(DATE_FROM,'YYYY-MM-DD'),
to_char(DATE_TO,'YYYY-MM-DD'),
PHONE_TYPE,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PHONE_TYPE' AND LOOKUP_CODE = PHONE_TYPE
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') COMM_DLVRY_METHOD_DESC,
PHONE_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
PARTY_ID,
VALIDITY
FROM PER_PHONES
WHERE PARENT_ID = p_person_id
AND PARENT_TABLE = 'PER_ALL_PEOPLE_F'
and p_eff_date between DATE_FROM and nvl(DATE_TO,to_date('31/12/4712','DD/MM/YYYY'));
select QUALIFICATION_ID,
TITLE,
GRADE_ATTAINED,
STATUS,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = STATUS
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') STATUS_DESC,
TO_CHAR(AWARDED_DATE,'YYYY-MM-DD'),
FEE,
FEE_CURRENCY,
(SELECT name from fnd_currencies_vl WHERE
CURRENCY_CODE = FEE_CURRENCY
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') FEE_CURRENCY_DESC,
TRAINING_COMPLETED_AMOUNT,
REIMBURSEMENT_ARRANGEMENTS,
TRAINING_COMPLETED_UNITS,
TOTAL_TRAINING_AMOUNT,
TO_CHAR(START_DATE,'YYYY-MM-DD'),
TO_CHAR(END_DATE,'YYYY-MM-DD'),
LICENSE_NUMBER,
TO_CHAR(EXPIRY_DATE,'YYYY-MM-DD'),
LICENSE_RESTRICTIONS,
TO_CHAR(PROJECTED_COMPLETION_DATE,'YYYY-MM-DD'),
AWARDING_BODY,
TUITION_METHOD,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_TUITION_METHODS' AND LOOKUP_CODE = TUITION_METHOD
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y') TUITION_METHOD_DESC,
GROUP_RANKING,
COMMENTS,
pq.QUALIFICATION_TYPE_ID,
TL.name,
pq.ATTENDANCE_ID,
pq.ATTRIBUTE_CATEGORY,
pq.ATTRIBUTE1,
pq.ATTRIBUTE2,
pq.ATTRIBUTE3,
pq.ATTRIBUTE4,
pq.ATTRIBUTE5,
pq.ATTRIBUTE6,
pq.ATTRIBUTE7,
pq.ATTRIBUTE8,
pq.ATTRIBUTE9,
pq.ATTRIBUTE10,
pq.ATTRIBUTE11,
pq.ATTRIBUTE12,
pq.ATTRIBUTE13,
pq.ATTRIBUTE14,
pq.ATTRIBUTE15,
pq.ATTRIBUTE16,
pq.ATTRIBUTE17,
pq.ATTRIBUTE18,
pq.ATTRIBUTE19,
pq.ATTRIBUTE20,
pq.PARTY_ID,
PROFESSIONAL_BODY_NAME,
MEMBERSHIP_NUMBER,
MEMBERSHIP_CATEGORY,
SUBSCRIPTION_PAYMENT_METHOD,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PQP_SUBSCRIPTION_PAY_METHODS' AND LOOKUP_CODE = SUBSCRIPTION_PAYMENT_METHOD
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y')SUBSCRIPTION_PAYMENT_DESC,
QUA_INFORMATION_CATEGORY,
QUA_INFORMATION1,
QUA_INFORMATION2,
QUA_INFORMATION3,
QUA_INFORMATION4,
QUA_INFORMATION5,
QUA_INFORMATION6,
QUA_INFORMATION7,
QUA_INFORMATION8,
QUA_INFORMATION9,
QUA_INFORMATION10,
QUA_INFORMATION11,
QUA_INFORMATION12,
QUA_INFORMATION13,
QUA_INFORMATION14,
QUA_INFORMATION15,
QUA_INFORMATION16,
QUA_INFORMATION17,
QUA_INFORMATION18,
QUA_INFORMATION19,
QUA_INFORMATION20
FROM
PER_QUALIFICATIONS pq,per_qualification_types_tl tl,PER_ESTABLISHMENT_ATTENDANCES pea
WHERE (pq.person_id = p_person_id or pea.person_id = p_person_id)
and pq.business_group_id = p_business_group_id
and pq.attendance_id = pea.attendance_id(+)
and tl.qualification_type_id = pq.qualification_type_id
and tl.language = userenv('LANG')
and nvl(start_date,p_eff_date) <= p_eff_date;
SELECT SUBJECTS_TAKEN_ID,
to_char(START_DATE,'YYYY-MM-DD'),
MAJOR,
SUBJECT_STATUS,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECT_STATUSES' AND LOOKUP_CODE = SUBJECT_STATUS
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y')SUBJECT_STATUS_DESC,
SUBJECT,
(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PER_SUBJECTS' AND LOOKUP_CODE = SUBJECT
AND P_EFF_DATE BETWEEN NVL(START_DATE_ACTIVE,P_EFF_DATE) AND NVL(END_DATE_ACTIVE,P_EFF_DATE)
AND ENABLED_FLAG = 'Y')
SUBJECT_DESC,
GRADE_ATTAINED,
to_char(END_DATE,'YYYY-MM-DD'),
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
SUB_INFORMATION_CATEGORY,
SUB_INFORMATION1,
SUB_INFORMATION2,
SUB_INFORMATION3,
SUB_INFORMATION4,
SUB_INFORMATION5,
SUB_INFORMATION6,
SUB_INFORMATION7,
SUB_INFORMATION8,
SUB_INFORMATION9,
SUB_INFORMATION10,
SUB_INFORMATION11,
SUB_INFORMATION12,
SUB_INFORMATION13,
SUB_INFORMATION14,
SUB_INFORMATION15,
SUB_INFORMATION16,
SUB_INFORMATION17,
SUB_INFORMATION18,
SUB_INFORMATION19,
SUB_INFORMATION20
FROM PER_SUBJECTS_TAKEN
WHERE qualification_id = p_qualification_id
and nvl(start_date,p_eff_date) <= p_eff_date;
SELECT distinct ppf.full_name,
ppf.person_id,
employee_number ,
ppf.npw_number,
ppf.applicant_number,
papf.location_id,
(SELECT DESCRIPTION FROM HR_LOCATIONS_ALL_TL HLAT WHERE HLAT.LOCATION_ID = PAPF.LOCATION_ID
AND LANGUAGE = USERENV('LANG')) REPORTEE_LOCATION_DESC,
papf.assignment_status_type_id,
(SELECT USER_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES_TL
WHERE ASSIGNMENT_STATUS_TYPE_ID = papf.assignment_status_type_id
AND LANGUAGE = USERENV('LANG')) REPORTEE_ASG_STATUS_DESC
FROM per_assignments_f papf , per_people_f ppf
WHERE
p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
and p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
and papf.business_group_id = P_BUSINESS_GROUP_ID
and papf.supervisor_id = P_PERSON_ID
and papf.person_id=ppf.person_id;
SELECT ACCRUAL_PLAN_ID,
ACCRUAL_PLAN_NAME,
ACCRUAL_UNITS_OF_MEASURE_NAME,
ASSIGNMENT_ID,
PAYROLL_ID
FROM PAY_VIEW_ACCRUAL_PLANS_V
WHERE PERSON_ID = p_person_id
AND BUSINESS_GROUP_ID = p_business_group_id
AND P_EFF_dATE BETWEEN ASG_EFFECTIVE_START_DATE AND ASG_EFFECTIVE_END_DATE;
select distinct pen.per_in_ler_id,pil.lf_evt_ocrd_dt
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_ler_f le
where pen.business_group_id=p_business_group_id
and p_effective_date BETWEEN pen.effective_start_date AND pen.effective_end_date
and p_effective_date BETWEEN le.effective_start_date AND le.effective_end_date
and pen.prtt_enrt_rslt_stat_cd IS NULL
and pil.ler_id=le.ler_id
and pen.ENRT_CVG_THRU_DT > pen.EFFECTIVE_START_DATE
and le.typ_cd not in ('IREC','GSP','COMP','ABS','SCHEDDU','SCHEDDA')
and pen.person_id=p_person_id
order by pil.lf_evt_ocrd_dt desc; */
select dependent_full_name, relation, plan_type_name, 'COVERED'
from
(
select pln.name Plan_Name,
opt.name Option_Name,
plt.name plan_type_name,
(select name from ben_pgm_f pgm
where p_effective_date between pgm.effective_start_date
and pgm.effective_end_date
and pgm.pgm_id=epe.pgm_id
and pgm.business_group_id = p_business_group_id) Program_Name,
ppf.first_name||' '||ppf.last_name || ' ' || ppf.suffix Dependent,
ppf.national_identifier Ssn,
(select HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type)
from per_contact_relationships pcr
where pcr.personal_flag = 'Y'
and pcr.person_id = pen.person_id
and pcr.contact_person_id = pdp.dpnt_person_id
and p_effective_date between nvl(pcr.date_start, p_effective_date )
and nvl(pcr.date_end, p_effective_date )
and decode(pcr.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8) <=
(select decode(pcr2.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8)
from per_contact_relationships pcr2
where pcr2.person_id = pcr.person_id
and pcr2.contact_person_id = pcr.contact_person_id
and p_effective_date between nvl(pcr2.date_start, p_effective_date )
and nvl(pcr2.date_end, p_effective_date )
and pcr2.personal_flag = 'Y'
)
and rownum = 1
) Relation,
epe.per_in_ler_id,
epe.pgm_id,
ler.name le_name,
ppf.full_name dependent_full_name,
pdp.cvg_strt_dt,
to_date(null) cvg_thru_dt
from ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp,
per_contact_relationships pcr,
per_people_f ppf,
ben_pl_typ_f plt,
ben_pl_f pln,
ben_opt_f opt,
ben_oipl_f oipl,
ben_per_in_ler pil,
ben_ler_f ler
where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and epe.pl_id = pln.pl_id
and epe.pl_typ_id = plt.pl_typ_id
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
and pdp.dpnt_person_id = pcr.contact_person_id
and pcr.contact_person_id = ppf.person_id
and pcr.personal_flag = 'Y'
and epe.prtt_enrt_rslt_id is not null
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and pcr.person_id = pil.person_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
-- Code Changes for bug 7689952 - Start
--and pil.per_in_ler_id = l_per_in_ler_id
and pen.enrt_cvg_thru_dt >= pen.effective_start_date
and pil.per_in_ler_id = pen.per_in_ler_id
and pen.enrt_cvg_thru_dt >= p_effective_date
-- Code Changes for bug 7689952 - End
--and epe.pgm_id = :8
and pil.person_id = p_person_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between plt.effective_start_date
and plt.effective_end_date
and p_effective_date between nvl(pcr.date_start, p_effective_date )
and nvl(pcr.date_end, p_effective_date )
and p_effective_date between ppf.effective_start_date
and ppf.effective_end_date
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pdp.cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
and pdp.per_in_ler_id = pil.per_in_ler_id
and pen.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
and p_effective_date between
oipl.effective_start_date (+) and
oipl.effective_end_date (+)
and p_effective_date between
opt.effective_start_date (+) and
opt.effective_end_date (+)
and pcr.business_group_id = p_business_group_id
and ppf.business_group_id = p_business_group_id
and plt.business_group_id = p_business_group_id
and pln.business_group_id = p_business_group_id
and opt.business_group_id = p_business_group_id
and oipl.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and epe.business_group_id = p_business_group_id
and pen.business_group_id = p_business_group_id
and pdp.business_group_id = p_business_group_id
and ler.business_group_id = p_business_group_id
);
select Plan_Type_Name,Plan_Name, Option_Name, Beneficiary, Ssn, Relation, Primary_Bnf, Contingent_Bnf,
le_name,beneficiary_full_name,Primary_Bnf_Amt,Contingent_Bnf_Amt
from (select plt.name Plan_Type_Name,pln.name Plan_Name, opt.name Option_Name,
ppf.last_name last_name,
ppf.first_name first_name,
decode(pbn.organization_id, null, ppf.first_name||
' '||ppf.last_name || ' ' || ppf.suffix,
org.name) Beneficiary,
nvl(ppf.full_name, org.name) beneficiary_full_name,
ppf.national_identifier Ssn,
nvl(HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type),
decode(pbn.organization_id, null,
HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','SLF'),
HR_GENERAL.DECODE_LOOKUP('BEN_EXT_RLSHP','TP'))) Relation, sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.pct_dsgd_num,0)) Primary_Bnf, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.pct_dsgd_num,0)) Contingent_Bnf,
sum(decode(pbn.prmry_cntngnt_cd,'PRIMY',pbn.amt_dsgd_val,0)) Primary_Bnf_Amt, sum(decode(pbn.prmry_cntngnt_cd,'CNTNGNT',pbn.amt_dsgd_val,0)) Contingent_Bnf_Amt,
pcr.contact_type contact_type,
ler.name le_name,
ppf.date_of_birth date_of_birth,
pen.ptip_ordr_num ptip_ordr_num,
pen.plip_ordr_num plip_ordr_num,
pen.pl_ordr_num pl_ordr_num,
pen.oipl_ordr_num oipl_ordr_num,
pen.bnft_ordr_num bnft_ordr_num
from per_people_f ppf,
per_contact_relationships pcr,
ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn,
ben_pl_typ_f plt,
ben_pl_f pln,
hr_all_organization_units org,
ben_opt_f opt,
ben_oipl_f oipl,
ben_per_in_ler pil,
ben_ler_f ler
where pen.pl_id = pln.pl_id
-- Code Changes for bug 7689952 - Start
--and (pen.per_in_ler_id = l_per_in_ler_id)
and pen.enrt_cvg_thru_dt >= pen.effective_start_date
and pen.enrt_cvg_thru_dt >= p_effective_date
-- Code Changes for bug 7689952 - End
and pen.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
and pln.invk_dcln_prtn_pl_flag = 'N'
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and pbn.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and exists (select null from ben_per_in_ler pil
where pil.per_in_ler_id = pbn.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
and pen.prtt_enrt_rslt_stat_cd IS NULL
and pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
and pen.pl_typ_id = plt.pl_typ_id
and pcr.personal_flag(+) = 'Y'
and pcr.person_id(+) = p_person_id
and pbn.bnf_person_id = pcr.contact_person_id(+)
and pbn.bnf_person_id = ppf.person_id(+)
and pbn.organization_id = org.organization_id(+)
and p_effective_date between ler.effective_start_date
and ler.effective_end_date
and p_effective_date between plt.effective_start_date
and plt.effective_end_date
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date
and p_effective_date between
nvl(ppf.effective_start_date, p_effective_date ) and
nvl(ppf.effective_end_date, p_effective_date )
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between
nvl(org.date_from, p_effective_date ) and
nvl(org.date_to, p_effective_date )
and p_effective_date between
oipl.effective_start_date (+) and
oipl.effective_end_date (+)
and p_effective_date between
opt.effective_start_date (+) and
opt.effective_end_date (+)
and ppf.business_group_id = p_business_group_id
and pcr.business_group_id = p_business_group_id
and pen.business_group_id = p_business_group_id
and pbn.business_group_id = p_business_group_id
and plt.business_group_id = p_business_group_id
and pln.business_group_id = p_business_group_id
and opt.business_group_id = p_business_group_id
and oipl.business_group_id = p_business_group_id
and org.business_group_id(+) = p_business_group_id
and ler.business_group_id = p_business_group_id
and (pcr.contact_relationship_id is null or
(pcr.contact_relationship_id is not null and
(p_effective_date between
nvl(pcr.date_start, p_effective_date ) and
nvl(pcr.date_end, p_effective_date )) or
((pcr.date_start = (select max(pcr2.date_start)
from per_contact_relationships pcr2
where pcr2.contact_person_id = pcr.contact_person_id
and pcr2.person_id = pcr.person_id
and pcr2.personal_flag = 'Y')) and
not exists (select null
from PER_CONTACT_RELATIONSHIPS pcr3
where pcr3.contact_person_id = pcr.contact_person_id
and pcr3.person_id = pcr.person_id
and pcr3.personal_flag = 'Y'
and p_effective_date between
nvl(pcr3.date_start, p_effective_date )
and nvl(pcr3.date_end, p_effective_date )))
))
group by plt.name,
pln.name,
opt.name,
ppf.first_name,
ppf.last_name,
ppf.suffix,
ppf.full_name,
pbn.organization_id,
org.name,
ppf.national_identifier,
pcr.contact_type,
ler.name,
ppf.date_of_birth,
pln.bnf_cntngt_bnfs_alwd_flag,
pen.ptip_ordr_num,
pen.plip_ordr_num,
pen.pl_ordr_num,
pen.oipl_ordr_num,
pen.bnft_ordr_num
)order by ptip_ordr_num,
plip_ordr_num,
pl_ordr_num,
oipl_ordr_num,
bnft_ordr_num,
Plan_Name,
decode(contact_type,'S',1,'D',1,'A',2,
'C',2,'O',2,'T',2,'P',3,4),
date_of_birth,
last_name,
first_name,
Beneficiary;
SELECT plan_type_name,
plan_name,
option_name
FROM
(
SELECT elc.plt_name plan_type_name,
pln.name plan_name,
opt.name option_name,
elc.Program_Name,
elc.Coverage,
elc.le_name,
elc.per_in_ler_id,
elc.pgm_id,
elc.sspndd_flag,
elc.crntly_enrd_flag,
elc.elctbl_flag,
elc.enrt_cvg_strt_dt,
elc.enrt_cvg_thru_dt,
decode(elc.sspndd_flag,'Y','Suspended') suspended,
decode(elc.interim, 'Y', 'Interim') interim
FROM
(
SELECT epe.ptip_ordr_num ptip_ordr_num,
epe.plip_ordr_num plip_ordr_num,
pen.pl_ordr_num pl_ordr_num,
epe.oipl_ordr_num oipl_ordr_num,
pen.bnft_ordr_num bnft_ordr_num,
plt.name plt_name,
pen.person_id person_id,
pen.bnft_amt Coverage,
epe.per_in_ler_id per_in_ler_id,
pen.sspndd_flag,
epe.crntly_enrd_flag,
epe.elctbl_flag,
pen.enrt_cvg_strt_dt ,
decode(pen.enrt_cvg_thru_dt,to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
ler.name le_name,
(select name from ben_pgm_f pgm
where p_effective_date between pgm.effective_start_date
and pgm.effective_end_date
and pgm.pgm_id=epe.pgm_id
and pgm.business_group_id = p_business_group_id) Program_Name,
epe.pgm_id pgm_id,
epe.ptip_id ptip_id,
epe.plip_id plip_id,
epe.oiplip_id oiplip_id,
epe.pl_id,
epe.oipl_id
FROM ben_elig_per_elctbl_chc epe,
ben_enrt_bnft beb,
ben_prtt_enrt_rslt_f pen,
ben_pl_typ_f plt,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen1,
ben_ler_f ler
WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
and ( p_effective_date) between pen1.effective_start_date (+)
and pen1.effective_end_date (+)
and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
-- Code Changes for bug 7689952 - Start
--and epe.per_in_ler_id = l_per_in_ler_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pen.enrt_cvg_thru_dt >= pen.effective_start_date
and pen.enrt_cvg_thru_dt >= p_effective_date
--Code Changes for bug 7689952 - End
and pen.person_id = p_person_id
and epe.business_group_id = p_business_group_id
and plt.business_group_id = p_business_group_id
and ler.business_group_id = p_business_group_id
AND ((epe.elctbl_flag = 'N' and
(nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
or epe.auto_enrt_flag = 'Y'))
or epe.elctbl_flag = 'Y')
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
AND epe.pl_typ_id = plt.pl_typ_id
AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
AND epe.business_group_id = beb.business_group_id(+)
AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
AND ( p_effective_date) BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND ( p_effective_date) BETWEEN plt.effective_start_date
AND plt.effective_end_date
AND ( p_effective_date) BETWEEN ler.effective_start_date
AND ler.effective_end_date
AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
and pen.ler_id=ler.ler_id
AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
AND pen.prtt_enrt_rslt_stat_cd is null
UNION
SELECT epe.ptip_ordr_num ptip_ordr_num,
epe.plip_ordr_num plip_ordr_num,
pen.pl_ordr_num pl_ordr_num,
epe.oipl_ordr_num oipl_ordr_num,
pen.bnft_ordr_num bnft_ordr_num,
plt.name plt_name,
pen.person_id person_id,
pen.bnft_amt Coverage,
epe.per_in_ler_id per_in_ler_id,
pen.sspndd_flag,
epe.crntly_enrd_flag,
epe.elctbl_flag,
pen.enrt_cvg_strt_dt ,
decode(pen.enrt_cvg_thru_dt,
to_date('31-12-4712','DD-MM-YYYY'),to_date(null),pen.enrt_cvg_thru_dt) enrt_cvg_thru_dt,
decode(pen1.prtt_enrt_rslt_id , null, 'N', 'Y') interim,
ler.name le_name,
(select name from ben_pgm_f pgm
where p_effective_date between pgm.effective_start_date
and pgm.effective_end_date
and pgm.pgm_id=epe.pgm_id
and pgm.business_group_id = p_business_group_id) Program_Name,
epe.pgm_id pgm_id,
epe.ptip_id ptip_id,
epe.plip_id plip_id,
epe.oiplip_id oiplip_id,
epe.pl_id,
epe.oipl_id
FROM ben_elig_per_elctbl_chc epe,
ben_enrt_bnft beb,
ben_prtt_enrt_rslt_f pen,
ben_pl_typ_f plt,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen1,
ben_ler_f ler
WHERE pen.prtt_enrt_rslt_id = pen1.rplcs_sspndd_rslt_id (+)
and (p_effective_date) between pen1.effective_start_date (+)
and pen1.effective_end_date (+)
and pen1.prtt_enrt_rslt_stat_cd (+) is NULL
and pen1.enrt_cvg_thru_dt (+) =to_date('31-12-4712','DD-MM-YYYY')
-- Code Changes for bug 7689952 - Start
-- and epe.per_in_ler_id = l_per_in_ler_id
and pen.enrt_cvg_thru_dt >= pen.effective_start_date
and pen.per_in_ler_id=epe.per_in_ler_id
and pen.enrt_cvg_thru_dt >= p_effective_date
-- Code Changes for bug 7689952 - End
and pen.person_id = p_person_id
and epe.business_group_id = p_business_group_id
and plt.business_group_id = p_business_group_id
and ler.business_group_id = p_business_group_id
AND ((epe.elctbl_flag = 'N' and
(nvl(beb.crntly_enrld_flag, epe.crntly_enrd_flag) = 'Y'
or epe.auto_enrt_flag = 'Y'))
or epe.elctbl_flag = 'Y')
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
AND epe.pl_typ_id = plt.pl_typ_id
AND decode(beb.enrt_bnft_id, null, epe.prtt_enrt_rslt_id, beb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
AND epe.business_group_id = beb.business_group_id(+)
AND epe.elig_per_elctbl_chc_id = beb.elig_per_elctbl_chc_id (+)
AND (p_effective_date) BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND (p_effective_date) BETWEEN plt.effective_start_date
AND plt.effective_end_date
AND ( p_effective_date) BETWEEN ler.effective_start_date
AND ler.effective_end_date
AND pen.enrt_cvg_thru_dt = to_date('31-12-4712','DD-MM-YYYY')
and pen.ler_id=ler.ler_id
AND epe.comp_lvl_cd NOT IN ('PLANFC' ,'PLANIMP')
AND pen.prtt_enrt_rslt_stat_cd is null
) elc,
ben_pl_f pln,
ben_oipl_f oipl,
ben_opt_f opt,
ben_ptip_f ptip,
ben_plip_f plip
where elc.pl_id = pln.pl_id
AND elc.ptip_id = ptip.ptip_id
AND elc.plip_id = plip.plip_id
AND elc.oipl_id = oipl.oipl_id(+)
AND oipl.opt_id = opt.opt_id(+)
and pln.business_group_id = p_business_group_id
and oipl.business_group_id = p_business_group_id
and opt.business_group_id = p_business_group_id
and ptip.business_group_id = p_business_group_id
and plip.business_group_id = p_business_group_id
AND ( p_effective_date) BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND ( p_effective_date) BETWEEN oipl.effective_start_date(+)
AND oipl.effective_end_date(+)
AND ( p_effective_date) BETWEEN opt.effective_start_date(+)
AND opt.effective_end_date(+)
AND ( p_effective_date) BETWEEN ptip.effective_start_date
AND ptip.effective_end_date
AND ( p_effective_date) BETWEEN plip.effective_start_date
AND plip.effective_end_date
ORDER BY elc.ptip_ordr_num,
elc.plip_ordr_num,
elc.pl_ordr_num,
elc.oipl_ordr_num,
elc.bnft_ordr_num
);
select to_char(org_information9) from
hr_organization_information where organization_id = p_bus_grp_id
and org_information_context = 'Business Group Information';
select distinct
ppa.payroll_action_id
,paa.assignment_id
,paa.assignment_action_id
, paf.location_id
from pay_payroll_actions ppa
,pay_assignment_actions paa
,per_assignments_f paf
,per_people_f ppf
,hr_lookups hl
where ppa.action_type = 'X'
and ppa.action_status = 'C'
and ppa.report_type = hl.meaning
and hl.lookup_type = 'PAYSLIP_REPORT_TYPES'
and hl.lookup_code = 'US'
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paf.person_id = ppf.person_id
and ppf.person_id = p_person_id
and ppa.effective_date = (select max(ppa1.effective_date)
from pay_payroll_actions ppa1
,pay_assignment_actions paa1
,hr_lookups hl1
where ppa1.effective_date <= p_eff_date
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.report_type = hl1.meaning
and hl1.lookup_type = 'PAYSLIP_REPORT_TYPES'
and hl1.lookup_code = 'US'
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.assignment_id = paa.assignment_id
and ppa1.business_group_id = ppa.business_group_id);
select region_2,location_code,
(select state_name from pay_us_states where state_abbrev = region_2)
from hr_locations_all
where location_id = p_location_id;
SELECT
organization_name
,job
,to_char(payment_date,'YYYY-MM-DD')
,Period_type
,location_name
,employee_address1 || employee_address2 || employee_address3 || ' ' || employee_city || ' ' || employee_state || ' ' || employee_zip_code
,payroll_name
,'USD'
,to_char(ending_date,'YYYY-MM-DD')
,'Federal'
,(select status from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and tax_jurisdiction = 'Federal'
and trunc(effective_date) <= p_eff_date) STATUS
,(select exemptions from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = 'Federal') EXEMPTIONS
,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = 'Federal') ADDNL_TAX_AMOUNT
,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = 'Federal') OVERRIDE_TAX_AMOUNT
,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = 'Federal') OVERRIDE_TAX_PERCENTAGE
,p_state_code
,(select exemptions from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = p_state_desc) STEXEMPTIONS
,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = p_state_desc) STADDNL_TAX_AMOUNT
,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_AMOUNT
,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_PERCENTAGE
,(select gross_earnings
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY CURRENT') TOTAL_EARNINGS_CV
,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY CURRENT') TAXABLE_GROSS_CV
,(select taxes
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY CURRENT') TOTAL_TAXES_CV
,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY CURRENT') TOTAL_DEDUCTIONS_CV
,(select net_pay
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY CURRENT') NET_PAY_CV
,(select gross_earnings
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY YTD') TOTAL_EARNINGS_YTD
,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY YTD') TAXABLE_GROSS_YTD
,(select taxes
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY YTD') TOTAL_TAXES_YTD
,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY YTD') TOTAL_DEDUCTIONS_YTD
,(select net_pay
from pay_ac_emp_sum_action_info_v
where action_context_id = p_asg_action_id
and action_information_category = 'AC SUMMARY YTD') NET_PAY_YTD
from pay_employee_action_info_v peai
where action_context_id = p_asg_action_id
and assignment_id = p_asg_id
and trunc(effective_date) <= p_eff_date
and location_name = p_loc_name;
select to_char(action_context_id) assignment_action_id
from pay_emp_payslip_action_info_v
where person_id = p_person_id
and effective_date = (select max(effective_date)
from pay_emp_payslip_action_info_v
where person_id = p_person_id
and effective_date <= p_eff_date);
select organization_name ,
location_name ,
job ,
payroll_name ,
to_char(payment_date,'YYYY-MM-DD'),
pbg.currency_code ,
to_char(beginning_date,'YYYY-MM-DD') ,
to_char(ending_date,'YYYY-MM-DD'),
paa.assignment_id
from pay_employee_action_info_v empv,
pay_assignment_actions paa,
per_business_groups pbg
where empv.action_context_id = p_asg_action_id
and empv.action_context_id = paa.assignment_action_id
and paa.assignment_id = nvl(empv.assignment_id,paa.assignment_id)
and pbg.name = organization_name ;
select prtf.run_type_name
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_types_f prtf
where paa.assignment_action_id in (SELECT /*+ USE_NL(paa, pact, ptp) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions pact
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status = 'C'
AND pact.effective_date <= p_eff_date)
and ppa.payroll_action_id = paa.payroll_action_id
and prtf.run_type_id = ppa.run_type_id
and prtf.legislation_code = 'GB';
SELECT /*+ leading(lck,paa2) */
--pai.action_information4 NARRATIVE,
SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
FROM pay_action_interlocks lck, -- archive action locking prepayment
pay_assignment_actions paa1, -- prepayment action
pay_assignment_actions paa2, -- archive action
pay_payroll_actions ppa, -- prepayment
pay_action_information pai, -- archived element/input value definition
pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
pay_assignment_actions paa, -- payroll run/quickpay action
pay_payroll_actions ppa1, -- payroll run/quickpay action
pay_element_types_f pet, -- element types processed by the payroll run/quickpay
pay_input_values_f piv, -- "Pay values" of type Money
pay_run_results prr, -- run result created by the payroll run/quick pay
pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
WHERE lck.locking_action_id = paa2.assignment_action_id
AND paa2.payroll_action_id = pai.action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND lck.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id IS NULL
AND paa1.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('P','U')
AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
AND paa1.assignment_action_id = pac.locking_action_id
AND pet.element_type_id = pai.action_information2
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pai.action_information3
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P','PA')
AND prv.input_value_id = piv.input_value_id
AND prv.run_result_id = prr.run_result_id
AND piv.name = 'Pay Value'
AND piv.uom = 'M'
AND pac.locked_action_id = prr.assignment_action_id
AND pac.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa1.payroll_action_id
AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND lck.locking_action_id = p_assignment_action_id
AND pai.action_information5 in ( 'E','P')
GROUP BY lck.locking_action_id;
SELECT /*+ leading(lck,paa2) */
SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
FROM pay_action_interlocks lck, -- archive action locking prepayment
pay_assignment_actions paa1, -- prepayment action
pay_assignment_actions paa2, -- archive action
pay_payroll_actions ppa, -- prepayment
pay_action_information pai, -- archived element/input value definition
pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
pay_assignment_actions paa, -- payroll run/quickpay action
pay_payroll_actions ppa1, -- payroll run/quickpay action
pay_element_types_f pet, -- element types processed by the payroll run/quickpay
pay_input_values_f piv, -- "Pay values" of type Money
pay_run_results prr, -- run result created by the payroll run/quick pay
pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
WHERE lck.locking_action_id = paa2.assignment_action_id
AND paa2.payroll_action_id = pai.action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND lck.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id IS NULL
AND paa1.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('P','U')
AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
AND paa1.assignment_action_id = pac.locking_action_id
AND pet.element_type_id = pai.action_information2
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pai.action_information3
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P','PA')
AND prv.input_value_id = piv.input_value_id
AND prv.run_result_id = prr.run_result_id
AND piv.name = 'Pay Value'
AND piv.uom = 'M'
AND pac.locked_action_id = prr.assignment_action_id
AND pac.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa1.payroll_action_id
AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND lck.locking_action_id = p_assignment_action_id
AND pai.action_information5 in ('D', NULL)
AND pai.action_information4 = ('PAYE')
GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
SELECT /*+ leading(lck,paa2) */
SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value)) value
FROM pay_action_interlocks lck, -- archive action locking prepayment
pay_assignment_actions paa1, -- prepayment action
pay_assignment_actions paa2, -- archive action
pay_payroll_actions ppa, -- prepayment
pay_action_information pai, -- archived element/input value definition
pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
pay_assignment_actions paa, -- payroll run/quickpay action
pay_payroll_actions ppa1, -- payroll run/quickpay action
pay_element_types_f pet, -- element types processed by the payroll run/quickpay
pay_input_values_f piv, -- "Pay values" of type Money
pay_run_results prr, -- run result created by the payroll run/quick pay
pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
WHERE lck.locking_action_id = paa2.assignment_action_id
AND paa2.payroll_action_id = pai.action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND lck.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id IS NULL
AND paa1.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('P','U')
AND ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id)
AND paa1.assignment_action_id = pac.locking_action_id
AND pet.element_type_id = pai.action_information2
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pai.action_information3
AND prr.element_type_id = pet.element_type_id
AND prr.status IN ('P','PA')
AND prv.input_value_id = piv.input_value_id
AND prv.run_result_id = prr.run_result_id
AND piv.name = 'Pay Value'
AND piv.uom = 'M'
AND pac.locked_action_id = prr.assignment_action_id
AND pac.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa1.payroll_action_id
AND ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND lck.locking_action_id = p_assignment_action_id
AND pai.action_information5 in ('D', NULL)
AND pai.action_information4 like 'NI%'
GROUP BY lck.locking_action_id, pet.element_type_id, piv.input_value_id, pai.action_information4, pai.action_information5;
select ACTION_INFORMATION16
from pay_action_information pai
where pai.action_context_id = p_asg_action_id
and pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
and pai.action_context_type = 'AAP';
select pai.ACTION_INFORMATION4
from pay_action_information pai,
pay_defined_balances pdb,
pay_balance_types pbt
where pai.action_context_id = p_assignment_action_id -- 182069
and to_char(pdb.DEFINED_BALANCE_ID) = (pai.ACTION_INFORMATION1)
and pdb.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
and pai.action_information_category = 'EMEA BALANCES'
and pai.action_context_type = 'AAP'
and balance_name = p_bal_name
and pbt.legislation_code = 'GB'
and pdb.legislation_code = 'GB';
select defined_balance_id
from pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pbt.balance_name = 'NI '||(select ACTION_INFORMATION23
from pay_action_information pai
where pai.action_context_id = p_assignment_action_id --182069
and pai.action_information_category = 'GB EMPLOYEE DETAILS'
and pai.action_context_type = 'AAP')
||' Employee'
and pbd.dimension_name = '_ASG_TD_YTD'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.legislation_code='GB'
and pbt.legislation_code='GB';
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)) assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions pact
WHERE paa.assignment_id = p_assignment_id --16986
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.action_type IN ('Q','R','B','I','V')
AND paa.action_status = 'C'
AND pact.effective_date <= p_payment_date;
select defined_balance_id
from pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pbt.balance_name = 'Net Pay'
and pbd.dimension_name = '_ASG_TD_YTD'
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.legislation_code='GB'
and pbt.legislation_code='GB';
select business_group_id into p_bus_group_id
from hr_organization_units org
where upper(name) = upper(p_srch_criteria(i).p_bgrp_name)
and org.organization_id = org.business_group_id
and p_srch_criteria(i).p_effective_date between date_from and
nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
select LOOKUP_CODE into p_empl_category
from HR_LOOKUPS where lookup_type = 'EMPLOYEE_CATG'
AND meaning = p_srch_criteria(i).p_employee_category
AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
and enabled_flag = 'Y';
select LOOKUP_CODE into p_emplmt_category
from HR_LOOKUPS where lookup_type = 'EMP_CAT'
AND meaning = p_srch_criteria(i).p_employment_category
AND p_srch_criteria(i).p_effective_date between nvl(start_date_active,p_srch_criteria(i).p_effective_date)
and nvl(end_date_active,p_srch_criteria(i).p_effective_date)
and enabled_flag = 'Y';
select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
from per_people_f ppf
,per_person_types ppt
,per_phones pp
where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = pp.parent_id(+)
and pp.parent_table = 'PER_ALL_PEOPLE_F'
and pp.phone_id = p_srch_criteria(i).p_phone_id;
select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
from per_people_f ppf
,per_person_types ppt
,per_qualifications pq
,per_establishment_attendances pea
where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and pq.attendance_id = pea.attendance_id(+)
and (pq.person_id = ppf.person_id or pea.person_id = ppf.person_id)
and pq.qualification_id = p_srch_criteria(i).p_qualification_id;
select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
from per_people_f ppf
,per_person_types ppt
,per_person_dlvry_methods ppdm
where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppdm.person_id = ppf.person_id
and ppdm.delivery_method_id = p_srch_criteria(i).p_delivery_method_id;
select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
from per_people_f ppf
,per_person_types ppt
,per_addresses pa
where ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and pa.person_id = ppf.person_id
and pa.address_id = p_srch_criteria(i).p_address_id;
select distinct ppf.person_id ,ppf.business_group_id,p_srch_criteria(i).p_effective_date
from per_people_f ppf ,per_person_types ppt ,per_assignments_f paaf
where nvl(p_srch_criteria(i).p_person_id, ppf.person_id) = ppf.person_id
and ppf.person_id between nvl(p_srch_criteria(i).p_start_person_id, ppf.person_id)
and nvl(p_srch_criteria(i).p_end_person_id, ppf.person_id)
and ppf.business_group_id = nvl(p_bus_group_id,ppf.business_group_id)
and last_name = nvl(p_srch_criteria(i).p_last_name,last_name)
and nvl(first_name,'*') = nvl(nvl(p_srch_criteria(i).p_first_name,first_name),'*')
and nvl(employee_number,'*') = nvl(nvl(p_srch_criteria(i).p_employee_no,employee_number),'*')
and nvl(npw_number,'*') = nvl(nvl(p_srch_criteria(i).p_cwk_no,npw_number),'*')
and nvl(applicant_number,'*') = nvl(nvl(p_srch_criteria(i).p_applicant_no,applicant_number),'*')
and p_srch_criteria(i).p_effective_date between ppf.effective_start_date and ppf.effective_end_date
and nvl(ppt.user_person_type ,'*') = nvl(nvl(p_srch_criteria(i).p_person_type,ppt.user_person_type),'*')
and ppf.person_type_id = ppt.person_type_id
and ppf.business_group_id = ppt.business_group_id
and ppf.person_id = paaf.person_id (+)
and nvl(paaf.assignment_id, -1 ) = nvl(nvl(p_srch_criteria(i).p_assignment_id,paaf.assignment_id),-1)
and p_srch_criteria(i).p_effective_date between paaf.effective_start_date (+) and paaf.effective_end_date (+)
and nvl(employment_category,'*') = nvl(nvl(p_emplmt_category,paaf.employment_category ),'*')
and nvl(employee_category,'*') = nvl(nvl(p_empl_category,paaf.employee_category ),'*');
SELECT ACCRUAL_DETAILS.ACCRUAL_PLAN_NAME,L_ENTITLEMENT,ACCRUAL_DETAILS.ACCRUAL_UNITS_OF_MEASURE_NAME,
TO_CHAR(p_srch_filter_tbl.p_srch_dt(j),'YYYY-MM-DD')
INTO p_person(j).ABSENCE(P_CNT) FROM DUAL;
select pay_balance_pkg.get_value(p_netpay_bal_id,p_latest_action_id)
into p_person(j).payroll(p_cnt).TOTAL_NETPAY_YTD
from dual ;
select pay_balance_pkg.get_value(p_def_bal_id,p_latest_action_id)
into p_person(j).payroll(p_cnt).NI_YTD
from dual;