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