1 PACKAGE BODY pay_batch_object_status_pkg AS
2 /* $Header: pybos.pkb 120.2 2006/09/27 17:44:31 thabara noship $ */
3
4 --
5 -- ----------------------------------------------------------------------------
6 -- get_status
7 --
8 -- Returns the status of the specified object.
9 -- ----------------------------------------------------------------------------
10 function get_status
11 (p_object_type in varchar2
12 ,p_object_id in number
13 ) return varchar2
14 is
15 cursor csr_obj
16 is
17 select
18 bos.object_status
19 from
20 pay_batch_object_status bos
21 where
22 bos.object_type = p_object_type
23 and bos.object_id = p_object_id
24 ;
25 l_object_status pay_batch_object_status.object_status%type;
26 begin
27 open csr_obj;
28 fetch csr_obj into l_object_status;
29 close csr_obj;
30 --
31 return l_object_status;
32
33 end get_status;
34 --
35 -- ----------------------------------------------------------------------------
36 -- get_status_meaning
37 --
38 -- Returns the status meaning of the specified object.
39 -- ----------------------------------------------------------------------------
40 function get_status_meaning
41 (p_object_type in varchar2
42 ,p_object_id in number
43 ,p_default_status in varchar2 default null
44 ) return varchar2
45 is
46 l_object_status pay_batch_object_status.object_status%type;
47 begin
48 --
49 l_object_status := nvl(get_status(p_object_type, p_object_id)
50 ,p_default_status);
51 --
52 return hr_general.decode_lookup('ACTION_STATUS', l_object_status);
53
54 end get_status_meaning;
55 --
56 -- ----------------------------------------------------------------------------
57 -- lock_batch_object_internal
58 --
59 -- Locks the object record. If the caller is not the owner payroll action
60 -- and the object is being processed, this will raise an error.
61 --
62 -- ----------------------------------------------------------------------------
63 procedure lock_batch_object_internal
64 (p_object_type in varchar2
65 ,p_object_id in number
66 ,p_payroll_action_id in number default null
67 ,p_object_status out nocopy varchar2
68 )
69 is
70 cursor csr_obj
71 is
72 select
73 bos.object_status
74 ,bos.payroll_action_id
75 from
76 pay_batch_object_status bos
77 where
78 bos.object_type = p_object_type
79 and bos.object_id = p_object_id
80 for update nowait
81 ;
82 --
83 cursor csr_ppa(p_pact_id number)
84 is
85 select 1
86 from pay_payroll_actions
87 where payroll_action_id = p_pact_id
88 ;
89 --
90 l_rec csr_obj%rowtype;
91 l_dummy number;
92 l_object_name hr_lookups.meaning%type;
93 --
94 begin
95 --
96 open csr_obj;
97 fetch csr_obj into l_rec;
98 if csr_obj%found then
99 close csr_obj;
100 --
101 -- Check if the object is being processed by another process.
102 --
103 if l_rec.payroll_action_id <> nvl(p_payroll_action_id, -99999) then
104
105 --
106 -- Check if it is being processed.
107 --
108 if l_rec.object_status = 'P' then
109 --
110 -- Ensure if the processing payroll action exists.
111 --
112 open csr_ppa(l_rec.payroll_action_id);
113 fetch csr_ppa into l_dummy;
114 if csr_ppa%found then
115 close csr_ppa;
116 --
117 l_object_name
118 := hr_general.decode_lookup('PAY_BATCH_OBJECT_TYPE', p_object_type);
119 --
120 -- You cannot lock the object that is being processed
121 -- by another batch process.
122 --
123 hr_utility.set_message(801, 'PAY_33446_BAT_OBJ_LOCKED');
124 hr_utility.set_message_token('OBJECT_NAME', l_object_name);
125 hr_utility.raise_error;
126 end if;
127 close csr_ppa;
128 end if;
129 end if;
130 else
131 close csr_obj;
132 end if;
133
134 --
135 -- Set out variables
136 --
137 p_object_status := l_rec.object_status;
138 --
139 end lock_batch_object_internal;
140 --
141 -- ----------------------------------------------------------------------------
142 -- lock_batch_object
143 --
144 -- Locks the object status record.
145 -- If p_object_status is specified, see if the status is up to date.
146 -- If the batch object does not exist, the default status will be used instead.
147 --
148 -- NOTE: This should be called from a generic interface ie Forms instead of
149 -- a payroll process.
150 -- ----------------------------------------------------------------------------
151 procedure lock_batch_object
152 (p_object_type in varchar2
153 ,p_object_id in number
154 ,p_object_status in varchar2 default null
155 ,p_default_status in varchar2 default null
156 )
157 is
158 l_object_status pay_batch_object_status.object_status%type;
159 begin
160 --
161 -- Lock the batch object.
162 --
163 lock_batch_object_internal
164 (p_object_type => p_object_type
165 ,p_object_id => p_object_id
166 ,p_payroll_action_id => null
167 ,p_object_status => l_object_status
168 );
169 --
170 -- Compare the status if p_object_status is specified.
171 --
172 if p_object_status is not null then
173 --
174 if (p_object_status = nvl(l_object_status, p_default_status)) then
175 null;
176 else
177 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
178 app_exception.raise_exception;
179 end if;
180 end if;
181
182 end lock_batch_object;
183 --
184 -- ----------------------------------------------------------------------------
185 -- chk_complete_status
186 --
187 -- Locks the object record and see if the status is complete.
188 -- ----------------------------------------------------------------------------
189 procedure chk_complete_status
190 (p_object_type in varchar2
191 ,p_object_id in number
192 )
193 is
194 l_object_status pay_batch_object_status.object_status%type;
195 l_object_name hr_lookups.meaning%type;
196 begin
197 --
198 -- Lock the batch object.
199 --
200 lock_batch_object_internal
201 (p_object_type => p_object_type
202 ,p_object_id => p_object_id
203 ,p_payroll_action_id => null
204 ,p_object_status => l_object_status
205 );
206 --
207 -- Raise an error if the status is not complete.
208 --
209 if l_object_status <> 'C' then
210 --
211 l_object_name
212 := hr_general.decode_lookup('PAY_BATCH_OBJECT_TYPE', p_object_type);
213 --
214 hr_utility.set_message(801, 'PAY_33447_BAT_OBJ_INCOMP');
215 hr_utility.set_message_token('OBJECT_NAME', l_object_name);
216 hr_utility.raise_error;
217 end if;
218
219 end chk_complete_status;
220 --
221 -- ----------------------------------------------------------------------------
222 -- set_status
223 --
224 -- Sets the object status.
225 -- ----------------------------------------------------------------------------
226 procedure set_status
227 (p_object_type in varchar2
228 ,p_object_id in number
229 ,p_object_status in varchar2
230 ,p_payroll_action_id in number default null
231 )
232 is
233 l_object_status pay_batch_object_status.object_status%type;
234 begin
235 --
236 -- Note: In order to lock a batch object with a payroll action,
237 -- the same payroll_action_id has to be specified.
238 --
239
240 --
241 -- Lock the batch object.
242 --
243 lock_batch_object_internal
244 (p_object_type => p_object_type
245 ,p_object_id => p_object_id
246 ,p_payroll_action_id => p_payroll_action_id
247 ,p_object_status => l_object_status
248 );
249
250 if l_object_status is not null then
251
252 --
253 -- Update the batch object status.
254 --
255 update pay_batch_object_status
256 set object_status = p_object_status
257 ,payroll_action_id = p_payroll_action_id
258 where
259 object_type = p_object_type
260 and object_id = p_object_id;
261
262 else
263 --
264 -- Insert a new row.
265 --
266 insert into pay_batch_object_status
267 (object_type
268 ,object_id
269 ,object_status
270 ,payroll_action_id
271 )
272 values
273 (p_object_type
274 ,p_object_id
275 ,p_object_status
276 ,p_payroll_action_id
277 );
278 end if;
279 end set_status;
280 --
281 -- ----------------------------------------------------------------------------
282 -- delete_object_status
283 --
284 -- Deletes the object status record.
285 -- ----------------------------------------------------------------------------
286 procedure delete_object_status
287 (p_object_type in varchar2
288 ,p_object_id in number
289 ,p_payroll_action_id in number default null
290 )
291 is
292 l_object_status pay_batch_object_status.object_status%type;
293 begin
294 --
295 -- Lock the batch object.
296 --
297 lock_batch_object_internal
298 (p_object_type => p_object_type
299 ,p_object_id => p_object_id
300 ,p_payroll_action_id => p_payroll_action_id
301 ,p_object_status => l_object_status
302 );
303
304 delete from pay_batch_object_status
305 where
306 object_type = p_object_type
307 and object_id = p_object_id;
308
309 end delete_object_status;
310 --
311 -------------------------------------------------------------------------------
312
313 end pay_batch_object_status_pkg;