OTA Registration

            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)