[Home] [Help]
PACKAGE BODY: APPS.RCV_CHARGES_GRP
Source
1 PACKAGE BODY RCV_CHARGES_GRP AS
2 /* $Header: RCVGFSCB.pls 120.11.12010000.2 2010/01/25 22:43:25 vthevark ship $*/
3
4 -- package globals
5 g_base_weight_uom mtl_units_of_measure.unit_of_measure%TYPE;
6 g_base_volume_uom mtl_units_of_measure.unit_of_measure%TYPE;
7 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
8
9 UNKNOWN_ALLOCATION_METHOD EXCEPTION;
10
11 FUNCTION get_base_weight_uom
12 RETURN VARCHAR2
13 IS
14 BEGIN
15 IF g_base_weight_uom IS NULL THEN
16 SELECT unit_of_measure
17 INTO g_base_weight_uom
18 FROM mtl_units_of_measure_tl
19 WHERE base_uom_flag = 'Y'
20 AND uom_class = 'Weight'
21 AND ROWNUM < 2;
22 END IF;
23
24 RETURN g_base_weight_uom;
25 END get_base_weight_uom;
26
27 FUNCTION get_base_volume_uom
28 RETURN VARCHAR2
29 IS
30 BEGIN
31 IF g_base_volume_uom IS NULL THEN
32 SELECT unit_of_measure
33 INTO g_base_volume_uom
34 FROM mtl_units_of_measure_tl
35 WHERE base_uom_flag = 'Y'
36 AND uom_class = 'Volume'
37 AND ROWNUM < 2;
38 END IF;
39
40 RETURN g_base_volume_uom;
41 END get_base_volume_uom;
42
43 PROCEDURE Derive_Cost_Factor
44 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
45 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
46 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
47 ) IS
48 BEGIN
49 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
50 RETURN;
51 END IF;
52
53 IF p_charge_record.cost_factor_id IS NULL
54 AND p_charge_record.cost_factor_code IS NOT NULL
55 THEN
56 p_charge_record.cost_factor_id :=
57 pon_cf_type_grp.get_cost_factor_details(p_charge_record.cost_factor_code).price_element_type_id;
58 END IF;
59 END Derive_Cost_Factor;
60
61 Procedure default_vendor_info
62 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
63 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
64 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
65 ) IS
66 BEGIN
67 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
68 RETURN;
69 END IF;
70
71 IF p_charge_record.parent_interface_id IS NULL THEN
72 -- header-level charge
73 IF p_charge_record.vendor_id IS NULL THEN
74 p_charge_record.vendor_id := p_header_record.vendor_id;
75 END IF;
76
77 IF p_charge_record.vendor_site_id IS NULL THEN
78 p_charge_record.vendor_site_id := p_header_record.vendor_site_id;
79 END IF;
80 ELSE
81 -- line-level charge
82 IF p_charge_record.vendor_id IS NULL THEN
83 p_charge_record.vendor_id := p_transaction_record.vendor_id;
84 END IF;
85
86 IF p_charge_record.vendor_site_id IS NULL THEN
87 p_charge_record.vendor_site_id := p_transaction_record.vendor_site_id;
88 END IF;
89 END IF;
90 END;
91
92 Procedure default_currency_info
93 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
94 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
95 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
96 ) IS
97 BEGIN
98 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
99 RETURN;
100 END IF;
101
102 IF p_charge_record.parent_interface_id IS NOT NULL THEN
103 -- line-level charge
104 IF p_charge_record.currency_code IS NOT NULL
105 AND p_charge_record.currency_conversion_type IS NULL
106 AND p_charge_record.currency_conversion_rate IS NULL
107 AND p_charge_record.currency_conversion_date IS NULL
108 THEN
109 p_charge_record.currency_code := p_transaction_record.currency_code;
110 p_charge_record.currency_conversion_type := p_transaction_record.currency_conversion_type;
111 p_charge_record.currency_conversion_rate := p_transaction_record.currency_conversion_rate;
112 p_charge_record.currency_conversion_date := p_transaction_record.currency_conversion_date;
113 END IF;
114 END IF;
115 END;
116
117 -- make sure cost factor is defined in cost factor setup.
118 Procedure Validate_cost_factor
119 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
120 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
121 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
122 ) IS
123 BEGIN
124 -- errored out already
125 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
126 RETURN;
127 END IF;
128
129 IF pon_cf_type_grp.get_cost_factor_details(p_charge_record.cost_factor_id)
130 .price_element_type_id IS NULL
131 THEN
132 asn_debug.put_line('Invalid cost factor id: ' || p_charge_record.cost_factor_id);
133 rcv_error_pkg.set_error_message('RCV_INVALID_ROI_VALUE');
134 rcv_error_pkg.set_token('COLUMN', 'COST_FACTOR_ID');
135 rcv_error_pkg.set_token('ROI_VALUE', p_charge_record.cost_factor_id);
136 rcv_error_pkg.set_token('SYS_VALUE', '');
137 rcv_error_pkg.log_interface_error( 'RCV_CHARGES_INTERFACE'
138 , 'COST_FACTOR_ID'
139 , FALSE
140 );
141
142 p_charge_record.processing_status_code := 'E';
143 END IF;
144 END;
145
146 -- validate vendor_id and vendor_site_id exist.
147 Procedure Validate_vendor_info
148 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
149 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
150 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
151 ) IS
152 l_vendor_record RCV_SHIPMENT_HEADER_SV.vendorrectype;
153 BEGIN
154 -- errored out already
155 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
156 RETURN;
157 END IF;
158
159 -- validated for item already
160 IF p_charge_record.vendor_id = p_header_record.vendor_id THEN
161 RETURN;
162 END IF;
163
164 -- validate vendor info
165 l_vendor_record.vendor_id := p_charge_record.vendor_id;
166 po_vendors_sv.validate_vendor_info(l_vendor_record);
167
168 IF l_vendor_record.error_record.error_status = 'E' THEN
169 asn_debug.put_line('validate_vendor_info returned error: vendor_id: ' || p_charge_record.vendor_id);
170 IF l_vendor_record.error_record.error_message = 'VEN_DISABLED' THEN
171 asn_debug.put_line('Invalid vendor id: ' || p_charge_record.vendor_id);
172 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_VENDOR');
173 rcv_error_pkg.set_token('VALUE', l_vendor_record.vendor_id);
174 rcv_error_pkg.log_interface_error( 'RCV_CHARGES_INTERFACE'
175 , 'VENDOR_ID'
176 , FALSE
177 );
178 ELSIF l_vendor_record.error_record.error_message = 'VEN_HOLD' THEN
179 asn_debug.put_line('Invalid vendor id: ' || p_charge_record.vendor_id);
180 rcv_error_pkg.set_error_message('PO_PO_VENDOR_ON_HOLD');
181 rcv_error_pkg.set_token('VALUE', l_vendor_record.vendor_id);
182 rcv_error_pkg.log_interface_error( 'RCV_CHARGES_INTERFACE'
183 , 'VENDOR_ID'
184 , FALSE
185 );
186 ELSIF l_vendor_record.error_record.error_message = 'VEN_ID' THEN
187 asn_debug.put_line('Invalid vendor id: ' || p_charge_record.vendor_id);
188 rcv_error_pkg.set_error_message('RCV_VEN_ID');
189 rcv_error_pkg.set_token('SUPPLIER', l_vendor_record.vendor_id);
190 rcv_error_pkg.log_interface_error( 'RCV_CHARGES_INTERFACE'
191 , 'VENDOR_ID'
192 , FALSE
193 );
194 END IF;
195
196 p_charge_record.processing_status_code := 'E';
197 END IF;
198 END Validate_vendor_info;
199
200 Procedure Validate_vendor_site_info
201 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
202 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
203 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
204 ) IS
205 l_vendor_site_record RCV_SHIPMENT_HEADER_SV.vendorsiterectype;
206 BEGIN
207 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
208 RETURN;
209 END IF;
210
211 -- validated for item already
212 IF p_charge_record.vendor_site_id = p_header_record.vendor_site_id THEN
213 RETURN;
214 END IF;
215
216 -- validate vendor site info
217 l_vendor_site_record.vendor_id := p_charge_record.vendor_id;
218 l_vendor_site_record.vendor_site_id := p_charge_record.vendor_site_id;
219 po_vendor_sites_sv.validate_vendor_site_info(l_vendor_site_record);
220
221 IF l_vendor_site_record.error_record.error_status = 'E' THEN
222 asn_debug.put_line('validate_vendor_site_info returned error: vendor_id: ' || p_charge_record.vendor_id || ' vendor_site_id: ' || p_charge_record.vendor_site_id);
223 IF l_vendor_site_record.error_record.error_message IN
224 ('VEN_SITE_ID', 'VEN_SITE_DISABLED', 'VEN_SITE_NOT_PURCH')
225 THEN
226 asn_debug.put_line('Invalid vendor site id: ' || p_charge_record.vendor_site_id);
227 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_VENDOR_SITE');
228 rcv_error_pkg.set_token('VALUE', l_vendor_site_record.vendor_site_id);
229 rcv_error_pkg.log_interface_error( 'RCV_HEADERS_INTERFACE'
230 , 'VENDOR_SITE_ID'
231 , FALSE
232 );
233 END IF;
234
235 p_charge_record.processing_status_code := 'E';
236 END IF;
237 END Validate_vendor_site_info;
238
239 -- validate positive amount
240 Procedure Validate_amount
241 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
242 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
243 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
244 ) IS
245 BEGIN
246 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
247 RETURN;
248 END IF;
249
250 IF p_charge_record.amount <= 0 THEN
251 rcv_error_pkg.set_error_message('RCV_ROI_INVALID_VALUE');
252 rcv_error_pkg.set_token('COLUMN', 'AMOUNT');
253 rcv_error_pkg.set_token('ROI_VALUE', p_charge_record.amount);
254 rcv_error_pkg.set_token('SYS_VALUE', 'a positive value');
255 rcv_error_pkg.log_interface_error( 'RCV_CHARGES_INTERFACE'
256 , 'ESTIMATED_AMOUNT'
257 , FALSE);
258
259 p_charge_record.processing_status_code := 'E';
260 END IF;
261 END;
262
263 -- validate currency code exists in system
264 Procedure Validate_currency_info
265 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
266 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
267 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
268 ) IS
269 l_currency_record rcv_shipment_header_sv.currectype;
270 BEGIN
271 IF p_charge_record.processing_status_code NOT IN ('S','W') THEN
272 RETURN;
273 END IF;
274
275 l_currency_record.currency_code := p_charge_record.currency_code;
276 po_currency_sv.validate_currency_info(l_currency_record);
277
278 IF l_currency_record.error_record.error_status = 'E' THEN
279 IF l_currency_record.error_record.error_message IN
280 ('CURRENCY_DISABLED', 'CURRENCY_INVALID')
281 THEN
282 rcv_error_pkg.set_error_message('PO_PDOI_INVALID_CURRENCY');
283 rcv_error_pkg.set_token('VALUE', l_currency_record.currency_code);
284 rcv_error_pkg.log_interface_error( 'RCV_HEADERS_INTERFACE'
285 , 'CURRENCY_CODE'
286 , FALSE
287 );
288
289 p_charge_record.processing_status_code := 'E';
290 END IF;
291 END IF;
292 END;
293
294 Procedure Derive_Charge_Info
295 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
296 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
297 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
298 ) IS
299 BEGIN
300 derive_cost_factor( p_charge_record
301 , p_header_record
302 , p_transaction_record
303 );
304 END Derive_Charge_Info;
305
306 Procedure Default_charge_info
307 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
308 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
309 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
310 ) IS
311 BEGIN
312 default_vendor_info(p_charge_record, p_header_record, p_transaction_record);
313 default_currency_info(p_charge_record, p_header_record, p_transaction_record);
314 END;
315
316 Procedure Validate_charge_info
317 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
318 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
319 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
320 ) IS
321 BEGIN
322 Validate_cost_factor( p_charge_record, p_header_record, p_transaction_record );
323 Validate_vendor_info( p_charge_record, p_header_record, p_transaction_record );
324 Validate_amount( p_charge_record, p_header_record, p_transaction_record );
325 Validate_currency_info( p_charge_record, p_header_record, p_transaction_record );
326 END;
327
328 Procedure Update_Interface_Charges
329 ( p_charge_interface_table IN charge_interface_table_type
330 ) IS
331 l_interface_charge_id_tbl dbms_utility.number_array;
332 BEGIN
333 FOR i IN 1..p_charge_interface_table.COUNT LOOP
334 l_interface_charge_id_tbl(i) := p_charge_interface_table(i).interface_charge_id;
335 END LOOP;
336
337 FORALL i IN 1..p_charge_interface_table.COUNT
338 UPDATE rcv_charges_interface
339 SET ROW = p_charge_interface_table(i)
340 WHERE interface_charge_id = l_interface_charge_id_tbl(i);
341 END Update_Interface_Charges;
342
343 Procedure Add_Charge_From_Interface
344 ( p_charge_interface IN rcv_charges_interface%ROWTYPE
345 , p_charge_table IN OUT NOCOPY PO_CHARGES_GRP.charge_table_type
346 , p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE
347 , p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE
348 ) IS
349 l_charge po_rcv_charges%ROWTYPE;
350 l_cost_factor_details pon_price_element_types_vl%ROWTYPE;
351 BEGIN
352 SELECT po_rcv_charges_s.NEXTVAL
353 INTO l_charge.charge_id
354 FROM dual;
355
356 l_charge.creation_date := SYSDATE;
357 l_charge.created_by := FND_GLOBAL.user_id;
358 l_charge.last_update_date := SYSDATE;
359 l_charge.last_updated_by := FND_GLOBAL.user_id;
360
361 l_charge.interface_charge_id := p_charge_interface.interface_charge_id;
362 l_charge.shipment_header_id := p_shipment_header_id;
363 IF p_charge_interface.parent_interface_id IS NOT NULL THEN
364 l_charge.shipment_line_id := p_shipment_line_id;
365 END IF;
366
367 l_charge.cost_factor_id := p_charge_interface.cost_factor_id;
368 l_charge.estimated_amount := p_charge_interface.amount;
369 l_charge.vendor_id := p_charge_interface.vendor_id;
370 l_charge.vendor_site_id := p_charge_interface.vendor_site_id;
371 l_charge.currency_code := p_charge_interface.currency_code;
372 l_charge.currency_conversion_type := p_charge_interface.currency_conversion_type;
373 l_charge.currency_conversion_rate := p_charge_interface.currency_conversion_rate;
374 l_charge.currency_conversion_date := p_charge_interface.currency_conversion_date;
375
376 l_cost_factor_details := pon_cf_type_grp.get_cost_factor_details(p_charge_interface.cost_factor_id);
377 l_charge.allocation_method := l_cost_factor_details.allocation_basis;
378 l_charge.cost_component_class_id := l_cost_factor_details.cost_component_class_id;
379 l_charge.cost_analysis_code := l_cost_factor_details.cost_analysis_code;
380 l_charge.include_in_acquisition_cost := l_cost_factor_details.cost_acquisition_code;
381
382 -- add to table at the end to avoid orphaned row on error
383 p_charge_table(p_charge_table.COUNT+1) := l_charge;
384 END Add_Charge_From_Interface;
385
386 PROCEDURE Add_Allocation_From_Charge
387 ( p_charge IN po_rcv_charges%ROWTYPE
388 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
389 ) IS
390 l_charge_allocation po_rcv_charge_allocations%ROWTYPE;
391 BEGIN
392 SELECT po_rcv_charge_allocations_s.NEXTVAL
393 INTO l_charge_allocation.charge_allocation_id
394 FROM dual;
395
396 l_charge_allocation.creation_date := SYSDATE;
397 l_charge_allocation.created_by := FND_GLOBAL.user_id;
398 l_charge_allocation.last_update_date := SYSDATE;
399 l_charge_allocation.last_updated_by := FND_GLOBAL.user_id;
400
401 l_charge_allocation.charge_id := p_charge.charge_id;
402 l_charge_allocation.shipment_line_id := p_charge.shipment_line_id;
403 l_charge_allocation.estimated_amount := p_charge.estimated_amount;
404 l_charge_allocation.actual_amount := p_charge.actual_amount;
405
406 -- l_charge_allocation.est_recoverable_tax
407 -- l_charge_allocation.est_non_recoverable_tax
408 -- l_charge_allocation.act_recoverable_tax
409 -- l_charge_allocation.act_non_recoverable_tax
410
411 -- add to table at the end to avoid orphaned row on error
412 p_charge_allocation_table(p_charge_allocation_table.COUNT+1) := l_charge_allocation;
413 END Add_Allocation_From_Charge;
414
415 PROCEDURE Allocate_Line_Level_Charge
416 ( p_charge IN po_rcv_charges%ROWTYPE
417 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
418 ) IS
419 BEGIN
420 -- all we need to do is instantiate a new allocation based on the charge
421 Add_Allocation_From_Charge
422 ( p_charge
423 , p_charge_allocation_table
424 );
425 END Allocate_Line_Level_Charge;
426
427 PROCEDURE Prorate_Charge
428 ( p_charge IN po_rcv_charges%ROWTYPE
429 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
430 , p_id_table IN dbms_utility.number_array
431 , p_prorate_table IN dbms_utility.number_array
432 ) IS
433 l_total NUMBER := 0;
434 l_ratio NUMBER;
435 l_remaining_estimated_amount NUMBER;
436 l_remaining_actual_amount NUMBER;
437 l_precision NUMBER;
438 j NUMBER;
439 BEGIN
440 asn_debug.put_line('Prorating across ' || p_prorate_table.COUNT || ' rows');
441
442 IF p_id_table.COUNT < 1 OR p_prorate_table.COUNT < 1 THEN
443 RETURN;
444 END IF;
445
446 IF p_id_table.COUNT <> p_prorate_table.COUNT THEN
447 RETURN;
448 END IF;
449
450 -- get the precision for rounding
451 DECLARE
452 l_ext_precision NUMBER;
453 l_min_acct_unit NUMBER;
454 BEGIN
455 FND_CURRENCY_CACHE.get_info( currency_code => p_charge.currency_code
456 , precision => l_precision
457 , ext_precision => l_ext_precision
458 , min_acct_unit => l_min_acct_unit
459 );
460 END;
461
462 -- get the total for proration
463 FOR i IN 1..p_prorate_table.COUNT LOOP
464 l_total := l_total + p_prorate_table(i);
465 END LOOP;
466
467 -- initialize the remaining amounts for the last row
468 l_remaining_estimated_amount := p_charge.estimated_amount;
469 l_remaining_actual_amount := p_charge.actual_amount;
470
471 -- allocate the charge amounts based on the prorate table
472 FOR i IN 1..p_id_table.COUNT LOOP
473 -- default fields from the charge
474 Add_Allocation_From_Charge( p_charge, p_charge_allocation_table );
475
476 -- set the shipment line for this allocation
477 p_charge_allocation_table(p_charge_allocation_table.COUNT).shipment_line_id := p_id_table(i);
478
479 -- assign the prorated amounts
480 l_ratio := p_prorate_table(i) / l_total;
481 p_charge_allocation_table(p_charge_allocation_table.COUNT).estimated_amount :=
482 ROUND( p_charge.estimated_amount * l_ratio
483 , l_precision);
484 p_charge_allocation_table(p_charge_allocation_table.COUNT).actual_amount :=
485 ROUND( p_charge.actual_amount * l_ratio
486 , l_precision);
487 l_remaining_estimated_amount :=
488 l_remaining_estimated_amount - p_charge_allocation_table(p_charge_allocation_table.COUNT).estimated_amount;
489 l_remaining_actual_amount :=
490 l_remaining_actual_amount - p_charge_allocation_table(p_charge_allocation_table.COUNT).actual_amount;
491 END LOOP;
492
493 -- the last row needs to take the remaining amount to keep the sum the same
494 p_charge_allocation_table(p_charge_allocation_table.COUNT).estimated_amount :=
495 p_charge_allocation_table(p_charge_allocation_table.COUNT).estimated_amount + l_remaining_estimated_amount;
496 p_charge_allocation_table(p_charge_allocation_table.COUNT).actual_amount :=
497 p_charge_allocation_table(p_charge_allocation_table.COUNT).actual_amount + l_remaining_actual_amount;
498 END Prorate_Charge;
499
500 PROCEDURE Allocate_Charge_By_Volume
501 ( p_charge IN po_rcv_charges%ROWTYPE
502 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
503 ) IS
504 TYPE uom_table_type IS TABLE OF mtl_units_of_measure.unit_of_measure%TYPE;
505 TYPE uom_class_table_type IS TABLE OF mtl_uom_classes.uom_class%TYPE;
506
507 -- tables of values
508 l_shipment_line_ids dbms_utility.number_array;
509 l_organization_ids dbms_utility.number_array;
510 l_item_ids dbms_utility.number_array;
511 l_quantities dbms_utility.number_array;
512 l_units_of_measure uom_table_type;
513 l_uom_classes uom_class_table_type;
514 l_primary_units_of_measure uom_table_type;
515 l_unit_volumes dbms_utility.number_array;
516 l_volume_uoms uom_table_type;
517 l_volumes dbms_utility.number_array;
518
519 -- simplifying conditions
520 l_all_null_items boolean := TRUE;
521 l_all_volume_uoms boolean := TRUE;
522 l_all_same_item boolean := TRUE;
523 l_all_same_uom boolean := TRUE;
524
525 -- transient variables
526 l_primary_quantity NUMBER;
527 l_base_volume NUMBER;
528 BEGIN
529 -- fetch the relevant shipments
530 SELECT rsl.shipment_line_id
531 , rsl.to_organization_id
532 , rsl.item_id
533 , decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)
534 , rsl.unit_of_measure
535 , muom_rsl.uom_class
536 , msi.primary_unit_of_measure
537 , msi.unit_volume
538 , muom_unit.unit_of_measure
539 BULK COLLECT INTO l_shipment_line_ids
540 , l_organization_ids
541 , l_item_ids
542 , l_quantities
543 , l_units_of_measure
544 , l_uom_classes
545 , l_primary_units_of_measure
546 , l_unit_volumes
547 , l_volume_uoms
548 FROM rcv_shipment_lines rsl
549 , mtl_system_items msi
550 , mtl_units_of_measure muom_rsl
551 , mtl_units_of_measure muom_unit
552 WHERE rsl.shipment_header_id = p_charge.shipment_header_id
553 AND msi.inventory_item_id (+) = rsl.item_id
554 AND msi.organization_id (+) = rsl.to_organization_id
555 AND muom_unit.uom_code (+) = msi.volume_uom_code
556 AND muom_rsl.unit_of_measure = rsl.unit_of_measure;
557
558 FOR i IN 1..l_shipment_line_ids.COUNT LOOP
559 l_all_null_items := l_all_null_items AND l_item_ids(i) IS NULL;
560 l_all_volume_uoms := l_all_volume_uoms AND l_uom_classes(i) = 'Volume';
561
562 IF i > 1 THEN
563 l_all_same_uom := l_all_same_uom AND l_units_of_measure(i) = l_units_of_measure(i-1);
564 l_all_same_item := l_all_same_item
565 AND l_item_ids(i) = l_item_ids(i-1)
566 AND l_organization_ids(i) = l_organization_ids(i-1);
567 END IF;
568 END LOOP;
569
570 -- simplifying cases
571 IF l_all_same_item AND l_all_same_uom THEN
572 Prorate_Charge( p_charge
573 , p_charge_allocation_table
574 , l_shipment_line_ids
575 , l_quantities
576 );
577 RETURN;
578 END IF;
579
580 FOR i IN 1..l_shipment_line_ids.COUNT LOOP
581 -- convert quantity to primary uom and get volume from unit volume
582 IF l_uom_classes(i) <> 'Volume' THEN
583 po_uom_s.uom_convert( l_quantities(i)
584 , l_units_of_measure(i)
585 , l_item_ids(i)
586 , l_primary_units_of_measure(i)
587 , l_primary_quantity
588 );
589 po_uom_s.uom_convert( l_primary_quantity * l_unit_volumes(i)
590 , l_volume_uoms(i)
591 , l_item_ids(i)
592 , get_base_volume_uom
593 , l_volumes(i)
594 );
595 ELSE
596 -- uom is already a volume, use intraclass conversion directly to base volume
597 po_uom_s.uom_convert( l_quantities(i)
598 , l_units_of_measure(i)
599 , l_item_ids(i)
600 , get_base_volume_uom
601 , l_volumes(i)
602 );
603 END IF;
604 END LOOP;
605
606 -- prorate the charge according to the volumes
607 Prorate_Charge( p_charge
608 , p_charge_allocation_table
609 , l_shipment_line_ids
610 , l_volumes
611 );
612 END Allocate_Charge_By_Volume;
613
614 PROCEDURE Allocate_Charge_By_Weight
615 ( p_charge IN po_rcv_charges%ROWTYPE
616 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
617 ) IS
618 TYPE uom_table_type IS TABLE OF mtl_units_of_measure.unit_of_measure%TYPE;
619 TYPE uom_class_table_type IS TABLE OF mtl_uom_classes.uom_class%TYPE;
620
621 -- tables of values
622 l_shipment_line_ids dbms_utility.number_array;
623 l_organization_ids dbms_utility.number_array;
624 l_item_ids dbms_utility.number_array;
625 l_quantities dbms_utility.number_array;
626 l_units_of_measure uom_table_type;
627 l_uom_classes uom_class_table_type;
628 l_primary_units_of_measure uom_table_type;
629 l_unit_weights dbms_utility.number_array;
630 l_weight_uoms uom_table_type;
631 l_weights dbms_utility.number_array;
632
633 -- simplifying conditions
634 l_all_null_items boolean := TRUE;
635 l_all_weight_uoms boolean := TRUE;
636 l_all_same_item boolean := TRUE;
637 l_all_same_uom boolean := TRUE;
638
639 -- transient variables
640 l_primary_quantity NUMBER;
641 l_base_weight NUMBER;
642 BEGIN
643 -- fetch the relevant shipments
644 SELECT rsl.shipment_line_id
645 , rsl.to_organization_id
646 , rsl.item_id
647 , decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received)
648 , rsl.unit_of_measure
649 , muom_rsl.uom_class
650 , msi.primary_unit_of_measure
651 , msi.unit_weight
652 , muom_unit.unit_of_measure
653 BULK COLLECT INTO l_shipment_line_ids
654 , l_organization_ids
655 , l_item_ids
656 , l_quantities
657 , l_units_of_measure
658 , l_uom_classes
659 , l_primary_units_of_measure
660 , l_unit_weights
661 , l_weight_uoms
662 FROM rcv_shipment_lines rsl
663 , mtl_system_items msi
664 , mtl_units_of_measure muom_rsl
665 , mtl_units_of_measure muom_unit
666 WHERE rsl.shipment_header_id = p_charge.shipment_header_id
667 AND msi.inventory_item_id (+) = rsl.item_id
668 AND msi.organization_id (+) = rsl.to_organization_id
669 AND muom_unit.uom_code (+) = msi.weight_uom_code
670 AND muom_rsl.unit_of_measure = rsl.unit_of_measure;
671
672 FOR i IN 1..l_shipment_line_ids.COUNT LOOP
673 l_all_null_items := l_all_null_items AND l_item_ids(i) IS NULL;
674 l_all_weight_uoms := l_all_weight_uoms AND l_uom_classes(i) = 'Weight';
675
676 IF i > 1 THEN
677 l_all_same_uom := l_all_same_uom AND l_units_of_measure(i) = l_units_of_measure(i-1);
678 l_all_same_item := l_all_same_item
679 AND l_item_ids(i) = l_item_ids(i-1)
680 AND l_organization_ids(i) = l_organization_ids(i-1);
681 END IF;
682 END LOOP;
683
684 -- simplifying cases
685 IF l_all_same_item AND l_all_same_uom THEN
686 Prorate_Charge( p_charge
687 , p_charge_allocation_table
688 , l_shipment_line_ids
689 , l_quantities
690 );
691 RETURN;
692 END IF;
693
694 FOR i IN 1..l_shipment_line_ids.COUNT LOOP
695 -- convert quantity to primary uom and get weight from unit weight
696 IF l_uom_classes(i) <> 'Weight' THEN
697 po_uom_s.uom_convert( l_quantities(i)
698 , l_units_of_measure(i)
699 , l_item_ids(i)
700 , l_primary_units_of_measure(i)
701 , l_primary_quantity
702 );
703 po_uom_s.uom_convert( l_primary_quantity * l_unit_weights(i)
704 , l_weight_uoms(i)
705 , l_item_ids(i)
706 , get_base_weight_uom
707 , l_weights(i)
708 );
709 ELSE
710 -- uom is already a weight, use intraclass conversion directly to base weight
711 po_uom_s.uom_convert( l_quantities(i)
712 , l_units_of_measure(i)
713 , l_item_ids(i)
714 , get_base_weight_uom
715 , l_weights(i)
716 );
717 END IF;
718 END LOOP;
719
720 -- prorate the charge according to the weights
721 Prorate_Charge( p_charge
722 , p_charge_allocation_table
723 , l_shipment_line_ids
724 , l_weights
725 );
726 END Allocate_Charge_By_Weight;
727
728 PROCEDURE Allocate_Charge_By_Quantity
729 ( p_charge IN po_rcv_charges%ROWTYPE
730 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
731 ) IS
732 l_shipment_line_ids dbms_utility.number_array;
733 l_shipment_line_quantities dbms_utility.number_array;
734 BEGIN
735 -- get the quantities to allocate across
736 SELECT shipment_line_id
737 , decode(quantity_received, 0, quantity_shipped, quantity_received)
738 BULK COLLECT INTO l_shipment_line_ids
739 , l_shipment_line_quantities
740 FROM rcv_shipment_lines
741 WHERE shipment_header_id = p_charge.shipment_header_id;
742
743 -- prorate the charge according to the quantities
744 Prorate_Charge( p_charge
745 , p_charge_allocation_table
746 , l_shipment_line_ids
747 , l_shipment_line_quantities
748 );
749 END Allocate_Charge_By_Quantity;
750
751 PROCEDURE Allocate_Charge_By_Value
752 ( p_charge IN po_rcv_charges%ROWTYPE
753 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
754 ) IS
755 l_shipment_line_ids dbms_utility.number_array;
756 l_shipment_line_amounts dbms_utility.number_array;
757 BEGIN
758 asn_debug.put_line('Allocating header level charge by value for shipment header id ' || p_charge.shipment_header_id);
759
760 -- get the item values to allocate across
761 SELECT rsl.shipment_line_id
762 , decode(rsl.quantity_received, 0, rsl.quantity_shipped, rsl.quantity_received) * pol.unit_price
763 BULK COLLECT INTO l_shipment_line_ids
764 , l_shipment_line_amounts
765 FROM rcv_shipment_lines rsl
766 , po_lines_all pol
767 WHERE rsl.shipment_header_id = p_charge.shipment_header_id
768 AND pol.po_line_id = rsl.po_line_id;
769
770 asn_debug.put_line('id: ' || l_shipment_line_ids.count || ' amt: ' || l_shipment_line_amounts.count);
771
772 -- prorate the charge according to the amounts
773 Prorate_Charge( p_charge
774 , p_charge_allocation_table
775 , l_shipment_line_ids
776 , l_shipment_line_amounts
777 );
778 END Allocate_Charge_By_Value;
779
780 PROCEDURE Allocate_Header_Level_Charge
781 ( p_charge IN OUT NOCOPY po_rcv_charges%ROWTYPE
782 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
783 ) IS
784 BEGIN
785 IF p_charge.allocation_method IS NULL THEN
786 p_charge.allocation_method := 'VALUE';
787 END IF;
788
789 CASE p_charge.allocation_method
790 WHEN 'WEIGHT' THEN
791 Allocate_Charge_By_Weight( p_charge
792 , p_charge_allocation_table
793 );
794 WHEN 'VOLUME' THEN
795 Allocate_Charge_By_Volume( p_charge
796 , p_charge_allocation_table
797 );
798 WHEN 'QUANTITY' THEN
799 Allocate_Charge_By_Quantity( p_charge
800 , p_charge_allocation_table
801 );
802 WHEN 'VALUE' THEN
803 Allocate_Charge_By_Value( p_charge
804 , p_charge_allocation_table
805 );
806 ELSE
807 asn_debug.put_line('Unknown allocation method: ' || p_charge.allocation_method);
808 rcv_error_pkg.set_error_message('RCV_UNKNOWN_ALLOCATION_METHOD');
809 rcv_error_pkg.set_token('VALUE', p_charge.allocation_method);
810 rcv_error_pkg.log_interface_error( 'RCV_CHARGES'
811 , 'ALLOCATION_METHOD'
812 , FALSE
813 );
814 RAISE UNKNOWN_ALLOCATION_METHOD;
815 END CASE;
816 END Allocate_Header_Level_Charge;
817
818 PROCEDURE Allocate_Charges
819 ( p_charge_table IN OUT NOCOPY PO_CHARGES_GRP.charge_table_type
820 , p_charge_allocation_table IN OUT NOCOPY PO_CHARGES_GRP.charge_allocation_table_type
821 , p_charge_interface_table IN OUT NOCOPY charge_interface_table_type
822 ) IS
823 l_rci_idx BINARY_INTEGER := 1;
824 BEGIN
825 asn_debug.put_line('Allocating ' || p_charge_table.COUNT || ' charges');
826
827 FOR i IN 1..p_charge_table.COUNT LOOP
828 BEGIN
829 asn_debug.put_line('Allocating charge ' || i);
830
831 -- synchronize rci index
832 WHILE l_rci_idx <= p_charge_interface_table.COUNT AND
833 p_charge_table(i).interface_charge_id <> p_charge_interface_table(l_rci_idx).interface_charge_id
834 LOOP
835 l_rci_idx := l_rci_idx + 1;
836 END LOOP;
837
838 IF p_charge_table(i).shipment_line_id IS NULL THEN
839 asn_debug.put_line('Header level charge');
840 Allocate_Header_Level_Charge( p_charge_table(i)
841 , p_charge_allocation_table
842 );
843 ELSE
844 asn_debug.put_line('Line level charge');
845 Allocate_Line_Level_Charge( p_charge_table(i)
846 , p_charge_allocation_table
847 );
848 END IF;
849 EXCEPTION
850 WHEN OTHERS THEN
851 asn_debug.put_line('Caught exception in Allocate_Charges: i=' || i || ' SQLERRM=' || SQLERRM);
852
853 -- mark the error on the current charge interface row
854 IF p_charge_interface_table.EXISTS(l_rci_idx) THEN
855 p_charge_interface_table(l_rci_idx).transaction_status_code := 'E';
856 END IF;
857
858 -- delete any allocations tied to the current charge
859 FOR j IN REVERSE 1..p_charge_allocation_table.COUNT LOOP
860 IF p_charge_allocation_table(j).charge_id = p_charge_table(i).charge_id THEN
861 p_charge_allocation_table.DELETE(j);
862 ELSE
863 EXIT;
864 END IF;
865 END LOOP;
866 END;
867 END LOOP;
868
869 asn_debug.put_line('Done allocating charges');
870 END Allocate_Charges;
871
872 Procedure Preprocess_Charge_Line
873 ( p_charge_record IN OUT NOCOPY rcv_charges_interface%ROWTYPE
874 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
875 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
876 ) IS
877 BEGIN
878 Derive_charge_info(p_charge_record, p_header_record, p_transaction_record);
879 Default_charge_info(p_charge_record, p_header_record, p_transaction_record);
880 Validate_charge_info(p_charge_record, p_header_record, p_transaction_record);
881 END Preprocess_charge_line;
882
883 Procedure Preprocess_Charges
884 ( p_api_version IN NUMBER
885 , p_init_msg_list IN VARCHAR2
886 , x_return_status OUT NOCOPY VARCHAR2
887 , x_msg_count OUT NOCOPY NUMBER
888 , x_msg_data OUT NOCOPY VARCHAR2
889 , p_header_record IN RCV_ROI_PREPROCESSOR.headers_cur%ROWTYPE
890 , p_transaction_record IN RCV_ROI_PREPROCESSOR.txns_cur%ROWTYPE
891 ) IS
892 l_charge_interface_table charge_interface_table_type;
893 BEGIN
894 asn_debug.put_line('In preprocess_charges');
895
896 -- initialize return status
897 x_return_status := FND_API.g_ret_sts_success;
898
899 --fetch relevant charges into the temporary charge interface table
900 SELECT *
901 BULK COLLECT INTO l_charge_interface_table
902 FROM rcv_charges_interface
903 WHERE ( parent_header_interface_id = p_transaction_record.header_interface_id OR
904 parent_interface_id = p_transaction_record.interface_transaction_id
905 )
906 AND processing_status_code = 'P'
907 ORDER BY interface_charge_id
908 FOR UPDATE;
909
910 asn_debug.put_line('Found ' || l_charge_interface_table.COUNT || ' charges to preprocess');
911
912 -- return if there is no qualified RCI row.
913 IF l_charge_interface_table.COUNT < 1 THEN
914 asn_debug.put_line('No RCI row to preprocess, returning');
915 RETURN;
916 END IF;
917
918 -- loop through all charges associated to the item transaction
919 FOR i IN 1..l_charge_interface_table.COUNT LOOP
920 l_charge_interface_table(i).processing_status_code := 'S';
921 asn_debug.put_line('Preprocessing charge line ' || i);
922
923 -- default derive and validate this charge line
924 Preprocess_charge_line( l_charge_interface_table(i)
925 , p_header_record
926 , p_transaction_record
927 );
928
929 asn_debug.put_line('Preprocessed charge line ' || l_charge_interface_table(i).processing_status_code);
930
931 -- If charge preprocessing returns error, pass out 'E' to item error status.
932 IF l_charge_interface_table(i).processing_status_code NOT IN ('S', 'W') THEN
933 x_return_status := l_charge_interface_table(i).processing_status_code;
934 END IF;
935 END LOOP;
936
937 asn_debug.put_line('Done preprocessing, updating RCI');
938
939 -- Update the preprocessed charge interface data on rcv_charges_interface.
940 Update_Interface_Charges(l_charge_interface_table);
941
942 asn_debug.put_line('Done preprocessing charges');
943 EXCEPTION
944 WHEN OTHERS THEN
945 asn_debug.put_line('Exception in Preprocess_Charges:');
946 asn_debug.put_line(SQLERRM);
947 x_return_status := FND_API.g_ret_sts_error;
948 END Preprocess_Charges;
949
950 PROCEDURE Process_Charges
951 ( p_api_version IN NUMBER
952 , p_init_msg_list IN VARCHAR2
953 , x_return_status OUT NOCOPY VARCHAR2
954 , x_msg_count OUT NOCOPY NUMBER
955 , x_msg_data OUT NOCOPY VARCHAR2
956 , p_rhi_id IN RCV_HEADERS_INTERFACE.header_interface_id%TYPE
957 , p_rti_id IN RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
958 , p_shipment_header_id IN RCV_SHIPMENT_HEADERS.shipment_header_id%TYPE
959 , p_shipment_line_id IN RCV_SHIPMENT_LINES.shipment_line_id%TYPE
960 ) IS
961 l_charge_interface_table charge_interface_table_type;
962 l_charge_table PO_CHARGES_GRP.charge_table_type;
963 l_charge_allocation_table PO_CHARGES_GRP.charge_allocation_table_type;
964 l_fail_all_charges EXCEPTION;
965 l_rti_t_status varchar2(50);
966 l_rti_p_status varchar2(50);
967 BEGIN
968 asn_debug.put_line('In Process_Charges: *' || p_rhi_id || '*' || p_rti_id || '*' || p_shipment_header_id || '*' || p_shipment_line_id || '*');
969
970 -- initialize return status
971 x_return_status := FND_API.g_ret_sts_success;
972
973 -- fetch relevant charges into the temporary charge interface table
974 SELECT *
975 BULK COLLECT INTO l_charge_interface_table
976 FROM rcv_charges_interface
977 WHERE ( parent_interface_id = p_rti_id
978 OR ( parent_header_interface_id = p_rhi_id
979 AND parent_interface_id IS NULL
980 AND NOT EXISTS (
981 SELECT NULL
982 FROM rcv_transactions_interface
983 WHERE header_interface_id = p_rhi_id
984 AND interface_transaction_id <> p_rti_id)
985 )
986 )
987 AND processing_status_code IN ('S', 'W')
988 AND transaction_status_code = 'P'
989 ORDER BY interface_charge_id;
990
991 asn_debug.put_line('Found ' || l_charge_interface_table.COUNT || ' charges to process');
992
993 -- return if there is no qualified RCI row.
994 IF l_charge_interface_table.COUNT < 1 THEN
995 asn_debug.put_line('No RCI row to process, returning');
996 RETURN;
997 END IF;
998
999 -- populate the PL/SQL table l_charge_table
1000 FOR i IN 1..l_charge_interface_table.COUNT LOOP --{
1001 BEGIN
1002 asn_debug.put_line('Processing charge line ' || i);
1003 l_charge_interface_table(i).transaction_status_code := 'S';
1004 Add_Charge_From_Interface
1005 ( l_charge_interface_table(i)
1006 , l_charge_table
1007 , p_shipment_header_id
1008 , p_shipment_line_id
1009 );
1010 EXCEPTION
1011 WHEN others THEN
1012 l_charge_interface_table(i).transaction_status_code := 'E';
1013 asn_debug.put_line('RCV_CHARGES_GRP: Process_Charges: interface charge'||
1014 l_charge_interface_table(i).interface_charge_id ||' failed');
1015 END;
1016 END LOOP; --}
1017
1018 asn_debug.put_line('Done creating charges, allocating...');
1019
1020 -- if profile option RCV_CHARGE_FAIL_ITEM is Y and there is a failure
1021 -- then fail all the charges as well as the backing item transaction.
1022 -- otherwise, only fail the errored out charge.
1023 IF NVL(fnd_profile.VALUE('RCV_CHARGE_FAIL_ITEM'), 'N') = 'Y'
1024 AND l_charge_interface_table.COUNT > l_charge_table.COUNT
1025 THEN
1026 asn_debug.put_line('Fail all charges');
1027 -- Error out all charges
1028 FOR i IN 1..l_charge_interface_table.COUNT LOOP
1029 l_charge_interface_table(i).transaction_status_code := 'E';
1030 END LOOP;
1031
1032 -- update interface table
1033 Update_Interface_Charges(l_charge_interface_table);
1034
1035 RAISE l_fail_all_charges;
1036 END IF;
1037
1038 -- populate po_rcv_charge_allocations
1039 Allocate_Charges( l_charge_table, l_charge_allocation_table , l_charge_interface_table );
1040
1041 -- populate po_rcv_charges
1042 FORALL i IN 1..l_charge_table.COUNT
1043 INSERT INTO po_rcv_charges
1044 VALUES l_charge_table(i);
1045
1046 asn_debug.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into PO_RCV_CHARGES');
1047
1048 FORALL i IN 1..l_charge_allocation_table.COUNT
1049 INSERT INTO po_rcv_charge_allocations
1050 VALUES l_charge_allocation_table(i);
1051
1052 asn_debug.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into PO_RCV_CHARGE_ALLOCATIONS');
1053
1054 -- update rcv_charges_interface with status code
1055 Update_Interface_Charges(l_charge_interface_table);
1056
1057 -- delete all successfully processed rows from interfacet table
1058 DELETE FROM rcv_charges_interface
1059 WHERE transaction_status_code IN ('S','W');
1060
1061 asn_debug.put_line('Deleted ' || SQL%ROWCOUNT || ' successful rows from rcv_charges_interface');
1062
1063 asn_debug.put_line('Done processing charges');
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 -- pass out an error return status to fail the item transaction
1067 x_return_status := FND_API.g_ret_sts_error;
1068 IF (g_asn_debug = 'Y') THEN
1069 asn_debug.put_line('RCV_CHARGES_GRP.Process_Charges: Unexpected exception:');
1070 asn_debug.put_line(SQLERRM);
1071 END IF;
1072
1073 END Process_Charges;
1074
1075 END RCV_CHARGES_GRP;
1076