[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row
(X_Rowid IN OUT NOCOPY VARCHAR2,
p_summary_tax_line_id NUMBER,
p_internal_organization_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_event_type_code VARCHAR2,
p_trx_line_number NUMBER,--
p_trx_id NUMBER,
p_trx_number VARCHAR2,--
p_trx_line_id NUMBER,--
p_trx_level_type VARCHAR2,
p_line_amt NUMBER,--
p_trx_line_date DATE,--
p_tax_regime_code VARCHAR2,
p_tax VARCHAR2,
p_tax_jurisdiction_code VARCHAR2,
p_tax_status_code VARCHAR2,
p_tax_rate_id NUMBER,
p_tax_rate_code VARCHAR2,
p_tax_rate NUMBER,
p_tax_amt NUMBER,
p_enabled_record VARCHAR2,
p_manually_entered_flag VARCHAR2,
p_content_owner_id NUMBER,
p_record_type_code VARCHAR2,
p_last_manual_entry VARCHAR2,
p_trx_line_amt NUMBER,
p_tax_amt_included_flag VARCHAR2,
p_self_assessed_flag VARCHAR2,
p_tax_only_line_flag VARCHAR2,
p_created_by NUMBER,
p_creation_date DATE,
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER) IS
l_tax_line_id NUMBER;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row.BEGIN',
'Insert_Row (+)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Insert into zx_lines for allocation of lines. (+)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Is Record enabled: '|| p_enabled_record);
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Input Parameters :- Summary Tax Line Id: '|| to_char(p_summary_tax_line_id)||
', Application Id: '|| to_char(p_application_id)||
', Entity Code: '|| p_entity_code||
', Event Class Code: '|| p_event_class_code||
', Trx Id: '|| to_char(p_trx_id)||
', Trx Line Id: '|| to_char(p_trx_line_id));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Executing Trx Line Exists Validation - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
SELECT /*+first_rows(1)*/ 'Y'
INTO l_trx_line_exist
FROM zx_lines_det_factors
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = p_trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type
AND ROWNUM = 1;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Trx Line Exists Validation : FAILED');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Trx Line Exists Validation : PASSED - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
SELECT NVL(max(TAX_LINE_NUMBER),0)+1
INTO l_tax_line_number
FROM ZX_LINES
WHERE APPLICATION_ID = p_application_id
AND EVENT_CLASS_CODE = p_event_class_code
AND ENTITY_CODE = p_entity_code
AND TRX_ID = p_trx_id
AND TRX_LINE_ID = p_trx_line_id
AND TRX_LEVEL_TYPE = p_trx_level_type;
SELECT zx_lines_s.nextval
INTO l_tax_line_id
FROM dual;
SELECT DISTINCT precision,minimum_accountable_unit,trx_currency_code
INTO l_precision, l_minimum_accountable_unit, l_trx_currency_code
FROM zx_lines_det_factors zxd, zx_lines_summary zxs
WHERE zxd.trx_id = zxs.trx_id
AND zxs.summary_tax_line_id = p_summary_tax_line_id;
ZX_TRL_DETAIL_OVERRIDE_PKG.Insert_row
(x_rowid => X_Rowid,
p_tax_line_id => l_tax_line_id,
p_internal_organization_id => p_internal_organization_id,
p_application_id => p_application_id,
p_entity_code => p_entity_code,
p_event_class_code => p_event_class_code,
p_event_type_code => NULL,
p_trx_id => p_trx_id,
p_trx_line_id => p_trx_line_id,
p_trx_level_type => p_trx_level_type,
p_trx_line_number => p_trx_line_number,
p_doc_event_status => NULL,
p_tax_event_class_code => NULL,
p_tax_event_type_code => NULL,
p_tax_line_number => l_tax_line_number,
p_content_owner_id => p_content_owner_id,
p_tax_regime_id => NULL,
p_tax_regime_code => p_tax_regime_code,
p_tax_id => NULL,
p_tax => p_tax,
p_tax_status_id => NULL,
p_tax_status_code => p_tax_status_code,
p_tax_rate_id => p_tax_rate_id,
p_tax_rate_code => p_tax_rate_code,
p_tax_rate => p_tax_rate,
p_tax_rate_type => NULL,
p_tax_apportionment_line_num => NULL,
p_trx_id_level2 => NULL,
p_trx_id_level3 => NULL,
p_trx_id_level4 => NULL,
p_trx_id_level5 => NULL,
p_trx_id_level6 => NULL,
p_trx_user_key_level1 => NULL,
p_trx_user_key_level2 => NULL,
p_trx_user_key_level3 => NULL,
p_trx_user_key_level4 => NULL,
p_trx_user_key_level5 => NULL,
p_trx_user_key_level6 => NULL,
p_mrc_tax_line_flag => 'N',
p_mrc_link_to_tax_line_id => NULL,
p_ledger_id => NULL,
p_establishment_id => NULL,
p_legal_entity_id => NULL,
--p_legal_entity_tax_reg_number => NULL,
p_hq_estb_reg_number => NULL,
p_hq_estb_party_tax_prof_id => NULL,
p_currency_conversion_date => NULL,
p_currency_conversion_type => NULL,
p_currency_conversion_rate => NULL,
p_tax_curr_conversion_date => NULL,
p_tax_curr_conversion_type => NULL,
p_tax_curr_conversion_rate => NULL,
p_trx_currency_code => l_trx_currency_code,
p_reporting_currency_code => NULL,
p_minimum_accountable_unit => l_minimum_accountable_unit,
p_precision => l_precision,
p_trx_number => p_trx_number,
p_trx_date => NULL,
p_unit_price => NULL,
p_line_amt => p_trx_line_amt,
p_trx_line_quantity => NULL,
p_tax_base_modifier_rate => NULL,
p_ref_doc_application_id => NULL,
p_ref_doc_entity_code => NULL,
p_ref_doc_event_class_code => NULL,
p_ref_doc_trx_id => NULL,
p_ref_doc_trx_level_type => NULL,
p_ref_doc_line_id => NULL,
p_ref_doc_line_quantity => NULL,
p_other_doc_line_amt => NULL,
p_other_doc_line_tax_amt => NULL,
p_other_doc_line_taxable_amt => NULL,
p_unrounded_taxable_amt => p_trx_line_amt,
p_unrounded_tax_amt => p_tax_amt,
p_related_doc_application_id => NULL,
p_related_doc_entity_code => NULL,
p_related_doc_evt_class_code => NULL,
p_related_doc_trx_id => NULL,
p_related_doc_trx_level_type => NULL,
p_related_doc_number => NULL,
p_related_doc_date => NULL,
p_applied_from_appl_id => NULL,
p_applied_from_evt_clss_code => NULL,
p_applied_from_entity_code => NULL,
p_applied_from_trx_id => NULL,
p_applied_from_trx_level_type => NULL,
p_applied_from_line_id => NULL,
p_applied_from_trx_number => NULL,
p_adjusted_doc_appln_id => NULL,
p_adjusted_doc_entity_code => NULL,
p_adjusted_doc_evt_clss_code => NULL,
p_adjusted_doc_trx_id => NULL,
p_adjusted_doc_trx_level_type => NULL,
p_adjusted_doc_line_id => NULL,
p_adjusted_doc_number => NULL,
p_adjusted_doc_date => NULL,
p_applied_to_application_id => NULL,
p_applied_to_evt_class_code => NULL,
p_applied_to_entity_code => NULL,
p_applied_to_trx_id => NULL,
p_applied_to_trx_level_type => NULL,
p_applied_to_line_id => NULL,
p_summary_tax_line_id => p_summary_tax_line_id,
p_offset_link_to_tax_line_id => NULL,
p_offset_flag => 'N',
p_process_for_recovery_flag => 'N',
p_tax_jurisdiction_id => NULL,
p_tax_jurisdiction_code => p_tax_jurisdiction_code,
p_place_of_supply => NULL,
p_place_of_supply_type_code => NULL,
p_place_of_supply_result_id => NULL,
p_tax_date_rule_id => NULL,
p_tax_date => NULL,
p_tax_determine_date => sysdate,
p_tax_point_date => NULL,
p_trx_line_date => p_trx_line_date,
p_tax_type_code => NULL,
p_tax_code => NULL,
p_tax_registration_id => NULL,
p_tax_registration_number => NULL,
p_registration_party_type => NULL,
p_rounding_level_code => 'HEADER',
p_rounding_rule_code => NULL,
p_rndg_lvl_party_tax_prof_id => NULL,
p_rounding_lvl_party_type => NULL,
p_compounding_tax_flag => 'N',
p_orig_tax_status_id => NULL,
p_orig_tax_status_code => NULL,
p_orig_tax_rate_id => NULL,
p_orig_tax_rate_code => NULL,
p_orig_tax_rate => NULL,
p_orig_tax_jurisdiction_id => NULL,
p_orig_tax_jurisdiction_code => NULL,
p_orig_tax_amt_included_flag => 'N',
p_orig_self_assessed_flag => 'N',
p_tax_currency_code => NULL,
p_tax_amt => p_tax_amt,
p_tax_amt_tax_curr => NULL,
p_tax_amt_funcl_curr => NULL,
p_taxable_amt => NULL,
p_taxable_amt_tax_curr => NULL,
p_taxable_amt_funcl_curr => NULL,
p_orig_taxable_amt => NULL,
p_orig_taxable_amt_tax_curr => NULL,
p_cal_tax_amt => NULL,
p_cal_tax_amt_tax_curr => NULL,
p_cal_tax_amt_funcl_curr => NULL,
p_orig_tax_amt => NULL,
p_orig_tax_amt_tax_curr => NULL,
p_rec_tax_amt => NULL,
p_rec_tax_amt_tax_curr => NULL,
p_rec_tax_amt_funcl_curr => NULL,
p_nrec_tax_amt => NULL,
p_nrec_tax_amt_tax_curr => NULL,
p_nrec_tax_amt_funcl_curr => NULL,
p_tax_exemption_id => NULL,
p_tax_rate_before_exemption => NULL,
p_tax_rate_name_before_exempt => NULL,
p_exempt_rate_modifier => NULL,
p_exempt_certificate_number => NULL,
p_exempt_reason => NULL,
p_exempt_reason_code => NULL,
p_tax_exception_id => NULL,
p_tax_rate_before_exception => NULL,
p_tax_rate_name_before_except => NULL,
p_exception_rate => NULL,
p_tax_apportionment_flag => 'N',
p_historical_flag => 'N',
p_taxable_basis_formula => NULL,
p_tax_calculation_formula => NULL,
p_cancel_flag => 'N',
p_purge_flag => 'N',
p_delete_flag => 'N',
p_tax_amt_included_flag => p_tax_amt_included_flag,
p_self_assessed_flag => p_self_assessed_flag,
p_overridden_flag => 'N',
p_manually_entered_flag => p_manually_entered_flag,
p_reporting_only_flag => 'N',
p_freeze_until_overriddn_flg => 'N',
p_copied_from_other_doc_flag => 'N',
p_recalc_required_flag => 'Y',
p_settlement_flag => 'N',
p_item_dist_changed_flag => 'N',
p_assoc_children_frozen_flg => 'N',
p_tax_only_line_flag => p_tax_only_line_flag,
p_compounding_dep_tax_flag => 'N',
p_compounding_tax_miss_flag => 'N',
p_sync_with_prvdr_flag => 'N',
p_last_manual_entry => p_last_manual_entry,
p_tax_provider_id => NULL,
p_record_type_code => p_record_type_code,
p_reporting_period_id => NULL,
p_legal_justification_text1 => NULL,
p_legal_justification_text2 => NULL,
p_legal_justification_text3 => NULL,
p_legal_message_appl_2 => NULL,
p_legal_message_status => NULL,
p_legal_message_rate => NULL,
p_legal_message_basis => NULL,
p_legal_message_calc => NULL,
p_legal_message_threshold => NULL,
p_legal_message_pos => NULL,
p_legal_message_trn => NULL,
p_legal_message_exmpt => NULL,
p_legal_message_excpt => NULL,
p_tax_regime_template_id => NULL,
p_tax_applicability_result_id => NULL,
p_direct_rate_result_id => NULL,
p_status_result_id => NULL,
p_rate_result_id => NULL,
p_basis_result_id => NULL,
p_thresh_result_id => NULL,
p_calc_result_id => NULL,
p_tax_reg_num_det_result_id => NULL,
p_eval_exmpt_result_id => NULL,
p_eval_excpt_result_id => NULL,
p_enforced_from_nat_acct_flg => 'N',
p_tax_hold_code => NULL,
p_tax_hold_released_code => NULL,
p_prd_total_tax_amt => NULL,
p_prd_total_tax_amt_tax_curr => NULL,
p_prd_total_tax_amt_funcl_curr => NULL,
p_trx_line_index => NULL,
p_offset_tax_rate_code => NULL,
p_proration_code => NULL,
p_other_doc_source => NULL,
p_internal_org_location_id => NULL,
p_line_assessable_value => NULL,
p_ctrl_total_line_tx_amt => NULL,
p_applied_to_trx_number => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_global_attribute_category => NULL,
p_global_attribute1 => NULL,
p_global_attribute2 => NULL,
p_global_attribute3 => NULL,
p_global_attribute4 => NULL,
p_global_attribute5 => NULL,
p_global_attribute6 => NULL,
p_global_attribute7 => NULL,
p_global_attribute8 => NULL,
p_global_attribute9 => NULL,
p_global_attribute10 => NULL,
p_global_attribute11 => NULL,
p_global_attribute12 => NULL,
p_global_attribute13 => NULL,
p_global_attribute14 => NULL,
p_global_attribute15 => NULL,
p_numeric1 => NULL,
p_numeric2 => NULL,
p_numeric3 => NULL,
p_numeric4 => NULL,
p_numeric5 => NULL,
p_numeric6 => NULL,
p_numeric7 => NULL,
p_numeric8 => NULL,
p_numeric9 => NULL,
p_numeric10 => NULL,
p_char1 => NULL,
p_char2 => NULL,
p_char3 => NULL,
p_char4 => NULL,
p_char5 => NULL,
p_char6 => NULL,
p_char7 => NULL,
p_char8 => NULL,
p_char9 => NULL,
p_char10 => NULL,
p_date1 => NULL,
p_date2 => NULL,
p_date3 => NULL,
p_date4 => NULL,
p_date5 => NULL,
p_date6 => NULL,
p_date7 => NULL,
p_date8 => NULL,
p_date9 => NULL,
p_date10 => NULL,
p_interface_entity_code => NULL,
p_interface_tax_line_id => NULL,
p_taxing_juris_geography_id => NULL,
p_adjusted_doc_tax_line_id => NULL,
p_object_version_number => 1,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id);
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row',
'Insert into zx_lines for allocation of lines. (-)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Row.END',
'Insert_Row (-)');
END Insert_Row;
| Procedure : Update_tax_amt |
| |
| Description: |
| Procedure to prorate the summary tax line amount across |
| across the detail tax lines as per their corresponding |
| line amounts. |
| |
| Modified the procedure parameters for Bug#9664737 |
+-------------------------------------------------------------*/
PROCEDURE Update_tax_amt
(p_summary_tax_line_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_trx_id NUMBER,
p_tax_amt NUMBER DEFAULT NULL) IS
--l_total_tax_amt NUMBER;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt.BEGIN',
'Update_tax_amt (+)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
'Input Parameters :- Summary Tax Line Id: '|| to_char(p_summary_tax_line_id)||
', Application Id: '|| to_char(p_application_id)||
', Entity Code: '|| p_entity_code||
', Event Class Code: '|| p_event_class_code||
', Trx Id: '|| to_char(p_trx_id)||
', Tax Amount: '|| to_char(p_tax_amt));
SELECT TAX_AMT
INTO l_summary_tax_amt
FROM ZX_LINES_SUMMARY
WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
'Summary_Tax_Amount : '||to_char(l_summary_tax_amt));
SELECT SUM(LINE_AMT)
INTO l_total_trx_line_amt
FROM ZX_LINES
WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
AND APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND NVL(CANCEL_FLAG,'N') <> 'Y';
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
'Total_Trx_Line_Amount : '||to_char(l_total_trx_line_amt));
UPDATE ZX_LINES
SET unrounded_tax_amt = ((line_amt * l_summary_tax_amt) / l_total_trx_line_amt)
WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
AND APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND NVL(CANCEL_FLAG,'N') <> 'Y';
SELECT SUM(unrounded_tax_amt)
INTO l_unrounded_tax_amt
FROM ZX_LINES
WHERE SUMMARY_TAX_LINE_ID = p_summary_tax_line_id
AND APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND NVL(CANCEL_FLAG,'N') <> 'Y';
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt',
'Total_Unrounded_Tax_Amt '||to_char(l_unrounded_tax_amt));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Update_tax_amt.END',
'Update_tax_amt (-)');
END Update_tax_amt;
SELECT 'Y' INTO l_alloc_to_summ
FROM zx_lines
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = l_trx_record.p_trx_id(i)
AND trx_line_id = l_trx_record.p_trx_line_id(i)
AND trx_level_type = l_trx_record.p_trx_level_type(i)
AND summary_tax_line_id = p_summary_tax_line_id
AND ROWNUM = 1;
SELECT 'Y' INTO l_alloc_to_r2r
FROM zx_lines
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = l_trx_record.p_trx_id(i)
AND trx_line_id = l_trx_record.p_trx_line_id(i)
AND trx_level_type = l_trx_record.p_trx_level_type(i)
AND tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND ROWNUM = 1;
| Procedure : Insert_All_Allocation |
| |
| Description: |
| Procedure to create detail tax lines for the item lines |
| marked for allocation |
| |
| Modified the procedure parameters for Bug#9664737 |
+-------------------------------------------------------------*/
PROCEDURE Insert_All_Allocation
(X_Rowid IN OUT NOCOPY VARCHAR2,
p_summary_tax_line_id NUMBER,
p_internal_organization_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_tax_regime_code VARCHAR2,
p_tax VARCHAR2,
p_tax_jurisdiction_code VARCHAR2,
p_tax_status_code VARCHAR2,
p_tax_rate_id NUMBER,
p_tax_rate_code VARCHAR2,
p_tax_rate NUMBER,
p_tax_amt NUMBER,
p_enabled_record VARCHAR2,
p_summ_tax_only VARCHAR2,
p_statement VARCHAR2,
p_manually_entered_flag VARCHAR2,
p_content_owner_id NUMBER,
p_record_type_code VARCHAR2,
p_last_manual_entry VARCHAR2,
p_tax_amt_included_flag VARCHAR2,
p_self_assessed_flag VARCHAR2,
p_tax_only_line_flag VARCHAR2,
p_created_by NUMBER,
p_creation_date DATE,
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER,
p_allocate_flag IN VARCHAR2 DEFAULT 'N'
) IS
l_tax_line_id NUMBER;
l_insert BOOLEAN ;
SELECT tax_id,Def_Place_Of_Supply_Type_Code --Bug 12688194
FROM zx_sco_taxes_b_v
WHERE tax = c_tax
AND tax_regime_code = c_tax_regime_code
ORDER BY subscription_level_code;
SELECT tax_status_id
FROM zx_sco_status_b_v
WHERE tax_status_code = c_tax_status_code
AND tax = c_tax
AND tax_regime_code = c_tax_regime_code
ORDER BY subscription_level_code;
SELECT ledger_id,
legal_entity_id,
establishment_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
taxable_basis_formula,
tax_calculation_formula,
tax_exemption_id,
exempt_certificate_number,
exempt_reason_code,
tax_exception_id,
tax_provider_id
FROM zx_lines_summary
WHERE summary_tax_line_id = c_summary_tax_line_id;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.BEGIN',
'Insert_All_Allocation (+)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Input Parameters : Summary_Tax_line_id='||p_summary_tax_line_id||
', Application_id='||p_application_id||
', Entity_code='||p_entity_code||
', Event_class_code='||p_event_class_code||
', Internal_org_id='||p_internal_organization_id||
', Tax_regime_code='||p_tax_regime_code||
', Tax='||p_tax||
', Tax_jurisdiction_code='||p_tax_jurisdiction_code||
', Tax_status_code='||p_tax_status_code||
', Tax_rate_id='||p_tax_rate_id||
', Tax_rate_code='||p_tax_rate_code||
', Tax_rate='||p_tax_rate||
', Tax_amt='||p_tax_amt||
', Content_owner_id='||p_content_owner_id||
', Manually_entered_flag='||p_manually_entered_flag||
', Record_type_code='||p_record_type_code||
', Last_manual_entry='||p_last_manual_entry||
', Tax_amt_included_flag='||p_tax_amt_included_flag||
', Self_assessed_flag='||p_self_assessed_flag||
', Tax_only_line_flag='||p_tax_only_line_flag);
l_insert := TRUE;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'key : ' || l_key) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Allocate TRUE') ;
l_insert := TRUE;
l_insert := FALSE;
IF l_insert THEN
-- Start : Added a prevetive check to make the trx line exists --
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure,
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Executing Trx Line Exists Validation - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
SELECT /*+first_rows(1)*/ 'Y'
INTO l_trx_line_exist
FROM zx_lines_det_factors
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND trx_id = v_trx_id
AND trx_line_id = v_trx_line_id
AND trx_level_type = v_trx_level_type
AND ROWNUM = 1;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Trx Line Exists Validation : FAILED');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Trx Line Exists Validation : PASSED - '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MM:SS'));
SELECT NVL(MAX(TAX_LINE_NUMBER),0) + 1
INTO l_tax_line_number
FROM ZX_LINES
WHERE APPLICATION_ID = p_application_id
AND EVENT_CLASS_CODE = p_event_class_code
AND ENTITY_CODE = p_entity_code
AND TRX_ID = v_trx_id
AND TRX_LINE_ID = v_trx_line_id
AND TRX_LEVEL_TYPE = v_trx_level_type;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Tax_Line_Number : ' || to_char(l_tax_line_number)) ;
SELECT tax_regime_id
INTO l_regime_id
FROM zx_regimes_b
WHERE TAX_REGIME_CODE = p_tax_regime_code;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Tax_Regime_Id : ' || to_char(l_regime_id)) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Tax_Id : ' || to_char(l_tax_id)) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Tax_Status_Id : ' || to_char(l_status_id)) ;
SELECT zx_lines_s.nextval
INTO l_tax_line_id
FROM dual;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Line_Amt : ' || to_char(v_line_amt)) ;
SELECT DISTINCT nvl(related_doc_date,nvl(provnl_tax_determination_date
,nvl(adjusted_doc_date,trx_date))) ,
trx_date,
related_doc_date ,
adjusted_doc_date
INTO l_tax_determine_date, l_trx_date ,l_related_doc_date, l_adjusted_doc_date
FROM zx_lines_det_factors
WHERE trx_id = v_trx_id
AND application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_code = p_event_class_code
AND rownum = 1;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'exception : ' || sqlerrm);
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'Summarization Columns Values: '||
'Ledger_id='||l_ledger_id||
', Legal_entity_id='||l_legal_entity_id||
', Establishment_id='||l_establishment_id||
', Currency_conversion_date='||TO_CHAR(l_currency_conversion_date,'DD-MON-YYYY')||
', Currency_conversion_type='||l_currency_conversion_type||
', Currency_conversion_rate='||l_currency_conversion_rate||
', Taxable_basis_formula='||l_taxable_basis_formula||
', Tax_calculation_formula='||l_tax_calculation_formula||
', Tax_exemption_id='||l_tax_exemption_id||
', Exempt_certificate_number='||l_exempt_certificate_number||
', Exempt_reason_code='||l_exempt_reason_code||
', Tax_exception_id='||l_tax_exception_id||
', Tax_provider_id='||l_tax_provider_id);
ZX_TRL_DETAIL_OVERRIDE_PKG.Insert_row
(x_rowid => X_Rowid,
p_tax_line_id => l_tax_line_id,
p_internal_organization_id => p_internal_organization_id,
p_application_id => p_application_id,
p_entity_code => p_entity_code,
p_event_class_code => p_event_class_code,
p_event_type_code => NULL,
p_trx_id => v_trx_id,
p_trx_line_id => v_trx_line_id,
p_trx_level_type => v_trx_level_type,
p_trx_line_number => v_trx_line_number,
p_doc_event_status => NULL,
p_tax_event_class_code => NULL,
p_tax_event_type_code => NULL,
p_tax_line_number => l_tax_line_number,
p_content_owner_id => p_content_owner_id,
p_tax_regime_id => l_regime_id,
p_tax_regime_code => p_tax_regime_code,
p_tax_id => l_tax_id,
p_tax => p_tax,
p_tax_status_id => l_status_id,
p_tax_status_code => p_tax_status_code,
p_tax_rate_id => p_tax_rate_id,
p_tax_rate_code => p_tax_rate_code,
p_tax_rate => p_tax_rate,
p_tax_rate_type => NULL,
p_tax_apportionment_line_num => NULL,
p_trx_id_level2 => NULL,
p_trx_id_level3 => NULL,
p_trx_id_level4 => NULL,
p_trx_id_level5 => NULL,
p_trx_id_level6 => NULL,
p_trx_user_key_level1 => NULL,
p_trx_user_key_level2 => NULL,
p_trx_user_key_level3 => NULL,
p_trx_user_key_level4 => NULL,
p_trx_user_key_level5 => NULL,
p_trx_user_key_level6 => NULL,
p_mrc_tax_line_flag => 'N',
p_mrc_link_to_tax_line_id => NULL,
p_ledger_id => l_ledger_id,
p_establishment_id => l_establishment_id,
p_legal_entity_id => l_legal_entity_id,
-- p_legal_entity_tax_reg_number => NULL,
p_hq_estb_reg_number => NULL,
p_hq_estb_party_tax_prof_id => NULL,
p_currency_conversion_date => l_currency_conversion_date,
p_currency_conversion_type => l_currency_conversion_type,
p_currency_conversion_rate => l_currency_conversion_rate,
p_tax_curr_conversion_date => NULL,
p_tax_curr_conversion_type => NULL,
p_tax_curr_conversion_rate => NULL,
p_trx_currency_code => NULL,
p_reporting_currency_code => NULL,
p_minimum_accountable_unit => NULL,
p_precision => NULL,
p_trx_number => v_trx_number,
p_trx_date => l_trx_date,
p_unit_price => NULL,
p_line_amt => v_line_amt,
p_trx_line_quantity => NULL,
p_tax_base_modifier_rate => NULL,
p_ref_doc_application_id => NULL,
p_ref_doc_entity_code => NULL,
p_ref_doc_event_class_code => NULL,
p_ref_doc_trx_id => NULL,
p_ref_doc_trx_level_type => NULL,
p_ref_doc_line_id => NULL,
p_ref_doc_line_quantity => NULL,
p_other_doc_line_amt => NULL,
p_other_doc_line_tax_amt => NULL,
p_other_doc_line_taxable_amt => NULL,
p_unrounded_taxable_amt => v_line_amt,
p_unrounded_tax_amt => v_line_amt,
p_related_doc_application_id => NULL,
p_related_doc_entity_code => NULL,
p_related_doc_evt_class_code => NULL,
p_related_doc_trx_id => NULL,
p_related_doc_trx_level_type => NULL,
p_related_doc_number => NULL,
p_related_doc_date => l_related_doc_date,
p_applied_from_appl_id => NULL,
p_applied_from_evt_clss_code => NULL,
p_applied_from_entity_code => NULL,
p_applied_from_trx_id => NULL,
p_applied_from_trx_level_type => NULL,
p_applied_from_line_id => NULL,
p_applied_from_trx_number => NULL,
p_adjusted_doc_appln_id => NULL,
p_adjusted_doc_entity_code => NULL,
p_adjusted_doc_evt_clss_code => NULL,
p_adjusted_doc_trx_id => NULL,
p_adjusted_doc_trx_level_type => NULL,
p_adjusted_doc_line_id => NULL,
p_adjusted_doc_number => NULL,
p_adjusted_doc_date => l_adjusted_doc_date,
p_applied_to_application_id => NULL,
p_applied_to_evt_class_code => NULL,
p_applied_to_entity_code => NULL,
p_applied_to_trx_id => NULL,
p_applied_to_trx_level_type => NULL,
p_applied_to_line_id => NULL,
p_summary_tax_line_id => p_summary_tax_line_id,
p_offset_link_to_tax_line_id => NULL,
p_offset_flag => 'N',
p_process_for_recovery_flag => 'N',
p_tax_jurisdiction_id => NULL,
p_tax_jurisdiction_code => p_tax_jurisdiction_code,
p_place_of_supply => NULL,
p_place_of_supply_type_code => l_Place_Of_Supply_Type_Code, --Bug 12688194
p_place_of_supply_result_id => NULL,
p_tax_date_rule_id => NULL,
p_tax_date => l_tax_determine_date,
p_tax_determine_date => l_tax_determine_date,
p_tax_point_date => l_tax_determine_date,
p_trx_line_date => v_trx_line_date,
p_tax_type_code => NULL,
p_tax_code => NULL,
p_tax_registration_id => NULL,
p_tax_registration_number => NULL,
p_registration_party_type => NULL,
p_rounding_level_code => 'HEADER',
p_rounding_rule_code => NULL,
p_rndg_lvl_party_tax_prof_id => NULL,
p_rounding_lvl_party_type => NULL,
p_compounding_tax_flag => 'N',
p_orig_tax_status_id => NULL,
p_orig_tax_status_code => NULL,
p_orig_tax_rate_id => NULL,
p_orig_tax_rate_code => NULL,
p_orig_tax_rate => NULL,
p_orig_tax_jurisdiction_id => NULL,
p_orig_tax_jurisdiction_code => NULL,
p_orig_tax_amt_included_flag => 'N',
p_orig_self_assessed_flag => 'N',
p_tax_currency_code => NULL,
p_tax_amt => NULL,
p_tax_amt_tax_curr => NULL,
p_tax_amt_funcl_curr => NULL,
p_taxable_amt => NULL,
p_taxable_amt_tax_curr => NULL,
p_taxable_amt_funcl_curr => NULL,
p_orig_taxable_amt => NULL,
p_orig_taxable_amt_tax_curr => NULL,
p_cal_tax_amt => NULL,
p_cal_tax_amt_tax_curr => NULL,
p_cal_tax_amt_funcl_curr => NULL,
p_orig_tax_amt => NULL,
p_orig_tax_amt_tax_curr => NULL,
p_rec_tax_amt => NULL,
p_rec_tax_amt_tax_curr => NULL,
p_rec_tax_amt_funcl_curr => NULL,
p_nrec_tax_amt => NULL,
p_nrec_tax_amt_tax_curr => NULL,
p_nrec_tax_amt_funcl_curr => NULL,
p_tax_exemption_id => l_tax_exemption_id,
p_tax_rate_before_exemption => NULL,
p_tax_rate_name_before_exempt => NULL,
p_exempt_rate_modifier => NULL,
p_exempt_certificate_number => l_exempt_certificate_number,
p_exempt_reason => NULL,
p_exempt_reason_code => l_exempt_reason_code,
p_tax_exception_id => l_tax_exception_id,
p_tax_rate_before_exception => NULL,
p_tax_rate_name_before_except => NULL,
p_exception_rate => NULL,
p_tax_apportionment_flag => 'N',
p_historical_flag => 'N',
p_taxable_basis_formula => l_taxable_basis_formula,
p_tax_calculation_formula => l_tax_calculation_formula,
p_cancel_flag => 'N',
p_purge_flag => 'N',
p_delete_flag => 'N',
p_tax_amt_included_flag => p_tax_amt_included_flag,
p_self_assessed_flag => p_self_assessed_flag,
p_overridden_flag => 'N',
p_manually_entered_flag => p_manually_entered_flag,
p_reporting_only_flag => 'N',
p_freeze_until_overriddn_flg => 'N',
p_copied_from_other_doc_flag => 'N',
p_recalc_required_flag => 'Y',
p_settlement_flag => 'N',
p_item_dist_changed_flag => 'N',
p_assoc_children_frozen_flg => 'N',
p_tax_only_line_flag => p_tax_only_line_flag,
p_compounding_dep_tax_flag => 'N',
p_compounding_tax_miss_flag => 'N',
p_sync_with_prvdr_flag => 'N',
p_last_manual_entry => NULL,
p_tax_provider_id => l_tax_provider_id,
p_record_type_code => p_record_type_code,
p_reporting_period_id => NULL,
p_legal_justification_text1 => NULL,
p_legal_justification_text2 => NULL,
p_legal_justification_text3 => NULL,
p_legal_message_appl_2 => NULL,
p_legal_message_status => NULL,
p_legal_message_rate => NULL,
p_legal_message_basis => NULL,
p_legal_message_calc => NULL,
p_legal_message_threshold => NULL,
p_legal_message_pos => NULL,
p_legal_message_trn => NULL,
p_legal_message_exmpt => NULL,
p_legal_message_excpt => NULL,
p_tax_regime_template_id => NULL,
p_tax_applicability_result_id => NULL,
p_direct_rate_result_id => NULL,
p_status_result_id => NULL,
p_rate_result_id => NULL,
p_basis_result_id => NULL,
p_thresh_result_id => NULL,
p_calc_result_id => NULL,
p_tax_reg_num_det_result_id => NULL,
p_eval_exmpt_result_id => NULL,
p_eval_excpt_result_id => NULL,
p_enforced_from_nat_acct_flg => 'N',
p_tax_hold_code => NULL,
p_tax_hold_released_code => NULL,
p_prd_total_tax_amt => NULL,
p_prd_total_tax_amt_tax_curr => NULL,
p_prd_total_tax_amt_funcl_curr => NULL,
p_trx_line_index => NULL,
p_offset_tax_rate_code => NULL,
p_proration_code => NULL,
p_other_doc_source => NULL,
p_internal_org_location_id => NULL,
p_line_assessable_value => NULL,
p_ctrl_total_line_tx_amt => NULL,
p_applied_to_trx_number => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_global_attribute_category => NULL,
p_global_attribute1 => NULL,
p_global_attribute2 => NULL,
p_global_attribute3 => NULL,
p_global_attribute4 => NULL,
p_global_attribute5 => NULL,
p_global_attribute6 => NULL,
p_global_attribute7 => NULL,
p_global_attribute8 => NULL,
p_global_attribute9 => NULL,
p_global_attribute10 => NULL,
p_global_attribute11 => NULL,
p_global_attribute12 => NULL,
p_global_attribute13 => NULL,
p_global_attribute14 => NULL,
p_global_attribute15 => NULL,
p_numeric1 => NULL,
p_numeric2 => NULL,
p_numeric3 => NULL,
p_numeric4 => NULL,
p_numeric5 => NULL,
p_numeric6 => NULL,
p_numeric7 => NULL,
p_numeric8 => NULL,
p_numeric9 => NULL,
p_numeric10 => NULL,
p_char1 => NULL,
p_char2 => NULL,
p_char3 => NULL,
p_char4 => NULL,
p_char5 => NULL,
p_char6 => NULL,
p_char7 => NULL,
p_char8 => NULL,
p_char9 => NULL,
p_char10 => NULL,
p_date1 => NULL,
p_date2 => NULL,
p_date3 => NULL,
p_date4 => NULL,
p_date5 => NULL,
p_date6 => NULL,
p_date7 => NULL,
p_date8 => NULL,
p_date9 => NULL,
p_date10 => NULL,
p_interface_entity_code => NULL,
p_interface_tax_line_id => NULL,
p_taxing_juris_geography_id => NULL,
p_adjusted_doc_tax_line_id => NULL,
p_object_version_number => 1,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id );
ZX_TRL_ALLOCATIONS_PKG.Update_Tax_Amt
(p_summary_tax_line_id => p_summary_tax_line_id,
p_application_id => p_application_id,
p_entity_code => p_entity_code,
p_event_class_code => p_event_class_code,
p_trx_id => v_trx_id,
p_tax_amt => p_tax_amt);
ZX_TRL_ALLOCATIONS_PKG.Delete_alloc_tbl;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.END',
'Insert_All_Allocation (-)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG. Insert_All_Allocation.EXCEPTION',
'NO_DATA_FOUND Excpetion raised.');
END Insert_All_Allocation;
PROCEDURE Insert_Tax_Line
(p_summary_tax_line_id NUMBER,
p_internal_organization_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_trx_id NUMBER,
p_trx_number VARCHAR2,
p_tax_regime_code VARCHAR2,
p_tax VARCHAR2,
p_tax_jurisdiction_code VARCHAR2,
p_tax_status_code VARCHAR2,
p_tax_rate_id NUMBER,
p_tax_rate_code VARCHAR2,
p_tax_rate NUMBER,
p_tax_amt NUMBER,
p_line_amt NUMBER,
p_trx_line_date DATE,
p_summ_tax_only VARCHAR2,
p_manually_entered_flag VARCHAR2,
p_last_manual_entry VARCHAR2,
p_tax_amt_included_flag VARCHAR2,
p_self_assessed_flag VARCHAR2,
p_created_by NUMBER,
p_creation_date DATE,
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER,
p_event_type_code VARCHAR,
p_legal_entity_id NUMBER,
p_ledger_id NUMBER,
p_trx_currency_code VARCHAR,
p_currency_conversion_date DATE,
p_currency_conversion_rate NUMBER,
p_currency_conversion_type VARCHAR2,
p_content_owner_id NUMBER,
p_trx_date DATE,
p_minimum_accountable_unit NUMBER,
p_precision NUMBER,
p_trx_line_gl_date DATE ) IS
l_tax_line_id NUMBER;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Tax_Line.BEGIN',
'Insert_Tax_Line (+)');
SELECT nvl(min(trx_line_id),0),
nvl(min(trx_line_number),0)
INTO v_trx_line_number,
v_trx_line_id
FROM ZX_LINES
WHERE TRX_ID = p_trx_id
AND APPLICATION_ID = p_application_id
AND EVENT_CLASS_CODE = p_event_class_code
AND ENTITY_CODE = p_entity_code;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'event type code ' || p_event_type_code) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'legal entity id' || to_char(p_legal_entity_id)) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'ledger id' || to_char(p_ledger_id)) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'trx_currency_code ' || p_trx_currency_code) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'content owner id ' || p_content_owner_id) ;
SELECT TAX_EVENT_CLASS_CODE, TAX_REPORTING_FLAG, EVENT_CLASS_MAPPING_ID
INTO l_tax_event_class_code, l_tax_reporting_flag, l_event_class_mapping_id
FROM ZX_EVNT_CLS_MAPPINGS
WHERE application_id = p_application_id
AND entity_code = p_entity_code
AND event_class_Code = p_event_class_code;
select tax_id, tax_currency_code, tax_type_code,
DECODE(NVL(l_tax_reporting_flag,'N'),'Y',
legal_reporting_status_def_val,NULL)
INTO l_tax_id, l_tax_currency_code, l_tax_type_code, l_legal_reporting_status
FROM ZX_SCO_TAXES
WHERE TAX_REGIME_CODE = p_tax_regime_code
AND TAX=p_tax;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'tax id : ' || to_char(l_tax_id)) ;
select tax_regime_id
INTO l_regime_id
FROM zx_regimes_b
WHERE TAX_REGIME_CODE = p_tax_regime_code;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
select tax_status_id
INTO l_status_id
FROM zx_sco_status
WHERE tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND tax_status_code = p_tax_status_code ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
SELECT tax_jurisdiction_id
INTO l_tax_jurisdiction_id
FROM zx_jurisdictions_b
WHERE tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND tax_jurisdiction_code = p_tax_jurisdiction_code;
SELECT rate_type_code
INTO l_rate_type_code
FROM ZX_SCO_RATES
WHERE tax_rate_id = p_tax_rate_id;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.insert_tax_line.END',
'ZX_TRL_ALLOCATIONS_PKG.insert_tax_line(-)');
SELECT zx_lines_s.nextval
INTO l_tax_line_id
FROM dual;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation',
'status id : ' || to_char(l_status_id)) ;
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_All_Allocation.',
'Trx Line Id: '||to_char(l_trx_line_id));
SELECT TRX_NUMBER, NVL(P_TRX_CURRENCY_CODE, TRX_CURRENCY_CODE),
NVL(P_CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_DATE),
NVL(P_CURRENCY_CONVERSION_RATE, CURRENCY_CONVERSION_RATE),
NVL(P_CURRENCY_CONVERSION_TYPE, CURRENCY_CONVERSION_TYPE),
HQ_ESTB_PARTY_TAX_PROF_ID, INTERNAL_ORG_LOCATION_ID,
DEFAULT_TAXATION_COUNTRY, RDNG_SHIP_FROM_PTY_TX_PROF_ID,
RDNG_BILL_FROM_PTY_TX_PROF_ID, RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
RDNG_BILL_FROM_PTY_TX_P_ST_ID, SHIP_TO_LOCATION_ID,
SHIP_FROM_LOCATION_ID, BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID, SHIP_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_PARTY_TAX_PROF_ID, SHIP_FROM_SITE_TAX_PROF_ID,
BILL_FROM_SITE_TAX_PROF_ID, CTRL_HDR_TX_APPL_FLAG,
SHIP_THIRD_PTY_ACCT_SITE_ID, BILL_THIRD_PTY_ACCT_SITE_ID,
SHIP_THIRD_PTY_ACCT_ID, BILL_THIRD_PTY_ACCT_ID
INTO l_trx_number,l_trx_currency_code,l_currency_conversion_date,
l_currency_conversion_rate,l_currency_conversion_type,
l_hq_estb_pty_tax_prof_id, l_internal_org_loc_id,
l_default_taxation_country, l_rndg_ship_fr_pty_tx_prof_id,
l_rndg_bill_fr_pty_tx_prof_id, l_rndg_ship_fr_pty_tx_p_st_id,
l_rndg_bill_fr_pty_tx_p_st_id, l_ship_to_location_id,
l_ship_from_location_id, l_bill_to_location_id,
l_bill_from_location_id, l_ship_from_pty_tax_prof_id,
l_bill_from_pty_tax_prof_id, l_ship_from_site_tx_prof_id,
l_bill_from_site_tx_prof_id, l_ctrl_hdr_tx_appl_flag,
l_ship_third_pty_acct_site_id, l_bill_third_pty_acct_site_id,
l_ship_third_pty_acct_id, l_bill_third_pty_acct_id
FROM ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND ENTITY_CODE = P_ENTITY_CODE
AND EVENT_CLASS_CODE = P_EVENT_CLASS_CODE
AND TRX_ID = P_TRX_ID
AND ROWNUM = 1;
INSERT INTO ZX_LINES (SUMMARY_TAX_LINE_ID,
INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
--EVENT_TYPE_CODE,
TRX_LINE_NUMBER,
TRX_ID,
TRX_NUMBER,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
TAX_LINE_ID,
CONTENT_OWNER_ID,
TAX_LINE_NUMBER,
LINE_AMT,
TRX_LINE_DATE,
TRX_DATE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
TAX_REGIME_CODE,
TAX_REGIME_ID,
TAX,
TAX_ID,
TAX_JURISDICTION_CODE,
TAX_STATUS_CODE,
TAX_STATUS_ID,
TAX_DETERMINE_DATE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
TAX_JURISDICTION_ID,
TAX_AMT,
TAX_CURRENCY_CODE,
OFFSET_FLAG,
PROCESS_FOR_RECOVERY_FLAG,
COMPOUNDING_TAX_FLAG,
ORIG_TAX_AMT_INCLUDED_FLAG,
ORIG_SELF_ASSESSED_FLAG,
TAX_APPORTIONMENT_FLAG,
HISTORICAL_FLAG,
CANCEL_FLAG,
PURGE_FLAG,
DELETE_FLAG,
TAX_AMT_INCLUDED_FLAG,
SELF_ASSESSED_FLAG,
OVERRIDDEN_FLAG,
MANUALLY_ENTERED_FLAG,
REPORTING_ONLY_FLAG,
FREEZE_UNTIL_OVERRIDDEN_FLAG,
COPIED_FROM_OTHER_DOC_FLAG,
RECALC_REQUIRED_FLAG,
SETTLEMENT_FLAG,
ITEM_DIST_CHANGED_FLAG,
ASSOCIATED_CHILD_FROZEN_FLAG,
TAX_ONLY_LINE_FLAG,
COMPOUNDING_DEP_TAX_FLAG,
ENFORCE_FROM_NATURAL_ACCT_FLAG,
MRC_TAX_LINE_FLAG,
LAST_MANUAL_ENTRY,
UNROUNDED_TAX_AMT,
UNROUNDED_TAXABLE_AMT,
RECORD_TYPE_CODE,
TAX_APPORTIONMENT_LINE_NUMBER,
ROUNDING_LEVEL_CODE,
OBJECT_VERSION_NUMBER,
--bug 7300367
TAX_EVENT_CLASS_CODE,
TAXABLE_BASIS_FORMULA,
TAX_CALCULATION_FORMULA,
COMPOUNDING_TAX_MISS_FLAG,
TRX_CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
LEGAL_REPORTING_STATUS,
--bug 7300367
--bug 7369708
HQ_ESTB_PARTY_TAX_PROF_ID,
TAX_CURRENCY_CONVERSION_DATE,
UNIT_PRICE,
TRX_LINE_QUANTITY,
TAX_BASE_MODIFIER_RATE,
PLACE_OF_SUPPLY_TYPE_CODE,
TAX_DATE,
TAX_POINT_DATE,
TAX_TYPE_CODE,
--ROUNDING_LVL_PARTY_TAX_PROF_ID,
--ROUNDING_LVL_PARTY_TYPE,
INTERNAL_ORG_LOCATION_ID,
LINE_ASSESSABLE_VALUE,
TAX_RATE_TYPE,
--bug 7369708
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (p_summary_tax_line_id,
p_internal_organization_id,
p_application_id,
p_entity_code,
p_event_class_code,
--p_event_type_code,
l_trx_line_number,
p_trx_id,
p_trx_number,
l_trx_line_id,
'LINE',--p_trx_level_type,
l_tax_line_id,
p_content_owner_id,
l_tax_line_number,
p_line_amt,
p_trx_line_date,
p_trx_date,
p_minimum_accountable_unit,
p_precision,
p_tax_regime_code,
l_regime_id,
p_tax,
l_tax_id,
p_tax_jurisdiction_code,
p_tax_status_code,
l_status_id,
p_trx_date,
p_tax_rate_id,
p_tax_rate_code,
p_tax_rate,
l_tax_jurisdiction_id,
NULL, --p_tax_amt,
l_tax_currency_code,
'N',
'Y',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
p_tax_amt_included_flag,
p_self_assessed_flag,
'N',
'Y',
'N',
'N',
'N',
'Y',
'N',
'N',
'N',
'Y',
'N',
'N',
'N',
'TAX_AMOUNT',
p_tax_amt,
(p_tax_amt/p_tax_rate) * 100,
'ETAX_CREATED',
1,
'HEADER',
1, -- object_version_number,
--bug 7300367
l_tax_event_class_code,
'STANDARD_TB',
'STANDARD_TC',
'N',
l_trx_currency_code,
l_CURRENCY_CONVERSION_DATE,
l_CURRENCY_CONVERSION_RATE,
l_CURRENCY_CONVERSION_TYPE,
l_legal_reporting_status,
--bug 7300367
--bug 7369708
l_hq_estb_pty_tax_prof_id, --HQ_ESTB_PARTY_TAX_PROF_ID
l_CURRENCY_CONVERSION_DATE, --TAX_CURRENCY_CONVERSION_DATE
P_LINE_AMT, --UNIT_PRICE
1, --TRX_LINE_QUANTITY
1, --TAX_BASE_MODIFIER_RATE
'SHIP_TO',
p_trx_date, --TAX_DATE
p_trx_date, --TAX_POINT_DATE
l_tax_type_code,
--ROUNDING_LVL_PARTY_TAX_PROF_ID,
--ROUNDING_LVL_PARTY_TYPE,
l_internal_org_loc_id, --INTERNAL_ORG_LOCATION_ID
P_LINE_AMT, --LINE_ASSESSABLE_VALUE
l_rate_type_code,
--bug 7369708
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
INSERT INTO ZX_LINES_DET_FACTORS( INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
LINE_LEVEL_ACTION,
TRX_ID,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
TRX_DATE,
TRX_LINE_GL_DATE,
LEGAL_ENTITY_ID,
LINE_AMT,
LINE_AMT_INCLUDES_TAX_FLAG,
record_type_code,
object_version_number,
LEDGER_ID,
FIRST_PTY_ORG_ID,
TRX_CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
tax_processing_completed_flag,
--bug 7300367
TAX_EVENT_CLASS_CODE,
LINE_CLASS,
TRX_LINE_TYPE,
TRX_NUMBER,
TRX_LINE_NUMBER,
ASSESSABLE_VALUE,
HISTORICAL_FLAG,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
TAX_REPORTING_FLAG,
--bug 7300367
--bug 7369708
EVENT_CLASS_MAPPING_ID,
DEFAULT_TAXATION_COUNTRY,
--DOC_SEQ_ID,
--DOC_SEQ_NAME,
--DOC_SEQ_VALUE,
RDNG_SHIP_FROM_PTY_TX_PROF_ID,
RDNG_BILL_FROM_PTY_TX_PROF_ID,
RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
RDNG_BILL_FROM_PTY_TX_P_ST_ID,
TRX_LINE_QUANTITY,
HQ_ESTB_PARTY_TAX_PROF_ID,
SHIP_TO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
SHIP_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_PARTY_TAX_PROF_ID,
SHIP_FROM_SITE_TAX_PROF_ID,
BILL_FROM_SITE_TAX_PROF_ID,
CTRL_HDR_TX_APPL_FLAG,
TRX_LINE_DATE,
INTERNAL_ORG_LOCATION_ID,
UNIT_PRICE,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
SHIP_THIRD_PTY_ACCT_ID,
BILL_THIRD_PTY_ACCT_ID,
TRX_LINE_CURRENCY_CODE,
TRX_LINE_CURRENCY_CONV_RATE,
TRX_LINE_CURRENCY_CONV_DATE,
TRX_LINE_PRECISION,
TRX_LINE_MAU,
TRX_LINE_CURRENCY_CONV_TYPE,
TOTAL_INC_TAX_AMT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
--bug 7369708
)
VALUES
(
p_internal_organization_id,
p_APPLICATION_ID,
P_ENTITY_CODE,
P_EVENT_CLASS_CODE,
p_event_type_code,
'LINE_INFO_TAX_ONLY',
p_TRX_ID,
l_trx_line_id,
'LINE',
P_TRX_DATE,
p_trx_line_gl_date,
p_LEGAL_ENTITY_ID,
p_tax_amt,
p_tax_amt_included_flag,
'ETAX_CREATED',
1,
p_LEDGER_ID,
p_content_owner_id,
l_trx_currency_code, -- should be changed to p_trx_currence_code
l_CURRENCY_CONVERSION_DATE,
l_CURRENCY_CONVERSION_RATE,
l_CURRENCY_CONVERSION_TYPE,
'Y',
--bug 7300367
l_tax_event_class_code,
P_EVENT_CLASS_CODE,
'ITEM',
l_trx_number,
l_trx_line_number,
p_line_amt,
'N',
p_minimum_accountable_unit,
p_precision,
l_tax_reporting_flag,
--bug 7300367
--bug 7369708
l_event_class_mapping_id, --EVENT_CLASS_MAPPING_ID
l_default_taxation_country, --DEFAULT_TAXATION_COUNTRY
--DOC_SEQ_ID,
--DOC_SEQ_NAME,
--DOC_SEQ_VALUE,
l_rndg_ship_fr_pty_tx_prof_id, --RDNG_SHIP_FROM_PTY_TX_PROF_ID
l_rndg_bill_fr_pty_tx_prof_id, --RDNG_BILL_FROM_PTY_TX_PROF_ID
l_rndg_ship_fr_pty_tx_p_st_id, --RDNG_SHIP_FROM_PTY_TX_P_ST_ID
l_rndg_bill_fr_pty_tx_p_st_id, --RDNG_BILL_FROM_PTY_TX_P_ST_ID
1, --TRX_LINE_QUANTITY
l_hq_estb_pty_tax_prof_id,--HQ_ESTB_PARTY_TAX_PROF_ID
l_ship_to_location_id, --SHIP_TO_LOCATION_ID
l_ship_from_location_id, --SHIP_FROM_LOCATION_ID
l_bill_to_location_id, --BILL_TO_LOCATION_ID
l_bill_from_location_id, --BILL_FROM_LOCATION_ID
l_ship_from_pty_tax_prof_id, --SHIP_FROM_PARTY_TAX_PROF_ID
l_bill_from_pty_tax_prof_id, --BILL_FROM_PARTY_TAX_PROF_ID
l_ship_from_site_tx_prof_id, --SHIP_FROM_SITE_TAX_PROF_ID
l_bill_from_site_tx_prof_id, --BILL_FROM_SITE_TAX_PROF_ID
l_ctrl_hdr_tx_appl_flag, --CTRL_HDR_TX_APPL_FLAG
p_trx_line_date, --TRX_LINE_DATE
l_internal_org_loc_id, --INTERNAL_ORG_LOCATION_ID
p_line_amt, --UNIT_PRICE
l_ship_third_pty_acct_site_id, --SHIP_THIRD_PTY_ACCT_SITE_ID
l_bill_third_pty_acct_site_id, --BILL_THIRD_PTY_ACCT_SITE_ID
l_ship_third_pty_acct_id, --SHIP_THIRD_PTY_ACCT_ID
l_bill_third_pty_acct_id, --BILL_THIRD_PTY_ACCT_ID
l_trx_currency_code, --TRX_LINE_CURRENCY_CODE
l_currency_conversion_rate, --TRX_LINE_CURRENCY_CONV_RATE
l_currency_conversion_date, --TRX_LINE_CURRENCY_CONV_DATE
p_precision, --TRX_LINE_PRECISION
p_minimum_accountable_unit, --TRX_LINE_MAU
l_currency_conversion_type, --TRX_LINE_CURRENCY_CONV_TYPE
0, --TOTAL_INC_TAX_AMT
fnd_global.user_id, --CREATED_BY
sysdate, --CREATION_DATE
fnd_global.user_id, --LAST_UPDATED_BY
sysdate, --LAST_UPDATE_DATE
fnd_global.login_id --LAST_UPDATE_LOGIN
--bug 7369708
);
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Insert_Tax_Line.BEGIN',
'Insert_Tax_Line (-)');
END Insert_Tax_Line;
PROCEDURE Delete_alloc_tbl IS
BEGIN
IF (g_level_statement >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_statement,
'ZX.PLSQL.ZX_TRL_ALLOCATIONS_PKG.Delete_alloc_tbl',
'Plsql Cache Structure flushed') ;
g_trx_allocate_tbl.DELETE;