OTA Attendance Signature Sheet

Description
Categories: BI Publisher, Human Resources
Application: Learning Management
Source: Attendance Signature Sheet Report (XML)
Short Name: OTARPATT_XML
DB package: OTA_OTARPATT_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,
        etl.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,
       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_OTARPATT_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, ftv.TERRITORY_SHORT_NAME,
	OTA_OTARPATT_XMLP_PKG.cf_optional_columnformula() CF_OPTIONAL_COLUMN,
	OTA_OTARPATT_XMLP_PKG.cf_venueformula(evt.event_id) CF_venue
/* Commented during DT Fixes
	OTA_OTARPATT_XMLP_PKG.cf_venueformula(evt.event_id) CF_venue,
	OTA_OTARPATT_XMLP_PKG.CP_venue_p CP_venue
End of DT Fix Comment */
From  ota_events evt,
        ota_events_tl etl,
        ota_activity_versions_tl avt,
        ota_activity_versions acv,
        ota_delegate_bookings db,
        per_all_people_f del,
        (select substrb( PARTY.person_last_name,1,50)  LAST_NAME,
         	    substrb( PARTY.person_first_name,1,40)  FIRST_NAME,
	    party.person_pre_name_adjunct title,
      	   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)  LAST_NAME,
           		substrb( PARTY.person_first_name,1,40)  FIRST_NAME,
	   	 party.person_pre_name_adjunct title,
       		  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 substrb(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 bst,
        oe_hold_definitions hdf,
        oe_hold_sources_all hsrc,
        oe_order_holds_all hld,
        oe_order_lines_all lns,
 FND_TERRITORIES_VL ftv, HR_LOCATIONS_ALL loc
Where       evt.activity_version_id = acv.activity_version_id(+)
       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     evt.location_id =loc.location_id(+)
       and     loc.country = ftv.territory_code(+)
       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 evt.event_type NOT IN ('DEVELOPMENT', 'SESSION')
      and bst.type = 'P'
      and evt.business_group_id = :p_business_group_id
      and ota_admin_access_util.admin_can_access_object('CL',evt.event_id)='Y'
      and etl.event_id = evt.event_id
      and etl.language = Userenv('LANG')
      and avt.activity_version_id = acv.activity_version_id
      and avt.language = userenv('LANG')
&p_and
ORDER BY nvl(del.order_name,del.full_name), company, employee_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