VIEW: APPS.BEN_PL_EXTRACT_IDENTIFIER_D

Source

View Text - Preformatted

SELECT pei.pl_extract_identifier_id, pei.effective_start_date, pei.effective_end_date, pln3.NAME, DECODE (pei.plip_id, NULL, pln.NAME, pgm.NAME || ' - ' || pln.NAME), DECODE (pei.oipl_id, NULL, NULL, pln2.NAME || ' -' || opt.NAME), pei.third_party_identifier, hr_general.decode_organization (pei.organization_id), jbt.NAME, hr_general.decode_position_latest_name (pei.position_id) POSITION, grp.CONCATENATED_SEGMENTS, grt.NAME grade_name, payroll.payroll_name, fnd.meaning, pei.home_zip, pei.last_update_date, fuser.user_name FROM ben_pl_extract_identifier_f pei, per_grades_tl grt, per_jobs_tl jbt, PAY_PEOPLE_GROUPS_KFV grp, pay_all_payrolls_f payroll, hr_lookups fnd, ben_pl_f pln, ben_pgm_f pgm, ben_plip_f plip, ben_oipl_f oipl, ben_opt_f opt, ben_pl_f pln2, ben_pl_f pln3, fnd_user fuser WHERE GRT.GRADE_ID(+) = PEI.GRADE_ID AND GRT.LANGUAGE(+) = USERENV ('LANG') AND JBT.JOB_ID(+) = PEI.JOB_ID AND JBT.LANGUAGE(+) = USERENV ('LANG') AND GRP.PEOPLE_GROUP_ID(+) = PEI.PEOPLE_GROUP_ID AND PAYROLL.PAYROLL_ID(+) = PEI.PAYROLL_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PAYROLL.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (PAYROLL.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE ) AND FND.LOOKUP_CODE(+) = PEI.HOME_STATE AND FND.LOOKUP_TYPE(+) = 'US_STATE' AND PLN3.PL_ID(+) = PEI.PL_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN3.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (PLN3.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE) AND PLIP.PLIP_ID(+) = PEI.PLIP_ID AND PLN.PL_ID(+) = PLIP.PL_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (PLN.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE) AND PGM.PGM_ID(+) = PLIP.PGM_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PGM.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (PGM.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE) AND OIPL.OIPL_ID(+) = PEI.OIPL_ID AND OPT.OPT_ID(+) = OIPL.OPT_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (OPT.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (OPT.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE) AND PLN2.PL_ID(+) = OIPL.PL_ID AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN2.EFFECTIVE_START_DATE,PEI.EFFECTIVE_START_DATE) AND NVL (PLN2.EFFECTIVE_END_DATE,PEI.EFFECTIVE_START_DATE) AND FUSER.USER_ID(+) = PEI.LAST_UPDATED_BY

View Text - HTML Formatted

SELECT PEI.PL_EXTRACT_IDENTIFIER_ID
, PEI.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_END_DATE
, PLN3.NAME
, DECODE (PEI.PLIP_ID
, NULL
, PLN.NAME
, PGM.NAME || ' - ' || PLN.NAME)
, DECODE (PEI.OIPL_ID
, NULL
, NULL
, PLN2.NAME || ' -' || OPT.NAME)
, PEI.THIRD_PARTY_IDENTIFIER
, HR_GENERAL.DECODE_ORGANIZATION (PEI.ORGANIZATION_ID)
, JBT.NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME (PEI.POSITION_ID) POSITION
, GRP.CONCATENATED_SEGMENTS
, GRT.NAME GRADE_NAME
, PAYROLL.PAYROLL_NAME
, FND.MEANING
, PEI.HOME_ZIP
, PEI.LAST_UPDATE_DATE
, FUSER.USER_NAME
FROM BEN_PL_EXTRACT_IDENTIFIER_F PEI
, PER_GRADES_TL GRT
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS_KFV GRP
, PAY_ALL_PAYROLLS_F PAYROLL
, HR_LOOKUPS FND
, BEN_PL_F PLN
, BEN_PGM_F PGM
, BEN_PLIP_F PLIP
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PLN2
, BEN_PL_F PLN3
, FND_USER FUSER
WHERE GRT.GRADE_ID(+) = PEI.GRADE_ID
AND GRT.LANGUAGE(+) = USERENV ('LANG')
AND JBT.JOB_ID(+) = PEI.JOB_ID
AND JBT.LANGUAGE(+) = USERENV ('LANG')
AND GRP.PEOPLE_GROUP_ID(+) = PEI.PEOPLE_GROUP_ID
AND PAYROLL.PAYROLL_ID(+) = PEI.PAYROLL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PAYROLL.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PAYROLL.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE )
AND FND.LOOKUP_CODE(+) = PEI.HOME_STATE
AND FND.LOOKUP_TYPE(+) = 'US_STATE'
AND PLN3.PL_ID(+) = PEI.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN3.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PLN3.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PLIP.PLIP_ID(+) = PEI.PLIP_ID
AND PLN.PL_ID(+) = PLIP.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PLN.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PGM.PGM_ID(+) = PLIP.PGM_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PGM.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PGM.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND OIPL.OIPL_ID(+) = PEI.OIPL_ID
AND OPT.OPT_ID(+) = OIPL.OPT_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (OPT.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (OPT.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PLN2.PL_ID(+) = OIPL.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PLN2.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PLN2.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND FUSER.USER_ID(+) = PEI.LAST_UPDATED_BY