OTA Learner Sign-In Sheet

Description
Categories: BI Publisher, Human Resources
Application: Learning Management
Source: Learner Sign-In Sheet (XML)
Short Name: OTARPSIS_XML
DB package: OTA_OTARPSIS_XMLP_PKG
            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
Parameter Name SQL text Validation
Session Date
 
Business Group ID
 
Number
Display Trainer Signature
 
LOV Oracle
Display Payment Confirmation
 
LOV Oracle
Optional Column Heading
 
Enrollment Number
 
Number
Class End Date
 
Date
Class Start Date
 
Date
Training Center
 
LOV Oracle
Class
 
LOV Oracle