OTA Budget Costs Breakdown

Description
Categories: BI Publisher, Human Resources
Application: Learning Management
Source: Budget Costs Breakdown Report (XML)
Short Name: OTARPBUD_XML
DB package: OTA_OTARPBUD_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,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'
Parameter Name SQL text Validation
Standard Enrollment Price
 
Number
Payment Made
 
LOV Oracle
Class End Date
 
Date
Class Start Date
 
Date
Display Summary
 
LOV Oracle
Resource Booking Currency
 
LOV Oracle
Student Booking Currency
 
LOV Oracle
Resource Booking Status
 
LOV Oracle
Transfer Status
 
LOV Oracle
Program
 
LOV Oracle
Class
 
LOV Oracle
Course
 
LOV Oracle
Activity Type
 
LOV Oracle