[Home] [Help]
PACKAGE BODY: APPS.PQP_RIW_COURSE_WRAPPER
Source
1 PACKAGE BODY pqp_riw_course_wrapper as
2 /* $Header: pqpriwcowr.pkb 120.0.12010000.3 2009/01/21 13:50:15 sbrahmad noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_package varchar2(33) := 'pqp_riw_course_wrapper.';
8 --
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< InsUpd_Course >-----------------------|
11 -- ----------------------------------------------------------------------------
12 PROCEDURE InsUpd_Course
13 (p_effective_date in date
14 ,p_validate in number default hr_api.g_false_num
15 ,p_activity_id in number
16 ,p_superseded_by_act_version_id in number default null
17 ,p_developer_organization_id in number
18 ,p_controlling_person_id in number default null
19 ,p_version_name in varchar2
20 ,p_comments in varchar2 default null
21 ,p_description in varchar2 default null
22 ,p_duration in number default null
23 ,p_duration_units in varchar2 default null
24 ,p_end_date in date default null
25 ,p_intended_audience in varchar2 default null
26 ,p_language_id in number default null
27 ,p_maximum_attendees in number default null
28 ,p_minimum_attendees in number default null
29 ,p_objectives in varchar2 default null
30 ,p_start_date in date default null
31 ,p_success_criteria in varchar2 default null
32 ,p_user_status in varchar2 default null
33 ,p_vendor_id in number default null
34 ,p_actual_cost in number default null
35 ,p_budget_cost in number default null
36 ,p_budget_currency_code in varchar2 default null
37 ,p_expenses_allowed in varchar2 default null
38 ,p_professional_credit_type in varchar2 default null
39 ,p_professional_credits in number default null
40 ,p_maximum_internal_attendees in number default null
41 ,p_tav_information_category in varchar2 default null
42 ,p_tav_information1 in varchar2 default null
43 ,p_tav_information2 in varchar2 default null
44 ,p_tav_information3 in varchar2 default null
45 ,p_tav_information4 in varchar2 default null
46 ,p_tav_information5 in varchar2 default null
47 ,p_tav_information6 in varchar2 default null
48 ,p_tav_information7 in varchar2 default null
49 ,p_tav_information8 in varchar2 default null
50 ,p_tav_information9 in varchar2 default null
51 ,p_tav_information10 in varchar2 default null
52 ,p_tav_information11 in varchar2 default null
53 ,p_tav_information12 in varchar2 default null
54 ,p_tav_information13 in varchar2 default null
55 ,p_tav_information14 in varchar2 default null
56 ,p_tav_information15 in varchar2 default null
57 ,p_tav_information16 in varchar2 default null
58 ,p_tav_information17 in varchar2 default null
59 ,p_tav_information18 in varchar2 default null
60 ,p_tav_information19 in varchar2 default null
61 ,p_tav_information20 in varchar2 default null
62 ,p_inventory_item_id in number default null
63 ,p_organization_id in number default null
64 ,p_rco_id in number default null
65 ,p_version_code in varchar2 default null
66 ,p_keywords in varchar2 default null
67 ,p_business_group_id in number default null
68 ,p_activity_version_id in number default null
69 ,p_object_version_number in number default null
70 ,p_return_status out nocopy varchar2
71 ,p_data_source in varchar2 default null
72 ,p_competency_update_level in varchar2 default null
73 ,P_CRT_UPD in varchar2 default null
74 ) is
75 -- =============================================================================
76 -- Variables for API Boolean parameters
77 -- =============================================================================
78
79 l_validate boolean;
80
81 -- =============================================================================
82 -- Other variables
83 -- =============================================================================
84
85 l_activity_version_id number;
86 l_activity_category varchar2(72);
87 l_primary_flag varchar2(72):='Y';
88 l_aci_information_category varchar2(72);
89 l_category_usage_id number(9,0);
90 p_aci_information1 varchar2(72);
91 p_aci_information2 varchar2(72);
92 p_aci_information3 varchar2(72);
93 p_aci_information4 varchar2(72);
94 p_aci_information5 varchar2(72);
95 p_aci_information6 varchar2(72);
96 p_aci_information7 varchar2(72);
97 p_aci_information8 varchar2(72);
98 p_aci_information9 varchar2(72);
99 p_aci_information10 varchar2(72);
100 p_aci_information11 varchar2(72);
101 p_aci_information12 varchar2(72);
102 p_aci_information13 varchar2(72);
103 p_aci_information14 varchar2(72);
104 p_aci_information15 varchar2(72);
105 p_aci_information16 varchar2(72);
106 p_aci_information17 varchar2(72);
107 p_aci_information18 varchar2(72);
108 p_aci_information19 varchar2(72);
109 p_aci_information20 varchar2(72);
110 l_error_msg varchar2(4000);
111 -- =============================================================================
112 -- ~ Package Body Cursor variables:
113 -- =============================================================================
114
115 Cursor C_Sel_one is
116 Select ota_activity_definitions.CATEGORY_USAGE_ID
117 from ota_activity_definitions
118 where activity_id = p_activity_id;
119
120 -- =============================================================================
121 -- Default_Record_Values:
122 -- =============================================================================
123 l_proc varchar2(72) := g_package ||'InsUpd_Course';
124
125 l_create_flag number(2) := 1;
126 e_upl_not_allowed exception; -- when mode is 'View Only'
127 e_crt_not_allowed exception; -- when mode is 'Update Only'
128 g_upl_err_msg varchar2(100) := 'Upload NOT allowed.';
129 g_crt_err_msg varchar2(100) := 'Creating NOT allowed.';
130 l_act_ver_id number(9,0);
131 l_obj_ver_num number(2);
132 l_object_version_number number(2);
133
134
135 Begin
136 --hr_utility.trace_on(null, 'Course_Trace');
137 hr_utility.set_location(' Entering:' || l_proc,10);
138 --
139 -- Issue a savepoint
140 --
141 savepoint pqp_riw_course_wrapper;
142 --
143 -- Convert constant values to their corresponding boolean value
144 --
145 l_validate :=
146 hr_api.constant_to_boolean
147 (p_constant_value => p_validate);
148 --
149 -- Register Surrogate ID or user key values
150 --
151 ota_tav_ins.set_base_key_value
152 (p_activity_version_id => p_activity_version_id
153 );
154 --
155 -- Call API
156 --
157 l_activity_version_id := p_activity_version_id;
158 hr_utility.set_location('The version id is : '||l_activity_version_id, 89);
159 if l_activity_version_id is not null then
160 l_create_flag := 2; --update course
161 else
162 l_create_flag := 1; --create course
163 end if;
164
165 if (P_CRT_UPD = 'D') then
166 raise e_upl_not_allowed; -- View only flag is enabled but Trying to Upload
167 end if;
168 if (P_CRT_UPD = 'U' and l_create_flag = 1) then
169 raise e_crt_not_allowed; -- Update only flag is enabled but Trying to Create
170 end if;
171
172 if(l_create_flag = 1) then
173 ota_activity_version_api.create_activity_version
174 (p_effective_date => p_effective_date
175 ,p_validate => l_validate
176 ,p_activity_id => p_activity_id
177 ,p_superseded_by_act_version_id => p_superseded_by_act_version_id
178 ,p_developer_organization_id => p_developer_organization_id
179 ,p_controlling_person_id => p_controlling_person_id
180 ,p_version_name => p_version_name
181 ,p_comments => p_comments
182 ,p_description => p_description
183 ,p_duration => p_duration
184 ,p_duration_units => p_duration_units
185 ,p_end_date => p_end_date
186 ,p_intended_audience => p_intended_audience
187 ,p_language_id => p_language_id
188 ,p_maximum_attendees => p_maximum_attendees
189 ,p_minimum_attendees => p_minimum_attendees
190 ,p_objectives => p_objectives
191 ,p_start_date => p_start_date
192 ,p_success_criteria => p_success_criteria
193 ,p_user_status => p_user_status
194 ,p_vendor_id => p_vendor_id
195 ,p_actual_cost => p_actual_cost
196 ,p_budget_cost => p_budget_cost
197 ,p_budget_currency_code => p_budget_currency_code
198 ,p_expenses_allowed => p_expenses_allowed
199 ,p_professional_credit_type => p_professional_credit_type
200 ,p_professional_credits => p_professional_credits
201 ,p_maximum_internal_attendees => p_maximum_internal_attendees
202 ,p_tav_information_category => p_tav_information_category
203 ,p_tav_information1 => p_tav_information1
204 ,p_tav_information2 => p_tav_information2
205 ,p_tav_information3 => p_tav_information3
206 ,p_tav_information4 => p_tav_information4
207 ,p_tav_information5 => p_tav_information5
208 ,p_tav_information6 => p_tav_information6
209 ,p_tav_information7 => p_tav_information7
210 ,p_tav_information8 => p_tav_information8
211 ,p_tav_information9 => p_tav_information9
212 ,p_tav_information10 => p_tav_information10
213 ,p_tav_information11 => p_tav_information11
214 ,p_tav_information12 => p_tav_information12
215 ,p_tav_information13 => p_tav_information13
216 ,p_tav_information14 => p_tav_information14
217 ,p_tav_information15 => p_tav_information15
218 ,p_tav_information16 => p_tav_information16
219 ,p_tav_information17 => p_tav_information17
220 ,p_tav_information18 => p_tav_information18
221 ,p_tav_information19 => p_tav_information19
222 ,p_tav_information20 => p_tav_information20
223 ,p_inventory_item_id => p_inventory_item_id
224 ,p_organization_id => p_organization_id
225 ,p_rco_id => p_rco_id
226 ,p_version_code => p_version_code
227 ,p_keywords => p_keywords
228 ,p_business_group_id => p_business_group_id
229 ,p_activity_version_id => l_act_ver_id
230 ,p_object_version_number => l_obj_ver_num
231 ,p_data_source => p_data_source
232 ,p_competency_update_level => p_competency_update_level
233 );
234
235 hr_utility.set_location('The code has created success', 90);
236 Open C_Sel_one;
237 Fetch C_Sel_one into l_category_usage_id;
238 Close C_Sel_one;
239 hr_utility.set_location('After that success as well', 90);
240
241
242
243 ota_activity_category_api.create_act_cat_inclusion
244 (p_validate => l_validate
245 ,p_effective_date => p_effective_date
246 ,p_activity_version_id => l_act_ver_id
247 ,p_activity_category => l_activity_category
248 ,p_comments => p_comments
249 ,p_object_version_number => l_obj_ver_num
250 ,p_aci_information_category => l_aci_information_category
251 ,p_aci_information1 => p_aci_information1
252 ,p_aci_information2 => p_aci_information2
253 ,p_aci_information3 => p_aci_information3
254 ,p_aci_information4 => p_aci_information4
255 ,p_aci_information5 => p_aci_information5
256 ,p_aci_information6 => p_aci_information6
257 ,p_aci_information7 => p_aci_information7
258 ,p_aci_information8 => p_aci_information8
259 ,p_aci_information9 => p_aci_information9
260 ,p_aci_information10 => p_aci_information10
261 ,p_aci_information11 => p_aci_information11
262 ,p_aci_information12 => p_aci_information12
263 ,p_aci_information13 => p_aci_information13
264 ,p_aci_information14 => p_aci_information14
265 ,p_aci_information15 => p_aci_information15
266 ,p_aci_information16 => p_aci_information16
267 ,p_aci_information17 => p_aci_information17
268 ,p_aci_information18 => p_aci_information18
269 ,p_aci_information19 => p_aci_information19
270 ,p_aci_information20 => p_aci_information20
271 ,p_start_date_active => null
272 ,p_end_date_active => null
273 ,p_primary_flag => l_primary_flag
274 ,p_category_usage_id => l_category_usage_id
275 );
276 end if;
277
278 if l_create_flag = 2 then
279 hr_utility.set_location('Inside the update', 90);
280
281 select object_version_number into l_object_version_number from
282 ota_activity_versions where activity_version_id = l_activity_version_id;
283
284 ota_activity_version_api.update_activity_version
285 (
286 p_effective_date => p_effective_date,
287 p_activity_version_id => l_activity_version_id,
288 p_activity_id => p_activity_id,
289 p_superseded_by_act_version_id => p_superseded_by_act_version_id,
290 p_developer_organization_id => p_developer_organization_id,
291 p_controlling_person_id => p_controlling_person_id,
292 p_object_version_number => l_object_version_number,
293 p_version_name => p_version_name,
294 p_comments => p_comments,
295 p_description => p_description,
296 p_duration => p_duration,
297 p_duration_units => p_duration_units,
298 p_end_date => p_end_date,
299 p_intended_audience => p_intended_audience,
300 p_language_id => p_language_id,
301 p_maximum_attendees => p_maximum_attendees,
302 p_minimum_attendees => p_minimum_attendees,
303 p_objectives => p_objectives,
304 p_start_date => p_start_date,
305 p_success_criteria => p_success_criteria,
306 p_user_status => p_user_status,
307 p_vendor_id => p_vendor_id,
308 p_actual_cost => p_actual_cost,
309 p_budget_cost => p_budget_cost,
310 p_budget_currency_code => p_budget_currency_code,
311 p_expenses_allowed => p_expenses_allowed,
312 p_professional_credit_type => p_professional_credit_type,
313 p_professional_credits => p_professional_credits,
314 p_maximum_internal_attendees => p_maximum_internal_attendees
315 ,p_tav_information_category => p_tav_information_category
316 ,p_tav_information1 => p_tav_information1
317 ,p_tav_information2 => p_tav_information2
318 ,p_tav_information3 => p_tav_information3
319 ,p_tav_information4 => p_tav_information4
320 ,p_tav_information5 => p_tav_information5
321 ,p_tav_information6 => p_tav_information6
322 ,p_tav_information7 => p_tav_information7
323 ,p_tav_information8 => p_tav_information8
324 ,p_tav_information9 => p_tav_information9
325 ,p_tav_information10 => p_tav_information10
326 ,p_tav_information11 => p_tav_information11
327 ,p_tav_information12 => p_tav_information12
328 ,p_tav_information13 => p_tav_information13
329 ,p_tav_information14 => p_tav_information14
330 ,p_tav_information15 => p_tav_information15
331 ,p_tav_information16 => p_tav_information16
332 ,p_tav_information17 => p_tav_information17
333 ,p_tav_information18 => p_tav_information18
334 ,p_tav_information19 => p_tav_information19
335 ,p_tav_information20 => p_tav_information20
336 ,p_inventory_item_id => p_inventory_item_id
337 ,p_organization_id => p_organization_id
338 ,p_rco_id => p_rco_id
339 ,p_version_code => p_version_code
340 ,p_keywords => p_keywords
341 ,p_business_group_id => p_business_group_id
342 ,p_data_source => p_data_source
343 ,p_competency_update_level => p_competency_update_level
344 );
345 end if;
346
347
348
349 hr_utility.set_location(' Leaving:' || l_proc,20);
350
351 exception
352
353 when e_upl_not_allowed then
354 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
355 hr_utility.set_message_token('GENERIC_TOKEN',g_upl_err_msg);
356 hr_utility.set_location('Leaving: ' || l_proc, 90);
357 hr_utility.raise_error;
358 when e_crt_not_allowed then
359 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
360 hr_utility.set_message_token('GENERIC_TOKEN',g_crt_err_msg);
361 hr_utility.set_location('Leaving: ' || l_proc, 100);
362 hr_utility.raise_error;
363 when others then
364 --l_error_msg := Substr(SQLERRM,1,2000);
365 hr_utility.set_location('SQLCODE :' || SQLCODE,90);
366 hr_utility.set_location('SQLERRM :' || SQLERRM,90);
367 --hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
368 --hr_utility.set_message_token('GENERIC_TOKEN',substr(l_error_msg,1,500) );
369 hr_utility.set_location(' Leaving:' || l_proc,50);
370 hr_utility.raise_error;
371
372 end InsUpd_Course;
373 end pqp_riw_course_wrapper;