1 PACKAGE BODY PO_AP_INVOICE_MATCH_GRP AS
2 /* $Header: POXAPINB.pls 120.11 2006/09/26 06:56:06 scolvenk noship $*/
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 WHERE pll.line_location_id = p_line_loc_changes_rec.po_line_location_id
126 OR (pll.shipment_type = 'PLANNED'
127 AND pll.line_location_id =
128 (SELECT pll2.source_shipment_id
129 FROM po_line_locations pll2
130 WHERE pll2.shipment_type = 'SCHEDULED'
131 AND pll2.line_location_id = p_line_loc_changes_rec.po_line_location_id)
132 )
133 ;
134
135 d_position := 40;
136 IF PO_LOG.d_stmt THEN
137 PO_LOG.stmt(d_mod,d_position,'Line Locations updated: ' || SQL%ROWCOUNT);
138 END IF;
139
140 -- SQL What: Update the AP-related fields on the PO distribution
141 -- SQL Where: For the distribution rows represented in p_dist_changes_rec
142 -- Also, for Scheduled Releases, update the information on the
143 -- backing Planned PO distribution row as well
144 FORALL i IN 1..p_dist_changes_rec.po_distribution_id_tbl.COUNT
145 UPDATE po_distributions_all pod
146 SET quantity_billed =
147 DECODE(p_dist_changes_rec.quantity_billed_tbl(i)
148 , NULL, quantity_billed
149 , nvl(quantity_billed, 0) + p_dist_changes_rec.quantity_billed_tbl(i)),
150 amount_billed =
151 DECODE(p_dist_changes_rec.amount_billed_tbl(i)
152 , NULL, amount_billed
153 , nvl(amount_billed, 0) + p_dist_changes_rec.amount_billed_tbl(i)),
154 quantity_financed =
155 DECODE(p_dist_changes_rec.quantity_financed_tbl(i)
156 , NULL, quantity_financed
157 , nvl(quantity_financed, 0) + p_dist_changes_rec.quantity_financed_tbl(i)),
158 amount_financed =
159 DECODE(p_dist_changes_rec.amount_financed_tbl(i)
160 , NULL, amount_financed
161 , nvl(amount_financed, 0) + p_dist_changes_rec.amount_financed_tbl(i)),
162 quantity_recouped =
163 DECODE(p_dist_changes_rec.quantity_recouped_tbl(i)
164 , NULL, quantity_recouped
165 , nvl(quantity_recouped, 0) + p_dist_changes_rec.quantity_recouped_tbl(i)),
166 amount_recouped =
167 DECODE(p_dist_changes_rec.amount_recouped_tbl(i)
168 , NULL, amount_recouped
169 , nvl(amount_recouped, 0) + p_dist_changes_rec.amount_recouped_tbl(i)),
170 retainage_withheld_amount =
171 DECODE(p_dist_changes_rec.retainage_withheld_amt_tbl(i)
172 , NULL, retainage_withheld_amount
173 , nvl(retainage_withheld_amount, 0) + p_dist_changes_rec.retainage_withheld_amt_tbl(i)),
174 retainage_released_amount =
175 DECODE(p_dist_changes_rec.retainage_released_amt_tbl(i)
176 , NULL, retainage_released_amount
177 , nvl(retainage_released_amount, 0) + p_dist_changes_rec.retainage_released_amt_tbl(i)),
178 last_update_login = nvl(p_dist_changes_rec.last_update_login_tbl(i), last_update_login),
179 request_id = nvl(p_dist_changes_rec.request_id_tbl(i), request_id)
180 WHERE pod.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)
181 OR (pod.distribution_type = 'PLANNED'
182 AND pod.po_distribution_id =
183 (SELECT pod2.source_distribution_id
184 FROM po_distributions pod2
185 WHERE pod2.distribution_type = 'SCHEDULED'
186 AND pod2.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)))
187 ;
188
189 d_position := 50;
190 IF PO_LOG.d_stmt THEN
191 PO_LOG.stmt(d_mod,d_position,'Distributions updated: ' || SQL%ROWCOUNT);
192 END IF;
193
194 IF PO_LOG.d_proc THEN
195 PO_LOG.proc_end(d_mod,'x_return_status', x_return_status);
196 p_line_loc_changes_rec.dump_to_log;
197 p_dist_changes_rec.dump_to_log;
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 ROLLBACK TO update_document_ap_values_SP;
203 IF PO_LOG.d_exc THEN
204 PO_LOG.exc(d_mod,d_position,SQLERRM);
205 END IF;
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 FND_MESSAGE.set_name('PO', 'PO_ALL_TRACE_ERROR');
208 FND_MESSAGE.set_token('FILE', 'POXAPINB.pls');
209 FND_MESSAGE.set_token('ERR_NUMBER', SQLERRM(SQLCODE));
210 FND_MESSAGE.set_token('SUBROUTINE', l_api_name);
211 x_msg_data := FND_MESSAGE.get;
212
213 END; --update_document_ap_values
214
215
216 ---------------------------------------------------------------------------
217 --Pre-reqs:
218 -- All line locations must belong to the same PO document
219 --Function:
220 -- Calculate how much to retain against particular line locations, based
221 -- on the contract terms specified on the PO.
222 --Parameters:
223 --IN:
224 --p_api_version
225 -- Apps API Std - To control correct version in use
226 --p_line_location_id_tbl
227 -- Table of ids from the set of {existing POLL.line_location}
228 -- All ids must belong to the same PO document
229 --p_line_loc_match_amt_tbl
230 -- Each tbl entry corresponds to 1 entry (w/ same index) in
231 -- p_line_location_id_tbl. It passes in the amount being matched against
232 -- each line location in this trxn
233 -- The amount must be passed in using the PO currency
234 --OUT:
235 --x_return_status
236 -- Apps API Std param. Value is VARCHAR2(1)
237 -- FND_API.G_RET_STS_SUCCESS if calculation succeeds
238 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
239 --x_msg_data
240 -- Contains the error details in the case of UNEXP_ERROR or ERROR
241 --x_amount_to_retain_tbl
242 -- Each tbl entry corresponds to 1 entry (with same index) in
243 -- p_line_location_id_tbl. It returns the calculated amount to retain
244 -- against each line location
245 -------------------------------------------------------------------------
246 PROCEDURE get_amount_to_retain(
247 p_api_version IN NUMBER
248 , p_line_location_id_tbl IN po_tbl_number
249 , p_line_loc_match_amt_tbl IN po_tbl_number
250 , x_return_status OUT NOCOPY VARCHAR2
251 , x_msg_data OUT NOCOPY VARCHAR2
252 , x_amount_to_retain_tbl OUT NOCOPY po_tbl_number
253 )
254 IS
255 l_api_version CONSTANT NUMBER := 1.0;
256 l_api_name CONSTANT VARCHAR2(30) := 'get_amount_to_retain';
257 d_mod CONSTANT VARCHAR2(100) :=
258 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,l_api_name);
259 d_position NUMBER := 0;
260 l_gt_key NUMBER;
261 BEGIN
262
263 IF PO_LOG.d_proc THEN
264 PO_LOG.proc_begin(d_mod,'p_api_version',p_api_version);
265 PO_LOG.proc_begin(d_mod,'p_line_location_id_tbl',p_line_location_id_tbl);
266 PO_LOG.proc_begin(d_mod,'p_line_loc_match_amt_tbl',p_line_loc_match_amt_tbl);
267 END IF;
268
269 -- Initialize out parameters
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 x_msg_data := NULL;
272 x_amount_to_retain_tbl:= po_tbl_number();
273 x_amount_to_retain_tbl.extend;
274
275 d_position := 10;
276
277 IF (NOT FND_API.compatible_api_call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280
281 d_position := 20;
282
283 SELECT PO_SESSION_GT_S.nextval
284 INTO l_gt_key
285 FROM dual;
286
287 d_position := 30;
288
289 -- Calculate the amount to retain against each line location
290 -- based on the retainage rate. Use the GTT to do the calculation
291 -- in bulk
292
293 --Bug 5524978: Modified Logic to fcator in that max_retainage_amount can be NULL
294 --Bug 5549067: Used NVL around PO_LINES_INT.retained_amount
295 FORALL i IN 1 .. p_line_location_id_tbl.COUNT
296 INSERT INTO PO_SESSION_GT GTT(
297 key,
298 num1,
299 num2
300 )
301 SELECT
302 l_gt_key,
303 p_line_location_id_tbl(i),
304 NVL2(POL.max_retainage_amount,
305 least(p_line_loc_match_amt_tbl(i) * (POL.retainage_rate/100),POL.max_retainage_amount - nvl(PO_LINES_INT.retained_amount,0)),
306 p_line_loc_match_amt_tbl(i) * (POL.retainage_rate/100)
307 )
308 FROM PO_LINES_ALL POL,
309 PO_LINE_LOCATIONS_ALL PLL,
310 ( select PLL1.po_line_id ,
311 SUM(PLL1.RETAINAGE_WITHHELD_AMOUNT) retained_amount
312 FROM PO_LINE_LOCATIONS_ALL PLL1
313 WHERE PLL1.po_line_id = (select po_line_id from po_line_locations_all PLL2
314 where PLL2.line_location_id = p_line_location_id_tbl(i))
315 GROUP BY PLL1.po_line_id
316 ) PO_LINES_INT
317 WHERE PLL.line_location_id = p_line_location_id_tbl(i)
318 AND POL.po_line_id = PLL.po_line_id
319 AND PO_LINES_INT.po_line_id = POL.po_line_id
320 ;
321
322 d_position := 40;
323
324 -- Retrieve the results from the GT into the plsql table out param
325 SELECT GTT.num2
326 BULK COLLECT INTO x_amount_to_retain_tbl
327 FROM PO_SESSION_GT GTT
328 WHERE GTT.key = l_gt_key
329 ORDER BY GTT.num1 --input and output tbls have same ordering
330 ;
331
332 d_position := 50;
333
334 -- Clean up the GT by deleting the data
335 DELETE FROM PO_SESSION_GT GTT WHERE GTT.key = l_gt_key;
336
337 d_position := 60;
338
339 IF PO_LOG.d_proc THEN
340 PO_LOG.proc_end(d_mod,'x_return_status',x_return_status);
341 PO_LOG.proc_end(d_mod,'x_amount_to_retain_tbl',x_amount_to_retain_tbl);
342 END IF;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 IF PO_LOG.d_exc THEN
347 PO_LOG.exc(d_mod,d_position,SQLERRM);
348 END IF;
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 FND_MESSAGE.set_name('PO', 'PO_ALL_TRACE_ERROR');
351 FND_MESSAGE.set_token('FILE', 'POXAPINB.pls');
352 FND_MESSAGE.set_token('ERR_NUMBER', SQLERRM(SQLCODE));
353 FND_MESSAGE.set_token('SUBROUTINE', l_api_name);
354 x_msg_data := FND_MESSAGE.get;
355
356 END; --get_amount_to_retain
357 --<Complex Work R12 END>
358
359
360
361 ---------------------------------------------------------------------------------------------
362 --Start of Comments
363 --Name: get_po_ship_amounts
364 --
365 --Pre-reqs: None
366 --
367 --Modifies: None
368 --
369 --Locks: None
370 --
371 --Function: This procedure provides AP with ordered and cancelled amounts on the PO
372 -- shipments for amount matching purposes
373 --
374 --
375 --Parameters:
376 --IN:
377 -- p_api_version
378 -- Specifies the version of the api. Value that needs to be passed inis 1.0
379 -- p_receive_transaction_id
380 -- Specifies the receive transaction id for which the amounts need to be retrieved
381 -- from the corresponding shipments
382 --OUT:
386 -- The cancelled amount on the PO shipment corresponding to the rcv transaction
383 -- x_ship_amt_ordered
384 -- The amount on the PO shipment corresponding to the rcv transaction id passed
385 -- x_ship_amt_cancelled
387 -- id passed
388 -- x_ret_status
389 -- (a) FND_API.G_RET_STS_SUCCESS if successful
390 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
391 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
392 -- x_msg_count
393 -- The number of error messages to be returned (1 in this case)
394 -- x_msg_data
395 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
396 --
397 --Testing: -
398 --End of Comments
399 -------------------------------------------------------------------------------------------------
400
401 PROCEDURE get_po_ship_amounts (p_api_version IN NUMBER,
402 p_receive_transaction_id IN RCV_TRANSACTIONS.transaction_id%TYPE,
403 x_ship_amt_ordered OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount%TYPE,
404 x_ship_amt_cancelled OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount_cancelled%TYPE,
405 x_ret_status OUT NOCOPY VARCHAR2,
406 x_msg_count OUT NOCOPY NUMBER,
407 x_msg_data OUT NOCOPY VARCHAR2) IS
408
409 l_api_name CONSTANT VARCHAR2(30) := 'get_po_ship_amounts';
410 l_api_version CONSTANT NUMBER := 1.0;
411
412 BEGIN
413
414 -- Initialize return status and msg data
415 x_ret_status := FND_API.G_RET_STS_SUCCESS;
416 x_msg_count := 0;
417 x_msg_data := NULL;
418
419 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
420 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 END IF;
422
423 -- SQL What : Gets the amount and amount cancelled on the PO shipments corresponding to the
424 -- Receive transaction_id
425 -- SQL Why : These amounts are used for amount based mathing on receipts
426
427 SELECT pll.amount,
428 pll.amount_cancelled
429 INTO x_ship_amt_ordered,
430 x_ship_amt_cancelled
431 FROM po_line_locations pll,
432 rcv_transactions rt
433 WHERE rt.po_line_location_id = pll.line_location_id
434 AND rt.transaction_id = p_receive_transaction_id;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 END;
440
441
442 -----------------------------------------------------------------------------------------------
443 --Start of Comments
444 --Name: get_po_dist_amounts
445 --
446 --Pre-reqs: None
447 --
448 --Modifies: None
449 --
450 --Locks: None
451 --
452 --Function: This procedure provides AP with ordered and cancelled amounts on the PO
453 -- distributions for amount matching purposes
454 --
455 --
456 --Parameters:
457 --IN:
458 -- p_api_version
459 -- Specifies the version of the api. Value that needs to be passed inis 1.0
460 -- p_po_distribution_id
461 -- Specifies the distributions id for which the amounts need to be retrieved
462 --
463 --OUT:
464 -- x_dist_amt_ordered
465 -- The amount ordered on the PO distribution
466 -- x_dist_amt_cancelled
467 -- The cancelled amount on the PO distribution
468 -- x_ret_status
469 -- (a) FND_API.G_RET_STS_SUCCESS if successful
470 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
471 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
475 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
472 -- x_msg_count
473 -- The number of error messages to be returned (1 in this case)
474 -- x_msg_data
476 --
477 --
478 --Testing: -
479 --End of Comments
480 ----------------------------------------------------------------------------------------
481
482 PROCEDURE get_po_dist_amounts (p_api_version IN NUMBER,
483 p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE,
484 x_dist_amt_ordered OUT NOCOPY PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE,
485 x_dist_amt_cancelled OUT NOCOPY PO_DISTRIBUTIONS_ALL.amount_cancelled%TYPE,
486 x_ret_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2) IS
489
490 l_api_name CONSTANT VARCHAR2(30) := 'get_po_dist_amounts';
491 l_api_version CONSTANT NUMBER := 1.0;
492
493 BEGIN
494
495 -- Initialize return status and msg data
496 x_ret_status := FND_API.G_RET_STS_SUCCESS;
497 x_msg_count := 0;
498 x_msg_data := NULL;
499
500 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
501 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
502 END IF;
503
504 -- SQL What : Gets the amountordered and amount cancelled on the PO distributions
505 -- corresponding to the distribution id passed
506 -- SQL Why : These amounts are used for amount based matching of invoices
507
508 SELECT pod.amount_ordered,
509 pod.amount_cancelled
510 INTO x_dist_amt_ordered,
511 x_dist_amt_cancelled
512 FROM po_distributions pod
513 WHERE pod.po_distribution_id = p_po_distribution_id;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 END;
519
520 ---------------------------------------------------------------------------------------
521 --Start of Comments
522 --Name: update_po_ship_amounts
523 --
524 --Pre-reqs: None
525 --
526 --Modifies: PO_LINE_LOCATIONS_ALL
527 --
528 --Locks: None
529 --
530 --Function: This procedure updates the amount billed on po shipments during amount matching process
531 --
532 --Parameters:
533 --IN:
534 -- p_api_version
535 -- Specifies the version of the api. Value that needs to be passed inis 1.0
536 -- p_po_line_location_id
537 -- Specifies the line location id for which the amounts need updated
538 --
539 --OUT:
540 -- x_dist_amt_billed
541 -- The amount billed to be updated on the PO Shipment
542 -- x_ret_status
543 -- (a) FND_API.G_RET_STS_SUCCESS if successful
544 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
545 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
546 -- x_msg_count
547 -- The number of error messages to be returned (1 in this case)
548 -- x_msg_data
549 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
550 --
551 --
552 --Testing: -
553 --End of Comments
554 ------------------------------------------------------------------------------------------------
555
556 PROCEDURE update_po_ship_amounts (p_api_version IN NUMBER,
557 p_po_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
558 p_ship_amt_billed IN PO_LINE_LOCATIONS_ALL.amount_billed%TYPE,
559 x_ret_status OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2) IS
562
563 l_api_name CONSTANT VARCHAR2(30) := 'update_po_ship_amounts';
564 l_api_version CONSTANT NUMBER := 1.0;
565
566 BEGIN
567
568 -- Initialize return status and msg data
569 x_ret_status := FND_API.G_RET_STS_SUCCESS;
570 x_msg_count := 0;
571 x_msg_data := NULL;
572
573 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
574 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 END IF;
576
577 -- SQL What : Updates the amount billed on the po shipments
578 -- SQL Why : This is the amount that was billed against this shipment
579
580 UPDATE po_line_locations_all
581 SET amount_billed = nvl(amount_billed,0) + nvl(p_ship_amt_billed,0)
582 WHERE line_location_id = p_po_line_location_id;
583
584
585 EXCEPTION
586 WHEN OTHERS THEN
587 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 END;
589
590 -----------------------------------------------------------------------------------------------------
591 --Start of Comments
592 --Name: update_po_dist_amounts
593 --
594 --Pre-reqs: None
595 --
596 --Modifies: PO_DISTRIBUTIONS_ALL
597 --
598 --Locks: None
599 --
600 --Function: This procedure updates the amount billed on po distributions during amount matching process
601 --
602 --
603 --Parameters:
604 --IN:
605 -- p_api_version
606 -- Specifies the version of the api. Value that needs to be passed inis 1.0
607 -- p_po_distribution_id
608 -- Specifies the distributions id for which the amounts need updated
609 --
610 --OUT:
611 -- x_dist_amt_billed
615 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
612 -- The amount billed to be updated on the PO distribution
613 -- x_ret_status
614 -- (a) FND_API.G_RET_STS_SUCCESS if successful
616 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
617 -- x_msg_count
618 -- The number of error messages to be returned (1 in this case)
619 -- x_msg_data
620 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
621 --
622 --Testing: -
623 --End of Comments
624 -------------------------------------------------------------------------------------------------------
625
626 PROCEDURE update_po_dist_amounts (p_api_version IN NUMBER,
627 p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE,
628 p_dist_amt_billed IN PO_DISTRIBUTIONS_ALL.amount_billed%TYPE,
629 x_ret_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2) IS
632
633 l_api_name CONSTANT VARCHAR2(30) := 'update_po_dist_amounts';
634 l_api_version CONSTANT NUMBER := 1.0;
635
636 BEGIN
637
638 -- Initialize return status and msg data
639 x_ret_status := FND_API.G_RET_STS_SUCCESS;
640 x_msg_count := 0;
641 x_msg_data := NULL;
642
643 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) ) THEN
644 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 END IF;
646
647 -- SQL What : Updates the amount billed on the po distribution
648 -- SQL Why : This is the amount that was billed against this distribution
649
650 UPDATE po_distributions_all
651 SET amount_billed = nvl(amount_billed,0) + nvl(p_dist_amt_billed,0)
652 WHERE po_distribution_id = p_po_distribution_id;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 END;
658
659 ---------------------------------------------------------------------------------------
660 --Start of Comments
661 --Created 02/09/04 Sanjay Chitlapilly
662 --
663 --Name: set_final_match_flag
664 --
665 --Pre-reqs: None
666 --
667 --Modifies: PO_LINE_LOCATIONS_ALL
668 --
669 --Locks: None
670 --
671 --Function: This procedure updates the final_match_flag on po shipments when an invoice is finally matched.
672
673 --
674 --Parameters:
675 --IN:
676 -- p_api_version
677 -- Specifies the version of the api. Value that needs to be passed in is 1.0
678 -- p_entity_type
679 -- Possible values: PO_HEADERS, PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
680 -- PO_LINE_LOCATIONS only supported currently.
681 -- p_entity_id_tbl
682 -- This will have one or more ids that you want to set the final match flag value.
683 -- p_final_match_flag
684 -- Possible Values: Y or N
685 -- p_init_msg_list (Optional) (Default FALSE)
686 -- Allows API callers to request the initialization of the message list.
687 -- p_commit (Optional) (Default FALSE)
688 -- Allows API callers to ask the API to commit on their behalf after performing its function.
689 --
690 --OUT:
691 -- x_ret_status
692 -- (a) FND_API.G_RET_STS_SUCCESS if successful
693 -- (b) FND_API.G_RET_STS_ERROR if known error occurs
694 -- (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
695 -- x_msg_count
696 -- The number of error messages to be returned (1 in this case)
697 -- x_msg_data
698 -- The error message if the msg ct > 0 should be retrieved using FND_MSG_PUB.get
699 --
700 --
701 --End of Comments
702 ------------------------------------------------------------------------------------------------
703
704 PROCEDURE set_final_match_flag (p_api_version IN NUMBER ,
705 p_entity_type IN VARCHAR2 ,
706 p_entity_id_tbl IN PO_TBL_NUMBER ,
707 p_final_match_flag IN PO_LINE_LOCATIONS.FINAL_MATCH_FLAG%TYPE ,
708 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
709 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
710 x_ret_status OUT NOCOPY VARCHAR2 ,
711 x_msg_count OUT NOCOPY NUMBER ,
712 x_msg_data OUT NOCOPY VARCHAR2 ) IS
713
714 l_api_name CONSTANT VARCHAR2(30) := 'set_final_match_flag';
715 l_api_version CONSTANT NUMBER := 1.0;
716
717 BEGIN
718
719 -- Standard Start of API savepoint
720 SAVEPOINT set_final_match_flag_PVT;
721
722 -- Initialize message list if p_init_msg_list is set to TRUE.
723 IF FND_API.to_Boolean (p_init_msg_list) THEN
724 FND_MSG_PUB.initialize;
725 END IF;
726
727 -- Initialize return status and msg data
728 x_ret_status := FND_API.G_RET_STS_SUCCESS;
729 x_msg_count := 0;
730 x_msg_data := NULL;
731
732 IF (NOT FND_API.compatible_api_call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)) THEN
733
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735
736 END IF;
737
738 IF p_entity_type <> 'PO_LINE_LOCATIONS' THEN
739
740 RAISE FND_API.G_EXC_ERROR;
741
742 ELSE
743 -- SQL What : Updates the final_match_flag on the po shipments
744 -- SQL Why : This is to indicate the shipment has been finally matched by an invoice.
745
746 FORALL i IN 1 .. p_entity_id_tbl.COUNT
747
748 UPDATE po_line_locations_all
749 SET final_match_flag = p_final_match_flag
750 WHERE line_location_id = p_entity_id_tbl(i);
751
752 END IF;
753
754 -- Standard check of p_commit.
755 IF FND_API.To_Boolean (p_commit) THEN
756 COMMIT;
757 END IF;
758
759 -- Standard call to get message count and if count is 1, get message info.
760 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
761 p_data => x_msg_data );
762
763
764 EXCEPTION
765
766 WHEN FND_API.G_EXC_ERROR THEN
767
768 ROLLBACK TO set_final_match_flag_PVT;
769 x_ret_status := FND_API.G_RET_STS_ERROR ;
770 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
771 p_data => x_msg_data );
772
773
774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
775
776 ROLLBACK TO set_final_match_flag_PVT;
777 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
778 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
779 p_data => x_msg_data );
780
781 WHEN OTHERS THEN
782
783 ROLLBACK TO set_final_match_flag_PVT;
784 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
785 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
786 p_data => x_msg_data );
787
788 END set_final_match_flag;
789
790
791 END PO_AP_INVOICE_MATCH_GRP;
[Home] [Help]