[Home] [Help]
PACKAGE BODY: APPS.FUN_GLINT_WF
Source
1 PACKAGE BODY FUN_GLINT_WF AS
2 /* $Header: FUN_GLINT_WF_B.pls 120.7.12010000.2 2008/09/24 11:56:26 makansal ship $ */
3
4
5
6 /*-----------------------------------------------------
7 * FUNCTION autonomous_update_ini_complete
8 * ----------------------------------------------------
9 * Autonomously update the status to XFER_INI_GL in
10 * the recipient's system.
11 *
12 * Returns the new status.
13 * ---------------------------------------------------
14
15 FUNCTION autonomous_update_ini_complete (
16 p_trx_id IN number) RETURN varchar2
17 IS
18 PRAGMA AUTONOMOUS_TRANSACTION;
19 l_status varchar2(15);
20 BEGIN
21 SELECT status INTO l_status
22 FROM fun_trx_headers
23 WHERE trx_id = p_trx_id
24 FOR UPDATE;
25
26 l_status := fun_gl_transfer.update_status(p_trx_id, l_status);
27 COMMIT;
28 RETURN l_status;
29 END autonomous_update_ini_complete;
30 */
31
32
33 /*-----------------------------------------------------
34 * PROCEDURE get_attr_gl
35 * ----------------------------------------------------
36 * Get the attributes for the GL WF.
37 * ---------------------------------------------------*/
38
39 PROCEDURE get_attr_gl (
40 itemtype IN varchar2,
41 itemkey IN varchar2,
42 actid IN number,
43 funcmode IN varchar2,
44 resultout IN OUT NOCOPY varchar2)
45 IS
46 l_party_id number;
47 l_init_id number;
48 l_batch_id number;
49 l_trx_id number;
50 l_contact varchar2(30);
51 l_recipient_name varchar2(500);
52 l_trx_amount varchar2(100);
53 l_description varchar2(500);
54
55
56 BEGIN
57 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
58 fun_recipient_wf.get_attr(itemtype, itemkey, actid, funcmode, resultout);
59
60 l_party_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
61 itemkey => itemkey,
62 aname => 'PARTY_ID');
63 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
64 itemkey => itemkey,
65 aname => 'BATCH_ID');
66 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
67 itemkey => itemkey,
68 aname => 'TRX_ID');
69
70
71 -- added by rani shergill for notifications - start
72
73 SELECT rec.party_name
74 INTO l_recipient_name
75 FROM fun_trx_headers,
76 hz_parties rec
77 WHERE trx_id = l_trx_id
78 AND recipient_id = rec.party_id;
79
80 wf_engine.SetItemAttrText(itemtype => itemtype,
81 itemkey => itemkey,
82 aname => 'RECIPIENT_NAME',
83 avalue => l_recipient_name);
84
85
86 -- Bug: 7139371 Changing the query to make the l_trx_amt number reginal independent
87
88 --select ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
89 -- 0,h.reci_amount_dr,
90 -- h.reci_amount_cr),'999999999.99'))||' '||b.currency_code
91 -- into l_trx_amount
92 -- from fun_trx_headers h, fun_trx_batches b
93 -- where b.batch_id = l_batch_id
94 -- and h.trx_id = l_trx_id;
95
96 select ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
97 0,h.reci_amount_dr,
98 h.reci_amount_cr),'999999999D99'))||' '||b.currency_code
99 into l_trx_amount
100 from fun_trx_headers h, fun_trx_batches b
101 where b.batch_id = l_batch_id
102 and h.trx_id = l_trx_id;
103
104 -- Bug: 7139371 END
105
106 wf_engine.SetItemAttrText(itemtype => itemtype,
107 itemkey => itemkey,
108 aname => 'TRX_AMOUNT',
109 avalue => l_trx_amount);
110
111 -- added by rani shergill for notifications - end
112
113
114 SELECT initiator_id INTO l_init_id
115 FROM fun_trx_batches
116 WHERE batch_id = l_batch_id;
117
118 IF (l_init_id = l_party_id) THEN
119 wf_engine.SetItemAttrText(itemtype => itemtype,
120 itemkey => itemkey,
121 aname => 'PARTY_TYPE',
122 avalue => 'I');
123 UPDATE fun_trx_headers
124 SET init_wf_key = itemkey
125 WHERE trx_id = l_trx_id;
126 ELSE
127 wf_engine.SetItemAttrText(itemtype => itemtype,
128 itemkey => itemkey,
129 aname => 'PARTY_TYPE',
130 avalue => 'R');
131 UPDATE fun_trx_headers
132 SET reci_wf_key = itemkey
133 WHERE trx_id = l_trx_id;
134 END IF;
135
136 /* Start of changes for AME Uptake, 3671923. Bidisha S, 09 Jun 2004 */
137 -- The contact will now be obtained separately within workflow
138 -- as per AME rules
139 -- l_contact := fun_wf_common.get_contact_role(l_party_id);
140 -- wf_engine.SetItemAttrText(itemtype => itemtype,
141 -- itemkey => itemkey,
142 -- aname => 'CONTACT',
143 -- avalue => 'OPERATIONS');
144 -- -- TODO: avalue => l_contact);
145 /* End of changes for AME Uptake, 3671923. Bidisha S, 09 Jun 2004 */
146
147 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
148 RETURN;
149 END IF;
150
151 resultout := wf_engine.eng_null;
152 EXCEPTION
153 WHEN others THEN
154 wf_core.context('FUN_GLINT_WF', 'GET_ATTR_GL',
155 itemtype, itemkey, TO_CHAR(actid), funcmode);
156 RAISE;
157 END get_attr_gl;
158
159
160 /*-----------------------------------------------------
161 * PROCEDURE check_gl_setup
162 * ----------------------------------------------------
163 * Check whether there exists conversion between the
164 * transaction currency and the GL currency.
165 * Check whether the GL period is open.
166 * ---------------------------------------------------*/
167
168 PROCEDURE check_gl_setup (
169 itemtype IN varchar2,
170 itemkey IN varchar2,
171 actid IN number,
172 funcmode IN varchar2,
173 resultout IN OUT NOCOPY varchar2)
174 IS
175 l_success boolean := TRUE;
176 l_party_type varchar2(1);
177 l_batch_id number;
178 l_trx_id number;
179 l_ledger_id number;
180 l_gl_date date;
181 l_conv_date date;
182 l_trx_currency varchar2(15);
183 l_gl_currency varchar2(15);
184 l_conv_type varchar2(30);
185 l_has_rate number;
186 l_period_status varchar2(1);
187 l_rate number;
188 BEGIN
189 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
190 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
191 itemkey => itemkey,
192 aname => 'BATCH_ID');
193 l_party_type := wf_engine.GetItemAttrText(itemtype => itemtype,
194 itemkey => itemkey,
195 aname => 'PARTY_TYPE');
196
197 SELECT currency_code, from_ledger_id, gl_date, batch_date,
198 exchange_rate_type
199 INTO l_trx_currency, l_ledger_id, l_gl_date, l_conv_date,
200 l_conv_type
201 FROM fun_trx_batches
202 WHERE batch_id = l_batch_id;
203
204 IF (l_party_type = 'R') THEN
205 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
206 itemkey => itemkey,
207 aname => 'TRX_ID');
208 SELECT to_ledger_id INTO l_ledger_id
209 FROM fun_trx_headers
210 WHERE trx_id = l_trx_id;
211 END IF;
212
213 fnd_msg_pub.initialize;
214
215 -- Check GL period.
216 l_period_status := fun_gl_transfer.get_period_status(101, l_gl_date, l_ledger_id);
217 IF (l_period_status NOT IN ('O', 'F')) THEN
218 fnd_message.set_name('FUN', 'GL_PERIOD_NOT_OPEN');
219 fnd_msg_pub.add;
220 l_success := FALSE;
221 END IF;
222
223 -- Check GL currency conversion.
224 SELECT currency_code INTO l_gl_currency
225 FROM gl_ledgers
226 WHERE ledger_id = l_ledger_id;
227
228 l_rate := fun_gl_transfer.has_conversion_rate(l_trx_currency, l_gl_currency,
229 l_conv_type, l_conv_date);
230 IF l_rate = -1 THEN
231 fnd_message.set_name('FUN', 'FUN_API_CONV_RATE_NOT_FOUND');
232 fnd_msg_pub.add;
233 l_success := FALSE;
234 ELSIF l_rate = -2 THEN
235 fnd_message.set_name('FUN', 'FUN_API_INVALID_CURRENCY');
236 fnd_msg_pub.add;
237 l_success := FALSE;
238 END IF;
239
240
241 IF (l_success) THEN
242 resultout := wf_engine.eng_completed||':T';
243 ELSE
244 wf_engine.SetItemAttrText(itemtype, itemkey, 'ERROR',
245 fun_wf_common.concat_msg_stack(fnd_msg_pub.count_msg));
246 resultout := wf_engine.eng_completed||':F';
247 END IF;
248 RETURN;
249 END IF;
250
251 resultout := wf_engine.eng_null;
252 EXCEPTION
253 WHEN others THEN
254 wf_core.context('FUN_GLINT_WF', 'CHECK_GL_SETUP',
255 itemtype, itemkey, TO_CHAR(actid), funcmode);
256 RAISE;
257 END check_gl_setup;
258
259
260
261 /*-----------------------------------------------------
262 * PROCEDURE transfer_to_gl
263 * ----------------------------------------------------
264 * Transfer to GL. Wrapper for
265 * FUN_GL_TRANSFER.AUTONOMOUS_TRANSFER.
266 *
267 * If AUTONOMOUS_TRANSFER returns false, it means the
268 * status is incorrect, i.e. the trx is already
269 * transferred. So we abort our WF process.
270 * ---------------------------------------------------*/
271
272 PROCEDURE transfer_to_gl (
273 itemtype IN varchar2,
274 itemkey IN varchar2,
275 actid IN number,
276 funcmode IN varchar2,
277 resultout IN OUT NOCOPY varchar2)
278 IS
279 l_success boolean := FALSE;
280 l_party_type varchar2(1);
281 l_trx_id number;
282 l_batch_id number;
283 l_ledger_id number;
284 l_gl_date date;
285 l_trx_currency varchar2(15);
286 l_desc varchar2(240);
287 l_conv_date date;
288 l_conv_type varchar2(30);
289 l_status varchar2(15);
290 BEGIN
291 IF (funcmode = 'RUN') THEN
292 l_party_type := wf_engine.GetItemAttrText(itemtype => itemtype,
293 itemkey => itemkey,
294 aname => 'PARTY_TYPE');
295 l_trx_id := wf_engine.GetItemAttrNumber
296 (itemtype => itemtype,
297 itemkey => itemkey,
298 aname => 'TRX_ID');
299 l_batch_id := wf_engine.GetItemAttrNumber
300 (itemtype => itemtype,
301 itemkey => itemkey,
302 aname => 'BATCH_ID');
303
304 SELECT b.from_ledger_id, b.gl_date, b.currency_code,
305 b.exchange_rate_type, b.batch_date, b.description
306 INTO l_ledger_id, l_gl_date, l_trx_currency,
307 l_conv_type, l_conv_date, l_desc
308 FROM fun_trx_batches b
309 WHERE b.batch_id = l_batch_id;
310
311 IF (l_party_type = 'R') THEN
312 SELECT to_ledger_id, description INTO l_ledger_id, l_desc
313 FROM fun_trx_headers
314 WHERE trx_id = l_trx_id;
315 END IF;
316
317 l_success := fun_gl_transfer.lock_and_transfer(
318 l_trx_id, l_ledger_id, l_gl_date,
319 l_trx_currency, 'Global Intercompany',
320 'Global Intercompany', l_desc, l_conv_date,
321 l_conv_type, l_party_type);
322
323 IF (NOT l_success) THEN
324 SELECT status INTO l_status
325 FROM fun_trx_headers
326 WHERE trx_id = l_trx_id;
327
328 IF ((l_status = 'XFER_INI_GL' AND l_party_type = 'I') OR
329 (l_status = 'XFER_RECI_GL' AND l_party_type = 'R') OR
330 (l_status = 'COMPLETE')) THEN
331 wf_engine.AbortProcess
332 (itemtype => itemtype,
333 itemkey => itemkey,
334 process => 'GL_TRANSFER');
335 ELSE
336 RAISE gl_transfer_failure;
337 END IF;
338 END IF;
339
340 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
341 END IF;
342
343 resultout := wf_engine.eng_null;
344 EXCEPTION
345 WHEN others THEN
346 wf_core.context('FUN_GLINT_WF', 'TRANSFER_TO_GL',
347 itemtype, itemkey, TO_CHAR(actid), funcmode);
348 RAISE;
349 END transfer_to_gl;
350
351
352 /*-----------------------------------------------------
353 * PROCEDURE check_signal_initiator
354 * ----------------------------------------------------
355 * Check whether we raise an event to the initiator.
356 * ---------------------------------------------------*/
357
358 PROCEDURE check_signal_initiator (
359 itemtype IN varchar2,
360 itemkey IN varchar2,
361 actid IN number,
362 funcmode IN varchar2,
363 resultout IN OUT NOCOPY varchar2)
364 IS
365 l_trx_id number;
366 l_status varchar2(15);
367 BEGIN
368 IF (funcmode = 'RUN' OR funcmode = 'CANcEL') THEN
369 l_trx_id := wf_engine.GetItemAttrNumber
370 (itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'TRX_ID');
373 SELECT status INTO l_status
374 FROM fun_trx_headers
375 WHERE trx_id = l_trx_id;
376
377 IF (l_status = 'XFER_RECI_GL') THEN
378 resultout := wf_engine.eng_completed||':F';
379 ELSIF (l_status = 'COMPLETE') THEN
380 resultout := wf_engine.eng_completed||':T';
381 ELSE
382 wf_core.Raise('Internal error: check signal (initiator) found status'||
383 l_status);
384 END IF;
385 RETURN;
386 END IF;
387
388 resultout := wf_engine.eng_null;
389 EXCEPTION
390 WHEN others THEN
391 wf_core.context('FUN_GLINT_WF', 'CHECK_SIGNAL_INITIATOR',
392 itemtype, itemkey, TO_CHAR(actid), funcmode);
393 RAISE;
394 END check_signal_initiator;
395
396
397 /*-----------------------------------------------------
398 * PROCEDURE raise_gl_complete
399 * ----------------------------------------------------
400 * Raise the (initiator) GL complete event.
401 * ---------------------------------------------------*/
402
403 PROCEDURE raise_gl_complete (
404 itemtype IN varchar2,
405 itemkey IN varchar2,
406 actid IN number,
407 funcmode IN varchar2,
408 resultout IN OUT NOCOPY varchar2)
409 IS
410 l_status varchar2(15);
411 l_batch_id number;
412 l_trx_id number;
413 l_event_key varchar2(240);
414 l_params wf_parameter_list_t := wf_parameter_list_t();
415 BEGIN
416 IF (funcmode = 'RUN') THEN
417 l_batch_id := wf_engine.GetItemAttrNumber
418 (itemtype => itemtype,
419 itemkey => itemkey,
420 aname => 'BATCH_ID');
421 l_trx_id := wf_engine.GetItemAttrNumber
422 (itemtype => itemtype,
423 itemkey => itemkey,
424 aname => 'TRX_ID');
425 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
426
427 SELECT status INTO l_status
428 FROM fun_trx_headers
429 WHERE trx_id = l_trx_id;
430
431 IF (l_status <> 'XFER_INI_GL') THEN
432 RETURN;
433 END IF;
434
435 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
436
437 wf_event.AddParameterToList(p_name => 'TRX_ID',
438 p_value => TO_CHAR(l_trx_id),
439 p_parameterlist => l_params);
440 wf_event.AddParameterToList(p_name => 'BATCH_ID',
441 p_value => TO_CHAR(l_batch_id),
442 p_parameterlist => l_params);
443
444 wf_event.raise(
445 p_event_name => 'oracle.apps.fun.manualtrx.glcomplete.send',
446 p_event_key => l_event_key,
447 p_parameters => l_params);
448
449 l_params.delete();
450
451 RETURN;
452 END IF;
453
454 resultout := wf_engine.eng_null;
455 RETURN;
456
457 EXCEPTION
458 WHEN others THEN
459 wf_core.context('FUN_GLINT_WF', 'RAISE_GL_COMPLETE',
460 itemtype, itemkey, TO_CHAR(actid), funcmode);
461 RAISE;
462 END raise_gl_complete;
463
464
465 /*-----------------------------------------------------
466 * PROCEDURE update_ini_complete
467 * ----------------------------------------------------
468 * Update the status to XFER_INI_GL.
469 * ---------------------------------------------------*/
470
471 PROCEDURE update_ini_complete (
472 itemtype IN varchar2,
473 itemkey IN varchar2,
474 actid IN number,
475 funcmode IN varchar2,
476 resultout IN OUT NOCOPY varchar2)
477 IS
478 l_party_type varchar2(1);
479 l_trx_id number;
480 l_status varchar2(15);
481 BEGIN
482 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
483 l_trx_id := wf_engine.GetItemAttrNumber
484 (itemtype => itemtype,
485 itemkey => itemkey,
486 aname => 'TRX_ID');
487 l_party_type := wf_engine.GetItemAttrText(itemtype => itemtype,
488 itemkey => itemkey,
489 aname => 'PARTY_TYPE');
490
491 SELECT status INTO l_status
492 FROM fun_trx_headers
493 WHERE trx_id = l_trx_id
494 FOR UPDATE;
495
496 l_status := fun_gl_transfer.update_status(l_trx_id, l_status, l_party_type);
497
498 wf_engine.SetItemAttrText(itemtype => itemtype,
499 itemkey => itemkey,
500 aname => 'STATUS',
501 avalue => l_status);
502
503 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
504 RETURN;
505 END IF;
506
507 resultout := wf_engine.eng_null;
508 RETURN;
509
510 EXCEPTION
511 WHEN others THEN
512 wf_core.context('FUN_GLINT_WF', 'UPDATE_INI_COMPLETE',
513 itemtype, itemkey, TO_CHAR(actid), funcmode);
514 RAISE;
515 END update_ini_complete;
516
517
518
519 END;