OTA Budget Costs Breakdown

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'