[Home] [Help]
PACKAGE BODY: APPS.PO_WF_PO_RULE_ACC
Source
1 PACKAGE BODY PO_WF_PO_RULE_ACC AS
2 /* $Header: POXRUACB.pls 120.1 2005/06/03 01:15:44 appldev $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 l_segment_table t_segment_table;
8 l_counter NUMBER := -1;
9
10 --------------------------------------------------------------------
11 -- Procedure get_default_requester_acc gets the requester's default
12 -- charge account code_combination_id.
13 --
14 PROCEDURE get_default_requester_acc ( itemtype in varchar2,
15 itemkey in varchar2,
16 actid in number,
17 funcmode in varchar2,
18 result out NOCOPY varchar2 )
19 is
20 x_progress varchar2(100);
21 x_requester_id NUMBER;
22 x_ccid NUMBER;
23 --<Bug2711577 fix variable define START>
24 x_bg_id_hr NUMBER;
25 x_bg_id_fsp NUMBER;
26 --<Bug2711577 fix variable define END>
27
28 --< Shared Proc FPJ Start >
29 l_expense_rules_org_id NUMBER;
30 --< Shared Proc FPJ End >
31
32 BEGIN
33
34 x_progress := 'PO_WF_PO_RULE_ACC.get_default_requester_acc: 01';
35 IF (g_po_wf_debug = 'Y') THEN
36 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
37 END IF;
38
39 -- Do nothing in cancel or timeout mode
40 --
41 if (funcmode <> wf_engine.eng_run) then
42
43 result := wf_engine.eng_null;
44 return;
45
46 end if;
47
48 x_progress := 'PO_WF_PO_RULE_ACC.get_default_requester_acc: 02';
49 x_requester_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype => itemtype,
50 itemkey => itemkey,
51 aname => 'TO_PERSON_ID');
52
53 --< Shared Proc FPJ Start >
54
55 -- The Expense Account Rules are called from the PO AG Workflow
56 -- twice -- once for POU and then second time for DOU. Therefore,
57 -- all the queries in this package that assume the ORG_ID from
58 -- the org context, needs to join explicitly to the ORG_ID given
59 -- in the attribute EXPENSE_RULES_ORG_ID.
60 -- This attribute is populated in the WF, before calling the
61 -- Expense Account rules to either POU or DOU's org ID depending
62 -- on which OU's accounts are being generated.
63 -- For Req AG Workflow, the attribute EXPENSE_RULES_ORG_ID
64 -- would not be present. In that case,
65 -- the PO_WF_UTIL_PKG.GetItemAttrNumber() would return NULL.
66 -- Then, we would populate it with the ORG_ID in the org context.
67
68 l_expense_rules_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
69 itemtype => itemtype,
70 itemkey => itemkey,
71 aname => 'EXPENSE_RULES_ORG_ID');
72
73 -- If it is NULL and the org context's org_id is not null, then copy
74 -- org_context's org_id.
75 IF l_expense_rules_org_id IS NULL THEN
76 l_expense_rules_org_id := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
77 END IF;
78 --< Shared Proc FPJ End >
79
80 --<Bug2711577 fix code change START>
81
82 --Replaced the previous piece of code to include foll. SQL.
83 -- SQL What: Querying for Code Combination ID for the employee
84 -- SQL Why: Need to build the Charge Account Values
85 -- SQL Join: employee_id and business_group_id
86
87 -- bug 2744108
88 -- The chart of accounts tied to the user can differ from that of the org.
89 -- This check prevents an invalid attempt to copy values between the
90 -- differing flex field structures at a later step in the workflow process.
91 -- <re-opened due to bug 3589917>
92 -- The check should be that the chart of accounts associated with the
93 -- current org and the chart of accounts associated with the employee
94 -- are consistent, instead of the set of books being identical.
95 -- Different sets of books can share the same chart of accounts.
96
97 SELECT min(hrecv.default_code_combination_id),min(hrecv.business_group_id),
98 min(fsp.business_group_id)
99 INTO x_ccid,x_bg_id_hr,x_bg_id_fsp
100 FROM per_workforce_current_x hrecv, --R12 CWK Enhancement
101
102 --< Shared Proc FPJ Start >
103 --financials_system_parameters fsp
104 financials_system_params_all fsp
105 --< Shared Proc FPJ End >
106 , GL_SETS_OF_BOOKS emp_sob
107 , GL_SETS_OF_BOOKS org_sob
108
109 WHERE hrecv.person_id = x_requester_id
110 AND hrecv.business_group_id = fsp.business_group_id
111 AND org_sob.set_of_books_id = fsp.set_of_books_id
112 AND emp_sob.set_of_books_id = hrecv.set_of_books_id
113 AND emp_sob.chart_of_accounts_id = org_sob.chart_of_accounts_id
114
115 --< Shared Proc FPJ Start >
116 -- NVL is required for the single-org instance case.
117 AND NVL(fsp.org_id, -99) = NVL(l_expense_rules_org_id, -99);
118 --< Shared Proc FPJ End >
119
120
121 x_progress := 'PO_WF_PO_RULE_ACC.get_default_requester_acc: 03';
122 if x_ccid is not null then
123 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => itemtype,
124 itemkey => itemkey,
125 aname => 'DEFAULT_ACCT_ID',
126 avalue => x_ccid );
127
128 --<Bug2711577 fix code change END>
129
130 result := 'COMPLETE:SUCCESS';
131 else
132 result := 'COMPLETE:FAILURE';
133 end if;
134
135 RETURN;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 wf_core.context('PO_WF_PO_RULE_ACC','get_default_requester_acc',x_progress);
140 result := 'COMPLETE:FAILURE';
141 RETURN;
142
143 END get_default_requester_acc;
144
145 PROCEDURE get_favorite_charge_acc (
146 itemtype in varchar2,
147 itemkey in varchar2,
148 actid in number,
149 funcmode in varchar2,
150 result out NOCOPY varchar2 )
151 is
152 x_progress varchar2(100);
153 x_user_id NUMBER;
154 x_resp_id NUMBER;
155 x_ccid NUMBER;
156
157 BEGIN
158
159 x_progress := 'PO_WF_PO_RULE_ACC.get_favorite_charge_acc: 01';
160
161 IF (g_po_wf_debug = 'Y') THEN
162 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
163 END IF;
164
165 if (funcmode <> wf_engine.eng_run) then
166 result := wf_engine.eng_null;
167 return;
168 end if;
169
170 -- Verify if the user has access to Favorite charge account function
171 IF NOT FND_FUNCTION.TEST('POR_FAV_CHG_ACCT') THEN
172 x_progress := 'PO_WF_PO_RULE_ACC.get_favorite_charge_acc: Favorite charge account functionality not provided';
173 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
174 result := 'COMPLETE:FAILURE'; -- Bug 3626954 Return failure instead of success
175 RETURN;
176 END IF;
177
178 x_progress := 'PO_WF_PO_RULE_ACC.get_favorite_charge_acc: 02';
179
180 x_user_id := FND_GLOBAL.EMPLOYEE_ID;
181
182 x_resp_id := FND_GLOBAL.RESP_ID;
183
184 x_progress := 'PO_WF_PO_RULE_ACC. get_favorite_charge_acc: 03';
185
186 Select CHARGE_ACCOUNT_ID
187 into x_ccid
188 from POR_FAV_CHARGE_ACCOUNTS
189 where EMPLOYEE_ID = x_user_id and
190 RESPONSIBILITY_ID = x_resp_id and
191 DEFAULT_ACCOUNT = 'Y';
192
193 x_progress := 'PO_WF_PO_RULE_ACC.get_favorite_charge_acc: 04';
194
195
196 if x_ccid is not null then
197 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => itemtype,
198 itemkey => itemkey,
199 aname => 'DEFAULT_ACCT_ID',
200 avalue => x_ccid );
201 result := 'COMPLETE:SUCCESS';
202 else
203 result := 'COMPLETE:FAILURE';
204 end if;
205
206 RETURN;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 wf_core.context('PO_WF_PO_RULE_ACC','get_favorite_charge_acc',x_progress);
211 result := 'COMPLETE:FAILURE';
212 RETURN;
213
214 END get_favorite_charge_acc;
215
216
217 PROCEDURE if_enforce_expense_acc_rules(
218 itemtype in varchar2,
219 itemkey in varchar2,
220 actid in number,
221 funcmode in varchar2,
222 result out NOCOPY varchar2 )
223 is
224 l_progress varchar2(100);
225 l_options_value VARCHAR2(30);
226
227 BEGIN
228 l_progress := 'PO_WF_PO_RULE_ACC.if_enforce_expense_acc_rules: 01';
229
230 fnd_profile.get('POR_REQ_ENFORCE_EXP_ACC_RULE', l_options_value);
231
232 l_progress := 'PO_WF_PO_RULE_ACC.if_enforce_expense_acc_rules: 02';
233
234 if l_options_value is not null then
235 result:='COMPLETE:'|| l_options_value;
236 return;
237 else
238 result:='COMPLETE:'|| 'N';
239 return;
240 end if;
241
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 WF_CORE.context('PO_WF_PO_RULE_ACC','if_enforce_expense_acc_rules', l_progress);
246 result:='COMPLETE:'||'N';
247
248 END if_enforce_expense_acc_rules;
249
250
251 PROCEDURE if_rule_exist_for_all_segments (
252 itemtype in varchar2,
253 itemkey in varchar2,
254 actid in number,
255 funcmode in varchar2,
256 result out NOCOPY varchar2 )
257
258 IS
259 l_progress varchar2(100);
260 l_segments_number_sob NUMBER :=0;
261 l_get_result varchar2(25) := NULL;
262 l_segment_array FND_FLEX_EXT.SegmentArray;
263 l_delimiter VARCHAR2(10);
264 l_chart_of_accounts_id NUMBER;
265 l_concat_segs VARCHAR2(2000);
266 l_ccId NUMBER;
267
268 --< Shared Proc FPJ Start >
269 l_expense_rules_org_id NUMBER;
270 --< Shared Proc FPJ End >
271
272 BEGIN
273
274 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 01';
275
276 IF (g_po_wf_debug = 'Y') THEN
277 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
278 END IF;
279
280 if (funcmode <> wf_engine.eng_run) then
281 result := wf_engine.eng_null;
282 return;
283 end if;
284
285 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 02';
286
287 --< Shared Proc FPJ Start >
288
289 -- The Expense Account Rules are called from the PO AG Workflow
290 -- twice -- once for POU and then second time for DOU. Therefore,
291 -- all the queries in this package that assume the ORG_ID from
292 -- the org context, needs to join explicitly to the ORG_ID given
293 -- in the attribute EXPENSE_RULES_ORG_ID.
294 -- This attribute is populated in the WF, before calling the
295 -- Expense Account rules to either POU or DOU's org ID depending
296 -- on which OU's accounts are being generated.
297 -- For Req AG Workflow, the attribute EXPENSE_RULES_ORG_ID
298 -- would not be present. In that case,
299 -- the PO_WF_UTIL_PKG.GetItemAttrNumber() would return NULL.
300 -- Then, we would populate it with the ORG_ID in the org context.
301
302 l_expense_rules_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
303 itemtype => itemtype,
304 itemkey => itemkey,
305 aname => 'EXPENSE_RULES_ORG_ID');
306
307 -- If it is NULL and the org context's org_id is not null, then copy
308 -- org_context's org_id.
309 IF l_expense_rules_org_id IS NULL THEN
310 l_expense_rules_org_id := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
311 END IF;
312
313 IF (g_po_wf_debug = 'Y') THEN
314 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
315 'PO_WF_PO_RULE_ACC.if_rule_exist_for_all_segments '||
316 'l_expense_rules_org_id='||l_expense_rules_org_id);
317 END IF;
318
319 --< Shared Proc FPJ End >
320
321 -- create table l_segment_table for expense account rule
322 get_segment_records(itemtype, itemkey, l_get_result);
323
324 IF (g_po_wf_debug = 'Y') THEN
325 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
326 'PO_WF_PO_RULE_ACC.if_rule_exist_for_all_segments '||
327 'get_segment_records->result='||l_get_result);
328 END IF;
329
330
331 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 03';
332
333 if (l_get_result = 'fail') then
334 result := 'COMPLETE:'||'N';
335 l_counter := -1;
336 return;
337 end if;
338
339 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 04';
340
341 -- set l_segments_number_sob , the total number of account segments for
342 -- the current set of books, by any query or api from GL, sent email to
343 -- ask Gursat.Olgun for the query or api.
344
345 select count(*)
346 into l_segments_number_sob
347 from FND_ID_FLEX_SEGMENTS fs,
348
349 --< Shared Proc FPJ Start >
350 --financials_system_parameters fsp,
351 financials_system_params_all fsp,
352 --< Shared Proc FPJ End >
353
354 gl_sets_of_books gls
355 where fsp.set_of_books_id = gls.set_of_books_id and
356 fs.id_flex_num = gls.chart_of_accounts_id and
357 fs.id_flex_code = 'GL#' and
358 fs.application_id = 101 AND
359
360 --< Shared Proc FPJ Start >
361 -- NVL is required for the single-org instance case.
362 NVL(fsp.org_id, -99) = NVL(l_expense_rules_org_id, -99);
363 --< Shared Proc FPJ End >
364
365 if ( l_segment_table.count = l_segments_number_sob) THEN
366
367 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 05';
368
369 -- get concatenated account segments
370 FOR i IN 0..l_segment_table.count-1 LOOP
371 wf_engine.SetItemAttrText(itemtype, itemkey,
372 'FND_FLEX_SEGMENT' || TO_CHAR(i+1),
373 l_segment_table(i).segment_value);
374 l_segment_array(i+1) := l_segment_table(i).segment_value;
375 END LOOP;
376
377 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 06';
378
379 -- get chart_of_accounts_id
380
381 select gls.chart_of_accounts_id
382 into l_chart_of_accounts_id
383
384 --< Shared Proc FPJ Start >
385 --from financials_system_parameters fsp,
386 FROM financials_system_params_all fsp,
387 --< Shared Proc FPJ End >
388
389 gl_sets_of_books gls
390 where fsp.set_of_books_id = gls.set_of_books_id AND
391
392 --< Shared Proc FPJ Start >
393 -- NVL is required for the single-org instance case.
394 NVL(fsp.org_id, -99) = NVL(l_expense_rules_org_id, -99);
395 --< Shared Proc FPJ End >
396
397
398 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 07';
399
400 --
401 -- Use the FND_FLEX_EXT pacakge to concatenate the segments
402 --
403 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', l_chart_of_accounts_id);
404
405 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 08';
406
407 IF (l_delimiter is not null) THEN
408
409 l_concat_segs := fnd_flex_ext.concatenate_segments(l_segment_table.count,l_segment_array, l_delimiter);
410
411 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 09';
412
413 l_ccId := fnd_flex_ext.get_ccid('SQLGL','GL#',l_chart_of_accounts_id, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),l_concat_segs);
414
415 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 10';
416
417 if l_ccId is not null then
418 PO_WF_UTIL_PKG.SetItemAttrNumber (
419 itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'DEFAULT_ACCT_ID',
422 avalue => l_ccId );
423
424 result := 'COMPLETE:'||'Y';
425
426 else
427 result := 'COMPLETE:'||'N';
428 end if;
429
430 ELSE
431 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 11';
432
433 result := 'COMPLETE:'||'N';
434 END IF;
435
436 else
437 l_progress := 'PO_WF_PO_RULE_ACC.IF_RULE_EXIST_FOR_ALL_SEGMENTS: 12';
438
439 result := 'COMPLETE:'||'N';
440
441 end if;
442
443 IF (g_po_wf_debug = 'Y') THEN
444 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
445 'PO_WF_PO_RULE_ACC.if_rule_exist_for_all_segments '||
446 'result='||result);
447 END IF;
448
449 l_segment_table.DELETE;
450 l_counter := -1;
451 return;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455
456 IF (g_po_wf_debug = 'Y') THEN
457 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
458 'PO_WF_PO_RULE_ACC.if_rule_exist_for_all_segments '||
459 'EXCEPTION at '|| l_progress);
460 END IF;
461
462 wf_core.context('PO_WF_PO_RULE_ACC','IF_RULE_EXIST_FOR_ALL_SEGMENTS',
463 l_progress);
464 result := 'COMPLETE:'||'N';
465 RETURN;
466 END IF_RULE_EXIST_FOR_ALL_SEGMENTS;
467
468 PROCEDURE get_category_account_segment( itemtype in varchar2,
469 itemkey in varchar2,
470 actid in number,
471 funcmode in varchar2,
472 result out NOCOPY varchar2 )
473 IS
474 x_progress varchar2(100);
475 x_segment_name varchar2(30);
476 x_segment_value varchar2(25);
477 l_get_result varchar2(25) := NULL;
478
479 BEGIN
480
481 x_progress := 'PO_WF_PO_RULE_ACC.get_category_account_segment: 01';
482 IF (g_po_wf_debug = 'Y') THEN
483 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
484 END IF;
485
486 -- Do nothing in cancel or timeout mode
487 --
488 if (funcmode <> wf_engine.eng_run) then
489 result := wf_engine.eng_null;
490 return;
491 end if;
492
493 if (l_counter = -1) then
494 get_segment_records(itemtype, itemkey, l_get_result);
495 if (l_get_result = 'fail') then
496 result := 'COMPLETE:FAILURE';
497 return;
498 end if;
499 end if;
500
501 if (l_counter >= l_segment_table.count) THEN
502 result := 'COMPLETE:FAILURE';
503 l_segment_table.DELETE;
504 l_counter := -1;
505 return;
506
507 else
508 x_segment_name := l_segment_table(l_counter).segment_name;
509 x_segment_value := l_segment_table(l_counter).segment_value;
510
511 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
512 itemkey => itemkey,
513 aname => 'SEGMENT',
514 avalue => x_segment_name);
515
516 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
517 itemkey => itemkey,
518 aname => 'VALUE',
519 avalue => x_segment_value);
520
521 l_counter := l_counter + 1;
522 result := 'COMPLETE:SUCCESS';
523 end if;
524 RETURN;
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 wf_core.context('PO_WF_PO_RULE_ACC','get_category_account_segment',x_progress);
529 result := 'COMPLETE:FAILURE';
530 RETURN;
531
532 END get_category_account_segment;
533
534
535
536 PROCEDURE get_segment_records ( itemtype in varchar2,
537 itemkey in varchar2,
538 resultout out NOCOPY varchar2)
539 is
540
541 x_progress varchar2(100);
542 l_index NUMBER := 0;
543 x_category_id NUMBER;
544
545 type t_segment_Cursor is ref cursor return t_segment_record;
546 c_seg t_segment_Cursor;
547
548 --< Shared Proc FPJ Start >
549 l_expense_rules_org_id NUMBER;
550 --< Shared Proc FPJ End >
551
552 BEGIN
553 x_progress := 'PO_WF_PO_RULE_ACC.get_segment_records: 01';
554 x_category_id := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'CATEGORY_ID');
557
558 --< Shared Proc FPJ Start >
559 l_expense_rules_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
560 itemtype => itemtype,
561 itemkey => itemkey,
562 aname => 'EXPENSE_RULES_ORG_ID');
563
564 -- If it is NULL and the org context's org_id is not null, then copy
565 -- org_context's org_id.
566 IF l_expense_rules_org_id IS NULL THEN
567 l_expense_rules_org_id := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
568 END IF;
569
570 IF (g_po_wf_debug = 'Y') THEN
571 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
572 'PO_WF_PO_RULE_ACC.get_segment_records '||
573 'l_expense_rules_org_id='||l_expense_rules_org_id);
574 END IF;
575
576 --< Shared Proc FPJ End >
577
578
579 open c_seg for
580
581 --< Shared Proc FPJ Start >
582 --SELECT PREA.segment_name, PREA.segment_value, FFSV.segment_num
583 SELECT FFSV.segment_name, PREA.segment_value, FFSV.segment_num
584 --FROM PO_RULE_EXPENSE_ACCOUNTS_V PREA,
585 FROM PO_RULE_EXPENSE_ACCOUNTS PREA,
586 --< Shared Proc FPJ End >
587
588 fnd_id_flex_segments_vl FFSV,
589
590 --< Shared Proc FPJ Start >
591 --financials_system_parameters fsp,
592 financials_system_params_all fsp,
593 MTL_CATEGORIES_KFV MCK,
594 MTL_CATEGORY_SETS MCS,
595 MTL_DEFAULT_CATEGORY_SETS MDCS,
596 MTL_CATEGORIES MC,
597 --< Shared Proc FPJ End >
598
599 gl_sets_of_books gls
600
601 WHERE PREA.rule_type = 'ITEM CATEGORY'
602 AND PREA.RULE_VALUE_ID = x_category_id
603
604 --< Shared Proc FPJ Start >
605 --AND PREA.segment_name is NOT NULL
606 AND FFSV.segment_name is NOT NULL
607 --< Shared Proc FPJ Start >
608
609 AND PREA.segment_value is NOT NULL
610 AND PREA.segment_num = FFSV.application_column_name
611 AND FFSV.application_id = 101
612 and FFSV.id_flex_code = 'GL#'
613 and FFSV.id_flex_num = gls.chart_of_accounts_id
614 and fsp.set_of_books_id = gls.set_of_books_id
615
616 --< Shared Proc FPJ Start >
617 -- NVL is required for the single-org instance case.
618 AND NVL(FSP.org_id, -99) = NVL(l_expense_rules_org_id, -99)
619 AND NVL(PREA.org_id, -99) = NVL(l_expense_rules_org_id, -99)
620
621 AND MCK.ENABLED_FLAG = 'Y'
622 AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE,SYSDATE)
623 AND NVL(MCK.END_DATE_ACTIVE,SYSDATE)
624 AND MCS.CATEGORY_SET_id=mdcs.category_set_id
625 AND MDCS.FUNCTIONAL_AREA_ID=2
626 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
627 AND NVL(mck.DISABLE_DATE,SYSDATE + 1) > SYSDATE
628 AND (MCS.VALIDATE_FLAG='Y'
629 AND mck.CATEGORY_ID IN
630 (SELECT
631 MCSV.CATEGORY_ID
632 FROM
633 MTL_CATEGORY_SET_VALID_CATS MCSV
634 WHERE MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID)
635 OR MCS.VALIDATE_FLAG <> 'Y')
636 AND MCK.CATEGORY_ID = MC.CATEGORY_ID
637 AND PREA.RULE_VALUE_ID = MCK.CATEGORY_ID
638 --< Shared Proc FPJ End >
639
640 order by FFSV.segment_num asc;
641
642 loop
643 fetch c_seg into l_segment_table(l_index);
644 exit when c_seg%NOTFOUND;
645 l_index := l_index + 1;
646 end loop;
647 close c_seg;
648
649 if (l_index = 0) then
650 resultout := 'fail';
651 RETURN;
652 else
653 l_counter := 0;
654 resultout := 'ok';
655 end if;
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 wf_core.context('PO_WF_PO_RULE_ACC','get_segment_records',x_progress);
660 resultout := 'fail';
661 RETURN;
662
663 END get_segment_records;
664
665 --< Shared Proc FPJ Start >
666
667 ---------------------------------------------------------------------------
668 --Start of Comments
669 --Name: set_expense_rules_org_as_POU
670 --Pre-reqs:
671 -- None.
672 --Modifies:
673 -- Item Attribute: EXPENSE_RULES_ORG_ID
674 --Locks:
675 -- None.
676 --Function:
677 -- Gets the value of PURCHASING_OU_ID and puts in EXPENSE_RULES_ORG_ID
678 --Parameters:
679 --IN:
680 -- Standard workflow function parameters
681 --OUT:
682 -- Standard workflow function result parameter
683 --Testing:
684 --End of Comments
685 ---------------------------------------------------------------------------
686 PROCEDURE set_expense_rules_org_as_POU(itemtype IN VARCHAR2,
687 itemkey IN VARCHAR2,
688 actid IN NUMBER,
689 funcmode IN VARCHAR2,
690 result OUT NOCOPY VARCHAR2)
691 IS
692 x_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
693 l_purchasing_ou_id NUMBER;
694 BEGIN
695 -- Do nothing in cancel or timeout mode
696 IF (funcmode <> WF_ENGINE.eng_run) THEN
697 result := WF_ENGINE.eng_null;
698 RETURN;
699 END IF;
700
701 l_purchasing_ou_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
702 itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'PURCHASING_OU_ID');
705
706 PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype,
707 itemkey => itemkey,
708 aname => 'EXPENSE_RULES_ORG_ID',
709 avalue => l_purchasing_ou_id);
710 EXCEPTION
711 WHEN OTHERS THEN
712 WF_CORE.context('PO_WF_PO_RULE_ACC', 'set_expense_rules_org_as_POU',
713 x_progress);
714 RAISE;
715 END set_expense_rules_org_as_POU;
716
717 ---------------------------------------------------------------------------
718 --Start of Comments
719 --Name: set_expense_rules_org_as_DOU
720 --Pre-reqs:
721 -- None.
722 --Modifies:
723 -- Item Attribute: EXPENSE_RULES_ORG_ID
724 --Locks:
725 -- None.
726 --Function:
727 -- Gets the value of SHIP_TO_OU_ID and puts in EXPENSE_RULES_ORG_ID
728 --Parameters:
729 --IN:
730 -- Standard workflow function parameters
731 --OUT:
732 -- Standard workflow function result parameter
733 --Testing:
734 --End of Comments
735 ---------------------------------------------------------------------------
736 PROCEDURE set_expense_rules_org_as_DOU(itemtype IN VARCHAR2,
737 itemkey IN VARCHAR2,
738 actid IN NUMBER,
739 funcmode IN VARCHAR2,
740 result OUT NOCOPY VARCHAR2)
741 IS
742 x_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
743 l_destination_ou_id NUMBER;
744 BEGIN
745 -- Do nothing in cancel or timeout mode
746 IF (funcmode <> WF_ENGINE.eng_run) THEN
747 result := WF_ENGINE.eng_null;
748 RETURN;
749 END IF;
750
751 l_destination_ou_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
752 itemtype => itemtype,
753 itemkey => itemkey,
754 aname => 'SHIP_TO_OU_ID');
755
756 PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype,
757 itemkey => itemkey,
758 aname => 'EXPENSE_RULES_ORG_ID',
759 avalue => l_destination_ou_id);
760 EXCEPTION
761 WHEN OTHERS THEN
762 WF_CORE.context('PO_WF_PO_RULE_ACC', 'set_expense_rules_org_as_DOU',
763 x_progress);
764 RAISE;
765 END set_expense_rules_org_as_DOU;
766
767 --< Shared Proc FPJ End >
768
769 END PO_WF_PO_RULE_ACC;