OTA Learner Sign-In Sheet
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Attendance Signature Sheet Report
Application: Learning Management
Source: Learner Sign-In Sheet (XML)
Short Name: OTARPSIS_XML
DB package: OTA_OTARPSIS_XMLP_PKG
Description: Attendance Signature Sheet Report
Application: Learning Management
Source: Learner Sign-In Sheet (XML)
Short Name: OTARPSIS_XML
DB package: OTA_OTARPSIS_XMLP_PKG
Run
OTA Learner Sign-In Sheet and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Class |
|
LOV Oracle | |
Training Center |
|
LOV Oracle | |
Class Start Date |
|
Date | |
Class End Date |
|
Date | |
Enrollment Number |
|
Number | |
Optional Column Heading |
|
Char | |
Display Payment Confirmation |
|
LOV Oracle | |
Display Trainer Signature |
|
LOV Oracle |