[Home] [Help]
PACKAGE BODY: APPS.ZX_TRL_ALLOCATIONS_PKG
Source
1 PACKAGE BODY ZX_TRL_ALLOCATIONS_PKG AS
2 /* $Header: zxriallocatnpkgb.pls 120.42 2011/08/16 09:46:37 snoothi ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 PROCEDURE Insert_Row
11 (X_Rowid IN OUT NOCOPY VARCHAR2,
12 p_summary_tax_line_id NUMBER,
13 p_internal_organization_id NUMBER,
14 p_application_id NUMBER,
15 p_entity_code VARCHAR2,
16 p_event_class_code VARCHAR2,
17 p_event_type_code VARCHAR2,
18 p_trx_line_number NUMBER,--
19 p_trx_id NUMBER,
20 p_trx_number VARCHAR2,--
21 p_trx_line_id NUMBER,--
22 p_trx_level_type VARCHAR2,
23 p_line_amt NUMBER,--
24 p_trx_line_date DATE,--
25 p_tax_regime_code VARCHAR2,
26 p_tax VARCHAR2,
27 p_tax_jurisdiction_code VARCHAR2,
28 p_tax_status_code VARCHAR2,
29 p_tax_rate_id NUMBER,
30 p_tax_rate_code VARCHAR2,
31 p_tax_rate NUMBER,
32 p_tax_amt NUMBER,
33 p_enabled_record VARCHAR2,
34 p_manually_entered_flag VARCHAR2,
35 p_content_owner_id NUMBER,
36 p_record_type_code VARCHAR2,
37 p_last_manual_entry VARCHAR2,
38 p_trx_line_amt NUMBER,
39 p_tax_amt_included_flag VARCHAR2,
40 p_self_assessed_flag VARCHAR2,
41 p_tax_only_line_flag VARCHAR2,
42 p_created_by NUMBER,
43 p_creation_date DATE,
44 p_last_updated_by NUMBER,
45 p_last_update_date DATE,
46 p_last_update_login NUMBER) IS
47
48 l_tax_line_id NUMBER;
49 l_tax_line_number NUMBER;
50 l_minimum_accountable_unit NUMBER;
51 l_precision NUMBER;
52 l_trx_currency_code VARCHAR2(20);
53 l_trx_line_exist VARCHAR2(1);
54 BEGIN
55
56 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57
58 IF (g_level_procedure >= g_current_runtime_level ) THEN
59 FND_LOG.STRING(g_level_procedure,
60 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row.BEGIN',
61 'Insert_Row (+)');
62 END IF;
63
64 IF (g_level_procedure >= g_current_runtime_level ) THEN
65 FND_LOG.STRING(g_level_procedure,
66 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
67 'Insert into zx_lines for allocation of lines. (+)');
68 END IF;
69
70 IF (g_level_procedure >= g_current_runtime_level ) THEN
71 FND_LOG.STRING(g_level_procedure,
72 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
73 'Is Record enabled: '|| p_enabled_record);
74 FND_LOG.STRING(g_level_procedure,
75 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
76 'Input Parameters :- Summary Tax Line Id: '|| to_char(p_summary_tax_line_id)||
77 ', Application Id: '|| to_char(p_application_id)||
78 ', Entity Code: '|| p_entity_code||
79 ', Event Class Code: '|| p_event_class_code||
80 ', Trx Id: '|| to_char(p_trx_id)||
81 ', Trx Line Id: '|| to_char(p_trx_line_id));
82 END IF;
83
84 IF p_enabled_record ='Y' THEN
85
86 -- Start : Added a prevetive check to make the trx line exists --
87 IF (g_level_procedure >= g_current_runtime_level ) THEN
88 FND_LOG.STRING(g_level_procedure,
89 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
90 'Executing Trx Line Exists Validation - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
91 END IF;
92
93 l_trx_line_exist := NULL;
94 BEGIN
95 SELECT /*+first_rows(1)*/ 'Y'
96 INTO l_trx_line_exist
97 FROM zx_lines_det_factors
98 WHERE application_id = p_application_id
99 AND entity_code = p_entity_code
100 AND event_class_code = p_event_class_code
101 AND trx_id = p_trx_id
102 AND trx_line_id = p_trx_line_id
103 AND trx_level_type = p_trx_level_type
104 AND ROWNUM = 1;
105 EXCEPTION
106 WHEN OTHERS THEN
107 l_trx_line_exist := 'N';
108 END;
109
110 IF l_trx_line_exist = 'N' THEN
111 IF (g_level_procedure >= g_current_runtime_level ) THEN
112 FND_LOG.STRING(g_level_procedure,
113 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
114 'Trx Line Exists Validation : FAILED');
115 END IF;
116 FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_TEXT');
117 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Error : Transaction Line does not exist. System cannot create tax line without transaction line.');
118 APP_EXCEPTION.RAISE_EXCEPTION;
119 ELSE
120 IF (g_level_procedure >= g_current_runtime_level ) THEN
121 FND_LOG.STRING(g_level_procedure,
122 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
123 'Trx Line Exists Validation : PASSED - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
124 END IF;
125 END IF;
126 -- End : Added a prevetive check to make the trx line exists --
127
128 SELECT NVL(max(TAX_LINE_NUMBER),0)+1
129 INTO l_tax_line_number
130 FROM ZX_LINES
131 WHERE APPLICATION_ID = p_application_id
132 AND EVENT_CLASS_CODE = p_event_class_code
133 AND ENTITY_CODE = p_entity_code
134 AND TRX_ID = p_trx_id
135 AND TRX_LINE_ID = p_trx_line_id
136 AND TRX_LEVEL_TYPE = p_trx_level_type;
137
138 SELECT zx_lines_s.nextval
139 INTO l_tax_line_id
140 FROM dual;
141
142 SELECT DISTINCT precision,minimum_accountable_unit,trx_currency_code
143 INTO l_precision, l_minimum_accountable_unit, l_trx_currency_code
144 FROM zx_lines_det_factors zxd, zx_lines_summary zxs
145 WHERE zxd.trx_id = zxs.trx_id
146 AND zxs.summary_tax_line_id = p_summary_tax_line_id;
147
148 ZX_TRL_DETAIL_OVERRIDE_PKG.Insert_row
149 (x_rowid => X_Rowid,
150 p_tax_line_id => l_tax_line_id,
151 p_internal_organization_id => p_internal_organization_id,
152 p_application_id => p_application_id,
153 p_entity_code => p_entity_code,
154 p_event_class_code => p_event_class_code,
155 p_event_type_code => NULL,
156 p_trx_id => p_trx_id,
157 p_trx_line_id => p_trx_line_id,
158 p_trx_level_type => p_trx_level_type,
159 p_trx_line_number => p_trx_line_number,
160 p_doc_event_status => NULL,
161 p_tax_event_class_code => NULL,
162 p_tax_event_type_code => NULL,
163 p_tax_line_number => l_tax_line_number,
164 p_content_owner_id => p_content_owner_id,
165 p_tax_regime_id => NULL,
166 p_tax_regime_code => p_tax_regime_code,
167 p_tax_id => NULL,
168 p_tax => p_tax,
169 p_tax_status_id => NULL,
170 p_tax_status_code => p_tax_status_code,
171 p_tax_rate_id => p_tax_rate_id,
172 p_tax_rate_code => p_tax_rate_code,
173 p_tax_rate => p_tax_rate,
174 p_tax_rate_type => NULL,
175 p_tax_apportionment_line_num => NULL,
176 p_trx_id_level2 => NULL,
177 p_trx_id_level3 => NULL,
178 p_trx_id_level4 => NULL,
179 p_trx_id_level5 => NULL,
180 p_trx_id_level6 => NULL,
181 p_trx_user_key_level1 => NULL,
182 p_trx_user_key_level2 => NULL,
183 p_trx_user_key_level3 => NULL,
184 p_trx_user_key_level4 => NULL,
185 p_trx_user_key_level5 => NULL,
186 p_trx_user_key_level6 => NULL,
187 p_mrc_tax_line_flag => 'N',
188 p_mrc_link_to_tax_line_id => NULL,
189 p_ledger_id => NULL,
190 p_establishment_id => NULL,
191 p_legal_entity_id => NULL,
192 --p_legal_entity_tax_reg_number => NULL,
193 p_hq_estb_reg_number => NULL,
194 p_hq_estb_party_tax_prof_id => NULL,
195 p_currency_conversion_date => NULL,
196 p_currency_conversion_type => NULL,
197 p_currency_conversion_rate => NULL,
198 p_tax_curr_conversion_date => NULL,
199 p_tax_curr_conversion_type => NULL,
200 p_tax_curr_conversion_rate => NULL,
201 p_trx_currency_code => l_trx_currency_code,
202 p_reporting_currency_code => NULL,
203 p_minimum_accountable_unit => l_minimum_accountable_unit,
204 p_precision => l_precision,
205 p_trx_number => p_trx_number,
206 p_trx_date => NULL,
207 p_unit_price => NULL,
208 p_line_amt => p_trx_line_amt,
209 p_trx_line_quantity => NULL,
210 p_tax_base_modifier_rate => NULL,
211 p_ref_doc_application_id => NULL,
212 p_ref_doc_entity_code => NULL,
213 p_ref_doc_event_class_code => NULL,
214 p_ref_doc_trx_id => NULL,
215 p_ref_doc_trx_level_type => NULL,
216 p_ref_doc_line_id => NULL,
217 p_ref_doc_line_quantity => NULL,
218 p_other_doc_line_amt => NULL,
219 p_other_doc_line_tax_amt => NULL,
220 p_other_doc_line_taxable_amt => NULL,
221 p_unrounded_taxable_amt => p_trx_line_amt,
222 p_unrounded_tax_amt => p_tax_amt,
223 p_related_doc_application_id => NULL,
224 p_related_doc_entity_code => NULL,
225 p_related_doc_evt_class_code => NULL,
226 p_related_doc_trx_id => NULL,
227 p_related_doc_trx_level_type => NULL,
228 p_related_doc_number => NULL,
229 p_related_doc_date => NULL,
230 p_applied_from_appl_id => NULL,
231 p_applied_from_evt_clss_code => NULL,
232 p_applied_from_entity_code => NULL,
233 p_applied_from_trx_id => NULL,
234 p_applied_from_trx_level_type => NULL,
235 p_applied_from_line_id => NULL,
236 p_applied_from_trx_number => NULL,
237 p_adjusted_doc_appln_id => NULL,
238 p_adjusted_doc_entity_code => NULL,
239 p_adjusted_doc_evt_clss_code => NULL,
240 p_adjusted_doc_trx_id => NULL,
241 p_adjusted_doc_trx_level_type => NULL,
242 p_adjusted_doc_line_id => NULL,
246 p_applied_to_evt_class_code => NULL,
243 p_adjusted_doc_number => NULL,
244 p_adjusted_doc_date => NULL,
245 p_applied_to_application_id => NULL,
247 p_applied_to_entity_code => NULL,
248 p_applied_to_trx_id => NULL,
249 p_applied_to_trx_level_type => NULL,
250 p_applied_to_line_id => NULL,
251 p_summary_tax_line_id => p_summary_tax_line_id,
252 p_offset_link_to_tax_line_id => NULL,
253 p_offset_flag => 'N',
254 p_process_for_recovery_flag => 'N',
255 p_tax_jurisdiction_id => NULL,
256 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
257 p_place_of_supply => NULL,
258 p_place_of_supply_type_code => NULL,
259 p_place_of_supply_result_id => NULL,
260 p_tax_date_rule_id => NULL,
261 p_tax_date => NULL,
262 p_tax_determine_date => sysdate,
263 p_tax_point_date => NULL,
264 p_trx_line_date => p_trx_line_date,
265 p_tax_type_code => NULL,
266 p_tax_code => NULL,
267 p_tax_registration_id => NULL,
268 p_tax_registration_number => NULL,
269 p_registration_party_type => NULL,
270 p_rounding_level_code => 'HEADER',
271 p_rounding_rule_code => NULL,
272 p_rndg_lvl_party_tax_prof_id => NULL,
273 p_rounding_lvl_party_type => NULL,
274 p_compounding_tax_flag => 'N',
275 p_orig_tax_status_id => NULL,
276 p_orig_tax_status_code => NULL,
277 p_orig_tax_rate_id => NULL,
278 p_orig_tax_rate_code => NULL,
279 p_orig_tax_rate => NULL,
280 p_orig_tax_jurisdiction_id => NULL,
281 p_orig_tax_jurisdiction_code => NULL,
282 p_orig_tax_amt_included_flag => 'N',
283 p_orig_self_assessed_flag => 'N',
284 p_tax_currency_code => NULL,
285 p_tax_amt => p_tax_amt,
286 p_tax_amt_tax_curr => NULL,
287 p_tax_amt_funcl_curr => NULL,
288 p_taxable_amt => NULL,
289 p_taxable_amt_tax_curr => NULL,
290 p_taxable_amt_funcl_curr => NULL,
291 p_orig_taxable_amt => NULL,
292 p_orig_taxable_amt_tax_curr => NULL,
293 p_cal_tax_amt => NULL,
294 p_cal_tax_amt_tax_curr => NULL,
295 p_cal_tax_amt_funcl_curr => NULL,
296 p_orig_tax_amt => NULL,
297 p_orig_tax_amt_tax_curr => NULL,
298 p_rec_tax_amt => NULL,
299 p_rec_tax_amt_tax_curr => NULL,
300 p_rec_tax_amt_funcl_curr => NULL,
301 p_nrec_tax_amt => NULL,
302 p_nrec_tax_amt_tax_curr => NULL,
303 p_nrec_tax_amt_funcl_curr => NULL,
304 p_tax_exemption_id => NULL,
305 p_tax_rate_before_exemption => NULL,
306 p_tax_rate_name_before_exempt => NULL,
307 p_exempt_rate_modifier => NULL,
308 p_exempt_certificate_number => NULL,
309 p_exempt_reason => NULL,
310 p_exempt_reason_code => NULL,
311 p_tax_exception_id => NULL,
312 p_tax_rate_before_exception => NULL,
313 p_tax_rate_name_before_except => NULL,
314 p_exception_rate => NULL,
315 p_tax_apportionment_flag => 'N',
316 p_historical_flag => 'N',
317 p_taxable_basis_formula => NULL,
318 p_tax_calculation_formula => NULL,
319 p_cancel_flag => 'N',
320 p_purge_flag => 'N',
321 p_delete_flag => 'N',
322 p_tax_amt_included_flag => p_tax_amt_included_flag,
323 p_self_assessed_flag => p_self_assessed_flag,
324 p_overridden_flag => 'N',
325 p_manually_entered_flag => p_manually_entered_flag,
326 p_reporting_only_flag => 'N',
327 p_freeze_until_overriddn_flg => 'N',
328 p_copied_from_other_doc_flag => 'N',
329 p_recalc_required_flag => 'Y',
330 p_settlement_flag => 'N',
331 p_item_dist_changed_flag => 'N',
332 p_assoc_children_frozen_flg => 'N',
333 p_tax_only_line_flag => p_tax_only_line_flag,
334 p_compounding_dep_tax_flag => 'N',
335 p_compounding_tax_miss_flag => 'N',
336 p_sync_with_prvdr_flag => 'N',
337 p_last_manual_entry => p_last_manual_entry,
338 p_tax_provider_id => NULL,
339 p_record_type_code => p_record_type_code,
340 p_reporting_period_id => NULL,
341 p_legal_justification_text1 => NULL,
342 p_legal_justification_text2 => NULL,
343 p_legal_justification_text3 => NULL,
344 p_legal_message_appl_2 => NULL,
345 p_legal_message_status => NULL,
346 p_legal_message_rate => NULL,
347 p_legal_message_basis => NULL,
348 p_legal_message_calc => NULL,
349 p_legal_message_threshold => NULL,
350 p_legal_message_pos => NULL,
351 p_legal_message_trn => NULL,
352 p_legal_message_exmpt => NULL,
356 p_direct_rate_result_id => NULL,
353 p_legal_message_excpt => NULL,
354 p_tax_regime_template_id => NULL,
355 p_tax_applicability_result_id => NULL,
357 p_status_result_id => NULL,
358 p_rate_result_id => NULL,
359 p_basis_result_id => NULL,
360 p_thresh_result_id => NULL,
361 p_calc_result_id => NULL,
362 p_tax_reg_num_det_result_id => NULL,
363 p_eval_exmpt_result_id => NULL,
364 p_eval_excpt_result_id => NULL,
365 p_enforced_from_nat_acct_flg => 'N',
366 p_tax_hold_code => NULL,
367 p_tax_hold_released_code => NULL,
368 p_prd_total_tax_amt => NULL,
369 p_prd_total_tax_amt_tax_curr => NULL,
370 p_prd_total_tax_amt_funcl_curr => NULL,
371 p_trx_line_index => NULL,
372 p_offset_tax_rate_code => NULL,
373 p_proration_code => NULL,
374 p_other_doc_source => NULL,
375 p_internal_org_location_id => NULL,
376 p_line_assessable_value => NULL,
377 p_ctrl_total_line_tx_amt => NULL,
378 p_applied_to_trx_number => NULL,
379 p_attribute_category => NULL,
380 p_attribute1 => NULL,
381 p_attribute2 => NULL,
382 p_attribute3 => NULL,
383 p_attribute4 => NULL,
384 p_attribute5 => NULL,
385 p_attribute6 => NULL,
386 p_attribute7 => NULL,
387 p_attribute8 => NULL,
388 p_attribute9 => NULL,
389 p_attribute10 => NULL,
390 p_attribute11 => NULL,
391 p_attribute12 => NULL,
392 p_attribute13 => NULL,
393 p_attribute14 => NULL,
394 p_attribute15 => NULL,
395 p_global_attribute_category => NULL,
396 p_global_attribute1 => NULL,
397 p_global_attribute2 => NULL,
398 p_global_attribute3 => NULL,
399 p_global_attribute4 => NULL,
400 p_global_attribute5 => NULL,
401 p_global_attribute6 => NULL,
402 p_global_attribute7 => NULL,
403 p_global_attribute8 => NULL,
404 p_global_attribute9 => NULL,
405 p_global_attribute10 => NULL,
406 p_global_attribute11 => NULL,
407 p_global_attribute12 => NULL,
408 p_global_attribute13 => NULL,
409 p_global_attribute14 => NULL,
410 p_global_attribute15 => NULL,
411 p_numeric1 => NULL,
412 p_numeric2 => NULL,
413 p_numeric3 => NULL,
414 p_numeric4 => NULL,
415 p_numeric5 => NULL,
416 p_numeric6 => NULL,
417 p_numeric7 => NULL,
418 p_numeric8 => NULL,
419 p_numeric9 => NULL,
420 p_numeric10 => NULL,
421 p_char1 => NULL,
422 p_char2 => NULL,
423 p_char3 => NULL,
424 p_char4 => NULL,
425 p_char5 => NULL,
426 p_char6 => NULL,
427 p_char7 => NULL,
428 p_char8 => NULL,
429 p_char9 => NULL,
430 p_char10 => NULL,
431 p_date1 => NULL,
432 p_date2 => NULL,
433 p_date3 => NULL,
434 p_date4 => NULL,
435 p_date5 => NULL,
436 p_date6 => NULL,
437 p_date7 => NULL,
438 p_date8 => NULL,
439 p_date9 => NULL,
440 p_date10 => NULL,
441 p_interface_entity_code => NULL,
442 p_interface_tax_line_id => NULL,
443 p_taxing_juris_geography_id => NULL,
444 p_adjusted_doc_tax_line_id => NULL,
445 p_object_version_number => 1,
446 p_created_by => fnd_global.user_id,
447 p_creation_date => sysdate,
448 p_last_updated_by => fnd_global.user_id,
449 p_last_update_date => sysdate,
450 p_last_update_login => fnd_global.login_id);
451
452 END IF;
453
454 IF (g_level_procedure >= g_current_runtime_level ) THEN
455 FND_LOG.STRING(g_level_procedure,
456 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
457 'Insert into zx_lines for allocation of lines. (-)');
458 END IF;
459
460 IF (g_level_procedure >= g_current_runtime_level ) THEN
461 FND_LOG.STRING(g_level_procedure,
462 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row.END',
463 'Insert_Row (-)');
464 END IF;
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 NULL;
468
469 END Insert_Row;
470
474 | Description: |
471 /*-------------------------------------------------------------+
472 | Procedure : Update_tax_amt |
473 | |
475 | Procedure to prorate the summary tax line amount across |
476 | across the detail tax lines as per their corresponding |
477 | line amounts. |
478 | |
479 | Modified the procedure parameters for Bug#9664737 |
480 +-------------------------------------------------------------*/
481 PROCEDURE Update_tax_amt
482 (p_summary_tax_line_id NUMBER,
483 p_application_id NUMBER,
484 p_entity_code VARCHAR2,
485 p_event_class_code VARCHAR2,
486 p_trx_id NUMBER,
487 p_tax_amt NUMBER DEFAULT NULL) IS
488
489 --l_total_tax_amt NUMBER;
490 --l_detail_tax_amt NUMBER;
491 --l_tax_line_id NUMBER;
492 l_summary_tax_amt NUMBER;
493 l_total_trx_line_amt NUMBER;
494 l_unrounded_tax_amt NUMBER;
495
496 BEGIN
497
498 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
499
500 IF (g_level_procedure >= g_current_runtime_level ) THEN
501 FND_LOG.STRING(g_level_procedure,
502 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt.BEGIN',
503 'Update_tax_amt (+)');
504 END IF;
505
506 IF (g_level_procedure >= g_current_runtime_level ) THEN
507 FND_LOG.STRING(g_level_procedure,
508 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
509 'Input Parameters :- Summary Tax Line Id: '|| to_char(p_summary_tax_line_id)||
510 ', Application Id: '|| to_char(p_application_id)||
511 ', Entity Code: '|| p_entity_code||
512 ', Event Class Code: '|| p_event_class_code||
513 ', Trx Id: '|| to_char(p_trx_id)||
514 ', Tax Amount: '|| to_char(p_tax_amt));
515 END IF;
516
517 --l_total_tax_amt :=0;
518 --l_detail_tax_amt :=0;
519 l_summary_tax_amt :=0;
520 l_total_trx_line_amt :=0;
521 l_unrounded_tax_amt :=0;
522
523 IF p_tax_amt IS NULL THEN
524 SELECT TAX_AMT
525 INTO l_summary_tax_amt
526 FROM ZX_LINES_SUMMARY
527 WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id;
528 ELSE
529 l_summary_tax_amt := p_tax_amt;
530 END IF;
531
532 IF (g_level_statement >= g_current_runtime_level ) THEN
533 FND_LOG.STRING(g_level_statement,
534 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
535 'Summary_Tax_Amount : '||to_char(l_summary_tax_amt));
536 END IF;
537
538 SELECT SUM(LINE_AMT)
539 INTO l_total_trx_line_amt
540 FROM ZX_LINES
541 WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
542 AND APPLICATION_ID = p_application_id
543 AND ENTITY_CODE = p_entity_code
544 AND EVENT_CLASS_CODE = p_event_class_code
545 AND TRX_ID = p_trx_id
546 AND NVL(CANCEL_FLAG,'N') <> 'Y';
547
548 IF (g_level_statement >= g_current_runtime_level ) THEN
549 FND_LOG.STRING(g_level_statement,
550 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
551 'Total_Trx_Line_Amount : '||to_char(l_total_trx_line_amt));
552 END IF;
553
554 IF l_total_trx_line_amt <> 0 THEN
555 UPDATE ZX_LINES
556 SET unrounded_tax_amt = ((line_amt * l_summary_tax_amt) / l_total_trx_line_amt)
557 WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
558 AND APPLICATION_ID = p_application_id
559 AND ENTITY_CODE = p_entity_code
560 AND EVENT_CLASS_CODE = p_event_class_code
561 AND TRX_ID = p_trx_id
562 AND NVL(CANCEL_FLAG,'N') <> 'Y';
563 END IF;
564
565 SELECT SUM(unrounded_tax_amt)
566 INTO l_unrounded_tax_amt
567 FROM ZX_LINES
568 WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
569 AND APPLICATION_ID = p_application_id
570 AND ENTITY_CODE = p_entity_code
571 AND EVENT_CLASS_CODE = p_event_class_code
572 AND TRX_ID = p_trx_id
573 AND NVL(CANCEL_FLAG,'N') <> 'Y';
574
575 IF (g_level_statement >= g_current_runtime_level ) THEN
576 FND_LOG.STRING(g_level_statement,
577 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
578 'Total_Unrounded_Tax_Amt '||to_char(l_unrounded_tax_amt));
579 END IF;
580
581 IF (g_level_procedure >= g_current_runtime_level ) THEN
582 FND_LOG.STRING(g_level_procedure,
583 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt.END',
584 'Update_tax_amt (-)');
585 END IF;
586 END Update_tax_amt;
587
588 /*-------------------------------------------------------------+
589 | Procedure : Populate_Allocation |
590 | |
591 | Description: |
592 | Procedure to populate the allocations structure which is |
593 | used to populate the Allocation Block on Summary Tax Form |
594 | |
595 | Modified the procedure parameters for Bug#9664737 |
596 +-------------------------------------------------------------*/
600 p_application_id IN NUMBER,
597 PROCEDURE Populate_Allocation
598 (p_statement IN VARCHAR2,
599 p_summary_tax_line_id IN NUMBER,
601 p_entity_code IN VARCHAR2,
602 p_event_class_code IN VARCHAR2,
603 p_tax_regime_code IN VARCHAR2,
604 p_tax IN VARCHAR2,
605 p_trx_record OUT NOCOPY trx_record_tbl_type)
606 IS
607
608 TYPE EmpCurTyp IS REF CURSOR;
609 ALLOC EmpCurTyp;
610 l_alloc_to_summ VARCHAR2(1);
611 l_alloc_to_r2r VARCHAR2(1);
612
613 l_trx_record trx_record_tbl_type;
614 i NUMBER;
615 j NUMBER;
616
617 BEGIN
618
619 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
620
621 i := 0;
622 j := 0;
623
624 IF (g_level_procedure >= g_current_runtime_level ) THEN
625 FND_LOG.STRING(g_level_procedure,
626 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_Allocation.BEGIN',
627 'Populate_Allocation (+)');
628 END IF;
629
630 IF (g_level_statement >= g_current_runtime_level ) THEN
631 FND_LOG.STRING(g_level_statement,
632 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_Allocation',
633 'Input Parameters :- Summary_Tax_Line_Id:'||p_summary_tax_line_id||
634 ', Application_id:'||p_application_id||
635 ', Entity_Code:'||p_entity_code||
636 ', Event_Class_Code:'||p_event_class_code||
637 ', Tax_Regime_Code:'||p_tax_regime_code||
638 ', Tax:'||p_tax);
639 END IF;
640
641 IF (g_level_statement >= g_current_runtime_level ) THEN
642 FND_LOG.STRING(g_level_statement,
643 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_Allocation',
644 'Cursor Statement : '||p_statement);
645 END IF;
646
647 OPEN ALLOC FOR p_statement;
648 LOOP
649 l_alloc_to_r2r := 'N';
650 l_alloc_to_summ := 'N';
651 i := i + 1;
652 FETCH ALLOC INTO l_trx_record.p_trx_id(i),
653 l_trx_record.p_trx_line_id(i),
654 l_trx_record.p_trx_level_type(i),
655 l_trx_record.p_trx_number(i),
656 l_trx_record.p_trx_line_number(i),
657 l_trx_record.p_trx_line_description(i),
658 l_trx_record.p_line_amt(i),
659 l_trx_record.p_trx_line_date(i);
660
661 -- Start : Added code to find if summary tax line already
662 -- allocated to the trx line for Bug#9664737
663 BEGIN
664 SELECT 'Y' INTO l_alloc_to_summ
665 FROM zx_lines
666 WHERE application_id = p_application_id
667 AND entity_code = p_entity_code
668 AND event_class_code = p_event_class_code
669 AND trx_id = l_trx_record.p_trx_id(i)
670 AND trx_line_id = l_trx_record.p_trx_line_id(i)
671 AND trx_level_type = l_trx_record.p_trx_level_type(i)
672 AND summary_tax_line_id = p_summary_tax_line_id
673 AND ROWNUM = 1;
674 EXCEPTION
675 WHEN OTHERS THEN
676 l_alloc_to_summ := 'N';
677 END;
678
679 l_trx_record.p_alloc_exist_flag(i) := NVL(l_alloc_to_summ,'N');
680
681 IF NVL(l_alloc_to_summ,'N') = 'N' THEN
682 BEGIN
683 SELECT 'Y' INTO l_alloc_to_r2r
684 FROM zx_lines
685 WHERE application_id = p_application_id
686 AND entity_code = p_entity_code
687 AND event_class_code = p_event_class_code
688 AND trx_id = l_trx_record.p_trx_id(i)
689 AND trx_line_id = l_trx_record.p_trx_line_id(i)
690 AND trx_level_type = l_trx_record.p_trx_level_type(i)
691 AND tax_regime_code = p_tax_regime_code
692 AND tax = p_tax
693 AND ROWNUM = 1;
694 EXCEPTION
695 WHEN OTHERS THEN
696 l_alloc_to_r2r := 'N';
697 END;
698
699 ELSE
700 l_alloc_to_r2r := 'N';
701 END IF;
702
703 IF (g_level_statement >= g_current_runtime_level ) THEN
704 FND_LOG.STRING(g_level_statement,
705 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_Allocation',
706 'Trx_Id:'||l_trx_record.p_trx_id(i)||
707 ', Trx_Line_Id:'||l_trx_record.p_trx_line_id(i)||
708 ', Alloc_Exist_Flag:'||l_trx_record.p_alloc_exist_flag(i)||
709 ', Allocated_to_R2R_Flag:'||l_alloc_to_r2r);
710 END IF;
711
712 IF NVL(l_alloc_to_r2r,'N') = 'N' THEN
713 j := j + 1;
714 p_trx_record.p_trx_id(j) := l_trx_record.p_trx_id(i);
715 p_trx_record.p_trx_line_id(j) := l_trx_record.p_trx_line_id(i);
716 p_trx_record.p_trx_level_type(j) := l_trx_record.p_trx_level_type(i);
717 p_trx_record.p_trx_number(j) := l_trx_record.p_trx_number(i);
718 p_trx_record.p_trx_line_number(j) := l_trx_record.p_trx_line_number(i);
719 p_trx_record.p_trx_line_description(j) := l_trx_record.p_trx_line_description(i);
720 p_trx_record.p_line_amt(j) := l_trx_record.p_line_amt(i);
721 p_trx_record.p_trx_line_date(j) := l_trx_record.p_trx_line_date(i);
722 p_trx_record.p_alloc_exist_flag(j) := l_trx_record.p_alloc_exist_flag(i);
723 END IF;
724
725 -- End : Added code for Bug#9664737
726 EXIT WHEN ALLOC%NOTFOUND;
727 END LOOP;
728
729 CLOSE ALLOC;
730
734 'Populate_Allocation (-)');
731 IF (g_level_procedure >= g_current_runtime_level ) THEN
732 FND_LOG.STRING(g_level_procedure,
733 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_Allocation.END',
735 END IF;
736
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 NULL;
740
741 END Populate_Allocation;
742
743 /*-------------------------------------------------------------+
744 | Procedure : Insert_All_Allocation |
745 | |
746 | Description: |
747 | Procedure to create detail tax lines for the item lines |
748 | marked for allocation |
749 | |
750 | Modified the procedure parameters for Bug#9664737 |
751 +-------------------------------------------------------------*/
752 PROCEDURE Insert_All_Allocation
753 (X_Rowid IN OUT NOCOPY VARCHAR2,
754 p_summary_tax_line_id NUMBER,
755 p_internal_organization_id NUMBER,
756 p_application_id NUMBER,
757 p_entity_code VARCHAR2,
758 p_event_class_code VARCHAR2,
759 p_tax_regime_code VARCHAR2,
760 p_tax VARCHAR2,
761 p_tax_jurisdiction_code VARCHAR2,
762 p_tax_status_code VARCHAR2,
763 p_tax_rate_id NUMBER,
764 p_tax_rate_code VARCHAR2,
765 p_tax_rate NUMBER,
766 p_tax_amt NUMBER,
767 p_enabled_record VARCHAR2,
768 p_summ_tax_only VARCHAR2,
769 p_statement VARCHAR2,
770 p_manually_entered_flag VARCHAR2,
771 p_content_owner_id NUMBER,
772 p_record_type_code VARCHAR2,
773 p_last_manual_entry VARCHAR2,
774 p_tax_amt_included_flag VARCHAR2,
775 p_self_assessed_flag VARCHAR2,
776 p_tax_only_line_flag VARCHAR2,
777 p_created_by NUMBER,
778 p_creation_date DATE,
779 p_last_updated_by NUMBER,
780 p_last_update_date DATE,
781 p_last_update_login NUMBER,
782 p_allocate_flag IN VARCHAR2 DEFAULT 'N'
783 ) IS
784
785 l_tax_line_id NUMBER;
786 l_trx_line_id NUMBER;
787 l_tax_line_number NUMBER;
788 l_tax_id NUMBER;
789 v_trx_number zx_lines.trx_number%TYPE;
790 v_trx_id zx_lines.trx_id%TYPE;
791 v_trx_line_id zx_lines.trx_line_id%TYPE;
792 v_trx_level_type zx_lines.trx_level_type%TYPE;
793 v_trx_line_date zx_lines.trx_line_date%TYPE;
794 v_trx_line_number zx_lines.trx_line_number%TYPE;
795 v_trx_line_description zx_transaction_lines.trx_line_description%TYPE;
796 v_line_amt zx_lines.line_amt%TYPE := 0;
797 l_Place_Of_Supply_Type_Code zx_lines.Place_Of_Supply_Type_Code%TYPE; --Bug 12688194
798 l_taxable_amt NUMBER;
799 l_event_id NUMBER;
800 l_regime_id NUMBER;
801 l_status_id NUMBER;
802 l_tax_determine_date DATE;
803 l_trx_date DATE;
804 l_related_doc_date DATE;
805 l_adjusted_doc_date DATE;
806 l_trx_line_exist VARCHAR2(1);
807
808 TYPE EmpCurTyp IS REF CURSOR;
809 ALLOC EmpCurTyp;
810 l_key VARCHAR2(100);
811 l_insert BOOLEAN ;
812
813 -- Added this cursor to fetch the data from
814 -- ZX_SCO_TAXES_B_V in place of ZX_SCO_TAXES
815 CURSOR get_tax_id (c_tax_regime_code VARCHAR2,
816 c_tax VARCHAR2)
817 IS
818 SELECT tax_id,Def_Place_Of_Supply_Type_Code --Bug 12688194
819 FROM zx_sco_taxes_b_v
820 WHERE tax = c_tax
821 AND tax_regime_code = c_tax_regime_code
822 ORDER BY subscription_level_code;
823
824 -- Added this cursor to fetch the data from
825 -- ZX_SCO_STATUS_B_V in place of ZX_SCO_STATUS
826 CURSOR get_tax_status_id (c_tax_regime_code VARCHAR2,
827 c_tax VARCHAR2,
828 c_tax_status_code VARCHAR2)
829 IS
830 SELECT tax_status_id
831 FROM zx_sco_status_b_v
832 WHERE tax_status_code = c_tax_status_code
833 AND tax = c_tax
834 AND tax_regime_code = c_tax_regime_code
835 ORDER BY subscription_level_code;
836
837 -- Added for reallocation feature --
838 l_ledger_id ZX_LINES_SUMMARY.ledger_id%TYPE;
839 l_legal_entity_id ZX_LINES_SUMMARY.legal_entity_id%TYPE;
840 l_establishment_id ZX_LINES_SUMMARY.establishment_id%TYPE;
841 l_currency_conversion_date ZX_LINES_SUMMARY.currency_conversion_date%TYPE;
842 l_currency_conversion_type ZX_LINES_SUMMARY.currency_conversion_type%TYPE;
843 l_currency_conversion_rate ZX_LINES_SUMMARY.currency_conversion_rate%TYPE;
844 l_taxable_basis_formula ZX_LINES_SUMMARY.taxable_basis_formula%TYPE;
845 l_tax_calculation_formula ZX_LINES_SUMMARY.tax_calculation_formula%TYPE;
846 l_tax_exemption_id ZX_LINES_SUMMARY.tax_exemption_id%TYPE;
847 l_exempt_certificate_number ZX_LINES_SUMMARY.exempt_certificate_number%TYPE;
851
848 l_exempt_reason_code ZX_LINES_SUMMARY.exempt_reason_code%TYPE;
849 l_tax_exception_id ZX_LINES_SUMMARY.tax_exception_id%TYPE;
850 l_tax_provider_id ZX_LINES_SUMMARY.tax_provider_id%TYPE;
852 -- Added this cursor for reallocation feature
853 -- to fetch the values of some summarization columns
854 CURSOR get_summary_columns (c_summary_tax_line_id NUMBER)
855 IS
856 SELECT ledger_id,
857 legal_entity_id,
858 establishment_id,
859 currency_conversion_date,
860 currency_conversion_type,
861 currency_conversion_rate,
862 taxable_basis_formula,
863 tax_calculation_formula,
864 tax_exemption_id,
865 exempt_certificate_number,
866 exempt_reason_code,
867 tax_exception_id,
868 tax_provider_id
869 FROM zx_lines_summary
870 WHERE summary_tax_line_id = c_summary_tax_line_id;
871
872 BEGIN
873
874 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
875
876 IF (g_level_procedure >= g_current_runtime_level ) THEN
877 FND_LOG.STRING(g_level_procedure,
878 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.BEGIN',
879 'Insert_All_Allocation (+)');
880 END IF;
881
882 IF (g_level_procedure >= g_current_runtime_level ) THEN
883 FND_LOG.STRING(g_level_procedure,
884 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
885 'Input Parameters : Summary_Tax_line_id='||p_summary_tax_line_id||
886 ', Application_id='||p_application_id||
887 ', Entity_code='||p_entity_code||
888 ', Event_class_code='||p_event_class_code||
889 ', Internal_org_id='||p_internal_organization_id||
890 ', Tax_regime_code='||p_tax_regime_code||
891 ', Tax='||p_tax||
892 ', Tax_jurisdiction_code='||p_tax_jurisdiction_code||
893 ', Tax_status_code='||p_tax_status_code||
894 ', Tax_rate_id='||p_tax_rate_id||
895 ', Tax_rate_code='||p_tax_rate_code||
896 ', Tax_rate='||p_tax_rate||
897 ', Tax_amt='||p_tax_amt||
898 ', Content_owner_id='||p_content_owner_id||
899 ', Manually_entered_flag='||p_manually_entered_flag||
900 ', Record_type_code='||p_record_type_code||
901 ', Last_manual_entry='||p_last_manual_entry||
902 ', Tax_amt_included_flag='||p_tax_amt_included_flag||
903 ', Self_assessed_flag='||p_self_assessed_flag||
904 ', Tax_only_line_flag='||p_tax_only_line_flag);
905 END IF;
906
907 l_insert := TRUE;
908
909 OPEN ALLOC FOR p_statement;
910 LOOP
911
912 FETCH ALLOC INTO v_trx_id,
913 v_trx_line_id,
914 v_trx_level_type,
915 v_trx_number,
916 v_trx_line_number,
917 v_trx_line_description,
918 v_line_amt,
919 v_trx_line_date;
920
921 EXIT WHEN ALLOC%NOTFOUND;
922
923 v_trx_level_type := NVL(v_trx_level_type, 'LINE');
924
925
926 IF p_allocate_flag = 'Y' THEN
927 l_key := to_char(p_summary_tax_line_id)||to_char(v_trx_id)||to_char(v_trx_line_id);
928 IF (g_level_statement >= g_current_runtime_level ) THEN
929 FND_LOG.STRING(g_level_statement,
930 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
931 'key : ' || l_key) ;
932 END IF;
933 IF g_trx_allocate_tbl.exists(l_key) THEN
934 IF (g_level_statement >= g_current_runtime_level ) THEN
935 FND_LOG.STRING(g_level_statement,
936 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
937 'Allocate TRUE') ;
938 END IF;
939 l_insert := TRUE;
940 ELSE
941 l_insert := FALSE;
942 END IF;
943 END IF;
944
945 IF l_insert THEN
946 -- Start : Added a prevetive check to make the trx line exists --
947 IF (g_level_procedure >= g_current_runtime_level ) THEN
948 FND_LOG.STRING(g_level_procedure,
949 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
950 'Executing Trx Line Exists Validation - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
951 END IF;
952 l_trx_line_exist := NULL;
953 BEGIN
954 SELECT /*+first_rows(1)*/ 'Y'
955 INTO l_trx_line_exist
956 FROM zx_lines_det_factors
957 WHERE application_id = p_application_id
958 AND entity_code = p_entity_code
959 AND event_class_code = p_event_class_code
960 AND trx_id = v_trx_id
961 AND trx_line_id = v_trx_line_id
962 AND trx_level_type = v_trx_level_type
963 AND ROWNUM = 1;
964 EXCEPTION
965 WHEN OTHERS THEN
966 l_trx_line_exist := 'N';
967 END;
968
969 IF l_trx_line_exist = 'N' THEN
970 IF (g_level_procedure >= g_current_runtime_level ) THEN
971 FND_LOG.STRING(g_level_procedure,
972 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
973 'Trx Line Exists Validation : FAILED');
974 END IF;
975 FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_TEXT');
979 IF (g_level_procedure >= g_current_runtime_level ) THEN
976 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Error : Transaction Line does not exist. System cannot create tax line without transaction line.');
977 APP_EXCEPTION.RAISE_EXCEPTION;
978 ELSE
980 FND_LOG.STRING(g_level_procedure,
981 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
982 'Trx Line Exists Validation : PASSED - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
983 END IF;
984 END IF;
985 -- End : Added a prevetive check to make the trx line exists --
986
987 SELECT NVL(MAX(TAX_LINE_NUMBER),0) + 1
988 INTO l_tax_line_number
989 FROM ZX_LINES
990 WHERE APPLICATION_ID = p_application_id
991 AND EVENT_CLASS_CODE = p_event_class_code
992 AND ENTITY_CODE = p_entity_code
993 AND TRX_ID = v_trx_id
994 AND TRX_LINE_ID = v_trx_line_id
995 AND TRX_LEVEL_TYPE = v_trx_level_type;
996
997 IF (g_level_procedure >= g_current_runtime_level ) THEN
998 FND_LOG.STRING(g_level_procedure,
999 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1000 'Tax_Line_Number : ' || to_char(l_tax_line_number)) ;
1001 END IF;
1002
1003 SELECT tax_regime_id
1004 INTO l_regime_id
1005 FROM zx_regimes_b
1006 WHERE TAX_REGIME_CODE = p_tax_regime_code;
1007
1008 IF (g_level_procedure >= g_current_runtime_level ) THEN
1009 FND_LOG.STRING(g_level_procedure,
1010 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1011 'Tax_Regime_Id : ' || to_char(l_regime_id)) ;
1012 END IF;
1013
1014 IF l_regime_id IS NULL THEN
1015 RAISE NO_DATA_FOUND;
1016 END IF;
1017
1018 OPEN get_tax_id (p_tax_regime_code,p_tax);
1019 FETCH get_tax_id INTO l_tax_id,l_Place_Of_Supply_Type_Code; --Bug 12688194;
1020 CLOSE get_tax_id;
1021
1022 IF (g_level_procedure >= g_current_runtime_level ) THEN
1023 FND_LOG.STRING(g_level_procedure,
1024 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1025 'Tax_Id : ' || to_char(l_tax_id)) ;
1026 END IF;
1027
1028 IF l_tax_id IS NULL THEN
1029 RAISE NO_DATA_FOUND;
1030 END IF;
1031
1032 OPEN get_tax_status_id (p_tax_regime_code,p_tax,p_tax_status_code);
1033 FETCH get_tax_status_id INTO l_status_id;
1034 CLOSE get_tax_status_id;
1035
1036 IF (g_level_procedure >= g_current_runtime_level ) THEN
1037 FND_LOG.STRING(g_level_procedure,
1038 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1039 'Tax_Status_Id : ' || to_char(l_status_id)) ;
1040 END IF;
1041
1042 IF l_status_id IS NULL THEN
1043 RAISE NO_DATA_FOUND;
1044 END IF;
1045
1046 SELECT zx_lines_s.nextval
1047 INTO l_tax_line_id
1048 FROM dual;
1049
1050 IF (g_level_procedure >= g_current_runtime_level ) THEN
1051 FND_LOG.STRING(g_level_procedure,
1052 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1053 'Line_Amt : ' || to_char(v_line_amt)) ;
1054 END IF;
1055
1056 BEGIN
1057 SELECT DISTINCT nvl(related_doc_date,nvl(provnl_tax_determination_date
1058 ,nvl(adjusted_doc_date,trx_date))) ,
1059 trx_date,
1060 related_doc_date ,
1061 adjusted_doc_date
1062 INTO l_tax_determine_date, l_trx_date ,l_related_doc_date, l_adjusted_doc_date
1063 FROM zx_lines_det_factors
1064 WHERE trx_id = v_trx_id
1065 AND application_id = p_application_id
1066 AND entity_code = p_entity_code
1067 AND event_class_code = p_event_class_code
1068 AND rownum = 1;
1069 EXCEPTION
1070 WHEN OTHERS THEN NULL;
1071 IF (g_level_procedure >= g_current_runtime_level ) THEN
1072 FND_LOG.STRING(g_level_procedure,
1073 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1074 'exception : ' || sqlerrm);
1075 END IF;
1076 END ;
1077
1078 -- Fetch summarization columns from ZX_LINES_SUMMARY for re-allocation
1079 OPEN get_summary_columns(p_summary_tax_line_id);
1080 FETCH get_summary_columns INTO
1081 l_ledger_id,
1082 l_legal_entity_id,
1083 l_establishment_id,
1084 l_currency_conversion_date,
1085 l_currency_conversion_type,
1086 l_currency_conversion_rate,
1087 l_taxable_basis_formula,
1088 l_tax_calculation_formula,
1089 l_tax_exemption_id,
1090 l_exempt_certificate_number,
1091 l_exempt_reason_code,
1092 l_tax_exception_id,
1093 l_tax_provider_id;
1094 CLOSE get_summary_columns;
1095
1096 IF (g_level_statement >= g_current_runtime_level ) THEN
1097 FND_LOG.STRING(g_level_statement,
1098 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1099 'Summarization Columns Values: '||
1100 'Ledger_id='||l_ledger_id||
1101 ', Legal_entity_id='||l_legal_entity_id||
1102 ', Establishment_id='||l_establishment_id||
1103 ', Currency_conversion_date='||TO_CHAR(l_currency_conversion_date,'DD-MON-YYYY')||
1107 ', Tax_calculation_formula='||l_tax_calculation_formula||
1104 ', Currency_conversion_type='||l_currency_conversion_type||
1105 ', Currency_conversion_rate='||l_currency_conversion_rate||
1106 ', Taxable_basis_formula='||l_taxable_basis_formula||
1108 ', Tax_exemption_id='||l_tax_exemption_id||
1109 ', Exempt_certificate_number='||l_exempt_certificate_number||
1110 ', Exempt_reason_code='||l_exempt_reason_code||
1111 ', Tax_exception_id='||l_tax_exception_id||
1112 ', Tax_provider_id='||l_tax_provider_id);
1113 END IF;
1114
1115 ZX_TRL_DETAIL_OVERRIDE_PKG.Insert_row
1116 (x_rowid => X_Rowid,
1117 p_tax_line_id => l_tax_line_id,
1118 p_internal_organization_id => p_internal_organization_id,
1119 p_application_id => p_application_id,
1120 p_entity_code => p_entity_code,
1121 p_event_class_code => p_event_class_code,
1122 p_event_type_code => NULL,
1123 p_trx_id => v_trx_id,
1124 p_trx_line_id => v_trx_line_id,
1125 p_trx_level_type => v_trx_level_type,
1126 p_trx_line_number => v_trx_line_number,
1127 p_doc_event_status => NULL,
1128 p_tax_event_class_code => NULL,
1129 p_tax_event_type_code => NULL,
1130 p_tax_line_number => l_tax_line_number,
1131 p_content_owner_id => p_content_owner_id,
1132 p_tax_regime_id => l_regime_id,
1133 p_tax_regime_code => p_tax_regime_code,
1134 p_tax_id => l_tax_id,
1135 p_tax => p_tax,
1136 p_tax_status_id => l_status_id,
1137 p_tax_status_code => p_tax_status_code,
1138 p_tax_rate_id => p_tax_rate_id,
1139 p_tax_rate_code => p_tax_rate_code,
1140 p_tax_rate => p_tax_rate,
1141 p_tax_rate_type => NULL,
1142 p_tax_apportionment_line_num => NULL,
1143 p_trx_id_level2 => NULL,
1144 p_trx_id_level3 => NULL,
1145 p_trx_id_level4 => NULL,
1146 p_trx_id_level5 => NULL,
1147 p_trx_id_level6 => NULL,
1148 p_trx_user_key_level1 => NULL,
1149 p_trx_user_key_level2 => NULL,
1150 p_trx_user_key_level3 => NULL,
1151 p_trx_user_key_level4 => NULL,
1152 p_trx_user_key_level5 => NULL,
1153 p_trx_user_key_level6 => NULL,
1154 p_mrc_tax_line_flag => 'N',
1155 p_mrc_link_to_tax_line_id => NULL,
1156 p_ledger_id => l_ledger_id,
1157 p_establishment_id => l_establishment_id,
1158 p_legal_entity_id => l_legal_entity_id,
1159 -- p_legal_entity_tax_reg_number => NULL,
1160 p_hq_estb_reg_number => NULL,
1161 p_hq_estb_party_tax_prof_id => NULL,
1162 p_currency_conversion_date => l_currency_conversion_date,
1163 p_currency_conversion_type => l_currency_conversion_type,
1164 p_currency_conversion_rate => l_currency_conversion_rate,
1165 p_tax_curr_conversion_date => NULL,
1166 p_tax_curr_conversion_type => NULL,
1167 p_tax_curr_conversion_rate => NULL,
1168 p_trx_currency_code => NULL,
1169 p_reporting_currency_code => NULL,
1170 p_minimum_accountable_unit => NULL,
1171 p_precision => NULL,
1172 p_trx_number => v_trx_number,
1173 p_trx_date => l_trx_date,
1174 p_unit_price => NULL,
1175 p_line_amt => v_line_amt,
1176 p_trx_line_quantity => NULL,
1177 p_tax_base_modifier_rate => NULL,
1178 p_ref_doc_application_id => NULL,
1179 p_ref_doc_entity_code => NULL,
1180 p_ref_doc_event_class_code => NULL,
1181 p_ref_doc_trx_id => NULL,
1182 p_ref_doc_trx_level_type => NULL,
1183 p_ref_doc_line_id => NULL,
1184 p_ref_doc_line_quantity => NULL,
1185 p_other_doc_line_amt => NULL,
1186 p_other_doc_line_tax_amt => NULL,
1187 p_other_doc_line_taxable_amt => NULL,
1188 p_unrounded_taxable_amt => v_line_amt,
1189 p_unrounded_tax_amt => v_line_amt,
1190 p_related_doc_application_id => NULL,
1191 p_related_doc_entity_code => NULL,
1192 p_related_doc_evt_class_code => NULL,
1193 p_related_doc_trx_id => NULL,
1194 p_related_doc_trx_level_type => NULL,
1195 p_related_doc_number => NULL,
1196 p_related_doc_date => l_related_doc_date,
1197 p_applied_from_appl_id => NULL,
1198 p_applied_from_evt_clss_code => NULL,
1199 p_applied_from_entity_code => NULL,
1200 p_applied_from_trx_id => NULL,
1201 p_applied_from_trx_level_type => NULL,
1202 p_applied_from_line_id => NULL,
1203 p_applied_from_trx_number => NULL,
1204 p_adjusted_doc_appln_id => NULL,
1208 p_adjusted_doc_trx_level_type => NULL,
1205 p_adjusted_doc_entity_code => NULL,
1206 p_adjusted_doc_evt_clss_code => NULL,
1207 p_adjusted_doc_trx_id => NULL,
1209 p_adjusted_doc_line_id => NULL,
1210 p_adjusted_doc_number => NULL,
1211 p_adjusted_doc_date => l_adjusted_doc_date,
1212 p_applied_to_application_id => NULL,
1213 p_applied_to_evt_class_code => NULL,
1214 p_applied_to_entity_code => NULL,
1215 p_applied_to_trx_id => NULL,
1216 p_applied_to_trx_level_type => NULL,
1217 p_applied_to_line_id => NULL,
1218 p_summary_tax_line_id => p_summary_tax_line_id,
1219 p_offset_link_to_tax_line_id => NULL,
1220 p_offset_flag => 'N',
1221 p_process_for_recovery_flag => 'N',
1222 p_tax_jurisdiction_id => NULL,
1223 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
1224 p_place_of_supply => NULL,
1225 p_place_of_supply_type_code => l_Place_Of_Supply_Type_Code, --Bug 12688194
1226 p_place_of_supply_result_id => NULL,
1227 p_tax_date_rule_id => NULL,
1228 p_tax_date => l_tax_determine_date,
1229 p_tax_determine_date => l_tax_determine_date,
1230 p_tax_point_date => l_tax_determine_date,
1231 p_trx_line_date => v_trx_line_date,
1232 p_tax_type_code => NULL,
1233 p_tax_code => NULL,
1234 p_tax_registration_id => NULL,
1235 p_tax_registration_number => NULL,
1236 p_registration_party_type => NULL,
1237 p_rounding_level_code => 'HEADER',
1238 p_rounding_rule_code => NULL,
1239 p_rndg_lvl_party_tax_prof_id => NULL,
1240 p_rounding_lvl_party_type => NULL,
1241 p_compounding_tax_flag => 'N',
1242 p_orig_tax_status_id => NULL,
1243 p_orig_tax_status_code => NULL,
1244 p_orig_tax_rate_id => NULL,
1245 p_orig_tax_rate_code => NULL,
1246 p_orig_tax_rate => NULL,
1247 p_orig_tax_jurisdiction_id => NULL,
1248 p_orig_tax_jurisdiction_code => NULL,
1249 p_orig_tax_amt_included_flag => 'N',
1250 p_orig_self_assessed_flag => 'N',
1251 p_tax_currency_code => NULL,
1252 p_tax_amt => NULL,
1253 p_tax_amt_tax_curr => NULL,
1254 p_tax_amt_funcl_curr => NULL,
1255 p_taxable_amt => NULL,
1256 p_taxable_amt_tax_curr => NULL,
1257 p_taxable_amt_funcl_curr => NULL,
1258 p_orig_taxable_amt => NULL,
1259 p_orig_taxable_amt_tax_curr => NULL,
1260 p_cal_tax_amt => NULL,
1261 p_cal_tax_amt_tax_curr => NULL,
1262 p_cal_tax_amt_funcl_curr => NULL,
1263 p_orig_tax_amt => NULL,
1264 p_orig_tax_amt_tax_curr => NULL,
1265 p_rec_tax_amt => NULL,
1266 p_rec_tax_amt_tax_curr => NULL,
1267 p_rec_tax_amt_funcl_curr => NULL,
1268 p_nrec_tax_amt => NULL,
1269 p_nrec_tax_amt_tax_curr => NULL,
1270 p_nrec_tax_amt_funcl_curr => NULL,
1271 p_tax_exemption_id => l_tax_exemption_id,
1272 p_tax_rate_before_exemption => NULL,
1273 p_tax_rate_name_before_exempt => NULL,
1274 p_exempt_rate_modifier => NULL,
1275 p_exempt_certificate_number => l_exempt_certificate_number,
1276 p_exempt_reason => NULL,
1277 p_exempt_reason_code => l_exempt_reason_code,
1278 p_tax_exception_id => l_tax_exception_id,
1279 p_tax_rate_before_exception => NULL,
1280 p_tax_rate_name_before_except => NULL,
1281 p_exception_rate => NULL,
1282 p_tax_apportionment_flag => 'N',
1283 p_historical_flag => 'N',
1284 p_taxable_basis_formula => l_taxable_basis_formula,
1285 p_tax_calculation_formula => l_tax_calculation_formula,
1286 p_cancel_flag => 'N',
1287 p_purge_flag => 'N',
1288 p_delete_flag => 'N',
1289 p_tax_amt_included_flag => p_tax_amt_included_flag,
1290 p_self_assessed_flag => p_self_assessed_flag,
1291 p_overridden_flag => 'N',
1292 p_manually_entered_flag => p_manually_entered_flag,
1293 p_reporting_only_flag => 'N',
1294 p_freeze_until_overriddn_flg => 'N',
1295 p_copied_from_other_doc_flag => 'N',
1296 p_recalc_required_flag => 'Y',
1297 p_settlement_flag => 'N',
1298 p_item_dist_changed_flag => 'N',
1299 p_assoc_children_frozen_flg => 'N',
1300 p_tax_only_line_flag => p_tax_only_line_flag,
1301 p_compounding_dep_tax_flag => 'N',
1302 p_compounding_tax_miss_flag => 'N',
1303 p_sync_with_prvdr_flag => 'N',
1304 p_last_manual_entry => NULL,
1305 p_tax_provider_id => l_tax_provider_id,
1306 p_record_type_code => p_record_type_code,
1307 p_reporting_period_id => NULL,
1311 p_legal_message_appl_2 => NULL,
1308 p_legal_justification_text1 => NULL,
1309 p_legal_justification_text2 => NULL,
1310 p_legal_justification_text3 => NULL,
1312 p_legal_message_status => NULL,
1313 p_legal_message_rate => NULL,
1314 p_legal_message_basis => NULL,
1315 p_legal_message_calc => NULL,
1316 p_legal_message_threshold => NULL,
1317 p_legal_message_pos => NULL,
1318 p_legal_message_trn => NULL,
1319 p_legal_message_exmpt => NULL,
1320 p_legal_message_excpt => NULL,
1321 p_tax_regime_template_id => NULL,
1322 p_tax_applicability_result_id => NULL,
1323 p_direct_rate_result_id => NULL,
1324 p_status_result_id => NULL,
1325 p_rate_result_id => NULL,
1326 p_basis_result_id => NULL,
1327 p_thresh_result_id => NULL,
1328 p_calc_result_id => NULL,
1329 p_tax_reg_num_det_result_id => NULL,
1330 p_eval_exmpt_result_id => NULL,
1331 p_eval_excpt_result_id => NULL,
1332 p_enforced_from_nat_acct_flg => 'N',
1333 p_tax_hold_code => NULL,
1334 p_tax_hold_released_code => NULL,
1335 p_prd_total_tax_amt => NULL,
1336 p_prd_total_tax_amt_tax_curr => NULL,
1337 p_prd_total_tax_amt_funcl_curr => NULL,
1338 p_trx_line_index => NULL,
1339 p_offset_tax_rate_code => NULL,
1340 p_proration_code => NULL,
1341 p_other_doc_source => NULL,
1342 p_internal_org_location_id => NULL,
1343 p_line_assessable_value => NULL,
1344 p_ctrl_total_line_tx_amt => NULL,
1345 p_applied_to_trx_number => NULL,
1346 p_attribute_category => NULL,
1347 p_attribute1 => NULL,
1348 p_attribute2 => NULL,
1349 p_attribute3 => NULL,
1350 p_attribute4 => NULL,
1351 p_attribute5 => NULL,
1352 p_attribute6 => NULL,
1353 p_attribute7 => NULL,
1354 p_attribute8 => NULL,
1355 p_attribute9 => NULL,
1356 p_attribute10 => NULL,
1357 p_attribute11 => NULL,
1358 p_attribute12 => NULL,
1359 p_attribute13 => NULL,
1360 p_attribute14 => NULL,
1361 p_attribute15 => NULL,
1362 p_global_attribute_category => NULL,
1363 p_global_attribute1 => NULL,
1364 p_global_attribute2 => NULL,
1365 p_global_attribute3 => NULL,
1366 p_global_attribute4 => NULL,
1367 p_global_attribute5 => NULL,
1368 p_global_attribute6 => NULL,
1369 p_global_attribute7 => NULL,
1370 p_global_attribute8 => NULL,
1371 p_global_attribute9 => NULL,
1372 p_global_attribute10 => NULL,
1373 p_global_attribute11 => NULL,
1374 p_global_attribute12 => NULL,
1375 p_global_attribute13 => NULL,
1376 p_global_attribute14 => NULL,
1377 p_global_attribute15 => NULL,
1378 p_numeric1 => NULL,
1379 p_numeric2 => NULL,
1380 p_numeric3 => NULL,
1381 p_numeric4 => NULL,
1382 p_numeric5 => NULL,
1383 p_numeric6 => NULL,
1384 p_numeric7 => NULL,
1385 p_numeric8 => NULL,
1386 p_numeric9 => NULL,
1387 p_numeric10 => NULL,
1388 p_char1 => NULL,
1389 p_char2 => NULL,
1390 p_char3 => NULL,
1391 p_char4 => NULL,
1392 p_char5 => NULL,
1393 p_char6 => NULL,
1394 p_char7 => NULL,
1395 p_char8 => NULL,
1396 p_char9 => NULL,
1397 p_char10 => NULL,
1398 p_date1 => NULL,
1399 p_date2 => NULL,
1400 p_date3 => NULL,
1401 p_date4 => NULL,
1402 p_date5 => NULL,
1403 p_date6 => NULL,
1404 p_date7 => NULL,
1405 p_date8 => NULL,
1406 p_date9 => NULL,
1407 p_date10 => NULL,
1408 p_interface_entity_code => NULL,
1409 p_interface_tax_line_id => NULL,
1410 p_taxing_juris_geography_id => NULL,
1411 p_adjusted_doc_tax_line_id => NULL,
1412 p_object_version_number => 1,
1413 p_created_by => fnd_global.user_id,
1414 p_creation_date => sysdate,
1415 p_last_updated_by => fnd_global.user_id,
1419 END IF;
1416 p_last_update_date => sysdate,
1417 p_last_update_login => fnd_global.login_id );
1418
1420
1421 END LOOP;
1422
1423 CLOSE ALLOC;
1424
1425 ZX_TRL_ALLOCATIONS_PKG.Update_Tax_Amt
1426 (p_summary_tax_line_id => p_summary_tax_line_id,
1427 p_application_id => p_application_id,
1428 p_entity_code => p_entity_code,
1429 p_event_class_code => p_event_class_code,
1430 p_trx_id => v_trx_id,
1431 p_tax_amt => p_tax_amt);
1432
1433 ZX_TRL_ALLOCATIONS_PKG.Delete_alloc_tbl;
1434
1435 IF (g_level_procedure >= g_current_runtime_level ) THEN
1436 FND_LOG.STRING(g_level_procedure,
1437 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.END',
1438 'Insert_All_Allocation (-)');
1439 END IF;
1440
1441 EXCEPTION
1442 WHEN NO_DATA_FOUND THEN
1443 IF (g_level_procedure >= g_current_runtime_level ) THEN
1444 FND_LOG.STRING(g_level_procedure,
1445 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG. Insert_All_Allocation.EXCEPTION',
1446 'NO_DATA_FOUND Excpetion raised.');
1447 END IF;
1448 NULL;
1449
1450 END Insert_All_Allocation;
1451
1452 PROCEDURE Insert_Tax_Line
1453 (p_summary_tax_line_id NUMBER,
1454 p_internal_organization_id NUMBER,
1455 p_application_id NUMBER,
1456 p_entity_code VARCHAR2,
1457 p_event_class_code VARCHAR2,
1458 p_trx_id NUMBER,
1459 p_trx_number VARCHAR2,
1460 p_tax_regime_code VARCHAR2,
1461 p_tax VARCHAR2,
1462 p_tax_jurisdiction_code VARCHAR2,
1463 p_tax_status_code VARCHAR2,
1464 p_tax_rate_id NUMBER,
1465 p_tax_rate_code VARCHAR2,
1466 p_tax_rate NUMBER,
1467 p_tax_amt NUMBER,
1468 p_line_amt NUMBER,
1469 p_trx_line_date DATE,
1470 p_summ_tax_only VARCHAR2,
1471 p_manually_entered_flag VARCHAR2,
1472 p_last_manual_entry VARCHAR2,
1473 p_tax_amt_included_flag VARCHAR2,
1474 p_self_assessed_flag VARCHAR2,
1475 p_created_by NUMBER,
1476 p_creation_date DATE,
1477 p_last_updated_by NUMBER,
1478 p_last_update_date DATE,
1479 p_last_update_login NUMBER,
1480 p_event_type_code VARCHAR,
1481 p_legal_entity_id NUMBER,
1482 p_ledger_id NUMBER,
1483 p_trx_currency_code VARCHAR,
1484 p_currency_conversion_date DATE,
1485 p_currency_conversion_rate NUMBER,
1486 p_currency_conversion_type VARCHAR2,
1487 p_content_owner_id NUMBER,
1488 p_trx_date DATE,
1489 p_minimum_accountable_unit NUMBER,
1490 p_precision NUMBER,
1491 p_trx_line_gl_date DATE ) IS
1492
1493 l_tax_line_id NUMBER;
1494 l_trx_line_id NUMBER;
1495 l_trx_line_number NUMBER;
1496 l_tax_line_number NUMBER;
1497 l_tax_jurisdiction_id NUMBER;
1498 l_tax_currency_code VARCHAR2(100);
1499 l_tax_id NUMBER;
1500 l_regime_id NUMBER;
1501 l_status_id NUMBER;
1502
1503 v_trx_line_id zx_lines.trx_line_id%TYPE;
1504 v_trx_line_number zx_lines.trx_line_number%TYPE;
1505
1506 l_tax_event_class_code zx_evnt_cls_mappings.tax_event_class_code%type;
1507 l_trx_number zx_lines_det_factors.trx_number%type;
1508 l_legal_reporting_status zx_taxes_b.legal_reporting_status_def_val%type;
1509 l_tax_reporting_flag zx_lines_det_factors.tax_reporting_flag%type;
1510
1511 l_hq_estb_pty_tax_prof_id ZX_LINES_DET_FACTORS.HQ_ESTB_PARTY_TAX_PROF_ID%TYPE;
1512 l_internal_org_loc_id ZX_LINES_DET_FACTORS.INTERNAL_ORG_LOCATION_ID%TYPE;
1513 l_event_class_mapping_id ZX_EVNT_CLS_MAPPINGS.EVENT_CLASS_MAPPING_ID%TYPE;
1514 l_tax_type_code ZX_TAXES_B.TAX_TYPE_CODE%TYPE;
1515 l_rate_type_code ZX_RATES_B.RATE_TYPE_CODE%TYPE;
1516 l_default_taxation_country ZX_LINES_DET_FACTORS.DEFAULT_TAXATION_COUNTRY%TYPE;
1517 l_rndg_ship_fr_pty_tx_prof_id ZX_LINES_DET_FACTORS.RDNG_SHIP_FROM_PTY_TX_PROF_ID%TYPE;
1518 l_rndg_bill_fr_pty_tx_prof_id ZX_LINES_DET_FACTORS.RDNG_BILL_FROM_PTY_TX_PROF_ID%TYPE;
1519 l_rndg_ship_fr_pty_tx_p_st_id ZX_LINES_DET_FACTORS.RDNG_SHIP_FROM_PTY_TX_P_ST_ID%TYPE;
1520 l_rndg_bill_fr_pty_tx_p_st_id ZX_LINES_DET_FACTORS.RDNG_BILL_FROM_PTY_TX_P_ST_ID%TYPE;
1521 l_ship_to_location_id ZX_LINES_DET_FACTORS.SHIP_TO_LOCATION_ID%TYPE;
1522 l_ship_from_location_id ZX_LINES_DET_FACTORS.SHIP_FROM_LOCATION_ID%TYPE;
1523 l_bill_to_location_id ZX_LINES_DET_FACTORS.BILL_TO_LOCATION_ID%TYPE;
1524 l_bill_from_location_id ZX_LINES_DET_FACTORS.BILL_FROM_LOCATION_ID%TYPE;
1525 l_ship_from_pty_tax_prof_id ZX_LINES_DET_FACTORS.SHIP_FROM_PARTY_TAX_PROF_ID%TYPE;
1529 l_ctrl_hdr_tx_appl_flag ZX_LINES_DET_FACTORS.CTRL_HDR_TX_APPL_FLAG%TYPE;
1526 l_bill_from_pty_tax_prof_id ZX_LINES_DET_FACTORS.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
1527 l_ship_from_site_tx_prof_id ZX_LINES_DET_FACTORS.SHIP_FROM_SITE_TAX_PROF_ID%TYPE;
1528 l_bill_from_site_tx_prof_id ZX_LINES_DET_FACTORS.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
1530 l_ship_third_pty_acct_site_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_SITE_ID%TYPE;
1531 l_bill_third_pty_acct_site_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_SITE_ID%TYPE;
1532 l_ship_third_pty_acct_id ZX_LINES_DET_FACTORS.SHIP_THIRD_PTY_ACCT_ID%TYPE;
1533 l_bill_third_pty_acct_id ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%TYPE;
1534 l_trx_currency_code ZX_LINES_DET_FACTORS.TRX_CURRENCY_CODE%TYPE;
1535 l_currency_conversion_date ZX_LINES_DET_FACTORS.CURRENCY_CONVERSION_DATE%TYPE;
1536 l_currency_conversion_rate ZX_LINES_DET_FACTORS.CURRENCY_CONVERSION_RATE%TYPE;
1537 l_currency_conversion_type ZX_LINES_DET_FACTORS.CURRENCY_CONVERSION_TYPE%TYPE;
1538
1539
1540 BEGIN
1541
1542 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1543
1544 IF (g_level_procedure >= g_current_runtime_level ) THEN
1545 FND_LOG.STRING(g_level_procedure,
1546 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Tax_Line.BEGIN',
1547 'Insert_Tax_Line (+)');
1548 END IF;
1549
1550 l_tax_line_number :=1;
1551
1552 IF p_summ_tax_only = 'Y' THEN
1553
1554 SELECT nvl(min(trx_line_id),0),
1555 nvl(min(trx_line_number),0)
1556 INTO v_trx_line_number,
1557 v_trx_line_id
1558 FROM ZX_LINES
1559 WHERE TRX_ID = p_trx_id
1560 AND APPLICATION_ID = p_application_id
1561 AND EVENT_CLASS_CODE = p_event_class_code
1562 AND ENTITY_CODE = p_entity_code;
1563
1564 IF v_trx_line_number >= 0 THEN
1565 l_trx_line_id := -1;
1566 l_trx_line_number := -1;
1567
1568 ELSIF v_trx_line_number < 0 THEN
1569 l_trx_line_id := v_trx_line_id - 1;
1570 l_trx_line_number := v_trx_line_number - 1;
1571
1572 END IF;
1573
1574 IF (g_level_statement >= g_current_runtime_level ) THEN
1575 FND_LOG.STRING(g_level_statement,
1576 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1577 'event type code ' || p_event_type_code) ;
1578 FND_LOG.STRING(g_level_statement,
1579 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1580 'legal entity id' || to_char(p_legal_entity_id)) ;
1581 FND_LOG.STRING(g_level_statement,
1582 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1583 'ledger id' || to_char(p_ledger_id)) ;
1584 FND_LOG.STRING(g_level_statement,
1585 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1586 'trx_currency_code ' || p_trx_currency_code) ;
1587 FND_LOG.STRING(g_level_statement,
1588 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1589 'content owner id ' || p_content_owner_id) ;
1590 END IF;
1591
1592 BEGIN
1593 SELECT TAX_EVENT_CLASS_CODE, TAX_REPORTING_FLAG, EVENT_CLASS_MAPPING_ID
1594 INTO l_tax_event_class_code, l_tax_reporting_flag, l_event_class_mapping_id
1595 FROM ZX_EVNT_CLS_MAPPINGS
1596 WHERE application_id = p_application_id
1597 AND entity_code = p_entity_code
1598 AND event_class_Code = p_event_class_code;
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 l_tax_event_class_code := NULL;
1602 l_tax_reporting_flag := NULL;
1603 l_event_class_mapping_id := NULL;
1604 END;
1605
1606 BEGIN
1607
1608 select tax_id, tax_currency_code, tax_type_code,
1609 DECODE(NVL(l_tax_reporting_flag,'N'),'Y',
1610 legal_reporting_status_def_val,NULL)
1611 INTO l_tax_id, l_tax_currency_code, l_tax_type_code, l_legal_reporting_status
1612 FROM ZX_SCO_TAXES
1613 WHERE TAX_REGIME_CODE = p_tax_regime_code
1614 AND TAX=p_tax;
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1619 FND_LOG.STRING(g_level_unexpected,
1620 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
1621 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1622 FND_LOG.STRING(g_level_unexpected,
1623 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
1624 'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
1625 END IF;
1626
1627 RETURN;
1628
1629 END;
1630 IF (g_level_procedure >= g_current_runtime_level ) THEN
1631 FND_LOG.STRING(g_level_procedure,
1632 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1633 'tax id : ' || to_char(l_tax_id)) ;
1634 END IF;
1635
1636 BEGIN
1637
1638 select tax_regime_id
1639 INTO l_regime_id
1640 FROM zx_regimes_b
1641 WHERE TAX_REGIME_CODE = p_tax_regime_code;
1642
1643 EXCEPTION
1644 WHEN OTHERS THEN
1645 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1646 FND_LOG.STRING(g_level_unexpected,
1647 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
1648 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1649 FND_LOG.STRING(g_level_unexpected,
1653
1650 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
1651 'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
1652 END IF;
1654 RETURN;
1655
1656 END;
1657
1658 BEGIN
1659
1660 select tax_status_id
1661 INTO l_status_id
1662 FROM zx_sco_status
1663 WHERE tax_regime_code = p_tax_regime_code
1664 AND tax = p_tax
1665 AND tax_status_code = p_tax_status_code ;
1666
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1670 FND_LOG.STRING(g_level_unexpected,
1671 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
1672 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1673 FND_LOG.STRING(g_level_unexpected,
1674 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
1675 'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
1676 END IF;
1677
1678 RETURN;
1679
1680 END;
1681 BEGIN
1682 SELECT tax_jurisdiction_id
1683 INTO l_tax_jurisdiction_id
1684 FROM zx_jurisdictions_b
1685 WHERE tax_regime_code = p_tax_regime_code
1686 AND tax = p_tax
1687 AND tax_jurisdiction_code = p_tax_jurisdiction_code;
1688 EXCEPTION
1689 WHEN OTHERS THEN
1690 l_tax_jurisdiction_id := NULL;
1691 END;
1692
1693 BEGIN
1694
1695 SELECT rate_type_code
1696 INTO l_rate_type_code
1697 FROM ZX_SCO_RATES
1698 WHERE tax_rate_id = p_tax_rate_id;
1699
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1703 FND_LOG.STRING(g_level_unexpected,
1704 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
1705 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1706 FND_LOG.STRING(g_level_unexpected,
1707 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
1708 'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
1709 END IF;
1710
1711 RETURN;
1712
1713 END;
1714
1715 SELECT zx_lines_s.nextval
1716 INTO l_tax_line_id
1717 FROM dual;
1718
1719 IF (g_level_procedure >= g_current_runtime_level ) THEN
1720 FND_LOG.STRING(g_level_procedure,
1721 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
1722 'status id : ' || to_char(l_status_id)) ;
1723 END IF;
1724
1725
1726 IF (g_level_procedure >= g_current_runtime_level ) THEN
1727 FND_LOG.STRING(g_level_procedure,
1728 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.',
1729 'Trx Line Id: '||to_char(l_trx_line_id));
1730 END IF;
1731
1732 BEGIN
1733 SELECT TRX_NUMBER, NVL(P_TRX_CURRENCY_CODE, TRX_CURRENCY_CODE),
1734 NVL(P_CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_DATE),
1735 NVL(P_CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_RATE),
1736 NVL(P_CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_TYPE),
1737 HQ_ESTB_PARTY_TAX_PROF_ID, INTERNAL_ORG_LOCATION_ID,
1738 DEFAULT_TAXATION_COUNTRY, RDNG_SHIP_FROM_PTY_TX_PROF_ID,
1739 RDNG_BILL_FROM_PTY_TX_PROF_ID, RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
1740 RDNG_BILL_FROM_PTY_TX_P_ST_ID, SHIP_TO_LOCATION_ID,
1741 SHIP_FROM_LOCATION_ID, BILL_TO_LOCATION_ID,
1742 BILL_FROM_LOCATION_ID, SHIP_FROM_PARTY_TAX_PROF_ID,
1743 BILL_FROM_PARTY_TAX_PROF_ID, SHIP_FROM_SITE_TAX_PROF_ID,
1744 BILL_FROM_SITE_TAX_PROF_ID, CTRL_HDR_TX_APPL_FLAG,
1745 SHIP_THIRD_PTY_ACCT_SITE_ID, BILL_THIRD_PTY_ACCT_SITE_ID,
1746 SHIP_THIRD_PTY_ACCT_ID, BILL_THIRD_PTY_ACCT_ID
1747 INTO l_trx_number,l_trx_currency_code,l_currency_conversion_date,
1748 l_currency_conversion_rate,l_currency_conversion_type,
1749 l_hq_estb_pty_tax_prof_id, l_internal_org_loc_id,
1750 l_default_taxation_country, l_rndg_ship_fr_pty_tx_prof_id,
1751 l_rndg_bill_fr_pty_tx_prof_id, l_rndg_ship_fr_pty_tx_p_st_id,
1752 l_rndg_bill_fr_pty_tx_p_st_id, l_ship_to_location_id,
1753 l_ship_from_location_id, l_bill_to_location_id,
1754 l_bill_from_location_id, l_ship_from_pty_tax_prof_id,
1755 l_bill_from_pty_tax_prof_id, l_ship_from_site_tx_prof_id,
1756 l_bill_from_site_tx_prof_id, l_ctrl_hdr_tx_appl_flag,
1757 l_ship_third_pty_acct_site_id, l_bill_third_pty_acct_site_id,
1758 l_ship_third_pty_acct_id, l_bill_third_pty_acct_id
1759 FROM ZX_LINES_DET_FACTORS
1760 WHERE APPLICATION_ID = P_APPLICATION_ID
1761 AND ENTITY_CODE = P_ENTITY_CODE
1762 AND EVENT_CLASS_CODE = P_EVENT_CLASS_CODE
1763 AND TRX_ID = P_TRX_ID
1764 AND ROWNUM = 1;
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 l_trx_number := NULL;
1768 --Start Bug 8266185
1769 L_TRX_CURRENCY_CODE := P_TRX_CURRENCY_CODE;
1770 L_CURRENCY_CONVERSION_DATE := P_CURRENCY_CONVERSION_DATE;
1771 L_CURRENCY_CONVERSION_RATE := P_CURRENCY_CONVERSION_RATE;
1772 L_CURRENCY_CONVERSION_TYPE := P_CURRENCY_CONVERSION_TYPE;
1773 --End Bug 8266185
1774 END;
1775
1776
1777 INSERT INTO ZX_LINES (SUMMARY_TAX_LINE_ID,
1778 INTERNAL_ORGANIZATION_ID,
1779 APPLICATION_ID,
1780 ENTITY_CODE,
1784 TRX_ID,
1781 EVENT_CLASS_CODE,
1782 --EVENT_TYPE_CODE,
1783 TRX_LINE_NUMBER,
1785 TRX_NUMBER,
1786 TRX_LINE_ID,
1787 TRX_LEVEL_TYPE,
1788 TAX_LINE_ID,
1789 CONTENT_OWNER_ID,
1790 TAX_LINE_NUMBER,
1791 LINE_AMT,
1792 TRX_LINE_DATE,
1793 TRX_DATE,
1794 MINIMUM_ACCOUNTABLE_UNIT,
1795 PRECISION,
1796 TAX_REGIME_CODE,
1797 TAX_REGIME_ID,
1798 TAX,
1799 TAX_ID,
1800 TAX_JURISDICTION_CODE,
1801 TAX_STATUS_CODE,
1802 TAX_STATUS_ID,
1803 TAX_DETERMINE_DATE,
1804 TAX_RATE_ID,
1805 TAX_RATE_CODE,
1806 TAX_RATE,
1807 TAX_JURISDICTION_ID,
1808 TAX_AMT,
1809 TAX_CURRENCY_CODE,
1810 OFFSET_FLAG,
1811 PROCESS_FOR_RECOVERY_FLAG,
1812 COMPOUNDING_TAX_FLAG,
1813 ORIG_TAX_AMT_INCLUDED_FLAG,
1814 ORIG_SELF_ASSESSED_FLAG,
1815 TAX_APPORTIONMENT_FLAG,
1816 HISTORICAL_FLAG,
1817 CANCEL_FLAG,
1818 PURGE_FLAG,
1819 DELETE_FLAG,
1820 TAX_AMT_INCLUDED_FLAG,
1821 SELF_ASSESSED_FLAG,
1822 OVERRIDDEN_FLAG,
1823 MANUALLY_ENTERED_FLAG,
1824 REPORTING_ONLY_FLAG,
1825 FREEZE_UNTIL_OVERRIDDEN_FLAG,
1826 COPIED_FROM_OTHER_DOC_FLAG,
1827 RECALC_REQUIRED_FLAG,
1828 SETTLEMENT_FLAG,
1829 ITEM_DIST_CHANGED_FLAG,
1830 ASSOCIATED_CHILD_FROZEN_FLAG,
1831 TAX_ONLY_LINE_FLAG,
1832 COMPOUNDING_DEP_TAX_FLAG,
1833 ENFORCE_FROM_NATURAL_ACCT_FLAG,
1834 MRC_TAX_LINE_FLAG,
1835 LAST_MANUAL_ENTRY,
1836 UNROUNDED_TAX_AMT,
1837 UNROUNDED_TAXABLE_AMT,
1838 RECORD_TYPE_CODE,
1839 TAX_APPORTIONMENT_LINE_NUMBER,
1840 ROUNDING_LEVEL_CODE,
1841 OBJECT_VERSION_NUMBER,
1842 --bug 7300367
1843 TAX_EVENT_CLASS_CODE,
1844 TAXABLE_BASIS_FORMULA,
1845 TAX_CALCULATION_FORMULA,
1846 COMPOUNDING_TAX_MISS_FLAG,
1847 TRX_CURRENCY_CODE,
1848 CURRENCY_CONVERSION_DATE,
1849 CURRENCY_CONVERSION_RATE,
1850 CURRENCY_CONVERSION_TYPE,
1851 LEGAL_REPORTING_STATUS,
1852 --bug 7300367
1853 --bug 7369708
1854 HQ_ESTB_PARTY_TAX_PROF_ID,
1855 TAX_CURRENCY_CONVERSION_DATE,
1856 UNIT_PRICE,
1857 TRX_LINE_QUANTITY,
1858 TAX_BASE_MODIFIER_RATE,
1859 PLACE_OF_SUPPLY_TYPE_CODE,
1860 TAX_DATE,
1861 TAX_POINT_DATE,
1862 TAX_TYPE_CODE,
1863 --ROUNDING_LVL_PARTY_TAX_PROF_ID,
1864 --ROUNDING_LVL_PARTY_TYPE,
1865 INTERNAL_ORG_LOCATION_ID,
1866 LINE_ASSESSABLE_VALUE,
1867 TAX_RATE_TYPE,
1868 --bug 7369708
1869 CREATED_BY,
1870 CREATION_DATE,
1871 LAST_UPDATED_BY,
1872 LAST_UPDATE_DATE,
1873 LAST_UPDATE_LOGIN)
1874 VALUES (p_summary_tax_line_id,
1875 p_internal_organization_id,
1876 p_application_id,
1877 p_entity_code,
1878 p_event_class_code,
1879 --p_event_type_code,
1880 l_trx_line_number,
1881 p_trx_id,
1882 p_trx_number,
1883 l_trx_line_id,
1884 'LINE',--p_trx_level_type,
1885 l_tax_line_id,
1886 p_content_owner_id,
1887 l_tax_line_number,
1888 p_line_amt,
1889 p_trx_line_date,
1890 p_trx_date,
1891 p_minimum_accountable_unit,
1892 p_precision,
1893 p_tax_regime_code,
1894 l_regime_id,
1895 p_tax,
1896 l_tax_id,
1897 p_tax_jurisdiction_code,
1901 p_tax_rate_id,
1898 p_tax_status_code,
1899 l_status_id,
1900 p_trx_date,
1902 p_tax_rate_code,
1903 p_tax_rate,
1904 l_tax_jurisdiction_id,
1905 NULL, --p_tax_amt,
1906 l_tax_currency_code,
1907 'N',
1908 'Y',
1909 'N',
1910 'N',
1911 'N',
1912 'N',
1913 'N',
1914 'N',
1915 'N',
1916 'N',
1917 p_tax_amt_included_flag,
1918 p_self_assessed_flag,
1919 'N',
1920 'Y',
1921 'N',
1922 'N',
1923 'N',
1924 'Y',
1925 'N',
1926 'N',
1927 'N',
1928 'Y',
1929 'N',
1930 'N',
1931 'N',
1932 'TAX_AMOUNT',
1933 p_tax_amt,
1934 (p_tax_amt/p_tax_rate) * 100,
1935 'ETAX_CREATED',
1936 1,
1937 'HEADER',
1938 1, -- object_version_number,
1939 --bug 7300367
1940 l_tax_event_class_code,
1941 'STANDARD_TB',
1942 'STANDARD_TC',
1943 'N',
1944 l_trx_currency_code,
1945 l_CURRENCY_CONVERSION_DATE,
1946 l_CURRENCY_CONVERSION_RATE,
1947 l_CURRENCY_CONVERSION_TYPE,
1948 l_legal_reporting_status,
1949 --bug 7300367
1950 --bug 7369708
1951 l_hq_estb_pty_tax_prof_id, --HQ_ESTB_PARTY_TAX_PROF_ID
1952 l_CURRENCY_CONVERSION_DATE, --TAX_CURRENCY_CONVERSION_DATE
1953 P_LINE_AMT, --UNIT_PRICE
1954 1, --TRX_LINE_QUANTITY
1955 1, --TAX_BASE_MODIFIER_RATE
1956 'SHIP_TO',
1957 p_trx_date, --TAX_DATE
1958 p_trx_date, --TAX_POINT_DATE
1959 l_tax_type_code,
1960 --ROUNDING_LVL_PARTY_TAX_PROF_ID,
1961 --ROUNDING_LVL_PARTY_TYPE,
1962 l_internal_org_loc_id, --INTERNAL_ORG_LOCATION_ID
1963 P_LINE_AMT, --LINE_ASSESSABLE_VALUE
1964 l_rate_type_code,
1965 --bug 7369708
1966 fnd_global.user_id,
1967 sysdate,
1968 fnd_global.user_id,
1969 sysdate,
1970 fnd_global.login_id);
1971
1972 INSERT INTO ZX_LINES_DET_FACTORS( INTERNAL_ORGANIZATION_ID,
1973 APPLICATION_ID,
1974 ENTITY_CODE,
1975 EVENT_CLASS_CODE,
1976 EVENT_TYPE_CODE,
1977 LINE_LEVEL_ACTION,
1978 TRX_ID,
1979 TRX_LINE_ID,
1980 TRX_LEVEL_TYPE,
1981 TRX_DATE,
1982 TRX_LINE_GL_DATE,
1983 LEGAL_ENTITY_ID,
1984 LINE_AMT,
1985 LINE_AMT_INCLUDES_TAX_FLAG,
1986 record_type_code,
1987 object_version_number,
1988 LEDGER_ID,
1989 FIRST_PTY_ORG_ID,
1990 TRX_CURRENCY_CODE,
1991 CURRENCY_CONVERSION_DATE,
1992 CURRENCY_CONVERSION_RATE,
1993 CURRENCY_CONVERSION_TYPE,
1994 tax_processing_completed_flag,
1995 --bug 7300367
1996 TAX_EVENT_CLASS_CODE,
1997 LINE_CLASS,
1998 TRX_LINE_TYPE,
1999 TRX_NUMBER,
2000 TRX_LINE_NUMBER,
2001 ASSESSABLE_VALUE,
2002 HISTORICAL_FLAG,
2003 MINIMUM_ACCOUNTABLE_UNIT,
2004 PRECISION,
2005 TAX_REPORTING_FLAG,
2006 --bug 7300367
2007 --bug 7369708
2008 EVENT_CLASS_MAPPING_ID,
2009 DEFAULT_TAXATION_COUNTRY,
2010 --DOC_SEQ_ID,
2014 RDNG_BILL_FROM_PTY_TX_PROF_ID,
2011 --DOC_SEQ_NAME,
2012 --DOC_SEQ_VALUE,
2013 RDNG_SHIP_FROM_PTY_TX_PROF_ID,
2015 RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
2016 RDNG_BILL_FROM_PTY_TX_P_ST_ID,
2017 TRX_LINE_QUANTITY,
2018 HQ_ESTB_PARTY_TAX_PROF_ID,
2019 SHIP_TO_LOCATION_ID,
2020 SHIP_FROM_LOCATION_ID,
2021 BILL_TO_LOCATION_ID,
2022 BILL_FROM_LOCATION_ID,
2023 SHIP_FROM_PARTY_TAX_PROF_ID,
2024 BILL_FROM_PARTY_TAX_PROF_ID,
2025 SHIP_FROM_SITE_TAX_PROF_ID,
2026 BILL_FROM_SITE_TAX_PROF_ID,
2027 CTRL_HDR_TX_APPL_FLAG,
2028 TRX_LINE_DATE,
2029 INTERNAL_ORG_LOCATION_ID,
2030 UNIT_PRICE,
2031 SHIP_THIRD_PTY_ACCT_SITE_ID,
2032 BILL_THIRD_PTY_ACCT_SITE_ID,
2033 SHIP_THIRD_PTY_ACCT_ID,
2034 BILL_THIRD_PTY_ACCT_ID,
2035 TRX_LINE_CURRENCY_CODE,
2036 TRX_LINE_CURRENCY_CONV_RATE,
2037 TRX_LINE_CURRENCY_CONV_DATE,
2038 TRX_LINE_PRECISION,
2039 TRX_LINE_MAU,
2040 TRX_LINE_CURRENCY_CONV_TYPE,
2041 TOTAL_INC_TAX_AMT,
2042 CREATED_BY,
2043 CREATION_DATE,
2044 LAST_UPDATED_BY,
2045 LAST_UPDATE_DATE,
2046 LAST_UPDATE_LOGIN
2047 --bug 7369708
2048 )
2049 VALUES
2050 (
2051 p_internal_organization_id,
2052 p_APPLICATION_ID,
2053 P_ENTITY_CODE,
2054 P_EVENT_CLASS_CODE,
2055 p_event_type_code,
2056 'LINE_INFO_TAX_ONLY',
2057 p_TRX_ID,
2058 l_trx_line_id,
2059 'LINE',
2060 P_TRX_DATE,
2061 p_trx_line_gl_date,
2062 p_LEGAL_ENTITY_ID,
2063 p_tax_amt,
2064 p_tax_amt_included_flag,
2065 'ETAX_CREATED',
2066 1,
2067 p_LEDGER_ID,
2068 p_content_owner_id,
2069 l_trx_currency_code, -- should be changed to p_trx_currence_code
2070 l_CURRENCY_CONVERSION_DATE,
2071 l_CURRENCY_CONVERSION_RATE,
2072 l_CURRENCY_CONVERSION_TYPE,
2073 'Y',
2074 --bug 7300367
2075 l_tax_event_class_code,
2076 P_EVENT_CLASS_CODE,
2077 'ITEM',
2078 l_trx_number,
2079 l_trx_line_number,
2080 p_line_amt,
2081 'N',
2082 p_minimum_accountable_unit,
2083 p_precision,
2084 l_tax_reporting_flag,
2085 --bug 7300367
2086 --bug 7369708
2087 l_event_class_mapping_id, --EVENT_CLASS_MAPPING_ID
2088 l_default_taxation_country, --DEFAULT_TAXATION_COUNTRY
2089 --DOC_SEQ_ID,
2090 --DOC_SEQ_NAME,
2091 --DOC_SEQ_VALUE,
2092 l_rndg_ship_fr_pty_tx_prof_id, --RDNG_SHIP_FROM_PTY_TX_PROF_ID
2093 l_rndg_bill_fr_pty_tx_prof_id, --RDNG_BILL_FROM_PTY_TX_PROF_ID
2094 l_rndg_ship_fr_pty_tx_p_st_id, --RDNG_SHIP_FROM_PTY_TX_P_ST_ID
2095 l_rndg_bill_fr_pty_tx_p_st_id, --RDNG_BILL_FROM_PTY_TX_P_ST_ID
2096 1, --TRX_LINE_QUANTITY
2097 l_hq_estb_pty_tax_prof_id,--HQ_ESTB_PARTY_TAX_PROF_ID
2098 l_ship_to_location_id, --SHIP_TO_LOCATION_ID
2099 l_ship_from_location_id, --SHIP_FROM_LOCATION_ID
2100 l_bill_to_location_id, --BILL_TO_LOCATION_ID
2104 l_ship_from_site_tx_prof_id, --SHIP_FROM_SITE_TAX_PROF_ID
2101 l_bill_from_location_id, --BILL_FROM_LOCATION_ID
2102 l_ship_from_pty_tax_prof_id, --SHIP_FROM_PARTY_TAX_PROF_ID
2103 l_bill_from_pty_tax_prof_id, --BILL_FROM_PARTY_TAX_PROF_ID
2105 l_bill_from_site_tx_prof_id, --BILL_FROM_SITE_TAX_PROF_ID
2106 l_ctrl_hdr_tx_appl_flag, --CTRL_HDR_TX_APPL_FLAG
2107 p_trx_line_date, --TRX_LINE_DATE
2108 l_internal_org_loc_id, --INTERNAL_ORG_LOCATION_ID
2109 p_line_amt, --UNIT_PRICE
2110 l_ship_third_pty_acct_site_id, --SHIP_THIRD_PTY_ACCT_SITE_ID
2111 l_bill_third_pty_acct_site_id, --BILL_THIRD_PTY_ACCT_SITE_ID
2112 l_ship_third_pty_acct_id, --SHIP_THIRD_PTY_ACCT_ID
2113 l_bill_third_pty_acct_id, --BILL_THIRD_PTY_ACCT_ID
2114 l_trx_currency_code, --TRX_LINE_CURRENCY_CODE
2115 l_currency_conversion_rate, --TRX_LINE_CURRENCY_CONV_RATE
2116 l_currency_conversion_date, --TRX_LINE_CURRENCY_CONV_DATE
2117 p_precision, --TRX_LINE_PRECISION
2118 p_minimum_accountable_unit, --TRX_LINE_MAU
2119 l_currency_conversion_type, --TRX_LINE_CURRENCY_CONV_TYPE
2120 0, --TOTAL_INC_TAX_AMT
2121 fnd_global.user_id, --CREATED_BY
2122 sysdate, --CREATION_DATE
2123 fnd_global.user_id, --LAST_UPDATED_BY
2124 sysdate, --LAST_UPDATE_DATE
2125 fnd_global.login_id --LAST_UPDATE_LOGIN
2126 --bug 7369708
2127 );
2128 END IF;
2129
2130 IF (g_level_procedure >= g_current_runtime_level ) THEN
2131 FND_LOG.STRING(g_level_procedure,
2132 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Tax_Line.BEGIN',
2133 'Insert_Tax_Line (-)');
2134 END IF;
2135
2136 EXCEPTION
2137 WHEN NO_DATA_FOUND THEN
2138 NULL;
2139
2140 END Insert_Tax_Line;
2141
2142 PROCEDURE Populate_alloc_tbl(p_key IN VARCHAR2) IS
2143 BEGIN
2144 IF (g_level_statement >= g_current_runtime_level ) THEN
2145 FND_LOG.STRING(g_level_statement,
2146 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Populate_alloc_tbl',
2147 'key : ' || p_key) ;
2148 END IF;
2149 g_trx_allocate_tbl(p_key) := 'Y';
2150 EXCEPTION
2151 WHEN others THEN
2152 NULL;
2153 END;
2154
2155 PROCEDURE Delete_alloc_tbl IS
2156 BEGIN
2157 IF (g_level_statement >= g_current_runtime_level ) THEN
2158 FND_LOG.STRING(g_level_statement,
2159 'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Delete_alloc_tbl',
2160 'Plsql Cache Structure flushed') ;
2161 END IF;
2162 g_trx_allocate_tbl.DELETE;
2163 EXCEPTION
2164 WHEN others THEN
2165 NULL;
2166 END;
2167 END ZX_TRL_ALLOCATIONS_PKG;