[Home] [Help]
PACKAGE BODY: APPS.CSTPACWF
Source
1 PACKAGE BODY CSTPACWF AS
2 /* $Header: CSTACWFB.pls 120.1 2006/09/11 08:44:18 arathee noship $ */
3
4 -- FUNCTION
5 -- START_AVG_WF Calls the appropriate Average Costing Workflow process
6 -- based on the accounting line type.
7 --
8 --
9 -- RETURN VALUES
10 -- integer -1 Use default account.
11 -- >0 This is the User defined account.
12 -- 0 Error
13
14 FUNCTION START_AVG_WF(X_TXN_ID IN NUMBER,
15 X_TXN_TYPE_ID IN NUMBER,
16 X_TXN_ACT_ID NUMBER,
17 X_TXN_SRC_TYPE_ID IN NUMBER,
18 X_ORG_ID IN NUMBER,
19 X_ITEM_ID IN NUMBER,
20 X_CE_ID IN NUMBER,
21 X_ALT IN NUMBER,
22 X_CG_ID IN NUMBER,
23 X_RES_ID IN NUMBER,
24 X_ERR_NUM OUT NOCOPY NUMBER,
25 X_ERR_CODE OUT NOCOPY VARCHAR2,
26 X_ERR_MSG OUT NOCOPY VARCHAR2)
27 RETURN integer IS
28
29 L_ITEMTYPE VARCHAR2(30) := 'CSTAVGWF';
30 L_ITEMKEY VARCHAR2(30) := '#SYNCH';
31 L_ACCT_NUM NUMBER := -1;
32 L_WORKFLOW_FUNC_FLAG NUMBER := 0; /* Bug 5513993 */
33
34 BEGIN
35
36 -- SELECT TO_CHAR(FND_FLEX_WORKFLOW_ITEMKEY_S.NEXTVAL) into L_ITEMKEY from dual;
37
38 IF (X_ALT = 1) THEN
39 -- Inventory valuation
40
41 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT1');
42 L_WORKFLOW_FUNC_FLAG := 1;
43
44 ELSIF (X_ALT = 2) THEN
45 -- Account
46
47 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT2');
48 L_WORKFLOW_FUNC_FLAG := 1;
49
50 ELSIF (X_ALT = 3) THEN
51 -- Overhead absorption
52
53 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT3');
54 L_WORKFLOW_FUNC_FLAG := 1;
55
56 ELSIF (X_ALT = 4) THEN
57 -- Resource absorption
58
59 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT4');
60 L_WORKFLOW_FUNC_FLAG := 1;
61
62 ELSIF (X_ALT = 5) THEN
63 -- Receiving inspection
64
65 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT5');
66 L_WORKFLOW_FUNC_FLAG := 1;
67
68 ELSIF (X_ALT = 6) THEN
69 -- PPV or rate variance
70
71 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT6');
72 L_WORKFLOW_FUNC_FLAG := 1;
73
74 ELSIF (X_ALT = 7) THEN
75 -- WIP valuation
76
77 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT7');
78 L_WORKFLOW_FUNC_FLAG := 1;
79
80 ELSIF (X_ALT = 8) THEN
81 -- WIP variance
82
83 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT8');
84 L_WORKFLOW_FUNC_FLAG := 1;
85
86 ELSIF (X_ALT = 9) THEN
87 -- Inter-org payables
88
89 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT9');
90 L_WORKFLOW_FUNC_FLAG := 1;
91
92 ELSIF (X_ALT = 10) THEN
93 -- Inter-org receivables
94
95 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT10');
96 L_WORKFLOW_FUNC_FLAG := 1;
97
98 ELSIF (X_ALT = 11) THEN
99 -- Inter-org transfer credit
100
101 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT11');
102 L_WORKFLOW_FUNC_FLAG := 1;
103
104 ELSIF (X_ALT = 12) THEN
105 -- Inter-org freight charge
106
107 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT12');
108 L_WORKFLOW_FUNC_FLAG := 1;
109
110 ELSIF (X_ALT = 13) THEN
111 -- Average cost variance
112
113 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT13');
114 L_WORKFLOW_FUNC_FLAG := 1;
115
116 ELSIF (X_ALT = 14) THEN
117 -- Intransit inventory
118
119 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT14');
120 L_WORKFLOW_FUNC_FLAG := 1;
121
122 ELSIF (X_ALT = 15) THEN
123 -- Encumbrance reversal
124
125 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT15');
126 L_WORKFLOW_FUNC_FLAG := 1;
127
128 ELSIF (X_ALT = 30) THEN
129 /* Added for Transfer Pricing Project */
130 -- Profit in inventory
131
132 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT30');
133 L_WORKFLOW_FUNC_FLAG := 1;
134
135 ELSIF (X_ALT = 16) THEN
136 -- Accrual
137
138 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT16');
139 L_WORKFLOW_FUNC_FLAG := 1;
140
141 ELSIF (X_ALT = 31) THEN
142 -- Clearing Account
143
144 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT31');
145 L_WORKFLOW_FUNC_FLAG := 1;
146
147 ELSIF (X_ALT = 32) THEN
148 -- Retroactive Price Adjustment
149
150 wf_engine.CreateProcess(L_ITEMTYPE, L_ITEMKEY, 'AVGALT32');
151 L_WORKFLOW_FUNC_FLAG := 1;
152
153 END IF;
154
155 IF ( L_WORKFLOW_FUNC_FLAG = 1 ) THEN /* Bug 5513993 */
156
157 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
158 itemkey => L_ITEMKEY,
159 aname => 'TXN_ID',
160 avalue => X_TXN_ID);
161
162 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
163
164 itemkey => L_ITEMKEY,
165
166 aname => 'TXN_TYPE_ID',
167 avalue => X_TXN_TYPE_ID);
168
169 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
170
171 itemkey => L_ITEMKEY,
172
173 aname => 'TXN_ACT_ID',
174 avalue => X_TXN_ACT_ID);
175
176 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
177
178 itemkey => L_ITEMKEY,
179
180 aname => 'SRC_TYPE_ID',
181 avalue => X_TXN_SRC_TYPE_ID);
182
183 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
184 itemkey => L_ITEMKEY,
185 aname => 'ITEM_ID',
186 avalue => X_ITEM_ID);
187
188 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
189
190 itemkey => L_ITEMKEY,
191
192 aname => 'CG_ID',
193 avalue => X_CG_ID);
194
195 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
196
197 itemkey => L_ITEMKEY,
198
199 aname => 'ORG_ID',
200 avalue => X_ORG_ID);
201
202 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
203
204 itemkey => L_ITEMKEY,
205
206 aname => 'CE_ID',
207 avalue => X_CE_ID);
208
209 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
210
211 itemkey => L_ITEMKEY,
212
213 aname => 'RES_ID',
214 avalue => X_RES_ID);
215
216 wf_engine.StartProcess(L_ITEMTYPE, L_ITEMKEY);
217
218 L_ACCT_NUM := wf_engine.GetItemAttrNumber(itemtype => L_ITEMTYPE,
219
220 itemkey => L_ITEMKEY,
221
222 aname => 'ACCT');
223
224
225 X_ERR_NUM := wf_engine.GetItemAttrNumber(itemtype => L_ITEMTYPE,
226
227 itemkey => L_ITEMKEY,
228
229 aname => 'ERR_NUM');
230
231 X_ERR_CODE := wf_engine.GetItemAttrText(itemtype => L_ITEMTYPE,
232
233 itemkey => L_ITEMKEY,
234
235 aname => 'ERR_CODE');
236
237 X_ERR_MSG := wf_engine.GetItemAttrText(itemtype => L_ITEMTYPE,
238
239 itemkey => L_ITEMKEY,
240
241 aname => 'ERR_MSG');
242 END IF;
243
244 return L_ACCT_NUM;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 X_ERR_NUM := -1;
249 X_ERR_CODE := TO_CHAR(SQLCODE);
250 X_ERR_MSG := 'Error in CSTPACWF.START_AVG_WF' || substrb(SQLERRM,1,150);
251 return 0;
252 RAISE;
253 END; /* START_AVG_WF */
254
255
256 -- WORKFLOW FUNCTION
257 -- GET_AVG_CE Returns the cost element ID.
258 --
259 --
260 -- RETURN VALUES
261 -- integer RESULT OUT variable contains the cost element ID.
262
263
264 PROCEDURE GET_AVG_CE(ITEMTYPE IN VARCHAR2,
265 ITEMKEY IN VARCHAR2,
266 ACTID IN NUMBER,
267 FUNCMODE IN VARCHAR2,
268 RESULT OUT NOCOPY VARCHAR2)
269 IS
270 L_CE NUMBER;
271 BEGIN
272 IF (FUNCMODE = 'RUN') THEN
273 L_CE:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'CE_ID');
274 if (L_CE = 1) then
275 result := 'COMPLETE:1';
276 elsif (L_CE = 2) then
277 result := 'COMPLETE:2';
278 elsif (L_CE = 3) then
279 result := 'COMPLETE:3';
280 elsif (L_CE = 4) then
281 result := 'COMPLETE:4';
282 else
283 result := 'COMPLETE:5';
284 end if;
285
286 RETURN;
287 END IF;
288
289 IF (funcmode = 'CANCEL') THEN
290 result := 'COMPLETE';
291 RETURN;
292 ELSE
293 result := '';
294 RETURN;
295 END IF;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 wf_core.context('CSTPACWF','GET_AVG_CE',itemtype,itemkey,TO_CHAR(actid),funcmode);
300 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
301 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
302 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
303 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_CE' || substrb(SQLERRM,1,150));
304 result := 'COMPLETE:FAILURE';
305 RAISE;
306 END; /* GET_AVG_CE */
307
308
309 -- WORKFLOW FUNCTION
310 -- GET_DEF_ACC Returns -1 for using default accounts.
311 --
312 --
313 -- RETURN VALUES
314 -- integer RESULT OUT variable contains -1.
315
316
317 PROCEDURE GET_DEF_ACC(ITEMTYPE IN VARCHAR2,
318 ITEMKEY IN VARCHAR2,
319 ACTID IN NUMBER,
320 FUNCMODE IN VARCHAR2,
321 RESULT OUT NOCOPY VARCHAR2) IS
322 BEGIN
323
324 IF (FUNCMODE = 'RUN') THEN
325 result := 'COMPLETE:-1';
326
327 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
328
329 RETURN;
330 END IF;
331
332 IF (funcmode = 'CANCEL') THEN
333 result := 'COMPLETE';
334 RETURN;
335 ELSE
336 result := '';
337 RETURN;
338 END IF;
339 EXCEPTION
340 WHEN OTHERS THEN
341 wf_core.context('CSTPACWF','GET_DEF_ACC',itemtype,itemkey,TO_CHAR(actid),funcmode);
342 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
343 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
344 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
345 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_DEF_ACC' || substrb(SQLERRM,1,150));
346 result := 'COMPLETE:FAILURE';
347 RAISE;
348 END; /* GET_DEF_ACC */
349
350
351 -- WORKFLOW FUNCTION
352 -- GET_AVG_MTL_PLA Returns the Product line Material Account.
353 --
354 --
355 -- RETURN VALUES
356 -- integer RESULT OUT variable contains SUCCESS or FAILURE.
357
358
359 PROCEDURE GET_AVG_MTL_PLA(ITEMTYPE IN VARCHAR2,
360 ITEMKEY IN VARCHAR2,
361 ACTID IN NUMBER,
362 FUNCMODE IN VARCHAR2,
363 RESULT OUT NOCOPY VARCHAR2)
364 IS
365 L_ACCOUNT number := -1;
366 L_ORG_ID number;
367 L_ITEM_ID number;
368 L_CG_ID number;
369 BEGIN
370 IF (FUNCMODE = 'RUN') THEN
371
372 L_ORG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
373 L_ITEM_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ITEM_ID');
374 L_CG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CG_ID');
375
376
377 SELECT nvl(mca.material_account,-1) into L_ACCOUNT
378 FROM MTL_CATEGORY_ACCOUNTS mca,
379 MTL_ITEM_CATEGORIES mic,
380 MTL_DEFAULT_CATEGORY_SETS mdcs
381 WHERE mdcs.functional_area_id = 8
382 AND mdcs.category_set_id = mic.category_set_id
383 AND mca.category_id = mic.category_id
384 AND mca.category_set_id = mic.category_set_id
385 AND mca.organization_id = mic.organization_id
386 AND mca.cost_group_id = L_CG_ID
387 AND mic.organization_id = L_ORG_ID
388 AND mic.inventory_item_id = L_ITEM_ID;
389
390 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',L_ACCOUNT);
391
392 result := 'COMPLETE:SUCCESS';
393 RETURN;
394 END IF;
395
396 IF (funcmode = 'CANCEL') THEN
397 result := wf_engine.eng_completed;
398 RETURN;
399
400 ELSE
401
402 result := '';
403
404 RETURN;
405
406 END IF;
407
408 EXCEPTION
409
410 WHEN NO_DATA_FOUND THEN
411 L_ACCOUNT := -1;
412 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
413 result := 'COMPLETE:SUCCESS';
414 RETURN;
415
416
417 WHEN OTHERS THEN
418 wf_core.context('CSTPACWF','GET_AVG_MTL_PLA',itemtype,itemkey,TO_CHAR(actid),funcmode);
419 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
420 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
421 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
422 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_MTL_PLA' || substrb(SQLERRM,1,150));
423 result := 'COMPLETE:FAILURE';
424 RAISE;
425 END; /* GET_AVG_MTL_PLA */
426
427
428 -- WORKFLOW FUNCTION
429 -- GET_AVG_MO_PLA Returns the Product line Material Overhead Account.
430 --
431 --
432 -- RETURN VALUES
433 -- integer RESULT OUT variable contains SUCCESS or FAILURE.
434
435
436 PROCEDURE GET_AVG_MO_PLA(ITEMTYPE IN VARCHAR2,
437 ITEMKEY IN VARCHAR2,
438 ACTID IN NUMBER,
439 FUNCMODE IN VARCHAR2,
440 RESULT OUT NOCOPY VARCHAR2)
441 IS
442 L_ACCOUNT number := -1;
443 L_ORG_ID number;
444 L_ITEM_ID number;
445 L_CG_ID number;
446 BEGIN
447 IF (FUNCMODE = 'RUN') THEN
448
449 L_ORG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
450 L_ITEM_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ITEM_ID');
454 SELECT nvl(mca.material_overhead_account,-1) into L_ACCOUNT
451 L_CG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CG_ID');
452
453
455 FROM MTL_CATEGORY_ACCOUNTS mca,
456 MTL_ITEM_CATEGORIES mic,
457 MTL_DEFAULT_CATEGORY_SETS mdcs
458 WHERE mdcs.functional_area_id = 8
459 AND mdcs.category_set_id = mic.category_set_id
460 AND mca.category_id = mic.category_id
461 AND mca.category_set_id = mic.category_set_id
462 AND mca.organization_id = mic.organization_id
463 AND mca.cost_group_id = L_CG_ID
464 AND mic.organization_id = L_ORG_ID
465 AND mic.inventory_item_id = L_ITEM_ID;
466
467 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',L_ACCOUNT);
468
469 result := 'COMPLETE:SUCCESS';
470 RETURN;
471 END IF;
472
473 IF (funcmode = 'CANCEL') THEN
474 result := wf_engine.eng_completed;
475 RETURN;
476
477 ELSE
478
479 result := '';
480
481 RETURN;
482
483 END IF;
484
485 EXCEPTION
486
487 WHEN NO_DATA_FOUND THEN
488 L_ACCOUNT := -1;
489 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
490 result := 'COMPLETE:SUCCESS';
491 RETURN;
492
493
494 WHEN OTHERS THEN
495 wf_core.context('CSTPACWF','GET_AVG_MO_PLA',itemtype,itemkey,TO_CHAR(actid),funcmode);
496 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
497 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
498 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
499 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_MO_PLA' || substrb(SQLERRM,1,150));
500 result := 'COMPLETE:FAILURE';
501 RAISE;
502 END; /* GET_AVG_MO_PLA */
503
504
505 -- WORKFLOW FUNCTION
506 -- GET_AVG_RES_PLA Returns the Product line Resource Account.
507 --
508 --
509 -- RETURN VALUES
510 -- integer RESULT OUT variable contains SUCCESS or FAILURE.
511
512
513 PROCEDURE GET_AVG_RES_PLA(ITEMTYPE IN VARCHAR2,
514 ITEMKEY IN VARCHAR2,
515 ACTID IN NUMBER,
516 FUNCMODE IN VARCHAR2,
517 RESULT OUT NOCOPY VARCHAR2)
518 IS
519 L_ACCOUNT number := -1;
520 L_ORG_ID number;
521 L_ITEM_ID number;
522 L_CG_ID number;
523 BEGIN
524 IF (FUNCMODE = 'RUN') THEN
525
526 L_ORG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
527 L_ITEM_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ITEM_ID');
528 L_CG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CG_ID');
529
530
531 SELECT nvl(mca.resource_account,-1) into L_ACCOUNT
532 FROM MTL_CATEGORY_ACCOUNTS mca,
533 MTL_ITEM_CATEGORIES mic,
534 MTL_DEFAULT_CATEGORY_SETS mdcs
535 WHERE mdcs.functional_area_id = 8
536 AND mdcs.category_set_id = mic.category_set_id
537 AND mca.category_id = mic.category_id
538 AND mca.category_set_id = mic.category_set_id
539 AND mca.organization_id = mic.organization_id
540 AND mca.cost_group_id = L_CG_ID
541 AND mic.organization_id = L_ORG_ID
542 AND mic.inventory_item_id = L_ITEM_ID;
543
544 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',L_ACCOUNT);
545
546 result := 'COMPLETE:SUCCESS';
547 RETURN;
548 END IF;
549
550 IF (funcmode = 'CANCEL') THEN
551 result := wf_engine.eng_completed;
552 RETURN;
553
554 ELSE
555
556 result := '';
557
558 RETURN;
559
560 END IF;
561
562 EXCEPTION
563
564 WHEN NO_DATA_FOUND THEN
565 L_ACCOUNT := -1;
566 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
567 result := 'COMPLETE:SUCCESS';
568 RETURN;
569
570
571 WHEN OTHERS THEN
572 wf_core.context('CSTPACWF','GET_AVG_RES_PLA',itemtype,itemkey,TO_CHAR(actid),funcmode);
573 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
574 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
575 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
576 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_RES_PLA' || substrb(SQLERRM,1,150));
577 result := 'COMPLETE:FAILURE';
578 RAISE;
579 END; /* GET_AVG_RES_PLA */
580
581
582 -- WORKFLOW FUNCTION
583 -- GET_AVG_OSP_PLA Returns the Product line Outside Processing Account.
584 --
585 --
586 -- RETURN VALUES
587 -- integer RESULT OUT variable contains SUCCESS or FAILURE.
588
589
590 PROCEDURE GET_AVG_OSP_PLA(ITEMTYPE IN VARCHAR2,
591 ITEMKEY IN VARCHAR2,
592 ACTID IN NUMBER,
593 FUNCMODE IN VARCHAR2,
594 RESULT OUT NOCOPY VARCHAR2)
595 IS
596 L_ACCOUNT number := -1;
597 L_ORG_ID number;
598 L_ITEM_ID number;
599 L_CG_ID number;
600 BEGIN
604 L_ITEM_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ITEM_ID');
601 IF (FUNCMODE = 'RUN') THEN
602
603 L_ORG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
605 L_CG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CG_ID');
606
607 SELECT nvl(mca.outside_processing_account,-1) into L_ACCOUNT
608 FROM MTL_CATEGORY_ACCOUNTS mca,
609 MTL_ITEM_CATEGORIES mic,
610 MTL_DEFAULT_CATEGORY_SETS mdcs
611 WHERE mdcs.functional_area_id = 8
612 AND mdcs.category_set_id = mic.category_set_id
613 AND mca.category_id = mic.category_id
614 AND mca.category_set_id = mic.category_set_id
615 AND mca.organization_id = mic.organization_id
616 AND mca.cost_group_id = L_CG_ID
617 AND mic.organization_id = L_ORG_ID
618 AND mic.inventory_item_id = L_ITEM_ID;
619
620 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',L_ACCOUNT);
621
622 result := 'COMPLETE:SUCCESS';
623 RETURN;
624 END IF;
625
626 IF (funcmode = 'CANCEL') THEN
627 result := wf_engine.eng_completed;
628 RETURN;
629
630 ELSE
631
632 result := '';
633
634 RETURN;
635
636 END IF;
637
638 EXCEPTION
639
640 WHEN NO_DATA_FOUND THEN
641 L_ACCOUNT := -1;
642 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
643 result := 'COMPLETE:SUCCESS';
644 RETURN;
645
646
647 WHEN OTHERS THEN
648 wf_core.context('CSTPACWF','GET_AVG_OSP_PLA',itemtype,itemkey,TO_CHAR(actid),funcmode);
649 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
650 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
651 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
652 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_OSP_PLA' || substrb(SQLERRM,1,150));
653 result := 'COMPLETE:FAILURE';
654 RAISE;
655 END; /* GET_AVG_OSP_PLA */
656
657
658 -- WORKFLOW FUNCTION
659 -- GET_AVG_OVH_PLA Returns the Product line Overhead Account.
660 --
661 --
662 -- RETURN VALUES
663 -- integer RESULT OUT variable contains SUCCESS or FAILURE.
664
665
666 PROCEDURE GET_AVG_OVH_PLA(ITEMTYPE IN VARCHAR2,
667 ITEMKEY IN VARCHAR2,
668 ACTID IN NUMBER,
669 FUNCMODE IN VARCHAR2,
670 RESULT OUT NOCOPY VARCHAR2)
671 IS
672 L_ACCOUNT number := -1;
673 L_ORG_ID number;
674 L_ITEM_ID number;
675 L_CG_ID number;
676 BEGIN
677 IF (FUNCMODE = 'RUN') THEN
678
679 L_ORG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
680 L_ITEM_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ITEM_ID');
681 L_CG_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'CG_ID');
682
683
684 SELECT nvl(mca.overhead_account,-1) into L_ACCOUNT
685 FROM MTL_CATEGORY_ACCOUNTS mca,
686 MTL_ITEM_CATEGORIES mic,
687 MTL_DEFAULT_CATEGORY_SETS mdcs
688 WHERE mdcs.functional_area_id = 8
689 AND mdcs.category_set_id = mic.category_set_id
690 AND mca.category_id = mic.category_id
691 AND mca.category_set_id = mic.category_set_id
692 AND mca.organization_id = mic.organization_id
693 AND mca.cost_group_id = L_CG_ID
694 AND mic.organization_id = L_ORG_ID
695 AND mic.inventory_item_id = L_ITEM_ID;
696
697 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',L_ACCOUNT);
698
699 result := 'COMPLETE:SUCCESS';
700 RETURN;
701 END IF;
702
703 IF (funcmode = 'CANCEL') THEN
704 result := wf_engine.eng_completed;
705 RETURN;
706
707 ELSE
708
709 result := '';
710
711 RETURN;
712
713 END IF;
714
715 EXCEPTION
716
717 WHEN NO_DATA_FOUND THEN
718 L_ACCOUNT := -1;
719 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',-1);
720 result := 'COMPLETE:SUCCESS';
721 RETURN;
722
723
724 WHEN OTHERS THEN
725 wf_core.context('CSTPACWF','GET_AVG_OVH_PLA',itemtype,itemkey,TO_CHAR(actid),funcmode);
726 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ACCT',0);
727 wf_engine.SetItemAttrNumber(itemtype,itemkey,'ERR_NUM',-1);
728 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_CODE',TO_CHAR(SQLCODE));
729 wf_engine.SetItemAttrText(itemtype,itemkey,'ERR_MSG','Error in CSTPACWF.GET_AVG_OVH_PLA' || substrb(SQLERRM,1,150));
730 result := 'COMPLETE:FAILURE';
731 RAISE;
732 END; /* GET_AVG_OVH_PLA */
733
734 END CSTPACWF;