[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_ccselectiontext in varchar2 default null
, p_oafunc in varchar2 default null
, p_processname in varchar2 default null
, p_calledfrom in varchar2 default null
, p_frommenu 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(200);
l_ccselectiontext := hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_CCSELECTIONTEXT');
p_review_data := nvl(l_eventid,0)||'^'||nvl(l_specialInstruction,'null')||'^'||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');
SELECT hao.business_group_id,
evt.organization_id,
evt.currency_code,
evt.offering_id,
evt.owner_id,
evt.activity_version_id,
evt.Title,
evt.course_start_date,
evt.course_end_date,
evt.business_group_id bg_id -- Bug#2215026 evt.business_group_id included.
FROM OTA_EVENTS_VL evt,
HR_ALL_ORGANIZATION_UNITS hao
WHERE evt.event_id = l_eventid
AND evt.organization_id = hao.organization_id (+); --Bug#2215026 (+) included.
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 ;
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);
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');