[Home] [Help]
PACKAGE BODY: APPS.HXC_WF_ERROR_HELPER
Source
1 Package Body HXC_WF_ERROR_HELPER as
2 /* $Header: hxcwferrhelper.pkb 120.3.12010000.2 2008/08/05 12:09:27 ubhat ship $ */
3
4 g_pkg constant varchar2(30) := 'hxc_wf_error_helper.';
5 g_debug BOOLEAN :=hr_utility.debug_enabled;
6
7 procedure prepare_error(
8 itemtype IN varchar2,
9 itemkey IN varchar2,
10 actid IN number,
11 funcmode IN varchar2,
12 result IN OUT NOCOPY varchar2)
13 is
14
15 l_app_bb_id number;
16 l_app_bb_ovn number;
17 l_timecard_id number;
18 l_timecard_ovn number;
19 l_effective_end_date date;
20 l_effective_start_date date;
21 l_worker_role wf_local_roles.name%type;
22 l_error_admin_role wf_local_roles.name%type;
23 l_total_hours number;
24 l_premium_hours number;
25 l_non_worked_hours number;
26 l_description varchar2(1000);
27 l_title varchar2(1000);
28 l_fyi_subject varchar2(5000);
29 l_item_type_desc varchar2(1000);
30 l_worker_full_name varchar2(1000);
31 l_error_body varchar2(32000);
32 l_tc_start_date date;
33 l_tc_stop_date date;
34 l_resource_id number;
35 l_itemkey wf_items.item_key%type;
36 l_error varchar2(3000);
37 l_recipient_login fnd_user.user_name%type;
38 l_recipient_id number;
39 l_proc constant varchar2(61) := g_pkg ||'prepare_error';
40
41 CURSOR c_tc_info(
42 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
43 )
44 IS
45 SELECT tcsum.resource_id,
46 tcsum.start_time,
47 tcsum.stop_time
48 FROM hxc_timecard_summary tcsum
49 WHERE tcsum.timecard_id = p_tc_bbid;
50
51 CURSOR c_tc_info_tbb(
52 p_tc_bbid hxc_time_building_blocks.time_building_block_id%TYPE
53 )
54 IS
55 select htb.resource_id, htb.start_time,htb.stop_time
56 from hxc_time_building_blocks htb
57 where htb.time_building_block_id =p_tc_bbid
58 and htb.scope = 'TIMECARD'
59 and htb.object_version_number = (select max(object_version_number)
60 from hxc_time_building_blocks htb1
61 where htb.time_building_block_id =htb1.time_building_block_id);
62
63
64 CURSOR c_get_error(p_itemkey wf_items.item_key%type)
65 is
66 select error_message
67 from WF_ITEM_ACTIVITY_STATUSES
68 where item_type = 'HXCEMP'
69 and item_key = p_itemkey
70 and activity_status = 'ERROR';
71
72
73 CURSOR c_get_parent_itemkey(itemkey in wf_items.item_key%type)
74 is
75 select parent_item_key
76 from wf_items
77 where item_key = itemkey;
78
79 BEGIN
80
81 g_debug:=hr_utility.debug_enabled;
82 if g_debug then
83 hr_utility.set_location(l_proc, 10);
84 end if;
85
86 --The itemkey passed to this procedure is not the itemkey assoicated with application period id. This is entirely --a different item key(error item key). The parent to this error item key is the item key in App bb id.
87
88 open c_get_parent_itemkey(itemkey);
89 fetch c_get_parent_itemkey into l_itemkey;
90 close c_get_parent_itemkey;
91
92 l_app_bb_id:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
93 itemkey => l_itemkey,
94 aname => 'APP_BB_ID');
95
96 l_app_bb_ovn:= wf_engine.GetItemAttrNumber(itemtype => itemtype,
97 itemkey => l_itemkey,
98 aname => 'APP_BB_OVN');
99
100 l_timecard_id := wf_engine.GetItemAttrNumber
101 (itemtype => itemtype,
102 itemkey => l_itemkey,
103 aname => 'TC_BLD_BLK_ID');
104 l_timecard_ovn := wf_engine.GetItemAttrNumber
105 (itemtype => itemtype,
106 itemkey => l_itemkey,
107 aname => 'TC_BLD_BLK_OVN');
108
109
110 l_effective_end_date := wf_engine.GetItemAttrDate(
111 itemtype => itemtype,
112 itemkey => l_itemkey,
113 aname => 'APP_END_DATE');
114
115 l_effective_start_date := wf_engine.GetItemAttrDate(
116 itemtype => itemtype,
117 itemkey => l_itemkey,
118 aname => 'APP_START_DATE');
119 --Instead of fetching these from item attributes, fetch it from sumary table since in the case of
120 --submission-worker these attributes will not be set.
121
122 open c_tc_info(l_timecard_id);
123 fetch c_tc_info into l_resource_id, l_tc_start_date, l_tc_stop_date;
124
125 if c_tc_info%notfound then
126
127 open c_tc_info_tbb(l_timecard_id);
128 fetch c_tc_info_tbb into l_resource_id, l_tc_start_date, l_tc_stop_date;
129 close c_tc_info_tbb;
130
131 end if;
132
133 close c_tc_info;
134
135 l_worker_role :=HXC_APPROVAL_WF_HELPER.find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_worker,l_timecard_id,l_timecard_ovn);
136
137 if l_worker_role is null then
138 l_worker_full_name :=hxc_find_notify_aprs_pkg.get_name(l_resource_id,l_tc_stop_date);
139 else
140 l_worker_full_name := hxc_approval_wf_helper.find_full_name_from_role(l_worker_role,l_tc_start_date);
141 end if;
142
143 l_error_admin_role := hxc_approval_wf_helper.find_role_for_recipient(hxc_app_comp_notifications_api.c_recipient_error_admin,l_timecard_id,l_timecard_ovn);
144
145 if g_debug then
146 hr_utility.set_location(l_proc, 20);
147 end if;
148 wf_engine.SetItemAttrText(itemtype => itemtype,
149 itemkey => itemkey,
150 aname => 'TC_FROM_ROLE',
151 avalue => l_worker_role);
152 --set TITLE
153 fnd_message.set_name('HXC','HXC_APPR_WF_TITLE');
154
155 --l_effective_start_date and l_effective_end_date will be null when error happens in create_app_period_info, --since in this procedure the itemkey will be the itelkey ascoaited with the timecard,hence we need to pass --timecard start and stop times.
156
157 if(l_effective_start_date is null and l_effective_end_date is null) then
158 fnd_message.set_token('START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
159 fnd_message.set_token('END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
160 else
161 fnd_message.set_token('START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
162 fnd_message.set_token('END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
163 end if;
164
165 l_title := fnd_message.get();
166
167 wf_engine.SetItemAttrText(
168 itemtype => itemtype,
169 itemkey => itemkey,
170 aname => 'TITLE',
171 avalue => l_title);
172 --set DESCRIPTION
173
174 --l_app_bb_id will be null when error happens in create_app_period_info, since in this procedure the itemkey will --be the itelkey ascoaited with the timecard,hence when l_app_bb_id is null total hours should be calculated --based on timecard id
175 if l_app_bb_id is null then
176 wf_engine.SetItemAttrText
177 (itemtype => itemtype,
178 itemkey => itemkey,
179 aname => 'DESCRIPTION',
180 avalue => hxc_find_notify_aprs_pkg.get_description_tc(l_timecard_id,l_timecard_ovn)
181 );
182 else
183 wf_engine.SetItemAttrText
184 (itemtype => itemtype,
185 itemkey => itemkey,
186 aname => 'DESCRIPTION',
187 avalue => hxc_find_notify_aprs_pkg.get_description(l_app_bb_id)
188 );
189 end if;
190
191 --set ERROR_BODY
192 select display_name
193 into l_item_type_desc
194 from wf_item_types_vl
195 where name = 'HXCEMP';
196
197 open c_get_error(l_itemkey);
198 fetch c_get_error into l_error;
199 close c_get_error;
200
201 --set FYI_SUBJECT
202 fnd_message.set_name('HXC','HXC_APPR_ERROR_SUBJECT');
203
204 --l_effective_start_date and l_effective_end_date will be null when error happens in create_app_period_info, --since in this procedure the itemkey will be the itelkey ascoaited with the timecard,hence we need to pass --timecard start and stop times.
205 if(l_effective_start_date is null and l_effective_end_date is null) then
206 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_tc_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
207 fnd_message.set_token('APPLICATION_PERIOD_END_DATE',to_char(l_tc_stop_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
208 else
209 fnd_message.set_token('APPLICATION_PERIOD_START_DATE',to_char(l_effective_start_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
210 fnd_message.set_token('APPLICATION_PERIOD_END_DATE',to_char(l_effective_end_date,fnd_profile.value('ICX_DATE_FORMAT_MASK')));
211 end if;
212
213 fnd_message.set_token('RESOURCE_FULL_NAME',l_worker_full_name);
214 fnd_message.set_token('ERROR',l_error);
215 l_fyi_subject :=fnd_message.get();
216
217 wf_engine.SetItemAttrText(
218 itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'FYI_SUBJECT',
221 avalue => l_fyi_subject);
222
223 --set FYI_RECIPIENT_LOGIN
224 wf_engine.SetItemAttrText(
225 itemtype => itemtype,
226 itemkey => itemkey,
227 aname => 'ERROR_ROLE',
228 avalue =>l_error_admin_role);
229
230 fnd_message.set_name('HXC','HXC_APPR_ERROR_BODY');
231 fnd_message.set_token('ITEM_TYPE_DESC',l_item_type_desc);
232 fnd_message.set_token('ITEM_TYPE',itemtype);
233 fnd_message.set_token('ITEM_KEY',l_itemkey);
234 fnd_message.set_token('ERROR_INFORMATION',l_error);
235 l_error_body := fnd_message.get();
236
237 wf_engine.SetItemAttrText(
238 itemtype => itemtype,
239 itemkey => itemkey,
240 aname => 'ERROR_BODY',
241 avalue =>l_error_body);
242
243 if g_debug then
244 hr_utility.set_location(l_proc, 30);
245 end if;
246
247
248 result := 'COMPLETE';
249 exception
250 when others then
251
252 -- The line below records this function call in the error system
253 -- in the case of an exception.
254 --
255 if g_debug then
256 hr_utility.set_location(l_proc, 999);
257 end if;
258
259 wf_core.context('HXCERRORHELPER', 'HXC_WF_ERROR_HELPER.prepare_error',
260 itemtype, itemkey, to_char(actid), funcmode);
261 raise;
262 result := '';
263 return;
264 End prepare_error;
265
266 END HXC_WF_ERROR_HELPER;