[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_MVT_EXPORT_DATA
Source
1 PACKAGE BODY INV_MGD_MVT_EXPORT_DATA AS
2 -- $Header: INVIDEPB.pls 120.4 2006/05/25 18:10:03 yawang noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVIDEPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package body of generating export data |
13 --| |
14 --| PROCEDURE LIST |
15 --| Generate_Export_Data |
16 --| |
17 --| HISTORY |
18 --| 10/12/01 yawang Created |
19 --| 10/23/01 yawang Modified, add new parameters: legal entity |
20 --| zone code, usage type, stat type and period|
21 --| name |
22 --| 03/15/02 yawang Add two new parameters currency code and |
23 --| exchange rate |
24 --| 12/03/02 vma Add NOCOPY to OUT parameters to comply |
25 --| with new PL/SQL standards for better |
26 --| performance. |
27 --+========================================================================
28
29 --===================
30 -- GLOBALS
31 --===================
32
33 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MGD_MVT_EXPORT_DADA';
34 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_MGD_MVT_EXPORT_DADA.';
35
36 --========================================================================
37 -- PROCEDURE : Generate_Export_Data PUBLIC
38 --
39 -- PARAMETERS: x_return_status Procedure return status
40 -- x_msg_count Number of messages in the list
41 -- x_msg_data Message text
42 -- p_api_version_number Known Version Number
43 -- p_init_msg_list Empty PL/SQL Table list for
44 -- Initialization
45 --
46 -- p_legal_entity_id Legal Entity
47 -- p_zone_code Economic Zone
48 -- p_usage_type Usage Type
49 -- p_stat_type Statistical Type
50 -- p_period_name Period Name
51 -- p_movement_type Movement Type
52 -- p_currency_code The currency in which user want to see
53 -- the statistic value
54 -- p_exchange_rate The exchange rate for the currency code
55 -- user selected
56 -- p_amount_display Display whole number or of currency precision
57 --
58 -- VERSION : current version 1.0
59 -- initial version 1.0
60 --
61 -- COMMENT : Procedure specification
62 -- to generate flat data file used in IDEP
63 --
64 -- Updated : 15/Mar/2002
65 --=======================================================================--
66
67 PROCEDURE Generate_Export_Data
68 ( p_api_version_number IN NUMBER
69 , p_init_msg_list IN VARCHAR2
70 , p_legal_entity_id IN NUMBER
71 , p_zone_code IN VARCHAR2
72 , p_usage_type IN VARCHAR2
73 , p_stat_type IN VARCHAR2
74 , p_movement_type IN VARCHAR2
75 , p_period_name IN VARCHAR2
76 , p_amount_display IN VARCHAR2
77 , p_currency_code IN VARCHAR2
78 , p_exchange_rate_char IN VARCHAR2
79 , x_return_status OUT NOCOPY VARCHAR2
80 , x_msg_count OUT NOCOPY NUMBER
81 , x_msg_data OUT NOCOPY VARCHAR2
82 )
83 IS
84 l_api_version_number CONSTANT NUMBER := 1.0;
85 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Export_Data';
86
87 l_base_currency VARCHAR2(3);
88 l_cons_country VARCHAR2(2);
89 l_cons_eu_country VARCHAR2(3);
90 l_invoice_num VARCHAR2(50);
91 l_invoice_currency VARCHAR2(3);
92 l_process_rec_count NUMBER := 0;
93 l_invoice_value_foreign NUMBER;
94 l_invoice_curr_foreign VARCHAR2(3);
95 l_invoice_value_local NUMBER;
96 l_invoice_value_calc NUMBER;
97 l_stat_ext_val_conv VARCHAR2(50);
98 l_invoice_val_loc_conv VARCHAR2(50);
99 l_invoice_val_fore_conv VARCHAR2(50);
100 l_total_weight_conv VARCHAR2(50);
101 l_exchange_rate NUMBER;
102 l_nls_numeric_char VARCHAR2(50);
103 l_alt_qty_conv VARCHAR2(50);
104 l_precision NUMBER;
105 l_weight_precision NUMBER;
106 l_round_total_weight NUMBER;
107 l_round_stat_ext_value NUMBER;
108 l_round_invoice_value NUMBER;
109 l_round_alternate_qty NUMBER;
110 l_rounding_precision NUMBER;
111 l_rounding_method VARCHAR2(30);
112 l_format_mask VARCHAR2(50);
113 l_weight_format_mask VARCHAR2(50);
114
115 /*CURSOR c_precision IS
116 SELECT
117 NVL(precision,2)
118 FROM
119 fnd_currencies
120 WHERE currency_code = l_invoice_currency;*/
121
122 --Get report currency precision
123 CURSOR c_rep_curr_precision IS
124 SELECT
125 NVL(precision,2)
126 FROM
127 fnd_currencies
128 WHERE currency_code = p_currency_code;
129
130 CURSOR c_mvt IS
131 SELECT
132 commodity_code
133 , transaction_nature
134 , transport_mode
135 , port
136 , statistical_procedure_code
137 , total_weight
138 , alternate_quantity
139 , dispatch_territory_code
140 , dispatch_territory_eu_code
141 , destination_territory_code
142 , destination_territory_eu_code
143 , origin_territory_code
144 , origin_territory_eu_code
145 , stat_ext_value
146 , area
147 , comments
148 , delivery_terms
149 , customer_vat_number
150 -- , invoice_line_ext_value
151 , movement_amount
152 , invoice_reference
153 , entity_org_id
154 , invoice_id
155 , document_source_type
156 , currency_conversion_rate
157 , transaction_quantity
158 , invoice_unit_price
159 FROM
160 mtl_movement_statistics
161 WHERE movement_status = 'F'
162 AND report_reference = to_char(p_legal_entity_id)||p_zone_code
163 ||p_period_name||p_usage_type
164 ||p_stat_type||p_movement_type
165 ORDER BY commodity_code, parent_movement_id, movement_id;
166
167 mvt_rec c_mvt%ROWTYPE;
168
169 BEGIN
170 -- Standard call to check for call compatibility
171 IF NOT FND_API.Compatible_API_Call
172 ( l_api_version_number
173 , p_api_version_number
174 , l_api_name
175 , G_PKG_NAME
176 )
177 THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180
181 -- Initialize message stack if required
182 IF FND_API.to_Boolean(p_init_msg_list)
183 THEN
184 FND_MSG_PUB.initialize;
185 END IF;
186
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
190 THEN
191 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
192 , G_MODULE_NAME || l_api_name || '.begin'
193 ,'enter procedure'
194 );
195 END IF;
196
197 --Temporary file for unit test,will remove after using concurrent pgm
198 --FND_FILE.Put_Names('yawang.log','yawang.out','/sqlcom/mgdev115');
199
200 --Get base currency
201 l_base_currency := INV_MGD_MVT_UTILS_PKG.Get_LE_Currency(p_legal_entity_id);
202
203 --Get profile value of NLS Numeric Character
204 l_nls_numeric_char := FND_PROFILE.Value('ICX_NUMERIC_CHARACTERS');
205
206 --Convert exchange rate from char to number
207 l_exchange_rate := FND_NUMBER.Canonical_To_Number(p_exchange_rate_char);
208
209 FOR mvt_rec IN c_mvt
210 LOOP
211 --Count how many records have been processed
212 l_process_rec_count := l_process_rec_count + 1;
213
214 --Get country of consignment
215 IF p_movement_type IN ('A','AA')
216 THEN
217 l_cons_country := mvt_rec.dispatch_territory_code;
218 l_cons_eu_country := mvt_rec.dispatch_territory_eu_code;
219 ELSE
220 l_cons_country := mvt_rec.destination_territory_code;
221 l_cons_eu_country := mvt_rec.destination_territory_eu_code;
222 END IF;
223
224 --Calculate invoice value. Can not use invoice_line_ext_value
225 --directly, becasue for multiple receipts one invoice,the invoice
226 --value in table is the total for all the receipts. We need to
227 --split for each receipt using transaction quantity
228 IF mvt_rec.invoice_unit_price IS NOT NULL
229 THEN
230 l_invoice_value_calc := mvt_rec.invoice_unit_price *
231 mvt_rec.transaction_quantity;
232 ELSE
233 l_invoice_value_calc := null;
234 END IF;
235
236 --Get invoice number and invoice currency
237 --Get invoice value in foreign currency, invoice foreign currency and
238 --invoice value in local currency
239 IF mvt_rec.document_source_type IN ('PO','RTV')
240 AND mvt_rec.invoice_id IS NOT NULL
241 THEN
242 BEGIN
243 SELECT
244 invoice_num
245 , invoice_currency_code
246 INTO
247 l_invoice_num
248 , l_invoice_currency
249 FROM ap_invoices_all
250 WHERE invoice_id = mvt_rec.invoice_id;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 l_invoice_num := null;
255 l_invoice_currency := null;
256 END;
257
258 IF l_invoice_currency IS NOT NULL
259 THEN
260 IF (l_invoice_currency <> l_base_currency
261 AND l_invoice_value_calc IS NOT NULL)
262 THEN
263 /* --Get precision of the foreign currency
264 OPEN c_precision;
265 FETCH c_precision
266 INTO l_precision;
267 CLOSE c_precision;*/
268
269 --Calculate the base invoice value
270 --Fix bug 5203245. Don't round here. The final rounding will
271 --be later after apply exchange rate
272 l_invoice_value_foreign := null; --mvt_rec.invoice_line_ext_value;
273 l_invoice_curr_foreign := null; --l_invoice_currency;
274 l_invoice_value_local := l_invoice_value_calc *
275 mvt_rec.currency_conversion_rate;
276 ELSE
277 l_invoice_value_foreign := null;
278 l_invoice_curr_foreign := null;
279 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
280 END IF;
281 ELSE
282 l_invoice_value_foreign := null;
283 l_invoice_curr_foreign := null;
284 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
285 END IF;
286 ELSIF mvt_rec.document_source_type IN ('SO', 'RMA', 'OPSO')
287 AND mvt_rec.invoice_id IS NOT NULL
288 THEN
289 BEGIN
290 --the invoice is a customer invoice
291 SELECT
292 trx_number
293 , invoice_currency_code
294 INTO
295 l_invoice_num
296 , l_invoice_currency
297 FROM ra_customer_trx_all
298 WHERE customer_trx_id = mvt_rec.invoice_id;
299
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 IF p_movement_type = 'A'
303 THEN
304 BEGIN
305 -- the invoice is ap intercompany invoice
306 SELECT
307 invoice_num
308 , invoice_currency_code
309 INTO
310 l_invoice_num
311 , l_invoice_currency
312 FROM ap_invoices_all
313 WHERE invoice_id = mvt_rec.invoice_id;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 l_invoice_num := null;
318 l_invoice_currency := null;
319 END;
320 ELSE
321 l_invoice_num := null;
322 l_invoice_currency := null;
323 END IF;
324
325 WHEN OTHERS THEN
326 l_invoice_num := null;
327 l_invoice_currency := null;
328
329 END;
330
331 IF l_invoice_currency IS NOT NULL
332 THEN
333 IF (l_invoice_currency <> l_base_currency
334 AND l_invoice_value_calc IS NOT NULL)
335 THEN
336 --comment this cursor out bug 5203245
337 /*--Get precision of the foreign currency
338 OPEN c_precision;
339 FETCH c_precision
340 INTO l_precision;
341 CLOSE c_precision;*/
342
343 --Calculate the base invoice value
344 --Fix bug 5203245. Don't round here. The final rounding will
345 --be later after apply exchange rate
346 l_invoice_value_foreign := null; --mvt_rec.invoice_line_ext_value;
347 l_invoice_curr_foreign := null; --l_invoice_currency;
348 l_invoice_value_local := l_invoice_value_calc *
349 mvt_rec.currency_conversion_rate;
350 ELSE
351 l_invoice_value_foreign := null;
352 l_invoice_curr_foreign := null;
353 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
354 END IF;
355 ELSE
356 l_invoice_value_foreign := null;
357 l_invoice_curr_foreign := null;
358 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
359 END IF;
360 ELSIF mvt_rec.document_source_type = 'MISC'
361 THEN
362 l_invoice_num := mvt_rec.invoice_reference;
363 l_invoice_currency := null;
364 l_invoice_value_foreign := null;
365 l_invoice_curr_foreign := null;
366 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
367 ELSE
368 l_invoice_num := null;
369 l_invoice_currency := null;
370 l_invoice_value_foreign := null;
371 l_invoice_curr_foreign := null;
372 l_invoice_value_local := NVL(l_invoice_value_calc,mvt_rec.movement_amount);
373 END IF;
374
375 --Fix bug 4866967 and 5203245, get weight precision and rounding
376 --method defined on parameter form
377 INV_MGD_MVT_UTILS_PKG.Get_Weight_Precision
378 (p_legal_entity_id => p_legal_entity_id
379 , p_zone_code => p_zone_code
380 , p_usage_type => p_usage_type
384
381 , p_stat_type => p_stat_type
382 , x_weight_precision => l_weight_precision
383 , x_rep_rounding => l_rounding_method);
385 --Get currency precision for the reporting currency
386 OPEN c_rep_curr_precision;
387 FETCH c_rep_curr_precision
388 INTO l_precision;
389
390 IF c_rep_curr_precision%NOTFOUND
391 THEN
392 l_precision := 2;
393 END IF;
394 CLOSE c_rep_curr_precision;
395
396 --Get rounding precision based on display format
397 IF p_amount_display = 'W'
398 THEN
399 l_rounding_precision := 0;
400 ELSE
401 l_rounding_precision := l_precision; --normal currency precision
402 END IF;
403
404 --Fix bug 4866967 and 5203245 allow user to decide the round digit
405 --and rounding method
406 --Round total weight before formating (it is rounded already in processor
407 --but in case the value in db has different precision as defined, round again here
408 l_round_total_weight := INV_MGD_MVT_UTILS_PKG.Round_Number
409 ( p_number => mvt_rec.total_weight
410 , p_precision => l_weight_precision
411 , p_rounding_method => l_rounding_method
412 );
413
414 l_round_alternate_qty := INV_MGD_MVT_UTILS_PKG.Round_Number
415 ( p_number => mvt_rec.alternate_quantity
416 , p_precision => l_weight_precision
417 , p_rounding_method => l_rounding_method
418 );
419
420 --Round statistics value and apply exchange rate
421 l_round_stat_ext_value := INV_MGD_MVT_UTILS_PKG.Round_Number
422 ( p_number => mvt_rec.stat_ext_value * l_exchange_rate
423 , p_precision => l_rounding_precision
424 , p_rounding_method => l_rounding_method
425 );
426
427 --Round invoice value and apply exchange rate
428 l_round_invoice_value := INV_MGD_MVT_UTILS_PKG.Round_Number
429 ( p_number => l_invoice_value_local * l_exchange_rate
430 , p_precision => l_rounding_precision
431 , p_rounding_method => l_rounding_method
432 );
433
434 --Get weight format mask
435 IF l_weight_precision = 0
436 THEN
437 l_weight_format_mask := 'FM9999999999999990';
438 ELSE
439 l_weight_format_mask := 'FM9999999999999990'||rpad('D',l_weight_precision+1,'0');
440 END IF;
441
442 --Get amount format mask
443 IF p_amount_display = 'W'
444 THEN
445 l_format_mask := 'FM9999999999999990';
446 ELSE
447 l_format_mask := 'FM9999999999999990'||rpad('D',l_precision+1,'0');
448 END IF;
449
450 --Apply user preference for numeric value
451 IF l_nls_numeric_char IS NOT NULL
452 THEN
453 l_stat_ext_val_conv := TO_CHAR(l_round_stat_ext_value, l_format_mask,
454 'NLS_NUMERIC_CHARACTERS = '''||l_nls_numeric_char||'''');
455
456 l_invoice_val_loc_conv := TO_CHAR(l_round_invoice_value, l_format_mask,
457 'NLS_NUMERIC_CHARACTERS = '''||l_nls_numeric_char||'''');
458
459 l_alt_qty_conv := TO_CHAR(l_round_alternate_qty, l_weight_format_mask,
460 'NLS_NUMERIC_CHARACTERS = '''||l_nls_numeric_char||'''');
461
462 l_total_weight_conv := TO_CHAR(l_round_total_weight, l_weight_format_mask,
463 'NLS_NUMERIC_CHARACTERS = '''||l_nls_numeric_char||'''');
464
465 /*l_invoice_val_fore_conv :=
466 TO_CHAR(l_invoice_value_foreign,'FM9999999999999990D00',
467 'NLS_NUMERIC_CHARACTERS = '''||l_nls_numeric_char||'''');*/
468 ELSE
469 l_stat_ext_val_conv := TO_CHAR(l_round_stat_ext_value, l_format_mask);
470 l_invoice_val_loc_conv := TO_CHAR(l_round_invoice_value, l_format_mask);
471 l_alt_qty_conv := TO_CHAR(l_round_alternate_qty, l_weight_format_mask);
472 l_total_weight_conv := TO_CHAR(l_round_total_weight, l_weight_format_mask);
473
474 --l_invoice_val_fore_conv := TO_CHAR(l_invoice_value_foreign,'FM9999999999999990D00');
475 END IF;
476
477 FND_FILE.Put_Line(FND_FILE.Output, NVL(RPAD(mvt_rec.commodity_code,8), ' ')
478 ||NVL(RPAD(l_cons_country, 2), ' ')
479 ||NVL(RPAD(l_cons_eu_country, 3), ' ')
480 ||NVL(RPAD(mvt_rec.transaction_nature,2), ' ')
481 ||NVL(RPAD(mvt_rec.transport_mode,1), ' ')
482 ||NVL(RPAD(mvt_rec.statistical_procedure_code,5), ' ')
483 ||NVL(RPAD(mvt_rec.port,5), ' ')
484 ||NVL(RPAD(mvt_rec.area,10), ' ')
485 ||NVL(RPAD(mvt_rec.delivery_terms,4), ' ')
486 ||NVL(RPAD(l_total_weight_conv,15), ' ')
487 ||NVL(RPAD(l_alt_qty_conv,15),' ')
488 ||NVL(RPAD(mvt_rec.origin_territory_code,2), ' ')
489 ||NVL(RPAD(l_stat_ext_val_conv,15),' ')
490 ||NVL(RPAD(l_invoice_val_loc_conv,15),' ')
491 ||NVL(RPAD(l_invoice_val_fore_conv,15),' ')
492 ||NVL(RPAD(l_invoice_num, 25),' ')
493 ||NVL(RPAD(l_invoice_curr_foreign,3), ' ')
494 ||NVL(RPAD(mvt_rec.customer_vat_number, 20), ' ')
495 ||NVL(mvt_rec.comments, ''));
496
497
498 END LOOP;
499 --FND_FILE.Close;
500
501 --Check if process_rec_count is greate than 0, if yes then update
502 --movement status to Exported,else print "No Data Found" in output file
503 IF l_process_rec_count > 0
504 THEN
505 UPDATE
506 mtl_movement_statistics
507 SET
511 ||p_period_name||p_usage_type
508 movement_status = 'X'
509 WHERE movement_status = 'F'
510 AND report_reference = to_char(p_legal_entity_id)||p_zone_code
512 ||p_stat_type||p_movement_type;
513 ELSE
514 --FND_FILE.Put_Names('yawang.log','yawang.out','/sqlcom/mgdev115');
515 FND_FILE.Put_Line(FND_FILE.Output, 'No Data Found');
516 --FND_FILE.Close;
517 END IF;
518
519 --Commit the Operation
520 COMMIT;
521
522 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
523 THEN
524 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
525 , G_MODULE_NAME || l_api_name || '.end'
526 ,'exit procedure'
527 );
528 END IF;
529
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 -- generate proper error message
534 FND_MESSAGE.set_name
535 ( application => 'INV'
536 , name => 'INV_MGD_MVT_NO_DATA_FOUND'
537 );
538 FND_MSG_PUB.Add;
539 FND_MSG_PUB.Count_And_Get
540 ( p_encoded => FND_API.G_FALSE
541 , p_count => x_msg_count
542 , p_data => x_msg_data
543 );
544
545 WHEN TOO_MANY_ROWS THEN
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 -- generate proper error message
548 FND_MESSAGE.set_name
549 ( application => 'INV'
550 , name => 'INV_MGD_MVT_TOO_MANY_TRANS'
551 );
552 FND_MSG_PUB.Add;
553 FND_MSG_PUB.Count_And_Get
554 ( p_encoded => FND_API.G_FALSE
555 , p_count => x_msg_count
556 , p_data => x_msg_data
557 );
558
559 WHEN FND_API.G_EXC_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 -- Get message count and data
562 FND_MSG_PUB.Count_And_Get
563 ( p_encoded => FND_API.G_FALSE
564 , p_count => x_msg_count
565 , p_data => x_msg_data
566 );
567
568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 -- Get message count and data
571 FND_MSG_PUB.Count_And_Get
572 ( p_encoded => FND_API.G_FALSE
573 , p_count => x_msg_count
574 , p_data => x_msg_data
575 );
576
577 WHEN OTHERS THEN
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
580 THEN
581 FND_MSG_PUB.Add_Exc_Msg
582 ( G_PKG_NAME
583 , 'Generate_Export_Data'
584 );
585 END IF;
586 -- Get message count and data
587 FND_MSG_PUB.Count_And_Get
588 ( p_encoded => FND_API.G_FALSE
589 , p_count => x_msg_count
590 , p_data => x_msg_data
591 );
592 END Generate_Export_Data;
593
594 END INV_MGD_MVT_EXPORT_DATA;