[Home] [Help]
PACKAGE BODY: APPS.POR_TAX_PVT
Source
1 PACKAGE BODY POR_TAX_PVT AS
2 /* $Header: PORVTAXB.pls 120.22 2011/09/28 11:45:00 rkandima ship $ */
3
4
5 -- Logging Static Variables
6 G_CURRENT_RUNTIME_LEVEL NUMBER;
7 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'PO.PLSQL.POR_TAX_PVT';
14
15 PROCEDURE calculate_tax (p_tax_head_tbl IN POR_TAX_HEADER_OBJ_TBL_TYPE,
16 p_tax_line_tbl IN POR_TAX_LINE_OBJ_TBL_TYPE,
17 p_tax_dist_tbl IN POR_TAX_DIST_OBJ_TBL_TYPE,
18 x_tax_dist_id_tbl OUT NOCOPY ICX_TBL_NUMBER,
19 x_tax_recov_amt_tbl OUT NOCOPY ICX_TBL_NUMBER,
20 x_tax_nonrecov_amt_tbl OUT NOCOPY ICX_TBL_NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER) IS
24
25 l_legal_entity_id PLS_INTEGER;
26 i PLS_INTEGER;
27 tbl_sz_line PLS_INTEGER;
28 j PLS_INTEGER;
29 tbl_sz_dist PLS_INTEGER;
30 acc_ccid NUMBER;
31 l_msg_count NUMBER;
32 l_return_status VARCHAR2(60);
33 l_msg_data VARCHAR2(480);
34 l_dist_id PLS_INTEGER;
35 l_recov_tax NUMBER;
36 l_non_recov_tax NUMBER;
37 l_progress PLS_INTEGER;
38
39
40 -- Logging Infra
41 l_procedure_name CONSTANT VARCHAR2(30) := 'calculate_tax';
42 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
43
44 --Bug 7412966 : Changed the code to calculate the rec and non-rec tax in functional currency
45 CURSOR c_get_tax IS
46 SELECT trx_line_dist_id,
47 sum(decode(recoverable_flag, 'Y', rec_nrec_tax_amt_funcl_curr, 0)) as recov_tax,
48 sum(decode(recoverable_flag, 'N', rec_nrec_tax_amt_funcl_curr, 0)) as non_recov_tax
49 FROM ZX_REC_NREC_DIST_GT
50 GROUP BY trx_line_dist_id;
51
52 BEGIN
53 l_progress := 0;
54
55 -- Logging Infra: Setting up runtime level
56 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57
58 -- Logging Infra: Procedure level
59 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
60 l_log_msg := '<-------------------->';
61 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
62 l_log_msg := l_procedure_name||'(+)';
63 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
64 END IF;
65
66 -- delete from ZX_TRX_HEADERS_GT
67 DELETE ZX_TRX_HEADERS_GT;
68
69 l_progress := 100;
70
71 -- Logging Infra: Statement level
72 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
73 l_log_msg := 'Deleted ZX_TRX_HEADERS_GT';
74 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
75 END IF;
76
77 -- get legal entity id
78 l_legal_entity_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_tax_head_tbl(1).org_id);
79
80 -- Logging Infra: Statement level
81 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
82 l_log_msg := 'XLE Legal Entity=' || l_legal_entity_id;
83 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
84 END IF;
85
86 l_progress := 200;
87
88 -- insert into ZX Headers global temp table
89 INSERT INTO ZX_TRX_HEADERS_GT (internal_organization_id,
90 icx_session_id,
91 application_id,
92 entity_code,
93 event_class_code,
94 event_type_code,
95 trx_id,
96 trx_date,
97 ledger_id,
98 legal_entity_id,
99 rounding_bill_to_party_id,
100 default_taxation_country,
101 quote_flag,
102 document_sub_type)
103 VALUES(p_tax_head_tbl(1).org_id,
104 p_tax_head_tbl(1).icx_session_id,
105 201,
106 'REQUISITION',
107 'REQUISITION',
108 'REQ_CREATED',
109 p_tax_head_tbl(1).trx_id,
110 p_tax_head_tbl(1).trx_date,
111 p_tax_head_tbl(1).ledger_id,
112 l_legal_entity_id,
113 p_tax_head_tbl(1).rndg_bill_to_party_id,
114 p_tax_head_tbl(1).taxation_country,
115 'Y',
116 p_tax_head_tbl(1).doc_subtype);
117
118 l_progress := 300;
119
120 -- Logging Infra: Statement level
121 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
122 l_log_msg := 'Inserted into ZX_TRX_HEADERS_GT';
123 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
124 END IF;
125
126 -- delete from ZX_TRANSACTION_LINES_GT
127 DELETE ZX_TRANSACTION_LINES_GT;
128
129 l_progress := 400;
130
131 -- Logging Infra: Statement level
132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
133 l_log_msg := 'Deleted from ZX_TRANSACTION_LINES_GT';
134 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
135 END IF;
136
137 -- insert into ZX Lines global temp table
138 i := 1;
139 j := 1;
140 tbl_sz_line := p_tax_line_tbl.count();
141 tbl_sz_dist := p_tax_dist_tbl.count();
142
143 LOOP
144
145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
146 l_log_msg := 'Getting the Account_CCID for the Line ID >>> '|| p_tax_line_tbl(i).trx_line_id;
147 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
148 END IF;
149
150 LOOP
151 IF (p_tax_line_tbl(i).trx_line_id=p_tax_dist_tbl(j).trx_line_id) THEN --- bug 12769270 changed condition from trx_id to trx_line_id
152 acc_ccid := p_tax_dist_tbl(j).account_ccid;
153 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
154 l_log_msg := 'Account_CCID >>> '||acc_ccid||' for the Line ID >>> '|| p_tax_line_tbl(i).trx_line_id;
155 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
156 END IF;
157 EXIT;
158 END IF;
159 j := j+1;
160 EXIT WHEN j > tbl_sz_dist;
161 END LOOP ;
162
163 INSERT INTO ZX_TRANSACTION_LINES_GT( application_id,
164 entity_code,
165 event_class_code,
166 trx_id,
167 trx_level_type,
168 trx_line_id,
169 line_class,
170 line_level_action,
171 trx_line_type,
172 trx_line_date,
173 trx_business_category,
174 line_intended_use,
175 user_defined_fisc_class,
176 line_amt_includes_tax_flag,
177 line_amt,
178 trx_line_quantity,
179 unit_price,
180 product_id,
181 product_fisc_classification,
182 product_org_id,
183 uom_code,
184 product_type,
185 product_code,
186 product_category,
187 ship_to_party_id,
188 ship_from_party_id,
189 bill_to_party_id,
190 bill_from_party_id,
191 ship_from_party_site_id,
192 bill_from_party_site_id,
193 ship_to_location_id,
194 ship_from_location_id,
195 bill_to_location_id,
196 ship_third_pty_acct_id,
197 ship_third_pty_acct_site_id,
198 assessable_value,
199 historical_flag,
200 trx_line_currency_code,
201 trx_line_currency_conv_date,
202 trx_line_currency_conv_rate,
203 trx_line_currency_conv_type,
204 trx_line_mau,
205 trx_line_precision,
206 historical_tax_code_id,
207 input_tax_classification_code,
208 user_upd_det_factors_flag,
209 account_ccid,
210 bill_From_location_id
211 )
212 VALUES(201,
213 'REQUISITION',
214 'REQUISITION',
215 p_tax_line_tbl(i).trx_id,
216 'LINE',
217 p_tax_line_tbl(i).trx_line_id,
218 'INVOICE',
219 'CREATE',
220 'ITEM',
221 p_tax_line_tbl(i).trx_line_date,
222 p_tax_line_tbl(i).trx_business_cat,
223 p_tax_line_tbl(i).line_intended_use,
224 p_tax_line_tbl(i).user_fiscal_class,
225 'N',
226 p_tax_line_tbl(i).line_amt,
227 p_tax_line_tbl(i).trx_line_quantity,
228 p_tax_line_tbl(i).unit_price,
229 p_tax_line_tbl(i).product_id,
230 p_tax_line_tbl(i).prod_fiscal_class,
231 p_tax_line_tbl(i).product_org_id,
232 p_tax_line_tbl(i).uom_code,
233 p_tax_line_tbl(i).product_type,
234 p_tax_line_tbl(i).product_code,
235 p_tax_line_tbl(i).product_category,
236 p_tax_line_tbl(i).ship_to_party_id,
237 p_tax_line_tbl(i).ship_from_party_id,
238 p_tax_line_tbl(i).bill_to_party_id,
239 p_tax_line_tbl(i).bill_from_party_id,
240 p_tax_line_tbl(i).ship_from_party_site_id,
241 p_tax_line_tbl(i).bill_from_party_site_id,
242 p_tax_line_tbl(i).ship_to_location_id,
243 (SELECT hzps.location_id
244 FROM hz_party_sites hzps
245 WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id),
246 p_tax_line_tbl(i).bill_to_location_id,
247 p_tax_line_tbl(i).ship_third_pty_id,
248 p_tax_line_tbl(i).ship_third_pty_site_id,
249 p_tax_line_tbl(i).assessable_value,
250 'N',
251 p_tax_line_tbl(i).currency_code,
252 p_tax_line_tbl(i).currency_date,
253 p_tax_line_tbl(i).currency_rate,
254 p_tax_line_tbl(i).currency_type,
255 p_tax_line_tbl(i).currency_min_unit,
256 p_tax_line_tbl(i).currency_precision,
257 p_tax_line_tbl(i).historical_tax_id,
258 p_tax_line_tbl(i).input_tax_class,
259 p_tax_line_tbl(i).user_override_flag,
260 acc_ccid,
261 (SELECT hzps.location_id
262 FROM hz_party_sites hzps
263 WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id) /* 8680577 - Passing Location Id as Bill From Location Id */
264
265 );
266
267 -- Logging Infra: Statement level
268 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
269 l_log_msg := 'Inserted record into ZX_TRANSACTION_LINES_GT.';
270 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
271 l_log_msg := '>>> LineId=' || p_tax_line_tbl(i).trx_line_id || '; OverrideFlag=' || p_tax_line_tbl(i).user_override_flag;
272 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
273 END IF;
274
275 i := i + 1;
276 EXIT WHEN (i > tbl_sz_line);
277 END LOOP;
278
279 l_progress := 500;
280
281 -- delete from ZX_ITM_DISTRIBUTIONS_GT;
282 DELETE ZX_ITM_DISTRIBUTIONS_GT;
283
284 l_progress := 600;
285
286 -- Logging Infra: Statement level
287 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
288 l_log_msg := 'Deleted ZX_ITM_DISTRIBUTIONS_GT';
289 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
290 END IF;
291
292 -- insert into ZX Distributions global temp table
293 i := 1;
294 tbl_sz_dist := p_tax_dist_tbl.count();
295
296 LOOP
297 INSERT INTO ZX_ITM_DISTRIBUTIONS_GT (application_id,
298 entity_code,
299 event_class_code,
300 trx_id,
301 trx_line_id,
302 trx_level_type,
303 trx_line_dist_id,
304 dist_level_action,
305 trx_line_dist_date,
306 item_dist_number,
307 dist_intended_use,
308 task_id,
309 award_id,
310 project_id,
311 expenditure_type,
312 expenditure_organization_id,
313 expenditure_item_date,
314 trx_line_dist_amt,
315 trx_line_dist_qty,
316 trx_line_quantity,
317 account_ccid,
318 historical_flag,
319 OVERRIDING_RECOVERY_RATE)
320 VALUES(201,
321 'REQUISITION',
322 'REQUISITION',
323 p_tax_dist_tbl(i).trx_id,
324 p_tax_dist_tbl(i).trx_line_id,
325 'LINE',
326 p_tax_dist_tbl(i).trx_line_dist_id,
327 'CREATE',
328 p_tax_dist_tbl(i).trx_line_dist_date,
329 p_tax_dist_tbl(i).item_dist_number,
330 p_tax_dist_tbl(i).intended_use,
331 p_tax_dist_tbl(i).task_id,
332 p_tax_dist_tbl(i).award_id,
333 p_tax_dist_tbl(i).project_id,
334 p_tax_dist_tbl(i).expenditure_type,
335 p_tax_dist_tbl(i).expenditure_org_id,
336 p_tax_dist_tbl(i).expenditure_item_date,
337 p_tax_dist_tbl(i).trx_line_dist_amt,
338 p_tax_dist_tbl(i).trx_line_dist_quantity,
339 p_tax_dist_tbl(i).trx_line_quantity,
340 p_tax_dist_tbl(i).account_ccid,
341 'N',
342 p_tax_dist_tbl(i).recovery_rate_override);
343
344 -- Logging Infra: Statement level
345 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
346 l_log_msg := 'Inserted record into ZX_ITM_DISTRIBUTIONS_GT. DistId=' || p_tax_dist_tbl(i).trx_line_dist_id;
347 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
348 END IF;
349
350 i := i + 1;
351 EXIT WHEN (i > tbl_sz_dist);
352 END LOOP;
353
354 l_progress := 700;
355
356 -- Logging Infra: Statement level
357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
358 l_log_msg := 'ZX calculate_tax(+)';
359 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
360 END IF;
361
362 -- Now calculate the tax
363 zx_api_pub.calculate_tax(p_api_version => 1.0,
364 p_init_msg_list => null,
365 p_commit => null,
366 p_validation_level => null,
367 x_return_status => l_return_status,
368 x_msg_count => l_msg_count,
369 x_msg_data => l_msg_data);
370
371 -- Logging Infra: Statement level
372 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
373 l_log_msg := 'ZX calculate_tax returned status=' || l_return_status;
374 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
375 l_log_msg := 'ZX calculate_tax(-)';
376 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
377 END IF;
378
379 l_progress := 800;
380
381 x_msg_count := l_msg_count;
382 x_msg_data := l_msg_data;
383 x_return_status := l_return_status;
384
385 -- only continue if calculate_tax was successful
386 IF (x_return_status = 'S') THEN
387
388 -- Logging Infra: Statement level
389 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
390 l_log_msg := 'ZX determine_recovery(+)';
391 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
392 END IF;
393
394 -- Now determine the recovery
395 zx_api_pub.determine_recovery(p_api_version => 1.0,
396 p_init_msg_list => null,
397 p_commit => null,
398 p_validation_level => null,
399 x_return_status => l_return_status,
400 x_msg_count => l_msg_count,
401 x_msg_data => l_msg_data);
402
403 -- Logging Infra: Statement level
404 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
405 l_log_msg := 'ZX determine_recovery returned status=' || l_return_status;
406 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
407 l_log_msg := 'ZX determine_recovery(-)';
408 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
409 END IF;
410
411 l_progress := 900;
412
413 -- update the return error-handling variables
414 x_msg_count := x_msg_count + l_msg_count;
415 x_msg_data := x_msg_data || ':' || l_msg_data;
416 x_return_status := x_return_status || ':' || l_return_status;
417
418 -- query resultant tax from global tables
419 i := 1;
420 OPEN c_get_tax;
421 LOOP
422 FETCH c_get_tax into l_dist_id, l_recov_tax, l_non_recov_tax;
423 EXIT WHEN c_get_tax%NOTFOUND;
424
425 IF (i=1) THEN
426 -- Initialize the output tables
427 x_tax_dist_id_tbl := ICX_TBL_NUMBER(null);
428 x_tax_recov_amt_tbl := ICX_TBL_NUMBER(null);
429 x_tax_nonrecov_amt_tbl := ICX_TBL_NUMBER(null);
430 ELSE
431 x_tax_dist_id_tbl.EXTEND;
432 x_tax_recov_amt_tbl.EXTEND;
433 x_tax_nonrecov_amt_tbl.EXTEND;
434 END IF;
435
436 x_tax_dist_id_tbl(i) := l_dist_id;
437 x_tax_recov_amt_tbl(i) := l_recov_tax;
438 x_tax_nonrecov_amt_tbl(i) := l_non_recov_tax;
439
440 -- Logging Infra: Statement level
441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
442 l_log_msg := 'Tax=' || l_dist_id || ':' || l_recov_tax || ':' || l_non_recov_tax;
443 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
444 END IF;
445
446 i := i+1;
447 END LOOP;
448 CLOSE c_get_tax;
449
450 l_progress := 1000;
451
452 -- Logging Infra: Statement level
453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
454 i := i-1;
455 l_log_msg := 'Found tax for ' || i || ' distributions.';
456 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
457 END IF;
458 END IF;
459
460 -- Logging Infra: Procedure level
461 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
462 l_log_msg := l_procedure_name||'(-)';
463 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
464 l_log_msg := '<-------------------->';
465 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 RAISE_APPLICATION_ERROR(-20000,
471 'Exception at POR_TAX_PVT.calculate_tax():'
472 || ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
473 || ' Progress= ' || l_progress);
474
475 END calculate_tax;
476
477
478
479
480
481 PROCEDURE get_default_tax_attributes (p_tax_head_tbl IN POR_TAX_HEADER_OBJ_TBL_TYPE,
482 p_tax_line_tbl IN POR_TAX_LINE_OBJ_TBL_TYPE,
483 x_tax_country OUT NOCOPY VARCHAR2,
484 x_doc_subtype OUT NOCOPY VARCHAR2,
485 x_tax_class_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
486 x_trx_bus_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
487 x_prd_fisc_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
488 x_prd_type_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
489 x_int_use_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
490 x_usr_fisc_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
491 x_ass_val_tbl OUT NOCOPY ICX_TBL_NUMBER,
492 x_prd_cat_tbl OUT NOCOPY ICX_TBL_VARCHAR240,
493 x_override_tbl OUT NOCOPY ICX_TBL_FLAG,
494 x_line_id_tbl OUT NOCOPY ICX_TBL_NUMBER,
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER) IS
498
499 l_legal_entity_id PLS_INTEGER;
500 i PLS_INTEGER;
501 tbl_sz PLS_INTEGER;
502 l_msg_count NUMBER;
503 l_return_status VARCHAR2(60);
504 l_msg_data VARCHAR2(480);
505 l_tax_obj_init POR_TAX_OBJECT_TYPE;
506 l_user_override_flag VARCHAR2(1);
507 l_progress PLS_INTEGER;
508
509 -- Additional Tax Attributes
510 l_line_id NUMBER;
511 l_tax_country VARCHAR2(2);
512 l_doc_subtype VARCHAR2(240);
513 l_tax_class_code VARCHAR2(30);
514 l_intended_use VARCHAR2(240);
515 l_trx_business_cat VARCHAR2(240);
516 l_user_fiscal_class VARCHAR2(30);
517 l_prod_fiscal_class VARCHAR2(240);
518 l_product_category VARCHAR2(240);
519 l_product_type VARCHAR2(240);
520 l_assessable_value NUMBER;
521 l_line_level_action VARCHAR2(30);
522
523
524 -- Logging Infra
525 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_tax_attributes';
526 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
527
528 CURSOR c_tax_head IS
529 SELECT default_taxation_country,
530 document_sub_type
531 FROM ZX_TRX_HEADERS_GT;
532
533 CURSOR c_tax_line IS
534 SELECT TRX_LINE_ID,
535 INPUT_TAX_CLASSIFICATION_CODE,
536 TRX_BUSINESS_CATEGORY,
537 PRODUCT_FISC_CLASSIFICATION,
538 PRODUCT_TYPE,
539 LINE_INTENDED_USE,
540 USER_DEFINED_FISC_CLASS,
541 ASSESSABLE_VALUE,
542 PRODUCT_CATEGORY,
543 USER_UPD_DET_FACTORS_FLAG
544 FROM ZX_TRANSACTION_LINES_GT;
545
546 BEGIN
547
548 l_progress := 0;
549
550 -- Logging Infra: Setting up runtime level
551 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
552
553 -- Logging Infra: Procedure level
554 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
555 l_log_msg := '<-------------------->';
556 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
557 l_log_msg := l_procedure_name||'(+)';
558 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
559 END IF;
560
561 -- get legal entity id
562 l_legal_entity_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_tax_head_tbl(1).org_id);
563
564 -- Logging Infra: Statement level
565 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
566 l_log_msg := 'XLE Legal Entity=' || l_legal_entity_id;
567 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
568 END IF;
569
570 l_progress := 100;
571
572 -- delete from ZX_TRX_HEADERS_GT
573 DELETE ZX_TRX_HEADERS_GT;
574
575 l_progress := 200;
576
577 -- delete from ZX_TRANSACTION_LINES_GT
578 DELETE ZX_TRANSACTION_LINES_GT;
579
580 l_progress := 300;
581
582 -- insert into ZX Lines global temp table
583 i := 1;
584 tbl_sz := p_tax_line_tbl.count();
585
586 -- Logging Infra: Statement level
587 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
588 l_log_msg := 'Input lines table size=' || tbl_sz;
589 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
590 END IF;
591
592 LOOP
593
594 IF (i=1) THEN
595 -- Logging Infra: Statement level
596 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
597 l_log_msg := 'Inserting into ZX_TRX_HEADERS_GT';
598 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
599 END IF;
600
601 -- insert into ZX Headers global temp table
602 INSERT INTO ZX_TRX_HEADERS_GT (internal_organization_id,
603 icx_session_id,
604 application_id,
605 entity_code,
606 event_class_code,
607 event_type_code,
608 trx_id,
609 trx_date,
610 ledger_id,
611 legal_entity_id,
612 rounding_bill_to_party_id,
613 default_taxation_country,
614 quote_flag,
615 document_sub_type)
616 VALUES(p_tax_head_tbl(1).org_id,
617 p_tax_head_tbl(1).icx_session_id,
618 201,
619 'REQUISITION',
620 'REQUISITION',
621 'REQ_CREATED',
622 p_tax_head_tbl(1).trx_id,
623 p_tax_head_tbl(1).trx_date,
624 p_tax_head_tbl(1).ledger_id,
625 l_legal_entity_id,
626 p_tax_head_tbl(1).rndg_bill_to_party_id,
627 NULL,
628 'Y',
629 NULL);
630 END IF;
631
632
633 -- Logging Infra: Statement level
634 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
635 l_log_msg := 'Inserting into ZX_TRANSACTION_LINES_GT';
636 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
637 l_log_msg := '--> Defaulting attribute is ship_to_loc_org_id=' || p_tax_line_tbl(i).product_org_id;
638 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
639 END IF;
640
641 --- bug 13024005 start
642
643 IF p_tax_line_tbl(i).tax_attribute_update_code IS NULL THEN
644
645 Begin
646
647 SELECT 'UPDATE'
648 INTO l_line_level_action
649 FROM ZX_LINES_DET_FACTORS
650 WHERE trx_id = p_tax_line_tbl(i).trx_id
651 AND trx_line_id = p_tax_line_tbl(i).trx_line_id
652 AND USER_UPD_DET_FACTORS_FLAG = 'Y';
653
654 EXCEPTION
655
656 WHEN No_Data_Found THEN
657 l_line_level_action := 'CREATE';
658
659 END;
660
661 ELSE
662
663 l_line_level_action := p_tax_line_tbl(i).tax_attribute_update_code;
664
665 END IF;
666
667 --- bug 13024005 end
668
669 INSERT INTO ZX_TRANSACTION_LINES_GT(application_id,
670 entity_code,
671 event_class_code,
672 trx_id,
673 trx_level_type,
674 trx_line_id,
675 line_class,
676 line_level_action,
677 trx_line_type,
678 trx_line_date,
679 trx_business_category,
680 line_intended_use,
681 user_defined_fisc_class,
682 line_amt_includes_tax_flag,
683 line_amt,
684 trx_line_quantity,
685 unit_price,
686 product_id,
687 product_fisc_classification,
688 product_org_id,
689 uom_code,
690 product_type,
691 product_code,
692 product_category,
693 ship_to_party_id,
694 ship_from_party_id,
695 bill_to_party_id,
696 bill_from_party_id,
697 ship_from_party_site_id,
698 bill_from_party_site_id,
699 ship_to_location_id,
700 ship_from_location_id,
701 bill_to_location_id,
702 ship_third_pty_acct_id,
703 ship_third_pty_acct_site_id,
704 assessable_value,
705 historical_flag,
706 trx_line_currency_code,
707 trx_line_currency_conv_date,
708 trx_line_currency_conv_rate,
709 trx_line_currency_conv_type,
710 trx_line_mau,
711 trx_line_precision,
712 historical_tax_code_id,
713 input_tax_classification_code,
714 user_upd_det_factors_flag,
715 DEFAULTING_ATTRIBUTE1,
716 Bill_From_location_id
717 )
718 VALUES(201,
719 'REQUISITION',
720 'REQUISITION',
721 p_tax_line_tbl(i).trx_id,
722 'LINE',
723 p_tax_line_tbl(i).trx_line_id,
724 'INVOICE',
725 l_line_level_action,
726 'ITEM',
727 p_tax_line_tbl(i).trx_line_date,
728 NULL,
729 NULL,
730 NULL,
731 'N',
732 p_tax_line_tbl(i).line_amt,
733 p_tax_line_tbl(i).trx_line_quantity,
734 p_tax_line_tbl(i).unit_price,
735 p_tax_line_tbl(i).product_id,
736 NULL,
737 p_tax_line_tbl(i).product_org_id,
738 p_tax_line_tbl(i).uom_code,
739 NULL,
740 p_tax_line_tbl(i).product_code,
741 NULL,
742 p_tax_line_tbl(i).ship_to_party_id,
743 p_tax_line_tbl(i).ship_from_party_id,
744 p_tax_line_tbl(i).bill_to_party_id,
745 p_tax_line_tbl(i).bill_from_party_id,
746 p_tax_line_tbl(i).ship_from_party_site_id,
747 p_tax_line_tbl(i).bill_from_party_site_id,
748 p_tax_line_tbl(i).ship_to_location_id,
749 (SELECT hzps.location_id
750 FROM hz_party_sites hzps
751 WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id),
752 p_tax_line_tbl(i).bill_to_location_id,
753 p_tax_line_tbl(i).ship_third_pty_id,
754 p_tax_line_tbl(i).ship_third_pty_site_id,
755 NULL,
756 'N',
757 p_tax_line_tbl(i).currency_code,
758 p_tax_line_tbl(i).currency_date,
759 p_tax_line_tbl(i).currency_rate,
760 p_tax_line_tbl(i).currency_type,
761 p_tax_line_tbl(i).currency_min_unit,
762 p_tax_line_tbl(i).currency_precision,
763 p_tax_line_tbl(i).historical_tax_id,
764 NULL,
765 NULL,
766 p_tax_line_tbl(i).product_org_id,
767 (SELECT hzps.location_id
768 FROM hz_party_sites hzps
769 WHERE hzps.party_site_id =p_tax_line_tbl(i).ship_from_party_site_id)
770 );
771 i := i + 1;
772 EXIT WHEN (i > tbl_sz);
773 END LOOP;
774
775 l_progress := 400;
776
777 -- Logging Infra: Statement level
778 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
779 l_log_msg := 'ZX get_default_tax_det_attribs(+)';
780 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
781 END IF;
782
783 -- Now get the default tax attibutes
784 zx_api_pub.get_default_tax_det_attribs(p_api_version => 1.0,
785 p_init_msg_list => null,
786 p_commit => null,
787 p_validation_level => null,
788 x_return_status => l_return_status,
789 x_msg_count => l_msg_count,
790 x_msg_data => l_msg_data);
791
792 l_progress := 500;
793
794 -- Logging Infra: Statement level
795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
796 l_log_msg := 'ZX get_default_tax_det_attribs returned status=' || l_return_status;
797 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
798 l_log_msg := 'ZX get_default_tax_det_attribs(-)';
799 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
800 END IF;
801
802 x_msg_count := l_msg_count;
803 x_msg_data := l_msg_data;
804 x_return_status := l_return_status;
805
806 -- Query the tax header attributes from the global temp table
807 OPEN c_tax_head;
808 FETCH c_tax_head into x_tax_country, x_doc_subtype;
809 CLOSE c_tax_head;
810
811 l_progress := 600;
812
813 -- Logging Infra: Statement level
814 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
815 l_log_msg := 'DEFAULT: tax_country=' || x_tax_country;
816 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
817 l_log_msg := 'DEFAULT: doc_subtype=' || x_doc_subtype;
818 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
819 END IF;
820
821 -- Query the tax line attributes from the global temp table
822 i := 1;
823 OPEN c_tax_line;
824 LOOP
825 FETCH c_tax_line into l_line_id,
826 l_tax_class_code,
827 l_trx_business_cat,
828 l_prod_fiscal_class,
829 l_product_type,
830 l_intended_use,
831 l_user_fiscal_class,
832 l_assessable_value,
833 l_product_category,
834 l_user_override_flag;
835
836 EXIT WHEN c_tax_line%NOTFOUND;
837
838 -- Logging Infra: Statement level
839 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
840 l_log_msg := 'DEFAULT: req line id=' || l_line_id;
841 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
842 l_log_msg := 'DEFAULT: business_cat=' || l_trx_business_cat;
843 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
844 l_log_msg := 'DEFAULT: intended_use=' || l_intended_use;
845 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
846 l_log_msg := 'DEFAULT: user_fisc_class=' || l_user_fiscal_class;
847 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
848 l_log_msg := 'DEFAULT: prod_fisc_class=' || l_prod_fiscal_class;
849 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
850 l_log_msg := 'DEFAULT: prod_category=' || l_product_category;
851 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
852 l_log_msg := 'DEFAULT: prod_type=' || l_product_type;
853 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
854 l_log_msg := 'DEFAULT: assessable_value=' || l_assessable_value;
855 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
856 l_log_msg := 'DEFAULT: tax_classification=' || l_tax_class_code;
857 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
858 l_log_msg := 'DEFAULT: override_flag=' || l_user_override_flag;
859 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
860 END IF;
861
862 IF (i=1) THEN
863 -- Initialize the tables
864 x_line_id_tbl := ICX_TBL_NUMBER(null);
865 x_tax_class_tbl := ICX_TBL_VARCHAR240(null);
866 x_trx_bus_tbl := ICX_TBL_VARCHAR240(null);
867 x_prd_fisc_tbl := ICX_TBL_VARCHAR240(null);
868 x_prd_type_tbl := ICX_TBL_VARCHAR240(null);
869 x_int_use_tbl := ICX_TBL_VARCHAR240(null);
870 x_usr_fisc_tbl := ICX_TBL_VARCHAR240(null);
871 x_ass_val_tbl := ICX_TBL_NUMBER(null);
872 x_prd_cat_tbl := ICX_TBL_VARCHAR240(null);
873 x_override_tbl := ICX_TBL_FLAG(null);
874 ELSE
875 -- extend the tables
876 x_line_id_tbl.EXTEND;
877 x_tax_class_tbl.EXTEND;
878 x_trx_bus_tbl.EXTEND;
879 x_prd_fisc_tbl.EXTEND;
880 x_prd_type_tbl.EXTEND;
881 x_int_use_tbl.EXTEND;
882 x_usr_fisc_tbl.EXTEND;
883 x_ass_val_tbl.EXTEND;
884 x_prd_cat_tbl.EXTEND;
885 x_override_tbl.EXTEND;
886 END IF;
887
888 x_line_id_tbl(i) := l_line_id;
889 x_tax_class_tbl(i) := l_tax_class_code;
890 x_trx_bus_tbl(i) := l_trx_business_cat;
891 x_prd_fisc_tbl(i) := l_prod_fiscal_class;
892 x_prd_type_tbl(i) := l_product_type;
893 x_int_use_tbl(i) := l_intended_use;
894 x_usr_fisc_tbl(i) := l_user_fiscal_class;
895 x_ass_val_tbl(i) := l_assessable_value;
896 x_prd_cat_tbl(i) := l_product_category;
897 x_override_tbl(i) := l_user_override_flag;
898
899 i := i + 1;
900 END LOOP;
901 CLOSE c_tax_line;
902
903 -- Logging Infra: Procedure level
904 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
905 l_log_msg := l_procedure_name||'(-)';
906 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
907 l_log_msg := '<-------------------->';
908 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
909 END IF;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 RAISE_APPLICATION_ERROR(-20000,
914 'Exception at POR_TAX_PVT.get_default_tax_attributes():'
915 || ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
916 || ' Progress= ' || l_progress);
917
918 END get_default_tax_attributes;
919
920
921
922 PROCEDURE delete_all_tax_attr (p_org_id IN NUMBER,
923 p_trx_id IN NUMBER,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_data OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER) IS
927
928 PRAGMA AUTONOMOUS_TRANSACTION;
929
930 l_transaction_rec ZX_API_PUB.transaction_rec_type;
931 l_msg_count NUMBER;
932 l_return_status VARCHAR2(60);
933 l_msg_data VARCHAR2(480);
934 l_progress PLS_INTEGER;
935
936 -- Logging Infra
937 l_procedure_name CONSTANT VARCHAR2(30) := 'delete_all_tax_lines';
938 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
939
940 BEGIN
941
942 l_progress := 0;
943
944 -- Logging Infra: Setting up runtime level
945 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
946
947 -- Logging Infra: Procedure level
948 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
949 l_log_msg := '<-------------------->';
950 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
951 l_log_msg := l_procedure_name||'(+)';
952 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
953 END IF;
954
955 -- populate l_transaction_rec
956 l_transaction_rec.internal_organization_id := p_org_id;
957 l_transaction_rec.application_id := 201;
958 l_transaction_rec.entity_code := 'REQUISITION';
959 l_transaction_rec.event_class_code := 'REQUISITION';
960 l_transaction_rec.event_type_code := 'REQ_DELETED';
961 l_transaction_rec.trx_id := p_trx_id;
962
963 -- Logging Infra: Statement level
964 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
965 l_log_msg := 'Deleting record for trx_id=' || p_trx_id;
966 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
967 l_log_msg := 'ZX global_document_update(+)';
968 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
969 END IF;
970
971 -- this will delete all tax attribute records for this requisition
972 zx_api_pub.global_document_update(p_api_version => 1.0,
973 p_init_msg_list => null,
974 p_commit => null,
975 p_validation_level => null,
976 x_return_status => l_return_status,
977 x_msg_count => l_msg_count,
978 x_msg_data => l_msg_data,
979 p_transaction_rec => l_transaction_rec);
980
981 l_progress := 100;
982
983 -- Logging Infra: Statement level
984 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
985 l_log_msg := 'ZX global_document_update returned status=' || l_return_status;
986 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
987 l_log_msg := 'ZX global_document_update(-)';
988 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
989 END IF;
990
991 x_msg_count := l_msg_count;
992 x_msg_data := l_msg_data;
993 x_return_status := l_return_status;
994
995 -- Logging Infra: Procedure level
996 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
997 l_log_msg := l_procedure_name||'(-)';
998 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
999 l_log_msg := '<-------------------->';
1000 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1001 END IF;
1002
1003 COMMIT;
1004
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 -- rollback
1008 ROLLBACK;
1009 -- raise exception for java layer
1010 RAISE_APPLICATION_ERROR(-20000,
1011 'Exception at POR_TAX_PVT.delete_all_tax_attr():'
1012 || ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
1013 || ' Progress= ' || l_progress || '; ROLLBACK Complete.');
1014
1015 END delete_all_tax_attr;
1016
1017
1018
1019
1020 PROCEDURE insert_line_det_attr (p_tax_info_tbl IN POR_INSERT_TAX_OBJ_TBL_TYPE,
1021 x_return_status OUT NOCOPY VARCHAR2,
1022 x_msg_data OUT NOCOPY VARCHAR2,
1023 x_msg_count OUT NOCOPY NUMBER ) IS
1024
1025 PRAGMA AUTONOMOUS_TRANSACTION;
1026
1027 l_transaction_rec ZX_API_PUB.transaction_rec_type;
1028 l_transaction_line_rec ZX_API_PUB.transaction_line_rec_type;
1029
1030 l_legal_entity_id PLS_INTEGER;
1031 i PLS_INTEGER;
1032 tbl_sz PLS_INTEGER;
1033 l_msg_count NUMBER;
1034 l_return_status VARCHAR2(60);
1035 l_msg_data VARCHAR2(480);
1036 l_progress PLS_INTEGER;
1037
1038 -- Logging Infra
1039 l_procedure_name CONSTANT VARCHAR2(30) := 'insert_line_det_attr';
1040 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1041
1042 BEGIN
1043
1044 l_progress := 0;
1045
1046 -- Logging Infra: Setting up runtime level
1047 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1048
1049 -- Logging Infra: Procedure level
1050 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1051 l_log_msg := '<-------------------->';
1052 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1053 l_log_msg := l_procedure_name||'(+)';
1054 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1055 END IF;
1056
1057 -- get legal entity id
1058 l_legal_entity_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_tax_info_tbl(1).org_id);
1059
1060 -- Logging Infra: Statement level
1061 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1062 l_log_msg := 'XLE Legal Entity=' || l_legal_entity_id;
1063 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1064 END IF;
1065
1066 l_progress := 50;
1067
1068 -- populate l_transaction_rec
1069 l_transaction_rec.internal_organization_id := p_tax_info_tbl(1).org_id;
1070 l_transaction_rec.application_id := 201;
1071 l_transaction_rec.entity_code := 'REQUISITION';
1072 l_transaction_rec.event_class_code := 'REQUISITION';
1073 l_transaction_rec.event_type_code := 'REQ_DELETED';
1074 l_transaction_rec.trx_id := p_tax_info_tbl(1).trx_id;
1075
1076 -- Logging Infra: Statement level
1077 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1078 l_log_msg := 'Deleting record for trx_id=' || p_tax_info_tbl(1).trx_id;
1079 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1080 l_log_msg := 'ZX global_document_update(+)';
1081 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1082 END IF;
1083
1084 -- this will delete all tax attribute records for this requisition
1085 zx_api_pub.global_document_update(p_api_version => 1.0,
1086 p_init_msg_list => null,
1087 p_commit => null,
1088 p_validation_level => null,
1089 x_return_status => l_return_status,
1090 x_msg_count => l_msg_count,
1091 x_msg_data => l_msg_data,
1092 p_transaction_rec => l_transaction_rec);
1093
1094 l_progress := 100;
1095
1096 -- Logging Infra: Statement level
1097 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1098 l_log_msg := 'ZX global_document_update returned status=' || l_return_status;
1099 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1100 l_log_msg := 'ZX global_document_update(-)';
1101 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1102 END IF;
1103
1104 x_msg_count := l_msg_count;
1105 x_msg_data := l_msg_data;
1106 x_return_status := l_return_status;
1107
1108 -- loop through and insert reqLine data into EBTax global plsql table
1109 i := 1;
1110 tbl_sz := p_tax_info_tbl.count();
1111
1112 LOOP
1113
1114 -- EBTax initialization procedure
1115 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(i);
1116
1117 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i) :=
1118 p_tax_info_tbl(i).org_id;
1119 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(i) :=
1120 201;
1121 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(i) :=
1122 'REQUISITION';
1123 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(i) :=
1124 'REQUISITION';
1125 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(i) :=
1126 'REQ_CREATED';
1127 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(i) :=
1128 p_tax_info_tbl(i).trx_id;
1129 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(i) :=
1130 p_tax_info_tbl(i).trx_date;
1131 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(i) :=
1132 p_tax_info_tbl(i).ledger_id;
1133 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(i) :=
1134 p_tax_info_tbl(i).currency_code;
1135 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(i) :=
1136 p_tax_info_tbl(i).currency_date;
1137 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(i) :=
1138 p_tax_info_tbl(i).currency_rate;
1139 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(i) :=
1140 p_tax_info_tbl(i).currency_type;
1141 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(i) :=
1142 p_tax_info_tbl(i).currency_min_unit;
1143 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(i) :=
1144 p_tax_info_tbl(i).currency_precision;
1145 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_PRECISION(i) :=
1146 p_tax_info_tbl(i).currency_precision;
1147 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(i) :=
1148 l_legal_entity_id;
1149 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOCUMENT_SUB_TYPE(i) :=
1150 p_tax_info_tbl(i).doc_subtype;
1151 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(i) :=
1152 p_tax_info_tbl(i).rndg_bill_to_party_id;
1153 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DEFAULT_TAXATION_COUNTRY(i) :=
1154 p_tax_info_tbl(i).tax_country;
1155 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_REPORTING_FLAG(i) :=
1156 'N';
1157 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INPUT_TAX_CLASSIFICATION_CODE(i) :=
1158 p_tax_info_tbl(i).input_tax_class;
1159 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_FLAG(i) :=
1160 'N';
1161 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(i) :=
1162 'LINE';
1163 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(i) :=
1164 p_tax_info_tbl(i).trx_line_id;
1165 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_CLASS(i) :=
1166 'INVOICE';
1167 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(i) :=
1168 'CREATE';
1169 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_TYPE(i) :=
1170 'ITEM';
1171 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(i) :=
1172 p_tax_info_tbl(i).trx_line_date;
1173 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_BUSINESS_CATEGORY(i) :=
1174 p_tax_info_tbl(i).trx_business_cat;
1175 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_INTENDED_USE(i) :=
1176 p_tax_info_tbl(i).line_intended_use;
1177 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.USER_DEFINED_FISC_CLASS(i) :=
1178 p_tax_info_tbl(i).user_fiscal_class;
1179 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(i) :=
1180 'N';
1181 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(i) :=
1182 p_tax_info_tbl(i).line_amt;
1183 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_QUANTITY(i) :=
1184 p_tax_info_tbl(i).trx_line_quantity;
1185 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UNIT_PRICE(i) :=
1186 p_tax_info_tbl(i).unit_price;
1187 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(i) :=
1188 'S';
1189 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(i) :=
1190 p_tax_info_tbl(i).product_id;
1191 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_FISC_CLASSIFICATION(i) :=
1192 p_tax_info_tbl(i).prod_fiscal_class;
1193 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ORG_ID(i) :=
1194 p_tax_info_tbl(i).product_org_id;
1195 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UOM_CODE(i) :=
1196 p_tax_info_tbl(i).uom_code;
1197 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_TYPE(i) :=
1198 p_tax_info_tbl(i).product_type;
1199 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_CODE(i) :=
1200 p_tax_info_tbl(i).product_code;
1201 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_CATEGORY(i) :=
1202 p_tax_info_tbl(i).product_category;
1203 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(i) :=
1204 p_tax_info_tbl(i).ship_to_party_id;
1205 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_ID(i) :=
1206 p_tax_info_tbl(i).ship_from_party_id;
1207 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(i) :=
1208 p_tax_info_tbl(i).bill_to_party_id;
1209 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_PARTY_ID(i) :=
1210 p_tax_info_tbl(i).bill_from_party_id;
1211 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_SITE_ID(i) :=
1212 p_tax_info_tbl(i).ship_from_party_site_id;
1213 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_PARTY_SITE_ID(i) :=
1214 p_tax_info_tbl(i).bill_from_party_site_id;
1215 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(i) :=
1216 p_tax_info_tbl(i).ship_to_location_id;
1217 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(i) :=
1218 p_tax_info_tbl(i).bill_to_location_id;
1219 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_ID(i) :=
1220 p_tax_info_tbl(i).ship_third_pty_id;
1221 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(i) :=
1222 p_tax_info_tbl(i).ship_third_pty_site_id;
1223 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ASSESSABLE_VALUE(i) :=
1224 p_tax_info_tbl(i).assessable_value;
1225 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_TAX_CODE_ID(i) :=
1226 p_tax_info_tbl(i).historical_tax_id;
1227 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.USER_UPD_DET_FACTORS_FLAG(i) :=
1228 p_tax_info_tbl(i).user_override;
1229 -- Bug 7526131: populating ship_from_location_id
1230 if (p_tax_info_tbl(i).ship_from_party_site_id is null) then
1231 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(i) := null;
1232 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_LOCATION_ID(i) := null;
1233 Else
1234 SELECT hzps.location_id INTO ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(i)
1235 FROM hz_party_sites hzps
1236 WHERE hzps.party_site_id =p_tax_info_tbl(i).ship_from_party_site_id and rownum =1 ;
1237 SELECT pvs.location_id INTO ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_FROM_LOCATION_ID(i)
1238 from hz_party_sites pvs /* - Passing Location Id as Bill From Location Id */
1239 WHERE pvs.party_site_id =p_tax_info_tbl(i).ship_from_party_site_id and rownum =1 ;
1240
1241
1242 End if;
1243
1244
1245 -- Logging Infra: Statement level
1246 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1247 l_log_msg := 'Inserted record into ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl for trx_line_id=' || p_tax_info_tbl(1).trx_line_id;
1248 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1249 END IF;
1250
1251 i := i + 1;
1252 EXIT WHEN (i > tbl_sz);
1253 END LOOP;
1254
1255 l_progress := 200;
1256
1257 -- populate l_transaction_rec as all NULLs as there are no duplicates
1258 l_transaction_line_rec.internal_organization_id := NULL;
1259 l_transaction_line_rec.application_id := NULL;
1260 l_transaction_line_rec.entity_code := NULL;
1261 l_transaction_line_rec.event_class_code := NULL;
1262 l_transaction_line_rec.event_type_code := NULL;
1263 l_transaction_line_rec.trx_id := NULL;
1264
1265 -- Logging Infra: Statement level
1266 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1267 l_log_msg := 'ZX insert_line_det_factors(+)';
1268 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1269 END IF;
1270
1271 -- this will insert the tax determining factors into the EBTax tables
1272 zx_api_pub.insert_line_det_factors(p_api_version => 1.0,
1273 p_init_msg_list => null,
1274 p_commit => null,
1275 p_validation_level => null,
1276 x_return_status => l_return_status,
1277 x_msg_count => l_msg_count,
1278 x_msg_data => l_msg_data);
1279
1280 l_progress := 300;
1281
1282 -- Logging Infra: Statement level
1283 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1284 l_log_msg := 'ZX insert_line_det_factors returned status=' || l_return_status;
1285 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1286 l_log_msg := 'ZX insert_line_det_factors(-)';
1287 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1288 END IF;
1289
1290 x_msg_count := x_msg_count + l_msg_count;
1291 x_msg_data := x_msg_data || ':' || l_msg_data;
1292 x_return_status := x_return_status || ':' || l_return_status;
1293
1294 -- Logging Infra: Procedure level
1295 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1296 l_log_msg := l_procedure_name||'(-)';
1297 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1298 l_log_msg := '<-------------------->';
1299 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1300 END IF;
1301
1302 COMMIT;
1303
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 -- rollback
1307 ROLLBACK;
1308 -- raise exception for java layer
1309 RAISE_APPLICATION_ERROR(-20000,
1310 'Exception at POR_TAX_PVT.insert_line_det_attr():'
1311 || ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
1312 || ' Progress= ' || l_progress || '; ROLLBACK Complete.');
1313 END insert_line_det_attr;
1314
1315
1316 PROCEDURE copy_tax_attributes (p_tax_copy_tbl IN POR_TAX_COPY_OBJ_TBL_TYPE,
1317 x_return_status OUT NOCOPY VARCHAR2,
1318 x_msg_data OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER) IS
1320
1321 PRAGMA AUTONOMOUS_TRANSACTION;
1322
1323 l_legal_entity_id PLS_INTEGER;
1324 i PLS_INTEGER;
1325 tbl_sz PLS_INTEGER;
1326 l_msg_count NUMBER;
1327 l_return_status VARCHAR2(60);
1328 l_msg_data VARCHAR2(480);
1329 l_progress PLS_INTEGER;
1330
1331 -- Logging Infra
1332 l_procedure_name CONSTANT VARCHAR2(30) := 'copy_tax_attributes';
1333 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1334
1335 BEGIN
1336
1337 l_progress := 0;
1338
1339 -- Logging Infra: Setting up runtime level
1340 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1341
1342 -- Logging Infra: Procedure level
1343 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1344 l_log_msg := '<-------------------->';
1345 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1346 l_log_msg := l_procedure_name||'(+)';
1347 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1348 END IF;
1349
1350 -- get legal entity id
1351 l_legal_entity_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_tax_copy_tbl(1).org_id);
1352
1353 -- Logging Infra: Statement level
1354 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1355 l_log_msg := 'XLE Legal Entity=' || l_legal_entity_id;
1356 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1357 END IF;
1358
1359 l_progress := 50;
1360
1361 -- loop through and insert reqLine data into EBTax global plsql table
1362 i := 1;
1363 tbl_sz := p_tax_copy_tbl.count();
1364
1365 LOOP
1366 -- EBTax initialization procedure
1367 ZX_GLOBAL_STRUCTURES_PKG.INIT_TRX_LINE_DIST_TBL(i);
1368
1369 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(i) :=
1370 p_tax_copy_tbl(i).org_id;
1371 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(i) :=
1372 201;
1373 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(i) :=
1374 'REQUISITION';
1375 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(i) :=
1376 'REQUISITION';
1377 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(i) :=
1378 'REQ_CREATED';
1379 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(i) :=
1380 p_tax_copy_tbl(i).trx_id;
1381 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(i) :=
1382 p_tax_copy_tbl(i).trx_date;
1383 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(i) :=
1384 p_tax_copy_tbl(i).ledger_id;
1385 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(i) :=
1386 l_legal_entity_id;
1387 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(i) :=
1388 'LINE';
1389 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(i) :=
1390 'CREATE';
1391 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(i) :=
1392 p_tax_copy_tbl(i).trx_line_id;
1393 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(i) :=
1394 p_tax_copy_tbl(i).line_amt;
1395 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(i) :=
1396 p_tax_copy_tbl(i).trx_line_date;
1397 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_APPLICATION_ID(i) :=
1398 201;
1399 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_ENTITY_CODE(i) :=
1400 'REQUISITION';
1401 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_EVENT_CLASS_CODE(i) :=
1402 'REQUISITION';
1403 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_TRX_ID(i) :=
1404 p_tax_copy_tbl(i).source_trx_id;
1405 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_LINE_ID(i) :=
1406 p_tax_copy_tbl(i).source_line_id;
1407 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SOURCE_TRX_LEVEL_TYPE(i) :=
1408 'LINE';
1409 ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.line_amt_includes_tax_flag(i) :=
1410 'N';
1411
1412 -- Logging Infra: Procedure level
1413 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1414 l_log_msg := 'Inserted record into ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl';
1415 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1416 l_log_msg := '--> source id=' || p_tax_copy_tbl(i).source_trx_id || ':' || p_tax_copy_tbl(i).source_line_id;
1417 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1418 l_log_msg := '--> dest id=' || p_tax_copy_tbl(i).trx_id || ':' || p_tax_copy_tbl(i).trx_line_id;
1419 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1420 END IF;
1421
1422 i := i + 1;
1423 EXIT WHEN (i > tbl_sz);
1424 END LOOP;
1425
1426 l_progress := 100;
1427
1428 -- this will insert the tax determining factors into the EBTax tables
1429 zx_api_pub.copy_insert_line_det_factors(p_api_version => 1.0,
1430 p_init_msg_list => null,
1431 p_commit => null,
1432 p_validation_level => null,
1433 x_return_status => l_return_status,
1434 x_msg_count => l_msg_count,
1435 x_msg_data => l_msg_data);
1436
1437 l_progress := 200;
1438
1439 x_msg_count := l_msg_count;
1440 x_msg_data := l_msg_data;
1441 x_return_status := l_return_status;
1442
1443 -- Logging Infra: Procedure level
1444 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1445 l_log_msg := l_procedure_name||'(-)';
1446 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
1447 l_log_msg := '<-------------------->';
1448 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1449 END IF;
1450
1451 COMMIT;
1452
1453 EXCEPTION
1454 WHEN OTHERS THEN
1455 -- rollback
1456 ROLLBACK;
1457 -- raise exception for java layer
1458 RAISE_APPLICATION_ERROR(-20000,
1459 'Exception at POR_TAX_PVT.copy_tax_attributes():'
1460 || ' SQLERRM= ' || SQLERRM || ' Error= ' || x_msg_data
1461 || ' Progress= ' || l_progress || '; ROLLBACK Complete.');
1462
1463 END copy_tax_attributes;
1464
1465
1466 END POR_TAX_PVT;