View: OTFV_RESOURCES_BOOKED

Product:	OTA - Learning Management
Description:	Business view template from which the flexfield view is generated.
Implementation/DBA Data:	APPS.OTFV_RESOURCES_BOOKED

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_BOOKING_STATUS'
, TRB.STATUS) BOOKING_STATUS
, TRB.DATE_BOOKING_PLACED BOOKING_DATE
, TRB.CONTACT_NAME DELIVERY_CONTACT
, TRB.CONTACT_PHONE_NUMBER DELIVERY_TELEPHONE
, TRB.DELIVER_TO DELIVERY_LOCATION
, TRB.DELEGATES_PER_UNIT MAXIMUM_USAGE
, TRB.QUANTITY QUANTITY
, TRB.REQUIRED_DATE_FROM REQUIRED_FROM
, TRB.REQUIRED_DATE_TO REQUIRED_TO
, TRB.REQUIRED_START_TIME SESSION_TIME_FROM
, TRB.REQUIRED_END_TIME SESSION_TIME_TO
, DECODE(TRB.PRIMARY_VENUE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TRB.PRIMARY_VENUE_FLAG)) PRIMARY_VENUE
, DECODE(TRB.ROLE_TO_PLAY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('TRAINER_PARTICIPATION'
, TRB.ROLE_TO_PLAY)) TRAINER_ROLE
, TRB.COMMENTS RESOURCE_BOOKING_COMMENTS
, FND.USER_NAME BOOKED_BY
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE) RESOURCE_TYPE
, TSR.CAPACITY CAPACITY
, TSR.NAME RESOURCE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TSR.CONSUMABLE_FLAG) CONSUMABLE
, TSR.COST COST_AMOUNT
, TSR.COST_UNIT COST_UNIT
, DECODE(TSR.CURRENCY_CODE
, NULL
, NULL
, OTA_GENERAL.FND_CURRENCY_NAME(TSR.CURRENCY_CODE)) COST_CURRENCY
, TSR.LEAD_TIME LEAD_TIME
, TSR.START_DATE RESOURCE_AVAILABLE_FROM
, TSR.END_DATE RESOURCE_AVAILABLE_TO
, TSR.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS
, TSR.SUPPLIER_REFERENCE SUPPLIER_REFERENCE
, TSR.COMMENTS SUPPLIABLE_RESOURCE_COMMENTS
, VEN.VENDOR_NAME SUPPLIER_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE EVENT_FROM
, EVT.COURSE_END_DATE EVENT_TO
, ORG.NAME EVENT_CENTER
, TAV.VERSION_NAME ACTIVITY_NAME
, TAD.NAME ACTIVITY_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, ACI.ACTIVITY_CATEGORY) CATEGORY_NAME
, TFL.MONEY_AMOUNT INVOICE_AMOUNT
, DECODE(TFL.CURRENCY_CODE
, NULL
, NULL
, OTA_GENERAL.FND_CURRENCY_NAME(TFL.CURRENCY_CODE)) INVOICE_CURRENCY
, TFL.FINANCE_LINE_ID FINANCE_LINE
, TFL.FINANCE_HEADER_ID FINANCE_HEADER
, '_KF:OTA:RES:RES'
, '_DF:OTA:OTA_RESOURCE_BOOKINGS:TRB'
, TSR.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, VEN.VENDOR_ID VENDOR_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TRB.BOOKING_PERSON_ID BOOKING_PERSON_ID
, TRB.SUPPLIED_RESOURCE_ID SUPPLIED_RESOURCE_ID
, TRB.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_RESOURCE_BOOKINGS TRB
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_RESOURCE_DEFINITIONS RES
, PO_VENDORS VEN
, OTA_EVENTS EVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_VERSIONS TAV
, OTA_FINANCE_LINES TFL
, FND_USER FND
, HR_ALL_ORGANIZATION_UNITS ORG
, OTA_ACT_CAT_INCLUSIONS ACI
, OTA_CATEGORY_USAGES TCU
WHERE TRB.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID
AND TSR.RESOURCE_DEFINITION_ID = RES.RESOURCE_DEFINITION_ID
AND TRB.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = ACI.ACTIVITY_VERSION_ID (+)
AND ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID (+)
AND (TCU.TYPE IS NULL OR TCU.TYPE = 'DM')
AND EVT.TRAINING_CENTER_ID = ORG.ORGANIZATION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TRB.RESOURCE_BOOKING_ID = TFL.RESOURCE_BOOKING_ID (+)
AND TFL.CANCELLED_FLAG (+) = 'N'
AND TRB.BOOKING_PERSON_ID = FND.USER_ID (+)
AND TSR.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TSR.VENDOR_ID = VEN.VENDOR_ID (+)
AND TSR.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TSR.BUSINESS_GROUP_ID) WITH READ ONLY

Columns