1 PACKAGE OTA_CLASSIC_UPGRADE AUTHID CURRENT_USER AS
2 /* $Header: otclassicupg.pkh 120.2.12000000.2 2007/02/13 13:59:51 vkkolla noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |--------------------------< Upgrade_Category >----------------------------|
6 -- ----------------------------------------------------------------------------
7 -- This procedure does the following :
8 -- 1. Updates ota_booking_deals.category with category_usage_id
9 -- 2. Updates ota_category_usages.Category with Meaning from
10 -- lookup table (earlier it stores lookup code)
11 -- 3. Populates the category translation table ota_category_usages_tl
12
13 procedure Upgrade_Category(
14 p_process_control IN varchar2,
15 p_start_pkid IN number,
16 p_end_pkid IN number,
17 p_rows_processed OUT nocopy number,
18 p_update_id in number default 1);
19
20 -- ----------------------------------------------------------------------------
21 -- |-------------------< Create_Activity_for_Category >-----------------------|
22 -- ----------------------------------------------------------------------------
23 -- This procedure creates the Activity typ for each category exists in
24 -- Ota_category_usages table.
25 procedure Create_Activity_for_Category(
26 p_process_control IN varchar2,
27 p_start_pkid IN number,
28 p_end_pkid IN number,
29 p_rows_processed OUT nocopy number,
30 p_update_id in number default 1 );
31
32
33 -- ----------------------------------------------------------------------------
34 -- |-------------------< Create_Category_for_Activity >-----------------------|
35 -- ----------------------------------------------------------------------------
36 -- This procedure does the following
37 -- 1. Creates a Category for each BG in ota_activity_definitions
38 -- and ota_category_usages. And this new category will be the
39 -- parent category for other categories(belongs to that BG).
40 -- 2. Creates Category for each Activity types, which are not
41 -- as part of step 3. Attaches the newly created category to
42 -- Activity versions exist under the equivalent Activity type.
43 -- If NO primary category specified for that Activity version,
44 -- then newly created category will be the primary.
45
46 procedure Create_Category_for_Activity(
47 p_process_control IN varchar2,
48 p_start_pkid IN number,
49 p_end_pkid IN number,
50 p_rows_processed OUT nocopy number,
51 p_update_id in number default 1 );
52
53 -- ----------------------------------------------------------------------------
54 -- |------------------------< Create_Offerings >----------------------------|
55 -- ----------------------------------------------------------------------------
56 -- This procedure creates the Offering based on the records exists in ota_events
57 -- and ota_activity_versions table.
58 Procedure Create_Offering(
59 p_process_control IN varchar2,
60 p_start_pkid IN number,
61 p_end_pkid IN number,
62 p_rows_processed OUT nocopy number,
63 p_update_id in number default 1 );
64
65
66 Procedure add_log_entry(p_upgrade_id in number
67 ,p_table_name in varchar2
68 ,p_business_group_id in number default null
69 ,p_source_primary_key in varchar2
70 ,p_object_value in varchar2 default null
71 ,p_message_text in varchar2 default null
72 ,p_process_date in date
73 ,p_log_type in varchar2 default null
74 ,p_upgrade_name in varchar2 default null);
75
76 Procedure create_ctg_dm_for_act_bg(p_update_id in number default 1 );
77
78 Procedure Migrate_Lookup (p_update_id in number default 1 );
79
80 function get_process_date (p_upgrade_id in number
81 ,p_upgrade_name in varchar2)
82 return date;
83
84
85 function get_apps_timezone(ila_tzone in varchar2) return varchar2;
86 -- Required to be able to use get_apps_timezone in update statement in Upgrade_Events
87 pragma restrict_references (get_apps_timezone , WNDS);
88
89 Procedure Upgrade_Events( p_process_control IN varchar2,
90 p_start_pkid IN number,
91 p_end_pkid IN number,
92 p_rows_processed OUT nocopy number,
93 p_update_id in number default 1 ) ;
94
95
96 Procedure Upgrade_Event_Associations(
97 p_process_control IN varchar2,
98 p_start_pkid IN number,
99 p_end_pkid IN number,
100 p_rows_processed OUT nocopy number,
101 p_update_id in number default 1 ) ;
102
103
104 function get_offering_name_with_lang(p_off_name in varchar2,
105 p_language_id in number,
106 p_language in varchar2 )
107 return varchar2;
108 pragma restrict_references (get_offering_name_with_lang , WNDS);
109
110
111 procedure submit_upgrade_report;
112
113 procedure validate_proc_for_hr_upg(do_upg out nocopy varchar2) ;
114
115
116 -- To be called as the last procedure in ottadupg.sql
117 procedure upgrade_root_category_dates;
118
119 procedure upgrade_online_delivery_modes (p_upgrade_id in number default 1);
120
121 procedure upgrade_act_cat_inclusions;
122
123 procedure create_root_ctg_and_dms;
124
125 procedure migrate_tad_dff_contexts (p_upgrade_id in number default 1);
126
127
128 -- ----------------------------------------------------------------------------
129 -- |---------------------< upgrade_lp_history_flag >--------------------------|
130 -- ----------------------------------------------------------------------------
131 -- This procedure will update ota_lp_enrollments table record is_history_flag to 'Y'
132 -- where learning path enrollment status is 'Completed'
133
134 PROCEDURE upgrade_lp_history_flag(
135 p_process_control IN varchar2,
136 p_start_rowid IN rowid,
137 p_end_rowid IN rowid,
138 p_rows_processed OUT nocopy number,
139 p_update_id in number default 1 ) ;
140
141
142
143 --enh 2733966 --
144 -- ----------------------------------------------------------------------------
145 -- |--------------------------< Upgrade_Language_Code >------------------------|
146 -- ----------------------------------------------------------------------------
147 -- This procedure will check if LANUGAGE_CODE is null in OTA_OFFERINGS ---------
148 -- OTA_LEARNING_OBJECTS, OTA_COMPETENCE_LANGUAGES and populate the corresponding-
149 -- fnd_natural_languages.Language_Code from fnd_languages.Language_Id
150
151 Procedure Upgrade_Language_Code;
152
153
154 -- ----------------------------------------------------------------------------
155 -- |---------------------< upg_tdb_history_att_flags>-------------------------|
156 -- ----------------------------------------------------------------------------
157 -- This procedure will update ota_delegate_bookings table records
158 -- a. successful_attendance_flag to 'Y' where it is NULL and enrollment status
159 -- is 'Attended'
160 -- b. is_history_flag to 'Y' where enrollment status is 'Attended'
161
162 PROCEDURE upg_tdb_history_att_flags(
163 p_process_control IN varchar2,
164 p_start_rowid IN rowid,
165 p_end_rowid IN rowid,
166 p_rows_processed OUT nocopy number,
167 p_update_id in number default 1 ) ;
168
169 End Ota_classic_upgrade ;