[Home] [Help]
PACKAGE BODY: APPS.FUN_GLINT_WF
Source
1 PACKAGE BODY FUN_GLINT_WF AS
2 /* $Header: FUN_GLINT_WF_B.pls 120.10 2010/10/05 07:32:45 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, gl_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 l_user_env_lang VARCHAR2(5);
291 BEGIN
292 IF (funcmode = 'RUN') THEN
293 l_party_type := wf_engine.GetItemAttrText(itemtype => itemtype,
294 itemkey => itemkey,
295 aname => 'PARTY_TYPE');
296 l_trx_id := wf_engine.GetItemAttrNumber
297 (itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'TRX_ID');
300 l_batch_id := wf_engine.GetItemAttrNumber
301 (itemtype => itemtype,
302 itemkey => itemkey,
303 aname => 'BATCH_ID');
304
305 l_user_env_lang := wf_engine.GetItemAttrText
306 (itemtype => itemtype,
307 itemkey => itemkey,
308 aname => 'USER_LANG');
309
310 SELECT b.from_ledger_id, b.gl_date, b.currency_code,
311 b.exchange_rate_type, b.gl_date, b.description
312 INTO l_ledger_id, l_gl_date, l_trx_currency,
313 l_conv_type, l_conv_date, l_desc
314 FROM fun_trx_batches b
315 WHERE b.batch_id = l_batch_id;
316
317 IF (l_party_type = 'R') THEN
318 SELECT to_ledger_id, description INTO l_ledger_id, l_desc
319 FROM fun_trx_headers
320 WHERE trx_id = l_trx_id;
321 END IF;
322
323 l_success := fun_gl_transfer.lock_and_transfer(
324 l_trx_id, l_ledger_id, l_gl_date,
325 l_trx_currency, 'Global Intercompany',
326 'Global Intercompany', l_desc, l_conv_date,
327 l_conv_type, l_party_type, l_user_env_lang);
328
329 IF (NOT l_success) THEN
330 SELECT status INTO l_status
331 FROM fun_trx_headers
332 WHERE trx_id = l_trx_id;
333
334 IF ((l_status = 'XFER_INI_GL' AND l_party_type = 'I') OR
335 (l_status = 'XFER_RECI_GL' AND l_party_type = 'R') OR
336 (l_status = 'COMPLETE')) THEN
337 wf_engine.AbortProcess
338 (itemtype => itemtype,
339 itemkey => itemkey,
340 process => 'GL_TRANSFER');
341 ELSE
342 RAISE gl_transfer_failure;
343 END IF;
344 END IF;
345
346 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
347 END IF;
351 WHEN others THEN
348
349 resultout := wf_engine.eng_null;
350 EXCEPTION
352 wf_core.context('FUN_GLINT_WF', 'TRANSFER_TO_GL',
353 itemtype, itemkey, TO_CHAR(actid), funcmode);
354 RAISE;
355 END transfer_to_gl;
356
357
358 /*-----------------------------------------------------
359 * PROCEDURE check_signal_initiator
360 * ----------------------------------------------------
361 * Check whether we raise an event to the initiator.
362 * ---------------------------------------------------*/
363
364 PROCEDURE check_signal_initiator (
365 itemtype IN varchar2,
366 itemkey IN varchar2,
367 actid IN number,
368 funcmode IN varchar2,
369 resultout IN OUT NOCOPY varchar2)
370 IS
371 l_trx_id number;
372 l_status varchar2(15);
373 BEGIN
374 IF (funcmode = 'RUN' OR funcmode = 'CANcEL') THEN
375 l_trx_id := wf_engine.GetItemAttrNumber
376 (itemtype => itemtype,
377 itemkey => itemkey,
378 aname => 'TRX_ID');
379 SELECT status INTO l_status
380 FROM fun_trx_headers
381 WHERE trx_id = l_trx_id;
382
383 IF (l_status = 'XFER_RECI_GL') THEN
384 resultout := wf_engine.eng_completed||':F';
385 ELSIF (l_status = 'COMPLETE') THEN
386 resultout := wf_engine.eng_completed||':T';
387 ELSE
388 wf_core.Raise('Internal error: check signal (initiator) found status'||
389 l_status);
390 END IF;
391 RETURN;
392 END IF;
393
394 resultout := wf_engine.eng_null;
395 EXCEPTION
396 WHEN others THEN
397 wf_core.context('FUN_GLINT_WF', 'CHECK_SIGNAL_INITIATOR',
398 itemtype, itemkey, TO_CHAR(actid), funcmode);
399 RAISE;
400 END check_signal_initiator;
401
402
403 /*-----------------------------------------------------
404 * PROCEDURE raise_gl_complete
405 * ----------------------------------------------------
406 * Raise the (initiator) GL complete event.
407 * ---------------------------------------------------*/
408
409 PROCEDURE raise_gl_complete (
410 itemtype IN varchar2,
411 itemkey IN varchar2,
412 actid IN number,
413 funcmode IN varchar2,
414 resultout IN OUT NOCOPY varchar2)
415 IS
416 l_status varchar2(15);
417 l_batch_id number;
418 l_trx_id number;
419 l_event_key varchar2(240);
420 l_params wf_parameter_list_t := wf_parameter_list_t();
421 BEGIN
422 IF (funcmode = 'RUN') THEN
423 l_batch_id := wf_engine.GetItemAttrNumber
424 (itemtype => itemtype,
425 itemkey => itemkey,
426 aname => 'BATCH_ID');
427 l_trx_id := wf_engine.GetItemAttrNumber
428 (itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'TRX_ID');
431 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
432
433 SELECT status INTO l_status
434 FROM fun_trx_headers
435 WHERE trx_id = l_trx_id;
436
437 IF (l_status <> 'XFER_INI_GL') THEN
438 RETURN;
439 END IF;
440
441 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
442
443 wf_event.AddParameterToList(p_name => 'TRX_ID',
444 p_value => TO_CHAR(l_trx_id),
445 p_parameterlist => l_params);
446 wf_event.AddParameterToList(p_name => 'BATCH_ID',
447 p_value => TO_CHAR(l_batch_id),
448 p_parameterlist => l_params);
449
450 wf_event.raise(
451 p_event_name => 'oracle.apps.fun.manualtrx.glcomplete.send',
452 p_event_key => l_event_key,
453 p_parameters => l_params);
454
455 l_params.delete();
456
457 RETURN;
458 END IF;
459
460 resultout := wf_engine.eng_null;
461 RETURN;
462
463 EXCEPTION
464 WHEN others THEN
465 wf_core.context('FUN_GLINT_WF', 'RAISE_GL_COMPLETE',
466 itemtype, itemkey, TO_CHAR(actid), funcmode);
467 RAISE;
468 END raise_gl_complete;
469
470
471 /*-----------------------------------------------------
472 * PROCEDURE update_ini_complete
473 * ----------------------------------------------------
474 * Update the status to XFER_INI_GL.
475 * ---------------------------------------------------*/
476
477 PROCEDURE update_ini_complete (
478 itemtype IN varchar2,
479 itemkey IN varchar2,
480 actid IN number,
481 funcmode IN varchar2,
482 resultout IN OUT NOCOPY varchar2)
483 IS
484 l_party_type varchar2(1);
485 l_trx_id number;
486 l_status varchar2(15);
487 BEGIN
488 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
489 l_trx_id := wf_engine.GetItemAttrNumber
490 (itemtype => itemtype,
491 itemkey => itemkey,
492 aname => 'TRX_ID');
493 l_party_type := wf_engine.GetItemAttrText(itemtype => itemtype,
494 itemkey => itemkey,
498 FROM fun_trx_headers
495 aname => 'PARTY_TYPE');
496
497 SELECT status INTO l_status
499 WHERE trx_id = l_trx_id
500 FOR UPDATE;
501
502 l_status := fun_gl_transfer.update_status(l_trx_id, l_status, l_party_type);
503
504 wf_engine.SetItemAttrText(itemtype => itemtype,
505 itemkey => itemkey,
506 aname => 'STATUS',
507 avalue => l_status);
508
509 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
510 RETURN;
511 END IF;
512
513 resultout := wf_engine.eng_null;
514 RETURN;
515
516 EXCEPTION
517 WHEN others THEN
518 wf_core.context('FUN_GLINT_WF', 'UPDATE_INI_COMPLETE',
519 itemtype, itemkey, TO_CHAR(actid), funcmode);
520 RAISE;
521 END update_ini_complete;
522
523
524
525 END;