OTA Learner Sign-In Sheet

            Select  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,
                    db.booking_id registration_id,
                    evt.title 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,
                    evt.course_start_date course_start,
                    evt.course_end_date course_end,
                   avt.version_name,
                    evt.duration,
                    hr_general.decode_lookup ('FREQUENCY',evt.duration_units) duration_units,
                    decode(db.customer_id,null,org1.name,rcu.customer_name) company,
                    hr_general.decode_lookup ('FINANCE_HEADER_TYPE', fh.type) finance_header_type,
                   del.employee_number employee_id,
                   db.delegate_person_id person_id,
                   OTA_OTARPSIS_XMLP_PKG.cf_yes_no_meaning(decode(db.internal_booking_flag,
                                'N', decode(lns.flow_status_code,'ENTERED','N',nvl(hld.released_flag,'Y')),
                                'Y', decode(fl.finance_line_id, null, null, 'Y'))) pmt_confirmed,
                   fl.finance_line_id finance_line_id, ftv.territory_short_name territory_short_name,
                OTA_OTARPSIS_XMLP_PKG.cf_event_durationformula(evt.course_end_date, evt.course_start_date) CF_event_duration,
                OTA_OTARPSIS_XMLP_PKG.cf_sign1formula((evt.course_end_date - evt.course_start_date)+1) CF_Sign1,
                OTA_OTARPSIS_XMLP_PKG.cf_sign2formula((evt.course_end_date - evt.course_start_date)+1) CF_Sign2,
                OTA_OTARPSIS_XMLP_PKG.cf_sign3formula((evt.course_end_date - evt.course_start_date)+1) CF_Sign3,
                OTA_OTARPSIS_XMLP_PKG.cf_sign4formula((evt.course_end_date - evt.course_start_date)+1) CF_Sign4,
                OTA_OTARPSIS_XMLP_PKG.cf_optional_columnformula() CF_OPTIONAL_COLUMN,
                OTA_OTARPSIS_XMLP_PKG.cf_venueformula(evt.event_id) CF_venue,
                OTA_OTARPSIS_XMLP_PKG.CP_venue_p CP_venue
            From  ota_events_vl evt,
                    ota_activity_versions acv,
                    ota_activity_versions_tl avt,
                    ota_delegate_bookings db,
                    per_all_people_f del,FND_TERRITORIES_VL ftv, HR_LOCATIONS_ALL loc,
                         (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,
                    hr_all_organization_units org1,
                    hr_all_organization_units org2,
                    ota_finance_lines fl,
                    ota_finance_headers fh,
                    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,
                          (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_booking_status_types_vl bst,
                    oe_hold_definitions hdf,
                    oe_hold_sources_all hsrc,
                    oe_order_holds_all hld,
                    oe_order_lines_all lns
            Where       evt.activity_version_id = acv.activity_version_id(+)
                   and     avt.activity_version_id(+) = acv.activity_version_id
                   and     avt.language(+) = userenv('LANG')
                   and     fl.finance_header_id     = fh.finance_header_id(+)
                   and     fl.booking_id(+)             = db.booking_id
                   and     db.organization_id        = org1.organization_id(+)
                   and     db.customer_id              = rcu.customer_id(+)
                   and     db.event_id                     = evt.event_id
                   and     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   loc.country = ftv.territory_code(+)
            and  evt.location_id =loc.location_id(+)
                   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     db.booking_status_type_id = bst.booking_status_type_id
                   and (hld.order_hold_id = (select max(order_hold_id)
                                                              from oe_hold_sources_all hsrc2, oe_order_holds_all hld2
                                                              where hsrc2.hold_source_id = hld2.hold_source_id
                                                                  and hld2.header_id = hld.header_id
                                                                  and hsrc2.hold_id = hsrc.hold_id)
                           or nvl(hdf.name, 'X') not in ('Credit Card Auth Failure', 'Credit Check Failure'))
                  and hdf.hold_id (+) = hsrc.hold_id
                  and hsrc.hold_source_id (+) = hld.hold_source_id
                  and hld.header_id (+) = lns.header_id
                  and lns.line_id (+) = db.line_id
                  and org2.organization_id(+) = evt.training_center_id
                  and org2.business_group_id(+) = evt.business_group_id
                  and evt.event_type NOT IN ('DEVELOPMENT', 'SESSION')
                  and ota_admin_access_util.admin_can_access_object('CL',evt.event_id)='Y'
                  and bst.type = 'P'
                   and evt.business_group_id = :p_business_group_id
            &p_and
            --ORDER BY last_name, company, employee_id
			ORDER BY title,territory_short_name,company,course_end,course_start,student_name,finance_line_id