View: OTFV_ENROLLED_EVENTS

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

View Text

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DECODE(CON.CONTACT_ID
, NULL
, NULL
, CON.LAST_NAME || '
, ' || INITCAP(CON.TITLE) || ' ' || CON.FIRST_NAME ) PERSON_NAME
, EVT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, ORGT1.NAME TRAINING_CENTER
, TDB.ATTENDANCE_RESULT ATTENDANCE_RESULT
, DECODE(TDB.FAILURE_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('DELEGATE_FAILURE_REASON'
, TDB.FAILURE_REASON)) FAILURE_REASON
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.SUCCESSFUL_ATTENDANCE_FLAG)) SUCCESSFUL_ATTENDANCE_FLAG
, TDB.TOTAL_TRAINING_TIME TOTAL_TRAINING_TIME
, DECODE(TDB.CONTENT_PLAYER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, TDB.SCORE TEST_SCORE
, TDB.COMPLETED_CONTENT NUMBER_OF_MODULES_COMPLETED
, TDB.TOTAL_CONTENT TOTAL_MODULES
, NULL CURRENT_EMPLOYEE_FLAG
, NULL INTERNAL_BOOKING_FLAG
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, NULL ASSIGNMENT_NUMBER
, NULL ASSIGNMENT_ORGANIZATION
, NULL ASSIGNMENT_LOCATION
, NULL ASSIGNMENT_JOB_CATEGORY
, NULL ASSIGNMENT_JOB
, BST.NAME BOOKING_STATUS
, BST.TYPE BOOKING_STATUS_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, LNG.DESCRIPTION LANGUAGE
, ORGT.NAME ORGANIZATION_NAME
, TDB.NUMBER_OF_PLACES NUMBER_OF_PLACES
, (TDB.NUMBER_OF_PLACES * DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)) NUMBER_OF_SUCCESSFUL_PLACES
, DECODE(BST.TYPE
, 'R'
, TDB.NUMBER_OF_PLACES
, 0) REQUESTED_ENROLLMENT
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0) ATTENDED_ENROLLMENT
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 0) PLACED_ENROLLMENT
, DECODE(BST.TYPE
, 'W'
, TDB.NUMBER_OF_PLACES
, 0) WAITLISTED_ENROLLMENT
, 'EXTERNAL' TRAINING_TARGET_TYPE
, 0 SUCC_ATT_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0)) SUCC_ATT_EXT_STUDENT_COUNT
, 0 NON_SUCC_ATT_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'N'
, TDB.NUMBER_OF_PLACES
, 0)) NON_SUCC_ATT_EXT_STUDENT_COUNT
, 0 CONFIRMED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) CONFIRMED_EXT_STUDENT_COUNT
, 0 ATTENDED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0)) ATTENDED_EXT_STUDENT_COUNT
, 0 NON_ATTENDED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) NON_ATTENDED_EXT_STUDENT_COUNT
, 0 CANCELLED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) CANCELLED_EXT_STUDENT_COUNT
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, TDB.COMMENTS ENROLMENT_COMMENTS
, TDB.SPECIAL_BOOKING_INSTRUCTIONS SPECIAL_REQUIREMENTS
, DECODE(TDB.SOURCE_OF_BOOKING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_SOURCE'
, TDB.SOURCE_OF_BOOKING)) SOURCE_OF_BOOKING_MEANING
, DECODE(TDB.CORESPONDENT
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('CORRESPONDENT'
, TDB.CORESPONDENT)) CORRESPONDENCE_MEANING
, TDB.DELEGATE_CONTACT_PHONE CORRESPONDENCE_PHONE
, TDB.DELEGATE_CONTACT_FAX CORRESPONDENCE_FAX
, TDB.DELEGATE_CONTACT_EMAIL CORRESPONDENCE_EMAIL
, CAD.ADDRESS1 ADDRESS_LINE_1
, CAD.ADDRESS2 ADDRESS_LINE_2
, CAD.ADDRESS3 ADDRESS_LINE_3
, CAD.ADDRESS4 ADDRESS_LINE_4
, CAD.CITY CITY
, CAD.STATE STATE
, CAD.POSTAL_CODE POSTAL_CODE
, CAD.COUNTRY COUNTRY
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, 0 ASSIGNMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.DELEGATE_CONTACT_ID DELEGATE_CONTACT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.ORG_ID ORG_ID
, TDB.LINE_ID LINE_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM RA_CUSTOMERS CUS
, RA_CONTACTS CON
, OTA_BOOKING_STATUS_TYPES BST
, OTA_ACTIVITY_VERSIONS TAV
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_DELEGATE_BOOKINGS TDB
, FND_LANGUAGES_VL LNG
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, RA_ADDRESSES CAD
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE EVT.EVENT_TYPE = 'SCHEDULED'
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID(+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND BGR.ORGANIZATION_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID
AND TDB.DELEGATE_CONTACT_ID = CON.CONTACT_ID (+)
AND TDB.LANGUAGE_ID = LNG.LANGUAGE_ID (+)
AND TDB.BUSINESS_GROUP_ID +0 = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID)
AND TDB.CONTACT_ADDRESS_ID = CAD.ADDRESS_ID (+) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DEL.FULL_NAME PERSON_NAME
, EVT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, ORGT1.NAME TRAINING_CENTER
, TDB.ATTENDANCE_RESULT ATTENDANCE_RESULT
, DECODE(TDB.FAILURE_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('DELEGATE_FAILURE_REASON'
, TDB.FAILURE_REASON)) FAILURE_REASON
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.SUCCESSFUL_ATTENDANCE_FLAG)) SUCCESSFUL_ATTENDANCE_FLAG
, TDB.TOTAL_TRAINING_TIME TOTAL_TRAINING_TIME
, DECODE(TDB.CONTENT_PLAYER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, TDB.SCORE TEST_SCORE
, TDB.COMPLETED_CONTENT NUMBER_OF_MODULES_COMPLETED
, TDB.TOTAL_CONTENT TOTAL_MODULES
, DECODE(DEL.CURRENT_EMPLOYEE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DEL.CURRENT_EMPLOYEE_FLAG)) CURRENT_EMPLOYEE_FLAG
, DECODE(TDB.INTERNAL_BOOKING_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.INTERNAL_BOOKING_FLAG)) INTERNAL_BOOKING_FLAG
, CUS.CUSTOMER_NAME CUSTOMER_NAME
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ORG1T.NAME ASSIGNMENT_ORGANIZATION
, LOCT.LOCATION_CODE ASSIGNMENT_LOCATION
, JEI.JEI_INFORMATION1 ASSIGNMENT_JOB_CATEGORY
, JOB.NAME ASSIGNMENT_JOB
, BST.NAME BOOKING_STATUS
, BST.TYPE BOOKING_STATUS_TYPE
, TAV.VERSION_NAME ACTIVITY_NAME
, LNG.DESCRIPTION LANGUAGE
, ORGT.NAME ORGANIZATION_NAME
, TDB.NUMBER_OF_PLACES NUMBER_OF_PLACES
, (TDB.NUMBER_OF_PLACES * DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)) NUMBER_OF_SUCCESSFUL_PLACES
, DECODE(BST.TYPE
, 'R'
, TDB.NUMBER_OF_PLACES
, 0) REQUESTED_ENROLLMENT
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0) ATTENDED_ENROLLMENT
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 0) PLACED_ENROLLMENT
, DECODE(BST.TYPE
, 'W'
, TDB.NUMBER_OF_PLACES
, 0) WAITLISTED_ENROLLMENT
, 'INTERNAL' TRAINING_TARGET_TYPE
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0)) SUCC_ATT_INT_STUDENT_COUNT
, 0 SUCC_ATT_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'N'
, TDB.NUMBER_OF_PLACES
, 0)) NON_SUCC_ATT_INT_STUDENT_COUNT
, 0 NON_SUCC_ATT_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) CONFIRMED_INT_STUDENT_COUNT
, 0 CONFIRMED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0)) ATTENDED_INT_STUDENT_COUNT
, 0 ATTENDED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) NON_ATTENDED_INT_STUDENT_COUNT
, 0 NON_ATTENDED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) CANCELLED_INT_STUDENT_COUNT
, 0 CANCELLED_EXT_STUDENT_COUNT
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, TDB.COMMENTS ENROLMENT_COMMENTS
, TDB.SPECIAL_BOOKING_INSTRUCTIONS SPECIAL_REQUIREMENTS
, DECODE(TDB.SOURCE_OF_BOOKING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_SOURCE'
, TDB.SOURCE_OF_BOOKING)) SOURCE_OF_BOOKING_MEANING
, DECODE(TDB.CORESPONDENT
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('CORRESPONDENT'
, TDB.CORESPONDENT)) CORRESPONDENCE_MEANING
, TDB.DELEGATE_CONTACT_PHONE CORRESPONDENCE_PHONE
, TDB.DELEGATE_CONTACT_FAX CORRESPONDENCE_FAX
, TDB.DELEGATE_CONTACT_EMAIL CORRESPONDENCE_EMAIL
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE1
, 'I'
, ASG.INTERNAL_ADDRESS_LINE
, 'C'
, ASG2.INTERNAL_ADDRESS_LINE) ADDRESS_LINE_1
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE2
, 'I'
, LOC1.ADDRESS_LINE_1
, 'C'
, LOC2.ADDRESS_LINE_1) ADDRESS_LINE_2
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE3
, 'I'
, LOC1.ADDRESS_LINE_2
, 'C'
, LOC2.ADDRESS_LINE_2) ADDRESS_LINE_3
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, NULL
, 'I'
, LOC1.ADDRESS_LINE_3
, 'C'
, LOC2.ADDRESS_LINE_3) ADDRESS_LINE_4
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.TOWN_OR_CITY
, 'I'
, LOC1.TOWN_OR_CITY
, 'C'
, LOC2.TOWN_OR_CITY) CITY
, NULL STATE
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.POSTAL_CODE
, 'I'
, LOC1.POSTAL_CODE
, 'C'
, LOC2.POSTAL_CODE) POSTAL_CODE
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.COUNTRY
, 'I'
, LOC1.COUNTRY
, 'C'
, LOC2.COUNTRY) COUNTRY
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TDB.DELEGATE_ASSIGNMENT_ID ASSIGNMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, CUS.CUSTOMER_ID CUSTOMER_ID
, TDB.DELEGATE_CONTACT_ID DELEGATE_CONTACT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.ORG_ID ORG_ID
, TDB.LINE_ID LINE_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, EVT.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORG1T
, FND_LANGUAGES_VL LNG
, OTA_ACTIVITY_VERSIONS TAV
, PER_ALL_ASSIGNMENTS_F ASG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_BOOKING_STATUS_TYPES BST
, PER_ALL_PEOPLE_F DEL
, OTA_EVENTS EVT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_DELEGATE_BOOKINGS TDB
, HR_LOCATIONS_ALL_TL LOCT
, PER_JOBS JOB
, PER_JOB_EXTRA_INFO JEI
, RA_CUSTOMERS CUS
, PER_ADDRESSES ADDR
, PER_ALL_ASSIGNMENTS_F ASG2
, HR_LOCATIONS_ALL LOC1
, HR_LOCATIONS_ALL LOC2
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID (+)
AND ( ( TDB.INTERNAL_BOOKING_FLAG = 'Y') OR ((TDB.INTERNAL_BOOKING_FLAG = 'N')
AND (TDB.DELEGATE_PERSON_ID IS NOT NULL)
AND (DEL.PERSON_ID IS NOT NULL) ) )
AND NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(DEL.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(DEL.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TDB.DELEGATE_ASSIGNMENT_ID = ASG.ASSIGNMENT_ID (+)
AND (ASG.ROWID (+) = OTA_TDB_BUS.ASSIGNMENT_OK('STUDENT'
, TDB.DELEGATE_ASSIGNMENT_ID
, TDB.EVENT_ID
, TDB.DATE_BOOKING_PLACED))
AND ASG.ORGANIZATION_ID = ORG1T.ORGANIZATION_ID (+)
AND DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, ORG1T.LANGUAGE) = DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, LOCT.LANGUAGE) = DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.JOB_ID = JEI.JOB_ID (+)
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND TDB.LANGUAGE_ID = LNG.LANGUAGE_ID (+)
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID (+)
AND DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, BGRT.LANGUAGE) = DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID)
AND TDB.PERSON_ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND TDB.SPONSOR_ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN ASG2.EFFECTIVE_START_DATE (+)
AND ASG2.EFFECTIVE_END_DATE (+)
AND ASG.LOCATION_ID = LOC1.LOCATION_ID (+)
AND ASG2.LOCATION_ID = LOC2.LOCATION_ID (+) WITH READ ONLY

Columns