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