VIEW: APPS.OTA_TRAINING_PLANS_V

Source

View Text - Preformatted

SELECT tps.ROWID , tps.TRAINING_PLAN_ID , decode(tps.organization_id, null, 'PERSON', 'ORGANIZATION') , tps.TIME_PERIOD_ID , cal.PERIOD_SET_NAME , ptp.PERIOD_NAME , ptp.START_DATE , ptp.END_DATE , tps.PLAN_STATUS_TYPE_ID , lkp.meaning , tps.ORGANIZATION_ID , org.NAME , tps.PERSON_ID , peo.FULL_NAME , peo.EMPLOYEE_NUMBER , tps.BUDGET_CURRENCY , tps.NAME , tps.DESCRIPTION , tps.BUSINESS_GROUP_ID , tps.OBJECT_VERSION_NUMBER , tps.ATTRIBUTE_CATEGORY , tps.ATTRIBUTE1 , tps.ATTRIBUTE2 , tps.ATTRIBUTE3 , tps.ATTRIBUTE4 , tps.ATTRIBUTE5 , tps.ATTRIBUTE6 , tps.ATTRIBUTE7 , tps.ATTRIBUTE8 , tps.ATTRIBUTE9 , tps.ATTRIBUTE10 , tps.ATTRIBUTE11 , tps.ATTRIBUTE12 , tps.ATTRIBUTE13 , tps.ATTRIBUTE14 , tps.ATTRIBUTE15 , tps.ATTRIBUTE16 , tps.ATTRIBUTE17 , tps.ATTRIBUTE18 , tps.ATTRIBUTE19 , tps.ATTRIBUTE20 , tps.ATTRIBUTE21 , tps.ATTRIBUTE22 , tps.ATTRIBUTE23 , tps.ATTRIBUTE24 , tps.ATTRIBUTE25 , tps.ATTRIBUTE26 , tps.ATTRIBUTE27 , tps.ATTRIBUTE28 , tps.ATTRIBUTE29 , tps.ATTRIBUTE30 , tps.CREATED_BY , tps.CREATION_DATE , tps.LAST_UPDATED_BY , tps.LAST_UPDATE_DATE , tps.LAST_UPDATE_LOGIN from fnd_sessions ses , per_all_people_f peo , pay_calendars cal , per_time_periods ptp , fnd_lookup_values lkp , hr_all_organization_units org , ota_training_plans tps WHERE lkp.language = userenv('LANG') and lkp.view_application_id = 3 and tps.plan_status_type_id = lkp.lookup_code and lkp.lookup_type = 'OTA_PLAN_USER_STATUS_TYPE' and tps.time_period_id = ptp.time_period_id and ptp.period_set_name = cal.period_set_name and tps.organization_id = org.organization_id(+) and ses.session_id = userenv('SESSIONID') and tps.person_id = peo.person_id(+) and ses.effective_date >= nvl(peo.effective_start_date,ses.effective_date) and ses.effective_date <= nvl(peo.effective_end_date,ses.effective_date)

View Text - HTML Formatted

SELECT TPS.ROWID
, TPS.TRAINING_PLAN_ID
, DECODE(TPS.ORGANIZATION_ID
, NULL
, 'PERSON'
, 'ORGANIZATION')
, TPS.TIME_PERIOD_ID
, CAL.PERIOD_SET_NAME
, PTP.PERIOD_NAME
, PTP.START_DATE
, PTP.END_DATE
, TPS.PLAN_STATUS_TYPE_ID
, LKP.MEANING
, TPS.ORGANIZATION_ID
, ORG.NAME
, TPS.PERSON_ID
, PEO.FULL_NAME
, PEO.EMPLOYEE_NUMBER
, TPS.BUDGET_CURRENCY
, TPS.NAME
, TPS.DESCRIPTION
, TPS.BUSINESS_GROUP_ID
, TPS.OBJECT_VERSION_NUMBER
, TPS.ATTRIBUTE_CATEGORY
, TPS.ATTRIBUTE1
, TPS.ATTRIBUTE2
, TPS.ATTRIBUTE3
, TPS.ATTRIBUTE4
, TPS.ATTRIBUTE5
, TPS.ATTRIBUTE6
, TPS.ATTRIBUTE7
, TPS.ATTRIBUTE8
, TPS.ATTRIBUTE9
, TPS.ATTRIBUTE10
, TPS.ATTRIBUTE11
, TPS.ATTRIBUTE12
, TPS.ATTRIBUTE13
, TPS.ATTRIBUTE14
, TPS.ATTRIBUTE15
, TPS.ATTRIBUTE16
, TPS.ATTRIBUTE17
, TPS.ATTRIBUTE18
, TPS.ATTRIBUTE19
, TPS.ATTRIBUTE20
, TPS.ATTRIBUTE21
, TPS.ATTRIBUTE22
, TPS.ATTRIBUTE23
, TPS.ATTRIBUTE24
, TPS.ATTRIBUTE25
, TPS.ATTRIBUTE26
, TPS.ATTRIBUTE27
, TPS.ATTRIBUTE28
, TPS.ATTRIBUTE29
, TPS.ATTRIBUTE30
, TPS.CREATED_BY
, TPS.CREATION_DATE
, TPS.LAST_UPDATED_BY
, TPS.LAST_UPDATE_DATE
, TPS.LAST_UPDATE_LOGIN
FROM FND_SESSIONS SES
, PER_ALL_PEOPLE_F PEO
, PAY_CALENDARS CAL
, PER_TIME_PERIODS PTP
, FND_LOOKUP_VALUES LKP
, HR_ALL_ORGANIZATION_UNITS ORG
, OTA_TRAINING_PLANS TPS
WHERE LKP.LANGUAGE = USERENV('LANG')
AND LKP.VIEW_APPLICATION_ID = 3
AND TPS.PLAN_STATUS_TYPE_ID = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'OTA_PLAN_USER_STATUS_TYPE'
AND TPS.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PTP.PERIOD_SET_NAME = CAL.PERIOD_SET_NAME
AND TPS.ORGANIZATION_ID = ORG.ORGANIZATION_ID(+)
AND SES.SESSION_ID = USERENV('SESSIONID')
AND TPS.PERSON_ID = PEO.PERSON_ID(+)
AND SES.EFFECTIVE_DATE >= NVL(PEO.EFFECTIVE_START_DATE
, SES.EFFECTIVE_DATE)
AND SES.EFFECTIVE_DATE <= NVL(PEO.EFFECTIVE_END_DATE
, SES.EFFECTIVE_DATE)