[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_ACCT_GENERATOR_PKG
Source
1 PACKAGE BODY IGI_ITR_ACCT_GENERATOR_PKG AS
2 -- $Header: igiitrrb.pls 120.5.12000000.1 2007/09/12 10:32:32 mbremkum ship $
3 --
4
5
6
7 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 l_state_level number := FND_LOG.LEVEL_STATEMENT;
9 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
10 l_event_level number := FND_LOG.LEVEL_EVENT;
11 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
12 l_error_level number := FND_LOG.LEVEL_ERROR;
13 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
14 l_path VARCHAR2(50):= 'IGI.PLSQL.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.';
15
16
17 -- ****************************************************************************
18 -- Private procedure: Display diagnostic message
19 -- ****************************************************************************
20 PROCEDURE diagn_msg ( p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2 ) IS
21 BEGIN
22 IF (p_level >= l_debug_level ) THEN
23 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
24 END IF;
25 END;
26
27
28
29 -- ****************************************************************************
30 -- Start_Acct_Generator_Workflow
31 -- ****************************************************************************
32 FUNCTION start_acct_generator_workflow (p_coa_id NUMBER,
33 p_sob_id NUMBER,
34 p_acct_type VARCHAR2,
35 p_charge_center_id NUMBER,
36 p_preparer_id NUMBER,
37 p_charge_service_id NUMBER,
38 p_cost_center_value VARCHAR2,
39 p_additional_seg_value VARCHAR2,
40 x_return_ccid IN OUT NOCOPY NUMBER,
41 x_concat_segs IN OUT NOCOPY VARCHAR2)
42 return boolean
43 IS
44
45
46
47 -- Local variables
48 l_itemtype VARCHAR2(8) := 'ITRWKFAG';
49 l_itemkey VARCHAR2(50) ;
50
51 x_appl_short_name varchar2(40);
52 x_flex_field_code varchar2(150);
53 x_flex_field_struc_num number; -- p_coa_id
54
55 result BOOLEAN;
56 -- x_return_ccid number;
57 -- x_concat_segs varchar2(2000);
58 x_concat_ids varchar2(2000);
59 x_concat_descrs varchar2(2000);
60 x_errmsg varchar2(2000);
61
62 l_return_ccid number;
63 p_char_date varchar2(27);
64
65 l_return_ccid_old number;
66 l_concat_segs_old varchar2(2000);
67
68 BEGIN
69
70 diagn_msg(l_state_level,'start_acct_generator_workflow','**** Beginning ITR account generation ****');
71
72 /* ssemwal for NOCOPY */
73 /* added l_return_ccid_old, l_concat_segs_old */
74
75 l_return_ccid_old := x_return_ccid;
76 l_concat_segs_old := x_concat_segs;
77
78 x_appl_short_name := 'SQLGL';
79 x_flex_field_code := 'GL#';
80 x_flex_field_struc_num := p_coa_id;
81
82 diagn_msg(l_state_level,'start_acct_generator_workflow','Calling fnd_flex_workflow.initialize to create workflow process');
83
84 -- The procedure fnd_flex_workflow.initialize will fetch an itemkey and
85 -- create the workflow process. The process will be 'started' later
86
87 l_itemkey := FND_FLEX_WORKFLOW.INITIALIZE (x_appl_short_name
88 ,x_flex_field_code
89 ,x_flex_field_struc_num
90 ,l_itemtype);
91
92 diagn_msg(l_state_level,'start_acct_generator_workflow','Item Key = '||l_itemkey);
93
94 /* initialize the workflow item attributes */
95
96 -- Set set of books id attribute
97 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
98 itemkey => l_itemkey,
99 aname => 'SOB_ID',
100 avalue => p_sob_id );
101 -- diagn_msg(l_state_level,'start_acct_generator_workflow','Attribute SOB_ID set to' ||(p_sob_id));
102
103 -- Set chart of accounts id attribute
104 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
105 itemkey => l_itemkey,
106 aname => 'COA_ID',
107 avalue => p_coa_id );
108 -- diagn_msg('Attribute COA_ID set to' ||(p_coa_id));
109
110
111 -- Set code combination level (header or line)
112 wf_engine.SetItemAttrText( itemtype => l_itemtype,
113 itemkey => l_itemkey,
114 aname => 'ACCT_TYPE',
115 avalue => p_acct_type );
116 -- diagn_msg('Attribute ACCT_TYPE set to' ||(p_acct_type));
117
118
119 -- Set charge_center_id (of preparer)
120 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
121 itemkey => l_itemkey,
122 aname => 'CHARGE_CENTER_ID',
123 avalue => p_charge_center_id );
124 -- diagn_msg('Attribute CHARGE_CENTER_ID set to' ||to_char(p_charge_center_id));
125
126 -- Set charge_service_id
127 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
128 itemkey => l_itemkey,
129 aname => 'CHARGE_SERVICE_ID',
130 avalue => p_charge_service_id );
131 -- diagn_msg('Attribute CHARGE_SERVICE_ID set to' ||to_char(p_charge_service_id));
132
133 -- Set cost_center_value (if chosen by preparer)
134 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
135 itemkey => l_itemkey,
136 aname => 'COST_CENTER_VALUE',
137 avalue => p_cost_center_value );
138 -- diagn_msg('Attribute COST_CENTER_VALUE set to' ||p_cost_center_value);
139
140 -- Set additional_segment_value (if chosen by preparer)
141 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
142 itemkey => l_itemkey,
143 aname => 'ADDITIONAL_SEG_VALUE',
144 avalue => p_additional_seg_value );
145 -- diagn_msg('Attribute ADDITIONAL_SEG_VALUE set to' ||p_additional_seg_value);
146
147 -- Set preparer attribute
148 wf_engine.SetItemAttrNumber( itemtype => l_itemtype,
149 itemkey => l_itemkey,
150 aname => 'PREPARER_ID',
151 avalue => p_preparer_id );
152 -- diagn_msg('Attribute PREPARER_ID set to' ||to_char(p_preparer_id));
153
154 -- Once all the attributes have been set, call the AOL function to start -- the workflow process and retrieve the results
155
156
157 diagn_msg(l_state_level,'start_acct_generator_workflow','fnd_flex_workflow.generate called');
158
159 result := FND_FLEX_WORKFLOW.generate('ITRWKFAG',
160 l_itemkey,
161 x_return_ccid,
162 x_concat_segs,
163 x_concat_ids,
164 x_concat_descrs,
165 x_errmsg);
166
167 IF result THEN
168 -- diagn_msg('Successful. Ccid = '||to_char(x_return_ccid));
169 -- diagn_msg('Concat Segs = '||x_concat_segs);
170
171 IF (x_return_ccid = -1) THEN
172 select to_char(sysdate,'DD-MON-RRRR')
173 into p_char_date
174 FROM dual;
175
176 l_return_ccid := FND_FLEX_EXT.get_ccid(
177 'SQLGL',
178 'GL#',
179 p_coa_id,
180 p_char_date,
181 x_concat_segs);
182 IF (l_return_ccid = 0) THEN
183 diagn_msg(l_error_level,'start_acct_generator_workflow','No ccid found');
184 return FALSE;
185 ELSE
186 x_return_ccid := l_return_ccid;
187 diagn_msg(l_state_level,'start_acct_generator_workflow','return ccid = '||x_return_ccid);
188 END IF;
189
190 END IF; /* if x_return_ccid = -1 */
191
192 diagn_msg(l_state_level,'start_acct_generator_workflow','return ccid = '||x_return_ccid);
193 RETURN result;
194 ELSE
195 diagn_msg(l_error_level,'start_acct_generator_workflow','Unsuccessful');
196 RETURN result;
197 END IF;
198
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 x_return_ccid := l_return_ccid_old;
203 x_concat_segs := l_concat_segs_old;
204 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'start_acct_generator_workflow', l_itemtype, l_itemkey);
205 IF ( l_unexp_level >= l_debug_level) THEN
206 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
207 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
208 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
209 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.start_acct_generator_workflow',TRUE);
210 END IF;
211 raise;
212
213 END start_acct_generator_workflow;
214
215
216 --
217 --****************************************************************************
218 -- account_type
219 -- *****************************************************************************
220 --
221
222 --
223 -- Procedure
224 -- account_type
225 -- Purpose
226 -- Retrieve the account type for which the code combination is required
227 -- i.e. the creating charge center's account (Creation account)
228 -- or the receiving charge center's account (Receiving account)
229 -- This is important because creation and receiving combinations
230 -- are generated using different rules.
231 -- History
232 -- 03-NOV-2000 S Brewer Created.
233 -- Arguments
234 -- itemtype Workflow item type (ITR Account Generator)
235 -- itemkey fnd flex workflow item key
236 -- actid ID of activity, provided by workflow engine
237 -- (not used in this procedure)
238 -- funcmode Function mode (RUN or CANCEL)
239 -- result Result code of the activity
240 -- Example
241 -- N/A (not user-callable)
242 --
243 -- Notes
244 -- This procedure is called from the Oracle Workflow engine
245 -- It retrieves the account type for which the code combination
246 -- is required, which is then used to determine which process the
247 -- workflow should call next
248 --
249 PROCEDURE account_type ( itemtype IN VARCHAR2,
250 itemkey IN VARCHAR2,
251 actid IN NUMBER,
252 funcmode IN VARCHAR2,
253 result OUT NOCOPY VARCHAR2 ) IS
254
255 l_acct_type VARCHAR2(1);
256
257 BEGIN
258
259 IF ( funcmode = 'RUN' ) THEN
260
261 diagn_msg(l_state_level,'account_type','Procedure account type being executed');
262 -- Get acct_type
263 l_acct_type := wf_engine.GetItemAttrText(
264 itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'ACCT_TYPE');
267
268
269 diagn_msg(l_state_level,'account_type','Account type retrieved : '||l_acct_type);
270 IF l_acct_type = 'C' THEN
271 result := 'COMPLETE:C';
272 return;
273 ELSIF l_acct_type = 'R' THEN
274 result := 'COMPLETE:R';
275 return;
276 -- ELSE exception;
277 END IF;
278
279 ELSIF ( funcmode = 'CANCEL' ) THEN
280 null;
281 END IF;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 result := null;
286 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG','account_type', itemtype, itemkey);
287 IF ( l_unexp_level >= l_debug_level) THEN
288 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
289 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
290 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
291 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.account_type',TRUE);
292 END IF;
293
294 raise;
295 END account_type;
296
297
298 --
299 -- *****************************************************************************
300 -- Fetch_Creation_Account
301 -- *****************************************************************************
302 --
303 PROCEDURE fetch_creation_account(itemtype IN VARCHAR2,
304 itemkey IN VARCHAR2,
305 actid IN NUMBER,
306 funcmode IN VARCHAR2,
307 result OUT NOCOPY VARCHAR2 ) IS
308
309
310 l_charge_center_id NUMBER;
311 l_charge_service_id NUMBER;
312 l_creation_ccid NUMBER;
313
314 --
315 BEGIN
316
317 IF ( funcmode = 'RUN' ) THEN
318 diagn_msg(l_state_level,'account_type','Procedure fetch_creation_account being executed');
319
320 -- Get charge center ID for the creator
321 l_charge_center_id := wf_engine.GetItemAttrNumber(
322 itemtype => itemtype,
323 itemkey => itemkey,
324 aname => 'CHARGE_CENTER_ID');
325
326 -- Get service type ID chosen by the user
327 l_charge_service_id := wf_engine.GetItemAttrNumber(
328 itemtype => itemtype,
329 itemkey => itemkey,
330 aname => 'CHARGE_SERVICE_ID');
331
332 -- using the charge center and the charge service type chosen by the user
333 -- retrieve the creation charge center's service type account
334 SELECT creation_ccid
335 INTO l_creation_ccid
336 FROM igi_itr_charge_service serv
337 WHERE serv.charge_center_id = l_charge_center_id
338 AND serv.charge_service_id = l_charge_service_id
339 AND sysdate BETWEEN nvl(serv.start_date,sysdate)
340 AND nvl(serv.end_date,sysdate);
341
342
343 diagn_msg(l_state_level,'account_type','Retrieved creation ccid: '||to_char(l_creation_ccid)||
344 ' for charge center '||to_char(l_charge_center_id)||
345 ' and charge_service_id '||to_char(l_charge_service_id));
346
347 -- set workflow attribute
348 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
349 itemkey => itemkey,
350 aname => 'CREATION_CCID',
351 avalue => l_creation_ccid);
352
353 IF l_creation_ccid is not null THEN
354 result := 'COMPLETE:SUCCESS';
355 return;
356 ELSE
357 result := 'COMPLETE:FAILURE';
358 return;
359 END IF;
360
361
362 ELSIF ( funcmode = 'CANCEL' ) THEN
363 null;
364 END IF;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 result := null;
369 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'fetch_creation_account', itemtype, itemkey);
370 IF ( l_unexp_level >= l_debug_level) THEN
371 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
372 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
373 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
377 raise;
374 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.fetch_creation_account',TRUE);
375 END IF;
376
378
379 END fetch_creation_account;
380
381
382
383 --
384 -- *****************************************************************************
385 -- Find_No_Of_Segs
386 -- *****************************************************************************
387 --
388 PROCEDURE find_no_of_segs ( itemtype IN VARCHAR2,
389 itemkey IN VARCHAR2,
390 actid IN NUMBER,
391 funcmode IN VARCHAR2,
392 result OUT NOCOPY VARCHAR2 ) IS
393
394 l_coa_id NUMBER;
395 l_no_of_segs NUMBER;
396 l_loop_limit NUMBER;
397
398
399 BEGIN
400 IF ( funcmode = 'RUN') THEN
401 -- diagn_msg('Procedure find_no_of_segs being executed');
402
403
404 -- fetch the chart of accounts id to be used for finding the number of
405 -- segments
406 l_coa_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
407 itemkey => itemkey,
408 aname => 'COA_ID');
409
410 -- diagn_msg('Chart of Accounts Id fetched '||to_char(l_coa_id));
411
412 -- find the number of segments defined for this chart of accounts
413 SELECT count(*)
414 INTO l_no_of_segs
415 FROM fnd_id_flex_segments
416 WHERE application_id = 101
417 AND id_flex_code = 'GL#'
418 AND id_flex_num = l_coa_id;
419
420
421 -- diagn_msg('Number of Segments Fetched :'||to_char(l_no_of_segs));
422
423 -- set the number of segments workflow attribute
424 wf_engine.SetItemAttrNumber( itemtype => itemtype,
425 itemkey => itemkey,
426 aname => 'NO_OF_SEGS',
427 avalue => l_no_of_segs);
428
429 diagn_msg(l_state_level,'find_no_of_segs','Attribute NO_OF_SEGS set to' ||(l_no_of_segs));
430
431 -- set the loop limit to (number of segments - 1)
432 l_loop_limit := l_no_of_segs - 1;
433
434 -- set the loop limit workflow attribute
435 wf_engine.SetItemAttrNumber( itemtype => itemtype,
436 itemkey => itemkey,
437 aname => 'LOOP_LIMIT',
438 avalue => l_loop_limit);
439
440 diagn_msg(l_state_level,'find_no_of_segs','Attribute LOOP_LIMIT set to' ||(l_loop_limit));
441
442 ELSIF ( funcmode = 'CANCEL' ) THEN
443 null;
444 END IF;
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 result := null;
449 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'find_no_of_segs', itemtype, itemkey);
450 IF ( l_unexp_level >= l_debug_level) THEN
451 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
452 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
453 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
454 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.find_no_of_segs',TRUE);
455 END IF;
456 raise;
457 END find_no_of_segs;
458
459
460
461 --
462 -- *****************************************************************************
463 -- Increase_Counter
464 -- *****************************************************************************
465 --
466 PROCEDURE increase_counter( itemtype IN VARCHAR2,
467 itemkey IN VARCHAR2,
468 actid IN NUMBER,
469 funcmode IN VARCHAR2,
470 result OUT NOCOPY VARCHAR2 ) IS
471
472 l_counter NUMBER;
473 l_segmenti_number VARCHAR2(15);
474
475 BEGIN
476 IF ( funcmode = 'RUN') THEN
477 -- diagn_msg('Procedure increase_counter being executed');
478
479 -- fetch the counter value to be increased
480 l_counter := wf_engine.GetItemAttrNumber( itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'COUNTER');
483
484 -- diagn_msg('Counter fetched '||to_char(l_counter));
485
486 -- increase counter value by 1
487 l_counter := l_counter + 1;
488
489 -- set the counter workflow attribute
490 wf_engine.SetItemAttrNumber( itemtype => itemtype,
491 itemkey => itemkey,
492 aname => 'COUNTER',
493 avalue => l_counter);
494
495 -- diagn_msg('Attribute COUNTER set to' ||(l_counter));
496
497 -- set the segment number (application_column_name)
498 l_segmenti_number := 'SEGMENT'||to_char(l_counter);
499
500 -- diagn_msg('segmenti number set to '||l_segmenti_number);
501
502 -- set the segment number workflow attribute
503 wf_engine.SetItemAttrText( itemtype => itemtype,
504 itemkey => itemkey,
505 aname => 'SEGMENTI_NUMBER',
506 avalue => l_segmenti_number);
507
508 -- diagn_msg('Attribute SEGMENTI_NUMBER set to' ||(l_segmenti_number));
509
510 ELSIF ( funcmode = 'CANCEL' ) THEN
511 null;
512 END IF;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 result := null;
517 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'increase_counter', itemtype, itemkey);
518 IF ( l_unexp_level >= l_debug_level) THEN
519 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
520 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
524 raise;
521 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
522 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.increase_counter',TRUE);
523 END IF;
525 END increase_counter;
526
527
528 --
529 -- *****************************************************************************
530 -- Fetch_Segmenti_Value
531 -- *****************************************************************************
532 --
533 PROCEDURE fetch_segmenti_value( itemtype IN VARCHAR2,
534 itemkey IN VARCHAR2,
535 actid IN NUMBER,
536 funcmode IN VARCHAR2,
537 result OUT NOCOPY VARCHAR2 ) IS
538
539 l_segmenti_number VARCHAR2(15);
540 l_segmenti_value VARCHAR2(30);
541 l_preparer_id NUMBER;
542 l_set_of_books_id NUMBER;
543
544 BEGIN
545 IF ( funcmode = 'RUN') THEN
546 -- diagn_msg('Procedure fetch_segmenti_value being executed');
547
548 -- get the segment number of the segment we want to find a value
549 -- for (from the workflow attribute 'SEGMENTI_NUMBER')
550 l_segmenti_number := wf_engine.GetItemAttrText( itemtype => itemtype,
551 itemkey => itemkey,
552 aname => 'SEGMENTI_NUMBER');
553
554 -- diagn_msg('Segment i number fetched '||l_segmenti_number);
555
556 -- get the id of the preparer
557 l_preparer_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
558 itemkey => itemkey,
559 aname => 'PREPARER_ID');
560
561 -- diagn_msg('Preparer Id fetched '||l_preparer_id);
562
563 -- get the set of books id
564 l_set_of_books_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'SOB_ID');
567
568 -- diagn_msg('Set of Books Id fetched '||l_set_of_books_id);
569
570 -- fetch originator's value (if it exists) for this segment
571 -- will need to use native dynamic sql here since we do not know the column
572 -- we are selecting from
573
574 EXECUTE immediate 'SELECT '||l_segmenti_number||
575 ' FROM igi_itr_charge_orig orig'||
576 ' ,igi_itr_charge_center center'||
577 ' WHERE orig.originator_id = :preparer_id'||
578 ' AND sysdate BETWEEN nvl(orig.start_date,sysdate)'||
579 ' AND nvl(orig.end_date,sysdate) '||
580 ' AND orig.charge_center_id = center.charge_center_id'||
581 ' AND center.set_of_books_id = :set_of_books_id'||
582 ' AND sysdate BETWEEN'||
583 ' nvl(center.start_date_active,sysdate)'||
584 ' AND nvl(center.end_date_active,sysdate)'
585 INTO l_segmenti_value
586 USING l_preparer_id, l_set_of_books_id;
587
588 IF l_segmenti_value is not null THEN
589
590 -- set segmenti_value workflow attribute
591 wf_engine.SetItemAttrText ( itemtype => itemtype,
592 itemkey => itemkey,
593 aname => 'SEGMENTI_VALUE',
594 avalue => l_segmenti_value );
595 diagn_msg(l_state_level,'fetch_segmenti_value','Attribute SEGMENTI_VALUE set to' ||l_segmenti_value||
596 'for segment '||l_segmenti_number);
597
598 result := 'COMPLETE:Y';
599 return;
600 ELSE
601 result := 'COMPLETE:N';
602 return;
603 END IF;
604
605
606 ELSIF ( funcmode = 'CANCEL' ) THEN
607 null;
608 END IF;
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 result := null;
613 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'fetch_segmenti_value', itemtype, itemkey);
614 IF ( l_unexp_level >= l_debug_level) THEN
615 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
616 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
617 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
618 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.fetch_segmenti_value',TRUE);
619 END IF;
620 raise;
621 END fetch_segmenti_value;
622
623 --
624 -- *****************************************************************************
625 -- Fetch_Segmenti_Name
626 -- *****************************************************************************
627 --
628 PROCEDURE fetch_segmenti_name ( itemtype IN VARCHAR2,
629 itemkey IN VARCHAR2,
630 actid IN NUMBER,
631 funcmode IN VARCHAR2,
632 result OUT NOCOPY VARCHAR2 ) IS
633
634
635 l_segmenti_number VARCHAR2(15);
636 l_coa_id NUMBER;
637 l_segmenti_name VARCHAR2(30);
638
639 BEGIN
640 IF ( funcmode = 'RUN') THEN
641 -- diagn_msg('Procedure fetch_segmenti_name being executed');
642
643 -- get the segment number of the segment we want to find the segment name
644 -- for (from the workflow attribute 'SEGMENTI_NUMBER')
645
646 l_segmenti_number := wf_engine.GetItemAttrText( itemtype => itemtype,
647 itemkey => itemkey,
648 aname => 'SEGMENTI_NUMBER');
649
650 -- diagn_msg('Segment i number fetched '||l_segmenti_number);
651
652 -- get the chart of accounts id (from the workflow attribute)
653 l_coa_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
654 itemkey => itemkey,
655 aname => 'COA_ID');
656
657 -- diagn_msg('Chart of Accounts Id fetched '||l_coa_id);
658
659 -- find the segment name for the segmenti
663 WHERE application_id = 101
660 SELECT segment_name
661 INTO l_segmenti_name
662 FROM fnd_id_flex_segments
664 AND id_flex_code = 'GL#'
665 AND id_flex_num = l_coa_id
666 AND application_column_name = l_segmenti_number;
667
668
669 -- diagn_msg('Segment name found = '||l_segmenti_name);
670
671 -- set the segmenti name workflow attribute
672 wf_engine.SetItemAttrText ( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'SEGMENTI_NAME',
675 avalue => l_segmenti_name );
676 diagn_msg(l_state_level,'fetch_segmenti_name','Attribute SEGMENTI_NAME set to' ||l_segmenti_name);
677
678
679 ELSIF ( funcmode = 'CANCEL' ) THEN
680 null;
681 END IF;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 result := null;
686 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'fetch_segmenti_name', itemtype, itemkey);
687 IF ( l_unexp_level >= l_debug_level) THEN
688 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
689 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
690 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
691 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.fetch_segmenti_name',TRUE);
692 END IF;
693 raise;
694 END fetch_segmenti_name;
695
696
697
698 --
699 -- *****************************************************************************
700 -- Cost_Center_Value_Chosen
701 -- *****************************************************************************
702 --
703 PROCEDURE cost_center_value_chosen (itemtype IN VARCHAR2,
704 itemkey IN VARCHAR2,
705 actid IN NUMBER,
706 funcmode IN VARCHAR2,
707 result OUT NOCOPY VARCHAR2 ) IS
708
709 l_cost_center_value VARCHAR2(30);
710 BEGIN
711 IF ( funcmode = 'RUN') THEN
712
713 diagn_msg(l_state_level,'cost_center_value_chosen','Procedure cost_center_value_chosen being executed');
714 l_cost_center_value := wf_engine.GetItemAttrText ( itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'COST_CENTER_VALUE');
717 IF l_cost_center_value is not null THEN
718 diagn_msg(l_state_level,'cost_center_value_chosen','Cost center value has been chosen ');
719 diagn_msg(l_state_level,'cost_center_value_chosen','Cost center value :'||l_cost_center_value);
720 result := 'COMPLETE:Y';
721 return;
722 ELSE
723 diagn_msg(l_state_level,'cost_center_value_chosen','Cost center value has not been chosen');
724 result := 'COMPLETE:N';
725 return;
726 END IF;
727
728 ELSIF ( funcmode = 'CANCEL' ) THEN
729 null;
730 END IF;
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 result := null;
735 Wf_Core.Context('IGI_ITR_ACCOUNT_GENERATOR_PKG', 'cost_center_value_chosen', itemtype, itemkey);
736 IF ( l_unexp_level >= l_debug_level) THEN
737 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
738 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
739 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
740 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.cost_center_value_chosen',TRUE);
741 END IF;
742
743 raise;
744 END cost_center_value_chosen;
745
746
747
748 --
749 -- *****************************************************************************
750 -- Additional_Seg_Value_Chosen
751 -- *****************************************************************************
752 --
753 PROCEDURE Additional_Seg_Value_Chosen(itemtype IN VARCHAR2,
754 itemkey IN VARCHAR2,
755 actid IN NUMBER,
756 funcmode IN VARCHAR2,
757 result OUT NOCOPY VARCHAR2 ) IS
758
759 l_additional_seg_value VARCHAR2(30);
760 BEGIN
761
762 IF ( funcmode = 'RUN') THEN
763 diagn_msg(l_state_level,'Additional_Seg_Value_Chosen','Procedure additional_seg_value_chosen being executed');
764
765 l_additional_seg_value := wf_engine.GetItemAttrText ( itemtype => itemtype,
766 itemkey => itemkey,
767 aname => 'ADDITIONAL_SEG_VALUE');
768
769
770 IF l_additional_seg_value is not null THEN
771 diagn_msg(l_state_level,'Additional_Seg_Value_Chosen','Additional segment value has been chosen ');
772 diagn_msg(l_state_level,'Additional_Seg_Value_Chosen','Additional segment value :'||l_additional_seg_value);
773 result := 'COMPLETE:Y';
774 return;
775 ELSE
776 diagn_msg(l_state_level,'Additional_Seg_Value_Chosen','Additional segment value has not been chosen');
777 result := 'COMPLETE:N';
778 return;
779 END IF;
780
781
782 ELSIF ( funcmode = 'CANCEL' ) THEN
783 null;
784 END IF;
785
786 EXCEPTION
787 WHEN OTHERS THEN
788 result := null;
789 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'additional_seg_value_chosen', itemtype, itemkey);
790 IF ( l_unexp_level >= l_debug_level) THEN
791 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
792 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
793 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
794 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.additional_seg_value_chosen',TRUE);
795 END IF;
796 raise;
797 END additional_seg_value_chosen;
798
799 --
800 -- *****************************************************************************
804 PROCEDURE Fetch_Additional_Seg_Name(itemtype IN VARCHAR2,
801 -- Fetch_Additional_Seg_Name
802 -- *****************************************************************************
803 --
805 itemkey IN VARCHAR2,
806 actid IN NUMBER,
807 funcmode IN VARCHAR2,
808 result OUT NOCOPY VARCHAR2 ) IS
809
810 l_additional_seg_name VARCHAR2(30);
811 l_sob_id NUMBER;
812
813 BEGIN
814
815 IF ( funcmode = 'RUN') THEN
816
817 diagn_msg(l_state_level,'Fetch_Additional_Seg_Name','Procedure fetch_additional_seg_name being executed');
818
819 l_sob_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
820 itemkey => itemkey,
821 aname => 'SOB_ID');
822
823
824
825
826 -- Fetch segment name for additional segment
827 SELECT segment_name
828 INTO l_additional_seg_name
829 FROM igi_itr_charge_setup
830 WHERE set_of_books_id = l_sob_id;
831
832 -- Set additional_segment_name attribute
833 wf_engine.SetItemAttrText ( itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'ADDITIONAL_SEG_NAME',
836 avalue => l_additional_seg_name );
837 diagn_msg(l_state_level,'Fetch_Additional_Seg_Name','Attribute ADDITIONAL_SEG_NAME set to' ||l_additional_seg_name);
838
839
840 ELSIF ( funcmode = 'CANCEL' ) THEN
841 null;
842 END IF;
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 result := null;
847 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'fetch_additional_seg_name', itemtype, itemkey);
848 IF ( l_unexp_level >= l_debug_level) THEN
849 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
850 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
851 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
852 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.fetch_additional_seg_name',TRUE);
853 END IF;
854 raise;
855 END fetch_additional_seg_name;
856
857
858
859 --
860 -- *****************************************************************************
861 -- Fetch_Service_Receiving_Acct
862 -- *****************************************************************************
863 --
864 PROCEDURE fetch_service_receiving_acct (itemtype IN VARCHAR2,
865 itemkey IN VARCHAR2,
866 actid IN NUMBER,
867 funcmode IN VARCHAR2,
868 result OUT NOCOPY VARCHAR2 ) IS
869
870 l_charge_center_id NUMBER;
871 l_charge_service_id NUMBER;
872 l_service_receiving_ccid NUMBER;
873
874 BEGIN
875 IF ( funcmode = 'RUN') THEN
876
877 diagn_msg(l_state_level,'fetch_service_receiving_acct', 'Procedure fetch_service_receiving_acct being executed');
878
879 l_charge_center_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
880 itemkey => itemkey,
881 aname => 'CHARGE_CENTER_ID');
882
883 l_charge_service_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
884 itemkey => itemkey,
885 aname => 'CHARGE_SERVICE_ID');
886
887 -- Fetch receiving ccid for service type
888 SELECT receiving_ccid
889 INTO l_service_receiving_ccid
890 FROM igi_itr_charge_service serv
891 WHERE serv.charge_service_id = l_charge_service_id
892 AND serv.charge_center_id = l_charge_center_id
893 AND sysdate BETWEEN nvl(serv.start_date,sysdate)
894 AND nvl(serv.end_date,sysdate);
895
896 -- Set service_type_ccid attribute
897 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'SERVICE_TYPE_RECEIVING_CCID',
900 avalue => l_service_receiving_ccid );
901 diagn_msg(l_state_level,'fetch_service_receiving_acct','Attribute SERVICE_TYPE_RECEIVING_CCID set to ' ||l_service_receiving_ccid);
902
903
904 IF l_service_receiving_ccid is not null THEN
905 result := 'COMPLETE:SUCCESS';
906 return;
907 ELSE
908 result := 'COMPLETE:FAILURE';
909 return;
910 END IF;
911
912 ELSIF ( funcmode = 'CANCEL' ) THEN
913 null;
914 END IF;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 result := null;
919 Wf_Core.Context('IGI_ITR_ACCT_GENERATOR_PKG', 'fetch_service_receiving_acct', itemtype, itemkey);
920 IF ( l_unexp_level >= l_debug_level) THEN
921 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
922 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
923 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
924 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrrb.IGI_ITR_ACCT_GENERATOR_PKG.fetch_service_receiving_acct',TRUE);
925 END IF;
926 raise;
927 END fetch_service_receiving_acct;
928
929
930 --
931
932 END IGI_ITR_ACCT_GENERATOR_PKG;