1 PACKAGE BODY PO_INTG_DOCUMENT_FUNDS_GRP AS
2 /* $Header: POXGFCKB.pls 120.3 2006/07/12 13:07:28 vsanjay noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'PO_INTG_DOCUMENT_FUNDS_GRP';
5
6 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.' ;
7
8 -- Read the profile option that enables/disables the debug log
9 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10
11
12 -------------------------------------------------------------------------------
13 --Start of Comments
14 --Name: reinstate_po_encumbrance
15 --Pre-reqs:
16 -- None.
17 --Modifies:
18 -- None.
19 --Locks:
20 -- None.
21 --Function:
22 -- It should created GL reversal entries if the distribution is
23 -- either unreserved or the its shipment is Finally closed.
24 --Parameters:
25 --IN:
26 --p_api_version
27 -- Apps API Std - To control correct version in use
28 --p_commit
29 -- Apps API Std - Should data be commited?
30 --p_init_msg_list
31 -- Apps API Std - Initialize the message list?
32 --p_validation_level
33 -- Apps API Std - Level of validations to be done
34 --p_distribution_id
35 -- po_distribution_id
36 --p_invoice_id
37 -- po_invoice_id
38 --p_encumbrance_amt
39 -- encumbrance amount in functional currency for which AP will reinstate the
40 -- PO encumbrance on Invoice cancellation. AP should take care of the
41 -- overbilled case and any variances.
42 -- IF (p_encumbrance_amt >0) THEN
43 -- Invoice Cancellation, PO API does Cr (AP is doing -Cr)
44 -- ELSE
45 -- Memo Cancellation, PO API does Dr (AP is doing -Dr)
46 --p_qty_cancelled
47 -- Invoice qty cancelled for the PO distribution. This should be PO UOM
48 -- p_qty_cancelled is -ve for Invoice Cancellation
49 -- +ve for Credit Memo Cancellation
50 --p_budget_account_id
51 -- Budget account id - account on which the AP does PO reversal
52 --p_gl_date
53 -- Valid open Date on which AP will reinstate PO encumbrance on Invoice
54 -- cancellation. We want the Dr and Cr to go in the same period.
55 --p_period_name
56 -- period name
57 --p_period_year
58 -- period year
59 --p_period_num
60 -- period num
61 --p_quarter_num
62 -- quarter num
63 --p_tax_line_flag -- Bug 3480949
64 -- Set depending upon which values of p_encumbered_amt AP calls the API with.
65 -- g_parameter_NO - the original amounts before tax applied
66 -- g_parameter_YES - the tax on the original amounts only
67 -- Default NULL, which will be assumed to be g_parameter_NO
68 -- Check Priavate API comments for more usage notes.
69 --OUT:
70 --x_packet_id
71 -- GL PACKET ID, if gl entries are made otherwise null
72 --x_return_status
73 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
74 -- FND_API.G_RET_STS_ERROR if validation fails
75 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
76 --Testing:
77 --
78 --End of Comments
79 -------------------------------------------------------------------------------
80 PROCEDURE reinstate_po_encumbrance(
81 p_api_version IN NUMBER,
82 p_commit IN VARCHAR2 default FND_API.G_FALSE,
83 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
84 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
85 p_distribution_id IN NUMBER,
86 p_invoice_id IN NUMBER,
87 p_encumbrance_amt IN NUMBER,
88 p_qty_cancelled IN NUMBER,
89 p_budget_account_id IN NUMBER,
90 p_gl_date IN DATE,
91 p_period_name IN VARCHAR2,
92 p_period_year IN VARCHAR2,
93 p_period_num IN VARCHAR2,
94 p_quarter_num IN VARCHAR2,
95 p_tax_line_flag IN VARCHAR2 default NULL, -- Bug 3480949
96 x_packet_id OUT NOCOPY NUMBER,
97 x_return_status OUT NOCOPY VARCHAR2
98 ) is
99
100 l_api_name CONSTANT varchar2(30) := 'REINSTATE_PO_ENCUMBRANCE';
101 l_api_version CONSTANT NUMBER := 1.0;
102 l_progress VARCHAR2(3);
103
104 BEGIN
105
106 SAVEPOINT REINSTATE_PO_ENCUMBRANCE_SP;
107
108 l_progress := '000';
109
110 IF g_fnd_debug = 'Y' THEN
111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
112 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
113 || l_progress,'Invoked');
114 END IF;
115 END IF;
116
117
118 PO_DOCUMENT_FUNDS_GRP.reinstate_po_encumbrance(
119 p_api_version => p_api_version
120 , p_commit => p_commit
121 , p_init_msg_list => p_init_msg_list
122 , p_validation_level => p_validation_level
123 , p_distribution_id => p_distribution_id
124 , p_invoice_id => p_invoice_id
125 , p_encumbrance_amt => p_encumbrance_amt
126 , p_qty_cancelled => p_qty_cancelled
127 , p_budget_account_id => p_budget_account_id
128 , p_gl_date => p_gl_date
129 , p_period_name => p_period_name
130 , p_period_year => p_period_year
131 , p_period_num => p_period_num
132 , p_quarter_num => p_quarter_num
133 , p_tax_line_flag => p_tax_line_flag -- Bug 3480949
134 , x_packet_id => x_packet_id
135 , x_return_status => x_return_status
136 );
137
138 l_progress := '999';
139 IF g_fnd_debug = 'Y' THEN
140 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
141 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
142 || l_progress, 'End');
143 END IF;
144 END IF;
145
146 EXCEPTION
147
148 WHEN OTHERS THEN
149 ROLLBACK TO REINSTATE_PO_ENCUMBRANCE_SP;
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
151 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
152 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
153 END IF;
154
155 IF (g_fnd_debug = 'Y') THEN
156 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
157 FND_LOG.string(FND_LOG.level_unexpected, c_log_head ||
158 l_api_name || '.others_exception', 'EXCEPTION: '
159 || 'Location is '|| l_progress || ' SQL CODE is '||sqlcode);
160 END IF;
161
162 END IF;
163
164 END reinstate_po_encumbrance;
165
166
167
168 -------------------------------------------------------------------------------
169 --Start of Comments
170 --Name: get_active_encumbrance_amount
171 --Pre-reqs:
172 -- Organization context must be set
173 --Modifies:
174 -- None.
175 --Locks:
176 -- None.
177 --Function:
178 -- Calculates the active encumbrance on a given Req/PO distribution. The
179 -- active encumbrance is the encumbrance originally reserved minus any
180 -- encumbrance that has already been moved in actuals by CST or AP
181 --Parameters:
182 --IN:
183 --p_api_version
184 -- Apps API Std - To control correct version in use
185 --p_init_msg_list
186 -- Apps API Std - Initialize the message list?
187 --p_validation_level
188 -- Apps API Std - Level of validations to be done
189 --p_doc_type
190 -- Document type. Use the PO_INTG_DOCUMENT_FUNDS_GRP package variables:
191 -- g_doc_type_REQUISITION, (Note: INTERNAL Reqs are not supported)
192 -- g_doc_type_PA,
193 -- g_doc_type_PO,
194 -- g_doc_type_RELEASE (optional: behaves same as g_doc_type_PO)
195 -- NOTE: API does not current support Internal Requisitions
196 --p_distribution_id
197 -- Unique id of row from either po_req_distributions_all or
198 -- po_distributions_all table, depending on p_doc_type
199 --OUT:
200 --x_return_status
201 -- FND_API.G_RET_STS_SUCCESS if validation succeeds
202 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
203 --x_active_enc_amount
204 -- The current active encumbrance on the distribution identified
205 -- by p_distribution_id
206 --Testing:
207 --
208 --End of Comments
209 -------------------------------------------------------------------------------
210 PROCEDURE get_active_encumbrance_amount(
211 p_api_version IN NUMBER
212 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
213 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
214 , x_return_status OUT NOCOPY VARCHAR2
215 , p_doc_type IN VARCHAR2
216 , p_distribution_id IN NUMBER
217 , x_active_enc_amount OUT NOCOPY NUMBER
218 )
219 IS
220
221 l_api_name CONSTANT varchar2(30) := 'GET_ACTIVE_ENCUMBRANCE_AMOUNT';
222 l_api_version CONSTANT NUMBER := 1.0;
223 l_progress VARCHAR2(3) := '000';
224
225 l_encumbered_amount NUMBER;
226 l_document_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
227 l_accrue_on_receipt_flag PO_DISTRIBUTIONS_ALL.accrue_on_receipt_flag%TYPE;
228 l_amount_moved_to_actual NUMBER;
229 l_return_status VARCHAR2(1); --Bug#5058165
230 l_msg_count NUMBER; --Bug#5058165
231 l_msg_data VARCHAR2(2000); --Bug#5058165
232 BEGIN
233
234 IF g_fnd_debug = 'Y' THEN
235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
236 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
237 || l_progress,'Invoked');
238 END IF;
239 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
240 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
241 || l_progress,'p_api_version: ' || p_api_version);
242 END IF;
243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
244 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
245 || l_progress,'p_doc_type: ' || p_doc_type);
246 END IF;
247 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
248 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
249 || l_progress,'p_distribution_id: ' || p_distribution_id);
250 END IF;
251 END IF;
252
253 -- Standard call to check for call compatibility
254 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
255 G_PKG_NAME) THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259 l_progress := '010';
260
261 -- Initialize message list if p_init_msg_list is set to TRUE.
262 IF FND_API.to_Boolean( p_init_msg_list ) THEN
263 FND_MSG_PUB.initialize;
264 END IF;
265
266 l_progress := '020';
267
268 -- Initialize API return status to success
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 l_progress := '030';
272 IF g_fnd_debug = 'Y' THEN
273 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
274 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
275 || l_progress, 'Finished Initialization');
276 END IF;
277 END IF;
278
279 -- The appropriate calculation for encumbered amount
280 -- depends on the type of document
281
282 IF p_doc_type = g_doc_type_REQUISITION THEN
283
284 -- The API currently only supports Purchase Requisitions
285 -- If Req line is an internal requsition, raise an exception
286 l_progress := '040';
287 IF g_fnd_debug = 'Y' THEN
288 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
289 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
290 || l_progress, 'doc is a Req');
291 END IF;
292 END IF;
293
294 SELECT
295 PRL.source_type_code
296 , nvl(PRD.encumbered_amount, 0)
297 INTO
298 l_document_subtype
299 , l_encumbered_amount
300 FROM PO_REQUISITION_LINES_ALL PRL
301 , PO_REQ_DISTRIBUTIONS_ALL PRD
302 WHERE PRD.distribution_id = p_distribution_id
303 AND PRD.requisition_line_id = PRL.requisition_line_id
304 ;
305
306 l_progress := '050';
307
308 IF l_document_subtype = 'VENDOR' THEN
309 -- For Purchase Reqs, active enc = encumbered amt always
310
311 l_progress := '060';
312 IF g_fnd_debug = 'Y' THEN
313 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
314 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name
315 ||'.' || l_progress, 'Vendor Req enc amt: ' || l_encumbered_amount);
316 END IF;
317 END IF;
318
319 x_active_enc_amount := l_encumbered_amount;
320 ELSE
321 -- source type = 'INVENTORY' (internal Req)
322 -- this is not currently supported
323
324 l_progress := '070';
325 IF g_fnd_debug = 'Y' THEN
326 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
327 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name
328 ||'.'|| l_progress, 'Internal Reqs are not supported');
329 END IF;
330 END IF;
331
335 ELSIF p_doc_type = g_doc_type_PA THEN
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END IF;
334
336
337 --If doc type is given as PA, assume it is encumbered,
338 --else there would not be a p_distribution_id
339 l_progress := '080';
340
341 -- Bug 3446983: For encumbered PA, active enc = encumbered amt
342 SELECT nvl(POD.encumbered_amount, 0)
343 INTO x_active_enc_amount
344 FROM PO_DISTRIBUTIONS_ALL POD
345 WHERE POD.po_distribution_id = p_distribution_id
346 ;
347
348 l_progress := '090';
349 IF g_fnd_debug = 'Y' THEN
350 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
351 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
352 || l_progress, 'PA enc amount: ' || x_active_enc_amount);
353 END IF;
354 END IF;
355
356 ELSIF p_doc_type IN (g_doc_type_PO, g_doc_type_RELEASE) THEN
357
358 l_progress := '100';
359
360 SELECT
361 nvl(POD.encumbered_amount, 0)
362 , nvl(POD.accrue_on_receipt_flag, 'N')
363 , POLL.shipment_type
364 INTO
365 l_encumbered_amount
366 , l_accrue_on_receipt_flag
367 , l_document_subtype
368 FROM PO_DISTRIBUTIONS_ALL POD
369 , PO_LINE_LOCATIONS_ALL POLL
370 WHERE POD.po_distribution_id = p_distribution_id
371 AND POD.line_location_id = POLL.line_location_id
372 ;
373
374 l_progress := '110';
375 IF g_fnd_debug = 'Y' THEN
376 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
377 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
378 || l_progress, 'doc shipment type: ' || l_document_subtype);
379 END IF;
380 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
381 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
382 || l_progress, 'accrue on receipt: ' || l_accrue_on_receipt_flag);
383 END IF;
384 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
385 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
386 || l_progress, 'encumbered amount: ' || l_encumbered_amount);
387 END IF;
388 END IF;
389 IF l_document_subtype = 'PLANNED' THEN
390 -- If this is a PPO, then the active encumbrance will always just be
391 -- the PO encumbrance (there is no reversal from Delivery/Invoicing)
392 l_progress := '120';
393
394 x_active_enc_amount := l_encumbered_amount;
395
396 ELSE
397 -- For all other POs/Rels, the amount moved to actuals
398 -- by AP or CST must be subtracted out to get the
399 -- active encumbrance amount
400 l_progress := '130';
401
402 IF (l_accrue_on_receipt_flag = 'Y') THEN
403 -- Online Accruals: actual amount is determined by CST reversals
404 l_progress := '140';
405
406 -- Bug 3455267: CST API
407 -- Use an API from CST to determine the amount of PO encumbrance
408 -- relieved by delivery transactions.
409 l_amount_moved_to_actual :=
410 RCV_ACCRUALUTILITIES_GRP.Get_encumReversalAmt(
411 p_po_distribution_id => p_distribution_id
412 , p_start_txn_date => NULL
413 , p_end_txn_date => NULL
414 ); --bug 3450228: removed '* -1' added in bug 3330335
415
416 l_progress := '150';
417 IF g_fnd_debug = 'Y' THEN
418 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
419 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'
420 ||l_api_name ||'.' || l_progress,
421 'CST reversed amt: ' || l_amount_moved_to_actual);
422 END IF;
423 END IF;
424
425 ELSE
426 -- Period End Accruals: actual amount is determined by AP reversals
427 l_progress := '160';
428
429 -- Bug 3068177: AP API
430 -- Use an API from AP to determine the amount of PO encumbrance
431 -- AP has relieved. In the over-billed case, AP will not over-relieve
432 -- the PO encumbrance. So this API should never return an actual amount
433 -- that is greater than the PO encumbered amount.
434
435 -- bug5058165
436 -- Changed the package name from AP_UTILITIES_PKG to PSA_AP_BC_GRP
437 PSA_AP_BC_GRP.get_po_reversed_encumb_amount(
438 p_api_version =>1.0,
439 p_init_msg_list => FND_API.G_FALSE,
440 x_return_status => l_return_status,
441 x_msg_count=>l_msg_count,
442 x_msg_data=>l_msg_data,
443 p_po_distribution_id => p_distribution_id,
444 p_start_gl_date => NULL,
445 p_end_gl_date => NULL,
446 p_calling_sequence => NULL,
447 x_unencumbered_amount => l_amount_moved_to_actual);
448 l_progress := '170';
449 IF g_fnd_debug = 'Y' THEN
453 'AP reversed amt: ' || l_amount_moved_to_actual);
450 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
451 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'
452 ||l_api_name ||'.' || l_progress,
454 END IF;
455 END IF;
456
457 END IF;
458
459 l_progress := '180';
460
461 --bug 3519982: remove the greatest(0, <>) from this subtract
462 --if active enc is negative, we do not want to supress that
463 x_active_enc_amount := l_encumbered_amount -
464 nvl(l_amount_moved_to_actual, 0);
465
466 l_progress := '190';
467 IF g_fnd_debug = 'Y' THEN
468 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
469 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'
470 ||l_api_name ||'.' || l_progress,
471 '***Active Encumbrance Amount: ' || x_active_enc_amount);
472 END IF;
473 END IF;
474
475 END IF; -- if doc was PPO or not
476
477 ELSE
478 --Invalid parameter value
479
480 l_progress := '200';
481 IF g_fnd_debug = 'Y' THEN
482 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
483 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'
484 ||l_api_name ||'.' || l_progress,
485 'Invalid value for doc type: ' || p_doc_type);
486 END IF;
487 END IF;
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489
490 END IF; -- check on p_doc_type
491
492 EXCEPTION
493
494 WHEN OTHERS THEN
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
497 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
498 END IF;
499
500 IF (g_fnd_debug = 'Y') THEN
501 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
502 FND_LOG.string(FND_LOG.level_unexpected, c_log_head ||
503 l_api_name || '.others_exception', 'EXCEPTION: '
504 || 'Location is '|| l_progress || ' SQL CODE is '||sqlcode);
505 END IF;
506
507 END IF;
508
509 END get_active_encumbrance_amount;
510
511
512 FUNCTION get_active_encumbrance_func(
513 p_doc_type IN VARCHAR2
514 , p_distribution_id IN NUMBER
515 )
516 RETURN NUMBER IS
517 l_return_status VARCHAR2(1);
518 l_active_enc_amt NUMBER;
519 BEGIN
520
521 get_active_encumbrance_amount(
522 p_api_version => 1.0
523 , p_init_msg_list => FND_API.G_FALSE
524 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
525 , x_return_status => l_return_status
526 , p_doc_type => p_doc_type
527 , p_distribution_id => p_distribution_id
528 , x_active_enc_amount => l_active_enc_amt
529 );
530
531 RETURN l_active_enc_amt;
532
533 END get_active_encumbrance_func;
534
535
536 END PO_INTG_DOCUMENT_FUNDS_GRP;
[Home] [Help]