1 PACKAGE BODY PO_WF_PO_CHARGE_ACC AS
2 /* $Header: POXWPCAB.pls 120.9.12010000.2 2008/08/04 08:36:47 rramasam 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 /*=======================================================================+
8 | FILENAME
9 | POXWPCAB.pls
10 |
11 | DESCRIPTION
12 | PL/SQL body for package: PO_WF_PO_CHARGE_ACC
13 |
14 | NOTES
15 | MODIFIED IMRAN ALI (09/02/97) - Created
16 | Imran Ali (01/23/98)
17 *=====================================================================*/
18
19
20 /*
21 * A Global variable to set the debug mode
22 */
23 debug_acc_generator_wf BOOLEAN := FALSE;
24
25
26 --
27 -- Check Destination Type
28 --
29 procedure check_destination_type ( itemtype in varchar2,
30 itemkey in varchar2,
31 actid in number,
32 funcmode in varchar2,
33 result out NOCOPY varchar2 )
34 is
35 x_progress varchar2(100);
36 x_destination_type varchar2(25);
37 begin
38
39 x_progress := 'PO_WF_PO_CHARGE_ACC.check_destination_type: 01';
40 IF (g_po_wf_debug = 'Y') THEN
41 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
42 END IF;
43
44
45 -- Do nothing in cancel or timeout mode
46 --
47 if (funcmode <> wf_engine.eng_run) then
48
49 result := wf_engine.eng_null;
50 return;
51
52 end if;
53
54 x_destination_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
55 itemkey => itemkey,
56 aname => 'DESTINATION_TYPE_CODE');
57
58 /* Start DEBUG
59 If (debug_acc_generator_wf) then
60 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.check_destination_type');
61 dbms_output.put_line ('DESTINATION_TYPE_CODE: ' || x_destination_type);
62 end if;
63 End DEBUG */
64
65 if x_destination_type = 'EXPENSE' then
66 result := 'COMPLETE:EXPENSE';
67 elsif x_destination_type = 'INVENTORY' then
68 result := 'COMPLETE:INVENTORY';
69 elsif x_destination_type = 'SHOP FLOOR' then
70 result := 'COMPLETE:SHOP_FLOOR';
71 end if;
72
73 return;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 wf_core.context('PO_WF_PO_CHARGE_ACC','check_destination_type',x_progress);
78
79 /* Start DEBUG
80 If (debug_acc_generator_wf) then
81 dbms_output.put_line (' --> EXCEPTION <-- in PO_WF_PO_CHARGE_ACC.check_destination_type');
82 end if;
83 End DEBUG */
84
85 raise;
86 end check_destination_type;
87
88 -- * ****************************************************************************** *
89 -- * ****************************************************************************** *
90
91 --
92 -- Private functions specifications for Inventory destination type.
93 --
94
95 function check_inv_item_type (itemtype varchar2, itemkey varchar2, x_dest_org_id number, x_item_id number)
96 return varchar2;
97
98 function check_sub_inv_type (itemtype varchar2, itemkey varchar2, x_dest_sub_inv varchar2, x_dest_org_id number) return varchar2;
99
100
101 --
102 -- Inventory
103 --
104 procedure inventory ( itemtype in varchar2,
105 itemkey in varchar2,
106 actid in number,
107 funcmode in varchar2,
108 result out NOCOPY varchar2 )
109 is
110 x_progress varchar2(100) := '000';
111 x_debug_stmt varchar2(100) := NULL;
112 x_dest_sub_inv varchar2(25);
113 x_subinv_type varchar2(25);
114 x_account number := NULL;
115 x_inv_item_type varchar2(25);
116 x_dest_org_id number;
117 x_item_id number;
118 --<INVCONV R12 START>
119 x_status varchar2(1);
120 x_vendor_site_id number;
121 x_msg_data varchar2(2000);
122 x_msg_count number;
123 --<INVCONV R12 END>
124 success varchar2(2) := 'Y';
125 dummy VARCHAR2(40);
126 ret BOOLEAN;
127 begin
128
129 x_debug_stmt := 'PO_WF_PO_CHARGE_ACC.inventory: 01' || x_progress;
130 IF (g_po_wf_debug = 'Y') THEN
131 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_debug_stmt);
132 END IF;
133
134
135 -- Do nothing in cancel or timeout mode
136 --
137 if (funcmode <> wf_engine.eng_run) then
138
139 result := wf_engine.eng_null;
140 return;
141
142 end if;
143
144 x_dest_org_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
145 itemkey => itemkey,
146 aname => 'DESTINATION_ORGANIZATION_ID');
147
148 x_item_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
149 itemkey => itemkey,
150 aname => 'ITEM_ID');
151
152 x_dest_sub_inv := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'DESTINATION_SUBINVENTORY');
155
156 /* Start DEBUG
157 If (debug_acc_generator_wf) then
158 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.inventory');
159 dbms_output.put_line ('DESTINATION_ORGANIZATION_ID: ' || to_char(x_dest_org_id));
160 dbms_output.put_line ('ITEM_ID: ' || to_char(x_item_id));
161 dbms_output.put_line ('DESTINATION_SUBINVENTORY: ' || x_dest_sub_inv);
162 end if;
163 End DEBUG */
164
165 x_debug_stmt := 'PO_WF_PO_CHARGE_ACC.inventory: dest sub inv :' || x_dest_sub_inv;
166 IF (g_po_wf_debug = 'Y') THEN
167 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_debug_stmt);
168 END IF;
169
170 /* Start DEBUG
171 If (debug_acc_generator_wf) then
172 dbms_output.put_line ('X_INV_ITEM_TYPE:' || x_inv_item_type);
173 end if;
174 End DEBUG */
175
176 x_debug_stmt := 'PO_WF_PO_CHARGE_ACC.inventory: inv item type :' || x_inv_item_type;
177 IF (g_po_wf_debug = 'Y') THEN
178 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_debug_stmt);
179 END IF;
180
181 x_inv_item_type := check_inv_item_type (itemtype, itemkey, x_dest_org_id, x_item_id);
182
183 --<INVCONV R12 START> call the SLA API instead of GML_ACT_ENERATE */
184 if ( PO_GML_DB_COMMON.check_process_org(x_dest_org_id) = 'Y')
185 then
186 x_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'VENDOR_SITE_ID');
189
190 if (x_dest_sub_inv is not null) then
191 x_subinv_type := check_sub_inv_type(itemtype, itemkey, x_dest_sub_inv,
192 x_dest_org_id);
193 end if;
194 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).account_type_code := GMF_transaction_accounts_PUB.G_CHARGE_INV_ACCT;
195 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).item_type := x_inv_item_type;
196 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).subinventory_type := x_subinv_type;
197 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).organization_id := x_dest_org_id;
198 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).inventory_item_id := x_item_id;
199 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).vendor_site_id := x_vendor_site_id;
200
201 GMF_transaction_accounts_PUB.get_accounts(
202 p_api_version => 1.0,
203 p_init_msg_list => dummy,
204 p_source => 'PO',
205 x_return_status => X_status,
206 x_msg_data => x_msg_data,
207 x_msg_count => x_msg_count);
208 x_account := GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).target_ccid;
209 --<INVCONV R12 END>
210 ELSE
211
212
213 If x_inv_item_type = 'EXPENSE' then
214
215 if (x_dest_sub_inv IS NOT NULL) then -- Subinventory is provided
216 begin
217
218 /* 949595 kbenjami 8/25/99. Proprogated fix from R11.
219 Bug # 943948
220 Adding the organization_id condition to the select below.
221 */
222 select expense_account into x_account
223 from mtl_secondary_inventories
224 where secondary_inventory_name = x_dest_sub_inv
225 and organization_id = x_dest_org_id;
226 exception
227 when others then
228 x_account := NULL;
229 end;
230 end if;
231
232 if (x_account is NULL) then -- Get expense account from Item Master
233 begin
234 select EXPENSE_ACCOUNT into x_account
235 from MTL_SYSTEM_ITEMS
236 where organization_id = x_dest_org_id
237 and inventory_item_id = x_item_id;
238
239 exception
240 when others then
241 x_account := NULL;
242 end;
243 end if;
244
245 if (x_account is NULL) then -- Get account from Org
246 begin
247 select expense_account into x_account
248 from mtl_parameters
249 where organization_id = x_dest_org_id;
250
251 exception
252 when no_data_found then
253 x_account := NULL;
254 when others then
255 x_progress := '001';
256 raise;
257 end;
258 end if;
259
260 else -- item type is ASSET
261
262 -- Test subinventory for Asset or Expense tracking.
263
264 if (x_dest_sub_inv is not null) then
265 x_subinv_type := check_sub_inv_type(itemtype, itemkey, x_dest_sub_inv, x_dest_org_id);
266 end if;
267
268 IF (x_dest_sub_inv is null) then
269
270 -- Get the default account from the Organization
271 begin
272 select material_account into x_account
273 from mtl_parameters
274 where organization_id = x_dest_org_id;
275 exception
276 when no_data_found then
277 x_account := NULL;
278 when others then
279 x_progress := '002';
280 raise;
281 end;
282
283 ELSIF x_subinv_type = 'EXPENSE' then
284
285 begin
286 select expense_account into x_account
287 from mtl_secondary_inventories
288 where secondary_inventory_name = x_dest_sub_inv
289 and organization_id = x_dest_org_id;
290 exception
291 when others then
292 x_account := NULL;
293 end;
294
295 if (x_account is NULL) then
296
297 -- Get the default account from the Organization
298 begin
299 select expense_account into x_account
300 from mtl_parameters
301 where organization_id = x_dest_org_id;
302 exception
303 when no_data_found then
304 x_account := NULL;
305 when others then
306 x_progress := '003';
307 raise;
308 end;
309 end if;
310
311 ELSE -- destination sub inv type is ASSET
312
313 begin
314 select material_account into x_account
315 from mtl_secondary_inventories
316 where secondary_inventory_name = x_dest_sub_inv
317 and organization_id = x_dest_org_id;
318 exception
319 when others then
320 x_account := NULL;
321 end;
322
323 if (x_account IS NULL) then
324 begin
325 select material_account into x_account
326 from mtl_parameters
327 where organization_id = x_dest_org_id;
328 exception
329 when no_data_found then
330 x_account := NULL;
331 when others then
332 x_progress := '004';
333 raise;
334 end;
335 end if;
336 END IF;
337 end if;
338 END IF;
339
340 if (x_account IS NULL) then
341
342 /* Start DEBUG
343 If (debug_acc_generator_wf) then
344 dbms_output.put_line ('RESULT = COMPLETE:FAILURE');
345 end if;
346 End DEBUG */
347
348 result := 'COMPLETE:FAILURE';
349 return;
350 end if;
351
352 po_wf_util_pkg.SetItemAttrNumber ( itemtype=>itemtype,
353 itemkey=>itemkey,
354 aname=>'TEMP_ACCOUNT_ID',
355 avalue=>x_account );
356 /* Start DEBUG
357 If (debug_acc_generator_wf) then
358 dbms_output.put_line ('RESULT = COMPLETE:SUCCESS, x_account = ' || to_char(x_account));
359 end if;
360 End DEBUG */
361
362 result := 'COMPLETE:SUCCESS';
363 RETURN;
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 wf_core.context('PO_WF_PO_CHARGE_ACC','inventory',x_progress);
368
369 /* Start DEBUG
370 If (debug_acc_generator_wf) then
371 dbms_output.put_line (' --> EXCEPTION <-- in PO_WF_PO_CHARGE_ACC.inventory');
372 end if;
373 End DEBUG */
374
375 raise;
376
377 end inventory;
378
379
380 --
381 -- Private functions body for Inventory destination type.
382 --
383
384 function check_inv_item_type ( itemtype varchar2,
385 itemkey varchar2,
386 x_dest_org_id number,
387 x_item_id number)
388 return varchar2
389 is
390 x_asset_item_flag varchar2(4);
391 x_progress varchar2(200);
392 begin
393 x_progress := 'PO_WF_PO_CHARGE_ACC.check_inv_item_type: 01';
394
395 select inventory_asset_flag into x_asset_item_flag
396 from mtl_system_items
397 where organization_id = x_dest_org_id
398 and inventory_item_id = x_item_id;
399
400 /* Start DEBUG
401 If (debug_acc_generator_wf) then
402 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.check_inv_item_type');
403 dbms_output.put_line ('X_ASSET_ITEM_FLAG: ' || x_asset_item_flag);
404 end if;
405 End DEBUG */
406
407 if x_asset_item_flag = 'Y' then
408 return 'ASSET';
409 else
410 return 'EXPENSE';
411 end if;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 -- Bug 3433867: Enhanced exception handling for this function
416 IF (g_po_wf_debug = 'Y') THEN
417 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
418 'PO_WF_PO_CHARGE_ACC.check_inv_item_type EXCEPTION at '||x_progress
419 ||': '||SQLERRM);
420 END IF;
421 wf_core.context('PO_WF_PO_CHARGE_ACC','check_inv_item_type',x_progress);
422 raise;
423 end;
424
425 --
426
427 function check_sub_inv_type ( itemtype varchar2,
428 itemkey varchar2,
429 x_dest_sub_inv varchar2,
430 x_dest_org_id number )
431 return varchar2
432 is
433 x_asset_inventory number;
434 x_progress varchar2(100);
435 begin
436
437 x_progress := 'PO_WF_PO_CHARGE_ACC.check_sub_inv_type: 01';
438
439 select asset_inventory into x_asset_inventory
440 from mtl_secondary_inventories
441 where secondary_inventory_name = x_dest_sub_inv
442 and organization_id = x_dest_org_id;
443
444 if (x_asset_inventory = 1) then
445 return 'ASSET';
446 elsif (x_asset_inventory = 2) then
447 return 'EXPENSE';
448 else
449 return '';
450 end if;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 wf_core.context('PO_WF_PO_CHARGE_ACC','check_sub_inv_type',x_progress);
455 raise;
456 end;
457
458 -- * ****************************************************************************** *
459 -- * ****************************************************************************** *
460
461 --
462 -- Expense
463 --
464 procedure expense ( itemtype in varchar2,
465 itemkey in varchar2,
466 actid in number,
467 funcmode in varchar2,
468 result out NOCOPY varchar2 )
469 is
470 x_progress varchar2(100);
471 success varchar2(2);
472 x_dest_org_id number;
473 x_item_id number;
474 x_expense_acc number;
475 --<INVCONV R12 START>
476 x_status varchar2(1);
477 x_vendor_site_id number;
478 x_msg_data varchar2(2000);
479 x_msg_count number;
480 --<INVCONV R12 END>
481
482 dummy VARCHAR2(40);
483 ret BOOLEAN;
484 begin
485
486 x_progress := 'PO_WF_PO_CHARGE_ACC.expense: 01';
487 IF (g_po_wf_debug = 'Y') THEN
488 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
489 END IF;
490
491
492 -- Do nothing in cancel or timeout mode
493 --
494 if (funcmode <> wf_engine.eng_run) then
495
496 result := wf_engine.eng_null;
497 return;
498
499 end if;
500
501 x_dest_org_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
502 itemkey => itemkey,
503 aname => 'DESTINATION_ORGANIZATION_ID');
504
505 x_item_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'ITEM_ID');
508
509 /* Start DEBUG
510 If (debug_acc_generator_wf) then
511 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.expense');
512 dbms_output.put_line ('DESTINATION_ORGANIZATION_ID: ' || to_char(x_dest_org_id));
513 dbms_output.put_line ('ITEM_ID: ' || to_char(x_item_id));
514 end if;
515 End DEBUG */
516
517 --<INVCONV R12 START>
518 if ( PO_GML_DB_COMMON.check_process_org(x_dest_org_id) = 'Y')
519 then
520 x_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
521 itemkey => itemkey,
522 aname => 'VENDOR_SITE_ID');
523 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).account_type_code := GMF_transaction_accounts_PUB.G_CHARGE_EXP_ACCT;
524 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).item_type := '';
525 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).subinventory_type := '';
526 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).organization_id := x_dest_org_id;
527 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).inventory_item_id := x_item_id;
528 GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).vendor_site_id := x_vendor_site_id;
529
530 GMF_transaction_accounts_PUB.get_accounts(
531 p_api_version => 1.0,
532 p_init_msg_list => dummy,
533 p_source => 'PO',
534 x_return_status => X_status,
535 x_msg_data => x_msg_data,
536 x_msg_count => x_msg_count);
537 x_expense_acc := GMF_transaction_accounts_PUB.g_gmf_accts_tab_PUR(1).target_ccid;
538
539 /* GML_ACCT_GENERATE.GENERATE_OPM_ACCT('EXPENSE','', '', x_dest_org_id, x_item_id, x_vendor_site_id, x_expense_acc); */
540 If (x_expense_acc is null) then
541 success := 'N';
542 end if;
543 ELSE
544 begin
545
546 select EXPENSE_ACCOUNT into x_expense_acc
547 from MTL_SYSTEM_ITEMS
548 where organization_id = x_dest_org_id
549 and inventory_item_id = x_item_id;
550
551 /*Bug 1319679
552 If the default expense account for the item is null
553 we should be returning 'N' for Success ie the result
554 of the workflow process should be COMPLETE:FAILURE
555 */
556
557 if (x_expense_acc is null) then
558 success := 'N';
559 end if;
560
561 exception
562 WHEN NO_DATA_FOUND THEN
563 success := 'N';
564 end;
565 END IF;
566 --<INVCONV R12 END>
567
568 if (success = 'N') then
569 result := 'COMPLETE:FAILURE';
570
571 IF (g_po_wf_debug = 'Y') THEN
572 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
573 'PO_WF_PO_CHARGE_ACC.expense result='||result);
574 END IF;
575
576 return;
577 end if;
578
579 po_wf_util_pkg.SetItemAttrNumber ( itemtype=>itemtype,
580 itemkey=>itemkey,
581 aname=>'TEMP_ACCOUNT_ID',
582 avalue=>x_expense_acc );
583
584 result := 'COMPLETE:SUCCESS';
585 -- Bug 3703469: Clear any previous messages in the stack if
586 -- account is generated successfully
587 fnd_message.clear;
588 RETURN;
589
590 IF (g_po_wf_debug = 'Y') THEN
591 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
592 'PO_WF_PO_CHARGE_ACC.expense x_expense_acc='||x_expense_acc);
593 END IF;
594
595 EXCEPTION
596 WHEN OTHERS THEN
597 wf_core.context('PO_WF_PO_CHARGE_ACC','expense',x_progress);
598
599 /* Start DEBUG
600 If (debug_acc_generator_wf) then
601 dbms_output.put_line (' --> EXCEPTION <-- in PO_WF_PO_CHARGE_ACC.expense');
602 end if;
603 End DEBUG */
604
605 raise;
606
607 end expense;
608
609 -- * ****************************************************************************** *
610 -- * ****************************************************************************** *
611 --
612 -- Check type of WIP
613 --
614 procedure check_type_of_wip ( itemtype in varchar2,
615 itemkey in varchar2,
616 actid in number,
617 funcmode in varchar2,
618 result out NOCOPY varchar2 )
619 is
620 wip_entity_type varchar2(80);
621 x_progress varchar2(100);
622 begin
623
624 x_progress := 'PO_WF_PO_CHARGE_ACC.check_type_of_wip: 01';
625 IF (g_po_wf_debug = 'Y') THEN
626 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
627 END IF;
628
629
630 -- Do nothing in cancel or timeout mode
631 --
632 if (funcmode <> wf_engine.eng_run) then
633
634 result := wf_engine.eng_null;
635 return;
636
637 end if;
638
639 wip_entity_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
640 itemkey => itemkey,
641 aname => 'WIP_ENTITY_TYPE');
642 /* Start DEBUG
643 If (debug_acc_generator_wf) then
644 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.check_type_of_wip');
645 dbms_output.put_line ('WIP_ENTITY_TYPE: ' || wip_entity_type);
646 end if;
647 End DEBUG */
648
649 if (wip_entity_type = '2') then
650 result := 'COMPLETE:SCHEDULE';
651 else
652 result := 'COMPLETE:JOB_WIP';
653 end if;
654
655 return;
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 wf_core.context('PO_WF_PO_CHARGE_ACC','check_type_of_wip',x_progress);
660 raise;
661
662 end check_type_of_wip;
663
664 -- * ****************************************************************************** *
665
666 --
667 -- JOB_WIP
668 --
669 procedure job_wip ( itemtype in varchar2,
670 itemkey in varchar2,
671 actid in number,
672 funcmode in varchar2,
673 result out NOCOPY varchar2 )
674 is
675 x_wip_entity_type varchar2(80);
676 x_wip_job_account NUMBER := NULL;
677 x_wip_entity_id NUMBER;
678 x_bom_cost_element_id NUMBER;
679 x_destination_organization_id NUMBER;
680 x_progress varchar2(200);
681 --Bug# 1902716 togeorge 07/25/2001
682 --EAM:
683 x_return_status varchar2(1);
684 x_msg_count number;
685 x_msg_data varchar2(8000);
686
687 -- <FPJ Costing CST_EAM API START>
688 l_category_id NUMBER;
689 -- <FPJ Costing CST_EAM API END>
690
691 begin
692
693 x_progress := 'PO_WF_PO_CHARGE_ACC.job_wip: 01';
694 IF (g_po_wf_debug = 'Y') THEN
695 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
696 END IF;
697
698 -- Do nothing in cancel or timeout mode
699 --
700 if (funcmode <> wf_engine.eng_run) then
701
702 result := wf_engine.eng_null;
703 return;
704
705 end if;
706
707 x_destination_organization_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
708 itemkey => itemkey,
709 aname => 'DESTINATION_ORGANIZATION_ID');
710
711 x_bom_cost_element_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
712 itemkey => itemkey,
713 aname => 'BOM_COST_ELEMENT_ID');
714
715 x_wip_entity_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'WIP_ENTITY_ID');
718
719 x_wip_entity_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
720 itemkey => itemkey,
721 aname => 'WIP_ENTITY_TYPE');
722
723 -- <FPJ Costing CST_EAM API START>
724 l_category_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
725 itemkey => itemkey,
726 aname => 'CATEGORY_ID');
727 -- <FPJ Costing CST_EAM API END>
728
729 /* Start DEBUG
730 If (debug_acc_generator_wf) then
731 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.job_wip');
732 dbms_output.put_line ('DESTINATION_ORGANIZATION_ID: ' || to_char(x_destination_organization_id));
733 dbms_output.put_line ('BOM_COST_ELEMENT_ID: ' || to_char(x_bom_cost_element_id));
734 dbms_output.put_line ('WIP_ENTITY_ID: ' || to_char(x_wip_entity_id));
735 dbms_output.put_line ('WIP_ENTITY_TYPE: ' || x_wip_entity_type);
736 end if;
737 End DEBUG */
738
739 x_progress := 'org_id:' || to_char(x_destination_organization_id) || 'bom_cost_element_id:' ||
740 to_char(x_bom_cost_element_id) || 'wip_entity_id' || to_char(x_wip_entity_id) ||
741 'wip_entity_type' || x_wip_entity_type ||
742 -- <FPJ Costing CST_EAM API START>
743 'l_category_id' || to_char(l_category_id);
744 -- <FPJ Costing CST_EAM API END>
745
746 IF (g_po_wf_debug = 'Y') THEN
747 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
748 END IF;
749
750 --Bug# 1902716 togeorge 07/25/2001
751 --EAM: if entity type in(6,7) get the eam account. This call is
752 -- made from job_wip to avoid the impact on workflow.
753 --On failure the API returns -1. So it is nullified for the check
754 --at the end of this procedure.
755
756 IF (x_wip_entity_type in (6,7)) THEN
757 IF (x_wip_job_account IS NULL) THEN
758
759 -- bug 556021 : if the cost element is OSP then pick the OSP account.
760 IF (x_bom_cost_element_id LIKE '4') THEN
761
762 BEGIN
763 SELECT outside_processing_account
764 INTO x_wip_job_account
765 FROM WIP_DISCRETE_JOBS
766 WHERE ORGANIZATION_ID = x_destination_organization_id
767 AND WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
768 AND OUTSIDE_PROCESSING_ACCOUNT <> -1;
769 EXCEPTION
770 WHEN NO_DATA_FOUND THEN
771 NULL;
772 END;
773
774 ELSE
775 -- <FPJ Costing CST_EAM API START>
776 IF (CST_VersionCtrl_GRP.Get_Current_Release_Level <
777 CST_Release_GRP.Get_J_Release_Level) THEN
778 -- Lower the J Release
779 CST_eamCost_PUB.get_Direct_Item_Charge_Acct(
780 p_api_version => 1.0,
781 p_init_msg_list => null,
782 p_commit => null,
783 p_validation_level => null,
784 p_wip_entity_id => x_wip_entity_id,
785 x_material_acct => x_wip_job_account,
786 x_return_status => x_return_status,
787 x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data);
789 ELSE
790 -- J Release or higher
791 CST_Utility_PUB.get_Direct_Item_Charge_Acct(
792 p_api_version => 1.0,
793 p_init_msg_list => null,
794 p_commit => null,
795 p_validation_level => null,
796 p_wip_entity_id => x_wip_entity_id,
797 x_material_acct => x_wip_job_account,
798 x_return_status => x_return_status,
799 x_msg_count => x_msg_count,
800 x_msg_data => x_msg_data,
801 p_category_id => l_category_id);
802 END IF; /* IF (CST_VersionCtrl_GRP.Get_Current_Release_Level < */
803 -- <FPJ Costing CST_EAM API END>
804 END IF; -- bug 556021
805
806 IF (x_wip_job_account = -1) THEN
807 x_wip_job_account := null;
808 END IF;
809 END IF;
810 ELSE
811
812 /* Bug - 2204214 - WIP has added more WIP ENTITY types for JOBS and hence
813 checking for just 1 for a JOB is not valid and does not build the account.
814 Changed all the where clauses in the below sqls to check for 1, 3, 4 , 5 */
815
816 IF (x_wip_job_account IS NULL) THEN
817 IF (x_bom_cost_element_id LIKE '5') THEN
818 BEGIN
819 SELECT OVERHEAD_ACCOUNT INTO x_wip_job_account
820 FROM WIP_DISCRETE_JOBS
821 WHERE ORGANIZATION_ID = x_destination_organization_id
822 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
823 AND OVERHEAD_ACCOUNT <> -1
824 AND NVL(x_wip_entity_type,'2') in ('1', '3', '4', '5'));
825 EXCEPTION
826 WHEN NO_DATA_FOUND THEN
827 NULL;
828 END;
829 END IF;
830 END IF;
831 IF (x_wip_job_account IS NULL) THEN
832 IF (x_bom_cost_element_id LIKE '4') THEN
833 BEGIN
834 SELECT outside_processing_account INTO x_wip_job_account
835 FROM WIP_DISCRETE_JOBS
836 WHERE ORGANIZATION_ID = x_destination_organization_id
837 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
838 AND OUTSIDE_PROCESSING_ACCOUNT <> -1
839 AND NVL(x_wip_entity_type,'2') in ('1', '3', '4', '5'));
840 EXCEPTION
841 WHEN NO_DATA_FOUND THEN
842 NULL;
843 END;
844 END IF;
845 END IF;
846 IF (x_wip_job_account IS NULL) THEN
847 IF (x_bom_cost_element_id LIKE '3') THEN
848 BEGIN
849 SELECT RESOURCE_ACCOUNT INTO x_wip_job_account
850 FROM WIP_DISCRETE_JOBS
851 WHERE ORGANIZATION_ID = x_destination_organization_id
852 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
853 AND RESOURCE_ACCOUNT <> -1
854 AND NVL(x_wip_entity_type,'2') in ('1', '3', '4', '5'));
855 EXCEPTION
856 WHEN NO_DATA_FOUND THEN
857 NULL;
858 END;
859 END IF;
860 END IF;
861 IF (x_wip_job_account IS NULL) THEN
862 IF (x_bom_cost_element_id LIKE '2') THEN
863 BEGIN
864 SELECT MATERIAL_OVERHEAD_ACCOUNT INTO x_wip_job_account
865 FROM WIP_DISCRETE_JOBS
866 WHERE ORGANIZATION_ID = x_destination_organization_id
867 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
868 AND MATERIAL_OVERHEAD_ACCOUNT <> -1
869 AND NVL(x_wip_entity_type,'2') in ('1', '3', '4', '5'));
870 EXCEPTION
871 WHEN NO_DATA_FOUND THEN
872 NULL;
873 END;
874 END IF;
875 END IF;
876 IF (x_wip_job_account IS NULL) THEN
877 IF (x_bom_cost_element_id LIKE '1') THEN
878 BEGIN
879 SELECT MATERIAL_ACCOUNT INTO x_wip_job_account
880 FROM WIP_DISCRETE_JOBS
881 WHERE ORGANIZATION_ID = x_destination_organization_id
882 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
883 AND MATERIAL_ACCOUNT <> -1
884 AND NVL(x_wip_entity_type,'2') in ('1', '3', '4', '5'));
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 NULL;
888 END;
889 END IF;
890 END IF;
891 IF (x_wip_job_account IS NULL) THEN
892 BEGIN
893 SELECT FLEX_VALUE INTO x_wip_job_account
894 FROM FND_FLEX_VALUES_VL
895 WHERE FLEX_VALUE = x_destination_organization_id
896 AND (FLEX_VALUE_SET_ID = 102256);
897 EXCEPTION
898 WHEN NO_DATA_FOUND THEN
899 NULL;
900 END;
901 END IF;
902 END IF;
903
904 x_progress := 'WIP_JOB_ACCOUNT is :' || to_char(x_wip_job_account);
905 IF (g_po_wf_debug = 'Y') THEN
906 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
907 END IF;
908
909 if (x_wip_job_account IS NOT NULL) then
910
911 po_wf_util_pkg.SetItemAttrText ( itemtype=>itemtype,
912 itemkey=>itemkey,
913 aname=>'TEMP_ACCOUNT_ID',
914 avalue=>x_wip_job_account );
915 result := 'COMPLETE:SUCCESS';
916 else
917 result := 'COMPLETE:FAILURE';
918 end if;
919
920 RETURN;
921
922 EXCEPTION
923 WHEN OTHERS THEN
924 wf_core.context('PO_WF_PO_CHARGE_ACC','job_wip',x_progress);
925 IF (g_po_wf_debug = 'Y') THEN
926 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, 'EXCEPTION IN PO_WF_PO_CHARGE_ACC.JOB_WIP');
927 END IF;
928 raise;
929
930 end job_wip;
931
932 -- * ****************************************************************************** *
933
934 --
935 -- Schedule
936 --
937 procedure schedule ( itemtype in varchar2,
938 itemkey in varchar2,
939 actid in number,
940 funcmode in varchar2,
941 result out NOCOPY varchar2 )
942 is
943 x_wip_entity_type varchar2(80);
944 x_wip_schedule_account NUMBER := NULL;
945 x_wip_entity_id NUMBER;
946 x_bom_cost_element_id NUMBER;
947 x_destination_organization_id NUMBER;
948 x_wip_repetitive_schedule_id NUMBER;
949 x_progress varchar2(200);
950 begin
951
952 x_progress := 'PO_WF_PO_CHARGE_ACC.schedule: 01';
953 IF (g_po_wf_debug = 'Y') THEN
954 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
955 END IF;
956
957
958 -- Do nothing in cancel or timeout mode
959 --
960 if (funcmode <> wf_engine.eng_run) then
961
962 result := wf_engine.eng_null;
963 return;
964
965 end if;
966
967 x_destination_organization_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
968 itemkey => itemkey,
969 aname => 'DESTINATION_ORGANIZATION_ID');
970
971 x_bom_cost_element_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
972 itemkey => itemkey,
973 aname => 'BOM_COST_ELEMENT_ID');
974
975 x_wip_entity_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
976 itemkey => itemkey,
977 aname => 'WIP_ENTITY_ID');
978
979 x_wip_entity_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
980 itemkey => itemkey,
981 aname => 'WIP_ENTITY_TYPE');
982
983 x_wip_repetitive_schedule_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'WIP_REPETITIVE_SCHEDULE_ID');
986
987 /* Start DEBUG
988 If (debug_acc_generator_wf) then
989 dbms_output.put_line ('Procedure PO_WF_PO_CHARGE_ACC.Schedule');
990 dbms_output.put_line ('DESTINATION_ORGANIZATION_ID: ' || to_char(x_destination_organization_id));
991 dbms_output.put_line ('BOM_COST_ELEMENT_ID: ' || to_char(x_bom_cost_element_id));
992 dbms_output.put_line ('WIP_ENTITY_ID: ' || to_char(x_wip_entity_id));
993 dbms_output.put_line ('WIP_ENTITY_TYPE: ' || x_wip_entity_type);
994 dbms_output.put_line ('WIP_REPETITIVE_SCHEDULE_ID: ' || to_char(x_wip_repetitive_schedule_id));
995 end if;
996 End DEBUG */
997
998 x_progress := 'org_id:' || to_char(x_destination_organization_id) || 'bom_cost_element_id:' ||
999 to_char(x_bom_cost_element_id) || 'wip_entity_id' || to_char(x_wip_entity_id) ||
1000 'wip_entity_type' || x_wip_entity_type || 'wip_repetitive_schedule_id' ||
1001 to_char(x_wip_repetitive_schedule_id);
1002
1003 IF (g_po_wf_debug = 'Y') THEN
1004 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1005 END IF;
1006
1007
1008 IF (x_wip_schedule_account IS NULL) THEN
1009 IF (x_bom_cost_element_id LIKE '5') THEN
1010 BEGIN
1011 SELECT OVERHEAD_ACCOUNT INTO x_wip_schedule_account
1012 FROM WIP_REPETITIVE_SCHEDULES
1013 WHERE ORGANIZATION_ID = x_destination_organization_id
1014 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
1015 AND '2' = NVL(x_wip_entity_type,'1')
1016 AND REPETITIVE_SCHEDULE_ID = NVL(x_wip_repetitive_schedule_id,-99)
1017 AND OVERHEAD_ACCOUNT <> -1);
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 NULL;
1021 END;
1022 END IF;
1023 END IF;
1024
1025 IF (x_wip_schedule_account IS NULL) THEN
1026 IF (x_bom_cost_element_id LIKE '4') THEN
1027 BEGIN
1028 SELECT OUTSIDE_PROCESSING_ACCOUNT INTO x_wip_schedule_account
1029 FROM WIP_REPETITIVE_SCHEDULES
1030 WHERE ORGANIZATION_ID = TO_NUMBER(x_destination_organization_id)
1031 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
1032 AND '2' = NVL(x_wip_entity_type,'1')
1033 AND REPETITIVE_SCHEDULE_ID = NVL(x_wip_repetitive_schedule_id,-99)
1034 AND OUTSIDE_PROCESSING_ACCOUNT <> -1);
1035 EXCEPTION
1036 WHEN NO_DATA_FOUND THEN
1037 NULL;
1038 END;
1039 END IF;
1040 END IF;
1041 IF (x_wip_schedule_account IS NULL) THEN
1042 IF (x_bom_cost_element_id LIKE '3') THEN
1043 BEGIN
1044 SELECT RESOURCE_ACCOUNT INTO x_wip_schedule_account
1045 FROM WIP_REPETITIVE_SCHEDULES
1046 WHERE ORGANIZATION_ID = x_destination_organization_id
1047 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
1048 AND '2' = NVL(x_wip_entity_type,'1')
1049 AND REPETITIVE_SCHEDULE_ID = NVL(x_wip_repetitive_schedule_id,-99)
1050 AND RESOURCE_ACCOUNT <> -1) ;
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND THEN
1053 NULL;
1054 END;
1055 END IF;
1056 END IF;
1057
1058 IF (x_wip_schedule_account IS NULL) THEN
1059 IF (x_bom_cost_element_id LIKE '2') THEN
1060 BEGIN
1061 SELECT MATERIAL_OVERHEAD_ACCOUNT INTO x_wip_schedule_account
1062 FROM WIP_REPETITIVE_SCHEDULES
1063 WHERE ORGANIZATION_ID = TO_NUMBER(x_destination_organization_id)
1064 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
1065 AND '2' = NVL(x_wip_entity_type,'1')
1066 AND REPETITIVE_SCHEDULE_ID = NVL(x_wip_repetitive_schedule_id,-99)
1067 AND MATERIAL_OVERHEAD_ACCOUNT <> -1);
1068 EXCEPTION
1069 WHEN NO_DATA_FOUND THEN
1070 NULL;
1071 END;
1072 END IF;
1073 END IF;
1074
1075 IF (x_wip_schedule_account IS NULL) THEN
1076 IF (x_bom_cost_element_id LIKE '1') THEN
1077 BEGIN
1078 SELECT MATERIAL_ACCOUNT INTO x_wip_schedule_account
1079 FROM WIP_REPETITIVE_SCHEDULES
1080 WHERE ORGANIZATION_ID = x_destination_organization_id
1081 AND (WIP_ENTITY_ID = NVL(x_wip_entity_id,-99)
1082 AND '2' = NVL(x_wip_entity_type,'2')
1083 AND REPETITIVE_SCHEDULE_ID = NVL(x_wip_repetitive_schedule_id,-99)
1084 AND MATERIAL_ACCOUNT <> -1);
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 NULL;
1088 END;
1089 END IF;
1090 END IF;
1091 IF (X_WIP_SCHEDULE_ACCOUNT IS NULL) THEN
1092 BEGIN
1093 SELECT FLEX_VALUE INTO x_wip_schedule_account
1094 FROM FND_FLEX_VALUES_VL
1095 WHERE FLEX_VALUE = x_destination_organization_id
1096 AND (FLEX_VALUE_SET_ID = 102256);
1097 EXCEPTION
1098 WHEN NO_DATA_FOUND THEN
1099 NULL;
1100 END;
1101 END IF;
1102
1103 x_progress := 'WIP_JOB_ACCOUNT is :' || to_char(x_wip_schedule_account);
1104 IF (g_po_wf_debug = 'Y') THEN
1105 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1106 END IF;
1107
1108 if (x_wip_schedule_account IS NOT NULL) then
1109
1110 po_wf_util_pkg.SetItemAttrText ( itemtype=>itemtype,
1111 itemkey=>itemkey,
1112 aname=>'TEMP_ACCOUNT_ID',
1113 avalue=>x_wip_schedule_account );
1114 result := 'COMPLETE:SUCCESS';
1115 else
1116 result := 'COMPLETE:FAILURE';
1117 end if;
1118
1119 RETURN;
1120
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 wf_core.context('PO_WF_PO_CHARGE_ACC','schedule',x_progress);
1124 IF (g_po_wf_debug = 'Y') THEN
1125 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, 'EXCEPTION IN PO_WF_PO_CHARGE_ACC.SCHEDULE');
1126 END IF;
1127 raise;
1128
1129 end schedule;
1130
1131 --
1132
1133 --
1134 -- is_encumbrance_on
1135 --
1136
1137 procedure is_encumbrance_on ( itemtype in varchar2,
1138 itemkey in varchar2,
1139 actid in number,
1140 funcmode in varchar2,
1141 result out NOCOPY varchar2 )
1142 is
1143 po_encumbrance_flag varchar2(4);
1144 x_destination_type varchar2(25);
1145 l_is_financing_flag varchar2(4); --<Complex Work R12>
1146 l_is_advance_flag varchar2(4); --<Complex Work R12>
1147 x_progress varchar2(200);
1148
1149 l_purch_encumbrance_flag VARCHAR2(10);
1150 l_req_encumbrance_flag VARCHAR2(10);
1151 begin
1152
1153 x_progress := 'PO_WF_PO_CHARGE_ACC.is_encumbrance_on: 01';
1154 IF (g_po_wf_debug = 'Y') THEN
1155 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1156 END IF;
1157
1158
1159 -- Do nothing in cancel or timeout mode
1160 --
1161 if (funcmode <> wf_engine.eng_run) then
1162
1163 result := wf_engine.eng_null;
1164 return;
1165
1166 end if;
1167
1168 po_encumbrance_flag := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
1169 itemkey => itemkey,
1170 aname => 'PO_ENCUMBRANCE_FLAG');
1171
1172 x_destination_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
1173 itemkey => itemkey,
1174 aname => 'DESTINATION_TYPE_CODE');
1175
1176 --<Complex Work R12>: added check for financing_flag and advance_flag in the
1177 --setting of result below. if either flag is set to 'Y', then this is a PREPAYMENT
1178 --type distribution and we do not encumber it.
1179 l_is_financing_flag := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
1180 itemkey => itemkey,
1181 aname => 'IS_FINANCING_DISTRIBUTION');
1182
1183 l_is_advance_flag := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
1184 itemkey => itemkey,
1185 aname => 'IS_ADVANCE_DISTRIBUTION');
1186
1187 -- Bug 5058123 Start: This code is shared between Req and PO Account Generators.
1188 -- The new R12 Complex Work attributes are not defined in Req AG WF. So we would
1189 -- get NULL for these attribute values for requisition case. Set them to 'N'.
1190 -- We do not want to compare the item_type to 'POWFRQAG' (i.e. seeded Req AG WF)
1191 -- because the item_types are not hard-coded and customers may change them.
1192 IF (g_po_wf_debug = 'Y') THEN
1193 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'po_encumbrance_flag='||po_encumbrance_flag||', x_destination_type='||x_destination_type||', l_is_financing_flag='||l_is_financing_flag||', l_is_advance_flag='||l_is_advance_flag);
1194
1195 -- DEBUG QUERIES: Start
1196 -- As part of bug 5058123, there was another issue related to MOAC setup
1197 -- of MO: Security Profile. The value of PO_ENCUMBRANCE_FLAG was NULL.
1198 -- Bug 4932685 had similar symptoms. In both cases, the bug stopped
1199 -- getting reproduced after couple of days, and we could not get to the
1200 -- root cause. The following queries are put in to record some debug values
1201 -- that will help get to the root cause if the issue appears again.
1202 -- I suspect that the values loaded in PO_STARTUP_VALUES are NULL in
1203 -- PO_CORE_S.get_po_parameters() [POXCOC1B.pls]. The following queries should
1204 -- get us moe information.
1205 -- Remove this DEBUG QUERIES block, once the MOAC issue is completely fixed.
1206 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'org_id from PO_MOAC='||PO_MOAC_UTILS_PVT.get_current_org_id||', org_id from PO_GA_PVT='||PO_GA_PVT.get_current_org );
1207 -- Query from: PO_CORE_S.get_po_parameters() [POXCOC1B.pls].
1208 SELECT nvl(fsp.purch_encumbrance_flag,'N'),
1209 nvl(fsp.req_encumbrance_flag,'N')
1210 INTO l_purch_encumbrance_flag,
1211 l_req_encumbrance_flag
1212 FROM financials_system_parameters fsp,
1213 gl_sets_of_books sob,
1214 po_system_parameters psp,
1215 rcv_parameters rcv
1216 WHERE fsp.set_of_books_id = sob.set_of_books_id
1217 AND rcv.organization_id (+) = fsp.inventory_organization_id;
1218
1219 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_CORE_S: l_purch_encumbrance_flag='||l_purch_encumbrance_flag||', l_req_encumbrance_flag='||l_req_encumbrance_flag);
1220
1221 SELECT purch_encumbrance_flag,
1222 req_encumbrance_flag
1223 INTO l_purch_encumbrance_flag,
1224 l_req_encumbrance_flag
1225 FROM FINANCIALS_SYSTEM_PARAMETERS; -- view
1226
1227 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'View: l_purch_encumbrance_flag='||l_purch_encumbrance_flag||', l_req_encumbrance_flag='||l_req_encumbrance_flag);
1228
1229 SELECT purch_encumbrance_flag,
1230 req_encumbrance_flag
1231 INTO l_purch_encumbrance_flag,
1232 l_req_encumbrance_flag
1233 FROM FINANCIALS_SYSTEM_PARAMS_ALL -- table
1234 WHERE org_id = PO_MOAC_UTILS_PVT.get_current_org_id;
1235
1236 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'Table: MOAC: l_purch_encumbrance_flag='||l_purch_encumbrance_flag||', l_req_encumbrance_flag='||l_req_encumbrance_flag);
1237
1238 SELECT purch_encumbrance_flag,
1239 req_encumbrance_flag
1240 INTO l_purch_encumbrance_flag,
1241 l_req_encumbrance_flag
1242 FROM FINANCIALS_SYSTEM_PARAMS_ALL -- table
1243 WHERE org_id = PO_GA_PVT.get_current_org;
1244
1245 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'Table: PO_GA_PVT: l_purch_encumbrance_flag='||l_purch_encumbrance_flag||', l_req_encumbrance_flag='||l_req_encumbrance_flag);
1246 -- DEBUG QUERIES: End
1247 END IF;
1248
1249 IF (l_is_financing_flag IS NULL) THEN
1250 l_is_financing_flag := 'N';
1251 END IF;
1252
1253 IF (l_is_advance_flag IS NULL) THEN
1254 l_is_advance_flag := 'N';
1255 END IF;
1256
1257 IF (g_po_wf_debug = 'Y') THEN
1258 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'After: l_is_financing_flag='||l_is_financing_flag||', l_is_advance_flag='||l_is_advance_flag);
1259 END IF;
1260 -- Bug 5058123: End
1261
1262 if (po_encumbrance_flag = 'Y' and x_destination_type <> 'SHOP FLOOR'
1263 AND l_is_financing_flag <> 'Y' AND l_is_advance_flag <> 'Y') then
1264 result := 'COMPLETE:TRUE';
1265 else
1266 result := 'COMPLETE:FALSE';
1267 end if;
1268
1269 x_progress := 'PO_WF_PO_CHARGE_ACC.is_encumbrance_on: result = ' || result;
1270 IF (g_po_wf_debug = 'Y') THEN
1271 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1272 END IF;
1273
1274 return;
1275
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 wf_core.context('PO_WF_PO_CHARGE_ACC','is_encumbrace_on',x_progress);
1279 raise;
1280
1281 end is_encumbrance_on;
1282
1283 --
1284
1285 -- * ****************************************************************************** *
1286 -- * ****************************************************************************** *
1287
1288 --
1289 -- is_po_project_related
1290 --
1291 -- This is a dummy function that should be replaced by the customized function
1292 -- activity in the workflow that return TRUE or FALSE based on whether you want to
1293 -- use the default PO expense charge account generation rules or use "CUSTOMIZED"
1294 -- project accounting rules.
1295
1296 procedure is_po_project_related ( itemtype in varchar2,
1297 itemkey in varchar2,
1298 actid in number,
1299 funcmode in varchar2,
1300 result out NOCOPY varchar2 )
1301 is
1302 begin
1303
1304 result := 'COMPLETE:F';
1305 return;
1306
1307 end is_po_project_related;
1308
1309
1310 /*
1311 * Set the debug mode on
1312 */
1313
1314 PROCEDURE debug_on IS
1315 BEGIN
1316 debug_acc_generator_wf := TRUE;
1317
1318 END debug_on;
1319
1320 /*
1321 * Set the debug mode off
1322 */
1323
1324 PROCEDURE debug_off IS
1325 BEGIN
1326 debug_acc_generator_wf := FALSE;
1327
1328 END debug_off;
1329
1330 --< Shared Proc FPJ Start >
1331
1332 ---------------------------------------------------------------------------
1333 --Start of Comments
1334 --Name: is_dest_accounts_flow_type
1335 --Pre-reqs:
1336 -- None.
1337 --Modifies:
1338 -- None
1339 --Locks:
1340 -- None.
1341 --Function:
1342 -- The PO Account Generator could be run 2 times -- first to generate the
1343 -- PO Accounts and second time to generate the DESTINATION accounts. The
1344 -- calling program specifies the flow type in the WF item attribute
1345 -- called ACCOUNT_GENERATION_FLOW_TYPE.
1346 -- The flow could take either of the following 2 values:
1347 -- 1. PO_WF_BUILD_ACCOUNT_INIT.g_po_accounts (PO_ACCOUNTS)
1348 -- 2. PO_WF_BUILD_ACCOUNT_INIT.g_destination_accounts (DESTINATION_ACCOUNTS)
1349 -- This function determines if the flow type specified by the calling
1350 -- program is for the Destination Accounts or not.
1351 --Parameters:
1352 --IN:
1353 -- Standard workflow function parameters
1354 --OUT:
1355 -- Standard workflow function result parameter of type 'Yes/No'.
1356 --Testing:
1357 --
1358 --Notes:
1359 -- We could have used the standard WF activity COMPARE_TEXT for this
1360 -- purpose, but it is not as readable because on the workflow diagram
1361 -- we would see just 'Compare Text' written beneath the activity. To find
1362 -- out what it is exactly being compared, we would have to go to the 'Node
1363 -- Attribute' tab of that activity and inspect the item attributes. Also,
1364 -- we would have had to hard-code the value being compared there. In this
1365 -- function, we can use the global variable
1366 -- 'PO_WF_BUILD_ACCOUNT_INIT.g_po_accounts' instead of the hard coded value.
1367 --End of Comments
1368 ---------------------------------------------------------------------------
1369 PROCEDURE is_dest_accounts_flow_type
1370 (
1371 itemtype IN VARCHAR2,
1372 itemkey IN VARCHAR2,
1373 actid IN NUMBER,
1374 funcmode IN VARCHAR2,
1375 result OUT NOCOPY VARCHAR2
1376 )
1377 IS
1378 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
1379 l_account_gen_flow_type VARCHAR2(25);
1380 BEGIN
1381 l_progress := '010';
1382
1383 -- Do nothing in cancel or timeout mode
1384 IF (funcmode <> WF_ENGINE.eng_run) THEN
1385 result := WF_ENGINE.eng_null;
1386 RETURN;
1387 END IF;
1388
1389 l_progress := '020';
1390
1391 l_account_gen_flow_type := PO_WF_UTIL_PKG.GetItemAttrText(
1392 itemtype => itemtype,
1393 itemkey => itemkey,
1394 aname => 'ACCOUNT_GENERATION_FLOW_TYPE');
1395
1396 l_progress := '030';
1397
1398 IF (l_account_gen_flow_type IS NULL OR
1399 l_account_gen_flow_type = PO_WF_BUILD_ACCOUNT_INIT.g_po_accounts)
1400 THEN
1401 result := WF_ENGINE.eng_completed || ':N';
1402 ELSE
1403 result := WF_ENGINE.eng_completed || ':Y';
1404 END IF;
1405
1406 IF (g_po_wf_debug = 'Y') THEN
1407 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1408 'PO_WF_PO_CHARGE_ACC.is_dest_accounts_flow_type result='||result);
1409 END IF;
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412 WF_CORE.context('PO_WF_PO_CHARGE_ACC', 'is_dest_accounts_flow_type',
1413 l_progress);
1414 RAISE;
1415 END is_dest_accounts_flow_type;
1416
1417 ---------------------------------------------------------------------------
1418 --Start of Comments
1419 --Name: is_SPS_distribution
1420 --Pre-reqs:
1421 -- None.
1422 --Modifies:
1423 -- None
1424 --Locks:
1425 -- None.
1426 --Function:
1427 -- Determines if it is a Shared Procurement Services (SPS) distribution.
1428 --Parameters:
1429 --IN:
1430 -- Standard workflow function parameters
1431 --OUT:
1432 -- Standard workflow function result parameter of type 'Yes/No'.
1433 --Testing:
1434 --End of Comments
1435 ---------------------------------------------------------------------------
1436 PROCEDURE is_SPS_distribution
1437 (
1438 itemtype IN VARCHAR2,
1439 itemkey IN VARCHAR2,
1440 actid IN NUMBER,
1441 funcmode IN VARCHAR2,
1442 result OUT NOCOPY VARCHAR2
1443 )
1444 IS
1445 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
1446 l_is_SPS_distribution VARCHAR2(1);
1447 BEGIN
1448 -- Do nothing in cancel or timeout mode
1449 IF (funcmode <> WF_ENGINE.eng_run) THEN
1450 result := WF_ENGINE.eng_null;
1451 RETURN;
1452 END IF;
1453
1454 l_progress := '010';
1455 l_is_SPS_distribution := PO_WF_UTIL_PKG.GetItemAttrText(
1456 itemtype => itemtype,
1457 itemkey => itemkey,
1458 aname => 'IS_SPS_DISTRIBUTION');
1459
1460 l_progress := '020';
1461 IF (l_is_SPS_distribution IS NULL) THEN
1462 result := WF_ENGINE.eng_completed || ':N';
1463 ELSE
1464 result := WF_ENGINE.eng_completed || ':' || l_is_SPS_distribution;
1465 END IF;
1466
1467 IF (g_po_wf_debug = 'Y') THEN
1468 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1469 'PO_WF_PO_CHARGE_ACC.is_SPS_distribution result='||result);
1470 END IF;
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 WF_CORE.context('PO_WF_PO_CHARGE_ACC', 'is_SPS_distribution', l_progress);
1474 RAISE;
1475 END is_SPS_distribution;
1476
1477 ---------------------------------------------------------------------------
1478 --Start of Comments
1479 --Name: is_shopfloor_enabled_item
1480 --Pre-reqs:
1481 -- None.
1482 --Modifies:
1483 -- None
1484 --Locks:
1485 -- None.
1486 --Function:
1487 -- Determines if a given item is enabled for shopfloor (outside processing)
1488 -- in the given Inventory Org
1489 --Parameters:
1490 --IN:
1491 -- p_item_id
1492 -- : The given item's ID
1493 -- p_inv_org_id
1494 -- : The org ID of the Inventory Org where the item's attribute need to be
1495 -- tested.
1496 --OUT:
1497 -- None.
1498 --RETURN
1499 -- BOOLEAN: TRUE if the item is shopfloor enables, FALSE otherwise.
1500 --Testing:
1501 --End of Comments
1502 ---------------------------------------------------------------------------
1503 FUNCTION is_shopfloor_enabled_item(p_item_id IN NUMBER,
1504 p_inv_org_id IN NUMBER) RETURN BOOLEAN
1505 IS
1506 is_shopfloor_enabled_item MTL_SYSTEM_ITEMS.outside_operation_flag%TYPE;
1507 BEGIN
1508 --SQL WHAT: Get the outside_operation_flag for a given item
1509 --SQL WHY: To find out if the item is Shopfloor enabled.
1510 SELECT outside_operation_flag -- it is a NOT NULL column
1511 INTO is_shopfloor_enabled_item
1512 FROM MTL_SYSTEM_ITEMS
1513 WHERE inventory_item_id = p_item_id AND
1514 organization_id = p_inv_org_id;
1515
1516 IF (is_shopfloor_enabled_item = 'Y') THEN
1517 RETURN TRUE;
1518 ELSE
1519 RETURN FALSE;
1520 END IF;
1521 EXCEPTION
1522 WHEN NO_DATA_FOUND THEN
1523 RETURN FALSE;
1524 WHEN OTHERS THEN
1525 RAISE;
1526 END is_shopfloor_enabled_item;
1527
1528 ---------------------------------------------------------------------------
1529 --Start of Comments
1530 --Name: get_COGS_account
1531 --Pre-reqs:
1532 -- None.
1533 --Modifies:
1534 -- None
1535 --Locks:
1536 -- None.
1537 --Function:
1538 -- Gets the Cost of Goods Sold (COGS) account associated with the intermediate
1539 -- Logical Inventory Org for the POU.
1540 --Parameters:
1541 --IN:
1542 -- p_inv_org_id IN NUMBER
1543 -- : The Org ID of the Logical Inv Org associated with a Transaction Flow
1544 --OUT:
1545 -- None
1546 --RETURN
1547 -- NUMBER
1548 -- : The Cost of Goods Sold Account for the LINV.
1549 --Testing:
1550 --End of Comments
1551 ---------------------------------------------------------------------------
1552 FUNCTION get_COGS_account(p_inv_org_id IN NUMBER) RETURN NUMBER
1553 IS
1554 l_COGS_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE := NULL;
1555 BEGIN
1556 --SQL WHAT: Get the Get the COGS account for the Logical Inventory Org
1557 -- associated with an OU for a given Transaction Flow.
1558 --SQL WHY: Default as the PO Charge Account for SPS case
1559 SELECT cost_of_sales_account
1560 INTO l_COGS_account_id
1561 FROM MTL_PARAMETERS
1562 WHERE ORGANIZATION_ID = p_inv_org_id;
1563
1564 RETURN l_COGS_account_id;
1565 EXCEPTION
1566 WHEN NO_DATA_FOUND THEN
1567 RETURN NULL;
1568 WHEN OTHERS THEN
1569 RAISE;
1570 END get_COGS_account;
1571
1572 ---------------------------------------------------------------------------
1573 --Start of Comments
1574 --Name: get_item_expense_account
1575 --Pre-reqs:
1576 -- None.
1577 --Modifies:
1578 -- None
1579 --Locks:
1580 -- None.
1581 --Function:
1582 -- Gets the Expense Account associated with a given inventory item.
1583 --Parameters:
1584 --IN:
1585 -- p_item_id IN NUMBER
1586 -- : The given item id
1587 -- p_inv_org_id IN NUMBER
1588 -- : The inventory org id to which the item belongs
1589 --OUT:
1590 -- None
1591 --RETURN
1592 -- NUMBER
1593 -- : The Expense Account associated with the given inventory item.
1594 --Testing:
1595 --End of Comments
1596 ---------------------------------------------------------------------------
1597 FUNCTION get_item_expense_account(p_item_id IN NUMBER,
1598 p_inv_org_id IN NUMBER)
1599 RETURN NUMBER
1600 IS
1601 l_item_expense_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1602 BEGIN
1603 --SQL WHAT: Get the Expense Account associated with an item in the Logical
1604 -- Inventory Org for a Start OU for a given Transaction Flow.
1605 --SQL WHY: To default this as the PO Expense Account for SPS case
1606 SELECT expense_account
1607 INTO l_item_expense_account_id
1608 FROM MTL_SYSTEM_ITEMS
1609 WHERE organization_id = p_inv_org_id AND
1610 inventory_item_id = p_item_id;
1611
1612 RETURN l_item_expense_account_id;
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 RETURN NULL;
1616 WHEN OTHERS THEN
1617 RAISE;
1618 END get_item_expense_account;
1619
1620 ---------------------------------------------------------------------------
1621 --Start of Comments
1622 --Name: get_org_expense_account
1623 --Pre-reqs:
1624 -- None.
1625 --Modifies:
1626 -- None
1627 --Locks:
1628 -- None.
1629 --Function:
1630 -- Gets the Expense Account associated with a given inventory org.
1631 --Parameters:
1632 --IN:
1633 -- p_inv_org_id IN NUMBER
1634 -- : The given inventory org id
1635 --OUT:
1636 -- None
1637 --RETURN
1638 -- NUMBER
1639 -- : The Expense Account associated with the given inventory org.
1640 --Testing:
1641 --End of Comments
1642 ---------------------------------------------------------------------------
1643 FUNCTION get_org_expense_account(p_inv_org_id IN NUMBER)
1644 RETURN NUMBER
1645 IS
1646 l_org_expense_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1647 BEGIN
1648 --SQL WHAT: Get the Expense Account associated with the Logical
1649 -- Inventory Org for a Start OU for a given Transaction Flow.
1650 --SQL WHY: To default this as the PO Expense Account for SPS case
1651 SELECT expense_account
1652 INTO l_org_expense_account_id
1653 FROM MTL_PARAMETERS
1654 WHERE organization_id = p_inv_org_id;
1655
1656 RETURN l_org_expense_account_id;
1657 EXCEPTION
1658 WHEN NO_DATA_FOUND THEN
1659 RETURN NULL;
1660 WHEN OTHERS THEN
1661 RAISE;
1662 END get_org_expense_account;
1663
1664 ---------------------------------------------------------------------------
1665 --Start of Comments
1666 --Name: get_org_material_account
1667 --Pre-reqs:
1668 -- None.
1669 --Modifies:
1670 -- None
1671 --Locks:
1672 -- None.
1673 --Function:
1674 -- Gets the Material Account associated with a given inventory org.
1675 --Parameters:
1676 --IN:
1677 -- p_inv_org_id IN NUMBER
1678 -- : The given inventory org id
1679 --OUT:
1680 -- None
1681 --RETURN
1682 -- NUMBER
1683 -- : The Material Account associated with the given inventory org.
1684 --Testing:
1685 --End of Comments
1686 ---------------------------------------------------------------------------
1687 FUNCTION get_org_material_account(p_inv_org_id IN NUMBER)
1688 RETURN NUMBER
1689 IS
1690 l_org_material_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1691 BEGIN
1692 --SQL WHAT: Get the Material Account associated with the Logical
1693 -- Inventory Org for a Start OU for a given Transaction Flow.
1694 --SQL WHY: To default this as the PO Expense Account for SPS case
1695 SELECT material_account
1696 INTO l_org_material_account_id
1697 FROM MTL_PARAMETERS
1698 WHERE organization_id = p_inv_org_id;
1699
1700 RETURN l_org_material_account_id;
1701 EXCEPTION
1702 WHEN NO_DATA_FOUND THEN
1703 RETURN NULL;
1704 WHEN OTHERS THEN
1705 RAISE;
1706 END get_org_material_account;
1707
1708 ---------------------------------------------------------------------------
1709 --Start of Comments
1710 --Name: sanity_check_logical_inv_org
1711 --Pre-reqs:
1712 -- None.
1713 --Modifies:
1714 -- None
1715 --Locks:
1716 -- None.
1717 --Function:
1718 -- This function gets executed in DEBUG MODE ONLY.
1719 -- The purpose of this function is to perform a sanity check on the Logical
1720 -- Inventory Org that is derived from the MTL_TRANSATION_FLOW_LINES table.
1721 -- Since this a new table with a brand new functionality of Transaction Flows,
1722 -- and since this table belongs to a group outside of Procurement Family, we
1723 -- should make sure that the Logical Inventory Org (LINV) derived from this
1724 -- table is correct.
1725 -- Here we check if the LINV actually belong to the POU, as it should.
1726 -- This function merely inserts debug comments in PO debug tables.
1727 --
1728 --Parameters:
1729 --IN:
1730 -- p_logical_inv_org_id IN NUMBER
1731 -- : The logical inventory org id, derived from MTL table.
1732 -- p_itemtype IN VARCHAR2
1733 -- : The item type of the current AG workflow
1734 -- p_itemkey IN VARCHAR2
1735 -- : The item key of the current AG workflow
1736 --
1737 --OUT:
1738 -- None
1739 --Testing:
1740 --End of Comments
1741 ---------------------------------------------------------------------------
1742 PROCEDURE sanity_check_logical_inv_org(p_logical_inv_org_id IN NUMBER,
1743 p_itemtype IN VARCHAR2,
1744 p_itemkey IN VARCHAR2)
1745 IS
1746 l_temp_ou_id NUMBER;
1747 l_purchasing_ou_id NUMBER;
1748 BEGIN
1749 IF (g_po_wf_debug = 'Y') THEN
1750 BEGIN
1751 SELECT operating_unit
1752 INTO l_temp_ou_id
1753 FROM org_organization_definitions
1754 WHERE organization_id = p_logical_inv_org_id;
1755
1756 l_purchasing_ou_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1757 itemtype => p_itemtype,
1758 itemkey => p_itemkey,
1759 aname => 'PURCHASING_OU_ID');
1760
1761 IF (l_temp_ou_id <> l_purchasing_ou_id) THEN
1762 PO_WF_DEBUG_PKG.insert_debug(p_itemtype, p_itemkey,
1763 'LINV does not belong to POU');
1764 END IF;
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 -- record and ignore the exception, this is just debug code.
1768 PO_WF_DEBUG_PKG.insert_debug(p_itemtype, p_itemkey,
1769 'Exception while sanity checking LINV');
1770 END;
1771 END IF; -- IF (g_po_wf_debug = 'Y'), sanity check for LINV
1772 END sanity_check_logical_inv_org;
1773
1774 ---------------------------------------------------------------------------
1775 --Start of Comments
1776 --Name: get_SPS_charge_account
1777 --Pre-reqs:
1778 -- None.
1779 --Modifies:
1780 -- Item Attribute: TEMP_ACCOUNT_ID
1781 --Locks:
1782 -- None.
1783 --Function:
1784 -- Gets the PO Charge Account for SPS case.
1785 --Parameters:
1786 --IN:
1787 -- Standard workflow function parameters
1788 --OUT:
1789 -- Standard workflow function result parameter
1790 --Testing:
1791 --End of Comments
1792 ---------------------------------------------------------------------------
1793 PROCEDURE get_SPS_charge_account
1794 (
1795 itemtype IN VARCHAR2,
1796 itemkey IN VARCHAR2,
1797 actid IN NUMBER,
1798 funcmode IN VARCHAR2,
1799 result OUT NOCOPY VARCHAR2
1800 )
1801 IS
1802 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
1803 l_item_id NUMBER;
1804 l_transaction_flow_header_id NUMBER;
1805 l_logical_inv_org_id NUMBER;
1806 l_SPS_charge_account_id NUMBER := NULL;
1807 l_item_inventory_type VARCHAR2(10);
1808 BEGIN
1809 l_progress := '010';
1810
1811 -- Do nothing in cancel or timeout mode
1812 IF (funcmode <> WF_ENGINE.eng_run) THEN
1813 result := WF_ENGINE.eng_null;
1814 RETURN;
1815 END IF;
1816
1817 l_progress := '020';
1818 l_item_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1819 itemtype => itemtype,
1820 itemkey => itemkey,
1821 aname => 'ITEM_ID');
1822
1823 l_progress := '030';
1824 l_transaction_flow_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1825 itemtype => itemtype,
1826 itemkey => itemkey,
1827 aname => 'TRANSACTION_FLOW_HEADER_ID');
1828
1829 l_progress := '040';
1830
1831 -- Get the org id for the Logical Inventory Org associated with
1832 -- the given Transaction Flow. This LINV would belong to the POU.
1833 l_logical_inv_org_id := PO_SHARED_PROC_PVT.get_logical_inv_org_id(l_transaction_flow_header_id);
1834
1835 l_progress := '050';
1836 IF (l_logical_inv_org_id IS NULL) THEN
1837 l_progress := 'Logical Inventory Org Not Found';
1838 APP_EXCEPTION.raise_exception(exception_type => 'GET_LOGICAL_INV_ORG_ID',
1839 exception_code => 0,
1840 exception_text => l_progress);
1841 END IF;
1842
1843 l_progress := '060';
1844 -- Sanity check. Does LINV belong to POU?
1845 -- Check this in debug mode only.
1846 sanity_check_logical_inv_org(l_logical_inv_org_id,
1847 itemtype,
1848 itemkey);
1849
1850 l_progress := '070';
1851 -- 1. If one-time item or shopfloor enabled item, get COGS account
1852 IF ( (l_item_id IS NULL) OR
1853 (is_shopfloor_enabled_item(l_item_id,
1854 l_logical_inv_org_id)) ) THEN
1855 l_SPS_charge_account_id := get_COGS_account(l_logical_inv_org_id);
1856
1857 ELSE -- else if NOT a one-time or shopfloor enabled item --(
1858
1859 l_progress := '080';
1860 -- Get the Inventory Item Type of the given item in the given LINV.
1861 IF (check_inv_item_type(itemtype,
1862 itemkey,
1863 l_logical_inv_org_id,
1864 l_item_id) = 'EXPENSE') THEN
1865 l_item_inventory_type := 'EXPENSE';
1866 ELSE
1867 l_item_inventory_type := 'ASSET';
1868 END IF;
1869
1870 l_progress := '090';
1871 -- 2a. If Expense Item, get Item Expense Account
1872 IF ( (l_SPS_charge_account_id IS NULL) AND
1873 (l_item_inventory_type = 'EXPENSE') ) THEN
1874 l_progress := '100';
1875 l_SPS_charge_account_id := get_item_expense_account(l_item_id,
1876 l_logical_inv_org_id);
1877
1878 l_progress := '110';
1879 -- 2b. If Expense Item, get Org Expense Account
1880 IF (l_SPS_charge_account_id IS NULL) THEN
1881 l_SPS_charge_account_id := get_org_expense_account(l_logical_inv_org_id);
1882 END IF;
1883 END IF;
1884
1885 l_progress := '120';
1886 -- 3. If Asset Item, get Org Material Account
1887 IF ( (l_SPS_charge_account_id IS NULL) AND
1888 (l_item_inventory_type = 'ASSET') ) THEN
1889 l_SPS_charge_account_id := get_org_material_account(l_logical_inv_org_id);
1890 END IF;
1891
1892 l_progress := '130';
1893 IF (l_SPS_charge_account_id IS NULL) THEN
1894 result := WF_ENGINE.eng_completed || ':FAILURE';
1895 RETURN;
1896 END IF;
1897 END IF; -- IF (l_item_id IS NULL) OR
1898 -- (is_shopfloor_enabled_item(l_item_id, l_logical_inv_org_id)) --)
1899
1900 l_progress := '140';
1901 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
1902 itemkey => itemkey,
1903 aname => 'TEMP_ACCOUNT_ID',
1904 avalue => l_SPS_charge_account_id);
1905
1906 l_progress := '150';
1907 result := WF_ENGINE.eng_completed || ':SUCCESS';
1908
1909 l_progress := '160';
1910
1911 IF (g_po_wf_debug = 'Y') THEN
1912 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1913 'PO_WF_PO_CHARGE_ACC.get_SPS_charge_account result='||result);
1914 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1915 'PO_WF_PO_CHARGE_ACC.get_SPS_charge_account l_SPS_charge_account_id='||
1916 l_SPS_charge_account_id);
1917 END IF;
1918 EXCEPTION
1919 WHEN OTHERS THEN
1920 IF (g_po_wf_debug = 'Y') THEN
1921 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1922 'PO_WF_PO_CHARGE_ACC.get_SPS_charge_account EXCEPTION at '|| l_progress);
1923 END IF;
1924 WF_CORE.context('PO_WF_PO_CHARGE_ACC', 'get_SPS_charge_account', l_progress);
1925 RAISE;
1926 END get_SPS_charge_account;
1927
1928 ---------------------------------------------------------------------------
1929 --Start of Comments
1930 --Name: is_dest_charge_acc_null
1931 --Pre-reqs:
1932 -- None.
1933 --Modifies:
1934 -- Item Attribute: TEMP_ACCOUNT_ID
1935 --Locks:
1936 -- None.
1937 --Function:
1938 -- Checks if the attribute DEST_CHARGE_ACCOUNT_ID is NULL or not.
1939 -- If it is NULL, it returns 'N'.
1940 -- If it is not NULL, it copies the value in DEST_CHARGE_ACCOUNT_ID to
1941 -- TEMP_ACCOUNT_ID and returns 'Y'.
1942 --Parameters:
1943 --IN:
1944 -- Standard workflow function parameters
1945 --OUT:
1946 -- Standard workflow function result parameter
1947 --Testing:
1948 --End of Comments
1949 ---------------------------------------------------------------------------
1950 PROCEDURE is_dest_charge_acc_null
1951 (
1952 itemtype IN VARCHAR2,
1953 itemkey IN VARCHAR2,
1954 actid IN NUMBER,
1955 funcmode IN VARCHAR2,
1956 result OUT NOCOPY VARCHAR2
1957 )
1958 IS
1959 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
1960 l_dest_charge_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1961 l_temp_acc_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
1962 BEGIN
1963 l_progress := '010';
1964
1965 -- Do nothing in cancel or timeout mode
1966 IF (funcmode <> WF_ENGINE.eng_run) THEN
1967 result := WF_ENGINE.eng_null;
1968 RETURN;
1969 END IF;
1970
1971 l_progress := '020';
1972 l_dest_charge_account_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1973 itemtype => itemtype,
1974 itemkey => itemkey,
1975 aname => 'DEST_CHARGE_ACCOUNT_ID');
1976
1977 l_progress := '030';
1978 IF l_dest_charge_account_id IS NULL OR
1979 l_dest_charge_account_id = 0 OR
1980 l_dest_charge_account_id = -1 THEN
1981 result := WF_ENGINE.eng_completed || ':Y';
1982 ELSE
1983 IF (g_po_wf_debug = 'Y') THEN
1984 l_temp_acc_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1985 itemtype => itemtype,
1986 itemkey => itemkey,
1987 aname => 'TEMP_ACCOUNT_ID');
1988
1989 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
1990 'PO_WF_PO_CHARGE_ACC.is_dest_charge_acc_null '||
1991 'Copying DestChargeAccId to TEMP_ACCOUNT_ID '||
1992 '(current val = ' || l_temp_acc_id || ') ' ||
1993 'dest_charge_account_id='||l_dest_charge_account_id);
1994 END IF;
1995
1996 -- If the Dest Charge Account is not null (autocreate, or through
1997 -- Forms/PDOI), then copy it into the TEMP_ACCOUNT_ID.
1998 l_progress := '040';
1999 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
2000 itemkey => itemkey,
2001 aname => 'TEMP_ACCOUNT_ID',
2002 avalue => l_dest_charge_account_id);
2003 l_progress := '050';
2004 result := WF_ENGINE.eng_completed || ':N';
2005 END IF;
2006
2007 IF (g_po_wf_debug = 'Y') THEN
2008 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2009 'PO_WF_PO_CHARGE_ACC.is_dest_charge_acc_null result='||result||
2010 ' l_dest_charge_account_id='||l_dest_charge_account_id);
2011 END IF;
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 WF_CORE.context('PO_WF_PO_CHARGE_ACC', 'is_dest_charge_acc_null',
2016 l_progress);
2017 RAISE;
2018 END is_dest_charge_acc_null;
2019
2020
2021 ---------------------------------------------------------------------------
2022 --Start of Comments
2023 --Name: are_COAs_same
2024 --Pre-reqs:
2025 -- None.
2026 --Modifies:
2027 -- None
2028 --Locks:
2029 -- None.
2030 --Function:
2031 -- Determines if the Chart of Accounts (COA's) of the POU and the DOU are
2032 -- the same or not.
2033 --Parameters:
2034 --IN:
2035 -- Standard workflow function parameters
2036 --OUT:
2037 -- Standard workflow function result parameter
2038 --Testing:
2039 --End of Comments
2040 ---------------------------------------------------------------------------
2041 PROCEDURE are_COAs_same
2042 (
2043 itemtype IN VARCHAR2,
2044 itemkey IN VARCHAR2,
2045 actid IN NUMBER,
2046 funcmode IN VARCHAR2,
2047 result OUT NOCOPY VARCHAR2
2048 )
2049 IS
2050 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
2051 l_pou_coa_id GL_CODE_COMBINATIONS.chart_of_accounts_id%TYPE;
2052 l_ship_to_ou_coa_id GL_CODE_COMBINATIONS.chart_of_accounts_id%TYPE;
2053 l_is_sps_distribution VARCHAR2(10); --<BUG 4882220>
2054 BEGIN
2055 l_progress := '010';
2056
2057 -- Do nothing in cancel or timeout mode
2058 IF (funcmode <> WF_ENGINE.eng_run) THEN
2059 result := WF_ENGINE.eng_null;
2060 RETURN;
2061 END IF;
2062
2063 --<BUG 4882220 START>
2064 -- Return immediately if this is not an SPS distribution.
2065 --
2066 l_progress := '015';
2067 l_is_sps_distribution := PO_WF_UTIL_PKG.GetItemAttrText(
2068 itemtype => itemtype,
2069 itemkey => itemkey,
2070 aname => 'IS_SPS_DISTRIBUTION' );
2071
2072 IF nvl( l_is_sps_distribution, 'N' ) = 'N' THEN
2073 result := WF_ENGINE.eng_completed || ':N';
2074 RETURN;
2075 END IF;
2076 --<BUG 4882220 END>
2077
2078 l_progress := '020';
2079 l_pou_coa_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
2080 itemtype => itemtype,
2081 itemkey => itemkey,
2082 aname => 'CHART_OF_ACCOUNTS_ID');
2083
2084 l_progress := '030';
2085 -- Use the wrapper because it is a new attribute
2086 l_ship_to_ou_coa_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
2087 itemtype => itemtype,
2088 itemkey => itemkey,
2089 aname => 'SHIP_TO_OU_COA_ID');
2090
2091 l_progress := '040';
2092 IF (l_ship_to_ou_coa_id IS NULL) OR
2093 (l_ship_to_ou_coa_id <> l_pou_coa_id) THEN
2094 result := WF_ENGINE.eng_completed || ':N';
2095 ELSE
2096 result := WF_ENGINE.eng_completed || ':Y';
2097 END IF;
2098
2099 IF (g_po_wf_debug = 'Y') THEN
2100 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2101 'PO_WF_PO_CHARGE_ACC.are_COAs_same result='||result);
2102 END IF;
2103 EXCEPTION
2104 WHEN OTHERS THEN
2105 WF_CORE.context('PO_WF_PO_CHARGE_ACC', 'are_COAs_same',
2106 l_progress);
2107 RAISE;
2108 END are_COAs_same;
2109
2110 --< Shared Proc FPJ End >
2111
2112 --Bug4033391 Start
2113 --In the account generator, all the accounts are validated using the activity
2114 --FND_FLEX_VALIDATE_COMBINATION, which applies the security rule irrespective
2115 --of whether the account is entered by the user or derived.
2116 --Security rules should be only be applied for the user entered accounts.
2117 --This is fixed by setting the responsibility_id of the context to null
2118 --before calling validation and resetting the same after validation.
2119 -------------------------------------------------------------------------------
2120 --Start of Comments
2121 --Name: set_null_resp_id
2122 --Pre-reqs:
2123 -- None.
2124 --Modifies:
2125 -- None
2126 --Locks:
2127 -- None.
2128 --Function:
2129 -- Sets the responsibility ID to NULL
2130 --End of Comments
2131 -----------------------------------------------------------------------------
2132 PROCEDURE set_null_resp_id(itemtype IN VARCHAR2,
2133 itemkey IN VARCHAR2,
2134 actid IN NUMBER,
2135 funcmode IN VARCHAR2,
2136 result OUT NOCOPY VARCHAR2)
2137 IS
2138 l_progress varchar2(3);
2139 BEGIN
2140 l_progress := '010';
2141 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2142 itemkey => itemkey,
2143 aname =>'RESPONSIBILITY_ID',
2144 avalue => fnd_global.resp_id);
2145 l_progress := '020';
2146 IF (g_po_wf_debug = 'Y') THEN
2147 PO_WF_DEBUG_PKG.insert_debug(itemtype,
2148 itemkey,
2149 'PO_WF_PO_CHARGE_ACC.set_null_resp_id: Setting the Responsibility to NULL ');
2150 END IF;
2151 FND_GLOBAL.apps_initialize( user_id => fnd_global.user_id,
2152 resp_id => NULL,
2153 resp_appl_id => fnd_global.resp_appl_id);
2154 l_progress := '030';
2155 result := WF_ENGINE.eng_completed || ':Y';
2156 EXCEPTION
2157 WHEN OTHERS THEN
2158 WF_CORE.context('PO_WF_PO_CHARGE_ACC','SET_NULL_RESP_ID',l_progress);
2159 raise;
2160
2161 END set_null_resp_id;
2162
2163 -------------------------------------------------------------------------------
2164 --Start of Comments
2165 --Name: reset_resp_id
2166 --Pre-reqs:
2167 -- None.
2168 --Modifies:
2169 -- None
2170 --Locks:
2171 -- None.
2172 --Function:
2173 -- Sets the responsibility ID back to original value
2174 --End of Comments
2175 -----------------------------------------------------------------------------
2176 PROCEDURE reset_resp_id(itemtype IN VARCHAR2,
2177 itemkey IN VARCHAR2,
2178 actid IN NUMBER,
2179 funcmode IN VARCHAR2,
2180 result OUT NOCOPY VARCHAR2)
2181 IS
2182 l_progress varchar2(3);
2183 l_resp_id FND_RESPONSIBILITY.responsibility_id%type;
2184 BEGIN
2185 l_resp_id :=NULL;
2186 l_progress := '010';
2187 l_resp_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2188 itemkey => itemkey,
2189 aname => 'RESPONSIBILITY_ID');
2190 l_progress := '020';
2191 IF (g_po_wf_debug = 'Y') THEN
2192 PO_WF_DEBUG_PKG.insert_debug(itemtype,
2193 itemkey,
2194 'PO_WF_PO_CHARGE_ACC.reset_resp_id: Setting the Responsibility back to:' || l_resp_id);
2195 END IF;
2196 FND_GLOBAL.apps_initialize( user_id => fnd_global.user_id,
2197 resp_id => l_resp_id,
2198 resp_appl_id => fnd_global.resp_appl_id);
2199 l_progress := '030';
2200 result := WF_ENGINE.eng_completed || ':Y';
2201 EXCEPTION
2202 WHEN OTHERS THEN
2203 WF_CORE.context('PO_WF_PO_CHARGE_ACC','RESET_RESP_ID',l_progress);
2204 RAISE;
2205 END reset_resp_id;
2206 --Bug4033391 End
2207
2208 ---------------------------------------------------------------------------
2209 --Start of Comments
2210 --Bug 7260456: Added this procedure.
2211 --Name: validate_combination
2212 --Pre-reqs:
2213 -- FND_FLEX_WORKFLOW_APIS.VALIDATE_COMBINATION should be called to validate
2214 -- standard validations. Workflow attributes FND_FLEX_STATUS,
2215 -- CHART_OF_ACCOUNTS_ID, FND_FLEX_SEGMENTS, ENCUMBRANCE_DATE must be set.
2216 --Modifies:
2217 -- None
2218 --Locks:
2219 -- None.
2220 --Function:
2221 -- This procedure checks for validations apart from
2222 -- FND_FLEX_WORKFLOW_APIS.VALIDATE_COMBINATION and should be called just
2223 -- after calling VALIDATE_COMBINATION.
2224 -- This procedure has been created to validate for parent account and the
2225 -- posting allowed flag.
2226 -- The procedure is called from the account generator workflow, for
2227 -- validating the accounts.
2228 --Parameters:
2229 --IN:
2230 -- Standard workflow function parameters
2231 --OUT:
2232 -- Standard workflow function result parameter.
2233 --Testing:
2234 --
2235 --Notes:
2236 -- This procedure has been created because the standard workflow API
2237 -- FND_FLEX_WORKFLOW_APIS.VALIDATE_COMBINATION does not support VRULE.
2238 -- Bug 7168777 has been logged for same.
2239 --End of Comments
2240 ---------------------------------------------------------------------------
2241 PROCEDURE validate_combination(
2242 itemtype IN VARCHAR2,
2243 itemkey IN VARCHAR2,
2244 actid IN NUMBER,
2245 funcmode IN VARCHAR2,
2246 result OUT NOCOPY VARCHAR2)
2247 IS
2248 l_progress wf_item_activity_statuses.error_stack%TYPE;
2249 l_flex_status VARCHAR2(100);
2250 l_concat_segments VARCHAR2(2000);
2251 l_validation_date DATE;
2252 l_coa_id gl_code_combinations.chart_of_accounts_id%TYPE;
2253 l_is_combination_valid BOOLEAN;
2254 BEGIN
2255
2256 l_progress := '010';
2257 -- Do nothing in cancel or timeout mode
2258 IF (funcmode <> wf_engine.eng_run) THEN
2259 result := wf_engine.eng_null;
2260 RETURN;
2261 END IF;
2262
2263 l_progress := '020';
2264 l_flex_status := po_wf_util_pkg.getitemattrtext(
2265 itemtype => itemtype,
2266 itemkey => itemkey,
2267 aname => 'FND_FLEX_STATUS');
2268
2269 IF (g_po_wf_debug = 'Y') THEN
2270 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2271 'PO_WF_PO_CHARGE_ACC.validate_combination: l_flex_status='
2272 ||l_flex_status);
2273 END IF;
2274
2275 -- Do nothing if FND_FLEX_STATUS is INVALID
2276 IF (l_flex_status = 'INVALID') THEN
2277 result := wf_engine.eng_null;
2278 RETURN;
2279 END IF;
2280
2281 l_coa_id := po_wf_util_pkg.getitemattrtext(
2282 itemtype => itemtype,
2283 itemkey => itemkey,
2284 aname => 'CHART_OF_ACCOUNTS_ID');
2285
2286 l_concat_segments := po_wf_util_pkg.getitemattrtext(
2287 itemtype => itemtype,
2288 itemkey => itemkey,
2289 aname => 'FND_FLEX_SEGMENTS');
2290
2291 l_validation_date := nvl(po_wf_util_pkg.getitemattrtext(
2292 itemtype => itemtype,
2293 itemkey => itemkey,
2294 aname => 'ENCUMBRANCE_DATE'),
2295 SYSDATE);
2296
2297 l_progress := '030';
2298 -- Validate VRULE for the combination
2299 l_is_combination_valid := fnd_flex_keyval.validate_segs(
2300 operation => 'CHECK_COMBINATION',
2301 appl_short_name => 'SQLGL',
2302 key_flex_code => 'GL#',
2303 structure_number => l_coa_id,
2304 concat_segments => l_concat_segments,
2305 validation_date => l_validation_date,
2306 vrule => '\nSUMMARY_FLAG\nI' ||
2307 '\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
2308 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED\nI\nNAME=PO_ALL_POSTING_NA\nY');
2309
2310 IF (NOT l_is_combination_valid) THEN
2311 IF (g_po_wf_debug = 'Y') THEN
2312 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
2313 'PO_WF_PO_CHARGE_ACC.validate_combination: l_is_combination_valid is false');
2314 END IF;
2315
2316 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_STATUS','INVALID');
2317 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_MESSAGE',
2318 fnd_flex_keyval.encoded_error_message);
2319 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_CCID','0');
2320 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_DATA','');
2321 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_DESCRIPTIONS','');
2322 wf_engine.setitemattrtext(itemtype,itemkey,'FND_FLEX_NEW','N');
2323 END IF;
2324
2325 result := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2326 END validate_combination;
2327
2328 END PO_WF_PO_CHARGE_ACC;
[Home] [Help]