[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_ccselectiontext in varchar2 default null
, p_offering_id in VARCHAR2
,p_booking_justification_id IN VARCHAR2 default null
)
as
l_transaction_id number default null;
hr_transaction_api.delete_transaction_step(p_validate => false
,p_transaction_step_id => l_old_transaction_step_id
,p_person_id => p_login_person_id
,p_object_version_number => l_old_object_version_number);
l_transaction_table(l_count).param_name := 'P_CCSELECTIONTEXT';
l_transaction_table(l_count).param_value := p_ccselectiontext;
p_message_name => 'HR_UPDATE_NOT_ALLOWED');
l_ccselectiontext varchar2(2000);
l_ccselectiontext := hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CCSELECTIONTEXT');
||'^'||nvl(l_ccselectiontext,' ')
||'^'||nvl(l_tdb_information_category,'null')
||'^'||nvl(l_tdb_information1,'null')
||'^'||nvl(l_tdb_information2,'null')
||'^'||nvl(l_tdb_information3,'null')
||'^'||nvl(l_tdb_information4,'null')
||'^'||nvl(l_tdb_information5,'null')
||'^'||nvl(l_tdb_information6,'null')
||'^'||nvl(l_tdb_information7,'null')
||'^'||nvl(l_tdb_information8,'null')
||'^'||nvl(l_tdb_information9,'null')
||'^'||nvl(l_tdb_information10,'null')
||'^'||nvl(l_tdb_information11,'null')
||'^'||nvl(l_tdb_information12,'null')
||'^'||nvl(l_tdb_information13,'null')
||'^'||nvl(l_tdb_information14,'null')
||'^'||nvl(l_tdb_information15,'null')
||'^'||nvl(l_tdb_information16,'null')
||'^'||nvl(l_tdb_information17,'null')
||'^'||nvl(l_tdb_information18,'null')
||'^'||nvl(l_tdb_information19,'null')
||'^'||nvl(l_tdb_information20,'null')
||'^'||nvl(l_trnorgnames,'null')
||'^'||nvl(l_offering_id,0)
||'^'||nvl(l_booking_justification, 'null');
SELECT hao.business_group_id,
evt.organization_id,
evt.currency_code,
evt.offering_id,
evt.owner_id,
ofr.activity_version_id,
evt.Title,
evt.course_start_date,
evt.course_end_date,
evt.business_group_id bg_id,
evt.course_start_time,
Ctl.Category,
-- Modified for bug 3389890 as usage of inline query in CURSOR is not supported in 8.1.7
-- (Select Category from ota_category_usages_tl where Category_Usage_Id = ofr.Delivery_Mode_Id
-- and Language = userenv('LANG')) Delivery_Mode,
ota_general.get_location_code(ota_utility.get_event_location(evt.event_id)) Location_Name,
ota_timezone_util.get_timezone_name(evt.timezone) timezone,
evt.course_end_time
FROM OTA_EVENTS_VL evt,
OTA_OFFERINGS ofr,
OTA_CATEGORY_USAGES_TL ctl,
HR_ALL_ORGANIZATION_UNITS hao
WHERE evt.event_id = l_eventid
AND evt.parent_offering_id = ofr.offering_id
AND evt.organization_id = hao.organization_id (+)
AND ctl.Category_usage_id = ofr.delivery_mode_id
AND ctl.language = userenv('LANG') ;
Select event_status, maximum_internal_attendees,nvl(price_basis,NULL),standard_price
from OTA_EVENTS
WHERE EVENT_ID = l_eventid;
SELECT count(*)
FROM OTA_DELEGATE_BOOKINGS dbt,
OTA_BOOKING_STATUS_TYPES bst
WHERE dbt.event_id = l_eventid
AND dbt.internal_booking_flag = 'Y'
AND dbt.booking_status_type_id = bst.booking_status_type_id
AND bst.type in ('P','A','E');
SELECT ov.booking_id,
ov.date_booking_placed,
ov.object_version_number
FROM ota_booking_status_types os,
ota_delegate_bookings ov
WHERE ov.event_id = p_event_id
AND ov.delegate_person_id = p_person_id
AND os.booking_status_type_id = ov.booking_status_type_id
AND os.type = 'R';
SELECT version_name
FROM OTA_ACTIVITY_VERSIONS_TL
WHERE activity_version_id = p_activity_version_id
AND language=userenv('LANG');
SELECT
USER_NAME
FROM
FND_USER
WHERE
Employee_id = p_owner_id
AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
select priority_level
from ota_bkng_justifications_b
where booking_justification_id = p_booking_justification_id;
select sysdate into l_current_date from dual;
update hr_api_transaction_values
set varchar2_value = 'APPROVE'
where transaction_step_id = p_transaction_step_id
and name = 'P_FROM';
ELSIF l_from = 'APPROVE' then -- update enrollment and create finance line if profile is set to YES
l_eventid := TO_NUMBER(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_EVENTID'));
select sysdate into l_current_date from dual;
ota_tdb_api_upd2.update_enrollment(
p_booking_id => l_booking_id,
p_event_id => l_eventid,
p_object_version_number => l_object_version_number,
p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
p_tfl_object_version_number => result_object_version_number,
-- p_update_finance_line => 'N',
p_update_finance_line => 'Y',
p_currency_code => l_event_currency_code,
p_standard_amount => l_standard_price,
p_money_amount => l_standard_price,
p_unitary_amount => null,
p_booking_deal_id => null,
p_booking_deal_type => 'N',
p_finance_header_id => result_finance_header_id,
p_finance_line_id => l_finance_line_id,
p_date_status_changed => l_current_date,
p_date_booking_placed => l_date_booking_placed);
UPDATE ota_finance_lines SET transfer_status = 'AT'
WHERE finance_line_id = l_finance_line_id;
ota_tdb_api_upd2.update_enrollment(
p_booking_id => l_booking_id,
p_event_id => l_eventid,
p_object_version_number => l_object_version_number,
p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
p_tfl_object_version_number => result_object_version_number,
p_finance_line_id => l_finance_line_id,
p_date_status_changed => l_current_date,
p_date_booking_placed => l_date_booking_placed);
/*Bug#2215026 Two separate update_enrollment procedure calls included for successful and unsuccessful finance creation.
--Bug#2221320 hdshah p_currency_code, p_standard_amount, p_money_amount, p_unitary_amount, p_booking_deal_id, p_booking_deal_type included.
-- p_update_finance_line parameter changed from 'N' to 'Y'.
*/
wf_engine.setItemAttrText (itemtype => l_item_type
,itemkey => l_item_key
,aname => 'BOOKING_STATUS_TYPE_ID'
,avalue => l_booking_status_row.booking_status_type_id);
ota_tdb_api_upd2.update_enrollment(
p_booking_id => l_booking_id,
p_event_id => l_eventid,
p_object_version_number => l_object_version_number,
p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
p_tfl_object_version_number => result_object_version_number,
p_finance_line_id => l_finance_line_id,
p_date_status_changed => l_current_date,
p_date_booking_placed => l_date_booking_placed);
SELECT ov.booking_id,
ov.date_booking_placed,
ov.object_version_number,
ov.business_group_id
FROM ota_booking_status_types os,
ota_delegate_bookings ov
WHERE ov.event_id = p_event_id
AND ov.delegate_person_id = p_person_id
AND os.booking_status_type_id = ov.booking_status_type_id
AND os.type = 'R';
select sysdate into l_current_date from dual;
ota_tdb_api_upd2.update_enrollment(
p_booking_id => l_booking_id,
p_event_id => l_eventid,
p_object_version_number => l_object_version_number,
p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
p_tfl_object_version_number => result_object_version_number,
p_finance_line_id => l_finance_line_id,
p_status_change_comments => null, --Bug 2359495
p_date_status_changed => l_current_date,
p_date_booking_placed => l_date_booking_placed,
p_source_cancel => 'AME');
Select
tcc.gl_set_of_books_id,
thg.SEGMENT
,thg.SEGMENT_NUM
,thg.HR_DATA_SOURCE
,thg.CONSTANT
,thg.HR_COST_SEGMENT
FROM OTA_HR_GL_FLEX_MAPS THG
,OTA_CROSS_CHARGES TCC
WHERE THG.Cross_charge_id = TCC.Cross_charge_id and
TCC.Business_group_id = p_business_group_id and
TCC.Type = 'E' and
TCC.FROM_TO = 'F' and
Trunc(sysdate) between tcc.start_date_active and nvl(tcc.end_date_active,sysdate)
ORDER BY thg.segment_num;
Select
tcc.gl_set_of_books_id,
thg.SEGMENT
,thg.SEGMENT_NUM
,thg.HR_DATA_SOURCE
,thg.CONSTANT
,thg.HR_COST_SEGMENT
FROM OTA_HR_GL_FLEX_MAPS THG
,OTA_CROSS_CHARGES TCC
WHERE THG.Cross_charge_id = TCC.Cross_charge_id and
TCC.Business_group_id = p_business_group_id_to and
TCC.Type = 'E' and
TCC.FROM_TO = 'T' and
Trunc(sysdate) between tcc.start_date_active and nvl(tcc.end_date_active,sysdate)
ORDER BY thg.segment_num;
SELECT
COST_ALLOCATION_KEYFLEX_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = l_organization_id;
SELECT CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = p_set_of_books_id;
SELECT hr.COST_ALLOCATION_KEYFLEX_ID
FROM HR_ALL_ORGANIZATION_UNITS hr ,
PER_ALL_ASSIGNMENTS_F asg
WHERE hr.organization_id = asg.organization_id and
asg.organization_id = p_organization_id and
asg.assignment_id = p_assignment_id and
trunc(sysdate) between asg.effective_start_date and
asg.effective_end_date;
SELECT hr.COST_ALLOCATION_KEYFLEX_ID
FROM HR_ALL_ORGANIZATION_UNITS hr ,
OTA_EVENTS EVT
WHERE hr.organization_id = evt.organization_id and
evt.event_id = p_event_id;
SELECT offering_id
FROM ota_events
where event_id= p_event_id;
SELECT COST_ALLOCATION_KEYFLEX_ID INTO l_cost_allocation_keyflex_id
FROM HR_ALL_ORGANIZATION_UNITS WHERE organization_id = p_business_group_id_from;
l_dynamicSqlString := 'SELECT ' ||from_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
l_dynamicSqlString := 'SELECT ' || from_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
/* SELECT hr.COST_ALLOCATION_KEYFLEX_ID INTO l_cost_allocation_keyflex_id
FROM HR_ALL_ORGANIZATION_UNITS hr ,
PER_ALL_ASSIGNMENTS_F asg
WHERE hr.organization_id = asg.organization_id and
asg.organization_id = p_organization_id and
asg.assignment_id = p_assignment_id ; */
l_dynamicSqlString := 'SELECT ' ||from_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
/* SELECT hr.COST_ALLOCATION_KEYFLEX_ID INTO l_cost_allocation_keyflex_id
FROM HR_ALL_ORGANIZATION_UNITS hr ,
OTA_EVENTS EVT
WHERE hr.organization_id = evt.organization_id and
evt.event_id = p_event_id; */
l_dynamicSqlString := 'SELECT ' ||from_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
SELECT COST_ALLOCATION_KEYFLEX_ID INTO l_cost_allocation_keyflex_id
FROM HR_ALL_ORGANIZATION_UNITS WHERE organization_id = p_business_group_id_to;
l_dynamicSqlString := 'SELECT ' ||to_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
l_dynamicSqlString := 'SELECT ' || to_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
l_dynamicSqlString := 'SELECT ' ||to_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
l_dynamicSqlString := 'SELECT ' ||to_rec.HR_COST_SEGMENT ||' FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = :txn ' ;
,P_transaction_type => 'INSERT');
SELECT usr.user_name
FROM OTA_EVENTS evt,
FND_USER USR
WHERE evt.event_id = p_event_id and
usr.employee_id = evt.owner_id
and trunc(sysdate) between usr.start_date and nvl(usr.end_date,to_date('4712/12/31', 'YYYY/MM/DD')) ; --Bug 5676892
select
--added after show n tell
oev.actual_cost, oev.budget_currency_code,
oev.parent_offering_id,oev.timezone
from ota_events oev
where
oev.event_id = l_event_id;
select ofe.language_id, ocu.category
from ota_offerings ofe, ota_category_usages_tl ocu
where ofe.delivery_mode_id = ocu.category_usage_id
and ocu.language=USERENV('LANG')
and ofe.offering_id = l_off_id;
SELECT usr.user_name
FROM OTA_EVENTS evt,
FND_USER USR
WHERE evt.event_id = p_event_id and
usr.employee_id = evt.owner_id
and trunc(sysdate) between usr.start_date and nvl(usr.end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
SELECT bst.Type
FROM OTA_DELEGATE_BOOKINGS tdb,
OTA_BOOKING_STATUS_TYPES bst
WHERE tdb.booking_id = p_booking_id
AND bst.booking_status_type_id = tdb.booking_status_type_id;
Select OCU.synchronous_flag, OCU.online_flag
From ota_events OEV,
ota_offerings OFR,
ota_category_usages OCU
Where OFR.offering_id = OEV.parent_offering_id
And OCU.category_usage_id = OFR.delivery_mode_id
And OEV.event_id = p_event_id;
Select
hrtrns.transaction_step_id
From
wf_item_activity_statuses process
,wf_item_attribute_values attribute2
,wf_process_activities activity
,hr_api_transaction_steps hrtrns
Where
activity.activity_name = p_process_name
and activity.process_item_type = 'HRSSA'
and activity.activity_item_type = 'HRSSA'
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = 'HRSSA'
and process.item_key = attribute2.item_key
and attribute2.item_type = process.item_type
and attribute2.name = 'TRAN_SUBMIT'
and attribute2.text_value = 'Y'
and process.item_key = hrtrns.item_key
and trim(upper(hrtrns.api_name)) = trim(upper(g_package||'.PROCESS_API2'))
and hrtrns.item_type = 'HRSSA'
and hrtrns.update_person_id = p_person_id;