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.OTA_DELEGATE_BOOKINGS_V

Source

View Text - Preformatted


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 )

View Text - HTML Formatted

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 )