[Home] [Help]
PACKAGE BODY: APPS.PO_CHARGES_GRP
Source
1 PACKAGE BODY PO_CHARGES_GRP AS
2 /* $Header: POXGFSCB.pls 120.16.12010000.8 2010/01/26 00:39:29 vthevark ship $*/
3
4 -- package globals
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_CHARGES_GRP';
6 G_LOG_MODULE CONSTANT VARCHAR2(40) := 'po.plsql.' || G_PKG_NAME;
7 G_CONC_LOG VARCHAR2(32767);
8
9 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790: rcv debug enhancement
10 g_fte_cost_factor_details pon_price_element_types_vl%ROWTYPE:= pon_cf_type_grp.get_cost_factor_details('ORACLE_FTE_COST');
11 g_dummy_rci_tbl RCV_CHARGES_GRP.charge_interface_table_type;
12 g_charge_numbers NUMBER;
13
14 -- Private support procedures
15
16 -- wrapper for asn_debug
17 PROCEDURE string
18 ( log_level IN number
19 , module IN varchar2
20 , message IN varchar2
21 ) IS
22 BEGIN
23 -- add to fnd_log_messages
24 asn_debug.put_line(module||': '||message,log_level);
25 END string;
26
27 -- helper function for testing if a line has been rated in FTE
28 FUNCTION shipment_line_fte_rated(p_shipment_line_id NUMBER) RETURN BOOLEAN IS
29 l_count NUMBER;
30 BEGIN
31 SELECT count(*)
32 INTO l_count
33 FROM po_rcv_charges
34 WHERE shipment_line_id = p_shipment_line_id
35 AND cost_factor_id = -20;
36
37 IF l_count < 1 THEN
38 RETURN FALSE;
39 ELSE
40 RETURN TRUE;
41 END IF;
42 END shipment_line_fte_rated;
43
44 -- Public Procedures
45
46 PROCEDURE Capture_QP_Charges
47 ( p_api_version IN NUMBER
48 , p_init_msg_list IN VARCHAR2
49 , x_return_status OUT NOCOPY VARCHAR2
50 , x_msg_count OUT NOCOPY NUMBER
51 , x_msg_data OUT NOCOPY VARCHAR2
52 , p_group_id IN NUMBER
53 , p_request_id IN NUMBER
54 ) IS --{
55 l_rsh_id_table DBMS_SQL.number_table;
56 l_charge_table CHARGE_TABLE_TYPE;
57 l_charge_alloc_table CHARGE_ALLOCATION_TABLE_TYPE;
58 l_cost_factor_details PON_PRICE_ELEMENT_TYPES_VL%ROWTYPE;
59 k NUMBER;
60 BEGIN
61
62 IF (g_asn_debug = 'Y') THEN
63 asn_debug.put_line('Entering Capture_QP_Charges() for group_id:' || p_group_id
64 || ' and request_id:'|| p_request_id);
65 END IF;
66 k := 1;
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 -- Retrieve QP charges for PO receipts and import ASN
70
71 SELECT shipment_header_id
72 BULK COLLECT INTO l_rsh_id_table
73 FROM (-- po receipts
74 SELECT rt.shipment_header_id
75 FROM rcv_transactions rt,
76 rcv_parameters rp,
77 po_line_locations_all pll -- lcm changes
78 WHERE rt.group_id = DECODE (p_group_id, 0, rt.group_id, p_group_id)
79 AND rt.request_id = p_request_id
80 AND rt.organization_id = rp.organization_id
81 AND rt.transaction_type = 'RECEIVE'
82 AND rt.source_document_code = 'PO'
83 AND rp.advanced_pricing = 'Y'
84 -- to exclude receipts agasint ASN
85 AND (NOT EXISTS (SELECT 1 FROM po_rcv_charges prc
86 WHERE rt.shipment_header_id = prc.shipment_header_id))
87 AND rt.po_line_location_id = pll.line_location_id
88 AND nvl(pll.lcm_flag, 'N') = 'N'
89 UNION
90 -- import ASN
91 SELECT rsh.shipment_header_id
92 FROM rcv_shipment_headers rsh,
93 rcv_headers_interface rhi,
94 rcv_parameters rp
95 WHERE rhi.group_id = DECODE(p_group_id, 0, rhi.group_id, p_group_id)
96 AND rsh.shipment_num = rhi.shipment_num
97 AND rsh.request_id = p_request_id
98 AND rsh.receipt_source_code = 'VENDOR'
99 AND rsh.ship_to_org_id = rp.organization_id
100 AND rp.advanced_pricing = 'Y');
101
102 IF (g_asn_debug = 'Y') THEN
103 asn_debug.put_line('Number of shipment headers retrieved for QP:' || l_rsh_id_table.count);
104 END IF;
105
106 IF l_rsh_id_table.count < 1 THEN
107 IF (g_asn_debug = 'Y') THEN
108 asn_debug.put_line('No shipment found for QP charges -> Return.');
109 END IF;
110 RETURN;
111 END IF;
112
113 FOR i IN l_rsh_id_table.FIRST..l_rsh_id_table.LAST LOOP --{
114
115 IF (g_asn_debug = 'Y') THEN
116 asn_debug.put_line('Inside QP rsh loop for rsh_id_tbl('||i||'): '|| l_rsh_id_table(i));
117 END IF;
118
119 DECLARE
120 l_header_rec PO_ADVANCED_PRICE_PVT.Header_Rec_Type;
121 l_line_rec_table PO_ADVANCED_PRICE_PVT.Line_Tbl_Type;
122 l_freight_charge_tbl PO_ADVANCED_PRICE_PVT.Freight_Charges_Rec_Tbl_Type;
123 l_qp_cost_table PO_ADVANCED_PRICE_PVT.Qp_Price_Result_Rec_Tbl_Type;
124 l_rsh_id RCV_SHIPMENT_HEADERS.shipment_header_id%type;
125 l_rsl_id RCV_SHIPMENT_LINES.shipment_line_id%type;
126 l_currency_code RCV_SHIPMENT_HEADERS.currency_code%type;
127
128 l_line_quantities DBMS_SQL.number_table;
129
130 l_return_status VARCHAR2(1);
131 l_no_qp_charge EXCEPTION;
132 l_no_freight_charge EXCEPTION;
133 l_qp_api_exception EXCEPTION;
134 l_qp_charge_exception EXCEPTION;
135 l_line_level_charge VARCHAR2(1) := 'N'; --Bug 8551844
136 BEGIN
137 l_rsh_id := l_rsh_id_table(i);
138
139 IF (g_asn_debug = 'Y') THEN
140 asn_debug.put_line('Populating l_header_rec');
141 END IF;
142 -- populate l_header_rec
143 SELECT PO_MOAC_UTILS_PVT.get_current_org_id,
144 NULL, --p_order_header_id
145 vendor_id,
146 vendor_site_id,
147 creation_date,
148 NULL, --order_type
149 ship_to_location_id,
150 ship_to_org_id,
151 shipment_header_id,
152 hazard_class,
153 hazard_code,
154 shipped_date,
155 shipment_num,
156 carrier_method,
157 packaging_code,
158 freight_carrier_code,
159 freight_terms,
160 currency_code,
161 conversion_rate,
162 conversion_rate_type,
163 organization_id,
164 expected_receipt_date
165 INTO l_header_rec
166 FROM rcv_shipment_headers
167 WHERE shipment_header_id = l_rsh_id;
168
169 IF (g_asn_debug = 'Y') THEN
170 asn_debug.put_line('Populating l_line_rec');
171 END IF;
172 -- populate l_line_rec_table
173 -- Passing Vendor_id and vendor_site_id so that line level modifiers with qualifiers will be applied.
174 --Bug 8731760, Acquisiton costs are not correct when PO UOM is different from receipt UOM.
175 --So,Passing the quantity to Pricing in PO UOM .
176 SELECT NULL, --order_line_id
177 NULL, --agreement_type
178 NULL, --agreement_id
179 NULL, --agreement_line_id
180 pha.vendor_id, -- Bug 7186657
181 pha.vendor_site_id, --Bug 7186657
182 rsl.ship_to_location_id,
183 NULL, --ship_to_org_id
184 rsl.vendor_item_num,
185 rsl.item_revision,
186 rsl.item_id,
187 NULL, --category_id
188 pha.rate,
189 pha.rate_type,
190 pha.currency_code,
191 plla.need_by_date, --need_by_date
192 rsl.shipment_line_id,
193 rsl.primary_unit_of_measure,
194 rsl.to_organization_id,
195 NVL(pla.unit_meas_lookup_code,plla.unit_meas_lookup_code),
196 rsl.source_document_code,
197 pla.unit_price,
198 ROUND(decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)*
199 po_uom_s.po_uom_convert(rsl.unit_of_measure,NVL(pla.unit_meas_lookup_code,plla.unit_meas_lookup_code),nvl(rsl.item_id,0)),9),
200 NULL --order_type added for pricing enhancement
201 BULK COLLECT INTO l_line_rec_table
202 FROM rcv_shipment_lines rsl,
203 po_lines_all pla,
204 po_headers_all pha,
205 po_line_locations_all plla
206 WHERE rsl.po_line_id = pla.po_line_id
207 AND rsl.po_header_id = pha.po_header_id
208 ANd rsl.po_line_location_id = plla.line_location_id
209 AND rsl.shipment_header_id = l_rsh_id
210 AND nvl(plla.lcm_flag,'N') = 'N'; -- lcm changes
211
212 -- lcm changes
213 IF (l_line_rec_table.COUNT = 0) THEN
214 IF (g_asn_debug = 'Y') THEN
215 asn_debug.put_line('No shipment line found for QP charges -> Return.');
216 END IF;
217 RETURN;
218 END IF;
219
220
221 -- Bug 4776006: Use the currency_code of the first receipt line
222 -- if there is no currency defined on receipt header level.
223 IF l_header_rec.currency_code IS NULL THEN
224 IF (g_asn_debug = 'Y') THEN
225 asn_debug.put_line('Use first receipt line currency_code: '
226 || l_line_rec_table(1).currency_code);
227 END IF;
228 l_header_rec.currency_code := l_line_rec_table(1).currency_code;
229 END IF;
230
231 IF (g_asn_debug = 'Y') THEN
232 asn_debug.put_line('Calling PO_ADVANCED_PRICE_PVT.get_advanced_price');
233 END IF;
234
235 --PO_LOG.enable_logging();
236
237 PO_ADVANCED_PRICE_PVT.get_advanced_price(
238 p_header_rec => l_header_rec
239 ,p_line_rec_tbl => l_line_rec_table
240 ,p_request_type => 'PO'
241 ,p_pricing_event => 'PO_RECEIPT'
242 ,p_has_header_pricing => TRUE
243 ,p_return_price_flag => FALSE
244 ,p_return_freight_flag => TRUE
245 ,x_price_tbl => l_qp_cost_table
246 ,x_return_status => l_return_status);
247
248 IF (g_asn_debug = 'Y') THEN
249 asn_debug.put_line('l_return_status: '|| l_return_status);
250 END IF;
251
252 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
253 RAISE l_qp_api_exception;
254 END IF;
255
256 IF l_qp_cost_table.count < 1 THEN
257 RAISE l_no_qp_charge;
258 END IF;
259
260 IF (g_asn_debug = 'Y') THEN
261 asn_debug.put_line('Number of lines in l_qp_cost_table: ' ||l_qp_cost_table.count);
262 END IF;
263
264 l_currency_code := l_header_rec.currency_code;
265
266 -- prepare the quantities for multiplying with freight charges
267 FOR l in l_line_rec_table.FIRST..l_line_rec_table.LAST LOOP
268 l_line_quantities(l_line_rec_table(l).shipment_line_id) := NVL(l_line_rec_table(l).quantity, 1);
269 END LOOP;
270
271 FOR j IN l_qp_cost_table.FIRST..l_qp_cost_table.LAST LOOP --{
272 IF (g_asn_debug = 'Y') THEN
273 asn_debug.put_line('Retrieving freight charges from l_qp_cost_table('||j||').'
274 ||' with line_id = '|| l_qp_cost_table(j).line_id);
275 END IF;
276
277 BEGIN --{ line loop block
278 IF l_qp_cost_table(j).freight_charge_rec_tbl.COUNT < 1 THEN
279 RAISE l_no_freight_charge;
280 END IF;
281
282 /* Bug 8551844: Checking if there are lines with shipment_header_id = shipment_line_id
283 ** If yes, check then treat it as line level charge */
284 Begin
285 SELECT 'Y'
286 INTO l_line_level_charge
287 FROM rcv_shipment_lines
288 WHERE shipment_header_id = l_rsh_id
289 AND EXISTS (SELECT 1
290 FROM rcv_shipment_lines
291 WHERE shipment_header_id = l_rsh_id
292 AND shipment_line_id = shipment_header_id);
293
294 Exception
295 When Others then
296 l_line_level_charge := 'N';
297 End;
298 /*
299 ** Bug 8551844: Add codition l_qp_cost_table(j).base_unit_price IS NOT null here
300 ** to identify if this is a line level charge or a header level. For line level
301 ** l_qp_cost_table(j).base_unit_price should always be non-null value.
302 */
303 IF l_qp_cost_table(j).line_id <> l_rsh_id OR
304 (l_qp_cost_table(j).line_id = l_rsh_id AND l_line_level_charge = 'Y'
305 AND l_qp_cost_table(j).base_unit_price IS NOT null ) THEN
306 l_rsl_id := l_qp_cost_table(j).line_id;
307 IF (g_asn_debug = 'Y') THEN
308 asn_debug.put_line('This is a line level charge.');
309 END IF;
310 ELSE
311 l_rsl_id :=NULL;
312 IF (g_asn_debug = 'Y') THEN
313 asn_debug.put_line('This is a header level charge.');
314 END IF;
315 END IF;
316
317 l_freight_charge_tbl := l_qp_cost_table(j).freight_charge_rec_tbl;
318 FOR n IN l_freight_charge_tbl.FIRST..l_freight_charge_tbl.LAST LOOP --{
319 IF (g_asn_debug = 'Y') THEN
320 asn_debug.put_line('Getting cost factor detail for cost_factor_id '
321 ||l_freight_charge_tbl(n).charge_type_code);
322 END IF;
323 l_cost_factor_details :=
324 pon_cf_type_grp.get_cost_factor_details(TO_NUMBER(l_freight_charge_tbl(n).charge_type_code));
325
326 SELECT po_rcv_charges_s.NEXTVAL
327 INTO l_charge_table(k).charge_id
328 FROM dual;
329
330 IF (g_asn_debug = 'Y') THEN
331 asn_debug.put_line('charge_id: ' ||l_charge_table(k).charge_id);
332 END IF;
333
334 l_charge_table(k).creation_date := SYSDATE;
335 l_charge_table(k).created_by := FND_GLOBAL.user_id;
336 l_charge_table(k).last_update_date := SYSDATE;
337 l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
338
339 l_charge_table(k).shipment_header_id := l_rsh_id;
340 l_charge_table(k).shipment_line_id := l_rsl_id;
341 l_charge_table(k).currency_code := l_currency_code;
342
343 -- bug 4966430: multiply line level freight charge by quantity
344 IF l_qp_cost_table(j).line_id = l_rsl_id THEN
345 l_charge_table(k).estimated_amount := l_freight_charge_tbl(n).freight_charge * l_line_quantities(l_rsl_id);
346 ELSE
347 l_charge_table(k).estimated_amount := l_freight_charge_tbl(n).freight_charge;
348 END IF;
349
350 /* Bug#6821589:
351 include_in_acquisition_cost is used by Costing team to determine whether they have to
352 include that cost mentioned in po_rcv_charges as acquisition cost. As that value is not
353 passed, 'Acquisition cost report' is not working properly.
354 We need to stamp the cost_acquisition_code got from Cost factor setup table in
355 po_rcv_charges.include_in_acquisition_cost
356 * */
357
358 l_charge_table(k).cost_factor_id := l_cost_factor_details.price_element_type_id;
359 l_charge_table(k).allocation_method := l_cost_factor_details.allocation_basis;
360 l_charge_table(k).cost_component_class_id := l_cost_factor_details.cost_component_class_id;
361 l_charge_table(k).cost_analysis_code := l_cost_factor_details.cost_analysis_code;
362 l_charge_table(k).include_in_acquisition_cost := l_cost_factor_details.cost_acquisition_code;--Bug#6821589
363
364 IF (g_asn_debug = 'Y') THEN
365 asn_debug.put_line('After populating charge_table(:' || k || ')');
366 asn_debug.put_line('shipment_header_id: ' || l_charge_table(k).shipment_header_id );
367 asn_debug.put_line('shipment_line_id: ' || l_charge_table(k).shipment_line_id );
368 asn_debug.put_line('estimated_amount: ' || l_charge_table(k).estimated_amount );
369 asn_debug.put_line('currency_code: ' || l_charge_table(k).currency_code );
370 asn_debug.put_line('cost_factor_id: ' || l_charge_table(k).cost_factor_id );
371 asn_debug.put_line('allocation_method: ' ||l_charge_table(k).allocation_method );
372 asn_debug.put_line('cost_component_class_id: ' || l_charge_table(k).cost_component_class_id );
373 asn_debug.put_line('cost_analysis_code: ' || l_charge_table(k).cost_analysis_code );
374 asn_debug.put_line('include_in_acquisition_cost: ' || l_charge_table(k).include_in_acquisition_cost );--Bug#6821589
375 END IF;
376 k := k + 1; --increment k for every new charge record
377 END LOOP; --} end of l_freight_charge_tbl loop
378 EXCEPTION
379 WHEN l_no_freight_charge THEN
380 IF (g_asn_debug = 'Y') THEN
381 asn_debug.put_line('No QP charge for the line : ' ||l_qp_cost_table(j).line_id);
382 END IF;
383 END; --} end of line loop block
384 END LOOP; --} end of qp_cost_table loop
385 EXCEPTION
386 WHEN l_no_qp_charge THEN
387 IF (g_asn_debug = 'Y') THEN
388 asn_debug.put_line('No QP charge for shipment header: ' ||l_rsh_id_table(i) );
389 END IF;
390 WHEN l_qp_api_exception THEN
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 IF (g_asn_debug = 'Y') THEN
393 asn_debug.put_line('QP API returned error for shipment_id: ' ||l_rsh_id_table(i) );
394 END IF;
395 WHEN others THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 x_msg_data := sqlerrm;
398 IF (g_asn_debug = 'Y') THEN
399 asn_debug.put_line('Unexpected exception occured in QP loop: '|| x_msg_data);
400 END IF;
401
402 END;
403 END LOOP; --} end of rsh_id_table loop
404
405 IF (g_asn_debug = 'Y') THEN
406 asn_debug.put_line('Total number of QP charges retrieved: ' || l_charge_table.COUNT );
407 END IF;
408
409 -- Allocate all the QP charges
410 -- QP charges can be header level or line level
411 RCV_CHARGES_GRP.Allocate_charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
412
413 -- bulk insert po_rcv_charges from the charge table
414 FORALL i IN INDICES OF l_charge_table
415 INSERT INTO po_rcv_charges
416 VALUES l_charge_table(i);
417
418 IF (g_asn_debug = 'Y') THEN
419 asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charges');
420 END IF;
421
422 -- bulk insert po_rcv_charge_allocations from the charge table
423 FORALL i IN INDICES OF l_charge_alloc_table
424 INSERT INTO po_rcv_charge_allocations
425 VALUES l_charge_alloc_table(i);
426
427 IF (g_asn_debug = 'Y') THEN
428 asn_debug.put_line(sql%rowcount || ' rows inserted into po_rcv_charge_allocations');
429 END IF;
430
431 EXCEPTION
432 WHEN OTHERS THEN
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 x_msg_data := sqlerrm;
435 IF (g_asn_debug = 'Y') THEN
436 asn_debug.put_line('Unexpected exception occured in Capture_QP_Charges(): '|| x_msg_data);
437 END IF;
438 END Capture_QP_Charges; --}
439
440 PROCEDURE Capture_FTE_Estimated_Charges
441 ( p_api_version IN NUMBER
442 , p_init_msg_list IN VARCHAR2
443 , x_return_status OUT NOCOPY VARCHAR2
444 , x_msg_count OUT NOCOPY NUMBER
445 , x_msg_data OUT NOCOPY VARCHAR2
446 , p_group_id IN NUMBER
447 , p_request_id IN NUMBER
448 ) IS
449
450 l_rsh_id_table DBMS_SQL.number_table;
451 l_fte_cost_table FTE_PO_INTEGRATION_GRP.fte_receipt_lines_tab;
452 l_charge_table CHARGE_TABLE_TYPE;
453 l_charge_alloc_table CHARGE_ALLOCATION_TABLE_TYPE;
454 l_cost_factor_details PON_PRICE_ELEMENT_TYPES_VL%ROWTYPE;
455 l_precision NUMBER;
456 j INTEGER;
457 k NUMBER;
458
459 BEGIN
460
461 IF (g_asn_debug = 'Y') THEN
462 asn_debug.put_line('Entering Capture_FTE_Estimated_Charges for group_id:' || p_group_id
463 || ' and request_id:'|| p_request_id);
464 END IF;
465
466 k := 1;
467
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469
470 SELECT DISTINCT shipment_header_id
471 BULK COLLECT INTO l_rsh_id_table
472 FROM rcv_transactions rt,
473 rcv_parameters rp,
474 po_line_locations_all pll -- lcm changes
475 WHERE rt.group_id = decode(p_group_id, 0, rt.group_id, p_group_id)
476 AND rt.request_id = p_request_id -- 0 for online mode
477 AND rt.transaction_type = 'RECEIVE'
478 AND rt.source_document_code = 'PO'
479 AND rt.organization_id = rp.organization_id
480 AND rp.transportation_execution = 'Y'
481 AND rt.po_line_location_id = pll.line_location_id
482 AND nvl(pll.lcm_flag, 'N') = 'N';
483
484 IF (g_asn_debug = 'Y') THEN
485 asn_debug.put_line('Number of shipment headers retreived:' || l_rsh_id_table.COUNT);
486 END IF;
487
488 IF l_rsh_id_table.count < 1 THEN
489 IF (g_asn_debug = 'Y') THEN
490 asn_debug.put_line('No shipment found for FTE charges -> Return.');
491 END IF;
492 RETURN;
493 END IF;
494
495 FOR i IN 1..l_rsh_id_table.COUNT LOOP --{
496
497 IF (g_asn_debug = 'Y') THEN
498 asn_debug.put_line('Inside FTE rsh loop for rsh_id_tbl('||i||'): '|| l_rsh_id_table(i));
499 END IF;
500
501 DECLARE
502 l_return_status VARCHAR2(1);
503 l_msg_count NUMBER;
504 l_msg_data VARCHAR2(2400);
505 l_fte_exception EXCEPTION;
506 l_no_fte_charge EXCEPTION;
507 BEGIN
508
509 FTE_PO_INTEGRATION_GRP.get_estimated_rates(
510 p_init_msg_list => FND_API.G_FALSE,
511 p_api_version_number => 1.0,
512 x_msg_count => l_msg_count,
513 x_msg_data => l_msg_data,
514 x_return_status => l_return_status,
515 p_shipment_header_id => l_rsh_id_table(i),
516 x_receipt_lines_tab => l_fte_cost_table);
517
518 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
519 RAISE l_fte_exception;
520 END IF;
521
522 IF (g_asn_debug = 'Y') THEN
523 asn_debug.put_line('Numbers of FTE charges fetched for this rsh: '|| l_fte_cost_table.count);
524 END IF;
525
526 IF l_fte_cost_table.count < 1 THEN
527 RAISE l_no_fte_charge;
528 END IF;
529
530 -- l_fte_cost_table is indexed by rsl_id
531 j:= l_fte_cost_table.FIRST;
532 WHILE j IS NOT NULL
533 LOOP --{
534 IF (g_asn_debug = 'Y') THEN
535 asn_debug.put_line('Processing charge for shipment line: ' ||j);
536 END IF;
537
538 -- get the precision for rounding
539 DECLARE
540 l_ext_precision NUMBER;
541 l_min_acct_unit NUMBER;
542 BEGIN
543 FND_CURRENCY_CACHE.get_info( currency_code => l_fte_cost_table(j).currency_code
544 , precision => l_precision
545 , ext_precision => l_ext_precision
546 , min_acct_unit => l_min_acct_unit
547 );
548 END;
549
550 IF shipment_line_fte_rated(j) THEN
551 -- Update the existing charge with new estimated amount if
552 -- the amount is differnt than that on the original charge.
553 -- This handles partial receipt and add to receipt, where
554 -- currency and vendor remains the same as previous receipt.
555
556 IF (g_asn_debug = 'Y') THEN
557 asn_debug.put_line('FTE charge exists for shipment_line: ' || j);
558 END IF;
559
560 UPDATE po_rcv_charges
561 SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
562 WHERE shipment_line_id = j
563 AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
564
565 IF (g_asn_debug = 'Y') THEN
566 asn_debug.put_line('updated ' || sql%rowcount || ' row in po_rcv_charges');
567 END IF;
568
569 UPDATE po_rcv_charge_allocations
570 SET estimated_amount = ROUND(l_fte_cost_table(j).total_cost, l_precision)
571 WHERE shipment_line_id = j
572 AND estimated_amount <> ROUND(l_fte_cost_table(j).total_cost, l_precision);
573
574 IF (g_asn_debug = 'Y') THEN
575 asn_debug.put_line('updated ' || sql%rowcount || ' row in po_rcv_charge_allocations');
576 END IF;
577 ELSE
578 IF (g_asn_debug = 'Y') THEN
579 asn_debug.put_line('Populating charge_table (' || k ||')');
580 END IF;
581
582 SELECT po_rcv_charges_s.NEXTVAL
583 INTO l_charge_table(k).charge_id
584 FROM dual;
585
586 IF (g_asn_debug = 'Y') THEN
587 asn_debug.put_line('charge_id: ' ||l_charge_table(k).charge_id);
588 END IF;
589
590 l_charge_table(k).creation_date := SYSDATE;
591 l_charge_table(k).created_by := FND_GLOBAL.user_id;
592 l_charge_table(k).last_update_date := SYSDATE;
593 l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
594
595 l_charge_table(k).shipment_header_id := l_rsh_id_table(i);
596 l_charge_table(k).shipment_line_id := l_fte_cost_table(j).rcv_shipment_line_id;
597 l_charge_table(k).currency_code := l_fte_cost_table(j).currency_code;
598 l_charge_table(k).vendor_id := l_fte_cost_table(j).vendor_id;
599 l_charge_table(k).vendor_site_id := l_fte_cost_table(j).vendor_site_id;
600 l_charge_table(k).cost_factor_id := g_fte_cost_factor_details.price_element_type_id;
601 l_charge_table(k).allocation_method := g_fte_cost_factor_details.allocation_basis;
602 l_charge_table(k).cost_component_class_id := g_fte_cost_factor_details.cost_component_class_id;
603 l_charge_table(k).cost_analysis_code := g_fte_cost_factor_details.cost_analysis_code;
604 l_charge_table(k).include_in_acquisition_cost := g_fte_cost_factor_details.cost_acquisition_code;--Bug#6821589
605 l_charge_table(k).estimated_amount := ROUND(l_fte_cost_table(j).total_cost, l_precision);
606
607 IF (g_asn_debug = 'Y') THEN
608 asn_debug.put_line('currency_precision: ' || l_precision);
609 asn_debug.put_line('shipment_header_id: ' || l_charge_table(k).shipment_header_id );
610 asn_debug.put_line('shipment_line_id: ' || l_charge_table(k).shipment_line_id );
611 asn_debug.put_line('estimated_amount: ' || l_charge_table(k).estimated_amount );
612 asn_debug.put_line('currency_code: ' || l_charge_table(k).currency_code );
613 asn_debug.put_line('vendor_id: ' || l_charge_table(k).vendor_id);
614 asn_debug.put_line('vendor_site_id: ' || l_charge_table(k).vendor_site_id);
615 asn_debug.put_line('cost_factor_id: ' || l_charge_table(k).cost_factor_id );
616 asn_debug.put_line('allocation_method: ' ||l_charge_table(k).allocation_method );
617 asn_debug.put_line('cost_component_class_id: ' || l_charge_table(k).cost_component_class_id );
618 asn_debug.put_line('cost_analysis_code: ' || l_charge_table(k).cost_analysis_code );
619 asn_debug.put_line('include_in_acquisition_cost: ' || l_charge_table(k).include_in_acquisition_cost );--Bug#6821589
620 END IF;
621
622 IF (g_asn_debug = 'Y') THEN
623 asn_debug.put_line('Done populating charge_table (' || k || ')');
624 END IF;
625
626 k := k + 1;
627 END IF;
628 j := l_fte_cost_table.NEXT(j);
629 END LOOP; --} end of fte_cost_table loop
630 EXCEPTION
631 WHEN l_no_fte_charge THEN
632 IF (g_asn_debug = 'Y') THEN
633 asn_debug.put_line('No FTE charges retreived for shipment_id: '||l_rsh_id_table(i));
634 END IF;
635 WHEN l_fte_exception THEN
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 x_msg_data := l_msg_data;
638 IF (g_asn_debug = 'Y') THEN
639 asn_debug.put_line('FTE API failed for shipment_id: '||l_rsh_id_table(i)||'. msg_data: '|| l_msg_data );
640 END IF;
641 WHEN others THEN
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 x_msg_data := sqlerrm;
644 IF (g_asn_debug = 'Y') THEN
645 asn_debug.put_line('Unexpected exception occured in FTE loop: '|| x_msg_data);
646 END IF;
647 END;
648 END LOOP; --} end of rsh_id_table loop
649
650 IF (g_asn_debug = 'Y') THEN
651 asn_debug.put_line('Total number of FTE charges retrieved: ' || l_charge_table.COUNT );
652 END IF;
653
654 -- Allocate all the FTE charges
655 -- FTE charges are always line level charges
656 RCV_CHARGES_GRP.Allocate_Charges(l_charge_table, l_charge_alloc_table, g_dummy_rci_tbl);
657
658 -- bulk insert po_rcv_charges from the charge table
659 FORALL i IN INDICES OF l_charge_table
660 INSERT INTO po_rcv_charges
661 VALUES l_charge_table(i);
662
663 IF (g_asn_debug = 'Y') THEN
664 asn_debug.put_line('Done bulk insert into po_rcv_charges');
665 END IF;
666
667 -- bulk insert po_rcv_charge_allocations from the charge table
668 FORALL i IN INDICES OF l_charge_alloc_table
669 INSERT INTO po_rcv_charge_allocations
670 VALUES l_charge_alloc_table(i);
671
672 IF (g_asn_debug = 'Y') THEN
673 asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
674 END IF;
675
676 IF (g_asn_debug = 'Y') THEN
677 asn_debug.put_line('Exit Capture_FTE_Estimated_Charges()');
678 END IF;
679 EXCEPTION
680 WHEN OTHERS THEN
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 x_msg_data := sqlerrm;
683 IF (g_asn_debug = 'Y') THEN
684 asn_debug.put_line('Unexpected exception occured in Capture_FTE_Estimated_Charges(): ' || x_msg_data);
685 END IF;
686 END Capture_FTE_Estimated_Charges;
687
688 -- capture the actual freight charge for each shipment_line upon bill approval
689 PROCEDURE Capture_FTE_Actual_Charges
690 ( p_api_version IN NUMBER
691 , p_init_msg_list IN VARCHAR2
692 , x_return_status OUT NOCOPY VARCHAR2
693 , x_msg_count OUT NOCOPY NUMBER
694 , x_msg_data OUT NOCOPY VARCHAR2
695 , p_fte_actual_charge IN PO_RCV_CHARGES%ROWTYPE
696 ) IS
697
698 l_new_fte_charge VARCHAR2(1) := 'Y';
699 l_shipment_line_id NUMBER;
700 l_fte_actual_charges CHARGE_TABLE_TYPE;
701 l_fte_actual_charge_allocs CHARGE_ALLOCATION_TABLE_TYPE;
702 l_invalid_shipment EXCEPTION;
703
704 BEGIN
705 IF (g_asn_debug = 'Y') THEN
706 asn_debug.put_line('Entering Capture_FTE_Actual_Charges()');
707 END IF;
708
709 SAVEPOINT PO_FTE_ACTUAL;
710
711 -- Initialize message list if p_init_msg_list is set to TRUE.
712 IF FND_API.to_Boolean( p_init_msg_list )
713 THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 IF (g_asn_debug = 'Y') THEN
718 asn_debug.put_line('Initialized FND message');
719 END IF;
720
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 x_msg_count := 0;
723 x_msg_data := '';
724
725 IF (g_asn_debug = 'Y') THEN
726 asn_debug.put_line('Data passed in by FTE');
727 asn_debug.put_line('shipment_line_id: ' || p_fte_actual_charge.shipment_line_id );
728 asn_debug.put_line('actual_amount: ' || p_fte_actual_charge.actual_amount );
729 asn_debug.put_line('currency_code: ' || p_fte_actual_charge.currency_code );
730 asn_debug.put_line('vendor_id: ' || p_fte_actual_charge.vendor_id);
731 asn_debug.put_line('vendor_site_id: ' || p_fte_actual_charge.vendor_site_id);
732 asn_debug.put_line('cost_factor_id: ' || g_fte_cost_factor_details.price_element_type_id);
733 END IF;
734
735 -- The actual charge is a previously FTE-estimated charge if:
736 -- shipment_line_id matches an existing FTE charge
737
738 SELECT decode(count(*), 0, 'Y', 'N')
739 INTO l_new_fte_charge
740 FROM po_rcv_charges
741 WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id
742 AND cost_factor_id = g_fte_cost_factor_details.price_element_type_id
743 -- if vendor_id/vendor_site_id is null, consider as an existing
744 -- charge if cost type and rsl is matched.
745 AND NVL(vendor_id, p_fte_actual_charge.vendor_id)
746 = p_fte_actual_charge.vendor_id
747 AND NVL(vendor_site_id, p_fte_actual_charge.vendor_site_id)
748 = p_fte_actual_charge.vendor_site_id;
749
750
751 IF (g_asn_debug = 'Y') THEN
752 asn_debug.put_line('New FTE Charge? : ' || l_new_fte_charge);
753 END IF;
754
755 -- For existing FTE charges, update the charge with the actual cost.
756 IF l_new_fte_charge = 'N' THEN
757
758 IF (g_asn_debug = 'Y') THEN
759 asn_debug.put_line('Updating PO_RCV_CHARGES with actual amount: '
760 || p_fte_actual_charge.actual_amount);
761 END IF;
762
763 UPDATE po_rcv_charges
764 SET actual_amount = p_fte_actual_charge.actual_amount
765 , vendor_id = NVL(vendor_id, p_fte_actual_charge.vendor_id)
766 , vendor_site_id = NVL(vendor_site_id, p_fte_actual_charge.vendor_site_id)
767 WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id;
768
769 IF (g_asn_debug = 'Y') THEN
770 asn_debug.put_line('Updating PO_RCV_CHARGE_ALLOCATIONS with actual amount'||
771 p_fte_actual_charge.actual_amount);
772 END IF;
773
774 UPDATE po_rcv_charge_allocations
775 SET actual_amount = p_fte_actual_charge.actual_amount
776 WHERE shipment_line_id = p_fte_actual_charge.shipment_line_id;
777
778 IF (g_asn_debug = 'Y') THEN
779 asn_debug.put_line('Done updating actual amounts for existing charge');
780 END IF;
781 -- For new charges, populate new rows in po_rcv_charges and po_rcv_charge_allocations.
782 ELSE
783 IF (g_asn_debug = 'Y') THEN
784 asn_debug.put_line('This is a new charge');
785 END IF;
786
787 l_fte_actual_charges(1) := p_fte_actual_charge;
788
789 SELECT po_rcv_charges_s.nextval
790 INTO l_fte_actual_charges(1).charge_id
791 FROM dual;
792
793 SELECT shipment_header_id
794 INTO l_fte_actual_charges(1).shipment_header_id
795 FROM rcv_shipment_lines
796 WHERE shipment_line_id = l_fte_actual_charges(1).shipment_line_id;
797
798 IF l_fte_actual_charges(1).shipment_header_id IS NULL THEN
799 RAISE l_invalid_shipment;
800 END IF;
801
802 l_fte_actual_charges(1).creation_date := SYSDATE;
803 l_fte_actual_charges(1).created_by := FND_GLOBAL.user_id;
804 l_fte_actual_charges(1).last_update_date := SYSDATE;
805 l_fte_actual_charges(1).last_updated_by := FND_GLOBAL.user_id;
806
807 l_fte_actual_charges(1).cost_factor_id := g_fte_cost_factor_details.price_element_type_id;
808 l_fte_actual_charges(1).allocation_method := g_fte_cost_factor_details.allocation_basis;
809 l_fte_actual_charges(1).cost_component_class_id := g_fte_cost_factor_details.cost_component_class_id;
810 l_fte_actual_charges(1).cost_analysis_code := g_fte_cost_factor_details.cost_analysis_code;
811 l_fte_actual_charges(1).include_in_acquisition_cost := g_fte_cost_factor_details.cost_acquisition_code;--Bug#6821589
812
813 IF (g_asn_debug = 'Y') THEN
814 asn_debug.put_line('shipment_header_id: ' || l_fte_actual_charges(1).shipment_header_id );
815 asn_debug.put_line('shipment_line_id: ' || l_fte_actual_charges(1).shipment_line_id );
816 asn_debug.put_line('actual_amount: ' || l_fte_actual_charges(1).actual_amount );
817 asn_debug.put_line('currency_code: ' || l_fte_actual_charges(1).currency_code );
818 asn_debug.put_line('vendor_id: ' || l_fte_actual_charges(1).vendor_id);
819 asn_debug.put_line('vendor_site_id: ' || l_fte_actual_charges(1).vendor_site_id);
820 asn_debug.put_line('cost_factor_id: ' || l_fte_actual_charges(1).cost_factor_id );
821 asn_debug.put_line('allocation_method: ' ||l_fte_actual_charges(1).allocation_method );
822 asn_debug.put_line('cost_component_class_id: ' || l_fte_actual_charges(1).cost_component_class_id );
823 asn_debug.put_line('cost_analysis_code: ' || l_fte_actual_charges(1).cost_analysis_code );
824 asn_debug.put_line('include_in_acquisition_cost: ' || l_fte_actual_charges(1).include_in_acquisition_cost );--Bug#6821589
825 END IF;
826
827 RCV_CHARGES_GRP.Allocate_charges(l_fte_actual_charges, l_fte_actual_charge_allocs, g_dummy_rci_tbl);
828
829 -- Using charge table insetead of po_rcv_charge rowtype because GSCC doesn't
830 -- like insert without column list unless for bulk insert
831 FORALL i in 1..l_fte_actual_charges.COUNT
832 INSERT INTO po_rcv_charges
833 VALUES l_fte_actual_charges(i);
834
835 IF (g_asn_debug = 'Y') THEN
836 asn_debug.put_line('Done bulk insert into po_rcv_charges');
837 END IF;
838
839 -- bulk insert po_rcv_charge_allocations from the charge table
840 FORALL i IN 1..l_fte_actual_charge_allocs.COUNT
841 INSERT INTO po_rcv_charge_allocations
842 VALUES l_fte_actual_charge_allocs(i);
843
844 IF (g_asn_debug = 'Y') THEN
845 asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
846 END IF;
847
848 END IF;
849
850 FND_MSG_PUB.Count_And_Get
851 (
852 p_count => x_msg_count,
853 p_data => x_msg_data,
854 p_encoded => FND_API.G_FALSE
855 );
856
857 EXCEPTION
858 WHEN l_invalid_shipment THEN
859 x_msg_data := 'Invalid Shipment';
860 x_return_status:= FND_API.G_RET_STS_ERROR;
861 IF (g_asn_debug = 'Y') THEN
862 asn_debug.put_line('Invalid shipment.' );
863 END IF;
864 WHEN OTHERS THEN
865 ROLLBACK TO PO_FTE_ACTUAL;
866 x_msg_data := sqlerrm;
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
868 IF (g_asn_debug = 'Y') THEN
869 asn_debug.put_line('Unexpected error in Capture_FTE_Actual_Charges(), err_msg:'
870 || x_msg_data);
871 END IF;
872 END Capture_FTE_Actual_Charges;
873
874
875 PROCEDURE Extract_AP_Actual_Charges
876 ( errbuf OUT NOCOPY VARCHAR2
877 , retcode OUT NOCOPY VARCHAR2
878 )
879 IS
880 l_charge_table CHARGE_TABLE_TYPE;
881 l_charge_alloc_table CHARGE_ALLOCATION_TABLE_TYPE;
882 k NUMBER;
883
884 BEGIN
885
886 G_CONC_LOG := '';
887 g_charge_numbers := 0;
888 k := 1;
889
890 IF (g_asn_debug = 'Y') THEN
891 asn_debug.put_line('Entering extract AP charges program.');
892 END IF;
893
894
895 FOR l_ap_po_charge_distribution in po_charges_grp.ap_po_charge_distributions_csr LOOP --{
896
897 IF (g_asn_debug = 'Y') THEN
898 asn_debug.put_line('Inside the cursor loop.');
899 asn_debug.put_line('Charge cursor has '||
900 po_charges_grp.ap_po_charge_distributions_csr%rowcount||' rows.');
901 END IF;
902
903 Process_AP_Actual_Charges(l_ap_po_charge_distribution,l_charge_table,
904 l_charge_alloc_table ,k);
905
906 g_charge_numbers := g_charge_numbers + 1;
907 END LOOP; --}
908
909 IF (g_asn_debug = 'Y') THEN
910 asn_debug.put_line('After Loop ' || g_charge_numbers||
911 ' AP charges after l_ap_po_charge_distribution, '
912 ||l_charge_table.COUNT||' of them are new charges');
913 END IF;
914
915
916 FOR l_ap_rcv_charge_distribution in po_charges_grp.ap_rcv_charge_distr_csr LOOP --{
917
918 IF (g_asn_debug = 'Y') THEN
919 asn_debug.put_line('Inside the cursor loop.');
920 asn_debug.put_line('Charge cursor has '||
921 po_charges_grp.ap_rcv_charge_distr_csr%rowcount||' rows.');
922 END IF;
923
924 Process_AP_Actual_Charges(l_ap_rcv_charge_distribution,
925 l_charge_table,l_charge_alloc_table ,k);
926
927 g_charge_numbers := g_charge_numbers + 1;
928 END LOOP; --}
929
930
931 IF (g_asn_debug = 'Y') THEN
932 asn_debug.put_line('After Loop ' || g_charge_numbers||
933 ' AP charges after l_ap_rcv_charge_distribution, '
934 ||l_charge_table.COUNT||' of them are new charges');
935 END IF;
936
937
938 -- bulk insert po_rcv_charges from the charge table
939 FORALL i IN 1..l_charge_table.COUNT
940 INSERT INTO po_rcv_charges
941 VALUES l_charge_table(i);
942
943 IF (g_asn_debug = 'Y') THEN
944 asn_debug.put_line('Done bulk insert into po_rcv_charges');
945 END IF;
946
947 -- bulk insert po_rcv_charge_allocations from the charge table
948 FORALL i IN 1..l_charge_alloc_table.COUNT
949 INSERT INTO po_rcv_charge_allocations
950 VALUES l_charge_alloc_table(i);
951
952 IF (g_asn_debug = 'Y') THEN
953 asn_debug.put_line('Done bulk insert into po_rcv_charge_allocations');
954 END IF;
955
956 -- generate summary report
957 G_CONC_LOG := G_CONC_LOG || 'Summary information: ' ||
958 FND_GLOBAL.local_chr(10) ||
959 ' AP charge extracted: ' || g_charge_numbers ||
960 FND_GLOBAL.local_chr(10);
961
962 -- send output to concurrent log
963 errbuf := G_CONC_LOG;
964 retcode := 0;
965
966 IF (g_asn_debug = 'Y') THEN
967 asn_debug.put_line('AP extracting completed');
968 END IF;
969 EXCEPTION
970 WHEN OTHERS THEN
971 string( log_level => FND_LOG.LEVEL_UNEXPECTED
972 , module => G_LOG_MODULE
973 , message => 'Error in PO_CHARGES_GRP.Extract_AP_Actual_Charges: ' || SQLERRM
974 );
975 G_CONC_LOG := G_CONC_LOG || 'AP Charge Extraction failed'|| FND_GLOBAL.local_chr(10);
976 errbuf := G_CONC_LOG;
977 retcode := 2;
978 CLOSE po_charges_grp.ap_po_charge_distributions_csr;
979 CLOSE po_charges_grp.ap_rcv_charge_distr_csr;
980
981 END Extract_AP_Actual_Charges;
982
983 PROCEDURE Process_AP_Actual_Charges
984 (
985 l_ap_charge_distribution IN OUT NOCOPY po_charges_grp.ap_po_charge_distributions_csr%ROWTYPE,
986 l_charge_table IN OUT NOCOPY CHARGE_TABLE_TYPE,
987 l_charge_alloc_table IN OUT NOCOPY CHARGE_ALLOCATION_TABLE_TYPE,
988 k IN OUT NOCOPY number
989 )
990
991 IS
992
993 l_charge_id po_rcv_charges.charge_id%TYPE;
994 l_charge_allocation_id po_rcv_charge_allocations.charge_allocation_id%TYPE;
995 l_cost_factor_details pon_price_element_types_vl%ROWTYPE;
996 l_new_ap_charge VARCHAR2(1) := 'Y';
997
998 BEGIN
999
1000 IF (g_asn_debug = 'Y') THEN
1001 asn_debug.put_line('Entering Process AP charges program.');
1002 END IF;
1003
1004 select sum(amount)
1005 into l_ap_charge_distribution.rec_tax
1006 from ap_invoice_distributions_all where
1007 line_type_lookup_code = 'TAX' and
1008 charge_applicable_to_dist_id = l_ap_charge_distribution.invoice_distribution_id;
1009
1010 select sum(amount)
1011 into l_ap_charge_distribution.nonrec_tax
1012 from ap_invoice_distributions_all where
1013 line_type_lookup_code = 'NONREC_TAX' and
1014 charge_applicable_to_dist_id = l_ap_charge_distribution.invoice_distribution_id;
1015
1016
1017 IF (g_asn_debug = 'Y') THEN
1018 asn_debug.put_line('costfactor id '||l_ap_charge_distribution.cost_factor_id);
1019 END IF;
1020
1021 If (l_ap_charge_distribution.cost_factor_id = 0 ) then
1022 l_cost_factor_details := pon_cf_type_grp.get_cost_factor_details( l_ap_charge_distribution.cost_factor_code );
1023 else
1024 l_cost_factor_details := pon_cf_type_grp.get_cost_factor_details( l_ap_charge_distribution.cost_factor_id );
1025 end if;
1026
1027 IF (g_asn_debug = 'Y') THEN
1028 asn_debug.put_line('price_element_type_id id '||l_cost_factor_details.price_element_type_id);
1029 END IF;
1030
1031 SELECT decode(count(*), 0, 'Y', 'N')
1032 INTO l_new_ap_charge
1033 FROM po_rcv_charges
1034 WHERE cost_factor_id = l_cost_factor_details.price_element_type_id
1035 AND shipment_header_id = l_ap_charge_distribution.shipment_header_id
1036 AND NVL(vendor_id, l_ap_charge_distribution.vendor_id)
1037 = l_ap_charge_distribution.vendor_id
1038 AND NVL(vendor_site_id, l_ap_charge_distribution.vendor_site_id)
1039 = l_ap_charge_distribution.vendor_site_id;
1040
1041 IF (g_asn_debug = 'Y') THEN
1042 asn_debug.put_line('Is it a new AP charge? : ' || l_new_ap_charge);
1043 END IF;
1044
1045 IF l_new_ap_charge = 'N' THEN --{
1046
1047 IF (g_asn_debug = 'Y') THEN
1048 asn_debug.put_line('Updating charge amount for existing charge');
1049 END IF;
1050
1051 UPDATE po_rcv_charges
1052 SET actual_amount = nvl(actual_amount, 0) + l_ap_charge_distribution.amount
1053 , actual_tax = nvl(actual_tax, 0) +
1054 l_ap_charge_distribution.rec_tax + l_ap_charge_distribution.nonrec_tax
1055 , vendor_id = NVL(vendor_id, l_ap_charge_distribution.vendor_id)
1056 , vendor_site_id = NVL(vendor_site_id, l_ap_charge_distribution.vendor_site_id)
1057 WHERE cost_factor_id = l_cost_factor_details.price_element_type_id
1058 AND shipment_line_id = l_ap_charge_distribution.shipment_line_id
1059 RETURNING charge_id INTO l_charge_id;
1060
1061 IF (g_asn_debug = 'Y') THEN
1062 asn_debug.put_line('Updated PRC (charge_id=' || l_charge_id || ') with amount'||l_ap_charge_distribution.amount);
1063 END IF;
1064
1065 UPDATE po_rcv_charge_allocations
1066 SET actual_amount = nvl(actual_amount,0) + l_ap_charge_distribution.amount
1067 , act_recoverable_tax = l_ap_charge_distribution.rec_tax
1068 , act_non_recoverable_tax = l_ap_charge_distribution.nonrec_tax
1069 WHERE shipment_line_id = l_ap_charge_distribution.shipment_line_id
1070 AND charge_id = l_charge_id;
1071
1072 IF (g_asn_debug = 'Y') THEN
1073 asn_debug.put_line('Updated corresponding PRCA with amount '||l_ap_charge_distribution.amount);
1074 END IF;
1075 ELSE --}{
1076 IF (g_asn_debug = 'Y') THEN
1077 asn_debug.put_line('Populating charge_table (' || k ||') for a new charge');
1078 END IF;
1079
1080 SELECT po_rcv_charges_s.NEXTVAL
1081 INTO l_charge_table(k).charge_id
1082 FROM dual;
1083
1084 IF (g_asn_debug = 'Y') THEN
1085 asn_debug.put_line('charge_id: ' ||l_charge_table(k).charge_id);
1086 END IF;
1087
1088 l_charge_table(k).creation_date := SYSDATE;
1089 l_charge_table(k).created_by := FND_GLOBAL.user_id;
1090 l_charge_table(k).last_update_date := SYSDATE;
1091 l_charge_table(k).last_updated_by := FND_GLOBAL.user_id;
1092
1093 l_charge_table(k).shipment_header_id := l_ap_charge_distribution.shipment_header_id;
1094 l_charge_table(k).shipment_line_id := l_ap_charge_distribution.shipment_line_id;
1095 l_charge_table(k).actual_amount := l_ap_charge_distribution.amount;
1096 l_charge_table(k).currency_code := l_ap_charge_distribution.currency_code;
1097 l_charge_table(k).vendor_id := l_ap_charge_distribution.vendor_id;
1098 l_charge_table(k).vendor_site_id := l_ap_charge_distribution.vendor_site_id;
1099
1100 l_charge_table(k).cost_factor_id := l_cost_factor_details.price_element_type_id;
1101 l_charge_table(k).allocation_method := l_cost_factor_details.allocation_basis;
1102 l_charge_table(k).cost_component_class_id := l_cost_factor_details.cost_component_class_id;
1103 l_charge_table(k).cost_analysis_code := l_cost_factor_details.cost_analysis_code;
1104 l_charge_table(k).include_in_acquisition_cost := l_cost_factor_details.cost_acquisition_code;--Bug#6821589
1105
1106 l_charge_table(k).actual_tax :=
1107 l_ap_charge_distribution.rec_tax + l_ap_charge_distribution.nonrec_tax;
1108
1109 IF (g_asn_debug = 'Y') THEN
1110 asn_debug.put_line('shipment_header_id: ' || l_charge_table(k).shipment_header_id );
1111 asn_debug.put_line('shipment_line_id: ' || l_charge_table(k).shipment_line_id );
1112 asn_debug.put_line('estimated_amount: ' || l_charge_table(k).estimated_amount );
1113 asn_debug.put_line('currency_code: ' || l_charge_table(k).currency_code );
1114 asn_debug.put_line('vendor_id: ' || l_charge_table(k).vendor_id);
1115 asn_debug.put_line('vendor_site_id: ' || l_charge_table(k).vendor_site_id);
1116 asn_debug.put_line('cost_factor_id: ' || l_charge_table(k).cost_factor_id );
1117 asn_debug.put_line('allocation_method: ' ||l_charge_table(k).allocation_method );
1118 asn_debug.put_line('cost_component_class_id: ' || l_charge_table(k).cost_component_class_id );
1119 asn_debug.put_line('cost_analysis_code: ' || l_charge_table(k).cost_analysis_code );
1120 asn_debug.put_line('include_in_acquisition_cost: ' || l_charge_table(k).include_in_acquisition_cost );--Bug#6821589
1121 END IF;
1122
1123 IF (g_asn_debug = 'Y') THEN
1124 asn_debug.put_line('Done populating charge_table (' || k || ')');
1125 END IF;
1126
1127 SELECT po_rcv_charge_allocations_s.NEXTVAL
1128 INTO l_charge_alloc_table(k).charge_allocation_id
1129 FROM dual;
1130
1131 l_charge_alloc_table(k).creation_date := SYSDATE;
1132 l_charge_alloc_table(k).created_by := FND_GLOBAL.user_id;
1133 l_charge_alloc_table(k).last_update_date := SYSDATE;
1134 l_charge_alloc_table(k).last_updated_by := FND_GLOBAL.user_id;
1135
1136 l_charge_alloc_table(k).charge_id := l_charge_table(k).charge_id;
1137 l_charge_alloc_table(k).shipment_line_id := l_charge_table(k).shipment_line_id;
1138 l_charge_alloc_table(k).actual_amount := l_charge_table(k).actual_amount;
1139
1140 l_charge_alloc_table(k).act_recoverable_tax := l_ap_charge_distribution.rec_tax;
1141 l_charge_alloc_table(k).act_non_recoverable_tax := l_ap_charge_distribution.nonrec_tax;
1142
1143 IF (g_asn_debug = 'Y') THEN
1144 asn_debug.put_line('charge_allocation_id: ' || l_charge_alloc_table(k).charge_allocation_id);
1145 asn_debug.put_line('act_recoverable_tax: ' || l_charge_alloc_table(k).act_recoverable_tax);
1146 asn_debug.put_line('act_non_recoverable_tax: ' || l_charge_alloc_table(k).act_non_recoverable_tax);
1147 END IF;
1148
1149 IF (g_asn_debug = 'Y') THEN
1150 asn_debug.put_line('Done populating charge_alloc_table (' || k || ')');
1151 END IF;
1152
1153 k := k + 1;
1154
1155 IF (g_asn_debug = 'Y') THEN
1156 asn_debug.put_line('Created new charge and allocation record for new charge');
1157 END IF;
1158
1159 END IF; --}
1160
1161 -- update APs flag
1162 UPDATE ap_invoice_distributions_all
1163 SET rcv_charge_addition_flag = 'Y'
1164 WHERE invoice_distribution_id = l_ap_charge_distribution.invoice_distribution_id;
1165
1166
1167 IF (g_asn_debug = 'Y') THEN
1168 asn_debug.put_line('Exit Process AP extracting completed');
1169 END IF;
1170
1171 EXCEPTION
1172 WHEN OTHERS THEN
1173 string( log_level => FND_LOG.LEVEL_UNEXPECTED
1174 , module => G_LOG_MODULE
1175 , message => 'Error in PO_CHARGES_GRP.Process_AP_Actual_Charges: ' || SQLERRM
1176 );
1177
1178 END Process_AP_Actual_Charges;
1179
1180 END PO_CHARGES_GRP;
1181