Product: | OTA - Learning Management |
---|---|
Description: | Business view template from which the flexfield view is generated. |
Implementation/DBA Data: | APPS.OTFV_RESOURCE_CHECKLIST |
SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, DECODE(TSR.SUPPLIED_RESOURCE_ID
, NULL
, TRU.RESOURCE_TYPE
, TSR.RESOURCE_TYPE)) RESOURCE_TYPE
, TSR.NAME RESOURCE_NAME
, TRU.QUANTITY RESOURCE_QUANTITY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TRU.REQUIRED_FLAG) RESOURCE_REQUIRED
, DECODE(TRU.USAGE_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_USAGE_REASON'
, TRU.USAGE_REASON)) RESOURCE_USAGE
, DECODE(TRU.ROLE_TO_PLAY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINER_PARTICIPATION'
, TRU.ROLE_TO_PLAY)) RESOURCE_ROLE
, TRU.START_DATE RESOURCE_START_DATE
, TRU.END_DATE RESOURCE_END_DATE
, TRU.COMMENTS RESOURCE_USAGE_COMMENTS /* FOREIGN KEY RESOLUTION */
, ORG.ORGANIZATION_NAME INVENTORY_ORGANIZATION
, '_KF:INV:MSTK:INV'
, TAV.VERSION_NAME ACTIVITY_NAME
, TAV.DESCRIPTION ACTIVITY_DESCRIPTION
, TAV.INTENDED_AUDIENCE ACTIVITY_AUDIENCE
, TAV.OBJECTIVES ACTIVITY_OBJECTIVES
, FNDT.DESCRIPTION ACTIVITY_LANGUAGE
, TAV2.VERSION_NAME NEXT_ACTIVITY
, TAV.START_DATE ACTIVITY_FROM
, TAV.END_DATE ACTIVITY_TO
, ORGT.NAME SPONSOR_BY
, PER.FULL_NAME MANAGED_BY
, DECODE(TAV.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_USER_STATUS'
, TAV.USER_STATUS)) ACTIVITY_STATUS
, DECODE(TAV.SUCCESS_CRITERIA
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_SUCCESS_CRITERIA'
, TAV.SUCCESS_CRITERIA)) SUCCESS_CRITERIA
, DECODE(TAV.EXPENSES_ALLOWED
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TAV.EXPENSES_ALLOWED)) ALLOW_EXPENSES
, TAV.MINIMUM_ATTENDEES MINIMUM_STUDENTS
, TAV.MAXIMUM_ATTENDEES MAXIMUM_STUDENTS
, TAV.MAXIMUM_INTERNAL_ATTENDEES MAXIMUM_INTERNAL_STUDENTS
, OTA_GENERAL.FND_CURRENCY_NAME(TAV.BUDGET_CURRENCY_CODE) CURRENCY
, TAV.DURATION DURATION
, DECODE(TAV.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, TAV.DURATION_UNITS)) DURATION_UNITS
, DECODE(TAV.PROFESSIONAL_CREDIT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PROFESSIONAL_CREDIT_TYPE'
, TAV.PROFESSIONAL_CREDIT_TYPE)) PROFERSSIONAL_CREDIT_TYPE
, TAV.PROFESSIONAL_CREDITS PROFESSIONAL_CREDIT_AMOUNT
, VEN.VENDOR_NAME SUPPLIER_NAME
, TAD.NAME ACTIVITY_TYPE
, TAD.DESCRIPTION ACTIVITY_TYPE_DESCRIPTION
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, ACI.ACTIVITY_CATEGORY) CATEGORY_NAME
, '_DF:OTA:OTA_RESOURCE_USAGES:TRU'
, TAD.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TRU.RESOURCE_USAGE_ID RESOURCE_CHECK_LIST_ID
, TRU.SUPPLIED_RESOURCE_ID SUPPLIED_RESOURCE_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, TAV.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, TAV.DEVELOPER_ORGANIZATION_ID SPONSOR_ORGANIZATION_ID
, TAV.CONTROLLING_PERSON_ID MANAGED_BY_PERSON_ID
, TAV.LANGUAGE_ID LANGUAGE_ID
, TAV.SUPERSEDED_BY_ACT_VERSION_ID SUPERSESED_ACT_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.RCO_ID RCO_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_RESOURCE_USAGES TRU
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS TAV2
, OTA_ACTIVITY_DEFINITIONS TAD
, ORG_ORGANIZATION_DEFINITIONS ORG
, FND_LANGUAGES FND
, FND_LANGUAGES_TL FNDT
, PER_ALL_PEOPLE_F PER
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PO_VENDORS VEN
, MTL_SYSTEM_ITEMS_B INV
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID (+)
AND ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID (+)
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND TRU.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID (+)
AND TAV2.SUPERSEDED_BY_ACT_VERSION_ID (+) = TAV.ACTIVITY_VERSION_ID
AND TAV.LANGUAGE_ID = FND.LANGUAGE_ID (+)
AND FND.LANGUAGE_CODE = FNDT.LANGUAGE_CODE (+)
AND DECODE(TAV.LANGUAGE_ID
, NULL
, 'X'
, FNDT.LANGUAGE) = DECODE(TAV.LANGUAGE_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TAV.VENDOR_ID = VEN.VENDOR_ID (+)
AND TAD.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TAV.DEVELOPER_ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(TAV.DEVELOPER_ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(TAV.DEVELOPER_ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TAV.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND NVL(ORG.DISABLE_DATE
, SYSDATE+1) > SYSDATE
AND TAV.CONTROLLING_PERSON_ID = PER.PERSON_ID (+)
AND NVL(PER.CURRENT_EMPLOYEE_FLAG
, 'Y') = 'Y'
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE (+)
AND PER.EFFECTIVE_END_DATE (+)
AND TAV.ORGANIZATION_ID = INV.ORGANIZATION_ID (+)
AND TAV.INVENTORY_ITEM_ID = INV.INVENTORY_ITEM_ID (+)
AND TAD.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TAD.BUSINESS_GROUP_ID) WITH READ ONLY