[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
23-Jan-02 115.2 HDSHAH 2193880 Cursor csr_get_date_time updated.
25-Jan-02 115.3 HDSHAH 2200017 Timezone included in event creation and update procedure calls.
28-Jan-02 115.4 HDSHAH 2201416 book_independent_flag parameter missing for create event procedure call.
29-Jan-02 115.5 HDSHAH 2201416 trunc() included for course_start_date, course_end_date,
enrollment_start_date,enrollment_end_date in included for
ota_evt_ins.ins and ota_evt_upd.upd procedure calls
29-Jan-02 115.6 DHMULIA 2201416 Added Trunc() to sysdate before calling ota_tav_ins.
30-Jan-02 115.7 HDSHAH 2201416 Added Trunc() to l_course_date parameter for ota_tav_upd procedure calls.
15-Feb-02 115.8 HDSHAH 2209467 p_start_date and p_end_date parameter type changed to varchar2 in
crt_or_chk_xml_prcs_tbl and upd_xml_prcs_tbl procedure.
21-Feb-02 115.9 HDSHAH 2236928 Log messages modified.
21-Feb-02 115.10 HDSHAH 2236928 Log messages modified.
16-APR-02 115.11 HDSHAH 2324698 Modified cur_get_date_time cursor in crt_or_upd_event procedure.
26-NOV-02 115.12 ARKASHYA 2684733 Included the NOCOPY directive in OUT and IN OUT parameters for procedures.
25-Mar-03 115.13 Pbhasin MLS changes added.
30-May-03 115.14 Arkashya 2984480 MLS Changes (Additional) Added calls to insert and update functions on _TL
tables for activity version and events.
24-Dec-03 115.15 arkashya Modified for eBS changed the call to ota_aci_api.ins to ota_aci_ins.ins
10-Jan-04 115.16 arkashya Modified for eBS changed the call to ota_tcu_api.ins to ota_ctu_ins.ins
Also added the call to ota_ctt_ins.ins_tl and defaulted synchronous_flag
and online_flag.
28-jun-04 115.17 ssur 3725560 Restricted import of newly created RCO and Offerings.
07-Jan-08 120.1 aabalakr 6683076, modified to include the new enrollment status, 'E'(Pending Evaluation)
*/
--------------------------------------------------------------------------------
g_package varchar2(33) := ' ota_ilearning.'; -- Global package name
p_update in varchar2
,p_rco_id in number
,p_language_code in varchar2
,p_activity_version_name in varchar2
,p_description in varchar2
,p_objectives in varchar2
,p_audience in varchar2
,p_business_group_id in number
,p_activity_definition_name in varchar2
,p_activity_version_id out nocopy number
,p_language_id out nocopy number
,p_status out nocopy varchar2
,p_message out nocopy varchar2
) is
no_language_id_found EXCEPTION;
select
activity_version_id,
language_id,
object_version_number
from
ota_activity_versions OAV
where
OAV.rco_id = p_rco_id and
OAV.developer_organization_id = p_business_group_id;
select
version_name
from
ota_activity_versions_vl OAV -- MLS change _vl added
where
OAV.version_name = p_activity_version_name and
OAV.developer_organization_id = p_business_group_id;
select
OAD.activity_id
from
ota_activity_definitions OAD
where
OAD.name = p_activity_definition_name and
OAD.business_group_id = p_business_group_id;
select
event_id,
object_version_number
from
ota_events
where
activity_version_id = l_activity_version_id and
business_group_id = p_business_group_id;
select
language_id
from
fnd_languages
where
language_code = p_language_code;
if p_update is null then -- if not only update
open cur_get_activity_version_id;
/* for Bug 2201416 Added Trunc when selecting sysdate */
select trunc(sysdate) into l_sysdate from dual;
--Bug 2984480 - arkashya MLS Changes calls to _TL row handler for Insert
ota_avt_ins.ins_tl
(
P_effective_date => l_sysdate
,P_language_code => USERENV('LANG')
,P_activity_version_id => l_activity_version_id
,P_version_name => p_activity_version_name
,P_description => p_description
,P_intended_audience => p_audience
,P_objectives => p_objectives
);
select trunc(sysdate) into l_sysdate from dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Activity Version for RCO ' ||
p_activity_version_name||'.');
p_message := 'updated successfully ';
fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Activity Version for RCO '||
p_activity_version_name || '. Reason:' || hr_utility.get_message);
p_message := 'updated successfully ';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Language ID for Event ID '|| cur_evt.event_id ||
'.');
p_message := 'Event updated successfully.Event id is -'||cur_evt.event_id||
' for Language id - '||l2_language_id;
fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the language ID for Event ID '||
cur_evt.event_id || '. Reason:' || hr_utility.get_message);
else -- if not only update
open cur_get_activity_version_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The RCO ' || p_activity_version_name || ' update failed because no activity version exists. Please return to OiL and update any Offering of this RCO.');
p_message := 'ERROR:no activity_version_id found for update corresponding to Rco_Id - '|| p_rco_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not update the Activity version for the RCO '||
p_activity_version_name || ' because the Language code '|| p_language_code || ' does not exist. Please return to OiL and correct the Language Name in the RCO.');
select trunc(sysdate) into l_sysdate from dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Activity Version for RCO ' ||
p_activity_version_name || '.');
p_message := 'updated successfully ';
fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Activity Version for RCO '||
p_activity_version_name || '. Reason:' || hr_utility.get_message);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Language ID for Event ID '|| cur_evt.event_id || '.');
p_message := 'Event updated successfully.Event id is -'||cur_evt.event_id||
' for Language id - '||l2_language_id;
fND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the language ID for Event ID '||
cur_evt.event_id || '. Reason:' || hr_utility.get_message);
end if; -- if not only update
select
event_id,
object_version_number,
event_type,
course_end_date,
enrolment_end_date,
event_status,
maximum_attendees,
-- to_date(to_char(Course_start_date,'DD-MON-YYYY')||Course_start_time,'DD-MON-YYYYHH24:MI'),
to_date(to_char(Course_start_date,'DD/MM/YYYY')||Course_start_time,'DD/MM/YYYYHH24:MI'),
owner_id
from
ota_events
where
offering_id = p_offering_id and
business_group_id = p_business_group_id;
select
category_usage_id
from
ota_category_usages
where
business_group_id = p_business_group_id and
type = 'DM' and
category = l_category;
select l_date,
-- select to_date(to_char(l_date,'DD/MM/YYYY')),
-- select to_date(to_char(l_date,'DD-MON-RRRR')),
--Bug#2324698 hdshah changed to MI instead of MM
-- to_char(l_date,'HH24:MM')
to_char(l_date,'HH24:MI')
from
dual;
select
'dummy'
from
ota_act_cat_inclusions
where
activity_version_id = p_activity_version_id and
category_usage_id = l_category_usage_id;
select
count(*)
from
ota_category_usages OCU,
ota_act_cat_inclusions OAC
where
OAC.activity_version_id = p_activity_version_id and
OAC.category_usage_id = OCU.category_usage_id and
OCU.type = 'DM' and
OAC.primary_flag = 'Y';
select
start_date,
object_version_number
from
ota_activity_versions
where
activity_version_id = p_activity_version_id;
select
count(*)
from
ota_delegate_bookings
where
event_id = l_event_id;
select count(*)
from ota_delegate_bookings
where booking_status_type_id in (SELECT booking_status_type_id
FROM ota_booking_status_types
WHERE type = 'W')
and event_id = l_event_id;
select count(*)
from ota_delegate_bookings
where booking_status_type_id in (SELECT booking_status_type_id
FROM ota_booking_status_types
WHERE type in ('A','P','E'))
and event_id = l_event_id;
select user_name
from fnd_user
where employee_id = l_owner_id;
select title
from ota_events_vl -- MLS change _vl added
where title = p_offering_title and
business_group_id = p_business_group_id;
select to_date('31/12/4712','DD/MM/YYYY') into l_course_end_date from dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application did not create or update the Event for the Offering '||
p_offering_title || '. You must return to OiL and enter a Start Date for the Offering.');
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Activity Start Date for Activity Version ID '||
p_activity_version_id || '. REASON:' || hr_utility.get_message);
p_message := 'ERROR:Unable to update Activity start date for activity_version_id - '||
p_activity_version_id || ' And offering Id - ' ||p_offering_id;
select trunc(sysdate) into l_sysdate from dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Offering '|| p_offering_title ||
', because you cannot change Offerings from Self-Paced to Scheduled or Scheduled to Self-Paced ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Activity Start Date for Activity Version ID '||
p_activity_version_id || '. REASON:' || hr_utility.get_message);
p_message := 'ERROR:Unable to update Activity start date for activity_version_id - '||
p_activity_version_id || ' And offering Id - ' ||p_offering_id;
select sysdate into l_sysdate from dual;
select sysdate into l_sysdate from dual;
SELECT to_char(sysdate,'DD/MM/YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
SELECT to_char(sysdate,'DD/MM/YYYY:HH24:MI:SS') INTO l_sysdatetime FROM dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update Maximum attendees to '||
p_offering_max_attendees || 'for the Event ' ||
p_offering_title || ' , because '|| l_total_placed ||
' students have already enrolled in the event.');
l_event_id || ' cannot be updated to '|| p_offering_max_attendees ||
' because '|| l_total_placed || ' students are already enrolled.';
select trunc(sysdate) into l_sysdate from dual;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully updated the Event '|| p_offering_title || '.');
p_message := 'Event updated successfully.Event id is -'||l_event_id||' for Offering id - '||p_offering_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The application could not update the Event '|| p_offering_title ||
'. Reason:' || hr_utility.get_message);
p_message := 'ERROR:Unable to update Event.Event id is -'||l_event_id||' for Offering id - '||p_offering_id;
l_update varchar2(10);
l_update := 'true';
l_update := NULL;
p_update => l_update -- (Input)
,p_rco_id => to_number(p_array(p_array_idx).rco_id) -- (Input)
,p_language_code => p_array(p_array_idx).rco_language -- (Input)
,p_activity_version_name => p_array(p_array_idx).rco_title -- (Input)
,p_description => p_array(p_array_idx).rco_description -- (Input)
,p_objectives => p_array(p_array_idx).rco_objective -- (Input)
,p_audience => p_array(p_array_idx).rco_audience -- (Input)
,p_business_group_id => to_number(p_business_group_id) -- (Input)
,p_activity_definition_name => p_activity_definition_name -- (Input)
,p_activity_version_id => l_activity_version_id -- (Output)
,p_language_id => l_language_id -- (Output)
,p_status => l_rco_status -- (Output)
,p_message => l_rco_message -- (Output)
);
l_update varchar2(10);
p_update => 'false' -- (Input)
,p_rco_id => to_number(p_array(p_array_idx).rco_id) -- (Input)
,p_language_code => p_array(p_array_idx).rco_language -- (Input)
,p_activity_version_name => p_array(p_array_idx).rco_title -- (Input)
,p_description => p_array(p_array_idx).rco_description -- (Input)
,p_objectives => p_array(p_array_idx).rco_objective -- (Input)
,p_audience => p_array(p_array_idx).rco_audience -- (Input)
,p_business_group_id => to_number(p_business_group_id) -- (Input)
,p_activity_definition_name => p_activity_definition_name -- (Input)
,p_activity_version_id => l_activity_version_id -- (Output)
,p_language_id => l_language_id -- (Output)
,p_status => l_rco_status -- (Output)
,p_message => l_message -- (Output)
);
select
to_date
from
ota_iln_xml_processes
where
executable_name = p_process_name and
business_group_id = l_business_group_id and
site_id = l_site_id;
select
site_id
from
ota_iln_xml_processes
where
executable_name = p_process_name and
business_group_id = l_business_group_id;
select
business_group_id
from
ota_iln_xml_processes
where
executable_name = p_process_name and
site_id = l_site_id;
select sysdate into l_sysdate from dual;
insert into ota_iln_xml_processes
(executable_name,
business_group_id,
site_id,
from_date,
to_date)
values (p_process_name,
l_business_group_id,
l_site_id,
pl_start_date,
pl_end_date);
/* --Do not need to update now. Created new procedure to update the table
update
ota_iln_xml_processes
set
from_date = p_start_date,
to_date = p_end_date
where
executable_name = p_process_name and
business_group_id = l_business_group_id and
site_id = l_site_id;
update
ota_iln_xml_processes
set
from_date = l_start_date,
to_date = l_end_date
where
executable_name = p_process_name and
business_group_id = l_business_group_id and
site_id = l_site_id;