1 PACKAGE BODY PO_WF_PO_VARIANCE_ACC AS
2 /* $Header: POXWPVAB.pls 120.0 2005/06/01 19:17:58 appldev noship $ */
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 | POXWPVAB.pls
10 |
11 | DESCRIPTION
12 | PL/SQL body for package: PO_WF_PO_VARIANCE_ACC
13 |
14 | NOTES
15 | MODIFIED IMRAN ALI (09/08/97) - Created
16 *=====================================================================*/
17
18
19 --
20 -- Check Destination Type
21 --
22 procedure destination_type ( itemtype in varchar2,
23 itemkey in varchar2,
24 actid in number,
25 funcmode in varchar2,
26 result out NOCOPY varchar2 )
27 is
28 x_progress varchar2(100);
29 x_destination_type varchar2(25);
30 begin
31
32 x_progress := 'PO_WF_PO_VARIANCE_ACC.destination_type: 01';
33 IF (g_po_wf_debug = 'Y') THEN
34 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
35 END IF;
36
37
38 -- Do nothing in cancel or timeout mode
39 --
40 if (funcmode <> wf_engine.eng_run) then
41
42 result := wf_engine.eng_null;
43 return;
44
45 end if;
46
47 x_destination_type := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,
48 itemkey => itemkey,
49 aname => 'DESTINATION_TYPE_CODE');
50 if x_destination_type = 'EXPENSE' then
51 result := 'COMPLETE:EXPENSE';
52 elsif x_destination_type = 'INVENTORY' then
53 result := 'COMPLETE:INVENTORY';
54 elsif x_destination_type = 'SHOP FLOOR' then
55 result := 'COMPLETE:SHOP_FLOOR';
56 end if;
57
58 return;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 wf_core.context('PO_WF_PO_VARIANCE_ACC','destination_type',x_progress);
63 raise;
64 end destination_type;
65
66 -- * ****************************************************************************** *
67
68 --
69 -- VA_from_org
70 --
71 procedure VA_from_org ( itemtype in varchar2,
72 itemkey in varchar2,
73 actid in number,
74 funcmode in varchar2,
75 result out NOCOPY varchar2 )
76 is
77 x_progress varchar2(100);
78 x_account number;
79 x_dest_org_id number;
80
81 x_destination_type varchar2(25); -- Bug 4008665
82
83 --Bug# 1902716 togeorge 07/25/2001
84 --EAM: if item id is null get the accrual account from po_system_parameters
85 -- (one time items can be delivered to shopfloor with eam)
86 x_item_id number;
87 begin
88
89 x_progress := 'PO_WF_PO_VARIANCE_ACC.VA_from_org : 01';
90 IF (g_po_wf_debug = 'Y') THEN
91 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
92 END IF;
93
94
95 -- Do nothing in cancel or timeout mode
96 --
97 if (funcmode <> wf_engine.eng_run) then
98
99 result := wf_engine.eng_null;
100 return;
101
102 end if;
103
104 x_dest_org_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
105 itemkey => itemkey,
106 aname => 'DESTINATION_ORGANIZATION_ID');
107 --Bug# 1902716 togeorge 07/25/2001
108 --EAM: if item id is null get the accrual account from po_system_parameters
109 -- (one time items can be delivered to shopfloor with eam)
110 x_item_id := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
111 itemkey => itemkey,
112 aname => 'ITEM_ID');
113
114 -- Bug 4008665: Get the destination type code.
115 x_destination_type := wf_engine.GetItemAttrText ( itemtype => itemtype, itemkey => itemkey, aname => 'DESTINATION_TYPE_CODE');
116
117 begin
118 --Bug# 1902716 togeorge 07/25/2001
119 --EAM: if item id is null get the accrual account from po_system_parameters
120 -- (one time items can be delivered to shopfloor with eam)
121
122 -- Bug 4008665 START
123 -- In the case of one time expense items that are shipped to
124 -- Shop Floor, the Variance Account in Distributions should come from
125 -- mtl_paramters.
126 IF ( (x_item_id is not null) OR
127 ((x_item_id is null) and (x_destination_type='SHOP FLOOR')) -- condition for EAM
128 ) then
129 -- Bug 4008665 END
130 select invoice_price_var_account into x_account
131 from mtl_parameters
132 where organization_id = x_dest_org_id;
133 ELSE --treat like an expense item, directly copy the charge account here.
134 x_account:=po_wf_util_pkg.GetItemAttrNumber (
135 itemtype => itemtype,
136 itemkey => itemkey,
137 aname => 'CODE_COMBINATION_ID');
138 END IF;
139 --
140 po_wf_util_pkg.SetItemAttrNumber ( itemtype=>itemtype,
141 itemkey=>itemkey,
142 aname=>'TEMP_ACCOUNT_ID',
143 avalue=>x_account );
144
145 exception
146 when no_data_found then
147 null;
148 end;
149
150 if (x_account IS NOT NULL) then
151 result := 'COMPLETE:SUCCESS';
152 else
153 result := 'COMPLETE:FAILURE';
154 end if;
155
156 return;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 wf_core.context('PO_WF_PO_VARIANCE_ACC','VA_from_org',x_progress);
161 raise;
162 end VA_from_org;
163
164 --
165
166 -- * ****************************************************************************** *
167
168 --
169 -- is_po_project_related
170 --
171 -- This is a dummy function that should be replaced by the customized function
172 -- activity in the workflow that return TRUE or FALSE based on whether you want to
173 -- use the default PO expense variance account generation rules or use "CUSTOMIZED"
174 -- project accounting rules.
175
176 procedure is_po_project_related ( itemtype in varchar2,
177 itemkey in varchar2,
178 actid in number,
179 funcmode in varchar2,
180 result out NOCOPY varchar2 )
181 is
182 begin
183
184 result := 'COMPLETE:F';
185 return;
186
187 end is_po_project_related;
188
189 --
190
191 procedure get_charge_account ( itemtype in varchar2,
192 itemkey in varchar2,
193 actid in number,
194 funcmode in varchar2,
195 result out NOCOPY varchar2 )
196 is
197 x_ccid NUMBER;
198 x_progress varchar2(100);
199 begin
200
201 -- get code_combination_id from item attribute
202
203 x_ccid := po_wf_util_pkg.GetItemAttrNumber ( itemtype => itemtype,
204 itemkey => itemkey,
205 aname => 'CODE_COMBINATION_ID');
206
207 if (x_ccid IS NOT NULL) then
208
209 po_wf_util_pkg.SetItemAttrNumber ( itemtype=>itemtype,
210 itemkey=>itemkey,
211 aname=>'TEMP_ACCOUNT_ID',
212 avalue=>x_ccid );
213 result := 'COMPLETE:SUCCESS';
214 else
215 result := 'COMPLETE:FAILURE';
216 end if;
217
218 return;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 wf_core.context('PO_WF_PO_VARIANCE_ACC','get_charge_account',x_progress);
223 raise;
224 end get_charge_account;
225
226
227 --
228
229 --< Shared Proc FPJ Start >
230
231 ---------------------------------------------------------------------------
232 --Start of Comments
233 --Name: is_dest_variance_acc_null
234 --Pre-reqs:
235 -- None.
236 --Modifies:
237 -- Item Attribute: TEMP_ACCOUNT_ID
238 --Locks:
239 -- None.
240 --Function:
241 -- Checks if the attribute DEST_VARIANCE_ACCOUNT_ID is NULL or not.
242 -- If it is NULL, it returns 'N'.
243 -- If it is not NULL, it copies the value in DEST_VARIANCE_ACCOUNT_ID to
244 -- TEMP_ACCOUNT_ID and returns 'Y'.
245 --Parameters:
246 --IN:
247 -- Standard workflow function parameters
248 --OUT:
249 -- Standard workflow function result parameter
250 --Testing:
251 --End of Comments
252 ---------------------------------------------------------------------------
253 PROCEDURE is_dest_variance_acc_null
254 (itemtype IN VARCHAR2,
255 itemkey IN VARCHAR2,
256 actid IN NUMBER,
257 funcmode IN VARCHAR2,
258 result OUT NOCOPY VARCHAR2)
259 IS
260 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
261 l_dest_variance_account_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
262 BEGIN
263 l_progress := '010';
264
265 -- Do nothing in cancel or timeout mode
266 IF (funcmode <> WF_ENGINE.eng_run) THEN
267 result := WF_ENGINE.eng_null;
268 RETURN;
269 END IF;
270
271 l_progress := '020';
272 l_dest_variance_account_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
273 itemtype => itemtype,
274 itemkey => itemkey,
275 aname => 'DEST_VARIANCE_ACCOUNT_ID');
276
277 IF (g_po_wf_debug = 'Y') THEN
278 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
279 'PO_WF_PO_VARIANCE_ACC.is_dest_variance_acc_null '||
280 'l_dest_variance_account_id='||l_dest_variance_account_id);
281 END IF;
282
283
284 l_progress := '030';
285 IF l_dest_variance_account_id IS NULL OR
286 l_dest_variance_account_id = 0 OR
287 l_dest_variance_account_id = -1 THEN
288 result := WF_ENGINE.eng_completed || ':Y';
289 ELSE
290 -- If the Dest Variance Account is not null (only one case -- autocreate),
291 -- then copy it into the TEMP_ACCOUNT_ID.
292 l_progress := '040';
293 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
294 itemkey => itemkey,
295 aname => 'TEMP_ACCOUNT_ID',
296 avalue => l_dest_variance_account_id);
297 l_progress := '050';
298 result := WF_ENGINE.eng_completed || ':N';
299 END IF;
300
301 IF (g_po_wf_debug = 'Y') THEN
302 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
303 'PO_WF_PO_VARIANCE_ACC.is_dest_variance_acc_null result= '||
304 result);
305 END IF;
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF (g_po_wf_debug = 'Y') THEN
309 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
310 'PO_WF_PO_VARIANCE_ACC.is_dest_variance_acc_null EXCEPTION at '||
311 l_progress);
312 END IF;
313 WF_CORE.context('PO_WF_PO_VARIANCE_ACC', 'is_dest_variance_acc_null',
314 l_progress);
315 RAISE;
316 END is_dest_variance_acc_null;
317
318 ---------------------------------------------------------------------------
319 --Start of Comments
320 --Name: get_destination_charge_account
321 --Pre-reqs:
322 -- None.
323 --Modifies:
324 -- Item Attribute: TEMP_ACCOUNT_ID
325 --Locks:
326 -- None.
327 --Function:
328 -- Copies the values in the attribute DEST_VARIANCE_ACCOUNT_ID to
329 -- the attribute TEMP_ACCOUNT_ID.
330 -- If the value in DEST_VARIANCE_ACCOUNT_ID is NULL, then it retruns a
331 -- FAILURE, else it returns a SUCCESS.
332 --
333 --Parameters:
334 --IN:
335 -- Standard workflow function parameters
336 --OUT:
337 -- Standard workflow function result parameter
338 --Testing:
339 --End of Comments
340 ---------------------------------------------------------------------------
341 PROCEDURE get_destination_charge_account
342 (itemtype IN VARCHAR2,
343 itemkey IN VARCHAR2,
344 actid IN NUMBER,
345 funcmode IN VARCHAR2,
346 result OUT NOCOPY VARCHAR2)
347 IS
348 l_progress WF_ITEM_ACTIVITY_STATUSES.error_stack%TYPE; -- VARCHAR2(4000)
349 l_dest_charge_account_id NUMBER;
350 BEGIN
351 l_progress := '010';
352
353 -- Do nothing in cancel or timeout mode
354 IF (funcmode <> WF_ENGINE.eng_run) THEN
355 result := WF_ENGINE.eng_null;
356 RETURN;
357 END IF;
358
359 l_progress := '020';
360 l_dest_charge_account_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
361 itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'DEST_CHARGE_ACCOUNT_ID');
364
365 IF (g_po_wf_debug = 'Y') THEN
366 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
367 'PO_WF_PO_VARIANCE_ACC.get_destination_charge_account '||
368 'l_dest_charge_account_id='||l_dest_charge_account_id);
369 END IF;
370
371 l_progress := '030';
372 IF l_dest_charge_account_id IS NULL OR
373 l_dest_charge_account_id = 0 OR
374 l_dest_charge_account_id = -1 THEN
375 result := WF_ENGINE.eng_completed || ':FAILURE';
376 ELSE
377 l_progress := '040';
378 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'TEMP_ACCOUNT_ID',
381 avalue => l_dest_charge_account_id);
382 l_progress := '050';
383 result := WF_ENGINE.eng_completed || ':SUCCESS';
384 END IF;
385
386 IF (g_po_wf_debug = 'Y') THEN
387 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
388 'PO_WF_PO_VARIANCE_ACC.get_destination_charge_account '||
389 'result='||result);
390 END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF (g_po_wf_debug = 'Y') THEN
394 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
395 'PO_WF_PO_VARIANCE_ACC.get_destination_charge_account EXCEPTION at '||
396 l_progress);
397 END IF;
398 WF_CORE.context('PO_WF_PO_VARIANCE_ACC', 'get_destination_charge_account',
399 l_progress);
400 RAISE;
401 END get_destination_charge_account;
402
403 ---------------------------------------------------------------------------
404 --Start of Comments
405 --Name: dest_VA_from_org
406 --Pre-reqs:
407 -- None
408 --Modifies:
409 -- Item Attribute: TEMP_ACCOUNT_ID
410 --Locks:
411 -- None
412 --Function:
413 -- Retrieves the Destination Variance Account from the Organization level
414 -- if the item is not a one-time item; otherwise, treat the item as an Expense
415 -- item and copy the Destination Charge Account
416 --Parameters:
417 --IN:
418 -- Standard workflow function parameters
419 --OUT:
420 -- Standard workflow function result parameter
421 --Testing:
422 --End of Comments
423 ---------------------------------------------------------------------------
424 PROCEDURE dest_VA_from_org ( itemtype IN VARCHAR2,
425 itemkey IN VARCHAR2,
426 actid IN NUMBER,
427 funcmode IN VARCHAR2,
428 result OUT NOCOPY VARCHAR2)
429 IS
430 x_progress VARCHAR2(100);
431 x_account NUMBER;
432 x_dest_org_id NUMBER;
433 x_item_id NUMBER;
434
435 BEGIN
436
437 x_progress := 'PO_WF_PO_VARIANCE_ACC.dest_VA_from_org : 01';
438
439 IF (g_po_wf_debug = 'Y') THEN
440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
441 END IF;
442
443 -- Do nothing in cancel or timeout mode
444 --
445 IF (funcmode <> wf_engine.eng_run) THEN
446 result := wf_engine.eng_null;
447 RETURN;
448 END IF;
449
450 x_dest_org_id := po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
451 itemkey => itemkey,
452 aname => 'DESTINATION_ORGANIZATION_ID');
453
454 --EAM: if item id is null get the accrual account from po_system_parameters
455 -- (one time items can be delivered to shopfloor with eam)
456 x_item_id := po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
457 itemkey => itemkey,
458 aname => 'ITEM_ID');
459
460 IF x_item_id IS NOT NULL THEN
461 BEGIN
462 select invoice_price_var_account into x_account
463 from mtl_parameters
464 where organization_id = x_dest_org_id;
465 EXCEPTION
466 WHEN no_data_found THEN
467 NULL;
468 END;
469 ELSE --treat like an expense item, directly copy the dest charge account here
470 x_account := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
471 itemkey => itemkey,
472 aname => 'DEST_CHARGE_ACCOUNT_ID');
473
474 x_progress := 'PO_WF_PO_VARIANCE_ACC.dest_VA_from_org : 02';
475
476 IF (g_po_wf_debug = 'Y') THEN
477 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
478 END IF;
479 END IF;
480
481 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
482 itemkey => itemkey,
483 aname => 'TEMP_ACCOUNT_ID',
484 avalue => x_account);
485
486 IF (x_account IS NOT NULL) THEN
487 result := 'COMPLETE:SUCCESS';
488 ELSE
489 result := 'COMPLETE:FAILURE';
490 END IF;
491
492 RETURN;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 wf_core.context('PO_WF_PO_VARIANCE_ACC','dest_VA_from_org',x_progress);
497 raise;
498 END dest_VA_from_org;
499
500 --< Shared Proc FPJ End >
501
502 end PO_WF_PO_VARIANCE_ACC;
[Home] [Help]