1 PACKAGE BODY PO_AP_INVOICE_MATCH_GRP AS
2 /* $Header: POXAPINB.pls 120.12.12020000.3 2013/02/10 22:52:23 vegajula ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AP_INVOICE_MATCH_GRP';
5 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base(G_PKG_NAME);
6
7 --<Complex Work R12 START>
8 -------------------------------------------------------------------------
9 --Pre-reqs:
10 -- N/A
11 --Function:
12 -- Updates values on the PO line location and distribution due to AP
13 -- activity (billing, prepayments, recoupment, retainage, etc)
14 --Parameters:
15 --IN:
16 --p_api_version
17 -- Apps API Std - To control correct version in use
18 --IN OUT:
19 --p_line_loc_changes_rec
20 -- An object of PO_AP_LINE_LOC_REC_TYPE
21 --p_dist_changes_rec
22 -- An object of PO_AP_DIST_REC_TYPE
23 --OUT:
24 --x_return_status
25 -- Apps API param. Value is VARCHAR2(1)
26 -- FND_API.G_RET_STS_SUCCESS if update succeeds
27 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
28 --x_msg_data
29 -- Contains the error details in the case of UNEXP_ERROR or ERROR
30 -------------------------------------------------------------------------
31 PROCEDURE update_document_ap_values(
32 p_api_version IN NUMBER
33 , p_line_loc_changes_rec IN OUT NOCOPY PO_AP_LINE_LOC_REC_TYPE
34 , p_dist_changes_rec IN OUT NOCOPY PO_AP_DIST_REC_TYPE
35 , x_return_status OUT NOCOPY VARCHAR2
36 , x_msg_data OUT NOCOPY VARCHAR2
37 )
38 IS
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'update_document_ap_values';
41 d_mod CONSTANT VARCHAR2(100) :=
42 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_api_name);
43 d_position NUMBER := 0;
44 BEGIN
45
46 -- Standard API Savepoint
47 SAVEPOINT update_document_ap_values_SP;
48
49 IF PO_LOG.d_proc THEN
50 PO_LOG.proc_begin(d_mod,'p_api_version',p_api_version);
51 p_line_loc_changes_rec.dump_to_log;
52 p_dist_changes_rec.dump_to_log;
53 END IF;
54
55 -- Initialize return status and msg data
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57 x_msg_data := NULL;
58
59 d_position := 10;
60
61 IF (NOT FND_API.compatible_api_call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 d_position := 20;
66
67 -- Invoke methods on the parameter objects that calculate field
68 -- values not set by AP when AP created these objects (this also
69 -- performs the UOM conversion between AP UOM and PO UOM)
70 -- First call the line loc object
71 p_line_loc_changes_rec.populate_calculated_fields;
72
73 d_position := 25;
74
75 -- Next call the distribution object
76 p_dist_changes_rec.populate_calculated_fields;
77
78 d_position := 30;
79
80 IF PO_LOG.d_stmt THEN
81 PO_LOG.stmt(d_mod,d_position,'Calculated fields populated');
82 p_line_loc_changes_rec.dump_to_log;
83 p_dist_changes_rec.dump_to_log;
84 END IF;
85
86 -- SQL What: Update the AP-related fields on the PO line location
87 -- SQL Where: For the line location row represented in p_line_loc_changes_rec
88 -- Also, for Scheduled Releases, update the information on the
89 -- backing Planned PO line location row as well
90 UPDATE po_line_locations_all pll
91 SET quantity_billed =
92 DECODE(p_line_loc_changes_rec.quantity_billed
93 , NULL, quantity_billed
94 , nvl(quantity_billed, 0) + p_line_loc_changes_rec.quantity_billed),
95 amount_billed =
96 DECODE(p_line_loc_changes_rec.amount_billed
97 , NULL, amount_billed
98 , nvl(amount_billed, 0) + p_line_loc_changes_rec.amount_billed),
99 quantity_financed =
100 DECODE(p_line_loc_changes_rec.quantity_financed
101 , NULL, quantity_financed
102 , nvl(quantity_financed, 0) + p_line_loc_changes_rec.quantity_financed),
103 amount_financed =
104 DECODE(p_line_loc_changes_rec.amount_financed
105 , NULL, amount_financed
106 , nvl(amount_financed, 0) + p_line_loc_changes_rec.amount_financed),
107 quantity_recouped =
108 DECODE(p_line_loc_changes_rec.quantity_recouped
109 , NULL, quantity_recouped
110 , nvl(quantity_recouped, 0) + p_line_loc_changes_rec.quantity_recouped),
111 amount_recouped =
112 DECODE(p_line_loc_changes_rec.amount_recouped
113 , NULL, amount_recouped
114 , nvl(amount_recouped, 0) + p_line_loc_changes_rec.amount_recouped),
115 retainage_withheld_amount =
116 DECODE(p_line_loc_changes_rec.retainage_withheld_amt
117 , NULL, retainage_withheld_amount
118 , nvl(retainage_withheld_amount, 0) + p_line_loc_changes_rec.retainage_withheld_amt),
119 retainage_released_amount =
120 DECODE(p_line_loc_changes_rec.retainage_released_amt
121 , NULL, retainage_released_amount
122 , nvl(retainage_released_amount, 0) + p_line_loc_changes_rec.retainage_released_amt),
123 last_update_login = nvl(p_line_loc_changes_rec.last_update_login, last_update_login),
124 request_id = nvl(p_line_loc_changes_rec.request_id, request_id) ,
125 last_update_date = SYSDATE, -- Bug 10021100
126 last_updated_by = FND_GLOBAL.USER_ID
127 WHERE pll.line_location_id = p_line_loc_changes_rec.po_line_location_id
128 OR (pll.shipment_type = 'PLANNED'
129 AND pll.line_location_id =
130 (SELECT pll2.source_shipment_id
131 FROM po_line_locations pll2
132 WHERE pll2.shipment_type = 'SCHEDULED'
133 AND pll2.line_location_id = p_line_loc_changes_rec.po_line_location_id)
134 )
135 ;
136
137 d_position := 40;
138 IF PO_LOG.d_stmt THEN
139 PO_LOG.stmt(d_mod,d_position,'Line Locations updated: ' || SQL%ROWCOUNT || '; fnd_global.user_id: ' || fnd_global.user_id);
140 END IF;
141
142 -- SQL What: Update the AP-related fields on the PO distribution
143 -- SQL Where: For the distribution rows represented in p_dist_changes_rec
144 -- Also, for Scheduled Releases, update the information on the
145 -- backing Planned PO distribution row as well
146 FORALL i IN 1..p_dist_changes_rec.po_distribution_id_tbl.COUNT
147 UPDATE po_distributions_all pod
148 SET quantity_billed =
149 DECODE(p_dist_changes_rec.quantity_billed_tbl(i)
150 , NULL, quantity_billed
151 , nvl(quantity_billed, 0) + p_dist_changes_rec.quantity_billed_tbl(i)),
152 amount_billed =
153 DECODE(p_dist_changes_rec.amount_billed_tbl(i)
154 , NULL, amount_billed
155 , nvl(amount_billed, 0) + p_dist_changes_rec.amount_billed_tbl(i)),
156 quantity_financed =
157 DECODE(p_dist_changes_rec.quantity_financed_tbl(i)
158 , NULL, quantity_financed
159 , nvl(quantity_financed, 0) + p_dist_changes_rec.quantity_financed_tbl(i)),
160 amount_financed =
161 DECODE(p_dist_changes_rec.amount_financed_tbl(i)
162 , NULL, amount_financed
163 , nvl(amount_financed, 0) + p_dist_changes_rec.amount_financed_tbl(i)),
164 quantity_recouped =
165 DECODE(p_dist_changes_rec.quantity_recouped_tbl(i)
166 , NULL, quantity_recouped
167 , nvl(quantity_recouped, 0) + p_dist_changes_rec.quantity_recouped_tbl(i)),
168 amount_recouped =
169 DECODE(p_dist_changes_rec.amount_recouped_tbl(i)
170 , NULL, amount_recouped
171 , nvl(amount_recouped, 0) + p_dist_changes_rec.amount_recouped_tbl(i)),
172 retainage_withheld_amount =
173 DECODE(p_dist_changes_rec.retainage_withheld_amt_tbl(i)
174 , NULL, retainage_withheld_amount
175 , nvl(retainage_withheld_amount, 0) + p_dist_changes_rec.retainage_withheld_amt_tbl(i)),
176 retainage_released_amount =
177 DECODE(p_dist_changes_rec.retainage_released_amt_tbl(i)
178 , NULL, retainage_released_amount
179 , nvl(retainage_released_amount, 0) + p_dist_changes_rec.retainage_released_amt_tbl(i)),
180 last_update_login = nvl(p_dist_changes_rec.last_update_login_tbl(i), last_update_login),
181 request_id = nvl(p_dist_changes_rec.request_id_tbl(i), request_id),
182 last_update_date = SYSDATE, -- Bug 10021100
183 last_updated_by = FND_GLOBAL.USER_ID
184 WHERE pod.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)
185 OR (pod.distribution_type = 'PLANNED'
186 AND pod.po_distribution_id =
187 (SELECT pod2.source_distribution_id
188 FROM po_distributions pod2
189 WHERE pod2.distribution_type = 'SCHEDULED'
190 AND pod2.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)))
191 ;
192
193 d_position := 50;
194 IF PO_LOG.d_stmt THEN
195 PO_LOG.stmt(d_mod,d_position,'Distributions updated: ' || SQL%ROWCOUNT || '; fnd_global.user_id: ' || fnd_global.user_id);
196 END IF;
197
198 IF PO_LOG.d_proc THEN
199 PO_LOG.proc_end(d_mod,'x_return_status', x_return_status);
200 p_line_loc_changes_rec.dump_to_log;
201 p_dist_changes_rec.dump_to_log;
202 END IF;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 ROLLBACK TO update_document_ap_values_SP;
207 IF PO_LOG.d_exc THEN
208 PO_LOG.exc(d_mod,d_position,SQLERRM);
209 END IF;
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 FND_MESSAGE.set_name('PO', 'PO_ALL_TRACE_ERROR');
212 FND_MESSAGE.set_token('FILE', 'POXAPINB.pls');
213 FND_MESSAGE.set_token('ERR_NUMBER', SQLERRM(SQLCODE));
214 FND_MESSAGE.set_token('SUBROUTINE', l_api_name);
215 x_msg_data := FND_MESSAGE.get;
216
217 END; --update_document_ap_values
218
219
220 ---------------------------------------------------------------------------
221 --Pre-reqs:
222 -- All line locations must belong to the same PO document
223 --Function:
224 -- Calculate how much to retain against particular line locations, based
225 -- on the contract terms specified on the PO.
226 --Parameters:
227 --IN:
228 --p_api_version
229 -- Apps API Std - To control correct version in use
230 --p_line_location_id_tbl
231 -- Table of ids from the set of {existing POLL.line_location}
232 -- All ids must belong to the same PO document
233 --p_line_loc_match_amt_tbl
234 -- Each tbl entry corresponds to 1 entry (w/ same index) in
235 -- p_line_location_id_tbl. It passes in the amount being matched against
236 -- each line location in this trxn
237 -- The amount must be passed in using the PO currency
238 --OUT:
239 --x_return_status
240 -- Apps API Std param. Value is VARCHAR2(1)
241 -- FND_API.G_RET_STS_SUCCESS if calculation succeeds
242 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
243 --x_msg_data
244 -- Contains the error details in the case of UNEXP_ERROR or ERROR
245 --x_amount_to_retain_tbl
246 -- Each tbl entry corresponds to 1 entry (with same index) in
247 -- p_line_location_id_tbl. It returns the calculated amount to retain
248 -- against each line location
249 -------------------------------------------------------------------------
250 PROCEDURE get_amount_to_retain(
251 p_api_version IN NUMBER
252 , p_line_location_id_tbl IN po_tbl_number
253 , p_line_loc_match_amt_tbl IN po_tbl_number
254 , x_return_status OUT NOCOPY VARCHAR2
255 , x_msg_data OUT NOCOPY VARCHAR2
256 , x_amount_to_retain_tbl OUT NOCOPY po_tbl_number
257 )
258 IS
259 l_api_version CONSTANT NUMBER := 1.0;
260 l_api_name CONSTANT VARCHAR2(30) := 'get_amount_to_retain';
261 d_mod CONSTANT VARCHAR2(100) :=
262 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,l_api_name);
263 d_position NUMBER := 0;
264 l_gt_key NUMBER;
265 BEGIN
266
267 IF PO_LOG.d_proc THEN
268 PO_LOG.proc_begin(d_mod,'p_api_version',p_api_version);
269 PO_LOG.proc_begin(d_mod,'p_line_location_id_tbl',p_line_location_id_tbl);
270 PO_LOG.proc_begin(d_mod,'p_line_loc_match_amt_tbl',p_line_loc_match_amt_tbl);
271 END IF;
272
273 -- Initialize out parameters
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275 x_msg_data := NULL;
276 x_amount_to_retain_tbl:= po_tbl_number();
277 x_amount_to_retain_tbl.extend;
278
279 d_position := 10;
280
281 IF (NOT FND_API.compatible_api_call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END IF;
284
285 d_position := 20;
286
287 SELECT PO_SESSION_GT_S.nextval
288 INTO l_gt_key
289 FROM dual;
290
291 d_position := 30;
292
293 -- Calculate the amount to retain against each line location
294 -- based on the retainage rate. Use the GTT to do the calculation
295 -- in bulk
296
297 --Bug 5524978: Modified Logic to fcator in that max_retainage_amount can be NULL
298 --Bug 5549067: Used NVL around PO_LINES_INT.retained_amount
299 -- Bug 13443523 -start
300 FORALL i IN 1 .. p_line_location_id_tbl.COUNT
301 INSERT INTO PO_SESSION_GT GTT(
302 key,
303 num1, -- Shipment Id on the current Invoice
304 num2, -- Line Id on the current Invoice
305 num3, -- CurrenT Invoice Amount at Shipment Level
306 num6, -- Retainage Rate for the Line
307 num7 -- Maximum Retainage Amount for the Line
308 )
309 SELECT
310 l_gt_key,
311 p_line_location_id_tbl(i),
312 pl.po_line_id,
313 p_line_loc_match_amt_tbl(i),
314 pl.retainage_rate,
315 pl.max_retainage_amount
316 FROM po_lines_all pl,
317 po_line_locations_all pll
318 where pl.po_line_id = pll.po_line_id
319 and pll.line_location_id = p_line_location_id_tbl(i);
320
321 -- SumAmtInvoiced_Line_Session (SIALS)
322 UPDATE PO_SESSION_GT GTO
323 SET GTO.NUM4 = (SELECT SUM(GTI.NUM3)
324 FROM PO_SESSION_GT GTI
325 WHERE GTI.num2 = GTO.num2);
326
327
328 -- Sum of Retained Amount at Line(SRAL)
329 UPDATE PO_SESSION_GT GTO
330 SET GTO.NUM5 = (SELECT SUM(Nvl(pll.retainage_withheld_amount,0))
331 FROM po_line_locations_all PLL
332 WHERE PLL.po_line_id = GTO.num2);
333
334 -- Calculated Retainable Amount for the Current Session (CRAS)
335 UPDATE PO_SESSION_GT GTO
336 SET GTO.NUM8 = (SELECT GTI.NUM4*GTI.NUM6/100
337 FROM PO_SESSION_GT GTI
338 WHERE GTI.num2 = GTO.num2);
339
340 FOR CREC IN (SELECT * FROM PO_SESSION_GT GTT WHERE GTT.key = l_gt_key ORDER BY GTT.num1)
341 LOOP
342
343 --#1: Check if the Max retainage Amount is defined and is less than the total retained amount calculated
344 -- In this case we need to retain only the difference betweem Max. Retained Amount and Already Retained Amount
345
346 IF CREC.NUM7 IS NOT NULL AND
347 CREC.NUM7 >= 0 AND
348 CREC.NUM8 >= 0 AND
349 CREC.NUM5+CREC.NUM8 > CREC.NUM7 THEN
350 UPDATE PO_SESSION_GT GTO
351 SET GTO.NUM9 = CREC.NUM7-CREC.NUM5
352 WHERE GTO.num1 = CREC.num1;
353
354 --#2: If calculated retainage amount is negative and more than sum of the retained amount at line level,
355 -- then we will just release sum of retained amount.
356
357 ELSIF CREC.NUM8 < 0 AND
358 CREC.NUM5+CREC.NUM8 < 0 THEN
359 UPDATE PO_SESSION_GT GTO
360 SET GTO.NUM9 = -CREC.NUM5
361 WHERE GTO.num1 = CREC.num1 ;
362 --#3: For any other case, calculated retainage amount can be assigned to the Adjustable Retainable Amount.
363 ELSE
364 UPDATE PO_SESSION_GT GTO
365 SET GTO.NUM9 = CREC.NUM8
366 WHERE GTO.num1 = CREC.num1;
367 END IF;
368 END LOOP;
369
370 --Prorated Retainable Amount per Record (PRAR)
371 UPDATE PO_SESSION_GT GTO
372 SET GTO.NUM10 = (SELECT (GTI.NUM3/GTI.NUM4)*GTI.NUM9
373 FROM PO_SESSION_GT GTI
374 WHERE GTI.num1 = GTO.num1);
375
376 -- Bug 13443523 -End
377
378 d_position := 40;
379
380 -- Retrieve the results from the GT into the plsql table out param
381 SELECT GTT.NUM10
382 BULK COLLECT INTO x_amount_to_retain_tbl
383 FROM PO_SESSION_GT GTT
384 WHERE GTT.key = l_gt_key
385 ORDER BY GTT.num1 --input and output tbls have same ordering
386 ;
387
388 d_position := 50;
389
390 -- Clean up the GT by deleting the data
391 DELETE FROM PO_SESSION_GT GTT WHERE GTT.key = l_gt_key;
392
393 d_position := 60;
394
395 IF PO_LOG.d_proc THEN
396 PO_LOG.proc_end(d_mod,'x_return_status',x_return_status);
397 PO_LOG.proc_end(d_mod,'x_amount_to_retain_tbl',x_amount_to_retain_tbl);
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF PO_LOG.d_exc THEN
403 PO_LOG.exc(d_mod,d_position,SQLERRM);
404 END IF;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 FND_MESSAGE.set_name('PO', 'PO_ALL_TRACE_ERROR');
407 FND_MESSAGE.set_token('FILE', 'POXAPINB.pls');
408 FND_MESSAGE.set_token('ERR_NUMBER', SQLERRM(SQLCODE));
409 FND_MESSAGE.set_token('SUBROUTINE', l_api_name);
410 x_msg_data := FND_MESSAGE.get;
411
412 END; --get_amount_to_retain
413 --<Complex Work R12 END>
414
415
416
417 ---------------------------------------------------------------------------------------------
418 --Start of Comments
419 --Name: get_po_ship_amounts
420 --
421 --Pre-reqs: None
422 --
423 --Modifies: None
424 --
425 --Locks: None
426 --
427 --Function: This procedure provides AP with ordered and cancelled amounts on the PO
428 -- shipments for amount matching purposes
429 --
430 --
431 --Parameters:
432 --IN:
433 -- p_api_version
434 -- Specifies the version of the api. Value that needs to be passed inis 1.0
435 -- p_receive_transaction_id
436 -- Specifies the receive transaction id for which the amounts need to be retrieved
437 -- from the corresponding shipments
438 --OUT:
439 -- x_ship_amt_ordered
440 -- The amount on the PO shipment corresponding to the rcv transaction id passed
441 -- x_ship_amt_cancelled
442 -- The cancelled amount on the PO shipment corresponding to the rcv transaction
443 -- id passed
444 -- x_ret_status
445 -- (a) FND_API.G_RET_STS_SUCCESS if successful
446 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
447 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
448 -- x_msg_count
449 -- The number of error messages to be returned (1 in this case)
450 -- x_msg_data
451 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
452 --
453 --Testing: -
454 --End of Comments
455 -------------------------------------------------------------------------------------------------
456
457 PROCEDURE get_po_ship_amounts (p_api_version IN NUMBER,
458 p_receive_transaction_id IN RCV_TRANSACTIONS.transaction_id%TYPE,
459 x_ship_amt_ordered OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount%TYPE,
460 x_ship_amt_cancelled OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount_cancelled%TYPE,
461 x_ret_status OUT NOCOPY VARCHAR2,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2) IS
464
465 l_api_name CONSTANT VARCHAR2(30) := 'get_po_ship_amounts';
466 l_api_version CONSTANT NUMBER := 1.0;
467
468 BEGIN
469
470 -- Initialize return status and msg data
471 x_ret_status := FND_API.G_RET_STS_SUCCESS;
472 x_msg_count := 0;
473 x_msg_data := NULL;
474
475 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
476 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 END IF;
478
479 -- SQL What : Gets the amount and amount cancelled on the PO shipments corresponding to the
480 -- Receive transaction_id
481 -- SQL Why : These amounts are used for amount based mathing on receipts
482
483 SELECT pll.amount,
484 pll.amount_cancelled
485 INTO x_ship_amt_ordered,
486 x_ship_amt_cancelled
487 FROM po_line_locations pll,
488 rcv_transactions rt
489 WHERE rt.po_line_location_id = pll.line_location_id
490 AND rt.transaction_id = p_receive_transaction_id;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
495 END;
496
497
498 -----------------------------------------------------------------------------------------------
499 --Start of Comments
500 --Name: get_po_dist_amounts
501 --
502 --Pre-reqs: None
503 --
504 --Modifies: None
505 --
506 --Locks: None
507 --
508 --Function: This procedure provides AP with ordered and cancelled amounts on the PO
509 -- distributions for amount matching purposes
510 --
511 --
512 --Parameters:
513 --IN:
514 -- p_api_version
515 -- Specifies the version of the api. Value that needs to be passed inis 1.0
516 -- p_po_distribution_id
517 -- Specifies the distributions id for which the amounts need to be retrieved
518 --
519 --OUT:
520 -- x_dist_amt_ordered
521 -- The amount ordered on the PO distribution
522 -- x_dist_amt_cancelled
523 -- The cancelled amount on the PO distribution
524 -- x_ret_status
525 -- (a) FND_API.G_RET_STS_SUCCESS if successful
526 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
527 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
528 -- x_msg_count
529 -- The number of error messages to be returned (1 in this case)
530 -- x_msg_data
531 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
532 --
533 --
534 --Testing: -
535 --End of Comments
536 ----------------------------------------------------------------------------------------
537
538 PROCEDURE get_po_dist_amounts (p_api_version IN NUMBER,
539 p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE,
540 x_dist_amt_ordered OUT NOCOPY PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE,
541 x_dist_amt_cancelled OUT NOCOPY PO_DISTRIBUTIONS_ALL.amount_cancelled%TYPE,
542 x_ret_status OUT NOCOPY VARCHAR2,
543 x_msg_count OUT NOCOPY NUMBER,
544 x_msg_data OUT NOCOPY VARCHAR2) IS
545
546 l_api_name CONSTANT VARCHAR2(30) := 'get_po_dist_amounts';
547 l_api_version CONSTANT NUMBER := 1.0;
548
549 BEGIN
550
551 -- Initialize return status and msg data
552 x_ret_status := FND_API.G_RET_STS_SUCCESS;
553 x_msg_count := 0;
554 x_msg_data := NULL;
555
556 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
557 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 END IF;
559
560 -- SQL What : Gets the amountordered and amount cancelled on the PO distributions
561 -- corresponding to the distribution id passed
562 -- SQL Why : These amounts are used for amount based matching of invoices
563
564 SELECT pod.amount_ordered,
565 pod.amount_cancelled
566 INTO x_dist_amt_ordered,
567 x_dist_amt_cancelled
568 FROM po_distributions pod
569 WHERE pod.po_distribution_id = p_po_distribution_id;
570
571 EXCEPTION
572 WHEN OTHERS THEN
573 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 END;
575
576 ---------------------------------------------------------------------------------------
577 --Start of Comments
578 --Name: update_po_ship_amounts
579 --
580 --Pre-reqs: None
581 --
582 --Modifies: PO_LINE_LOCATIONS_ALL
583 --
584 --Locks: None
585 --
586 --Function: This procedure updates the amount billed on po shipments during amount matching process
587 --
588 --Parameters:
589 --IN:
590 -- p_api_version
591 -- Specifies the version of the api. Value that needs to be passed inis 1.0
592 -- p_po_line_location_id
593 -- Specifies the line location id for which the amounts need updated
594 --
595 --OUT:
596 -- x_dist_amt_billed
597 -- The amount billed to be updated on the PO Shipment
598 -- x_ret_status
599 -- (a) FND_API.G_RET_STS_SUCCESS if successful
600 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
601 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
602 -- x_msg_count
603 -- The number of error messages to be returned (1 in this case)
604 -- x_msg_data
605 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
606 --
607 --
608 --Testing: -
609 --End of Comments
610 ------------------------------------------------------------------------------------------------
611
612 PROCEDURE update_po_ship_amounts (p_api_version IN NUMBER,
613 p_po_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
614 p_ship_amt_billed IN PO_LINE_LOCATIONS_ALL.amount_billed%TYPE,
615 x_ret_status OUT NOCOPY VARCHAR2,
616 x_msg_count OUT NOCOPY NUMBER,
617 x_msg_data OUT NOCOPY VARCHAR2) IS
618
619 l_api_name CONSTANT VARCHAR2(30) := 'update_po_ship_amounts';
620 l_api_version CONSTANT NUMBER := 1.0;
621
622 BEGIN
623
624 -- Initialize return status and msg data
625 x_ret_status := FND_API.G_RET_STS_SUCCESS;
626 x_msg_count := 0;
627 x_msg_data := NULL;
628
629 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
630 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 END IF;
632
633 -- SQL What : Updates the amount billed on the po shipments
634 -- SQL Why : This is the amount that was billed against this shipment
635
636 UPDATE po_line_locations_all
637 SET amount_billed = nvl(amount_billed,0) + nvl(p_ship_amt_billed,0)
638 WHERE line_location_id = p_po_line_location_id;
639
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 END;
645
646 -----------------------------------------------------------------------------------------------------
647 --Start of Comments
648 --Name: update_po_dist_amounts
649 --
650 --Pre-reqs: None
651 --
652 --Modifies: PO_DISTRIBUTIONS_ALL
653 --
654 --Locks: None
655 --
656 --Function: This procedure updates the amount billed on po distributions during amount matching process
657 --
658 --
659 --Parameters:
660 --IN:
661 -- p_api_version
662 -- Specifies the version of the api. Value that needs to be passed inis 1.0
663 -- p_po_distribution_id
664 -- Specifies the distributions id for which the amounts need updated
665 --
666 --OUT:
667 -- x_dist_amt_billed
668 -- The amount billed to be updated on the PO distribution
669 -- x_ret_status
670 -- (a) FND_API.G_RET_STS_SUCCESS if successful
671 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
672 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
673 -- x_msg_count
674 -- The number of error messages to be returned (1 in this case)
675 -- x_msg_data
676 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
677 --
678 --Testing: -
679 --End of Comments
680 -------------------------------------------------------------------------------------------------------
681
682 PROCEDURE update_po_dist_amounts (p_api_version IN NUMBER,
683 p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE,
684 p_dist_amt_billed IN PO_DISTRIBUTIONS_ALL.amount_billed%TYPE,
685 x_ret_status OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_msg_data OUT NOCOPY VARCHAR2) IS
688
689 l_api_name CONSTANT VARCHAR2(30) := 'update_po_dist_amounts';
690 l_api_version CONSTANT NUMBER := 1.0;
691
692 BEGIN
693
694 -- Initialize return status and msg data
695 x_ret_status := FND_API.G_RET_STS_SUCCESS;
696 x_msg_count := 0;
697 x_msg_data := NULL;
698
699 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
700 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
701 END IF;
702
703 -- SQL What : Updates the amount billed on the po distribution
704 -- SQL Why : This is the amount that was billed against this distribution
705
706 UPDATE po_distributions_all
707 SET amount_billed = nvl(amount_billed,0) + nvl(p_dist_amt_billed,0)
708 WHERE po_distribution_id = p_po_distribution_id;
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
713 END;
714
715 ---------------------------------------------------------------------------------------
716 --Start of Comments
717 --Created 02/09/04 Sanjay Chitlapilly
718 --
719 --Name: set_final_match_flag
720 --
721 --Pre-reqs: None
722 --
723 --Modifies: PO_LINE_LOCATIONS_ALL
724 --
725 --Locks: None
726 --
727 --Function: This procedure updates the final_match_flag on po shipments when an invoice is finally matched.
728
729 --
730 --Parameters:
731 --IN:
732 -- p_api_version
733 -- Specifies the version of the api. Value that needs to be passed in is 1.0
734 -- p_entity_type
735 -- Possible values: PO_HEADERS, PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
736 -- PO_LINE_LOCATIONS only supported currently.
737 -- p_entity_id_tbl
738 -- This will have one or more ids that you want to set the final match flag value.
739 -- p_final_match_flag
740 -- Possible Values: Y or N
741 -- p_init_msg_list (Optional) (Default FALSE)
742 -- Allows API callers to request the initialization of the message list.
743 -- p_commit (Optional) (Default FALSE)
744 -- Allows API callers to ask the API to commit on their behalf after performing its function.
745 --
746 --OUT:
747 -- x_ret_status
748 -- (a) FND_API.G_RET_STS_SUCCESS if successful
749 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
750 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
751 -- x_msg_count
752 -- The number of error messages to be returned (1 in this case)
753 -- x_msg_data
754 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
755 --
756 --
757 --End of Comments
758 ------------------------------------------------------------------------------------------------
759
760 PROCEDURE set_final_match_flag (p_api_version IN NUMBER ,
761 p_entity_type IN VARCHAR2 ,
762 p_entity_id_tbl IN PO_TBL_NUMBER ,
763 p_final_match_flag IN PO_LINE_LOCATIONS.FINAL_MATCH_FLAG%TYPE ,
764 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
765 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
766 x_ret_status OUT NOCOPY VARCHAR2 ,
767 x_msg_count OUT NOCOPY NUMBER ,
768 x_msg_data OUT NOCOPY VARCHAR2 ) IS
769
770 l_api_name CONSTANT VARCHAR2(30) := 'set_final_match_flag';
771 l_api_version CONSTANT NUMBER := 1.0;
772
773 BEGIN
774
775 -- Standard Start of API savepoint
776 SAVEPOINT set_final_match_flag_PVT;
777
778 -- Initialize message list if p_init_msg_list is set to TRUE.
779 IF FND_API.to_Boolean (p_init_msg_list) THEN
780 FND_MSG_PUB.initialize;
781 END IF;
782
783 -- Initialize return status and msg data
784 x_ret_status := FND_API.G_RET_STS_SUCCESS;
785 x_msg_count := 0;
786 x_msg_data := NULL;
787
788 IF (NOT FND_API.compatible_api_call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
789
790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791
792 END IF;
793
794 IF p_entity_type <> 'PO_LINE_LOCATIONS' THEN
795
796 RAISE FND_API.G_EXC_ERROR;
797
798 ELSE
799 -- SQL What : Updates the final_match_flag on the po shipments
800 -- SQL Why : This is to indicate the shipment has been finally matched by an invoice.
801
802 FORALL i IN 1 .. p_entity_id_tbl.COUNT
803
804 UPDATE po_line_locations_all
805 SET final_match_flag = p_final_match_flag
806 WHERE line_location_id = p_entity_id_tbl(i);
807
808 END IF;
809
810 -- Standard check of p_commit.
811 IF FND_API.To_Boolean (p_commit) THEN
812 COMMIT;
813 END IF;
814
815 -- Standard call to get message count and if count is 1, get message info.
816 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
817 p_data => x_msg_data );
818
819
820 EXCEPTION
821
822 WHEN FND_API.G_EXC_ERROR THEN
823
824 ROLLBACK TO set_final_match_flag_PVT;
825 x_ret_status := FND_API.G_RET_STS_ERROR ;
826 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
827 p_data => x_msg_data );
828
829
830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
831
832 ROLLBACK TO set_final_match_flag_PVT;
833 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
834 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
835 p_data => x_msg_data );
836
837 WHEN OTHERS THEN
838
839 ROLLBACK TO set_final_match_flag_PVT;
840 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
841 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
842 p_data => x_msg_data );
843
844 END set_final_match_flag;
845
846
847 END PO_AP_INVOICE_MATCH_GRP;
[Home] [Help]