VIEW: APPS.OTFV_RESOURCE_CHECKLIST

Source

View Text - Preformatted

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 ,tst.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 ,org.organization_name inventory_organization ,'_KF:INV:MSTK:inv' ,tvt.version_name activity_name ,tav.description activity_description ,tav.intended_audience activity_audience ,tav.objectives activity_objectives ,fndT.description activity_language ,tvt2.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('OTA_DURATION_UNITS', 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 ,tdt.name activity_type ,tdt.description activity_type_description ,tct.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_suppliable_resources_tl tst ,ota_resource_usages tru ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_versions tav2 ,ota_activity_versions_tl tvt2 ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,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 ,ota_category_usages_tl tct ,ota_offerings ofr WHERE tru.activity_version_id = tav.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = userenv('LANG') AND tav.activity_id = tad.activity_id AND tad.activity_id = tdt.activity_id AND tdt.language = userenv('LANG') AND tav.activity_version_id = aci.activity_version_id (+) AND tav.activity_version_id = ofr.activity_version_id (+) AND ofr.delivery_mode_id = tcu.category_usage_id (+) AND tcu.category_usage_id = tct.category_usage_id (+) AND tct.language (+) = userenv('LANG') AND (tcu.type = 'DM' OR tcu.type is null) AND tru.supplied_resource_id = tsr.supplied_resource_id (+) AND tsr.supplied_resource_id = tst.supplied_resource_id (+) AND tst.language (+) = userenv('LANG') AND tav2.superseded_by_act_version_id (+) = tav.activity_version_id AND tav2.activity_version_id = tvt2.activity_version_id (+) AND tvt2.language (+) = userenv('LANG') 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

View Text - HTML Formatted

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
, TST.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
, ORG.ORGANIZATION_NAME INVENTORY_ORGANIZATION
, '_KF:INV:MSTK:INV'
, TVT.VERSION_NAME ACTIVITY_NAME
, TAV.DESCRIPTION ACTIVITY_DESCRIPTION
, TAV.INTENDED_AUDIENCE ACTIVITY_AUDIENCE
, TAV.OBJECTIVES ACTIVITY_OBJECTIVES
, FNDT.DESCRIPTION ACTIVITY_LANGUAGE
, TVT2.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('OTA_DURATION_UNITS'
, 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
, TDT.NAME ACTIVITY_TYPE
, TDT.DESCRIPTION ACTIVITY_TYPE_DESCRIPTION
, TCT.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_SUPPLIABLE_RESOURCES_TL TST
, OTA_RESOURCE_USAGES TRU
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_VERSIONS TAV2
, OTA_ACTIVITY_VERSIONS_TL TVT2
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, 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
, OTA_CATEGORY_USAGES_TL TCT
, OTA_OFFERINGS OFR
WHERE TRU.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID
AND TDT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = OFR.ACTIVITY_VERSION_ID (+)
AND OFR.DELIVERY_MODE_ID = TCU.CATEGORY_USAGE_ID (+)
AND TCU.CATEGORY_USAGE_ID = TCT.CATEGORY_USAGE_ID (+)
AND TCT.LANGUAGE (+) = USERENV('LANG')
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND TRU.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID (+)
AND TSR.SUPPLIED_RESOURCE_ID = TST.SUPPLIED_RESOURCE_ID (+)
AND TST.LANGUAGE (+) = USERENV('LANG')
AND TAV2.SUPERSEDED_BY_ACT_VERSION_ID (+) = TAV.ACTIVITY_VERSION_ID
AND TAV2.ACTIVITY_VERSION_ID = TVT2.ACTIVITY_VERSION_ID (+)
AND TVT2.LANGUAGE (+) = USERENV('LANG')
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