VIEW: APPS.OTFV_RESOURCES_BOOKED

Source

View Text - Preformatted

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 ,tst.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 ,ett.title event_title ,evt.course_start_date event_from ,evt.course_end_date event_to ,orgT.name event_center ,tvt.version_name activity_name ,tdt.name activity_type ,tct.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 ,ofr.offering_id offering_id FROM hr_all_organization_units_tl bgrT ,ota_resource_bookings trb ,ota_suppliable_resources tsr ,ota_suppliable_resources_tl tst ,ota_resource_definitions res ,po_vendors ven ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_finance_lines tfl ,fnd_user fnd ,hr_all_organization_units_tl orgT ,ota_category_usages tcu ,ota_category_usages_tl tct WHERE trb.supplied_resource_id = tsr.supplied_resource_id AND tsr.supplied_resource_id = tst.supplied_resource_id AND tst.language = USERENV('LANG') AND tsr.resource_definition_id = res.resource_definition_id AND trb.event_id = evt.event_id (+) AND evt.event_id = ett.event_id (+) AND ett.language (+) = USERENV('LANG') AND evt.parent_offering_id = ofr.offering_id (+) AND ofr.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = USERENV('LANG') 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 is null OR tcu.type = 'DM') AND evt.training_center_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') 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

View Text - HTML Formatted

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
, TST.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
, ETT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE EVENT_FROM
, EVT.COURSE_END_DATE EVENT_TO
, ORGT.NAME EVENT_CENTER
, TVT.VERSION_NAME ACTIVITY_NAME
, TDT.NAME ACTIVITY_TYPE
, TCT.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
, OFR.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_RESOURCE_BOOKINGS TRB
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_SUPPLIABLE_RESOURCES_TL TST
, OTA_RESOURCE_DEFINITIONS RES
, PO_VENDORS VEN
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_FINANCE_LINES TFL
, FND_USER FND
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_CATEGORY_USAGES TCU
, OTA_CATEGORY_USAGES_TL TCT
WHERE TRB.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID
AND TSR.SUPPLIED_RESOURCE_ID = TST.SUPPLIED_RESOURCE_ID
AND TST.LANGUAGE = USERENV('LANG')
AND TSR.RESOURCE_DEFINITION_ID = RES.RESOURCE_DEFINITION_ID
AND TRB.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID (+)
AND ETT.LANGUAGE (+) = USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
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 IS NULL OR TCU.TYPE = 'DM')
AND EVT.TRAINING_CENTER_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
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