OTA Registration
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Registration Report
Application: Learning Management
Source: Registration Report (XML)
Short Name: OTARPREG_XML
DB package: OTA_OTARPREG_XMLP_PKG
Description: Registration Report
Application: Learning Management
Source: Registration Report (XML)
Short Name: OTARPREG_XML
DB package: OTA_OTARPREG_XMLP_PKG
Select evt.title, evt.event_id, fnd_date.date_to_displaydate(evt.course_start_date,2) course_start_date, fnd_date.date_to_displaydate(evt.course_end_date,2) course_end_date, decode(db.customer_id, null, decode(del.last_name,null,del2.last_name, del.last_name), dlc.last_name)||', '|| decode(db.customer_id, null, decode(del.last_name, null,decode(del2.title,null,null,del2.title||' '),decode(del.title, null, null,del.title||' ')), decode(dlc.title, null, null, dlc.title||' '))|| decode (db.customer_id, null, decode(del.first_name, null, del2.first_name, del.first_name),dlc.first_name) student_name, decode(db.customer_id, null, decode(del.last_name, null, del2.last_name, del.last_name), dlc.last_name) last_name, decode(db.corespondent, 'S',db.delegate_contact_phone,null) student_number, decode(db.customer_id, null, decode(del2.last_name,null, null, del2.last_name||', '), decode(dlc2.last_name, null, null,dlc2.last_name||', '))|| decode(db.customer_id, null, decode(del2.title, null, null,del2.title||' '), decode(dlc2.title, null, null, dlc2.title||' '))|| decode (db.customer_id, null, del2.first_name, dlc2.first_name) contact_name, decode(db.corespondent, 'C',db.delegate_contact_phone,null) contact_number, avt.version_name activity_name, tbst.name enrollment_status, fl.finance_header_id, fh.payment_status_flag, OTA_OTARPREG_XMLP_PKG.cf_venueformula(evt.event_id) CF_venue, OTA_OTARPREG_XMLP_PKG.CP_venue_p CP_venue From ota_delegate_bookings db, ota_events_vl evt, ota_finance_lines fl, ota_finance_headers fh, per_all_people_f del, ( select substrb( PARTY.person_last_name,1,50) FIRST_NAME, substrb( PARTY.person_first_name,1,40) LAST_NAME, ORG_CONT.title TITLE, ORG_CONT.contact_number, ACCT_ROLE.cust_account_role_id CONTACT_ID, ACCT_ROLE.cust_account_id CUSTOMER_ID 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, per_all_people_f del2, ( select substrb( PARTY.person_last_name,1,50) FIRST_NAME, substrb( PARTY.person_first_name,1,40) LAST_NAME, ORG_CONT.title TITLE, ORG_CONT.contact_number, ACCT_ROLE.cust_account_role_id CONTACT_ID, ACCT_ROLE.cust_account_id CUSTOMER_ID 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 ) dlc2, ota_booking_status_types_vl tbst, ( Select substr(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 ) rcu, ota_activity_versions acv, ota_activity_versions_tl avt, hr_all_organization_units org1, hr_all_organization_units org2 Where db.delegate_person_id = del.person_id(+) and db.delegate_contact_id = dlc.contact_id(+) and db.sponsor_person_id = del2.person_id(+) and db.contact_id = dlc2.contact_id(+) and evt.activity_version_id = acv.activity_version_id(+) and db.organization_id = org1.organization_id(+) and db.customer_id = rcu.customer_id(+) and db.booking_id = fl.booking_id(+) and fl.finance_header_id = fh.finance_header_id(+) and org2.organization_id(+) = evt.training_center_id and db.event_id = evt.event_id and db.booking_status_type_id = tbst.booking_status_type_id and avt.activity_version_id(+) = acv.activity_version_id and avt.language(+) = userenv('LANG') and ((db.internal_booking_flag = 'Y' and (trunc(evt.course_start_date) between decode(del.last_name, null, del2.effective_start_date, del.effective_start_date) and decode(del.last_name, null,del2.effective_end_date, del.effective_end_date) ) and (trunc(evt.course_start_date) between decode(del2.last_name, null, del.effective_start_date, del2.effective_start_date) and decode(del2.last_name, null, del.effective_end_date, del2.effective_end_date) )) or db.internal_booking_flag = 'N') and evt.event_type NOT IN ('DEVELOPMENT','SESSION') and ota_admin_access_util.admin_can_access_object('CL',evt.event_id)='Y' and tbst.type in ('A','P') and org1.business_group_id(+) = :p_business_group_id and evt.business_group_id = :p_business_group_id &p_and order by nvl(del.order_name,del.full_name) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Class |
|
LOV Oracle | |
Class Type |
|
LOV Oracle | |
Training Center |
|
LOV Oracle | |
Class Start Date |
|
Date | |
Class End Date |
|
Date |