View: OTA_DELEGATE_BOOKINGS_V

Product:	OTA - Learning Management
Description:	View to list all Student and Customer Enrollment.
Implementation/DBA Data:	APPS.OTA_DELEGATE_BOOKINGS_V

View Text

SELECT TDB.ROWID
, TDB.BUSINESS_GROUP_ID
, TDB.BOOKING_ID
, TDB.BOOKING_STATUS_TYPE_ID
, BST.NAME
, DECODE(TDB.CUSTOMER_ID
, NULL
, 'O'
, 'C')
, TDB.CUSTOMER_ID
, CUS.CUSTOMER_NAME
, TDB.ORGANIZATION_ID
, ORG.NAME
, DECODE(TDB.CUSTOMER_ID
, NULL
, 'OR'
, 'CU')
, TDB.DELEGATE_PERSON_ID
, TDB.DELEGATE_ASSIGNMENT_ID
, TDB.DELEGATE_CONTACT_ID
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, DEL.PER_INFORMATION18
, DEL.LAST_NAME)
, DLC.LAST_NAME)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DEL.APPLICANT_NUMBER
, NULL)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DEL.EMPLOYEE_NUMBER
, NULL)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, DEL.PER_INFORMATION19
, DEL.FIRST_NAME)
, DLC.FIRST_NAME)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DEL.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(DLC.LAST_NAME
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE'
, DLC.TITLE)
, DLC.FIRST_NAME
, BG.LEGISLATION_CODE
, DLC.LAST_NAME_ALT
, DLC.FIRST_NAME_ALT))
, DECODE(TDB.CUSTOMER_ID
, NULL
, DEL.TITLE
, DLC.TITLE)
, DECODE(TDB.CUSTOMER_ID
, NULL
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, DEL.TITLE)
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE'
, DLC.TITLE))
, DECODE(ASG1.ASSIGNMENT_NUMBER
, NULL
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, ASG1.ASSIGNMENT_NUMBER
, -1)
, ASG1.ASSIGNMENT_NUMBER)
, DECODE(ORG1.NAME
, NULL
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, ORG1.NAME
, '****')
, ORG1.NAME)
, TDB.SPONSOR_PERSON_ID
, TDB.SPONSOR_ASSIGNMENT_ID
, TDB.CONTACT_ID
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, SPN.PER_INFORMATION18
, SPN.LAST_NAME)
, CON.LAST_NAME)
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.APPLICANT_NUMBER
, NULL)
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.EMPLOYEE_NUMBER
, NULL)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, SPN.PER_INFORMATION19
, SPN.FIRST_NAME)
, CON.FIRST_NAME)
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.FULL_NAME
, OTA_TDB_BUS.GET_FULL_NAME(CON.LAST_NAME
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE'
, CON.TITLE)
, CON.FIRST_NAME
, BG.LEGISLATION_CODE
, CON.LAST_NAME_ALT
, CON.FIRST_NAME_ALT))
, DECODE(TDB.CUSTOMER_ID
, NULL
, SPN.TITLE
, CON.TITLE)
, DECODE(TDB.CUSTOMER_ID
, NULL
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, SPN.TITLE)
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE'
, CON.TITLE))
, DECODE(ASG2.ASSIGNMENT_NUMBER
, NULL
, DECODE(TDB.SPONSOR_PERSON_ID
, NULL
, ASG2.ASSIGNMENT_NUMBER
, -1)
, ASG2.ASSIGNMENT_NUMBER)
, DECODE(ORG2.NAME
, NULL
, DECODE(TDB.SPONSOR_PERSON_ID
, NULL
, ORG2.NAME
, '****')
, ORG2.NAME)
, TDB.CONTACT_ADDRESS_ID
, TDB.PERSON_ADDRESS_ID
, TDB.CORESPONDENT
, HR_GENERAL.DECODE_LOOKUP ('CORRESPONDENT'
, TDB.CORESPONDENT)
, TDB.PERSON_ADDRESS_TYPE
, DECODE (TDB.CUSTOMER_ID
, NULL
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE1||DECODE(ADDR.ADDRESS_LINE1
, NULL
, ''
, '
, ')|| ADDR.ADDRESS_LINE2 ||DECODE(ADDR.ADDRESS_LINE2
, NULL
, ''
, '
, ')|| ADDR.ADDRESS_LINE3|| DECODE(ADDR.ADDRESS_LINE3
, NULL
, ''
, '
, ')|| ADDR.TOWN_OR_CITY||DECODE(ADDR.TOWN_OR_CITY
, NULL
, ''
, '
, ')|| ADDR.REGION_1|| DECODE(ADDR.REGION_1
, NULL
, ''
, '
, ')|| ADDR.REGION_2||DECODE(ADDR.REGION_2
, NULL
, ''
, '
, ')|| ADDR.REGION_3||DECODE(ADDR.REGION_3
, NULL
, ''
, '
, ')|| ADDR.POSTAL_CODE|| DECODE(ADDR.POSTAL_CODE
, NULL
, ''
, '
, ')|| ADDR.COUNTRY||DECODE(ADDR.COUNTRY
, NULL
, ''
, '
, ')
, 'I'
, DECODE(TDB.CORESPONDENT
, 'S'
, ASG1.INTERNAL_ADDRESS_LINE|| DECODE(ASG1.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ')|| HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG1.LOCATION_ID)
, 'C'
, ASG2.INTERNAL_ADDRESS_LINE|| DECODE(ASG2.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ')|| HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG2.LOCATION_ID)
, NULL))
, CAD.ADDRESS1||DECODE(CAD.ADDRESS1
, NULL
, ''
, '
, ')|| CAD.ADDRESS2||DECODE(CAD.ADDRESS2
, NULL
, ''
, '
, ')|| CAD.ADDRESS3|| DECODE(CAD.ADDRESS3
, NULL
, ''
, '
, ')|| CAD.ADDRESS4||DECODE(CAD.ADDRESS4
, NULL
, ''
, '
, ')|| CAD.CITY||DECODE(CAD.CITY
, NULL
, ''
, '
, ')|| CAD.STATE||DECODE(CAD.STATE
, NULL
, ''
, '
, ')|| CAD.PROVINCE||DECODE(CAD.PROVINCE
, NULL
, ''
, '
, ')|| CAD.COUNTY||DECODE(CAD. COUNTY
, NULL
, ''
, '
, ')|| CAD.POSTAL_CODE||DECODE(CAD.POSTAL_CODE
, NULL
, ''
, '
, ')|| CAD.COUNTRY )
, TDB.DELEGATE_CONTACT_PHONE
, TDB.DELEGATE_CONTACT_EMAIL
, TDB.DELEGATE_CONTACT_FAX
, TDB.THIRD_PARTY_CUSTOMER_ID
, THP.CUSTOMER_NAME
, TDB.THIRD_PARTY_CONTACT_ID
, THC.LAST_NAME
, THC.FIRST_NAME
, THC.TITLE
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE'
, THC.TITLE)
, OTA_TDB_BUS.GET_FULL_NAME(THC.LAST_NAME
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_MEANING'
, THC.TITLE)
, THC.FIRST_NAME
, BG.LEGISLATION_CODE
, THC.LAST_NAME_ALT
, THC.FIRST_NAME_ALT)
, THA.ADDRESS1||DECODE(THA.ADDRESS1
, NULL
, ''
, '
, ') || THA.ADDRESS2||DECODE(THA.ADDRESS2
, NULL
, ''
, '
, ')|| THA.ADDRESS3|| DECODE(THA.ADDRESS3
, NULL
, ''
, '
, ')|| THA.ADDRESS4||DECODE(THA.ADDRESS4
, NULL
, ''
, '
, ')|| THA.CITY|| DECODE(THA.CITY
, NULL
, ''
, '
, ')|| THA.STATE|| DECODE(THA.STATE
, NULL
, ''
, '
, ')|| THA.PROVINCE|| DECODE(THA.PROVINCE
, NULL
, ''
, '
, ')|| THA.COUNTY|| DECODE(THA.COUNTY
, NULL
, ''
, '
, ')|| THA.POSTAL_CODE|| DECODE(THA.POSTAL_CODE
, NULL
, ''
, '
, ')|| THA.COUNTRY
, TDB.THIRD_PARTY_ADDRESS_ID
, TDB.THIRD_PARTY_CONTACT_PHONE
, TDB.THIRD_PARTY_CONTACT_FAX
, TDB.THIRD_PARTY_EMAIL
, EVT.EVENT_ID
, ENT.TITLE
, EVT.CENTRE
, HR_GENERAL.DECODE_LOOKUP ('TRAINING_CENTRE'
, EVT.CENTRE)
, EVT.COURSE_END_DATE
, EVT.COURSE_START_DATE
, EVT.ENROLMENT_END_DATE
, EVT.ENROLMENT_START_DATE
, EVT.EVENT_TYPE
, HR_GENERAL.DECODE_LOOKUP ('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)
, DECODE(EVT.PRICE_BASIS
, 'S'
, EVT.STANDARD_PRICE
, NULL)
, DECODE(EVT.PRICE_BASIS
, 'S'
, EVT.CURRENCY_CODE
, NULL)
, TAV.VERSION_NAME
, TAV.ACTIVITY_VERSION_ID
, EVT.PRICE_BASIS
, EVT.PUBLIC_EVENT_FLAG
, EVT.SECURE_EVENT_FLAG
, TDB.DATE_BOOKING_PLACED
, TDB.INTERNAL_BOOKING_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, TDB.INTERNAL_BOOKING_FLAG)
, TDB.NUMBER_OF_PLACES
, TDB.ADMINISTRATOR
, TDB.AUTHORIZER_PERSON_ID
, AUT.USER_NAME
, TDB.BOOKING_PRIORITY
, HR_GENERAL.DECODE_LOOKUP ('PRIORITY_LEVEL'
, TDB.BOOKING_PRIORITY)
, TDB.COMMENTS
, TDB.DATE_STATUS_CHANGED
, TDB.LANGUAGE_ID
, OTA_GENERAL.FND_LANG_DESC(TDB.LANGUAGE_ID)
, TDB.SOURCE_OF_BOOKING
, HR_GENERAL.DECODE_LOOKUP ('BOOKING_SOURCE'
, TDB.SOURCE_OF_BOOKING)
, TDB.FAILURE_REASON
, HR_GENERAL.DECODE_LOOKUP ('DELEGATE_FAILURE_REASON'
, TDB.FAILURE_REASON)
, TDB.ATTENDANCE_RESULT
, TDB.SPECIAL_BOOKING_INSTRUCTIONS
, TDB.SUCCESSFUL_ATTENDANCE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, TDB.SUCCESSFUL_ATTENDANCE_FLAG)
, TDB.LAST_UPDATE_DATE
, TDB.LAST_UPDATED_BY
, TDB.LAST_UPDATE_LOGIN
, TDB.CREATED_BY
, TDB.CREATION_DATE
, TDB.TDB_INFORMATION_CATEGORY
, TDB.TDB_INFORMATION1
, TDB.TDB_INFORMATION2
, TDB.TDB_INFORMATION3
, TDB.TDB_INFORMATION4
, TDB.TDB_INFORMATION5
, TDB.TDB_INFORMATION6
, TDB.TDB_INFORMATION7
, TDB.TDB_INFORMATION8
, TDB.TDB_INFORMATION9
, TDB.TDB_INFORMATION10
, TDB.TDB_INFORMATION11
, TDB.TDB_INFORMATION12
, TDB.TDB_INFORMATION13
, TDB.TDB_INFORMATION14
, TDB.TDB_INFORMATION15
, TDB.TDB_INFORMATION16
, TDB.TDB_INFORMATION17
, TDB.TDB_INFORMATION18
, TDB.TDB_INFORMATION19
, TDB.TDB_INFORMATION20
, TDB.OBJECT_VERSION_NUMBER
, TFL.FINANCE_HEADER_ID
, TFL.FINANCE_LINE_ID
, TFH.CUSTOMER_ID
, CU.CUSTOMER_NAME
, OTA_TDB_BUS.GET_FULL_NAME(ICO.LAST_NAME
, HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_MEANING'
, ICO.TITLE)
, ICO.FIRST_NAME
, BG.LEGISLATION_CODE
, ICO.LAST_NAME_ALT
, ICO.FIRST_NAME_ALT)
, TFH.INVOICE_ADDRESS
, TFL.TRANSFER_STATUS
, HR_GENERAL.DECODE_LOOKUP ('GL_TRANSFER_STATUS'
, TFL.TRANSFER_STATUS)
, TFL.BOOKING_DEAL_ID
, OBD.TYPE
, HR_GENERAL.DECODE_LOOKUP ('BOOKING_DEAL_TYPE'
, OBD.TYPE)
, DECODE(OBD.TYPE
, 'D'
, OBD.NAME
, NULL)
, DECODE(OBD.TYPE
, 'P'
, OBD.NAME
, NULL)
, TFL.CURRENCY_CODE
, TFL.STANDARD_AMOUNT
, TFL.UNITARY_AMOUNT
, TFL.MONEY_AMOUNT
, TFL.OBJECT_VERSION_NUMBER
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, DEL.LAST_NAME
, NULL)
, DLC.LAST_NAME_ALT)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, DEL.FIRST_NAME
, NULL)
, DLC.FIRST_NAME_ALT)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, SPN.LAST_NAME
, NULL)
, CON.LAST_NAME_ALT)
, DECODE(TDB.CUSTOMER_ID
, NULL
, DECODE(BG.LEGISLATION_CODE
, 'JP'
, SPN.FIRST_NAME
, NULL)
, CON.FIRST_NAME_ALT)
, TDB.LINE_ID
, TDB.ORG_ID
, TDB.DAEMON_FLAG
, TDB.DAEMON_TYPE
, TDB.OLD_EVENT_ID
, TDB.QUOTE_LINE_ID
, TDB.INTERFACE_SOURCE
, TDB.TOTAL_TRAINING_TIME
, TDB.CONTENT_PLAYER_STATUS
, TDB.SCORE
, TDB.COMPLETED_CONTENT
, TDB.TOTAL_CONTENT
, EVT.PARENT_OFFERING_ID
, EVT.EVENT_STATUS
, TDB.BOOKING_JUSTIFICATION_ID
, TDB.IS_HISTORY_FLAG
FROM FND_USER AUT
, OTA_BOOKING_STATUS_TYPES_TL BST
, (SELECT SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB( PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, NULL LAST_NAME_ALT
, NULL FIRST_NAME_ALT
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) CON
, (SELECT SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB( PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, NULL LAST_NAME_ALT
, NULL FIRST_NAME_ALT
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) THC
, (SELECT LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTY COUNTY
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.COUNTRY COUNTRY
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_SITE.STATUS STATUS
, ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
, ACCT_SITE.ORG_ID ORG_ID
FROM HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID ) THA
, (SELECT LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTY COUNTY
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.COUNTRY COUNTRY
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_SITE.STATUS STATUS
, ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
, ACCT_SITE.ORG_ID ORG_ID
FROM HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID ) CAD
, (SELECT SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CUST_ACCT.STATUS STATUS
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) CUS
, (SELECT SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CUST_ACCT.STATUS STATUS
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) THP
, OTA_BOOKING_DEALS OBD
, (SELECT SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CUST_ACCT.STATUS STATUS
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) CU
, (SELECT SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB( PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, NULL LAST_NAME_ALT
, NULL FIRST_NAME_ALT
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID) ICO
, OTA_FINANCE_HEADERS TFH
, OTA_FINANCE_LINES TFL
, PER_ADDRESSES ADDR
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORG2
, PER_ALL_ASSIGNMENTS_F ASG1
, PER_ALL_PEOPLE_F DEL
, PER_ALL_ASSIGNMENTS_F ASG2
, PER_ALL_PEOPLE_F SPN
, (SELECT SUBSTRB( PARTY.PERSON_LAST_NAME
, 1
, 50) LAST_NAME
, SUBSTRB( PARTY.PERSON_FIRST_NAME
, 1
, 40) FIRST_NAME
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, ACCT_ROLE.CUST_ACCOUNT_ID CUSTOMER_ID
, NULL LAST_NAME_ALT
, NULL FIRST_NAME_ALT
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID) DLC
, OTA_ACTIVITY_VERSIONS_TL TAV
, OTA_EVENTS EVT
, OTA_EVENTS_TL ENT
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, OTA_DELEGATE_BOOKINGS TDB
, PER_BUSINESS_GROUPS BG
WHERE TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND BST.LANGUAGE = USERENV('LANG')
AND TDB.EVENT_ID = EVT.EVENT_ID
AND ENT.EVENT_ID = EVT.EVENT_ID
AND ENT.LANGUAGE= USERENV('LANG')
AND EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.LANGUAGE (+)= USERENV('LANG')
AND TDB.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND TDB.CONTACT_ADDRESS_ID = CAD.ADDRESS_ID (+)
AND TDB.CONTACT_ID = CON.CONTACT_ID (+)
AND TDB.DELEGATE_CONTACT_ID = DLC.CONTACT_ID (+)
AND TDB.THIRD_PARTY_CUSTOMER_ID = THP.CUSTOMER_ID (+)
AND TDB.THIRD_PARTY_CONTACT_ID = THC.CONTACT_ID (+)
AND TDB.THIRD_PARTY_ADDRESS_ID = THA.ADDRESS_ID (+)
AND TDB.BOOKING_ID = TFL.BOOKING_ID (+)
AND TFL.CANCELLED_FLAG (+) = 'N'
AND TFL.FINANCE_HEADER_ID = TFH.FINANCE_HEADER_ID (+)
AND TFH.CUSTOMER_ID = CU.CUSTOMER_ID (+)
AND TFH.CONTACT_ID = ICO.CONTACT_ID (+)
AND TFL.BOOKING_DEAL_ID = OBD.BOOKING_DEAL_ID (+)
AND TDB.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND TDB.SPONSOR_PERSON_ID = SPN.PERSON_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN NVL(SPN.EFFECTIVE_START_DATE
, TDB.DATE_BOOKING_PLACED)
AND NVL(SPN.EFFECTIVE_END_DATE
, TDB.DATE_BOOKING_PLACED)
AND TDB.SPONSOR_ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID (+)
AND (ASG2.ROWID (+) = OTA_TDB_BUS.ASSIGNMENT_OK('CONTACT'
, TDB.SPONSOR_ASSIGNMENT_ID
, TDB.EVENT_ID
, TDB.DATE_BOOKING_PLACED))
AND ASG2.ORGANIZATION_ID = ORG2.ORGANIZATION_ID (+)
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID (+)
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)) OR (TDB.DATE_BOOKING_PLACED BETWEEN NVL(DEL.EFFECTIVE_START_DATE
, TDB.DATE_BOOKING_PLACED)
AND NVL(DEL.EFFECTIVE_END_DATE
, TDB.DATE_BOOKING_PLACED)
AND NOT EXISTS (SELECT PERSON_ID
FROM PER_ALL_PEOPLE_F PAP
WHERE PAP.PERSON_ID = TDB.DELEGATE_PERSON_ID
AND NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(PAP.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(PAP.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))) ))
AND TDB.DELEGATE_ASSIGNMENT_ID = ASG1.ASSIGNMENT_ID (+)
AND (ASG1.ROWID (+) = OTA_TDB_BUS.ASSIGNMENT_OK('STUDENT'
, TDB.DELEGATE_ASSIGNMENT_ID
, TDB.EVENT_ID
, TDB.DATE_BOOKING_PLACED))
AND ASG1.ORGANIZATION_ID = ORG1.ORGANIZATION_ID (+)
AND TDB.PERSON_ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND TDB.AUTHORIZER_PERSON_ID = AUT.USER_ID (+)
AND BG.BUSINESS_GROUP_ID = TDB.BUSINESS_GROUP_ID
AND DECODE(ORG.ORGANIZATION_ID
, NULL
, '1'
, ORG.LANGUAGE) = DECODE(ORG.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND DECODE(ORG1.ORGANIZATION_ID
, NULL
, '1'
, ORG1.LANGUAGE) =DECODE(ORG1.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND DECODE(ORG2.ORGANIZATION_ID
, NULL
, '1'
, ORG2.LANGUAGE) =DECODE(ORG2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(CAD.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(THA.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )

Columns