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