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