OTA Attendance Signature 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,
        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