[Home] [Help]
PACKAGE BODY: APPS.OT_WORKFLOW_SS
Source
1 PACKAGE BODY ot_workflow_ss AS
2 /* $Header: otwkflss.pkb 120.0.12010000.2 2009/07/07 06:11:52 pekasi ship $ */
3 /*
4 This package contails new (v4.0+)workflow related business logic
5 */
6 --
7 -- Package Variables
8 --
9 g_package varchar2(33) := 'ot_workflow_ss.';
10 --
11
12 -------------------------------------------------------------------------------
13 --------- function get_item_type --------------------------------------------
14
15 ---------- private function to get item type for current transaction ---------
16 -------------------------------------------------------------------------------
17 function get_item_type
18 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
19 return varchar2 is
20 c_item_type varchar2(50);
21
22 begin
23
24 SELECT DISTINCT ITEM_TYPE
25 INTO c_item_type
26 FROM HR_API_TRANSACTION_STEPS
27 WHERE TRANSACTION_ID=p_transaction_id;
28
29 return nvl(c_item_type, '-1');
30 EXCEPTION
31 WHEN OTHERS THEN
32 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
33 RAISE;
34
35 end get_item_type;
36
37
38
39
40
41
42 -------------------------------------------------------------------------------
43 --------- function get_item_key --------------------------------------------
44 ---------- private function to get item key for current transaction ---------
45 -------------------------------------------------------------------------------
46
47
48 function get_item_key
49 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
50 return varchar2 is
51 c_item_key varchar2(100);
52
53 begin
54
55 SELECT DISTINCT ITEM_KEY
56 INTO c_item_key
57 FROM HR_API_TRANSACTION_STEPS
58 WHERE TRANSACTION_ID=p_transaction_id;
59
60 return nvl(c_item_key, '-1');
61 EXCEPTION
62 WHEN OTHERS THEN
63 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
64 RAISE;
65
66
67
68 end get_item_key;
69
70
71
72 -- ------------------------------------------------------------------------
73 -- |------------------------< Get_event_standard_price >-------------------------|
74 -- ------------------------------------------------------------------------
75 --
76 -- Description
77 --
78 -- Get the event standard price
79 --
80
81 function get_event_standard_price
82 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
83 return number is
84 c_event_standard_price ota_events.standard_price%type;
85 c_event_id ota_events.event_id%type;
86 c_item_type varchar2(50);
87 c_item_key varchar2(100);
88
89 cursor c_get_standard_price is
90 select standard_price
91 from ota_events
92 where event_id = c_event_id;
93
94 begin
95
96 c_item_type := get_item_type(p_transaction_id);
97 c_item_key := get_item_key(p_transaction_id);
98
99 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
100 itemkey => c_item_key,
101 aname => 'OTA_EVENT_ID',
102 ignore_notfound => TRUE);
103 if c_event_id is null then
104 c_event_standard_price := null;
105 else
106 open c_get_standard_price;
107 fetch c_get_standard_price into c_event_standard_price;
108 close c_get_standard_price;
109 end if;
110
111 return c_event_standard_price;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 WF_CORE.CONTEXT(g_package,'get_event_standard_price',c_item_type,c_item_key);
116 RAISE;
117
118
119 end get_event_standard_price;
120
121
122 -- ------------------------------------------------------------------------
123 -- |------------------------< Get_activity_type >-------------------------|
124 -- ------------------------------------------------------------------------
125 --
126 -- Description
127 --
128 -- Get the activity type
129 --
130 function get_activity_type
131 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
132 return varchar2 is
133
134 c_activity_name ota_activity_definitions.name%type;
135 c_event_id ota_events.event_id%type;
136 c_item_type varchar2(50);
137 c_item_key varchar2(100);
138
139 cursor c_get_activity_name is
140 select oad.name
141 from ota_events evt,
142 ota_activity_versions oav,
143 ota_activity_definitions_tl oad
144 where evt.event_id = c_event_id and
145 oad.language=userenv('LANG') and
146 evt.activity_version_id = oav.activity_version_id and
147 oav.activity_id = oad.activity_id;
148
149
150 begin
151
152 c_item_type := get_item_type(p_transaction_id);
153 c_item_key := get_item_key(p_transaction_id);
154
155 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
156 itemkey => c_item_key,
157 aname => 'OTA_EVENT_ID',
158 ignore_notfound => TRUE);
159 if c_event_id is null then
160 c_activity_name := null;
161 else
162 open c_get_activity_name;
163 fetch c_get_activity_name into c_activity_name;
164 close c_get_activity_name;
165 end if;
166
167 return c_activity_name;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 WF_CORE.CONTEXT(g_package,'get_activity_type',c_item_type,c_item_key);
172 RAISE;
173
174 end get_activity_type;
175 --
176 -- ------------------------------------------------------------------------
177 -- |------------------------< Get_act_pm_category >-------------------------|
178 -- ------------------------------------------------------------------------
179 --
180 -- Description
181 --
182 -- Get the activity category
183 --
184 function get_act_pm_category
185 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
186 return varchar2 is
187
188 c_activity_category ota_act_cat_inclusions.activity_category%type;
189 c_event_id ota_events.event_id%type;
190 c_item_type varchar2(50);
191 c_item_key varchar2(100);
192
193 cursor c_get_activity_category is
194 select
195 hl.meaning
196 from
197 ota_events evt,
198 ota_act_cat_inclusions oac,
199 ota_category_usages ocu,
200 hr_lookups hl
201 where evt.event_id = c_event_id and
202 evt.activity_version_id = oac.activity_version_id and
203 oac.category_usage_id = ocu.category_usage_id and
204 ocu.type = 'C' and
205 oac.primary_flag = 'Y' and
206 hl.lookup_type = 'ACTIVITY_CATEGORY' and
207 oac.activity_category = hl.lookup_code;
208
209 begin
210
211 c_item_type := get_item_type(p_transaction_id);
212 c_item_key := get_item_key(p_transaction_id);
213
214 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
215 itemkey => c_item_key,
216 aname => 'OTA_EVENT_ID',
217 ignore_notfound => TRUE);
218
219 if c_event_id is null then
220 c_activity_category := null;
221 else
222 open c_get_activity_category;
223 fetch c_get_activity_category into c_activity_category;
224 close c_get_activity_category;
225 end if;
226
227 return c_activity_category;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 WF_CORE.CONTEXT(g_package,'get_act_pm_category',c_item_type,c_item_key);
232 RAISE;
233
234 end get_act_pm_category;
235 --
236 -- ------------------------------------------------------------------------
237 -- |------------------------< Get_act_pm_delivery_method >-------------------------|
238 -- ------------------------------------------------------------------------
239 --
240 -- Description
241 --
242 -- Get the activity primary delivery method
243 --
244 function get_act_pm_delivery_method
245 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
246 return varchar2 is
247
248 c_delivery_method ota_act_cat_inclusions.activity_category%type;
249 c_event_id ota_events.event_id%type;
250 c_item_type varchar2(50);
251 c_item_key varchar2(100);
252
253 cursor c_get_activity_dm is
254 select
255 hl.meaning
256 from
257 ota_events evt,
258 ota_act_cat_inclusions oac,
259 ota_category_usages ocu,
260 hr_lookups hl
261 where evt.event_id = c_event_id and
262 evt.activity_version_id = oac.activity_version_id and
263 oac.category_usage_id = ocu.category_usage_id and
264 ocu.type = 'DM' and
265 oac.primary_flag = 'Y' and
266 hl.lookup_type = 'ACTIVITY_CATEGORY' and
267 oac.activity_category = hl.lookup_code;
268
269
270 begin
271
272 c_item_type := get_item_type(p_transaction_id);
273 c_item_key := get_item_key(p_transaction_id);
274
275 c_event_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
276 itemkey => c_item_key,
277 aname => 'OTA_EVENT_ID',
278 ignore_notfound => TRUE);
279
280 if c_event_id is null then
281 c_delivery_method := null;
282 else
283 open c_get_activity_dm;
284 fetch c_get_activity_dm into c_delivery_method;
285 close c_get_activity_dm;
286 end if;
287
288 return c_delivery_method;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 WF_CORE.CONTEXT(g_package,'get_act_pm_delivery_method',c_item_type,c_item_key);
293 RAISE;
294
295 end get_act_pm_delivery_method;
296 --
297 -- ------------------------------------------------------------------------
298 -- |------------------------< Get_enrollment_status >-------------------------|
299 -- ------------------------------------------------------------------------
300 --
301 -- Description
302 --
303 -- Get the enrollment status
304 --
305 function get_enrollment_status
306 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
307 return varchar2 is
308
309 c_enrollment_status ota_booking_status_types.name%type;
310 c_booking_id ota_delegate_bookings.booking_id%type;
311 c_item_type varchar2(50);
312 c_item_key varchar2(100);
313
314 cursor c_get_enrollment_status is
315 select
316 bst.name
317 from
318 ota_delegate_bookings tdb,
319 ota_booking_status_types_tl bst
320 where tdb.booking_id = c_booking_id and
321 bst.language=userenv('LANG') and
322 tdb.booking_status_type_id = bst.booking_status_type_id;
323
324 begin
325
326 c_item_type := get_item_type(p_transaction_id);
327 c_item_key := get_item_key(p_transaction_id);
328
329 c_booking_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
330 itemkey => c_item_key,
331 aname => 'BOOKING_ID',
332 ignore_notfound => TRUE);
333
334 if c_booking_id is null then
335 c_enrollment_status := null;
336 else
337 open c_get_enrollment_status;
338 fetch c_get_enrollment_status into c_enrollment_status;
339 close c_get_enrollment_status;
340 end if;
341
342 return c_enrollment_status;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 WF_CORE.CONTEXT(g_package,'get_enrollment_status',c_item_type,c_item_key);
347 RAISE;
348
349 end get_enrollment_status;
350 --
351
352
353 END ot_workflow_ss;