eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OTFV_CUST_RESTRICTED_EVENTS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,ett.title event_title ,evt.comments event_comments ,evt.course_start_date event_start_date ,evt.course_end_date event_end_date ,evt.course_start_time event_start_time ,evt.course_end_time event_end_time ,evt.timezone time_zone ,evt.duration event_duration ,DECODE(evt.duration_units, NULL, NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) event_duration_units ,per1.full_name event_creator ,per2.full_name event_owner ,DECODE(evt.secure_event_flag, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO', evt.secure_event_flag)) secure_event ,hr_bis.bis_decode_lookup('YES_NO', SUBSTR (OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID),1,1)) resource_bookings ,hr_bis.bis_decode_lookup('RESOURCE_TYPE',tsr.resource_type) resource_type ,tst.name resource_name ,orgT.name training_center ,ven.vendor_name supplier_name ,evt.enrolment_start_date enrollment_start_date ,evt.enrolment_end_date enrollment_end_date ,ota_utility.get_lang_name(ofr.language_code) LANGUAGE ,DECODE(evt.event_status, NULL, NULL, hr_bis.bis_decode_lookup('SCHEDULED_EVENT_STATUS',evt.event_status)) event_status ,DECODE(evt.user_status, NULL, NULL, hr_bis.bis_decode_lookup('EVENT_USER_STATUS',evt.user_status)) event_user_status ,evt.minimum_attendees minimum_attendees ,evt.maximum_attendees maximum_attendees ,evt.standard_price standard_price ,cur.name currency ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,tea.comments event_association_comments ,tdt.name activity_type ,tvt.version_name activity_name ,tfl.finance_header_id finance_header_number ,tfl.finance_line_id finance_line_number ,DECODE(evt.line_id, NULL, NULL, HRI_BPL_OE_ORDER.GET_ORDER_NUMBER(evt.line_id)) order_number ,DECODE(evt.line_id, NULL, NULL, HRI_BPL_OE_ORDER.GET_LINE_NUMBER(evt.line_id)) line_number ,'_DF:OTA:OTA_EVENT_ASSOCIATIONS:tea' ,evt.business_group_id business_group_id ,evt.event_id event_id ,tea.customer_id customer_id ,tea.event_association_id event_association_id ,evt.training_center_id training_center_id ,ofr.offering_id offering_id ,evt.owner_id owner_id ,trb.resource_booking_id resource_booking_id ,tsr.supplied_resource_id supplied_resource_id ,tfl.finance_header_id finance_header_id ,tfl.finance_line_id finance_line_id ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,DECODE(evt.line_id, NULL, NULL, HRI_BPL_OE_ORDER.GET_HEADER_ID(evt.line_id)) oe_header_id ,evt.line_id oe_line_id FROM hr_all_organization_units_tl bgrT ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,ota_event_associations tea ,po_vendors ven ,fnd_currencies_vl cur ,hr_all_organization_units_tl orgT ,ota_resource_bookings trb ,ota_suppliable_resources tsr ,ota_suppliable_resources_tl tst ,ota_finance_lines tfl ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,per_all_people_f per1 ,per_all_people_f per2 WHERE evt.public_event_flag = 'N' AND evt.training_center_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND evt.event_id = tea.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') AND evt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND tea.customer_id IS NOT NULL AND evt.vendor_id = ven.vendor_id (+) AND evt.created_by = per1.person_id (+) AND evt.owner_id = per2.person_id(+) AND evt.currency_code = cur.currency_code (+) AND evt.event_id = trb.event_id (+) AND trb.supplied_resource_id = tsr.supplied_resource_id (+) AND tsr.supplied_resource_id = tst.supplied_resource_id (+) AND tst.language (+) = userenv('LANG') AND trb.resource_booking_id = tfl.resource_booking_id (+) 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 tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') AND evt.course_start_date BETWEEN per1.effective_start_date(+) AND per1.effective_end_date(+) AND evt.course_start_date BETWEEN per2.effective_start_date(+) AND per2.effective_end_date(+) AND evt.business_group_id = NVL(ota_general.get_business_group_id,evt.business_group_id) AND tea.customer_id = CUST_ACCT.cust_account_id AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE EVENT_START_DATE
, EVT.COURSE_END_DATE EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, EVT.TIMEZONE TIME_ZONE
, EVT.DURATION EVENT_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) EVENT_DURATION_UNITS
, PER1.FULL_NAME EVENT_CREATOR
, PER2.FULL_NAME EVENT_OWNER
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.SECURE_EVENT_FLAG)) SECURE_EVENT
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, SUBSTR (OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID)
, 1
, 1)) RESOURCE_BOOKINGS
, HR_BIS.BIS_DECODE_LOOKUP('RESOURCE_TYPE'
, TSR.RESOURCE_TYPE) RESOURCE_TYPE
, TST.NAME RESOURCE_NAME
, ORGT.NAME TRAINING_CENTER
, VEN.VENDOR_NAME SUPPLIER_NAME
, EVT.ENROLMENT_START_DATE ENROLLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLLMENT_END_DATE
, OTA_UTILITY.GET_LANG_NAME(OFR.LANGUAGE_CODE) LANGUAGE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) EVENT_USER_STATUS
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, CUR.NAME CURRENCY
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, TEA.COMMENTS EVENT_ASSOCIATION_COMMENTS
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, TFL.FINANCE_HEADER_ID FINANCE_HEADER_NUMBER
, TFL.FINANCE_LINE_ID FINANCE_LINE_NUMBER
, DECODE(EVT.LINE_ID
, NULL
, NULL
, HRI_BPL_OE_ORDER.GET_ORDER_NUMBER(EVT.LINE_ID)) ORDER_NUMBER
, DECODE(EVT.LINE_ID
, NULL
, NULL
, HRI_BPL_OE_ORDER.GET_LINE_NUMBER(EVT.LINE_ID)) LINE_NUMBER
, '_DF:OTA:OTA_EVENT_ASSOCIATIONS:TEA'
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, TEA.CUSTOMER_ID CUSTOMER_ID
, TEA.EVENT_ASSOCIATION_ID EVENT_ASSOCIATION_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, OFR.OFFERING_ID OFFERING_ID
, EVT.OWNER_ID OWNER_ID
, TRB.RESOURCE_BOOKING_ID RESOURCE_BOOKING_ID
, TSR.SUPPLIED_RESOURCE_ID SUPPLIED_RESOURCE_ID
, TFL.FINANCE_HEADER_ID FINANCE_HEADER_ID
, TFL.FINANCE_LINE_ID FINANCE_LINE_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, DECODE(EVT.LINE_ID
, NULL
, NULL
, HRI_BPL_OE_ORDER.GET_HEADER_ID(EVT.LINE_ID)) OE_HEADER_ID
, EVT.LINE_ID OE_LINE_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, OTA_EVENT_ASSOCIATIONS TEA
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_RESOURCE_BOOKINGS TRB
, OTA_SUPPLIABLE_RESOURCES TSR
, OTA_SUPPLIABLE_RESOURCES_TL TST
, OTA_FINANCE_LINES TFL
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, PER_ALL_PEOPLE_F PER1
, PER_ALL_PEOPLE_F PER2
WHERE EVT.PUBLIC_EVENT_FLAG = 'N'
AND EVT.TRAINING_CENTER_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND EVT.EVENT_ID = TEA.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TEA.CUSTOMER_ID IS NOT NULL
AND EVT.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.CREATED_BY = PER1.PERSON_ID (+)
AND EVT.OWNER_ID = PER2.PERSON_ID(+)
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE (+)
AND EVT.EVENT_ID = TRB.EVENT_ID (+)
AND TRB.SUPPLIED_RESOURCE_ID = TSR.SUPPLIED_RESOURCE_ID (+)
AND TSR.SUPPLIED_RESOURCE_ID = TST.SUPPLIED_RESOURCE_ID (+)
AND TST.LANGUAGE (+) = USERENV('LANG')
AND TRB.RESOURCE_BOOKING_ID = TFL.RESOURCE_BOOKING_ID (+)
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 TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND EVT.COURSE_START_DATE BETWEEN PER1.EFFECTIVE_START_DATE(+)
AND PER1.EFFECTIVE_END_DATE(+)
AND EVT.COURSE_START_DATE BETWEEN PER2.EFFECTIVE_START_DATE(+)
AND PER2.EFFECTIVE_END_DATE(+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)
AND TEA.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID WITH READ ONLY