[Home] [Help]
PACKAGE BODY: APPS.INL_TAX_PVT
Source
1 PACKAGE BODY INL_TAX_PVT AS
2 /* $Header: INLVTAXB.pls 120.8.12010000.13 2008/12/09 21:20:13 aicosta ship $ */
3
4 -- Utl name : Prorate_TaxValues
5 -- Type : Private
6 -- Function : Import Tax Lines from PO
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_po_line_location_id IN NUMBER
10 -- p_sl_currency_code IN VARCHAR2
11 -- p_sl_currency_conversion_type IN VARCHAR2
12 -- p_sl_currency_conversion_date IN DATE
13 -- p_sl_currency_conversion_rate IN NUMBER
14 -- p_sl_txn_qty IN NUMBER
15 -- p_sl_txn_unit_price IN NUMBER
16 --
17 -- OUT : x_tax_amt IN OUT NOCOPY NUMBER
18 -- x_rec_tax_amt IN OUT NOCOPY NUMBER
19 -- x_return_status OUT NOCOPY VARCHAR2
20 --
21 -- Version : Current version 1.0
22 --
23 -- Notes :
24 PROCEDURE Prorate_TaxValues (
25 p_po_line_location_id IN NUMBER,
26 p_sl_currency_code IN VARCHAR2,
27 p_sl_currency_conversion_type IN VARCHAR2,
28 p_sl_currency_conversion_date IN DATE,
29 p_sl_txn_qty IN NUMBER,
30 p_sl_txn_unit_price IN NUMBER,
31 x_tax_amt IN OUT NOCOPY NUMBER,
32 x_rec_tax_amt IN OUT NOCOPY NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2
34 ) IS
35 l_proc_name CONSTANT VARCHAR2(100) := 'Prorate_TaxValues';
36 l_debug_info VARCHAR2(240);
37 l_return_status VARCHAR2(1);
38
39 l_qty NUMBER;
40 l_amt NUMBER;
41 l_po_curr_code VARCHAR2(15);
42 l_po_curr_rate_type VARCHAR2(30);
43 l_po_curr_rate_date DATE;
44 l_proration_rate NUMBER;
45 BEGIN
46
47 -- Standard Beginning of Procedure/Function Logging
48 INL_LOGGING_PVT.Log_BeginProc (
49 p_module_name => g_module_name,
50 p_procedure_name => l_proc_name
51 );
52
53 -- Initialize API return status to success
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 --Getting PO_LINE_LOCATION data
57 l_debug_info := 'Getting PO_LINE_LOCATION data';
58 INL_LOGGING_PVT.Log_Statement (
59 p_module_name => g_module_name,
60 p_procedure_name => l_proc_name,
61 p_debug_info => l_debug_info
62 );
63
64 SELECT pll.quantity,
65 (pll.quantity * pL.unit_price) amount,
66 pH.currency_code,
67 pH.rate_type,
68 pH.rate_date
69 INTO l_qty,
70 l_amt,
71 l_po_curr_code,
72 l_po_curr_rate_type,
73 l_po_curr_rate_date
74 FROM po_line_locations pll,
75 po_lines_all pL,
76 po_headers pH
77 WHERE pll.line_location_id = p_po_line_location_id
78 AND pll.po_line_id = pL.po_line_id
79 AND pll.po_header_id = pH.po_header_id
80 ;
81 --Verify if currency convertion is required
82 l_debug_info := 'Verify if currency convertion is required';
83 INL_LOGGING_PVT.Log_Statement (
84 p_module_name => g_module_name,
85 p_procedure_name => l_proc_name,
86 p_debug_info => l_debug_info
87 );
88 IF p_sl_currency_code <> l_po_curr_code THEN
89 --Use LCM INL_LANDEDCOST_PVT.Converted_Amt to get the converted amount
90 l_debug_info := 'Use LCM INL_LANDEDCOST_PVT.Converted_Amt to get the converted amount';
91 INL_LOGGING_PVT.Log_Statement (
92 p_module_name => g_module_name,
93 p_procedure_name => l_proc_name,
94 p_debug_info => l_debug_info
95 );
96 l_amt := INL_LANDEDCOST_PVT.Converted_Amt(
97 p_amt => l_amt,
98 p_from_currency_code => l_po_curr_code,
99 p_to_currency_code => p_sl_currency_code,
100 p_currency_conversion_type => nvl(l_po_curr_rate_type, p_sl_currency_conversion_type),
101 p_currency_conversion_date => nvl(l_po_curr_rate_date, p_sl_currency_conversion_date)
102 )
103 ;
104
105 END IF;
106 --Calculates the proration rate
107 l_debug_info := 'Calculates the proration rate';
108 INL_LOGGING_PVT.Log_Statement (
109 p_module_name => g_module_name,
110 p_procedure_name => l_proc_name,
111 p_debug_info => l_debug_info
112 );
113 l_proration_rate := (p_sl_txn_unit_price*p_sl_txn_qty) / l_amt;
114
115 --Calculates the proration of tax
116 l_debug_info := 'Calculates the proration of tax (proration_rate= '||l_proration_rate||')';
117 INL_LOGGING_PVT.Log_Statement (
118 p_module_name => g_module_name,
119 p_procedure_name => l_proc_name,
120 p_debug_info => l_debug_info
121 );
122
123 x_rec_tax_amt := x_rec_tax_amt * l_proration_rate;
124
125 x_tax_amt := x_tax_amt * l_proration_rate;
126
127 -- Standard End of Procedure/Function Logging
128 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
129 p_procedure_name => l_proc_name);
130
131 EXCEPTION
132 WHEN FND_API.G_EXC_ERROR THEN
133 -- Standard Expected Error Logging
134 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
135 p_procedure_name => l_proc_name);
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
138 -- Standard Unexpected Error Logging
139 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
140 p_procedure_name => l_proc_name);
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 WHEN OTHERS THEN
143 -- Standard Unexpected Error Logging
144 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
145 p_procedure_name => l_proc_name);
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
148 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
149 END IF;
150 END Prorate_TaxValues;
151
152 -- API name : Generate_TaxesFromPO
153 -- Type : Private
154 -- Function : Import Tax Lines from PO
155 -- Pre-reqs : None
156 -- Parameters :
157 -- IN : p_ship_header_id IN NUMBER Required
158 --
159 -- OUT p_tax_ln_tbl OUT tax_ln_tbl,
160 -- x_return_status OUT NOCOPY VARCHAR2
161 --
162 -- Version : Current version 1.0
163 --
164 -- Notes :
165 PROCEDURE Generate_TaxesFromPO (
166 p_ship_header_id IN NUMBER,
167 p_tax_ln_tbl OUT NOCOPY inl_tax_pvt.tax_ln_tbl,
168 x_return_status OUT NOCOPY VARCHAR2
169 ) IS
170 l_proc_name CONSTANT VARCHAR2(100) := 'Generate_TaxesFromPO';
171 l_debug_info VARCHAR2(240);
172 l_return_status VARCHAR2(1);
173
174 CURSOR zlines is
175 SELECT
176 zl.tax,
177 zl.tax_line_id,
178 zl.tax_amt_tax_curr,
179 zl.rec_tax_amt_tax_curr,
180 zl.tax_currency_code,
181 zl.tax_currency_conversion_type,
182 zl.tax_currency_conversion_date,
183 zl.tax_amt_included_flag,
184 zl.trx_line_quantity,
185 zl.line_amt,
186 zl.trx_currency_code,
187 sl.ship_line_id,
188 sl.ship_line_source_id po_line_location_id,
189 sl.currency_code sl_currency_code,
190 sl.currency_conversion_type sl_currency_conversion_type,
191 sl.currency_conversion_date sl_currency_conversion_date,
192 sl.txn_qty sl_txn_qty,
193 sl.txn_unit_price sl_txn_unit_price,
194 tl.tax_line_id inl_tax_line_id,
195 tl.tax_line_num inl_tax_line_num
196 FROM inl_ship_lines sl,
197 zx_lines zl,
198 inl_tax_lines tl
199 WHERE sl.ship_header_id = p_ship_header_id
200 AND sl.ship_line_src_type_code = 'PO'
201 AND sl.ship_line_source_id = zl.trx_line_id
202 AND zl.application_id = 201
203 AND tl.ship_header_id(+) = p_ship_header_id
204 AND tl.source_parent_table_name(+) = 'ZX_LINES'
205 AND tl.source_parent_table_id(+) = zl.tax_line_id
206 ORDER BY sl.ship_line_group_id,sl.ship_line_num
207 ;
208 zlines_rec zlines%ROWTYPE;
209 TYPE zlines_tbl_tp IS TABLE OF zlines%ROWTYPE INDEX BY BINARY_INTEGER;
210 zlines_tbl zlines_tbl_tp;
211 l_tax_amt_prorated NUMBER;
212 l_rec_tax_amt_prorated NUMBER;
213 BEGIN
214 -- Standard Beginning of Procedure/Function Logging
215 INL_LOGGING_PVT.Log_BeginProc (
216 p_module_name => g_module_name,
217 p_procedure_name => l_proc_name
218 );
219
220 -- Initialize API return status to success
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 --Uses the zlines cursor
224 l_debug_info := 'Uses the zlines cursor ';
225 INL_LOGGING_PVT.Log_Statement (
226 p_module_name => g_module_name,
227 p_procedure_name => l_proc_name,
228 p_debug_info => l_debug_info
229 );
230
231 OPEN zlines;
232 FETCH zlines
233 BULK COLLECT INTO zlines_tbl;
234 CLOSE zlines;
235
236 IF ( NVL(zlines_tbl.COUNT,0) > 0) THEN
237 -- loop in all taxes of current shipment
238 FOR i IN zlines_tbl.FIRST..zlines_tbl.LAST LOOP
239 -- when eBTax recalculates the taxes the tax_line_id in zx_lines remains the same
240
241 IF zlines_tbl(i).inl_tax_line_id IS NOT NULL THEN
242 p_tax_ln_tbl(i).tax_line_id := zlines_tbl(i).inl_tax_line_id; -- NULL ON INSERTS
243 p_tax_ln_tbl(i).tax_line_num := zlines_tbl(i).inl_tax_line_num;
244 END IF;
245 p_tax_ln_tbl(i).tax_code := zlines_tbl(i).tax;
246 p_tax_ln_tbl(i).ship_header_id := p_ship_header_id;
247 p_tax_ln_tbl(i).adjustment_num := 0; -- this proc is about estimated taxes only
248 p_tax_ln_tbl(i).parent_tax_line_id := null;
249 p_tax_ln_tbl(i).source_parent_table_name := 'ZX_LINES';
250 p_tax_ln_tbl(i).source_parent_table_id := zlines_tbl(i).tax_line_id;
251 p_tax_ln_tbl(i).to_parent_table_name := 'INL_SHIP_LINES';
252 p_tax_ln_tbl(i).to_parent_table_id := zlines_tbl(i).ship_line_id;
253 p_tax_ln_tbl(i).currency_code := zlines_tbl(i).tax_currency_code;
254 p_tax_ln_tbl(i).currency_conversion_type := zlines_tbl(i).tax_currency_conversion_type;
255 p_tax_ln_tbl(i).currency_conversion_date := zlines_tbl(i).tax_currency_conversion_date;
256 p_tax_ln_tbl(i).tax_amt_included_flag := zlines_tbl(i).tax_amt_included_flag;
257 p_tax_ln_tbl(i).match_id := null;
258
259 -- sometimes the shipment line doesn't receive a whole PO Line
260 -- in these cases a proration is necessary
261
262 l_tax_amt_prorated := zlines_tbl(i).tax_amt_tax_curr;
263 l_rec_tax_amt_prorated := zlines_tbl(i).rec_tax_amt_tax_curr;
264
265 prorate_TaxValues(
266 p_po_line_location_id => zlines_tbl(i).po_line_location_id,
267 p_sl_currency_code => zlines_tbl(i).sl_currency_code,
268 p_sl_currency_conversion_type => zlines_tbl(i).sl_currency_conversion_type,
269 p_sl_currency_conversion_date => zlines_tbl(i).sl_currency_conversion_date,
270 p_sl_txn_qty => zlines_tbl(i).sl_txn_qty,
271 p_sl_txn_unit_price => zlines_tbl(i).sl_txn_unit_price,
272 x_tax_amt => l_tax_amt_prorated,
273 x_rec_tax_amt => l_rec_tax_amt_prorated,
274 x_return_status => l_return_status
275 );
276 -- If any errors happen abort the process.
277 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
278 RAISE FND_API.G_EXC_ERROR;
279 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 p_tax_ln_tbl(i).tax_amt := l_tax_amt_prorated;
284 p_tax_ln_tbl(i).nrec_tax_amt := l_tax_amt_prorated - l_rec_tax_amt_prorated;
285
286 END LOOP;
287 END IF;
288
289 -- Standard End of Procedure/Function Logging
290 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
291 p_procedure_name => l_proc_name);
292
293
294 EXCEPTION
295 WHEN FND_API.G_EXC_ERROR THEN
296 -- Standard Expected Error Logging
297 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
298 p_procedure_name => l_proc_name);
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 -- Standard Unexpected Error Logging
302 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
303 p_procedure_name => l_proc_name);
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 WHEN OTHERS THEN
306 -- Standard Unexpected Error Logging
307 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
308 p_procedure_name => l_proc_name);
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
311 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_proc_name);
312 END IF;
313 END Generate_TaxesFromPO;
314
315 -- API name : Generate_Taxes
316 -- Type : Private
317 -- Function : Generate Tax Lines automatically from a source for now
318 -- can be the PO or any other logic defined inside the Taxes Hook.
319 -- Pre-reqs : None
320 -- Parameters :
321 -- IN : p_api_version IN NUMBER Required
322 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
323 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
324 -- p_ship_header_id IN NUMBER Required
325 --
326 -- OUT x_return_status OUT NOCOPY VARCHAR2
327 -- x_msg_count OUT NOCOPY NUMBER
328 -- x_msg_data OUT NOCOPY VARCHAR2
329 --
330 -- Version : Current version 1.0
331 --
332 -- Notes :
333 PROCEDURE Generate_Taxes(
334 p_api_version IN NUMBER,
335 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
336 p_commit IN VARCHAR2 := FND_API.G_FALSE,
337 p_ship_header_id IN NUMBER,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_msg_count OUT NOCOPY NUMBER,
340 x_msg_data OUT NOCOPY VARCHAR2
341 ) IS
342
343 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Taxes';
344 l_api_version CONSTANT NUMBER := 1.0;
345 l_debug_info VARCHAR2(240);
346 l_return_status VARCHAR2(1);
347 l_override_default_processing BOOLEAN;
348
349 l_tax_ln_tbl inl_tax_pvt.tax_ln_tbl;
350 BEGIN
351
352 -- Standard Beginning of Procedure/Function Logging
353 INL_LOGGING_PVT.Log_BeginProc (
354 p_module_name => g_module_name,
355 p_procedure_name => l_api_name
356 );
357
358 -- Standard Start of API savepoint
359 SAVEPOINT Generate_Taxes_PVT;
360
361 -- Initialize message list if p_init_msg_list is set to TRUE.
362 IF FND_API.to_Boolean( p_init_msg_list ) THEN
363 FND_MSG_PUB.initialize;
364 END IF;
365
366 -- Check for call compatibility.
367 IF NOT FND_API.Compatible_API_Call (p_current_version_number => l_api_version,
368 p_caller_version_number => p_api_version,
369 p_api_name => l_api_name,
370 p_pkg_name => g_pkg_name)
371 THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 -- Initialize API return status to success
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377
378 l_debug_info := 'Calls the rotine to generate tax according parameter: ';
379 INL_LOGGING_PVT.Log_Statement (
380 p_module_name => g_module_name,
381 p_procedure_name => l_api_name,
382 p_debug_info => l_debug_info
383 );
384
385 Generate_TaxesFromPO(
386 p_ship_header_id => p_ship_header_id,
387 p_tax_ln_tbl => l_tax_ln_tbl,
388 x_return_status => l_return_status
389 );
390 -- If any errors happen abort the process.
391 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
392 RAISE FND_API.G_EXC_ERROR;
393 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395 END IF;
396
397 inl_taxeshook_pvt.Get_Taxes(
398 x_tax_ln_tbl => l_tax_ln_tbl,
399 x_override_default_processing => l_override_default_processing,
400 x_return_status => l_return_status
401 );
402 -- If any errors happen abort the process.
403 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
404 RAISE FND_API.G_EXC_ERROR;
405 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 END IF;
408
409 IF ( NVL(l_tax_ln_tbl.COUNT,0) > 0) THEN
410 -- Persists the generated taxes
411 l_debug_info := 'Persists the generated taxes';
412 INL_LOGGING_PVT.Log_Statement (
413 p_module_name => g_module_name,
414 p_procedure_name => l_api_name,
415 p_debug_info => l_debug_info
416 );
417
418 FOR i IN l_tax_ln_tbl.FIRST..l_tax_ln_tbl.LAST LOOP
419 IF l_tax_ln_tbl(i).tax_line_id IS NULL THEN
420
421 --Insert IN INL_TAX_LINES getting ID from sequence
422 l_debug_info := 'Insert IN INL_TAX_LINES getting ID from sequence';
423 INL_LOGGING_PVT.Log_Statement (
424 p_module_name => g_module_name,
425 p_procedure_name => l_api_name,
426 p_debug_info => l_debug_info
427 );
428
429 SELECT inl_tax_lines_s.NEXTVAL
430 INTO l_tax_ln_tbl(i).tax_line_id
431 FROM DUAL
432 ;
433 IF l_tax_ln_tbl(i).tax_line_num IS NULL THEN
434 SELECT NVL(MAX(tl.tax_line_num),0)+1
435 INTO l_tax_ln_tbl(i).tax_line_num
436 FROM inl_tax_lines tl
437 WHERE tl.ship_header_id = l_tax_ln_tbl(i).ship_header_id
438 ;
439 END IF;
440
441 --Inserting record in INL_TAX_LINES
442 l_debug_info := 'Inserting record in INL_TAX_LINES';
443 INL_LOGGING_PVT.Log_Statement (
444 p_module_name => g_module_name,
445 p_procedure_name => l_api_name,
446 p_debug_info => l_debug_info
447 );
448
449 INSERT
450 INTO inl_tax_lines (
451 tax_line_id , /* 01 */
452 tax_line_num , /* 02 */
453 tax_code , /* 03 */
454 ship_header_id , /* 04 */
455 parent_tax_line_id , /* 05 */
456 adjustment_num , /* 06 */
457 match_id , /* 07 */
458 source_parent_table_name, /* 08 */
459 source_parent_table_id , /* 09 */
460 tax_amt , /* 10 */
461 nrec_tax_amt , /* 11 */
462 currency_code , /* 12 */
463 currency_conversion_type, /* 13 */
464 currency_conversion_date, /* 14 */
465 currency_conversion_rate, /* 15 */
466 tax_amt_included_flag , /* 16 */
467 created_by , /* 17 */
468 creation_date , /* 18 */
469 last_updated_by , /* 19 */
470 last_update_date , /* 20 */
471 last_update_login /* 21 */
472 )
473 VALUES (
474 l_tax_ln_tbl(i).tax_line_id , /* 01 */
475 l_tax_ln_tbl(i).tax_line_num , /* 02 */
476 l_tax_ln_tbl(i).tax_code , /* 03 */
477 l_tax_ln_tbl(i).ship_header_id , /* 04 */
478 l_tax_ln_tbl(i).parent_tax_line_id , /* 05 */
479 l_tax_ln_tbl(i).adjustment_num , /* 06 */
480 l_tax_ln_tbl(i).match_id , /* 07 */
481 l_tax_ln_tbl(i).source_parent_table_name, /* 08 */
482 l_tax_ln_tbl(i).source_parent_table_id , /* 09 */
483 l_tax_ln_tbl(i).tax_amt , /* 10 */
484 l_tax_ln_tbl(i).nrec_tax_amt , /* 11 */
485 l_tax_ln_tbl(i).currency_code , /* 12 */
486 l_tax_ln_tbl(i).currency_conversion_type, /* 13 */
487 l_tax_ln_tbl(i).currency_conversion_date, /* 14 */
488 l_tax_ln_tbl(i).currency_conversion_rate, /* 15 */
489 l_tax_ln_tbl(i).tax_amt_included_flag , /* 16 */
490 fnd_global.user_id , /* 17 */
491 SYSDATE , /* 18 */
492 fnd_global.user_id , /* 19 */
493 SYSDATE , /* 20 */
494 fnd_global.login_id /* 21 */
495 );
496 --Inserting record in INL_ASSOCIATIONS
497 l_debug_info := 'Inserting record in INL_ASSOCIATIONS';
498 INL_LOGGING_PVT.Log_Statement (
499 p_module_name => g_module_name,
500 p_procedure_name => l_api_name,
501 p_debug_info => l_debug_info
502 );
503
504 INSERT
505 INTO inl_associations (
506 association_id , /* 01 */
507 ship_header_id , /* 02 */
508 from_parent_table_name , /* 03 */
509 from_parent_table_id , /* 04 */
510 to_parent_table_name , /* 05 */
511 to_parent_table_id , /* 06 */
512 allocation_basis , /* 07 */
513 allocation_uom_code , /* 08 */
514 created_by , /* 09 */
515 creation_date , /* 10 */
516 last_updated_by , /* 11 */
517 last_update_date , /* 12 */
518 last_update_login /* 13 */
519 )
520 VALUES (
521 inl_associations_s.NEXTVAL , /* 01 */
522 l_tax_ln_tbl(i).ship_header_id , /* 02 */
523 'INL_TAX_LINES' , /* 03 */
524 l_tax_ln_tbl(i).tax_line_id , /* 04 */
525 l_tax_ln_tbl(i).to_parent_table_name , /* 05 */
526 l_tax_ln_tbl(i).to_parent_table_id , /* 06 */
527 'VALUE' , /* 07 */
528 null , /* 08 */
529 fnd_global.user_id , /* 09 */
530 SYSDATE , /* 10 */
531 fnd_global.user_id , /* 11 */
532 SYSDATE , /* 12 */
533 fnd_global.login_id /* 13 */
534 );
535 ELSE
536 --Updating record in INL_TAX_LINES
537 l_debug_info := 'Updating record in INL_TAX_LINES';
538 INL_LOGGING_PVT.Log_Statement (
539 p_module_name => g_module_name,
540 p_procedure_name => l_api_name,
541 p_debug_info => l_debug_info
542 );
543 UPDATE inl_tax_lines
544 SET tax_amt = l_tax_ln_tbl(i).tax_amt ,
545 nrec_tax_amt = l_tax_ln_tbl(i).nrec_tax_amt ,
546 currency_code = l_tax_ln_tbl(i).currency_code ,
547 currency_conversion_type= l_tax_ln_tbl(i).currency_conversion_type,
548 currency_conversion_date= l_tax_ln_tbl(i).currency_conversion_date,
549 currency_conversion_rate= l_tax_ln_tbl(i).currency_conversion_rate,
550 tax_amt_included_flag = l_tax_ln_tbl(i).tax_amt_included_flag ,
551 last_updated_by = fnd_global.user_id ,
552 last_update_date = SYSDATE ,
553 last_update_login = fnd_global.login_id
554 WHERE
555 tax_line_id = l_tax_ln_tbl(i).tax_line_id
556 ;
557 END IF;
558 END LOOP;
559 END IF;
560
561 -- Standard check of p_commit.
562 IF FND_API.To_Boolean( p_commit ) THEN
563 COMMIT WORK;
564 END IF;
565
566 -- Standard call to get message count and if count is 1, get message info.
567 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
568 p_count => x_msg_count,
569 p_data => x_msg_data);
570
571 -- Standard End of Procedure/Function Logging
572 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
573 p_procedure_name => l_api_name);
574
575 EXCEPTION
576 WHEN FND_API.G_EXC_ERROR THEN
577 -- Standard Expected Error Logging
578 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
579 p_procedure_name => l_api_name);
580 ROLLBACK TO Generate_Taxes_PVT;
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
583 p_count => x_msg_count,
584 p_data => x_msg_data);
585 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
586 -- Standard Unexpected Error Logging
587 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
588 p_procedure_name => l_api_name);
589 ROLLBACK TO Generate_Taxes_PVT;
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
592 p_count => x_msg_count,
593 p_data => x_msg_data);
594 WHEN OTHERS THEN
595 -- Standard Unexpected Error Logging
596 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
597 p_procedure_name => l_api_name);
598 ROLLBACK TO Generate_Taxes_PVT;
599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
601 THEN
602 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
603 p_procedure_name => l_api_name);
604 END IF;
605 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
606 p_count => x_msg_count,
607 p_data => x_msg_data);
608 END Generate_Taxes;
609
610 --==============================================================================
611 --==============================================================================
612 --==============================================================================
613 -- Utility name: Get_Event_Class_Code
614 --
615 -- Type : Private
616 --
617 -- Function : It will get the event class code required to call
618 -- the eTax services based on the category code of the shipment type.
619 -- These event class code is LCM specific.
620 -- eTax will convert this event class code to the tax class
621 -- code used by eTax.
622 --
623 -- Pre-reqs : None
624 --
625 -- Parameters :
626 -- IN : p_ship_type_id IN NUMBER,
627 --
628 -- OUT : x_return_status OUT NOCOPY VARCHAR2,
629 -- x_event_class_code OUT NOCOPY VARCHAR2)
630 --
631 -- Version : Current version 1.0
632 --
633 -- Notes :
634 PROCEDURE Get_Event_Class_Code(p_ship_type_id IN NUMBER,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_event_class_code OUT NOCOPY VARCHAR2)
637 IS
638 l_function_name CONSTANT VARCHAR2(100) := 'Get_Event_Class_Code ';
639 l_debug_info VARCHAR2(240);
640 l_category_code VARCHAR2(100);
641
642 BEGIN
643 -- logging message
644 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
645 p_procedure_name => l_function_name);
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648
649 -- get category to derivate Event_Class_Code
650 x_Event_Class_Code := 'ARRIVALS';
651 -- logging message
652 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
653 p_procedure_name => l_function_name);
654 EXCEPTION
655 WHEN FND_API.G_EXC_ERROR THEN
656 -- logging message
657 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
658 p_procedure_name => l_function_name);
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 -- logging message
662 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
663 p_procedure_name => l_function_name);
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665 WHEN OTHERS THEN
666 -- logging message
667 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
668 p_procedure_name => l_function_name);
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
671 THEN
672 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_function_name);
673 END IF;
674 END Get_Event_Class_Code;
675
676
677 -- Utility name: Get_Po_Info
678 --
679 -- Type : Private
680 --
681 -- Function : It get the Po Info.
682 --
683 -- Pre-reqs : None
684 --
685 -- Parameters :
686 -- IN : p_po_line_location_id IN NUMBER,
687 --
688 -- OUT : x_return_status OUT NOCOPY VARCHAR2,
689 -- x_po_header_id OUT NOCOPY NUMBER,
690 -- x_po_header_curr_conv_rate OUT NOCOPY NUMBER
691 --
692 -- Version : Current version 1.0
693 --
694 -- Notes :
695
696 PROCEDURE Get_Po_Info(p_po_line_location_id IN NUMBER,
697 x_return_status OUT NOCOPY VARCHAR2,
698 x_po_header_id OUT NOCOPY NUMBER,
699 x_po_header_curr_conv_rate OUT NOCOPY NUMBER) IS
700 l_debug_info VARCHAR2(240);
701 l_function_name CONSTANT VARCHAR2(100) := 'Get_Po_Info ';
702 BEGIN
703 -- logging message
704 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
705 p_procedure_name => l_function_name);
706
707 x_return_status := FND_API.G_RET_STS_SUCCESS;
708 -- get PO_Header_id
709 SELECT pll.po_header_id, nvl(ph.rate ,1)
710 INTO x_po_header_id, x_po_header_curr_conv_rate
711 FROM po_line_locations pll, po_headers ph
712 WHERE pll.line_location_id = P_Po_Line_Location_Id
713 AND pll.po_header_id = ph.po_header_id
714 AND rownum = 1;
715
716 -- logging message
717 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
718 p_procedure_name => l_function_name);
719
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 -- logging message
723 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
724 p_procedure_name => l_function_name);
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 -- logging message
728 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
729 p_procedure_name => l_function_name);
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 WHEN OTHERS THEN
732 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
733 p_procedure_name => l_function_name);
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
736 THEN
737 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_function_name);
738 END IF;
739 END Get_Po_Info;
740
741 -- Utility name: Populate_Headers_GT
742 --
743 -- Type : Private
744 --
745 -- Function : It populate the temporary zx table: zx_trx_headers_gt
746 -- but it remove any record of the same ship_Header_id before
747 --
748 -- Pre-reqs : None
749 --
750 -- Parameters :
751 -- IN : p_etax_already_called_flag IN VARCHAR2
752 -- p_event_class_code IN VARCHAR2
753 --
754 -- OUT x_event_type_code OUT NOCOPY VARCHAR2,
755 -- x_return_status OUT NOCOPY VARCHAR2
756 --
757 -- Version : Current version 1.0
758 --
759 -- Notes :
760 PROCEDURE Populate_Headers_GT(p_etax_already_called_flag IN VARCHAR2,
761 p_event_class_code IN VARCHAR2,
762 x_event_type_code OUT NOCOPY VARCHAR2,
763 x_return_status OUT NOCOPY VARCHAR2)IS
764
765 l_function_name CONSTANT VARCHAR2(100) := 'Populate_Headers_GT ';
766 l_debug_info VARCHAR2(240);
767 l_application_id NUMBER;
768
769 -- This flag is always N except when the calculate service is called for
770 -- quote for the recurring invoices and distributions sets.
771 l_quote_flag VARCHAR2(1);
772
773 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
774 l_ship_to_organization_id NUMBER;
775 l_ledger_id NUMBER;
776 l_tax_event_type_code VARCHAR2(30);
777 l_doc_level_recalc_flag VARCHAR2(1):='Y';
778 l_step VARCHAR2(10);
779 BEGIN
780 -- logging message
781 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
782 p_procedure_name => l_function_name);
783
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785
786 -------------------------------------------------------------------
787 l_debug_info := 'Step 3: Populate product specific attributes';
788 -------------------------------------------------------------------
789 l_application_id := 9004; -- Landed Cost Management
790 -------------------------------------------------------------------
791 l_debug_info := 'Step 6: Get Ship to party id';
792 -------------------------------------------------------------------
793 -- logging message
794 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
795 p_procedure_name => l_function_name,
796 p_debug_info => l_debug_info);
797
798 l_ship_to_organization_id := l_ship_header_rec.organization_id;
799
800 -------------------------------------------------------------------
801 l_debug_info := 'Step 7: Get ledger_id. l_ship_header_rec.inv_org_id: '||l_ship_header_rec.organization_id;
802 -------------------------------------------------------------------
803 -- logging message
804 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
805 p_procedure_name => l_function_name,
806 p_debug_info => l_debug_info);
807 l_step:='02';
808 SELECT ood.set_of_books_id
809 INTO l_ledger_id
810 FROM org_organization_definitions ood
811 WHERE ood.organization_id = l_ship_header_rec.organization_id
812 AND rownum = 1;
813 l_step:='02a';
814 -------------------------------------------------------------------
815 l_debug_info := 'Step 7a: Get tax_event_type_code';
816 -- logging message
817 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
818 p_procedure_name => l_function_name,
819 p_debug_info => l_debug_info);
820 -------------------------------------------------------------------
821 IF p_etax_already_called_flag = 'Y' THEN
822 l_tax_event_type_code := 'UPDATE';
823 x_event_type_code := 'ARRIVAL UPDATED';
824 ELSE
825 l_tax_event_type_code := 'CREATE';
826 x_event_type_code := 'ARRIVAL CREATED';
827 END IF;
828 l_step:='03';
829 -------------------------------------------------------------------
830 l_debug_info := 'Step 8: Populate zx_trx_headers_gt';
831 -------------------------------------------------------------------
832 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
833 -- logging message
834 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
835 p_procedure_name => l_function_name,
836 p_debug_info => l_debug_info);
837 -- Delete records of the current shipment
838 DELETE FROM zx_trx_headers_gt
839 WHERE trx_id=l_ship_header_rec.ship_header_id;
840 -- Insert records
841 l_step:='04';
842
843 INSERT INTO zx_trx_headers_gt(
844 /*01*/ internal_organization_id,
845 /*02*/ internal_org_location_id,
846 /*03*/ application_id,
847 /*04*/ entity_code,
848 /*05*/ event_class_code,
849 /*06*/ event_type_code,
850 /*07*/ trx_id,
851 /*08*/ hdr_trx_user_key1,
852 /*09*/ hdr_trx_user_key2,
853 /*10*/ hdr_trx_user_key3,
854 /*11*/ hdr_trx_user_key4,
855 /*12*/ hdr_trx_user_key5,
856 /*13*/ hdr_trx_user_key6,
857 /*14*/ trx_date,
858 /*15*/ trx_doc_revision,
859 /*16*/ ledger_id,
860 /*17*/ trx_currency_code,
861 /*18*/ currency_conversion_date,
862 /*19*/ currency_conversion_rate,
863 /*20*/ currency_conversion_type,
864 /*21*/ minimum_accountable_unit,
865 /*22*/ precision,
866 /*23*/ legal_entity_id,
867 /*24*/ rounding_ship_to_party_id,
868 /*25*/ rounding_ship_from_party_id,
869 /*26*/ rounding_bill_to_party_id,
870 /*27*/ rounding_bill_from_party_id,
871 /*28*/ rndg_ship_to_party_site_id,
872 /*29*/ rndg_ship_from_party_site_id,
873 /*30*/ rndg_bill_to_party_site_id,
874 /*31*/ rndg_bill_from_party_site_id,
875 /*32*/ establishment_id,
876 /*33*/ receivables_trx_type_id,
877 /*34*/ related_doc_application_id,
878 /*35*/ related_doc_entity_code,
879 /*36*/ related_doc_event_class_code,
880 /*37*/ related_doc_trx_id,
881 /*38*/ rel_doc_hdr_trx_user_key1,
882 /*39*/ rel_doc_hdr_trx_user_key2,
883 /*40*/ rel_doc_hdr_trx_user_key3,
884 /*41*/ rel_doc_hdr_trx_user_key4,
885 /*42*/ rel_doc_hdr_trx_user_key5,
886 /*43*/ rel_doc_hdr_trx_user_key6,
887 /*44*/ related_doc_number,
888 /*45*/ related_doc_date,
889 /*46*/ default_taxation_country,
890 /*47*/ quote_flag,
891 /*48*/ ctrl_total_hdr_tx_amt,
892 /*49*/ trx_number,
893 /*50*/ trx_description,
894 /*51*/ trx_communicated_date,
895 /*52*/ batch_source_id,
896 /*53*/ batch_source_name,
897 /*54*/ doc_seq_id,
898 /*55*/ doc_seq_name,
899 /*56*/ doc_seq_value,
900 /*57*/ trx_due_date,
901 /*58*/ trx_type_description,
902 /*59*/ document_sub_type,
903 /*60*/ supplier_tax_invoice_number,
904 /*61*/ supplier_tax_invoice_date ,
905 /*62*/ supplier_exchange_rate,
906 /*63*/ tax_invoice_date,
907 /*64*/ tax_invoice_number,
908 /*65*/ tax_event_class_code,
909 /*66*/ tax_event_type_code,
910 /*67*/ doc_event_status,
911 /*68*/ rdng_ship_to_pty_tx_prof_id,
912 /*69*/ rdng_ship_from_pty_tx_prof_id,
913 /*70*/ rdng_bill_to_pty_tx_prof_id,
914 /*71*/ rdng_bill_from_pty_tx_prof_id,
915 /*72*/ rdng_ship_to_pty_tx_p_st_id,
916 /*73*/ rdng_ship_from_pty_tx_p_st_id,
917 /*74*/ rdng_bill_to_pty_tx_p_st_id,
918 /*75*/ rdng_bill_from_pty_tx_p_st_id,
919 /*76*/ bill_third_pty_acct_id,
920 /*77*/ bill_third_pty_acct_site_id,
921 /*78*/ ship_third_pty_acct_id,
922 /*79*/ ship_third_pty_acct_site_id,
923 /*80*/ doc_level_recalc_flag
924 )
925 VALUES (
926 /*01*/ l_ship_header_rec.organization_id, --internal_organization_id
927 /*02*/ l_ship_header_rec.location_id, --internal_org_location_id
928 /*03*/ l_application_id, --application_id
929 /*04*/ G_ENTITY_CODE, --entity_code
930 /*05*/ p_event_class_code, --event_class_code
931 /*06*/ x_event_type_code, --event_type_code
932 /*07*/ l_ship_header_rec.ship_header_id, --trx_id
933 /*08*/ NULL, --hdr_trx_user_key1
934 /*09*/ NULL, --hdr_trx_user_key2
935 /*10*/ NULL, --hdr_trx_user_key3
936 /*11*/ NULL, --hdr_trx_user_key4
937 /*12*/ NULL, --hdr_trx_user_key5
938 /*13*/ NULL , --hdr_trx_user_key6
939 /*14*/ l_ship_header_rec.ship_date, --trx_date
940 /*15*/ NULL, --trx_doc_revision
941 /*16*/ l_ledger_id, --ledger_id
942 /*17*/ null, -- 07/2007 Currency columns was transfer to lines l_ship_header_rec.CURRENCY_CODE,
943 /*18*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_DATE,
944 /*19*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_RATE,
945 /*20*/ null, -- l_ship_header_rec.CURRENCY_CONVERSION_TYPE,
946 /*21*/ null, -- l_minimum_accountable_unit,
947 /*22*/ null, -- l_precision,
948 /*23*/ l_ship_header_rec.legal_entity_id, --legal_entity_id
949 /*24*/ l_ship_to_organization_id, --rounding_ship_to_party_id
950 /*25*/ NULL, --rounding_ship_from_party_id
951 /*26*/ NULL, --rounding_bill_to_party_id
952 /*27*/ NULL, --rounding_bill_from_party_id
953 /*28*/ NULL, --rndg_ship_to_party_site_id
954 /*29*/ NULL, --rndg_ship_from_party_site_id
955 /*30*/ NULL, --rndg_bill_to_party_site_id
956 /*31*/ NULL, --rndg_bill_from_party_site_id
957 /*32*/ NULL, --establishment_id
958 /*33*/ NULL, --receivables_trx_type_id
959 /*34*/ NULL, --related_doc_application_id
960 /*35*/ NULL, --related_doc_entity_code
961 /*36*/ NULL, --related_doc_event_class_code
962 /*37*/ NULL, --related_doc_trx_id
963 /*38*/ NULL, --rel_doc_hdr_trx_user_key1
964 /*39*/ NULL, --rel_doc_hdr_trx_user_key2
965 /*40*/ NULL, --rel_doc_hdr_trx_user_key3
966 /*41*/ NULL, --rel_doc_hdr_trx_user_key4
967 /*42*/ NULL, --rel_doc_hdr_trx_user_key5
968 /*43*/ NULL, --rel_doc_hdr_trx_user_key6
969 /*44*/ NULL, --related_doc_number
970 /*45*/ NULL, --related_doc_date
971 /*46*/ l_ship_header_rec.taxation_country, --default_taxation_country
972 /*47*/ l_quote_flag, --quote_flag
973 /*48*/ NULL, --ctrl_total_hdr_tx_amt
974 /*49*/ l_ship_header_rec.ship_num, --trx_number
975 /*50*/ NULL, --'INL_SHIPMENT', --trx_description
976 /*51*/ NULL, --trx_communicated_date
977 /*52*/ NULL, --batch_source_id
978 /*53*/ NULL, --batch_source_name
979 /*54*/ NULL, --doc_seq_id
980 /*55*/ NULL, --doc_seq_name
981 /*56*/ NULL, --doc_seq_value
982 /*57*/ NULL, --trx_due_date
983 /*58*/ NULL, --trx_type_description
984 /*59*/ l_ship_header_rec.document_sub_type, --document_sub_type
985 /*60*/ NULL, --supplier_tax_invoice_number
986 /*61*/ NULL, --supplier_tax_invoice_date
987 /*62*/ NULL, --supplier_exchange_rate
988 /*63*/ NULL, --tax_invoice_date
989 /*64*/ NULL, --tax_invoice_number
990 /*65*/ NULL, --tax_event_class_code
991 /*66*/ l_tax_event_type_code, --tax_event_type_code
992 /*67*/ NULL, --doc_event_status
993 /*68*/ NULL, --rdng_ship_to_pty_tx_prof_id
994 /*69*/ NULL, --rdng_ship_from_pty_tx_prof_id
995 /*70*/ NULL, --rdng_bill_to_pty_tx_prof_id
996 /*71*/ NULL, --rdng_bill_from_pty_tx_prof_id
997 /*72*/ NULL, --rdng_ship_to_pty_tx_p_st_id
998 /*73*/ NULL, --rdng_ship_from_pty_tx_p_st_id
999 /*74*/ NULL, --rdng_bill_to_pty_tx_p_st_id
1000 /*75*/ NULL, --rdng_bill_from_pty_tx_p_st_id
1001 /*76*/ NULL, --bill_third_pty_acct_id
1002 /*77*/ NULL, --bill_third_pty_acct_site_id
1003 /*78*/ NULL, --ship_third_pty_acct_id
1004 /*79*/ NULL, --ship_third_pty_acct_site_id
1005 /*80*/ l_doc_level_recalc_flag
1006 );
1007 l_step:='05';
1008 END IF;
1009 -- logging message
1010 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1011 p_procedure_name => l_function_name);
1012
1013 EXCEPTION
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 -- logging message
1016 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1017 p_procedure_name => l_function_name||l_step);
1018 x_return_status := FND_API.G_RET_STS_ERROR;
1019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1020 -- logging message
1021 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1022 p_procedure_name => l_function_name||l_step);
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1024 WHEN OTHERS THEN
1025 -- logging message
1026 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1027 p_procedure_name => l_function_name||l_step);
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1030 THEN
1031 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_function_name);
1032 END IF;
1033
1034 END Populate_Headers_GT;
1035
1036 -- Utility name: Populate_Lines_GT
1037 --
1038 -- Type : Private
1039 --
1040 -- Function : It populate zx_transaction_lines_gt. The records of this table will be processed for
1041 -- zx_api_pub.calculate_tax procedure to generate taxes (zx_lines)
1042 --
1043 -- Pre-reqs : None
1044 --
1045 -- Parameters :
1046 -- IN : p_event_class_code IN VARCHAR2,
1047 -- p_line_number IN NUMBER DEFAULT NULL,
1048 --
1049 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1050 --
1051 -- Version : Current version 1.0
1052 --
1053 -- Notes :
1054 PROCEDURE Populate_Lines_GT(p_event_class_code IN VARCHAR2,
1055 p_line_number IN NUMBER DEFAULT NULL,
1056 x_return_status OUT NOCOPY VARCHAR2)
1057 IS
1058 l_function_name CONSTANT VARCHAR2(100) := 'Populate_Lines_GT ';
1059 l_debug_info VARCHAR2(240);
1060
1061 -- This structure to populate all the lines information previous to insert
1062 -- in eTax global temporary table.
1063 TYPE Trans_Lines_Tab_Type IS TABLE OF zx_transaction_lines_gt%ROWTYPE;
1064 trans_lines Trans_Lines_Tab_Type := Trans_Lines_Tab_Type();
1065 l_ctrl_hdr_tx_appl_flag VARCHAR2(1);
1066 l_line_level_action VARCHAR2(30);
1067 l_line_class VARCHAR2(30);
1068 l_product_org_id NUMBER :=l_ship_header_rec.organization_id;
1069 l_bill_to_location_id NUMBER;
1070 -- This variables for PO doc info
1071 l_ref_doc_application_id NUMBER;
1072 l_ref_doc_entity_code VARCHAR2(30);
1073 l_ref_doc_event_class_code VARCHAR2(30);
1074 l_ref_doc_line_quantity NUMBER;
1075 l_po_header_curr_conv_rate NUMBER;
1076 l_ref_doc_trx_level_type VARCHAR2(30);
1077 l_ref_doc_line_id NUMBER;
1078 l_ref_doc_trx_id NUMBER;
1079 l_line_amt_includes_tax_flag VARCHAR2(1);
1080 l_line_amount NUMBER;
1081 l_fob_point VARCHAR2(30);
1082 l_ship_from_location_id NUMBER;
1083 l_dflt_tax_class_code VARCHAR2(30);
1084 l_allow_tax_code_override VARCHAR2(10);
1085 l_ship_line_type_code VARCHAR2(30);
1086 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1087 l_to_parent_table_name VARCHAR2(30);
1088 l_to_parent_table_id NUMBER;
1089 l_precision NUMBER(1);
1090 l_minimum_accountable_unit NUMBER;
1091
1092 BEGIN
1093 -- logging message
1094 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1095 p_procedure_name => l_function_name);
1096
1097 x_return_status := FND_API.G_RET_STS_SUCCESS;
1098 -----------------------------------------------------------------
1099 l_debug_info := 'IN Populating shipment lines collection';
1100 -- logging message
1101 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1102 p_procedure_name => l_function_name,
1103 p_debug_info => l_debug_info);
1104 -----------------------------------------------------------------
1105 l_debug_info := 'Step 1: Get l_bill_to_location_id for org_id';
1106 -- logging message
1107 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1108 p_procedure_name => l_function_name,
1109 p_debug_info => l_debug_info);
1110 ----------------------------------------------------------------------
1111 -- if is a dispatch the location is the same from ship from in a Arrival
1112 -- else it is null
1113 ----------------------------------------------------------------------
1114 l_debug_info := 'Step 2: Go through taxable lines'||' l_ship_line_list.COUNT: '||l_ship_line_list.COUNT;
1115 -- logging message
1116 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1117 p_procedure_name => l_function_name,
1118 p_debug_info => l_debug_info);
1119 ----------------------------------------------------------------------
1120 IF ( l_ship_line_list.COUNT > 0) THEN
1121 -- For non-tax only lines
1122 trans_lines.EXTEND(l_ship_line_list.COUNT);
1123
1124 -- loop in all lines of current shipment
1125 FOR i IN l_ship_line_list.FIRST..l_ship_line_list.LAST LOOP
1126 /* 10/07/07 Currency columns was transfer to lines */
1127 -------------------------------------------------------------------
1128 l_debug_info := 'Step 3: Get transaction line currency details';
1129 -------------------------------------------------------------------
1130 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1131 BEGIN
1132 SELECT NVL(precision, 0), NVL(minimum_accountable_unit,(1/power(10,precision)))
1133 INTO l_precision, l_minimum_accountable_unit
1134 FROM fnd_currencies
1135 WHERE currency_code = l_ship_line_list(i).currency_code;
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138 IF (SQLCODE <> -20001) THEN
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1140 -- logging message
1141 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1142 p_procedure_name => l_function_name);
1143 END IF;
1144 APP_EXCEPTION.RAISE_EXCEPTION;
1145 END;
1146 END IF;
1147 -- For now only ARRIVALS are treaded
1148 IF P_Event_Class_Code = 'ARRIVALS'
1149 then
1150
1151 IF l_ship_line_list(i).ship_to_organization_id IS NULL THEN
1152 trans_lines(i).ship_to_party_id := l_ship_header_rec.organization_id;
1153 ELSE
1154 trans_lines(i).ship_to_party_id := l_ship_line_list(i).ship_to_organization_id;
1155 END IF;
1156
1157 IF l_ship_line_list(i).ship_from_party_id IS NULL THEN
1158 trans_lines(i).ship_from_party_id := l_ship_line_list(i).party_id;
1159 ELSE
1160 trans_lines(i).ship_from_party_id := l_ship_line_list(i).ship_from_party_id;
1161 END IF;
1162 IF l_ship_line_list(i).ship_from_party_site_id IS NULL THEN
1163 trans_lines(i).ship_from_party_site_id := l_ship_line_list(i).party_site_id;
1164 ELSE
1165 trans_lines(i).ship_from_party_site_id := l_ship_line_list(i).ship_from_party_site_id;
1166 END IF;
1167 IF l_ship_line_list(i).ship_to_location_id IS NULL THEN
1168 trans_lines(i).ship_to_location_id := l_ship_header_rec.location_id;
1169 ELSE
1170 trans_lines(i).ship_to_location_id := l_ship_line_list(i).ship_to_location_id;
1171 END IF;
1172 trans_lines(i).ship_from_location_id := l_ship_from_location_id;
1173 ELSE
1174 trans_lines(i).ship_to_party_id := l_ship_line_list(i).ship_to_organization_id;
1175 trans_lines(i).ship_from_party_id := l_ship_line_list(i).ship_from_party_id;
1176 trans_lines(i).ship_from_party_site_id := l_ship_line_list(i).ship_from_party_site_id;
1177 trans_lines(i).ship_to_location_id := l_ship_line_list(i).ship_to_location_id;
1178 END IF;
1179 ----------------------------------------------------------------------
1180 l_debug_info := 'Step 6: Get fob_lookup_code from po_vendor_sites';
1181 -- logging message
1182 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1183 p_procedure_name => l_function_name,
1184 p_debug_info => l_debug_info);
1185 ----------------------------------------------------------------------
1186 BEGIN
1187 SELECT fob_lookup_code -- From lookup FOB
1188 INTO l_fob_point
1189 FROM po_vendor_sites
1190 WHERE party_site_id = l_ship_line_list(i).party_site_id
1191 AND rownum = 1;
1192 EXCEPTION
1193 WHEN no_data_found THEN
1194 l_fob_point := null;
1195 END;
1196 -------------------------------------------------------------------
1197 l_debug_info := 'Step 7: Get ship_line_type_code and default Fiscal Classifications for line number:'||
1198 l_ship_line_list(i).ship_line_num;
1199 -- logging message
1200 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1201 p_procedure_name => l_function_name,
1202 p_debug_info => l_debug_info);
1203 -------------------------------------------------------------------
1204 IF l_ship_line_list(i).source = 'CHARGE' THEN
1205 l_ship_line_type_code := 'MISC';
1206 ELSE
1207 l_ship_line_type_code := 'ITEM';
1208 END IF;
1209 -------------------------------------------------------------------
1210 l_debug_info := 'Step 8: Get line_level_action for line number:'
1211 || l_ship_line_list(i).ship_line_num
1212 ||'l_ship_line_list(i).tax_already_calculated_flag'|| l_ship_line_list(i).tax_already_calculated_flag
1213 ;
1214
1215 -- logging message
1216 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1217 p_procedure_name => l_function_name,
1218 p_debug_info => l_debug_info);
1219 -------------------------------------------------------------------
1220 IF nvl(l_ship_line_list(i).tax_already_calculated_flag,'N') = 'Y' THEN
1221 l_line_level_action := 'UPDATE';
1222 ELSE
1223 l_line_level_action := 'CREATE';
1224 END IF;
1225 ------------------------------------------------------------------
1226 l_debug_info := 'Step 9: Get Additional PO matched info (l_line_level_action = '||l_line_level_action||')';
1227 -- logging message
1228 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1229 p_procedure_name => l_function_name,
1230 p_debug_info => l_debug_info);
1231 ------------------------------------------------------------------
1232 -- For now src_type_code can be PO = PO Shipment, RMA = Return Material Authorization Line OR IR = Internal Requisition
1233 IF l_ship_line_list(i).src_type_code IS NOT NULL THEN
1234
1235 IF l_ship_line_list(i).src_type_code='PO' AND l_ship_line_list(i).src_id IS NOT NULL THEN
1236 Get_PO_Info(
1237 p_po_line_location_id => l_ship_line_list(i).src_id,
1238 x_po_header_id => l_ref_doc_trx_id,
1239 x_po_header_curr_conv_rate => l_po_header_curr_conv_rate,
1240 x_return_status => l_return_status);
1241
1242 -- If any errors happen abort API.
1243 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1244 RAISE FND_API.G_EXC_ERROR;
1245 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247 END IF;
1248
1249 l_ref_doc_trx_level_type := 'SHIPMENT';
1250 l_ref_doc_line_id := l_ship_line_list(i).src_id;
1251 ELSE
1252 l_ref_doc_application_id := Null;
1253 l_ref_doc_entity_code := Null;
1254 l_ref_doc_event_class_code := Null;
1255 l_ref_doc_line_quantity := Null;
1256 l_product_org_id := Null;
1257 l_ref_doc_trx_id := Null;
1258 l_ref_doc_trx_level_type := Null;
1259 l_ref_doc_line_id := Null;
1260 END IF;
1261 ELSE
1262 l_ref_doc_application_id := Null;
1263 l_ref_doc_entity_code := Null;
1264 l_ref_doc_event_class_code := Null;
1265 l_ref_doc_line_quantity := Null;
1266 l_product_org_id := Null;
1267 l_ref_doc_trx_id := Null;
1268 l_ref_doc_trx_level_type := Null;
1269 l_ref_doc_line_id := Null;
1270 END IF;
1271 ------------------------------------------------------------------
1272 l_debug_info := 'Step 9: Get line_amt_includes_tax_flag';
1273 -- logging message
1274 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1275 p_procedure_name => l_function_name,
1276 p_debug_info => l_debug_info);
1277 ------------------------------------------------------------------
1278 l_line_amt_includes_tax_flag := 'S';
1279 ------------------------------------------------------------------
1280 l_debug_info := 'Step 10: Get ctrl_hdr_tx_appl_flag';
1281 ------------------------------------------------------------------
1282 l_ctrl_hdr_tx_appl_flag := 'N';
1283 ------------------------------------------------------------------
1284 l_line_amount := nvl(l_ship_line_list(i).unit_price * l_ship_line_list(i).line_qty,0);
1285 ------------------------------------------------------------------
1286 l_debug_info := 'Step 10.1: Get line_class';
1287 -- logging message
1288 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1289 p_procedure_name => l_function_name,
1290 p_debug_info => l_debug_info);
1291 ------------------------------------------------------------------
1292 l_line_Class := 'STANDARD'; -- Possible values are 'STANDARD', 'ADJUSTMENT', 'APPLICATION', 'UNAPPLICATION', 'AMOUNT_MATCHED'
1293 ------------------------------------------------------------------
1294 l_debug_info := 'Step 12: Populate pl/sql table';
1295 -- logging message
1296 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1297 p_procedure_name => l_function_name,
1298 p_debug_info => l_debug_info);
1299 ------------------------------------------------------------------
1300 -- Will populate a dinamic table to include in zx_transaction_lines_gt
1301 IF (l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1302 trans_lines(i).application_id := 9004;
1303 trans_lines(i).entity_code := G_ENTITY_CODE;
1304 trans_lines(i).event_class_code := p_event_class_code;
1305 trans_lines(i).trx_line_precision := l_precision;
1306 trans_lines(i).trx_line_mau := l_minimum_accountable_unit;
1307 trans_lines(i).trx_line_currency_code := l_ship_line_list(i).currency_code ;
1308 trans_lines(i).trx_line_currency_conv_rate := l_ship_line_list(i).currency_conversion_rate ;
1309 trans_lines(i).trx_line_currency_conv_date := l_ship_line_list(i).currency_conversion_date;
1310 trans_lines(i).trx_line_currency_conv_type := l_ship_line_list(i).currency_conversion_type;
1311 trans_lines(i).trx_id := l_ship_line_list(i).ship_header_id;
1312 trans_lines(i).trx_level_type := l_ship_line_list(i).SOURCE;
1313 trans_lines(i).trx_line_id := l_ship_line_list(i).ship_line_id;
1314 trans_lines(i).line_level_action := l_line_level_action;
1315 trans_lines(i).line_class := l_line_class;
1316 trans_lines(i).trx_receipt_date := l_ship_header_rec.ship_date;
1317 trans_lines(i).trx_line_type := l_ship_line_type_code;
1318 trans_lines(i).trx_line_date := l_ship_header_rec.ship_date;
1319 trans_lines(i).trx_business_category := l_ship_line_list(i).trx_business_category;
1320 trans_lines(i).line_intended_use := l_ship_line_list(i).intended_use;
1321 trans_lines(i).user_defined_fisc_class := l_ship_line_list(i).user_def_fiscal_class;
1322 trans_lines(i).product_category := l_ship_line_list(i).product_category;
1323 trans_lines(i).product_fisc_classification := l_ship_line_list(i).product_fiscal_class;
1324 trans_lines(i).assessable_value := 0; -- l_line_amount; --updated above
1325 trans_lines(i).line_amt := l_line_amount;
1326 trans_lines(i).trx_line_quantity := l_ship_line_list(i).line_qty;
1327 trans_lines(i).unit_price := l_ship_line_list(i).unit_price;
1328 trans_lines(i).product_id := l_ship_line_list(i).inventory_item_id;
1329 trans_lines(i).product_org_id := nvl(l_product_org_id,l_ship_header_rec.organization_id);
1330 trans_lines(i).uom_code := l_ship_line_list(i).uom_code;
1331 trans_lines(i).product_type := l_ship_line_list(i).product_type;
1332 trans_lines(i).fob_point := l_fob_point;
1333 trans_lines(i).bill_from_party_id := l_ship_line_list(i).bill_from_party_id ;
1334 trans_lines(i).bill_from_party_site_id := l_ship_line_list(i).bill_from_party_site_id;
1335 trans_lines(i).bill_to_party_id := l_ship_line_list(i).bill_to_organization_id ;
1336 trans_lines(i).bill_to_location_id := l_ship_line_list(i).bill_to_location_id ;
1337 trans_lines(i).poa_party_id := l_ship_line_list(i).poa_party_id ;
1338 trans_lines(i).poa_party_site_id := l_ship_line_list(i).poa_party_site_id ;
1339 trans_lines(i).poo_party_id := l_ship_line_list(i).poo_organization_id ;
1340 trans_lines(i).poo_location_id := l_ship_line_list(i).poo_location_id ;
1341 trans_lines(i).ref_doc_application_id := l_ref_doc_application_id;
1342 trans_lines(i).ref_doc_entity_code := l_ref_doc_entity_code;
1343 trans_lines(i).ref_doc_event_class_code := l_ref_doc_event_class_code;
1344 trans_lines(i).ref_doc_trx_id := l_ref_doc_trx_id;
1345 trans_lines(i).ref_doc_trx_level_type := l_ref_doc_trx_level_type;
1346 trans_lines(i).ref_doc_line_quantity := l_ref_doc_line_quantity;
1347 trans_lines(i).ref_doc_line_id := l_ref_doc_line_id;
1348 trans_lines(i).trx_line_number := l_ship_line_list(i).ship_line_num;
1349 trans_lines(i).trx_line_description := NULL; --'INL_SHIPMENT_LINE';
1350 trans_lines(i).trx_line_gl_date := SYSDATE;
1351 trans_lines(i).product_description := l_ship_line_list(i).inventory_item_id;--to report only
1352 trans_lines(i).line_amt_includes_tax_flag := l_line_amt_includes_tax_flag;
1353 trans_lines(i).historical_flag := 'N'; -- NVL(l_ship_header_rec.historical_flag, 'N');
1354 trans_lines(i).ctrl_hdr_tx_appl_flag := l_ctrl_hdr_tx_appl_flag;
1355 trans_lines(i).ctrl_total_line_tx_amt := NULL;
1356 trans_lines(i).source_application_id := NULL;
1357 trans_lines(i).source_entity_code := NULL;
1358 trans_lines(i).source_event_class_code := NULL;
1359 trans_lines(i).source_trx_id := NULL;
1360 trans_lines(i).source_line_id := NULL;
1361 trans_lines(i).source_trx_level_type := NULL;
1362 trans_lines(i).input_tax_classification_code := l_ship_line_list(i).tax_classification_code;
1363 ------------------------------------------------------------------
1364 l_debug_info := 'Step 14: Populate pl/sql table: trans_lines(i).trx_line_id='||trans_lines(i).trx_line_id;
1365 -- logging message
1366 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1367 p_procedure_name => l_function_name,
1368 p_debug_info => l_debug_info);
1369 ------------------------------------------------------------------
1370 END IF;
1371 END LOOP;
1372 END IF;
1373 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1374 -- if exist any row from this shipment it will be removed
1375 DELETE FROM zx_transaction_lines_gt
1376 WHERE trx_id= l_ship_header_rec.ship_header_id;
1377 -------------------------------------------------------------------
1378 l_debug_info := 'Step 15: Bulk Insert into global temp table';
1379 -------------------------------------------------------------------
1380 -- logging message
1381 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1382 p_procedure_name => l_function_name,
1383 p_debug_info => l_debug_info);
1384 -- populate the table
1385 FORALL m IN trans_lines.FIRST..trans_lines.LAST
1386 INSERT INTO zx_transaction_lines_gt
1387 VALUES trans_lines(m);
1388 -------------------------------------------------------------------
1389
1390 l_debug_info := 'Step 15: Populate pl/sql table inserted: '||sql%rowcount||' line(s)';
1391 -- logging message
1392 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1393 p_procedure_name => l_function_name,
1394 p_debug_info => l_debug_info);
1395 -------------------------------------------------------------------
1396 END IF;
1397 -- logging message
1398 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1399 p_procedure_name => l_function_name);
1400
1401 EXCEPTION
1402 WHEN FND_API.G_EXC_ERROR THEN
1403 -- logging message
1404 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1405 p_procedure_name => l_function_name);
1406 x_return_status := FND_API.G_RET_STS_ERROR;
1407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1408 -- logging message
1409 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1410 p_procedure_name => l_function_name);
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1412 WHEN OTHERS THEN
1413 -- logging message
1414 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1415 p_procedure_name => l_function_name);
1416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1417 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1418 THEN
1419 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_function_name);
1420 END IF;
1421
1422 END Populate_Lines_GT;
1423
1424 -- Utility name: Adjust_Tax_Lines
1425 --
1426 -- Type : Private
1427 --
1428 -- Function : It makes adjusts (Insert, Delete or Both) in tax_line table.
1429 -- For a given Ship_Header_Id DEL will remove all rows from this shipment of the
1430 -- INL_tax_lines and from INL_associations
1431 -- INS will insert in INL_tax_lines all lines from zx_lines
1432 -- If the zx_line has recover amount two lines will be generated in INL_tax_lines.
1433 --
1434 -- Pre-reqs : None
1435 --
1436 -- Parameters :
1437 -- IN : p_oper IN VARCHAR2
1438 -- INS: To insert record
1439 -- DEL: To delete
1440 -- ALL: Both
1441 -- p_ship_header_id IN NUMBER
1442 --
1443 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1444 --
1445 -- Version : Current version 1.0
1446 --
1447 -- Notes :
1448 PROCEDURE Adjust_Tax_Lines(p_oper IN VARCHAR2 /*Valid values INS, DEL, ALL */
1449 ,p_ship_header_id IN NUMBER
1450 ,x_return_status OUT NOCOPY VARCHAR2) IS
1451 l_procedure_name CONSTANT VARCHAR2(100) := 'Adjust_Tax_Lines ';
1452 l_debug_info VARCHAR2(240);
1453 l_tax_line_id NUMBER;
1454 l_allocation_basis VARCHAR2(30) := 'VALUE';
1455 l_allocation_uom_code VARCHAR2(30) := NULL;
1456 l_proc VARCHAR2(1):='N';
1457 BEGIN
1458 -- logging message
1459 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1460 p_procedure_name => l_procedure_name);
1461
1462 x_return_status := FND_API.G_RET_STS_SUCCESS;
1463 -- if action needed is in ('DEL','ALL') Delete existent lines
1464 IF p_oper IN ('DEL','ALL') THEN
1465 -- logging message
1466 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1467 p_procedure_name => l_procedure_name,
1468 p_debug_info => l_debug_info);
1469 DELETE FROM inl_associations
1470 WHERE ship_header_id = P_ship_Header_Id
1471 AND to_parent_table_name IN ('INL_SHIP_LINES', 'INL_CHARGE_LINES')
1472 AND from_parent_table_name = 'INL_TAX_LINES';
1473
1474 DELETE FROM inl_tax_lines tl
1475 WHERE ship_header_id = P_ship_Header_Id
1476 AND EXISTS (SELECT 1
1477 FROM inl_ebtax_lines_v ebV
1478 ,zx_lines zl
1479 WHERE ship_header_id = P_ship_Header_Id
1480 AND tl.source_parent_table_name = 'ZX_LINES'
1481 AND tl.source_parent_table_id = zl.tax_line_id
1482 AND ebv.ship_line_id = zl.trx_line_id );
1483 END IF;
1484 -- if action needed is in ('INS','ALL') Insert in INL_tax_lines lines from zx_lines
1485 IF p_oper IN ('INS','ALL') THEN
1486 FOR curTax in (SELECT zl.tax_line_id
1487 ,zl.tax_line_number
1488 ,zl.tax_code
1489 ,zl.trx_id
1490 ,zl.trx_line_id
1491 ,zl.tax_amt
1492 ,nvl(zl.Nrec_tax_amt,0) Nrec_tax_amt
1493 ,zl.tax_amt_included_flag
1494 ,zl.tax_currency_code
1495 ,zl.tax_currency_conversion_type
1496 ,zl.tax_currency_conversion_date
1497 ,zl.tax_currency_conversion_rate
1498 ,zl.created_by
1499 ,zl.creation_date
1500 ,zl.last_updated_by
1501 ,zl.last_update_date
1502 ,zl.last_update_login
1503 ,DECODE(lv.source, 'SHIP_LINE' ,'INL_SHIP_LINES'
1504 , 'CHARGE' ,'INL_CHARGE_LINES') source
1505 ,oh.ship_TYPE_ID
1506 ,inl_tax_lines_s.NEXTVAL tax_line_id_s
1507 FROM zx_lines zl
1508 ,inl_ebtax_lines_v lv
1509 ,inl_ship_headers oh
1510 WHERE zl.application_id = 9004
1511 AND zl.trx_id = P_ship_Header_Id
1512 AND oh.ship_Header_Id = P_ship_Header_Id
1513 AND lv.ship_line_id = zl.trx_line_id
1514 AND lv.ship_header_id = P_ship_Header_Id)
1515 LOOP
1516 IF l_proc = 'N' THEN
1517 l_proc := 'Y';
1518 -- logging message
1519 l_debug_info := 'It will mark the calculated line: curTax';
1520 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1521 p_procedure_name => l_procedure_name,
1522 p_debug_info => l_debug_info);
1523
1524 END IF;
1525 -----------------------------------------------------------------
1526 l_debug_info := 'Step 6a: Persisting zl.tax_code: '||curTax.tax_code||' zl.tax_line_id:'||curTax.tax_line_id;
1527 -- logging message
1528 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1529 p_procedure_name => l_procedure_name,
1530 p_debug_info => l_debug_info);
1531 -----------------------------------------------------------------
1532 -----------------------------------------------------------------
1533 l_debug_info := ' P_Ship_Header_Id: '||p_ship_Header_Id
1534 ||' zl.tax_line_id: '||curTax.tax_line_id
1535 ;
1536 -- logging message
1537 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1538 p_procedure_name => l_procedure_name,
1539 p_debug_info => l_debug_info);
1540 -----------------------------------------------------------------
1541 -- It will insert in LCM tax Table the information from eBTax tax Table
1542 INSERT INTO inl_tax_lines (
1543 tax_line_id
1544 ,tax_line_num
1545 ,tax_code
1546 ,ship_header_id
1547 ,adjustment_num
1548 ,match_id
1549 ,source_parent_table_name
1550 ,source_parent_table_id
1551 ,tax_amt
1552 ,nrec_tax_amt
1553 ,currency_code
1554 ,currency_conversion_type
1555 ,currency_conversion_date
1556 ,currency_conversion_rate
1557 ,tax_amt_included_flag
1558 ,created_by
1559 ,creation_date
1560 ,last_updated_by
1561 ,last_update_date
1562 ,last_update_login)
1563 VALUES(
1564 curTax.tax_line_id_s
1565 ,curTax.tax_line_number
1566 ,curTax.tax_code
1567 ,curTax.trx_id
1568 ,0
1569 ,NULL
1570 ,'ZX_LINES'
1571 ,curTax.tax_line_id
1572 ,curTax.tax_amt
1573 ,curTax.nrec_tax_amt
1574 ,curTax.tax_currency_code
1575 ,curTax.tax_currency_conversion_type
1576 ,curTax.tax_currency_conversion_date
1577 ,curTax.tax_currency_conversion_rate
1578 ,curTax.tax_amt_included_flag
1579 ,fnd_global.user_id
1580 ,SYSDATE
1581 ,fnd_global.user_id
1582 ,SYSDATE
1583 ,fnd_global.login_id);
1584 -----------------------------------------------------------------
1585 l_debug_info := 'Step 6b: Persisting zl.tax_code(Associations): '||curTax.tax_code||' zl.tax_line_id:'||curTax.tax_line_id;
1586 -- logging message
1587 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1588 p_procedure_name => l_procedure_name,
1589 p_debug_info => l_debug_info);
1590 -----------------------------------------------------------------
1591 -- It will create the association of new tax line with the correspondent component
1592 INSERT INTO inl_associations (
1593 association_id /* 01 */
1594 ,ship_header_id /* 02 */
1595 ,from_parent_table_name /* 03 */
1596 ,from_parent_table_id /* 04 */
1597 ,to_parent_table_name /* 05 */
1598 ,to_parent_table_id /* 06 */
1599 ,allocation_basis /* 07 */
1600 ,allocation_uom_code /* 08 */
1601 ,created_by /* 09 */
1602 ,creation_date /* 10 */
1603 ,last_updated_by /* 11 */
1604 ,last_update_date /* 12 */
1605 ,last_update_login) /* 13 */
1606 VALUES(
1607 inl_associations_s.NEXTVAL /* 01 */
1608 ,curTax.trx_id /* 02 */
1609 ,'INL_TAX_LINES' /* 03 */
1610 ,curTax.tax_line_id_s /* 04 */
1611 ,curTax.source /* 05 */
1612 ,curTax.trx_line_id /* 06 */
1613 ,l_allocation_basis /* 07 */
1614 ,l_allocation_uom_code /* 08 */
1615 ,fnd_global.user_id /* 09 */
1616 ,SYSDATE /* 10 */
1617 ,fnd_global.user_id /* 11 */
1618 ,SYSDATE /* 12 */
1619 ,fnd_global.login_id); /* 13 */
1620 END LOOP;
1621 UPDATE inl_ship_lines
1622 SET tax_already_calculated_flag = 'Y',
1623 last_updated_by = fnd_global.user_id,
1624 last_update_date = SYSDATE
1625 WHERE ship_header_id = p_ship_header_Id;
1626
1627 UPDATE inl_charge_lines
1628 SET tax_already_calculated_flag = 'Y',
1629 last_updated_by = fnd_global.user_id,
1630 last_update_date = SYSDATE
1631 WHERE NVL(parent_charge_line_id,charge_line_id) IN (SELECT assoc.from_parent_table_id
1632 FROM inl_associations assoc
1633 WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
1634 AND assoc.ship_header_id = p_ship_header_id);
1635 END IF;
1636 -- logging message
1637 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1638 p_procedure_name => l_procedure_name);
1639
1640 EXCEPTION
1641 WHEN FND_API.G_EXC_ERROR THEN
1642 -- logging message
1643 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1644 p_procedure_name => l_procedure_name);
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1647 -- logging message
1648 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1649 p_procedure_name => l_procedure_name);
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 WHEN OTHERS THEN
1652 -- logging message
1653 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
1654 p_procedure_name => l_procedure_name);
1655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1656 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1657 THEN
1658 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_procedure_name);
1659 END IF;
1660
1661 END Adjust_Tax_Lines;
1662
1663 -- API name : Calculate_Tax
1664 -- Type : Private
1665 -- Function : It populate the ZX temporary GT tables and then call eBTax Calculate Tax for a given LCM Shipment.
1666 -- Pre-reqs : None
1667 -- Parameters :
1668 -- IN : p_api_version IN NUMBER,
1669 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1670 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
1671 -- p_ship_header_id IN NUMBER,
1672 --
1673 -- OUT : x_return_status OUT NOCOPY VARCHAR2
1674 -- x_msg_count OUT NOCOPY NUMBER
1675 -- x_msg_data OUT NOCOPY VARCHAR2
1676 --
1677 -- Version : Current version 1.0
1678 --
1679 -- Notes :
1680
1681 PROCEDURE Calculate_Tax(p_api_version IN NUMBER,
1682 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1683 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1684 p_ship_header_id IN NUMBER,
1685 x_return_status OUT NOCOPY VARCHAR2,
1686 x_msg_count OUT NOCOPY NUMBER,
1687 x_msg_data OUT NOCOPY VARCHAR2)
1688 IS
1689
1690 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Tax';
1691 l_api_version CONSTANT NUMBER := 1.0;
1692
1693 l_debug_info VARCHAR2(240);
1694
1695 l_event_class_code VARCHAR2(30);
1696 l_event_type_code VARCHAR2(30);
1697 l_tax_already_calculated VARCHAR2(1);
1698
1699 l_msg_count NUMBER;
1700 l_msg_data VARCHAR2(4000);
1701 l_msg VARCHAR2(4000);
1702 l_error_code VARCHAR2(30);
1703
1704 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1705 l_no_tax_lines VARCHAR2(1) := 'N';
1706 l_inv_rcv_matched VARCHAR2(1) := 'N';
1707
1708 l_ledger_list GL_MC_INFO.r_sob_list;
1709 l_denominator_rate NUMBER;
1710 l_numerator_rate NUMBER;
1711
1712
1713 BEGIN
1714 -- logging message
1715 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
1716 p_procedure_name => l_api_name);
1717
1718 -- Standard Start of API savepoint
1719 SAVEPOINT Calculate_Tax_PVT;
1720
1721 -- Initialize message list if p_init_msg_list is set to TRUE.
1722 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1723 FND_MSG_PUB.initialize;
1724 END IF;
1725
1726 -------------------------------------------------------------------
1727 l_debug_info := 'FND_API.Compatible_API_Call';
1728 -------------------------------------------------------------------
1729 -- logging message
1730 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1731 p_procedure_name => l_api_name,
1732 p_debug_info => l_debug_info);
1733 -- Check for call compatibility.
1734 IF NOT FND_API.Compatible_API_Call (l_api_version,
1735 p_api_version,
1736 l_api_name,
1737 G_PKG_NAME)
1738 THEN
1739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1740 END IF;
1741
1742 -- Initialize API return status to success
1743 x_return_status := FND_API.G_RET_STS_SUCCESS;
1744
1745
1746
1747 --- VPILAN 11-Jun-2008
1748 --- This RETURN is due to the fact we don't have EBTAX events model yet.
1749 RETURN;
1750
1751
1752 -------------------------------------------------------------------
1753 l_debug_info := 'Step 0: Get Entity Code mapping on eBTax';
1754 -------------------------------------------------------------------
1755 -- logging message
1756 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1757 p_procedure_name => l_api_name,
1758 p_debug_info => l_debug_info);
1759
1760 SELECT entity_code INTO G_ENTITY_CODE
1761 FROM zx_evnt_cls_mappings
1762 WHERE application_id = 9004
1763 AND ROWNUM = 1;
1764 -----------------------------------------------------------------
1765 l_debug_info := 'Step 1: Deleting tax records from INL_ASSOCIATIONS and INL_TAX_LINES';
1766 -- logging message
1767 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1768 p_procedure_name => l_api_name,
1769 p_debug_info => l_debug_info);
1770 -----------------------------------------------------------------
1771 Adjust_Tax_Lines('DEL', p_ship_header_id, l_return_status);
1772 -- If any errors happen abort API.
1773 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1774 RAISE FND_API.G_EXC_ERROR;
1775 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1777 END IF;
1778
1779 -----------------------------------------------------------------
1780 l_debug_info := 'Step 1a: call LCM Calculation for all types of lines';
1781 -- logging message
1782 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1783 p_procedure_name => l_api_name,
1784 p_debug_info => l_debug_info);
1785 -----------------------------------------------------------------
1786 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1787 -----------------------------------------------------------------
1788 l_debug_info := 'Step 2: Populating shipment header local record';
1789 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1790 p_procedure_name => l_api_name,
1791 p_debug_info => l_debug_info);
1792 -----------------------------------------------------------------
1793
1794 BEGIN
1795 OPEN Shipment_Header(p_ship_header_id);
1796 FETCH Shipment_Header INTO l_ship_header_rec;
1797 CLOSE Shipment_Header;
1798 END;
1799 -----------------------------------------------------------------
1800 l_debug_info := 'Step 2a: Populating shipment lines collection';
1801 -- logging message
1802 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1803 p_procedure_name => l_api_name,
1804 p_debug_info => l_debug_info);
1805 -----------------------------------------------------------------
1806 -- Collect INL_eBTax_Line_v lines in l_ship_line_list dinamic table
1807 -- The view INL_eBTax_Line_v has Shipment_Lines and Charge_Lines froma given shipment
1808 BEGIN
1809 OPEN Shipment_Lines(p_ship_header_id);
1810 FETCH Shipment_Lines
1811 BULK COLLECT INTO l_ship_line_list;
1812 CLOSE Shipment_Lines;
1813 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1814 END;
1815 -- The eTax_Already_called_flag is in lines
1816 -- adjustments generate version of lines and charge lines
1817 -- to decide the value of flag we are seeing zx table
1818
1819 -------------------------------------------------------------------
1820 l_debug_info := 'Step 2b: Get event class code';
1821 -------------------------------------------------------------------
1822
1823 Get_Event_Class_Code(
1824 p_ship_type_id => l_ship_header_rec.ship_type_id,
1825 x_event_class_code => l_event_class_code,
1826 x_return_status => l_return_status);
1827 -- If any errors happen abort API.
1828 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834 -----------------------------------------------------------------
1835 l_debug_info := 'Step 2c: Getting l_tax_already_calculated. trx_id= '||l_ship_header_rec.ship_header_id
1836 ||' application_id: 9004'
1837 ||' event_class_code: '||l_event_class_code
1838 ||' entity_code: '||G_ENTITY_CODE;
1839 -- logging message
1840 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1841 p_procedure_name => l_api_name,
1842 p_debug_info => l_debug_info);
1843 -----------------------------------------------------------------
1844 BEGIN
1845 SELECT 'Y'
1846 INTO l_tax_already_calculated
1847 FROM zx_lines_det_factors
1848 WHERE trx_id = l_ship_header_rec.ship_header_id
1849 AND application_id = 9004
1850 AND entity_code = G_ENTITY_CODE
1851 AND event_class_code = l_event_class_code
1852 AND ROWNUM < 2;
1853 EXCEPTION
1854 WHEN OTHERS THEN l_tax_already_calculated := 'N';
1855 END;
1856 -----------------------------------------------------------------
1857 l_debug_info := 'Step 3: Populate Header';
1858 -- logging message
1859 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1860 p_procedure_name => l_api_name,
1861 p_debug_info => l_debug_info);
1862 -----------------------------------------------------------------
1863
1864 -- Populate eBTax temporary table (Header)
1865 INL_TAX_PVT.Populate_Headers_GT(
1866 p_etax_already_called_flag => l_tax_already_calculated,
1867 p_event_class_code => l_event_class_code,
1868 x_event_type_code => l_event_type_code,
1869 x_return_status => l_return_status);
1870
1871 -- If any errors happen abort API.
1872 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1873 RAISE FND_API.G_EXC_ERROR;
1874 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1876 END IF;
1877 -----------------------------------------------------------------
1878 l_debug_info := 'Step 4: Populate TRX lines';
1879 -- logging message
1880 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1881 p_procedure_name => l_api_name,
1882 p_debug_info => l_debug_info);
1883 -----------------------------------------------------------------
1884 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1885 -- Populate eBTax temporary table (Lines)
1886
1887 INL_TAX_PVT.Populate_Lines_GT(
1888 P_Event_Class_Code => l_event_class_code,
1889 P_Line_Number => 1,
1890 x_return_status => l_return_status);
1891 -- If any errors happen abort API.
1892 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1893 RAISE FND_API.G_EXC_ERROR;
1894 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1896 END IF;
1897
1898
1899 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1900 -- logging message
1901 INL_LOGGING_PVT.Log_APICallIn (p_module_name => g_module_name,
1902 p_procedure_name => l_api_name,
1903 p_call_api_name => 'ZX_API_PUB.Calculate_Tax',
1904 p_in_param_name1 => 'p_api_version',
1905 p_in_param_value1 => 1.0,
1906 p_in_param_name2 => 'p_init_msg_list',
1907 p_in_param_value2 => FND_API.G_TRUE,
1908 p_in_param_name3 => 'p_commit',
1909 p_in_param_value3 => FND_API.G_FALSE,
1910 p_in_param_name4 => 'p_validation_level',
1911 p_in_param_value4 => FND_API.G_VALID_LEVEL_FULL);
1912 -- It will run the calculate_tax procedure from eBTax
1913 -- This procedure will calculate the tax from the current transaction
1914 -- and populate zx_lines
1915
1916 /*
1917 FROM ZX_TRANSACTION_LINES_GT
1918 WHERE TRX_ID = l_ship_header_rec.ship_header_id
1919 */
1920 zx_api_pub.calculate_tax(
1921 p_api_version => 1.0,
1922 p_init_msg_list => FND_API.G_FALSE,
1923 p_commit => FND_API.G_FALSE,
1924 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1925 x_return_status => l_return_status,
1926 x_msg_count => l_msg_count,
1927 x_msg_data => l_msg_data);
1928
1929 -- logging message
1930 INL_LOGGING_PVT.Log_APICallOut (p_module_name => g_module_name,
1931 p_procedure_name => l_api_name,
1932 p_call_api_name => 'ZX_API_PUB.Calculate_Tax',
1933 p_out_param_name1 => 'l_return_status',
1934 p_out_param_value1 => l_return_status,
1935 p_out_param_name2 => 'l_msg_count',
1936 p_out_param_value2 => l_msg_count,
1937 p_out_param_name3 => 'l_msg_data',
1938 p_out_param_value3 => l_msg_data);
1939
1940 -- If any errors happen abort API.
1941 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1942 RAISE FND_API.G_EXC_ERROR;
1943 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1945 END IF;
1946
1947 -----------------------------------------------------------------
1948 l_debug_info := 'Step 6: Adjust_Tax_Lines to Persist tax records in INL_ASSOCIATIONS and INL_TAX_LINES';
1949 -- logging message
1950 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1951 p_procedure_name => l_api_name,
1952 p_debug_info => l_debug_info);
1953 -----------------------------------------------------------------
1954 -- It will answer the updates done in zx_lines in INL_tax_lines
1955 Adjust_Tax_Lines('INS', p_ship_header_id, l_return_status);
1956 -- If any errors happen abort API.
1957 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1958 RAISE FND_API.G_EXC_ERROR;
1959 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1961 END IF;
1962
1963 -----------------------------------------------------------------
1964 l_debug_info := 'Step 7: call LCM Calculation for tax lines';
1965 -- logging message
1966 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1967 p_procedure_name => l_api_name,
1968 p_debug_info => l_debug_info);
1969 -----------------------------------------------------------------
1970 END IF;
1971 END IF;
1972 END IF;
1973 -- If any errors happen abort API.
1974 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1975 RAISE FND_API.G_EXC_ERROR;
1976 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1978 END IF;
1979
1980 -- Standard check of p_commit.
1981 IF FND_API.To_Boolean( p_commit ) THEN
1982 COMMIT WORK;
1983 END IF;
1984
1985 -- Standard call to get message count and if count is 1, get message info.
1986 FND_MSG_PUB.Count_And_Get
1987 (p_encoded => FND_API.g_false,
1988 p_count => x_msg_count,
1989 p_data => x_msg_data);
1990
1991 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
1992 p_procedure_name => l_api_name);
1993 EXCEPTION
1994 WHEN FND_API.G_EXC_ERROR THEN
1995 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
1996 p_procedure_name => l_api_name);
1997 ROLLBACK TO Calculate_Tax_PVT;
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2000 p_count => x_msg_count,
2001 p_data => x_msg_data);
2002 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2003 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2004 p_procedure_name => l_api_name);
2005 ROLLBACK TO Calculate_Tax_PVT;
2006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2007 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2008 p_count => x_msg_count,
2009 p_data => x_msg_data);
2010 WHEN OTHERS THEN
2011 -- logging message
2012 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2013 p_procedure_name => l_api_name);
2014 ROLLBACK TO Calculate_Tax_PVT;
2015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2016 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2017 THEN
2018 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
2019 END IF;
2020 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2021 p_count => x_msg_count,
2022 p_data => x_msg_data);
2023 END Calculate_Tax;
2024
2025 -- API name : Get_DefaultTaxDetAttribs
2026 -- Type : Private
2027 -- Function : Get default tax attributes
2028 -- Pre-reqs : None
2029 -- Parameters :
2030 -- IN : p_api_version IN NUMBER,
2031 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2032 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
2033 -- p_application_id IN NUMBER,
2034 -- p_entity_code IN VARCHAR2,
2035 -- p_event_class_code IN VARCHAR2,
2036 -- p_org_id IN VARCHAR2,
2037 -- p_item_id IN NUMBER,
2038 -- p_country_code IN VARCHAR2,
2039 -- p_effective_date IN DATE,
2040 -- p_source_type_code IN VARCHAR2,
2041 -- p_po_line_location_id IN NUMBER,
2042 --
2043 -- OUT x_return_status OUT NOCOPY VARCHAR2
2044 -- x_msg_count OUT NOCOPY NUMBER
2045 -- x_msg_data OUT NOCOPY VARCHAR2
2046 -- x_trx_biz_category OUT NOCOPY VARCHAR2,
2047 -- x_intended_use OUT NOCOPY VARCHAR2,
2048 -- x_prod_category OUT NOCOPY VARCHAR2,
2049 -- x_prod_fisc_class_code OUT NOCOPY VARCHAR2,
2050 -- x_product_type OUT NOCOPY VARCHAR2
2051 -- Version : Current version 1.0
2052 --
2053 -- Notes :
2054 PROCEDURE Get_DefaultTaxDetAttribs(p_api_version IN NUMBER,
2055 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2056 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2057 p_application_id IN NUMBER,
2058 p_entity_code IN VARCHAR2,
2059 p_event_class_code IN VARCHAR2,
2060 p_org_id IN VARCHAR2,
2061 p_item_id IN NUMBER,
2062 p_country_code IN VARCHAR2,
2063 p_effective_date IN DATE,
2064 p_source_type_code IN VARCHAR2,
2065 p_po_line_location_id IN NUMBER,
2066 x_return_status OUT NOCOPY VARCHAR2,
2067 x_msg_count OUT NOCOPY NUMBER,
2068 x_msg_data OUT NOCOPY VARCHAR2,
2069 x_trx_biz_category OUT NOCOPY VARCHAR2,
2070 x_intended_use OUT NOCOPY VARCHAR2,
2071 x_prod_category OUT NOCOPY VARCHAR2,
2072 x_prod_fisc_class_code OUT NOCOPY VARCHAR2,
2073 x_product_type OUT NOCOPY VARCHAR2) IS
2074
2075 l_api_name CONSTANT VARCHAR2(30) := 'Get_DefaultTaxDetAttribs';
2076 l_api_version CONSTANT NUMBER := 1.0;
2077
2078 l_return_status VARCHAR2(1);
2079 l_msg_count NUMBER;
2080 l_msg_data VARCHAR2(2000);
2081 l_debug_info VARCHAR2(200);
2082 l_po_country_code VARCHAR2(20);
2083 BEGIN
2084
2085 -- Standard Beginning of Procedure/Function Logging
2086 INL_LOGGING_PVT.Log_BeginProc (p_module_name => g_module_name,
2087 p_procedure_name => l_api_name);
2088
2089 -- Standard Start of API savepoint
2090 SAVEPOINT Get_DefaultTaxDetAttribs_PVT;
2091
2092 -- Initialize message list if p_init_msg_list is set to TRUE.
2093 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2094 FND_MSG_PUB.initialize;
2095 END IF;
2096
2097 -- Check for call compatibility.
2098 IF NOT FND_API.Compatible_API_Call (l_api_version,
2099 p_api_version,
2100 l_api_name,
2101 g_pkg_name)
2102 THEN
2103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2104 END IF;
2105
2106 -- Initialize API return status to success
2107 x_return_status := FND_API.G_RET_STS_SUCCESS;
2108
2109 -- API Body
2110 BEGIN
2111
2112 IF(p_source_type_code = 'PO') THEN
2113 BEGIN
2114 SELECT zx.default_taxation_country,
2115 zx.trx_business_category,
2116 zx.product_fisc_classification,
2117 zx.product_category,
2118 zx.line_intended_use,
2119 zx.product_type
2120 INTO l_po_country_code,
2121 x_trx_biz_category,
2122 x_prod_fisc_class_code,
2123 x_prod_category,
2124 x_intended_use,
2125 x_product_type
2126 FROM zx_lines_det_factors zx,
2127 po_line_locations pll
2128 WHERE zx.application_id = 201
2129 AND zx.event_class_code = 'PO_PA'
2130 AND zx.entity_code = 'PURCHASE_ORDER'
2131 AND zx.trx_id = pll.po_header_id
2132 AND zx.trx_line_id = pll.line_location_id
2133 AND zx.trx_level_type = 'SHIPMENT'
2134 AND trx_line_id = p_po_line_location_id;
2135 EXCEPTION
2136 WHEN NO_DATA_FOUND THEN
2137 l_po_country_code := NULL;
2138 END;
2139
2140 IF(l_po_country_code IS NULL OR l_po_country_code <> p_country_code) THEN
2141
2142 ZX_API_PUB.get_default_tax_det_attribs(p_api_version => 1.0,
2143 p_init_msg_list => FND_API.G_FALSE,
2144 p_commit => FND_API.G_FALSE,
2145 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2146 x_return_status => l_return_status,
2147 x_msg_count => l_msg_count,
2148 x_msg_data => l_msg_data,
2149 p_application_id => p_application_id,
2150 p_entity_code => p_entity_code,
2151 p_event_class_code => p_event_class_code,
2152 p_org_id => p_org_id,
2153 p_item_id => p_item_id,
2154 p_country_code => p_country_code,
2155 p_effective_date => p_effective_date,
2156 p_source_event_class_code => NULL,
2157 x_trx_biz_category => x_trx_biz_category,
2158 x_intended_use => x_intended_use,
2159 x_prod_category => x_prod_category,
2160 x_prod_fisc_class_code => x_prod_fisc_class_code,
2161 x_product_type => x_product_type);
2162
2163 -- If any errors happen abort API.
2164 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2165 RAISE FND_API.G_EXC_ERROR;
2166 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168 END IF;
2169 END IF;
2170 ELSE
2171
2172 ZX_API_PUB.get_default_tax_det_attribs(p_api_version => 1.0,
2173 p_init_msg_list => FND_API.G_FALSE,
2174 p_commit => FND_API.G_FALSE,
2175 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2176 x_return_status => l_return_status,
2177 x_msg_count => l_msg_count,
2178 x_msg_data => l_msg_data ,
2179 p_application_id => p_application_id,
2180 p_entity_code => p_entity_code,
2181 p_event_class_code => p_event_class_code,
2182 p_org_id => p_org_id,
2183 p_item_id => p_item_id,
2184 p_country_code => p_country_code,
2185 p_effective_date => p_effective_date,
2186 p_source_event_class_code => NULL,
2187 x_trx_biz_category => x_trx_biz_category,
2188 x_intended_use => x_intended_use,
2189 x_prod_category => x_prod_category,
2190 x_prod_fisc_class_code => x_prod_fisc_class_code,
2191 x_product_type => x_product_type);
2192
2193 -- If any errors happen abort API.
2194 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2195 RAISE FND_API.G_EXC_ERROR;
2196 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198 END IF;
2199 END IF;
2200 END;
2201
2202 -- Standard check of p_commit.
2203 IF FND_API.To_Boolean( p_commit ) THEN
2204 COMMIT WORK;
2205 END IF;
2206
2207 -- Standard call to get message count and if count is 1, get message info.
2208 FND_MSG_PUB.Count_And_Get
2209 (p_encoded => FND_API.g_false,
2210 p_count => x_msg_count,
2211 p_data => x_msg_data);
2212
2213 -- Standard End of Procedure/Function Logging
2214 INL_LOGGING_PVT.Log_EndProc (p_module_name => g_module_name,
2215 p_procedure_name => l_api_name);
2216
2217
2218 EXCEPTION
2219 WHEN FND_API.G_EXC_ERROR THEN
2220 -- Standard Expected Error Logging
2221 INL_LOGGING_PVT.Log_ExpecError (p_module_name => g_module_name,
2222 p_procedure_name => l_api_name);
2223 ROLLBACK TO Get_DefaultTaxDetAttribs_PVT;
2224 x_return_status := FND_API.G_RET_STS_ERROR;
2225 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2226 p_count => x_msg_count,
2227 p_data => x_msg_data);
2228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2229 -- Standard Unexpected Error Logging
2230 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2231 p_procedure_name => l_api_name);
2232 ROLLBACK TO Get_DefaultTaxDetAttribs_PVT;
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2235 p_count => x_msg_count,
2236 p_data => x_msg_data);
2237 WHEN OTHERS THEN
2238 -- Standard Unexpected Error Logging
2239 INL_LOGGING_PVT.Log_UnexpecError (p_module_name => g_module_name,
2240 p_procedure_name => l_api_name);
2241 ROLLBACK TO Get_DefaultTaxDetAttribs_PVT;
2242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2243 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2244 THEN
2245 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,l_api_name);
2246 END IF;
2247 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
2248 p_count => x_msg_count,
2249 p_data => x_msg_data);
2250 END Get_DefaultTaxDetAttribs;
2251
2252 END INL_TAX_PVT;