VIEW: APPS.HXT_EMP_ASGN_INFO_V

Source

View Text - Preformatted

SELECT pa.assignment_id, pa.assignment_number, pa.effective_start_date, pa.effective_end_date, pa.business_group_id, bg.name business_group_name, per.full_name, per.last_name, per.first_name, per.employee_number, pay.payroll_name, ot.name organization_name, pafv.hxt_autogen_hours_yn, ep.name earning_pol_name, rp.name rotation_plan_name, sd.name shift_diff_pol_name, hdp.name hour_deduct_pol_name, egt.name earn_group_type_name, pip.name prem_interact_pol_name, pep.name prem_eligblty_pol_name, hcl.name holiday_calendar_name from per_assignments_f pa, hxt_per_aei_ddf_v pafv, per_business_groups bg, per_all_people_f per, pay_all_payrolls_f pay, hr_all_organization_units o, hr_all_organization_units_tl ot, hxt_earning_policies ep, hxt_rotation_plans rp, hxt_shift_diff_policies sd, hxt_hour_deduct_policies hdp, hxt_earn_group_types egt, hxt_prem_interact_policies pip, hxt_prem_eligblty_policies pep, hxt_holiday_calendars hcl, fnd_sessions f WHERE pa.assignment_type = 'E' and pa.assignment_id = pafv.assignment_id and f.effective_date between pafv.effective_start_date and pafv.effective_end_date and pa.business_group_id = bg.business_group_id(+) and pa.person_id = per.person_id(+) and pa.payroll_id = pay.payroll_id(+) and pa.organization_id = o.organization_id and ot.organization_id = o.organization_id and ot.language = userenv('LANG') and pafv.hxt_earning_policy = ep.id(+) and pafv.hxt_rotation_plan = rp.id(+) and pafv.hxt_shift_differential_policy = sd.id(+) and pafv.hxt_hour_deduction_policy = hdp.id(+) and ep.egt_id = egt.id(+) and ep.pip_id = pip.id(+) and ep.pep_id = pep.id(+) and ep.hcl_id = hcl.id(+) and f.session_id = userenv('SESSIONID') and f.effective_date between nvl(pa.effective_start_date, f.effective_date) and nvl(pa.effective_end_date, f.effective_date) and f.effective_date between nvl(per.effective_start_date, f.effective_date) and nvl(per.effective_end_date, f.effective_date) and f.effective_date between nvl(pay.effective_start_date, f.effective_date) and nvl(pay.effective_end_date, f.effective_date) and f.effective_date between nvl(ep.effective_start_date, f.effective_date) and nvl(ep.effective_end_date, f.effective_date) and f.effective_date between nvl(rp.date_from, f.effective_date) and nvl(rp.date_to, f.effective_date) and f.effective_date between nvl(sd.date_from, f.effective_date) and nvl(sd.date_to, f.effective_date) and f.effective_date between nvl(hdp.date_from, f.effective_date) and nvl(hdp.date_to, f.effective_date) and f.effective_date between nvl(egt.effective_start_date, f.effective_date) and nvl(egt.effective_end_date, f.effective_date) and f.effective_date between nvl(pip.date_from, f.effective_date) and nvl(pip.date_to, f.effective_date) and f.effective_date between nvl(pep.date_from, f.effective_date) and nvl(pep.date_to, f.effective_date) and f.effective_date between nvl(hcl.effective_start_date, f.effective_date) and nvl(hcl.effective_end_date, f.effective_date)

View Text - HTML Formatted

SELECT PA.ASSIGNMENT_ID
, PA.ASSIGNMENT_NUMBER
, PA.EFFECTIVE_START_DATE
, PA.EFFECTIVE_END_DATE
, PA.BUSINESS_GROUP_ID
, BG.NAME BUSINESS_GROUP_NAME
, PER.FULL_NAME
, PER.LAST_NAME
, PER.FIRST_NAME
, PER.EMPLOYEE_NUMBER
, PAY.PAYROLL_NAME
, OT.NAME ORGANIZATION_NAME
, PAFV.HXT_AUTOGEN_HOURS_YN
, EP.NAME EARNING_POL_NAME
, RP.NAME ROTATION_PLAN_NAME
, SD.NAME SHIFT_DIFF_POL_NAME
, HDP.NAME HOUR_DEDUCT_POL_NAME
, EGT.NAME EARN_GROUP_TYPE_NAME
, PIP.NAME PREM_INTERACT_POL_NAME
, PEP.NAME PREM_ELIGBLTY_POL_NAME
, HCL.NAME HOLIDAY_CALENDAR_NAME
FROM PER_ASSIGNMENTS_F PA
, HXT_PER_AEI_DDF_V PAFV
, PER_BUSINESS_GROUPS BG
, PER_ALL_PEOPLE_F PER
, PAY_ALL_PAYROLLS_F PAY
, HR_ALL_ORGANIZATION_UNITS O
, HR_ALL_ORGANIZATION_UNITS_TL OT
, HXT_EARNING_POLICIES EP
, HXT_ROTATION_PLANS RP
, HXT_SHIFT_DIFF_POLICIES SD
, HXT_HOUR_DEDUCT_POLICIES HDP
, HXT_EARN_GROUP_TYPES EGT
, HXT_PREM_INTERACT_POLICIES PIP
, HXT_PREM_ELIGBLTY_POLICIES PEP
, HXT_HOLIDAY_CALENDARS HCL
, FND_SESSIONS F
WHERE PA.ASSIGNMENT_TYPE = 'E'
AND PA.ASSIGNMENT_ID = PAFV.ASSIGNMENT_ID
AND F.EFFECTIVE_DATE BETWEEN PAFV.EFFECTIVE_START_DATE
AND PAFV.EFFECTIVE_END_DATE
AND PA.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID(+)
AND PA.PERSON_ID = PER.PERSON_ID(+)
AND PA.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND PA.ORGANIZATION_ID = O.ORGANIZATION_ID
AND OT.ORGANIZATION_ID = O.ORGANIZATION_ID
AND OT.LANGUAGE = USERENV('LANG')
AND PAFV.HXT_EARNING_POLICY = EP.ID(+)
AND PAFV.HXT_ROTATION_PLAN = RP.ID(+)
AND PAFV.HXT_SHIFT_DIFFERENTIAL_POLICY = SD.ID(+)
AND PAFV.HXT_HOUR_DEDUCTION_POLICY = HDP.ID(+)
AND EP.EGT_ID = EGT.ID(+)
AND EP.PIP_ID = PIP.ID(+)
AND EP.PEP_ID = PEP.ID(+)
AND EP.HCL_ID = HCL.ID(+)
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN NVL(PA.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PA.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(PER.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PER.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(PAY.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(PAY.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(EP.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(EP.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(RP.DATE_FROM
, F.EFFECTIVE_DATE)
AND NVL(RP.DATE_TO
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(SD.DATE_FROM
, F.EFFECTIVE_DATE)
AND NVL(SD.DATE_TO
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(HDP.DATE_FROM
, F.EFFECTIVE_DATE)
AND NVL(HDP.DATE_TO
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(EGT.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(EGT.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(PIP.DATE_FROM
, F.EFFECTIVE_DATE)
AND NVL(PIP.DATE_TO
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(PEP.DATE_FROM
, F.EFFECTIVE_DATE)
AND NVL(PEP.DATE_TO
, F.EFFECTIVE_DATE)
AND F.EFFECTIVE_DATE BETWEEN NVL(HCL.EFFECTIVE_START_DATE
, F.EFFECTIVE_DATE)
AND NVL(HCL.EFFECTIVE_END_DATE
, F.EFFECTIVE_DATE)