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,dlc.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,
fl.transfer_status,
-- fl.currency_code,
fl.currency_code currency_code1,
nvl(e.standard_price,0) standard_price,
nvl(fl.money_amount,0) money_amount,
fl.finance_line_id,
-- e.event_id,
e.event_id event_id1,
OTA_OTARPBUD_XMLP_PKG.cf_conv_amountformula(:EVENT_ID, fl.currency_code, nvl ( fl.money_amount , 0 ), :CF_eff_date, :CF_currency_type) CF_conv,
OTA_OTARPBUD_XMLP_PKG.CP_prev_event_p CP_prev_event,
OTA_OTARPBUD_XMLP_PKG.CP_conv_p CP_conv
From ota_delegate_bookings db,
ota_events e,
ota_finance_lines fl,
ota_finance_headers fh,
ota_activity_versions av,
per_all_people_f del,
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,
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,
(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
Where 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.customer_id = rcu.customer_id(+)
and nvl(e.course_start_date, db.date_booking_placed) between
nvl(del.effective_start_date,
nvl(e.course_start_date, db.date_booking_placed)) and
nvl(del.effective_end_date,
nvl(e.course_start_date, db.date_booking_placed))
and db.booking_id = fl.booking_id(+)
and e.activity_version_id = av.activity_version_id(+)
and fl.finance_header_id = fh.finance_header_id(+)
and fl.cancelled_flag <> 'Y'
and e.event_id = db.event_id
and (upper(fl.currency_code) like upper(:p_delegate_booking_currency||'%') or :p_delegate_booking_currency is null)
and (upper(fl.transfer_status) like upper(:p_transfer_status||'%')
or :p_transfer_status is null)
and (e.standard_price <= nvl(:p_price,99999) or :p_price is null)
and (upper(fh.payment_status_flag) like upper(:p_payment_status||'%')
or :p_payment_status is null)
and e.event_id=:event_id
and ota_admin_access_util.admin_can_access_object('CL',e.event_id)='Y' |
